Merge lp:~openerp-dev/openobject-addons/trunk-bug-748178-pso into lp:openobject-addons

[Priyesh (OpenERP)]

Fixed bug: 748178 (https://bugs.launchpad.net/openobject-addons/+bug/748178)
Improved invoice analysis report for computation of total without tax and removed average price from view

[Mustufa Rangwala (Open ERP) (mra-tinyerp)]

@qdp:

We have to check: FIXME the line total incl tax can't be calculated by deviding the invoice total / number of lines.

But we can merge this branch other things are working.

thanks,
mra

review: Approve

[qdp (OpenERP) (qdp)]

why did you removed average_price from view?

[Priyesh (OpenERP) (pso-openerp)]

Hello Quentin,

As the average price field has "group_operator = avg" so in group by environment that field value will be totaled / the no of lines in that group which will result in a wrong value. because that field already has the average value. so it shows sum(average_value) / no of lines. now if we remove the group_operator then by default it show the sum of the two lines which is also incorrect.

So If you want, can I add that price_average field in invoice analysis view ?

[qdp (OpenERP) (qdp)]

i remember the problem, :-)

it's cool like this

review: Approve

=== modified file 'account/report/account_invoice_report.py'
--- account/report/account_invoice_report.py	2011-02-01 14:25:28 +0000
+++ account/report/account_invoice_report.py	2011-04-13 08:55:51 +0000
@@ -69,6 +69,7 @@
         'address_contact_id': fields.many2one('res.partner.address', 'Contact Address Name', readonly=True),
         'address_invoice_id': fields.many2one('res.partner.address', 'Invoice Address Name', readonly=True),
         'account_id': fields.many2one('account.account', 'Account',readonly=True),
+        'account_line_id': fields.many2one('account.account', 'Account Line',readonly=True),
         'partner_bank_id': fields.many2one('res.partner.bank', 'Bank Account',readonly=True),
         'residual': fields.float('Total Residual', readonly=True),
         'delay_to_pay': fields.float('Avg. Delay To Pay', readonly=True, group_operator="avg"),
@@ -106,44 +107,48 @@
                     ai.address_contact_id as address_contact_id,
                     ai.address_invoice_id as address_invoice_id,
                     ai.account_id as account_id,
+                    ail.account_id as account_line_id,
                     ai.partner_bank_id as partner_bank_id,
                     sum(case when ai.type in ('out_refund','in_invoice') then
-                         ail.quantity / u.factor * -1
+                         -ail.quantity / u.factor
                         else
                          ail.quantity / u.factor
                         end) as product_qty,
+
                     sum(case when ai.type in ('out_refund','in_invoice') then
-                         ail.quantity*ail.price_unit * -1
+                         -ail.price_subtotal
                         else
-                         ail.quantity*ail.price_unit
+                          ail.price_subtotal
                         end) / cr.rate as price_total,
+
+--
+-- FIXME the line total incl tax can't be caculated by deviding the invoice total / number of lines
+--
                     sum(case when ai.type in ('out_refund','in_invoice') then
-                         ai.amount_total * -1
+                         -ai.amount_total
                         else
                          ai.amount_total
-                         end) / (CASE WHEN 
-                              (select count(l.id) from account_invoice_line as l
-                               left join account_invoice as a ON (a.id=l.invoice_id)
-                               where a.id=ai.id) <> 0 
-                            THEN 
-                              (select count(l.id) from account_invoice_line as l
-                               left join account_invoice as a ON (a.id=l.invoice_id)
-                               where a.id=ai.id) 
-                            ELSE 1 
+                         end) / (CASE WHEN
+                              (select count(l.id) from account_invoice_line as l
+                               left join account_invoice as a ON (a.id=l.invoice_id)
+                               where a.id=ai.id) <> 0
+                            THEN
+                              (select count(l.id) from account_invoice_line as l
+                               left join account_invoice as a ON (a.id=l.invoice_id)
+                               where a.id=ai.id)
+                            ELSE 1
                             END) / cr.rate as price_total_tax,
