eiru_reports_sales/controller/helpers/account_voucher.py

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

def get_account_voucher_all():
    company_currency_rate = r.env.user.company_id.currency_id.rate
    query = '''
    SELECT  voucher.id,
        	voucher.number,
        	voucher.type,
        	voucher.date,
        	journal.currency,
            voucher.amount,
        	CASE
        		WHEN journal.currency IS NULL
        		THEN voucher.amount
        		ELSE voucher.amount * (%s / (array_agg(rate.rate ORDER BY rate.name DESC))[1])
        		END AS amount_currency,
            voucher.journal_id,
            voucher.reference
        FROM account_voucher AS voucher
        LEFT JOIN res_store_journal_rel AS journal_rel
        ON voucher.journal_id = journal_rel.journal_id
        LEFT JOIN account_journal AS journal
        ON journal.id = voucher.journal_id
        LEFT JOIN res_currency_rate AS rate
        ON rate.currency_id = journal.currency
        WHERE voucher.state = 'posted'
        GROUP BY
        	voucher.id,
        	voucher.number,
        	voucher.type,
        	voucher.date,
        	voucher.amount,
        	journal.name,
        	journal.currency
    '''

    r.cr.execute(query,(tuple([company_currency_rate])))

    return [
        {
            'id': j[0],
            'number': j[1],
            'type': j[2],
            'date': j[3],
            'currency_id': j[4],
            'amount': j[5],
            'amount_currency': j[6],
            'journal_id': j[7],
            'reference': j[8],
        } for j in r.cr.fetchall()
    ]

def get_account_voucher_customer():
    company_currency_rate = r.env.user.company_id.currency_id.rate
    query = '''
    SELECT  voucher.id,
        	voucher.number,
        	voucher.type,
        	voucher.date,
        	journal.currency,
            voucher.amount,
        	CASE
        		WHEN journal.currency IS NULL
        		THEN voucher.amount
        		ELSE voucher.amount * (%s / (array_agg(rate.rate ORDER BY rate.name DESC))[1])
        		END AS amount_currency,
            voucher.journal_id,
            voucher.reference
        FROM account_voucher AS voucher
        LEFT JOIN res_store_journal_rel AS journal_rel
        ON voucher.journal_id = journal_rel.journal_id
        LEFT JOIN account_journal AS journal
        ON journal.id = voucher.journal_id
        LEFT JOIN res_currency_rate AS rate
        ON rate.currency_id = journal.currency
        WHERE voucher.state = 'posted'
        AND voucher.type = 'receipt'
        GROUP BY
        	voucher.id,
        	voucher.number,
        	voucher.type,
        	voucher.date,
        	voucher.amount,
        	journal.name,
        	journal.currency
    '''

    r.cr.execute(query,(tuple([company_currency_rate])))

    return [
        {
            'id': j[0],
            'number': j[1],
            'type': j[2],
            'date': j[3],
            'currency_id': j[4],
            'amount': j[5],
            'amount_currency': j[6],
            'journal_id': j[7],
            'reference': j[8],
        } for j in r.cr.fetchall()
    ]

def get_account_voucher_supplier():
    company_currency_rate = r.env.user.company_id.currency_id.rate
    query = '''
    SELECT  voucher.id,
        	voucher.number,
        	voucher.type,
        	voucher.date,
        	journal.currency,
            voucher.amount,
        	CASE
        		WHEN journal.currency IS NULL
        		THEN voucher.amount
        		ELSE voucher.amount * (%s / (array_agg(rate.rate ORDER BY rate.name DESC))[1])
        		END AS amount_currency,
            voucher.journal_id,
            voucher.reference
        FROM account_voucher AS voucher
        LEFT JOIN res_store_journal_rel AS journal_rel
        ON voucher.journal_id = journal_rel.journal_id
        LEFT JOIN account_journal AS journal
        ON journal.id = voucher.journal_id
        LEFT JOIN res_currency_rate AS rate
        ON rate.currency_id = journal.currency
        WHERE voucher.state = 'posted'
        AND voucher.type = 'payment'
        GROUP BY
        	voucher.id,
        	voucher.number,
        	voucher.type,
        	voucher.date,
        	voucher.amount,
        	journal.name,
        	journal.currency
    '''

    r.cr.execute(query,(tuple([company_currency_rate])))

    return [
        {
            'id': j[0],
            'number': j[1],
            'type': j[2],
            'date': j[3],
            'currency_id': j[4],
            'amount': j[5],
            'amount_currency': j[6],
            'journal_id': j[7],
            'reference': j[8],
        } for j in r.cr.fetchall()
    ]