
PRINT N'Refreshing View [sExamination].[vExtEmployeeExaminationArchiveDetail]...';


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


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


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


GO
PRINT N'Altering View [sExamination].[vExtEmployeeExaminationDetail]...';


GO

ALTER VIEW [sExamination].[vExtEmployeeExaminationDetail]
AS
SELECT        EM.Id AS EmployeeId, E.Id AS ExaminationId, E.OrgId, MTM.Id AS MainTypeId, MTM.Title AS MainTypeName, E.StoreLocationId, SLM.Title AS StoreLocationName, SLM.LocationId, EPD.DateOfBirth, EPD.IdentificationMark1, 
                         EPD.IdentificationMark2, EPD.BloodGroupId, EJD.EmployeeDesignationId, EJD.DateOfJoining, LM.Title AS LocationName, E.OrganizationDepartmentId, ISNULL(D.Title, '') AS DepartmentName, D.DepartmentGroupId, E.SetupId, 
                         ISNULL(SM.RoundNumber, 0) AS RoundNumber, ISNULL(SM.Title, 0) AS RoundName, E.StatusId, S.Title AS StatusName, E.ExaminationDate, E.ParticipantSourceTypeId, E.ParticipantSourceId, E.DoctorEmployeeId, 
                         EM.Code AS ParticipantCode, EM.FirstName, EM.MiddleName, EM.FirstName + ' ' + EM.LastName AS ParticipantName, 'SELF' AS RelationName, GM.Title AS Gender, GM.Id AS GenderId, 
                         CASE WHEN (CASE WHEN isnull(Users.DisplayTitle, '') <> '' THEN Users.DisplayTitle ELSE ISNULL(EM3.FirstName, '') + ' ' + ISNULL(EM3.LastName, '') END) 
                         = 'SysSuperAdmin' THEN '' ELSE (CASE WHEN isnull(Users.DisplayTitle, '') <> '' THEN Users.DisplayTitle ELSE ISNULL(EM3.FirstName, '') + ' ' + ISNULL(EM3.LastName, '') END) END AS DoctorName, 
                         ISNULL(EJD.EmployeeJobTitleId, 0) AS EmployeeJobTitleId, ISNULL(E.EmployeeDesignationId, 0) AS EmployeeExaminationDesignationId, ISNULL(EJD.ContractorId, 0) AS ContractorId, ISNULL(EJD.EmployeeCompanyId, 0) 
                         AS EmployeeCompanyId, ISNULL(EJD.EmployeeTypeId, 0) AS EmployeeTypeId, ISNULL(EJD.SkillId, 0) AS SkillId, ISNULL(E.ProcessTypeId, 0) AS ProcessTypeId, ISNULL(EJD.EmployeeStoreLocationId, 0) 
                         AS EmployeeStoreLocationId, ISNULL(EJD.EmployeeLocationId, 0) AS EmployeeLocationId, CASE E.ParticipantSourceTypeId WHEN 95 THEN ETM.Title WHEN 1001 THEN 'Dep' WHEN 1009 THEN 'Oth' END AS ParticipantType, 
                         SM.Title AS SetupName, ISNULL(EJD.GradeId, 0) AS GradeId, ISNULL(ETM.EmployeeTypeGroupId, 0) AS EmployeeTypeGroupId, ISNULL(EJD.EmployeeSubTypeId, 0) AS EmployeeSubTypeId, ISNULL(EJD.EmployeeGroupId, 0) 
                         AS EmployeeGroupId, ISNULL(EM.TargetGroupId, 0) AS EmployeeTargetGroupId, ISNULL(TGM.Title, '') AS EmployeeTargetGroupName, ISNULL(LEM.Title, '') AS EmployeeLegalEntity, ISNULL(LEM.Id, '') 
                         AS EmployeeLegalEntityId, ISNULL(EPD.UIDNumber, '-') AS UIDNumber, ISNULL(E.TargetGroupId, 0) AS ExaminationTargetGroupId, ISNULL(DPM.Title, '') AS EmployeeDesignatedPosition, 
						 ISNULL(E.ContractorId, 0) AS ExaminationContractorId
