migrationIsante.sql

drop function if exists IsNumeric;
CREATE FUNCTION IsNumeric (val varchar(255)) RETURNS tinyint 
 RETURN val REGEXP '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$';

DROP FUNCTION if exists FindNumericValue;
DELIMITER $$
 
CREATE FUNCTION FindNumericValue(val VARCHAR(255)) RETURNS VARCHAR(255)
    DETERMINISTIC
BEGIN
		DECLARE idx INT DEFAULT 0;
		IF ISNULL(val) THEN RETURN NULL; END IF;

		IF LENGTH(val) = 0 THEN RETURN ""; END IF;
 SET idx = LENGTH(val);
		WHILE idx > 0 DO
			IF IsNumeric(SUBSTRING(val,idx,1)) = 0 THEN
				SET val = REPLACE(val,SUBSTRING(val,idx,1),"");
				SET idx = LENGTH(val)+1;
			END IF;
				SET idx = idx - 1;
		END WHILE;
			RETURN val;
END
$$
DELIMITER ;

DROP FUNCTION if exists `formatDate`;
DELIMITER $$
CREATE FUNCTION `formatDate`( dateYy Varchar(10),dateMm Varchar(10),dateDd Varchar(10) ) RETURNS DATE
BEGIN

  IF (FindNumericValue(dateYy)<0 or dateYy='' or dateYy is null)
  THEN 
    RETURN null;
  END IF;
  
  IF (dateYy='00')
  THEN 
     set dateYy='2000';
  END IF;
  
  IF(length(dateYy)<=2) 
  THEN 
   set dateYy=concat('20',FindNumericValue(dateYy));
   END IF;
  
  IF(dateMm is null or dateMm='XX' or dateMm='' or dateMm>12 or dateMm<1)
  THEN 
   set dateMm='01';
   END IF;
 
  IF(dateDd is null or dateDd='XX' or dateDd='' or dateDd>31 or dateDd<1)
  THEN 
   set dateDd='01';
   END IF;
 
 IF(length(dateDd)<=2) 
  THEN 
   set dateDd=concat('0',FindNumericValue(dateDd));
   END IF;
 
 IF((dateMm='01' or dateMm='03' or dateMm='05' or dateMm='07' or dateMm='08' or dateMm='10' or dateMm='12') and dateDd>31)
 THEN 
  set dateDd='31';
  END IF;
 
  IF((dateMm='04' or dateMm='06' or dateMm='09' or dateMm='11') and dateDd>30)
 THEN 
  set dateDd='30';
  END IF;
  
 IF((dateMm='02') and mod(dateYy,4)>0 and dateDd>28)
 THEN 
  set dateDd='28';
  END IF;
  
   IF((dateMm='02') and mod(dateYy,4)=0 and dateDd>29)
 THEN 
  set dateDd='29';
  END IF;
 
  RETURN date_format(concat(trim(dateYy),'-',trim(dateMm),'-',trim(dateDd)),'%y-%m-%d');
END$$
DELIMITER ;
DROP FUNCTION if exists `digits`;
DELIMITER $$

CREATE FUNCTION `digits`( str longtext ) RETURNS char(32) CHARSET utf8
BEGIN
  DECLARE i, len SMALLINT DEFAULT 1;
  DECLARE ret VARCHAR(255) DEFAULT '';
  DECLARE c CHAR(1);
  DECLARE pos SMALLINT;
  DECLARE after_p CHAR(100);
  IF str IS NULL
  THEN 
    RETURN "0.0";
  END IF;
  SET len = CHAR_LENGTH( str );
  l:REPEAT
    BEGIN
      SET c = MID( str, i, 1 );
      IF c BETWEEN '0' AND '9' THEN 
        SET ret=CONCAT(ret,c);
      ELSEIF c = '.' OR c = ',' THEN
		IF c = '.' THEN
			SET pos=INSTR(str, '.' );
            SET after_p=MID(str,pos,pos+2);
			
			IF FindNumericValue(ret) = '' THEN
            SET ret=FindNumericValue(after_p);
			ELSE 
			 SET ret=CONCAT(FindNumericValue(ret),'.',FindNumericValue(after_p));
            END IF;
			LEAVE l;
		ELSEIF c = ',' THEN 
			SET pos=INSTR(str, ',');
            SET after_p=MID(str,pos,pos+2);
			IF FindNumericValue(ret) = '' THEN
            SET ret=FindNumericValue(after_p);
			ELSE 
			 SET ret=CONCAT(FindNumericValue(ret),'.',FindNumericValue(after_p));            
            END IF;
			LEAVE l;
		END IF;
      END IF;
      
      SET i = i + 1;
      
    END;
  UNTIL i > len END REPEAT;
  
    IF ret=""
  THEN 
    RETURN "0.0";
  END IF;
  
  RETURN ret;
