<?php

include "db.php";
include "include/functions.php";


$pYr = date("Y",time()) - 1;
$cYr = date("Y",time());
$nYr = $cYr + 1;
$cMo = date("m",time());

if ($cMo > '07') $renewal = $nYr . "-07-31";
else $renewal = $cYr . "-07-31";


$Resellers = array();
$query = "SELECT * FROM resellers";
$results = db_query($query);
while ($row = db_fetch_assoc($results)) {
   $business_name = $row['business_name'];
   $reseller_code = $row['reseller_code'];
   $Resellers[$reseller_code] = $business_name;
}

$query = "SELECT * FROM schools WHERE province = 'NB' and status = 'Active' AND url NOT LIKE '%fundraiserorders.com' ORDER BY school_name";
$schools = db_query($query);

$data = array();

while ($row = db_fetch_assoc($schools)) {
   $reseller = '';
   if ($row['reseller'] != 'hotlunch') $reseller = $Resellers[$row['reseller']];
   $school_num = $row['record_num'];

   $entry = array();
   $entry[] = $row['school_name'];
   $entry[] = $row['city'];
   $entry[] = $row['url'];
   $entry[] = $reseller;
   $entry[] = $row['expiry_date'];

   $expiry_date = $row['expiry_date'];
   $url = $row['url'];
   $query = "SELECT * FROM nb_schools WHERE url = '$url'";
   $results = db_query($query);

   if (db_num_rows($results)) {
      $r = db_fetch_assoc($results);
      $entry[] = $r['school_code'];
      $entry[] = $r['upload_date'];
      // $entry[] = $r['num_staff'];
      // $entry[] = $r['num_classes'];
      // $entry[] = $r['num_students'];
   }
   else {
      $entry[] = '';
      $entry[] = '';
   }

   $query = "SELECT count(*) FROM people WHERE school_num = $school_num AND people_type = 'Staff'";
   $nStaff = db_getVal($query);
   $entry[] = $nStaff;

   $query = "SELECT count(*) FROM classes WHERE school_num = $school_num";
   $nClasses = db_getVal($query);
   $entry[] = $nClasses;

   $query = "SELECT count(*) FROM people WHERE school_num = $school_num AND people_type = 'Student' AND class_num > 0";
   $nStudents = db_getVal($query);
   $entry[] = $nStudents;

   $flag = '';
   if ($expiry_date < $renewal) {
      $flag = '****';
      $entry[] = $flag;
      // $entry[] = 200;
   }
   else {
      $entry[] = $flag;
      // $entry[] = '';
   }

   $data[] = $entry;
}

$headers = array('School','City','URL','Caterer','Expiry','School Code','Staff Date','Staff','Classes','Students','');
xlsData($data, $headers, 'schools.csv');

?>