# -*- coding: utf-8 -*-
##############################################################################
#
#    OpenERP, Open Source Management Solution
#    Copyright (C) 2004-2010 Tiny SPRL (<http://tiny.be>).
#
#    This program is free software: you can redistribute it and/or modify
#    it under the terms of the GNU Affero General Public License as
#    published by the Free Software Foundation, either version 3 of the
#    License, or (at your option) any later version.
#
#    This program is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU Affero General Public License for more details.
#
#    You should have received a copy of the GNU Affero General Public License
#    along with this program.  If not, see <http://www.gnu.org/licenses/>.
#
##############################################################################

from openerp import tools
from openerp.osv import fields, osv

class report_stockgral1(osv.osv):
    """
    Quants are the smallest unit of stock physical instances
    """
    _name = "report.stockgral1"
    _description = "Stockgral"
    _auto = False
    _columns = {
		'product_id': fields.many2one('product.product', 'Product', required=True, select=True, readonly=True),
		'unit_price': fields.float('Precio Venta', readonly=True),
		'quantity': fields.float('Cantidad', readonly=True),
		'subtotal_venta': fields.float('Subtotal', readonly=True),
		'location_id': fields.many2one('stock.location', 'Ubicacion',  readonly=True),
		'categ_id': fields.many2one('product.category', 'Categoria', readonly=True),
		'brand_id': fields.many2one('product.brand', 'Marca', readonly=True),
		'factory_reference': fields.char('Referencia', readonly=True),
		'genre_id': fields.many2one('product.genre', 'Genero', readonly=True),
        'parent_id': fields.many2one('product.category', 'Categoria Padre', readonly=True),
        'id_parent' : fields.many2one('product.category', 'Categoria Padre', readonly=True),
    }
    _order = 'product_id desc'

    def init(self, cr):
		tools.sql.drop_view_if_exists(cr, 'report_stockgral1')
		cr.execute("""
            CREATE OR REPLACE VIEW report_stockgral1 as (
                SELECT	s.id as id,
                        s.product_id as product_id,
                        pt.list_price as unit_price,
                        SUM(s.qty) as quantity,
                        SUM(s.qty) * SUM(pt.list_price) as subtotal_venta,
                        pt.product_brand_id as brand_id,
                        pt.product_genre_id as genre_id,
                        pt.categ_id as categ_id,
                        pt.factory_reference as factory_reference,
                        r.id as location_id,
                        g.parent_id as parent_id,
                        pcc.id_parent as id_parent
                FROM stock_quant s
                        left join stock_location r on (r.id=s.location_id)
                        left join product_product p on (p.id=s.product_id)
                        left join product_template pt on (pt.id=p.product_tmpl_id)
                            left join product_brand z on (pt.product_brand_id=z.id)
                            left join product_genre t on (pt.product_genre_id=t.id)
                            left join product_category g on (g.id=pt.categ_id)
                        left join product_uom u on (u.id=pt.uom_id)
                        left join (SELECT pp.id, pp.parent_id,
                          case when (select ppp.parent_id from product_category ppp where ppp.id= pp.parent_id and ppp.parent_id !=1) is NULL
                          then pp.parent_id  ELSE
                          (select ppp.parent_id from product_category ppp where ppp.id= pp.parent_id and ppp.parent_id !=1) END as id_parent
                    from product_category pp) as pcc on pcc.id = pt.categ_id
                WHERE r.usage='internal' and  s.qty>0 and  pt.active=True
                GROUP BY s.id,s.product_id,r.id,pt.product_brand_id,pt.factory_reference,pt.categ_id,pt.product_genre_id,pt.list_price, g.parent_id,pcc.id_parent)
		""")
# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: