<?php
require_once("include/globals.php");

if (SessionActive() != 1 || ! UserLoggedIn()) {
   header("Location: /?error_message=" . urlencode(getLanguageText('ERRORMSG-SESSION-EXPIRED')));
   exit();
}


//
// Process all actions to list and edit records
//
$naction = "";
if ($action == 'schools-search') $naction = 'search';
if ($action == 'schools-add')    $naction = 'add';

$CurrentTable = "schools";
$DeleteField  = "school_name";
$FormFile     = "school.form.php";
$OrderBy      = "school_name";


// Start the template and output buffering
startTemplate();
$templatePageTitle = getLanguageText('TEXT-SCHOOL-ADMINISTRATION');


// Ensure that only valid user types have access to this module
$AllowedUserTypes = array('SuperUser');
if (checkPermission($AllowedUserTypes) != 1) {
   $error_message = getLanguageText('ERRORMSG-ACCESS-DENIED') . ": $action";
   header("Location: /?error_message=" . urlencode($error_message));
   exit();
}


if (array_key_exists('naction',$_GET)) $naction = $_GET['naction'];
if (array_key_exists('naction',$_POST)) $naction = $_POST['naction'];

if (array_key_exists('record_num',$_GET)) $record_num = intval($_GET['record_num']);
if (array_key_exists('record_num',$_POST)) $record_num = intval($_POST['record_num']);

include "schools.options.php";

$message_width = $default_data_width;
include "form.messages.php";


