
PRINT N'Refreshing View [sCwb].[vExtDoctorListIncludingSpecialityNotAttached]...';


GO
EXECUTE sp_refreshsqlmodule N'[sCwb].[vExtDoctorListIncludingSpecialityNotAttached]';


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'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 */
CREATE OR 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
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'Creating View [sWellness].[vExtPatientWellnessAppointmentDetail]...';


GO

CREATE OR 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 (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
	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 View [sWellness].[vMobExtPatientWellnessAppointmentDetail]...';


GO
  
CREATE OR ALTER VIEW [sWellness].[vMobExtPatientWellnessAppointmentDetail]  
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  ISNULL(EM.EmployeePrefix + ' ', ' ' ) + (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,   
                      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,ISNULL(EM1.EmployeePrefix + ' ', ' ') + ( EM1.FirstName + ISNULL(' ' + EM1.MiddleName, '') + ' ' + 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,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),8) + '-' + Left(convert(varchar,TSM.ToTime,108),8)) as TimeSlot,  
       case when A.AppointmentType=1 then 'Online' when A.AppointmentType=0 then 'Offline' else '' end as 'AppointmentType',SSM.Id as StatusId,SSM.Title as 'Status',case when A.MeetingId is null then '' else A.MeetingId end as MeetingId,  
       case when A.MeetingPassword is null then '' else A.MeetingPassword end as MeetingPassword,A.IsCompleted,A.IsMeetingStarted,IsMeetingCreated  ,A.CompliantRemark
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
					  LEFT JOIN sCommon.tStatusMaster SSM On SSM.Id=A.ApprovalStatusId
GO
PRINT N'Altering Function [dbo].[fn_ParseDateString]...';


GO
CREATE OR ALTER FUNCTION [dbo].[fn_ParseDateString] (@DateStr NVARCHAR(100))
RETURNS DATE
AS
BEGIN
    DECLARE @ParsedDate DATE;

    IF @DateStr IS NULL OR LTRIM(RTRIM(@DateStr)) = ''
        RETURN NULL;

    -- Case 1: dd/MM/yyyy
    IF @DateStr LIKE '[0-3][0-9]/[0-1][0-9]/[1-2][0-9][0-9][0-9]'
    BEGIN
        IF ISDATE(
            SUBSTRING(@DateStr, 7, 4) + '-' +
            SUBSTRING(@DateStr, 4, 2) + '-' +
            SUBSTRING(@DateStr, 1, 2)
        ) = 1
        BEGIN
            SET @ParsedDate = CONVERT(
                DATE,
                SUBSTRING(@DateStr, 7, 4) + '-' +
                SUBSTRING(@DateStr, 4, 2) + '-' +
                SUBSTRING(@DateStr, 1, 2),
                23
            );
        END
    END
    ELSE
    BEGIN
        -- Case 2: yyyy-mm-dd or yyyy-mm-dd hh:mm:ss
        IF ISDATE(@DateStr) = 1
        BEGIN
            SET @ParsedDate = CONVERT(DATE, @DateStr, 120);
        END
    END

    RETURN @ParsedDate;
END;
GO
PRINT N'Altering Function [sCommon].[fn_ConvertToIST]...';


GO
CREATE OR ALTER FUNCTION [sCommon].[fn_ConvertToIST]
(
    @DateTime DATETIME
)
RETURNS NVARCHAR(20)
AS
BEGIN
    DECLARE @ISTDateTime DATETIME

    -- Add 5 hours 30 minutes to convert UTC to IST
    SET @ISTDateTime = DATEADD(MINUTE, 330, @DateTime)

    -- Return formatted string yyyy-MM-dd HH:mm:ss
    RETURN ISNULL(
        CONVERT(NVARCHAR(20), @ISTDateTime, 120), -- 120 = ODBC canonical yyyy-mm-dd hh:mi:ss
        '-'
    )
END
GO
PRINT N'Creating Function [sWellness].[fn_IsEmployeeRegistered]...';


GO
CREATE OR ALTER FUNCTION [sWellness].[fn_IsEmployeeRegistered]
(
    @rProgramId BIGINT,
    @rEmployeeId BIGINT
)
RETURNS INT
AS
BEGIN
    DECLARE @Result INT = 0;

    -- Already registered
    IF EXISTS
    (
        SELECT 1
        FROM [sWellness].[tProgramParticipant] PP
        WHERE PP.ProgramId = @rProgramId
          AND PP.ParticipantSourceId = @rEmployeeId
    )
    BEGIN
        SET @Result = 1;
    END

    -- Registration closed
    ELSE IF EXISTS
    (
        SELECT 1
        FROM [sWellness].[tProgramMaster] PM
        WHERE PM.Id = @rProgramId
          AND PM.RegisterUptoDatetime < sCommon.fGetDate()
    )
    BEGIN
        SET @Result = 2;
    END

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


