eiru_reports/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,
            journal_rel.store_id,
            journal.name,
            invoice.origin,
            partner.name,
            voucher.name,
            voucher.company_id,
            move.id
            --,
        	--move_line.reconcile_ref
        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
        LEFT JOIN account_invoice AS invoice
        ON invoice.number = voucher.reference
        LEFT JOIN res_partner AS partner
        ON partner.id = voucher.partner_id
        LEFT JOIN account_move AS move
        ON move.name = voucher.number
        --LEFT JOIN account_move_line AS move_line
        --on move_line.move_id = move.id
        WHERE voucher.state = 'posted'
        AND voucher.type = 'receipt'
        --AND move_line.reconcile_ref IS NOT NULL
        GROUP BY
        	voucher.id,
        	voucher.number,
        	voucher.type,
        	voucher.date,
        	voucher.amount,
        	journal.name,
        	journal.currency,
            journal_rel.store_id,
            invoice.origin,
            partner.name,
            move.id
            --,
            --move_line.reconcile_ref
    '''

    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],
            'store_id': j[9],
            'journal_name': j[10],
            'origin': j[11],
            'partner_name': j[12],
            'name': j[13],
            'company_id': j[14],
            'move_id': j[15],
            # 'reconcile_ref': j[16],
        } 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,
            journal_rel.store_id,
            journal.name,
            invoice.origin,
            partner.name,
            voucher.name,
            voucher.company_id
        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
        LEFT JOIN account_invoice AS invoice
        ON invoice.number = voucher.reference
        LEFT JOIN res_partner AS partner
        ON partner.id = voucher.partner_id
        WHERE voucher.state = 'posted'
        AND voucher.type = 'payment'
        GROUP BY
        	voucher.id,
        	voucher.number,
        	voucher.type,
        	voucher.date,
        	voucher.amount,
        	journal.name,
        	journal.currency,
            journal_rel.store_id,
            invoice.origin,
            partner.name
    '''

    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],
            'store_id': j[9],
            'journal_name': j[10],
            'origin': j[11],
            'partner_name': j[12],
            'name': j[13],
            'company_id': j[14],
        } for j in r.cr.fetchall()
    ]


def get_account_voucher_payment():
    company_currency_rate = r.env.user.company_id.currency_id.rate
    query = '''
    SELECT
        voucher.id,
        voucher.number,
        voucher.type,
        voucher.date,
        journal.currency,
        voucher_line.amount_original,
        voucher_line.amount,
        CASE
            WHEN journal.currency IS NULL
        	THEN voucher_line.amount
        	ELSE voucher_line.amount * (%s / (array_agg(rate.rate ORDER BY rate.name DESC))[1])
        	END AS amount_currency,
        voucher.journal_id,
        voucher.reference,
        journal_rel.store_id,
        journal.name,
        partner.name,
        invoice.number,
        invoice.residual,
        voucher.company_id,
	    move_line.date_maturity,
        voucher.name,
        move_line.reconcile_ref
    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
    LEFT JOIN account_invoice AS invoice
    ON invoice.number = voucher.reference
    LEFT JOIN res_partner AS partner
    ON partner.id = voucher.partner_id
	LEFT JOIN account_voucher_line AS voucher_line
	ON voucher_line.voucher_id = voucher.id
    LEFT JOIN account_move_line AS move_line
    ON move_line.id = voucher_line.move_line_id
    WHERE voucher.state = 'posted'
    AND voucher.type = 'receipt'
    AND move_line.reconcile_ref IS NOT NULL
    GROUP BY
        voucher.id,
        voucher.number,
        voucher.type,
        voucher.date,
        voucher_line.amount_original,
        voucher_line.amount,
        journal.name,
        journal.currency,
		journal_rel.store_id,
		invoice.origin,
		invoice.number,
        invoice.residual,
        partner.name,
	    move_line.date_maturity,
        voucher.name,
        move_line.reconcile_ref

    '''
    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_original': j[5],
            'amount': j[6],
            'amount_currency': j[7],
            'journal_id': j[8],
            'reference': j[9],
            'store_id': j[10],
            'journal_name': j[11],
            'partner_name': j[12],
            'invoice_number': j[13],
            'residual': j[14],
            'company_id': j[15],
            'date_maturity': j[16],
            'name': j[17],
            'reconcile_ref': j[18],

        } for j in r.cr.fetchall()
    ]