";
$FormFile = "invoices.form.php";
$TitleText = "Invoice";
$OrderBy = "sequence";
$Sequence = 'N';
$gst_rate = 5;
// Start the template and output buffering
startTemplate();
$templatePageTitle = "$TitleText " . getLanguageText('TEXT-ADMINISTRATION');
// Ensure that only valid user types have access to this module
$AllowedUserTypes = array('SuperUser');
if (checkPermission($AllowedUserTypes) != 1) {
echo("
\n");
$error_message = getLanguageText('ERRORMSG-ACCESS-DENIED');
include "form.messages.php";
include "schools.options.php";
endTemplate();
exit();
}
unset($selected_school);
if (array_key_exists('selected_school',$_GET) && strlen($_GET['selected_school'])) $selected_school = $_GET['selected_school'];
if (array_key_exists('selected_school',$_POST) && strlen($_POST['selected_school'])) $selected_school = $_POST['selected_school'];
$subtitle = "";
if (isset($selected_school)) {
$school_name = db_getVal("select school_name from schools where record_num = $selected_school");
$subtitle = " - $school_name";
}
include "schools.options.php";
if (array_key_exists('naction',$_GET)) $naction = $_GET['naction'];
if (array_key_exists('naction',$_POST)) $naction = $_POST['naction'];
//
// Handle the 'process' pre-processing
//
if ($naction == 'process') {
$naction = 'add';
if (array_key_exists('school_year',$_GET)) $school_year = $_GET['school_year'];
if ($school_year) {
$query = "select * from invoice_header " .
"where school_num = $selected_school " .
"and school_year = '$school_year'";
$results = db_query($query);
if (db_num_rows($results) > 0) {
$row = db_fetch_array($results);
$invoice_number = $row['record_num'];
$naction = 'edit';
}
}
}
$numLines = 40;
switch (strtolower($naction)) {
case "add":
$PageTitle = "Create a New $TitleText";
$ButtonText = "Add $TitleText";
$next_naction = "insert";
$show = "Open";
$active = 'Y';
include $FormFile;
break;
case "insert":
extract($_POST);
$rstatus = 'Y';
// Insert primary record
$table = new Table("$CurrentTable");
$query = $table->BuildInsert();
db_query($query);
$invoice_num = db_insert_id();
// Process invoice items
$invoice_items = new Table("invoice_items");
// Save the invoice items
$sequence = 1;
$subtotal = 0;
$blank = 0;
for ($i = 0; $i <= $numLines; $i++) {
if (array_key_exists("description$i", $_POST)) {
$description = $_POST["description$i"];
$amount = $_POST["amount$i"];
$amount = str_replace("$","",$amount);
$amount = str_replace(",","",$amount);
$subtotal += $amount;
if (strlen($description) == 0 && $amount == 0) $blank++;
else $blank = 0;
if ($blank <= 1) {
$query = $invoice_items->BuildInsert();
db_query($query);
$sequence++;
}
}
}
// Update totals
$gst = 0;
if ($charge_gst == 'Y') {
$gst = $subtotal * $gst_rate / 100;
}
$total = $subtotal + $gst;
$query = "update invoice_header " .
"set subtotal = $subtotal, " .
"gst = $gst, " .
"total = $total, " .
"amount_paid = 0.00 " .
"where record_num = $invoice_num";
db_query($query);
// Update school record that the invoice has been issued
db_query("UPDATE schools SET renewal_invoice_issued = 'Y' WHERE record_num = $school_num");
$form_message = "$TitleText Has Been Inserted";
$naction = 'list';
header("Location: /?action=$action&naction=$naction&selected_school=$selected_school&form_message=" . urlencode($form_message) . "&error_message=" . urlencode($error_message));
exit();
case "edit":
$PageTitle = "Edit an Existing $TitleText";
$ButtonText = "Update $TitleText";
$next_naction = "update";
// Get current record
if (!isset($invoice_number)) $record_num = intval($_GET['record_num']);
else $record_num = $invoice_number;
$query = "select * from $CurrentTable where record_num = $record_num";
$results = db_query($query);
if (db_num_rows($results) == 1) {
$row = db_fetch_array($results);
extract($row);
include $FormFile;
}
else {
$error_message = "Error: Record Not Found";
$naction = 'list';
header("Location: /?action=$action&naction=$naction&selected_school=$selected_school&form_message=" . urlencode($form_message) . "&error_message=" . urlencode($error_message));
exit();
}
break;
case "update":
extract($_POST);
$amount_paid = str_replace(",","",$amount_paid);
$total = str_replace(",","",$total);
// Save the invoice header
$table = new Table("$CurrentTable");
$query = $table->BuildUpdate();
db_query($query);
$invoice_num = $record_num;
// Delete current invoice items
$query = "delete from invoice_items " .
"where invoice_num = $invoice_num";
db_query($query);
// Process invoice items
$invoice_items = new Table("invoice_items");
// Save the invoice items
$sequence = 1;
$subtotal = 0;
$blank = 0;
for ($i = 0; $i <= $numLines; $i++) {
if (array_key_exists("description$i", $_POST)) {
$description = $_POST["description$i"];
$amount = $_POST["amount$i"];
$amount = str_replace("\\$","",$amount);
$amount = str_replace(",","",$amount);
$subtotal += $amount;
if (strlen($description) == 0 && $amount == 0) $blank++;
else $blank = 0;
if ($blank <= 1) {
$query = $invoice_items->BuildInsert();
db_query($query);
$sequence++;
}
}
}
// Update totals
$gst = 0;
if ($charge_gst == 'Y') {
$gst = $subtotal * $gst_rate / 100;
}
$total = $subtotal + $gst;
$query = "update invoice_header " .
"set subtotal = $subtotal, " .
"gst = $gst, " .
"total = $total, " .
"amount_paid = $amount_paid " .
"where record_num = $invoice_num";
db_query($query);
// Update school record that the invoice has been issued
db_query("UPDATE schools SET renewal_invoice_issued = 'Y' WHERE record_num = $school_num");
$form_message = "$TitleText Has Been Updated";
if (!isset($show)) $show = '';
$naction = 'list';
header("Location: /?action=$action&show=$show&naction=$naction&selected_school=$selected_school&form_message=" . urlencode($form_message) . "&error_message=" . urlencode($error_message));
exit();
case "delete":
$record_num = intval($_GET['record_num']);
$selected_school = intval($_GET['selected_school']);
$show = $_GET['show'];
$query = "select * from $CurrentTable where record_num = $record_num";
$results = db_query($query);
if (db_num_rows($results) == 1) {
$row = db_fetch_array($results);
extract($row);
$dFields = explode(",",$DeleteField);
$dTitle = "$TitleText for School Num: ";
for ($i = 0; $i < count($dFields); $i++) {
$dTitle .= $row[$dFields[$i]] . $dSeparator;
}
$HiddenFields = " \n";
$HiddenFields .= " \n";
include "delete.form.php";
}
else {
$error_message = "Error: Record Not Found";
$naction = 'list';
header("Location: /?action=$action&show=$show&naction=$naction&selected_school=$selected_school&form_message=" . urlencode($form_message) . "&error_message=" . urlencode($error_message));
exit();
}
break;
case "delete-confirmed":
$record_num = intval($_GET['record_num']);
$show = $_GET['show'];
// Flag as deleted
$query = "UPDATE $CurrentTable SET status = 'Cancelled' WHERE record_num = $record_num";
db_query($query);
// Update school record that the invoice has been issued
db_query("UPDATE schools SET renewal_invoice_issued = 'N' WHERE record_num = $school_num");
$form_message = "$TitleText Has Been Cancelled";
$naction = 'list';
header("Location: /?action=$action&show=$show&naction=$naction&form_message=" . urlencode($form_message) . "&error_message=" . urlencode($error_message));
exit();
case "view-invoice":
ob_end_clean();
$invoice_record_num = intval($_GET['invoice_num']);
// Get invoice record
$results = db_query("SELECT * FROM invoice_header WHERE record_num = $invoice_record_num");
if (db_num_rows($results) == 0) {
$error_message = "Invoice Not Found - $invoice_record_num";
header("Location: /?action=invoices&error_message=" . urlencode($error_message));
exit();
}
$invoice = db_fetch_array($results);
$invoice_number = $row['record_num'];
$school_num = $invoice['school_num'];
$school_name = db_getVal("SELECT school_name FROM schools WHERE record_num = $school_num");
$inv_num = sprintf("%06d-HL",$invoice_num + 500);
$file_name = str_replace("/","-",$school_name);
$file_name = normalizeString($inv_num . "." . $file_name . ".pdf");
if (!is_file("invoices/$file_name")) {
$error_message = "Invoice Not Found - $file_name";
header("Location: /?action=invoices&error_message=" . urlencode($error_message));
exit();
}
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=$file_name");
header('Content-Transfer-Encoding: binary');
readfile("invoices/$file_name");
exit();
case "inv-paid":
$invoice_record_num = intval($_GET['invoice_num']);
$selected_school = intval($_GET['selected_school']);
$show = $_GET['show'];
$status = db_getVal("SELECT status FROM invoice_header WHERE record_num = $invoice_record_num");
if ($status != 'Open') {
$error_message = "Error: Invoice status is $status";
$naction = "list";
break;
}
// Get invoice record
$results = db_query("SELECT * FROM invoice_header WHERE record_num = $invoice_record_num");
$invoice = db_fetch_array($results);
$renewal_date = $invoice['renewal_date'];
$school_num = $invoice['school_num'];
$amount = $invoice['total'];
$today = date("Y-m-d",time());
db_query("UPDATE invoice_header SET amount_paid = total, status = 'Paid', payment_date = '$today', payment_method = 'Cheque' WHERE record_num = $invoice_record_num");
// Update the renewal date for the school
$renewal_date = date("Y-m-d",strtotime($renewal_date) - 60 * 60 * 12);
$edate = db_getVal("SELECT expiry_date FROM schools WHERE record_num = $school_num");
if ($renewal_date > $edate) db_query("UPDATE schools SET expiry_date = '$renewal_date' WHERE record_num = $school_num");
// Update school record that the invoice has been paid
db_query("UPDATE schools SET renewal_invoice_paid = 'Y' WHERE record_num = $school_num");
// Get country
$school_country = db_getVal("SELECT country FROM schools WHERE record_num = $school_num");
$currency = 'CAD';
if ($school_country == 'USA') $currency = 'USD';
//
// Save payment info for deposit report
//
$table = new Table("invoice_payments");
$invoice_num = $invoice_record_num + 500;
$entered = time();
$payment_date = date("Y-m-d",$entered);
$batch_num = 0;
$query = $table->BuildInsert();
db_query($query);
// Send thank you to school
include "include/queuemail.php";
// Get school contacts
$contacts = db_query("SELECT * FROM school_contact_list WHERE school_num = $school_num AND contact_email != '' AND contact_email IS NOT NULL ORDER BY sequence");
$count = 0;
while ($row = db_fetch_assoc($contacts)) {
extract($row);
// Get school info
$results = db_query("SELECT * FROM schools WHERE record_num = $school_num");
$row = db_fetch_array($results);
$school_name = $row['school_name'];
$status = $row['status'];
$expiry_date = $row['expiry_date'];
$system_configuration = $row['system_configuration'];
$emailMessage = "Hello $contact_name,\n" .
"\n" .
"Your payment has been received and posted to your account.\n" .
"\n" .
"Do not hesitate to send me an email with any questions or suggestions that you may have.\n" .
"\n" .
"School Name: $school_name\n" .
"School Status: $status\n" .
"Renewal Date: $renewal_date\n" .
"Configuration: $system_configuration\n" .
"\n" .
"Thank You\n" .
"\n" .
"Hot Lunches On-Line\n" .
"(604)996-7181" . "\n" .
"1-888-9HOTLUNCH (1-888-946-8586)" . "\n" .
"\n" .
"support@hotlunches.net\n" .
"http://www.hotlunches.net/\n\n";
$emailRecipient = $contact_name;
$emailTo = $contact_email;
$emailReply = "Hot Lunches On-Line";
$emailFrom = "support@hotlunches.net";
$emailSubject = "HotLunches.net Payment Received From $school_name";
QueueEmail($emailTo, $emailFrom, $emailSubject, $emailMessage, $emailReply, $emailRecipient);
if ($count == 0) {
$emailRecipient = "Hot Lunches Online";
$emailTo = "support@hotlunches.net";
QueueEmail($emailTo, $emailFrom, $emailSubject, $emailMessage, $emailReply, $emailRecipient);
}
$count++;
}
// Handle selected school parameter
$params = "";
if (isset($selected_school) && $selected_school > 0) $params = "&selected_school=$selected_school";
$form_message = "Invoice has been updated";
if (array_key_exists('return',$_GET) && strlen($_GET['return'])) {
$return = $_GET['return'];
header("Location: ?action=$return&search=yes&form_message=".urlencode($form_message));
exit();
}
$naction = 'list';
header("Location: /?action=$action&show=$show&naction=$naction$params&form_message=" . urlencode($form_message) . "&error_message=" . urlencode($error_message));
exit();
case "inv-outstanding":
$invoice_record_num = intval($_GET['invoice_num']);
// Get invoice record
$results = db_query("SELECT * FROM invoice_header WHERE record_num = $invoice_record_num");
$invoice = db_fetch_array($results);
$renewal_date = $invoice['renewal_date'];
$school_num = $invoice['school_num'];
$amount = $invoice['total'];
$invoice_number = $row['record_num'];
$school_name = db_getVal("SELECT school_name FROM schools WHERE record_num = $school_num");
$inv_num = sprintf("%06d-HL",$invoice_num + 500);
$file_name = str_replace("/","-",$school_name);
$file_name = normalizeString($inv_num . "." . $file_name . ".pdf");
// Send email to school
$recipients = "";
include "include/queuemail.php";
// Get school info
$results = db_query("SELECT * FROM schools WHERE record_num = $school_num");
$row = db_fetch_array($results);
$school_name = $row['school_name'];
$status = $row['status'];
$expiry_date = $row['expiry_date'];
$system_configuration = $row['system_configuration'];
// Additional message text based on end of year
$additionalText = "";
$month = date("m",time());
$day = date("d",time());
if ($row['reseller'] == 'hotlunch') {
if ($month == '06' || $month == '07') $additionalText = "Note that the Early Bird Discount offer expires July 31 and your invoice will be adjusted accordingly.\n";
if ($month == '08' && $day > '05') $additionalText = "The Early Bird Discount offer has expired and your invoice has been adjusted accordingly.\n";
}
// Get first school contact
$contacts = db_query("SELECT * FROM school_contact_list WHERE school_num = $school_num AND contact_email != '' AND contact_email IS NOT NULL ORDER BY sequence LIMIT 0,1");
while ($row = db_fetch_assoc($contacts)) {
extract($row);
$emailMessage = "Hello $contact_name,\n" .
"\n" .
"Please find attached the invoice the Hot Lunches On-Line Annual subscription for your school.\n" .
$additionalText .
"\n" .
"School Name: $school_name\n" .
"School Status: $status\n" .
"Renewal Date: $renewal_date\n" .
"Configuration: $system_configuration\n" .
"\n" .
"Thank You\n" .
"\n" .
"Hot Lunches On-Line\n" .
"(604)996-7181" . "\n" .
"1-888-9HOTLUNCH (1-888-946-8586)" . "\n" .
"\n" .
"support@hotlunches.net\n" .
"http://www.hotlunches.net/\n\n";
$emailRecipient = $contact_name;
$emailTo = $contact_email;
$emailReply = "Hot Lunches On-Line";
$emailFrom = "support@hotlunches.net";
$emailSubject = "HotLunches.net Invoice for $school_name";
QueueEmail($emailTo, $emailFrom, $emailSubject, $emailMessage, $emailReply, $emailRecipient, array("invoices/$file_name"));
if ($sequence == 1) {
$emailRecipient = "Hot Lunches Online";
$emailTo = "support@hotlunches.net";
QueueEmail($emailTo, $emailFrom, $emailSubject, $emailMessage, $emailReply, $emailRecipient, array("invoices/$file_name"));
}
}
$form_message = "Email Sent To: $contact_name";
$naction = 'list';
$params = "&show=Open";
header("Location: /?action=$action&show=$show&naction=$naction$params&form_message=" . urlencode($form_message) . "&error_message=" . urlencode($error_message));
exit();
case "inv-reminder":
include "include/queuemail.php";
$form_message = "";
$invoices = db_query("SELECT ih.*,school_name FROM invoice_header AS ih, schools AS s " .
"WHERE ih.status = 'Open' " .
"AND s.status = 'Active' " .
"AND s.record_num = ih.school_num " .
"AND ih.total > 0 " .
"ORDER BY school_name");
// Get invoice record
while ($invoice = db_fetch_assoc($invoices)) {
$renewal_date = $invoice['renewal_date'];
$school_num = $invoice['school_num'];
$amount = $invoice['total'];
$invoice_number = $invoice['record_num'];
// Get school info
$results = db_query("SELECT * FROM schools WHERE record_num = $school_num");
$row = db_fetch_array($results);
$school_name = $row['school_name'];
$status = $row['status'];
$expiry_date = $row['expiry_date'];
$system_configuration = $row['system_configuration'];
$reseller = $row['reseller'];
$inv_num = sprintf("%06d-HL",$invoice_number + 500);
$file_name = str_replace("/","-",$school_name);
$file_name = normalizeString($inv_num . "." . $file_name . ".pdf");
// Send email to school
$recipients = "";
// Get school contacts
$contacts = db_query("SELECT * FROM school_contact_list WHERE school_num = $school_num AND contact_email != '' AND contact_email IS NOT NULL ORDER BY sequence");
while ($row = db_fetch_assoc($contacts)) {
extract($row);
$today = date("m-d",time());
if ($today < "08-01") {
$emailMessage = "Hello $contact_name,\n" .
"\n" .
"This is a friendly reminder that the Hot Lunch 'Early Bird' discount will expire on July 31st and your invoice will be adjusted to remove this discount " .
"if payment has not been received by then. " .
"If you wish to take advantage of the discount you must pay online or via E-transfer to mark@hotlunches.net or contact us immediately regarding this matter.\n" .
"\n" .
"If we do not hear from you this week the discount will be removed from the invoice and your school will have to remit payment for " .
"the full amount without the 'Early Bird' Discount. " .
"\n" .
" - School Name: $school_name\n" .
" - School Status: $status\n" .
" - Configuration: $system_configuration\n" .
" - Balance Owing: $currencySymbol$amount\n" .
"\n" .
"Thank You\n" .
"\n" .
"Hot Lunches On-Line\n" .
"(604)996-7181" . "\n" .
"1-888-9HOTLUNCH (1-888-946-8586)" . "\n" .
"\n" .
"support@hotlunches.net\n" .
"http://www.hotlunches.net/\n\n";
}
else {
$emailMessage = "Hello $contact_name,\n" .
"\n" .
"This is a notification that the Hot Lunch 'Early Bird' discount expired on July 31st and your invoice has been adjusted to remove this discount.\n" .
"\n" .
" - School Name: $school_name\n" .
" - School Status: $status\n" .
" - Configuration: $system_configuration\n" .
" - Balance Owing: $currencySymbol$amount\n" .
"\n" .
"Thank You\n" .
"\n" .
"Hot Lunches On-Line\n" .
"(604)996-7181" . "\n" .
"1-888-9HOTLUNCH (1-888-946-8586)" . "\n" .
"\n" .
"support@hotlunches.net\n" .
"http://www.hotlunches.net/\n\n";
}
$emailRecipient = $contact_name;
$emailTo = $contact_email;
$emailReply = "Hot Lunches On-Line";
$emailFrom = "support@hotlunches.net";
$emailSubject = "HotLunches.net Outstanding Invoice for $school_name";
$form_message .= "$contact_name $contact_email
";
QueueEmail($emailTo, $emailFrom, $emailSubject, $emailMessage, $emailReply, $emailRecipient, array("invoices/$file_name"));
}
}
$naction = 'list';
$params = "&show=Open";
header("Location: /?action=$action&show=$show&naction=$naction$params&form_message=" . urlencode($form_message) . "&error_message=" . urlencode($error_message));
exit();
default:
$naction = "list";
break;
}
//
// List all records if naction is "list"
//
if ($naction == "list" || $naction == 'list-all') {
// Get records to display
$columnHeaders = array("School","Year","Inv #","Invoice Date","Renewal","Status","Total","Paid","Balance");
$columnAlign = array('left','center','center','center','center','center','right','right','right');
// Invoice sort order
$OrderBy = "order by record_num";
$OrderBy = "order by record_num,school_name";
//
// Display only selected invoices
//
$swhere = "";
if (array_key_exists('show',$_GET) && strlen($_GET['show'])) {
$show = $_GET['show'];
$swhere = " and h.status = '" . $_GET['show'] . "' ";
$TitleText = $_GET['show'] . " $TitleText";
if ($show == 'Paid') $OrderBy = "order by payment_date,record_num,school_name";
}
if (isset($selected_school) && $selected_school > 0) {
$swhere .= " and h.school_num = $selected_school ";
$TitleText = "$school_name $TitleText";
}
db_query("UPDATE schools SET balance_owing = 0");
$query = "select school_name,url,school_year,h.record_num + 500," .
"h.invoice_date,h.renewal_date,h.status,concat('$',h.total)," .
"concat('$',h.amount_paid),concat('$',h.total - h.amount_paid),h.school_num," .
"h.record_num,schools.status AS school_status,h.charge_gst " .
"from schools,invoice_header as h " .
"where schools.record_num = h.school_num " .
$swhere .
$OrderBy;
$results = db_query($query);
$data = array();
while ($row = db_fetch_array($results)) {
$school_name = $row['school_name'];
$school_num = $row['school_num'];
$school_status = $row['school_status'];
$status = $row['status'];
$charge_gst = $row['charge_gst'];
$rnum = $row['record_num'];
if ($school_status == 'Deleted' || $school_status == 'Inactive') $school_name = "$school_name";
else {
$month = date("m",time());
if ($month > '07' && $status != 'Paid') {
$discounts = db_getVal("SELECT count(*) FROM invoice_items WHERE school_num = $school_num AND invoice_num = $rnum AND amount < -1 AND description NOT LIKE '%Small%'");
if ($discounts > 0) $school_name = "$school_name";
}
}
$school_name = "$school_name";
$school_num = $row['school_num'];
$info_icon_small = "
";
if (!isset($invCode)) $invCode = '';
$info_icon = "$info_icon_small";
$school_name .= " $info_icon";
$cresults = db_query("SELECT * FROM school_contact_list WHERE school_num = $school_num ORDER BY sequence LIMIT 0,1");
if (db_num_rows($cresults)) {
$crow = db_fetch_array($cresults);
$name = $crow['contact_name'];
$email = $crow['contact_email'];
$school_name .= "
- $name";
}
$balance = db_getVal("SELECT sum(total - amount_paid) FROM invoice_header WHERE school_num = $school_num AND status = 'Open'");
$balance = sprintf("%0.2f",$balance);
db_query("UPDATE schools SET balance_owing = $balance WHERE record_num = $school_num");
$data[] = array($school_name,$row[2],$row[3],$row[4],$row[5],$row[6],$row[7],$row[8],$row[9],"record_num" => $row['record_num']);
}
//
// Get totals for the report
//
$swhere = preg_replace("/^ and /"," where ",$swhere);
$query = "select '','','','','','',sum(h.total) as total,sum(h.amount_paid) as paid,sum(h.total - h.amount_paid) as balance " .
"from invoice_header as h " .
$swhere;
$results2 = db_query($query);
$row = db_fetch_array($results2);
$totals = array('','','','','','','$'.number_format($row['total'],2),'$'.number_format($row['paid'],2),'$'.number_format($row['balance'],2));
// Check for an open batch
$numOpen = db_getVal("SELECT count(*) FROM invoice_payments WHERE batch_num = 0");
if ($numOpen > 0) {
$amount = db_getVal("SELECT sum(amount) FROM invoice_payments WHERE batch_num = 0");
$error_message = "Warning: There is an Open Batch with $numOpen Cheques - $currencySymbol".number_format($amount,2);
include "form.messages.php";
}
$message_width = $default_data_width;
include "form.messages.php";
$print_icon = "
";
$paid_icon = "
";
if (!isset($show)) $show = '';
$commands = array(
"$edit_icon_small",
"$delete_icon_small",
"$paid_icon",
"$email_icon_small",
"$print_icon",
"$view_icon_small",
);
if (isset($selected_school) && $selected_school > 0) $action .= "&selected_school=$selected_school";
if (isset($show)) $action .= "&show=$show";
listDataCustom($data,$columnHeaders,$action,$commands,'Y',$columnAlign,$totals);
echo("Invoice Reminders
\n");
//
// Display totals by year
//
$columnHeaders = array("School Year","Subtotal","GST","Total","Invoices","Average per Invoice");
$columnAlign = array('center','right','right','right','center','right');
$query = "SELECT school_year, sum(subtotal) as subtotal, sum(gst) as gst, sum(total) as total, count(*) as invoices, round(avg(subtotal),2) as average " .
"FROM invoice_header " .
"WHERE status = 'Paid' " .
"GROUP BY 1 ORDER BY 1";
$results = db_query($query);
$data = array();
$count = 0;
$total_subtotal = 0;
$total_gst = 0;
$total_total = 0;
while ($row = db_fetch_array($results)) {
extract($row);
$total_subtotal += $subtotal;
$total_gst += $gst;
$total_total += $total;
$count += $invoices;
$data[] = array($school_year,
"$" . number_format($subtotal,2),
"$" . number_format($gst,2),
"$" . number_format($total,2),
$invoices,
"$" . number_format($average,2));
}
$totals = array('',
'$'.number_format($total_subtotal,2),
'$'.number_format($total_gst,2),
'$'.number_format($total_total,2),
$count,
'$'.number_format($total_subtotal / $count,2));
$default_data_width = 750;
listDataCustom($data,$columnHeaders,'',array(),'N',$columnAlign,$totals);
echo("
\n");
echo("Revenue Report
\n");
//
// Display totals by year
//
$columnHeaders = array("Calendar Year","Subtotal","GST","Total","Invoices","Average per School");
$columnAlign = array('center','right','right','right','center','right');
$query = "SELECT substr(payment_date,1,4) AS year, sum(subtotal) as subtotal, sum(gst) as gst, sum(total) as total, " .
"count(*) as invoices, round(avg(subtotal),2) as average " .
"FROM invoice_header " .
"WHERE status = 'Paid' " .
"GROUP BY 1 ORDER BY 1";
$results = db_query($query);
$data = array();
$count = 0;
$total_subtotal = 0;
$total_gst = 0;
$total_total = 0;
while ($row = db_fetch_array($results)) {
extract($row);
$total_subtotal += $subtotal;
$total_gst += $gst;
$total_total += $total;
$count += $invoices;
$data[] = array($year,
"$" . number_format($subtotal,2),
"$" . number_format($gst,2),
"$" . number_format($total,2),
$invoices,
"$" . number_format($average,2));
}
$totals = array('',
'$'.number_format($total_subtotal,2),
'$'.number_format($total_gst,2),
'$'.number_format($total_total,2),
$count,
'$'.number_format($total_subtotal / $count,2));
$default_data_width = 750;
echo("
\n");
listDataCustom($data,$columnHeaders,'',array(),'N',$columnAlign,$totals);
//
// Get future totals by type
//
$columnHeaders = array("School Configuration","Count","Rate","Annual","Discounted","Annual");
$columnAlign = array('left','center','right','right','right','right');
// $query = "SELECT 'hotlunch' AS code, system_configuration AS category, count(*) as number FROM schools WHERE status = 'Active' AND reseller = 'hotlunch' GROUP BY 1,2 " .
// "UNION " .
// "SELECT 'resellers', reseller, count(*) as number FROM schools WHERE status = 'Active' AND reseller != 'hotlunch' GROUP BY 1,2 " .
// "ORDER BY 1,2";
// $results = db_query($query);
$today = date("Y-m-d",time());
$ResellerCount = array();
$query = "SELECT system_configuration AS category, count(*) as number FROM schools " .
"WHERE status = 'Active' AND reseller = 'hotlunch' AND expiry_date >= '$today' " .
"GROUP BY 1 ORDER BY 1";
$results = db_query($query);
while ($row = db_fetch_assoc($results)) {
extract($row);
$ResellerCount[] = array('code' => 'hotlunch',
'category' => $category,
'number' => $number
);
}
$query = "SELECT reseller, count(*) as number FROM schools WHERE status = 'Active' AND reseller != 'hotlunch' AND expiry_date >= '$today' GROUP BY 1";
$results = db_query($query);
while ($row = db_fetch_assoc($results)) {
extract($row);
$ResellerCount[] = array('code' => 'resellers',
'category' => $reseller,
'number' => $number
);
}
$data = array();
$count = 0;
$total_total = 0;
$disc_total = 0;
// while ($row = db_fetch_assoc($results)) {
for ($i = 0; $i < count($ResellerCount); $i++) {
$row = $ResellerCount[$i];
extract($row);
if ($code == 'hotlunch') {
$rate = 250;
$disc = 225;
if ($category == 'Full') {
$rate = 350;
$disc = 300;
}
$data[] = array($category,$number,'$'.number_format($rate,2),'$'.number_format($number * $rate,2),'$'.number_format($disc,2),'$'.number_format($number*$disc,2));
$total_total += $number * $rate;
$disc_total += $number * $disc;
}
else {
$reseller = $category;
$rate = db_getVal("SELECT annual_rate FROM resellers WHERE reseller_code = '$reseller'");
if ($rate == 0) $rate = 200;
$rate = floatval($rate);
$number = floatval($number);
$data[] = array(strtoupper($category),$number,'$'.number_format($rate,2),'$'.number_format($number * $rate,2),'$'.number_format($rate,2),'$'.number_format($number*$rate,2));
$total_total += $number * $rate;
$disc_total += $number * $rate;
}
$count += $number;
}
$totals = array('',$count,'$'.number_format($total_total / $count,2),'$'.number_format($total_total,2),'$'.number_format($disc_total / $count,2),'$'.number_format($disc_total,2));
$default_data_width = 700;
listDataCustom($data,$columnHeaders,'',array(),'N',$columnAlign,$totals);
echo("
\n");
echo("Revenue Projections Report
\n");
//
// Get future totals by type with revised pricing
//
// $columnHeaders = array("School Configuration","Count","Rate","Annual","Discounted","Annual");
// $columnAlign = array('left','center','right','right','right','right');
// $query = "SELECT 'hotlunch' AS code, system_configuration AS category, count(*) as number FROM schools WHERE status = 'Active' AND reseller = 'hotlunch' GROUP BY 1,2 " .
// "UNION " .
// "SELECT 'resellers', reseller, count(*) as number FROM schools WHERE status = 'Active' AND reseller != 'hotlunch' GROUP BY 1,2 " .
// "ORDER BY 1,2";
// $results = db_query($query);
//
// $data = array();
// $count = 0;
// $total_total = 0;
// $disc_total = 0;
// while ($row = db_fetch_array($results)) {
// extract($row);
//
// if ($code == 'hotlunch') {
// $rate = 300;
// $disc = 250;
// if ($category == 'Full') {
// $rate = 300;
// $disc = 250;
// }
// $data[] = array($category,$number,'$'.number_format($rate,2),'$'.number_format($number * $rate,2),'$'.number_format($disc,2),'$'.number_format($number*$disc,2));
// $total_total += $number * $rate;
// $disc_total += $number * $disc;
// }
// else {
// $reseller = $category;
// $rate = db_getVal("SELECT annual_rate FROM resellers WHERE reseller_code = '$reseller'");
// $data[] = array(strtoupper($category),$number,'$'.number_format($rate,2),'$'.number_format($number * $rate,2),'$'.number_format($rate,2),'$'.number_format($number*$rate,2));
// $total_total += $number * $rate;
// $disc_total += $number * $rate;
// }
//
// $count += $number;
// }
//
// $totals = array('',$count,'$'.number_format($total_total / $count,2),'$'.number_format($total_total,2),'$'.number_format($disc_total / $count,2),'$'.number_format($disc_total,2));
//
// $default_data_width = 700;
// listDataCustom($data,$columnHeaders,'',array(),'N',$columnAlign,$totals);
// echo("
\n");
}
include "schools.options.php";
endTemplate();
?>