[IMP] sale, pos_sale: merge sale report

In Sales, we want to merge the `All Channels Sale Orders` report with
the `Sales` report in order to avoid having information scattered
accross two different reports.

Related to task #58961
Closes #23324

11 files changed, 179 insertions, 130 deletions

diff --git a/addons/pos_sale/report/__init__.py b/addons/pos_sale/report/__init__.py
index 72bdd30312f..7b0761d5199 100644
--- a/addons/pos_sale/report/__init__.py
+++ b/addons/pos_sale/report/__init__.py
@@ -1,4 +1,4 @@
 # -*- coding: utf-8 -*-
 # Part of Odoo. See LICENSE file for full copyright and licensing details.
 
-from . import report_all_channels_sales
+from . import sale_report
diff --git a/addons/pos_sale/report/report_all_channels_sales.py b/addons/pos_sale/report/report_all_channels_sales.py
deleted file mode 100644
index 5683f68a8ab..00000000000
--- a/addons/pos_sale/report/report_all_channels_sales.py
+++ /dev/null
@@ -1,44 +0,0 @@
-# -*- coding: utf-8 -*-
-# Part of Odoo. See LICENSE file for full copyright and licensing details.
-
-from odoo import models
-
-
-class PosSaleReport(models.Model):
-    _inherit = "report.all.channels.sales"
-
-    def _pos(self):
-        pos_str = """
-                 SELECT
-                    (-1) * pol.id AS id,
-                    pos.name AS name,
-                    pos.partner_id AS partner_id,
-                    pol.product_id AS product_id,
-                    pro.product_tmpl_id AS product_tmpl_id,
-                    pos.date_order AS date_order,
-                    pos.user_id AS user_id,
-                    pt.categ_id AS categ_id,
-                    pos.company_id AS company_id,
-                    ((pol.qty * pol.price_unit) * (100 - pol.discount) / 100) AS price_total,
-                    pos.pricelist_id AS pricelist_id,
-                    rp.country_id AS country_id,
-                    (pol.qty * pol.price_unit) AS price_subtotal,
-                    (pol.qty * u.factor) AS product_qty,
-                    NULL AS analytic_account_id,
-                    config.crm_team_id AS team_id
-
-                FROM pos_order_line AS pol
-                    JOIN pos_order pos ON (pos.id = pol.order_id)
-                    LEFT JOIN pos_session session ON (session.id = pos.session_id)
-                    LEFT JOIN pos_config config ON (config.id = session.config_id)
-                    LEFT JOIN product_product pro ON (pol.product_id = pro.id)
-                    LEFT JOIN product_template pt ON (pro.product_tmpl_id = pt.id)
-                    LEFT JOIN product_category AS pc ON (pt.categ_id = pc.id)
-                    LEFT JOIN res_company AS rc ON (pos.company_id = rc.id)
-                    LEFT JOIN res_partner rp ON (rc.partner_id = rp.id)
-                    LEFT JOIN uom_uom u ON (u.id=pt.uom_id)
-         """
-        return pos_str
-
-    def _from(self):
-        return """(%s UNION ALL %s)""" % (self._so(), self._pos())
diff --git a/addons/pos_sale/report/sale_report.py b/addons/pos_sale/report/sale_report.py
new file mode 100644
index 00000000000..640e674b0e7
--- /dev/null
+++ b/addons/pos_sale/report/sale_report.py
@@ -0,0 +1,107 @@
+# -*- 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):
+    _inherit = "sale.report"
+
+    state = fields.Selection(selection_add=[('pos_draft', 'New'),
+                                            ('paid', 'Paid'),
+                                            ('pos_done', 'Posted'),
+                                            ('invoiced', 'Invoiced')], string='Status', readonly=True)
+
+    def _query(self, with_clause='', fields={}, groupby=''):
+        with_clause += ''', pol_tax AS (SELECT line_tax.pos_order_line_id AS pol_id,sum(tax.amount/100) AS amount
+                       FROM account_tax_pos_order_line_rel line_tax
+                         JOIN account_tax tax ON line_tax.account_tax_id = tax.id
+                      GROUP BY line_tax.pos_order_line_id)'''
+
+        res = super(SaleReport, self)._query(with_clause, fields, groupby)
+
+        select_ = '''
+            MIN(l.id) AS id,
+            l.product_id AS product_id,
+            t.uom_id AS product_uom,
+            sum(l.qty * u.factor) AS product_uom_qty,
+            sum(l.qty * u.factor) AS qty_delivered,
+            CASE WHEN pos.state = 'invoiced' THEN sum(qty) ELSE 0 END AS qty_invoiced,
+            CASE WHEN pos.state != 'invoiced' THEN sum(qty) ELSE 0 END AS qty_to_invoice,
+            COALESCE(COALESCE((l.price_unit * l.qty * u.factor * ((1 - COALESCE(l.discount / 100, 0)) )) + (l.price_unit * l.qty * u.factor * ((1 - COALESCE(l.discount / 100, 0)) ) * pol_tax.amount), (l.price_unit * l.qty * u.factor * ((1 - COALESCE(l.discount / 100, 0)) ))), 1.0) AS price_total,
+            COALESCE((l.qty * u.factor * l.price_unit * ((1 - COALESCE(l.discount / 100, 0)) )), 1.0) AS price_subtotal,
+            CASE WHEN pos.state != 'invoiced' THEN ((l.price_unit * l.qty * u.factor * ((1 - COALESCE(l.discount / 100, 0)))) + (l.price_unit * l.qty * u.factor * (1 - COALESCE(l.discount / 100, 0)) * pol_tax.amount)) ELSE 0 END AS amount_to_invoice,
+            CASE WHEN pos.state = 'invoiced' THEN ((l.price_unit * l.qty * u.factor * ((1 - COALESCE(l.discount / 100, 0)) )) + (l.price_unit * l.qty * u.factor * (1 - COALESCE(l.discount / 100, 0)) * pol_tax.amount)) ELSE 0  END AS amount_invoiced,
+            count(*) AS nbr,
+            pos.name AS name,
+            pos.date_order AS date,
+            pos.date_order AS confirmation_date,
+            CASE WHEN pos.state = 'draft' THEN 'pos_draft' WHEN pos.state = 'done' THEN 'pos_done' else pos.state END AS state,
+            pos.partner_id AS partner_id,
+            pos.user_id AS user_id,
+            pos.company_id AS company_id,
+            extract(epoch from avg(date_trunc('day',pos.date_order)-date_trunc('day',pos.create_date)))/(24*60*60)::decimal(16,2) AS delay,
+            t.categ_id AS categ_id,
+            pos.pricelist_id AS pricelist_id,
+            NULL AS analytic_account_id,
+            config.crm_team_id AS team_id,
+            p.product_tmpl_id,
+            partner.country_id AS country_id,
+            partner.commercial_partner_id AS commercial_partner_id,
+            (select sum(t.weight*l.qty/u.factor) from pos_order_line l
+               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=t.uom_id)) AS weight,
+            (select sum(t.volume*l.qty/u.factor) from pos_order_line l
+               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=t.uom_id)) AS volume
+        '''
+
+        for field in fields.keys():
+            select_ += ', NULL AS %s' % (field)
+
+        from_ = '''
+            pos_order_line l
+                  join pos_order pos on (l.order_id=pos.id)
+                  left join res_partner partner ON (pos.partner_id = partner.id OR pos.partner_id = NULL)
+                    left join pol_tax pol_tax on pol_tax.pol_id=l.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=t.uom_id)
+                    LEFT JOIN pos_session session ON (session.id = pos.session_id)
+                    LEFT JOIN pos_config config ON (config.id = session.config_id)
+                left join product_pricelist pp on (pos.pricelist_id = pp.id)
+                left join currency_rate cr on (cr.currency_id = pp.currency_id and
+                    cr.company_id = pos.company_id and
+                    cr.date_start <= coalesce(pos.date_order, now()) and
+                    (cr.date_end is null or cr.date_end > coalesce(pos.date_order, now())))
+        '''
+
+        groupby_ = '''
+            l.order_id,
+            l.product_id,
+            l.price_unit,
+            l.discount,
+            l.qty,
+            t.uom_id,
+            t.categ_id,
+            pos.name,
+            pos.date_order,
+            pos.partner_id,
+            pos.user_id,
+            pos.state,
+            pos.company_id,
+            pos.pricelist_id,
+            p.product_tmpl_id,
+            partner.country_id,
+            partner.commercial_partner_id,
+            pol_tax.amount,
+            u.factor,
+            config.crm_team_id
+        '''
+
+        current = '(SELECT %s FROM %s GROUP BY %s)' % (select_, from_, groupby_)
+
+        return '%s UNION ALL %s' % (res, current)
diff --git a/addons/sale/__manifest__.py b/addons/sale/__manifest__.py
index a5ff679d745..915261dfa62 100644
--- a/addons/sale/__manifest__.py
+++ b/addons/sale/__manifest__.py
@@ -17,7 +17,6 @@ This module contains all the common features of Sales Management and eCommerce.
         'report/sale_report_views.xml',
         'report/sale_report_templates.xml',
         'report/invoice_report_templates.xml',
