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