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_address a WHERE a.partner_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.section_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):
tools.drop_view_if_exists(cr, 'account_summary')
cr.execute("""
create or replace view account_summary as (
select
l.id as id,
am.date as date,
to_char(am.date, 'YYYY') as year,
p.fiscalyear_id as fiscalyear_id,
am.period_id as period_id,
l.account_id as account_id,
a.code_2 as account_code,
l.debit as debit,
l.credit as credit,
l.debit-l.credit as balance,
l.credit-l.debit as balance_inv
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)
where l.state != 'draft'
)
""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'account_move_line_report')
cr.execute("""
create or replace view account_move_line_report as (
select
aml.id as id,
aml.name as name,
aml.date as date,
aml.account_id as account_id,
aml.currency_id as currency_id,
aml.debit as debit,
aml.credit as credit,
aml.ref as ref,
aml.journal_id as journal_id,
aml.period_id as period_id,
aml.reconcile_id as reconcile_id,
aml.move_id as move_id,
aml.tot_balance as tot_balance,
res.id as partner_id
from account_move_line as aml
left join res_partner res on (aml.partner_id=res.id)
group by
aml.id,
res.id
)
""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'report_vote')
cr.execute("""
create or replace view report_vote as (
select
min(iv.id) as id,
count(*) as nbr,
to_date(to_char(ii.open_date, 'dd-MM-YYYY'),'dd-MM-YYYY') as date,
to_char(ii.open_date, 'YYYY') as year,
to_char(ii.open_date, 'MM') as month,
to_char(ii.open_date, 'YYYY-MM-DD') as day,
iv.user_id as user_id,
iv.idea_id as idea_id,
ii.state as idea_state,
ii.user_id as creater_id,
ii.category_id,
(sum(CAST(iv.score as integer))/count(iv.*)) as score
from
idea_vote as iv
left join idea_idea as ii on (ii.id = iv.idea_id)
group by
iv.id ,to_char(ii.open_date, 'dd-MM-YYYY'),to_char(ii.open_date, 'YYYY'),
to_char(ii.open_date, 'MM'),to_char(ii.open_date, 'YYYY-MM-DD'),ii.state,
iv.user_id,ii.user_id,ii.category_id,iv.idea_id
)
""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'bpa_summary')
cr.execute("""
CREATE OR REPLACE view bpa_summary AS (
SELECT
row_number() OVER () as id,
a.type_of_charge,
d.id as invoice_id,
d.date_invoice as bill_date,
d.date_due as due_date,
b.id as invoice_related_id,
b.bl_number,
b.bl_date,
a.picking_related_id,
d.partner_id,
d.currency_id,
sum(a.price_subtotal) as amount
FROM
account_invoice_line a
LEFT JOIN account_invoice b ON b.id=a.invoice_related_id
LEFT JOIN stock_picking c ON c.id=a.picking_related_id
LEFT JOIN account_invoice d ON d.id=a.invoice_id
WHERE
d.type='in_invoice' and a.type_of_charge is not NULL and d.state not in ('cancel')
GROUP BY
d.id, d.partner_id, a.picking_related_id, b.id,
b.bl_number, b.bl_date, d.date_invoice,
d.date_due, d.currency_id, a.type_of_charge
ORDER BY
d.partner_id, a.picking_related_id, b.id
)
""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'report_account_invoice_product')
cr.execute("""
create or replace view report_account_invoice_product as (
select
min(l.id) as id,
i.create_date as date,
to_char(date_trunc('day',i.date_invoice), 'YYYY') as year,
to_char(date_trunc('day',i.date_invoice), 'MM') as month,
to_char(date_trunc('day',i.date_invoice), 'YYYY-MM-DD') as day,
i.type,
i.state,
l.product_id,
t.categ_id,
i.partner_id,
sum(l.quantity * l.price_unit * (1.0 - l.discount/100.0)) as amount,
sum(l.quantity * l.cost_price) as cost_price,
sum((l.quantity * l.price_unit * (1.0 - l.discount/100.0) - (l.quantity * l.cost_price))) as margin,
sum(l.quantity) as quantity
from account_invoice i
left join account_invoice_line l on (i.id = l.invoice_id)
left join product_product p on (p.id = l.product_id)
left join product_template t on (t.id = p.product_tmpl_id)
group by t.categ_id,i.partner_id,l.product_id, i.date_invoice, i.type, i.state,i.create_date
)
""")
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,
to_char(c.create_date, 'YYYY') as name,
to_char(c.create_date, 'MM') as month,
to_char(c.create_date, 'YYYY-MM-DD') as day,
to_char(c.date_open, 'YYYY-MM-DD') as opening_date,
to_char(c.create_date, 'YYYY-MM-DD') as creation_date,
c.state,
c.user_id,
c.working_hours_open,
c.working_hours_close,
c.section_id,
c.stage_id,
to_char(c.date_closed, 'YYYY-mm-dd') 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_id,
c.task_id,
date_trunc('day',c.create_date) as create_date,
extract('epoch' from (c.date_open-c.create_date))/(3600*24) as delay_open,
extract('epoch' from (c.date_closed-c.date_open))/(3600*24) 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_timesheet_task_user')
cr.execute(""" create or replace view report_timesheet_task_user as (
select
((r.id*12)+to_number(months.m_id,'99'))::integer as id,
months.name as name,
r.id as user_id,
to_char(to_date(months.name, 'YYYY/MM/DD'),'YYYY') as year,
to_char(to_date(months.name, 'YYYY/MM/DD'),'MM') as month,
(select sum(hours) from project_task_work where user_id = r.id and date between to_date(months.name, 'YYYY/MM/DD') and (to_date(months.name, 'YYYY/MM/DD') + interval '1 month' -
interval '1 day') ) as task_hrs
from res_users r,
(select to_char(p.date,'YYYY-MM-01') as name,
to_char(p.date,'MM') as m_id
from project_task_work p
union
select to_char(h.name,'YYYY-MM-01') as name,
to_char(h.name,'MM') as m_id
from hr_timesheet_sheet_sheet_day h) as months
group by
r.id,months.m_id,months.name,
to_char(to_date(months.name, 'YYYY/MM/DD'),'YYYY') ,
to_char(to_date(months.name, 'YYYY/MM/DD'),'MM')
) """)
def init(self, cr):
tools.drop_view_if_exists(cr, 'campaign_analysis')
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, 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, '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,
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, '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, '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,
to_char(wl.date_planned, 'YYYY') as year,
to_char(wl.date_planned, 'MM') as month,
to_char(wl.date_planned, 'YYYY-MM-DD') as day,
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, '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.discount) 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):
""" 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.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, 'campaign_analysis')
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, 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):
tools.drop_view_if_exists(cr, 'analytic_entries_report')
cr.execute("""
create or replace view analytic_entries_report as (
select
min(a.id) as id,
count(distinct a.id) as nbr,
a.create_date as date,
to_char(a.create_date, 'YYYY') as year,
to_char(a.create_date, 'MM') as month,
to_char(a.create_date, 'YYYY-MM-DD') as day,
a.user_id as user_id,
a.name as name,
analytic.partner_id as partner_id,
a.company_id as company_id,
a.currency_id as currency_id,
a.account_id as account_id,
a.general_account_id as general_account_id,
a.journal_id as journal_id,
a.move_id as move_id,
a.product_id as product_id,
a.product_uom_id as product_uom_id,
sum(a.amount) as amount,
sum(a.unit_amount) as unit_amount
from
account_analytic_line a, account_analytic_account analytic
where analytic.id = a.account_id
group by
a.create_date, a.user_id,a.name,analytic.partner_id,a.company_id,a.currency_id,
a.account_id,a.general_account_id,a.journal_id,
a.move_id,a.product_id,a.product_uom_id
)
""")
def init(self, cr):
""" Display Number of cases and Average Probability
@param cr: the current row, from the database cursor
"""
tools.drop_view_if_exists(cr, 'crm_fundraising_report')
cr.execute("""
create or replace view crm_fundraising_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,
c.state,
c.user_id,
c.section_id,
c.categ_id,
c.type_id,
c.company_id,
c.partner_id,
count(*) as nbr,
date_trunc('day',c.create_date) as create_date,
sum(planned_revenue) as amount_revenue,
sum(planned_cost) as planned_cost,
sum(planned_revenue*probability)::decimal(16,2) as amount_revenue_prob,
avg(probability)::decimal(16,2) as probability,
avg(extract('epoch' from (c.date_closed-c.create_date)))/(3600*24) as delay_close
from
crm_fundraising c
where c.active = 'true'
group by to_char(c.date, 'YYYY'), to_char(c.date, 'MM'),\
c.state, c.user_id,c.section_id,c.categ_id,type_id,c.partner_id,c.company_id,
c.create_date,to_char(c.date, 'YYYY-MM-DD')
)""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'sps_dashboard')
cr.execute("""
create or replace view sps_dashboard as (
with S1 as (
Select 1 as nbr,pr.id as Xid, (select id from sps_state where code = tt.name) as state_id, p.id as id, aa.name as name, p.id as partner_id, tt.name as state from account_analytic_account aa
inner join res_partner p on aa.partner_id = p.id
inner join project_project pr ON aa.id = pr.analytic_account_id
inner join project_task_type tt on pr.project_task_stage = tt.id and tt.name not in ('Waiting Goods')
),
S2 as (
Select 1 as nbr, s.id as Xid,(select id from sps_state where code = s.state) as state_id, p.id as id, s.name as name, p.id as partner_id, s.state as state from sale_order s
inner join res_partner p on s.partner_id = p.id where state not in ('confirmed','done','progress','manual','follow_up','cancel')
),
S3 as (
select 1 as nbr, l.id as Xid,(select id from sps_state where code = cs.name) as state_id, p.id as id, l.name as name, p.id as partner_id, cs.name as state from crm_lead l
inner join crm_case_stage cs on l.stage_id = cs.id
inner join res_partner p on l.partner_id = p.id
where l.state not in ('cancel','done')
)
select 1 as nbr, state_id, 1000*id + 200*Xid as id, name, partner_id from S1
union
select 1 as nbr, state_id, 20000*id + 305*Xid as id, name, partner_id from S2 where S2.id not in (select id from S1)
union
select 1 as nbr, state_id, 300000*id + 101*Xid as id, name, partner_id from S3
where S3.id not in (select partner_id from S1))""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'hr_holidays_report')
cr.execute("""
create or replace view hr_holidays_report as (
select
min(s.id) as id,
date_trunc('day',s.create_date) as date,
date_trunc('day',s.date_from) as date_from,
date_trunc('day',s.date_to) as date_to,
sum(s.number_of_days_temp) as number_of_days_temp,
s.employee_id,
s.user_id as user_id,
to_char(s.create_date, 'YYYY') as year,
to_char(s.create_date, 'MM') as month,
to_char(s.create_date, 'YYYY-MM-DD') as day,
s.holiday_status_id,
s.department_id,
s.state
from
hr_holidays s
where type='remove' and
s.employee_id is not null
group by
s.create_date,s.state,s.date_from,s.date_to,
s.employee_id,s.user_id,s.holiday_status_id,
s.department_id
)
""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'document_report_gbu')
current_date=time.strftime('%Y-%m-%d %H:%M:%S')
print "current date",current_date
cr.execute("""
CREATE OR REPLACE VIEW document_report_gbu as (
SELECT
min(f.id) as id,
to_char(f.date_expired, 'YYYY') as name,
to_char(f.date_expired, 'MM') as month,
f.user_id as user_id,
f.name as doc_name,
p.name as partner_name,
u.name as user,
count(*) as nbr,
d.name as directory,
f.datas_fname as datas_fname,
f.create_date as create_date,
f.date_expired as date_expired,
f.file_size as file_size,
min(d.type) as type,
f.write_date as change_date
FROM ir_attachment f
left join document_directory d on (f.parent_id=d.id and d.name<>'')
inner join res_users u on (f.user_id=u.id)
inner join res_partner p on (f.partner_id=p.id)
group by to_char(f.date_expired, 'YYYY'), to_char(f.date_expired, 'MM'),f.user_id,f.name,p.name,u.name,d.name,f.datas_fname,f.create_date,f.date_expired,f.file_size,f.write_date,d.type
)
""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'report_vote')
cr.execute("""
create or replace view report_vote as (
select
min(iv.id) as id,
count(*) as nbr,
to_date(to_char(ii.open_date, 'dd-MM-YYYY'),'dd-MM-YYYY') as date,
to_char(ii.open_date, 'YYYY') as year,
to_char(ii.open_date, 'MM') as month,
to_char(ii.open_date, 'YYYY-MM-DD') as day,
iv.user_id as user_id,
iv.idea_id as idea_id,
ii.state as idea_state,
ii.user_id as creater_id,
ii.category_id,
(sum(CAST(iv.score as integer))/count(iv.*)) as score
from
idea_vote as iv
left join idea_idea as ii on (ii.id = iv.idea_id)
group by
iv.id ,to_char(ii.open_date, 'dd-MM-YYYY'),to_char(ii.open_date, 'YYYY'),
to_char(ii.open_date, 'MM'),to_char(ii.open_date, 'YYYY-MM-DD'),ii.state,
iv.user_id,ii.user_id,ii.category_id,iv.idea_id
)
""")
def init(self, cr):
tools.drop_view_if_exists(cr, "tms_analisys_05")
cr.execute(
"""
create or replace view tms_analisys_05 as (
select
pl.id as id,
pl.id as product_line_id,
(select p.name_template from product_product as p where p.id=pl.product_id) as product_name,
(select p.id from product_product as p where p.id=pl.product_id) as product_id,
pl.quantity as quantity,
pl.list_price as price,
(pl.quantity*pl.list_price) as total_price,
(select p.name_template from product_product as p where p.id = activity.product_id) as activity_name,
(activity.id ) as activity_id,
o.name as order_name,
o.id as order_id
from tms_product_line as pl, tms_maintenance_order_activity as activity, tms_maintenance_order as o
where pl.state like 'delivered' and pl.activity_id = activity.id and o.id = activity.maintenance_order_id
)
"""
)
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 or replace view account_followup_stat_by_partner as (
SELECT
l.partner_id * 10000 + 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):
try:
tools.drop_view_if_exists(cr, 'view_stockmove_accountmove_report')
except:
cr.rollback() # allows next statement to pass correctly
cr.execute("""
CREATE OR REPLACE VIEW view_stockmove_accountmove_report AS
SELECT row_number() over (order by move_report.date) as id, move_report.date, move_report.code, move_report.name, sum(move_report.inv_qty) AS inv_qty, sum(move_report.balance) AS balance, sum(move_report.acc_qty) AS acc_qty, sum(move_report.inventory) AS inventory, sum(COALESCE(move_report.balance, 0.0) - COALESCE(move_report.inventory, 0.0)) AS diff
FROM ( ( SELECT l.date, p.default_code AS code, p.name_template AS name, 0.0 AS inv_qty, sum(l.quantity) AS acc_qty, COALESCE(sum(l.debit - l.credit), 0.0) AS balance, 0.0 AS inventory
FROM product_product p
LEFT JOIN account_move_line l ON p.id = l.product_id
WHERE (p.id = l.product_id OR l.product_id IS NULL AND l.name::text ~~ (('['::text || p.default_code::text) || '] %'::text)) AND (l.account_id = (( SELECT account_account.id
FROM account_account
WHERE account_account.code::text = '5020'::text)) OR l.id IS NULL)
GROUP BY l.date, p.default_code, p.name_template, l.account_id
ORDER BY l.date DESC)
UNION ALL
( SELECT rsi.date::date AS date, p.default_code AS code, p.name_template AS name, sum(rsi.product_qty) AS inv_qty, 0.0 AS acc_qty, 0.0 AS balance, round(sum(rsi.value), 6) AS inventory
FROM product_product p
LEFT JOIN report_stock_inventory rsi ON rsi.product_id = p.id
WHERE rsi.location_type::text = 'internal'::text AND rsi.state::text = 'done'::text
GROUP BY rsi.date, p.default_code, p.name_template
ORDER BY rsi.date DESC)) move_report
GROUP BY move_report.date, move_report.code, move_report.name
HAVING sum(move_report.balance) <> 0.0 OR sum(move_report.inventory) IS NOT NULL AND round(sum(move_report.inventory), 2) <> 0.00
ORDER BY move_report.code, move_report.date;
""")
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, 'report_timesheet_task_user')
cr.execute(""" create or replace view report_timesheet_task_user as (
select
((r.id*12)+to_number(months.m_id,'99'))::integer as id,
months.name as name,
r.id as user_id,
to_char(to_date(months.name, 'YYYY/MM/DD'),'YYYY') as year,
to_char(to_date(months.name, 'YYYY/MM/DD'),'MM') as month,
(select sum(hours) from project_task_work where user_id = r.id and date between to_date(months.name, 'YYYY/MM/DD') and (to_date(months.name, 'YYYY/MM/DD') + interval '1 month' -
interval '1 day') ) as task_hrs
from res_users r,
(select to_char(p.date,'YYYY-MM-01') as name,
to_char(p.date,'MM') as m_id
from project_task_work p
union
select to_char(h.name,'YYYY-MM-01') as name,
to_char(h.name,'MM') as m_id
from hr_timesheet_sheet_sheet_day h) as months
group by
r.id,months.m_id,months.name,
to_char(to_date(months.name, 'YYYY/MM/DD'),'YYYY') ,
to_char(to_date(months.name, 'YYYY/MM/DD'),'MM')
) """)
def init(self, cr):
tools.drop_view_if_exists(cr, 'report_account_invoice_product')
cr.execute("""
create or replace view report_account_invoice_product as (
select
min(l.id) as id,
i.create_date as date,
to_char(date_trunc('day',i.date_invoice), 'YYYY') as year,
to_char(date_trunc('day',i.date_invoice), 'MM') as month,
to_char(date_trunc('day',i.date_invoice), 'YYYY-MM-DD') as day,
i.type,
i.state,
l.product_id,
t.name,
p.default_code,
t.categ_id,
i.partner_id,
sum(l.quantity * l.price_unit * (1.0 - l.discount/100.0)) as amount,
sum(l.quantity * l.cost_price) as cost_price,
avg(l.margin) as margin,
sum(l.quantity) as quantity,
avg(l.margin_perc) as pourc_marg,
avg(l.discount) as discount
from account_invoice i
left join account_invoice_line l on (i.id = l.invoice_id)
left join product_product p on (p.id = l.product_id)
left join product_template t on (t.id = p.product_tmpl_id)
where i.date_invoice>='2012-01-01' and i.account_id=389 and l.partner_id is not NULL and l.product_id is not NULL and i.state!='draft' and i.state!='cancel'
group by t.categ_id,i.partner_id,l.product_id, i.date_invoice, i.type, i.state,i.create_date,t.name,p.default_code
)
""")
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,
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, 'account_followup_stat')
cr.execute("""
create or replace view account_followup_stat as (
SELECT
l.partner_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):
tools.drop_view_if_exists(cr, 'project_issue_report')
cr.execute("""
CREATE OR REPLACE VIEW project_issue_report AS (
SELECT
c.id as id,
to_char(c.create_date, 'YYYY') as name,
to_char(c.create_date, 'MM') as month,
to_char(c.create_date, 'YYYY-MM-DD') as day,
to_char(c.date_open, 'YYYY-MM-DD') as opening_date,
to_char(c.create_date, 'YYYY-MM-DD') as creation_date,
c.state,
c.user_id,
c.working_hours_open,
c.working_hours_close,
c.section_id,
c.stage_id,
to_char(c.date_closed, 'YYYY-mm-dd') 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_id,
c.task_id,
date_trunc('day',c.create_date) as create_date,
extract('epoch' from (c.date_open-c.create_date))/(3600*24) as delay_open,
extract('epoch' from (c.date_closed-c.date_open))/(3600*24) 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, "tms_analisys_02")
cr.execute(
"""
create or replace view tms_analisys_02 as (
select a.id, o.date as date_order, o.id as order_id, a.id as name, a.product_id, e.categ_id as product_category_id,
a.date_start as date,
to_char(date_trunc('day',a.date_start), 'YYYY') as year,
to_char(date_trunc('day',a.date_start), 'MM') as month,
to_char(date_trunc('day',a.date_start), 'YYYY-MM-DD') as day,
a.hours_real as duration, a.date_start_real as date_start, a.date_end_real as date_end,
a.external_workshop, a.invoice_id, c.supplier_invoice_number,
o.unit_id, o.driver_id, a.supplier_id,
a.parts_cost as spare_parts,
a.cost_service as manpower,
a.parts_cost_external as spare_parts_external,
a.cost_service_external as manpower_external
from tms_maintenance_order_activity a
left join tms_maintenance_order as o on o.id=a.maintenance_order_id and o.state='done'
left join account_invoice as c on c.id=a.invoice_id and c.state <> 'cancel'
left join product_product d on d.id=a.product_id
left join product_template e on e.id=d.product_tmpl_id
where a.state = 'done'
order by o.date, a.date_start_real
)
"""
)
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_address a WHERE a.partner_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.section_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):
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 followups 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 or replace view account_followup_stat_by_partner as (
SELECT
l.partner_id * 10000 + 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_account_invoice_product')
cr.execute("""
create or replace view report_account_invoice_product as (
select
min(l.id) as id,
i.create_date as date,
to_char(date_trunc('day',i.date_invoice), 'YYYY') as year,
to_char(date_trunc('day',i.date_invoice), 'MM') as month,
to_char(date_trunc('day',i.date_invoice), 'YYYY-MM-DD') as day,
i.type,
i.state,
l.product_id,
t.categ_id,
i.partner_id,
sum(l.quantity * l.price_unit * (1.0 - l.discount/100.0)) as amount,
sum(l.quantity * l.cost_price) as cost_price,
sum((l.quantity * l.price_unit * (1.0 - l.discount/100.0) - (l.quantity * l.cost_price))) as margin,
sum(l.quantity) as quantity
from account_invoice i
left join account_invoice_line l on (i.id = l.invoice_id)
left join product_product p on (p.id = l.product_id)
left join product_template t on (t.id = p.product_tmpl_id)
group by t.categ_id,i.partner_id,l.product_id, i.date_invoice, i.type, i.state,i.create_date
)
""")
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(line.price_ded) 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, 'sps_dashboard')
cr.execute("""
create or replace view sps_dashboard as (
with S1 as (
Select 1 as nbr,pr.id as Xid, (select id from sps_state where code = tt.name) as state_id, p.id as id, aa.name as name, p.id as partner_id, tt.name as state from account_analytic_account aa
inner join res_partner p on aa.partner_id = p.id
inner join project_project pr ON aa.id = pr.analytic_account_id
inner join project_task_type tt on pr.project_task_stage = tt.id and tt.name not in ('Waiting Goods')
),
S2 as (
Select 1 as nbr, s.id as Xid,(select id from sps_state where code = s.state) as state_id, p.id as id, s.name as name, p.id as partner_id, s.state as state from sale_order s
inner join res_partner p on s.partner_id = p.id where state not in ('confirmed','done','progress','manual','follow_up','cancel')
),
S3 as (
select 1 as nbr, l.id as Xid,(select id from sps_state where code = cs.name) as state_id, p.id as id, l.name as name, p.id as partner_id, cs.name as state from crm_lead l
inner join crm_case_stage cs on l.stage_id = cs.id
inner join res_partner p on l.partner_id = p.id
where l.state not in ('cancel','done')
)
select 1 as nbr, state_id, 1000*id + 200*Xid as id, name, partner_id from S1
union
select 1 as nbr, state_id, 20000*id + 305*Xid as id, name, partner_id from S2 where S2.id not in (select id from S1)
union
select 1 as nbr, state_id, 300000*id + 101*Xid as id, name, partner_id from S3
where S3.id not in (select partner_id from S1))""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'analytic_entries_report')
cr.execute("""
create or replace view analytic_entries_report as (
select
min(a.id) as id,
count(distinct a.id) as nbr,
a.create_date as date,
to_char(a.create_date, 'YYYY') as year,
to_char(a.create_date, 'MM') as month,
to_char(a.create_date, 'YYYY-MM-DD') as day,
a.user_id as user_id,
a.name as name,
analytic.partner_id as partner_id,
a.company_id as company_id,
a.currency_id as currency_id,
a.account_id as account_id,
a.general_account_id as general_account_id,
a.journal_id as journal_id,
a.move_id as move_id,
a.product_id as product_id,
a.product_uom_id as product_uom_id,
sum(a.amount) as amount,
sum(a.unit_amount) as unit_amount
from
account_analytic_line a, account_analytic_account analytic
where analytic.id = a.account_id
group by
a.create_date, a.user_id,a.name,analytic.partner_id,a.company_id,a.currency_id,
a.account_id,a.general_account_id,a.journal_id,
a.move_id,a.product_id,a.product_uom_id
)
""")
def init(self, cr):
""" Display Number of cases and Average Probability
@param cr: the current row, from the database cursor
"""
tools.drop_view_if_exists(cr, 'crm_fundraising_report')
cr.execute("""
create or replace view crm_fundraising_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,
c.state,
c.user_id,
c.section_id,
c.categ_id,
c.type_id,
c.company_id,
c.partner_id,
count(*) as nbr,
date_trunc('day',c.create_date) as create_date,
sum(planned_revenue) as amount_revenue,
sum(planned_cost) as planned_cost,
sum(planned_revenue*probability)::decimal(16,2) as amount_revenue_prob,
avg(probability)::decimal(16,2) as probability,
avg(extract('epoch' from (c.date_closed-c.create_date)))/(3600*24) as delay_close
from
crm_fundraising c
where c.active = 'true'
group by to_char(c.date, 'YYYY'), to_char(c.date, 'MM'),\
c.state, c.user_id,c.section_id,c.categ_id,type_id,c.partner_id,c.company_id,
c.create_date,to_char(c.date, 'YYYY-MM-DD')
)""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'payment_advice_report')
cr.execute("""
create or replace view payment_advice_report as (
select
min(l.id) as id,
sum(l.bysal) as bysal,
p.name,
p.state,
p.date,
p.number,
p.company_id,
p.bank_id,
p.chaque_nos as cheque_nos,
p.neft,
l.employee_id,
l.ifsc_code,
l.name as employee_bank_no,
to_char(p.date, 'YYYY') as year,
to_char(p.date, 'MM') as month,
to_char(p.date, 'YYYY-MM-DD') as day,
1 as nbr
from
hr_payroll_advice as p
left join hr_payroll_advice_line as l on (p.id=l.advice_id)
where
l.employee_id IS NOT NULL
group by
p.number,p.name,p.date,p.state,p.company_id,p.bank_id,p.chaque_nos,p.neft,
l.employee_id,l.advice_id,l.bysal,l.ifsc_code, l.name
)
""")
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):
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,
0.00 as net_margin_per_qty,
0.00 *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,
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, 'account_followup_stat')
cr.execute("""
create or replace view account_followup_stat as (
SELECT
l.partner_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):
tools.drop_view_if_exists(cr,'tms_analisys_01')
cr.execute("""
create or replace view tms_analisys_01 as (
select
o.id, o.id as name, o.product_id, o.supervisor_id, o.maint_cycle_id, o.driver_id,
o.user_id, o.unit_id, o.date,
to_char(date_trunc('day',o.date), 'YYYY') as year,
to_char(date_trunc('day',o.date), 'MM') as month,
to_char(date_trunc('day',o.date), 'YYYY-MM-DD') as day,
o.date_start_real, o.date_end_real,
o.duration_real, o.notes,
sum(a.parts_cost) as parts_cost,
sum(a.cost_service) as cost_service,
sum(a.parts_cost_external) as parts_cost_external,
sum(a.cost_service_external) as cost_service_external
from tms_maintenance_order as o
left join tms_maintenance_order_activity a on o.id=a.maintenance_order_id and a.state='done'
where o.state = 'done'
group by o.id, o.product_id, o.supervisor_id, o.maint_cycle_id, o.driver_id,
o.user_id, o.unit_id, o.name, o.date, o.date_start_real, o.date_end_real,
o.duration_real, o.notes
order by o.date
);
""")
def init(self, cr):
try:
tools.drop_view_if_exists(cr, "view_stockmove_accountmove_report")
except:
cr.rollback() # allows next statement to pass correctly
cr.execute(
"""
CREATE OR REPLACE VIEW view_stockmove_accountmove_report AS
SELECT row_number() over (order by move_report.date) as id, move_report.date, move_report.code, move_report.name, sum(move_report.inv_qty) AS inv_qty, sum(move_report.balance) AS balance, sum(move_report.acc_qty) AS acc_qty, sum(move_report.inventory) AS inventory, sum(COALESCE(move_report.balance, 0.0) - COALESCE(move_report.inventory, 0.0)) AS diff
FROM ( ( SELECT l.date, p.default_code AS code, p.name_template AS name, 0.0 AS inv_qty, sum(l.quantity) AS acc_qty, COALESCE(sum(l.debit - l.credit), 0.0) AS balance, 0.0 AS inventory
FROM product_product p
LEFT JOIN account_move_line l ON p.id = l.product_id
WHERE (p.id = l.product_id OR l.product_id IS NULL AND l.name::text ~~ (('['::text || p.default_code::text) || '] %'::text)) AND (l.account_id = (( SELECT account_account.id
FROM account_account
WHERE account_account.code::text = '5020'::text)) OR l.id IS NULL)
GROUP BY l.date, p.default_code, p.name_template, l.account_id
ORDER BY l.date DESC)
UNION ALL
( SELECT rsi.date::date AS date, p.default_code AS code, p.name_template AS name, sum(rsi.product_qty) AS inv_qty, 0.0 AS acc_qty, 0.0 AS balance, round(sum(rsi.value), 6) AS inventory
FROM product_product p
LEFT JOIN report_stock_inventory rsi ON rsi.product_id = p.id
WHERE rsi.location_type::text = 'internal'::text AND rsi.state::text = 'done'::text
GROUP BY rsi.date, p.default_code, p.name_template
ORDER BY rsi.date DESC)) move_report
GROUP BY move_report.date, move_report.code, move_report.name
HAVING sum(move_report.balance) <> 0.0 OR sum(move_report.inventory) IS NOT NULL AND round(sum(move_report.inventory), 2) <> 0.00
ORDER BY move_report.code, move_report.date;
"""
)
def init(self, cr):
tools.drop_view_if_exists(cr, 'report_account_invoice_product')
cr.execute("""
create or replace view report_account_invoice_product as (
select
min(l.id) as id,
i.create_date as date,
to_char(date_trunc('day',i.date_invoice), 'YYYY') as year,
to_char(date_trunc('day',i.date_invoice), 'MM') as month,
to_char(date_trunc('day',i.date_invoice), 'YYYY-MM-DD') as day,
i.type,
i.state,
l.product_id,
t.name,
p.default_code,
t.categ_id,
i.partner_id,
sum(l.quantity * l.price_unit * (1.0 - l.discount/100.0)) as amount,
sum(l.quantity * l.cost_price) as cost_price,
avg(l.margin) as margin,
sum(l.quantity) as quantity,
avg(l.margin_perc) as pourc_marg,
avg(l.discount) as discount
from account_invoice i
left join account_invoice_line l on (i.id = l.invoice_id)
left join product_product p on (p.id = l.product_id)
left join product_template t on (t.id = p.product_tmpl_id)
where i.date_invoice>='2012-01-01' and i.account_id=389 and l.partner_id is not NULL and l.product_id is not NULL and i.state!='draft' and i.state!='cancel'
group by t.categ_id,i.partner_id,l.product_id, i.date_invoice, i.type, i.state,i.create_date,t.name,p.default_code
)
""")
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.discount) 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):
""" 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.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, "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,
to_char(wl.date_planned, 'YYYY') as year,
to_char(wl.date_planned, 'MM') as month,
to_char(wl.date_planned, 'YYYY-MM-DD') as day,
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, 'fg_sale_order_report_daily_source')
cr.execute("""
create or replace view fg_sale_order_report_daily_source as (
SELECT
MIN(line."id")AS "id",
o.date_order AS DATE,
SUM(line.subtotal_amount)AS amount,
COALESCE(product.source, '未知来源') as source
FROM
fg_sale_order_line line
INNER JOIN fg_sale_order o ON line.order_id = o. ID
INNER JOIN product_product product ON line.product_id = product."id"
WHERE
(o."state" = 'done' OR o.minus = TRUE)
AND(
o.date_order > CURRENT_DATE - INTERVAL '3 months'
)
GROUP BY
o.date_order,
product."source"
ORDER BY
o.date_order ASC
)
""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'account_report_gl')
cr.execute("""
CREATE OR REPLACE VIEW account_report_gl AS
SELECT min(account_move_line.move_id) AS id, account_move_line.date, account_move_line.partner_id, account_move_line.move_id, account_move_line.account_id, sum(account_move_line.debit) AS debit, sum(account_move_line.credit) AS credit, sum(account_move_line.debit - account_move_line.credit) AS balance
FROM account_move_line
WHERE account_move_line.date > '2012-09-01'::date AND account_move_line.date < '2012-09-29'::date
GROUP BY account_move_line.account_id, account_move_line.partner_id, account_move_line.move_id, account_move_line.date
""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'report_stock_inventory')
cr.execute("""
CREATE OR REPLACE view report_stock_inventory AS (
(SELECT
min(m.id) as id, m.date as date,
m.partner_id as partner_id, m.location_id as location_id,
m.product_id as product_id, pt.categ_id as product_categ_id, l.usage as location_type,
m.company_id,
m.state as state, m.prodlot_id as prodlot_id,
coalesce(sum(-pt.standard_price * m.product_qty * pu.factor / u.factor)::decimal, 0.0) as value,
CASE when pt.uom_id = m.product_uom
THEN
coalesce(sum(-m.product_qty)::decimal, 0.0)
ELSE
coalesce(sum(-m.product_qty * pu.factor / u.factor )::decimal, 0.0) END as product_qty
FROM
stock_move m
LEFT JOIN stock_picking p ON (m.picking_id=p.id)
LEFT JOIN product_product pp ON (m.product_id=pp.id)
LEFT JOIN product_template pt ON (pp.product_tmpl_id=pt.id)
LEFT JOIN product_uom pu ON (pt.uom_id=pu.id)
LEFT JOIN product_uom u ON (m.product_uom=u.id)
LEFT JOIN stock_location l ON (m.location_id=l.id)
where pt.sale_ok = 't'
GROUP BY
m.id, m.product_id, m.product_uom, pt.categ_id, m.partner_id, m.location_id, m.location_dest_id,
m.prodlot_id, m.date, m.state, l.usage, m.company_id,pt.uom_id
) UNION ALL (
SELECT
-m.id as id, m.date as date,
m.partner_id as partner_id, m.location_dest_id as location_id,
m.product_id as product_id, pt.categ_id as product_categ_id, l.usage as location_type,
m.company_id,
m.state as state, m.prodlot_id as prodlot_id,
coalesce(sum(pt.standard_price * m.product_qty * pu.factor / u.factor )::decimal, 0.0) as value,
CASE when pt.uom_id = m.product_uom
THEN
coalesce(sum(m.product_qty)::decimal, 0.0)
ELSE
coalesce(sum(m.product_qty * pu.factor / u.factor)::decimal, 0.0) END as product_qty
FROM
stock_move m
LEFT JOIN stock_picking p ON (m.picking_id=p.id)
LEFT JOIN product_product pp ON (m.product_id=pp.id)
LEFT JOIN product_template pt ON (pp.product_tmpl_id=pt.id)
LEFT JOIN product_uom pu ON (pt.uom_id=pu.id)
LEFT JOIN product_uom u ON (m.product_uom=u.id)
LEFT JOIN stock_location l ON (m.location_dest_id=l.id)
where pt.sale_ok = 't'
GROUP BY
m.id, m.product_id, m.product_uom, pt.categ_id, m.partner_id, m.location_id, m.location_dest_id,
m.prodlot_id, m.date, m.state, l.usage, m.company_id,pt.uom_id
)
);
""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'crm_super_calendar')
try:
cr.execute("""
create or replace view crm_super_calendar AS (
SELECT
row_number() OVER ()::INTEGER AS id,
*
FROM (
SELECT
partner_id as partner_id,
user_id as user_id,
date_action::DATE as date_action,
concat('crm.lead,', id) AS source_id,
'lead_opportunity' as type
from crm_lead
where state not in ('done') and date_action is not Null
UNION
SELECT
partner_id as partner_id,
user_id as user_id,
date::DATE as date_action,
concat('crm.meeting,', id) AS source_id,
'crm_meeting' as type
from crm_meeting
where state not in ('done', 'cancel') and date is not Null
UNION
SELECT
partner_id as partner_id,
user_id as user_id,
date::DATE as date_action,
concat('crm.phonecall,', id) AS source_id,
'crm_phonecall' as type
from crm_phonecall
where state not in ('done', 'cancel') and date is not Null
UNION
SELECT
partner_id as partner_id,
user_id as user_id,
date_action_next::DATE as date_action,
concat('sale.order,', id) AS source_id,
'sale_order' as type
from sale_order
where state not in ('done', 'cancel') and date_action_next is not Null
) as A
)
""")
except Exception as e:
_logger.error(u'Error: {error}'.format(error=e))
def init(self, cr):
tools.drop_view_if_exists(cr, 'hr_expense_report')
cr.execute("""
create or replace view hr_expense_report as (
select
min(l.id) as id,
date_trunc('day',s.date) as date,
s.employee_id,
s.journal_id,
s.currency_id,
to_date(to_char(s.date_confirm, 'dd-MM-YYYY'),'dd-MM-YYYY') as date_confirm,
to_date(to_char(s.date_valid, 'dd-MM-YYYY'),'dd-MM-YYYY') as date_valid,
s.invoice_id,
count(s.invoice_id) as invoiced,
s.user_valid as user_id,
s.department_id,
to_char(date_trunc('day',s.create_date), 'YYYY') as year,
to_char(date_trunc('day',s.create_date), 'MM') as month,
to_char(date_trunc('day',s.create_date), 'YYYY-MM-DD') as day,
avg(extract('epoch' from age(s.date_valid,s.date)))/(3600*24) as delay_valid,
avg(extract('epoch' from age(s.date_valid,s.date_confirm)))/(3600*24) as delay_confirm,
l.product_id as product_id,
l.analytic_account as analytic_account,
sum(l.unit_quantity * u.factor) as product_qty,
s.company_id as company_id,
sum(l.unit_quantity*l.unit_amount) as price_total,
(sum(l.unit_quantity*l.unit_amount)/sum(case when l.unit_quantity=0 or u.factor=0 then 1 else l.unit_quantity * u.factor end))::decimal(16,2) as price_average,
count(*) as nbr,
(select unit_quantity from hr_expense_line where id=l.id and product_id is not null) as no_of_products,
(select analytic_account from hr_expense_line where id=l.id and analytic_account is not null) as no_of_account,
s.state
from hr_expense_line l
left join hr_expense_expense s on (s.id=l.expense_id)
left join product_uom u on (u.id=l.uom_id)
group by
date_trunc('day',s.date),
to_char(date_trunc('day',s.create_date), 'YYYY'),
to_char(date_trunc('day',s.create_date), 'MM'),
to_char(date_trunc('day',s.create_date), 'YYYY-MM-DD'),
to_date(to_char(s.date_confirm, 'dd-MM-YYYY'),'dd-MM-YYYY'),
to_date(to_char(s.date_valid, 'dd-MM-YYYY'),'dd-MM-YYYY'),
l.product_id,
l.analytic_account,
s.invoice_id,
s.currency_id,
s.user_valid,
s.department_id,
l.uom_id,
l.id,
s.state,
s.journal_id,
s.company_id,
s.employee_id
)
""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'hr_agent_customer_commission')
cr.execute("""
create or replace view hr_agent_customer_commission as (
select
min(s.id) as id,
s.amount_total as price_total,
s.date_order as date,
s.date_confirm as date_confirm,
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.internal_user_id as hr_employee_id,
s.id as sale_order_id,
s.trimester as trimester,
s.sale_commission_amount as sale_amount,
(case when s.flag_paid = True then
s.sale_commission_amount
else
0.0
end) as amount,
(case when s.state in ('cancel') then
s.sale_commission_amount
else
0.0
end) as invoice_amount,
s.sale_commission_amount as amount_for_graph,
p.res_partner_zone_id as zone_id,
s.partner_id as customer_id,
s.company_id as company_id,
s.state,
s.pricelist_id as pricelist_id,
s.project_id as analytic_account_id
from
sale_order s
left join res_partner p on (p.id = s.partner_id)
where
s.state not in ('draft','cancel')
group by
s.amount_total,
s.date_order,
s.date_confirm,
s.internal_user_id,
s.id,
s.trimester,
s.sale_commission_amount,
s.flag_paid,
s.partner_id,
p.res_partner_zone_id,
s.company_id,
s.state,
s.pricelist_id,
s.project_id
)
""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'report_stock_inventory')
cr.execute("""
CREATE OR REPLACE view report_stock_inventory AS (
(SELECT
min(m.id) as id, m.date as date,
to_char(m.date, 'YYYY') as year,
to_char(m.date, 'MM') as month,
m.address_id as partner_id, m.location_id as location_id,
m.product_id as product_id, pt.categ_id as product_categ_id, l.usage as location_type,
m.company_id,
m.state as state, m.prodlot_id as prodlot_id,
coalesce(sum(-pt.standard_price * m.product_qty * pu.factor / pu2.factor)::decimal, 0.0) as value,
coalesce(sum(-m.product_qty * pu.factor / pu2.factor)::decimal, 0.0) as product_qty
FROM
stock_move m
LEFT JOIN stock_picking p ON (m.picking_id=p.id)
LEFT JOIN product_product pp ON (m.product_id=pp.id)
LEFT JOIN product_template pt ON (pp.product_tmpl_id=pt.id)
LEFT JOIN product_uom pu ON (pt.uom_id=pu.id)
LEFT JOIN product_uom pu2 ON (m.product_uom=pu2.id)
LEFT JOIN product_uom u ON (m.product_uom=u.id)
LEFT JOIN stock_location l ON (m.location_id=l.id)
WHERE m.state != 'cancel'
GROUP BY
m.id, m.product_id, m.product_uom, pt.categ_id, m.address_id, m.location_id, m.location_dest_id,
m.prodlot_id, m.date, m.state, l.usage, m.company_id, pt.uom_id
) UNION ALL (
SELECT
-m.id as id, m.date as date,
to_char(m.date, 'YYYY') as year,
to_char(m.date, 'MM') as month,
m.address_id as partner_id, m.location_dest_id as location_id,
m.product_id as product_id, pt.categ_id as product_categ_id, l.usage as location_type,
m.company_id,
m.state as state, m.prodlot_id as prodlot_id,
coalesce(sum(pt.standard_price * m.product_qty * pu.factor / pu2.factor)::decimal, 0.0) as value,
coalesce(sum(m.product_qty * pu.factor / pu2.factor)::decimal, 0.0) as product_qty
FROM
stock_move m
LEFT JOIN stock_picking p ON (m.picking_id=p.id)
LEFT JOIN product_product pp ON (m.product_id=pp.id)
LEFT JOIN product_template pt ON (pp.product_tmpl_id=pt.id)
LEFT JOIN product_uom pu ON (pt.uom_id=pu.id)
LEFT JOIN product_uom pu2 ON (m.product_uom=pu2.id)
LEFT JOIN product_uom u ON (m.product_uom=u.id)
LEFT JOIN stock_location l ON (m.location_dest_id=l.id)
WHERE m.state != 'cancel'
GROUP BY
m.id, m.product_id, m.product_uom, pt.categ_id, m.address_id, m.location_id, m.location_dest_id,
m.prodlot_id, m.date, m.state, l.usage, m.company_id, pt.uom_id
)
);
""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'sale_report')
cr.execute("""
create or replace view sale_report as (
select
min(l.id) as id,
l.product_id as product_id,
t.uom_id as product_uom,
sum(l.product_uom_qty / u.factor * u2.factor) as product_uom_qty,
sum(l.product_uom_qty * l.price_unit * (100.0-l.discount) / 100.0) as price_total,
1 as nbr,
s.date_order as date,
s.date_confirm as date_confirm,
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.user_id as user_id,
s.shop_id as shop_id,
s.company_id as company_id,
extract(epoch from avg(date_trunc('day',s.date_confirm)-date_trunc('day',s.create_date)))/(24*60*60)::decimal(16,2) as delay,
s.state,
t.categ_id as categ_id,
s.shipped,
s.shipped::integer as shipped_qty_1,
s.pricelist_id as pricelist_id,
s.project_id as analytic_account_id,
s.active
from
sale_order s
left join sale_order_line l on (s.id=l.order_id)
left join product_product p on (l.product_id=p.id)
left join product_template t on (p.product_tmpl_id=t.id)
left join product_uom u on (u.id=l.product_uom)
left join product_uom u2 on (u2.id=t.uom_id)
group by
l.product_id,
l.product_uom_qty,
l.order_id,
t.uom_id,
t.categ_id,
s.date_order,
s.date_confirm,
s.partner_id,
s.user_id,
s.shop_id,
s.company_id,
s.state,
s.shipped,
s.pricelist_id,
s.project_id,
s.active
)
""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'report_document_file')
cr.execute("""
create or replace view report_document_file as (
select min(f.id) as id,
count(*) as nbr,
min(EXTRACT(MONTH FROM f.create_date)||'-'||to_char(f.create_date,'Month')) as month,
sum(f.file_size) as file_size
from ir_attachment f
group by EXTRACT(MONTH FROM f.create_date)
)
""")