eiru_reports/controller/helpers/pos_order_line.py

# -*- coding: utf-8 -*-
from openerp.http import request as r

def get_pos_order_line():
    user_store = r.env.user.store_id.id
    company_currency_rate = r.env.user.company_id.currency_id.rate

    validate = '''
        SELECT EXISTS(
            SELECT table_name
            FROM information_schema.columns
            WHERE table_schema='public'
                AND table_name='pos_order')
    '''

    validate_brand = '''
        SELECT EXISTS(
            SELECT table_name
            FROM information_schema.columns
            WHERE table_schema='public'
                AND table_name='product_brand')
    '''

    query_with_brand = '''
        SELECT
        	pos.id,
        	line.id,
        	pos.name,
        	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,
        	line.qty,
        	line.price_subtotal,
        	line.price_subtotal_incl - line.price_subtotal AS impuestos,
        	(array_agg(history.cost ORDER BY history.id DESC))[1] AS cost,
            history.product_template_id,
            journal.store_id,
            pos.date_order,
            pos.company_id,
            pos.sale_journal,
            template.categ_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,
            product.id,
            customer.ruc,
            customer.name,
            customer.id,
            line.discount
        FROM pos_order AS pos
        LEFT JOIN pos_order_line AS line
        ON pos.id = line.order_id
        LEFT JOIN res_store_journal_rel as journal
        ON journal.journal_id = pos.sale_journal
        LEFT JOIN product_product as product
        ON product.id = line.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 product_price_history AS history
        ON history.product_template_id = product.product_tmpl_id
        LEFT JOIN product_brand AS brand
        ON brand.id = template.product_brand_id
        LEFT JOIN res_partner AS customer
        ON customer.id = pos.partner_id
        WHERE pos.state NOT IN ('draft')
        GROUP BY
        	pos.id,
        	line.id,
        	pos.name,
        	product.name_template,
        	line.price_unit,
        	line.qty,
        	line.price_subtotal,
            history.product_template_id,
            journal.store_id,
            pos.date_order,
            pos.company_id,
            pos.sale_journal,
            template.categ_id,
            product.default_code,
            template.product_brand_id,
            brand.name,
            product.id,
            customer.ruc,
            customer.name,
            customer.id
    '''

    query_without_brand = '''
        SELECT
        	pos.id,
        	line.id,
        	pos.name,
        	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,
        	line.qty,
        	line.price_subtotal,
        	line.price_subtotal_incl - line.price_subtotal AS impuestos,
        	(array_agg(history.cost ORDER BY history.id DESC))[1] AS cost,
            history.product_template_id,
            journal.store_id,
            pos.date_order,
            pos.company_id,
            pos.sale_journal,
            template.categ_id,
            (array_agg(attr_rel.att_id)) AS attr_rel,
            (array_agg(attr_value.name)) AS attr_value,
            (array_agg(attr.id)) AS attr,
            product.id,
            customer.ruc,
            customer.name,
            customer.id,
            line.discount
        FROM pos_order AS pos
        LEFT JOIN pos_order_line AS line
        ON pos.id = line.order_id
        LEFT JOIN res_store_journal_rel as journal
        ON journal.journal_id = pos.sale_journal
        LEFT JOIN product_product as product
        ON product.id = line.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 product_price_history AS history
        ON history.product_template_id = product.product_tmpl_id
        LEFT JOIN res_partner AS customer
        ON customer.id = pos.partner_id
        WHERE pos.state NOT IN ('draft')
        GROUP BY
        	pos.id,
        	line.id,
        	pos.name,
        	product.name_template,
        	line.price_unit,
        	line.qty,
        	line.price_subtotal,
            history.product_template_id,
            journal.store_id,
            pos.date_order,
            pos.company_id,
            pos.sale_journal,
            template.categ_id,
            product.default_code,
            product.id,
            customer.ruc,
            customer.name,
            customer.id
    '''

    r.cr.execute(validate)

    for j in r.cr.fetchall():
        band = j[0]

    if band == True:
        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])))
            return [
                {
                    'order_id': j[0],
                    'order_line_id': j[1],
                    'name': j[2],
                    'product_name':j[3],
                    'price_unit':j[4],
                    'quantity':j[5],
                    'subtotal':j[6],
                    'tax': j[7],
                    'cost': j[8],
                    'template_id': j[9],
                    'store_id': j[10],
                    'date': j[11],
                    'company_id': j[12],
                    'journal_id': j[13],
                    'categ_id': j[14],
                    'attribute_value_ids': j[15],
                    'attribute_values': j[16],
                    'attribute_ids': j[17],
                    'product_brand_id': j[18],
                    'brand_name': j[19],
                    'product_id': j[20],
                    'customer_ruc': j[21],
                    'customer_name': j[22],
                    'customer_id': j[23],
                    'discount': j[24],
                } for j in r.cr.fetchall()
            ]
        else:
            r.cr.execute(query_without_brand,(tuple([company_currency_rate,company_currency_rate])))
            return [
                {
                    'order_id': j[0],
                    'order_line_id': j[1],
                    'name': j[2],
                    'product_name':j[3],
                    'price_unit':j[4],
                    'quantity':j[5],
                    'subtotal':j[6],
                    'tax': j[7],
                    'cost': j[8],
                    'template_id': j[9],
                    'store_id': j[10],
                    'date': j[11],
                    'company_id': j[12],
                    'journal_id': j[13],
                    'categ_id': j[14],
                    'attribute_value_ids': j[15],
                    'attribute_values': j[16],
                    'attribute_ids': j[17],
                    'product_id': j[18],
                    'customer_ruc': j[19],
                    'customer_name': j[20],
                    'customer_id': j[21],
                    'discount': j[22],
                } for j in r.cr.fetchall()
            ]
    else:
        return []