Changes between Version 1 and Version 2 of UseCase007

Timestamp:

05/13/26 12:07:45 (2 weeks ago)

Author:

232012

Comment:

--

UseCase007

@@ -15 +15 @@
 {{{#!div style="text-align: justify; width: 100%;"
 
-1. User selects a specific {{{Product}}} and chooses the option to add it to {{{Order}}}.
+1. User selects a specific Product and chooses the option to add it to Order.
 
-2. System checks the current {{{stock}}} level of the product in the {{{Product}}} table to ensure availability.
+2. System checks the current stock level of the product in the Product table to ensure availability.
 
-3. System retrieves the current {{{price}}} of the product to establish the {{{price_at_purchase}}} for the transaction.
+{{{#!div style="margin-left: 20px;"
+{{{
+SELECT stock, price 
+FROM Product 
+WHERE product_id = @selected_product_id;
+}}}
+}}}
 
-4. System creates or identifies an active {{{Order}}} record associated with the consumer’s {{{user_id}}}.
+3. System retrieves the current price of the product to establish the price_at_purchase for the transaction.
 
-5. System creates a new entry in the {{{OrderItem}}} table, linking the {{{product_id}}} to the {{{order_id}}}.
+4. System creates or identifies an active Order record associated with the consumer’s user_id.
 
-6. System records the specific {{{quantity}}} requested by the {{{User}}} and saves the {{{price_at_purchase}}} to the {{{OrderItem}}} record.
+{{{#!div style="margin-left: 20px;"
+{{{
+SELECT order_id 
+FROM `Order` 
+WHERE user_id = @authenticated_user_id AND status = 'Pending';
 
-7. System updates the user's view to reflect that the item has been added to their {{{Order}}} total.
+INSERT INTO `Order` (user_id, date_created, status, total_price) 
+VALUES (@authenticated_user_id, CURRENT_TIMESTAMP, 'Pending', 0.00);
 }}}
+}}}
+
+5. System creates a new entry in the !OrderItem table, linking the product_id to the order_id.
+
+6. System records the specific quantity requested by the User and saves the price_at_purchase to the OrderItem record.
+
+{{{#!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;
+}}}
+}}}
+
+7. System updates the user's view to reflect that the item has been added to their Order total.
+
+{{{#!div style="margin-left: 20px;"
+{{{
+SELECT SUM(quantity * price_at_purchase) AS new_total
+FROM OrderItem
+WHERE order_id = @active_order_id;
+}}}
+}}}
+
+}}}