pos_sale_report/report/pos_sale_report.py

# -*- encoding: utf-8 -*-
from openerp import models, fields
from openerp import tools


class pos_sale_report(models.Model):
    _name = 'pos.sale.report'
    _description = 'POS orders and Sale orders aggregated report'
    _auto = False
    _rec_name = 'date'
    _order = 'date desc'

    date = fields.Date(string='Order Date', readonly=True)
    product_id = fields.Many2one(
        'product.product', string='Variantes', readonly=True)
    product_tmpl_id = fields.Many2one(
        'product.template', string='Producto', readonly=True)
    user_id = fields.Many2one(
        'res.users', string='User', readonly=True)  # Add this line for user_id
    # company_id = fields.Many2one(
    #     'res.company', string='Company', readonly=True)
    origin = fields.Char(string='Origin', readonly=True)
    qty = fields.Float(string='Cantidad', readonly=True)

    number = fields.Char(string='Venta', readonly=True)
    store_id = fields.Many2one(
        'res.store', string='Sucursal', readonly=True)
    subtotal_taxed = fields.Float(string='total', readonly=True)
    standard_price = fields.Float(string='Costo', readonly=True)
    utility = fields.Float(string='Utilidad', readonly=True)
    categ_id = fields.Many2one(
        'product.category', string='Categoría', readonly=True)

    # WARNING : this code doesn't handle uom conversion for the moment
    def _sale_order_select(self):
        select = """
            SELECT
                --min(invoice_line.id)*-1 AS id,
                invoice_line.id*-1 AS id,
                invoice.number AS number,
                invoice.date_invoice AS date,
                invoice_line.product_id AS product_id,
                product.product_tmpl_id AS product_tmpl_id,
                invoice.user_id AS user_id,
                invoice.partner_id AS partner_id,
                invoice.company_id AS company_id,
                'Pedido de Venta' AS origin,
                --sum(invoice_line.quantity) AS qty,
                invoice_line.quantity AS qty,
                journal.store_id AS store_id,
                invoice_line.price_subtotal AS subtotal_untaxed,
                --invoice_line.discount,
                --((invoice_line.quantity * invoice_line.price_unit) * invoice_line.discount) / 100 AS amount_discount,
                (invoice_line.quantity * invoice_line.price_unit) - (((invoice_line.quantity * invoice_line.price_unit) * invoice_line.discount) / 100) AS subtotal_taxed,
                (array_agg(history.cost ORDER BY history.id DESC))[1] * invoice_line.quantity AS standard_price,
                ((invoice_line.quantity * invoice_line.price_unit) - (((invoice_line.quantity * invoice_line.price_unit) * invoice_line.discount) / 100) - ((array_agg(history.cost ORDER BY history.id DESC))[1] * invoice_line.quantity)) AS utility,
                template.categ_id
            FROM account_invoice_line AS invoice_line
            LEFT JOIN account_invoice AS invoice
            ON invoice.id = invoice_line.invoice_id
            LEFT JOIN res_users AS users
            ON users.id = invoice.user_id
            LEFT JOIN res_partner AS partner
            ON partner.id = invoice.partner_id            
            LEFT JOIN product_product AS product
            ON product.id = invoice_line.product_id
            LEFT JOIN product_template AS template
            ON template.id = product.product_tmpl_id
            LEFT JOIN res_store_journal_rel as journal
            ON journal.journal_id = invoice.journal_id
            LEFT JOIN product_price_history AS history
            ON history.product_template_id = product.product_tmpl_id
            WHERE invoice.state IN ('open','paid')
            AND invoice.type = 'out_invoice'
            GROUP BY
                invoice_line.id,
                invoice.date_invoice,
                invoice.number,
                invoice_line.product_id,
                product.product_tmpl_id,
                invoice.user_id,
                invoice.partner_id,
                invoice.company_id,
                journal.store_id,
                invoice_line.price_subtotal,
                template.categ_id
        """
        return select

    def _pos_order_select(self):
        select = """
            SELECT
                --min(pol.id) AS id,
                pol.id AS id,
                po.name AS number,
                po.date_order::date AS date,
                pol.product_id AS product_id,
                product.product_tmpl_id AS product_tmpl_id,
                po.user_id AS user_id,
                po.partner_id AS partner_id,
                po.company_id AS company_id,
                'TPV' AS origin,
                --sum(pol.qty) AS qty,
                pol.qty AS qty,
                journal.store_id AS store_id,
                pol.price_subtotal_incl AS subtotal_untaxed,
                pol.price_subtotal_incl AS subtotal_taxed,
                (array_agg(history.cost ORDER BY history.id DESC))[1] * pol.qty AS standard_price,
                (pol.price_subtotal_incl - ((array_agg(history.cost ORDER BY history.id DESC))[1] * pol.qty)) AS utility,
                template.categ_id
            FROM pos_order_line AS pol
            LEFT JOIN pos_order AS po
            ON po.id = pol.order_id
            LEFT JOIN res_users AS users
            ON users.id = po.user_id
            LEFT JOIN res_partner AS partner
            ON partner.id = po.partner_id
            LEFT JOIN product_product AS product
            ON product.id = pol.product_id
            LEFT JOIN product_template AS template
            ON template.id = product.product_tmpl_id
            LEFT JOIN res_store_journal_rel as journal
            ON journal.journal_id = po.sale_journal
            LEFT JOIN product_price_history AS history
            ON history.product_template_id = product.product_tmpl_id
            WHERE po.state IN ('paid', 'done', 'invoiced')
            GROUP BY
                pol.id,
                po.name,
                po.date_order,
                pol.product_id,
                product.product_tmpl_id,
                po.user_id,
                po.partner_id,
                po.company_id,
                journal.store_id,
                pol.price_subtotal_incl,
                template.categ_id
        """
        return select

    def init(self, cr):
        tools.drop_view_if_exists(cr, self._table)
        cr.execute("CREATE OR REPLACE VIEW %s AS (%s UNION %s)" % (
            self._table, self._sale_order_select(), self._pos_order_select()))