USE [MockingJay]
GO
/****** Object:  StoredProcedure [dbo].[rs_PBB_BonusReport]    Script Date: 1/5/2026 12:44:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[rs_PBB_BonusReport] 
@StartDate_p datetime, @EndDate_p datetime 
as 
begin
declare @StartDate datetime = @StartDate_p, @EndDate datetime = @EndDate_p
--declare @StartDate datetime = '9/11/25', @EndDate datetime = '9/24/25'
set datefirst 1
;
with 
	csat as 
	(
		select cdate,ads.employeeid, agentid, Satisfied, responses,--weekdate, weekname,
			(Satisfied/(1.0*case when responses > 0 then responses else 1 end))*100.0 as csat
		from
		(
			Select cdate, 			
				ea.employeeid, agentid, sum(satisfiedresponse) as Satisfied, sum(CsatResponded) as responses 
			from 
				(				
					select convert(varchar, cast([date] as datetime2), 101) as cdate, Operator_id, call_id, disposition, 
							case when isnumeric(response) > 0 and response >='4' then 1 else 0 end as SatisfiedResponse, 
							case when isnumeric(response) > 0 and response < '4' then 1 else 0 end as Unsatisfiedresponse,  
							response = case when isnumeric(response) > 0 then response else 0 end
							, direction , [state] as CsatState
							,CsatResponded = case when state = 'completed' then 1 else 0 end
					
					from PointBroadBand.dbo.PBB_API_CSAT
					where  cast([date] as datetime2) between @StartDate and dateadd(day,1,@EndDate)
					
				) adsSub					
					 join (select * from nest..EmployeesACDs ea (nolock) where campaignid = 9312998 )ea on  adsSub.operator_id= ea.agentid 
		
				where ea.employeeid <> 0
				group by cdate,--datepart(week,  cdate) , convert(varchar,DATETRUNC(week,  cdate) , 101) ,
						ea.employeeid, agentid
			)ads
		
	),


	taskhrs as 
	(
		select * from 

		(
			select  taskdate				
					 , agent, --sum(Duration) as RosteredDuration, 
				 
					sum(case when TaskName <> 'Log out' then duration else 0 end) as tLoggedTime,
					sum(case when TaskName in('Break', 'Lunch') then duration2 else 0 end) as break_lunch,
					sum(case when TaskName like '%CS Phones' then duration else 0 end) as AvailDuration,
					sum(case when TaskName like 'Handle CS Phones'  or TaskName in ('ACW','Finish Service Order') then duration else 0 end) as HandleDuration
			from 
			(
				select convert(varchar, cast([start time] as datetime2), 101) as taskdate ,[user] as agent,
				TaskName = case when [task name] = 'CS Phones' and identifier like '%available%' then 'Avail CS Phones' 
								when [task name] = 'CS Phones' and identifier not like '%available%' then 'Handle CS Phones' 
								else [Task name] end , 
					sum(datediff(s,cast([start time] as datetime2), cast([end time] as datetime2))) as duration
					,sum(cast([duration (mins)] as int))*60.0 as duration2

				from pointBroadBand.dbo.pbb_taskdata (nolock) 
				where cast([start time] as datetime2) between @Startdate and dateadd (day, 1, @enddate)
			
				group by [user],convert(varchar, cast([start time] as datetime2), 101), 
							case when [task name] = 'CS Phones' and identifier like '%available%' then 'Avail CS Phones' 
							when [task name] = 'CS Phones' and identifier not like '%available%' then 'Handle CS Phones' 
							else [Task name] end
			)x
			group by taskdate,agent
		)taskSum
		join (select * from nest..EmployeesACDs ea (nolock) where campaignid = 9312998 )ea on  tasksum.agent= ea.agentid 
	
	
	),

	Incontacthrs as
	(
	
			SELECT agentid, convert(varchar, callday,101) as icallday			
				,iProdtime = sum(totaltime-unavailableTime) --productive		
			FROM Nest.dbo.ProjectTimeByHalfHour (nolock) h
			WHERE CallDay BETWEEN @StartDate AND dateadd(day, 1,@EndDate) 
				AND (campaignid = 9313190 or teamname like '%Point%band%')		
			GROUP BY agentid,convert(varchar, callday,101)
				,datepart(week,  callday) 
				,convert(varchar,DATETRUNC(week,  callday) , 101) 
	
		
		
	),

	Workact as 
	(
		select employeeid, convert(varchar, cast ([date] as datetime), 101) as wDate,
			[user], 
			(case when isnumeric([Rostered duration (mins)]) < 1 then 1 else cast([Rostered duration (mins)] as float) end )*60.0 as scheduledSec, 
			cast([Actual duration (mins)] as float)*60.0 as WAactualT, 
			cast([total handle time (mins)] as float)*60.0 as WAHandleT
			,case when isnumeric(adherence) > 0 then cast(adherence as float) else 0.0 end as WAadherence			
			,Occupancy as WAoccupancy 
			,(case when isnumeric([Rostered duration (mins)]) < 1 then 1 else cast([Rostered duration (mins)] as float) end *60) * case when isnumeric(adherence) > 0 then cast(adherence as float) else 1 end as SubAdher	
	
		from (select * from PointBroadBand..PBB_WorkActivity wa (nolock)
			where cast(wa.Date as datetime) between @Startdate and @Enddate) wa
		 join (
					select * from nest..EmployeesACDs ea (nolock) 
					where campaignid = 9312998 
				)ea on  wa.[user]= ea.agentid 
	
	)

	select  calendardate, WeekFirst, WeekLast, e.firstname + ' ' + e.lastname as AgentName, 
		--isnull(taskhrs.agent, e.firstname + ' ' + e.lastname ) as AgentRosterName,
		e.employeeid, isnull(tLoggedTime,0.00)-isnull(break_lunch,0.0) + isnull(iProdTime,0.0) as TotalProdTime
		,isnull(tLoggedTime,0.00)-isnull(break_lunch,0.0) as WActExcBreak
		,calendardate as date,		
		taskdate, agent,WAactualT, isnull(tLoggedTime,0.0) as tLogTime,
		isnull(break_lunch,0.0) as break_lunch, 
		isnull(AvailDuration,0.0) as AvailDuration, 
		cdate, 
		isnull(satisfied,0.0) as satisfied, 
		isnull(responses,0.0) as responses, 
		icallday, 
		isnull(iprodtime,0.0) as iprodtime, 
		isnull(Workact.scheduledSec,0.0) as scheduledSec,
		isnull(WAactualT,0.0) as  actualHrs, 
		isnull(WAHandleT,0.0) as HandleT
		,isnull(WAadherence,0.0) as adherence
		,isnull(WAoccupancy ,0.0) as occupancy		
		,case when isnull(Workact.scheduledSec,0.0) = 0 and isnull(WAactualT,0.0) = 0 then 1 else isnull(SubAdher,0.0) end as SubAdher
	from 
		(
			select e.employeeid, e.teamid, e.firstname, e.lastname
			from ravenx..employees e (nolock) 
				left join ravenx..teams t(nolock) on e.teamid = t.teamid 
			where t.teamname like '%point%band%'
		) e 
		cross join (
						select convert(varchar,calendardate ,101) as calendardate
							,WeekFirst = convert(varchar, DATEADD(DAY, 1 - DATEPART(WEEKDAY, calendardate), calendardate), 101 )
							,Weeklast = convert(varchar,DATEADD(DAY, 7 - DATEPART(WEEKDAY, calendardate), calendardate),101)
						from nest..calendar n (nolock)
						where n.calendardate between @startDate and  @Enddate
					)n
				
		left join taskhrs on taskhrs.employeeid = e.employeeID and taskhrs.taskdate = n.CalendarDate
		left join csat on e.employeeid = csat.employeeid and csat.cdate = n.CalendarDate
		left join Incontacthrs on e.employeeid = incontacthrs.agentid and incontacthrs.icallday = n.CalendarDate
		left join Workact on e.employeeid = workact.employeeID and workAct.wDate = n.CalendarDate
	where ( cdate is not null or taskdate is not null or icallday is not null or Workact.wdate is not null)
--	and  isnull(WAactualT,0.0)-isnull(break_lunch,0.0) + isnull(iProdTime,0.0) > 0
  order by agentname, calendardate--, isnull(callday, isnull(taskdate,cdate))

end
GO
