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_channel') cr.execute(""" CREATE OR REPLACE VIEW im_livechat_report_channel AS ( SELECT C.id as id, C.uuid as uuid, C.id as channel_id, C.name as channel_name, CONCAT(L.name, ' / ', C.id) as technical_name, C.livechat_channel_id as livechat_channel_id, C.create_date as start_date, to_char(date_trunc('hour', C.create_date), 'YYYY-MM-DD HH24:MI:SS') as start_date_hour, EXTRACT('epoch' FROM (max((SELECT (max(M.create_date)) FROM mail_message M JOIN mail_message_mail_channel_rel R ON (R.mail_message_id = M.id) WHERE R.mail_channel_id = C.id))-C.create_date)) as duration, count(distinct P.id) as nbr_speaker, count(distinct M.id) as nbr_message, MAX(S.partner_id) as partner_id FROM mail_channel C JOIN mail_message_mail_channel_rel R ON (C.id = R.mail_channel_id) JOIN mail_message M ON (M.id = R.mail_message_id) JOIN mail_channel_partner S ON (S.channel_id = C.id) JOIN im_livechat_channel L ON (L.id = C.livechat_channel_id) LEFT JOIN res_partner P ON (M.author_id = P.id) GROUP BY C.id, C.name, C.livechat_channel_id, L.name, C.create_date, C.uuid ) """)
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_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): """ Display Number of cases And Team 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.team_id, c.partner_id, c.company_id, c.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, 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.team_id, c.stage_id,\ c.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, '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 left join "crm_lead" l on (m.res_id = l.id) inner join "crm_activity" a on (m.subtype_id = a.subtype_id) WHERE (m.model = 'crm.lead') )""")
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, '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): """Mass Mail Statistical Report: based on mail.mail.statistics that models the various statistics collected for each mailing, and mail.mass_mailing model that models the various mailing performed. """ tools.drop_view_if_exists(cr, 'mail_statistics_report') cr.execute(""" CREATE OR REPLACE VIEW mail_statistics_report AS ( SELECT min(ms.id) as id, ms.scheduled as scheduled_date, mm.name as name, mc.name as campaign, count(ms.bounced) as bounced, count(ms.sent) as sent, (count(ms.sent) - count(ms.bounced)) as delivered, count(ms.opened) as opened, count(ms.replied) as replied, mm.state, mm.email_from FROM mail_mail_statistics as ms left join mail_mass_mailing as mm ON (ms.mass_mailing_id=mm.id) left join mail_mass_mailing_campaign as mc ON (ms.mass_mailing_campaign_id=mc.id) GROUP BY ms.scheduled, mm.name, mc.name, mm.state, mm.email_from )""")
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, 'project_issue_report') cr.execute(""" CREATE OR REPLACE VIEW project_issue_report AS ( SELECT c.id as id, c.date_open as opening_date, c.create_date as create_date, c.date_last_stage_update as date_last_stage_update, c.user_id, c.working_hours_open, c.working_hours_close, c.team_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, 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 LEFT JOIN project_task t on c.task_id = t.id WHERE c.active= 'true' )""")
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.date FROM res_partner p left join account_invoice_report i on (i.partner_id=p.id and i.type in ('out_invoice','out_refund') and i.state in ('open','paid')) )""")
def init(self, cr): # self._table = sale_report 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): # self._table = hr_timesheet_report tools.drop_view_if_exists(cr, self._table) cr.execute("""CREATE or REPLACE VIEW %s as ( %s %s %s %s )""" % (self._table, self._select(), self._from(), self._where(), self._group_by()))
def init(self, cr): # self._table = sale_report 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): tools.drop_view_if_exists(cr, 'crm_opportunity_report') cr.execute(""" CREATE OR REPLACE VIEW crm_opportunity_report AS ( SELECT c.id, c.date_deadline, 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, stage.name as stage_name, c.type, c.company_id, c.priority, c.team_id, activity.nbr_activities, c.active, 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 LEFT JOIN ( SELECT m.res_id, COUNT(*) nbr_activities FROM "mail_message" m WHERE m.model = 'crm.lead' GROUP BY m.res_id ) activity ON (activity.res_id = c.id) LEFT JOIN "crm_stage" stage ON stage.id = c.stage_id GROUP BY c.id, activity.nbr_activities, stage.name )""")
def init(self, cr): tools.drop_view_if_exists(cr, 'hr_recruitment_report') cr.execute(""" create or replace view hr_recruitment_report as ( select min(s.id) as id, s.create_date as date_create, date(s.date_closed) as date_closed, s.date_last_stage_update as date_last_stage_update, s.partner_id, s.company_id, s.user_id, s.job_id, s.type_id, s.department_id, s.priority, s.stage_id, s.last_stage_id, s.medium_id, s.source_id, sum(salary_proposed) as salary_prop, (sum(salary_proposed)/count(*)) as salary_prop_avg, sum(salary_expected) as salary_exp, (sum(salary_expected)/count(*)) as salary_exp_avg, extract('epoch' from (s.write_date-s.create_date))/(3600*24) as delay_close, count(*) as nbr from hr_applicant s group by s.date_open, s.create_date, s.write_date, s.date_closed, s.date_last_stage_update, s.partner_id, s.company_id, s.user_id, s.stage_id, s.last_stage_id, s.type_id, s.priority, s.job_id, s.department_id, s.medium_id, s.source_id ) """)
def init(self, cr): tools.drop_view_if_exists(cr, 'report_sales_by_user_pos') cr.execute(""" create or replace view report_sales_by_user_pos as ( select min(po.id) as id, to_char(date_trunc('day',po.date_order),'YYYY-MM-DD')::text as date_order, po.user_id as user_id, sum(pol.qty)as qty, sum((pol.price_unit * pol.qty * (1 - (pol.discount) / 100.0))) as amount from pos_order as po,pos_order_line as pol,product_product as pp,product_template as pt where pt.id=pp.product_tmpl_id and pp.id=pol.product_id and po.id = pol.order_id group by to_char(date_trunc('day',po.date_order),'YYYY-MM-DD')::text, po.user_id ) """)
def init(self, cr): """Initialize the sql view for the event registration """ tools.drop_view_if_exists(cr, 'report_event_registration') # TOFIX this request won't select events that have no registration cr.execute(""" CREATE VIEW report_event_registration AS ( SELECT e.id::varchar || '/' || coalesce(r.id::varchar,'') AS id, e.id AS event_id, e.user_id AS user_id, r.name AS name_registration, r.create_date AS create_date, e.company_id AS company_id, e.date_begin AS event_date, count(r.id) AS nbevent, count(r.event_id) AS nbregistration, CASE WHEN r.state IN ('draft') THEN count(r.event_id) ELSE 0 END AS draft_state, CASE WHEN r.state IN ('open','done') THEN count(r.event_id) ELSE 0 END AS confirm_state, CASE WHEN r.state IN ('cancel') THEN count(r.event_id) ELSE 0 END AS cancel_state, e.event_type_id AS event_type_id, e.seats_max AS seats_max, e.state AS event_state, r.state AS registration_state FROM event_event e LEFT JOIN event_registration r ON (e.id=r.event_id) GROUP BY event_id, r.id, registration_state, event_type_id, e.id, e.date_begin, e.user_id, event_state, e.company_id, e.seats_max, name_registration ) """)
def init(self, cr): tools.drop_view_if_exists(cr, 'hr_holidays_remaining_leaves_user') cr.execute(""" CREATE or REPLACE view hr_holidays_remaining_leaves_user as ( SELECT min(hrs.id) as id, rr.name as name, sum(hrs.number_of_days) as no_of_leaves, rr.user_id as user_id, hhs.name as leave_type FROM hr_holidays as hrs, hr_employee as hre, resource_resource as rr,hr_holidays_status as hhs WHERE hrs.employee_id = hre.id and hre.resource_id = rr.id and hhs.id = hrs.holiday_status_id GROUP BY rr.name,rr.user_id,hhs.name ) """)
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, s.date_order as date, sum(l.qty * u.factor) as product_qty, sum(l.qty * l.price_unit) as price_sub_total, sum((l.qty * l.price_unit) * (100 - l.discount) / 100) as price_total, sum((l.qty * l.price_unit) * (l.discount / 100)) as total_discount, (sum(l.qty*l.price_unit)/sum(l.qty * u.factor))::decimal as average_price, sum(cast(to_char(date_trunc('day',s.date_order) - date_trunc('day',s.create_date),'DD') as int)) as delay_validation, s.partner_id as partner_id, s.state as state, s.user_id as user_id, s.location_id as location_id, s.company_id as company_id, s.sale_journal as journal_id, l.product_id as product_id, pt.categ_id as product_categ_id, p.product_tmpl_id, ps.config_id, pt.pos_categ_id, pc.stock_location_id, s.pricelist_id, s.invoice_id IS NOT NULL AS invoiced from pos_order_line as l left join pos_order s on (s.id=l.order_id) left join product_product p on (l.product_id=p.id) left join product_template pt on (p.product_tmpl_id=pt.id) left join product_uom u on (u.id=pt.uom_id) left join pos_session ps on (s.session_id=ps.id) left join pos_config pc on (ps.config_id=pc.id) group by s.date_order, s.partner_id,s.state, pt.categ_id, s.user_id,s.location_id,s.company_id,s.sale_journal,s.pricelist_id,s.invoice_id,l.product_id,s.create_date,pt.categ_id,pt.pos_categ_id,p.product_tmpl_id,ps.config_id,pc.stock_location_id having sum(l.qty * u.factor) != 0)""")
def init(self, 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.date as date, (CASE WHEN dlmin.id = min(dl.id) THEN a.value ELSE 0 END) as gross_value, dl.amount as depreciation_value, dl.amount as installment_value, (CASE WHEN dl.move_check THEN dl.amount ELSE 0 END) as posted_value, (CASE WHEN NOT dl.move_check THEN dl.amount ELSE 0 END) as unposted_value, dl.asset_id as asset_id, dl.move_check as move_check, a.category_id as asset_category_id, a.partner_id as partner_id, a.state as state, count(dl.*) as installment_nbr, count(dl.*) as depreciation_nbr, a.company_id as company_id from account_asset_depreciation_line dl left join account_asset_asset a on (dl.asset_id=a.id) left join (select min(d.id) as id,ac.id as ac_id from account_asset_depreciation_line as d inner join account_asset_asset as ac ON (ac.id=d.asset_id) group by ac_id) as dlmin on dlmin.ac_id=a.id group by dl.amount,dl.asset_id,dl.depreciation_date,dl.name, a.date, dl.move_check, a.state, a.category_id, a.partner_id, a.company_id, a.value, a.id, a.salvage_value, dlmin.id )""")
def init(self, 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_operator') cr.execute(""" CREATE OR REPLACE VIEW im_livechat_report_operator AS ( SELECT row_number() OVER () AS id, P.id as partner_id, L.id as livechat_channel_id, count(C.id) as nbr_channel, C.id as channel_id, C.create_date as start_date, EXTRACT('epoch' FROM (max((SELECT (max(M.create_date)) FROM mail_message M JOIN mail_message_mail_channel_rel R ON (R.mail_message_id = M.id) WHERE R.mail_channel_id = C.id))-C.create_date)) as duration, EXTRACT('epoch' from ((SELECT min(M.create_date) FROM mail_message M, mail_message_mail_channel_rel R WHERE M.author_id=P.id AND R.mail_channel_id = C.id AND R.mail_message_id = M.id)-(SELECT min(M.create_date) FROM mail_message M, mail_message_mail_channel_rel R WHERE M.author_id IS NULL AND R.mail_channel_id = C.id AND R.mail_message_id = M.id))) as time_to_answer FROM im_livechat_channel_im_user O JOIN res_users U ON (O.user_id = U.id) JOIN res_partner P ON (U.partner_id = P.id) LEFT JOIN im_livechat_channel L ON (L.id = O.channel_id) LEFT JOIN mail_channel C ON (C.livechat_channel_id = L.id) GROUP BY P.id, L.id, C.id, C.create_date ) """)
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, c.date_open as opening_date, c.date_closed 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.team_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, 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): """ 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, 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 LEFT 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, 'stock_history') cr.execute(""" CREATE OR REPLACE VIEW stock_history AS ( SELECT MIN(id) as id, move_id, location_id, company_id, product_id, product_categ_id, product_template_id, SUM(quantity) as quantity, date, price_unit_on_quant, source, serial_number FROM ((SELECT stock_move.id AS id, stock_move.id AS move_id, dest_location.id AS location_id, dest_location.company_id AS company_id, stock_move.product_id AS product_id, product_template.id AS product_template_id, product_template.categ_id AS product_categ_id, quant.qty AS quantity, stock_move.date AS date, quant.cost as price_unit_on_quant, stock_move.origin AS source, stock_production_lot.name AS serial_number FROM stock_quant as quant JOIN stock_quant_move_rel ON stock_quant_move_rel.quant_id = quant.id JOIN stock_move ON stock_move.id = stock_quant_move_rel.move_id LEFT JOIN stock_production_lot ON stock_production_lot.id = quant.lot_id JOIN stock_location dest_location ON stock_move.location_dest_id = dest_location.id JOIN stock_location source_location ON stock_move.location_id = source_location.id JOIN product_product ON product_product.id = stock_move.product_id JOIN product_template ON product_template.id = product_product.product_tmpl_id WHERE quant.qty>0 AND stock_move.state = 'done' AND dest_location.usage in ('internal', 'transit') AND ( (source_location.company_id is null and dest_location.company_id is not null) or (source_location.company_id is not null and dest_location.company_id is null) or source_location.company_id != dest_location.company_id or source_location.usage not in ('internal', 'transit')) ) UNION ALL (SELECT (-1) * stock_move.id AS id, stock_move.id AS move_id, source_location.id AS location_id, source_location.company_id AS company_id, stock_move.product_id AS product_id, product_template.id AS product_template_id, product_template.categ_id AS product_categ_id, - quant.qty AS quantity, stock_move.date AS date, quant.cost as price_unit_on_quant, stock_move.origin AS source, stock_production_lot.name AS serial_number FROM stock_quant as quant JOIN stock_quant_move_rel ON stock_quant_move_rel.quant_id = quant.id JOIN stock_move ON stock_move.id = stock_quant_move_rel.move_id LEFT JOIN stock_production_lot ON stock_production_lot.id = quant.lot_id JOIN stock_location source_location ON stock_move.location_id = source_location.id JOIN stock_location dest_location ON stock_move.location_dest_id = dest_location.id JOIN product_product ON product_product.id = stock_move.product_id JOIN product_template ON product_template.id = product_product.product_tmpl_id WHERE quant.qty>0 AND stock_move.state = 'done' AND source_location.usage in ('internal', 'transit') AND ( (dest_location.company_id is null and source_location.company_id is not null) or (dest_location.company_id is not null and source_location.company_id is null) or dest_location.company_id != source_location.company_id or dest_location.usage not in ('internal', 'transit')) )) AS foo GROUP BY move_id, location_id, company_id, product_id, product_categ_id, date, price_unit_on_quant, source, product_template_id, serial_number )""")
def init(self, cr): '''Create the view''' tools.drop_view_if_exists(cr, 'report_membership') cr.execute(""" CREATE OR REPLACE VIEW report_membership AS ( SELECT MIN(id) AS id, partner_id, count(membership_id) as quantity, user_id, membership_state, associate_member_id, membership_amount, date_to, start_date, COUNT(num_waiting) AS num_waiting, COUNT(num_invoiced) AS num_invoiced, COUNT(num_paid) AS num_paid, SUM(tot_pending) AS tot_pending, SUM(tot_earned) AS tot_earned, membership_id, company_id FROM (SELECT MIN(p.id) AS id, p.id AS partner_id, p.user_id AS user_id, p.membership_state AS membership_state, p.associate_member AS associate_member_id, p.membership_amount AS membership_amount, p.membership_stop AS date_to, p.membership_start AS start_date, CASE WHEN ml.state = 'waiting' THEN ml.id END AS num_waiting, CASE WHEN ml.state = 'invoiced' THEN ml.id END AS num_invoiced, CASE WHEN ml.state = 'paid' THEN ml.id END AS num_paid, CASE WHEN ml.state IN ('waiting', 'invoiced') THEN SUM(il.price_subtotal) ELSE 0 END AS tot_pending, CASE WHEN ml.state = 'paid' OR p.membership_state = 'old' THEN SUM(il.price_subtotal) ELSE 0 END AS tot_earned, ml.membership_id AS membership_id, p.company_id AS company_id FROM res_partner p LEFT JOIN membership_membership_line ml ON (ml.partner = p.id) LEFT JOIN account_invoice_line il ON (ml.account_invoice_line = il.id) LEFT JOIN account_invoice ai ON (il.invoice_id = ai.id) WHERE p.membership_state != 'none' and p.active = 'true' GROUP BY p.id, p.user_id, p.membership_state, p.associate_member, p.membership_amount, p.membership_start, ml.membership_id, p.company_id, ml.state, ml.id ) AS foo GROUP BY start_date, date_to, partner_id, user_id, membership_id, company_id, membership_state, associate_member_id, membership_amount )""")
def init(self, cr): tools.drop_view_if_exists(cr, 'report_stock_forecast') cr.execute("""CREATE or REPLACE VIEW report_stock_forecast AS (SELECT MIN(id) as id, product_id as product_id, date as date, sum(product_qty) AS quantity, sum(sum(product_qty)) OVER (PARTITION BY product_id ORDER BY date) AS cumulative_quantity FROM (SELECT MIN(id) as id, MAIN.product_id as product_id, SUB.date as date, CASE WHEN MAIN.date = SUB.date THEN sum(MAIN.product_qty) ELSE 0 END as product_qty FROM (SELECT MIN(sq.id) as id, sq.product_id, date_trunc('week', to_date(to_char(CURRENT_DATE, 'YYYY/MM/DD'), 'YYYY/MM/DD')) as date, SUM(sq.qty) AS product_qty FROM stock_quant as sq LEFT JOIN product_product ON product_product.id = sq.product_id LEFT JOIN stock_location location_id ON sq.location_id = location_id.id WHERE location_id.usage = 'internal' GROUP BY date, sq.product_id UNION ALL SELECT MIN(-sm.id) as id, sm.product_id, CASE WHEN sm.date_expected > CURRENT_DATE THEN date_trunc('week', to_date(to_char(sm.date_expected, 'YYYY/MM/DD'), 'YYYY/MM/DD')) ELSE date_trunc('week', to_date(to_char(CURRENT_DATE, 'YYYY/MM/DD'), 'YYYY/MM/DD')) END AS date, SUM(sm.product_qty) AS product_qty FROM stock_move as sm LEFT JOIN product_product ON product_product.id = sm.product_id LEFT JOIN stock_location dest_location ON sm.location_dest_id = dest_location.id LEFT JOIN stock_location source_location ON sm.location_id = source_location.id WHERE sm.state IN ('confirmed','assigned','waiting') and source_location.usage != 'internal' and dest_location.usage = 'internal' GROUP BY sm.date_expected,sm.product_id UNION ALL SELECT MIN(-sm.id) as id, sm.product_id, CASE WHEN sm.date_expected > CURRENT_DATE THEN date_trunc('week', to_date(to_char(sm.date_expected, 'YYYY/MM/DD'), 'YYYY/MM/DD')) ELSE date_trunc('week', to_date(to_char(CURRENT_DATE, 'YYYY/MM/DD'), 'YYYY/MM/DD')) END AS date, SUM(-(sm.product_qty)) AS product_qty FROM stock_move as sm LEFT JOIN product_product ON product_product.id = sm.product_id LEFT JOIN stock_location source_location ON sm.location_id = source_location.id LEFT JOIN stock_location dest_location ON sm.location_dest_id = dest_location.id WHERE sm.state IN ('confirmed','assigned','waiting') and source_location.usage = 'internal' and dest_location.usage != 'internal' GROUP BY sm.date_expected,sm.product_id) as MAIN LEFT JOIN (SELECT DISTINCT date FROM ( SELECT date_trunc('week', CURRENT_DATE) AS DATE UNION ALL SELECT date_trunc('week', to_date(to_char(sm.date_expected, 'YYYY/MM/DD'), 'YYYY/MM/DD')) AS date FROM stock_move sm LEFT JOIN stock_location source_location ON sm.location_id = source_location.id LEFT JOIN stock_location dest_location ON sm.location_dest_id = dest_location.id WHERE sm.state IN ('confirmed','assigned','waiting') and sm.date_expected > CURRENT_DATE and ((dest_location.usage = 'internal' AND source_location.usage != 'internal') or (source_location.usage = 'internal' AND dest_location.usage != 'internal'))) AS DATE_SEARCH) SUB ON (SUB.date IS NOT NULL) GROUP BY MAIN.product_id,SUB.date, MAIN.date ) AS FINAL GROUP BY product_id,date)""")