USP_DATALIST_PROSPECT_CONSTITUENTGROUPPLANNEDGIFTS

CREATE procedure dbo.USP_DATALIST_PROSPECT_CONSTITUENTGROUPPLANNEDGIFTS
(
  @PROSPECTID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null,
  @CURRENCYCODE tinyint = 2,
  @CAMPAIGNFILTERMODE tinyint = 0,
  @CAMPAIGNSSELECTED xml = null
)
as
begin

  set nocount on;

  -------------------------------------

  --CAMPAIGNSSELECTED

  -------------------------------------

  declare @CAMPAIGNFILTERTABLE table
  (
    ID uniqueidentifier
  );

  if @CAMPAIGNFILTERMODE != 0
  begin
    insert into
      @CAMPAIGNFILTERTABLE
    select
      T.c.value('(ID)[1]','uniqueidentifier')
    from
      @CAMPAIGNSSELECTED.nodes('/CAMPAIGNSSELECTED/ITEM') T(c);
  end

  -------------------------------------

  --MULTICURRENCY

  -------------------------------------

  declare @MULTICURRENCYENABLED bit = dbo.UFN_CONDITIONSETTING_EVALUATEEXISTSCONDITION('Multicurrency');

  if @MULTICURRENCYENABLED = 0
    set @CURRENCYCODE = 1;

  declare @CURRENCYID uniqueidentifier;

  if @CURRENCYCODE = 1
    set @CURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

  if @CURRENCYCODE = 3
  begin
    set @CURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID);

    if @CURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
    begin
      set @CURRENCYCODE = 1
    end
  end

  -------------------------------------

  --GET PROSPECTIDS

  -------------------------------------

  declare @PROSPECTIDS table
  (
    ID uniqueidentifier,
    NAME nvarchar(300),
    PLANNEDGIFTID uniqueidentifier
  );

  insert into
    @PROSPECTIDS
  select distinct
    PG.CONSTITUENTID as ID,
    NF.NAME,
    PG.ID as PLANNEDGIFTID
  from
    dbo.PLANNEDGIFT PG
  outer apply
    dbo.UFN_CONSTITUENT_DISPLAYNAME(PG.CONSTITUENTID) NF
  where
    PG.CONSTITUENTID in
    (
      select
        @PROSPECTID

      union all

      select
        ID
      from
        dbo.UFN_GROUP_GETCURRENTMEMBERSNOPERMISSIONCHECK(@PROSPECTID)
    )

  union all

  select distinct
    RELATEDCONSTITUENT.ID as ID,
    NF.NAME,
    PG.ID as PLANNEDGIFTID
  from
    dbo.PLANNEDGIFT PG
  left join
    dbo.PLANNEDGIFTRELATIONSHIP on PLANNEDGIFTRELATIONSHIP.PLANNEDGIFTID = PG.ID
  left join
    dbo.RELATIONSHIP on RELATIONSHIP.ID = PLANNEDGIFTRELATIONSHIP.RELATIONSHIPID
  left join
    dbo.CONSTITUENT RELATEDCONSTITUENT on
      (RELATEDCONSTITUENT.ID = RELATIONSHIP.RECIPROCALCONSTITUENTID and RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @PROSPECTID) or
      (RELATEDCONSTITUENT.ID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID and RELATIONSHIP.RECIPROCALCONSTITUENTID = @PROSPECTID)
  outer apply
    dbo.UFN_CONSTITUENT_DISPLAYNAME(RELATEDCONSTITUENT.ID) NF;

  -------------------------------------

  --MAIN WORK

  -------------------------------------

  declare @DATALISTID uniqueidentifier = '5a514950-deef-48c7-8e86-47b67cfcb3c1';

  declare @PLANNEDGIFTINFO table
  (
    PROSPECTID uniqueidentifier,
    PROSPECTNAME nvarchar(400),
    ID uniqueidentifier,
    VEHICLE nvarchar(100),
    DATE date,
    AMOUNT money,
    STATUS nvarchar(100),
    ISANONYMOUS bit,
    ISREVOCABLE bit,
    GROUPORPRIMARYSORT nvarchar(300),
    HASPERMISSIONS bit,
    EXPECTEDMATURITY smallint,
    SITES nvarchar(400),
    DISPLAYCURRENCY uniqueidentifier,
    BALANCE money,
    PAYMENTS money,
    PARENTID uniqueidentifier,
    VIEWFORMID uniqueidentifier,
    CAMPAIGNS nvarchar(max)
  );

  insert into
    @PLANNEDGIFTINFO
  select
    PROSPECT.ID as PROSPECTID,
    PROSPECT.NAME as PROSPECTNAME,
    PG.ID,
    PG.VEHICLE,
    PG.GIFTDATE as [DATE],
    dbo.UFN_PLANNEDGIFTREVENUE_GETTOTALAMOUNT(PG.ID, @CURRENCYCODE, @CURRENCYID) as AMOUNT,
    PG.[STATUS],
    PG.ISANONYMOUS,
    PG.ISREVOCABLE,
    case
      when PG.CONSTITUENTID = @PROSPECTID
        then '0'
      when (select ISPRIMARY from dbo.GROUPMEMBER where GROUPID = @PROSPECTID and MEMBERID = PG.CONSTITUENTID) = 1
        then '1'
      else
        '2' + PROSPECT.NAME + ' ' + cast(PROSPECT.ID as nvarchar(36))
    end as GROUPORPRIMARYSORT,
    case
      when dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
        then 1
      else 
        dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORCONSTIT(@CURRENTAPPUSERID, @DATALISTID, PROSPECT.ID)
    end as HASPERMISSIONS,
    PG.EXPECTEDMATURITY,
    dbo.UFN_PLANNEDGIFT_GETSITELIST(PG.ID) as SITES,
    dbo.UFN_PLANNEDGIFTREVENUE_GETCURRENCYIDFROMCODE(PG.ID, @CURRENCYCODE, @CURRENCYID) as DISPLAYCURRENCY,
    dbo.UFN_PLANNEDGIFT_GETBALANCE(PG.ID, @CURRENCYID, @CURRENCYCODE) as BALANCE,
    dbo.UFN_PLANNEDGIFT_GETPAYMENTS(PG.ID, @CURRENCYID, @CURRENCYCODE) as PAYMENTS,
    null as PARENTID,
    'C1FBFDA5-2EF8-4211-8B9A-B1E30689D08C' as VIEWFORMID,
    (
      select
        dbo.UDA_BUILDLIST(distinct CAMPAIGN.NAME)
      from
        dbo.PLANNEDGIFTDESIGNATION
      inner join
        dbo.PLANNEDGIFTDESIGNATIONCAMPAIGN on PLANNEDGIFTDESIGNATIONCAMPAIGN.PLANNEDGIFTDESIGNATIONID = PLANNEDGIFTDESIGNATION.ID
      inner join
        dbo.CAMPAIGN on CAMPAIGN.ID = PLANNEDGIFTDESIGNATIONCAMPAIGN.CAMPAIGNID
      where
        PLANNEDGIFTDESIGNATION.PLANNEDGIFTID = PG.ID
    ) as CAMPAIGNS
  from
    dbo.PLANNEDGIFT as PG
  inner join
    @PROSPECTIDS as PROSPECT on PROSPECT.ID = PG.CONSTITUENTID and PROSPECT.PLANNEDGIFTID = PG.ID
  where
  (
    select
      count(*) 
    from
      dbo.UFN_SITEID_MAPFROM_PLANNEDGIFTID(PG.ID) as PLANNEDGIFTSITE 
    where
      (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[PLANNEDGIFTSITE].[SITEID] or (SITEID is null and [PLANNEDGIFTSITE].[SITEID] is null)))
  ) > 0
  and 
  (
    @SITEFILTERMODE = 0 or 
    PG.ID in
    (
      select
        PLANNEDGIFTSITE.PLANNEDGIFTID
      from
        dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) as SITEFILTER
      inner join
        dbo.PLANNEDGIFTSITE on PLANNEDGIFTSITE.SITEID = SITEFILTER.SITEID
    )
  )
  and
  (
    @CAMPAIGNFILTERMODE = 0 or
    exists
    (
    select
      1
    from
      dbo.PLANNEDGIFTDESIGNATION
    inner join
      dbo.PLANNEDGIFTDESIGNATIONCAMPAIGN on PLANNEDGIFTDESIGNATIONCAMPAIGN.PLANNEDGIFTDESIGNATIONID = PLANNEDGIFTDESIGNATION.ID
    inner join
      @CAMPAIGNFILTERTABLE as CAMPAIGNFILTER on CAMPAIGNFILTER.ID = PLANNEDGIFTDESIGNATIONCAMPAIGN.CAMPAIGNID
    where
      PLANNEDGIFTDESIGNATION.PLANNEDGIFTID = PG.ID
    )
  )

  union all

  --get all planned gift additions

  select
    PROSPECT.ID as PROSPECTID,
    PROSPECT.NAME as PROSPECTNAME,
    PGA.ID,
    PG.VEHICLE,
    PGA.GIFTDATE as [DATE],
    dbo.UFN_PLANNEDGIFTADDITIONREVENUE_GETTOTALAMOUNT(PGA.ID, @CURRENCYCODE, @CURRENCYID) as AMOUNT,
    PG.[STATUS],
    PG.ISANONYMOUS,
    PG.ISREVOCABLE,
    case
      when PG.CONSTITUENTID = @PROSPECTID
        then '0'
      when (select ISPRIMARY from dbo.GROUPMEMBER where GROUPID = @PROSPECTID and MEMBERID = PG.CONSTITUENTID) = 1
        then '1'
      else
        '2' + PROSPECT.NAME + ' ' + cast(PROSPECT.ID as nvarchar(36))
    end as GROUPORPRIMARYSORT,
    case
      when dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
        then 1
      else 
        dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORCONSTIT(@CURRENTAPPUSERID, @DATALISTID, PROSPECT.ID)
    end as HASPERMISSIONS,
    PG.EXPECTEDMATURITY,
    dbo.UFN_PLANNEDGIFT_GETSITELIST(PG.ID) as SITES,
    dbo.UFN_PLANNEDGIFTADDITIONREVENUE_GETCURRENCYIDFROMCODE(PGA.ID, @CURRENCYCODE, @CURRENCYID) as DISPLAYCURRENCY,
    dbo.UFN_PLANNEDGIFTADDITION_GETBALANCE(PGA.ID, @CURRENCYID, @CURRENCYCODE) as BALANCE,
    dbo.UFN_PLANNEDGIFTADDITION_GETPAYMENTS(PGA.ID, @CURRENCYID, @CURRENCYCODE) as PAYMENTS,
    PG.ID PARENTID,
    'd8e2f2b5-4c4f-46de-a2f7-39c595dd2892' as VIEWFORMID,
    (
      select dbo.UDA_BUILDLIST(distinct CAMPAIGN.NAME)
      from dbo.PLANNEDGIFTADDITIONDESIGNATION
      inner join dbo.PLANNEDGIFTADDITIONDESIGNATIONCAMPAIGN on PLANNEDGIFTADDITIONDESIGNATION.ID = PLANNEDGIFTADDITIONDESIGNATIONCAMPAIGN.PLANNEDGIFTADDITIONDESIGNATIONID
      inner join dbo.CAMPAIGN on CAMPAIGN.ID = PLANNEDGIFTADDITIONDESIGNATIONCAMPAIGN.CAMPAIGNID
      where PLANNEDGIFTADDITIONDESIGNATION.PLANNEDGIFTADDITIONID = PGA.ID
    ) as CAMPAIGNS
  from
    dbo.PLANNEDGIFT PG
  inner join
    @PROSPECTIDS as PROSPECT on PROSPECT.ID = PG.CONSTITUENTID and PROSPECT.PLANNEDGIFTID = PG.ID
  inner join
    dbo.PLANNEDGIFTADDITION as PGA on PG.ID = PGA.PLANNEDGIFTID
  where
  (
    select
      count(*) 
    from
      dbo.UFN_SITEID_MAPFROM_PLANNEDGIFTID(PG.ID) as PLANNEDGIFTSITE
    where
      (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[PLANNEDGIFTSITE].[SITEID] or (SITEID is null and [PLANNEDGIFTSITE].[SITEID] is null)))
  ) > 0
  and 
  (
    @SITEFILTERMODE = 0 or 
    PG.ID in
    (
      select
        PLANNEDGIFTSITE.PLANNEDGIFTID
      from
        dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) as SITEFILTER
      inner join
        dbo.PLANNEDGIFTSITE on PLANNEDGIFTSITE.SITEID = SITEFILTER.SITEID
    )
  )
  and
  (
    @CAMPAIGNFILTERMODE = 0 or
    exists
    (
      select
        1
      from
        dbo.PLANNEDGIFTADDITIONDESIGNATION
      inner join
        dbo.PLANNEDGIFTADDITIONDESIGNATIONCAMPAIGN on PLANNEDGIFTADDITIONDESIGNATIONCAMPAIGN.PLANNEDGIFTADDITIONDESIGNATIONID = PLANNEDGIFTADDITIONDESIGNATION.ID
      inner join
        @CAMPAIGNFILTERTABLE as CAMPAIGNFILTER on CAMPAIGNFILTER.ID = PLANNEDGIFTADDITIONDESIGNATIONCAMPAIGN.CAMPAIGNID 
      where
        PLANNEDGIFTADDITIONDESIGNATION.PLANNEDGIFTADDITIONID = PGA.ID
    )
  );


  --TOP ROLLUP LEVEL

  select
    PGINFO.PROSPECTID,
    PGINFO.PROSPECTNAME,
    PGINFO.ID,
    PGINFO.VEHICLE,
    case
      when PGCHILDNODEINFO.PARENTID is null
        then PGINFO.[DATE]
      else
        null
    end as [DATE],
    case
      when coalesce(PGCHILDNODEINFO.CURRENCYCOUNT, 1) = 1 and coalesce((select top 1 DISPLAYCURRENCY from @PLANNEDGIFTINFO PGADDITIONINFO where PGINFO.ID = PGADDITIONINFO.PARENTID),PGINFO.DISPLAYCURRENCY) = PGINFO.DISPLAYCURRENCY
        then
          case 
            when PGINFO.AMOUNT is not null and PGCHILDNODEINFO.TOTALAMOUNT is not null
              then PGINFO.AMOUNT + PGCHILDNODEINFO.TOTALAMOUNT
            when PGINFO.AMOUNT is not null and PGCHILDNODEINFO.TOTALAMOUNT is null
              then PGINFO.AMOUNT
            when PGINFO.AMOUNT is null and PGCHILDNODEINFO.TOTALAMOUNT is not null
              then PGCHILDNODEINFO.TOTALAMOUNT
            else
              null
          end
    end as AMOUNT,
    PGINFO.[STATUS],
    PGINFO.ISANONYMOUS,
    PGINFO.ISREVOCABLE,
    PGINFO.GROUPORPRIMARYSORT,
    PGINFO.HASPERMISSIONS,
    PGINFO.EXPECTEDMATURITY,
    PGINFO.SITES,
    PGINFO.DISPLAYCURRENCY,
    case
      when coalesce(PGCHILDNODEINFO.CURRENCYCOUNT, 1) = 1 and coalesce((select top 1 DISPLAYCURRENCY from @PLANNEDGIFTINFO PGADDITIONINFO where PGINFO.ID = PGADDITIONINFO.PARENTID),PGINFO.DISPLAYCURRENCY) = PGINFO.DISPLAYCURRENCY
        then
          case
            when PGINFO.BALANCE is not null and PGCHILDNODEINFO.TOTALBALANCE is not null
              then PGINFO.BALANCE + PGCHILDNODEINFO.TOTALBALANCE
            when PGINFO.BALANCE is not null and PGCHILDNODEINFO.TOTALBALANCE is null
              then PGINFO.BALANCE
            when PGINFO.BALANCE is null and PGCHILDNODEINFO.TOTALBALANCE is not null
              then PGCHILDNODEINFO.TOTALBALANCE
            else
              null
          end
    end as BALANCE,
    case
      when coalesce(PGCHILDNODEINFO.CURRENCYCOUNT, 1) = 1 and coalesce((select top 1 DISPLAYCURRENCY from @PLANNEDGIFTINFO PGADDITIONINFO where PGINFO.ID = PGADDITIONINFO.PARENTID),PGINFO.DISPLAYCURRENCY) = PGINFO.DISPLAYCURRENCY
        then 
          case
            when PGINFO.PAYMENTS is not null and PGCHILDNODEINFO.TOTALPAYMENTS is not null
              then PGINFO.PAYMENTS + PGCHILDNODEINFO.TOTALPAYMENTS
            when PGINFO.PAYMENTS is not null and PGCHILDNODEINFO.TOTALPAYMENTS is null
              then PGINFO.PAYMENTS
            when PGINFO.PAYMENTS is null and PGCHILDNODEINFO.TOTALPAYMENTS is not null
              then PGCHILDNODEINFO.TOTALPAYMENTS
            else
              null
          end
    end as PAYMENTS,
    null as PARENTID,
    PGINFO.ID as IDFORTREE,
    PGINFO.VIEWFORMID,
    0 as ISADDITION,
    PGINFO.CAMPAIGNS
  from
    @PLANNEDGIFTINFO as PGINFO
  left join
  (
    select 
      PGADDITIONINFO.PARENTID,
      count(distinct PGADDITIONINFO.DISPLAYCURRENCY) as CURRENCYCOUNT,
      sum(PGADDITIONINFO.AMOUNT) as TOTALAMOUNT,
      sum(PGADDITIONINFO.BALANCE) as TOTALBALANCE,
      sum(PGADDITIONINFO.PAYMENTS) as TOTALPAYMENTS
    from
      @PLANNEDGIFTINFO as PGADDITIONINFO 
    group by
      PGADDITIONINFO.PARENTID
  ) PGCHILDNODEINFO on PGCHILDNODEINFO.PARENTID = PGINFO.ID
  where 
    PGINFO.PARENTID is null

  union all

  --PLANNED GIFTS

  select
    PROSPECTID,
    'Original gift' as PROSPECTNAME,
    ID,
    VEHICLE,
    [DATE],
    AMOUNT,
    [STATUS],
    ISANONYMOUS,
    ISREVOCABLE,
    GROUPORPRIMARYSORT,
    HASPERMISSIONS,
    EXPECTEDMATURITY,
    SITES,
    DISPLAYCURRENCY,
    BALANCE,
    PAYMENTS,
    ID as PARENTID,
    null as IDFORTREE,
    VIEWFORMID,
    0 as ISADDITION,
    CAMPAIGNS
  from
    @PLANNEDGIFTINFO PGINFO
  where 
    PARENTID is null and
    ID in (select PGINFOSUB.PARENTID from @PLANNEDGIFTINFO as PGINFOSUB)

  union all

  --PLANNED GIFT ADDITIONS

  select
    PROSPECTID,
    'Addition' as PROSPECTNAME,
    ID,
    VEHICLE,
    [DATE],
    AMOUNT,
    [STATUS],
    ISANONYMOUS,
    ISREVOCABLE,
    GROUPORPRIMARYSORT,
    HASPERMISSIONS,
    EXPECTEDMATURITY,
    SITES,
    DISPLAYCURRENCY,
    BALANCE,
    PAYMENTS,
    PARENTID,
    null as IDFORTREE,
    VIEWFORMID,
    1 as ISADDITION,
    CAMPAIGNS
  from
    @PLANNEDGIFTINFO PGINFO
  where 
    PARENTID is not null
  order by
    PROSPECTNAME desc,
    GROUPORPRIMARYSORT,
    [DATE] desc;

end