USP_DATAFORMTEMPLATE_EDIT_MEMBERSHIPTRANSACTION_3

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_MEMBERSHIPTRANSACTION_3
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @MEMBERSHIPLEVELID uniqueidentifier,
    @MEMBERSHIPLEVELTERMID uniqueidentifier,
    @TRANSACTIONDATE datetime,
    @EXPIRATIONDATE datetime,
    @CANCELLATIONREASONCODE uniqueidentifier,
    @COMMENTS nvarchar(1000)
)
as
    set nocount on;

    if @CHANGEAGENTID is null
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    --get old transaction date, membership id, actioncode

    declare @OLDTRANSDATE datetime,
            @OLDEXPRDATE datetime,
            @MEMBERSHIPID uniqueidentifier,
            @ACTIONCODE tinyint,
            @DATEADDED datetime;

    select
        @OLDTRANSDATE = TRANSACTIONDATE,
        @OLDEXPRDATE = EXPIRATIONDATE,
        @MEMBERSHIPID = MEMBERSHIPID,
        @ACTIONCODE = ACTIONCODE,
        @DATEADDED = DATEADDED
    from dbo.MEMBERSHIPTRANSACTION
    where MEMBERSHIPTRANSACTION.ID = @ID;

    --get bounds for transaction date and expr date

    declare @PREVTRANSDATE datetime,
            @NEXTTRANSDATE datetime,
            @PREVEXPRDATE datetime,
            @NEXTEXPRDATE datetime,
            @PREVLEVELID uniqueidentifier,
            @NEXTLEVELID uniqueidentifier,
            @NEXTTRANSID uniqueidentifier,
            @PREVACTIONCODE tinyint,
            @NEXTACTIONCODE tinyint;

    select top 1
        @PREVTRANSDATE = TRANSACTIONDATE,
        @PREVEXPRDATE = EXPIRATIONDATE,
        @PREVLEVELID = MEMBERSHIPLEVELID,
        @PREVACTIONCODE = ACTIONCODE
    from
        dbo.MEMBERSHIPTRANSACTION
    where
        MEMBERSHIPID = @MEMBERSHIPID
        and (
            TRANSACTIONDATE < @OLDTRANSDATE
            or (
                TRANSACTIONDATE = @OLDTRANSDATE
                and DATEADDED < @DATEADDED
            )
        )
    order by
        TRANSACTIONDATE desc, DATEADDED desc;

    select top 1
        @NEXTTRANSDATE = TRANSACTIONDATE,
        @NEXTEXPRDATE = EXPIRATIONDATE,
        @NEXTLEVELID = MEMBERSHIPLEVELID,
        @NEXTACTIONCODE = ACTIONCODE,
        @NEXTTRANSID = ID
    from
        dbo.MEMBERSHIPTRANSACTION
    where
        MEMBERSHIPID = @MEMBERSHIPID
        and (
            TRANSACTIONDATE > @OLDTRANSDATE
            or (
                TRANSACTIONDATE = @OLDTRANSDATE
                and DATEADDED > @DATEADDED
            )
        )
    order by
        TRANSACTIONDATE asc, DATEADDED asc;

    -- check to make sure our dates are not breaking the bounds set by any 

    -- transactions before or after the one we are changing.


    begin try
    if @TRANSACTIONDATE is null
        raiserror('The transaction date must have a value.',13,1)

    if @EXPIRATIONDATE is null and @NEXTEXPRDATE is not null
        raiserror('The expiration date must have a value unless it is the latest transaction.',13,1)

    set @EXPIRATIONDATE = dbo.UFN_DATE_GETLATESTTIME(@EXPIRATIONDATE);
    set @TRANSACTIONDATE = dbo.UFN_DATE_GETEARLIESTTIME(@TRANSACTIONDATE);

    if @EXPIRATIONDATE <= @TRANSACTIONDATE  
        raiserror('BBERR_EXPIRATIONDATE', 13, 1);

    -- Update cancellation reason code

    if @ACTIONCODE = 6 -- Cancelled

    begin
        update dbo.MEMBERSHIP set
            CANCELLATIONREASONCODEID = @CANCELLATIONREASONCODE
        where
            ID = @MEMBERSHIPID
    end

    if (
        (@TRANSACTIONDATE >= @PREVTRANSDATE or @PREVTRANSDATE is null)
        and (@TRANSACTIONDATE <= @NEXTTRANSDATE or @NEXTTRANSDATE is null)
        and (
            (
                (@EXPIRATIONDATE >= @PREVEXPRDATE or @PREVEXPRDATE is null)
                and (@EXPIRATIONDATE <= @NEXTEXPRDATE or @NEXTEXPRDATE is null)
            )
            or (@EXPIRATIONDATE is null and @NEXTEXPRDATE is null)
        )
    )
    begin

    -- if transaction date or expiration date is changed

    -- need to decide the action code for the current transaction

    -- and the action code for the next transaction

    if @TRANSACTIONDATE <> @OLDTRANSDATE or @EXPIRATIONDATE <> @OLDEXPRDATE or @PREVLEVELID <> @MEMBERSHIPLEVELID
    begin
        set @ACTIONCODE = case @ACTIONCODE
                when 4 then 4
                else
                    dbo.UFN_MEMBERSHIPTRANSACTION_DETERMINENEWACTIONCODE(@PREVLEVELID,
                                                                            @PREVEXPRDATE,
                                                                            @PREVACTIONCODE,
                                                                            @MEMBERSHIPLEVELID,
                                                                            @TRANSACTIONDATE)
                end

        set @NEXTACTIONCODE = case @NEXTACTIONCODE
                when 4 then 4
                else
                    dbo.UFN_MEMBERSHIPTRANSACTION_DETERMINENEWACTIONCODE(@MEMBERSHIPLEVELID,
                                                                            @EXPIRATIONDATE,
                                                                            @ACTIONCODE,
                                                                            @NEXTLEVELID,
                                                                            @NEXTTRANSDATE)
                end

        update dbo.MEMBERSHIPTRANSACTION set
            ACTIONCODE = @NEXTACTIONCODE,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where ID = @NEXTTRANSID;
    end

    update dbo.MEMBERSHIPTRANSACTION set
            MEMBERSHIPLEVELID = @MEMBERSHIPLEVELID,
            MEMBERSHIPLEVELTERMID = @MEMBERSHIPLEVELTERMID,
            TRANSACTIONDATE = @TRANSACTIONDATE,
            EXPIRATIONDATE = @EXPIRATIONDATE,
            ACTIONCODE = @ACTIONCODE,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE,
            COMMENTS = @COMMENTS
        where ID = @ID;
    end
    else
        raiserror('Transaction and expiration dates must stay in order with the other transactions.',13,1)

        --get the id of the latest transaction for this membership


        declare @LATESTTRANSACTIONID uniqueidentifier
        select top 1 @LATESTTRANSACTIONID = ID
        from dbo.MEMBERSHIPTRANSACTION
        where MEMBERSHIPID = @MEMBERSHIPID
        order by TRANSACTIONDATE desc, DATEADDED desc 

        --the next block does the following logic

        --if its the latest transaction, and a renew, then update level, term, expiration, last renewed

        --if its latest transaction, and a join, update joindate, expiration date, level, term

        --if its not the latest transaction, but it is a join, update joindate


        if @LATESTTRANSACTIONID = @ID
        begin
            declare @MEMBERSHIPSTATUSCODE tinyint;
            select @MEMBERSHIPSTATUSCODE = STATUSCODE from dbo.UFN_MEMBERSHIPTRANSACTION_CALCULATEMEMBERSHIPSTATUSCODE(@LATESTTRANSACTIONID, @CURRENTDATE);

            if @ACTIONCODE in (1,2,3,5)
            begin
                update dbo.MEMBERSHIP set
                    MEMBERSHIPLEVELID = @MEMBERSHIPLEVELID,
                    MEMBERSHIPLEVELTERMID = @MEMBERSHIPLEVELTERMID,
                    EXPIRATIONDATE = @EXPIRATIONDATE,
                    STATUSCODE = @MEMBERSHIPSTATUSCODE,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE,
                    LASTRENEWEDON = @TRANSACTIONDATE
                where ID = @MEMBERSHIPID
            end
            else if @ACTIONCODE = 0
                begin
                    update dbo.MEMBERSHIP set
                        MEMBERSHIPLEVELID = @MEMBERSHIPLEVELID,
                        MEMBERSHIPLEVELTERMID = @MEMBERSHIPLEVELTERMID,
                        JOINDATE = @TRANSACTIONDATE,
                        EXPIRATIONDATE = @EXPIRATIONDATE,
                        STATUSCODE = @MEMBERSHIPSTATUSCODE,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                    where ID = @MEMBERSHIPID
                end
        end
        else
            if @ACTIONCODE = 0
                begin
                    update dbo.MEMBERSHIP set
                        JOINDATE = @TRANSACTIONDATE,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                    where ID = @MEMBERSHIPID
                end

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

return 0;