<?php # Script banking customers
// This script retrieves all the records from the customers table.
include (CHAPTER_PATH.'/'.$chapter.'/'.$path.'/includes/header.php');

// connect to db
require(CONNECT);

// START PAGINATION CONFIG
//allow user to set results per page - post(from form submission) overrides get(in pagination links)
// if(isset($_POST['d']) && is_numeric($_POST['d']) && $_POST['d'] > 0 ) {
// 	$display = $_POST['d'];
// } elseif(isset($_GET['d']) && is_numeric($_GET['d']) && $_GET['d'] > 0 ) {
// 	$display = $_GET['d'];
// }else {
	$display = 10;
// }

//determine which page we are on using url parameters
if (isset($_GET['p']) && is_numeric($_GET['p'])) { 
	$page = $_GET['p'];
} else {
	$page = 1;
}
// Determine how many total pages in result set
$q = "SELECT COUNT(customer_id) FROM customers";
$r = @mysqli_query ($link, $q);
$row = @mysqli_fetch_array ($r, MYSQLI_NUM);
$totalResults = $row[0];
// Calculate the number of pages...
if ($totalResults > $display) { // More than 1 page.
	$pages = ceil ($totalResults/$display);
} else {
	$pages = 1;
}
//reset $page if user is naughty and overwrites $_GET variable
if($page>$pages) $page=$pages;
if($page<1) $page=1;

// set the starting point in the database to return results...
$start = (($page * $display) - $display);
// END PAGINATION Config

// START SORTING functionality
// Default is by last name ASC.
$sort = (isset($_GET['sort'])) ? $_GET['sort'] : 'ln';

// Determine the sorting order:
switch ($sort) {
	case 'ln':
		$order_by = 'last_name';
		$sort = 'ln';
		$col = 'Last Name';
		break;
	case 'fn':
		$order_by = 'first_name';
		$sort = 'fn';
		$col = 'First Name';
		break;
	default:
		$order_by = 'last_name';
		$sort = 'ln';
		break;
}
$sortCols = array(
	'ln' => 'Last Name',
	'fn' => 'First Name'	
);
$dir = (isset($_GET['dir'])) ? $_GET['dir'] : 'ASC';
switch ($dir) { // allow only two values
	case 'DESC':
		$dir='DESC';
		break;
	default:
		$dir = 'ASC';
		break;
}
//format string for display
$column = ucwords(str_replace('_', ' ', $order_by));
$column .= ($dir=='ASC')?' Ascending':' Descending';
//format string for sql orderby clause
$order_by .= ' '.$dir;
// END SORTING 

