502 lines
17 KiB
PHP
502 lines
17 KiB
PHP
<?php
|
|
$ValidAccess = 2010;
|
|
require_once("include/globals.php");
|
|
|
|
|
|
//
|
|
// Ensure that the user is logged in
|
|
//
|
|
if (SessionActive() != 1 || !UserLoggedIn()) {
|
|
echo("Invalid Access");
|
|
exit();
|
|
}
|
|
|
|
|
|
//
|
|
// Setup the PDF
|
|
//
|
|
$document_font = $school_record['document_font'];
|
|
if (strlen($document_font) == 0) $document_font = "Helvetica";
|
|
|
|
$document_font_weight = "";
|
|
$document_text_color = "#000000";
|
|
$document_font_size = 8;
|
|
|
|
|
|
// Setup parameters for this document
|
|
$max_rows = 176;
|
|
$left_col = 8;
|
|
$name_col = 100;
|
|
$columns = array(8,40,60,92,175,205,222,240,255,273);
|
|
$start_row = 25;
|
|
$curr_row = $max_rows + 10;
|
|
$incr = 4;
|
|
|
|
$document_name = getLanguageText('TEXT-ACCOUNT-TRANSACTION-REPORT');
|
|
$currentUserNum = 0;
|
|
|
|
|
|
|
|
//
|
|
// Setup PDF document
|
|
//
|
|
require("tcpdf/config/lang/eng.php");
|
|
require("tcpdf/tcpdf.php");
|
|
|
|
$logo_url = $school_record['logo_url'];
|
|
$logo_image = "images/" . $school_record['logo_image'];
|
|
$logo_row = $max_rows + 10;
|
|
|
|
class PDF extends TCPDF {
|
|
function Header() {
|
|
return;
|
|
}
|
|
|
|
function Footer() {
|
|
global $incr, $left_col, $title_width, $max_rows, $document_font, $document_font_size, $document_name, $logo_image, $logo_url, $logo_row, $school_name;
|
|
global $currentUserNum, $base_school_num, $name_col, $school_record;
|
|
|
|
// Print the date
|
|
$this->SetFont($document_font,"",$document_font_size - 1);
|
|
$this->SetXY($left_col,$logo_row);
|
|
$date = dateString('datetime');
|
|
$this->Cell($title_width,$incr - 1,"$document_name: $date",'0',1,'R');
|
|
|
|
$this->SetXY($left_col,$logo_row + $incr);
|
|
$this->Cell($title_width,$incr - 1,"$school_name",'0',1,'R','',1);
|
|
|
|
$this->Image($logo_image,10,$logo_row, 50);
|
|
|
|
$this->SetXY(20,$logo_row + 9);
|
|
$this->SetFont($document_font,"B",$document_font_size);
|
|
$this->Cell($title_width,$incr,$logo_url,'0',1,'L');
|
|
|
|
if (isset($currentUserNum) && $currentUserNum > 0) {
|
|
$nameText = db_getVal("SELECT concat(first_name,' ',last_name) FROM users WHERE school_num = $base_school_num AND record_num = $currentUserNum");
|
|
if (strlen($school_record['tax_number'])) $nameText .= " - " . $school_record['tax_number'];
|
|
|
|
$this->SetFont($document_font,"B",$document_font_size);
|
|
$this->SetXY($name_col,$logo_row);
|
|
$this->Cell(100,$incr,"$nameText",'0',1,'L');
|
|
}
|
|
}
|
|
}
|
|
|
|
// Force exact physical size for Letter: 8.5 x 11 inches => 279.4mm x 215.9mm (landscape)
|
|
$pageFormat = array(279.4, 215.9);
|
|
|
|
$pdf= new PDF('L', 'mm', $pageFormat, true, 'UTF-8', false);
|
|
|
|
$pdf->addPage();
|
|
|
|
$font_red = hexdec("00");
|
|
$font_green = hexdec("00");
|
|
$font_blue = hexdec("00");
|
|
|
|
$pdf->SetTextColor($font_red,$font_green,$font_blue);
|
|
|
|
$school_name = $school_record['school_name'];
|
|
$school_address = $school_record['address'].', '.$school_record['city'].' '.$school_record['province'].' '.$school_record['postal'];
|
|
|
|
|
|
// Build the query statement
|
|
if ($_SESSION['UserInfo']['type'] == 'Parent' || $_SESSION['UserInfo']['type'] == 'Staff') {
|
|
$user_num = intval($_SESSION['UserInfo']['record_num']);
|
|
$currentUserNum = $user_num;
|
|
|
|
$query = "SELECT u.userid,substr(transaction_datetime,1,10) as trx_date,transaction_type,note,transaction_amount," .
|
|
"payment_method,payment_reference,user_num,ua.record_num " .
|
|
"FROM user_account as ua, users AS u " .
|
|
"WHERE u.school_num = $base_school_num " .
|
|
"AND ua.school_num = $school_num " .
|
|
"AND ua.user_num = $user_num " .
|
|
"AND ua.user_num = u.record_num " .
|
|
"AND (deleted != 'Y' OR deleted IS NULL) " .
|
|
"ORDER BY userid,transaction_datetime,record_num";
|
|
$transactions = db_query($query);
|
|
$mode = 'account';
|
|
}
|
|
else {
|
|
// Start the query statement
|
|
$query = "SELECT u.userid,substr(transaction_datetime,1,10) as trx_date,transaction_type,note,transaction_amount," .
|
|
"payment_method,payment_reference,user_num,ua.record_num,deleted,deleted_datetime,deleted_by_user_num,delete_reason " .
|
|
"FROM user_account as ua, users AS u " .
|
|
"WHERE u.school_num = $base_school_num " .
|
|
"AND ua.school_num = $school_num " .
|
|
"AND ua.user_num = u.record_num ";
|
|
|
|
$mode = 'lookup';
|
|
|
|
// Manage start date parameter
|
|
if (array_key_exists('start_date',$_GET) && strlen($_GET['start_date']) > 0) {
|
|
$query .= "AND transaction_datetime >= '" . db_escape_string($_GET['start_date']) . " 00:00:00' ";
|
|
}
|
|
|
|
// Manage end date parameter
|
|
if (array_key_exists('end_date',$_GET) && strlen($_GET['end_date']) > 0) {
|
|
$query .= "AND transaction_datetime <= '" . db_escape_string($_GET['end_date']) . " 23:59:59' ";
|
|
}
|
|
|
|
// Manage user_num parameter
|
|
if (array_key_exists('selected_usernum',$_GET) && strlen($_GET['selected_usernum']) > 0) {
|
|
$currentUserNum = intval($_GET['selected_usernum']);
|
|
$query .= "AND ua.user_num = " . intval($_GET['selected_usernum']) . " ";
|
|
$mode = 'account';
|
|
}
|
|
|
|
// Manage selected transaction type parameter
|
|
if (array_key_exists('selected_transaction_type',$_GET) && strlen($_GET['selected_transaction_type']) > 0) {
|
|
$query .= "AND ua.transaction_type = '" . db_escape_string($_GET['selected_transaction_type']) . "' ";
|
|
}
|
|
|
|
// Manage selected payment methods
|
|
if (array_key_exists('selected_payment_method',$_GET) && strlen($_GET['selected_payment_method']) > 0) {
|
|
if (array_key_exists('selected_transaction_type',$_GET) && $_GET['selected_transaction_type'] != 'Payment') $selected_payment_method == NULL;
|
|
else {
|
|
$query .= "AND ua.payment_method = '" . db_escape_string($_GET['selected_payment_method']) . "' ";
|
|
}
|
|
}
|
|
|
|
// View only selected schedule
|
|
if (array_key_exists('selected_schedule_num',$_GET) && strlen($_GET['selected_schedule_num']) > 0) {
|
|
$query .= "AND ua.schedule_num = ' " . intval($_GET['selected_schedule_num']) . "' ";
|
|
}
|
|
|
|
// Process the optional type parameter
|
|
if (array_key_exists('type',$_GET) && strlen($_GET['type']) > 0 && $_GET['type'] == 'deleted') {
|
|
$query .= "AND deleted = 'Y' ";
|
|
}
|
|
else {
|
|
$query .= "AND (deleted != 'Y' OR deleted IS NULL) ";
|
|
}
|
|
|
|
// Set trx view
|
|
if (array_key_exists('view_trx',$_REQUEST) && strlen($_REQUEST['view_trx'])) {
|
|
$view_trx = $_REQUEST['view_trx'];
|
|
if ($view_trx == 'Open') $query .= "AND transaction_status = 'Open' ";
|
|
if ($view_trx == 'Closed') $query .= "AND transaction_status = 'Closed' ";
|
|
}
|
|
|
|
|
|
// Set query sort order
|
|
$order_by = "ORDER BY transaction_datetime,record_num";
|
|
if (array_key_exists('sort',$_GET) && strlen($_GET['sort'])) {
|
|
if (array_key_exists('sort', $_GET) && $_GET['sort'] == 'Name') $order_by = "ORDER BY last_name,first_name,transaction_datetime,record_num";
|
|
if (array_key_exists('sort', $_GET) && $_GET['sort'] == 'Payment Date') $order_by = "ORDER BY payment_time,record_num";
|
|
}
|
|
|
|
|
|
// Complete the query
|
|
$query .= $order_by;
|
|
$transactions = db_query($query);
|
|
}
|
|
|
|
|
|
$pdf->SetFillColor(hexdec("ee"),hexdec("ee"),hexdec("ee"));
|
|
|
|
$page = 0;
|
|
$count = 0;
|
|
$balance = 0;
|
|
$total_charges = 0;
|
|
$total_credits = 0;
|
|
|
|
while ($row = db_fetch_array($transactions)) {
|
|
extract($row);
|
|
|
|
if ($curr_row > $max_rows) {
|
|
// Put page titles on the page whenever a page is full
|
|
if ($page > 0) $pdf->addPage();
|
|
$curr_row = 10;
|
|
|
|
$pdf->SetFont($document_font,"B",$document_font_size + 2);
|
|
$pdf->SetXY(10,$curr_row);
|
|
$pdf->Cell(250,$incr,getLanguageText('TEXT-ACCOUNT-TRANSACTION-REPORT'),'0',1,'L',false,'',1);
|
|
$pdf->SetFont($document_font,"B",$document_font_size);
|
|
|
|
if (isset($currentUserNum) && $currentUserNum > 0) {
|
|
$nameText = db_getVal("SELECT concat(first_name,' ',last_name) FROM users WHERE school_num = $base_school_num AND record_num = $currentUserNum");
|
|
$pdf->SetXY($left_col,$curr_row);
|
|
$pdf->Cell(250,$incr,$nameText,'0',1,'R',false,'',1);
|
|
}
|
|
|
|
$curr_row += $incr + 2;
|
|
$pdf->SetXY(10,$curr_row);
|
|
$pdf->Cell(250,$incr,$school_name,'0',1,'L',false,'',1);
|
|
|
|
$curr_row += $incr - 1;
|
|
$pdf->SetXY(10,$curr_row);
|
|
$pdf->Cell(250,$incr,$school_address,'0',1,'L',false,'',1);
|
|
|
|
$curr_row = 26;
|
|
$pdf->SetFillColor(hexdec("cc"),hexdec("cc"),hexdec("cc"));
|
|
|
|
$cnum = 0;
|
|
$curr_col = $columns[$cnum];
|
|
$width = $columns[$cnum + 1] - $columns[$cnum];
|
|
$pdf->SetXY($curr_col,$curr_row);
|
|
$pdf->Cell($width,$incr,getLanguageText('TEXT-USER-ID'),'0',1,'L',true,'',1);
|
|
|
|
$cnum++;
|
|
$curr_col = $columns[$cnum];
|
|
$width = $columns[$cnum + 1] - $columns[$cnum];
|
|
$pdf->SetXY($curr_col,$curr_row);
|
|
$pdf->Cell($width,$incr,getLanguageText('TEXT-DATE'),'0',1,'L',true,'',1);
|
|
|
|
$cnum++;
|
|
$curr_col = $columns[$cnum];
|
|
$width = $columns[$cnum + 1] - $columns[$cnum];
|
|
$pdf->SetXY($curr_col,$curr_row);
|
|
$pdf->Cell($width,$incr,getLanguageText('TEXT-TYPE'),'0',1,'L',true,'',1);
|
|
|
|
$cnum++;
|
|
$curr_col = $columns[$cnum];
|
|
$width = $columns[$cnum + 1] - $columns[$cnum];
|
|
$pdf->SetXY($curr_col,$curr_row);
|
|
$pdf->Cell($width,$incr,getLanguageText('TEXT-NOTE'),'0',1,'L',true,'',1);
|
|
|
|
$cnum++;
|
|
$curr_col = $columns[$cnum];
|
|
$width = $columns[$cnum + 1] - $columns[$cnum];
|
|
$pdf->SetXY($curr_col,$curr_row);
|
|
$pdf->Cell($width,$incr,getLanguageText('TEXT-TRX-ID'),'0',1,'L',true,'',1);
|
|
|
|
$cnum++;
|
|
$curr_col = $columns[$cnum];
|
|
$width = $columns[$cnum + 1] - $columns[$cnum];
|
|
$pdf->SetXY($curr_col,$curr_row);
|
|
$pdf->Cell($width,$incr,getLanguageText('TEXT-CHARGES'),'0',1,'R',true,'',1);
|
|
|
|
$cnum++;
|
|
$curr_col = $columns[$cnum];
|
|
$width = $columns[$cnum + 1] - $columns[$cnum];
|
|
$pdf->SetXY($curr_col,$curr_row);
|
|
$pdf->Cell($width,$incr,getLanguageText('TEXT-CREDITS'),'0',1,'R',true,'',1);
|
|
|
|
$cnum++;
|
|
$curr_col = $columns[$cnum];
|
|
$width = $columns[$cnum + 1] - $columns[$cnum];
|
|
$pdf->SetXY($curr_col,$curr_row);
|
|
$pdf->Cell($width,$incr," ".getLanguageText('TEXT-METHOD'),'0',1,'L',true,'',1);
|
|
|
|
$cnum++;
|
|
$curr_col = $columns[$cnum];
|
|
$width = $columns[$cnum + 1] - $columns[$cnum];
|
|
$pdf->SetXY($curr_col,$curr_row);
|
|
$pdf->Cell($width,$incr,getLanguageText('TEXT-BALANCE'),'0',1,'R',true,'',1);
|
|
|
|
|
|
$pdf->SetFillColor(hexdec("ee"),hexdec("ee"),hexdec("ee"));
|
|
$pdf->SetFont($document_font,"",$document_font_size);
|
|
$curr_row += $incr;
|
|
$page++;
|
|
}
|
|
|
|
$bg = false;
|
|
if ($count % 2) $bg = true;
|
|
|
|
$cnum = 0;
|
|
$curr_col = $columns[$cnum];
|
|
$width = $columns[$cnum + 1] - $columns[$cnum];
|
|
$pdf->SetXY($curr_col,$curr_row);
|
|
$pdf->Cell($width,$incr,$row['userid'],'0',1,'L',$bg,'',1);
|
|
|
|
$cnum++;
|
|
$curr_col = $columns[$cnum];
|
|
$width = $columns[$cnum + 1] - $columns[$cnum];
|
|
$pdf->SetXY($curr_col,$curr_row);
|
|
$pdf->Cell($width,$incr,$row['trx_date'],'0',1,'L',$bg,'',1);
|
|
|
|
$cnum++;
|
|
$curr_col = $columns[$cnum];
|
|
$width = $columns[$cnum + 1] - $columns[$cnum];
|
|
$pdf->SetXY($curr_col,$curr_row);
|
|
$transaction_type = translateText($row['transaction_type']);
|
|
$pdf->Cell($width,$incr,$transaction_type,'0',1,'L',$bg,'',1);
|
|
|
|
$cnum++;
|
|
$curr_col = $columns[$cnum];
|
|
$width = $columns[$cnum + 1] - $columns[$cnum];
|
|
$pdf->SetXY($curr_col,$curr_row);
|
|
$note = translateText($row['note']);
|
|
$pdf->Cell($width,$incr,substr($note,0,50),'0',1,'L',$bg,'',1);
|
|
|
|
$cnum++;
|
|
$curr_col = $columns[$cnum];
|
|
$width = $columns[$cnum + 1] - $columns[$cnum];
|
|
$pdf->SetXY($curr_col,$curr_row);
|
|
$pdf->Cell($width,$incr,$row['payment_reference'],'0',1,'L',$bg,'',1);
|
|
|
|
// Determine if this is a charge or credit and set the values
|
|
$charge = '';
|
|
$credit = '';
|
|
|
|
// Lookup TRX Type code
|
|
$trx_type = $transaction_type;
|
|
$r = db_query("SELECT * FROM user_account_trx_codes WHERE school_num = $school_num AND transaction_type_code = '$transaction_type'");
|
|
if (db_num_rows($r)) {
|
|
$row = db_fetch_assoc($r);
|
|
$trx_type = $row['trx_category'];
|
|
}
|
|
|
|
switch ($trx_type) {
|
|
case 'Order':
|
|
case 'Donation':
|
|
case 'NSF':
|
|
case 'Refund':
|
|
case 'Late Charge':
|
|
case 'Tax Charge':
|
|
case 'Paper Order Charge':
|
|
case 'Remittance Form Fee':
|
|
case 'Additional Order Fee':
|
|
case 'Other Charge':
|
|
case 'Charge Adjustment':
|
|
case 'PayPal Transaction Fee':
|
|
case 'Credit Card Transaction Fee':
|
|
case 'Reversed Payment':
|
|
case 'Charge':
|
|
case 'Generated Charge':
|
|
$charge = '$' . $transaction_amount;
|
|
$balance += $transaction_amount;
|
|
$total_charges += $transaction_amount;
|
|
break;
|
|
|
|
case 'TRX Note':
|
|
break;
|
|
|
|
case 'Payment':
|
|
case 'Credit':
|
|
case 'Credit Adjustment':
|
|
case 'Multiple Order Discount':
|
|
default:
|
|
$credit = "- $currencySymbol" . number_format(abs($transaction_amount),2);
|
|
$balance += $transaction_amount;
|
|
$total_credits += $transaction_amount;
|
|
break;
|
|
}
|
|
|
|
// Format the current balance
|
|
$balance = round($balance * 100) / 100;
|
|
if ($balance < 0.00) $balance_amt = "- $currencySymbol" . number_format(abs($balance),2);
|
|
else $balance_amt = $currencySymbol . number_format($balance,2);
|
|
|
|
$cnum++;
|
|
$curr_col = $columns[$cnum];
|
|
$width = $columns[$cnum + 1] - $columns[$cnum];
|
|
$pdf->SetXY($curr_col,$curr_row);
|
|
$pdf->Cell($width,$incr,$charge,'0',1,'R',$bg,'',1);
|
|
|
|
$cnum++;
|
|
$curr_col = $columns[$cnum];
|
|
$width = $columns[$cnum + 1] - $columns[$cnum];
|
|
$pdf->SetXY($curr_col,$curr_row);
|
|
$pdf->Cell($width,$incr,$credit,'0',1,'R',$bg,'',1);
|
|
|
|
$cnum++;
|
|
$curr_col = $columns[$cnum];
|
|
$width = $columns[$cnum + 1] - $columns[$cnum];
|
|
$pdf->SetXY($curr_col,$curr_row);
|
|
|
|
$payment_method = '';
|
|
if (array_key_exists('payment_method', $row)) $payment_method = translateText($row['payment_method']);
|
|
$pdf->Cell($width,$incr,' ' . $payment_method,'0',1,'L',$bg,'',1);
|
|
|
|
$cnum++;
|
|
$curr_col = $columns[$cnum];
|
|
$width = $columns[$cnum + 1] - $columns[$cnum];
|
|
$pdf->SetXY($curr_col,$curr_row);
|
|
$pdf->Cell($width,$incr,$balance_amt,'0',1,'R',$bg,'',1);
|
|
|
|
$curr_row += $incr;
|
|
|
|
|
|
// Display deleted information if required
|
|
if (isset($row['deleted']) && $row['deleted'] == 'Y') {
|
|
$deleted_datetime = $row['deleted_datetime'];
|
|
$deleted_by_user_num = $row['deleted_by_user_num'];
|
|
$delete_reason = $row['delete_reason'];
|
|
|
|
$delete_userid = "superuser";
|
|
if ($deleted_by_user_num != 1) $delete_userid = db_getVal("SELECT userid FROM users WHERE school_num = $base_school_num AND record_num = $deleted_by_user_num");
|
|
$delete_info = "Deleted $deleted_datetime by $delete_userid - $delete_reason";
|
|
|
|
$pdf->SetXY($columns[0],$curr_row);
|
|
$pdf->Cell(100,$incr," ",'0',1,'L',$bg,'',1);
|
|
|
|
$pdf->SetXY($columns[1],$curr_row);
|
|
$width = $columns[count($columns) - 1] - $columns[1];
|
|
$pdf->Cell($width,$incr,$delete_info,'0',1,'L',$bg,'',1);
|
|
|
|
$curr_row += $incr + 2;
|
|
}
|
|
|
|
$count++;
|
|
}
|
|
|
|
$pdf->SetFillColor(hexdec("cc"),hexdec("cc"),hexdec("cc"));
|
|
$pdf->SetFont($document_font,"B",$document_font_size);
|
|
|
|
|
|
if ($_SESSION['UserInfo']['type'] == 'Parent' || $_SESSION['UserInfo']['type'] == 'Staff' || $mode == "account") {
|
|
// Display totals for the report
|
|
$curr_col = $columns[0];
|
|
$width = $columns[8] - $columns[0];
|
|
$pdf->SetXY($curr_col,$curr_row);
|
|
$pdf->Cell($width,$incr,getLanguageText('TEXT-ACCOUNT-BALANCE')." :: ",'0',1,'R',true,'',1);
|
|
|
|
$cnum = 8;
|
|
$curr_col = $columns[$cnum];
|
|
$width = $columns[$cnum + 1] - $columns[$cnum];
|
|
$pdf->SetXY($curr_col,$curr_row);
|
|
$pdf->Cell($width,$incr,$balance_amt,'0',1,'R',true,'',1);
|
|
}
|
|
else {
|
|
// Format report totals
|
|
$total_charges = round($total_charges * 100) / 100;
|
|
if ($total_charges < 0.00) $total_charges = "- $currencySymbol" . number_format(abs($total_charges),2);
|
|
else $total_charges = $currencySymbol . number_format($total_charges,2);
|
|
|
|
$total_credits = round($total_credits * 100) / 100;
|
|
if ($total_credits < 0.00) $total_credits = "- $currencySymbol" . number_format(abs($total_credits),2);
|
|
else $total_credits = $currencySymbol . number_format($total_credits,2);
|
|
|
|
|
|
// Display totals for the report
|
|
$curr_col = $columns[0];
|
|
$width = $columns[5] - $columns[0];
|
|
$pdf->SetXY($curr_col,$curr_row);
|
|
$pdf->Cell($width,$incr,getLanguageText('TEXT-TRX-TOTALS-FOR-PERIOD')." :: ",'0',1,'R',true,'',1);
|
|
|
|
$cnum = 5;
|
|
$curr_col = $columns[$cnum];
|
|
$width = $columns[$cnum + 1] - $columns[$cnum];
|
|
$pdf->SetXY($curr_col,$curr_row);
|
|
$pdf->Cell($width,$incr,$total_charges,'0',1,'R',true,'',1);
|
|
|
|
$cnum = 6;
|
|
$curr_col = $columns[$cnum];
|
|
$width = $columns[$cnum + 1] - $columns[$cnum];
|
|
$pdf->SetXY($curr_col,$curr_row);
|
|
$pdf->Cell($width,$incr,$total_credits,'0',1,'R',true,'',1);
|
|
|
|
$curr_col = $columns[7];
|
|
$width = $columns[9] - $columns[7];
|
|
$pdf->SetXY($curr_col,$curr_row);
|
|
$pdf->Cell($width,$incr,"",'0',1,'R',true,'',1);
|
|
}
|
|
|
|
|
|
|
|
// Output the completed PDF
|
|
header('Pragma: public');
|
|
header('Expires: 0');
|
|
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
|
|
header('Cache-Control: private',false);
|
|
header('Content-type: application/pdf');
|
|
header("Content-Disposition: attachment; filename=account-transactions.pdf");
|
|
header('Content-Transfer-Encoding: binary');
|
|
|
|
$pdf->Output("account-transactions.pdf","D");
|
|
return;
|
|
|
|
?>
|