<?php # Script banking accounts
// add a new account for an existing customer
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(transaction_id) FROM transactions";
$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 date submitted Desc.
$sort = (isset($_GET['sort'])) ? $_GET['sort'] : 'd';

// Determine the sorting order:
switch ($sort) {
	case 'cfrom':
		$order_by = 'cf.last_name';
		$sort = 'cfrom';
		$col = 'From Customer<br />(Account/Balance)';
		break;
	case 'cto':
		$order_by = 'ct.last_name';
		$sort = 'cto';
		$col = 'To Customer<br />(Account/Balance)';
		break;
	case 'a':
		$order_by = 'amount';
		$sort = 'a';
		$col = 'Transfer Amount';
		break;
	case 'd':
		$order_by = 'date_entered';
		$sort = 'd';
		$col = 'Date Submitted';
		break;
	default:
		$order_by = 'date_entered';
		$sort = 'd';
		break;
}
$sortCols = array(
	'cfrom' => 'From Customer<br />(Account/Balance)',
	'cto' => 'To Customer<br />(Account/Balance)',
	'a' => 'Transfer Amount',
	'd' => 'Date Submitted'
);
$dir = (isset($_GET['dir'])) ? $_GET['dir'] : 'DESC';
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
	// customer account FROM
	if (empty($_POST['customerFrom'])) {
		$errors['cFrom'] = 'Select a customer account to withdraw money from.';
	} else {	
		$string = explode(',', $_POST['customerFrom']); //Last Name, First Name (accountType/$balance)
		$lnF = mysqli_real_escape_string($link,trim($string[0]));
		$string = trim($string[1]);
		$fnF = mysqli_real_escape_string($link,trim(substr($string,0,strpos($string,"("))));
		$tF = (strpos($string, 'Checking'))?'Checking':'Savings'; 
		$bF= trim(substr($string,(strpos($string,"$") +1),(strpos($string,")")-strpos($string,"$")-1)));
	}
	// customer account TO
	if (empty($_POST['customerTo'])) {
		$errors['cTo'] = 'Select a customer account to deposit money to.';
	} else {	
		$string = explode(',', $_POST['customerTo']);
		$lnT = mysqli_real_escape_string($link,trim($string[0]));
		$string = trim($string[1]);
		$fnT = mysqli_real_escape_string($link,trim(substr($string,0,strpos($string,"(")))); 
		$tT = (strpos($string, 'Checking'))?'Checking':'Savings'; 
		$bT= trim(substr($string,(strpos($string,"$") +1),(strpos($string,")")-strpos($string,"$")-1))); 
	}

	// transfer amount (two decimal float!)
	if ($_POST['amount'] == '' ) {
		$errors['a'] = 'Enter a transfer amount.';
	} elseif(!is_numeric($_POST['amount'])) {
		$errors['a'] = 'Please enter a number.';
	} elseif ($_POST['amount'] > $bF) {
		$errors['a'] = 'You cannot transfer more than is available in the account.';
	} else {
		$a = mysqli_real_escape_string($link,trim($_POST['amount']));
	}

	if(empty($errors)) { // if no errors
		// get id of selected From Account
		$qFrom= "SELECT account_id FROM customers, accounts WHERE first_name='$fnF' AND last_name='$lnF' AND type='$tF' AND balance=$bF LIMIT 1";
		$qTo= "SELECT account_id FROM customers, accounts WHERE first_name='$fnT' AND last_name='$lnT' AND type='$tT' AND balance=$bT LIMIT 1";
		$rFrom= mysqli_query($link, $qFrom);
		$numFrom = mysqli_num_rows($rFrom);
		$rTo=mysqli_query($link,$qTo);
		$numTo = mysqli_num_rows($rTo);
		if($rFrom && $rTo){
			$rowFrom = mysqli_fetch_assoc($rFrom);
			$rowTo = mysqli_fetch_assoc($rTo);
			$account_idFrom = $rowFrom['account_id'];
			$account_idTo = $rowTo['account_id'];

			//BUILD QUERY
			// begin mysql transaction
			$transaction[] = "BEGIN";
			// update each account
			$transaction[] = "UPDATE accounts SET balance = balance - $a WHERE account_id=$account_idFrom";
			$transaction[] = "UPDATE accounts SET balance = balance + $a WHERE account_id=$account_idTo";

			// insert into transaction table
			$transaction[] = "INSERT INTO transactions (to_account_id,from_account_id,amount,date_entered) VALUES ($account_idTo,$account_idFrom,$a,NOW())";
	
			// commit transaction
			$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 == 3) {
					// data successfully inserted
					$message = "<h2>Transaction Completed!</h2><p>Your money has been transferred.</p>";
					$totalResults +=1;
				} else {
					// error - data not inserted
					$message = "<h2>System Error</h2><p class='error'>Your transaction could not be completed.<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[1]. Please try again later.</p>';	
			}
			
			// provide feedback from submission
			echo '<div id="content" class="message">'.$message.'</div>';

		} else {
			//did not retrieve two account ids
			$errors['flag'] = "<div class='error-message error'><h2>Error</h2><p>Transfer incomplete.[2].<br />Please double check your information and resubmit after correcting the highlighted errors.</p></div>";
		}	
	} else {
		$errors['flag'] = "<div class='error-message error'><h2>Error</h2><p>Transfer incomplete.[3].<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>Transaction History</h1>
<?php 
$q="SELECT 
transaction_id AS id,
CONCAT(cf.last_name,', ',cf.first_name,'<br />(',f.type,'/$',f.balance,')') AS 'From Customer<br />(Account/Balance)',
CONCAT(ct.last_name,', ',ct.first_name,'<br />(',t.type,'/$',t.balance,')') AS 'To Customer<br />(Account/Balance)',
trans.amount AS 'Transfer Amount', 
DATE_FORMAT(trans.date_entered,'%M %D, %Y (%l:%i %p)') AS 'Date Submitted'
FROM transactions AS trans 
INNER JOIN accounts AS t
ON trans.to_account_id = t.account_id
INNER JOIN accounts as f
ON trans.from_account_id = f.account_id 
INNER JOIN customers as ct
ON t.customer_id = ct.customer_id 
INNER JOIN customers as cf
ON f.customer_id = cf.customer_id
ORDER BY $order_by LIMIT $start, $display";
if($r = mysqli_query($link, $q) ){
	$num = mysqli_num_rows($r);
	if($num>0){
		// Print how many transactions there are:
		echo "<div class='message'>Viewing ";
		echo $start+1;
		echo "-";
		echo ($page!=$pages)? $start+$display : $totalResults;
		echo "  of <b>$totalResults</b> transactions. Sorted by $column.</div>\n";
		results_to_table_mod($r,'','id',false,'delete transaction','chapter=10&amp;path=pursue','transactions',$page,$sort,$sortCols,$dir);
	}
	// 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=transactions&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=transactions&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=transactions&amp;p='.($page+1).'&amp;sort='.$sort.'&amp;dir='.$dir.'">Next</a>';
		}
		echo '</p>';	
	} 
} ?>
<h1>Add Transaction</h1>
<?php echo (isset($errors['flag']))? $errors['flag'] : ''; ?>
<form action="" method="post">
	<p>
		<label for='cfrom'>Transfer from Customer Account</label>
		<?php 
		//get all customer accounts with details to populate dropdown menu
		$q= "SELECT CONCAT( c.last_name,', ', c.first_name, ' (',a.type, '/$', a.balance,')') AS 'account'