// check if form has been submitted
if($_SERVER['REQUEST_METHOD'] == 'POST') {

	// create error array 
	$errors = array();

	// check each field for validity, assign error message if fails
	// first_name
	if (empty($_POST['first_name'])) {
		$errors['fn'] = 'Enter customer\'s first name.';
	} else {
		$fn = mysqli_real_escape_string($link,trim($_POST['first_name']));
	}

	// last_name
	if (empty($_POST['last_name'])) {
		$errors['ln'] = 'Enter customer\'s last name.';
	} else {
		$ln = mysqli_real_escape_string($link,trim($_POST['last_name']));
	}

	// prevent customer with duplicate first & last names
	if( isset($fn) && isset($ln) ) {
		$q = "SELECT customer_id FROM customers WHERE first_name='$fn' AND last_name='$ln'";
		if ($r = @mysqli_query($link, $q)) {
			if(mysqli_num_rows($r) != 0) {
				$errors['ln'] = 'Sorry, a customer with that first and last name already exists. Please enter a different name.';
			}
			// free result set 
			mysqli_free_result($r);
		}	
	}

	if(empty($errors)) { // if no errors
		// build transaction query
		$transaction = array(
			"START TRANSACTION",
			"INSERT INTO customers (first_name,last_name) VALUES ('$fn','$ln')"
		);
	
		$transaction[] ="COMMIT";

		// submit data
		$count = 0;
		foreach ($transaction as $q) {
			$r = mysqli_query($link,$q);
			$count += mysqli_affected_rows($link);
		}
		if($count>0) {
			if($count == 1) {
				// data successfully inserted
				$message = "<h2>Thank you!</h2><p>Your customer profile has been completed!<br />Would you like to <a href='index.php?path=pursue&amp;chapter=9&amp;script=accounts'>add an account</a> for this customer?</p>";
			} else {
				// error - data not inserted
				$message = "<h2>System Error</h2><p class='error'>Your information could not be added to our database.<br />We apologize for any inconvenience, please <a href='javascript:history.back()'>try again</a>.</p>";
				$message .= '<p><span class="content-caption">Debugging information</span>Error message: <br />'.mysqli_error($link).'<br /><br />Query: <br />'. $q .'</p>';
			}
		} else {
			//query unsuccessful
			$message = '<h2>Error</h2><p class="error-message error">There was an error accessing the database. Please try again later.</p>';	
		}
		
		// provide feedback from submission
		echo '<div id="content" class="message">'.$message.'</div>';

	} else {
		$errors['flag'] = "<div class='error-message error'><h2>Error</h2><p>Customer creation is not complete.<br />Please double check your information and resubmit after correcting the highlighted errors.</p></div>";
	}
}
// begin form output. if submitted with errors - include original submission values with error messages
echo "<div id='content'>";
?>
<h1>Current Customers</h1>
<?php 
//get all current customers
$q= "SELECT customer_id AS id, last_name AS 'Last Name', first_name AS 'First Name' FROM customers ORDER BY $order_by LIMIT $start, $display";
if($r = mysqli_query($link, $q)) {
	$num = mysqli_num_rows($r);
	if($num>0){
		// Print how many customers there are:
		echo "<div class='message'>Viewing ";
		echo $start+1;
		echo "-";
		echo ($page!=$pages)? $start+$display : $totalResults;
		echo "  of <b>$totalResults</b> customers. Sorted by $column.</div>\n";
		results_to_table_mod($r,'','id','edit customer','delete customer','chapter=10&amp;path=pursue','customers',$page,$sort,$sortCols,$dir);
	}  else {
		// no results
		echo '<p class="error-message error">There are currently no customers.</p>';
	}
	// Build navigation through paginated results
	if ($pages > 1) {
		echo '<p class="pagination">';
		// If it's not the first page, make a Previous button:
		if ($page != 1) {
			echo '<a href="index.php?chapter=10&amp;path=pursue&amp;script=customers&amp;p='.($page-1).'&amp;sort='.$sort.'&amp;dir='.$dir.'">Previous</a> ';
		}
		// Make all the numbered pages:
		for ($i = 1; $i <= $pages; $i++) {
			if ($i != $page) {
				echo '<a href="index.php?chapter=10&amp;path=pursue&amp;script=customers&amp;p='.$i.'&amp;sort='.$sort.'&amp;dir='.$dir.'">' . $i . '</a> ';
			} else {
				echo '<b>'.$i.'</b> ';
			}
		} 
		// If it's not the last page, make a Next button:
		if ($page != $pages) {
			echo '<a href="index.php?chapter=10&amp;path=pursue&amp;script=customers&amp;p='.($page+1).'&amp;sort='.$sort.'&amp;dir='.$dir.'">Next</a>';
		}
		echo '</p>';	
	} 
} 
?>
<h1>Add New Customer</h1>
<?php echo (isset($errors['flag']))? $errors['flag'] : ''; ?>
<form action="" method="post">
	<p>
		<label for='fn'>First Name: </label>
		<input type="text" id='fn' name="first_name" size="15" maxlength="20" value="<?php if (isset($_POST['first_name'])) echo $_POST['first_name']; ?>" />
		<?php echo (isset($errors['fn']))?'<span class="error">'.$errors['fn'].'</span>' : ''; ?>
	</p>
	<p>
		<label for='ln'>Last Name: </label>
		<input type="text" id='ln' name="last_name" size="15" maxlength="40" value="<?php if (isset($_POST['last_name'])) echo $_POST['last_name']; ?>" />
		<?php echo (isset($errors['ln']))?'<span class="error">'.$errors['ln'].'</span>' : ''; ?>
	</p>
	<p>
		<input type="submit" name="submit" value="Add Customer" />
	</p>
</form>
<?php
echo "</div>";

//disconnect from db
require(DISCONNECT);

include (CHAPTER_PATH.'/'.$chapter.'/'.$path.'/includes/footer.php');
?>