def init(self, cr):
tools.drop_view_if_exists(cr, 'hr_timesheet_report')
cr.execute("""
create or replace view hr_timesheet_report as (
select
min(t.id) as id,
l.date as date,
sum(l.amount) as cost,
sum(l.unit_amount) as quantity,
l.account_id as account_id,
l.journal_id as journal_id,
l.product_id as product_id,
l.general_account_id as general_account_id,
l.user_id as user_id,
l.company_id as company_id,
l.currency_id as currency_id
from
hr_analytic_timesheet as t
left join account_analytic_line as l ON (t.line_id=l.id)
group by
l.date,
l.account_id,
l.product_id,
l.general_account_id,
l.journal_id,
l.user_id,
l.company_id,
l.currency_id
)
""")
def init(self, cr):
tools.drop_view_if_exists(cr, "gym_class_detail_report")
cr.execute(
"""
CREATE OR REPLACE VIEW gym_class_detail_report AS (
SELECT
date,
schedule_id,
activity_id,
class_code,
week_day,
trainer_id,
trainee_id,
gym_gym_class_detail.id,
gym_gym_class.id as class_id,
value
FROM gym_gym_class_detail
INNER JOIN gym_gym_class
ON gym_gym_class_detail.gym_class_id = gym_gym_class.id
)
"""
)
def init(self, cr):
tools.drop_view_if_exists(cr, 'payslip_report')
cr.execute("""
create or replace view payslip_report as (
select
min(l.id) as id,
l.name,
p.struct_id,
p.state,
p.date_from,
p.date_to,
p.number,
p.company_id,
p.paid,
l.category_id,
l.employee_id,
sum(l.total) as total,
to_char(p.date_from, 'YYYY') as year,
to_char(p.date_from, 'MM') as month,
to_char(p.date_from, 'YYYY-MM-DD') as day,
to_char(p.date_to, 'YYYY') as to_year,
to_char(p.date_to, 'MM') as to_month,
to_char(p.date_to, 'YYYY-MM-DD') as to_day,
1 AS nbr
from
hr_payslip as p
left join hr_payslip_line as l on (p.id=l.slip_id)
where
l.employee_id IS NOT NULL
group by
p.number,l.name,p.date_from,p.date_to,p.state,p.company_id,p.paid,
l.employee_id,p.struct_id,l.category_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,
date(c.date_open) as opening_date,
date(c.create_date) as create_date,
date(c.date_last_stage_update) as date_last_stage_update,
c.user_id,
c.working_hours_open,
c.working_hours_close,
c.section_id,
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,
c.version_id as version_id,
1 as nbr,
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
WHERE c.active= 'true'
)""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'report_account_analytic_line_to_invoice')
cr.execute("""
CREATE OR REPLACE VIEW report_account_analytic_line_to_invoice AS (
SELECT
DISTINCT(to_char(l.date,'MM')) as month,
to_char(l.date, 'YYYY') as name,
MIN(l.id) AS id,
l.product_id,
l.account_id,
SUM(l.amount) AS amount,
SUM(l.unit_amount*t.list_price) AS sale_price,
SUM(l.unit_amount) AS unit_amount,
l.product_uom_id
FROM
account_analytic_line l
left join
product_product p on (l.product_id=p.id)
left join
product_template t on (p.product_tmpl_id=t.id)
WHERE
(invoice_id IS NULL) and (to_invoice IS NOT NULL)
GROUP BY
to_char(l.date, 'YYYY'), to_char(l.date,'MM'), product_id, product_uom_id, account_id
)
""")
def init(self, cr):
# self._table = sale_invoice_payment_report
tools.drop_view_if_exists(cr, self._table)
cr.execute("""CREATE or REPLACE VIEW sale_invoice_payment_report as (
select *, ((case when coalesce(order_amount, 0) = 0 then 1 when paid_amount > order_amount then 1 else paid_amount / order_amount end) * 100)::decimal(16,2) as percent_paid
from (
select *, (case when coalesce(amount_total, 0) = 0 then 0 else ((invoiced_amount / amount_total) * residual) end)::decimal(16,2) as unpaid_amount,
(case when coalesce(amount_total, 0) = 0 then 0 else invoiced_amount - ((invoiced_amount / amount_total) * residual) end)::decimal(16,2) as paid_amount
from (
select so.id as id,
so.user_id,
so.partner_id,
so.id as order_id,
so.date_order as date,
to_char(so.date_order::timestamp with time zone, 'YYYY'::text) AS year,
to_char(so.date_order::timestamp with time zone, 'MM'::text) AS month,
to_char(so.date_order::timestamp with time zone, 'YYYY-MM-DD'::text) AS day,
(case when coalesce(so.amount_untaxed, 0) = 0 then so.amount_untaxed else so.amount_untaxed end) as order_amount,
ai.id as invoice_id,
ai.amount_beforetax as invoiced_amount,
-- to be calculated
ai.amount_total,
ai.residual
from sale_order so
left outer join sale_order_invoice_rel sir on so.id = sir.order_id
left outer join account_invoice ai on ai.id = sir.invoice_id and ai.state not in ('draft','cancel')
where so.state not in ('draft', 'cancel')
) a
) b
)""")
def init(self, cr):
""" Phone Calls By User And Section
@param cr: the current row, from the database cursor,
"""
tools.drop_view_if_exists(cr, 'crm_phonecall_report')
cr.execute("""
create or replace view crm_phonecall_report as (
select
id,
to_char(c.date, 'YYYY') as name,
to_char(c.date, 'MM') as month,
to_char(c.date, 'YYYY-MM-DD') as day,
to_char(c.create_date, 'YYYY-MM-DD') as creation_date,
to_char(c.date_open, 'YYYY-MM-DD') as opening_date,
to_char(c.date_closed, 'YYYY-mm-dd') as date_closed,
c.state,
c.user_id,
c.section_id,
c.categ_id,
c.partner_id,
c.opportunity_id,
c.duration,
c.company_id,
c.priority,
1 as nbr,
date_trunc('day',c.create_date) as create_date,
extract('epoch' from (c.date_closed-c.create_date))/(3600*24) as delay_close,
extract('epoch' from (c.date_open-c.create_date))/(3600*24) as delay_open
from
crm_phonecall c
)""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'asset_asset_report')
cr.execute("""
create or replace view asset_asset_report as (
select
min(dl.id) as id,
dl.name as name,
dl.line_date as depreciation_date,
a.date_start as date_start,
a.date_remove as date_remove,
a.asset_value as asset_value,
dl.amount as depreciation_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 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)
group by
dl.amount, dl.asset_id, dl.line_date, dl.name,
a.date_start, a.date_remove, dl.move_check, a.state,
a.category_id, a.partner_id, a.company_id, a.asset_value,
a.id, a.salvage_value
)""")
def init(self, cr):
tools.drop_view_if_exists(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.
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.active AND
a.type = 'receivable' AND
l.reconcile_id is NULL AND
l.partner_id IS NOT NULL
GROUP BY
l.partner_id, l.company_id
)""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'report_pos_order')
cr.execute("""
create or replace view report_pos_order as (
select
min(l.id) as id,
count(*) as nbr,
to_date(to_char(s.date_order, 'dd-MM-YYYY'),'dd-MM-YYYY') as date,
sum(l.qty * u.factor) as product_qty,
sum(l.qty * l.price_unit) 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(16,2) 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,
to_char(s.date_order, 'YYYY') as year,
to_char(s.date_order, 'MM') as month,
to_char(s.date_order, 'YYYY-MM-DD') as day,
s.partner_id as partner_id,
s.state as state,
s.user_id as user_id,
s.shop_id as shop_id,
s.company_id as company_id,
s.sale_journal as journal_id,
l.product_id as product_id
from pos_order_line as l
left join pos_order s on (s.id=l.order_id)
left join product_template pt on (pt.id=l.product_id)
left join product_uom u on (u.id=pt.uom_id)
group by
to_char(s.date_order, 'dd-MM-YYYY'),to_char(s.date_order, 'YYYY'),to_char(s.date_order, 'MM'),
to_char(s.date_order, 'YYYY-MM-DD'), s.partner_id,s.state,
s.user_id,s.shop_id,s.company_id,s.sale_journal,l.product_id,s.create_date
having
sum(l.qty * u.factor) != 0)""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'is_comparatif_gamme_standard_generique')
cr.execute("""
CREATE OR REPLACE view is_comparatif_gamme_standard_generique AS (
SELECT
mb.id,
mb.product_tmpl_id as product_id,
mb.id as bom_id,
mr1.id as standard_routing_id,
mrw1.sequence as standard_sequence,
mrw1.name as standard_name,
mrw1.workcenter_id as standard_workcenter_id,
mrw1.is_nb_secondes as standard_nb_secondes,
mr2.id as generique_routing_id,
mrw2.sequence as generique_sequence,
mrw2.name as generique_name,
mrw2.workcenter_id as generique_workcenter_id,
mrw2.is_nb_secondes as generique_nb_secondes,
(mrw1.is_nb_secondes-mrw2.is_nb_secondes) as delta_nb_secondes
FROM mrp_bom mb inner join mrp_routing mr1 on mb.routing_id=mr1.id
inner join mrp_routing_workcenter mrw1 on mr1.id=mrw1.routing_id
inner join mrp_routing mr2 on mb.is_gamme_generique_id=mr2.id
inner join mrp_routing_workcenter mrw2 on mr2.id=mrw2.routing_id
WHERE mb.id>0 and mrw1.sequence=mrw2.sequence
)
""")
def init(self, cr):
# Order Lines
tools.drop_view_if_exists(cr, self._table)
cr.execute("""CREATE or REPLACE VIEW %s as (
select sub.id, purchase_id, invoice_id, product_id, sub.name, order_id, product_qty, product_uom,
case when curr.type_ref_base = 'smaller' then
sub.price_unit / (case when sub.type in ('in_invoice', 'in_refund') then cr.rate else cr.rate_sell end)
else
sub.price_unit * (case when sub.type in ('in_invoice', 'in_refund') then cr.rate else cr.rate_sell end)
end AS price_unit,
case when curr.type_ref_base = 'smaller' then
sub.price_subtotal / (case when sub.type in ('in_invoice', 'in_refund') then cr.rate else cr.rate_sell end)
else
sub.price_subtotal * (case when sub.type in ('in_invoice', 'in_refund') then cr.rate else cr.rate_sell end)
end AS price_subtotal
from
(select ai.type, ai.date_invoice, ai.currency_id, ail.id, po.id as purchase_id, ail.invoice_id, po.ref_order_id as order_id, ail.product_id, ail.name,
case when ai.type = 'in_invoice' then ail.quantity else -ail.quantity end as product_qty,
ail.uos_id product_uom, ail.price_unit,
case when ai.type = 'in_invoice' then price_subtotal else -price_subtotal end as price_subtotal
from account_invoice ai
join (select purchase_id purchase_id, invoice_id from purchase_invoice_rel) pil
on pil.invoice_id = ai.id
join purchase_order po on po.id = pil.purchase_id and po.is_subcontract = True and po.ref_order_id is not null
join account_invoice_line ail on ail.invoice_id = ai.id
where ai.state not in ('draft', 'cancel') and ai.type in ('in_invoice', 'in_refund')) sub
-- currency
JOIN res_currency_rate cr ON cr.currency_id = sub.currency_id
JOIN res_currency curr ON curr.id = cr.currency_id
WHERE cr.id IN (
SELECT cr2.id
FROM res_currency_rate cr2
WHERE cr2.currency_id = sub.currency_id AND (sub.date_invoice IS NOT NULL AND cr2.name <= sub.date_invoice OR sub.date_invoice IS NULL AND cr2.name <= now())
ORDER BY name DESC LIMIT 1)
)""" % (self._table,))
def init(self, cr):
tools.drop_view_if_exists(cr, 'account_followup_stat')
cr.execute("""
create or replace view account_followup_stat as (
SELECT
l.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 followup_id,
sum(l.debit) AS debit,
sum(l.credit) AS credit,
sum(l.debit - l.credit) AS balance,
l.company_id AS company_id,
l.blocked as blocked,
l.period_id AS period_id
FROM
account_move_line l
LEFT JOIN account_account a ON (l.account_id = a.id)
WHERE
a.active AND
a.type = 'receivable' AND
l.reconcile_id is NULL AND
l.partner_id IS NOT NULL
GROUP BY
l.id, l.partner_id, l.company_id, l.blocked, l.period_id
)""")
def init(self, cr):
# Order Lines
tools.drop_view_if_exists(cr, self._table)
cr.execute("""CREATE or REPLACE VIEW %s as (
select sub.id, sub.expense_id, sub.product_id, sub.name, sub.order_id, sub.unit_quantity product_qty, sub.uom_id product_uom,
case when curr.type_ref_base = 'smaller' then
sub.unit_amount / cr.rate
else
sub.unit_amount * cr.rate
end AS price_unit,
case when curr.type_ref_base = 'smaller' then
sub.price_subtotal / cr.rate
else
sub.price_subtotal * cr.rate
end AS price_subtotal
from (select x.date, x.currency_id, xl.id, expense_id, product_id, xl.name, cost_order_id as order_id, unit_quantity,
uom_id, unit_amount, unit_quantity*unit_amount as price_subtotal
from hr_expense_line xl
join hr_expense_expense x on xl.expense_id = x.id
where xl.cost_order_id is not null) sub
-- currency
JOIN res_currency_rate cr ON cr.currency_id = sub.currency_id
JOIN res_currency curr ON curr.id = cr.currency_id
WHERE cr.id IN (
SELECT cr2.id
FROM res_currency_rate cr2
WHERE cr2.currency_id = sub.currency_id AND (sub.date IS NOT NULL AND cr2.name <= sub.date OR sub.date IS NULL AND cr2.name <= now())
ORDER BY name DESC LIMIT 1)
)""" % (self._table,))
def init(self, cr):
""" Phone Calls By User And Team
@param cr: the current row, from the database cursor,
"""
tools.drop_view_if_exists(cr, 'crm_phonecall_report')
cr.execute("""
create or replace view crm_phonecall_report as (
select
id,
c.date_open as opening_date,
c.date_closed as date_closed,
c.state,
c.user_id,
c.team_id,
c.categ_id,
c.partner_id,
c.duration,
c.company_id,
c.priority,
1 as nbr,
c.create_date as create_date,
extract('epoch' from (c.date_closed-c.create_date))/(3600*24) as delay_close,
extract('epoch' from (c.date_open-c.create_date))/(3600*24) as delay_open
from
crm_phonecall c
)""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'report_day_seo')
cr.execute("""
create or replace view report_day_seo as (
SELECT
row_number() over() as id,
p.date_start,
p.date_start date_end,
extract(week FROM s.date) week_number,
l.partner_id,
l.service_id,
p.specialist_id,
p.process_type,
p.campaign,
s.top3,
s.top10,
s.fact,
s.date,
pl.name plan
FROM
process_seo p
LEFT JOIN process_launch l on (l.id=p.launch_id)
LEFT JOIN report_day_seo_statistic s on (p.campaign=s.campaign)
LEFT JOIN kpi_period k on (k.name=to_char(s.date, 'YYYY/MM') and calendar='rus')
LEFT JOIN process_seo_plan pl on (pl.period_id=k.id)
WHERE p.state='implementation' and p.campaign is not null
)""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'project_task_history_cumulative')
cr.execute(""" CREATE VIEW project_task_history_cumulative 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
)
""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'mrp_workorder')
cr.execute("""
create or replace view mrp_workorder as (
select
date(wl.date_planned) as date,
min(wl.id) as id,
mp.product_id as product_id,
p.product_tmpl_id,
t.categ_id as category_id,
sum(wl.hour) as total_hours,
avg(wl.delay) as delay,
(w.costs_hour*sum(wl.hour)) as total_cost,
wl.production_id as production_id,
wl.workcenter_id as workcenter_id,
sum(wl.cycle) as total_cycles,
count(*) as nbr,
sum(mp.product_qty) as product_qty,
wl.state as state,
mp.user_id,
mp.routing_id,
mp.bom_id
from mrp_production_workcenter_line wl
left join mrp_workcenter w on (w.id = wl.workcenter_id)
left join mrp_production mp on (mp.id = wl.production_id)
left join product_product p on (mp.product_id=p.id)
left join product_template t on (p.product_tmpl_id=t.id)
group by
w.costs_hour, mp.product_id, mp.name, mp.user_id, mp.routing_id, mp.bom_id, wl.state, wl.date_planned, wl.production_id, wl.workcenter_id, p.product_tmpl_id, t.categ_id
)""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'crm_activity_report')
cr.execute("""
CREATE OR REPLACE VIEW crm_activity_report AS (
select
m.id,
m.subtype_id,
m.author_id,
m.date,
l.user_id,
l.team_id,
l.country_id,
l.company_id,
l.stage_id,
l.partner_id,
l.type as lead_type
from
"mail_message" m
join
"crm_lead" l
on
(m.res_id = l.id)
WHERE
(m.model = 'crm.lead')
)""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'account_due_statment')
cr.execute("""
create or replace view account_due_statment as (
SELECT
l.id as id,
l.partner_id AS partner_id,
am.name AS invoice_number,
l.ref AS ref,
min(l.date) AS date_move,
max(l.date_maturity) AS date_maturity,
sum(l.debit) AS debit,
sum(l.credit) AS credit,
sum(l.debit - l.credit) AS balance,
l.company_id AS company_id,
l.period_id AS period_id
FROM
account_move_line l
LEFT JOIN account_account a ON (l.account_id = a.id)
LEFT JOIN account_move am ON (l.move_id = am.id)
WHERE
a.active AND
a.type = 'receivable' AND
l.reconcile_id is NULL AND
l.partner_id IS NOT NULL
GROUP BY
l.id, l.partner_id,am.name,l.ref, l.company_id, l.period_id
)""")
def init(self, cr):
tools.drop_view_if_exists(cr, self._table)
cr.execute("""CREATE or REPLACE VIEW %s as (
%s
FROM ( %s )
%s
)""" % (self._table, self._select(), self._from(), self._group_by()))
def init(self, cr):
# 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(cr, 'im_livechat_report')
cr.execute("""
CREATE OR REPLACE VIEW im_livechat_report AS (
SELECT
min(M.id) as id,
S.uuid as uuid,
CONCAT(C.name, ' / ', S.id) as session_name,
S.create_date as start_date,
to_char(date_trunc('hour', S.create_date), 'YYYY-MM-DD HH24:MI:SS') as start_date_hour,
EXTRACT('epoch' from ((SELECT (max(create_date)-min(create_date)) FROM im_chat_message WHERE to_id=S.id AND from_id = U.id))) as time_in_session,
EXTRACT('epoch' from ((SELECT min(create_date) FROM im_chat_message WHERE to_id=S.id AND from_id IS NOT NULL)-(SELECT min(create_date) FROM im_chat_message WHERE to_id=S.id AND from_id IS NULL))) as time_to_answer,
EXTRACT('epoch' from (max((SELECT (max(create_date)) FROM im_chat_message WHERE to_id=S.id))-S.create_date)) as duration,
(SELECT count(distinct COALESCE(from_id, 0)) FROM im_chat_message WHERE to_id=S.id) as nbr_speakers,
(SELECT count(id) FROM im_chat_message WHERE to_id=S.id) as nbr_messages,
count(M.id) as nbr_user_messages,
U.id as user_id,
S.channel_id as channel_id,
S.id as session_id
FROM im_chat_message M
LEFT JOIN im_chat_session S on (S.id = M.to_id)
LEFT JOIN res_users U on (U.id = M.from_id)
LEFT JOIN im_livechat_channel C on (S.channel_id = C.id)
WHERE S.channel_id IS NOT NULL
GROUP BY U.id, M.to_id, S.id, C.name, S.uuid
)
""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'report_sales_by_margin_pos_month')
cr.execute("""
create or replace view report_sales_by_margin_pos_month as (
select
min(pol.id) as id,
po.user_id as user_id,
pt.name as product_name,
to_char(date_trunc('month',po.date_order),'YYYY-MM-DD')::text as date_order,
sum(pol.qty) as qty,
pt.list_price-pt.standard_price as net_margin_per_qty,
(pt.list_price-pt.standard_price) *sum(pol.qty) as total
from
product_template as pt,
product_product as pp,
pos_order_line as pol,
pos_order as po
where
pol.product_id = pp.product_tmpl_id and
pp.product_tmpl_id = pt.id and
po.id = pol.order_id
group by
pt.name,
pt.list_price,
pt.standard_price,
po.user_id,
to_char(date_trunc('month',po.date_order),'YYYY-MM-DD')::text
)
""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'report_pos_rest')
cr.execute("""
create or replace view report_pos_rest as (
select
min(l.id) as id,
count(*) as nbr,
s.date_order as date,
ps.start_at as date_service,
sum(l.qty * u.factor) as product_qty,
sum(price_subtotal) as price_total,
sum(l.price_subtotal_incl) as total_incl,
sum((l.qty * l.price_unit) * (l.discount / 100)) as total_discount,
(sum(l.qty*l.price_unit)/sum(l.qty * u.factor))::decimal(16,2) 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.partner_id as partner_id,
s.state as state,
s.user_id as user_id,
s.warehouse_id as warehouse_id,
s.company_id as company_id,
s.sale_journal as journal_id,
l.product_id as product_id,
pt.categ_id,pc.parent_id as parent_categ_id
from pos_order_line as l
left join pos_order s on (s.id=l.order_id)
left join pos_session ps on (ps.id=s.session_id)
left join product_product pp on (pp.id=l.product_id)
left join product_template pt on (pt.id=pp.product_tmpl_id)
left join product_category pc on (pc.id=pt.categ_id)
left join product_uom u on (u.id=pt.uom_id)
group by
s.date_order,ps.start_at, s.partner_id,s.state,
s.user_id,s.warehouse_id,s.company_id,s.sale_journal,l.product_id,pt.categ_id,pc.parent_id,s.create_date
having
sum(l.qty * u.factor) != 0)""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'phieunhap_report')
cr.execute("""
create or replace view phieunhap_report as (
select
min(ctn.id) as id,
ctn.phieunhap_id as phieunhap_id,
ctn.nguyenlieu_id as nguyenlieu_id,
ctn.slnhap as slnhap,
ctn.dongia as dongia,
sum(ctn.slnhap * ctn.dongia) as tong,
count(pn.id) as slnl,
to_char(pn.ngaynhap, 'YYYY') as year,
to_char(pn.ngaynhap, 'MM') as month,
to_char(pn.ngaynhap, 'YYYY-MM-DD') as day
from
chitiet_nhap ctn
join phieu_nhap pn on (pn.id=ctn.phieunhap_id)
group by
ctn.phieunhap_id,
ctn.nguyenlieu_id,
pn.ngaynhap,
ctn.slnhap,
ctn.dongia
)
""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'is_pic_3mois')
cr.execute("""
CREATE OR REPLACE view is_pic_3mois AS (
SELECT
sol.id,
so.partner_id,
sol.product_id,
pt.is_category_id,
concat (pt.is_code, ' ', pt.name, ' (', im.name, ')') description,
pt.is_ref_client,
pt.is_mold_id,
sol.product_uom_qty,
sol.is_date_expedition,
sol.is_type_commande,
im.project,
(select route_id from stock_route_product srp where srp.product_id=pt.id limit 1) route_id
FROM sale_order so inner join sale_order_line sol on so.id=sol.order_id
inner join product_product pp on pp.id = sol.product_id
inner join product_template pt on pt.id = pp.product_tmpl_id
left outer join is_mold im on pt.is_mold_id = im.id
)
""")
def init(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,
line.lot as lot,
uom.name as uom,
wh.name as warehouse,
sum(line.qty_remaining) as goods_qty,
sum(line.subtotal) 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 uom uom ON line.uom_id = uom.id
WHERE line.qty_remaining > 0
AND wh.type = 'stock'
AND line.state = 'done'
GROUP BY wh.name, line.lot, goods.name, uom.name
ORDER BY goods.name, wh.name, goods_qty asc
)
""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'report_transaction_pos')
cr.execute("""
create or replace view report_transaction_pos as (
select
min(absl.id) as id,
count(absl.id) as no_trans,
sum(absl.amount) as amount,
sum((100.0-line.discount) * line.price_unit * line.qty / 100.0) as disc,
to_char(date_trunc('day',absl.create_date),'YYYY-MM-DD')::text as date_create,
po.user_id as user_id,
po.sale_journal as journal_id,
abs.journal_id as jl_id,
count(po.invoice_id) as invoice_id,
count(p.id) as product_nb
from
account_bank_statement_line as absl,
account_bank_statement as abs,
product_product as p,
pos_order_line as line,
pos_order as po
where
absl.pos_statement_id = po.id and
line.order_id=po.id and
line.product_id=p.id and
absl.statement_id=abs.id
group by
po.user_id,po.sale_journal, abs.journal_id,
to_char(date_trunc('day',absl.create_date),'YYYY-MM-DD')::text
)
""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'op_notas_bimestre_view')
cr.execute(""" create or replace view op_notas_bimestre_view AS (
(select cast((cast(Extract(Year FROM exame.data_exame) as text) || aluno.roll_number || cast(exame.classroom_id as text) || cast(exame.type_id as text)) as numeric(24,0)) as id, Extract(Year FROM exame.data_exame) as ano,
exame.course_id, exame.classroom_id, exame.type_id, exame.partner_id,
notas.student_id, cast(aluno.roll_number as int) as numero
, (select distinct b1.avaliacao from op_exam_session b1s, op_exam b1
where b1.session_id = b1s.id and b1s.bimestre = '01' and b1.student_id = notas.student_id and b1s.course_id = exame.course_id
and b1s.classroom_id = exame.classroom_id and b1s.partner_id = exame.partner_id and b1s.type_id = exame.type_id ) as bim1
, (select distinct b1.avaliacao from op_exam_session b1s, op_exam b1
where b1.session_id = b1s.id and b1s.bimestre = '02' and b1.student_id = notas.student_id and b1s.course_id = exame.course_id
and b1s.classroom_id = exame.classroom_id and b1s.partner_id = exame.partner_id and b1s.type_id = exame.type_id ) as bim2
, (select distinct b1.avaliacao from op_exam_session b1s, op_exam b1
where b1.session_id = b1s.id and b1s.bimestre = '03' and b1.student_id = notas.student_id and b1s.course_id = exame.course_id
and b1s.classroom_id = exame.classroom_id and b1s.partner_id = exame.partner_id and b1s.type_id = exame.type_id ) as bim3
, (select distinct b1.avaliacao from op_exam_session b1s, op_exam b1
where b1.session_id = b1s.id and b1s.bimestre = '04' and b1.student_id = notas.student_id and b1s.course_id = exame.course_id
and b1s.classroom_id = exame.classroom_id and b1s.partner_id = exame.partner_id and b1s.type_id = exame.type_id ) as bim4
from op_exam_session exame, op_exam notas, op_student aluno
where exame.id = notas.session_id
and aluno.id = notas.student_id
and exame.type_id is not null
and exame.classroom_id is not null
and exame.course_id is not null
and exame.partner_id is not null
and (Extract(Year FROM exame.data_exame) > 2014)
group by exame.course_id, exame.classroom_id, exame.partner_id,
notas.student_id, ano, aluno.roll_number
,exame.type_id
order by cast(aluno.roll_number as int)
))
""")
def init(self, cr):
"""
CRM Lead Report
@param cr: the current row, from the database cursor
"""
tools.drop_view_if_exists(cr, 'crm_partner_report_assign')
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 opp,
i.price_total as turnover,
i.period_id
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, cr):
"""
CRM Lead Report
@param cr: the current row, from the database cursor
"""
tools.drop_view_if_exists(cr, 'crm_lead_report')
cr.execute("""
CREATE OR REPLACE VIEW crm_lead_report AS (
SELECT
id,
c.date_deadline,
to_char(c.date_open, 'YYYY-MM-DD') as opening_date,
to_char(c.date_closed, 'YYYY-mm-dd') as date_closed,
date_trunc('day',c.date_last_stage_update) as date_last_stage_update,
c.user_id,
c.probability,
c.stage_id,
c.type,
c.company_id,
c.priority,
c.section_id,
c.channel_id,
c.type_id,
c.partner_id,
c.country_id,
c.planned_revenue,
c.planned_revenue*(c.probability/100) as probable_revenue,
date_trunc('day',c.create_date) as create_date,
extract('epoch' from (c.date_closed-c.create_date))/(3600*24) as delay_close,
abs(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
WHERE c.active = 'true'
)""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'grp_anticipos_report')
cr.execute("""
CREATE OR replace VIEW grp_anticipos_report AS (
SELECT
sa.id AS id,
sa.id AS solicitud_anticipo_id,
af.id AS anticipo_fondo_id,
ra.id AS rendicion_anticipo_id,
da.id AS devolucion_anticipo_id,
sa.name AS sa_nro_solicitud,
sa.user_uid AS sa_user_uid,
sa.fecha_ingreso AS sa_fecha_ingreso,
sa.aprobador_id AS sa_aprobador_id,
sa.operating_unit_id AS sa_operating_unit_id,
sa.currency_id AS sa_currency_id,
sa.state AS sa_state,
(SELECT SUM(amount) FROM grp_informacion_solicitud sal WHERE sal.solicitud_id = sa.id) AS sa_amount_total,
af.number AS af_number,
af.fecha_aprobacion_pago AS af_fecha_aprobacion_pago,
af.date AS af_date,
af.state AS af_state,
ra.x_sequence AS ra_sequence,
ra.entry_date AS ra_entry_date,
ra.name AS ra_name,
ra.amount AS ra_amount,
ra.amount - ra.adelanto_anticipo AS ra_diferencia,
ra.state AS ra_state,
da.number AS da_number,
da.fecha_aprobacion_pago AS da_fecha_aprobacion_pago,
da.date AS da_date,
da.state AS da_state
FROM
grp_solicitud_anticipos_fondos sa LEFT JOIN
(SELECT * FROM account_voucher WHERE type = 'payment') af ON af.solicitud_anticipos_id = sa.id LEFT JOIN
hr_expense_expense ra ON ra.solicitud_anticipos_id = sa.id LEFT JOIN
(SELECT * FROM account_voucher WHERE type = 'sale') da ON da.solicitud_anticipos_id = sa.id AND da.rendicion_anticipos_id = ra.id
)
""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'crm_opportunity_report')
cr.execute("""
CREATE OR REPLACE VIEW crm_opportunity_report AS (
SELECT
id,
c.date_deadline,
count(id) as nbr_cases,
c.date_open as opening_date,
c.date_closed as date_closed,
c.date_last_stage_update as date_last_stage_update,
c.user_id,
c.probability,
c.stage_id,
c.type,
c.company_id,
c.priority,
c.team_id,
c.campaign_id,
c.source_id,
c.medium_id,
c.partner_id,
c.country_id,
c.planned_revenue as total_revenue,
c.planned_revenue*(c.probability/100) as expected_revenue,
c.create_date as create_date,
extract('epoch' from (c.date_closed-c.create_date))/(3600*24) as delay_close,
abs(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,
c.lost_reason,
c.date_conversion as date_conversion
FROM
crm_lead c
WHERE c.active = 'true'
GROUP BY c.id
)""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'report_account_sales')
cr.execute("""create or replace view report_account_sales as (
select
min(inv_line.id) as id,
to_char(inv.date_invoice, 'YYYY') as name,
to_char(inv.date_invoice,'MM') as month,
sum(inv_line.price_subtotal) as amount_total,
inv.currency_id as currency_id,
inv.period_id,
inv_line.product_id,
sum(inv_line.quantity) as quantity,
account.id as account_id
from
account_invoice_line inv_line
inner join account_invoice inv on inv.id = inv_line.invoice_id
inner join account_account account on account.id = inv_line.account_id
where
inv.state in ('open','paid')
group by
to_char(inv.date_invoice, 'YYYY'),to_char(inv.date_invoice,'MM'),inv.currency_id, inv.period_id, inv_line.product_id, account.id
)""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'report_stock_lot_change_location')
cr.execute("""
CREATE OR REPLACE view report_stock_lot_change_location AS (
SELECT
min(quant.id) as id,
sum(quant.qty) as qty,
quant.lot_id as lot_id,
quant.product_id as product_id,
quant.location_id as location_id,
'change_location' as operation,
max(quant.in_date) as in_date
FROM
stock_quant quant
INNER JOIN stock_location location ON (quant.location_id = location.id)
WHERE quant.lot_id is not Null
AND location.usage = 'internal'
GROUP BY quant.lot_id, quant.product_id, quant.location_id
HAVING sum(quant.qty) > 0
ORDER BY quant.product_id
)
""")
def init(self, cr):
try:
with cr.savepoint():
cr.execute("CREATE EXTENSION tablefunc")
except psycopg2.Error:
pass
# View
tools.drop_view_if_exists(cr, self._table)
_sql = """
select aml.id, aml.id as move_line_id,
aml.date_maturity, aml.date, aml.partner_id,
aa.type account_type, new_title,
case when letter.l1 is not null then true else false end as l1,
letter.l1 l1_date,
case when letter.l2 is not null then true else false end as l2,
letter.l2 l2_date,
case when letter.l3 is not null then true else false end as l3,
letter.l3 l3_date
from account_move_line aml
join account_account aa on aa.id = aml.account_id
join res_partner rp on rp.id = aml.partner_id
left outer join pabi_dunning_config_title pdct
on rp.title = pdct.title_id
-- Crosstab table
left outer join (
select move_line_id, l1, l2, l3
from crosstab('
select move_line_id, letter_type, date_run
from pabi_partner_dunning_letter_line order by 1, 2
') AS final_result(
move_line_id integer, l1 date, l2 date, l3 date)
)as letter on letter.move_line_id = aml.id
where aml.state = 'valid' and aa.type in ('receivable', 'payable')
and aml.date_maturity is not null
and aml.partner_id is not null
"""
cr.execute("""CREATE or REPLACE VIEW %s as (%s)""" %
(self._table, _sql,))
def init(self, cr):
tools.drop_view_if_exists(cr, 'buy_summary_partner')
cr.execute("""
CREATE OR REPLACE VIEW buy_summary_partner AS (
SELECT MIN(wml.id) AS id,
MIN(wm.date) AS date,
s_categ.name AS s_category,
partner.name AS partner,
goods.code AS goods_code,
goods.name AS goods,
attr.name AS attribute,
wh.name AS warehouse_dest,
uos.name AS uos,
SUM(wml.goods_uos_qty) AS qty_uos,
uom.name AS uom,
SUM(wml.goods_qty) AS qty,
SUM(wml.amount) / SUM(wml.goods_qty) AS price,
SUM(wml.amount) AS amount,
SUM(wml.tax_amount) AS tax_amount,
SUM(wml.subtotal) AS subtotal
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 core_category AS s_categ ON partner.s_category_id = s_categ.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_dest_id = wh.id
LEFT JOIN uom AS uos ON goods.uos_id = uos.id
LEFT JOIN uom ON goods.uom_id = uom.id
WHERE wml.state = 'done'
AND wm.origin like 'buy%'
GROUP BY s_category,partner,goods_code,goods,attribute,warehouse_dest,uos,uom
ORDER BY partner,goods,attribute,warehouse_dest
)
""")
def init(self, cr):
"""
Display Deadline ,Responsible user, partner ,Department
@param cr: the current row, from the database cursor
"""
tools.drop_view_if_exists(cr, 'crm_helpdesk_report')
cr.execute("""
create or replace view crm_helpdesk_report as (
select
min(c.id) as id,
to_char(c.date, 'YYYY') as name,
to_char(c.date, 'MM') as month,
to_char(c.date, 'YYYY-MM-DD') as day,
to_char(c.create_date, 'YYYY-MM-DD') as create_date,
to_char(c.date_closed, 'YYYY-mm-dd') as date_closed,
c.state,
c.user_id,
c.section_id,
c.partner_id,
c.company_id,
c.priority,
c.date_deadline,
c.categ_id,
c.channel_id,
c.planned_cost,
count(*) as nbr,
extract('epoch' from (c.date_closed-c.create_date))/(3600*24) as delay_close,
(SELECT count(id) FROM mail_message WHERE model='crm.helpdesk' AND res_id=c.id AND type = 'email') AS email,
abs(avg(extract('epoch' from (c.date_deadline - c.date_closed)))/(3600*24)) as delay_expected
from
crm_helpdesk c
where c.active = 'true'
group by to_char(c.date, 'YYYY'), to_char(c.date, 'MM'),to_char(c.date, 'YYYY-MM-DD'),\
c.state, c.user_id,c.section_id,c.priority,\
c.partner_id,c.company_id,c.date_deadline,c.create_date,c.date,c.date_closed,\
c.categ_id,c.channel_id,c.planned_cost,c.id
)""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'ineco_sale_audit_delivery')
cr.execute("""
CREATE OR REPLACE VIEW ineco_sale_audit_delivery AS
select
sp.id,
garment_order_no,
amount_untaxed,
(select coalesce(sum(amount_untaxed),0.00) from account_invoice
where account_invoice.garment_order_no = so.garment_order_no and
type = 'out_invoice' and
state not in ('cancel')) as invoice_amount_untaxed,
sp.name,
sp.batch_no,
(select sum(product_uom_qty) from sale_order_line
join product_product pp on pp.id = sale_order_line.product_id
join product_template pt on pt.id = pp.product_tmpl_id
where order_id = so.id and pt.type not in ('service')
) as sale_quantity,
case
when sp.type = 'in' then -sp.quantity
when sp.type = 'out' then sp.quantity
else
sp.quantity
end as delivery_quantity
from
sale_order so
left join stock_picking sp on sp.sale_id = so.id
where
extract(year from garment_order_date) >= 2015
and so.state not in ('cancel')
and sp.type not in ('internal')
and sp.invoice_state = '2binvoiced'
and so.amount_untaxed > 0
order by
so.garment_order_no,
sp.batch_no
""")
def init(self, cr):
"""
CRM Lead Report
@param cr: the current row, from the database cursor
"""
tools.drop_view_if_exists(cr, 'crm_lead_report_assign')
cr.execute("""
CREATE OR REPLACE VIEW crm_lead_report_assign AS (
SELECT
c.id,
to_char(c.create_date, 'YYYY-MM-DD') as creation_date,
to_char(c.date_open, 'YYYY-MM-DD') as opening_date,
to_char(c.date_closed, 'YYYY-mm-dd') as date_closed,
c.date_assign,
c.user_id,
c.probability,
c.probability as probability_max,
c.stage_id,
c.type,
c.company_id,
c.priority,
c.section_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,
date_trunc('day',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, cr):
tools.drop_view_if_exists(cr, 'is_sale_order_line')
cr.execute("""
CREATE OR REPLACE view is_sale_order_line AS (
select sol.id,
sol.is_date_expedition as date_expedition,
sol.is_date_livraison as date_livraison,
sol.is_client_order_ref as client_order_ref,
so.partner_id as partner_id,
pt.id as product_id,
pt.segment_id as segment_id,
pt.is_category_id as is_category_id,
pt.is_gestionnaire_id as is_gestionnaire_id,
pt.is_mold_id as mold_id,
pt.is_ref_client as ref_client,
sol.product_uom_qty as product_uom_qty,
sol.product_uom as product_uom,
sol.price_unit as price_unit,
(sol.price_unit*sol.product_uom_qty) as price_subtotal,
get_amortissement_moule_a_date(rp.is_code, pt.id, sol.is_date_expedition) as amortissement_moule,
get_amt_interne_a_date(rp.is_code, pt.id, sol.is_date_expedition) as amt_interne,
get_cagnotage_a_date(rp.is_code, pt.id, sol.is_date_expedition) as cagnotage,
get_amortissement_moule_a_date(rp.is_code, pt.id, sol.is_date_expedition)*sol.product_uom_qty as montant_amt_moule,
get_amt_interne_a_date(rp.is_code, pt.id, sol.is_date_expedition)*sol.product_uom_qty as montant_amt_interne,
get_cagnotage_a_date(rp.is_code, pt.id, sol.is_date_expedition)*sol.product_uom_qty as montant_cagnotage,
get_cout_act_matiere_st(pp.id)*sol.product_uom_qty as montant_matiere,
so.id as order_id,
sol.id as order_line_id,
so.state as state
from sale_order so inner join sale_order_line sol on so.id=sol.order_id
inner join product_product pp on sol.product_id=pp.id
inner join product_template pt on pp.product_tmpl_id=pt.id
inner join res_partner rp on so.partner_id=rp.id
)
""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'asset_asset_report')
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.purchase_date as purchase_date,
(CASE WHEN (select min(d.id) from account_asset_depreciation_line as d
left join account_asset_asset as ac ON (ac.id=d.asset_id)
where a.id=ac.id) = min(dl.id)
THEN a.purchase_value
ELSE 0
END) as gross_value,
dl.amount as depreciation_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 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)
group by
dl.amount,dl.asset_id,dl.depreciation_date,dl.name,
a.purchase_date, dl.move_check, a.state, a.category_id, a.partner_id, a.company_id,
a.purchase_value, a.id, a.salvage_value
)""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'hr_timesheet_report')
cr.execute("""
create or replace view hr_timesheet_report as (
select
min(t.id) as id,
l.date as date,
to_char(l.date, 'YYYY-MM-DD') as day,
to_char(l.date,'YYYY') as year,
to_char(l.date,'MM') as month,
sum(l.amount) as cost,
sum(l.unit_amount) as quantity,
l.account_id as account_id,
a.project_analytic_account_id as
project_analytic_account_id,
l.journal_id as journal_id,
l.product_id as product_id,
l.general_account_id as general_account_id,
l.user_id as user_id,
l.company_id as company_id,
l.currency_id as currency_id
from
hr_analytic_timesheet as t
left join account_analytic_line as l ON (t.line_id=l.id)
left join account_analytic_account a ON
(l.account_id = a.id)
group by
l.date,
l.account_id,
a.project_analytic_account_id,
l.product_id,
l.general_account_id,
l.journal_id,
l.user_id,
l.company_id,
l.currency_id
)
""")
def init(self, cr):
tools.drop_view_if_exists(cr, self._table)
cr.execute("""CREATE or REPLACE VIEW %s as (
select row_number() over (order by rpt.chart_view,
rpt.fiscalyear_id) as id,
rpt.chart_view, rpt.fiscalyear_id,
coalesce(plan, 0.0) as plan,
(select coalesce(sum(policy_amount), 0.0)
from budget_policy
where fiscalyear_id = rpt.fiscalyear_id
and chart_view = rpt.chart_view) as policy,
coalesce(released, 0.0) as released,
coalesce(pr_commit, 0.0) as pr_commit,
coalesce(po_commit, 0.0) as po_commit,
coalesce(exp_commit, 0.0) as exp_commit,
coalesce(actual_total, 0.0) as actual_total,
coalesce(balance, 0.0) as balance,
coalesce(total, 0.0) as commit_and_actual,
case when total = 0 then 0
else (actual_total/total) * 100 end as actual_percent,
coalesce(pr_commit+po_commit+exp_commit, 0.0)
as commit_total,
case when total = 0 then 0 else
coalesce(pr_commit+po_commit+exp_commit, 0.0)/total * 100
end as commit_percent
from
(select chart_view, fiscalyear_id,
sum(planned_amount) as plan,
sum(released_amount) as released,
sum(amount_pr_commit) as pr_commit,
sum(amount_po_commit) as po_commit,
sum(amount_exp_commit) as exp_commit,
sum(amount_actual) as actual_total,
sum(amount_balance) as balance,
sum(amount_consumed) as total
from budget_monitor_report
where budget_method = 'expense'
group by chart_view, fiscalyear_id) rpt)""" % self._table)
def init(self, cr):
"""
CRM Lead Report
@param cr: the current row, from the database cursor
"""
tools.drop_view_if_exists(cr, 'ineco_crm_dashboard_2')
cr.execute("""
CREATE OR REPLACE VIEW ineco_crm_dashboard_2 AS (
select
date_part('day', cl.date_deadline) as day,
date_part('month', cl.date_deadline) as month,
date_part('year', cl.date_deadline) as year,
rp_ru.id as saleman_id,
ccs.id as stage_id,
sum(cl.planned_revenue) as planned_revenue,
cl.state
from crm_lead cl
left join res_users ru on cl.user_id = ru.id
left join res_partner rp_ru on ru.id = rp_ru.id
left join crm_case_stage ccs on cl.stage_id = ccs.id
where date_part('year', cl.date_deadline) = date_part('year', now())
and ccs.id = 6
group by
EXTRACT(day FROM cl.date_deadline),
EXTRACT(MONTH FROM cl.date_deadline),
EXTRACT(year FROM cl.date_deadline),
rp_ru.id,
ccs.id,
cl.state
order by
rp_ru.id,
ccs.id,
date_part('year', cl.date_deadline),
date_part('month', cl.date_deadline),
date_part('day', cl.date_deadline),
cl.state
)""")
def init(self, cr):
""" Display Number of cases And Section Name
@param cr: the current row, from the database cursor,
"""
tools.drop_view_if_exists(cr, 'crm_claim_report')
cr.execute("""
create or replace view crm_claim_report as (
select
min(c.id) as id,
c.date as claim_date,
c.date_closed as date_closed,
c.date_deadline as date_deadline,
c.user_id,
c.stage_id,
c.section_id,
c.partner_id,
c.company_id,
c.categ_id,
c.sub_categ_id,
c.name as subject,
count(*) as nbr,
c.priority as priority,
c.type_action as type_action,
c.create_date as create_date,
c.eq_costs,
c.eq_waste_parts,
c.eq_good_parts,
avg(extract('epoch' from (c.date_closed-c.create_date)))/(3600*24) as delay_close,
(SELECT count(id) FROM mail_message WHERE model='crm.claim' AND res_id=c.id) AS email,
extract('epoch' from (c.date_deadline - c.date_closed))/(3600*24) as delay_expected
from
crm_claim c
group by c.date,\
c.user_id,c.section_id, c.stage_id,\
c.categ_id, c.sub_categ_id,c.partner_id,c.company_id,c.create_date,
c.priority,c.type_action,c.date_deadline,c.date_closed,c.id
)""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'is_comparatif_cde_draft_done')
cr.execute("""
CREATE OR REPLACE view is_comparatif_cde_draft_done AS (
select
sol2.id,
pt.id product_id,
sol2.is_date_livraison date_livraison,
so.id order_id,
sol2.qty_done qt_commande,
sol2.qty_draft qt_livree
from (
select
sol.id,
sol.product_id,
sol.is_date_livraison,
sol.order_id,
sol.product_uom_qty qty_done,
(
select sol2.product_uom_qty
from sale_order_line sol2
where
sol2.is_type_commande='ferme' and
sol2.state='draft' and
sol2.product_id=sol.product_id and
sol2.is_date_livraison=sol.is_date_livraison
limit 1
) qty_draft
from sale_order_line sol
where is_type_commande='ferme' and state='done'
order by product_id
) sol2 inner join product_product pp on sol2.product_id=pp.id
inner join product_template pt on pp.product_tmpl_id=pt.id
inner join sale_order so on sol2.order_id=so.id
where sol2.qty_draft>0
order by pt.is_code, sol2.is_date_livraison
)
""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'annexure_2b_report')
cr.execute("""CREATE OR REPLACE view annexure_2b_report AS (
select
row_number() OVER () AS id,
ai.date_invoice AS date,
ai.partner_id as partner_id,
rp.tin_no as tin_no,
ait.invoice_id as invoice_id,
ait.account_id as account_id,
ait.base as base,
ait.amount as amount,
ait.base_code_id as base_code_id,
ait.base_amount as base_amount,
ait.tax_code_id as tax_code_id,
ait.tax_amount as tax_amount
FROM
account_invoice_line ail
LEFT JOIN account_invoice ai ON (ail.invoice_id = ai.id)
LEFT JOIN account_invoice_tax ait ON (ait.invoice_id = ai.id)
LEFT JOIN res_partner rp ON (ai.partner_id = rp.id)
WHERE ai.type = 'out_invoice'
GROUP BY ail.id,
ai.date_invoice,
ai.partner_id,
rp.tin_no,
ail.account_id,
ait.invoice_id,
ait.account_id,
ait.base,
ait.amount,
ait.base_amount,
ait.tax_amount,
ait.base_code_id,
ait.tax_code_id
)""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'report_pos_order')
cr.execute("""
create or replace view report_pos_order as (
select
min(l.id) as id,
count(*) as nbr,
to_date(to_char(s.date_order, 'dd-MM-YYYY'),'dd-MM-YYYY') as date,
sum(l.qty * u.factor) as product_qty,
sum(l.qty * l.price_unit) as total_wo_disc,
sum(l.qty * l.price_unit * (100.0-l.discount) / 100.0) 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(16,2) 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,
to_char(s.date_order, 'YYYY') as year,
to_char(s.date_order, 'MM') as month,
to_char(s.date_order, 'YYYY-MM-DD') as day,
s.partner_id as partner_id,
s.state as state,
s.user_id as user_id,
s.shop_id as shop_id,
s.company_id as company_id,
s.sale_journal as journal_id,
l.product_id as product_id,
s.section_id as section_id,
s.event_id as event_id
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)
group by
to_char(s.date_order, 'dd-MM-YYYY'),to_char(s.date_order, 'YYYY'),to_char(s.date_order, 'MM'),
to_char(s.date_order, 'YYYY-MM-DD'), s.partner_id,s.state,
s.user_id,s.shop_id,s.company_id,s.sale_journal,l.product_id,s.create_date,
s.section_id, s.event_id
having
sum(l.qty * u.factor) != 0)""")
def init(self, cr):
drop_view_if_exists(cr, "account_query_payable_aging")
strSQL = """
CREATE OR REPLACE VIEW account_query_payable_aging AS (
SELECT
a.id AS id,
a.name AS name,
a.move_id AS move_id,
a.account_id AS account_id,
b.company_id AS company_id,
b.date as date,
a.date_maturity as date_due,
b.journal_id AS journal_id,
a.partner_id AS partner_id,
b.period_id AS period_id,
CASE
WHEN a.currency_id IS NULL
THEN e.currency_id
ELSE a.currency_id
END AS respective_currency_id,
e.currency_id AS base_currency_id,
a.reconcile_id AS reconcile_id,
a.reconcile_partial_id AS reconcile_partial_id,
a.debit AS debit,
a.credit AS credit,
a.amount_currency AS amount_currency,
b.state AS state
FROM account_move_line AS a
JOIN account_move AS b ON a.move_id = b.id
JOIN account_journal AS c ON b.journal_id = c.id
JOIN account_account AS d ON a.account_id = d.id
JOIN res_company AS e ON b.company_id = e.id
WHERE (d.type = 'payable') AND
(b.state = 'posted') AND
(a.credit > 0)
)
"""
cr.execute(strSQL)
def init(self, cr):
drop_view_if_exists(cr, 'account_query_receivable_aging')
strSQL = """
CREATE OR REPLACE VIEW account_query_receivable_aging AS (
SELECT
A.id AS id,
A.name AS name,
A.move_id AS move_id,
A.account_id AS account_id,
B.company_id AS company_id,
B.date as date,
A.date_maturity as date_due,
B.journal_id AS journal_id,
A.partner_id AS partner_id,
B.period_id AS period_id,
CASE
WHEN A.currency_id IS NULL
THEN E.currency_id
ELSE A.currency_id
END AS respective_currency_id,
E.currency_id AS base_currency_id,
A.reconcile_id AS reconcile_id,
A.reconcile_partial_id AS reconcile_partial_id,
A.debit AS debit,
A.credit AS credit,
A.amount_currency AS amount_currency,
B.state AS state
FROM account_move_line AS A
JOIN account_move AS B ON A.move_id = B.id
JOIN account_journal C ON B.journal_id = C.id
JOIN account_account D ON A.account_id = D.id
JOIN res_company E ON B.company_id = E.id
WHERE (D.type = 'receivable') AND
(B.state = 'posted') AND
(A.debit > 0)
)
"""
cr.execute(strSQL)
def init(self, cr):
tools.drop_view_if_exists(cr, 'od_hr_document_history')
cr.execute("""
create or replace view od_hr_document_history as (
select ROW_NUMBER () OVER (ORDER BY od_holiday_document_line.id ) AS id,
document_type_id as document_type_id,recieved_date as process_date,hr_holidays.employee_id as employee_id,'issue' as doc_action,'Leave' as action_type
from od_holiday_document_line
join hr_holidays on hr_holidays.id = od_holiday_document_line.holiday_id
join od_employee_document_type on od_employee_document_type.id = od_holiday_document_line.document_type_id
where recieved <> 't' and issued = 't' and od_employee_document_type.custodian = 'company'
UNION
select ROW_NUMBER () OVER (ORDER BY od_holiday_document_line.id ) AS id, document_type_id,issued_date as process_date,hr_holidays.employee_id,'received' as doc_action,'Leave' as action_type
from od_holiday_document_line
join hr_holidays on hr_holidays.id = od_holiday_document_line.holiday_id
join od_employee_document_type on od_employee_document_type.id = od_holiday_document_line.document_type_id
where recieved = 't' and od_employee_document_type.custodian = 'company'
UNION
select ROW_NUMBER () OVER (ORDER BY od_document_request.id ) AS id, document_type_id,return_date as process_date,employee_id,'received' as doc_action,'Document' as action_type
from od_document_request
join od_employee_document_type on od_employee_document_type.id = od_document_request.document_type_id
where is_returned ='t' and od_employee_document_type.custodian = 'company'
UNION
select ROW_NUMBER () OVER (ORDER BY od_document_request.id ) AS id, document_type_id,issued_date as process_date,employee_id,'issue' as doc_action,'Document' as action_type
from od_document_request
join od_employee_document_type on od_employee_document_type.id = od_document_request.document_type_id
where is_returned <> 't' and is_issued = 't' and od_employee_document_type.custodian = 'company'
UNION
select ROW_NUMBER () OVER (ORDER BY od_employee_joining_document_line.id ) AS id, document_type_id,recieved_date as process_date,od_employee_joining.employee_id,'received' as doc_action,'Joining' as action_type
FROM od_employee_joining_document_line
join od_employee_joining on od_employee_joining_document_line.joining_id =od_employee_joining.id
join od_employee_document_type on od_employee_document_type.id = od_employee_joining_document_line.document_type_id
where recieved = 't' and od_employee_document_type.custodian = 'company'
)
""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'academic_evaluation_analysis')
cr.execute("""
create or replace view academic_evaluation_analysis as (
SELECT
survey_user_input_question_score.id as id,
survey_user_input.score as avg_score,
survey_user_input.score as min_score,
survey_user_input.score as max_score,
survey_user_input.survey_id as survey_id,
survey_user_input.partner_id as partner_id,
survey_user_input.state as input_state,
academic_observation_category.dont_consider as dont_consider,
academic_group_evaluation.group_id as group_id,
academic_group.level_id as course_level_id,
academic_group_evaluation.state as group_evaluation_state,
academic_group.company_id,
res_partner.disabled_person as disabled_person,
survey_survey.is_evaluation as is_evaluation,
survey_survey.stage_id as survey_stage_id,
survey_survey.period_id as period_id
FROM survey_user_input_question_score
INNER JOIN survey_user_input
on survey_user_input_question_score.user_input_id = survey_user_input.id
INNER JOIN survey_survey
on survey_user_input.survey_id = survey_survey.id
FULL JOIN academic_group_evaluation
on survey_user_input.group_evaluation_id = academic_group_evaluation.id
FULL JOIN academic_group
on academic_group_evaluation.group_id = academic_group.id
LEFT JOIN academic_observation_category
on survey_user_input.observation_category_id = academic_observation_category.id
INNER JOIN res_partner
on survey_user_input.partner_id = res_partner.id
LEFT JOIN academic_level
on academic_group.level_id = academic_level.id
)
""")
def init(self, cr):
""" Event Question main report """
tools.drop_view_if_exists(cr, 'event_question_report')
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, cr):
# self._table = account_invoice_report
tools.drop_view_if_exists(cr, self._table)
cr.execute("""CREATE or REPLACE VIEW %s as (
WITH currency_rate (currency_id, rate, date_start, date_end) AS (
SELECT r.currency_id, 1/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, cr):
tools.drop_view_if_exists(cr, 'mrp_workorder')
cr.execute("""
create or replace view mrp_workorder as (
select
to_date(to_char(wl.date_planned, 'MM-dd-YYYY'),'MM-dd-YYYY') as date,
min(wl.id) as id,
mp.product_id as product_id,
sum(wl.hour) as total_hours,
avg(wl.delay) as delay,
(w.costs_hour*sum(wl.hour)) as total_cost,
wl.production_id as production_id,
wl.workcenter_id as workcenter_id,
sum(wl.cycle) as total_cycles,
count(*) as nbr,
sum(mp.product_qty) as product_qty,
wl.state as state
from mrp_production_workcenter_line wl
left join mrp_workcenter w on (w.id = wl.workcenter_id)
left join mrp_production mp on (mp.id = wl.production_id)
group by
w.costs_hour, mp.product_id, mp.name, wl.state, wl.date_planned, wl.production_id, wl.workcenter_id
)""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'grp_paid_consult')
cr.execute("""
CREATE OR replace VIEW grp_paid_consult AS (
SELECT
avl.id AS id,
av.id AS voucher_id,
avl.id AS voucher_line_id,
av.journal_id AS journal_id,
aj.operating_unit_id AS voucher_ue_id,
ai.id AS invoice_id,
ai.operating_unit_id AS invoice_ue_id
FROM account_voucher AS av
INNER JOIN account_voucher_line avl ON avl.voucher_id = av.id
INNER JOIN account_move_line aml ON avl.move_line_id = aml.id
INNER JOIN account_move am ON aml.move_id = am.id
INNER JOIN account_invoice ai ON ai.move_id = am.id
INNER JOIN account_journal aj ON av.journal_id = aj.id
WHERE av.state = 'posted' AND av."type" = 'payment'
AND aj.operating_unit_id <> ai.operating_unit_id AND avl.amount > 0
ORDER BY voucher_id
)
""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'report_analytic_account_close')
cr.execute("""
create or replace view report_analytic_account_close as (
select
a.id as id,
a.id as name,
a.state as state,
sum(l.unit_amount) as quantity,
sum(l.amount) as balance,
a.partner_id as partner_id,
a.quantity_max as quantity_max,
a.date as date_deadline
from
account_analytic_line l
right join
account_analytic_account a on (l.account_id=a.id)
group by
a.id,a.state, a.quantity_max,a.date,a.partner_id
having
(a.quantity_max>0 and (sum(l.unit_amount)>=a.quantity_max)) or
a.date <= current_date
)""")
def init(self, cr):
tools.drop_view_if_exists(cr, self._table)
cr.execute("""CREATE or REPLACE VIEW %s as (
select min(ct.id) id,
c.state,
ct.cert_id,
c.income_tax_form,
ap.name as wht_period,
c.date as date_value,
round(sum(ct.amount)/sum(ct.base)*100) as tax_percent,
ct.wht_cert_income_type, ct.wht_cert_income_desc,
case when c.state != 'cancel'
then sum(ct.base) else 0.0 end as base,
case when c.state != 'cancel'
then sum(ct.amount) else 0.0 end as tax
from account_wht_cert c join wht_cert_tax_line ct
on ct.cert_id = c.id
left outer join account_period ap on ap.id = c.period_id
where c.state != 'draft'
group by c.period_id, c.income_tax_form, ct.cert_id,
ap.name, c.date, ct.wht_cert_income_type,
ct.wht_cert_income_desc, c.state
)""" % (self._table, ))
def init(self, cr):
"""Initialize the sql view for the OpenAcademy """
tools.drop_view_if_exists(cr, 'report_openacademy')
# TOFIX this request won't select events that have no registration
cr.execute(""" CREATE VIEW report_openacademy AS (
SELECT
s.id::varchar || '/' || coalesce(a.id::varchar,'') AS id,
c.id AS course_id,
c.responsible_id AS responsible_id,
s.instructor_id AS instructor_id,
s.name AS name_session,
s.start_date AS start_date,
s.seats AS seats,
s.duration AS duration,
a.partner_id AS partner_id,
a.id AS attendee_id
FROM
openacademy_session s
JOIN openacademy_attendee a ON (a.session_id=s.id)
LEFT JOIN openacademy_course c ON (s.course_id=c.id)
)
""")