# -*- coding: utf-8 -*- from openerp.http import request as r def get_account_invoice_line_all_type(): user_store = r.env.user.store_id.id company_currency_rate = r.env.user.company_id.currency_id.rate query = ''' SELECT invoice.id AS invoice_id, line.id AS invoice_line_id, invoice.number, invoice.origin, invoice.date_invoice, invoice.type, line.price_unit * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1]) as price_unit, line.quantity AS cant, line.price_subtotal * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1]) AS subtotal, (line.quantity * (line.price_unit * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1]))) - (line.price_subtotal * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1])) AS impuestos, (array_agg(history.cost ORDER BY history.id DESC))[1] AS cost, --line.name, journal.store_id, template.categ_id FROM account_invoice AS invoice LEFT JOIN account_invoice_line AS line ON line.invoice_id = invoice.id LEFT JOIN product_product AS product ON line.product_id = product.id LEFT JOIN product_template AS template ON template.id = product.product_tmpl_id LEFT JOIN res_store_journal_rel AS journal ON journal.journal_id = invoice.journal_id LEFT JOIN product_price_history AS history ON history.product_template_id = product.product_tmpl_id LEFT JOIN res_currency_rate AS rate ON rate.currency_id = invoice.currency_id WHERE invoice.state NOT IN ('draft', 'cancel') GROUP BY invoice.id, line.id, invoice.number, invoice.origin, invoice.date_invoice, product.name_template, line.price_unit, line.quantity, line.price_subtotal, journal.store_id, template.categ_id ''' r.cr.execute(query,(tuple([company_currency_rate,company_currency_rate,company_currency_rate,company_currency_rate]))) return [ { 'invoice_id': j[0], 'invoice_line_id': j[1], 'number': j[2], 'origin': j[3], 'date': j[4], 'type': j[5], 'product_name':j[6], 'price_unit':j[7], 'quantity':j[8], 'subtotal':j[9], 'tax': j[10], 'cost': j[11], 'store_id': j[12], 'categ_id': j[13], } for j in r.cr.fetchall() ] def get_account_invoice_line_out_invoice(): #analisis de venta user_store = r.env.user.store_id.id company_currency_rate = r.env.user.company_id.currency_id.rate validate_brand = ''' SELECT EXISTS( SELECT table_name FROM information_schema.columns WHERE table_schema='public' AND table_name='product_brand') ''' query_with_brand = ''' SELECT invoice.id AS invoice_id, line.id AS invoice_line_id, invoice.number, invoice.origin, invoice.date_invoice, invoice.type, CASE WHEN product.default_code IS NOT NULL THEN ('[' || product.default_code || '] ' || product.name_template) ELSE product.name_template END AS display_name, line.price_unit * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1]) as price_unit, line.quantity AS cant, line.price_subtotal * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1]) AS subtotal, (line.quantity * (line.price_unit * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1]))) - (((line.quantity * (line.price_unit * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1]) * line.discount)/100))) - (line.price_subtotal * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1])) AS impuestos, (array_agg(history.cost ORDER BY history.id DESC))[1] AS cost, journal.store_id, template.categ_id, partner.ruc, partner.name, product.id, invoice.company_id, invoice.journal_id, (array_agg(attr_rel.att_id)) AS attr_rel, (array_agg(attr_value.name)) AS attr_value, (array_agg(attr.id)) AS attr, template.product_brand_id, brand.name, line.discount, line.name FROM account_invoice AS invoice LEFT JOIN account_invoice_line AS line ON line.invoice_id = invoice.id --Product LEFT JOIN product_product AS product ON line.product_id = product.id LEFT JOIN product_template AS template ON template.id = product.product_tmpl_id LEFT JOIN product_attribute_value_product_product_rel AS attr_rel ON attr_rel.prod_id = product.id LEFT JOIN product_attribute_value AS attr_value ON attr_value.id = attr_rel.att_id LEFT JOIN product_attribute AS attr ON attr.id = attr_value.attribute_id LEFT JOIN res_store_journal_rel AS journal ON journal.journal_id = invoice.journal_id LEFT JOIN product_price_history AS history ON history.product_template_id = product.product_tmpl_id LEFT JOIN res_currency_rate AS rate ON rate.currency_id = invoice.currency_id LEFT JOIN res_partner AS partner ON partner.id = invoice.partner_id LEFT JOIN product_brand AS brand ON brand.id = template.product_brand_id WHERE invoice.state NOT IN ('draft', 'cancel') AND invoice.type = 'out_invoice' GROUP BY invoice.id, line.id, invoice.number, invoice.origin, invoice.date_invoice, product.name_template, line.price_unit, line.quantity, line.price_subtotal, --line.name, journal.store_id, template.categ_id, product.default_code, partner.ruc, partner.name, product.id, invoice.company_id, invoice.journal_id, template.product_brand_id, brand.name, line.name ''' query_without_brand = ''' SELECT invoice.id AS invoice_id, line.id AS invoice_line_id, invoice.number, invoice.origin, invoice.date_invoice, invoice.type, CASE WHEN product.default_code IS NOT NULL THEN ('[' || product.default_code || '] ' || product.name_template) ELSE product.name_template END AS display_name, line.price_unit * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1]) as price_unit, line.quantity AS cant, line.price_subtotal * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1]) AS subtotal, (line.quantity * (line.price_unit * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1]))) - (((line.quantity * (line.price_unit * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1]) * line.discount)/100))) - (line.price_subtotal * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1])) AS impuestos, (array_agg(history.cost ORDER BY history.id DESC))[1] AS cost, journal.store_id, template.categ_id, partner.ruc, partner.name, product.id, invoice.company_id, invoice.journal_id, (array_agg(attr_rel.att_id)) AS attr_rel, (array_agg(attr_value.name)) AS attr_value, (array_agg(attr.id)) AS attr, line.discount, line.name FROM account_invoice AS invoice LEFT JOIN account_invoice_line AS line ON line.invoice_id = invoice.id --Product LEFT JOIN product_product AS product ON line.product_id = product.id LEFT JOIN product_template AS template ON template.id = product.product_tmpl_id LEFT JOIN product_attribute_value_product_product_rel AS attr_rel ON attr_rel.prod_id = product.id LEFT JOIN product_attribute_value AS attr_value ON attr_value.id = attr_rel.att_id LEFT JOIN product_attribute AS attr ON attr.id = attr_value.attribute_id LEFT JOIN res_store_journal_rel AS journal ON journal.journal_id = invoice.journal_id LEFT JOIN product_price_history AS history ON history.product_template_id = product.product_tmpl_id LEFT JOIN res_currency_rate AS rate ON rate.currency_id = invoice.currency_id LEFT JOIN res_partner AS partner ON partner.id = invoice.partner_id WHERE invoice.state NOT IN ('draft', 'cancel') AND invoice.type = 'out_invoice' GROUP BY invoice.id, line.id, invoice.number, invoice.origin, invoice.date_invoice, product.name_template, -- line.name, line.price_unit, line.quantity, line.price_subtotal, journal.store_id, template.categ_id, product.default_code, partner.ruc, partner.name, product.id, invoice.company_id, invoice.journal_id, line.name ''' r.cr.execute(validate_brand) for j in r.cr.fetchall(): brand = j[0] if brand == True: r.cr.execute(query_with_brand,[company_currency_rate,company_currency_rate,company_currency_rate,company_currency_rate,company_currency_rate]) return [ { 'invoice_id': j[0], 'invoice_line_id': j[1], 'number': j[2], 'origin': j[3], 'date': j[4], 'type': j[5], 'product_name':j[6], 'price_unit':j[7], 'quantity':j[8], 'subtotal':j[9], 'tax': j[10], 'cost': j[11], 'store_id': j[12], 'categ_id': j[13], 'partner_name': j[14], 'partner_ruc': j[15], 'product_id': j[16], 'company_id': j[17], 'journal_id': j[18], 'attribute_value_ids': j[19], 'attribute_values': j[20], 'attribute_ids': j[21], 'product_brand_id': j[22], 'brand_name': j[23], 'discount': j[24], 'name': j[25], } for j in r.cr.fetchall() ] else: r.cr.execute(query_without_brand,[company_currency_rate,company_currency_rate,company_currency_rate,company_currency_rate,company_currency_rate]) return [ { 'invoice_id': j[0], 'invoice_line_id': j[1], 'number': j[2], 'origin': j[3], 'date': j[4], 'type': j[5], 'product_name':j[6], 'price_unit':j[7], 'quantity':j[8], 'subtotal':j[9], 'tax': j[10], 'cost': j[11], 'store_id': j[12], 'categ_id': j[13], 'partner_ruc': j[14], 'partner_name': j[15], 'product_id': j[16], 'company_id': j[17], 'journal_id': j[18], 'attribute_value_ids': j[19], 'attribute_values': j[20], 'attribute_ids': j[21], 'discount': j[22], 'name': j[23], } for j in r.cr.fetchall() ] def get_account_invoice_line_out_invoice_and_out_refund(): user_store = r.env.user.store_id.id company_currency_rate = r.env.user.company_id.currency_id.rate validate_brand = ''' SELECT EXISTS( SELECT table_name FROM information_schema.columns WHERE table_schema='public' AND table_name='product_brand') ''' query_with_brand = ''' SELECT invoice.id AS invoice_id, line.id AS invoice_line_id, invoice.number, invoice.origin, invoice.date_invoice, invoice.type, CASE WHEN product.default_code IS NOT NULL THEN ('[' || product.default_code || '] ' || product.name_template) ELSE product.name_template END AS display_name, line.price_unit * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1]) as price_unit, line.quantity AS cant, line.price_subtotal * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1]) AS subtotal, (line.quantity * (line.price_unit * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1]))) - (line.price_subtotal * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1])) AS impuestos, (array_agg(history.cost ORDER BY history.id DESC))[1] AS cost, journal.store_id, template.categ_id, partner.name, product.id, invoice.company_id, invoice.journal_id, (array_agg(attr_rel.att_id)) AS attr_rel, (array_agg(attr_value.name)) AS attr_value, (array_agg(attr.id)) AS attr, template.product_brand_id, brand.name, partner.id FROM account_invoice AS invoice LEFT JOIN account_invoice_line AS line ON line.invoice_id = invoice.id --Product LEFT JOIN product_product AS product ON line.product_id = product.id LEFT JOIN product_template AS template ON template.id = product.product_tmpl_id LEFT JOIN product_attribute_value_product_product_rel AS attr_rel ON attr_rel.prod_id = product.id LEFT JOIN product_attribute_value AS attr_value ON attr_value.id = attr_rel.att_id LEFT JOIN product_attribute AS attr ON attr.id = attr_value.attribute_id LEFT JOIN res_store_journal_rel AS journal ON journal.journal_id = invoice.journal_id LEFT JOIN product_price_history AS history ON history.product_template_id = product.product_tmpl_id LEFT JOIN res_currency_rate AS rate ON rate.currency_id = invoice.currency_id LEFT JOIN res_partner AS partner ON partner.id = invoice.partner_id LEFT JOIN product_brand AS brand ON brand.id = template.product_brand_id WHERE invoice.state NOT IN ('draft', 'cancel') AND invoice.type in ('out_invoice','out_refund') GROUP BY invoice.id, line.id, invoice.number, invoice.origin, invoice.date_invoice, product.name_template, line.price_unit, line.quantity, line.price_subtotal, journal.store_id, template.categ_id, product.default_code, partner.name, product.id, invoice.company_id, invoice.journal_id, template.product_brand_id, brand.name, partner.id ''' query_without_brand = ''' SELECT invoice.id AS invoice_id, line.id AS invoice_line_id, invoice.number, invoice.origin, invoice.date_invoice, invoice.type, CASE WHEN product.default_code IS NOT NULL THEN ('[' || product.default_code || '] ' || product.name_template) ELSE product.name_template END AS display_name, line.price_unit * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1]) as price_unit, line.quantity AS cant, line.price_subtotal * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1]) AS subtotal, (line.quantity * (line.price_unit * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1]))) - (line.price_subtotal * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1])) AS impuestos, (array_agg(history.cost ORDER BY history.id DESC))[1] AS cost, --line.name journal.store_id, template.categ_id, partner.name, product.id, invoice.company_id, invoice.journal_id, (array_agg(attr_rel.att_id)) AS attr_rel, (array_agg(attr_value.name)) AS attr_value, (array_agg(attr.id)) AS attr, partner.id FROM account_invoice AS invoice LEFT JOIN account_invoice_line AS line ON line.invoice_id = invoice.id --Product LEFT JOIN product_product AS product ON line.product_id = product.id LEFT JOIN product_template AS template ON template.id = product.product_tmpl_id LEFT JOIN product_attribute_value_product_product_rel AS attr_rel ON attr_rel.prod_id = product.id LEFT JOIN product_attribute_value AS attr_value ON attr_value.id = attr_rel.att_id LEFT JOIN product_attribute AS attr ON attr.id = attr_value.attribute_id LEFT JOIN res_store_journal_rel AS journal ON journal.journal_id = invoice.journal_id LEFT JOIN product_price_history AS history ON history.product_template_id = product.product_tmpl_id LEFT JOIN res_currency_rate AS rate ON rate.currency_id = invoice.currency_id LEFT JOIN res_partner AS partner ON partner.id = invoice.partner_id WHERE invoice.state NOT IN ('draft', 'cancel') AND invoice.type = 'out_invoice' GROUP BY invoice.id, line.id, invoice.number, invoice.origin, invoice.date_invoice, product.name_template, line.price_unit, line.quantity, line.price_subtotal, journal.store_id, template.categ_id, product.default_code, partner.name, product.id, invoice.company_id, invoice.journal_id, partner.id ''' r.cr.execute(validate_brand) for j in r.cr.fetchall(): brand = j[0] if brand == True: r.cr.execute(query_with_brand,(tuple([company_currency_rate,company_currency_rate,company_currency_rate,company_currency_rate]))) return [ { 'invoice_id': j[0], 'invoice_line_id': j[1], 'number': j[2], 'origin': j[3], 'date': j[4], 'type': j[5], 'product_name':j[6], 'price_unit':j[7], 'quantity':j[8], 'subtotal':j[9], 'tax': j[10], 'cost': j[11], 'store_id': j[12], 'categ_id': j[13], 'partner_name': j[14], 'product_id': j[15], 'company_id': j[16], 'journal_id': j[17], 'attribute_value_ids': j[18], 'attribute_values': j[19], 'attribute_ids': j[20], 'product_brand_id': j[21], 'brand_name': j[22], 'partner_id': j[23], } for j in r.cr.fetchall() ] else: r.cr.execute(query_without_brand,(tuple([company_currency_rate,company_currency_rate,company_currency_rate,company_currency_rate]))) return [ { 'invoice_id': j[0], 'invoice_line_id': j[1], 'number': j[2], 'origin': j[3], 'date': j[4], 'type': j[5], 'product_name':j[6], 'price_unit':j[7], 'quantity':j[8], 'subtotal':j[9], 'tax': j[10], 'cost': j[11], 'store_id': j[12], 'categ_id': j[13], 'partner_name': j[14], 'product_id': j[15], 'company_id': j[16], 'journal_id': j[17], 'attribute_value_ids': j[18], 'attribute_values': j[19], 'attribute_ids': j[20], 'partner_id': j[21], } for j in r.cr.fetchall() ] def get_account_invoice_line_in_invoice_purchase(): user_store = r.env.user.store_id.id company_currency_rate = r.env.user.company_id.currency_id.rate origin = '%PO%' validate_brand = ''' SELECT EXISTS( SELECT table_name FROM information_schema.columns WHERE table_schema='public' AND table_name='product_brand') ''' query_with_brand = ''' SELECT invoice.id AS invoice_id, line.id AS invoice_line_id, invoice.number, invoice.origin, invoice.supplier_invoice_number, invoice.date_invoice, invoice.type, CASE WHEN product.default_code IS NOT NULL THEN ('[' || product.default_code || '] ' || product.name_template) ELSE product.name_template END AS display_name, line.price_unit * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1]) as price_unit, line.quantity AS cant, line.price_subtotal * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1]) AS subtotal, (line.quantity * (line.price_unit * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1]))) - (line.price_subtotal * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1])) AS impuestos, (array_agg(history.cost ORDER BY history.id DESC))[1] AS cost, --line.name journal.store_id, template.categ_id, partner.ruc, partner.name, product.id, invoice.company_id, invoice.journal_id, (array_agg(attr_rel.att_id)) AS attr_rel, (array_agg(attr_value.name)) AS attr_value, (array_agg(attr.id)) AS attr, template.product_brand_id, brand.name FROM account_invoice AS invoice LEFT JOIN account_invoice_line AS line ON line.invoice_id = invoice.id --Product LEFT JOIN product_product AS product ON line.product_id = product.id LEFT JOIN product_template AS template ON template.id = product.product_tmpl_id LEFT JOIN product_attribute_value_product_product_rel AS attr_rel ON attr_rel.prod_id = product.id LEFT JOIN product_attribute_value AS attr_value ON attr_value.id = attr_rel.att_id LEFT JOIN product_attribute AS attr ON attr.id = attr_value.attribute_id LEFT JOIN res_store_journal_rel AS journal ON journal.journal_id = invoice.journal_id LEFT JOIN product_price_history AS history ON history.product_template_id = product.product_tmpl_id LEFT JOIN res_currency_rate AS rate ON rate.currency_id = invoice.currency_id LEFT JOIN res_partner AS partner ON partner.id = invoice.partner_id LEFT JOIN product_brand AS brand ON brand.id = template.product_brand_id WHERE invoice.state NOT IN ('draft', 'cancel') AND invoice.type = 'in_invoice' AND invoice.origin LIKE %s GROUP BY invoice.id, line.id, invoice.number, invoice.origin, invoice.supplier_invoice_number, invoice.date_invoice, product.name_template, line.price_unit, line.quantity, line.price_subtotal, journal.store_id, template.categ_id, product.default_code, partner.ruc, partner.name, product.id, invoice.company_id, invoice.journal_id, template.product_brand_id, brand.name ''' query_without_brand = ''' SELECT invoice.id AS invoice_id, line.id AS invoice_line_id, invoice.number, invoice.origin, invoice.supplier_invoice_number, invoice.date_invoice, invoice.type, CASE WHEN product.default_code IS NOT NULL THEN ('[' || product.default_code || '] ' || product.name_template) ELSE product.name_template END AS display_name, line.price_unit * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1]) as price_unit, line.quantity AS cant, line.price_subtotal * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1]) AS subtotal, (line.quantity * (line.price_unit * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1]))) - (line.price_subtotal * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1])) AS impuestos, (array_agg(history.cost ORDER BY history.id DESC))[1] AS cost, --line.name journal.store_id, template.categ_id, partner.ruc, partner.name, product.id, invoice.company_id, invoice.journal_id, (array_agg(attr_rel.att_id)) AS attr_rel, (array_agg(attr_value.name)) AS attr_value, (array_agg(attr.id)) AS attr FROM account_invoice AS invoice LEFT JOIN account_invoice_line AS line ON line.invoice_id = invoice.id --Product LEFT JOIN product_product AS product ON line.product_id = product.id LEFT JOIN product_template AS template ON template.id = product.product_tmpl_id LEFT JOIN product_attribute_value_product_product_rel AS attr_rel ON attr_rel.prod_id = product.id LEFT JOIN product_attribute_value AS attr_value ON attr_value.id = attr_rel.att_id LEFT JOIN product_attribute AS attr ON attr.id = attr_value.attribute_id LEFT JOIN res_store_journal_rel AS journal ON journal.journal_id = invoice.journal_id LEFT JOIN product_price_history AS history ON history.product_template_id = product.product_tmpl_id LEFT JOIN res_currency_rate AS rate ON rate.currency_id = invoice.currency_id LEFT JOIN res_partner AS partner ON partner.id = invoice.partner_id WHERE invoice.state NOT IN ('draft', 'cancel') AND invoice.type = 'in_invoice' AND invoice.origin LIKE %s GROUP BY invoice.id, line.id, invoice.number, invoice.origin, invoice.supplier_invoice_number, invoice.date_invoice, product.name_template, line.price_unit, line.quantity, line.price_subtotal, journal.store_id, template.categ_id, product.default_code, partner.ruc, partner.name, product.id, invoice.company_id, invoice.journal_id ''' r.cr.execute(validate_brand) for j in r.cr.fetchall(): brand = j[0] if brand == True: r.cr.execute(query_with_brand,([company_currency_rate,company_currency_rate,company_currency_rate,company_currency_rate,origin])) return [ { 'invoice_id': j[0], 'invoice_line_id': j[1], 'number': j[2], 'origin': j[3], 'supplier_invoice_number': j[4], 'date': j[5], 'type': j[6], 'product_name':j[7], 'price_unit':j[8], 'quantity':j[9], 'subtotal':j[10], 'tax': j[11], 'cost': j[12], 'store_id': j[13], 'categ_id': j[14], 'partner_ruc': j[15], 'partner_name': j[16], 'product_id': j[17], 'company_id': j[18], 'journal_id': j[19], 'attribute_value_ids': j[20], 'attribute_values': j[21], 'attribute_ids': j[22], 'product_brand_id': j[23], 'brand_name': j[24], } for j in r.cr.fetchall() ] else: r.cr.execute(query_without_brand,([company_currency_rate,company_currency_rate,company_currency_rate,company_currency_rate,origin])) return [ { 'invoice_id': j[0], 'invoice_line_id': j[1], 'number': j[2], 'origin': j[3], 'supplier_invoice_number': j[4], 'date': j[5], 'type': j[6], 'product_name':j[7], 'price_unit':j[8], 'quantity':j[9], 'subtotal':j[10], 'tax': j[11], 'cost': j[12], 'store_id': j[13], 'categ_id': j[14], 'partner_ruc': j[15], 'partner_name': j[16], 'product_id': j[17], 'company_id': j[18], 'journal_id': j[19], 'attribute_value_ids': j[20], 'attribute_values': j[21], 'attribute_ids': j[22], } for j in r.cr.fetchall() ] def get_account_invoice_line_in_invoice_expense(): user_store = r.env.user.store_id.id company_currency_rate = r.env.user.company_id.currency_id.rate origin = '%PO%' validate_brand = ''' SELECT EXISTS( SELECT table_name FROM information_schema.columns WHERE table_schema='public' AND table_name='product_brand') ''' query_with_brand = ''' SELECT invoice.id AS invoice_id, line.id AS invoice_line_id, invoice.number, invoice.origin, invoice.date_invoice, invoice.type, CASE WHEN product.default_code IS NOT NULL THEN ('[' || product.default_code || '] ' || product.name_template) ELSE product.name_template END AS display_name, line.price_unit * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1]) as price_unit, line.quantity AS cant, line.price_subtotal * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1]) AS subtotal, (line.quantity * (line.price_unit * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1]))) - (line.price_subtotal * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1])) AS impuestos, (array_agg(history.cost ORDER BY history.id DESC))[1] AS cost, --line.name journal.store_id, template.categ_id, partner.ruc, partner.name, product.id, invoice.company_id, invoice.journal_id, (array_agg(attr_rel.att_id)) AS attr_rel, (array_agg(attr_value.name)) AS attr_value, (array_agg(attr.id)) AS attr, template.product_brand_id, brand.name FROM account_invoice AS invoice LEFT JOIN account_invoice_line AS line ON line.invoice_id = invoice.id --Product LEFT JOIN product_product AS product ON line.product_id = product.id LEFT JOIN product_template AS template ON template.id = product.product_tmpl_id LEFT JOIN product_attribute_value_product_product_rel AS attr_rel ON attr_rel.prod_id = product.id LEFT JOIN product_attribute_value AS attr_value ON attr_value.id = attr_rel.att_id LEFT JOIN product_attribute AS attr ON attr.id = attr_value.attribute_id LEFT JOIN res_store_journal_rel AS journal ON journal.journal_id = invoice.journal_id LEFT JOIN product_price_history AS history ON history.product_template_id = product.product_tmpl_id LEFT JOIN res_currency_rate AS rate ON rate.currency_id = invoice.currency_id LEFT JOIN res_partner AS partner ON partner.id = invoice.partner_id LEFT JOIN product_brand AS brand ON brand.id = template.product_brand_id WHERE invoice.state NOT IN ('draft', 'cancel') AND invoice.type = 'in_invoice' AND (invoice.origin NOT LIKE %s OR invoice.origin IS NULL) GROUP BY invoice.id, line.id, invoice.number, invoice.origin, invoice.date_invoice, product.name_template, line.price_unit, line.quantity, line.price_subtotal, journal.store_id, template.categ_id, product.default_code, partner.ruc, partner.name, product.id, invoice.company_id, invoice.journal_id, template.product_brand_id, brand.name ''' query_without_brand = ''' SELECT invoice.id AS invoice_id, line.id AS invoice_line_id, invoice.number, invoice.origin, invoice.date_invoice, invoice.type, CASE WHEN product.default_code IS NOT NULL THEN ('[' || product.default_code || '] ' || product.name_template) ELSE product.name_template END AS display_name, line.price_unit * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1]) as price_unit, line.quantity AS cant, line.price_subtotal * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1]) AS subtotal, (line.quantity * (line.price_unit * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1]))) - (line.price_subtotal * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1])) AS impuestos, (array_agg(history.cost ORDER BY history.id DESC))[1] AS cost, --line.name journal.store_id, template.categ_id, partner.ruc, partner.name, product.id, invoice.company_id, invoice.journal_id, (array_agg(attr_rel.att_id)) AS attr_rel, (array_agg(attr_value.name)) AS attr_value, (array_agg(attr.id)) AS attr FROM account_invoice AS invoice LEFT JOIN account_invoice_line AS line ON line.invoice_id = invoice.id --Product LEFT JOIN product_product AS product ON line.product_id = product.id LEFT JOIN product_template AS template ON template.id = product.product_tmpl_id LEFT JOIN product_attribute_value_product_product_rel AS attr_rel ON attr_rel.prod_id = product.id LEFT JOIN product_attribute_value AS attr_value ON attr_value.id = attr_rel.att_id LEFT JOIN product_attribute AS attr ON attr.id = attr_value.attribute_id LEFT JOIN res_store_journal_rel AS journal ON journal.journal_id = invoice.journal_id LEFT JOIN product_price_history AS history ON history.product_template_id = product.product_tmpl_id LEFT JOIN res_currency_rate AS rate ON rate.currency_id = invoice.currency_id LEFT JOIN res_partner AS partner ON partner.id = invoice.partner_id WHERE invoice.state NOT IN ('draft', 'cancel') AND invoice.type = 'in_invoice' AND (invoice.origin NOT LIKE %s OR invoice.origin IS NULL) GROUP BY invoice.id, line.id, invoice.number, invoice.origin, invoice.date_invoice, product.name_template, line.price_unit, line.quantity, line.price_subtotal, journal.store_id, template.categ_id, product.default_code, partner.ruc, partner.name, product.id, invoice.company_id, invoice.journal_id ''' r.cr.execute(validate_brand) for j in r.cr.fetchall(): brand = j[0] if brand == True: r.cr.execute(query_with_brand,([company_currency_rate,company_currency_rate,company_currency_rate,company_currency_rate,origin])) return [ { 'invoice_id': j[0], 'invoice_line_id': j[1], 'number': j[2], 'origin': j[3], 'date': j[4], 'type': j[5], 'product_name':j[6], 'price_unit':j[7], 'quantity':j[8], 'subtotal':j[9], 'tax': j[10], 'cost': j[11], 'store_id': j[12], 'categ_id': j[13], 'partner_ruc': j[14], 'partner_name': j[15], 'product_id': j[16], 'company_id': j[17], 'journal_id': j[18], 'attribute_value_ids': j[19], 'attribute_values': j[20], 'attribute_ids': j[21], 'product_brand_id': j[22], 'brand_name': j[23], } for j in r.cr.fetchall() ] else: r.cr.execute(query_without_brand,([company_currency_rate,company_currency_rate,company_currency_rate,company_currency_rate,origin])) return [ { 'invoice_id': j[0], 'invoice_line_id': j[1], 'number': j[2], 'origin': j[3], 'date': j[4], 'type': j[5], 'product_name':j[6], 'price_unit':j[7], 'quantity':j[8], 'subtotal':j[9], 'tax': j[10], 'cost': j[11], 'store_id': j[12], 'categ_id': j[13], 'partner_ruc': j[14], 'partner_name': j[15], 'product_id': j[16], 'company_id': j[17], 'journal_id': j[18], 'attribute_value_ids': j[19], 'attribute_values': j[20], 'attribute_ids': j[21], } for j in r.cr.fetchall() ]