USP_DATALIST_CONSTITUENTMEMBERSHIPPROGRAM_RECENTACTIVITY

                CREATE procedure dbo.USP_DATALIST_CONSTITUENTMEMBERSHIPPROGRAM_RECENTACTIVITY
                (
                    @MEMBERSHIPID uniqueidentifier,
                    @CURRENTAPPUSERID uniqueidentifier,
                    @NUMROWS int = 0
                )
                as
                    set nocount on;

                    -- Activity Type Code List

                    -- 9: Interaction

                    -- 1: Event

                    -- 2: Ticket

                    -- 3: Merchandise

                    -- 4: Plan step

                    -- 5: Completed Sales Order (Ticket)

                    -- 6: Completed Sales Order (Merchandise)

                    -- 7: Group Sale Reservation (Pending)


                    declare @CONSTITUENTS table
                    (
                        ID uniqueidentifier
                    )

                    -- Collect constituents in the membership

                    insert into @CONSTITUENTS
                    select
                        CONSTITUENTID
                    from
                        dbo.MEMBER
                    where
                        MEMBERSHIPID = @MEMBERSHIPID
                        and ISDROPPED = 0;

                    declare @ACTIVITIES table
                    (
                        ID uniqueidentifier,
                        ACTIVITYTYPECODE tinyint,
                        ACTIVITY nvarchar(11),
                        DATE date,
                        DESCRIPTION nvarchar(255)
                    )

                    -- Collect interactions

                    insert into @ACTIVITIES
                    select
                        I.ID,
                        case
                            when I.PROSPECTPLANID is null then 9
                            else 4
                        end as ACTIVITYTYPECODE,
                        'Interaction' as ACTIVITY,
                        DATE,
                        I.OBJECTIVE as DESCRIPTION
                    from
                        dbo.INTERACTION I
                        left join dbo.INTERACTIONSITE IAS on I.ID = IAS.INTERACTIONID
                        left join dbo.PROSPECTPLANSITE PPS on I.PROSPECTPLANID = PPS.PROSPECTPLANID and I.PROSPECTPLANID is not null
                    where
                        CONSTITUENTID in (select ID from @CONSTITUENTS) and
                        (
                            dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or
                            exists
                            (
                                select 1
                                from
                                    dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,'da253487-b13f-42aa-a7d6-f54c4b0a6e28',2)
                                where
                                    SITEID = IAS.SITEID or
                                    SITEID = PPS.SITEID or
                                    (
                                        SITEID is null and
                                        IAS.SITEID is null and
                                        PPS.SITEID is null
                                    )
                            )
                        )
                    order by DATE desc

                    -- Collect attended/will attend events

                    insert into @ACTIVITIES
                    select
                        R.ID,
                        1 as ACTIVITYTYPECODE,
                        'Event' as ACTIVITY,
                        E.STARTDATE as DATE,
                        E.NAME + ' registration' as DESCRIPTION
                    from
                        dbo.REGISTRANT R
                        left join dbo.EVENT E on R.EVENTID = E.ID
                    where
                        CONSTITUENTID in (select ID from @CONSTITUENTS) and
                        dbo.UFN_EVENT_USERHASSITEACCESS(@CURRENTAPPUSERID, E.ID) = 1 and
                        (
                            ATTENDED = 1 or
                            WILLNOTATTEND = 0
                        )
                    order by E.STARTDATE desc

                    if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('BB1C17BC-9E0B-4683-B490-EE40D511FA05') = 1 or
                       dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('f238e8fe-06ae-4fdc-beaf-fdf6637e1982') = 1
                    begin
                        -- Collect ticketing

                        insert into @ACTIVITIES
                        select distinct
                            SO.ID,
                            case
                                when SO.STATUSCODE = 0 and R.ARRIVALDATE is not null then 7
                                when SO.STATUSCODE = 1 then 5
                                else 2
                            end as ACTIVITYTYPECODE,
                            'Tickets' as ACTIVITY,
                            case
                                when SO.SALESMETHODTYPECODE = 3 then R.ARRIVALDATE
                                else SO.TRANSACTIONDATE
                            end as DATE,
                            P.NAME as DESCRIPTION
                        from
                            dbo.SALESORDER SO
                            inner join dbo.SALESORDERITEM SOI on SOI.SALESORDERID = SO.ID
                            inner join dbo.SALESORDERITEMTICKET SOIT on SOIT.ID = SOI.ID
                            left join dbo.EVENT E on E.ID = SOIT.EVENTID
                            inner join dbo.PROGRAM P on P.ID = E.PROGRAMID or P.ID = SOIT.PROGRAMID
                            left join dbo.RESERVATION R on SO.ID = R.ID
                        where
                            SO.RECIPIENTID in (select ID from @CONSTITUENTS)
                        order by case when SO.SALESMETHODTYPECODE = 3 then R.ARRIVALDATE else SO.TRANSACTIONDATE end desc

                        -- Collect merchandise

                        insert into @ACTIVITIES
                        select distinct
                            case
                                when SO.STATUSCODE = 1 then SO.ID
                                else P.ID
                            end as ID,
                            case
                                when SO.STATUSCODE = 1 then 6
                                else 3
                            end as ACTIVITYTYPECODE,
                            'Merchandise' as ACTIVITY,
                            SO.TRANSACTIONDATE as DATE,
                            SOI.DESCRIPTION as DESCRIPTION
                        from
                            dbo.SALESORDER SO
                            inner join dbo.SALESORDERITEM SOI on SO.ID = SOI.SALESORDERID
                            inner join dbo.SALESORDERITEMMERCHANDISE SOIM on SOI.ID = SOIM.ID
                            inner join dbo.MERCHANDISEPRODUCTINSTANCE MPI on SOIM.MERCHANDISEPRODUCTINSTANCEID = MPI.ID
                            inner join dbo.PRODUCT P on MPI.MERCHANDISEPRODUCTID = P.ID
                        where
                            SO.CONSTITUENTID in (select ID from @CONSTITUENTS) and
                            SOI.TYPECODE = 14
                        order by SO.TRANSACTIONDATE desc

                    end

                    if @NUMROWS = 0
                    begin
                        -- Select all

                        select 
                            ID,
                            ACTIVITYTYPECODE,
                            ACTIVITY,
                            DATE,
                            DESCRIPTION
                        from @ACTIVITIES
                        order by DATE desc
                    end
                    else
                    begin
                        -- Select some

                        select top(@NUMROWS)
                            ID,
                            ACTIVITYTYPECODE,
                            ACTIVITY,
                            DATE,
                            DESCRIPTION
                        from @ACTIVITIES
                        order by DATE desc
                    end