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.apc.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.sales.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 VAT declaration in Lino Voga 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.