From 055696d38bca31f494a798637e5bd4739dae2e0e Mon Sep 17 00:00:00 2001
From: stephanchrst <stephanchrst@gmail.com>
Date: Wed, 15 May 2024 16:54:29 +0700
Subject: calculate profit and loss

---
 calculate_financial_report.ktr | 222 ++++++++++++++++++++++++++++++++++++-----
 1 file changed, 197 insertions(+), 25 deletions(-)

(limited to 'calculate_financial_report.ktr')

diff --git a/calculate_financial_report.ktr b/calculate_financial_report.ktr
index eae8260..f3094f7 100644
--- a/calculate_financial_report.ktr
+++ b/calculate_financial_report.ktr
@@ -436,14 +436,14 @@
   <notepads>
   </notepads>
   <connection>
-    <name>dw bi indoteknik</name>
+    <name>dw bi indoteknik (localhost)</name>
     <server>localhost</server>
     <type>POSTGRESQL</type>
     <access>Native</access>
     <database>dw_bi_indoteknik</database>
-    <port>5703</port>
-    <username>metabase</username>
-    <password>Encrypted 2be98dfba28d7bf86853c9f58cfc4f889</password>
+    <port>5432</port>
+    <username>odoo</username>
+    <password>Encrypted 2be98afc82bdfd8dd9a2ca45acc83f6c3</password>
     <servername/>
     <data_tablespace/>
     <index_tablespace/>
@@ -462,7 +462,7 @@
       </attribute>
       <attribute>
         <code>PORT_NUMBER</code>
-        <attribute>5703</attribute>
+        <attribute>5432</attribute>
       </attribute>
       <attribute>
         <code>PRESERVE_RESERVED_WORD_CASE</code>
@@ -478,7 +478,7 @@
       </attribute>
       <attribute>
         <code>SUPPORTS_TIMESTAMP_DATA_TYPE</code>
-        <attribute>Y</attribute>
+        <attribute>N</attribute>
       </attribute>
       <attribute>
         <code>USE_POOLING</code>
@@ -627,14 +627,24 @@
       <to>Merge join 14</to>
       <enabled>Y</enabled>
     </hop>
+    <hop>
+      <from>combine select</from>
+      <to>Insert / update</to>
+      <enabled>Y</enabled>
+    </hop>
+    <hop>
+      <from>total_fy</from>
+      <to>Merge join 15</to>
+      <enabled>Y</enabled>
+    </hop>
     <hop>
       <from>Merge join 14</from>
-      <to>combine select</to>
+      <to>Merge join 15</to>
       <enabled>Y</enabled>
     </hop>
     <hop>
-      <from>combine select</from>
-      <to>Insert / update</to>
+      <from>Merge join 15</from>
+      <to>combine select</to>
       <enabled>Y</enabled>
     </hop>
   </order>
@@ -649,7 +659,7 @@
       <method>none</method>
       <schema_name/>
     </partitioning>
-    <connection>dw bi indoteknik</connection>
+    <connection>dw bi indoteknik (localhost)</connection>
     <commit>100</commit>
     <update_bypassed>N</update_bypassed>
     <lookup>
@@ -746,6 +756,11 @@
         <rename>fy12</rename>
         <update>Y</update>
       </value>
+      <value>
+        <name>total_fy</name>
+        <rename>total_fy</rename>
+        <update>Y</update>
+      </value>
     </lookup>
     <attributes/>
     <cluster_schema/>
@@ -1317,6 +1332,10 @@
         <name>fy12</name>
         <rename/>
       </field>
+      <field>
+        <name>total_fy</name>
+        <rename/>
+      </field>
       <select_unspecified>N</select_unspecified>
     </fields>
     <attributes/>
@@ -1344,7 +1363,7 @@
       <method>none</method>
       <schema_name/>
     </partitioning>
