eiru_reports_dashboard/controllers/helpers/account_move_line.py

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

def get_account_move_line_widget():
    user_store = r.env.user.store_id.id
    company_currency_rate = r.env.user.company_id.currency_id.rate
    query = '''
        SELECT
            invoice.number,
            line.date_maturity,
            partner.name,
            partner.ruc,
            line.credit,
            line.debit,
            invoice.id,
            line.reconcile_ref,
            line.reconcile_id,
            line.reconcile_partial_id,
            invoice.amount_total AS invoice_amount,
            CASE
        		WHEN invoice.currency_id IS NULL
        		THEN invoice.amount_total
        		ELSE invoice.amount_total * (%s / (array_agg(rate.rate ORDER BY rate.name DESC))[1])
        		END AS invoice_amount_currency
        -- INVOICE
        FROM account_invoice AS invoice
        -- JOURNAL/STORE
        LEFT JOIN res_store_journal_rel AS journal_rel
        ON journal_rel.journal_id = invoice.journal_id
        -- JOURNAL
        LEFT JOIN account_journal AS journal
        ON journal.id = invoice.journal_id
	-- MOVE
        LEFT JOIN account_move AS move
        ON move.name = invoice.number
	-- MOVE LINE
        LEFT JOIN account_move_line AS line
        ON line.move_id = move.id AND line.date_maturity IS NOT NULL
        -- PARTNER
        LEFT JOIN res_partner AS partner
        ON line.partner_id = partner.id
        -- RATE
        LEFT JOIN res_currency_rate AS rate
        ON rate.currency_id = invoice.currency_id
        --WHERE
        WHERE invoice.state = 'open'
        AND journal_rel.store_id = ''' + str(user_store) + '''
        --AND invoice.id = 20940
        GROUP BY    invoice.number,
                    line.date_maturity,
                    partner.name,
                    partner.ruc,
                    line.credit,
                    line.debit,
                    invoice.id,
                    line.reconcile_ref,
                    line.reconcile_id,
                    line.reconcile_partial_id,
                    invoice.amount_total,
                    journal.currency,
                    journal.name

    '''

    r.cr.execute(query,(tuple([company_currency_rate])))
    return [
        {
            'number': j[0],
            'date_maturity': j[1],
            'partner': j[2],
            'ruc': j[3],
            'credit': j[4],
            'debit': j[5],
            'invoice_id': j[6],
            'reconcile_ref': j[7],
            'reconcile_id': j[8],
            'reconcile_partial_id': j[9],
            'amount_total': j[10],
            'amount_total_currency': j[11],
        } for j in r.cr.fetchall()
    ]

def get_account_move_line_partial_reconcile():
    query = '''
        SELECT
        	journal.id,
        	line.reconcile_partial_id,
            line.credit,
            line.debit
        FROM account_journal AS journal
        LEFT JOIN account_move_line AS line
        ON line.journal_id = journal.id
        WHERE journal.type in ('bank','cash')
        AND line.reconcile_partial_id IS NOT NULL
    '''

    r.cr.execute(query)

    return [
        {
            'journal_id': j[0],
            'reconcile_partial_id': j[1],
            'credit': j[2],
            'debit': j[3],
        } for j in r.cr.fetchall()
    ]