USP_SALESORDERITEMMEMBERSHIP_ONLINE_GENERIC_UPDATE

CREATE procedure USP_SALESORDERITEMMEMBERSHIP_ONLINE_GENERIC_UPDATE (
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @SALESORDERID uniqueidentifier,
    @DESCRIPTION nvarchar(255),
    @QUANTITY decimal,
    @AMOUNT money,
    @DATA xml,
    @OPTIONS xml,
    @CALLBACKURL nvarchar(255),
    @SYSTEMTYPENAME nvarchar(255),
    @ASSEMBLYNAME nvarchar(255),
    @ATTRIBUTES xml,
    @CATEGORYNAME nvarchar(255),
    @ACKNOWLEDGEMENT nvarchar(max),
    @MEMBERSHIPID uniqueidentifier,
    @MEMBERSHIPPROGRAMID uniqueidentifier,
    @MEMBERSHIPLEVELID uniqueidentifier,
    @MEMBERSHIPLEVELTERMID uniqueidentifier,
    @MEMBERSHIPLEVELTYPEID uniqueidentifier,
    @MEMBERSHIPADDONS xml,
    @NUMBEROFCHILDREN tinyint = 0
)
as
set nocount on;

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

declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()

-- handle inserting the data

merge dbo.[SALESORDERITEM] as [TARGET]
using (select @ID as ID) as [SOURCE]
on (TARGET.ID = SOURCE.ID)
when matched then
    update 
    set 
        [SALESORDERID] = @SALESORDERID, 
        [TYPECODE] = 1, 
        [DESCRIPTION] = @DESCRIPTION, 
        [QUANTITY] = 1, 
        [PRICE] = @AMOUNT,
        [CHANGEDBYID] = @CHANGEAGENTID, 
        [DATECHANGED] = @CURRENTDATE,
        [DATA] = @DATA,
        [OPTIONS] = @OPTIONS,
        [CALLBACKURL] = @CALLBACKURL,
        [SYSTEMTYPENAME] = @SYSTEMTYPENAME,
        [ASSEMBLYNAME] = @ASSEMBLYNAME,
        [ATTRIBUTES] = @ATTRIBUTES,
        [CATEGORYNAME] = @CATEGORYNAME,
        [ACKNOWLEDGEMENT] = @ACKNOWLEDGEMENT
    when not matched then
        insert (
            [ID], 
            [SALESORDERID], 
            [TYPECODE], 
            [DESCRIPTION], 
            [QUANTITY], 
            [PRICE], 
            [ADDEDBYID], 
            [CHANGEDBYID], 
            [DATEADDED], 
            [DATECHANGED],
            [DATA],
            [OPTIONS],
            [CALLBACKURL],
            [SYSTEMTYPENAME],
            [ASSEMBLYNAME],
            [ATTRIBUTES],
            [CATEGORYNAME],
            [ACKNOWLEDGEMENT]
        )
        values
        (
            @ID, 
            @SALESORDERID, 
            1, 
            @DESCRIPTION, 
            1, 
            @AMOUNT,
            @CHANGEAGENTID, 
            @CHANGEAGENTID, 
            @CURRENTDATE, 
            @CURRENTDATE,
            @DATA,
            @OPTIONS,
            @CALLBACKURL,
            @SYSTEMTYPENAME,
            @ASSEMBLYNAME,
            @ATTRIBUTES,
            @CATEGORYNAME,
            @ACKNOWLEDGEMENT
        );

    declare @EXPIRATIONDATE datetime;
    declare @ACTIONCODE tinyint;
    select @ACTIONCODE = dbo.UFN_MEMBERSHIPTRANSACTION_DETERMINEACTIONCODE(@MEMBERSHIPID, @MEMBERSHIPLEVELID, @CURRENTDATE)

    if @ACTIONCODE = 0 or @ACTIONCODE = 5 --join/rejoin

        set @EXPIRATIONDATE = dbo.UFN_MEMBERSHIP_CALCULATEEXPIRATIONDATEBYLEVEL(@MEMBERSHIPLEVELID, @MEMBERSHIPLEVELTERMID, @CURRENTDATE);
    else
    begin
        declare @CURRENTSTATUS tinyint;
        select 
            @EXPIRATIONDATE = [EXPIRATIONDATE], 
            @CURRENTSTATUS = [STATUSCODE]
        from dbo.[MEMBERSHIP] 
        where ID = @MEMBERSHIPID;

        if @CURRENTSTATUS = 2 --pending membership                    

            set @EXPIRATIONDATE = dbo.UFN_MEMBERSHIP_CALCULATEEXPIRATIONDATEBYLEVEL(@MEMBERSHIPLEVELID, @MEMBERSHIPLEVELTERMID, @CURRENTDATE);
        else
            set @EXPIRATIONDATE = dbo.UFN_MEMBERSHIP_CALCULATERENEWALEXPIRATION(@EXPIRATIONDATE, @MEMBERSHIPLEVELTERMID);
    end

    declare @MEMBERSHIPLEVELTYPECODEID uniqueidentifier = null;
    if @MEMBERSHIPLEVELTYPEID is not null
        select @MEMBERSHIPLEVELTYPECODEID = [LEVELTYPECODEID] from dbo.[MEMBERSHIPLEVELTYPE] where [ID] = @MEMBERSHIPLEVELTYPEID;

    merge dbo.[SALESORDERITEMMEMBERSHIP] as [TARGET]
    using (select @ID as ID) as [SOURCE]
    on (TARGET.ID = SOURCE.ID)
    when matched then
        update
        set
            [MEMBERSHIPID] = @MEMBERSHIPID,
            [MEMBERSHIPPROGRAMID] = @MEMBERSHIPPROGRAMID,
            [MEMBERSHIPLEVELID] = @MEMBERSHIPLEVELID,
            [MEMBERSHIPLEVELTERMID] = @MEMBERSHIPLEVELTERMID,
            [MEMBERSHIPLEVELTYPECODEID] = @MEMBERSHIPLEVELTYPECODEID,
            [EXPIRATIONDATE] = @EXPIRATIONDATE,
            [NUMBEROFCHILDREN] = @NUMBEROFCHILDREN,
            [CHANGEDBYID] = @CHANGEAGENTID, 
            [DATECHANGED] = @CURRENTDATE
    when not matched then
        insert (
            [ID],
            [MEMBERSHIPID],
            [MEMBERSHIPPROGRAMID],
            [MEMBERSHIPLEVELID],
            [MEMBERSHIPLEVELTERMID],
            [MEMBERSHIPLEVELTYPECODEID],
            [EXPIRATIONDATE],
            [NUMBEROFCHILDREN],
            [ADDEDBYID], 
            [CHANGEDBYID], 
            [DATEADDED], 
            [DATECHANGED]
        )
        values (
            @ID,
            @MEMBERSHIPID,
            @MEMBERSHIPPROGRAMID,
            @MEMBERSHIPLEVELID,
            @MEMBERSHIPLEVELTERMID,
            @MEMBERSHIPLEVELTYPECODEID,
            @EXPIRATIONDATE,
            @NUMBEROFCHILDREN,
            @CHANGEAGENTID, 
            @CHANGEAGENTID, 
            @CURRENTDATE, 
            @CURRENTDATE
        );

    --Handle add-ons

    declare @ADDONS_TABLE table (
        [ID] uniqueidentifier,
        [ADDONID] uniqueidentifier,
        [ADDONNAME] nvarchar(100),
        [ADDONTYPECODE] tinyint,
        [PRICE] money,
        [quantity] smallint
    );

    insert into @ADDONS_TABLE select
        isnull(SALESORDERITEMMEMBERSHIPADDON.ID, newid()),
        ADDON.ID,
        ADDON.NAME,
        ADDON.ADDONTYPECODE,
        T.membershipaddon.value('(PRICE)[1]','money') as 'PRICE',
        T.membershipaddon.value('(QUANTITY)[1]','int') as 'NUMBEROFADDONS'
    from @MEMBERSHIPADDONS.nodes('/MEMBERSHIPADDONS/ITEM') T(membershipaddon)
    inner join dbo.ADDON with (nolock) on
        T.membershipaddon.value('(ADDONID)[1]','uniqueidentifier') = ADDON.ID
    left join dbo.SALESORDERITEMMEMBERSHIPADDON on
        SALESORDERITEMMEMBERSHIPADDON.SALESORDERITEMMEMBERSHIPID = @ID and
        SALESORDERITEMMEMBERSHIPADDON.ADDONID = ADDON.ID

    --delete, add, and update sales order items

    delete dbo.SALESORDERITEM
    from dbo.SALESORDERITEM
    inner join dbo.SALESORDERITEMMEMBERSHIPADDON on
        SALESORDERITEM.ID = SALESORDERITEMMEMBERSHIPADDON.ID
    where 
        SALESORDERITEMMEMBERSHIPADDON.SALESORDERITEMMEMBERSHIPID = @ID and
        not exists (
            select 1
            from @ADDONS_TABLE [ADDONS]
            where SALESORDERITEM.ID = ADDONS.ID
        )

    update dbo.SALESORDERITEM
    set
        [DESCRIPTION] = ADDONS.ADDONNAME,
        QUANTITY = ADDONS.QUANTITY,
        PRICE = ADDONS.PRICE,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
    from dbo.SALESORDERITEM
    inner join @ADDONS_TABLE [ADDONS] on
        SALESORDERITEM.ID = [ADDONS].ID

    insert into dbo.SALESORDERITEM
    (
        ID,
        SALESORDERID,
        TYPECODE,
        [DESCRIPTION],
        QUANTITY,
        PRICE,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED
    )
    (
        select
            [ADDONS].ID,
            @SALESORDERID,
            16,
            [ADDONS].ADDONNAME,
            [ADDONS].QUANTITY,
            [ADDONS].PRICE,
            @CHANGEAGENTID, 
            @CHANGEAGENTID, 
            @CURRENTDATE, 
            @CURRENTDATE
        from @ADDONS_TABLE [ADDONS]
        where not exists (select 1 from dbo.SALESORDERITEM where ID = ADDONS.ID)
    );

    update dbo.SALESORDERITEMMEMBERSHIPADDON
    set
        SALESORDERITEMMEMBERSHIPID = @ID,
        ADDONTYPECODE = ADDONS.ADDONTYPECODE,
        ADDONID = ADDONS.ADDONID,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
    from dbo.SALESORDERITEMMEMBERSHIPADDON
    inner join @ADDONS_TABLE [ADDONS] on
        SALESORDERITEMMEMBERSHIPADDON.ID = [ADDONS].ID

    insert into dbo.SALESORDERITEMMEMBERSHIPADDON
    (
        ID,
        SALESORDERITEMMEMBERSHIPID,
        ADDONTYPECODE,
        ADDONID,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED
    )
    (
        select
            [ADDONS].ID,
            @ID,
            [ADDONS].ADDONTYPECODE,
            [ADDONS].ADDONID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        from @ADDONS_TABLE [ADDONS]
        where not exists (select 1 from dbo.SALESORDERITEMMEMBERSHIPADDON where ID = ADDONS.ID)
    );

    exec dbo.USP_SALESORDER_APPLYITEMDISCOUNTS @SALESORDERID, @CHANGEAGENTID;
    exec dbo.USP_SALESORDER_APPLYORDERDISCOUNTS @SALESORDERID, @CHANGEAGENTID;    
    exec dbo.USP_SALESORDER_CALCULATETAXES @SALESORDERID, @CHANGEAGENTID;  
    exec dbo.USP_SALESORDER_UPDATEMEMBERSHIPPROMOS @SALESORDERID, @CHANGEAGENTID;
return 0;