diff options
Diffstat (limited to 'addons/purchase/report/purchase_report.py')
| -rw-r--r-- | addons/purchase/report/purchase_report.py | 200 |
1 files changed, 200 insertions, 0 deletions
diff --git a/addons/purchase/report/purchase_report.py b/addons/purchase/report/purchase_report.py new file mode 100644 index 00000000..46cbb555 --- /dev/null +++ b/addons/purchase/report/purchase_report.py @@ -0,0 +1,200 @@ +# -*- coding: utf-8 -*- +# Part of Odoo. See LICENSE file for full copyright and licensing details. + +# +# Please note that these reports are not multi-currency !!! +# + +import re + +from odoo import api, fields, models, tools +from odoo.exceptions import UserError +from odoo.osv.expression import AND, expression + + +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, help="Depicts the date when the Quotation should be validated and converted into a purchase order.") + 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, help="Amount of time between purchase approval and order by date.") + delay_pass = fields.Float('Days to Receive', digits=(16, 2), readonly=True, help="Amount of time between date planned and order by date for each purchase order line.") + avg_days_to_purchase = fields.Float( + 'Average Days to Purchase', digits=(16, 2), readonly=True, store=False, # needs store=False to prevent showing up as a 'measure' option + help="Amount of time between purchase approval and document creation date. Due to a hack needed to calculate this, \ + every record will show the same average value, therefore only use this as an aggregated value with group_operator=avg") + price_total = fields.Float('Total', readonly=True) + price_average = fields.Float('Average Cost', readonly=True, group_operator="avg") + 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) + account_analytic_id = fields.Many2one('account.analytic.account', 'Analytic Account', 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) + + def init(self): + # self._table = sale_report + tools.drop_view_if_exists(self.env.cr, self._table) + self.env.cr.execute("""CREATE or REPLACE VIEW %s as ( + %s + FROM ( %s ) + %s + )""" % (self._table, self._select(), self._from(), self._group_by())) + + def _select(self): + select_str = """ + WITH currency_rate as (%s) + 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, + po.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) 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) as price_average, + partner.country_id as country_id, + partner.commercial_partner_id as commercial_partner_id, + analytic_account.id as account_analytic_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) 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 + """ % self.env['res.currency']._select_companies_rates() + return select_str + + def _from(self): + from_str = """ + 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 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 account_analytic_account analytic_account on (l.account_analytic_id = analytic_account.id) + left join currency_rate cr on (cr.currency_id = po.currency_id and + cr.company_id = po.company_id and + cr.date_start <= coalesce(po.date_order, now()) and + (cr.date_end is null or cr.date_end > coalesce(po.date_order, now()))) + """ + return from_str + + def _group_by(self): + group_by_str = """ + GROUP BY + po.company_id, + po.user_id, + po.partner_id, + line_uom.factor, + po.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, + analytic_account.id, + po.id + """ + 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 of PO specific date values since + the normal report query result will duplicate PO values across its PO lines during joins and + lead to incorrect aggregation values. + + Only the AVG operator is supported for avg_days_to_purchase. + """ + avg_days_to_purchase = next((field for field in fields if re.search(r'\bavg_days_to_purchase\b', field)), False) + + if avg_days_to_purchase: + fields.remove(avg_days_to_purchase) + if any(field.split(':')[1].split('(')[0] != 'avg' for field in [avg_days_to_purchase] if field): + raise UserError("Value: 'avg_days_to_purchase' should only be used to show an average. If you are seeing this message then it is being accessed incorrectly.") + + res = [] + if fields: + res = super(PurchaseReport, self).read_group(domain, fields, groupby, offset=offset, limit=limit, orderby=orderby, lazy=lazy) + + if not res and avg_days_to_purchase: + res = [{}] + + if avg_days_to_purchase: + self.check_access_rights('read') + query = """ SELECT AVG(days_to_purchase.po_days_to_purchase)::decimal(16,2) AS avg_days_to_purchase + FROM ( + SELECT extract(epoch from age(po.date_approve,po.create_date))/(24*60*60) AS po_days_to_purchase + FROM purchase_order po + WHERE po.id IN ( + SELECT "purchase_report"."order_id" FROM %s WHERE %s) + ) AS days_to_purchase + """ + + subdomain = AND([domain, [('company_id', '=', self.env.company.id), ('date_approve', '!=', False)]]) + subtables, subwhere, subparams = expression(subdomain, self).query.get_sql() + + self.env.cr.execute(query % (subtables, subwhere), subparams) + res[0].update({ + '__count': 1, + avg_days_to_purchase.split(':')[0]: self.env.cr.fetchall()[0][0], + }) + return res |