eiru_reports/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
        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
        GROUP BY
        	product.id,
        	template.categ_id,
        	product.default_code,
        	template.product_brand_id,
        	brand.name
    '''

    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
        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
        GROUP BY
        	product.id,
        	template.categ_id,
        	product.default_code
    '''

    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]
            } 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],
            } for j in r.cr.fetchall()
        ]