# -*- 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)