USP_REPORT_EVENTATTENDANCE

create procedure dbo.USP_REPORT_EVENTATTENDANCE 
(
    @FROMDATE datetime = null,
    @TODATE datetime = null,
    @EVENTTYPE tinyint = 0, 
    @USERNAME nvarchar(100) = null,
    @INCLUDEINACTIVE bit = 0
)
as
    set nocount on;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    declare @APPUSERID uniqueidentifier;
    select @APPUSERID = ID from dbo.APPUSER where USERNAME = @USERNAME;

    set @FROMDATE = dbo.UFN_DATE_GETEARLIESTTIME(@FROMDATE);
    set @TODATE = dbo.UFN_DATE_GETLATESTTIME(@TODATE);
    declare @CURRDATE datetime = getdate();

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

    -- These security checks deactivate links to pages that the user

    -- has been denied. This is merely a convenience, not the actual security.

    -- Therefore it has been designed to 'fail open', i.e. show the links

    -- if the user is not found.

    declare @GRANTEDPREREGISTEREDROSTER bit = 1;
    declare @GRANTEDSCHEDULEDEVENTDETAILREPORT bit = 1;
    declare @GRANTEDEVENTPROFILEREPORT bit = 1;

    if @APPUSERID is not null and dbo.UFN_APPUSER_ISSYSADMIN(@APPUSERID) <> 1 begin
        select @GRANTEDPREREGISTEREDROSTER = dbo.UFN_SECURITY_APPUSER_GRANTED_DASHBOARD_IN_SYSTEMROLE(@APPUSERID, 'ce1594d7-911a-498e-a67a-2c2968f16c40');
        select @GRANTEDSCHEDULEDEVENTDETAILREPORT = dbo.UFN_SECURITY_APPUSER_GRANTED_REPORT_IN_SYSTEMROLE(@APPUSERID, '0db13684-bf29-48ea-b584-f00ad03e860d');
        select @GRANTEDEVENTPROFILEREPORT = dbo.UFN_SECURITY_APPUSER_GRANTED_REPORT_IN_SYSTEMROLE(@APPUSERID, '672c39eb-a5d5-452c-9111-55e202488ede');
    end

    -- EVENTTYPE: 0 = all, 1 = daily admission, 2 = scheduled events, 3 = special events


    declare @RETURNTABLE table 
    (
        EVENTNAME nvarchar(100),
        STARTDATE date,
        STARTTIME nvarchar(8),
        CAPACITY int,
        EVENTURL nvarchar(255),
        PRICETYPE nvarchar(100),
        PRICETYPEREGISTEREDCOUNT int,
        PRICETYPEATTENDINGCOUNT int,
        EVENTTYPE tinyint,
        AVAILABILITYDRAIN int,
        STARTTIMENOFORMAT int,
        SCANNEDCOUNT int,
        SOLD int,
        EVENTID uniqueidentifier
    );

    declare @PROGRAMS table
    (
        EVENTNAME nvarchar(100),
        STARTDATE date,
        PRICETYPE nvarchar(100),
        SCANNEDCOUNT int,
        SOLD int,
        unique clustered (EVENTNAME,STARTDATE,PRICETYPE)
    );

    -- Insert daily admission data into temp table

    if @EVENTTYPE = 1 or @EVENTTYPE = 0 -- Daily admission or All types of events

    begin
        insert into @PROGRAMS
        select
            PROGRAM.NAME,
            SALESORDER.DATE as STARTDATE,
            dbo.UFN_PRICETYPECODE_GETDESCRIPTION(SALESORDERITEMTICKET.PRICETYPECODEID) PRICETYPE,
            0,
            sum(SALESORDERITEM.QUANTITY - isnull(CANCELLEDTICKETS.QUANTITY,0)) as SOLD
        from
            dbo.SALESORDERITEMTICKET 
        inner join
            dbo.SALESORDERITEM on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
        inner join (
            select RESERVATION.ID, RESERVATION.ARRIVALDATE as DATE
            from dbo.SALESORDER
            inner join dbo.RESERVATION on RESERVATION.ID = SALESORDER.ID
            where RESERVATION.ARRIVALDATE between @FROMDATE and @TODATE
                and (SALESORDER.STATUSCODE = 1 or (SALESORDER.SALESMETHODTYPECODE = 3 and SALESORDER.STATUSCODE <> 5))

            union all

            select SALESORDER.ID, cast(SALESORDER.TRANSACTIONDATE as date) as DATE
            from dbo.SALESORDER
            left join dbo.RESERVATION on RESERVATION.ID = SALESORDER.ID
            where
                SALESORDER.TRANSACTIONDATE between @FROMDATE and @TODATE
                and RESERVATION.ID is null
                and (SALESORDER.STATUSCODE = 1 or (SALESORDER.SALESMETHODTYPECODE = 3 and SALESORDER.STATUSCODE <> 5))
        ) as SALESORDER on SALESORDER.ID = SALESORDERITEM.SALESORDERID
        inner join
            dbo.PROGRAM on SALESORDERITEMTICKET.PROGRAMID = PROGRAM.ID
        left join
            dbo.SALESORDERRESERVEDITEM on SALESORDERITEM.ID = SALESORDERRESERVEDITEM.ID
        left join
            dbo.RESERVATION on SALESORDER.ID = RESERVATION.ID
        left outer join (
            select T. SALESORDERITEMTICKETID, count(*) as QUANTITY from dbo.TICKET T where T.STATUSCODE = 2  group by T.SALESORDERITEMTICKETID
        ) as CANCELLEDTICKETS on CANCELLEDTICKETS.SALESORDERITEMTICKETID = SALESORDERITEMTICKET.ID
        where PROGRAM.ISDAILYADMISSION = 1
            and (@INCLUDEINACTIVE = 1 or PROGRAM.ISACTIVE = 1)
            and (SALESORDERRESERVEDITEM.EXPIRATIONDATE is null or SALESORDERRESERVEDITEM.EXPIRATIONDATE >= @CURRDATE)
        group by 
            SALESORDERITEMTICKET.PRICETYPECODEID, PROGRAM.NAME, SALESORDER.DATE
        order by SALESORDER.DATE;

        declare @TICKETSSCANNED as table
        (
            SCANNED int,
            SCANDATE date,
            PROGRAMNAME nvarchar(200),
            PRICETYPENAME nvarchar(100),
            unique clustered (PROGRAMNAME,SCANDATE,PRICETYPENAME)
        )
        insert into @TICKETSSCANNED
        select
            count(*),
            convert(date, TICKET.SCANDATEWITHTIMEOFFSET),
            PROGRAM.NAME,
            dbo.UFN_PRICETYPECODE_GETDESCRIPTION(TICKET.PRICETYPECODEID)
        from
            dbo.TICKET
        inner join
            dbo.PROGRAM on PROGRAM.ID = TICKET.PROGRAMID
        where
            TICKET.EVENTID is null and TICKET.SALESORDERITEMTICKETID is not null
            and SCANDATEWITHTIMEOFFSET between @FROMDATETIMEOFFSET and @TODATETIMEOFFSET
            and (@INCLUDEINACTIVE = 1 or PROGRAM.ISACTIVE = 1)
        group by convert(date, TICKET.SCANDATEWITHTIMEOFFSET), TICKET.PRICETYPECODEID, PROGRAM.NAME

        merge @PROGRAMS as SOLDTICKETS
        using @TICKETSSCANNED as SCANNEDTICKETS on (
            SOLDTICKETS.STARTDATE = SCANNEDTICKETS.SCANDATE
            and SOLDTICKETS.EVENTNAME = SCANNEDTICKETS.PROGRAMNAME
            and SOLDTICKETS.PRICETYPE = SCANNEDTICKETS.PRICETYPENAME
        )
        when matched then
            update set SOLDTICKETS.SCANNEDCOUNT = SCANNEDTICKETS.SCANNED
        when not matched then
            insert (
                EVENTNAME,
                STARTDATE,
                PRICETYPE,
                SCANNEDCOUNT,
                SOLD
            )
            values (
                SCANNEDTICKETS.PROGRAMNAME,
                SCANNEDTICKETS.SCANDATE,
                SCANNEDTICKETS.PRICETYPENAME,
                SCANNEDTICKETS.SCANNED,
                0
            );
    end

    -- Insert scheduled event data into temp table

    if @EVENTTYPE = 2 or @EVENTTYPE = 0 begin -- Scheduled Event or All

        insert into @RETURNTABLE
        select
            [EVENT].NAME,
            [EVENT].STARTDATE, 
            dbo.UFN_HOURMINUTE_DISPLAYTIME([EVENT].STARTTIME) STARTTIME, 
            [EVENT].CAPACITY as CAPACITY,
            case
                when PROGRAM.ISPREREGISTERED = 1 then
                    case
                        when @GRANTEDPREREGISTEREDROSTER = 1 then
                            'http://www.blackbaud.com/PREREGISTEREDEVENTID?PREREGISTEREDEVENTID=' + convert(nvarchar(36), [EVENT].ID)
                        else
                            null
                    end
                else
                    case
                        when @GRANTEDSCHEDULEDEVENTDETAILREPORT = 1 then
                            'http://www.blackbaud.com/SCHEDULEDEVENTID?SCHEDULEDEVENTID=' + convert(nvarchar(36), [EVENT].ID)
                        else
                            null
                    end
            end as EVENTURL,
            dbo.UFN_PRICETYPECODE_GETDESCRIPTION(SALESORDERITEMTICKET.PRICETYPECODEID) as PRICETYPE,
            PREREGATTENDANCE.REGISTERED as PRICETYPEREGISTEREDCOUNT,
            PREREGATTENDANCE.ATTENDED as PRICETYPEATTENDINGCOUNT,
            2 as EVENTTYPE,
            sum(
                case
                    when SALESORDERRESERVEDITEM.EXPIRATIONDATE is null or SALESORDERRESERVEDITEM.EXPIRATIONDATE >= @CURRDATE then
                        isnull(SALESORDERITEM.QUANTITY, 0) - isnull(CANCELLEDTICKETS.QUANTITY,0)
                    else
                        0
                end
            ) as AVAILABILITYDRAIN,
            case
                when len([EVENT].STARTTIME) = 0 then 0
                else cast([EVENT].STARTTIME as int)
            end as STARTTIMENOFORMAT,
            isnull(TICKETSSCANNED.TICKETCOUNT,0),
            sum(
                case
                    when (SALESORDER.SALESMETHODTYPECODE = 3 and SALESORDER.STATUSCODE <> 5) or SALESORDER.STATUSCODE = 1 then  -- (Group Sales and Cancelled) or Complete

                        isnull(SALESORDERITEM.QUANTITY, 0) - isnull(CANCELLEDTICKETS.QUANTITY,0)
                    else
                        0
                end
            ) as SOLD,
            EVENT.ID
        from dbo.[EVENT]
        inner join dbo.PROGRAM on [EVENT].PROGRAMID = PROGRAM.ID
        left join dbo.SALESORDERITEMTICKET on SALESORDERITEMTICKET.EVENTID = [EVENT].ID
        left join dbo.SALESORDERITEM on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
        left join dbo.SALESORDER on SALESORDERITEM.SALESORDERID = SALESORDER.ID
        left join dbo.SALESORDERRESERVEDITEM on SALESORDERITEM.ID = SALESORDERRESERVEDITEM.ID
        left outer join (
            select T.SALESORDERITEMTICKETID, count(*) as QUANTITY from dbo.TICKET T where T.STATUSCODE = 2 group by T.SALESORDERITEMTICKETID
        ) as CANCELLEDTICKETS on CANCELLEDTICKETS.SALESORDERITEMTICKETID = SALESORDERITEMTICKET.ID
        left outer join (
            select
                T.EVENTID,
                T.PRICETYPECODEID,
                T.SALESORDERITEMTICKETID,
                count(*) as TICKETCOUNT
            from
                dbo.TICKET T
            where
                T.SCANDATEWITHTIMEOFFSET is not null
            group by
                T.EVENTID, T.PRICETYPECODEID, T.SALESORDERITEMTICKETID
        ) as TICKETSSCANNED on TICKETSSCANNED.EVENTID = [EVENT].ID and TICKETSSCANNED.PRICETYPECODEID = SALESORDERITEMTICKET.PRICETYPECODEID and TICKETSSCANNED.SALESORDERITEMTICKETID = SALESORDERITEMTICKET.ID
        outer apply (
            select
                sum(case 
                    when REGISTRANT.ID is not null and REGISTRANT.ISCANCELLED = 0
                        then 1
                    else 0
                end) as REGISTERED,
                sum(case
                    when REGISTRANT.ATTENDED = 1 and REGISTRANT.ISCANCELLED = 0
                        then 1
                    else 0
                end) as ATTENDED
            from 
                dbo.SALESORDERITEMTICKETREGISTRANT
            inner join
                dbo.REGISTRANT on SALESORDERITEMTICKETREGISTRANT.REGISTRANTID = REGISTRANT.ID
            where
                SALESORDERITEMTICKETREGISTRANT.SALESORDERITEMTICKETID = SALESORDERITEMTICKET.ID
        ) as PREREGATTENDANCE
        where [EVENT].STARTDATE between @FROMDATE and @TODATE
            and (@INCLUDEINACTIVE = 1 or PROGRAM.ISACTIVE = 1)
        group by 
            SALESORDERITEMTICKET.PRICETYPECODEID, [EVENT].STARTDATETIME,
            [EVENT].NAME, [EVENT].STARTDATE, [EVENT].STARTTIME, [EVENT].CAPACITY, [EVENT].ID, TICKETSSCANNED.TICKETCOUNT,
            PREREGATTENDANCE.REGISTERED, PREREGATTENDANCE.ATTENDED, PROGRAM.ISPREREGISTERED, SALESORDERITEMTICKET.ID
        order by 
            [EVENT].STARTDATETIME;
    end

    -- Insert special event data into temp table

    if @EVENTTYPE = 3 or @EVENTTYPE = 0 begin -- BBEC Event or All

        insert into @RETURNTABLE
        select
            [EVENT].NAME, 
            [EVENT].STARTDATE, 
            dbo.UFN_HOURMINUTE_DISPLAYTIME([EVENT].STARTTIME) STARTTIME,
            [EVENT].CAPACITY as CAPACITY,
            case
                when @GRANTEDEVENTPROFILEREPORT = 1 then 'http://www.blackbaud.com/SPECIALEVENTID?SPECIALEVENTID=' + CONVERT(nvarchar(36), [EVENT].ID)
                else null
            end  EVENTURL,
            isnull(dbo.UFN_EVENTREGISTRATIONTYPE_GETDESCRIPTION(EVENTPRICE.EVENTREGISTRATIONTYPEID), 'No option selected'),
            count(REGISTRANT.ID) as PRICETYPEREGISTEREDCOUNT,
            count(nullif(REGISTRANT.ATTENDED, 0)) as PRICETYPEATTENDINGCOUNT,
            3 as EVENTTYPE,
            count(REGISTRANT.ID) as AVAILABILITYDRAIN,
            case
                when len([EVENT].STARTTIME) = 0 then 0
                else cast([EVENT].STARTTIME as int)
            end as STARTTIMENOFORMAT, 
            null as SCANNEDCOUNT,
            null as SOLD,
            EVENT.ID
        from 
            dbo.[EVENT] 
        left outer join 
            dbo.REGISTRANT on REGISTRANT.EVENTID = [EVENT].ID
        left outer join 
            dbo.REGISTRANTREGISTRATIONMAP on REGISTRANT.ID = REGISTRANTREGISTRATIONMAP.REGISTRANTID
        left outer join 
            dbo.REGISTRANTREGISTRATION on REGISTRANTREGISTRATIONMAP.REGISTRANTREGISTRATIONID = REGISTRANTREGISTRATION.ID
        left outer join 
            dbo.EVENTPRICE on REGISTRANTREGISTRATION.EVENTPRICEID = EVENTPRICE.ID
        where
            [EVENT].PROGRAMID is null
            and (@INCLUDEINACTIVE = 1 or [EVENT].ISACTIVE = 1)
            and [EVENT].STARTDATE between @FROMDATE and @TODATE
            and (
                REGISTRANT.ID is null
                or (
                    REGISTRANT.WILLNOTATTEND = 0
                    and dbo.[UFN_REGISTRANT_ISCANCELLED](REGISTRANT.ID) = 0
                )
            )
        group by 
            EVENTPRICE.EVENTREGISTRATIONTYPEID, [EVENT].ID, [EVENT].NAME, [EVENT].STARTDATE, [EVENT].STARTTIME, [EVENT].CAPACITY, [EVENT].STARTDATETIME
        order by 
            [EVENT].STARTDATETIME asc; 
    end;

    declare @EVENTCAPACITY table 
    (
        EVENTNAME nvarchar(100) primary key,
        TOTALCAPACITY bigint
    );
    insert into @EVENTCAPACITY
    select TC.EVENTNAME,sum(cast(TC.CAPACITY as bigint))
    from (select distinct RT.EVENTID,RT.CAPACITY,RT.EVENTNAME from @RETURNTABLE RT) TC
    group by TC.EVENTNAME;

    select   
        RT.EVENTNAME,
        STARTDATE,
        STARTTIME,
        CAPACITY,
        EVENTURL, 
        PRICETYPE,
        PRICETYPEATTENDINGCOUNT as PRICETYPECOUNT,  
        EVENTTYPE, 
        PRICETYPEREGISTEREDCOUNT,
        PRICETYPEATTENDINGCOUNT,
        AVAILABILITYDRAIN,
        STARTTIMENOFORMAT,
        SCANNEDCOUNT,
        SOLD,
        T.TOTALCAPACITY
    from  
        @RETURNTABLE RT
    left outer join
        @EVENTCAPACITY T on T.EVENTNAME = RT.EVENTNAME

    union all

    select
        P.EVENTNAME,
        STARTDATE,
        null,
        null,
        null,
        PRICETYPE,
        null,
        1,
        null,
        null,
        null,
        0,
        SCANNEDCOUNT,
        SOLD,
        T.TOTALCAPACITY
    from
        @PROGRAMS P
    left outer join
        @EVENTCAPACITY T on T.EVENTNAME = P.EVENTNAME;