/** @fn IPP_Log($szMsg='', $username="******", $level='ERROR',$student_id='')
* @brief Puts log entry in MySQL database
* @detail Currently only logs errors; Warnings and Information don't have complete code.
* @param string $szMsg
* @param string $username
* @param string $level
* @param string $student_id
* @return void|boolean
* @todo
* 1. Rename as function that *puts* information in the database
*/
function IPP_Log($szMsg = '', $username = "******", $level = 'ERROR', $student_id = '')
{
//Error Handler
switch ($level) {
case 'WARNING':
case 'INFORMATIONAL':
case 'ERROR':
//connect
if (!connectIPPDB()) {
return;
//crappy...but...oh well.
}
$log_query = "INSERT INTO error_log (level,username,time,message,student_id) VALUES ('{$level}','{$username}',now(),'" . mysql_real_escape_string($szMsg) . "',";
if ($student_id == "") {
$log_query = $log_query . "NULL";
} else {
$log_query = $log_query . "'{$student_id}'";
}
$log_query = $log_query . ")";
$log_result = mysql_query($log_query);
//don't care about the result...if she don't log, she don't log.
if (!$log_result) {
echo "log error: " . mysql_error() . "<BR>Query= " . $log_query . "<BR>";
}
break;
}
return TRUE;
}
/** @fn getNumUsersOnline()
* @brief gets count of logged-in support_members
* @detail Returns NULL on failure
* @todo See where this function is called; perhaps move to a better place
*/
function getNumUsersOnline()
{
//returns the number of users in support_member tables
//or NULL on fail.
global $error_message;
if (!connectIPPDB()) {
$error_message = $error_message;
//just to remember we need this - todo: make this a why? comment
return NULL;
}
$query = "SELECT * FROM logged_in WHERE 1=1";
$result = mysql_query($query);
if (!$result) {
$error_message = "Database query failed (" . __FILE__ . ":" . __LINE__ . "): " . mysql_error() . "<BR>Query: '{$query}'<BR>";
return NULL;
}
return mysql_num_rows($result);
}
function parse_submission()
{
if (!$_POST['first_name']) {
return "You must supply a first name<BR>";
}
if (!$_POST['last_name']) {
return "You must supply a last name<BR>";
}
//check that date is the correct pattern...
$regexp = '/^\\d\\d\\d\\d-\\d\\d?-\\d\\d?$/';
if (!preg_match($regexp, $_POST['birthday'])) {
return "Birthday must be in YYYY-MM-DD format<BR>";
}
if (!preg_match($regexp, $_POST['at_school_since'])) {
return "At School Since must be in YYYY-MM-DD format<BR>";
}
//if(!$_POST['prov_ed_num']) return "You must supply a Provincial Education Number<BR>";
//if(!$_POST['ab_ed_code']) return "You must supply an Alberta Education Coding Value<BR>";
//check duplicate prov ed number...
if (!connectIPPDB()) {
$error_message = $error_message;
//just to remember we need this
$system_message = $error_message;
IPP_LOG($system_message, $_SESSION['egps_username'], 'ERROR');
}
if ($_POST['prov_ed_num'] != "") {
$duplicate_query = "SELECT * FROM student WHERE prov_ed_num='" . mysql_real_escape_string($_POST['prov_ed_num']) . "'";
$duplicate_result = mysql_query($duplicate_query);
if (mysql_num_rows($duplicate_result) > 0) {
$duplicate_row = mysql_fetch_array($duplicate_result);
return "Duplicate Provincial Education Number (name:" . $duplicate_row['first_name'] . " " . $duplicate_row['last_name'] . "),<BR>This student probably already exists in the database<BR>";
}
}
//$duplicate_query = "SELECT * FROM student WHERE ab_ed_code='" . mysql_real_escape_string($_POST['ab_ed_code']) ."'";
//$duplicate_result= mysql_query($duplicate_query);
//if(mysql_num_rows($duplicate_result) > 0) {$duplicate_row = mysql_fetch_array($duplicate_result);return "Duplicate Alberta Education Code Number (name:" . $duplicate_row['first_name'] . " " . $duplicate_row['last_name'] ."),<BR>This student probably already exists in the database<BR>"; }
return NULL;
}
function getSupportMembers()
{
global $error_message, $iLimit, $iCur, $student_id;
if (!connectIPPDB()) {
$system_message = $system_message . $error_message;
//just to remember we need this
IPP_LOG($system_message, $_SESSION['egps_username'], 'ERROR');
}
// LEFT JOIN area_list ON support_list.uid=area_list.support_list_uid LEFT JOIN area_type ON area_list.area_type_id=area_type.area_type_id
// original $query = "SELECT * FROM support_list where student_id=" . $student_id . " ORDER BY egps_username ASC LIMIT $iCur,$iLimit";
$query = "SELECT * FROM support_list where student_id=" . $student_id . " ORDER BY egps_username ASC";
$result = mysql_query($query);
if (!$result) {
$error_message = "Database query failed (" . __FILE__ . ":" . __LINE__ . "): " . mysql_error() . "<BR>Query: '{$query}'<BR>";
return NULL;
}
return $result;
}
function getLogTotals()
{
global $error_message, $iLimit, $iCur, $szLevel;
if (!connectIPPDB()) {
$error_message = $error_message;
//just to remember we need this
IPP_LOG($system_message, $_SESSION['egps_username'], 'ERROR');
}
if ($szLevel == "ALL") {
$log_query = "SELECT * FROM error_log WHERE 1=1";
} else {
$log_query = "SELECT * FROM error_log WHERE level='{$szLevel}'";
}
$log_result = mysql_query($log_query);
if (!$log_result) {
$error_message = "Database query failed (" . __FILE__ . ":" . __LINE__ . "): " . mysql_error() . "<BR>Query: '{$log_query}'<BR>";
return NULL;
}
return mysql_num_rows($log_result);
}
/** @fn getStudentPermission($student_id='')
* @brief Determines user's access to specific student's records
* @detail
* 1. Returns error or null under some circumstances.
* 2. Otherwise, may return NONE,ERROR,READ,WRITE(READ,WRITE),ASSIGN(READ,WRITE,ASSIGN),ALL(READ,WRITE,ASSIGN,DELETE), or support_list['permission'] or NONE for no permissions.
* @param string $student_id
* @return string|NULL|Ambigous
* @todo
* 1. Rename function because it is a confusing name
* 2. It can start with get_. Separate words with underscores. Perhaps get_access_to_student_record().
*/
function getStudentPermission($student_id = '')
{
//returns NONE,ERROR,READ,WRITE(READ,WRITE),ASSIGN(READ,WRITE,ASSIGN),ALL(READ,WRITE,ASSIGN,DELETE),
//or support_list['permission'] or NONE for no permissions.
global $error_message, $mysql_user_select_login, $mysql_user_select_password, $mysql_user_table, $mysql_user_append_to_login;
$error_message = "";
$permission_level = getPermissionLevel($_SESSION['egps_username']);
if ($permission_level == NULL) {
return "ERROR";
}
//find the currently logged in persons school code...
if (!connectUserDB()) {
$error_message = $error_message;
//just to remember we need this
return "ERROR";
}
$query = "SELECT * FROM {$mysql_user_table} WHERE (" . $mysql_user_select_login . "='" . $_SESSION['egps_username'] . $mysql_user_append_to_login . "' or " . $mysql_user_select_login . "='" . $_SESSION['egps_username'] . "') and " . $mysql_user_select_password . "='" . $_SESSION['password'] . "' AND aliased_name IS NULL";
$result = mysql_query($query);
if (!$result) {
$error_message = "Database query failed (" . __FILE__ . ":" . __LINE__ . "): " . mysql_error() . "<BR>Query: '{$query}'<BR>";
return "ERROR";
}
$user_row = mysql_fetch_array($result);
$school_code = $user_row['school_code'];
if (!connectIPPDB()) {
$error_message = $error_message;
//just to remember we need this
return "ERROR";
}
//check if this staff member is local to this student...
$local_query = "SELECT * FROM school_history WHERE student_id={$student_id} AND school_code='{$school_code}' AND end_date IS NULL";
$local_result = mysql_query($local_query);
//ignore errors...
$is_local_student = FALSE;
if ($local_result && mysql_num_rows($local_result) > 0) {
$is_local_student = TRUE;
}
//Special case we are the school-based IPP administrator
//get our school code
$error_message = "";
if (!connectIPPDB()) {
$error_message = $error_message;
//just to remember we need this
return NULL;
}
$system_query = "SELECT * from support_member WHERE egps_username='******'egps_username'] . "'";
$system_result = mysql_query($system_query);
if (!$system_result) {
$error_message = "Database query failed (" . __FILE__ . ":" . __LINE__ . "): " . mysql_error() . "<BR>Query: '{$system_query}'<BR>";
return "ERROR";
} else {
$system_row = mysql_fetch_array($system_result);
if ($is_local_student && $system_row['is_local_ipp_administrator'] == 'Y') {
return "ASSIGN";
}
}
//base our permission on the level we're assigned.
switch ($permission_level) {
case 0:
//Super Admin
//Super Admin
case 10:
//Administrator
return "ALL";
case 30:
//Principal (assign local) special case
//fall through and return ALL for local students.
//Principal (assign local) special case
//fall through and return ALL for local students.
case 20:
//Assistant Admin. (view all) special case
//fall through and return at least read...
//Assistant Admin. (view all) special case
//fall through and return at least read...
case 40:
//Vice Principal (view local)
//Vice Principal (view local)
default:
//we need to find the permissions from the support list
//as this user has no inherent permissions...
$support_query = "SELECT * FROM support_list WHERE egps_username='******'egps_username'] . "' AND student_id={$student_id}";
$support_result = mysql_query($support_query);
//if(mysql_num_rows($support_result) <= 0) {
switch ($permission_level) {
case 30:
case 40:
//changed as per s. chomistek (2006-03-23)
if ($is_local_student) {
return "ASSIGN";
} else {
return "NONE";
}
case 20:
//Asst admin special case of read for all
if ($is_local_student) {
return "ASSIGN";
} else {
return "READ";
}
//case 40: //vp special case read local
// if($is_local_student) return "READ";
//else return "NONE";
//case 40: //vp special case read local
// if($is_local_student) return "READ";
//else return "NONE";
default:
//return "NONE";
}
//} //else {
$row = mysql_fetch_array($support_result);
if ($row['permission'] != '') {
return $row['permission'];
}
return "NONE";
//}
}
}
function getStudents()
{
global $error_message, $permission_level, $system_message, $IPP_MIN_VIEW_LIST_ALL_LOCAL_STUDENTS, $IPP_MIN_VIEW_LIST_ALL_STUDENTS, $iLimit, $iCur, $szSchool, $szTotalStudents;
if (!connectIPPDB()) {
$system_message = $system_message . $error_message;
//just to remember we need this
IPP_LOG($system_message, $_SESSION['egps_username'], 'ERROR');
}
//do a subquery to find our school code...easier than messing with the ugly
//query below...
$school_code_query = "SELECT school_code FROM support_member WHERE egps_username='******'egps_username']) . "'";
$school_code_result = mysql_query($school_code_query);
if (!$school_code_result) {
$error_message = "Database query failed (" . __FILE__ . ":" . __LINE__ . "): " . mysql_error() . "<BR>Query: '{$school_code_query}'<BR>";
return NULL;
}
$school_code_row = mysql_fetch_array($school_code_result);
$school_code = $school_code_row['school_code'];
$student_query = "SELECT DISTINCT student.student_id,last_name,first_name FROM student LEFT JOIN support_list ON student.student_id = support_list.student_id LEFT JOIN school_history ON student.student_id=school_history.student_id WHERE ((support_list.egps_username='******'egps_username']) . "' AND support_list.student_id IS NOT NULL)";
//prior to april 20/06: $student_query = "SELECT DISTINCT student.student_id,last_name,first_name FROM student LEFT JOIN support_list ON student.student_id = support_list.student_id LEFT JOIN school_history ON student.student_id=school_history.student_id WHERE ((support_list.egps_username='******'egps_username']) . "' AND support_list.student_id IS NOT NULL)";
//prior to march 18/06: $student_query = "SELECT DISTINCT student.student_id,last_name,first_name,school_history.school_code,school.* FROM student LEFT JOIN support_list ON student.student_id = support_list.student_id LEFT JOIN school_history ON student.student_id=school_history.student_id LEFT JOIN school ON school_history.school_code=school.school_code WHERE (support_list.egps_username='******'egps_username']) . "' AND support_list.student_id IS NOT NULL) OR (";
if ($IPP_MIN_VIEW_LIST_ALL_STUDENTS >= $permission_level) {
//orig 2006-04-20: $student_query = $student_query . " OR (end_date IS NOT NULL)";
$student_query = $student_query . " OR (student.student_id IS NOT NULL)";
}
$student_query .= ") AND NOT EXISTS (SELECT student.student_id,last_name,first_name FROM school_history WHERE school_history.student_id=student.student_id AND school_history.end_date IS NULL)";
$student_query_limit = $student_query . " ORDER BY student.last_name ASC LIMIT {$iCur},{$iLimit}";
$student_result_limit = mysql_query($student_query_limit);
if (!$student_result_limit) {
$error_message = "Database query failed (" . __FILE__ . ":" . __LINE__ . "): " . mysql_error() . "<BR>Query: '{$student_query_limit}'<BR>";
return NULL;
}
//$system_message = $system_message . "rows returned: " . mysql_num_rows($student_result_limit) . "<BR>";
//$system_message = $system_message . $student_query_limit . "<BR>";
//find the totals...
$student_result_total = mysql_query($student_query);
if (!$student_result_total) {
$error_message = "Database query failed (" . __FILE__ . ":" . __LINE__ . "): " . mysql_error() . "<BR>Query: '{$student_query}'<BR>";
return NULL;
}
$szTotalStudents = mysql_num_rows($student_result_total);
$system_message = $system_message . "Number of archived students: {$szTotalStudents}<BR>";
$system_message = $system_message . "(Showing: " . mysql_num_rows($student_result_limit) . ")<BR>";
//$system_message = $system_message . "<BR>$student_query<BR><BR>";
return $student_result_limit;
}
/** @fn getStudents()
* @brief Gets a count of students from the database that go to a member
* @return NULL|resource
* @todo get_student_count()
*/
function getStudents()
{
global $error_message, $IPP_MIN_VIEW_LIST_ALL_LOCAL_STUDENTS, $permission_level, $system_message, $IPP_MIN_VIEW_LIST_ALL_STUDENTS, $iLimit, $iCur, $szSchool, $szTotalStudents;
if (!connectIPPDB()) {
$system_message = $system_message . $error_message;
//just to remember we need this
IPP_LOG($system_message, $_SESSION['egps_username'], 'ERROR');
}
//do a subquery to find our school code...easier than messing with the ugly
//query below...
$school_code_query = "SELECT school_code FROM support_member WHERE egps_username='******'egps_username']) . "'";
$school_code_result = mysql_query($school_code_query);
if (!$school_code_result) {
$error_message = "Database query failed (" . __FILE__ . ":" . __LINE__ . "): " . mysql_error() . "<BR>Query: '{$school_code_query}'<BR>";
return NULL;
}
$school_code_row = mysql_fetch_array($school_code_result);
$school_code = $school_code_row['school_code'];
//$student_query = "SELECT student.student_id,last_name,first_name,school_history.school_code,school.* FROM student LEFT JOIN school_history ON student.student_id=school_history.student_id LEFT JOIN school ON school_history.school_code=school.school_code WHERE end_date IS NULL ";
$student_query = "SELECT DISTINCT student.student_id,last_name,first_name,school_history.school_code,school.* FROM student LEFT JOIN support_list ON student.student_id = support_list.student_id LEFT JOIN school_history ON student.student_id=school_history.student_id LEFT JOIN school ON school_history.school_code=school.school_code WHERE ((support_list.egps_username='******'egps_username']) . "' AND school_history.end_date IS NULL AND support_list.student_id IS NOT NULL) OR (";
//prior to march 18/06: $student_query = "SELECT DISTINCT student.student_id,last_name,first_name,school_history.school_code,school.* FROM student LEFT JOIN support_list ON student.student_id = support_list.student_id LEFT JOIN school_history ON student.student_id=school_history.student_id LEFT JOIN school ON school_history.school_code=school.school_code WHERE (support_list.egps_username='******'egps_username']) . "' AND support_list.student_id IS NOT NULL) OR (";
if (!($IPP_MIN_VIEW_LIST_ALL_STUDENTS >= $permission_level)) {
//$IPP_MIN_VIEW_LIST_ALL_LOCAL_STUDENTS >= $permission_level) {
$student_query = $student_query . "school_history.school_code='{$school_code}' AND ";
//prior to 2006-03-21: $student_query = $student_query . "school_history.school_code='$school_code' AND ";
if ($IPP_MIN_VIEW_LIST_ALL_LOCAL_STUDENTS < $permission_level) {
//$system_message .= "debug: permission level: $IPP_MIN_VIEW_LIST_ALL_LOCAL_STUDENTS < $permission_level<BR><BR>";
$student_query .= "support_list.egps_username='******'egps_username']) . "' AND ";
}
$student_query .= "end_date IS NULL) ";
} else {
$student_query = $student_query . "end_date IS NULL) ";
}
if (isset($_GET['SEARCH'])) {
switch ($_GET['field']) {
case 'last_name':
$student_query = $student_query . "AND student.last_name LIKE '" . mysql_real_escape_string($_GET['szSearchVal']) . "' ";
break;
case 'first_name':
$student_query = $student_query . "AND student.first_name LIKE '" . mysql_real_escape_string($_GET['szSearchVal']) . "' ";
break;
case 'last_name':
$student_query = $student_query . "AND student.last_name LIKE '" . mysql_real_escape_string($_GET['szSearchVal']) . "' ";
break;
case 'school_name':
$student_query = $student_query . "AND school.school_name LIKE '" . mysql_real_escape_string($_GET['szSearchVal']) . "' ";
break;
case 'school_code':
$student_query = $student_query . "AND school_history.school_code LIKE '" . mysql_real_escape_string($_GET['szSearchVal']) . "' ";
}
}
//added 2006-04-20: to prevent null school histories from showing up as active.
$student_query .= ") AND EXISTS (SELECT school_history.student_id FROM school_history WHERE school_history.student_id=student.student_ID) ";
//end added 2006-04-20
$student_query_limit = $student_query . "ORDER BY school_history.school_code,student.last_name ASC LIMIT {$iCur},{$iLimit}";
$student_result_limit = mysql_query($student_query_limit);
if (!$student_result_limit) {
$error_message = "Database query failed (" . __FILE__ . ":" . __LINE__ . "): " . mysql_error() . "<BR>Query: '{$student_query_limit}'<BR>";
return NULL;
}
//$system_message = $system_message . "debug: " . $student_query_limit . "<BR>";
//find the totals...
$student_result_total = mysql_query($student_query);
if (!$student_result_total) {
$error_message = "Database query failed (" . __FILE__ . ":" . __LINE__ . "): " . mysql_error() . "<BR>Query: '{$student_query}'<BR>";
return NULL;
}
$szTotalStudents = mysql_num_rows($student_result_total);
return $student_result_limit;
}
function getUsers()
{
global $error_message, $iLimit, $iCur, $bShowNav, $system_message;
if (!connectIPPDB()) {
$system_message = $system_message . $error_message;
//just to remember we need this
IPP_LOG($system_message, $_SESSION['egps_username'], 'ERROR');
}
if (!isset($_GET['username'])) {
if (isset($_GET['showall'])) {
$query = "SELECT * FROM support_member LEFT JOIN school ON support_member.school_code=school.school_code where 1=1 ORDER BY egps_username ASC";
} else {
$query = "SELECT * FROM support_member LEFT JOIN school ON support_member.school_code=school.school_code where 1=1 ORDER BY egps_username ASC LIMIT {$iCur},{$iLimit}";
$bShowNav = TRUE;
}
} else {
$query = "SELECT * FROM support_member LEFT JOIN school ON support_member.school_code=school.school_code WHERE egps_username LIKE '" . $_GET['username'] . "' ORDER BY egps_username ASC";
}
if (isset($_GET['index'])) {
$query = "SELECT * FROM support_member LEFT JOIN school on support_member.school_code=school.school_code WHERE ASCII(LOWER(egps_username)) >= ASCII('" . mysql_real_escape_string($_GET['index']) . "') ORDER BY egps_username ASC LIMIT {$iLimit}";
//do some moronic thing to find our index- were I not so lazy I'd find a more elegant method.
$get_index_query = "SELECT * FROM support_member LEFT JOIN school on support_member.school_code=school.school_code WHERE ASCII(LOWER(egps_username)) < ASCII('" . mysql_real_escape_string($_GET['index']) . "')";
$get_index_result = mysql_query($get_index_query);
if ($get_index_result) {
$iCur = mysql_num_rows($get_index_result);
} else {
$system_message .= "Database query failed (" . __FILE__ . ":" . __LINE__ . "): " . mysql_error() . "<BR>Query: '{$get_index_query}'<BR>";
}
}
$result = mysql_query($query);
if (!$result) {
$error_message = "Database query failed (" . __FILE__ . ":" . __LINE__ . "): " . mysql_error() . "<BR>Query: '{$query}'<BR>";
return NULL;
}
return $result;
}
//}
//$iNumResults = mysql_num_rows($egps_username_result);
//if($iNumResults > 50) {
// $system_message = "";
// $system_message = "Your selection yielded $iNumResults names. Please try to refine your search";
// require (IPP_PATH . "superuser_new_member.php");
// exit();
//}
//if($iNumResults <= 0) {
// $system_message = "";
// $system_message = "Your selection yielded $iNumResults names.";
// require (IPP_PATH . "superuser_new_member.php");
// exit();
//}
//get the permission levels from db
if (!connectIPPDB()) {
$error_message = $error_message;
//just to remember we need this
$system_message = $error_message;
IPP_LOG($system_message, $_SESSION['egps_username'], 'ERROR');
}
$permission_query = "SELECT * FROM permission_levels WHERE 1=1 ORDER BY level DESC ";
$permission_result = mysql_query($permission_query);
if (!$permission_result) {
$error_message = $error_message . "Database query failed (" . __FILE__ . ":" . __LINE__ . "): " . mysql_error() . "<BR>Query: '{$permission_query}'<BR>";
$system_message = $system_message . $error_message;
IPP_LOG($system_message, $_SESSION['egps_username'], 'ERROR');
}
$school_query = "SELECT * FROM school WHERE 1=1";
$school_result = mysql_query($school_query);
if (!$school_result) {