From f7b0bcf87e430862c2229ed7a821c095eed18d8b Mon Sep 17 00:00:00 2001 From: stephanchrst Date: Wed, 15 May 2024 15:04:59 +0700 Subject: add financial report --- calculate_financial_report.ktr | 3140 ++++++++++++++++++++++++++++++++++++++++ 1 file changed, 3140 insertions(+) create mode 100644 calculate_financial_report.ktr (limited to 'calculate_financial_report.ktr') diff --git a/calculate_financial_report.ktr b/calculate_financial_report.ktr new file mode 100644 index 0000000..eae8260 --- /dev/null +++ b/calculate_financial_report.ktr @@ -0,0 +1,3140 @@ + + + + calculate_financial_report + + + + 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/05/14 09:35:21.627 + - + 2024/05/14 09:35:21.627 + H4sIAAAAAAAAAAMAAAAAAAAAAAA= + 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 full + Merge join + Y + + + last year running date + Merge join + Y + + + Merge join + Merge join 2 + Y + + + last year only + Merge join 2 + Y + + + Merge join 2 + Merge join 3 + Y + + + month1 + Merge join 3 + Y + + + Merge join 3 + Merge join 4 + Y + + + month2 + Merge join 4 + Y + + + Merge join 4 + Merge join 5 + Y + + + month3 + Merge join 5 + Y + + + Merge join 5 + Merge join 6 + Y + + + month4 + Merge join 6 + Y + + + Merge join 6 + Merge join 7 + Y + + + month5 + Merge join 7 + Y + + + Merge join 7 + Merge join 8 + Y + + + month6 + Merge join 8 + Y + + + month7 + Merge join 9 + Y + + + Merge join 8 + Merge join 9 + Y + + + Merge join 9 + Merge join 10 + Y + + + month8 + Merge join 10 + Y + + + month9 + Merge join 11 + Y + + + Merge join 10 + Merge join 11 + Y + + + Merge join 11 + Merge join 12 + Y + + + month10 + Merge join 12 + Y + + + month11 + Merge join 13 + Y + + + Merge join 12 + Merge join 13 + Y + + + Merge join 13 + Merge join 14 + Y + + + month12 + Merge join 14 + Y + + + Merge join 14 + combine select + Y + + + combine select + Insert / update + Y + + + + Insert / update + InsertUpdate + + Y + + 1 + + none + + + dw bi indoteknik + 100 + N + + public +
profit_and_loss
+ + id + id + = + + + + id + id + N + + + name + name + Y + + + ly_full + ly_full + Y + + + ly_running + ly_running + Y + + + ly_current + ly_current + Y + + + fy1 + fy1 + Y + + + fy2 + fy2 + Y + + + fy3 + fy3 + Y + + + fy4 + fy4 + Y + + + fy5 + fy5 + Y + + + fy6 + fy6 + Y + + + fy7 + fy7 + Y + + + fy8 + fy8 + Y + + + fy9 + fy9 + Y + + + fy10 + fy10 + Y + + + fy11 + fy11 + Y + + + fy12 + fy12 + Y + + + + + + + + + + + + 320 + 256 + Y + + + + Merge join + MergeJoin + + Y + + 1 + + none + + + INNER + last year full + last year running date + + id + + + id + + + + + + + + + + + 112 + 160 + Y + + + + Merge join 10 + MergeJoin + + Y + + 1 + + none + + + INNER + Merge join 9 + month8 + + id + + + id + + + + + + + + + + + 1072 + 160 + Y + + + + Merge join 11 + MergeJoin + + Y + + 1 + + none + + + INNER + Merge join 10 + month9 + + id + + + id + + + + + + + + + + + 1072 + 256 + Y + + + + Merge join 12 + MergeJoin + + Y + + 1 + + none + + + INNER + Merge join 11 + month10 + + id + + + id + + + + + + + + + + + 960 + 256 + Y + + + + Merge join 13 + MergeJoin + + Y + + 1 + + none + + + INNER + Merge join 12 + month11 + + id + + + id + + + + + + + + + + + 832 + 256 + Y + + + + Merge join 14 + MergeJoin + + Y + + 1 + + none + + + INNER + Merge join 13 + month12 + + id + + + id + + + + + + + + + + + 720 + 256 + Y + + + + Merge join 2 + MergeJoin + + Y + + 1 + + none + + + INNER + Merge join + last year only + + id + + + id + + + + + + + + + + + 240 + 160 + Y + + + + Merge join 3 + MergeJoin + + Y + + 1 + + none + + + INNER + Merge join 2 + month1 + + id + + + id + + + + + + + + + + + 384 + 160 + Y + + + + Merge join 4 + MergeJoin + + Y + + 1 + + none + + + INNER + Merge join 3 + month2 + + id + + + id + + + + + + + + + + + 480 + 160 + Y + + + + Merge join 5 + MergeJoin + + Y + + 1 + + none + + + INNER + Merge join 4 + month3 + + id + + + id + + + + + + + + + + + 576 + 160 + Y + + + + Merge join 6 + MergeJoin + + Y + + 1 + + none + + + INNER + Merge join 5 + month4 + + id + + + id + + + + + + + + + + + 672 + 160 + Y + + + + Merge join 7 + MergeJoin + + Y + + 1 + + none + + + INNER + Merge join 6 + month5 + + id + + + id + + + + + + + + + + + 768 + 160 + Y + + + + Merge join 8 + MergeJoin + + Y + + 1 + + none + + + INNER + Merge join 7 + month6 + + id + + + id + + + + + + + + + + + 864 + 160 + Y + + + + Merge join 9 + MergeJoin + + Y + + 1 + + none + + + INNER + Merge join 8 + month7 + + id + + + id + + + + + + + + + + + 976 + 160 + Y + + + + combine select + SelectValues + + Y + + 1 + + none + + + + + id + + + + name + + + + ly_full + + + + ly_running + + + + ly_current + + + + fy1 + + + + fy2 + + + + fy3 + + + + fy4 + + + + fy5 + + + + fy6 + + + + fy7 + + + + fy8 + + + + fy9 + + + + fy10 + + + + fy11 + + + + fy12 + + + N + + + + + + + + + + + 464 + 256 + Y + + + + last year full + TableInput + + Y + + 1 + + none + + + dw bi indoteknik + select afrr.id, afrr.name, +( + select 0-coalesce(sum(balance),0) as ly_full from account_move_line aml + join account_move am on am.id = aml.move_id + where aml.account_id in( + select account_id from account_account_financial_report aafr + where aafr.report_line_id in( + select id from account_financial_report afr where id = afrr.id + ) + ) + and am.state = 'posted' + and am."date" between get_first_day_last_year()::date and get_last_year_date()::date +) +from account_financial_report afrr +where afrr.parent_id = 321 +order by id + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + last year full + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + name + 2147483647 + -1 + last year full + name + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + ly_full + -1 + -1 + last year full + ly_full + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 112 + 64 + Y + + + + last year only + TableInput + + Y + + 1 + + none + + + dw bi indoteknik + select afrr.id, afrr.name, +( + select 0-coalesce(sum(balance),0) as ly_current from account_move_line aml + join account_move am on am.id = aml.move_id + where aml.account_id in( + select account_id from account_account_financial_report aafr + where aafr.report_line_id in( + select id from account_financial_report afr where id = afrr.id + ) + ) + and am.state = 'posted' + and to_char(am."date", 'YYYY-MM') = get_last_year_month() +) +from account_financial_report afrr +where afrr.parent_id = 321 +order by id + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + last year only + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + name + 2147483647 + -1 + last year only + name + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + ly_current + -1 + -1 + last year only + ly_current + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 384 + 64 + Y + + + + last year running date + TableInput + + Y + + 1 + + none + + + dw bi indoteknik + -- query for ratio profit and loss +select afrr.id, afrr.name, +( + select 0-coalesce(sum(balance),0) as ly_running from account_move_line aml + join account_move am on am.id = aml.move_id + where aml.account_id in( + select account_id from account_account_financial_report aafr + where aafr.report_line_id in( + select id from account_financial_report afr where id = afrr.id + ) + ) + and am.state = 'posted' + and am."date" between get_first_day_last_year()::date and get_last_current_date()::date +) +from account_financial_report afrr +where afrr.parent_id = 321 +order by id + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + last year running date + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + name + 2147483647 + -1 + last year running date + name + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + ly_running + -1 + -1 + last year running date + ly_running + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 240 + 64 + Y + + + + month1 + TableInput + + Y + + 1 + + none + + + dw bi indoteknik + select afrr.id, afrr.name, +( + select 0-coalesce(sum(balance),0) as fy1 from account_move_line aml + join account_move am on am.id = aml.move_id + where aml.account_id in( + select account_id from account_account_financial_report aafr + where aafr.report_line_id in( + select id from account_financial_report afr where id = afrr.id + ) + ) + and am.state = 'posted' + and to_char(am."date",'YYYY') = get_now_year() + and to_char(am."date",'MM') = '01' +) +from account_financial_report afrr +where afrr.parent_id = 321 +order by id + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + month1 + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + name + 2147483647 + -1 + month1 + name + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + fy1 + -1 + -1 + month1 + fy1 + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 480 + 64 + Y + + + + month10 + TableInput + + Y + + 1 + + none + + + dw bi indoteknik + select afrr.id, afrr.name, +( + select 0-coalesce(sum(balance),0) as fy10 from account_move_line aml + join account_move am on am.id = aml.move_id + where aml.account_id in( + select account_id from account_account_financial_report aafr + where aafr.report_line_id in( + select id from account_financial_report afr where id = afrr.id + ) + ) + and am.state = 'posted' + and to_char(am."date",'YYYY') = get_now_year() + and to_char(am."date",'MM') = '10' +) +from account_financial_report afrr +where afrr.parent_id = 321 +order by id + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + month10 + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + name + 2147483647 + -1 + month10 + name + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + fy10 + -1 + -1 + month10 + fy10 + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 960 + 336 + Y + + + + month11 + TableInput + + Y + + 1 + + none + + + dw bi indoteknik + select afrr.id, afrr.name, +( + select 0-coalesce(sum(balance),0) as fy11 from account_move_line aml + join account_move am on am.id = aml.move_id + where aml.account_id in( + select account_id from account_account_financial_report aafr + where aafr.report_line_id in( + select id from account_financial_report afr where id = afrr.id + ) + ) + and am.state = 'posted' + and to_char(am."date",'YYYY') = get_now_year() + and to_char(am."date",'MM') = '11' +) +from account_financial_report afrr +where afrr.parent_id = 321 +order by id + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + month11 + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + name + 2147483647 + -1 + month11 + name + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + fy11 + -1 + -1 + month11 + fy11 + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 832 + 336 + Y + + + + month12 + TableInput + + Y + + 1 + + none + + + dw bi indoteknik + select afrr.id, afrr.name, +( + select 0-coalesce(sum(balance),0) as fy12 from account_move_line aml + join account_move am on am.id = aml.move_id + where aml.account_id in( + select account_id from account_account_financial_report aafr + where aafr.report_line_id in( + select id from account_financial_report afr where id = afrr.id + ) + ) + and am.state = 'posted' + and to_char(am."date",'YYYY') = get_now_year() + and to_char(am."date",'MM') = '12' +) +from account_financial_report afrr +where afrr.parent_id = 321 +order by id + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + month12 + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + name + 2147483647 + -1 + month12 + name + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + fy12 + -1 + -1 + month12 + fy12 + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 720 + 336 + Y + + + + month2 + TableInput + + Y + + 1 + + none + + + dw bi indoteknik + select afrr.id, afrr.name, +( + select 0-coalesce(sum(balance),0) as fy2 from account_move_line aml + join account_move am on am.id = aml.move_id + where aml.account_id in( + select account_id from account_account_financial_report aafr + where aafr.report_line_id in( + select id from account_financial_report afr where id = afrr.id + ) + ) + and am.state = 'posted' + and to_char(am."date",'YYYY') = get_now_year() + and to_char(am."date",'MM') = '02' +) +from account_financial_report afrr +where afrr.parent_id = 321 +order by id + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + month2 + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + name + 2147483647 + -1 + month2 + name + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + fy2 + -1 + -1 + month2 + fy2 + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 576 + 64 + Y + + + + month3 + TableInput + + Y + + 1 + + none + + + dw bi indoteknik + select afrr.id, afrr.name, +( + select 0-coalesce(sum(balance),0) as fy3 from account_move_line aml + join account_move am on am.id = aml.move_id + where aml.account_id in( + select account_id from account_account_financial_report aafr + where aafr.report_line_id in( + select id from account_financial_report afr where id = afrr.id + ) + ) + and am.state = 'posted' + and to_char(am."date",'YYYY') = get_now_year() + and to_char(am."date",'MM') = '03' +) +from account_financial_report afrr +where afrr.parent_id = 321 +order by id + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + month3 + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + name + 2147483647 + -1 + month3 + name + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + fy3 + -1 + -1 + month3 + fy3 + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 672 + 64 + Y + + + + month4 + TableInput + + Y + + 1 + + none + + + dw bi indoteknik + select afrr.id, afrr.name, +( + select 0-coalesce(sum(balance),0) as fy4 from account_move_line aml + join account_move am on am.id = aml.move_id + where aml.account_id in( + select account_id from account_account_financial_report aafr + where aafr.report_line_id in( + select id from account_financial_report afr where id = afrr.id + ) + ) + and am.state = 'posted' + and to_char(am."date",'YYYY') = get_now_year() + and to_char(am."date",'MM') = '04' +) +from account_financial_report afrr +where afrr.parent_id = 321 +order by id + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + month4 + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + name + 2147483647 + -1 + month4 + name + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + fy4 + -1 + -1 + month4 + fy4 + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 768 + 64 + Y + + + + month5 + TableInput + + Y + + 1 + + none + + + dw bi indoteknik + select afrr.id, afrr.name, +( + select 0-coalesce(sum(balance),0) as fy5 from account_move_line aml + join account_move am on am.id = aml.move_id + where aml.account_id in( + select account_id from account_account_financial_report aafr + where aafr.report_line_id in( + select id from account_financial_report afr where id = afrr.id + ) + ) + and am.state = 'posted' + and to_char(am."date",'YYYY') = get_now_year() + and to_char(am."date",'MM') = '05' +) +from account_financial_report afrr +where afrr.parent_id = 321 +order by id + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + month5 + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + name + 2147483647 + -1 + month5 + name + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + fy5 + -1 + -1 + month5 + fy5 + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 864 + 64 + Y + + + + month6 + TableInput + + Y + + 1 + + none + + + dw bi indoteknik + select afrr.id, afrr.name, +( + select 0-coalesce(sum(balance),0) as fy6 from account_move_line aml + join account_move am on am.id = aml.move_id + where aml.account_id in( + select account_id from account_account_financial_report aafr + where aafr.report_line_id in( + select id from account_financial_report afr where id = afrr.id + ) + ) + and am.state = 'posted' + and to_char(am."date",'YYYY') = get_now_year() + and to_char(am."date",'MM') = '06' +) +from account_financial_report afrr +where afrr.parent_id = 321 +order by id + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + month6 + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + name + 2147483647 + -1 + month6 + name + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + fy6 + -1 + -1 + month6 + fy6 + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 976 + 64 + Y + + + + month7 + TableInput + + Y + + 1 + + none + + + dw bi indoteknik + select afrr.id, afrr.name, +( + select 0-coalesce(sum(balance),0) as fy7 from account_move_line aml + join account_move am on am.id = aml.move_id + where aml.account_id in( + select account_id from account_account_financial_report aafr + where aafr.report_line_id in( + select id from account_financial_report afr where id = afrr.id + ) + ) + and am.state = 'posted' + and to_char(am."date",'YYYY') = get_now_year() + and to_char(am."date",'MM') = '07' +) +from account_financial_report afrr +where afrr.parent_id = 321 +order by id + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + month7 + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + name + 2147483647 + -1 + month7 + name + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + fy7 + -1 + -1 + month7 + fy7 + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 1072 + 64 + Y + + + + month8 + TableInput + + Y + + 1 + + none + + + dw bi indoteknik + select afrr.id, afrr.name, +( + select 0-coalesce(sum(balance),0) as fy8 from account_move_line aml + join account_move am on am.id = aml.move_id + where aml.account_id in( + select account_id from account_account_financial_report aafr + where aafr.report_line_id in( + select id from account_financial_report afr where id = afrr.id + ) + ) + and am.state = 'posted' + and to_char(am."date",'YYYY') = get_now_year() + and to_char(am."date",'MM') = '08' +) +from account_financial_report afrr +where afrr.parent_id = 321 +order by id + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + month8 + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + name + 2147483647 + -1 + month8 + name + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + fy8 + -1 + -1 + month8 + fy8 + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 1168 + 64 + Y + + + + month9 + TableInput + + Y + + 1 + + none + + + dw bi indoteknik + select afrr.id, afrr.name, +( + select 0-coalesce(sum(balance),0) as fy9 from account_move_line aml + join account_move am on am.id = aml.move_id + where aml.account_id in( + select account_id from account_account_financial_report aafr + where aafr.report_line_id in( + select id from account_financial_report afr where id = afrr.id + ) + ) + and am.state = 'posted' + and to_char(am."date",'YYYY') = get_now_year() + and to_char(am."date",'MM') = '09' +) +from account_financial_report afrr +where afrr.parent_id = 321 +order by id + 0 + + N + N + N + N + + + Integer + normal + id + 9 + 0 + month9 + id + ####0;-####0 + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + String + normal + name + 2147483647 + -1 + month9 + name + + . + , + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + BigNumber + normal + fy9 + -1 + -1 + month9 + fy9 + ######0.0###################;-######0.0################### + . + + + none + N + Y + 0 + N + N + N + en_ID + Asia/Bangkok + N + + + + + + + + + + + + 1072 + 336 + Y + + + + + + + N + + -- cgit v1.2.3