USE [LABORATORY]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_LabWithVerifiedResult]    Script Date: 9/23/2020 2:44:47 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[fn_LabWithVerifiedResult] (@IdNum varchar(20),@HospNumber varchar(20) = null )  
RETURNS varchar(1) AS 
BEGIN 
	Declare @WithVerifiedResult varchar(1),
			@HospNum varchar(10)

	Set @IdNum = ltrim(rtrim(@idNum));
	if @HospNumber is not null 
	begin
	 Set @HospNum = ltrim(rtrim(@HospNumber));
	end
	else
	begin
		Set @HospNum = (
			Case when IsNumeric(@IdNum) = 1 Then (Select top 1 HospNum from Patient_Data..tbPatient where IdNum = @IdNum)
			     Else
				Case When @IdNum like '%B' Then (Select top 1 HospNum from Patient_Data..tbOutPatient where IdNum = @IdNum)
				     When @IdNum like 'T%C' Then substring(@IdNum, 2, len(@IdNum)-2)
				     Else @IdNum
				End
			End
			);
	End
	If exists (	Select B.RefNum from  tbLabLogbook B 
					Inner Join LABORATORY..tbLABMaster LabMaster 
						on B.RequestNum = LabMaster.RequestNum and IsNull(LabMaster.RequestStatus,'') <> 'R'
					left outer join Build_File..tbCoLabExam E on E.Labexamid = B.LabExamID
				where B.HospNum = @HospNum 
					and IsNull(B.VerifyId,'') <> '' 
					and IsNull(E.ResultConfidential,'N')= 'N')
		Set @WithVerifiedResult  = '1' 
	Else 
		Set @WithVerifiedResult  ='0'
		
Return @WithVerifiedResult
END