From 6a0c8a8ad63c60903065f903fddc300439d12bc8 Mon Sep 17 00:00:00 2001 From: stephanchrst Date: Tue, 26 Aug 2025 16:04:12 +0700 Subject: changes database calculate margin inv v3 --- calculate_margin_invoice_v3.ktr | 2126 +++++++++++++++++++-------------------- 1 file changed, 1029 insertions(+), 1097 deletions(-) diff --git a/calculate_margin_invoice_v3.ktr b/calculate_margin_invoice_v3.ktr index fa17fbb..a11ee0d 100644 --- a/calculate_margin_invoice_v3.ktr +++ b/calculate_margin_invoice_v3.ktr @@ -430,7 +430,7 @@ 2025/08/13 09:47:28.228 - 2025/08/13 09:47:28.228 - + H4sIAAAAAAAAAAMAAAAAAAAAAAA= N @@ -578,6 +578,601 @@ Y + + Add constants + Constant + + Y + + 1 + + none + + + + + one_hundred + BigNumber + + + + + 100 + -1 + -1 + N + + + + + + + + + + + + 960 + 400 + Y + + + + Calculator + Calculator + + Y + + 1 + + none + + + Y + + margin + SUBTRACT + penjualan + hpp + + None + -1 + -1 + N + + + + + + + div_margin + DIVIDE + margin + penjualan + + None + -1 + -1 + N + + + + + + + percent_margin + MULTIPLY + div_margin + one_hundred + + None + -1 + -1 + N + + + + + + + + + + + + + + + 1104 + 400 + Y + + + + Filter rows + FilterRows + + Y + + 1 + + none + + + select all + Write to log + + + N + + + N + penjualan + > + + + constant + BigNumber + 0.0 + -1 + -1 + N + ######0.0###################;-######0.0################### + + + + N + AND + hpp + > + + + constant + BigNumber + 0.0 + -1 + -1 + N + ######0.0###################;-######0.0################### + + + + + + + + + + + + + + + 416 + 288 + Y + + + + Insert / update + InsertUpdate + + Y + + 1 + + none + + + dw bi indoteknik (localhost) + 100 + N + + public + margin_invoice_v3
+ + id + id + = + + + + id + id + N + + + move_id + move_id + Y + + + date + date + Y + + + invoice_origin + invoice_origin + Y + + + invoice + invoice + Y + + + customer + customer + Y + + + lob + lob + Y + + + brand + brand + Y + + + product_id + product_id + Y + + + item_code + item_code + Y + + + product + product + Y + + + salesperson + salesperson + Y + + + quantity + quantity + Y + + + price_unit + price_unit + Y + + + price_subtotal + price_subtotal + Y + + + price_total + price_total + Y + + + penjualan + penjualan + Y + + + hpp + hpp + Y + + + sale_order + sale_order + Y + + + sale_product_id + sale_product_id + Y + + + vendor_id + vendor_id + Y + + + vendor + vendor + Y + + + margin + margin + Y + + + div_margin + div_margin + Y + + + percent_margin + percent_margin + Y + + + + + + + + + + + + 1104 + 80 + Y + + + + Merge join + MergeJoin + + Y + + 1 + + none + + + LEFT OUTER + penjualan + hpp + + move_id + product_id + + + move_id + product_id + + + + + + + + + + + 240 + 288 + Y + + + + Merge join 2 + MergeJoin + + Y + + 1 + + none + + + LEFT OUTER + Sort rows + Sort rows 2 + + id + + + id + + + + + + + + + + + 832 + 400 + Y + + + + Select values + SelectValues + + Y + + 1 + + none + + + + + id + + + move_id + + + date + + + invoice_origin + + + invoice + + + customer + + + lob + + + brand + + + product_id + + + item_code + + + product + + + salesperson + + + quantity + + + price_unit + + + price_subtotal + + + price_total + + + penjualan + + + hpp + + + sale_order + + + sale_product_id + + + vendor_id + + + vendor + + + margin + + + div_margin + + + percent_margin + + N + + + + + + + + + + + 1104 + 272 + Y + + + + Sort rows + SortRows + + Y + + 1 + + none + + + %%java.io.tmpdir%% + out + 1000000 + + N + + N + + + id + Y + N + N + 0 + N + + + + + + + + + + + + 688 + 400 + Y + + + + Sort rows 2 + SortRows + + Y + + 1 + + none + + + %%java.io.tmpdir%% + out + 1000000 + + N + + N + + + id + Y + N + N + 0 + N + + + + + + + + + + + + 832 + 272 + Y + + Table input TableInput @@ -607,185 +1202,25 @@ left join brand b on b.id = p.brand_id where 1=1 and aml.account_id = 462 and am.state = 'posted' +--and aml.date >= '2025-08-20' and aml.date >= '2024-06-01' --and aml.write_date >= (now() - '3 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_US - Asia/Bangkok - N - - - Integer - normal - move_id - 9 - 0 - Table input - move_id - ####0;-####0 - . - , - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - Date - normal - date - -1 - -1 - Table input - date - - . - , - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - String - normal - invoice_origin - 2147483647 - -1 - Table input - invoice_origin - - . - , - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - String - normal - invoice - 2147483647 - -1 - Table input - invoice - - . - , - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - String - normal - customer - 2147483647 - -1 - Table input - customer - - . - , - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - String - normal - lob - 2147483647 - -1 - Table input - lob - - . - , - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - + + N + N + N + N + - String + Integer normal - brand - 2147483647 - -1 + id + 9 + 0 Table input - brand - + id + ####0;-####0 . , @@ -803,11 +1238,11 @@ and aml.date >= '2024-06-01' Integer normal - product_id + move_id 9 0 Table input - product_id + move_id ####0;-####0 . , @@ -824,13 +1259,13 @@ and aml.date >= '2024-06-01' N - String + Date normal - item_code - 2147483647 + date + -1 -1 Table input - item_code + date . , @@ -849,11 +1284,11 @@ and aml.date >= '2024-06-01' String normal - product + invoice_origin 2147483647 -1 Table input - product + invoice_origin . , @@ -872,11 +1307,11 @@ and aml.date >= '2024-06-01' String normal - salesperson + invoice 2147483647 -1 Table input - salesperson + invoice . , @@ -893,62 +1328,16 @@ and aml.date >= '2024-06-01' N - BigNumber - normal - quantity - -1 - -1 - Table input - quantity - ######0.0###################;-######0.0################### - . - - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - BigNumber - normal - price_unit - -1 - -1 - Table input - price_unit - ######0.0###################;-######0.0################### - . - - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - BigNumber + String normal - price_subtotal - -1 + customer + 2147483647 -1 Table input - price_subtotal - ######0.0###################;-######0.0################### + customer + . - + , none N @@ -962,16 +1351,16 @@ and aml.date >= '2024-06-01' N - BigNumber + String normal - price_total - -1 + lob + 2147483647 -1 Table input - price_total - ######0.0###################;-######0.0################### + lob + . - + , none N @@ -985,152 +1374,14 @@ and aml.date >= '2024-06-01' N - BigNumber + String normal - penjualan - -1 + brand + 2147483647 -1 Table input - penjualan - ######0.0###################;-######0.0################### - . - - - none - N - Y - 0 - N - N - N - en_US - Asia/Bangkok - N - - - - - - - - - - - - 144 - 80 - Y - - - - penjualan - SortRows - - Y - - 1 - - none - - - %%java.io.tmpdir%% - out - 1000000 - - N - - N - - - id - Y - N - N - 0 - N - - - - - - - - - - - - 144 - 176 - Y - - - - Table input 2 - TableInput - - Y - - 1 - - none - - - dw bi indoteknik (localhost) - select aml.id, am.id as move_id, aml.product_id, aml.balance as hpp -from account_move_line aml -join account_move am on am.id = aml.move_id ---join product p on p.product_id = aml.product_id ---join res_partner rp on rp.id = am.partner_id ---join res_users ru on ru.id = am.invoice_user_id ---join res_partner rp2 on rp2.id = ru.partner_id ---join account_account aa on aa.id = aml.account_id ---left join res_partner_industry rpi on rpi.id = rp.industry_id ---left join group_partner gp on gp.id = rp.group_partner_id ---left join brand b on b.id = p.brand_id -where 1=1 -and aml.account_id = 466 -and am.state = 'posted' -and aml.date >= '2024-06-01' ---and aml.write_date >= (now() - '3 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_US - Asia/Bangkok - N - - - Integer - normal - move_id - 9 - 0 - Table input 2 - move_id - ####0;-####0 + brand + . , @@ -1151,7 +1402,7 @@ and aml.date >= '2024-06-01' product_id 9 0 - Table input 2 + Table input product_id ####0;-####0 . @@ -1169,316 +1420,190 @@ and aml.date >= '2024-06-01' N - BigNumber + String normal - hpp - -1 + item_code + 2147483647 -1 - Table input 2 - hpp - ######0.0###################;-######0.0################### + Table input + item_code + . - + , none N Y 0 - N - N - N - en_US - Asia/Bangkok - N - - - - - - - - - - - - 320 - 80 - Y - - - - hpp - SortRows - - Y - - 1 - - none - - - %%java.io.tmpdir%% - out - 1000000 - - N - - N - - - id - Y - N - N - 0 - N - - - - - - - - - - - - 320 - 176 - Y - - - - Merge join - MergeJoin - - Y - - 1 - - none - - - LEFT OUTER - penjualan - hpp - - move_id - product_id - - - move_id - product_id - - - - - - - - - - - 240 - 288 - Y - - - - Filter rows - FilterRows - - Y - - 1 - - none - - - select all - Write to log - - - N - - - N - penjualan - > - - - constant - BigNumber - 0.0 - -1 - -1 - N - ######0.0###################;-######0.0################### - - - - N - AND - hpp - > - - - constant - BigNumber - 0.0 - -1 - -1 - N - ######0.0###################;-######0.0################### - - - - - - - - - - - - - - - 416 - 288 - Y - - - - Write to log - WriteToLog - - Y - - 1 - - none - - - log_level_basic - Y - N - 0 - - - - - - - - - - - - - 416 - 400 - Y - - - - select all - SelectValues - - N - - 1 - - none - - - - - id - - - - move_id - - - - date - - - - invoice_origin - - - - invoice - - - - customer - - - - lob - - - - brand - - - - product_id - - - - item_code - - - + N + N + N + en_US + Asia/Bangkok + N + + + String + normal product - - - + 2147483647 + -1 + Table input + product + + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + String + normal salesperson - - - + 2147483647 + -1 + Table input + salesperson + + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + BigNumber + normal quantity - - - + -1 + -1 + Table input + quantity + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + BigNumber + normal price_unit - - - + -1 + -1 + Table input + price_unit + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + BigNumber + normal price_subtotal - - - + -1 + -1 + Table input + price_subtotal + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + BigNumber + normal price_total - - - + -1 + -1 + Table input + price_total + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + BigNumber + normal penjualan - - - - id_1 - - - - move_id_1 - - - - product_id_1 - - - - hpp - - - N - + -1 + -1 + Table input + penjualan + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + @@ -1488,14 +1613,14 @@ and aml.date >= '2024-06-01' - 560 - 288 + 144 + 80 Y - param only - SelectValues + Table input 2 + TableInput Y @@ -1504,21 +1629,124 @@ and aml.date >= '2024-06-01' none - - + dw bi indoteknik (localhost) + select aml.id, am.id as move_id, aml.product_id, aml.balance as hpp +from account_move_line aml +join account_move am on am.id = aml.move_id +--join product p on p.product_id = aml.product_id +--join res_partner rp on rp.id = am.partner_id +--join res_users ru on ru.id = am.invoice_user_id +--join res_partner rp2 on rp2.id = ru.partner_id +--join account_account aa on aa.id = aml.account_id +--left join res_partner_industry rpi on rpi.id = rp.industry_id +--left join group_partner gp on gp.id = rp.group_partner_id +--left join brand b on b.id = p.brand_id +where 1=1 +and aml.account_id = 466 +and am.state = 'posted' +--and aml.date >= '2025-08-20' +and aml.date >= '2024-06-01' +--and aml.write_date >= (now() - '3 days'::interval) + 0 + + N + N + N + N + + + Integer + normal id - - - - invoice_origin - - - + 9 + 0 + Table input 2 + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Integer + normal + move_id + 9 + 0 + Table input 2 + move_id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + Integer + normal product_id - - - N - + 9 + 0 + Table input 2 + product_id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + + BigNumber + normal + hpp + -1 + -1 + Table input 2 + hpp + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_US + Asia/Bangkok + N + + @@ -1528,8 +1756,8 @@ and aml.date >= '2024-06-01' - 688 - 192 + 320 + 80 Y @@ -1684,7 +1912,39 @@ and tso.name = ? and tsol.vendor_id = ? 832 - 192 + 80 + Y + + + + Write to log + WriteToLog + + Y + + 1 + + none + + + log_level_basic + Y + N + 0 + + + + + + + + + + + + + 416 + 400 Y @@ -1702,87 +1962,66 @@ and tso.name = ? and tsol.vendor_id = ? id - move_id - date - invoice_origin - invoice - customer - lob - brand - product_id - item_code - product - salesperson - quantity - price_unit - price_subtotal - price_total - penjualan - id_1 - move_id_1 - product_id_1 - hpp - N @@ -1801,49 +2040,7 @@ and tso.name = ? and tsol.vendor_id = ? - Sort rows - SortRows - - Y - - 1 - - none - - - %%java.io.tmpdir%% - out - 1000000 - - N - - N - - - id - Y - N - N - 0 - N - - - - - - - - - - - - 688 - 400 - Y - - - - Sort rows 2 + hpp SortRows Y @@ -1867,114 +2064,9 @@ and tso.name = ? and tsol.vendor_id = ? N N 0 - N - - - - - - - - - - - - 832 - 272 - Y - - - - Merge join 2 - MergeJoin - - Y - - 1 - - none - - - LEFT OUTER - Sort rows - Sort rows 2 - - id - - - id - - - - - - - - - - - 832 - 400 - Y - - - - Calculator - Calculator - - Y - - 1 - - none - - - Y - - margin - SUBTRACT - penjualan - hpp - - None - -1 - -1 - N - - - - - - - div_margin - DIVIDE - margin - penjualan - - None - -1 - -1 - N - - - - - - - percent_margin - MULTIPLY - div_margin - one_hundred - - None - -1 - -1 - N - - - - - + N + + @@ -1984,14 +2076,14 @@ and tso.name = ? and tsol.vendor_id = ? - 992 - 160 + 320 + 176 Y - Add constants - Constant + param only + SelectValues Y @@ -2002,17 +2094,15 @@ and tso.name = ? and tsol.vendor_id = ? - one_hundred - BigNumber - - - - - 100 - -1 - -1 - N + id + + + invoice_origin + + + product_id + N @@ -2023,14 +2113,14 @@ and tso.name = ? and tsol.vendor_id = ? - 992 - 32 + 688 + 192 Y - Select values - SelectValues + penjualan + SortRows Y @@ -2039,106 +2129,111 @@ and tso.name = ? and tsol.vendor_id = ? none + %%java.io.tmpdir%% + out + 1000000 + + N + + N + + + id + Y + N + N + 0 + N + + + + + + + + + + + + 144 + 176 + Y + + + + select all + SelectValues + + N + + 1 + + none + + id - move_id - date - invoice_origin - invoice - customer - lob - brand - product_id - item_code - product - salesperson - quantity - price_unit - price_subtotal - price_total - penjualan - - - - hpp - - - - sale_order - - - - sale_product_id - - vendor_id - - - - vendor - + id_1 - margin - + move_id_1 - div_margin - + product_id_1 - percent_margin - + hpp N @@ -2151,171 +2246,8 @@ and tso.name = ? and tsol.vendor_id = ? - 992 - 256 - Y - - - - Insert / update - InsertUpdate - - Y - - 1 - - none - - - dw bi indoteknik (localhost) - 100 - N - - public - margin_invoice_v3
- - - id - - - - - id - id - N - - - move_id - move_id - Y - - - date - date - Y - - - invoice_origin - invoice_origin - Y - - - invoice - invoice - Y - - - customer - customer - Y - - - lob - lob - Y - - - brand - brand - Y - - - product_id - product_id - Y - - - item_code - item_code - Y - - - product - product - Y - - - salesperson - salesperson - Y - - - quantity - quantity - Y - - - price_unit - price_unit - Y - - - price_subtotal - price_subtotal - Y - - - price_total - price_total - Y - - - penjualan - penjualan - Y - - - hpp - hpp - Y - - - sale_order - sale_order - Y - - - sale_product_id - sale_product_id - Y - - - vendor_id - vendor_id - Y - - - vendor - vendor - Y - - - margin - margin - Y - - - div_margin - div_margin - Y - - - percent_margin - percent_margin - Y - - - - - - - - - - - - 992 - 352 + 560 + 288 Y -- cgit v1.2.3