breaking_model.py

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

from openerp import models, fields, api
import numpy as np

class stockBreaking(models.Model):
    _name ="stock.breaking"
    _description = "Table of breaking following artile"
    state_breaking = fields.Integer("State of article", index=True)
    qty = fields.Float("Qty of move trigger")
    product_id = fields.Many2one('product.product', 'Article', index=True)
    notifying = fields.Boolean( 'notifying breaking', default = False, requered = True)
    
    
    @api.model
    def get_artile_breaking(self):
        sql = """SELECT id, state_breaking, product_id, create_date
            FROM stock_breaking 
            WHERE id IN (SELECT MAX(id) FROM stock_breaking GROUP BY product_id) AND state_breaking = 0"""
        self.env.cr.execute(sql)
        res = self.env.cr.dictfetchall()
        return (res)
        
    @api.model
    def get_artile_breaking_period_by_list(self,lProduct_id):
        sql ="""SELECT t.product_id, TO_CHAR(t.create_date, 'YYYY-MM-DD"T"HH24:MI') as start, TO_CHAR(coalesce(t.next_d, now()),'YYYY-MM-DD"T"HH24:MI') as end
            FROM (SELECT t1.product_id, 
                t1.create_date,
                t1.state_breaking,
                lag(t1.create_date) OVER (PARTITION BY product_id ORDER BY t1.create_date DESC) as next_d
            FROM stock_breaking as t1
            ) as t
            WHERE t.state_breaking = 0 and product_id in ({}) ORDER BY t.product_id"""
        print(sql.format(str(lProduct_id)[1:-1]))
        self.env.cr.execute(sql.format(str(lProduct_id)[1:-1]))
        res = self.env.cr.dictfetchall()
        print (res)
        return (res)