Location: PHPKode > projects > Gsys > treasury/treasury_functions.php
<?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

Return current item: Gsys