PRINT N'Altering View [sAppointment].[vExtPatientAppointmentDetail]...';
GO

/******************************************************************************  
 Purpose          : External view to get data from sRegistration.tRegistration table  
 Created date     : 28 May 2009  
 Author           : Rajendra Patel  
 Copyright©       : Copyright © Sapphire Software  
 Last Change Desc : Replaced table by view  
******************************************************************************  
INNER JOIN sCommon.tLocationMaster LM ON SLM.LocationId = LM.Id*/  
/* Removed double space between PatientName (12-11-2025) - Dheer */
ALTER VIEW [sAppointment].[vExtPatientAppointmentDetail]  
AS  
SELECT     A.Id, A.AppointmentNumber, A.PatientSourceTypeId, A.PatientSourceId,   
                      CASE WHEN A.PatientSourceTypeId = 95 THEN EM.Id WHEN A.PatientSourceTypeId = 1009 THEN CSB.Id ELSE EFD.Id END AS DependentId,   
                      --CASE WHEN A.PatientSourceTypeId = 95 THEN (EM.FirstName + ISNULL(' ' + EM.MiddleName, '') + ' ' + EM.LastName)   
                      --WHEN A.PatientSourceTypeId = 1009 THEN CASE WHEN isnull(A.OtherName,'') <> '' then A.OtherName else (CSB.FirstName + ISNULL(' ' + CSB.MiddleName, '') + ' ' + CSB.LastName) end ELSE EFD.Name END AS PatientName,
					 
    CASE 
        WHEN A.PatientSourceTypeId = 95 THEN
            LTRIM(RTRIM(
                ISNULL(EM.FirstName,'') +
                CASE WHEN ISNULL(EM.MiddleName,'') <> '' THEN ' ' + EM.MiddleName ELSE '' END +
                CASE WHEN ISNULL(EM.LastName,'') <> '' THEN ' ' + EM.LastName ELSE '' END
            ))
        WHEN A.PatientSourceTypeId = 1009 THEN
            CASE 
                WHEN ISNULL(A.OtherName,'') <> '' THEN A.OtherName
                ELSE LTRIM(RTRIM(
                    ISNULL(CSB.FirstName,'') +
                    CASE WHEN ISNULL(CSB.MiddleName,'') <> '' THEN ' ' + CSB.MiddleName ELSE '' END +
                    CASE WHEN ISNULL(CSB.LastName,'') <> '' THEN ' ' + CSB.LastName ELSE '' END
                ))
            END
        ELSE EFD.Name
    END AS PatientName,
                      A.AppointmentDate, A.FromTime, A.ToTime, A.DoctorEmployeeId, A.StoreLocationId, A.SetupId,   
                      CASE WHEN A.PatientSourceTypeId = 95 THEN EM.Code WHEN A.PatientSourceTypeId = 1009 THEN CSB.Code ELSE EM2.Code END AS EmployeeCode,   
                      CASE WHEN A.PatientSourceTypeId = 95 THEN EM.Id WHEN A.PatientSourceTypeId = 1009 THEN CSB.Id ELSE EFD.Id END AS EmployeeId,   
                      CASE WHEN A.PatientSourceTypeId = 95 THEN 'Self' WHEN A.PatientSourceTypeId = 1009 THEN 'Other' ELSE RM.Title END AS Relation,   
                      CASE WHEN A.PatientSourceTypeId = 95 THEN ISNULL(GM.Title, '') WHEN A.PatientSourceTypeId = 1009 THEN ISNULL(GM2.Title, '')   
                      ELSE CASE WHEN ISNULL([sCommon].[fGenderFromRelationIdGet](EFD.RelationshipId), '') = '' THEN  
                      (select Title from sCommon.tGenderMaster where Id = EFD.GenderId) ELSE  
                      ISNULL([sCommon].[fGenderFromRelationIdGet](EFD.RelationshipId), '') END END AS Gender,   
                      CASE WHEN A.PatientSourceTypeId = 95 THEN EJD.EmployeeLocationId WHEN A.PatientSourceTypeId = 1009 THEN CSB.LocationId ELSE EJD1.EmployeeLocationId END  
                       AS LocationId, EM1.FirstName + ' ' + EM1.LastName AS DoctorName, A.OrgId, A.IsRegister, A.IsObsolete,   
                      ODM.Title AS DepartmentName, ODM.Id AS DepartmentId, SM.Title AS StatusName, SLM.Title AS StoreLocation, VSM.MainTypeId AS ExaminationTypeId,   
                      VSM.MainType, VSM.Title AS Setup, A.ReferSourceId, CASE A.PatientSourceTypeId WHEN 95 THEN ISNULL(EJD.EmployeeStoreLocationId, 0)   
                      WHEN 1001 THEN ISNULL(EJD1.EmployeeStoreLocationId, 0) END AS EmployeeStoreLocationId,  
                      A.CauseId,A.OtherName,A.FollowupRemark,A.CompliantRemark,isnull(A.Age,0) as Age,isnull(A.Months,0) as Months,  
                      isnull(A.GenderId,0) as GenderId,  
                      isnull(A.ExaminationId,0) as ExaminationId,  
       (Left(convert(varchar,TSM.FromTime,108),5) + '-' + Left(convert(varchar,TSM.ToTime,108),5)) as TimeSlot,  
       A.ApprovalStatusId,ISNULL(A.AppointmentType,0) as AppointmentType,
	   ISNULL(A.AppointmentFlag,'New Appointment')AS AppointmentFlag,isnull(A.VisitTypeId,0) as VisitTypeId
	FROM sAppointment.tAppointment AS A  
       LEFT JOIN sHrm.tEmployeeFamilyDetails AS EFD ON A.PatientSourceId = EFD.Id AND A.PatientSourceTypeId = 1001  
                      LEFT JOIN sHrm.tEmployeeMaster AS EM2 ON EM2.Id = EFD.EmployeeId AND A.PatientSourceTypeId = 1001  
                      LEFT JOIN sHrm.tEmployeeMaster AS EM ON (EM.Id = A.PatientSourceId OR EM.Id = EFD.EmployeeId) AND A.PatientSourceTypeId = 95  
                      LEFT JOIN sHrm.tCsrBenefiterMaster AS CSB ON CSB.Id = A.PatientSourceId AND A.PatientSourceTypeId = 1009  
                      LEFT JOIN sHrm.tEmployeePersonalDetail AS EPD ON EPD.EmployeeId = EM.Id  
                      LEFT JOIN sCommon.tGenderMaster AS GM ON GM.Id = EPD.GenderId  
                      LEFT JOIN sCommon.tGenderMaster AS GM2 ON GM.Id = CSB.GenderId  
                      LEFT JOIN sCommon.tStatusMaster AS SM ON SM.Id = A.StatusId  
                      LEFT JOIN sHrm.tEmployeeMaster AS EM1 ON EM1.Id = A.DoctorEmployeeId  
                      LEFT JOIN sCommon.tRelationshipMaster AS RM ON RM.Id = EFD.RelationshipId  
                      LEFT JOIN sHrm.tEmployeeJobDetail AS EJD ON EJD.EmployeeId = EM.Id  
                      LEFT JOIN sHrm.tEmployeeJobDetail AS EJD1 ON EJD1.EmployeeId = EFD.EmployeeId  
                      LEFT JOIN sCommon.tOrganizationDepartmentMaster AS ODM ON ODM.Id = EJD.OrganizationDepartmentId  
                      INNER JOIN sIp.tStoreLocationMaster AS SLM ON SLM.Id = A.StoreLocationId  
                      INNER JOIN sExamination.vExtSetupMaster AS VSM ON VSM.Id = A.SetupId  
       LEFT JOIN sAppointment.tTimeSlotMaster TSM On TSM.Id=A.TimeslotId
