<?PHP
//Filename : treasury_functions.php
//Description : functions for manipulating all treasury/financial data.
//Author : darc
//Last modified : 2006.12.20
// AUTHORIZATION
session_start();
if ($_SESSION[valid_login] != "true")
{
header("Location:../index.html");
exit;
}
if($_SESSION[auth] != "EC" && $_SESSION[auth] != "ADMIN")
{header("Location:brother_home.php"); exit;}
// END AUTHORIZATION
//Setup database connection
include '../includes/db.php';
switch($_GET["cat"])
{
//Display all transactions that have been made
case "transaction_list" :
{
if(!isset($_GET['page']))
{
$page = 1;
}
else
{
$page = $_GET['page'];
}
// Define the number of results per page
$max_results = 40;
// Figure out the limit for the query based
// on the current page number.
$from = (($page * $max_results) - $max_results);
$sql = "SELECT * FROM financial, brothers WHERE financial.pin_num = brothers.pin_num ORDER BY date_time DESC LIMIT $from, $max_results";
$result = mysql_query($sql,$connection) or die(mysql_error());
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM financial"),0);
// Figure out the total number of pages. Always round up using ceil()
$total_pages = ceil($total_results / $max_results);
echo "
<html>
<head>
<title>Transaction List</title>
<link href=\"../includes/index.css\" rel=\"stylesheet\" type=\"text/css\">
</head>
<body class=\"body\">
<center><h2>Transaction List || Generated ";
echo date("F j, Y, g:i a");
echo "
</h2></center>
<br><br>
<p><a href=\"index.php\">Return to Treasury main</a></p>
<br><br>";
// -----------------Page Number Output------------------- //
echo "Select a Page<br />";
// Build Previous Link
if($page > 1){
$prev = ($page - 1);
echo "<a href=\"".$_SERVER['PHP_SELF']."?cat=transaction_list&page=$prev\"><<Previous</a> ";
}
for($i = 1; $i <= $total_pages; $i++){
if(($page) == $i){
echo "$i ";
} else {
echo "<a href=\"".$_SERVER['PHP_SELF']."?cat=transaction_list&page=$i\">$i</a> ";
}
}
// Build Next Link
if($page < $total_pages){
$next = ($page + 1);
echo "<a href=\"".$_SERVER['PHP_SELF']."?cat=transaction_list&page=$next\">Next>></a>";
}
// -----------------Page Number Output------------------- //
echo "<table>
<tr>
<td width=\"10%\" align=\"center\"><strong>Transaction Number</strong></td>
<td width=\"15%\" align=\"center\"><strong>Date Posted</strong></td>
<td><strong>Posted By</strong></td>
<td><strong>Last Name</strong></td>
<td><strong>First Name</strong></td>
<td><strong>Amount</strong></td>
<td><strong>Edit</strong></td>
<td><strong>Delete</strong></td>
</tr>";
//START LOOP
while ($row =mysql_fetch_array($result))
{
$l_name = $row['l_name'];
$f_name = $row['f_name'];
$transaction_num = $row['transaction_num'];
$date_time = $row['date_time'];
$amount = $row['amount'];
$amount = number_format($amount, 2);
$posted_by = $row['added_by'];
echo "<tr><td align=\"center\">";
echo($transaction_num);
echo "</td><td align=\"center\">";
echo($date_time);
echo "</td><td>";
echo($posted_by);
echo "</td><td>";
echo($l_name);
echo "</td><td>";
echo($f_name);
echo "</td><td>$";
echo($amount);
echo "</td><td>";
?>
<form method="post" action="edit_transaction.php">
<input type="hidden" name="trans_num" value="<?php echo $transaction_num; ?>">
<INPUT TYPE="SUBMIT" NAME="submit" VALUE="Edit">
</form>
</td><td>
<form method="post" action="treasury_functions.php?cat=delete_charge">
<input type="hidden" name="trans_num" value="<?php echo $transaction_num; ?>">
<INPUT TYPE="SUBMIT" NAME="submit" VALUE="Delete">
</form>
<?php
echo "</td></tr>";
}
//CLOSE LOOP
echo "
</table>
<p><a href=\"index.php\">Return to Treasury main</a></p>
</body>
</html>";
break;
}
case "add_wide" :
{
//Setup query to add a charge for all members
$amount = - $_POST['amount'];
$description = $_POST['description'];
$added_by = $_POST['added_by'];
$pin_sql = "SELECT pin_num, status, l_name FROM brothers WHERE status = \"active\" ORDER BY l_name;";
$pin_result = mysql_query($pin_sql,$connection) or die(mysql_error());
echo "Total rows : " .mysql_num_rows($pin_result). "<br >";
// LOOP, INSERTING TRANSACTIONS and UPDATING BALANCES
while ($row =mysql_fetch_array($pin_result))
{
$pin_tmp = $row['pin_num'];
$sql = "INSERT INTO financial (transaction_num, pin_num, amount, date_time, description, added_by) VALUES ('', '$pin_tmp', '$amount', NOW(), '$description', '$added_by');";
$result = mysql_query($sql,$connection) or die(mysql_error());
echo "<html>
<head>
<title>Add Organization Wide Charge</title>
<link href=\"../includes/index.css\" rel=\"stylesheet\" type=\"text/css\">
</head>
<body class=\"body\">
<br />
<table width=\"810\" height=\"626\" border=\"0\" align=\"center\" cellpadding=\"15\">
<tr>
<td height=\"128\" colspan=\"2\"><img src=\"../images/eg_banner.gif\" width=\"810\" height=\"172\"></td>
</tr>
<tr>
<td width=\"27%\" height=\"488\" valign=\"top\">"; include '../includes/nav.inc'; echo "</td>
<td width=\"53%\">Transaction completed successfully!<br><br>All active members were charged $amount </td>
</tr>
</table>";
}
// CLOSE LOOP
break;
}
case "add_pledge_wide" :
{
//Setup query to add a charge for all members
$amount = - $_POST['amount'];
$description = $_POST['description'];
$added_by = $_POST['added_by'];
$pin_sql = "SELECT pin_num, status, l_name FROM brothers WHERE status = \"pledge\" ORDER BY l_name;";
$pin_result = mysql_query($pin_sql,$connection) or die(mysql_error());
echo "Total rows : " .mysql_num_rows($pin_result). "<br >";
// LOOP, INSERTING TRANSACTIONS and UPDATING BALANCES
while ($row =mysql_fetch_array($pin_result))
{
$pin_tmp = $row['pin_num'];
$sql = "INSERT INTO financial (transaction_num, pin_num, amount, date_time, description, added_by) VALUES ('', '$pin_tmp', '$amount', NOW(), '$description', '$added_by');";
$result = mysql_query($sql,$connection) or die(mysql_error());
echo "<html>
<head>
<title>Add Pledge Wide Charge</title>
<link href=\"../includes/index.css\" rel=\"stylesheet\" type=\"text/css\">
</head>
<body class=\"body\">
<br />
<table width=\"810\" height=\"626\" border=\"0\" align=\"center\" cellpadding=\"15\">
<tr>
<td height=\"128\" colspan=\"2\"><img src=\"../images/eg_banner.gif\" width=\"810\" height=\"172\"></td>
</tr>
<tr>
<td width=\"27%\" height=\"488\" valign=\"top\">"; include '../includes/nav.inc'; echo "</td>
<td width=\"53%\">Transaction completed successfully!<br><br>All active members were charged $amount </td>
</tr>
</table>";
}
// CLOSE LOOP
break;
}
case "add_rent" :
{
//Setup query to add a charge for all members living in the house
$amount = - $_POST['amount'];
$description = $_POST['description'];
$added_by = $_POST['added_by'];
$pin_sql = "SELECT pin_num, status, l_name FROM brothers WHERE house = \"house\" ORDER BY l_name;";
$pin_result = mysql_query($pin_sql,$connection) or die(mysql_error());
echo "Total rows : " .mysql_num_rows($pin_result). "<br >";
// LOOP, INSERTING TRANSACTIONS and UPDATING BALANCES
while ($row =mysql_fetch_array($pin_result))
{
$pin_tmp = $row['pin_num'];
$sql = "INSERT INTO financial (transaction_num, pin_num, amount, date_time, description, added_by) VALUES ('', '$pin_tmp', '$amount', NOW(), '$description', '$added_by');";
$result = mysql_query($sql,$connection) or die(mysql_error());
echo "<html>
<head>
<title>Add House Rent Charge</title>
<link href=\"../includes/index.css\" rel=\"stylesheet\" type=\"text/css\">
</head>
<body class=\"body\">
<br />
<table width=\"810\" height=\"626\" border=\"0\" align=\"center\" cellpadding=\"15\">
<tr>
<td height=\"128\" colspan=\"2\"><img src=\"../images/eg_banner.gif\" width=\"810\" height=\"172\"></td>
</tr>
<tr>
<td width=\"27%\" height=\"488\" valign=\"top\">"; include '../includes/nav.inc'; echo "</td>
<td width=\"53%\">Transaction completed successfully!<br><br>All members living in the house were charged $amount</td>
</tr>
</table>";
}
// CLOSE LOOP
break;
}
case "add_specific" :
{
$pin_tmp = "";
$pin_tmp = $_POST['member_name'];
$amount = - $_POST['amount'];
$added_by = $_POST['added_by'];
$description = $_POST['description'];
$sql = "INSERT INTO financial (transaction_num, pin_num, amount, date_time, description, added_by) VALUES ('', '$pin_tmp', '$amount', NOW(), '$description', '$added_by');";
$result = mysql_query($sql,$connection) or die(mysql_error());
echo "<html>
<head>
<title>Add Specific Charge</title>
<link href=\"../includes/index.css\" rel=\"stylesheet\" type=\"text/css\">
</head>
<body class=\"body\">
<br />
<table width=\"810\" height=\"626\" border=\"0\" align=\"center\" cellpadding=\"15\">
<tr>
<td height=\"128\" colspan=\"2\"><img src=\"../images/eg_banner.gif\" width=\"810\" height=\"172\"></td>
</tr>
<tr>
<td width=\"27%\" height=\"488\" valign=\"top\">"; include '../includes/nav.inc'; echo "</td>
<td width=\"53%\">Transaction completed successfully!<br><br>User with pin number $pin_tmp was charged: $amount</td>
</tr>
</table>";
break;
}
case "add_payment" :
{
$pin_tmp = "";
$pin_tmp = $_POST['member_name'];
$amount = $_POST['amount'];
$added_by = $_POST['added_by'];
$description = $_POST['description'];
$sql = "INSERT INTO financial (transaction_num, pin_num, amount, date_time, description, added_by) VALUES ('', '$pin_tmp', '$amount', NOW(), '$description', '$added_by');";
$result = mysql_query($sql,$connection) or die(mysql_error());
echo "<html>
<head>
<title>Add Payment</title>
<link href=\"../includes/index.css\" rel=\"stylesheet\" type=\"text/css\">
</head>
<body class=\"body\">
<br />
<table width=\"810\" height=\"626\" border=\"0\" align=\"center\" cellpadding=\"15\">
<tr>
<td height=\"128\" colspan=\"2\"><img src=\"../images/eg_banner.gif\" width=\"810\" height=\"172\"></td>
</tr>
<tr>
<td width=\"27%\" height=\"488\" valign=\"top\">"; include '../includes/nav.inc'; echo "</td>
<td width=\"53%\">Transaction completed successfully!<br><br>User with pin number $pin_tmp was credited: $amount</td>
</tr>
</table>";
break;
}
case "balance_list" :
{
//Setup query to select all members
$sql = "SELECT pin_num, status, l_name, f_name FROM brothers WHERE status = \"active\" && l_name != \"\" || status = \"inactive\" && l_name != \"\" ORDER BY l_name";
$result = mysql_query($sql,$connection) or die(mysql_error());
$total_debt = 0;
echo "
<Html>
<head><title>Balance List</title><link href=\"../includes/index.css\" rel=\"stylesheet\" type=\"text/css\">
</head>
<body class=\"body\">
<center><h2>Balance List || Generated ";
echo date("F j, Y, g:i a");
echo "
</h2></center>
<br><br>
<p><a href=\"index.php\">Return to Treasury main</a></p>
<br><br>
<table>
<tr>
<td><strong>Last Name</strong></td>
<td><strong>First Name</strong></td>
<td><strong>Current Balance</strong></td>
</tr>";
//START LOOP
while ($row =mysql_fetch_array($result))
{
$l_name = $row['l_name'];
$f_name = $row['f_name'];
$pin_tmp = $row['pin_num'];
$sql_bal = "SELECT pin_num, date_time, SUM(amount) AS balance FROM financial WHERE pin_num = '$pin_tmp' GROUP BY pin_num";
$sql_bal_result = mysql_query($sql_bal,$connection) or die(mysql_error());
$bal_row = mysql_fetch_array($sql_bal_result);
$balance = number_format($bal_row['balance'], 2);
if($balance < 0)
$total_debt = $total_debt + $balance;
echo "<tr><td>";
echo($l_name);
echo "</td><td>";
echo($f_name);
echo "</td><td>$ ";
if($balance < 0)
echo "<font color=\"red\">$balance</font>";
else
echo "$balance";
echo "</td></tr>";
}
//CLOSE LOOP
echo "
</table>
<br />
Total debt: $ <font color=\"red\">$total_debt</font><br />
<p><a href=\"index.php\">Return to Treasury main</a></p>
</body>
</html>";
break;
}
case "pledge_balance_list" :
{
//Setup query to select all members
$sql = "SELECT pin_num, status, l_name, f_name FROM brothers WHERE status = \"pledge\" && l_name != \"\" ORDER BY l_name";
$result = mysql_query($sql,$connection) or die(mysql_error());
$total_debt = 0;
echo "
<Html>
<head><title>Pledge Balance List</title><link href=\"../includes/index.css\" rel=\"stylesheet\" type=\"text/css\">
</head>
<body class=\"body\">
<center><h2>Balance List || Generated ";
echo date("F j, Y, g:i a");
echo "
</h2></center>
<br><br>
<p><a href=\"index.php\">Return to Treasury main</a></p>
<br><br>
<table>
<tr>
<td><strong>Last Name</strong></td>
<td><strong>First Name</strong></td>
<td><strong>Current Balance</strong></td>
</tr>";
//START LOOP
while ($row =mysql_fetch_array($result))
{
$l_name = $row['l_name'];
$f_name = $row['f_name'];
$pin_tmp = $row['pin_num'];
$sql_bal = "SELECT pin_num, date_time, SUM(amount) AS balance FROM financial WHERE pin_num = '$pin_tmp' GROUP BY pin_num";
$sql_bal_result = mysql_query($sql_bal,$connection) or die(mysql_error());
$bal_row = mysql_fetch_array($sql_bal_result);
$balance = number_format($bal_row['balance'], 2);
if($balance < 0)
$total_debt = $total_debt + $balance;
echo "<tr><td>";
echo($l_name);
echo "</td><td>";
echo($f_name);
echo "</td><td>$ ";
if($balance < 0)
echo "<font color=\"red\">$balance</font>";
else
echo "$balance";
echo "</td></tr>";
}
//CLOSE LOOP
echo "
</table>
<br />
Total debt: $ <font color=\"red\">$total_debt</font><br />
<p><a href=\"index.php\">Return to Treasury main</a></p>
</body>
</html>";
break;
}
case "delete_charge" :
{
$trans_num = $_POST['trans_num'];
$sql = "DELETE FROM financial WHERE transaction_num = '$trans_num'";
$result = mysql_query($sql,$connection) or die(mysql_error());
echo "<html>
<head>
<title>Delete Record</title>
<link href=\"../includes/index.css\" rel=\"stylesheet\" type=\"text/css\">
</head>
<body class=\"body\">
<br />
<table width=\"810\" height=\"626\" border=\"0\" align=\"center\" cellpadding=\"15\">
<tr>
<td height=\"128\" colspan=\"2\"><img src=\"../images/eg_banner.gif\" width=\"810\" height=\"172\"></td>
</tr>
<tr>
<td width=\"27%\" height=\"488\" valign=\"top\">"; include '../includes/nav.inc'; echo "</td>
<td width=\"53%\"><center>Record was successfully deleted!</center></td>
</tr>
</table>";
return;
}
case "update_transaction" :
{
$trans_num = $_POST['trans_num'];
$amount = $_POST['amount'];
$added_by = $_POST['added_by'];
$description = $_POST['description'];
$sql = "UPDATE financial SET amount = '$amount', added_by = '$added_by', description = '$description' WHERE transaction_num = '$trans_num' LIMIT 1";
$result = mysql_query($sql,$connection) or die(mysql_error());
}
case "user_transaction_list" :
{
$pin_tmp = $_POST['member_pin'];
//SQL query to get all the user's transactions, sort descending
$sql = "SELECT amount, transaction_num, date_time, added_by, description FROM financial INNER JOIN brothers ON financial.pin_num = brothers.pin_num WHERE financial.pin_num = '$pin_tmp' GROUP BY amount, transaction_num, date_time, added_by, description ORDER BY date_time DESC";
$result = mysql_query($sql,$connection) or die(mysql_error());
//SQL query to get the current balance of the user ~ IE: SUM(amount)
$sql_balance = "SELECT SUM(amount) AS sum_amount FROM financial INNER JOIN brothers ON financial.pin_num = brothers.pin_num WHERE financial.pin_num = '$pin_tmp' ORDER BY date_time";
$sql_balance_result = mysql_query($sql_balance,$connection) or die(mysql_error());
//Set the current balance
$row_bal = mysql_fetch_array($sql_balance_result);
$balance = $row_bal[sum_amount];
echo "
<Html>
<head><title>User Transaction List</title></head>
<body>
<center><h2>User Transaction List || Generated ";
echo date("F j, Y, g:i a");
echo "
<html>
<head>
<title>Transaction List</title>
<link href=\"../includes/index.css\" rel=\"stylesheet\" type=\"text/css\">
</head>
<body class=\"body\">
</h2></center>
<br><br>
<p><a href=\"index.php\">Return to Treasury main</a></p>
<br><br>
<table>
<tr>
<td width=\"10%\" align=\"center\"><strong>Transaction Number</strong></td>
<td width=\"15%\" align=\"center\"><strong>Date Posted</strong></td>
<td><strong>Posted By</strong></td>
<td><strong>Amount</strong></td>
<td><strong>Balance</strong></td>
<td><strong>Description</strong></td>
<td><strong>Edit</strong></td>
<td><strong>Delete</strong></td>
</tr>";
//START LOOP
while ($row = mysql_fetch_array($result))
{
$balance = number_format($balance, 2);
$transaction_num = $row['transaction_num'];
$date_time = $row['date_time'];
$amount = $row['amount'];
$amount = number_format($amount, 2);
$posted_by = $row['added_by'];
$description = $row['description'];
echo "<tr><td align=\"center\">";
echo($transaction_num);
echo "</td><td align=\"center\">";
echo($date_time);
echo "</td><td>";
echo($posted_by);
echo "</td><td>$";
echo($amount);
echo "</td><td>$";
echo($balance);
echo "</td><td>";
echo($description);
echo "</td><td>";
?>
<form method="post" action="edit_transaction.php">
<input type="hidden" name="trans_num" value="<?php echo $transaction_num; ?>">
<INPUT TYPE="SUBMIT" NAME="submit" VALUE="Edit">
</form>
</td><td>
<form method="post" action="treasury_functions.php?cat=delete_charge">
<input type="hidden" name="trans_num" value="<?php echo $transaction_num; ?>">
<INPUT TYPE="SUBMIT" NAME="submit" VALUE="Delete">
</form>
<?php
echo "</td></tr>";
//Update balance for next iteration (NOTE: Do this *AFTER* displaying current balance)
$balance -= $row['amount'];
}
//CLOSE LOOP
echo "
</table>
<p><a href=\"index.php\">Return to Treasury main</a></p>
</body>
</html>";
break;
}
}
unset($pin_temp);
?>
<br /><br />
add charge
add credit
display current balances
display specific user's current balance
show detailed description if a transaction is clicked