# Part of Odoo. See LICENSE file for full copyright and licensing details. from odoo import api, fields, models from odoo.addons.sale.models.sale_order import SALE_ORDER_STATE class SaleReport(models.Model): _name = "sale.report" _description = "Sales Analysis Report" _auto = False _rec_name = 'date' _order = 'date desc' @api.model def _get_done_states(self): return ['sale'] # sale.order fields name = fields.Char(string="Order Reference", readonly=True) date = fields.Datetime(string="Order Date", readonly=True) partner_id = fields.Many2one(comodel_name='res.partner', string="Customer", readonly=True) company_id = fields.Many2one(comodel_name='res.company', readonly=True) pricelist_id = fields.Many2one(comodel_name='product.pricelist', readonly=True) team_id = fields.Many2one(comodel_name='crm.team', string="Sales Team", readonly=True) user_id = fields.Many2one(comodel_name='res.users', string="Salesperson", readonly=True) state = fields.Selection(selection=SALE_ORDER_STATE, string="Status", readonly=True) analytic_account_id = fields.Many2one( comodel_name='account.analytic.account', string="Analytic Account", readonly=True) invoice_status = fields.Selection( selection=[ ('upselling', "Upselling Opportunity"), ('invoiced', "Fully Invoiced"), ('to invoice', "To Invoice"), ('no', "Nothing to Invoice"), ], string="Invoice Status", readonly=True) campaign_id = fields.Many2one(comodel_name='utm.campaign', string="Campaign", readonly=True) medium_id = fields.Many2one(comodel_name='utm.medium', string="Medium", readonly=True) source_id = fields.Many2one(comodel_name='utm.source', string="Source", readonly=True) # res.partner fields commercial_partner_id = fields.Many2one( comodel_name='res.partner', string="Customer Entity", readonly=True) country_id = fields.Many2one( comodel_name='res.country', string="Customer Country", readonly=True) industry_id = fields.Many2one( comodel_name='res.partner.industry', string="Customer Industry", readonly=True) partner_zip = fields.Char(string="Customer ZIP", readonly=True) state_id = fields.Many2one(comodel_name='res.country.state', string="Customer State", readonly=True) # sale.order.line fields order_reference = fields.Reference(string='Related Order', selection=[('sale.order', 'Sales Order')], group_operator="count_distinct") categ_id = fields.Many2one( comodel_name='product.category', string="Product Category", readonly=True) product_id = fields.Many2one( comodel_name='product.product', string="Product Variant", readonly=True) product_tmpl_id = fields.Many2one( comodel_name='product.template', string="Product", readonly=True) product_uom = fields.Many2one(comodel_name='uom.uom', string="Unit of Measure", readonly=True) product_uom_qty = fields.Float(string="Qty Ordered", readonly=True) qty_to_deliver = fields.Float(string="Qty To Deliver", readonly=True) qty_delivered = fields.Float(string="Qty Delivered", readonly=True) qty_to_invoice = fields.Float(string="Qty To Invoice", readonly=True) qty_invoiced = fields.Float(string="Qty Invoiced", readonly=True) price_subtotal = fields.Monetary(string="Untaxed Total", readonly=True) price_total = fields.Monetary(string="Total", readonly=True) untaxed_amount_to_invoice = fields.Monetary(string="Untaxed Amount To Invoice", readonly=True) untaxed_amount_invoiced = fields.Monetary(string="Untaxed Amount Invoiced", readonly=True) weight = fields.Float(string="Gross Weight", readonly=True) volume = fields.Float(string="Volume", readonly=True) discount = fields.Float(string="Discount %", readonly=True, group_operator='avg') discount_amount = fields.Monetary(string="Discount Amount", readonly=True) # aggregates or computed fields nbr = fields.Integer(string="# of Lines", readonly=True) currency_id = fields.Many2one(comodel_name='res.currency', compute='_compute_currency_id') @api.depends_context('allowed_company_ids') def _compute_currency_id(self): self.currency_id = self.env.company.currency_id def _with_sale(self): return "" def _select_sale(self): select_ = f""" MIN(l.id) AS id, l.product_id AS product_id, t.uom_id AS product_uom, CASE WHEN l.product_id IS NOT NULL THEN SUM(l.product_uom_qty / u.factor * u2.factor) ELSE 0 END AS product_uom_qty, CASE WHEN l.product_id IS NOT NULL THEN SUM(l.qty_delivered / u.factor * u2.factor) ELSE 0 END AS qty_delivered, CASE WHEN l.product_id IS NOT NULL THEN SUM((l.product_uom_qty - l.qty_delivered) / u.factor * u2.factor) ELSE 0 END AS qty_to_deliver, CASE WHEN l.product_id IS NOT NULL THEN SUM(l.qty_invoiced / u.factor * u2.factor) ELSE 0 END AS qty_invoiced, CASE WHEN l.product_id IS NOT NULL THEN SUM(l.qty_to_invoice / u.factor * u2.factor) ELSE 0 END AS qty_to_invoice, CASE WHEN l.product_id IS NOT NULL THEN SUM(l.price_total / {self._case_value_or_one('s.currency_rate')} * {self._case_value_or_one('currency_table.rate')} ) ELSE 0 END AS price_total, CASE WHEN l.product_id IS NOT NULL THEN SUM(l.price_subtotal / {self._case_value_or_one('s.currency_rate')} * {self._case_value_or_one('currency_table.rate')} ) ELSE 0 END AS price_subtotal, CASE WHEN l.product_id IS NOT NULL THEN SUM(l.untaxed_amount_to_invoice / {self._case_value_or_one('s.currency_rate')} * {self._case_value_or_one('currency_table.rate')} ) ELSE 0 END AS untaxed_amount_to_invoice, CASE WHEN l.product_id IS NOT NULL THEN SUM(l.untaxed_amount_invoiced / {self._case_value_or_one('s.currency_rate')} * {self._case_value_or_one('currency_table.rate')} ) ELSE 0 END AS untaxed_amount_invoiced, COUNT(*) AS nbr, s.name AS name, s.date_order AS date, s.state AS state, s.invoice_status as invoice_status, s.partner_id AS partner_id, s.user_id AS user_id, s.company_id AS company_id, s.campaign_id AS campaign_id, s.medium_id AS medium_id, s.source_id AS source_id, t.categ_id AS categ_id, s.pricelist_id AS pricelist_id, s.analytic_account_id AS analytic_account_id, s.team_id AS team_id, p.product_tmpl_id, partner.commercial_partner_id AS commercial_partner_id, partner.country_id AS country_id, partner.industry_id AS industry_id, partner.state_id AS state_id, partner.zip AS partner_zip, CASE WHEN l.product_id IS NOT NULL THEN SUM(p.weight * l.product_uom_qty / u.factor * u2.factor) ELSE 0 END AS weight, CASE WHEN l.product_id IS NOT NULL THEN SUM(p.volume * l.product_uom_qty / u.factor * u2.factor) ELSE 0 END AS volume, l.discount AS discount, CASE WHEN l.product_id IS NOT NULL THEN SUM(l.price_unit * l.product_uom_qty * l.discount / 100.0 / {self._case_value_or_one('s.currency_rate')} * {self._case_value_or_one('currency_table.rate')} ) ELSE 0 END AS discount_amount, concat('sale.order', ',', s.id) AS order_reference""" additional_fields_info = self._select_additional_fields() template = """, %s AS %s""" for fname, query_info in additional_fields_info.items(): select_ += template % (query_info, fname) return select_ def _case_value_or_one(self, value): return f"""CASE COALESCE({value}, 0) WHEN 0 THEN 1.0 ELSE {value} END""" def _select_additional_fields(self): """Hook to return additional fields SQL specification for select part of the table query. :returns: mapping field -> SQL computation of field, will be converted to '_ AS _field' in the final table definition :rtype: dict """ return {} def _from_sale(self): return """ sale_order_line l LEFT JOIN sale_order s ON s.id=l.order_id JOIN res_partner partner ON s.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 u ON u.id=l.product_uom LEFT JOIN uom_uom u2 ON u2.id=t.uom_id JOIN {currency_table} ON currency_table.company_id = s.company_id """.format( currency_table=self.env['res.currency']._get_query_currency_table(self.env.companies.ids, fields.Date.today()) ) def _where_sale(self): return """ l.display_type IS NULL""" def _group_by_sale(self): return """ l.product_id, l.order_id, t.uom_id, t.categ_id, s.name, s.date_order, s.partner_id, s.user_id, s.state, s.invoice_status, s.company_id, s.campaign_id, s.medium_id, s.source_id, s.pricelist_id, s.analytic_account_id, s.team_id, p.product_tmpl_id, partner.commercial_partner_id, partner.country_id, partner.industry_id, partner.state_id, partner.zip, l.discount, s.id, currency_table.rate""" def _query(self): with_ = self._with_sale() return f""" {"WITH" + with_ + "(" if with_ else ""} SELECT {self._select_sale()} FROM {self._from_sale()} WHERE {self._where_sale()} GROUP BY {self._group_by_sale()} {")" if with_ else ""} """ @property def _table_query(self): return self._query()