Rich eForm Migration

Owned by Marc Dumontier
Feb 21, 2017
8 min read

This page describes the migration of Indivica Rich eForms to Oscar eForms


Preface

Some Oscar sites have a different version of the eForm tables called richeform. This page describes how to convert the richeform to standard Oscar eforms.

Warning: Do NOT do this unless you are very comfortable with MySQL commands and procedures.  It is very easy to destroy your existing Oscar database with a single line.

 Document Version History

This document is copyright by Earl Wertheimer 2014 © under the Creative Commons Attribution-Share Alike 3.0 Unported License

 

 

The major difference is that the richeform does not have the .html code as part of the database. So in addition to converting the database data, the .html code has to be imported into the Oscar eform tables.

The different tables are:

richeform
richeform_instances
richeform_values

 

The following SQL scripts and procedure will help in the analysis and conversion of the richeform to eform.

Note: It contains many commands used to understand, analyse and restart the conversion procedure.

 

If you are converting from Indivica to Oscar, then the destination eform tables should be empty. That makes the process much easier.

 

I created a temporary table (ew_reform_html) to contain the eform html that has to be converted.

 

-- analyse existing data in richeform select r.rfid, rin.rfuuid, count(*), r.form_name, r.file_name, r.status
from richeform_instances rin
left join richeform r on rin.rfuuid = r.uuid
group by rin.rfuuid ;   
   
-- with x-ref (contains source html)
select r.rfid, rin.rfuuid, count(*), r.form_name, r.file_name, r.status, ew.rfid
from richeform_instances rin
left join richeform r on rin.rfuuid = r.uuid
left join ew_reform_html ew on rin.rfuuid = ew.uuid
group by rin.rfuuid ;   
   
   
select v.instanceid, count(*) from richeform_values v
left join richeform_instances i on v.instanceid = i.instanceid ;
 
select v.instanceid, v.demographic_no, count(*) from richeform_values v
left join richeform_instances i on v.instanceid = i.instanceid
group by v.instanceid, v.demographic_no ;
 
select val.instanceid, val.var_name, left(val.var_value,40) 
from richeform_values val
left join richeform_instances i on val.instanceid = i.instanceid
where i.rfuuid = ( select uuid from richeform where rfid = 61 )
order by val.instanceid, val.var_name
limit 100 ;
 
 
--eform info
select fid, form_name, file_name, subject, status, patient_independent from eform ;
 
select ed.fid, count(*), ef.form_name, ef.file_name, length(ef.form_html), ef.subject, ef.status, ef.patient_independent  
from eform_data ed
left join eform ef on ed.fid = ef.fid
group by ed.fid ;
 
 
 
--create table to hold richeform html
delete from ew_reform_html ;
alter table ew_reform_html auto_increment = 1 ;
 
drop table ew_reform_html ;
 
create table ew_reform_html (
id         int(8) UNIQUE PRIMARY KEY auto_increment,
uuid       varchar(50),
rfid	     int(11),
form_html  mediumtext,
used       tinyint ) ;


 
--html in /var/lib/tomcat6/webapps/oscar/richeform/html
 
--WinSCP html files to Oscar /home/earlw/import
                        
                          

ls -l
copy screen to clipboard, column mode,
 
-- run client rich eform loading.  loads html into ew_reform_html
set sql_mode = 'NO_BACKSLASH_ESCAPES' ;
load data local infile '/home/earlw/import/07faae12-6baa-41ed-b8a1-edc80f34c336.html' into table ew_reform_html fields terminated by 'tsnbf' lines terminated by 'tsnbf' ( form_html ) set uuid = '07faae12-6baa-41ed-b8a1-edc80f34c336' ;
load data local infile '/home/earlw/import/richtextletter-orig-nofax.txt' into table ew_reform_html fields terminated by 'tsnbf' lines terminated by 'tsnbf' ( form_html ) set uuid = 'richtextletter-orig-nofax' ;
 
