Modify the solution for SQL
sky852TechSupport/Ch 21 project.doc
1 Projects.doc
Project 21-1: Add user authentication
For this project, you will use a secure connection and require all users to log in including customers, technicians, and administrators. (Required reading: chapters 1-6, 10, and 21)
The Home page
Operation
· If you’ve been using the starting Home page for these projects, you’ll have to replace it with a Home page like the one above.
· When the user clicks one of the links on the main menu, the application displays a login form that’s appropriate for the type of user.
The Admin Login page
Operation
· When the user enters a valid username and password, the Admin Menu page is displayed. Otherwise, the Admin Login page is displayed again.
· To log in, you can use “admin” as the username and “sesame” as the password.
The Admin Menu page
Operation
· To navigate to an application, the user can click on the appropriate link.
· The page displays a message that indicates the login status.
· To log out, the user can click on the Logout button. This displays the Main Menu page.
Specifications
· All pages should include a link to the Home page in the header for the page.
· Except for the Home page, all pages should use a secure (https) connection.
· No pages should allow an unauthorized user to access them. For example, only a user that’s logged in as an administrator should be able to access the Admin Menu page.
The Technician Login page
Operation
· When the user enters a valid technician email and password, the Select Incident page is displayed. Otherwise, the Technician Login page is displayed again.
The Select Incident page
Operation
· Same as project 20-3, but the bottom of the page displays a message about the technician that’s logged in and provides a Logout button that the technician can use to log out.
· If there are open incidents for the current technician, this page displays a table of incidents as shown in project 20-3.
· If there are no open incidents for the current technician, this page displays a message and a link as shown above. However, this link only displays new incidents if new incidents have been assigned to the technician.
The Customer Login page
Operation
· When the user enters a valid customer email and password, the Register Product page is displayed. Otherwise, the Customer Login page is displayed again.
The Register Product page
Operation
· Same as project 6-4, but the bottom of the page displays a message about the customer that’s logged in and provides a Logout button that the customer can use to log out.
TechSupport/ch21_tech_support/admin/admin_login.php
<?php include '../view/header.php'; ?> <div id="main"> <h1>Admin Login</h1> <form action="" method="post" id="aligned"> <input type="hidden" name="action" value="login" /> <label>Username:</label> <input type="text" name="username" size="30" /> <br /> <label>Password:</label> <input type="password" name="password" size="30" /> <br /> <label> </label> <input type="submit" value="Login" /> </form> </div> <?php include '../view/footer.php'; ?>
TechSupport/ch21_tech_support/admin/admin_menu.php
<?php require_once('../util/secure_conn.php'); require_once('../util/valid_admin.php'); ?> <?php include '../view/header.php'; ?> <div id="main"> <h2>Admin Menu</h2> <ul class="nav"> <li><a href="../product_manager">Manage Products</a></li> <li><a href="../technician_manager">Manage Technicians</a></li> <li><a href="../customer_manager">Manage Customers</a></li> <li><a href="../incident_create">Create Incident</a></li> <li><a href="../incident_assign">Assign Incident</a></li> <li><a href="../incident_display">Display Incidents</a></li> </ul> <h2>Login Status</h2> <p>You are logged in as <?php echo $_SESSION['admin']; ?>.</p> <form action="" method="post"> <input type="hidden" name="action" value="logout" /> <input type="submit" value="Logout" /> </form> </div> <?php include '../view/footer.php'; ?>
TechSupport/ch21_tech_support/admin/index.php
<?php require_once('../util/secure_conn.php'); require_once('../model/database.php'); require_once('../model/admin_db.php'); session_start(); if (isset($_POST['action'])) { $action = $_POST['action']; } elseif (isset($_GET['action'])) { $action = $_GET['action']; } elseif (isset($_SESSION['admin'])) { $action = 'view_menu'; } else { $action = 'view_login'; } switch ($action) { case 'view_login': include 'admin_login.php'; break; case 'view_menu': include 'admin_menu.php'; break; case 'login': $username = $_POST['username']; $password = $_POST['password']; // If valid username/password, login if (is_valid_admin_login($username, $password)) { $_SESSION['admin'] = $username; } else { $error_message = 'Login failed. Invalid email or password.'; } include 'admin_menu.php'; break; case 'logout': unset($_SESSION['admin']); header('Location: ..' ); break; default: echo 'Unknown action: ' . $action; break; } ?>
TechSupport/ch21_tech_support/customer_manager/customer_display.php
<?php require_once('../util/secure_conn.php'); require_once('../util/valid_admin.php'); ?> <?php include '../view/header.php'; ?> <div id="main"> <div id="content"> <!-- display a table of customer information --> <h2>Add/Update Customer</h2> <form action="" method="post" id="aligned"> <input type="hidden" name="action" value="<?php echo $action; ?>" /> <input type="hidden" name="customer_id" value="<?php echo $customer_id; ?>" /> <label>First Name:</label> <input type="text" name="first_name" value="<?php echo $first_name; ?>" /> <?php echo $fields->getField('first_name')->getHTML(); ?> <br /> <label>Last Name:</label> <input type="text" name="last_name" value="<?php echo $last_name; ?>" /> <?php echo $fields->getField('last_name')->getHTML(); ?> <br /> <label>Address:</label> <input type="text" name="address" value="<?php echo $address; ?>" size="50" /> <?php echo $fields->getField('address')->getHTML(); ?> <br /> <label>City:</label> <input type="text" name="city" value="<?php echo $city; ?>" /> <?php echo $fields->getField('city')->getHTML(); ?> <br /> <label>State:</label> <input type="text" name="state" value="<?php echo $state; ?>" /> <?php echo $fields->getField('state')->getHTML(); ?> <br /> <label>Postal Code:</label> <input type="text" name="postal_code" value="<?php echo $postal_code; ?>" /> <?php echo $fields->getField('postal_code')->getHTML(); ?> <br /> <label>Country:</label> <select name="country_code"> <?php foreach ($countries as $country) : if ($country_code == $country['countryCode']) { $selected = 'selected="selected"'; } else { $selected = ''; } ?> <option value="<?php echo $country['countryCode']; ?>" <?php echo $selected; ?>> <?php echo $country['countryName']; ?> </option> <?php endforeach; ?> </select> <br /> <label>Phone:</label> <input type="text" name="phone" value="<?php echo $phone; ?>" /> <?php echo $fields->getField('phone')->getHTML(); ?> <br /> <label>Email:</label> <input type="text" name="email" value="<?php echo $email; ?>" size="50" /> <?php echo $fields->getField('email')->getHTML(); ?> <br /> <label>Password:</label> <input type="text" name="password" value="<?php echo $password; ?>" /> <?php echo $fields->getField('password')->getHTML(); ?> <br /> <label> </label> <input type="submit" value="<?php echo $button_text ?>" /> <br /> </form> <p><a href="">Search Customers</a></p> </div> </div> <?php include '../view/footer.php'; ?>
TechSupport/ch21_tech_support/customer_manager/customer_search.php
<?php require_once('../util/secure_conn.php'); require_once('../util/valid_admin.php'); ?> <?php include '../view/header.php'; ?> <div id="main"> <h2>Customer Search</h2> <div id="content"> <!-- display a search form --> <form action="index.php" method="post"> <input type="hidden" name="action" value="display_customers" /> <label>Last Name:</label> <input type="input" name="last_name" value="<?php echo $last_name; ?>" /> <input type="submit" value="Search" /> </form> <?php if (isset($message)) : ?> <p><?php echo $message; ?></p> <?php elseif ($customers) : ?> <h2>Results</h2> <table> <tr> <th>Name</th> <th>Email Address</th> <th>City</th> <th> </th> </tr> <?php foreach ($customers as $customer) : ?> <tr> <td><?php echo $customer['firstName'] . ' ' . $customer['lastName']; ?></td> <td><?php echo $customer['email']; ?></td> <td><?php echo $customer['city']; ?></td> <td><form action="." method="post"> <input type="hidden" name="action" value="display_customer" /> <input type="hidden" name="customer_id" value="<?php echo $customer['customerID']; ?>" /> <input type="submit" value="Select" /> </form></td> </tr> <?php endforeach; ?> </table> <?php endif; ?> <h2>Add a new customer</h2> <form action="." method="post"> <input type="hidden" name="action" value="display_add" /> <input type="submit" value="Add Customer" /> </form> </div> </div> <?php include '../view/footer.php'; ?>
TechSupport/ch21_tech_support/customer_manager/index.php
<?php require('../model/database.php'); require('../model/customer_db.php'); require('../model/country_db.php'); require('../model/fields.php'); require('../model/validate.php'); session_start(); // Create Validate object $validate = new Validate(); $fields = $validate->getFields(); $fields->addField('first_name'); $fields->addField('last_name'); $fields->addField('address'); $fields->addField('city'); $fields->addField('state'); $fields->addField('postal_code'); $fields->addField('phone'); $fields->addField('email'); $fields->addField('password'); if (isset($_POST['action'])) { $action = $_POST['action']; } else if (isset($_GET['action'])) { $action = $_GET['action']; } else { $action = 'search_customers'; } switch ($action) { case 'search_customers': include('customer_search.php'); break; case 'display_customers': $last_name = $_POST['last_name']; if (empty($last_name)) { $message = 'You must enter a last name.'; } else { $customers = get_customers_by_last_name($last_name); } include('customer_search.php'); break; case 'display_customer': $customer_id = $_POST['customer_id']; $customer = get_customer($customer_id); // Get data from $customer array $customer_id = $customer['customerID']; $first_name = $customer['firstName']; $last_name = $customer['lastName']; $address = $customer['address']; $city = $customer['city']; $state = $customer['state']; $postal_code = $customer['postalCode']; $country_code = $customer['countryCode']; $phone = $customer['phone']; $email = $customer['email']; $password = $customer['password']; // Get countries $countries = get_countries(); // Set action and button text for form $action = 'update_customer'; $button_text = 'Update Customer'; include('customer_display.php'); break; case 'display_add': $password = ''; // don't display db connect password $country_code = 'US'; // set default country code $countries = get_countries(); $action = 'add_customer'; $button_text = 'Add Customer'; include('customer_display.php'); break; case 'add_customer': // Get data from POST request $first_name = $_POST['first_name']; $last_name = $_POST['last_name']; $address = $_POST['address']; $city = $_POST['city']; $state = $_POST['state']; $postal_code = $_POST['postal_code']; $country_code = $_POST['country_code']; $phone = $_POST['phone']; $email = $_POST['email']; $password = $_POST['password']; // Validate form data $validate->text('first_name', $first_name, true, 1, 50); $validate->text('last_name', $last_name, true, 1, 50); $validate->text('address', $address, true, 1, 50); $validate->text('city', $city, true, 1, 50); $validate->text('state', $state, true, 1, 50); $validate->text('postal_code', $postal_code, true, 1, 20); $validate->phone('phone', $phone, true, 1, 20); $validate->email('email', $email, true, 1, 50); $validate->password('password', $password, true, 1, 20); // Load appropriate view based on hasErrors if ($fields->hasErrors()) { $countries = get_countries(); $action = 'add_customer'; $button_text = 'Add Customer'; include('customer_display.php'); } else { add_customer($first_name, $last_name, $address, $city, $state, $postal_code, $country_code, $phone, $email, $password); include('customer_search.php'); } break; case 'update_customer': // Get data from POST request $customer_id = $_POST['customer_id']; $first_name = $_POST['first_name']; $last_name = $_POST['last_name']; $address = $_POST['address']; $city = $_POST['city']; $state = $_POST['state']; $postal_code = $_POST['postal_code']; $country_code = $_POST['country_code']; $phone = $_POST['phone']; $email = $_POST['email']; $password = $_POST['password']; // Validate form data $validate->text('first_name', $first_name, true, 1, 50); $validate->text('last_name', $last_name, true, 1, 50); $validate->text('address', $address, true, 1, 50); $validate->text('city', $city, true, 1, 50); $validate->text('state', $state, true, 1, 50); $validate->text('postal_code', $postal_code, true, 1, 20); $validate->phone('phone', $phone, true, 1, 20); $validate->email('email', $email, true, 1, 50); $validate->password('password', $password, true, 1, 20); // Load appropriate view based on hasErrors if ($fields->hasErrors()) { $action = 'update_customer'; $button_text = 'Update Customer'; $countries = get_countries(); include('customer_display.php'); } else { update_customer($customer_id, $first_name, $last_name, $address, $city, $state, $postal_code, $country_code, $phone, $email, $password); include('customer_search.php'); } break; } ?>
TechSupport/ch21_tech_support/errors/db_error.php
<?php include '../view/header.php'; ?> <div id="main"> <h1 class="top">Database Error</h1> <p>An error occurred while attempting to work with the database.</p> <p>Message: <?php echo $error_message; ?></p> </div> <?php include '../view/footer.php'; ?>
TechSupport/ch21_tech_support/errors/db_error_connect.php
<?php include '../view/header.php'; ?> <div id="main"> <h1 class="top">Database Error</h1> <p>There was an error connecting to the database.</p> <p>The database must be installed as described in appendix A.</p> <p>The database must be running as described in chapter 1.</p> <p>Error message: <?php echo $error_message; ?></p> <p> </p> </div><!-- end main --> <?php include '../view/footer.php'; ?>
TechSupport/ch21_tech_support/errors/error.php
<?php include '../view/header.php'; ?> <div id="main"> <h1 class="top">Error</h1> <p><?php echo $error; ?></p> </div> <?php include '../view/footer.php'; ?>
TechSupport/ch21_tech_support/incident_assign/incident_assign.php
<?php require_once('../util/secure_conn.php'); require_once('../util/valid_admin.php'); ?> <?php include '../view/header.php'; ?> <div id="main"> <div id="content"> <h2>Assign Incident</h2> <?php if (isset($message)) : ?> <p><?php echo $message; ?></p> <p><a href="">Select Another Incident</a></p> <?php else: ?> <form action="" method="post" id="aligned"> <label>Customer:</label> <span><?php echo $customer['firstName'] . ' ' . $customer['lastName']; ?></span> <br /> <label>Product:</label> <span><?php echo $incident['productCode']; ?></span> <br /> <label>Technician:</label> <span><?php echo $technician['firstName'] . ' ' . $technician['lastName']; ?></span> <br /> <input type="hidden" name="action" value="assign_incident" /> <input type="submit" value="Assign Incident" /> </form> <?php endif; ?> </div> </div> <?php include '../view/footer.php'; ?>
TechSupport/ch21_tech_support/incident_assign/incident_select.php
<?php require_once('../util/secure_conn.php'); require_once('../util/valid_admin.php'); ?> <?php include '../view/header.php'; ?> <div id="main"> <h2>Select Incident</h2> <div id="content"> <table> <tr> <th>Customer</th> <th>Product</th> <th>Date Opened</th> <th>Title</th> <th>Description</th> <th> </th> </tr> <?php foreach ($incidents as $i) : ?> <tr> <td><?php echo $i['firstName'] . ' ' . $i['lastName']; ?></td> <td><?php echo $i['productCode']; ?></td> <td><?php echo $i['dateOpened']; ?></td> <td><?php echo $i['title']; ?></td> <td><?php echo $i['description']; ?></td> <td><form action="." method="post"> <input type="hidden" name="action" value="select_incident" /> <input type="hidden" name="incident_id" value="<?php echo $i['incidentID']; ?>" /> <input type="submit" value="Select" /> </form></td> </tr> <?php endforeach; ?> </table> </div> </div> <?php include '../view/footer.php'; ?>
TechSupport/ch21_tech_support/incident_assign/index.php
<?php require('../model/database.php'); require('../model/customer_db.php'); require('../model/technician_db.php'); require('../model/incident_db.php'); session_start(); if (isset($_POST['action'])) { $action = $_POST['action']; } else if (isset($_GET['action'])) { $action = $_GET['action']; } else { $action = 'display_incident_select'; } switch ($action) { case 'display_incident_select': $incidents = get_incidents_unassigned(); include('incident_select.php'); break; case 'select_incident': // Set incident in session $incident_id = $_POST['incident_id']; $_SESSION['incident_id'] = $incident_id; $technicians = get_technicians_with_count(); include('technician_select.php'); break; case 'select_technician': // Set technician ID in session $technician_id = $_POST['technician_id']; $_SESSION['technician_id'] = $technician_id; // Get incident ID from session $incident_id = $_SESSION['incident_id']; // Get data $technician = get_technician($technician_id); $incident = get_incident($incident_id); $customer = get_customer($incident['customerID']); include('incident_assign.php'); break; case 'assign_incident': $count = assign_incident($_SESSION['incident_id'], $_SESSION['technician_id']); if ($count == 1) { $message = "This incident was assigned to a technician."; } else { $message = "An error occurred while attempting to update the database."; } include('incident_assign.php'); break; } ?>
TechSupport/ch21_tech_support/incident_assign/technician_select.php
<?php require_once('../util/secure_conn.php'); require_once('../util/valid_admin.php'); ?> <?php include '../view/header.php'; ?> <div id="main"> <h2>Select Technician</h2> <div id="content"> <table> <tr> <th>Name</th> <th>Open Incidents</th> <th> </th> </tr> <?php foreach ($technicians as $t) : ?> <tr> <td><?php echo $t['firstName'] . ' ' . $t['lastName']; ?></td> <td><?php echo $t['openIncidentCount']; ?></td> <td><form action="." method="post"> <input type="hidden" name="action" value="select_technician" /> <input type="hidden" name="technician_id" value="<?php echo $t['techID']; ?>" /> <input type="submit" value="Select" /> </form></td> </tr> <?php endforeach; ?> </table> </div> </div> <?php include '../view/footer.php'; ?>
TechSupport/ch21_tech_support/incident_create/customer_get.php
<?php require_once('../util/secure_conn.php'); require_once('../util/valid_admin.php'); ?> <?php include '../view/header.php'; ?> <div id="main"> <h2>Get Customer</h2> <p>You must enter the customer's email address to select the customer.</p> <div id="content"> <!-- display a search form --> <form action="" method="post"> <input type="hidden" name="action" value="get_customer" /> <label>Email:</label> <input type="input" name="email" value="<?php echo $email; ?>" /> <input type="submit" value="Get Customer" /> </form> </div> </div> <?php include '../view/footer.php'; ?>
TechSupport/ch21_tech_support/incident_create/incident_create.php
<?php require_once('../util/secure_conn.php'); require_once('../util/valid_admin.php'); ?> <?php include '../view/header.php'; ?> <div id="main"> <div id="content"> <h2>Create Incident</h2> <?php if (isset($message)) : ?> <p><?php echo $message; ?></p> <?php else: ?> <form action="" method="post" id="aligned"> <input type="hidden" name="action" value="create_incident" /> <input type="hidden" name="customer_id" value="<?php echo $customer['customerID']; ?>" /> <label>Customer:</label> <span><?php echo $customer['firstName'] . ' ' . $customer['lastName'] ?></span> <br /> <label>Product:</label> <select name="product_code"> <?php foreach ($products as $product) : ?> <option value="<?php echo $product['productCode']; ?>"> <?php echo $product['name']; ?> </option> <?php endforeach; ?> </select> <br /> <label>Title:</label> <input type="input" name="title" /> <br /> <label>Description:</label> <textarea name="description" cols="40" rows="5"></textarea> <br /> <label> </label> <input type="submit" value="Create Incident" /> </form> <?php endif; ?> </div> </div> <?php include '../view/footer.php'; ?>
TechSupport/ch21_tech_support/incident_create/index.php
<?php require('../model/database.php'); require('../model/customer_db.php'); require('../model/product_db.php'); require('../model/incident_db.php'); session_start(); if (isset($_POST['action'])) { $action = $_POST['action']; } else if (isset($_GET['action'])) { $action = $_GET['action']; } else { $action = 'display_customer_get'; } switch ($action) { case 'display_customer_get': include('customer_get.php'); break; case 'get_customer': $email = $_POST['email']; $customer = get_customer_by_email($email); $products = get_products_by_customer($email); include('incident_create.php'); break; case 'create_incident': $customer_id = $_POST['customer_id']; $product_code = $_POST['product_code']; $title = $_POST['title']; $description = $_POST['description']; add_incident($customer_id, $product_code, $title, $description); $message = "This incident was added to our database."; include('incident_create.php'); break; } ?>
TechSupport/ch21_tech_support/incident_display/incidents_assigned.php
<?php require_once('../util/secure_conn.php'); require_once('../util/valid_admin.php'); ?> <?php include '../view/header.php'; ?> <div id="main"> <h2>Assigned Incidents</h2> <p><a href="?action=display_unassigned">View Unassigned Incidents</a></p> <div id="content"> <table> <tr> <th>Customer</th> <th>Product</th> <th>Technician</th> <th>Incident</th> </tr> <?php foreach ($incidents as $i) : $ts = strtotime($i['dateOpened']); $date_opened = date('n/j/Y', $ts); if (isset($i['dateClosed'])) { $ts = strtotime($i['dateClosed']); $date_closed = date('n/j/Y', $ts); } else { $date_closed = 'OPEN'; } ?> <tr> <td> <?php echo $i['customerFirstName'] . ' ' . $i['customerLastName']; ?> </td> <td> <?php echo $i['productName']; ?> </td> <td> <?php echo $i['techFirstName'] . ' ' . $i['techLastName']; ?> </td> <td> <table id="no_border"> <tr> <td>ID:</td> <td><?php echo $i['incidentID']; ?></td> </tr> <tr> <td>Opened:</td> <td><?php echo $date_opened; ?></td> </tr> <tr> <td>Closed:</td> <td><?php echo $date_closed; ?></td> </tr> <tr> <td>Title:</td> <td><?php echo $i['title']; ?></td> </tr> <tr> <td>Description:</td> <td><?php echo $i['description']; ?></td> </tr> </table> </td> </tr> <?php endforeach; ?> </table> </div> </div> <?php include '../view/footer.php'; ?>
TechSupport/ch21_tech_support/incident_display/incidents_unassigned.php
<?php require_once('../util/secure_conn.php'); require_once('../util/valid_admin.php'); ?> <?php include '../view/header.php'; ?> <div id="main"> <h2>Unassigned Incidents</h2> <p><a href="?action=display_assigned">View Assigned Incidents</a></p> <div id="content"> <table> <tr> <th>Customer</th> <th>Product</th> <th>Incident</th> </tr> <?php foreach ($incidents as $i) : $ts = strtotime($i['dateOpened']); $date_opened = date('n/j/Y', $ts); ?> <tr> <td> <?php echo $i['firstName'] . ' ' . $i['lastName']; ?> </td> <td> <?php echo $i['productName']; ?> </td> <td> <table id="no_border"> <tr> <td>ID:</td> <td><?php echo $i['incidentID']; ?></td> </tr> <tr> <td>Opened:</td> <td><?php echo $date_opened; ?></td> </tr> <tr> <td>Title:</td> <td><?php echo $i['title']; ?></td> </tr> <tr> <td>Description:</td> <td><?php echo $i['description']; ?></td> </tr> </table> </td> </tr> <?php endforeach; ?> </table> </div> </div> <?php include '../view/footer.php'; ?>
TechSupport/ch21_tech_support/incident_display/index.php
<?php require('../model/database.php'); require('../model/customer_db.php'); require('../model/technician_db.php'); require('../model/incident_db.php'); session_start(); if (isset($_POST['action'])) { $action = $_POST['action']; } else if (isset($_GET['action'])) { $action = $_GET['action']; } else { $action = 'display_unassigned'; } switch ($action) { case 'display_unassigned': $incidents = get_incidents_unassigned(); include('incidents_unassigned.php'); break; case 'display_assigned': $incidents = get_incidents_assigned(); include('incidents_assigned.php'); break; } ?>
TechSupport/ch21_tech_support/incident_update/incident_select.php
<?php require_once('../util/secure_conn.php'); require_once('../util/valid_technician.php'); ?> <?php include '../view/header.php'; ?> <div id="main"> <h2>Select Incident</h2> <div id="content"> <?php if (isset($message)) : ?> <p><?php echo $message; ?></p> <p><a href="">Refresh List of Incidents</a></p> <?php else : ?> <table> <tr> <th>Customer</th> <th>Product</th> <th>Date Opened</th> <th>Title</th> <th>Description</th> <th> </th> </tr> <?php foreach ($incidents as $i) : $ts = strtotime($i['dateOpened']); $date_opened = date('n/j/Y', $ts); ?> <tr> <td><?php echo $i['firstName'] . ' ' . $i['lastName']; ?></td> <td><?php echo $i['productCode']; ?></td> <td><?php echo $date_opened; ?></td> <td><?php echo $i['title']; ?></td> <td><?php echo $i['description']; ?></td> <td><form action="." method="post"> <input type="hidden" name="action" value="select_incident" /> <input type="hidden" name="incident_id" value="<?php echo $i['incidentID']; ?>" /> <input type="submit" value="Select" /> </form></td> </tr> <?php endforeach; ?> </table> <?php endif; ?> <p>You are logged in as <?php echo $technician['email']; ?></p> <form action="" method="post"> <input type="hidden" name="action" value="logout" /> <input type="submit" value="Logout" /> </form> </div> </div> <?php include '../view/footer.php'; ?>
TechSupport/ch21_tech_support/incident_update/incident_update.php
<?php include '../view/header.php'; ?> <div id="main"> <div id="content"> <h2>Update Incident</h2> <?php if (isset($message)) : ?> <p><?php echo $message; ?></p> <p><a href="">Select Another Incident</a></p> <?php else: $ts = strtotime($incident['dateOpened']); $date_opened = date('n/j/Y', $ts); ?> <form action="" method="post" id="aligned"> <label>Incident ID:</label> <span><?php echo $incident['incidentID']; ?></span> <br /> <label>Product Code:</label> <span><?php echo $incident['productCode']; ?></span> <br /> <label>Date Opened:</label> <span><?php echo $date_opened; ?></span> <br /> <label>Date Closed:</label> <input type="text" name="date_closed" /> <br /> <label>Title:</label> <span><?php echo $incident['title']; ?></span> <br /> <label>Description:</label> <textarea name="description" cols="40" rows="6"><?php echo $incident['description']; ?></textarea> <br /> <input type="hidden" name="action" value="update_incident" /> <input type="submit" value="Update Incident" /> </form> <p>You are logged in as <?php echo $_SESSION['technician']['email']; ?></p> <form action="" method="post"> <input type="hidden" name="action" value="logout" /> <input type="submit" value="Logout" /> </form> <?php endif; ?> </div> </div> <?php include '../view/footer.php'; ?>
TechSupport/ch21_tech_support/incident_update/index.php
<?php require('../model/database.php'); require('../model/customer_db.php'); require('../model/technician_db.php'); require('../model/incident_db.php'); session_start(); if (isset($_POST['action'])) { $action = $_POST['action']; } else if (isset($_GET['action'])) { $action = $_GET['action']; } else if (isset($_SESSION['technician'])) { // Skip login if technician is in the session $action = 'display_incident_select'; } else { $action = 'display_login'; } switch ($action) { case 'display_login': include('technician_login.php'); break; case 'display_incident_select': // If technician is not in the session, set it in the session if (!isset($_SESSION['technician'])) { $email = $_POST['email']; $password = $_POST['password']; if (is_valid_technician_login($email, $password)) { $technician = get_technician_by_email($email); $_SESSION['technician'] = $technician; } } $technician = $_SESSION['technician']; $incidents = get_incidents_by_technician($technician['techID']); if (count($incidents) == 0) { $message = 'There are no open incidents for this technician.'; } include('incident_select.php'); break; case 'select_incident': // Set incident in session $incident_id = $_POST['incident_id']; $_SESSION['incident_id'] = $incident_id; $incident = get_incident($incident_id); include('incident_update.php'); break; case 'update_incident': $date_closed = $_POST['date_closed']; $description = $_POST['description']; $incident_id = $_SESSION['incident_id']; // convert date to correct format $ts = strtotime($date_closed); $date_closed = date('Y-m-d', $ts); $count = update_incident($incident_id, $date_closed, $description); if ($count == 1) { $message = "This incident was updated."; } else { $message = "An error occurred while attempting to update the database."; } include('incident_update.php'); break; case 'logout': unset($_SESSION['technician']); include('technician_login.php'); break; } ?>
TechSupport/ch21_tech_support/incident_update/technician_login.php
<?php require_once('../util/secure_conn.php'); ?> <?php include '../view/header.php'; ?> <div id="main"> <h2>Technician Login</h2> <p>You must login before you can update an incident.</p> <div id="content"> <!-- display a search form --> <form action="" method="post" id="aligned"> <input type="hidden" name="action" value="display_incident_select" /> <label>Email:</label> <input type="input" name="email" value="<?php echo $technician['email']; ?>" /> <br /> <label>Password:</label> <input type="password" name="password" value="<?php echo $technician['password']; ?>" /> <br /> <label> </label> <input type="submit" value="Login" /> </form> </div> </div> <?php include '../view/footer.php'; ?>
TechSupport/ch21_tech_support/index.php
<?php include 'view/header.php'; ?> <div id="main"> <h2>Main Menu</h2> <ul class="nav"> <li><a href="admin">Administrators</a></li> <li><a href="incident_update">Technicians</a></li> <li><a href="product_register">Customers</a></li> </ul> </div> <?php include 'view/footer.php'; ?>
TechSupport/ch21_tech_support/main.css
/* the styles for the HTML elements */ body { margin-top: 0; background-color: #BFCFFE; font-family: Arial, Helvetica, sans-serif; } h1 { font-size: 150%; margin: 0; padding: .5em 0 .25em; } h2 { font-size: 120%; margin: 0; padding: .25em 0 .25em ; } h1, h2 { color: black; } p { margin: .5em 0 .5em 0; padding: 0; } ul { margin: 0; padding: 0; } li { margin: 0; padding: 0; } ul.nav { list-style-type: none; margin-left: 0; padding-left: 0; } ul.nav li { padding-bottom: 0.5em; } a { color: #3333CC; font-weight: bold; } a:hover { color: #3333CC; } table { border: 1px solid #001963; border-collapse: collapse; } td, th { border: 1px dashed #001963; padding: .2em .5em .2em .5em; vertical-align: top; text-align: left; } #no_border { border: 0px; } #no_border td { border: 0px; } form { margin: 0; } br { clear: left; } textarea { font-family: Arial, Helvetica, sans-serif; font-size: 80%; } /* the styles for the div tags that divide the page into sections */ #page { width: 760px; margin: 0 auto; background-color: white; border: 1px solid #001963; } #header { margin: 0; border-bottom: 2px solid black; padding: .5em 2em; } #header h1 { color: black; margin: 0; padding: 0; } #header p { margin: 0; padding: .25em 0 0 0; } #header ul { margin: 0; padding: 1em 0 0 0; } #main { margin: 0; padding: .5em 2em .25em; } #content { padding-bottom: .25em; } #footer { clear: both; margin-top: 1em; padding-right: 1em; border-top: 2px solid black; } #footer p { text-align: right; font-size: 80%; margin: 1em 0; } .right { text-align: right; } .error { color: red; } /******************************************************************** * Additional styles for aligned forms ********************************************************************/ #aligned { margin: .5em 0 2em; } #aligned label { width: 8em; padding-right: 1em; padding-bottom: .5em; float: left; } #aligned input { float: left; } #aligned input[text] { width: 15em; }
TechSupport/ch21_tech_support/model/admin_db.php
<?php function is_valid_admin_login($username, $password) { global $db; $query = ' SELECT * FROM administrators WHERE username = :username AND password = :password'; $statement = $db->prepare($query); $statement->bindValue(':username', $username); $statement->bindValue(':password', $password); $statement->execute(); if ($statement->rowCount() == 1) { $valid = true; } else { $valid = false; } $statement->closeCursor(); return $valid; } ?>
TechSupport/ch21_tech_support/model/country_db.php
<?php function get_countries() { global $db; $query = 'SELECT * FROM countries ORDER BY countryName'; try { $statement = $db->prepare($query); $statement->execute(); $result = $statement->fetchAll(); $statement->closeCursor(); return $result; } catch (PDOException $e) { display_db_error($e->getMessage()); } } ?>
TechSupport/ch21_tech_support/model/customer_db.php
<?php function is_valid_customer_login($email, $password) { global $db; $query = ' SELECT * FROM customers WHERE email = :email AND password = :password'; $statement = $db->prepare($query); $statement->bindValue(':email', $email); $statement->bindValue(':password', $password); $statement->execute(); if ($statement->rowCount() == 1) { $valid = true; } else { $valid = false; } $statement->closeCursor(); return $valid; } function get_customers() { global $db; $query = 'SELECT * FROM customers ORDER BY lastName'; try { $statement = $db->prepare($query); $statement->execute(); $result = $statement->fetchAll(); $statement->closeCursor(); return $result; } catch (PDOException $e) { display_db_error($e->getMessage()); } } function get_customers_by_last_name($last_name) { global $db; $query = 'SELECT * FROM customers WHERE lastName = :last_name ORDER BY lastName'; try { $statement = $db->prepare($query); $statement->bindValue(':last_name', $last_name); $statement->execute(); $result = $statement->fetchAll(); $statement->closeCursor(); return $result; } catch (PDOException $e) { display_db_error($e->getMessage()); } } function get_customer($customer_id) { global $db; $query = 'SELECT * FROM customers WHERE customerID = :customer_id'; try { $statement = $db->prepare($query); $statement->bindValue(':customer_id', $customer_id); $statement->execute(); $result = $statement->fetch(); $statement->closeCursor(); return $result; } catch (PDOException $e) { display_db_error($e->getMessage()); } } function get_customer_by_email($email) { global $db; $query = 'SELECT * FROM customers WHERE email = :email'; try { $statement = $db->prepare($query); $statement->bindValue(':email', $email); $statement->execute(); $result = $statement->fetch(); $statement->closeCursor(); return $result; } catch (PDOException $e) { display_db_error($e->getMessage()); } } function delete_customer($customer_id) { global $db; $query = 'DELETE FROM customers WHERE customerID = :customer_id'; try { $statement = $db->prepare($query); $statement->bindValue(':customer_id', $customer_id); $row_count = $statement->execute(); $statement->closeCursor(); return $row_count; } catch (PDOException $e) { display_db_error($e->getMessage()); } } function add_customer($first_name, $last_name, $address, $city, $state, $postal_code, $country_code, $phone, $email, $password) { global $db; $query = 'INSERT INTO customers (firstName, lastName, address, city, state, postalCode, countryCode, phone, email, password) VALUES (:first_name, :last_name, :address, :city, :state, :postal_code, :country_code, :phone, :email, :password)'; try { $statement = $db->prepare($query); $statement->bindValue(':first_name', $first_name); $statement->bindValue(':last_name', $last_name); $statement->bindValue(':address', $address); $statement->bindValue(':city', $city); $statement->bindValue(':state', $state); $statement->bindValue(':postal_code', $postal_code); $statement->bindValue(':country_code', $country_code); $statement->bindValue(':phone', $phone); $statement->bindValue(':email', $email); $statement->bindValue(':password', $password); $statement->execute(); $statement->closeCursor(); // Get the last product ID that was automatically generated $id = $db->lastInsertId(); return $id; } catch (PDOException $e) { $error_message = $e->getMessage(); display_db_error($error_message); } } function update_customer($customer_id, $first_name, $last_name, $address, $city, $state, $postal_code, $country_code, $phone, $email, $password) { global $db; $query = 'UPDATE customers SET firstName = :first_name, lastName = :last_name, address = :address, city = :city, state = :state, postalCode = :postal_code, countryCode = :country_code, phone = :phone, email = :email, password = :password WHERE customerID = :customer_id'; try { $statement = $db->prepare($query); $statement->bindValue(':first_name', $first_name); $statement->bindValue(':last_name', $last_name); $statement->bindValue(':address', $address); $statement->bindValue(':city', $city); $statement->bindValue(':state', $state); $statement->bindValue(':postal_code', $postal_code); $statement->bindValue(':country_code', $country_code); $statement->bindValue(':phone', $phone); $statement->bindValue(':email', $email); $statement->bindValue(':password', $password); $statement->bindValue(':customer_id', $customer_id); $row_count = $statement->execute(); $statement->closeCursor(); return $row_count; } catch (PDOException $e) { $error_message = $e->getMessage(); display_db_error($error_message); } } ?>
TechSupport/ch21_tech_support/model/database.php
<?php $dsn = 'mysql:host=localhost;dbname=tech_support'; $username = 'ts_user'; $password = 'pa55word'; $options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION); try { $db = new PDO($dsn, $username, $password, $options); } catch (PDOException $e) { $error_message = $e->getMessage(); include('../errors/database_error.php'); exit(); } function display_db_error($error_message) { include '../errors/db_error.php'; exit; } ?>
TechSupport/ch21_tech_support/model/database_oo.php
<?php class Database { private static $dsn = 'mysql:host=localhost;dbname=tech_support'; private static $username = 'ts_user'; private static $password = 'pa55word'; private static $options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION); private static $db; private function __construct() {} public static function getDB () { if (!isset(self::$db)) { try { self::$db = new PDO(self::$dsn, self::$username, self::$password, self::$options); } catch (PDOException $e) { $error_message = $e->getMessage(); include('../errors/database_error.php'); exit(); } } return self::$db; } } ?>
TechSupport/ch21_tech_support/model/fields.php
<?php class Field { private $name; private $message = ''; private $hasError = false; public function __construct($name, $message = '') { $this->name = $name; $this->message = $message; } public function getName() { return $this->name; } public function getMessage() { return $this->message; } public function hasError() { return $this->hasError; } public function setErrorMessage($message) { $this->message = $message; $this->hasError = true; } public function clearErrorMessage() { $this->message = ''; $this->hasError = false; } public function getHTML() { $message = htmlspecialchars($this->message); if ($this->hasError()) { return '<span class="error">' . $message . '</span>'; } else { return '<span>' . $message . '</span>'; } } } class Fields { private $fields = array(); public function addField($name, $message = '') { $field = new Field($name, $message); $this->fields[$field->getName()] = $field; } public function getField($name) { return $this->fields[$name]; } public function hasErrors() { foreach ($this->fields as $field) { if ($field->hasError()) return true; } return false; } } ?>
TechSupport/ch21_tech_support/model/incident_db.php
<?php function get_incidents() { global $db; $query = 'SELECT c.firstName, c.lastName, i.* FROM incidents i INNER JOIN customers c ON c.customerID = i.customerID'; try { $statement = $db->prepare($query); $statement->execute(); $result = $statement->fetchAll(); $statement->closeCursor(); return $result; } catch (PDOException $e) { display_db_error($e->getMessage()); } } function get_incidents_unassigned() { global $db; $query = 'SELECT c.firstName, c.lastName, p.name AS productName, i.* FROM incidents i INNER JOIN customers c ON c.customerID = i.customerID INNER JOIN products p ON p.productCode = i.productCode WHERE techID IS NULL'; try { $statement = $db->prepare($query); $statement->execute(); $result = $statement->fetchAll(); $statement->closeCursor(); return $result; } catch (PDOException $e) { display_db_error($e->getMessage()); } } function get_incidents_assigned() { global $db; $query = 'SELECT c.firstName AS customerFirstName, c.lastName AS customerLastName, t.firstName AS techFirstName, t.lastName AS techLastName, p.name AS productName, i.* FROM incidents i INNER JOIN customers c ON c.customerID = i.customerID INNER JOIN products p ON p.productCode = i.productCode INNER JOIN technicians t ON t.techID = i.techID'; try { $statement = $db->prepare($query); $statement->execute(); $result = $statement->fetchAll(); $statement->closeCursor(); return $result; } catch (PDOException $e) { display_db_error($e->getMessage()); } } function get_incidents_by_technician($id) { global $db; $query = 'SELECT c.firstName, c.lastName, i.* FROM incidents i INNER JOIN customers c ON c.customerID = i.customerID WHERE techID = :id AND dateClosed IS NULL'; try { $statement = $db->prepare($query); $statement->bindValue(':id', $id); $statement->execute(); $result = $statement->fetchAll(); $statement->closeCursor(); return $result; } catch (PDOException $e) { display_db_error($e->getMessage()); } } function get_incident($id) { global $db; $query = 'SELECT * FROM incidents WHERE incidentID = :id'; try { $statement = $db->prepare($query); $statement->bindValue(':id', $id); $statement->execute(); $result = $statement->fetch(); $statement->closeCursor(); return $result; } catch (PDOException $e) { display_db_error($e->getMessage()); } } function add_incident($customer_id, $product_code, $title, $description) { global $db; $date_opened = date('Y-m-d'); // get current date in yyyy-mm-dd format $query = 'INSERT INTO incidents (customerID, productCode, dateOpened, title, description) VALUES ( :customer_id, :product_code, :date_opened, :title, :description)'; try { $statement = $db->prepare($query); $statement->bindValue(':customer_id', $customer_id); $statement->bindValue(':product_code', $product_code); $statement->bindValue(':date_opened', $date_opened); $statement->bindValue(':title', $title); $statement->bindValue(':description', $description); $statement->execute(); $statement->closeCursor(); // Get the last product ID that was automatically generated $id = $db->lastInsertId(); return $id; } catch (PDOException $e) { $error_message = $e->getMessage(); display_db_error($error_message); } } function assign_incident($incident_id, $technician_id) { global $db; $query = 'UPDATE incidents SET techID = :technician_id WHERE incidentID = :incident_id'; try { $statement = $db->prepare($query); $statement->bindValue(':incident_id', $incident_id); $statement->bindValue(':technician_id', $technician_id); $row_count = $statement->execute(); $statement->closeCursor(); return $row_count; } catch (PDOException $e) { $error_message = $e->getMessage(); display_db_error($error_message); } } function update_incident($incident_id, $date_closed, $description) { global $db; $query = 'UPDATE incidents SET dateClosed = :date_closed, description = :description WHERE incidentID = :incident_id'; try { $statement = $db->prepare($query); $statement->bindValue(':date_closed', $date_closed); $statement->bindValue(':description', $description); $statement->bindValue(':incident_id', $incident_id); $row_count = $statement->execute(); $statement->closeCursor(); return $row_count; } catch (PDOException $e) { $error_message = $e->getMessage(); display_db_error($error_message); } } ?>
TechSupport/ch21_tech_support/model/product_db.php
<?php function get_products() { global $db; $query = 'SELECT * FROM products ORDER BY name'; try { $statement = $db->prepare($query); $statement->execute(); $result = $statement->fetchAll(); $statement->closeCursor(); return $result; } catch (PDOException $e) { display_db_error($e->getMessage()); } } function get_products_by_customer($email) { global $db; $query = 'SELECT products.productCode, products.name FROM products INNER JOIN registrations ON products.productCode = registrations.productCode INNER JOIN customers ON registrations.customerID = customers.customerID WHERE customers.email = :email'; try { $statement = $db->prepare($query); $statement->bindValue(':email', $email); $statement->execute(); $result = $statement->fetchAll(); $statement->closeCursor(); return $result; } catch (PDOException $e) { display_db_error($e->getMessage()); } } function get_product($product_code) { global $db; $query = 'SELECT * FROM products WHERE productCode = :product_code'; try { $statement = $db->prepare($query); $statement->bindValue(':product_code', $product_code); $statement->execute(); $result = $statement->fetch(); $statement->closeCursor(); return $result; } catch (PDOException $e) { display_db_error($e->getMessage()); } } function delete_product($product_code) { global $db; $query = 'DELETE FROM products WHERE productCode = :product_code'; try { $statement = $db->prepare($query); $statement->bindValue(':product_code', $product_code); $row_count = $statement->execute(); $statement->closeCursor(); return $row_count; } catch (PDOException $e) { display_db_error($e->getMessage()); } } function add_product($code, $name, $version, $release_date) { global $db; $query = 'INSERT INTO products (productCode, name, version, releaseDate) VALUES (:code, :name, :version, :release_date)'; try { $statement = $db->prepare($query); $statement->bindValue(':code', $code); $statement->bindValue(':name', $name); $statement->bindValue(':version', $version); $statement->bindValue(':release_date', $release_date); $statement->execute(); $statement->closeCursor(); // Get the last product ID that was automatically generated $id = $db->lastInsertId(); return $id; } catch (PDOException $e) { $error_message = $e->getMessage(); display_db_error($error_message); } } function update_product($code, $name, $version, $release_date) { global $db; $query = 'UPDATE products SET name = :name, version = :version, releaseDate = :release_date WHERE productCode = :product_code'; try { $statement = $db->prepare($query); $statement->bindValue(':code', $code); $statement->bindValue(':name', $name); $statement->bindValue(':version', $version); $statement->bindValue(':release_date', $release_date); $row_count = $statement->execute(); $statement->closeCursor(); return $row_count; } catch (PDOException $e) { $error_message = $e->getMessage(); display_db_error($error_message); } } ?>
TechSupport/ch21_tech_support/model/registration_db.php
<?php function add_registration($customer_id, $product_code) { global $db; $date = date('Y-m-d'); // get current date in yyyy-mm-dd format $query = 'INSERT INTO registrations VALUES (:customer_id, :product_code, :date)'; try { $statement = $db->prepare($query); $statement->bindValue(':customer_id', $customer_id); $statement->bindValue(':product_code', $product_code); $statement->bindValue(':date', $date); $statement->execute(); $statement->closeCursor(); // Get the last product ID that was automatically generated $id = $db->lastInsertId(); return $id; } catch (PDOException $e) { $error_message = $e->getMessage(); display_db_error($error_message); } } ?>
TechSupport/ch21_tech_support/model/technician.php
<?php class Technician { private $id, $first_name, $last_name, $email, $phone, $password; public function __construct($first_name, $last_name, $email, $phone, $password) { $this->first_name = $first_name; $this->last_name = $last_name; $this->email = $email; $this->phone = $phone; $this->password = $password; } public function getID() { return $this->id; } public function setID($value) { $this->id = $value; } public function getFirstName() { return $this->first_name; } public function setFirstName($value) { $this->first_name = $value; } public function getLastName() { return $this->last_name; } public function setLastName($value) { $this->last_name = $value; } public function getFullName() { return $this->first_name . ' ' . $this->last_name; } public function getEmail() { return $this->email; } public function setEmail($value) { $this->email = $value; } public function getPhone() { return $this->phone; } public function setPhone($value) { $this->phone = $value; } public function getPassword() { return $this->password; } public function setPassword($value) { $this->password = $value; } } ?>
TechSupport/ch21_tech_support/model/technician_db.php
<?php function is_valid_technician_login($email, $password) { global $db; $query = ' SELECT * FROM technicians WHERE email = :email AND password = :password'; $statement = $db->prepare($query); $statement->bindValue(':email', $email); $statement->bindValue(':password', $password); $statement->execute(); if ($statement->rowCount() == 1) { $valid = true; } else { $valid = false; } $statement->closeCursor(); return $valid; } function get_technicians() { global $db; $query = 'SELECT * FROM technicians ORDER BY lastName'; try { $statement = $db->prepare($query); $statement->execute(); $result = $statement->fetchAll(); $statement->closeCursor(); return $result; } catch (PDOException $e) { display_db_error($e->getMessage()); } } function get_technicians_with_count() { global $db; $query = 'SELECT *, (SELECT COUNT(*) FROM incidents WHERE incidents.techID = technicians.techID) AS openIncidentCount FROM technicians ORDER BY openIncidentCount'; try { $statement = $db->prepare($query); $statement->execute(); $result = $statement->fetchAll(); $statement->closeCursor(); return $result; } catch (PDOException $e) { display_db_error($e->getMessage()); } } function get_technician($id) { global $db; $query = 'SELECT * FROM technicians WHERE techID = :id'; try { $statement = $db->prepare($query); $statement->bindValue(':id', $id); $statement->execute(); $result = $statement->fetch(); $statement->closeCursor(); return $result; } catch (PDOException $e) { display_db_error($e->getMessage()); } } function get_technician_by_email($email) { global $db; $query = 'SELECT * FROM technicians WHERE email = :email'; try { $statement = $db->prepare($query); $statement->bindValue(':email', $email); $statement->execute(); $result = $statement->fetch(); $statement->closeCursor(); return $result; } catch (PDOException $e) { display_db_error($e->getMessage()); } } function delete_technician($technician_id) { global $db; $query = 'DELETE FROM technicians WHERE techID = :technician_id'; try { $statement = $db->prepare($query); $statement->bindValue(':technician_id', $technician_id); $row_count = $statement->execute(); $statement->closeCursor(); return $row_count; } catch (PDOException $e) { display_db_error($e->getMessage()); } } function add_technician($first_name, $last_name, $email, $phone, $password) { global $db; $query = 'INSERT INTO technicians (firstName, lastName, email, phone, password) VALUES (:first_name, :last_name, :email, :phone, :password)'; try { $statement = $db->prepare($query); $statement->bindValue(':first_name', $first_name); $statement->bindValue(':last_name', $last_name); $statement->bindValue(':email', $email); $statement->bindValue(':phone', $phone); $statement->bindValue(':password', $password); $statement->execute(); $statement->closeCursor(); // Get the last product ID that was automatically generated $id = $db->lastInsertId(); return $id; } catch (PDOException $e) { $error_message = $e->getMessage(); display_db_error($error_message); } } function update_technician($id, $first_name, $last_name, $email, $phone, $password) { global $db; $query = 'UPDATE technicians SET firstName = :first_name, lastName = :last_name, email = :email, phone = :phone, password = :password WHERE technicianID = :id'; try { $statement = $db->prepare($query); $statement->bindValue(':id', $id); $statement->bindValue(':first_name', $first_name); $statement->bindValue(':last_name', $last_name); $statement->bindValue(':email', $email); $statement->bindValue(':phone', $phone); $statement->bindValue(':password', $password); $row_count = $statement->execute(); $statement->closeCursor(); return $row_count; } catch (PDOException $e) { $error_message = $e->getMessage(); display_db_error($error_message); } } ?>
TechSupport/ch21_tech_support/model/technician_db_oo.php
<?php class TechnicianDB { public static function getTechnicians() { $db = Database::getDB(); $query = 'SELECT * FROM technicians ORDER BY lastName'; // get an array of rows try { $statement = $db->prepare($query); $statement->execute(); $rows = $statement->fetchAll(); $statement->closeCursor(); } catch (PDOException $e) { display_db_error($e->getMessage()); } // convert the array of rows to an array of Technician objects $technicians = array(); foreach($rows as $row) { $t = new Technician( $row['firstName'], $row['lastName'], $row['email'], $row['phone'], $row['password']); $t->setID($row['techID']); $technicians[] = $t; } return $technicians; } public static function deleteTechnician($technician_id) { $db = Database::getDB(); $query = 'DELETE FROM technicians WHERE techID = :technician_id'; try { $statement = $db->prepare($query); $statement->bindValue(':technician_id', $technician_id); $row_count = $statement->execute(); $statement->closeCursor(); return $row_count; } catch (PDOException $e) { display_db_error($e->getMessage()); } } public static function addTechnician($t) { $db = Database::getDB(); $first_name = $t->getFirstName(); $last_name = $t->getLastName(); $email = $t->getEmail(); $phone = $t->getPhone(); $password = $t->getPassword(); $query = 'INSERT INTO technicians (firstName, lastName, email, phone, password) VALUES (:first_name, :last_name, :email, :phone, :password)'; try { $statement = $db->prepare($query); $statement->bindValue(':first_name', $first_name); $statement->bindValue(':last_name', $last_name); $statement->bindValue(':email', $email); $statement->bindValue(':phone', $phone); $statement->bindValue(':password', $password); $statement->execute(); $statement->closeCursor(); // Get the last product ID that was automatically generated $id = $db->lastInsertId(); return $id; } catch (PDOException $e) { $error_message = $e->getMessage(); display_db_error($error_message); } } } ?>
TechSupport/ch21_tech_support/model/validate.php
<?php class Validate { private $fields; public function __construct() { $this->fields = new Fields(); } public function getFields() { return $this->fields; } // Validate a generic text field public function text($name, $value, $required = true, $min = 1, $max = 255) { // Get Field object $field = $this->fields->getField($name); // If field is not required and empty, remove errors and exit if (!$required && empty($value)) { $field->clearErrorMessage(); return; } // Check field and set or clear error message if ($required && empty($value)) { $field->setErrorMessage('Required.'); } else if (strlen($value) < $min) { $field->setErrorMessage('Too short.'); } else if (strlen($value) > $max) { $field->setErrorMessage('Too long.'); } else { $field->clearErrorMessage(); } } // Validate a field with a generic pattern public function pattern($name, $value, $pattern, $message, $required = true) { // Get Field object $field = $this->fields->getField($name); // If field is not required and empty, remove errors and exit if (!$required && empty($value)) { $field->clearErrorMessage(); return; } // Check field and set or clear error message $match = preg_match($pattern, $value); if ($match === false) { $field->setErrorMessage('Error testing field.'); } else if ( $match != 1 ) { $field->setErrorMessage($message); } else { $field->clearErrorMessage(); } } public function phone($name, $value, $required = false) { $field = $this->fields->getField($name); // Call the text method and exit if it yields an error $this->text($name, $value, $required); if ($field->hasError()) { return; } // Call the pattern method to validate a phone number in the (999) 999-9999 format $pattern = '/^\([[:digit:]]{3}\) [[:digit:]]{3}-[[:digit:]]{4}$/'; $message = 'Use (999) 999-9999 format.'; $this->pattern($name, $value, $pattern, $message, $required); } public function email($name, $value, $required = true) { $field = $this->fields->getField($name); // If field is not required and empty, remove errors and exit if (!$required && empty($value)) { $field->clearErrorMessage(); return; } // Call the text method and exit if it yields an error $this->text($name, $value, $required); if ($field->hasError()) { return; } // Split email address on @ sign and check parts $parts = explode('@', $value); if (count($parts) < 2) { $field->setErrorMessage('At sign required.'); return; } if (count($parts) > 2) { $field->setErrorMessage('Only one at sign allowed.'); return; } $local = $parts[0]; $domain = $parts[1]; // Check lengths of local and domain parts if (strlen($local) > 64) { $field->setErrorMessage('Username part too long.'); return; } if (strlen($domain) > 255) { $field->setErrorMessage('Domain name part too long.'); return; } // Patterns for address formatted local part $atom = '[[:alnum:]_!#$%&\'*+\/=?^`{|}~-]+'; $dotatom = '(\.' . $atom . ')*'; $address = '(^' . $atom . $dotatom . '$)'; // Patterns for quoted text formatted local part $char = '([^\\\\"])'; $esc = '(\\\\[\\\\"])'; $text = '(' . $char . '|' . $esc . ')+'; $quoted = '(^"' . $text . '"$)'; // Combined pattern for testing local part $localPattern = '/' . $address . '|' . $quoted . '/'; // Call the pattern method and exit if it yields an error $this->pattern($name, $local, $localPattern, 'Invalid username part.'); if ($field->hasError()) { return; } // Patterns for domain part $hostname = '([[:alnum:]]([-[:alnum:]]{0,62}[[:alnum:]])?)'; $hostnames = '(' . $hostname . '(\.' . $hostname . ')*)'; $top = '\.[[:alnum:]]{2,6}'; $domainPattern = '/^' . $hostnames . $top . '$/'; // Call the pattern method $this->pattern($name, $domain, $domainPattern, 'Invalid domain name part.'); } public function password($name, $password, $required = true) { $field = $this->fields->getField($name); if (!$required && empty($value)) { $field->clearErrorMessage(); return; } // Must be at least 6 characters $this->text($name, $password, $required, 6, 20); if ($field->hasError()) { return; } } } ?>
TechSupport/ch21_tech_support/nbproject/private/config.properties
TechSupport/ch21_tech_support/nbproject/private/private.properties
auxiliary.org-netbeans-modules-web-client-tools-api.clientdebug=false auxiliary.org-netbeans-modules-web-client-tools-api.dialogShowDebugPanel=true auxiliary.org-netbeans-modules-web-client-tools-api.FIREFOX=true auxiliary.org-netbeans-modules-web-client-tools-api.INTERNET_5f_EXPLORER=false auxiliary.org-netbeans-modules-web-client-tools-api.serverdebug=true run.as=LOCAL url=http://localhost/project_solutions/ch21_tech_support/
TechSupport/ch21_tech_support/nbproject/private/private.xml
TechSupport/ch21_tech_support/nbproject/project.properties
include.path=${php.global.include.path} php.version=PHP_5 source.encoding=UTF-8 src.dir=. tags.asp=false tags.short=true web.root=.
TechSupport/ch21_tech_support/nbproject/project.xml
org.netbeans.modules.php.project ch21_tech_support
TechSupport/ch21_tech_support/product_manager/index.php
<?php require('../model/database.php'); require('../model/product_db.php'); session_start(); if (isset($_POST['action'])) { $action = $_POST['action']; } else if (isset($_GET['action'])) { $action = $_GET['action']; } else { $action = 'list_products'; } switch ($action) { case 'list_products': // Get product data $products = get_products(); // Display the product list include('product_list.php'); break; case 'delete_product': $product_code = $_POST['product_code']; delete_product($product_code); header("Location: ."); break; case 'show_add_form': include('product_add.php'); break; case 'add_product': $code = $_POST['code']; $name = $_POST['name']; $version = $_POST['version']; $release_date = $_POST['release_date']; $ts = strtotime($release_date); $release_date_db = date('Y-m-d', $ts); // convert to yyyy-mm-dd format for database storage // Validate the inputs if (empty($code) || empty($name) || empty($version) || empty($release_date_db)) { $error = "Invalid product data. Check all fields and try again."; include('../errors/error.php'); } else { add_product($code, $name, $version, $release_date_db); header("Location: ."); } break; } ?>
TechSupport/ch21_tech_support/product_manager/product_add.php
<?php require_once('../util/secure_conn.php'); require_once('../util/valid_admin.php'); ?> <?php include '../view/header.php'; ?> <div id="main"> <h1>Add Product</h1> <form action="" method="post" id="aligned"> <input type="hidden" name="action" value="add_product" /> <label>Code:</label> <input type="input" name="code" /> <br /> <label>Name:</label> <input type="input" name="name" /> <br /> <label>Version:</label> <input type="input" name="version" /> <br /> <label>Release Date:</label> <input type="input" name="release_date" /> <span>Use any valid date format</span> <br /> <label> </label> <input type="submit" value="Add Product" /> <br /> </form> <p><a href="?action=list_products">View Product List</a></p> </div> <?php include '../view/footer.php'; ?>
TechSupport/ch21_tech_support/product_manager/product_list.php
<?php require_once('../util/secure_conn.php'); require_once('../util/valid_admin.php'); ?> <?php include '../view/header.php'; ?> <div id="main"> <h1>Product List</h1> <div id="content"> <!-- display a table of products --> <table> <tr> <th>Code</th> <th>Name</th> <th>Version</th> <th>Release Date</th> <th> </th> </tr> <?php foreach ($products as $product) : $ts = strtotime($product['releaseDate']); $release_date_formatted = date('n/j/Y', $ts); ?> <tr> <td><?php echo $product['productCode']; ?></td> <td><?php echo $product['name']; ?></td> <td><?php echo $product['version']; ?></td> <td><?php echo $release_date_formatted; ?></td> <td><form action="." method="post"> <input type="hidden" name="action" value="delete_product" /> <input type="hidden" name="product_code" value="<?php echo $product['productCode']; ?>" /> <input type="submit" value="Delete" /> </form></td> </tr> <?php endforeach; ?> </table> <p><a href="?action=show_add_form">Add Product</a></p> </div> </div> <?php include '../view/footer.php'; ?>
TechSupport/ch21_tech_support/product_register/customer_login.php
<?php require_once('../util/secure_conn.php'); // require_once('../util/valid_customer.php'); ?> <?php include '../view/header.php'; ?> <div id="main"> <h2>Customer Login</h2> <p>You must login before you can register a product.</p> <div id="content"> <!-- display a search form --> <form action="" method="post" id="aligned"> <input type="hidden" name="action" value="display_register" /> <label>Email:</label> <input type="input" name="email" value="<?php echo $customer['email']; ?>" /> <br /> <label>Password:</label> <input type="password" name="password" value="<?php echo $customer['password']; ?>" /> <br /> <label> </label> <input type="submit" value="Login" /> </form> </div> </div> <?php include '../view/footer.php'; ?>
TechSupport/ch21_tech_support/product_register/index.php
<?php require('../model/database.php'); require('../model/customer_db.php'); require('../model/product_db.php'); require('../model/registration_db.php'); // Start session session_start(); if (isset($_POST['action'])) { $action = $_POST['action']; } else if (isset($_GET['action'])) { $action = $_GET['action']; } else if (isset($_SESSION['customer'])) { // Skip login if customer is in the session $action = 'display_register'; } else { $action = 'display_login'; } switch ($action) { case 'display_login': include('customer_login.php'); break; case 'display_register': // If customer is not in the session, set it in the session if (!isset($_SESSION['customer'])) { $email = $_POST['email']; $password = $_POST['password']; if (is_valid_customer_login($email, $password)) { $customer = get_customer_by_email($email); $_SESSION['customer'] = $customer; } } $customer = $_SESSION['customer']; $products = get_products(); include('product_register.php'); break; case 'register_product': $customer = $_SESSION['customer']; $product_code = $_POST['product_code']; add_registration($customer['customerID'], $product_code); $message = "Product ($product_code) was registered successfully."; include('product_register.php'); break; case 'logout': unset($_SESSION['customer']); include('customer_login.php'); break; } ?>
TechSupport/ch21_tech_support/product_register/product_register.php
<?php require_once('../util/secure_conn.php'); require_once('../util/valid_customer.php'); ?> <?php include '../view/header.php'; ?> <div id="main"> <div id="content"> <h2>Register Product</h2> <?php if (isset($message)) : ?> <p><?php echo $message; ?></p> <?php else: ?> <form action="" method="post" id="aligned"> <input type="hidden" name="action" value="register_product" /> <label>Customer:</label> <span><?php echo $customer['firstName'] . ' ' . $customer['lastName'] ?></span> <br /> <label>Product:</label> <select name="product_code"> <?php foreach ($products as $product) : ?> <option value="<?php echo $product['productCode']; ?>"> <?php echo $product['name']; ?> </option> <?php endforeach; ?> </select> <br /> <label> </label> <input type="submit" value="Register Product" /> </form> <?php endif; ?> <br /> <p>You are logged in as <?php echo $customer['email']; ?></p> <form action="" method="post"> <input type="hidden" name="action" value="logout" /> <input type="submit" value="Logout" /> </form> </div> </div> <?php include '../view/footer.php'; ?>
TechSupport/ch21_tech_support/technician_manager/index.php
<?php require('../model/database.php'); require('../model/database_oo.php'); require('../model/technician.php'); require('../model/technician_db_oo.php'); session_start(); if (isset($_POST['action'])) { $action = $_POST['action']; } else if (isset($_GET['action'])) { $action = $_GET['action']; } else { $action = 'list_technicians'; } switch ($action) { case 'list_technicians': // Get technician data $technicians = TechnicianDB::getTechnicians(); // Display the technician list include('technician_list.php'); break; case 'delete_technician': $technician_id = $_POST['technician_id']; TechnicianDB::deleteTechnician($technician_id); header("Location: ."); break; case 'show_add_form': include('technician_add.php'); break; case 'add_technician': $first_name = $_POST['first_name']; $last_name = $_POST['last_name']; $email = $_POST['email']; $phone = $_POST['phone']; $password = $_POST['password']; // Validate the inputs if (empty($first_name) || empty($last_name) || empty($email) || empty($phone) || empty($password)) { $error = "Invalid technician data. Check all fields and try again."; include('../errors/error.php'); } else { // Create technician object $t = new Technician($first_name, $last_name, $email, $phone, $password); TechnicianDB::addTechnician($t); header("Location: ."); } break; } ?>
TechSupport/ch21_tech_support/technician_manager/technician_add.php
<?php require_once('../util/secure_conn.php'); require_once('../util/valid_admin.php'); ?> <?php include '../view/header.php'; ?> <div id="main"> <h1>Add Technician</h1> <form action="" method="post" id="aligned"> <input type="hidden" name="action" value="add_technician" /> <label>First Name:</label> <input type="input" name="first_name" /> <br /> <label>Last Name:</label> <input type="input" name="last_name" /> <br /> <label>Email:</label> <input type="input" name="email" /> <br /> <label>Phone:</label> <input type="input" name="phone" /> <br /> <label>Password:</label> <input type="input" name="password" /> <br /> <label> </label> <input type="submit" value="Add Technician" /> <br /> </form> <p><a href="?action=list_technicians">View Technician List</a></p> </div> <?php include '../view/footer.php'; ?>
TechSupport/ch21_tech_support/technician_manager/technician_list.php
<?php require_once('../util/secure_conn.php'); require_once('../util/valid_admin.php'); ?> <?php include '../view/header.php'; ?> <div id="main"> <h1>Technician List</h1> <div id="content"> <!-- display a table of technicians --> <table> <tr> <th>Name</th> <th>Email</th> <th>Phone</th> <th>Password</th> <th> </th> </tr> <?php foreach ($technicians as $technician) : ?> <tr> <td><?php echo $technician->getFullName(); ?></td> <td><?php echo $technician->getEmail(); ?></td> <td><?php echo $technician->getPhone(); ?></td> <td><?php echo $technician->getPassword(); ?></td> <td><form action="." method="post"> <input type="hidden" name="action" value="delete_technician" /> <input type="hidden" name="technician_id" value="<?php echo $technician->getID(); ?>" /> <input type="submit" value="Delete" /> </form></td> </tr> <?php endforeach; ?> </table> <p><a href="?action=show_add_form">Add Technician</a></p> </div> </div> <?php include '../view/footer.php'; ?>
TechSupport/ch21_tech_support/under_construction.php
<?php include 'view/header.php'; ?> <div id="main"> <h2>Sorry, this page is currently under construction.</h2> <p>We'll finish it as quickly as we can. Thanks!</p> </div> <?php include 'view/footer.php'; ?>
TechSupport/ch21_tech_support/util/secure_conn.php
<?php // make sure the page uses a secure connection if (!isset($_SERVER['HTTPS'])) { $url = 'https://' . $_SERVER['HTTP_HOST'] . $_SERVER['REQUEST_URI']; header("Location: " . $url); exit(); } ?>
TechSupport/ch21_tech_support/util/valid_admin.php
<?php // make sure the user is logged in as a valid administrator if (!isset($_SESSION['admin'])) { header('Location: .' ); } ?>
TechSupport/ch21_tech_support/util/valid_customer.php
<?php // make sure the user is logged in as a valid customer if (!isset($_SESSION['customer'])) { header('Location: .' ); } ?>
TechSupport/ch21_tech_support/util/valid_technician.php
<?php // make sure the user is logged in as a valid technician if (!isset($_SESSION['technician'])) { header('Location: .' ); } ?>
TechSupport/ch21_tech_support/view/footer.php
<div id="footer"> <p class="copyright"> © <?php echo date("Y"); ?> SportsPro, Inc. </p> </div> </div><!-- end page --> </body> </html>
TechSupport/ch21_tech_support/view/header.php
SportsPro Technical Support
Sports management software for the sports enthusiast
TechSupport/tech_support.sql
/***************************************** * Create the tech_support database *****************************************/ DROP DATABASE IF EXISTS tech_support; CREATE DATABASE tech_support; USE tech_support; CREATE TABLE products ( productCode varchar(10) NOT NULL, name varchar(50) NOT NULL, version decimal(18, 1) NOT NULL, releaseDate datetime NOT NULL, PRIMARY KEY (productCode) ); INSERT INTO products VALUES ('DRAFT10', 'Draft Manager 1.0', 1.0, '2008-03-01'), ('DRAFT20', 'Draft Manager 2.0', 2.0, '2010-08-15'), ('LEAG10', 'League Scheduler 1.0', 1.0, '2007-06-01'), ('LEAGD10', 'League Scheduler Deluxe 1.0', 1.0, '2007-09-01'), ('TEAM10', 'Team Manager Version 1.0', 1.0, '2008-06-01'), ('TRNY10', 'Tournament Master Version 1.0', 1.0, '2007-01-01'), ('TRNY20', 'Tournament Master Version 2.0', 2.0, '2009-03-15'); CREATE TABLE technicians ( techID int NOT NULL AUTO_INCREMENT, firstName varchar(50) NOT NULL, lastName varchar(50) NOT NULL, email varchar(50) NOT NULL UNIQUE, phone varchar(20) NOT NULL, password varchar(20) NOT NULL, PRIMARY KEY (techID) ); INSERT INTO technicians VALUES (11, 'Alison', 'Diaz', '[email protected]', '800-555-0443', 'sesame'), (12, 'Jason', 'Lee', '[email protected]', '800-555-0444', 'sesame'), (13, 'Andrew', 'Wilson', '[email protected]', '800-555-0449', 'sesame'), (14, 'Gunter', 'Wendt', '[email protected]', '800-555-0400', 'sesame'), (15, 'Gina', 'Fiori', '[email protected]', '800-555-0459', 'sesame'); CREATE TABLE customers ( customerID int NOT NULL AUTO_INCREMENT, firstName varchar(50) NOT NULL, lastName varchar(50) NOT NULL, address varchar(50) NOT NULL, city varchar(50) NOT NULL, state varchar(50) NOT NULL, postalCode varchar(20) NOT NULL, countryCode char(2) NOT NULL, phone varchar(20) NOT NULL, email varchar(50) NOT NULL UNIQUE, password varchar(20) NOT NULL, PRIMARY KEY (customerID) ); INSERT INTO customers VALUES (1002, 'Kelly', 'Irvin', 'PO Box 96621', 'Washington', 'DC', '20090', 'US', '(301) 555-8950', '[email protected]', 'sesame'), (1004, 'Kenzie', 'Quinn', '1990 Westwood Blvd Ste 260', 'Los Angeles', 'CA', '90025', 'US', '(800) 555-8725', '[email protected]', 'sesame'), (1006, 'Anton', 'Mauro', '3255 Ramos Cir', 'Sacramento', 'CA', '95827', 'US', '(916) 555-6670', '[email protected]', 'sesame'), (1008, 'Kaitlyn', 'Anthoni', 'Box 52001', 'San Francisco', 'CA', '94152', 'US', '(800) 555-6081', '[email protected]', 'sesame'), (1010, 'Kendall', 'Mayte', 'PO Box 2069', 'Fresno', 'CA', '93718', 'US', '(559) 555-9999', '[email protected]', 'sesame'), (1012, 'Marvin', 'Quintin', '4420 N. First Street, Suite 108', 'Fresno', 'CA', '93726', 'US', '(559) 555-9586', '[email protected]', 'sesame'), (1015, 'Gonzalo', 'Keeton', '27371 Valderas', 'Mission Viejo', 'CA', '92691', 'US', '(214) 555-3647', '', 'sesame'), (1016, 'Derek', 'Chaddick', '1952 "H" Street', 'Fresno', 'CA', '93718', 'US', '(559) 555-3005', '[email protected]', 'sesame'), (1017, 'Malia', 'Marques', '7700 Forsyth', 'St Louis', 'MO', '63105', 'US', '(314) 555-8834', '[email protected]', 'sesame'), (1018, 'Emily', 'Evan', '1555 W Lane Ave', 'Columbus', 'OH', '43221', 'US', '(614) 555-4435', '[email protected]', 'sesame'), (1019, 'Alexandro', 'Alexis', '3711 W Franklin', 'Fresno', 'CA', '93706', 'US', '(559) 555-2993', '[email protected]', 'sesame'), (1023, 'Ingrid', 'Neil', '12 Daniel Road', 'Fairfield', 'NJ', '07004', 'US', '(201) 555-9742', '[email protected]', 'sesame'), (1026, 'Eileen', 'Lawrence', '1483 Chain Bridge Rd, Ste 202', 'Mclean', 'VA', '22101', 'US', '(770) 555-9558', '[email protected]', 'sesame'), (1027, 'Marjorie', 'Essence', 'PO Box 31', 'East Brunswick', 'NJ', '08810', 'US', '(800) 555-8110', '[email protected]', 'sesame'), (1029, 'Trentin', 'Camron', 'PO Box 61000', 'San Francisco', 'CA', '94161', 'US', '(800) 555-4426', '[email protected]', 'sesame'), (1030, 'Demetrius', 'Hunter', 'PO Box 956', 'Selma', 'CA', '93662', 'US', '(559) 555-1534', '[email protected]', 'sesame'), (1033, 'Thalia', 'Neftaly', '60 Madison Ave', 'New York', 'NY', '10010', 'US', '(212) 555-4800', '[email protected]', 'sesame'), (1034, 'Harley', 'Myles', 'PO Box 7028', 'St Louis', 'MO', '63177', 'US', '(301) 555-1494', '[email protected]', 'sesame'), (1037, 'Gideon', 'Paris', '1033 N Sycamore Ave.', 'Los Angeles', 'CA', '90038', 'US', '(213) 555-4322', '[email protected]', 'sesame'), (1038, 'Jayda', 'Maxwell', 'PO Box 39046', 'Minneapolis', 'MN', '55439', 'US', '(612) 555-0057', '[email protected]', 'sesame'), (1040, 'Kristofer', 'Gerald', 'PO Box 40513', 'Jacksonville', 'FL', '32231', 'US', '(800) 555-6041', '[email protected]', 'sesame'), (1045, 'Priscilla', 'Smith', 'Box 1979', 'Marion', 'OH', '43305', 'US', '(800) 555-1669', '[email protected]', 'sesame'), (1047, 'Brian', 'Griffin', '1150 N Tustin Ave', 'Anaheim', 'CA', '92807', 'US', '(714) 555-9000', '[email protected]', 'sesame'), (1049, 'Kaylea', 'Cheyenne', '2384 E Gettysburg', 'Fresno', 'CA', '93726', 'US', '(559) 555-0765', '[email protected]', 'sesame'), (1050, 'Kayle', 'Misael', 'PO Box 95857', 'Chicago', 'IL', '60694', 'US', '(800) 555-5811', '[email protected]', 'sesame'), (1051, 'Clarence', 'Maeve', 'PO Box 7247-7051', 'Philadelphia', 'PA', '19170', 'US', '(215) 555-8700', '[email protected]', 'sesame'), (1054, 'Jovon', 'Walker', '627 Aviation Way', 'Manhatttan Beach', 'CA', '90266', 'US', '(310) 555-2732', '[email protected]', 'sesame'), (1056, 'Nashalie', 'Angelica', '828 S Broadway', 'Tarrytown', 'NY', '10591', 'US', '(800) 555-0037', '[email protected]', 'sesame'), (1063, 'Leroy', 'Aryn', '3502 W Greenway #7', 'Phoenix', 'AZ', '85023', 'US', '(602) 547-0331', '[email protected]', 'sesame'), (1065, 'Anne', 'Braydon', 'PO Box 942', 'Fresno', 'CA', '93714', 'US', '(559) 555-7900', '[email protected]', 'sesame'), (1066, 'Leah', 'Colton', '1626 E Street', 'Fresno', 'CA', '93786', 'US', '(559) 555-4442', '[email protected]', 'sesame'), (1067, 'Cesar', 'Arodondo', '4545 Glenmeade Lane', 'Auburn Hills', 'MI', '48326', 'US', '(810) 555-3700', '[email protected]', 'sesame'), (1068, 'Rachael', 'Danielson', '353 E Shaw Ave', 'Fresno', 'CA', '93710', 'US', '(559) 555-1704', '[email protected]', 'sesame'), (1070, 'Salina', 'Edgardo', '6435 North Palm Ave, Ste 101', 'Fresno', 'CA', '93704', 'US', '(559) 555-7070', '[email protected]', 'sesame'), (1071, 'Daniel', 'Bradlee', '4 Cornwall Dr Ste 102', 'East Brunswick', 'NJ', '08816', 'US', '(908) 555-7222', '[email protected]', 'sesame'), (1074, 'Quentin', 'Warren', 'PO Box 12332', 'Fresno', 'CA', '93777', 'US', '(559) 555-3112', '[email protected]', 'sesame'), (1080, 'Jillian', 'Clifford', '3250 Spring Grove Ave', 'Cincinnati', 'OH', '45225', 'US', '(800) 555-1957', '[email protected]', 'sesame'), (1081, 'Angel', 'Lloyd', 'Department #1872', 'San Francisco', 'CA', '94161', 'US', '(617) 555-0700', '[email protected]', 'sesame'), (1083, 'Jeanette', 'Helena', '4775 E Miami River Rd', 'Cleves', 'OH', '45002', 'US', '(513) 555-3043', '[email protected]', 'sesame'), (1086, 'Luciano', 'Destin', 'P O Box 7126', 'Pasadena', 'CA', '91109', 'US', '(800) 555-7009', '[email protected]', 'sesame'), (1089, 'Kyra', 'Francis', '4150 W Shaw Ave ', 'Fresno', 'CA', '93722', 'US', '(559) 555-8300', '[email protected]', 'sesame'), (1094, 'Lance', 'Potter', '28210 N Avenue Stanford', 'Valencia', 'CA', '91355', 'US', '(805) 555-0584', '[email protected]', 'sesame'), (1097, 'Jeffrey', 'Smitzen', 'Post Office Box 924', 'New Delhi', '', '110001', 'IN', '91-12345-12345', '[email protected]', 'sesame'), (1098, 'Vance', 'Smith', '9 River Pk Pl E 400', 'Boston', 'MA', '02134', 'US', '(508) 555-8737', '[email protected]', 'sesame'), (1100, 'Thom', 'Aaronsen', '7112 N Fresno St Ste 200', 'Fresno', 'CA', '93720', 'US', '(559) 555-8484', '[email protected]', 'sesame'), (1112, 'Harold', 'Spivak', '2874 S Cherry Ave', 'Fresno', 'CA', '93706', 'US', '(559) 555-2770', '[email protected]', 'sesame'), (1113, 'Rachael', 'Bluzinski', 'P.O. Box 860070', 'Pasadena', 'CA', '91186', 'US', '(415) 555-7600', '[email protected]', 'sesame'), (1114, 'Reba', 'Hernandez', 'PO Box 2061', 'Fresno', 'CA', '93718', 'US', '(559) 555-0600', '[email protected]', 'sesame'), (1116, 'Jaime', 'Ronaldsen', '3467 W Shaw Ave #103', 'Fresno', 'CA', '93711', 'US', '(559) 555-8625', '[email protected]', 'sesame'), (1117, 'Violet', 'Beauregard', 'P.O. Box 505820', 'Reno', 'NV', '88905', 'US', '(800) 555-0855', '[email protected]', 'sesame'), (1118, 'Charlie', 'Bucket', 'Lodhi Road', 'New Delhi', '', '110003', 'IN', '(800) 555-4091', '[email protected]', 'sesame'); CREATE TABLE registrations ( customerID int NOT NULL, productCode varchar(10) NOT NULL, registrationDate datetime NOT NULL, PRIMARY KEY (customerID, productCode) ); INSERT INTO registrations VALUES (1002, 'LEAG10', '2008-11-01'), (1004, 'DRAFT10', '2009-01-11'), (1004, 'LEAG10', '2007-09-19'), (1004, 'TRNY10', '2009-01-13'), (1006, 'TRNY10', '2009-11-18'), (1008, 'DRAFT10', '2008-08-03'), (1008, 'LEAG10', '2007-10-29'), (1008, 'TEAM10', '2009-03-01'), (1008, 'TRNY10', '2007-04-02'), (1010, 'LEAG10', '2008-01-29'), (1012, 'DRAFT10', '2008-03-19'), (1015, 'TRNY10', '2007-05-19'), (1016, 'TEAM10', '2009-02-14'), (1017, 'TRNY10', '2009-05-09'), (1018, 'TEAM10', '2008-06-03'), (1018, 'TRNY10', '2007-12-25'), (1019, 'TRNY20', '2009-06-20'), (1023, 'LEAGD10', '2008-05-12'), (1026, 'LEAG10', '2008-01-02'), (1027, 'LEAGD10', '2008-03-14'), (1029, 'LEAGD10', '2009-10-18'), (1029, 'TEAM10', '2009-03-28'), (1030, 'LEAG10', '2008-01-04'), (1033, 'DRAFT10', '2008-07-20'), (1034, 'DRAFT10', '2008-03-20'), (1034, 'LEAGD10', '2009-02-21'), (1034, 'TEAM10', '2009-02-22'), (1037, 'LEAGD10', '2008-03-10'), (1038, 'LEAG10', '2008-01-03'), (1038, 'TRNY10', '2007-04-03'), (1040, 'TRNY10', '2007-04-07'), (1045, 'LEAGD10', '2008-01-14'), (1047, 'LEAGD10', '2008-02-14'), (1047, 'TEAM10', '2008-10-27'), (1047, 'TRNY20', '2010-02-27'), (1049, 'DRAFT10', '2009-01-11'), (1049, 'LEAGD10', '2008-07-12'), (1049, 'TRNY10', '2009-09-21'), (1049, 'TRNY20', '2009-07-12'), (1050, 'LEAGD10', '2008-08-24'), (1051, 'TEAM10', '2009-03-18'), (1054, 'DRAFT10', '2008-07-07'), (1054, 'TRNY20', '2009-05-09'), (1056, 'TRNY20', '2009-07-06'), (1063, 'LEAG10', '2008-01-02'), (1063, 'TEAM10', '2009-11-05'), (1065, 'LEAG10', '2008-01-21'), (1065, 'LEAGD10', '2008-07-04'), (1065, 'TEAM10', '2009-03-14'), (1066, 'LEAGD10', '2007-12-22'), (1066, 'TEAM10', '2008-10-01'), (1066, 'TRNY10', '2007-06-22'), (1067, 'LEAGD10', '2009-01-04'), (1068, 'DRAFT10', '2008-03-03'), (1070, 'DRAFT10', '2008-07-28'), (1070, 'LEAGD10', '2008-06-09'), (1070, 'TEAM10', '2008-07-29'), (1070, 'TRNY20', '2009-09-13'), (1071, 'TRNY10', '2007-10-15'), (1074, 'LEAG10', '2007-11-02'), (1080, 'DRAFT10', '2009-01-24'), (1080, 'LEAGD10', '2008-01-05'), (1080, 'TRNY10', '2009-05-29'), (1081, 'LEAGD10', '2008-02-09'), (1083, 'LEAG10', '2007-11-07'), (1083, 'LEAGD10', '2008-03-27'), (1083, 'TEAM10', '2009-05-26'), (1086, 'LEAG10', '2008-05-01'), (1089, 'LEAG10', '2009-10-12'), (1089, 'LEAGD10', '2008-10-10'), (1089, 'TRNY10', '2007-06-03'), (1094, 'TEAM10', '2010-01-08'), (1097, 'TRNY20', '2009-09-18'), (1098, 'LEAG10', '2007-12-03'), (1098, 'TRNY10', '2007-04-11'), (1100, 'LEAG10', '2007-08-07'), (1112, 'DRAFT10', '2008-09-27'), (1112, 'TRNY10', '2007-11-12'), (1112, 'TRNY20', '2009-12-13'), (1113, 'LEAGD10', '2008-02-18'), (1114, 'TRNY10', '2009-07-06'), (1116, 'DRAFT10', '2008-06-09'), (1117, 'DRAFT10', '2009-05-06'), (1117, 'TRNY10', '2007-03-04'), (1117, 'TRNY20', '2009-08-22'), (1118, 'DRAFT10', '2008-11-23'); CREATE TABLE incidents( incidentID int NOT NULL AUTO_INCREMENT, customerID int NOT NULL, productCode varchar(10) NOT NULL, techID int NULL, dateOpened datetime NOT NULL, dateClosed datetime NULL, title varchar(50) NOT NULL, description varchar(2000) NOT NULL, PRIMARY KEY (incidentID) ); INSERT INTO incidents VALUES (27, 1010, 'LEAG10', 11, '2010-06-05', '2010-06-06', 'Could not install', 'Media appears to be bad.'), (28, 1117, 'TRNY20', 11, '2010-06-14', NULL, 'Error importing data', 'Received error message 415 while trying to import data from previous version.'), (29, 1116, 'DRAFT10', 13, '2010-06-20', NULL, 'Could not install', 'Setup failed with code 104.'), (30, 1010, 'TEAM10', 14, '2010-06-21', '2010-06-24', 'Error launching program', 'Program fails with error code 510, unable to open database.'), (31, 1010, 'TRNY20', 14, '2010-06-21', NULL, 'Unable to activate product', 'Customer''s product activation key does not work.'), (32, 1056, 'TRNY20', 12, '2010-06-24', NULL, 'Product activation error', 'Customer could not activate product because of an invalid product activation code.'), (34, 1018, 'DRAFT10', 13, '2010-07-02', '2010-07-04', 'Error launching program', 'Program fails with error code 340: Database exceeds size limit.'), (36, 1065, 'LEAG10', NULL, '2010-07-04', NULL, 'Error adding data', 'Received error message 201 when trying to add records: database must be reorganized.'), (42, 1097, 'TRNY20', NULL, '2010-07-08', NULL, 'Unable to import data', 'Import command not available for importing data from previous version.'), (44, 1063, 'LEAG10', NULL, '2010-07-09', NULL, 'Installation error', 'Error during installation: cmd.exe not found.'), (45, 1089, 'LEAGD10', NULL, '2010-07-09', NULL, 'Problem upgrading from League Scheduler 1.0', 'Program fails with error 303 when trying to install upgrade.'), (46, 1016, 'TEAM10', NULL, '2010-07-09', NULL, 'Unable to restore data from backup', 'Error 405 encountered while restoring backup: File not found.'), (47, 1034, 'DRAFT10', NULL, '2010-07-09', NULL, 'Can''t activate product', 'Product activation code invalid.'), (48, 1049, 'TRNY20', NULL, '2010-07-09', NULL, 'Unable to print brackets', 'Program doesn''t recognize printer.'), (49, 1083, 'LEAGD10', NULL, '2010-07-10', NULL, 'Can''t start application', 'Error 521 on startup: database must be reorganized.'), (50, 1116, 'DRAFT10', NULL, '2010-07-10', NULL, 'Error during data file backup', 'Program abends with error 228 during database backup'), (51, 1067, 'LEAGD10', NULL, '2010-07-10', NULL, 'Error when adding new records', 'Received error 340: database exceeds size limit.'), (52, 1066, 'TEAM10', NULL, '2010-07-11', NULL, 'Installation problem', 'Customer states that the setup program failed with code 203 during configuration.'); CREATE TABLE countries ( countryCode char(2) NOT NULL, countryName varchar(20) NOT NULL, PRIMARY KEY (countryCode) ); INSERT INTO countries VALUES ('AF', 'Afghanistan'), ('AX', 'Aland Islands'), ('AL', 'Albania'), ('DZ', 'Algeria'), ('AS', 'American Samoa'), ('AD', 'Andorra'), ('AO', 'Angola'), ('AI', 'Anguilla'), ('AQ', 'Antarctica'), ('AG', 'Antigua and Barbuda'), ('AR', 'Argentina'), ('AM', 'Armenia'), ('AW', 'Aruba'), ('AU', 'Australia'), ('AT', 'Austria'), ('AZ', 'Azerbaijan'), ('BS', 'Bahamas, The'), ('BH', 'Bahrain'), ('BD', 'Bangladesh'), ('BB', 'Barbados'), ('BY', 'Belarus'), ('BE', 'Belgium'), ('BZ', 'Belize'), ('BJ', 'Benin'), ('BM', 'Bermuda'), ('BT', 'Bhutan'), ('BO', 'Bolivia'), ('BA', 'Bosnia and Herzegovina'), ('BW', 'Botswana'), ('BV', 'Bouvet Island'), ('BR', 'Brazil'), ('IO', 'British Indian Ocean Territory'), ('BN', 'Brunei Darussalam'), ('BG', 'Bulgaria'), ('BF', 'Burkina Faso'), ('BI', 'Burundi'), ('KH', 'Cambodia'), ('CM', 'Cameroon'), ('CA', 'Canada'), ('CV', 'Cape Verde'), ('KY', 'Cayman Islands'), ('CF', 'Central African Republic'), ('TD', 'Chad'), ('CL', 'Chile'), ('CN', 'China'), ('CX', 'Christmas Island'), ('CC', 'Cocos (Keeling) Islands'), ('CO', 'Colombia'), ('KM', 'Comoros'), ('CG', 'Congo'), ('CD', 'Congo, The Democratic Republic Of The'), ('CK', 'Cook Islands'), ('CR', 'Costa Rica'), ('CI', 'Cote D''ivoire'), ('HR', 'Croatia'), ('CY', 'Cyprus'), ('CZ', 'Czech Republic'), ('DK', 'Denmark'), ('DJ', 'Djibouti'), ('DM', 'Dominica'), ('DO', 'Dominican Republic'), ('EC', 'Ecuador'), ('EG', 'Egypt'), ('SV', 'El Salvador'), ('GQ', 'Equatorial Guinea'), ('ER', 'Eritrea'), ('EE', 'Estonia'), ('ET', 'Ethiopia'), ('FK', 'Falkland Islands - Malvinas'), ('FO', 'Faroe Islands'), ('FJ', 'Fiji'), ('FI', 'Finland'), ('FR', 'France'), ('GF', 'French Guiana'), ('PF', 'French Polynesia'), ('TF', 'French Southern Territories'), ('GA', 'Gabon'), ('GM', 'Gambia, The'), ('GE', 'Georgia'), ('DE', 'Germany'), ('GH', 'Ghana'), ('GI', 'Gibraltar'), ('GR', 'Greece'), ('GL', 'Greenland'), ('GD', 'Grenada'), ('GP', 'Guadeloupe'), ('GU', 'Guam'), ('GT', 'Guatemala'), ('GG', 'Guernsey'), ('GN', 'Guinea'), ('GW', 'Guinea-Bissau'), ('GY', 'Guyana'), ('HT', 'Haiti'), ('HM', 'Heard Island and the McDonald Islands'), ('VA', 'Holy See'), ('HN', 'Honduras'), ('HK', 'Hong Kong'), ('HU', 'Hungary'), ('IS', 'Iceland'), ('IN', 'India'), ('ID', 'Indonesia'), ('IQ', 'Iraq'), ('IE', 'Ireland'), ('IM', 'Isle Of Man'), ('IL', 'Israel'), ('IT', 'Italy'), ('JM', 'Jamaica'), ('JP', 'Japan'), ('JE', 'Jersey'), ('JO', 'Jordan'), ('KZ', 'Kazakhstan'), ('KE', 'Kenya'), ('KI', 'Kiribati'), ('KR', 'Korea, Republic Of'), ('KW', 'Kuwait'), ('KG', 'Kyrgyzstan'), ('LA', 'Lao People''s Democratic Republic'), ('LV', 'Latvia'), ('LB', 'Lebanon'), ('LS', 'Lesotho'), ('LR', 'Liberia'), ('LY', 'Libya'), ('LI', 'Liechtenstein'), ('LT', 'Lithuania'), ('LU', 'Luxembourg'), ('MO', 'Macao'), ('MK', 'Macedonia, The Former Yugoslav Republic Of'), ('MG', 'Madagascar'), ('MW', 'Malawi'), ('MY', 'Malaysia'), ('MV', 'Maldives'), ('ML', 'Mali'), ('MT', 'Malta'), ('MH', 'Marshall Islands'), ('MQ', 'Martinique'), ('MR', 'Mauritania'), ('MU', 'Mauritius'), ('YT', 'Mayotte'), ('MX', 'Mexico'), ('FM', 'Micronesia, Federated States Of'), ('MD', 'Moldova, Republic Of'), ('MC', 'Monaco'), ('MN', 'Mongolia'), ('ME', 'Montenegro'), ('MS', 'Montserrat'), ('MA', 'Morocco'), ('MZ', 'Mozambique'), ('MM', 'Myanmar'), ('NA', 'Namibia'), ('NR', 'Nauru'), ('NP', 'Nepal'), ('NL', 'Netherlands'), ('AN', 'Netherlands Antilles'), ('NC', 'New Caledonia'), ('NZ', 'New Zealand'), ('NI', 'Nicaragua'), ('NE', 'Niger'), ('NG', 'Nigeria'), ('NU', 'Niue'), ('NF', 'Norfolk Island'), ('MP', 'Northern Mariana Islands'), ('NO', 'Norway'), ('OM', 'Oman'), ('PK', 'Pakistan'), ('PW', 'Palau'), ('PS', 'Palestinian Territories'), ('PA', 'Panama'), ('PG', 'Papua New Guinea'), ('PY', 'Paraguay'), ('PE', 'Peru'), ('PH', 'Philippines'), ('PN', 'Pitcairn'), ('PL', 'Poland'), ('PT', 'Portugal'), ('PR', 'Puerto Rico'), ('QA', 'Qatar'), ('RE', 'Reunion'), ('RO', 'Romania'), ('RU', 'Russian Federation'), ('RW', 'Rwanda'), ('BL', 'Saint Barthelemy'), ('SH', 'Saint Helena'), ('KN', 'Saint Kitts and Nevis'), ('LC', 'Saint Lucia'), ('MF', 'Saint Martin'), ('PM', 'Saint Pierre and Miquelon'), ('VC', 'Saint Vincent and The Grenadines'), ('WS', 'Samoa'), ('SM', 'San Marino'), ('ST', 'Sao Tome and Principe'), ('SA', 'Saudi Arabia'), ('SN', 'Senegal'), ('RS', 'Serbia'), ('SC', 'Seychelles'), ('SL', 'Sierra Leone'), ('SG', 'Singapore'), ('SK', 'Slovakia'), ('SI', 'Slovenia'), ('SB', 'Solomon Islands'), ('SO', 'Somalia'), ('ZA', 'South Africa'), ('GS', 'South Georgia and the South Sandwich Islands'), ('ES', 'Spain'), ('LK', 'Sri Lanka'), ('SR', 'Suriname'), ('SJ', 'Svalbard and Jan Mayen'), ('SZ', 'Swaziland'), ('SE', 'Sweden'), ('CH', 'Switzerland'), ('TW', 'Taiwan'), ('TJ', 'Tajikistan'), ('TZ', 'Tanzania, United Republic Of'), ('TH', 'Thailand'), ('TL', 'Timor-leste'), ('TG', 'Togo'), ('TK', 'Tokelau'), ('TO', 'Tonga'), ('TT', 'Trinidad and Tobago'), ('TN', 'Tunisia'), ('TR', 'Turkey'), ('TM', 'Turkmenistan'), ('TC', 'Turks and Caicos Islands'), ('TV', 'Tuvalu'), ('UG', 'Uganda'), ('UA', 'Ukraine'), ('AE', 'United Arab Emirates'), ('GB', 'United Kingdom'), ('US', 'United States'), ('UM', 'United States Minor Outlying Islands'), ('UY', 'Uruguay'), ('UZ', 'Uzbekistan'), ('VU', 'Vanuatu'), ('VE', 'Venezuela'), ('VN', 'Vietnam'), ('VG', 'Virgin Islands, British'), ('VI', 'Virgin Islands, U.S.'), ('WF', 'Wallis and Futuna'), ('EH', 'Western Sahara'), ('YE', 'Yemen'), ('ZM', 'Zambia'), ('ZW', 'Zimbabwe'); CREATE TABLE administrators ( username VARCHAR(40) NOT NULL UNIQUE, password VARCHAR(40) NOT NULL, PRIMARY KEY (username) ); INSERT INTO administrators VALUES ('admin', 'sesame'), ('joel', 'sesame'); -- Create a user named ts_user GRANT SELECT, INSERT, UPDATE, DELETE ON * TO ts_user@localhost IDENTIFIED BY 'pa55word';