From ee0a77e6d08755b517a90396b91034fdb53ecb7e Mon Sep 17 00:00:00 2001 From: stephanchrst Date: Tue, 12 Sep 2023 18:54:09 +0700 Subject: add count product and vendor in purchase order --- calculate_price_after_disc.ktr | 1463 +++++++++++++++++++++++++++++++------ import_all_data_bi_indoteknik.kjb | 144 ++++ 2 files changed, 1366 insertions(+), 241 deletions(-) diff --git a/calculate_price_after_disc.ktr b/calculate_price_after_disc.ktr index f3798dd..86705b1 100644 --- a/calculate_price_after_disc.ktr +++ b/calculate_price_after_disc.ktr @@ -499,6 +499,101 @@ Calculator 2 + filter group1 + Y + + + filter group1 + select group1 + Y + + + filter group1 + filter group2 + Y + + + filter group2 + select group2 + Y + + + filter group2 + filter group3 + Y + + + filter group3 + select group3 + Y + + + filter group3 + filter group4 + Y + + + filter group4 + select group4 + Y + + + filter group4 + filter group5 + Y + + + filter group5 + select group5 + Y + + + filter group5 + Write to log + Y + + + select group1 + Append streams + Y + + + select group2 + Append streams + Y + + + select group3 + Append streams 2 + Y + + + Append streams + Append streams 2 + Y + + + select group4 + Append streams 3 + Y + + + Append streams 2 + Append streams 3 + Y + + + select group5 + Append streams 4 + Y + + + Append streams 3 + Append streams 4 + Y + + + Append streams 4 Insert / update Y @@ -1043,264 +1138,74 @@ name Y - - purchase_price - purchase_price - Y - pricing_group pricing_group Y - markup_std1 - markup_std1 - Y - - - markup_std2 - markup_std2 - Y - - - markup_std3 - markup_std3 - Y - - - markup_std4 - markup_std4 - Y - - - markup_std5 - markup_std5 - Y - - - markup_group1 - markup_group1 - Y - - - markup_group2 - markup_group2 - Y - - - markup_group3 - markup_group3 - Y - - - markup_group4 - markup_group4 - Y - - - markup_group5 - markup_group5 - Y - - - markup_tier2_1 - markup_tier2_1 - Y - - - markup_tier2_2 - markup_tier2_2 - Y - - - markup_tier2_3 - markup_tier2_3 - Y - - - markup_tier2_4 - markup_tier2_4 - Y - - - markup_tier2_5 - markup_tier2_5 - Y - - - markup_tier3_1 - markup_tier3_1 - Y - - - markup_tier3_2 - markup_tier3_2 - Y - - - markup_tier3_3 - markup_tier3_3 - Y - - - markup_tier3_4 - markup_tier3_4 - Y - - - markup_tier3_5 - markup_tier3_5 - Y - - - price_after_disc_1 - price_after_disc_1 - Y - - - price_after_disc_2 - price_after_disc_2 - Y - - - price_after_disc_3 - price_after_disc_3 - Y - - - price_after_disc_4 - price_after_disc_4 - Y - - - price_after_disc_5 - price_after_disc_5 - Y - - - price_before_disc1 - price_before_disc1 - Y - - - price_before_disc2 - price_before_disc2 - Y - - - price_before_disc3 - price_before_disc3 - Y - - - price_before_disc4 - price_before_disc4 - Y - - - price_before_disc5 - price_before_disc5 - Y - - - price_tier2_1 - price_tier2_1 - Y - - - price_tier2_2 - price_tier2_2 - Y - - - price_tier2_3 - price_tier2_3 - Y - - - price_tier2_4 - price_tier2_4 - Y - - - price_tier2_5 - price_tier2_5 - Y - - - price_tier3_1 - price_tier3_1 - Y - - - price_tier3_2 - price_tier3_2 - Y - - - price_tier3_3 - price_tier3_3 + purchase_price + purchase_price Y - price_tier3_4 - price_tier3_4 + markup_std + markup_std Y - price_tier3_5 - price_tier3_5 + markup_group + markup_group Y - price_tier4_1 - price_tier4_1 + markup_tier2 + markup_tier2 Y - price_tier4_2 - price_tier4_2 + markup_tier3 + markup_tier3 Y - price_tier4_3 - price_tier4_3 + markup_tier4 + markup_tier4 Y - price_tier4_4 - price_tier4_4 + markup_tier5 + markup_tier5 Y - price_tier4_5 - price_tier4_5 + price_after_disc + price_after_disc Y - price_tier5_1 - price_tier5_1 + price_before_disc + price_before_disc Y - price_tier5_2 - price_tier5_2 + price_tier2 + price_tier2 Y - price_tier5_3 - price_tier5_3 + price_tier3 + price_tier3 Y - price_tier5_4 - price_tier5_4 + price_tier4 + price_tier4 Y - price_tier5_5 - price_tier5_5 + price_tier5 + price_tier5 Y @@ -1313,8 +1218,8 @@ - 720 - 64 + 1312 + 544 Y @@ -1330,7 +1235,14 @@ dw bi indoteknik - select mpp.product_id, p.item_code, p."name", mpp.purchase_price, b.pricing_group, + select ppd.product_id, p.item_code, p.name, b.pricing_group, +case + when human_last_update is null then coalesce(system_price, 0) + when system_last_update is null then coalesce(product_price, 0) + when system_last_update >= human_last_update then coalesce(system_price, 0) + when b.pricing_group is null then 0 + else coalesce(product_price, 0) +end as purchase_price, (select group1 from price_group_dw pgd where id = 2) as markup_std1, (select group2 from price_group_dw pgd where id = 2) as markup_std2, (select group3 from price_group_dw pgd where id = 2) as markup_std3, @@ -1361,10 +1273,9 @@ (select group3 from price_group_dw pgd where id = 6) as markup_tier5_3, (select group4 from price_group_dw pgd where id = 6) as markup_tier5_4, (select group5 from price_group_dw pgd where id = 6) as markup_tier5_5 -from mv_purchase_pricelist mpp -join product p on p.product_id = mpp.product_id -left join brand b on b.id = p.brand_id -where b.id in(10, 122, 89) +from purchase_pricelist_dw ppd +join product p on ppd.product_id = p.product_id +left join brand b on b.id = p.brand_id 0 N @@ -1442,16 +1353,16 @@ where b.id in(10, 122, 89) N - BigNumber + String normal - purchase_price - -1 + pricing_group + 2147483647 -1 Table input - purchase_price - ######0.0###################;-######0.0################### + pricing_group + . - + , none N @@ -1465,16 +1376,16 @@ where b.id in(10, 122, 89) N - String + BigNumber normal - pricing_group - 2147483647 + purchase_price + -1 -1 Table input - pricing_group - + purchase_price + ######0.0###################;-######0.0################### . - , + none N @@ -1947,12 +1858,242 @@ where b.id in(10, 122, 89) Asia/Bangkok N - - - - - - + + BigNumber + normal + markup_tier4_1 + -1 + -1 + Table input + markup_tier4_1 + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + BigNumber + normal + markup_tier4_2 + -1 + -1 + Table input + markup_tier4_2 + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + BigNumber + normal + markup_tier4_3 + -1 + -1 + Table input + markup_tier4_3 + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + BigNumber + normal + markup_tier4_4 + -1 + -1 + Table input + markup_tier4_4 + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + BigNumber + normal + markup_tier4_5 + -1 + -1 + Table input + markup_tier4_5 + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + BigNumber + normal + markup_tier5_1 + -1 + -1 + Table input + markup_tier5_1 + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + BigNumber + normal + markup_tier5_2 + -1 + -1 + Table input + markup_tier5_2 + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + BigNumber + normal + markup_tier5_3 + -1 + -1 + Table input + markup_tier5_3 + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + BigNumber + normal + markup_tier5_4 + -1 + -1 + Table input + markup_tier5_4 + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + BigNumber + normal + markup_tier5_5 + -1 + -1 + Table input + markup_tier5_5 + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + + + + + @@ -1962,6 +2103,846 @@ where b.id in(10, 122, 89) Y + + filter group1 + FilterRows + + Y + + 1 + + none + + + select group1 + filter group2 + + + N + pricing_group + = + + + constant + String + group1 + -1 + -1 + N + + + + + + + + + + + + + + 704 + 64 + Y + + + + select group1 + SelectValues + + Y + + 1 + + none + + + + + product_id + + + + item_code + + + + name + + + + pricing_group + + + + purchase_price + + + + markup_std1 + markup_std + + + markup_group1 + markup_group + + + markup_tier2_1 + markup_tier2 + + + markup_tier3_1 + markup_tier3 + + + markup_tier4_1 + markup_tier4 + + + markup_tier5_1 + markup_tier5 + + + price_after_disc_1 + price_after_disc + + + price_before_disc1 + price_before_disc + + + price_tier2_1 + price_tier2 + + + price_tier3_1 + price_tier3 + + + price_tier4_1 + price_tier4 + + + price_tier5_1 + price_tier5 + + N + + + + + + + + + + + 864 + 64 + Y + + + + filter group2 + FilterRows + + Y + + 1 + + none + + + select group2 + filter group3 + + + N + pricing_group + = + + + constant + String + group2 + -1 + -1 + N + + + + + + + + + + + + + + 704 + 176 + Y + + + + select group2 + SelectValues + + Y + + 1 + + none + + + + + product_id + + + + item_code + + + + name + + + + pricing_group + + + + purchase_price + + + + markup_std2 + markup_std + + + markup_group2 + markup_group + + + markup_tier2_2 + markup_tier2 + + + markup_tier3_2 + markup_tier3 + + + markup_tier4_2 + markup_tier4 + + + markup_tier5_2 + markup_tier5 + + + price_after_disc_2 + price_after_disc + + + price_before_disc2 + price_before_disc + + + price_tier2_2 + price_tier2 + + + price_tier3_2 + price_tier3 + + + price_tier4_2 + price_tier4 + + + price_tier5_2 + price_tier5 + + N + + + + + + + + + + + 864 + 176 + Y + + + + filter group3 + FilterRows + + Y + + 1 + + none + + + select group3 + filter group4 + + + N + pricing_group + = + + + constant + String + group3 + -1 + -1 + N + + + + + + + + + + + + + + 704 + 320 + Y + + + + select group3 + SelectValues + + Y + + 1 + + none + + + + + product_id + + + + item_code + + + + name + + + + pricing_group + + + + purchase_price + + + + markup_std3 + markup_std + + + markup_group3 + markup_group + + + markup_tier2_3 + markup_tier2 + + + markup_tier3_3 + markup_tier3 + + + markup_tier4_3 + markup_tier4 + + + markup_tier5_3 + markup_tier5 + + + price_after_disc_3 + price_after_disc + + + price_before_disc3 + price_before_disc + + + price_tier2_3 + price_tier2 + + + price_tier3_3 + price_tier3 + + + price_tier4_3 + price_tier4 + + + price_tier5_3 + price_tier5 + + N + + + + + + + + + + + 864 + 320 + Y + + + + filter group4 + FilterRows + + Y + + 1 + + none + + + select group4 + filter group5 + + + N + pricing_group + = + + + constant + String + group4 + -1 + -1 + N + + + + + + + + + + + + + + 704 + 432 + Y + + + + select group4 + SelectValues + + Y + + 1 + + none + + + + + product_id + + + + item_code + + + + name + + + + pricing_group + + + + purchase_price + + + + markup_std4 + markup_std + + + markup_group4 + markup_group + + + markup_tier2_4 + markup_tier2 + + + markup_tier3_4 + markup_tier3 + + + markup_tier4_4 + markup_tier4 + + + markup_tier5_4 + markup_tier5 + + + price_after_disc_4 + price_after_disc + + + price_before_disc4 + price_before_disc + + + price_tier2_4 + price_tier2 + + + price_tier3_4 + price_tier3 + + + price_tier4_4 + price_tier4 + + + price_tier5_4 + price_tier5 + + N + + + + + + + + + + + 864 + 432 + Y + + + + filter group5 + FilterRows + + Y + + 1 + + none + + + select group5 + Write to log + + + N + pricing_group + = + + + constant + String + group5 + -1 + -1 + N + + + + + + + + + + + + + + 704 + 544 + Y + + + + select group5 + SelectValues + + Y + + 1 + + none + + + + + product_id + + + + item_code + + + + name + + + + pricing_group + + + + purchase_price + + + + markup_std5 + markup_std + + + markup_group5 + markup_group + + + markup_tier2_5 + markup_tier2 + + + markup_tier3_5 + markup_tier3 + + + markup_tier4_5 + markup_tier4 + + + markup_tier5_5 + markup_tier5 + + + price_after_disc_5 + price_after_disc + + + price_before_disc5 + price_before_disc + + + price_tier2_5 + price_tier2 + + + price_tier3_5 + price_tier3 + + + price_tier4_5 + price_tier4 + + + price_tier5_5 + price_tier5 + + N + + + + + + + + + + + 864 + 544 + Y + + + + Write to log + WriteToLog + + Y + + 1 + + none + + + log_level_basic + Y + N + 0 + + + + + + + + + + + + + 544 + 544 + Y + + + + Append streams + Append + + Y + + 1 + + none + + + select group1 + select group2 + + + + + + + + + + 1104 + 176 + Y + + + + Append streams 2 + Append + + Y + + 1 + + none + + + Append streams + select group3 + + + + + + + + + + 1104 + 320 + Y + + + + Append streams 3 + Append + + Y + + 1 + + none + + + Append streams 2 + select group4 + + + + + + + + + + 1104 + 432 + Y + + + + Append streams 4 + Append + + Y + + 1 + + none + + + Append streams 3 + select group5 + + + + + + + + + + 1104 + 544 + Y + + diff --git a/import_all_data_bi_indoteknik.kjb b/import_all_data_bi_indoteknik.kjb index eef86bc..0e4bbd0 100644 --- a/import_all_data_bi_indoteknik.kjb +++ b/import_all_data_bi_indoteknik.kjb @@ -1034,6 +1034,123 @@ 560 + + count product + + TRANS + + filename + + /home/stephan/transformation-job-bi/update_count_trx_po_product.ktr + + N + N + N + N + N + N + + + N + N + Basic + N + + N + Y + N + N + N + Pentaho local + N + + Y + + N + Y + 0 + 320 + 560 + + + + count vendor + + TRANS + + filename + + /home/stephan/transformation-job-bi/update_count_trx_po_vendor.ktr + + N + N + N + N + N + N + + + N + N + Basic + N + + N + Y + N + N + N + Pentaho local + N + + Y + + N + Y + 0 + 160 + 560 + + + + calculate pricelist + + TRANS + + filename + + /home/stephan/transformation-job-bi/calculate_price_after_disc.ktr + + N + N + N + N + N + N + + + N + N + Basic + N + + N + Y + N + N + N + Pentaho local + N + + Y + + N + Y + 0 + 160 + 688 + + @@ -1207,6 +1324,33 @@ Y N + + import price group + count product + 0 + 0 + Y + Y + N + + + count product + count vendor + 0 + 0 + Y + Y + N + + + count vendor + calculate pricelist + 0 + 0 + Y + Y + N + -- cgit v1.2.3