switch (strtolower($naction)) {
   case "create-link":
      $PageTitle = "Create a Linked School Site";
      $ButtonText = "Create Linked School";
      $next_naction = "insert";

      // Get current record
      $record_num = intval($_GET['record_num']);
      $query = "select * from $CurrentTable where record_num = $record_num";
      $results = db_query($query);
      if (db_num_rows($results) == 1) {
         $school_record = db_fetch_assoc($results);
         $school_record['status'] = 'Linked';
         $school_record['linked_site'] = $record_num;
         $school_record['system_configuration'] = 'Standard';
         $school_record['email_system'] = 'N';
         $school_record['other_modules'] = 'N';
         $school_record['event_calendar'] = 'N';
         $school_record['classified_ads'] = 'N';
         $school_record['photo_gallery'] = 'N';
         $school_record['email_processing'] = 'N';
         $school_record['enable_parent_registration'] = 'N';
         $school_record['registrations_permitted'] = 'None';
         $school_record['record_num'] = 0;
         include $FormFile;
      }
      else {
         $error_message = "Error: Record Not Found";
      }
      break;

   case "insert":
      extract($_POST);
      $parameters = $_POST;

      // startLog("schools - insert");

      $table = new Table("$CurrentTable");
      $query = $table->BuildInsert($parameters);
      writeLog($query);
      db_query($query);

      // Create other entries for a new school
      $school_num = db_insert_id();
      $new_school_num = $school_num;
      writeLog("school_num: $school_num");

      // Insert staff categories
      include "school-setup.php";
      writeLog("school-setup: complete");

      $form_message = getLanguageText('RECORD-INSERTED-MSG');
      $naction = '';
      header("Location: /?action=$action&naction=$naction&form_message=" . urlencode($form_message) . "&error_message=" . urlencode($error_message));
      exit();

   case "edit":
      $next_naction = "update";

      // Get current record
      $record_num = intval($_GET['record_num']);
      $query = "select * from $CurrentTable where record_num = $record_num";
      $results = db_query($query);
      if (db_num_rows($results) == 1) {
         $school_record = db_fetch_array($results);

         // Check for a duplicate URL
         $url = $school_record['url'];
         $rnum = $school_record['record_num'];
         $url = db_escape_string($url);
         $results = db_query("SELECT * FROM schools WHERE url = '$url' AND status IN ('Active','Special')");
         if (db_num_rows($results) > 0) {
            $error_message = "ERROR: Duplicate URL - $url";
            header("Location: /?action=schools&naction=list&show=Pending&error_message=" . urlencode($error_message));
            exit();
         }

         include $FormFile;
      }
      else {
         $error_message = getLanguageText('ERRORMSG-NOT-FOUND');
         $naction = '';
         header("Location: /?action=$action&naction=$naction&form_message=" . urlencode($form_message) . "&error_message=" . urlencode($error_message));
         exit();
      }
      break;

   case "update":
      extract($_POST);
      $parameters = $_POST;

      $pr = db_query("SELECT * FROM schools WHERE record_num = $record_num");
      $prev_record = db_fetch_assoc($pr);

      // Get school status before update
      if ($status == 'Pending Edit') {
         $parameters['status'] = 'Pending';
      }
      else {
         $prev_status = $prev_record['status'];
         if ($prev_status == 'Pending' && $status == 'Pending') $parameters['status'] = 'Active';
      }

      // if ($prev_record['implement_meal_locations'] == 'N' && $implement_meal_locations == 'Y')
            // db_query("UPDATE people SET meal_location = class_num WHERE school_num = $base_school_num AND people_type IN ('Student','Customer','Staff')");

      if ($prev_record['special_item_prices'] == 'Y' && $special_item_prices == 'N') {
         $query = "UPDATE classes SET default_price_category = 'Regular' WHERE school_num = $record_num";
         db_query($query);

         $query = "UPDATE people SET menu_price_category = 'Regular' WHERE school_num = $record_num";
         db_query($query);
      }


      // Handle some configuration defaults
      if ($participants_are != 'Students') {
         $parameters['display_staff_list'] = 'N';
         $parameters['display_class_lists'] = 'N';
         $parameters['display_pac_info'] = 'N';
      }

      if ($orders_for == 'Registrant') {
         $parameters['registrations_permitted'] = 'Guest';
         $parameters['allow_parent_login'] = 'N';
         $parameters['allow_guest_login'] = 'Y';
      }


      // Reformat entered phone numbers
      $phone = trim($phone);
      $contact_phone = trim($contact_phone);
      $contact2_phone = trim($contact2_phone);
      $bill_to_phone = trim($bill_to_phone);

      if (strlen($phone) > 0) {
         $tphone = preg_replace("/[^0-9]/","",$phone);
         if (strlen($tphone) == 10) {
            $phone = "(" . substr($tphone,0,3) . ")" . substr($tphone,3,3) . "-" . substr($tphone,6);
         }
         $parameters['phone'] = $phone;
      }

      if (strlen($contact_phone) > 0) {
         $tphone = preg_replace("/[^0-9]/","",$contact_phone);
         if (strlen($tphone) == 10) {
            $contact_phone = "(" . substr($tphone,0,3) . ")" . substr($tphone,3,3) . "-" . substr($tphone,6);
         }
         $parameters['contact_phone'] = $contact_phone;
      }

      if (strlen($contact2_phone) > 0) {
         $tphone = preg_replace("/[^0-9]/","",$contact2_phone);
         if (strlen($tphone) == 10) {
            $contact2_phone = "(" . substr($tphone,0,3) . ")" . substr($tphone,3,3) . "-" . substr($tphone,6);
         }
         $parameters['contact2_phone'] = $contact2_phone;
      }

      if (strlen($bill_to_phone) > 0) {
         $tphone = preg_replace("/[^0-9]/","",$bill_to_phone);
         if (strlen($tphone) == 10) {
            $bill_to_phone = "(" . substr($tphone,0,3) . ")" . substr($tphone,3,3) . "-" . substr($tphone,6);
         }
         $parameters['bill_to_phone'] = $bill_to_phone;
      }

      $table = new Table("$CurrentTable");
      $query = $table->BuildUpdate($parameters);
      db_query($query);
      $form_message = getLanguageText('RECORD-UPDATED-MSG');

      if ($prev_status == 'Pending' && $status == 'Active') {
         header("Location: /?action=$action&naction=send-notification&record_num=$record_num&show_next=Pending");
      }
      else {
         $naction = '';
         header("Location: /?action=$action&naction=$naction&form_message=" . urlencode($form_message) . "&error_message=" . urlencode($error_message));
      }
      exit();

   case "delete":
      $record_num = intval($_GET['record_num']);

      $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 = getLanguageText('TEXT-DELETE-ENTRY');
         for ($i = 0; $i < count($dFields); $i++) {
            $dTitle .= $row[$dFields[$i]] . $dSeparator;
         }

         include "delete.form.php";
      }
      else {
         $error_message = getLanguageText('ERRORMSG-NOT-FOUND');
         $naction = '';
         header("Location: /?action=$action&naction=$naction&form_message=" . urlencode($form_message) . "&error_message=" . urlencode($error_message));
         exit();
      }
      break;

   case "delete-confirmed":
      $record_num = intval($_GET['record_num']);
      $show = "";
      if (array_key_exists('show',$_GET)) $show = $_GET['show'];

      $status = db_getVal("SELECT status FROM schools WHERE record_num = $record_num");

      if ($status == 'Deleted') {
         // Delete the school
         db_query("DELETE FROM schools WHERE record_num = $record_num");
         db_query("DELETE FROM users WHERE school_num = $record_num");
         db_query("DELETE FROM user_account WHERE school_num = $record_num");
         db_query("DELETE FROM people WHERE school_num = $record_num");
         // db_query("DELETE FROM staff WHERE school_num = $record_num");
         db_query("DELETE FROM hotlunch_schedules WHERE school_num = $record_num");
         db_query("DELETE FROM hotlunch_menu_dates WHERE school_num = $record_num");
         db_query("DELETE FROM hotlunch_menu_dates_classes WHERE school_num = $record_num");
         db_query("DELETE FROM hotlunch_menu_items WHERE school_num = $record_num");
         db_query("DELETE FROM hotlunch_order_header WHERE school_num = $record_num");
         db_query("DELETE FROM hotlunch_order_items_save WHERE school_num = $record_num");
         db_query("DELETE FROM hotlunch_schedules WHERE school_num = $record_num");
         db_query("DELETE FROM documents WHERE school_num = $record_num");
         db_query("DELETE FROM formatted_content WHERE school_num = $record_num");
         db_query("DELETE FROM activity_log WHERE school_num = $record_num");
         db_query("DELETE FROM login_log WHERE school_num = $record_num");
      }
      else {
         // Delete the current record
         $query = "UPDATE $CurrentTable SET status = 'Deleted' WHERE record_num = $record_num";
         db_query($query);
      }

      $form_message = getLanguageText('RECORD-DELETED-MSG');
      $naction = 'list';
      header("Location: /?action=$action&naction=$naction&show=$show&form_message=" . urlencode($form_message));
      exit();

   case "link":
      include "school.link.form.php";
      break;

   case "send-notification":
      // Send notification email to hot lunch contact
      $record_num = intval($_GET['record_num']);
      $school_num = $record_num;

      // Get selected school record
      $results = db_query("select * from schools where record_num = $record_num");
      $row = db_fetch_array($results);
      extract($row);

      $notification = array();
      $notification['notification_date'] = date("Y-m-d H:i:s",time());
      $notification['school_num'] = $school_num;

      // Get first school contact
      include "include/queuemail.php";
      $results = db_query("SELECT * FROM school_contact_list WHERE school_num = $school_num ORDER BY sequence");
      if (db_num_rows($results)) {
         while ($row = db_fetch_array($results)) {
            extract($row);

            // Get the admin password
            $password = db_getVal("SELECT password FROM users WHERE school_num = $base_school_num AND userid = 'admin'");
            if ($password == "admin") $password .= "   (please change this)";

            $emailMessage = "Hello $contact_name,\n" .
                     "\n" .
                     "Welcome to the HotLunches.net On-line System.  Your school has been setup in the system and an initial\n" .
                     "administrative user has been created so that you can login and start using the system and of course\n" .
                     "customize the welcome page, the color scheme and background image.\n" .
                     "\n" .
                     "You have 2 months to trial the system to evaluate if it meets your needs. We will provide full support for\n" .
                     "duration of your trial. Please reference the <b>HELP</b> menu for the <b>How to Guides</b> and to note our <b>Support Policy</b>.\n" .
                     "\n" .
                     "Organization Name :: $school_name\n" .
                     "Address           :: $address, $city, $province  $postal\n" .
                     "\n" .
                     "Organization      :: $pac_name\n" .
                     "\n" .
                     "Contact Person    :: $contact_name\n" .
                     "Email Address     :: $contact_email\n" .
                     "Contact Phone     :: $contact_phone\n" .
                     "\n" .
                     "Application URL   :: <a href='http://$url'>http://$url</a>\n" .
                     "Admin User ID     :: admin\n" .
                     "Password          :: $password\n" .
                     "Access Code       :: $system_access_code\n" .
                     "Expiry Date       :: $expiry_date\n" .
                     "\n" .
                     "Do not hesitate to send me an email with any questions or suggestions that you may have.\n" .
                     "\n" .
                     "Regards\n" .
                     "\n" .
                     "Hot Lunches On-Line\n" .
                     "(604)996-7181\n" .
                     "1-888-9HOTLUNCH  (1-888-946-8586)\n" .
                     "<a href='mailto:support@hotlunches.net'>support@hotlunches.net</a>\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 Registration Complete for $school_name";
            $emailMessage   = nl2br($emailMessage);

            $attachments = array("docs/Sample Hot Lunch Parent Letter.doc","how-to-guides/Setup Checklist.pdf");

            // Send the message
            QueueEmail($emailTo, $emailFrom, $emailSubject, $emailMessage, $emailReply, $emailRecipient, $attachments, "email-template.lunches.html");
            $notification['notification_name1'] = $emailRecipient;
            $notification['notification_email1'] = $emailTo;

            $form_message .= "Email Has Been Sent to $contact_name<br>";
         }

         // Send a copy to me
         $emailRecipient = "Hot Lunches Online";
         $emailTo        = "support@hotlunches.net";
         $attachments    = array();
         QueueEmail($emailTo, $emailFrom, $emailSubject, $emailMessage, $emailReply, $emailRecipient, $attachments, "email-template.lunches.html");

         $school_notifications = new Table("school_notifications");
         $query = $school_notifications->BuildInsert($notification);
         db_query($query);

      }
      else {
         $error_message = "Unable to Send Email";
      }

      $show = "";
      if (array_key_exists('show_next',$_GET) && strlen($_GET['show_next'])) $show = $_GET['show_next'];

      $naction = '';
      if (!isset($error_message)) $error_message = '';
      header("Location: /?action=$action&naction=$naction&show=$show&form_message=" . urlencode($form_message) . "&error_message=" . urlencode($error_message));
      exit();

   case "search":
      $PageTitle = getLanguageText('TEXT-SEARCH');
      $ButtonText = getLanguageText('TEXT-SEARCH');
      $ChoosePagination = 'Y';
      $next_naction = "lookup";

      include $FormFile;
      break;

   case "lookup":
      extract($_POST);
      $table = new Table("$CurrentTable");
      $lookup_query = $table->BuildQuery();
      $lookup_where = "";
      if (stripos($lookup_query,"where ")) $lookup_where = preg_replace("/.* where/i","where",$lookup_query);
      if (stripos($lookup_where,"order by ")) $lookup_where = preg_replace("/ order by .*/i","",$lookup_where);
      $naction = "list";
      break;

   case "fetch":
      $lookup_where = "where school.record_num = $record_num";
      $naction = "list";
      break;

   default:
      break;
}


