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

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

class Report_Stockgral_Template(models.Model):

    _name = "report.stockgral.template"
    _description = "Stock_general_template"
    _auto = False
    _columns = {
    	'quantity': fields.float('Cantidad', readonly=True),
        'location_id': fields.many2one('stock.location', 'Ubicacion',  readonly=True),
        'product_tmpl_id': fields.many2one('product.template', 'Product', required=True, select=True, readonly=True),
	    'brand_id': fields.many2one('product.brand', 'Marca', readonly=True),
        'genre_id': fields.many2one('product.genre', 'Genero', readonly=True),
        'factory_reference': fields.char('Referencia', readonly=True),
		'parent_id': fields.many2one('product.category', 'Categoria Padre', readonly=True),

    }
    _order = 'product_tmpl_id desc'

    def init(self, cr):
		tools.sql.drop_view_if_exists(cr, 'report_stockgral_template')
		cr.execute("""
            CREATE OR REPLACE VIEW report_stockgral_template as (
                select 	row_number() over (ORDER BY product_tmpl_id)as id,
	sum(sq.qty) as quantity,
	sq.location_id as location_id,
	pp.product_tmpl_id as product_tmpl_id,
	pt.product_brand_id as brand_id,
	pt.product_genre_id as genre_id,
	pt.factory_reference as factory_reference,
    pcc.id_parent as parent_id
from stock_quant sq
	left join stock_location sl on (sl.id = sq.location_id)
        left join product_product pp on (pp.id = sq.product_id)
        left join product_template pt on (pt.id = pp.product_tmpl_id)
        left join product_brand pb on (pt.product_brand_id = pb.id)
        left join product_genre pg on (pt.product_genre_id = pg.id)
        left join product_category pc on (pc.id = pt.categ_id)
	left join product_uom pu on (pu.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 sq.qty > 0 and pt.active=True AND sl.usage='internal'
GROUP BY sq.location_id, pp.product_tmpl_id, pt.product_brand_id, pt.product_genre_id, pt.factory_reference, pcc.id_parent)
		""")


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