USP_DATAFORMTEMPLATE_EDIT_SALESORDERITEMMEMBERSHIP_MIDTERMUPGRADE

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_SALESORDERITEMMEMBERSHIP_MIDTERMUPGRADE (
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @MEMBERSHIPID uniqueidentifier,
    @MEMBERSHIPLEVELID uniqueidentifier
)
as

    set nocount on;

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

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

declare @SALESORDERID uniqueidentifier;
declare @CURRENTTERMLENGTHCODE tinyint
declare @CURRENTTERMTIMELENGTH tinyint
declare @PRICE money;

declare @MEMBERSHIPPROGRAMID uniqueidentifier;
declare @MEMBERSHIPLEVELTERMID uniqueidentifier;
declare @MEMBERSHIPLEVELTYPECODEID uniqueidentifier;
declare @NUMBEROFCHILDREN smallint;
declare @COMMENTS nvarchar(1000);
declare @ISGIFT bit;
declare @SENDRENEWALCODE tinyint;
declare @EXPIRATIONDATE datetime;
declare @GIVENBYID uniqueidentifier;
declare @NUMBEROFADDONADULTS smallint = 0;
declare @NUMBEROFADDONGUESTS smallint = 0;

begin try

    select @SALESORDERID = SALESORDERID 
    from dbo.SALESORDERITEM
    where ID = @ID

    exec dbo.USP_SALESORDER_ISCOMPLETE_RAISERROR @SALESORDERID, @EXCLUDEGROUPSALES = 1;

    select
        @MEMBERSHIPPROGRAMID = MEMBERSHIP.MEMBERSHIPPROGRAMID,
        @MEMBERSHIPLEVELTYPECODEID = MEMBERSHIP.MEMBERSHIPLEVELTYPECODEID,
        @NUMBEROFCHILDREN = MEMBERSHIP.NUMBEROFCHILDREN,
        @COMMENTS = MEMBERSHIP.COMMENTS,
        @ISGIFT = MEMBERSHIP.ISGIFT,
        @SENDRENEWALCODE = MEMBERSHIP.SENDRENEWALCODE,
        @EXPIRATIONDATE = MEMBERSHIP.EXPIRATIONDATE,
        @GIVENBYID = MEMBERSHIP.GIVENBYID,
        @NUMBEROFADDONADULTS = MEMBERSHIP.NUMBEROFADDONADULTS,
        @NUMBEROFADDONGUESTS = MEMBERSHIP.NUMBEROFADDONGUESTS,
        @CURRENTTERMLENGTHCODE = MEMBERSHIPLEVELTERM.TERMLENGTHCODE,
        @CURRENTTERMTIMELENGTH = MEMBERSHIPLEVELTERM.TERMTIMELENGTH
    from dbo.MEMBERSHIP
        inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIP.MEMBERSHIPLEVELTERMID = MEMBERSHIPLEVELTERM.ID
    where MEMBERSHIP.ID = @MEMBERSHIPID


    select
        @MEMBERSHIPLEVELTERMID = ID
    from dbo.MEMBERSHIPLEVELTERM
    where
        LEVELID = @MEMBERSHIPLEVELID and
        TERMLENGTHCODE = @CURRENTTERMLENGTHCODE and
        TERMTIMELENGTH = @CURRENTTERMTIMELENGTH

    set @PRICE = dbo.UFN_MEMBERSHIP_GETMIDTERMUPGRADEPRICE(@MEMBERSHIPID, @MEMBERSHIPLEVELID)

    if @MEMBERSHIPLEVELTERMID is not null and @PRICE is not null
    begin
        update dbo.SALESORDERITEM set 
            DESCRIPTION = isnull(dbo.UFN_MEMBERSHIPPROGRAM_GETNAME(@MEMBERSHIPPROGRAMID) + ' - ' +
                                    dbo.UFN_MEMBERSHIPLEVEL_GETNAME(@MEMBERSHIPLEVELID) + ' (' +
                                    dbo.UFN_MEMBERSHIPLEVELTERM_GETVALUE(@MEMBERSHIPLEVELTERMID) + '): ', '') + 'Upgrade',
            PRICE = @PRICE,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where ID = @ID

        update dbo.SALESORDERITEMMEMBERSHIP set 
            MEMBERSHIPID = @MEMBERSHIPID,
            MEMBERSHIPPROGRAMID = @MEMBERSHIPPROGRAMID,
            MEMBERSHIPLEVELID = @MEMBERSHIPLEVELID,
            MEMBERSHIPLEVELTERMID = @MEMBERSHIPLEVELTERMID,
            MEMBERSHIPLEVELTYPECODEID = @MEMBERSHIPLEVELTYPECODEID,
            NUMBEROFCHILDREN = @NUMBEROFCHILDREN,
            COMMENTS = @COMMENTS,
            ISGIFT = @ISGIFT,
            SENDRENEWALCODE = @SENDRENEWALCODE,
            GIVENBYID = @GIVENBYID,
            EXPIRATIONDATE = @EXPIRATIONDATE,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
       where ID = @ID

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

return 0;