Extending python-sql

[Udo Spallek]

Hi,

I would like to extend account_invoice.Invoice.search_total_amount[A]
which is based on a python-sql query.
It should be extended with another module.
In my extension I would like to replace the following column[B]::

Coalesce(Sum(Round(
(
line.quantity * line.unit_price).cast(type_name),
currency.digits)), 0).as_('total_amount'),

with something like this::

Coalesce(Sum(Round(
(
line.quantity * line.unit_price * (1 - line.discount /
100.0) ).cast(type_name), currency.digits)), 0).as_('total_amount')


The general boilerplate code in the other module looks like::

class Invoice:
__metaclass__ = PoolMeta
__name__ = 'account.invoice'

@classmethod
def search_total_amount(cls, name, clause):

result = super(Invoice, cls).search_total_amount(
name, clause)


With introspection I found the column::

columns = list(
result[0][2].columns[0].table.select.im_self
.queries[0].columns)

But I have no idea, how to replace the column, because I have not the
correct line object from the depends module.
When I try something like this::

pool = Pool()
Line = pool.get('account.invoice.line')
line = Line.__table__()

columns[1] = Coalesce(Sum(Round((
line.quantity * line.unit_price * (1 - line.discount / 100.0)
).cast(type_name), currency.digits)), 0).as_('total_amount')

result[0][2].columns[0].table.select.im_self.queries[0].columns = \
tuple(columns)

the line table is wrong, I need the table from [B].

Questions:
1. Is it a good practice trying to extend python-sql?
2. Is there a better way to introspect the python-sql then this::

columns = list(
result[0][2].columns[0].table.select.im_self
.queries[0].columns)
3. How to get the line-object from the base sql?

TIA and Regards Udo Spallek

[A]http://hg.tryton.org/modules/account_invoice/file/tip/invoice.py#l741
[B]
http://hg.tryton.org/modules/account_invoice/file/tip/invoice.py#l764

[Cédric Krier]

On 2017-12-30 15:08, 'Udo Spallek' via tryton-dev wrote:
> Hi,
>
> I would like to extend account_invoice.Invoice.search_total_amount[A]
> which is based on a python-sql query.
> It should be extended with another module.
> In my extension I would like to replace the following column[B]::
>
> Coalesce(Sum(Round(
> (
> line.quantity * line.unit_price).cast(type_name),
> currency.digits)), 0).as_('total_amount'),
>
> with something like this::
>
> Coalesce(Sum(Round(
> (
> line.quantity * line.unit_price * (1 - line.discount /
> 100.0) ).cast(type_name), currency.digits)), 0).as_('total_amount')
>
>
>

> With introspection I found the column::
>
> columns = list(
> result[0][2].columns[0].table.select.im_self
> .queries[0].columns)
>
> But I have no idea, how to replace the column, because I have not the
> correct line object from the depends module.
> When I try something like this::
>
> pool = Pool()
> Line = pool.get('account.invoice.line')
> line = Line.__table__()
>
> columns[1] = Coalesce(Sum(Round((
> line.quantity * line.unit_price * (1 - line.discount / 100.0)
> ).cast(type_name), currency.digits)), 0).as_('total_amount')
>
> result[0][2].columns[0].table.select.im_self.queries[0].columns = \
> tuple(columns)
>
> the line table is wrong, I need the table from [B].

Yes, you must take it from the 'from_' of the select but it is a
complicated task and fragile.

> Questions:
> 1. Is it a good practice trying to extend python-sql?

Not really this way. The usual way is to have composable query.

> 2. Is there a better way to introspect the python-sql then this::
>
> columns = list(
> result[0][2].columns[0].table.select.im_self
> .queries[0].columns)

Normally you should not need to use 'im_self' attributes otherwise it
seems OK. But as I said it is quite fragile as it will fail if any order
is changed in the initial query.

> 3. How to get the line-object from the base sql?

It is stored in the 'from_' of the SelectQuery.


In general, I think it is the wrong way to implement a discount. The
discount should modify the unit price. Otherwise you are changing the
definition of the field 'unit_price' and this may break other modules
which may rely on it.

--
Cédric Krier - B2CK SPRL
Email/Jabber: cedric...@b2ck.com
Tel: +32 472 54 46 59
Website: http://www.b2ck.com/

[Sergi Almacellas Abellana]

El 04/01/18 a les 11:26, Cédric Krier ha escrit:

> In general, I think it is the wrong way to implement a discount. The
> discount should modify the unit price. Otherwise you are changing the
> definition of the field 'unit_price' and this may break other modules
> which may rely on it.

I agree with Cèdric here.

Indeed if you are interested on having discount information on
sales/invoices (not sure if really required for purchase), you should
probably start a blueprint as I think it's generic enough to have it on
base.

--
Sergi Almacellas Abellana
www.koolpi.com
Twitter: @pokoli_srk

[Udo Spallek]

Hi,

Thu, 4 Jan 2018 15:31:45 +0100
Sergi Almacellas Abellana <se...@koolpi.com>:

>El 04/01/18 a les 11:26, Cédric Krier ha escrit:
>> In general, I think it is the wrong way to implement a discount. The
>> discount should modify the unit price.

Is this in general your proposal?

On sale.line/invoice.line, if there is a product, it has a list price.
We can calculate the percentage between product list price and line unit
price as line discount.
When changing unit price value the discount to the product list_price
is re-calculated.
When changing discount value, the unit price is re-calculated from
product list_price.
As an exception the unit price also can be an individual amount if
there is no product given. Without a product, there is no discount,
the discount field becomes invisible.

Regards Udo

[Sergi Almacellas Abellana]

El 04/01/18 a les 18:13, 'Udo Spallek' via tryton-dev ha escrit:

Moreless. Probably the discount can be a functional field which should
update the unit_price.

But this will be better discusses as a blueprint on
https://discuss.tryton.org/

[Udo Spallek]

Thu, 4 Jan 2018 19:38:54 +0100
Sergi Almacellas Abellana <se...@koolpi.com>:

>El 04/01/18 a les 18:13, 'Udo Spallek' via tryton-dev ha escrit:

>> Thu, 4 Jan 2018 15:31:45 +0100
>> Sergi Almacellas Abellana <se...@koolpi.com>:
>>> El 04/01/18 a les 11:26, Cédric Krier ha escrit:
>>>> In general, I think it is the wrong way to implement a discount.
>>>> The discount should modify the unit price.
>> Is this in general your proposal?
>> On sale.line/invoice.line, if there is a product, it has a list
>> price. We can calculate the percentage between product list price
>> and line unit price as line discount.
>> When changing unit price value the discount to the product list_price
>> is re-calculated.
>> When changing discount value, the unit price is re-calculated from
>> product list_price.
>> As an exception the unit price also can be an individual amount if
>> there is no product given. Without a product, there is no discount,
>> the discount field becomes invisible.
>Moreless. Probably the discount can be a functional field which should
>update the unit_price.
>But this will be better discusses as a blueprint on
>https://discuss.tryton.org/

Yes, lets move to:
https://discuss.tryton.org/t/discount-on-the-list-price-of-products/472

Thanks for comments!