USP_REPORT_STAFFINGREPORT

      create procedure dbo.USP_REPORT_STAFFINGREPORT
      (
        @STARTDATE datetime = null,
        @ENDDATE datetime = null,
        @TICKETORDERFILTER bit = null,
        @STARTTIME nvarchar(10) = null,
        @ENDTIME nvarchar(10) = null,
        @DAYOFWEEK integer = null,
        @PROGRAMID uniqueidentifier = null,
        @PROGRAMQUERY uniqueidentifier = null, 
        @SALESMETHODID uniqueidentifier = null,
        @SALESMETHODQUERY uniqueidentifier = null
      )
      as
          set nocount on;
        begin

            set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE)
            set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE)

            --calculate the total number of days

            declare @TOTALNUMBEROFDAYS integer = datediff(day, @STARTDATE, @ENDDATE) + 1

            --in order to get the correct number of days to divide by

            declare @NUMBEROFDAYSPERWEEKDAY integer = @TOTALNUMBEROFDAYS / 7
            declare @NUMBEROFEXTRADAYS integer = @TOTALNUMBEROFDAYS % 7
            declare @STARTWEEKDAY tinyint = datepart(dw, @STARTDATE)

            -- Single day of week

            declare @DAYS table([DAY] integer, [NUMBEROFDAYS] integer);
            if @DAYOFWEEK < 8
            begin
                insert into @DAYS ([DAY], [NUMBEROFDAYS]) values (@DAYOFWEEK, @NUMBEROFDAYSPERWEEKDAY);
            end;

            -- Mon - Fri

            if @DAYOFWEEK = 8
            begin
                insert into @DAYS ([DAY], [NUMBEROFDAYS]) values (2, @NUMBEROFDAYSPERWEEKDAY);
                insert into @DAYS ([DAY], [NUMBEROFDAYS]) values (3, @NUMBEROFDAYSPERWEEKDAY);
                insert into @DAYS ([DAY], [NUMBEROFDAYS]) values (4, @NUMBEROFDAYSPERWEEKDAY);
                insert into @DAYS ([DAY], [NUMBEROFDAYS]) values (5, @NUMBEROFDAYSPERWEEKDAY);
                insert into @DAYS ([DAY], [NUMBEROFDAYS]) values (6, @NUMBEROFDAYSPERWEEKDAY);
            end;

            -- Sat - Sun

            if @DAYOFWEEK = 9
            begin
                insert into @DAYS ([DAY], [NUMBEROFDAYS]) values (1, @NUMBEROFDAYSPERWEEKDAY);
                insert into @DAYS ([DAY], [NUMBEROFDAYS]) values (7, @NUMBEROFDAYSPERWEEKDAY);
            end;

            -- All week

            if @DAYOFWEEK = 10
            begin
                insert into @DAYS ([DAY], [NUMBEROFDAYS]) values (1, @NUMBEROFDAYSPERWEEKDAY);
                insert into @DAYS ([DAY], [NUMBEROFDAYS]) values (2, @NUMBEROFDAYSPERWEEKDAY);
                insert into @DAYS ([DAY], [NUMBEROFDAYS]) values (3, @NUMBEROFDAYSPERWEEKDAY);
                insert into @DAYS ([DAY], [NUMBEROFDAYS]) values (4, @NUMBEROFDAYSPERWEEKDAY);
                insert into @DAYS ([DAY], [NUMBEROFDAYS]) values (5, @NUMBEROFDAYSPERWEEKDAY);
                insert into @DAYS ([DAY], [NUMBEROFDAYS]) values (6, @NUMBEROFDAYSPERWEEKDAY);
                insert into @DAYS ([DAY], [NUMBEROFDAYS]) values (7, @NUMBEROFDAYSPERWEEKDAY);
            end

            --Add on the extra days

            update @DAYS set [NUMBEROFDAYS] = [NUMBEROFDAYS] + 1
            where [DAY] between @STARTWEEKDAY and @STARTWEEKDAY + @NUMBEROFEXTRADAYS - 1
            or [DAY] + 7 between @STARTWEEKDAY and @STARTWEEKDAY + @NUMBEROFEXTRADAYS - 1


            declare @STARTHOUR integer = (datepart(hh,convert(datetime,@STARTTIME)));
            declare @ENDHOUR integer = (datepart(hh,convert(datetime,@ENDTIME))) - 1;

            --Case when we're ending at 11:59 and the hour is 24

            if @ENDHOUR = -1
                set @ENDHOUR = 23

            -- Dummy data that does not affect totals or averages, but will ensure that all days and time

            -- periods are shown in the graphs and table

            declare @DUMMYDATA table(TOTALQUANTITY integer,TRANSACTIONDAY integer,TRANSACTIONHOUR integer, NUMBEROFDAYS integer)
            declare @DAYCOUNT tinyint = 1
            declare @HOURCOUNT tinyint
            declare @NUMBEROFDAYS integer
            while @DAYCOUNT < 8
            begin
                set @HOURCOUNT = 0

                set @NUMBEROFDAYS = 0
                select @NUMBEROFDAYS = NUMBEROFDAYS from @DAYS D where D.DAY = @DAYCOUNT

                while @HOURCOUNT < 24
                begin
                if (@HOURCOUNT between @STARTHOUR and @ENDHOUR)
                begin
                    insert into @DUMMYDATA (TOTALQUANTITY, TRANSACTIONDAY, TRANSACTIONHOUR, NUMBEROFDAYS)
                    values (0, @DAYCOUNT, @HOURCOUNT, @NUMBEROFDAYS)
                end
                set @HOURCOUNT = @HOURCOUNT + 1
                end
                set @DAYCOUNT = @DAYCOUNT + 1
            end

            declare @PROGRAMS table (
                ID uniqueidentifier
            )
            if @PROGRAMID is null and @PROGRAMQUERY is null
                insert into @PROGRAMS
                select ID from dbo.PROGRAM
            else if @PROGRAMID is not null
                insert into @PROGRAMS
                values(@PROGRAMID)
            else
                insert into @PROGRAMS
                select ID
                from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@PROGRAMQUERY)

            declare @SALESMETHODS table (
                TYPECODE tinyint
            )
            if @SALESMETHODID is null and @SALESMETHODQUERY is null
                insert into @SALESMETHODS
                select TYPECODE from dbo.SALESMETHOD
            else if @SALESMETHODID is not null
                insert into @SALESMETHODS
                select TYPECODE from dbo.SALESMETHOD where ID = @SALESMETHODID
            else
                insert into @SALESMETHODS
                select TYPECODE 
                from dbo.SALESMETHOD
                inner join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SALESMETHODQUERY) [SMS] on
                    SALESMETHOD.ID = [SMS].ID

            -- Tickets

            if @TICKETORDERFILTER = 0
            begin
                --update the dummy dada to include the actual data

                update D
                    set D.TOTALQUANTITY = D.TOTALQUANTITY + DATA.TOTALQUANTITY
                from (
                    select 
                        sum(SOI.QUANTITY) as TOTALQUANTITY, 
                        [DATEPARTS].[DAY] as TRANSACTIONDAY,
                        [DATEPARTS].[HOUR] as TRANSACTIONHOUR
                    from dbo.SALESORDER as SO
                    inner join @SALESMETHODS [SALESMETHODS] on
                        SO.SALESMETHODTYPECODE = [SALESMETHODS].[TYPECODE]
                    inner join dbo.SALESORDERITEM as SOI on SO.ID = SOI.SALESORDERID
                    inner join dbo.SALESORDERITEMTICKET as SOIT on SOI.ID = SOIT.ID
                    left outer join dbo.[EVENT] as E on E.ID = SOIT.EVENTID
                    inner join @PROGRAMS [PROGRAMS] on
                        SOIT.PROGRAMID = [PROGRAMS].ID or
                        E.[PROGRAMID] = [PROGRAMS].ID
                    cross apply(
                        select 
                            datepart(dw,[SO].[TRANSACTIONDATE]) [DAY],
                            datepart(hh,[SO].[TRANSACTIONDATE]) [HOUR]
                    ) as [DATEPARTS]
                    inner join @DAYS [DAYS] on
                        [DATEPARTS].[DAY] = [DAYS].[DAY] and
                        [DATEPARTS].[HOUR] between @STARTHOUR and @ENDHOUR
                    where
                        SOI.TYPECODE = 0 and --This shouldn't be necessary since we're inner joining on SALESORDERITEMTICKET

                        SO.STATUSCODE = 1 and
                        [SO].[TRANSACTIONDATE] between @STARTDATE and @ENDDATE
                    group by 
                        [DATEPARTS].[DAY],
                        [DATEPARTS].[HOUR]
                ) DATA
                inner join @DUMMYDATA D 
                on 
                    D.TRANSACTIONHOUR = DATA.TRANSACTIONHOUR and 
                    D.TRANSACTIONDAY = DATA.TRANSACTIONDAY
            end

            -- Orders

            if @TICKETORDERFILTER = 1
            begin
                --update the dummy dada to include the actual data

                update D
                set D.TOTALQUANTITY = D.TOTALQUANTITY + DATA.TOTALQUANTITY
                from (
                    select 
                        count(distinct SO.[ID]) as TOTALQUANTITY, 
                        [DATEPARTS].[DAY] as TRANSACTIONDAY,
                        [DATEPARTS].[HOUR] as TRANSACTIONHOUR
                    from dbo.SALESORDER as SO
                    inner join @SALESMETHODS [SALESMETHODS] on
                        SO.SALESMETHODTYPECODE = [SALESMETHODS].[TYPECODE]
                    inner join dbo.SALESORDERITEM as SOI on SO.ID = SOI.SALESORDERID
                    --Since we aren't just reporting on tickets, these next 3 are left joins. We'll do the program check in the where clause (which would essentially make this like @TICKETORDERFILTER = 0)

                    left join dbo.SALESORDERITEMTICKET as SOIT on SOI.ID = SOIT.ID
                    left outer join dbo.[EVENT] as E on E.ID = SOIT.EVENTID
                    left join @PROGRAMS [PROGRAMS] on 
                        SOIT.PROGRAMID = [PROGRAMS].ID or
                        E.[PROGRAMID] = [PROGRAMS].ID
                    cross apply(
                        select 
                            datepart(dw,[SO].[TRANSACTIONDATE]) [DAY],
                            datepart(hh,[SO].[TRANSACTIONDATE]) [HOUR]
                    ) as [DATEPARTS]
                    inner join @DAYS [DAYS] on
                        [DATEPARTS].[DAY] = [DAYS].[DAY] and
                        [DATEPARTS].[HOUR] between @STARTHOUR and @ENDHOUR
                    where
                        SO.STATUSCODE = 1 and
                        [SO].[TRANSACTIONDATE] between @STARTDATE and @ENDDATE and
                        (--Program report parameter check

                            (@PROGRAMID is null and @PROGRAMQUERY is null) or
                            [PROGRAMS].ID is not null
                        )
                    group by 
                        [DATEPARTS].[DAY],
                        [DATEPARTS].[HOUR]
                ) DATA
                inner join @DUMMYDATA D 
                on 
                    D.TRANSACTIONHOUR = DATA.TRANSACTIONHOUR and 
                    D.TRANSACTIONDAY = DATA.TRANSACTIONDAY
            end



            --update dummy data to include subtotal columns and rows for the grid

            --Include the subtotals for the hours

            insert into @DUMMYDATA (TOTALQUANTITY, TRANSACTIONDAY, TRANSACTIONHOUR, NUMBEROFDAYS)
            select
                sum(TOTALQUANTITY),
                8,
                TRANSACTIONHOUR,
                sum(NUMBEROFDAYS)
            from
                @DUMMYDATA
            group by TRANSACTIONHOUR

            --Include the subtotals for the days

            insert into @DUMMYDATA (TOTALQUANTITY, TRANSACTIONDAY, TRANSACTIONHOUR, NUMBEROFDAYS)
            select
                sum(TOTALQUANTITY),
                TRANSACTIONDAY,
                25,
                NUMBEROFDAYS
            from
                @DUMMYDATA
            where TRANSACTIONDAY <> 8
            group by TRANSACTIONDAY, NUMBEROFDAYS

            select TOTALQUANTITY, TRANSACTIONDAY, TRANSACTIONHOUR, NUMBEROFDAYS from @DUMMYDATA

        end