USP_DATALIST_DONORCHALLENGEMEMBERSHIPS

CREATE procedure dbo.USP_DATALIST_DONORCHALLENGEMEMBERSHIPS
(
  @ID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier
)
as                 
  set nocount on;

  declare @DONORCHALLENGEBASECURRENCYID uniqueidentifier;
  select @DONORCHALLENGEBASECURRENCYID = BASECURRENCYID from dbo.DONORCHALLENGE where ID = @ID;

  with DESIGNATIONAMOUNTS_CTE as
  (
    --Note this shows all funds given to a membership level regardless of if they were all mapped to the same designation    

    select
      MT.MEMBERSHIPLEVELID,
      DCE.STATUSTYPECODE,
      coalesce(sum(
        case
            when @DONORCHALLENGEBASECURRENCYID = RS.TRANSACTIONCURRENCYID
                then RS.TRANSACTIONAMOUNT
            else dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(RS.ID, @DONORCHALLENGEBASECURRENCYID)
        end 
      ),0) GIVENAMOUNT,
      coalesce(sum(DCE.AMOUNT),0) CHALLENGEAMOUNT
    from
      dbo.DONORCHALLENGEENCUMBERED DCE
    inner join
      dbo.REVENUESPLIT RS on RS.ID = DCE.REVENUESPLITID
    inner join
      dbo.MEMBERSHIPTRANSACTION MT on MT.REVENUESPLITID = RS.ID
    where
      DCE.DONORCHALLENGEID = @ID
      and DCE.STATUSTYPECODE in (0,1)
    group by
      MT.MEMBERSHIPLEVELID, DCE.STATUSTYPECODE
  )
  select
    DCMM.ID,
    MP.ID as MEMBERSHIPPROGRAMID,
    MP.NAME as MEMBERSHIPPROGRAMNAME,
    ML.NAME as MEMBERSHIPLEVELNAME,
    (select coalesce(sum(GIVENAMOUNT),0) from DESIGNATIONAMOUNTS_CTE where DESIGNATIONAMOUNTS_CTE.MEMBERSHIPLEVELID = DCMM.MEMBERSHIPLEVELID) as AMOUNTGIVEN,
    (select coalesce(sum(CHALLENGEAMOUNT),0) from DESIGNATIONAMOUNTS_CTE where DESIGNATIONAMOUNTS_CTE.MEMBERSHIPLEVELID = DCMM.MEMBERSHIPLEVELID and STATUSTYPECODE = 0) as AMOUNTENCUMBERED,
    (select coalesce(sum(CHALLENGEAMOUNT),0) from DESIGNATIONAMOUNTS_CTE where DESIGNATIONAMOUNTS_CTE.MEMBERSHIPLEVELID = DCMM.MEMBERSHIPLEVELID and STATUSTYPECODE = 1) as AMOUNTMATCHED,
    DCMM.MATCHINGDESIGNATIONID,
    dbo.UFN_DESIGNATION_BUILDNAME(DCMM.MATCHINGDESIGNATIONID) as MATCHINGDESIGNATIONNAME,
    MD.USERID as MATCHINGDESIGNATIONLOOKUPID,
    @DONORCHALLENGEBASECURRENCYID
  from
    dbo.DONORCHALLENGEMEMBERSHIPLEVELMAP DCMM
  inner join
    dbo.MEMBERSHIPLEVEL ML on ML.ID = DCMM.MEMBERSHIPLEVELID
  inner join
    dbo.MEMBERSHIPPROGRAM MP on MP.ID = ML.MEMBERSHIPPROGRAMID
  left join
    dbo.DESIGNATION MD on MD.ID = DCMM.MATCHINGDESIGNATIONID
  where
    DONORCHALLENGEID = @ID
  order by
    MEMBERSHIPPROGRAMNAME, MEMBERSHIPLEVELNAME