def _query(self, with_clause='', fields={}, groupby='', from_clause=''):
with_ = ("WITH %s" % with_clause) if with_clause else ""
select_ = """
min(v.id) as id,
count(v.id) as nombre_voyage,
-- chiffre d'affaire
COALESCE(sum(v."total_livre"),0) as total_livre,
COALESCE(sum(v."total_Eloignement_livre"),0) as total_eloignement_livre,
-- total les charges
COALESCE(sum(v."total_comission_Livreur_livre"),0) as total_comission_livreur_livre,
COALESCE(sum(p."salaire"),0) as salaire_emplyees, -- fix
COALESCE(sum(c.total),0) as total_charges,
(COALESCE(sum(v."total_livre"),0)+COALESCE(sum(v."total_Eloignement_livre"),0)-COALESCE(sum(v."total_comission_Livreur_livre"),0)-COALESCE(sum(p."salaire"),0)-COALESCE(sum(c.total),0)) as rondemnt,
(COALESCE(sum(v."total_comission_Livreur_livre"),0)+COALESCE(sum(p."salaire"),0)+COALESCE(sum(c.total),0))/COALESCE(sum(v."Qte_livre"),1) as cout_caisse,
COALESCE(sum(v."Qte_livre"),0) as qte_livre,
DATE_TRUNC('month',v.date_depart) as date """
for field in fields.values():
select_ += field
from_ = """
gvoyage_voyage v
full join gvoyage_charge_variable c on (DATE_TRUNC('month',date) = DATE_TRUNC('month',v.date_depart))
full join gvoyage_attendance p on (DATE_TRUNC('month',work_day) = DATE_TRUNC('month',v.date_depart))
group by DATE_TRUNC('month',v.date_depart)
%s
""" % from_clause
return '%s (SELECT %s FROM %s)' % (with_, select_, from_)
def _query(self, with_clause='', fields={}, orderby='', from_clause=''):
with_ = ("WITH %s" % with_clause) if with_clause else ""
select_ = """
ROW_NUMBER () OVER (ORDER BY model_id) as id,
model_id,
name,
odoo_model,
website_id,
product_public_category_id,
description_short,
description_full,
website_published,
start_date,
end_date
"""
for field in fields.values():
select_ += field
from_ = """
(
select po.id as model_id,
po.name as name,
po.description_short as description_short,
po.description_full as description_full,
po.website_id as website_id,
po.category_id as product_public_category_id,
'product.offer' AS odoo_model,
po.website_published as website_published,
po.start_date as start_date,
po.end_date as end_date
from product_offer po
JOIN product_public_category ppc ON po.category_id = ppc.id
where po.start_date <= NOW() and (po.end_date >= NOW() or po.end_date is NULL)
AND ppc.website_published = True
AND (ppc.parent_id IS NULL OR ppc.parent_id IN (SELECT id from product_public_category WHERE website_published = True))
UNION
select pt.id as model_id,
pt.name as name,
pt.description_short as description_short,
pt.description_full as description_full,
pt.website_id as website_id,
ppcpt.product_public_category_id as product_public_category_id,
'product.template' AS odoo_model,
pt.is_published AS website_published,
NULL as start_date,
NULL as end_date
from product_template pt
LEFT JOIN product_public_category_product_template_rel ppcpt ON pt.id = ppcpt.product_template_id
LEFT JOIN product_style_product_template_rel pspt ON pt.id = pspt.product_template_id
LEFT JOIN product_style ps ON pspt.product_style_id = ps.id
WHERE ps.html_class = 'oe_ribbon_promo'
and pt.active = True
) x
"""
return '%s (SELECT %s FROM %s)' % (with_, select_, from_)
def _query(self, with_clause='', fields={}, groupby='', from_clause=''):
with_ = ("WITH %s" % with_clause) if with_clause else ""
select_ = """
aml.id as id,
aml.partner_id as partner_id,
aml.invoice_id as invoice_id,
aml.amount_residual as residual,
aml.date_maturity as date_maturity,
ai.date_invoice as date_invoice,
ai.user_id as user_id
"""
for field in fields.values():
select_ += field
from_ = """
account_move_line aml
INNER JOIN account_account aa ON aml.account_id = aa.id
LEFT JOIN account_invoice ai ON ai.id = aml.invoice_id
%s
""" % from_clause
groupby_ = " "
return "%s SELECT %s FROM %s WHERE aa.internal_type in ('receivable') and aml.reconciled = False" % (
with_, select_, from_)
def _query(self, with_clause='', fields={}, groupby='', from_clause=''):
with_ = ("WITH %s" % with_clause) if with_clause else ""
select_ = """
att.id as id,
att.salaire as total_pointage,
COALESCE(att.work_day,avance.date_avance) as date,
COALESCE(avance.montant_avance,0) as total_avance ,
att.employee_id as employee_id ,
COALESCE(att.salaire,0)+COALESCE(primes.prime,0)-COALESCE(avance.montant_avance,0) as total_payer,
COALESCE(primes.prime,0) as total_primes ,
primes.note_prime as note_prime """
for field in fields.values():
select_ += field
from_ = """
gvoyage_attendance att
FULL join (select total as prime ,name as note_prime ,date as date_prime ,employee_id as employee_id from gvoyage_prime ) as primes on (primes.employee_id =att.employee_id and att.work_day =primes.date_prime)
FULL join (select montant as montant_avance ,date as date_avance ,employee_id as employee_id from gvoyage_avance) as avance on (avance.employee_id = att.employee_id and att.work_day = avance.date_avance)
%s
""" % from_clause
return '%s (SELECT %s FROM %s where att.state_val=1)' % (
with_, select_, from_)
def _query(self, with_clause='', fields={}, groupby='', from_clause=''):
with_ = ("WITH %s" % with_clause) if with_clause else ""
select_ = """
gg2_rpt080.soid AS id,
gg2_rpt080.sostate AS sostate,
gg2_rpt080.soname AS soname,
gg2_rpt080.orderdate AS orderdate,
gg2_rpt080.whotoinvoice AS whotoinvoice
"""
for field in fields.values():
select_ += field
from_ = """
gg2_rpt080
%s
""" % from_clause
groupby_ = """
gg2_rpt080.soid,
gg2_rpt080.sostate,
gg2_rpt080.soname,
gg2_rpt080.orderdate,
gg2_rpt080.whotoinvoice
%s
""" % groupby
order_ = """
gg2_rpt080.soid DESC
"""
return '%s (SELECT %s FROM %s GROUP BY %s ORDER BY %s)' % (
with_, select_, from_, groupby_, order_)
def _query(self, with_clause='', fields={}, groupby='', from_clause=''):
with_ = ("WITH %s" % with_clause) if with_clause else ""
select_ = """
min(l.id) as id,
l.product_id as product_id,
sum(l.qty) as qty,
sum(l.price_subtotal / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) as price_subtotal,
count(*) as nbr,
s.name as name,
s.date_order as date,
s.state as state,
s.partner_id as partner_id,
s.company_id as company_id,
extract(epoch from avg(date_trunc('day',s.date_order)-date_trunc('day',s.create_date)))/(24*60*60)::decimal(16,2) as delay,
t.categ_id as categ_id,
s.pricelist_id as pricelist_id,
p.product_tmpl_id,
l.discount as discount,
sum((l.price_unit * l.discount / 100.0 / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END)) as discount_amount,
s.id as order_id
"""
for field in fields.values():
select_ += field
from_ = """
pos_order_line l
join pos_order s on (l.order_id=s.id)
left join product_product p on (l.product_id=p.id)
left join product_template t on (p.product_tmpl_id=t.id)
left join product_pricelist pp on (s.pricelist_id = pp.id)
%s
""" % from_clause
groupby_ = """
l.product_id,
l.order_id,
t.categ_id,
s.name,
s.date_order,
s.partner_id,
s.state,
s.company_id,
s.pricelist_id,
p.product_tmpl_id,
l.discount,
s.id %s
""" % (groupby)
return '%s (SELECT %s FROM %s WHERE l.product_id IS NOT NULL GROUP BY %s)' % (with_, select_, from_, groupby_)
def _query(self, with_clause='', fields={}, groupby='', from_clause=''):
with_ = ("WITH %s" % with_clause) if with_clause else ""
select_ = """
min(j.id) as id,
j.name as name,
j.create_date as date,
j.partner_id as partner_id,
CONCAT(TO_CHAR(me.rating, '9999.'), ' ', me.rating_unit) as rating,
j.urgency as urgency,
j.deadline_date as deadline,
j.stage_id as stage_id,
count(wo.id) FILTER (WHERE wc.name = 'Disassembly' AND state in ('pending','ready','progress')) AS disassembly,
count(wo.id) FILTER (WHERE wc.name = 'Bake Oven' AND state in ('pending','ready','progress')) AS bake_oven,
count(wo.id) FILTER (WHERE wc.name = 'Utilities' AND state in ('pending','ready','progress')) AS utilities,
count(wo.id) FILTER (WHERE wc.name = 'Machine Shop' AND state in ('pending','ready','progress')) AS machine,
count(wo.id) FILTER (WHERE wc.name = 'Winding' AND state in ('pending','ready','progress')) AS winding,
count(wo.id) FILTER (WHERE wc.name = 'Balancing' AND state in ('pending','ready','progress')) AS balancing,
count(wo.id) FILTER (WHERE wc.name = 'Electrical Testing' AND state in ('pending','ready','progress')) AS electrical,
count(wo.id) FILTER (WHERE wc.name = 'Paint' AND state in ('pending','ready','progress')) AS paint,
count(wo.id) FILTER (WHERE wc.name = 'Assembly' AND state in ('pending','ready','progress')) AS assembly,
count(wo.id)FILTER (WHERE state in ('pending','ready','progress')) AS total
"""
for field in fields.values():
select_ += field
from_ = """
ssi_jobs j
left join maintenance_equipment me on (me.id=j.equipment_id)
left join mrp_workorder wo on (j.id=wo.ssi_job_id)
left join mrp_workcenter wc on (wo.workcenter_id=wc.id)
%s
""" % from_clause
groupby_ = """
j.id,
j.name,
date,
j.partner_id,
rating,
rating_unit,
urgency,
deadline,
stage_id %s
""" % (groupby)
return "%s (SELECT %s FROM %s WHERE wo.workcenter_id IN (1,2,3,4,6,7,8,9,15) GROUP BY %s)" % (
with_, select_, from_, groupby_)
def _query(self, with_clause='', fields={}, groupby='', from_clause=''):
with_ = ("WITH %s" % with_clause) if with_clause else ""
select_ = """
min(l.id) as id,
l.product_id as product_id,
count(*) as nbr,
s.name as name,
s.date as date,
s.partner_id as partner_id,
s.id as return_id,
s.sale_id as sale_id,
s.user_id as user_id,
s.reason_id as reason_id,
s.ticket_id as ticket_id,
s.delivery_id as delivery_id,
s.with_refund as with_refund,
s.amount_total as amount_total,
s.state as state
"""
for field in fields.values():
select_ += field
from_ = """
return_order_line l
join return_order s on (l.return_id=s.id)
left join product_product p on (l.product_id=p.id)
left join product_template t on (p.product_tmpl_id=t.id)
left join uom_uom u on (u.id=l.uom_id)
%s
""" % from_clause
groupby_ = """
l.product_id,
l.return_id,
l.uom_id,
s.name,
s.date,
s.sale_id,
s.date,
s.partner_id,
s.user_id,
s.id %s
""" % (groupby)
return '%s (SELECT %s FROM %s WHERE l.product_id IS NOT NULL GROUP BY %s)' % (with_, select_, from_, groupby_)
def _select_sale(self, fields=None):
if not fields:
fields = {}
select_ = """
coalesce(min(l.id), -s.id) as id,
l.product_id as product_id,
t.uom_id as product_uom,
CASE WHEN l.product_id IS NOT NULL THEN sum(l.product_uom_qty / u.factor * u2.factor) ELSE 0 END as product_uom_qty,
CASE WHEN l.product_id IS NOT NULL THEN sum(l.qty_delivered / u.factor * u2.factor) ELSE 0 END as qty_delivered,
CASE WHEN l.product_id IS NOT NULL THEN SUM((l.product_uom_qty - l.qty_delivered) / u.factor * u2.factor) ELSE 0 END as qty_to_deliver,
CASE WHEN l.product_id IS NOT NULL THEN sum(l.qty_invoiced / u.factor * u2.factor) ELSE 0 END as qty_invoiced,
CASE WHEN l.product_id IS NOT NULL THEN sum(l.qty_to_invoice / u.factor * u2.factor) ELSE 0 END as qty_to_invoice,
CASE WHEN l.product_id IS NOT NULL THEN sum(l.price_total / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) ELSE 0 END as price_total,
CASE WHEN l.product_id IS NOT NULL THEN sum(l.price_subtotal / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) ELSE 0 END as price_subtotal,
CASE WHEN l.product_id IS NOT NULL THEN sum(l.untaxed_amount_to_invoice / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) ELSE 0 END as untaxed_amount_to_invoice,
CASE WHEN l.product_id IS NOT NULL THEN sum(l.untaxed_amount_invoiced / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) ELSE 0 END as untaxed_amount_invoiced,
count(*) as nbr,
s.name as name,
s.date_order as date,
s.state as state,
s.partner_id as partner_id,
s.user_id as user_id,
s.company_id as company_id,
s.campaign_id as campaign_id,
s.medium_id as medium_id,
s.source_id as source_id,
extract(epoch from avg(date_trunc('day',s.date_order)-date_trunc('day',s.create_date)))/(24*60*60)::decimal(16,2) as delay,
t.categ_id as categ_id,
s.pricelist_id as pricelist_id,
s.analytic_account_id as analytic_account_id,
s.team_id as team_id,
p.product_tmpl_id,
partner.country_id as country_id,
partner.industry_id as industry_id,
partner.commercial_partner_id as commercial_partner_id,
CASE WHEN l.product_id IS NOT NULL THEN sum(p.weight * l.product_uom_qty / u.factor * u2.factor) ELSE 0 END as weight,
CASE WHEN l.product_id IS NOT NULL THEN sum(p.volume * l.product_uom_qty / u.factor * u2.factor) ELSE 0 END as volume,
l.discount as discount,
CASE WHEN l.product_id IS NOT NULL THEN sum((l.price_unit * l.product_uom_qty * l.discount / 100.0 / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END))ELSE 0 END as discount_amount,
s.id as order_id
"""
for field in fields.values():
select_ += field
return select_
def _query(self, with_clause='', fields={}, groupby='', from_clause=''):
with_ = ("WITH %s" % with_clause) if with_clause else ""
select_ = """
pt.name as producto,
am.name as motivo,
ss.date_expected as fecha,
concat(sl2.name,'/',sl.name) as ubicacion,
pt.taste_product as sabor,
pt.clase_prod as clase,
pt.presentation_prod as presentacion,
pt.brand_product as marca,
ss.id
"""
for field in fields.values():
select_ += field
from_ = """
stock_scrap ss
left join product_product pp on (pp.id=ss.product_id)
left join product_template pt on (pt.id = pp.product_tmpl_id)
left join add_motivo am on (am.id = motivo)
left join stock_location sl on sl.id = ss.location_id
left join stock_location sl2 on sl2.id = sl.location_id
%s
""" % from_clause
groupby_ = """
pt.name,
am.name,
pt.taste_product,
pt.clase_prod,
pt.presentation_prod,
pt.brand_product,
sl2.name,
sl.name,
ss.id
%s
""" % (groupby)
return '%s (SELECT %s FROM %s GROUP BY %s)' % (with_, select_, from_,
groupby_)
def _query(self, with_clause='', fields={}, groupby='', from_clause=''):
with_ = ("WITH %s" % with_clause) if with_clause else ""
select_ = """
min(l.id) as id,
w.name as name,
w.produced_date as produced_date,
w.worker_id as worker_id,
l.production_id as production_id,
sum(l.produced_quantity) as produced_quantity,
l.product_id as product_id,
l.product_uom as product_uom,
sum(l.piece_credit_subtotal) as piece_credit_total,
l.over_work_char as over_work_char,
l.note as note
"""
for field in fields.values():
select_ += field
from_ = """
piece_work_line l
join piece_work w on (l.work_id=w.id)
join hr_employee h on (w.worker_id = h.id)
left join product_product p on (l.product_id=p.id)
left join uom_uom u on (u.id=l.product_uom)
left join mrp_production m on (m.id=l.production_id)
%s
""" % from_clause
groupby_ = """
l.product_id,
l.production_id,
l.product_uom,
l.note,
w.name,
w.worker_id,
w.produced_date,
l.over_work_char %s
""" % (groupby)
return '%s (SELECT %s FROM %s WHERE w.worker_id IS NOT NULL GROUP BY %s)' % (
with_, select_, from_, groupby_)
def _query(self, with_clause='', fields={}, groupby='', from_clause=''):
with_ = ("WITH %s" % with_clause) if with_clause else ""
select_ = """
s.id as id,
s.id as session_id,
g.id as game_id,
p.id as player_id,
s.won as won,
g.date as date,
tm.id as teammate_id,
o1.id as opponent1_id,
o2.id as opponent2_id
"""
for field in fields.values():
select_ += field
from_ = """
kicker_session s
join kicker_game g on (g.id = s.game_id)
join res_partner p on s.player_id = p.id
join kicker_session sm on (sm.game_id=g.id and sm.team=s.team and sm.player_id!=s.player_id)
join res_partner tm on (sm.player_id=tm.id)
join kicker_session os1 on os1.game_id=g.id and os1.team!=s.team
join res_partner o1 on o1.id=os1.player_id
join kicker_session os2 on os2.game_id=g.id and os2.team!=s.team and os2.id>os1.id
join res_partner o2 on o2.id=os2.player_id
%s
""" % from_clause
groupby_ = """
s.id,
p.id,
tm.id,
o1.id,
o2.id,
g.id,
s.won %s
""" % (groupby)
return '%s (SELECT %s FROM %s GROUP BY %s)' % (with_, select_, from_, groupby_)
def _query(self, with_clause='', fields={}, groupby='', from_clause=''):
with_ = ("WITH %s" % with_clause) if with_clause else ""
select_ = """
account_move_line.id AS id,
account_move_line.account_id AS account_id,
account_account.id AS acctid,
account_account.code AS code,
account_account.name AS name,
CONCAT(account_account.code, ' ', account_account.name) AS codename,
account_move_line.date AS date,
(account_move_line.debit) AS debit,
(account_move_line.credit) AS credit,
(account_move_line.balance) AS balance,
account_move.id AS moveid,
account_account_type.id AS accttypeid
"""
for field in fields.values():
select_ += field
from_ = """
account_move_line
INNER JOIN account_move ON account_move_line.move_id = account_move.id
INNER JOIN account_account ON account_move_line.account_id = account_account.id
INNER JOIN account_account_type ON account_account.user_type_id = account_account_type.id
%s
""" % from_clause
where_ = """
((account_move.state::text) = ('posted'::text)) AND
((account_account_type.internal_group::text) = ('asset'::text)) OR
((account_account_type.internal_group::text) = ('liability'::text))
"""
order_ = """
account_account.code,
account_move_line.date desc
"""
return '%s (SELECT %s FROM %s WHERE %s ORDER BY %s)' % (
with_, select_, from_, where_, order_)
def _query(self, with_clause='', fields={}, groupby='', from_clause="", where_="", orderby_=""):
with_ = ("WITH %s" % with_clause) if with_clause else ""
select_ = """
"""
for field in fields.values():
select_ += field
from_ = """ %s
""" % from_clause
if groupby:
groupby_ = """ GROUP BY %s
""" % (groupby)
else:
groupby_ = ""
return '%s SELECT %s ' \
' FROM %s ' \
' WHERE 1=1 %s ' \
' %s ' % (with_, select_, from_, where_, groupby_)
def validate_address(self):
self.validated_dir = True # Validate the address to not break with flow
listFields = [] # Dictionary to control all fields of address
fields = {
'Street': self.partner_shipping_id.street,
'Zip': self.partner_shipping_id.zip,
'City': self.partner_shipping_id.city,
'State': self.partner_shipping_id.state_id.id,
'Country': self.partner_shipping_id.country_id.id
}
if not all(fields.values()):
warning = _(
'The address is incorrect, the following fields are empty:\n'
) # warning message
for key, value in fields.items(): # Rebuilt the list of fields
if not value:
listFields.append(_(key))
warning += ', '.join(listFields) # Separate by commas
self.env.user.notify_warning(message=warning, sticky=True)
def _query(self, with_clause='', fields={}, groupby='', from_clause=''):
with_ = ("WITH %s" % with_clause) if with_clause else ""
select_ = """
coalesce(min(l.id), -s.id) as id,
l.product_id as product_id,
t.uom_id as product_uom,
CASE WHEN l.product_id IS NOT NULL THEN sum(l.product_uom_qty / u.factor * u2.factor) ELSE 0 END as product_uom_qty,
CASE WHEN l.product_id IS NOT NULL THEN sum(l.qty_delivered / u.factor * u2.factor) ELSE 0 END as qty_delivered,
CASE WHEN l.product_id IS NOT NULL THEN sum(l.qty_invoiced / u.factor * u2.factor) ELSE 0 END as qty_invoiced,
CASE WHEN l.product_id IS NOT NULL THEN sum(l.qty_to_invoice / u.factor * u2.factor) ELSE 0 END as qty_to_invoice,
CASE WHEN l.product_id IS NOT NULL THEN sum(l.price_total / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) ELSE 0 END as price_total,
CASE WHEN l.product_id IS NOT NULL THEN sum(l.price_subtotal / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) ELSE 0 END as price_subtotal,
CASE WHEN l.product_id IS NOT NULL THEN sum(l.untaxed_amount_to_invoice / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) ELSE 0 END as untaxed_amount_to_invoice,
CASE WHEN l.product_id IS NOT NULL THEN sum(l.untaxed_amount_invoiced / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) ELSE 0 END as untaxed_amount_invoiced,
count(*) as nbr,
s.name as name,
s.date_order as date,
s.state as state,
s.partner_id as partner_id,
s.user_id as user_id,
s.company_id as company_id,
s.campaign_id as campaign_id,
s.medium_id as medium_id,
s.source_id as source_id,
extract(epoch from avg(date_trunc('day',s.date_order)-date_trunc('day',s.create_date)))/(24*60*60)::decimal(16,2) as delay,
t.categ_id as categ_id,
s.pricelist_id as pricelist_id,
s.analytic_account_id as analytic_account_id,
s.team_id as team_id,
p.product_tmpl_id,
partner.country_id as country_id,
partner.industry_id as industry_id,
partner.commercial_partner_id as commercial_partner_id,
CASE WHEN l.product_id IS NOT NULL THEN sum(p.weight * l.product_uom_qty / u.factor * u2.factor) ELSE 0 END as weight,
CASE WHEN l.product_id IS NOT NULL THEN sum(p.volume * l.product_uom_qty / u.factor * u2.factor) ELSE 0 END as volume,
l.discount as discount,
CASE WHEN l.product_id IS NOT NULL THEN sum((l.price_unit * l.product_uom_qty * l.discount / 100.0 / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END))ELSE 0 END as discount_amount,
s.id as order_id
"""
for field in fields.values():
select_ += field
from_ = """
sale_order_line l
right outer join sale_order s on (s.id=l.order_id)
join res_partner partner on s.partner_id = partner.id
left join product_product p on (l.product_id=p.id)
left join product_template t on (p.product_tmpl_id=t.id)
left join uom_uom u on (u.id=l.product_uom)
left join uom_uom u2 on (u2.id=t.uom_id)
left join product_pricelist pp on (s.pricelist_id = pp.id)
%s
""" % from_clause
groupby_ = """
l.product_id,
l.order_id,
t.uom_id,
t.categ_id,
s.name,
s.date_order,
s.partner_id,
s.user_id,
s.state,
s.company_id,
s.campaign_id,
s.medium_id,
s.source_id,
s.pricelist_id,
s.analytic_account_id,
s.team_id,
p.product_tmpl_id,
partner.country_id,
partner.industry_id,
partner.commercial_partner_id,
l.discount,
s.id %s
""" % (groupby)
return '%s (SELECT %s FROM %s GROUP BY %s)' % (with_, select_, from_, groupby_)
def _query(self, with_clause='', fields={}, groupby='', from_clause=''):
with_ = ("WITH %s" % with_clause) if with_clause else ""
select_ = """
min(l.id) as id,
l.product_id as product_id,
t.uom_id as product_uom,
sum(l.product_uom_qty / u.factor * u2.factor) as product_uom_qty,
sum(l.price_total) as price_total,
sum(l.price_subtotal) as price_subtotal,
count(*) as nbr,
s.name as name,
s.date_order as date,
s.state as state,
s.partner_id as partner_id,
s.user_id as user_id,
s.company_id as company_id,
s.campaign_id as campaign_id,
s.medium_id as medium_id,
s.source_id as source_id,
extract(epoch from avg(date_trunc('day',s.date_order)-date_trunc('day',s.create_date)))/(24*60*60)::decimal(16,2) as delay,
t.categ_id as categ_id,
s.team_id as team_id,
p.product_tmpl_id,
partner.country_id as country_id,
partner.industry_id as industry_id,
partner.commercial_partner_id as commercial_partner_id,
sum(p.weight * l.product_uom_qty / u.factor * u2.factor) as weight,
sum(p.volume * l.product_uom_qty / u.factor * u2.factor) as volume,
l.discount as discount,
sum((l.price_unit * l.discount / 100.0 )) as discount_amount,
s.id as order_id
"""
for field in fields.values():
select_ += field
from_ = """
sale_return_line l
join sale_return s on (l.order_id=s.id)
join res_partner partner on s.partner_id = partner.id
left join product_product p on (l.product_id=p.id)
left join product_template t on (p.product_tmpl_id=t.id)
left join uom_uom u on (u.id=l.product_uom)
left join uom_uom u2 on (u2.id=t.uom_id)
%s
""" % from_clause
groupby_ = """
l.product_id,
l.order_id,
t.uom_id,
t.categ_id,
s.name,
s.date_order,
s.partner_id,
s.user_id,
s.state,
s.company_id,
s.campaign_id,
s.medium_id,
s.source_id,
s.team_id,
p.product_tmpl_id,
partner.country_id,
partner.industry_id,
partner.commercial_partner_id,
l.discount,
s.id %s
""" % (groupby)
return '%s (SELECT %s FROM %s WHERE l.product_id IS NOT NULL GROUP BY %s)' % (
with_, select_, from_, groupby_)
def _query(self, with_clause='', fields={}, groupby='', from_clause=''):
with_ = ("WITH %s" % with_clause) if with_clause else ""
select_ = """
min(pl.id) as id,
pl.product_id as product_id,
pl.actual_qty as actual_qty,
po.name as name,
po.date_order as date_order,
pl.product_qty as qty,
pl.product_uom as product_uom,
pl.price_subtotal as vendor_total,
sl.price_subtotal as customer_total,
po.shipment_date as shipment_date,
po.partner_id as partner_id,
po.customer_id as customer_id,
p.product_tmpl_id,
plc.no_of_packages as no_of_packages,
plc.landing_date_etd as bill_of_ladding_date,
plc.landing_date_eta as date_arrival,
partner.country_id as country_id,
pl.price_unit as purchase_price,
sl.price_unit as selling_price,
pl.commission as commission,
po.notes as notes
"""
for field in fields.values():
select_ += field
from_ = """
sale_order_line sl
left outer join purchase_order_line pl on (sl.purchase_order_line_id=pl.id)
left outer join purchase_order po on (pl.order_id=po.id)
left outer join sale_order so on (sl.order_id=so.id)
left outer join purchase_landing_cost plc on (pl.order_id=plc.purchase_id)
left outer join res_partner partner on po.partner_id = partner.id
left join product_product p on (pl.product_id=p.id)
left join product_template t on (p.product_tmpl_id=t.id)
left join uom_uom u on (u.id=pl.product_uom)
left join uom_uom u2 on (u2.id=t.uom_id)
%s
""" % from_clause
groupby_ = """
po.name,
po.partner_id,
po.customer_id,
partner.country_id,
pl.product_id,
pl.price_unit,
pl.product_uom,
pl.product_qty,
po.date_order,
plc.no_of_packages,
pl.price_subtotal,
sl.price_subtotal,
sl.price_unit,
pl.actual_qty,
po.shipment_date,
p.product_tmpl_id,
plc.landing_date_etd,
plc.landing_date_eta,
pl.commission,
po.notes %s
""" % (groupby)
return '%s (SELECT %s FROM %s WHERE pl.product_id IS NOT NULL GROUP BY %s)' % (
with_, select_, from_, groupby_)
def _query(self, with_clause='', fields={}, groupby='', from_clause=''):
fields[
'days_to_confirm'] = ", DATE_PART('day', s.confirmation_date::timestamp - s.create_date::timestamp) as days_to_confirm"
fields['invoice_status'] = ', s.invoice_status as invoice_status'
groupby += ', s.invoice_status'
with_ = ("WITH %s" % with_clause) if with_clause else ""
select_ = """
min(l.id) as id,
l.product_id as product_id,
t.uom_id as product_uom,
sum(l.product_uom_qty / u.factor * u2.factor) as product_uom_qty,
sum(l.qty_delivered / u.factor * u2.factor) as qty_delivered,
sum(l.qty_invoiced / u.factor * u2.factor) as qty_invoiced,
sum(l.qty_to_invoice / u.factor * u2.factor) as qty_to_invoice,
sum(l.price_total / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) as price_total,
sum(l.price_subtotal / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) as price_subtotal,
sum(l.untaxed_amount_to_invoice / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) as untaxed_amount_to_invoice,
sum(l.untaxed_amount_invoiced / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) as untaxed_amount_invoiced,
count(*) as nbr,
s.name as name,
s.date_order as date,
s.confirmation_date as confirmation_date,
s.state as state,
s.partner_id as partner_id,
partner.simple_name as partner_simple_name,
s.user_id as user_id,
s.company_id as company_id,
extract(epoch from avg(date_trunc('day',s.date_order)-date_trunc('day',s.create_date)))/(24*60*60)::decimal(16,2) as delay,
t.categ_id as categ_id,
s.pricelist_id as pricelist_id,
s.analytic_account_id as analytic_account_id,
s.team_id as team_id,
p.product_tmpl_id,
partner.country_id as country_id,
partner.commercial_partner_id as commercial_partner_id,
sum(p.weight * l.product_uom_qty / u.factor * u2.factor) as weight,
sum(p.volume * l.product_uom_qty / u.factor * u2.factor) as volume,
l.discount as discount,
sum((l.price_unit * l.discount / 100.0 / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END)) as discount_amount,
s.id as order_id
"""
for field in fields.values():
select_ += field
from_ = """
sub_sale_order l
join sale_order s on (l.order_id=s.id)
join res_partner partner on s.partner_id = partner.id
left join product_product p on (l.product_id=p.id)
left join product_template t on (p.product_tmpl_id=t.id)
left join uom_uom u on (u.id=l.product_uom)
left join uom_uom u2 on (u2.id=t.uom_id)
left join product_pricelist pp on (s.pricelist_id = pp.id)
%s
""" % from_clause
groupby_ = """
l.product_id,
l.order_id,
t.uom_id,
t.categ_id,
s.name,
s.date_order,
s.confirmation_date,
s.partner_id,
partner.simple_name,
s.user_id,
s.state,
s.company_id,
s.pricelist_id,
s.analytic_account_id,
s.team_id,
p.product_tmpl_id,
partner.country_id,
partner.commercial_partner_id,
l.discount,
s.id %s
""" % (groupby)
return '%s (SELECT %s FROM %s WHERE l.product_id IS NOT NULL GROUP BY %s)' % (
with_, select_, from_, groupby_)
def _query(self, with_clause='', fields={}, groupby='', from_clause=''):
with_ = ("WITH %s" % with_clause) if with_clause else ""
select_ = """
min(l.id) as id,
po.name as name,
po.date_order as date_order,
'Purchase Order' as trans_type,
po.partner_id as partner_id,
po.company_id as company_id,
l.product_id,
p.product_tmpl_id,
t.categ_id as category_id,
sum(l.product_qty / line_uom.factor * product_uom.factor) as qty_ordered,
t.uom_id as product_uom,
sum(l.price_total)::decimal(16,2) as price_total,
(sum(l.product_uom_qty * l.price_unit)/NULLIF(sum(l.product_uom_qty/line_uom.factor*product_uom.factor),0.0))::decimal(16,2) as price_average,
partner.country_id as country_id,
analytic_account.id as account_analytic_id
"""
for field in fields.values():
select_ += field
from_ = """
purchase_order_line l
join purchase_order po on (l.order_id=po.id)
join res_partner partner on po.partner_id = partner.id
left join product_product p on (l.product_id=p.id)
left join product_template t on (p.product_tmpl_id=t.id)
left join uom_uom line_uom on (line_uom.id=l.product_uom)
left join uom_uom product_uom on (product_uom.id=t.uom_id)
left join account_analytic_account analytic_account on (l.account_analytic_id = analytic_account.id) %s
""" % from_clause
groupby_ = """
po.name,
po.company_id,
po.partner_id,
l.price_unit,
l.product_uom,
l.product_id,
p.product_tmpl_id,
t.categ_id,
trans_type,
po.date_order,
t.uom_id,
line_uom.id,
product_uom.factor,
partner.country_id,
analytic_account.id %s
""" % groupby
select2_ = """
min(l.id) as id,
po.name as name,
po.date_order as date_order,
'Return Order' as trans_type,
po.partner_id as partner_id,
po.company_id as company_id,
l.product_id,
p.product_tmpl_id,
t.categ_id as category_id,
sum(l.product_qty / line_uom.factor * product_uom.factor)*-1 as qty_ordered,
t.uom_id as product_uom,
sum(l.price_total)::decimal(16,2) *-1 as price_total,
(sum(l.product_uom_qty * l.price_unit)/NULLIF(sum(l.product_uom_qty/line_uom.factor*product_uom.factor),0.0))::decimal(16,2) *-1 as price_average,
partner.country_id as country_id,
analytic_account.id as account_analytic_id
"""
for field in fields.values():
select2_ += field
from2_ = """
purchase_return_line l
join purchase_return po on (l.order_id=po.id)
join res_partner partner on po.partner_id = partner.id
left join product_product p on (l.product_id=p.id)
left join product_template t on (p.product_tmpl_id=t.id)
left join uom_uom line_uom on (line_uom.id=l.product_uom)
left join uom_uom product_uom on (product_uom.id=t.uom_id)
left join account_analytic_account analytic_account on (l.account_analytic_id = analytic_account.id) %s
""" % from_clause
groupby2_ = """
po.name,
po.company_id,
po.partner_id,
l.price_unit,
l.product_uom,
l.product_id,
p.product_tmpl_id,
t.categ_id,
trans_type,
po.date_order,
t.uom_id,
line_uom.id,
product_uom.factor,
partner.country_id,
analytic_account.id %s
""" % groupby
return '%s (SELECT %s FROM %s WHERE l.product_id IS NOT NULL GROUP BY %s Union SELECT %s FROM %s WHERE ' \
'l.product_id IS NOT NULL GROUP BY %s)' % (with_, select_, from_, groupby_, select2_, from2_, groupby2_)
def _query(self, with_clause='', fields={}, groupby='', from_clause=''):
with_ = ("WITH %s" % with_clause) if with_clause else ""
select_ = """
min(l.id) as id,
l.product_id as product_id,
t.uom_id as product_uom,
sum(l.product_uom_qty / u.factor * u2.factor) as product_uom_qty,
sum(l.qty_delivered / u.factor * u2.factor) as qty_delivered,
sum(l.qty_invoiced / u.factor * u2.factor) as qty_invoiced,
sum(l.qty_to_invoice / u.factor * u2.factor) as qty_to_invoice,
sum(l.price_total / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) as price_total,
sum(l.price_subtotal / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) as price_subtotal,
sum(l.untaxed_amount_to_invoice / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) as untaxed_amount_to_invoice,
sum(l.untaxed_amount_invoiced / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) as untaxed_amount_invoiced,
count(*) as nbr,
s.name as name,
s.date_order as date,
s.confirmation_date as confirmation_date,
s.state as state,
s.partner_id as partner_id,
s.user_id as user_id,
s.company_id as company_id,
extract(epoch from avg(date_trunc('day',s.date_order)-date_trunc('day',s.create_date)))/(24*60*60)::decimal(16,2) as delay,
t.categ_id as categ_id,
s.pricelist_id as pricelist_id,
s.analytic_account_id as analytic_account_id,
s.team_id as team_id,
p.product_tmpl_id,
partner.country_id as country_id,
partner.commercial_partner_id as commercial_partner_id,
sum(p.weight * l.product_uom_qty / u.factor * u2.factor) as weight,
sum(p.volume * l.product_uom_qty / u.factor * u2.factor) as volume,
l.discount as discount,
sum((l.price_unit * l.discount / 100.0 / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END)) as discount_amount,
s.id as order_id
"""
for field in fields.values():
select_ += field
from_ = """
sale_order_line l
join sale_order s on (l.order_id=s.id)
join res_partner partner on s.partner_id = partner.id
left join product_product p on (l.product_id=p.id)
left join product_template t on (p.product_tmpl_id=t.id)
left join uom_uom u on (u.id=l.product_uom)
left join uom_uom u2 on (u2.id=t.uom_id)
left join product_pricelist pp on (s.pricelist_id = pp.id)
%s
""" % from_clause
groupby_ = """
l.product_id,
l.order_id,
t.uom_id,
t.categ_id,
s.name,
s.date_order,
s.confirmation_date,
s.partner_id,
s.user_id,
s.state,
s.company_id,
s.pricelist_id,
s.analytic_account_id,
s.team_id,
p.product_tmpl_id,
partner.country_id,
partner.commercial_partner_id,
l.discount,
s.id %s
""" % (groupby)
return '%s (SELECT %s FROM %s WHERE l.product_id IS NOT NULL GROUP BY %s)' % (with_, select_, from_, groupby_)
def _query(self, with_clause='', fields={}, groupby='', from_clause=''):
with_ = ("WITH %s" % with_clause) if with_clause else ""
select_ = """
min(l.id) as id,
l.product_id as product_id,
t.uom_id as product_uom,
round(sum(l.product_uom_qty / u.factor * u2.factor),3) as product_uom_qty,
sum(l.price_total) as price_total,
sum(l.price_subtotal) as price_subtotal,
s.name as name,
s.date_order as date,
'Sales Order' as trans_type,
s.analytic_account_id as analytic_account_id,
s.state as state,
s.partner_id as partner_id,
t.categ_id as categ_id,
p.product_tmpl_id,
round(sum((l.price_unit * l.discount / 100.0 )),3) as discount_amount
"""
for field in fields.values():
select_ += field
from_ = """
sale_order_line l
join sale_order s on (l.order_id=s.id)
join res_partner partner on s.partner_id = partner.id
left join product_product p on (l.product_id=p.id)
left join product_template t on (p.product_tmpl_id=t.id)
left join uom_uom u on (u.id=l.product_uom)
left join uom_uom u2 on (u2.id=t.uom_id)
%s
""" % from_clause
groupby_ = """
l.product_id,
l.order_id,
t.uom_id,
t.categ_id,
s.name,
s.date_order,
trans_type,
s.analytic_account_id,
s.partner_id,
s.state,
p.product_tmpl_id %s
""" % groupby
select2_ = """
min(l.id) as id,
l.product_id as product_id,
t.uom_id as product_uom,
round(sum(l.product_uom_qty / u.factor * u2.factor),3) *-1 as product_uom_qty,
sum(l.price_total)*-1 as price_total,
sum(l.price_subtotal)*-1 as price_subtotal,
s.name as name,
s.date_order as date,
'Return Order' as trans_type,
s.analytic_account_id as analytic_account_id,
s.state as state,
s.partner_id as partner_id,
t.categ_id as categ_id,
p.product_tmpl_id,
round(sum((l.price_unit * l.discount / 100.0 )),3) as discount_amount
"""
for field in fields.values():
select2_ += field
from2_ = """
sale_return_line l
join sale_return s on (l.order_id=s.id)
join res_partner partner on s.partner_id = partner.id
left join product_product p on (l.product_id=p.id)
left join product_template t on (p.product_tmpl_id=t.id)
left join uom_uom u on (u.id=l.product_uom)
left join uom_uom u2 on (u2.id=t.uom_id)
%s
""" % from_clause
groupby2_ = """
l.product_id,
l.order_id,
t.uom_id,
t.categ_id,
s.name,
s.date_order,
trans_type,
s.analytic_account_id,
s.partner_id,
s.state,
p.product_tmpl_id %s
""" % groupby
select3_ = """
min(pol.id) as id,
pol.product_id as product_id,
pt.uom_id as product_uom,
round(sum(pol.qty / u.factor * u2.factor),3) as product_uom_qty,
sum(pol.price_unit * pol.qty - pol.price_unit * pol.qty / 100 * pol.discount) as price_total,
sum(pol.price_unit * pol.qty - pol.price_unit * pol.qty / 100 * pol.discount) as price_subtotal,
po.name as name,
po.date_order as date,
'POS' as trans_type,
po.analytic_account_id as analytic_account_id,
po.state as state,
po.partner_id as partner_id,
pt.categ_id as categ_id,
pp.product_tmpl_id,
round(sum((pol.price_unit * pol.discount / 100.0 ) + cast(pol.extra_discount_value as numeric)),3) as discount_amount
"""
for field in fields.values():
select3_ += field
from3_ = """
pos_order_line pol
LEFT JOIN pos_order po ON po.id = pol.order_id
LEFT JOIN product_product pp ON pp.id = pol.product_id
LEFT JOIN product_template pt ON pt.id = pp.product_tmpl_id
left join uom_uom u on (u.id=pol.product_uom_id)
left join uom_uom u2 on (u2.id=pt.uom_id)
left join res_partner partner on po.partner_id = partner.id
%s
""" % from_clause
groupby3_ = """
pol.product_id,
pt.uom_id,
po.name,
po.date_order,
po.analytic_account_id,
po.state,
po.partner_id,
pt.categ_id,
pp.product_tmpl_id %s
""" % groupby
return '%s (SELECT %s FROM %s WHERE l.product_id IS NOT NULL GROUP BY %s Union SELECT %s FROM %s WHERE ' \
'l.product_id IS NOT NULL GROUP BY %s Union SELECT %s FROM %s WHERE ' \
'pol.product_id IS NOT NULL GROUP BY %s)' % (
with_, select_, from_, groupby_, select2_, from2_, groupby2_, select3_, from3_, groupby3_)
def _query(with_clause='', fields={}, groupby='', from_clause=''):
"""
l = sale_order_line
s = sale_order
partner = res_partner
p = product_product
t = product_template
u = uom_uom on (u.id=l.product_uom)
u2 = uom_uom on (u2.id=t.uom_id)
pp = product_pricelist
"""
with_ = ("WITH %s" % with_clause) if with_clause else ""
select_ = """
min(l.id) as id,
l.product_id as product_id,
t.uom_id as product_uom,
sum(l.product_uom_qty / u.factor * u2.factor) as product_uom_qty,
sum(l.qty_delivered / u.factor * u2.factor) as qty_delivered,
sum(l.qty_invoiced / u.factor * u2.factor) as qty_invoiced,
sum(l.price_total / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) as price_total,
sum(l.price_subtotal / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) as price_subtotal,
sum(l.price_unit * l.product_uom_qty / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) as subtotal_nodiscount,
sum(l.untaxed_amount_invoiced / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) as untaxed_amount_invoiced,
sum(l.margin / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) as margin,
sum(l.margin_invoiced / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) as margin_invoiced,
sum(l.price_tax / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END) as price_tax,
count(*) as nbr,
s.name as name,
s.date_order as date,
s.state as state,
s.partner_id as partner_id,
s.create_uid as created_by,
s.user_id as user_id,
s.company_id as company_id,
t.categ_id as categ_id,
s.pricelist_id as pricelist_id,
s.team_id as team_id,
s.warehouse_id as warehouse_id,
s.invoice_status as invoice_status,
p.product_tmpl_id,
partner.country_id as country_id,
partner.commercial_partner_id as commercial_partner_id,
sum(p.weight * l.product_uom_qty / u.factor * u2.factor) as weight,
sum(p.weight * l.qty_delivered / u.factor * u2.factor) as weight_delivered,
l.route_id as route_id,
sum((l.price_unit * l.product_uom_qty * l.discount / 100.0 / CASE COALESCE(s.currency_rate, 0) WHEN 0 THEN 1.0 ELSE s.currency_rate END)) as discount_amount,
s.days_to_confirm as days_to_confirm,
s.days_to_invoice as days_to_invoice,
s.id as order_id
"""
for field in fields.values():
select_ += field
from_ = """
sale_order_line l
join sale_order s on (l.order_id=s.id)
join res_partner partner on (s.partner_id = partner.id)
left join product_product p on (l.product_id=p.id)
left join product_template t on (p.product_tmpl_id=t.id)
left join uom_uom u on (u.id=l.product_uom)
left join uom_uom u2 on (u2.id=t.uom_id)
left join product_pricelist pp on (s.pricelist_id = pp.id)
%s
""" % from_clause
groupby_ = """
l.product_id,
l.order_id,
t.uom_id,
t.categ_id,
s.name,
s.date_order,
s.partner_id,
s.create_uid,
s.user_id,
s.state,
s.company_id,
s.pricelist_id,
s.team_id,
s.warehouse_id,
s.invoice_status,
p.product_tmpl_id,
partner.country_id,
partner.commercial_partner_id,
l.route_id,
s.id %s
""" % groupby
return '%s (SELECT %s FROM %s WHERE l.product_id IS NOT NULL GROUP BY %s)' % (with_, select_, from_, groupby_)
def _query(self, with_clause='', fields={}, groupby='', from_clause=''):
with_ = """currency_rate as (%s) %s""" % (self.env['res.currency']._select_companies_rates(), with_clause)
select_ = """
min(l.id) as id,
l.product_id as product_id,
t.uom_id as product_uom,
sum(l.product_uom_qty / u.factor * u2.factor) as product_uom_qty,
sum(l.qty_delivered / u.factor * u2.factor) as qty_delivered,
sum(l.qty_invoiced / u.factor * u2.factor) as qty_invoiced,
sum(l.qty_to_invoice / u.factor * u2.factor) as qty_to_invoice,
sum(l.price_total / COALESCE(cr.rate, 1.0)) as price_total,
sum(l.price_subtotal / COALESCE(cr.rate, 1.0)) as price_subtotal,
sum(l.price_reduce * l.qty_to_invoice / COALESCE(cr.rate, 1.0)) as amount_to_invoice,
sum(l.price_reduce * l.qty_invoiced / COALESCE(cr.rate, 1.0)) as amount_invoiced,
count(*) as nbr,
s.name as name,
s.date_order as date,
s.confirmation_date as confirmation_date,
s.state as state,
s.partner_id as partner_id,
s.user_id as user_id,
s.company_id as company_id,
extract(epoch from avg(date_trunc('day',s.date_order)-date_trunc('day',s.create_date)))/(24*60*60)::decimal(16,2) as delay,
t.categ_id as categ_id,
s.pricelist_id as pricelist_id,
s.analytic_account_id as analytic_account_id,
s.team_id as team_id,
p.product_tmpl_id,
partner.country_id as country_id,
partner.commercial_partner_id as commercial_partner_id,
sum(p.weight * l.product_uom_qty / u.factor * u2.factor) as weight,
sum(p.volume * l.product_uom_qty / u.factor * u2.factor) as volume
"""
for field in fields.values():
select_ += field
from_ = """
sale_order_line l
join sale_order s on (l.order_id=s.id)
join res_partner partner on s.partner_id = partner.id
left join product_product p on (l.product_id=p.id)
left join product_template t on (p.product_tmpl_id=t.id)
left join uom_uom u on (u.id=l.product_uom)
left join uom_uom u2 on (u2.id=t.uom_id)
left join product_pricelist pp on (s.pricelist_id = pp.id)
left join currency_rate cr on (cr.currency_id = pp.currency_id and
cr.company_id = s.company_id and
cr.date_start <= coalesce(s.date_order, now()) and
(cr.date_end is null or cr.date_end > coalesce(s.date_order, now())))
%s
""" % from_clause
groupby_ = """
l.product_id,
l.order_id,
t.uom_id,
t.categ_id,
s.name,
s.date_order,
s.confirmation_date,
s.partner_id,
s.user_id,
s.state,
s.company_id,
s.pricelist_id,
s.analytic_account_id,
s.team_id,
p.product_tmpl_id,
partner.country_id,
partner.commercial_partner_id %s
""" % (groupby)
return 'WITH %s (SELECT %s FROM %s WHERE l.product_id IS NOT NULL GROUP BY %s)' % (with_, select_, from_, groupby_)
def _query(self, with_clause='', fields={}, groupby='', from_clause=''):
with_ = ("WITH %s" % with_clause) if with_clause else ""
select_ = """
sale_order_line.id AS id,
sale_order_line.name AS name,
sale_order_line.qty_invoiced AS invqty,
sale_order_line.qty_delivered AS qty_delivered,
sale_order_line.qty_delivered_manual AS qty_delivered_manual,
sale_order_line.product_uom_qty AS orderqty,
sale_order_line.product_id AS item,
sale_order.id AS soid,
sale_order.state AS sostate,
sale_order.name AS soname,
sale_order.date_order AS orderdate,
sale_order.partner_id AS customer,
sale_order.partner_invoice_id AS invto,
sale_order.partner_shipping_id AS shipto,
res_partner.id AS partnerid,
res_partner.display_name AS whotoinvoice,
product_product.id as productid,
product_product.default_code as itemcode,
product_template.id as producttmplid,
product_template.type as itemtype
"""
for field in fields.values():
select_ += field
from_ = """
sale_order_line
INNER JOIN sale_order ON sale_order_line.order_id = sale_order.id
INNER JOIN res_partner ON sale_order.partner_invoice_id = res_partner.id
INNER JOIN product_product ON sale_order_line.product_id = product_product.id
INNER JOIN product_template ON product_product.product_tmpl_id = product_template.id
%s
""" % from_clause
where_ = """
(sale_order_line.qty_delivered::numeric < sale_order_line.qty_invoiced::numeric)
AND (sale_order_line.invoice_status::text = 'invoiced'::text)
AND (sale_order_line.qty_invoiced::numeric >= 1::numeric)
AND (sale_order_line.product_uom_qty::numeric >= 1::numeric)
AND (sale_order_line.name NOT LIKE 'SHIPPING%')
AND (sale_order_line.name NOT LIKE 'POST%')
AND (sale_order_line.name NOT LIKE 'SVCE%')
AND (sale_order_line.name NOT LIKE 'Courier%')
AND (sale_order_line.name NOT LIKE 'SVCE%')
AND (sale_order_line.name NOT LIKE 'Australia Post%')
AND (sale_order_line.name NOT LIKE '[SHIPPING%')
AND (sale_order_line.name NOT LIKE '[POST%')
AND (sale_order_line.name NOT LIKE '[SVCE%')
AND (sale_order_line.name NOT LIKE '[Courier%')
AND (sale_order_line.name NOT LIKE '[SVCE%')
AND (sale_order_line.name NOT LIKE '[Australia Post%')
AND (sale_order.state::text <> 'draft'::text)
AND (sale_order.state::text <> 'cancel'::text)
AND (sale_order.state::text <> 'done'::text)
AND (product_template.type::text <> 'service'::text)
AND (product_template.type::text <> 'consu'::text)
AND (product_product.default_code::text <> 'SHIPPING'::text)
AND (product_product.default_code::text NOT ILIKE 'POST%'::text)
"""
order_ = """
sale_order.id DESC,
sale_order_line.name ASC
"""
return '%s (SELECT %s FROM %s WHERE %s ORDER BY %s)' % (
with_, select_, from_, where_, order_)
