USP_SALESORDER_CALCULATEFEES

CREATE procedure dbo.USP_SALESORDER_CALCULATEFEES
(
    @ORDERID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null
)
as
    set nocount on;

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

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    declare @SALESMETHODID uniqueidentifier
    declare @DELIVERYMETHODID uniqueidentifier
    declare @SALESMETHODTYPECODE tinyint

    select 
        @SALESMETHODID = SALESMETHOD.ID,
        @DELIVERYMETHODID = SALESORDER.DELIVERYMETHODID,
        @SALESMETHODTYPECODE = SALESORDER.SALESMETHODTYPECODE
    from
        dbo.SALESORDER
    inner join
        dbo.SALESMETHOD on SALESMETHOD.TYPECODE = SALESORDER.SALESMETHODTYPECODE
    where
        SALESORDER.ID = @ORDERID

    declare @ORDERTICKET table
    (
        ID uniqueidentifier,
        PROGRAMID uniqueidentifier
    )

    declare @FEES table
    (
        ID uniqueidentifier,
        FEEID uniqueidentifier,
        ITEMLINKID uniqueidentifier,
        APPLIESTOCODE int,
        AMOUNT money,
        [PERCENT] decimal(7,4),
        NAME nvarchar(255),
        ITEMTYPECODE tinyint,
        TYPECODE tinyint,
        PRICE money,
        NEWFEE bit
    )

    insert into @ORDERTICKET
    select
        SALESORDERITEM.ID,
        SALESORDERITEMTICKET.PROGRAMID
    from dbo.SALESORDERITEM
    inner join dbo.SALESORDERITEMTICKET on
        SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
    where
        SALESORDERITEM.SALESORDERID = @ORDERID

    if @@rowcount > 0 begin
        ---     ORDER FEES    ---


        -- Per order Sales Method fees

        insert into @FEES
        (
            FEEID,
            APPLIESTOCODE,
            AMOUNT,
            [PERCENT],
            NAME,
            ITEMTYPECODE,
            TYPECODE
        )
        select
            FEE.ID,
            FEE.APPLIESTOCODE,
            FEE.AMOUNT,
            FEE.[PERCENT],
            FEE.NAME,
            1 as ITEMTYPECODE, -- Type is sales method

            FEE.TYPECODE
        from
            dbo.SALESMETHODFEE
        inner join
            dbo.FEE on FEE.ID = SALESMETHODFEE.FEEID
        where
            SALESMETHODFEE.SALESMETHODID = @SALESMETHODID
            and FEE.APPLIESTOCODE = 0  -- Order

            and FEE.ISACTIVE = 1
            and SALESMETHODFEE.FEEID not in (select FEEID from dbo.SALESORDERFEEDELETED where SALESORDERID = @ORDERID)

        -- Per order Delivery Method fees

        insert into @FEES
        (
            FEEID,
            APPLIESTOCODE,
            AMOUNT,
            [PERCENT],
            NAME,
            ITEMTYPECODE,
            TYPECODE
        )
        select
            FEE.ID,
            FEE.APPLIESTOCODE,
            FEE.AMOUNT,
            FEE.[PERCENT],
            FEE.NAME,
            0 as ITEMTYPECODE, -- Type is delivery method

            FEE.TYPECODE
        from
            dbo.DELIVERYMETHODFEE
        inner join
            dbo.FEE on FEE.ID = DELIVERYMETHODFEE.FEEID
        where
            DELIVERYMETHODFEE.DELIVERYMETHODID = @DELIVERYMETHODID
            and FEE.APPLIESTOCODE = 0
            and FEE.ISACTIVE = 1
            -- No duplicate order-wide fees

            and DELIVERYMETHODFEE.FEEID not in (select FEEID from @FEES)
            and DELIVERYMETHODFEE.FEEID not in (select FEEID from dbo.SALESORDERFEEDELETED where SALESORDERID = @ORDERID)

        ---     ITEM FEES    ---


        -- Per item program fees

        insert into @FEES
        (
            FEEID,
            ITEMLINKID,
            APPLIESTOCODE,
            AMOUNT,
            [PERCENT],
            NAME,
            ITEMTYPECODE,
            TYPECODE
        )
        select
            FEE.ID,
            OT.ID as ITEMLINKID,
            FEE.APPLIESTOCODE,
            FEE.AMOUNT,
            FEE.[PERCENT],
            FEE.NAME,
            2 as ITEMTYPECODE,
            FEE.TYPECODE
        from dbo.PROGRAMFEE
        inner join
            dbo.FEE on FEE.ID = PROGRAMFEE.FEEID
        inner join
            @ORDERTICKET OT on OT.PROGRAMID = PROGRAMFEE.PROGRAMID
        where
            FEE.APPLIESTOCODE = 1
            and FEE.ISACTIVE = 1
            and PROGRAMFEE.FEEID not in (select FEEID from dbo.SALESORDERFEEDELETED where SALESORDERID = @ORDERID)

        -- Per item sales method fees

        insert into @FEES
        (
            FEEID,
            ITEMLINKID,
            APPLIESTOCODE,
            AMOUNT,
            [PERCENT],
            NAME,
            ITEMTYPECODE,
            TYPECODE
        )
        select
            FEE.ID,
            OT.ID as ITEMLINKID,
            FEE.APPLIESTOCODE,
            FEE.AMOUNT,
            FEE.[PERCENT],
            FEE.NAME,
            1 as ITEMTYPECODE,
            FEE.TYPECODE
        from
            dbo.SALESMETHODFEE
        inner join
            dbo.FEE on FEE.ID = SALESMETHODFEE.FEEID
        outer apply
            @ORDERTICKET OT
        where
            FEE.APPLIESTOCODE = 1
            and FEE.ISACTIVE = 1
            and SALESMETHODFEE.SALESMETHODID = @SALESMETHODID
            -- No duplicate fees on an item

            and SALESMETHODFEE.FEEID not in (select FEEID from @FEES where ITEMLINKID = OT.ID)
            and SALESMETHODFEE.FEEID not in (select FEEID from dbo.SALESORDERFEEDELETED where SALESORDERID = @ORDERID)

        -- Per item delivery method fees

        insert into @FEES
        (
            FEEID,
            ITEMLINKID,
            APPLIESTOCODE,
            AMOUNT,
            [PERCENT],
            NAME,
            ITEMTYPECODE,
            TYPECODE
        )
        select
            FEE.ID,
            OT.ID as ITEMLINKID,
            FEE.APPLIESTOCODE,
            FEE.AMOUNT,
            FEE.[PERCENT],
            FEE.NAME,
            0 as ITEMTYPECODE,
            FEE.TYPECODE
        from dbo.DELIVERYMETHODFEE
        inner join dbo.FEE on FEE.ID = DELIVERYMETHODFEE.FEEID
        outer apply @ORDERTICKET OT
        where
            FEE.APPLIESTOCODE = 1
            and FEE.ISACTIVE = 1
            and DELIVERYMETHODFEE.DELIVERYMETHODID = @DELIVERYMETHODID
            -- No duplicate fees on an item

            and DELIVERYMETHODFEE.FEEID not in (select FEEID from @FEES where ITEMLINKID = OT.ID)
    end

    -- Rate scale fees do not require sales order items to exist on the order

    if @SALESMETHODTYPECODE = 3 begin
        insert into @FEES
        (
            FEEID,
            APPLIESTOCODE,
            AMOUNT,
            [PERCENT],
            NAME,
            ITEMTYPECODE,
            TYPECODE
        )
        select
            FEE.ID,
            FEE.APPLIESTOCODE,
            0.0,
            0.0,
            FEE.NAME,
            3 as ITEMTYPECODE, -- Type is rate scale

            FEE.TYPECODE
        from dbo.FEE    
        where 
            FEE.APPLIESTOCODE = 0 and
            FEE.ISACTIVE = 1 and
            exists
            (
                select 1 from dbo.RESERVATIONRATESCALEAPPLICATION RRSA
                inner join dbo.RESERVATIONRATESCALE RRS on
                    RRS.ID = RRSA.RESERVATIONRATESCALEID
                where
                    RRS.ID = @ORDERID and
                    RRSA.FEEID = FEE.ID and
                    (
                        INCLUDEALLFEES = 1 or
                        exists
                        (
                            select 1 from dbo.RESERVATIONRATESCALEFEE RRSF
                            where
                                RRSF.RESERVATIONRATESCALEID = RRS.ID and
                                RRSF.FEEID = FEE.ID
                        )
                    )
            ) 
            and
            not exists
            (    -- No duplicate order-wide fees

                select 1 from @FEES where FEEID = FEE.ID
            ) and
            not exists
            (
                select 1 from dbo.SALESORDERFEEDELETED 
                where SALESORDERID = @ORDERID and 
                [SALESORDERFEEDELETED].FEEID = FEE.ID
            )
    end

    update @FEES set
        PRICE = dbo.UFN_SALESORDERITEMFEE_CALCULATE_2(
            @ORDERID,
            FEEID,
            [PERCENT],
            ITEMLINKID
        )

    delete @FEES
    where PRICE = 0;

    -- Retrieve the IDs of the Fees that already exist in the table 

    update @FEES set
        ID = SOIFEE.ID,
        NEWFEE = 0
    from dbo.SALESORDERITEMFEE SOIFEE
    inner join dbo.SALESORDERITEM on
        SALESORDERITEM.ID = SOIFEE.ID
    where
        SALESORDERITEM.SALESORDERID = @ORDERID and
        [@FEES].FEEID = SOIFEE.FEEID and
        (
            [@FEES].ITEMLINKID = SOIFEE.SALESORDERITEMID or
            ([@FEES].ITEMLINKID is null and SOIFEE.SALESORDERITEMID is null)
        )

    --JustinMe 11/9/2009 Fixing deadlock for bug#65775

    declare @SALESORDERITEMSTOBEDELETED table (ID uniqueidentifier)
    insert into @SALESORDERITEMSTOBEDELETED
    select ID from dbo.SALESORDERITEM with (nolock)
    where 
        SALESORDERID = @ORDERID and
        TYPECODE = 3 and
        not exists (
            select *
            from @FEES
            where ID = SALESORDERITEM.ID
        )

    if @@rowcount > 0 begin
        --delete fees in table that are not in the collection

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

        set @contextCache = CONTEXT_INFO();
        set CONTEXT_INFO @CHANGEAGENTID;

        delete from dbo.SALESORDERITEM with (rowlock)
        where ID in (select ID from @SALESORDERITEMSTOBEDELETED)

        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

    end

    if exists (select * from @FEES) begin
        -- Prepare new fees

        update @FEES set 
            ID = newid(),
            NEWFEE = 1
        where ID is null

        if @@rowcount > 0 begin
            -- add fees to the table that are in this collection but not in the table            

            insert into dbo.SALESORDERITEM
            ( 
                ID, 
                SALESORDERID,
                TYPECODE,
                DESCRIPTION,
                QUANTITY,
                PRICE,
                [PERCENT],
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED,
                ASSEMBLYNAME,
                SYSTEMTYPENAME 
            )
            select
                ID,
                @ORDERID as SALESORDERID,
                3 as TYPECODE,
                NAME as DESCRIPTION,
                1 as QUANTITY,
                PRICE,
                case TYPECODE
                    when 0 then 0 
                    when 1 then [PERCENT] 
                end,
                @CHANGEAGENTID, 
                @CHANGEAGENTID, 
                @CURRENTDATE, 
                @CURRENTDATE,
                case @SALESMETHODTYPECODE when 2 then 'Blackbaud.AppFx.CMS.FCL.ShoppingCart.SalesOrder' else '' end,
                case @SALESMETHODTYPECODE when 2 then 'Blackbaud.AppFx.ContentManagement.FrameworkClassLibrary.ShoppingCart.SalesOrder.FeeItem' else '' end
            from @FEES
            where NEWFEE = 1

            insert into dbo.SALESORDERITEMFEE
            ( 
                ID, 
                SALESORDERITEMID,    
                FEEID, 
                FEENAME,
                TYPECODE,
                APPLIESTOCODE,
                ADDEDBYID, CHANGEDBYID,    DATEADDED, DATECHANGED
            )
            select
                FEES.ID,
                FEES.ITEMLINKID as SALESORDERITEMID,
                FEES.FEEID,
                FEE.NAME,
                FEES.ITEMTYPECODE,
                FEES.APPLIESTOCODE,
                @CHANGEAGENTID, @CHANGEAGENTID,    @CURRENTDATE, @CURRENTDATE
            from
                @FEES FEES
            inner join
                dbo.FEE ON FEE.ID = FEES.FEEID
            where FEES.NEWFEE = 1
        end

        update dbo.SALESORDERITEM with (rowlock) set 
            PRICE = FEES.PRICE,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        from @FEES [FEES]
        where
            SALESORDERID = @ORDERID and
            SALESORDERITEM.TYPECODE = 3 and
            [FEES].ID = SALESORDERITEM.ID and
            FEES.NEWFEE = 0 and
            SALESORDERITEM.PRICE <> FEES.PRICE
    end

    return 0;