# -*- encoding: utf-8 -*-
###############################################################################
#                                                                             #
# Copyright (C) 2011  Renato Lima - Akretion                                  #
#                                                                             #
#This program is free software: you can redistribute it and/or modify         #
#it under the terms of the GNU Affero General Public License as published by  #
#the Free Software Foundation, either version 3 of the License, or            #
#(at your option) any later version.                                          #
#                                                                             #
#This program is distributed in the hope that it will be useful,              #
#but WITHOUT ANY WARRANTY; without even the implied warranty of               #
#MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the                #
#GNU General Public License for more details.                                 #
#                                                                             #
#You should have received a copy of the GNU General Public License            #
#along with this program.  If not, see <http://www.gnu.org/licenses/>.        #
###############################################################################

from openerp import tools
from openerp.osv import orm, fields


class sale_report(orm.Model):
    _inherit = "sale.report"
    _columns = {
        'fiscal_category_id': fields.many2one(
            'l10n_br_account.fiscal.category', 'Fiscal Category',
            readonly=True),
        'fiscal_position': fields.many2one(
            'account.fiscal.position', 'Fiscal Position', readonly=True)
    }

    def init(self, cr):
        tools.drop_view_if_exists(cr, 'sale_report')
        cr.execute("""create or replace view sale_report as (
                select
                    min(l.id) as id,
                    l.product_id as product_id,
                    l.fiscal_category_id as fiscal_category_id,
                    l.fiscal_position as fiscal_position,
                    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.pricelist_id as pricelist_id,
                    s.project_id as analytic_account_id
                from
                    sale_order s
                    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.fiscal_category_id,
                    l.fiscal_position,
                    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,
                    s.user_id,
                    s.shop_id,
                    s.company_id,
                    s.state,
                    s.pricelist_id,
                    s.project_id)
        """)

@@ -1797,21 +1797,20 @@
 def 
-ini
+_selec
 t(self
-, cr
 ):%0A 
@@ -1820,157 +1820,44 @@
     
-tools.drop_view_if_exists(cr, 'sale_report')%0A        cr.execute(%22%22%22create or replace view sale_report as (%0A                select%0A                   
+select_str = %22%22%22%0A             SELECT
  min
@@ -1910,32 +1910,77 @@
  as product_id,%0A
+                    t.uom_id as product_uom,%0A
                 
@@ -2089,53 +2089,8 @@
 on,%0A
-                    t.uom_id as product_uom,%0A
     
@@ -2294,17 +2294,24 @@
         
-1
+count(*)
  as nbr,
@@ -2409,189 +2409,8 @@
 rm,%0A
-                    to_char(s.date_order, 'YYYY') as year,%0A                    to_char(s.date_order, 'MM') as month,%0A                    to_char(s.date_order, 'YYYY-MM-DD') as day,%0A
     
@@ -2499,50 +2499,8 @@
 id,%0A
-                    s.shop_id as shop_id,%0A
     
@@ -2882,435 +2882,125 @@
     
-        from%0A                    sale_order s%0A                    join sale_order_line l on (s.id=l.order_id)%0A                        left join product_product p on (l.product_id=p.id)%0A                            left join product_template t on (p.product_tmpl_id=t.id)%0A                    left join product_uom u on (u.id=l.product_uom)%0A                    left join product_uom u2 on (u2.id=t.uom_id)%0A                group by
+%22%22%22%0A        return select_str%0A%0A    def _group_by(self):%0A        group_by_str = %22%22%22%0A            GROUP BY l.product_id,
 %0A   
@@ -3081,81 +3081,8 @@
 on,%0A
-                    l.product_id,%0A                    l.product_uom_qty,%0A
     
@@ -3310,39 +3310,8 @@
 id,%0A
-                    s.shop_id,%0A
     
@@ -3437,17 +3437,16 @@
 oject_id
-)
 %0A       
@@ -3449,10 +3449,37 @@
      %22%22%22
-)
+%0A        return group_by_str
 %0A

migrate sale.report