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

from openerp import models, fields, api

class LacagetteToolsModel(models.Model):
    
    _name = "lacagette_tools"
    
    @api.model
    def get_stockQuant_last_Sale(self):
        """Recupère la date de la dernière vente de tout les article """
        
        sql = """SELECT t4.value as name, t2.maxDate, t1.id, t.uom_id, t.purchase_ok,
                COALESCE((SELECT sum(t3.qty)  FROM stock_quant as t3 
                            WHERE location_id = 12 AND t3.product_id =t1.id),0) as stockQt
                    FROM product_product as t1
                    left join product_template as t on t1.product_tmpl_id = t.id
                    right join ir_translation as t4 on t.id = t4.res_id
                    INNER JOIN 
                    ( SELECT MAX(write_date) as maxDate, product_id
                        FROM stock_quant 
                        WHERE location_id = 9 
                        GROUP BY product_id) as t2
                     ON t1.id = t2.product_id
                     WHERE t.active = true and t.sale_ok = true and t4.name='product.template,name'
                     ORDER BY t2.maxDate;"""
        self.env.cr.execute(sql)
        res = self.env.cr.dictfetchall()
        return (res)


    @api.model
    def get_list_article_of_fournisseur(self, partner_id):
        """Recupère la liste des article d'un fournisseur (partner_id) avec leur nom et la qantité en stock"""
        
        sql="""SELECT distinct(t1.product_id), t2.name_template,
                (SELECT coalesce(sum(stock_quant.qty),0)
                      FROM 
                        stock_quant
                      where 
                         stock_quant.location_id =12
                         AND stock_quant.product_id =t1.product_id) as stock_qty
            FROM purchase_order_line as t1 left join product_product as t2 on 
            t1.product_id =t2.id
            WHERE t1.partner_id ={} AND t2.active = true"""
        self.env.cr.execute(sql.format(partner_id))
        res = self.env.cr.dictfetchall()
        return (res)

    @api.model
    def get_sale_qty_by_article(self, lProduct_id):
        """Recupère le moment de la vent et la quantité pour un article"""
        
        sql="""SELECT product_id, coalesce(sum(qty),0) as sumQty 
                FROM stock_quant 
                WHERE location_id = 9 and product_id in ({}) and write_date > CURRENT_TIMESTAMP - INTERVAL '4 WEEK' group by product_id"""
        
        self.env.cr.execute(sql.format(str(lProduct_id)[1:-1]))
        res = self.env.cr.dictfetchall()
        print (res)
        return (res)
    @api.model
    def get_sale_qty_by_from(self, nbWeek):
        """Recupère le moment de la vent et la quantité pour un article"""


        sql="""SELECT t.product_id, coalesce(sum(t.qty),0) as sumQty, coalesce(t3.value, 'Neant') as name
                FROM stock_quant as t
                left join product_product as t1 on t1.id = t.product_id
                left join product_template as t2 on t1.product_tmpl_id = t2.id
                right join ir_translation as t3 on t2.id = t3.res_id
                WHERE t.location_id = 9 and t.write_date::date >= CURRENT_TIMESTAMP::date - INTERVAL '{} WEEK' 
                    AND t3.name='product.template,name'
                GROUP BY t.product_id, t3.value"""
        
        self.env.cr.execute( sql.format(str(nbWeek)))
        res = self.env.cr.dictfetchall()
        print (res)
        return res
    
    
    
    @api.model
    def get_sale_article_by_date(self, myDate):
        """Recupère les article qui ont été ventu a une date"""
        
        sql="""SELECT distinct(product_id)
                FROM stock_quant 
                WHERE location_id = 9 and write_date::date = '{}'::date group by product_id"""
        
        self.env.cr.execute(sql.format(myDate))
        res = self.env.cr.dictfetchall()
        #print (res)
        return (res)