
Declare @OrgId int = (select ID from sCommon.tCompanyInformation Where id > 100);
INSERT INTO sSecurity.tScreenValidation (OrgId, FieldId, IsShow, IsMandatory)
SELECT * FROM (VALUES
	(@OrgId, 19,0,0),  
	(@OrgId, 20,0,0),
	(@OrgId, 21,0,0),
	(@OrgId, 22,0,0),
	(@OrgId, 23,0,0),
	(@OrgId, 24,0,0),
	(@OrgId, 25,0,0),
	(@OrgId, 26,0,0),
	(@OrgId, 27,0,0),
	(@OrgId, 28,0,0),
	(@OrgId, 29,0,0),
	(@OrgId, 30,0,0),
	(@OrgId, 31,0,0),
	(@OrgId, 32,0,0),
	(@OrgId, 33,0,0),
	(@OrgId, 34,0,0),
	(@OrgId, 35,0,0),
	(@OrgId, 36,0,0),
	(@OrgId, 37,0,0),
	(@OrgId, 38,0,0),
	(@OrgId, 39,0,0),
	(@OrgId, 40,0,0),
	(@OrgId, 41,0,0),
	(@OrgId, 42,0,0),
	(@OrgId, 43,0,0),
	(@OrgId, 44,0,0)
) AS Temp(OrgId, FieldId, IsShow, IsMandatory)
WHERE NOT EXISTS (
    SELECT 1
    FROM sSecurity.tScreenValidation AS Master
    WHERE Master.OrgId = Temp.OrgId
      AND Master.FieldId = Temp.FieldId
);

GO

INSERT INTO sSecurity.tScreenValidationFieldMaster (ScreenId, FieldId, FieldName)
SELECT * FROM (VALUES 
    (20631, 1, 'Doctor'),
    (20523, 2, 'Doctor'),
    (20631, 3, 'Referral Hospital'),
    (20523, 4, 'Location'),
    (20629, 5, 'Remark'),
	(9014, 6, 'Worker Unique Type'),  
	(9014, 7, 'Worker Unique Id'),
	(9014, 8, 'Email Address'),
	(9093, 9, 'Trip Type'),
	(9014, 10, 'Other Name'),
	(9014, 11, 'Age(Year/Month)'),
	(9014, 12, 'Gender'),
	(9014, 13, 'Reference'),
	(9014, 14, 'Other Sub Group'),
	(9014, 15, 'Waive Off'),
	(9014, 16, 'Pediatric'),
	(20631,17,'Nature & extent of sickness'),
	(20631,18,'Location'),
	(20379,40,'Doctor'),
	(20252,41,'Doctor'),
	(20252,42,'Medical Agency'),
	(9014,43,'Mobile Number'),
	(20892,44,'Mobile Number')
) AS Temp(ScreenId, FieldId, FieldName)
WHERE NOT EXISTS (
    SELECT 1 
    FROM sSecurity.tScreenValidationFieldMaster AS Master
    WHERE Master.FieldId = Temp.FieldId 
      AND Master.ScreenId = Temp.ScreenId
      AND Master.FieldName = Temp.FieldName
);

GO


ALTER PROC [sExamination].[pGetTestResultsExaminationAllNg]
@rExaminationId bigint,
@rOrgId int,
@rSetupId int,
@rSourceTypeId int,
@rProcessId int,
@rFlag int,
@rFlagDefault int = null,
@rTargetGroupId int = null,
@rAge int = 0,
@rGenderId int = 0
AS 
BEGIN 

declare @rPatientId bigint,@lMainTypeId int, @rConfigBloodGroup int = 0,@rEMPId bigint, @TitleBloodGroup NVARCHAR(20), @IdBloodGroup int; 
	select @rConfigBloodGroup = 
		CASE
			WHEN sConfig.fConfigurationValueGet('Examination.BloodGroupCarryForwardFromHR',@rOrgId,0,0,0) = 'True' 
			THEN 1 ELSE 0 
		END
	select @lMainTypeId = ST.MainTypeId from sExamination.tExamination E inner join sExamination.tSetupMaster SM
	on E.SetupId = SM.Id inner join sExamination.tSubTypeMaster ST on SM.SubTypeId = ST.Id
	where E.Id = @rExaminationId

	select @rEMPId = ParticipantSourceId from sExamination.tExamination where Id = @rExaminationId	
	
	SET @TitleBloodGroup = (select top 1 BGM.Title from sHrm.tEmployeePersonalDetail EPD 
	Inner Join sExamination.tExamination E2 on E2.ParticipantSourceId = EPD.EmployeeId
	Inner Join sCommon.tBloodGroupMaster BGM on BGM.Id = EPD.BloodGroupId
	where E2.Id = @rExaminationId)

	SET @IdBloodGroup = (select top 1 Id from sOrderEntry.tListValueMaster 
	where Title = @TitleBloodGroup and ListValueTypeId = sCommon.fGetIntIdForOrg(4,@rOrgId))

	if(@rAge = 0 AND @rGenderId = 0)
	BEGIN
		if @rExaminationId > 0
		begin
			select @rPatientId = ParticipantSourceId from sExamination.tExamination where Id = @rExaminationId	
			select @rAge = DATEDIFF(yy,DateOfBirth,GETDATE()),@rGenderId = GenderId from sHrm.vEmployeeCommonDetail where EmployeeId = @rPatientId
		end
		else
		begin
			select @rAge = DATEDIFF(yy,DateOfBirth,GETDATE()),@rGenderId = GenderId from sHrm.vEmployeeCommonDetail where EmployeeId = @rPatientId
		end	
	END
select 
 ISNULL(TM.Id,0) as TestId,
 ISNULL(TP.Id,0) as TestParameterId,
 isnull(TRR.TestRequestId,0) as TestRequestId,
isnull(TRR.Id,0) as TestRequestDetailId,
isnull(PVTM.Id,0) as ParameterValueTypeId,
CASE WHEN ROW_NUMBER() OVER(PARTITION BY TM.Title order by TP.OrderIndex) = 1 THEN TM.Title ELSE '' END as AllergyType,
isnull(TP.Title,'') as Parameter,
case when PVTM.Id = 1 then 
	case when @rExaminationId = 0 then '' else isnull(TRR.ServiceValue,'') end  
	when PVTM.Id = 2 then isnull(sExamination.fGetTestResultsDropDownsNg(TP.ListValueTypeId,case when @rExaminationId = 0 then '' else isnull(TRR.ServiceValue,'') end,@rOrgId),0)
	when PVTM.Id = 3 then 
	case when @rExaminationId = 0 then '' else
	case when isnull(TRR.ServiceValue,'') = '' then '' else isnull(TRR.ServiceValue,'') end end 
	else '' end 
	as NumericResult,
	case when PVTM.Id = 1 then 
	case when @rExaminationId = 0 then '' else isnull(TRR.ServiceValue,'') end  
	when PVTM.Id = 2 then (Case When TRRC.BloodCount > 0 and @rConfigBloodGroup = 1  
	and isnull(sExamination.fGetSelectedTestResultsDropDownsNg(TP.ListValueTypeId,case when @rExaminationId = 0 then '' else isnull(TRR.ServiceValue,'') end,@rOrgId),0) = 0
	Then Cast(isnull(@IdBloodGroup, 0) as nvarchar(10)) ELSE
	isnull(sExamination.fGetSelectedTestResultsDropDownsNg(TP.ListValueTypeId,case when @rExaminationId = 0 then '' else isnull(TRR.ServiceValue,'') end,@rOrgId),0) END)
	when PVTM.Id = 3 then
	case when @rExaminationId = 0 then '' else
	case when isnull(TRR.ServiceValue,'') = '' then '' else isnull(TRR.ServiceValue,'') end end 
	else '' end 
	as NumericResultSelectedValue,
	case when PVTM.Id = 1 then 
	case when @rExaminationId = 0 then '' else isnull(TRR.ServiceValue,'') end  
	when PVTM.Id = 2 then (Case When TRRC.BloodCount > 0 and @rConfigBloodGroup = 1 
	and isnull(sExamination.fGetSelectedTestResultsDropDownsNg(TP.ListValueTypeId,case when @rExaminationId = 0 then '' else isnull(TRR.ServiceValue,'') end,@rOrgId),0) = 0
	Then isnull(@TitleBloodGroup, '') ELSE
	isnull(sExamination.fGetTestResultsDropDownSelectedTextNg(TP.ListValueTypeId,case when @rExaminationId = 0 then '' else isnull(TRR.ServiceValue,'') end,@rOrgId),0) END)
	when PVTM.Id = 3 then 
	case when @rExaminationId = 0 then '' else
	case when isnull(TRR.ServiceValue,'') = '' then '' else isnull(TRR.ServiceValue,'') end end 
	else '' end 
	as NumericResultSelectedText,
PVTM.Title  as ParmeterType,
case when @rExaminationId = 0 then '' else isnull(TRR.Remark,'') end as TemplateResultRemark,

case when TP.StaticTestParameterId <> 57 then
	case when TP.ParameterValueTypeId = 1 then [sExamination].[fGetParameterRange](TP.Id,@rAge,@rGenderId) else '' end
	else '' end as openDilution,
	
	LVTM.Id,
	@rAge as age,
	@rGenderId as gender,
	ISNULL(TP.StaticTestParameterId,0) as StaticTestParameterId
from sOrderEntry.tTestMaster TM
	inner join sOrderEntry.tTestDetail TD on TD.TestId = TM.Id
	inner join sOrderEntry.tTestParameterMaster TP on TP.Id = TD.TestParameterId	
	inner join sExamination.tTargetGroupExaminationTemplate SA on TP.Id = SA.SourceParameterId
	--left join sOrderEntry.tTestParameterDetail TPD on TPD.TestParameterId=TP.Id and TPD.GenderId=@rGenderId and TPD.IsNormal=1
	left join sOrderEntry.tParameterValueTypeMaster PVTM on PVTM.Id = TP.ParameterValueTypeId
	left join sOrderEntry.tListValueTypeMaster LVTM on (LVTM.Id = TP.ListValueTypeId) AND TP.Id = TD.TestParameterId	
	left join
	(
		select TR.Id as TestRequestId,TR.TestId,TRD.Id,
		TRD.TestDetailId,TRD.ServiceValue,TRD.ServiceResult,TRD.Remark		
		from sExamination.tTestRequest TR
		inner join sExamination.tTestRequestDetail TRD
		on TR.Id = TRD.TestRequestId
		where TR.SourceId = @rExaminationId
		and TR.SourceTypeId = @rSourceTypeId
		and TR.OrgId = @rOrgId and TRD.OrgId = @rOrgId
	) TRR on TRR.TestDetailId = TD.Id and TRR.TestId = TD.TestId
	left join
	(
		select TD.Id, TD.TestId, COUNT(TD.Id) as BloodCount from sOrderEntry.tTestDetail TD
		LEFT Join sOrderEntry.tTestParameterMaster TPM ON TPM.Id = TD.TestParameterId
		where TPM.StaticTestParameterId = 1
		Group By TD.Id, TD.TestId
	) TRRC on TRRC.Id = TD.Id and TRRC.TestId = TD.TestId
	
	where TM.OrgId = @rOrgId
	and isnull(SA.TargetGroupId,0) = @rTargetGroupId -- Mantis- 0038727 by- Shivam on  19/12/2024
	--and isnull(SA.TargetGroupId,0) = case when @lMainTypeId = 7 then 0 else @rTargetGroupId end
	and SA.MainTypeId = @rSetupId
	and SA.SourceTemplateId = 118 
	and ISNULL(TP.StaticTestParameterId,0) <> case when @rGenderId = 1 then 111 else 58 end -- for mentis issue no:8803 (2019-05-22)
	and ISNULL(TP.StaticTestParameterId,0) <> case when @rGenderId = 1 then 122 else 58 end -- for mentis issue no:8803 (2019-05-22)
	and ISNULL(TP.StaticTestParameterId,0) <> case when @rGenderId = 1 then 241 else 58 end -- for mantis 0049101
	order by TM.Title,isnull(TP.OrderIndex,0)	
	
END

GO


ALTER PROCEDURE [sExamination].[pServiceListingResultAllGet]
@rSourceId [bigint],
@rOrgId [smallint],	
@rSourceTypeId [smallint],
@rTestId int = 0,
@rGenderId int = 1,
@rAge int = 0,	 
@rParameterId int = 0
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	declare @ParticipantSourceTypeId int,@EmployeeId bigint,@setupID int,@TargetGroup int,@rMainTypeId int,@lConfValue varchar(10)
	select @rGenderId = CASE when E.ParticipantSourceTypeId=95 then EM.GenderId when (E.ParticipantSourceTypeId=1009 and o.genderid is null) then BM.GenderId when (E.ParticipantSourceTypeId=1009 and ISNULL(o.GenderId,0)<>0)  then o.GenderId when E.ParticipantSourceTypeId=1001 then EFD.GenderId END,
	@rAge = CASE when E.ParticipantSourceTypeId=95 then datediff(yy,ISNULL(EM.DateOfBirth,''),sCommon.fGetDate())
	when (E.ParticipantSourceTypeId=1009 and Isnull(o.Age,'')='') then datediff(yy,ISNULL(BM.DateOfBirth,''),sCommon.fGetDate()) when (E.ParticipantSourceTypeId=1009 and Isnull(o.Age,'')<>'') then o.Age when E.ParticipantSourceTypeId=1001 then datediff(yy,ISNULL(EFD.DateOfBirth,''),sCommon.fGetDate()) end,
	@ParticipantSourceTypeId = ParticipantSourceTypeId,
	@EmployeeId = ParticipantSourceId,
	@setupID = E.SetupId,@TargetGroup = E.TargetGroupId,@rMainTypeId = sT.MainTypeId
	from sExamination.tExamination E
	inner join sExamination.tSetupMaster sM on E.SetupId = sM.Id
	inner join sExamination.tSubTypeMaster sT on sM.SubTypeId = sT.Id
	left join sExamination.tOpd o on o.ExaminationId=E.Id
	left join sHrm.tEmployeePersonalDetail EM on E.ParticipantSourceId = EM.EmployeeId and E.ParticipantSourceTypeId = 95
	left join sHrm.tCsrBenefiterMaster BM on E.ParticipantSourceId = BM.Id and E.ParticipantSourceTypeId = 1009
	left join sHrm.tEmployeeFamilyDetails EFD on E.ParticipantSourceId = EFD.Id and E.ParticipantSourceTypeId = 1001
	where E.Id = @rSourceId
	
	if @ParticipantSourceTypeId = 1009
	begin
		select @rAge = isnull(Age,0),@rGenderId = Isnull(GenderId,0) from sExamination.tOPD where ExaminationId = @rSourceId				
		if (@rAge = 0)
		begin
			select @rAge = datediff(yy,ISNULL(DateOfBirth,''),sCommon.fGetDate()) from sHrm.tCSRBenefiterMaster where Id = @EmployeeId
			select @rAge = isnull(@rAge,0)
		end
		if (@rGenderId=0)
		begin
			select @rGenderId = GenderId from sHrm.tCSRBenefiterMaster where Id = @EmployeeId			
		end
		select @TargetGroup = 0
	end
	else if @ParticipantSourceTypeId = 1001
	begin
		select @rGenderId = GenderId, @rAge = datediff(yy,ISNULL(DateOfBirth,''),sCommon.fGetDate()) from sHrm.tEmployeeFamilyDetails where Id = @EmployeeId
		select @TargetGroup = 0
	end
	if @rMainTypeId = 7
	begin
		select @TargetGroup = 0
	end
print @rAge		
print @rGenderId

		select @lConfValue = Isnull(CS.ItemValue,CM.ItemValue) 
		from sConfig.tConfigurationMaster CM
		LEFT JOIN sConfig.tConfigurationSetup CS ON CS.ConfigurationId = CM.Id 
		where CM.ItemKey = 'Examination.IsTestAutoSaveinVital'

		if((@lConfValue='True') and (@setupID<>[sCommon].[fGetIntIdForOrg](8,@rOrgId)))
		begin
			exec [sExamination].[pSaveTestResultsFirst] @rSourceId,@rMainTypeId,@setupID,1,@TargetGroup
		end

		print @setupID

