USP_REPORT_PLANNEDGIFTDETAIL

CREATE procedure dbo.USP_REPORT_PLANNEDGIFTDETAIL
(
  @SELECTIONID uniqueidentifier,
  @FROMDATE datetime = null,
  @TODATE datetime = null,
  @VEHICLECODE tinyint = null,
  @STATUSCODE tinyint = null,
  @GROUPBY tinyint = null,
  @SHOWMEMBERDATAFORGROUPS bit = null,
  @SHOWGROUPDATAFORMEMBERS bit = null,
  @REPORTUSERID nvarchar(128) = null,
  @CURRENCYCODE tinyint = 1,
  @ALTREPORTUSERID nvarchar(128) = null
)
with execute as owner as
  set nocount on;

  declare @CURRENTAPPUSERID uniqueidentifier;
  declare @ISADMIN bit;
  declare @APPUSER_IN_NONRACROLE bit;
  declare @APPUSER_IN_NOSECGROUPROLE bit;
  declare @APPUSER_IN_NONSITEROLE bit;
  declare @APPUSER_IN_NOSITEROLE bit;

  set @CURRENTAPPUSERID = dbo.UFN_APPUSER_GETREPORTAPPUSERID(@REPORTUSERID, @ALTREPORTUSERID);
  set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);

  if @ISADMIN = 0
  begin
    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_NOSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SITE_ROLE(@CURRENTAPPUSERID);
  end

  declare @CURRENTDATEEARLIESTTIME datetime = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

  declare @SQLTOEXEC nvarchar(max);

  set @SQLTOEXEC = '
