Changes between Version 5 and Version 6 of i2b2 LCBRU Customisations


Ignore:
Timestamp:
12/10/15 09:55:07 (10 years ago)
Author:
Richard Bramley
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • i2b2 LCBRU Customisations

    v5 v6  
    4848The procedures that come with i2b2 are broken, but are used by the i2b2 mapped importer.  Ideally I will write the process into the mapped importer.
    4949
    50 === Customisation:
    51 
    52 {{{
    53 #!sql
    54 CREATE PROCEDURE [dbo].[INSERT_EID_MAP_FROMTEMP_RB] (@tempEidTableName VARCHAR(500),  @upload_id INT,
    55 @errorMsg VARCHAR(MAX) = NULL OUTPUT)
    56 AS
    57 BEGIN
    58  declare @existingEncounterNum varchar(32);
    59  declare  @maxEncounterNum int;
    60  declare @deleteDuplicateSql nvarchar(MAX);
    61 
    62 declare  @sql_stmt  nvarchar(MAX);
    63  
    64 declare  @disEncounterId varchar(200);
    65 declare  @disEncounterIdSource varchar(50);
    66 declare  @disPatientMapId varchar(200);
    67 declare  @disPatientMapIdSource varchar(50);
    68 
    69 
    70  BEGIN TRY
    71 
    72 --Delete duplicate rows with same encounter and patient combination
    73 set @deleteDuplicateSql = 'with deleteTempDup as (SELECT *,ROW_NUMBER() OVER
    74 ( PARTITION BY encounter_map_id,encounter_map_id_source,encounter_id,encounter_id_source
    75   ORDER BY encounter_map_id,encounter_map_id_source,encounter_id,encounter_id_source ) AS RNUM FROM ' + @tempEidTableName +')
    76 delete  from deleteTempDup where rnum>1';
    77 exec sp_executesql @deleteDuplicateSql;
    78 
    79 
    80 --set IDENTITY_INSERT encounter_mapping ON;
    81  -- get max encounter num
    82  select @maxEncounterNum = isnull(max(encounter_num),0) from encounter_mapping with (UPDLOCK);
    83  -- cursor which iterates distinct encounter_id,encounter_id_source compination
    84  SELECT @sql_stmt = 'DECLARE my_cur INSENSITIVE CURSOR FOR ' +
    85               ' SELECT distinct encounter_id,encounter_id_source,patient_map_id,patient_map_id_source from ' +  @tempEidTableName  ;
    86 EXEC sp_executesql @sql_stmt;
    87 
    88 OPEN my_cur;
    89 
    90 FETCH NEXT FROM my_cur into @disEncounterId, @disEncounterIdSource,@disPatientMapId, @disPatientMapIdSource ;
    91  WHILE @@FETCH_STATUS = 0
    92  
    93  BEGIN
    94  BEGIN TRANSACTION
    95   if  @disEncounterIdSource = 'HIVE'   
    96    begin
    97      SET @existingEncounterNum = NULL
    98     --check if hive number exist, if so assign that number to reset of map_id's within that pid
    99     select @existingEncounterNum = encounter_num from encounter_mapping where encounter_num = @disEncounterId and encounter_ide_source = 'HIVE';
    100    
    101    if @existingEncounterNum is not NULL
    102    begin
    103         set @sql_stmt =  ' update ' + @tempEidTableName  + ' set encounter_num = encounter_id, process_status_flag = ''P'' ' +
    104         ' where encounter_id = @pdisEncounterId and not exists (select 1 from encounter_mapping em where em.encounter_ide = encounter_map_id ' +
    105         ' and em.encounter_ide_source = encounter_map_id_source)';
    106         EXEC sp_executesql @sql_stmt,N'@pdisEncounterId nvarchar(200)', @pdisEncounterId = @disEncounterId;
    107     end
    108     else
    109     begin
    110         -- generate new patient_num i.e. take max(_num) + 1
    111         if @maxEncounterNum < @disEncounterId
    112         begin
    113             set @maxEncounterNum = @disEncounterId;
    114         end;
    115         set @sql_stmt = ' update ' + @tempEidTableName + ' set encounter_num = encounter_id, process_status_flag = ''P'' where ' +
    116         ' encounter_id =  @pdisEncounterId and encounter_id_source = ''HIVE'' and not exists (select 1 from encounter_mapping em where em.encounter_ide = encounter_map_id ' +
    117         ' and em.encounter_ide_source = encounter_map_id_source)';
    118         EXEC sp_executesql @sql_stmt, N'@pdisEncounterId nvarchar(200)',@pdisEncounterId=@disEncounterId ;
    119      end;   
    120     -- print ' HIVE ';
    121  end
    122  else
    123  begin
    124      SET @existingEncounterNum = NULL
    125        select @existingEncounterNum = encounter_num  from encounter_mapping where encounter_ide = @disEncounterId and
    126         encounter_ide_source = @disEncounterIdSource ;
    127 
    128        
    129        if @existingEncounterNum is not  NULL
    130        begin
    131             set @sql_stmt =  ' update ' + @tempEidTableName + ' set encounter_num = @pexistingEncounterNum, process_status_flag = ''P'' ' +
    132             ' where encounter_id = @pdisEncounterId and not exists (select 1 from encounter_mapping em where em.encounter_ide = encounter_map_id ' +
    133             ' and em.encounter_ide_source = encounter_map_id_source)' ;
    134         EXEC sp_executesql @sql_stmt,N'@pexistingEncounterNum int, @pdisEncounterId nvarchar(200)',@pexistingEncounterNum=@existingEncounterNum ,
    135           @pdisEncounterId=@disEncounterId;
    136        end
    137        else
    138        begin
    139 
    140             set @maxEncounterNum = @maxEncounterNum + 1 ;
    141              set @sql_stmt =   ' insert into ' + @tempEidTableName + ' (encounter_map_id,encounter_map_id_source,patient_map_id,patient_map_id_source,encounter_id,encounter_id_source,encounter_num,process_status_flag
    142              ,encounter_map_id_status,update_date,download_date,import_date,sourcesystem_cd)
    143              values(@pmaxEncounterNum1,''HIVE'',@pdisPatientMapId, @pdisPatientMapIdSource,@pmaxEncounterNum2,''HIVE'',@pmaxEncounterNum3,''P'',''A'',getdate(),getdate(),getdate(),''edu.harvard.i2b2.crc'')' ;
    144             EXEC sp_executesql @sql_stmt, N'@pmaxEncounterNum1 int,@pmaxEncounterNum2 int, @pmaxEncounterNum3 int, @pdisPatientMapId varchar(200), @pdisPatientMapIdSource varchar(50)',
    145             @pmaxEncounterNum1=@maxEncounterNum,@pmaxEncounterNum2=@maxEncounterNum,@pmaxEncounterNum3=@maxEncounterNum,@pdisPatientMapId=@disPatientMapId, @pdisPatientMapIdSource=@disPatientMapIdSource;
    146            
    147                          set @sql_stmt =   ' update ' + @tempEidTableName +' set encounter_num = @pmaxEncounterNum , process_status_flag = ''P'' ' + 
    148              ' where encounter_id = @pdisEncounterId and  not exists (select 1 from ' +
    149              ' encounter_mapping em where em.encounter_ide = encounter_map_id ' +
    150              ' and em.encounter_ide_source = encounter_map_id_source)' ;
    151             EXEC sp_executesql @sql_stmt,N'@pmaxEncounterNum int,@pdisEncounterId nvarchar(200)',@pmaxEncounterNum=@maxEncounterNum , @pdisEncounterId=@disEncounterId;
    152            
    153        end  ;
    154    
    155       -- print ' NOT HIVE ';
    156  end ;
    157 commit;
    158 FETCH NEXT FROM my_cur into @disEncounterId, @disEncounterIdSource,@disPatientMapId, @disPatientMapIdSource ;
    159 END ;
    160 CLOSE my_cur
    161 DEALLOCATE my_cur
    162  BEGIN TRANSACTION
    163 -- do the mapping update if the update date is old and the encounter_id_source is HIVE
    164    set @sql_stmt = ' update encounter_mapping set encounter_num = temp.encounter_id,
    165         patient_ide   =   temp.patient_map_id ,
    166         patient_ide_source  =   temp.patient_map_id_source ,
    167         encounter_ide_status    = temp.encounter_map_id_status  ,
    168         update_date = temp.update_date,
    169         download_date  = temp.download_date ,
    170                 import_date = getdate() ,
    171         sourcesystem_cd  = temp.sourcesystem_cd ,
    172                 upload_id = ' + convert(nvarchar,@upload_id) + '
    173                 from encounter_mapping em 
    174          inner join ' + @tempEidTableName + ' temp
    175                     on em.encounter_ide = temp.encounter_map_id and
    176                         em.encounter_ide_source = temp.encounter_map_id_source
    177         where  temp.encounter_id_source = ''HIVE'' and temp.process_status_flag is null  and
    178         isnull(em.update_date,0)<= isnull(temp.update_date,0) ' ;
    179 EXEC sp_executesql @sql_stmt;
    180 
    181 -- insert new encounters into encounter_mapping
    182 set @sql_stmt =  ' insert into encounter_mapping (encounter_ide,encounter_ide_source,patient_ide,patient_ide_source,encounter_ide_status,encounter_num,update_date,download_date,import_date,sourcesystem_cd,upload_id)
    183     select encounter_map_id,encounter_map_id_source,patient_map_id,patient_map_id_source,encounter_map_id_status,encounter_num,update_date,download_date,getdate(),sourcesystem_cd,' + convert(nvarchar,@upload_id) + ' from ' + @tempEidTableName + ' 
    184     where process_status_flag = ''P'' ' ;
    185 EXEC sp_executesql @sql_stmt;
    186 commit;
    187 END TRY
    188 
    189 BEGIN CATCH
    190    if @@TRANCOUNT > 0
    191    begin
    192       ROLLBACK
    193    end
    194    begin try
    195    DEALLOCATE my_cur
    196    end try
    197    begin catch
    198    end catch 
    199    declare @errMsg nvarchar(4000), @errSeverity int
    200    select @errMsg = ERROR_MESSAGE(), @errSeverity = ERROR_SEVERITY();
    201    set @errorMsg = @errMsg;
    202    RAISERROR(@errMsg,@errSeverity,1);
    203  END CATCH
    204 
    205 end;
    206 
    207 GO
    208 }}}
    209 
    210 {{{
    211 #!sql
    212 CREATE PROCEDURE  [dbo].[INSERT_PID_MAP_FROMTEMP_RB] (@tempPatientMapTableName VARCHAR(500), @upload_id INT,
    213    @errorMsg varchar(max)  = NULL OUTPUT)
    214 AS
    215 
    216 BEGIN
    217   declare @deleteDuplicateSql nvarchar(MAX),
    218     @insertSql nvarchar(MAX);
    219 
    220 declare  @existingPatientNum nvarchar(32);
    221 declare @maxPatientNum int;
    222 declare @disPatientId nvarchar(200);
    223 declare @disPatientIdSource nvarchar(50);
    224 declare @sql nvarchar(MAX);
    225 BEGIN TRY
    226    
    227 
    228         --Delete duplicate rows with same patient combination
    229         set @deleteDuplicateSql = 'with deleteTempDup as (SELECT *,ROW_NUMBER() OVER
    230 ( PARTITION BY patient_map_id,patient_map_id_source,patient_id,patient_id_source
    231   ORDER BY patient_map_id,patient_map_id_source,patient_id,patient_id_source ) AS RNUM FROM ' + @tempPatientMapTableName +')
    232 delete  from deleteTempDup where rnum>1';
    233 
    234 exec sp_executesql @deleteDuplicateSql;
    235          
    236 --get max patient_num from patient_mapping table
    237 select @maxPatientNum = isnull(max(patient_num),0) from patient_mapping with (UPDLOCK);
    238 -- create cursor to iterate distinct event_id,event_id_source combination
    239 SELECT @sql = 'DECLARE my_cur INSENSITIVE CURSOR FOR ' +
    240               ' SELECT distinct patient_id,patient_id_source from ' +  @tempPatientMapTableName  ;
    241 EXEC sp_executesql @sql
    242 
    243 OPEN my_cur
    244 
    245 FETCH NEXT FROM my_cur into @disPatientId, @disPatientIdSource ;
    246  WHILE @@FETCH_STATUS = 0
    247 
    248  BEGIN
    249 BEGIN TRANSACTION
    250    --print @disPatientId + ' ' + @disPatientIdSource     
    251   if  @disPatientIdSource = 'HIVE' 
    252   begin
    253      SET @existingPatientNum = NULL
    254      --check if hive number exist, if so assign that number to reset of map_id's within that pid
    255      select @existingPatientNum  = patient_num  from patient_mapping where patient_num = @disPatientId and patient_ide_source = 'HIVE';
    256    
    257      if @existingPatientNum IS NOT NULL
    258      begin
    259          --print 'not null'
    260         set @sql = ' update ' + @tempPatientMapTableName + ' set patient_num = patient_id, process_status_flag = ''P'' ' +
    261         ' where patient_id =  @pdisPatientId   and not exists (select 1 from patient_mapping pm where pm.patient_ide = patient_map_id ' +
    262         ' and pm.patient_ide_source = patient_map_id_source)' ; 
    263         EXEC sp_executesql @sql, N'@pdisPatientId nvarchar(200)',  @pdisPatientId = @disPatientId;
    264         --EXEC sp_executesql @sql;
    265         --select @disPatientId;
    266      end
    267      else
    268      begin
    269          --print 'null not exist HIVE' + @disPatientId
    270         -- generate new patient_num i.e. take max(patient_num) + 1
    271         if @maxPatientNum < @disPatientId
    272         begin
    273            set @maxPatientNum = @disPatientId;
    274         end;
    275         set @sql = ' update ' + @tempPatientMapTableName +' set patient_num = patient_id, process_status_flag = ''P'' where ' +
    276         ' patient_id = @pdisPatientId and patient_id_source = ''HIVE'' and not exists (select 1 from patient_mapping pm where pm.patient_ide = patient_map_id ' +
    277         ' and pm.patient_ide_source = patient_map_id_source)';
    278         EXEC sp_executesql @sql, N'@pdisPatientId nvarchar(200)', @pdisPatientId=@disPatientId;
    279       end;
    280    -- test if record fectched
    281    --print ' HIVE ';
    282   end;
    283  else
    284  begin
    285      SET @existingPatientNum = NULL
    286        --print 'Not HIVE'
    287        select @existingPatientNum = patient_num   from patient_mapping where patient_ide = @disPatientId and
    288         patient_ide_source = @disPatientIdSource ;
    289 
    290                 --print 'existing PNum' + @existingPatientNum
    291        
    292        -- test if record fetched.
    293      
    294        if @existingPatientNum is not NULL
    295        begin
    296                   --print 'About to update Temp with P'
    297           set @sql = ' update ' + @tempPatientMapTableName +' set patient_num = @pexistingPatientNum , process_status_flag = ''P'' ' +
    298             ' where patient_id = @pdisPatientId and not exists (select 1 from patient_mapping pm where pm.patient_ide = patient_map_id ' +
    299             ' and pm.patient_ide_source = patient_map_id_source)' ;
    300             --print @sql
    301             --print '@pexistingPatientNum = ' + @existingPatientNum
    302             --print '@pdisPatientId = ' + @disPatientId
    303             EXEC sp_executesql @sql,N'@pexistingPatientNum int, @pdisPatientId nvarchar(200)',@pexistingPatientNum=@existingPatientNum,@pdisPatientId=@disPatientId;
    304        end
    305        else
    306        begin
    307               -- print ' NOT HIVE and not present ' + @disPatientId;
    308              set @maxPatientNum = @maxPatientNum + 1 ;
    309              set @sql = 'insert into ' + @tempPatientMapTableName + ' (patient_map_id,patient_map_id_source,patient_id,patient_id_source,patient_num,process_status_flag ' +
    310              ',patient_map_id_status,update_date,download_date,import_date,sourcesystem_cd ) ' + 
    311              ' values(@pmaxPatientNum1,''HIVE'',@pmaxPatientNum2,''HIVE'',@pmaxPatientNum3,''P'',''A'',getdate(),getdate(),getdate(),''edu.harvard.i2b2.crc'')' ; 
    312              EXEC sp_executesql  @sql ,N'@pmaxPatientNum1 int,@pmaxPatientNum2 int,
    313                         @pmaxPatientNum3 int',@pmaxPatientNum1 = @maxPatientNum ,@pmaxPatientNum2 = @maxPatientNum,@pmaxPatientNum3 = @maxPatientNum;
    314             set @sql =  'update ' + @tempPatientMapTableName + ' set patient_num =  @pmaxPatientNum , process_status_flag = ''P'' ' + 
    315              ' where patient_id = @pdisPatientId and  not exists (select 1 from ' +
    316             ' patient_mapping pm where pm.patient_ide = patient_map_id ' +
    317             ' and pm.patient_ide_source = patient_map_id_source)' ;
    318              EXEC sp_executesql @sql,N'@pmaxPatientNum int,@pdisPatientId nvarchar(200)',@pmaxPatientNum = @maxPatientNum, @pdisPatientId=@disPatientId  ;
    319           end;
    320        
    321  end ;
    322 commit;
    323  FETCH NEXT FROM my_cur into @disPatientId, @disPatientIdSource ;
    324 END
    325 
    326 CLOSE my_cur
    327 DEALLOCATE my_cur
    328 BEGIN TRANSACTION
    329 
    330 -- do the mapping update if the update date is old
    331    set @sql = ' update patient_mapping set patient_num = temp.patient_id,
    332         patient_ide_status      = temp.patient_map_id_status  ,
    333         update_date = temp.update_date,
    334         download_date  = temp.download_date ,
    335                 import_date = getdate() ,
    336         sourcesystem_cd  = temp.sourcesystem_cd ,
    337                 upload_id = ' + convert(nvarchar,@upload_id) + '
    338                 from patient_mapping pm
    339         inner join ' + @tempPatientMapTableName + ' temp
    340         on  pm.patient_ide = temp.patient_map_id and pm.patient_ide_source = temp.patient_map_id_source
    341         where temp.patient_id_source = ''HIVE'' and temp.process_status_flag is null  and isnull(temp.update_date,0) >= isnull(pm.update_date,0)';
    342 
    343 EXEC sp_executesql @sql;
    344 
    345 set @sql = ' insert into patient_mapping (patient_ide,patient_ide_source,patient_ide_status,patient_num,update_date,download_date,import_date,sourcesystem_cd,upload_id) ' +
    346     ' select patient_map_id,patient_map_id_source,patient_map_id_status,patient_num,update_date,download_date,getdate(),sourcesystem_cd,' + convert(nvarchar,@upload_id) + ' from '+@tempPatientMapTableName+ 
    347     ' where process_status_flag = ''P'' ' ;
    348 EXEC sp_executesql @sql;
    349 
    350 commit;
    351  
    352  END TRY
    353 BEGIN CATCH
    354    if @@TRANCOUNT > 0
    355   begin
    356      ROLLBACK
    357    end
    358    
    359    begin try
    360    DEALLOCATE my_cur
    361    end try
    362    begin catch
    363    end catch
    364    declare @errMsg nvarchar(4000), @errSeverity int
    365    select @errMsg = ERROR_MESSAGE(), @errSeverity = ERROR_SEVERITY();
    366    set @errorMsg = @errMsg;
    367    RAISERROR(@errMsg,@errSeverity,1);
    368  END CATCH
    369 END;
    370 
    371 GO
    372 }}}
     50The procedures are named the same as the original i2b2 stored procedures, but with the suffix `_LCBRU`.
    37351
    37452[[BackLinks]]