if(@setupID=[sCommon].[fGetIntIdForOrg](8,@rOrgId))
begin
	SELECT Distinct TRD.Id as TestRequestDetailId,
	PVTM.Id as ParameterValueTypeId,
	ISNULL(TPM.ListValueTypeId,0) as ListValueTypeId,
	TPM.Title as ParmeterName,
	TPM.Code,TPM.Id AS TestParameterId,
	PVTM.Title as ParaMeterType,
	RTRIM(Ltrim(ISNULL(TRD.ServiceValue,''))) as ServiceValue,
	ISNULL(TRD.ServiceResult,'') as ServiceResult,
	ISNULL(TPR.AgeFrom,0) as AgeFrom,
	ISNULL(TPR.AgeTo,0) as AgeTo,
	TPR.Minimum as Minimum,
	TPR.Maximum as Maximum,
	ISNULL(TRD.Remark,'') AS Remark,
	ISNULL(TR.Remark,'') AS RemarkMain,
	SM.Title AS TestName,
	SM.Id AS TestId,
	ISNULL(TPR.IsNormal,0) AS IsNormal,
	ISNULL(TPM.ClinicalUOM,'') AS ClinicalUOM,
	1 AS IsDone,
	CASE WHEN TR.SourceId IS NULL THEN 0 ELSE 1 END AS IsExist,
	TPM.NormalRange,
	[sExamination].[fGetParameterRange](TPM.Id,@rAge,@rGenderId) as [Range],
	--[sExamination].[fGetNormalTestParameterRange](TPM.Id,@rAge,@rGenderId) as [Range],
	CASE WHEN ROW_NUMBER() OVER(PARTITION BY SM.Title order by TD.OrderIndex) = 1 
    THEN isnull(TR.RequestRemark,'') else '' end as RequestRemark,
    CASE WHEN ROW_NUMBER() OVER(PARTITION BY SM.Title order by TD.OrderIndex) = 1 
    THEN isnull(TR.TestSpecificationId,0) else 0 end as TestSpecificationId,
    CASE WHEN ROW_NUMBER() OVER(PARTITION BY SM.Title order by TD.OrderIndex) = 1 
    THEN isnull(TS.Specification,'') else '' end as Specification,
    TPM.StaticTestParameterId,
	isnull(TPM.OrderIndex,0) OrderIndex
	FROM sExamination.tTestRequest TR
	INNER JOIN sOrderEntry.tTestMaster SM ON TR.TestId = SM.Id	
	INNER JOIN sExamination.tTestRequestDetail TRD ON TRD.TestRequestId=TR.Id AND isnull(TR.IsObsolete,0) = 0
	INNER JOIN sOrderEntry.tTestDetail TD ON TRD.TestDetailId=TD.Id and TR.TestId = TD.TestId
	INNER JOIN sOrderEntry.tTestParameterMaster TPM ON TPM.Id=TD.TestParameterId
	--left JOIN sExamination.tTargetGroupExaminationTemplate TGT on TGT.SourceParameterId = TPM.Id AND TGT.SourceTemplateId = 118 and TGT.TargetGroupId = isnull(@TargetGroup,0) 
	LEFT JOIN sOrderEntry.tParameterValueTypeMaster PVTM ON PVTM.Id=TPM.ParameterValueTypeId
	LEFT JOIN sOrderEntry.tTestSpecification TS on TS.Id = TR.TestSpecificationId
	LEFT JOIN sOrderEntry.tListValueTypeMaster LVTM ON LVTM.Id = TPM.ListValueTypeId AND TPM.Id = TD.TestParameterId and LVTM.OrgId = @rOrgId
	LEFT JOIN
	(
		SELECT top 1 R.TestParameterId,R.AgeFrom,R.AgeTo,R.Minimum,R.Maximum,R.IsNormal AS IsNormal
		FROM sOrderEntry.tTestParameterDetail R WHERE R.AgeFrom <= @rAge AND R.AgeTo >= @rAge AND R.GenderId = @rGenderId AND IsNormal = 1
	) TPR ON TPM.Id = TPR.TestParameterId AND TPM.ParameterValueTypeId = 1			  				
	WHERE TR.OrgId = @rOrgId AND TR.SourceTypeId = @rSourceTypeId AND TR.SourceId = @rSourceId And ((TR.TestId = @rTestId) OR (@rTestId = 0)) 
	AND ((TPM.Id = @rParameterId) OR (@rParameterId = 0)) and SM.IsObsolete = 0
	and SM.StaticTestId <> case when @rGenderId = 1 then 1032 else 1013 end -- for mentis issue no:8803 (2019-05-22)
	and SM.StaticTestId <> case when @rGenderId = 1 then 1027 else 1013 end -- for mentis issue no:8803 (2019-05-22)	
	--and (1=(case when @setupID=[sCommon].[fGetIntIdForOrg](8,@rOrgId) then 1 else 0 end) or (TGT.MainTypeId = @setupID))
	ORDER BY SM.Title,isnull(TPM.OrderIndex,0)
END
Else
Begin
		SELECT Distinct TRD.Id as TestRequestDetailId,
	PVTM.Id as ParameterValueTypeId,
	ISNULL(TPM.ListValueTypeId,0) as ListValueTypeId,
	TPM.Title as ParmeterName,
	TPM.Code,TPM.Id AS TestParameterId,
	PVTM.Title as ParaMeterType,
	RTRIM(Ltrim(ISNULL(TRD.ServiceValue,''))) as ServiceValue,
	ISNULL(TRD.ServiceResult,'') as ServiceResult,
	ISNULL(TPR.AgeFrom,0) as AgeFrom,
	ISNULL(TPR.AgeTo,0) as AgeTo,
	TPR.Minimum as Minimum,
	TPR.Maximum as Maximum,
	ISNULL(TRD.Remark,'') AS Remark,
	SM.Title AS TestName,
	SM.Id AS TestId,
	ISNULL(TPR.IsNormal,0) AS IsNormal,
	ISNULL(TPM.ClinicalUOM,'') AS ClinicalUOM,
	1 AS IsDone,
	CASE WHEN TR.SourceId IS NULL THEN 0 ELSE 1 END AS IsExist,
	TPM.NormalRange,
	[sExamination].[fGetParameterRange](TPM.Id,@rAge,@rGenderId) as [Range],
	--[sExamination].[fGetNormalTestParameterRange](TPM.Id,@rAge,@rGenderId) as [Range],
	CASE WHEN ROW_NUMBER() OVER(PARTITION BY SM.Title order by TD.OrderIndex) = 1 
    THEN isnull(TR.RequestRemark,'') else '' end as RequestRemark,
    CASE WHEN ROW_NUMBER() OVER(PARTITION BY SM.Title order by TD.OrderIndex) = 1 
    THEN isnull(TR.TestSpecificationId,0) else 0 end as TestSpecificationId,
    CASE WHEN ROW_NUMBER() OVER(PARTITION BY SM.Title order by TD.OrderIndex) = 1 
    THEN isnull(TS.Specification,'') else '' end as Specification,
    TPM.StaticTestParameterId,
	isnull(TPM.OrderIndex,0) OrderIndex
	FROM sExamination.tTestRequest TR
	INNER JOIN sOrderEntry.tTestMaster SM ON TR.TestId = SM.Id	
	INNER JOIN sExamination.tTestRequestDetail TRD ON TRD.TestRequestId=TR.Id AND isnull(TR.IsObsolete,0) = 0
	INNER JOIN sOrderEntry.tTestDetail TD ON TRD.TestDetailId=TD.Id and TR.TestId = TD.TestId
	INNER JOIN sOrderEntry.tTestParameterMaster TPM ON TPM.Id=TD.TestParameterId
	left JOIN sExamination.tTargetGroupExaminationTemplate TGT on TGT.SourceParameterId = TPM.Id AND TGT.SourceTemplateId = 118 and TGT.TargetGroupId = isnull(@TargetGroup,0) 
	LEFT JOIN sOrderEntry.tParameterValueTypeMaster PVTM ON PVTM.Id=TPM.ParameterValueTypeId
	LEFT JOIN sOrderEntry.tTestSpecification TS on TS.Id = TR.TestSpecificationId
	LEFT JOIN sOrderEntry.tListValueTypeMaster LVTM ON LVTM.Id = TPM.ListValueTypeId AND TPM.Id = TD.TestParameterId and LVTM.OrgId = @rOrgId
	LEFT JOIN
	(
		SELECT top 1 R.TestParameterId,R.AgeFrom,R.AgeTo,R.Minimum,R.Maximum,R.IsNormal AS IsNormal
		FROM sOrderEntry.tTestParameterDetail R WHERE R.AgeFrom <= @rAge AND R.AgeTo >= @rAge AND R.GenderId = @rGenderId AND IsNormal = 1
	) TPR ON TPM.Id = TPR.TestParameterId AND TPM.ParameterValueTypeId = 1			  				
	WHERE TR.OrgId = @rOrgId AND TR.SourceTypeId = @rSourceTypeId AND TR.SourceId = @rSourceId And ((TR.TestId = @rTestId) OR (@rTestId = 0)) 
	AND ((TPM.Id = @rParameterId) OR (@rParameterId = 0)) and SM.IsObsolete = 0
	and SM.StaticTestId <> case when @rGenderId = 1 then 1032 else 1013 end -- for mentis issue no:8803 (2019-05-22)
	and SM.StaticTestId <> case when @rGenderId = 1 then 1027 else 1013 end -- for mentis issue no:8803 (2019-05-22)	
	and TGT.MainTypeId = @setupID
	ORDER BY SM.Title,isnull(TPM.OrderIndex,0)
END
END

GO


