USE [LABORATORY]
GO
/****** Object:  StoredProcedure [dbo].[LAB_Get_NursingRequestsIPD]    Script Date: 11/23/2017 14:33:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[LAB_Get_NursingRequestsIPD]
  @Mode int,
  @LastName varchar(30),
  @UserName varchar(50) = ''

AS

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


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(A.IdNum) = 1 
			AND isnull(A.StationID,'') NOT IN ('OPD','ER')
	
	   --  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(A.IdNum) = 0
 			--  AND isnull(A.StationID,'') NOT IN ('OPD','ER', 'KS','OR')
	   --   order by [name],[examination],[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(A.IdNum) = 1 		
			AND isnull(A.StationID,'') NOT IN ('OPD','ER')
			
	   --  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(A.IdNum) = 0		
    --    	  AND isnull(A.StationID,'') NOT IN ('OPD','ER', 'KS','OR')	
	   --   order by [name],[examination],[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(A.IdNum) = 1 
			AND isnull(A.StationID,'') NOT IN ('OPD','ER')

	   --  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(A.IdNum) = 0 
			 -- AND isnull(A.StationID,'') NOT IN ('OPD','ER','KS','OR')
	   --   order by [name],[examination],[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(A.IdNum) = 1 
			AND isnull(A.StationID,'') NOT IN ('OPD','ER')
	
	   --  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(A.IdNum) = 0 
			 -- AND isnull(A.StationID,'') NOT IN ('OPD','ER', 'KS','OR')
	   --   order by [name],[examination],[Date] desc
	end;

   END