IF EXISTS (SELECT name FROM sysobjects WHERE  name  = 'MigrateMedicalCon2Problems'
AND type = 'P')
DROP PROCEDURE MigrateMedicalCon2Problems
GO
Create Procedure MigrateMedicalCon2Problems AS
Declare
        @Genid                numeric(20,0),
        @Onset_Date        datetime,
        @Resolved_Date        datetime,
        @Patient_ID        varchar(20),
        @Comments        varchar (4000),
        @Problem_Desc        varchar(250),
        @Creator        varchar(50),
        @PROBLEM_NAME        varchar(100),
        @IS_CODED        numeric(1,0),
        @PRB_TYPE_ID        numeric (20,0),
        @STATUS        numeric(1,0),
        @ENCOUNTER_ID        numeric (20,0),
        @PROBLEM_CODE        varchar (100),
        @IS_HISTORY        numeric (1,0),
        @MODIFIED_BY        varchar(50),
        @LAST_MODIFIED_DATE        datetime,
        @Created_Date        datetime,
        @Group_ID        numeric (20,0),
        @UNC_PROBLEM_NAME        varchar(100)
Begin
--For inserting the table EMRPATIENTPROBLEMS
SET @IS_CODED = 0;
SELECT @Genid = EZEMRXID FROM EMRIDS WHERE PROPERTY_NAME = 'PROBLEM_ID'
SET @Genid = @Genid + 1
DECLARE Problem_Cursor CURSOR FAST_FORWARD FOR
                SELECT EM.PATIENT_ID, EM.ENCOUNTER_ID, EM.MEDICAL_CODE, 
                      EM.COMMENTS, EM.STATUS, EM.RESOLVED_DATE, 
                      case EM.MEDICAL_TYPE when 'H' then 0 
                          WHEN 'C' then 1 END  as MEDICAL_TYPE , EM.USER_LOGIN,
EM.CREATED_DATE, 
                      EM.LAST_UPDATED_USER, EM.LAST_UPDATED_DATE, EL.GROUP_ID
FROM         EMRMedicalConditions EM INNER JOIN
                      EMRPatientsMaster EPM ON EM.PATIENT_ID = EPM.PATIENT_ID INNER
JOIN
                      EMRLocationMaster EL ON EPM.LOCATION_ID = EL.LOCATION_ID;
        OPEN Problem_Cursor
        FETCH NEXT FROM Problem_Cursor INTO @PATIENT_ID,@ENCOUNTER_ID,@PROBLEM_NAME,@Comments,
                        @STATUS,@Resolved_Date,@IS_HISTORY,@Creator,@Created_Date,@MODIFIED_BY,@LAST_MODIFIED_DATE,@Group_ID;        
        WHILE @@FETCH_Status = 0 
          BEGIN                
                If (@PROBLEM_NAME is null )
                  Begin
                   Set @Problem_Desc = @PROBLEM_NAME;
                  End 
                Else
                    Begin
                      Set @PROBLEM_CODE = SUBSTRING(@PROBLEM_NAME, 1, 3); -- Getting
problem code
                      Set @Problem_Desc = @PROBLEM_CODE + '-' + @PROBLEM_NAME;
                    End 
                
                Insert Into
EMRPATIENTPROBLEMS(Problem_ID,Onset_Date,Resolved_Date,Patient_ID,Comments,
                 Problem_Desc,Creator,PROBLEM_NAME,IS_CODED,PRB_TYPE_ID,STATUS,ENCOUNTER_ID,PROBLEM_CODE,
                 IS_HISTORY,MODIFIED_BY,LAST_MODIFIED_DATE,Created_Date)
                 Values(@Genid,@Onset_Date,@Resolved_Date,@PATIENT_ID,@Comments,@Problem_Desc,@Creator,
                        @PROBLEM_NAME,@IS_CODED,@PRB_TYPE_ID,@STATUS,@ENCOUNTER_ID,@PROBLEM_CODE,@IS_HISTORY,
                        @MODIFIED_BY,@LAST_MODIFIED_DATE,@Created_Date);
                Update EMRIDS Set EZEMRXID = @Genid Where PROPERTY_NAME = 'PROBLEM_ID'
                SET @Genid = @Genid + 1
                FETCH NEXT FROM Problem_Cursor INTO
@PATIENT_ID,@ENCOUNTER_ID,@PROBLEM_NAME,@Comments,
                        @STATUS,@Resolved_Date,@IS_HISTORY,@Creator,@Created_Date,@MODIFIED_BY,@LAST_MODIFIED_DATE,@Group_ID;        
          END
        CLOSE Problem_Cursor
    DEALLOCATE Problem_Cursor

--For inserting the table EMRUNCODEDPROBLEMS

SELECT @Genid = EZEMRXID FROM EMRIDS WHERE PROPERTY_NAME = 'UNCODED_PROBLEM_ID'
SET @Genid = @Genid + 1
DECLARE UncodedProblem_Cursor CURSOR FAST_FORWARD FOR
                SELECT distinct EM.MEDICAL_CODE, EL.GROUP_ID FROM EMRMedicalConditions EM INNER JOIN
                      EMRPatientsMaster EPM ON EM.PATIENT_ID = EPM.PATIENT_ID INNER
JOIN
                      EMRLocationMaster EL ON EPM.LOCATION_ID = EL.LOCATION_ID;
        OPEN UncodedProblem_Cursor
        FETCH NEXT FROM UncodedProblem_Cursor INTO @UNC_PROBLEM_NAME,@Group_ID;
        WHILE @@FETCH_Status = 0 
          BEGIN                               
                 Set @PROBLEM_CODE = NULL;
                 If (@UNC_PROBLEM_NAME is null )
                  Begin
                   Set @Problem_Desc = @UNC_PROBLEM_NAME;
                  End 
                Else
                    Begin
                      Set @PROBLEM_CODE = SUBSTRING(@UNC_PROBLEM_NAME, 1, 3)
                      Set @Problem_Desc = @PROBLEM_CODE + '-' + @UNC_PROBLEM_NAME;
                    End 
                Insert Into
EMRUNCODEDPROBLEMS(PROBLEM_DESC,PROBLEM_CODE,PROBLEM_NAME,STATUS,GROUP_ID,UNCODED_PROBLEM_ID)
                 Values(@Problem_Desc,@PROBLEM_CODE,@UNC_PROBLEM_NAME,1,@Group_ID,@Genid);

                Update EMRIDS Set EZEMRXID = @Genid Where PROPERTY_NAME =
'UNCODED_PROBLEM_ID'
                SET @Genid = @Genid + 1
                FETCH NEXT FROM UncodedProblem_Cursor INTO @UNC_PROBLEM_NAME,@Group_ID;
          END
        CLOSE UncodedProblem_Cursor
    DEALLOCATE UncodedProblem_Cursor
End