# -*- coding: utf-8 -*-

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

class report_stock_product(models.Model):

    _name = "report.stock.product"
    _description = "report.stock.product"
    _auto = False
    _columns = {
        'id': fields.integer('id',required=True),
		'product_id': fields.many2one('product.product', 'Product', required=True, select=True, readonly=True),
		'quantity': fields.float('Cantidad', readonly=True),
        'brand_id': fields.many2one('product.brand', 'Marca', readonly=True),
        'genre_id': fields.many2one('product.genre', 'Genero', readonly=True),
        'categ_id': fields.many2one('product.category', 'Categoria', readonly=True),
		'factory_reference': fields.char('Referencia', readonly=True),
		'location_id': fields.many2one('stock.location', 'Ubicacion',  readonly=True),
		'id_parent' : fields.many2one('product.category', 'Categoria Padre', readonly=True),
    }
    _order = 'id asc'

    def init(self, cr):
		tools.sql.drop_view_if_exists(cr, 'report_stock_product')
		cr.execute("""
            CREATE OR REPLACE VIEW report_stock_product as (
            SELECT	row_number() over (ORDER BY product_id)as id,
	               s.product_id as product_id,
                   SUM(s.qty) as quantity,
                   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,
                   s.location_id as location_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 (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.location_id, s.product_id, pt.product_brand_id, pt.product_genre_id, pt.factory_reference, pt.categ_id, pcc.id_parent)
		""")


# vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: