Chapter 10 - Transactions
Bank of the People
In Bitcoin We Trust
Transaction History
Delete | From Customer (Account/Balance) | To Customer (Account/Balance) | Transfer Amount | Date Submitted |
---|---|---|---|---|
Delete | Vowell, Sarah (Checking/$5294.24) | Vowell, Sarah (Checking/$5294.24) | 1000.00 | July 12th, 2019 (4:57 PM) |
Delete | Sedaris, David (Savings/$8095.97) | kelvin, jude (Savings/$5250.00) | 5000.00 | October 19th, 2015 (10:29 AM) |
Add Transaction
<?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&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&path=pursue&script=transactions&p='.($page-1).'&sort='.$sort.'&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&path=pursue&script=transactions&p='.$i.'&sort='.$sort.'&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&path=pursue&script=transactions&p='.($page+1).'&sort='.$sort.'&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'); ?>