<?php
require_once("include/globals.php");

if (SessionActive() != 1 || ! UserLoggedIn()) {
   header("Location: /");
   exit();
}


//
// Process all actions to list and edit records
//

$CurrentTable = "invoice_header";
$DeleteField  = "school_num,school_year,invoice_date";
$dSeparator   = "<br>";
$FormFile     = "invoices.form.php";
$TitleText    = "Invoice";
$OrderBy      = "sequence";
$Sequence     = 'N';

$REP = "AND s.rep = 'R' ";
$REP = "";


// 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("<br>\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";



//
// Display totals by year
//
$columnHeaders = array("School Year","Subtotal","GST","Total","Invoices","Average per School");
$columnAlign   = array('center','right','right','right','center','right');
$query = "SELECT ih.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 AS ih, schools AS s " .
         "WHERE ih.school_num = s.record_num " .
         "AND ih.status = 'Paid' " .
         $REP .
         "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("<br>\n");
echo("<p><a href='revenue.report.php' class='btn btn--primary btn--xsmall'>Revenue Report</a></p>\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";
$query = "SELECT substr(ih.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 AS ih, schools AS s " .
         "WHERE ih.school_num = s.record_num " .
         $REP .
         "AND ih.status = 'Paid' " .
         "AND payment_date >= '2010-07-01' " .
         "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("<br>\n");
listDataCustom($data,$columnHeaders,'',array(),'N',$columnAlign,$totals);


//
// Get future totals by type
//
$today = date("Y-m-d",time());
$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' AND expiry_date >= '$today' " .
         "GROUP BY 1,2 " .
         "UNION " .
         "SELECT 'resellers', reseller, count(*) as number FROM schools " .
         "WHERE status = 'Active' AND reseller != 'hotlunch' AND expiry_date >= '$today' " .
         "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 = 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'");
      $rate = floatval($rate);

      $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("<br>\n");
echo("<p><a href='revenue-projections.report.php' class='btn btn--primary btn--xsmall'>Revenue Projections Report</a></p>\n");


include "schools.options.php";
endTemplate();
?>