END$$
DELIMITER ;

drop procedure if exists migrationIsante;
DELIMITER $$ 
CREATE PROCEDURE migrationIsante()
BEGIN

  DECLARE cnt INT;
  DECLARE maxObsId INT;

create table if not exists migration_log(id int(11) primary key auto_increment,prcodedure varchar(25),starttime datetime,endtime datetime,maxid int);

 select count(*) into cnt from migration_log where prcodedure = 'encounter'  and endtime is not null;
 if(cnt=0) then 
   SET SQL_SAFE_UPDATES = 0;
/* Clean openmrs database before import */
   call cleanOpenmrs();
   select 1  as CleanOpenmrs;
/* patient registration migration */
   SET SQL_SAFE_UPDATES = 0;
   insert into migration_log(prcodedure,starttime) values('demographic',now());
   call patientDemographics();
   select 2 as Demographic;
   update migration_log set endtime=now() where prcodedure = 'demographic';
/* visit and Encounter migration*/
   SET SQL_SAFE_UPDATES = 0;
   insert into migration_log(prcodedure,starttime) values('encounter',now());
   call encounter_Migration();
   select 3 as Encounter;
   update migration_log set endtime=now() where prcodedure = 'encounter';
   update migration_log set maxid=(select max(obs_id) from obs) where prcodedure = 'encounter';
 end if;  

  /* migration of Ordonance */
  select count(*) into cnt from migration_log where prcodedure = 'ordonance' and endtime is not null;
  if(cnt=0) then 
   select maxid into maxObsId from migration_log where prcodedure = 'encounter';  
   
   delete from migration_log where prcodedure in ('ordonance');
   insert into migration_log(prcodedure,starttime) values('ordonance',now());
   
   update obs o  set o.obs_group_id=null where o.obs_id>maxObsId; 
   delete o from obs o where o.obs_id>maxObsId;
   
   SET SQL_SAFE_UPDATES = 0;
/* ordonance migration */ 
   call ordonanceMigration();
   select 7 as Ordonance;
   SET SQL_SAFE_UPDATES = 0;
   update migration_log set endtime=now() where prcodedure = 'ordonance';
   update migration_log set maxid=(select max(obs_id) from obs) where prcodedure = 'ordonance';
 end if; 
   
 
/* migration of Lab*/ 
 select count(*) into cnt from migration_log where prcodedure = 'lab' and endtime is not null;
 if(cnt=0) then   

   select maxid into maxObsId from migration_log where prcodedure = 'ordonance';  
   delete from migration_log where prcodedure in ('lab');
   insert into migration_log(prcodedure,starttime) values('lab',now());
   update obs o  set o.obs_group_id=null where o.obs_id>maxObsId; 
   delete o from obs o where o.obs_id>maxObsId;
   
   SET SQL_SAFE_UPDATES = 0;
   call labsMigration();
   SET SQL_SAFE_UPDATES = 0;
   select 6 as Lab;
   update migration_log set endtime=now() where prcodedure = 'lab';
   update migration_log set maxid=(select max(obs_id) from obs) where prcodedure = 'lab';
 end if; 
 
 
  /* migration of Old Lab*/
  select count(*) into cnt from migration_log where prcodedure = 'Old lab' and endtime is not null;
 if(cnt=0) then
   select maxid into maxObsId from migration_log where prcodedure = 'lab'; 
   delete from migration_log where prcodedure in ('Old lab');
   insert into migration_log(prcodedure,starttime) values('Old lab',now());   
   update obs o  set o.obs_group_id=null where o.obs_id>maxObsId; 
   delete o from obs o where o.obs_id>maxObsId;
   
   SET SQL_SAFE_UPDATES = 0;
   call labsMigrationOldData();
   select 6 as LabOLD;
   SET SQL_SAFE_UPDATES = 0;
   update migration_log set endtime=now() where prcodedure = 'Old lab';
   update migration_log set maxid=(select max(obs_id) from obs) where prcodedure = 'Old lab';
 end if;
 
 
 
 
 
 
  /* migration of discontinuation*/
  select count(*) into cnt from migration_log where prcodedure = 'discontinuation' and endtime is not null;
  if(cnt=0) then   
   select maxid into maxObsId from migration_log where prcodedure = 'Old lab'; 
   delete from migration_log where prcodedure in ('discontinuation');
   insert into migration_log(prcodedure,starttime) values('discontinuation',now()); 
   update obs o  set o.obs_group_id=null where o.obs_id>maxObsId; 
   delete o from obs o where o.obs_id>maxObsId;     