GO
PRINT N'Refreshing View [sAppointment].[vMobExtPatientAppointmentDetail]...';


GO
EXECUTE sp_refreshsqlmodule N'[sAppointment].[vMobExtPatientAppointmentDetail]';


GO
PRINT N'Refreshing View [sExamination].[vExtAllExaminationDetail]...';


GO
EXECUTE sp_refreshsqlmodule N'[sExamination].[vExtAllExaminationDetail]';


GO
PRINT N'Refreshing View [sExamination].[vExtEmployeeOPDDetail]...';


GO
EXECUTE sp_refreshsqlmodule N'[sExamination].[vExtEmployeeOPDDetail]';


GO
PRINT N'Refreshing View [sExamination].[vExtPrescription]...';


GO
EXECUTE sp_refreshsqlmodule N'[sExamination].[vExtPrescription]';


GO
PRINT N'Refreshing View [sExamination].[vExtPrescriptionDetail]...';


GO
EXECUTE sp_refreshsqlmodule N'[sExamination].[vExtPrescriptionDetail]';


GO
PRINT N'Refreshing View [sExamination].[vExtExaminationDetail]...';


GO
EXECUTE sp_refreshsqlmodule N'[sExamination].[vExtExaminationDetail]';


GO
PRINT N'Refreshing View [sOrderEntry].[vExtTestMaster]...';


