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_)