ALTER PROCEDURE [sExamination].[pRptOpdPrescriptionList]
@rBuilder1 nvarchar(max)='',
@rBuilder2 nvarchar(max)='',
@rReportId int,
@rStoreLocationId int,
@rParticipantTypeId int,
@rCaseCauseId int,
@rLocationId int,
@rFunctionId int,
@rDepartmentGroupId int,
@rDepartmentId int,
@rGradeId int,
@rDesignationId int,
@rJobTitleId int,
@rEmployeeTypeId int,
@rCompanyId int,
@rContractorId int,
@rGenderId smallint,
@rStatusId int,
@rAgeFrom int=0,
@rAgeTo int=100,
@rFromDate date = null,
@rToDate datetime = null,
@rOrgId int,
@rEmployeeCode nvarchar(50)='',
@rEmployeeTypeGroupId int=0,
@rEmployeeSubTypeId int=0,
@rEmployeeGroupId int=0,
@rOtherTypeId int=0,
@rExaminationId bigint=0,
@rLogo bit = 0
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN 

	-- Validate @rEmployeeCode entries
    IF EXISTS 
    (
        SELECT SplitValues.item
        FROM sCommon.fnsplit(@rEmployeeCode, ',') AS SplitValues
        WHERE NOT EXISTS 
        (
            SELECT 1 FROM sHrm.tEmployeeMaster EM WHERE EM.Code = SplitValues.item
            UNION
            SELECT 1 FROM sHrm.tCsrBenefiterMaster BM WHERE BM.Code = SplitValues.item
        )
    )
    BEGIN
        RAISERROR('One or more Employee Codes are invalid / Not Exists.', 16, 1);
        RETURN;
    END

	select @rAgeTo = case when @rAgeTo = '' or @rAgeTo = 100 then 1000 end
	select @rAgeFrom = case when @rAgeFrom = '' then 0 end	

	Declare @rConfig varchar(10), @rConfig1 varchar(10);

	Set @rConfig = (select sConfig.fConfigurationValueGet('Examination.IsTempChangetoCelsius',@rOrgId,0,0,0))

	Set @rConfig1 = (case @rConfig When 'true' Then '°C' Else '°F' END)

	--DECLARE @TestTable TABLE (TestName varchar(200))
	--declare @results varchar(Max) 

	--INSERT INTO @TestTable
	--select TPM.Title from sExamination.tTestRequest TR
	--inner join sOrderEntry.tTestDetail OTD on TR.TestId = OTD.TestId
	--inner join sExamination.tTestRequestDetail TD on TR.Id = TD.TestRequestId and TD.TestDetailId = OTD.Id
	--inner join  sOrderEntry.tTestParameterMaster TPM on TPM.Id = OTD.TestParameterId 
	--where  TR.OrgId = @rOrgId and sourceid = @rExaminationId
	--Order by TPM.Title

	--select @results = coalesce(@results + ', ', '') +  convert(varchar(200),TestName) from @TestTable

	select EED.Id AS ExaminationId,
	EP.Id as prescriptionId,
	ParticipantCode = CASE EED.ParticipantSourceTypeId WHEN 95 THEN EM.Code WHEN 1001 THEN EM2.Code WHEN 1009 THEN BM.Code END,
	ParticipantName = CASE EED.ParticipantSourceTypeId WHEN 1009 THEN
	CASE WHEN isnull(O.OtherName,'')='' THEN
	isnull(BM.FirstName,'') + ' ' + isnull(BM.LastName,'') ELSE O.OtherName END
	WHEN 95 THEN EM.FirstName + ' ' + EM.LastName WHEN 1001 THEN EFD.Name END,
	PatientCategory = CASE EED.ParticipantSourceTypeId WHEN 95 THEN isnull(ETM.Title,'') WHEN 1001 THEN isnull(RM.Title,'') WHEN 1009 THEN isnull(BTM.Title,'') END,
	--Gender = CASE EED.ParticipantSourceTypeId
	--WHEN 95 THEN GM.Title
	--WHEN 1001 THEN ISNULL([sCommon].[fGenderFromRelationIdGet](EFD.RelationshipId),'')
	--WHEN 1009 THEN CASE WHEN isnull(O.GenderId,0) = 0 THEN GM2.Title ELSE isnull((SELECT Title FROM sCommon.tGenderMaster where O.GenderId = Id),'') END END,
	Gender = CASE EED.ParticipantSourceTypeId
	WHEN 95 THEN GM.Title
	WHEN 1001 THEN ISNULL(GM3.Title,'')
	WHEN 1009 THEN CASE WHEN isnull(O.GenderId,0) = 0 THEN GM2.Title ELSE isnull((SELECT Title FROM sCommon.tGenderMaster where O.GenderId = Id),'') END END,
	
	--EED.CreatedDateTime as ExaminationDate,
	
	case when isnull(O.OPDTime,'')='' then convert(varchar,EED.CreatedDateTime,103) else convert(varchar,EED.CreatedDateTime,103)  end as ExaminationDate,
	isnull(O.OPDOutTime,'') as OPDOutTime,
	
	Age = CASE EED.ParticipantSourceTypeId WHEN 95 THEN isnull(datediff(yy,PD.DateOfBirth,sCommon.fGetDate()),0)
	WHEN 1001 THEN isnull(datediff(yy,EFD.DateOfBirth,sCommon.fGetDate()),0) WHEN 1009 THEN isnull(O.Age,isnull(datediff(yy,BM.DateOfBirth,sCommon.fGetDate()),0)) end,
	V.Pulse,
	convert(varchar(20),V.Temperature) + convert(varchar(20),@rConfig1) as Temperature,
	V.BloodPressureSystolic,
	V.BloodPressureDiastolic,
	R.ContactPerson,
	O.OutsideMedicine,
	O.OutsideTest,
	[sExamination].[fExistingDiseaseGet](EED.Id) AS Diagnosis,
	(select top 1 isnull(Remark,'') from sExamination.tComplaint where SourceId = EED.Id) as problems,
	case when isnull(EPD.DrugInstruction,'') = '' then SM.Title else EPD.DrugInstruction end as DrugInstruction,
	SIM.Title as Medicines,
	convert(varchar(20),EPD.Dosage) as MedicineQty,
	convert(varchar(20),EPD.DispensedQuantity)DispensedQuantity,
	convert(varchar,EPD.Duration)Duration,
	convert(varchar(20),EPD.PrescribedQuantity)PrescribedQuantity,
	EED.ParticipantSourceId,
	Isnull(SIM.Id,0) as StockId,
	SIM.DrawSize,
	DFM.Title as MedicineDosage,
	convert(varchar(20),(EPD.PrescribedQuantity-EPD.DispensedQuantity)) as RemainingQty,	
	'' as UpdatedDateTime,
	CASE 
		WHEN D.EmployeeId IS NOT NULL THEN (CASE 
                WHEN ISNULL(LTRIM(RTRIM(EM3.EmployeePrefix)), '') = '' 
                    THEN '' 
                    ELSE EM3.EmployeePrefix + ' ' 
              END) + EM3.FirstName + ' ' + EM3.LastName
		WHEN VD.Id IS NOT NULL THEN 'Dr. ' + VD.FirstName + ' ' + VD.LastName
		ELSE ''																																									
	END AS RefferedName,
	Convert(varchar,D.MedicalRegistrationNo)MedicalRegistrationNo,
	ISNULL(EED.ExaminationNumber,'') as ExaminationNumber,
	ISNULL(CM.Title,'') as CauseTitle,
	isnull(OD.Title,'') as DepartmentName,
	--ISNULL(D.DoctorSignature, CONVERT(VARBINARY(MAX), 0))AS DoctorSignature,
	D.DoctorSignature AS DoctorSignature,
	CASE WHEN @rLogo = 0 THEN null 
    ELSE CID.Logo END AS Logo,
	 isnull(LM.SmallAddress1,'') as SmallAddress1,
	isnull(LM.SmallAddress2,'') as SmallAddress2,
	CI.Title as CompanyName,
	isnull(V.Remark,'') as Advice,
	--case when Isnull(SIM.Isnotinstore,0)= 1 then 'Outside' else --commented this for adani prescription report
	isnull(O.FurtherActionRemark,'') 
	--end --commented this for adani prescription report
	as FurtherActionRemark,
	'1' orderindex,
	[sExamination].[fTestParameterGetbyExaminationId](EED.Id, @rOrgId) AS TestName,	
	ISNULL(R.ContactPerson,'') as ReferedDoctor,
	ISNULL((Select Title from sCommon.tClinicMaster CCM where CCM.Id = R.ReferToClinicId),'') as ReferedHospital,
	ISNULL((select Title from [sFinanceCommon].[tStockUomMaster] FSUM where FSUM.Id = SIM.IssueStockUomId),'') as IssueUOM,
	AP.FollowupRemark as FollowupRemark,
	V.Remark as VitalRemark,
	V.SPo2,
	(FAM.Title + ISNULL((CASE 
		WHEN FAM.CODE='REFER TO HOSPITAL'
		THEN ISNULL((SELECT CASE 
				WHEN (ISNULL(CLM.Title,'') != '') AND (ISNULL(R.ContactPerson,'') != '') THEN (' - ' + CLM.Title + ' - ' + R.ContactPerson)
				WHEN (ISNULL(CLM.Title,'') != '') OR (ISNULL(R.ContactPerson,'') != '') THEN (' - ' + ISNULL(CLM.Title,'') + ISNULL(R.ContactPerson,'')) 
				ELSE ''
				END),'') 
		WHEN FAM.CODE='109' OR FAM.CODE='110'
		THEN ' - ' + ISNULL(RTDEM.EmployeePrefix + ' ','') + RTDEM.FirstName + ' ' + RTDEM.LastName
		END),'')) AS FurtherAction,
	--( --commented this for adani prescription report
	CONVERT(varchar(10), AP.AppointmentDate, 103) 
    --+ ' and ' + CAST(DATEDIFF(DAY, EED.ExaminationDate, AP.AppointmentDate) AS varchar(10))) --commented this for adani prescription report
	AS FollowUpDate
	from sExamination.tExamination EED
	LEFT JOIN sCwb.tDoctorMaster D on EED.DoctorEmployeeId = D.EmployeeId
	LEFT JOIN sHrm.tEmployeeMaster EM3 on D.EmployeeId = EM3.Id
	LEFT JOIN sExamination.tVital V	ON EED.Id = V.SourceId	
	LEFT JOIN sExamination.tReferral R on R.SourceId = EED.Id 
	LEFT JOIN sExamination.tOPD O on EED.Id = O.ExaminationId
	LEFT JOIN sExamination.vExtPrescription EP on EED.Id = EP.SourceId AND (EP.IsObsolete = 0)
	LEFT JOIN sExamination.vExtPrescriptionDetail EPD on EP.Id = EPD.PrescriptionId AND EPD.StoreLocationId = @rStoreLocationId AND (EPD.IsObsolete = 0)
	LEFT JOIN sIp.tStockItemMaster SIM on EPD.DrugStockItemId = SIM.Id 
	LEFT JOIN sCommon.tOrganizationDepartmentMaster OD on EED.OrganizationDepartmentId = OD.Id
	LEFT JOIN sCommon.tDepartmentGroupMaster DGM ON OD.DepartmentGroupId = DGM.Id
	LEFT JOIN sCommon.tFunctionMaster FM ON DGM.FunctionId= FM.Id
	LEFT JOIN sIp.tDrugDosageFrequencyMaster DFM on DFM.Id=EPD.DrugDosageFrequencyid
	LEFT JOIN sIp.tDrugScheduleMaster SM on SM.Id = EPD.DrugDosageScheduleId	
	LEFT JOIN sHrm.tEmployeeMaster EM on EED.ParticipantSourceId = EM.Id and EED.ParticipantSourceTypeId = 95
	LEFT JOIN sHrm.tEmployeeFamilyDetails EFD on EED.ParticipantSourceId = EFD.Id and EED.ParticipantSourceTypeId = 1001
	LEFT JOIN sCommon.tRelationshipMaster RM on RM.Id = EFD.RelationshipId and EED.ParticipantSourceTypeId = 1001
	LEFT JOIN sHrm.tCsrBenefiterMaster BM on BM.Id = EED.ParticipantSourceId and EED.ParticipantSourceTypeId = 1009
	LEFT JOIN sHrm.tCsrBenefiterTypeMaster BTM on BM.CSRBenefiterTypeId = BTM.Id
	LEFT JOIN sHrm.tEmployeeMaster EM2 on EFD.EmployeeId = EM2.Id
	LEFT JOIN sHrm.tEmployeePersonalDetail PD on EM.Id = PD.EmployeeId
	LEFT JOIN sCommon.tGenderMaster GM ON GM.Id = PD.GenderId
	LEFT JOIN sCommon.tGenderMaster GM2 ON GM2.Id = BM.GenderId
	LEFT JOIN sCommon.tGenderMaster GM3 ON GM3.Id = EFD.GenderId
	LEFT JOIN sHrm.tEmployeeJobDetail EJD ON EM.Id = EJD.EmployeeId  
	LEFT JOIN sHrm.tEmployeeJobDetail EJD2 ON EM2.Id = EJD2.EmployeeId	
	LEFT JOIN sHrm.tEmployeeTypeMaster ETM on ETM.Id = EJD.EmployeeTypeId
	LEFT JOIN sAppointment.tVisitingDoctorMaster VD on EED.DoctorEmployeeId = VD.Id
	LEFT JOIN sExamination.tCauseMaster CM on CM.Id = EED.CauseId 
	INNER JOIN sExamination.tSetupMaster SSM on EED.SetupId = SSM.Id
	INNER JOIN sExamination.tSubTypeMaster ST on SSM.SubTypeId = ST.Id
	INNER JOIN sIp.tStoreLocationMaster SLM on SLM.Id = EED.StoreLocationId
	INNER JOIN sCommon.tLocationMaster LM ON SLM.LocationId = LM.Id
	INNER JOIN sCommon.tCompanyInformation CI ON LM.OrgId = CI.Id	
	INNER JOIN sCommon.tCompanyInformationDetail CID on ci.ID = CID.orgid
	LEFT JOIN sAppointment.tAppointment AP on AP.ReferSourceId = EED.Id
	LEFT JOIN sSafety.tFurtherActionMaster FAM ON O.FurtherActionId = FAM.Id
	LEFT JOIN sCommon.tClinicMaster CLM ON R.ReferToClinicId = CLM.Id
	LEFT JOIN sHrm.tEmployeeMaster RTDEM ON O.ReferToDoctorId = RTDEM.Id
	WHERE (EED.Id = @rExaminationId OR @rExaminationId = 0)
	AND ST.MainTypeId = 8 and isnull(EED.Iscancel,0) = 0
	AND (EED.StoreLocationId = @rStoreLocationId)	
	AND 
	(
		ISNULL(@rEmployeeCode, '') = ''
		OR EXISTS 
		(
			SELECT 1
			FROM sCommon.fnsplit(@rEmployeeCode, ',') AS SplitValues
			WHERE 
			(
				CHARINDEX(',', @rEmployeeCode) = 0 
				AND 
				(
					(EM.Code LIKE SplitValues.item + '%' AND EED.ParticipantSourceTypeId = 95)
					OR (EM2.Code LIKE SplitValues.item + '%' AND EED.ParticipantSourceTypeId = 1001)
					OR (BM.Code LIKE SplitValues.item + '%' AND EED.ParticipantSourceTypeId = 1009)
				)
			)
			OR
			(
				CHARINDEX(',', @rEmployeeCode) > 0 
				AND 
				(
					(EM.Code = SplitValues.item AND EED.ParticipantSourceTypeId = 95)
					OR (EM2.Code = SplitValues.item AND EED.ParticipantSourceTypeId = 1001)
					OR (BM.Code = SplitValues.item AND EED.ParticipantSourceTypeId = 1009)
				)
			)
		)
	)
	AND (EED.OrganizationDepartmentId = @rDepartmentId OR @rDepartmentId = 0)	
	AND (EED.GradeId = @rGradeId OR @rGradeId=0)	
	AND (EED.EmployeeDesignationId = @rDesignationId OR @rDesignationId = 0)	
	AND
	(
		(EJD.EmployeeJobTitleId = @rJobTitleId AND EED.ParticipantSourceTypeId = 95)
		OR (EJD2.EmployeeJobTitleId = @rJobTitleId AND EED.ParticipantSourceTypeId = 1001)
		OR (0 = 0 AND EED.ParticipantSourceTypeId = 1009)
		OR @rJobTitleId = 0
	)
	AND
	(
		(EJD.EmployeeTypeId = @rEmployeeTypeId and EED.ParticipantSourceTypeId = 95)
		OR (EJD2.ContractorId = @rEmployeeTypeId and EED.ParticipantSourceTypeId = 1001)
		OR (0 = 0 AND EED.ParticipantSourceTypeId = 1009)
		OR @rEmployeeTypeId = 0
	)	
	AND
	(
		(EJD.ContractorId = @rContractorId and EED.ParticipantSourceTypeId = 95)
		OR (EJD2.ContractorId = @rContractorId and EED.ParticipantSourceTypeId = 1001)
		OR (0 = 0 AND EED.ParticipantSourceTypeId = 1009)
		OR @rContractorId = 0
	)	
	AND (EED.EmployeeCompanyId = @rCompanyId OR @rCompanyId=0)	
	AND
	(
		(PD.GenderId = @rGenderId AND EED.ParticipantSourceTypeId = 95)
		OR (EFD.GenderId = @rGenderId AND EED.ParticipantSourceTypeId = 1001)
		OR (BM.GenderId = @rGenderId AND EED.ParticipantSourceTypeId = 1009)
		OR @rGenderId = 0
	)		
	AND (EED.EmployeeStatusId = @rStatusId OR @rStatusId=0) 	
	AND
	(
		(DATEDIFF(yy,PD.DateOfBirth,sCommon.fGetDate()) <= @rAgeTo AND EED.ParticipantSourceTypeId = 95)
		OR (DATEDIFF(yy,isnull(EFD.DateOfBirth,'2050-01-01'),sCommon.fGetDate()) <= @rAgeTo AND EED.ParticipantSourceTypeId = 1001)
		OR (DATEDIFF(yy,isnull(BM.DateOfBirth,'2050-01-01'),sCommon.fGetDate()) <= @rAgeTo AND EED.ParticipantSourceTypeId = 1009)
		OR ISNULL(@rAgeTo,0)=0
	)
	AND
	(
		(DATEDIFF(yy,PD.DateOfBirth,sCommon.fGetDate()) >= @rAgeFrom AND EED.ParticipantSourceTypeId = 95)
		OR (DATEDIFF(yy,isnull(EFD.DateOfBirth,'1900-12-31'),sCommon.fGetDate()) >= @rAgeFrom AND EED.ParticipantSourceTypeId = 1001)
		OR (DATEDIFF(yy,isnull(BM.DateOfBirth,'1900-12-31'),sCommon.fGetDate()) >= @rAgeFrom AND EED.ParticipantSourceTypeId = 1009)
		OR ISNULL(@rAgeFrom,0)=0
	)
		
	AND ((EED.ExaminationDate >= @rFromDate) OR (ISNULL(@rFromDate,'') = ''))	
	AND ((EED.ExaminationDate < @rToDate + 1) OR (ISNULL(@rToDate,'') = ''))	
	AND (SLM.LocationId = @rLocationId) 	
	AND (EED.StoreLocationId = @rStoreLocationId) 	
	AND (EED.OrgId = @rOrgId)	
	AND (EED.CauseId = @rCaseCauseId or @rCaseCauseId=0)	
	AND (EED.ParticipantSourceTypeId = @rParticipantTypeId or @rParticipantTypeId=0)	
	AND (ETM.EmployeeTypeGroupId = @rEmployeeTypeGroupId or @rEmployeeTypeGroupId=0)	
	AND (EJD.EmployeeSubTypeId = @rEmployeeSubTypeId or @rEmployeeSubTypeId=0)	
	AND (EJD.EmployeeGroupId = @rEmployeeGroupId or @rEmployeeGroupId=0)	
	AND (BM.CsrBenefiterTypeId = @rOtherTypeId or @rOtherTypeId=0)
	
	union

	select EED.Id AS ExaminationId,
	OSP.id as prescriptionId,
	'' as ParticipantCode,
	'' as ParticipantName,
	'' as PatientCategory,	
	'' as Gender,
	'' as ExaminationDate,
	'' as OPDOutTime,	
	0 as Age,
	'' Pulse,
	'0' Temperature,
	'' BloodPressureSystolic,
	'' BloodPressureDiastolic,
	'' ContactPerson,
	'' OutsideMedicine,
	'' OutsideTest,
	'' AS Diagnosis,
	'' as problems,
	Isnull(DSM.Title,'') as DrugInstruction,
	Isnull(OSPD.StockItemName,'') as Medicines,
	Isnull(OSPD.DrugInstruction,'0.00') as MedicineQty,
	'0.00' DispensedQuantity,
	Isnull(OSPD.Duration,'') Duration,
	case when Isnull(OSPD.PrescribedQuantity,'')='' then '0' else Isnull(OSPD.PrescribedQuantity,'0') end PrescribedQuantity,
	EED.ParticipantSourceId,
	OSPD.id as StockId,
	0 DrawSize,
	DFM.Title as MedicineDosage,
	case when Isnull(OSPD.PrescribedQuantity,'')='' then '0' else Isnull(OSPD.PrescribedQuantity,'0') end  as RemainingQty,	
	GetDate() as UpdatedDateTime,
	'' as RefferedName,
	'' MedicalRegistrationNo,
	ISNULL(EED.ExaminationNumber,'') as ExaminationNumber,
	'' as CauseTitle,
	'' as DepartmentName,	
	convert(varbinary,'0') AS DoctorSignature,
	convert(varbinary,'0') Logo,
	'' as SmallAddress1,
	'' as SmallAddress2,
	'' as CompanyName,
	'' as Advice,
	'Outside' as FurtherActionRemark,
	'2' orderindex,
	[sExamination].[fTestParameterGetbyExaminationId](EED.Id, @rOrgId) AS TestName,	
	'' as ReferedDoctor,
	'' as ReferedHospital,
	'' as IssueUOM,
	'' as FollowupRemark,
	'' as VitalRemark,
	0 as SPo2,
	'' as FurtherAction,
	'' as FollowUpDate

	from sExamination.tExamination EED
	inner join sExamination.tOutSidePrescription OSP on OSP.Sourceid=EED.Id
	inner join sExamination.tOutSidePrescriptionDetail OSPD on OSPD.OutSidePrescriptionId=OSP.id
	left join sIp.tDrugDosageFrequencyMaster DFM on DFM.id=OSPD.DrugDosageFrequencyId
	left join sIp.tDrugScheduleMaster DSM on DSM.id=OSPD.DrugDosageScheduleId
	LEFT JOIN sHrm.tEmployeeMaster EM on EED.ParticipantSourceId = EM.Id and EED.ParticipantSourceTypeId = 95
	LEFT JOIN sHrm.tEmployeeFamilyDetails EFD on EED.ParticipantSourceId = EFD.Id and EED.ParticipantSourceTypeId = 1001
	LEFT JOIN sHrm.tCsrBenefiterMaster BM on BM.Id = EED.ParticipantSourceId and EED.ParticipantSourceTypeId = 1009
	LEFT JOIN sHrm.tEmployeeMaster EM2 on EFD.EmployeeId = EM2.Id

	WHERE (EED.Id = @rExaminationId OR @rExaminationId = 0)
	and isnull(EED.Iscancel,0) = 0
	AND (EED.StoreLocationId = @rStoreLocationId)		
	AND (EED.OrganizationDepartmentId = @rDepartmentId OR @rDepartmentId = 0)	
	AND (EED.GradeId = @rGradeId OR @rGradeId=0)	
	AND (EED.EmployeeDesignationId = @rDesignationId OR @rDesignationId = 0)		
	AND (EED.EmployeeCompanyId = @rCompanyId OR @rCompanyId=0)			
	AND (EED.EmployeeStatusId = @rStatusId OR @rStatusId=0) 		
	AND ((EED.ExaminationDate >= @rFromDate) OR (ISNULL(@rFromDate,'') = ''))	
	AND ((EED.ExaminationDate < @rToDate + 1) OR (ISNULL(@rToDate,'') = ''))			
	AND (EED.OrgId = @rOrgId)	
	AND (EED.CauseId = @rCaseCauseId or @rCaseCauseId=0)	
	AND (EED.ParticipantSourceTypeId = @rParticipantTypeId or @rParticipantTypeId=0)		
	AND 
	(
		ISNULL(@rEmployeeCode, '') = ''
		OR EXISTS 
		(
			SELECT 1
			FROM sCommon.fnsplit(@rEmployeeCode, ',') AS SplitValues
			WHERE 
			(
				CHARINDEX(',', @rEmployeeCode) = 0 
				AND 
				(
					(EM.Code LIKE SplitValues.item + '%' AND EED.ParticipantSourceTypeId = 95)
					OR (EM2.Code LIKE SplitValues.item + '%' AND EED.ParticipantSourceTypeId = 1001)
					OR (BM.Code LIKE SplitValues.item + '%' AND EED.ParticipantSourceTypeId = 1009)
				)
			)
			OR
			(
				CHARINDEX(',', @rEmployeeCode) > 0 
				AND 
				(
					(EM.Code = SplitValues.item AND EED.ParticipantSourceTypeId = 95)
					OR (EM2.Code = SplitValues.item AND EED.ParticipantSourceTypeId = 1001)
					OR (BM.Code = SplitValues.item AND EED.ParticipantSourceTypeId = 1009)
				)
			)
		)
	)
	order by orderindex
	OPTION(RECOMPILE)
end

GO

ALTER TABLE [sHospitalization].[tIntimationDetails]

ADD [TreatingDoctor] nvarchar(10) NULL;
 
ALTER TABLE [sHospitalization].[tIntimationDetails]

ADD Flag bit default(0);
 
 GO
 
 
ALTER PROCEDURE [sHospitalization].[pDashboardClosedDetailsGetAll]  
    @rFromDate NVARCHAR(50) = NULL,        
    @rToDate NVARCHAR(50) = NULL,        
    @rDoctorId NVARCHAR(50) = NULL,        
    @rStatus NVARCHAR(50) = NULL,        
    @rState NVARCHAR(50) = NULL,      
 @rBussnessid NVARCHAR(1000) = NULL,       
    @rOrgId BIGINT = NULL        
