def init(self, cr): drop_view_if_exists(cr, "intrastat") drop_view_if_exists(cr, "l10n_hr_intrastat") cr.execute( """ create or replace view intrastat as ( SELECT SP.id , SP.name as picking_name, to_char(SP.date_done, 'YYYY') || '-' || to_char(SP.date_done, 'MM') as date, to_char(SP.date_done, 'MM') as month, to_char(SP.date_done, 'YYYY') as year, SP.partner_id as partner_id, PA.name as partner, C.code as country_code, C.intrastat as country_intrastat, SP.transaction_type_id as transaction_type_id, SP.invoice_state as invoice_state, URA.number as ura, PTEM.intrastat_id as intrastat_id, PPROD.id as product_id FROM stock_picking as SP LEFT JOIN res_partner PA on (PA.id = SP.partner_id) LEFT JOIN res_country C on C.id = PA.country_id LEFT JOIN account_invoice URA on URA.id = SP.purchase_id LEFT JOIN stock_move SM on SM.picking_id=SP.id left join (product_template PTEM left join product_product PPROD on (PPROD.product_tmpl_id = PTEM.id)) on (SM.product_id = PPROD.id) /*croatia_id=98* and c.id <>'98' */ WHERE SP.state='done' )""" )
def init(self, cr): drop_view_if_exists(cr, 'report_timesheet_line') cr.execute(""" create or replace view report_timesheet_line as ( select min(l.id) as id, l.date as date, to_char(l.date,'YYYY') as name, to_char(l.date,'MM') as month, l.user_id, to_char(l.date, 'YYYY-MM-DD') as day, l.invoice_id, l.product_id, l.account_id, l.general_account_id, sum(l.unit_amount) as quantity, sum(l.amount) as cost from account_analytic_line l where l.user_id is not null group by l.date, l.user_id, l.product_id, l.account_id, l.general_account_id, l.invoice_id ) """)
def init(self, cr): drop_view_if_exists(cr, 'report_timesheet_invoice') cr.execute(""" create or replace view report_timesheet_invoice as ( select min(l.id) as id, l.user_id as user_id, l.account_id as account_id, a.user_id as manager_id, sum(l.unit_amount) as quantity, sum(l.unit_amount * t.list_price) as amount_invoice from account_analytic_line l left join hr_timesheet_invoice_factor f on (l.to_invoice=f.id) left join account_analytic_account a on (l.account_id=a.id) left join product_product p on (l.to_invoice=f.id) left join product_template t on (l.to_invoice=f.id) where l.to_invoice is not null and l.invoice_id is null group by l.user_id, l.account_id, a.user_id ) """)
def init(self, cr): drop_view_if_exists(cr, 'account_invoice_duplicate_line') cr.execute("""CREATE OR REPLACE VIEW account_invoice_duplicate_line AS (WITH invoices AS (SELECT ail.invoice_id, ail.product_id, ROUND(AVG(ail.price_unit), 4) AS price_avg FROM account_invoice_line AS ail INNER JOIN account_invoice AS ai ON ai.id = ail.invoice_id AND ai.type IN ('out_invoice', 'out_refund') WHERE ail.product_id IS NOT NULL AND ail.quantity <> 0.0 GROUP BY ail.invoice_id, ail.product_id HAVING COUNT(ail.product_id) > 1 ORDER BY ail.invoice_id), in_lines AS (SELECT ai.id AS invoice_id, ai.partner_id AS partner_id, ail.product_id AS product_id, ai.state AS state FROM account_invoice_line AS ail INNER JOIN account_invoice AS ai ON ai.id = ail.invoice_id AND ai.type IN ('out_invoice', 'out_refund') INNER JOIN invoices AS inv ON inv.invoice_id = ail.invoice_id AND inv.product_id = ail.product_id AND inv.price_avg <> ail.price_unit GROUP BY ai.id, ai.partner_id, ail.product_id, ai.state) SELECT row_number() OVER (ORDER BY invoice_id, product_id) AS id, invoice_id, partner_id, product_id, state FROM in_lines ORDER BY invoice_id, product_id);""")
def init(self, cr): drop_view_if_exists(cr, 'intrastat') cr.execute(""" create or replace view intrastat as ( select MIN (SM.id) AS id, to_char(SP.date_done, 'YYYY') || '-' || to_char(SP.date_done, 'MM') as date, to_char(SP.date_done, 'MM') as month, to_char(SP.date_done, 'YYYY') as year, SP.transaction_type_id as transaction_type_id, PT.intrastat_id AS intrastat_id, SUM (SM.product_qty * PT.weight_net) AS weight, SUM (SM.product_qty * SM.price_unit) AS value, SM.country_origin_id AS country_origin, PA.country_id AS country_supplier from stock_picking SP left join stock_move SM on SM.picking_id=SP.id left join (product_template PT left join product_product PP on (PP.product_tmpl_id = PT.id)) on (SM.product_id = PP.id) left join res_partner PA on PA.id = SP.partner_id left join res_country C on C.id = PA.country_id where SP.state = 'done' AND PA.country_id != 201 AND SP.type = 'in' AND C.intrastat = true group by to_char(SP.date_done, 'YYYY') || '-' || to_char(SP.date_done, 'MM'), to_char(SP.date_done, 'MM'), to_char(SP.date_done, 'YYYY'), SP.transaction_type_id, PT.intrastat_id, SM.country_origin_id, PA.country_id )""")
def write(self, cr, uid, ids, vals, context=None): """ If the description change, we must update the action """ if context is None: context = {} if vals.get('sql_name') or vals.get('sql_view'): sql_name = vals.get('sql_name', self.browse(cr, uid, ids[0]).sql_name) sql_view = vals.get('sql_view', self.browse(cr, uid, ids[0]).sql_view) drop_view_if_exists(cr, sql_name) sql_query = 'CREATE OR REPLACE VIEW %s AS\n%s' % (sql_name, sql_view) cr.execute(sql_query, (ids, )) res = super(jasper_document, self).write(cr, uid, ids, vals, context=context) if not context.get('action'): for id in ids: self.make_action(cr, uid, id, context=context) if 'enabled' in vals: if vals['enabled']: for id in ids: self.create_values(cr, uid, id, context) else: for id in ids: self.unlink_values(cr, uid, id, context) return res
def init(self, cr): drop_view_if_exists(cr, "account_account_period_sum_delta") # ??? drop_view_if_exists(cr, "account_account_period_sum_cur_prev") cr.execute(""" create or replace view account_account_period_sum_cur_prev as select c.id*2 as id, c.company_id, account_id, p.id as period_id, p.fiscalyear_id, case when c.name like '%00' then '00' else p.code end as name, c.debit-c.credit as balance_curr ,0 as balance_prev, case when c.name like '%00' then p.date_start -1 else p.date_start end as date_start from account_period p left outer join account_account_period_sum c on (c.period_id = p.id) union select c.id*2 -1 as id, c.company_id, account_id,p.id as period_id, p.fiscalyear_id, case when c.name like '%00' then '00' else p.code end as name, 0 as balance_curr, c.debit-c.credit as balance_prev, case when c.name like '%00' then p.date_start -1 else p.date_start end as date_start from account_period p left outer join account_account_period_sum c on (c.period_id = p.prev_fy_period); """)
def init(self, cr): drop_view_if_exists(cr, "account_account_fiscalyear_sum") cr.execute("""create or replace view account_account_fiscalyear_sum as select --sum_fy_period_id as id, -- FIXME - how to create a unique seq. number to avoid chricar_view_id ? min(s.id) as id, s.company_id, account_id, to_char(y.date_stop,'YYYY') || case when to_char(y.date_stop,'MM') != '12' then '-'||to_char(y.date_stop,'MM') else '' end as name, y.id as fiscalyear_id, sum(case when s.name like '%00' then 0 else debit end) as debit, sum(case when s.name like '%00' then 0 else credit end) as credit, sum(debit) - sum(credit) as balance, sum(case when s.name like '%00' then debit - credit else 0 end) as opening_balance, y.date_start,y.date_stop from account_account_period_sum s, account_period p, account_fiscalyear y where p.id = s.period_id and y.id = p.fiscalyear_id group by s.company_id, sum_fy_period_id, s.account_id, y.id, to_char(y.date_stop,'YYYY') || case when to_char(y.date_stop,'MM') != '12' then '-'||to_char(y.date_stop,'MM') else '' end , y.date_start,y.date_stop""")
def init(self, cr): drop_view_if_exists(cr, "chricar_location_move_col") cr.execute(""" create or replace view chricar_location_move_col as ( select id,company_id, product_id,location_id, picking_id, prodlot_id, date, date::date as date2,period_id, product_qty, case when product_qty >0 then product_qty else 0 end as qty_plus , case when product_qty <0 then product_qty else 0 end as qty_minus, 0 as qty_inventory, move_value_cost, case when move_value_cost >0 then move_value_cost else 0 end as cost_plus , case when move_value_cost <0 then move_value_cost else 0 end as cost_minus, 0 as cost_inventory,state from chricar_report_location_moves where state ='done' and id not in (select 2*move_id from stock_inventory_move_rel union select 2*move_id-1 from stock_inventory_move_rel) union all select id,company_id, product_id, location_id, picking_id, prodlot_id, date,date::date as date2,period_id, product_qty, 0,0, product_qty as qty_inventory, move_value_cost, 0,0, move_value_cost as cost_inventory,state from chricar_report_location_moves where state ='done' and id in (select 2*move_id from stock_inventory_move_rel union select 2*move_id-1 from stock_inventory_move_rel) )""")
def write(self, cr, uid, ids, vals, context=None): """ If the description change, we must update the action """ if context is None: context = {} if vals.get('sql_name') or vals.get('sql_view'): sql_name = vals.get('sql_name', self.browse(cr, uid, ids[0]).sql_name) sql_view = vals.get('sql_view', self.browse(cr, uid, ids[0]).sql_view) drop_view_if_exists(cr, sql_name) sql_query = 'CREATE OR REPLACE VIEW %s AS\n%s' % (sql_name, sql_view) cr.execute(sql_query, (ids,)) res = super(jasper_document, self).write(cr, uid, ids, vals, context=context) if not context.get('action'): for id in ids: self.make_action(cr, uid, id, context=context) if 'enabled' in vals: if vals['enabled']: for id in ids: self.create_values(cr, uid, id, context) else: for id in ids: self.unlink_values(cr, uid, id, context) return res
def init(self, cr): drop_view_if_exists(cr, "account_account_with_postings") cr.execute("""create or replace view account_account_with_postings as select a.id, a.code, a.name, a.company_id, a.shortcut, a.currency_id, a.note, a.user_type, a.reconcile from account_account a where exists ( select 'x' from account_account_period_sum where account_id = a.id and company_id = a.company_id); """)
def init(self, cr): drop_view_if_exists(cr, 'chricar_stock_product_by_location_prodlot') cr.execute("""create or replace view chricar_stock_product_by_location_prodlot as select min(id) as id ,location_id,product_id,prodlot_id, sum(name) as name, sum(product_qty_pending) as product_qty_pending, sum(move_value_cost) as amount, case when sum(name+product_qty_pending) != 0 then sum(move_value_cost)/sum(name+product_qty_pending) else 0 end as average_price,company_id from chricar_stock_move_by_location group by location_id,prodlot_id,product_id,company_id having round(sum(name),4) != 0 or round(sum(move_value_cost),2) != 0 ;""")
def init(self, cr): drop_view_if_exists(cr, 'stock_product_by_location_prodlot') cr.execute("""create or replace view stock_product_by_location_prodlot as select min(id) as id ,location_id,product_id,prodlot_id, sum(name) as name, sum(product_qty_pending) as product_qty_pending, company_id from stock_move_by_location group by location_id,prodlot_id,product_id,company_id having round(sum(name),4) != 0 ;""")
def init(self, cr): drop_view_if_exists(cr, "account_account_period_sum_delta") cr.execute(""" create or replace view account_account_period_sum_delta as select min(id) as id, company_id,account_id, name,period_id,date_start,fiscalyear_id, sum(balance_curr) as balance_curr,sum(balance_prev) as balance_prev, sum(balance_curr) - sum(balance_prev) as diff, case when sum(balance_prev) != 0 then ((sum(balance_curr) / sum(balance_prev)) -1)*100 else 0.0 end as diff_pro from account_account_period_sum_cur_prev group by company_id,account_id, name, period_id,fiscalyear_id,date_start having company_id >0 ; """)
def init(self, cr): drop_view_if_exists(cr, "stock_product_moves") cr.execute( """CREATE OR REPLACE VIEW stock_product_moves AS (SELECT sm.id AS id, sm.product_id AS product_id, sp.id AS picking_id, sm.date AS date, pa.id AS partner_id, CASE WHEN sp.type = 'in' THEN pai.id WHEN sp.type = 'out' THEN sai.id ELSE NULL END AS invoice_id, CASE WHEN sp.type = 'in' OR sp.id IS NULL THEN sm.product_qty ELSE NULL END AS qty_received, CASE WHEN sp.type = 'out' THEN sm.product_qty ELSE NULL END AS qty_delivered, (SELECT SUM(CASE WHEN sp1.type = 'in' OR sp1.id IS NULL THEN sm1.product_qty ELSE sm1.product_qty * -1 END) FROM stock_move AS sm1 LEFT OUTER JOIN stock_picking AS sp1 ON sp1.id = sm1.picking_id WHERE sm1.product_id = sm.product_id AND sm1.state = 'done' AND sm1.date <= sm.date) AS qty_stock, sm.price_unit AS price_unit, sm.product_qty * sm.price_unit AS amount FROM stock_move AS sm LEFT OUTER JOIN res_partner AS pa ON pa.id = sm.partner_id LEFT OUTER JOIN stock_picking AS sp ON sp.id = sm.picking_id LEFT OUTER JOIN sale_order_line_invoice_rel AS solir ON solir.order_line_id = sm.sale_line_id LEFT OUTER JOIN purchase_order_line_invoice_rel AS polir ON polir.order_line_id = sm.purchase_line_id LEFT OUTER JOIN account_invoice_line AS sail ON sail.id = solir.invoice_id LEFT OUTER JOIN account_invoice AS sai ON sai.id = sail.invoice_id LEFT OUTER JOIN account_invoice_line AS pail ON pail.id = polir.invoice_id LEFT OUTER JOIN account_invoice AS pai ON pai.id = pail.invoice_id WHERE sm.state = 'done');""" )
def init(self, cr): drop_view_if_exists(cr, "report_intrastat") cr.execute( """ create or replace view report_intrastat as ( select to_char(inv.create_date, 'YYYY') as name, to_char(inv.create_date, 'MM') as month, min(inv_line.id) as id, intrastat.id as intrastat_id, upper(inv_country.code) as code, sum(case when inv_line.price_unit is not null then inv_line.price_unit * inv_line.quantity else 0 end) as value, sum( case when uom.category_id != puom.category_id then (pt.weight_net * inv_line.quantity) else (pt.weight_net * inv_line.quantity * uom.factor) end ) as weight, sum( case when uom.category_id != puom.category_id then inv_line.quantity else (inv_line.quantity * uom.factor) end ) as supply_units, inv.currency_id as currency_id, inv.number as ref, case when inv.type in ('out_invoice','in_refund') then 'export' else 'import' end as type from account_invoice inv left join account_invoice_line inv_line on inv_line.invoice_id=inv.id left join (product_template pt left join product_product pp on (pp.product_tmpl_id = pt.id)) on (inv_line.product_id = pp.id) left join product_uom uom on uom.id=inv_line.uos_id left join product_uom puom on puom.id = pt.uom_id left join report_intrastat_code intrastat on pt.intrastat_id = intrastat.id left join (res_partner inv_address left join res_country inv_country on (inv_country.id = inv_address.country_id)) on (inv_address.id = inv.partner_id) where inv.state in ('open','paid') and inv_line.product_id is not null and inv_country.intrastat=true group by to_char(inv.create_date, 'YYYY'), to_char(inv.create_date, 'MM'),intrastat.id,inv.type,pt.intrastat_id, inv_country.code,inv.number, inv.currency_id )""" )
def init(self, cr): drop_view_if_exists(cr, 'stock_product_moves') cr.execute("""CREATE OR REPLACE VIEW stock_product_moves AS (SELECT sm.id AS id, sm.product_id AS product_id, sp.id AS picking_id, sm.date AS date, pa.id AS partner_id, CASE WHEN sp.type = 'in' THEN pai.id WHEN sp.type = 'out' THEN sai.id ELSE NULL END AS invoice_id, CASE WHEN sp.type = 'in' OR sp.id IS NULL THEN sm.product_qty ELSE NULL END AS qty_received, CASE WHEN sp.type = 'out' THEN sm.product_qty ELSE NULL END AS qty_delivered, (SELECT SUM(CASE WHEN sp1.type = 'in' OR sp1.id IS NULL THEN sm1.product_qty ELSE sm1.product_qty * -1 END) FROM stock_move AS sm1 LEFT OUTER JOIN stock_picking AS sp1 ON sp1.id = sm1.picking_id WHERE sm1.product_id = sm.product_id AND sm1.state = 'done' AND sm1.date <= sm.date) AS qty_stock, sm.price_unit AS price_unit, sm.product_qty * sm.price_unit AS amount FROM stock_move AS sm LEFT OUTER JOIN res_partner AS pa ON pa.id = sm.partner_id LEFT OUTER JOIN stock_picking AS sp ON sp.id = sm.picking_id LEFT OUTER JOIN sale_order_line_invoice_rel AS solir ON solir.order_line_id = sm.sale_line_id LEFT OUTER JOIN purchase_order_line_invoice_rel AS polir ON polir.order_line_id = sm.purchase_line_id LEFT OUTER JOIN account_invoice_line AS sail ON sail.id = solir.invoice_id LEFT OUTER JOIN account_invoice AS sai ON sai.id = sail.invoice_id LEFT OUTER JOIN account_invoice_line AS pail ON pail.id = polir.invoice_id LEFT OUTER JOIN account_invoice AS pai ON pai.id = pail.invoice_id WHERE sm.state = 'done');""")
def init(self, cr): drop_view_if_exists(cr, 'report_profit_partner') cr.execute(""" create or replace view report_profit_partner as ( select partner_id as id, partner_id, SUM(last_cost) as sum_last_cost, SUM(price_subtotal) as sum_price_subtotal, SUM(qty_consol) as sum_qty_consol, p_uom_c_id from report_profit p group by partner_id,p_uom_c_id ) """)
def init(self, cr): drop_view_if_exists(cr, 'report_stock_lines_date') cr.execute(""" create or replace view report_stock_lines_date as ( select p.id as id, p.id as product_id, max(s.date) as date from product_product p left outer join stock_inventory_line l on (p.id=l.product_id) left join stock_inventory s on (l.inventory_id=s.id) and s.state = 'done' group by p.id )""")
def create(self, cr, uid, vals, context=None): """ Dynamicaly declare the wizard for this document """ if context is None: context = {} doc_id = super(jasper_document, self).create(cr, uid, vals, context=context) self.make_action(cr, uid, doc_id, context=context) # Check if view and create it in the database if vals.get('sql_name') and vals.get('sql_view'): drop_view_if_exists(cr, vals.get('sql_name')) sql_query = 'CREATE OR REPLACE VIEW %s AS\n%s' % (vals['sql_name'], vals['sql_view']) cr.execute(sql_query) return doc_id
def init(self, cr): drop_view_if_exists(cr, 'random_timesheet_lines') cr.execute("""create or replace view random_timesheet_lines as ( select line.id as id, line.date as date, line.name as name, line.unit_amount as quantity, line.product_id as product_id, line.account_id as analytic_account_id, line.product_uom_id as uom_id, line.amount as amount, line.to_invoice as to_invoice, line.general_account_id as general_account_id, line.user_id as user_id from account_analytic_line line where (line.date <= CURRENT_DATE AND line.date > (CURRENT_DATE-15)) ) """ )
def init(self, cr): drop_view_if_exists(cr, 'report_intrastat') cr.execute(""" create or replace view report_intrastat as ( select to_char(inv.create_date, 'YYYY') as name, to_char(inv.create_date, 'MM') as month, min(inv_line.id) as id, intrastat.id as intrastat_id, upper(inv_country.code) as code, sum(case when inv_line.price_unit is not null then inv_line.price_unit * inv_line.quantity else 0 end) as value, sum( case when uom.category_id != puom.category_id then (pt.weight_net * inv_line.quantity) else (pt.weight_net * inv_line.quantity * uom.factor) end ) as weight, sum( case when uom.category_id != puom.category_id then inv_line.quantity else (inv_line.quantity * uom.factor) end ) as supply_units, inv.currency_id as currency_id, inv.number as ref, case when inv.type in ('out_invoice','in_refund') then 'export' else 'import' end as type from account_invoice inv left join account_invoice_line inv_line on inv_line.invoice_id=inv.id left join (product_template pt left join product_product pp on (pp.product_tmpl_id = pt.id)) on (inv_line.product_id = pp.id) left join product_uom uom on uom.id=inv_line.uos_id left join product_uom puom on puom.id = pt.uom_id left join report_intrastat_code intrastat on pt.intrastat_id = intrastat.id left join (res_partner_address inv_address left join res_country inv_country on (inv_country.id = inv_address.country_id)) on (inv_address.id = inv.address_invoice_id) where inv.state in ('open','paid') and inv_line.product_id is not null and inv_country.intrastat=true group by to_char(inv.create_date, 'YYYY'), to_char(inv.create_date, 'MM'),intrastat.id,inv.type,pt.intrastat_id, inv_country.code,inv.number, inv.currency_id )""")
def init(self, cr): drop_view_if_exists(cr, 'report_profit_uxc') cr.execute(""" create or replace view report_profit_uxc as ( select ((user_id*1000000)+cat_id) as id, user_id, cat_id, SUM(last_cost) as sum_last_cost, SUM(price_subtotal) as sum_price_subtotal, SUM(qty_consol) as sum_qty_consol, p_uom_c_id from report_profit p group by user_id,cat_id,p_uom_c_id ) """)
def init(self, cr): drop_view_if_exists(cr, 'report_timesheet_account_date') cr.execute(""" create or replace view report_timesheet_account_date as ( select min(id) as id, date as name, user_id, account_id, sum(unit_amount) as quantity from account_analytic_line group by date, user_id, account_id ) """)
def init(self, cr): drop_view_if_exists(cr, "chricar_report_location_moves_sum") cr.execute(""" create or replace view chricar_report_location_moves_sum as ( select min(id) as id, product_id,location_id, sum(product_qty) as product_qty, sum(move_value_cost) as move_value_cost, sum(move_value_sale) as move_value_sale, case when sum(product_qty) != 0 then sum(move_value_cost)/sum(product_qty) else 0 end as cost_price, case when sum(product_qty) != 0 then sum(move_value_sale)/sum(product_qty) else 0 end as sale_price from chricar_report_location_moves where state ='done' group by product_id,location_id ) """)
def init(self, cr, context=None): sql=""" CREATE or REPLACE view intrastat_preview as( SELECT row_number() OVER (ORDER BY SP.id) as id , SP.id as picking_id, SP.name as picking_name, to_char(SP.date_done, 'YYYY') || '-' || to_char(SP.date_done, 'MM') as date, to_char(SP.date_done, 'MM') as month, to_char(SP.date_done, 'YYYY') as year, SP.partner_id as partner_id, PA.name as partner, CPAR.code as country_code, CPAR.intrastat as country_intrastat, SP.transaction_type_id as transaction_type_id, SP.invoice_state as invoiced_state, URA.id as ura_id, URA.number as ura, IRA.id as ira_id, IRA.number as ira, PPROD.id as product_id, PTEM.name as product_name, PTEM.intrastat_id as intrastat_id, PTEM.weight_net as weight_net, CPRO.code as country_origin FROM stock_picking as SP LEFT JOIN res_partner PA on (PA.id = SP.partner_id) LEFT JOIN res_country CPAR on CPAR.id = PA.country_id LEFT JOIN account_invoice URA on URA.id = SP.purchase_id LEFT JOIN account_invoice IRA on IRA.id = SP.sale_id LEFT JOIN stock_move SM on SM.picking_id=SP.id LEFT JOIN (product_template PTEM LEFT JOIN product_product PPROD on (PPROD.product_tmpl_id = PTEM.id)) on (SM.product_id = PPROD.id) LEFT JOIN res_country CPRO on CPRO.id = PTEM.country_origin /*croatia_id=98* AND CPAR.id <> '98' */ WHERE SP.state='done' AND SP.type<>'internal' AND CPAR.id <> '98' ) """ drop_view_if_exists(cr, 'intrastat_preview') cr.execute(sql)
def init(self, cr): """creates view when install""" drop_view_if_exists(cr, 'report_partner_affected_bycase') cr.execute(""" create or replace view report_partner_affected_bycase as ( select stock_move.id as id, prodlots.id as prodlot_id, prodlots.case_id, stock_move.picking_id, stock_move.date, stock_move.sale_line_id, res_partner.id as partner_id, res_partner.vat, res_partner.fax, res_partner.city, res_partner.phone, res_partner.zip, res_partner.country_id, res_partner.email, prodlots.product_id as product_id, stock_move.product_qty from stock_move inner join (select distinct stock_production_lot.*, case_id from stock_production_lot inner join blocked_prodlots_cases_ids on id = blocked_prodlot) as prodlots on stock_move.prodlot_id = prodlots.id inner join stock_location on stock_move.location_dest_id = stock_location.id inner join res_partner on res_partner.id = stock_location.partner_id) """)
def init(self, cr, uid=1): drop_view_if_exists(cr, 'report_random_timesheet') cr.execute("""create or replace view report_random_timesheet as ( select line.id as id, line.account_id as analytic_account_id, line.name as name, line.unit_amount as quantity, line.date as date, line.user_id as user_id from account_analytic_line line, hr_department dept,hr_department_user_rel dept_user where (dept.id = dept_user.department_id AND dept_user.user_id=line.user_id AND line.user_id is not null) AND (dept.manager_id = %s) AND (line.date <= CURRENT_DATE AND line.date > (CURRENT_DATE-3)) LIMIT 10 ) """, (uid,))
def init(self, cr): drop_view_if_exists(cr, 'report_timesheet_user') cr.execute(""" create or replace view report_timesheet_user as ( select min(l.id) as id, l.date as name, l.user_id, sum(l.unit_amount) as quantity, sum(l.amount) as cost from account_analytic_line l where user_id is not null group by l.date, l.user_id ) """)
def init(self, cr): drop_view_if_exists(cr, "account_account_with_postings") cr.execute(""" create or replace view account_account_with_postings as select a.id, a.code, a.name, a.company_id, a.shortcut, a.currency_id, a.note, a.user_type, a.reconcile from account_account a where exists (select 'x' from account_account_period_sum where account_id = a.id and company_id = a.company_id); """)
def init(self, cr): drop_view_if_exists(cr, 'report_timesheet_account_date') cr.execute(""" create or replace view report_timesheet_account_date as ( select min(id) as id, to_char(date,'YYYY') as name, to_char(date,'MM') as month, user_id, account_id, sum(unit_amount) as quantity from account_analytic_line group by to_char(date,'YYYY'),to_char(date,'MM'), user_id, account_id ) """)
def init(self, cr): drop_view_if_exists(cr, 'report_timesheet_account') cr.execute(""" create or replace view report_timesheet_account as ( select min(id) as id, to_char(create_date, 'YYYY') as name, to_char(create_date,'MM') as month, user_id, account_id, sum(unit_amount) as quantity from account_analytic_line group by to_char(create_date, 'YYYY'),to_char(create_date, 'MM'), user_id, account_id ) """)
def init(self, cr): drop_view_if_exists(cr, 'wms_report_stock_available') cr.execute(""" CREATE OR REPLACE VIEW wms_report_stock_available AS ( SELECT max(sub.id) AS id, sl.company_id, sl.warehouse_id, sub.location_id, sub.product_id, pt.uom_id, sub.prodlot_id, sub.usage, sum(sub.qty) AS product_qty, 0.0 as product_warehouse_qty FROM ( SELECT -max(sm.id) AS id, sm.location_id, sm.product_id, sm.prodlot_id, sl.usage, -sum(sm.product_qty /uo.factor) AS qty FROM stock_move as sm LEFT JOIN stock_location sl ON (sl.id = sm.location_id) LEFT JOIN product_uom uo ON (uo.id=sm.product_uom) WHERE state = 'done' AND sm.location_id != sm.location_dest_id GROUP BY sm.location_id, sm.product_id, sm.product_uom, sm.prodlot_id, sl.usage UNION ALL SELECT max(sm.id) AS id, sm.location_dest_id AS location_id, sm.product_id, sm.prodlot_id, sl.usage, sum(sm.product_qty /uo.factor) AS qty FROM stock_move AS sm LEFT JOIN stock_location sl ON (sl.id = sm.location_dest_id) LEFT JOIN product_uom uo ON (uo.id=sm.product_uom) WHERE sm.state = 'done' AND sm.location_id != sm.location_dest_id GROUP BY sm.location_dest_id, sm.product_id, sm.product_uom, sm.prodlot_id, sl.usage ) AS sub LEFT JOIN stock_location sl ON sl.id = sub.location_id LEFT JOIN product_product pp ON pp.id = sub.product_id LEFT JOIN product_template pt ON pt.id = pp.product_tmpl_id GROUP BY sub.location_id, sub.product_id, sub.prodlot_id, sub.usage, sl.warehouse_id, pt.uom_id, sl.company_id HAVING sum(sub.qty) > 0) """)
def init(self, cr): drop_view_if_exists(cr, "account_account_period_sum_delta") cr.execute(""" create or replace view account_account_period_sum_delta as select min(id) as id, company_id,account_id, name,period_id, date_start, fiscalyear_id, sum(balance_curr) as balance_curr, sum(balance_prev) as balance_prev, sum(balance_curr) - sum(balance_prev) as diff, case when sum(balance_prev) != 0 then ((sum(balance_curr) / sum(balance_prev)) -1)*100 else 0.0 end as diff_pro from account_account_period_sum_cur_prev group by company_id,account_id, name, period_id,fiscalyear_id,date_start having company_id > 0; """)
def init(self, cr): drop_view_if_exists(cr, 'report_timesheet_user') cr.execute(""" create or replace view report_timesheet_user as ( select min(l.id) as id, to_char(l.date,'YYYY') as name, to_char(l.date,'MM') as month, l.user_id, sum(l.unit_amount) as quantity, sum(l.amount) as cost from account_analytic_line l where user_id is not null group by l.date, to_char(l.date,'YYYY'),to_char(l.date,'MM'), l.user_id ) """)
def init(self, cr): drop_view_if_exists(cr, 'stock_report_prodlots') cr.execute(""" create or replace view stock_report_prodlots as ( select max(id) as id, location_id, product_id, prodlot_id, sum(qty) as qty from ( select -max(sm.id) as id, sm.location_id, sm.product_id, sm.prodlot_id, -sum(sm.product_qty / uo.factor * pu.factor) as qty from stock_move as sm left join stock_location sl on (sl.id = sm.location_id) left join product_uom uo on (uo.id=sm.product_uom) left join product_product pp on (sm.product_id=pp.id) left join product_template pt on (pp.product_tmpl_id=pt.id) left join product_uom pu on (pt.uom_id=pu.id) where sm.state = 'done' group by sm.location_id, sm.product_id, sm.product_uom, sm.prodlot_id union all select max(sm.id) as id, sm.location_dest_id as location_id, sm.product_id, sm.prodlot_id, sum(sm.product_qty / uo.factor * pu.factor) as qty from stock_move as sm left join stock_location sl on (sl.id = sm.location_dest_id) left join product_uom uo on (uo.id=sm.product_uom) left join product_product pp on (sm.product_id=pp.id) left join product_template pt on (pp.product_tmpl_id=pt.id) left join product_uom pu on (pt.uom_id=pu.id) where sm.state = 'done' group by sm.location_dest_id, sm.product_id, sm.product_uom, sm.prodlot_id ) as report group by location_id, product_id, prodlot_id )""")
def init(self, cr): """overwrites the create query""" drop_view_if_exists(cr, 'stock_report_prodlots') cr.execute(""" create or replace view stock_report_prodlots as ( select max(id) as id, location_id, product_id, prodlot_id, partner_id, sum(qty) as qty from ( select -max(sm.id) as id, sm.location_id, sm.product_id, sm.prodlot_id, sl.partner_id, -sum(sm.product_qty /uo.factor) as qty from stock_move as sm left join stock_location sl on (sl.id = sm.location_id) left join product_uom uo on (uo.id=sm.product_uom) where state = 'done' group by sm.location_id, sm.product_id, sm.product_uom, sm.prodlot_id, sl.partner_id union all select max(sm.id) as id, sm.location_dest_id as location_id, sm.product_id, sm.prodlot_id, sl.partner_id, sum(sm.product_qty /uo.factor) as qty from stock_move as sm left join stock_location sl on (sl.id = sm.location_dest_id) left join product_uom uo on (uo.id=sm.product_uom) where sm.state = 'done' group by sm.location_dest_id, sm.product_id, sm.product_uom, sm.prodlot_id, sl.partner_id ) as report group by location_id, product_id, prodlot_id, partner_id )""")
def init(self, cr): drop_view_if_exists(cr, "stock_report_tracklots") cr.execute( """ create or replace view stock_report_tracklots as ( select max(id) as id, location_id, product_id, tracking_id, sum(qty) as name from ( select -max(sm.id) as id, sm.location_id, sm.product_id, sm.tracking_id, -sum(sm.product_qty /uo.factor) as qty from stock_move as sm left join stock_location sl on (sl.id = sm.location_id) left join product_uom uo on (uo.id=sm.product_uom) where state = 'done' group by sm.location_id, sm.product_id, sm.product_uom, sm.tracking_id union all select max(sm.id) as id, sm.location_dest_id as location_id, sm.product_id, sm.tracking_id, sum(sm.product_qty /uo.factor) as qty from stock_move as sm left join stock_location sl on (sl.id = sm.location_dest_id) left join product_uom uo on (uo.id=sm.product_uom) where sm.state = 'done' group by sm.location_dest_id, sm.product_id, sm.product_uom, sm.tracking_id ) as report group by location_id, product_id, tracking_id )""" )
def init(self, cr) : drop_view_if_exists(cr, "chricar_location_move_col") drop_view_if_exists(cr, "chricar_report_location_moves_sum") drop_view_if_exists(cr, "chricar_report_location_moves") cr.execute(""" create or replace view chricar_report_location_moves as ( select id*2 as id,name, picking_id,product_id, location_dest_id as location_id, partner_id,period_id,prodlot_id, date, product_qty,move_value_cost,move_value_sale, case when product_qty != 0 then move_value_cost/product_qty else 0 end as cost_price, case when product_qty != 0 then move_value_sale/product_qty else 0 end as sale_price, state, company_id from stock_move union all select id*2-1 as id,name, picking_id,product_id, location_id, partner_id,period_id,prodlot_id, date, -product_qty,-move_value_cost,-move_value_sale, case when product_qty != 0 then move_value_cost/product_qty else 0 end as cost_price, case when product_qty != 0 then move_value_sale/product_qty else 0 end as sale_price, state, company_id from stock_move ) """)
def init(self, cr): drop_view_if_exists(cr, 'chricar_stock_product_partner') cr.execute(""" create view chricar_stock_product_partner as select get_id('chricar_stock_product_partner', (case when s.type = 'in' then 1 when s.type = 'out' then 2 end)::int , a.partner_id, p.id ,product_id) as id, s.type as type, a.partner_id, p.id as period_id ,product_id, sum(product_qty) as product_qty, sum(move_value_cost) as move_value_cost, sum(move_value_sale) as move_value_sale, case when sum(product_qty) > 0 then sum(move_value_cost) / sum(product_qty) else 0 end as avg_price, case when sum(product_qty) > 0 then sum(move_value_sale) / sum(product_qty) else 0 end as avg_sale_price from stock_move, account_period p, res_partner_address a, stock_picking s where a.id= s.address_id and s.id=picking_id and stock_move.date between date_start and date_stop and s.type in ('in','out') and stock_move.state != 'cancel' and s.state != 'cancel' group by get_id('chricar_stock_product_partner', (case when s.type = 'in' then 1 when s.type = 'out' then 2 end)::int , a.partner_id, p.id ,product_id), s.type,a.partner_id, p.id ,product_id; """)
def init(self, cr): drop_view_if_exists(cr, 'chricar_stock_product_by_location_prodlot') drop_view_if_exists(cr, 'chricar_stock_product_by_location') drop_view_if_exists(cr, 'chricar_stock_move_by_location') cr.execute("""create or replace view chricar_stock_move_by_location as select i.id , l.id as location_id,product_id, case when state ='done' then product_qty else 0 end as name, case when state !='done' then product_qty else 0 end as product_qty_pending, i.move_value_cost, date, prodlot_id, case when product_qty != 0 then i.move_value_cost/product_qty else 0 end as average_price, picking_id,production_id,sale_line_id,l.company_id from stock_location l, stock_move i where l.usage='internal' and i.location_dest_id = l.id and state != 'cancel' and i.company_id = l.company_id union all select -o.id , l.id as location_id ,product_id, case when state ='done' then -product_qty else 0 end as name, case when state !='done' then -product_qty else 0 end as product_qty_pending, -o.move_value_cost, date, prodlot_id, case when product_qty != 0 then o.move_value_cost/product_qty else 0 end as average_price, picking_id,production_id,sale_line_id,l.company_id from stock_location l, stock_move o where l.usage='internal' and o.location_id = l.id and state != 'cancel' and o.company_id = l.company_id ;""")
def init(self, cr): drop_view_if_exists(cr, 'stock_product_by_location_prodlot') drop_view_if_exists(cr, 'stock_product_by_location') drop_view_if_exists(cr, 'stock_move_by_location') cr.execute("""create or replace view stock_move_by_location as select i.id , l.id as location_id,product_id, i.name as description, case when state ='done' then product_qty else 0 end as name, case when state !='done' then product_qty else 0 end as product_qty_pending, date, prodlot_id, picking_id,l.company_id from stock_location l, stock_move i where l.usage='internal' and i.location_dest_id = l.id and state != 'cancel' and i.company_id = l.company_id union all select -o.id , l.id as location_id ,product_id, o.name as description, case when state ='done' then -product_qty else 0 end as name, case when state !='done' then -product_qty else 0 end as product_qty_pending, date, prodlot_id, picking_id,l.company_id from stock_location l, stock_move o where l.usage='internal' and o.location_id = l.id and state != 'cancel' and o.company_id = l.company_id ;""")
def init(self, cr): drop_view_if_exists(cr, "account_account_fy_period_sum") cr.execute(""" create or replace view account_account_fy_period_sum as select s.id as id, s.name, s.company_id, s.account_id,s. period_id, p.fiscalyear_id, s.debit as debit ,s.credit as credit, s.debit-s.credit as balance, sum(case when pcum.date_start <= p.date_start then cum.debit-cum.credit else 0 end) as balance_cumulative, --s.sum_fy_period_id as sum_fy_period_id, p.date_start,p.date_stop from account_account_period_sum s, account_period p, account_fiscalyear y, account_account_period_sum cum, account_period pcum where p.id = s.period_id and y.id = p.fiscalyear_id and pcum.id = cum.period_id and y.id = pcum.fiscalyear_id and pcum.date_start <= p.date_start and case when s.name like '%00' then s.id = cum.id else 1=1 end and cum.account_id = s.account_id group by s.id, s.name, s.company_id, s.account_id,s.period_id,p.fiscalyear_id, s.debit,s.credit , s.debit-s.credit , s.sum_fy_period_id , p.date_start, p.date_stop; """)
def init(self, cr): drop_view_if_exists(cr, 'tax_records_issued') cr.execute(""" create or replace view tax_records_issued as ( SELECT min(AML.id) as id, AM.tax_period_id as period_id, AM.date as date, AM.name as document_name, P.name as partner_name, P.vat as vat, sum( case when ATB.position = '7' then credit-debit else 0 end) as col_7, sum( case when ATB.position = '8' then credit-debit else 0 end) as col_8, sum( case when ATB.position = '9' then credit-debit else 0 end) as col_9, sum( case when ATB.position = '10.a' then credit-debit else 0 end) as col_10a, sum( case when ATB.position = '10.a1' then credit-debit else 0 end) as col_10a1, sum( case when ATB.position = '10b' then credit-debit else 0 end) as col_10b, sum( case when ATB.position = '11' then credit-debit else 0 end) as col_11, sum( case when ATB.position = '12' then credit-debit else 0 end) as col_12, sum( case when ATB.position = '13' then credit-debit else 0 end) as col_13, sum( case when ATB.position = '14' then credit-debit else 0 end) as col_14, sum( case when ATB.position = '15' then credit-debit else 0 end) as col_15, sum( case when ATB.position = '16' then credit-debit else 0 end) as col_16, sum( case when ATB.position = '17' then credit-debit else 0 end) as col_17, sum( case when ATB.position = '18' then credit-debit else 0 end) as col_18, sum( case when ATB.position = '19' then credit-debit else 0 end) as col_19, sum( case when ATB.position = '20' then credit-debit else 0 end) as col_20, sum( case when ATB.position = '21' then credit-debit else 0 end) as col_21, sum( case when ATB.position = '22' then credit-debit else 0 end) as col_22, sum( case when ATB.position = '23' then credit-debit else 0 end) as col_23 FROM account_move_line AML, account_move AM, account_tax_book_fields_issued_tax_code_rel REL, account_tax_book_fields ATB, res_partner P WHERE AML.tax_code_id = REL.tax_code_id and REL.tax_book_fields_id = ATB.id and AML.tax_code_id != 0 and AML.partner_id = P.id and AML.move_id = AM.id GROUP BY AM.name, AM.date, AM.tax_period_id, P.name, P.vat ORDER BY AM.name )""")
def init(self, cr): drop_view_if_exists(cr, 'stock_report_prodlots1') cr.execute(""" create or replace view stock_report_prodlots1 as ( select max(id) as id, location_id, product_id, prodlot_id, life_date, use_date, removal_date, alert_date, sum(qty) as name from ( select -max(sm.id) as id, sm.location_id, sm.product_id, sm.prodlot_id, spl.life_date, spl.use_date, spl.removal_date, spl.alert_date, -sum(sm.product_qty /uo.factor) as qty from stock_move as sm left join stock_location sl on (sl.id = sm.location_id) left join stock_production_lot spl on (sm.prodlot_id=spl.id) left join product_uom uo on (uo.id=sm.product_uom) where state = 'done' group by sm.location_id, sm.product_id, sm.product_uom, sm.prodlot_id, spl.life_date, spl.use_date, spl.removal_date, spl.alert_date union all select max(sm.id) as id, sm.location_dest_id as location_id, sm.product_id, sm.prodlot_id, spl.life_date, spl.use_date, spl.removal_date, spl.alert_date, sum(sm.product_qty /uo.factor) as qty from stock_move as sm left join stock_location sl on (sl.id = sm.location_dest_id) left join stock_production_lot spl on (sm.prodlot_id=spl.id) left join product_uom uo on (uo.id=sm.product_uom) where sm.state = 'done' group by sm.location_dest_id, sm.product_id, sm.product_uom, sm.prodlot_id, spl.life_date, spl.use_date, spl.removal_date, spl.alert_date ) as report group by location_id, product_id, prodlot_id, life_date, use_date, removal_date, alert_date )""")
def init(self, cr): drop_view_if_exists(cr, 'hr_expense_report')