-    <connection>dw bi indoteknik</connection>
+    <connection>dw bi indoteknik (localhost)</connection>
     <sql>select afrr.id, afrr.name, 
 (
 	select 0-coalesce(sum(balance),0) as ly_full from account_move_line aml
@@ -1463,7 +1482,7 @@ order by id</sql>
       <method>none</method>
       <schema_name/>
     </partitioning>
-    <connection>dw bi indoteknik</connection>
+    <connection>dw bi indoteknik (localhost)</connection>
     <sql>select afrr.id, afrr.name, 
 (
 	select 0-coalesce(sum(balance),0) as ly_current from account_move_line aml
@@ -1582,7 +1601,7 @@ order by id</sql>
       <method>none</method>
       <schema_name/>
     </partitioning>
-    <connection>dw bi indoteknik</connection>
+    <connection>dw bi indoteknik (localhost)</connection>
     <sql>-- query for ratio profit and loss
 select afrr.id, afrr.name, 
 (
@@ -1702,7 +1721,7 @@ order by id</sql>
       <method>none</method>
       <schema_name/>
     </partitioning>
-    <connection>dw bi indoteknik</connection>
+    <connection>dw bi indoteknik (localhost)</connection>
     <sql>select afrr.id, afrr.name, 
 (
 	select 0-coalesce(sum(balance),0) as fy1 from account_move_line aml
@@ -1822,7 +1841,7 @@ order by id</sql>
       <method>none</method>
       <schema_name/>
     </partitioning>
-    <connection>dw bi indoteknik</connection>
+    <connection>dw bi indoteknik (localhost)</connection>
     <sql>select afrr.id, afrr.name, 
 (
 	select 0-coalesce(sum(balance),0) as fy10 from account_move_line aml
@@ -1942,7 +1961,7 @@ order by id</sql>
       <method>none</method>
       <schema_name/>
     </partitioning>
-    <connection>dw bi indoteknik</connection>
+    <connection>dw bi indoteknik (localhost)</connection>
     <sql>select afrr.id, afrr.name, 
 (
 	select 0-coalesce(sum(balance),0) as fy11 from account_move_line aml
@@ -2062,7 +2081,7 @@ order by id</sql>
       <method>none</method>
       <schema_name/>
     </partitioning>
-    <connection>dw bi indoteknik</connection>
+    <connection>dw bi indoteknik (localhost)</connection>
     <sql>select afrr.id, afrr.name, 
 (
 	select 0-coalesce(sum(balance),0) as fy12 from account_move_line aml
@@ -2182,7 +2201,7 @@ order by id</sql>
       <method>none</method>
       <schema_name/>
     </partitioning>
-    <connection>dw bi indoteknik</connection>
+    <connection>dw bi indoteknik (localhost)</connection>
     <sql>select afrr.id, afrr.name, 
 (
 	select 0-coalesce(sum(balance),0) as fy2 from account_move_line aml
@@ -2302,7 +2321,7 @@ order by id</sql>
       <method>none</method>
       <schema_name/>
     </partitioning>
-    <connection>dw bi indoteknik</connection>
+    <connection>dw bi indoteknik (localhost)</connection>
     <sql>select afrr.id, afrr.name, 
 (
 	select 0-coalesce(sum(balance),0) as fy3 from account_move_line aml
@@ -2422,7 +2441,7 @@ order by id</sql>
       <method>none</method>
       <schema_name/>
     </partitioning>
-    <connection>dw bi indoteknik</connection>
+    <connection>dw bi indoteknik (localhost)</connection>
     <sql>select afrr.id, afrr.name, 
 (
 	select 0-coalesce(sum(balance),0) as fy4 from account_move_line aml
@@ -2542,7 +2561,7 @@ order by id</sql>
       <method>none</method>
       <schema_name/>
     </partitioning>
-    <connection>dw bi indoteknik</connection>
+    <connection>dw bi indoteknik (localhost)</connection>
     <sql>select afrr.id, afrr.name, 
 (
 	select 0-coalesce(sum(balance),0) as fy5 from account_move_line aml
@@ -2662,7 +2681,7 @@ order by id</sql>
       <method>none</method>
       <schema_name/>
     </partitioning>
-    <connection>dw bi indoteknik</connection>
+    <connection>dw bi indoteknik (localhost)</connection>
     <sql>select afrr.id, afrr.name, 
 (
 	select 0-coalesce(sum(balance),0) as fy6 from account_move_line aml
@@ -2782,7 +2801,7 @@ order by id</sql>
       <method>none</method>
       <schema_name/>
     </partitioning>
-    <connection>dw bi indoteknik</connection>
+    <connection>dw bi indoteknik (localhost)</connection>
     <sql>select afrr.id, afrr.name, 
 (
 	select 0-coalesce(sum(balance),0) as fy7 from account_move_line aml
@@ -2902,7 +2921,7 @@ order by id</sql>
       <method>none</method>
       <schema_name/>
     </partitioning>
-    <connection>dw bi indoteknik</connection>
+    <connection>dw bi indoteknik (localhost)</connection>
     <sql>select afrr.id, afrr.name, 
 (
 	select 0-coalesce(sum(balance),0) as fy8 from account_move_line aml
@@ -3022,7 +3041,7 @@ order by id</sql>
       <method>none</method>
       <schema_name/>
     </partitioning>
-    <connection>dw bi indoteknik</connection>
+    <connection>dw bi indoteknik (localhost)</connection>
     <sql>select afrr.id, afrr.name, 
 (
 	select 0-coalesce(sum(balance),0) as fy9 from account_move_line aml
@@ -3131,6 +3150,159 @@ order by id</sql>
       <draw>Y</draw>
     </GUI>
   </step>
+  <step>
+    <name>total_fy</name>
+    <type>TableInput</type>
+    <description/>
+    <distribute>Y</distribute>
+    <custom_distribution/>
+    <copies>1</copies>
+    <partitioning>
+      <method>none</method>
+      <schema_name/>
+    </partitioning>
+    <connection>dw bi indoteknik (localhost)</connection>
+    <sql>select afrr.id, afrr.name, 
+(
+	select 0-coalesce(sum(balance),0) as total_fy 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()
+)
+from account_financial_report afrr
+where afrr.parent_id = 321
+order by id</sql>
+    <limit>0</limit>
+    <lookup/>
+    <execute_each_row>N</execute_each_row>
+    <variables_active>N</variables_active>
+    <lazy_conversion_active>N</lazy_conversion_active>
+    <cached_row_meta_active>N</cached_row_meta_active>
+    <row-meta>
+      <value-meta>
+        <type>Integer</type>
+        <storagetype>normal</storagetype>
+        <name>id</name>
+        <length>9</length>
+        <precision>0</precision>
+        <origin>month12 2</origin>
+        <comments>id</comments>
+        <conversion_Mask>####0;-####0</conversion_Mask>
+        <decimal_symbol>.</decimal_symbol>
+        <grouping_symbol>,</grouping_symbol>
+        <currency_symbol/>
+        <trim_type>none</trim_type>
+        <case_insensitive>N</case_insensitive>
+        <collator_disabled>Y</collator_disabled>
+        <collator_strength>0</collator_strength>
+        <sort_descending>N</sort_descending>
+        <output_padding>N</output_padding>
+        <date_format_lenient>N</date_format_lenient>
+        <date_format_locale>en_ID</date_format_locale>
+        <date_format_timezone>Asia/Bangkok</date_format_timezone>
+        <lenient_string_to_number>N</lenient_string_to_number>
+      </value-meta>
+      <value-meta>
+        <type>String</type>
+        <storagetype>normal</storagetype>
+        <name>name</name>
+        <length>2147483647</length>
+        <precision>-1</precision>
+        <origin>month12 2</origin>
+        <comments>name</comments>
+        <conversion_Mask/>
+        <decimal_symbol>.</decimal_symbol>
+        <grouping_symbol>,</grouping_symbol>
+        <currency_symbol/>
+        <trim_type>none</trim_type>
+        <case_insensitive>N</case_insensitive>
+        <collator_disabled>Y</collator_disabled>
+        <collator_strength>0</collator_strength>
+        <sort_descending>N</sort_descending>
+        <output_padding>N</output_padding>
+        <date_format_lenient>N</date_format_lenient>
+        <date_format_locale>en_ID</date_format_locale>
+        <date_format_timezone>Asia/Bangkok</date_format_timezone>
+        <lenient_string_to_number>N</lenient_string_to_number>
+      </value-meta>
+      <value-meta>
+        <type>BigNumber</type>
+        <storagetype>normal</storagetype>
+        <name>total_fy</name>
+        <length>-1</length>
+        <precision>-1</precision>
+        <origin>month12 2</origin>
+        <comments>total_fy</comments>
+        <conversion_Mask>######0.0###################;-######0.0###################</conversion_Mask>
+        <decimal_symbol>.</decimal_symbol>
+        <grouping_symbol/>
+        <currency_symbol/>
+        <trim_type>none</trim_type>
+        <case_insensitive>N</case_insensitive>
+        <collator_disabled>Y</collator_disabled>
+        <collator_strength>0</collator_strength>
+        <sort_descending>N</sort_descending>
+        <output_padding>N</output_padding>
+        <date_format_lenient>N</date_format_lenient>
+        <date_format_locale>en_ID</date_format_locale>
+        <date_format_timezone>Asia/Bangkok</date_format_timezone>
+        <lenient_string_to_number>N</lenient_string_to_number>
+      </value-meta>
+    </row-meta>
+    <attributes/>
+    <cluster_schema/>
+    <remotesteps>
+      <input>
+      </input>
+      <output>
+      </output>
+    </remotesteps>
+    <GUI>
+      <xloc>608</xloc>
+      <yloc>336</yloc>
+      <draw>Y</draw>
+    </GUI>
+  </step>
+  <step>
+    <name>Merge join 15</name>
+    <type>MergeJoin</type>
+    <description/>
+    <distribute>Y</distribute>
+    <custom_distribution/>
+    <copies>1</copies>
+    <partitioning>
+      <method>none</method>
+      <schema_name/>
+    </partitioning>
+    <join_type>INNER</join_type>
+    <step1>Merge join 14</step1>
+    <step2>total_fy</step2>
+    <keys_1>
+      <key>id</key>
+    </keys_1>
+    <keys_2>
+      <key>id</key>
+    </keys_2>
+    <attributes/>
+    <cluster_schema/>
+    <remotesteps>
+      <input>
+      </input>
+      <output>
+      </output>
+    </remotesteps>
+    <GUI>
+      <xloc>608</xloc>
+      <yloc>256</yloc>
+      <draw>Y</draw>
+    </GUI>
+  </step>
   <step_error_handling>
   </step_error_handling>
   <slave-step-copy-partition-distribution>
-- 
cgit v1.2.3