/** * return array of the courses associated to a netid * * @param string $netid * @return array key: course code; value: course description */ function courses_list($netid) { // prepared requests $statements = array('course_all_get' => 'SELECT DISTINCT ' . db_gettable('courses') . '.course_code AS mnemonic, ' . db_gettable('courses') . '.course_name AS label ' . 'FROM ' . db_gettable('courses') . ' ' . 'ORDER BY mnemonic ASC', 'user_courses_get' => 'SELECT DISTINCT ' . db_gettable('users_courses') . '.ID, ' . db_gettable('courses') . '.course_code, ' . db_gettable('courses') . '.shortname, ' . db_gettable('courses') . '.course_name, ' . db_gettable('courses') . '.in_recorders, ' . db_gettable('users_courses') . '.origin ' . 'FROM ' . db_gettable('courses') . ' ' . 'INNER JOIN ' . db_gettable('users_courses') . ' ON ' . db_gettable('courses') . '.course_code = ' . db_gettable('users_courses') . '.course_code ' . 'WHERE user_ID = :user_ID'); $db = db_prepare($statements); if (!$db) { debuglog("could not connect to sgbd:" . mysql_error()); die; } $result = array(); if ($netid == "") { // retrieves all courses in the database $course_list = db_courses_all_get(); $result = array(); foreach ($course_list as $value) { $result[$value['mnemonic']] = $value['mnemonic'] . '|' . $value['label']; } } else { // retrieves all courses for a given netid $course_list = db_user_courses_get($netid); $result = array(); foreach ($course_list as $value) { $result[$value['course_code']] = $value['course_code'] . '|' . $value['course_name']; } } db_close(); return $result; }
function add_first_user() { global $input; // Add the first user in database $first_user = file_get_contents("../first_user"); $first_user = explode(" , ", $first_user); $user_ID = $first_user[0]; $surname = $first_user[3]; $forename = $first_user[2]; $passwd = $first_user[1]; $permissions = 1; // try { if (!db_ready()) { db_prepare(); } db_user_create($user_ID, $surname, $forename, $passwd, $permissions); add_admin_to_file($user_ID); push_users_to_ezmanager(); db_log(db_gettable('users'), 'Created user ' . $user_ID, $_SESSION['user_login']); db_close(); // } catch (PDOException $e) { // $errors['db_error'] = $e->getMessage(); // require template_getpath('install.php'); // die; // } session_destroy(); unlink("../first_user"); require template_getpath('install_success.php'); }
function stat_statements_get() { return array('thread_get' => 'SELECT * FROM ' . db_gettable('threads') . ' ' . 'WHERE id = :thread_id ' . 'LIMIT 1', 'thread_oldest_get' => 'SELECT min(creationDate) as minDate FROM ' . db_gettable('threads') . ' ' . 'LIMIT 1', 'thread_newest_get' => 'SELECT max(creationDate) as maxDate FROM ' . db_gettable('threads') . ' ' . 'LIMIT 1', 'threads_all_get' => 'SELECT * FROM ' . db_gettable("threads"), 'threads_by_asset_get' => 'SELECT * FROM ' . db_gettable("threads") . ' ' . 'WHERE albumName like :album_name ' . 'AND assetName like :asset_name ' . 'ORDER BY timecode', 'threads_count' => 'SELECT count(*) FROM ' . db_gettable('threads'), 'threads_by_month_count' => 'SELECT count(*) as nbTrd FROM ' . db_gettable('threads') . ' ' . 'WHERE creationDate like :creation_date', 'threads_by_interval_count' => 'SELECT count(*) as nbTrd FROM ' . db_gettable('threads') . ' ' . 'WHERE creationDate between :earlier and :later', 'threads_by_album_count' => 'SELECT count(*) FROM ' . db_gettable('threads') . ' ' . 'WHERE albumName like :album_name', 'threads_by_album_by_month_count' => 'SELECT count(*) as nbTrd FROM ' . db_gettable('threads') . ' ' . 'WHERE albumName like :album_name ' . 'AND creationDate like :creation_date', 'threads_by_album_by_interval_count' => 'SELECT count(*) as nbTrd FROM ' . db_gettable('threads') . ' ' . 'WHERE albumName like :album_name ' . 'AND creationDate between :earlier AND :later', 'threads_by_asset_count' => 'SELECT creationDate, albumName, assetName, count(*) FROM ' . db_gettable('threads') . ' ' . 'GROUP BY EXTRACT(DAY from creationDate), albumName', 'comments_by_thread_get' => 'SELECT * FROM ' . db_gettable("comments") . ' ' . 'WHERE thread = :thread_id', 'comments_count' => 'SELECT count(*) FROM ' . db_gettable('comments'), 'comments_by_album_count' => 'SELECT count(*) FROM ' . db_gettable('comments') . ' ' . 'JOIN ' . db_gettable('threads') . ' as t ' . 'ON t.id = thread ' . 'AND albumName like :album_name', 'comments_by_month_count' => 'SELECT count(*) as nbCmt FROM ' . db_gettable('comments') . ' ' . 'WHERE creationDate like :creation_date', 'comments_by_interval_count' => 'SELECT count(*) as nbCmt FROM ' . db_gettable('comments') . ' ' . 'WHERE creationDate between :earlier and :later', 'comments_by_album_by_month_count' => 'SELECT count(*) FROM ' . db_gettable('comments') . ' ' . 'JOIN ' . db_gettable('threads') . ' as t ' . 'ON t.id = thread ' . 'AND albumName like :album_name ' . 'WHERE t.creationDate like :creation_date', 'comments_by_album_by_interval_count' => 'SELECT count(*) FROM ' . db_gettable('comments') . ' c ' . 'JOIN ' . db_gettable('threads') . ' as t ' . 'ON t.id = thread ' . 'AND albumName like :album_name ' . 'WHERE c.creationDate between :earlier and :later', 'albums_all_get' => 'SELECT DISTINCT albumName FROM ' . db_gettable('threads'), 'albums_count' => 'SELECT count(*) FROM ' . db_gettable('threads') . ' ' . 'WHERE albumName like :album_name'); }
function remove_classroom() { global $input; if (!db_classroom_delete(trim($input['id']))) { echo json_encode(array('error' => '1')); } else { echo json_encode(array('success' => '1')); db_log(db_gettable('classrooms'), 'Deleted classroom ' . $input['id'], $_SESSION['user_login']); notify_changes(); } }
function thread_search_old($words, $fields, $albums, $asset = '') { global $db_object; if (count($fields) <= 0) { return null; } if (count($albums) <= 0) { return null; } // db columns where we can do a search $accepted_fields = array('title' => array('title', 't.title'), 'message' => array('message', 'c.message')); // prepares the fields to add in the query foreach ($fields as $field) { foreach ($accepted_fields as $accepted_field => $val) { if (strpos($field, $accepted_field) !== false) { $fields_in_query_thread[] = $val[0]; $fields_in_query_comment[] = $val[1]; break; } } } // for each album in the array, adds a parameter in the prepared statement $albums_in_query = implode(',', array_fill(0, count($albums), '?')); switch (count($words)) { case 0: $where = ''; // stmt will be: // SELECT ... FROM threads break; case 1: $where = ''; foreach ($fields_in_query_thread as $field => $column) { $where .= $where == '' ? 'WHERE ' : ' OR '; $where .= $column . ' LIKE ' . $db_object->quote("%" . $words[0] . "%"); } // stmt will be: // SELECT ... FROM threads // WHERE title LIKE "%$word%" // OR message LIKE "%$word%" break; default: $where = ''; foreach ($words as $index => $word) { if ($index == 0) { $where .= 'WHERE ( '; } else { $where .= ' AND id IN (SELECT id FROM ' . db_gettable('threads') . ' WHERE '; } $or = ''; foreach ($fields_in_query_thread as $field => $column) { $where .= $or . $column . ' LIKE ' . $db_object->quote("%" . $word . "%"); $or = ' OR '; } $where .= ') '; } // stmt will be: // SELECT ... FROM threads // WHERE (title LIKE '%$words[i]%' OR message LIKE '%$words[i]%') // AND id IN (SELECT id FROM threads WHERE title LIKE '%$words[i+1]%' OR message LIKE '%$words[i+1]%') // AND id IN (SELECT id FROM threads WHERE title LIKE '%$words[i+2]%' OR message LIKE '%$words[i+2]%') // AND ... } $stmt = 'SELECT DISTINCT id, title, message, timecode, albumName, assetName, assetTitle, studentOnly ' . 'FROM ' . db_gettable('threads') . ' ' . $where . ($where == '' ? ' WHERE ' : ' AND ') . 'albumName in(' . $albums_in_query . ') ' . (isset($asset) && $asset != '' ? ' AND assetName LIKE ' . $db_object->quote("%{$asset}%") . ' ' : ' ') . 'GROUP BY albumName, assetName, id'; $prepared_stmt = $db_object->prepare($stmt); $prepared_stmt->execute($albums); $result_threads = $prepared_stmt->fetchAll(); $stmt = 'SELECT DISTINCT t.id, t.title, c.message, t.albumName, t.assetTitle, t.studentOnly ' . 'FROM ' . db_gettable('comments') . ' c ' . 'JOIN threads t on t.id = thread ' . 'WHERE MATCH(' . $fields_in_query_comment . ') ' . 'AGAINST(' . $quoted_search . ' IN BOOLEAN MODE) ' . 'AND t.albumName in(' . $albums_in_query . ') ' . (isset($asset) && $asset != '' ? 'AND assetName LIKE ' . $db_object->quote("%{$asset}%") . ' ' : ' ') . 'GROUP BY t.albumName, t.assetName, t.id, c.id'; return $result_threads; /* // --> Alternatively, we can use MATCH(...) AGAINST(...) but // it doesn't give expected results. */ // converts fields array in string to inject in the prepared query // we can't use db->quote() or bind param because it adds quotes // and we can't have quotes in SQL 'MATCH' instruction $fields_in_query_thread = implode(',', $fields_in_query_thread); $fields_in_query_comment = implode(',', $fields_in_query_comment); // for each album in the array, adds a parameter in the prepared statement $albums_in_query = implode(',', array_fill(0, count($albums), '?')); // for each word to search, adds the word in a string // for SQL 'AGAINST' instruction foreach ($words as $word) { $search .= '+' . $word . '* '; } // pdo quotes the string to search, to avoid SQL injections $quoted_search = $db_object->quote($search); // prepared stmt $stmt = 'SELECT DISTINCT id, title, message, timecode, albumName, assetName, assetTitle, studentOnly ' . 'FROM ' . db_gettable('threads') . ' WHERE MATCH(' . $fields_in_query_thread . ') ' . 'AGAINST(' . $quoted_search . ' IN BOOLEAN MODE) ' . 'AND albumName in(' . $albums_in_query . ') ' . (isset($asset) && $asset != '' ? 'AND assetName LIKE ' . $db_object->quote("%{$asset}%") . ' ' : ' ') . 'GROUP BY albumName, assetName, id'; $prepared_stmt = $db_object->prepare($stmt); $prepared_stmt->execute($albums); $result_threads = $prepared_stmt->fetchAll(); $stmt = 'SELECT DISTINCT t.id, t.title, c.message, t.albumName, t.assetTitle, t.studentOnly ' . 'FROM ' . db_gettable('comments') . ' c ' . 'JOIN threads t on t.id = thread ' . 'WHERE MATCH(' . $fields_in_query_comment . ') ' . 'AGAINST(' . $quoted_search . ' IN BOOLEAN MODE) ' . 'AND t.albumName in(' . $albums_in_query . ') ' . (isset($asset) && $asset != '' ? 'AND assetName LIKE ' . $db_object->quote("%{$asset}%") . ' ' : ' ') . 'GROUP BY t.albumName, t.assetName, t.id, c.id'; $prepared_stmt = $db_object->prepare($stmt); $prepared_stmt->execute($albums); $result_comments = $prepared_stmt->fetchAll(); return array_merge($result_threads, $result_comments); }
function db_logs_get($date_start, $date_end, $table, $author, $limit) { global $db_object; $query = 'SELECT DISTINCT SQL_CALC_FOUND_ROWS `time`, `table`, message, author FROM ' . db_gettable('logs'); $where = ''; if (!empty($date_start)) { $where .= 'time >= \'' . $date_start . ' 00:00:00\''; } if (!empty($date_end)) { if (!empty($where)) { $where .= ' AND '; } $where .= 'time <= \'' . $date_end . ' 00:00:00\''; } if (!empty($table)) { if ($table != 'all') { if (!empty($where)) { $where .= ' AND '; } $where .= '`table` LIKE \'' . db_gettable($table) . '\''; } } if (!empty($author)) { if (!empty($where)) { $where .= ' AND '; } $where .= 'author LIKE %' . $author . '%'; } $fullQuery = $query; if (!empty($where)) { $fullQuery .= ' WHERE ' . $where; } return $db_object->query($fullQuery . ' ORDER BY `time` DESC LIMIT ' . $limit); }