HotLunches/school-directory.csv.php

<?php
$ValidAccess = 2010;
require_once("include/globals.php");


if (SessionActive() != 1 || ! UserLoggedIn()) {
   header("Location: /");
   exit();
}


// Ensure that only valid user types have access to this module
$AllowedUserTypes = array('SuperUser','System Administrator','Application Administrator','Administration Team','Schedule Team');
if (checkPermission($AllowedUserTypes) != 1) {
   echo("<br>\n");
   $error_message = getLanguageText('ERRORMSG-ACCESS-DENIED');
   include "form.messages.php";
   return;
}


// Update class info
$results = db_query("SELECT * FROM classes WHERE school_num = $school_num");
while ($row = db_fetch_array($results)) {
   $record_num = $row['record_num'];
   $class_info = db_escape_string(getClassInfo($record_num));
   db_query("UPDATE classes SET class_info = '$class_info' WHERE record_num = $record_num");
}


// Define column headers
$column_headers = array(getLanguageText('TEXT-CLASS'),
                        getLanguageText('TEXT-FIRST-NAME'),
                        getLanguageText('TEXT-LAST-NAME'));

// Determine what student fields to select
$Select = "c.class_info,p.first_name,p.last_name";

if ($school_record['collect_student_number'] == 'Y') {
   $Select .= ",p.student_number";
   $column_headers[] = getLanguageText('TEXT-STUDENT-NUMBER');
}

if ($school_record['collect_student_birthdate'] == 'Y') {
   $Select .= ",p.birthdate";
   $column_headers[] = getLanguageText('TEXT-BIRTH-DATE');
}

if ($school_record['collect_student_age'] == 'Y') {
   $Select .= ",p.age";
   $column_headers[] = getLanguageText('TEXT-AGE');
}

if ($school_record['collect_student_sex'] == 'Y') {
   $Select .= ",p.sex";
   $column_headers[] = getLanguageText('TEXT-SEX');
}

if ($school_record['collect_student_grade'] == 'Y') {
   $Select .= ",p.school_grade";
   $column_headers[] = getLanguageText('TEXT-GRADE');
}

if ($school_record['collect_student_permission'] == 'Y') {
   $Select .= ",p.additional_permission,p.additional_permission_note";
   $column_headers[] = getLanguageText('TEXT-ADDITIONAL-PERMISSION');
   $column_headers[] = getLanguageText('TEXT-NOTE');
}

if ($school_record['user_defined_values'] == 'Y') {
   $Select .= ",p.user_defined_value";
   $column_headers[] = $school_record['user_defined_title'];
}

if ($school_record['user_defined_values2'] == 'Y') {
   $Select .= ",p.user_defined_value2";
   $column_headers[] = $school_record['user_defined_title2'];
}

if ($school_record['user_defined_values3'] == 'Y') {
   $Select .= ",p.user_defined_value3";
   $column_headers[] = $school_record['user_defined_title3'];
}


// Add user and contact table fields
$Select .= ",uc.first_name,uc.last_name";
$column_headers[] = getLanguageText('TEXT-FIRST-NAME');
$column_headers[] = getLanguageText('TEXT-LAST-NAME');

// Second parent name if collected
if (in_array($school_record['registration_second_parent'],array('Required','Optional'))) {
   $Select .= ",u.first_name2,u.last_name2";
   $column_headers[] = getLanguageText('TEXT-FIRST-NAME');
   $column_headers[] = getLanguageText('TEXT-LAST-NAME');
}

$Select .= ",uc.home_phone,uc.work_phone,uc.cell_phone,uc.home_email,u.userid";
$column_headers[] = getLanguageText('TEXT-HOME-PHONE');
$column_headers[] = getLanguageText('TEXT-WORK-PHONE');
$column_headers[] = getLanguageText('TEXT-CELL-PHONE');
$column_headers[] = getLanguageText('TEXT-EMAIL');
$column_headers[] = getLanguageText('TEXT-USER-ID');

if (in_array($school_record['registration_address'],array('Optional','Required'))) {
   $Select .= ",uc.address,uc.city,uc.province,uc.postal";
   $column_headers[] = getLanguageText('TEXT-ADDRESS');
   $column_headers[] = getLanguageText('TEXT-CITY');
   $column_headers[] = getLanguageText('TEXT-PROVINCE');
   $column_headers[] = getLanguageText('TEXT-POSTAL');
}

if ($school_record['enable_class_parents'] == 'Y') {
   $Select .= ",u.class_parent";
   $column_headers[] = getLanguageText('TEXT-CLASS-PARENT');
}

if ($school_record['additional_registration_data_field1'] == 'Y') {
   $Select .= ",additional1";
   $column_headers[] = "";
}

if ($school_record['additional_registration_data_field2'] == 'Y') {
   $Select .= ",additional2";
   $column_headers[] = "";
}

if ($school_record['collect_permission'] != 'N') {
   $Select .= ",p.additional_permission,p.additional_permission_note";
   $column_headers[] = $school_record['permission_prompt'];
   $column_headers[] = $school_record['permission_description'];
}

if ($school_record['school_directory_permission'] == 'Y') {
   $Select .= ",u.school_directory";
   $column_headers[] = getLanguageText('TEXT-DIRECTORY');
}

if ($school_record['email_list_permission'] == 'Y') {
   $Select .= ",u.email_list";
   $column_headers[] = getLanguageText('TEXT-EMAIL-LIST');
}

$Select .= ",u.receive_emails";
$column_headers[] = getLanguageText('TEXT-RECEIVE-EMAIL-NEWS');

// $Select .= ",u.date_added";
// $column_headers[] = getLanguageText('TEXT-DATE-ADDED');

// $column_headers[] = getLanguageText('TEXT-NUMBER');
// $Select .= ",u.record_num AS family_num";


$orderby = "c.sequence,p.last_name,p.first_name";
if ($school_record['directory_sort'] == 'Family') $orderby = "p.last_name,p.user_num,p.first_name";

// Get set of records
$query = "SELECT $Select " .
         "FROM people AS p, classes AS c, users AS u, contacts AS uc " .
         "WHERE u.school_num = $base_school_num " .
         "AND u.type = 'Parent' " .
         "AND u.record_num = p.user_num " .
         "AND u.school_directory = 'Y' " .
         "AND p.school_num = $base_school_num " .
         "AND c.school_num = $base_school_num " .
         "AND c.record_num = p.class_num " .
         "AND uc.school_num = $school_num " .
         "AND u.record_num = uc.user_num " .
         "ORDER BY $orderby";
$query = "SELECT $Select " .
         "FROM people AS p, classes AS c, users AS u, contacts AS uc " .
         "WHERE u.school_num = $base_school_num " .
         "AND u.type = 'Parent' " .
         "AND u.record_num = p.user_num " .
         "AND p.school_num = $base_school_num " .
         "AND c.school_num = $base_school_num " .
         "AND c.record_num = p.class_num " .
         "AND uc.school_num = $base_school_num " .
         "AND u.record_num = uc.user_num " .
         "ORDER BY $orderby";
$results = db_query($query);

$data = array();
while ($row = db_fetch_array($results)) {
   $data[] = $row;
}

$fname = genFileName("directory.csv");
xlsData($data,$column_headers,$fname);

exit();
?>