-- flag records that are used
update ew_reform_html set used = 1
where uuid in ( select distinct rfuuid from richeform_instances ) ;
 
-- keep richeform.rfid
update ew_reform_html set rfid = ( select rfid from richeform where ew_reform_html.uuid = richeform.uuid ) ;
 
select ew.id, ew.rfid, ew.uuid, ew.used from ew_reform_html ew join richeform r on ew.uuid = r.uuid ;
 
 
 
-- check x-ref
select r.rfid, r.form_name, r.file_name, r.uuid, ew.uuid from richeform r left join ew_reform_html ew on r.uuid = ew.uuid ;
 
-- backup eform data
create table bu_eform like eform ;
insert into bu_eform select * from eform ;
create table bu_eform_data like eform_data ;
insert into bu_eform_data select * from eform_data ;
create table bu_eform_values like eform_values ;
insert into bu_eform_values select * from eform_values ;
 
mysqldump --add-drop-table -uroot -p<password> oscar_mcmaster > oscar_20130127.sql
 
 
-- build eform from richeform and ew_reform_html
insert into eform (
form_name, file_name, subject, form_date, form_time, form_creator, status, form_html, patient_independent )
( select 
r.form_name, r.file_name, r.subject, r.form_date, r.form_time, r.form_creator, r.status, ew.form_html, r.patient_independent
from richeform r
left join ew_reform_html ew on r.uuid = ew.uuid
where r.status = 1 
and ew.used = 1 ) ;
 
 
 
-- restore eform data (for restarts & testing)
delete from eform ;
alter table eform auto_increment = 1 ;
delete from eform_data ;
alter table eform_data auto_increment = 1 ;
delete from eform_values ;
alter table eform_values auto_increment = 1 ;
 
 
call pr_richeform2eform;
 
-- restore (if needed)
insert into eform select * from bu_eform ;
insert into eform_data select * from bu_eform_data ;
insert into eform_values select * from bu_eform_values ;
 
 
select count(*) from richeform ;
select count(*) from richeform_instances ;
select count(*) from richeform_values ;
 
select count(*) from eform ;
select count(*) from eform_data ;
select count(*) from eform_values ;
 
select uuid from richeform where form_name = 'Rich Text Letter' and status = 1 ;
select instanceid, demographic_no from richeform_instances where rfuuid IN ( select uuid from richeform where form_name = 'Rich Text Letter' and status = 1 ) ;
select instanceid from richeform_instances where demographic_no = 4154 and rfuuid IN ( select uuid from richeformwhere form_name = 'CPP' and status = 1 ) ;
select * from richeform_values where instanceid = ( select instanceid from richeform_instances where rfuuid = ( select uuid from richeform where form_name = 'CHF Flowsheet' and status = 1 ) ) ;
 
select fid from eform where form_name = 'Rich Text Letter' and status = 1 ;
select fdid, form_date from eform_data where demographic_no = 4154 and fid = ( select fid from eform where form_name = 'Surface Anatomy' and status = 1 ) ;
select * from eform_values where fdid = ( select DISTINCT fdid from eform_data where demographic_no = 4154 and fid = ( select fid from eform where form_name = 'Surface Anatomy' and status = 1 ) ) ;

 Stored procedure: 

DROP PROCEDURE IF EXISTS `pr_richeform2eform` ;
delimiter $$
CREATE PROCEDURE `pr_richeform2eform`() 
BEGIN 
-- transfer data from Indivica richeform to Oscar eform

