[IMP] fleet: model cleanup, dashboard

Purpose
=======

It is currently difficult to know exactly what is used for costs
and services on the contracts.

Hence it make it difficult to evolve the fleet application
We should clean the models in order to have something that is clearer

Specification
=============

Cleanup of models: removed Fuel and Cost. Contract and Services are no
longer inheriting Cost and are seperate models simplifying the use of
Fleet. A contract now has included services and they are no longer
generating cost.

closes odoo/odoo#34090

Taskid: 1931775
Related: odoo/enterprise#4572
Signed-off-by: Yannick Tivisse (yti) <yti@odoo.com>

2 files changed, 132 insertions, 0 deletions

diff --git a/addons/fleet/report/__init__.py b/addons/fleet/report/__init__.py
new file mode 100644
index 00000000000..a5b36f4db3f
--- /dev/null
+++ b/addons/fleet/report/__init__.py
@@ -0,0 +1 @@
+from . import fleet_report
diff --git a/addons/fleet/report/fleet_report.py b/addons/fleet/report/fleet_report.py
new file mode 100644
index 00000000000..6d1fdc56497
--- /dev/null
+++ b/addons/fleet/report/fleet_report.py
@@ -0,0 +1,131 @@
+# -*- 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 FleetReport(models.Model):
+    _name = "fleet.vehicle.cost.report"
+    _description = "Fleet Analysis Report"
+    _auto = False
+    _order = 'date_start desc'
+
+    company_id = fields.Many2one('res.company', 'Company', readonly=True)
+    vehicle_id = fields.Many2one('fleet.vehicle', 'Vehicle', readonly=True)
+    name = fields.Char('Vehicle Name', readonly=True)
+    driver_id = fields.Many2one('res.partner', 'Driver', readonly=True)
+    fuel_type = fields.Char('Fuel', readonly=True)
+    date_start = fields.Date('Date', readonly=True)
+
+    cost = fields.Float('Cost', readonly=True)
+    cost_type = fields.Selection(string='Cost Type', selection=[
+        ('contract', 'Contract'),
+        ('service', 'Service')
+    ], readonly=True)
+
+    def init(self):
+        query = """
+WITH service_costs AS (
+    SELECT
+        ve.id AS vehicle_id,
+        ve.company_id AS company_id,
+        ve.name AS name,
+        ve.driver_id AS driver_id,
+        ve.fuel_type AS fuel_type,
+        date(date_trunc('month', d)) AS date_start,
+        COALESCE(sum(se.amount), 0) AS
+        COST,
+        'service' AS cost_type
+    FROM
+        fleet_vehicle ve
+    CROSS JOIN generate_series((
+            SELECT
+                min(acquisition_date)
+                FROM fleet_vehicle), CURRENT_DATE, '1 month') d
+        LEFT JOIN fleet_vehicle_log_services se ON se.vehicle_id = ve.id
+            AND date_trunc('month', se.date) = date_trunc('month', d)
+    WHERE
+        ve.active
+    GROUP BY
+        ve.id,
+        ve.company_id,
+        ve.name,
+        date_start,
+        d
+    ORDER BY
+        ve.id,
+        date_start
+),
+contract_costs AS (
+    SELECT
+        ve.id AS vehicle_id,
+        ve.company_id AS company_id,
+        ve.name AS name,
+        ve.driver_id AS driver_id,
+        ve.fuel_type AS fuel_type,
+        date(date_trunc('month', d)) AS date_start,
+        (COALESCE(sum(co.amount), 0) + COALESCE(sum(cod.cost_generated * extract(day FROM least (date_trunc('month', d) + interval '1 month', cod.expiration_date) - greatest (date_trunc('month', d), cod.start_date))), 0) + COALESCE(sum(com.cost_generated), 0) + COALESCE(sum(coy.cost_generated), 0)) AS
+        COST,
+        'contract' AS cost_type
+    FROM
+        fleet_vehicle ve
+    CROSS JOIN generate_series((
+            SELECT
+                min(acquisition_date)
+                FROM fleet_vehicle), CURRENT_DATE, '1 month') d
+        LEFT JOIN fleet_vehicle_log_contract co ON co.vehicle_id = ve.id
+            AND date_trunc('month', co.date) = date_trunc('month', d)
+        LEFT JOIN fleet_vehicle_log_contract cod ON cod.vehicle_id = ve.id
+            AND date_trunc('month', cod.start_date) <= date_trunc('month', d)
+            AND date_trunc('month', cod.expiration_date) >= date_trunc('month', d)
+            AND cod.cost_frequency = 'daily'
+    LEFT JOIN fleet_vehicle_log_contract com ON com.vehicle_id = ve.id
+        AND date_trunc('month', com.start_date) <= date_trunc('month', d)
+        AND date_trunc('month', com.expiration_date) >= date_trunc('month', d)
+        AND com.cost_frequency = 'monthly'
+    LEFT JOIN fleet_vehicle_log_contract coy ON coy.vehicle_id = ve.id
+        AND date_trunc('month', coy.date) = date_trunc('month', d)
+        AND date_trunc('month', coy.start_date) <= date_trunc('month', d)
+        AND date_trunc('month', coy.expiration_date) >= date_trunc('month', d)
+        AND coy.cost_frequency = 'yearly'
+WHERE
+    ve.active
+GROUP BY
+    ve.id,
+    ve.company_id,
+    ve.name,
+    date_start,
+    d
+ORDER BY
+    ve.id,
+    date_start
+)
+SELECT
+    vehicle_id AS id,
+    company_id,
+    vehicle_id,
+    name,
+    driver_id,
+    fuel_type,
+    date_start,
+    COST,
+    cost_type
+FROM
+    service_costs sc
+UNION ALL (
+    SELECT
+        vehicle_id AS id,
+        company_id,
+        vehicle_id,
+        name,
+        driver_id,
+        fuel_type,
+        date_start,
+        COST,
+        cost_type
+    FROM
+        contract_costs cc)
+"""
+        tools.drop_view_if_exists(self.env.cr, self._table)
+        self.env.cr.execute("""CREATE or REPLACE VIEW %s as (%s)""" % (self._table, query))
\ No newline at end of file