UFN_SMARTQUERY_MEMBERSHIPMAILINGPREFERENCE_2

create function dbo.[UFN_SMARTQUERY_MEMBERSHIPMAILINGPREFERENCE_2]
(
  @MEMBERSHIPPROGRAMID uniqueidentifier,
  @MAILINGPREFERENCE int,
  @INCLUDENOPREFERENCE bit,
  @EXCLUDEPREFERENCE int = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @MAXROWS int
)
returns @MEMBERSHIPS table
(
  ID uniqueidentifier,
  CONSTITUENTID uniqueidentifier,
  MEMBERNAME nvarchar(154),
  MEMBERSHIPPROGRAMNAME nvarchar(100),
  MEMBERSHIPLEVELNAME nvarchar(100),
  EXPIRATIONDATE datetime,
  STATUS nvarchar(9),
  TYPE nvarchar(100),
  TIER nvarchar(100),
  TERM nvarchar(8)
)
as
begin

  declare @ISADMIN bit;
  declare @APPUSER_IN_NONRACROLE bit;
  declare @APPUSER_IN_NOSECGROUPROLE bit;
  declare @APPUSER_IN_NONSITEROLE bit;
  declare @APPUSER_IN_NO_SITE_ROLE bit;
  declare @TODAY datetime;
  declare @MEMBERSHIPAPPEALCATEGORYID uniqueidentifier;

  set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
  set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
  set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);
  set @APPUSER_IN_NONSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NONSITEROLE(@CURRENTAPPUSERID);
  set @APPUSER_IN_NO_SITE_ROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SITE_ROLE(@CURRENTAPPUSERID);
  set @TODAY = getdate();
  set @MEMBERSHIPAPPEALCATEGORYID = dbo.UFN_APPEALCATEGORYCODE_GETID('Membership');

  insert into @MEMBERSHIPS
    --Repeating the subquery name to emphasize that the "outer apply"s only provide filter criteria (not data to output).

    select top (@MAXROWS)
      MEMBERSHIPSUBQUERY.[ID],
      MEMBERSHIPSUBQUERY.[MEMBERCONSTITUENTID],
      MEMBERSHIPSUBQUERY.[MEMBERNAME],
      MEMBERSHIPSUBQUERY.[MEMBERSHIPPROGRAMNAME],
      MEMBERSHIPSUBQUERY.[MEMBERSHIPLEVELNAME],
      MEMBERSHIPSUBQUERY.EXPIRATIONDATE,
      MEMBERSHIPSUBQUERY.[STATUS],
      MEMBERSHIPSUBQUERY.[TYPE],
      MEMBERSHIPSUBQUERY.[TIER],
      MEMBERSHIPSUBQUERY.[TERM]
    from
    (
      select
        [MEMBERSHIP].[ID],
        [MEMBER].[CONSTITUENTID] [MEMBERCONSTITUENTID],
        [CONSTITUENT].[ID] [RECIPIENTCONSTITUENTID],  -- Same as [MEMBERCONSTITUENTID] in this select clause

        [CONSTITUENT].[NAME] [MEMBERNAME],
        [MEMBERSHIPPROGRAM].[NAME] [MEMBERSHIPPROGRAMNAME],
        [MEMBERSHIPLEVEL].[NAME] [MEMBERSHIPLEVELNAME],
        --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

        cast([MEMBERSHIP].[EXPIRATIONDATE] as date) as EXPIRATIONDATE,
        MEMBERSHIP.STATUS as [STATUS],
        [MEMBERSHIPLEVELTYPECODE].[DESCRIPTION] [TYPE],
        [TIERCODE].[DESCRIPTION] [TIER],
        [MEMBERSHIPLEVELTERM].[TERM],
        [MEMBERSHIPPROGRAM].[ID] [MEMBERSHIPPROGRAMID],
        [MEMBERSHIPPROGRAM].[SITEID] [SITEID],
        [MEMBERSHIP].STATUSCODE
      from
        dbo.[MEMBERSHIP]
        inner join dbo.[MEMBER] on 
          [MEMBER].[MEMBERSHIPID] = [MEMBERSHIP].[ID] and 
          [MEMBER].[ISPRIMARY] = 1 and
          [MEMBER].[ISDROPPED] = 0
        inner join dbo.[CONSTITUENT] on [MEMBER].[CONSTITUENTID] = [CONSTITUENT].[ID] -- Membership recipient's constituentID

        inner join dbo.[MEMBERSHIPPROGRAM] on [MEMBERSHIPPROGRAM].[ID] = [MEMBERSHIP].[MEMBERSHIPPROGRAMID]
        inner join dbo.[MEMBERSHIPLEVEL] on [MEMBERSHIPLEVEL].[ID] = [MEMBERSHIP].[MEMBERSHIPLEVELID]
        left join dbo.[MEMBERSHIPLEVELTYPECODE] on [MEMBERSHIPLEVELTYPECODE].[ID] = [MEMBERSHIP].[MEMBERSHIPLEVELTYPECODEID]
        left join dbo.[TIERCODE] on [TIERCODE].[ID] = [MEMBERSHIPLEVEL].[TIERCODEID]
        left join dbo.[MEMBERSHIPLEVELTERM] on [MEMBERSHIPLEVELTERM].[ID] = [MEMBERSHIP].[MEMBERSHIPLEVELTERMID]
      where
        [MEMBERSHIP].[SENDRENEWALCODE] in (1,2)

      union    -- Changed to a union to remove a conditional inner join to CONSTITUENT.ID and prevent query timeout.


      select
       [MEMBERSHIP].[ID],
        [MEMBER].[CONSTITUENTID] [MEMBERCONSTITUENTID],
        [CONSTITUENT].[ID] [RECIPIENTCONSTITUENTID],  -- Constituent who gave the membership

        [CONSTITUENT].[NAME] [MEMBERNAME],
        [MEMBERSHIPPROGRAM].[NAME] [MEMBERSHIPPROGRAMNAME],
        [MEMBERSHIPLEVEL].[NAME] [MEMBERSHIPLEVELNAME],
        --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

        cast([MEMBERSHIP].[EXPIRATIONDATE] as date) as EXPIRATIONDATE,
        MEMBERSHIP.STATUS as [STATUS],
        [MEMBERSHIPLEVELTYPECODE].[DESCRIPTION] [TYPE],
        [TIERCODE].[DESCRIPTION] [TIER],
        [MEMBERSHIPLEVELTERM].[TERM],
        [MEMBERSHIPPROGRAM].[ID] [MEMBERSHIPPROGRAMID],
        [MEMBERSHIPPROGRAM].[SITEID] [SITEID],
        [MEMBERSHIP].[STATUSCODE]
      from
        dbo.[MEMBERSHIP]
        inner join dbo.[MEMBER] on 
          [MEMBER].[MEMBERSHIPID] = [MEMBERSHIP].[ID] and 
          [MEMBER].[ISPRIMARY] = 1 and
          [MEMBER].[ISDROPPED] = 0
        inner join dbo.[CONSTITUENT] on [MEMBERSHIP].[GIVENBYID] = [CONSTITUENT].[ID] -- Membership giver's constituentID

        inner join dbo.[MEMBERSHIPPROGRAM] on [MEMBERSHIPPROGRAM].[ID] = [MEMBERSHIP].[MEMBERSHIPPROGRAMID]
        inner join dbo.[MEMBERSHIPLEVEL] on [MEMBERSHIPLEVEL].[ID] = [MEMBERSHIP].[MEMBERSHIPLEVELID]
        left join dbo.[MEMBERSHIPLEVELTYPECODE] on [MEMBERSHIPLEVELTYPECODE].[ID] = [MEMBERSHIP].[MEMBERSHIPLEVELTYPECODEID]
        left join dbo.[TIERCODE] on [TIERCODE].[ID] = [MEMBERSHIPLEVEL].[TIERCODEID]
        left join dbo.[MEMBERSHIPLEVELTERM] on [MEMBERSHIPLEVELTERM].[ID] = [MEMBERSHIP].[MEMBERSHIPLEVELTERMID]
      where
        [MEMBERSHIP].[SENDRENEWALCODE] in (0,2)
    ) as MEMBERSHIPSUBQUERY
    outer apply (
      select top 1
        MP.ID,
        MP.SENDMAIL,
        MP.DELIVERYMETHODCODE,
        MP.DONOTSENDOTHERCHANNEL
      from dbo.MAILPREFERENCE MP
      where 
        MP.MAILTYPECODE = 1 and -- appeal

        (
          MP.CATEGORYCODEID = @MEMBERSHIPAPPEALCATEGORYID or
          MP.CATEGORYCODEID is null
        ) and
        MP.CONSTITUENTID = [RECIPIENTCONSTITUENTID]
      order by MP.CATEGORYCODEID desc --Rule with Membership category rules beats rule without

    ) as [PREFERENCE]
    outer apply (
      select
        case 
          when
            [PREFERENCE].DELIVERYMETHODCODE = 0 and 
            exists (
              select [ADDRESS].ID 
              from dbo.[ADDRESS] 
              where 
                [ADDRESS].CONSTITUENTID = [RECIPIENTCONSTITUENTID] and 
                [ADDRESS].DONOTMAIL = 0 
            )
              then 1
          when 
            [PREFERENCE].DELIVERYMETHODCODE = 1 and 
            exists (
              select EMAILADDRESS.ID 
              from dbo.EMAILADDRESS 
              where 
                EMAILADDRESS.CONSTITUENTID = [RECIPIENTCONSTITUENTID] and 
                EMAILADDRESS.DONOTEMAIL = 0
            )
              then 1
          else 0
        end [HAS]
    ) as [PREFERENCECONTACTINFORMATION]
    where
      (@MEMBERSHIPPROGRAMID is null or [MEMBERSHIPPROGRAMID] = @MEMBERSHIPPROGRAMID) and
      (
        @ISADMIN = 1 or 
        @APPUSER_IN_NONSITEROLE = 1 or 
        (
          (SITEID is null and @APPUSER_IN_NO_SITE_ROLE = 1) or
          SITEID in (select SITEID from dbo.UFN_SITESFORUSER(@CURRENTAPPUSERID))
        )
      ) and
      (STATUSCODE not in (1, 2) ) and
      (
        @ISADMIN = 1 or 
        @APPUSER_IN_NONRACROLE = 1 or 
        dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, [RECIPIENTCONSTITUENTID], @APPUSER_IN_NOSECGROUPROLE) = 1
      ) and
      --Make sure they aren't refusing communication

      (
        [PREFERENCE].[ID] is null or
        [PREFERENCE].[SENDMAIL] = 1
      ) and
      (
        -- Check "Do not mail" or "Do not email" of constituent's maining address or email address

        (
          @MAILINGPREFERENCE is null or
          (
            (
              [PREFERENCE].[DELIVERYMETHODCODE] <> @MAILINGPREFERENCE or
              [PREFERENCE].[ID] is null
            ) and
            (
              exists (
                select [ADDRESS].ID 
                from dbo.[ADDRESS] 
                where 
                  [ADDRESS].CONSTITUENTID = [RECIPIENTCONSTITUENTID] and 
                  [ADDRESS].DONOTMAIL = 0 and 
                  @MAILINGPREFERENCE = 0
              ) or
              exists (
                select EMAILADDRESS.ID 
                from dbo.EMAILADDRESS 
                where 
                  EMAILADDRESS.CONSTITUENTID = [RECIPIENTCONSTITUENTID] and 
                  EMAILADDRESS.DONOTEMAIL = 0 and 
                  @MAILINGPREFERENCE = 1
              )
            )
          ) or
          (--Already did this check 

            [PREFERENCE].[DELIVERYMETHODCODE] = @MAILINGPREFERENCE and 
            [PREFERENCECONTACTINFORMATION].[HAS] = 1
          )
        ) and
        --Check exclude preference

        (
          @EXCLUDEPREFERENCE is null or
          [PREFERENCE].ID is null or
          not (
            [PREFERENCE].DELIVERYMETHODCODE <> @EXCLUDEPREFERENCE and --If the constituent hasn't chosen this mailing preference

            [PREFERENCE].DONOTSENDOTHERCHANNEL = 1 --they have denied it here

          )
        ) and
        -- Check communication preference

        (
          @MAILINGPREFERENCE is null or
          (
            [PREFERENCE].DELIVERYMETHODCODE = @MAILINGPREFERENCE and
            [PREFERENCE].SENDMAIL = 1
          ) or
          -- No preference

          (
            @INCLUDENOPREFERENCE = 1 and
            --Doesn't have a "Don't send preference"

            [PREFERENCE].ID is null
          ) or
          --Constituent has the opposite mailing preference, but doesn't have contact information for their preference

          --Treating them here as if they don't have a preference

          --There is a check above to see that the constituent has contact information for the selected mailing preference

          (
            @INCLUDENOPREFERENCE = 1 and
            [PREFERENCE].DELIVERYMETHODCODE <> @MAILINGPREFERENCE and
            [PREFERENCECONTACTINFORMATION].[HAS] = 0 and
            [PREFERENCE].DONOTSENDOTHERCHANNEL = 0 and
            [PREFERENCE].SENDMAIL = 1
          )
        )
      )
    order by MEMBERNAME, MEMBERSHIPPROGRAMNAME;

  return;
end;