//
// List all records if naction is "list"
//
if ($naction == "list") {


   unset($reseller);

   $today = date("Y-m-d",time());
   $days60 = date("Y-m-d",time() + 60 * 60 * 24 * 65);
   $swhere = " and s.status in ('Pending','Trial','Active') ";
   if (array_key_exists('show',$_GET) && $_GET['show'] == 'All') $swhere = "";
   if (array_key_exists('show',$_GET) && $_GET['show'] == 'Pending') $swhere = " and s.status = 'Pending' ";
   if (array_key_exists('show',$_GET) && $_GET['show'] == 'Trial') $swhere = " and s.status = 'Trial' ";
   if (array_key_exists('show',$_GET) && $_GET['show'] == 'Inactive') $swhere = " and s.status = 'Inactive' ";
   if (array_key_exists('show',$_GET) && $_GET['show'] == 'Special') $swhere = " and s.status = 'Special' ";
   if (array_key_exists('show',$_GET) && $_GET['show'] == 'Linked') $swhere = " and s.status = 'Linked' ";
   if (array_key_exists('show',$_GET) && $_GET['show'] == 'PayPal') $swhere = " and s.paypal_enabled = 'Y' and s.status = 'Active' ";
   if (array_key_exists('show',$_GET) && $_GET['show'] == 'Bambora') $swhere = " and s.bambora_enabled = 'Y' and s.status = 'Active' ";
   if (array_key_exists('show',$_GET) && $_GET['show'] == 'Deleted') {
      $swhere = " and s.status = 'Deleted' ";
      $OrderByOverride = "expiry_date DESC, school_name";
   }
   if (array_key_exists('show',$_GET) && $_GET['show'] == 'Expiring') {
      $swhere = " and expiry_date <= '$days60' and s.status = 'Active' ";
      $OrderByOverride = "expiry_date, school_name";
   }
   if (array_key_exists('show',$_GET) && $_GET['show'] == 'Expired') $swhere = " and ((expiry_date < '$today' and s.status = 'Active') or s.status in ('Inactive','Deleted')) ";
   if (array_key_exists('show',$_GET) && $_GET['show'] == 'Basic') $swhere = " and system_configuration IN ('Basic','Standard') and s.status in ('Pending','Trial','Active') ";
   if (array_key_exists('show',$_GET) && $_GET['show'] == 'Full') $swhere = " and system_configuration = 'Full' and s.status in ('Pending','Trial','Active') ";
   if (array_key_exists('show',$_GET)) $action .= "&show=".$_GET['show'];

   if (array_key_exists('first_letter',$_GET) && strlen($_GET['first_letter'])) $swhere .= " AND school_name LIKE '" . $_GET['first_letter'] . "%' AND s.status = 'Active' ";
   if (array_key_exists('start_year',$_GET)) $swhere .= " and substr(start_date,1,4) = '" . $_GET['start_year'] . "' ";

   if (array_key_exists('reseller',$_GET) && strlen($_GET['reseller']) > 0) {
      $reseller = $_GET['reseller'];
      if ($reseller == 'hotlunch') $swhere = " and reseller = '$reseller' AND s.status IN ('Active') ";
      else $swhere = " and reseller = '$reseller' AND s.status IN ('Active','Linked','Special') ";
   }

   if (array_key_exists('sortby',$_GET) && strlen($_GET['sortby']) > 0) {
      switch ($_GET['sortby']) {
         case 'date':
            $OrderBy = "start_date desc, school_name";
            break;

         case 'expirydate':
            $OrderBy = "expiry_date, school_name";
            break;
      }
   }

   if (isset($OrderByOverride)) $OrderBy = $OrderByOverride;

   if (!isset($lookup_where) || strlen($lookup_where) == 0) $swhere = preg_replace("/^ and/i"," where",$swhere);
   if (!isset($lookup_where)) $lookup_where = '';

   if (array_key_exists('prov',$_GET)) {
      $swhere .= " and province = '".$_GET['prov']."' ";
      $sTitle = $_GET['prov']." ";
      $slink = "&prov=".$_GET['prov'];
   }


   $setup_icon      = "<img src='images/icons/wizard24.png' alt='Setup School' border='0'>";
   $email_icon      = "<img src='images/icons/mailsend24.png' alt='Send Account Email' border='0'>";
   $agreement_icon  = "<img src='images/icons/textfile24.png' alt='Software License Agreement' border='0'>";
   $invoice_icon    = "<img src='images/icons/money24.png' alt='School Invoices' border='0'>";
   $add_icon        = "<img src='images/icons/add24.png' alt='Create Linked Site' border='0'>";
   $toggle_icon     = "<img src='images/icons/key24.png' alt='Toggle Reporting' border='0'>";
   $info_icon       = "<img src='images/icons/infoabout24.png' alt='School Info' border='0'>";

   $commands = array(
               "<a href='#' class='edit-school' rnum='%d'>$edit_icon</a>",
               "<a href='#' class='school-setup' rnum=%d' title='School Setup'>$setup_icon</a>",
               "<a href='?action=$action&naction=send-notification&record_num=%d' title='Send Account Notification Email'>$email_icon</a>",
               "<a href='?action=invoices&selected_school=%d' title='School Invoices'>$invoice_icon</a>",
               "<a href='?action=schools-list&naction=create-link&record_num=%d' title='Create Linked Site'>$add_icon</a>",
               "<a href='#' class='show-school-info' snum='%d' title='Show School Info'>$info_icon</a>",
                  );

   // Determine max renewal year in system
   // $maxRenewal = db_getVal("SELECT max(school_year) FROM invoice_header");
   $maxRenewal = "2016/2017";
   $NoInvoice = 0;

   // Get records to display
   $columnHeaders = array("School","Contact","Cfg","Expires","Days","Inv","Last");
   $columnAlign   = array('left','left','center','center','center','center','center');
   $query = "select school_name,system_configuration,expiry_date,datediff(expiry_date,curdate()),count(i.record_num),max(school_year)," .
               "address,city,province,url,principal,principal_email,phone,type,s.status,rep,email_system,start_date,expiry_date," .
               "renewal_contact,testimonial," .
               "paypal_enabled,system_access_code,document_path,timezone,s.display,s.reseller,s.record_num " .
            "from schools as s left join invoice_header as i on s.record_num = i.school_num AND i.status != 'Cancelled' " .
            $lookup_where . $swhere .
            "group by 1,2,3,4,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28 " .
            "order by $OrderBy";
   $results = db_query($query);
   // echo("$query<br><br>");

   $otipSpans = "";
   $data = array();

   while ($row = db_fetch_array($results)) {
      extract($row);
      $snum  = $row['record_num'];
      $sname = $row['school_name'];

      // Create otip for school name
      $span_id = "school$snum";
      $otipSpans .= "<span id='$span_id'>" . $otipTABLE .
                    sprintf($otipROW2,"School: ","<nobr>$sname</nobr>") .
                    sprintf($otipROW2,"Principal: ","<nobr>$principal</nobr>") .
                    sprintf($otipROW2,"Phone: ",$phone) .
                    sprintf($otipROW2,"Type: ",$type) .
                    sprintf($otipROW2,"URL: ",$url) .
                    sprintf($otipROW2,"Status: ",$status) .
                    sprintf($otipROW2,"Display: ",$display) .
                    sprintf($otipROW2,"Start Date: ",$start_date) .
                    sprintf($otipROW2,"Expiry Date: ",$expiry_date) .
                    sprintf($otipROW2,"PayPal: ",$paypal_enabled) .
                    sprintf($otipROW2,"Access Code: ",$system_access_code) .
                    sprintf($otipROW2,"Time Zone: ",$timezone);

      // Get admin account info
      $apassword = db_getVal("SELECT password FROM users WHERE school_num = $snum AND userid = 'admin'");
      if ($apassword) $otipSpans .= sprintf($otipROW2,"admin: ",$apassword);

      $otipSpans .= sprintf($otipROW2,"Doc Path: ",$document_path) .
                    sprintf($otipROW2,"School Num: ",$record_num);

      $cutoff = date("Y-m-d",time() - 60 * 60 * 24 * 90);
      $orders = db_getVal("SELECT count(*) FROM hotlunch_order_header WHERE school_num = $snum AND ordered_by_type != 'Concession' AND added_date > '$cutoff'");
      $otipSpans .= sprintf($otipROW2,"Orders: ",$orders);

      $rinfo = "";
      if ($reseller != "hotlunch") $rinfo = " <span class='reseller'>(" . strtoupper($reseller) . ")</span>";

      // Complete otip table
      $otipSpans .= "</table></span>\n";
      $school_name = "<b><a href='http://$url' onmouseover=\"TagToTip('$span_id');\" onmouseout=\"UnTip()\" target='_blank'>$sname</a></b>$rinfo<br>$address, $city, $province ($snum)";

      // Create otip for the school contacts
      $contacts = "";
      $cresults = db_query("SELECT * FROM school_contact_list WHERE school_num = $snum AND active = 'Y' ORDER BY sequence");
      if (db_num_rows($cresults)) {
         while ($crow = db_fetch_array($cresults)) {
            $span_id = 'contact' . $crow['record_num'];
            $otipSpans .= "<span id='$span_id'>" . $otipTABLE .
                           sprintf($otipROW2,"Name: ",$crow['contact_name']) .
                           sprintf($otipROW2,"Email: ",$crow['contact_email']) .
                           sprintf($otipROW2,"Phone: ",$crow['contact_phone']);
            if (strlen($crow['contact_cell'])) $otipSpans .= sprintf($otipROW2,"Cell: ",$crow['contact_cell']);
            $otipSpans .= "</table></span>\n";

            if (strlen($crow['contact_email']) == 0) {
               $contacts .= "<span class='red'><a href='mailto:" . $crow['contact_email'] . "' onmouseover=\"TagToTip('$span_id');\" onmouseout=\"UnTip()\">" . $crow['contact_name'] . "</a></span><br>\n";
            }
            else {
               $contacts .= "<a href='mailto:" . $crow['contact_email'] . "' onmouseover=\"TagToTip('$span_id');\" onmouseout=\"UnTip()\">" . $crow['contact_name'] . "</a><br>\n";
            }
         }
      }
      else {
         $contacts = "<span class='red'>&nbsp;&nbsp;&nbsp;&nbsp;</span>";
      }

      $maxInvoiceYear = $row[5];
      if ($maxInvoiceYear == '' || $maxInvoiceYear < $maxRenewal) $NoInvoice++;
      if ($maxInvoiceYear == '') $maxInvoiceYear = "<span class='red'>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span>";
      if ($maxInvoiceYear < $maxRenewal) $maxInvoiceYear = "<span class='red'>$maxInvoiceYear</span>";

      $iresults = db_query("SELECT * FROM invoice_header WHERE school_num = $snum ORDER BY invoice_date DESC");
      if (db_num_rows($iresults)) {
         $span_id = 'invoices_' . $snum;
         $otipSpans .= "<span id='$span_id'>" . $otipTABLE;
         while ($irow = db_fetch_assoc($iresults)) {
            $otipSpans .= sprintf($otipROW2,$irow['school_year'],$irow['invoice_date'].': '.$currencySymbol.$irow['total'].' ('.$irow['status'].')');
         }
         $otipSpans .= "</table></span>\n";
         $maxInvoiceYear = "<a href='' onmouseover=\"TagToTip('$span_id');\" onmouseout=\"UnTip()\">$maxInvoiceYear</a>";
      }

      $contactFlags = "<br><b>$renewal_contact</b>";

      $num_linked = db_getVal("SELECT count(*) FROM schools WHERE linked_site = $snum AND status = 'Linked'");
      // if ($system_configuration == 'Standard' && $email_system == 'Y') $system_configuration = "<span class='red'>$system_configuration</span>";
      $class = 'configuration';
      if ($system_configuration == 'Standard' && $email_system == 'Y') $class .= " red";
      $cfg = "<span class='$class' id='config-$record_num' config='$system_configuration' status='$status' snum='$record_num'>" . $system_configuration . "</span>" .
             "-<br>\n($num_linked)";
      $cfg .= "<br><span class='school-status' id='school-status-$record_num' config='$system_configuration' status='$status' snum='$record_num'>$status</span>";
      $days = $row[3];
      $expires = "<span id='ed-$snum' class='expiry-date' snum='$snum' edate='" . $row[2] . "'>" . $row[2] . "</span><br>($orders)";
      if ($days <= 0) $school_name = "<span class='warning'>$school_name</span>";
      if ($days > 30 && $days <= 90) $days = "<span class='warning'>&nbsp;&nbsp;$days&nbsp;&nbsp;</span>";
      else {
         if ($days <= 30) $days = "<span class='red'>&nbsp;&nbsp;$days&nbsp;&nbsp;</span>";
      }

      // Check the URL
      $num = db_getVal("SELECT count(*) FROM schools WHERE url = '$url' AND status IN ('Active','Special','Pending')");
      $nclass = '';
      if ($num > 1) $school_name = "<span class='error'>DUPLICATE</span> $school_name";

      $data[] = array($school_name,$contacts,$cfg,$expires,$days,$row[4],$maxInvoiceYear.$contactFlags,'record_num' => $snum);
   }


   echo("<center>\n");
   if (isset($page_links) && strlen($page_links) > 0) echo("<p>$page_links</p>\n");
   echo("</center>\n");

   listDataCustom($data,$columnHeaders,$action,$commands,'Y',$columnAlign);
   echo("$NoInvoice");

   if (isset($page_links) && strlen($page_links) > 0) {
      echo("<center>\n");
      echo("<p>$page_links</p>\n");
      echo("</center>\n");
   }
   echo("</center>\n");

   if (isset($reseller) && strlen($reseller) > 0) {
      echo("<center>\n");
      echo("<p><a href='#' id='update-reseller-renewal' rsid='$reseller' class='btn btn--primary>'>Update Reseller Renewal Date</a></p>\n");
      echo("</center>\n");
   }

   include "schools.options.php";
}

