USE [LABORATORY]
GO
/****** Object:  StoredProcedure [dbo].[Lab_CheckCommRequestsIPD]    Script Date: 7/8/2020 1:53:40 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
	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 not BuildFile.LabGrp in ('BBK')
				--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 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(*) 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')
				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 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;
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')
				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 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(*) 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 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(A.StationID,'') NOT IN ('OPD','ER')
			and A.ItemID in (Select LabExamId from Build_File..tbCoLabExam 
					 where Status <> 'I' and LabSectionID = @SectionId)
    	   end;

     End;