USE [LABORATORY]
GO
/****** Object:  StoredProcedure [dbo].[Lab_Get_RequestFormPrintOut_2010]    Script Date: 11/21/2017 16:18:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Lab_Get_RequestFormPrintOut_2010]
	@UserID VARCHAR(10),
	@SectionID VARCHAR(4),
	@RefNum VARCHAR(20) = ''
AS


DECLARE @TempItemID VARCHAR(30)

SELECT
	ISNULL( ( SELECT MAX(ISNULL(LastName,'') + ', ' + ISNULL(FirstName,'') + ' ' + ISNULL(MiddleName,''))
                    FROM Password..tbPasswordMain WHERE EmployeeID = A.UserId
                    GROUP BY EmployeeID ), '' ) [UserName],
	IsNull(C.LabSectionID,'') as SectionID,
	B.LabSection + ' REQUEST FORM' AS [Heading],
	A.LabExamID,
	C.LabExam,
	D.Specimen,
	B.ISOFormCode,
	A.ProcessedBy,
	CASE WHEN (ISNULL((SELECT TOP 1 AMPickup FROM STATION..tbNurseCommunicationFile WHERE ReferenceNum = @RefNum AND ItemID = A.LabExamID),0))=1
		THEN 'In AM PICK' ELSE A.Stat
	END AS [Stat],
	A.Company,
	
	CASE WHEN A.ReqDoctor = '0' 
			THEN ISNULL((SELECT TOP 1 OutsideDoctor FROM tbLabMaster WHERE RefNum = @RefNum),'')
		 ELSE E.LastName + ', ' + E.FirstName + '  ' + LEFT(E.MiddleName,1) + '.' 
	END AS ReqDoctor,
	
	A.RequestDate,
	ISNULL( ( SELECT TOP 1 MAX(ISNULL(LastName,'') + ', ' + ISNULL(FirstName,'') + ' ' + ISNULL(MiddleName,''))
                    FROM Password..tbPasswordMain WHERE EmployeeID = A.ClerkID
                    GROUP BY EmployeeID ), '' ) AS Clerk,
                    
	ISNULL(A.PrintStatus,'') AS PrintStatus,
	ISNULL(F.Exam,'Z') AS ProfileName,
	ISNULL(A.NurseRemarks,'') AS NurseRemarks,
	ISNULL(A.StationID,'') AS StationId,
	CASE 
	     WHEN A.LabExamID = '995' THEN 'B'
	     WHEN A.LabExamID = '996' THEN 'C'
	     ELSE '' 
	END AS Glucose,
	A.QtyIssued,
	A.OPService,
	(SELECT TOP 1 cf.amount FROM laboratory..tblabmaster cf WHERE cf.itemid = a.labexamid AND refnum = @RefNum) / a.qtyissued AS amount, --could have used requestnum lol
	(SELECT TOP 1 ISNULL(Remarks,'') FROM tbLABMaster WHERE RefNum = @RefNum) AS Remarks
FROM tbLabRequestForm_Dump A
     LEFT OUTER JOIN BUILD_FILE..tbCoLabExam C ON A.LabExamID = C.LabExamID
     LEFT OUTER JOIN BUILD_FILE..tbCoLabSection B ON C.LabsectionID = B.LabSectionID
     LEFT OUTER JOIN BUILD_FILE..tbCoLabSpecimen D ON A.SpecimenId = D.SpecimenID
     LEFT OUTER JOIN BUILD_FILE..tbCoDoctor E ON A.ReqDoctor = E.DoctorID
     LEFT OUTER JOIN BUILD_FILE..tbCoLabProfile F ON A.ProfileId = F.ExamId
WHERE A.UserId = @UserId AND A.Refnum = @RefNum