def init(self):
tools.drop_view_if_exists(self._cr, 'account_followup_stat_by_partner')
# Here we don't have other choice but to create a virtual ID based on
# the concatenation of the partner_id and the company_id, because
# if a partner is shared between 2 companies, we want to see 2 lines
# for him in this table. It means that both company should be able
# to send him follow-ups separately . An assumption that the number
# of companies will not reach 10 000 records is made, what should
# be enough for a time.
self._cr.execute("""
create view account_followup_stat_by_partner as (
SELECT
l.partner_id * 10000::bigint + l.company_id as id,
l.partner_id AS partner_id,
min(l.date) AS date_move,
max(l.date) AS date_move_last,
max(l.followup_date) AS date_followup,
max(l.followup_line_id) AS max_followup_id,
sum(l.debit - l.credit) AS balance,
l.company_id as company_id
FROM
account_move_line l
LEFT JOIN account_account a ON (l.account_id = a.id)
WHERE
a.user_type_id IN (SELECT id FROM account_account_type
WHERE type = 'receivable') AND
l.full_reconcile_id is NULL AND
l.partner_id IS NOT NULL
GROUP BY
l.partner_id, l.company_id
)""")
def init(self):
"""
CRM Lead Report
@param cr: the current row, from the database cursor
"""
tools.drop_view_if_exists(self._cr, 'crm_partner_report_assign')
self._cr.execute("""
CREATE OR REPLACE VIEW crm_partner_report_assign AS (
SELECT
coalesce(i.id, p.id - 1000000000) as id,
p.id as partner_id,
(SELECT country_id FROM res_partner a WHERE a.parent_id=p.id AND country_id is not null limit 1) as country_id,
p.grade_id,
p.activation,
p.date_review,
p.date_partnership,
p.user_id,
p.team_id,
(SELECT count(id) FROM crm_lead WHERE partner_assigned_id=p.id) AS nbr_opportunities,
i.price_total as turnover,
i.date
FROM
res_partner p
left join account_invoice_report i
on (i.partner_id=p.id and i.type in ('out_invoice','out_refund') and i.state in ('open','paid'))
)""")
def init(self):
tools.drop_view_if_exists(self.env.cr, self._table)
self.env.cr.execute("""CREATE or REPLACE VIEW %s as (
SELECT
%s
FROM hr_employee emp
)""" % (self._table, self._get_fields()))
def init(self):
# Note : start_date_hour must be remove when the read_group will allow grouping on the hour of a datetime. Don't forget to change the view !
tools.drop_view_if_exists(self.env.cr, 'im_livechat_report_channel')
self.env.cr.execute("""
CREATE OR REPLACE VIEW im_livechat_report_channel AS (
SELECT
C.id as id,
C.uuid as uuid,
C.id as channel_id,
C.name as channel_name,
CONCAT(L.name, ' / ', C.id) as technical_name,
C.livechat_channel_id as livechat_channel_id,
C.create_date as start_date,
to_char(date_trunc('hour', C.create_date), 'YYYY-MM-DD HH24:MI:SS') as start_date_hour,
EXTRACT('epoch' FROM (max((SELECT (max(M.create_date)) FROM mail_message M JOIN mail_message_mail_channel_rel R ON (R.mail_message_id = M.id) WHERE R.mail_channel_id = C.id))-C.create_date)) as duration,
count(distinct P.id) as nbr_speaker,
count(distinct M.id) as nbr_message,
MAX(S.partner_id) as partner_id
FROM mail_channel C
JOIN mail_message_mail_channel_rel R ON (C.id = R.mail_channel_id)
JOIN mail_message M ON (M.id = R.mail_message_id)
JOIN mail_channel_partner S ON (S.channel_id = C.id)
JOIN im_livechat_channel L ON (L.id = C.livechat_channel_id)
LEFT JOIN res_partner P ON (M.author_id = P.id)
GROUP BY C.id, C.name, C.livechat_channel_id, L.name, C.create_date, C.uuid
)
""")
def init(self):
"""Mass Mail Statistical Report: based on mail.mail.statistics that models the various
statistics collected for each mailing, and mail.mass_mailing model that models the
various mailing performed. """
tools.drop_view_if_exists(self.env.cr, 'mail_statistics_report')
self.env.cr.execute("""
CREATE OR REPLACE VIEW mail_statistics_report AS (
SELECT
min(ms.id) as id,
ms.scheduled as scheduled_date,
utm_source.name as name,
utm_campaign.name as campaign,
count(ms.bounced) as bounced,
count(ms.sent) as sent,
(count(ms.sent) - count(ms.bounced)) as delivered,
count(ms.opened) as opened,
count(ms.replied) as replied,
mm.state,
mm.email_from
FROM
mail_mail_statistics as ms
left join mail_mass_mailing as mm ON (ms.mass_mailing_id=mm.id)
left join mail_mass_mailing_campaign as mc ON (ms.mass_mailing_campaign_id=mc.id)
left join utm_campaign as utm_campaign ON (mc.campaign_id = utm_campaign.id)
left join utm_source as utm_source ON (mm.source_id = utm_source.id)
GROUP BY ms.scheduled, utm_source.name, utm_campaign.name, mm.state, mm.email_from
)""")
def init(self):
# Note : start_date_hour must be remove when the read_group will allow grouping on the hour of a datetime. Don't forget to change the view !
tools.drop_view_if_exists(self.env.cr, 'im_livechat_report_channel')
self.env.cr.execute("""
CREATE OR REPLACE VIEW im_livechat_report_channel AS (
SELECT
C.id as id,
C.uuid as uuid,
C.id as channel_id,
C.name as channel_name,
CONCAT(L.name, ' / ', C.id) as technical_name,
C.livechat_channel_id as livechat_channel_id,
C.create_date as start_date,
to_char(date_trunc('hour', C.create_date), 'YYYY-MM-DD HH24:MI:SS') as start_date_hour,
to_char(date_trunc('hour', C.create_date), 'HH24') as start_hour,
extract(dow from C.create_date) as day_number,
EXTRACT('epoch' FROM MAX(M.create_date) - MIN(M.create_date)) AS duration,
EXTRACT('epoch' FROM MIN(MO.create_date) - MIN(M.create_date)) AS time_to_answer,
count(distinct C.livechat_operator_id) as nbr_speaker,
count(distinct M.id) as nbr_message,
CASE
WHEN EXISTS (select distinct M.author_id FROM mail_message M, mail_message_mail_channel_rel R
WHERE M.author_id=C.livechat_operator_id AND R.mail_channel_id = C.id
AND R.mail_message_id = M.id and C.livechat_operator_id = M.author_id)
THEN 0
ELSE 1
END as is_without_answer,
(DATE_PART('day', date_trunc('day', now()) - date_trunc('day', C.create_date)) + 1) as days_of_activity,
CASE
WHEN C.anonymous_name IS NULL THEN 0
ELSE 1
END as is_anonymous,
C.country_id,
CASE
WHEN rate.rating = 5 THEN 1
ELSE 0
END as is_happy,
Rate.rating as rating,
CASE
WHEN Rate.rating = 1 THEN 'Unhappy'
WHEN Rate.rating = 5 THEN 'Happy'
WHEN Rate.rating = 3 THEN 'Neutral'
ELSE null
END as rating_text,
CASE
WHEN rate.rating > 0 THEN 0
ELSE 1
END as is_unrated,
C.livechat_operator_id as partner_id
FROM mail_channel C
JOIN mail_message_mail_channel_rel R ON (C.id = R.mail_channel_id)
JOIN mail_message M ON (M.id = R.mail_message_id)
JOIN im_livechat_channel L ON (L.id = C.livechat_channel_id)
LEFT JOIN mail_message MO ON (R.mail_message_id = MO.id AND MO.author_id = C.livechat_operator_id)
LEFT JOIN rating_rating Rate ON (Rate.res_id = C.id and Rate.res_model = 'mail.channel' and Rate.parent_res_model = 'im_livechat.channel')
WHERE C.livechat_operator_id is not null
GROUP BY C.livechat_operator_id, C.id, C.name, C.livechat_channel_id, L.name, C.create_date, C.uuid, Rate.rating
)
""")
def init(self):
tools.drop_view_if_exists(self.env.cr, self._table)
self.env.cr.execute("""CREATE or REPLACE VIEW %s AS (
%s
FROM (
%s %s %s
) AS sub %s)""" % (self._table, self._select(), self._sub_select(),
self._from(), self._where(), self._group_by()))
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 )
%s
)""" % (self._table, self._select(), self._from(), self._group_by()))
def init(self):
tools.drop_view_if_exists(self.env.cr, self._table)
self.env.cr.execute(
"""CREATE or REPLACE VIEW %s as ( SELECT
%s
FROM %s
%s
)""" %
(self._table, self._select(), self._from(), self._group_by()))
def init(self):
tools.drop_view_if_exists(self._cr, self._table)
self._cr.execute("""
CREATE view %s as
%s
FROM project_task t
WHERE t.active = 'true'
%s
""" % (self._table, self._select(), self._group_by()))
def init(self):
tools.drop_view_if_exists(self.env.cr, self._table)
self.env.cr.execute("""CREATE VIEW %s AS (
%s
%s
%s
%s
%s
)""" % (self._table, self._select(), self._from(), self._join(),
self._where(), self._group_by()))
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())
)
def init(self):
cr = self.env.cr
tools.drop_view_if_exists(cr, 'report_plm_document_file')
cr.execute("""
create or replace view report_plm_document_file as (
select min(f.id) as id,
count(*) as nbr,
min(EXTRACT(YEAR FROM f.create_date)||'-'||EXTRACT(MONTH FROM f.create_date)) as month,
sum(f.file_size) as file_size,
f.write_uid as write_uid
from plm_document f
group by EXTRACT(MONTH FROM f.create_date), write_uid
)
""")
def init(self):
tools.drop_view_if_exists(self._cr, self._table)
# pylint: disable=E8103
self._cr.execute("""CREATE or REPLACE VIEW {} as (
{}
{}
{}
{}
)""".format(
self._table,
self._select(),
self._from(),
self._where(),
self._group_by(),
))
def init(self):
tools.drop_view_if_exists(self._cr, 'analytic_summary_user')
self._cr.execute('''
CREATE OR REPLACE VIEW analytic_summary_user AS (with mu as
(select max(id) as max_user from res_users)
, lu AS(SELECT l.account_id AS account_id,
coalesce(l.user_id, 0) AS user_id,
SUM(l.unit_amount) AS unit_amount
FROM account_analytic_line AS l, account_journal AS j
WHERE (j.type = 'sale' ) and (j.id=l.journal_id)
GROUP BY l.account_id, l.user_id)
select (lu.account_id::bigint * mu.max_user) + lu.user_id as id,
lu.account_id as account_id,
lu.user_id as "user_id",
unit_amount from lu, mu)''')
def init(self):
tools.drop_view_if_exists(self.env.cr, self._table)
self.env.cr.execute('''
CREATE OR REPLACE VIEW %s AS (
SELECT DISTINCT ASCII(code) * 1000 + ASCII(SUBSTRING(code,2,1)) AS id,
LEFT(code,2) AS name,
ASCII(code) AS parent_id,
company_id
FROM account_account WHERE code IS NOT NULL
UNION ALL
SELECT DISTINCT ASCII(code) AS id,
LEFT(code,1) AS name,
NULL::int AS parent_id,
company_id
FROM account_account WHERE code IS NOT NULL
)''' % (self._table, ))
def init(self):
# self._table = account_invoice_report
tools.drop_view_if_exists(self.env.cr, self._table)
self.env.cr.execute("""CREATE or REPLACE VIEW %s as (
WITH currency_rate AS (%s)
%s
FROM (
%s %s %s
) AS sub
LEFT JOIN currency_rate cr ON
(cr.currency_id = sub.currency_id AND
cr.company_id = sub.company_id AND
cr.date_start <= COALESCE(sub.date, NOW()) AND
(cr.date_end IS NULL OR cr.date_end > COALESCE(sub.date, NOW())))
)""" % (
self._table, self.env['res.currency']._select_companies_rates(),
self._select(), self._sub_select(), self._from(), self._group_by()))
def init(self):
tools.drop_view_if_exists(self._cr, 'hr_leave_report')
self._cr.execute("""
CREATE or REPLACE view hr_leave_report as (
SELECT row_number() over(ORDER BY leaves.employee_id) as id,
leaves.employee_id as employee_id, leaves.name as name,
leaves.number_of_days as number_of_days, leaves.leave_type as leave_type,
leaves.category_id as category_id, leaves.department_id as department_id,
leaves.holiday_status_id as holiday_status_id, leaves.state as state,
leaves.holiday_type as holiday_type, leaves.date_from as date_from,
leaves.date_to as date_to, leaves.payslip_status as payslip_status
from (select
allocation.employee_id as employee_id,
allocation.private_name as name,
allocation.number_of_days as number_of_days,
allocation.category_id as category_id,
allocation.department_id as department_id,
allocation.holiday_status_id as holiday_status_id,
allocation.state as state,
allocation.holiday_type,
null as date_from,
null as date_to,
FALSE as payslip_status,
'allocation' as leave_type
from hr_leave_allocation as allocation
union all select
request.employee_id as employee_id,
request.private_name as name,
(request.number_of_days * -1) as number_of_days,
request.category_id as category_id,
request.department_id as department_id,
request.holiday_status_id as holiday_status_id,
request.state as state,
request.holiday_type,
request.date_from as date_from,
request.date_to as date_to,
request.payslip_status as payslip_status,
'request' as leave_type
from hr_leave as request) leaves
);
""")
def init(self):
cr = self._cr
tools.drop_view_if_exists(
cr, 'analytic_summary_month')
cr.execute(
'CREATE VIEW analytic_summary_month AS ('
'SELECT '
'(TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') + ('
'd.account_id * 1000000::bigint))::bigint AS id, '
'd.account_id AS account_id, '
'TO_CHAR(d.month, \'Mon YYYY\') AS month, '
'TO_NUMBER(TO_CHAR(d.month, \'YYYYMM\'), \'999999\') AS month_id, '
'COALESCE(SUM(l.unit_amount), 0.0) AS unit_amount '
'FROM (SELECT d2.account_id, d2.month FROM '
'(SELECT a.id AS account_id, l.month AS month '
'FROM (SELECT DATE_TRUNC(\'month\', l.date) AS month '
'FROM account_analytic_line AS l, '
'account_journal AS j '
'WHERE j.type = \'sale\' '
'GROUP BY DATE_TRUNC(\'month\', l.date) '
') AS l, '
'account_analytic_account AS a '
'GROUP BY l.month, a.id '
') AS d2 '
'GROUP BY d2.account_id, d2.month '
') AS d '
'LEFT JOIN '
'(SELECT l.account_id AS account_id, '
'DATE_TRUNC(\'month\', l.date) AS month, '
'SUM(l.unit_amount) AS unit_amount '
'FROM account_analytic_line AS l, '
'account_journal AS j '
'WHERE (j.type = \'sale\') and (j.id=l.journal_id) '
'GROUP BY l.account_id, DATE_TRUNC(\'month\', l.date) '
') AS l '
'ON ('
'd.account_id = l.account_id '
'AND d.month = l.month'
') '
'GROUP BY d.month, d.account_id '
')')
def init(self):
tools.drop_view_if_exists(self._cr, 'helpdesk_report')
self._cr.execute("""
CREATE VIEW helpdesk_report as (
SELECT
t.id as id,
h.ticket_seq as ticket_seq,
t.name as name,
h.partner_id as partner_id,
h.user_id as user_id,
h.issue_type_id as issue_type_id,
h.team_id as team_id,
h.start_date as start_date,
h.end_date as end_date,
h.stage_id as stage_id,
h.help_description as description,
h.priority as priority
FROM helpdesk_team t
INNER JOIN helpdesk_ticket h
ON (h.team_id = t.id and h.active = True))
""")
def init(self):
tools.drop_view_if_exists(self._cr, 'asset_asset_report')
self._cr.execute("""
create or replace view asset_asset_report as (
select
min(dl.id) as id,
dl.name as name,
dl.depreciation_date as depreciation_date,
a.date as date,
(CASE WHEN dlmin.id = min(dl.id)
THEN a.value
ELSE 0
END) as gross_value,
dl.amount as depreciation_value,
dl.amount as installment_value,
(CASE WHEN dl.move_check
THEN dl.amount
ELSE 0
END) as posted_value,
(CASE WHEN NOT dl.move_check
THEN dl.amount
ELSE 0
END) as unposted_value,
dl.asset_id as asset_id,
dl.move_check as move_check,
a.category_id as asset_category_id,
a.partner_id as partner_id,
a.state as state,
count(dl.*) as installment_nbr,
count(dl.*) as depreciation_nbr,
a.company_id as company_id
from account_asset_depreciation_line dl
left join account_asset_asset a on (dl.asset_id=a.id)
left join (select min(d.id) as id,ac.id as ac_id from account_asset_depreciation_line as d inner join account_asset_asset as ac ON (ac.id=d.asset_id) group by ac_id) as dlmin on dlmin.ac_id=a.id
where a.active is true
group by
dl.amount,dl.asset_id,dl.depreciation_date,dl.name,
a.date, dl.move_check, a.state, a.category_id, a.partner_id, a.company_id,
a.value, a.id, a.salvage_value, dlmin.id
)""")
def init(self):
# Note : start_date_hour must be remove when the read_group will allow grouping on the hour of a datetime. Don't forget to change the view !
tools.drop_view_if_exists(self.env.cr, 'im_livechat_report_operator')
self.env.cr.execute("""
CREATE OR REPLACE VIEW im_livechat_report_operator AS (
SELECT
row_number() OVER () AS id,
C.livechat_operator_id AS partner_id,
C.livechat_channel_id AS livechat_channel_id,
COUNT(DISTINCT C.id) AS nbr_channel,
C.id AS channel_id,
C.create_date AS start_date,
EXTRACT('epoch' FROM MAX(M.create_date) - MIN(M.create_date)) AS duration,
EXTRACT('epoch' FROM MIN(MO.create_date) - MIN(M.create_date)) AS time_to_answer
FROM mail_channel C
JOIN mail_message_mail_channel_rel R ON R.mail_channel_id = C.id
JOIN mail_message M ON R.mail_message_id = M.id
LEFT JOIN mail_message MO ON (R.mail_message_id = MO.id AND MO.author_id = C.livechat_operator_id)
WHERE C.livechat_channel_id IS NOT NULL
GROUP BY C.id, C.livechat_operator_id
)
""")
def init(self):
tools.drop_view_if_exists(self._cr,
'hr_holidays_remaining_leaves_user')
self._cr.execute("""
CREATE or REPLACE view hr_holidays_remaining_leaves_user as (
SELECT
min(hrs.id) as id,
rr.name as name,
sum(hrs.number_of_days) as no_of_leaves,
rr.user_id as user_id,
hhs.name as leave_type
FROM
hr_holidays as hrs, hr_employee as hre,
resource_resource as rr,hr_holidays_status as hhs
WHERE
hrs.employee_id = hre.id and
hre.resource_id = rr.id and
hhs.id = hrs.holiday_status_id
GROUP BY
rr.name,rr.user_id,hhs.name
)
""")
def init(self):
# Note : start_date_hour must be remove when the read_group will allow grouping on the hour of a datetime. Don't forget to change the view !
tools.drop_view_if_exists(self.env.cr, 'im_livechat_report_operator')
self.env.cr.execute("""
CREATE OR REPLACE VIEW im_livechat_report_operator AS (
SELECT
row_number() OVER () AS id,
P.id as partner_id,
L.id as livechat_channel_id,
count(C.id) as nbr_channel,
C.id as channel_id,
C.create_date as start_date,
EXTRACT('epoch' FROM (max((SELECT (max(M.create_date)) FROM mail_message M JOIN mail_message_mail_channel_rel R ON (R.mail_message_id = M.id) WHERE R.mail_channel_id = C.id))-C.create_date)) as duration,
EXTRACT('epoch' from ((SELECT min(M.create_date) FROM mail_message M, mail_message_mail_channel_rel R WHERE M.author_id=P.id AND R.mail_channel_id = C.id AND R.mail_message_id = M.id)-(SELECT min(M.create_date) FROM mail_message M, mail_message_mail_channel_rel R WHERE M.author_id IS NULL AND R.mail_channel_id = C.id AND R.mail_message_id = M.id))) as time_to_answer
FROM im_livechat_channel_im_user O
JOIN res_users U ON (O.user_id = U.id)
JOIN res_partner P ON (U.partner_id = P.id)
LEFT JOIN im_livechat_channel L ON (L.id = O.channel_id)
LEFT JOIN mail_channel C ON (C.livechat_channel_id = L.id)
GROUP BY P.id, L.id, C.id, C.create_date
)
""")
def init(self):
""" Event Question main report """
tools.drop_view_if_exists(self._cr, 'event_question_report')
self._cr.execute(""" CREATE VIEW event_question_report AS (
SELECT
att_answer.id as id,
att_answer.event_registration_id as attendee_id,
answer.question_id as question_id,
answer.id as answer_id,
question.event_id as event_id
FROM
event_registration_answer as att_answer
LEFT JOIN
event_answer as answer ON answer.id = att_answer.event_answer_id
LEFT JOIN
event_question as question ON question.id = answer.question_id
GROUP BY
attendee_id,
event_id,
question_id,
answer_id,
att_answer.id
)""")
def init(self):
"""
CRM Lead Report
@param cr: the current row, from the database cursor
"""
tools.drop_view_if_exists(self._cr, 'crm_lead_report_assign')
self._cr.execute("""
CREATE OR REPLACE VIEW crm_lead_report_assign AS (
SELECT
c.id,
c.date_open as opening_date,
c.date_closed as date_closed,
c.date_assign,
c.user_id,
c.probability,
c.probability as probability_max,
c.type,
c.company_id,
c.priority,
c.team_id,
c.partner_id,
c.country_id,
c.planned_revenue,
c.partner_assigned_id,
p.grade_id,
p.date as partner_date,
c.planned_revenue*(c.probability/100) as probable_revenue,
1 as nbr_cases,
c.create_date as create_date,
extract('epoch' from (c.write_date-c.create_date))/(3600*24) as delay_close,
extract('epoch' from (c.date_deadline - c.date_closed))/(3600*24) as delay_expected,
extract('epoch' from (c.date_open-c.create_date))/(3600*24) as delay_open
FROM
crm_lead c
left join res_partner p on (c.partner_assigned_id=p.id)
)""")
def init(self):
tools.drop_view_if_exists(self._cr, 'hr_leave_report_calendar')
self._cr.execute("""CREATE OR REPLACE VIEW hr_leave_report_calendar AS
(SELECT
row_number() OVER() AS id,
CONCAT(em.name, ': ', hl.duration_display) AS name,
hl.date_from AS start_datetime,
hl.date_to AS stop_datetime,
hl.employee_id AS employee_id,
hl.state AS state,
em.company_id AS company_id,
CASE
WHEN hl.holiday_type = 'employee' THEN rr.tz
ELSE %s
END AS tz
FROM hr_leave hl
LEFT JOIN hr_employee em
ON em.id = hl.employee_id
LEFT JOIN resource_resource rr
ON rr.id = em.resource_id
WHERE
hl.state IN ('confirm', 'validate', 'validate1')
ORDER BY id);
""", [self.env.company.resource_calendar_id.tz or self.env.user.tz or 'UTC'])
def init(self):
tools.drop_view_if_exists(self._cr, 'partner_followup_statistics')
self._cr.execute("""
create view partner_followup_statistics as (
SELECT
l.partner_id * 10000::bigint + l.company_id as id,
l.partner_id AS partner_id,
min(l.date) AS first_move_date,
max(l.date) AS last_move_date,
max(l.date_payment_followup) AS date_payment_followup,
max(l.payment_followup_line_id) AS max_fup_id,
sum(l.debit - l.credit) AS amount_balance,
l.company_id as company_id
FROM
account_move_line l
LEFT JOIN account_account a ON (l.account_id = a.id)
WHERE
a.user_type_id IN (SELECT id FROM account_account_type
WHERE type = 'receivable') AND
l.full_reconcile_id is NULL AND
l.partner_id IS NOT NULL
GROUP BY
l.partner_id, l.company_id
)""")
def init(self):
'''Create the view'''
tools.drop_view_if_exists(self._cr, self._table)
self._cr.execute("""
CREATE OR REPLACE VIEW %s AS (
SELECT
MIN(id) AS id,
partner_id,
count(membership_id) as quantity,
user_id,
membership_state,
associate_member_id,
membership_amount,
date_to,
start_date,
COUNT(num_waiting) AS num_waiting,
COUNT(num_invoiced) AS num_invoiced,
COUNT(num_paid) AS num_paid,
SUM(tot_pending) AS tot_pending,
SUM(tot_earned) AS tot_earned,
membership_id,
company_id
FROM
(SELECT
MIN(p.id) AS id,
p.id AS partner_id,
p.user_id AS user_id,
p.membership_state AS membership_state,
p.associate_member AS associate_member_id,
p.membership_amount AS membership_amount,
p.membership_stop AS date_to,
p.membership_start AS start_date,
CASE WHEN ml.state = 'waiting' THEN ml.id END AS num_waiting,
CASE WHEN ml.state = 'invoiced' THEN ml.id END AS num_invoiced,
CASE WHEN ml.state = 'paid' THEN ml.id END AS num_paid,
CASE WHEN ml.state IN ('waiting', 'invoiced') THEN SUM(il.price_subtotal) ELSE 0 END AS tot_pending,
CASE WHEN ml.state = 'paid' OR p.membership_state = 'old' THEN SUM(il.price_subtotal) ELSE 0 END AS tot_earned,
ml.membership_id AS membership_id,
p.company_id AS company_id
FROM res_partner p
LEFT JOIN membership_membership_line ml ON (ml.partner = p.id)
LEFT JOIN account_invoice_line il ON (ml.account_invoice_line = il.id)
LEFT JOIN account_invoice ai ON (il.invoice_id = ai.id)
WHERE p.membership_state != 'none' and p.active = 'true'
GROUP BY
p.id,
p.user_id,
p.membership_state,
p.associate_member,
p.membership_amount,
p.membership_start,
ml.membership_id,
p.company_id,
ml.state,
ml.id
) AS foo
GROUP BY
start_date,
date_to,
partner_id,
user_id,
membership_id,
company_id,
membership_state,
associate_member_id,
membership_amount
)""" % (self._table, ))
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)))
