USP_REPORT_SCHEDULEDTICKETDETAIL

create procedure dbo.USP_REPORT_SCHEDULEDTICKETDETAIL
(
    @EVENTID uniqueidentifier,
    @USERNAME nvarchar(100),
    @SORTBY tinyint = 0  -- allows the user to sort by full name when 0, last name (keyname) when 1
)
as  

set nocount on; 

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

declare @CURRDATE datetime = getdate()

-- 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 @GRANTEDADVANCESALES bit = 1;
declare @GRANTEDORDERPAGE bit = 1;
declare @GRANTEDCONSTITUENTPAGE bit = 1;
declare @GRANTEDRESERVATIONPAGE bit = 1;

if not @APPUSERID is null and not dbo.UFN_APPUSER_ISSYSADMIN(@APPUSERID) = 1
begin
    select @GRANTEDADVANCESALES = dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_IN_SYSTEMROLE(@APPUSERID, 'BCE1D740-D4FB-4DFC-A047-B7B487366EC0')
    select @GRANTEDORDERPAGE = dbo.UFN_SECURITY_APPUSER_GRANTED_PAGEEXPRESSIONFORM_IN_SYSTEMROLE(@APPUSERID, '256be822-d0b4-45dd-a42a-8f4eb054a4ad')
    select @GRANTEDCONSTITUENTPAGE = dbo.UFN_SECURITY_APPUSER_GRANTED_PAGEEXPRESSIONFORM_IN_SYSTEMROLE(@APPUSERID, '88159265-2B7E-4c7b-82A2-119D01ECD40F')
    select @GRANTEDRESERVATIONPAGE = dbo.UFN_SECURITY_APPUSER_GRANTED_PAGEEXPRESSIONFORM_IN_SYSTEMROLE(@APPUSERID, '4ac8ed9d-413a-4ca3-98d3-10680a2b0151')
end;

with TICKETDETAIL_CTE as
(
    select distinct
        CONSTITUENT.ID as ID,
        CONSTITUENT.NAME as NAME,
        CONSTITUENT.KEYNAME as KEYNAME,
        CONSTITUENT.KEYNAMEPREFIX as KEYNAMEPREFIX,
        CONSTITUENT.DISPLAYNAME as DISPLAYNAME,
        CONSTITUENT.FIRSTNAME as FIRSTNAME,
        CONSTITUENT.MIDDLENAME as MIDDLENAME,
        CONSTITUENT.ISORGANIZATION as ISORGANIZATION,
        CONSTITUENT.ISGROUP as ISGROUP,
        case
            when CONSTITUENT.ID is null then 
                '---'
            when MEMBER.ID is null then 
                'No'
            else 
                'Yes'
        end as MEMBER,
        PRICETYPECODE.[DESCRIPTION] + ' - ' + 
            case
                when TICKET.EVENTID is null then 
                    cast(cast(sum(SALESORDERITEM.QUANTITY) as int) as varchar)
                else 
                    cast(coalesce(count(TICKET.ID), 0) as varchar)
            end as TICKETS,
        SALESORDER.SEQUENCEID as ORDERNUMBER,
        case
            when TOTALS.BALANCE <= 0 
            then 
                'Yes'
            else 
                'No'
        end as ORDERPAID,
        case
            when @GRANTEDCONSTITUENTPAGE = 1 then 
                'http://www.blackbaud.com/CONSTITUENTID?CONSTITUENTID=' + CONVERT(nvarchar(36), CONSTITUENT.ID)
            else 
                null
        end as CONSTITUENTURL,
        @USERNAME as USERNAME,
        SALESORDER.ID as SALESORDERID,
        SALESORDER.SALESMETHODTYPECODE as SALESMETHODTYPECODE,
        SALESORDER.STATUSCODE as STATUSCODE
    from 
        dbo.[EVENT]
    inner join 
        dbo.SALESORDERITEMTICKET on SALESORDERITEMTICKET.EVENTID = [EVENT].ID
    inner join 
        dbo.SALESORDERITEM on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
    inner join 
        dbo.SALESORDER on SALESORDERITEM.SALESORDERID = SALESORDER.ID
    inner join 
        dbo.PRICETYPECODE on SALESORDERITEMTICKET.PRICETYPECODEID = PRICETYPECODE.ID
    left outer join 
        dbo.TICKET on TICKET.SALESORDERITEMTICKETID = SALESORDERITEMTICKET.ID
    left outer join 
        dbo.CONSTITUENT on SALESORDER.CONSTITUENTID = CONSTITUENT.ID
    left outer join 
        dbo.MEMBER on MEMBER.CONSTITUENTID = CONSTITUENT.ID and MEMBER.ISDROPPED = 0 and 0 = (select STATUSCODE from dbo.MEMBERSHIP where ID = MEMBER.MEMBERSHIPID)
    left outer join 
        dbo.SALESORDERRESERVEDITEM on SALESORDERITEM.ID = SALESORDERRESERVEDITEM.ID
    outer apply
        dbo.UFN_SALESORDER_TOTALS(SALESORDER.ID) as TOTALS
    where 
        [EVENT].ID = @EVENTID 
        and not [EVENT].PROGRAMID is null 
        and (TICKET.STATUSCODE in (0, 1) or TICKET.ID is null)
        and not SALESORDER.STATUSCODE = 5
        and (
            SALESORDER.STATUSCODE in (1,7) or --Unresolved, Complete Online Orders can still have reserved items
            SALESORDERRESERVEDITEM.EXPIRATIONDATE is null or 
            SALESORDERRESERVEDITEM.EXPIRATIONDATE >= @CURRDATE
        )
    group by 
        CONSTITUENT.NAME, 
        PRICETYPECODE.[DESCRIPTION], 
        SALESORDER.SEQUENCEID, 
        TICKET.EVENTID,
        SALESORDER.ID, 
        CONSTITUENT.ID, 
        MEMBER.ID, 
        SALESORDER.SALESMETHODTYPECODE, 
        SALESORDER.STATUSCODE, 
        CONSTITUENT.NAME,
        CONSTITUENT.KEYNAME,
        CONSTITUENT.KEYNAMEPREFIX,
        CONSTITUENT.DISPLAYNAME,
        CONSTITUENT.FIRSTNAME,
        CONSTITUENT.MIDDLENAME,
        CONSTITUENT.ISORGANIZATION,
        CONSTITUENT.ISGROUP,
        TOTALS.BALANCE
)

