Changes between Version 3 and Version 4 of UseCase007

Timestamp:

05/21/26 18:24:54 (6 days ago)

Author:

232012

Comment:

--

UseCase007

@@ -22 +22 @@
 {{{
 SELECT stock, price 
-FROM Product 
-WHERE product_id = @selected_product_id;
+FROM PRODUCTS 
+WHERE product_id = :selected_product_id;
 }}}
 }}}
@@ -34 +34 @@
 {{{
 SELECT order_id 
-FROM `Order` 
-WHERE user_id = @authenticated_user_id AND status = 'Pending';
+FROM ORDERS 
+WHERE user_id = :authenticated_user_id AND status = 'PENDING';
 
-INSERT INTO `Order` (user_id, date_created, status, total_price) 
-VALUES (@authenticated_user_id, CURRENT_TIMESTAMP, 'Pending', 0.00);
+INSERT INTO ORDERS (order_id, user_id, payment_method, purchase_date, points_earned, points_used, status) 
+VALUES (
+    :new_order_id, 
+    :authenticated_user_id, 
+    'CARD',
+    CURRENT_DATE,
+    0, 
+    0, 
+    'PENDING'
+);
 }}}
 }}}
@@ -48 +56 @@
 {{{#!div style="margin-left: 20px;"
 {{{
-INSERT INTO OrderItem (order_id, product_id, quantity, price_at_purchase) 
-VALUES (@active_order_id, @selected_product_id, @requested_quantity, @current_price)
-ON DUPLICATE KEY UPDATE quantity = quantity + @requested_quantity;
+INSERT INTO ORDER_ITEMS (order_id, product_id, quantity, price_at_purchase) 
+VALUES (:active_order_id, :selected_product_id, :requested_quantity, :current_price)
+ON CONFLICT (order_id, product_id) 
+DO UPDATE SET quantity = ORDER_ITEMS.quantity + EXCLUDED.quantity;
 }}}
 }}}
@@ -58 +67 @@
 {{{#!div style="margin-left: 20px;"
 {{{
-SELECT SUM(quantity * price_at_purchase) AS new_total
-FROM OrderItem
-WHERE order_id = @active_order_id;
+SELECT COALESCE(SUM(quantity * price_at_purchase), 0) AS new_total
+FROM ORDER_ITEMS
+WHERE order_id = :active_order_id;
 }}}
 }}}