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 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 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.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._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, '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()))