FROM            sExamination.tExamination AS E INNER JOIN
                         sHrm.tEmployeeMaster AS EM ON EM.Id = E.ParticipantSourceId AND E.ParticipantSourceTypeId = 95 LEFT OUTER JOIN
                         sHrm.tEmployeePersonalDetail AS EPD ON EPD.EmployeeId = EM.Id INNER JOIN
                         sCommon.tGenderMaster AS GM ON GM.Id = EPD.GenderId INNER JOIN
                         sHrm.tEmployeeJobDetail AS EJD ON EM.Id = EJD.EmployeeId LEFT OUTER JOIN
                         sCommon.tOrganizationDepartmentMaster AS D ON D.Id = E.OrganizationDepartmentId AND D.IsObsolete = 0 LEFT OUTER JOIN
                         sHrm.tEmployeeMaster AS EM1 ON EM1.Id = E.DoctorEmployeeId INNER JOIN
                         sExamination.tSetupMaster AS SM ON SM.Id = E.SetupId INNER JOIN
                         sExamination.tSubTypeMaster AS STM ON STM.Id = SM.SubTypeId INNER JOIN
                         sExamination.tMainTypeMaster AS MTM ON STM.MainTypeId = MTM.Id INNER JOIN
                         sIp.tStoreLocationMaster AS SLM ON SLM.Id = E.StoreLocationId INNER JOIN
                         sCommon.tLocationMaster AS LM ON LM.Id = SLM.LocationId INNER JOIN
                         sExamination.tStatusMaster AS S ON S.Id = E.StatusId LEFT OUTER JOIN
                         sHrm.tEmployeeTypeMaster AS ETM ON ETM.Id = EJD.EmployeeTypeId LEFT OUTER JOIN
                         sHrm.tEmployeeMaster AS EM3 ON EM3.Id = E.DoctorEmployeeId LEFT OUTER JOIN
                         sSecurity.tUser AS Users ON Users.Id = E.CreatedUserId LEFT OUTER JOIN
                         sHrm.tTargetGroupMaster AS TGM ON TGM.Id = EM.TargetGroupId LEFT OUTER JOIN
                         sHrm.tEmployeeLegalEntitymaster AS LEM ON LEM.Id = E.LegalEntityId LEFT OUTER JOIN
                         sHrm.tDesignatedPositionMaster AS DPM ON DPM.Id = EJD.DesignatedPositionId
GO
PRINT N'Refreshing View [sExamination].[vExtEmployeeOPDDetail]...';


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


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


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


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


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


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


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


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


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


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, ISNULL(A.MobileNumber,'') AS MobileNumber
	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'Refreshing View [sWellness].[vMobExtPatientWellnessAppointmentDetail]...';


GO
EXECUTE sp_refreshsqlmodule N'[sWellness].[vMobExtPatientWellnessAppointmentDetail]';


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


GO
/******************************************************************************
	Purpose          : External view to get data from sHedp.tPrescriptionDetail table
	Created date     : 27 May 2009
	Author           : System Generated
	Copyright©       : Copyright © Sapphire Software
	Last Change Desc : Added DispensedQuantity
*******************************************************************************/
ALTER VIEW sExamination.vExtPrescriptionDetail
AS
SELECT        PD.Id, PD.PrescriptionId, PD.StockItemId AS DrugStockItemId, SIM.Code AS StockItemCode, SIM.Title AS StockItemTitle, PD.DrugInstruction, PD.Dosage, PD.Duration, PD.IsDispensed, PD.DispensedQuantity, 
                         PD.PrescribedQuantity, PD.IsNotInStore, PD.IsDirectIssue, PD.IsObsolete, SIM.StoreLocationId, PD.DrugDosageFrequencyId, PD.DrugDosageScheduleId, PD.DrugFrequencyPattern
FROM            sExamination.tPrescriptionDetail AS PD INNER JOIN
                         sIp.vExtStockItemMaster AS SIM ON PD.StockItemId = SIM.Id
