From 3751379f1e9a4c215fb6eb898b4ccc67659b9ace Mon Sep 17 00:00:00 2001 From: stephanchrst Date: Tue, 10 May 2022 21:51:50 +0700 Subject: initial commit 2 --- addons/purchase/report/__init__.py | 5 + addons/purchase/report/purchase_bill.py | 64 +++++++ addons/purchase/report/purchase_bill_views.xml | 36 ++++ .../purchase/report/purchase_order_templates.xml | 153 ++++++++++++++++ .../report/purchase_quotation_templates.xml | 70 ++++++++ addons/purchase/report/purchase_report.py | 200 +++++++++++++++++++++ addons/purchase/report/purchase_report_views.xml | 72 ++++++++ addons/purchase/report/purchase_reports.xml | 28 +++ 8 files changed, 628 insertions(+) create mode 100644 addons/purchase/report/__init__.py create mode 100644 addons/purchase/report/purchase_bill.py create mode 100644 addons/purchase/report/purchase_bill_views.xml create mode 100644 addons/purchase/report/purchase_order_templates.xml create mode 100644 addons/purchase/report/purchase_quotation_templates.xml create mode 100644 addons/purchase/report/purchase_report.py create mode 100644 addons/purchase/report/purchase_report_views.xml create mode 100644 addons/purchase/report/purchase_reports.xml (limited to 'addons/purchase/report') diff --git a/addons/purchase/report/__init__.py b/addons/purchase/report/__init__.py new file mode 100644 index 00000000..427ed1d3 --- /dev/null +++ b/addons/purchase/report/__init__.py @@ -0,0 +1,5 @@ +# -*- coding: utf-8 -*- +# Part of Odoo. See LICENSE file for full copyright and licensing details. + +from . import purchase_report +from . import purchase_bill diff --git a/addons/purchase/report/purchase_bill.py b/addons/purchase/report/purchase_bill.py new file mode 100644 index 00000000..05e64fa8 --- /dev/null +++ b/addons/purchase/report/purchase_bill.py @@ -0,0 +1,64 @@ +# -*- coding: utf-8 -*- +# Part of Odoo. See LICENSE file for full copyright and licensing details. + +from odoo import api, fields, models, tools +from odoo.osv import expression +from odoo.tools import formatLang + +class PurchaseBillUnion(models.Model): + _name = 'purchase.bill.union' + _auto = False + _description = 'Purchases & Bills Union' + _order = "date desc, name desc" + + name = fields.Char(string='Reference', readonly=True) + reference = fields.Char(string='Source', readonly=True) + partner_id = fields.Many2one('res.partner', string='Vendor', readonly=True) + date = fields.Date(string='Date', readonly=True) + amount = fields.Float(string='Amount', readonly=True) + currency_id = fields.Many2one('res.currency', string='Currency', readonly=True) + company_id = fields.Many2one('res.company', 'Company', readonly=True) + vendor_bill_id = fields.Many2one('account.move', string='Vendor Bill', readonly=True) + purchase_order_id = fields.Many2one('purchase.order', string='Purchase Order', readonly=True) + + def init(self): + tools.drop_view_if_exists(self.env.cr, 'purchase_bill_union') + self.env.cr.execute(""" + CREATE OR REPLACE VIEW purchase_bill_union AS ( + SELECT + id, name, ref as reference, partner_id, date, amount_untaxed as amount, currency_id, company_id, + id as vendor_bill_id, NULL as purchase_order_id + FROM account_move + WHERE + move_type='in_invoice' and state = 'posted' + UNION + SELECT + -id, name, partner_ref as reference, partner_id, date_order::date as date, amount_untaxed as amount, currency_id, company_id, + NULL as vendor_bill_id, id as purchase_order_id + FROM purchase_order + WHERE + state in ('purchase', 'done') AND + invoice_status in ('to invoice', 'no') + )""") + + def name_get(self): + result = [] + for doc in self: + name = doc.name or '' + if doc.reference: + name += ' - ' + doc.reference + amount = doc.amount + if doc.purchase_order_id and doc.purchase_order_id.invoice_status == 'no': + amount = 0.0 + name += ': ' + formatLang(self.env, amount, monetary=True, currency_obj=doc.currency_id) + result.append((doc.id, name)) + return result + + @api.model + def _name_search(self, name, args=None, operator='ilike', limit=100, name_get_uid=None): + args = args or [] + domain = [] + if name: + domain = ['|', ('name', operator, name), ('reference', operator, name)] + purchase_bills_union_ids = self._search(expression.AND([domain, args]), limit=limit, access_rights_uid=name_get_uid) + return purchase_bills_union_ids diff --git a/addons/purchase/report/purchase_bill_views.xml b/addons/purchase/report/purchase_bill_views.xml new file mode 100644 index 00000000..5830837f --- /dev/null +++ b/addons/purchase/report/purchase_bill_views.xml @@ -0,0 +1,36 @@ + + + + + purchase.bill.union.select + purchase.bill.union + + + + + + + + + + + + + + + purchase.bill.union.tree + purchase.bill.union + + + + + + + + + + + + + + diff --git a/addons/purchase/report/purchase_order_templates.xml b/addons/purchase/report/purchase_order_templates.xml new file mode 100644 index 00000000..8ca91d26 --- /dev/null +++ b/addons/purchase/report/purchase_order_templates.xml @@ -0,0 +1,153 @@ + + + + + + diff --git a/addons/purchase/report/purchase_quotation_templates.xml b/addons/purchase/report/purchase_quotation_templates.xml new file mode 100644 index 00000000..958de3e1 --- /dev/null +++ b/addons/purchase/report/purchase_quotation_templates.xml @@ -0,0 +1,70 @@ + + + + + + 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 diff --git a/addons/purchase/report/purchase_report_views.xml b/addons/purchase/report/purchase_report_views.xml new file mode 100644 index 00000000..7ebcaf05 --- /dev/null +++ b/addons/purchase/report/purchase_report_views.xml @@ -0,0 +1,72 @@ + + + + product.month.pivot + purchase.report + + + + + + + + + + + product.month.graph + purchase.report + + + + + + + + + + report.purchase.order.search + purchase.report + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + Purchase Analysis + purchase.report + graph,pivot + + Purchase Analysis allows you to easily check and analyse your company purchase history and performance. From this menu you can track your negotiation performance, the delivery performance of your vendors, etc. + current + + + + + diff --git a/addons/purchase/report/purchase_reports.xml b/addons/purchase/report/purchase_reports.xml new file mode 100644 index 00000000..e44c3608 --- /dev/null +++ b/addons/purchase/report/purchase_reports.xml @@ -0,0 +1,28 @@ + + + + + Purchase Order + purchase.order + qweb-pdf + purchase.report_purchaseorder + purchase.report_purchaseorder + + (object.state in ('draft', 'sent') and 'Request for Quotation - %s' % (object.name) or + 'Purchase Order - %s' % (object.name)) + + report + + + + Request for Quotation + purchase.order + qweb-pdf + purchase.report_purchasequotation + purchase.report_purchasequotation + 'Request for Quotation - %s' % (object.name) + + report + + + -- cgit v1.2.3