eiru_reports_sales/controller/helpers/product_product.py

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

def get_product_product():
    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
        	product.id,
        	template.categ_id,
        	CASE
        		WHEN product.default_code IS NOT NULL
        		THEN ('[' || product.default_code || '] ' || product.name_template)
        		ELSE product.name_template
        		END AS display_name,
        	(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.product_tmpl_id,
            template.list_price,
            product.default_code,
            product.active,
            template.sale_ok,
            template.company_id,
            template.store_id,
            template.type,
            product.ean13,
            (array_agg(history.cost ORDER BY history.id DESC))[1] AS standard_price
        FROM product_product AS product
        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_brand AS brand
        ON brand.id = template.product_brand_id
        LEFT JOIN product_price_history AS history
        ON history.product_template_id = product.product_tmpl_id
        GROUP BY
        	product.id,
        	template.categ_id,
        	product.default_code,
        	template.product_brand_id,
        	brand.name,
            template.list_price,
            template.sale_ok,
            template.company_id,
            template.store_id,
            template.type
    '''

    query_without_brand = '''
        SELECT
        	product.id,
        	template.categ_id,
        	CASE
        		WHEN product.default_code IS NOT NULL
        		THEN ('[' || product.default_code || '] ' || product.name_template)
        		ELSE product.name_template
        		END AS display_name,
        	(array_agg(attr_rel.att_id)) AS attr_rel,
        	(array_agg(attr_value.name)) AS attr_value,
        	(array_agg(attr.id)) AS attr,
            product.product_tmpl_id,
            template.list_price,
            product.default_code,
            product.active,
            template.sale_ok,
            template.company_id,
            template.store_id,
            template.type,
            product.ean13,
            (array_agg(history.cost ORDER BY history.id DESC))[1] AS standard_price
        FROM product_product AS product
        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
        GROUP BY
        	product.id,
        	template.categ_id,
        	product.default_code,
            template.list_price,
            template.sale_ok,
            template.company_id,
            template.store_id,
            template.type
    '''

    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]))
        return [
            {
                'product_id': j[0],
                'categ_id': j[1],
                'product_name': j[2],
                'attribute_value_ids':j[3],
                'attribute_values':j[4],
                'attribute':j[5],
                'product_brand_id':j[6],
                'brand_name': j[7],
                'product_tmpl_id': j[8],
                'list_price': j[9],
                'default_code': j[10],
                'active': j[11],
                'sale_ok': j[12],
                'company_id': j[13],
                'store_id': j[14],
                'type': j[15],
                'ean13': j[16],
                'standard_price': j[17],
            } for j in r.cr.fetchall()
        ]
    else:
        r.cr.execute(query_without_brand,([company_currency_rate]))
        return [
            {
                'product_id': j[0],
                'categ_id': j[1],
                'product_name': j[2],
                'attribute_value_ids':j[3],
                'attribute_values':j[4],
                'attribute':j[5],
                'product_tmpl_id': j[6],
                'list_price': j[7],
                'default_code': j[8],
                'active': j[9],
                'sale_ok': j[10],
                'company_id': j[11],
                'store_id': j[12],
                'type': j[13],
                'ean13': j[14],
                'standard_price': j[15],
            } for j in r.cr.fetchall()
        ]