GO
PRINT N'Altering Function [sExamination].[fExistingDiseaseGet]...';


GO
-- drop function [sExamination].[fExistingDiseaseGet]
ALTER FUNCTION [sExamination].[fExistingDiseaseGet](@rExaminationId bigint)
RETURNS [nvarchar](1000) WITH EXECUTE AS CALLER
AS 
BEGIN
	DECLARE @DiseaseName VARCHAR(1000)
	
	(
		--SELECT @DiseaseName = COALESCE(@DiseaseName,'') + ISNULL(DM.Title,'') + '-' + ISNULL(EDD.Remark,'') + ', '
		SELECT @DiseaseName = COALESCE(@DiseaseName,'') + 
		
		ISNULL(DM.Title,'') + 
CASE 
    WHEN ISNULL(EDD.Remark,'') <> '' 
    THEN '-' + EDD.Remark 
    ELSE '' 
END + ', '

		FROM sExamination.tExamination EAED
		INNER JOIN sExamination.tExaminDisease ED ON ED.SourceId=EAED.Id
		INNER JOIN sExamination.tExaminDiseaseDetail EDD ON EDD.ExaminDiseaseId = ED.Id
		INNER JOIN sExamination.tDiseaseMaster DM ON DM.Id=EDD.DiseaseId
		WHERE ED.SourceId = @rExaminationId AND EDD.Isobsolete=0
	)
	SET @DiseaseName = RTRIM(@DiseaseName);
	SET @DiseaseName = LEFT(@DiseaseName, LEN(@DiseaseName) - 1);  -- Remove the trailing comma and space [Shivam 27-10-2023]
	if isnull(@DiseaseName,'') = ''
	begin
		(
			SELECT @DiseaseName = COALESCE(@DiseaseName,'') + ISNULL(DM.Title,'') 
			+ case when left(@rExaminationId,4)=2001 then '-' + Convert(varchar(10),ISNULL(EDD.Duration,'')) + ' ' + ISNULL(DUM.Title,'') Else '' end +', '
			FROM sExamination.tExistingDiseaseDetail EDD 
			INNER JOIN sExamination.tExistingDisease ED	ON EDD.ExistingDiseaseId = ED.Id 
			INNER JOIN sExamination.tDiseaseMaster DM on EDD.DiseaseId = DM.Id
			LEft Join sCommon.tDurationUnitMaster DUM on DUM.id=EDD.DurationUnitId
			WHERE ED.SourceId = @rExaminationId AND EDD.Isobsolete=0
		)
		SET @DiseaseName = LEFT(@DiseaseName, LEN(@DiseaseName) - 1)  
	end
	RETURN @DiseaseName
END
GO
PRINT N'Creating Function [sExamination].[fn_GetPrescriptionDetails]...';


