Tuesday, September 5, 2017¶
For Lino Tera we need to print the list of intra-Community operations. Vocabulary:
de : Liste der innergemeinschaftlichen Operationen (Einkäufe / Verkäufe)
fr : Liste des opérations intracommunautaires (achats / ventes)
en : List of intra-Community operations (purchases / sales)
This list has the special challenge that it might contain data from
several models. Namely all models which inherit from
lino_xl.lib.vat.VatDocument. For example in Lino Così there
are two such models:
>>> from lino import startup
>>> startup('lino_book.projects.cosi1.settings.demo')
...
>>> from lino.api.doctest import *
>>> from lino_xl.lib.vat.mixins import VatDocument
>>> for m in rt.models_by_base(VatDocument):
... print(m)
...
<class 'lino_xl.lib.trading.models.VatProductInvoice'>
<class 'lino_xl.lib.vat.models.VatAccountInvoice'>
The number of invoice models depends on the application. For example in Lino Tera there are three models.
Currently there is no example where invoices of different models occur in a same intracom list, but it is theoretically possible and even probably that some site some day will want them. So I asked Google for “Django” and “union” and that’s how I discovered that Django now has a union() operator for QuerySets. Let’s try it:
>>> rt.show(vat.IntracomInvoices)
...
======================= ========== ==================== =========== ======================= =================== ============== ===================
Buchungsdatum Rechnung Partner MwSt.-Nr. MwSt.-Regime Total zzgl. MwSt. MwSt. Total inkl. MwSt.
----------------------- ---------- -------------------- ----------- ----------------------- ------------------- -------------- -------------------
03.01.14 *PRC 1* AS Express Post Innergemeinschaftlich 33,06 6,94 40,00
11.01.14 *SLS 5* Garage Mergelsberg Innergemeinschaftlich 442,15 92,85 535,00
08.02.14 *PRC 6* AS Express Post Innergemeinschaftlich 34,13 7,17 41,30
06.03.14 *PRC 11* AS Express Post Innergemeinschaftlich 33,55 7,05 40,60
04.04.14 *PRC 16* AS Express Post Innergemeinschaftlich 35,12 7,38 42,50
13.04.14 *SLS 17* Ausdemwald Alfons Innergemeinschaftlich 385,09 80,87 465,96
09.05.14 *PRC 21* AS Express Post Innergemeinschaftlich 33,97 7,13 41,10
07.06.14 *PRC 26* AS Express Post Innergemeinschaftlich 33,06 6,94 40,00
11.06.14 *SLS 29* Evers Eberhart Innergemeinschaftlich 1 942,00 407,81 2 349,81
05.07.14 *PRC 31* AS Express Post Innergemeinschaftlich 34,13 7,17 41,30
03.08.14 *PRC 36* AS Express Post Innergemeinschaftlich 33,55 7,05 40,60
08.09.14 *PRC 41* AS Express Post Innergemeinschaftlich 35,12 7,38 42,50
13.09.14 *SLS 41* Johnen Johann Innergemeinschaftlich 442,15 92,85 535,00
06.10.14 *PRC 46* AS Express Post Innergemeinschaftlich 33,97 7,13 41,10
04.11.14 *PRC 51* AS Express Post Innergemeinschaftlich 33,06 6,94 40,00
11.11.14 *SLS 53* Meier Marie-Louise Innergemeinschaftlich 385,09 80,87 465,96
09.12.14 *PRC 56* AS Express Post Innergemeinschaftlich 34,13 7,17 41,30
07.01.15 *PRC 61* AS Express Post Innergemeinschaftlich 33,88 7,12 41,00
05.02.15 *PRC 66* AS Express Post Innergemeinschaftlich 35,45 7,45 42,90
13.02.15 *SLS 65* Radermacher Hans Innergemeinschaftlich 1 942,00 407,81 2 349,81
03.03.15 *PRC 71* AS Express Post Innergemeinschaftlich 34,30 7,20 41,50
**Total (21 Zeilen)** **6 048,96** **1 270,28** **7 319,24**
======================= ========== ==================== =========== ======================= =================== ============== ===================
Cool! The above table would have required a virtual table before
Django 1.11 because (in that demo database) purchase invoices are
entered as VatAccountInvoice while sales invoices as
VatProductInvoice.
Of course this needed a few changes to the core code:
Lino now supports Django’s union()
operator. A UNION is automatically done when
lino.core.actors.Actor.model is an abstact model for which
the application has more than one concrete model.
Until now it was possible for Lino to tell whether a table was “abstract” (i.e. to be ignored when analyzing) or not by looking at the model. But now we have non-abstract tables on abstract models.
A side effect of the intracom reports is that vat_id is now injected to Partner and no longer to Company. That’s not perfect because persons never have a VAT id. That might be a reason to consider making invoices only for organizations as partner. Invoices to a private person would then point to a special company “private partner”.
I had to add a unelegant filter keyword argument to
lino.core.model.Model.get_request_queryset() because union
querysets do not support filter on subqueries, and unfortunately Lino
is currently designed to do all custom filtering inside
lino.core.actors.Actor.get_request_queryset() (i.e. after having
called the model’s get_request_queryset). TODO: find a more
elegant API. Maybe a new method get_filter_kw() (after renaming
the existing lino.core.tables.Table.get_filter_kw() to
get_master_kw)
It seems that there is some limitation when trying to order the
invoices by order_by = [‘entry_date’, ‘partner’] which causes a
DatabaseError ORDER BY term does not match any column in the
result set. But the sorting order is not that important for us.
Note that I must specify certain fields as hidden_elements.
And lino_xl.lib.vat.VatDocument.compute_totals() now adds also
returnable VAT to the total_vat field. Because we need that sum in
our listing. And anyway it was irritating to not have it visible
there. Yes, returnable VAT is special because the VAT is computed but
isn’t paid to the supplier.
TODO: The menu versions of the tables are not really usable because when the user opens the parameter panel in order to specifiy a period range, they say:
There was a problem with the database connection.
Exception: ParameterStore of LayoutHandle for lino.core.layouts.ParamsLayout on lino_xl.lib.vat.desktop.IntracomInvoices expects a list of 6 values but got 5: [u'', u'', u'', u'', u'']
If the error persists, try reloading the browser page.
In Lino Voga I had this failure:
DoesNotExist: VatProductInvoice matching query does not exist.
I added a snippet in voga.specs.vat to reproduce it. It was
caused when Lino tried to read fields from the model instance which
were not included in the original query. I removed disabled_fields
by setting the table’s to editable to False.
Internal optimization around description_column¶
We had two “clickable descriptions” of an object : overview
and description_column.
The description_column field was being used only on Grantings and
Confirmations in lino_welfare.modlib.aids. Its header was
“Description”, which clashes with the virtual column overview. I
renamed the field to detail_pointer and changed its header
from “Description” to “Details”. It is no longer being defined on
Model because implemented in AbstractTable (advantage: the column is
also available on abstract tables,
e.g. aids.ConfirmationsByGranting).
lino.core.inject.update_field() failed on virtual fields, and in
general worked only on models, not on a table. But for detail_pointer
we want the default label to be “Invoice”.
So I reimplemented the lookup method.
I moved create_user
from
from lino_noi.lib.users.models
to lino.modlib.users.utils because importing online users when they were not installed caused a side effect with the new implementation of update_field.
UNION and MariaDB¶
Oops, after deploying today’s work to their production site I see that the UNION is obiously not supported on MariaDB:
ProgrammingError: (1064, "You have an error in your SQL syntax;
check the manual that corresponds to your MariaDB server version for
the right syntax to use near 'UNION (SELECT
`ledger_voucher`.`user_id`, `ledger_voucher`.`journal_id`, `ledger'
at line 1")
I upgraded from Django 1.11.3 to 1.11.5 just to make sure, but that didn’t help.
Tomorrow I will try using MySQL or PostgreSQL.