This implies adding a new record to
the orders table and a number of records (one record for each product) in the order_detail table.
-- Create shopping_cart_create_order stored procedure
CREATE PROCEDURE shopping_cart_create_order(IN inCartId CHAR(32))
BEGIN
DECLARE orderId INT;
-- Insert a new record into orders and obtain the new order ID
INSERT INTO orders (created_on) VALUES (NOW());
-- Obtain the new Order ID
SELECT LAST_INSERT_ID() INTO orderId;
-- Insert order details in order_detail table
INSERT INTO order_detail (order_id, product_id, attributes,
product_name, quantity, unit_cost)
SELECT orderId, p.product_id, sc.attributes, p.name, sc.quantity,
COALESCE(NULLIF(p.discounted_price, 0), p.price) AS unit_cost
CHAPTER 14 ?– ACCEPTING CUSTOMER ORDERS 436
FROM shopping_cart sc
INNER JOIN product p
ON sc.product_id = p.product_id
WHERE sc.cart_id = inCartId AND sc.buy_now;
-- Save the order's total amount
UPDATE orders
SET total_amount = (SELECT SUM(unit_cost * quantity)
FROM order_detail
WHERE order_id = orderId)
WHERE order_id = orderId;
-- Clear the shopping cart
CALL shopping_cart_empty(inCartId);
-- Return the Order ID
SELECT orderId;
END$$
How It Works: Implementing shopping_cart_empty and shopping_cart_create_order
The first step in implementing shopping_cart_create_order involves creating the new record in the orders
table.
Pages:
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554