eiru_reports/controller/helpers/account_invoice.py

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

def get_account_invoice_all_type():
    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,
            invoice.number,
            partner.name,
            customer.name,
            invoice.amount_tax,
            invoice.state,
            journal.store_id,
            invoice.journal_id,
            invoice.state,
            customer.ruc,
            invoice.supplier_invoice_number
        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
        LEFT JOIN res_users AS users
        ON users.id = invoice.user_id
        LEFT JOIN res_partner AS partner
        ON partner.id = users.partner_id
        LEFT JOIN res_partner AS customer
        ON customer.id = invoice.partner_id
        WHERE invoice.state NOT IN ('draft', 'cancel')
        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,
            partner.name,
            customer.name,
            invoice.amount_tax,
            invoice.state,
            journal.store_id,
            invoice.journal_id,
            invoice.state,
            customer.ruc,
            invoice.supplier_invoice_number
    '''

    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],
            'number':j[8],
            'user_name':j[9],
            'customer_name':j[10],
            'amount_tax':j[11],
            'state':j[12],
            'store_id':j[13],
            'journal_id':j[14],
            'state':j[15],
            'customer_ruc':j[16],
            'supplier_invoice_number':j[17],
        } for j in r.cr.fetchall()
    ]

def get_account_invoice_sale_type(): #historico de venta
    company_currency_rate = r.env.user.company_id.currency_id.rate
    validate_columns = '''
        SELECT EXISTS (SELECT 1 FROM information_schema.columns
        WHERE table_name='account_invoice' AND column_name in ('contado','credito'))'''

    query1 = '''
        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]),
            invoice.number,
            partner.name,
            customer.name,
            invoice.amount_tax * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1]),
            invoice.state,
            journal.store_id,
            invoice.journal_id,
            invoice.state,
            invoice.company_id,
            customer.ruc,
            invoice.supplier_invoice_number,
            customer.phone,
            customer.mobile,
            customer.city,
            customer.email
        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
        LEFT JOIN res_users AS users
        ON users.id = invoice.user_id
        LEFT JOIN res_partner AS partner
        ON partner.id = users.partner_id
        LEFT JOIN res_partner AS customer
        ON customer.id = invoice.partner_id
        WHERE invoice.state NOT IN ('draft', 'cancel')
        AND invoice.type IN ('out_invoice')
        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,
            partner.name,
            customer.name,
            invoice.amount_tax,
            invoice.state,
            journal.store_id,
            invoice.journal_id,
            invoice.state,
            invoice.company_id,
            customer.ruc,
            invoice.supplier_invoice_number,
            customer.phone,
            customer.mobile,
            customer.city,
            customer.email
    '''

    query2 = '''
        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]),
            invoice.number,
            partner.name,
            customer.name,
            invoice.amount_tax * (%s / (array_agg(rate.rate ORDER BY rate.id DESC))[1]),
            invoice.state,
            journal.store_id,
            invoice.journal_id,
            invoice.state,
            invoice.company_id,
            customer.ruc,
            invoice.supplier_invoice_number,
            invoice.contado,
            invoice.credito,
            customer.phone,
            customer.mobile,
            customer.city,
            customer.email
        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
        LEFT JOIN res_users AS users
        ON users.id = invoice.user_id
        LEFT JOIN res_partner AS partner
        ON partner.id = users.partner_id
        LEFT JOIN res_partner AS customer
        ON customer.id = invoice.partner_id
        WHERE invoice.state NOT IN ('draft', 'cancel')
        AND invoice.type IN ('out_invoice')
        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,
            partner.name,
            customer.name,
            invoice.amount_tax,
            invoice.state,
            journal.store_id,
            invoice.journal_id,
            invoice.state,
            invoice.company_id,
            customer.ruc,
            invoice.supplier_invoice_number,
            customer.phone,
            customer.mobile,
            customer.city,
            customer.email
    '''

    r.cr.execute(validate_columns)

    for j in r.cr.fetchall():
        column = j[0]

    if column == True:
        r.cr.execute(query2,(tuple([company_currency_rate,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],
            'number':j[8],
            'user_name':j[9],
            'customer_name':j[10],
            'amount_tax':j[11],
            'state':j[12],
            'store_id':j[13],
            'journal_id':j[14],
            'state':j[15],
            'company_id':j[16],
            'customer_ruc':j[17],
            'supplier_invoice_number':j[18],
            'contado':j[19],
            'credito':j[20],
            'customer_phone':j[21],
            'customer_mobile':j[22],
            'customer_city':j[23],
            'customer_email':j[24],
        } for j in r.cr.fetchall()]
    else:
        r.cr.execute(query1,(tuple([company_currency_rate,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],
            'number':j[8],
            'user_name':j[9],
            'customer_name':j[10],
            'amount_tax':j[11],
            'state':j[12],
            'store_id':j[13],
            'journal_id':j[14],
            'state':j[15],
            'company_id':j[16],
            'customer_ruc':j[17],
            'supplier_invoice_number':j[18],
            'customer_phone':j[19],
            'customer_mobile':j[20],
            'customer_city':j[21],
            'customer_email':j[22],             
        } for j in r.cr.fetchall()]

def get_account_invoice_sale_and_refund_type():
    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,
            invoice.number,
            partner.name,
            customer.name,
            invoice.amount_tax,
            invoice.state,
            journal.store_id,
            invoice.journal_id,
            invoice.state,
            invoice.company_id
        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
        LEFT JOIN res_users AS users
        ON users.id = invoice.user_id
        LEFT JOIN res_partner AS partner
        ON partner.id = users.partner_id
        LEFT JOIN res_partner AS customer
        ON customer.id = invoice.partner_id
        WHERE invoice.state NOT IN ('draft', 'cancel')
        AND invoice.type IN ('out_invoice','out_refund')
        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,
            partner.name,
            customer.name,
            invoice.amount_tax,
            invoice.state,
            journal.store_id,
            invoice.journal_id,
            invoice.state,
            invoice.company_id
    '''

    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],
            'number':j[8],
            'user_name':j[9],
            'customer_name':j[10],
            'amount_tax':j[11],
            'state':j[12],
            'store_id':j[13],
            'journal_id':j[14],
            'state':j[15],
            'company_id':j[16]
        } for j in r.cr.fetchall()
    ]