with PLANNEDGIFTS_CTE as
(
  select
    PG.ID as PLANNEDGIFTID,
    PG.GIFTDATE,
    PG.VEHICLE,
    PG.VEHICLECODE,
    PG.EXPECTEDMATURITY,
    case
      when PG.EXPECTEDMATURITY = 0 then null
      else PG.EXPECTEDMATURITY
    end as EXPECTEDMATURITYYEAR,
    PG.STATUS,
    PG.STATUSCODE,
    PG.SUBTYPE,
    PG.BASECURRENCYID,
    PG.TRANSACTIONCURRENCYID,
    case @CURRENCYCODE
      when 0 then PG.GIFTAMOUNT
      when 1 then PG.ORGANIZATIONGIFTAMOUNT
      when 2 then PG.TRANSACTIONGIFTAMOUNT
    end as PLANNEDGIFTAMOUNT,
    case @CURRENCYCODE
      when 0 then PG.REMAINDERVALUE
      when 1 then PG.ORGANIZATIONREMAINDERVALUE
      when 2 then PG.TRANSACTIONREMAINDERVALUE
    end as TOTALREMAINDERVALUE,
    case @CURRENCYCODE
      when 0 then PG.EXPECTEDGIFTAMOUNT
      when 2 then PG.TRANSACTIONEXPECTEDGIFTAMOUNT
      else PG.ORGANIZATIONEXPECTEDGIFTAMOUNT
    end as EXPECTEDGIFTAMOUNT,
    case @CURRENCYCODE
      when 0 then PG.BASECURRENCYID
      when 2 then PG.TRANSACTIONCURRENCYID
      else dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
    end as PGCURRENCYID,
    C.ID as CONSTITUENTID,
    C.KEYNAME as KEYNAME,
    C.FIRSTNAME as FIRSTNAME,
    C.MIDDLENAME as MIDDLENAME,
    NF.NAME,
    C.ISGROUP,
    CINFO.CURRENCYSYMBOL,
    CINFO.ISO4217,
    CINFO.DECIMALDIGITS,
    CINFO.SYMBOLDISPLAYSETTINGCODE,
    dbo.UFN_PROSPECTPLANTYPECODE_GETDESCRIPTION(PP.PROSPECTPLANTYPECODEID) as PLANTYPE
  from
    dbo.PLANNEDGIFT PG
  inner join
    dbo.CONSTITUENT C with (nolock) on C.ID = PG.CONSTITUENTID
  left join
    dbo.PROSPECTPLAN PP on PP.ID = PG.PROSPECTPLANID
  outer apply
    dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 0 then PG.BASECURRENCYID when 1 then dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() when 2 then PG.TRANSACTIONCURRENCYID end) as CINFO
  outer apply
    dbo.UFN_CONSTITUENT_DISPLAYNAME(C.ID) NF'

  if @SELECTIONID is not null
    set @SQLTOEXEC = @SQLTOEXEC + '
  inner join
    dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) SELECTION on PG.ID = SELECTION.ID'


  declare @WHERECLAUSESQL nvarchar(1000) = ''

  if @FROMDATE is not null and @TODATE is not null
    set @WHERECLAUSESQL = '
    (PG.GIFTDATE between @FROMDATE and @TODATE or @FROMDATE is null or @TODATE is null)'

  if @VEHICLECODE is not null
  begin
    if Len(@WHERECLAUSESQL) > 0
      set @WHERECLAUSESQL = @WHERECLAUSESQL + ' and'

    set @WHERECLAUSESQL = @WHERECLAUSESQL + '
    (PG.VEHICLECODE = @VEHICLECODE)'
  end

  if @STATUSCODE is not null
  begin
    if Len(@WHERECLAUSESQL) > 0
      set @WHERECLAUSESQL = @WHERECLAUSESQL + ' and'

    set @WHERECLAUSESQL = @WHERECLAUSESQL + '
    (PG.STATUSCODE = @STATUSCODE)'
  end

  if @ISADMIN = 0
  begin
    if len(@WHERECLAUSESQL) > 0
      set @WHERECLAUSESQL = @WHERECLAUSESQL + ' and'

    set @WHERECLAUSESQL = @WHERECLAUSESQL + '
    (dbo.UFN_PLANNEDGIFT_USERHASSITEACCESS(@CURRENTAPPUSERID, PG.ID) = 1)'

    if @APPUSER_IN_NONRACROLE = 0
    begin
      if Len(@WHERECLAUSESQL) > 0
        set @WHERECLAUSESQL = @WHERECLAUSESQL + ' and'

      set @WHERECLAUSESQL = @WHERECLAUSESQL + '
    (dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, C.ID, @APPUSER_IN_NOSECGROUPROLE) = 1)'
    end    

    if @APPUSER_IN_NONSITEROLE = 0
    begin
    if Len(@WHERECLAUSESQL) > 0
      set @WHERECLAUSESQL = @WHERECLAUSESQL + ' and'

    set @WHERECLAUSESQL = @WHERECLAUSESQL + '
    (dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, C.ID, @APPUSER_IN_NOSITEROLE) = 1)'
    end
  end

  if len(@WHERECLAUSESQL) > 0
    set @SQLTOEXEC = @SQLTOEXEC + '
  where
    ' + @WHERECLAUSESQL


  set @SQLTOEXEC = @SQLTOEXEC + '
), DESIGNATIONS_CTE as
(
  select
    PLANNEDGIFTID,
    DESIGNATIONID,
    sum(DESIGNATIONAMOUNT) PLANNEDGIFTDESIGNATIONAMOUNT,
    DESIGNATION.NAME,
    DESIGNATION.VANITYNAME
  from
  (
    select
      PGA.PLANNEDGIFTID,
      PGAD.DESIGNATIONID,
      case @CURRENCYCODE
        when 0 then coalesce(PGAD.AMOUNT, PGA.GIFTAMOUNT)
        when 1 then coalesce(PGAD.ORGANIZATIONAMOUNT, PGA.ORGANIZATIONGIFTAMOUNT)
        when 2 then coalesce(PGAD.TRANSACTIONAMOUNT, PGA.TRANSACTIONGIFTAMOUNT)
      end as DESIGNATIONAMOUNT
    from
      dbo.PLANNEDGIFTADDITION PGA
    inner join
      PLANNEDGIFTS_CTE PG on PG.PLANNEDGIFTID = PGA.PLANNEDGIFTID
    left join
      PLANNEDGIFTADDITIONDESIGNATION PGAD on PGAD.PLANNEDGIFTADDITIONID = PGA.ID

    union all

    select
      PG.PLANNEDGIFTID,
      PGD.DESIGNATIONID,
      coalesce(case @CURRENCYCODE
        when 0 then PGD.AMOUNT
        when 1 then PGD.ORGANIZATIONAMOUNT
        when 2 then PGD.TRANSACTIONAMOUNT
      end, PG.PLANNEDGIFTAMOUNT) as DESIGNATIONAMOUNT
    from
      PLANNEDGIFTS_CTE PG
    left join
      dbo.PLANNEDGIFTDESIGNATION PGD on PGD.PLANNEDGIFTID = PG.PLANNEDGIFTID
  ) DESIGNATIONS
  left join
    dbo.DESIGNATION on DESIGNATION.ID = DESIGNATIONS.DESIGNATIONID
  group by
    PLANNEDGIFTID, DESIGNATIONID, NAME, VANITYNAME
)
, PLANNEDGIFTDESIGNATION_CTE as
(
  select
    PG.*,
    D.PLANNEDGIFTDESIGNATIONAMOUNT,
    D.DESIGNATIONID,
    D.NAME as DESIGNATIONNAME,
    D.VANITYNAME as DESIGNATIONVANITYNAME,
    coalesce(case
      when PLANNEDGIFTAMOUNT = 0 then 0
      else (cast(TOTALREMAINDERVALUE as decimal(30, 10)) *
        (cast(PLANNEDGIFTDESIGNATIONAMOUNT as decimal(30, 10)) /
        cast((select sum(PLANNEDGIFTDESIGNATIONAMOUNT) from DESIGNATIONS_CTE where PLANNEDGIFTID = PG.PLANNEDGIFTID) as decimal(30, 10))))
      end, TOTALREMAINDERVALUE)
    as DESIGNATIONREMAINDERVALUE,
    row_number() over (partition by PG.PLANNEDGIFTID order by PLANNEDGIFTDESIGNATIONAMOUNT) as DESIGNATIONNUMBER
  from
    PLANNEDGIFTS_CTE PG
  left join
    DESIGNATIONS_CTE D on PG.PLANNEDGIFTID = D.PLANNEDGIFTID
)'


  declare @GROUPFIELDCLAUSESQL nvarchar(max)
  declare @GROUPMEMBERJOIN nvarchar(1000)
  declare @GROUPJOIN nvarchar(1000)
  declare @GROUPBYCLAUSE nvarchar(150) = ''

  if @GROUPBY is not null and (@SHOWMEMBERDATAFORGROUPS = 1 or @SHOWGROUPDATAFORMEMBERS = 1)
  begin
    set @GROUPBYCLAUSE = 
      case @GROUPBY
        when 2 then
          '(PGD.EXPECTEDMATURITY = GPGD.EXPECTEDMATURITY) and'
        when 3 then
          '(PGD.VEHICLECODE = GPGD.VEHICLECODE) and'
        when 4 then
          '(PGD.STATUSCODE = GPGD.STATUSCODE) and'
        when 5 then
          '(PGD.DESIGNATIONID = GPGD.DESIGNATIONID) and'
        else
          ''
      end
  end

  if @SHOWMEMBERDATAFORGROUPS = 1
  begin
    set @GROUPMEMBERJOIN = '
  from
    dbo.GROUPMEMBER GM
  inner join
    dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
  inner join
    PLANNEDGIFTDESIGNATION_CTE GPGD on GPGD.CONSTITUENTID = GM.MEMBERID
  where
    ' + @GROUPBYCLAUSE + '
    GM.GROUPID = PGD.CONSTITUENTID and
    ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATEEARLIESTTIME))
      or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME))
      or (GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME and GMDR.DATETO > @CURRENTDATEEARLIESTTIME))'

    set @GROUPJOIN = '
  from
    PLANNEDGIFTDESIGNATION_CTE GPGD
  where
    ' + @GROUPBYCLAUSE + '
    GPGD.CONSTITUENTID = PGD.CONSTITUENTID'


    set @GROUPFIELDCLAUSESQL = '
  case
    when PGD.ISGROUP = 1 then
      (
        select
          sum(coalesce(PLANNEDGIFTDESIGNATIONAMOUNT, EXPECTEDGIFTAMOUNT))
        ' + @GROUPMEMBERJOIN + '
        ) + (
        select
          sum(coalesce(PLANNEDGIFTDESIGNATIONAMOUNT, EXPECTEDGIFTAMOUNT))
        ' + @GROUPJOIN + '
      )
    else null
  end as CONSTITUENTANDMEMBERSGIFTAMOUNT,

  case
    when PGD.ISGROUP = 1 then
      (
        select
          case @CURRENCYCODE
            when 0 then max(cast(GPGD.BASECURRENCYID as nvarchar(36)))
            when 1 then max(cast(dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() as nvarchar(36)))
            when 2 then max(cast(GPGD.TRANSACTIONCURRENCYID as nvarchar(36)))
          end as CONSTITUENTCURRENCYID
        ' + @GROUPMEMBERJOIN + '
        having
          case @CURRENCYCODE
            when 0 then count(distinct(GPGD.BASECURRENCYID))
            when 2 then count(distinct(GPGD.TRANSACTIONCURRENCYID))
            else 1
          end = 1
      )
    else null
  end as CONSTITUENTCURRENCYID,

  case
    when PGD.ISGROUP = 1 then
      (
        select
          sum(GPGD.DESIGNATIONREMAINDERVALUE)
        ' + @GROUPMEMBERJOIN + '
        ) + (
        select
          sum(GPGD.DESIGNATIONREMAINDERVALUE)
        ' + @GROUPJOIN + '
      )
    else null
  end as CONSTITUENTANDMEMBERSREMAINDER,'

  end
  else
    set @GROUPFIELDCLAUSESQL = '
  null as CONSTITUENTANDMEMBERSGIFTAMOUNT,
  null as CONSTITUENTCURRENCYID,
  null as CONSTITUENTANDMEMBERSREMAINDER,'


  if @SHOWGROUPDATAFORMEMBERS = 1
  begin
    set @GROUPMEMBERJOIN = '
  from
    dbo.GROUPMEMBER GM
  inner join
    dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
  inner join
    PLANNEDGIFTDESIGNATION_CTE GPGD on GPGD.CONSTITUENTID = GM.GROUPID
  where
    ' + @GROUPBYCLAUSE + '
    GM.MEMBERID = PGD.CONSTITUENTID and
    ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATEEARLIESTTIME))
      or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME))
      or (GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME and GMDR.DATETO > @CURRENTDATEEARLIESTTIME))'

    set @GROUPJOIN = '
  from
    PLANNEDGIFTDESIGNATION_CTE GPGD
  where
    ' + @GROUPBYCLAUSE + '
    GPGD.CONSTITUENTID = PGD.CONSTITUENTID'

    set @GROUPFIELDCLAUSESQL = @GROUPFIELDCLAUSESQL + '
  case
    when PGD.ISGROUP = 0 then
      (
        select
          sum(coalesce(GPGD.PLANNEDGIFTDESIGNATIONAMOUNT, EXPECTEDGIFTAMOUNT))
        ' + @GROUPMEMBERJOIN + '
        ) + (
        select
          sum(coalesce(GPGD.PLANNEDGIFTDESIGNATIONAMOUNT, EXPECTEDGIFTAMOUNT))
        ' + @GROUPJOIN + '
      )
    else null
  end as CONSTITUENTANDGROUPSGIFTAMOUNT,

  case
    when PGD.ISGROUP = 0 then
      (
        select
          case @CURRENCYCODE
            when 0 then max(cast(GPGD.BASECURRENCYID as nvarchar(36)))
            when 1 then max(cast(dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() as nvarchar(36)))
            when 2 then max(cast(GPGD.TRANSACTIONCURRENCYID as nvarchar(36)))
          end as CONSTITUENTCURRENCYID
        ' + @GROUPMEMBERJOIN + '
        having
          case @CURRENCYCODE
            when 0 then count(distinct(GPGD.BASECURRENCYID))
            when 2 then count(distinct(GPGD.TRANSACTIONCURRENCYID))
            else 1
          end = 1
      )
    else null
  end as CONSTITUENTGROUPCURRENCYID,

  case
    when PGD.ISGROUP = 0 then
      (
        select
          sum(GPGD.DESIGNATIONREMAINDERVALUE)
        ' + @GROUPMEMBERJOIN + '
        ) + (
        select
          sum(GPGD.DESIGNATIONREMAINDERVALUE)
        ' + @GROUPJOIN + '
      )
    else null
  end as CONSTITUENTANDGROUPSREMAINDER,'

  end
  else
    set @GROUPFIELDCLAUSESQL = @GROUPFIELDCLAUSESQL + '
  null as CONSTITUENTANDGROUPSGIFTAMOUNT,
  null as CONSTITUENTGROUPCURRENCYID,
  null as CONSTITUENTANDGROUPSREMAINDER,'

  set @SQLTOEXEC = @SQLTOEXEC + '

