HotLunches/user_account.report.php

<?php
require_once("include/globals.php");

if (SessionActive() != 1 || ! UserLoggedIn()) {
   header("Location: /");
   exit();
}


//
// Process all actions to list and edit records
//
$action       = "user-account-report";
$CurrentTable = "user_account";
$FormFile     = "";
$TitleText    = getLanguageText('TEXT-ACCOUNT-TRANSACTION-REPORT');
$OrderBy      = "transaction_datetime";

if (array_key_exists('type',$_REQUEST) && strlen($_REQUEST['type']) > 0 && $_REQUEST['type'] == 'deleted') {
   $TitleText = getLanguageText('TEXT-DELETED-ACCOUNT-TRANSACTION-REPORT');
}


// Start the template and output buffering
startTemplate();
$templatePageTitle = $TitleText;



// Ensure that only valid user types have access to this module
if (!in_array($_SESSION['UserInfo']['type'],array('SuperUser','System Administrator','Application Administrator','Administration Team','Schedule Team','Financial','Financial Team'))) {
   $error_message = getLanguageText('ERRORMSG-ACCESS-DENIED');
   header("Location: /?error_message=".urlencode($error_message));
   exit();
}


$school_num = $school_record['record_num'];

$print_cmd = "";

// Handle selected date ranges
$start_date = date("Y-m-d 00:00:00",time());
$end_date   = date("Y-m-d 23:59:59",time());

if (array_key_exists('start_date',$_REQUEST) && strlen($_REQUEST['start_date']) > 0) $start_date = date("Y-m-d 00:00:00",strtotime($_REQUEST['start_date']));
if (array_key_exists('end_date',$_REQUEST) && strlen($_REQUEST['end_date']) > 0) {
   $end_date = $_REQUEST['end_date'];
   if ($end_date < substr($start_date,0,10)) {
      $end_date = substr($start_date,0,10);
      $error_message = getLanguageText('ERRORMSG-INVALID-DATE-RANGE');
   }

   $end_date = date("Y-m-d 23:59:59",strtotime($end_date));
}

$sdate = substr($start_date,0,10);
$edate = substr($end_date,0,10);

$twhere = "AND transaction_datetime BETWEEN '$start_date' AND '$end_date' ";
$print_cmd .= "&start_date=$sdate&end_date=$edate";


// View only a selected userid
$uwhere = "";
unset($selected_usernum);
if (array_key_exists('selected_usernum',$_REQUEST) && strlen($_REQUEST['selected_usernum']) > 0) {
   $selected_usernum = intval($_REQUEST['selected_usernum']);
   $userid = db_getVal("SELECT userid FROM users WHERE record_num = $selected_usernum");
   $uwhere = "AND ua.user_num = $selected_usernum ";
   $print_cmd .= "&selected_usernum=$selected_usernum";
}


// View only selected transaction types
unset($selected_transaction_type);
if (array_key_exists('selected_transaction_type',$_REQUEST) && strlen($_REQUEST['selected_transaction_type']) > 0) {
   $selected_transaction_type = db_escape_string($_REQUEST['selected_transaction_type']);
   $uwhere .= "AND ua.transaction_type = '$selected_transaction_type' ";
   $print_cmd .= "&selected_transaction_type=$selected_transaction_type";
}



// View only selected payment methods
unset($selected_payment_method);
if (array_key_exists('selected_payment_method',$_REQUEST) && strlen($_REQUEST['selected_payment_method']) > 0) {
   $selected_payment_method = db_escape_string($_REQUEST['selected_payment_method']);
   $uwhere .= "AND ua.payment_method = '$selected_payment_method' ";
   $print_cmd .= "&selected_payment_method=$selected_payment_method";
}


// View only selected schedule
$swhere = "";
$selected_schedule_num = 0;
if (array_key_exists('selected_schedule_num',$_REQUEST) && strlen($_REQUEST['selected_schedule_num']) > 0) {
   $selected_schedule_num = db_escape_string($_REQUEST['selected_schedule_num']);
   $uwhere .= "AND ua.schedule_num = '$selected_schedule_num' ";
   $print_cmd .= "&selected_schedule_num=$selected_schedule_num";
}

if (!isset($selected_transaction_type)) $selected_transaction_type = '';
if (!isset($selected_payment_method)) $selected_payment_method = '';
if (!isset($selected_schedule_num)) $selected_schedule_num = 0;


