eiru_reports_agroyguazu/controllers/helpers/account_invoice_line.py

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

def get_account_invoice_line():
    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,
            	product.name_template,
            	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] * line.quantity as cost,
                template.categ_id,
                journal.store_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 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 product_template AS template
            ON template.id = product.product_tmpl_id
            WHERE invoice.state NOT IN ('draft', 'cancel')
            AND invoice.type = 'in_invoice'
            AND invoice.origin IS NULL
            --AND TO_CHAR(invoice.date_invoice,'YYYY-MM') = TO_CHAR(current_date,'YYYY-MM')
            --AND journal.store_id = ''' + str(user_store) + '''
        GROUP BY
        	invoice.id,
        	line.id,
        	invoice.number,
        	invoice.origin,
        	invoice.date_invoice,
        	product.name_template,
        	line.price_unit,
        	line.quantity,
        	line.price_subtotal,
            template.categ_id,
            journal.store_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],
            'categ_id': j[12],
            'store_id': j[13],
        } for j in r.cr.fetchall()
    ]