select
  PGD.PLANNEDGIFTID,
  PGD.CONSTITUENTID,
  PGD.NAME as CONSTITUENTNAME,
  PGD.GIFTDATE,
  PGD.VEHICLE as GIFTVEHICLE,
  PGD.VEHICLECODE as GIFTVEHICLECODE,
  PGD.DESIGNATIONNAME,
  PGD.DESIGNATIONVANITYNAME,
  PGD.DESIGNATIONID,
  coalesce(PGD.PLANNEDGIFTDESIGNATIONAMOUNT, PGD.PLANNEDGIFTAMOUNT) as GIFTAMOUNT,
  case DESIGNATIONNUMBER
    when 1 then
      round((round(PGD.DESIGNATIONREMAINDERVALUE, 2) + REMAINDERADJUST.REMAINDERVALUEADJUST), 2)
    else
      round(PGD.DESIGNATIONREMAINDERVALUE, 2)
  end as REMAINDERVALUE,
  PGD.EXPECTEDMATURITYYEAR,
  PGD.STATUS,
  PGD.STATUSCODE,
  PGD.SUBTYPE,
  PGD.PLANTYPE,
  null as PGDESIGNATIONID,
  PGD.PGCURRENCYID,
  PGD.CURRENCYSYMBOL,
  PGD.ISO4217 as CURRENCYISOCODE,
  PGD.DECIMALDIGITS as CURRENCYDECIMALCOUNT,
  PGD.SYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAY,
  ' + @GROUPFIELDCLAUSESQL + '
  ''http://www.blackbaud.com/PLANNEDGIFTLINK?PLANNEDGIFTLINK='' + CONVERT(nvarchar(36), PGD.PLANNEDGIFTID) as PLANNEDGIFTLINK,
  PGD.KEYNAME as KEYNAME,
  PGD.FIRSTNAME as FIRSTNAME,
  PGD.MIDDLENAME as MIDDLENAME