GO
EXECUTE sp_refreshsqlmodule N'[sOrderEntry].[vExtTestMaster]';


GO
PRINT N'Refreshing View [sOrderEntry].[vExtTestParameterMaster]...';


GO
EXECUTE sp_refreshsqlmodule N'[sOrderEntry].[vExtTestParameterMaster]';


GO
PRINT N'Refreshing View [sOrderEntry].[vOrganizationTestMaster]...';


GO
EXECUTE sp_refreshsqlmodule N'[sOrderEntry].[vOrganizationTestMaster]';


GO
PRINT N'Altering View [sHospitalization].[vIntimationAssignedDoctor]...';


GO
ALTER VIEW [sHospitalization].[vIntimationAssignedDoctor]
AS
WITH ConfigCheck AS (
    SELECT 
        CASE 
            WHEN EXISTS (
                SELECT 1 
                FROM [sConfig].[tConfigurationMaster] 
                WHERE ItemKey = 'ClaimIntimation.HospitalizationAssignDoctorMaster'
                AND ItemValue = 'True'
                AND IsObsolete = 0
            ) THEN 1  -- Use New table
            ELSE 0   -- Use Old table
        END AS UseNewTable
)
SELECT 
    Id,
    OrgId,
    BusinessId,
    SubBusinessId,
    LocationId,
    SubLocationId,
    StateId,
    OrganizationId,
    DoctorEmployeeId,
    AssignDesignationId,
    AssignDoctorIndex,
    IsObsolete,
    CreatedUserId,
    CreatedDateTime,
    UpdatedUserId,
    UpdatedDateTime,
    AssignDesignationName,
    -- Additional columns with NULL for old table compatibility
    NULL AS AssignTypeId,
    NULL AS AssignSubTypeId,
    NULL AS DesignationId
