UFN_SMARTQUERY_MEMBERS

create function dbo.UFN_SMARTQUERY_MEMBERS
(
  @MEMBERSHIPPROGRAMID uniqueidentifier,
  @MEMBERSHIPLEVELID uniqueidentifier,
  @STATUS nvarchar(9),
  @MEMBERSHIPLEVELTYPECODEID uniqueidentifier,
  @TIERCODEID uniqueidentifier,
  @CHECKEXPIRATIONDATE bit,
  @MONTHSTOEXPIRATION int,
  @CURRENTAPPUSERID uniqueidentifier = null,           
  @MAXROWS int
)
returns @T table 
(
  ID uniqueidentifier,    
  MEMBERSHIPID uniqueidentifier,
  MEMBERNAME nvarchar(154), 
  MEMBERSHIPPROGRAMNAME nvarchar(100),
  MEMBERSHIPLEVELNAME nvarchar(100),
  EXPIRATIONDATE datetime,
  STATUS nvarchar(9),
  TYPE nvarchar(50),
  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;

  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 @T
    select top (@MAXROWS)
      MEMBER.CONSTITUENTID as ID,
      MEMBERSHIP.ID,
      [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,
      dbo.UFN_MEMBERSHIPSTATUS_GETVALUE(MEMBERSHIP.ID) as [STATUS],
      [MEMBERSHIPLEVELTYPECODE].[DESCRIPTION] [TYPE],
      [TIERCODE].[DESCRIPTION] [TIER],
      [MEMBERSHIPLEVELTERM].[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 (@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)
      and (
          @CHECKEXPIRATIONDATE = 0 or (
  @CHECKEXPIRATIONDATE = 1 and (
                  datediff(m, getdate(), [MEMBERSHIP].[EXPIRATIONDATE]) = @MONTHSTOEXPIRATION
              )
          )
      )
    order by MEMBERNAME, MEMBERSHIPPROGRAMNAME;

  return;
end;