# -*- coding: utf-8 -*- # Part of Odoo. See LICENSE file for full copyright and licensing details. # # Please note that these reports are not multi-currency !!! # from odoo import fields, models, api class PurchaseReport(models.Model): _name = "purchase.report" _description = "Purchase Report" _auto = False _order = 'date_order desc, price_total desc' date_order = fields.Datetime('Order Date', readonly=True) state = fields.Selection([ ('draft', 'Draft RFQ'), ('sent', 'RFQ Sent'), ('to approve', 'To Approve'), ('purchase', 'Purchase Order'), ('done', 'Done'), ('cancel', 'Cancelled') ], 'Status', readonly=True) product_id = fields.Many2one('product.product', 'Product', readonly=True) partner_id = fields.Many2one('res.partner', 'Vendor', readonly=True) date_approve = fields.Datetime('Confirmation Date', readonly=True) product_uom = fields.Many2one('uom.uom', 'Reference Unit of Measure', required=True) company_id = fields.Many2one('res.company', 'Company', readonly=True) currency_id = fields.Many2one('res.currency', 'Currency', readonly=True) user_id = fields.Many2one('res.users', 'Purchase Representative', readonly=True) delay = fields.Float('Days to Confirm', digits=(16, 2), readonly=True, group_operator='avg', help="Amount of time between purchase approval and order by date.") delay_pass = fields.Float('Days to Receive', digits=(16, 2), readonly=True, group_operator='avg', help="Amount of time between date planned and order by date for each purchase order line.") price_total = fields.Float('Total', readonly=True) price_average = fields.Float('Average Cost', readonly=True, group_operator="avg", digits='Product Price') nbr_lines = fields.Integer('# of Lines', readonly=True) category_id = fields.Many2one('product.category', 'Product Category', readonly=True) product_tmpl_id = fields.Many2one('product.template', 'Product Template', readonly=True) country_id = fields.Many2one('res.country', 'Partner Country', readonly=True) fiscal_position_id = fields.Many2one('account.fiscal.position', string='Fiscal Position', readonly=True) commercial_partner_id = fields.Many2one('res.partner', 'Commercial Entity', readonly=True) weight = fields.Float('Gross Weight', readonly=True) volume = fields.Float('Volume', readonly=True) order_id = fields.Many2one('purchase.order', 'Order', readonly=True) untaxed_total = fields.Float('Untaxed Total', readonly=True) qty_ordered = fields.Float('Qty Ordered', readonly=True) qty_received = fields.Float('Qty Received', readonly=True) qty_billed = fields.Float('Qty Billed', readonly=True) qty_to_be_billed = fields.Float('Qty to be Billed', readonly=True) @property def _table_query(self): ''' Report needs to be dynamic to take into account multi-company selected + multi-currency rates ''' return '%s %s %s %s' % (self._select(), self._from(), self._where(), self._group_by()) def _select(self): select_str = """ SELECT po.id as order_id, min(l.id) as id, po.date_order as date_order, po.state, po.date_approve, po.dest_address_id, po.partner_id as partner_id, po.user_id as user_id, po.company_id as company_id, po.fiscal_position_id as fiscal_position_id, l.product_id, p.product_tmpl_id, t.categ_id as category_id, c.currency_id, t.uom_id as product_uom, extract(epoch from age(po.date_approve,po.date_order))/(24*60*60)::decimal(16,2) as delay, extract(epoch from age(l.date_planned,po.date_order))/(24*60*60)::decimal(16,2) as delay_pass, count(*) as nbr_lines, sum(l.price_total / COALESCE(po.currency_rate, 1.0))::decimal(16,2) * currency_table.rate as price_total, (sum(l.product_qty * l.price_unit / COALESCE(po.currency_rate, 1.0))/NULLIF(sum(l.product_qty/line_uom.factor*product_uom.factor),0.0))::decimal(16,2) * currency_table.rate as price_average, partner.country_id as country_id, partner.commercial_partner_id as commercial_partner_id, sum(p.weight * l.product_qty/line_uom.factor*product_uom.factor) as weight, sum(p.volume * l.product_qty/line_uom.factor*product_uom.factor) as volume, sum(l.price_subtotal / COALESCE(po.currency_rate, 1.0))::decimal(16,2) * currency_table.rate as untaxed_total, sum(l.product_qty / line_uom.factor * product_uom.factor) as qty_ordered, sum(l.qty_received / line_uom.factor * product_uom.factor) as qty_received, sum(l.qty_invoiced / line_uom.factor * product_uom.factor) as qty_billed, case when t.purchase_method = 'purchase' then sum(l.product_qty / line_uom.factor * product_uom.factor) - sum(l.qty_invoiced / line_uom.factor * product_uom.factor) else sum(l.qty_received / line_uom.factor * product_uom.factor) - sum(l.qty_invoiced / line_uom.factor * product_uom.factor) end as qty_to_be_billed """ return select_str def _from(self): from_str = """ FROM purchase_order_line l join purchase_order po on (l.order_id=po.id) join res_partner partner on po.partner_id = partner.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 res_company C ON C.id = po.company_id left join uom_uom line_uom on (line_uom.id=l.product_uom) left join uom_uom product_uom on (product_uom.id=t.uom_id) left join {currency_table} ON currency_table.company_id = po.company_id """.format( currency_table=self.env['res.currency']._get_query_currency_table(self.env.companies.ids, fields.Date.today()) ) return from_str def _where(self): return """ WHERE l.display_type IS NULL """ def _group_by(self): group_by_str = """ GROUP BY po.company_id, po.user_id, po.partner_id, line_uom.factor, c.currency_id, l.price_unit, po.date_approve, l.date_planned, l.product_uom, po.dest_address_id, po.fiscal_position_id, l.product_id, p.product_tmpl_id, t.categ_id, po.date_order, po.state, line_uom.uom_type, line_uom.category_id, t.uom_id, t.purchase_method, line_uom.id, product_uom.factor, partner.country_id, partner.commercial_partner_id, po.id, currency_table.rate """ return group_by_str @api.model def read_group(self, domain, fields, groupby, offset=0, limit=None, orderby=False, lazy=True): """ This is a hack to allow us to correctly calculate the average price of product. """ if 'price_average:avg' in fields: fields.extend(['aggregated_qty_ordered:array_agg(qty_ordered)']) fields.extend(['aggregated_price_average:array_agg(price_average)']) res = [] if fields: res = super(PurchaseReport, self).read_group(domain, fields, groupby, offset=offset, limit=limit, orderby=orderby, lazy=lazy) if 'price_average:avg' in fields: qties = 'aggregated_qty_ordered' special_field = 'aggregated_price_average' for data in res: if data[special_field] and data[qties]: total_unit_cost = sum(float(value) * float(qty) for value, qty in zip(data[special_field], data[qties]) if qty and value) total_qty_ordered = sum(float(qty) for qty in data[qties] if qty) data['price_average'] = (total_unit_cost / total_qty_ordered) if total_qty_ordered else 0 del data[special_field] del data[qties] return res