AS        
BEGIN                
    SET NOCOUNT ON;              
              
          
    IF @rFromDate = '' SET @rFromDate = NULL;              
    IF @rToDate = '' SET @rToDate = NULL;              
    IF @rDoctorId = '' OR @rDoctorId = '0' SET @rDoctorId = NULL;              
    IF @rStatus = '' OR @rStatus = '0' SET @rStatus = NULL;              
    IF @rBussnessid = '' OR @rBussnessid = '0' SET @rBussnessid = NULL;          
    DECLARE @FromDateConverted DATE = NULL;              
    DECLARE @ToDateConverted DATE = NULL;              
          
    IF @rFromDate IS NOT NULL              
        SET @FromDateConverted = CONVERT(DATE, LEFT(@rFromDate, 10), 103);      
              
    IF @rToDate IS NOT NULL              
        SET @ToDateConverted = CONVERT(DATE, LEFT(@rToDate, 10), 103);      
           
    IF (@FromDateConverted IS NOT NULL AND @ToDateConverted IS NOT NULL AND @FromDateConverted > @ToDateConverted)              
    BEGIN              
        DECLARE @tmp DATE;              
        SET @tmp = @FromDateConverted;              
        SET @FromDateConverted = @ToDateConverted;              
        SET @ToDateConverted = @tmp;              
    END              
              
          
    DECLARE @DoctorIdConverted BIGINT = NULL;              
    IF ISNUMERIC(@rDoctorId) = 1              
        SET @DoctorIdConverted = CAST(@rDoctorId AS BIGINT);              
              
          
     SELECT ROW_NUMBER() OVER (ORDER BY TicketNumber) AS [Sr.No.] ,    
  [Employee Code],   
  [Employee Name],  
  [Business],   
  [Hr Location],  
  [Beneficiary Name],   
  [Relation],    
    [Treating Doctor],  
    [Age],  
    [Nature Of Illness],   
    [Assigned Doctor],   
    [Status], [Remarks],   
    [Hospital Name],    
       [Network City],  
    [State],   
    [Claim Type],  
    [Date Of Intimation]   
 from    
 (     
 SELECT distinct  PAL.TicketNumber,   
 PAL.EmpCode AS [Employee Code],  
 PAL.ProposerName AS [Employee Name],  
 SCT.Title as Business,    
 LM.Title AS [Hr Location],  
 PAL.BeneficiaryName as [Beneficiary Name],  
 PAL.Relation,  
 Isnull(PAL.TreatingDoctor,'NA') as [Treating Doctor],  
 --  PAL.TreatingDoctor,  
 PAL.Age,      
 PAL.NatureOfIllness as [Nature Of Illness],  
   CUR.EmployeeName AS [Assigned Doctor],  
   PAL.TicketStatus AS [Status],        
      STUFF(( SELECT CHAR(10) + CONVERT(varchar(10), R.CreatedDateTime, 103) + ': ' + R.Remark      
        FROM sHospitalization.tIntimationRemark R      
        WHERE R.HospitalizationIntimationDetailId = pal.Id      
        ORDER BY R.CreatedDateTime DESC      
        FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '') AS Remarks ,    
      Pal.HospitalName as [Hospital Name],  
   PAL.NetworkCity as [Network City],PAL.State as [State],PAL.ClaimType as [Claim Type],      
      CASE       
      WHEN PAL.DateOfIntimation = DATEADD(DAY, DATEDIFF(DAY, 0, PAL.DateOfIntimation), 0)      
      THEN CONVERT(VARCHAR(10), PAL.DateOfIntimation, 103)      
      ELSE       
      CONVERT(VARCHAR(10), PAL.DateOfIntimation, 103) + ' ' + CONVERT(VARCHAR(8), PAL.DateOfIntimation, 108)      
      END AS [Date Of Intimation],PAL.CreatedDateTime               
              
    FROM [sHospitalization].[tIntimationDetails] PAL           
 INNER JOIN sHospitalization.tIntimationAssignedDoctor ASID On ASID.DoctorEmployeeId =PAL.AssignedDoctorId           
 LEFT JOIN [sHospitalization].[tIntimationAssignedDoctor] SM ON SM.Id = PAL.IntimationAssignedDoctorId              
    LEFT JOIN [sSecurity].[tUser] UR ON UR.Id = PAL.CreatedUserId AND UR.IsObsolete = 0 AND UR.OrgId = PAL.OrgId              
    LEFT JOIN [shrm].[vEmployeeCommonDetail] CUR ON CUR.EmployeeId = PAL.AssignedDoctorId    
    LEFT JOIN [sHrm].[tSectorMaster] HosB ON HosB.Id = SM.AssignSubTypeId and  HosB.IsObsolete =SM.IsObsolete and HosB.OrgId = SM.OrgId    
    LEFT JOIN [sHrm].[tEmployeeMaster] EMD ON EMD.Id = SM.DoctorEmployeeId                
    LEFT JOIN [sHrm].[tEmployeeMaster] EMP ON EMP.Id = PAL.AssignedDoctorId                
    LEFT JOIN [sHrm].[tEmployeeCommunications] ECD ON ECD.EmployeeId = EMP.Id                
    LEFT JOIN [sHrm].[tEmployeeJobDetail] EJD ON EJD.EmployeeId = EMP.Id                
    LEFT JOIN [sHrm].[tEmployeeTypeMaster] ETM ON ETM.Id = EJD.EmployeeTypeId                
    LEFT JOIN [sHrm].[tEmployeeCompanyMaster] ECM ON ECM.Id = EJD.EmployeeCompanyId                
    LEFT JOIN [sHrm].[tCompanyGroupMaster] CGM ON CGM.Id = ECM.CompanyGroupId                
    LEFT JOIN [sHrm].[tVerticalMaster] VTM ON VTM.Id = CGM.VerticalId                
    LEFT JOIN [sHrm].[tSectorMaster] SCT ON SCT.Id = VTM.SectorId                
    LEFT JOIN [sCommon].[tLocationMaster] LM ON LM.Id = EJD.EmployeeLocationId                
    LEFT JOIN [sCommon].[tDepartmentGroupMaster] DGM ON DGM.Id = EJD.OrganizationDepartmentId                
    LEFT JOIN [sCommon].[tOrganizationDepartmentMaster] ODM ON ODM.Id = EJD.OrganizationDepartmentId    
    LEFT JOIN [sHrm].[tEmployeeDesignationMaster] EMDS ON EMDS.Id = EJD.EmployeeDesignationId                
    LEFT JOIN [sHrm].[vExtEmployeeMaster] VEMDS ON VEMDS.EmployeeId = PAL.ParticipantSourceId                
    LEFT JOIN [sHrm].[vEmployeeCommonDetail] VEMDS1 ON VEMDS1.EmployeeId = PAL.AssignedDoctorId                
    LEFT JOIN [sHrm].[vEmployeeCommonDetail] VEMDS2 ON VEMDS2.EmployeeId = PAL.UpdatedUserId                
    LEFT JOIN [sHrm].[tGradeMaster] GRD ON GRD.Id = VEMDS.GradeId                
                
    WHERE               
       PAL.OrgId = @rOrgId AND PAL.IsObsolete = 0          
    --and PAL.OrgId = ASID.OrgId and ASID.Id = PAL.IntimationAssignedDoctorId       
       AND (@FromDateConverted IS NULL OR PAL.DateOfIntimation >= @FromDateConverted)              
       AND (@ToDateConverted IS NULL OR PAL.DateOfIntimation < DATEADD(DAY, 1, @ToDateConverted))              
       AND (@DoctorIdConverted IS NULL OR PAL.AssignedDoctorId = @DoctorIdConverted)              
       AND (@rStatus IS NULL OR PAL.TicketStatus = @rStatus)      
    AND PAL.TicketStatus ='Close'    
    AND (@rState IS NULL OR @rState = '' OR @rState = '0' OR @rState = '- All -' OR PAL.State = @rState)   
 AND (@rBussnessid IS NULL OR @rBussnessid = '' OR @rBussnessid = '0' OR @rBussnessid = '- All -' OR SCT.id = @rBussnessid)  
 )tmp    
 ORDER BY tmp.CreatedDateTime ASC;           
END 

GO

ALTER PROCEDURE [sHospitalization].[pDashboardDetailsGetAll]      
    @rFromDate NVARCHAR(50) = NULL,      
    @rToDate NVARCHAR(50) = NULL,      
    @rDoctorId NVARCHAR(50) = NULL,      
    @rStatus NVARCHAR(50) = NULL,      
    @rState NVARCHAR(50) = NULL,      
   @rBussnessid NVARCHAR(1000) = NULL,       
    @rOrgId BIGINT = NULL      
AS      
BEGIN        
    SET NOCOUNT ON;      
    
    IF @rFromDate = '' SET @rFromDate = NULL;      
    IF @rToDate = '' SET @rToDate = NULL;      
    IF @rDoctorId = '' OR @rDoctorId = '0' SET @rDoctorId = NULL;      
    IF @rStatus = '' OR @rStatus = '0' SET @rStatus = NULL;      
   IF @rBussnessid = '' SET @rBussnessid = NULL;    
      
    DECLARE @FromDateConverted DATE = NULL;      
    DECLARE @ToDateConverted DATE = NULL;      
      
    
    IF @rFromDate IS NOT NULL      
        SET @FromDateConverted = CONVERT(DATE, LEFT(@rFromDate, 10), 103)
      
    IF @rToDate IS NOT NULL      
        SET @ToDateConverted = CONVERT(DATE, LEFT(@rToDate, 10), 103);
      
       
    IF (@FromDateConverted IS NOT NULL AND @ToDateConverted IS NOT NULL AND @FromDateConverted > @ToDateConverted)      
    BEGIN      
        DECLARE @tmp DATE;      
        SET @tmp = @FromDateConverted;      
        SET @FromDateConverted = @ToDateConverted;      
        SET @ToDateConverted = @tmp;      
    END      
      
    -- DoctorID conversion      
    DECLARE @DoctorIdConverted BIGINT = NULL;      
    IF ISNUMERIC(@rDoctorId) = 1      
        SET @DoctorIdConverted = CAST(@rDoctorId AS BIGINT);      
      
    
      
    SELECT        
   ROW_NUMBER() OVER (ORDER BY PAL.TicketNumber) AS [Serial No],      
    PAL.EmpCode AS [Employee Code],      
     PAL.ProposerName AS [Employee Name],        
     --HosB.Title as Business,    
 SCT.Title as Business,   
 --  ISNULL(SCT.Title, 'Unknown') AS Business,
    SCT.id as BusinessId,    
    LM.Title AS   [Hr Location],       
  PAL.BeneficiaryName as [Beneficiary Name],      
  PAL.Relation,    
 Isnull(PAL.TreatingDoctor,'NA') as TreatingDoctor,  
   PAL.Age,      
           PAL.NatureOfIllness as [Nature Of Illness]  ,     
       CUR.EmployeeName AS [Assigned Doctor],      
      PAL.TicketStatus AS [Status],        
      STUFF((      
    SELECT       
        CHAR(10) + CONVERT(varchar(10), R.CreatedDateTime, 103)       
        + ': ' + R.Remark      
    FROM sHospitalization.tIntimationRemark R      
    WHERE R.HospitalizationIntimationDetailId = pal.Id      
    ORDER BY R.CreatedDateTime DESC      
    FOR XML PATH(''), TYPE      
).value('.', 'nvarchar(max)'), 1, 1, '') AS Remarks ,    
  Pal.HospitalName as [Hospital Name],      
    PAL.NetworkCity as [Network City],      
      PAL.State as [State],        
  PAL.ClaimType as [Claim Type],      
      CASE       
          WHEN PAL.DateOfIntimation = DATEADD(DAY, DATEDIFF(DAY, 0, PAL.DateOfIntimation), 0)      
              THEN CONVERT(VARCHAR(10), PAL.DateOfIntimation, 103)      
          ELSE       
              CONVERT(VARCHAR(10), PAL.DateOfIntimation, 103)      
              + ' ' + CONVERT(VARCHAR(8), PAL.DateOfIntimation, 108)      
      END AS [Date Of Intimation]    
           
    
    FROM [sHospitalization].[tIntimationDetails] PAL        
    LEFT JOIN [sHospitalization].[tIntimationAssignedDoctor] SM        
        ON SM.Id = PAL.IntimationAssignedDoctorId      
    LEFT JOIN [sSecurity].[tUser] UR        
        ON UR.Id = PAL.CreatedUserId        
        AND UR.IsObsolete = 0       
        AND UR.OrgId = PAL.OrgId      
    LEFT JOIN [shrm].[vEmployeeCommonDetail] CUR        
        ON CUR.EmployeeId = PAL.AssignedDoctorId       
    Left JOIN [sHrm].[tEmployeeMaster] EMD         
            ON EMD.Id = SM.DoctorEmployeeId        
        LEFT JOIN [sHrm].[tEmployeeMaster] EMP         
            ON EMP.Id = PAL.AssignedDoctorId         
        LEFT JOIN [sHrm].[tEmployeeCommunications] ECD         
            ON ECD.EmployeeId = EMP.Id        
        LEFT JOIN [sHrm].[tEmployeeJobDetail] EJD         
            ON EJD.EmployeeId = EMP.Id        
        LEFT JOIN [sHrm].[tEmployeeTypeMaster] ETM         
            ON ETM.Id = EJD.EmployeeTypeId        
        LEFT JOIN [sHrm].[tEmployeeCompanyMaster] ECM             
		ON ECM.Id = EJD.EmployeeCompanyId        
        LEFT JOIN [sHrm].[tCompanyGroupMaster] CGM         
            ON CGM.Id = ECM.CompanyGroupId        
        LEFT JOIN [sHrm].[tVerticalMaster] VTM         
            ON VTM.Id = CGM.VerticalId        
        LEFT JOIN [sHrm].[tSectorMaster] SCT         
            ON SCT.Id = VTM.SectorId        
        Left JOIN [sCommon].[tLocationMaster] LM         
            ON LM.Id = EJD.EmployeeLocationId        
       
         
    
     
    

        
    WHERE       
        PAL.OrgId = @rOrgId      
        AND PAL.IsObsolete = 0      
        AND (@FromDateConverted IS NULL OR PAL.DateOfIntimation >= @FromDateConverted)      
        AND (@ToDateConverted IS NULL OR PAL.DateOfIntimation < DATEADD(DAY, 1, @ToDateConverted))      
        AND (@DoctorIdConverted IS NULL OR PAL.AssignedDoctorId = @DoctorIdConverted)      
        AND (@rStatus IS NULL OR PAL.TicketStatus = @rStatus)      
   AND (      
       @rState IS NULL      
       OR @rState = ''      
       OR @rState = '0'      
       OR @rState = '- All -'      
       OR PAL.State = @rState      
    )      
  AND (  
    @rBussnessid IS NULL 
    OR @rBussnessid = '0'
    OR SCT.id = @rBussnessid
)  
      
    ORDER BY PAL.CreatedDateTime ASC;      
END 

GO


ALTER PROCEDURE [sHospitalization].[pDashboardDoctorDetailsGetAll]            
    @rFromDate NVARCHAR(50) = NULL,            
    @rToDate NVARCHAR(50) = NULL,            
    @rDoctorId NVARCHAR(50) = NULL,            
    @rStatus NVARCHAR(50) = NULL,            
 @rState NVARCHAR(50) = NULL,            
    @rBussnessid NVARCHAR(1000) = NULL,  
    @rOrgId BIGINT = NULL            
AS            
BEGIN              
    SET NOCOUNT ON;            
            
        
    IF @rFromDate = '' SET @rFromDate = NULL;            
    IF @rToDate = '' SET @rToDate = NULL;            
    IF @rDoctorId = '' OR @rDoctorId = '0' SET @rDoctorId = NULL;            
    IF @rStatus = '' OR @rStatus = '0' SET @rStatus = NULL;            
                 IF @rBussnessid = '' SET @rBussnessid = NULL;     
    DECLARE @FromDateConverted DATE = NULL;            
    DECLARE @ToDateConverted DATE = NULL;            
        
    IF @rFromDate IS NOT NULL            
        SET @FromDateConverted = CONVERT(DATE, LEFT(@rFromDate, 10), 103);    
            
    IF @rToDate IS NOT NULL            
        SET @ToDateConverted = CONVERT(DATE, LEFT(@rToDate, 10), 103);    
         
    IF (@FromDateConverted IS NOT NULL AND @ToDateConverted IS NOT NULL AND @FromDateConverted > @ToDateConverted)            
    BEGIN            
        DECLARE @tmp DATE;            
        SET @tmp = @FromDateConverted;            
        SET @FromDateConverted = @ToDateConverted;            
        SET @ToDateConverted = @tmp;            
    END            
            
        
    DECLARE @DoctorIdConverted BIGINT = NULL;            
    IF ISNUMERIC(@rDoctorId) = 1            
        SET @DoctorIdConverted = CAST(@rDoctorId AS BIGINT);            
            
        
    SELECT      
   ROW_NUMBER() OVER (ORDER BY PAL.TicketNumber) AS [Serial No],    
    PAL.EmpCode AS [Employee Code],    
     PAL.ProposerName AS [Employee Name],      
     --HosB.Title as Business,    
	 	 SCT.Title as Business,
    LM.Title AS   [Hr Location],     
  PAL.BeneficiaryName as [Beneficiary Name],    
   PAL.Age,    
           PAL.NatureOfIllness as [Nature Of Illness]  ,   
       CUR.EmployeeName AS [Assigned Doctor],    
      PAL.TicketStatus AS [Status],      
      STUFF((    
    SELECT     
        CHAR(10) + CONVERT(varchar(10), R.CreatedDateTime, 103)     
        + ': ' + R.Remark    
    FROM sHospitalization.tIntimationRemark R    
    WHERE R.HospitalizationIntimationDetailId = pal.Id    
    ORDER BY R.CreatedDateTime DESC    
    FOR XML PATH(''), TYPE    
).value('.', 'nvarchar(max)'), 1, 1, '') AS Remarks ,  
  Pal.HospitalName as [Hospital Name],    
    PAL.NetworkCity as [Network City],    
      PAL.State as [State],      
  PAL.ClaimType as [Claim Type],    
      CASE     
          WHEN PAL.DateOfIntimation = DATEADD(DAY, DATEDIFF(DAY, 0, PAL.DateOfIntimation), 0)    
              THEN CONVERT(VARCHAR(10), PAL.DateOfIntimation, 103)    
          ELSE     
              CONVERT(VARCHAR(10), PAL.DateOfIntimation, 103)    
              + ' ' + CONVERT(VARCHAR(8), PAL.DateOfIntimation, 108)    
      END AS [Date Of Intimation]       
            
    FROM [sHospitalization].[tIntimationDetails] PAL         
 Inner Join sHospitalization.tIntimationAssignedDoctor ASID        
  On ASID.DoctorEmployeeId =PAL.AssignedDoctorId         
    LEFT JOIN [sHospitalization].[tIntimationAssignedDoctor] SM              
        ON SM.Id = PAL.IntimationAssignedDoctorId            
    LEFT JOIN [sSecurity].[tUser] UR              
        ON UR.Id = PAL.CreatedUserId              
        AND UR.IsObsolete = 0             
        AND UR.OrgId = PAL.OrgId            
    LEFT JOIN [shrm].[vEmployeeCommonDetail] CUR              
        ON CUR.EmployeeId = PAL.AssignedDoctorId              
            
    Left JOIN [sHrm].[tSectorMaster] HosB              
        ON HosB.Id = SM.AssignSubTypeId and  HosB.IsObsolete =SM.IsObsolete and HosB.OrgId = SM.OrgId            
            
    Left JOIN [sHrm].[tEmployeeMaster] EMD               
       ON EMD.Id = SM.DoctorEmployeeId              
        LEFT JOIN [sHrm].[tEmployeeMaster] EMP               
   ON EMP.Id = PAL.ParticipantSourceId              
        LEFT JOIN [sHrm].[tEmployeeCommunications] ECD               
            ON ECD.EmployeeId = EMP.Id              
        LEFT JOIN [sHrm].[tEmployeeJobDetail] EJD               
        ON EJD.EmployeeId = EMP.Id              
        LEFT JOIN [sHrm].[tEmployeeTypeMaster] ETM               
            ON ETM.Id = EJD.EmployeeTypeId              
        LEFT JOIN [sHrm].[tEmployeeCompanyMaster] ECM               
            ON ECM.Id = EJD.EmployeeCompanyId              
        LEFT JOIN [sHrm].[tCompanyGroupMaster] CGM               
            ON CGM.Id = ECM.CompanyGroupId              
        LEFT JOIN [sHrm].[tVerticalMaster] VTM               
            ON VTM.Id = CGM.VerticalId              
        LEFT JOIN [sHrm].[tSectorMaster] SCT               
            ON SCT.Id = VTM.SectorId              
        Left JOIN [sCommon].[tLocationMaster] LM               
            ON LM.Id = EJD.EmployeeLocationId              
        LEFT JOIN [sCommon].[tDepartmentGroupMaster] DGM               
            ON DGM.Id = EJD.OrganizationDepartmentId              
        LEFT JOIN [sCommon].[tOrganizationDepartmentMaster] ODM               
            ON ODM.Id = EJD.OrganizationDepartmentId              
              
     LEFT JOIN [sHrm].[tEmployeeDesignationMaster] EMDS               
            ON EMDS.Id = EJD.EmployeeDesignationId              
     LEFT JOIN [sHrm].[vExtEmployeeMaster]  VEMDS               
            ON VEMDS.EmployeeId = PAL.ParticipantSourceId              
        LEFT JOIN [sHrm].[vEmployeeCommonDetail]  VEMDS1               
            ON VEMDS1.EmployeeId = PAL.AssignedDoctorId              
     LEFT JOIN [sHrm].[vEmployeeCommonDetail]  VEMDS2              
            ON VEMDS2.EmployeeId = PAL.UpdatedUserId              
        LEFT JOIN [sHrm].[tGradeMaster] GRD               
            ON GRD.Id = VEMDS.GradeId              
              
    WHERE             
        PAL.OrgId = @rOrgId            
        AND PAL.IsObsolete = 0      
  AND PAL.OrgId = ASID.OrgId and ASID.Id = PAL.IntimationAssignedDoctorId     
  and    PAL.OrgId = ASID.OrgId and ASID.Id = PAL.IntimationAssignedDoctorId    
        AND (@FromDateConverted IS NULL OR PAL.DateOfIntimation >= @FromDateConverted)            
        AND (@ToDateConverted IS NULL OR PAL.DateOfIntimation < DATEADD(DAY, 1, @ToDateConverted))            
        AND (@DoctorIdConverted IS NULL OR PAL.AssignedDoctorId = @DoctorIdConverted)            
        AND (@rStatus IS NULL OR PAL.TicketStatus = @rStatus)            
   AND (            
       @rState IS NULL            
       OR @rState = ''            
       OR @rState = '0'            
       OR @rState = '- All -'            
       OR PAL.State = @rState            
    )            
            			 AND (
    @rBussnessid IS NULL           -- When parameter is NULL, include all
    OR @rBussnessid = '0'          -- When '0', include all (for backward compatibility)
    OR SCT.id = @rBussnessid       -- Filter by specific business
)       
    ORDER BY PAL.CreatedDateTime ASC;            
END 

GO


ALTER PROCEDURE [sHospitalization].[pDashboardNewticketDetailsGetAll]                
    @rFromDate NVARCHAR(50) = NULL,                
    @rToDate NVARCHAR(50) = NULL,                
    @rDoctorId NVARCHAR(50) = NULL,                
    @rStatus NVARCHAR(50) = NULL,                
   @rState NVARCHAR(50) = NULL,              
   @rBussnessid NVARCHAR(1000) = NULL,          
   @rOrgId BIGINT = NULL                
   AS                
   BEGIN                      
    SET NOCOUNT ON;                    
                    
                
    IF @rFromDate = '' SET @rFromDate = NULL;                    
    IF @rToDate = '' SET @rToDate = NULL;                    
    IF @rDoctorId = '' OR @rDoctorId = '0' SET @rDoctorId = NULL;                    
    IF @rStatus = '' OR @rStatus = '0' SET @rStatus = NULL;       
    IF @rBussnessid = '' OR @rBussnessid = '0' SET @rBussnessid = NULL;         
                
    DECLARE @FromDateConverted DATE = NULL;                    
    DECLARE @ToDateConverted DATE = NULL;                    
                
    IF @rFromDate IS NOT NULL                    
        SET @FromDateConverted = CONVERT(DATE, LEFT(@rFromDate, 10), 103);            
                    
    IF @rToDate IS NOT NULL                    
        SET @ToDateConverted = CONVERT(DATE, LEFT(@rToDate, 10), 103);            
                 
    IF (@FromDateConverted IS NOT NULL AND @ToDateConverted IS NOT NULL AND @FromDateConverted > @ToDateConverted)                    
    BEGIN                    
        DECLARE @tmp DATE;                    
        SET @tmp = @FromDateConverted;                    
        SET @FromDateConverted = @ToDateConverted;                    
        SET @ToDateConverted = @tmp;                    
    END                    
                    
                
    DECLARE @DoctorIdConverted BIGINT = NULL;                    
    IF ISNUMERIC(@rDoctorId) = 1                    
        SET @DoctorIdConverted = CAST(@rDoctorId AS BIGINT);                    
                    
                
       SELECT ROW_NUMBER() OVER (ORDER BY TicketNumber) AS [Sr.No.] ,      
       [Employee Code],   
      [Employee Name],  
      [Business],   
      [Hr Location],   
      [Beneficiary Name],  
      [Relation],      
      [TreatingDoctor],  
      [Age],  
      [Nature Of Illness],  
      [Assigned Doctor],   
      [Status], [Remarks],   
      [Hospital Name],      
         [Network City],   
      [State],   
      [Claim Type],   
      [Date Of Intimation]   
 from      
 (       
    SELECT distinct   
     PAL.TicketNumber,  
     PAL.EmpCode AS [Employee Code],  
     PAL.ProposerName AS [Employee Name],  
     SCT.Title as Business,      
     LM.Title AS [Hr Location],  
     PAL.BeneficiaryName as [Beneficiary Name],    
     PAL.Relation,    
     Isnull(PAL.TreatingDoctor,'NA') as TreatingDoctor,    
     PAL.Age,        
     PAL.NatureOfIllness as [Nature Of Illness],  
     CUR.EmployeeName AS [Assigned Doctor],  
     PAL.TicketStatus AS [Status],          
     STUFF(( SELECT CHAR(10) + CONVERT(varchar(10), R.CreatedDateTime, 103) + ': ' + R.Remark        
        FROM sHospitalization.tIntimationRemark R        
        WHERE R.HospitalizationIntimationDetailId = pal.Id        
        ORDER BY R.CreatedDateTime DESC        
        FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '') AS Remarks ,      
     Pal.HospitalName as [Hospital Name],PAL.NetworkCity as [Network City],PAL.State as [State],PAL.ClaimType as [Claim Type],        
      CASE         
      WHEN PAL.DateOfIntimation = DATEADD(DAY, DATEDIFF(DAY, 0, PAL.DateOfIntimation), 0)        
      THEN CONVERT(VARCHAR(10), PAL.DateOfIntimation, 103)        
      ELSE         
      CONVERT(VARCHAR(10), PAL.DateOfIntimation, 103) + ' ' + CONVERT(VARCHAR(8), PAL.DateOfIntimation, 108)        
      END AS [Date Of Intimation],PAL.CreatedDateTime                 
                
    FROM [sHospitalization].[tIntimationDetails] PAL             
    INNER JOIN sHospitalization.tIntimationAssignedDoctor ASID On ASID.DoctorEmployeeId =PAL.AssignedDoctorId             
    LEFT JOIN [sHospitalization].[tIntimationAssignedDoctor] SM ON SM.Id = PAL.IntimationAssignedDoctorId                
    LEFT JOIN [sSecurity].[tUser] UR ON UR.Id = PAL.CreatedUserId AND UR.IsObsolete = 0 AND UR.OrgId = PAL.OrgId                
    LEFT JOIN [shrm].[vEmployeeCommonDetail] CUR ON CUR.EmployeeId = PAL.AssignedDoctorId      
    LEFT JOIN [sHrm].[tSectorMaster] HosB ON HosB.Id = SM.AssignSubTypeId and  HosB.IsObsolete =SM.IsObsolete and HosB.OrgId = SM.OrgId      
    LEFT JOIN [sHrm].[tEmployeeMaster] EMD ON EMD.Id = SM.DoctorEmployeeId                  
    LEFT JOIN [sHrm].[tEmployeeMaster] EMP ON EMP.Id = PAL.AssignedDoctorId                  
    LEFT JOIN [sHrm].[tEmployeeCommunications] ECD ON ECD.EmployeeId = EMP.Id                  
    LEFT JOIN [sHrm].[tEmployeeJobDetail] EJD ON EJD.EmployeeId = EMP.Id                  
    LEFT JOIN [sHrm].[tEmployeeTypeMaster] ETM ON ETM.Id = EJD.EmployeeTypeId                  
    LEFT JOIN [sHrm].[tEmployeeCompanyMaster] ECM ON ECM.Id = EJD.EmployeeCompanyId                  
    LEFT JOIN [sHrm].[tCompanyGroupMaster] CGM ON CGM.Id = ECM.CompanyGroupId                  
    LEFT JOIN [sHrm].[tVerticalMaster] VTM ON VTM.Id = CGM.VerticalId                  
    LEFT JOIN [sHrm].[tSectorMaster] SCT ON SCT.Id = VTM.SectorId                  
    LEFT JOIN [sCommon].[tLocationMaster] LM ON LM.Id = EJD.EmployeeLocationId                  
    LEFT JOIN [sCommon].[tDepartmentGroupMaster] DGM ON DGM.Id = EJD.OrganizationDepartmentId                  
    LEFT JOIN [sCommon].[tOrganizationDepartmentMaster] ODM ON ODM.Id = EJD.OrganizationDepartmentId      
    LEFT JOIN [sHrm].[tEmployeeDesignationMaster] EMDS ON EMDS.Id = EJD.EmployeeDesignationId                  
    LEFT JOIN [sHrm].[vExtEmployeeMaster] VEMDS ON VEMDS.EmployeeId = PAL.ParticipantSourceId                  
    LEFT JOIN [sHrm].[vEmployeeCommonDetail] VEMDS1 ON VEMDS1.EmployeeId = PAL.AssignedDoctorId                  
    LEFT JOIN [sHrm].[vEmployeeCommonDetail] VEMDS2 ON VEMDS2.EmployeeId = PAL.UpdatedUserId                  
    LEFT JOIN [sHrm].[tGradeMaster] GRD ON GRD.Id = VEMDS.GradeId                  
                  
    WHERE                 
       PAL.OrgId = @rOrgId AND PAL.IsObsolete = 0            
       AND (@FromDateConverted IS NULL OR PAL.DateOfIntimation >= @FromDateConverted)                
       AND (@ToDateConverted IS NULL OR PAL.DateOfIntimation < DATEADD(DAY, 1, @ToDateConverted))                
       AND (@DoctorIdConverted IS NULL OR PAL.AssignedDoctorId = @DoctorIdConverted)                
       AND (@rStatus IS NULL OR PAL.TicketStatus = @rStatus)        
       AND PAL.TicketStatus ='New'      
       AND (@rState IS NULL OR @rState = '' OR @rState = '0' OR @rState = '- All -' OR PAL.State = @rState)       
       AND (@rBussnessid IS NULL OR @rBussnessid = '' OR @rBussnessid = '0' OR @rBussnessid = '- All -' OR SCT.id = @rBussnessid)    
 )tmp      
 ORDER BY tmp.CreatedDateTime ASC;              
END 

GO


ALTER PROCEDURE [sHospitalization].[pDashboardOpenAndNewDetailsGetAll]          
    @rFromDate NVARCHAR(50) = NULL,          
    @rToDate NVARCHAR(50) = NULL,          
    @rDoctorId NVARCHAR(50) = NULL,          
    @rStatus NVARCHAR(50) = NULL,          
    @rState NVARCHAR(50) = NULL,     
    @rBussnessid NVARCHAR(1000) = NULL,  
    @rOrgId BIGINT = NULL          
AS          
BEGIN                
    SET NOCOUNT ON;              
              
          
    IF @rFromDate = '' SET @rFromDate = NULL;              
    IF @rToDate = '' SET @rToDate = NULL;              
    IF @rDoctorId = '' OR @rDoctorId = '0' SET @rDoctorId = NULL;              
    IF @rStatus = '' OR @rStatus = '0' SET @rStatus = NULL;              
    IF @rBussnessid = '' OR @rBussnessid = '0' SET @rBussnessid = NULL;         
    DECLARE @FromDateConverted DATE = NULL;              
    DECLARE @ToDateConverted DATE = NULL;              
          
    IF @rFromDate IS NOT NULL              
        SET @FromDateConverted = CONVERT(DATE, LEFT(@rFromDate, 10), 103);      
              
    IF @rToDate IS NOT NULL              
        SET @ToDateConverted = CONVERT(DATE, LEFT(@rToDate, 10), 103);      
           
    IF (@FromDateConverted IS NOT NULL AND @ToDateConverted IS NOT NULL AND @FromDateConverted > @ToDateConverted)              
    BEGIN              
        DECLARE @tmp DATE;              
        SET @tmp = @FromDateConverted;              
        SET @FromDateConverted = @ToDateConverted;              
        SET @ToDateConverted = @tmp;              
    END              
              
          
    DECLARE @DoctorIdConverted BIGINT = NULL;              
    IF ISNUMERIC(@rDoctorId) = 1              
        SET @DoctorIdConverted = CAST(@rDoctorId AS BIGINT);              
              
          
      SELECT ROW_NUMBER() OVER (ORDER BY TicketNumber) AS [Sr.No.] ,    
     [Employee Code], 
	 [Employee Name],
	 [Business], 
	 [Hr Location], 
	 [Beneficiary Name],
	 [Relation],    
     [TreatingDoctor],
	 [Age],
	 [Nature Of Illness],
	 [Assigned Doctor], 
	 [Status],
	 [Remarks], 
	 [Hospital Name],    
     [Network City], 
	 [State], 
	 [Claim Type],
	 [Date Of Intimation] from    
 (     
 SELECT distinct  PAL.TicketNumber, PAL.EmpCode AS [Employee Code], PAL.ProposerName AS [Employee Name],SCT.Title as Business,    
      LM.Title AS [Hr Location],PAL.BeneficiaryName as [Beneficiary Name]  
   ,PAL.Relation,  
   Isnull(PAL.TreatingDoctor,'NA') as TreatingDoctor,  
   PAL.Age,      
      PAL.NatureOfIllness as [Nature Of Illness],CUR.EmployeeName AS [Assigned Doctor],PAL.TicketStatus AS [Status],        
      STUFF(( SELECT CHAR(10) + CONVERT(varchar(10), R.CreatedDateTime, 103) + ': ' + R.Remark      
        FROM sHospitalization.tIntimationRemark R      
        WHERE R.HospitalizationIntimationDetailId = pal.Id      
        ORDER BY R.CreatedDateTime DESC      
        FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '') AS Remarks ,    
      Pal.HospitalName as [Hospital Name],PAL.NetworkCity as [Network City],PAL.State as [State],PAL.ClaimType as [Claim Type],      
      CASE       
      WHEN PAL.DateOfIntimation = DATEADD(DAY, DATEDIFF(DAY, 0, PAL.DateOfIntimation), 0)      
      THEN CONVERT(VARCHAR(10), PAL.DateOfIntimation, 103)      
      ELSE       
      CONVERT(VARCHAR(10), PAL.DateOfIntimation, 103) + ' ' + CONVERT(VARCHAR(8), PAL.DateOfIntimation, 108)      
      END AS [Date Of Intimation],PAL.CreatedDateTime               
              
    FROM [sHospitalization].[tIntimationDetails] PAL           
    INNER JOIN sHospitalization.tIntimationAssignedDoctor ASID On ASID.DoctorEmployeeId =PAL.AssignedDoctorId           
    LEFT JOIN [sHospitalization].[tIntimationAssignedDoctor] SM ON SM.Id = PAL.IntimationAssignedDoctorId              
    LEFT JOIN [sSecurity].[tUser] UR ON UR.Id = PAL.CreatedUserId AND UR.IsObsolete = 0 AND UR.OrgId = PAL.OrgId              
    LEFT JOIN [shrm].[vEmployeeCommonDetail] CUR ON CUR.EmployeeId = PAL.AssignedDoctorId    
    LEFT JOIN [sHrm].[tSectorMaster] HosB ON HosB.Id = SM.AssignSubTypeId and  HosB.IsObsolete =SM.IsObsolete and HosB.OrgId = SM.OrgId    
    LEFT JOIN [sHrm].[tEmployeeMaster] EMD ON EMD.Id = SM.DoctorEmployeeId                
    LEFT JOIN [sHrm].[tEmployeeMaster] EMP ON EMP.Id = PAL.AssignedDoctorId     
    LEFT JOIN [sHrm].[tEmployeeCommunications] ECD ON ECD.EmployeeId = EMP.Id                
    LEFT JOIN [sHrm].[tEmployeeJobDetail] EJD ON EJD.EmployeeId = EMP.Id                
    LEFT JOIN [sHrm].[tEmployeeTypeMaster] ETM ON ETM.Id = EJD.EmployeeTypeId                
    LEFT JOIN [sHrm].[tEmployeeCompanyMaster] ECM ON ECM.Id = EJD.EmployeeCompanyId                
    LEFT JOIN [sHrm].[tCompanyGroupMaster] CGM ON CGM.Id = ECM.CompanyGroupId                
    LEFT JOIN [sHrm].[tVerticalMaster] VTM ON VTM.Id = CGM.VerticalId                
    LEFT JOIN [sHrm].[tSectorMaster] SCT ON SCT.Id = VTM.SectorId                
    LEFT JOIN [sCommon].[tLocationMaster] LM ON LM.Id = EJD.EmployeeLocationId                
    LEFT JOIN [sCommon].[tDepartmentGroupMaster] DGM ON DGM.Id = EJD.OrganizationDepartmentId                
    LEFT JOIN [sCommon].[tOrganizationDepartmentMaster] ODM ON ODM.Id = EJD.OrganizationDepartmentId    
    LEFT JOIN [sHrm].[tEmployeeDesignationMaster] EMDS ON EMDS.Id = EJD.EmployeeDesignationId                
    LEFT JOIN [sHrm].[vExtEmployeeMaster] VEMDS ON VEMDS.EmployeeId = PAL.ParticipantSourceId                
    LEFT JOIN [sHrm].[vEmployeeCommonDetail] VEMDS1 ON VEMDS1.EmployeeId = PAL.AssignedDoctorId                
    LEFT JOIN [sHrm].[vEmployeeCommonDetail] VEMDS2 ON VEMDS2.EmployeeId = PAL.UpdatedUserId                
    LEFT JOIN [sHrm].[tGradeMaster] GRD ON GRD.Id = VEMDS.GradeId                
                
    WHERE               
       PAL.OrgId = @rOrgId AND PAL.IsObsolete = 0          
       AND (@FromDateConverted IS NULL OR PAL.DateOfIntimation >= @FromDateConverted)              
       AND (@ToDateConverted IS NULL OR PAL.DateOfIntimation < DATEADD(DAY, 1, @ToDateConverted))              
       AND (@DoctorIdConverted IS NULL OR PAL.AssignedDoctorId = @DoctorIdConverted)              
       AND (@rStatus IS NULL OR PAL.TicketStatus = @rStatus)      
       AND PAL.TicketStatus ='Open'    
       AND (@rState IS NULL OR @rState = '' OR @rState = '0' OR @rState = '- All -' OR PAL.State = @rState)  
       AND (@rBussnessid IS NULL OR @rBussnessid = '' OR @rBussnessid = '0' OR @rBussnessid = '- All -' OR SCT.id = @rBussnessid)  
 )tmp    
 ORDER BY tmp.CreatedDateTime ASC;     
END 

GO

Alter   PROCEDURE [sHospitalization].[pHospitalizationDoctorAssignmentMailGet]      
(      
    @rOrgId INT,      
    @rTemplate NVARCHAR(MAX),      
    @rMailSubject NVARCHAR(MAX),      
    @rIntimationDetailId BIGINT,      
    @rEventId INT      
)      
AS      
BEGIN      
    SET NOCOUNT ON;      
    
    SELECT      
        ID.OrgId AS OrgGroupId,      
        95 AS SourceTypeId,      
        EC.MobileNumber AS MobileNo,      
        EC.CompanyEmail AS EmailId,      
        ID.TicketNumber,      
        EM.FirstName,      
        EC.ExtensionNo,      
        ID.Relation,      
        EM1.FirstName,      
    
        -- 🔹 Message text with ALL replacements    
        REPLACE(    
            REPLACE(    
                REPLACE(    
                    REPLACE(    
                        REPLACE(    
                            REPLACE(    
                                REPLACE(    
                                    REPLACE(    
                                        @rTemplate,    
                                        '[DoctorName]',    
                                        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    
                                        ))    
                                    ),    
                                    '[BeneficiaryName]', ISNULL(ID.BeneficiaryName, '')    
                                ),    
                                '[RecipientName]',    
                                LTRIM(RTRIM(    
                                    ISNULL(EM1.FirstName, '') +    
                                    CASE WHEN ISNULL(EM1.MiddleName, '') <> '' THEN ' ' + EM1.MiddleName ELSE '' END +    
                                    CASE WHEN ISNULL(EM1.LastName, '') <> '' THEN ' ' + EM1.LastName ELSE '' END    
                                ))    
                            ),    
                            '[Relationship]', ISNULL(ID.Relation, '')    
                        ),    
                        '[Extension]', ISNULL(EC.ExtensionNo, '')    
                    ),    
                    '[MobileNumber]', ISNULL(EC.MobileNumber, '')    
                ),    
                '[NewAssignedCount]',    
               -- CAST(    
               --     (    
               --SELECT COUNT(1)    
               -- FROM sHospitalization.tIntimationDetails ID2    
               -- INNER JOIN sHospitalization.tIntimationAssignedDoctor AD    
               --     ON AD.Id = ID2.IntimationAssignedDoctorId    
               -- WHERE AD.DoctorEmployeeId = EM.Id    
    
               --     ) AS NVARCHAR(10)    
               -- )    
       CAST(  
                    (  
               SELECT COUNT(1)  
                FROM sHospitalization.tIntimationDetails ID2                 
                WHERE ID2.AssignedDoctorId = EM.Id and ID2.TicketStatus = 'New'  
    AND CONVERT(DATE,ID2.CreatedDateTime,103)=CONVERT(DATE,GETDATE(),103)  
                    ) AS NVARCHAR(10)  
                )  
            ),    
 '[OpenFrom]',    
CAST(    
(    
    --SELECT COUNT(1)    
    --FROM sHospitalization.tIntimationDetails ID2    
    --INNER JOIN sHospitalization.tIntimationAssignedDoctor AD2    
    --    ON AD2.Id = ID2.IntimationAssignedDoctorId    
    --WHERE AD2.DoctorEmployeeId = EM.Id    
    --  AND ISNULL(ID2.IsObsolete, 0) = 0    
    --  AND ID2.TicketStatus = 'New'    
    --  AND ID2.DateOfAdmission >= DATEADD(HOUR, -48, GETDATE())    
  
    --   SELECT COUNT(1)    
    --FROM sHospitalization.tIntimationDetails ID2    
    --INNER JOIN sHospitalization.tIntimationAssignedDoctor AD2    
    --    ON AD2.Id = ID2.IntimationAssignedDoctorId    
    --WHERE AD2.DoctorEmployeeId = EM.Id    
    --  AND ISNULL(ID2.IsObsolete, 0) = 0    
    -- AND TicketStatus IN ('Open', 'New')   
    -- AND ID2.CreatedDateTime <= DATEADD(HOUR, -48, GETDATE())    
  
  
   SELECT COUNT(1)    
    FROM sHospitalization.tIntimationDetails ID2    
    --INNER JOIN sHospitalization.tIntimationAssignedDoctor AD2    
    --    ON AD2.Id = ID2.IntimationAssignedDoctorId    
    WHERE ID2.AssignedDoctorId = EM.Id    
      AND ISNULL(ID2.IsObsolete, 0) = 0    
     AND TicketStatus IN ('Open', 'New')   
     AND ID2.CreatedDateTime <= DATEADD(HOUR, -48, GETDATE())    
  
) AS NVARCHAR(10)    
)    
    
        ) AS MsgText,    
    
        @rMailSubject AS Subject      
    
    FROM sHospitalization.tIntimationDetails ID      
    LEFT JOIN sHrm.tEmployeeMaster EM      
        ON ID.AssignedDoctorId = EM.Id      
    LEFT JOIN sHrm.tEmployeeCommunications EC      
        ON EM.Id = EC.EmployeeId      
    LEFT JOIN sHrm.tEmployeeMaster EM1      
        ON ID.ParticipantSourceId = EM1.Id      
   -- LEFT JOIN sHospitalization.tIntimationAssignedDoctor EM2      
     --   ON ID.AssignedDoctorId = EM2.DoctorEmployeeId      
    
    WHERE ID.Id = @rIntimationDetailId      
      AND ISNULL(ID.AssignedDoctorId, 0) <> 0;      
    
END; 

GO


ALTER PROCEDURE [sHospitalization].[pIntimationDetailsInsert]       
    @rHospitalName NVARCHAR(255) = NULL,        
    @rPatientName NVARCHAR(255) = NULL,        
    @rEmpCode NVARCHAR(100) = NULL,        
    @rMobile1 NVARCHAR(20) = NULL,        
    @rOrgId INT = NULL,          
    @rRawEmployeeNo NVARCHAR(100) = NULL,        
    @rRawBeneficiaryName NVARCHAR(255) = NULL,        
    @rRawAge NVARCHAR(10) = NULL,        
    @rRawRelation NVARCHAR(100) = NULL,        
    @rRawNatureOfIllness NVARCHAR(500) = NULL,        
    @rRawProposerName NVARCHAR(255) = NULL,        
    @rRawContact1 NVARCHAR(20) = NULL,        
    @rRawHospitalName NVARCHAR(255) = NULL,        
    @rRawNetworkCity NVARCHAR(100) = NULL,        
    @rRawState NVARCHAR(100) = NULL,        
    @rRawTreatingDoctor NVARCHAR(255) = NULL,        
    @rRawLikelyDateOfAdmission NVARCHAR(200) = NULL,        
    @rRawDateOfDischarge NVARCHAR(200) = NULL,        
    @rRawStatusOfIntimation NVARCHAR(100) = NULL,        
    @rRawTypeOfIntimation NVARCHAR(100) = NULL,        
    @rRawFIRNo NVARCHAR(100) = NULL,        
    @rRawDateOfFileReceived NVARCHAR(200) = NULL,        
    @rRawClaimType NVARCHAR(200) = NULL,        
    @rRawIntimationNo NVARCHAR(100) = NULL,        
    @rRawDateOfIntimation NVARCHAR(200) = NULL,        
    @rRawTPACardNo NVARCHAR(100) = NULL,        
    @rRawGrade NVARCHAR(200) = NULL,        
    @rRawGroupCode NVARCHAR(200) = NULL,        
    @rRawModeOfStatus NVARCHAR(200) = NULL,        
    @rRawTAT NVARCHAR(200) = NULL,        
    @rRawAdmissionType NVARCHAR(200) = NULL,        
    @rRawEstimatedAmount NVARCHAR(200) = NULL,        
    @rRawNetworkStatus NVARCHAR(200) = NULL,        
    @rRawTAT_Godi NVARCHAR(200) = NULL,        
    @rRawTAT_Band_Godi NVARCHAR(200) = NULL,        
    @rCreatedUserId BIGINT = NULL,        
    @rIsObsolete BIT = 0,        
    @AssignedDoctorId BIGINT = NULL,        
    @IntimationAssignedDoctorId INT = NULL ,  
    @rflag BIT = 0   
            
AS        
BEGIN        
    SET nocount ON;        
 DECLARE @NewRecordId BIGINT ;        
  DECLARE @ParticipantSourceId BIGINT ;       
    BEGIN try        
        DECLARE @EmployeeId BIGINT = NULL,        
                @PreviousDoctorId BIGINT = NULL,        
                @TicketStatus NVARCHAR(50) = 'New',        
                @CurrentDateTime DATETIME = Getutcdate(),        
                @TicketNumber NVARCHAR(50) = NULL,        
                @AssignTypeId BIGINT = NULL,        
                @AssignSubTypeId BIGINT = NULL,        
                @DesignationId BIGINT = NULL,        
                @ParsedDateOfAdmission DATE,        
                @ParsedDateOfDischarge DATE,        
                @ParsedDateOfIntimation DATE,        
                @ParsedEstimatedAmount DECIMAL(18, 2),        
                @ParsedAge INT,        
                @lNextNumber BIGINT,        
                @FIRNo_num NUMERIC(18, 0) = NULL,        
                @TPACardNo_num NUMERIC(18, 0) = NULL,        
                @EstimatedAmount_num DECIMAL(18, 2) = NULL,        
                @TAT_Godi_int INT = NULL,        
                @ParticipantSourceTypeId BIGINT = NULL;        
            
        ---------------------------------------------------------------------        
        -- Generate new RowId        
        ---------------------------------------------------------------------        
        EXEC scommon.ptablerowidget @rOrgId = @rOrgId,        
                                    @rTableTitle = 'sHospitalization.tIntimationDetails',        
                                    @rIsCurrentNumber = 0,        
                                    @rSeed = 1,        
                                    @rIncrement = 1,        
                                    @outNextNumber = @lNextNumber output;        
        SET @NewRecordId = @lNextNumber;        
        ---------------------------------------------------------------------        
        -- Resolve EmployeeId (if present)        
        ---------------------------------------------------------------------        
        ---------------------------------------------------------------------        
        -- Resolve EmployeeId (if present)        
        ---------------------------------------------------------------------        
        IF ISNULL(LTRIM(RTRIM(@rRawEmployeeNo)), '') <> ''        
        BEGIN        
            SELECT @EmployeeId = Id        
            FROM [sHrm].[tEmployeeMaster]        
            WHERE Code = @rRawEmployeeNo        
     AND IsObsolete = 0;        
        
            IF @EmployeeId IS NOT NULL        
            BEGIN        
        
                SET @ParticipantSourceTypeId = 95;        
            END        
            ELSE        
            BEGIN        
        
                SET @ParticipantSourceTypeId = NULL;        
            END        
        END        
        ELSE        
        BEGIN        
        
            SET @ParticipantSourceTypeId = NULL;        
        END        
        
        ---------------------------------------------------------------------        
        -- Doctor assignment placeholder logic (currently manual or external)        
        ---------------------------------------------------------------------        
        -- ASSIGNDOCTORDONE:        
        ---------------------------------------------------------------------        
        -- Generate Ticket Number        
        ---------------------------------------------------------------------        
        -- SET @TicketNumber = 'TKT-' + Format(Getutcdate(), 'yyyyMMdd') + '-' + RIGHT('00000' + Cast(Abs(Checksum(Newid())) % 100000 AS NVARCHAR(5)), 5);        
        -- Generate sequential TicketNumber (e.g. TKT-20251105-00001)        
        ---------------------------------------------------------------------        
        -- Generate sequential TicketNumber like TKT-20251105-00001        
        ---------------------------------------------------------------------        
        DECLARE @NextNumber INT;        
        
        SELECT @NextNumber = ISNULL(MAX(CAST(RIGHT(TicketNumber, 5) AS INT)), 0) + 1        
        FROM [sHospitalization].[tIntimationDetails]        
        WHERE TicketNumber LIKE 'TKT-' + FORMAT(GETUTCDATE(), 'yyyyMMdd') + '%';        
        
        SET @TicketNumber        
            = 'TKT-' + FORMAT(GETUTCDATE(), 'yyyyMMdd') + '-' + RIGHT('00000' + CAST(@NextNumber AS NVARCHAR(5)), 5);        
        
        
        ---------------------------------------------------------------------        
        -- Parse / normalize fields        
        ---------------------------------------------------------------------        
        SET @ParsedDateOfAdmission = dbo.Fn_parsedatestring(@rRawLikelyDateOfAdmission);        
        SET @ParsedDateOfDischarge = dbo.Fn_parsedatestring(@rRawDateOfDischarge);        
        SET @ParsedDateOfIntimation = dbo.Fn_parsedatestring(@rRawDateOfIntimation);        
        -- Normalize estimated amount        
        IF Isnull(Ltrim(Rtrim(@rRawEstimatedAmount)), '') <> ''        
        BEGIN        
            SET @rRawEstimatedAmount = Replace(Replace(Ltrim(Rtrim(@rRawEstimatedAmount)), ',', ''), ' ', '');        
            SET @ParsedEstimatedAmount = try_cast(@rRawEstimatedAmount as decimal(18, 2));        
        END        
        ELSE        
            SET @ParsedEstimatedAmount = NULL;        
        SET @ParsedAge = try_cast(@rRawAge as int);        
        ---------------------------------------------------------------------        
        -- Validate numeric fields BEFORE INSERT        
        ---------------------------------------------------------------------        
        -- FIRNo        
        IF Isnull(Ltrim(Rtrim(@rRawFIRNo)), '') <> ''        
        BEGIN        
            SET @FIRNo_num = try_cast(Replace(Ltrim(Rtrim(@rRawFIRNo)), ' ', '') as numeric(18, 0));        
            IF @FIRNo_num IS NULL        
            RAISERROR('Invalid FIRNo: cannot convert [%s] to numeric', 16, 1, @rRawFIRNo);        
        END        
        ELSE        
            SET @FIRNo_num = NULL;        
        -- TPACardNo        
        IF Isnull(Ltrim(Rtrim(@rRawTPACardNo)), '') <> ''        
        BEGIN        
            SET @TPACardNo_num = try_cast(Replace(Ltrim(Rtrim(@rRawTPACardNo)), ' ', '') as numeric(18, 0));        
            IF @TPACardNo_num IS NULL        
                RAISERROR('Invalid TPACardNo: cannot convert [%s] to numeric', 16, 1, @rRawTPACardNo);        
        END        
        ELSE        
            SET @TPACardNo_num = NULL;        
        -- EstimatedAmount        
        IF Isnull(Ltrim(Rtrim(@rRawEstimatedAmount)), '') <> ''        
        BEGIN        
            IF @ParsedEstimatedAmount IS NULL        
                RAISERROR('Invalid EstimatedAmount: cannot convert [%s] to decimal', 16, 1, @rRawEstimatedAmount);        
            ELSE        
                SET @EstimatedAmount_num = @ParsedEstimatedAmount;        
        END        
        ELSE        
           SET @EstimatedAmount_num = NULL;        
        -- TAT_Godi        
        IF Isnull(Ltrim(Rtrim(@rRawTAT_Godi)), '') <> ''        
        BEGIN        
            SET @TAT_Godi_int = try_cast(Ltrim(Rtrim(@rRawTAT_Godi)) as int);        
            IF @TAT_Godi_int IS NULL        
                RAISERROR('Invalid TAT_Godi: cannot convert [%s] to int', 16, 1, @rRawTAT_Godi);        
        END        
        ELSE        
            SET @TAT_Godi_int = NULL;        
        
        
           ---------------------------------------------------------------------        
        -- Insert / Update State Master        
        ---------------------------------------------------------------------        
        IF ISNULL(LTRIM(RTRIM(@rRawState)), '') <> ''        
        BEGIN        
            DECLARE @lStateId BIGINT;        
        
            -- Check if state already exists        
            SELECT @lStateId = Id        
            FROM [sHospitalization].[tStateMaster]        
            WHERE StateName = @rRawState        
                  AND OrgId = @rOrgId        
                  AND IsObsolete = 0;        
        
            -- If not found, insert new        
            IF @lStateId IS NULL        
            BEGIN        
                EXEC scommon.ptablerowidget         
                    @rOrgId = @rOrgId,        
                    @rTableTitle = 'sHospitalization.tStateMaster',        
                    @rIsCurrentNumber = 0,        
                    @rSeed = 1,        
                    @rIncrement = 1,        
                    @outNextNumber = @lStateId OUTPUT;        
        
                INSERT INTO [sHospitalization].[tStateMaster]        
                (        
                    Id,        
                    OrgId,        
                    StateName,        
                    CreatedUserId,        
                    CreatedDateTime,        
                    UpdatedUserId,        
                    UpdatedDateTime,        
                    IsObsolete        
                )        
                VALUES        
                (        
                    @lStateId,        
                    @rOrgId,        
                    @rRawState,        
                    @rCreatedUserId,        
                    @CurrentDateTime,        
                    @rCreatedUserId,        
                    @CurrentDateTime,        
                    0        
                );        
            END        
        END        
        
        ---------------------------------------------------------------------        
        -- All validations passed — perform insert inside a transaction        
        ---------------------------------------------------------------------        
IF @ParsedDateOfIntimation IS NOT NULL AND NOT EXISTS    
(    
    SELECT 1    
    FROM [sHospitalization].[tIntimationDetails]    
    WHERE EmpCode = @rRawEmployeeNo    
      AND BeneficiaryName = @rRawBeneficiaryName    
      AND DateOfIntimation = @ParsedDateOfIntimation    
)     
  BEGIN        
        BEGIN TRANSACTION;        
        
          
    INSERT INTO [sHospitalization].[tIntimationDetails]        
        (        
            id,        
            orgid,        
            participantsourceid,        
            participantsourcetypeid,        
            empcode,        
            beneficiaryname,        
            dateofintimation,        
           dateofadmission,        
            dateofdischarge,        
            claimtype,        
            intimationnumber,        
            natureofillness,        
            status,        
            hospitalname,        
            networkcity,        
            state,        
            age,        
            mobile1,        
            assigneddoctorid,        
            IntimationAssignedDoctorId,        
            ticketnumber,        
            ticketstatus,        
            ticketopendate,        
            relation,        
            proposername,        
            statusofintimation,        
            typeofintimation,        
            firno,        
            tpacardno,        
            grade,        
            groupcode,        
            modeofstatus,        
            tat,        
            admissiontype,        
            estimatedamount,        
            networkstatus,        
            tat_godi,        
            tat_band_godi,        
            isobsolete,        
            createddatetime,        
            createduserid,        
            updateddatetime,        
            updateduserid,  
            TreatingDoctor,  
             Flag  
        )        
        VALUES        
        (@NewRecordId,        
         Isnull(@rOrgId, 0),        
         @EmployeeId,        
         @ParticipantSourceTypeId,        
         @rRawEmployeeNo,        
         @rRawBeneficiaryName,        
         @ParsedDateOfIntimation,        
         @ParsedDateOfAdmission,        
         @ParsedDateOfDischarge,        
         @rRawClaimType,        
         @rRawIntimationNo,        
         @rRawNatureOfIllness,        
         @rRawStatusOfIntimation,        
         @rRawHospitalName,        
         @rRawNetworkCity,        
         @rRawState,        
         @ParsedAge,        
         @rRawContact1,        
         @AssignedDoctorId,        
         @IntimationAssignedDoctorId,        
         @TicketNumber,        
         @TicketStatus,        
         @CurrentDateTime,        
         @rRawRelation,        
         @rRawProposerName,        
         @rRawStatusOfIntimation,        
         @rRawTypeOfIntimation,        
         @FIRNo_num,        
         @TPACardNo_num,        
         @rRawGrade,        
         @rRawGroupCode,        
         @rRawModeOfStatus,        
         @rRawTAT,        
         @rRawAdmissionType,        
         @EstimatedAmount_num,        
         @rRawNetworkStatus,        
         @TAT_Godi_int,        
         @rRawTAT_Band_Godi,        
         @rIsObsolete,        
         @CurrentDateTime,        
         @rCreatedUserId,        
         @CurrentDateTime,        
         @rCreatedUserId ,  
         NULLIF(LTRIM(RTRIM(@rRawTreatingDoctor)), ''),  
         @rflag       
        );        
          
        ---------------------------------------------------------------------        
        -- Insert doctor assignment log        
        ---------------------------------------------------------------------        
                
  IF @AssignedDoctorId IS NOT NULL        
        BEGIN        
            DECLARE @lAssignLogId BIGINT;        
            EXEC scommon.ptablerowidget @rOrgId = @rOrgId,        
                                        @rTableTitle = 'sHospitalization.tIntimationDoctorAssignmentLog',        
                                        @rIsCurrentNumber = 0,        
                                        @rSeed = 1,        
                                        @rIncrement = 1,        
                 @outNextNumber = @lAssignLogId output;        
            INSERT INTO [sHospitalization].[tIntimationDoctorAssignmentLog]        
            (        
                id,        
                orgid,        
                hospitalizationintimationdetailid,        
                previousdoctoremployeeid,        
                newdoctoremployeeid,        
                assigneddate,        
                ticketnumber,        
                createduserid,        
                createddatetime,        
                updateduserid,        
                updateddatetime,        
    Status        
            )        
            VALUES        
            (@lAssignLogId,        
             Isnull(@rOrgId, 0),        
             @NewRecordId,        
             @PreviousDoctorId,        
             @AssignedDoctorId,        
             @CurrentDateTime,        
             @TicketNumber,        
             @rCreatedUserId,        
             @CurrentDateTime,        
             @rCreatedUserId,        
             @CurrentDateTime,        
    'New'        
            );        
        
        END        
        COMMIT TRANSACTION;        
  END        
  ---------------------------------------------------------------------        
-- Insert default remark entry with Status = 'New'        
---------------------------------------------------------------------        
--IF @AssignedDoctorId IS NOT NULL        
--BEGIN        
--    DECLARE @lRemarkId BIGINT;        
        
--    EXEC scommon.ptablerowidget         
--        @rOrgId = @rOrgId,        
--        @rTableTitle = 'sHospitalization.tIntimationRemark',        
--        @rIsCurrentNumber = 0,        
--        @rSeed = 1,        
--        @rIncrement = 1,        
--        @outNextNumber = @lRemarkId OUTPUT;        
        
--    INSERT INTO [sHospitalization].[tIntimationRemark]        
--    (        
--        Id,        
--        OrgId,        
--        HospitalizationIntimationDetailId,        
--        DoctorEmployeeId,        
--        Remark,        
--        CreatedUserId,        
--        CreatedDateTime,        
--        UpdatedUserId,        
--        UpdatedDateTime,        
--        Status        
--    )        
--    VALUES        
--    (        
--        @lRemarkId,        
--        @rOrgId,        
--        @NewRecordId,        
--        @AssignedDoctorId,        
--        NULL,                     -- Remark initially empty        
--        @rCreatedUserId,        
--        @CurrentDateTime,        
--        @rCreatedUserId,        
--        @CurrentDateTime,        
--        'New'                     -- Default Status        
--    );        
--END        
        
        ---------------------------------------------------------------------        
        -- Return newly created record id        
        ---------------------------------------------------------------------        
        SELECT @NewRecordId AS newrecordid ,@EmployeeId as ParticipantSourceId ,@IntimationAssignedDoctorId as IntimationAssignedDoctorId ;        
        
        RETURN;        
    END try        
    BEGIN catch        
        -- Ensure rollback        
        IF @@TRANCOUNT > 0        
            ROLLBACK TRANSACTION;        
        DECLARE @ErrorMessage NVARCHAR(4000) = Error_message();        
        DECLARE @lErrorId BIGINT;        
        EXEC scommon.ptablerowidget @rOrgId = @rOrgId,        
                                    @rTableTitle = 'sHospitalization.tErrorLog',        
                                    @rIsCurrentNumber = 0,        
                                    @rSeed = 1,        
                                    @rIncrement = 1,        
                                    @outNextNumber = @lErrorId output;        
        INSERT INTO [sHospitalization].[tErrorLog]        
        (        
            id,        
            errormessage,        
            storedprocedure,        
            errordatetime,        
            userid,        
   orgid        
        )        
        VALUES        
        (@lErrorId, @ErrorMessage, 'sHospitalization.pIntimationDetailsInsert', Getutcdate(), @rCreatedUserId, @rOrgId);        
        
        SET @NewRecordId = -1;        
        RAISERROR(@ErrorMessage, 16, 1);        
        RETURN;        
    END catch        
END;  

GO


ALTER PROCEDURE [sHospitalization].[pIntimationDetailsListReviewGet]  
(              
    @rOrgId SMALLINT,              
    @rFromDate NVARCHAR(50) = NULL,              
    @rToDate NVARCHAR(50) = NULL,              
    @rDoctorId BIGINT = 0,              
    @rStatusId VARCHAR(50) = 0,              
    @rCodeLike NVARCHAR(MAX) = ''              
)              
AS              
BEGIN              
    SET NOCOUNT ON;              
    SET DATEFORMAT dmy;              
              
    DECLARE               
        @FromDate DATETIME = NULL,              
        @ToDate DATETIME = NULL;              
              
DECLARE @StatusIds TABLE (Id INT);        
  
              
INSERT INTO @StatusIds (Id)              
SELECT T.X.value('.', 'INT')              
FROM (              
    SELECT CAST(              
        '<x>' + REPLACE(@rStatusId, ',', '</x><x>') + '</x>'              
        AS XML              
    ) AS XmlData              
) A              
CROSS APPLY XmlData.nodes('/x') AS T(X);              
              
   ---------------------------------------------------  
    -- Date handling  
    ---------------------------------------------------  
    IF (ISNULL(@rFromDate, '') <> '')  
        SET @FromDate = CONVERT(DATETIME, @rFromDate, 103);  
  
    IF (ISNULL(@rToDate, '') <> '')  
        SET @ToDate = DATEADD(DAY, 1, CONVERT(DATETIME, @rToDate, 103));  
  
   ---------------------------------------------------  
    -- Cache EventTemplate (IMPORTANT)  
    ---------------------------------------------------  
    DECLARE  
        @MailSubject NVARCHAR(MAX),  
        @MailTemplate NVARCHAR(MAX);  
  
    SELECT TOP 1  
        @MailSubject = MailSubject,  
        @MailTemplate = Template  
    FROM sAlert.tEventTemplate  
    WHERE OrgId = 1002  
      AND EventId = 108  
      AND IsAutoSend = 1;  
              
			  IF OBJECT_ID('tempdb..#AlertLogLatest') IS NOT NULL
    DROP TABLE #AlertLogLatest;

SELECT
    ClaimIntimationDetailId,
    MAX(CreatedDateTime) AS CreatedDate
INTO #AlertLogLatest
FROM sAlert.tAlertLog WITH (NOLOCK)
GROUP BY ClaimIntimationDetailId;

    -----------------------------------------              
    -- If no code, make it empty              
    -----------------------------------------              
    IF (ISNULL(@rCodeLike, '') = '') SET @rCodeLike = '';              
              
    BEGIN TRY              
        SELECT               
            TD.Id AS IntimationId, TD.EmpCode AS EmployeeCode, TD.BeneficiaryName, TD.Relation,            
                        
   CASE  
                WHEN UPPER(ISNULL(TD.Relation, '')) = 'SELF'  
                     AND VEMDS.EmployeeId = TD.ParticipantSourceId  
                THEN ISNULL(VEMDS.Age, TD.Age)  
                ELSE TD.Age  
            END AS Age,  
     
            TD.HospitalName, TD.NetworkCity,TD.State,TD.StatusOfIntimation,              
            TD.TypeOfIntimation,              
            TD.FIRNo,              
            TD.CreatedDateTime,              
            TD.TicketStatus AS StatusName,              
            TD.Grade AS Grade,              
            TD.NatureOfIllness,              
            TD.IntimationNumber,              
            TD.ClaimType,              
            TD.DateOfIntimation,              
            TD.DateOfAdmission,              
            TD.DateOfDischarge,              
            TD.TicketNumber,              
            TD.Remarks,              
              
            ISNULL(VEMDS1.FirstName,'') + ' ' + ISNULL(VEMDS1.LastName,'') AS AssignedDoctor,  
            EMDS.Id AS DesignationId,          
   CASE  
                WHEN ISNULL(VEMDS.FirstName,'') = '' AND ISNULL(VEMDS.LastName,'') = ''  
                THEN TD.ProposerName  
                ELSE ISNULL(VEMDS.FirstName,'') + ' ' + ISNULL(VEMDS.LastName,'')  
            END AS EmployeeName,  
             
              
            ISNULL(ECD.CompanyEmail,'') AS CompanyEmail,  
            ISNULL(VEMDS.MobileNumber,'') AS OracleMobile,  
            ISNULL(TD.Mobile1,'') AS ApiMobile,             
          
            SCT.Title AS Business,              
            VTM.Title AS Vertical,              
            CGM.Title AS CompanyGroup,              
            ECM.Title AS Company,              
  LM.Title AS Location,              
            DGM.Title AS DepartmentGroup,              
            ODM.Title AS Department,              
            ETM.Title AS EmployeeType,              
            GRD.Title AS GradeTitle,              
   VEMDS.Designation as DesignationName,              
              
            AD.UpdatedDateTime AS LastActionDate,              
            TD.AssignedDoctorId AS DoctorEmployeeId,              
   TD.ParticipantSourceId as EmployeeId,              
              
ISNULL(VEMDS1.FirstName, '') + ' ' + ISNULL(VEMDS1.LastName, '') AS AssignDoctor,              
ISNULL(VEMDS1.ExtensionNo,'') DoctorExtensionNo,              
ISNULL(VEMDS1.MobileNumber,'') DoctorMobileNumber,              
CASE    
    
    WHEN TDLog.PreviousDoctorEmployeeId IS NOT NULL    
        THEN ISNULL(VEMDS2.FirstName, '') + ' ' + ISNULL(VEMDS2.LastName, '')    
    ELSE 'System'    
END AS AssignBYDoctor    
,            AL.CreatedDate,  
          
              
              
            STUFF((SELECT ', ' + R.Remark              
                FROM sHospitalization.tIntimationRemark R              
                WHERE R.HospitalizationIntimationDetailId = TD.Id              
                ORDER BY R.CreatedDateTime DESC              
                FOR XML PATH(''), TYPE              
            ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS AllRemarks,              
              
            (              
                SELECT               
                    R.Id,              
                    R.Remark,              
                    R.CreatedDateTime,              
                    R.CreatedUserId              
                FROM sHospitalization.tIntimationRemark R              
                WHERE R.HospitalizationIntimationDetailId = TD.Id              
                ORDER BY R.CreatedDateTime DESC              
                FOR JSON PATH              
            ) AS RemarkHistoryJSON,  
   Isnull(TD.TreatingDoctor,'NA') as TreatingDoctor,  
     CASE   
        WHEN TD.Flag = 1  
             AND YEAR(TD.CreatedDateTime) = YEAR(GETDATE())  
        THEN 'Repeat Case'  
        ELSE ''  
    END AS RepeatCaseTitle,  
 ECD1.CompanyEmail as Reportingmanagermail,  
  @MailSubject AS MailSubject, @MailTemplate AS MailTemplate  
              
        FROM [sHospitalization].[tIntimationDetails] TD              
        INNER JOIN [sHospitalization].[tIntimationAssignedDoctor] AD               
            ON  TD.IntimationAssignedDoctorId = AD.Id              
        Left JOIN [sHrm].[tEmployeeMaster] EMD               
            ON EMD.Id = AD.DoctorEmployeeId              
        LEFT JOIN [sHrm].[tEmployeeMaster] EMP               
            ON EMP.Id = TD.ParticipantSourceId              
        LEFT JOIN [sHrm].[tEmployeeCommunications] ECD               
            ON ECD.EmployeeId = EMP.Id              
        LEFT JOIN [sHrm].[tEmployeeJobDetail] EJD               
            ON EJD.EmployeeId = EMP.Id              
        LEFT JOIN [sHrm].[tEmployeeTypeMaster] ETM               
            ON ETM.Id = EJD.EmployeeTypeId              
        LEFT JOIN [sHrm].[tEmployeeCompanyMaster] ECM               
            ON ECM.Id = EJD.EmployeeCompanyId              
        LEFT JOIN [sHrm].[tCompanyGroupMaster] CGM               
            ON CGM.Id = ECM.CompanyGroupId              
        LEFT JOIN [sHrm].[tVerticalMaster] VTM               
            ON VTM.Id = CGM.VerticalId              
        LEFT JOIN [sHrm].[tSectorMaster] SCT               
            ON SCT.Id = VTM.SectorId              
        Left JOIN [sCommon].[tLocationMaster] LM               
            ON LM.Id = EJD.EmployeeLocationId              
        LEFT JOIN [sCommon].[tDepartmentGroupMaster] DGM               
            ON DGM.Id = EJD.OrganizationDepartmentId              
        LEFT JOIN [sCommon].[tOrganizationDepartmentMaster] ODM               
            ON ODM.Id = EJD.OrganizationDepartmentId              
              
     LEFT JOIN [sHrm].[tEmployeeDesignationMaster] EMDS               
            ON EMDS.Id = EJD.EmployeeDesignationId              
     LEFT JOIN [sHrm].[vExtEmployeeMaster]  VEMDS               
            ON VEMDS.EmployeeId = TD.ParticipantSourceId              
        LEFT JOIN [sHrm].[vEmployeeCommonDetail]  VEMDS1               
            ON VEMDS1.EmployeeId = TD.AssignedDoctorId              
     LEFT JOIN [sHrm].[vExtEmployeeMaster]    VEMDS2              
            ON VEMDS2.UserId = TD.UpdatedUserId              
     LEFT JOIN [sHrm].[tGradeMaster] GRD               
            ON GRD.Id = VEMDS.GradeId         
 LEFT JOIN [sHrm].[tEmployeeCommunications] ECD1               
            ON ECD1.EmployeeId = EJD.ReportingMangerEmployeeId  
OUTER APPLY    
(    
    SELECT TOP 1 PreviousDoctorEmployeeId    
    FROM sHospitalization.tIntimationDoctorAssignmentLog L    
    WHERE L.HospitalizationIntimationDetailId = TD.Id    
    ORDER BY L.AssignedDate DESC    
) TDLog    
		LEFT JOIN #AlertLogLatest AL
    ON AL.ClaimIntimationDetailId = TD.Id 
  
              
        WHERE               
            TD.OrgId = @rOrgId              
            AND (TD.AssignedDoctorId = @rDoctorId OR @rDoctorId = 0)              
            -----------------------------------------              
            -- ✅ CORRECT DATE FILTER (NO 2025 LEAK)              
            -----------------------------------------              
            AND (              
                    (@FromDate IS NULL OR TD.CreatedDateTime >= @FromDate)              
                AND (@ToDate   IS NULL OR TD.CreatedDateTime <  @ToDate)              
            )              
              
            AND (                      EXISTS (              
                    SELECT 1              
                    FROM sCommon.fnsplit(@rCodeLike, ',') AS SplitValues              
                    WHERE               
                        (CHARINDEX(',', @rCodeLike) = 0 AND TD.EmpCode LIKE SplitValues.item + '%')              
                        OR              
                        (CHARINDEX(',', @rCodeLike) > 0 AND TD.EmpCode = SplitValues.item)              
                )              
                OR @rCodeLike = ''              
            )              
            AND (              
                      
              
                   @rStatusId = '0'              
                   OR TD.TicketStatus IN (              
             SELECT              
                           CASE Id              
                               WHEN 1 THEN 'New'              
                               WHEN 2 THEN 'Open'              
                               WHEN 3 THEN 'Close'              
                           END              
                       FROM @StatusIds              
                   )              
            )              
              
        ORDER BY TD.CreatedDateTime DESC       
   OPTION (RECOMPILE);  
    END TRY              
              
    BEGIN CATCH              
        DECLARE @ErrMsg NVARCHAR(MAX) = ERROR_MESSage();              
        INSERT INTO [sHospitalization].[tErrorLog]              
        (              
            ErrorMessage,               
            StoredProcedure,               
            ErrorDateTime,               
            UserId,               
            OrgId              
        )              
        VALUES              
        (              
            @ErrMsg,               
            'pIntimationDetailsListReviewGet',               
            GETUTCDATE(),               
            NULL,               
            @rOrgId              
        );              
    END CATCH              
END;    
  
GO


ALTER PROCEDURE [sHospitalization].[pIntimationDetailsListReviewGetExport]  
(  
    @rOrgId SMALLINT,  
    @rFromDate NVARCHAR(50) = NULL,  
    @rToDate NVARCHAR(50) = NULL,  
    @rDoctorId BIGINT = 0,  
    @rStatusId VARCHAR(50) = 0,  
    @rCodeLike NVARCHAR(MAX) = ''  
)  
AS  
BEGIN  
    SET NOCOUNT ON;  
    SET DATEFORMAT dmy;        -- ✅ FORCE DD/MM/YYYY parsing  
  
    DECLARE   
        @FromDate DATETIME = NULL,  
        @ToDate DATETIME = NULL;  
  
  
DECLARE @StatusIds TABLE (Id INT);  
  
INSERT INTO @StatusIds (Id)  
SELECT T.X.value('.', 'INT')  
FROM (  
    SELECT CAST(  
        '<x>' + REPLACE(@rStatusId, ',', '</x><x>') + '</x>'  
        AS XML  
    ) AS XmlData  
) A  
CROSS APPLY XmlData.nodes('/x') AS T(X);  
  
    -- Map rStatusId to text  
    --DECLARE @StatusText NVARCHAR(20) = NULL;  
    --IF @rStatusId = 1 SET @StatusText = 'New';  
    --IF @rStatusId = 2 SET @StatusText = 'Open';  
    --IF @rStatusId = 3 SET @StatusText = 'Close';  
 --IF @rStatusId = 4 SET @StatusText = 'New & Open';  
  
    -----------------------------------------  
    -- ✅ SQL 2008/2012 SAFE DATE CONVERSION  
    -----------------------------------------  
    IF (@rFromDate IS NOT NULL AND @rFromDate <> '')  
        SET @FromDate = CONVERT(DATETIME, @rFromDate, 103);  -- FORCES DD/MM/YYYY  
  
    IF (@rToDate IS NOT NULL AND @rToDate <> '')  
        SET @ToDate = DATEADD(DAY, 1, CONVERT(DATETIME, @rToDate, 103));  -- include full day  
  
    -----------------------------------------  
    -- If no code, make it empty  
    -----------------------------------------  
    IF (ISNULL(@rCodeLike, '') = '') SET @rCodeLike = '';  
  
    BEGIN TRY  
        SELECT   
         --   TD.Id AS IntimationId,  
              TD.EmpCode AS [Employee Code],  
         CASE   
              WHEN (ISNULL(VEMDS.FirstName, '') = '' AND ISNULL(VEMDS.LastName, '') = '')  
              THEN TD.ProposerName  
              ELSE ISNULL(VEMDS.FirstName, '') + ' ' + ISNULL(VEMDS.LastName, '')  
              END AS EmployeeName,  
   -- TD.Relation,  
   --TD.ProposerName as [Proposer Name],  
   --         ISNULL(EMD.FirstName, '') + ' ' + ISNULL(EMD.LastName, '') AS [Assigned Doctor],  
   --         --TD.DateOfAdmission AS [Date Of Admission],  
   --        -- TD.DateOfDischarge AS [Date Of Discharge],  
   --CONVERT(VARCHAR(10), TD.DateOfAdmission, 103) AS [Date Of Admission],  
   --         CONVERT(VARCHAR(10), TD.DateOfDischarge, 103) AS [Date Of Discharge],  
   --         TD.StatusOfIntimation AS [Status Of Intimation],  
   --         TD.TypeOfIntimation AS [Type Of Intimation],  
   --         TD.FIRNo AS [FIR No],  
   ----delete  
   --         --TD.CreatedDateTime,  
   --   TD.ClaimType AS [Claim Type],  
   --       TD.IntimationNumber AS [Intimation Numberr],  
   --         TD.TicketStatus AS [Status Name],  
   --        -- TD.Grade AS Grade,  
   -- GRD.Title AS Grade,  
   --         TD.IntimationNumber as [Intimation Number],  
   --         TD.ClaimType,  
   -- --   TD.DateOfIntimation,  
   -- CONVERT(VARCHAR(10), TD.DateOfIntimation, 103) AS [Date Of Intimation],  
   -- TD.TPACardNo,  
     --TD.Grade,  
      -- GRD.Title AS Grade,  
      --TD.GroupCode,  
      --TD.ModeOfStatus,  
      --TD.ModeOfStatus,   
      --TD.TAT,  
      --TD.AdmissionType,   
      VEMDS.Designation as Designation,  
      SCT.Title AS Business,  
      LM.Title AS [Hr Location],  
               CASE   
                            WHEN TD.TicketStatus != 'New'   
                                THEN ISNULL(VEMDS2.FirstName, '') + ' ' + ISNULL(VEMDS2.LastName, '')  
                            ELSE 'System'  
                        END AS [Assigned By],  
      ISNULL(VEMDS1.FirstName, '') + ' ' + ISNULL(VEMDS1.LastName, '') AS [Assigned To],  
        TD.BeneficiaryName,  
      TD.Relation,  
      TD.Age,  
      TD.HospitalName as [Hospital Name],  
         TD.State,  
      TD.NetworkCity as [Network City],  
      ISNULL(ECD.MobileNumber, '') AS [Mobile (Oracle)],  
      TD.Mobile1 as [Mobile (PHS)],  
      TD.NatureOfIllness as [Nature Of Illness],  
                        STUFF((  
                           SELECT   
                               CHAR(10) + CONVERT(varchar(10), R.CreatedDateTime, 103)   
                               + ': ' + R.Remark  
                           FROM sHospitalization.tIntimationRemark R  
                           WHERE R.HospitalizationIntimationDetailId = Td.Id  
                           ORDER BY R.CreatedDateTime DESC  
                           FOR XML PATH(''), TYPE  
                        ).value('.', 'nvarchar(max)'), 1, 1, '') AS [Remarks],  
      TD.TicketStatus as [Status]  ,
	     Isnull(TD.TreatingDoctor,'NA') as TreatingDoctor
  
        FROM [sHospitalization].[tIntimationDetails] TD  
        INNER JOIN [sHospitalization].[tIntimationAssignedDoctor] AD   
            ON  TD.IntimationAssignedDoctorId = AD.Id  
        LEFT JOIN [sHrm].[tEmployeeMaster] EMD   
            ON EMD.Id = AD.DoctorEmployeeId  
        LEFT JOIN [sHrm].[tEmployeeMaster] EMP   
            ON EMP.Id = TD.ParticipantSourceId  
        LEFT JOIN [sHrm].[tEmployeeCommunications] ECD   
            ON ECD.EmployeeId = EMP.Id  
        LEFT JOIN [sHrm].[tEmployeeJobDetail] EJD   
            ON EJD.EmployeeId = EMP.Id  
        LEFT JOIN [sHrm].[tEmployeeTypeMaster] ETM   
            ON ETM.Id = EJD.EmployeeTypeId  
        LEFT JOIN [sHrm].[tEmployeeCompanyMaster] ECM   
            ON ECM.Id = EJD.EmployeeCompanyId  
        LEFT JOIN [sHrm].[tCompanyGroupMaster] CGM   
            ON CGM.Id = ECM.CompanyGroupId  
        LEFT JOIN [sHrm].[tVerticalMaster] VTM   
            ON VTM.Id = CGM.VerticalId  
        LEFT JOIN [sHrm].[tSectorMaster] SCT   
            ON SCT.Id = VTM.SectorId  
        LEFT JOIN [sCommon].[tLocationMaster] LM   
            ON LM.Id = EJD.EmployeeLocationId  
        LEFT JOIN [sCommon].[tDepartmentGroupMaster] DGM   
            ON DGM.Id = EJD.OrganizationDepartmentId  
        LEFT JOIN [sCommon].[tOrganizationDepartmentMaster] ODM   
            ON ODM.Id = EJD.OrganizationDepartmentId  
        LEFT JOIN [sHrm].[vExtEmployeeMaster]  VEMDS   
            ON VEMDS.EmployeeId = TD.ParticipantSourceId  
        LEFT JOIN [sHrm].[vEmployeeCommonDetail]  VEMDS1   
            ON VEMDS1.EmployeeId = TD.AssignedDoctorId  
     LEFT JOIN [sHrm].[vExtEmployeeMaster]   VEMDS2  
            ON VEMDS2.UserId = TD.UpdatedUserId  
   LEFT JOIN [sHrm].[tGradeMaster] GRD   
            ON GRD.Id = VEMDS.GradeId  
     LEFT JOIN [sHrm].[tEmployeeDesignationMaster] EMDS   
            ON EMDS.Id = EJD.EmployeeDesignationId  
  
        WHERE   
            TD.OrgId = @rOrgId  
            AND (AD.DoctorEmployeeId = @rDoctorId OR @rDoctorId = 0)  
  
            -----------------------------------------  
            -- ✅ CORRECT DATE FILTER (NO 2025 LEAK)  
            -----------------------------------------  
            AND (  
                    (@FromDate IS NULL OR TD.CreatedDateTime >= @FromDate)  
                AND (@ToDate   IS NULL OR TD.CreatedDateTime <  @ToDate)  
            )  
  
            AND (  
                EXISTS (  
                    SELECT 1  
                    FROM sCommon.fnsplit(@rCodeLike, ',') AS SplitValues  
                    WHERE   
                        (CHARINDEX(',', @rCodeLike) = 0 AND TD.EmpCode LIKE SplitValues.item + '%')  
                        OR  
                        (CHARINDEX(',', @rCodeLike) > 0 AND TD.EmpCode = SplitValues.item)  
                )  
                OR @rCodeLike = ''  
            )  
            AND (  
                --TD.TicketStatus = @StatusText  
               @rStatusId = '0'  
                   OR TD.TicketStatus IN (  
                       SELECT  
                           CASE Id  
                               WHEN 1 THEN 'New'  
                               WHEN 2 THEN 'Open'  
                               WHEN 3 THEN 'Close'  
                           END  
                       FROM @StatusIds  
                   )  
  
            )  
  
        ORDER BY TD.CreatedDateTime DESC;  
    END TRY  
  
    BEGIN CATCH  
        DECLARE @ErrMsg NVARCHAR(MAX) = ERROR_MESSAGE();  
        INSERT INTO [sHospitalization].[tErrorLog]  
        (  
            ErrorMessage,   
            StoredProcedure,   
            ErrorDateTime,   
            UserId,   
            OrgId  
        )  
        VALUES  
        (  
            @ErrMsg,   
            'pIntimationDetailsListReviewGet',   
            GETUTCDATE(),   
            NULL,   
            @rOrgId  
        );  
    END CATCH  
END;  