FROM customers AS c
INNER JOIN accounts AS a
USING (customer_id)";
		if($r = mysqli_query($link,$q) ){
			echo "<select id='cfrom' name='customerFrom'>";
			while($row = mysqli_fetch_assoc($r)) {
				echo "<option";
				echo (isset($_POST['customerFrom']) && $_POST['customerFrom']==$row['account'])?' selected':'';
				echo " value=\"".$row['account']."\">".$row['account']."</option>";
			}	
			echo "</select>";
		} else {
			echo "We are experiencing difficulties. Try back later.";
		}
		?>
		<?php echo (isset($errors['cFrom']))?'<span class="error">'.$errors['cFrom'].'</span>' : ''; ?>
	</p>
	<p>
		<label for='cto'>Transfer to Customer Account</label>
		<?php 
		//get all customer names to populate dropdown menu
		$q= "SELECT CONCAT( c.last_name,', ', c.first_name, ' (',a.type, '/$', a.balance,')') AS 'account'
FROM customers AS c
INNER JOIN accounts AS a
USING (customer_id)";
		if($r = mysqli_query($link,$q) ){
			echo "<select id='cto' name='customerTo'>";
			while($row = mysqli_fetch_assoc($r)) {
				echo "<option";
				echo (isset($_POST['customerTo']) && $_POST['customerTo']==$row['account'])?' selected':'';
				echo " value=\"".$row['account']."\">".$row['account']."</option>";
			}	
			echo "</select>";
		} else {
			echo "We are experiencing difficulties. Try back later.";
		}
		?>
		<?php echo (isset($errors['cTo']))?'<span class="error">'.$errors['cTo'].'</span>' : ''; ?>
	</p>
	<p>
		<label for='a'>Transfer Amount: </label>
		<input type="text" id='a' name="amount" size="10" maxlength="20" value="<?php if (isset($_POST['amount'])) echo $_POST['amount']; ?>"  />
		<?php echo (isset($errors['a']))?'<span class="error">'.$errors['a'].'</span>' : ''; ?>
	</p>
	<p>
		<input type="submit" name="submit" value="Submit Transaction" />
	</p>
</form>
<?php
//disconnect from db
require(DISCONNECT);
echo "</div>";
include (CHAPTER_PATH.'/'.$chapter.'/'.$path.'/includes/footer.php');
?>