From af33dac188fe74e189246c0c9d1c0c9df93b3bc2 Mon Sep 17 00:00:00 2001 From: stephanchrst Date: Wed, 22 Oct 2025 09:33:17 +0700 Subject: update_cogs_fixco.ktr --- delete_useless_data_account_move.ktr | 2 +- delete_useless_data_account_move_line.ktr | 247 +++++++------ import_account_move_line_onetime.ktr | 55 ++- import_account_move_onetime.ktr | 561 +++++++++++++++++++++++++++++- import_all_data_bi_indoteknik.kjb | 70 +++- update_cogs_fixco.ktr | 106 +++++- 6 files changed, 862 insertions(+), 179 deletions(-) diff --git a/delete_useless_data_account_move.ktr b/delete_useless_data_account_move.ktr index 7277f6b..54725ad 100644 --- a/delete_useless_data_account_move.ktr +++ b/delete_useless_data_account_move.ktr @@ -430,7 +430,7 @@ 2025/10/07 11:52:02.699 - 2025/10/07 11:52:02.699 - + H4sIAAAAAAAAAAMAAAAAAAAAAAA= N diff --git a/delete_useless_data_account_move_line.ktr b/delete_useless_data_account_move_line.ktr index b61aab9..f3cb0ba 100644 --- a/delete_useless_data_account_move_line.ktr +++ b/delete_useless_data_account_move_line.ktr @@ -430,7 +430,7 @@ 2025/10/02 17:22:32.051 - 2025/10/02 17:22:32.051 - + H4sIAAAAAAAAAAMAAAAAAAAAAAA= N @@ -575,8 +575,8 @@ - production - TableInput + Delete + Delete Y @@ -585,39 +585,18 @@ none - erp indoteknik production - select id from account_move_line order by id - 0 - - N - N - N - N - - - Integer - normal + dw bi indoteknik + 100 + + public + account_move_line
+ id - 9 - 0 - Table input - id - ####0;-####0 - . - , - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - + id + = + + + @@ -627,14 +606,14 @@ - 192 - 96 + 896 + 240 Y - data warehouse - TableInput + Filter rows + FilterRows Y @@ -643,14 +622,16 @@ none - dw bi indoteknik - select id from account_move_line order by id - 0 - - N - N - N - N + Select values 2 + Write to log + + + N + in_two_loc + IS NULL + + + @@ -660,14 +641,14 @@ - 400 - 96 + 608 + 240 Y - Stream lookup - StreamLookup + Select values + SelectValues Y @@ -676,23 +657,15 @@ none - production - N - Y - N - N - - - id - id - - + + id - in_two_loc - - None - - + + + in_two_loc + + N + @@ -702,13 +675,13 @@ - 272 + 464 240 Y - Select values + Select values 2 SelectValues Y @@ -721,11 +694,9 @@ id - in_two_loc - N @@ -738,14 +709,14 @@ - 464 + 752 240 Y - Filter rows - FilterRows + Stream lookup + StreamLookup Y @@ -754,16 +725,23 @@ none - Select values 2 - Write to log - - - N - in_two_loc - IS NULL - - - + production + N + Y + N + N + + + id + id + + + id + in_two_loc + + None + + @@ -773,14 +751,14 @@ - 608 + 272 240 Y - Select values 2 - SelectValues + Write to log + WriteToLog Y @@ -789,17 +767,13 @@ none + log_level_basic + Y + N + 0 + - - id - - - - in_two_loc - - - N - + @@ -809,14 +783,14 @@ - 752 - 240 + 608 + 368 Y - Write to log - WriteToLog + data warehouse + TableInput Y @@ -825,13 +799,15 @@ none - log_level_basic - Y - N - 0 - - - + dw bi indoteknik + select id from account_move_line order by id + 0 + + N + N + N + N + @@ -841,14 +817,14 @@ - 608 - 368 + 400 + 96 Y - Delete - Delete + production + TableInput Y @@ -857,18 +833,39 @@ none - dw bi indoteknik - 100 - - public - account_move_line
- + erp indoteknik production + select id from account_move_line order by id + 0 + + N + N + N + N + + + Integer + normal id - id - = - - - + 9 + 0 + Table input + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + @@ -878,8 +875,8 @@ - 896 - 240 + 192 + 96 Y diff --git a/import_account_move_line_onetime.ktr b/import_account_move_line_onetime.ktr index d37e6cd..fb7afb5 100644 --- a/import_account_move_line_onetime.ktr +++ b/import_account_move_line_onetime.ktr @@ -437,14 +437,14 @@ - dw bi indoteknik (localhost) + dw bi indoteknik localhost POSTGRESQL Native dw_bi_indoteknik - 5432 - pdi - Encrypted 2be989bb20084ca95810197629d9ea18e + 5703 + metabase + Encrypted 2be98dfba28d7bf86853c9f58cfc4f889 @@ -463,7 +463,7 @@ PORT_NUMBER - 5432 + 5703 PRESERVE_RESERVED_WORD_CASE @@ -479,7 +479,7 @@ SUPPORTS_TIMESTAMP_DATA_TYPE - N + Y USE_POOLING @@ -488,14 +488,14 @@ - erp indoteknik production (localhost) - 10.148.0.3 + erp indoteknik production + localhost POSTGRESQL Native erp_indoteknik - 5432 - pdi - Encrypted 2be989bb20084ca95810197629d9ea18e + 5901 + odoo + Encrypted 2be98afc82bdfd8dd9a2ca45acc83f6c3 @@ -514,7 +514,7 @@ PORT_NUMBER - 5432 + 5901 PRESERVE_RESERVED_WORD_CASE @@ -530,7 +530,7 @@ SUPPORTS_TIMESTAMP_DATA_TYPE - N + Y USE_POOLING @@ -556,7 +556,7 @@ none - dw bi indoteknik (localhost) + dw bi indoteknik 100 N @@ -889,11 +889,11 @@ none - erp indoteknik production (localhost) + erp indoteknik production select aml.* from account_move_line aml join account_move am on am.id = aml.move_id where 1=1 -and aml.write_date between '2024-06-01' and '2024-06-30' +and aml.write_date between '2025-01-01' and '2025-03-30' --and to_char(am.date, 'YYYY') = '2023' --and (am.move_type = 'out_invoice' or am.journal_id = 3) --and aml.write_date >= (now() - '5 days'::interval) @@ -2261,6 +2261,29 @@ and aml.write_date between '2024-06-01' and '2024-06-30' Asia/Bangkok N + + Integer + normal + line_no + 9 + 0 + Table input + line_no + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + diff --git a/import_account_move_onetime.ktr b/import_account_move_onetime.ktr index fe12538..da576f8 100644 --- a/import_account_move_onetime.ktr +++ b/import_account_move_onetime.ktr @@ -437,14 +437,14 @@ - dw bi indoteknik (localhost) + dw bi indoteknik localhost POSTGRESQL Native dw_bi_indoteknik - 5432 - pdi - Encrypted 2be989bb20084ca95810197629d9ea18e + 5703 + metabase + Encrypted 2be98dfba28d7bf86853c9f58cfc4f889 @@ -463,7 +463,7 @@ PORT_NUMBER - 5432 + 5703 PRESERVE_RESERVED_WORD_CASE @@ -479,7 +479,7 @@ SUPPORTS_TIMESTAMP_DATA_TYPE - N + Y USE_POOLING @@ -488,14 +488,14 @@ - erp indoteknik production (localhost) - 10.148.0.3 + erp indoteknik production + localhost POSTGRESQL Native erp_indoteknik - 5432 - pdi - Encrypted 2be989bb20084ca95810197629d9ea18e + 5901 + odoo + Encrypted 2be98afc82bdfd8dd9a2ca45acc83f6c3 @@ -514,7 +514,7 @@ PORT_NUMBER - 5432 + 5901 PRESERVE_RESERVED_WORD_CASE @@ -530,7 +530,7 @@ SUPPORTS_TIMESTAMP_DATA_TYPE - N + Y USE_POOLING @@ -556,7 +556,7 @@ none - dw bi indoteknik (localhost) + dw bi indoteknik 100 N @@ -1019,10 +1019,10 @@ none - erp indoteknik production (localhost) + erp indoteknik production select am.* from account_move am where 1=1 -and am.write_date between '2024-06-01' and '2024-06-30' +and am.write_date between '2025-01-01' and '2025-10-30' --and to_char(am.date, 'YYYY') = '2023' --and (am.move_type = 'out_invoice' or journal_id = 3) --and am.write_date >= (now() - '5 days'::interval) @@ -2942,6 +2942,535 @@ and am.write_date between '2024-06-01' and '2024-06-30' Asia/Bangkok N + + String + normal + no_faktur_pajak + 2147483647 + -1 + Table input + no_faktur_pajak + + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Timestamp + normal + date_completed + 6 + -1 + Table input + date_completed + + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Boolean + normal + is_customer_commision + -1 + -1 + Table input + is_customer_commision + + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Integer + normal + sale_id + 9 + 0 + Table input + sale_id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Integer + normal + reklas_id + 9 + 0 + Table input + reklas_id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Timestamp + normal + date_efaktur_upload + 6 + -1 + Table input + date_efaktur_upload + + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Integer + normal + real_invoice_id + 9 + 0 + Table input + real_invoice_id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Integer + normal + bills_efaktur_exporter + 9 + 0 + Table input + bills_efaktur_exporter + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Timestamp + normal + bills_date_efaktur + 6 + -1 + Table input + bills_date_efaktur + + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Integer + normal + bills_invoice_exporter + 9 + 0 + Table input + bills_invoice_exporter + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Timestamp + normal + bills_date_invoice + 6 + -1 + Table input + bills_date_invoice + + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Boolean + normal + is_invoice_uploaded + -1 + -1 + Table input + is_invoice_uploaded + + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Boolean + normal + is_efaktur_uploaded + -1 + -1 + Table input + is_efaktur_uploaded + + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Boolean + normal + already_paid + -1 + -1 + Table input + already_paid + + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + String + normal + nomor_kwitansi + 2147483647 + -1 + Table input + nomor_kwitansi + + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Boolean + normal + is_hr + -1 + -1 + Table input + is_hr + + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Integer + normal + purchase_order_id + 9 + 0 + Table input + purchase_order_id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Integer + normal + reklas_misc_id + 9 + 0 + Table input + reklas_misc_id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Integer + normal + bill_id + 9 + 0 + Table input + bill_id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Boolean + normal + down_payment + -1 + -1 + Table input + down_payment + + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Integer + normal + refund_id + 9 + 0 + Table input + refund_id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Date + normal + reminder_sent_date + -1 + -1 + Table input + reminder_sent_date + + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Date + normal + customer_promise_date + -1 + -1 + Table input + customer_promise_date + + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + diff --git a/import_all_data_bi_indoteknik.kjb b/import_all_data_bi_indoteknik.kjb index 660768c..208dbce 100644 --- a/import_all_data_bi_indoteknik.kjb +++ b/import_all_data_bi_indoteknik.kjb @@ -445,7 +445,7 @@ N Y 0 - 656 + 592 192 @@ -523,7 +523,7 @@ N Y 0 - 800 + 720 192 @@ -1787,6 +1787,45 @@ 640 + + update cogs fixco + + TRANS + + filename + + /home/stephan/transformation-job-bi/update_cogs_fixco.ktr + + N + N + N + N + N + N + + + N + N + Basic + N + + N + Y + N + N + N + Pentaho local + N + + Y + + N + Y + 0 + 864 + 192 + + @@ -1807,15 +1846,6 @@ Y N - - import fixco - refresh mv fixco - 0 - 0 - Y - Y - N - refresh mv fixco Delete files @@ -2140,6 +2170,24 @@ Y N + + import fixco + update cogs fixco + 0 + 0 + Y + Y + N + + + update cogs fixco + refresh mv fixco + 0 + 0 + Y + Y + N + diff --git a/update_cogs_fixco.ktr b/update_cogs_fixco.ktr index f26d5e0..9be8f7e 100644 --- a/update_cogs_fixco.ktr +++ b/update_cogs_fixco.ktr @@ -436,12 +436,12 @@ - dw bi fixco + dw bi fixco (localhost) localhost POSTGRESQL Native dw_bi_fixco - 5703 + 5432 pdi Encrypted 2be989bb20084ca95810197629d9ea18e @@ -462,7 +462,7 @@ PORT_NUMBER - 5703 + 5432 PRESERVE_RESERVED_WORD_CASE @@ -502,14 +502,24 @@ Calculator Y + + Select values + Update + Y + Calculator + Filter rows + Y + + + Filter rows Select values Y - Select values - Update + Filter rows + Write to log Y @@ -583,7 +593,7 @@ - 528 + 656 64 Y @@ -641,7 +651,7 @@ none - dw bi fixco + dw bi fixco (localhost) N 100 N @@ -670,7 +680,7 @@ - 672 + 816 64 Y @@ -686,7 +696,7 @@ none - dw bi fixco + dw bi fixco (localhost) select item_code, cost_with_tax, cost, tax from product_cost where year = 2025 0 @@ -814,7 +824,7 @@ from product_cost where year = 2025 none - dw bi fixco + dw bi fixco (localhost) select id, item_code, qty, harga, total_harga, ppn, dpp from total_omzet_by_product where 1=1 @@ -1003,6 +1013,82 @@ and cogs <= 0 Y + + Filter rows + FilterRows + + Y + + 1 + + none + + + Select values + Write to log + + + N + total_cogs + > + + + constant + BigNumber + 0.0 + -1 + -1 + N + ######0.0###################;-######0.0################### + + + + + + + + + + + + + 512 + 64 + Y + + + + Write to log + WriteToLog + + Y + + 1 + + none + + + log_level_basic + Y + N + 0 + + + + + + + + + + + + + 512 + 160 + Y + + -- cgit v1.2.3