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, 'stock_product_location') location_id = self.pool.get('ir.model.data').get_object_reference(cr, None, 'stock', 'stock_location_locations_virtual')[1] cr.execute("""create or replace view stock_product_location as SELECT ROW_NUMBER() OVER (ORDER BY location_id, product_id DESC) AS id, * FROM ( SELECT l.id AS location_id,product_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 AND l.active = True And l.location_id <> %s UNION SELECT l.id AS location_id ,product_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 AND l.active = True And l.location_id <> %s ) AS product_stock_location ORDER BY location_id, product_id DESC ;""" % (str(location_id), str(location_id)))
def init(self, cr): sql.drop_view_if_exists(cr,'tms_analisys_04') cr.execute(""" create or replace view tms_analisys_04 as ( select (control.id) as id, (control.id) as kiosk_id, (control.order_id) as order_id, (control.name_order) as order_name, (control.activity_id) as activity_id, (control.name_activity) as activity_name, (hr_employee_id) as hr_employee_id, (select e.name_related from hr_employee as e where e.id = control.hr_employee_id) as hr_employee_name, (select u.id from fleet_vehicle as u where u.id=o.unit_id) as unit_id, (select u.name from fleet_vehicle as u where u.id=o.unit_id) as unit_name, (select e.id from hr_employee as e where e.id=o.driver_id) as driver_id, (select e.name_related from hr_employee as e where e.id=o.driver_id) as driver_name, (control.date_begin) as date_begin, (control.date_end) as date_end, (control.hours_mechanic) as hours_work from tms_activity_control_time as control, tms_maintenance_order as o where control.state like 'end' and o.state like 'done' and o.id = control.order_id order by control.id ) """)
def init(self, cr): drop_view_if_exists(cr, 'distefano_quants_report') cr.execute(""" create or replace view distefano_quants_report as ( with attr1 as( select prod_id, att_id from product_attribute_value_product_product_rel vpr join product_attribute_value v on(vpr.att_id = v.id and v.attribute_id = 1) ), attr2 as( select prod_id, att_id from product_attribute_value_product_product_rel vpr join product_attribute_value v on(vpr.att_id = v.id and v.attribute_id = 2) ) select min(q.id) as id, sum(q.qty) as qty, q.location_id as location_id, p.product_tmpl_id as product_template_id, p.active as active, attr1.att_id as color_id, attr2.att_id as talla_id, pt.categ_id as product_categ_id from stock_quant q join product_product p on (q.product_id = p.id) left join attr1 on (p.id = attr1.prod_id) left join product_template pt on (pt.id=p.product_tmpl_id) left join attr2 on (p.id = attr2.prod_id) group by location_id, product_template_id, color_id, talla_id, p.active,pt.categ_id )""")
def init(self, cr): drop_view_if_exists(cr, 'prod_last_moved_report') cr.execute(""" create or replace view prod_last_moved_report as ( SELECT sm.id, sm.name AS desc, sm.origin, sm.location_id, sm.product_id, stock_picking_time AS last_moved_date FROM stock_move sm JOIN ( SELECT max(id) AS id FROM stock_move osm WHERE (product_id, stock_picking_time) IN (SELECT sm.product_id, max(sm.stock_picking_time) FROM stock_move sm JOIN stock_location sl ON sm.location_dest_id = sl.id AND sl.name = 'Customers' GROUP BY product_id) GROUP BY product_id) AS csm ON sm.id = csm.id )""")
def init(self, cr): drop_view_if_exists(cr, 'report_profit_picking') cr.execute(""" create or replace view report_profit_picking as ( select sm.id as id, to_char(sm.date, 'YYYY-MM-DD:HH24:MI:SS') as name, sm.date as date, to_char(sm.date, 'YYYY') as year, to_char(sm.date, 'MM') as month, to_char(sm.date, 'YYYY-MM-DD') as day, sm.picking_id as picking_id, sp.type as type, sm.purchase_line_id as purchase_line_id, sm.sale_line_id as sale_line_id, sm.product_id as product_id, sm.location_id as location_id, sm.location_dest_id as location_dest_id, sm.id as stk_mov_id, sm.product_qty as picking_qty, sm.state as state from stock_picking sp right join stock_move sm on (sp.id=sm.picking_id) left join product_product d on (d.id=sm.product_id) left join product_template pt on (pt.id=d.product_tmpl_id) where sm.state='done' and pt.type!='service' order by name ) """)
def init(self, cr): drop_view_if_exists(cr, 'account_report') cr.execute(""" create or replace view account_report as ( (select id,date,account_id, sum(actual_amount) as actual_amount,sum(amount_received) as amount_received from ( SELECT pg_catalog.concat(ail.account_id, '_', ai.date_invoice, '_', ai.type) AS id,ai.id as invoice_id, ai.date_invoice AS date, ail.account_id, CASE WHEN ai.type = 'out_refund' THEN 0 ELSE sum(ail.price_subtotal) END AS actual_amount, CASE WHEN ai.type = 'out_refund' THEN sum( (-ail.price_subtotal) * (ai.amount_total / (ai.amount_tax + ai.amount_untaxed))) ELSE sum(ail.price_subtotal * (ai.amount_total / (ai.amount_tax + ai.amount_untaxed))) END AS amount_received FROM account_invoice ai, account_invoice_line ail WHERE ail.invoice_id = ai.id AND (ai.amount_tax + ai.amount_untaxed) <> 0 AND state = 'paid' GROUP BY ail.account_id, ai.date_invoice, ai.type, ai.id UNION SELECT pg_catalog.concat(ail.account_id, '_', ai.date_invoice, '_', ai.type) AS id,ai.id as invoice_id, ai.date_invoice AS date, ail.account_id, CASE WHEN ai.type = 'out_refund' THEN 0 ELSE max(ai.amount_total) END AS actual_amount, CASE WHEN ai.type = 'out_refund' THEN max(ai.amount_total) * -1 ELSE max(ai.amount_total) END AS amount_received FROM account_invoice ai, account_invoice_line ail WHERE ail.invoice_id = ai.id AND (ai.amount_tax + ai.amount_untaxed) = 0 AND state = 'paid' and ail.account_id not in (select id from account_account where name in ('FINE','Discount','Overcharge')) GROUP BY ail.account_id, ai.date_invoice, ai.type,ai.id UNION SELECT pg_catalog.concat(ail.account_id, '_', ai.date_invoice, '_', ai.type) AS id,ai.id as invoice_id, ai.date_invoice AS date, ail.account_id, CASE WHEN ai.type = 'out_refund' THEN 0 ELSE sum(ai.amount_total) END AS actual_amount, CASE WHEN ai.type = 'out_refund' THEN sum(-ai.amount_total) ELSE sum(ai.amount_total) END AS amount_received FROM account_invoice ai, account_invoice_line ail WHERE ail.invoice_id = ai.id AND (ai.amount_tax + ai.amount_untaxed) = 0 AND state = 'paid' and ail.account_id in (select id from account_account where name in ('FINE','Discount','Overcharge')) GROUP BY ail.account_id, ai.date_invoice, ai.type,ai.id ) as r group by id,date,account_id) )""")
def init(self, cr): sql.drop_view_if_exists(cr,'tms_analisys_02') cr.execute(""" create or replace view tms_analisys_02 as ( select a.id, o.date as date_order, o.id as order_id, a.id as name, a.product_id, e.categ_id as product_category_id, a.date_start as date, to_char(date_trunc('day',a.date_start), 'YYYY') as year, to_char(date_trunc('day',a.date_start), 'MM') as month, to_char(date_trunc('day',a.date_start), 'YYYY-MM-DD') as day, a.hours_real as duration, a.date_start_real as date_start, a.date_end_real as date_end, a.external_workshop, a.invoice_id, c.supplier_invoice_number, o.unit_id, o.driver_id, a.supplier_id, a.parts_cost as spare_parts, a.cost_service as manpower, a.parts_cost_external as spare_parts_external, a.cost_service_external as manpower_external from tms_maintenance_order_activity a left join tms_maintenance_order as o on o.id=a.maintenance_order_id and o.state='done' left join account_invoice as c on c.id=a.invoice_id and c.state <> 'cancel' left join product_product d on d.id=a.product_id left join product_template e on e.id=d.product_tmpl_id where a.state = 'done' order by o.date, a.date_start_real ) """)
def init(self, cr): drop_view_if_exists(cr, 'fedex_odbc') cr.execute(""" CREATE OR REPLACE VIEW fedex_odbc AS ( SELECT carrier.fedex_code AS or_sm_id, picking.id AS picking_id, picking.name AS picking_key, picking.id AS or_key, address.id AS cu_number, picking.name AS or_number, CAST('' AS CHAR(128)) AS or_deliver_instruct, address.name AS or_ship_name, address.street AS or_ship_address1, COALESCE(address.phone, CAST('9999999999' AS CHAR(10))) AS or_ship_phone, COALESCE(address.phone, CAST('9999999999' AS CHAR(10))) AS customer_phone, address.street2 AS or_ship_address2, address.city AS or_ship_city, country.code AS or_ship_country, state.code AS or_ship_state, address.zip AS or_ship_postal_code, CAST('t' AS BOOLEAN) AS residential, COALESCE(address.email, '*****@*****.**') AS em_address, CAST('Y' AS BOOLEAN) AS use_email, CAST('email' AS CHAR(15)) AS notification_type, CAST('Y' AS BOOLEAN) AS qvn_ship_notification_1, CAST ('4' AS CHAR(4)) AS goods_type, CAST('Goods' AS CHAR(16)) AS cn22_description FROM stock_picking picking JOIN res_partner address ON (picking.partner_id = address.id) LEFT OUTER JOIN res_country_state state ON (address.state_id = state.id) LEFT OUTER JOIN res_country country ON (address.country_id = country.id) JOIN delivery_carrier carrier ON (picking.carrier_id = carrier.id) )""")
def init(self, cr): ''' Search method that executes query. ''' drop_view_if_exists(cr, 'custom_project_task') cr.execute(''' create or replace view custom_project_task as ( SELECT 1 AS counter, task.id AS id, task.date_deadline AS deadline, task.user_id AS task_user_id, task.project_leader_id AS project_leader_id, date(task.date_end) AS date_end, to_char(task.date_end,'MM/YYYY') AS period_end, analytic.id AS analytic_id, us.id AS userstory, task.id AS task_id, task_type.name AS state FROM project_task AS task LEFT JOIN project_project AS project ON project.id = task.project_id LEFT JOIN account_analytic_account AS analytic ON analytic.id = project.analytic_account_id LEFT JOIN user_story AS us ON us.id = task.userstory_id LEFT JOIN project_task_type AS task_type ON task_type.id = task.stage_id )''')
def init(self, cr): ''' Search method that executes query. ''' drop_view_if_exists(cr, 'custom_timesheet_all') cr.execute(''' create or replace view custom_timesheet_all as ( SELECT work.id AS id, to_char(work.date,'MM/YYYY') AS period, date(work.date) AS date, analytic.id AS analytic_id, us.id AS userstory, task.id AS task_id, work.user_id AS user_id, work.name AS name, work.hours AS unit_amount, acc_anal_line.to_invoice AS invoiceable, work.hours - (work.hours * (hr_ts_factor.factor / 100)) AS invoiceables_hours FROM project_task_work AS work LEFT JOIN hr_analytic_timesheet AS tsheet ON tsheet.id = work.hr_analytic_timesheet_id LEFT JOIN account_analytic_line AS acc_anal_line ON acc_anal_line.id = tsheet.line_id LEFT JOIN hr_timesheet_invoice_factor AS hr_ts_factor ON hr_ts_factor.id = acc_anal_line.to_invoice LEFT JOIN account_analytic_account AS analytic ON analytic.id = acc_anal_line.account_id LEFT JOIN project_task AS task ON task.id = work.task_id LEFT JOIN user_story AS us ON us.id = task.userstory_id )''')
def init(self, cr): drop_view_if_exists(cr, 'custom_timesheet') cr.execute(''' create or replace view custom_timesheet as ( SELECT work.id AS id, work.date AS date, work.user_id AS user_id, us.id AS userstory_id, us.id AS userstory, analytic.id AS analytic_id, task.name AS task_title, work.name AS name, work.hours AS unit_amount, tsheet.id AS timesheet_id FROM project_task_work AS work LEFT JOIN hr_analytic_timesheet AS tsheet ON tsheet.id = work.hr_analytic_timesheet_id INNER JOIN project_task AS task ON task.id = work.task_id INNER JOIN user_story AS us ON us.id = task.userstory_id INNER JOIN project_project AS project ON project.id = task.project_id INNER JOIN account_analytic_account AS analytic ON analytic.id = project.analytic_account_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.primary_qty) as qty from stock_move as sm left join stock_location sl on (sl.id = sm.location_id) 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.primary_qty) as qty from stock_move as sm left join stock_location sl on (sl.id = sm.location_dest_id) 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): sql.drop_view_if_exists(cr,'tms_analisys_01') cr.execute(""" create or replace view tms_analisys_01 as ( select o.id, o.id as name, o.product_id, o.supervisor_id, o.maint_cycle_id, o.driver_id, o.user_id, o.unit_id, o.date, to_char(date_trunc('day',o.date), 'YYYY') as year, to_char(date_trunc('day',o.date), 'MM') as month, to_char(date_trunc('day',o.date), 'YYYY-MM-DD') as day, o.date_start_real, o.date_end_real, o.duration_real, o.notes, sum(a.parts_cost) as parts_cost, sum(a.cost_service) as cost_service, sum(a.parts_cost_external) as parts_cost_external, sum(a.cost_service_external) as cost_service_external from tms_maintenance_order as o left join tms_maintenance_order_activity a on o.id=a.maintenance_order_id and a.state='done' where o.state = 'done' group by o.id, o.product_id, o.supervisor_id, o.maint_cycle_id, o.driver_id, o.user_id, o.unit_id, o.name, o.date, o.date_start_real, o.date_end_real, o.duration_real, o.notes order by o.date ); """)
def init(self, cr): drop_view_if_exists(cr, "chricar_stock_product_partner") cr.execute( """ create view chricar_stock_product_partner as select 0 as id, s.type as type, a.id as 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 a, stock_picking s where 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 s.type,a.id,p.id ,product_id; """ )
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, "wms_report_stock_available") cr.execute( """ CREATE OR REPLACE VIEW wms_report_stock_available AS ( WITH RECURSIVE location(id, name, parent_id, warehouse_id) AS ( select sw.lot_stock_id, ''::varchar, 0, sw.id FROM stock_warehouse sw UNION SELECT sl.id, sl.name, sl.location_id, sl.warehouse_id FROM stock_location sl, location WHERE sl.location_id = location.id) SELECT max(id) AS id, (SELECT warehouse_id FROM stock_location WHERE id=report.location_id) AS warehouse_id, location_id, product_id, (SELECT product_template.uom_id FROM product_product, product_template WHERE product_product.product_tmpl_id = product_template.id AND product_product.id = report.product_id) AS uom_id, prodlot_id, usage, sum(qty) AS product_qty, sum(qty_v) AS product_qty_v, sum(qty_l) AS product_qty_l, sum(qty_a) AS product_qty_a FROM ( SELECT -max(sm.id) AS id, sm.location_id, sm.product_id, sm.prodlot_id, sl.usage, -sum(case when sm.state='done' then (sm.product_qty /uo.factor) else 0 end) AS qty, -sum(case when (sm.state='done' or sm.state='confirmed') then (sm.product_qty /uo.factor) else 0 end) AS qty_v, -sum(case when (sm.state='assigned') then (sm.product_qty /uo.factor) else 0 end) AS qty_l, -sum(case when (sm.state='done' or sm.state='assigned') then (sm.product_qty /uo.factor) else 0 end) AS qty_a 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 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(case when sm.state='done' then (sm.product_qty /uo.factor) else 0 end) AS qty, sum(case when (sm.state='done' or sm.state='confirmed') then (sm.product_qty /uo.factor) else 0 end) AS qty_v, 0 AS qty_l, sum(case when (sm.state='done') then (sm.product_qty /uo.factor) else 0 end) AS qty_a 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.location_id != sm.location_dest_id GROUP BY sm.location_dest_id, sm.product_id, sm.product_uom, sm.prodlot_id, sl.usage ) AS report GROUP BY location_id, product_id, prodlot_id, usage ) """ )
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): sql.drop_view_if_exists(cr,'tms_analisys_05') cr.execute(""" create or replace view tms_analisys_05 as ( select pl.id as id, pl.id as product_line_id, (select p.name_template from product_product as p where p.id=pl.product_id) as product_name, (select p.id from product_product as p where p.id=pl.product_id) as product_id, pl.quantity as quantity, pl.list_price as price, (pl.quantity*pl.list_price) as total_price, (select p.name_template from product_product as p where p.id = activity.product_id) as activity_name, (activity.id ) as activity_id, o.name as order_name, o.id as order_id from tms_product_line as pl, tms_maintenance_order_activity as activity, tms_maintenance_order as o where pl.state like 'delivered' and pl.activity_id = activity.id and o.id = activity.maintenance_order_id ) """)
def init(self, cr): 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, '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, 'prodlots_report') cr.execute(""" create or replace view prodlots_report as ( select report_without_unit.id, location_id, prodlot_id, (qty * product_uom.factor) as qty, product_id, life_date, product_uom.id as unit_id from (select max(id) as id, location_id, product_id, prodlot_id, life_date, sum(qty) as qty from ( select -max(sm.id) as id, sm.location_id, sm.product_id, sm.prodlot_id, spl.life_date, -sum(sm.product_qty /uo.factor) as qty from stock_move as sm left join stock_production_lot spl on (spl.id = sm.prodlot_id) 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, spl.life_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, sum(sm.product_qty /uo.factor) as qty from stock_move as sm left join stock_production_lot spl on (spl.id = sm.prodlot_id) 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, spl.life_date ) as report group by location_id, product_id, prodlot_id, life_date ) as report_without_unit left join product_product on (product_product.id=report_without_unit.product_id) left join product_template on (product_template.id=product_product.product_tmpl_id) left join product_uom on (product_uom.id=product_template.uom_id) )""")
def init(self,cr): drop_view_if_exists(cr,'stock_move_inventory_report') cr.execute(""" create or replace view stock_move_inventory_report AS(select row_number() OVER (order by sm.write_date) as id,pp.name_template as name, sum(sm.product_qty) as quantity,sm.write_date::timestamp::date as date_order, sm.location_dest_id, sm.location_id from stock_move sm inner join product_product pp on sm.product_id=pp.id and sm.state='done' GROUP BY sm.write_date,pp.name_template,pp.id,sm.location_dest_id,sm.location_id ORDER BY pp.id , date_order) """)
def init(self, cr): drop_view_if_exists(cr, 'report_intrastat') cr.execute(""" create or replace view report_intrastat as ( select to_char(inv.date_invoice, 'YYYY') as name, to_char(inv.date_invoice, 'MM') as month, inv.partner_id as partner_id, min(inv_line.id) as id, intrastat.id as intrastat_id, inv_country.id as country_id, sum(case when inv_line.price_unit is not null then CASE WHEN inv.type in ('out_invoice', 'in_invoice') THEN inv_line.price_subtotal WHEN inv.type in ('out_refund', 'in_refund') THEN - inv_line.price_subtotal end else 0 end) as value, (SELECT sum(pick.weight_edit) as weight from stock_picking pick where pick.invoice_id = inv.id) 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 = 'out_invoice' THEN 'export' WHEN inv.type = 'in_invoice' THEN 'import' WHEN inv.type = 'out_refund' THEN 'export_refund' WHEN inv.type = 'in_refund' THEN 'import_refund' 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.date_invoice, 'YYYY'), to_char(inv.date_invoice, 'MM'), inv.partner_id,intrastat.id,inv.type,pt.intrastat_id, inv_country.id,inv.number, inv.id, inv.currency_id)""")
def init(self, cr): drop_view_if_exists(cr, 'stock_report_analytic_account') cr.execute(""" create or replace view stock_report_analytic_account as ( select max(id) as id, location_id, usage, product_id, analytic_account_id, analytic_reserved, sum(qty) as qty from ( select -max(sm.id) as id, sm.location_id, sl.usage, sm.product_id, sm.analytic_account_id, sm.analytic_reserved, -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, sl.usage, sm.product_id, sm.product_uom, sm.analytic_account_id, sm.analytic_reserved union all select max(sm.id) as id, sm.location_dest_id as location_id, sl.usage, sm.product_id, sm.analytic_account_id, sm.analytic_reserved, 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, sl.usage, sm.product_id, sm.product_uom, sm.analytic_account_id, sm.analytic_reserved ) as report group by location_id, usage, product_id, analytic_account_id, analytic_reserved )""")
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 * inv_line.quantity) else (pt.weight * 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, inv.company_id as company_id 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.uom_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, inv.company_id )""" )
def init(self, cr): drop_view_if_exists(cr, 'account_count_report') cr.execute(""" create or replace view account_count_report as ( select concat(ail.account_id, '_', ai.date_invoice) as id, ai.date_invoice as date, ail.account_id as account_id, count(*) as count from account_invoice ai, account_invoice_line ail where ail.invoice_id = ai.id group by ail.account_id, ai.date_invoice )""")
def init(self, cr): sql.drop_view_if_exists(cr, 'tms_waybill_analysis') cr.execute(""" create or replace view tms_waybill_analysis as select row_number() over() as id, a.office_id, a.waybill_category, a.sequence_id, a.state, a.name, date_order, --date_trunc('day', a.date_order) as date_order, to_char(date_trunc('day',a.date_order), 'YYYY') as year, to_char(date_trunc('day',a.date_order), 'MM') as month, to_char(date_trunc('day',a.date_order), 'YYYY-MM-DD') as day, a.partner_id, a.travel_id, d.employee_id, d.unit_id, fv.name as unit_char, d.trailer1_id, d.dolly_id, d.trailer2_id, d.route_id, e.departure_id, e.arrival_id, a.currency_id, a.waybill_type, a.invoice_id, a.invoice_name, a.user_id, c.tms_category, b.product_id, d.framework, f.product_id as shipped_product_id, sum(f.product_uom_qty) / (case (select count(id) from tms_waybill_line where waybill_id=a.id)::FLOAT when 0.0 then 1 else (select count(id) from tms_waybill_line where waybill_id=a.id)::FLOAT end) qty, sum(b.price_subtotal) / (case (select count(id) from tms_waybill_shipped_product where waybill_id=a.id)::FLOAT when 0.0 then 1 else (select count(id) from tms_waybill_shipped_product where waybill_id=a.id)::FLOAT end) amount, a.operation_id from tms_waybill a left join tms_waybill_line b on (b.waybill_id = a.id and b.line_type = 'product') left join product_product c on (c.id = b.product_id) left join tms_travel d on (a.travel_id = d.id) left join fleet_vehicle fv on (d.unit_id = fv.id) left join tms_route e on (d.route_id = e.id) left join tms_waybill_shipped_product f on (f.waybill_id = a.id) group by a.id, c.id, a.office_id, a.sequence_id, a.state, a.name, a.date_order, a.partner_id, a.travel_id, d.employee_id, d.unit_id, fv.name, d.trailer1_id, d.dolly_id, d.trailer2_id, d.route_id, e.departure_id, e.arrival_id, a.currency_id, a.waybill_type, a.invoice_id, a.invoice_name, a.user_id, c.tms_category, b.product_id, d.framework, b.price_subtotal, f.product_id order by a.office_id, a.date_order, a.name ; """)
def init(self, cr): sql.drop_view_if_exists(cr, 'report_sale_order_board') cr.execute(""" CREATE view report_sale_order_board as select to_char(date_order,'IYYYIW')::int as id, to_char(date_order,'IYYY-IW') as week, sum(case when state='draft' then amount_untaxed else 0 end) as so_untaxed_draft, sum(case when state='progress' then amount_untaxed else 0 end) as so_untaxed_progress, sum(case when state='done' then amount_untaxed else 0 end) as so_untaxed_done from sale_order where state != 'cancel' group by to_char(date_order,'IYYYIW')::int, to_char(date_order,'IYYY-IW') order by 1 desc limit 13; """)
def init(self, cr): drop_view_if_exists(cr, 'wms_report_stock_available') cr.execute(""" CREATE OR REPLACE VIEW wms_report_stock_available AS ( WITH RECURSIVE location(id, name, parent_id, warehouse_id) AS ( select sw.lot_stock_id, ''::varchar, 0, sw.id FROM stock_warehouse sw UNION SELECT sl.id, sl.name, sl.location_id, sl.warehouse_id FROM stock_location sl, location WHERE sl.location_id = location.id) SELECT max(id) AS id, (SELECT warehouse_id FROM stock_location WHERE id=report.location_id) AS warehouse_id, location_id, product_id, (SELECT product_template.uom_id FROM product_product, product_template WHERE product_product.product_tmpl_id = product_template.id AND product_product.id = report.product_id) AS uom_id, prodlot_id, usage, sum(qty) AS product_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 report GROUP BY location_id, product_id, prodlot_id, usage HAVING sum(qty) > 0) """)
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, 'prodlots_report') cr.execute(""" create or replace view prodlots_report as ( select report_without_unit.id,report_without_unit.location_id,prodlot_id, (qty * product_uom.factor) as qty, product_category.name as category, (case WHEN (sale_price != 0) then sale_price else list_price end ) as list_price, (case WHEN (sale_price != 0) then qty*sale_price else qty*list_price end) as value, report_without_unit.product_id, life_date, expired, expire_60_90, expire_30_60, expire_30, product_uom.id as unit_id, swo.product_min_qty as reorder_level from (select max(id) as id, location_id, product_id, prodlot_id, sale_price, life_date,expired, expire_60_90,expire_30_60,expire_30,sum(qty) as qty from ( select -max(sm.id) as id, sm.location_id, sm.product_id, sm.prodlot_id, spl.sale_price, spl.life_date,spl.expired,spl.expire_60_90,spl.expire_30_60,spl.expire_30, -sum(sm.product_qty /uo.factor) as qty from stock_move as sm left join stock_production_lot spl on (spl.id = sm.prodlot_id) left join stock_location sl on (sl.id = sm.location_id) left join product_uom uo on (uo.id=sm.product_uom) where state in ('done', 'confirmed') group by sm.location_id, sm.product_id, sm.product_uom, sm.prodlot_id, spl.life_date,spl.sale_price,spl.expired,spl.expire_60_90,spl.expire_30_60,spl.expire_30 union all select max(sm.id) as id, sm.location_dest_id as location_id, sm.product_id, sm.prodlot_id, spl.sale_price, spl.life_date, spl.expired,spl.expire_60_90,spl.expire_30_60,spl.expire_30, sum(sm.product_qty /uo.factor) as qty from stock_move as sm left join stock_production_lot spl on (spl.id = sm.prodlot_id) 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 in ('done', 'confirmed') group by sm.location_dest_id, sm.product_id, sm.product_uom, sm.prodlot_id, spl.life_date,spl.expired,spl.expire_60_90,spl.expire_30_60,spl.expire_30,spl.sale_price ) as report group by location_id, product_id, prodlot_id, sale_price,life_date,expired,expire_60_90,expire_30_60,expire_30 ) as report_without_unit left join product_product on (product_product.id=report_without_unit.product_id) left join product_template on (product_template.id=product_product.product_tmpl_id) left join product_category on (product_category.id=product_template.categ_id) left join product_uom on (product_uom.id=product_template.uom_id) left join stock_warehouse_orderpoint swo on (product_product.id=swo.product_id) and swo.active = true )""")
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, "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, 'product_stock_card') cr.execute("""CREATE OR REPLACE VIEW product_stock_card 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, sm.price_unit AS price_unit, sm.product_qty * sm.price_unit AS amount, case WHEN sp.name is null THEN sm.name ELSE sp.name END as name, sm.location_id as location_id, sm.location_dest_id as location_dest_id, CASE WHEN sp.type = 'internal' and (select usage from stock_location sl WHERE sl.id = sm.location_id) = (select usage from stock_location sl WHERE sl.id = sm.location_dest_id) THEN 'move' WHEN sp.type is null THEN 'adjust' ELSE sp.type END as type, sm.product_qty as picking_qty, pt.uom_id as default_uom, sm.product_uom as move_uom, sp.ref_order_id as ref_order_id, sp.ref_project_name as ref_project_name 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 LEFT OUTER JOIN product_product d on (d.id=sm.product_id) LEFT OUTER JOIN product_template pt on (pt.id=d.product_tmpl_id) WHERE sm.state = 'done' and sm.location_id <> sm.location_dest_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, max(m.date) as move_date, p.active as active from product_product p left join ( stock_inventory_line l inner join stock_inventory s on (l.inventory_id=s.id and s.state = 'done') ) on (p.id=l.product_id) left join stock_move m on (m.product_id=p.id and m.state = 'done') group by p.id )""")
def init(self, cr): drop_view_if_exists(cr, 'supplier_category_report') cr.execute(""" create or replace view supplier_category_report AS( select prl.id,pp.name_template,prl.product_qty,po.create_date as date_order,po.amount_total,spl.name,xpsc.x_name from stock_move sm INNER JOIN purchase_order_line prl on sm.purchase_line_id=prl.id and sm.state='done' and sm.purchase_line_id is not null AND sm.prodlot_id is not NULL LEFT JOIN stock_production_lot spl on spl.id = sm.prodlot_id LEFT JOIN x_product_supplier_category xpsc on xpsc.id = spl.x_supplier_category LEFT JOIN purchase_order po on prl.order_id=po.id LEFT JOIN product_product pp on pp.id = sm.product_id ) """)
def init(self, cr): drop_view_if_exists(cr, 'custom_timesheet') cr.execute(''' create or replace view custom_timesheet as ( SELECT work.id AS id, work.date AS date, work.user_id AS user_id, us.id AS userstory_id, us.id AS userstory, analytic.id AS analytic_id, task.name AS task_title, work.name AS name, work.hours AS unit_amount FROM project_task_work AS work INNER JOIN project_task AS task ON task.id = work.task_id INNER JOIN user_story AS us ON us.id = task.userstory_id INNER JOIN project_project AS project ON project.id = task.project_id INNER JOIN account_analytic_account AS analytic ON analytic.id = project.analytic_account_id )''')
def init(self, cr): sql.drop_view_if_exists(cr, 'tms_travel_analysis') cr.execute (""" CREATE OR REPLACE VIEW tms_travel_analysis as select row_number() over() as id, a.office_id, a.name, a.date, to_char(date_trunc('day',a.date), 'YYYY') as year, to_char(date_trunc('day',a.date), 'MM') as month, to_char(date_trunc('day',a.date), 'YYYY-MM-DD') as day, a.state, a.employee_id, a.framework, f.unit_type_id, a.unit_id, f.name as unit_char, a.trailer1_id, a.dolly_id, a.trailer2_id, a.route_id, a.departure_id departure, a.arrival_id arrival, b.id as waybill_id, b.date_order as waybill_date, case when b.partner_id is null then 1 else b.partner_id end partner_id, b.state as waybill_state, b.sequence_id as waybill_sequence, b.currency_id, b.waybill_type, b.invoice_id, b.invoice_name, b.user_id, c.product_id, c.price_subtotal / (case (select count(id) from tms_waybill_shipped_product where waybill_id=b.id)::FLOAT when 0.0 then 1.0 else (select count(id) from tms_waybill_shipped_product where waybill_id=b.id)::FLOAT end) as amount, d.tms_category, e.product_id as shipped_product_id, e.product_uom_qty / (case (select count(id) from tms_waybill_line where waybill_id=b.id)::FLOAT when 0.0 then 1 else (select count(id) from tms_waybill_line where waybill_id=b.id)::FLOAT end) as qty, a.operation_id from tms_travel a left join tms_waybill b on (a.id = b.travel_id and b.state in ('approved', 'confirmed')) left join tms_waybill_line c on (c.waybill_id = b.id and c.line_type = 'product') left join product_product d on (c.product_id = d.id) left join tms_waybill_shipped_product e on (e.waybill_id = b.id) left join fleet_vehicle f on (a.unit_id = f.id) order by a.office_id, a.name, a.date; """)
def init(self, cr): drop_view_if_exists(cr, 'report_cost') cr.execute(''' create or replace view report_cost as ( select invo.date_invoice as date, line.id as id, line.product_id as product_id, invo.type as type_inv, case when invo.type='out_refund' then 0.0 else case when invo.type='in_invoice' then line.price_unit else case when invo.type='in_refund' then line.price_unit*(-1) else 0.0 end end end as last_cost, line.uos_id as uom_id, case when invo.type='out_refund' then line.price_unit*(-1) else case when invo.type='in_invoice' then 0.0 else line.price_unit end end as price_unit from account_invoice invo inner join account_invoice_line line on (invo.id=line.invoice_id) where invo.state in ('open','paid') )''')
def init(self, cr): # union money_order, other_money_order, money_transfer_order # cr = self._cr # tools.drop_view_if_exists(cr, 'bank_statements_report') drop_view_if_exists(cr, 'bank_statements_report') cr.execute(""" create or replace view bank_statements_report as ( select ROW_NUMBER() OVER(ORDER BY journal_id,date) AS id, absl.date as date, absl.name as name, absl.journal_id as journal_id, (CASE WHEN absl.amount >= 0 THEN absl.amount ELSE 0 END) AS get, (CASE WHEN absl.amount < 0 THEN -absl.amount ELSE 0 END) AS pay, 0 as balance, absl.partner_id as partner_id, absl.note as note from account_bank_statement_line as absl )""")
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 vals and 'is_no_display' in vals: for doc in self.browse(cr, uid, ids, context=context): if doc.is_no_display: self.unlink_values(cr, uid, doc.id, context=context) else: self.create_values(cr, uid, doc.id, 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, 'distefano_report_pos_product_template') cr.execute(""" create or replace view distefano_report_pos_product_template as ( select min(l.id) as id, sum(l.qty) as qty, count(*) as nbr, s.date_order 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 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 as product_template_id, p.active as active, vpr1.att_id as color_id, vpr2.att_id as talla_id from pos_order_line l join product_product p on (l.product_id = p.id) join pos_order s on (s.id=l.order_id) left join product_template pt on (pt.id=p.product_tmpl_id) left join product_uom u on (u.id=pt.uom_id) join product_attribute_value_product_product_rel vpr1 on(p.id = vpr1.prod_id) join product_attribute_value v1 on(vpr1.att_id = v1.id and v1.attribute_id = 1) join product_attribute_value_product_product_rel vpr2 on(p.id = vpr2.prod_id) join product_attribute_value v2 on(vpr2.att_id = v2.id and v2.attribute_id = 2) group by product_template_id, color_id, talla_id, s.date_order, s.partner_id,s.state, pt.categ_id, s.user_id,s.location_id,s.company_id,s.sale_journal,l.product_id,s.create_date,p.active having sum(l.qty * u.factor) != 0 )""")
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): sql.drop_view_if_exists(cr, 'tms_travel_availability') cr.execute (""" CREATE OR REPLACE VIEW tms_travel_availability as select row_number() over() as id, a.id as name, a.supplier_unit, a.fleet_type, b.office_id, b.id travel_id, b.trailer1_id, b.dolly_id, b.trailer2_id, b.employee_id, case when b.date is null then current_date else b.date end date, b.date_start, b.date_end, case when b.state is null then 'free' else b.state end state, b.framework, a.unit_type_id, b.route_id, b.departure_id departure, b.arrival_id arrival, c.id waybill_id, c.date_order waybill_date, c.partner_id, b.user_id from fleet_vehicle a left join tms_travel b on a.id = b.unit_id and b.state in ('draft','progress') left join tms_waybill c on c.travel_id = b.id and c.state <> 'cancelled' order by a.name, b.date, a.office_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, "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, 'padron_report') cr.execute(""" create or replace view padron_report as ( select p.id as id, p.id as padron_id, p.registro as registro, p.dni as dni, p.edad, p.nombre as nombre, p.apellidos as apellidos, p.name, p.email, p.lugar_trabajo as lugar_trabajo, p.tipo_trabajador, p.grado_instruccion, p.sistema_pension, p.estado from padron_padron p )""")
def init(self, cr): sql.drop_view_if_exists(cr, 'tms_analisys_03') cr.execute(""" create or replace view tms_analisys_03 as ( select sm.id, pl.id as name, sm.date, o.date as date_order, o.id as order_id, task.id as task_id, task.date_start, sm.product_id, prod_tmpl.categ_id as product_category_id, to_char(date_trunc('day',sm.date), 'YYYY') as year, to_char(date_trunc('day',sm.date), 'MM') as month, to_char(date_trunc('day',sm.date), 'DD') as day, task.external_workshop, sm.unit_id, o.driver_id, task.supplier_id, sm.product_qty qty, sm.product_uom, case when aml.debit > 0 then aml.debit / sm.product_qty else pl.list_price end as list_price, case when aml.debit > 0 then aml.debit else pl.quantity * pl.list_price end as amount from stock_move sm left join tms_product_line pl on pl.stock_move_id=sm.id and pl.state = 'delivered' left join account_move_line aml on aml.stock_move_id=sm.id and aml.state='valid' and aml.debit > 0 left join account_move am on aml.move_id=am.id left join tms_maintenance_order_activity task on task.id=sm.activity_id --and task.state='done' left join tms_maintenance_order as o on o.id=sm.maintenance_order_id --and o.state='done' left join product_product prod on prod.id=sm.product_id left join product_template prod_tmpl on prod_tmpl.id=prod.product_tmpl_id where sm.state='done' and sm.picking_id is not null and sm.unit_id is not null --and sm.driver_id is not null and ( sm.location_dest_id in (select id from stock_location where usage in ('production', 'inventory')) or sm.location_id in (select id from stock_location where usage in ('production', 'inventory')) ) order by sm.date ); """)
def init(self, cr): drop_view_if_exists(cr, 'batch_stock_future_forecast') cr.execute(""" create or replace view batch_stock_future_forecast as ( select max(id) as id, location_id, product_id, prodlot_id, round(sum(qty),3) as qty from ( select -max(sm.id) as id, sm.location_id, sm.product_id, sm.prodlot_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 in ('done', 'confirmed') 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) 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 in ('done', 'confirmed') 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): drop_view_if_exists(cr,'kpi_data_store') cr.execute(""" create or replace view kpi_data_store AS(select row_number() OVER (order by sm.write_date) as id,pp.name_template as name, pp.id as product_id,sm.product_qty as quantity,sm.write_date as date_order, sm.location_dest_id, sm.location_id,sm.prodlot_id, CASE WHEN sm.location_dest_id =27 THEN '+' WHEN sm.location_id =27 THEN '-' ELSE '*' END as way, srcloc.name as fromloc, dstloc.name as toloc, pp.default_code as itemreference, swo.product_min_qty,swo.product_max_qty,swo.x_bare_minimum, pt.x_formulary, pt.x_govt, pt.x_low_cost_eq, pp.antibiotic,pp.other_item,pp.medical_item,pp.lab_item, xpsc.x_name as supplier_category, pt.list_price, pc.name as product_category, rp.name as supplier,pol.price_unit as purchase_price from stock_move sm inner join product_product pp on sm.product_id=pp.id and sm.state='done' AND (sm.location_dest_id=27 or sm.location_id=27) LEFT JOIN stock_warehouse_orderpoint swo on swo.product_id=sm.product_id and swo.location_id = (SELECT id FROM stock_location where name = 'BPH Storeroom') LEFT JOIN product_template pt on pt.id = pp.product_tmpl_id LEFT JOIN stock_production_lot spl on spl.id=sm.prodlot_id LEFT JOIN x_product_supplier_category xpsc on xpsc.id=spl.x_supplier_category LEFT JOIN product_category pc on pt.categ_id= pc.id LEFT JOIN purchase_order_line pol on pol.id=sm.purchase_line_id LEFT JOIN res_partner rp on rp.id=pol.partner_id LEFT JOIN stock_location dstloc on dstloc.id = sm.location_dest_id LEFT JOIN stock_location srcloc on srcloc.id = sm.location_id ORDER BY pp.id , date_order) """)
def init(self, cr): drop_view_if_exists(cr, 'report_ysale_order') cr.execute(""" create or replace view report_yo as ( select s.id as id, s.name as name, sum(sl.subtotal) as order_amount from ysale_order s left join ysale_order_line sl on (s.id=sl.order_id) group by s.id ); create or replace view report_yp as ( SELECT s.id, s.name, sum(sp.money) AS pay_amount FROM ysale_order s LEFT JOIN ysale_pay sp ON s.id = sp.order_id GROUP BY s.id ); create or replace view report_ysale_order as ( SELECT o.id, o.name, o.order_amount, p.pay_amount FROM report_yo o LEFT JOIN report_yp p ON o.id = p.id ) """)
def init(self, cr): drop_view_if_exists(cr, "stock_levels_report") cr.execute("""CREATE OR REPLACE VIEW stock_levels_report AS ( WITH link_location_warehouse AS ( SELECT sl.id AS location_id, sw.id AS warehouse_id FROM stock_warehouse sw LEFT JOIN stock_location sl_view ON sl_view.id = sw.view_location_id LEFT JOIN stock_location sl ON sl.parent_left >= sl_view.parent_left AND sl.parent_left <= sl_view.parent_right ), min_product AS ( SELECT min(sm.date_expected) - INTERVAL '1 second' AS min_date, sm.product_id AS product_id FROM stock_move sm LEFT JOIN stock_location sl ON sm.location_dest_id = sl.id LEFT JOIN link_location_warehouse link ON link.location_id = sm.location_id LEFT JOIN link_location_warehouse link_dest ON link_dest.location_id = sm.location_dest_id WHERE ((link_dest.warehouse_id IS NOT NULL AND (link.warehouse_id IS NULL OR link.warehouse_id != link_dest.warehouse_id)) OR (link.warehouse_id IS NOT NULL AND (link_dest.warehouse_id IS NULL OR link.warehouse_id != link_dest.warehouse_id))) AND sm.state :: TEXT <> 'cancel' :: TEXT AND sm.state :: TEXT <> 'done' :: TEXT AND sm.state :: TEXT <> 'draft' :: TEXT GROUP BY sm.product_id ) SELECT foo.product_id :: TEXT || '-' || foo.warehouse_id :: TEXT || '-' || coalesce(foo.move_id :: TEXT, 'existing') AS id, foo.product_id, pt.categ_id AS product_categ_id, foo.move_type, sum(foo.qty) OVER (PARTITION BY foo.warehouse_id, foo.product_id ORDER BY date) AS qty, foo.date AS date, foo.qty AS move_qty, foo.warehouse_id, foo.other_warehouse_id FROM ( SELECT sq.product_id AS product_id, 'existing' :: TEXT AS move_type, coalesce(min(mp.min_date), max(sq.in_date)) AS date, sum(sq.qty) AS qty, NULL AS move_id, link.warehouse_id, NULL AS other_warehouse_id FROM stock_quant sq LEFT JOIN stock_location sl ON sq.location_id = sl.id LEFT JOIN link_location_warehouse link ON link.location_id = sl.location_id LEFT JOIN min_product mp ON mp.product_id = sq.product_id WHERE link.warehouse_id IS NOT NULL GROUP BY sq.product_id, link.warehouse_id UNION ALL SELECT sm.product_id AS product_id, 'in' :: TEXT AS move_type, sm.date_expected AS date, sm.product_qty AS qty, sm.id AS move_id, link_dest.warehouse_id, link.warehouse_id AS other_warehouse_id FROM stock_move sm LEFT JOIN stock_location sl ON sm.location_dest_id = sl.id LEFT JOIN link_location_warehouse link ON link.location_id = sm.location_id LEFT JOIN link_location_warehouse link_dest ON link_dest.location_id = sm.location_dest_id WHERE link_dest.warehouse_id IS NOT NULL AND (link.warehouse_id IS NULL OR link.warehouse_id != link_dest.warehouse_id) AND sm.state :: TEXT <> 'cancel' :: TEXT AND sm.state :: TEXT <> 'done' :: TEXT AND sm.state :: TEXT <> 'draft' :: TEXT UNION ALL SELECT sm.product_id AS product_id, 'out' :: TEXT AS move_type, sm.date_expected AS date, -sm.product_qty AS qty, sm.id AS move_id, link.warehouse_id, link_dest.warehouse_id AS other_warehouse_id FROM stock_move sm LEFT JOIN stock_location sl ON sm.location_id = sl.id LEFT JOIN link_location_warehouse link ON link.location_id = sm.location_id LEFT JOIN link_location_warehouse link_dest ON link_dest.location_id = sm.location_dest_id WHERE link.warehouse_id IS NOT NULL AND (link_dest.warehouse_id IS NULL OR link.warehouse_id != link_dest.warehouse_id) AND sm.state :: TEXT <> 'cancel' :: TEXT AND sm.state :: TEXT <> 'done' :: TEXT AND sm.state :: TEXT <> 'draft' :: TEXT ) foo LEFT JOIN product_product pp ON foo.product_id = pp.id LEFT JOIN product_template pt ON pp.product_tmpl_id = pt.id ) """)
def init(self, cr): drop_view_if_exists(cr, 'retention_lines_list_report') cr.execute(""" create or replace view retention_lines_list_report as ( SELECT ROW_NUMBER() OVER(ORDER BY invoice_id ASC) AS id, id_objeto, invoice_id, nro_obligacion, numero_factura, no_interno_factura, proveedor, rut, partner_id, iva, monto_retencion, tipo, state, fondo_rotatorio, doc_type, date_invoice, tipo_retencion, importe_total, importe_sin_iva, base_impuesto, base_linea, currency_id FROM ( SELECT rl.id as id_objeto, rl.invoice_id, ai.nro_obligacion, ai.number as numero_factura, ai.nro_factura_grp as no_interno_factura, rp.name as proveedor, rp.nro_doc_rupe as rut, rp.id as partner_id, ret.name as iva, -- rl.amount_ret as monto_retencion, CASE WHEN ret.base_compute='ret_tax' THEN rl.base_retax * ret.percent / 100 WHEN ret.base_compute='ret_line_amount' THEN rl.base_ret_line * ret.percent / 100 ELSE 0.0 END as monto_retencion, 'Siif' as tipo_retencion, 'ret_lines' as tipo, ai.state, --fondo rotatorio CASE WHEN t.codigo='P' THEN True ELSE False END as fondo_rotatorio, CASE WHEN ai.doc_type='opi_invoice' THEN 'Factura OPI' WHEN ai.doc_type='obligacion_invoice' THEN 'Obligación' WHEN ai.doc_type='3en1_invoice' THEN 'TresEnUno' ELSE 'Factura' END as doc_type, ai.date_invoice as date_invoice, ai.amount_total as importe_total, ai.amount_untaxed as importe_sin_iva, rl.base_retax as base_impuesto, rl.base_ret_line as base_linea, ai.currency_id FROM account_retention_line_ret rlr left join account_retention_line rl on rlr.account_ret_line_id = rl.id inner join account_invoice ai on ai.id = rl.invoice_id left join res_partner rp on rp.id = ai.partner_id left join tipo_ejecucion_siif t on t.id = ai.siif_tipo_ejecucion left join account_retention ret on ret.id = rlr.retention_id where rl.amount_ret > 0 and ai.state in ('open','paid','forced','intervened','prioritized') --2 Retenciones globales UNION SELECT grl.id as id_objeto, grl.invoice_id, ai.nro_obligacion, ai.number as numero_factura, ai.nro_factura_grp as no_interno_factura, rp.name as proveedor, rp.nro_doc_rupe as rut, rp.id as partner_id, grl.name as iva, grl.amount_ret_pesos as monto_retencion, 'Manual' as tipo_retencion, 'global' as tipo, ai.state, --fondo rotatorio CASE WHEN t.codigo='P' THEN True ELSE False END as fondo_rotatorio, CASE WHEN ai.doc_type='opi_invoice' THEN 'Factura OPI' WHEN ai.doc_type='obligacion_invoice' THEN 'Obligación' WHEN ai.doc_type='3en1_invoice' THEN 'TresEnUno' ELSE 'Factura' END as doc_type, ai.date_invoice as date_invoice, ai.amount_total as importe_total, ai.amount_untaxed as importe_sin_iva, 0.0 as base_impuesto, 0.0 as base_linea, (select c.id from res_currency c where c.name = 'UYU') as currency_id FROM account_global_retention_line grl inner join account_invoice ai on ai.id = grl.invoice_id left join tipo_ejecucion_siif t on t.id = ai.siif_tipo_ejecucion left join res_partner rp on rp.id = ai.partner_id where grl.amount_ret_pesos > 0 and ai.state in ('open','paid','forced','intervened','prioritized') --Retencion IRPF 7% UNION SELECT rlrpf.id as id_objeto, rlrpf.invoice_id, ai.nro_obligacion, ai.number as numero_factura, ai.nro_factura_grp as no_interno_factura, rp.name as proveedor, rp.nro_doc_rupe as rut, rp.id as partner_id, ret.name as iva, rlrpf.amount_ret as monto_retencion, 'IRPF' as tipo_retencion, 'ret_irpf' as tipo, ai.state, --fondo rotatorio CASE WHEN t.codigo='P' THEN True ELSE False END as fondo_rotatorio, CASE WHEN ai.doc_type='opi_invoice' THEN 'Factura OPI' WHEN ai.doc_type='obligacion_invoice' THEN 'Obligación' WHEN ai.doc_type='3en1_invoice' THEN 'TresEnUno' ELSE 'Factura' END as doc_type, ai.date_invoice as date_invoice, ai.amount_total as importe_total, ai.amount_untaxed as importe_sin_iva, --rl.base_amount as base_impuesto, --rl.base_ret_line as base_linea, --ai.currency_id rlrpf.base_amount_pend as base_impuesto, 0.0 as base_linea, ai.currency_id FROM account_retention_line_irpf rlrpf inner join account_invoice ai on ai.id = rlrpf.invoice_id left join tipo_ejecucion_siif t on t.id = ai.siif_tipo_ejecucion left join res_partner rp on rp.id = ai.partner_id left join account_retention ret on ret.id = rlrpf.retention_id where rlrpf.amount_ret > 0 and ai.state in ('open','paid','forced','intervened','prioritized') order by date_invoice desc) as query1)""" )
def action_confirm(self, cr, uid, ids, context={}): drop_view_if_exists(cr, 'report_profit_picking') cr.execute("DELETE FROM stock_card_line") cr.execute(""" create or replace view report_profit_picking as ( select sm.id as id, to_char(sm.date_planned, 'YYYY-MM-DD:HH24:MI:SS') as name, sm.picking_id as picking_id, sp.type as type, sm.purchase_line_id as purchase_line_id, sm.sale_line_id as sale_line_id, sm.product_id as product_id, sm.location_id as location_id, sm.location_dest_id as location_dest_id, sm.id as stk_mov_id, sm.product_qty as picking_qty, sm.state as state from stock_picking sp right join stock_move sm on (sp.id=sm.picking_id) left join product_template pt on (pt.id=sm.product_id) where sm.state='done' and pt.type!='service' order by name ) """) rpp_obj = self.pool.get('report.profit.picking') sc_line_obj = self.pool.get('stock.card.line') rpp_ids = rpp_obj.search(cr, uid, []) for rpp in rpp_obj.browse(cr, uid, rpp_ids): vals = {} vals = { 'stock_card_id': ids[0], 'name': rpp.name or False, 'picking_id': rpp.picking_id and rpp.picking_id.id or False, 'purchase_line_id': rpp.purchase_line_id and rpp.purchase_line_id.id or False, 'sale_line_id': rpp.sale_line_id and rpp.sale_line_id.id or False, 'product_id': rpp.product_id and rpp.product_id.id or False, 'location_id': rpp.location_id and rpp.location_id.id or False, 'location_dest_id': rpp.location_dest_id and rpp.location_dest_id.id or False, 'stk_mov_id': rpp.stk_mov_id and rpp.stk_mov_id.id or False, 'picking_qty': rpp.picking_qty or 0.0, 'type': rpp.type or False, 'state': rpp.state or False, 'aml_cost_id': rpp.aml_cost_id and rpp.aml_cost_id.id or False, 'invoice_line_id': rpp.invoice_line_id and rpp.invoice_line_id.id or False, 'invoice_qty': rpp.invoice_qty or 0.0, 'aml_cost_qty': rpp.aml_cost_qty or 0.0, 'invoice_price_unit': rpp.invoice_price_unit or 0.0, 'aml_cost_price_unit': rpp.aml_cost_price_unit or 0.0, 'invoice_id': rpp.invoice_id and rpp.invoice_id.id or False, 'stock_before': rpp.stock_before or 0.0, 'stock_after': rpp.stock_after or False, 'date_inv': rpp.date_inv or False, 'stock_invoice': rpp.stock_invoice or 0.0, 'aml_inv_id': rpp.aml_inv_id and rpp.aml_inv_id.id or False, 'aml_inv_price_unit': rpp.aml_inv_price_unit or 0.0, 'aml_inv_qty': rpp.aml_inv_qty or 0.0, } sc_line_obj.create(cr, uid, vals) self.find_parent(cr, uid, ids) self.action_done(cr, uid, ids, context) self.action_get_ready(cr, uid, ids, context) print 'LISTO MAN' return True