USP_ORDER_COPYSPLITS

CREATE procedure dbo.USP_ORDER_COPYSPLITS
(
    @SOURCEID uniqueidentifier,
    @DESTINATIONID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier,
    @DATEADDED datetime,
    @DESTINATIONAMOUNT money = null,
    @APPLICATIONCODE tinyint = null
)
as
set nocount on;

begin try
    if @CHANGEAGENTID is null
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    if @DESTINATIONAMOUNT is null
        select @DESTINATIONAMOUNT = AMOUNT from dbo.REVENUE where ID = @DESTINATIONID ;

    declare @MAPPING table
    (
        REVENUESPLITID uniqueidentifier,
        PAYMENTSPLITID uniqueidentifier,
        AMOUNT money,
        TYPECODE tinyint,
        APPLICATIONCODE tinyint,
        DESIGNATIONID uniqueidentifier,
        SOURCELINEITEMID uniqueidentifier,
        CREDITAMOUNT money,
        ORIGINALAPPLICATIONCODE tinyint
    );

    declare @WEIGHTSUM decimal(30, 5);
    declare @IDEALAMOUNT decimal(30, 5);
    declare @AMOUNTDISTRIBUTEDIDEAL decimal(30, 5);
    declare @AMOUNTDISTRIBUTED decimal(30, 5);
    declare @SOURCETYPE tinyint;

    declare @WEIGHTAMOUNT decimal(30, 5);
    declare @DESIGNATIONID uniqueidentifier;
    declare @APPLICATIONID uniqueidentifier;
    declare @SOURCELINEITEMID uniqueidentifier;
    declare @CREDITAMOUNT money;
    declare @ORIGINALAPPLICATIONCODE tinyint;
    declare @CONTRIBUTEDTOTAL money;
    declare @MEMBERSHIPLEVELID uniqueidentifier;

    declare @CONTRIBUTEDREVENUE table (
        ID uniqueidentifier,
        DESIGNATIONID uniqueidentifier,
        AMOUNT money
    );

    set @AMOUNTDISTRIBUTEDIDEAL = 0;
    set @AMOUNTDISTRIBUTED = 0;

    insert into @MAPPING(REVENUESPLITID, PAYMENTSPLITID, AMOUNT, TYPECODE, APPLICATIONCODE, DESIGNATIONID, SOURCELINEITEMID, CREDITAMOUNT, ORIGINALAPPLICATIONCODE)
    select
        FINANCIALTRANSACTIONLINEITEM.ID,
        newid(),
        FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT,
        REVENUESPLIT_EXT.TYPECODE,
        @APPLICATIONCODE,
        REVENUESPLIT_EXT.DESIGNATIONID,
        FINANCIALTRANSACTIONLINEITEM.SOURCELINEITEMID,
        coalesce((
                select sum((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS)
                from dbo.FINANCIALTRANSACTIONLINEITEM LI
                inner join dbo.CREDITITEM_EXT EXT on EXT.ID = LI.ID
                inner join dbo.FINANCIALTRANSACTION FT on FT.ID = LI.FINANCIALTRANSACTIONID
                where
                    LI.SOURCELINEITEMID = REVENUESPLIT_EXT.ID
                    and LI.TYPECODE = 5  -- Discount

                    and FT.TYPECODE in (5, 99)  -- Discount, Orphaned

        ), 0),
        REVENUESPLIT_EXT.APPLICATIONCODE
    from dbo.REVENUESPLIT_EXT with (nolock)
        inner join dbo.FINANCIALTRANSACTIONLINEITEM with (nolock) on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
    where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @SOURCEID
        and FINANCIALTRANSACTIONLINEITEM.TYPECODE = 0
        and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null;

    declare MEMBERSHIPSPLITS cursor local fast_forward for
        select REVENUESPLITID, AMOUNT, SOURCELINEITEMID, CREDITAMOUNT, ORIGINALAPPLICATIONCODE
        from @MAPPING MEMBERSHIPS
        where ORIGINALAPPLICATIONCODE = 5 and
                CREDITAMOUNT > 0 and
                exists (select * from @MAPPING DONATIONS where DONATIONS.SOURCELINEITEMID = MEMBERSHIPS.REVENUESPLITID and DONATIONS.ORIGINALAPPLICATIONCODE = 0)

    open MEMBERSHIPSPLITS
    fetch next from MEMBERSHIPSPLITS into @APPLICATIONID, @WEIGHTAMOUNT, @SOURCELINEITEMID, @CREDITAMOUNT, @ORIGINALAPPLICATIONCODE;
    while @@FETCH_STATUS = 0
    begin
        set @CONTRIBUTEDTOTAL = 0;
        select @CONTRIBUTEDTOTAL = SUM(AMOUNT)
        from @MAPPING DONATIONS where DONATIONS.SOURCELINEITEMID = @APPLICATIONID and DONATIONS.ORIGINALAPPLICATIONCODE = 0;

        if @CONTRIBUTEDTOTAL >= @CREDITAMOUNT
        begin
            set @CONTRIBUTEDTOTAL -= @CREDITAMOUNT;
        end
        else
        begin
            set @WEIGHTAMOUNT -= (@CREDITAMOUNT - @CONTRIBUTEDTOTAL);
            set @CONTRIBUTEDTOTAL = 0;
        end

        set @CREDITAMOUNT = 0;

        if @CONTRIBUTEDTOTAL > 0
        begin
            select @MEMBERSHIPLEVELID = MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID
            from dbo.MEMBERSHIPTRANSACTION
            where MEMBERSHIPTRANSACTION.REVENUESPLITID = @APPLICATIONID;

            insert into @CONTRIBUTEDREVENUE(ID, DESIGNATIONID, AMOUNT)
            select
                @APPLICATIONID,
                CONTRIBUTEDAMOUNTS.DESIGNATIONID,
                CONTRIBUTEDAMOUNTS.AMOUNT
            from dbo.UFN_MEMBERSHIP_GETCONTRIBUTEDAMOUNTSBYLEVELID(@MEMBERSHIPLEVELID, @CONTRIBUTEDTOTAL, 2) as CONTRIBUTEDAMOUNTS;
        end
        else
            delete @MAPPING where SOURCELINEITEMID = @APPLICATIONID and ORIGINALAPPLICATIONCODE = 0;

        update @MAPPING set AMOUNT = @WEIGHTAMOUNT, CREDITAMOUNT = 0 where REVENUESPLITID = @APPLICATIONID;

        fetch next from MEMBERSHIPSPLITS into @APPLICATIONID, @WEIGHTAMOUNT, @SOURCELINEITEMID, @CREDITAMOUNT, @ORIGINALAPPLICATIONCODE;
    end

    close MEMBERSHIPSPLITS
    deallocate MEMBERSHIPSPLITS;

    if exists (select ID from @CONTRIBUTEDREVENUE)
        update @MAPPING
        set AMOUNT = CONTRIBUTEDREVENUE.AMOUNT
        from @MAPPING MAPPING
        inner join @CONTRIBUTEDREVENUE CONTRIBUTEDREVENUE
            on MAPPING.SOURCELINEITEMID is not null and
                MAPPING.SOURCELINEITEMID = CONTRIBUTEDREVENUE.ID and
                MAPPING.DESIGNATIONID is not null and
                MAPPING.DESIGNATIONID = CONTRIBUTEDREVENUE.DESIGNATIONID;

    select
        @WEIGHTSUM = dbo.UFN_SALESORDER_TOTAL(SALESORDER.ID)
    from dbo.SALESORDER with (nolock)
    where SALESORDER.REVENUEID = @SOURCEID;

    declare PAYMENTSPLITS cursor local fast_forward for
        select REVENUESPLITID, AMOUNT, CREDITAMOUNT
        from @MAPPING;

    open PAYMENTSPLITS;
    fetch next from PAYMENTSPLITS into @APPLICATIONID, @WEIGHTAMOUNT, @CREDITAMOUNT;

    while @@FETCH_STATUS = 0
    begin

        set @WEIGHTAMOUNT -= @CREDITAMOUNT;

        if @WEIGHTSUM <> 0
            set @IDEALAMOUNT = (@WEIGHTAMOUNT / @WEIGHTSUM) * @DESTINATIONAMOUNT;
        else
            set @IDEALAMOUNT = 0;

        set @WEIGHTAMOUNT = ROUND(@IDEALAMOUNT + @AMOUNTDISTRIBUTEDIDEAL - @AMOUNTDISTRIBUTED, 2);

        update @MAPPING
        set AMOUNT = @WEIGHTAMOUNT
        where REVENUESPLITID = @APPLICATIONID;

        set @AMOUNTDISTRIBUTEDIDEAL += @IDEALAMOUNT;
        set @AMOUNTDISTRIBUTED += @WEIGHTAMOUNT;

        fetch next from PAYMENTSPLITS into @APPLICATIONID, @WEIGHTAMOUNT, @CREDITAMOUNT;
    end

    deallocate PAYMENTSPLITS;

    -- Don't create splits for items that have been discounted to 0

    delete from @MAPPING where AMOUNT = 0;


    declare @POSTSTATUSCODE tinyint;

    select  @POSTSTATUSCODE = case when POSTSTATUSCODE = 3 then 3 else 1 end
    from dbo.FINANCIALTRANSACTION where ID = @DESTINATIONID;


    declare @TRANSACTIONDATE datetime;

    if @POSTSTATUSCODE <> 3 begin
        select
            @TRANSACTIONDATE = cast(DATE as date)
        from
            dbo.FINANCIALTRANSACTION with (nolock)
        where
            ID = @SOURCEID;
    end

    insert into dbo.FINANCIALTRANSACTIONLINEITEM (
        ID,
        FINANCIALTRANSACTIONID,
        BASEAMOUNT,
        TRANSACTIONAMOUNT,
        ORGAMOUNT,
        TYPECODE,
        POSTSTATUSCODE,
        POSTDATE,
        SOURCELINEITEMID,
        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
    )
    select
        MAP.PAYMENTSPLITID,
        @DESTINATIONID,
        MAP.AMOUNT,
        MAP.AMOUNT,
        MAP.AMOUNT,
        0,
        @POSTSTATUSCODE,
        @TRANSACTIONDATE,
        MAP.REVENUESPLITID,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @DATEADDED,
        @DATEADDED
    from @MAPPING MAP;

    insert into dbo.REVENUESPLIT_EXT (
        ID,
        APPLICATIONCODE,
        TYPECODE,
        DESIGNATIONID,
        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
    )
    select
        MAP.PAYMENTSPLITID,
        MAP.APPLICATIONCODE,
        MAP.TYPECODE,
        MAP.DESIGNATIONID,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @DATEADDED,
        @DATEADDED
    from @MAPPING MAP;

    insert into dbo.REVENUESPLITORDER(ID, PROGRAMID, EVENTID, FEEID, TAXID, MEMBERSHIPLEVELID, RESOURCEID, VOLUNTEERTYPEID, EVENTLOCATIONID, MERCHANDISEPRODUCTINSTANCEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    select MAP.PAYMENTSPLITID,
        SOURCE.PROGRAMID,
        SOURCE.EVENTID,
        SOURCE.FEEID,
        SOURCE.TAXID,
        SOURCE.MEMBERSHIPLEVELID,
        SOURCE.RESOURCEID,
        SOURCE.VOLUNTEERTYPEID,
        SOURCE.EVENTLOCATIONID,
        SOURCE.MERCHANDISEPRODUCTINSTANCEID,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @DATEADDED,
        @DATEADDED
    from dbo.REVENUESPLITORDER SOURCE
    inner join @MAPPING MAP on SOURCE.ID = MAP.REVENUESPLITID
    where MAP.TYPECODE in (1,2,5,6,7,10,11,14,16);

end try
begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
end catch
return 0;