USE [LABORATORY]
GO
/****** Object:  StoredProcedure [dbo].[Lab_Get_PatientDemographics]    Script Date: 1/28/2021 1:59:02 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[Lab_Get_PatientDemographics]--'1737'
	@RequestNum VARCHAR(20) = NULL
AS
SET NOCOUNT ON;
DECLARE @HospNum VARCHAR(20), 
		@IdNum VARCHAR(20), 
		@Pathologist AS VARCHAR(50)

/*
SET DATEFIRST 1
DECLARE @Today AS VARCHAR(15)

SET @Today =(SELECT  
	CASE
		WHEN DATEPART(dw, GETDATE()) = 1 THEN 'Monday'
		WHEN DATEPART(dw, GETDATE()) = 2 THEN 'Tuesday'
		WHEN DATEPART(dw, GETDATE()) = 3 THEN 'Wednesday'
		WHEN DATEPART(dw, GETDATE()) = 4 THEN 'Thursday'
		WHEN DATEPART(dw, GETDATE()) = 5 THEN 'Friday'
		WHEN DATEPART(dw, GETDATE()) = 6 THEN 'Saturday'
		WHEN DATEPART(dw, GETDATE()) = 7 THEN 'Sunday'
END AS 'Today')*/

SET @Pathologist = (SELECT TOP 1 CASE ISNULL(D.Firstname +' ' + D.MiddleName  + ' '+ D.LastName + ' ' + D.Title,'')
							WHEN '' THEN Sectionhead
							ELSE D.Firstname +' ' + D.MiddleName  + ' '+ D.LastName + ' ' + D.Title
						END AS Sectionhead
					FROM LABORATORY..tbLabMaster M
						LEFT OUTER JOIN tbLabLogbook L
							ON m.requestnum = l.Requestnum
						LEFT OUTER JOIN BUILD_FILE..tbCoLabExam E
							ON M.Itemid =  E.labExamID
						LEFT OUTER JOIN BUILD_FILE..tbCoLabSection S
							ON E.LabSectionID = S.LabSectionID
						LEFT OUTER JOIN build_file..tbcodoctor D
							ON L.Pathologist = D.DoctorID
					WHERE m.requestnum = @RequestNum)



SELECT  
	@HospNum = HospNum, 
	@IdNum = IdNum
