From 220fbc8a87333dac4d0c51b4bd93207017371968 Mon Sep 17 00:00:00 2001 From: stephanchrst Date: Mon, 9 Sep 2024 10:53:16 +0700 Subject: add calculate lob category --- calculate_customer_per_year.ktr | 1238 +++++++++ calculate_lob_category.ktr | 781 ++++++ calculate_lob_category_v2.ktr | 5088 ++++++++++++++++++++++++++++++++++++ calculate_percent_lob_category.ktr | 772 ++++++ import_all_data_bi_indoteknik.kjb | 48 + import_partner_one_time.ktr | 687 +---- 6 files changed, 7950 insertions(+), 664 deletions(-) create mode 100644 calculate_customer_per_year.ktr create mode 100644 calculate_lob_category.ktr create mode 100644 calculate_lob_category_v2.ktr create mode 100644 calculate_percent_lob_category.ktr diff --git a/calculate_customer_per_year.ktr b/calculate_customer_per_year.ktr new file mode 100644 index 0000000..6138ebd --- /dev/null +++ b/calculate_customer_per_year.ktr @@ -0,0 +1,1238 @@ + + + + calculate_customer_per_year + + + + 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/09/06 14:04:36.322 + - + 2024/09/06 14:04:36.322 + + 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 + + + + + + last year + parter last year + Y + + + partner + parter last year + Y + + + parter last year + last and now + Y + + + this year + last and now + Y + + + last and now + Select values + Y + + + Select values + Calculator + Y + + + Calculator + Select values 2 + Y + + + + partner + TableInput + + Y + + 1 + + none + + + dw bi indoteknik + select rp.id, coalesce(gp.name, rp.name) as group, rp.name as customer +from res_partner rp +left join group_partner gp on gp.id = rp.group_partner_id +where rp.id in ( + select partner_id + from account_move am + where am.state = 'posted' and am.move_type = 'out_invoice' +) +order by rp.id + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + partner + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + group + 2147483647 + -1 + partner + group + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + customer + 2147483647 + -1 + partner + customer + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 176 + 192 + Y + + + + last year + TableInput + + Y + + 1 + + none + + + dw bi indoteknik + select rp.id, coalesce(gp.name, rp.name) as group, rp.name as customer, +sum(aml.price_subtotal) as ly_value, count(distinct am.name) as ly_trx +from account_move am +join res_partner rp on rp.id = am.partner_id +join account_move_line aml on aml.move_id = am.id +join product p on p.product_id = aml.product_id +left join group_partner gp on gp.id = rp.group_partner_id +where am.state = 'posted' and am.move_type = 'out_invoice' +and am.date between get_first_day_last_year()::date and get_now_last_year()::date +group by rp.id, gp.name, rp.name +order by rp.id + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + last year + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + group + 2147483647 + -1 + last year + group + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + customer + 2147483647 + -1 + last year + customer + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + ly_value + -1 + -1 + last year + ly_value + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + Integer + normal + ly_trx + 15 + 0 + last year + ly_trx + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 272 + 96 + Y + + + + parter last year + MergeJoin + + Y + + 1 + + none + + + LEFT OUTER + partner + last year + + id + + + id + + + + + + + + + + + 272 + 192 + Y + + + + this year + TableInput + + Y + + 1 + + none + + + dw bi indoteknik + select rp.id, coalesce(gp.name, rp.name) as group, rp.name as customer, +sum(aml.price_subtotal) as fy_value, count(distinct am.name) as fy_trx +from account_move am +join res_partner rp on rp.id = am.partner_id +join account_move_line aml on aml.move_id = am.id +join product p on p.product_id = aml.product_id +left join group_partner gp on gp.id = rp.group_partner_id +where am.state = 'posted' and am.move_type = 'out_invoice' +and am.date between get_first_day_current_year()::date and get_now()::date +group by rp.id, gp.name, rp.name +order by rp.id + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + this year + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + group + 2147483647 + -1 + this year + group + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + customer + 2147483647 + -1 + this year + customer + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + fy_value + -1 + -1 + this year + fy_value + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + Integer + normal + fy_trx + 15 + 0 + this year + fy_trx + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 400 + 96 + Y + + + + last and now + MergeJoin + + Y + + 1 + + none + + + LEFT OUTER + parter last year + this year + + id + + + id + + + + + + + + + + + 400 + 192 + Y + + + + Select values + SelectValues + + Y + + 1 + + none + + + + + id + + + + group + + + + customer + + + + fy_value + + + + fy_trx + + + + ly_value + + + + ly_trx + + + N + + + + + + + + + + + 528 + 192 + Y + + + + Calculator + Calculator + + Y + + 1 + + none + + + Y + + diff_value + SUBTRACT + fy_value + ly_value + + None + -1 + -1 + N + + + + + + + diff_trx + SUBTRACT + fy_trx + ly_trx + + None + -1 + -1 + N + + + + + + + growth_value + DIVIDE + diff_value + ly_value + + None + -1 + -1 + N + + + + + + + growth_trx + DIVIDE + diff_trx + ly_trx + + None + -1 + -1 + N + + + + + + + + + + + + + + + 640 + 192 + Y + + + + Select values 2 + SelectValues + + Y + + 1 + + none + + + + + id + + + + group + + + + customer + + + + fy_value + + + + fy_trx + + + + ly_value + + + + ly_trx + + + + growth_value + + + + growth_trx + + + N + + + + + + + + + + + 752 + 192 + Y + + + + + + + N + + diff --git a/calculate_lob_category.ktr b/calculate_lob_category.ktr new file mode 100644 index 0000000..389fe94 --- /dev/null +++ b/calculate_lob_category.ktr @@ -0,0 +1,781 @@ + + + + calculate_lob_category + + + + 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/09/05 10:23:34.990 + - + 2024/09/05 10:23:34.990 + + 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 + + + + + + Table input + Insert / update + Y + + + + Table input + TableInput + + Y + + 1 + + none + + + dw bi indoteknik + select rpi.id as industry_id, ppc.id as category_id, coalesce(rpi.name, 'Unknown') as industry, +coalesce(ppc.name, 'Undefined') as category, +to_char(am.date, 'YYYY') as year_date, sum(aml.price_subtotal) as value +from account_move am +join res_partner rp on rp.id = am.partner_id +join account_move_line aml on aml.move_id = am.id +join product p on p.product_id = aml.product_id +left join res_partner_industry rpi on rpi.id = rp.industry_id +left join product_public_category ppc on ppc.id = p.parent_category_id +where am.move_type = 'out_invoice' +and am.state = 'posted' +and am."date" between get_first_day_last_year()::date and get_now_last_year()::date +group by rpi.id, ppc.id, rpi.name, ppc.name, to_char(am.date, 'YYYY') +union +select rpi.id as industry_id, ppc.id as category_id, coalesce(rpi.name, 'Unknown') as industry, +coalesce(ppc.name, 'Undefined') as category, +to_char(am.date, 'YYYY') as year_date, sum(aml.price_subtotal) as value +from account_move am +join res_partner rp on rp.id = am.partner_id +join account_move_line aml on aml.move_id = am.id +join product p on p.product_id = aml.product_id +left join res_partner_industry rpi on rpi.id = rp.industry_id +left join product_public_category ppc on ppc.id = p.parent_category_id +where am.move_type = 'out_invoice' +and am.state = 'posted' +and am."date" between get_first_day_current_year()::date and get_now()::date +group by rpi.id, ppc.id, rpi.name, ppc.name, to_char(am.date, 'YYYY') + 0 + + N + N + N + N + + + Integer + normal + industry_id + 9 + 0 + Table input + industry_id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + Integer + normal + category_id + 9 + 0 + Table input + category_id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + industry + 2147483647 + -1 + Table input + industry + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + category + 2147483647 + -1 + Table input + category + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + year_date + 2147483647 + -1 + Table input + year_date + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + value + -1 + -1 + Table input + value + ######0.0###################;-######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 +
t_lob_category_value
+ + industry_id + industry_id + = + + + + category_id + category_id + = + + + + year_date + year_date + = + + + + industry_id + industry_id + N + + + category_id + category_id + N + + + industry + industry + Y + + + category + category + Y + + + year_date + year_date + N + + + value + value + Y + + + + + + + + + + + + 464 + 144 + Y + + + + + + + N + + diff --git a/calculate_lob_category_v2.ktr b/calculate_lob_category_v2.ktr new file mode 100644 index 0000000..ae442f2 --- /dev/null +++ b/calculate_lob_category_v2.ktr @@ -0,0 +1,5088 @@ + + + + calculate_lob_category_v2 + + + + 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/09/05 14:53:27.746 + - + 2024/09/05 14:53:27.746 + H4sIAAAAAAAAAAMAAAAAAAAAAAA= + N + + + + + dw bi indoteknik (localhost) + localhost + POSTGRESQL + Native + dw_bi_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 + + + + + + safety_ly + safety + Y + + + safety_fy + safety + Y + + + safety + safety_tools + Y + + + tools_ly + safety_tools + Y + + + safety_tools + tools + Y + + + tools_fy + tools + Y + + + tools + tools_machine + Y + + + machine_ly + tools_machine + Y + + + tools_machine + machine + Y + + + machine_fy + machine + Y + + + machine + machine_agri + Y + + + agri_ly + machine_agri + Y + + + machine_agri + agri + Y + + + agri_fy + agri + Y + + + agri + agri_clean + Y + + + clean_ly + agri_clean + Y + + + agri_clean + clean + Y + + + clean_fy + clean + Y + + + clean + clean_chemic + Y + + + chemic_ly + clean_chemic + Y + + + clean_chemic + chemic + Y + + + chemic_fy + chemic + Y + + + chemic + chemic_meas + Y + + + meas_ly + chemic_meas + Y + + + chemic_meas + meas + Y + + + meas_fy + meas + Y + + + meas + meas_elec + Y + + + elec_ly + meas_elec + Y + + + meas_elec + elec + Y + + + elec_fy + elec + Y + + + elec + elec_log + Y + + + log_ly + elec_log + Y + + + elec_log + log + Y + + + log_fy + log + Y + + + log + log_office + Y + + + office_ly + log_office + Y + + + log_office + office + Y + + + office_fy + office + Y + + + office + office_acc + Y + + + acc_ly + office_acc + Y + + + office_acc + acc + Y + + + acc_fy + acc + Y + + + acc + acc_hore + Y + + + hore_ly + acc_hore + Y + + + acc_hore + hore + Y + + + hore_fy + hore + Y + + + hore + Select values + Y + + + Select values + Calculator + Y + + + Calculator + Select values 2 + Y + + + Select values 2 + Insert / update + Y + + + + Calculator + Calculator + + Y + + 1 + + none + + + Y + + safety_g + PERCENT_1 + safety_fy + safety_ly + + None + -1 + -1 + N + + + + + + + tools_g + PERCENT_1 + tools_fy + tools_ly + + None + -1 + -1 + N + + + + + + + machine_g + PERCENT_1 + machine_fy + machine_ly + + None + -1 + -1 + N + + + + + + + agri_g + PERCENT_1 + agri_fy + agri_ly + + None + -1 + -1 + N + + + + + + + clean_g + PERCENT_1 + clean_fy + clean_ly + + None + -1 + -1 + N + + + + + + + chemic_g + PERCENT_1 + chemic_fy + chemic_ly + + None + -1 + -1 + N + + + + + + + meas_g + PERCENT_1 + meas_fy + meas_ly + + None + -1 + -1 + N + + + + + + + elec_g + PERCENT_1 + elec_fy + elec_ly + + None + -1 + -1 + N + + + + + + + log_g + PERCENT_1 + log_fy + log_ly + + None + -1 + -1 + N + + + + + + + office_g + PERCENT_1 + office_fy + office_ly + + None + -1 + -1 + N + + + + + + + acc_g + PERCENT_1 + acc_fy + acc_ly + + None + -1 + -1 + N + + + + + + + hore_g + PERCENT_1 + hore_fy + hore_ly + + None + -1 + -1 + N + + + + + + + + + + + + + + + 192 + 416 + Y + + + + Insert / update + InsertUpdate + + Y + + 1 + + none + + + dw bi indoteknik (localhost) + 100 + N + + public +
t_lob_category_value_v2
+ + id + id + = + + + + id + id + N + + + industry + industry + Y + + + safety_ly + safety_ly + Y + + + safety_fy + safety_fy + Y + + + tools_ly + tools_ly + Y + + + tools_fy + tools_fy + Y + + + machine_ly + machine_ly + Y + + + machine_fy + machine_fy + Y + + + agri_ly + agri_ly + Y + + + agri_fy + agri_fy + Y + + + clean_ly + clean_ly + Y + + + clean_fy + clean_fy + Y + + + chemic_ly + chemic_ly + Y + + + chemic_fy + chemic_fy + Y + + + meas_ly + meas_ly + Y + + + meas_fy + meas_fy + Y + + + elec_ly + elec_ly + Y + + + elec_fy + elec_fy + Y + + + log_ly + log_ly + Y + + + log_fy + log_fy + Y + + + office_ly + office_ly + Y + + + office_fy + office_fy + Y + + + acc_ly + acc_ly + Y + + + acc_fy + acc_fy + Y + + + hore_ly + hore_ly + Y + + + hore_fy + hore_fy + Y + + + safety_g + safety_g + Y + + + tools_g + tools_g + Y + + + machine_g + machine_g + Y + + + agri_g + agri_g + Y + + + clean_g + clean_g + Y + + + chemic_g + chemic_g + Y + + + meas_g + meas_g + Y + + + elec_g + elec_g + Y + + + log_g + log_g + Y + + + office_g + office_g + Y + + + acc_g + acc_g + Y + + + hore_g + hore_g + Y + + + + + + + + + + + + 464 + 416 + Y + + + + Select values + SelectValues + + Y + + 1 + + none + + + + + id + + + industry + + + safety_ly + + + safety_fy + + + tools_ly + + + tools_fy + + + machine_ly + + + machine_fy + + + agri_ly + + + agri_fy + + + clean_ly + + + clean_fy + + + chemic_ly + + + chemic_fy + + + meas_ly + + + meas_fy + + + elec_ly + + + elec_fy + + + log_ly + + + log_fy + + + office_ly + + + office_fy + + + acc_ly + + + acc_fy + + + hore_ly + + + hore_fy + + N + + + + + + + + + + + 80 + 416 + Y + + + + Select values 2 + SelectValues + + Y + + 1 + + none + + + + + id + + + industry + + + safety_ly + + + safety_fy + + + tools_ly + + + tools_fy + + + machine_ly + + + machine_fy + + + agri_ly + + + agri_fy + + + clean_ly + + + clean_fy + + + chemic_ly + + + chemic_fy + + + meas_ly + + + meas_fy + + + elec_ly + + + elec_fy + + + log_ly + + + log_fy + + + office_ly + + + office_fy + + + acc_ly + + + acc_fy + + + hore_ly + + + hore_fy + + + safety_g + + + tools_g + + + machine_g + + + agri_g + + + clean_g + + + chemic_g + + + meas_g + + + elec_g + + + log_g + + + office_g + + + acc_g + + + hore_g + + N + + + + + + + + + + + 304 + 416 + Y + + + + acc + MergeJoin + + Y + + 1 + + none + + + LEFT OUTER + office_acc + acc_fy + + id + + + id + + + + + + + + + + + 288 + 224 + Y + + + + acc_fy + TableInput + + Y + + 1 + + none + + + dw bi indoteknik (localhost) + select rpi.id, coalesce(rpi.name, 'Unknown') as industry, +(select sum(aml.price_subtotal) +from account_move am +join res_partner rp on rp.id = am.partner_id +join account_move_line aml on aml.move_id = am.id +join product p on p.product_id = aml.product_id +left join res_partner_industry rpi2 on rpi2.id = rp.industry_id +left join product_public_category ppc on ppc.id = p.parent_category_id +where am.move_type = 'out_invoice' +and am.state = 'posted' +and am."date" between get_first_day_current_year()::date and get_now()::date +and ppc.id = 2445 and rpi2.id = rpi.id) as acc_fy +from res_partner_industry rpi +order by rpi.id + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + office_fy 2 + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + industry + 2147483647 + -1 + office_fy 2 + industry + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + acc_fy + -1 + -1 + office_fy 2 + acc_fy + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 288 + 320 + Y + + + + acc_hore + MergeJoin + + Y + + 1 + + none + + + LEFT OUTER + acc + hore_ly + + id + + + id + + + + + + + + + + + 192 + 224 + Y + + + + acc_ly + TableInput + + Y + + 1 + + none + + + dw bi indoteknik (localhost) + select rpi.id, coalesce(rpi.name, 'Unknown') as industry, +(select sum(aml.price_subtotal) +from account_move am +join res_partner rp on rp.id = am.partner_id +join account_move_line aml on aml.move_id = am.id +join product p on p.product_id = aml.product_id +left join res_partner_industry rpi2 on rpi2.id = rp.industry_id +left join product_public_category ppc on ppc.id = p.parent_category_id +where am.move_type = 'out_invoice' +and am.state = 'posted' +and am."date" between get_first_day_last_year()::date and get_now_last_year()::date +and ppc.id = 2445 and rpi2.id = rpi.id) as acc_ly +from res_partner_industry rpi +order by rpi.id + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + office_ly 2 + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + industry + 2147483647 + -1 + office_ly 2 + industry + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + acc_ly + -1 + -1 + office_ly 2 + acc_ly + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 384 + 320 + Y + + + + agri + MergeJoin + + Y + + 1 + + none + + + LEFT OUTER + machine_agri + agri_fy + + id + + + id + + + + + + + + + + + 784 + 144 + Y + + + + agri_clean + MergeJoin + + Y + + 1 + + none + + + LEFT OUTER + agri + clean_ly + + id + + + id + + + + + + + + + + + 880 + 144 + Y + + + + agri_fy + TableInput + + Y + + 1 + + none + + + dw bi indoteknik (localhost) + select rpi.id, coalesce(rpi.name, 'Unknown') as industry, +(select sum(aml.price_subtotal) +from account_move am +join res_partner rp on rp.id = am.partner_id +join account_move_line aml on aml.move_id = am.id +join product p on p.product_id = aml.product_id +left join res_partner_industry rpi2 on rpi2.id = rp.industry_id +left join product_public_category ppc on ppc.id = p.parent_category_id +where am.move_type = 'out_invoice' +and am.state = 'posted' +and am."date" between get_first_day_current_year()::date and get_now()::date +and ppc.id = 2222 and rpi2.id = rpi.id) as agri_fy +from res_partner_industry rpi +order by rpi.id + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + agri_fy + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + industry + 2147483647 + -1 + agri_fy + industry + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + agri_fy + -1 + -1 + agri_fy + agri_fy + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 880 + 48 + Y + + + + agri_ly + TableInput + + Y + + 1 + + none + + + dw bi indoteknik (localhost) + select rpi.id, coalesce(rpi.name, 'Unknown') as industry, +(select sum(aml.price_subtotal) +from account_move am +join res_partner rp on rp.id = am.partner_id +join account_move_line aml on aml.move_id = am.id +join product p on p.product_id = aml.product_id +left join res_partner_industry rpi2 on rpi2.id = rp.industry_id +left join product_public_category ppc on ppc.id = p.parent_category_id +where am.move_type = 'out_invoice' +and am.state = 'posted' +and am."date" between get_first_day_last_year()::date and get_now_last_year()::date +and ppc.id = 2222 and rpi2.id = rpi.id) as agri_ly +from res_partner_industry rpi +order by rpi.id + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + agri_ly + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + industry + 2147483647 + -1 + agri_ly + industry + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + agri_ly + -1 + -1 + agri_ly + agri_ly + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 784 + 48 + Y + + + + chemic + MergeJoin + + Y + + 1 + + none + + + LEFT OUTER + clean_chemic + chemic_fy + + id + + + id + + + + + + + + + + + 1168 + 144 + Y + + + + chemic_fy + TableInput + + Y + + 1 + + none + + + dw bi indoteknik (localhost) + select rpi.id, coalesce(rpi.name, 'Unknown') as industry, +(select sum(aml.price_subtotal) +from account_move am +join res_partner rp on rp.id = am.partner_id +join account_move_line aml on aml.move_id = am.id +join product p on p.product_id = aml.product_id +left join res_partner_industry rpi2 on rpi2.id = rp.industry_id +left join product_public_category ppc on ppc.id = p.parent_category_id +where am.move_type = 'out_invoice' +and am.state = 'posted' +and am."date" between get_first_day_current_year()::date and get_now()::date +and ppc.id = 2273 and rpi2.id = rpi.id) as chemic_fy +from res_partner_industry rpi +order by rpi.id + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + checmic_fy + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + industry + 2147483647 + -1 + checmic_fy + industry + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + chemic_fy + -1 + -1 + checmic_fy + chemic_fy + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 1264 + 48 + Y + + + + chemic_ly + TableInput + + Y + + 1 + + none + + + dw bi indoteknik (localhost) + select rpi.id, coalesce(rpi.name, 'Unknown') as industry, +(select sum(aml.price_subtotal) +from account_move am +join res_partner rp on rp.id = am.partner_id +join account_move_line aml on aml.move_id = am.id +join product p on p.product_id = aml.product_id +left join res_partner_industry rpi2 on rpi2.id = rp.industry_id +left join product_public_category ppc on ppc.id = p.parent_category_id +where am.move_type = 'out_invoice' +and am.state = 'posted' +and am."date" between get_first_day_last_year()::date and get_now_last_year()::date +and ppc.id = 2273 and rpi2.id = rpi.id) as chemic_ly +from res_partner_industry rpi +order by rpi.id + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + clean_ly 2 + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + industry + 2147483647 + -1 + clean_ly 2 + industry + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + chemic_ly + -1 + -1 + clean_ly 2 + chemic_ly + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 1168 + 48 + Y + + + + chemic_meas + MergeJoin + + Y + + 1 + + none + + + LEFT OUTER + chemic + meas_ly + + id + + + id + + + + + + + + + + + 1168 + 224 + Y + + + + clean + MergeJoin + + Y + + 1 + + none + + + LEFT OUTER + agri_clean + clean_fy + + id + + + id + + + + + + + + + + + 976 + 144 + Y + + + + clean_chemic + MergeJoin + + Y + + 1 + + none + + + LEFT OUTER + clean + chemic_ly + + id + + + id + + + + + + + + + + + 1072 + 144 + Y + + + + clean_fy + TableInput + + Y + + 1 + + none + + + dw bi indoteknik (localhost) + select rpi.id, coalesce(rpi.name, 'Unknown') as industry, +(select sum(aml.price_subtotal) +from account_move am +join res_partner rp on rp.id = am.partner_id +join account_move_line aml on aml.move_id = am.id +join product p on p.product_id = aml.product_id +left join res_partner_industry rpi2 on rpi2.id = rp.industry_id +left join product_public_category ppc on ppc.id = p.parent_category_id +where am.move_type = 'out_invoice' +and am.state = 'posted' +and am."date" between get_first_day_current_year()::date and get_now()::date +and ppc.id = 2246 and rpi2.id = rpi.id) as clean_fy +from res_partner_industry rpi +order by rpi.id + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + agri_fy 2 + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + industry + 2147483647 + -1 + agri_fy 2 + industry + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + clean_fy + -1 + -1 + agri_fy 2 + clean_fy + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 1072 + 48 + Y + + + + clean_ly + TableInput + + Y + + 1 + + none + + + dw bi indoteknik (localhost) + select rpi.id, coalesce(rpi.name, 'Unknown') as industry, +(select sum(aml.price_subtotal) +from account_move am +join res_partner rp on rp.id = am.partner_id +join account_move_line aml on aml.move_id = am.id +join product p on p.product_id = aml.product_id +left join res_partner_industry rpi2 on rpi2.id = rp.industry_id +left join product_public_category ppc on ppc.id = p.parent_category_id +where am.move_type = 'out_invoice' +and am.state = 'posted' +and am."date" between get_first_day_last_year()::date and get_now_last_year()::date +and ppc.id = 2246 and rpi2.id = rpi.id) as clean_ly +from res_partner_industry rpi +order by rpi.id + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + agri_ly 2 + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + industry + 2147483647 + -1 + agri_ly 2 + industry + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + clean_ly + -1 + -1 + agri_ly 2 + clean_ly + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 976 + 48 + Y + + + + elec + MergeJoin + + Y + + 1 + + none + + + LEFT OUTER + meas_elec + elec_fy + + id + + + id + + + + + + + + + + + 864 + 224 + Y + + + + elec_fy + TableInput + + Y + + 1 + + none + + + dw bi indoteknik (localhost) + select rpi.id, coalesce(rpi.name, 'Unknown') as industry, +(select sum(aml.price_subtotal) +from account_move am +join res_partner rp on rp.id = am.partner_id +join account_move_line aml on aml.move_id = am.id +join product p on p.product_id = aml.product_id +left join res_partner_industry rpi2 on rpi2.id = rp.industry_id +left join product_public_category ppc on ppc.id = p.parent_category_id +where am.move_type = 'out_invoice' +and am.state = 'posted' +and am."date" between get_first_day_current_year()::date and get_now()::date +and ppc.id = 2354 and rpi2.id = rpi.id) as elec_fy +from res_partner_industry rpi +order by rpi.id + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + meas_fy 2 + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + industry + 2147483647 + -1 + meas_fy 2 + industry + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + elec_fy + -1 + -1 + meas_fy 2 + elec_fy + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 864 + 320 + Y + + + + elec_log + MergeJoin + + Y + + 1 + + none + + + LEFT OUTER + elec + log_ly + + id + + + id + + + + + + + + + + + 768 + 224 + Y + + + + elec_ly + TableInput + + Y + + 1 + + none + + + dw bi indoteknik (localhost) + select rpi.id, coalesce(rpi.name, 'Unknown') as industry, +(select sum(aml.price_subtotal) +from account_move am +join res_partner rp on rp.id = am.partner_id +join account_move_line aml on aml.move_id = am.id +join product p on p.product_id = aml.product_id +left join res_partner_industry rpi2 on rpi2.id = rp.industry_id +left join product_public_category ppc on ppc.id = p.parent_category_id +where am.move_type = 'out_invoice' +and am.state = 'posted' +and am."date" between get_first_day_last_year()::date and get_now_last_year()::date +and ppc.id = 2354 and rpi2.id = rpi.id) as elec_ly +from res_partner_industry rpi +order by rpi.id + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + meas_ly 2 + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + industry + 2147483647 + -1 + meas_ly 2 + industry + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + elec_ly + -1 + -1 + meas_ly 2 + elec_ly + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 960 + 320 + Y + + + + hore + MergeJoin + + Y + + 1 + + none + + + LEFT OUTER + acc_hore + hore_fy + + id + + + id + + + + + + + + + + + 80 + 320 + Y + + + + hore_fy + TableInput + + Y + + 1 + + none + + + dw bi indoteknik (localhost) + select rpi.id, coalesce(rpi.name, 'Unknown') as industry, +(select sum(aml.price_subtotal) +from account_move am +join res_partner rp on rp.id = am.partner_id +join account_move_line aml on aml.move_id = am.id +join product p on p.product_id = aml.product_id +left join res_partner_industry rpi2 on rpi2.id = rp.industry_id +left join product_public_category ppc on ppc.id = p.parent_category_id +where am.move_type = 'out_invoice' +and am.state = 'posted' +and am."date" between get_first_day_current_year()::date and get_now()::date +and ppc.id = 2477 and rpi2.id = rpi.id) as hore_fy +from res_partner_industry rpi +order by rpi.id + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + acc_fy 2 + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + industry + 2147483647 + -1 + acc_fy 2 + industry + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + hore_fy + -1 + -1 + acc_fy 2 + hore_fy + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 80 + 224 + Y + + + + hore_ly + TableInput + + Y + + 1 + + none + + + dw bi indoteknik (localhost) + select rpi.id, coalesce(rpi.name, 'Unknown') as industry, +(select sum(aml.price_subtotal) +from account_move am +join res_partner rp on rp.id = am.partner_id +join account_move_line aml on aml.move_id = am.id +join product p on p.product_id = aml.product_id +left join res_partner_industry rpi2 on rpi2.id = rp.industry_id +left join product_public_category ppc on ppc.id = p.parent_category_id +where am.move_type = 'out_invoice' +and am.state = 'posted' +and am."date" between get_first_day_last_year()::date and get_now_last_year()::date +and ppc.id = 2477 and rpi2.id = rpi.id) as hore_ly +from res_partner_industry rpi +order by rpi.id + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + acc_ly 2 + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + industry + 2147483647 + -1 + acc_ly 2 + industry + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + hore_ly + -1 + -1 + acc_ly 2 + hore_ly + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 192 + 320 + Y + + + + log + MergeJoin + + Y + + 1 + + none + + + LEFT OUTER + elec_log + log_fy + + id + + + id + + + + + + + + + + + 672 + 224 + Y + + + + log_fy + TableInput + + Y + + 1 + + none + + + dw bi indoteknik (localhost) + select rpi.id, coalesce(rpi.name, 'Unknown') as industry, +(select sum(aml.price_subtotal) +from account_move am +join res_partner rp on rp.id = am.partner_id +join account_move_line aml on aml.move_id = am.id +join product p on p.product_id = aml.product_id +left join res_partner_industry rpi2 on rpi2.id = rp.industry_id +left join product_public_category ppc on ppc.id = p.parent_category_id +where am.move_type = 'out_invoice' +and am.state = 'posted' +and am."date" between get_first_day_current_year()::date and get_now()::date +and ppc.id = 2394 and rpi2.id = rpi.id) as log_fy +from res_partner_industry rpi +order by rpi.id + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + elec_fy 2 + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + industry + 2147483647 + -1 + elec_fy 2 + industry + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + log_fy + -1 + -1 + elec_fy 2 + log_fy + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 672 + 320 + Y + + + + log_ly + TableInput + + Y + + 1 + + none + + + dw bi indoteknik (localhost) + select rpi.id, coalesce(rpi.name, 'Unknown') as industry, +(select sum(aml.price_subtotal) +from account_move am +join res_partner rp on rp.id = am.partner_id +join account_move_line aml on aml.move_id = am.id +join product p on p.product_id = aml.product_id +left join res_partner_industry rpi2 on rpi2.id = rp.industry_id +left join product_public_category ppc on ppc.id = p.parent_category_id +where am.move_type = 'out_invoice' +and am.state = 'posted' +and am."date" between get_first_day_last_year()::date and get_now_last_year()::date +and ppc.id = 2394 and rpi2.id = rpi.id) as log_ly +from res_partner_industry rpi +order by rpi.id + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + elec_ly 2 + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + industry + 2147483647 + -1 + elec_ly 2 + industry + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + log_ly + -1 + -1 + elec_ly 2 + log_ly + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 768 + 320 + Y + + + + log_office + MergeJoin + + Y + + 1 + + none + + + LEFT OUTER + log + office_ly + + id + + + id + + + + + + + + + + + 576 + 224 + Y + + + + machine + MergeJoin + + Y + + 1 + + none + + + LEFT OUTER + tools_machine + machine_fy + + id + + + id + + + + + + + + + + + 544 + 144 + Y + + + + machine_agri + MergeJoin + + Y + + 1 + + none + + + LEFT OUTER + machine + agri_ly + + id + + + id + + + + + + + + + + + 672 + 144 + Y + + + + machine_fy + TableInput + + Y + + 1 + + none + + + dw bi indoteknik (localhost) + select rpi.id, coalesce(rpi.name, 'Unknown') as industry, +(select sum(aml.price_subtotal) +from account_move am +join res_partner rp on rp.id = am.partner_id +join account_move_line aml on aml.move_id = am.id +join product p on p.product_id = aml.product_id +left join res_partner_industry rpi2 on rpi2.id = rp.industry_id +left join product_public_category ppc on ppc.id = p.parent_category_id +where am.move_type = 'out_invoice' +and am.state = 'posted' +and am."date" between get_first_day_current_year()::date and get_now()::date +and ppc.id = 2161 and rpi2.id = rpi.id) as machine_fy +from res_partner_industry rpi +order by rpi.id + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + machine_fy + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + industry + 2147483647 + -1 + machine_fy + industry + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + machine_fy + -1 + -1 + machine_fy + machine_fy + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 672 + 48 + Y + + + + machine_ly + TableInput + + Y + + 1 + + none + + + dw bi indoteknik (localhost) + select rpi.id, coalesce(rpi.name, 'Unknown') as industry, +(select sum(aml.price_subtotal) +from account_move am +join res_partner rp on rp.id = am.partner_id +join account_move_line aml on aml.move_id = am.id +join product p on p.product_id = aml.product_id +left join res_partner_industry rpi2 on rpi2.id = rp.industry_id +left join product_public_category ppc on ppc.id = p.parent_category_id +where am.move_type = 'out_invoice' +and am.state = 'posted' +and am."date" between get_first_day_last_year()::date and get_now_last_year()::date +and ppc.id = 2161 and rpi2.id = rpi.id) as machine_ly +from res_partner_industry rpi +order by rpi.id + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + machine_ly + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + industry + 2147483647 + -1 + machine_ly + industry + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + machine_ly + -1 + -1 + machine_ly + machine_ly + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 544 + 48 + Y + + + + meas + MergeJoin + + Y + + 1 + + none + + + LEFT OUTER + chemic_meas + meas_fy + + id + + + id + + + + + + + + + + + 1072 + 224 + Y + + + + meas_elec + MergeJoin + + Y + + 1 + + none + + + LEFT OUTER + meas + elec_ly + + id + + + id + + + + + + + + + + + 960 + 224 + Y + + + + meas_fy + TableInput + + Y + + 1 + + none + + + dw bi indoteknik (localhost) + select rpi.id, coalesce(rpi.name, 'Unknown') as industry, +(select sum(aml.price_subtotal) +from account_move am +join res_partner rp on rp.id = am.partner_id +join account_move_line aml on aml.move_id = am.id +join product p on p.product_id = aml.product_id +left join res_partner_industry rpi2 on rpi2.id = rp.industry_id +left join product_public_category ppc on ppc.id = p.parent_category_id +where am.move_type = 'out_invoice' +and am.state = 'posted' +and am."date" between get_first_day_current_year()::date and get_now()::date +and ppc.id = 2315 and rpi2.id = rpi.id) as meas_fy +from res_partner_industry rpi +order by rpi.id + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + chemic_fy 2 + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + industry + 2147483647 + -1 + chemic_fy 2 + industry + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + meas_fy + -1 + -1 + chemic_fy 2 + meas_fy + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 1072 + 320 + Y + + + + meas_ly + TableInput + + Y + + 1 + + none + + + dw bi indoteknik (localhost) + select rpi.id, coalesce(rpi.name, 'Unknown') as industry, +(select sum(aml.price_subtotal) +from account_move am +join res_partner rp on rp.id = am.partner_id +join account_move_line aml on aml.move_id = am.id +join product p on p.product_id = aml.product_id +left join res_partner_industry rpi2 on rpi2.id = rp.industry_id +left join product_public_category ppc on ppc.id = p.parent_category_id +where am.move_type = 'out_invoice' +and am.state = 'posted' +and am."date" between get_first_day_last_year()::date and get_now_last_year()::date +and ppc.id = 2315 and rpi2.id = rpi.id) as meas_ly +from res_partner_industry rpi +order by rpi.id + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + chemic_ly 2 + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + industry + 2147483647 + -1 + chemic_ly 2 + industry + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + meas_ly + -1 + -1 + chemic_ly 2 + meas_ly + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 1168 + 320 + Y + + + + office + MergeJoin + + Y + + 1 + + none + + + LEFT OUTER + log_office + office_fy + + id + + + id + + + + + + + + + + + 480 + 224 + Y + + + + office_acc + MergeJoin + + Y + + 1 + + none + + + LEFT OUTER + office + acc_ly + + id + + + id + + + + + + + + + + + 384 + 224 + Y + + + + office_fy + TableInput + + Y + + 1 + + none + + + dw bi indoteknik (localhost) + select rpi.id, coalesce(rpi.name, 'Unknown') as industry, +(select sum(aml.price_subtotal) +from account_move am +join res_partner rp on rp.id = am.partner_id +join account_move_line aml on aml.move_id = am.id +join product p on p.product_id = aml.product_id +left join res_partner_industry rpi2 on rpi2.id = rp.industry_id +left join product_public_category ppc on ppc.id = p.parent_category_id +where am.move_type = 'out_invoice' +and am.state = 'posted' +and am."date" between get_first_day_current_year()::date and get_now()::date +and ppc.id = 2420 and rpi2.id = rpi.id) as office_fy +from res_partner_industry rpi +order by rpi.id + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + log_fy 2 + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + industry + 2147483647 + -1 + log_fy 2 + industry + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + office_fy + -1 + -1 + log_fy 2 + office_fy + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 480 + 320 + Y + + + + office_ly + TableInput + + Y + + 1 + + none + + + dw bi indoteknik (localhost) + select rpi.id, coalesce(rpi.name, 'Unknown') as industry, +(select sum(aml.price_subtotal) +from account_move am +join res_partner rp on rp.id = am.partner_id +join account_move_line aml on aml.move_id = am.id +join product p on p.product_id = aml.product_id +left join res_partner_industry rpi2 on rpi2.id = rp.industry_id +left join product_public_category ppc on ppc.id = p.parent_category_id +where am.move_type = 'out_invoice' +and am.state = 'posted' +and am."date" between get_first_day_last_year()::date and get_now_last_year()::date +and ppc.id = 2420 and rpi2.id = rpi.id) as office_ly +from res_partner_industry rpi +order by rpi.id + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + log_ly 2 + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + industry + 2147483647 + -1 + log_ly 2 + industry + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + office_ly + -1 + -1 + log_ly 2 + office_ly + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 576 + 320 + Y + + + + safety + MergeJoin + + Y + + 1 + + none + + + LEFT OUTER + safety_ly + safety_fy + + id + + + id + + + + + + + + + + + 64 + 144 + Y + + + + safety_fy + TableInput + + Y + + 1 + + none + + + dw bi indoteknik (localhost) + select rpi.id, coalesce(rpi.name, 'Unknown') as industry, +(select sum(aml.price_subtotal) +from account_move am +join res_partner rp on rp.id = am.partner_id +join account_move_line aml on aml.move_id = am.id +join product p on p.product_id = aml.product_id +left join res_partner_industry rpi2 on rpi2.id = rp.industry_id +left join product_public_category ppc on ppc.id = p.parent_category_id +where am.move_type = 'out_invoice' +and am.state = 'posted' +and am."date" between get_first_day_current_year()::date and get_now()::date +and ppc.id = 2040 and rpi2.id = rpi.id) as safety_fy +from res_partner_industry rpi +order by rpi.id + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + safety_ly + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + industry + 2147483647 + -1 + safety_ly + industry + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + safety_ly + -1 + -1 + safety_ly + safety_ly + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + Integer + normal + id_1 + 9 + 0 + safety_fy + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + industry_1 + 2147483647 + -1 + safety_fy + industry + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + safety_fy + -1 + -1 + safety_fy + safety_fy + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 192 + 48 + Y + + + + safety_ly + TableInput + + Y + + 1 + + none + + + dw bi indoteknik (localhost) + select rpi.id, coalesce(rpi.name, 'Unknown') as industry, +(select sum(aml.price_subtotal) +from account_move am +join res_partner rp on rp.id = am.partner_id +join account_move_line aml on aml.move_id = am.id +join product p on p.product_id = aml.product_id +left join res_partner_industry rpi2 on rpi2.id = rp.industry_id +left join product_public_category ppc on ppc.id = p.parent_category_id +where am.move_type = 'out_invoice' +and am.state = 'posted' +and am."date" between get_first_day_last_year()::date and get_now_last_year()::date +and ppc.id = 2040 and rpi2.id = rpi.id) as safety_ly +from res_partner_industry rpi +order by rpi.id + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + safety_ly + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + industry + 2147483647 + -1 + safety_ly + industry + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + safety_ly + -1 + -1 + safety_ly + safety_ly + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 64 + 48 + Y + + + + safety_tools + MergeJoin + + Y + + 1 + + none + + + LEFT OUTER + safety + tools_ly + + id + + + id + + + + + + + + + + + 192 + 144 + Y + + + + tools + MergeJoin + + Y + + 1 + + none + + + LEFT OUTER + safety_tools + tools_fy + + id + + + id + + + + + + + + + + + 320 + 144 + Y + + + + tools_fy + TableInput + + Y + + 1 + + none + + + dw bi indoteknik (localhost) + select rpi.id, coalesce(rpi.name, 'Unknown') as industry, +(select sum(aml.price_subtotal) +from account_move am +join res_partner rp on rp.id = am.partner_id +join account_move_line aml on aml.move_id = am.id +join product p on p.product_id = aml.product_id +left join res_partner_industry rpi2 on rpi2.id = rp.industry_id +left join product_public_category ppc on ppc.id = p.parent_category_id +where am.move_type = 'out_invoice' +and am.state = 'posted' +and am."date" between get_first_day_current_year()::date and get_now()::date +and ppc.id = 2097 and rpi2.id = rpi.id) as tools_fy +from res_partner_industry rpi +order by rpi.id + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + tools_fy + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + industry + 2147483647 + -1 + tools_fy + industry + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + tools_fy + -1 + -1 + tools_fy + tools_fy + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 432 + 48 + Y + + + + tools_ly + TableInput + + Y + + 1 + + none + + + dw bi indoteknik (localhost) + select rpi.id, coalesce(rpi.name, 'Unknown') as industry, +(select sum(aml.price_subtotal) +from account_move am +join res_partner rp on rp.id = am.partner_id +join account_move_line aml on aml.move_id = am.id +join product p on p.product_id = aml.product_id +left join res_partner_industry rpi2 on rpi2.id = rp.industry_id +left join product_public_category ppc on ppc.id = p.parent_category_id +where am.move_type = 'out_invoice' +and am.state = 'posted' +and am."date" between get_first_day_last_year()::date and get_now_last_year()::date +and ppc.id = 2097 and rpi2.id = rpi.id) as tools_ly +from res_partner_industry rpi +order by rpi.id + 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 + industry + 2147483647 + -1 + Table input + industry + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + tools_ly + -1 + -1 + Table input + tools_ly + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 320 + 48 + Y + + + + tools_machine + MergeJoin + + Y + + 1 + + none + + + LEFT OUTER + tools + machine_ly + + id + + + id + + + + + + + + + + + 432 + 144 + Y + + + + + + + N + + diff --git a/calculate_percent_lob_category.ktr b/calculate_percent_lob_category.ktr new file mode 100644 index 0000000..8499efd --- /dev/null +++ b/calculate_percent_lob_category.ktr @@ -0,0 +1,772 @@ + + + + calculate_percent_lob_category + + + + 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/09/05 11:56:05.480 + - + 2024/09/05 11:56:05.480 + + 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 + + + + + + Table input + Insert / update + Y + + + + Table input + TableInput + + Y + + 1 + + none + + + dw bi indoteknik + select industry_id, category_id, industry, category, 'p.%' as year_date, +( + ((select tlcv2.value from t_lob_category_value tlcv2 + where tlcv2.industry_id=tlcv.industry_id + and tlcv2.category_id=tlcv.category_id + and tlcv2.year_date=get_now_year())- + (select tlcv2.value from t_lob_category_value tlcv2 + where tlcv2.industry_id=tlcv.industry_id + and tlcv2.category_id=tlcv.category_id + and tlcv2.year_date=get_last_year()))/ + (select tlcv2.value from t_lob_category_value tlcv2 + where tlcv2.industry_id=tlcv.industry_id + and tlcv2.category_id=tlcv.category_id + and tlcv2.year_date=get_last_year())* + 100 +) as value +from t_lob_category_value tlcv +where tlcv.year_date = get_last_year() + 0 + + N + N + N + N + + + Integer + normal + industry_id + 9 + 0 + Table input + industry_id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + Integer + normal + category_id + 9 + 0 + Table input + category_id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + industry + 2147483647 + -1 + Table input + industry + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + category + 2147483647 + -1 + Table input + category + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + year_date + 2147483647 + -1 + Table input + year_date + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + value + -1 + -1 + Table input + value + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 112 + 64 + Y + + + + Insert / update + InsertUpdate + + Y + + 1 + + none + + + dw bi indoteknik + 100 + N + + public +
t_lob_category_value
+ + industry_id + industry_id + = + + + + category_id + category_id + = + + + + year_date + year_date + = + + + + industry_id + industry_id + N + + + category_id + category_id + N + + + industry + industry + Y + + + category + category + Y + + + year_date + year_date + N + + + value + value + Y + + + + + + + + + + + + 288 + 64 + Y + + + + + + + N + + diff --git a/import_all_data_bi_indoteknik.kjb b/import_all_data_bi_indoteknik.kjb index e6c4c2a..0a1c224 100644 --- a/import_all_data_bi_indoteknik.kjb +++ b/import_all_data_bi_indoteknik.kjb @@ -1697,6 +1697,45 @@ 528 + + calculate_lob_category + + TRANS + + filename + + /home/stephan/transformation-job-bi/calculate_lob_category_v2.ktr + + N + N + N + N + N + N + + + N + N + Basic + N + + N + Y + N + N + N + Pentaho local + N + + Y + + N + Y + 0 + 1088 + 640 + + @@ -2023,6 +2062,15 @@ Y N + + refresh_materialized_view + calculate_lob_category + 0 + 0 + Y + Y + N + diff --git a/import_partner_one_time.ktr b/import_partner_one_time.ktr index 8d7677d..650ed89 100644 --- a/import_partner_one_time.ktr +++ b/import_partner_one_time.ktr @@ -437,14 +437,14 @@ - dw bi indoteknik + dw bi indoteknik (localhost) localhost POSTGRESQL Native dw_bi_indoteknik - 5703 - metabase - Encrypted 2be98dfba28d7bf86853c9f58cfc4f889 + 5432 + odoo + Encrypted 2be98afc82bdfd8dd9a2ca45acc83f6c3 @@ -463,7 +463,7 @@ PORT_NUMBER - 5703 + 5432 PRESERVE_RESERVED_WORD_CASE @@ -479,7 +479,7 @@ SUPPORTS_TIMESTAMP_DATA_TYPE - Y + N USE_POOLING @@ -488,12 +488,12 @@ - erp indoteknik production - localhost + erp indoteknik production (localhost) + 10.148.0.3 POSTGRESQL Native erp_indoteknik - 5901 + 5432 odoo Encrypted 2be98afc82bdfd8dd9a2ca45acc83f6c3 @@ -514,7 +514,7 @@ PORT_NUMBER - 5901 + 5432 PRESERVE_RESERVED_WORD_CASE @@ -530,7 +530,7 @@ SUPPORTS_TIMESTAMP_DATA_TYPE - Y + N USE_POOLING @@ -540,18 +540,13 @@ - Table input - Insert / update - Y - - - Table input 2 - Insert / update 2 + Table input 3 + Insert / update 3 Y - Insert / update + Insert / update 3 InsertUpdate Y @@ -561,7 +556,7 @@ none - dw bi indoteknik + dw bi indoteknik (localhost) 100 N @@ -663,13 +658,13 @@ - 368 - 128 + 384 + 80 Y - Table input + Table input 3 TableInput Y @@ -679,654 +674,18 @@ none - erp indoteknik production + erp indoteknik production (localhost) select id, name, display_name, active, email, industry_id, state_id, kota_id, kecamatan_id, kelurahan_id, is_potential, parent_id, email, phone, mobile, pareto_status, group_partner_id from res_partner -where pareto_status is not null -and pareto_status not in('NP') and group_partner_id is not null ---group_partner_id is not null ---write_date >= (now() - '5 days'::interval) - 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 - - - String - normal - display_name - 2147483647 - -1 - Table input - display_name - - . - , - - 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 - - - String - normal - email - 2147483647 - -1 - Table input - email - - . - , - - none - N - Y - 0 - N - N - N - en_ID - Asia/Bangkok - N - - - Integer - normal - industry_id - 9 - 0 - Table input - industry_id - ####0;-####0 - . - , - - none - N - Y - 0 - N - N - N - en_ID - Asia/Bangkok - N - - - Integer - normal - state_id - 9 - 0 - Table input - state_id - ####0;-####0 - . - , - - none - N - Y - 0 - N - N - N - en_ID - Asia/Bangkok - N - - - Integer - normal - kota_id - 9 - 0 - Table input - kota_id - ####0;-####0 - . - , - - none - N - Y - 0 - N - N - N - en_ID - Asia/Bangkok - N - - - Integer - normal - kecamatan_id - 9 - 0 - Table input - kecamatan_id - ####0;-####0 - . - , - - none - N - Y - 0 - N - N - N - en_ID - Asia/Bangkok - N - - - Integer - normal - kelurahan_id - 9 - 0 - Table input - kelurahan_id - ####0;-####0 - . - , - - none - N - Y - 0 - N - N - N - en_ID - Asia/Bangkok - N - - - Boolean - normal - is_potential - -1 - -1 - Table input - is_potential - - . - , - - none - N - Y - 0 - N - N - N - en_ID - Asia/Bangkok - N - - - Integer - normal - parent_id - 9 - 0 - Table input - parent_id - ####0;-####0 - . - , - - none - N - Y - 0 - N - N - N - en_ID - Asia/Bangkok - N - - - String - normal - email_1 - 2147483647 - -1 - Table input - email - - . - , - - none - N - Y - 0 - N - N - N - en_ID - Asia/Bangkok - N - - - String - normal - phone - 2147483647 - -1 - Table input - phone - - . - , - - none - N - Y - 0 - N - N - N - en_ID - Asia/Bangkok - N - - - String - normal - mobile - 2147483647 - -1 - Table input - mobile - - . - , - - none - N - Y - 0 - N - N - N - en_ID - Asia/Bangkok - N - - - String - normal - pareto_status - 2147483647 - -1 - Table input - pareto_status - - . - , - - none - N - Y - 0 - N - N - N - en_ID - Asia/Bangkok - N - - - Integer - normal - group_partner_id - 9 - 0 - Table input - group_partner_id - ####0;-####0 - . - , - - none - N - Y - 0 - N - N - N - en_ID - Asia/Bangkok - N - - - - - - - - - - - - 176 - 128 - Y - - - - Table input 2 - TableInput - - Y - - 1 - - none - - - erp indoteknik production - select * from group_partner +--where write_date >= (now() - '5 days'::interval) 0 N N N N - - - Integer - normal - id - 9 - 0 - Table input 2 - id - ####0;-####0 - . - , - - none - N - Y - 0 - N - N - N - en_ID - Asia/Bangkok - N - - - String - normal - name - 2147483647 - -1 - Table input 2 - name - - . - , - - none - N - Y - 0 - N - N - N - en_ID - Asia/Bangkok - N - - - Integer - normal - create_uid - 9 - 0 - Table input 2 - create_uid - ####0;-####0 - . - , - - none - N - Y - 0 - N - N - N - en_ID - Asia/Bangkok - N - - - Timestamp - normal - create_date - 6 - -1 - Table input 2 - create_date - - . - , - - none - N - Y - 0 - N - N - N - en_ID - Asia/Bangkok - N - - - Integer - normal - write_uid - 9 - 0 - Table input 2 - write_uid - ####0;-####0 - . - , - - none - N - Y - 0 - N - N - N - en_ID - Asia/Bangkok - N - - - Timestamp - normal - write_date - 6 - -1 - Table input 2 - write_date - - . - , - - none - N - Y - 0 - N - N - N - en_ID - Asia/Bangkok - N - - - - - - - - - - - - 176 - 240 - Y - - - - Insert / update 2 - InsertUpdate - - Y - - 1 - - none - - - dw bi indoteknik - 100 - N - - public - group_partner
- - id - id - = - - - - id - id - N - - - name - name - Y - - - create_uid - create_uid - Y - - - create_date - create_date - Y - - - write_uid - write_uid - Y - - - write_date - write_date - Y - - + @@ -1336,8 +695,8 @@ and pareto_status not in('NP') and group_partner_id is not null - 368 - 240 + 192 + 80 Y -- cgit v1.2.3