FROM (
    -- New table data (when config is True)
    SELECT 
        Id,
        OrgId,
        BusinessId,
        SubBusinessId,
        LocationId,
        SubLocationId,
        StateId,
        OrganizationId,
        DoctorEmployeeId,
        AssignDesignationId,
        AssignDoctorIndex,
        IsObsolete,
        CreatedUserId,
        CreatedDateTime,
        UpdatedUserId,
        UpdatedDateTime,
        AssignDesignationName,
        1 AS TableType  -- 1 for New table
    FROM [sHospitalization].[tClaimIntimationAssignedDoctor]
    WHERE IsObsolete = 0
    
    UNION ALL
    
    -- Old table data (when config is False)
    SELECT 
        Id,
        OrgId,
        NULL AS BusinessId,  -- Old table doesn't have this
        NULL AS SubBusinessId,
        NULL AS LocationId,
        NULL AS SubLocationId,
        NULL AS StateId,
        NULL AS OrganizationId,
        DoctorEmployeeId,
        DesignationId AS AssignDesignationId,
        NULL AS AssignDoctorIndex,
        IsObsolete,
        CreatedUserId,
        CreatedDateTime,
        UpdatedUserId,
        UpdatedDateTime,
        NULL AS AssignDesignationName,
        0 AS TableType  -- 0 for Old table
    FROM [sHospitalization].[tIntimationAssignedDoctor]
    WHERE IsObsolete = 0
) AS AllData
CROSS JOIN ConfigCheck
WHERE (
    (ConfigCheck.UseNewTable = 1 AND TableType = 1)  -- Config True: Show only New table data
    OR 
    (ConfigCheck.UseNewTable = 0 AND TableType = 0)  -- Config False: Show only Old table data
);
GO
PRINT N'Altering View [sWellness].[vExtPatientWellnessAppointmentDetail]...';


GO

