From 4dd50edc2a5023f737b9ba16605d967096a1e2fd Mon Sep 17 00:00:00 2001 From: stephanchrst Date: Tue, 13 May 2025 17:15:41 +0700 Subject: calculate bi marketing --- calculate_bi_marketing_md.ktr | 1640 ++++++++++++++++++++++++++++++++++++++++- 1 file changed, 1621 insertions(+), 19 deletions(-) diff --git a/calculate_bi_marketing_md.ktr b/calculate_bi_marketing_md.ktr index 5ff1f94..83e522e 100644 --- a/calculate_bi_marketing_md.ktr +++ b/calculate_bi_marketing_md.ktr @@ -435,6 +435,57 @@ + + 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 @@ -487,6 +538,121 @@ + + parent category + Sort rows + Y + + + last year + Sort rows 2 + Y + + + Sort rows + Merge join + Y + + + Sort rows 2 + Merge join + Y + + + this year + Sort rows 3 + Y + + + Merge join + Merge join 2 + Y + + + Sort rows 3 + Merge join 2 + Y + + + category target + Sort rows 4 + Y + + + Merge join 2 + Merge join 3 + Y + + + Sort rows 4 + Merge join 3 + Y + + + trx last year + Sort rows 5 + Y + + + trx this year + Sort rows 6 + Y + + + Merge join 3 + Merge join 4 + Y + + + Sort rows 5 + Merge join 4 + Y + + + Merge join 4 + Merge join 5 + Y + + + Sort rows 6 + Merge join 5 + Y + + + Select values + Calculator + Y + + + Calculator + Select values 2 + Y + + + last year full + Sort rows 7 + Y + + + Merge join 5 + Merge join 6 + Y + + + Sort rows 7 + Merge join 6 + Y + + + Merge join 6 + Select values + Y + + + Select values 2 + Insert / update + Y + parent category @@ -500,22 +666,27 @@ erp indoteknik production - select ppc.id, name as category -from product_public_category ppc -where ppc.id in( -2040, -2097, -2161, -2222, -2246, -2273, -2315, -2354, -2394, -2420, -2477, -2445 -) + select id, category, now()::timestamp-'7 hours'::interval as history_date +from( + select ppc.id, name as category + from product_public_category ppc + where ppc.id in( + 2040, + 2097, + 2161, + 2222, + 2246, + 2273, + 2315, + 2354, + 2394, + 2420, + 2477, + 2445 + ) + union + select 999999, 'Others' +)a order by id 0 N @@ -529,7 +700,7 @@ where ppc.id in( id 9 0 - Table input + parent category id ####0;-####0 . @@ -552,7 +723,7 @@ where ppc.id in( category 2147483647 -1 - Table input + parent category category . @@ -579,11 +750,1442 @@ where ppc.id in( - 192 + 160 + 288 + Y + + + + last year + TableInput + + Y + + 1 + + none + + + dw bi indoteknik + select coalesce(p.parent_category_id, 999999) as product_category_id, sum(aml.price_subtotal) as last_year +from account_move_line aml +join product p on p.product_id = aml.product_id +join account_move am on am.id = aml.move_id +where 1=1 +and aml.parent_state = 'posted' +and am.move_type = 'out_invoice' +and aml.date between to_timestamp(get_first_day_last_year(), 'YYYY-MM-DD') and to_timestamp(get_now_last_year(), 'YYYY-MM-DD') +group by p.parent_category_id + 0 + + N + N + N + N + + + Integer + normal + product_category_id + 9 + 0 + last year + product_category_id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + BigNumber + normal + last_year + -1 + -1 + last year + last_year + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + + + + + + + + + + 352 + 112 + Y + + + + Sort rows + SortRows + + Y + + 1 + + none + + + %%java.io.tmpdir%% + out + 1000000 + + N + + N + + + id + Y + N + N + 0 + N + + + + + + + + + + + + 256 + 288 + Y + + + + Sort rows 2 + SortRows + + Y + + 1 + + none + + + %%java.io.tmpdir%% + out + 1000000 + + N + + N + + + product_category_id + Y + N + N + 0 + N + + + + + + + + + + + + 352 + 208 + Y + + + + Merge join + MergeJoin + + Y + + 1 + + none + + + LEFT OUTER + Sort rows + Sort rows 2 + + id + + + product_category_id + + + + + + + + + + + 352 + 288 + Y + + + + this year + TableInput + + Y + + 1 + + none + + + dw bi indoteknik + select coalesce(p.parent_category_id, 999999) as product_category_id, sum(aml.price_subtotal) as this_year +from account_move_line aml +join product p on p.product_id = aml.product_id +join account_move am on am.id = aml.move_id +where 1=1 +and aml.parent_state = 'posted' +and am.move_type = 'out_invoice' +and to_char(aml.date, 'YYYY') = get_now_year() +group by p.parent_category_id + 0 + + N + N + N + N + + + Integer + normal + product_category_id + 9 + 0 + this year + product_category_id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + BigNumber + normal + this_year + -1 + -1 + this year + this_year + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + + + + + + + + + + 496 112 Y + + Sort rows 3 + SortRows + + Y + + 1 + + none + + + %%java.io.tmpdir%% + out + 1000000 + + N + + N + + + product_category_id + Y + N + N + 0 + N + + + + + + + + + + + + 496 + 208 + Y + + + + Merge join 2 + MergeJoin + + Y + + 1 + + none + + + LEFT OUTER + Merge join + Sort rows 3 + + id + + + product_category_id + + + + + + + + + + + 496 + 288 + Y + + + + category target + TableInput + + Y + + 1 + + none + + + dw bi indoteknik + select * from category_target + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + Table input + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + String + normal + name + 2147483647 + -1 + Table input + name + + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + BigNumber + normal + target + -1 + -1 + Table input + target + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + + + + + + + + + + 624 + 112 + Y + + + + Sort rows 4 + SortRows + + Y + + 1 + + none + + + %%java.io.tmpdir%% + out + 1000000 + + N + + N + + + id + Y + N + N + 0 + N + + + + + + + + + + + + 624 + 208 + Y + + + + Merge join 3 + MergeJoin + + Y + + 1 + + none + + + LEFT OUTER + Merge join 2 + Sort rows 4 + + id + + + id + + + + + + + + + + + 624 + 288 + Y + + + + Calculator + Calculator + + Y + + 1 + + none + + + Y + + diff_ly + SUBTRACT + this_year + last_year + + Number + -1 + -1 + N + + + + + + + growth_value + DIVIDE + diff_ly + last_year + + Number + -1 + -1 + N + + + + + + + diff_target + SUBTRACT + this_year + target + + Number + -1 + -1 + N + + + + + + + achv_target + DIVIDE + diff_target + target + + Number + -1 + -1 + N + + + + + + + diff_trx + SUBTRACT + trx_this_year + trx_last_year + + Number + -1 + -1 + N + + + + + + + growth_trx + DIVIDE + diff_trx + trx_last_year + + Number + -1 + -1 + N + + + + + + + diff_ly_full + SUBTRACT + this_year + last_year_full + + Number + -1 + -1 + N + + + + + + + growth_value_full + DIVIDE + diff_ly_full + last_year_full + + Number + -1 + -1 + N + + + + + + + + + + + + + + + 864 + 384 + Y + + + + trx last year + TableInput + + Y + + 1 + + none + + + dw bi indoteknik + select coalesce(p.parent_category_id, 999999) as product_category_id, count(distinct aml.move_id) as trx_last_year +from account_move_line aml +join product p on p.product_id = aml.product_id +join account_move am on am.id = aml.move_id +where 1=1 +and aml.parent_state = 'posted' +and am.move_type = 'out_invoice' +and aml.date between to_timestamp(get_first_day_last_year(), 'YYYY-MM-DD') and to_timestamp(get_now_last_year(), 'YYYY-MM-DD') +group by p.parent_category_id + 0 + + N + N + N + N + + + Integer + normal + product_category_id + 9 + 0 + trx last year + product_category_id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Integer + normal + trx_last_year + 15 + 0 + trx last year + trx_last_year + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + + + + + + + + + + 752 + 112 + Y + + + + trx this year + TableInput + + Y + + 1 + + none + + + dw bi indoteknik + select coalesce(p.parent_category_id, 999999) as product_category_id, count(distinct aml.move_id) as trx_this_year +from account_move_line aml +join product p on p.product_id = aml.product_id +join account_move am on am.id = aml.move_id +where 1=1 +and aml.parent_state = 'posted' +and am.move_type = 'out_invoice' +and to_char(aml.date, 'YYYY') = get_now_year() +group by p.parent_category_id + 0 + + N + N + N + N + + + Integer + normal + product_category_id + 9 + 0 + trx this year + product_category_id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Integer + normal + trx_this_year + 15 + 0 + trx this year + trx_this_year + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + + + + + + + + + + 864 + 112 + Y + + + + Sort rows 5 + SortRows + + Y + + 1 + + none + + + %%java.io.tmpdir%% + out + 1000000 + + N + + N + + + product_category_id + Y + N + N + 0 + N + + + + + + + + + + + + 752 + 208 + Y + + + + Sort rows 6 + SortRows + + Y + + 1 + + none + + + %%java.io.tmpdir%% + out + 1000000 + + N + + N + + + product_category_id + Y + N + N + 0 + N + + + + + + + + + + + + 864 + 208 + Y + + + + Merge join 4 + MergeJoin + + Y + + 1 + + none + + + LEFT OUTER + Merge join 3 + Sort rows 5 + + id + + + product_category_id + + + + + + + + + + + 752 + 288 + Y + + + + Merge join 5 + MergeJoin + + N + + 1 + + none + + + LEFT OUTER + Merge join 4 + Sort rows 6 + + id + + + product_category_id + + + + + + + + + + + 864 + 288 + Y + + + + Select values + SelectValues + + Y + + 1 + + none + + + + + id + + + + category + + + + history_date + + + + last_year + + + + this_year + + + + target + + + + trx_last_year + + + + trx_this_year + + + + last_year_full + + + N + + + + + + + + + + + 992 + 384 + Y + + + + Select values 2 + SelectValues + + Y + + 1 + + none + + + + + id + + + + category + + + + history_date + + + + last_year + + + + this_year + + + + target + + + + trx_last_year + + + + trx_this_year + + + + growth_value + + + + achv_target + + + + growth_trx + + + + growth_value_full + + + N + + + + + + + + + + + 752 + 384 + Y + + + + Insert / update + InsertUpdate + + Y + + 1 + + none + + + dw bi indoteknik + 100 + N + + public + t_omset_by_parent_category
+ + id + id + = + + + + id + id + N + + + category + category + Y + + + history_date + history_date + Y + + + last_year + last_year + Y + + + this_year + this_year + Y + + + target + target + Y + + + trx_last_year + trx_last_year + Y + + + trx_this_year + trx_this_year + Y + + + growth_value + growth_value + Y + + + achv_target + achv_target + Y + + + growth_trx + growth_trx + Y + + + growth_value_full + growth_value_full + Y + + + + + + + + + + + + 624 + 384 + Y + + + + last year full + TableInput + + Y + + 1 + + none + + + dw bi indoteknik + select coalesce(p.parent_category_id, 999999) as product_category_id, sum(aml.price_subtotal) as last_year_full +from account_move_line aml +join product p on p.product_id = aml.product_id +join account_move am on am.id = aml.move_id +where 1=1 +and aml.parent_state = 'posted' +and am.move_type = 'out_invoice' +and to_char(aml.date, 'YYYY') = get_last_year() +group by p.parent_category_id + 0 + + N + N + N + N + + + Integer + normal + product_category_id + 9 + 0 + last year full + product_category_id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + BigNumber + normal + last_year_full + -1 + -1 + last year full + last_year_full + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + + + + + + + + + + 992 + 112 + Y + + + + Sort rows 7 + SortRows + + Y + + 1 + + none + + + %%java.io.tmpdir%% + out + 1000000 + + N + + N + + + product_category_id + Y + N + N + 0 + N + + + + + + + + + + + + 992 + 208 + Y + + + + Merge join 6 + MergeJoin + + Y + + 1 + + none + + + LEFT OUTER + Merge join 5 + Sort rows 7 + + id + + + product_category_id + + + + + + + + + + + 992 + 288 + Y + + -- cgit v1.2.3