-        'report/report_all_channels_sales_views.xml',
         'data/ir_sequence_data.xml',
         'data/sale_data.xml',
         'data/mail_data.xml',
diff --git a/addons/sale/report/__init__.py b/addons/sale/report/__init__.py
index 90248f477ca..881c37f9e16 100644
--- a/addons/sale/report/__init__.py
+++ b/addons/sale/report/__init__.py
@@ -3,4 +3,3 @@
 
 from . import sale_report
 from . import invoice_report
-from . import report_all_channels_sales
diff --git a/addons/sale/report/sale_report.py b/addons/sale/report/sale_report.py
index 38d2409c9f7..f300a32618f 100644
--- a/addons/sale/report/sale_report.py
+++ b/addons/sale/report/sale_report.py
@@ -46,43 +46,45 @@ class SaleReport(models.Model):
     weight = fields.Float('Gross Weight', readonly=True)
     volume = fields.Float('Volume', readonly=True)
 
-    def _select(self):
-        select_str = """
-            WITH currency_rate as (%s)
-             SELECT min(l.id) as id,
-                    l.product_id as product_id,
-                    t.uom_id as product_uom,
-                    sum(l.product_uom_qty / u.factor * u2.factor) as product_uom_qty,
-                    sum(l.qty_delivered / u.factor * u2.factor) as qty_delivered,
-                    sum(l.qty_invoiced / u.factor * u2.factor) as qty_invoiced,
-                    sum(l.qty_to_invoice / u.factor * u2.factor) as qty_to_invoice,
-                    sum(l.price_total / COALESCE(cr.rate, 1.0)) as price_total,
-                    sum(l.price_subtotal / COALESCE(cr.rate, 1.0)) as price_subtotal,
-                    sum(l.price_reduce * l.qty_to_invoice / COALESCE(cr.rate, 1.0)) as amount_to_invoice,
-                    sum(l.price_reduce * l.qty_invoiced / COALESCE(cr.rate, 1.0)) as amount_invoiced,
-                    count(*) as nbr,
-                    s.name as name,
-                    s.date_order as date,
-                    s.confirmation_date as confirmation_date,
-                    s.state as state,
-                    s.partner_id as partner_id,
-                    s.user_id as user_id,
-                    s.company_id as company_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.commercial_partner_id as commercial_partner_id,
-                    sum(p.weight * l.product_uom_qty / u.factor * u2.factor) as weight,
-                    sum(p.volume * l.product_uom_qty / u.factor * u2.factor) as volume
-        """ % self.env['res.currency']._select_companies_rates()
-        return select_str
+    def _query(self, with_clause='', fields={}, groupby=''):
+        with_ = """currency_rate as (%s) %s""" % (self.env['res.currency']._select_companies_rates(), with_clause)
 
