Medications Upload specs

Data file

Upload steps & logic

Step 0- Prepare & clean original data

1. Copy table from original PDF file (http://www.moz.gov.ua/docfiles/dn_20170728_875_dod..pdf) and paste into Google tables file.
2. Del separate rows (between page, head of document, etc)
3. Add 0-column `Status`
4. Add 17- column `Мінімальна кратність відпуску` & fill column `0`
5. Replace symbol `,` on `.` into all amount & digital column (5,6,11-16)
6. Replace Cyrillic symbol `А`, `В`, `С` on Latin symbol `A`, `B`, `C` into column 7
7. Replace text `необмеженний`(etc) on `31.12.3000` into column 10
8. Del text symbols into all amount & digital column (5,6,11-16)
9. Create pivot table for check & repair `form` (see page `FORM`). Update problem rows
10. Export table to `*.csv` file
11. Del 2-s first rows in file (header of table)

Step 1- Load data into temp table on DB

1. Connect to DB

2. Create temp table

    Expand source

   -- drop TABLE _temp_reestr
   create table "_temp_reestr"
   (
   	status varchar(10),
   	id integer,
   	innm_name_original varchar(255),
   	brand_name varchar(255),
   	form varchar(255),
   	dosage_value numeric,
   	package_qty numeric,
   	atc varchar(255),
   	manufacturer varchar(255),
   	lic_num varchar(255),
   	lic_exp_at varchar(255),
   	amount_11 real,
   	amount_12 real,
   	amount_13 real,
   	amount_14 real,
   	amount_reimbursement real,
   	amount_16 real,
   	package_min_qty numeric,
   	country varchar(10),
   	manufacturer_jsonb varchar(255)
   )
   ;
   

3. Import data into `_temp_reestr` from csv-file 
     

4. Check count loaded rows
    

    Expand source

   select count(*) from _temp_reestr

Step 2- Load Innms

1. Create temp table 

    Expand source

   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 !!!):

   ="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 !!!):

   ="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

Step 4 - Prepare  Manufacturer JSONB

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

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

Step 5 - Load medications, innm_dosages

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. Run script create medical_programs
8. Run script create program_medications
9. Move data from `_temp_tables` to `_real_tables` (OR Generate `SQL Inserts` from `*._temp_tables`, then rename tables name from `_temp` → `origin_name`, run SQL Inserts scripts)

SQL-script file:  2Load_reestr_innms_medications.sql

SQL Insert script file (ONLY prepared values): SQL_INSERT_innms_medications_etc.sql