/* discontinutation */   
   call discontinuationMigration();
   SET SQL_SAFE_UPDATES = 0;
   select 8 as discontinuation;
   update migration_log set endtime=now() where prcodedure = 'discontinuation';
   update migration_log set maxid=(select max(obs_id) from obs) where prcodedure = 'discontinuation';
 end if; 
 
 

   /* migration of adult visit */ 
 select count(*) into cnt from migration_log where prcodedure in ('adult visit') and endtime is not null;
 if(cnt=0) then 
   select maxid into maxObsId from migration_log where prcodedure = 'discontinuation'; 
   delete from migration_log where prcodedure in ('adult visit');
   insert into migration_log(prcodedure,starttime) values('adult visit',now());
   update obs o  set o.obs_group_id=null where o.obs_id>maxObsId; 
   delete o from obs o where o.obs_id>maxObsId;    
 
   SET SQL_SAFE_UPDATES = 0;
   call adult_visit_Migration();
   select 4 as Adult;
   update migration_log set endtime=now() where prcodedure = 'adult visit';
   update migration_log set maxid=(select max(obs_id) from obs) where prcodedure = 'adult visit';

 end if;
 
 
 
 
 
 
 
 
   /* migration of pediatric visit */ 
 select count(*) into cnt from migration_log where prcodedure in ('pediatric visit') and endtime is not null;
 if(cnt=0) then 
   select maxid into maxObsId from migration_log where prcodedure = 'adult visit'; 
   delete from migration_log where prcodedure in ('pediatric visit');
   insert into migration_log(prcodedure,starttime) values('pediatric visit',now());
   update obs o  set o.obs_group_id=null where o.obs_id>maxObsId; 
   delete o from obs o where o.obs_id>maxObsId;    
 
   SET SQL_SAFE_UPDATES = 0;
   call pediatric_visit_Migration();
   SET SQL_SAFE_UPDATES = 0;
   select 5 as Pediatric;
   update migration_log set endtime=now() where prcodedure = 'pediatric visit';
   update migration_log set maxid=(select max(obs_id) from obs) where prcodedure = 'pediatric visit';
 end if; 
 
 
 
    /* migration of adherence */ 
 select count(*) into cnt from migration_log where prcodedure in ('adherence') and endtime is not null;
 if(cnt=0) then 
   select maxid into maxObsId from migration_log where prcodedure = 'pediatric visit'; 
   delete from migration_log where prcodedure in ('adherence');
   insert into migration_log(prcodedure,starttime) values('adherence',now());
   update obs o  set o.obs_group_id=null where o.obs_id>maxObsId; 
   delete o from obs o where o.obs_id>maxObsId;    

   SET SQL_SAFE_UPDATES = 0;
   call  adherenceMigration();
   SET SQL_SAFE_UPDATES = 0;
   select 5 as Pediatric;
   update migration_log set endtime=now() where prcodedure = 'adherence';
   update migration_log set maxid=(select max(obs_id) from obs) where prcodedure = 'adherence';
end if; 
 
 
 
     /* migration of home Visit */ 
 select count(*) into cnt from migration_log where prcodedure in ('homeVisit') and endtime is not null;
 if(cnt=0) then 
   select maxid into maxObsId from migration_log where prcodedure = 'adherence'; 
   delete from migration_log where prcodedure in ('homeVisit');
   insert into migration_log(prcodedure,starttime) values('homeVisit',now());
   update obs o  set o.obs_group_id=null where o.obs_id>maxObsId; 
   delete o from obs o where o.obs_id>maxObsId;    

   SET SQL_SAFE_UPDATES = 0;
   call  homeVisitMigration();
   SET SQL_SAFE_UPDATES = 0;
   select 5 as Pediatric;
   update migration_log set endtime=now() where prcodedure = 'homeVisit';
   update migration_log set maxid=(select max(obs_id) from obs) where prcodedure = 'homeVisit';
