# -*- 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) # """)