UFN_QUERY_RECOGNITIONPROGRAMMEMBERS

CREATE function dbo.UFN_QUERY_RECOGNITIONPROGRAMMEMBERS
(
  @RECOGNITIONPROGRAMID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier = null
)
returns @RETURN table
(
  CONSTITUENTID uniqueidentifier,
  CONSTITUENTRECOGNITIONID uniqueidentifier,
  CONSTITUENTNAME nvarchar(154),
  NICKNAME nvarchar(50),
  LOOKUPID nvarchar(100),
  TYPECODE tinyint,
  RECOGNITIONLEVEL nvarchar(100),
  DATEACHIEVED date,
  TOTALAMOUNT money,
  AMOUNTTONEXTLEVEL money,
  STATUSCODE tinyint,
  STATUS nvarchar(100),
  GIVESANONYMOUSLY bit,
  ISDECEASED bit,
  BASECURRENCYID uniqueidentifier
) as
begin
  declare @CURRENTDATE datetime = getdate();

  with PROGRAMRECOGNITIONS as 
  (
    select 
      CONSTITUENTID,
      case
        when max(EXPIRATIONDATE) is null or max(EXPIRATIONDATE) >= @CURRENTDATE then 0
        else 1
      end [EXPIRED]
    from dbo.CONSTITUENTRECOGNITION
    where CONSTITUENTRECOGNITION.RECOGNITIONPROGRAMID = @RECOGNITIONPROGRAMID
    group by CONSTITUENTID
  ),
  RECOGNITIONLEVELS as
  (
    select 
      CONSTITUENTRECOGNITION.ID,
      CONSTITUENTRECOGNITION.JOINDATE,
      case
        when CONSTITUENTRECOGNITION.EXPIRATIONDATE is null or CONSTITUENTRECOGNITION.EXPIRATIONDATE >= @CURRENTDATE then 0
        else 1
      end [EXPIRED],
      CONSTITUENTRECOGNITION.CONSTITUENTID,
      CONSTITUENTRECOGNITION.STATUSCODE,
      RECOGNITIONLEVEL.AMOUNT as RECOGNITIONLEVELAMOUNT
    from dbo.CONSTITUENTRECOGNITION
      inner join dbo.RECOGNITIONLEVEL on RECOGNITIONLEVEL.ID = CONSTITUENTRECOGNITION.RECOGNITIONLEVELID
    where CONSTITUENTRECOGNITION.RECOGNITIONPROGRAMID = @RECOGNITIONPROGRAMID
  ),
  CURRENTRECOGNITIONPROGRAM as
  (
    select 
      TOPLEVEL.ID,
      PROGRAMRECOGNITIONS.EXPIRED,
      PROGRAMRECOGNITIONS.CONSTITUENTID
    from PROGRAMRECOGNITIONS
      cross apply 
      (
        select top 1 RECOGNITIONLEVELS.ID
        from RECOGNITIONLEVELS
        where
          PROGRAMRECOGNITIONS.CONSTITUENTID = RECOGNITIONLEVELS.CONSTITUENTID and
          (
            (
              PROGRAMRECOGNITIONS.EXPIRED = 0 and
              RECOGNITIONLEVELS.EXPIRED = 0
            ) or
            PROGRAMRECOGNITIONS.EXPIRED = 1
          )
        order by
          case STATUSCODE
            when 3 then 1 -- List 'Inactive (Merge)' last

            else 0
          end asc,
          RECOGNITIONLEVELS.JOINDATE desc, RECOGNITIONLEVELS.RECOGNITIONLEVELAMOUNT desc
      ) TOPLEVEL
  )
  insert into @RETURN
  select
    CONSTITUENTRECOGNITION.CONSTITUENTID,
    CONSTITUENTRECOGNITION.ID as CONSTITUENTRECOGNITIONID,
    NF.NAME as CONSTITUENTNAME,
    CONSTITUENT.NICKNAME,
    CONSTITUENT.LOOKUPID,
    RECOGNITIONPROGRAM.TYPECODE,
    RECOGNITIONLEVEL.NAME as RECOGNITIONLEVEL,
    CONSTITUENTRECOGNITION.JOINDATE as DATEACHIEVED,
    CONSTITUENTRECOGNITION.TOTALAMOUNT,
    case
      when RECOGNITIONLEVEL.AMOUNT <> (select MAX(AMOUNT) from dbo.RECOGNITIONLEVEL where RECOGNITIONPROGRAMID = RECOGNITIONPROGRAM.ID) then
        case
          -- Planned gift only

          when RECOGNITIONPROGRAM.REVENUECODE = 1 then
          (
            select min(PLANNEDGIFTAMOUNT)
            from dbo.RECOGNITIONLEVEL RLN
            where
              RLN.RECOGNITIONPROGRAMID = CONSTITUENTRECOGNITION.RECOGNITIONPROGRAMID
              and RLN.PLANNEDGIFTAMOUNT > CONSTITUENTRECOGNITION.TOTALPLANNEDGIFTAMOUNT
              and RLN.ISACTIVE = 1
          ) - CONSTITUENTRECOGNITION.TOTALPLANNEDGIFTAMOUNT
          else
          (
            select min(AMOUNT)
            from dbo.RECOGNITIONLEVEL RLN
            where
              RLN.RECOGNITIONPROGRAMID = CONSTITUENTRECOGNITION.RECOGNITIONPROGRAMID
              and AMOUNT > CONSTITUENTRECOGNITION.TOTALAMOUNT
              and RLN.ISACTIVE = 1
          ) - CONSTITUENTRECOGNITION.TOTALAMOUNT
        end
      else null
    end as AMOUNTTONEXTLEVEL,
    CONSTITUENTRECOGNITION.STATUSCODE,
    case
      when (CONSTITUENTRECOGNITION.STATUSCODE = 0 and CURRENTRECOGNITIONPROGRAM.EXPIRED = 1) then 'Lapsed'
      else CONSTITUENTRECOGNITION.STATUS
    end as STATUS,
    CONSTITUENT.GIVESANONYMOUSLY,
    case
      when exists (select top 1 ID from dbo.DECEASEDCONSTITUENT where ID = CONSTITUENT.ID) then cast(1 as bit)
      else cast(0 as bit)
    end as ISDECEASED,
    CONSTITUENTRECOGNITION.BASECURRENCYID
  from CURRENTRECOGNITIONPROGRAM
    inner join dbo.CONSTITUENTRECOGNITION on CONSTITUENTRECOGNITION.ID = CURRENTRECOGNITIONPROGRAM.ID
    inner join dbo.CONSTITUENT on CONSTITUENTRECOGNITION.CONSTITUENTID = CONSTITUENT.ID
    inner join dbo.RECOGNITIONPROGRAM on CONSTITUENTRECOGNITION.RECOGNITIONPROGRAMID  = RECOGNITIONPROGRAM.ID
    inner join dbo.RECOGNITIONLEVEL on RECOGNITIONLEVEL.ID = CONSTITUENTRECOGNITION.RECOGNITIONLEVELID
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF;

  return;
end