USE [LABORATORY]
GO
/****** Object:  StoredProcedure [dbo].[Lab_View_PatientExams_PerCS]    Script Date: 9/23/2020 2:57:52 PM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[Lab_View_PatientExams_PerCS]
   @RefNum varchar(10),
   @ViewType varchar(1) = '0'
AS

If @ViewType = '0' 
   Begin
  	Select  L.IDNum [ID Number], 
	            L.LabSectionID [SectionID],
	            S.LabSection [SectionName],
	            E.LabExam [LabExam],
		    E.ShortName [ShortName], 
	            L.RequestNum,
	            L.ResultDate [ResultDate],
	            L.TransDate [RequestDate],
		    L.HospNum,             
	            /* ***** Note Formtype here is only temporary,
	                        once the process of saving formtype to result tables is complete then
	                        [FormType] will be read from L itself.... ********** */
	            L.FormType,
	            L.LabExamID, 
	            L.PatientType,
		L.RoomID,
		(isnull(D.LastName,'') + ' ' + isnull(D.FirstName,'')) as doctor,
		--(isnull(M.LastName,'') + ' ' + isnull(M.FirstName,'')) as medtech ,
		IsNull( ( Select Max(IsNull(FirstName,'') + ' ' + IsNull(MiddleName,'') + ' ' + IsNull(LastName,''))
	                    from Password..tbPasswordMain Where EmployeeID = L.UserID
	                    Group by EmployeeID ), '' ) [MedTech],
		(select Max(isnull(FirstName,'') + ' ' + isnull(MiddleName,'')+' '+isnull(LastName,''))
			from password..tbpasswordmain
			where employeeid = L.VerifyID
	                Group by EmployeeID
		  ) as [Verified By],
		IsNull(V.ValueType,'') as ValueType,
		Isnull(Sp.SpecimenId,'') as SpecimenId,
		IsNull(Sp.Specimen, '') as Specimen,
		convert(varchar(10), IsNull(L.VerifyDate,''), 101) + ' ' + convert(varchar(10), IsNull(L.VerifyDate,''), 108) as VerifyDate,
		IsNull(L.AccessionNum,'') AccessionNum,
		IsNull(L.ResultPrinted,'0') as ResultPrinted
	From laboratory..tbLabLogbook L 
		left outer join build_file..tbCoLabSection S on L.LabSectionID = S.LabSectionID
		left outer join build_file..tbCoLabExam E on L.LabExamID = E.LabExamID
		left outer join build_file..tbCoDoctor D on L.doctorid = D.DoctorID
		left outer join build_file..tbCoMedTech M on L.medtechid = M.medtechid
		left outer join build_file..tbCoLabValues V on L.LabExamID = V.Code
		left outer join build_file..tbCoLabSpecimen Sp on Sp.SpecimenId = L.SpecimenID
	Where (L.VerifyDate is not null or L.VerifyDate <> '')
	       and L.RefNum = @RefNum and IsNull(E.ResultConfidential,'') <> 'Y'
	Order by SectionName, LabExam
   End
--~*~*~*~ View BBK Confidential Results
Else
   Begin
	Select  L.IDNum [ID Number], 
	            L.LabSectionID [SectionID],
	            S.LabSection [SectionName],
	            E.LabExam [LabExam],
		    E.ShortName [ShortName], 
	            L.RequestNum,
	            L.ResultDate [ResultDate],
	            L.TransDate [RequestDate],
		    L.HospNum,             
	            /* ***** Note Formtype here is only temporary,
	                        once the process of saving formtype to result tables is complete then
	                        [FormType] will be read from L itself.... ********** */
	            L.FormType,
	            L.LabExamID, 
	            L.PatientType,
		L.RoomID,
		(isnull(D.LastName,'') + ' ' + isnull(D.FirstName,'')) as doctor,
		--(isnull(M.LastName,'') + ' ' + isnull(M.FirstName,'')) as medtech ,
		IsNull( ( Select Max(IsNull(FirstName,'') + ' ' + IsNull(MiddleName,'') + ' ' + IsNull(LastName,''))
	                    from Password..tbPasswordMain Where EmployeeID = L.UserID
	                    Group by EmployeeID ), '' ) [MedTech],
		(select Max(isnull(FirstName,'') + ' ' + isnull(MiddleName,'')+' '+isnull(LastName,''))
			from password..tbpasswordmain
			where employeeid = L.VerifyID
	                Group by EmployeeID
		  ) as [Verified By],
		IsNull(V.ValueType,'') as ValueType,
		Isnull(Sp.SpecimenId,'') as SpecimenId,
		IsNull(Sp.Specimen, '') as Specimen,
		convert(varchar(10), IsNull(L.VerifyDate,''), 101) + ' ' + convert(varchar(10), IsNull(L.VerifyDate,''), 108) as VerifyDate,
		IsNull(L.AccessionNum,'') AccessionNum,
		IsNull(L.ResultPrinted,'0') as ResultPrinted
	From laboratory..tbLabLogbook L 
		left outer join build_file..tbCoLabSection S on L.LabSectionID = S.LabSectionID
		left outer join build_file..tbCoLabExam E on L.LabExamID = E.LabExamID
		left outer join build_file..tbCoDoctor D on L.doctorid = D.DoctorID
		left outer join build_file..tbCoMedTech M on L.medtechid = M.medtechid
		left outer join build_file..tbCoLabValues V on L.LabExamID = V.Code
		left outer join build_file..tbCoLabSpecimen Sp on Sp.SpecimenId = L.SpecimenID
	Where (L.VerifyDate is not null or L.VerifyDate <> '')
	       and L.RefNum = @RefNum
	Order by SectionName, LabExam	
   End