Medications Upload specs

Data file

File	2Load_innms,innm_dosages,brands
Pages	New-198-2017-07-26 Form Innms

Upload steps & logic

Step 0- Prepare & clean original data

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

Step 1- Load data into temp table on DB

Connect to DB

Create temp table

-- 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)
)
;

Import data into `_temp_reestr` from csv-file
Check count loaded rows
Expand source
```
select count(*) from _temp_reestr
```

Step 2- Load Innms

Create temp table

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
)

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

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

Insert rows from calculating SQL-insert statements

Step 3 - Load forms

Source page: "Forms"

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&"')"

Insert rows from calculating SQL-insert statements

Step 4 - Prepare Manufacturer JSONB

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

Prepare JSON object manufacturer based on formula

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

Step 5 - Load medications, innm_dosages

Create temp table for medications (with mandatory column `ext_id` !!!)
Run script load medications = brands
Run script load medications = innm_dosage
Create temp table for ingredients
Run script create ingredients for medications = brands
Run script create ingredients for medications = innm_dosages
Run script create medical_programs
Run script create program_medications
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)