\n"); $error_message = getLanguageText('ERRORMSG-ACCESS-DENIED'); include "form.messages.php"; return; } // Start the template and output buffering startTemplate(); $templatePageTitle = "School Status"; include "schools.options.php"; // Setup date range for schedules and orders $this_month = date("m",time()); $this_year = date("Y",time()); $last_year = $this_year - 1; $next_year = $this_year + 1; if ($this_month >= '09') { $start_date = "{$this_year}-09-01"; $end_date = "{$next_year}-08-31"; } else { $start_date = "{$last_year}-09-01"; $end_date = "{$this_year}-08-31"; } // Get records to display $columnHeaders = array("School Name","Staff","Classes","Users","Students","Suppliers","Products","Schedules","Menus","Orders","Revenue"); $columnAlign = array('left','center','center','center','center','center','center','center','center','center','right'); $query = "select * " . "from schools " . "where status in ('Pending','Trial','Active') " . "and reseller = 'hotlunch' " . "order by school_name"; $schools = db_query($query); $data = array(); $totals = array(); $otipSpans = ""; for ($i = 1; $i <= 9; $i++) $totals[$i] = 0; $count = 0; while ($row = db_fetch_array($schools)) { extract($row); $snum = $row['record_num']; $sname = $row['school_name']; // Create otip for school name $span_id = "school$snum"; $otipSpans .= "" . $otipTABLE400 . sprintf($otipROW2,"School: ","$sname, $city $province") . sprintf($otipROW2,"Principal: ","$principal") . sprintf($otipROW2,"Phone: ",$phone) . sprintf($otipROW2,"Type: ",$type) . sprintf($otipROW2,"Type & Status: ","$system_configuration - $status") . sprintf($otipROW2,"Start Date: ",$start_date) . sprintf($otipROW2,"Expiry Date: ",$expiry_date) . sprintf($otipROW2,"Reseller: ",$reseller) . sprintf($otipROW2,"PayPal: ",$paypal_enabled) . sprintf($otipROW2,"Access Code: ",$system_access_code); // 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); // Insert divider row $otipSpans .= sprintf($otipROW21,'center',"-----------------------------------------------"); // Get 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)) { $otipSpans .= sprintf($otipROW2,"Name: ",$crow['contact_name']); if (strlen($crow['contact_email'])) $otipSpans .= sprintf($otipROW2,"Email: ",$crow['contact_email']); if (strlen($crow['contact_phone'])) $otipSpans .= sprintf($otipROW2,"Phone: ",$crow['contact_phone']); if (strlen($crow['contact_cell'])) $otipSpans .= sprintf($otipROW2,"Cell: ",$crow['contact_cell']); $otipSpans .= sprintf($otipROW21,'center',"-----------------------------------------------"); } } // Complete otip table $otipSpans .= "\n"; $sname = "$sname"; $data[$count][0] = $sname; $linked_sites = db_query("SELECT record_num FROM schools WHERE linked_site = $snum"); echo("\n"); if (db_num_rows($linked_sites) > 0) { while ($row = db_fetch_array($linked_sites)) { $snum .= "," . $row['record_num']; } } // Get number of staff entries $num = db_getVal("select count(*) from people where school_num IN ($snum) and people_type = 'Staff'"); if ($num == 0) $data[$count][1] = " $num "; else $data[$count][1] = $num; // Get number of classes $num = db_getVal("select count(*) from classes where school_num IN ($snum)"); if ($num == 0) $data[$count][2] = " $num "; else $data[$count][2] = $num; // Determine number of users $num = db_getVal("select count(*) from users where school_num IN ($snum) and status = 'Active'"); if ($num == 0) $data[$count][3] = " $num "; else $data[$count][3] = $num; // Get number of students $num = db_getVal("select count(*) from people where school_num IN ($snum) and people_type = 'Student'"); if ($num == 0) $data[$count][4] = " $num "; else $data[$count][4] = $num; // Number of suppliers $num = db_getVal("select count(*) from suppliers where school_num IN ($snum) and status in ('Pending','Active')"); if ($num == 0) $data[$count][5] = " $num "; else $data[$count][5] = $num; // Number of products $num = db_getVal("select count(*) from supplier_products as sp,suppliers as s " . "where s.school_num IN ($snum) " . "and sp.school_num IN ($snum) " . "and s.record_num = sp.supplier_num " . "and sp.status = 'Active' " . "and s.status in ('Pending','Active')"); if ($num == 0) $data[$count][6] = " $num "; else $data[$count][6] = $num; // Number of schedules $data[$count][7] = db_getVal("select count(*) from hotlunch_schedules where school_num IN ($snum) and schedule_start_date between '$start_date' and '$end_date'"); // Number of menu dates $data[$count][8] = db_getVal("select count(hld.record_num) from hotlunch_menu_dates as hld,hotlunch_schedules as hls " . "where hls.school_num IN ($snum) " . "and hld.school_num IN ($snum) " . "and hls.record_num = hld.schedule_num " . "and schedule_start_date between '$start_date' and '$end_date'"); // Number of orders and revenue $result = db_query("select count(*) as orders,sum(order_total) as revenue from hotlunch_order_header as hlo,hotlunch_schedules as hls " . "where hls.school_num IN ($snum) " . "and hlo.school_num IN ($snum) " . "and hls.status in ('Pending','Active') " . "and hls.record_num = hlo.schedule_num " . "and schedule_start_date between '$start_date' and '$end_date'"); $row = db_fetch_array($result); $data[$count][9] = $row['orders']; $span = ''; if ($row['revenue'] > 80000) $span = ""; $data[$count][10] = $span . '$' . number_format($row['revenue'],2) . ""; for ($i = 1; $i <= 9; $i++) $totals[$i] += $data[$count][$i]; $totals[10] += $row['revenue']; $count++; } $totals[10] = '$' . number_format($totals[10],2); listDataCustom($data,$columnHeaders,'',array(),'N',$columnAlign,$totals); echo("
\n"); include "schools.options.php"; endTemplate(); ?>