from
  PLANNEDGIFTDESIGNATION_CTE as PGD
inner join
(
  select
    TOTALREMAINDERVALUE - sum(round(DESIGNATIONREMAINDERVALUE, 2)) as REMAINDERVALUEADJUST,
    TOTALREMAINDERVALUE,
    PLANNEDGIFTID
  from
    PLANNEDGIFTDESIGNATION_CTE
  group by
    PLANNEDGIFTID, TOTALREMAINDERVALUE
) REMAINDERADJUST on REMAINDERADJUST.PLANNEDGIFTID = PGD.PLANNEDGIFTID
order by
  PGD.KEYNAME, PGD.FIRSTNAME'

  exec sp_executesql @SQLTOEXEC, N'@CURRENCYCODE tinyint, @CURRENTDATEEARLIESTTIME datetime, @SELECTIONID uniqueidentifier, @FROMDATE datetime, @TODATE datetime, @VEHICLECODE tinyint, @STATUSCODE tinyint, @CURRENTAPPUSERID uniqueidentifier, @APPUSER_IN_NOSECGROUPROLE bit, @APPUSER_IN_NOSITEROLE bit',
   @CURRENCYCODE, @CURRENTDATEEARLIESTTIME, @SELECTIONID, @FROMDATE, @TODATE, @VEHICLECODE, @STATUSCODE, @CURRENTAPPUSERID, @APPUSER_IN_NOSECGROUPROLE, @APPUSER_IN_NOSITEROLE;

  return 0;