def init(self): drop_view_if_exists(self.env.cr, self._table) self.env.cr.execute(""" create or replace view report_pos_commission as ( select * from pos_agent_commission ) """)
def init(self): drop_view_if_exists(self.env.cr, self._table) self.env.cr.execute(""" create or replace view payslip_report as ( select min(l.id) as id, l.name, p.struct_id, p.state, p.date_from, p.date_to, p.number, p.company_id, p.paid, l.category_id, l.employee_id, sum(l.total) as total, to_char(p.date_from, 'YYYY') as year, to_char(p.date_from, 'MM') as month, to_char(p.date_from, 'YYYY-MM-DD') as day, to_char(p.date_to, 'YYYY') as to_year, to_char(p.date_to, 'MM') as to_month, to_char(p.date_to, 'YYYY-MM-DD') as to_day, 1 AS nbr from hr_payslip as p left join hr_payslip_line as l on (p.id=l.slip_id) where l.employee_id IS NOT NULL group by p.number,l.name,p.date_from,p.date_to,p.state,p.company_id,p.paid, l.employee_id,p.struct_id,l.category_id ) """)
def init(self): drop_view_if_exists(self.env.cr, self._table) self.env.cr.execute(""" create or replace view payment_advice_report as ( select min(l.id) as id, sum(l.bysal) as bysal, p.name, p.state, p.date, p.number, p.company_id, p.bank_id, p.chaque_nos as cheque_nos, p.neft, l.employee_id, l.ifsc_code, l.name as employee_bank_no, to_char(p.date, 'YYYY') as year, to_char(p.date, 'MM') as month, to_char(p.date, 'YYYY-MM-DD') as day, 1 as nbr from hr_payroll_advice as p left join hr_payroll_advice_line as l on (p.id=l.advice_id) where l.employee_id IS NOT NULL group by p.number,p.name,p.date,p.state,p.company_id,p.bank_id,p.chaque_nos,p.neft, l.employee_id,l.advice_id,l.bysal,l.ifsc_code, l.name ) """)
def init(self): #v10 drop_view_if_exists(self._cr, 'pos_combo_pack_report') self.env.cr.execute( """create or replace view pos_combo_pack_report as ( SELECT p.id as id, p.id as product_id, pt.name as name, po.date_order as date, po.state as state, p.pos_type as pos_type, sum(pol.qty) as qty, sum(pol.qty * pol.price_unit * (1 - pol.discount / 100.0)) as sub_total FROM product_product p, product_template pt, pos_order po, pos_order_line pol WHERE p.product_tmpl_id = pt.id AND pol.product_id = p.id AND po.id = pol.order_id AND p.pos_type != 'none' GROUP BY p.id, po.date_order, po.state, p.pos_type, pt.id, pt.name )""")
def init(self): cr = 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): drop_view_if_exists(self._cr, 'line_bus_employee_shedule4bus') self._cr.execute(""" CREATE OR REPLACE VIEW line_bus_employee_shedule4bus AS ( select bus_code as id, company_id, bus_group_id, line_id, bus_model, bus_model_name, bus_code, entry_state from line_bus_employee_shedule group by company_id, line_id, bus_model, bus_model_name, bus_code, bus_group_id, entry_state )""")
def init(self): drop_view_if_exists(self._cr, 'account_invoice_line_sindescuento') self._cr.execute(""" create or replace view account_invoice_line_sindescuento as ( SELECT account_invoice_line.id, account_invoice_line.origin, account_invoice_line.create_date, account_invoice_line.price_unit, account_invoice_line.price_subtotal, account_invoice_line.write_uid, account_invoice_line.currency_id, account_invoice_line.uom_id, account_invoice_line.partner_id, account_invoice_line.create_uid, account_invoice_line.sequence, account_invoice_line.company_id, account_invoice_line.account_analytic_id, account_invoice_line.account_id, account_invoice_line.discount, account_invoice_line.write_date, account_invoice_line.price_subtotal_signed, account_invoice_line.name, account_invoice_line.product_id, account_invoice_line.invoice_id, account_invoice_line.quantity, account_invoice_line.layout_category_sequence, account_invoice_line.layout_category_id, account_invoice_line.purchase_line_id, account_invoice_line.asset_category_id, account_invoice_line.asset_end_date, account_invoice_line.asset_start_date, account_invoice_line.asset_mrr, account_invoice_line.serie FROM account_invoice_line WHERE account_invoice_line.product_id <> 19880 )""")
def write(self, vals): """ If the description change, we must update the action """ if vals.get('sql_name') or vals.get('sql_view'): sql_name = vals.get('sql_name', self[0].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) self.env.cr.execute(sql_query, (ids, )) res = super(jasper_document, self).write(vals) if 'enabled' in vals: if vals['enabled']: for record in self: _logger.debug('Request to create the report action: %s', record) record.make_action() else: for record in self: record.unlink_values() return res
def init(self): cr = 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, l.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, l.user_id ) """)
def init(self): drop_view_if_exists(self._cr, 'sli_historical_employee') self._cr.execute(""" create or replace view sli_historical_employee as( select he.id as id,name_related as nombre, ah.admissiondate as fechaingreso,ah.dischargedate as fechabaja, ah.reason as motivo, ah.settlement as finiquito, ah.retracted as recontratado from hr_employee he left join applicant_historical ah on ah.employee_id = he.id )""")
def init(self): drop_view_if_exists(self._cr, 'report_mrp_production') self._cr.execute(""" create or replace view report_mrp_production as ( SELECT mrp_p."id" AS ID, mrp_production."id" AS production_id, pt."id" AS product_id, mrp_wo."name" AS mrp_workorder_name, wc."id" AS workcenter_id, mrp_p.qty AS qty, mrp_p.empleado_id AS employee_id, pt2."id" AS service_id, mrp_p.price AS price, mrp_p.monto AS monto, mrp_p."id" AS pago_id, merma."name" AS merma, mrp_p."fecha_pago" AS fecha_pago, mrp_p.periodo_id AS periodo_id, stock_move.product_id AS base, mbl.numero_pasadas AS pasadas, mrp_p.qty / ( mrp_production.product_qty / stock_move.product_uom_qty ) AS total_bases, pagoayudante.empleado_id AS ayudante FROM mrp_pago mrp_p INNER JOIN product_template pt2 ON pt2."id" = mrp_p.servicio_id LEFT JOIN mrp_workorder mrp_wo ON mrp_wo."id" = mrp_p.workorder_id LEFT JOIN mrp_production ON mrp_production."id" = mrp_wo.production_id LEFT JOIN mrp_bom ON mrp_bom."id" = mrp_production.bom_id LEFT JOIN product_template pt ON pt."id" = mrp_production.product_id LEFT JOIN mrp_workcenter wc ON wc."id" = mrp_wo.workcenter_id LEFT JOIN mrp_merma merma ON merma."id" = mrp_p.merma_id LEFT JOIN stock_move ON stock_move.raw_material_production_id = mrp_production."id" AND stock_move.product_id IN ( SELECT product_move."id" FROM product_product product_move INNER JOIN product_template template_move ON template_move."id" = product_move.product_tmpl_id WHERE template_move.bases_x_carro IS NOT NULL ) LEFT JOIN mrp_bom_line mbl ON mbl.bom_service_id = mrp_bom."id" AND mbl.operation_id = mrp_wo.operation_id AND mbl.product_id = mrp_p.servicio_id LEFT JOIN mrp_pago pagoayudante ON pagoayudante.workorder_id = mrp_p.workorder_id AND pagoayudante.servicio_id != mrp_p.servicio_id AND pagoayudante.price > 0 AND mrp_p.price > 0 WHERE mrp_p.qty IS NOT NULL ORDER BY mrp_p."id" )""")
def _drop_relation_if_exists(self, cursor): """ Drops middle relation, both if it is a table or a query """ if self._relation_is_actually_a_table(cursor): sql = 'DROP TABLE IF EXISTS {};' sql = sql.format(self.relation) cursor.execute(sql) else: self._drop_rules(cursor) sqltools.drop_view_if_exists(cursor, self.relation)
def init(self): cr = self._cr drop_view_if_exists(cr, 'stock_card_report') cr.execute(""" CREATE OR REPLACE VIEW stock_card_report AS ( SELECT sm.id AS id, sm.branch_id AS branch_id, sm.date AS date, sm.product_id AS product_id, CASE WHEN spt.code = 'outgoing' THEN SUM(sm.product_uom_qty) END AS qty_out, CASE WHEN spt.code = 'incoming' THEN SUM(sm.product_uom_qty) END AS qty_in, CASE WHEN spt.code = 'outgoing' THEN SUM(sm.product_uom_qty * pt.list_price) END AS total_sale, CASE WHEN spt.code = 'incoming' THEN SUM(sm.product_uom_qty * ir.value_float) END AS total_cost, CASE WHEN spt.code = 'outgoing' THEN 0 - SUM(sm.product_uom_qty) ELSE SUM(sm.product_uom_qty) END AS qty_left, CASE WHEN spt.code = 'outgoing' THEN SUM(sm.product_uom_qty * pt.list_price) ELSE 0 - SUM(sm.product_uom_qty * ir.value_float) END AS gain_loss FROM stock_move sm JOIN stock_picking sp ON (sm.picking_id = sp.id) JOIN stock_picking_type spt ON (sm.picking_type_id = spt.id) JOIN product_product pp ON (sm.product_id = pp.id) LEFT JOIN product_template pt ON (pp.product_tmpl_id = pt.id) JOIN ir_property ir ON (split_part(res_id, ',', 2)::integer = pp.id) WHERE sm.state = 'done' AND spt.code IN ('outgoing', 'incoming') AND ir.name = 'standard_price' GROUP BY sm.id, sm.branch_id, sm.date, sm.product_id, spt.code, sm.product_uom_qty, pt.list_price, ir.value_float ) """)
def create(self, vals): """ Dynamicaly declare the wizard for this document """ doc_id = super(jasper_document, self).create(vals) doc_id.make_action() # Check if view exists 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']) self.env.cr.execute(sql_query) return doc_id
def init(self): drop_view_if_exists(self.env.cr, self._table) self.env.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, 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 * (1.0 - coalesce(inv_line.discount, 0.0) / 100.0) * inv_line.quantity else 0 end) as value, sum( case when uom.category_id != puom.category_id then (coalesce(nullif(pp.weight, 0), pt.weight) * inv_line.quantity) else (coalesce(nullif(pp.weight, 0), 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 = coalesce(inv.partner_shipping_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'),intrastat.id,inv.type,pt.intrastat_id, inv_country.code,inv.number, inv.currency_id, inv.company_id )""")
def init(self): drop_view_if_exists(self.env.cr, self._table) self.env.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, 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 uom_uom uom on uom.id=inv_line.uom_id left join uom_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'),intrastat.id,inv.type,pt.intrastat_id, inv_country.code,inv.number, inv.currency_id, inv.company_id )""")
def init(self): drop_view_if_exists(self._cr, 'hr_timesheet_sheet_sheet_account') self._cr.execute("""create view hr_timesheet_sheet_sheet_account as ( select min(l.id) as id, l.account_id as name, s.id as sheet_id, sum(l.unit_amount) as total from account_analytic_line l LEFT JOIN hr_timesheet_sheet_sheet s ON (s.date_to >= l.date AND s.date_from <= l.date AND s.user_id = l.user_id) group by l.account_id, s.id )""")
def init(self): drop_view_if_exists(self._cr, 'report_service') self._cr.execute(""" create or replace view report_service as ( SELECT i.id, i.id AS factura, ( CASE WHEN date_part('month'::text, i.date) = 1::double precision THEN 'Enero'::text WHEN date_part('month'::text, i.date) = 2::double precision THEN 'Febrero'::text WHEN date_part('month'::text, i.date) = 3::double precision THEN 'Marzo'::text WHEN date_part('month'::text, i.date) = 4::double precision THEN 'Abril'::text WHEN date_part('month'::text, i.date) = 5::double precision THEN 'Mayo'::text WHEN date_part('month'::text, i.date) = 6::double precision THEN 'Junio'::text WHEN date_part('month'::text, i.date) = 7::double precision THEN 'Julio'::text WHEN date_part('month'::text, i.date) = 8::double precision THEN 'Agosto'::text WHEN date_part('month'::text, i.date) = 9::double precision THEN 'Septiembre'::text WHEN date_part('month'::text, i.date) = 10::double precision THEN 'Octubre'::text WHEN date_part('month'::text, i.date) = 11::double precision THEN 'Noviembre'::text WHEN date_part('month'::text, i.date) = 12::double precision THEN 'Diciembre'::text ELSE NULL::text END || ' '::text) || date_part('year'::text, i.date) AS date, i.origin AS order,pr.name as service, i.partner_id, e.login AS executive, c.name AS currency, ( SELECT sum(price_subtotal) AS sum FROM account_invoice_line LEFT JOIN product_template pr ON pr.id = product_id WHERE invoice_id = i.id AND pr.type='service' ) AS monto, i.tipo_cambio, (( SELECT sum(price_subtotal) AS sum FROM account_invoice_line LEFT JOIN product_template pr ON pr.id = product_id WHERE invoice_id = i.id AND pr.type='service'))::double precision * i.tipo_cambio AS montomxn, rp.name as vendedor FROM account_invoice i LEFT JOIN res_currency c ON c.id = i.currency_id left join account_invoice_line il on il.invoice_id=i.id LEFT JOIN product_template pr ON pr.id = il.product_id LEFT JOIN res_partner p ON i.partner_id = p.id LEFT JOIN res_users u ON u.id = i.user_id LEFT JOIN res_users e ON e.id = p.user_id left join res_partner rp on rp.id= u.partner_id WHERE i.state::text = 'paid'::text AND date_part('year'::text, i.date) = date_part('year'::text, now()) and pr.type::text = 'service'::text ORDER BY u.login, i.number )""")
def init(self): drop_view_if_exists(self.env.cr, self._table) self.env.cr.execute(""" create or replace view account_entries_report as ( select l.id as id, am.date as date_effective, l.date_maturity as date_maturity, coalesce(am.ref,am.name) as ref, l.name as note, l.move_id as move_id, am.state as move_state, l.full_reconcile_id as reconcile_id, l.partner_id as partner_id, l.product_id as product_id, l.product_uom_id as product_uom_id, am.company_id as company_id, am.journal_id as journal_id, case when aj.type = 'opening' then 'opening' when aj.type = 'closing' then 'shutdown' else 'regular' end as journal_type, l.account_id as account_id, a.group_id as parent_account_id, l.analytic_account_id as analytic_account_id, aaa.parent_id as parent_analytic_id, a.user_type_id as user_type, at.financial_report_id as report_type, a.chart_account_id as chart_account, 1 as nbr, l.quantity as product_quantity, l.currency_id as currency_id, l.amount_currency as amount_currency, l.debit as debit, l.credit as credit, coalesce(l.debit, 0.0) - coalesce(l.credit, 0.0) as balance %s from account_move_line l inner join account_account a on (l.account_id = a.id) inner join account_move am on (am.id=l.move_id) inner join account_journal aj on (aj.id=am.journal_id) left join account_account_type at on (a.user_type_id = at.id) left join account_analytic_account aaa on (l.analytic_account_id = aaa.id) %s where l.id > 0 %s ) """%(self._get_select(),self._get_from(),self._get_where()))
def init(self): drop_view_if_exists(self._cr, 'assigned_boxes') self._cr.execute(""" create or replace view assigned_boxes as ( select sl.id, s.name as pedido, pt.name as producto,sl.product_uom_qty as qty, pu.name unidad, resp.name as vendedor, usm.login as repartidor from sale_order_line sl left join sale_order s on s.id= sl.order_id left join stock_picking sp on sp.origin =s.name left join product_product p on p.id =sl.product_id left join product_template pt on p.product_tmpl_id=pt.id left join product_uom pu on pu.id=sl.product_uom left join res_users us on us.id = s.user_id left join res_partner resp on resp.id= us.partner_id left join res_users usm on usm.id = sp.deliveryman_id group by resp.name,sl.id, s.name, pt.name,sl.product_uom_qty, pu.name,usm.login )""")
def init(self): drop_view_if_exists(self._cr, 'report_comissions') self._cr.execute(""" create or replace view report_comissions as ( SELECT i.id, i.id AS factura, ( CASE WHEN date_part('month'::text, i.date) = 1::double precision THEN 'Enero'::text WHEN date_part('month'::text, i.date) = 2::double precision THEN 'Febrero'::text WHEN date_part('month'::text, i.date) = 3::double precision THEN 'Marzo'::text WHEN date_part('month'::text, i.date) = 4::double precision THEN 'Abril'::text WHEN date_part('month'::text, i.date) = 5::double precision THEN 'Mayo'::text WHEN date_part('month'::text, i.date) = 6::double precision THEN 'Junio'::text WHEN date_part('month'::text, i.date) = 7::double precision THEN 'Julio'::text WHEN date_part('month'::text, i.date) = 8::double precision THEN 'Agosto'::text WHEN date_part('month'::text, i.date) = 9::double precision THEN 'Septiembre'::text WHEN date_part('month'::text, i.date) = 10::double precision THEN 'Octubre'::text WHEN date_part('month'::text, i.date) = 11::double precision THEN 'Noviembre'::text WHEN date_part('month'::text, i.date) = 12::double precision THEN 'Diciembre'::text ELSE NULL::text END || ' '::text) || date_part('year'::text, i.date) AS date, i.origin AS order, i.partner_id, e.login AS executive, c.name AS currency, (select sum(price_subtotal) from account_invoice_line il Left join product_template pr on pr.id = il.product_id where il.invoice_id=i.id and pr.categ_id not in ('94') ) as monto ,i.tipo_cambio ,(select sum(price_subtotal) from account_invoice_line il Left join product_template pr on pr.id = il.product_id where il.invoice_id=i.id and pr.categ_id not in ('94') ) *i.tipo_cambio as montomxn, rp.name as vendedor,i.state,i.number as nofactura, i.date_invoice from account_invoice i left join res_currency c on c.id=i.currency_id left join res_partner p on i.partner_id = p.id left join res_users u on u.id=i.user_id left join res_users e on e.id=p.user_id left join res_partner rp on rp.id= u.partner_id where i.state::text = 'paid'::text or i.state::text = 'open'::text AND date_part('year'::text, i.date) = date_part('year'::text, now()) ORDER BY u.login, i.number )""")
def init(self): cr = 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): drop_view_if_exists(self._cr, 'report_stock_lines_date') self._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): drop_view_if_exists(self._cr, "report_stock_lines_date") self._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): """ Display Deadline, Responsible user, Partner, Department """ drop_view_if_exists(self._cr, 'crm_helpdesk_report') self._cr.execute(""" create or replace view crm_helpdesk_report as ( select min(c.id) as id, c.date as date, c.create_date, c.date_closed, c.state, c.user_id, c.team_id, c.partner_id, c.company_id, c.priority, c.date_deadline, c.tag_ids, c.channel_id, count(*) as nbr_requests, extract('epoch' from (c.date_closed-c.create_date) )/(3600*24) as delay_close, (SELECT count(id) FROM mail_message WHERE model='crm.helpdesk' AND res_id=c.id AND message_type = 'email') AS email, abs(avg( extract('epoch' from (c.date_deadline - c.date_closed) ))/(3600*24)) as delay_expected from crm_helpdesk c where c.active = 'true' group by c.date,c.state,c.user_id,c.team_id,c.priority, c.partner_id,c.company_id,c.date_deadline,c.create_date, c.date,c.date_closed,c.tag_ids,c.channel_id,c.id )""")
def init(self): drop_view_if_exists(self._cr, 'map_line_station_info') self._cr.execute(""" CREATE OR REPLACE VIEW map_line_station_info AS ( select opertation_resources_station_platform.id as id, opertation_resources_station_platform.route_id, opertation_resources_station_platform.station_id, opertation_resources_station_platform.station_type, opertation_resources_station_platform.sequence as squence, opertation_resources_station.latitude, opertation_resources_station.longitude, opertation_resources_station.state, opertation_resources_station.name as station_name, opertation_resources_station_platform.direction as direction from opertation_resources_station_platform left join opertation_resources_station on opertation_resources_station_platform.station_id = opertation_resources_station.id where opertation_resources_station.state = 'inuse' )""")
def init(self): drop_view_if_exists(self._cr, 'quotation_price_line') self._cr.execute(""" CREATE OR REPLACE FUNCTION pricelist_trg() RETURNS trigger AS $BODY$ DECLARE v_categ integer; v_quotation integer; v_provider integer; v_id integer; v_utility double precision; v_costo double precision; v_RECORD RECORD; v_logistic double precision; v_value1 double precision; v_value2 double precision; BEGIN IF TG_OP='INSERT' THEN select provider_id into v_provider from quotation_price where id=new.quotation_id ; select logistic into v_logistic from res_partner where id=v_provider; select id into v_id from res_partner_lines where categ_id =new.categ_id and partner_id=v_provider; FOR v_RECORD IN (select pricelist_id, utility from res_partnerprice_lines where reslines_id=v_id ) LOOP v_utility:=1-(v_RECORD.utility/100); v_value1:=1+ (v_logistic/100); v_costo:=(new.total*v_value1)/v_utility; --raise exception '%','utilidad: '|| v_costo; INSERT INTO quotation_pricelist_line( create_uid, quotationprice_id, write_uid, create_date, write_date, totals, pricelist_id) VALUES (new.create_uid, new.id, new.write_uid, new.create_date, new.write_date, v_costo::double precision, v_RECORD.pricelist_id ); END LOOP; END IF; IF TG_OP='UPDATE' THEN select provider_id into v_provider from quotation_price where id=new.quotation_id ; select logistic into v_logistic from res_partner where id=v_provider; select id into v_id from res_partner_lines where categ_id =new.categ_id and partner_id=v_provider; FOR v_RECORD IN (select pricelist_id, utility from res_partnerprice_lines where reslines_id=v_id ) LOOP v_utility:=1-(v_RECORD.utility/100); v_value1:=1+ (v_logistic/100); v_costo:=(new.total*v_value1)/v_utility; --raise exception '%','utilidad: '|| v_costo; UPDATE quotation_pricelist_line SET totals=v_costo WHERE quotationprice_id=new.id AND pricelist_id=v_RECORD.pricelist_id; END LOOP; END IF; RETURN NEW; END ; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION pricelist_trg() OWNER TO odoo; CREATE TRIGGER pricelist_trg AFTER INSERT OR UPDATE ON quotation_price_line FOR EACH ROW EXECUTE PROCEDURE pricelist_trg(); """)
def init(self): drop_view_if_exists(self._cr, 'dispatch_line_citycode_ref') self._cr.execute(""" create or replace view dispatch_line_citycode_ref as ( select * from ( select route_manage_route_manage."gprs_id" as line_id, route_manage_route_manage."line_name" as line_name, up_start.station_first_name as station_start, up_last.station_last_name as station_end, cast(up_start.direction as char(4)) as direction, hr_department.company_id as company_id, res_partner.zip as company_citycode from route_manage_route_manage left join ( select opertation_resources_station_up.route_id, opertation_resources_station_up.station_id, opertation_resources_station.name as station_first_name, '1' as direction from opertation_resources_station_up left join opertation_resources_station on opertation_resources_station_up.station_id = opertation_resources_station.id where opertation_resources_station_up.station_type = 'first_station' ) as up_start on up_start.route_id = route_manage_route_manage.id left join ( select opertation_resources_station_up.route_id, opertation_resources_station_up.station_id, opertation_resources_station.name as station_last_name, '1' as direction from opertation_resources_station_up left join opertation_resources_station on opertation_resources_station_up.station_id = opertation_resources_station.id where opertation_resources_station_up.station_type = 'last_station' ) as up_last on up_last.route_id = route_manage_route_manage.id left join hr_department on route_manage_route_manage.department_id = hr_department.id left join res_partner on res_partner.company_id = res_partner.id ) as up where up.direction is not null union select * from ( select route_manage_route_manage."gprs_id" as line_id, route_manage_route_manage."line_name" as line_name, down_start.station_first_name as station_start, down_last.station_last_name as station_end, cast(down_start.direction as char(4)) as direction, hr_department.company_id as company_id, res_partner.zip as company_citycode from route_manage_route_manage left join ( select opertation_resources_station_down.route_id, opertation_resources_station_down.station_id, opertation_resources_station.name as station_first_name, '2' as direction from opertation_resources_station_down left join opertation_resources_station on opertation_resources_station_down.station_id = opertation_resources_station.id where opertation_resources_station_down.station_type = 'first_station' ) as down_start on down_start.route_id = route_manage_route_manage.id left join ( select opertation_resources_station_down.route_id, opertation_resources_station_down.station_id, opertation_resources_station.name as station_last_name, '2' as direction from opertation_resources_station_down left join opertation_resources_station on opertation_resources_station_down.station_id = opertation_resources_station.id where opertation_resources_station_down.station_type = 'last_station' ) as down_last on down_last.route_id = route_manage_route_manage.id left join hr_department on route_manage_route_manage.department_id = hr_department.id left join res_partner on res_partner.company_id = res_partner.id ) as down where down.direction is not null )""")
def init(self): drop_view_if_exists(self.env.cr,self._table) self._cr.execute(''' create or replace view registro_compras as ( select ai.id, ai.date_invoice, ai.date_due, substr(ai.x_tipo_comprobante_pago,1,2) tipo_cp, case when position('-' in ai.number) = 0 then '' else substr(ai.number, 1, position('-' in ai.number)-1) end serie, case when position('-' in ai.number) = 0 then ai.number else substr(ai.number, position('-' in ai.number)+1) end number, substr(p.x_tipo_persona,1,2) tipo_persona, substr(p.x_tipo_documento_identidad,1,1) tipo_doc, p.vat, ai.vendor_display_name, ai.amount_untaxed_signed amount_untaxed, 0 exempted, 0 no_vat, 0 isc, (ai.amount_total_company_signed - ai.amount_untaxed_signed) amount_tax, 0 others, ai.amount_total_company_signed amount_total, ai.type, ai.state, ai.date account_date, concat( extract(year from ai.date), right(concat('0',extract(month from ai.date)),2), '00' ) account_period, concat('M', right(concat('00000000',aml.id), 8) ) correlativo, c.name currency_name, case when c.name = 'USD' then 1 else r.rate end rate, ai.move_id cuo, trim( concat_ws('|', concat( extract(year from ai.date), right(concat('0',extract(month from ai.date)),2), '00' ), ai.move_id, concat( 'M', right(concat('00000000',aml.id), 8) ), to_char(ai.date_invoice,'DD/MM/YYYY'), to_char(ai.date_due,'DD/MM/YYYY'), substr(ai.x_tipo_comprobante_pago,1,2), case when position('-' in ai.number) = 0 then '' else substr(ai.number, 1, position('-' in ai.number)-1) end, '', case when position('-' in ai.number) = 0 then ai.number else substr(ai.number, position('-' in ai.number)+1) end, '', substr(p.x_tipo_documento_identidad,1,1), p.vat, ai.vendor_display_name, ai.amount_untaxed_signed, (ai.amount_total_company_signed - ai.amount_untaxed_signed), 0, 0, 0, 0, 0, 0, 0, ai.amount_total_company_signed, c.name, to_char(coalesce(r.rate,1),'9D999'), '', '', '', '', '', coalesce(to_char(ai.x_fecha_emision_detraccion,'DD/MM/YYYY'),''), coalesce(ai.x_nro_constancia_detraccion,''), '', trim(both '0' from coalesce(substr(ai.x_clasificacion_bienes_y_servicios,1,2),'')), '', '', '', '', '', '', 1, '', '', '' ) ) as texto from account_invoice ai inner join (account_invoice_line ail inner join account_move_line aml on (ail.account_id = aml.account_id) ) on (ai.id = ail.invoice_id and ai.move_id = aml.move_id) inner join res_partner p on (ai.partner_id = p.id) inner join res_currency c on (ai.currency_id = c.id) left join res_currency_rate r on (ai.currency_id = r.currency_id and ai.date_invoice = r.name) where ai.type = 'in_invoice' and ai.state not in ('draft') and substr(ai.x_tipo_comprobante_pago,1,2) not in ('91','97','98') UNION ALL select he.id, he.x_fecha_factura, null, substr(he.x_tipo_comprobante_pago,1,2) tipo_cp, case when position('-' in he.reference) = 0 then '' else substr(he.reference, 1, position('-' in he.reference)-1) end serie, case when position('-' in he.reference) = 0 then he.reference else substr(he.reference, position('-' in he.reference)+1) end number, substr(p.x_tipo_persona,1,2) tipo_persona, substr(p.x_tipo_documento_identidad,1,1) tipo_doc, p.vat, p.name, 0 amount_untaxed, 0 exempted, he.untaxed_amount no_vat, 0 isc, 0 amount_tax, 0 others, he.total_amount amount_total, 'expense', he.state, he.date account_date, concat( extract(year from he.date), right(concat('0',extract(month from he.date)),2), '00' ) account_period, concat('M', right(concat('00000000',aml.id), 8) ) correlativo, c.name currency_name, case when c.name = 'USD' then 1 else r.rate end rate, hes.account_move_id cuo, trim( concat_ws('|', concat( extract(year from he.date), right(concat('0',extract(month from he.date)),2), '00' ), hes.account_move_id, concat('M', right(concat('00000000',aml.id), 8) ), to_char(he.x_fecha_factura,'DD/MM/YYYY'), '', substr(he.x_tipo_comprobante_pago,1,2), case when position('-' in he.reference) = 0 then '' else substr(he.reference, 1, position('-' in he.reference)-1) end, '', case when position('-' in he.reference) = 0 then he.reference else substr(he.reference, position('-' in he.reference)+1) end, '', substr(p.x_tipo_documento_identidad,1,1), p.vat, p.name, 0, 0, 0, 0, 0, 0, he.untaxed_amount, 0, 0, he.total_amount, c.name, to_char(coalesce(r.rate,1),'9D999'), '', '', '', '', '', coalesce(to_char(he.x_fecha_constancia_detraccion,'DD/MM/YYYY'),''), coalesce(he.x_nro_constancia_detraccion,''), '', trim(both '0' from coalesce(substr(he.x_clasificacion_bienes_y_servicios,1,2),'')), '', '', '', '', '', '', 1, '', '', '' ) ) as texto from hr_expense he inner join (hr_expense_sheet hes inner join account_move_line aml on (hes.account_move_id = aml.move_id) ) on (he.sheet_id = hes.id and he.account_id = aml.account_id) inner join res_partner p on (he.x_proveedor = p.id) inner join res_currency c on (he.currency_id = c.id) left join res_currency_rate r on (he.currency_id = r.currency_id and he.x_fecha_factura = r.name) where he.state not in ('draft') and substr(he.x_tipo_comprobante_pago_1,1,2) not in ('91','97','98') ) ''')
def init(self): drop_view_if_exists(self.env.cr, self._table) self._cr.execute(''' create or replace view registro_ventas as ( select ai.id, ai.date_invoice, ai.date_due, ai.x_tipo_comprobante_pago tipo_cp, case when position('-' in ai.number) = 0 then '' else substr(ai.number, 1, position('-' in ai.number)-1) end serie, case when position('-' in ai.number) = 0 then ai.number else substr(ai.number, position('-' in ai.number)+1) end number, p.x_tipo_persona tipo_persona, p.x_tipo_documento_identidad tipo_doc, p.vat, p.name customer_name, ai.amount_untaxed_signed amount_untaxed, 0 exempted, 0 no_vat, 0 isc, (ai.amount_total_company_signed - ai.amount_untaxed_signed) amount_tax, 0 others, ai.amount_total_company_signed amount_total, ai.type, ai.state, ai.date account_date, concat(extract(year from ai.date), right(concat('0',extract(month from ai.date)),2), '00' ) account_period, concat('M', right(concat('00000000',aml.id), 8) ) correlativo, c.name currency_name, case when c.name = 'PEN' then 1 else r.rate end rate, ai.move_id cuo, trim( concat_ws('|', concat( extract(year from ai.date), right(concat('0',extract(month from ai.date)),2), '00' ), ai.move_id, concat( 'M', right(concat('00000000',aml.id), 8) ), to_char(ai.date_invoice,'DD/MM/YYYY'), to_char(ai.date_due,'DD/MM/YYYY'), ai.x_tipo_comprobante_pago, case when position('-' in ai.number) = 0 then '' else substr(ai.number, 1, position('-' in ai.number)-1) end, case when position('-' in ai.number) = 0 then ai.number else substr(ai.number, position('-' in ai.number)+1) end, '', p.x_tipo_documento_identidad, p.vat, p.name, 0, ai.amount_untaxed_signed, 0, (ai.amount_total_company_signed - ai.amount_untaxed_signed), 0, 0, 0, 0, 0, 0, 0, ai.amount_total_company_signed, c.name, to_char(coalesce(r.rate,1),'9D999'), '', '', '', '', '', '', '', '1', '' ) ) as ple from account_invoice ai inner join (account_invoice_line ail inner join account_move_line aml on (ail.account_id = aml.account_id) ) on (ai.id = ail.invoice_id and ai.move_id = aml.move_id) inner join res_partner p on (ai.partner_id = p.id) inner join res_currency c on (ai.currency_id = c.id) left join res_currency_rate r on (ai.currency_id = r.currency_id and ai.date_invoice = r.name) where ai.type = 'out_invoice' and ai.state not in ('draft') ) ''')
def init(self): drop_view_if_exists(self._cr, self._table) self._cr.execute("""CREATE or REPLACE VIEW %s as ( %s )""" % (self._table, self._select_sql()))
def init(self): drop_view_if_exists(self._cr, self._table) self._cr.execute("""CREATE or REPLACE VIEW %s as ( %s FROM %s %s )""" % (self._table, self._select(), self._from(), self._group_by()))