change source data

2 files changed, 67 insertions, 90 deletions

diff --git a/import_all_data_bi_indoteknik.kjb b/import_all_data_bi_indoteknik.kjb
index 339bef8..a3bdf5b 100644
--- a/import_all_data_bi_indoteknik.kjb
+++ b/import_all_data_bi_indoteknik.kjb
@@ -383,7 +383,7 @@
       <draw>Y</draw>
       <nr>0</nr>
       <xloc>480</xloc>
-      <yloc>128</yloc>
+      <yloc>32</yloc>
       <attributes_kjc/>
     </entry>
     <entry>
@@ -421,7 +421,7 @@
       <parallel>N</parallel>
       <draw>Y</draw>
       <nr>0</nr>
-      <xloc>704</xloc>
+      <xloc>768</xloc>
       <yloc>128</yloc>
       <attributes_kjc/>
     </entry>
@@ -835,8 +835,8 @@
       <parallel>N</parallel>
       <draw>Y</draw>
       <nr>0</nr>
-      <xloc>928</xloc>
-      <yloc>416</yloc>
+      <xloc>1344</xloc>
+      <yloc>48</yloc>
       <attributes_kjc/>
     </entry>
     <entry>
@@ -874,8 +874,8 @@
       <parallel>N</parallel>
       <draw>Y</draw>
       <nr>0</nr>
-      <xloc>1152</xloc>
-      <yloc>416</yloc>
+      <xloc>1440</xloc>
+      <yloc>176</yloc>
       <attributes_kjc/>
     </entry>
     <entry>
@@ -913,8 +913,8 @@
       <parallel>N</parallel>
       <draw>Y</draw>
       <nr>0</nr>
-      <xloc>1152</xloc>
-      <yloc>544</yloc>
+      <xloc>1408</xloc>
+      <yloc>400</yloc>
       <attributes_kjc/>
     </entry>
     <entry>
@@ -952,7 +952,7 @@
       <parallel>N</parallel>
       <draw>Y</draw>
       <nr>0</nr>
-      <xloc>928</xloc>
+      <xloc>1344</xloc>
       <yloc>544</yloc>
       <attributes_kjc/>
     </entry>
@@ -991,7 +991,7 @@
       <parallel>N</parallel>
       <draw>Y</draw>
       <nr>0</nr>
-      <xloc>704</xloc>
+      <xloc>1008</xloc>
       <yloc>544</yloc>
       <attributes_kjc/>
     </entry>
@@ -1016,24 +1016,6 @@
       <unconditional>N</unconditional>
     </hop>
     <hop>
-      <from>data so adempiere</from>
-      <to>data so odoo</to>
-      <from_nr>0</from_nr>
-      <to_nr>0</to_nr>
-      <enabled>Y</enabled>
-      <evaluation>Y</evaluation>
-      <unconditional>N</unconditional>
-    </hop>
-    <hop>
-      <from>data so odoo</from>
-      <to>refresh_materialized_view</to>
-      <from_nr>0</from_nr>
-      <to_nr>0</to_nr>
-      <enabled>Y</enabled>
-      <evaluation>Y</evaluation>
-      <unconditional>N</unconditional>
-    </hop>
-    <hop>
       <from>refresh_materialized_view</from>
       <to>import fixco</to>
       <from_nr>0</from_nr>
@@ -1115,15 +1097,6 @@
       <unconditional>N</unconditional>
     </hop>
     <hop>
-      <from>import sale order</from>
-      <to>import account move</to>
-      <from_nr>0</from_nr>
-      <to_nr>0</to_nr>
-      <enabled>Y</enabled>
-      <evaluation>Y</evaluation>
-      <unconditional>N</unconditional>
-    </hop>
-    <hop>
       <from>import account move</from>
       <to>import account move line</to>
       <from_nr>0</from_nr>
@@ -1159,6 +1132,33 @@
       <evaluation>Y</evaluation>
       <unconditional>N</unconditional>
     </hop>
+    <hop>
+      <from>data so adempiere</from>
+      <to>import account move</to>
+      <from_nr>0</from_nr>
+      <to_nr>0</to_nr>
+      <enabled>Y</enabled>
+      <evaluation>Y</evaluation>
+      <unconditional>N</unconditional>
+    </hop>
+    <hop>
+      <from>import user</from>
+      <to>data so odoo</to>
+      <from_nr>0</from_nr>
+      <to_nr>0</to_nr>
+      <enabled>Y</enabled>
+      <evaluation>Y</evaluation>
+      <unconditional>N</unconditional>
+    </hop>
+    <hop>
+      <from>data so odoo</from>
+      <to>refresh_materialized_view</to>
+      <from_nr>0</from_nr>
+      <to_nr>0</to_nr>
+      <enabled>Y</enabled>
+      <evaluation>Y</evaluation>
+      <unconditional>N</unconditional>
+    </hop>
   </hops>
   <notepads>
   </notepads>