GO
ALTER FUNCTION [sExamination].[fn_GetPrescriptionDetails]
(
    @SourceId bigint,
    @rOrgId int
)
RETURNS NVARCHAR(MAX)
AS
BEGIN

    DECLARE @lEnableConfigVal varchar(5);            
	SELECT @lEnableConfigVal = sConfig.fConfigurationValueGet('OPD.PrescriptionFrequencyPattern',@rOrgId,0,0,0)  

    DECLARE @Result NVARCHAR(MAX)

    SELECT @Result = STUFF((
        SELECT DISTINCT
            
            'Medicine : ' + ISNULL(SIM.Title, '') + ' | ' +
            'Qty : ' + CAST(ISNULL(CAST (PD.Dosage AS int),0) AS VARCHAR) + ' | ' +
            CASE WHEN @lEnableConfigVal = 'false' THEN ISNULL(DFM.Title,'') ELSE ISNULL(PD.DrugFrequencyPattern, '') END + ' | ' +
            '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'Altering Function [dbo].[fAbnormalFindingsHealthIndicator]...';


GO

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,

            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 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
    )

    SELECT 
        D1.ExaminationId,

        STUFF
        (
            (
                SELECT '<br/>' + HtmlText
                FROM
				(
					SELECT 
						HtmlText,
						CASE 
							WHEN HtmlText LIKE '%color:red%' THEN 1
							WHEN HtmlText LIKE '%color:blue%' THEN 2
							WHEN HtmlText LIKE '%color:orange%' THEN 3
							WHEN HtmlText LIKE '%color:indigo%' THEN 4
							ELSE 5
						END AS SortOrder
					FROM
					(
						SELECT
						/* ================= BMI ================= */
						CASE 
						WHEN Param = 'BMI' AND ISNUMERIC(Value) = 1 THEN
							'<b style="color:' +
							CASE 
								WHEN CAST(Value AS FLOAT) >= 36 THEN 'red'
								WHEN CAST(Value AS FLOAT) BETWEEN 30 AND 35 THEN 'orange'
								WHEN CAST(Value AS FLOAT) BETWEEN 26 AND 29.99 THEN 'indigo'
								ELSE 'black'
							END + '">BMI-' + Value + '</b>'

						/* ================= BP ================= */
						WHEN Param = 'BP' AND ISNUMERIC(Value) = 1 AND ISNUMERIC(Diastolic) = 1 THEN
							'<b style="color:' +
							CASE 
								WHEN CAST(Value AS FLOAT) > 160 OR CAST(Diastolic AS FLOAT) > 110 THEN 'red'
								WHEN CAST(Value AS FLOAT) BETWEEN 150 AND 160 OR CAST(Diastolic AS FLOAT) BETWEEN 100 AND 110 THEN 'orange'
								WHEN CAST(Value AS FLOAT) BETWEEN 140 AND 149 OR CAST(Diastolic AS FLOAT) BETWEEN 90 AND 99 THEN 'indigo'
								ELSE 'black'
							END + '">BP(Sys/Dia)-' + Value + '/' + Diastolic + '</b>'

						/* ================= HB ================= */
						WHEN Param = 'HB' AND ISNUMERIC(Value) = 1 THEN
							'<b style="color:' +
							CASE 
								WHEN CAST(Value AS FLOAT) < 8 AND CAST(Value AS FLOAT) > 0 THEN 'red'
								WHEN CAST(Value AS FLOAT) BETWEEN 8 AND 9.99 THEN 'orange'
								WHEN CAST(Value AS FLOAT) BETWEEN 10 AND 11.99 THEN 'indigo'
								ELSE 'black'
							END + '">HB-' + Value + '</b>'

						/* ================= HBA1C ================= */
						WHEN Param = 'HBA1C' AND ISNUMERIC(Value) = 1 THEN
							'<b style="color:' +
							CASE 
								WHEN CAST(Value AS FLOAT) > 10 THEN 'red'
								WHEN CAST(Value AS FLOAT) BETWEEN 8 AND 10 THEN 'orange'
								WHEN CAST(Value AS FLOAT) BETWEEN 6.5 AND 7.99 THEN 'indigo'
								ELSE 'black'
							END + '">HBA1C-' + Value + '</b>'

						/* ================= SERUM CREATININE ================= */
						WHEN Param = 'SERUM CREATININE' AND ISNUMERIC(Value) = 1 THEN
							'<b style="color:' +
							CASE 
								WHEN CAST(Value AS FLOAT) > 2 THEN 'red'
								WHEN CAST(Value AS FLOAT) BETWEEN 1.5 AND 2 THEN 'orange'
								WHEN CAST(Value AS FLOAT) BETWEEN 1.3 AND 1.49 THEN 'indigo'
								ELSE 'black'
							END + '">SERUM CREATININE-' + Value + '</b>'

						/* ================= SERUM URIC ACID ================= */
						WHEN Param = 'SERUM URIC ACID' AND ISNUMERIC(Value) = 1 THEN
							'<b style="color:' +
							CASE 
								WHEN CAST(Value AS FLOAT) > 9.5 THEN 'red'
								WHEN CAST(Value AS FLOAT) BETWEEN 7.7 AND 9.4 THEN 'orange'
								WHEN CAST(Value AS FLOAT) BETWEEN 6.5 AND 7.69 THEN 'indigo'
								ELSE 'black'
							END + '">SERUM URIC ACID-' + Value + '</b>'

						/* ================= TRIGLYCERIDE ================= */
						WHEN Param = 'TRIGLYCERIDE' AND ISNUMERIC(Value) = 1 THEN
							'<b style="color:' +
							CASE 
								WHEN CAST(Value AS FLOAT) > 500 THEN 'red'
								WHEN CAST(Value AS FLOAT) BETWEEN 200 AND 500 THEN 'orange'
								WHEN CAST(Value AS FLOAT) BETWEEN 150 AND 199.99 THEN 'indigo'
								ELSE 'black'
							END + '">TRIGLYCERIDE-' + Value + '</b>'

						/* ================= LDL ================= */
						WHEN Param = 'LDL' AND ISNUMERIC(Value) = 1 THEN
							'<b style="color:' +
							CASE 
								WHEN CAST(Value AS FLOAT) > 200 THEN 'red'
								WHEN CAST(Value AS FLOAT) BETWEEN 150 AND 200 THEN 'orange'
								WHEN CAST(Value AS FLOAT) BETWEEN 101 AND 149.99 THEN 'indigo'
								ELSE 'black'
							END + '">LDL-' + Value + '</b>'

						/* ================= TSH ================= */
						WHEN Param = 'TSH' AND ISNUMERIC(Value) = 1 THEN
							'<b style="color:' +
							CASE 
								WHEN CAST(Value AS FLOAT) > 50 THEN 'red'
								WHEN CAST(Value AS FLOAT) BETWEEN 11 AND 50 THEN 'orange'
								WHEN CAST(Value AS FLOAT) BETWEEN 5.6 AND 10.99 THEN 'indigo'
								ELSE 'black'
							END + '">TSH-' + Value + '</b>'

						/* ================= PSA ================= */
						WHEN Param = 'PSA' AND ISNUMERIC(Value) = 1 THEN
							'<b style="color:' +
							CASE 
								WHEN CAST(Value AS FLOAT) > 10 THEN 'red'
								WHEN CAST(Value AS FLOAT) > 5 THEN 'orange'
								ELSE 'black'
							END + '">PSA-' + Value + '</b>'

						/* ================= BLUE ================= */
						WHEN Param IN ('TMT','ECHO','ECG','SONOGRAPHY','XRAY')
						THEN '<b style="color:' +
							CASE 
								WHEN Value IN ('Positive','Abnormal') THEN 'blue'
								ELSE 'black'
							END + '">' + Param + '-' + Value + '</b>'
						END AS HtmlText

						FROM DataCTE
						CROSS APPLY
						(
							VALUES
							('BMI', CAST(BMI AS NVARCHAR(50)), NULL),
							('BP', CAST(BloodPressureSystolic AS NVARCHAR(50)), CAST(BloodPressureDiastolic AS NVARCHAR(50))),
							('HB', HB, NULL),
							('HBA1C', HBA1C, NULL),
							('SERUM CREATININE', [SERUM CREATININE], NULL),
							('SERUM URIC ACID', [SERUM URIC ACID], NULL),
							('TRIGLYCERIDE', [TRIGLYCERIDE], NULL),
							('LDL', LDL, NULL),
							('TSH', TSH, NULL),
							('PSA', PSA, NULL),
							('TMT', TMT, NULL),
							('ECHO', ECHO, NULL),
							('ECG', ECG, NULL),
							('SONOGRAPHY', SONOGRAPHY, NULL),
							('XRAY', XRAY, NULL)
						) P(Param, Value, Diastolic)

						WHERE Value IS NOT NULL AND LTRIM(RTRIM(Value)) <> ''
					) A
					WHERE HtmlText IS NOT NULL
				) B
				ORDER BY SortOrder
				FOR XML PATH(''), TYPE
				).value('.', 'NVARCHAR(MAX)')
			,1,5,'') AS AbnormalFindingHtml

    FROM DataCTE D1
    GROUP BY D1.ExaminationId
);
GO
PRINT N'Refreshing Function [dbo].[fn_GetTestProfileServiceValue_Inline]...';


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