Rifakat (OpenERP) has proposed merging
lp:~openerp-dev/openobject-addons/6.0-opw-50851-rha into
lp:openobject-addons/6.0.
Requested reviews:
Priyesh (OpenERP) (pso-openerp)
For more details, see:
https://code.launchpad.net/~openerp-dev/openobject-addons/6.0-opw-50851-rha/+merge/84714
Hello,
Improved query for sale_report which returns more than one row for having more
than one reference UoM per UoM category.
Backported from Trunk revision 5162.
Thanks,
Rifakat
--
https://code.launchpad.net/~openerp-dev/openobject-addons/6.0-opw-50851-rha/+merge/84714
Your team OpenERP R&D Team is subscribed to branch
lp:~openerp-dev/openobject-addons/6.0-opw-50851-rha.
=== modified file 'sale/report/sale_report.py'
--- sale/report/sale_report.py 2011-09-22 05:29:56 +0000
+++ sale/report/sale_report.py 2011-12-07 05:01:30 +0000
@@ -38,7 +38,7 @@
('10', 'October'), ('11', 'November'), ('12', 'December')], 'Month', readonly=True),
'day': fields.char('Day', size=128, readonly=True),
'product_id': fields.many2one('product.product', 'Product', readonly=True),
- 'uom_name': fields.char('Reference UoM', size=128, readonly=True),
+ 'product_uom': fields.many2one('product.uom', 'UoM', readonly=True),
'product_uom_qty': fields.float('# of Qty', readonly=True),
'partner_id': fields.many2one('res.partner', 'Partner', readonly=True),
@@ -67,51 +67,42 @@
tools.drop_view_if_exists(cr, 'sale_report')
cr.execute("""
create or replace view sale_report as (
- select el.*,
- -- (select count(1) from sale_order_line where order_id = s.id) as nbr,
- (select 1) as nbr,
- s.date_order as date,
- s.date_confirm as date_confirm,
- to_char(s.date_order, 'YYYY') as year,
- to_char(s.date_order, 'MM') as month,
- to_char(s.date_order, 'YYYY-MM-DD') as day,
- s.partner_id as partner_id,
- s.user_id as user_id,
- s.shop_id as shop_id,
- s.company_id as company_id,
- extract(epoch from avg(date_trunc('day',s.date_confirm)-date_trunc('day',s.create_date)))/(24*60*60)::decimal(16,2) as delay,
- s.state,
- s.shipped,
- s.shipped::integer as shipped_qty_1,
- s.pricelist_id as pricelist_id,
- s.project_id as analytic_account_id
+ select
+ min(l.id) as id,
+ l.product_id as product_id,
+ t.uom_id as product_uom,
+ sum(l.product_uom_qty / u.factor * u2.factor) as product_uom_qty,
+ sum(l.product_uom_qty * l.price_unit * (100.0-l.discount) / 100.0) as price_total,
+ 1 as nbr,
+ s.date_order as date,
+ s.date_confirm as date_confirm,
+ to_char(s.date_order, 'YYYY') as year,
+ to_char(s.date_order, 'MM') as month,
+ to_char(s.date_order, 'YYYY-MM-DD') as day,
+ s.partner_id as partner_id,
+ s.user_id as user_id,
+ s.shop_id as shop_id,
+ s.company_id as company_id,
+ extract(epoch from avg(date_trunc('day',s.date_confirm)-date_trunc('day',s.create_date)))/(24*60*60)::decimal(16,2) as delay,
+ s.state,
+ t.categ_id as categ_id,
+ s.shipped,
+ s.shipped::integer as shipped_qty_1,
+ s.pricelist_id as pricelist_id,
+ s.project_id as analytic_account_id
from
- sale_order s,
- (
- select l.id as id,
- l.product_id as product_id,
- (case when u.uom_type not in ('reference') then
- (select name from product_uom where uom_type='reference' and category_id=u.category_id and active LIMIT 1)
- else
- u.name
- end) as uom_name,
- sum(l.product_uom_qty / u.factor) as product_uom_qty,
- sum(l.product_uom_qty * l.price_unit) as price_total,
- pt.categ_id, l.order_id
- from
- sale_order_line l ,product_uom u, product_product p, product_template pt
- where u.id = l.product_uom
- and pt.id = p.product_tmpl_id
- and p.id = l.product_id
- group by l.id, l.order_id, l.product_id, u.name, pt.categ_id, u.uom_type, u.category_id) el
- where s.id = el.order_id
- group by el.id,
- el.product_id,
- el.uom_name,
- el.product_uom_qty,
- el.price_total,
- el.categ_id,
- el.order_id,
+ sale_order s
+ left join sale_order_line l on (s.id=l.order_id)
+ left join product_product p on (l.product_id=p.id)
+ left join product_template t on (p.product_tmpl_id=t.id)
+ left join product_uom u on (u.id=l.product_uom)
+ left join product_uom u2 on (u2.id=t.uom_id)
+ group by
+ l.product_id,
+ l.product_uom_qty,
+ l.order_id,
+ t.uom_id,
+ t.categ_id,
s.date_order,
s.date_confirm,
s.partner_id,
=== modified file 'sale/report/sale_report_view.xml'
--- sale/report/sale_report_view.xml 2011-01-14 00:11:01 +0000
+++ sale/report/sale_report_view.xml 2011-12-07 05:01:30 +0000
@@ -22,7 +22,7 @@
<field name="nbr" sum="# of Lines"/>
<field name="product_uom_qty" sum="# of Qty"/>
<field name="shipped_qty_1" sum="Shipped Qty"/>
- <field name="uom_name" invisible="not context.get('set_visible',False)"/>
+ <field name="product_uom" invisible="not context.get('set_visible',False)"/>
<field name="price_total" sum="Total Price"/>
<field name="delay" sum="Commitment Delay"/>
<field name="state" invisible="1"/>
@@ -99,7 +99,7 @@
<filter string="Partner" icon="terp-partner" name="Customer" context="{'group_by':'partner_id'}"/>
<separator orientation="vertical"/>
<filter string="Product" icon="terp-accessories-archiver" context="{'group_by':'product_id','set_visible':True}"/>
- <filter string="Reference UoM" icon="terp-mrp" context="{'group_by':'uom_name'}"/>
+ <filter string="Reference UoM" icon="terp-mrp" context="{'group_by':'product_uom'}"/>
<filter string="Category of Product" icon="terp-stock_symbol-selection" name="Category" context="{'group_by':'categ_id'}"/>
<separator orientation="vertical" groups="analytic.group_analytic_accounting"/>
<filter string="Analytic Account" icon="terp-folder-green" context="{'group_by':'analytic_account_id'}" groups="analytic.group_analytic_accounting"/>
@@ -209,7 +209,7 @@
<field name="price_total" />
<field name="user_id" invisible="1"/>
<field name="partner_id" invisible="1"/>
- <field name="uom_name" invisible="1"/>
+ <field name="product_uom" invisible="1"/>
<field name="categ_id" invisible="1"/>
<field name="analytic_account_id" invisible="1" groups="analytic.group_analytic_accounting"/>
<field name="state" invisible="1"/>
_______________________________________________
Mailing list: https://launchpad.net/~openerp-dev-gtk
Post to : [email protected]
Unsubscribe : https://launchpad.net/~openerp-dev-gtk
More help : https://help.launchpad.net/ListHelp