GO
CREATE OR ALTER FUNCTION [dbo].[fAbnormalFindingsHealthIndicator]
(
    @rExaminationId BIGINT
)
RETURNS TABLE
AS
RETURN
(
    WITH DataCTE AS
(
    SELECT
        EXM.Id AS ExaminationId,
		PA.HealthIndicatorId,
        DV.BMI,
        DV.BloodPressureSystolic,
        DV.BloodPressureDiastolic,

        -- Explicit columns from Pivot (NO ExaminationId here)
        D.HB,
        D.HBA1C,
        D.[SERUM CREATININE],
        D.[SERUM URIC ACID],
        D.[TRIGLYCERIDE],
        D.LDL,
        D.TSH,
        D.PSA,
        D.TMT,
        D.ECHO,
        D.ECG,
        D.SONOGRAPHY,
        D.XRAY
    FROM sSupport.tProviderAppointment AS PA
	INNER JOIN sExamination.tExamination EXM ON EXM.ParticipantSourceId = PA.PatientSourceId AND EXM.SetupId = PA.SetupId
    LEFT JOIN sExamination.tVital DV
        ON DV.SourceId = EXM.Id
    LEFT JOIN
    (
        SELECT *
        FROM
        (
            SELECT
                TR.SourceId AS ExaminationId,
                SPM.Title,
                TRD.ServiceValue
            FROM sOrderEntry.tStaticTestParameterMaster SPM
            INNER JOIN sOrderEntry.tTestParameterMaster TPM 
                ON TPM.StaticTestParameterId = SPM.Id
            INNER JOIN sOrderEntry.tTestDetail TTD 
                ON TTD.TestParameterId = TPM.Id
            INNER JOIN sExamination.tTestRequestDetail TRD 
                ON TRD.TestDetailId = TTD.Id
            INNER JOIN sExamination.tTestRequest TR 
                ON TR.Id = TRD.TestRequestId
            WHERE SPM.Id IN
            (
                3,5,19,20,21,25,31,32,33,34,
                38,107,104,105,109,108,2,
                27,49,166,54,58
            )
        ) X
        PIVOT
        (
            MIN(ServiceValue) FOR Title IN
            (
                [HB],[HBA1C],[SERUM CREATININE],
                [SERUM URIC ACID],[TRIGLYCERIDE],
                [LDL],[TSH],[PSA],
                [TMT],[ECHO],[ECG],
                [SONOGRAPHY],[XRAY]
            )
        ) P
    ) D ON D.ExaminationId = EXM.Id
    WHERE EXM.Id = @rExaminationId
	),
    AbnormalCTE AS
    (
        SELECT *,
        CASE
            /* ================= SEVERE ================= */
            WHEN CAST(BMI AS FLOAT) >= 36 THEN 'BMI'
            WHEN CAST(BloodPressureSystolic AS FLOAT) > 160 THEN 'BPSys'
            WHEN CAST(BloodPressureDiastolic AS FLOAT) > 110 THEN 'BPDia'
            WHEN CAST(HB AS FLOAT) < 8 AND CAST(HB AS FLOAT) > 0 THEN 'HB'
            WHEN CAST(HBA1C AS FLOAT) > 10 THEN 'HBA1C'
            WHEN CAST([SERUM CREATININE] AS FLOAT) > 2 THEN 'SERUM CREATININE'
            WHEN CAST([SERUM URIC ACID] AS FLOAT) > 9.5 THEN 'SERUM URIC ACID'
            WHEN CAST([TRIGLYCERIDE] AS FLOAT) > 500 THEN 'TRIGLYCERIDE'
            WHEN CAST(LDL AS FLOAT) > 200 THEN 'LDL'
            WHEN CAST(TSH AS FLOAT) > 50 THEN 'TSH'
            WHEN CAST(PSA AS FLOAT) > 10 THEN 'PSA'
            WHEN HealthIndicatorId = 607 AND LTRIM(RTRIM(TMT)) = 'Positive' THEN 'TMT'
            WHEN HealthIndicatorId = 607 AND LTRIM(RTRIM(ECHO)) = 'Abnormal' THEN 'ECHO'
            WHEN HealthIndicatorId = 607 AND LTRIM(RTRIM(ECG)) = 'Abnormal' THEN 'ECG'
            WHEN HealthIndicatorId = 607 AND LTRIM(RTRIM(SONOGRAPHY)) = 'Abnormal' THEN 'SONOGRAPHY'
            WHEN HealthIndicatorId = 607 AND LTRIM(RTRIM(XRAY)) = 'Abnormal' THEN 'XRAY'

            /* ================= MODERATE ================= */
            WHEN CAST(BMI AS FLOAT) BETWEEN 30 AND 35 THEN 'BMI'
            WHEN CAST(BloodPressureSystolic AS FLOAT) BETWEEN 150 AND 160 THEN 'BPSys'
            WHEN CAST(BloodPressureDiastolic AS FLOAT) BETWEEN 100 AND 110 THEN 'BPDia'
            WHEN CAST(HB AS FLOAT) BETWEEN 8 AND 9.99 THEN 'HB'
            WHEN CAST(HBA1C AS FLOAT) BETWEEN 8 AND 10 THEN 'HBA1C'
            WHEN CAST([SERUM CREATININE] AS FLOAT) BETWEEN 1.5 AND 2 THEN 'SERUM CREATININE'
            WHEN CAST([SERUM URIC ACID] AS FLOAT) BETWEEN 7.7 AND 9.4 THEN 'SERUM URIC ACID'
            WHEN CAST([TRIGLYCERIDE] AS FLOAT) BETWEEN 200 AND 500 THEN 'TRIGLYCERIDE'
            WHEN CAST(LDL AS FLOAT) BETWEEN 150 AND 200 THEN 'LDL'
            WHEN CAST(TSH AS FLOAT) BETWEEN 11 AND 50 THEN 'TSH'
            WHEN CAST(PSA AS FLOAT) > 5 THEN 'PSA'

            /* ================= MILD ================= */
            WHEN CAST(BMI AS FLOAT) BETWEEN 26 AND 29.99 THEN 'BMI'
            WHEN CAST(BloodPressureSystolic AS FLOAT) BETWEEN 140 AND 149 THEN 'BPSys'
            WHEN CAST(BloodPressureDiastolic AS FLOAT) BETWEEN 90 AND 99 THEN 'BPDia'
            WHEN CAST(HB AS FLOAT) BETWEEN 10 AND 11.99 THEN 'HB'
            WHEN CAST(HBA1C AS FLOAT) BETWEEN 6.5 AND 7.99 THEN 'HBA1C'
            WHEN CAST([SERUM CREATININE] AS FLOAT) BETWEEN 1.3 AND 1.49 THEN 'SERUM CREATININE'
            WHEN CAST([SERUM URIC ACID] AS FLOAT) BETWEEN 6.5 AND 7.69 THEN 'SERUM URIC ACID'
            WHEN CAST([TRIGLYCERIDE] AS FLOAT) BETWEEN 150 AND 199.99 THEN 'TRIGLYCERIDE'
            WHEN CAST(LDL AS FLOAT) BETWEEN 101 AND 149.99 THEN 'LDL'
            WHEN CAST(TSH AS FLOAT) BETWEEN 5.6 AND 10.99 THEN 'TSH'
            ELSE ''
        END AS AbnormalKey
        FROM DataCTE
    )
    SELECT
        ExaminationId,
        (
            /* ===== BOLD ABNORMAL TEST ===== */
            CASE AbnormalKey
                WHEN 'BMI' THEN '<b>BMI-' + CAST(BMI AS NVARCHAR(20)) + '</b>,<br/>'
				WHEN 'BPSys' THEN '<b>BP(Sys/Dia)-' + ISNULL(CAST(BloodPressureSystolic AS NVARCHAR(50)),'') + '/' + ISNULL(CAST(BloodPressureDiastolic AS NVARCHAR(50)),'') + '</b>,<br/>'
				WHEN 'BPDia' THEN '<b>BP(Sys/Dia)-' + ISNULL(CAST(BloodPressureSystolic AS NVARCHAR(50)),'') + '/' + ISNULL(CAST(BloodPressureDiastolic AS NVARCHAR(50)),'') + '</b>,<br/>'
                WHEN 'HB' THEN '<b>HB-' + CAST(HB AS NVARCHAR(20)) + '</b>,<br/>'
                WHEN 'HBA1C' THEN '<b>HBA1C-' + CAST(HBA1C AS NVARCHAR(20)) + '</b>,<br/>'
                WHEN 'SERUM CREATININE' THEN '<b>SERUM CREATININE-' + CAST([SERUM CREATININE] AS NVARCHAR(20)) + '</b>,<br/>'
                WHEN 'SERUM URIC ACID' THEN '<b>SERUM URIC ACID-' + CAST([SERUM URIC ACID] AS NVARCHAR(20)) + '</b>,<br/>'
                WHEN 'TRIGLYCERIDE' THEN '<b>TRIGLYCERIDE-' + CAST([TRIGLYCERIDE] AS NVARCHAR(20)) + '</b>,<br/>'
                WHEN 'LDL' THEN '<b>LDL-' + CAST(LDL AS NVARCHAR(20)) + '</b>,<br/>'
                WHEN 'TSH' THEN '<b>TSH-' + CAST(TSH AS NVARCHAR(20)) + '</b>,<br/>'
                WHEN 'PSA' THEN '<b>PSA-' + CAST(PSA AS NVARCHAR(20)) + '</b>,<br/>'
                WHEN 'TMT' THEN '<b>TMT-Positive</b>,<br/>'
                WHEN 'ECHO' THEN '<b>ECHO-Abnormal</b>,<br/>'
                WHEN 'ECG' THEN '<b>ECG-Abnormal</b>,<br/>'
                WHEN 'SONOGRAPHY' THEN '<b>SONOGRAPHY-Abnormal</b>,<br/>'
                WHEN 'XRAY' THEN '<b>XRAY-Abnormal</b>,<br/>'
                ELSE ''
            END
			+
            /* ===== DETAILS (EXCLUDE BOLD ONE) ===== */
			STUFF(
            CASE WHEN AbnormalKey <> 'BMI' THEN ', BMI-' + ISNULL(CAST(BMI AS NVARCHAR(20)), '') ELSE '' END
            + CASE WHEN AbnormalKey NOT IN ('BPSys','BPDia') 
                THEN ', BP(Sys/Dia)-' + ISNULL(CAST(BloodPressureSystolic AS NVARCHAR(20)), '') + '/' 
                          + ISNULL(CAST(BloodPressureDiastolic AS NVARCHAR(20)), '') ELSE '' END
            + CASE WHEN AbnormalKey <> 'HB' THEN ', HB-' + ISNULL(CAST(HB AS NVARCHAR(20)), '') ELSE '' END
            + CASE WHEN AbnormalKey <> 'HBA1C' THEN ', HBA1C-' + ISNULL(CAST(HBA1C AS NVARCHAR(20)), '') ELSE '' END
            + CASE WHEN AbnormalKey <> 'SERUM CREATININE' THEN ', SERUM CREATININE-' + ISNULL(CAST([SERUM CREATININE] AS NVARCHAR(20)), '') ELSE '' END
            + CASE WHEN AbnormalKey <> 'SERUM URIC ACID' THEN ', SERUM URIC ACID-' + ISNULL(CAST([SERUM URIC ACID] AS NVARCHAR(20)), '') ELSE '' END
            + CASE WHEN AbnormalKey <> 'TRIGLYCERIDE' THEN ', TRIGLYCERIDE-' + ISNULL(CAST([TRIGLYCERIDE] AS NVARCHAR(20)), '') ELSE '' END
            + CASE WHEN AbnormalKey <> 'LDL' THEN ', LDL-' + ISNULL(CAST(LDL AS NVARCHAR(20)), '') ELSE '' END
            + CASE WHEN AbnormalKey <> 'TSH' THEN ', TSH-' + ISNULL(CAST(TSH AS NVARCHAR(20)), '') ELSE '' END
            + CASE WHEN AbnormalKey <> 'PSA' THEN ', PSA-' + ISNULL(CAST(PSA AS NVARCHAR(20)), '') ELSE '' END
            + CASE WHEN AbnormalKey <> 'TMT' THEN ', TMT-' + ISNULL(TMT,'') ELSE '' END
            + CASE WHEN AbnormalKey <> 'ECHO' THEN ', ECHO-' + ISNULL(ECHO,'') ELSE '' END
            + CASE WHEN AbnormalKey <> 'ECG' THEN ', ECG-' + ISNULL(ECG,'') ELSE '' END
            + CASE WHEN AbnormalKey <> 'SONOGRAPHY' THEN ', SONOGRAPHY-' + ISNULL(SONOGRAPHY,'') ELSE '' END
            + CASE WHEN AbnormalKey <> 'XRAY' THEN ', XRAY-' + ISNULL(XRAY,'') ELSE '' END, 
			1, 2, ''
			)
        ) AS AbnormalFindingHtml
    FROM AbnormalCTE
);
GO