USP_RECURRINGGIFT_FIXMISSINGINSTALLMENTSCHEDULE

CREATE procedure dbo.USP_RECURRINGGIFT_FIXMISSINGINSTALLMENTSCHEDULE (
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @CURRENTDATE datetime = null
)
as
begin
    if @CHANGEAGENTID is null
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    if @CURRENTDATE is null
        set @CURRENTDATE = getdate();

    begin try
        --------------------------------------------------------------------------------

        -- create paid installments for all payments


        insert into dbo.RECURRINGGIFTINSTALLMENT 
            (ID, REVENUEID, AMOUNT, DATE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, BASECURRENCYID,ORGANIZATIONAMOUNT,ORGANIZATIONEXCHANGERATEID,TRANSACTIONAMOUNT,TRANSACTIONCURRENCYID,BASEEXCHANGERATEID)
        select 
            RECURRINGGIFTACTIVITY.ID, 
            RECURRINGGIFTACTIVITY.SOURCEREVENUEID, 
            case when REVENUE.BASEEXCHANGERATEID is not null 
                then dbo.UFN_CURRENCY_CONVERT(RECURRINGGIFTACTIVITY.AMOUNT, REVENUE.BASEEXCHANGERATEID)
                else RECURRINGGIFTACTIVITY.AMOUNT
            end,
            RECURRINGGIFTACTIVITY.SCHEDULEDATE, 
            @CHANGEAGENTID, 
            @CHANGEAGENTID, 
            @CURRENTDATE, 
            @CURRENTDATE,
            REVENUE.BASECURRENCYID,
            case when REVENUE.BASEEXCHANGERATEID is not null and REVENUE.ORGANIZATIONEXCHANGERATEID is not null 
                    then dbo.UFN_CURRENCY_CONVERT(dbo.UFN_CURRENCY_CONVERT(RECURRINGGIFTACTIVITY.AMOUNT, REVENUE.BASEEXCHANGERATEID), REVENUE.ORGANIZATIONEXCHANGERATEID) -- convert from transaction to base to organization currency

                when REVENUE.BASEEXCHANGERATEID is not null
                    then dbo.UFN_CURRENCY_CONVERT(RECURRINGGIFTACTIVITY.AMOUNT, REVENUE.BASEEXCHANGERATEID) -- org and base currencies are the same

                when REVENUE.ORGANIZATIONEXCHANGERATEID is not null
                    then dbo.UFN_CURRENCY_CONVERT(RECURRINGGIFTACTIVITY.AMOUNT, REVENUE.ORGANIZATIONEXCHANGERATEID) -- transaction and base currencies are the same

                else RECURRINGGIFTACTIVITY.AMOUNT
            end,
            REVENUE.ORGANIZATIONEXCHANGERATEID,
            RECURRINGGIFTACTIVITY.AMOUNT,
            REVENUE.TRANSACTIONCURRENCYID,
            REVENUE.BASEEXCHANGERATEID
        from 
            dbo.RECURRINGGIFTACTIVITY
            inner join dbo.REVENUE on RECURRINGGIFTACTIVITY.SOURCEREVENUEID = REVENUE.ID
        where 
            RECURRINGGIFTACTIVITY.SOURCEREVENUEID = @ID
            and RECURRINGGIFTACTIVITY.TYPECODE = 0;

        insert into dbo.RECURRINGGIFTINSTALLMENTPAYMENT 
            (ID, RECURRINGGIFTINSTALLMENTID, PAYMENTID, AMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, APPLICATIONCURRENCYID, APPLICATIONEXCHANGERATEID)
        select 
            newid(), 
            RECURRINGGIFTACTIVITY.ID, -- this is the ID of the installment created above

            REVENUESPLIT.REVENUEID, 
            RECURRINGGIFTACTIVITY.AMOUNT, 
            @CHANGEAGENTID, 
            @CHANGEAGENTID, 
            @CURRENTDATE, 
            @CURRENTDATE,
            RECURRINGGIFTACTIVITY.APPLICATIONCURRENCYID,
            RECURRINGGIFTACTIVITY.APPLICATIONEXCHANGERATEID --TODO: DON'T KNOW IF WE NEED THIS

        from 
            dbo.RECURRINGGIFTACTIVITY
            inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = RECURRINGGIFTACTIVITY.PAYMENTREVENUEID
        where 
            RECURRINGGIFTACTIVITY.SOURCEREVENUEID = @ID
            and RECURRINGGIFTACTIVITY.TYPECODE = 0;

        --------------------------------------------------------------------------------

        -- create written-off installments for all skips


        insert into dbo.RECURRINGGIFTINSTALLMENT 
            (ID, REVENUEID, AMOUNT, DATE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, BASECURRENCYID,ORGANIZATIONAMOUNT,ORGANIZATIONEXCHANGERATEID,TRANSACTIONAMOUNT,TRANSACTIONCURRENCYID,BASEEXCHANGERATEID)
        select 
            newid(), 
            [SKIP].SOURCEREVENUEID, 
            case when REVENUE.BASEEXCHANGERATEID is not null 
                then dbo.UFN_CURRENCY_CONVERT([SKIP].AMOUNT, REVENUE.BASEEXCHANGERATEID)
                else [SKIP].AMOUNT
            end,
            [SKIP].SCHEDULEDATE, 
            @CHANGEAGENTID, 
            @CHANGEAGENTID, 
            @CURRENTDATE, 
            @CURRENTDATE,
            REVENUE.BASECURRENCYID,
            case when REVENUE.BASEEXCHANGERATEID is not null and REVENUE.ORGANIZATIONEXCHANGERATEID is not null 
                    then dbo.UFN_CURRENCY_CONVERT(dbo.UFN_CURRENCY_CONVERT([SKIP].AMOUNT, REVENUE.BASEEXCHANGERATEID), REVENUE.ORGANIZATIONEXCHANGERATEID) -- convert from transaction to base to organization currency

                when REVENUE.BASEEXCHANGERATEID is not null
                    then dbo.UFN_CURRENCY_CONVERT([SKIP].AMOUNT, REVENUE.BASEEXCHANGERATEID) -- org and base currencies are the same

                when REVENUE.ORGANIZATIONEXCHANGERATEID is not null
                    then dbo.UFN_CURRENCY_CONVERT([SKIP].AMOUNT, REVENUE.ORGANIZATIONEXCHANGERATEID) -- transaction and base currencies are the same

                else [SKIP].AMOUNT
            end,
            REVENUE.ORGANIZATIONEXCHANGERATEID,
            [SKIP].AMOUNT,
            REVENUE.TRANSACTIONCURRENCYID,
            REVENUE.BASEEXCHANGERATEID
        from 
            dbo.RECURRINGGIFTACTIVITY as [SKIP]
            inner join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = [SKIP].SOURCEREVENUEID
            inner join dbo.REVENUE on [SKIP].SOURCEREVENUEID = REVENUE.ID
        where 
            [SKIP].SOURCEREVENUEID = @ID
            and [SKIP].TYPECODE = 1
            -- ignore skips that are not earlier than the next transaction date, since they must have been rolled back

            and [SKIP].SCHEDULEDATE < REVENUESCHEDULE.NEXTTRANSACTIONDATE
            -- ignore skips that are later paid, since they must have been rolled back

            -- only use the latest skip for a given scheduledate

            and not exists
            (
                select 'x'
                from 
                    dbo.RECURRINGGIFTACTIVITY as [SUPERCEDE]
                where 
                    [SUPERCEDE].SOURCEREVENUEID = [SKIP].SOURCEREVENUEID
                   and [SUPERCEDE].SCHEDULEDATE = [SKIP].SCHEDULEDATE
                   and ([SUPERCEDE].TYPECODE = 0 or ([SUPERCEDE].TYPECODE = 1 and [SUPERCEDE].DATEADDED > [SKIP].DATEADDED))
            );

        --TODO: Add multicurrency fields when we address write-offs

        insert into dbo.RECURRINGGIFTWRITEOFF 
            (ID, REVENUEID, DATE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        select 
            newid(), 
            [SKIP].SOURCEREVENUEID, 
            [SKIP].SCHEDULEDATE, 
            @CHANGEAGENTID, 
            @CHANGEAGENTID, 
            @CURRENTDATE, 
            @CURRENTDATE
        from 
            dbo.RECURRINGGIFTACTIVITY as [SKIP]
            inner join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = [SKIP].SOURCEREVENUEID
        where 
            [SKIP].SOURCEREVENUEID = @ID
            and [SKIP].TYPECODE = 1
            and [SKIP].SCHEDULEDATE < REVENUESCHEDULE.NEXTTRANSACTIONDATE
            and not exists
            (
                select 'x'
                from 
                    dbo.RECURRINGGIFTACTIVITY as [SUPERCEDE]
                where 
                    [SUPERCEDE].SOURCEREVENUEID = [SKIP].SOURCEREVENUEID
                    and [SUPERCEDE].SCHEDULEDATE = [SKIP].SCHEDULEDATE
                    and ([SUPERCEDE].TYPECODE = 0 or ([SUPERCEDE].TYPECODE = 1 and [SUPERCEDE].DATEADDED > [SKIP].DATEADDED))
            );

        insert into dbo.RECURRINGGIFTINSTALLMENTWRITEOFF 
            (ID, RECURRINGGIFTINSTALLMENTID, WRITEOFFID, AMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        select 
            newid(), 
            RECURRINGGIFTINSTALLMENT.ID, 
            RECURRINGGIFTWRITEOFF.ID, 
            RECURRINGGIFTINSTALLMENT.AMOUNT, 
            @CHANGEAGENTID, 
            @CHANGEAGENTID, 
            @CURRENTDATE, 
            @CURRENTDATE
        from 
            dbo.RECURRINGGIFTINSTALLMENT
            inner join dbo.RECURRINGGIFTWRITEOFF on RECURRINGGIFTWRITEOFF.REVENUEID = RECURRINGGIFTINSTALLMENT.REVENUEID and RECURRINGGIFTWRITEOFF.DATE = RECURRINGGIFTINSTALLMENT.DATE
        where 
            RECURRINGGIFTINSTALLMENT.REVENUEID = @ID;

        --------------------------------------------------------------------------------

        -- create next installment


        insert into dbo.RECURRINGGIFTINSTALLMENT 
            (ID, REVENUEID, AMOUNT, DATE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, BASECURRENCYID,ORGANIZATIONAMOUNT,ORGANIZATIONEXCHANGERATEID,TRANSACTIONAMOUNT,TRANSACTIONCURRENCYID,BASEEXCHANGERATEID)
        select 
            newid(), 
            REVENUE.ID, 
            REVENUE.AMOUNT, 
            REVENUESCHEDULE.NEXTTRANSACTIONDATE, 
            @CHANGEAGENTID, 
            @CHANGEAGENTID, 
            @CURRENTDATE, 
            @CURRENTDATE,
            REVENUE.BASECURRENCYID,
            REVENUE.ORGANIZATIONAMOUNT,
            REVENUE.ORGANIZATIONEXCHANGERATEID,
            REVENUE.TRANSACTIONAMOUNT,
            REVENUE.TRANSACTIONCURRENCYID,
            REVENUE.BASEEXCHANGERATEID
        from 
            dbo.REVENUE
            inner join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = REVENUE.ID
        where 
            REVENUE.ID = @ID
            and REVENUE.TRANSACTIONTYPECODE = 2
            and REVENUESCHEDULE.STATUSCODE in (0,5)
            and REVENUESCHEDULE.NEXTTRANSACTIONDATE is not null
            and (REVENUESCHEDULE.ENDDATE is null or REVENUESCHEDULE.ENDDATE > REVENUESCHEDULE.NEXTTRANSACTIONDATE);
    end try
    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch

    return 0;
end