151 lines
4.1 KiB
PHP
151 lines
4.1 KiB
PHP
<?php
|
|
include "db.php";
|
|
include "include/functions.php";
|
|
|
|
$syear = 2008;
|
|
$eyear = date("Y",time());
|
|
|
|
$mode = 'all';
|
|
$mode = 'reporting';
|
|
|
|
$where = "";
|
|
$hwhere = "";
|
|
$awhere = "";
|
|
|
|
if ($mode == 'reporting') {
|
|
$syear = 2011;
|
|
$eyear = 2014;
|
|
$where = "AND ih.status != 'Cancelled' ";
|
|
$hwhere = "AND s.rep != 'N' AND (ih.reference IS NULL OR ih.reference = '') ";
|
|
// $hwhere = "AND (ih.reference IS NULL OR ih.reference = '') ";
|
|
$awhere = "AND s.rep != 'N' AND (ih.gst > 0.00 OR ih.billing_currency = 'CAD') ";
|
|
// $awhere = "AND (ih.gst > 0.00 OR ih.billing_currency = 'CAD') ";
|
|
}
|
|
|
|
|
|
$data = array();
|
|
|
|
for ($yr = $syear; $yr <= $eyear; $yr++) {
|
|
$hotlunch = 0;
|
|
$appt = 0;
|
|
$subtotal = 0;
|
|
$tax = 0;
|
|
|
|
// if ($mode == 'reporting' && $yr >= 2012) {
|
|
// $hwhere = "AND (ih.reference IS NULL OR ih.reference = '') ";
|
|
// $awhere = "AND (ih.gst > 0.00 OR ih.billing_currency = 'CAD') ";
|
|
// }
|
|
|
|
if ($mode == 'reporting' && $yr >= 2014) {
|
|
$awhere = "AND (ih.gst > 0.00 OR ih.billing_currency = 'CAD') ";
|
|
$awhere = "";
|
|
}
|
|
|
|
if ($yr >= 2015) {
|
|
$hwhere = "";
|
|
$awhere = "";
|
|
}
|
|
|
|
$smonth = 1;
|
|
if ($yr == 2011 && $mode == 'reporting') $smonth = 3;
|
|
|
|
for ($mo = $smonth; $mo <= 12; $mo++) {
|
|
$month = $yr . '-' . sprintf("%02d",$mo);
|
|
$entry = array();
|
|
|
|
db_query("USE hotlunch");
|
|
|
|
$query = "SELECT substr(invoice_date,1,7), count(ih.record_num), sum(ih.subtotal), sum(ih.gst), sum(ih.total) " .
|
|
"FROM invoice_header AS ih, schools AS s " .
|
|
"WHERE substr(invoice_date,1,7) = '$month' " .
|
|
"AND ih.school_num = s.record_num " .
|
|
"AND s.status = 'Active' " .
|
|
$hwhere .
|
|
$where .
|
|
"GROUP BY 1 ORDER BY 1";
|
|
$results = db_query($query);
|
|
|
|
if (db_num_rows($results)) {
|
|
$row = db_fetch_array($results);
|
|
$entry[] = $month;
|
|
$entry[] = $row[1];
|
|
$entry[] = '$'.number_format($row[2],2);
|
|
$entry[] = '$'.number_format($row[3],2);
|
|
$total = $row[2] + $row[3];
|
|
$entry[] = '$'.number_format($total,2);
|
|
$hotlunch += $total;
|
|
$subtotal += $row[2];
|
|
$tax += $row[3];
|
|
}
|
|
else {
|
|
$entry[] = $month;
|
|
$entry[] = '';
|
|
$entry[] = '';
|
|
$entry[] = '';
|
|
$entry[] = '';
|
|
}
|
|
|
|
if ($mo == 12) $entry[] = '$'.number_format($hotlunch,2);
|
|
else $entry[] = '';
|
|
|
|
$entry[] = '';
|
|
|
|
db_query("USE schoolappointments");
|
|
|
|
$query = "SELECT substr(invoice_date,1,7), count(ih.record_num), sum(ih.subtotal), sum(ih.gst), sum(ih.total) " .
|
|
"FROM invoice_header AS ih, schools AS s " .
|
|
"WHERE substr(invoice_date,1,7) = '$month' " .
|
|
"AND ih.school_num = s.record_num " .
|
|
"AND s.status = 'Active' " .
|
|
$awhere .
|
|
$where .
|
|
"GROUP BY 1 ORDER BY 1";
|
|
$results = db_query($query);
|
|
|
|
if (db_num_rows($results)) {
|
|
$row = db_fetch_array($results);
|
|
$entry[] = $month;
|
|
$entry[] = $row[1];
|
|
$entry[] = '$'.number_format($row[2],2);
|
|
$entry[] = '$'.number_format($row[3],2);
|
|
$total = $row[2] + $row[3];
|
|
$entry[] = '$'.number_format($total,2);
|
|
$appt += $total;
|
|
$subtotal += $row[2];
|
|
$tax += $row[3];
|
|
}
|
|
else {
|
|
$entry[] = $month;
|
|
$entry[] = '';
|
|
$entry[] = '';
|
|
$entry[] = '';
|
|
$entry[] = '';
|
|
}
|
|
|
|
if ($mo == 12) $entry[] = '$'.number_format($appt,2);
|
|
else $entry[] = '';
|
|
|
|
$entry[] = '';
|
|
|
|
if ($mo == 12) {
|
|
$entry[] = '$'.number_format($subtotal,2);
|
|
$entry[] = '$'.number_format($tax,2);
|
|
$entry[] = '$'.number_format($hotlunch + $appt,2);
|
|
}
|
|
else {
|
|
$entry[] = '';
|
|
$entry[] = '';
|
|
$entry[] = '';
|
|
}
|
|
|
|
$data[] = $entry;
|
|
}
|
|
$data[] = array();
|
|
}
|
|
|
|
$column_headers = array('Month','Count','Subtotal','Tax','Total','Annual','','Month','Count','Subtotal','Tax','Total','Annual','','Subtotal','Tax','Total');
|
|
$fname = genFileName("invoices.csv");
|
|
xlsData($data,$column_headers,$fname);
|
|
|
|
?>
|