ALTER VIEW [sWellness].[vExtPatientWellnessAppointmentDetail]
AS  
SELECT     A.Id, A.AppointmentNumber, A.PatientSourceTypeId, A.PatientSourceId,   
                      CASE WHEN A.PatientSourceTypeId = 95 THEN EM.Id WHEN A.PatientSourceTypeId = 1009 THEN CSB.Id ELSE EFD.Id END AS DependentId,	 
    CASE 
        WHEN A.PatientSourceTypeId = 95 THEN
            LTRIM(RTRIM(
                ISNULL(EM.FirstName,'') +
                CASE WHEN ISNULL(EM.MiddleName,'') <> '' THEN ' ' + EM.MiddleName ELSE '' END +
                CASE WHEN ISNULL(EM.LastName,'') <> '' THEN ' ' + EM.LastName ELSE '' END
            ))
        WHEN A.PatientSourceTypeId = 1009 THEN
            CASE 
                WHEN ISNULL(A.OtherName,'') <> '' THEN A.OtherName
                ELSE LTRIM(RTRIM(
                    ISNULL(CSB.FirstName,'') +
                    CASE WHEN ISNULL(CSB.MiddleName,'') <> '' THEN ' ' + CSB.MiddleName ELSE '' END +
                    CASE WHEN ISNULL(CSB.LastName,'') <> '' THEN ' ' + CSB.LastName ELSE '' END
                ))
            END
        ELSE EFD.Name
    END AS PatientName,
                      A.AppointmentDate, A.FromTime, A.ToTime, A.DoctorEmployeeId, A.StoreLocationId, A.SetupId,   
                      CASE WHEN A.PatientSourceTypeId = 95 THEN EM.Code WHEN A.PatientSourceTypeId = 1009 THEN CSB.Code ELSE EM2.Code END AS EmployeeCode,   
                      CASE WHEN A.PatientSourceTypeId = 95 THEN EM.Id WHEN A.PatientSourceTypeId = 1009 THEN CSB.Id ELSE EFD.Id END AS EmployeeId,   
                      CASE WHEN A.PatientSourceTypeId = 95 THEN 'Self' WHEN A.PatientSourceTypeId = 1009 THEN 'Other' ELSE RM.Title END AS Relation,   
                      CASE WHEN A.PatientSourceTypeId = 95 THEN ISNULL(GM.Title, '') WHEN A.PatientSourceTypeId = 1009 THEN ISNULL(GM2.Title, '')   
                      ELSE CASE WHEN ISNULL([sCommon].[fGenderFromRelationIdGet](EFD.RelationshipId), '') = '' THEN  
                      (select Title from sCommon.tGenderMaster where Id = EFD.GenderId) ELSE  
                      ISNULL([sCommon].[fGenderFromRelationIdGet](EFD.RelationshipId), '') END END AS Gender,   
                      CASE WHEN A.PatientSourceTypeId = 95 THEN EJD.EmployeeLocationId WHEN A.PatientSourceTypeId = 1009 THEN CSB.LocationId ELSE EJD1.EmployeeLocationId END  
                       AS LocationId, CASE WHEN ISNULL(EM1.EmployeePrefix,'') <> '' THEN ISNULL(EM1.EmployeePrefix,'') + ' ' ELSE '' END
												+ EM1.FirstName + ' ' + EM1.LastName AS DoctorName,
					   A.OrgId, A.IsRegister, A.IsObsolete,   
                      ODM.Title AS DepartmentName, ODM.Id AS DepartmentId, SM.Title AS StatusName, SLM.Title AS StoreLocation, VSM.MainTypeId AS ExaminationTypeId,   
                      VSM.MainType, VSM.Title AS Setup, A.ReferSourceId, CASE A.PatientSourceTypeId WHEN 95 THEN ISNULL(EJD.EmployeeStoreLocationId, 0)   
                      WHEN 1001 THEN ISNULL(EJD1.EmployeeStoreLocationId, 0) END AS EmployeeStoreLocationId,  
                      A.CauseId,A.OtherName,A.FollowupRemark,A.CompliantRemark,isnull(A.Age,0) as Age,isnull(A.Months,0) as Months,  
                      isnull(A.GenderId,0) as GenderId,  
                      isnull(A.ExaminationId,0) as ExaminationId,
					  A.TimeslotId,
       (Left(convert(varchar,TSM.FromTime,108),5) + '-' + Left(convert(varchar,TSM.ToTime,108),5)) as TimeSlot,  
       A.ApprovalStatusId,ISNULL(A.AppointmentType,0) as AppointmentType,
	   ISNULL(A.AppointmentFlag,'New Wellness Appointment') AS AppointmentFlag
	FROM [sWellness].[tAppointment] AS A  
        LEFT JOIN sHrm.tEmployeeFamilyDetails AS EFD ON A.PatientSourceId = EFD.Id AND A.PatientSourceTypeId = 1001  
		LEFT JOIN sHrm.tEmployeeMaster AS EM2 ON EM2.Id = EFD.EmployeeId AND A.PatientSourceTypeId = 1001  
		LEFT JOIN sHrm.tEmployeeMaster AS EM ON (EM.Id = A.PatientSourceId OR EM.Id = EFD.EmployeeId) AND A.PatientSourceTypeId = 95  
		LEFT JOIN sHrm.tCsrBenefiterMaster AS CSB ON CSB.Id = A.PatientSourceId AND A.PatientSourceTypeId = 1009  
		LEFT JOIN sHrm.tEmployeePersonalDetail AS EPD ON EPD.EmployeeId = EM.Id  
		LEFT JOIN sCommon.tGenderMaster AS GM ON GM.Id = EPD.GenderId  
		LEFT JOIN sCommon.tGenderMaster AS GM2 ON GM.Id = CSB.GenderId  
		LEFT JOIN sCommon.tStatusMaster AS SM ON SM.Id = A.StatusId  
		LEFT JOIN sHrm.tEmployeeMaster AS EM1 ON EM1.Id = A.DoctorEmployeeId  
		LEFT JOIN sCommon.tRelationshipMaster AS RM ON RM.Id = EFD.RelationshipId  
		LEFT JOIN sHrm.tEmployeeJobDetail AS EJD ON EJD.EmployeeId = EM.Id  
		LEFT JOIN sHrm.tEmployeeJobDetail AS EJD1 ON EJD1.EmployeeId = EFD.EmployeeId  
		LEFT JOIN sCommon.tOrganizationDepartmentMaster AS ODM ON ODM.Id = EJD.OrganizationDepartmentId  
		INNER JOIN sIp.tStoreLocationMaster AS SLM ON SLM.Id = A.StoreLocationId  
		INNER JOIN sExamination.vExtSetupMaster AS VSM ON VSM.Id = A.SetupId  
		LEFT JOIN sAppointment.tTimeSlotMaster TSM On TSM.Id=A.TimeslotId
