eiru_reports/controller/helpers/account_bank_statement_line.py

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

def get_account_bank_statement_line():
    user_store = r.env.user.store_id.id

    validate = '''
        SELECT EXISTS(
            SELECT table_name
            FROM information_schema.columns
            WHERE table_schema='public'
                AND table_name='pos_order')
    '''

    query = '''
        SELECT
        	line.id,
        	line.name,
        	statement.journal_id,
        	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,
        	journal_rel.store_id,
        	line.company_id,
        	journal.name,
        	partner.name,
            line.date,
            line.ref
            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 statement.journal_id = journal_rel.journal_id
            LEFT JOIN account_journal AS journal
            ON journal.id = statement.journal_id
            LEFT JOIN res_partner AS partner
            ON partner.id = line.partner_id
            LEFT JOIN res_currency_rate AS rate
            ON rate.currency_id = journal.currency
            WHERE line.pos_statement_id IS NOT NULL
            GROUP BY
        		line.id,
        		statement.journal_id,
        		journal.currency,
        		journal_rel.store_id,
        		journal.name,
        		partner.name
    '''

    r.cr.execute(validate)
    for j in r.cr.fetchall():
        band = j[0]

    if band == True:
        company_currency_rate = r.env.user.company_id.currency_id.rate
        r.cr.execute(query,[company_currency_rate])
        return [
            {
                'id': j[0],
                'name': j[1],
                'journal_id': j[2],
                'amount': j[3],
                'amount_currency': j[4],
                'store_id': j[5],
                'company_id': j[6],
                'journal_name': j[7],
                'partner_name': j[8],
                'date': j[9],
                'origin': j[10],
            } for j in r.cr.fetchall()
        ]
    else:
        return []