USE [LABORATORY]
GO
/****** Object:  StoredProcedure [dbo].[Lab_CheckCommRequestsOPD]    Script Date: 09/10/2019 4:20:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Lab_CheckCommRequestsOPD]
	@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 isnull(Station.StationID,'') = 'OPD'
				and IsNumeric(right(Station.IdNum,1)) = 0
				AND  isnull(Station.StationID,'') <> ('ER')
			   -- and isnull(Station.StationID,'') in ('OPD','OR')
				
				--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 isnull(A.StationID,'') = 'OPD'
			and IsNumeric(right(A.IdNum,1)) = 0
			and isnull(A.StationID,'') <> ('ER')
			
	  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,'') = 'OPD'
				and IsNumeric(right(Station.IdNum,1)) = 0
			  --  and isnull(Station.StationID,'') in ('OPD','OR')
			) 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,'') = 'OPD'
			and IsNumeric(right(A.IdNum,1)) = 0
			and isnull(A.StationID,'') in ('OPD','OR')
			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,'') = 'OPD'
				--and IsNumeric(right(Station.IdNum,1)) = 0
			    and isnull(Station.StationID,'') in ('OPD','OR')
			) 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,'') = 'OPD'
			and IsNumeric(right(A.IdNum,1)) = 0
			--and isnull(A.StationID,'') in ('OPD','OR')
			and A.ItemID in (Select LabExamId from Build_File..tbCoLabExam 
					 where Status <> 'I' and LabSectionID = @SectionId)
    	   end;

     End;