FROM tbLabLogbook 
WHERE requestNum = @RequestNum;

	SELECT 	M.HospNum,
		L.IdNum AS IdNum,		
		ISNULL(M.LastName,'') + ', ' + ISNULL(M.FirstName,'') + ' ' + ISNULL(M.MiddleName,'') AS PatientName,
	    	CASE 
	      	   WHEN M.birthdate IS NULL THEN CONVERT(VARCHAR(3), ISNULL(M.Age,''))
	      	   WHEN M.birthdate <= '01/01/1900' THEN CONVERT(VARCHAR(3), ISNULL(M.Age,''))
	      	   ELSE Laboratory.dbo.fn_LabComputeAge(M.Birthdate,GETDATE()) 
	    	END AS Age,
		Sex = CASE WHEN ISNULL(M.Sex,'') = 'F' THEN 'Female'
		           ELSE 'Male'
		      END,
		ISNULL(C.CivilStatus,'') AS CivilStatus,
		ISNULL(L.RoomID,'') AS RoomID,
		ISNULL(R.RoomDescription,'') AS CLASS,
		--ISNULL(L.ResultDate, GETDATE()) AS ResultDate,
		Isnull(L.ResultDate,GETDATE()) AS ResultDate,
		CASE WHEN ISNULL(LM.NurseRequestNum,'') = '' THEN LM.TransDate ELSE N.RequestDate END AS RequestDate,
		ISNULL(L.AccessionNum,'') AS [Acc.#],
		CASE WHEN L.DoctorId = '0' THEN	ISNULL(LM.OutsideDoctor,'')
		     ELSE ISNULL(D.LastName,'') + ', ' + ISNULL(D.FirstName,'') + ' ' + ISNULL(D.MiddleName,'')
		END AS Doctor,
		LM.Rush,
		LM.RefNum,
		CASE WHEN LM.Transplant = 0 THEN 'NON TRANSPLANT'
		     ELSE 'TRANSPLANT'
		END AS Transplant,
		ISNULL(R.StationId,'') AS Ward,
		ISNULL(LM.ORNum,'') AS OrNumber,
		ISNULL(L.VerifyDate,'') AS VerifyDate,
		CASE WHEN ISNULL(LM.PhysicianId,'') = '0' THEN
			ISNULL(LM.OutsideDoctor,'')
		     WHEN ISNULL(LM.PhysicianId,'') <> '' AND ISNULL(LM.PhysicianId,'') <> '0' THEN
			'DR. ' + ISNULL(PD.FirstName,'') + ' ' + ISNULL(PD.MiddleName,'') + ' ' +  ISNULL(PD.LastName,'')
		     ELSE ''
		END AS Physician,
		ISNULL(LM.OPService,0) AS OPService,
		ISNULL(Reader.Firstname,'')+ ' ' + ISNULL(Reader.MiddleName,'') + ' ' +	ISNULL(Reader.LastName,'')+ ', M.D.' AS Reader,
		ISNULL(S.Specimen,'') AS Specimen,
		ISNULL(Laboratory.dbo.fn_LabGetMedTech(ISNULL(L.VerifyID,ISNULL(L.VerifyID,''))),'') AS Medtech,
		ISNULL(LS.LabSection,'') + ' SECTION' AS SECTION,
		ISNULL(LD.Division,'') AS Division,
		ISNULL(E.ISOFormCode,'') AS ISOFormCode,
		CASE ISNULL(@Pathologist,'')
			WHEN '' THEN ISNULL(LS.SectionHead,'')
			ELSE @Pathologist
		END AS Pathologist,

		CASE WHEN ISNULL(E.LabExam,'') LIKE '%Renal%' THEN '' 
		            ELSE 
						CASE WHEN ISNULL(L.ResidentId,'') = '' THEN '' 
							 ELSE ISNULL(Resident.Firstname,'')+ ' ' + ISNULL(Resident.MiddleName,'')  + ' ' + ISNULL(Resident.LastName,'')+ ', M.D.'  
						END
		END  AS Resident,
		
		ISNULL(L.Method, '') AS Method,
		M.BloodType,
   		DateFinished,
		ISNULL(Laboratory.dbo.fn_LabGetMedTechLicense(ISNULL(L.VerifyID,ISNULL(L.VerifyID,''))),'') AS License,
   		ISNULL(dbImage.dbImage,'')		AS [PathoSign],
   		ISNULL(MedTech.Signature,'')	AS [MedTechSign],
   		M.BirthDate AS [BirthDate],
		Patient_Data.dbo.fn_GetCompleteAddress(L.HospNum) AS [Address],
		Patho.License as PathoLicense,
		ISNULL(Laboratory.dbo.fn_LabGetMedTech(ISNULL(L.MedTechID,ISNULL(L.MedTechID,''))),'') AS Performer,
		ISNULL(Laboratory.dbo.fn_LabGetMedTechLicense(ISNULL(L.MedTechID,ISNULL(L.MedTechID,''))),'') AS PerformLic,
		ISNULL(Performer.Signature,'')	AS [PerformerSign],
		CAse when isnumeric(@IdNum) = 1 
		 then (select top 1 admDate from patient_data..tbpatient where idnum=@IdNum)
		 else
		  (select top 1 admDate from patient_data..tboutpatient where idnum=@IdNum)
		  end as AdmissionDate
	FROM tbLabLogbook L
			LEFT OUTER JOIN  vwPatientResultEntry M ON M.HospNum = L.HospNum
			LEFT OUTER JOIN Build_File..tbCoCivilStatus C ON M.CivilStatus = C.CivilStatusID
			LEFT OUTER JOIN Build_File..tbCoDoctor D ON L.DoctorId = D.DoctorID
			LEFT OUTER JOIN tbLabMaster LM ON L.RequestNum = LM.RequestNum
			LEFT OUTER JOIN Station..tbNurseCommunicationFile N ON LM.NurseRequestNum = N.RequestNum
			LEFT OUTER JOIN Build_File..tbCoRoom R ON R.RoomId = L.RoomID
			LEFT OUTER JOIN Build_File..tbCoDoctor PD ON LM.PhysicianId = PD.DoctorID
			LEFT OUTER JOIN Build_File..tbCoDoctor Reader ON LM.ProfId = Reader.DoctorID
			LEFT OUTER JOIN Build_File..tbCoLabSpecimen S ON S.SpecimenId = LM.SpecimenID
			LEFT OUTER JOIN Build_File..tbCoLabSection LS ON LS.LabSectionID = LM.SectionId
   			LEFT OUTER JOIN Build_File..tbCoDivision LD ON LD.DivisionCode = LS.DivisionCode
			LEFT OUTER JOIN Build_File..tbCoLabExam E ON E.LabExamID = L.LabExamID
			LEFT OUTER JOIN Build_File..tbCoLabValues V ON E.LabExamID = V.Code
			LEFT OUTER JOIN Build_File..tbCoDoctor Resident ON L.ResidentId = Resident.DoctorID
			LEFT OUTER JOIN BUILD_FILE..tbDBImage dbImage ON L.Pathologist = dbImage.IDKEY AND
							DBImage.FieldName = 'Doctor Sign' AND TableName = 'TbCoDoctor'
			LEFT OUTER JOIN BUILD_FILE..tbCoMedTech MedTech ON L.VerifyID = MedTech.MedTechID
			LEFT OUTER JOIN BUILD_FILE..tbCoMedTech Performer ON L.MedtechID = MedTech.MedTechID
			LEFT OUTER JOIN BUILD_FILE..tbcodoctor Patho ON L.Pathologist = Patho.DoctorID
	WHERE L.RequestNum = @RequestNum