u8RkkR8W

1--to do: 
2--make Gemscript as a NEW vocabulary with RxE adding 
3--remove the gemscript_dmd_map as nobody uses it
4--use Gemscript reference where it's possible
5--there's no need to run the real bug fix on gemscript before we updated dmd
6--add the Brand name taken ingredient match
7--
8drop sequence code_seq;
9DO $$
10DECLARE
11	ex INTEGER;
12BEGIN
13	SELECT MAX(replace(concept_code, 'OMOP','')::int4)+1 into ex FROM (
14		SELECT concept_code FROM concept WHERE concept_code like 'OMOP%'  and concept_code not like '% %' -- Last valid value of the OMOP123-type codes
15		/*UNION ALL
16		SELECT concept_code FROM drug_concept_stage where concept_code like 'OMOP%' and concept_code not like '% %' -- Last valid value of the OMOP123-type codes*/
17	) AS s0;
18	DROP SEQUENCE IF EXISTS code_seq;
19	EXECUTE 'CREATE SEQUENCE code_seq INCREMENT BY 1 START WITH ' || ex || ' NO CYCLE CACHE 20';
20END;
21
22$$;
23
24
25--1 Update latest_update field to new date 
26DO $_$
27BEGIN
28	PERFORM VOCABULARY_PACK.SetLatestUpdate(
29	pVocabularyName			=> 'Gemscript',
30	pVocabularyDate			=> to_date ('01052018','ddmmyyyy') ,-- pick the latest date between CPRD and THIN files
31	pVocabularyVersion		=> 'Gemscript '||to_date ('01052018','ddmmyyyy'),  
32	pVocabularyDevSchema	=> 'DEV_GEMSCRIPT'
33);
34END; $_$;
35
36DROP TABLE IF EXISTS rel_to_conc_old;
37CREATE TABLE rel_to_conc_old AS
38SELECT c.concept_id AS concept_id_1,
39	'Source - RxNorm eq'::varchar AS relationship_id,
40	concept_id_2
41FROM (
42	SELECT *
43	FROM dev_dpd.relationship_to_concept
44	WHERE precedence = 1
45	
46	UNION
47	
48	SELECT *
49	FROM dev_aus.relationship_to_concept
50	WHERE precedence = 1
51	) a
52JOIN concept c ON c.concept_code = a.concept_code_1
53	AND c.vocabulary_id = a.vocabulary_id_1
54	AND c.invalid_reason IS NULL;
55
56--add Gemscript concept set,
57--take concepts from additional tables
58--reference table from CPRD - goes first - NovoNordisk Need
59TRUNCATE TABLE concept_stage;
60INSERT INTO concept_stage
61SELECT NULL AS concept_id,
62	productname AS concept_name,
63	'Drug' AS domain_id,
64	'Gemscript' AS vocabulary_id,
65	'Gemscript' AS concept_class_id,
66	NULL AS standard_concept,
67	gemscriptcode AS concept_code,
68	(
69		SELECT latest_update
70		FROM vocabulary
71		WHERE vocabulary_id = 'Gemscript'
72		) AS valid_start_date,
73	to_date('20991231', 'yyyymmdd') AS valid_end_date,
74	NULL AS invalid_reason
75FROM gemscript_reference
76;
77
78--mappings from Urvi
79INSERT INTO concept_stage
80SELECT NULL AS concept_id,
81	BRAND AS concept_name,
82	'Drug' AS domain_id,
83	'Gemscript' AS vocabulary_id,
84	'Gemscript' AS concept_class_id,
85	NULL AS standard_concept,
86	gemscript_drugcode AS concept_code,
87	(
88		SELECT latest_update
89		FROM vocabulary
90		WHERE vocabulary_id = 'Gemscript'
91		) AS valid_start_date,
92	to_date('20991231', 'yyyymmdd') AS valid_end_date,
93	NULL AS invalid_reason
94FROM thin_gemsc_dmd
95WHERE gemscript_drugcode NOT IN (
96		SELECT concept_code
97		FROM concept_stage
98		);
99		
100--dm+d variant is not a real Gemscript name they said
101
102INSERT INTO concept_stage
103SELECT NULL AS concept_id,
104	dmd_drug_name AS concept_name,
105	'Drug' AS domain_id,
106	'Gemscript' AS vocabulary_id,
107	'Gemscript' AS concept_class_id,
108	NULL AS standard_concept,
109	gemscript_drug_code AS concept_code,
110	(
111		SELECT latest_update
112		FROM vocabulary
113		WHERE vocabulary_id = 'Gemscript'
114		) AS valid_start_date,
115	to_date('20991231', 'yyyymmdd') AS valid_end_date,
116	NULL AS invalid_reason
117FROM gemscript_dmd_map
118WHERE gemscript_drug_code NOT IN (
119		SELECT concept_code
120		FROM concept_stage
121		)
122
123;--table we had before, only God knows how we got this table
124
125--Gemscript THIN concepts 
126INSERT INTO concept_stage
127SELECT NULL AS concept_id,
128	GENERIC AS concept_name,
129	'Drug' AS domain_id,
130	'Gemscript' AS vocabulary_id,
131	'Gemscript THIN' AS concept_class_id,
132	NULL AS standard_concept,
133	encrypted_drugcode AS concept_code,
134	(
135		SELECT latest_update
136		FROM vocabulary
137		WHERE vocabulary_id = 'Gemscript'
138		) AS valid_start_date, -- TRUNC(SYSDATE)
139	to_date('20991231', 'yyyymmdd') AS valid_end_date,
140	NULL AS invalid_reason
141FROM thin_gemsc_dmd;
142
143--!codes remain as they are
144
145--build concept_relationship_stage table
146TRUNCATE TABLE concept_relationship_stage;
147
148--Gemscript to dm+d
149--new table from URVI
150INSERT INTO concept_relationship_stage
151SELECT NULL AS concept_id_1,
152	NULL AS concept_id_2,
153	gemscript_drugcode AS concept_code_1,
154	dmd_code AS concept_code_2,
155	'Gemscript' AS vocabulary_id_1,
156	'dm+d' AS vocabulary_id_2,
157	'Maps to' AS relationship_id,
158	(
159		SELECT latest_update
160		FROM vocabulary
161		WHERE vocabulary_id = 'Gemscript'
162		) AS valid_start_date,
163	to_date('20991231', 'yyyymmdd') AS valid_end_date,
164	NULL AS invalid_reason
165FROM thin_gemsc_dmd;
166
167--old table from Christian
168INSERT INTO concept_relationship_stage
169SELECT NULL AS concept_id_1,
170	NULL AS concept_id_2,
171	gemscript_drug_code AS concept_code_1,
172	dmd_code AS concept_code_2,
173	'Gemscript' AS vocabulary_id_1,
174	'dm+d' AS vocabulary_id_2,
175	'Maps to' AS relationship_id,
176	(
177		SELECT latest_update
178		FROM vocabulary
179		WHERE vocabulary_id = 'Gemscript'
180		) AS valid_start_date,
181	to_date('20991231', 'yyyymmdd') AS valid_end_date,
182	NULL AS invalid_reason
183FROM gemscript_dmd_map
184WHERE gemscript_drug_code NOT IN (
185		SELECT concept_code_1
186		FROM concept_relationship_stage
187		);
188
189--mappings between THIN gemscript and Gemscript
190INSERT INTO concept_relationship_stage
191SELECT NULL AS concept_id_1,
192	NULL AS concept_id_2,
193	encrypted_drugcode AS concept_code_1,
194	gemscript_drugcode AS concept_code_2,
195	'Gemscript' AS vocabulary_id_1,
196	'Gemscript' AS vocabulary_id_2,
197	'Maps to' AS relationship_id,
198	(
199		SELECT latest_update
200		FROM vocabulary
201		WHERE vocabulary_id = 'Gemscript'
202		) AS valid_start_date,
203	to_date('20991231', 'yyyymmdd') AS valid_end_date,
204	NULL AS invalid_reason
205FROM thin_gemsc_dmd;
206
207--delete mappings to non-existing dm+ds because thei ruin further procedures result
208--it allows to exist old mappings , they are relatively good but not very precise actually, and we know that if there was exising dm+d concept it'll go to better dm+d RxE way, and actually gives us for about 4000 relationships, 
209-- so if we have time we can remap these concepts to RxE, give to medical coder to review them
210--but for now let's remain them
211DELETE
212FROM concept_relationship_stage
213WHERE vocabulary_id_2 = 'dm+d'
214	AND concept_code_2 NOT IN (
215		SELECT concept_code
216		FROM concept
217		WHERE vocabulary_id = 'dm+d'
218		);
219;
220--match using dm+d VMP's names is extrememly common
221--Preserve AMP mappings first
222INSERT INTO concept_relationship_stage
223SELECT DISTINCT NULL::INT4 AS concept_id_1,
224       NULL::INT4 AS concept_id_2,
225       gemscript_drugcode AS concept_code_1,
226       c.concept_code AS concept_code_2,
227       'Gemscript' AS vocabulary_id_1,
228       'dm+d' AS vocabulary_id_2,
229       'Maps to' AS relationship_id,
230       (SELECT latest_update
231        FROM vocabulary
232        WHERE vocabulary_id = 'Gemscript') AS valid_start_date,
233       TO_DATE('20991231','yyyymmdd') AS valid_end_date,
234       NULL AS invalid_reason
235FROM thin_gemsc_dmd t
236  JOIN concept c
237    ON REPLACE (lower (t.brand),' ','') = REPLACE (lower (c.concept_name),' ','')
238   AND c.vocabulary_id = 'dm+d'
239   AND c.concept_class_id = 'AMP'
240WHERE t.gemscript_drugcode NOT IN (SELECT concept_code_1 FROM concept_relationship_stage);
241
242--now VMPs from thin_gemsc_dmd
243INSERT INTO concept_relationship_stage
244SELECT DISTINCT NULL::INT4 AS concept_id_1,
245       NULL::INT4 AS concept_id_2,
246       encrypted_drugcode AS concept_code_1,
247       c.concept_code AS concept_code_2,
248       'Gemscript' AS vocabulary_id_1,
249       'dm+d' AS vocabulary_id_2,
250       'Maps to' AS relationship_id,
251       (SELECT latest_update
252        FROM vocabulary
253        WHERE vocabulary_id = 'Gemscript') AS valid_start_date,
254       TO_DATE('20991231','yyyymmdd') AS valid_end_date,
255       NULL AS invalid_reason
256FROM thin_gemsc_dmd t
257  JOIN concept c
258    ON REPLACE (lower (t.generic),' ','') = REPLACE (lower (c.concept_name),' ','')
259   AND c.vocabulary_id = 'dm+d'
260   AND c.concept_class_id = 'VMP'
261   AND REPLACE (lower (t.brand),' ','') = REPLACE (lower (t.generic),' ','')
262WHERE t.encrypted_drugcode NOT IN (SELECT concept_code_1 FROM concept_relationship_stage);
263
264--both for THIN and Gemscript
265INSERT INTO concept_relationship_stage
266SELECT DISTINCT NULL::INT4 AS concept_id_1,
267       NULL::INT4 AS concept_id_2,
268       gemscript_drugcode AS concept_code_1,
269       c.concept_code AS concept_code_2,
270       'Gemscript' AS vocabulary_id_1,
271       'dm+d' AS vocabulary_id_2,
272       'Maps to' AS relationship_id,
273       (SELECT latest_update
274        FROM vocabulary
275        WHERE vocabulary_id = 'Gemscript') AS valid_start_date,
276       TO_DATE('20991231','yyyymmdd') AS valid_end_date,
277       NULL AS invalid_reason
278FROM thin_gemsc_dmd t
279  JOIN concept c
280    ON REPLACE (lower (t.generic),' ','') = REPLACE (lower (c.concept_name),' ','')
281   AND c.vocabulary_id = 'dm+d'
282   AND c.concept_class_id = 'VMP'
283   AND REPLACE (lower (t.brand),' ','') = REPLACE (lower (t.generic),' ','')
284WHERE t.gemscript_drugcode NOT IN (SELECT concept_code_1 FROM concept_relationship_stage);
285
286--also from gemscript_reference
287INSERT INTO concept_relationship_stage
288SELECT DISTINCT NULL::INT4 AS concept_id_1,
289       NULL::INT4 AS concept_id_2,
290       gemscriptcode AS concept_code_1,
291       c.concept_code AS concept_code_2,
292       'Gemscript' AS vocabulary_id_1,
293       'dm+d' AS vocabulary_id_2,
294       'Maps to' AS relationship_id,
295       (SELECT latest_update
296        FROM vocabulary
297        WHERE vocabulary_id = 'Gemscript') AS valid_start_date,
298       TO_DATE('20991231','yyyymmdd') AS valid_end_date,
299       NULL AS invalid_reason
300FROM gemscript_reference t
301  JOIN concept c
302    ON REPLACE (lower (t.productname),' ','') = REPLACE (lower (c.concept_name),' ','')
303   AND c.vocabulary_id = 'dm+d'
304   AND c.concept_class_id = 'VMP'
305WHERE t.gemscriptcode NOT IN (SELECT concept_code_1 FROM concept_relationship_stage);
306
307
308;
309ANALYZE concept_stage;
310ANALYZE concept_relationship_stage;
311
312-- Working with replacement mappings
313DO $_$
314BEGIN
315	PERFORM VOCABULARY_PACK.CheckReplacementMappings();
316end; $_$;
317
318
319-- Deprecate 'Maps to' mappings to deprecated and upgraded concepts
320DO $_$
321BEGIN
322	PERFORM VOCABULARY_PACK.DeprecateWrongMAPSTO();
323end; $_$;
324
325
326-- Add mapping from deprecated to fresh concepts, and also from non-standard to standard concepts
327DO $_$
328BEGIN
329	PERFORM VOCABULARY_PACK.AddFreshMAPSTO();
330end; $_$;
331
332
333-- Delete ambiguous 'Maps to' mappings
334DO $_$
335BEGIN
336	PERFORM VOCABULARY_PACK.DeleteAmbiguousMAPSTO();
337end; $_$;
338
339
340--deprecate relationship mappings to Non-standard concepts
341--how's this possible?
342UPDATE concept_relationship_stage
343SET invalid_reason = 'D',
344	valid_end_date = (
345		SELECT latest_update - 1
346		FROM vocabulary
347		WHERE vocabulary_id = 'Gemscript'
348		)
349WHERE (
350		concept_code_1,
351		concept_code_2,
352		vocabulary_id_2
353		) NOT IN (
354		SELECT concept_code_1,
355			concept_code_2,
356			vocabulary_id_2
357		FROM concept_relationship_stage
358		JOIN concept ON concept_code = concept_code_2
359			AND vocabulary_id = vocabulary_id_2
360			AND standard_concept = 'S'
361		);
362
363--define drug domain (Drug set by default) based on target concept domain
364UPDATE concept_stage cs
365SET domain_id = (
366		SELECT domain_id
367		FROM (
368			SELECT DISTINCT --beware of multiple mappings
369				r.concept_code_1,
370				r.vocabulary_id_1,
371				c.domain_id
372			FROM concept_relationship_stage r -- concept_code_1 = s1.concept_code and vocabulary_id_1 = vocabulary_id
373			JOIN concept c ON c.concept_code = r.concept_code_2
374				AND r.vocabulary_id_2 = c.vocabulary_id
375				AND r.invalid_reason IS NULL -- and r.vocabulary_id_2 in ('dm+d', 'RxNorm', 'RxNorm Extension')
376			JOIN (
377				SELECT concept_code_1
378				FROM (
379					SELECT DISTINCT --beware of multiple mappings
380						r.concept_code_1,
381						r.vocabulary_id_1,
382						c.domain_id
383					FROM concept_relationship_stage r -- concept_code_1 = s1.concept_code and vocabulary_id_1 = vocabulary_id
384					JOIN concept c ON c.concept_code = r.concept_code_2
385						AND r.vocabulary_id_2 = c.vocabulary_id
386						AND r.invalid_reason IS NULL -- and r.vocabulary_id_2 in ('dm+d', 'RxNorm', 'RxNorm Extension')
387					) AS s0
388				GROUP BY concept_code_1
389				HAVING count(*) = 1
390				) zz --exclude those mapped to several domains such as Inert ingredient is a device (wrong BTW), cartridge is a device, etc.
391				ON zz.concept_code_1 = r.concept_code_1
392			) rr
393		WHERE rr.concept_code_1 = cs.concept_code
394			AND rr.vocabulary_id_1 = cs.vocabulary_id
395		);
396
397--not covered are Drugs for now
398UPDATE concept_stage
399SET domain_id = 'Drug'
400WHERE domain_id IS NULL;
401
402--select distinct domain_id from concept_stage;
403--create table gemscript_reference as select * from gemscript_reference;
404
405
406--why in this way????
407--for development purpose use temporary thin_need_to_map table:  
408DROP TABLE IF EXISTS thin_need_to_map;  --18457 the old version, 13965 --new version (join concept), well, really a big difference. not sure if those existing mappings are correct, 13877 - concept_relationship_stage version, why?
409CREATE TABLE thin_need_to_map AS
410SELECT --c.*
411	t.ENCRYPTED_DRUGCODE AS THIN_code,
412	t.GENERIC::varchar(255) AS THIN_name,
413	coalesce(gr.GEMSCRIPTCODE, t.GEMSCRIPT_DRUGCODE) AS GEMSCRIPT_code,
414	coalesce(gr.PRODUCTNAME, t.BRAND)::varchar(255) AS GEMSCRIPT_name,
415	c.domain_id
416FROM thin_gemsc_dmd t
417FULL OUTER JOIN gemscript_reference gr ON gr.GEMSCRIPTCODE = t.GEMSCRIPT_DRUGCODE
418LEFT JOIN concept_relationship_stage r ON coalesce(gr.GEMSCRIPTCODE, t.GEMSCRIPT_DRUGCODE) = r.concept_code_1
419	AND r.invalid_reason IS NULL --and r.vocabulary_id_2 in ('dm+d', 'RxNorm', 'RxNorm Extension') and relationship_id = 'Maps to' 
420JOIN concept_stage c -- join and left join gives us different results because of   !1360102 AND   !5264101 codes, so exclude those !!-CODES
421	ON coalesce(gr.GEMSCRIPTCODE, t.GEMSCRIPT_DRUGCODE) = c.concept_code
422	AND c.concept_class_id = 'Gemscript'
423WHERE r.concept_code_2 IS NULL;
424
425CREATE INDEX th_th_n_ix ON thin_need_to_map (lower (thin_name));
426CREATE INDEX th_ge_n_ix ON thin_need_to_map (lower (gemscript_name));
427
428UPDATE thin_need_to_map
429SET thin_name = replace(thin_name, 'polymixin b ', 'polymyxin b');
430
431UPDATE thin_need_to_map
432SET thin_name = replace(thin_name, 'ipecacuhana', 'ipecacuanha');
433
434UPDATE thin_need_to_map
435SET thin_name = replace(thin_name, 'chloesterol', 'cholesterol');
436
437UPDATE thin_need_to_map
438SET thin_name = replace(thin_name, 'capsicin', 'capsaicin');
439
440UPDATE thin_need_to_map
441SET thin_name = replace(thin_name, 'glycolsalicylate', 'glycol salicylate');
442
443UPDATE thin_need_to_map
444SET thin_name = replace(thin_name, 'azatidine', 'azacytidine');
445
446UPDATE thin_need_to_map
447SET thin_name = replace(thin_name, 'benzalkonium, chlorhexidine', 'benzalkonium / chlorhexidine');
448
449--define domain_id
450--DRUGSUBSTANCE is null and lower
451--!!! OK for gemscript part
452UPDATE thin_need_to_map n
453SET domain_id = 'Device'
454WHERE EXISTS (
455		SELECT 1
456		FROM gemscript_reference g
457		WHERE (
458				SELECT count(*)
459				FROM regexp_matches(PRODUCTNAME, '[a-z]', 'g')
460				) > 5 -- sometime we have these non HCl, mg as a part of UPPER case concept_name
461			AND (
462				DRUGSUBSTANCE IS NULL
463				OR DRUGSUBSTANCE = 'Syringe For Injection'
464				)
465			AND g.GEMSCRIPTCODE = n.GEMSCRIPT_CODE
466		);
467--4758
468
469--device by the name (taken from dmd?) part 1
470--ok
471UPDATE thin_need_to_map
472SET domain_id = 'Device'
473WHERE GEMSCRIPT_CODE IN (
474		SELECT GEMSCRIPT_CODE
475		FROM thin_need_to_map
476		WHERE THIN_name ~* 'stoma caps|urinal systems|shampoo|sunscreen|amidotrizoate|dialysis|smoflipid|camino|maxamum|sno-pro|lubri|peptamen|pepti-junior|dressing|diagnostic|glove|supplement| rope|weight|resource|accu-chek|accutrend|procal|glytactin|gauze|keyomega|cystine|docomega|anamixcranberry|pedialyte|hydralyte|hcu cooler|pouch'
477		
478		UNION ALL
479		
480		SELECT GEMSCRIPT_CODE
481		FROM thin_need_to_map
482		WHERE THIN_name ~* 'burger|biscuits|stocking|strip|remover|chamber|gauze|supply|beverage|cleanser|soup|protector|nutrision|repellent|wipes|kilocalories|cake|roll|adhesive|milk|dessert|medium chain|prozero|amino acid supplement|long chain|low protein|pouches|ribbon|cannula|swabs|bandage|cylinder'
483		
484		UNION ALL
485		
486		SELECT GEMSCRIPT_CODE
487		FROM thin_need_to_map
488		WHERE gemscript_name ~* 'amidotrizoate|burger|biscuits|stocking|strip|remover|chamber|gauze|supply|beverage|cleanser|soup|protector|nutrision|repellent|wipes|kilocalories|cake|roll|adhesive|milk|dessert|medium chain|prozero|amino acid supplement|long chain|low protein|pouches|ribbon|cannula|swabs|bandage|cylinder'
489		
490		UNION ALL
491		
492		SELECT GEMSCRIPT_CODE
493		FROM thin_need_to_map
494		WHERE gemscript_name ~* 'dialysis|smoflipid|camino|maxamum|sno-pro|lubri|peptamen|pepti-junior|dressing|diagnostic|glove|supplement| rope|weight|resource|accu-chek|accutrend|procal|glytactin|gauze|keyomega|cystine|docomega|anamixcranberry|pedialyte|hydralyte|hcu cooler|pouch'
495		)
496	AND domain_id = 'Drug';
497
498
499--device by the name (taken from dmd?) part 2
500--ok
501UPDATE thin_need_to_map
502SET domain_id = 'Device'
503--put these into script above
504WHERE gemscript_code IN (
505		SELECT gemscript_code
506		FROM thin_need_to_map
507		WHERE THIN_name ~* 'breath test|pizza|physical|diet food|sunscreen|tubing|nutrison|elasticated vest|oxygen|spaghetti|irrigation |sunscreen cream|sheaths|lancet| wash|contact lens|bag|gluten|plast|wax|catheter|device|needle|needle|emollient|feeding|colostomy| toe |rubber|flange|cotton|stockinette|urostomy|tube |ostomy|cracker|shield|larve|belt|pasta|garments|bread'
508			OR gemscript_name ~* 'breath test|pizza|physical|diet food|sunscreen|tubing|nutrison|elasticated vest|oxygen|spaghetti|irrigation |sunscreen cream|sheaths|lancet| wash|contact lens|bag|gluten|plast|wax|catheter|device|needle|needle|emollient|feeding|colostomy| toe |rubber|flange|cotton|stockinette|urostomy|tube |ostomy|cracker|shield|larve|belt|pasta|garments|bread'
509		)
510	AND domain_id = 'Drug';
511
512--these concepts are drugs anyway
513--put this condition into concept above!!! 
514--ok
515UPDATE thin_need_to_map n
516SET domain_id = 'Drug'
517WHERE EXISTS (
518		SELECT 1
519		FROM gemscript_reference g
520		WHERE g.GEMSCRIPTCODE = n.GEMSCRIPT_CODE
521			AND n.domain_id = 'Device'
522			AND lower(formulation) IN (
523				'capsule',
524				'chewable tablet',
525				--'cream',
526				'cutaneous solution',
527				'ear drops',
528				'ear/eye drops solution',
529				'emollient',
530				'emulsion',
531				'emulsion for infusion',
532				'enema',
533				'eye drops',
534				'eye ointment',
535				--'gel',
536				'granules',
537				'homeopathic drops',
538				'homeopathic pillule',
539				'homeopathic tablet',
540				'inhalation powder',
541				'injection',
542				'injection solution',
543				'lotion',
544				'ointment',
545				'oral gel',
546				'oral solution',
547				'oral suspension',
548				--'plasters',
549				'powder',
550				'sachets',
551				'solution for injection',
552				'suppository',
553				'tablet',
554				'infusion',
555				'solution',
556				'Suspension for injection',
557				'Spansule',
558				'lozenge',
559				'cream',
560				'Intravenous Infusion'
561				)
562		);
563
564--make standard representation of multicomponent drugs
565UPDATE thin_need_to_map
566SET THIN_NAME = replace(THIN_NAME, '%/', '% / ')
567WHERE thin_name ~ '%/'
568	AND domain_id = 'Drug';
569
570UPDATE thin_need_to_map
571SET THIN_NAME = regexp_replace(thin_name, '( with )(\D)', ' / \2', 'g')
572WHERE thin_name LIKE '% with %'
573	AND domain_id = 'Drug';
574
575UPDATE thin_need_to_map
576SET THIN_NAME = regexp_replace(thin_name, '( with )(\d)', '+\2', 'g')
577WHERE thin_name LIKE '% with %'
578	AND domain_id = 'Drug';
579
580UPDATE thin_need_to_map
581SET THIN_NAME = replace(THIN_NAME, ' & ', ' / ')
582WHERE thin_name LIKE '% & %'
583	AND NOT thin_name ~ ' & \d'
584	AND domain_id = 'Drug';
585
586UPDATE thin_need_to_map
587SET THIN_NAME = replace(THIN_NAME, ' and ', ' / ')
588WHERE thin_name LIKE '% and %'
589	AND NOT thin_name ~ ' and \d'
590	AND domain_id = 'Drug';
591
592UPDATE thin_need_to_map
593SET gemscript_name = replace(gemscript_name, '%/', '% / ')
594WHERE gemscript_name ~ '%/'
595	AND domain_id = 'Drug';
596
597UPDATE thin_need_to_map
598SET gemscript_name = regexp_replace(gemscript_name, '( with )(\D)', ' / \2', 'g')
599WHERE gemscript_name LIKE '% with %'
600	AND domain_id = 'Drug';
601
602UPDATE thin_need_to_map
603SET THIN_NAME = regexp_replace(gemscript_name, '( with )(\d)', '+\2', 'g')
604WHERE gemscript_name LIKE '% with %'
605	AND domain_id = 'Drug';
606
607UPDATE thin_need_to_map
608SET gemscript_name = replace(gemscript_name, ' & ', ' / ')
609WHERE gemscript_name LIKE '% & %'
610	AND NOT gemscript_name ~ ' & \d'
611	AND domain_id = 'Drug';
612
613UPDATE thin_need_to_map
614SET gemscript_name = replace(gemscript_name, ' and ', ' / ')
615WHERE gemscript_name LIKE '% and %'
616	AND NOT gemscript_name ~ ' and \d'
617	AND domain_id = 'Drug';
618
619UPDATE thin_need_to_map
620SET THIN_NAME = replace(THIN_NAME, 'i.u.', 'iu')
621WHERE thin_name LIKE '%i.u.%';
622
623UPDATE thin_need_to_map
624SET gemscript_name = replace(gemscript_name, 'i.u.', 'iu')
625WHERE gemscript_name LIKE '%i.u.%';
626
627--define what's a pack based on the concept_name, then manually parse this out, then add pack_component names as a codes (check the code replacing script) and add pack_components as a drug components in ds_stage creation algorithms
628DROP TABLE IF EXISTS packs_out;
629CREATE TABLE packs_out AS
630SELECT THIN_NAME,
631	GEMSCRIPT_CODE,
632	GEMSCRIPT_NAME,
633	NULL::VARCHAR(250) AS pack_component,
634	NULL::FLOAT AS amount
635FROM thin_need_to_map t
636WHERE t.domain_id = 'Drug'
637	AND gemscript_name NOT LIKE 'Becloforte%'
638	AND (
639		gemscript_name LIKE '% pack%'
640		OR gemscript_code IN
641		--packs defined manually
642		(
643			'67678021',
644			'76122020',
645			'80033020',
646			'1637007'
647			)
648		OR thin_name ~ '(\d\s*x\s*\d)|(estradiol.*\+)'
649		OR (
650			SELECT count(*)
651			FROM regexp_matches(thin_name, 'tablet| cream|capsule', 'g')
652			) > 1
653		);
654
655DROP TABLE IF EXISTS pc_stage;
656
657CREATE TABLE pc_stage 
658(
659  pack_concept_code   VARCHAR(550),
660  drug_concept_code   VARCHAR(550),
661  amount              INT,
662  box_size            INT
663);
664
665WbImport -file=/home/ekorchmar/Documents/packs_in.csv
666         -type=text
667         -table=packs_in
668         -encoding="UTF-8"
669         -header=true
670         -decode=false
671         -dateFormat="yyyy-MM-dd"
672         -timestampFormat="yyyy-MM-dd HH:mm:ss"
673         -delimiter='\t'
674         -quotechar='"'
675         -decimal=.
676         -fileColumns=thin_name,gemscript_code,gemscript_name,pack_component,amount
677         -quoteCharEscaping=NONE
678         -ignoreIdentityColumns=false
679         -deleteTarget=true
680         -continueOnError=false
681         -batchSize=1000;
682
683INSERT INTO pc_stage
684(
685  pack_concept_code,
686  drug_concept_code,
687  amount,
688  box_size
689)
690SELECT gemscript_code,
691       pack_component,
692       amount,
693       NULL
694FROM packs_in;
695--as we use not real concept_codes, let's make the longer fields for the codes
696--why I'm not using gemscript_reference table??
697 alter table thin_need_to_map alter column gemscript_code type varchar (250)
698 ;
699 alter table thin_need_to_map alter column thin_code type varchar (250)
700 ;
701
702INSERT INTO thin_need_to_map (
703	THIN_CODE,
704	THIN_NAME,
705	GEMSCRIPT_CODE,
706	GEMSCRIPT_NAME,
707	DOMAIN_ID
708	)
709SELECT NULL,
710	DRUG_CONCEPT_CODE,
711	'OMOP' || nextval('code_seq'),
712	DRUG_CONCEPT_CODE,
713	'Drug'
714FROM (select distinct DRUG_CONCEPT_CODE from pc_stage) s0;
715;
716DROP TABLE IF EXISTS thin_comp;
717CREATE TABLE thin_comp AS
718SELECT substring(lower(a.drug_comp), '(((\d)*[.,]*\d+)(\s)*(mg|%|ml|mcg|hr|hours|unit(s?)|iu|g|microgram(s*)|u|mmol|c|gm|litre|million unit(s?)|nanogram(s)*|x|ppm|million units| Kallikrein inactivator units|kBq|microlitres|MBq|molar|micromol)(/((\d)*[.,]*\d+)*(\s*)(g|dose|ml|mg|ampoule|litre|hour(s)*|h|square cm|microlitres|unit dose|drop))*)') AS dosage,
719	REPLACE(trim(substring(lower(thin_name), '((\s|\()[[:digit:]\.]+(\s*)(litre(s?)|ml))')), '(', '') AS volume,
720	A.*
721FROM (
722	SELECT DISTINCT unnest(string_to_array(t.thin_name, ' / ')) AS drug_comp,
723		t.*
724	FROM thin_need_to_map t
725	) a
726WHERE a.domain_id = 'Drug'
727	--exclusions
728	--Bendroflumethiazide / potassium 2.5mg+7.7mmol modified release tablets 
729	AND NOT thin_name ~* '[[:digit:]\,\.]+.*\+(\s*)[[:digit:]\,\.].*'
730--Co-triamterzide 50mg/25mg tablets
731--and not regexp_like (thin_name, '\dm(c*)g/[[:digit:]\,\.]+m(c*)g')
732
733UNION
734
735--Bendroflumethiazide / potassium 2.5mg+7.7mmol modified release tablets 
736SELECT CONCAT (
737		trim(l.dosage),
738		denom
739		) AS dosage,
740	volume,
741	trim(l.drug_comp) AS drug_comp,
742	thin_code,
743	thin_name,
744	gemscript_code,
745	gemscript_name,
746	domain_id
747FROM (
748	SELECT substring(lower(thin_name), '(((\d)*[.,]*\d+)(\s)*(g|mg|%|mcg|iu|mmol|micrograms)(\s)*\+(\s)*[[:digit:]\,\.]+(g|mg|%|mcg|iu|mmol|micrograms|ku)((\s)*\+(\s)*((\d)*[.,]*\d+)*(\s)*(g|mg|%|mcg|iu|mmol|micrograms))*)') AS dosage_0,
749		substring(lower(THIN_NAME), '(/[[:digit:]\,\.]+(ml| hr|g|mg))') AS denom,
750		REPLACE(trim(substring(thin_name, '((\s|\()[[:digit:]\.]+(\s*)(litre(s?)|ml))')), '(', '') AS volume,
751		t.*
752	FROM thin_need_to_map t
753	WHERE thin_name ~* '((\d)*[.,]*\d+)(\s)*(g|mg|%|mcg|iu|mmol|micrograms)(\s)*\+(\s)*[[:digit:]\,\.]+(g|mg|%|mcg|iu|mmol|micrograms|ku)((\s)*\+(\s)*((\d)*[.,]*\d+)*(\s)*(g|mg|%|mcg|iu|mmol|micrograms))*'
754		AND domain_id = 'Drug'
755	) t,
756	LATERAL(SELECT * FROM unnest(string_to_array(t.thin_name, ' / '), string_to_array(dosage_0, '+')) AS a(drug_comp, dosage)) l;
757
758--/ampoule is treated as denominator then
759UPDATE thin_comp
760SET dosage = replace(dosage, '/ampoule', '')
761WHERE dosage LIKE '%/ampoule';
762
763--',c is treated as dosage
764UPDATE thin_comp
765SET dosage = NULL
766WHERE dosage LIKE '\,%';
767
768--select * from thin_comp;
769
770CREATE INDEX drug_comp_ix ON thin_comp USING GIN (drug_comp devv5.gin_trgm_ops);
771CREATE INDEX drug_comp_ix2 ON thin_comp (lower (drug_comp));
772ANALYZE thin_comp;
773
774--how to define Ingredient, change scripts to COMPONENTS and use only (  lower (a.thin_name) like lower (b.concept_name)||' %' tomorrow!!!
775--take the longest ingredient, if this works, rough dm+d is better, becuase it has Sodium bla-bla-nate and RxNorm has just bla-bla-nate 
776--don't need to have two parts here
777--Execution time: 57.41s
778--Execution time: 1m 41s when more vocabularies added 
779
780DROP TABLE IF EXISTS i_map;
781CREATE TABLE i_map AS -- enhanced algorithm added  lower (a.thin_name) like lower '% '||(b.concept_name)||' %'
782SELECT *
783FROM (
784	SELECT DISTINCT i.dosage,
785		i.thin_name,
786		i.thin_code,
787		i.drug_comp,
788		i.gemscript_code,
789		i.gemscript_name,
790		i.volume,
791		i.concept_id,
792		i.concept_name,
793		i.vocabulary_id,
794		RANK() OVER (
795			PARTITION BY i.drug_comp ORDER BY LENGTH(i.concept_name) DESC,
796				i.vocabulary_id DESC,
797				i.concept_id
798			) AS rank1
799	FROM (
800		SELECT DISTINCT a.*,
801			rx.concept_id,
802			rx.concept_name,
803			rx.vocabulary_id
804		FROM thin_comp a
805		JOIN concept_synonym s ON (
806				a.drug_comp ILIKE s.concept_synonym_name || ' %'
807				OR LOWER(a.drug_comp) = LOWER(s.concept_synonym_name)
808				)
809		JOIN concept_relationship r ON s.concept_id = r.concept_id_1
810			AND r.invalid_reason IS NULL
811		JOIN concept rx ON r.concept_id_2 = rx.concept_id
812			AND rx.vocabulary_id LIKE 'Rx%'
813			AND rx.concept_class_id = 'Ingredient'
814			AND rx.invalid_reason IS NULL
815		) i
816	) AS s0
817--take the longest ingredient
818WHERE rank1 = 1;
819
820--map Ingredients derived from different vocabularies to RxNorm(E)
821DROP TABLE IF EXISTS rel_to_ing_1;
822CREATE TABLE rel_to_ing_1 AS
823SELECT DISTINCT i.dosage,
824	i.drug_comp,
825	i.thin_code,
826	i.thin_name,
827	i.gemscript_code,
828	i.gemscript_name,
829	i.volume,
830	concept_id AS target_id,
831	concept_name AS target_name,
832	vocabulary_id AS target_vocab
833FROM i_map i;
834
835--the same but with gemscript_name
836--make standard representation of multicomponent drugs
837--select count(*) from thin_comp2 ; select * from thin_comp where thin_code = '97245997'; select * from rel_to_ing_1 where thin_code is null;
838
839DROP TABLE IF EXISTS thin_comp2; 
840CREATE TABLE thin_comp2 AS
841
842SELECT substring(lower(a.drug_comp), '(((\d)*[.,]*\d+)(\s)*(mg|%|ml|mcg|hr|hours|unit(s?)|iu|g|microgram(s*)|u|mmol|c|gm|litre|million unit(s?)|nanogram(s)*|x|ppm|million units| Kallikrein inactivator units|kBq|microlitres|MBq|molar|micromol)(/((\d)*[.,]*\d+)*(\s*)(g|dose|ml|mg|ampoule|litre|hour(s)*|h|square cm|microlitres|unit dose|drop))*)') AS dosage,
843	replace(trim(substring(lower(gemscript_name), '((\s|\()[[:digit:]\.]+(\s*)(litre(s?)|ml))')), '(', '') AS volume,
844	A.*
845FROM (
846	SELECT DISTINCT trim(unnest(string_to_array(t.gemscript_name, ' / '))) AS drug_comp,
847		t.*
848	FROM thin_need_to_map t
849	) a
850WHERE a.domain_id = 'Drug'
851	--exclusions
852	--Bendroflumethiazide / potassium 2.5mg+7.7mmol modified release tablets 
853	AND NOT gemscript_name ~* '[[:digit:]\,\.]+.*\+(\s*)[[:digit:]\,\.].*'
854	AND gemscript_code NOT IN (
855		SELECT gemscript_code
856		FROM rel_to_ing_1
857		)
858--Co-triamterzide 50mg/25mg tablets
859--and not regexp_like (gemscript_name, '\dm(c*)g/[[:digit:]\,\.]+m(c*)g')
860
861UNION
862
863--Bendroflumethiazide / potassium 2.5mg+7.7mmol modified release tablets 
864SELECT CONCAT (
865		trim(l.dosage),
866		denom
867		) AS dosage,
868	volume,
869	trim(l.drug_comp) AS drug_comp,
870	thin_code,
871	gemscript_name,
872	gemscript_code,
873	gemscript_name,
874	domain_id
875FROM (
876	SELECT substring(lower(gemscript_name), '(((\d)*[.,]*\d+)(\s)*(mg|%|mcg|iu|mmol|micrograms)(\s)*\+(\s)*[[:digit:]\,\.]+(mg|%|mcg|iu|mmol|micrograms)((\s)*\+(\s)*((\d)*[.,]*\d+)*(\s)*(mg|%|mcg|iu|mmol|micrograms))*)') AS dosage_0,
877		substring(lower(gemscript_name), '(/[[:digit:]\,\.]+(ml| hr|g|mg))') AS denom,
878		replace(trim(substring(lower(gemscript_name), '((\s|\()[[:digit:]\.]+(\s*)(litre(s?)|ml))')), '(', '') AS volume,
879		t.*
880	FROM thin_need_to_map t
881	WHERE gemscript_name ~* '((\d)*[.,]*\d+)(\s)*(mg|%|mcg|iu|mmol|micrograms)(\s)*\+(\s)*[[:digit:]\,\.]+(mg|%|mcg|iu|mmol|micrograms)((\s)*\+(\s)*((\d)*[.,]*\d+)*(\s)*(mg|%|mcg|iu|mmol|micrograms))*'
882		AND domain_id = 'Drug'
883		AND gemscript_code NOT IN (
884			SELECT gemscript_code
885			FROM rel_to_ing_1
886			)
887	) t,
888	LATERAL(SELECT * FROM unnest(string_to_array(t.gemscript_name, ' / '), string_to_array(dosage_0, '+')) AS a(drug_comp, dosage)) l;
889
890--/ampoule is treated as denominator then
891UPDATE thin_comp2
892SET dosage = replace(dosage, '/ampoule', '')
893WHERE dosage LIKE '%/ampoule';
894
895--',c is treated as dosage
896UPDATE thin_comp2
897SET dosage = NULL
898WHERE dosage LIKE '\,%';
899
900CREATE INDEX drug_comp_ix_2 ON thin_comp2 USING GIN (drug_comp devv5.gin_trgm_ops);
901CREATE INDEX drug_comp_ix2_2 ON thin_comp2 (lower (drug_comp));
902ANALYZE thin_comp2;
903
904DROP TABLE IF EXISTS i_map_2;
905CREATE TABLE i_map_2 AS -- enhanced algorithm added  lower (a.thin_name) like lower '% '||(b.concept_name)||' %'
906SELECT *
907FROM (
908	SELECT DISTINCT i.dosage,
909		i.thin_name,
910		i.thin_code,
911		i.drug_comp,
912		i.gemscript_code,
913		i.gemscript_name,
914		i.volume,
915		i.concept_id,
916		i.concept_name,
917		i.vocabulary_id,
918		RANK() OVER (
919			PARTITION BY i.drug_comp ORDER BY LENGTH(i.concept_name) DESC,
920				i.vocabulary_id DESC,
921				i.concept_id
922			) AS rank1
923	FROM (
924		SELECT DISTINCT a.*,
925			rx.concept_id,
926			rx.concept_name,
927			rx.vocabulary_id
928		FROM thin_comp2 a
929		JOIN concept_synonym s ON (
930				a.drug_comp ILIKE s.concept_synonym_name || ' %'
931				OR LOWER(a.drug_comp) = LOWER(s.concept_synonym_name)
932				)
933		JOIN concept_relationship r ON s.concept_id = r.concept_id_1
934			AND r.invalid_reason IS NULL
935		JOIN concept rx ON r.concept_id_2 = rx.concept_id
936			AND rx.vocabulary_id LIKE 'Rx%'
937			AND rx.concept_class_id = 'Ingredient'
938			AND rx.invalid_reason IS NULL
939		) i
940	) AS s0
941--take the longest ingredient
942WHERE rank1 = 1;
943
944--map Ingredients derived from different vocabularies to RxNorm(E)
945DROP TABLE IF EXISTS rel_to_ing_2;
946CREATE TABLE rel_to_ing_2 AS
947SELECT DISTINCT i.dosage,
948	i.drug_comp,
949	i.thin_code,
950	i.thin_name,
951	i.gemscript_code,
952	i.gemscript_name,
953	i.volume,
954	concept_id AS target_id,
955	concept_name AS target_name,
956	vocabulary_id AS target_vocab
957FROM i_map_2 i;
958
959--make temp tables as it was in dmd drug procedure
960DROP TABLE IF EXISTS ds_all_tmp;
961CREATE TABLE ds_all_tmp AS
962SELECT dosage,
963	drug_comp,
964	thin_name AS concept_name,
965	gemscript_code AS concept_code,
966	target_name AS INGREDIENT_CONCEPT_CODE,
967	target_name AS ingredient_concept_name,
968	trim(volume) AS volume,
969	TARGET_ID AS ingredient_id
970FROM rel_to_ing_1
971
972UNION
973
974SELECT dosage,
975	drug_comp,
976	thin_name AS concept_name,
977	gemscript_code AS concept_code,
978	target_name AS INGREDIENT_CONCEPT_CODE,
979	target_name AS ingredient_concept_name,
980	trim(volume) AS volume,
981	TARGET_ID AS ingredient_id
982FROM rel_to_ing_2;
983
984--!!! manual table
985 /*
986--drop table full_manual;
987
988create table full_manual 
989(
990DOSAGE varchar (50),	VOLUME  varchar (50),	THIN_NAME	 varchar (550), GEMSCRIPT_NAME  varchar (550),	ingredient_id	 int, THIN_CODE  varchar (50),	gemscript_code  varchar (50),	INGREDIENT_CONCEPT_CODE  varchar (250),	DOMAIN_ID  varchar (50)
991)
992;
993WbImport -file=C:/work/gemscript_manual/full_manual.txt
994         -type=text
995         -table=FULL_MANUAL
996         -encoding="ISO-8859-15"
997         -header=true
998         -decode=false
999         -dateFormat="yyyy-MM-dd"
1000         -timestampFormat="yyyy-MM-dd HH:mm:ss"
1001         -delimiter='\t'
1002         -decimal=.
1003         -fileColumns=DOSAGE,VOLUME,THIN_NAME,GEMSCRIPT_NAME,INGREDIENT_ID,THIN_CODE,GEMSCRIPT_CODE,INGREDIENT_CONCEPT_CODE,DOMAIN_ID
1004         -quoteCharEscaping=none
1005         -ignoreIdentityColumns=false
1006         -deleteTarget=false
1007         -continueOnError=false
1008         -batchSize=1000;
1009;
1010Update full_manual set ingredient_concept_code=regexp_replace(ingredient_concept_code, '"', '')
1011;
1012MERGE INTO full_manual fm
1013     USING (SELECT distinct  first_value (c.concept_id) over (PARTITION BY c.concept_name order by c.concept_id ) as concept_id, lower (c.concept_name) as concept_name
1014              FROM concept a join concept_relationship cr
1015              on a.concept_id = cr.concept_id_1 
1016              JOIN CONCEPT C on c.concept_id = cr.concept_id_2 and relationship_id in ('Maps to', 'Source - RxNorm eq', 'Concept replaced by' ) 
1017
1018 where  c.vocabulary_id like 'RxNorm%' and c.concept_class_id = 'Ingredient' and c.invalid_reason is null) i
1019        ON ( replace (lower(fm.ingredient_concept_code), '"') = lower (i.concept_name))
1020WHEN MATCHED
1021THEN
1022   UPDATE SET fm.INGREDIENT_ID = i.concept_id;
1023COMMIT;
1024
1025MERGE INTO full_manual fm
1026     USING (SELECT distinct   c.concept_name  , c.concept_id 
1027              FROM concept c where  c.vocabulary_id like 'RxNorm%' and c.concept_class_id = 'Ingredient' and c.invalid_reason is null) i
1028        ON (fm.INGREDIENT_ID = i.concept_id)
1029WHEN MATCHED
1030THEN
1031   UPDATE SET fm.ingredient_concept_code = i.concept_name;
1032COMMIT;
1033
1034 -- update full_manual set ingredient_concept_code = initcap (ingredient_concept_code)
1035--  ;
1036  update full_manual set dosage = lower (dosage)
1037 -- ;
1038  commit
1039  */
1040;
1041create index if not exists dcs_idx_code on drug_concept_stage (concept_code varchar_pattern_ops)
1042;
1043analyze drug_concept_stage
1044/*;
1045update full_manual f
1046set gemscript_code = lpad (gemscript_code,8,'0')
1047where gemscript_code != lpad (gemscript_code,8,'0')
1048*/
1049;
1050DELETE
1051FROM ds_all_tmp
1052WHERE concept_code IN (SELECT gemscript_code
1053                       FROM full_manual
1054                       WHERE ingredient_concept_code IS NOT NULL);
1055
1056INSERT INTO ds_all_tmp (
1057	DOSAGE,
1058	DRUG_COMP,
1059	CONCEPT_NAME,
1060	CONCEPT_CODE,
1061	INGREDIENT_CONCEPT_CODE,
1062	INGREDIENT_CONCEPT_NAME,
1063	VOLUME,
1064	ingredient_id
1065	)
1066SELECT DISTINCT DOSAGE,
1067	NULL,
1068	coalesce(thin_name, gemscript_name),
1069	gemscript_CODE,
1070	INGREDIENT_CONCEPT_CODE,
1071	INGREDIENT_CONCEPT_CODE,
1072	volume,
1073	INGREDIENT_ID
1074FROM full_manual
1075WHERE ingredient_concept_code IS NOT NULL and gemscript_code in (select concept_code from drug_concept_stage);
1076
1077--domain_id definition
1078UPDATE thin_need_to_map t
1079SET domain_id = (
1080		SELECT DISTINCT domain_id
1081		FROM full_manual m
1082		WHERE t.gemscript_code = m.gemscript_code
1083		)
1084WHERE EXISTS (
1085		SELECT 1
1086		FROM full_manual m
1087		WHERE t.gemscript_code = m.gemscript_code
1088			AND domain_id IS NOT NULL
1089		);
1090
1091--packs after manual table in case if in manual table there will be packs
1092DELETE
1093FROM ds_all_tmp
1094WHERE concept_code IN (
1095		SELECT pack_concept_code
1096		FROM pc_stage
1097		);
1098
1099--then merge it with ds_all_tmp, for now temporary decision - make dosages NULL to avoid bug
1100--remove ' ' inside the dosage to make the same as it was before in dmd
1101UPDATE ds_all_tmp
1102SET dosage = replace(dosage, ' ', '');
1103
1104--clean up
1105UPDATE ds_all_tmp
1106SET dosage = replace(dosage, '/', '')
1107WHERE dosage LIKE '%/';
1108;
1109--assign proper OMOP codes to ingredients
1110drop table if exists i_coded;
1111create table i_coded as
1112	(
1113		select 'OMOP' || nextval('code_seq') as concept_code, ingredient_concept_name
1114		from (select distinct ingredient_concept_name from ds_all_tmp) si
1115	)
1116;
1117update ds_all_tmp d
1118set	ingredient_concept_code = (select concept_code from i_coded where ingredient_concept_name = d.ingredient_concept_code)
1119
1120;
1121--dosage distribution along the ds_stage
1122DROP TABLE IF EXISTS ds_all;
1123CREATE TABLE ds_all AS
1124
1125SELECT DISTINCT CASE 
1126		WHEN substring(lower(dosage), '([[:digit:]\,\.]+(mg|%|ml|mcg|hr|hours|unit(s)*|iu|g|microgram(s*)|u|mmol|c|gm|litre|million unit|nanogram(s)*|x|ppm| Kallikrein inactivator units|kBq|MBq|molar|micromol|microlitres|million units|unit dose|drop))') = lower(dosage)
1127			AND NOT dosage ~ '%'
1128			THEN replace(substring(dosage, '[[:digit:]\,\.]+'), ',', '')
1129		ELSE NULL
1130		END AS amount_value,
1131	CASE 
1132		WHEN substring(lower(dosage), '([[:digit:]\,\.]+(mg|%|ml|mcg|hr|hours|unit(s)*|iu|g|microgram(s*)|u|mmol|c|gm|litre|million unit|nanogram(s)*|x|ppm| Kallikrein inactivator units|kBq|MBq|molar|micromol|microlitres|million units|unit dose|drop))') = lower(dosage)
1133			AND NOT dosage ~ '%'
1134			THEN regexp_replace(lower(dosage), '[[:digit:]\,\.]+', '', 'g')
1135		ELSE NULL
1136		END AS amount_unit,
1137	CASE 
1138		WHEN (
1139				substring(lower(dosage), '([[:digit:]\,\.]+(mg|%|ml|mcg|hr|hours|unit(s)*|iu|g|microgram(s*)|u|mmol|c|gm|litre|million unit|nanogram(s)*|x|ppm| Kallikrein inactivator units|kBq|MBq|molar|micromol|microlitres|million units)/[[:digit:]\,\.]*(g|dose|ml|mg|ampoule|litre|hour(s)*|h|square cm|microlitres|unit dose|drop))') = lower(dosage)
1140				AND substring(volume, '[[:digit:]\,\.]+') IS NULL
1141				OR dosage ~ '%'
1142				)
1143			THEN replace(substring(dosage, '^[[:digit:]\,\.]+'), ',', '')
1144		WHEN substring(lower(dosage), '([[:digit:]\,\.]+(mg|%|ml|mcg|hr|hours|unit(s)*|iu|g|microgram(s*)|u|mmol|c|gm|litre|million unit|nanogram(s)*|x|ppm| Kallikrein inactivator units|kBq|MBq|molar|micromol|microlitres|million units)/[[:digit:]\,\.]*(g|dose|ml|mg|ampoule|litre|hour(s)*|h|square cm|microlitres|unit dose|drop))') = lower(dosage)
1145			AND substring(volume, '[[:digit:]\,\.]+') IS NOT NULL
1146			THEN (substring(volume, '[[:digit:]\,\.]+')::FLOAT * replace(substring(dosage, '^[[:digit:]\,\.]+'), ',', '')::FLOAT / coalesce(replace(substring(dosage, '/([[:digit:]\,\.]+)'), ',', '')::FLOAT, 1))::VARCHAR
1147		ELSE NULL
1148		END AS numerator_value,
1149	CASE 
1150		WHEN substring(lower(dosage), '([[:digit:]\,\.]+(mg|%|ml|mcg|hr|hours|unit(s)*|iu|g|microgram(s*)|u|mmol|c|gm|litre|million unit|nanogram(s)*|x|ppm| Kallikrein inactivator units|kBq|MBq|molar|micromol|microlitres|million units)/[[:digit:]\,\.]*(g|dose|ml|mg|ampoule|litre|hour(s)*|h|square cm|microlitres|unit dose|drop))') = lower(dosage)
1151			OR dosage ~ '%'
1152			THEN substring(lower(dosage), '(mg|%|mcg|hr|hours|unit(s)*|iu|g|microgram(s*)|u|mmol|c|gm|litre|million unit|nanogram(s)*|x|ppm| Kallikrein inactivator units|kBq|microlitres)')
1153		ELSE NULL
1154		END AS numerator_unit,
1155	CASE 
1156		WHEN (
1157				substring(dosage, '([[:digit:]\,\.]+(mg|%|ml|mcg|hr|hours|unit(s)*|iu|g|microgram(s*)|u|mmol|c|gm|litre|million unit|nanogram(s)*|x|ppm| Kallikrein inactivator units|kBq|MBq|molar|micromol|microlitres|million units)/[[:digit:]\,\.]*(g|dose|ml|mg|ampoule|litre|hour(s)|h|square cm|microlitres|unit dose|drop))') = dosage
1158				OR dosage ~ '%'
1159				)
1160			AND volume IS NULL
1161			THEN replace(substring(dosage, '/([[:digit:]\,\.]+)'), ',', '')
1162		WHEN volume IS NOT NULL
1163			THEN substring(volume, '[[:digit:]\,\.]+')
1164		ELSE NULL
1165		END AS denominator_value,
1166	CASE 
1167		WHEN (
1168				substring(dosage, '([[:digit:]\,\.]+(mg|%|ml|mcg|hr|hours|unit(s)*|iu|g|microgram(s*)|u|mmol|c|gm|litre|million unit|nanogram(s)*|x|ppm| Kallikrein inactivator units|kBq|MBq|molar|micromol|microlitres|million units)/[[:digit:]\,\.]*(g|dose|ml|mg|ampoule|litre|microlitres|hour(s)*|h|square cm|unit dose|drop))') = dosage
1169				OR dosage ~ '%'
1170				)
1171			AND volume IS NULL
1172			THEN substring(dosage, '(g|dose|ml|mg|ampoule|litre|hour(s)*|h*|square cm|microlitres|unit dose|drop)$')
1173		WHEN volume IS NOT NULL
1174			THEN regexp_replace(volume, '[[:digit:]\,\.]+', '', 'g')
1175		ELSE NULL
1176		END AS denominator_unit,
1177	concept_code,
1178	concept_name,
1179	dosage,
1180	drug_comp,
1181	ingredient_concept_code,
1182	ingredient_concept_name
1183FROM ds_all_tmp;
1184
1185--!!!check the previous script for dmd -patterns should be similar here
1186--add missing denominator if for the other combination it exist
1187UPDATE ds_all a
1188SET (
1189		DENOMINATOR_VALUE,
1190		DENOMINATOR_unit
1191		) = (
1192		SELECT DISTINCT b.DENOMINATOR_VALUE,
1193			b.DENOMINATOR_unit
1194		FROM ds_all b
1195		WHERE a.CONCEPT_CODE = b.CONCEPT_CODE
1196			AND a.DENOMINATOR_unit IS NULL
1197			AND (b.DENOMINATOR_unit IS NOT NULL and b.denominator_unit != '')
1198		)
1199-- a.numerator_value= a.amount_value,a.numerator_unit= a.amount_unit,a.amount_value = null, a.amount_unit = null
1200WHERE EXISTS (
1201		SELECT 1
1202		FROM ds_all b
1203		WHERE a.CONCEPT_CODE = b.CONCEPT_CODE
1204			AND (a.DENOMINATOR_unit IS NULL or a.denominator_unit = '')
1205			AND b.DENOMINATOR_unit IS NOT NULL
1206		);
1207
1208--somehow we get amount +denominator
1209UPDATE ds_all a
1210SET numerator_value = a.amount_value,
1211	numerator_unit = a.amount_unit,
1212	amount_value = NULL,
1213	amount_unit = NULL
1214WHERE a.denominator_unit IS NOT NULL
1215	AND numerator_unit IS NULL;
1216
1217UPDATE ds_all
1218SET amount_VALUE = NULL
1219WHERE amount_VALUE = '.';
1220/*
1221CREATE TABLE DS_STAGE
1222(
1223   DRUG_CONCEPT_CODE        VARCHAR(255 ),
1224   INGREDIENT_CONCEPT_CODE  VARCHAR(255 ),
1225   AMOUNT_VALUE             FLOAT,
1226   AMOUNT_UNIT              VARCHAR(255 ),
1227   NUMERATOR_VALUE          FLOAT,
1228   NUMERATOR_UNIT           VARCHAR(255 ),
1229   DENOMINATOR_VALUE        FLOAT,
1230   DENOMINATOR_UNIT         VARCHAR(255 ),
1231   BOX_SIZE                 int
1232)
1233;
1234*/
1235
1236TRUNCATE TABLE ds_stage;
1237INSERT INTO ds_stage (
1238	drug_concept_code,
1239	ingredient_concept_code,
1240	amount_value,
1241	amount_unit,
1242	numerator_value,
1243	numerator_unit,
1244	denominator_value,
1245	denominator_unit
1246	)
1247SELECT DISTINCT
1248	--add distinct here because of Paracetamol / pseudoephedrine / paracetamol / diphenhydramine tablet
1249	CONCEPT_CODE,
1250	INGREDIENT_CONCEPT_CODE,
1251	AMOUNT_VALUE::FLOAT,
1252	AMOUNT_UNIT,
1253	NUMERATOR_VALUE::FLOAT,
1254	NUMERATOR_UNIT,
1255	DENOMINATOR_VALUE::FLOAT,
1256	DENOMINATOR_UNIT
1257FROM ds_all;
1258
1259
1260-- update denominator with existing value for concepts having empty and non-emty denominator value/unit
1261 --fix wierd units
1262UPDATE ds_stage
1263SET amount_unit = 'unit'
1264WHERE amount_unit IN (
1265		'u',
1266		'iu'
1267		);
1268
1269UPDATE ds_stage
1270SET NUMERATOR_UNIT = 'unit'
1271WHERE NUMERATOR_UNIT IN (
1272		'u',
1273		'iu'
1274		);
1275
1276UPDATE ds_stage
1277SET DENOMINATOR_UNIT = NULL
1278WHERE DENOMINATOR_UNIT = 'ampoule';
1279
1280UPDATE ds_stage
1281SET DENOMINATOR_UNIT = replace(DENOMINATOR_UNIT, ' ', '')
1282WHERE DENOMINATOR_UNIT LIKE '% %';
1283
1284DELETE
1285FROM ds_stage
1286WHERE ingredient_concept_code = 'Syrup';
1287
1288DELETE
1289FROM ds_stage
1290WHERE 0 IN (
1291		numerator_value,
1292		amount_value,
1293		denominator_value
1294		);
1295
1296--sum up the Zinc undecenoate 20% / Undecenoic acid 5% cream
1297DELETE
1298FROM ds_stage
1299WHERE DRUG_CONCEPT_CODE = '1637007'
1300	AND INGREDIENT_CONCEPT_CODE = 'OMOP1021956'
1301	AND NUMERATOR_VALUE = 50;
1302
1303UPDATE ds_stage
1304SET NUMERATOR_VALUE = 250
1305WHERE DRUG_CONCEPT_CODE = '1637007'
1306	AND INGREDIENT_CONCEPT_CODE = 'OMOP1021956'
1307	AND NUMERATOR_VALUE = 200;
1308
1309--percents
1310--update ds_stage changing % to mg/ml, mg/g, etc.
1311--simple, when we have denominator_unit so we can define numerator based on denominator_unit
1312UPDATE ds_stage
1313SET numerator_value = DENOMINATOR_VALUE * NUMERATOR_VALUE * 10,
1314	numerator_unit = 'mg'
1315WHERE numerator_unit = '%'
1316	AND DENOMINATOR_UNIT IN (
1317		'ml',
1318		'gram',
1319		'g'
1320		);
1321
1322UPDATE ds_stage
1323SET numerator_value = DENOMINATOR_VALUE * NUMERATOR_VALUE * 0.01,
1324	numerator_unit = 'mg'
1325WHERE numerator_unit = '%'
1326	AND DENOMINATOR_UNIT IN ('mg');
1327
1328UPDATE ds_stage
1329SET numerator_value = DENOMINATOR_VALUE * NUMERATOR_VALUE * 10,
1330	numerator_unit = 'g'
1331WHERE numerator_unit = '%'
1332	AND DENOMINATOR_UNIT IN ('litre');
1333
1334--let's make only %-> mg/ml if denominator is null
1335UPDATE ds_stage ds
1336SET numerator_value = NUMERATOR_VALUE * 10,
1337	numerator_unit = 'mg',
1338	denominator_unit = 'ml'
1339WHERE numerator_unit = '%'
1340	AND denominator_unit IS NULL
1341	AND denominator_value IS NULL;
1342
1343DELETE
1344FROM ds_stage
1345WHERE drug_concept_code IN (
1346		SELECT pack_concept_code
1347		FROM pc_stage
1348		);
1349/*
1350--check for non ds_stage cover
1351select * from thin_need_to_map where gemscript_code not in (select drug_concept_code from ds_stage where drug_concept_code is not null)
1352 and gemscript_code not in (select gemscript_code from full_manual where gemscript_code is not null) and domain_id = 'Drug' 
1353and gemscript_code not in (select pack_concept_code from pc_stage where pack_concept_code is not null )
1354;
1355*/
1356--apply the dose form updates then to extract them from the original names
1357--make a proper dose form from the short terms used in a concept_names
1358
1359UPDATE thin_need_to_map
1360SET thin_name = regexp_replace(thin_name, 'oin$', 'ointment', 'gi')
1361WHERE thin_name LIKE '%oin';
1362
1363UPDATE thin_need_to_map
1364SET thin_name = regexp_replace(thin_name, 'tab$', 'tablet', 'gi')
1365WHERE thin_name LIKE '%tab';
1366
1367UPDATE thin_need_to_map
1368SET thin_name = regexp_replace(thin_name, 'inj$', 'injection', 'gi')
1369WHERE thin_name LIKE '%inj';
1370
1371UPDATE thin_need_to_map
1372SET thin_name = regexp_replace(thin_name, 'cre$', 'cream', 'gi')
1373WHERE thin_name LIKE '%cre';
1374
1375UPDATE thin_need_to_map
1376SET thin_name = regexp_replace(thin_name, 'lin$', 'linctus', 'gi')
1377WHERE thin_name LIKE '%lin';
1378
1379UPDATE thin_need_to_map
1380SET thin_name = regexp_replace(thin_name, 'sol$', 'solution', 'gi')
1381WHERE thin_name LIKE '%sol';
1382
1383UPDATE thin_need_to_map
1384SET thin_name = regexp_replace(thin_name, 'cap$', 'capsule', 'gi')
1385WHERE thin_name LIKE '%cap';
1386
1387UPDATE thin_need_to_map
1388SET thin_name = regexp_replace(thin_name, 'loz$', 'lozenge', 'gi')
1389WHERE thin_name LIKE '%loz';
1390
1391UPDATE thin_need_to_map
1392SET thin_name = regexp_replace(thin_name, 'lozenge$', 'lozenges', 'gi')
1393WHERE thin_name LIKE '%lozenge';
1394
1395UPDATE thin_need_to_map
1396SET thin_name = regexp_replace(thin_name, 'sus$', 'suspension', 'gi')
1397WHERE thin_name LIKE '%sus';
1398
1399UPDATE thin_need_to_map
1400SET thin_name = regexp_replace(thin_name, 'eli$', 'elixir', 'gi')
1401WHERE thin_name LIKE '%eli';
1402
1403UPDATE thin_need_to_map
1404SET thin_name = regexp_replace(thin_name, 'sup$', 'suppositories', 'gi')
1405WHERE thin_name LIKE '%sup';
1406
1407UPDATE thin_need_to_map
1408SET thin_name = regexp_replace(thin_name, 'gra$', 'granules', 'gi')
1409WHERE thin_name LIKE '%gra';
1410
1411UPDATE thin_need_to_map
1412SET thin_name = regexp_replace(thin_name, 'pow$', 'powder', 'gi')
1413WHERE thin_name LIKE '%pow';
1414
1415UPDATE thin_need_to_map
1416SET thin_name = regexp_replace(thin_name, 'pel$', 'pellets', 'gi')
1417WHERE thin_name LIKE '%pel';
1418
1419UPDATE thin_need_to_map
1420SET thin_name = regexp_replace(thin_name, 'lot$', 'lotion', 'gi')
1421WHERE thin_name LIKE '%lot';
1422
1423UPDATE thin_need_to_map
1424SET thin_name = regexp_replace(thin_name, 'pre-filled syr$', 'pre-filled syringe', 'gi')
1425WHERE thin_name LIKE '%pre-filled syr';
1426
1427UPDATE thin_need_to_map
1428SET thin_name = regexp_replace(thin_name, 'syr$', 'syrup', 'gi')
1429WHERE thin_name LIKE '%syr';
1430
1431UPDATE thin_need_to_map
1432SET thin_name = regexp_replace(thin_name, 'app$', 'applicator', 'gi')
1433WHERE thin_name LIKE '%app';
1434
1435UPDATE thin_need_to_map
1436SET thin_name = regexp_replace(thin_name, 'dro$', 'drops', 'gi')
1437WHERE thin_name LIKE '%dro';
1438
1439UPDATE thin_need_to_map
1440SET thin_name = regexp_replace(thin_name, 'aer$', 'aerosol', 'gi')
1441WHERE thin_name LIKE '%aer';
1442
1443UPDATE thin_need_to_map
1444SET thin_name = regexp_replace(thin_name, 'liq$', 'liquid', 'gi')
1445WHERE thin_name LIKE '%liq';
1446
1447UPDATE thin_need_to_map
1448SET thin_name = regexp_replace(thin_name, 'homeopathic pillules$', 'pillules', 'gi')
1449WHERE thin_name LIKE '%homeopathic pillules';
1450
1451UPDATE thin_need_to_map
1452SET thin_name = regexp_replace(thin_name, 'spa$', 'spansules', 'gi')
1453WHERE thin_name LIKE '%spa';
1454
1455UPDATE thin_need_to_map
1456SET thin_name = regexp_replace(thin_name, 'emu$', 'emulsion', 'gi')
1457WHERE thin_name LIKE '%emu';
1458
1459--paste
1460UPDATE thin_need_to_map
1461SET thin_name = regexp_replace(thin_name, 'pas$', 'paste', 'gi')
1462WHERE thin_name LIKE '%pas';
1463
1464--pillules
1465UPDATE thin_need_to_map
1466SET thin_name = regexp_replace(thin_name, 'pills$', 'pillules', 'gi')
1467WHERE thin_name LIKE '%pills';
1468
1469--spray
1470UPDATE thin_need_to_map
1471SET thin_name = regexp_replace(thin_name, 'spr$', 'spray', 'gi')
1472WHERE thin_name LIKE '%spr';
1473
1474--inhalation
1475UPDATE thin_need_to_map
1476SET thin_name = regexp_replace(thin_name, 'inh$', 'inhalation', 'gi')
1477WHERE thin_name LIKE '%inh';
1478
1479--suppositories
1480UPDATE thin_need_to_map
1481SET thin_name = regexp_replace(thin_name, 'suppository$', 'suppositories', 'gi')
1482WHERE thin_name LIKE '%suppository';
1483
1484--oitnment
1485UPDATE thin_need_to_map
1486SET thin_name = regexp_replace(thin_name, 'oitnment$', 'ointment', 'gi')
1487WHERE thin_name LIKE '%oitnment';
1488
1489--pessary
1490UPDATE thin_need_to_map
1491SET thin_name = regexp_replace(thin_name, 'pes$', 'pessary', 'gi')
1492WHERE thin_name LIKE '%pes';
1493
1494UPDATE thin_need_to_map
1495SET thin_name = regexp_replace(thin_name, 'pessary$', 'pessaries', 'gi')
1496WHERE thin_name LIKE '%pessary';
1497
1498UPDATE thin_need_to_map
1499SET thin_name = regexp_replace(thin_name, 'spansules$', 'capsule', 'gi')
1500WHERE thin_name LIKE '%spansules';
1501
1502UPDATE thin_need_to_map
1503SET thin_name = regexp_replace(thin_name, 'globuli$', 'granules', 'gi')
1504WHERE thin_name LIKE '%globuli';
1505
1506UPDATE thin_need_to_map
1507SET thin_name = regexp_replace(thin_name, 'sach$', 'sachet', 'gi')
1508WHERE thin_name LIKE '%sach';
1509
1510UPDATE thin_need_to_map
1511SET thin_name = regexp_replace(thin_name, 'oin$', 'ointment', 'gi')
1512WHERE thin_name LIKE '%oin';
1513
1514UPDATE thin_need_to_map
1515SET thin_name = regexp_replace(thin_name, 'tab$', 'tablet', 'gi')
1516WHERE thin_name LIKE '%tab';
1517
1518UPDATE thin_need_to_map
1519SET thin_name = regexp_replace(thin_name, 'inj$', 'injection', 'gi')
1520WHERE thin_name LIKE '%inj';
1521
1522UPDATE thin_need_to_map
1523SET thin_name = regexp_replace(thin_name, 'cre$', 'cream', 'gi')
1524WHERE thin_name LIKE '%cre';
1525
1526UPDATE thin_need_to_map
1527SET thin_name = regexp_replace(thin_name, 'lin$', 'linctus', 'gi')
1528WHERE thin_name LIKE '%lin';
1529
1530UPDATE thin_need_to_map
1531SET thin_name = regexp_replace(thin_name, 'sol$', 'solution', 'gi')
1532WHERE thin_name LIKE '%sol';
1533
1534UPDATE thin_need_to_map
1535SET thin_name = regexp_replace(thin_name, 'cap$', 'capsule', 'gi')
1536WHERE thin_name LIKE '%cap';
1537
1538UPDATE thin_need_to_map
1539SET thin_name = regexp_replace(thin_name, 'loz$', 'lozenge', 'gi')
1540WHERE thin_name LIKE '%loz';
1541
1542UPDATE thin_need_to_map
1543SET thin_name = regexp_replace(thin_name, 'lozenge$', 'lozenges', 'gi')
1544WHERE thin_name LIKE '%lozenge';
1545
1546UPDATE thin_need_to_map
1547SET thin_name = regexp_replace(thin_name, 'sus$', 'suspension', 'gi')
1548WHERE thin_name LIKE '%sus';
1549
1550UPDATE thin_need_to_map
1551SET thin_name = regexp_replace(thin_name, 'eli$', 'elixir', 'gi')
1552WHERE thin_name LIKE '%eli';
1553
1554UPDATE thin_need_to_map
1555SET thin_name = regexp_replace(thin_name, 'sup$', 'suppositories', 'gi')
1556WHERE thin_name LIKE '%sup';
1557
1558UPDATE thin_need_to_map
1559SET thin_name = regexp_replace(thin_name, 'gra$', 'granules', 'gi')
1560WHERE thin_name LIKE '%gra';
1561
1562UPDATE thin_need_to_map
1563SET thin_name = regexp_replace(thin_name, 'pow$', 'powder', 'gi')
1564WHERE thin_name LIKE '%pow';
1565
1566UPDATE thin_need_to_map
1567SET thin_name = regexp_replace(thin_name, 'pel$', 'pellets', 'gi')
1568WHERE thin_name LIKE '%pel';
1569
1570UPDATE thin_need_to_map
1571SET thin_name = regexp_replace(thin_name, 'lot$', 'lotion', 'gi')
1572WHERE thin_name LIKE '%lot';
1573
1574UPDATE thin_need_to_map
1575SET thin_name = regexp_replace(thin_name, 'pre-filled syr$', 'pre-filled syringe', 'gi')
1576WHERE thin_name LIKE '%pre-filled syr';
1577
1578UPDATE thin_need_to_map
1579SET thin_name = regexp_replace(thin_name, 'syr$', 'syrup', 'gi')
1580WHERE thin_name LIKE '%syr';
1581
1582UPDATE thin_need_to_map
1583SET thin_name = regexp_replace(thin_name, 'app$', 'applicator', 'gi')
1584WHERE thin_name LIKE '%app';
1585
1586UPDATE thin_need_to_map
1587SET thin_name = regexp_replace(thin_name, 'dro$', 'drops', 'gi')
1588WHERE thin_name LIKE '%dro';
1589
1590UPDATE thin_need_to_map
1591SET thin_name = regexp_replace(thin_name, 'aer$', 'aerosol', 'gi')
1592WHERE thin_name LIKE '%aer';
1593
1594UPDATE thin_need_to_map
1595SET thin_name = regexp_replace(thin_name, 'liq$', 'liquid', 'gi')
1596WHERE thin_name LIKE '%liq';
1597
1598UPDATE thin_need_to_map
1599SET thin_name = regexp_replace(thin_name, 'homeopathic pillules$', 'pillules', 'gi')
1600WHERE thin_name LIKE '%homeopathic pillules';
1601
1602UPDATE thin_need_to_map
1603SET thin_name = regexp_replace(thin_name, 'spa$', 'spansules', 'gi')
1604WHERE thin_name LIKE '%spa';
1605
1606UPDATE thin_need_to_map
1607SET thin_name = regexp_replace(thin_name, 'emu$', 'emulsion', 'gi')
1608WHERE thin_name LIKE '%emu';
1609
1610--paste
1611UPDATE thin_need_to_map
1612SET thin_name = regexp_replace(thin_name, 'pas$', 'paste', 'gi')
1613WHERE thin_name LIKE '%pas';
1614
1615--pillules
1616UPDATE thin_need_to_map
1617SET thin_name = regexp_replace(thin_name, 'pills$', 'pillules', 'gi')
1618WHERE thin_name LIKE '%pills';
1619
1620--spray
1621UPDATE thin_need_to_map
1622SET thin_name = regexp_replace(thin_name, 'spr$', 'spray', 'gi')
1623WHERE thin_name LIKE '%spr';
1624
1625--inhalation
1626UPDATE thin_need_to_map
1627SET thin_name = regexp_replace(thin_name, 'inh$', 'inhalation', 'gi')
1628WHERE thin_name LIKE '%inh';
1629
1630--suppositories
1631UPDATE thin_need_to_map
1632SET thin_name = regexp_replace(thin_name, 'suppository$', 'suppositories', 'gi')
1633WHERE thin_name LIKE '%suppository';
1634
1635--oitnment
1636UPDATE thin_need_to_map
1637SET thin_name = regexp_replace(thin_name, 'oitnment$', 'ointment', 'gi')
1638WHERE thin_name LIKE '%oitnment';
1639
1640--pessary
1641UPDATE thin_need_to_map
1642SET thin_name = regexp_replace(thin_name, 'pes$', 'pessary', 'gi')
1643WHERE thin_name LIKE '%pes';
1644
1645UPDATE thin_need_to_map
1646SET thin_name = regexp_replace(thin_name, 'pessary$', 'pessaries', 'gi')
1647WHERE thin_name LIKE '%pessary';
1648
1649UPDATE thin_need_to_map
1650SET thin_name = regexp_replace(thin_name, 'spansules$', 'capsule', 'gi')
1651WHERE thin_name LIKE '%spansules';
1652
1653UPDATE thin_need_to_map
1654SET thin_name = regexp_replace(thin_name, 'globuli$', 'granules', 'gi')
1655WHERE thin_name LIKE '%globuli';
1656
1657UPDATE thin_need_to_map
1658SET thin_name = regexp_replace(thin_name, 'sach$', 'sachet', 'gi')
1659WHERE thin_name LIKE '%sach';
1660
1661--Execution time: 3m 28s when "mm" is used
1662;
1663analyze thin_need_to_map
1664; 
1665create index idx_tnm on thin_need_to_map (thin_name varchar_pattern_ops, GEMSCRIPT_NAME varchar_pattern_ops)
1666;
1667create index idx_di on thin_need_to_map (domain_id)
1668;
1669analyze thin_need_to_map
1670;
1671drop table if exists f_map_var;
1672create table f_map_var as ( -- enhanced algorithm added  lower (a.thin_name) like lower '% '||(b.concept_name)||' %'
1673select * from 
1674	(
1675		select distinct 
1676			a.*,
1677			b.concept_id,
1678			b.concept_name,
1679			b.vocabulary_id,
1680			b.concept_code,
1681			RANK() OVER 
1682				(
1683					PARTITION BY a.gemscript_code
1684					ORDER BY 
1685						length(b.concept_name) desc, 
1686						case 
1687							when b.vocabulary_id = 'dm+d' then 1
1688							when b.vocabulary_id = 'GRR' then 2
1689							when b.vocabulary_id = 'AMT' then 3
1690							when  b.vocabulary_id = 'DPD' then 4
1691							when b.vocabulary_id = 'BDPM' then 5
1692							when b.vocabulary_id = 'LPD_Australia' then 6
1693							when b.vocabulary_id = 'AMIS' then 7
1694							else 10 
1695						end asc
1696				) as rank1
1697		from thin_need_to_map a 
1698		join concept b on 
1699			--Slow, for some reason?
1700			lower (coalesce (a.thin_name, a.GEMSCRIPT_NAME)) ~ ('(' || lower  (' '||b.concept_name||'( |$|s|es)') || ')|(' || lower  (' '||regexp_replace  (b.concept_name, 'y$', 'ies') ||'( |$)') || ')') and 
1701			/*(
1702				LOWER (COALESCE (a.thin_name,a.gemscript_name)) ~LOWER (' ' ||b.concept_name|| '( |$|s|es)') OR
1703				(LOWER (COALESCE (a.thin_name,a.gemscript_name)) ~LOWER (' ' ||regexp_replace (b.concept_name,'y$','ies') || '( |$)'))
1704			) and*/
1705			vocabulary_id in ('dm+d', 'AMT', 'BDPM', 'AMIS', 'DPD', 'LPD_Australia', 'GRR', 'RxNorm', 'RxNorm Extension') and
1706			concept_class_id in ( 'Dose Form', 'Form', 'AU Qualifier') and
1707			a.domain_id = 'Drug' and
1708			b.domain_id ='Drug' and
1709			invalid_reason is null
1710	) a
1711--take the longest ingredient
1712where rank1 = 1 
1713)
1714;
1715--mappings 
1716drop table if exists forms_mapping;
1717--use old relationship_to_concept tables to define form mappings with precedence
1718create table forms_mapping as
1719select distinct f.concept_name as concept_code_1, map.concept_id_2, precedence, x.concept_name as concept_name_2   from f_map_var f
1720join concept c on c.concept_id = f.concept_id
1721left join 
1722
1723form_map_old -- manual table
1724 map on c.concept_code = map.concept_code_1 and c.vocabulary_id = vocabulary_id_1
1725left join concept x on x.concept_id = map.concept_id_2
1726--where x.concept_id is null
1727;
1728update forms_mapping
1729set 
1730	precedence = 1,
1731	(concept_id_2, concept_name_2) = 
1732		(
1733			select concept_id, concept_name 
1734			from concept 
1735			where
1736				invalid_reason is null and
1737				lower (concept_name) = lower (concept_code_1) and
1738				concept_class_id = 'Dose Form' and
1739				vocabulary_id in ('RxNorm','RxNorm Extension')
1740		)
1741where concept_id_2 is null
1742;
1743update forms_mapping x
1744set 
1745	(concept_id_2, concept_name_2) = 
1746		(
1747			select distinct c2.concept_id, c2.concept_name 
1748			from concept c
1749			join concept_relationship r on
1750				c.invalid_reason is null and
1751				r.invalid_reason is null and
1752				lower (c.concept_name) = lower (x.concept_code_1) and
1753				c.concept_class_id in ( 'Dose Form', 'Form', 'AU Qualifier') and
1754				r.concept_id_1 = c.concept_id and
1755				r.relationship_id = 'Source - RxNorm eq'
1756			join concept c2 on
1757				c2.concept_id = r.concept_id_2 and
1758				c2.concept_id not in (19082109,1592486)
1759		)
1760where concept_id_2 is null
1761;
1762--update mappings with precedence using forms equivalents that have multiple mappings
1763insert into forms_mapping
1764select old_name, concept_id_2, precedence, concept_name_2 from forms_mapping join (
1765select 'Prefilled Syringe' as old_name , 'Pen' as new_name  
1766union
1767select 'Dry Powder Inhaler', 'Inhalation powder' 
1768union 
1769select  'Inhalant', 'Inhalation Solution' 
1770union
1771select  'Powder Spray', 'Inhalation powder'
1772) aa 
1773on aa.new_name  = forms_mapping.concept_code_1
1774;
1775
1776delete from forms_mapping where concept_code_1 in (select old_name from (
1777select 'Prefilled Syringe' as old_name , 'Pen' as new_name  
1778union
1779select 'Dry Powder Inhaler', 'Inhalation powder' 
1780union 
1781select  'Inhalant', 'Inhalation Solution' 
1782union
1783select  'Powder Spray', 'Inhalation powder'
1784) aa ) and concept_id_2 is null
1785;
1786/*
1787select * from forms_mapping where concept_code_1 =
1788'Gel'*/
1789;
1790--fix inacurracies
1791UPDATE FORMS_MAPPING
1792   SET PRECEDENCE = 4  WHERE CONCEPT_CODE_1 = 'Gel'
1793AND   concept_id_2 = 19010880;
1794INSERT INTO FORMS_MAPPING
1795(
1796  CONCEPT_CODE_1,  concept_id_2,  PRECEDENCE,  CONCEPT_NAME_2
1797)
1798VALUES
1799(
1800  'Gel',  19095973,  1,  'Topical Gel');
1801--algorithm for forms make ambiguities when there are two forms with the same length in within one vocabulary
1802DELETE
1803FROM F_MAP_VAR
1804WHERE GEMSCRIPT_CODE = '104007'
1805AND   concept_id = 21215788;
1806DELETE
1807FROM F_MAP_VAR
1808WHERE GEMSCRIPT_CODE = '54128020'
1809AND   concept_id = 43360666;
1810DELETE
1811FROM F_MAP_VAR
1812WHERE GEMSCRIPT_CODE = '58583020'
1813AND   concept_id = 43360666;
1814DELETE
1815FROM F_MAP_VAR
1816WHERE GEMSCRIPT_CODE = '61770020'
1817AND   concept_id = 21308470;
1818DELETE
1819FROM F_MAP_VAR
1820WHERE GEMSCRIPT_CODE = '76284020'
1821AND   concept_id = 21308470;
1822
1823commit
1824;
1825
1826--make Suppliers, some clean up
1827UPDATE thin_need_to_map
1828SET GEMSCRIPT_NAME = GEMSCRIPT_NAME || ')'
1829WHERE GEMSCRIPT_NAME LIKE '%(Neon Diagnostics';
1830
1831DROP TABLE IF EXISTS s_rel;
1832CREATE TABLE s_rel AS
1833SELECT substring(GEMSCRIPT_NAME, '\(([A-Z].+)\)$') AS Supplier,
1834	n.*
1835FROM thin_need_to_map n
1836WHERE domain_id = 'Drug';
1837
1838DROP TABLE IF EXISTS s_map;
1839CREATE TABLE s_map AS
1840SELECT DISTINCT s.gemscript_code,
1841	s.GEMSCRIPT_NAME,
1842	sss.concept_id_2,
1843	concept_name_2,
1844	vocabulary_id_2
1845FROM s_rel s
1846JOIN concept c ON lower(s.Supplier) = lower(c.concept_name)
1847JOIN (
1848	SELECT c.concept_id AS source_id,
1849		coalesce(d.concept_name, c.concept_name) AS concept_name_2,
1850		coalesce(d.concept_id, c.concept_id) AS concept_id_2,
1851		coalesce(d.vocabulary_id, c.vocabulary_id) AS vocabulary_id_2
1852	FROM concept c
1853	LEFT JOIN (
1854		SELECT concept_id_1,
1855			relationship_id,
1856			concept_id_2
1857		FROM concept_relationship
1858		WHERE invalid_reason IS NULL
1859		
1860		UNION
1861		
1862		SELECT concept_id_1,
1863			relationship_id,
1864			concept_id_2
1865		FROM rel_to_conc_old
1866		) r ON c.concept_id = r.concept_id_1
1867		AND relationship_id = 'Source - RxNorm eq'
1868	LEFT JOIN concept d ON d.concept_id = r.concept_id_2
1869		AND d.vocabulary_id LIKE 'RxNorm%'
1870		AND d.invalid_reason IS NULL
1871		AND d.concept_class_id = 'Supplier'
1872	WHERE c.concept_class_id IN ('Supplier')
1873		AND c.invalid_reason IS NULL
1874	) sss ON sss.source_id = c.concept_id
1875	AND sss.vocabulary_id_2 IN (
1876		'RxNorm',
1877		'RxNorm Extension'
1878		) --not clear, need to fix in the future
1879WHERE c.concept_class_id = 'Supplier';
1880
1881--make Brand Names
1882--select * from thin_need_to_map where thin_name like 'Generic%';
1883CREATE INDEX gemscript_name_idx ON thin_need_to_map USING GIN (gemscript_name devv5.gin_trgm_ops);
1884CREATE INDEX thin_name_idx ON thin_need_to_map USING GIN (thin_name devv5.gin_trgm_ops);
1885ANALYZE thin_need_to_map;
1886
1887DROP TABLE IF EXISTS b_map_0;
1888CREATE TABLE b_map_0 AS
1889SELECT T.GEMSCRIPT_CODE,
1890	T.GEMSCRIPT_NAME,
1891	T.THIN_CODE,
1892	T.THIN_NAME,
1893	C.concept_id,
1894	C.CONCEPT_NAME,
1895	C.vocabulary_id
1896FROM thin_need_to_map T
1897JOIN concept c ON gemscript_name ilike c.concept_name || ' %'
1898WHERE c.concept_class_id = 'Brand Name'
1899	AND invalid_reason IS NULL
1900	AND vocabulary_id IN (
1901		'RxNorm',
1902		'RxNorm Extension'
1903		)
1904	--exclude ingredients that accindentally got into Brand Names massive
1905	AND lower(c.concept_name) NOT IN (
1906		SELECT lower(concept_name)
1907		FROM concept
1908		WHERE concept_class_id = 'Ingredient'
1909			AND invalid_reason IS NULL
1910		)
1911	AND t.domain_id = 'Drug'
1912	AND C.CONCEPT_NAME NOT IN (
1913		'Gamma',
1914		'Mst',
1915		'Gx',
1916		'Simple',
1917		'Saline',
1918		'DF',
1919		'Stibium'
1920		);
1921
1922DROP TABLE IF EXISTS b_map_1;
1923CREATE TABLE b_map_1 AS
1924SELECT T.GEMSCRIPT_CODE,
1925	T.GEMSCRIPT_NAME,
1926	T.THIN_CODE,
1927	T.THIN_NAME,
1928	C.concept_id,
1929	C.CONCEPT_NAME,
1930	C.vocabulary_id
1931FROM thin_need_to_map T
1932JOIN concept c ON thin_name ilike c.concept_name || ' %'
1933LEFT JOIN b_map_0 b ON b.gemscript_code = t.gemscript_code
1934WHERE c.concept_class_id = 'Brand Name'
1935	AND c.invalid_reason IS NULL
1936	AND c.vocabulary_id IN (
1937		'RxNorm',
1938		'RxNorm Extension'
1939		)
1940	--exclude ingredients that accindally got into Brand Names massive
1941	AND lower(c.concept_name) NOT IN (
1942		SELECT lower(concept_name)
1943		FROM concept
1944		WHERE concept_class_id = 'Ingredient'
1945			AND invalid_reason IS NULL
1946		)
1947	AND t.domain_id = 'Drug'
1948	AND b.gemscript_code IS NULL
1949	AND C.CONCEPT_NAME NOT IN (
1950		'Natrum muriaticum',
1951		'Pulsatilla nigricans',
1952		'Multivitamin',
1953		'Saline',
1954		'Simple'
1955		);
1956
1957DROP INDEX gemscript_name_idx;
1958DROP INDEX thin_name_idx;
1959
1960DROP TABLE IF EXISTS b_map;
1961CREATE TABLE b_map AS
1962SELECT *
1963FROM (
1964	SELECT z.*,
1965		RANK() OVER (
1966			PARTITION BY gemscript_code ORDER BY length(concept_name) DESC
1967			) AS rank1
1968	FROM (
1969		SELECT *
1970		FROM b_map_0
1971		
1972		UNION
1973		
1974		SELECT *
1975		FROM b_map_1
1976		) z
1977	WHERE z.vocabulary_id IN (
1978			'RxNorm',
1979			'RxNorm Extension'
1980			) --not clear, need to fix in the future
1981	) x
1982WHERE x.rank1 = 1;
1983
1984--making input tables
1985
1986truncate table drug_concept_stage
1987;
1988
1989--Drug Product
1990INSERT INTO drug_concept_stage (
1991	CONCEPT_NAME,
1992	DOMAIN_ID,
1993	VOCABULARY_ID,
1994	CONCEPT_CLASS_ID,
1995	STANDARD_CONCEPT,
1996	CONCEPT_CODE,
1997	VALID_START_DATE,
1998	VALID_END_DATE,
1999	INVALID_REASON,
2000	SOURCE_CONCEPT_CLASS_ID
2001	)
2002SELECT DISTINCT gemscript_name,
2003	domain_id,
2004	'Gemscript',
2005	'Drug Product',
2006	NULL,
2007	gemscript_code,
2008	(
2009		SELECT latest_update
2010		FROM vocabulary
2011		WHERE vocabulary_id = 'Gemscript'
2012		) AS valid_start_date, -- TRUNC(SYSDATE)
2013	to_date('20991231', 'yyyymmdd') AS valid_end_date,
2014	NULL,
2015	'Gemscript'
2016FROM thin_need_to_map
2017WHERE domain_id = 'Drug';
2018
2019--Device
2020INSERT INTO drug_concept_stage (
2021	CONCEPT_NAME,
2022	DOMAIN_ID,
2023	VOCABULARY_ID,
2024	CONCEPT_CLASS_ID,
2025	STANDARD_CONCEPT,
2026	CONCEPT_CODE,
2027	VALID_START_DATE,
2028	VALID_END_DATE,
2029	INVALID_REASON,
2030	SOURCE_CONCEPT_CLASS_ID
2031	)
2032SELECT DISTINCT gemscript_name,
2033	domain_id,
2034	'Gemscript',
2035	'Device',
2036	'S',
2037	gemscript_code,
2038	(
2039		SELECT latest_update
2040		FROM vocabulary
2041		WHERE vocabulary_id = 'Gemscript'
2042		) AS valid_start_date, -- TRUNC(SYSDATE)
2043	to_date('20991231', 'yyyymmdd') AS valid_end_date,
2044	NULL,
2045	'Gemscript'
2046FROM thin_need_to_map
2047WHERE domain_id = 'Device';
2048--replace pc_stage component codes with their assigned OMOP% codes
2049update pc_stage p
2050set drug_concept_code = (select concept_code from drug_concept_stage where concept_name = p.drug_concept_code and concept_code like 'OMOP%' and concept_class_id = 'Drug Product')
2051;
2052--Ingredient
2053INSERT INTO drug_concept_stage (
2054	CONCEPT_NAME,
2055	DOMAIN_ID,
2056	VOCABULARY_ID,
2057	CONCEPT_CLASS_ID,
2058	STANDARD_CONCEPT,
2059	CONCEPT_CODE,
2060	VALID_START_DATE,
2061	VALID_END_DATE,
2062	INVALID_REASON,
2063	SOURCE_CONCEPT_CLASS_ID
2064	)
2065SELECT DISTINCT Ingredient_concept_name,
2066	'Drug',
2067	'Gemscript',
2068	'Ingredient',
2069	NULL,
2070	Ingredient_concept_code,
2071	(
2072		SELECT latest_update
2073		FROM vocabulary
2074		WHERE vocabulary_id = 'Gemscript'
2075		) AS valid_start_date, -- TRUNC(SYSDATE)
2076	to_date('20991231', 'yyyymmdd') AS valid_end_date,
2077	NULL,
2078	'Gemscript'
2079FROM ds_all_tmp;
2080	--only 1041 --looks susprecious
2081
2082--Supplier
2083INSERT INTO drug_concept_stage (
2084	CONCEPT_NAME,
2085	DOMAIN_ID,
2086	VOCABULARY_ID,
2087	CONCEPT_CLASS_ID,
2088	STANDARD_CONCEPT,
2089	CONCEPT_CODE,
2090	VALID_START_DATE,
2091	VALID_END_DATE,
2092	INVALID_REASON,
2093	SOURCE_CONCEPT_CLASS_ID
2094	)
2095SELECT DISTINCT CONCEPT_NAME_2,
2096	'Drug',
2097	'Gemscript',
2098	'Supplier',
2099	NULL,
2100	CONCEPT_NAME_2,
2101	(
2102		SELECT latest_update
2103		FROM vocabulary
2104		WHERE vocabulary_id = 'Gemscript'
2105		) AS valid_start_date, -- TRUNC(SYSDATE)
2106	to_date('20991231', 'yyyymmdd') AS valid_end_date,
2107	NULL,
2108	'Gemscript'
2109FROM s_map;
2110
2111--Dose Form
2112INSERT INTO drug_concept_stage (
2113	CONCEPT_NAME,
2114	DOMAIN_ID,
2115	VOCABULARY_ID,
2116	CONCEPT_CLASS_ID,
2117	STANDARD_CONCEPT,
2118	CONCEPT_CODE,
2119	VALID_START_DATE,
2120	VALID_END_DATE,
2121	INVALID_REASON,
2122	SOURCE_CONCEPT_CLASS_ID
2123	)
2124SELECT DISTINCT CONCEPT_CODE_1,
2125	'Drug',
2126	'Gemscript',
2127	'Dose Form',
2128	NULL,
2129	CONCEPT_CODE_1,
2130	(
2131		SELECT latest_update
2132		FROM vocabulary
2133		WHERE vocabulary_id = 'Gemscript'
2134		) AS valid_start_date, -- TRUNC(SYSDATE)
2135	to_date('20991231', 'yyyymmdd') AS valid_end_date,
2136	NULL,
2137	'Gemscript'
2138FROM forms_mapping;
2139
2140--Brand Name
2141INSERT INTO drug_concept_stage (
2142	CONCEPT_NAME,
2143	DOMAIN_ID,
2144	VOCABULARY_ID,
2145	CONCEPT_CLASS_ID,
2146	STANDARD_CONCEPT,
2147	CONCEPT_CODE,
2148	VALID_START_DATE,
2149	VALID_END_DATE,
2150	INVALID_REASON,
2151	SOURCE_CONCEPT_CLASS_ID
2152	)
2153SELECT DISTINCT CONCEPT_NAME,
2154	'Drug',
2155	'Gemscript',
2156	'Brand Name',
2157	NULL,
2158	CONCEPT_NAME,
2159	(
2160		SELECT latest_update
2161		FROM vocabulary
2162		WHERE vocabulary_id = 'Gemscript'
2163		) AS valid_start_date, -- TRUNC(SYSDATE)
2164	to_date('20991231', 'yyyymmdd') AS valid_end_date,
2165	NULL,
2166	'Gemscript'
2167FROM b_map;
2168
2169INSERT INTO drug_concept_stage (
2170	CONCEPT_NAME,
2171	DOMAIN_ID,
2172	VOCABULARY_ID,
2173	CONCEPT_CLASS_ID,
2174	STANDARD_CONCEPT,
2175	CONCEPT_CODE,
2176	VALID_START_DATE,
2177	VALID_END_DATE,
2178	INVALID_REASON,
2179	SOURCE_CONCEPT_CLASS_ID
2180	)
2181SELECT DISTINCT CONCEPT_NAME,
2182	'Drug',
2183	'Gemscript',
2184	'Unit',
2185	NULL,
2186	CONCEPT_NAME,
2187	(
2188		SELECT latest_update
2189		FROM vocabulary
2190		WHERE vocabulary_id = 'Gemscript'
2191		) AS valid_start_date, -- TRUNC(SYSDATE)
2192	to_date('20991231', 'yyyymmdd') AS valid_end_date,
2193	NULL,
2194	'Gemscript'
2195FROM unit_list
2196;
2197drop table if exists INTERNAL_RELATIONSHIP_STAGE
2198;
2199CREATE TABLE INTERNAL_RELATIONSHIP_STAGE
2200(
2201   CONCEPT_CODE_1  VARCHAR(550 ),
2202   CONCEPT_CODE_2  VARCHAR(550 )
2203)
2204;
2205--internal_relationship_stage
2206INSERT INTO internal_relationship_stage
2207SELECT GEMSCRIPT_CODE,
2208	CONCEPT_NAME
2209FROM b_map
2210
2211UNION
2212
2213SELECT GEMSCRIPT_CODE,
2214	CONCEPT_NAME
2215FROM f_map_var
2216
2217UNION
2218
2219SELECT GEMSCRIPT_CODE,
2220	CONCEPT_NAME_2
2221FROM s_map
2222
2223UNION
2224
2225SELECT DISTINCT CONCEPT_CODE,
2226	ingredient_concept_code
2227FROM ds_all_tmp;
2228
2229
2230
2231TRUNCATE TABLE relationship_to_concept;
2232INSERT INTO relationship_to_concept (
2233	concept_code_1,
2234	concept_id_2,
2235	precedence,
2236	conversion_factor
2237	)
2238--existing concepts used in mappings
2239--bug in RxE, so take the first_value of concept_id_2
2240SELECT DISTINCT concept_code_1,
2241	first_value(concept_id_2) OVER (
2242		PARTITION BY concept_code_1,
2243		precedence,
2244		conversion_factor ORDER BY concept_id_2
2245		) AS concept_id_2,
2246	precedence,
2247	conversion_factor
2248FROM (
2249	SELECT CONCEPT_NAME AS concept_code_1,
2250		concept_id AS concept_id_2,
2251		1 AS precedence,
2252		1 AS conversion_factor
2253	FROM b_map
2254	
2255	UNION
2256	
2257	SELECT CONCEPT_CODE_1,
2258		concept_id_2,
2259		precedence,
2260		1
2261	FROM forms_mapping
2262	
2263	UNION
2264	
2265	SELECT CONCEPT_NAME_2,
2266		concept_id_2,
2267		1,
2268		1
2269	FROM s_map
2270	
2271	UNION
2272	
2273	SELECT INGREDIENT_CONCEPT_CODE,
2274		INGREDIENT_ID,
2275		1,
2276		1
2277	FROM ds_all_tmp
2278	WHERE INGREDIENT_ID IS NOT NULL
2279	
2280	UNION
2281	
2282	--add units from dm+D
2283	SELECT concept_code_1,
2284		concept_id_2,
2285		precedence,
2286		conversion_factor
2287	FROM unit_map
2288	) AS s0;
2289	--need to change the mapping from mcg to 0.001 mg
2290
2291UPDATE RELATIONSHIP_TO_CONCEPT
2292SET concept_id_2 = 8576,
2293	CONVERSION_FACTOR = 0.001
2294WHERE CONCEPT_CODE_1 = 'mcg';
2295
2296UPDATE relationship_to_concept
2297SET concept_id_2 = 19069149
2298WHERE concept_id_2 = 46274409;
2299
2300--mapping to U instead of iU
2301UPDATE relationship_to_concept
2302SET concept_id_2 = 8510
2303WHERE concept_id_2 = 8718;
2304
2305--RxE builder requires Ingredients used in relationships to be a standard
2306UPDATE drug_concept_stage
2307SET Standard_concept = 'S'
2308WHERE concept_class_id = 'Ingredient';
2309
2310--ds_stage shouldn't have empty dosage
2311DELETE
2312FROM ds_stage
2313WHERE drug_concept_code IN (
2314		SELECT drug_concept_code
2315		FROM ds_stage
2316		WHERE coalesce(amount_value, numerator_value, 0) = 0 -- needs to have at least one value, zeros don't count
2317			OR coalesce(amount_unit, numerator_unit) IS NULL -- needs to have at least one unit
2318			OR (
2319				amount_value IS NOT NULL
2320				AND amount_unit IS NULL
2321				) -- if there is an amount record, there must be a unit
2322			OR (
2323				coalesce(numerator_value, 0) != 0
2324				AND coalesce(numerator_unit, denominator_unit) IS NULL
2325				) -- if there is a concentration record there must be a unit in both numerator and denominator
2326			OR amount_unit = '%' -- % should be in the numerator_unit
2327		);
2328
2329DELETE
2330FROM internal_relationship_stage
2331WHERE concept_code_1 = '4915007'
2332	AND concept_code_2 = 'Chewing Gum';
2333
2334DROP TABLE IF EXISTS code_replace;
2335CREATE TABLE code_replace AS
2336SELECT 'OMOP' || nextval('code_seq') AS new_code,
2337	concept_code AS old_code
2338FROM (
2339	SELECT DISTINCT concept_code
2340	FROM drug_concept_stage
2341	WHERE concept_class_id IN (
2342			'Ingredient',
2343			'Brand Name',
2344			'Supplier',
2345			'Dose Form'
2346			)
2347		OR concept_code IN (
2348			SELECT drug_concept_code
2349			FROM pc_stage
2350			)
2351	) AS s0;
2352
2353UPDATE drug_concept_stage a
2354SET concept_code = b.new_code
2355FROM code_replace b
2356WHERE a.concept_code = b.old_code
2357	AND a.concept_class_id IN (
2358		'Ingredient',
2359		'Brand Name',
2360		'Supplier',
2361		'Dose Form'
2362		)
2363	OR concept_code IN (
2364		SELECT drug_concept_code
2365		FROM pc_stage
2366		);
2367
2368UPDATE relationship_to_concept a
2369SET concept_code_1 = b.new_code
2370FROM code_replace b
2371WHERE a.concept_code_1 = b.old_code;
2372
2373UPDATE ds_stage a
2374SET ingredient_concept_code = b.new_code
2375FROM code_replace b
2376WHERE a.ingredient_concept_code = b.old_code;
2377
2378UPDATE ds_stage a
2379SET drug_concept_code = b.new_code
2380FROM code_replace b
2381WHERE a.drug_concept_code = b.old_code;
2382
2383UPDATE internal_relationship_stage a
2384SET concept_code_1 = b.new_code
2385FROM code_replace b
2386WHERE a.concept_code_1 = b.old_code;
2387
2388UPDATE internal_relationship_stage a
2389SET concept_code_2 = b.new_code
2390FROM code_replace b
2391WHERE a.concept_code_2 = b.old_code;
2392
2393UPDATE pc_stage a
2394SET drug_concept_code = b.new_code
2395FROM code_replace b
2396WHERE a.drug_concept_code = b.old_code;
2397
2398--Marketed Product must have strength and dose form otherwise Supplier needs to be removed
2399DELETE
2400FROM internal_relationship_stage
2401WHERE (
2402		concept_code_1,
2403		concept_code_2
2404		) IN (
2405		SELECT irs.concept_code_1,
2406			irs.concept_code_2
2407		FROM internal_relationship_stage irs
2408		JOIN drug_concept_stage ON concept_code_2 = concept_code
2409			AND concept_class_id = 'Supplier'
2410		LEFT JOIN ds_stage ds ON drug_concept_code = irs.concept_code_1
2411		LEFT JOIN (
2412			SELECT concept_code_1
2413			FROM internal_relationship_stage
2414			JOIN drug_concept_stage ON concept_code_2 = concept_code
2415				AND concept_class_id = 'Dose Form'
2416			) rf ON rf.concept_code_1 = irs.concept_code_1
2417		WHERE ds.drug_concept_code IS NULL
2418			OR rf.concept_code_1 IS NULL
2419		);
2420
2421--some ds_stage update
2422UPDATE ds_stage a
2423SET DENOMINATOR_unit = (
2424		SELECT DISTINCT b.DENOMINATOR_unit
2425		FROM ds_stage b
2426		WHERE a.drug_CONCEPT_CODE = b.drug_CONCEPT_CODE
2427			AND a.DENOMINATOR_unit IS NULL
2428			AND b.DENOMINATOR_unit IS NOT NULL
2429		)
2430WHERE EXISTS (
2431		SELECT 1
2432		FROM ds_stage b
2433		WHERE a.drug_CONCEPT_CODE = b.drug_CONCEPT_CODE
2434			AND a.DENOMINATOR_unit IS NULL
2435			AND b.DENOMINATOR_unit IS NOT NULL
2436		);
2437
2438UPDATE ds_stage a
2439SET numerator_value = a.amount_value,
2440	numerator_unit = a.amount_unit,
2441	amount_value = NULL,
2442	amount_unit = NULL
2443WHERE a.denominator_unit IS NOT NULL
2444	AND numerator_unit IS NULL;
2445
2446--for further work with CNDV and then mapping creation roundabound, make copies of existing concept_stage and concept_relationship_stage
2447DROP TABLE IF EXISTS basic_concept_stage;
2448CREATE TABLE basic_concept_stage AS
2449SELECT *
2450FROM concept_stage;
2451
2452DROP TABLE IF EXISTS basic_con_rel_stage;
2453CREATE TABLE basic_con_rel_stage AS
2454SELECT *
2455FROM concept_relationship_stage;
2456
2457UPDATE ds_stage
2458SET DENOMINATOR_VALUE = 30
2459WHERE DRUG_CONCEPT_CODE = '4231007'
2460	AND DENOMINATOR_VALUE IS NULL;
2461
2462SELECT *
2463FROM drug_concept_stage
2464WHERE concept_name IN (
2465		'Eftrenonacog alfa 250unit powder / solvent for solution for injection vials',
2466		'Odefsey 200mg/25mg/25mg tablets (Gilead Sciences International Ltd)',
2467		'Insuman rapid 100iu/ml Injection (Aventis Pharma)',
2468		'Engerix b 10microgram/0.5ml Paediatric vaccination (GlaxoSmithKline UK Ltd)',
2469		'Ethyloestranol 2mg Tablet'
2470		);
2471
2472--clean up
2473--ds_stage was parsed wrongly by some reasons
2474UPDATE ds_stage
2475SET NUMERATOR_VALUE = 10,
2476	NUMERATOR_UNIT = 'mg'
2477WHERE DRUG_CONCEPT_CODE = '6912007'
2478	AND NUMERATOR_VALUE = 5
2479	AND NUMERATOR_UNIT = 'ml';
2480
2481UPDATE ds_stage
2482SET NUMERATOR_VALUE = 20,
2483	NUMERATOR_UNIT = 'mg'
2484WHERE DRUG_CONCEPT_CODE = '6916007'
2485	AND NUMERATOR_VALUE = 10
2486	AND NUMERATOR_UNIT = 'ml';
2487
2488UPDATE ds_stage
2489SET AMOUNT_VALUE = NULL,
2490	AMOUNT_UNIT = NULL,
2491	NUMERATOR_VALUE = 10000000,
2492	NUMERATOR_UNIT = 'unit',
2493	DENOMINATOR_VALUE = 1,
2494	DENOMINATOR_UNIT = 'ml'
2495WHERE DRUG_CONCEPT_CODE = '94291020'
2496	AND NUMERATOR_VALUE IS NULL
2497	AND NUMERATOR_UNIT IS NULL;
2498
2499UPDATE ds_stage
2500SET NUMERATOR_VALUE = 4,
2501	NUMERATOR_UNIT = 'mg'
2502WHERE DRUG_CONCEPT_CODE = '49537020'
2503	AND NUMERATOR_VALUE = 8
2504	AND NUMERATOR_UNIT = 'ml';
2505
2506UPDATE ds_stage
2507SET NUMERATOR_VALUE = 20,
2508	NUMERATOR_UNIT = 'mg'
2509WHERE DRUG_CONCEPT_CODE = '3252007'
2510	AND NUMERATOR_VALUE = 10
2511	AND NUMERATOR_UNIT = 'ml';
2512
2513UPDATE ds_stage
2514SET NUMERATOR_VALUE = 30,
2515	NUMERATOR_UNIT = 'mg'
2516WHERE DRUG_CONCEPT_CODE = '81443020'
2517	AND NUMERATOR_VALUE = 10
2518	AND NUMERATOR_UNIT = 'ml';
2519
2520UPDATE ds_stage
2521SET AMOUNT_VALUE = NULL,
2522	AMOUNT_UNIT = NULL,
2523	NUMERATOR_VALUE = 6000000,
2524	NUMERATOR_UNIT = 'unit',
2525	DENOMINATOR_VALUE = 1,
2526	DENOMINATOR_UNIT = 'ml'
2527WHERE DRUG_CONCEPT_CODE = '80015020'
2528	AND NUMERATOR_VALUE IS NULL
2529	AND NUMERATOR_UNIT IS NULL;
2530
2531UPDATE ds_stage
2532SET NUMERATOR_VALUE = 40,
2533	NUMERATOR_UNIT = 'mg'
2534WHERE DRUG_CONCEPT_CODE = '58170020'
2535	AND NUMERATOR_VALUE = 20
2536	AND NUMERATOR_UNIT = 'ml';
2537
2538UPDATE ds_stage
2539SET NUMERATOR_UNIT = 'mg'
2540WHERE DRUG_CONCEPT_CODE = '58166020'
2541	AND NUMERATOR_VALUE = 50
2542	AND NUMERATOR_UNIT = 'ml';
2543
2544UPDATE ds_stage
2545SET NUMERATOR_VALUE = 60,
2546	NUMERATOR_UNIT = 'mg'
2547WHERE DRUG_CONCEPT_CODE = '2113007'
2548	AND NUMERATOR_VALUE = 10
2549	AND NUMERATOR_UNIT = 'ml';
2550
2551UPDATE ds_stage
2552SET AMOUNT_VALUE = NULL,
2553	AMOUNT_UNIT = NULL,
2554	NUMERATOR_VALUE = 50,
2555	NUMERATOR_UNIT = 'mcg',
2556	DENOMINATOR_VALUE = 5,
2557	DENOMINATOR_UNIT = 'ml'
2558WHERE DRUG_CONCEPT_CODE = '67456020'
2559	AND NUMERATOR_VALUE IS NULL
2560	AND NUMERATOR_UNIT IS NULL;
2561
2562UPDATE ds_stage
2563SET NUMERATOR_VALUE = 10,
2564	NUMERATOR_UNIT = 'mg'
2565WHERE DRUG_CONCEPT_CODE = '58165020'
2566	AND NUMERATOR_VALUE = 20
2567	AND NUMERATOR_UNIT = 'ml';
2568
2569DELETE
2570FROM ds_stage
2571WHERE drug_concept_Code IN (
2572		SELECT drug_concept_Code
2573		FROM ds_stage
2574		JOIN thin_need_to_map ON gemscript_code = DRUG_CONCEPT_CODE
2575		WHERE lower(numerator_unit) IN ('ml')
2576			OR lower(amount_unit) IN ('ml')
2577		);
2578
2579DELETE
2580FROM ds_stage
2581WHERE DRUG_CONCEPT_CODE IN (
2582		SELECT DRUG_CONCEPT_CODE
2583		FROM ds_stage s
2584		JOIN drug_concept_stage a ON a.concept_code = s.drug_concept_code
2585			AND a.concept_class_id = 'Device'
2586		);
2587DELETE
2588FROM drug_concept_stage
2589WHERE concept_name = 'Syrup'
2590	AND concept_class_id = 'Ingredient';
2591
2592DELETE
2593FROM drug_concept_stage
2594WHERE concept_name = 'Stibium'
2595	AND concept_class_id = 'Brand Name';
2596
2597--Marketed Drugs without the dosage or Drug Form are not allowed
2598DELETE
2599FROM internal_relationship_stage
2600WHERE (
2601		concept_code_1,
2602		concept_code_2
2603		) IN (
2604		SELECT concept_code_1,
2605			concept_code_2
2606		FROM drug_concept_stage dcs
2607		JOIN (
2608			SELECT concept_code_1,
2609				concept_code_2
2610			FROM internal_relationship_stage
2611			JOIN drug_concept_stage ON concept_code_2 = concept_code
2612				AND concept_class_id = 'Supplier'
2613			LEFT JOIN ds_stage ON drug_concept_code = concept_code_1
2614			WHERE drug_concept_code IS NULL
2615			
2616			UNION
2617			
2618			SELECT concept_code_1,
2619				concept_code_2
2620			FROM internal_relationship_stage
2621			JOIN drug_concept_stage ON concept_code_2 = concept_code
2622				AND concept_class_id = 'Supplier'
2623			WHERE concept_code_1 NOT IN (
2624					SELECT concept_code_1
2625					FROM internal_relationship_stage
2626					JOIN drug_concept_stage ON concept_code_2 = concept_code
2627						AND concept_class_id = 'Dose Form'
2628					)
2629			) s ON s.concept_code_1 = dcs.concept_code
2630		WHERE dcs.concept_class_id = 'Drug Product'
2631			AND invalid_reason IS NULL
2632		);
2633
2634--not smart clean up
2635UPDATE RELATIONSHIP_TO_CONCEPT
2636SET concept_id_2 = 44012620
2637WHERE concept_id_2 = 43125877;
2638
2639UPDATE RELATIONSHIP_TO_CONCEPT
2640SET concept_id_2 = 1505346
2641WHERE concept_id_2 = 36878682;
2642
2643UPDATE RELATIONSHIP_TO_CONCEPT
2644SET concept_id_2 = 36879003
2645WHERE concept_id_2 = 21014145;
2646
2647UPDATE RELATIONSHIP_TO_CONCEPT
2648SET concept_id_2 = 44784806
2649WHERE concept_id_2 = 36878894;
2650
2651DELETE
2652FROM ds_stage
2653WHERE drug_concept_code = '63620020';
2654
2655UPDATE RELATIONSHIP_TO_CONCEPT
2656SET concept_id_2 = 21020188
2657WHERE concept_id_2 = 19131170;
2658
2659DELETE
2660FROM relationship_to_concept
2661WHERE concept_id_2 IN (
2662		SELECT concept_id_2
2663		FROM relationship_to_concept
2664		JOIN concept ON concept_id = concept_id_2
2665		WHERE invalid_reason IS NOT NULL
2666		);
2667
2668DELETE
2669FROM internal_relationship_stage
2670WHERE concept_code_1 IN (
2671		'74777020',
2672		'66641020',
2673		'74778020'
2674		)
2675	AND concept_code_2 IN (
2676		SELECT concept_code
2677		FROM drug_concept_stage
2678		WHERE concept_name = 'Colgate'
2679			AND concept_class_id = 'Brand Name'
2680		);
2681
2682DELETE
2683FROM ds_stage
2684WHERE DRUG_CONCEPT_CODE = '80989020'
2685	AND NUMERATOR_VALUE = 10.8;
2686
2687DELETE
2688FROM ds_stage
2689WHERE DRUG_CONCEPT_CODE = '98751020'
2690	AND NUMERATOR_VALUE = 30;
2691
2692UPDATE ds_stage
2693SET NUMERATOR_VALUE = 35.2
2694WHERE DRUG_CONCEPT_CODE = '80989020'
2695	AND NUMERATOR_VALUE = 24.4;
2696
2697UPDATE ds_stage
2698SET NUMERATOR_VALUE = 110
2699WHERE DRUG_CONCEPT_CODE = '98751020'
2700	AND NUMERATOR_VALUE = 80;
2701
2702DELETE
2703FROM drug_concept_stage
2704WHERE concept_name = 'Colgate'
2705	AND concept_class_id = 'Brand Name';
2706	
2707DELETE
2708FROM ds_stage
2709WHERE drug_concept_code IN (SELECT ds.drug_concept_code
2710                            FROM concept c
2711                              JOIN relationship_to_concept rc2 ON concept_id_2 = concept_id
2712                              JOIN internal_relationship_stage irs ON rc2.concept_code_1 = irs.concept_code_2
2713                              JOIN ds_stage ds ON ds.drug_concept_code = irs.concept_code_1
2714                              JOIN relationship_to_concept rtc
2715                                ON amount_unit = rtc.concept_code_1
2716                               AND rtc.concept_id_2 IN (9324, 9325)
2717	WHERE NOT (c.concept_name LIKE '%Tablet%' OR c.concept_name LIKE '%Capsule%' OR c.concept_name LIKE '%Lozenge%')
2718                            AND   c.concept_class_id = 'Dose Form'
2719                            AND   c.vocabulary_id LIKE 'Rx%');
2720
2721UPDATE relationship_to_concept
2722   SET concept_id_2 = 8587,
2723       conversion_factor = 1000
2724WHERE concept_code_1 = 'litre';
2725
2726DELETE
2727FROM drug_concept_stage
2728WHERE concept_class_id = 'Unit'
2729AND   NOT EXISTS (SELECT
2730                  FROM ds_stage
2731                  WHERE concept_code IN (amount_unit,numerator_unit,denominator_unit));
2732
2733UPDATE ds_stage
2734   SET amount_unit = 'g'
2735WHERE amount_unit = 'gm';
2736
2737DELETE
2738FROM drug_concept_stage
2739WHERE concept_name = 'BioCare'
2740AND   concept_class_id = 'Brand Name'
2741-- not a brand name, supplier;
2742;
2743update relationship_to_concept
2744set concept_id_2 = 21014279
2745where
2746	concept_code_1 in (select concept_code from drug_concept_stage where concept_name = 'BioCare')
2747;
2748--fix gonadotropins
2749DELETE
2750FROM ds_stage
2751WHERE ingredient_concept_code = ( select concept_code from drug_concept_stage where concept_class_id = 'Ingredient' and concept_name = 'Menotrophin');
2752
2753INSERT INTO internal_relationship_stage
2754SELECT concept_code_1,
2755       'Chorionic Gonadotropin'
2756FROM internal_relationship_stage
2757WHERE concept_code_2 = (select concept_code from drug_concept_stage where concept_class_id = 'Ingredient' and concept_name = 'Menotrophin')
2758UNION
2759SELECT concept_code_1,
2760       'Luteinizing Hormone'
2761FROM internal_relationship_stage
2762WHERE concept_code_2 = (select concept_code from drug_concept_stage where concept_class_id = 'Ingredient' and concept_name = 'Menotrophin');
2763
2764DELETE
2765FROM internal_relationship_stage
2766WHERE concept_code_2 = (select concept_code from drug_concept_stage where concept_class_id = 'Ingredient' and concept_name = 'Menotrophin');
2767
2768DELETE
2769FROM drug_concept_stage
2770WHERE concept_class_id = 'Ingredient' and concept_name IN ('Menotrophin');
2771
2772UPDATE relationship_to_concept
2773   SET concept_id_2 = (SELECT concept_id_2
2774                       FROM relationship_to_concept
2775                       WHERE concept_code_1 = (select concept_code from drug_concept_stage where concept_class_id = 'Ingredient' and concept_name = 'Luteinizing Hormone'))
2776WHERE concept_code_1 = (select concept_code from drug_concept_stage where concept_class_id = 'Ingredient' and concept_name = 'human menopausal gonadotrophin');
2777
2778--fix precise ingredients
2779UPDATE relationship_to_concept r
2780   SET concept_id_2 = (SELECT concept_id_2
2781                       FROM concept_relationship
2782                       WHERE concept_id_1 = r.concept_id_2
2783                       AND   relationship_id = 'Form of'
2784                       AND   invalid_reason IS NULL)
2785WHERE EXISTS (SELECT
2786              FROM concept c
2787              WHERE c.concept_id = r.concept_id_2
2788              AND   c.concept_class_id = 'Precise Ingredient');
2789
2790DROP TABLE if exists dsinsert;
2791
2792CREATE TABLE dsinsert 
2793AS
2794SELECT DISTINCT drug_concept_code,
2795       ingredient_concept_code,
2796       SUM(amount_value) OVER (PARTITION BY drug_concept_code,ingredient_concept_code) AS amount_value,
2797       amount_unit,
2798       SUM(numerator_value) OVER (PARTITION BY drug_concept_code,ingredient_concept_code) AS numerator_value,
2799       numerator_unit,
2800       denominator_value,
2801       denominator_unit,
2802       box_size
2803FROM ds_stage
2804WHERE (drug_concept_code,ingredient_concept_code) IN (SELECT drug_concept_code,
2805                                                             ingredient_concept_code
2806                                                      FROM ds_stage
2807                                                      GROUP BY drug_concept_code,
2808                                                               ingredient_concept_code
2809                                                      HAVING COUNT(1) > 1);
2810
2811DELETE
2812FROM ds_stage
2813WHERE (drug_concept_code,ingredient_concept_code) IN (SELECT drug_concept_code,
2814                                                             ingredient_concept_code
2815                                                      FROM ds_stage
2816                                                      GROUP BY drug_concept_code,
2817                                                               ingredient_concept_code
2818                                                      HAVING COUNT(1) > 1);
2819
2820INSERT INTO ds_stage
2821SELECT *
2822FROM dsinsert;
2823
2824UPDATE ds_stage
2825   SET ingredient_concept_code = ( select concept_code from drug_concept_stage where concept_class_id = 'Ingredient' and concept_name = 'sodium phosphate')
2826WHERE drug_concept_code = '80989020';
2827
2828UPDATE internal_relationship_stage
2829   SET concept_code_2 = ( select concept_code from drug_concept_stage where concept_class_id = 'Ingredient' and concept_name = 'sodium phosphate')
2830WHERE concept_code_1 = '80989020' and concept_code_2 in (select concept_code from drug_concept_stage where concept_class_id = 'Ingredient')
2831;
2832delete from internal_relationship_stage
2833where concept_code_1 = '04231007' and concept_code_2 in (select concept_code from drug_concept_stage where concept_class_id = 'Ingredient' and concept_name = 'Prilocaine')
2834;
2835delete from ds_stage
2836where drug_concept_code = '04231007' and ingredient_concept_code in (select concept_code from drug_concept_stage where concept_class_id = 'Ingredient' and concept_name = 'Prilocaine')
2837;
2838update ds_stage d
2839set
2840	numerator_value = d.numerator_value / 10,
2841	denominator_unit = 'g'
2842where
2843	denominator_unit = 'ml' and
2844	exists
2845		(
2846			select
2847			from ds_stage x
2848			where 
2849				x.drug_concept_code = d.drug_concept_code and
2850				denominator_unit = 'g'
2851		)
2852;
2853delete from internal_relationship_stage where concept_code_2 in (select concept_code from drug_concept_stage where concept_name = 'n')
2854;
2855delete from drug_concept_stage where concept_name = 'n'
2856;
2857--RxE duplicate
2858delete from internal_relationship_stage where concept_code_2 = (select concept_code from drug_concept_stage where concept_name = 'Novomix')
2859;
2860delete from drug_concept_stage where concept_name = 'Novomix'
2861;
2862--duplicating forms, delete non-preferrable
2863delete from internal_relationship_stage 
2864where
2865	concept_code_1 in (select concept_code_1 from drug_concept_stage, internal_relationship_stage where concept_code = concept_code_2 and concept_name in ('Injection','Cream') and concept_class_id = 'Dose Form') and
2866	concept_code_2 in (select concept_code from drug_concept_stage where concept_class_id = 'Dose Form' and concept_name not in ('Injection','Cream'))