analytic_invoice_report/report/analytic_invoice_report.py

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

class analytic_invoice_report(models.Model):
    _name = 'analytic.invoice.report'
    _description = 'Invoices 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)
    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 _analytic_invoice_select(self):
        select = """
            SELECT
                invoice_line.id 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.company_id AS company_id,
                'Pedido de Venta' AS origin,
                invoice_line.quantity AS qty,
                journal.store_id AS store_id,
                (invoice_line.quantity * invoice_line.price_unit) AS subtotal_untaxed,
                (invoice_line.quantity * invoice_line.price_unit) AS subtotal_taxed,
                (invoice_line.price_unit) AS standard_price,
                ((invoice_line.quantity * invoice_line.price_unit)) 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 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.company_id,
                journal.store_id,
                invoice_line.price_unit,
                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)" % (self._table, self._analytic_invoice_select()))