summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorstephanchrst <stephanchrst@gmail.com>2023-08-25 15:54:45 +0700
committerstephanchrst <stephanchrst@gmail.com>2023-08-25 15:54:45 +0700
commit57b5844479d269a0a940f9bd4a1d28bef0cbb060 (patch)
treeca6969e7cf88cecd5ddf987101fdfa4babebaba1
parent644d994308d095e36c41dbf9f9834abbdecf49fe (diff)
temporary remove new concept of purchase pricelist and sales pricelist
-rw-r--r--import_all_data_bi_indoteknik.kjb336
-rw-r--r--import_purchase_order.ktr330
2 files changed, 235 insertions, 431 deletions
diff --git a/import_all_data_bi_indoteknik.kjb b/import_all_data_bi_indoteknik.kjb
index 6b3f602..e73e8c9 100644
--- a/import_all_data_bi_indoteknik.kjb
+++ b/import_all_data_bi_indoteknik.kjb
@@ -956,279 +956,6 @@
<yloc>560</yloc>
<attributes_kjc/>
</entry>
- <entry>
- <name>import purchase pricelist</name>
- <description/>
- <type>TRANS</type>
- <attributes/>
- <specification_method>filename</specification_method>
- <trans_object_id/>
- <filename>/home/superman/runapps/import_purchase_pricelist.ktr</filename>
- <transname/>
- <arg_from_previous>N</arg_from_previous>
- <params_from_previous>N</params_from_previous>
- <exec_per_row>N</exec_per_row>
- <clear_rows>N</clear_rows>
- <clear_files>N</clear_files>
- <set_logfile>N</set_logfile>
- <logfile/>
- <logext/>
- <add_date>N</add_date>
- <add_time>N</add_time>
- <loglevel>Basic</loglevel>
- <cluster>N</cluster>
- <slave_server_name/>
- <set_append_logfile>N</set_append_logfile>
- <wait_until_finished>Y</wait_until_finished>
- <follow_abort_remote>N</follow_abort_remote>
- <create_parent_folder>N</create_parent_folder>
- <logging_remote_work>N</logging_remote_work>
- <run_configuration>Pentaho local</run_configuration>
- <suppress_result_data>N</suppress_result_data>
- <parameters>
- <pass_all_parameters>Y</pass_all_parameters>
- </parameters>
- <parallel>N</parallel>
- <draw>Y</draw>
- <nr>0</nr>
- <xloc>704</xloc>
- <yloc>560</yloc>
- <attributes_kjc/>
- </entry>
- <entry>
- <name>update count trx po</name>
- <description/>
- <type>TRANS</type>
- <attributes/>
- <specification_method>filename</specification_method>
- <trans_object_id/>
- <filename>/home/superman/runapps/update_count_trx_po_product.ktr</filename>
- <transname/>
- <arg_from_previous>N</arg_from_previous>
- <params_from_previous>N</params_from_previous>
- <exec_per_row>N</exec_per_row>
- <clear_rows>N</clear_rows>
- <clear_files>N</clear_files>
- <set_logfile>N</set_logfile>
- <logfile/>
- <logext/>
- <add_date>N</add_date>
- <add_time>N</add_time>
- <loglevel>Basic</loglevel>
- <cluster>N</cluster>
- <slave_server_name/>
- <set_append_logfile>N</set_append_logfile>
- <wait_until_finished>Y</wait_until_finished>
- <follow_abort_remote>N</follow_abort_remote>
- <create_parent_folder>N</create_parent_folder>
- <logging_remote_work>N</logging_remote_work>
- <run_configuration>Pentaho local</run_configuration>
- <suppress_result_data>N</suppress_result_data>
- <parameters>
- <pass_all_parameters>Y</pass_all_parameters>
- </parameters>
- <parallel>N</parallel>
- <draw>Y</draw>
- <nr>0</nr>
- <xloc>480</xloc>
- <yloc>560</yloc>
- <attributes_kjc/>
- </entry>
- <entry>
- <name>update count po vendor</name>
- <description/>
- <type>TRANS</type>
- <attributes/>
- <specification_method>filename</specification_method>
- <trans_object_id/>
- <filename>/home/superman/runapps/update_count_trx_po_vendor.ktr</filename>
- <transname/>
- <arg_from_previous>N</arg_from_previous>
- <params_from_previous>N</params_from_previous>
- <exec_per_row>N</exec_per_row>
- <clear_rows>N</clear_rows>
- <clear_files>N</clear_files>
- <set_logfile>N</set_logfile>
- <logfile/>
- <logext/>
- <add_date>N</add_date>
- <add_time>N</add_time>
- <loglevel>Basic</loglevel>
- <cluster>N</cluster>
- <slave_server_name/>
- <set_append_logfile>N</set_append_logfile>
- <wait_until_finished>Y</wait_until_finished>
- <follow_abort_remote>N</follow_abort_remote>
- <create_parent_folder>N</create_parent_folder>
- <logging_remote_work>N</logging_remote_work>
- <run_configuration>Pentaho local</run_configuration>
- <suppress_result_data>N</suppress_result_data>
- <parameters>
- <pass_all_parameters>Y</pass_all_parameters>
- </parameters>
- <parallel>N</parallel>
- <draw>Y</draw>
- <nr>0</nr>
- <xloc>256</xloc>
- <yloc>560</yloc>
- <attributes_kjc/>
- </entry>
- <entry>
- <name>refresh purchase price</name>
- <description/>
- <type>TRANS</type>
- <attributes/>
- <specification_method>filename</specification_method>
- <trans_object_id/>
- <filename>/home/superman/runapps/refresh_materialized_view_purchase_price.ktr</filename>
- <transname/>
- <arg_from_previous>N</arg_from_previous>
- <params_from_previous>N</params_from_previous>
- <exec_per_row>N</exec_per_row>
- <clear_rows>N</clear_rows>
- <clear_files>N</clear_files>
- <set_logfile>N</set_logfile>
- <logfile/>
- <logext/>
- <add_date>N</add_date>
- <add_time>N</add_time>
- <loglevel>Basic</loglevel>
- <cluster>N</cluster>
- <slave_server_name/>
- <set_append_logfile>N</set_append_logfile>
- <wait_until_finished>Y</wait_until_finished>
- <follow_abort_remote>N</follow_abort_remote>
- <create_parent_folder>N</create_parent_folder>
- <logging_remote_work>N</logging_remote_work>
- <run_configuration>Pentaho local</run_configuration>
- <suppress_result_data>N</suppress_result_data>
- <parameters>
- <pass_all_parameters>Y</pass_all_parameters>
- </parameters>
- <parallel>N</parallel>
- <draw>Y</draw>
- <nr>0</nr>
- <xloc>256</xloc>
- <yloc>704</yloc>
- <attributes_kjc/>
- </entry>
- <entry>
- <name>import price group</name>
- <description/>
- <type>TRANS</type>
- <attributes/>
- <specification_method>filename</specification_method>
- <trans_object_id/>
- <filename>/home/superman/runapps/import_price_group.ktr</filename>
- <transname/>
- <arg_from_previous>N</arg_from_previous>
- <params_from_previous>N</params_from_previous>
- <exec_per_row>N</exec_per_row>
- <clear_rows>N</clear_rows>
- <clear_files>N</clear_files>
- <set_logfile>N</set_logfile>
- <logfile/>
- <logext/>
- <add_date>N</add_date>
- <add_time>N</add_time>
- <loglevel>Basic</loglevel>
- <cluster>N</cluster>
- <slave_server_name/>
- <set_append_logfile>N</set_append_logfile>
- <wait_until_finished>Y</wait_until_finished>
- <follow_abort_remote>N</follow_abort_remote>
- <create_parent_folder>N</create_parent_folder>
- <logging_remote_work>N</logging_remote_work>
- <run_configuration>Pentaho local</run_configuration>
- <suppress_result_data>N</suppress_result_data>
- <parameters>
- <pass_all_parameters>Y</pass_all_parameters>
- </parameters>
- <parallel>N</parallel>
- <draw>Y</draw>
- <nr>0</nr>
- <xloc>480</xloc>
- <yloc>704</yloc>
- <attributes_kjc/>
- </entry>
- <entry>
- <name>calculate price</name>
- <description/>
- <type>TRANS</type>
- <attributes/>
- <specification_method>filename</specification_method>
- <trans_object_id/>
- <filename>/home/superman/runapps/calculate_price_after_disc.ktr</filename>
- <transname/>
- <arg_from_previous>N</arg_from_previous>
- <params_from_previous>N</params_from_previous>
- <exec_per_row>N</exec_per_row>
- <clear_rows>N</clear_rows>
- <clear_files>N</clear_files>
- <set_logfile>N</set_logfile>
- <logfile/>
- <logext/>
- <add_date>N</add_date>
- <add_time>N</add_time>
- <loglevel>Basic</loglevel>
- <cluster>N</cluster>
- <slave_server_name/>
- <set_append_logfile>N</set_append_logfile>
- <wait_until_finished>Y</wait_until_finished>
- <follow_abort_remote>N</follow_abort_remote>
- <create_parent_folder>N</create_parent_folder>
- <logging_remote_work>N</logging_remote_work>
- <run_configuration>Pentaho local</run_configuration>
- <suppress_result_data>N</suppress_result_data>
- <parameters>
- <pass_all_parameters>Y</pass_all_parameters>
- </parameters>
- <parallel>N</parallel>
- <draw>Y</draw>
- <nr>0</nr>
- <xloc>624</xloc>
- <yloc>704</yloc>
- <attributes_kjc/>
- </entry>
- <entry>
- <name>refresh mv pricelist</name>
- <description/>
- <type>TRANS</type>
- <attributes/>
- <specification_method>filename</specification_method>
- <trans_object_id/>
- <filename>/home/superman/runapps/refresh_materialized_view_pricelist.ktr</filename>
- <transname/>
- <arg_from_previous>N</arg_from_previous>
- <params_from_previous>N</params_from_previous>
- <exec_per_row>N</exec_per_row>
- <clear_rows>N</clear_rows>
- <clear_files>N</clear_files>
- <set_logfile>N</set_logfile>
- <logfile/>
- <logext/>
- <add_date>N</add_date>
- <add_time>N</add_time>
- <loglevel>Basic</loglevel>
- <cluster>N</cluster>
- <slave_server_name/>
- <set_append_logfile>N</set_append_logfile>
- <wait_until_finished>Y</wait_until_finished>
- <follow_abort_remote>N</follow_abort_remote>
- <create_parent_folder>N</create_parent_folder>
- <logging_remote_work>N</logging_remote_work>
- <run_configuration>Pentaho local</run_configuration>
- <suppress_result_data>N</suppress_result_data>
- <parameters>
- <pass_all_parameters>Y</pass_all_parameters>
- </parameters>
- <parallel>N</parallel>
- <draw>Y</draw>
- <nr>0</nr>
- <xloc>816</xloc>
- <yloc>704</yloc>
- <attributes_kjc/>
- </entry>
</entries>
<hops>
<hop>
@@ -1384,69 +1111,6 @@
<evaluation>Y</evaluation>
<unconditional>N</unconditional>
</hop>
- <hop>
- <from>import purchase order</from>
- <to>import purchase pricelist</to>
- <from_nr>0</from_nr>
- <to_nr>0</to_nr>
- <enabled>Y</enabled>
- <evaluation>Y</evaluation>
- <unconditional>N</unconditional>
- </hop>
- <hop>
- <from>import purchase pricelist</from>
- <to>update count trx po</to>
- <from_nr>0</from_nr>
- <to_nr>0</to_nr>
- <enabled>Y</enabled>
- <evaluation>Y</evaluation>
- <unconditional>N</unconditional>
- </hop>
- <hop>
- <from>update count trx po</from>
- <to>update count po vendor</to>
- <from_nr>0</from_nr>
- <to_nr>0</to_nr>
- <enabled>Y</enabled>
- <evaluation>Y</evaluation>
- <unconditional>N</unconditional>
- </hop>
- <hop>
- <from>update count po vendor</from>
- <to>refresh purchase price</to>
- <from_nr>0</from_nr>
- <to_nr>0</to_nr>
- <enabled>Y</enabled>
- <evaluation>Y</evaluation>
- <unconditional>N</unconditional>
- </hop>
- <hop>
- <from>refresh purchase price</from>
- <to>import price group</to>
- <from_nr>0</from_nr>
- <to_nr>0</to_nr>
- <enabled>Y</enabled>
- <evaluation>Y</evaluation>
- <unconditional>N</unconditional>
- </hop>
- <hop>
- <from>import price group</from>
- <to>calculate price</to>
- <from_nr>0</from_nr>
- <to_nr>0</to_nr>
- <enabled>Y</enabled>
- <evaluation>Y</evaluation>
- <unconditional>N</unconditional>
- </hop>
- <hop>
- <from>calculate price</from>
- <to>refresh mv pricelist</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_purchase_order.ktr b/import_purchase_order.ktr
index afb9963..ee85cad 100644
--- a/import_purchase_order.ktr
+++ b/import_purchase_order.ktr
@@ -430,7 +430,7 @@
<created_date>2023/08/18 10:27:05.868</created_date>
<modified_user>-</modified_user>
<modified_date>2023/08/18 10:27:05.868</modified_date>
- <key_for_session_key/>
+ <key_for_session_key>H4sIAAAAAAAAAAMAAAAAAAAAAAA=</key_for_session_key>
<is_key_private>N</is_key_private>
</info>
<notepads>
@@ -545,6 +545,119 @@
</hop>
</order>
<step>
+ <name>Insert / update</name>
+ <type>InsertUpdate</type>
+ <description/>
+ <distribute>Y</distribute>
+ <custom_distribution/>
+ <copies>1</copies>
+ <partitioning>
+ <method>none</method>
+ <schema_name/>
+ </partitioning>
+ <connection>dw bi indoteknik</connection>
+ <commit>100</commit>
+ <update_bypassed>N</update_bypassed>
+ <lookup>
+ <schema>public</schema>
+ <table>purchase_order_dw</table>
+ <key>
+ <name>line_id</name>
+ <field>line_id</field>
+ <condition>=</condition>
+ <name2/>
+ </key>
+ <value>
+ <name>po_date</name>
+ <rename>po_date</rename>
+ <update>Y</update>
+ </value>
+ <value>
+ <name>line_date</name>
+ <rename>line_date</rename>
+ <update>Y</update>
+ </value>
+ <value>
+ <name>documentno</name>
+ <rename>documentno</rename>
+ <update>Y</update>
+ </value>
+ <value>
+ <name>state</name>
+ <rename>state</rename>
+ <update>Y</update>
+ </value>
+ <value>
+ <name>partner_id</name>
+ <rename>partner_id</rename>
+ <update>Y</update>
+ </value>
+ <value>
+ <name>vendor</name>
+ <rename>vendor</rename>
+ <update>Y</update>
+ </value>
+ <value>
+ <name>product_id</name>
+ <rename>product_id</rename>
+ <update>Y</update>
+ </value>
+ <value>
+ <name>qty</name>
+ <rename>qty</rename>
+ <update>Y</update>
+ </value>
+ <value>
+ <name>price_unit</name>
+ <rename>price_unit</rename>
+ <update>Y</update>
+ </value>
+ <value>
+ <name>price_subtotal</name>
+ <rename>price_subtotal</rename>
+ <update>Y</update>
+ </value>
+ <value>
+ <name>line_id</name>
+ <rename>line_id</rename>
+ <update>N</update>
+ </value>
+ <value>
+ <name>year_month</name>
+ <rename>year_month</rename>
+ <update>Y</update>
+ </value>
+ <value>
+ <name>year_date</name>
+ <rename>year_date</rename>
+ <update>Y</update>
+ </value>
+ <value>
+ <name>month_date</name>
+ <rename>month_date</rename>
+ <update>Y</update>
+ </value>
+ <value>
+ <name>day_date</name>
+ <rename>day_date</rename>
+ <update>Y</update>
+ </value>
+ </lookup>
+ <attributes/>
+ <cluster_schema/>
+ <remotesteps>
+ <input>
+ </input>
+ <output>
+ </output>
+ </remotesteps>
+ <GUI>
+ <xloc>432</xloc>
+ <yloc>96</yloc>
+ <draw>Y</draw>
+ </GUI>
+ </step>
+ <step>
<name>Table input</name>
<type>TableInput</type>
<description/>
@@ -558,7 +671,12 @@
<connection>erp indoteknik production</connection>
<sql>select po.write_date as po_date, pol.write_date as line_date, po.name as documentno,
po.state, po.partner_id, rp.name as vendor, pol.product_id, pol.product_uom_qty as qty,
-pol.price_unit, pol.price_subtotal, pol.id as line_id
+pol.price_unit, pol.price_subtotal, pol.id as line_id,
+to_char(pol.write_date::timestamp with time zone, 'YYYY-MM'::text) AS year_month,
+to_char(pol.write_date::timestamp with time zone, 'YYYY'::text) as year_date,
+to_char(pol.write_date::timestamp with time zone, 'MM'::text) as month_date,
+to_char(pol.write_date::timestamp with time zone, 'DD'::text) as day_date,
+get_quarter(to_char(pol.write_date::timestamp with time zone, 'MM'::text))
from purchase_order po
join res_partner rp on rp.id = po.partner_id
join purchase_order_line pol on pol.order_id = po.id
@@ -823,6 +941,121 @@ where pol.write_date >= (now() - '5 days'::interval)</sql>
<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>year_month</name>
+ <length>2147483647</length>
+ <precision>-1</precision>
+ <origin>Table input</origin>
+ <comments>year_month</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_US</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>year_date</name>
+ <length>2147483647</length>
+ <precision>-1</precision>
+ <origin>Table input</origin>
+ <comments>year_date</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_US</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>month_date</name>
+ <length>2147483647</length>
+ <precision>-1</precision>
+ <origin>Table input</origin>
+ <comments>month_date</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_US</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>day_date</name>
+ <length>2147483647</length>
+ <precision>-1</precision>
+ <origin>Table input</origin>
+ <comments>day_date</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_US</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>get_quarter</name>
+ <length>2147483647</length>
+ <precision>-1</precision>
+ <origin>Table input</origin>
+ <comments>get_quarter</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_US</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/>
@@ -838,99 +1071,6 @@ where pol.write_date >= (now() - '5 days'::interval)</sql>
<draw>Y</draw>
</GUI>
</step>
- <step>
- <name>Insert / update</name>
- <type>InsertUpdate</type>
- <description/>
- <distribute>Y</distribute>
- <custom_distribution/>
- <copies>1</copies>
- <partitioning>
- <method>none</method>
- <schema_name/>
- </partitioning>
- <connection>dw bi indoteknik</connection>
- <commit>100</commit>
- <update_bypassed>N</update_bypassed>
- <lookup>
- <schema>public</schema>
- <table>purchase_order_dw</table>
- <key>
- <name>line_id</name>
- <field>line_id</field>
- <condition>=</condition>
- <name2/>
- </key>
- <value>
- <name>po_date</name>
- <rename>po_date</rename>
- <update>Y</update>
- </value>
- <value>
- <name>line_date</name>
- <rename>line_date</rename>
- <update>Y</update>
- </value>
- <value>
- <name>documentno</name>
- <rename>documentno</rename>
- <update>Y</update>
- </value>
- <value>
- <name>state</name>
- <rename>state</rename>
- <update>Y</update>
- </value>
- <value>
- <name>partner_id</name>
- <rename>partner_id</rename>
- <update>Y</update>
- </value>
- <value>
- <name>vendor</name>
- <rename>vendor</rename>
- <update>Y</update>
- </value>
- <value>
- <name>product_id</name>
- <rename>product_id</rename>
- <update>Y</update>
- </value>
- <value>
- <name>qty</name>
- <rename>qty</rename>
- <update>Y</update>
- </value>
- <value>
- <name>price_unit</name>
- <rename>price_unit</rename>
- <update>Y</update>
- </value>
- <value>
- <name>price_subtotal</name>
- <rename>price_subtotal</rename>
- <update>Y</update>
- </value>
- <value>
- <name>line_id</name>
- <rename>line_id</rename>
- <update>N</update>
- </value>
- </lookup>
- <attributes/>
- <cluster_schema/>
- <remotesteps>
- <input>
- </input>
- <output>
- </output>
- </remotesteps>
- <GUI>
- <xloc>432</xloc>
- <yloc>96</yloc>
- <draw>Y</draw>
- </GUI>
- </step>
<step_error_handling>
</step_error_handling>
<slave-step-copy-partition-distribution>