UFN_GET_EMAILPROCESS_JOBSTATUS

Return

Return Type
table
Parameters

Parameter	Parameter Type	Mode
@STARTDATE	datetime	IN
@ENDDATE	datetime	IN
@EMAILTYPE	int	IN
@RETURNQUEUED	bit	IN
@RETURNPROCESSING	bit	IN
@RETURNCOMPLETED	bit	IN
@RETURNCANCELED	bit	IN
@RETURNDELETED	bit	IN
@RETURNSTOPPEDORPAUSED	bit	IN
@RETURNERROR	bit	IN
@QUEUEDLABEL	nvarchar(100)	IN
@PROCESSINGLABEL	nvarchar(100)	IN
@COMPLETEDLABEL	nvarchar(100)	IN
@CANCELEDLABEL	nvarchar(100)	IN
@DELETEDLABEL	nvarchar(100)	IN
@STOPPEDORPAUSEDLABEL	nvarchar(100)	IN
@ERRORLABEL	nvarchar(100)	IN
Definition

Copy


create function dbo.UFN_GET_EMAILPROCESS_JOBSTATUS
(
@STARTDATE datetime = null
,@ENDDATE datetime = null
,@EMAILTYPE integer = 0  --0: General purpose email jobs  1: Transactional email jobs

,@RETURNQUEUED bit
,@RETURNPROCESSING bit
,@RETURNCOMPLETED bit
,@RETURNCANCELED bit
,@RETURNDELETED bit
,@RETURNSTOPPEDORPAUSED bit
,@RETURNERROR bit
,@QUEUEDLABEL nvarchar(100) = null
,@PROCESSINGLABEL nvarchar(100) = null
,@COMPLETEDLABEL nvarchar(100) = null
,@CANCELEDLABEL nvarchar(100) = null
,@DELETEDLABEL nvarchar(100) = null
,@STOPPEDORPAUSEDLABEL nvarchar(100) = null
,@ERRORLABEL nvarchar(100) = null
)
returns @EMAILJOBSTATUS table
(
      ID int identity primary key
      ,STATUSLABEL nvarchar(100)
      ,NUMOFEMAILJOBS integer
)
as 
begin

      --This is for the edit widget setting.

        if @RETURNQUEUED = 1    
              insert into @EMAILJOBSTATUS(STATUSLABEL, NUMOFEMAILJOBS) values (@QUEUEDLABEL, 0)
        if @RETURNPROCESSING = 1                
              insert into @EMAILJOBSTATUS(STATUSLABEL, NUMOFEMAILJOBS) values (@PROCESSINGLABEL, 0)
      if @RETURNCOMPLETED = 1
              insert into @EMAILJOBSTATUS(STATUSLABEL, NUMOFEMAILJOBS) values (@COMPLETEDLABEL, 0)
        if @RETURNCANCELED = 1        
              insert into @EMAILJOBSTATUS(STATUSLABEL, NUMOFEMAILJOBS) values (@CANCELEDLABEL, 0)
        if @RETURNDELETED = 1        
              insert into @EMAILJOBSTATUS(STATUSLABEL, NUMOFEMAILJOBS) values (@DELETEDLABEL, 0)
        if @RETURNSTOPPEDORPAUSED = 1        
              insert into @EMAILJOBSTATUS(STATUSLABEL, NUMOFEMAILJOBS) values (@STOPPEDORPAUSEDLABEL, 0)
        if @RETURNERROR = 1        
              insert into @EMAILJOBSTATUS(STATUSLABEL, NUMOFEMAILJOBS) values (@ERRORLABEL, 0) 

      if @EMAILTYPE = 1 
      begin
                  update @EMAILJOBSTATUS 
                  set NUMOFEMAILJOBS = isnull((select count(*)
                  from dbo.EMAILSTATUSTRANSACTIONAL 
                  where
                  ((STATUS = 15 and @RETURNQUEUED = 1 and STATUSLABEL = @QUEUEDLABEL)
                              or (STATUS in (1,2,7,10) and @RETURNPROCESSING = 1 and STATUSLABEL = @PROCESSINGLABEL)
                              or (STATUS = 3 and @RETURNCOMPLETED = 1 and STATUSLABEL = @COMPLETEDLABEL)
                              or (STATUS = 5 and @RETURNCANCELED = 1 and STATUSLABEL = @CANCELEDLABEL)
                              or (STATUS = 18 and @RETURNDELETED = 1 and STATUSLABEL = @DELETEDLABEL)
                              or (STATUS in (6,8) and @RETURNSTOPPEDORPAUSED = 1 and STATUSLABEL = @STOPPEDORPAUSEDLABEL)
                              or (STATUS not in (15,1,2,7,10,3,5,18,6,8) and @RETURNERROR = 1 and STATUSLABEL = @ERRORLABEL))
                  and 
                  DATECHANGED >= isnull(@STARTDATE, '1/1/1900') and DATECHANGED < isnull(@ENDDATE, '12/31/2999')
                  group by STATUS),0)
      end
      else  
      begin
                  update @EMAILJOBSTATUS 
                  set NUMOFEMAILJOBS = isnull((select count(*)
                  from dbo.EMAILSTATUSGENERALPURPOSE
                  where
                  ((STATUS = 15 and @RETURNQUEUED = 1 and STATUSLABEL = @QUEUEDLABEL)
                              or (STATUS in (1,2,7,10) and @RETURNPROCESSING = 1 and STATUSLABEL = @PROCESSINGLABEL)
                              or (STATUS = 3 and @RETURNCOMPLETED = 1 and STATUSLABEL = @COMPLETEDLABEL)
                              or (STATUS = 5 and @RETURNCANCELED = 1 and STATUSLABEL = @CANCELEDLABEL)
                              or (STATUS = 18 and @RETURNDELETED = 1 and STATUSLABEL = @DELETEDLABEL)
                              or (STATUS in (6,8) and @RETURNSTOPPEDORPAUSED = 1 and STATUSLABEL = @STOPPEDORPAUSEDLABEL)
                              or (STATUS not in (15,1,2,7,10,3,5,18,6,8) and @RETURNERROR = 1 and STATUSLABEL = @ERRORLABEL))
                  and 
                  DATECHANGED >= isnull(@STARTDATE, '1/1/1900') and DATECHANGED < isnull(@ENDDATE, '12/31/2999')
                  group by STATUS),0)
      end

      return
end