<?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);

// 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>";
				} 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>';
			
			//disconnect from db
			// require(DISCONNECT);
			
			// provide feedback from submission
			// echo '<div id="content" class="message">'.$message.'</div>';

			// include footer
			// include (CHAPTER_PATH.'/'.$chapter.'/'.$path.'/includes/footer.php');
			
			// exit script - do not redisplay form
			// exit(); 

		} 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 
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 trans.date_entered DESC";
$r = mysqli_query($link, $q);
results_to_table($r);
?>
<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');
?>