1# -*- coding: utf-8 -*- 2# Part of Odoo. See LICENSE file for full copyright and licensing details. 3 4# 5# Please note that these reports are not multi-currency !!! 6# 7 8import re 9 10from odoo import api, fields, models, tools 11from odoo.exceptions import UserError 12from odoo.osv.expression import AND, expression 13 14 15class PurchaseReport(models.Model): 16 _name = "purchase.report" 17 _description = "Purchase Report" 18 _auto = False 19 _order = 'date_order desc, price_total desc' 20 21 date_order = fields.Datetime('Order Date', readonly=True, help="Depicts the date when the Quotation should be validated and converted into a purchase order.") 22 state = fields.Selection([ 23 ('draft', 'Draft RFQ'), 24 ('sent', 'RFQ Sent'), 25 ('to approve', 'To Approve'), 26 ('purchase', 'Purchase Order'), 27 ('done', 'Done'), 28 ('cancel', 'Cancelled') 29 ], 'Status', readonly=True) 30 product_id = fields.Many2one('product.product', 'Product', readonly=True) 31 partner_id = fields.Many2one('res.partner', 'Vendor', readonly=True) 32 date_approve = fields.Datetime('Confirmation Date', readonly=True) 33 product_uom = fields.Many2one('uom.uom', 'Reference Unit of Measure', required=True) 34 company_id = fields.Many2one('res.company', 'Company', readonly=True) 35 currency_id = fields.Many2one('res.currency', 'Currency', readonly=True) 36 user_id = fields.Many2one('res.users', 'Purchase Representative', readonly=True) 37 delay = fields.Float('Days to Confirm', digits=(16, 2), readonly=True, help="Amount of time between purchase approval and order by date.") 38 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.") 39 avg_days_to_purchase = fields.Float( 40 'Average Days to Purchase', digits=(16, 2), readonly=True, store=False, # needs store=False to prevent showing up as a 'measure' option 41 help="Amount of time between purchase approval and document creation date. Due to a hack needed to calculate this, \ 42 every record will show the same average value, therefore only use this as an aggregated value with group_operator=avg") 43 price_total = fields.Float('Total', readonly=True) 44 price_average = fields.Float('Average Cost', readonly=True, group_operator="avg") 45 nbr_lines = fields.Integer('# of Lines', readonly=True) 46 category_id = fields.Many2one('product.category', 'Product Category', readonly=True) 47 product_tmpl_id = fields.Many2one('product.template', 'Product Template', readonly=True) 48 country_id = fields.Many2one('res.country', 'Partner Country', readonly=True) 49 fiscal_position_id = fields.Many2one('account.fiscal.position', string='Fiscal Position', readonly=True) 50 account_analytic_id = fields.Many2one('account.analytic.account', 'Analytic Account', readonly=True) 51 commercial_partner_id = fields.Many2one('res.partner', 'Commercial Entity', readonly=True) 52 weight = fields.Float('Gross Weight', readonly=True) 53 volume = fields.Float('Volume', readonly=True) 54 order_id = fields.Many2one('purchase.order', 'Order', readonly=True) 55 untaxed_total = fields.Float('Untaxed Total', readonly=True) 56 qty_ordered = fields.Float('Qty Ordered', readonly=True) 57 qty_received = fields.Float('Qty Received', readonly=True) 58 qty_billed = fields.Float('Qty Billed', readonly=True) 59 qty_to_be_billed = fields.Float('Qty to be Billed', readonly=True) 60 61 def init(self): 62 # self._table = sale_report 63 tools.drop_view_if_exists(self.env.cr, self._table) 64 self.env.cr.execute("""CREATE or REPLACE VIEW %s as ( 65 %s 66 FROM ( %s ) 67 %s 68 )""" % (self._table, self._select(), self._from(), self._group_by())) 69 70 def _select(self): 71 select_str = """ 72 WITH currency_rate as (%s) 73 SELECT 74 po.id as order_id, 75 min(l.id) as id, 76 po.date_order as date_order, 77 po.state, 78 po.date_approve, 79 po.dest_address_id, 80 po.partner_id as partner_id, 81 po.user_id as user_id, 82 po.company_id as company_id, 83 po.fiscal_position_id as fiscal_position_id, 84 l.product_id, 85 p.product_tmpl_id, 86 t.categ_id as category_id, 87 po.currency_id, 88 t.uom_id as product_uom, 89 extract(epoch from age(po.date_approve,po.date_order))/(24*60*60)::decimal(16,2) as delay, 90 extract(epoch from age(l.date_planned,po.date_order))/(24*60*60)::decimal(16,2) as delay_pass, 91 count(*) as nbr_lines, 92 sum(l.price_total / COALESCE(po.currency_rate, 1.0))::decimal(16,2) as price_total, 93 (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, 94 partner.country_id as country_id, 95 partner.commercial_partner_id as commercial_partner_id, 96 analytic_account.id as account_analytic_id, 97 sum(p.weight * l.product_qty/line_uom.factor*product_uom.factor) as weight, 98 sum(p.volume * l.product_qty/line_uom.factor*product_uom.factor) as volume, 99 sum(l.price_subtotal / COALESCE(po.currency_rate, 1.0))::decimal(16,2) as untaxed_total, 100 sum(l.product_qty / line_uom.factor * product_uom.factor) as qty_ordered, 101 sum(l.qty_received / line_uom.factor * product_uom.factor) as qty_received, 102 sum(l.qty_invoiced / line_uom.factor * product_uom.factor) as qty_billed, 103 case when t.purchase_method = 'purchase' 104 then sum(l.product_qty / line_uom.factor * product_uom.factor) - sum(l.qty_invoiced / line_uom.factor * product_uom.factor) 105 else sum(l.qty_received / line_uom.factor * product_uom.factor) - sum(l.qty_invoiced / line_uom.factor * product_uom.factor) 106 end as qty_to_be_billed 107 """ % self.env['res.currency']._select_companies_rates() 108 return select_str 109 110 def _from(self): 111 from_str = """ 112 purchase_order_line l 113 join purchase_order po on (l.order_id=po.id) 114 join res_partner partner on po.partner_id = partner.id 115 left join product_product p on (l.product_id=p.id) 116 left join product_template t on (p.product_tmpl_id=t.id) 117 left join uom_uom line_uom on (line_uom.id=l.product_uom) 118 left join uom_uom product_uom on (product_uom.id=t.uom_id) 119 left join account_analytic_account analytic_account on (l.account_analytic_id = analytic_account.id) 120 left join currency_rate cr on (cr.currency_id = po.currency_id and 121 cr.company_id = po.company_id and 122 cr.date_start <= coalesce(po.date_order, now()) and 123 (cr.date_end is null or cr.date_end > coalesce(po.date_order, now()))) 124 """ 125 return from_str 126 127 def _group_by(self): 128 group_by_str = """ 129 GROUP BY 130 po.company_id, 131 po.user_id, 132 po.partner_id, 133 line_uom.factor, 134 po.currency_id, 135 l.price_unit, 136 po.date_approve, 137 l.date_planned, 138 l.product_uom, 139 po.dest_address_id, 140 po.fiscal_position_id, 141 l.product_id, 142 p.product_tmpl_id, 143 t.categ_id, 144 po.date_order, 145 po.state, 146 line_uom.uom_type, 147 line_uom.category_id, 148 t.uom_id, 149 t.purchase_method, 150 line_uom.id, 151 product_uom.factor, 152 partner.country_id, 153 partner.commercial_partner_id, 154 analytic_account.id, 155 po.id 156 """ 157 return group_by_str 158 159 @api.model 160 def read_group(self, domain, fields, groupby, offset=0, limit=None, orderby=False, lazy=True): 161 """ This is a hack to allow us to correctly calculate the average of PO specific date values since 162 the normal report query result will duplicate PO values across its PO lines during joins and 163 lead to incorrect aggregation values. 164 165 Only the AVG operator is supported for avg_days_to_purchase. 166 """ 167 avg_days_to_purchase = next((field for field in fields if re.search(r'\bavg_days_to_purchase\b', field)), False) 168 169 if avg_days_to_purchase: 170 fields.remove(avg_days_to_purchase) 171 if any(field.split(':')[1].split('(')[0] != 'avg' for field in [avg_days_to_purchase] if field): 172 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.") 173 174 res = [] 175 if fields: 176 res = super(PurchaseReport, self).read_group(domain, fields, groupby, offset=offset, limit=limit, orderby=orderby, lazy=lazy) 177 178 if not res and avg_days_to_purchase: 179 res = [{}] 180 181 if avg_days_to_purchase: 182 self.check_access_rights('read') 183 query = """ SELECT AVG(days_to_purchase.po_days_to_purchase)::decimal(16,2) AS avg_days_to_purchase 184 FROM ( 185 SELECT extract(epoch from age(po.date_approve,po.create_date))/(24*60*60) AS po_days_to_purchase 186 FROM purchase_order po 187 WHERE po.id IN ( 188 SELECT "purchase_report"."order_id" FROM %s WHERE %s) 189 ) AS days_to_purchase 190 """ 191 192 subdomain = AND([domain, [('company_id', '=', self.env.company.id), ('date_approve', '!=', False)]]) 193 subtables, subwhere, subparams = expression(subdomain, self).query.get_sql() 194 195 self.env.cr.execute(query % (subtables, subwhere), subparams) 196 res[0].update({ 197 '__count': 1, 198 avg_days_to_purchase.split(':')[0]: self.env.cr.fetchall()[0][0], 199 }) 200 return res 201