echo("\n<!-- \n$query\n-->\n");

$today = date("Y-m-d",time());
// $PayPal1 = db_getVal("SELECT count(*) FROM schools WHERE status = 'Active' AND paypal_enabled = 'Y' AND reseller = 'hotlunch'");
// $PayPal2 = db_getVal("SELECT count(*) FROM schools WHERE status = 'Active' AND paypal_enabled = 'Y' AND reseller != 'hotlunch'");
// $Bambora1 = db_getVal("SELECT count(*) FROM schools WHERE status = 'Active' AND bambora_enabled = 'Y' AND reseller = 'hotlunch'");
// $Bambora2 = db_getVal("SELECT count(*) FROM schools WHERE status = 'Active' AND bambora_enabled = 'Y' AND reseller != 'hotlunch'");

$PayPal1 = db_getVal("SELECT count(*) FROM schools WHERE status = 'Active' AND paypal_enabled = 'Y' AND reseller = 'hotlunch' AND expiry_date > '$today'");
$PayPal2 = db_getVal("SELECT count(*) FROM schools WHERE status = 'Active' AND paypal_enabled = 'Y' AND reseller != 'hotlunch' AND expiry_date > '$today'");
$Bambora1 = db_getVal("SELECT count(*) FROM schools WHERE status = 'Active' AND bambora_enabled = 'Y' AND reseller = 'hotlunch' AND expiry_date > '$today'");
$Bambora2 = db_getVal("SELECT count(*) FROM schools WHERE status = 'Active' AND bambora_enabled = 'Y' AND reseller != 'hotlunch' AND expiry_date > '$today'");
?>
 <br>
 <center>
 <table cellpadding='5' width='50%'>
  <tr class='data-even'>
   <td></td>
   <td align='center'><b>Hot Lunches</b></td>
   <td align='center'><b>Other Resellers</b></td>
  </tr>
  <tr>
   <td class='data-odd'>PayPal</td>
   <td align='center' class='data-odd'><?php echo($PayPal1) ?></td>
   <td align='center' class='data-odd'><?php echo($PayPal2) ?></td>
  </tr>
  <tr>
   <td class='data-odd'>Bambora</td>
   <td align='center' class='data-odd'><?php echo($Bambora1) ?></td>
   <td align='center' class='data-odd'><?php echo($Bambora2) ?></td>
  </tr>
 </table>
 </center>

<?php
   $SchoolFormName = "school-form";
   $ConfirmButtonID = "save-school-record";
?>
 <div id='school-form-dialog' title='Edit School Record'>
  <?php include "school.form.php"; ?>
 </div>

 <div id='school-url-dialog' title='Edit School URL'>
  <center>
  <table width='100%'>
   <tr>
    <td align='left'>
     <p class='align-left'><b><label for='dialog_new_url'>School URL</label>:</b><br>
      <input type='text' name='new_url' id='dialog_new_url' size='60' class="form-field text ui-widget-content ui-corner-all"></p>
     <input type='hidden' name='url-snum' id='url-snum'>
    </td>
   </tr>
  </table>
  <button id='save-school-url' class='btn btn--outline btn--small'> Save URL </button>
  <center>
 </div>

<?php 
endTemplate();
?>