USP_DATAFORMTEMPLATE_VIEW_DAILYSALEITEMBUTTONS_2

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_DAILYSALEITEMBUTTONS_2
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @BUTTONS xml = null output
)
as
    set nocount on;

    set @DATALOADED = 1;

    declare @CURRENTDATETIMEOFFSET datetimeoffset = sysdatetimeoffset();
    declare @CONSTITUENTID uniqueidentifier = (select [CONSTITUENTID] from dbo.[SALESORDER] where [ID] = @ID);

    declare @SALESMETHODID uniqueidentifier
    select @SALESMETHODID = [ID]
    from dbo.[SALESMETHOD]
    where [TYPECODE] = 0

    declare @CURRENCYSYMBOL nchar(1) = (select CURRENCYSYMBOL from dbo.CURRENCY where ISORGANIZATIONCURRENCY = 1);    

    /* 
        Button field description:

        The first 5 fields (QUICKBUTTONID, TYPECODE, BUTTONTEXT, ISENABLED, ISGENERIC)
        are common to all buttons despite their type.

        The next 5 fields are generic string fields that can be used to bring back
        any data specific to the function of a particular button type.  This is done
        to limit the number of fields passed down from a data form load call.  The 
        field lengths are limited to 36 characters since the longest piece of data
        is generally a guid.  I don't see a case where the data would be anything 
        other than a byte, boolean, or guid, but if there is need, the field's max
        length can be extended.

        The last 2 fields (BUTTONORDER, BUTTONSUBORDER) are for ordering the list.
    */

    declare @BUTTONTEMPTABLE table
    (
        QUICKBUTTONID uniqueidentifier, 
        TYPECODE tinyint,
        BUTTONTEXT nvarchar(303),
        ISENABLED bit,
        ISGENERIC bit,
        PARAM1 nvarchar(36),
        PARAM2 nvarchar(36),
        PARAM3 nvarchar(36),
        PARAM4 nvarchar(36),
        PARAM5 nvarchar(36),
        BUTTONORDER integer,
        BUTTONSUBORDER integer
    )

    -- BEGIN POPULATE TEMP TABLE
    -- DAILY ADMISSION

    insert into @BUTTONTEMPTABLE
    select 
        [DAILYSALEITEM].[ID],
        DAILYSALEITEM.TYPECODE,
        dbo.UFN_DAILYSALEITEM_BUILDPROGRAMBUTTONTEXT(DAILYSALEITEM.ID, @CURRENCYSYMBOL),
        1,
        0,
        null,
        null,
        null,
        null,
        null,
        [DAILYSALEITEM].[BUTTONORDER],
        null
    from dbo.[DAILYSALEITEMPROGRAM]
    inner join dbo.[DAILYSALEITEM]
        on [DAILYSALEITEM].[ID] = [DAILYSALEITEMPROGRAM].[ID]
    inner join dbo.[PROGRAM]
        on [DAILYSALEITEMPROGRAM].[PROGRAMID] = [PROGRAM].[ID]
    inner join dbo.[PRICETYPECODE]
        on [DAILYSALEITEMPROGRAM].[PRICETYPECODEID] = [PRICETYPECODE].[ID] 
    inner join dbo.[PROGRAMPRICE]
        on DAILYSALEITEMPROGRAM.PROGRAMID = [PROGRAMPRICE].[PROGRAMID] and 
            [DAILYSALEITEMPROGRAM].[PRICETYPECODEID] = [PROGRAMPRICE].[PRICETYPECODEID]
    inner join
        dbo.PROGRAMSALESMETHOD on PROGRAMSALESMETHOD.PROGRAMID = DAILYSALEITEMPROGRAM.PROGRAMID and PROGRAMSALESMETHOD.SALESMETHODID = @SALESMETHODID
    left outer join
        dbo.SALESMETHODEXCLUDEDPRICETYPE on SALESMETHODEXCLUDEDPRICETYPE.PRICETYPECODEID = DAILYSALEITEMPROGRAM.PRICETYPECODEID and SALESMETHODEXCLUDEDPRICETYPE.SALESMETHODID = @SALESMETHODID
    where
        [DAILYSALEITEM].[ISACTIVE] = 1
        and [DAILYSALEITEMPROGRAM].[ISDAILYADMISSION] = 1
        and [PROGRAM].[ISACTIVE] = 1
        and SALESMETHODEXCLUDEDPRICETYPE.PRICETYPECODEID is null
        and [PRICETYPECODE].[ACTIVE] = 1 

    -- DISCOUNT
    union all
    select 
        [DAILYSALEITEM].[ID],
        DAILYSALEITEM.TYPECODE,
        dbo.UFN_DAILYSALEITEM_BUILDDISCOUNTBUTTONTEXT(DAILYSALEITEM.ID, @CURRENCYSYMBOL),
        1,
        0,
        null,
        null,
        null,
        null,
        null,
        [DAILYSALEITEM].[BUTTONORDER],
        null
    from
        dbo.[DAILYSALEITEMDISCOUNT]
    inner join
        dbo.[DAILYSALEITEM] on [DAILYSALEITEM].[ID] = [DAILYSALEITEMDISCOUNT].[ID]
    inner join
        dbo.[DISCOUNT] on [DAILYSALEITEMDISCOUNT].[DISCOUNTID] = [DISCOUNT].[ID]
    inner join
        dbo.DISCOUNTAVAILABILITY on DISCOUNTAVAILABILITY.DISCOUNTID = DAILYSALEITEMDISCOUNT.DISCOUNTID
    inner join
        dbo.DISCOUNTAVAILABILITYSALESMETHOD on DISCOUNTAVAILABILITYSALESMETHOD.DISCOUNTAVAILABILITYID = DISCOUNTAVAILABILITY.ID and DISCOUNTAVAILABILITYSALESMETHOD.SALESMETHODID = @SALESMETHODID
    where 
        [DAILYSALEITEM].[ISACTIVE] = 1 and
        [DISCOUNT].[ISACTIVE] = 1

    -- DONATION
    union all
    select 
        [DAILYSALEITEM].[ID],
        DAILYSALEITEM.TYPECODE,
        dbo.UFN_DAILYSALEITEM_BUILDDONATIONBUTTONTEXT(DAILYSALEITEM.ID, @CURRENCYSYMBOL),
        1,
        0,
        null,
        null,
        null,
        null,
        null,
        [DAILYSALEITEM].[BUTTONORDER],
        null
    from dbo.[DAILYSALEITEMDONATION]
    inner join dbo.[DAILYSALEITEM]
        on [DAILYSALEITEM].[ID] = [DAILYSALEITEMDONATION].[ID]
    inner join dbo.[DESIGNATION]
        on [DAILYSALEITEMDONATION].[DESIGNATIONID] = [DESIGNATION].[ID]
    where 
        [DAILYSALEITEM].[ISACTIVE] = 1 and
        [DESIGNATION].[ISACTIVE] = 1

    -- SCHEDULED PROGRAM
    union all
    select
        [QUICKBUTTONID],
        TYPECODE,
        case SCHEDULEOPTIONTYPECODE
            when 3 then BUTTONTEXTLINE1
            else BUTTONTEXTLINE1 + char(10) + BUTTONTEXTLINE2 + char(10) + BUTTONTEXTLINE3
        end,
        ISONSALE,
        0,
        SCHEDULEOPTIONTYPECODE,
        PROGRAMID,
        EVENTID,
        ISPREREGISTERED,
        PRICETYPECODEID,
        [BUTTONORDER],
        row_number() over(partition by BUTTONORDER order by [EVENTTIME]) as BUTTONSUBORDER
    from (
        select                        
            [DAILYSALEITEM].[ID] as [QUICKBUTTONID],
            DAILYSALEITEM.TYPECODE,
            case DESCRIPTIONFIELD1TYPECODE
                when 2 then PROGRAM.NAME
                when 3 then PRICETYPECODE.DESCRIPTION
                when 4 then @CURRENCYSYMBOL + convert(nvarchar(19),EVENTAVAILABILITY.FACEPRICE)
            when 13 then @CURRENCYSYMBOL + convert(nvarchar(19),EVENTAVAILABILITY.FACEPRICE) + ' (' + convert(nvarchar(10),EVENTAVAILABILITY.CAPACITY) + ')'
                when 14 then substring(convert(varchar(20), EVENT.STARTDATETIMEWITHOFFSET, 9), 13, 5) + ' ' + substring(convert(varchar(30), EVENT.STARTDATETIMEWITHOFFSET, 9), 25, 2)
                when 15 then convert(nvarchar(10),convert(integer,EVENTAVAILABILITY.CAPACITY))
                when 16 then convert(nvarchar(10), EVENT.STARTDATE)
                when 17 then EVENTAVAILABILITY.LOCATION
                when 18 then EVENT.NAME
                else DESCRIPTIONFIELD1
            end BUTTONTEXTLINE1,
            case DESCRIPTIONFIELD2TYPECODE
                when 2 then PROGRAM.NAME
                when 3 then PRICETYPECODE.DESCRIPTION
                when 4 then @CURRENCYSYMBOL + convert(nvarchar(19),EVENTAVAILABILITY.FACEPRICE)
            when 13 then @CURRENCYSYMBOL + convert(nvarchar(19),EVENTAVAILABILITY.FACEPRICE) + ' (' + convert(nvarchar(10),EVENTAVAILABILITY.CAPACITY) + ')'
                when 14 then substring(convert(varchar(20), EVENT.STARTDATETIMEWITHOFFSET, 9), 13, 5) + ' ' + substring(convert(varchar(30), EVENT.STARTDATETIMEWITHOFFSET, 9), 25, 2)
                when 15 then convert(nvarchar(10),convert(integer,EVENTAVAILABILITY.CAPACITY))
                when 16 then convert(nvarchar(10), EVENT.STARTDATE)
                when 17 then EVENTAVAILABILITY.LOCATION
                when 18 then EVENT.NAME
                else DESCRIPTIONFIELD2
            end BUTTONTEXTLINE2,
            case DESCRIPTIONFIELD3TYPECODE
                when 2 then PROGRAM.NAME
                when 3 then PRICETYPECODE.DESCRIPTION
                when 4 then @CURRENCYSYMBOL + convert(nvarchar(19),EVENTAVAILABILITY.FACEPRICE)
            when 13 then @CURRENCYSYMBOL + convert(nvarchar(19),EVENTAVAILABILITY.FACEPRICE) + ' (' + convert(nvarchar(10),EVENTAVAILABILITY.CAPACITY) + ')'
                when 14 then substring(convert(varchar(20), EVENT.STARTDATETIMEWITHOFFSET, 9), 13, 5) + ' ' + substring(convert(varchar(30), EVENT.STARTDATETIMEWITHOFFSET, 9), 25, 2)
                when 15 then convert(nvarchar(10),convert(integer,EVENTAVAILABILITY.CAPACITY))
                when 16 then convert(nvarchar(10), EVENT.STARTDATE)
                when 17 then EVENTAVAILABILITY.LOCATION
                when 18 then EVENT.NAME
                else DESCRIPTIONFIELD3
            end BUTTONTEXTLINE3,
            [EVENT].[STARTTIME] as [EVENTTIME],
            -- It is on sale and it's not sold out
            case EVENTAVAILABILITY.CAPACITY
                when 0 then 0
                else EVENTAVAILABILITY.ISONSALE
            end as ISONSALE,
            [DAILYSALEITEMPROGRAM].[SCHEDULEOPTIONTYPECODE],
            PROGRAM.ID PROGRAMID,
            EVENT.ID EVENTID,
            [PROGRAM].[ISPREREGISTERED],
            PRICETYPECODE.ID PRICETYPECODEID,
            [DAILYSALEITEM].[BUTTONORDER]
        from
            dbo.UFN_DAILYSALEITEM_EVENT_GETAVAILABILITY_2(@CURRENTDATETIMEOFFSET, @SALESMETHODID) as EVENTAVAILABILITY
        inner join
            dbo.[DAILYSALEITEMPROGRAM] with (nolock) on DAILYSALEITEMPROGRAM.ID = EVENTAVAILABILITY.DAILYSALESITEMID
        inner join
            dbo.[DAILYSALEITEM] with (nolock) on [DAILYSALEITEM].[ID] = EVENTAVAILABILITY.DAILYSALESITEMID
        inner join
            dbo.[PROGRAM] with (nolock) on [DAILYSALEITEMPROGRAM].[PROGRAMID] = [PROGRAM].[ID]
        inner join
            dbo.[PRICETYPECODE] with (nolock) on [DAILYSALEITEMPROGRAM].[PRICETYPECODEID] = [PRICETYPECODE].[ID]
        inner join
            dbo.[EVENT] with (nolock) on EVENTAVAILABILITY.EVENTID = [EVENT].ID    
        where 
            [DAILYSALEITEMPROGRAM].[SCHEDULEOPTIONTYPECODE] in (1, 2) and  -- One button for each time, One button for the next available time
            [DAILYSALEITEM].[ISACTIVE] = 1 and
            [PRICETYPECODE].[ACTIVE] = 1 and
            [PROGRAM].[ISACTIVE] = 1

        union all
        select
            [DAILYSALEITEM].[ID] as [QUICKBUTTONID],
            DAILYSALEITEM.TYPECODE,
            dbo.UFN_DAILYSALEITEM_BUILDPROGRAMBUTTONTEXT(DAILYSALEITEM.ID, @CURRENCYSYMBOL) BUTTONTEXTLINE1,
            null as BUTTONTEXTLINE2,
            null as BUTTONTEXTLINE3,
            null as [EVENTTIME],
            ISONSALETEST.ISONSALE,
            3 [SCHEDULEOPTIONTYPECODE],
            PROGRAM.ID PROGRAMID,
            null as EVENTID,
            [PROGRAM].[ISPREREGISTERED],
            PRICETYPECODE.ID PRICETYPECODEID,
            [DAILYSALEITEM].[BUTTONORDER]
        from dbo.[DAILYSALEITEMPROGRAM]
        inner join dbo.[DAILYSALEITEM]
            on [DAILYSALEITEM].[ID] = [DAILYSALEITEMPROGRAM].[ID]
        inner join dbo.[PROGRAM]
            on [DAILYSALEITEMPROGRAM].[PROGRAMID] = [PROGRAM].[ID]
        inner join dbo.[PROGRAMPRICE]
            on [PROGRAM].[ID] = [PROGRAMPRICE].[PROGRAMID] and 
                [DAILYSALEITEMPROGRAM].[PRICETYPECODEID] = [PROGRAMPRICE].[PRICETYPECODEID]
        inner join dbo.[PRICETYPECODE]
            on [DAILYSALEITEMPROGRAM].[PRICETYPECODEID] = [PRICETYPECODE].[ID]
        left outer join
            dbo.SALESMETHODEXCLUDEDPRICETYPE on SALESMETHODEXCLUDEDPRICETYPE.PRICETYPECODEID = DAILYSALEITEMPROGRAM.PRICETYPECODEID and SALESMETHODEXCLUDEDPRICETYPE.SALESMETHODID = @SALESMETHODID
        outer apply (
            -- Bringing this query inline seems to keep from having index scans
            select
                case when exists (
                    select
                        *
                    from
                        dbo.EVENT
                    inner join
                        dbo.EVENTSALESMETHOD on EVENTSALESMETHOD.EVENTID = EVENT.ID and EVENTSALESMETHOD.SALESMETHODID = @SALESMETHODID
                    inner join
                        dbo.PROGRAMSALESMETHOD on PROGRAMSALESMETHOD.PROGRAMID = EVENT.PROGRAMID and PROGRAMSALESMETHOD.SALESMETHODID = @SALESMETHODID
                    where
                        EVENT.PROGRAMID = PROGRAM.ID
                        and EVENTSALESMETHOD.ONSALEDATETIMEWITHOFFSET <= @CURRENTDATETIMEOFFSET
                        and EVENT.STARTDATETIMEWITHOFFSET >
                            case PROGRAMSALESMETHOD.ONSALEENDTYPECODE
                                when 0 then  -- Event start time
                                    @CURRENTDATETIMEOFFSET
                                when 1 then  -- Minutes before start
                                    dateadd(minute, PROGRAMSALESMETHOD.ONSALEENDINTERVAL, @CURRENTDATETIMEOFFSET)
                                when 2 then  -- Hours before start
                                    dateadd(hour, PROGRAMSALESMETHOD.ONSALEENDINTERVAL, @CURRENTDATETIMEOFFSET)
                                when 3 then  -- Minutes after start
                                    dateadd(minute, -PROGRAMSALESMETHOD.ONSALEENDINTERVAL, @CURRENTDATETIMEOFFSET)
                            end
                    ) then 1
                    else 0
                end as ISONSALE
        ) as ISONSALETEST
        where 
            [DAILYSALEITEMPROGRAM].[SCHEDULEOPTIONTYPECODE] = 3 and
            [DAILYSALEITEM].[ISACTIVE] = 1 and
            [PRICETYPECODE].[ACTIVE] = 1 and
            SALESMETHODEXCLUDEDPRICETYPE.PRICETYPECODEID is null and 
            [PROGRAM].[ISACTIVE] = 1
    ) as [SCHEDULEDPROGRAMS]

    -- EVENT REGISTRATION
    insert into @BUTTONTEMPTABLE
    select
        [DAILYSALEITEM].[ID],
        DAILYSALEITEM.TYPECODE,
        dbo.UFN_DAILYSALEITEM_BUILDEVENTREGISTRATIONBUTTONTEXT(DAILYSALEITEM.ID, @CURRENCYSYMBOL),
        1,
        0,
        EVENT.ID,
    DAILYSALEITEMEVENTREGISTRATION.EVENTPRICEID,
        null,
        null,
        null,
        [DAILYSALEITEM].[BUTTONORDER],
        null
    from dbo.[DAILYSALEITEMEVENTREGISTRATION]
    inner join dbo.[DAILYSALEITEM]
        on [DAILYSALEITEM].[ID] = [DAILYSALEITEMEVENTREGISTRATION].[ID]
    inner join dbo.[EVENT]
        on [DAILYSALEITEMEVENTREGISTRATION].[EVENTID] = [EVENT].[ID]
    where
        [DAILYSALEITEM].[ISACTIVE] = 1 and [EVENT].[ISACTIVE] = 1

    -- COMBINATION
    union all
    select
        [DAILYSALEITEM].[ID],
        DAILYSALEITEM.TYPECODE,
        dbo.UFN_DAILYSALEITEM_BUILDCOMBINATIONBUTTONTEXT(DAILYSALEITEM.ID, @CURRENCYSYMBOL),
        1,
        0,
        PRICETYPECODE.ID,
        COMBINATION.ID,
        null,
        null,
        null,
        [DAILYSALEITEM].[BUTTONORDER],
        null
    from dbo.[DAILYSALEITEMCOMBINATION]
    inner join dbo.[DAILYSALEITEM]
        on [DAILYSALEITEM].[ID] = [DAILYSALEITEMCOMBINATION].[ID]
    inner join dbo.[COMBINATION]
        on [DAILYSALEITEMCOMBINATION].[COMBINATIONID] = [COMBINATION].[ID]
    inner join dbo.[COMBINATIONPRICETYPE]
        on [COMBINATION].[ID] = [COMBINATIONPRICETYPE].[COMBINATIONID]
    inner join dbo.[PRICETYPECODE]
        on [COMBINATIONPRICETYPE].[PRICETYPECODEID] = [PRICETYPECODE].[ID] and
            [DAILYSALEITEMCOMBINATION].[PRICETYPECODEID] = [PRICETYPECODE].[ID]
    left outer join
        dbo.SALESMETHODEXCLUDEDPRICETYPE on SALESMETHODEXCLUDEDPRICETYPE.PRICETYPECODEID = [DAILYSALEITEMCOMBINATION].[PRICETYPECODEID] and SALESMETHODEXCLUDEDPRICETYPE.SALESMETHODID = @SALESMETHODID
    where
        [PRICETYPECODE].[ACTIVE] = 1 and
    SALESMETHODEXCLUDEDPRICETYPE.PRICETYPECODEID is null and
        -- Combination level check
        [COMBINATION].[ISACTIVE] = 1 and
        dbo.UFN_COMBINATION_AVAILABLEFORORDER([COMBINATION].[ID], @SALESMETHODID) = 1 and
        dbo.UFN_COMBINATION_ELIGIBLEFORORDER([COMBINATION].[ID], @CONSTITUENTID) = 1 and
        [DAILYSALEITEM].[ISACTIVE] = 1

    -- MEMBERSHIP (User Configured)
    union all
    select
        [DAILYSALEITEM].[ID],
        [DAILYSALEITEM].[TYPECODE],
        dbo.UFN_DAILYSALEITEM_BUILDMEMBERSHIPBUTTONTEXT(DAILYSALEITEM.ID, @CURRENCYSYMBOL),
        1,
        0,
        --4.25.12 Activating pending memberships no longer exists in sales
        '00000000-0000-0000-0000-000000000000' as INITIALMEMBERSHIPID,
        [MEMBERSHIPPROGRAM].[ID],
        [MEMBERSHIPLEVEL].[ID],
        DAILYSALEITEMMEMBERSHIP.MEMBERSHIPLEVELTERMID,
        coalesce(DAILYSALEITEMMEMBERSHIP.MEMBERSHIPLEVELTYPECODEID,'00000000-0000-0000-0000-000000000000'),
        [DAILYSALEITEM].[BUTTONORDER],
        null
    from dbo.[DAILYSALEITEMMEMBERSHIP]
    inner join dbo.[DAILYSALEITEM]
        on [DAILYSALEITEM].[ID] = [DAILYSALEITEMMEMBERSHIP].[ID]
    inner join dbo.[MEMBERSHIPPROGRAM]
        on [DAILYSALEITEMMEMBERSHIP].[MEMBERSHIPPROGRAMID] = [MEMBERSHIPPROGRAM].[ID]
    inner join dbo.[MEMBERSHIPLEVEL]
        on [DAILYSALEITEMMEMBERSHIP].[MEMBERSHIPLEVELID] = [MEMBERSHIPLEVEL].[ID]
    where 
        [DAILYSALEITEM].[ISACTIVE] = 1 and
        [MEMBERSHIPPROGRAM].[ISACTIVE] = 1 and
        [MEMBERSHIPLEVEL].[ISACTIVE] = 1 and
        --Check that the membership program does allow multiple memberships if the member already has an active membership for that program
        not exists (
            select *
            from dbo.[MEMBER]
            inner join dbo.[MEMBERSHIP]
                on [MEMBER].[MEMBERSHIPID] = [MEMBERSHIP].[ID]
            where 
                [MEMBER].[CONSTITUENTID] = @CONSTITUENTID and
                [MEMBERSHIPPROGRAM].[ALLOWMULTIPLEMEMBERSHIPS] = 0 and
                [MEMBERSHIP].[MEMBERSHIPPROGRAMID] = [MEMBERSHIPPROGRAM].[ID] and
                [MEMBER].[ISDROPPED] = 0
                --4.25.12 No existing memberships are supported through the membership buttons these days
                -- and [MEMBERSHIP].[STATUSCODE] in (0,1)
        )

    -- MEMBERSHIP PROMOTION
    union all
    select 
        [DAILYSALEITEM].[ID],
        DAILYSALEITEM.TYPECODE,
        dbo.UFN_DAILYSALEITEM_BUILDMEMBERSHIPPROMOBUTTONTEXT(DAILYSALEITEM.ID, @CURRENCYSYMBOL),
        1,
        0,
        MEMBERSHIPPROMO.ID,
        null,
        null,
        null,
        null,
        [DAILYSALEITEM].[BUTTONORDER],
        null
    from dbo.[DAILYSALEITEMMEMBERSHIPPROMO]
    inner join dbo.[DAILYSALEITEM]
        on [DAILYSALEITEM].[ID] = [DAILYSALEITEMMEMBERSHIPPROMO].[ID]
    inner join dbo.[MEMBERSHIPPROMO]
        on [DAILYSALEITEMMEMBERSHIPPROMO].[MEMBERSHIPPROMOID] = [MEMBERSHIPPROMO].[ID]
    where 
        [DAILYSALEITEM].[ISACTIVE] = 1 and
        [MEMBERSHIPPROMO].[ISACTIVE] = 1

    -- GENERIC
    union all
    select
        null,
        TYPECODE,
        TYPE + '...',
        1,
        1,
        null,
        null,
        null,
        null,
        null,    
        BUTTONORDER,
        null
    from
        dbo.UFN_DAILYSALESGENERICACTION_GETGENERICBUTTONS(0)
    where
        (TYPECODE in (2, 5, 9)) or -- 2: memberships, 5: event registrations, 9: scan tickets
        ((TYPECODE = 6) and exists(select * from dbo.MERCHANDISEPRODUCT where ISACTIVE = 1)) or -- 6: merchandise
        ((TYPECODE = 8) and dbo.UFN_SALESORDER_PATRONHASUPGRADE(@ID) = 1) or -- 8: mid-term upgrade
        (
            (TYPECODE = 3) and -- 3: discounts and promos
            (
                exists(select * from dbo.DISCOUNT where APPLICATIONTYPECODE <> 0 and ISACTIVE = 1) or 
                exists(select * from dbo.MEMBERSHIPPROMO where ISACTIVE = 1)
            )
        ) or
        (
            (TYPECODE = 1) and -- 1: scheduled programs
            exists (
                select *
                from dbo.EVENT
                where 
                    EVENT.PROGRAMID is not null
                    and EVENT.STARTDATETIMEWITHOFFSET >= @CURRENTDATETIMEOFFSET
            )
        )

    -- END POPULATE TEMP TABLE
    set @BUTTONS = (
        select
            QUICKBUTTONID,
            TYPECODE,
            BUTTONTEXT,
            ISENABLED,
            ISGENERIC,
            PARAM1,
            PARAM2,
            PARAM3,
            PARAM4,
            PARAM5
        from @BUTTONTEMPTABLE
        order by BUTTONORDER, BUTTONSUBORDER asc
        for xml raw ('ITEM'), type, elements, root('BUTTONS'), BINARY BASE64
    )

    return 0;