# Part of Odoo. See LICENSE file for full copyright and licensing details. from odoo import fields, models, tools from odoo.fields import Domain from odoo.tools import SQL class VendorDelayReport(models.Model): _name = 'vendor.delay.report' _description = "Vendor Delay Report" _auto = False partner_id = fields.Many2one('res.partner', 'Vendor', readonly=True) product_id = fields.Many2one('product.product', 'Product', readonly=True) category_id = fields.Many2one('product.category', 'Product Category', readonly=True) date = fields.Datetime('Effective Date', readonly=True) qty_total = fields.Float('Total Quantity', readonly=True) qty_on_time = fields.Float('On-Time Quantity', readonly=True) on_time_rate = fields.Float('On-Time Delivery Rate', readonly=True) def init(self): tools.drop_view_if_exists(self.env.cr, 'vendor_delay_report') self.env.cr.execute(""" CREATE OR replace VIEW vendor_delay_report AS( SELECT pol.id AS id, Min(m.date) AS date, pol.id AS purchase_line_id, pol.product_id AS product_id, Min(pc.id) AS category_id, pol.partner_id AS partner_id, pol.product_uom_qty AS qty_total, Sum(CASE WHEN (m.state = 'done' and pol.date_planned::date >= m.date::date) THEN ((ml.quantity * ml_uom.factor) / pt_uom.factor) ELSE 0 END) AS qty_on_time FROM stock_move m JOIN purchase_order_line pol ON pol.id = m.purchase_line_id JOIN product_product p ON p.id = m.product_id JOIN product_template pt ON pt.id = p.product_tmpl_id JOIN uom_uom pt_uom ON pt_uom.id = pt.uom_id LEFT JOIN product_category pc ON pc.id = pt.categ_id LEFT JOIN stock_move_line ml ON ml.move_id = m.id LEFT JOIN uom_uom ml_uom ON ml_uom.id = ml.product_uom_id GROUP BY pol.id )""") def _read_group_select(self, aggregate_spec, query): if aggregate_spec == 'on_time_rate:sum': # Make a weigthed average instead of simple average for these fields return SQL( 'CASE WHEN SUM(%s) !=0 THEN SUM(%s) / SUM(%s) * 100 ELSE 100 END', self._field_to_sql(self._table, 'qty_total', query), self._field_to_sql(self._table, 'qty_on_time', query), self._field_to_sql(self._table, 'qty_total', query), ) return super()._read_group_select(aggregate_spec, query) def _read_group(self, domain, groupby=(), aggregates=(), having=(), offset=0, limit=None, order=None): if 'on_time_rate:sum' in aggregates: having = Domain.AND([having, [('qty_total:sum', '>', '0')]]) return super()._read_group(domain, groupby, aggregates, having, offset, limit, order)