USP_WIDGETDASHBOARD_EMAILJOBSPROCESSED

create procedure dbo.USP_WIDGETDASHBOARD_EMAILJOBSPROCESSED
(
@SPECIFICDATEPARAMETER date = null
,@COMPLETEDLABELPARAMETER nvarchar(100) = null
,@FILTERMODEPARAMETER tinyint = 0  --0: Today mode ,1: Specific day mode, 2: Week/Month/Year

,@SELECTEDWEEKPARAMETER tinyint = 0 --0:All weeks of selected month, 1:First week, 2:Second week, 3:Third week, 4:Fourth week

,@SELECTEDMONTHPARAMETER integer = 0 --00: All months of selected year, 01: Jan, 02: Feb ....12: Dec

,@SELECTEDYEARPARAMETER integer = 2011 --Year drop down

,@ISINCLUDEGENERALPURPOSEEMAILJOBSPARAMETER bit = 0
,@ISINCLUDETRANSACTIONALEMAILJOBSPARAMETER bit = 0
)
as
    -- do work

  set nocount on;

declare @ALLEMAILJOBSTEMPTABLE as TABLE (ID int identity primary key, DATECHANGED datetime, TIMECHANGED nvarchar(25), NUMOFTRANSACTIONALEMAILJOBS integer, NUMOFGENERALPURPOSEEMAILJOBS integer)
declare @TRANSACTIONALNUMOFEMAILJOBS integer = 0
,@GENERALPURPOSENUMOFEMAILJOBS integer = 0

declare @TEMPNAME nvarchar(25) = ''

declare @STARTDATE datetime = null
,@ENDDATE datetime = null
,@TEMPDATE datetime = null

set @SELECTEDWEEKPARAMETER = 0  --Reserve for the next phase


if @FILTERMODEPARAMETER = 2
begin
    declare @FIRSTDAYOFMONTH datetime = null
    ,@LASTDAYOFMONTH datetime = null
    ,@TOTALDAYOFMONTH integer = 0
    ,@COUNTER integer = 0

    if @SELECTEDMONTHPARAMETER = 0 and @SELECTEDWEEKPARAMETER = 0 --Yearly report

        begin
            --Yearly report will show total of 12 month period

            set @COUNTER = 1
            while (@COUNTER <= 12)
            begin
                set @STARTDATE = dateadd(m,@COUNTER-1,dateadd(yyyy,@SELECTEDYEARPARAMETER-1900,0))
                set @ENDDATE =  dateadd(dd,-1,dateadd(m,1,@STARTDATE))

                set @TEMPNAME = datename(month,@STARTDATE)

                ----Transactional and General purpose email jobs - StatusCode = 2: Completed jobs

                select @TRANSACTIONALNUMOFEMAILJOBS=NUMOFEMAILJOBS from dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STARTDATE,@ENDDATE,1,0,0,1,0,0,0,0,null,null,@COMPLETEDLABELPARAMETER,null,null,null,null)
                select @GENERALPURPOSENUMOFEMAILJOBS=NUMOFEMAILJOBS from dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STARTDATE,@ENDDATE,0,0,0,1,0,0,0,0,null,null,@COMPLETEDLABELPARAMETER,null,null,null,null)

                INSERT INTO @ALLEMAILJOBSTEMPTABLE(DATECHANGED, TIMECHANGED,NUMOFTRANSACTIONALEMAILJOBS, NUMOFGENERALPURPOSEEMAILJOBS)
                VALUES(@ENDDATE,@TEMPNAME,@TRANSACTIONALNUMOFEMAILJOBS,@GENERALPURPOSENUMOFEMAILJOBS)
                --VALUES(@STARTDATE,convert(nvarchar,@ENDDATE),@TRANSACTIONALNUMOFEMAILJOBS,@GENERALPURPOSENUMOFEMAILJOBS)


                set @COUNTER = @COUNTER + 1
            end

        end
    else if @SELECTEDWEEKPARAMETER = 0 --Monthly report

        begin
            set @FIRSTDAYOFMONTH = dateadd(m,@SELECTEDMONTHPARAMETER-1,dateadd(yyyy,@SELECTEDYEARPARAMETER-1900,0))
            set @LASTDAYOFMONTH =  dateadd(dd,-1,dateadd(m,1,@FIRSTDAYOFMONTH))

            set @TOTALDAYOFMONTH = datediff(D,@FIRSTDAYOFMONTH,@LASTDAYOFMONTH)

            set @STARTDATE = @FIRSTDAYOFMONTH
            while (@COUNTER <= @TOTALDAYOFMONTH)
            begin
                set @ENDDATE = dateadd(dd,@COUNTER,@FIRSTDAYOFMONTH + 1)
                set @TEMPNAME = @COUNTER+1                

                ----Transactional and General purpose email jobs - StatusCode = 2: Completed jobs

                select @TRANSACTIONALNUMOFEMAILJOBS=NUMOFEMAILJOBS from dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STARTDATE,@ENDDATE,1,0,0,1,0,0,0,0,null,null,@COMPLETEDLABELPARAMETER,null,null,null,null)
                select @GENERALPURPOSENUMOFEMAILJOBS=NUMOFEMAILJOBS from dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STARTDATE,@ENDDATE,0,0,0,1,0,0,0,0,null,null,@COMPLETEDLABELPARAMETER,null,null,null,null)

                INSERT INTO @ALLEMAILJOBSTEMPTABLE(DATECHANGED, TIMECHANGED,NUMOFTRANSACTIONALEMAILJOBS, NUMOFGENERALPURPOSEEMAILJOBS)
                VALUES(@ENDDATE,@TEMPNAME,@TRANSACTIONALNUMOFEMAILJOBS,@GENERALPURPOSENUMOFEMAILJOBS)
                --VALUES(@STARTDATE,convert(nvarchar,@ENDDATE),@TRANSACTIONALNUMOFEMAILJOBS,@GENERALPURPOSENUMOFEMAILJOBS)


                set @STARTDATE = @ENDDATE
                set @COUNTER = @COUNTER + 1
            end
        end

    select 
    DATECHANGED
    ,TIMECHANGED
    ,case when @ISINCLUDETRANSACTIONALEMAILJOBSPARAMETER = 0 then null else NUMOFTRANSACTIONALEMAILJOBS end as NUMOFTRANSACTIONALEMAILJOBS
    ,case when @ISINCLUDEGENERALPURPOSEEMAILJOBSPARAMETER = 0 then null else NUMOFGENERALPURPOSEEMAILJOBS end as NUMOFGENERALPURPOSEEMAILJOBS
    from @ALLEMAILJOBSTEMPTABLE  

