 join thin_need_to_map on concept_code = thin_code
 and rownum =0
 ;
-WbImport -file=C:/work/manual_in_co_dose_2.txt
+WbImport -file=C:\Users\Dmitry\Desktop\manual_dosage_3.txt
          -type=text
          -table=MANUAL_IN_CO_DOSE_2
          -encoding="ISO-8859-15"

;
delete from ds_Stage where 0 in (numerator_value,amount_value,denominator_value)
;
--redefine packs later
/*
--remove Packs from ds_stage !!! need to find more


--;
--select * from manual_in_co_dose_1 left join thin_need_to_map on concept_code = gemscript_code where gemscript_code is not null
drop table manual_in_co_dose;
create table manual_in_co_dose as
select * from manual_in_co_dose_1
union
select * from manual_in_co_dose_2

WbImport -file=C:/work/thin_manual_260617.txt
         -type=text
         -table=THIN_MANUAL_260617
         -encoding="ISO-8859-15"
         -header=true
         -decode=false
         -dateFormat="yyyy-MM-dd"
         -timestampFormat="yyyy-MM-dd HH:mm:ss"
         -delimiter='\t'
         -decimal=.
         -fileColumns=THIN_CODE,THIN_NAME,GEMSCRIPT_NAME,DOMAIN_ID,INGREDIENT_ID,INGREDIENT_CONCEPT_CODE,DOSAGE,VOLUME
         -quoteCharEscaping=none
         -ignoreIdentityColumns=false
         -deleteTarget=false
         -continueOnError=false
         -batchSize=1000;
         
update thin_manual_260617 m set thin_code = (select t.gemscript_code from thin_need_to_map t where t.thin_code = m.thin_code)
where exists (select 1 from thin_need_to_map t where t.thin_code = m.thin_code)         


; 
drop table thin_manual_260617 
;
create table thin_manual_260617 ( THIN_CODE	varchar (250),THIN_NAME varchar (2500),	GEMSCRIPT_NAME varchar (2500),	DOMAIN_ID varchar (250),	ingredient_id int, 	INGREDIENT_CONCEPT_CODE varchar (250),	DOSAGE varchar (250),	VOLUME varchar (250)
)
;
alter table thin_manual_260617 rename column thin_code to gemscript_code
;
*/
;
update thin_manual_260617 set dosage = null where dosage = '0'
;
update thin_manual_260617 set dosage = replace (dosage, ',')
;
commit
;

delete from ds_all_tmp where concept_code in (select concept_code from manual_in_co_dose);
commit
;
delete from ds_all_tmp where concept_code in ( select gemscript_code from thin_manual_260617 where INGREDIENT_ID !=0 and gemscript_code not in (select concept_code from manual_in_co_dose))
;
commit
;
--packs after manual table in case if in manual table there will be packs
commit
;
insert into ds_all_tmp (DOSAGE,DRUG_COMP,CONCEPT_NAME,CONCEPT_CODE,INGREDIENT_CONCEPT_CODE,INGREDIENT_CONCEPT_NAME,VOLUME)
select DOSAGE, '', CONCEPT_NAME, CONCEPT_CODE, INGREDIENT_CONCEPT_CODE, INGREDIENT_CONCEPT_CODE, volume from manual_in_co_dose
;
--there were two steps (and already hard to figure out what's included in which table, so two steps)
insert into ds_all_tmp (DOSAGE,DRUG_COMP,CONCEPT_NAME,CONCEPT_CODE,INGREDIENT_CONCEPT_CODE,INGREDIENT_CONCEPT_NAME,VOLUME)
select DOSAGE, '', GEMSCRIPT_NAME, gemscript_CODE, INGREDIENT_CONCEPT_CODE, INGREDIENT_CONCEPT_CODE, volume from thin_manual_260617 where gemscript_code not in (select CONCEPT_CODE from manual_in_co_dose) and INGREDIENT_ID !=0
;
--set the proper domain_id based on the manual table
update  thin_need_to_map t set domain_id = (select distinct domain_id from thin_manual_260617 m where t.gemscript_code = m.gemscript_code)
where exists (select 1 from thin_manual_260617 m where t.gemscript_code = m.gemscript_code)
;
commit
;
--packs after manual table in case if in manual table there will be packs
delete from ds_all_tmp where concept_code in (Select drug_concept_code from pc_stage)
;
--then merge it with ds_all_tmp, for now temporary decision - make dosages NULL to avoid bug
--remove ' ' inside the dosage to make the same as it was before in dmd
