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