-- call pr_richeform2eform;
DECLARE v_done INT DEFAULT 0; 
DECLARE v_MaxID, v_count, v_recs, v_eform_fid, v_eform_data_fdid INT ;
-- declare data vars
DECLARE d_instanceid, d_demographic_no, d_status, d_patient_independent INT;
DECLARE d_form_date     date ;
DECLARE d_form_time     time ;
DECLARE d_var_name      varchar(30);
DECLARE d_rfuuid        varchar(50);
DECLARE d_subject, d_form_provider, d_form_name     varchar(255);
DECLARE d_var_value     text;
-- declare temp vars
DECLARE t_instanceid, t_last_instanceid, t_demographic_no, t_rfid  INT;
DECLARE t_file_name, t_form_creator             varchar(255);
DECLARE t_form_html, t_Str, t_Fld               mediumtext ;
DECLARE t_Loc, t_Pos, t_Beg, t_End, t_Len       INT ;
DECLARE cur1 CURSOR FOR 
   SELECT val.instanceid, val.var_name, val.var_value, val.demographic_no,
          ins.rfuuid, ins.demographic_no, ins.status, ins.subject, ins.form_date, ins.form_time, 
          ins.patient_independent, ins.form_provider, ins.form_name
     FROM richeform_values val
-- lookup richeform_instances
     JOIN richeform_instances ins on val.instanceid = ins.instanceid
 ORDER BY ins.instanceid ;
-- Surface Anatomy, D# 4154     
-- WHERE ins.instanceid = 2600
-- CPP, D# 4154     
-- WHERE ins.instanceid = 2601
-- WHERE ins.demographic_no = 4763
-- lookup x-ref for html
-- LEFT JOIN ew_reform_html ew on in.rfuuid = ew.uuid
-- lookup warning types
-- left join richeform_values val on in.instanceid = val.instanceid
    
DECLARE CONTINUE HANDLER FOR NOT FOUND 
SET v_done = 1; 
-- get count
SELECT max(instanceid) INTO v_MaxID FROM richeform_values ;
SET v_count = 99999 ; 
SET t_last_instanceid = -1 ;
  
OPEN cur1; 
    read_loop: 
    LOOP FETCH cur1 INTO
        d_instanceid, d_var_name, d_var_value, d_demographic_no,
        d_rfuuid, t_demographic_no, d_status, d_subject, d_form_date, d_form_time, 
        d_patient_independent, d_form_provider, d_form_name ;
        
    IF v_done THEN LEAVE read_loop; END IF; 
    set v_count = v_count + 1 ;  
    IF v_count > 1000 THEN
        SELECT concat( ' Working on InstanceID ', d_instanceid, ' of ', v_MaxID ) ;
        SET v_count = 0 ;
    END IF;
-- new instanceid.  Do header processing    
    IF t_last_instanceid <> d_instanceid THEN
        SET t_last_instanceid = d_instanceid ;
-- lookup richeform.  MUST exist!
        SELECT rfid, file_name, form_creator, count(*) INTO t_rfid, t_file_name, t_form_creator, v_recs 
            FROM richeform WHERE uuid = d_rfuuid ;
-- lookup ew_reform_html for form_html.  MUST exist!
        SELECT form_html INTO t_form_html FROM ew_reform_html where uuid = d_rfuuid ;  
      
-- check if eform exists.  If not, create it
        SELECT fid, count(*) INTO v_eform_fid, v_recs FROM eform WHERE form_name = d_form_name ;
        IF v_recs = 0 THEN
            INSERT INTO eform (
                form_name, file_name, subject, form_date, form_time, form_creator, 
                status, form_html, patient_independent )
            VALUES (
                d_form_name, t_file_name, d_subject, d_form_date, d_form_time, t_form_creator, 
                d_status, t_form_html, d_patient_independent );
        
            SELECT LAST_INSERT_ID() INTO v_eform_fid ;
        END IF; 
-- update src="${oscar_image_path}
        WHILE LOCATE( 'src="${oscar_image_path}', t_form_html ) > 0 DO
            SET t_Loc = LOCATE( 'src="${oscar_image_path}', t_form_html ) ;
            IF t_Loc > 0 then 
                SET t_Pos = t_Loc + 4 ;
                SET t_Str = '../eform/displayImage.do?imagefile=' ;
                SET t_form_html = concat( left( t_form_html, t_Pos) , t_Str, mid( t_Form_html, t_Loc + 24 ) ) ;
            END IF;    
        END WHILE;
        
