1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
|
# -*- coding: utf-8 -*-
# Part of Odoo. See LICENSE file for full copyright and licensing details.
from psycopg2 import sql
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(date)
FROM fleet_vehicle_log_services), CURRENT_DATE + '1 month'::interval, '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 AND se.active AND se.state != 'cancelled'
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'::interval, '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,
'service' as 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,
'contract' as cost_type
FROM
contract_costs cc)
"""
tools.drop_view_if_exists(self.env.cr, self._table)
self.env.cr.execute(
sql.SQL("""CREATE or REPLACE VIEW {} as ({})""").format(
sql.Identifier(self._table),
sql.SQL(query)
))
|
