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

def get_account_bank_statement_widget():
    user_store = r.env.user.store_id.id
    company_currency_rate = r.env.user.company_id.currency_id.rate
    query = '''
        SELECT
        	statement.id,
        	statement.name,
        	journal.name,
        	journal.type,
        	journal.currency,
        	statement.balance_end,
        	CASE
        		WHEN journal.currency IS NULL
        		THEN statement.balance_end
        		ELSE statement.balance_end * (%s / (array_agg(rate.rate ORDER BY rate.name DESC))[1])
        		END AS balance_end_currency,
            currency.symbol,
            currency.decimal_places,
            currency.thousands_separator,
            currency.decimal_separator
        FROM account_bank_statement AS statement
        LEFT JOIN res_store_journal_rel AS journal_rel
        ON statement.journal_id = journal_rel.journal_id
        LEFT JOIN account_journal AS journal
        ON statement.journal_id = journal.id
        LEFT JOIN res_currency_rate AS rate
        ON rate.currency_id = journal.currency
        LEFT JOIN res_currency AS currency
        ON currency.id = journal.currency
        WHERE statement.state NOT IN ('confirm')
        AND journal_rel.store_id = ''' + str(user_store) + '''
        GROUP BY
        	statement.id,
        	statement.name,
        	journal.name,
        	journal.type,
        	journal.currency,
        	statement.balance_end,
            currency.symbol,
            currency.decimal_places,
            currency.thousands_separator,
            currency.decimal_separator
    '''

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

    return [
        {
            'id': j[0],
            'name': j[1],
            'journal_name': j[2],
            'journal_type': j[3],
            'journal_currency': j[4],
            'balance_end': j[5],
            'balance_end_currency': j[6],
            'symbol': j[7],
            'decimal_places': j[8],
            'thousands_separator': j[9],
            'decimal_separator': j[10],
        } for j in r.cr.fetchall()
    ]