From d4ade9ed9b5dabdfb7f4dd8e6fc612cc9eef302b Mon Sep 17 00:00:00 2001 From: stephanchrst Date: Tue, 29 Jul 2025 17:24:37 +0700 Subject: update --- calculate_leadtime_po_to_mr.ktr | 1016 ++++++++++++++++++++++----------------- 1 file changed, 572 insertions(+), 444 deletions(-) (limited to 'calculate_leadtime_po_to_mr.ktr') diff --git a/calculate_leadtime_po_to_mr.ktr b/calculate_leadtime_po_to_mr.ktr index cabbe51..438a88b 100644 --- a/calculate_leadtime_po_to_mr.ktr +++ b/calculate_leadtime_po_to_mr.ktr @@ -430,7 +430,7 @@ 2025/07/28 14:26:08.522 - 2025/07/28 14:26:08.522 - + H4sIAAAAAAAAAAMAAAAAAAAAAAA= N @@ -554,10 +554,10 @@ - Table input - TableInput + Insert / update + InsertUpdate - N + Y 1 @@ -565,108 +565,65 @@ dw bi indoteknik (localhost) - select po_confirm_date, received_good_date, purchase_line_id, purchase_id -from mv_leadtime_po_to_received - 0 - - N - N - N - N - - - Timestamp - normal + 100 + N + + public + leadtime_po_to_received_working_hour
+ + purchase_line_id + purchase_line_id + = + + + + purchase_id + purchase_id + = + + + + received_id + received_id + = + + + po_confirm_date - 6 - -1 - Table input - po_confirm_date - - . - , - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - Timestamp - normal + po_confirm_date + Y + + received_good_date - 6 - -1 - Table input - received_good_date - - . - , - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - Integer - normal + received_good_date + Y + + purchase_line_id - 9 - 0 - Table input - purchase_line_id - ####0;-####0 - . - , - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - Integer - normal + purchase_line_id + N + + purchase_id - 9 - 0 - Table input - purchase_id - ####0;-####0 - . - , - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - + purchase_id + N + + + new_po_to_arrival + new_po_to_arrival + Y + + + po_to_arrival_status + po_to_arrival_status + Y + + + received_id + received_id + N + + @@ -676,7 +633,98 @@ from mv_leadtime_po_to_received - 176 + 768 + 304 + Y + + + + Merge join + MergeJoin + + Y + + 1 + + none + + + INNER + Sort rows + Sort rows 2 + + purchase_line_id + purchase_id + received_id + + + purchase_line_id + purchase_id + received_id + + + + + + + + + + + 464 + 304 + Y + + + + Modified JavaScript value + ScriptValueMod + + Y + + 1 + + none + + + N + 9 + + + 0 + Script 1 + //Script here +var po_to_arrival_status = 'not achieve'; +if (new_po_to_arrival <= 16) +{ + po_to_arrival_status = 'achieve' +} +else{ + po_to_arrival_status = 'not achieve' +} + + + + + + po_to_arrival_status + po_to_arrival_status + String + -1 + -1 + N + + + + + + + + + + + + 768 80 Y @@ -709,6 +757,10 @@ from mv_leadtime_po_to_received purchase_id + + received_id + + N @@ -726,8 +778,8 @@ from mv_leadtime_po_to_received - Table input 2 - TableInput + Select values 2 + SelectValues Y @@ -736,109 +788,29 @@ from mv_leadtime_po_to_received none - dw bi indoteknik (localhost) - WITH params AS ( - SELECT - ?::timestamp AS start_time, - ?::timestamp AS end_time -), -hours AS ( - SELECT generate_series( - (SELECT start_time FROM params), - (SELECT end_time FROM params), - interval '1 hour' - ) AS ts -), -filtered_hours AS ( - SELECT ts - FROM hours - WHERE - EXTRACT(HOUR FROM ts) BETWEEN 8 AND 16 - AND ( - EXTRACT(HOUR FROM ts) > 8 OR EXTRACT(MINUTE FROM ts) >= 30 - ) - AND EXTRACT(DOW FROM ts) NOT IN (0) - AND DATE(ts) NOT IN (SELECT holiday_date FROM holidays) -) -SELECT COUNT(*) AS new_po_to_arrival, ? as purchase_line_id, ? as purchase_id -FROM filtered_hours; - 0 - Select values - Y - Y - N - N - - - Integer - normal - new_po_to_arrival - 15 - 0 - Table input 2 - new_po_to_arrival - ####0;-####0 - . - , - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - String - normal + + + po_confirm_date + + + + received_good_date + + + purchase_line_id - 2147483647 - -1 - Table input 2 - purchase_line_id - - . - , - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - String - normal + + + purchase_id - 2147483647 - -1 - Table input 2 - purchase_id - - . - , - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - + + + + received_id + + + N + @@ -848,13 +820,13 @@ FROM filtered_hours; - 464 - 80 + 176 + 192 Y - Select values 2 + Select values 3 SelectValues Y @@ -881,6 +853,14 @@ FROM filtered_hours; purchase_id + + new_po_to_arrival + + + + received_id + + N @@ -892,7 +872,63 @@ FROM filtered_hours; - 176 + 592 + 304 + Y + + + + Select values 4 + SelectValues + + Y + + 1 + + none + + + + + po_confirm_date + + + + received_good_date + + + + purchase_line_id + + + + purchase_id + + + + new_po_to_arrival + + + + po_to_arrival_status + + + + received_id + + + N + + + + + + + + + + + 768 192 Y @@ -924,6 +960,22 @@ FROM filtered_hours; 0 N + + purchase_id + Y + N + N + 0 + N + + + received_id + Y + N + N + 0 + N + @@ -966,6 +1018,22 @@ FROM filtered_hours; 0 N + + purchase_id + Y + N + N + 0 + N + + + received_id + Y + N + N + 0 + N + @@ -982,8 +1050,8 @@ FROM filtered_hours; - Merge join - MergeJoin + Sort rows 3 + SortRows Y @@ -992,15 +1060,39 @@ FROM filtered_hours; none - INNER - Sort rows - Sort rows 2 - - purchase_line_id - - - purchase_line_id - + %%java.io.tmpdir%% + out + 1000000 + + N + + N + + + purchase_line_id + Y + N + N + 0 + N + + + purchase_id + Y + N + N + 0 + N + + + received_id + Y + N + N + 0 + N + + @@ -1010,45 +1102,149 @@ FROM filtered_hours; - 464 - 304 + 592 + 192 Y - Select values 3 - SelectValues + Table input + TableInput - Y + N 1 none - - + dw bi indoteknik (localhost) + select po_confirm_date, received_good_date, purchase_line_id, purchase_id, received_id +from mv_leadtime_po_to_received +--where purchase_id = 25089 and purchase_line_id = 141455 and received_id in (78477, 78910) + 0 + + N + N + N + N + + + Timestamp + normal po_confirm_date - - - + 6 + -1 + Table input + po_confirm_date + + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Timestamp + normal received_good_date - - - + 6 + -1 + Table input + received_good_date + + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Integer + normal purchase_line_id - - - + 9 + 0 + Table input + purchase_line_id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Integer + normal purchase_id - - - - new_po_to_arrival - - - N - + 9 + 0 + Table input + purchase_id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Integer + normal + received_id + 9 + 0 + Table input + received_id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + @@ -1058,14 +1254,14 @@ FROM filtered_hours; - 592 - 304 + 176 + 80 Y - Sort rows 3 - SortRows + Table input 2 + TableInput Y @@ -1074,31 +1270,132 @@ FROM filtered_hours; none - %%java.io.tmpdir%% - out - 1000000 - - N - - N - - + dw bi indoteknik (localhost) + WITH params AS ( + SELECT + ?::timestamp AS start_time, + ?::timestamp AS end_time +), +hours AS ( + SELECT generate_series( + (SELECT start_time FROM params), + (SELECT end_time FROM params), + interval '1 hour' + ) AS ts +), +filtered_hours AS ( + SELECT ts + FROM hours + WHERE + EXTRACT(HOUR FROM ts) BETWEEN 8 AND 17 + AND ( + EXTRACT(HOUR FROM ts) > 8 OR EXTRACT(MINUTE FROM ts) >= 30 + ) + --AND EXTRACT(DOW FROM ts) NOT IN (0) + AND DATE(ts) NOT IN (SELECT holiday_date FROM holidays) +) +SELECT COUNT(*) AS new_po_to_arrival, ? as purchase_line_id, ? as purchase_id, ? as received_id +FROM filtered_hours; + 0 + Select values + Y + Y + N + N + + + Integer + normal + new_po_to_arrival + 15 + 0 + Table input 2 + new_po_to_arrival + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + String + normal purchase_line_id - Y - N - N + 2147483647 + -1 + Table input 2 + purchase_line_id + + . + , + + none + N + Y 0 - N - - + N + N + N + en_US + Asia/Bangkok + N + + + String + normal purchase_id - Y - N - N + 2147483647 + -1 + Table input 2 + purchase_id + + . + , + + none + N + Y 0 - N - - + N + N + N + en_US + Asia/Bangkok + N + + + String + normal + received_id + 2147483647 + -1 + Table input 2 + received_id + + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + @@ -1108,8 +1405,8 @@ FROM filtered_hours; - 592 - 192 + 464 + 80 Y @@ -1137,58 +1434,9 @@ FROM filtered_hours; purchase_id N - - - - - - - - - - - 592 - 80 - Y - - - - Modified JavaScript value - ScriptValueMod - - Y - - 1 - - none - - - N - 9 - - - 0 - Script 1 - //Script here -var po_to_arrival_status = 'not achieve'; -if (new_po_to_arrival <= 16) -{ - po_to_arrival_status = 'achieve' -} -else{ - po_to_arrival_status = 'not achieve' -} - - - - - po_to_arrival_status - po_to_arrival_status - String - -1 - -1 - N + received_id + N @@ -1200,131 +1448,11 @@ else{ - 768 + 592 80 Y - - Select values 4 - SelectValues - - Y - - 1 - - none - - - - - po_confirm_date - - - - received_good_date - - - - purchase_line_id - - - - purchase_id - - - - new_po_to_arrival - - - - po_to_arrival_status - - - N - - - - - - - - - - - 768 - 192 - Y - - - - Insert / update - InsertUpdate - - Y - - 1 - - none - - - dw bi indoteknik (localhost) - 100 - N - - public - leadtime_po_to_received_working_hour
- - purchase_line_id - purchase_line_id - = - - - - po_confirm_date - po_confirm_date - Y - - - received_good_date - received_good_date - Y - - - purchase_line_id - purchase_line_id - N - - - purchase_id - purchase_id - Y - - - new_po_to_arrival - new_po_to_arrival - Y - - - po_to_arrival_status - po_to_arrival_status - Y - - - - - - - - - - - - 768 - 304 - Y - - -- cgit v1.2.3