// Process an optional type parameter
unset($deleteType);
if (array_key_exists('type',$_REQUEST) && strlen($_REQUEST['type']) > 0 && $_REQUEST['type'] == 'deleted') {
   $deleteType = db_escape_string($_REQUEST['type']);
   $uwhere .= "AND deleted = 'Y' ";
   $print_cmd .= "&type=$deleteType";
}
else {
   $deleteType = "active";
   $uwhere .= "AND (deleted != 'Y' OR deleted IS NULL) ";
}


// Set query sort order
$order_by = "ORDER BY transaction_datetime,record_num";
$sort = "Date";
if (array_key_exists('sort',$_REQUEST) && strlen($_REQUEST['sort'])) {
   $sort = $_REQUEST['sort'];
   if ($sort == 'Name') $order_by = "ORDER BY last_name,first_name,transaction_datetime,record_num";
   if ($sort == 'Payment Date') $order_by = "ORDER BY payment_time,record_num";
}
$print_cmd .= "&sort=" . urlencode($sort);


// Set trx view
$vwhere = "";
$view_trx = 'All';
if (array_key_exists('view_trx',$_REQUEST) && strlen($_REQUEST['view_trx'])) {
   $view_trx = $_REQUEST['view_trx'];
   if ($view_trx == 'Open') $vwhere = "AND transaction_status = 'Open' ";
   if ($view_trx == 'Closed') $vwhere = "AND transaction_status = 'Closed' ";
}
$print_cmd .= "&view_trx=" . urlencode($view_trx);




$message_width = 600;
include "form.messages.php";

?>
    <form method='post' name='transactions'>
     <table width='750' cellpadding='3' class='content-table'>
      <tr>
      <td align='center'>
      <table>
      <tr>
       <td align='left'><?php displayLanguageText('TEXT-START-DATE'); ?>:<br>
        <input type='text' name='start_date' id='start_date' value='<?php echo($sdate) ?>' size='15' class='date-field'></td>
      </tr>
      <tr>
       <td align='left'><?php displayLanguageText('TEXT-END-DATE'); ?>:<br>
        <input type='text' name='end_date' id='end_date' value='<?php echo($edate) ?>' size='15' class='date-field'></td>
      </tr>
      <tr>
       <td align='left' valign='top'><?php displayLanguageText('TEXT-USER'); ?>:<br>
        <select name='selected_usernum' class='select-width500'>
         <option value=''><?php displayLanguageText('TEXT-VIEW-ALL'); ?></option>
<?php
// List userid's from users table
$results = db_query("select userid,concat_ws(' ',first_name,last_name) as name,type,record_num from users where school_num = $base_school_num order by last_name,first_name");
while ($row = db_fetch_array($results)) {
   $userid = $row['userid'];
   $name   = $row['name'];
   $type   = $row['type'];
   $rnum   = $row['record_num'];

   $selected = "";
   if (isset($selected_usernum) && $selected_usernum == $rnum) $selected = " selected='selected'";
   echo("           <option value='$rnum'$selected>$name ($type - $userid)</option>\n");
}
?>
        </select>
       </td>
      </tr>
