USE [LABORATORY]
GO
/****** Object:  StoredProcedure [dbo].[Lab_CheckCommRequestsIPD]    Script Date: 09/10/2019 4:20:13 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[Lab_CheckCommRequestsIPD]
	@RevenueId varchar(4),
	@StationId varchar(4),
	@UserName varchar(50) = ''
AS

declare @SectionID as varchar(2)

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

If @RevenueId = 'LB'
    
	  begin
		Select count(Idnum) TotalRequests,
			(select count(Station.Stat) 
				from STATION..tbNurseCommunicationFile Station
				left outer join  Build_File..tbCoLabExam BuildFile on Station.ItemId = BuildFile.LabExamID
				Where IsNull(Station.RecordStatus,'') = '' and Station.RevenueId = 'LB' and Station.Stat = 'Y'
				and Station.ItemID in (Select LabExamId from Build_File..tbCoLabExam where Status <> 'I')
				--and not BuildFile.LabGrp in ('BBK')
				and IsNumeric(right(Station.IdNum,1)) = 1
				--and not Station.SectionId in('BBK', 'BDR')
				--and isnull(Station.StationID,'')  not in ('OPD','ER')
			) TotalStat
		From STATION..tbNurseCommunicationFile A 
			Left Outer Join Build_File..tbCoLabExam B on A.ItemId = B.LabExamID
		Where IsNull(A.RecordStatus,'') = '' and A.RevenueId = 'LB' 
		--	and not ISNULL(B.LabGrp,'') in('BBK')
			and IsNumeric(right(A.IdNum,1)) = 1
			--and isnull(A.StationID,'') NOT IN ('OPD','ER')
			--and A.ItemID in (Select LabExamId from Build_File..tbCoLabExam where Status <> 'I'
			--		 and LabSectionID = @SectionId)
	  end;

Else 	
     Begin
	if  datepart(hour,getdate()) > 18 or @SectionID = '00'
	    begin
		Select count(Idnum) TotalRequests,
			(select count(Station.Stat) 
				from STATION..tbNurseCommunicationFile Station
				left outer join  Build_File..tbCoLabExam BuildFile on Station.ItemId = BuildFile.LabExamID
				Where IsNull(Station.RecordStatus,'') = '' and Station.RevenueId = 'LB' and Station.Stat = 'Y'
				and Station.ItemID in (Select LabExamId from Build_File..tbCoLabExam where Status <> 'I')
				and BuildFile.LabGrp in('BBK')
				and IsNumeric(right(Station.IdNum,1)) = 1
				--and isnull(Station.StationID,'')  not in ('OPD','ER')
			) TotalStat
		From STATION..tbNurseCommunicationFile A 
			Left Outer Join Build_File..tbCoLabExam B on A.ItemId = B.LabExamID
		Where IsNull(A.RecordStatus,'') = '' and A.RevenueId = 'LB' 
			and ISNULL(B.LabGrp,'') in('BBK')
			and IsNumeric(right(A.IdNum,1)) = 1
			--and isnull(A.StationID,'') NOT IN ('OPD','ER')
			and A.ItemID in (Select LabExamId from Build_File..tbCoLabExam where Status <> 'I')
	   end;
	else
    	   begin
		Select count(IDNUM) TotalRequests,
			(select count(Station.Stat) 
				from STATION..tbNurseCommunicationFile Station
				left outer join  Build_File..tbCoLabExam BuildFile on Station.ItemId = BuildFile.LabExamID
				Where IsNull(Station.RecordStatus,'') = '' and Station.RevenueId = 'LB' and Station.Stat = 'Y'
				and Station.ItemID in (Select LabExamId from Build_File..tbCoLabExam where Status <> 'I')
				and IsNumeric(right(STATION.IdNum,1)) = 1
				and BuildFile.LabGrp in('BBK')
				--and isnull(Station.StationID,'')  not in ('OPD','ER')
			) TotalStat
		From STATION..tbNurseCommunicationFile A 
			Left Outer Join Build_File..tbCoLabExam B on A.ItemId = B.LabExamID
		Where IsNull(A.RecordStatus,'') = '' and A.RevenueId = 'LB' 
		     and IsNumeric(right(A.IdNum,1)) = 1		
			--and isnull(A.StationID,'') NOT IN ('OPD','ER')
			and A.ItemID in (Select LabExamId from Build_File..tbCoLabExam 
					 where Status <> 'I' and LabSectionID = @SectionId)
    	   end;

     End;