/**
* 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');
}
/**
* Pushes users (htpasswd) and associations between users and courses (courselist.php)
*/
function push_users_courses_to_recorder()
{
global $recorder_user;
global $recorder_basedir;
global $recorder_subdir;
global $recorder_password_storage_enabled;
if (!db_ready()) {
db_prepare(statements_get());
}
$users = db_users_in_recorder_get();
$classrooms = db_classrooms_list_enabled();
//htpasswd
$htpasswd = '';
$previous_user = "";
foreach ($users as $u) {
if ($previous_user != $u['user_ID']) {
$htpasswd .= $u['user_ID'] . ':' . $u['recorder_passwd'] . PHP_EOL;
$previous_user = $u['user_ID'];
}
}
file_put_contents('var/htpasswd', $htpasswd);
//courselist.php
$courselist = '<?php' . PHP_EOL;
foreach ($users as $u) {
$title = isset($u['shortname']) && !empty($u['shortname']) ? $u['shortname'] : $u['course_name'];
$courselist .= '$course[\'' . $u['user_ID'] . '\'][\'' . $u['course_code'] . '\'] = "' . $title . '";' . PHP_EOL;
$courselist .= '$users[\'' . $u['user_ID'] . '\'][\'full_name\']="' . $u['forename'] . ' ' . $u['surname'] . '";' . PHP_EOL;
$courselist .= '$users[\'' . $u['user_ID'] . '\'][\'email\']="";' . PHP_EOL;
}
$courselist .= '?>';
file_put_contents('var/courselist.php', $courselist);
// Upload all this on server
foreach ($classrooms as $c) {
exec('ping ' . $c['IP'] . ' 10', $output, $return_val);
if ($return_val == 0) {
$cmd = 'scp -o ConnectTimeout=10 ./var/htpasswd ' . $recorder_user . '@' . $c['IP'] . ':' . $recorder_basedir . $recorder_subdir;
exec($cmd, $output, $return_var);
$cmd = 'scp -o ConnectTimeout=10 ./var/courselist.php ' . $recorder_user . '@' . $c['IP'] . ':' . $recorder_basedir . $recorder_subdir;
exec($cmd, $output, $return_var);
}
}
return true;
}
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this software; if not, write to the Free Software
* Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
*/
/*
* This script is called every night to count the number of albums in ezmanager and update the DB accordingly
*/
require_once 'config.inc';
require_once '../commons/lib_database.php';
$dircontent = scandir($repository_path);
foreach ($dircontent as $album) {
if ($album == '.' || $album == '..') {
continue;
}
if ($course_code_str = strstr($album, '-pub', true)) {
$course_code = $course_code_str;
$updated_courses[] = $course_code;
} else {
if (($course_code_str = strstr($album, '-priv', true)) && !in_array($course_code_str, $updated_courses)) {
$course_code = $course_code_str;
$updated_courses[] = $course_code;
}
}
}
db_prepare();
db_courses_update_hasalbums($dircontent);
db_close();
* Thibaut Roskam
*
* This software is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation; either
* version 3 of the License, or (at your option) any later version.
*
* This software is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this software; if not, write to the Free Software
* Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
*
* @package ezcast.ezadmin.test
*/
require_once '../commons/lib_database.php';
$course_name = "COURSE_MNEMO";
$user_name = "netid";
echo 'Preparing DB ...';
var_dump(db_prepare());
echo 'Testing courses_search ...' . PHP_EOL;
db_courses_search_compact('%', '%', true, true, true, true);
echo "All users of {$course_name} ..." . PHP_EOL;
var_dump(db_courses_search_compact($course_name, '%', true, true, false, true));
echo "All courses by {$user_name} ..." . PHP_EOL;
var_dump(db_courses_search_compact('%', $user_name, true, true, false, true));
echo "All users of {$course_name}, step 2 ..." . PHP_EOL;
var_dump(db_course_get_users($course_name));
* version 3 of the License, or (at your option) any later version.
*
* This software is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this software; if not, write to the Free Software
* Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
*/
/**
* @package ezcast.ezadmin.lib.sqlRequest
*/
require_once '../commons/lib_database.php';
db_prepare(stat_statements_get());
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');
}
/**
* Returns the thread with the given id
* @global null $db
* @param int $_id
* @return array or false if wrong parameter
*/
function threads_select_by_id($thread_id)
{
global $statements;
if (!$thread_id) {
return false;
function execute()
{
if (!($this->stmt = db_prepare($this->sql))) {
throw new Exception('Unable to prepare query: ' . db_error() . ' ' . $this->sql);
}
if (count($this->params)) {
$this->_bind($this->params);
}
if (!$this->stmt->execute() || !$this->stmt->store_result()) {
throw new OrmException('Unable to execute query: ' . $this->stmt->error);
}
return true;
}
* This software is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this software; if not, write to the Free Software
* Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
*/
/**
* Library used to send and retreive threads (and comments) informations between the database and the server
* @package ezcast.ezplayer.lib.thread
*/
require_once '../commons/config.inc';
require_once '../commons/lib_database.php';
db_prepare(threads_statements_get());
function threads_statements_get()
{
return array('thread_insert' => 'INSERT INTO ' . db_gettable('threads') . ' (title, message, timecode, authorId, authorFullName, creationDate, lastEditDate, studentOnly, albumName, assetName, assetTitle) ' . 'VALUES (:title, :message, :timecode, :authorId, :authorFullName, :creationDate, :lastEditDate, :studentOnly, :albumName, :assetName, :assetTitle)', 'thread_update' => 'UPDATE ' . db_gettable('threads') . ' SET title = :title, message = :message, timecode = :timecode ' . 'WHERE id = :id', 'threads_select_all' => 'SELECT * FROM ' . db_gettable('threads') . ' WHERE albumName like :albumName and assetName like :assetName ' . 'AND deleted = "0" ORDER BY timecode', 'thread_select_by_id' => 'SELECT * FROM ' . db_gettable('threads') . ' WHERE id = :id AND deleted = "0"', 'thread_delete_by_id' => 'UPDATE ' . db_gettable('threads') . ' SET deleted = :deleted ' . ' WHERE id = :id LIMIT 1', 'threads_select_by_album' => 'SELECT * FROM ' . db_gettable('threads') . ' WHERE albumName like :albumName and deleted = "0" ' . 'ORDER BY lastEditDate DESC', 'threads_select_by_asset' => 'SELECT * FROM ' . db_gettable('threads') . ' WHERE albumName like :albumName AND assetName like :assetName AND deleted = "0" ' . 'ORDER BY timecode', 'thread_update_lastEdit' => 'UPDATE ' . db_gettable('threads') . ' SET lastEditDate = :lastEditDate, lastEditAuthor = :lastEditAuthor ' . 'WHERE id = :id', 'thread_inc_nbComments' => 'UPDATE ' . db_gettable('threads') . ' SET nbComments = nbComments + 1 ' . 'WHERE id = :id', 'thread_dec_nbComments' => 'UPDATE ' . db_gettable('threads') . ' SET nbComments = nbComments - 1 ' . 'WHERE id = :id', 'thread_reinit_nbComments' => 'UPDATE ' . db_gettable('threads') . ' SET nbComments = 0 ' . 'WHERE id = :id', 'comment_insert' => 'INSERT INTO ' . db_gettable('comments') . ' (message, thread, authorId, authorFullName, creationDate, lastEditDate) ' . ' VALUES (:message, :thread, :authorId, :authorFullName, :creationDate, :lastEditDate)', 'comment_insert_reply' => 'INSERT INTO ' . db_gettable('comments') . ' (message, thread, authorId, authorFullName, creationDate, lastEditDate, parent) ' . ' VALUES (:message, :thread, :authorId, :authorFullName, :creationDate, :lastEditDate, :parent)', 'comment_update' => 'UPDATE ' . db_gettable('comments') . ' SET message = :message, lastEditDate = :lastEditDate ' . 'WHERE id = :id', 'comment_update_nbChild_up' => 'UPDATE ' . db_gettable('comments') . ' SET nbChilds = nbChilds+1 WHERE id = :id', 'comment_update_nbChild_down' => 'UPDATE ' . db_gettable('comments') . ' SET nbChilds = nbChilds-1 WHERE id = :id', 'comment_select_by_threadId' => 'SELECT * FROM ' . db_gettable('comments') . ' WHERE thread = :thread AND deleted = "0"', 'comment_delete_by_id' => 'UPDATE ' . db_gettable('comments') . ' SET deleted = "1" ' . 'WHERE id = :id', 'comment_select_by_id' => 'SELECT * FROM ' . db_gettable('comments') . ' WHERE id = :id LIMIT 1', 'comment_children_get' => 'SELECT * FROM ' . db_gettable('comments') . ' WHERE parent = :parent', 'comment_delete_by_thread' => 'UPDATE ' . db_gettable('comments') . ' SET deleted = "1" WHERE thread = :thread', 'comment_update_approval' => 'UPDATE ' . db_gettable('comments') . ' SET approval = :approval WHERE id = :id', 'comment_select_best' => 'SELECT * FROM ' . db_gettable('comments') . ' WHERE thread = :thread ' . 'AND score > 0 ' . 'AND deleted = "0" ORDER BY score DESC LIMIT 1', 'comment_score_init' => 'UPDATE ' . db_gettable('comments') . ' SET score = 0 , upvoteScore = 0, downvoteScore = 0 ' . 'WHERE id = :id', 'comment_update_score_up' => 'UPDATE ' . db_gettable('comments') . ' SET score = score+1 , upvoteScore = upvoteScore+1 ' . 'WHERE id = :id', 'comment_update_score_down' => 'UPDATE ' . db_gettable('comments') . ' SET score = score-1 , downvoteScore = downvoteScore+1 ' . 'WHERE id = :id', 'vote_insert' => 'INSERT INTO ' . db_gettable('votes') . ' (login, comment, voteType) ' . 'VALUES (:login, :comment, :voteType)', 'vote_delete' => 'DELETE FROM ' . db_gettable('votes') . ' WHERE comment = :comment');
}
// ===== T H R E A D S
/**
* Inserts a thread into the database
* @global type $db
* @param type $values
* @return boolean errorflag
*/
function thread_insert($values)
{
global $statements;
global $db_object;
public function execute($query, $params = array())
{
$ret = false;
set_error_handler(function ($errno, $errstr, $errfile, $errline, array $errcontext) {
// error was suppressed with the @-operator
if (0 === error_reporting()) {
return false;
}
throw new ErrorException($errstr, 0, $errno, $errfile, $errline);
});
try {
$stmt = db_prepare($query, $params);
if ($stmt) {
$ret = sqlsrv_execute($stmt);
}
if (!$ret) {
trigger_error(var_export(sqlsrv_errors(), true));
return false;
}
return $ret;
} catch (Exception $e) {
//
}
restore_error_handler();
}