From 708b3d8c2ccf6714e41f3bc22715704277743689 Mon Sep 17 00:00:00 2001 From: stephanchrst Date: Fri, 12 Jul 2024 10:30:43 +0700 Subject: change import sale order --- import_all_data_bi_indoteknik.kjb | 82 ++- import_crm_team.ktr | 1102 +++++++++++++++++++++++++++++++++++++ import_sale_order.ktr | 53 +- import_sale_order_v2.ktr | 6 +- 4 files changed, 1172 insertions(+), 71 deletions(-) create mode 100644 import_crm_team.ktr diff --git a/import_all_data_bi_indoteknik.kjb b/import_all_data_bi_indoteknik.kjb index 13dd3fc..e49873e 100644 --- a/import_all_data_bi_indoteknik.kjb +++ b/import_all_data_bi_indoteknik.kjb @@ -601,7 +601,7 @@ N Y 0 - 512 + 560 192 @@ -640,7 +640,7 @@ N Y 0 - 368 + 288 192 @@ -874,7 +874,7 @@ N Y 0 - 240 + 160 192 @@ -913,7 +913,7 @@ N Y 0 - 80 + 32 192 @@ -952,7 +952,7 @@ N Y 0 - 80 + 32 320 @@ -991,7 +991,7 @@ N Y 0 - 240 + 176 320 @@ -1030,7 +1030,7 @@ N Y 0 - 384 + 336 320 @@ -1069,7 +1069,7 @@ N Y 0 - 528 + 496 320 @@ -1580,6 +1580,45 @@ 528 + + import sale order v2 + + TRANS + + filename + + /home/stephan/transformation-job-bi/import_sale_order_v2.ktr + + N + N + N + N + N + N + + + N + N + Basic + N + + N + Y + N + N + N + Pentaho local + N + + Y + + N + Y + 0 + 432 + 192 + + @@ -1627,15 +1666,6 @@ Y N - - import margin - import sale order - 0 - 0 - Y - Y - N - import account move import account move line @@ -1879,6 +1909,24 @@ Y N + + import margin + import sale order v2 + 0 + 0 + Y + Y + N + + + import sale order v2 + import sale order + 0 + 0 + Y + Y + N + diff --git a/import_crm_team.ktr b/import_crm_team.ktr new file mode 100644 index 0000000..3a0bca5 --- /dev/null +++ b/import_crm_team.ktr @@ -0,0 +1,1102 @@ + + + + import_crm_team + + + + Normal + / + + + + + + + + + + + + ID_BATCH + Y + ID_BATCH + + + CHANNEL_ID + Y + CHANNEL_ID + + + TRANSNAME + Y + TRANSNAME + + + STATUS + Y + STATUS + + + LINES_READ + Y + LINES_READ + + + + LINES_WRITTEN + Y + LINES_WRITTEN + + + + LINES_UPDATED + Y + LINES_UPDATED + + + + LINES_INPUT + Y + LINES_INPUT + + + + LINES_OUTPUT + Y + LINES_OUTPUT + + + + LINES_REJECTED + Y + LINES_REJECTED + + + + ERRORS + Y + ERRORS + + + STARTDATE + Y + STARTDATE + + + ENDDATE + Y + ENDDATE + + + LOGDATE + Y + LOGDATE + + + DEPDATE + Y + DEPDATE + + + REPLAYDATE + Y + REPLAYDATE + + + LOG_FIELD + Y + LOG_FIELD + + + EXECUTING_SERVER + N + EXECUTING_SERVER + + + EXECUTING_USER + N + EXECUTING_USER + + + CLIENT + N + CLIENT + + + + + +
+ + + + ID_BATCH + Y + ID_BATCH + + + SEQ_NR + Y + SEQ_NR + + + LOGDATE + Y + LOGDATE + + + TRANSNAME + Y + TRANSNAME + + + STEPNAME + Y + STEPNAME + + + STEP_COPY + Y + STEP_COPY + + + LINES_READ + Y + LINES_READ + + + LINES_WRITTEN + Y + LINES_WRITTEN + + + LINES_UPDATED + Y + LINES_UPDATED + + + LINES_INPUT + Y + LINES_INPUT + + + LINES_OUTPUT + Y + LINES_OUTPUT + + + LINES_REJECTED + Y + LINES_REJECTED + + + ERRORS + Y + ERRORS + + + INPUT_BUFFER_ROWS + Y + INPUT_BUFFER_ROWS + + + OUTPUT_BUFFER_ROWS + Y + OUTPUT_BUFFER_ROWS + + + + + +
+ + + ID_BATCH + Y + ID_BATCH + + + CHANNEL_ID + Y + CHANNEL_ID + + + LOG_DATE + Y + LOG_DATE + + + LOGGING_OBJECT_TYPE + Y + LOGGING_OBJECT_TYPE + + + OBJECT_NAME + Y + OBJECT_NAME + + + OBJECT_COPY + Y + OBJECT_COPY + + + REPOSITORY_DIRECTORY + Y + REPOSITORY_DIRECTORY + + + FILENAME + Y + FILENAME + + + OBJECT_ID + Y + OBJECT_ID + + + OBJECT_REVISION + Y + OBJECT_REVISION + + + PARENT_CHANNEL_ID + Y + PARENT_CHANNEL_ID + + + ROOT_CHANNEL_ID + Y + ROOT_CHANNEL_ID + + + + + +
+ + + ID_BATCH + Y + ID_BATCH + + + CHANNEL_ID + Y + CHANNEL_ID + + + LOG_DATE + Y + LOG_DATE + + + TRANSNAME + Y + TRANSNAME + + + STEPNAME + Y + STEPNAME + + + STEP_COPY + Y + STEP_COPY + + + LINES_READ + Y + LINES_READ + + + LINES_WRITTEN + Y + LINES_WRITTEN + + + LINES_UPDATED + Y + LINES_UPDATED + + + LINES_INPUT + Y + LINES_INPUT + + + LINES_OUTPUT + Y + LINES_OUTPUT + + + LINES_REJECTED + Y + LINES_REJECTED + + + ERRORS + Y + ERRORS + + + LOG_FIELD + N + LOG_FIELD + + + + + +
+ + + ID_BATCH + Y + ID_BATCH + + + CHANNEL_ID + Y + CHANNEL_ID + + + LOG_DATE + Y + LOG_DATE + + + METRICS_DATE + Y + METRICS_DATE + + + METRICS_CODE + Y + METRICS_CODE + + + METRICS_DESCRIPTION + Y + METRICS_DESCRIPTION + + + METRICS_SUBJECT + Y + METRICS_SUBJECT + + + METRICS_TYPE + Y + METRICS_TYPE + + + METRICS_VALUE + Y + METRICS_VALUE + + + + + +
+ + 0.0 + 0.0 + + 10000 + 50 + 50 + N + Y + 50000 + Y + + N + 1000 + 100 + + + + + + + + + - + 2024/07/12 10:27:18.969 + - + 2024/07/12 10:27:18.969 + + N + + + + + dw bi indoteknik + localhost + POSTGRESQL + Native + dw_bi_indoteknik + 5703 + metabase + Encrypted 2be98dfba28d7bf86853c9f58cfc4f889 + + + + + + FORCE_IDENTIFIERS_TO_LOWERCASE + N + + + FORCE_IDENTIFIERS_TO_UPPERCASE + N + + + IS_CLUSTERED + N + + + PORT_NUMBER + 5703 + + + PRESERVE_RESERVED_WORD_CASE + Y + + + QUOTE_ALL_FIELDS + N + + + SUPPORTS_BOOLEAN_DATA_TYPE + Y + + + SUPPORTS_TIMESTAMP_DATA_TYPE + Y + + + USE_POOLING + N + + + + + erp indoteknik production + localhost + POSTGRESQL + Native + erp_indoteknik + 5901 + odoo + Encrypted 2be98afc82bdfd8dd9a2ca45acc83f6c3 + + + + + + FORCE_IDENTIFIERS_TO_LOWERCASE + N + + + FORCE_IDENTIFIERS_TO_UPPERCASE + N + + + IS_CLUSTERED + N + + + PORT_NUMBER + 5901 + + + PRESERVE_RESERVED_WORD_CASE + Y + + + QUOTE_ALL_FIELDS + N + + + SUPPORTS_BOOLEAN_DATA_TYPE + Y + + + SUPPORTS_TIMESTAMP_DATA_TYPE + Y + + + USE_POOLING + N + + + + + + Table input + Insert / update + Y + + + + Table input + TableInput + + Y + + 1 + + none + + + erp indoteknik production + select * from crm_team ct + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + Table input + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + name + 2147483647 + -1 + Table input + name + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + Integer + normal + sequence + 9 + 0 + Table input + sequence + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + Boolean + normal + active + -1 + -1 + Table input + active + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + Integer + normal + company_id + 9 + 0 + Table input + company_id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + Integer + normal + user_id + 9 + 0 + Table input + user_id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + Integer + normal + color + 9 + 0 + Table input + color + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + Integer + normal + message_main_attachment_id + 9 + 0 + Table input + message_main_attachment_id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + Integer + normal + create_uid + 9 + 0 + Table input + create_uid + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + Timestamp + normal + create_date + 6 + -1 + Table input + create_date + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + Integer + normal + write_uid + 9 + 0 + Table input + write_uid + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + Timestamp + normal + write_date + 6 + -1 + Table input + write_date + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + Boolean + normal + use_quotations + -1 + -1 + Table input + use_quotations + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + Number + normal + invoiced_target + -1 + -1 + Table input + invoiced_target + ####0.0#########;-####0.0######### + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + Boolean + normal + use_leads + -1 + -1 + Table input + use_leads + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + Boolean + normal + use_opportunities + -1 + -1 + Table input + use_opportunities + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + Integer + normal + alias_id + 9 + 0 + Table input + alias_id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 272 + 144 + Y + + + + Insert / update + InsertUpdate + + Y + + 1 + + none + + + dw bi indoteknik + 100 + N + + public +
crm_team
+ + id + id + = + + + + id + id + N + + + name + name + Y + + + sequence + sequence + Y + + + active + active + Y + + + company_id + company_id + Y + + + user_id + user_id + Y + + + color + color + Y + + + message_main_attachment_id + message_main_attachment_id + Y + + + create_uid + create_uid + Y + + + create_date + create_date + Y + + + write_uid + write_uid + Y + + + write_date + write_date + Y + + + use_quotations + use_quotations + Y + + + invoiced_target + invoiced_target + Y + + + use_leads + use_leads + Y + + + use_opportunities + use_opportunities + Y + + + alias_id + alias_id + Y + + + + + + + + + + + + 432 + 144 + Y + + + + + + + N + + diff --git a/import_sale_order.ktr b/import_sale_order.ktr index 1abf2c0..f878d61 100644 --- a/import_sale_order.ktr +++ b/import_sale_order.ktr @@ -486,57 +486,6 @@ - - erp indoteknik production (localhost) - 10.148.0.3 - POSTGRESQL - Native - erp_indoteknik - 5432 - odoo - Encrypted 2be98afc82bdfd8dd9a2ca45acc83f6c3 - - - - - - FORCE_IDENTIFIERS_TO_LOWERCASE - N - - - FORCE_IDENTIFIERS_TO_UPPERCASE - N - - - IS_CLUSTERED - N - - - PORT_NUMBER - 5432 - - - PRESERVE_RESERVED_WORD_CASE - Y - - - QUOTE_ALL_FIELDS - N - - - SUPPORTS_BOOLEAN_DATA_TYPE - Y - - - SUPPORTS_TIMESTAMP_DATA_TYPE - N - - - USE_POOLING - N - - - Table input @@ -728,7 +677,7 @@ none - erp indoteknik production (localhost) + dw bi indoteknik (localhost) select * from v_sale_order 0 diff --git a/import_sale_order_v2.ktr b/import_sale_order_v2.ktr index 1a58ca1..4eb9a7a 100644 --- a/import_sale_order_v2.ktr +++ b/import_sale_order_v2.ktr @@ -561,7 +561,8 @@ erp indoteknik production (localhost) - select * from sale_order + select * from sale_order +where write_date >= (now() - '3 days'::interval) 0 N @@ -3266,7 +3267,8 @@ erp indoteknik production (localhost) - select * from sale_order_line + select * from sale_order_line +where write_date >= (now() - '3 days'::interval) 0 N -- cgit v1.2.3