<?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;

?>