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) ) """)