USP_SALESORDER_UPDATEMEMBERSHIPPROMOS

CREATE procedure dbo.USP_SALESORDER_UPDATEMEMBERSHIPPROMOS
(
    @SALESORDERID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier    = null
)
as
begin
    if @CHANGEAGENTID is null  
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

    -- Clear out the existing promos

    declare @e int;
    declare @contextCache varbinary(128);

    set @contextCache = CONTEXT_INFO();

    if not @CHANGEAGENTID is null
        set CONTEXT_INFO @CHANGEAGENTID

    delete 
        from dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION
    where
        SALESORDERITEMID in (select ID from dbo.SALESORDERITEM where SALESORDERID = @SALESORDERID) and
        MEMBERSHIPPROMOID is not null

    if not @contextCache is null
        set CONTEXT_INFO @contextCache

    select @e=@@error;

    if @e<>0 return -456; --always return non-zero sp result if an error occurs


    if exists (select 1 from dbo.SALESORDERMEMBERSHIPPROMO where SALESORDERID = @SALESORDERID) -- Only do stuff if there are membership promotions

    begin
        if exists (select 1 from dbo.SALESORDERITEM where SALESORDERID = @SALESORDERID and TYPECODE = 1) -- If there is a membership in the cart, do this!

        begin

            declare @PROMOVALUES table
            (
                MEMBERSHIPPROMOID uniqueidentifier,
                VALUE money
            )

            -- First, make sure the percentages are calculated correctly

            declare @MEMTOTAL money
            select @MEMTOTAL = sum(PRICE) from dbo.SALESORDERITEM where SALESORDERID = @SALESORDERID and TYPECODE = 1

            insert into @PROMOVALUES
            select
                MP.ID,
                @MEMTOTAL * MP.[PERCENT] * 0.01
            from dbo.SALESORDERMEMBERSHIPPROMO SOMP
                inner join dbo.MEMBERSHIPPROMO MP on SOMP.MEMBERSHIPPROMOID = MP.ID
            where 
                SOMP.SALESORDERID = @SALESORDERID and
                MP.PROMOTIONTYPECODE = 0 and
                MP.DISCOUNTCALCULATIONTYPECODE = 1

            -- Next, update the quantities on the amount discounts

            declare @MEMQUANTITY int
            select @MEMQUANTITY = count(*) from dbo.SALESORDERITEM where SALESORDERID = @SALESORDERID and TYPECODE = 1 and TOTAL > 0

            insert into @PROMOVALUES
            select
                MP.ID,
                MP.AMOUNT * @MEMQUANTITY
            from dbo.SALESORDERMEMBERSHIPPROMO SOMP
                inner join dbo.MEMBERSHIPPROMO MP on SOMP.MEMBERSHIPPROMOID = MP.ID
            where 
                SOMP.SALESORDERID = @SALESORDERID and
                MP.PROMOTIONTYPECODE = 0 and
                MP.DISCOUNTCALCULATIONTYPECODE = 0

            declare @CURRENTPROMO uniqueidentifier
            declare @PROMONAME nvarchar(100)
            declare @PROMOAMOUNT money
            declare @PROMOPERCENT decimal(5,2)
            declare @PROMOEXTTYPE tinyint
            declare @PROMOEXTVALUE int

            -- Now, find the biggest amount/percent discount

            select top(1)
                @CURRENTPROMO = MEMBERSHIPPROMOID
            from @PROMOVALUES
            order by VALUE desc

            if not (@CURRENTPROMO is null)
            begin

                select
                    @PROMONAME = NAME,
                    @PROMOAMOUNT = case DISCOUNTCALCULATIONTYPECODE when 0 then AMOUNT else null end,
                    @PROMOPERCENT = case DISCOUNTCALCULATIONTYPECODE when 1 then [PERCENT] else null end
                from dbo.MEMBERSHIPPROMO
                where ID = @CURRENTPROMO

                declare @MEMITEMS table
                (
                    SALESORDERITEMID uniqueidentifier,
                    CURRENTPRICE money,
                    PROMOAMOUNT money
                )

                insert into @MEMITEMS
                (
                    SALESORDERITEMID,
                    CURRENTPRICE,
                    PROMOAMOUNT
                )
                select
                    SOI.ID,
                    SOI.TOTAL - coalesce(sum(SOIMIP.AMOUNT),0),
                    case
                        when @PROMOAMOUNT is null then SOI.TOTAL * @PROMOPERCENT * 0.01
                        else @PROMOAMOUNT
                    end
                from dbo.SALESORDERITEM SOI
                    left join dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION SOIMIP on SOIMIP.SALESORDERITEMID = SOI.ID
                where
                    SOI.SALESORDERID = @SALESORDERID and
                    SOI.TYPECODE = 1 and
                    SOI.TOTAL > 0
                group by SOI.ID, SOI.TOTAL

                insert into dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION
                (
                    SALESORDERITEMID,
                    PROMOTIONNAME,
                    AMOUNT,
                    MEMBERSHIPPROMOID,
                    ADDEDBYID,
                    CHANGEDBYID,
                    DATEADDED,
                    DATECHANGED
                )
                select
                    SALESORDERITEMID,
                    @PROMONAME,
                    case
                        when PROMOAMOUNT > CURRENTPRICE then CURRENTPRICE
                        else round(PROMOAMOUNT, 2)
                    end,
                    @CURRENTPROMO,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    getdate(),
                    getdate()
                from @MEMITEMS
            end

            -- Now find the best term extension promo

            set @CURRENTPROMO = null;

            select top(1)
                @CURRENTPROMO = MP.ID
            from dbo.SALESORDERMEMBERSHIPPROMO SOMP
                inner join dbo.MEMBERSHIPPROMO MP on SOMP.MEMBERSHIPPROMOID = MP.ID
            where
                SOMP.SALESORDERID = @SALESORDERID and
                MP.PROMOTIONTYPECODE = 1
            order by
                case MP.EXTENSIONCALCULATIONTYPECODE
                    when 0 then EXTENSIONVALUE
                    else 30 * EXTENSIONVALUE
                end desc

            if not (@CURRENTPROMO is null)
            begin

                select
                    @PROMONAME = NAME,
                    @PROMOEXTTYPE = EXTENSIONCALCULATIONTYPECODE,
                    @PROMOEXTVALUE = EXTENSIONVALUE
                from dbo.MEMBERSHIPPROMO
                where ID = @CURRENTPROMO

                insert into dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION
                (
                    SALESORDERITEMID,
                    PROMOTIONNAME,
                    AMOUNT,
                    MEMBERSHIPPROMOID,
                    EXTENSIONCALCULATIONTYPECODE,
                    EXTENSIONVALUE,
                    ADDEDBYID,
                    CHANGEDBYID,
                    DATEADDED,
                    DATECHANGED
                )
                select
                    SOI.ID,
                    @PROMONAME,
                    0,
                    @CURRENTPROMO,
                    @PROMOEXTTYPE,
                    @PROMOEXTVALUE,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    getdate(),
                    getdate()
                from dbo.SALESORDERITEM SOI
                where
                    SOI.SALESORDERID = @SALESORDERID and
                    SOI.TYPECODE = 1
            end
        end
    end

    return 0
end