Page Comparison

Table of Contents

 

...

Upload steps & logic

Step 0- Prepare & clean original data

...

1. Create temp table 

   Code Block
   language sql collapse true
   create table _temp_innms ( id uuid not null constraint _temp_innms_pkey primary key, sctid varchar(255), name varchar(255) not null, name_original varchar(255) not null, is_active boolean default false not null, inserted_by uuid not null, updated_by uuid not null, inserted_at timestamp not null, updated_at timestamp not null )

   
   

2. Source page "innm" 
3. Prepare data (column `name` & `name_original`)

4. Prepare SQL-Insert statement on page (based on template)

   Example from page with formula (without CR\TAB\etc !!!):

   No Format
   ="insert into _temp_innms (id,sctid,name,name_original,is_active,inserted_by,inserted_at,updated_by,updated_at) values (uuid_generate_v4(), '','"&A1&"', '"&D1&"', TRUE ,'4261eacf-8008-4e62-899f-de1e2f7065f0', now(),'4261eacf-8008-4e62-899f-de1e2f7065f0',now());"

   Where:

             '4261eacf-8008-4e62-899f-de1e2f7065f0' - system user id
             A1 - name innm
             D1 - name original of innm
5. Insert rows from calculating SQL-insert statements

Step 3 - Load forms

1. Source page: "Forms"
2. Create pivot table, insert `key` for dictionary `MEDICATION_FORM` prepare SQL-Insert statement on page (based on template)

   Example from page with formula (without CR\TAB\etc !!!):

   No Format
   ="INSERT into _temp_form (name, name_key,dosage_num_unit,dosage_denum_unit, container_num_unit,container_denum_unit) values('"&C1&"','"&D1&"','"&E1&"','"&F1&"','"&G1&"','"&H1&"')"

   
   

3. Insert rows from calculating SQL-insert statements

...

1. Manually add new values into Dictionary: COUNTRY
2. Replace  symbol `"`, etc  on symbol `/"`  into column 8
3. Manually fill column 18 (`_Country`) according column 8

4. Prepare JSON object manufacturer based on formula

   No Format
   ="{""name"":"""&left(I7;find(",";I7)-1)&""", ""country"": """&S4&"""}"

   
   

...

1. Create temp table  for medications (with mandatory column `ext_id` !!!)

2. Run script load medications = brands

3. Run script load medications = innm_dosage

4. Create temp table for ingredients

5. Run script create ingredients for medications = brands

6. Run script create ingredients for medications = innm_dosages
7. Move data from `_temp_tables` to `_real_tables`

SQL-script file:  2Load_reestr_innms_medications.sql

...