end

else  --@FILTERMODEPARAMETER = 0 OR @FILTERMODEPARAMETER = 1


begin
      declare @TODAY date = getdate()
      declare @STARTTIME integer = 0
      ,@ENDTIME integer = 0
      ,@TIMECOUNTER integer = 0     

      while @TIMECOUNTER < 24
      begin                       
                if @TIMECOUNTER = 0
                    set @ENDTIME = @TIMECOUNTER + 2
                else
                begin
                    set @STARTTIME = @ENDTIME
                    set @ENDTIME = @ENDTIME + 2
                end                                        

                if @FILTERMODEPARAMETER = 0
                begin
                    --Reset time to 0 hour for today date

                    set @TEMPDATE = dateadd(hh,0,cast(@TODAY as datetime));
                end
                else if @FILTERMODEPARAMETER = 1
                begin
                    if @SPECIFICDATEPARAMETER is null 
                        set @TEMPDATE = dateadd(hh,0,cast(@TODAY as datetime));
                    else --Reset time to 0 hour for specific date        

                        set @TEMPDATE = dateadd(hh,0,cast(@SPECIFICDATEPARAMETER as datetime))
                end

                  set @STARTDATE = dateadd(hh,@STARTTIME,@TEMPDATE)
                set @ENDDATE = dateadd(hh,@ENDTIME,@TEMPDATE)

                declare @STARTHOURPART integer = datepart(hh,@STARTDATE);
                declare @ENDHOURPART integer = datepart(hh,@ENDDATE);

                -- modify to 12 hour format

                declare @ENDHOURSUFFIX nvarchar(2) = case when @ENDHOURPART >= 12 then 'PM' else 'AM' end;

                set @STARTHOURPART = case when @STARTHOURPART = 0 then 12
                                          when @STARTHOURPART > 12 then @STARTHOURPART - 12
                                          else @STARTHOURPART
                                     end;
                set @ENDHOURPART = case when @ENDHOURPART = 0 then 12
                                        when @ENDHOURPART > 12 then @ENDHOURPART - 12
                                        else @ENDHOURPART
                                   end;

                set @TEMPNAME = cast(@STARTHOURPART as nvarchar(2)) + ' - ' + cast(@ENDHOURPART as nvarchar(2)) + @ENDHOURSUFFIX;

                ----Transactional and General purpose email jobs - StatusCode = 2: Completed jobs

                  select @TRANSACTIONALNUMOFEMAILJOBS=NUMOFEMAILJOBS from dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STARTDATE,@ENDDATE,1,0,0,1,0,0,0,0,null,null,@COMPLETEDLABELPARAMETER,null,null,null,null)
                  select @GENERALPURPOSENUMOFEMAILJOBS=NUMOFEMAILJOBS from dbo.UFN_GET_EMAILPROCESS_JOBSTATUS(@STARTDATE,@ENDDATE,0,0,0,1,0,0,0,0,null,null,@COMPLETEDLABELPARAMETER,null,null,null,null)

                INSERT INTO @ALLEMAILJOBSTEMPTABLE(DATECHANGED, TIMECHANGED,NUMOFTRANSACTIONALEMAILJOBS, NUMOFGENERALPURPOSEEMAILJOBS)
                VALUES(@STARTDATE,@TEMPNAME,@TRANSACTIONALNUMOFEMAILJOBS,@GENERALPURPOSENUMOFEMAILJOBS)

                set @TIMECOUNTER = @TIMECOUNTER + 2
      end

select 
DATECHANGED
,TIMECHANGED
,case when @ISINCLUDETRANSACTIONALEMAILJOBSPARAMETER = 0 then null else NUMOFTRANSACTIONALEMAILJOBS end as NUMOFTRANSACTIONALEMAILJOBS
,case when @ISINCLUDEGENERALPURPOSEEMAILJOBSPARAMETER = 0 then null else NUMOFGENERALPURPOSEEMAILJOBS end as NUMOFGENERALPURPOSEEMAILJOBS
from @ALLEMAILJOBSTEMPTABLE  

end