diff --git a/import_odoo_14_to_data_warehouse.ktr b/import_odoo_14_to_data_warehouse.ktr
index 3791db9..cf3cc07 100644
--- a/import_odoo_14_to_data_warehouse.ktr
+++ b/import_odoo_14_to_data_warehouse.ktr
@@ -486,57 +486,6 @@
       </attribute>
     </attributes>
   </connection>
-  <connection>
-    <name>erp indoteknik production (localhost)</name>
-    <server>10.148.0.3</server>
-    <type>POSTGRESQL</type>
-    <access>Native</access>
-    <database>erp_indoteknik</database>
-    <port>5432</port>
-    <username>odoo</username>
-    <password>Encrypted 2be98afc82bdfd8dd9a2ca45acc83f6c3</password>
-    <servername/>
-    <data_tablespace/>
-    <index_tablespace/>
-    <attributes>
-      <attribute>
-        <code>FORCE_IDENTIFIERS_TO_LOWERCASE</code>
-        <attribute>N</attribute>
-      </attribute>
-      <attribute>
-        <code>FORCE_IDENTIFIERS_TO_UPPERCASE</code>
-        <attribute>N</attribute>
-      </attribute>
-      <attribute>
-        <code>IS_CLUSTERED</code>
-        <attribute>N</attribute>
-      </attribute>
-      <attribute>
-        <code>PORT_NUMBER</code>
-        <attribute>5432</attribute>
-      </attribute>
-      <attribute>
-        <code>PRESERVE_RESERVED_WORD_CASE</code>
-        <attribute>Y</attribute>
-      </attribute>
-      <attribute>
-        <code>QUOTE_ALL_FIELDS</code>
-        <attribute>N</attribute>
-      </attribute>
-      <attribute>
-        <code>SUPPORTS_BOOLEAN_DATA_TYPE</code>
-        <attribute>Y</attribute>
-      </attribute>
-      <attribute>
-        <code>SUPPORTS_TIMESTAMP_DATA_TYPE</code>
-        <attribute>N</attribute>
-      </attribute>
-      <attribute>
-        <code>USE_POOLING</code>
-        <attribute>N</attribute>
-      </attribute>
-    </attributes>
-  </connection>
   <order>
     <hop>
       <from>Table input</from>
@@ -771,8 +720,36 @@
       <method>none</method>
       <schema_name/>
     </partitioning>
-    <connection>erp indoteknik production (localhost)</connection>
-    <sql>select * from v_total_omzet_by_invoice</sql>
+    <connection>dw bi indoteknik (localhost)</connection>
+    <sql>SELECT m.name AS invoice,
+    i.name AS industry,
+    ( SELECT rpp.name
+           FROM res_partner rpp
+             JOIN res_users ru ON ru.partner_id = rpp.id
+          WHERE ru.id = m.invoice_user_id) AS salesperson,
+    m.invoice_date,
+    to_char(m.invoice_date::timestamp with time zone, 'YYYY-MM'::text) AS year_month_invoice_date,
+    to_char(m.invoice_date::timestamp with time zone, 'YYYY'::text) AS year,
+    to_char(m.invoice_date::timestamp with time zone, 'MM'::text) AS month,
+    mf.name AS brand,
+    p.name AS product,
+    p.item_code,
+    ml.quantity,
+    ml.price_unit,
+    ml.price_subtotal,
+    ml.price_total,
+    rp.name AS customer,
+    rp.id AS partner_id,
+    get_quarter(to_char(m.invoice_date::timestamp with time zone, 'MM'::text)::character varying) AS quarter,
+    ml.id AS account_move_line_id
+   FROM account_move m
+     JOIN res_partner rp ON rp.id = m.partner_id
+     JOIN account_move_line ml ON ml.move_id = m.id
+     JOIN product p ON p.product_id = ml.product_id
+     LEFT JOIN brand mf ON mf.id = p.brand_id
+     LEFT JOIN res_partner_industry i ON i.id = rp.industry_id
+  WHERE m.move_type::text = 'out_invoice'::text AND m.state::text = 'posted'::text 
+  AND ml.write_date >= (now() - '15 days'::interval)</sql>
     <limit>0</limit>
     <lookup/>
     <execute_each_row>N</execute_each_row>