eiru_reports_dashboard/controllers/helpers/account_bank_statement_line.py

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

def get_account_bank_statement_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 column_name
            FROM information_schema.columns
            WHERE table_schema='public'
                AND table_name='account_bank_statement_line'
                AND column_name='pos_statement_id')
    '''

    query = '''
        SELECT
        	line.id,
        	line.journal_id,
        	journal.currency,
            line.amount,
        	CASE
        		WHEN journal.currency IS NULL
        		THEN line.amount
        		ELSE line.amount * (%s / (array_agg(rate.rate ORDER BY rate.name DESC))[1])
        		END AS amount_currency
        FROM account_bank_statement_line AS line
        LEFT JOIN account_bank_statement AS statement
        ON statement.id = line.statement_id
        LEFT JOIN res_store_journal_rel AS journal_rel
        ON journal_rel.journal_id = statement.journal_id
        LEFT JOIN account_journal AS journal
        ON journal.id = statement.journal_id
        LEFT JOIN res_currency_rate AS rate
        ON rate.currency_id = journal.currency
        WHERE TO_CHAR(line.date,'YYYY-MM') = TO_CHAR(current_date,'YYYY-MM')
        AND line.pos_statement_id IS NOT NULL
        AND journal_rel.store_id = ''' + str(user_store) + '''
        GROUP BY
        	line.id,
        	line.amount,
        	line.journal_id,
        	journal.currency
    '''

    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 [
            {
                'id': j[0],
                'journal_id': j[1],
                'currency_id': j[2],
                'amount': j[3],
                'amount_currency': j[4],
            } for j in r.cr.fetchall()
        ]
    else:
        return []