select
    case
        when TICKETDETAIL_CTE.KEYNAME is null then
            'Anonymous'
        when @SORTBY = 1 then
            case TICKETDETAIL_CTE.ISORGANIZATION 
                when 1 then
                    case TICKETDETAIL_CTE.KEYNAMEPREFIX 
                        when '' then 
                            TICKETDETAIL_CTE.KEYNAME 
                        else 
                            TICKETDETAIL_CTE.KEYNAME + ', ' + TICKETDETAIL_CTE.KEYNAMEPREFIX 
                    end
                else
                    case TICKETDETAIL_CTE.ISGROUP 
                        when 1 then
                            case TICKETDETAIL_CTE.DISPLAYNAME 
                                when '' then 
                                    TICKETDETAIL_CTE.KEYNAME 
                                else 
                                    TICKETDETAIL_CTE.DISPLAYNAME 
                            end
                        else
                            dbo.UFN_NAMEFORMAT_08(TICKETDETAIL_CTE.ID, TICKETDETAIL_CTE.KEYNAME, TICKETDETAIL_CTE.FIRSTNAME, TICKETDETAIL_CTE.MIDDLENAME, 
                                null, null, null, null, null, null, null)
                    end
            end
        else
            TICKETDETAIL_CTE.NAME
    end as NAME,
    MEMBER,
    TICKETS,
    ORDERNUMBER,
    ORDERPAID,
    case
        when not STATUSCODE in (1, 6)
            then null
        when SALESMETHODTYPECODE = 3 and @GRANTEDRESERVATIONPAGE = 1 
            then 'http://www.blackbaud.com/RESERVATIONID?RESERVATIONID=' + CONVERT(nvarchar(36), SALESORDERID)
        when @GRANTEDORDERPAGE = 1 and (SALESMETHODTYPECODE in (0, 2) or 
            (SALESMETHODTYPECODE = 1 and (STATUSCODE = 1 or @GRANTEDADVANCESALES = 1)) or
            (SALESMETHODTYPECODE = 3 and STATUSCODE = 1) )
            then 
                case when ORDERPAID = 'Yes'
                then 
                    'http://www.blackbaud.com/SALESORDERID?SALESORDERID=' + CONVERT(nvarchar(36), SALESORDERID) 
                else 
                    'http://www.blackbaud.com/ADVANCESALESORDERID?SALESORDERID=' + CONVERT(nvarchar(36), SALESORDERID)
                end
        else null
    end as SALESORDERURL,
    CONSTITUENTURL,
    USERNAME
from 
    TICKETDETAIL_CTE
order by 
    NAME