def init(self, cr):
tools.drop_view_if_exists(cr, 'oa_journal_report')
cr.execute("""
create or replace view oa_journal_report as (
select * from oa_journal
)
""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'report_transaction_pos')
cr.execute("""
create or replace view report_transaction_pos as (
select
min(absl.id) as id,
count(absl.id) as no_trans,
sum(absl.amount) as amount,
sum((100.0-line.discount) * line.price_unit * line.qty / 100.0) as disc,
to_char(date_trunc('day',absl.create_date),'YYYY-MM-DD')::text as date_create,
po.user_id as user_id,
po.sale_journal as journal_id,
abs.journal_id as jl_id,
count(po.invoice_id) as invoice_id,
count(p.id) as product_nb
from
account_bank_statement_line as absl,
account_bank_statement as abs,
product_product as p,
pos_order_line as line,
pos_order as po
where
absl.pos_statement_id = po.id and
line.order_id=po.id and
line.product_id=p.id and
absl.statement_id=abs.id
group by
po.user_id,po.sale_journal, abs.journal_id,
to_char(date_trunc('day',absl.create_date),'YYYY-MM-DD')::text
)
""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'campaign_analysis')
cr.execute("""
create or replace view campaign_analysis as (
select
min(wi.id) as id,
min(wi.res_id) as res_id,
to_char(wi.date::date, 'YYYY') as year,
to_char(wi.date::date, 'MM') as month,
to_char(wi.date::date, 'YYYY-MM-DD') as day,
wi.date::date as date,
s.campaign_id as campaign_id,
wi.activity_id as activity_id,
wi.segment_id as segment_id,
wi.partner_id as partner_id ,
wi.state as state,
sum(act.revenue) as revenue,
count(*) as count
from
marketing_campaign_workitem wi
left join res_partner p on (p.id=wi.partner_id)
left join marketing_campaign_segment s on (s.id=wi.segment_id)
left join marketing_campaign_activity act on (act.id= wi.activity_id)
group by
s.campaign_id,wi.activity_id,wi.segment_id,wi.partner_id,wi.state,
wi.date::date
)
""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'hr_evaluation_report')
cr.execute("""
create or replace view hr_evaluation_report as (
select
min(l.id) as id,
s.create_date as create_date,
s.employee_id,
l.request_id,
s.plan_id,
s.rating,
s.date as deadline,
s.date_close as closed,
count(l.*) as nbr,
s.state,
avg(extract('epoch' from age(s.create_date,CURRENT_DATE)))/(3600*24) as delay_date,
avg(extract('epoch' from age(s.date,CURRENT_DATE)))/(3600*24) as overpass_delay
from
hr_evaluation_interview l
LEFT JOIN
hr_evaluation_evaluation s on (s.id=l.evaluation_id)
GROUP BY
s.create_date,
s.state,
s.employee_id,
s.date,
s.date_close,
l.request_id,
s.rating,
s.plan_id
)
""")
def init(self, cr):
# Note : start_date_hour must be remove when the read_group will allow grouping on the hour of a datetime. Don't forget to change the view !
tools.drop_view_if_exists(cr, 'im_livechat_report_channel')
cr.execute("""
CREATE OR REPLACE VIEW im_livechat_report_channel AS (
SELECT
C.id as id,
C.uuid as uuid,
C.id as channel_id,
C.name as channel_name,
CONCAT(L.name, ' / ', C.id) as technical_name,
C.livechat_channel_id as livechat_channel_id,
C.create_date as start_date,
to_char(date_trunc('hour', C.create_date), 'YYYY-MM-DD HH24:MI:SS') as start_date_hour,
EXTRACT('epoch' FROM (max((SELECT (max(M.create_date)) FROM mail_message M JOIN mail_message_mail_channel_rel R ON (R.mail_message_id = M.id) WHERE R.mail_channel_id = C.id))-C.create_date)) as duration,
count(distinct P.id) as nbr_speaker,
count(distinct M.id) as nbr_message,
MAX(S.partner_id) as partner_id
FROM mail_channel C
JOIN mail_message_mail_channel_rel R ON (C.id = R.mail_channel_id)
JOIN mail_message M ON (M.id = R.mail_message_id)
JOIN mail_channel_partner S ON (S.channel_id = C.id)
JOIN im_livechat_channel L ON (L.id = C.livechat_channel_id)
LEFT JOIN res_partner P ON (M.author_id = P.id)
GROUP BY C.id, C.name, C.livechat_channel_id, L.name, C.create_date, C.uuid
)
""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'payslip_report')
cr.execute("""
create or replace view payslip_report as (
select
min(l.id) as id,
l.name,
p.struct_id,
p.state,
p.date_from,
p.date_to,
p.number,
p.company_id,
p.paid,
l.category_id,
l.employee_id,
sum(l.total) as total,
to_char(p.date_from, 'YYYY') as year,
to_char(p.date_from, 'MM') as month,
to_char(p.date_from, 'YYYY-MM-DD') as day,
to_char(p.date_to, 'YYYY') as to_year,
to_char(p.date_to, 'MM') as to_month,
to_char(p.date_to, 'YYYY-MM-DD') as to_day,
1 AS nbr
from
hr_payslip as p
left join hr_payslip_line as l on (p.id=l.slip_id)
where
l.employee_id IS NOT NULL
group by
p.number,l.name,p.date_from,p.date_to,p.state,p.company_id,p.paid,
l.employee_id,p.struct_id,l.category_id
)
""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'payment_advice_report')
cr.execute("""
create or replace view payment_advice_report as (
select
min(l.id) as id,
sum(l.bysal) as bysal,
p.name,
p.state,
p.date,
p.number,
p.company_id,
p.bank_id,
p.chaque_nos as cheque_nos,
p.neft,
l.employee_id,
l.ifsc_code,
l.name as employee_bank_no,
to_char(p.date, 'YYYY') as year,
to_char(p.date, 'MM') as month,
to_char(p.date, 'YYYY-MM-DD') as day,
1 as nbr
from
hr_payroll_advice as p
left join hr_payroll_advice_line as l on (p.id=l.advice_id)
where
l.employee_id IS NOT NULL
group by
p.number,p.name,p.date,p.state,p.company_id,p.bank_id,p.chaque_nos,p.neft,
l.employee_id,l.advice_id,l.bysal,l.ifsc_code, l.name
)
""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'mrp_workorder')
cr.execute("""
create or replace view mrp_workorder as (
select
date(wl.date_planned) as date,
min(wl.id) as id,
mp.product_id as product_id,
p.product_tmpl_id,
t.categ_id as category_id,
sum(wl.hour) as total_hours,
avg(wl.delay) as delay,
(w.costs_hour*sum(wl.hour)) as total_cost,
wl.production_id as production_id,
wl.workcenter_id as workcenter_id,
sum(wl.cycle) as total_cycles,
count(*) as nbr,
sum(mp.product_qty) as product_qty,
wl.state as state,
mp.user_id,
mp.routing_id,
mp.bom_id
from mrp_production_workcenter_line wl
left join mrp_workcenter w on (w.id = wl.workcenter_id)
left join mrp_production mp on (mp.id = wl.production_id)
left join product_product p on (mp.product_id=p.id)
left join product_template t on (p.product_tmpl_id=t.id)
group by
w.costs_hour, mp.product_id, mp.name, mp.user_id, mp.routing_id, mp.bom_id, wl.state, wl.date_planned, wl.production_id, wl.workcenter_id, p.product_tmpl_id, t.categ_id
)""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'project_issue_report')
cr.execute("""
CREATE OR REPLACE VIEW project_issue_report AS (
SELECT
c.id as id,
c.date_open as opening_date,
c.create_date as create_date,
c.date_last_stage_update as date_last_stage_update,
c.user_id,
c.working_hours_open,
c.working_hours_close,
c.team_id,
c.stage_id,
date(c.date_closed) as date_closed,
c.company_id as company_id,
c.priority as priority,
c.project_id as project_id,
1 as nbr,
c.partner_id,
c.channel,
c.task_id,
c.day_open as delay_open,
c.day_close as delay_close,
(SELECT count(id) FROM mail_message WHERE model='project.issue' AND res_id=c.id) AS email
FROM
project_issue c
LEFT JOIN project_task t on c.task_id = t.id
WHERE c.active= 'true'
)""")
def init(self, cr):
# self._table = sale_report
tools.drop_view_if_exists(cr, self._table)
cr.execute("""CREATE or REPLACE VIEW %s as (
%s
FROM ( %s )
%s
)""" % (self._table, self._select(), self._from(), self._group_by()))
def init(self, cr):
# self._table = hr_timesheet_report
tools.drop_view_if_exists(cr, self._table)
cr.execute("""CREATE or REPLACE VIEW %s as (
%s
%s
%s
%s
)""" % (self._table, self._select(), self._from(), self._where(),
self._group_by()))
def init(self, cr):
tools.drop_view_if_exists(cr, 'report_document_file')
cr.execute("""
create or replace view report_document_file as (
select min(f.id) as id,
count(*) as nbr,
min(EXTRACT(MONTH FROM f.create_date)||'-'||to_char(f.create_date,'Month')) as month,
sum(f.file_size) as file_size
from ir_attachment f
group by EXTRACT(MONTH FROM f.create_date)
)
""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'report_plm_component')
cr.execute("""
CREATE OR REPLACE VIEW report_plm_component AS (
SELECT
(SELECT min(id) FROM product_template) as id,
(SELECT count(*) FROM product_template WHERE state = 'draft') AS count_component_draft,
(SELECT count(*) FROM product_template WHERE state = 'confirmed') AS count_component_confirmed,
(SELECT count(*) FROM product_template WHERE state = 'released') AS count_component_released,
(SELECT count(*) FROM product_template WHERE state = 'undermodify') AS count_component_modified,
(SELECT count(*) FROM product_template WHERE state = 'obsoleted') AS count_component_obsoleted
)
""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'hr_expense8_report')
cr.execute("""
create or replace view hr_expense8_report as (
select
min(l.id) as id,
s.date as date,
s.create_date as create_date,
s.employee_id,
s.journal_id,
s.currency_id,
s.date_confirm as date_confirm,
s.date_valid as date_valid,
s.user_valid as user_id,
s.department_id,
avg(extract('epoch' from age(s.date_valid,s.date)))/(3600*24) as delay_valid,
avg(extract('epoch' from age(s.date_valid,s.date_confirm)))/(3600*24) as delay_confirm,
l.product_id as product_id,
l.analytic_account as analytic_account,
sum(l.unit_quantity * u.factor) as product_qty,
s.company_id as company_id,
sum(l.unit_quantity*l.unit_amount) as price_total,
(sum(l.unit_quantity*l.unit_amount)/sum(case when l.unit_quantity=0 or u.factor=0 then 1 else l.unit_quantity * u.factor end))::decimal(16,2) as price_average,
count(*) as nbr,
(select unit_quantity from hr_expense8_line where id=l.id and product_id is not null) as no_of_products,
(select analytic_account from hr_expense8_line where id=l.id and analytic_account is not null) as no_of_account,
s.state
from hr_expense8_line l
left join hr_expense8_expense s on (s.id=l.expense_id)
left join product_uom u on (u.id=l.uom_id)
group by
s.date,
s.create_date,
s.date_confirm,
s.date_valid,
l.product_id,
l.analytic_account,
s.currency_id,
s.user_valid,
s.department_id,
l.uom_id,
l.id,
s.state,
s.journal_id,
s.company_id,
s.employee_id
)
""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'hr_recruitment_report')
cr.execute("""
create or replace view hr_recruitment_report as (
select
min(s.id) as id,
s.create_date as date_create,
date(s.date_closed) as date_closed,
s.date_last_stage_update as date_last_stage_update,
s.partner_id,
s.company_id,
s.user_id,
s.job_id,
s.type_id,
s.department_id,
s.priority,
s.stage_id,
s.last_stage_id,
s.medium_id,
s.source_id,
sum(salary_proposed) as salary_prop,
(sum(salary_proposed)/count(*)) as salary_prop_avg,
sum(salary_expected) as salary_exp,
(sum(salary_expected)/count(*)) as salary_exp_avg,
extract('epoch' from (s.write_date-s.create_date))/(3600*24) as delay_close,
count(*) as nbr
from hr_applicant s
group by
s.date_open,
s.create_date,
s.write_date,
s.date_closed,
s.date_last_stage_update,
s.partner_id,
s.company_id,
s.user_id,
s.stage_id,
s.last_stage_id,
s.type_id,
s.priority,
s.job_id,
s.department_id,
s.medium_id,
s.source_id
)
""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'report_sales_by_user_pos')
cr.execute("""
create or replace view report_sales_by_user_pos as (
select
min(po.id) as id,
to_char(date_trunc('day',po.date_order),'YYYY-MM-DD')::text as date_order,
po.user_id as user_id,
sum(pol.qty)as qty,
sum((pol.price_unit * pol.qty * (1 - (pol.discount) / 100.0))) as amount
from
pos_order as po,pos_order_line as pol,product_product as pp,product_template as pt
where
pt.id=pp.product_tmpl_id and pp.id=pol.product_id and po.id = pol.order_id
group by
to_char(date_trunc('day',po.date_order),'YYYY-MM-DD')::text,
po.user_id
)
""")
def init(self, cr):
"""Initialize the sql view for the event registration """
tools.drop_view_if_exists(cr, 'report_event_registration')
# TOFIX this request won't select events that have no registration
cr.execute(""" CREATE VIEW report_event_registration AS (
SELECT
e.id::varchar || '/' || coalesce(r.id::varchar,'') AS id,
e.id AS event_id,
e.user_id AS user_id,
r.name AS name_registration,
r.create_date AS create_date,
e.company_id AS company_id,
e.date_begin AS event_date,
count(r.id) AS nbevent,
count(r.event_id) AS nbregistration,
CASE WHEN r.state IN ('draft') THEN count(r.event_id) ELSE 0 END AS draft_state,
CASE WHEN r.state IN ('open','done') THEN count(r.event_id) ELSE 0 END AS confirm_state,
CASE WHEN r.state IN ('cancel') THEN count(r.event_id) ELSE 0 END AS cancel_state,
e.event_type_id AS event_type_id,
e.seats_max AS seats_max,
e.state AS event_state,
r.state AS registration_state
FROM
event_event e
LEFT JOIN event_registration r ON (e.id=r.event_id)
GROUP BY
event_id,
r.id,
registration_state,
event_type_id,
e.id,
e.date_begin,
e.user_id,
event_state,
e.company_id,
e.seats_max,
name_registration
)
""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'hr_holidays_remaining_leaves_user')
cr.execute("""
CREATE or REPLACE view hr_holidays_remaining_leaves_user as (
SELECT
min(hrs.id) as id,
rr.name as name,
sum(hrs.number_of_days) as no_of_leaves,
rr.user_id as user_id,
hhs.name as leave_type
FROM
hr_holidays as hrs, hr_employee as hre,
resource_resource as rr,hr_holidays_status as hhs
WHERE
hrs.employee_id = hre.id and
hre.resource_id = rr.id and
hhs.id = hrs.holiday_status_id
GROUP BY
rr.name,rr.user_id,hhs.name
)
""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'report_document_user')
cr.execute("""
CREATE OR REPLACE VIEW report_document_user as (
SELECT
min(f.id) as id,
to_char(f.create_date, 'YYYY') as name,
to_char(f.create_date, 'MM') as month,
f.user_id as user_id,
count(*) as nbr,
d.name as directory,
f.datas_fname as datas_fname,
f.create_date as create_date,
f.file_size as file_size,
min(d.type) as type,
f.write_date as change_date
FROM ir_attachment f
left join document_directory d on (f.parent_id=d.id and d.name<>'')
group by to_char(f.create_date, 'YYYY'), to_char(f.create_date, 'MM'),d.name,f.parent_id,d.type,f.create_date,f.user_id,f.file_size,d.type,f.write_date,f.datas_fname
)
""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'report_pos_order')
cr.execute("""
create or replace view report_pos_order as (
select
min(l.id) as id,
count(*) as nbr,
s.date_order as date,
sum(l.qty * u.factor) as product_qty,
sum(l.qty * l.price_unit) as price_sub_total,
sum((l.qty * l.price_unit) * (100 - l.discount) / 100) 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,
ps.config_id,
pt.pos_categ_id,
pc.stock_location_id,
s.pricelist_id,
s.invoice_id IS NOT NULL AS invoiced
from pos_order_line as l
left join pos_order s on (s.id=l.order_id)
left join product_product p on (l.product_id=p.id)
left join product_template pt on (p.product_tmpl_id=pt.id)
left join product_uom u on (u.id=pt.uom_id)
left join pos_session ps on (s.session_id=ps.id)
left join pos_config pc on (ps.config_id=pc.id)
group by
s.date_order, s.partner_id,s.state, pt.categ_id,
s.user_id,s.location_id,s.company_id,s.sale_journal,s.pricelist_id,s.invoice_id,l.product_id,s.create_date,pt.categ_id,pt.pos_categ_id,p.product_tmpl_id,ps.config_id,pc.stock_location_id
having
sum(l.qty * u.factor) != 0)""")
def init(self, cr):
# Note : start_date_hour must be remove when the read_group will allow grouping on the hour of a datetime. Don't forget to change the view !
tools.drop_view_if_exists(cr, 'im_livechat_report_operator')
cr.execute("""
CREATE OR REPLACE VIEW im_livechat_report_operator AS (
SELECT
row_number() OVER () AS id,
P.id as partner_id,
L.id as livechat_channel_id,
count(C.id) as nbr_channel,
C.id as channel_id,
C.create_date as start_date,
EXTRACT('epoch' FROM (max((SELECT (max(M.create_date)) FROM mail_message M JOIN mail_message_mail_channel_rel R ON (R.mail_message_id = M.id) WHERE R.mail_channel_id = C.id))-C.create_date)) as duration,
EXTRACT('epoch' from ((SELECT min(M.create_date) FROM mail_message M, mail_message_mail_channel_rel R WHERE M.author_id=P.id AND R.mail_channel_id = C.id AND R.mail_message_id = M.id)-(SELECT min(M.create_date) FROM mail_message M, mail_message_mail_channel_rel R WHERE M.author_id IS NULL AND R.mail_channel_id = C.id AND R.mail_message_id = M.id))) as time_to_answer
FROM im_livechat_channel_im_user O
JOIN res_users U ON (O.user_id = U.id)
JOIN res_partner P ON (U.partner_id = P.id)
LEFT JOIN im_livechat_channel L ON (L.id = O.channel_id)
LEFT JOIN mail_channel C ON (C.livechat_channel_id = L.id)
GROUP BY P.id, L.id, C.id, C.create_date
)
""")
def init(self, cr):
""" Event Question main report """
tools.drop_view_if_exists(cr, 'event_question_report')
cr.execute(""" CREATE VIEW event_question_report AS (
SELECT
att_answer.id as id,
att_answer.event_registration_id as attendee_id,
answer.question_id as question_id,
answer.id as answer_id,
question.event_id as event_id
FROM
event_registration_answer as att_answer
LEFT JOIN
event_answer as answer ON answer.id = att_answer.event_answer_id
LEFT JOIN
event_question as question ON question.id = answer.question_id
GROUP BY
attendee_id,
event_id,
question_id,
answer_id,
att_answer.id
)""")
def init(self, cr):
'''Create the view'''
tools.drop_view_if_exists(cr, 'report_membership')
cr.execute("""
CREATE OR REPLACE VIEW report_membership AS (
SELECT
MIN(id) AS id,
partner_id,
count(membership_id) as quantity,
user_id,
membership_state,
associate_member_id,
membership_amount,
date_to,
start_date,
COUNT(num_waiting) AS num_waiting,
COUNT(num_invoiced) AS num_invoiced,
COUNT(num_paid) AS num_paid,
SUM(tot_pending) AS tot_pending,
SUM(tot_earned) AS tot_earned,
membership_id,
company_id
FROM
(SELECT
MIN(p.id) AS id,
p.id AS partner_id,
p.user_id AS user_id,
p.membership_state AS membership_state,
p.associate_member AS associate_member_id,
p.membership_amount AS membership_amount,
p.membership_stop AS date_to,
p.membership_start AS start_date,
CASE WHEN ml.state = 'waiting' THEN ml.id END AS num_waiting,
CASE WHEN ml.state = 'invoiced' THEN ml.id END AS num_invoiced,
CASE WHEN ml.state = 'paid' THEN ml.id END AS num_paid,
CASE WHEN ml.state IN ('waiting', 'invoiced') THEN SUM(il.price_subtotal) ELSE 0 END AS tot_pending,
CASE WHEN ml.state = 'paid' OR p.membership_state = 'old' THEN SUM(il.price_subtotal) ELSE 0 END AS tot_earned,
ml.membership_id AS membership_id,
p.company_id AS company_id
FROM res_partner p
LEFT JOIN membership_membership_line ml ON (ml.partner = p.id)
LEFT JOIN account_invoice_line il ON (ml.account_invoice_line = il.id)
LEFT JOIN account_invoice ai ON (il.invoice_id = ai.id)
WHERE p.membership_state != 'none' and p.active = 'true'
GROUP BY
p.id,
p.user_id,
p.membership_state,
p.associate_member,
p.membership_amount,
p.membership_start,
ml.membership_id,
p.company_id,
ml.state,
ml.id
) AS foo
GROUP BY
start_date,
date_to,
partner_id,
user_id,
membership_id,
company_id,
membership_state,
associate_member_id,
membership_amount
)""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'report_stock_forecast')
cr.execute("""CREATE or REPLACE VIEW report_stock_forecast AS (SELECT
MIN(id) as id,
product_id as product_id,
date as date,
sum(product_qty) AS quantity,
sum(sum(product_qty)) OVER (PARTITION BY product_id ORDER BY date) AS cumulative_quantity
FROM
(SELECT
MIN(id) as id,
MAIN.product_id as product_id,
SUB.date as date,
CASE WHEN MAIN.date = SUB.date THEN sum(MAIN.product_qty) ELSE 0 END as product_qty
FROM
(SELECT
MIN(sq.id) as id,
sq.product_id,
date_trunc('week', to_date(to_char(CURRENT_DATE, 'YYYY/MM/DD'), 'YYYY/MM/DD')) as date,
SUM(sq.qty) AS product_qty
FROM
stock_quant as sq
LEFT JOIN
product_product ON product_product.id = sq.product_id
LEFT JOIN
stock_location location_id ON sq.location_id = location_id.id
WHERE
location_id.usage = 'internal'
GROUP BY date, sq.product_id
UNION ALL
SELECT
MIN(-sm.id) as id,
sm.product_id,
CASE WHEN sm.date_expected > CURRENT_DATE
THEN date_trunc('week', to_date(to_char(sm.date_expected, 'YYYY/MM/DD'), 'YYYY/MM/DD'))
ELSE date_trunc('week', to_date(to_char(CURRENT_DATE, 'YYYY/MM/DD'), 'YYYY/MM/DD')) END
AS date,
SUM(sm.product_qty) AS product_qty
FROM
stock_move as sm
LEFT JOIN
product_product ON product_product.id = sm.product_id
LEFT JOIN
stock_location dest_location ON sm.location_dest_id = dest_location.id
LEFT JOIN
stock_location source_location ON sm.location_id = source_location.id
WHERE
sm.state IN ('confirmed','assigned','waiting') and
source_location.usage != 'internal' and dest_location.usage = 'internal'
GROUP BY sm.date_expected,sm.product_id
UNION ALL
SELECT
MIN(-sm.id) as id,
sm.product_id,
CASE WHEN sm.date_expected > CURRENT_DATE
THEN date_trunc('week', to_date(to_char(sm.date_expected, 'YYYY/MM/DD'), 'YYYY/MM/DD'))
ELSE date_trunc('week', to_date(to_char(CURRENT_DATE, 'YYYY/MM/DD'), 'YYYY/MM/DD')) END
AS date,
SUM(-(sm.product_qty)) AS product_qty
FROM
stock_move as sm
LEFT JOIN
product_product ON product_product.id = sm.product_id
LEFT JOIN
stock_location source_location ON sm.location_id = source_location.id
LEFT JOIN
stock_location dest_location ON sm.location_dest_id = dest_location.id
WHERE
sm.state IN ('confirmed','assigned','waiting') and
source_location.usage = 'internal' and dest_location.usage != 'internal'
GROUP BY sm.date_expected,sm.product_id)
as MAIN
LEFT JOIN
(SELECT DISTINCT date
FROM
(
SELECT date_trunc('week', CURRENT_DATE) AS DATE
UNION ALL
SELECT date_trunc('week', to_date(to_char(sm.date_expected, 'YYYY/MM/DD'), 'YYYY/MM/DD')) AS date
FROM stock_move sm
LEFT JOIN
stock_location source_location ON sm.location_id = source_location.id
LEFT JOIN
stock_location dest_location ON sm.location_dest_id = dest_location.id
WHERE
sm.state IN ('confirmed','assigned','waiting') and sm.date_expected > CURRENT_DATE and
((dest_location.usage = 'internal' AND source_location.usage != 'internal')
or (source_location.usage = 'internal' AND dest_location.usage != 'internal'))) AS DATE_SEARCH)
SUB ON (SUB.date IS NOT NULL)
GROUP BY MAIN.product_id,SUB.date, MAIN.date
) AS FINAL
GROUP BY product_id,date)""")
def init(self, cr):
tools.drop_view_if_exists(cr, 'stock_history')
cr.execute("""
CREATE OR REPLACE VIEW stock_history AS (
SELECT MIN(id) as id,
move_id,
location_id,
company_id,
product_id,
product_categ_id,
product_template_id,
SUM(quantity) as quantity,
date,
price_unit_on_quant,
source,
serial_number
FROM
((SELECT
stock_move.id AS id,
stock_move.id AS move_id,
dest_location.id AS location_id,
dest_location.company_id AS company_id,
stock_move.product_id AS product_id,
product_template.id AS product_template_id,
product_template.categ_id AS product_categ_id,
quant.qty AS quantity,
stock_move.date AS date,
quant.cost as price_unit_on_quant,
stock_move.origin AS source,
stock_production_lot.name AS serial_number
FROM
stock_quant as quant
JOIN
stock_quant_move_rel ON stock_quant_move_rel.quant_id = quant.id
JOIN
stock_move ON stock_move.id = stock_quant_move_rel.move_id
LEFT JOIN
stock_production_lot ON stock_production_lot.id = quant.lot_id
JOIN
stock_location dest_location ON stock_move.location_dest_id = dest_location.id
JOIN
stock_location source_location ON stock_move.location_id = source_location.id
JOIN
product_product ON product_product.id = stock_move.product_id
JOIN
product_template ON product_template.id = product_product.product_tmpl_id
WHERE quant.qty>0 AND stock_move.state = 'done' AND dest_location.usage in ('internal', 'transit')
AND (
(source_location.company_id is null and dest_location.company_id is not null) or
(source_location.company_id is not null and dest_location.company_id is null) or
source_location.company_id != dest_location.company_id or
source_location.usage not in ('internal', 'transit'))
) UNION ALL
(SELECT
(-1) * stock_move.id AS id,
stock_move.id AS move_id,
source_location.id AS location_id,
source_location.company_id AS company_id,
stock_move.product_id AS product_id,
product_template.id AS product_template_id,
product_template.categ_id AS product_categ_id,
- quant.qty AS quantity,
stock_move.date AS date,
quant.cost as price_unit_on_quant,
stock_move.origin AS source,
stock_production_lot.name AS serial_number
FROM
stock_quant as quant
JOIN
stock_quant_move_rel ON stock_quant_move_rel.quant_id = quant.id
JOIN
stock_move ON stock_move.id = stock_quant_move_rel.move_id
LEFT JOIN
stock_production_lot ON stock_production_lot.id = quant.lot_id
JOIN
stock_location source_location ON stock_move.location_id = source_location.id
JOIN
stock_location dest_location ON stock_move.location_dest_id = dest_location.id
JOIN
product_product ON product_product.id = stock_move.product_id
JOIN
product_template ON product_template.id = product_product.product_tmpl_id
WHERE quant.qty>0 AND stock_move.state = 'done' AND source_location.usage in ('internal', 'transit')
AND (
(dest_location.company_id is null and source_location.company_id is not null) or
(dest_location.company_id is not null and source_location.company_id is null) or
dest_location.company_id != source_location.company_id or
dest_location.usage not in ('internal', 'transit'))
))
AS foo
GROUP BY move_id, location_id, company_id, product_id, product_categ_id, date, price_unit_on_quant, source, product_template_id, serial_number
)""")
