USP_REPORT_PLEDGECASHFLOW

create procedure dbo.USP_REPORT_PLEDGECASHFLOW
(
  @ASOFDATE date = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @CURRENCYCODE tinyint = null,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null,
  @FROMDATE datetime = null,
  @TODATE datetime = null,
  @DESIGNATIONID uniqueidentifier = null
)
as
  set nocount on;

  declare @ISADMIN bit;
  declare @APPUSER_IN_NONRACROLE bit;
  declare @APPUSER_IN_NOSECGROUPROLE bit;

  set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
  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);


  declare @SELECTEDCURRENCYID uniqueidentifier;
  if coalesce(@CURRENCYCODE, 1) = 1
    set @SELECTEDCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
  else if @CURRENCYCODE = 3
    set @SELECTEDCURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID);

  declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
  declare @DECIMALDIGITS tinyint;
  declare @ROUNDINGTYPECODE tinyint;
  declare @ISOCODE nvarchar(3);
  declare @SYMBOLDISPLAYSETTINGCODE tinyint;
  declare @CURRENCYSYMBOL nvarchar(5);

  select
    @DECIMALDIGITS = DECIMALDIGITS,
    @ROUNDINGTYPECODE = ROUNDINGTYPECODE,
    @ISOCODE = ISO4217,
    @SYMBOLDISPLAYSETTINGCODE = SYMBOLDISPLAYSETTINGCODE,
    @CURRENCYSYMBOL = CURRENCYSYMBOL
  from dbo.UFN_CURRENCY_GETPROPERTIES(@SELECTEDCURRENCYID);


  declare @USERGRANTEDTRANSACTIONPAGE bit = 0;
  declare @USERGRANTEDDESIGNATIONPAGE bit = 0;

  if @ISADMIN = 1
  begin
    set @USERGRANTEDTRANSACTIONPAGE = 1;
    set @USERGRANTEDDESIGNATIONPAGE = 1;
  end
  else
  begin
    select
      @USERGRANTEDTRANSACTIONPAGE = [dbo].[UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE](@CURRENTAPPUSERID, 'd00e6c42-2434-4d85-8a04-2323ca6bb2e7'),
      @USERGRANTEDDESIGNATIONPAGE = [dbo].[UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE](@CURRENTAPPUSERID, '4EADC264-0A44-4DF5-8C8C-D89A1C48746C');
  end

  -- #774147 - Performance/Filter Changes


  if object_id('tempdb..#TMP_PLEDGES_FILTERED') is not null
    drop table #TMP_PLEDGES_FILTERED;

  create table #TMP_PLEDGES_FILTERED (
      ID uniqueidentifier not null,
      CONSTITUENTID uniqueidentifier not null,
      INSTALLMENTID uniqueidentifier not null,
      INSTALLMENTDATE datetime not null,
      INSTALLMENTSPLITID uniqueidentifier not null,
      INSTALLMENTSPLITDESIGNATIONID uniqueidentifier not null
  );

  declare @DAYAFTERTODATE date = dateadd(ms, 86399996, @TODATE);

  insert into #TMP_PLEDGES_FILTERED
  select
      PLEDGE.ID,
      PLEDGE.CONSTITUENTID,
      INSTALLMENT.ID,
      INSTALLMENT.DATE,
      INSTALLMENTSPLIT.ID,
      INSTALLMENTSPLIT.DESIGNATIONID
  from
  (
    select
        PLEDGE.ID,
        PLEDGE.CONSTITUENTID
    from
        FINANCIALTRANSACTION PLEDGE
    where
        (PLEDGE.TYPECODE = 1) and
        (dbo.UFN_PLEDGE_GETBALANCEASOF_2(PLEDGE.ID, @DAYAFTERTODATE) > 0) and
        (cast(PLEDGE.DATE as date) between @FROMDATE and @TODATE) and
        (@SITEFILTERMODE = 0
            or exists (
                select 1
                from dbo.UFN_SITEID_MAPFROM_REVENUEID_2(PLEDGE.ID) REVENUESITE
                inner join dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER on REVENUESITE.SITEID = SITEFILTER.SITEID
            )
        ) and exists (-- Site security filter

                 select HASPERMISSION
                 from dbo.UFN_SITEID_MAPFROM_REVENUEID_2(PLEDGE.ID) REVSITES
                 cross apply dbo.UFN_REPORT_HASUSERSITEPERMISSION(@CURRENTAPPUSERID, '65359276-0c0f-44fa-8b79-e1f2d1cce0c2', REVSITES.SITEID)
        )
  ) PLEDGE
  inner join dbo.INSTALLMENT on PLEDGE.ID = INSTALLMENT.REVENUEID
  inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLIT.INSTALLMENTID = INSTALLMENT.ID
  where (@DESIGNATIONID is null)
     or (@DESIGNATIONID is not null and INSTALLMENTSPLIT.DESIGNATIONID = @DESIGNATIONID);

  if object_id('tempdb..#TMP_PLEDGES_PAYMENTAMOUNT') is not null
    drop table #TMP_PLEDGES_PAYMENTAMOUNT;

  create table #TMP_PLEDGES_PAYMENTAMOUNT (
    ID uniqueidentifier not null,
    PAYMENTAMOUNT money not null  
  );

  insert into #TMP_PLEDGES_PAYMENTAMOUNT
  select
      #TMP_PLEDGES_FILTERED.INSTALLMENTSPLITID,
      SUM(dbo.UFN_INSTALLMENTSPLITPAYMENT_GETAMOUNTINCURRENCY(ISP.ID, @SELECTEDCURRENCYID))
  from
      #TMP_PLEDGES_FILTERED
      inner join INSTALLMENTSPLITPAYMENT ISP on ISP.INSTALLMENTSPLITID = #TMP_PLEDGES_FILTERED.INSTALLMENTSPLITID
      inner join FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.ID = ISP.PAYMENTID
      inner join FINANCIALTRANSACTION FT on FT.ID = FTLI.FINANCIALTRANSACTIONID
  where
      @TODATE >= FT.DATE
  group by #TMP_PLEDGES_FILTERED.INSTALLMENTSPLITID;

  if object_id('tempdb..#TMP_PLEDGES_WRITEOFFAMOUNT') is not null
    drop table #TMP_PLEDGES_WRITEOFFAMOUNT;

  create table #TMP_PLEDGES_WRITEOFFAMOUNT (
      ID uniqueidentifier not null,
      WRITEOFFAMOUNT money not null 
  );

  insert into #TMP_PLEDGES_WRITEOFFAMOUNT
  select
      #TMP_PLEDGES_FILTERED.INSTALLMENTSPLITID,
      SUM(dbo.UFN_INSTALLMENTSPLITWRITEOFF_GETAMOUNTINCURRENCY(ISWO.ID, @SELECTEDCURRENCYID))
  from
      #TMP_PLEDGES_FILTERED
      inner join dbo.INSTALLMENTSPLITWRITEOFF ISWO on ISWO.INSTALLMENTSPLITID = #TMP_PLEDGES_FILTERED.INSTALLMENTSPLITID
      inner join dbo.WRITEOFF WO on WO.ID = ISWO.WRITEOFFID
  where
      @TODATE >= cast(WO.DATE as date)
  group by #TMP_PLEDGES_FILTERED.INSTALLMENTSPLITID;

  select
      PLEDGE.ID PLEDGEID,
      case when @USERGRANTEDTRANSACTIONPAGE = 1 then 'http://www.blackbaud.com/REVENUEID?REVENUEID=' + convert(nvarchar(36),PLEDGE.ID) else null end PLEDGELINK,
      CONSTITUENT.ISORGANIZATION ISORG,
      CONSTITUENT.FIRSTNAME FIRSTNAME,
      CONSTITUENT.KEYNAME LASTNAME,
      'http://www.blackbaud.com/CONSTITUENTID?CONSTITUENTID=' + convert(nvarchar(36),CONSTITUENT.ID) CONSTITUENTLINK,
      CONSTITUENT.ID CONSTITUENTID,
      PHONE.NUMBER PHONENUMBER,
      ADDRESS.DESCRIPTION ADDRESSBLOCK,
      EMAILADDRESS.EMAILADDRESS EMAILADDRESS,
      DESIGNATION.NAME DESIGNATIONNAME,
      DESIGNATION.ID DESIGNATIONID,
      case when @USERGRANTEDDESIGNATIONPAGE = 1 then 'http://www.blackbaud.com/DESIGNATIONID?DESIGNATIONID=' + convert(nvarchar(36),DESIGNATION.ID) else null end DESIGNATIONLINK,
      PLEDGE.INSTALLMENTDATE INSTALLMENTDUEDATES,
      dbo.UFN_INSTALLMENTSPLIT_GETAMOUNTINCURRENCY(PLEDGE.INSTALLMENTSPLITID, @SELECTEDCURRENCYID)  INSTALLMENTAMOUNT,
      PLEDGE.INSTALLMENTID INSTALLMENTID,
      #TMP_PLEDGES_PAYMENTAMOUNT.PAYMENTAMOUNT PAYMENTAMOUNT,
      #TMP_PLEDGES_WRITEOFFAMOUNT.WRITEOFFAMOUNT WRITEOFFAMOUNT,
      @ISOCODE PLEDGEISOCURRENCYCODE,
      @CURRENCYSYMBOL PLEDGECURRENCYSYMBOL,
      @SYMBOLDISPLAYSETTINGCODE PLEDGECURRENCYSYMBOLDISPLAYSETTINGCODE,
      @DECIMALDIGITS PLEDGEDECIMALDIGITS,
      case when CONSTITUENT.ISORGANIZATION = 1 then (case when RELATIONSHIP.ID is null then cast(0 as bit) else cast(1 as bit) end) else cast(0 as bit) end HASPRIMARYCONTACTINFO,
      case when CONSTITUENT.ISORGANIZATION = 1 then PRIMARYCONTACTNAME.KEYNAME else null end PCLASTNAME,
      case when CONSTITUENT.ISORGANIZATION = 1 then PRIMARYCONTACTNAME.FIRSTNAME else null end PCFIRSTNAME,
      case when CONSTITUENT.ISORGANIZATION = 1 then PRIMARYCONTACTEMAIL.EMAILADDRESS else null end PCEMAIL,
      case when CONSTITUENT.ISORGANIZATION = 1 then PRIMARYCONTACTPHONE.NUMBER else null end PCPHONE,
      case when CONSTITUENT.ISORGANIZATION = 1 then PRIMARYCONTACTADDRESS.DESCRIPTION else null end PCADDRESSBLOCK
  from
