USE [LABORATORY]
GO
/****** Object:  StoredProcedure [dbo].[Lab_CheckCommRequests]    Script Date: 09/10/2019 4:16:39 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Lab_CheckCommRequests]
	@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(*) 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 not Station.SectionId in('BBK', 'BDR')
			) 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 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(*) 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')
			) 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 A.ItemID in (Select LabExamId from Build_File..tbCoLabExam where Status <> 'I')
	   end;
	else
    	   begin
		Select count(*) 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')
			) 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 A.ItemID in (Select LabExamId from Build_File..tbCoLabExam 
					 where Status <> 'I' and LabSectionID = @SectionId)
    	   end;

     End;