USP_DATALIST_PLANNEDGIFTSTOBERECONCILED

CREATE procedure dbo.USP_DATALIST_PLANNEDGIFTSTOBERECONCILED
(
  @CURRENTAPPUSERID uniqueidentifier,
  @ORGPOSITIONSSELECTIONID uniqueidentifier = null,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null,
  @INCLUDECOMPLETED bit = 0,
  @DATAWINDOWOPEN datetime = null,
  @DATAWINDOWCLOSE datetime = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null
)
as
  set nocount on;

  declare @CURRENTDATE datetime;
  set @CURRENTDATE = getdate();

  declare @ISSYSADMIN bit;
  set @ISSYSADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);

  declare @IMAGEKEY nvarchar(32) = N'res:check'

  if @ORGPOSITIONSSELECTIONID is null
    select
      PGR.ID,
      PGR.PLANNEDGIFTID,
      PGR.PLANNEDGIFTADDITIONID,
      PGR.REVENUEID,
      PROMGR_NF.NAME PROSPECTMANAGER,
      PRIMARYMGR_NF.NAME PRIMARYMANAGER,
      SECONDARYMGR_NF.NAME SECONDARYMANAGER,
      CONSTITUENT_NF.NAME CONSTITUENT,
      case when PGR.PLANNEDGIFTADDITIONID is null then PG.GIFTDATE else PGA.GIFTDATE end GIFTDATE,
      case when PGR.PLANNEDGIFTADDITIONID is null then PG.GIFTAMOUNT else PGA.GIFTAMOUNT end GIFTAMOUNT,
      case when PLANNEDGIFTADDITIONID is null then 'Planned gift' else 'Addition' end +
      case REVENUE.TRANSACTIONTYPECODE when 0 then ' payment' else ' revenue' end
      GIFTTYPE,
      dbo.UFN_PLANNEDGIFT_GETSITELIST(PG.ID) SITES,
      PGR.DATECHANGED,
      case COMPLETED when 0 then '' else @IMAGEKEY end IMAGEKEY,
      case when PGR.PLANNEDGIFTADDITIONID is null then 0 else 1 end ISADDITION,
      case REVENUE.TRANSACTIONTYPECODE when 0 then 1 else 0 end ISPAYMENT,
      COMPLETED as ISCOMPLETED,
      PG.BASECURRENCYID,
      PG.TRANSACTIONCURRENCYID,
      PG.TRANSACTIONGIFTAMOUNT
    from
      dbo.PLANNEDGIFTRECONCILE PGR
      inner join dbo.PLANNEDGIFT PG on PGR.PLANNEDGIFTID = PG.ID
      inner join dbo.PROSPECT P on PG.CONSTITUENTID = P.ID
      left join dbo.PLANNEDGIFTADDITION PGA on PGR.PLANNEDGIFTADDITIONID = PGA.ID
      left join dbo.REVENUE on PGR.REVENUEID = REVENUE.ID
      left join dbo.PROSPECTPLAN PP on PG.PROSPECTPLANID = PP.ID
      outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PG.CONSTITUENTID) CONSTITUENT_NF
      outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PP.PRIMARYMANAGERFUNDRAISERID) PRIMARYMGR_NF
      outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PP.SECONDARYMANAGERFUNDRAISERID) SECONDARYMGR_NF
      outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(P.PROSPECTMANAGERFUNDRAISERID) PROMGR_NF
    where
      (@INCLUDECOMPLETED = 1 or PGR.COMPLETED = 0)
      and (@DATAWINDOWOPEN is null or @DATAWINDOWCLOSE is null or PGR.DATECHANGED between @DATAWINDOWOPEN and @DATAWINDOWCLOSE)
      and (
        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 (
        @ISSYSADMIN = 1
        or
          dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORCONSTIT(@CURRENTAPPUSERID,'0c24bdb7-ca86-46be-a0b2-4b853e29acb6',PG.CONSTITUENTID) = 1
      )
  else
  begin
    declare @TODAY datetime;
    set @TODAY = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

    declare @IDS as table(ID uniqueidentifier);
    insert into @IDS exec USP_ORGANIZATIONHIERARCHY_IDSET_POSITIONHOLDERS @ORGPOSITIONSSELECTIONID, @CURRENTAPPUSERID;

    select
      PGR.ID,
      PGR.PLANNEDGIFTID,
      PGR.PLANNEDGIFTADDITIONID,
      PGR.REVENUEID,
      PROMGR_NF.NAME PROSPECTMANAGER,
      PRIMARYMGR_NF.NAME PRIMARYMANAGER,
      SECONDARYMGR_NF.NAME SECONDARYMANAGER,
      CONSTITUENT_NF.NAME CONSTITUENT,
      case when PGR.PLANNEDGIFTADDITIONID is null then PG.GIFTDATE else PGA.GIFTDATE end GIFTDATE,
      case when PGR.PLANNEDGIFTADDITIONID is null then PG.GIFTAMOUNT else PGA.GIFTAMOUNT end GIFTAMOUNT,
      case when PLANNEDGIFTADDITIONID is null then 'Planned gift' else 'Addition' end GIFTTYPE,
      dbo.UFN_PLANNEDGIFT_GETSITELIST(PG.ID) SITES,
      PGR.DATECHANGED,
      case COMPLETED when 0 then '' else @IMAGEKEY end IMAGEKEY,
      case when PGR.PLANNEDGIFTADDITIONID is null then 0 else 1 end ISADDITION,
      case REVENUE.TRANSACTIONTYPECODE when 0 then 1 else 0 end ISPAYMENT,
      COMPLETED as ISCOMPLETED,
      PG.BASECURRENCYID,
      PG.TRANSACTIONCURRENCYID,
      PG.TRANSACTIONGIFTAMOUNT
    from
      dbo.PLANNEDGIFTRECONCILE PGR
      inner join dbo.PLANNEDGIFT PG on PGR.PLANNEDGIFTID = PG.ID
      inner join dbo.PROSPECT P on PG.CONSTITUENTID = P.ID
      left join dbo.PLANNEDGIFTADDITION PGA on PGR.PLANNEDGIFTADDITIONID = PGA.ID
      left join dbo.PROSPECTPLAN PP on PG.PROSPECTPLANID = PP.ID
      inner join dbo.ORGANIZATIONPOSITIONHOLDER OPH
        on OPH.CONSTITUENTID = P.PROSPECTMANAGERFUNDRAISERID and @TODAY between OPH.DATEFROM and coalesce(OPH.DATETO, @TODAY)
      inner join @IDS as SELECTION on OPH.[ID] = SELECTION.[ID]
      left join dbo.REVENUE on PGR.REVENUEID = REVENUE.ID
      outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PG.CONSTITUENTID) CONSTITUENT_NF
      outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PP.PRIMARYMANAGERFUNDRAISERID) PRIMARYMGR_NF
      outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PP.SECONDARYMANAGERFUNDRAISERID) SECONDARYMGR_NF
      outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(P.PROSPECTMANAGERFUNDRAISERID) PROMGR_NF
    where
      (@INCLUDECOMPLETED = 1 or PGR.COMPLETED = 0)
      and (@DATAWINDOWOPEN is null or @DATAWINDOWCLOSE is null or PGR.DATECHANGED between @DATAWINDOWOPEN and @DATAWINDOWCLOSE)
      and (
        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 (
        @ISSYSADMIN = 1
        or
        dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORCONSTIT(@CURRENTAPPUSERID,'0c24bdb7-ca86-46be-a0b2-4b853e29acb6',PG.CONSTITUENTID) = 1
      )
  end