<?php if ($deleteType != 'deleted') { ?>
      <tr>
       <td align='left' valign='top'><?php displayLanguageText('TEXT-TRANSACTION-TYPE'); ?>:<br>
        <select name='selected_transaction_type'>
         <option value=''><?php displayLanguageText('TEXT-VIEW-ALL'); ?></option>
         <optgroup label="<?php displayLanguageText('TEXT-CHARGES-AND-FEES'); ?>">
         <?php BuildFileList("transaction_types_charges.txt",$selected_transaction_type) ?>
         <?php
         $r = db_query("SELECT * FROM user_account_trx_codes WHERE school_num = $school_num AND trx_category = 'Charge' ORDER BY sequence");
         if (db_num_rows($r)) {
            while ($row = db_fetch_assoc($r)) {
               $transaction_type_code = $row['transaction_type_code'];
               $selected = "";
               if ($transaction_type_code == $selected_transaction_type) $selected = " selected='selected'";
               echo("<option value='$transaction_type_code'$selected>$transaction_type_code</option>\n");
            }
         }
         ?>
         </optgroup>
         <optgroup label="<?php displayLanguageText('TEXT-PAYMENTS-AND-CREDITS'); ?>">
         <?php BuildFileList("transaction_types_payments.txt",$selected_transaction_type) ?>
         <?php if ($selected_transaction_type == 'Complimentary Credit') $selected = " selected='selected'"; ?>
         <option value='Complimentary Credit'<?php echo($selected) ?>>Complimentary Credit</option>
         <?php
         $r = db_query("SELECT * FROM user_account_trx_codes WHERE school_num = $school_num AND trx_category = 'Credit' ORDER BY sequence");
         if (db_num_rows($r)) {
            while ($row = db_fetch_assoc($r)) {
               $transaction_type_code = $row['transaction_type_code'];
               $selected = "";
               if ($transaction_type_code == $selected_transaction_type) $selected = " selected='selected'";
               echo("<option value='$transaction_type_code'$selected>$transaction_type_code</option>\n");
            }
         }
         ?>
         </optgroup>
         <optgroup label="<?php displayLanguageText('TEXT-OTHER'); ?>">
         <?php BuildFileList("transaction_types_other.txt",$selected_transaction_type) ?>
         <?php
         $r = db_query("SELECT * FROM user_account_trx_codes WHERE school_num = $school_num AND trx_category = 'Note' ORDER BY sequence");
         if (db_num_rows($r)) {
            while ($row = db_fetch_assoc($r)) {
               $transaction_type_code = $row['transaction_type_code'];
               $selected = "";
               if ($transaction_type_code == $selected_transaction_type) $selected = " selected='selected'";
               echo("<option value='$transaction_type_code'$selected>$transaction_type_code</option>\n");
            }
         }
         ?>
         </optgroup>
        </select>
       </td>
      </tr>
      <tr>
       <td align='left' valign='top'><?php displayLanguageText('TEXT-PAYMENT-METHOD'); ?>:<br>
        <select name='selected_payment_method'>
         <option value=''><?php displayLanguageText('TEXT-VIEW-ALL'); ?></option>
         <?php BuildFileList("payment_method.pp.txt",$selected_payment_method) ?>
        </select>
       </td>
      </tr>
<?php } ?>
      <tr>
       <td align='left' valign='top'><?php displayLanguageText('TEXT-SCHEDULE'); ?>:<br>
        <select name='selected_schedule_num' class='select-width500'>
         <option value=''><?php displayLanguageText('TEXT-VIEW-ALL'); ?></option>
<?php
$end_date = date("Y-m-d",time() - 60 * 60 * 24 * 360);
$query = "SELECT schedule_name,record_num FROM hotlunch_schedules " .
         "WHERE school_num = $school_num " .
         "AND schedule_end_date >= '$end_date' " .
         "AND status != 'Deleted' " .
         "ORDER BY schedule_start_date DESC";
$schedules = db_query($query);
while ($row = db_fetch_array($schedules)) {
   $sname = $row['schedule_name'];
   $rnum = $row['record_num'];
   $selected = "";
   if ($selected_schedule_num == $rnum) $selected = " selected='selected'";
   echo("      <option value='$rnum'$selected>$sname</option>\n");
}
?>
        </select>
       </td>
      </tr>
      <tr>
       <td align='left' valign='top'><?php displayLanguageText('TEXT-REPORT-SORT'); ?>:<br>
        <?php BuildFileRadio("user_account.report.sort.txt","sort",$sort) ?>
       </td>
      </tr>
<?php if ($siteType == 'schoolfees') { ?>
      <tr>
       <td align='left' valign='top'><?php displayLanguageText('TEXT-VIEW'); ?>:<br>
        <?php BuildFileRadio("user_account.report.view.txt","view_trx",$view_trx) ?>
       </td>
<?php } ?>
      </tr>
      <tr>
       <td align='left' valign='top'><a href='#' class='btn btn--primary btn--xsmall' onClick='document.transactions.submit();'><span><?php displayLanguageText('TEXT-VIEW'); ?></span></a></td>
      </tr>
     </table>
     </td>
     </tr>
     </table>
     <input type='hidden' name='action' value='user-account-report'>
    </form>
<?php

//
// List all requested records
//
$columnHeaders = array("User ID","TRX Date","TRX Type","Note","Amount","Method");
$query = "SELECT u.userid,substring(transaction_datetime,1,10) as trx_date,transaction_type,note,transaction_amount,schedule_num,order_num," .
            "transaction_status,trx_history,payment_method,payment_reference,order_payments,ua.user_num,people_num,people_type,additional_note,ua.record_num,deleted," .
            "deleted_datetime,deleted_by_user_num,delete_reason,u.first_name,u.last_name " .
         "FROM user_account as ua LEFT JOIN people as p ON (p.school_num = $base_school_num AND ua.people_num = p.record_num), users AS u " .
         "WHERE u.school_num = $base_school_num " .
         "AND ua.school_num = $school_num " .
         "AND ua.user_num = u.record_num " .
         $uwhere . $twhere . $swhere . $vwhere .
         $order_by;
$transactions = db_query($query);

$mode = 'lookup';
include "user_account.list.php";


endTemplate();
?>