eiru_reports_dashboard/controllers/helpers/pos_order_line.py

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

def get_pos_order_line_widget():
    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')
    '''

    query = '''
        SELECT
        	pos.id,
        	line.id,
        	pos.name,
        	product.name_template,
        	line.price_unit,
        	line.qty,
        	line.price_subtotal,
        	(line.price_unit * line.qty) - line.price_subtotal AS impuestos,
        	(array_agg(history.cost ORDER BY history.id DESC))[1] AS cost,
            history.product_template_id,
            pos.date_order
        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_price_history AS history
        ON history.product_template_id = product.product_tmpl_id
        WHERE TO_CHAR(pos.date_order,'YYYY-MM') = TO_CHAR(current_date,'YYYY-MM')
        AND journal.store_id = ''' + str(user_store) + '''
        AND 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,
            pos.date_order
    '''

    r.cr.execute(validate)

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

    if band == True:
        r.cr.execute(query,(tuple([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],
                'history_id': j[9],
                'date': j[10],
            } for j in r.cr.fetchall()
        ]
    else:
        return []