USE [LABORATORY]
GO
/****** Object:  StoredProcedure [dbo].[Lab_GetNurseRequest]    Script Date: 11/23/2017 13:03:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/** Script Date:  Dec. 4, 2001   JMR **/

ALTER PROCEDURE [dbo].[Lab_GetNurseRequest] 
	@IDNum varchar(10),
	@Revenueid varchar(4),
	@StationId varchar(4)
AS

IF @RevenueId = 'LB'
  BEGIN
     IF isnumeric(@IDNum) = 1
  	Begin
     	     Select Nurse.ItemID,
        	tbExam.LabExam,
        	Nurse.Quantity,
       		 Case  When tbPatient.HospNum = tbpatient.AccountNum then
      
       						Case When Room.RoomClassID = 'A' then
		     					      cast(tbExam.RateA as money)
             				     When Room.RoomClassId = 'B' then
		     					      cast(tbExam.RateB as money)
	     						 When Room.RoomClassId = 'C' then	  
							          cast(tbExam.RateC as money)
	     						 When Room.RoomClassId = 'D' then
							          cast(tbExam.RateD as money)
							     When Room.RoomClassId = 'E' then
							          cast(tbExam.RateE as money)
							     When Room.RoomClassId = 'F' then
							          cast(tbExam.RateF as money)
							     When Room.RoomClassId = 'G' then
							          cast(tbExam.RateG as money)
						     Else 
	    						 cast (tbExam.RateB as money)
	    				     End     		
       		        		 
       	       	  When (select Isnull(Class,'') as Class from Build_file..tbcocompany where Accountnum=tbpatient.Accountnum) not in ('I','H','P') then
      
       						Case When Room.RoomClassID = 'A' then
		     					      cast(tbExam.RateA as money)
             				     When Room.RoomClassId = 'B' then
		     					      cast(tbExam.RateB as money)
	     						 When Room.RoomClassId = 'C' then	  
							          cast(tbExam.RateC as money)
	     						 When Room.RoomClassId = 'D' then
							          cast(tbExam.RateD as money)
							     When Room.RoomClassId = 'E' then
							          cast(tbExam.RateE as money)
							     When Room.RoomClassId = 'F' then
							          cast(tbExam.RateF as money)
							     When Room.RoomClassId = 'G' then
							          cast(tbExam.RateG as money)
						     Else 
	    						 cast (tbExam.RateB as money)
	    				     End
	    		
	         ELSE
	    		    
	    						 cast (tbExam.rateH as money)
	    End as [Amount],
		Nurse.RequestNum,
		Nurse.Remarks,
		Nurse.Stat,
		Nurse.SpecimenId,
		tbSpecimen.Specimen,
		Nurse.Dialysis,
		Nurse.Transplant,
		tbCoRoom.RoomDescription as RoomDescription,
		tbExam.LabSectionId,
		IsNull(Nurse.AMPickup,'0') as AMPickup,
		'I' as PatientType,
		tbPatient.RoomId as RoomId
     	   From Station..tbNurseCommunicationFile Nurse
       	     	Left Outer Join Patient_Data..tbPatient tbPatient on tbPatient.IdNum = Nurse.IdNum
       	     	Left Outer Join Build_File..tbCoRoom Room On Room.RoomID = tbPatient.RoomID 
       	     	Left Outer Join Build_File..tbCoLabExam tbExam On Nurse.ItemID = tbExam.LabExamID 
	     	Left Outer Join Build_File..tbCoLabSpecimen tbSpecimen on Nurse.SpecimenId = tbSpecimen.SpecimenID
	     	Left Outer Join Build_File..tbCoRoom tbCoRoom on tbPatient.RoomId = tbCoRoom.RoomID
       	   Where Nurse.RevenueID = 'LB' and Nurse.IDNum = @IdNum and IsNull(Nurse.RecordStatus,'') = ''  	
		and Nurse.ItemId in (Select LabExamId from Build_File..tbCoLabExam)	
		And not IsNull(tbExam.LabGrp,'') in ('BBK');
		--and not isnull(tbExam.LabSectionid,'') in ('BBK', 'BDR')
	     	--and IsNumeric(ItemID) = 1 and ((Nurse.Stat = 'N' and Nurse.SpecimenId = '4') or tbExam.LabPrintOut = 'Y')
        End

     ELSE

  	Begin
     	    Select Nurse.ItemID,
        	tbExam.LabExam,
        	Nurse.Quantity,
       		Case  When tbPatient.HospNum = tbpatient.AccountNum then
       		       cast (tbExam.RateA as money) 
       		      When (select Isnull(Class,'') as Class from Build_file..tbcocompany where Accountnum=tbpatient.Accountnum) not in ('I','H','P') then
				   cast (tbExam.RateA as money) 
				  else
				   cast (tbExam.RateH as money) 
				  End as [Amount],
		Nurse.RequestNum,
  		Nurse.Remarks,
		Nurse.Stat,
		Nurse.SpecimenId,
		tbSpecimen.Specimen,
		Nurse.Dialysis,
		Nurse.Transplant,
		'' as RoomDescription,
		tbExam.LabSectionId,
		IsNull(Nurse.AMPickup,'0') as AMPickup,
		'O' as PatientType,
		Nurse.StationID as RoomId
        	
     	   From Station..tbNurseCommunicationFile Nurse
     	   	Left Outer Join Patient_Data..tboutPatient tbPatient on tbPatient.IdNum = Nurse.IdNum
        	Left Outer Join Build_File..tbCoLabExam tbExam On Nurse.ItemID = tbExam.LabExamID 
		Left Outer Join Build_File..tbCoLabSpecimen tbSpecimen on Nurse.SpecimenId = tbSpecimen.SpecimenID
           Where Nurse.RevenueID = 'LB' and Nurse.IDNum = @IdNum and IsNull(Nurse.RecordStatus,'') = ''  	
		and Nurse.ItemId in (Select LabExamId from Build_File..tbCoLabExam)	
		And not IsNull(tbExam.LabGrp,'') in ('BBK');
		--and not isnull(tbExam.LabSectionid,'') in ('BBK', 'BDR')
		--and IsNumeric(ItemID) = 1 and ((Nurse.Stat = 'N' and Nurse.SpecimenId = '4') or tbExam.LabPrintOut = 'Y')
       End
    END
