USE [LABORATORY]
GO
/****** Object:  StoredProcedure [dbo].[Lab_FormMaker_GetResult_ReagentValues]    Script Date: 02/08/2018 15:55:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Lab_FormMaker_GetResult_ReagentValues]
	@RequestNum varchar(20)
AS

Declare @RefNum varchar(20), 
	@FormType varchar(1),
	@VerifiedBy varchar(10),
	@SectionId varchar(3),
	@Valuetype varchar(3),
	@AccessionNum varchar(100),
	@ItemId varchar(10)
	

Select @FormType = Formtype, @Refnum = RefNum,
	@VerifiedBy = VerifyID, @SectionId = LabSectionId, @ItemId = LabExamId,
	@AccessionNum = AccessionNum
From tbLabLogbook where requestnum = @RequestNum;

if @FormType = 'P'
	Begin
	Set @FormType = (select IsNull(Valuetype,FormType) as Valuetype  from BUILD_FILE..tbCoLabValues where Code=@ItemId)
	END



--If @FormType = 'P' 
--   Begin
--	select	L.RequestNum as RequestNum,
--		B.ItemId as ExamCode,
--		IsNull(V.ResultName, '') as ExamName,
--		IsNull(LS.LabSection,'') + ' SECTION' as Section,
--		IsNull(V.FormType,'') as FormType,
--		IsNull(B.strResult, IsNull(B.Result, '')) as sResult,
--		IsNull(B.strNValues, '') as sNValues,
--		IsNull(B.MinValue, '') as sMin,
--		IsNull(B.MaxValue, '') as sMax,
--		Isnull(B.Unit, '') as sUnit,
--		IsNull(B.ConvStrResult, isnull(B.ConvResult,'')) as cResult,
--		IsNull(B.ConvStrNValues, '') as cNValues,
--		IsNull(B.ConvMin, '') as cMin,
--		IsNull(B.ConvMax, '') as cMax,
--		IsNull(B.ConvUnit, '') as cUnit,
--		IsNull(B.Factor,0) as Factor,
--		IsNull(L.Verifydate, getdate()) as VerifyDate, 
--		IsNull(L.SampleSubmitted, getdate()) as SampleSubmitted, 
--		IsNull(L.LRARecordedTime, getdate()) as LRATime,
--		IsNull(L.ResultDate, getdate()) as ResultDate,
--		Isnull(L.Remarks,'') as Remarks,
--		IsNull(L.AccessionNum, '') as AccessionNum,
--		IsNull(M.Rush,'') as Stat,
--		IsNull(L.VerifyDate,'') as VerifyDate,
--		IsNull(E.LabExam,'') as ProfileName,
--		isnull(B.Remarks,'') as Comment,
--		isnull(B.RemarksTag,'') as RemarksTag
--	from tbLabResultNValues B
--		left outer join tbLabLogbook L on B.RequestNum = L.RequestNum
--		Left Outer Join tbLabMaster M on M.RequestNum = L.RequestNum
--	   	Left Outer Join Build_FIle..tbCoLabSection LS on LS.LabSectionID = L.LabSectionId
--	   	Left Outer Join Build_FIle..tbCoLabValues V on V.Code = B.ItemID
--		Left Outer join Build_FIle..tbCoLabExam E on E.LabExamId = L.LabExamId
--	where B.RequestNum = @RequestNum
--	order by sortorder;
--   End
--Else
--   Begin
	select	L.RequestNum as RequestNum,
		L.LabExamId as ExamCode,
		IsNull(V.ResultName, '') as ExamName,
		IsNull(LS.LabSection,'') + ' SECTION' as Section,
		IsNull(V.FormType,'') as FormType,
		IsNull(B.strResult, IsNull(B.Result, '')) as sResult,
		IsNull(B.strNValues, '') as sNValues,
		IsNull(B.MinValue, '') as sMin,
		IsNull(B.MaxValue, '') as sMax,
		Isnull(B.Unit, '') as sUnit,
		IsNull(B.ConvStrResult, isnull(B.ConvResult,'')) as cResult,
		IsNull(B.ConvStrNValues, '') as cNValues,
		IsNull(B.ConvMin, '') as cMin,
		IsNull(B.ConvMax, '') as cMax,
		IsNull(B.ConvUnit, '') as cUnit,
		IsNull(B.Factor,0) as Factor,
		IsNull(L.Verifydate, getdate()) as VerifyDate, 
		IsNull(L.SampleSubmitted, getdate()) as SampleSubmitted, 
		IsNull(L.LRARecordedTime, getdate()) as LRATime,
		IsNull(L.ResultDate, getdate()) as ResultDate,
		Isnull(L.Remarks,'') as Remarks,
		IsNull(L.AccessionNum, '') as AccessionNum,
		IsNull(M.Rush,'') as Stat,
		IsNull(L.VerifyDate,'') as VerifyDate,
		ISnull((select Isnull(Exam,'') from Build_file..tbcolabProfile where ExamId = L.LabExamId),'')
		 as ProfileName,
		--IsNull(E.LabExam,'')'' as ProfileName,
		isnull(B.Remarks,'') as Comment,
		isnull(B.RemarksTag,'') as RemarksTag
	from tbLabResultNValues B
		left outer join tbLabLogbook L on B.RequestNum = L.RequestNum
		Left Outer Join tbLabMaster M on M.RequestNum = L.RequestNum
	   	Left Outer Join Build_File..tbCoLabSection LS on LS.LabSectionID = L.LabSectionId
	   	Left Outer Join Build_File..tbCoLabValues V on V.Code = B.ItemID
	   	Left Outer join Build_FIle..tbCoLabExam E on E.LabExamID= L.LabExamId
	where  L.RefNum = @RefNum  and L.LabSectionID = @SectionId and V.FormType = @FormType
		   and L.AccessionNum = @AccessionNum
	--and L.VerifyID = @VerifiedBy
	order by sortorder;
   --End