eiru_reports_dashboard/controllers/helpers/pos_order.py

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

def get_pos_order_widget():
    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
        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
        WHERE TO_CHAR(pos.date_order,'YYYY-MM') = TO_CHAR(current_date,'YYYY-MM')
        AND journal.store_id = ''' + str(user_store)+ '''
        GROUP BY pos.create_date, pos.partner_id, pos.user_id, pos.name
    '''

    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],
            } for j in r.cr.fetchall()
        ]
    else:
        return []