def init(self):
tools.drop_view_if_exists(self.env.cr, self._table)
# We disable the check for SQL injection. The only risk of sql
# injection is from 'self._table' which is not controlled by an
# external source.
# pylint:disable=E8103
self.env.cr.execute("""
CREATE OR REPLACE VIEW %s AS
SELECT c.id, c.end_date, c.end_reason, c.sub_sponsorship_id,
c.sds_state, p.id as partner_id, %s, %s, %s,
p.lang, 100/s.sponsored as active_percentage,
100.0/s.sponsored_terminated as total_percentage,
s.sponsored_terminated,
s.study_date
FROM recurring_contract c JOIN res_partner p
ON c.correspondent_id = p.id
%s
WHERE c.state = 'terminated' AND c.child_id IS NOT NULL
AND c.end_date IS NOT NULL
""" % (self._table,
self._select_fiscal_year('c.end_date'),
self._select_category(),
self._select_sub_category(),
self._join_stats())
)
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):
cr = self._cr
tools.drop_view_if_exists(cr, 'report_lot_status')
cr.execute(
"""
create or replace view report_lot_status as (
SELECT MIN(line.id) as id,
goods.name as goods,
uom.name as uom,
uos.name as uos,
line.lot as lot,
line.attribute_id as attribute_id,
'在库' as status,
wh.name as warehouse,
max(line.date) as date,
sum(line.qty_remaining) as qty,
sum(line.uos_qty_remaining) as uos_qty
FROM wh_move_line line
LEFT JOIN goods goods ON line.goods_id = goods.id
LEFT JOIN uom uom ON goods.uom_id = uom.id
LEFT JOIN uom uos ON goods.uos_id = uos.id
LEFT JOIN warehouse wh ON line.warehouse_dest_id = wh.id
WHERE line.lot IS NOT NULL
AND line.qty_remaining > 0
AND wh.type = 'stock'
AND line.state = 'done'
GROUP BY goods, uom, uos, lot, attribute_id, warehouse
ORDER BY goods, lot, warehouse
)
""")
def init(self):
tools.drop_view_if_exists(self._cr, self._table)
self._cr.execute(""" CREATE VIEW %s AS (
SELECT
history.date::varchar||'-'||history.history_id::varchar AS id,
history.date AS end_date,
*
FROM (
SELECT
h.id AS history_id,
h.date+generate_series(0, CAST((coalesce(h.end_date, DATE 'tomorrow')::date - h.date) AS integer)-1) AS date,
h.task_id, h.type_id, h.user_id, h.kanban_state,
count(h.task_id) as nbr_tasks,
greatest(h.remaining_hours, 1) AS remaining_hours, greatest(h.planned_hours, 1) AS planned_hours,
t.project_id
FROM
project_task_history AS h
JOIN project_task AS t ON (h.task_id = t.id)
GROUP BY
h.id,
h.task_id,
t.project_id
) AS history
)
""" % (self._table,))
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):
drop_view_if_exists(self._cr, 'report_mrp_inout')
self._cr.execute("""
create view report_mrp_inout as (
select
min(sm.id) as id,
to_char(sm.date,'YYYY:IW') as date,
sum(case when (sl.usage='internal') then
sm.price_unit * sm.product_qty
else
0.0
end - case when (sl2.usage='internal') then
sm.price_unit * sm.product_qty
else
0.0
end) as value,
sm.company_id
from
stock_move sm
left join product_product pp
on (pp.id = sm.product_id)
left join product_template pt
on (pt.id = pp.product_tmpl_id)
left join stock_location sl
on ( sl.id = sm.location_id)
left join stock_location sl2
on ( sl2.id = sm.location_dest_id)
where
sm.state = 'done'
group by
to_char(sm.date,'YYYY:IW'), sm.company_id
)""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'project_issue_report')
cr.execute("""
CREATE OR REPLACE VIEW project_issue_report AS (
SELECT
c.id as id,
c.date_open as opening_date,
c.create_date as create_date,
c.date_last_stage_update as date_last_stage_update,
c.user_id,
c.working_hours_open,
c.working_hours_close,
c.stage_id,
date(c.date_closed) as date_closed,
c.company_id as company_id,
c.priority as priority,
c.project_id as project_id,
1 as nbr_issues,
c.partner_id,
c.channel,
c.task_id,
c.day_open as delay_open,
c.day_close as delay_close,
(SELECT count(id) FROM mail_message WHERE model='project.issue' AND res_id=c.id) AS email
FROM
project_issue c
LEFT JOIN project_task t on c.task_id = t.id
WHERE c.active= 'true'
)""")
def init(self):
tools.drop_view_if_exists(self.env.cr, 'campaign_analysis')
self.env.cr.execute("""
CREATE OR REPLACE VIEW campaign_analysis AS (
SELECT
min(wi.id) AS id,
min(wi.res_id) AS res_id,
to_char(wi.date::date, 'YYYY') AS year,
to_char(wi.date::date, 'MM') AS month,
to_char(wi.date::date, 'YYYY-MM-DD') AS day,
wi.date::date AS date,
s.campaign_id AS campaign_id,
wi.activity_id AS activity_id,
wi.segment_id AS segment_id,
wi.partner_id AS partner_id ,
wi.state AS state,
sum(act.revenue) AS revenue,
count(*) AS count
FROM
marketing_campaign_workitem wi
LEFT JOIN res_partner p ON (p.id=wi.partner_id)
LEFT JOIN marketing_campaign_segment s ON (s.id=wi.segment_id)
LEFT JOIN marketing_campaign_activity act ON (act.id= wi.activity_id)
GROUP BY
s.campaign_id,wi.activity_id,wi.segment_id,wi.partner_id,wi.state,
wi.date::date
)
""")
def init(self):
tools.drop_view_if_exists(self._cr, 'crm_activity_report')
self._cr.execute("""
CREATE VIEW crm_activity_report AS (
select
m.id,
m.subtype_id,
m.mail_activity_type_id,
m.author_id,
m.date,
m.subject,
l.id as lead_id,
l.user_id,
l.team_id,
l.country_id,
l.company_id,
l.stage_id,
l.partner_id,
l.type as lead_type,
l.active,
l.probability
from
"mail_message" m
join
"crm_lead" l
on
(m.res_id = l.id)
WHERE
(m.model = 'crm.lead' AND m.mail_activity_type_id IS NOT NULL)
)""")
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):
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 for %%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):
cr = self._cr
tools.drop_view_if_exists(cr, 'report_stock_balance')
cr.execute(
"""
create or replace view report_stock_balance as (
SELECT min(line.id) as id,
goods.name as goods,
goods.id as goods_id,
line.lot as lot,
attribute.name as attribute_id,
uom.name as uom,
uos.name as uos,
wh.name as warehouse,
sum(line.qty_remaining) as goods_qty,
sum(line.uos_qty_remaining) as goods_uos_qty,
sum(line.qty_remaining * line.cost_unit) as cost
FROM wh_move_line line
LEFT JOIN warehouse wh ON line.warehouse_dest_id = wh.id
LEFT JOIN goods goods ON line.goods_id = goods.id
LEFT JOIN attribute attribute on attribute.id = line.attribute_id
LEFT JOIN uom uom ON goods.uom_id = uom.id
LEFT JOIN uom uos ON goods.uos_id = uos.id
WHERE wh.type = 'stock'
AND line.state = 'done'
AND ( goods.no_stock is null or goods.no_stock = FALSE)
GROUP BY wh.name, line.lot, attribute.name, goods.name, goods.id, uom.name, uos.name
ORDER BY goods.name, wh.name, goods_qty asc
)
""")
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):
# 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
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):
# union money_order(type = 'get'), money_invoice(type = 'income')
cr = self._cr
tools.drop_view_if_exists(cr, 'customer_statements_report')
cr.execute("""
CREATE or REPLACE VIEW customer_statements_report AS (
SELECT ROW_NUMBER() OVER(ORDER BY partner_id,done_date) AS id,
partner_id,
name,
date,
done_date,
sale_amount,
benefit_amount,
fee,
amount,
pay_amount,
discount_money,
balance_amount,
0 AS this_balance_amount,
note,
move_id
FROM
(
SELECT m.partner_id,
m.name,
m.date,
m.write_date AS done_date,
0 AS sale_amount,
0 AS benefit_amount,
0 AS fee,
0 AS amount,
m.amount AS pay_amount,
m.discount_amount as discount_money,
0 AS balance_amount,
m.note,
0 AS move_id
FROM money_order AS m
WHERE m.type = 'get' AND m.state = 'done'
UNION ALL
SELECT mi.partner_id,
mi.name,
mi.date,
mi.create_date AS done_date,
sd.amount + sd.discount_amount AS sale_amount,
sd.discount_amount AS benefit_amount,
sd.partner_cost AS fee,
mi.amount,
0 AS pay_amount,
0 as discount_money,
0 AS balance_amount,
Null AS note,
mi.move_id
FROM money_invoice AS mi
LEFT JOIN core_category AS c ON mi.category_id = c.id
LEFT JOIN sell_delivery AS sd ON sd.sell_move_id = mi.move_id
WHERE c.type = 'income' AND mi.state = 'done'
) AS ps)
""")
def init(self):
# union money_order(type = 'pay'), money_invoice(type = 'expense')
cr = self._cr
tools.drop_view_if_exists(cr, 'supplier_statements_report')
cr.execute("""
CREATE or REPLACE VIEW supplier_statements_report AS (
SELECT ROW_NUMBER() OVER(ORDER BY partner_id, date, amount desc) AS id,
partner_id,
name,
date,
done_date,
amount,
pay_amount,
discount_money,
balance_amount,
note
FROM
(
SELECT m.partner_id,
m.name,
m.date,
m.write_date AS done_date,
0 AS amount,
m.amount AS pay_amount,
m.discount_amount AS discount_money,
0 AS balance_amount,
m.note
FROM money_order AS m
WHERE m.type = 'pay' AND m.state = 'done'
UNION ALL
SELECT mi.partner_id,
mi.name,
mi.date,
mi.create_date AS done_date,
mi.amount,
0 AS pay_amount,
0 AS discount_money,
0 AS balance_amount,
Null AS note
FROM money_invoice AS mi
LEFT JOIN core_category AS c ON mi.category_id = c.id
WHERE c.type = 'expense' AND mi.state = 'done'
UNION ALL
SELECT ro.partner_id,
ro.name,
ro.date,
ro.write_date AS done_date,
0 AS amount,
sol.this_reconcile AS pay_amount,
0 AS discount_money,
0 AS balance_amount,
Null AS note
FROM reconcile_order AS ro
LEFT JOIN money_invoice AS mi ON mi.name = ro.name
LEFT JOIN source_order_line AS sol ON sol.payable_reconcile_id = ro.id
WHERE ro.state = 'done' AND mi.state = 'done' AND mi.name ilike 'RO%'
) AS ps)
""")
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 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._cr, self._table)
query = """
CREATE VIEW %s AS (
WITH currency_rate as (%s)
SELECT
ROW_NUMBER() OVER (ORDER BY P.id, SOL.id) AS id,
P.user_id AS user_id,
TIMESHEET_AMOUNT.project_id AS project_id,
TIMESHEET_AMOUNT.sale_line_id AS sale_line_id,
AA.id AS analytic_account_id,
AA.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.price_reduce / COALESCE(CR.rate, 1.0)) * SOL.qty_to_invoice AS amount_untaxed_to_invoice,
(SOL.price_reduce / COALESCE(CR.rate, 1.0)) * SOL.qty_invoiced AS amount_untaxed_invoiced,
TIMESHEET_AMOUNT.timesheet_unit_amount AS timesheet_unit_amount,
TIMESHEET_AMOUNT.timesheet_cost AS timesheet_cost
FROM project_project P
LEFT JOIN account_analytic_account AA ON P.analytic_account_id = AA.id
JOIN res_company C ON C.id = AA.company_id
LEFT JOIN
(
SELECT T.sale_line_id AS sale_line_id, T.project_id AS project_id
FROM project_task T
WHERE T.sale_line_id IS NOT NULL
UNION
SELECT P.sale_line_id AS sale_line_id, P.id AS project_id
FROM project_project P
WHERE P.sale_line_id IS NOT NULL
) SOL_PER_PROJECT ON P.id = SOL_PER_PROJECT.project_id
RIGHT OUTER JOIN
(
SELECT
project_id AS project_id,
so_line AS sale_line_id,
array_agg(id),
SUM(TS.unit_amount) AS timesheet_unit_amount,
SUM(TS.amount) AS timesheet_cost
FROM account_analytic_line TS
WHERE project_id IS NOT NULL
GROUP BY project_id, so_line
) TIMESHEET_AMOUNT ON TIMESHEET_AMOUNT.project_id = SOL_PER_PROJECT.project_id AND TIMESHEET_AMOUNT.sale_line_id = SOL_PER_PROJECT.sale_line_id
LEFT JOIN sale_order_line SOL ON SOL_PER_PROJECT.sale_line_id = SOL.id
LEFT JOIN sale_order S ON SOL.order_id = S.id
LEFT JOIN currency_rate CR ON (CR.currency_id = SOL.currency_id
AND CR.currency_id != C.currency_id
AND CR.company_id = SOL.company_id
AND CR.date_start <= COALESCE(S.date_order, now())
AND (CR.date_end IS NULL OR cr.date_end > COALESCE(S.date_order, now())))
)
""" % (self._table, self.env['res.currency']._select_companies_rates())
self._cr.execute(query)
def init(self):
# union money_order(type = 'pay'), money_invoice(type = 'expense')
cr = self._cr
tools.drop_view_if_exists(cr, 'supplier_statements_report')
cr.execute("""
CREATE or REPLACE VIEW supplier_statements_report AS (
SELECT ROW_NUMBER() OVER(ORDER BY partner_id,done_date) AS id,
partner_id,
name,
date,
done_date,
purchase_amount,
benefit_amount,
amount,
pay_amount,
discount_money,
balance_amount,
0 AS this_balance_amount,
note,
move_id
FROM
(
SELECT m.partner_id,
m.name,
m.date,
m.write_date AS done_date,
0 AS purchase_amount,
0 AS benefit_amount,
0 AS amount,
m.amount AS pay_amount,
m.discount_amount AS discount_money,
0 AS balance_amount,
m.note,
NULL AS move_id
FROM money_order AS m
WHERE m.type = 'pay' AND m.state = 'done'
UNION ALL
SELECT mi.partner_id,
mi.name,
mi.date,
mi.create_date AS done_date,
br.amount + br.discount_amount AS purchase_amount,
br.discount_amount AS benefit_amount,
mi.amount,
0 AS pay_amount,
0 AS discount_money,
0 AS balance_amount,
Null AS note,
mi.move_id
FROM money_invoice AS mi
LEFT JOIN core_category AS c ON mi.category_id = c.id
LEFT JOIN buy_receipt AS br ON br.buy_move_id = mi.move_id
WHERE c.type = 'expense' AND mi.state = 'done'
) AS ps)
""")
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):
cr = self._cr
tools.drop_view_if_exists(cr, 'sell_order_detail')
cr.execute("""
CREATE or REPLACE VIEW sell_order_detail AS (
SELECT MIN(wml.id) AS id,
wm.date AS date,
wm.name AS order_name,
(CASE WHEN wm.origin = 'sell.delivery.sell' THEN '销货'
ELSE '退货' END) AS type,
wm.user_id AS user_id,
wm.partner_id AS partner_id,
goods.code AS goods_code,
goods.id AS goods_id,
attr.name AS attribute,
wh.id AS warehouse_id,
SUM(CASE WHEN wm.origin = 'sell.delivery.sell' THEN wml.goods_qty
ELSE - wml.goods_qty END) AS qty,
uom.name AS uom,
wml.price AS price,
SUM(CASE WHEN wm.origin = 'sell.delivery.sell' THEN wml.amount
ELSE - wml.amount END) AS amount,
SUM(CASE WHEN wm.origin = 'sell.delivery.sell' THEN wml.tax_amount
ELSE - wml.tax_amount END) AS tax_amount,
SUM(CASE WHEN wm.origin = 'sell.delivery.sell' THEN wml.subtotal
ELSE - wml.subtotal END) AS subtotal,
SUM(CASE WHEN wm.origin = 'sell.delivery.sell' THEN wml.goods_qty
ELSE - wml.goods_qty END) * (wml.price - wml.cost_unit) AS margin,
(CASE WHEN wm.origin = 'sell.delivery.sell' THEN sd.money_state
ELSE sd.return_state END) AS money_state,
wml.note AS note,
mi.get_amount_date AS last_receipt_date
FROM wh_move_line AS wml
LEFT JOIN wh_move wm ON wml.move_id = wm.id
LEFT JOIN partner ON wm.partner_id = partner.id
LEFT JOIN goods ON wml.goods_id = goods.id
LEFT JOIN attribute AS attr ON wml.attribute_id = attr.id
LEFT JOIN warehouse AS wh ON wml.warehouse_id = wh.id
OR wml.warehouse_dest_id = wh.id
LEFT JOIN uom ON goods.uom_id = uom.id
LEFT JOIN sell_delivery AS sd ON wm.id = sd.sell_move_id
LEFT JOIN money_invoice AS mi ON mi.id = sd.invoice_id
WHERE wml.state = 'done'
AND wm.origin like 'sell.delivery%%'
AND wh.type = 'stock'
GROUP BY wm.date, wm.name, origin, wm.user_id, wm.partner_id,
goods_code, goods.id, attribute, wh.id, uom,
wml.price, wml.cost_unit, sd.money_state, sd.return_state, wml.note,
mi.get_amount_date
)
""")
def init(self):
# self._table = construction_reduced_vat_agreement_report
tools.drop_view_if_exists(self.env.cr, self._table)
self.env.cr.execute("""CREATE or REPLACE VIEW %s as (
SELECT inv.id, agg.agreement_code, cust.matricule, s_addr.zip, inv.date_invoice as date, inv.move_name as number, inv.amount_untaxed, inv.amount_tax, inv.company_id
, to_char(date_trunc('quarter', current_date)::date - 1, 'yyyy-q') = to_char(inv.date_invoice, 'yyyy-q') as last_quarter
, to_char(current_date, 'yyyy-q') = to_char(inv.date_invoice, 'yyyy-q') as current_quarter
, agg.active
, inv.partner_id
FROM account_invoice inv, res_partner cust, construction_reduced_vat_agreement agg, construction_building_asset ba, construction_building_site bs, res_partner s_addr
WHERE inv.partner_id = cust.id AND inv.reduced_vat_agreement_id = agg.id AND inv.building_asset_id = ba.id AND ba.site_id = bs.id AND bs.address_id = s_addr.id
)""" % (self._table))
def init(self):
tools.drop_view_if_exists(self._cr, 'report_pos_order')
self._cr.execute("""
CREATE OR REPLACE VIEW report_pos_order AS (
SELECT
MIN(l.id) AS id,
COUNT(*) AS nbr_lines,
s.date_order AS date,
SUM(l.qty * u.factor) AS product_qty,
SUM(l.qty * l.price_unit) AS price_sub_total,
SUM((l.qty * l.price_unit) * (100 - l.discount) / 100) AS price_total,
SUM((l.qty * l.price_unit) * (l.discount / 100)) AS total_discount,
(SUM(l.qty*l.price_unit)/SUM(l.qty * u.factor))::decimal AS average_price,
SUM(cast(to_char(date_trunc('day',s.date_order) - date_trunc('day',s.create_date),'DD') AS INT)) AS delay_validation,
s.id as order_id,
s.partner_id AS partner_id,
s.state AS state,
s.user_id AS user_id,
s.location_id AS location_id,
s.company_id AS company_id,
s.sale_journal AS journal_id,
l.product_id AS product_id,
pt.categ_id AS product_categ_id,
p.product_tmpl_id,
ps.config_id,
pt.pos_categ_id,
pc.stock_location_id,
s.pricelist_id,
s.session_id,
s.invoice_id IS NOT NULL AS invoiced
FROM pos_order_line AS l
LEFT JOIN pos_order s ON (s.id=l.order_id)
LEFT JOIN product_product p ON (l.product_id=p.id)
LEFT JOIN product_template pt ON (p.product_tmpl_id=pt.id)
LEFT JOIN product_uom u ON (u.id=pt.uom_id)
LEFT JOIN pos_session ps ON (s.session_id=ps.id)
LEFT JOIN pos_config pc ON (ps.config_id=pc.id)
GROUP BY
s.id, s.date_order, s.partner_id,s.state, pt.categ_id,
s.user_id, s.location_id, s.company_id, s.sale_journal,
s.pricelist_id, s.invoice_id, s.create_date, s.session_id,
l.product_id,
pt.categ_id, pt.pos_categ_id,
p.product_tmpl_id,
ps.config_id,
pc.stock_location_id
HAVING
SUM(l.qty * u.factor) != 0
)
""")
def init(self):
tools.drop_view_if_exists(self._cr, 'stock_lot_revaluations')
self._cr.execute("""
CREATE VIEW stock_lot_revaluations AS (
select
l.id,
r.id as revaluation_id,
l.quant_id,
l.old_cost,
l.new_cost,
r.post_date
from stock_inventory_revaluation as r
left join stock_inventory_revaluation_quant as l on l.revaluation_id=r.id
)
""")
def init(self):
tools.drop_view_if_exists(self._cr, 'hr_recruitment_report')
self._cr.execute("""
create or replace view hr_recruitment_report as (
select
min(s.id) as id,
s.active,
s.create_date as date_create,
date(s.date_closed) as date_closed,
s.date_last_stage_update as date_last_stage_update,
s.partner_id,
s.company_id,
s.user_id,
s.job_id,
s.type_id,
s.department_id,
s.priority,
s.stage_id,
s.last_stage_id,
s.medium_id,
s.source_id,
sum(salary_proposed) as salary_prop,
(sum(salary_proposed)/count(*)) as salary_prop_avg,
sum(salary_expected) as salary_exp,
(sum(salary_expected)/count(*)) as salary_exp_avg,
extract('epoch' from (s.write_date-s.create_date))/(3600*24) as delay_close,
count(*) as nbr
from hr_applicant s
group by
s.active,
s.date_open,
s.create_date,
s.write_date,
s.date_closed,
s.date_last_stage_update,
s.partner_id,
s.company_id,
s.user_id,
s.stage_id,
s.last_stage_id,
s.type_id,
s.priority,
s.job_id,
s.department_id,
s.medium_id,
s.source_id
)
""")
def init(self):
drop_view_if_exists(self._cr, 'report_workcenter_load')
self._cr.execute("""
create view report_workcenter_load as (
SELECT
min(wl.id) as id,
to_char(p.date_planned_start,'YYYY:mm:dd') as name,
SUM(wl.duration_expected) AS duration,
wl.workcenter_id as workcenter_id
FROM
mrp_workorder wl
LEFT JOIN mrp_production p
ON p.id = wl.production_id
GROUP BY
wl.workcenter_id,
to_char(p.date_planned_start,'YYYY:mm:dd')
)""")
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 WHERE ail.account_id IS NOT NULL %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, 'crm_alima_dashboard_deux')
self._cr.execute("""
CREATE VIEW crm_alima_dashboard_deux AS (
SELECT
public.crm_alima_donateur.id as id,
1 as nombre,
public.crm_alima_don."montantEur" as montant,
date_part('year', crm_alima_donateur."datePremierDon") as anneedonateur,
date_part('year', crm_alima_don."date") as annedon,
(CASE WHEN date_part('year', crm_alima_don."date") = date_part('year', crm_alima_donateur."datePremierDon")
THEN 'nouveaux donateurs' ELSE 'anciens donateurs' END) as type,
crm_alima_don.mode_versement,
crm_alima_donateur.type_de_personne
FROM
public.crm_alima_don,
public.crm_alima_donateur
where
public.crm_alima_donateur.id = public.crm_alima_don.donateur
ORDER BY
date_part('year', crm_alima_don."date")
)""")
def init(self):
tools.drop_view_if_exists(self.env.cr, 'forecast_sale_ept_report')
self.env.cr.execute(
""" CREATE or REPLACE VIEW forecast_sale_ept_report as
(
select
min(sale.id) as id,
sale.product_id as product_id,
sale.sku as sku,
sale.period_id as period_id,
sale.warehouse_id as warehouse_id,
sum (sale.forecast_sales) as forecast_sales,
fp.date_start as date,
fp.fiscalyear_id as year_id
from forecast_sale_ept sale
join requisition_period_ept fp on fp.id = sale.period_id
group by
product_id,
sku,
period_id,
warehouse_id,date,year_id
)""")
def init(self):
tools.drop_view_if_exists(self._cr, 'kw_report_one')
self._cr.execute("""
CREATE VIEW kw_report_one AS (
SELECT
SUBJECT.id AS id,
SUBJECT.subject_name AS employee_subject,
EMPLOYEE.name AS employee_name,
SUBJECT.subject_mark AS employee_marks
FROM
hr_employee AS EMPLOYEE,
kw_college AS COLLEGE,
kw_stream AS STREAM,
kw_semester AS SEMESTER,
kw_subject AS SUBJECT
WHERE
EMPLOYEE.emp_college_id = COLLEGE.id
AND COLLEGE.id = STREAM.stream_college_id
AND STREAM.id = SEMESTER.semester_stream_id
AND SEMESTER.id = SUBJECT.subject_semester_id
)
""")
def init_table(self):
tools.drop_view_if_exists(self._cr, self._name.replace(".", "_"))
select_query = """
SELECT ROW_NUMBER () OVER (ORDER BY move_id) as id, concat(so.name,' - ', t.name) as name,
so.id as order_id, m.id as move_id, so.user_id, t.sku_code,
m.partner_id as partner_id, m.product_id as product_id, ml.qty_done as done_qty,
(sl.price_unit * ml.qty_done) as cost_price,
sl.currency_id as currency_id,
DATE(m.date) as moved_date,ml.product_uom_id
FROM stock_move m INNER JOIN stock_move_line ml
ON m.id = ml.move_id
INNER JOIN sale_order_line sl ON sl.id = m.sale_line_id
INNER JOIN sale_order so ON sl.order_id = so.id
INNER JOIN product_product p ON p.id = m.product_id
INNER JOIN product_template t ON p.product_tmpl_id = t.id
WHERE m.sale_line_id IS NOT NULL AND m.origin_returned_move_id IS NOT NULL AND m.state = 'done'
AND m.scrapped IS FALSE """
sql_query = "CREATE VIEW " + self._name.replace(
".", "_") + " AS ( " + select_query + " )"
self._cr.execute(sql_query)
def init(self):
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
FROM %s
WHERE %s
GROUP BY %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._where(),
self._group_by()))
def init(self):
tools.drop_view_if_exists(self.env.cr, self._table)
# Reference contract is the one with the latest start_date.
self.env.cr.execute("""CREATE or REPLACE VIEW %s AS (
WITH contract_information AS (
SELECT DISTINCT employee_id,
company_id,
FIRST_VALUE(id) OVER w_partition AS id,
MAX(CASE WHEN state='open' THEN 1 ELSE 0 END) OVER w_partition AS is_under_contract
FROM hr_contract AS contract
WHERE contract.state <> 'cancel'
AND contract.active = true
WINDOW w_partition AS (
PARTITION BY contract.employee_id
ORDER BY
CASE
WHEN contract.state = 'open' THEN 0
WHEN contract.state = 'draft' THEN 1
WHEN contract.state = 'close' THEN 2
ELSE 3 END,
contract.date_start DESC
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
)
SELECT employee.id AS id,
employee.id AS employee_id,
contract.id AS contract_id,
contract_information.is_under_contract::bool AS is_under_contract,
employee.first_contract_date AS date_hired,
%s
FROM hr_contract AS contract
INNER JOIN contract_information ON contract.id = contract_information.id
RIGHT JOIN hr_employee AS employee
ON contract_information.employee_id = employee.id
AND contract.company_id = employee.company_id
WHERE employee.employee_type IN ('employee', 'student')
AND (employee.active = true OR contract.state='draft')
)""" % (self._table, self._get_fields()))
def init(self):
"""
Purpose: To extract database information and create tree view using the query
"""
tools.drop_view_if_exists(self._cr, 'flsp_mrp_negative_inv_report')
query = """
CREATE or REPLACE VIEW flsp_mrp_negative_inv_report AS(
SELECT
sq.id as id,
pp.id as product_id,
pt.id as product_tmpl_id,
--pt.name as pdct_name,
pp.default_code as default_code,
sq.quantity as product_qty,
uom.id as product_uom,
sq.lot_id as lot,
--spl.name as lot_name,
sq.location_id as location_id,
--sl.name as stck_name,
sl.complete_name as location
--sl.usage as usage_name
FROM stock_quant as sq
inner join product_product as pp
on sq.product_id = pp.id
inner join product_template as pt
on pp.product_tmpl_id = pt.id
inner join uom_uom as uom
on pt.uom_id = uom.id
full join stock_production_lot as spl
on sq.lot_id = spl.id
inner join stock_location as sl
on sq.location_id = sl.id
where sl.usage = 'internal' and sq.quantity < 0
);
"""
self.env.cr.execute(query)
def init(self):
tools.drop_view_if_exists(self._cr, 'margin_report')
self._cr.execute("""
CREATE OR REPLACE VIEW public.margin_report AS
SELECT
row_number() OVER () AS id,
a.product,
a.tax_rate,
a.qty,
a.cost,
a.cost_tax,
a.cost_total,
a.date,
a.sale_price,
a.sale_amount_total,
a.sale_amount_total - a.cost_total AS margin
FROM
(SELECT
pt.name AS product,
round(pol.tax * 100::numeric / pol.price_unit / pol.qty, 2) AS tax_rate,
pol.qty,
pol.cost,
round(pol.cost * (pol.tax * 100::numeric / pol.price_unit) / pol.qty / 100::numeric * pol.qty, 2) AS cost_tax,
round(pol.cost * (pol.tax * 100::numeric / pol.price_unit) / pol.qty / 100::numeric * pol.qty + pol.cost * pol.qty, 2) AS cost_total,
po.date_order AS date,
pol.price_unit AS sale_price,
round(pol.price_unit * pol.qty, 2) AS sale_amount_total
FROM
pos_order po,
pos_order_line pol,
product_product pp,
product_template pt
WHERE
pp.id = pol.product_id
AND pp.product_tmpl_id = pt.id
AND po.id = pol.order_id) a;
""")
def init(self):
tools.drop_view_if_exists(self._cr, 'ancon_commission_report')
self._cr.execute("""
CREATE OR REPLACE VIEW ancon_commissions_report AS
SELECT
AIL.id AS id,
AIL.id AS account_invoice_line_id,
AI.id AS invoice_id,
AIL.product_id AS product_id,
AIL.commission_id AS commission_id,
PT.categ_id AS category_id,
AI.user_id AS vendor_id,
AI.payment_term_id AS payment_term_id,
AIL.name AS product_name,
PC.name AS category_name,
AI.number AS invoice_number,
RP.name AS vendor_name,
APT.name AS payment_term_name,
AI.state AS invoice_state,
AI.date_invoice AS invoice_date,
AIL.quantity AS quantity,
AIL.price_unit AS product_price_unit,
AIL.price_subtotal AS subtotal,
AIL.price_total AS total,
AIL.discount AS discount,
AIL.commission_percentage AS commission_percentage,
AIL.commission_total AS commission_total
FROM
account_invoice_line AIL
INNER JOIN ancon_commission AC ON AC.id = AIL.commission_id
INNER JOIN product_product PP ON PP.id = AIL.product_id
INNER JOIN product_template PT ON PT.id = PP.product_tmpl_id
INNER JOIN product_category PC ON PC.id = PT.categ_id
INNER JOIN account_invoice AI ON AI.id = AIL.invoice_id AND AI.state LIKE 'paid'
INNER JOIN res_users RU ON RU.id = AI.user_id
INNER JOIN res_partner RP ON RP.id = RU.partner_id
INNER JOIN account_payment_term APT ON APT.id = AI.payment_term_id"""
)
def init(self):
tools.drop_view_if_exists(self._cr, self._table)
query = """
SELECT
l.id as id,
l.id as move_line_id,
am.date as date,
l.date_maturity as date_maturity,
am.ref as ref,
am.state as move_state,
l.full_reconcile_id as full_reconcile_id,
-- l.reconcile_partial_id as reconcile_partial_id,
l.move_id as move_id,
l.partner_id as partner_id,
am.company_id as company_id,
am.journal_id as journal_id,
-- p.fiscalyear_id as fiscalyear_id,
-- am.period_id as period_id,
l.account_id as account_id,
l.analytic_account_id as analytic_account_id,
a.internal_type as type,
a.user_type_id as account_type,
l.currency_id as currency_id,
l.amount_currency as amount_currency,
pa.user_id as user_id,
coalesce(l.debit, 0.0) - coalesce(l.credit, 0.0) as amount
FROM
account_move_line l
left join account_account a on (l.account_id = a.id)
left join account_move am on (am.id=l.move_id)
-- left join account_period p on (am.period_id=p.id)
left join res_partner pa on (l.partner_id=pa.id)
WHERE
-- l.state != 'draft' and
a.internal_type IN ('payable', 'receivable')
"""
self._cr.execute("""CREATE or REPLACE VIEW %s as (%s
)""" % (self._table, query))
def init(self):
tools.drop_view_if_exists(self._cr,
'ancon_daily_sales_payments_report')
self._cr.execute("""
CREATE OR REPLACE VIEW ancon_daily_sales_payments_report AS
select
ap.id as id,
aj.id as payment_journal_id,
aj.name as journal_name,
sum(CASE
WHEN am.ref LIKE '%Inversa%'
THEN (round(( (((am.amount)::DOUBLE PRECISION))::NUMERIC), 2) * ('-1'::INTEGER)::NUMERIC)
ELSE round(( (((am.amount)::DOUBLE PRECISION))::NUMERIC), 2)
END) as subtotal,
sum(CASE
WHEN am.ref LIKE '%Inversa%'
THEN (round(((((am.amount)::DOUBLE PRECISION * aj.x_porcentaje_comision))::NUMERIC), 2) * ('-1'::INTEGER)::NUMERIC)
ELSE round(((((am.amount)::DOUBLE PRECISION * aj.x_porcentaje_comision))::NUMERIC), 2)
END) as percentage_amount,
sum(CASE
WHEN am.ref LIKE '%Inversa%'
THEN (round((am.amount - (((am.amount)::DOUBLE PRECISION * aj.x_porcentaje_comision))::NUMERIC), 2) * ('-1'::INTEGER)::NUMERIC)
ELSE round((am.amount - (((am.amount)::DOUBLE PRECISION * aj.x_porcentaje_comision))::NUMERIC), 2)
END) as payment_amount,
am.store_id as store_id,
am.date as date_invoice
FROM account_move am
LEFT
JOIN account_payment ap ON ap.move_name = am.name
LEFT
JOIN res_store rs ON rs.id = ap.store_id
LEFT
JOIN res_partner rp ON rp.id = am.partner_id
LEFT JOIN account_journal aj ON aj.id = am.journal_id
where aj.at_least_one_outbound = false
group by ap.id ,aj.id, aj.name, am.store_id, am.date
""")
def init(self):
#self._table = quotation_report
tools.drop_view_if_exists(self.env.cr, self._table)
self.env.cr.execute("""CREATE or REPLACE VIEW %s as(
select
row_number() OVER () as id,to_char(to_timestamp(to_char(c.date_deadline_mnth, '999'), 'MM'), 'Mon') as mnth,
c.date_deadline_year as year,
c.planned_revenue as topline,
c.bottom_line_revenue as bottom_line,
u.id as user_id,
c.name as oppor_name,
c.opportunity_stages as oppor_stg,
c.sales_stages as sale_stage,
c.partner_id as cust,
c.crm_pricelist_id as pricelist_id,
d.oem1 as vendor,
l.id as lob,ap.id as inside_sales,
case when so.create_date is null then c.create_date::Date
else so.create_date::Date end as quote_date,
so.name as quote_name
from crm_lead c
inner join res_users u on u.id=c.user_id
inner join res_partner r on r.id=c.partner_id
inner join product_pricelist p on p.id=c.crm_pricelist_id
left join sale_order so on so.opportunity_id=c.id
left join opportunity_stages_info o on o.id=c.opportunity_stages
left join sales_stages_info s on s.id=c.sales_stages
left join approval_info ap on ap.id=c.inside_sales
left join dr_data_info d on c.id=d.crm_order_dr_id
left join line_of_business_info l on l.id=d.dr_lob where so.state='draft'
group by
mnth,year,topline,bottom_line,u.id,oppor_name,oppor_stg,sale_stage,cust,c.crm_pricelist_id,
d.oem1,ap.id,quote_date,quote_name,c.id,l.id
order by quote_date desc
)
""" % (self._table))
def init(self):
tools.drop_view_if_exists(self.env.cr, self._table)
# We disable the check for SQL injection. The only risk of sql
# injection is from 'self._table' which is not controlled by an
# external source.
# pylint:disable=E8103
self.env.cr.execute("""
CREATE OR REPLACE VIEW %s AS
""" % self._table + """
-- http://www.postgresqltutorial.com/postgresql-window-function/
select *,
case
when cancellation_id IS NULL THEN true
ELSE false
end AS is_active
from (
select min(c.id) as acquisition_id,
max(c.id) as last_sponsorship_id,
sponsorship_line_id as id,
max(p.id) as partner_id,
row_number() over (partition by max(p.id)
order by sponsorship_line_id)
as sponsor_line
from recurring_contract c JOIN res_partner p
ON c.correspondent_id = p.id
where sponsorship_line_id is not null
group by sponsorship_line_id
) AS acquisition
FULL OUTER JOIN (
select id as cancellation_id, sponsorship_line_id as id
from recurring_contract
WHERE child_id IS NOT NULL AND end_date IS NOT NULL
AND sub_sponsorship_id IS NULL AND
(activation_date IS NOT NULL OR parent_id IS NOT NULL)
) AS cancellation USING(id)
where id is not null
order by id
""")
def init(self):
tools.drop_view_if_exists(
self._cr, 'conciliacion_bancaria_disponibilidad_resumen_report')
self._cr.execute("""
CREATE OR REPLACE VIEW conciliacion_bancaria_disponibilidad_resumen_report AS (
select cuenta_id as id,
cuenta_id,
sum(debe_sin_conciliar) as debe_sin_conciliar,
sum(haber_sin_conciliar) as haber_sin_conciliar,
sum(saldo_conciliado) as saldo_conciliado,
sum(creditos_no_encontrados) as creditos_no_encontrados,
sum(debitos_no_encontrados) as debitos_no_encontrados,
sum(saldo_conciliado + debe_sin_conciliar - haber_sin_conciliar) as saldo
from (
select l.id as id,
l.account_id as cuenta_id,
case when f.fecha is null and l.debit > 0 then l.debit else 0 end as debe_sin_conciliar,
case when f.fecha is null and l.credit > 0 then l.credit else 0 end as haber_sin_conciliar,
case when f.fecha is not null then l.debit - l.credit else 0 end as saldo_conciliado,
0 as creditos_no_encontrados,
0 as debitos_no_encontrados
from account_move_line l left join conciliacion_bancaria_fecha f on (l.id = f.move_id)
where account_id in (
select id from account_account where internal_type = 'liquidity'
)
union all
select l.id as id,
l.account_id as cuenta_id,
0 as debe_sin_conciliar,
0 as haber_sin_conciliar,
0 as saldo_conciliado,
case when monto > 0 then monto else 0 end as creditos_no_encontrados,
case when monto < 0 then monto else 0 end as debitos_no_encontrados
from conciliacion_bancaria_pendientes_excel l
) as detalles
group by cuenta_id
)
""")
def init(self):
"""
This SQL view is returning useful statistics about sponsorships.
The outer query is using window functions to compute cumulative numbers
Each inner query is computing sum of numbers grouped by _date_format
:return: None
"""
tools.drop_view_if_exists(self.env.cr, self._table)
date_format = self._date_format()
# We disable the check for SQL injection. The only risk of sql
# injection is from 'self._table' which is not controlled by an
# external source.
# pylint:disable=E8103
self.env.cr.execute(
(
"""
CREATE OR REPLACE VIEW %s AS
"""
% self._table
+ """
-- Super query making windows over monthly data, for cumulative
-- numbers
-- http://www.postgresqltutorial.com/postgresql-window-function/
SELECT
c.id, c.translator_id, c.src_translation_lang_id AS src_lang,
c.translation_language_id AS dst_lang,
l1.name || ' to ' || l2.name AS language,
to_char(date_trunc(%s, c.translate_date), %s) AS study_date,
c.sponsorship_id, c.id AS correspondence_id, c.direction,
c.translate_date
FROM correspondence c
JOIN res_lang_compassion l1 ON c.src_translation_lang_id = l1.id
JOIN res_lang_compassion l2 ON c.translation_language_id = l2.id
WHERE translator_id IS NOT NULL
"""
),
date_format,
)
def init(self):
tools.drop_view_if_exists(self._cr, 'crm_sales_person_report')
self._cr.execute("""CREATE OR REPLACE VIEW crm_sales_person_report AS (
SELECT
max(id) as id,
user_id as user_id,
quote_by as quote,
phone_by as phone,
deal_by as deal,
work_by as work,
opportunity_by as opportunity,
deal_date as deal_date,
work_date as work_date,
phone_date as phone_date,
opportunity_date as opportunity_date,
quote_date as quote_date,
count(user_id) as user_by_total,
count(quote_by) as quote_by_total,
count(phone_by) as phone_by_total,
count(deal_by) as deal_by_total,
count(work_by) as work_by_total,
count(opportunity_by) as opportunity_by_total
FROM
crm_lead
WHERE type = 'opportunity'
GROUP BY
id,
user_id,
quote_by,
phone_by,
deal_by,
work_by,
deal_date,
work_date,
phone_date,
opportunity_date,
quote_date
)""")
def init(self):
cr = self._cr
tools.drop_view_if_exists(cr, 'report_stock_balance')
cr.execute("""
create or replace view report_stock_balance as (
SELECT min(line.id) as id,
goods.name as goods,
goods.id as goods_id,
goods.brand as brand_id,
loc.name as location,
line.lot as lot,
attribute.name as attribute_id,
uom.name as uom,
uos.name as uos,
wh.name as warehouse,
sum(line.qty_remaining) as goods_qty,
sum(line.uos_qty_remaining) as goods_uos_qty,
sum(line.qty_remaining * line.cost_unit) as cost
FROM wh_move_line line
LEFT JOIN warehouse wh ON line.warehouse_dest_id = wh.id
LEFT JOIN goods goods ON line.goods_id = goods.id
LEFT JOIN attribute attribute on attribute.id = line.attribute_id
LEFT JOIN uom uom ON goods.uom_id = uom.id
LEFT JOIN uom uos ON goods.uos_id = uos.id
LEFT JOIN location loc ON loc.goods_id = line.goods_id
WHERE wh.type = 'stock'
AND line.state = 'done'
AND ( goods.no_stock is null or goods.no_stock = FALSE)
AND line.qty_remaining != 0
GROUP BY wh.name, line.lot, attribute.name, goods.name, goods.id, goods.brand, loc.name, uom.name, uos.name
ORDER BY goods.name, wh.name, goods_qty asc
)
""")
def init(self):
"""
@author: Harsh Parekh 15 Jan, 2020
:return: report data in pivot view based on warehouse and product selection and drop view if already exist then.
"""
context = dict(self._context) or {}
product_ids = context.get('product_ids') or []
warehouse_ids = context.get('warehouse_ids') or []
product_list = '(' + str(product_ids).strip('[]') + ')'
warehouse_list = '(' + str(warehouse_ids).strip('[]') + ')'
tools.drop_view_if_exists(self._cr,
'product_average_daily_sale_report')
where_clause = ""
if product_ids:
where_clause += """product_id IN %s AND """ % product_list
if warehouse_ids:
where_clause += """warehouse_id IN %s AND """ % warehouse_list
query = """
CREATE or REPLACE VIEW product_average_daily_sale_report AS (
Select
id,
period_start_date,
period_stop_date,
product_id,
period_id,
ads,
monthly_sale,
sw.code as warehouse_code,
type
From
get_product_average_daily_sale as get_product_avg_sale
INNER JOIN stock_warehouse sw on sw.id = get_product_avg_sale.warehouse_id
WHERE
%s
type='actual_past_sales'
);
""" % where_clause
self._cr.execute(query)
def init(self):
tools.drop_view_if_exists(self._cr, "mis_account_analytic_line")
self._cr.execute("""
CREATE OR REPLACE VIEW mis_account_analytic_line AS (
SELECT
aal.id AS id,
aal.id AS analytic_line_id,
aal.date as date,
aal.account_id as account_id,
aal.company_id as company_id,
'posted'::VARCHAR as state,
CASE
WHEN aal.amount >= 0.0 THEN aal.amount
ELSE 0.0
END AS credit,
CASE
WHEN aal.amount < 0 THEN (aal.amount * -1)
ELSE 0.0
END AS debit,
aal.amount as balance
FROM
account_analytic_line aal
)""")
def init(self):
tools.drop_view_if_exists(self._cr, 'psdc_project_task_histories_list')
self._cr.execute("""
CREATE OR REPLACE VIEW psdc_project_task_histories_list AS
SELECT
PPTH.id AS id,
PPTH.task_id AS task_id,
PT.project_id AS project_id,
PPTH.state AS history_state,
CASE
WHEN PPTH.state LIKE 'done' THEN 'Listo'
WHEN PPTH.state LIKE 'normal' THEN 'En proceso'
WHEN PPTH.state LIKE 'blocked' THEN 'Bloqueado'
END AS history_state_formatted,
PPTH.time AS history_time,
PPTH.comments AS history_comments,
PPTH.date AS history_date,
PPTH.create_date AS history_create_date,
PPTH.latitude AS latitude,
PPTH.longitude AS longitude
FROM
psdc_project_task_history PPTH
INNER JOIN project_task PT ON PT.id = PPTH.task_id""")
def init(self):
tools.drop_view_if_exists(self._cr, "product_kks_report")
self._cr.execute(
"""
CREATE or REPLACE view product_kks_report as (
SELECT
row_number() OVER () as id,t.*
FROM (
select customer_id,kp.magasin_id,item,k.id as kks_id,ss_type_appareil_id,pc.description as type,maker_id,concat('- REP: ',pp.no_piece,' ',pp.name) as designation,
ref_fab,ref_com,reference,(select id from product_unite limit 1) as unite_id,
(select id from product_travaux limit 1) as travaux_id,
(select id from product_arret limit 1) as arret_id
from product_kks k
inner join product_kks_piece kp on k.id=kp.kks_id
left join product_category pc on pc.id=k.ss_type_appareil_id
left join product_piece pp on pp.id=kp.piece_id
group by customer_id,kp.magasin_id,item,k.id,ss_type_appareil_id,pc.description,maker_id,concat('- REP: ',pp.no_piece,' ',pp.name),
ref_fab,ref_com,reference) as t
)
"""
)
def init(self):
tools.drop_view_if_exists(self._cr, "product_kks_poids_report")
self._cr.execute(
"""
CREATE or REPLACE view product_kks_poids_report as (
SELECT row_number() OVER () as id,t.*
FROM ( SELECT t1.*, t2.value, t3.value2 FROM (
select kks.id as kks_id,arret_id, customer_id,kks.reference as designation,kks.appareil_id,unite_id
from product_kks kks
inner join product_kks_arret pka on pka.kks_id=kks.id) AS t1
LEFT JOIN
(select pt.id as appareil_id, pal.value as value from product_template pt
left join product_attribute_line pal on pal.product_tmpl_id=pt.id
inner join product_attribute pa on pa.id=pal.attribute_id
where pa.name like 'Poids Corps' or pa.name like 'Poids') AS t2 on t1.appareil_id=t2.appareil_id
LEFT JOIN
(select pt.id as appareil_id, pal.value as value2 from product_template pt
left join product_attribute_line pal on pal.product_tmpl_id=pt.id
inner join product_attribute pa on pa.id=pal.attribute_id
where pa.name like 'Poids Motorisation') AS t3 on t1.appareil_id=t3.appareil_id) AS t
)
"""
)
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):
tools.drop_view_if_exists(self.env.cr, self._table)
self.env.cr.execute("""CREATE or REPLACE VIEW %s as (
SELECT
min(ru.id) AS "id",
ru.login as name,
ru.id as user_id,
pbl.bus_id as bus_id,
pbl."action" as action,
count(pbl."action") as number
FROM
"pos_bus_log" AS pbl,
"res_users" AS ru
WHERE
pbl.user_id=ru.id
and pbl.bus_id is not Null
GROUP BY
pbl.action,
ru.login,
ru.id,
pbl.bus_id
)""" % (self._table))
def init(self):
tools.drop_view_if_exists(self._cr, 'zk_report_daily_attendance')
self._cr.execute("""
create or replace view zk_report_daily_attendance as (
select
min(z.id) as id,
z.employee_id as name,
z.write_date as punching_day,
z.address_id as address_id,
z.attendance_type as attendance_type,
z.punching_time as punching_time,
z.punch_type as punch_type
from zk_machine_attendance z
join hr_employee e on (z.employee_id=e.id)
GROUP BY
z.employee_id,
z.write_date,
z.address_id,
z.attendance_type,
z.punch_type,
z.punching_time
)
""")
def init(self):
"""
CRM Lead Report
@param cr: the current row, from the database cursor
"""
tools.drop_view_if_exists(self._cr, 'project_agile_analytic_line_view')
self._cr.execute("""
CREATE OR REPLACE VIEW project_agile_analytic_line_view AS
SELECT l.id,
l.project_id,
l.task_id,
l. type_id,
l.user_id,
l.stage_id,
l.start_date,
l.end_date,
l.company_id,
COALESCE(EXTRACT(
epoch FROM COALESCE(l.end_date, now() at time zone 'utc') -
l.start_date)/3600, 0.0::double precision
) AS duration
FROM project_agile_analytic_line l;
""")
def init(self):
tools.drop_view_if_exists(self._cr, self._table)
self._cr.execute("""
CREATE view %s as
%s
FROM
hr_timesheet_sheet_sheet hts
left join hr_employee as hr on (hts.employee_id = hr.id)
left join res_users u on u.id = hts.user_id
left join account_analytic_line aal on (aal.sheet_id = hts.id)
left join project_project pp on (aal.project_id = pp.id)
left join project_task pt on (pt.id = aal.task_id and pt.project_id = pp.id)
left join employee_time_history eth on (eth.task_id = pt.id and eth.employee_id = hr.id)
WHERE pt.active = 'true'
%s
""" % (self._table, self._select(), self._group_by()))
# (CASE WHEN ((select count(*) from employee_time_history where (task_id = pt.id and employee_id = hr.id)) > 0)
# THEN eth.related_rate/(select count(*) from employee_time_history where task_id = pt.id and employee_id = hr.id)
# ELSE eth.related_rate
# END) as cost,
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):
# self._table = account_invoice_contact_report
tools.drop_view_if_exists(self.env.cr, self._table)
self.env.cr.execute("""CREATE or REPLACE VIEW %s as (
WITH currency_rate (currency_id, rate, date_start, date_end) AS (
SELECT r.currency_id, r.rate, r.name AS date_start,
(SELECT name FROM res_currency_rate r2
WHERE r2.name > r.name AND
r2.currency_id = r.currency_id
ORDER BY r2.name ASC
LIMIT 1) AS date_end
FROM res_currency_rate r
)
%s
FROM (
%s %s %s
) AS sub
JOIN currency_rate cr ON
(cr.currency_id = sub.currency_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._select(), self._sub_select(), self._from(),
self._group_by()))
def init(self):
print("Connected")
tools.drop_view_if_exists(self.env.cr, 'piros_renewals_overview')
self.env.cr.execute("""
CREATE OR REPLACE VIEW piros_renewals_overview AS (
SELECT
row_number() OVER () as id,
sale_order_line.x_enddate as x_date,
sale_order.name as x_so,
sale_order.id as x_order_id,
rp1.commercial_company_name as x_customer,
rp2.commercial_company_name as x_end_customer,
sale_order_line.name as x_product,
sale_order_line.product_uom_qty as x_qty,
sale_order_line.x_contract as x_contract
FROM sale_order_line
JOIN sale_order ON sale_order_line.order_id = sale_order.id
JOIN res_partner rp1 ON sale_order.partner_id = rp1.id
JOIN res_partner rp2 ON sale_order.x_end_customer_id = rp2.id
WHERE sale_order.state = 'sale'
GROUP BY sale_order_line.x_enddate,sale_order.id,sale_order.name,rp1.commercial_company_name,rp2.commercial_company_name,sale_order_line.name,sale_order_line.product_uom_qty,sale_order_line.x_contract
ORDER BY x_date
)""")
