USP_REPORT_DISBURSEMENT

CREATE procedure dbo.USP_REPORT_DISBURSEMENT
(
    @STARTDATE datetime = null,
    @ENDDATE datetime = null,
    @FINANCIALTRANSACTIONQUERY uniqueidentifier = null,
    @DISBURSEMENTID uniqueidentifier = null,
    @DISBURSEMENTPROCESSID uniqueidentifier = null,
    @REPORTUSERID nvarchar(128) = null,
    @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;

    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 @SQLTOEXEC nvarchar(max);
    declare @DBOBJECTNAME nvarchar(128);
    declare @DBOBJECTTYPE smallint;

    if @FINANCIALTRANSACTIONQUERY is not null
    begin
        if not exists(select ID from dbo.IDSETREGISTER where ID = @FINANCIALTRANSACTIONQUERY) raiserror('ID set does not exist in the database.', 15, 1);
        select @DBOBJECTNAME = DBOBJECTNAME, @DBOBJECTTYPE = OBJECTTYPE from dbo.IDSETREGISTER where ID = @FINANCIALTRANSACTIONQUERY;
        if @DBOBJECTTYPE = 1 set @DBOBJECTNAME = @DBOBJECTNAME + '()';
        else if @DBOBJECTTYPE = 2 set @DBOBJECTNAME = @DBOBJECTNAME + '(''' + convert(nvarchar(36), @FINANCIALTRANSACTIONQUERY) + ''')';
    end

    set @SQLTOEXEC = '
        declare @DISBURSEMENTS table (
            VENDORID nvarchar(200)
            ,NAME nvarchar(100)
            ,PAYMENTMETHOD nvarchar(100)
            ,BANKACCOUNT nvarchar(100)
            ,BANKACCOUNTTRANSACTIONID nvarchar(200)
            ,FINANCIALTRANSACTIONID uniqueidentifier
            ,NUMBER int
            ,DISBURSEMENTDATE date
            ,POSTDATE date
            ,AMOUNT money
            ,STATUS nvarchar(100)
        ); '

    if @DISBURSEMENTPROCESSID is null
    begin
        set @SQLTOEXEC = @SQLTOEXEC + 'insert into @DISBURSEMENTS
                select ''http://www.blackbaud.com?VENDORID='' + CONVERT(nvarchar(36), C.ID) as VENDORID
                    ,C.NAME
                    ,''Check'' PAYMENTMETHOD
                    ,BA.ACCOUNTNAME BANKACCOUNT
                    ,''http://www.blackbaud.com?BANKACCOUNTTRANSACTIONID='' + CONVERT(nvarchar(36), BAT.ID) as BANKACCOUNTTRANSACTIONID
                    ,FT.ID FINANCIALTRANSACTIONID
                    ,CAST(BAT.TRANSACTIONNUMBER as nvarchar) NUMBER
                    ,BAT.TRANSACTIONDATE [DISBURSEMENTDATE]
                    ,BAT.POSTDATE
                    ,BAT.AMOUNT
                    ,CASE WHEN BAT.PROCESSING = 1 THEN ''In Process'' ELSE BAT.STATUS END
                from dbo.BANKACCOUNTTRANSACTION BAT
                inner join dbo.FINANCIALTRANSACTION FT on FT.ID = BAT.ID
                inner join dbo.CONSTITUENT C on C.ID = FT.CONSTITUENTID
                inner join dbo.BANKACCOUNT BA on BA.ID = BAT.BANKACCOUNTID '

            if @FINANCIALTRANSACTIONQUERY is not null
                set @SQLTOEXEC = @SQLTOEXEC + 'inner join dbo.' + @DBOBJECTNAME + ' as SELECTION on BAT.[ID] = SELECTION.[ID]' + nchar(13);

            set @SQLTOEXEC = @SQLTOEXEC + ' where BAT.PROCESSING = 0 and BAT.DELETED = 0
                and ((BAT.TRANSACTIONDATE >= @STARTDATE or @STARTDATE is null) 
                    and (BAT.TRANSACTIONDATE <= @ENDDATE or @ENDDATE is null))
                and (@DISBURSEMENTID is null or BAT.ID = @DISBURSEMENTID)
                and ((@ISADMIN = 1 or @APPUSER_IN_NONRACROLE = 1) or
                    dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, BA.BANKID, @APPUSER_IN_NOSECGROUPROLE) = 1)'
    end
    else
    begin
        set @SQLTOEXEC = @SQLTOEXEC + ' declare @temp table ([ID] uniqueidentifier, [FINANCIALTRANSACTIONID] uniqueidentifier, [ORIGINALNUMBER] int, [CHANGEDNUMBER] int, [FUTUREFINANCIALTRANSACTIONID] uniqueidentifier);

        insert into @temp
        select H.ID, H.[FINANCIALTRANSACTIONID], H.[ORIGINALNUMBER], H.[CHANGEDNUMBER], H.[FUTUREFINANCIALTRANSACTIONID]
        from dbo.DISBURSEMENTHISTORY H
        left outer join dbo.BANKACCOUNTTRANSACTION_EXT D on H.[FINANCIALTRANSACTIONID] = D.ID
        left outer join dbo.DISBURSEMENTPROCESSDISBURSEMENT DPD on H.FINANCIALTRANSACTIONID = DPD.ID
        where D.DISBURSEMENTPROCESSID = @DISBURSEMENTPROCESSID or DPD.DISBURSEMENTPROCESSID = @DISBURSEMENTPROCESSID;

        update @temp
            set FUTUREFINANCIALTRANSACTIONID = T1.FUTUREFINANCIALTRANSACTIONID
        from @temp T
        inner join (select FINANCIALTRANSACTIONID, FUTUREFINANCIALTRANSACTIONID 
            from @temp where FUTUREFINANCIALTRANSACTIONID is not null) T1 on T1.FINANCIALTRANSACTIONID = T.FINANCIALTRANSACTIONID;

        update @temp
            set FUTUREFINANCIALTRANSACTIONID = null
        where FUTUREFINANCIALTRANSACTIONID not in (select FINANCIALTRANSACTIONID from @temp);

        WITH DisbursementHistoryInfo (ID, FINANCIALTRANSACTIONID, ORIGINALID)
        AS
        (
            select H.ID,  H.FINANCIALTRANSACTIONID, H.FINANCIALTRANSACTIONID
            from @temp H
            where H.FUTUREFINANCIALTRANSACTIONID is null

            union all

            select H.ID, HI.FINANCIALTRANSACTIONID, H.FINANCIALTRANSACTIONID
            from @temp H
            inner join DisbursementHistoryInfo HI on H.FUTUREFINANCIALTRANSACTIONID = HI.ORIGINALID and H.FINANCIALTRANSACTIONID not in (HI.ORIGINALID)
        )

        insert into @DISBURSEMENTS
        select ''http://www.blackbaud.com?VENDORID='' + CONVERT(nvarchar(36), C.ID) as VENDORID
            ,C.NAME
            ,''Check'' PAYMENTMETHOD
            ,BA.ACCOUNTNAME BANKACCOUNT
            ,CASE WHEN BAT.ID is null THEN NULL ELSE CASE WHEN BAT.PROCESSING = 0 THEN ''http://www.blackbaud.com?BANKACCOUNTTRANSACTIONID='' + CONVERT(nvarchar(36), BAT.ID) ELSE NULL END END as BANKACCOUNTTRANSACTIONID
            ,FT.ID FINANCIALTRANSACTIONID
            ,ISNULL(ISNULL(cast(BAT.TRANSACTIONNUMBER as nvarchar), 
                (select MAX(CASE WHEN T.CHANGEDNUMBER = 0 
                                THEN T.ORIGINALNUMBER 
                                ELSE T.CHANGEDNUMBER 
                            END) 
                 from @temp T where T.FINANCIALTRANSACTIONID = FT.ID)),
                ROW_NUMBER () over (order by DPD.DATEADDED) + isnull((select MAX(BAT1.TRANSACTIONNUMBER) from dbo.BANKACCOUNTTRANSACTION BAT1 where BAT1.BANKACCOUNTID = BA.ID), 0)) NUMBER
            ,ISNULL(BAT.TRANSACTIONDATE, DP.DISBURSEMENTDATE) [DISBURSEMENTDATE]
            ,ISNULL(BAT.POSTDATE, DP.POSTDATE) [POSTDATE]
            ,ISNULL(BAT.AMOUNT, FT.TRANSACTIONAMOUNT) [AMOUNT]
            ,CASE WHEN DPD.ID is not null or BAT.PROCESSING = 1 THEN ''In Process'' ELSE BAT.STATUS END
        from dbo.FINANCIALTRANSACTION FT
        left outer join dbo.DISBURSEMENTPROCESSDISBURSEMENT DPD on DPD.ID = FT.ID and DPD.DISBURSEMENTPROCESSID = @DISBURSEMENTPROCESSID
        left outer join dbo.BANKACCOUNTTRANSACTION_EXT D on D.ID = FT.ID and D.DISBURSEMENTPROCESSID = @DISBURSEMENTPROCESSID
        left outer join dbo.BANKACCOUNTTRANSACTION BAT on BAT.ID = D.ID
        inner join dbo.DISBURSEMENTPROCESS DP on DP.ID = @DISBURSEMENTPROCESSID
        inner join dbo.BANKACCOUNT BA on BA.ID = BAT.BANKACCOUNTID or BA.ID = DP.BANKACCOUNTID
        inner join dbo.CONSTITUENT C on C.ID = FT.CONSTITUENTID '

        if @FINANCIALTRANSACTIONQUERY is not null
                set @SQLTOEXEC = @SQLTOEXEC + 'inner join dbo.' + @DBOBJECTNAME + ' as SELECTION on BAT.[ID] = SELECTION.[ID]' + nchar(13);

        set @SQLTOEXEC = @SQLTOEXEC + 'where (FT.ID in (select ISNULL(T1.FINANCIALTRANSACTIONID, T.FINANCIALTRANSACTIONID) 
                        from @temp T
                        left outer join (
                            select Distinct ID, FINANCIALTRANSACTIONID 
                            from DisbursementHistoryInfo) T1 on T.ID = T1.ID) 
            or (FT.ID in (select ISNULL(T1.ORIGINALID, T.FINANCIALTRANSACTIONID) 
                        from @temp T
                        left outer join (
                            select Distinct ID, ORIGINALID 
                            from DisbursementHistoryInfo) T1 on T.ID = T1.ID))
            or (FT.ID in (select DPD.ID from dbo.DISBURSEMENTPROCESSDISBURSEMENT DPD
                            where DPD.DISBURSEMENTPROCESSID = @DISBURSEMENTPROCESSID and
                                DPD.ID not in (select ISNULL(T1.FINANCIALTRANSACTIONID, T.FINANCIALTRANSACTIONID) 
                                from @temp T
                                left outer join (
                                    select Distinct ID, FINANCIALTRANSACTIONID 
                                    from DisbursementHistoryInfo) T1 on T.ID = T1.ID))))
            and ((ISNULL(BAT.TRANSACTIONDATE, DP.DISBURSEMENTDATE) >= @STARTDATE or @STARTDATE is null) 
                and (ISNULL(BAT.TRANSACTIONDATE, DP.DISBURSEMENTDATE) <= @ENDDATE or @ENDDATE is null))
            and (@DISBURSEMENTID is null or D.ID = @DISBURSEMENTID)
            and ((@ISADMIN = 1 or @APPUSER_IN_NONRACROLE = 1) or
                    dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, BA.BANKID, @APPUSER_IN_NOSECGROUPROLE) = 1)'
    end
    set @SQLTOEXEC = @SQLTOEXEC + '
        select D.VENDORID
            ,D.NAME
            ,D.PAYMENTMETHOD
            ,D.BANKACCOUNT
            ,D.BANKACCOUNTTRANSACTIONID
            ,D.FINANCIALTRANSACTIONID
            ,D.NUMBER
            ,D.DISBURSEMENTDATE
            ,D.POSTDATE
            ,D.AMOUNT
            ,D.STATUS
            ,FT.TYPE [TRANSACTIONTYPE]
            ,FT.USERDEFINEDID [TRANSACTIONNUMBER]
            ,FT.DESCRIPTION
            ,FT.DATE
            ,I.DATEDUE
            ,CASE WHEN FT.TYPECODE = 101 THEN IDS.AMOUNT ELSE -1 * IDS.AMOUNT END [TRANSACTIONAMOUNT]
        from @DISBURSEMENTS D
        inner join (select IorCM.ID [I_CM_ID], FT.ID [FINANCIALTRANSACTIONID], SUM(FTA.AMOUNT) [AMOUNT]
            from dbo.FINANCIALTRANSACTION FT
            inner join dbo.FINANCIALTRANSACTIONAPPLICATION FTA on FT.ID = FTA.FINANCIALTRANSACTIONID
            inner join dbo.FINANCIALTRANSACTIONSCHEDULE FTS on FTS.ID = FTA.FINANCIALTRANSACTIONSCHEDULEID
            inner join dbo.FINANCIALTRANSACTION IorCM on IorCM.ID = FTS.FINANCIALTRANSACTIONID
            where FT.ID in (select FINANCIALTRANSACTIONID from @DISBURSEMENTS)
            group by IorCM.ID, FT.ID) IDS on IDS.FINANCIALTRANSACTIONID = D.FINANCIALTRANSACTIONID
        inner join dbo.FINANCIALTRANSACTION FT on FT.ID = IDS.I_CM_ID
        left outer join dbo.INVOICE I on I.ID = FT.ID and FT.TYPECODE = 101
        order by CAST(D.NUMBER as int);'

    exec sp_executesql @SQLTOEXEC,
        N'@STARTDATE datetime, @ENDDATE datetime, @DISBURSEMENTID uniqueidentifier, @DISBURSEMENTPROCESSID uniqueidentifier, @ISADMIN bit, @APPUSER_IN_NONRACROLE bit, @APPUSER_IN_NOSECGROUPROLE bit, @CURRENTAPPUSERID uniqueidentifier',
        @STARTDATE=@STARTDATE, @ENDDATE=@ENDDATE, @DISBURSEMENTID=@DISBURSEMENTID, @DISBURSEMENTPROCESSID=@DISBURSEMENTPROCESSID, @ISADMIN=@ISADMIN, @APPUSER_IN_NONRACROLE=@APPUSER_IN_NONRACROLE, @APPUSER_IN_NOSECGROUPROLE=@APPUSER_IN_NOSECGROUPROLE, @CURRENTAPPUSERID=@CURRENTAPPUSERID;