eiru_reports_baco/controller/helpers/pos_order.py

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

def get_pos_order_baco():
    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
            pos.create_date,
            pos.name,
            pos.partner_id,
            pos.user_id,
            SUM(line.price_subtotal_incl) as amount,
            partner.name,
            pos.id,
            salesperson.name,
            SUM(line.price_subtotal) as untaxed,
            pos.sale_journal,
            journal.store_id,
            pos.company_id,
            partner.ruc,
            partner.phone,
            partner.mobile,
            partner.email,
            partner.city
        FROM pos_order as pos
        LEFT JOIN res_store_journal_rel as journal
        ON journal.journal_id = pos.sale_journal
        LEFT JOIN pos_order_line AS line
        ON line.order_id = pos.id
        LEFT JOIN res_partner AS partner
        ON partner.id = pos.partner_id
        LEFT JOIN res_users AS users
        ON users.id = pos.user_id
        LEFT JOIN res_partner AS salesperson
        ON salesperson.id = users.partner_id
        GROUP BY
            pos.create_date,
            pos.partner_id,
            pos.user_id,
            pos.name,
            partner.name,
            pos.id,
            salesperson.name,
            pos.sale_journal,
            journal.store_id,
            pos.company_id,
            partner.ruc,
            partner.phone,
            partner.mobile,
            partner.email,
            partner.city
    '''

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

    if band == True:
        r.cr.execute(query)
        return [
            {
                'date': j[0],
                'name': j[1],
                'customer_id': j[2],
                'user_id': j[3],
                'amount': j[4],
                'customer_name': j[5],
                'order_id': j[6],
                'user_name': j[7],
                'amount_untaxed': j[8],
                'journal_id': j[9],
                'store_id': j[10],
                'company_id': j[11],
                'customer_ruc': j[12],
                'phone':j[13],
                'mobile':j[14],
                'email':j[15],
                'city':j[16],
            } for j in r.cr.fetchall()
        ]
    else:
        return []