Page Comparison

Table of Contents

 

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

...

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

...

Check count loaded rows
 

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
)

...

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:

...

Step 3 - Load forms

...

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

...

Step 4 - Prepare  Manufacturer JSONB

...

Prepare JSON object manufacturer based on formula

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

Step 5 - Prepare Dosage JSONB

1. Source page: "Original-2017-07-26"
2. Delete * from medications (delete old data)
3. Mapping:
   1. name = column `C`(3)

   2. is_active = TRUE

   3. start_at = `2017/07/26`
   4. end_at = `3000/01/01`
   5. route = column `D`(4) + search in Dictionary `medication_route` by name
   6. container_dosage =  Mapping procedure Container Dosage
   7. package_qty = column `G`(6)
   8. package_min_qty = column `G`(6)
   9. code_ATX = column `H`(7)
   10. manufacturer_name_country =column `I`(8) + Parsing procedure Manufacturer & Country
   11. certificate = column `J`(9)
   12. expired_certificate_date = column `K`(10) + format `DD.MM.YYYY`
4. Cascade load ingredients (see Step 5)

Step 500 - Load Ingredients

1. Source page: "Original-2017-07-26"
2. Delete * from ingredients where medications_id (delete old data)
3. Mapping:
   1. medication_id = parent
   2. innm_id = Search by innm.name
   3. is_active_substance = TRUE
   4.  dosage
      1. numerator_value = column `E`(5) + Mapping procedure Dosage

Mapping procedure Dosage

• numerator_unit = mapping by medications.route
  • аерозоль для інгаляцій = dose
  • Аерозоль для інгаляцій, дозований = dose 
  • інгаляція під тиском = dose
  • Порошок для інгаляцій = dose
  • Суспензія для розпилення = ml
  • таблетки = pill
  • таблетки з модифікованим вивільненням = pill
  • таблетки сублінгвальні = pill
  • таблетки, вкриті оболонкою = pill
  • таблетки, вкриті плівковою = pill
  • таблетки, вкриті плівковою оболонкою = pill
  • таблетки,вкриті оболонкою = pill
• numerator_value = column `E`(5)
• denumerator_unit mapping by numerator_unit
  • pill = pill
  • container = ml
  • aerosol = dose
• denumerator_value = `1`

Mapping procedure Container Dosage

• numerator_unit = mapping by medications.route
  • аерозоль для інгаляцій = aerosol
  • Аерозоль для інгаляцій, дозований = aerosol 
  • інгаляція під тиском = aerosol
  • Порошок для інгаляцій = aerosol
  • Суспензія для розпилення = container
  • таблетки = pill
  • таблетки з модифікованим вивільненням = pill
  • таблетки сублінгвальні = pill
  • таблетки, вкриті оболонкою = pill
  • таблетки, вкриті плівковою = pill
  • таблетки, вкриті плівковою оболонкою = pill
  • таблетки,вкриті оболонкою = pill
• numerator_value = `1`
• denumerator_unit mapping by numerator_unit
  • pill = pill
  • container = ml
  • aerosol = dose
• denumerator_value = `1`

Parsing procedure Manufacturer & Country

...

1. Юнікем Лабораторіз Лімітед , Індія;
2. Асіно Фарма АГ , Швейцарія

...

Table of Contents

 

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

   Code Block
   language sql collapse true
   -- 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 
   Image Added  Image Added

4. Check count loaded rows
    

   Code Block
   language sql collapse true
   select count(*) from _temp_reestr

   
   

Step 2- Load Innms

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

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

   No Format
   ="{""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