I'm working with Odoo 15 and I want to add a new measure on a report from point_of_sale. The module I need to inherit to add a new measure is report.pos.order
I've done it before with the module SaleReport, but this module has a _query() function and report.pos.order doesn't
report.pos.order module
class PosOrderReport(models.Model):
_name = "report.pos.order"
_description = "Point of Sale Orders Report"
_auto = False
_order = 'date desc'
date = fields.Datetime(string='Order Date', readonly=True)
order_id = fields.Many2one('pos.order', string='Order', readonly=True)
partner_id = fields.Many2one('res.partner', string='Customer', readonly=True)
product_id = fields.Many2one('product.product', string='Product', readonly=True)
product_tmpl_id = fields.Many2one('product.template', string='Product Template', readonly=True)
state = fields.Selection(
[('draft', 'New'), ('paid', 'Paid'), ('done', 'Posted'),
('invoiced', 'Invoiced'), ('cancel', 'Cancelled')],
string='Status')
user_id = fields.Many2one('res.users', string='User', readonly=True)
price_total = fields.Float(string='Total Price', readonly=True)
price_sub_total = fields.Float(string='Subtotal w/o discount', readonly=True)
total_discount = fields.Float(string='Total Discount', readonly=True)
average_price = fields.Float(string='Average Price', readonly=True, group_operator="avg")
company_id = fields.Many2one('res.company', string='Company', readonly=True)
nbr_lines = fields.Integer(string='Sale Line Count', readonly=True)
product_qty = fields.Integer(string='Product Quantity', readonly=True)
journal_id = fields.Many2one('account.journal', string='Journal')
delay_validation = fields.Integer(string='Delay Validation')
product_categ_id = fields.Many2one('product.category', string='Product Category', readonly=True)
invoiced = fields.Boolean(readonly=True)
config_id = fields.Many2one('pos.config', string='Point of Sale', readonly=True)
pos_categ_id = fields.Many2one('pos.category', string='PoS Category', readonly=True)
pricelist_id = fields.Many2one('product.pricelist', string='Pricelist', readonly=True)
session_id = fields.Many2one('pos.session', string='Session', readonly=True)
margin = fields.Float(string='Margin', readonly=True)
def _select(self):
return """
SELECT
MIN(l.id) AS id,
COUNT(*) AS nbr_lines,
s.date_order AS date,
SUM(l.qty) AS product_qty,
SUM(l.qty * l.price_unit / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) AS price_sub_total,
SUM(ROUND((l.qty * l.price_unit) * (100 - l.discount) / 100 / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END, cu.decimal_places)) AS price_total,
SUM((l.qty * l.price_unit) * (l.discount / 100) / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) AS total_discount,
CASE
WHEN SUM(l.qty * u.factor) = 0 THEN NULL
ELSE (SUM(l.qty*l.price_unit / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END)/SUM(l.qty * u.factor))::decimal
END AS average_price,
SUM(cast(to_char(date_trunc('day',s.date_order) - date_trunc('day',s.create_date),'DD') AS INT)) AS delay_validation,
s.id as order_id,
s.partner_id AS partner_id,
s.state AS state,
s.user_id AS user_id,
s.company_id AS company_id,
s.sale_journal AS journal_id,
l.product_id AS product_id,
pt.categ_id AS product_categ_id,
p.product_tmpl_id,
ps.config_id,
pt.pos_categ_id,
s.pricelist_id,
s.session_id,
s.account_move IS NOT NULL AS invoiced,
SUM(l.price_subtotal - l.total_cost / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) AS margin
"""
def _from(self):
return """
FROM pos_order_line AS l
INNER JOIN pos_order s ON (s.id=l.order_id)
LEFT JOIN product_product p ON (l.product_id=p.id)
LEFT JOIN product_template pt ON (p.product_tmpl_id=pt.id)
LEFT JOIN uom_uom u ON (u.id=pt.uom_id)
LEFT JOIN pos_session ps ON (s.session_id=ps.id)
LEFT JOIN res_company co ON (s.company_id=co.id)
LEFT JOIN res_currency cu ON (co.currency_id=cu.id)
"""
def _group_by(self):
return """
GROUP BY
s.id, s.date_order, s.partner_id,s.state, pt.categ_id,
s.user_id, s.company_id, s.sale_journal,
s.pricelist_id, s.account_move, s.create_date, s.session_id,
l.product_id,
pt.categ_id, pt.pos_categ_id,
p.product_tmpl_id,
ps.config_id
"""
def init(self):
tools.drop_view_if_exists(self._cr, self._table)
self._cr.execute("""
CREATE OR REPLACE VIEW %s AS (
%s
%s
%s
)
""" % (self._table, self._select(), self._from(), self._group_by())
)
I want to a add the field margin_percent from sale.order module, but in this case I can't use _query function. How can I do it?
You can override _select, _from, and _group_by functions to add the margin_percent field to the pos order report.
You can find the following example in the pos_hr module:
class PosOrderReport(models.Model):
_inherit = "report.pos.order"
employee_id = fields.Many2one(
'hr.employee', string='Employee', readonly=True)
def _select(self):
return super(PosOrderReport, self)._select() + ',s.employee_id AS employee_id'
def _group_by(self):
return super(PosOrderReport, self)._group_by() + ',s.employee_id'
You can also check the OCA pos_event_sale module which overrides the three functions to add the event and event ticket to the pos order report.