From 9828160c003af8915fd837f2ae4d3271387ea8e3 Mon Sep 17 00:00:00 2001
From: stephanchrst <stephanchrst@gmail.com>
Date: Mon, 18 Sep 2023 11:12:39 +0700
Subject: refactor insert update pricelist

---
 insert_update_pricelist.ktr | 210 +++++++++++++++++++++++++++++++++++++-------
 1 file changed, 179 insertions(+), 31 deletions(-)

(limited to 'insert_update_pricelist.ktr')

diff --git a/insert_update_pricelist.ktr b/insert_update_pricelist.ktr
index 02d665a..f14e7f6 100644
--- a/insert_update_pricelist.ktr
+++ b/insert_update_pricelist.ktr
@@ -6,6 +6,7 @@
     <extended_description/>
     <trans_version/>
     <trans_type>Normal</trans_type>
+    <trans_status>0</trans_status>
     <directory>/</directory>
     <parameters>
     </parameters>
@@ -491,7 +492,7 @@
     <server>localhost</server>
     <type>POSTGRESQL</type>
     <access>Native</access>
-    <database>indoteknik_20230913</database>
+    <database>indoteknik_20230914</database>
     <port>5432</port>
     <username>odoo</username>
     <password>Encrypted 2be98afc86aa7f2e4cb79ce10d196a0d5</password>
@@ -603,18 +604,6 @@
         <precision>-1</precision>
         <set_empty_string>N</set_empty_string>
       </field>
-      <field>
-        <name>base</name>
-        <type>String</type>
-        <format/>
-        <currency/>
-        <decimal/>
-        <group/>
-        <nullif>list_price</nullif>
-        <length>-1</length>
-        <precision>-1</precision>
-        <set_empty_string>N</set_empty_string>
-      </field>
       <field>
         <name>pricelist_id</name>
         <type>Integer</type>
@@ -622,7 +611,7 @@
         <currency/>
         <decimal/>
         <group/>
-        <nullif>1</nullif>
+        <nullif>17022</nullif>
         <length>-1</length>
         <precision>-1</precision>
         <set_empty_string>N</set_empty_string>
@@ -824,11 +813,6 @@
         <rename>applied_on_1</rename>
         <update>Y</update>
       </value>
-      <value>
-        <name>base</name>
-        <rename>base_1</rename>
-        <update>Y</update>
-      </value>
       <value>
         <name>pricelist_id</name>
         <rename>pricelist_id_1</rename>
@@ -1053,8 +1037,11 @@
       <schema_name/>
     </partitioning>
     <connection>dw bi indoteknik</connection>
-    <sql>select pc.product_id, p.template_id, price_before_disc, disc_tier1, 
-disc_tier2, disc_tier3, disc_tier4, disc_tier5
+    <sql>select pc.product_id, p.template_id, round(price_before_disc) as price_before_disc, 
+disc_tier1, disc_tier2, disc_tier3, disc_tier4, disc_tier5,
+round(price_tier1) as price_tier1, round(price_tier2) as price_tier2, 
+round(price_tier3) as price_tier3, round(price_tier4) as price_tier4, 
+round(price_tier5) as price_tier5
 from v_pricelist_compute pc
 join product p on p.product_id = pc.product_id
 order by product_id</sql>
@@ -1088,6 +1075,29 @@ order by product_id</sql>
         <date_format_timezone>Asia/Bangkok</date_format_timezone>
         <lenient_string_to_number>N</lenient_string_to_number>
       </value-meta>
+      <value-meta>
+        <type>Integer</type>
+        <storagetype>normal</storagetype>
+        <name>template_id</name>
+        <length>9</length>
+        <precision>0</precision>
+        <origin>price compute</origin>
+        <comments>template_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_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>BigNumber</type>
         <storagetype>normal</storagetype>
@@ -1114,11 +1124,11 @@ order by product_id</sql>
       <value-meta>
         <type>BigNumber</type>
         <storagetype>normal</storagetype>
-        <name>disc_web_tier1</name>
+        <name>disc_tier1</name>
         <length>-1</length>
         <precision>-1</precision>
         <origin>price compute</origin>
-        <comments>disc_web_tier1</comments>
+        <comments>disc_tier1</comments>
         <conversion_Mask>######0.0###################;-######0.0###################</conversion_Mask>
         <decimal_symbol>.</decimal_symbol>
         <grouping_symbol/>
@@ -1181,16 +1191,154 @@ order by product_id</sql>
         <lenient_string_to_number>N</lenient_string_to_number>
       </value-meta>
       <value-meta>
-        <type>Integer</type>
+        <type>BigNumber</type>
         <storagetype>normal</storagetype>
-        <name>template_id</name>
-        <length>9</length>
-        <precision>0</precision>
+        <name>disc_tier4</name>
+        <length>-1</length>
+        <precision>-1</precision>
         <origin>price compute</origin>
-        <comments>template_id</comments>
-        <conversion_Mask>####0;-####0</conversion_Mask>
+        <comments>disc_tier4</comments>
+        <conversion_Mask>######0.0###################;-######0.0###################</conversion_Mask>
         <decimal_symbol>.</decimal_symbol>
-        <grouping_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>BigNumber</type>
+        <storagetype>normal</storagetype>
+        <name>disc_tier5</name>
+        <length>-1</length>
+        <precision>-1</precision>
+        <origin>price compute</origin>
+        <comments>disc_tier5</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_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>BigNumber</type>
+        <storagetype>normal</storagetype>
+        <name>price_tier1</name>
+        <length>-1</length>
+        <precision>-1</precision>
+        <origin>price compute</origin>
+        <comments>price_tier1</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_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>BigNumber</type>
+        <storagetype>normal</storagetype>
+        <name>price_tier2</name>
+        <length>-1</length>
+        <precision>-1</precision>
+        <origin>price compute</origin>
+        <comments>price_tier2</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_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>BigNumber</type>
+        <storagetype>normal</storagetype>
+        <name>price_tier3</name>
+        <length>-1</length>
+        <precision>-1</precision>
+        <origin>price compute</origin>
+        <comments>price_tier3</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_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>BigNumber</type>
+        <storagetype>normal</storagetype>
+        <name>price_tier4</name>
+        <length>-1</length>
+        <precision>-1</precision>
+        <origin>price compute</origin>
+        <comments>price_tier4</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_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>BigNumber</type>
+        <storagetype>normal</storagetype>
+        <name>price_tier5</name>
+        <length>-1</length>
+        <precision>-1</precision>
+        <origin>price compute</origin>
+        <comments>price_tier5</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>
@@ -1230,7 +1378,7 @@ order by product_id</sql>
       <schema_name/>
     </partitioning>
     <connection>erp indoteknik local</connection>
-    <sql>select * from product_pricelist_item where pricelist_id = 1 order by product_id</sql>
+    <sql>select * from product_pricelist_item where pricelist_id = 17022 order by product_id</sql>
     <limit>0</limit>
     <lookup/>
     <execute_each_row>N</execute_each_row>
-- 
cgit v1.2.3