end if;
 
 
 
 
      /* migration of obgyn */ 
 select count(*) into cnt from migration_log where prcodedure in ('obgyn') and endtime is not null;
 if(cnt=0) then 
   select maxid into maxObsId from migration_log where prcodedure = 'homeVisit'; 
   delete from migration_log where prcodedure in ('obgyn');
   insert into migration_log(prcodedure,starttime) values('obgyn',now());
   update obs o  set o.obs_group_id=null where o.obs_id>maxObsId; 
   delete o from obs o where o.obs_id>maxObsId;    

   SET SQL_SAFE_UPDATES = 0;
   call obgynMigration();
   SET SQL_SAFE_UPDATES = 0;
   select 11 as obgyn;
   update migration_log set endtime=now() where prcodedure = 'obgyn';
   update migration_log set maxid=(select max(obs_id) from obs) where prcodedure = 'obgyn';
end if;
 
 
/* SOINS SANTE PRIMAIRE ADULTE */ 
 select count(*) into cnt from migration_log where prcodedure in ('sspAdult') and endtime is not null;
 if(cnt=0) then 
   select maxid into maxObsId from migration_log where prcodedure = 'obgyn'; 
   delete from migration_log where prcodedure in ('sspAdult');
   insert into migration_log(prcodedure,starttime) values('sspAdult',now());
   update obs o  set o.obs_group_id=null where o.obs_id>maxObsId; 
   delete o from obs o where o.obs_id>maxObsId;    

   SET SQL_SAFE_UPDATES = 0;
   call sspAdultMigration();
   SET SQL_SAFE_UPDATES = 0;
   select 5 as sspAdult;
   update migration_log set endtime=now() where prcodedure = 'sspAdult';
   update migration_log set maxid=(select max(obs_id) from obs) where prcodedure = 'sspAdult';
end if;


/* SOINS SANTE PRIMAIRE PEDIATRIC */ 
 select count(*) into cnt from migration_log where prcodedure in ('sspPediatric') and endtime is not null;
 if(cnt=0) then 
   select maxid into maxObsId from migration_log where prcodedure = 'sspAdult'; 
   delete from migration_log where prcodedure in ('sspPediatric');
   insert into migration_log(prcodedure,starttime) values('sspPediatric',now());
   update obs o  set o.obs_group_id=null where o.obs_id>maxObsId; 
   delete o from obs o where o.obs_id>maxObsId;    

   SET SQL_SAFE_UPDATES = 0;
   call sspPediatricMigration();
   SET SQL_SAFE_UPDATES = 0;
   select 5 as sspPediatric;
   update migration_log set endtime=now() where prcodedure = 'sspPediatric';
   update migration_log set maxid=(select max(obs_id) from obs) where prcodedure = 'sspPediatric';
end if;



/* SOINS SANTE PRIMAIRE VACCINATION */ 
 select count(*) into cnt from migration_log where prcodedure in ('vaccination') and endtime is not null;
 if(cnt=0) then 
   select maxid into maxObsId from migration_log where prcodedure = 'sspPediatric'; 
   delete from migration_log where prcodedure in ('vaccination');
   insert into migration_log(prcodedure,starttime) values('vaccination',now());
   update obs o  set o.obs_group_id=null where o.obs_id>maxObsId; 
   delete o from obs o where o.obs_id>maxObsId;    

   SET SQL_SAFE_UPDATES = 0;
   call vaccination();
   SET SQL_SAFE_UPDATES = 0;
   select 5 as vaccination;
   update migration_log set endtime=now() where prcodedure = 'vaccination';
   update migration_log set maxid=(select max(obs_id) from obs) where prcodedure = 'vaccination';
end if;   
   

 /* migration for next VisitDate*/  
INSERT INTO obs(person_id,concept_id,encounter_id,obs_datetime,location_id,value_datetime,creator,date_created,uuid)
SELECT DISTINCT e.patient_id,5096,e.encounter_id,e.encounter_datetime,e.location_id,
formatDate(FindNumericValue(c.nxtVisitYy),FindNumericValue(c.nxtVisitMm),FindNumericValue(c.nxtVisitDd)),1,e.date_created,UUID()
FROM itech.encounter c, encounter e
WHERE e.uuid = c.encGuid and formatDate(FindNumericValue(c.nxtVisitYy),FindNumericValue(c.nxtVisitMm),FindNumericValue(c.nxtVisitDd)) is not null;

select 1 as nextv;
/*Statut de la fiche*/
/* complete/Incomplete */
INSERT INTO obs(person_id,concept_id,encounter_id,obs_datetime,location_id,value_coded,creator,date_created,uuid)
SELECT DISTINCT e.patient_id,163340,e.encounter_id,e.encounter_datetime,e.location_id,
    CASE WHEN encStatus=5 or encStatus=7 THEN 163339
	     WHEN encStatus=1 or encStatus=3 or encStatus=0 THEN 1267	 
		ELSE NULL
	END,1,e.date_created,UUID()