ELSE IF @RevenueId = 'BBK'
  BEGIN
     IF isnumeric(@IDNum) = 1
  	Begin
     	     Select Nurse.ItemID,
        	tbExam.LabExam,
        	Nurse.Quantity,
       		Case When Room.RoomClassID = 'A' then
		     	cast(tbExam.RateA as money)  * Nurse.Quantity 
             	     When Room.RoomClassId = 'B' then
		     	cast(tbExam.RateB as money) * Nurse.Quantity 
	     	     When Room.RoomClassId = 'C' then	  
			cast(tbExam.RateC as money) * Nurse.Quantity 
	     	     When Room.RoomClassId = 'D' then
			cast(tbExam.RateD as money) * Nurse.Quantity 
		Else 
	    	     cast (tbExam.RateE as money) * Nurse.Quantity 
		End as [Amount],
		Nurse.RequestNum,
		Nurse.Remarks,
		Nurse.Stat,
		Nurse.SpecimenId,
		tbSpecimen.Specimen,
		Nurse.Dialysis,
		Nurse.Transplant,
		tbCoRoom.RoomDescription as RoomDescription,
		tbExam.LabSectionId,
		IsNull(Nurse.AMPickup,'0') as AMPickup,
		'I' as PatientType,
		tbPatient.RoomId as RoomId
        	
     	   From Station..tbNurseCommunicationFile Nurse
       	     	Left Outer Join Patient_Data..tbPatient tbPatient on tbPatient.IdNum = Nurse.IdNum
       	     	Left Outer Join Build_File..tbCoRoom Room On Room.RoomID = tbPatient.RoomID 
       	     	Left Outer Join Build_File..tbCoLabExam tbExam On Nurse.ItemID = tbExam.LabExamID 
	     	Left Outer Join Build_File..tbCoLabSpecimen tbSpecimen on Nurse.SpecimenId = tbSpecimen.SpecimenID
	     	Left Outer Join Build_File..tbCoRoom tbCoRoom on tbPatient.RoomId = tbCoRoom.RoomID
       	   Where Nurse.RevenueID = 'LB' and Nurse.IDNum = @IdNum and IsNull(Nurse.RecordStatus,'') = ''  	
		and Nurse.ItemId in (Select LabExamId from Build_File..tbCoLabExam)	
		And IsNull(tbExam.LabGrp,'') in ('BBK');
		--and isnull(tbExam.LabSectionid,'') in ('BBK', 'BDR')
	     	--and IsNumeric(ItemID) = 1 and ((Nurse.Stat = 'N' and Nurse.SpecimenId = '4') or tbExam.LabPrintOut = 'Y')
        End

     ELSE

  	Begin
     	    Select Nurse.ItemID,
        	tbExam.LabExam,
        	Nurse.Quantity,
       		Cast(tbExam.RateA as Money) as [Amount],
		Nurse.RequestNum,
  		Nurse.Remarks,
		Nurse.Stat,
		Nurse.SpecimenId,
		tbSpecimen.Specimen,
		Nurse.Dialysis,
		Nurse.Transplant,
		'' as RoomDescription,
		tbExam.LabSectionId,
		IsNull(Nurse.AMPickup,'0') as AMPickup,
		'O' as PatientType,
		Nurse.StationID as RoomId
        	
     	   From Station..tbNurseCommunicationFile Nurse
        	Left Outer Join Build_File..tbCoLabExam tbExam On Nurse.ItemID = tbExam.LabExamID 
		Left Outer Join Build_File..tbCoLabSpecimen tbSpecimen on Nurse.SpecimenId = tbSpecimen.SpecimenID
           Where Nurse.RevenueID = 'LB' and Nurse.IDNum = @IdNum and IsNull(Nurse.RecordStatus,'') = ''  	
		and Nurse.ItemId in (Select LabExamId from Build_File..tbCoLabExam)	
		And IsNull(tbExam.LabGrp,'') in ('BBK');
		--and isnull(tbExam.LabSectionid,'') in ('BBK', 'BDR')
		--and IsNumeric(ItemID) = 1 and ((Nurse.Stat = 'N' and Nurse.SpecimenId = '4') or tbExam.LabPrintOut = 'Y')
       End
    END