-- update <form action=
        SET t_Loc = LOCATE( '<form ', t_form_html ) ;
        IF t_Loc > 0 THEN
            SET t_Pos = t_Loc + 5 ;
            SET t_Str = CONCAT( 'action="../eform/addEForm.do?efmid=', v_eform_fid, '&efmdemographic_no=', d_demographic_no, 
                                '&efmprovider_no=', d_form_provider, '&eform_link=null" ' ) ;
            SET t_form_html = concat( left( t_form_html, t_Pos) , t_Str, mid( t_Form_html, t_Pos + 1 ) ) ;
        END IF;    
-- write eform_data 
        INSERT INTO eform_data (
            fid, form_name, subject, demographic_no, status, form_date, form_time, 
            form_provider, form_data, patient_independent )
        VALUES (
            v_eform_fid, d_form_name, d_subject, d_demographic_no, d_status, d_form_date, d_form_time, 
            d_form_provider, t_form_html, d_patient_independent );
            
        SELECT LAST_INSERT_ID() INTO v_eform_data_fdid ;
        
-- TESTING:  SELECT concat( 'html=', t_form_html ) ;
        
    END IF;
-- special fields:  subject, SubmitButton, uuid
-- find var_name in html and insert var_value    
    SET t_Fld = CONCAT( 'name="', d_var_name, '"' ) ;
    SET t_Loc = LOCATE(  t_Fld, t_form_html );
    
    IF d_var_name = 'SubmitButton' THEN SET t_Loc = 0 ;  END IF ;
    
    IF t_Loc > 0 THEN
        SET t_Str = SUBSTRING_INDEX( LEFT( t_form_html,t_Loc), '<', -1 );
        SET t_Beg = t_Loc - LENGTH(t_Str) ;
        SET t_End = LOCATE( '>', MID(t_form_html,t_Loc)) + t_Loc - 1 ; 
        SET t_Len = t_End - t_Beg + 1 ;
        
-- TEST: SELECT concat( 't_Fld=', t_Fld, ' t_Str=', t_Str, ' t_Beg=', t_Beg, ' t_End=', t_End, ' t_Loc=', t_Loc, ' HTML=[', MID(t_form_html,t_Beg,t_Len), ']' ) ;
            
-- go back to < and find out type of field ( <textarea,     
        IF LOCATE( '<textarea', MID(t_form_html,t_Beg,t_Len)) > 0 THEN
-- put value after >
            SET t_form_html = concat( left( t_form_html, t_End) , d_var_value, mid( t_Form_html, t_End + 1 ) ) ;
        ELSE
            
            IF LOCATE( '<input', MID(t_form_html,t_Beg,t_Len)) > 0 THEN
            
-- if type="checkbox" before closing >
                IF LOCATE( 'type="checkbox"', MID(t_form_html,t_Beg,t_Len) ) > 0 THEN
-- move to end of name="d_var_name"
                    SET t_Pos = t_Loc + 7 + length( d_var_name ) ;
                    SET t_form_html = concat( left( t_form_html, t_Pos) , 'checked ', mid( t_Form_html, t_Pos + 1 ) ) ;
                ELSE
                    SET t_Pos = t_Loc + 7 + length( d_var_name ) ;
                    SET t_form_html = concat( left( t_form_html, t_Pos) , 'value="', d_var_value, '" ', mid( t_Form_html, t_Pos + 1 ) ) ;
                END IF ;    
            END IF ;
        END IF ;
        
        UPDATE eform_data SET form_data = t_form_html WHERE fdid = v_eform_data_fdid ;    
            
        INSERT INTO eform_values (
            fdid, fid, demographic_no, var_name, var_value )
        VALUES (
            v_eform_data_fdid, v_eform_fid, d_demographic_no, d_var_name, d_var_value ) ;
            
    END IF ;
  
END LOOP; 
CLOSE cur1; 
END
$$
delimiter ;