-    def _from(self):
-        from_str = """
+        select_ = """
+            min(l.id) as id,
+            l.product_id as product_id,
+            t.uom_id as product_uom,
+            sum(l.product_uom_qty / u.factor * u2.factor) as product_uom_qty,
+            sum(l.qty_delivered / u.factor * u2.factor) as qty_delivered,
+            sum(l.qty_invoiced / u.factor * u2.factor) as qty_invoiced,
+            sum(l.qty_to_invoice / u.factor * u2.factor) as qty_to_invoice,
+            sum(l.price_total / COALESCE(cr.rate, 1.0)) as price_total,
+            sum(l.price_subtotal / COALESCE(cr.rate, 1.0)) as price_subtotal,
+            sum(l.price_reduce * l.qty_to_invoice / COALESCE(cr.rate, 1.0)) as amount_to_invoice,
+            sum(l.price_reduce * l.qty_invoiced / COALESCE(cr.rate, 1.0)) as amount_invoiced,
+            count(*) as nbr,
+            s.name as name,
+            s.date_order as date,
+            s.confirmation_date as confirmation_date,
+            s.state as state,
+            s.partner_id as partner_id,
+            s.user_id as user_id,
+            s.company_id as company_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.commercial_partner_id as commercial_partner_id,
+            sum(p.weight * l.product_uom_qty / u.factor * u2.factor) as weight,
+            sum(p.volume * l.product_uom_qty / u.factor * u2.factor) as volume
+        """
+
+        for field in fields.values():
+            select_ += field
+
+        from_ = """
                 sale_order_line l
                       join sale_order s on (l.order_id=s.id)
                       join res_partner partner on s.partner_id = partner.id
