DanTheMan
Member
Ok, here is the problem, this stored procedure is returning weird results. It will total the cart with tax sometimes, and then other times it wont. The biggest problem is the shipping, it isnt working right at all. It was working fine 3 days ago, although I would get an occasional error. Today it flat out stopped working. It is inputing 12.00 for shipping instead of 4.75. I am testing everything, but lately I have been inserting US for the country, and UT for the state. Also when I do the individual queries in the sql analyzer it returns everything just fine. I cant figure out where and why the results are being returned so different than my queries. I have spent at least 4 hours today looking it over trying everything I can think of. I am at a loss any help would be great.
/* DBTotalCart Totals the shopping cart */
CREATE PROCEDURE aaa_TestTotal
(
@UserID int,
@Group int,
@Active int,
@OrderID int,
@MasterID int
)
As
DECLARE @State varchar( 2 )
DECLARE @Country varchar( 2 )
DECLARE @Discount decimal( 3,2 )
DECLARE @OrderSubtotal money
DECLARE @OrderTax money
DECLARE @OrderShipping money
DECLARE @OrderDiscount money
DECLARE @ItemCount int
DECLARE @ChargeShipping int
SELECT
@State = o.ship_state,
@Country = o.ship_country
FROM SC_MasterOrders AS m, SC_Orders AS o
WHERE m.uid = @UserID
AND m.i_recid = o.moid
IF @Group <> 7
BEGIN
UPDATE SC_Items SET
discount = 0.00
WHERE oid = @OrderID
END
IF @Group = 7 AND @Active = 0
BEGIN
UPDATE SC_Items SET
discount = 0.00
WHERE oid = @OrderID
END
IF UPPER(@State) = UT
BEGIN
SELECT
@ItemCount = SUM(i.quantity),
@OrderSubtotal = SUM(i.price * i.quantity),
@OrderTax = SUM(((i.price - i.discount) * i.quantity) * 0.0625),
@OrderDiscount = SUM(i.discount * i.quantity),
@ChargeShipping = SUM(p.charge_shipping)
FROM SC_Items AS i, CP_Inventory AS p
WHERE i.oid = @OrderID
AND i.pid = p.i_recid
END
ELSE
BEGIN
SELECT
@ItemCount = SUM(i.quantity),
@OrderSubtotal = SUM(i.price * i.quantity),
@OrderTax = 0.00,
@OrderDiscount = SUM(i.discount * i.quantity),
@ChargeShipping = SUM(p.charge_shipping)
FROM SC_Items AS i, CP_Inventory AS p
WHERE i.oid = @OrderID
AND i.pid = p.i_recid
END
IF @ChargeShipping > 0
BEGIN
IF UPPER(@Country) = US
BEGIN
IF @OrderSubtotal > 50.00
SELECT @OrderShipping = 0.00
ELSE
SELECT @OrderShipping = 4.75
END
ELSE
BEGIN
IF UPPER(@Country) = CA
BEGIN
IF @ItemCount < 4
SELECT @OrderShipping = 5.75
IF @ItemCount > 3 AND @ItemCount < 7
SELECT @OrderShipping = 7.00
IF @ItemCount > 6 AND @ItemCount < 10
SELECT @OrderShipping = 9.00
IF @ItemCount > 9
SELECT @OrderShipping = 11.00
END
IF UPPER(@Country) = MX
BEGIN
IF @ItemCount < 4
SELECT @OrderShipping = 7.00
IF @ItemCount > 3 AND @ItemCount < 7
SELECT @OrderShipping = 9.00
IF @ItemCount > 6 AND @ItemCount < 10
SELECT @OrderShipping = 11.00
IF @ItemCount > 9
SELECT @OrderShipping = 15.00
END
IF UPPER(@Country) <> CA AND UPPER(@Country) <> MX
BEGIN
IF @ItemCount < 4
SELECT @OrderShipping = 12.00
IF @ItemCount > 3 AND @ItemCount < 7
SELECT @OrderShipping = 15.00
IF @ItemCount > 6 AND @ItemCount < 10
SELECT @OrderShipping = 17.00
IF @ItemCount > 9
SELECT @OrderShipping = 20.00
END
END
END
ELSE
BEGIN
SELECT @OrderShipping = 0.00
END
UPDATE SC_Items SET
moid = @MasterID
WHERE oid = @OrderID
UPDATE SC_Orders SET
subtotal = @OrderSubtotal,
tax = @OrderTax,
shipping = @OrderShipping,
total = (@OrderSubtotal + @OrderTax + @OrderShipping)
WHERE i_recid = @OrderID
UPDATE SC_MasterOrders SET
discount = @OrderDiscount,
subtotal = @OrderSubtotal,
tax = @OrderTax,
shipping = @OrderShipping,
total = ((@OrderSubtotal + @OrderTax + @OrderShipping) - @OrderDiscount)
WHERE i_recid = @MasterID
GO
/* DBTotalCart Totals the shopping cart */
CREATE PROCEDURE aaa_TestTotal
(
@UserID int,
@Group int,
@Active int,
@OrderID int,
@MasterID int
)
As
DECLARE @State varchar( 2 )
DECLARE @Country varchar( 2 )
DECLARE @Discount decimal( 3,2 )
DECLARE @OrderSubtotal money
DECLARE @OrderTax money
DECLARE @OrderShipping money
DECLARE @OrderDiscount money
DECLARE @ItemCount int
DECLARE @ChargeShipping int
SELECT
@State = o.ship_state,
@Country = o.ship_country
FROM SC_MasterOrders AS m, SC_Orders AS o
WHERE m.uid = @UserID
AND m.i_recid = o.moid
IF @Group <> 7
BEGIN
UPDATE SC_Items SET
discount = 0.00
WHERE oid = @OrderID
END
IF @Group = 7 AND @Active = 0
BEGIN
UPDATE SC_Items SET
discount = 0.00
WHERE oid = @OrderID
END
IF UPPER(@State) = UT
BEGIN
SELECT
@ItemCount = SUM(i.quantity),
@OrderSubtotal = SUM(i.price * i.quantity),
@OrderTax = SUM(((i.price - i.discount) * i.quantity) * 0.0625),
@OrderDiscount = SUM(i.discount * i.quantity),
@ChargeShipping = SUM(p.charge_shipping)
FROM SC_Items AS i, CP_Inventory AS p
WHERE i.oid = @OrderID
AND i.pid = p.i_recid
END
ELSE
BEGIN
SELECT
@ItemCount = SUM(i.quantity),
@OrderSubtotal = SUM(i.price * i.quantity),
@OrderTax = 0.00,
@OrderDiscount = SUM(i.discount * i.quantity),
@ChargeShipping = SUM(p.charge_shipping)
FROM SC_Items AS i, CP_Inventory AS p
WHERE i.oid = @OrderID
AND i.pid = p.i_recid
END
IF @ChargeShipping > 0
BEGIN
IF UPPER(@Country) = US
BEGIN
IF @OrderSubtotal > 50.00
SELECT @OrderShipping = 0.00
ELSE
SELECT @OrderShipping = 4.75
END
ELSE
BEGIN
IF UPPER(@Country) = CA
BEGIN
IF @ItemCount < 4
SELECT @OrderShipping = 5.75
IF @ItemCount > 3 AND @ItemCount < 7
SELECT @OrderShipping = 7.00
IF @ItemCount > 6 AND @ItemCount < 10
SELECT @OrderShipping = 9.00
IF @ItemCount > 9
SELECT @OrderShipping = 11.00
END
IF UPPER(@Country) = MX
BEGIN
IF @ItemCount < 4
SELECT @OrderShipping = 7.00
IF @ItemCount > 3 AND @ItemCount < 7
SELECT @OrderShipping = 9.00
IF @ItemCount > 6 AND @ItemCount < 10
SELECT @OrderShipping = 11.00
IF @ItemCount > 9
SELECT @OrderShipping = 15.00
END
IF UPPER(@Country) <> CA AND UPPER(@Country) <> MX
BEGIN
IF @ItemCount < 4
SELECT @OrderShipping = 12.00
IF @ItemCount > 3 AND @ItemCount < 7
SELECT @OrderShipping = 15.00
IF @ItemCount > 6 AND @ItemCount < 10
SELECT @OrderShipping = 17.00
IF @ItemCount > 9
SELECT @OrderShipping = 20.00
END
END
END
ELSE
BEGIN
SELECT @OrderShipping = 0.00
END
UPDATE SC_Items SET
moid = @MasterID
WHERE oid = @OrderID
UPDATE SC_Orders SET
subtotal = @OrderSubtotal,
tax = @OrderTax,
shipping = @OrderShipping,
total = (@OrderSubtotal + @OrderTax + @OrderShipping)
WHERE i_recid = @OrderID
UPDATE SC_MasterOrders SET
discount = @OrderDiscount,
subtotal = @OrderSubtotal,
tax = @OrderTax,
shipping = @OrderShipping,
total = ((@OrderSubtotal + @OrderTax + @OrderShipping) - @OrderDiscount)
WHERE i_recid = @MasterID
GO
