UFN_SMARTQUERY_MEMBERSHIPS

create function dbo.[UFN_SMARTQUERY_MEMBERSHIPS]
(
  @MEMBERSHIPPROGRAMID uniqueidentifier,
  @MEMBERSHIPLEVELID uniqueidentifier,
  @STATUS nvarchar(9),
  @MEMBERSHIPLEVELTYPECODEID uniqueidentifier,
  @TIERCODEID uniqueidentifier,
  @CHECKEXPIRATIONDATE bit,
  @MONTHSTOEXPIRATION int,
  @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(50),
  TIER nvarchar(100),
  TERM nvarchar(50)
)
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;

  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();

  insert into @MEMBERSHIPS
    select top (@MAXROWS)
      [MEMBERSHIP].[ID],
      [MEMBER].[CONSTITUENTID],
      [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 [STATUS],
      [MEMBERSHIPLEVELTYPECODE].[DESCRIPTION] [TYPE],
      [TIERCODE].[DESCRIPTION] [TIER],
      cast (dbo.UFN_MEMBERSHIPLEVELTERMS_PAYMENTOPTIONDESCRIPTION([MEMBERSHIPLEVELTERM].[ID]) as nvarchar(50)) [TERM]
    from 
      dbo.[MEMBERSHIP]
      inner join dbo.[MEMBER] on [MEMBER].[MEMBERSHIPID] = [MEMBERSHIP].[ID] and [MEMBER].[ISDROPPED] = 0
      left join dbo.[CONSTITUENT] on [CONSTITUENT].[ID] = [MEMBER].[CONSTITUENTID]
      left join dbo.[MEMBERSHIPPROGRAM] on [MEMBERSHIPPROGRAM].[ID] = [MEMBERSHIP].[MEMBERSHIPPROGRAMID]
      left 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
      (@MEMBERSHIPPROGRAMID is null or [MEMBERSHIP].[MEMBERSHIPPROGRAMID] = @MEMBERSHIPPROGRAMID) and
      (
        @ISADMIN = 1 or @APPUSER_IN_NONSITEROLE = 1 or (
          (MEMBERSHIPPROGRAM.SITEID is null and @APPUSER_IN_NO_SITE_ROLE = 1) or
          MEMBERSHIPPROGRAM.SITEID in (select SITEID from dbo.UFN_SITESFORUSER(@CURRENTAPPUSERID))
        )
      ) and
      (@MEMBERSHIPLEVELID is null or [MEMBERSHIP].[MEMBERSHIPLEVELID] = @MEMBERSHIPLEVELID) and 
      MEMBER.ISDROPPED = 0 and
      (@STATUS is null or @STATUS in (dbo.[UFN_MEMBERSHIPSTATUS_GETVALUE]([MEMBERSHIP].[ID]))) and 
      (@MEMBERSHIPLEVELTYPECODEID is null or [MEMBERSHIP].[MEMBERSHIPLEVELTYPECODEID] = @MEMBERSHIPLEVELTYPECODEID)  and 
      (@TIERCODEID is null or ([MEMBERSHIP].[MEMBERSHIPLEVELID] in 
        (
          select 
            [MEMBERSHIPLEVEL].[ID]
          from 
            dbo.[MEMBERSHIP] 
            inner join dbo.[MEMBERSHIPLEVEL] on [MEMBERSHIP].[MEMBERSHIPLEVELID] = [MEMBERSHIPLEVEL].[ID]
          where 
            [MEMBERSHIPLEVEL].[TIERCODEID] = @TIERCODEID
        ))) and 
      (@ISADMIN = 1 or @APPUSER_IN_NONRACROLE = 1 or dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, MEMBER.CONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1)                        
      -- LeeCh, 12/31/2009

      and (
          @CHECKEXPIRATIONDATE = 0 or (
              @CHECKEXPIRATIONDATE = 1 and (
                  datediff(m, getdate(), [MEMBERSHIP].[EXPIRATIONDATE]) = @MONTHSTOEXPIRATION
              )
          )
      )
    order by MEMBERNAME, MEMBERSHIPPROGRAMNAME;

  return;
end;