USP_DATALIST_BATCHTICKETPRINTJOBS

CREATE procedure dbo.USP_DATALIST_BATCHTICKETPRINTJOBS (
    @DATERANGETYPE tinyint = 5,  -- This week

    @FROMDATE date = null,
    @TODATE date = null
)
as
    set nocount on;

    -- Use the client's default timezone

    declare @CURRENTDATE date = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(getutcdate(), 1);
    declare @DAYOFWEEK int = datepart(weekday, @CURRENTDATE);

    -- Simple adjustment since our filter's day of week starts on Monday

    if @DAYOFWEEK = 1  -- Sunday

        set @DAYOFWEEK = 8;

    if @DATERANGETYPE = 2  -- Today

    begin
        set @FROMDATE = @CURRENTDATE;
        set @TODATE = @CURRENTDATE;
    end

    else if @DATERANGETYPE = 3  -- Yesterday

    begin
        set @FROMDATE = dateadd(day, -1, @CURRENTDATE);
        set @TODATE = @FROMDATE;
    end

    else if @DATERANGETYPE = 5  -- This week

    begin
        set @FROMDATE = dateadd(day, -(@DAYOFWEEK - 2), @CURRENTDATE);
        set @TODATE =  dateadd(day, 6, @FROMDATE);
    end

    else if @DATERANGETYPE = 11  -- Last week

    begin
        set @FROMDATE = dateadd(day, -(@DAYOFWEEK - 2) - 7, @CURRENTDATE);
        set @TODATE =  dateadd(day, 6, @FROMDATE);
    end

    else if @DATERANGETYPE = 6  -- This month

    begin
        set @FROMDATE = dateadd(month, datediff(month, 0, @CURRENTDATE), 0)
        set @TODATE = dateadd(day, -1, dateadd(month, 1, @FROMDATE));
    end

    else if @DATERANGETYPE = 12  -- Last month

    begin
        set @FROMDATE = dateadd(month, datediff(month, 0, @CURRENTDATE) - 1, 0)
        set @TODATE = dateadd(day, -1, dateadd(month, 1, @FROMDATE));
    end

    declare @FROMDATETIMEOFFSET datetimeoffset = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(@FROMDATE, 0);
    declare @TODATETIMEOFFSET datetimeoffset = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(@TODATE, 0);

    select
        BATCHTICKETPRINTJOB.ID,
        convert(datetime, BATCHTICKETPRINTJOB.PRINTDATEWITHTIMEOFFSET) as PRINTDATE,
        (
            select count(*)
            from dbo.BATCHTICKETPRINTJOBTICKETLINK
            where BATCHTICKETPRINTJOBTICKETLINK.BATCHTICKETPRINTJOBID = BATCHTICKETPRINTJOB.ID
        ) as TICKETCOUNT,
        BATCHTICKETPRINTJOB.PRINTERNAME,
        APPUSER.DISPLAYNAME as APPUSERDISPLAYNAME
    from
        dbo.BATCHTICKETPRINTJOB
    left outer join
        dbo.APPUSER on APPUSER.ID = BATCHTICKETPRINTJOB.APPUSERID
    where
        (@FROMDATETIMEOFFSET is null or BATCHTICKETPRINTJOB.PRINTDATEWITHTIMEOFFSET >= @FROMDATETIMEOFFSET)
        and (@TODATETIMEOFFSET is null or BATCHTICKETPRINTJOB.PRINTDATEWITHTIMEOFFSET < dateadd(day, 1,@TODATETIMEOFFSET))
    order by
        PRINTDATE desc;

    return 0;