eiru_dashboard_salesman_objective/controllers/helpers/account_invoice.py

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

def get_account_invoice_widget():
    user_store = r.env.user.store_id.id
    user_id = r.env.user.id
    company_currency_rate = r.env.user.company_id.currency_id.rate
    query = '''
        SELECT
        	invoice.id,
        	rate.currency_id,
        	invoice.date_invoice,
        	invoice.type,
        	invoice.origin,
        	invoice.partner_id,
        	invoice.user_id,
        	invoice.amount_total * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1]) as invoice_rate
        FROM account_invoice AS invoice
        LEFT JOIN res_store_journal_rel AS journal
        ON journal.journal_id = invoice.journal_id
        LEFT JOIN res_currency_rate AS rate
        ON rate.currency_id = invoice.currency_id
        LEFT JOIN res_company AS company
        ON company.id = invoice.company_id
        WHERE invoice.state NOT IN ('draft', 'cancel')
        AND TO_CHAR(invoice.date_invoice,'YYYY-MM') = TO_CHAR(current_date,'YYYY-MM')
        AND journal.store_id = ''' + str(user_store) + ''' AND invoice.user_id = ''' + str(user_id) + '''
        GROUP BY
        	invoice.id,
        	rate.currency_id,
        	invoice.date_invoice,
        	invoice.type,
        	invoice.origin,
        	invoice.amount_total,
        	invoice.partner_id,
        	invoice.user_id,
            invoice.amount_total
    '''

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

    return [
        {
            'invoice_id': j[0],
            'currency_id': j[1],
            'date': j[2],
            'type': j[3],
            'origin': j[4],
            'customer_id':j[5],
            'user_id':j[6],
            'amount':j[7],

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

def get_account_invoice_salesman_ranking():
    user_store = r.env.user.store_id.id
    company_currency_rate = r.env.user.company_id.currency_id.rate
    query = '''
        SELECT
        	invoice.id,
        	rate.currency_id,
        	invoice.date_invoice,
        	invoice.type,
        	invoice.origin,
        	invoice.partner_id,
        	invoice.user_id,
        	invoice.amount_total * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1]) as invoice_rate
        FROM account_invoice AS invoice
        LEFT JOIN res_store_journal_rel AS journal
        ON journal.journal_id = invoice.journal_id
        LEFT JOIN res_currency_rate AS rate
        ON rate.currency_id = invoice.currency_id
        LEFT JOIN res_company AS company
        ON company.id = invoice.company_id
        WHERE invoice.state NOT IN ('draft', 'cancel')
        AND TO_CHAR(invoice.date_invoice,'YYYY-MM') = TO_CHAR(current_date,'YYYY-MM')
        AND journal.store_id = ''' + str(user_store) + '''
        GROUP BY
        	invoice.id,
        	rate.currency_id,
        	invoice.date_invoice,
        	invoice.type,
        	invoice.origin,
        	invoice.amount_total,
        	invoice.partner_id,
        	invoice.user_id,
            invoice.amount_total
    '''

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

    return [
        {
            'invoice_id': j[0],
            'currency_id': j[1],
            'date': j[2],
            'type': j[3],
            'origin': j[4],
            'customer_id':j[5],
            'user_id':j[6],
            'amount':j[7],

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