#TMP_PLEDGES_FILTERED PLEDGE
      inner join dbo.DESIGNATION on DESIGNATION.ID = PLEDGE.INSTALLMENTSPLITDESIGNATIONID
      inner join dbo.CONSTITUENT on CONSTITUENT.ID = PLEDGE.CONSTITUENTID

      left join dbo.ADDRESS on ADDRESS.CONSTITUENTID = CONSTITUENT.ID and ADDRESS.ISPRIMARY =1
      left join dbo.PHONE on PHONE.CONSTITUENTID = CONSTITUENT.ID and PHONE.ISPRIMARY =1
      left join dbo.EMAILADDRESS on EMAILADDRESS.CONSTITUENTID = CONSTITUENT.ID and EMAILADDRESS.ISPRIMARY =1 

      left join dbo.RELATIONSHIP on CONSTITUENT.ISORGANIZATION = 1 and RELATIONSHIP.RECIPROCALCONSTITUENTID = CONSTITUENT.ID and RELATIONSHIP.ISPRIMARYCONTACT = 1
      left join dbo.CONSTITUENT PRIMARYCONTACTNAME on CONSTITUENT.ISORGANIZATION = 1 and PRIMARYCONTACTNAME.ID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID
      left join dbo.ADDRESS PRIMARYCONTACTADDRESS on CONSTITUENT.ISORGANIZATION = 1 and PRIMARYCONTACTADDRESS.CONSTITUENTID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID and PRIMARYCONTACTADDRESS.ISPRIMARY = 1  
      left join dbo.PHONE PRIMARYCONTACTPHONE on CONSTITUENT.ISORGANIZATION = 1 and RELATIONSHIP.RELATIONSHIPCONSTITUENTID = PRIMARYCONTACTPHONE.CONSTITUENTID and PRIMARYCONTACTPHONE.ISPRIMARY = 1  
      left join dbo.EMAILADDRESS PRIMARYCONTACTEMAIL on CONSTITUENT.ISORGANIZATION = 1 and RELATIONSHIP.RELATIONSHIPCONSTITUENTID = PRIMARYCONTACTEMAIL.CONSTITUENTID and PRIMARYCONTACTEMAIL.ISPRIMARY = 1  

      left join #TMP_PLEDGES_PAYMENTAMOUNT on #TMP_PLEDGES_PAYMENTAMOUNT.ID = PLEDGE.INSTALLMENTSPLITID
      left join #TMP_PLEDGES_WRITEOFFAMOUNT on #TMP_PLEDGES_WRITEOFFAMOUNT.ID = PLEDGE.INSTALLMENTSPLITID
      where
      (   --constituent security  

          @ISADMIN = 1 or 
          @APPUSER_IN_NONRACROLE = 1 or
          dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, CONSTITUENT.ID, @APPUSER_IN_NOSECGROUPROLE) = 1
      );

  drop table #TMP_PLEDGES_WRITEOFFAMOUNT;
  drop table #TMP_PLEDGES_PAYMENTAMOUNT;
  drop table #TMP_PLEDGES_FILTERED;

  return 0;