USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTMEMBERSHIPPROGRAMPAGEDATA

                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTMEMBERSHIPPROGRAMPAGEDATA
                (
                  @ID uniqueidentifier,
                  @DATALOADED bit = 0 output,
                  @MEMBERSHIPPROGRAMID uniqueidentifier=null output,
                  @CONSTITUENTID uniqueidentifier=null output,
                  @CONSTITUENTNAME nvarchar(700)=null output,
                  @PRIMARYMEMBERID uniqueidentifier=null output,
                  @MEMBERSHIPPROGRAMNAME nvarchar(100)=null output,
                  @MEMBERSHIPPROGRAMCAPTION nvarchar(254) = null output,
                  @MEMBERSHIPID uniqueidentifier = null output,
                  @MEMBERSHIPLOOKUPID nvarchar(100) = null output,
                  @BENEFITSANDSENTITEMSCOUNT int = null output,
                  @RECENTMEMBERACTIVITYCOUNT int = null output,
                  @CURRENTAPPUSERID uniqueidentifier = null
                )
                as
                  set nocount on;

                  -- be sure to set this, in case the select returns no rows
                  set @DATALOADED = 0;

                  declare @MEMBERSHIPLEVELTYPE nvarchar(100) = null;

                  select @DATALOADED = 1,
                    @MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID,
                    @CONSTITUENTID  = MEMBER.CONSTITUENTID,
                    @CONSTITUENTNAME = NF.NAME,
                    @PRIMARYMEMBERID = MEMBER.ID,
                    @MEMBERSHIPPROGRAMNAME=dbo.UFN_MEMBERSHIPPROGRAM_GETNAME(@MEMBERSHIPPROGRAMID),
                    @MEMBERSHIPID  = @ID,
                    @MEMBERSHIPLEVELTYPE = MT.DESCRIPTION,
                    @MEMBERSHIPLOOKUPID = MEMBERSHIP.LOOKUPID
                  from dbo.MEMBERSHIP
                    inner join dbo.MEMBER on MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
                    inner join dbo.MEMBERSHIPPROGRAM on MEMBERSHIP.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(MEMBER.CONSTITUENTID) NF
                    left join dbo.MEMBERSHIPLEVELTYPECODE MT
                    on MEMBERSHIP.MEMBERSHIPLEVELTYPECODEID = MT.ID
                  where MEMBERSHIP.ID = @ID and ISPRIMARY = 1;

                  set @MEMBERSHIPPROGRAMCAPTION = case when (@MEMBERSHIPLEVELTYPE is null or @MEMBERSHIPLEVELTYPE = '') then @MEMBERSHIPPROGRAMNAME else (@MEMBERSHIPPROGRAMNAME + ' - ' + @MEMBERSHIPLEVELTYPE) end;

                  -- count benefits
                  select @BENEFITSANDSENTITEMSCOUNT = count(BENEFIT.ID)
                  from dbo.MEMBERSHIPTRANSACTION
                    inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID=MEMBERSHIPLEVEL.ID
                    inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELTERMID=MEMBERSHIPLEVELTERM.ID
                    inner join dbo.REVENUESPLIT_EXT on MEMBERSHIPTRANSACTION.REVENUESPLITID = REVENUESPLIT_EXT.ID
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.ID = REVENUESPLIT_EXT.ID
                    inner join dbo.FINANCIALTRANSACTION FT on FT.ID = FTLI.FINANCIALTRANSACTIONID
                    inner join dbo.REVENUE_EXT on FT.ID = REVENUE_EXT.ID
                    inner join dbo.REVENUEBENEFIT on REVENUE_EXT.ID = REVENUEBENEFIT.REVENUEID
                    inner join dbo.BENEFIT on REVENUEBENEFIT.BENEFITID = BENEFIT.ID
                  where
                    MEMBERSHIPTRANSACTION.MEMBERSHIPID = @MEMBERSHIPID
                    and FTLI.TYPECODE <> 1
                    and FT.DELETEDON is null
                    and FTLI.DELETEDON is null;

                  -- count sent items
                  select @BENEFITSANDSENTITEMSCOUNT = @BENEFITSANDSENTITEMSCOUNT + count(CONSTITUENTAPPEAL.ID)
                  from (select CONSTITUENTID from dbo.MEMBER where MEMBERSHIPID = @MEMBERSHIPID) MEMBERS
                    inner join dbo.CONSTITUENTAPPEAL on CONSTITUENTAPPEAL.CONSTITUENTID = MEMBERS.CONSTITUENTID
                 inner join dbo.APPEAL on APPEAL.ID = CONSTITUENTAPPEAL.APPEALID
                  where APPEAL.MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID;

                  declare @CONSTITUENTS table (ID uniqueidentifier);

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

                  -- count interactions
                  select @RECENTMEMBERACTIVITYCOUNT = count(I.ID)
                  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
                          )
                      )
                    );

                  -- Collect attended/will attend events
                  select @RECENTMEMBERACTIVITYCOUNT = @RECENTMEMBERACTIVITYCOUNT + count(R.ID)
                  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);

                  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 and merchandise
                    select @RECENTMEMBERACTIVITYCOUNT += count(SO.ID)
                    from (
                        select SALESORDER.ID, SALESORDER.RECIPIENTID as CONSTITUENTID, SALESORDER.STATUSCODE
                        from dbo.SALESORDER
                        inner join dbo.SALESORDERITEM on SALESORDERITEM.SALESORDERID = SALESORDER.ID and SALESORDERITEM.TYPECODE = 0  -- Ticket

                        union all
                        select SALESORDER.ID, SALESORDER.CONSTITUENTID, SALESORDER.STATUSCODE
                        from dbo.SALESORDER
                        inner join dbo.SALESORDERITEM on SALESORDERITEM.SALESORDERID = SALESORDER.ID and SALESORDERITEM.TYPECODE = 14  -- Merchandise
                    ) as SO
                    where
                        SO.CONSTITUENTID in (select ID from @CONSTITUENTS)
                        and SO.STATUSCODE = 1  -- Complete
                  end;

                  return 0;