FROM itech.encounter c, encounter e
WHERE e.uuid = c.encGuid and encStatus in (0,1,3,5,7);

select 2 as nextv;
/* La fiche doit être passée en revue par la personne responsable de la qualité des données. */
INSERT INTO obs(person_id,concept_id,encounter_id,obs_datetime,location_id,value_coded,creator,date_created,uuid)
SELECT DISTINCT e.patient_id,163341,e.encounter_id,e.encounter_datetime,e.location_id,
    CASE WHEN encStatus=3 or encStatus=7 THEN 1065	 
		ELSE NULL
	END,1,e.date_created,UUID()
FROM itech.encounter c, encounter e
WHERE e.uuid = c.encGuid and encStatus in (3,7);

/*Evaluation et plan */
select 3 as nextv;


/*visit suivi */
INSERT INTO obs(person_id,concept_id,encounter_id,obs_datetime,location_id,value_text,creator,date_created,uuid)
SELECT DISTINCT e.patient_id,159395,e.encounter_id,e.encounter_datetime,e.location_id,
CASE WHEN v.followupComments<>'' then substring(v.followupComments,1000)
ELSE NULL
END,1,e.date_created,UUID()
FROM itech.encounter c, encounter e, itech.followupTreatment v 
WHERE e.uuid = c.encGuid and c.patientID = v.patientID and c.seqNum = v.seqNum and 
c.sitecode = v.sitecode and date_format(date(e.encounter_datetime),'%y-%m-%d')= formatDate(v.visitDateYy,v.visitDateMm,v.visitDateDd) AND 
v.followupComments<>'';

 select 13 as comments;
 
 /*  COUNSELING   */
INSERT INTO obs(person_id,concept_id,encounter_id,obs_datetime,location_id,value_text,creator,date_created,uuid)
SELECT DISTINCT e.patient_id,159395,e.encounter_id,e.encounter_datetime,e.location_id,
CASE WHEN v.obstaclesRemarks<>'' then trim(v.obstaclesRemarks)
ELSE NULL
END,1,e.date_created,UUID()
FROM itech.encounter c, encounter e, itech.comprehension v 
WHERE e.uuid = c.encGuid and c.patientID = v.patientID and c.seqNum = v.seqNum and 
c.sitecode = v.sitecode and date_format(date(e.encounter_datetime),'%y-%m-%d')= formatDate(v.visitDateYy,v.visitDateMm,v.visitDateDd) AND 
v.obstaclesRemarks<>'';
 
 select 13 as commentCounselling;

/* premiere visit  */
INSERT INTO obs(person_id,concept_id,encounter_id,obs_datetime,location_id,value_text,creator,date_created,uuid)
SELECT DISTINCT e.patient_id,159395,e.encounter_id,e.encounter_datetime,e.location_id,
CASE WHEN v.assessmentPlan<>'' then substring(v.assessmentPlan,1000)
ELSE NULL
END,1,e.date_created,UUID()
FROM itech.encounter c, encounter e, itech.vitals v 
WHERE e.uuid = c.encGuid and c.patientID = v.patientID and c.seqNum = v.seqNum and 
c.sitecode = v.sitecode and date_format(date(e.encounter_datetime),'%y-%m-%d')= concat(v.visitDateYy,'-',v.visitDateMm,'-',v.visitDateDd) AND 
v.assessmentPlan<>'';

 select 14 as comments;

 /* migration for From Autor*/  
INSERT INTO obs(person_id,concept_id,encounter_id,obs_datetime,location_id,value_text,creator,date_created,uuid)
SELECT DISTINCT e.patient_id,1473,e.encounter_id,e.encounter_datetime,e.location_id,
    CASE WHEN ifnull(formAuthor,'')<>'' and  ifnull(formAuthor2,'')<>'' then concat(formAuthor,' / ',formAuthor2)
	     WHEN ifnull(formAuthor,'')<>'' and  ifnull(formAuthor2,'')='' then formAuthor
		 WHEN ifnull(formAuthor,'')='' and  ifnull(formAuthor2,'')<>'' then formAuthor2
		ELSE NULL
	END,1,e.date_created,UUID()
FROM itech.encounter c, encounter e
WHERE e.uuid = c.encGuid ;

END$$