def init(self):
"""Mass Mail Statistical Report: based on mailing.trace that models the various
statistics collected for each mailing, and mailing.mailing model that models the
various mailing performed. """
tools.drop_view_if_exists(self.env.cr, 'mailing_trace_report')
self.env.cr.execute("""
CREATE OR REPLACE VIEW mailing_trace_report AS (
SELECT
min(trace.id) as id,
utm_source.name as name,
mailing.mailing_type,
utm_campaign.name as campaign,
trace.scheduled as scheduled_date,
mailing.state,
mailing.email_from,
count(trace.sent) as sent,
(count(trace.sent) - count(trace.bounced)) as delivered,
count(trace.opened) as opened,
count(trace.replied) as replied,
count(trace.clicked) as clicked,
count(trace.bounced) as bounced
FROM
mailing_trace as trace
left join mailing_mailing as mailing ON (trace.mass_mailing_id=mailing.id)
left join utm_campaign as utm_campaign ON (mailing.campaign_id = utm_campaign.id)
left join utm_source as utm_source ON (mailing.source_id = utm_source.id)
GROUP BY trace.scheduled, utm_source.name, utm_campaign.name, mailing.mailing_type, mailing.state, mailing.email_from
)""")
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','in_payment','paid'))
)""")
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,
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)
JOIN res_partner P ON (S.partner_id = p.id)
JOIN res_users U ON (U.partner_id = P.id)
JOIN im_livechat_channel_im_user O ON (O.user_id = U.id and O.channel_id = L.id)
GROUP BY C.id, C.name, C.livechat_channel_id, L.name, C.create_date, C.uuid, S.partner_id
)
""")
def init(self):
tools.drop_view_if_exists(self._cr, self._table)
self._cr.execute(
"""
CREATE or REPLACE view %s AS (
SELECT
row_number() over (ORDER BY users.id,product.id) AS id,
product.id AS product_id,
product.name,
product.category_id,
product.description,
product.price,
product.supplier_id,
product.company_id,
product.active,
users.id AS user_id,
fav.user_id IS NOT NULL AS is_favorite,
product.new_until >= current_date AS is_new,
orders.last_order_date
FROM lunch_product product
INNER JOIN res_users users ON product.company_id IS NULL OR users.company_id = product.company_id -- multi company
INNER JOIN res_groups_users_rel groups ON groups.uid = users.id -- only generate for internal users
LEFT JOIN LATERAL (select max(date) AS last_order_date FROM lunch_order where user_id=users.id and product_id=product.id) AS orders ON TRUE
LEFT JOIN LATERAL (select user_id FROM lunch_product_favorite_user_rel where user_id=users.id and product_id=product.id) AS fav ON TRUE
WHERE users.active AND product.active AND groups.gid = %%s --only take into account active products and users
);
""" % self._table, (self.env.ref('base.group_user').id, ))
def init(self):
tools.drop_view_if_exists(self.env.cr, self._table)
self._cr.execute("""CREATE OR REPLACE VIEW %s AS (
SELECT
max(id) AS id,
t.user_id,
t.date,
coalesce(sum(t.attendance), 0) AS total_attendance,
coalesce(sum(t.timesheet), 0) AS total_timesheet,
coalesce(sum(t.attendance), 0) - coalesce(sum(t.timesheet), 0) as total_difference
FROM (
SELECT
-hr_attendance.id AS id,
resource_resource.user_id AS user_id,
hr_attendance.worked_hours AS attendance,
NULL AS timesheet,
hr_attendance.check_in::date AS date
FROM hr_attendance
LEFT JOIN hr_employee ON hr_employee.id = hr_attendance.employee_id
LEFT JOIN resource_resource on resource_resource.id = hr_employee.resource_id
UNION ALL
SELECT
ts.id AS id,
ts.user_id AS user_id,
NULL AS attendance,
ts.unit_amount AS timesheet,
ts.date AS date
FROM account_analytic_line AS ts
WHERE ts.project_id IS NOT NULL
) AS t
GROUP BY t.user_id, t.date
ORDER BY t.date
)
""" % self._table)
def init(self):
tools.drop_view_if_exists(self._cr, self._table)
self._cr.execute("""
CREATE or REPLACE view %s as (
SELECT
lc.id as id,
lc.amount as amount,
lc.date as date,
lc.currency_id as currency_id,
lc.user_id as user_id,
lc.description as description
FROM lunch_cashmove lc
UNION ALL
SELECT
-lol.id as id,
-lol.price as amount,
lol.date as date,
lol.currency_id as currency_id,
lol.user_id as user_id,
format('Order: %%s x %%s %%s', lol.quantity::text, lp.name, lol.display_toppings) as description
FROM lunch_order lol
JOIN lunch_product lp ON lp.id = lol.product_id
WHERE
lol.state in ('ordered', 'confirmed')
AND lol.active = True
);
""" % self._table)
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):
"""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,
count(ms.clicked) as clicked,
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 = 10 THEN 1
ELSE 0
END as is_happy,
Rate.rating as rating,
CASE
WHEN Rate.rating = 1 THEN 'Unhappy'
WHEN Rate.rating = 10 THEN 'Happy'
WHEN Rate.rating = 5 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 %s %s %s
)
''' % (self._table, self._select(), self._from(), self._where(),
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 (
%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):
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):
# 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._cr, self._table)
self._cr.execute("""
CREATE OR REPLACE VIEW %s AS (
%s
%s
%s
%s
)
""" % (self._table, self._select(), self._from(), self._group_by(),
self._having()))
def init(self):
tools.drop_view_if_exists(self._cr, self._table)
self._cr.execute("""
CREATE OR REPLACE VIEW %s AS (
%s
%s
%s
%s
)
""" % (self._table, self._select(), self._from(), self._join(),
self._where()))
def init(self):
# self._table = education_group_student_report
tools.drop_view_if_exists(self.env.cr, self._table)
self.env.cr.execute(
"""CREATE or REPLACE VIEW %s as
(
%s %s %s
)""", (
AsIs(self._table),
AsIs(self._select()),
AsIs(self._from()),
AsIs(self._group_by()),
))
def init(self):
""" Display Number of cases And Team Name
@param cr: the current row, from the database cursor,
"""
tools.drop_view_if_exists(self.env.cr, self._table)
self.env.cr.execute(
"""
CREATE OR REPLACE VIEW %s AS (
%s
from
%s
%s)
""", (AsIs(self._table), AsIs(self._select()), AsIs(
self._from()), AsIs(self._group_by())))
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.env.cr, 'purchase_bill_union')
self.env.cr.execute("""
CREATE OR REPLACE VIEW purchase_bill_union AS (
SELECT
id, name, ref as reference, partner_id, date, amount_untaxed as amount, currency_id, company_id,
id as vendor_bill_id, NULL as purchase_order_id
FROM account_move
WHERE
type='in_invoice' and state = 'posted'
UNION
SELECT
-id, name, partner_ref as reference, partner_id, date_order::date as date, amount_untaxed as amount, currency_id, company_id,
NULL as vendor_bill_id, id as purchase_order_id
FROM purchase_order
WHERE
state in ('purchase', 'done') AND
invoice_status in ('to invoice', 'no')
)""")
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.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.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):
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):
# 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)
JOIN im_livechat_channel L ON (L.id = O.channel_id)
JOIN mail_channel C ON (C.livechat_channel_id = L.id)
JOIN mail_channel_partner MCP ON (MCP.partner_id = P.id AND MCP.channel_id = C.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):
tools.drop_view_if_exists(self._cr, self._table)
query = """
CREATE VIEW %s AS (
SELECT
ROW_NUMBER() OVER (ORDER BY P.id, SOL.id) AS id,
P.id AS project_id,
P.user_id AS user_id,
SOL.id AS sale_line_id,
P.analytic_account_id AS analytic_account_id,
P.partner_id AS partner_id,
C.id AS company_id,
C.currency_id AS currency_id,
S.id AS sale_order_id,
S.date_order AS order_confirmation_date,
SOL.product_id AS product_id,
SOL.qty_delivered_method AS sale_qty_delivered_method,
CASE
WHEN SOL.qty_delivered_method = 'analytic' THEN (SOL.untaxed_amount_to_invoice / CASE COALESCE(S.currency_rate, 0) WHEN 0 THEN 1.0 ELSE S.currency_rate END)
ELSE 0.0
END AS expense_amount_untaxed_to_invoice,
CASE
WHEN SOL.qty_delivered_method = 'analytic' AND SOL.invoice_status != 'no'
THEN
CASE
WHEN T.expense_policy = 'sales_price'
THEN (SOL.price_reduce / CASE COALESCE(S.currency_rate, 0) WHEN 0 THEN 1.0 ELSE S.currency_rate END) * SOL.qty_invoiced
ELSE -COST_SUMMARY.expense_cost
END
ELSE 0.0
END AS expense_amount_untaxed_invoiced,
CASE
WHEN SOL.qty_delivered_method IN ('timesheet', 'manual') THEN (SOL.untaxed_amount_to_invoice / CASE COALESCE(S.currency_rate, 0) WHEN 0 THEN 1.0 ELSE S.currency_rate END)
ELSE 0.0
END AS amount_untaxed_to_invoice,
CASE
WHEN SOL.qty_delivered_method IN ('timesheet', 'manual') THEN (COALESCE(SOL.untaxed_amount_invoiced, COST_SUMMARY.downpayment_invoiced) / CASE COALESCE(S.currency_rate, 0) WHEN 0 THEN 1.0 ELSE S.currency_rate END)
ELSE 0.0
END AS amount_untaxed_invoiced,
COST_SUMMARY.timesheet_unit_amount AS timesheet_unit_amount,
COST_SUMMARY.timesheet_cost AS timesheet_cost,
COST_SUMMARY.expense_cost AS expense_cost
FROM project_project P
JOIN res_company C ON C.id = P.company_id
LEFT JOIN (
SELECT
project_id,
analytic_account_id,
sale_line_id,
SUM(timesheet_unit_amount) AS timesheet_unit_amount,
SUM(timesheet_cost) AS timesheet_cost,
SUM(expense_cost) AS expense_cost,
SUM(downpayment_invoiced) AS downpayment_invoiced
FROM (
SELECT
P.id AS project_id,
P.analytic_account_id AS analytic_account_id,
TS.so_line AS sale_line_id,
SUM(TS.unit_amount) AS timesheet_unit_amount,
SUM(TS.amount) AS timesheet_cost,
0.0 AS expense_cost,
0.0 AS downpayment_invoiced
FROM account_analytic_line TS, project_project P
WHERE TS.project_id IS NOT NULL AND P.id = TS.project_id AND P.active = 't' AND P.allow_timesheets = 't'
GROUP BY P.id, TS.so_line
UNION
SELECT
P.id AS project_id,
P.analytic_account_id AS analytic_account_id,
AAL.so_line AS sale_line_id,
0.0 AS timesheet_unit_amount,
0.0 AS timesheet_cost,
SUM(AAL.amount) AS expense_cost,
0.0 AS downpayment_invoiced
FROM project_project P
LEFT JOIN account_analytic_account AA ON P.analytic_account_id = AA.id
LEFT JOIN account_analytic_line AAL ON AAL.account_id = AA.id
WHERE AAL.amount < 0.0 AND AAL.project_id IS NULL AND P.active = 't' AND P.allow_timesheets = 't'
GROUP BY P.id, AA.id, AAL.so_line
UNION
SELECT
P.id AS project_id,
P.analytic_account_id AS analytic_account_id,
MY_SOLS.id AS sale_line_id,
0.0 AS timesheet_unit_amount,
0.0 AS timesheet_cost,
0.0 AS expense_cost,
CASE WHEN MY_SOLS.invoice_status = 'invoiced' THEN MY_SOLS.price_reduce ELSE 0.0 END AS downpayment_invoiced
FROM project_project P
LEFT JOIN sale_order_line MY_SOL ON P.sale_line_id = MY_SOL.id
LEFT JOIN sale_order MY_S ON MY_SOL.order_id = MY_S.id
LEFT JOIN sale_order_line MY_SOLS ON MY_SOLS.order_id = MY_S.id
WHERE MY_SOLS.is_downpayment = 't'
GROUP BY P.id, MY_SOLS.id
UNION
SELECT
P.id AS project_id,
P.analytic_account_id AS analytic_account_id,
OLIS.id AS sale_line_id,
0.0 AS timesheet_unit_amount,
0.0 AS timesheet_cost,
OLIS.price_reduce AS expense_cost,
0.0 AS downpayment_invoiced
FROM project_project P
LEFT JOIN account_analytic_account ANAC ON P.analytic_account_id = ANAC.id
LEFT JOIN account_analytic_line ANLI ON ANAC.id = ANLI.account_id
LEFT JOIN sale_order_line OLI ON P.sale_line_id = OLI.id
LEFT JOIN sale_order ORD ON OLI.order_id = ORD.id
LEFT JOIN sale_order_line OLIS ON ORD.id = OLIS.order_id
WHERE OLIS.product_id = ANLI.product_id AND OLIS.is_downpayment = 't' AND ANLI.amount < 0.0 AND ANLI.project_id IS NULL AND P.active = 't' AND P.allow_timesheets = 't'
GROUP BY P.id, OLIS.id
UNION
SELECT
P.id AS project_id,
P.analytic_account_id AS analytic_account_id,
SOL.id AS sale_line_id,
0.0 AS timesheet_unit_amount,
0.0 AS timesheet_cost,
0.0 AS expense_cost,
0.0 AS downpayment_invoiced
FROM sale_order_line SOL
INNER JOIN project_project P ON SOL.project_id = P.id
WHERE P.active = 't' AND P.allow_timesheets = 't'
UNION
SELECT
P.id AS project_id,
P.analytic_account_id AS analytic_account_id,
SOL.id AS sale_line_id,
0.0 AS timesheet_unit_amount,
0.0 AS timesheet_cost,
0.0 AS expense_cost,
0.0 AS downpayment_invoiced
FROM sale_order_line SOL
INNER JOIN project_task T ON SOL.task_id = T.id
INNER JOIN project_project P ON P.id = T.project_id
WHERE P.active = 't' AND P.allow_timesheets = 't'
) SUB_COST_SUMMARY
GROUP BY project_id, analytic_account_id, sale_line_id
) COST_SUMMARY ON COST_SUMMARY.project_id = P.id
LEFT JOIN sale_order_line SOL ON COST_SUMMARY.sale_line_id = SOL.id
LEFT JOIN sale_order S ON SOL.order_id = S.id
LEFT JOIN product_product PP on (SOL.product_id=PP.id)
LEFT JOIN product_template T on (PP.product_tmpl_id=T.id)
WHERE P.active = 't' AND P.analytic_account_id IS NOT NULL
)
""" % self._table
self._cr.execute(query)
def init(self):
tools.drop_view_if_exists(self._cr, 'report_stock_forecast')
query = """
CREATE or REPLACE VIEW report_stock_forecast AS (SELECT
%s
FROM
(SELECT
MIN(id) as id,
MAIN.product_id as product_id,
date(SUB.date) as date,
CASE WHEN MAIN.date = SUB.date THEN sum(MAIN.product_qty) ELSE 0 END as product_qty,
MAIN.reference as reference,
MAIN.company_id as company_id
FROM
(SELECT
MIN(-product.id) as id,
product.id as product_id,
date(CURRENT_DATE) as date,
SUM(sq.quantity) AS product_qty,
'Starting Inventory' as reference,
sq.company_id
FROM
product_product as product
LEFT JOIN
stock_quant sq ON product.id = sq.product_id
LEFT JOIN
stock_location location_id ON sq.location_id = location_id.id
WHERE
location_id.usage = 'internal'
GROUP BY
date, product.id, sq.company_id, sq.quantity
UNION ALL
SELECT
MIN(sm.id) as id,
sm.product_id,
CASE WHEN sm.date_expected > CURRENT_DATE
THEN date(sm.date_expected)
ELSE date(CURRENT_DATE) END
AS date,
SUM(sm.product_qty) AS product_qty,
sm.reference as reference,
sm.company_id
FROM
stock_move as sm
LEFT JOIN
product_product ON product_product.id = sm.product_id
LEFT JOIN
stock_location dest_location ON sm.location_dest_id = dest_location.id
LEFT JOIN
stock_location source_location ON sm.location_id = source_location.id
WHERE
sm.state IN ('confirmed','partially_available','assigned','waiting') and
source_location.usage != 'internal' and dest_location.usage = 'internal'
GROUP BY
sm.date_expected,sm.product_id, sm.company_id, sm.reference
UNION ALL
SELECT
MIN(sm.id) as id,
sm.product_id,
CASE WHEN sm.date_expected > CURRENT_DATE
THEN date(sm.date_expected)
ELSE date(CURRENT_DATE) END
AS date,
SUM(-(sm.product_qty)) AS product_qty,
sm.reference AS reference,
sm.company_id
FROM
stock_move as sm
LEFT JOIN
product_product ON product_product.id = sm.product_id
LEFT JOIN
stock_location source_location ON sm.location_id = source_location.id
LEFT JOIN
stock_location dest_location ON sm.location_dest_id = dest_location.id
WHERE
sm.state IN ('confirmed','partially_available','assigned','waiting') and
source_location.usage = 'internal' and dest_location.usage != 'internal'
GROUP BY
sm.date_expected,sm.product_id, sm.company_id, sm.reference
) AS MAIN
LEFT JOIN
(SELECT
DISTINCT date
FROM
(SELECT
date_trunc('day', CURRENT_DATE) AS DATE
UNION ALL
SELECT
date(sm.date_expected) AS date
FROM
stock_move sm
LEFT JOIN
stock_location source_location ON sm.location_id = source_location.id
LEFT JOIN
stock_location dest_location ON sm.location_dest_id = dest_location.id
WHERE
sm.state IN ('confirmed','assigned','waiting') AND
sm.date_expected > CURRENT_DATE AND
((dest_location.usage = 'internal' AND
source_location.usage != 'internal') OR
(source_location.usage = 'internal' AND
dest_location.usage != 'internal'))
) AS DATE_SEARCH
) AS SUB ON SUB.date IS NOT NULL
GROUP BY
MAIN.product_id,SUB.date, MAIN.date, MAIN.company_id,MAIN.reference
) AS FINAL
%s
WHERE
final.product_qty != 0 OR final.reference = 'Starting Inventory'
%s
ORDER BY
date
)
""" % (self._select(), self._left_join(), self._groupby())
self.env.cr.execute(query)
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):
tools.drop_view_if_exists(self._cr, 'report_stock_quantity')
query = """
CREATE or REPLACE VIEW report_stock_quantity AS (
SELECT
m.id,
product_id,
CASE
WHEN (ls.usage = 'internal' OR ls.usage = 'transit' AND ls.company_id IS NOT NULL) AND ld.usage != 'internal' THEN 'out'
WHEN ls.usage != 'internal' AND (ld.usage = 'internal' OR ld.usage = 'transit' AND ld.company_id IS NOT NULL) THEN 'in'
END AS state,
date_expected::date AS date,
CASE
WHEN (ls.usage = 'internal' OR ls.usage = 'transit' AND ls.company_id IS NOT NULL) AND ld.usage != 'internal' THEN -product_qty
WHEN ls.usage != 'internal' AND (ld.usage = 'internal' OR ld.usage = 'transit' AND ld.company_id IS NOT NULL) THEN product_qty
END AS product_qty,
m.company_id,
CASE
WHEN (ls.usage = 'internal' OR ls.usage = 'transit' AND ls.company_id IS NOT NULL) AND ld.usage != 'internal' THEN whs.id
WHEN ls.usage != 'internal' AND (ld.usage = 'internal' OR ld.usage = 'transit' AND ld.company_id IS NOT NULL) THEN whd.id
END AS warehouse_id
FROM
stock_move m
LEFT JOIN stock_location ls on (ls.id=m.location_id)
LEFT JOIN stock_location ld on (ld.id=m.location_dest_id)
LEFT JOIN stock_warehouse whs ON ls.parent_path like concat('%/', whs.view_location_id, '/%')
LEFT JOIN stock_warehouse whd ON ld.parent_path like concat('%/', whd.view_location_id, '/%')
LEFT JOIN product_product pp on pp.id=m.product_id
LEFT JOIN product_template pt on pt.id=pp.product_tmpl_id
WHERE
pt.type = 'product' AND
product_qty != 0 AND (
(ls.usage = 'internal' OR ls.usage = 'transit' AND ls.company_id IS NOT NULL) AND ld.usage != 'internal' OR
ls.usage != 'internal' AND (ld.usage = 'internal' OR ld.usage = 'transit' AND ld.company_id IS NOT NULL)) AND
m.state NOT IN ('cancel', 'draft', 'done')
UNION
SELECT
-q.id as id,
product_id,
'forecast' as state,
date.*::date,
quantity as product_qty,
q.company_id,
wh.id as warehouse_id
FROM
GENERATE_SERIES((now() at time zone 'utc')::date - interval '3month',
(now() at time zone 'utc')::date + interval '3 month', '1 day'::interval) date,
stock_quant q
LEFT JOIN stock_location l on (l.id=q.location_id)
LEFT JOIN stock_warehouse wh ON l.parent_path like concat('%/', wh.view_location_id, '/%')
WHERE
l.usage = 'internal'
UNION
SELECT
m.id,
product_id,
'forecast' as state,
GENERATE_SERIES(
CASE
WHEN m.state = 'done' THEN (now() at time zone 'utc')::date - interval '3month'
ELSE date_expected::date
END,
CASE
WHEN m.state != 'done' THEN (now() at time zone 'utc')::date + interval '3 month'
ELSE date::date - interval '1 day'
END, '1 day'::interval)::date date,
CASE
WHEN (ls.usage = 'internal' OR ls.usage = 'transit' AND ls.company_id IS NOT NULL) AND ld.usage != 'internal' AND m.state = 'done' THEN product_qty
WHEN ls.usage != 'internal' AND (ld.usage = 'internal' OR ld.usage = 'transit' AND ld.company_id IS NOT NULL) AND m.state = 'done' THEN -product_qty
WHEN (ls.usage = 'internal' OR ls.usage = 'transit' AND ls.company_id IS NOT NULL) AND ld.usage != 'internal' THEN -product_qty
WHEN ls.usage != 'internal' AND (ld.usage = 'internal' OR ld.usage = 'transit' AND ld.company_id IS NOT NULL) THEN product_qty
END AS product_qty,
m.company_id,
CASE
WHEN (ls.usage = 'internal' OR ls.usage = 'transit' AND ls.company_id IS NOT NULL) AND ld.usage != 'internal' THEN whs.id
WHEN ls.usage != 'internal' AND (ld.usage = 'internal' OR ld.usage = 'transit' AND ld.company_id IS NOT NULL) THEN whd.id
END AS warehouse_id
FROM
stock_move m
LEFT JOIN stock_location ls on (ls.id=m.location_id)
LEFT JOIN stock_location ld on (ld.id=m.location_dest_id)
LEFT JOIN stock_warehouse whs ON ls.parent_path like concat('%/', whs.view_location_id, '/%')
LEFT JOIN stock_warehouse whd ON ld.parent_path like concat('%/', whd.view_location_id, '/%')
LEFT JOIN product_product pp on pp.id=m.product_id
LEFT JOIN product_template pt on pt.id=pp.product_tmpl_id
WHERE
pt.type = 'product' AND
product_qty != 0 AND (
(ls.usage = 'internal' OR ls.usage = 'transit' AND ls.company_id IS NOT NULL) AND ld.usage != 'internal' OR
ls.usage != 'internal' AND (ld.usage = 'internal' OR ld.usage = 'transit' AND ld.company_id IS NOT NULL)) AND
m.state NOT IN ('cancel', 'draft')
);
"""
self.env.cr.execute(query)
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)""" %
(self._table, self._query()))
def init(self):
tools.drop_view_if_exists(self.env.cr, self._table)
self.env.cr.execute(self.get_main_request())
