Changes between Version 1 and Version 2 of i2b2 LCBRU Customisations


Ignore:
Timestamp:
02/28/14 10:19:50 (11 years ago)
Author:
Richard Bramley
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • i2b2 LCBRU Customisations

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