eiru_reports/controller/helpers/account_invoice_line.py

# -*- 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,
            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.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,
            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.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,
            line.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,
            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.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 = '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,
            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,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],
                '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,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],
                'name': j[23],
            } 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,
            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.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,
            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.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.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 = '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,
            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,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],
                '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],
                '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],
                'name': j[22],
            } for j in r.cr.fetchall()
        ]