@@ -96,40 +98,34 @@ class SaleReport(models.Model):
                         cr.date_start <= coalesce(s.date_order, now()) and
                         (cr.date_end is null or cr.date_end > coalesce(s.date_order, now())))
         """
-        return from_str
 
-    def _group_by(self):
-        group_by_str = """
-            GROUP BY l.product_id,
-                    l.order_id,
-                    t.uom_id,
-                    t.categ_id,
-                    s.name,
-                    s.date_order,
-                    s.confirmation_date,
-                    s.partner_id,
-                    s.user_id,
-                    s.state,
-                    s.company_id,
-                    s.pricelist_id,
-                    s.analytic_account_id,
-                    s.team_id,
-                    p.product_tmpl_id,
-                    partner.country_id,
-                    partner.commercial_partner_id
-        """
-        return group_by_str
+        groupby_ = """
+            l.product_id,
+            l.order_id,
+            t.uom_id,
+            t.categ_id,
+            s.name,
+            s.date_order,
+            s.confirmation_date,
+            s.partner_id,
+            s.user_id,
+            s.state,
+            s.company_id,
+            s.pricelist_id,
+            s.analytic_account_id,
+            s.team_id,
+            p.product_tmpl_id,
+            partner.country_id,
+            partner.commercial_partner_id %s
+        """ % (groupby)
+
+        return 'WITH %s (SELECT %s FROM %s WHERE l.product_id IS NOT NULL GROUP BY %s)' % (with_, select_, from_, groupby_)
 
     @api.model_cr
     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
-            FROM ( %s )
-            WHERE l.product_id IS NOT NULL
-            %s
-            )""" % (self._table, self._select(), self._from(), self._group_by()))
+        self.env.cr.execute("""CREATE or REPLACE VIEW %s as (%s)""" % (self._table, self._query()))
 
 class SaleOrderReportProforma(models.AbstractModel):
     _name = 'report.sale.report_saleproforma'
diff --git a/addons/sale/security/ir.model.access.csv b/addons/sale/security/ir.model.access.csv
index 53f1a65c8e6..6fef14c849f 100644
--- a/addons/sale/security/ir.model.access.csv
+++ b/addons/sale/security/ir.model.access.csv
@@ -48,6 +48,4 @@ access_account_journal_sale_manager,account.journal sale manager,account.model_a
 access_account_invoice_tax_sale_manager,account.invoice.tax  sale manager,account.model_account_invoice_tax,sales_team.group_sale_salesman,1,0,0,0

 access_account_tax_group_sale_manager,account.tax.group sale manager,account.model_account_tax_group,sales_team.group_sale_salesman,1,0,0,0

 access_account_account_sale_manager,account.account sale manager,account.model_account_account,sales_team.group_sale_manager,1,0,0,0

-access_report_all_channels_sales_salesman,report.all.channels.sales,model_report_all_channels_sales,sales_team.group_sale_salesman,1,1,1,0

-access_report_all_channels_sales_manager,report.all.channels.sales,model_report_all_channels_sales,sales_team.group_sale_manager,1,1,1,1

