report_invoice_utility/model/report_invoice_utility.py

# -*- coding: utf-8 -*-

from openerp import tools, models, fields, api
from openerp.osv import fields, osv

class report_invoice_utility(models.Model):

    _name = "report.invoice.utility"
    _description = "report.invoice.utility"
    _auto = False

    # _columns = {
    #     'id': fields.integer('id',required=True),
	# 	'product_id': fields.many2one('product.product', 'Product', required=True, select=True, readonly=True),
	# 	'quantity': fields.float('Cantidad', readonly=True),
    #     'brand_id': fields.many2one('product.brand', 'Marca', readonly=True),
    #     'genre_id': fields.many2one('product.genre', 'Genero', readonly=True),
    #     'categ_id': fields.many2one('product.category', 'Categoria', readonly=True),
	# 	'factory_reference': fields.char('Referencia', readonly=True),
	# 	'location_id': fields.many2one('stock.location', 'Ubicacion',  readonly=True),
	# 	'id_parent' : fields.many2one('product.category', 'Categoria Padre', readonly=True),
    # }
    _columns ={
                'id': fields.integer('id',required=True),
                'id_invoice' : fields.many2one('account.invoice', 'invoice',readonly=True),
                'type' : fields.char('type'),
                'number' : fields.char('number'),
                'origin' : fields.char('origin'),
                'state' : fields.char('state'),
                'journal_id' : fields.many2one('account.journal', 'journal', readonly=True),
                'currency_id' : fields.many2one('res.currency', 'currency', readonly=True),
                'rate' : fields.float('rate', readonly=True),
                'id_line' : fields.many2one('account.invoice.line', 'Line invoice', readonly=True),
                'quantity' : fields.float('quantity', readonly=True),
                'price_unit_original' : fields.float('price_unit_original', readonly=True),
                'price_unit' : fields.float('price_unit', readonly=True),
                'discount' : fields.float('discount', readonly=True),
                'name' : fields.char('name', readonly=True),
                'id_product' : fields.many2one('product.product', 'Product', readonly=True),
                'default_code' : fields.char('default_code', readonly=True),
                'name_template' : fields.char('name_template', readonly=True),
                'factory_code' : fields.char('factory_code', readonly=True),
                'factory_reference' : fields.char('factory_reference', readonly=True),
                'factory_barcode' : fields.char('factory_barcode', readonly=True),
                'standard_price' : fields.float('standard_price', readonly=True),
                'price_subtotal' : fields.float('price_subtotal', readonly=True),
                'cost_subtotal' : fields.float('cost_subtotal', readonly=True),
                'utilidad' : fields.float('utilidad', readonly=True),
    }

    _order = 'id asc'

    def init(self, cr):
		tools.sql.drop_view_if_exists(cr, 'report_invoice_utility')
		cr.execute("""
            CREATE OR REPLACE VIEW report_invoice_utility as (
            SELECT row_number() over (ORDER BY il.id)as id,
	               i.id as id_invoice,
                   i.type as type,
                   i.number as number,
                   i.origin as origin,
                   i.state as state,
                   i.journal_id as journal_id,
                   i.currency_id as currency_id,
                   rcr.rate as rate,
                   il.id as id_line,
                   il.quantity as quantity,
                   il.price_unit as price_unit_original,
                   trunc(il.price_unit / rcr.rate,2) as price_unit,
                   il.discount as discount,
                   il.name as name,
                   pp.id as id_product,
                   pp.default_code as default_code,
                   pp.name_template as name_template,
                   pp.factory_code as factory_code,
                   pp.factory_reference as factory_reference,
                   pp.factory_barcode as factory_barcode,
                   (pph.cost)::numeric as standard_price,
                   trunc(il.quantity * (trunc(il.price_unit / rcr.rate,2)),2) as price_subtotal,
                   trunc((il.quantity) * (pph.cost)::numeric ,2) as cost_subtotal,
                   (trunc(il.quantity * (trunc(il.price_unit / rcr.rate,2)),2)-trunc((il.quantity) * (pph.cost)::numeric ,2))as utilidad
            FROM account_invoice i
                  left join res_currency_rate rcr on(rcr.currency_id= i.currency_id)
 	              left join account_invoice_line il on (il.invoice_id =i.id)
 	              left join product_product pp on (pp.id =il.product_id)
 	              left join (SELECT cost, product_template_id FROM product_price_history ppa
 		               where create_date=(select max(create_date) FROM product_price_history pps where ppa.product_template_id = pps.product_template_id))
 		                pph on(pph.product_template_id = pp.product_tmpl_id)
            where trim(upper(i.type))=trim(upper('out_invoice')) AND (i.state='open' OR i.state='paid'))
		""")

# Select i.id as id_invoice, i.type as type,  i.number as number, i.origin as origin, i.state as state,
# 	il.id as id_line,  il.quantity as quantity, il.price_unit as price_unit, il.discount as discount, il.name as name,
# 	pp.id as id_product, pp.default_code as default_code, pp.name_template as name_template, pp.factory_code as factory_code,
# 	pp.factory_reference as factory_reference, pp.factory_barcode as factory_barcode, pp.product_tmpl_id,
# 	pph.cost as standard_price, pph.product_template_id as product_template_id
# FROM account_invoice i
# 	left join account_invoice_line il on (il.invoice_id =i.id)
# 	left join product_product pp on (pp.id =il.product_id)
# 	left join (SELECT cost, product_template_id FROM product_price_history ppa
# 		    where create_date=(select max(create_date) FROM product_price_history pps where ppa.product_template_id = pps.product_template_id))
# 		    pph on(pph.product_template_id = pp.product_tmpl_id)
# where i.type='out_invoice' and i.state='open' or i.state='paid'
# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4:

#
# def init(self, cr):
#     tools.sql.drop_view_if_exists(cr, 'report_invoice_utility')
#     cr.execute("""
#         CREATE OR REPLACE VIEW report_invoice_utility as (
#         SELECT	row_number() over (ORDER BY product_id)as id,
#                s.product_id as product_id,
#                SUM(s.qty) as quantity,
#                pt.product_brand_id as brand_id,
#                pt.product_genre_id as genre_id,
#                pt.categ_id as categ_id,
#                pt.factory_reference as factory_reference,
#                s.location_id as location_id,
#                pcc.id_parent as id_parent
#         FROM stock_quant s
#            left join stock_location r on (r.id=s.location_id)
#            left join product_product p on (p.id=s.product_id)
#            left join product_template pt on (pt.id=p.product_tmpl_id)
#            left join product_brand z on (pt.product_brand_id=z.id)
#            left join product_genre t on (pt.product_genre_id=t.id)
#            left join product_category g on (g.id=pt.categ_id)
#            left join (SELECT pp.id, pp.parent_id,
#                 case when (select ppp.parent_id from product_category ppp where ppp.id= pp.parent_id and ppp.parent_id !=1) is NULL
#                 then pp.parent_id  ELSE
#                 (select ppp.parent_id from product_category ppp where ppp.id= pp.parent_id and ppp.parent_id !=1) END as id_parent
#                 from product_category pp) as pcc on pcc.id = pt.categ_id
#             WHERE r.usage='internal' and  s.qty>0 and  pt.active=True
#             GROUP BY s.location_id, s.product_id, pt.product_brand_id, pt.product_genre_id, pt.factory_reference, pt.categ_id, pcc.id_parent)
#     """)