USE [Laboratory]
GO
/****** Object:  StoredProcedure [dbo].[Lab_GetNurseRequest]    Script Date: 9/24/2020 12:01:46 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Lab_GetNurseRequest] 
	@IDNum varchar(10),
	@Revenueid varchar(4),
	@StationId varchar(4)
AS
Declare @SpecialHMO as varchar(20)
Declare @SpecialHMORate as varchar(20)

set @SpecialHMO = Radiology.dbo.fn_Radio_CheckIfSpecialHMO(@IDNum)

IF @RevenueId = 'LB'
	BEGIN
		IF isnumeric(@IDNum) = 1
			Begin
				Select Nurse.ItemID,
						tbExam.LabExam,
						Nurse.Quantity,
						CASE WHEN isnull(C.InterNational,'') = 'Y' THEN
								Case When Room.RoomClassID = 'A' then
		     					      cast(tbExam.RateA  + (tbExam.RateA * 0.10) as money)
             				     When Room.RoomClassId = 'B' then
		     					      cast(tbExam.RateB  + (tbExam.RateB * 0.10) as money)
	     						 When Room.RoomClassId = 'C' then	  
							          cast(tbExam.RateC  + (tbExam.RateC * 0.10) as money)
	     						 When Room.RoomClassId = 'D' then
							          cast(tbExam.RateD  + (tbExam.RateD * 0.10) as money)
							     When Room.RoomClassId = 'E' then
							          cast(tbExam.RateE  + (tbExam.RateE * 0.10) as money)
							     When Room.RoomClassId = 'F' then
							          cast(tbExam.RateF  + (tbExam.RateF * 0.10) as money)
							     When Room.RoomClassId = 'G' then
							          cast(tbExam.RateG  + (tbExam.RateG * 0.10) as money)
							     When Room.RoomClassId = 'H' then
							          cast(tbExam.RateH  + (tbExam.RateH * 0.10) as money)
						     Else 
	    						  cast(tbExam.RateA  + (tbExam.RateA * 0.10) as money)
							 End
						Else
							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)
							     When Room.RoomClassId = 'H' then
							          cast(tbExam.RateF as money)
						     Else 
	    						 cast (tbExam.RateA as money)
							 End
						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,
						isnull(tbExam.SubClass,'') as ExamSClass
					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
				        Left Outer Join Build_File..tbCoCompany C  on C.AccountNum = tbPatient.AccountNumII
					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 isnull(Nurse.StationID,'') = @StationId;
			End
		ELSE
			Begin
				Select Nurse.ItemID,
						tbExam.LabExam,
						Nurse.Quantity,
						
						/*** SCH Kidney Outptx. exemption to surcharge but RateA will be applied instead***/
						
						CASE  WHEN isnull(C.InterNational,'') = 'Y' THEN
							cast(tbExam.RateA  + (tbExam.RateA * 0.10) as money)
						ELSE
							Cast(tbExam.RateA 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, isnull(tbExam.SubClass,'') as ExamSClass
				   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
						Left Outer Join Patient_Data..tbOutPatient OP on OP.IDNum = @IdNum
						Left Outer Join Build_File..tbCoCompany C on C.AccountNum = OP.AccountNum
				   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 isnull(Nurse.StationID,'') = @StationId;
			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)
								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 Nurse.StationID in ('ICU','ACSU') then
									cast (tbExam.IcuRate 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,
						IsNull(Nurse.AMPickup,'0') as AMPickup,
						'I' as PatientType,
						tbPatient.RoomId as RoomId, isnull(tbExam.SubClass,'') as ExamSClass
				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(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,
						IsNull(Nurse.AMPickup,'0') as AMPickup,
						'O' as PatientType,
						Nurse.StationID as RoomId, isnull(tbExam.SubClass,'') as ExamSClass
			   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(Nurse.StationID,'') = @StationId;

			End
       
	 END
ELSE
    BEGIN
		IF isnumeric(@IDNum) = 1
  			Begin
     			 Select Nurse.ItemID,
						tbExam.LabExam,
						Nurse.Quantity,
						Case 	
								When Nurse.StationID in ('ICU','ACSU') then
									cast (tbExam.IcuRate as money)						
								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, isnull(tbExam.SubClass,'') as ExamSClass
				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 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, isnull(tbExam.SubClass,'') as ExamSClass
				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 Nurse.StationId = @StationId
			End
       
    END