Or, it might be better to combine this approach with Web Services.
To simplify things in this section, we??™ll supply a single page for both administrators and
suppliers. This might not be ideal in all situations, because we might not want to expose all
order details and audit information to external suppliers. However, for demonstration purposes,
this reduces the amount of code we have to get through. We??™ll also tie in the security
for this page with the administrator forms-based security used earlier in the book, assuming
that people with permission to edit the site data will also have permission to administer orders.
In a more advanced setup, we could modify this slightly, providing roles for different types of
users and restricting the functionality available to users in different roles.
Implementing the Data Tier
We need to add a new stored procedure (orders_get_audit_trail) to the tshirtshop database
and update an existing stored procedure (orders_update_order) to take into account the new
status codes.
Using phpMyAdmin, select the tshirtshop database, and open a new SQL query page. Now,
execute the following code, which creates a new stored procedure called orders_update_order
in the tshirtshop database:
-- Drop procedure orders_update_order
DROP PROCEDURE orders_update_order$$
-- Update orders_update_order stored procedure
CREATE PROCEDURE orders_update_order(IN inOrderId INT, IN inStatus INT,
IN inComments VARCHAR(255), IN inAuthCode VARCHAR(50),
CHAPTER 19 ?– IMPLEMENTING THE ORDER PIPELINE: PART 2 614
IN inReference VARCHAR(50))
BEGIN
DECLARE currentDateShipped DATETIME;
SELECT shipped_on
FROM orders
WHERE order_id = inOrderId
INTO currentDateShipped;
UPDATE orders
SET status = inStatus, comments = inComments,
auth_code = inAuthCode, reference = inReference
WHERE order_id = inOrderId;
IF inStatus < 7 AND currentDateShipped IS NOT NULL THEN
UPDATE orders SET shipped_on = NULL WHERE order_id = inOrderId;
ELSEIF inStatus > 6 AND currentDateShipped IS NULL THEN
UPDATE orders SET shipped_on = NOW() WHERE order_id = inOrderId;
END IF;
END$$
Next, execute this code, which creates the orders_get_audit_trail stored procedure in
the tshirtshop database:
-- Create orders_get_audit_trail stored procedure
CREATE PROCEDURE orders_get_audit_trail(IN inOrderId INT)
BEGIN
SELECT audit_id, order_id, created_on, message, code
FROM audit
WHERE order_id = inOrderId;
END$$
Implementing the Business Tier
To cater to the new data tier function added in the previous section, we also have to add a new
method to the Orders class from business/orders.
Pages:
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730