def init(self): """ CRM Lead Report @param cr: the current row, from the database cursor """ tools.drop_view_if_exists(self._cr, 'crm_partner_report_assign') self._cr.execute(""" CREATE OR REPLACE VIEW crm_partner_report_assign AS ( SELECT coalesce(i.id, p.id - 1000000000) as id, p.id as partner_id, (SELECT country_id FROM res_partner a WHERE a.parent_id=p.id AND country_id is not null limit 1) as country_id, p.grade_id, p.activation, p.date_review, p.date_partnership, p.user_id, p.team_id, (SELECT count(id) FROM crm_lead WHERE partner_assigned_id=p.id) AS nbr_opportunities, i.price_subtotal as turnover, i.invoice_date FROM res_partner p left join account_invoice_report i on (i.partner_id=p.id and i.type in ('out_invoice','out_refund') and i.state in ('open','in_payment','paid')) )""")
def init(self): """Mass Mail Statistical Report: based on mailing.trace that models the various statistics collected for each mailing, and mailing.mailing model that models the various mailing performed. """ tools.drop_view_if_exists(self.env.cr, 'mailing_trace_report') self.env.cr.execute(""" CREATE OR REPLACE VIEW mailing_trace_report AS ( SELECT min(trace.id) as id, utm_source.name as name, mailing.mailing_type, utm_campaign.name as campaign, trace.scheduled as scheduled_date, mailing.state, mailing.email_from, count(trace.sent) as sent, (count(trace.sent) - count(trace.bounced)) as delivered, count(trace.opened) as opened, count(trace.replied) as replied, count(trace.clicked) as clicked, count(trace.bounced) as bounced FROM mailing_trace as trace left join mailing_mailing as mailing ON (trace.mass_mailing_id=mailing.id) left join utm_campaign as utm_campaign ON (mailing.campaign_id = utm_campaign.id) left join utm_source as utm_source ON (mailing.source_id = utm_source.id) GROUP BY trace.scheduled, utm_source.name, utm_campaign.name, mailing.mailing_type, mailing.state, mailing.email_from )""")
def init(self): tools.drop_view_if_exists(self._cr, self._table) self._cr.execute(""" CREATE or REPLACE view %s AS ( SELECT row_number() over (ORDER BY users.id,product.id) AS id, product.id AS product_id, product.name, product.category_id, product.description, product.price, product.supplier_id, product.company_id, product.active, users.id AS user_id, fav.user_id IS NOT NULL AS is_favorite, product.new_until >= current_date AS is_new, orders.last_order_date FROM lunch_product product INNER JOIN res_users users ON product.company_id IS NULL OR users.company_id = product.company_id -- multi company INNER JOIN res_groups_users_rel groups ON groups.uid = users.id -- only generate for internal users LEFT JOIN LATERAL (select max(date) AS last_order_date FROM lunch_order where user_id=users.id and product_id=product.id) AS orders ON TRUE LEFT JOIN LATERAL (select user_id FROM lunch_product_favorite_user_rel where user_id=users.id and product_id=product.id) AS fav ON TRUE WHERE users.active AND product.active AND groups.gid = %%s --only take into account active products and users ); """ % self._table, (self.env.ref('base.group_user').id,))
def init(self): tools.drop_view_if_exists(self.env.cr, self._table) self.env.cr.execute("""CREATE or REPLACE VIEW %s as ( SELECT %s FROM hr_employee emp )""" % (self._table, self._get_fields()))
def init(self): tools.drop_view_if_exists(self.env.cr, self._table) self._cr.execute("""CREATE OR REPLACE VIEW %s AS ( SELECT max(id) AS id, t.user_id, t.date, coalesce(sum(t.attendance), 0) AS total_attendance, coalesce(sum(t.timesheet), 0) AS total_timesheet, coalesce(sum(t.attendance), 0) - coalesce(sum(t.timesheet), 0) as total_difference FROM ( SELECT -hr_attendance.id AS id, resource_resource.user_id AS user_id, hr_attendance.worked_hours AS attendance, NULL AS timesheet, hr_attendance.check_in::date AS date FROM hr_attendance LEFT JOIN hr_employee ON hr_employee.id = hr_attendance.employee_id LEFT JOIN resource_resource on resource_resource.id = hr_employee.resource_id UNION ALL SELECT ts.id AS id, ts.user_id AS user_id, NULL AS attendance, ts.unit_amount AS timesheet, ts.date AS date FROM account_analytic_line AS ts WHERE ts.project_id IS NOT NULL ) AS t GROUP BY t.user_id, t.date ORDER BY t.date ) """ % self._table)
def init(self): tools.drop_view_if_exists(self._cr, self._table) self._cr.execute(""" CREATE or REPLACE view %s as ( SELECT lc.id as id, lc.amount as amount, lc.date as date, lc.currency_id as currency_id, lc.user_id as user_id, lc.description as description FROM lunch_cashmove lc UNION ALL SELECT -lol.id as id, -lol.price as amount, lol.date as date, lol.currency_id as currency_id, lol.user_id as user_id, format('Order: %%s x %%s %%s', lol.quantity::text, lp.name, lol.display_toppings) as description FROM lunch_order lol JOIN lunch_product lp ON lp.id = lol.product_id WHERE lol.state in ('ordered', 'confirmed') AND lol.active = True ); """ % self._table)
def init(self): tools.drop_view_if_exists(self.env.cr, self._table) self.env.cr.execute("""CREATE or REPLACE VIEW %s AS ( %s FROM ( %s %s %s ) AS sub %s)""" % (self._table, self._select(), self._sub_select(), self._from(), self._where(), self._group_by()))
def init(self): tools.drop_view_if_exists(self.env.cr, self._table) self.env.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): # self._table = sale_report tools.drop_view_if_exists(self.env.cr, self._table) self.env.cr.execute( """CREATE or REPLACE VIEW %s as ( %s FROM ( %s ) %s )""" % (self._table, self._select(), self._from(), self._group_by()))
def init(self): tools.drop_view_if_exists(self._cr, self._table) self._cr.execute(""" CREATE OR REPLACE VIEW %s AS ( %s %s %s %s ) """ % (self._table, self._select(), self._from(), self._join(), self._where()) )
def init(self): tools.drop_view_if_exists(self.env.cr, 'purchase_bill_union') self.env.cr.execute(""" CREATE OR REPLACE VIEW purchase_bill_union AS ( SELECT id, name, ref as reference, partner_id, date, amount_untaxed as amount, currency_id, company_id, id as vendor_bill_id, NULL as purchase_order_id FROM account_move WHERE type='in_invoice' and state = 'posted' UNION SELECT -id, name, partner_ref as reference, partner_id, date_order::date as date, amount_untaxed as amount, currency_id, company_id, NULL as vendor_bill_id, id as purchase_order_id FROM purchase_order WHERE state in ('purchase', 'done') AND invoice_status in ('to invoice', 'no') )""")
def init(self): tools.drop_view_if_exists(self._cr, 'hr_leave_report') self._cr.execute(""" CREATE or REPLACE view hr_leave_report as ( SELECT row_number() over(ORDER BY leaves.employee_id) as id, leaves.employee_id as employee_id, leaves.name as name, leaves.number_of_days as number_of_days, leaves.leave_type as leave_type, leaves.category_id as category_id, leaves.department_id as department_id, leaves.holiday_status_id as holiday_status_id, leaves.state as state, leaves.holiday_type as holiday_type, leaves.date_from as date_from, leaves.date_to as date_to, leaves.payslip_status as payslip_status from (select allocation.employee_id as employee_id, allocation.name as name, allocation.number_of_days as number_of_days, allocation.category_id as category_id, allocation.department_id as department_id, allocation.holiday_status_id as holiday_status_id, allocation.state as state, allocation.holiday_type, null as date_from, null as date_to, FALSE as payslip_status, 'allocation' as leave_type from hr_leave_allocation as allocation union all select request.employee_id as employee_id, request.name as name, (request.number_of_days * -1) as number_of_days, request.category_id as category_id, request.department_id as department_id, request.holiday_status_id as holiday_status_id, request.state as state, request.holiday_type, request.date_from as date_from, request.date_to as date_to, request.payslip_status as payslip_status, 'request' as leave_type from hr_leave as request) leaves ); """)
def init(self): """ Event Question main report """ tools.drop_view_if_exists(self._cr, 'event_question_report') self._cr.execute(""" CREATE VIEW event_question_report AS ( SELECT att_answer.id as id, att_answer.event_registration_id as attendee_id, answer.question_id as question_id, answer.id as answer_id, question.event_id as event_id FROM event_registration_answer as att_answer LEFT JOIN event_answer as answer ON answer.id = att_answer.event_answer_id LEFT JOIN event_question as question ON question.id = answer.question_id GROUP BY attendee_id, event_id, question_id, answer_id, att_answer.id )""")
def init(self): tools.drop_view_if_exists(self.env.cr, self._table) self.env.cr.execute(self.get_main_request())
def init(self): tools.drop_view_if_exists(self._cr, 'report_stock_forecast') query = """ CREATE or REPLACE VIEW report_stock_forecast AS (SELECT %s FROM (SELECT MIN(id) as id, MAIN.product_id as product_id, date(SUB.date) as date, CASE WHEN MAIN.date = SUB.date THEN sum(MAIN.product_qty) ELSE 0 END as product_qty, MAIN.reference as reference, MAIN.company_id as company_id FROM (SELECT MIN(-product.id) as id, product.id as product_id, date(CURRENT_DATE) as date, SUM(sq.quantity) AS product_qty, 'Starting Inventory' as reference, sq.company_id FROM product_product as product LEFT JOIN stock_quant sq ON 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, product.id, sq.company_id, sq.quantity UNION ALL SELECT MIN(sm.id) as id, sm.product_id, CASE WHEN sm.date_expected > CURRENT_DATE THEN date(sm.date_expected) ELSE date(CURRENT_DATE) END AS date, SUM(sm.product_qty) AS product_qty, sm.reference as reference, sm.company_id 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','partially_available','assigned','waiting') and source_location.usage != 'internal' and dest_location.usage = 'internal' GROUP BY sm.date_expected,sm.product_id, sm.company_id, sm.reference UNION ALL SELECT MIN(sm.id) as id, sm.product_id, CASE WHEN sm.date_expected > CURRENT_DATE THEN date(sm.date_expected) ELSE date(CURRENT_DATE) END AS date, SUM(-(sm.product_qty)) AS product_qty, sm.reference AS reference, sm.company_id 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','partially_available','assigned','waiting') and source_location.usage = 'internal' and dest_location.usage != 'internal' GROUP BY sm.date_expected,sm.product_id, sm.company_id, sm.reference ) AS MAIN LEFT JOIN (SELECT DISTINCT date FROM (SELECT date_trunc('day', CURRENT_DATE) AS DATE UNION ALL SELECT date(sm.date_expected) 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 ) AS SUB ON SUB.date IS NOT NULL GROUP BY MAIN.product_id,SUB.date, MAIN.date, MAIN.company_id,MAIN.reference ) AS FINAL %s WHERE final.product_qty != 0 OR final.reference = 'Starting Inventory' %s ORDER BY date ) """ % (self._select(), self._left_join(), self._groupby()) self.env.cr.execute(query)
def init(self): # self._table = sale_report tools.drop_view_if_exists(self.env.cr, self._table) self.env.cr.execute("""CREATE or REPLACE VIEW %s as (%s)""" % (self._table, self._query()))
def init(self): tools.drop_view_if_exists(self._cr, 'report_stock_quantity') query = """ CREATE or REPLACE VIEW report_stock_quantity AS ( SELECT m.id, product_id, CASE WHEN (ls.usage = 'internal' OR ls.usage = 'transit' AND ls.company_id IS NOT NULL) AND ld.usage != 'internal' THEN 'out' WHEN ls.usage != 'internal' AND (ld.usage = 'internal' OR ld.usage = 'transit' AND ld.company_id IS NOT NULL) THEN 'in' END AS state, date_expected::date AS date, CASE WHEN (ls.usage = 'internal' OR ls.usage = 'transit' AND ls.company_id IS NOT NULL) AND ld.usage != 'internal' THEN -product_qty WHEN ls.usage != 'internal' AND (ld.usage = 'internal' OR ld.usage = 'transit' AND ld.company_id IS NOT NULL) THEN product_qty END AS product_qty, m.company_id, CASE WHEN (ls.usage = 'internal' OR ls.usage = 'transit' AND ls.company_id IS NOT NULL) AND ld.usage != 'internal' THEN whs.id WHEN ls.usage != 'internal' AND (ld.usage = 'internal' OR ld.usage = 'transit' AND ld.company_id IS NOT NULL) THEN whd.id END AS warehouse_id FROM stock_move m LEFT JOIN stock_location ls on (ls.id=m.location_id) LEFT JOIN stock_location ld on (ld.id=m.location_dest_id) LEFT JOIN stock_warehouse whs ON ls.parent_path like concat('%/', whs.view_location_id, '/%') LEFT JOIN stock_warehouse whd ON ld.parent_path like concat('%/', whd.view_location_id, '/%') LEFT JOIN product_product pp on pp.id=m.product_id LEFT JOIN product_template pt on pt.id=pp.product_tmpl_id WHERE pt.type = 'product' AND product_qty != 0 AND ( (ls.usage = 'internal' OR ls.usage = 'transit' AND ls.company_id IS NOT NULL) AND ld.usage != 'internal' OR ls.usage != 'internal' AND (ld.usage = 'internal' OR ld.usage = 'transit' AND ld.company_id IS NOT NULL)) AND m.state NOT IN ('cancel', 'draft', 'done') UNION SELECT -q.id as id, product_id, 'forecast' as state, date.*::date, quantity as product_qty, q.company_id, wh.id as warehouse_id FROM GENERATE_SERIES((now() at time zone 'utc')::date - interval '3month', (now() at time zone 'utc')::date + interval '3 month', '1 day'::interval) date, stock_quant q LEFT JOIN stock_location l on (l.id=q.location_id) LEFT JOIN stock_warehouse wh ON l.parent_path like concat('%/', wh.view_location_id, '/%') WHERE l.usage = 'internal' UNION SELECT m.id, product_id, 'forecast' as state, GENERATE_SERIES( CASE WHEN m.state = 'done' THEN (now() at time zone 'utc')::date - interval '3month' ELSE date_expected::date END, CASE WHEN m.state != 'done' THEN (now() at time zone 'utc')::date + interval '3 month' ELSE date::date - interval '1 day' END, '1 day'::interval)::date date, CASE WHEN (ls.usage = 'internal' OR ls.usage = 'transit' AND ls.company_id IS NOT NULL) AND ld.usage != 'internal' AND m.state = 'done' THEN product_qty WHEN ls.usage != 'internal' AND (ld.usage = 'internal' OR ld.usage = 'transit' AND ld.company_id IS NOT NULL) AND m.state = 'done' THEN -product_qty WHEN (ls.usage = 'internal' OR ls.usage = 'transit' AND ls.company_id IS NOT NULL) AND ld.usage != 'internal' THEN -product_qty WHEN ls.usage != 'internal' AND (ld.usage = 'internal' OR ld.usage = 'transit' AND ld.company_id IS NOT NULL) THEN product_qty END AS product_qty, m.company_id, CASE WHEN (ls.usage = 'internal' OR ls.usage = 'transit' AND ls.company_id IS NOT NULL) AND ld.usage != 'internal' THEN whs.id WHEN ls.usage != 'internal' AND (ld.usage = 'internal' OR ld.usage = 'transit' AND ld.company_id IS NOT NULL) THEN whd.id END AS warehouse_id FROM stock_move m LEFT JOIN stock_location ls on (ls.id=m.location_id) LEFT JOIN stock_location ld on (ld.id=m.location_dest_id) LEFT JOIN stock_warehouse whs ON ls.parent_path like concat('%/', whs.view_location_id, '/%') LEFT JOIN stock_warehouse whd ON ld.parent_path like concat('%/', whd.view_location_id, '/%') LEFT JOIN product_product pp on pp.id=m.product_id LEFT JOIN product_template pt on pt.id=pp.product_tmpl_id WHERE pt.type = 'product' AND product_qty != 0 AND ( (ls.usage = 'internal' OR ls.usage = 'transit' AND ls.company_id IS NOT NULL) AND ld.usage != 'internal' OR ls.usage != 'internal' AND (ld.usage = 'internal' OR ld.usage = 'transit' AND ld.company_id IS NOT NULL)) AND m.state NOT IN ('cancel', 'draft') ); """ self.env.cr.execute(query)
def init(self): tools.drop_view_if_exists(self._cr, self._table) query = """ CREATE VIEW %s AS ( SELECT ROW_NUMBER() OVER (ORDER BY P.id, SOL.id) AS id, P.id AS project_id, P.user_id AS user_id, SOL.id AS sale_line_id, P.analytic_account_id AS analytic_account_id, P.partner_id AS partner_id, C.id AS company_id, C.currency_id AS currency_id, S.id AS sale_order_id, S.date_order AS order_confirmation_date, SOL.product_id AS product_id, SOL.qty_delivered_method AS sale_qty_delivered_method, CASE WHEN SOL.qty_delivered_method = 'analytic' THEN (SOL.untaxed_amount_to_invoice / CASE COALESCE(S.currency_rate, 0) WHEN 0 THEN 1.0 ELSE S.currency_rate END) ELSE 0.0 END AS expense_amount_untaxed_to_invoice, CASE WHEN SOL.qty_delivered_method = 'analytic' AND SOL.invoice_status != 'no' THEN CASE WHEN T.expense_policy = 'sales_price' THEN (SOL.price_reduce / CASE COALESCE(S.currency_rate, 0) WHEN 0 THEN 1.0 ELSE S.currency_rate END) * SOL.qty_invoiced ELSE -COST_SUMMARY.expense_cost END ELSE 0.0 END AS expense_amount_untaxed_invoiced, CASE WHEN SOL.qty_delivered_method IN ('timesheet', 'manual') THEN (SOL.untaxed_amount_to_invoice / CASE COALESCE(S.currency_rate, 0) WHEN 0 THEN 1.0 ELSE S.currency_rate END) ELSE 0.0 END AS amount_untaxed_to_invoice, CASE WHEN SOL.qty_delivered_method IN ('timesheet', 'manual') THEN (COALESCE(SOL.untaxed_amount_invoiced, COST_SUMMARY.downpayment_invoiced) / CASE COALESCE(S.currency_rate, 0) WHEN 0 THEN 1.0 ELSE S.currency_rate END) ELSE 0.0 END AS amount_untaxed_invoiced, COST_SUMMARY.timesheet_unit_amount AS timesheet_unit_amount, COST_SUMMARY.timesheet_cost AS timesheet_cost, COST_SUMMARY.expense_cost AS expense_cost FROM project_project P JOIN res_company C ON C.id = P.company_id LEFT JOIN ( SELECT project_id, analytic_account_id, sale_line_id, SUM(timesheet_unit_amount) AS timesheet_unit_amount, SUM(timesheet_cost) AS timesheet_cost, SUM(expense_cost) AS expense_cost, SUM(downpayment_invoiced) AS downpayment_invoiced FROM ( SELECT P.id AS project_id, P.analytic_account_id AS analytic_account_id, TS.so_line AS sale_line_id, SUM(TS.unit_amount) AS timesheet_unit_amount, SUM(TS.amount) AS timesheet_cost, 0.0 AS expense_cost, 0.0 AS downpayment_invoiced FROM account_analytic_line TS, project_project P WHERE TS.project_id IS NOT NULL AND P.id = TS.project_id AND P.active = 't' AND P.allow_timesheets = 't' GROUP BY P.id, TS.so_line UNION SELECT P.id AS project_id, P.analytic_account_id AS analytic_account_id, AAL.so_line AS sale_line_id, 0.0 AS timesheet_unit_amount, 0.0 AS timesheet_cost, SUM(AAL.amount) AS expense_cost, 0.0 AS downpayment_invoiced FROM project_project P LEFT JOIN account_analytic_account AA ON P.analytic_account_id = AA.id LEFT JOIN account_analytic_line AAL ON AAL.account_id = AA.id WHERE AAL.amount < 0.0 AND AAL.project_id IS NULL AND P.active = 't' AND P.allow_timesheets = 't' GROUP BY P.id, AA.id, AAL.so_line UNION SELECT P.id AS project_id, P.analytic_account_id AS analytic_account_id, MY_SOLS.id AS sale_line_id, 0.0 AS timesheet_unit_amount, 0.0 AS timesheet_cost, 0.0 AS expense_cost, CASE WHEN MY_SOLS.invoice_status = 'invoiced' THEN MY_SOLS.price_reduce ELSE 0.0 END AS downpayment_invoiced FROM project_project P LEFT JOIN sale_order_line MY_SOL ON P.sale_line_id = MY_SOL.id LEFT JOIN sale_order MY_S ON MY_SOL.order_id = MY_S.id LEFT JOIN sale_order_line MY_SOLS ON MY_SOLS.order_id = MY_S.id WHERE MY_SOLS.is_downpayment = 't' GROUP BY P.id, MY_SOLS.id UNION SELECT P.id AS project_id, P.analytic_account_id AS analytic_account_id, OLIS.id AS sale_line_id, 0.0 AS timesheet_unit_amount, 0.0 AS timesheet_cost, OLIS.price_reduce AS expense_cost, 0.0 AS downpayment_invoiced FROM project_project P LEFT JOIN account_analytic_account ANAC ON P.analytic_account_id = ANAC.id LEFT JOIN account_analytic_line ANLI ON ANAC.id = ANLI.account_id LEFT JOIN sale_order_line OLI ON P.sale_line_id = OLI.id LEFT JOIN sale_order ORD ON OLI.order_id = ORD.id LEFT JOIN sale_order_line OLIS ON ORD.id = OLIS.order_id WHERE OLIS.product_id = ANLI.product_id AND OLIS.is_downpayment = 't' AND ANLI.amount < 0.0 AND ANLI.project_id IS NULL AND P.active = 't' AND P.allow_timesheets = 't' GROUP BY P.id, OLIS.id UNION SELECT P.id AS project_id, P.analytic_account_id AS analytic_account_id, SOL.id AS sale_line_id, 0.0 AS timesheet_unit_amount, 0.0 AS timesheet_cost, 0.0 AS expense_cost, 0.0 AS downpayment_invoiced FROM sale_order_line SOL INNER JOIN project_project P ON SOL.project_id = P.id WHERE P.active = 't' AND P.allow_timesheets = 't' UNION SELECT P.id AS project_id, P.analytic_account_id AS analytic_account_id, SOL.id AS sale_line_id, 0.0 AS timesheet_unit_amount, 0.0 AS timesheet_cost, 0.0 AS expense_cost, 0.0 AS downpayment_invoiced FROM sale_order_line SOL INNER JOIN project_task T ON SOL.task_id = T.id INNER JOIN project_project P ON P.id = T.project_id WHERE P.active = 't' AND P.allow_timesheets = 't' ) SUB_COST_SUMMARY GROUP BY project_id, analytic_account_id, sale_line_id ) COST_SUMMARY ON COST_SUMMARY.project_id = P.id LEFT JOIN sale_order_line SOL ON COST_SUMMARY.sale_line_id = SOL.id LEFT JOIN sale_order S ON SOL.order_id = S.id LEFT JOIN product_product PP on (SOL.product_id=PP.id) LEFT JOIN product_template T on (PP.product_tmpl_id=T.id) WHERE P.active = 't' AND P.analytic_account_id IS NOT NULL ) """ % self._table self._cr.execute(query)