GO
PRINT N'Creating Function [dbo].[fn_GetPrescriptionDetails]...';


GO
CREATE FUNCTION dbo.fn_GetPrescriptionDetails
(
    @SourceId bigint
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @Result NVARCHAR(MAX)

    SELECT @Result = STUFF((
        SELECT 
            ', ' + 
            ISNULL(SIM.Title, '') + ' | ' +
            'Qty:' + CAST(ISNULL(PD.Dosage,0) AS VARCHAR) + ' | ' +
            ISNULL(DFM.Title,'') + ' | ' +
            'Instruction:' + ISNULL(PD.DrugInstruction,'') + ' | ' +
            'Duration:' + CAST(ISNULL(PD.Duration,0) AS VARCHAR) + ' | ' +
            'Prescribed:' + CAST(ISNULL(PD.PrescribedQuantity,0) AS VARCHAR)

        FROM sExamination.vExtPrescription P
        INNER JOIN sExamination.vExtPrescriptionDetail PD 
            ON P.Id = PD.PrescriptionId AND PD.IsObsolete = 0
        LEFT JOIN sIp.tDrugDosageFrequencyMaster DFM 
            ON DFM.Id = PD.DrugDosageFrequencyId
        LEFT JOIN sIp.tStockItemMaster SIM 
            ON PD.DrugStockItemId = SIM.Id

        WHERE P.SourceId = @SourceId

        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

    RETURN @Result
END
GO
PRINT N'Creating Function [sExamination].[fn_GetEmployeePastHistory_String]...';


GO
CREATE FUNCTION [sExamination].[fn_GetEmployeePastHistory_String]
(
    @OrgId INT,
    @ParticipantSourceId BIGINT,
    @ParticipantSourceTypeId BIGINT
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @Result NVARCHAR(MAX)

    SELECT @Result = STUFF((
        SELECT 
            ' | ' + 
            HT.Title + ' (' +

            -- Build relation string
            STUFF(
                CASE WHEN MAX(CASE WHEN HD.RelationshipId = 1 THEN 1 ELSE 0 END) = 1 THEN ',Self' ELSE '' END +
                CASE WHEN MAX(CASE WHEN HD.RelationshipId = 2 THEN 1 ELSE 0 END) = 1 THEN ',Father' ELSE '' END +
                CASE WHEN MAX(CASE WHEN HD.RelationshipId = 3 THEN 1 ELSE 0 END) = 1 THEN ',Mother' ELSE '' END +
                CASE WHEN MAX(CASE WHEN HD.RelationshipId = 7 THEN 1 ELSE 0 END) = 1 THEN ',Brother' ELSE '' END +
                CASE WHEN MAX(CASE WHEN HD.RelationshipId = 8 THEN 1 ELSE 0 END) = 1 THEN ',Sister' ELSE '' END
            ,1,1,'') 

            + ')'

        FROM sExamination.tDiseaseMaster HT

        INNER JOIN sExamination.tHistory H 
            ON H.ParticipantSourceId = @ParticipantSourceId 
            AND H.OrgId = @OrgId
            AND H.ParticipantSourceTypeId = @ParticipantSourceTypeId

        INNER JOIN sExamination.tHistoryDetail HD 
            ON HD.HistoryId = H.Id 
            AND HD.DiseaseId = HT.Id

        WHERE HT.OrgId = @OrgId 
          AND HT.IsForOPD = 1

        GROUP BY HT.Title

        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

    RETURN @Result
END
GO
PRINT N'Creating Function [sExamination].[fn_GetPrescriptionDetails]...';


GO
CREATE FUNCTION [sExamination].[fn_GetPrescriptionDetails]
(
    @SourceId bigint
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @Result NVARCHAR(MAX)

    SELECT @Result = STUFF((
        SELECT DISTINCT
            
            'Medicine : ' + ISNULL(SIM.Title, '') + ' | ' +
            'Qty : ' + CAST(ISNULL(CAST (PD.Dosage AS int),0) AS VARCHAR) + ' | ' +
            ISNULL(DFM.Title,'') + ' | ' +
            'Instruction : ' + ISNULL(PD.DrugInstruction,'') + ' | ' +
            'Duration : ' + CAST(ISNULL(CAST (PD.Duration AS int),0) AS VARCHAR) + ' | ' +
            'Prescribed : ' + CAST(ISNULL(CAST (PD.PrescribedQuantity AS int),0) AS VARCHAR)+ 
			  ', '

        FROM sExamination.vExtPrescription P
        INNER JOIN sExamination.vExtPrescriptionDetail PD 
            ON P.Id = PD.PrescriptionId AND PD.IsObsolete = 0
        INNER JOIN sIp.tDrugDosageFrequencyMaster DFM 
            ON DFM.Id = PD.DrugDosageFrequencyId
        INNER JOIN sIp.tStockItemMaster SIM 
            ON PD.DrugStockItemId = SIM.Id

        WHERE P.SourceId = @SourceId

        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 0, '')

    RETURN @Result
END
GO
PRINT N'Refreshing Function [dbo].[fAbnormalFindingsHealthIndicator]...';


GO
EXECUTE sp_refreshsqlmodule N'[dbo].[fAbnormalFindingsHealthIndicator]';


GO
PRINT N'Creating Function [sExamination].[fn_GetEmployeePastHistory]...';


GO
CREATE FUNCTION [sExamination].[fn_GetEmployeePastHistory]
(
    @OrgId INT,
    @ParticipantSourceId BIGINT,
    @ParticipantSourceTypeId BIGINT
)
RETURNS TABLE
AS
RETURN
(
    SELECT 
        CONVERT(VARCHAR, HT.Id) AS HTId,
        CONVERT(VARCHAR, ISNULL(H.Id, 0)) AS HId,
        ISNULL(HD.DiseaseId, 0) AS HDDDid,
        HT.Title,
        ISNULL(HD.Remark, '') AS HDDRem,

        -- Relationship flags
        MAX(CASE WHEN HD.RelationshipId = 1 THEN 1 ELSE 0 END) AS HDDSelf,
        MAX(CASE WHEN HD.RelationshipId = 2 THEN 1 ELSE 0 END) AS HDDFather,
        MAX(CASE WHEN HD.RelationshipId = 3 THEN 1 ELSE 0 END) AS HDDMother,
        MAX(CASE WHEN HD.RelationshipId = 7 THEN 1 ELSE 0 END) AS HDDBrother,
        MAX(CASE WHEN HD.RelationshipId = 8 THEN 1 ELSE 0 END) AS HDDSister,

        -- History fields
        CASE WHEN H.IsSurgeryDone = 0 THEN 0 ELSE 1 END AS SurgeryDone,
        H.SurgeryRemark,
        H.Remark,
        H.TreatmentHistoryRemark

    FROM sExamination.tDiseaseMaster HT

    LEFT JOIN sExamination.tHistory H 
        ON H.ParticipantSourceId = @ParticipantSourceId 
        AND H.OrgId = @OrgId
        AND H.ParticipantSourceTypeId = @ParticipantSourceTypeId

    LEFT JOIN sExamination.tHistoryDetail HD 
        ON HD.HistoryId = H.Id 
        AND HD.DiseaseId = HT.Id

    WHERE HT.OrgId = @OrgId 
      AND HT.IsForOPD = 1
	  AND ISNULL(H.Id, 0) <> 0
      AND ISNULL(HD.DiseaseId, 0) <> 0

    GROUP BY 
        HT.Id, HT.Title, 
        H.Id, H.IsSurgeryDone, 
        H.SurgeryRemark, H.Remark, H.TreatmentHistoryRemark,
        HD.DiseaseId, HD.Remark
)