From deae0a00574870114b90e5950765b8a42e520a95 Mon Sep 17 00:00:00 2001
From: stephanchrst <stephanchrst@gmail.com>
Date: Tue, 15 Apr 2025 09:05:32 +0700
Subject: bf update reserved status

---
 update_reserved_status.ktr | 18 +++++++++++++++---
 1 file changed, 15 insertions(+), 3 deletions(-)

diff --git a/update_reserved_status.ktr b/update_reserved_status.ktr
index 76c3aa3..6499be5 100644
--- a/update_reserved_status.ktr
+++ b/update_reserved_status.ktr
@@ -729,7 +729,19 @@ else{
       <schema_name/>
     </partitioning>
     <connection>erp indoteknik production (localhost)</connection>
-    <sql>select sp.id as picking_id, sp.state, sum(coalesce(sm.product_uom_qty,0)) as qty_need, 
+    <sql>--select sp.id as picking_id, sp.state, sum(coalesce(sm.product_uom_qty,0)) as qty_need, 
+--sum(coalesce(sml.product_uom_qty,0)) as qty_reserved, sum(coalesce(sml.qty_done,0)) as qty_done,
+--sp.date_reserved, now()::timestamp-'7 hours'::interval as current_date
+--from stock_picking sp
+--join stock_move sm on sm.picking_id = sp.id
+--left join stock_move_line sml on sml.move_id = sm.id
+--join procurement_group pg on pg.id = sp.group_id
+--where 1=1
+--and sp.picking_type_id = 29
+--and (sp.state_reserve not in ('done', 'cancel') or sp.state_reserve is null)
+--and sp.write_date >= (now() - '180 days'::interval)
+--group by sp.id
+select sp.id as picking_id, sp.state, sum(coalesce(sm.product_uom_qty,0)) as qty_need, 
 sum(coalesce(sml.product_uom_qty,0)) as qty_reserved, sum(coalesce(sml.qty_done,0)) as qty_done,
 sp.date_reserved, now()::timestamp-'7 hours'::interval as current_date
 from stock_picking sp
@@ -738,11 +750,11 @@ left join stock_move_line sml on sml.move_id = sm.id
 join procurement_group pg on pg.id = sp.group_id
 where 1=1
 --and pg.sale_id > 0
-and sp.picking_type_id = 29
+and (sp.picking_type_id = 29 or sp.picking_type_id = 30)
 and (sp.state_reserve not in ('done', 'cancel') or sp.state_reserve is null)
 and sp.write_date >= (now() - '180 days'::interval)
 --and sp.state in ('assigned', 'confirmed', 'draft')
-group by sp.id</sql>
+group by sp.id </sql>
     <limit>0</limit>
     <lookup/>
     <execute_each_row>N</execute_each_row>
-- 
cgit v1.2.3