/**
* 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);
}
