USE [LABORATORY]
GO
/****** Object:  StoredProcedure [dbo].[Lab_RequestStatus]    Script Date: 12/19/2018 10:04:19 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Lab_RequestStatus] 
	@StartDate as varchar(20),
	@EndDate as varchar(20),
	@SearchCriteria as varchar(20),
	@SectionId as varchar(5),
	@SectionId_extra as varchar(5)=null
AS 

/* 
	For checking the status of the request
	Added the ff .
	Start date -  Start of Date Range on searching
	End Date  -  End of Date Range on searching
	Search Criteria - Lastname of patient
	AGE,DOctor,Room and Birthdate
*/


if @StartDate is null 
	begin 
		set @StartDate  = convert(varchar,getdate(),101) 
	end
if @EndDate is null 
	Begin
		set @EndDate  = convert(varchar,getdate(),101) 
	End

if @SectionId = '' 
	begin
		select A.Hospnum [Hosp #], A.IdNum [Adm #], 
			C.LastName + ', ' + C.FirstName + ' ' + left(isnull(C.MiddleName,''),1) + '.' as [Patient Name],
			A.Itemid [Item Code], B.LAbExam [Item Description],
			A.TransDate as [Transaction Date],
			case when A.RequestStatus = 'X' then 'X'
				 when A.RequestStatus = 'W' and D.verifydate is not null
						then 'V'
				 when A.RequestStatus = 'W' then 'W'
			else 'R' end as [Request Status],
			A.RoomID,
			A.DoctorId,
			convert(varchar,C.Birthdate,101) as Birthdate,
			Age =Laboratory.dbo.fn_LabComputeAge(C.BirthDate, getdate()),
			C.Sex,
			case when A.IdNum = 'CASH' then A.ORnum		
				else A.refnum 
			End as Refnum,
			Isnull(A.ORNum,'') as OrNum
		from Laboratory..tblabmaster A
			LEFT OUTER JOIN BUILD_FILE..tbcoLabExam B
				ON A.Itemid = B.LabExamID
			LEFT OUTER JOIN PATIENT_DATA..tbmaster C
				ON A.HospNum = C.HospNum
			LEFT OUTER JOIN Laboratory..tblablogbook D
				on cast(A.RequestNum as varchar)= D.Requestnum
		where A.transdate between @StartDate and @EndDate + ' 23:59:59'
			And isnumeric (A.Hospnum) = 1 
			and C.LastName like  @SearchCriteria + '%'

		order by A.Transdate desc
	end
else
	begin
		select A.Hospnum [Hosp #], A.IdNum [Adm #], 
			C.LastName + ', ' + C.FirstName + ' ' + left(isnull(C.MiddleName,''),1) + '.' as [Patient Name],
			A.Itemid [Item Code], B.LAbExam [Item Description],
			A.TransDate as [Transaction Date],
			case when A.RequestStatus = 'X' then 'X'
				 when A.RequestStatus = 'W' and D.verifydate is not null
						then 'V'
				 when A.RequestStatus = 'W' then 'W'
			else 'R' end as [Request Status],
			A.RoomID,
			A.DoctorId,
			convert(varchar,C.Birthdate,101) as Birthdate,
			Age =Laboratory.dbo.fn_LabComputeAge(C.BirthDate, getdate()),
			C.Sex,
			case when A.IdNum = 'CASH' then A.ORnum		
				else A.refnum 
			End as Refnum,
			Isnull(A.ORNum,'') as OrNum
		from Laboratory..tblabmaster A
			LEFT OUTER JOIN BUILD_FILE..tbcoLabExam B
				ON A.Itemid = B.LabExamID
			LEFT OUTER JOIN PATIENT_DATA..tbmaster C
				ON A.HospNum = C.HospNum
			LEFT OUTER JOIN Laboratory..tblablogbook D
				on cast(A.RequestNum as varchar) = D.Requestnum
		where A.transdate between @StartDate and @EndDate + ' 23:59:59'
			And isnumeric (A.Hospnum) = 1 
			and C.LastName like  @SearchCriteria + '%'
			And A.SectionId = @SectionId

		order by A.Transdate desc
	End