USE [LABORATORY]
GO
/****** Object:  StoredProcedure [dbo].[LAB_Get_NursingRequestsOPD]    Script Date: 7/8/2020 2:25:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[LAB_Get_NursingRequestsOPD]
  @Mode int,
  @LastName varchar(30),
  @UserName varchar(50) = ''

AS

/* *** 
     Mode=0 Detect Mode
     Mode=1 Fetch Mode
     Mode=2 Get BBK Requests
*/  


declare @SectionID as varchar(2)

set @SectionID = (select LabSectionID from tblab_Extractionlogin where NTUserName = @UserName);


IF (@Mode=0)
    BEGIN
   	Select HospNum
   	From station..tbNurseCommunicationFile
   	Where (LEN(RTRIM(RecordStatus))=0  or RecordStatus=' ') and RevenueID='LB'
    END
ELSE
  IF (@Mode=1)
    BEGIN
       if datepart(hour,getdate()) < 18 AND @SectionID <> '00'
          begin
	       Select
	          IsNull(MasterIn.LastName,'')+', '+IsNull(MasterIn.FirstName,'') + ' ' +IsNull(MasterIn.MiddleName,'') [Name],
	          A.IDNum as [Admission #],
		  C.LabExam as [Examination],
		  A.Stat,
		  A.SpecimenID,
		  F.Specimen,
	          A.RequestDate as [Date], 
	          A.Remarks, 
	          A.DoctorID,
	          A. ItemID, 
	          A.Amount, 
	          A.RequestNum, 
	          '1' as Quantity,
		  (select Max(isnull(FirstName,'') + ' ' + isnull(MiddleName,'')+' '+isnull(LastName,''))
			from password..tbpasswordmain
			where employeeid = A.Userid
	                Group by EmployeeID
		  ) as Nurse,
		  A.StationID,
	          A.PatientType,
		  A.Dialysis,
		  A.Transplant,
		  A.UserId as NurseId,
		  isnull(A.Printed,'') as Printed
	
	     From station..tbNurseCommunicationFile A
		Inner Join Patient_Data..tbPatient D on A.IdNum = D.IdNum 
	        Inner Join Patient_Data..tbMaster MasterIn on D.HospNum = MasterIn.HospNum
		Inner Join Build_File..tbCoLabExam C on A.ItemID = C.LabExamID
		Left Outer Join Build_File..tbCoLabSpecimen F on A.SpecimenId = F.SpecimenId
	     Where isnull(A.RecordStatus,'') = ''  and A.RevenueID='LB' 
		and MasterIn.LastName like @LastName + '%'
		and IsNumeric(right(A.IdNum,1)) = 1 
		and C.LabSectionID = @SectionID
		AND  isnull(StationID,'') in ('OPD')
	
	     Union All
	
	     Select
	          IsNull(MasterIn.LastName,'')+', '+IsNull(MasterIn.FirstName,'') + ' ' +IsNull(MasterIn.MiddleName,'') [Name],
	          A.IDNum as [Admission #],
		  C.LabExam as [Examination],
		  A.Stat,
		  A.SpecimenID,
		  F.Specimen,
	          A.RequestDate as [Date], 
	          A.Remarks, 
	          A.DoctorID,
	          A. ItemID, 
	          A.Amount, 
	          A.RequestNum, 
	          A.Quantity,
		  (select Max(isnull(FirstName,'')+' '+isnull(MiddleName,'')+' '+isnull(LastName,''))
			from password..tbpasswordmain
			where employeeid = A.Userid
	                Group by EmployeeID
		  ) as Nurse,
		  A.StationID,
	          A.PatientType,
		  A.Dialysis,
		  A.Transplant,
		  A.UserId as NurseId,
		  isnull(A.Printed,'') as Printed
	
	      From station..tbNurseCommunicationFile A
		  Inner Join Patient_Data..tbOutPatient D on A.IdNum = D.IdNum 
	          Inner Join Patient_Data..tbMaster MasterIn on D.HospNum = MasterIn.HospNum
		  Inner Join Build_File..tbCoLabExam C on A.ItemID = C.LabExamID
		  Left Outer Join Build_File..tbCoLabSpecimen F on A.SpecimenId = F.SpecimenId
	      Where isnull(A.RecordStatus,'') = ''  and A.RevenueID='LB' 
		  and MasterIn.LastName like @LastName + '%'  
		  and IsNumeric(right(A.IdNum,1)) = 0
	    	  and C.LabSectionID = @SectionID
 		  AND  isnull(StationID,'') in ('OPD','OR')
 		  
	      --order by [name],[examination],[Date] desc
		  order by [date] desc
	   END;
     else
	   begin
	       Select
	          IsNull(MasterIn.LastName,'')+', '+IsNull(MasterIn.FirstName,'') + ' ' +IsNull(MasterIn.MiddleName,'') [Name],
	          A.IDNum as [Admission #],
		  C.LabExam as [Examination],
		  A.Stat,
		  A.SpecimenID,
		  F.Specimen,
	          A.RequestDate as [Date], 
	          A.Remarks, 
	          A.DoctorID,
	          A. ItemID, 
	          A.Amount, 
	          A.RequestNum, 
	          '1' as Quantity,
		  (select Max(isnull(FirstName,'') + ' ' + isnull(MiddleName,'')+' '+isnull(LastName,''))
			from password..tbpasswordmain
			where employeeid = A.Userid
	                Group by EmployeeID
		  ) as Nurse,
		  A.StationID,
	          A.PatientType,
		  A.Dialysis,
		  A.Transplant,
		  A.UserId as NurseId,
		  isnull(A.Printed,'') as Printed
	
	     From station..tbNurseCommunicationFile A
		Inner Join Patient_Data..tbPatient D on A.IdNum = D.IdNum 
	        Inner Join Patient_Data..tbMaster MasterIn on D.HospNum = MasterIn.HospNum
		Inner Join Build_File..tbCoLabExam C on A.ItemID = C.LabExamID
		Left Outer Join Build_File..tbCoLabSpecimen F on A.SpecimenId = F.SpecimenId
	     Where isnull(A.RecordStatus,'') = ''  and A.RevenueID='LB' 
		and MasterIn.LastName like @LastName + '%'
		and IsNumeric(right(A.IdNum,1)) = 1 		
				AND  isnull(StationID,'') in ('OPD')
	     Union All
	
	     Select
	          IsNull(MasterIn.LastName,'')+', '+IsNull(MasterIn.FirstName,'') + ' ' +IsNull(MasterIn.MiddleName,'') [Name],
	          A.IDNum as [Admission #],
		  C.LabExam as [Examination],
		  A.Stat,
		  A.SpecimenID,
		  F.Specimen,
	          A.RequestDate as [Date], 
	          A.Remarks, 
	          A.DoctorID,
	          A. ItemID, 
	          A.Amount, 
	          A.RequestNum, 
	          A.Quantity,
		  (select Max(isnull(FirstName,'')+' '+isnull(MiddleName,'')+' '+isnull(LastName,''))
			from password..tbpasswordmain
			where employeeid = A.Userid
	                Group by EmployeeID
		  ) as Nurse,
		  A.StationID,
	          A.PatientType,
		  A.Dialysis,
		  A.Transplant,
		  A.UserId as NurseId,
		  isnull(A.Printed,'') as Printed
	
	      From station..tbNurseCommunicationFile A
		  Inner Join Patient_Data..tbOutPatient D on A.IdNum = D.IdNum 
	          Inner Join Patient_Data..tbMaster MasterIn on D.HospNum = MasterIn.HospNum
		  Inner Join Build_File..tbCoLabExam C on A.ItemID = C.LabExamID
		  Left Outer Join Build_File..tbCoLabSpecimen F on A.SpecimenId = F.SpecimenId
	      Where isnull(A.RecordStatus,'') = ''  and A.RevenueID='LB' 
		  and MasterIn.LastName like @LastName + '%'  
		  and IsNumeric(right(A.IdNum,1)) = 0		
        	  		AND  isnull(StationID,'') in ('OPD','OR')
	      --order by [name],[examination],[Date] desc
		  order by [date] desc
	   END;
    end;

ELSE
  IF (@Mode=2)
    BEGIN
       if  datepart(hour,getdate()) < 18   AND @SectionID <> '00'
	begin
	       Select
	          IsNull(MasterIn.LastName,'')+', '+IsNull(MasterIn.FirstName,'') + ' ' +IsNull(MasterIn.MiddleName,'') [Name],
	          A.IDNum as [Admission #],
		  C.LabExam as [Examination],
		  A.Stat,
		  A.SpecimenID,
		  F.Specimen,
	          A.RequestDate as [Date], 
	          A.Remarks, 
	          A.DoctorID,
	          A. ItemID, 
	          A.Amount, 
	          A.RequestNum, 
	          '1' as Quantity,
		  (select Max(isnull(FirstName,'') + ' ' + isnull(MiddleName,'')+' '+isnull(LastName,''))
			from password..tbpasswordmain
			where employeeid = A.Userid
	                Group by EmployeeID
		  ) as Nurse,
		  A.StationID,
	          A.PatientType,
		  A.Dialysis,
		  A.Transplant,
		  A.UserId as NurseId,
		  isnull(A.Printed,'') as Printed
	
	     From station..tbNurseCommunicationFile A
		Inner Join Patient_Data..tbPatient D on A.IdNum = D.IdNum 
	        Inner Join Patient_Data..tbMaster MasterIn on D.HospNum = MasterIn.HospNum
		Inner Join Build_File..tbCoLabExam C on A.ItemID = C.LabExamID
		Left Outer Join Build_File..tbCoLabSpecimen F on A.SpecimenId = F.SpecimenId
	     Where isnull(A.RecordStatus,'') = ''  and A.RevenueID='LB' 
		and MasterIn.LastName like @LastName + '%' 
		and IsNumeric(right(A.IdNum,1)) = 1 
		and C.LabSectionID = @SectionID
				AND  isnull(StationID,'') in ('OPD')

	     Union All
	
	     Select
	          IsNull(MasterIn.LastName,'')+', '+IsNull(MasterIn.FirstName,'') + ' ' +IsNull(MasterIn.MiddleName,'') [Name],
	          A.IDNum as [Admission #],
		  C.LabExam as [Examination],
		  A.Stat,
		  A.SpecimenID,
		  F.Specimen,
	          A.RequestDate as [Date], 
	          A.Remarks, 
	          A.DoctorID,
	          A. ItemID, 
	          A.Amount, 
	          A.RequestNum, 
	          A.Quantity,
		  (select Max(isnull(FirstName,'')+' '+isnull(MiddleName,'')+' '+isnull(LastName,''))
			from password..tbpasswordmain
			where employeeid = A.Userid
	                Group by EmployeeID
		  ) as Nurse,
		  A.StationID,
	          A.PatientType,
		  A.Dialysis,
		  A.Transplant,
		  A.UserId as NurseId,
		  isnull(A.Printed,'') as Printed
	
	      From station..tbNurseCommunicationFile A
		  Inner Join Patient_Data..tbOutPatient D on A.IdNum = D.IdNum 
	          Inner Join Patient_Data..tbMaster MasterIn on D.HospNum = MasterIn.HospNum
		  Inner Join Build_File..tbCoLabExam C on A.ItemID = C.LabExamID
		  Left Outer Join Build_File..tbCoLabSpecimen F on A.SpecimenId = F.SpecimenId
	      Where isnull(A.RecordStatus,'') = ''  and A.RevenueID='LB' 
		  and MasterIn.LastName like @LastName + '%'  
		  and IsNumeric(right(A.IdNum,1)) = 0 
	 	  and C.LabSectionID = @SectionID	
		  		AND  isnull(StationID,'') in ('OPD','OR')
	      --order by [name],[examination],[Date] desc
		  order by [date] desc
	end;
    else
	begin
	       Select
	          IsNull(MasterIn.LastName,'')+', '+IsNull(MasterIn.FirstName,'') + ' ' +IsNull(MasterIn.MiddleName,'') [Name],
	          A.IDNum as [Admission #],
		  C.LabExam as [Examination],
		  A.Stat,
		  A.SpecimenID,
		  F.Specimen,
	          A.RequestDate as [Date], 
	          A.Remarks, 
	          A.DoctorID,
	          A. ItemID, 
	          A.Amount, 
	          A.RequestNum, 
	          '1' as Quantity,
		  (select Max(isnull(FirstName,'') + ' ' + isnull(MiddleName,'')+' '+isnull(LastName,''))
			from password..tbpasswordmain
			where employeeid = A.Userid
	                Group by EmployeeID
		  ) as Nurse,
		  A.StationID,
	          A.PatientType,
		  A.Dialysis,
		  A.Transplant,
		  A.UserId as NurseId,
		  isnull(A.Printed,'') as Printed
	
	     From station..tbNurseCommunicationFile A
		Inner Join Patient_Data..tbPatient D on A.IdNum = D.IdNum 
	        Inner Join Patient_Data..tbMaster MasterIn on D.HospNum = MasterIn.HospNum
		Inner Join Build_File..tbCoLabExam C on A.ItemID = C.LabExamID
		Left Outer Join Build_File..tbCoLabSpecimen F on A.SpecimenId = F.SpecimenId
	     Where isnull(A.RecordStatus,'') = ''  and A.RevenueID='LB' 
		and MasterIn.LastName like @LastName + '%' 
		and IsNumeric(right(A.IdNum,1)) = 1 
				AND  isnull(StationID,'') in ('OPD')
	
	     Union All
	
	     Select
	          IsNull(MasterIn.LastName,'')+', '+IsNull(MasterIn.FirstName,'') + ' ' +IsNull(MasterIn.MiddleName,'') [Name],
	          A.IDNum as [Admission #],
		  C.LabExam as [Examination],
		  A.Stat,
		  A.SpecimenID,
		  F.Specimen,
	          A.RequestDate as [Date], 
	          A.Remarks, 
	          A.DoctorID,
	          A. ItemID, 
	          A.Amount, 
	          A.RequestNum, 
	          A.Quantity,
		  (select Max(isnull(FirstName,'')+' '+isnull(MiddleName,'')+' '+isnull(LastName,''))
			from password..tbpasswordmain
			where employeeid = A.Userid
	                Group by EmployeeID
		  ) as Nurse,
		  A.StationID,
	          A.PatientType,
		  A.Dialysis,
		  A.Transplant,
		  A.UserId as NurseId,
		  isnull(A.Printed,'') as Printed
	
	      From station..tbNurseCommunicationFile A
		  Inner Join Patient_Data..tbOutPatient D on A.IdNum = D.IdNum 
	          Inner Join Patient_Data..tbMaster MasterIn on D.HospNum = MasterIn.HospNum
		  Inner Join Build_File..tbCoLabExam C on A.ItemID = C.LabExamID
		  Left Outer Join Build_File..tbCoLabSpecimen F on A.SpecimenId = F.SpecimenId
	      Where isnull(A.RecordStatus,'') = ''  and A.RevenueID='LB' 
		  and MasterIn.LastName like @LastName + '%'  
		  and IsNumeric(right(A.IdNum,1)) = 0 
		  	and	 isnull(StationID,'') in ('OPD','OR')
	      --order by [name],[examination],[Date] desc
		  order by [date] desc
	end;

   END