def get_account_invoice_expense_type():
    company_currency_rate = r.env.user.company_id.currency_id.rate
    origin = '%PO%'
    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,
            invoice.number,
            partner.name,
            customer.ruc,
            customer.name,
            invoice.amount_tax,
            invoice.state,
            journal.store_id,
            invoice.journal_id,
            invoice.state,
            invoice.company_id,
            invoice.supplier_invoice_number
        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
        LEFT JOIN res_users AS users
        ON users.id = invoice.user_id
        LEFT JOIN res_partner AS partner
        ON partner.id = users.partner_id
        LEFT JOIN res_partner AS customer
        ON customer.id = invoice.partner_id
        WHERE invoice.state NOT IN ('draft', 'cancel')
        AND invoice.type IN ('in_invoice')
        AND (invoice.origin NOT LIKE %s OR invoice.origin IS NULL)
        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,
            partner.name,
            customer.ruc,
            customer.name,
            invoice.amount_tax,
            invoice.state,
            journal.store_id,
            invoice.journal_id,
            invoice.state,
            invoice.company_id,
            invoice.supplier_invoice_number
    '''
    r.cr.execute(query,([company_currency_rate,origin]))

    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],
            'number':j[8],
            'user_name':j[9],
            'supplier_ruc':j[10],
            'supplier_name':j[11],
            'amount_tax':j[12],
            'state':j[13],
            'store_id':j[14],
            'journal_id':j[15],
            'state':j[16],
            'company_id':j[17],
            'supplier_invoice_number':j[18],
        } for j in r.cr.fetchall()
    ]

def get_account_invoice_purchase_type():
    company_currency_rate = r.env.user.company_id.currency_id.rate
    origin = '%PO%'
    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,
            invoice.number,
            partner.name,
            customer.ruc,
            customer.name,
            invoice.amount_tax,
            invoice.state,
            journal.store_id,
            invoice.journal_id,
            invoice.state,
            invoice.company_id,
            invoice.supplier_invoice_number
        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
        LEFT JOIN res_users AS users
        ON users.id = invoice.user_id
        LEFT JOIN res_partner AS partner
        ON partner.id = users.partner_id
        LEFT JOIN res_partner AS customer
        ON customer.id = invoice.partner_id
        WHERE invoice.state NOT IN ('draft', 'cancel')
        AND invoice.type IN ('in_invoice')
        AND (invoice.origin LIKE %s OR invoice.origin <> NULL)
        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,
            partner.name,
            customer.ruc,
            customer.name,
            invoice.amount_tax,
            invoice.state,
            journal.store_id,
            invoice.journal_id,
            invoice.state,
            invoice.company_id,
            invoice.supplier_invoice_number
    '''
    r.cr.execute(query,([company_currency_rate,origin]))

    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],
            'number':j[8],
            'user_name':j[9],
            'supplier_ruc':j[10],
            'supplier_name':j[11],
            'amount_tax':j[12],
            'state':j[13],
            'store_id':j[14],
            'journal_id':j[15],
            'state':j[16],
            'company_id':j[17],
            'supplier_invoice_number':j[18],
        } for j in r.cr.fetchall()
    ]