# -*- coding: utf-8 -*- # Part of Odoo. See LICENSE file for full copyright and licensing details. from odoo import tools from odoo import api, fields, models 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', 'done', 'paid'] name = fields.Char('Order Reference', readonly=True) date = fields.Datetime('Order Date', readonly=True) product_id = fields.Many2one('product.product', 'Product Variant', readonly=True) product_uom = fields.Many2one('uom.uom', 'Unit of Measure', readonly=True) product_uom_qty = fields.Float('Qty Ordered', readonly=True) qty_to_deliver = fields.Float('Qty To Deliver', readonly=True) qty_delivered = fields.Float('Qty Delivered', readonly=True) qty_to_invoice = fields.Float('Qty To Invoice', readonly=True) qty_invoiced = fields.Float('Qty Invoiced', readonly=True) partner_id = fields.Many2one('res.partner', 'Customer', readonly=True) company_id = fields.Many2one('res.company', 'Company', readonly=True) user_id = fields.Many2one('res.users', 'Salesperson', readonly=True) price_total = fields.Float('Total', readonly=True) price_subtotal = fields.Float('Untaxed Total', readonly=True) untaxed_amount_to_invoice = fields.Float('Untaxed Amount To Invoice', readonly=True) untaxed_amount_invoiced = fields.Float('Untaxed Amount Invoiced', readonly=True) product_tmpl_id = fields.Many2one('product.template', 'Product', readonly=True) categ_id = fields.Many2one('product.category', 'Product Category', readonly=True) nbr = fields.Integer('# of Lines', readonly=True) pricelist_id = fields.Many2one('product.pricelist', 'Pricelist', readonly=True) analytic_account_id = fields.Many2one('account.analytic.account', 'Analytic Account', readonly=True) team_id = fields.Many2one('crm.team', 'Sales Team', readonly=True) country_id = fields.Many2one('res.country', 'Customer Country', readonly=True) industry_id = fields.Many2one('res.partner.industry', 'Customer Industry', readonly=True) commercial_partner_id = fields.Many2one('res.partner', 'Customer Entity', readonly=True) state = fields.Selection([ ('draft', 'Draft Quotation'), ('sent', 'Quotation Sent'), ('sale', 'Sales Order'), ('done', 'Sales Done'), ('cancel', 'Cancelled'), ], string='Status', readonly=True) weight = fields.Float('Gross Weight', readonly=True) volume = fields.Float('Volume', readonly=True) discount = fields.Float('Discount %', readonly=True) discount_amount = fields.Float('Discount Amount', readonly=True) campaign_id = fields.Many2one('utm.campaign', 'Campaign') medium_id = fields.Many2one('utm.medium', 'Medium') source_id = fields.Many2one('utm.source', 'Source') order_id = fields.Many2one('sale.order', 'Order #', readonly=True) def _select_sale(self, fields=None): if not fields: fields = {} select_ = """ 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 / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) ELSE 0 END as price_total, CASE WHEN l.product_id IS NOT NULL THEN sum(l.price_subtotal / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) ELSE 0 END as price_subtotal, CASE WHEN l.product_id IS NOT NULL THEN sum(l.untaxed_amount_to_invoice / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) ELSE 0 END as untaxed_amount_to_invoice, CASE WHEN l.product_id IS NOT NULL THEN sum(l.untaxed_amount_invoiced / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) ELSE 0 END as untaxed_amount_invoiced, count(*) as nbr, s.name as name, s.date_order as date, s.state as state, 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, extract(epoch from avg(date_trunc('day',s.date_order)-date_trunc('day',s.create_date)))/(24*60*60)::decimal(16,2) as delay, 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.country_id as country_id, partner.industry_id as industry_id, partner.commercial_partner_id as commercial_partner_id, 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 / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END))ELSE 0 END as discount_amount, s.id as order_id """ for field in fields.values(): select_ += field return select_ def _from_sale(self, from_clause=''): from_ = """ 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) left join product_pricelist pp on (s.pricelist_id = pp.id) %s """ % from_clause return from_ def _group_by_sale(self, groupby=''): groupby_ = """ 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.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.country_id, partner.industry_id, partner.commercial_partner_id, l.discount, s.id %s """ % (groupby) return groupby_ def _select_additional_fields(self, fields): """Hook to return additional fields SQL specification for select part of the table query. :param dict fields: additional fields info provided by _query overrides (old API), prefer overriding _select_additional_fields instead. :returns: mapping field -> SQL computation of the field :rtype: dict """ return fields def _query(self, with_clause='', fields=None, groupby='', from_clause=''): if not fields: fields = {} sale_report_fields = self._select_additional_fields(fields) with_ = ("WITH %s" % with_clause) if with_clause else "" return '%s (SELECT %s FROM %s WHERE l.display_type IS NULL GROUP BY %s)' % \ (with_, self._select_sale(sale_report_fields), self._from_sale(from_clause), self._group_by_sale(groupby)) 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)""" % (self._table, self._query())) class SaleOrderReportProforma(models.AbstractModel): _name = 'report.sale.report_saleproforma' _description = 'Proforma Report' @api.model def _get_report_values(self, docids, data=None): docs = self.env['sale.order'].browse(docids) return { 'doc_ids': docs.ids, 'doc_model': 'sale.order', 'docs': docs, 'proforma': True }