USP_REPORT_MATCHINGGIFTSUMMARY_WITHIDSET

/*
Generated by Blackbaud Application Framework
Date: 7/12/2019 5:26:46 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.2103.0, Culture=neutral, PublicKeyToken=null
*/
CREATE procedure dbo.USP_REPORT_MATCHINGGIFTSUMMARY_WITHIDSET (@IDSETID uniqueidentifier = null, @STARTDATE datetime, @ENDDATE datetime, @REPORTUSERID nvarchar(128), @CURRENCYCODE tinyint, @ALTREPORTUSERID nvarchar(128))
with execute as owner
as
  set nocount on;

  declare @r int;

  if @IDSETID is null
    begin
      exec @r = dbo.[USP_REPORT_MATCHINGGIFTSUMMARY] @STARTDATE, @ENDDATE, @REPORTUSERID, @CURRENCYCODE, @ALTREPORTUSERID;
    end
  else
    begin
      if not exists(select [ID] from dbo.[IDSETREGISTER] where [ID] = @IDSETID)
        raiserror('ID set does not exist in the database.', 15, 1);

      declare @DBOBJECTNAME nvarchar(max);
      declare @DBOBJECTTYPE smallint;
      declare @SQLTOEXEC nvarchar(max);

      select
        @DBOBJECTNAME = [DBOBJECTNAME],
        @DBOBJECTTYPE = [OBJECTTYPE]
      from dbo.[IDSETREGISTER]
      where [ID] = @IDSETID;

      if left(@DBOBJECTNAME, 1) <> '['
        set @DBOBJECTNAME = '[' + @DBOBJECTNAME + ']';

      if @DBOBJECTTYPE = 1
        begin
          set @DBOBJECTNAME = @DBOBJECTNAME + '(';
          set @DBOBJECTNAME = @DBOBJECTNAME + ')';
        end
      else
        begin
          if @DBOBJECTTYPE = 2
            set @DBOBJECTNAME = @DBOBJECTNAME + '(''' + convert(nvarchar(36), @IDSETID) + ''')';
        end

      set @SQLTOEXEC = cast(N'set nocount on;

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

            set @CURRENTAPPUSERID = dbo.UFN_APPUSER_GETREPORTAPPUSERID(@REPORTUSERID, @ALTREPORTUSERID);
            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 @CURRENTDATE datetime = getdate();
            declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
            declare @SELECTEDCURRENCYID uniqueidentifier;
            declare @DECIMALDIGITS tinyint;
            declare @ROUNDINGTYPECODE tinyint;
            declare @ORIGINCODE tinyint;

            select @ORIGINCODE = coalesce(ORGANIZATIONAMOUNTORIGINCODE, 0) from dbo.MULTICURRENCYCONFIGURATION;

            if @CURRENCYCODE = 1
            begin
                set @SELECTEDCURRENCYID = @ORGANIZATIONCURRENCYID;
                select
                    @DECIMALDIGITS = DECIMALDIGITS,
                    @ROUNDINGTYPECODE = ROUNDINGTYPECODE
                from
                    dbo.UFN_CURRENCY_GETPROPERTIES(@SELECTEDCURRENCYID);
            end

            select
                [PLEDGEID],
                ISNULL(sum([AMOUNT]), 0.0) as [TOTALPAID]
            into #V_INSTALLMENTPAYMENT_TOTALPAID
            from dbo.INSTALLMENTPAYMENT
            group by
                    [PLEDGEID];

            select
                FINANCIALTRANSACTION.ID as [REVENUEID],
                ISNULL(#V_INSTALLMENTPAYMENT_TOTALPAID.[TOTALPAID], 0.0) as [TOTALPAID],
                PLEDGEBALANCES.BALANCEINCURRENCY as [BALANCE]
            into #INACTIVEPARTIALLYPAIDMATCHINGGIFTCLAIMBALANCE
            from dbo.FINANCIALTRANSACTION
            inner join
                dbo.REVENUEMATCHINGGIFT on dbo.FINANCIALTRANSACTION.ID = dbo.REVENUEMATCHINGGIFT.ID
            left join
                #V_INSTALLMENTPAYMENT_TOTALPAID ON FINANCIALTRANSACTION.ID = #V_INSTALLMENTPAYMENT_TOTALPAID.PLEDGEID
            left join
                dbo.UFN_PLEDGE_GETBALANCEASOFINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, @CURRENTDATE, @ORIGINCODE, @CURRENCYCODE) PLEDGEBALANCES on PLEDGEBALANCES.ID = FINANCIALTRANSACTION.ID
            where REVENUEMATCHINGGIFT.ISACTIVE = 0
                    and FINANCIALTRANSACTION.DELETEDON is null
                    and FINANCIALTRANSACTION.TYPECODE = 3;

            select
                COMPANYNAME = COMPANY.NAME,
                COMPANYKEYNAME = COMPANY.KEYNAME,
                CONSTITUENTNAME = CONSTITUENT_NF.NAME,
                MATCHEDPAYMENTS = case @CURRENCYCODE when 0 then CONSTITRD.BASEAMOUNT when 2 then CONSTITRD.TRANSACTIONAMOUNT else CONSTITRD.ORGAMOUNT end,
                PLEDGEAMOUNT = case @CURRENCYCODE when 0 then REVENUE.BASEAMOUNT when 2 then REVENUE.TRANSACTIONAMOUNT else REVENUE.ORGAMOUNT end,
                SUMPAYMENTS = coalesce(PLEDGEAMOUNTSPAID.AMOUNTPAIDINCURRENCY, 0),
                SUMWRITEOFFS = coalesce(WO.AMOUNT, 0),
                BALANCE = PLEDGEBALANCES.BALANCEINCURRENCY - isnull(#INACTIVEPARTIALLYPAIDMATCHINGGIFTCLAIMBALANCE.BALANCE, 0.0),
                ''http://www.blackbaud.com/CONSTITUENTID?CONSTITUENTID='' + CONVERT(nvarchar(36),CONSTITUENT.ID) as CONSTITUENTID,
                MGCURRENCYPROPERTIES.ISO4217 [MGISOCURRENCYCODE],
                MGCURRENCYPROPERTIES.CURRENCYSYMBOL [MGCURRENCYSYMBOL],
                MGCURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [MGCURRENCYSYMBOLDISPLAYSETTINGCODE],
                MGCURRENCYPROPERTIES.DECIMALDIGITS [MGDECIMALDIGITS],
                CONSTITRDCURRENCYPROPERTIES.ISO4217 [CONSTITRDISOCURRENCYCODE],
                CONSTITRDCURRENCYPROPERTIES.CURRENCYSYMBOL [CONSTITRDCURRENCYSYMBOL],
                CONSTITRDCURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [CONSTITRDCURRENCYSYMBOLDISPLAYSETTINGCODE],
                CONSTITRDCURRENCYPROPERTIES.DECIMALDIGITS [CONSTITRDDECIMALDIGITS]
            from
                dbo.CONSTITUENT COMPANY
             inner join dbo.' as nvarchar(max)) + @DBOBJECTNAME + cast(N' as [IDSET_e1ddb93f_aa1f_419c_bd61_7d619c84b579] on [COMPANY].[ID] = [IDSET_e1ddb93f_aa1f_419c_bd61_7d619c84b579].[ID] 
            inner join
                dbo.FINANCIALTRANSACTION REVENUE with (nolock) on COMPANY.ID = REVENUE.CONSTITUENTID
            left outer join  dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on REVENUE.ID = V.FINANCIALTRANSACTIONID
            inner join
                dbo.REVENUEMATCHINGGIFT RMG on REVENUE.ID = RMG.ID
            inner join
                dbo.FINANCIALTRANSACTION CONSTITRD with (nolock) on RMG.MGSOURCEREVENUEID = CONSTITRD.ID
            left outer join  dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V_CONSTITRD with (noexpand) on CONSTITRD.ID = V_CONSTITRD.FINANCIALTRANSACTIONID
            inner join
                dbo.CONSTITUENT with (nolock) on CONSTITRD.CONSTITUENTID = CONSTITUENT.ID
            left join
                dbo.UFN_PLEDGE_GETBALANCEASOFINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, @CURRENTDATE, @ORIGINCODE, @CURRENCYCODE) PLEDGEBALANCES on PLEDGEBALANCES.ID = REVENUE.ID
            left join
                dbo.UFN_PLEDGE_GETAMOUNTPAIDINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, @CURRENTDATE, @ORIGINCODE, @CURRENCYCODE) PLEDGEAMOUNTSPAID on PLEDGEAMOUNTSPAID.PLEDGEID = REVENUE.ID
            outer apply
                dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 0 then V.BASECURRENCYID when 2 then REVENUE.TRANSACTIONCURRENCYID else dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() end) MGCURRENCYPROPERTIES
            outer apply
                dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 0 then V_CONSTITRD.BASECURRENCYID when 2 then CONSTITRD.TRANSACTIONCURRENCYID else dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() end) CONSTITRDCURRENCYPROPERTIES
            outer apply
                dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) CONSTITUENT_NF
            left join
                (select sum(INSTALLMENTSPLITWRITEOFF.AMOUNTINCURRENCY) AMOUNT, WRITEOFF.PARENTID [REVENUEID]
                    from dbo.UFN_INSTALLMENTSPLITWRITEOFF_GETAMOUNTSINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, @ORIGINCODE, @CURRENCYCODE) INSTALLMENTSPLITWRITEOFF
                    inner join dbo.FINANCIALTRANSACTION WRITEOFF on INSTALLMENTSPLITWRITEOFF.WRITEOFFID = WRITEOFF.ID
                    where WRITEOFF.TYPECODE = 20 and WRITEOFF.DELETEDON is null
                    group by WRITEOFF.PARENTID) WO on WO.REVENUEID = REVENUE.ID
            left join
                #INACTIVEPARTIALLYPAIDMATCHINGGIFTCLAIMBALANCE on REVENUE.ID = #INACTIVEPARTIALLYPAIDMATCHINGGIFTCLAIMBALANCE.REVENUEID
            where 
                REVENUE.TYPECODE = 3 and REVENUE.DELETEDON is null and CONSTITRD.DELETEDON is null
                and (@STARTDATE is null or cast(REVENUE.DATE as datetime) >= dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE))
                and (@ENDDATE is null or cast(REVENUE.DATE as datetime) <= dbo.UFN_DATE_GETLATESTTIME(@ENDDATE))
                and REVENUE.ID not in (select REVENUEID from #INACTIVEPARTIALLYPAIDMATCHINGGIFTCLAIMBALANCE where TOTALPAID = 0.0)
                and not (REVENUE.BASEAMOUNT = 0 and REVENUE.TRANSACTIONAMOUNT > 0)
                and not (CONSTITRD.BASEAMOUNT = 0 and CONSTITRD.TRANSACTIONAMOUNT > 0)
                and (@ISADMIN = 1 or 
                        @APPUSER_IN_NONRACROLE = 1 or
                            (dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, COMPANY.ID, @APPUSER_IN_NOSECGROUPROLE) = 1
                            and dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, CONSTITUENT.ID, @APPUSER_IN_NOSECGROUPROLE) = 1))
                and exists (select 1 from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT where REVENUESPLIT.FINANCIALTRANSACTIONID = REVENUE.ID and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 1
                            and exists
                            (
                                select HASPERMISSION
                                from dbo.UFN_SITEID_MAPFROM_REVENUEID(REVENUE.ID) REVSITES
                                cross apply dbo.UFN_REPORT_HASUSERSITEPERMISSION(@CURRENTAPPUSERID, ''5DC00A99-AD1C-428F-8FB7-8467FA19BDCB'', REVSITES.SITEID)
                            ))
                and exists (select 1 from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT where REVENUESPLIT.FINANCIALTRANSACTIONID = CONSTITRD.ID and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 1
                            and exists
                            (
                                select HASPERMISSION
                                from dbo.UFN_SITEID_MAPFROM_REVENUEID(REVENUE.ID) REVSITES
                                cross apply dbo.UFN_REPORT_HASUSERSITEPERMISSION(@CURRENTAPPUSERID, ''5DC00A99-AD1C-428F-8FB7-8467FA19BDCB'', REVSITES.SITEID)
                            ))
            order by
                COMPANY.KEYNAME, COMPANY.KEYNAMEPREFIX, CONSTITUENT.KEYNAME, CONSTITUENT.FIRSTNAME, CONSTITUENT.MIDDLENAME;

            drop table #INACTIVEPARTIALLYPAIDMATCHINGGIFTCLAIMBALANCE;
            drop table #V_INSTALLMENTPAYMENT_TOTALPAID;' as nvarchar(max));

      exec @r = sp_executesql @SQLTOEXEC, N'@STARTDATE datetime, @ENDDATE datetime, @REPORTUSERID nvarchar(128), @CURRENCYCODE tinyint, @ALTREPORTUSERID nvarchar(128)', @STARTDATE = @STARTDATE, @ENDDATE = @ENDDATE, @REPORTUSERID = @REPORTUSERID, @CURRENCYCODE = @CURRENCYCODE, @ALTREPORTUSERID = @ALTREPORTUSERID;
    end

  return @r;