-access_mail_activity_type_sale_manager,mail.activity.type.sale.manager,mail.model_mail_activity_type,sales_team.group_sale_manager,1,1,1,1
\ No newline at end of file
+access_mail_activity_type_sale_manager,mail.activity.type.sale.manager,mail.model_mail_activity_type,sales_team.group_sale_manager,1,1,1,1

diff --git a/addons/sale/views/sale_views.xml b/addons/sale/views/sale_views.xml
index 0415bf1ded1..eb5c08b230c 100644
--- a/addons/sale/views/sale_views.xml
+++ b/addons/sale/views/sale_views.xml
@@ -55,12 +55,6 @@
             action="sales_team.crm_team_salesteams_act"
             sequence="3"/>
 
-        <menuitem id="report_all_channels_sales"
-            action="report_all_channels_sales_action"
-            name="Sales by Channel"
-            parent="menu_sale_report"
-            sequence="2"/>
-
         <record id="product_template_action" model="ir.actions.act_window">
             <field name="name">Products</field>
             <field name="type">ir.actions.act_window</field>
diff --git a/addons/sale_margin/report/sale_report.py b/addons/sale_margin/report/sale_report.py
index f69a0e2c023..977bf420114 100644
--- a/addons/sale_margin/report/sale_report.py
+++ b/addons/sale_margin/report/sale_report.py
@@ -9,5 +9,6 @@ class SaleReport(models.Model):
 
     margin = fields.Float('Margin')
 
-    def _select(self):
-        return super(SaleReport, self)._select() + ", SUM(l.margin / COALESCE(cr.rate, 1.0)) AS margin"
+    def _query(self, with_clause='', fields={}, groupby=''):
+        fields['margin'] = ", SUM(l.margin / COALESCE(cr.rate, 1.0)) AS margin"
+        return super(SaleReport, self)._query(with_clause, fields, groupby)
diff --git a/addons/sale_stock/report/sale_report.py b/addons/sale_stock/report/sale_report.py
index 5101af87c01..96694bc04ac 100644
--- a/addons/sale_stock/report/sale_report.py
+++ b/addons/sale_stock/report/sale_report.py
@@ -9,8 +9,7 @@ class SaleReport(models.Model):
 
     warehouse_id = fields.Many2one('stock.warehouse', 'Warehouse', readonly=True)
 
-    def _select(self):
-        return super(SaleReport, self)._select() + ", s.warehouse_id as warehouse_id"
-
-    def _group_by(self):
-        return super(SaleReport, self)._group_by() + ", s.warehouse_id"
+    def _query(self, with_clause='', fields={}, groupby=''):
+        fields['warehouse_id'] = ", s.warehouse_id as warehouse_id"
+        groupby += ', s.warehouse_id'
+        return super(SaleReport, self)._query(with_clause, fields, groupby)
diff --git a/addons/sales_team/views/crm_team_views.xml b/addons/sales_team/views/crm_team_views.xml
index 413a41a7106..b1be79dad3f 100644
--- a/addons/sales_team/views/crm_team_views.xml
+++ b/addons/sales_team/views/crm_team_views.xml
@@ -22,7 +22,7 @@
 
         <!-- Case Teams Action -->
         <record id="crm_team_salesteams_act" model="ir.actions.act_window">
-            <field name="name">Sales Teams</field>
+            <field name="name">Sales Dashboard</field>
             <field name="res_model">crm.team</field>
             <field name="view_type">form</field>
             <field name="view_mode">kanban,form</field>
@@ -32,7 +32,7 @@
                 <p class="o_view_nocontent_smiling_face">
                     Define a new Sales Team
                 </p><p>
-                    Use Sales Teams to organize your sales departments. 
+                    Use Sales Teams to organize your sales departments.
                     Each channel will work with a separate pipeline.
                 </p>
             </field>
@@ -164,7 +164,7 @@
                 <p class="o_view_nocontent_smiling_face">
                     Define a new Sales Team
                 </p><p>
-                    Use Sales Teams to organize your sales departments. 
+                    Use Sales Teams to organize your sales departments.
                     Each channel will work with a separate pipeline.
                 </p>
             </field>