+
                     (case when ai.type in ('out_refund','in_invoice') then
-                      sum(ail.quantity*ail.price_unit*-1)
+                      sum(-ail.price_subtotal)
                     else
-                      sum(ail.quantity*ail.price_unit)
-                    end) / (CASE WHEN
-                         (case when ai.type in ('out_refund','in_invoice') 
-                          then sum(ail.quantity/u.factor*-1)
-                          else sum(ail.quantity/u.factor) end) <> 0 
-                       THEN 
-                         (case when ai.type in ('out_refund','in_invoice') 
-                          then sum(ail.quantity/u.factor*-1)
-                          else sum(ail.quantity/u.factor) end) 
-                       ELSE 1 
+                      sum(ail.price_subtotal)
+                    end) / (CASE WHEN sum(ail.quantity/u.factor) <> 0
+                       THEN
+                         (case when ai.type in ('out_refund','in_invoice')
+                          then sum(-ail.quantity/u.factor)
+                          else sum(ail.quantity/u.factor) end)
+                       ELSE 1
                        END)
                      / cr.rate as price_average,
 
@@ -159,22 +164,23 @@
                         left join account_invoice_line as l ON (a.id=l.invoice_id)
                         where a.id=ai.id)) as due_delay,
                     (case when ai.type in ('out_refund','in_invoice') then
-                      ai.residual * -1
+                      -ai.residual
                     else
                       ai.residual
-                    end)/ (CASE WHEN 
+                    end)/ (CASE WHEN
                         (select count(l.id) from account_invoice_line as l
                          left join account_invoice as a ON (a.id=l.invoice_id)
-                         where a.id=ai.id) <> 0 
+                         where a.id=ai.id) <> 0
                        THEN
                         (select count(l.id) from account_invoice_line as l
                          left join account_invoice as a ON (a.id=l.invoice_id)
-                         where a.id=ai.id) 
-                       ELSE 1 
+                         where a.id=ai.id)
+                       ELSE 1
                        END) / cr.rate as residual
                 from account_invoice_line as ail
                 left join account_invoice as ai ON (ai.id=ail.invoice_id)
-                left join product_template pt on (pt.id=ail.product_id)
+                left join product_product pr on (pr.id=ail.product_id)
+                left join product_template pt on (pt.id=pr.product_tmpl_id)
                 left join product_uom u on (u.id=ail.uos_id),
                 res_currency_rate cr
                 where cr.id in (select id from res_currency_rate cr2  where (cr2.currency_id = ai.currency_id)
@@ -202,6 +208,7 @@
                     ai.address_contact_id,
                     ai.address_invoice_id,
                     ai.account_id,
+                    ail.account_id,
                     ai.partner_bank_id,
                     ai.residual,
                     ai.amount_total,

=== modified file 'account/report/account_invoice_report_view.xml'
--- account/report/account_invoice_report_view.xml	2011-02-22 17:02:26 +0000
+++ account/report/account_invoice_report_view.xml	2011-04-13 08:55:51 +0000
@@ -27,10 +27,10 @@
                 <field name="partner_bank_id" invisible="1"/>
                 <field name="date_due" invisible="1"/>
                 <field name="account_id" invisible="1"/>
+                <field name="account_line_id" invisible="1"/>
                 <field name="nbr" sum="# of Lines"/>
                 <field name="product_qty" sum="Qty"/>
                 <!-- <field name="reconciled"  sum="# Reconciled"/> -->
-                <field name="price_average" sum="Average Price"/>
                 <field name="price_total" sum="Total Without Tax"/>
                 <field name="price_total_tax" sum="Total With Tax"/>
                 <field name="residual"  sum="Total Residual" invisible="context.get('residual_invisible',False)"/>
@@ -103,6 +103,7 @@
                     <separator orientation="vertical"/>
                     <field name="journal_id" widget="selection"/>
                     <field name="account_id"/>
+                    <field name="account_line_id"/>
                     <separator orientation="vertical"/>
                     <field name="date_due"/>
                     <separator orientation="vertical" groups="base.group_multi_company"/>
@@ -120,6 +121,7 @@
                     <filter string="Type" icon="terp-stock_symbol-selection" context="{'group_by':'type'}"/>
                     <separator orientation="vertical"/>
                     <filter string="Journal" icon="terp-folder-orange" context="{'group_by':'journal_id'}"/>
+                    <filter string="Account Line" icon="terp-folder-orange" context="{'group_by':'account_line_id'}"/>
                     <separator orientation="vertical"/>
                     <filter string="Due Date" icon="terp-go-today" context="{'group_by':'date_due'}"/>
                     <filter string="Period" icon="terp-go-month" context="{'group_by':'period_id'}"/>