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

?>