from tools.com_cache import cache, ctime, timeblock


 # 使用SQL来取得指定商品在指定仓库的库存数量
    def get_warehouse_stock_qty(self,warehouse_id):
        self.ensure_one()
# 从缓存中查询，如有
        cache_data = cache.warehouse_stock_qty.get([self.id,warehouse_id])
        if isinstance(cache_data,list):
        # 则直接将缓存中的数据返回。  要注意：每次出入库审核时将缓存中的数据清空或者更新，保证缓存数据是最新的。
            return cache_data
# 若无，则从数据库查询
        self.env.cr.execute('''
                SELECT sum(CASE WHEN whi.transport_state = 'onway' OR buyr.buy_receipt_state = 'onway' THEN 0 ELSE 
              line.qty_remaining END) as qty,
                       SUM(line.qty_remaining * line.cost_unit) AS cost,
                       wh.name as warehouse
                FROM wh_move_line line
                      LEFT JOIN wh_move whm ON whm.id = line.move_id
                      LEFT JOIN wh_internal whi ON whm.id = whi.move_id
                      LEFT JOIN buy_receipt buyr ON whm.id = buyr.buy_move_id
                      LEFT JOIN warehouse wh ON line.warehouse_dest_id = wh.id
                WHERE wh.type in ('stock','supplier')
                  AND wh.code not in ('Supplier','General')
                  AND line.qty_remaining != 0
                  AND line.state in ('done','onway')               
                  AND line.goods_id = %s
                  AND wh.id= %s
                GROUP BY wh.name
            ''' % (self.id,warehouse_id))
        rows=self.env.cr.dictfetchall()
# 将查询结果写入缓存中
        cache.warehouse_stock_qty.set((self.id,warehouse_id),rows)
        return rows