ELSE
    BEGIN
	IF isnumeric(@IDNum) = 1
  	Begin
     	     Select Nurse.ItemID,
        	tbExam.LabExam,
        	Nurse.Quantity,
       		Case When Room.RoomClassID = 'A' then
		     	cast(tbExam.RateA as money)
             	     When Room.RoomClassId = 'B' then
		     	cast(tbExam.RateB as money)
	     	     When Room.RoomClassId = 'C' then	  
			cast(tbExam.RateC as money)
	     	     When Room.RoomClassId = 'D' then
			cast(tbExam.RateD as money)
		Else 
	    	     cast (tbExam.RateE as money)
		End as [Amount],
		Nurse.RequestNum,
		Nurse.Remarks,
		Nurse.Stat,
		Nurse.SpecimenId,
		tbSpecimen.Specimen,
		Nurse.Dialysis,
		Nurse.Transplant,
		tbCoRoom.RoomDescription as RoomDescription,
		tbExam.LabSectionId,
		tbPatient.RoomId as RoomId
        	
     	   From Station..tbNurseCommunicationFile Nurse
       	     	Left Outer Join Patient_Data..tbPatient tbPatient on tbPatient.IdNum = Nurse.IdNum
       	     	Left Outer Join Build_File..tbCoRoom Room On Room.RoomID = tbPatient.RoomID 
       	     	Left Outer Join Build_File..tbCoLabExam tbExam On Nurse.ItemID = tbExam.LabExamID 
	     	Left Outer Join Build_File..tbCoLabSpecimen tbSpecimen on Nurse.SpecimenId = tbSpecimen.SpecimenID
	     	Left Outer Join Build_File..tbCoRoom tbCoRoom on tbPatient.RoomId = tbCoRoom.RoomID
       	   Where Nurse.RevenueID = 'LB' and Nurse.IDNum = @IdNum and IsNull(Nurse.RecordStatus,'') = ''  	
	     	--and (Nurse.Stat = 'Y' or (Nurse.Stat = 'N' and Nurse.SpecimenId <> '4') or tbExam.LabPrintOut <> 'Y')
		and Nurse.ItemId in (Select LabExamId from Build_File..tbCoLabExam)
		and Nurse.StationId = @StationId
        End

     ELSE

  	Begin
     	    Select Nurse.ItemID,
        	tbExam.LabExam,
        	Nurse.Quantity,
       		Cast(tbExam.RateA as Money) as [Amount],
		Nurse.RequestNum,
  		Nurse.Remarks,
		Nurse.Stat,
		Nurse.SpecimenId,
		tbSpecimen.Specimen,
		Nurse.Dialysis,
		Nurse.Transplant,
		'' as RoomDescription,
		tbExam.LabSectionId,
		Nurse.StationID as RoomId
        	
     	   From Station..tbNurseCommunicationFile Nurse
        	Left Outer Join Build_File..tbCoLabExam tbExam On Nurse.ItemID = tbExam.LabExamID 
		Left Outer Join Build_File..tbCoLabSpecimen tbSpecimen on Nurse.SpecimenId = tbSpecimen.SpecimenID
           Where Nurse.RevenueID = 'LB' and Nurse.IDNum = @IdNum and IsNull(Nurse.RecordStatus,'') = ''  	
		--and (Nurse.Stat = 'Y' or (Nurse.Stat = 'N' and Nurse.SpecimenId <> '4') or tbExam.LabPrintOut <> 'Y')
		and Nurse.ItemId in (Select LabExamId from Build_File..tbCoLabExam)
		and Nurse.StationId = @StationId
       End
    END