public static function get($inputData = array())
{
$limitQuery = "";
$limitShow = isset($inputData['limitShow']) ? $inputData['limitShow'] : 0;
$limitPage = isset($inputData['limitPage']) ? $inputData['limitPage'] : 0;
$limitPage = (int) $limitPage > 0 ? $limitPage : 0;
$limitPosition = $limitPage * (int) $limitShow;
$limitQuery = (int) $limitShow == 0 ? '' : " limit {$limitPosition},{$limitShow}";
$limitQuery = isset($inputData['limitQuery']) ? $inputData['limitQuery'] : $limitQuery;
$field = "cronid,timenumber,timetype,timeinterval,last_update,jobdata,date_added,status";
$selectFields = isset($inputData['selectFields']) ? $inputData['selectFields'] : $field;
$whereQuery = isset($inputData['where']) ? $inputData['where'] : '';
$orderBy = isset($inputData['orderby']) ? $inputData['orderby'] : 'order by cronid desc';
$result = array();
$command = "select {$selectFields} from cronjobs {$whereQuery} {$orderBy}";
$queryCMD = isset($inputData['query']) ? $inputData['query'] : $command;
$queryCMD .= $limitQuery;
$query = Database::query($queryCMD);
$inputData['isHook'] = isset($inputData['isHook']) ? $inputData['isHook'] : 'yes';
if ((int) $query->num_rows > 0) {
while ($row = Database::fetch_assoc($query)) {
if (isset($row['jobdata'])) {
$row['jobdata'] = String::jsonToArray($row['jobdata']);
}
$row['date_addedFormat'] = Render::dateFormat($row['date_added']);
$result[] = $row;
}
} else {
return false;
}
// print_r($result);die();
return $result;
}
/**
* @package chamilo.survey
* @author Arnaud Ligot <*****@*****.**>
* @version $Id: $
*
* A small peace of code to enable user to access images included into survey
* which are accessible by non authenticated users. This file is included
* by document/download.php
*/
function check_download_survey($course, $invitation, $doc_url)
{
require_once 'survey.lib.php';
// Getting all the course information
$_course = CourseManager::get_course_information($course);
$course_id = $_course['real_id'];
// Database table definitions
$table_survey = Database::get_course_table(TABLE_SURVEY);
$table_survey_question = Database::get_course_table(TABLE_SURVEY_QUESTION);
$table_survey_question_option = Database::get_course_table(TABLE_SURVEY_QUESTION_OPTION);
$table_survey_invitation = Database::get_course_table(TABLE_SURVEY_INVITATION);
// Now we check if the invitationcode is valid
$sql = "SELECT * FROM {$table_survey_invitation}\n\t WHERE\n\t c_id = {$course_id} AND\n\t invitation_code = '" . Database::escape_string($invitation) . "'";
$result = Database::query($sql);
if (Database::num_rows($result) < 1) {
Display::display_error_message(get_lang('WrongInvitationCode'), false);
Display::display_footer();
exit;
}
$survey_invitation = Database::fetch_assoc($result);
// Now we check if the user already filled the survey
if ($survey_invitation['answered'] == 1) {
Display::display_error_message(get_lang('YouAlreadyFilledThisSurvey'), false);
Display::display_footer();
exit;
}
// Very basic security check: check if a text field from a survey/answer/option contains the name of the document requested
// Fetch survey ID
// If this is the case there will be a language choice
$sql = "SELECT * FROM {$table_survey}\n\t WHERE\n\t c_id = {$course_id} AND\n\t code='" . Database::escape_string($survey_invitation['survey_code']) . "'";
$result = Database::query($sql);
if (Database::num_rows($result) > 1) {
if ($_POST['language']) {
$survey_invitation['survey_id'] = $_POST['language'];
} else {
echo '<form id="language" name="language" method="POST" action="' . api_get_self() . '?course=' . $_GET['course'] . '&invitationcode=' . $_GET['invitationcode'] . '">';
echo ' <select name="language">';
while ($row = Database::fetch_assoc($result)) {
echo '<option value="' . $row['survey_id'] . '">' . $row['lang'] . '</option>';
}
echo '</select>';
echo ' <input type="submit" name="Submit" value="' . get_lang('Ok') . '" />';
echo '</form>';
display::display_footer();
exit;
}
} else {
$row = Database::fetch_assoc($result);
$survey_invitation['survey_id'] = $row['survey_id'];
}
$sql = "SELECT count(*)\n\t FROM {$table_survey}\n\t WHERE\n\t c_id = {$course_id} AND\n\t survey_id = " . $survey_invitation['survey_id'] . " AND (\n title LIKE '%{$doc_url}%'\n or subtitle LIKE '%{$doc_url}%'\n or intro LIKE '%{$doc_url}%'\n or surveythanks LIKE '%{$doc_url}%'\n )\n\t\t UNION\n\t\t SELECT count(*)\n\t\t FROM {$table_survey_question}\n\t\t WHERE\n\t\t c_id = {$course_id} AND\n\t\t survey_id = " . $survey_invitation['survey_id'] . " AND (\n survey_question LIKE '%{$doc_url}%'\n or survey_question_comment LIKE '%{$doc_url}%'\n )\n\t\t UNION\n\t\t SELECT count(*)\n\t\t FROM {$table_survey_question_option}\n\t\t WHERE\n\t\t c_id = {$course_id} AND\n\t\t survey_id = " . $survey_invitation['survey_id'] . " AND (\n option_text LIKE '%{$doc_url}%'\n )";
$result = Database::query($sql);
if (Database::num_rows($result) == 0) {
Display::display_error_message(get_lang('WrongInvitationCode'), false);
Display::display_footer();
exit;
}
return $_course;
}
public static function get($inputData = array())
{
$limitQuery = "";
$limitShow = isset($inputData['limitShow']) ? $inputData['limitShow'] : 0;
$limitPage = isset($inputData['limitPage']) ? $inputData['limitPage'] : 0;
$limitPage = (int) $limitPage > 0 ? $limitPage : 0;
$limitPosition = $limitPage * (int) $limitShow;
$limitQuery = (int) $limitShow == 0 ? '' : " limit {$limitPosition},{$limitShow}";
$limitQuery = isset($inputData['limitQuery']) ? $inputData['limitQuery'] : $limitQuery;
$field = "id,parentid,date_added,title,url,status,sort_order";
$selectFields = isset($inputData['selectFields']) ? $inputData['selectFields'] : $field;
$whereQuery = isset($inputData['where']) ? $inputData['where'] : '';
$orderBy = isset($inputData['orderby']) ? $inputData['orderby'] : 'order by id desc';
$result = array();
$command = "select {$selectFields} from " . Database::getPrefix() . "links {$whereQuery}";
$command .= " {$orderBy}";
$queryCMD = isset($inputData['query']) ? $inputData['query'] : $command;
$queryCMD .= $limitQuery;
$cache = isset($inputData['cache']) ? $inputData['cache'] : 'yes';
$cacheTime = isset($inputData['cacheTime']) ? $inputData['cacheTime'] : -1;
$md5Query = md5($queryCMD);
if ($cache == 'yes') {
// Load dbcache
$loadCache = Cache::loadKey('dbcache/system/link/' . $md5Query, $cacheTime);
if ($loadCache != false) {
$loadCache = unserialize($loadCache);
return $loadCache;
}
// end load
}
$query = Database::query($queryCMD);
if (isset(Database::$error[5])) {
return false;
}
$inputData['isHook'] = isset($inputData['isHook']) ? $inputData['isHook'] : 'yes';
if ((int) $query->num_rows > 0) {
while ($row = Database::fetch_assoc($query)) {
if (isset($row['title'])) {
$row['title'] = String::decode($row['title']);
}
if (isset($row['date_added'])) {
$row['date_addedFormat'] = Render::dateFormat($row['date_added']);
}
if (isset($row['url']) && !preg_match('/^http/i', $row['url'])) {
if (preg_match('/^\\/(.*?)$/i', $row['url'], $matches)) {
$tmp = $matches[1];
$row['urlFormat'] = System::getUrl() . $tmp;
}
}
$result[] = $row;
}
} else {
return false;
}
// Save dbcache
Cache::saveKey('dbcache/system/link/' . $md5Query, serialize($result));
// end save
return $result;
}
public static function get($inputData = array())
{
$limitQuery = "";
$limitShow = isset($inputData['limitShow']) ? $inputData['limitShow'] : 0;
$limitPage = isset($inputData['limitPage']) ? $inputData['limitPage'] : 0;
$limitPage = (int) $limitPage > 0 ? $limitPage : 0;
$limitPosition = $limitPage * (int) $limitShow;
$limitQuery = (int) $limitShow == 0 ? '' : " limit {$limitPosition},{$limitShow}";
$limitQuery = isset($inputData['limitQuery']) ? $inputData['limitQuery'] : $limitQuery;
$field = "userid,groupid,username,firstname,lastname,image,email,password,userdata,ip,verify_code,parentid,date_added,forgot_code,forgot_date";
$selectFields = isset($inputData['selectFields']) ? $inputData['selectFields'] : $field;
$whereQuery = isset($inputData['where']) ? $inputData['where'] : '';
$orderBy = isset($inputData['orderby']) ? $inputData['orderby'] : 'order by date_added desc';
$result = array();
$prefix = '';
$prefixall = Database::isPrefixAll();
if ($prefixall != false || $prefixall == 'no') {
$prefix = Database::getPrefix();
}
$command = "select {$selectFields} from " . $prefix . "users {$whereQuery}";
$command .= " {$orderBy}";
$queryCMD = isset($inputData['query']) ? $inputData['query'] : $command;
$queryCMD .= $limitQuery;
$cache = isset($inputData['cache']) ? $inputData['cache'] : 'yes';
$cacheTime = isset($inputData['cacheTime']) ? $inputData['cacheTime'] : -1;
$md5Query = md5($queryCMD);
if ($cache == 'yes') {
// Load dbcache
$loadCache = Cache::loadKey('dbcache/system/user/' . $md5Query, $cacheTime);
if ($loadCache != false) {
$loadCache = unserialize($loadCache);
return $loadCache;
}
// end load
}
// echo $queryCMD;die();
$query = Database::query($queryCMD);
if (isset(Database::$error[5])) {
return false;
}
if ((int) $query->num_rows > 0) {
while ($row = Database::fetch_assoc($query)) {
if (isset($row['date_added'])) {
$row['date_addedFormat'] = Render::dateFormat($row['date_added']);
}
if (isset($row['image'])) {
$row['imageFormat'] = self::getAvatar($row['image']);
}
$result[] = $row;
}
} else {
return false;
}
// Save dbcache
Cache::saveKey('dbcache/system/user/' . $md5Query, serialize($result));
// end save
return $result;
}
public static function get($inputData = array())
{
$limitQuery = "";
$limitShow = isset($inputData['limitShow']) ? $inputData['limitShow'] : 0;
$limitPage = isset($inputData['limitPage']) ? $inputData['limitPage'] : 0;
$limitPage = (int) $limitPage > 0 ? $limitPage : 0;
$limitPosition = $limitPage * (int) $limitShow;
$limitQuery = (int) $limitShow == 0 ? '' : " limit {$limitPosition},{$limitShow}";
$limitQuery = isset($inputData['limitQuery']) ? $inputData['limitQuery'] : $limitQuery;
$field = "groupid,group_title,groupdata";
$selectFields = isset($inputData['selectFields']) ? $inputData['selectFields'] : $field;
$whereQuery = isset($inputData['where']) ? $inputData['where'] : '';
$orderBy = isset($inputData['orderby']) ? $inputData['orderby'] : 'order by groupid desc';
$result = array();
$prefix = '';
$prefixall = Database::isPrefixAll();
if ($prefixall != false || $prefixall == 'no') {
$prefix = Database::getPrefix();
}
$command = "select {$selectFields} from " . $prefix . "usergroups {$whereQuery}";
$command .= " {$orderBy}";
$queryCMD = isset($inputData['query']) ? $inputData['query'] : $command;
$queryCMD .= $limitQuery;
$cache = isset($inputData['cache']) ? $inputData['cache'] : 'yes';
$cacheTime = isset($inputData['cacheTime']) ? $inputData['cacheTime'] : 15;
$md5Query = md5($queryCMD);
if ($cache == 'yes') {
// Load dbcache
$loadCache = Cache::loadKey('dbcache/system/usergroup/' . $md5Query, $cacheTime);
if ($loadCache != false) {
$loadCache = unserialize($loadCache);
return $loadCache;
}
// end load
}
$query = Database::query($queryCMD);
if (isset(Database::$error[5])) {
return false;
}
$inputData['isHook'] = isset($inputData['isHook']) ? $inputData['isHook'] : 'yes';
if ((int) $query->num_rows > 0) {
while ($row = Database::fetch_assoc($query)) {
if (isset($row['date_added'])) {
$row['date_addedFormat'] = Render::dateFormat($row['date_added']);
}
if (isset($row['groupdata'])) {
$row['groupdata'] = self::arrayToLine($row['groupdata']);
}
$result[] = $row;
}
} else {
return false;
}
// Save dbcache
Cache::saveKey('dbcache/system/usergroup/' . $md5Query, serialize($result));
// end save
return $result;
}
public static function get($inputData = array())
{
$limitQuery = "";
$limitShow = isset($inputData['limitShow']) ? $inputData['limitShow'] : 0;
$limitPage = isset($inputData['limitPage']) ? $inputData['limitPage'] : 0;
$limitPage = (int) $limitPage > 0 ? $limitPage : 0;
$limitPosition = $limitPage * (int) $limitShow;
$limitQuery = (int) $limitShow == 0 ? '' : " limit {$limitPosition},{$limitShow}";
$limitQuery = isset($inputData['limitQuery']) ? $inputData['limitQuery'] : $limitQuery;
$field = "requestid,userid,total_request,date_added,status,comments";
$selectFields = isset($inputData['selectFields']) ? $inputData['selectFields'] : $field;
$whereQuery = isset($inputData['where']) ? $inputData['where'] : '';
$orderBy = isset($inputData['orderby']) ? $inputData['orderby'] : 'order by requestid desc';
$result = array();
$command = "select {$selectFields} from " . Database::getPrefix() . "request_payments {$whereQuery}";
$command .= " {$orderBy}";
$queryCMD = isset($inputData['query']) ? $inputData['query'] : $command;
$queryCMD .= $limitQuery;
$cache = isset($inputData['cache']) ? $inputData['cache'] : 'yes';
$cacheTime = isset($inputData['cacheTime']) ? $inputData['cacheTime'] : 1;
if ($cache == 'yes') {
// Load dbcache
$loadCache = DBCache::get($queryCMD, $cacheTime);
if ($loadCache != false) {
$loadCache = unserialize($loadCache);
return $loadCache;
}
// end load
}
$query = Database::query($queryCMD);
if (isset(Database::$error[5])) {
return false;
}
$inputData['isHook'] = isset($inputData['isHook']) ? $inputData['isHook'] : 'yes';
if ((int) $query->num_rows > 0) {
while ($row = Database::fetch_assoc($query)) {
if (isset($row['comments'])) {
$row['comments'] = String::decode($row['comments']);
}
if (isset($row['date_added'])) {
$row['date_addedFormat'] = Render::dateFormat($row['date_added']);
}
if ($inputData['isHook'] == 'yes') {
if (isset($row['comments'])) {
$row['comments'] = Shortcode::load($row['comments']);
}
}
$result[] = $row;
}
} else {
return false;
}
// Save dbcache
DBCache::make(md5($queryCMD), $result);
// end save
return $result;
}
public static function get($inputData = array())
{
$limitQuery = "";
$limitShow = isset($inputData['limitShow']) ? $inputData['limitShow'] : 0;
$limitPage = isset($inputData['limitPage']) ? $inputData['limitPage'] : 0;
$limitPage = (int) $limitPage > 0 ? $limitPage : 0;
$limitPosition = $limitPage * (int) $limitShow;
$limitQuery = (int) $limitShow == 0 ? '' : " limit {$limitPosition},{$limitShow}";
$limitQuery = isset($inputData['limitQuery']) ? $inputData['limitQuery'] : $limitQuery;
$field = "userid,company,firstname,lastname,address_1,address_2,city,state,postcode,country,phone,fax";
$selectFields = isset($inputData['selectFields']) ? $inputData['selectFields'] : $field;
$whereQuery = isset($inputData['where']) ? $inputData['where'] : '';
$orderBy = isset($inputData['orderby']) ? $inputData['orderby'] : 'order by userid desc';
$result = array();
$command = "select {$selectFields} from " . Database::getPrefix() . "address {$whereQuery}";
$command .= " {$orderBy}";
$queryCMD = isset($inputData['query']) ? $inputData['query'] : $command;
$queryCMD .= $limitQuery;
$cache = isset($inputData['cache']) ? $inputData['cache'] : 'yes';
$cacheTime = isset($inputData['cacheTime']) ? $inputData['cacheTime'] : -1;
$md5Query = md5($queryCMD);
if ($cache == 'yes') {
// Load dbcache
$loadCache = Cache::loadKey('dbcache/system/address/' . $md5Query, $cacheTime);
if ($loadCache != false) {
$loadCache = unserialize($loadCache);
return $loadCache;
}
// end load
}
$query = Database::query($queryCMD);
if (isset(Database::$error[5])) {
return false;
}
$inputData['isHook'] = isset($inputData['isHook']) ? $inputData['isHook'] : 'yes';
if ((int) $query->num_rows > 0) {
while ($row = Database::fetch_assoc($query)) {
if (isset($row['title'])) {
$row['title'] = String::decode($row['title']);
}
if (isset($row['friendly_url'])) {
$row['url'] = self::url($row);
}
if (isset($row['date_added'])) {
$row['date_addedFormat'] = Render::dateFormat($row['date_added']);
}
$result[] = $row;
}
} else {
return false;
}
// Save dbcache
Cache::saveKey('dbcache/system/address/' . $md5Query, serialize($result));
// end save
return $result;
}
/**
* Gets user info from external source
* @param string login
* @param string password
* @return user array with at least the following fields:
* firstname
* lastname
* status
* email
* login
* password
* or false if no data
* */
function external_get_user_info($login, $password)
{
//Those are the mandatory fields for user creation.
//See external_add_user function for all the fields you can have.
$table = USERINFO_TABLE;
$sql = "SELECT * from {$table} where username='******'";
$result = Database::query($sql);
if (Database::num_rows($result) == 0) {
//false password
return false;
}
$user_info = Database::fetch_assoc($result);
// User status
$admin = false;
switch ($user_info['status']) {
case 'admin':
$status = COURSEMANAGER;
$admin = true;
break;
case 'teacher':
$status = COURSEMANAGER;
break;
case 'user':
$status = STUDENT;
break;
default:
$status = STUDENT;
}
// Language
switch ($user_info['language']) {
case 'FR':
$language = 'french';
break;
case 'EN':
$language = 'english';
break;
default:
$language = 'english';
break;
}
//Can Send Message ?
$can_send_message = $user_info['can_send_message'] == 1 ? 'yes' : 'no';
$u = array('firstname' => $user_info['firstname'], 'lastname' => $user_info['lastname'], 'status' => $status, 'admin' => $admin, 'email' => $user_info['email'], 'username' => $user_info['username'], 'language' => $language, 'password' => DEFAULT_PASSWORD, 'courses' => $user_info['courses'], 'profile_link' => $user_info['profile_link'], 'worldwide_bu' => $user_info['worlwide_bu'], 'manager' => $user_info['manager'], 'extra' => array('position_title' => $user_info['position_title'], 'country' => $user_info['country'], 'job_family' => $user_info['job_family'], 'country_bu' => $user_info['country_bu'], 'worldwide_bu' => $user_info['worldwide_bu'], 'profile_link' => $user_info['profile_link'], 'can_send_message' => $can_send_message, 'update_type' => 'external_logininfo'));
return $u;
//Please return false if user does not exist
//return false;
}
public static function get($inputData = array())
{
$limitQuery = "";
$limitShow = isset($inputData['limitShow']) ? $inputData['limitShow'] : 0;
$limitPage = isset($inputData['limitPage']) ? $inputData['limitPage'] : 0;
$limitPage = (int) $limitPage > 0 ? $limitPage : 0;
$limitPosition = $limitPage * (int) $limitShow;
$limitQuery = (int) $limitShow == 0 ? '' : " limit {$limitPosition},{$limitShow}";
$limitQuery = isset($inputData['limitQuery']) ? $inputData['limitQuery'] : $limitQuery;
$field = "postid,catid";
$selectFields = isset($inputData['selectFields']) ? $inputData['selectFields'] : $field;
$whereQuery = isset($inputData['where']) ? $inputData['where'] : '';
$orderBy = isset($inputData['orderby']) ? $inputData['orderby'] : 'order by date_added desc';
$result = array();
$command = "select {$selectFields} from " . Database::getPrefix() . "post_categories {$whereQuery}";
$command .= " {$orderBy}";
$queryCMD = isset($inputData['query']) ? $inputData['query'] : $command;
$queryCMD .= $limitQuery;
$cache = isset($inputData['cache']) ? $inputData['cache'] : 'yes';
$cacheTime = isset($inputData['cacheTime']) ? $inputData['cacheTime'] : -1;
$md5Query = md5($queryCMD);
if ($cache == 'yes') {
// Load dbcache
$loadCache = Cache::loadKey('dbcache/system/postcategory/' . $md5Query, $cacheTime);
if ($loadCache != false) {
$loadCache = unserialize($loadCache);
return $loadCache;
}
// end load
}
$query = Database::query($queryCMD);
if (isset(Database::$error[5])) {
return false;
}
$inputData['isHook'] = isset($inputData['isHook']) ? $inputData['isHook'] : 'yes';
if ((int) $query->num_rows > 0) {
while ($row = Database::fetch_assoc($query)) {
$result[] = $row;
}
} else {
return false;
}
// Save dbcache
Cache::saveKey('dbcache/system/postcategory/' . $md5Query, serialize($result));
// end save
return $result;
}
public static function get($inputData = array())
{
$limitQuery = "";
$limitShow = isset($inputData['limitShow']) ? $inputData['limitShow'] : 0;
$limitPage = isset($inputData['limitPage']) ? $inputData['limitPage'] : 0;
$limitPage = (int) $limitPage > 0 ? $limitPage : 0;
$limitPosition = $limitPage * (int) $limitShow;
$limitQuery = (int) $limitShow == 0 ? '' : " limit {$limitPosition},{$limitShow}";
$limitQuery = isset($inputData['limitQuery']) ? $inputData['limitQuery'] : $limitQuery;
$field = "metaid,foldername,func,zonename,layoutname,layoutposition,content,status,type";
$selectFields = isset($inputData['selectFields']) ? $inputData['selectFields'] : $field;
$whereQuery = isset($inputData['where']) ? $inputData['where'] : '';
$orderBy = isset($inputData['orderby']) ? $inputData['orderby'] : 'order by metaid desc';
$result = array();
$command = "select {$selectFields} from plugins_meta {$whereQuery}";
$command .= " {$orderBy}";
$queryCMD = isset($inputData['query']) ? $inputData['query'] : $command;
$queryCMD .= $limitQuery;
$cache = isset($inputData['cache']) ? $inputData['cache'] : 'yes';
$cacheTime = isset($inputData['cacheTime']) ? $inputData['cacheTime'] : 15;
$md5Query = md5($queryCMD);
if ($cache == 'yes') {
// Load dbcache
$loadCache = Cache::loadKey('dbcache/system/pluginmeta/' . $md5Query, $cacheTime);
if ($loadCache != false) {
$loadCache = unserialize($loadCache);
return $loadCache;
}
// end load
}
$query = Database::query($queryCMD);
if (isset(Database::$error[5])) {
return false;
}
if ((int) $query->num_rows > 0) {
while ($row = Database::fetch_assoc($query)) {
$result[] = $row;
}
} else {
return false;
}
// Save dbcache
Cache::saveKey('dbcache/system/pluginmeta/' . $md5Query, serialize($result));
// end save
return $result;
}
/**
* Get recent posts
*
* Returns an array of all recent posts, given a certain posts amount.
*
* @param int $amount Number of posts to get (defaults to 10).
* @param int $offset How many posts to skip.
* @return array Array of {@link Post} instances of all recent posts.
*/
function getRecentPosts($amount = '%', $offset = 0)
{
// default amount from setting
if ($amount == '%') {
$amount = $this->getSetting('recent_count');
}
// db query
$sql = 'SELECT `postid`, `time`, `content`, `comments_count` ' . 'FROM `+posts` ' . 'WHERE 1 ' . 'ORDER BY `postid` DESC ' . 'LIMIT ' . ($offset + 0) . ',' . ($amount + 0);
$q = $this->db->query($sql) or $this->displayError('site->getRecentPosts', $this->db->error());
$recent = array();
while ($postdata = $this->db->fetch_assoc($q)) {
$post = new Post($this);
$post->fetch_from_array($postdata);
$recent[] = $post;
}
// while
return $recent;
}
function storage_get_all_users()
{
$sql = "select user_id, username, firstname, lastname\n\t\tfrom " . Database::get_main_table(TABLE_MAIN_USER) . "\n\t\torder by user_id asc";
$res = Database::query($sql);
$results = array();
while ($row = Database::fetch_assoc($res)) {
$results[] = $row;
}
return json_encode($results);
}
$all_visible_files_path[] = $all_visible_files['path'];
$files[$all_visible_files['path']] = $all_visible_files;
}
// 2nd: Get all folders that are invisible in the given path
$sql = "SELECT path, session_id, docs.id, props.to_group_id, docs.c_id\n FROM {$doc_table} AS docs INNER JOIN {$prop_table} AS props\n ON\n docs.id = props.ref AND\n docs.c_id = props.c_id\n WHERE\n docs.c_id = {$courseId} AND\n props.tool = '" . TOOL_DOCUMENT . "' AND\n docs.path LIKE '" . $querypath . "/%' AND\n props.visibility <> '1' AND\n (props.session_id IN ('0', '{$sessionId}') OR props.session_id IS NULL) AND\n docs.filetype = 'folder'";
$query2 = Database::query($sql);
// If we get invisible folders, we have to filter out these results from all visible files we found
if (Database::num_rows($query2) > 0) {
$files = array();
// Add item to an array
while ($invisible_folders = Database::fetch_assoc($query2)) {
//3rd: Get all files that are in the found invisible folder (these are "invisible" too)
$sql = "SELECT path, docs.id, props.to_group_id, docs.c_id\n FROM {$doc_table} AS docs\n INNER JOIN {$prop_table} AS props\n ON\n docs.id = props.ref AND\n docs.c_id = props.c_id\n WHERE\n docs.c_id = {$courseId} AND\n props.tool ='" . TOOL_DOCUMENT . "' AND\n docs.path LIKE '" . $invisible_folders['path'] . "/%' AND\n docs.filetype = 'file' AND\n (props.session_id IN ('0', '{$sessionId}') OR props.session_id IS NULL) AND\n props.visibility ='1'";
$query3 = Database::query($sql);
// Add tem to an array
while ($files_in_invisible_folder = Database::fetch_assoc($query3)) {
$files_in_invisible_folder_path[] = $files_in_invisible_folder['path'];
$files[$files_in_invisible_folder['path']] = $files_in_invisible_folder;
}
}
// Compare the array with visible files and the array with files in invisible folders
// and keep the difference (= all visible files that are not in an invisible folder)
$files_for_zipfile = diff((array) $all_visible_files_path, (array) $files_in_invisible_folder_path);
} else {
// No invisible folders found, so all visible files can be added to the zipfile
$files_for_zipfile = $all_visible_files_path;
}
Session::write('doc_files_to_download', $files);
// Add all files in our final array to the zipfile
for ($i = 0; $i < count($files_for_zipfile); $i++) {
$zip->add($sysCoursePath . $courseInfo['path'] . '/document' . $files_for_zipfile[$i], PCLZIP_OPT_REMOVE_PATH, $sysCoursePath . $courseInfo['path'] . '/document' . $remove_dir, PCLZIP_CB_PRE_ADD, 'fixDocumentNameCallback');
/**
* Get the subgroups ID from a group.
* The default $levels value is 10 considering it as a extensive level of depth
* @param int $groupId The parent group ID
* @param int $levels The depth levels
* @return array The list of ID
*/
public static function getGroupsByDepthLevel($groupId, $levels = 10)
{
$groups = array();
$groupId = intval($groupId);
$groupTable = Database::get_main_table(TABLE_USERGROUP);
$groupRelGroupTable = Database::get_main_table(TABLE_USERGROUP_REL_USERGROUP);
$select = "SELECT ";
$from = "FROM {$groupTable} g1 ";
for ($i = 1; $i <= $levels; $i++) {
$tableIndexNumber = $i;
$tableIndexJoinNumber = $i - 1;
$select .= "g{$i}.id as id_{$i} ";
$select .= $i != $levels ? ", " : null;
if ($i == 1) {
$from .= "INNER JOIN {$groupRelGroupTable} gg0 ON g1.id = gg0.subgroup_id and gg0.group_id = {$groupId} ";
} else {
$from .= "LEFT JOIN {$groupRelGroupTable} gg{$tableIndexJoinNumber} ";
$from .= " ON g{$tableIndexJoinNumber}.id = gg{$tableIndexJoinNumber}.group_id ";
$from .= "LEFT JOIN {$groupTable} g{$tableIndexNumber} ";
$from .= " ON gg{$tableIndexJoinNumber}.subgroup_id = g{$tableIndexNumber}.id ";
}
}
$result = Database::query("{$select} {$from}");
while ($item = Database::fetch_assoc($result)) {
foreach ($item as $groupId) {
if (!empty($groupId)) {
$groups[] = $groupId;
}
}
}
return array_map('intval', $groups);
}
public static function get($inputData = array())
{
$limitQuery = "";
$limitShow = isset($inputData['limitShow']) ? $inputData['limitShow'] : 0;
$limitPage = isset($inputData['limitPage']) ? $inputData['limitPage'] : 0;
$limitPage = (int) $limitPage > 0 ? $limitPage : 0;
$limitPosition = $limitPage * (int) $limitShow;
$limitQuery = (int) $limitShow == 0 ? '' : " limit {$limitPosition},{$limitShow}";
$limitQuery = isset($inputData['limitQuery']) ? $inputData['limitQuery'] : $limitQuery;
$field = "commentid,postid,type,fullname,email,parentid,date_added,status,content";
$selectFields = isset($inputData['selectFields']) ? $inputData['selectFields'] : $field;
$whereQuery = isset($inputData['where']) ? $inputData['where'] : '';
$orderBy = isset($inputData['orderby']) ? $inputData['orderby'] : 'order by commentid desc';
$result = array();
$command = "select {$selectFields} from " . Database::getPrefix() . "comments {$whereQuery}";
$command .= " {$orderBy}";
$queryCMD = isset($inputData['query']) ? $inputData['query'] : $command;
$queryCMD .= $limitQuery;
$cache = isset($inputData['cache']) ? $inputData['cache'] : 'yes';
$cacheTime = isset($inputData['cacheTime']) ? $inputData['cacheTime'] : -1;
$md5Query = md5($queryCMD);
if ($cache == 'yes') {
// Load dbcache
$loadCache = Cache::loadKey('dbcache/system/comment/' . $md5Query, $cacheTime);
if ($loadCache != false) {
$loadCache = unserialize($loadCache);
return $loadCache;
}
// end load
}
$query = Database::query($queryCMD);
if (isset(Database::$error[5])) {
return false;
}
$inputData['isHook'] = isset($inputData['isHook']) ? $inputData['isHook'] : 'yes';
if ((int) $query->num_rows > 0) {
while ($row = Database::fetch_assoc($query)) {
if (isset($row['fullname'])) {
$row['fullname'] = String::decode($row['fullname']);
}
if (isset($row['content'])) {
$row['content'] = String::decode($row['content']);
}
if (isset($row['date_added'])) {
$row['date_addedFormat'] = Render::dateFormat($row['date_added']);
}
if ($inputData['isHook'] == 'yes') {
if (isset($row['content'])) {
$row['content'] = Shortcode::toHTML($row['content']);
}
}
$result[] = $row;
}
} else {
return false;
}
// Save dbcache
Cache::saveKey('dbcache/system/comment/' . $md5Query, serialize($result));
// end save
return $result;
}
/**
* Displays a list with posts a user can select to execute his task.
*
* @param Integer $blog_id
* @param unknown_type $task_id
*/
public static function display_select_task_post($blog_id, $task_id)
{
// Init
$tbl_blogs_tasks = Database::get_course_table(TABLE_BLOGS_TASKS);
$tbl_blogs_posts = Database::get_course_table(TABLE_BLOGS_POSTS);
$tbl_users = Database::get_main_table(TABLE_MAIN_USER);
$course_id = api_get_course_int_id();
$sql = "SELECT title, description FROM {$tbl_blogs_tasks}\n\t\t\t\tWHERE task_id = '" . (int) $task_id . "'\n\t\t\t\tAND c_id = {$course_id}";
$result = Database::query($sql);
$row = Database::fetch_assoc($result);
// Get posts and authors
$sql = "SELECT post.*, user.lastname, user.firstname, user.username\n\t\t\t\tFROM {$tbl_blogs_posts} post\n\t\t\t\tINNER JOIN {$tbl_users} user ON post.author_id = user.user_id\n\t\t\t\tWHERE post.blog_id = '" . (int) $blog_id . "' AND post.c_id = {$course_id}\n\t\t\t\tORDER BY post_id DESC\n\t\t\t\tLIMIT 0, 100";
$result = Database::query($sql);
// Display
echo '<span class="blogpost_title">' . get_lang('SelectTaskArticle') . ' "' . stripslashes($row['title']) . '"</span>';
echo '<span style="font-style: italic;"">' . stripslashes($row['description']) . '</span><br><br>';
if (Database::num_rows($result) > 0) {
while ($blog_post = Database::fetch_array($result)) {
$username = api_htmlentities(sprintf(get_lang('LoginX'), $blog_post['username']), ENT_QUOTES);
echo '<a href="blog.php?action=execute_task&blog_id=' . $blog_id . '&task_id=' . $task_id . '&post_id=' . $blog_post['post_id'] . '#add_comment">' . stripslashes($blog_post['title']) . '</a>, ' . get_lang('WrittenBy') . ' ' . stripslashes(Display::tag('span', api_get_person_name($blog_post['firstname'], $blog_post['lastname']), array('title' => $username))) . '<br />';
}
} else {
echo get_lang('NoArticles');
}
}
}
if (!empty($data)) {
$data[] = array('id' => 'T', 'text' => 'TODOS');
echo json_encode($data);
} else {
echo json_encode(array(array('id' => 'T', 'text' => 'TODOS')));
}
}
break;
case 'search_survey_by_course':
if (api_is_platform_admin()) {
$survey = Database::get_course_table(TABLE_SURVEY);
$sql = "SELECT survey_id as id, title, anonymous\n FROM {$survey}\n WHERE\n c_id = %d AND\n session_id = %d AND\n title LIKE '%s'";
$sql_query = sprintf($sql, intval($_GET['course_id']), intval($_GET['session_id']), '%' . Database::escape_string($_GET['q']) . '%');
$result = Database::query($sql_query);
while ($survey = Database::fetch_assoc($result)) {
$survey['title'] .= $survey['anonymous'] == 1 ? ' (' . get_lang('Anonymous') . ')' : '';
$data[] = array('id' => $survey['id'], 'text' => strip_tags(html_entity_decode($survey['title'])));
}
if (!empty($data)) {
echo json_encode($data);
} else {
echo json_encode(array());
}
}
break;
case 'display_sessions_courses':
$sessionId = intval($_GET['session']);
$userTable = Database::get_main_table(TABLE_MAIN_USER);
$coursesData = SessionManager::get_course_list_by_session_id($sessionId);
$courses = array();
/**
* Displays the menu for manipulating a step
* @return string html
*/
public function display_manipulate($item_id, $item_type = TOOL_DOCUMENT)
{
$course_id = api_get_course_int_id();
$course_code = api_get_course_id();
$_course = api_get_course_info();
$return = '<div class="actions">';
switch ($item_type) {
case 'dokeos_chapter':
case 'chapter':
// Commented the message cause should not show it.
//$lang = get_lang('TitleManipulateChapter');
break;
case 'dokeos_module':
case 'module':
// Commented the message cause should not show it.
//$lang = get_lang('TitleManipulateModule');
break;
case TOOL_DOCUMENT:
// Commented the message cause should not show it.
//$lang = get_lang('TitleManipulateDocument');
break;
case TOOL_LINK:
case 'link':
// Commented the message cause should not show it.
//$lang = get_lang('TitleManipulateLink');
break;
case TOOL_QUIZ:
// Commented the message cause should not show it.
//$lang = get_lang('TitleManipulateQuiz');
break;
case TOOL_STUDENTPUBLICATION:
// Commented the message cause should not show it.
//$lang = get_lang('TitleManipulateStudentPublication');
break;
}
$tbl_lp_item = Database::get_course_table(TABLE_LP_ITEM);
$item_id = intval($item_id);
$sql = "SELECT * FROM " . $tbl_lp_item . " as lp WHERE lp.c_id = " . $course_id . " AND lp.id = " . $item_id;
$result = Database::query($sql);
$row = Database::fetch_assoc($result);
$webCodepath = api_get_path(WEB_CODE_PATH);
$audio_player = null;
// We display an audio player if needed.
if (!empty($row['audio'])) {
$audio_player .= '<div class="lp_mediaplayer" id="container">
<a href="http://www.macromedia.com/go/getflashplayer">Get the Flash Player</a> to see this player.</div>';
$audio_player .= '<script type="text/javascript" src="' . $webCodepath . 'inc/lib/mediaplayer/swfobject.js"></script>';
$audio_player .= '<script>
var s1 = new SWFObject("' . $webCodepath . 'inc/lib/mediaplayer/player.swf","ply","250","20","9","#FFFFFF");
s1.addParam("allowscriptaccess","always");
s1.addParam("flashvars","file=' . api_get_path(WEB_PUBLIC_PATH) . 'courses/' . $_course['path'] . '/document/audio/' . $row['audio'] . '&autostart=true");
s1.write("container");
</script>';
}
$url = api_get_self() . '?cidReq=' . Security::remove_XSS($_GET['cidReq']) . '&view=build&id=' . $item_id . '&lp_id=' . $this->lp_id;
$return .= Display::url(Display::return_icon('edit.png', get_lang('Edit'), array(), ICON_SIZE_SMALL), $url . '&action=edit_item&path_item=' . $row['path']);
$return .= Display::url(Display::return_icon('move.png', get_lang('Move'), array(), ICON_SIZE_SMALL), $url . '&action=move_item');
// Commented for now as prerequisites cannot be added to chapters.
if ($item_type != 'dokeos_chapter' && $item_type != 'chapter') {
$return .= Display::url(Display::return_icon('accept.png', get_lang('LearnpathPrerequisites'), array(), ICON_SIZE_SMALL), $url . '&action=edit_item_prereq');
}
$return .= Display::url(Display::return_icon('delete.png', get_lang('Delete'), array(), ICON_SIZE_SMALL), $url . '&action=delete_item');
if ($item_type == TOOL_HOTPOTATOES) {
$document_data = DocumentManager::get_document_data_by_id($row['path'], $course_code);
$return .= get_lang('File') . ': ' . $document_data['absolute_path_from_document'];
}
if ($item_type == TOOL_DOCUMENT) {
$document_data = DocumentManager::get_document_data_by_id($row['path'], $course_code);
$return .= get_lang('File') . ': ' . $document_data['absolute_path_from_document'];
}
$return .= '</div>';
if (!empty($audio_player)) {
$return .= '<br />' . $audio_player;
}
return $return;
}
/**
* Get the data of the last received messages for a user
* @param int $userId The user id
* @param int $lastId The id of the last received message
* @return int The count of new messages
*/
public static function getMessagesFromLastReceivedMessage($userId, $lastId = 0)
{
$userId = intval($userId);
$lastId = intval($lastId);
if (empty($userId)) {
return 0;
}
$messagesTable = Database::get_main_table(TABLE_MESSAGE);
$userTable = Database::get_main_table(TABLE_MAIN_USER);
$messages = array();
$sql = "SELECT m.*, u.user_id, u.lastname, u.firstname\n FROM {$messagesTable} as m\n INNER JOIN {$userTable} as u\n ON m.user_sender_id = u.user_id\n WHERE\n m.user_receiver_id = {$userId} AND\n m.msg_status = " . MESSAGE_STATUS_UNREAD . "\n AND m.id > {$lastId}\n ORDER BY m.send_date DESC";
$result = Database::query($sql);
if ($result !== false) {
while ($row = Database::fetch_assoc($result)) {
$messages[] = $row;
}
}
return $messages;
}
function fullexportspecial()
{
global $tbl_session, $tbl_session_course, $export;
$FileZip = create_zip();
$to_group_id = 0;
$code_course = '';
$list_course = array();
$zip_folder = new PclZip($FileZip['TEMP_FILE_ZIP']);
$list_course = CourseManager::get_course_list();
$tbl_document = Database::get_course_table(TABLE_DOCUMENT);
$tbl_property = Database::get_course_table(TABLE_ITEM_PROPERTY);
$groupCondition = " props.to_group_id = {$to_group_id}";
if (empty($to_group_id)) {
$groupCondition = " (props.to_group_id = 0 OR props.to_group_id IS NULL)";
}
if (count($list_course) > 0) {
foreach ($list_course as $_course) {
if ($FileZip['PATH'] == '/') {
$querypath = '';
// to prevent ...path LIKE '//%'... in query
} else {
$querypath = $FileZip['PATH'];
}
$course_id = $_course['real_id'];
//Add tem to the zip file course
$sql = "SELECT path FROM {$tbl_document} AS docs, {$tbl_property} AS props\n WHERE props.tool='" . TOOL_DOCUMENT . "'\n AND docs.id=props.ref\n AND docs.path LIKE '" . $querypath . "/%'\n AND docs.filetype='file'\n AND (docs.session_id = '0' OR docs.session_id IS NULL)\n AND props.visibility<>'2'\n AND {$groupCondition}\n AND docs.c_id = {$course_id}\n AND props.c_id = {$course_id}";
$query = Database::query($sql);
while ($rows_course_file = Database::fetch_assoc($query)) {
$rows_course_file['path'];
$zip_folder->add($FileZip['PATH_COURSE'] . $_course['directory'] . "/document" . $rows_course_file['path'], PCLZIP_OPT_ADD_PATH, $_course['directory'], PCLZIP_OPT_REMOVE_PATH, $FileZip['PATH_COURSE'] . $_course['directory'] . "/document" . $FileZip['PATH_REMOVE']);
}
//Add tem to the zip file session course
$code_course = $_course['code'];
$sql_session = "SELECT s.id, name, c_id\n FROM {$tbl_session_course} sc\n INNER JOIN {$tbl_session} s\n ON sc.session_id = s.id\n WHERE c_id = '{$course_id}' ";
$query_session = Database::query($sql_session);
while ($rows_session = Database::fetch_assoc($query_session)) {
$session_id = $rows_session['id'];
$sql_session_doc = "SELECT path FROM {$tbl_document} AS docs, {$tbl_property} AS props\n WHERE props.tool='" . TOOL_DOCUMENT . "'\n AND docs.id=props.ref\n AND docs.path LIKE '" . $querypath . "/%'\n AND docs.filetype='file'\n AND docs.session_id = '{$session_id}'\n AND props.visibility<>'2'\n AND {$groupCondition}\n AND docs.c_id = {$course_id}\n AND props.c_id = {$course_id} ";
$query_session_doc = Database::query($sql_session_doc);
while ($rows_course_session_file = Database::fetch_assoc($query_session_doc)) {
$zip_folder->add($FileZip['PATH_COURSE'] . $_course['directory'] . '/document' . $rows_course_session_file['path'], PCLZIP_OPT_ADD_PATH, $_course['directory'] . "/" . $rows_session['name'], PCLZIP_OPT_REMOVE_PATH, $FileZip['PATH_COURSE'] . $_course['directory'] . '/document' . $FileZip['PATH_REMOVE']);
}
}
}
$name = rename_zip($FileZip);
if ($name === false) {
$export = false;
return false;
} else {
$export = true;
return $name;
}
} else {
Display::display_error_message(get_lang('ErrorMsgSpecialExport'));
//main API
$export = false;
return false;
}
}
/**
* Gives a list of [session_id-course_code] => [status] for the current user.
* @param integer $user_id
* @return array list of statuses (session_id-course_code => status)
*/
public static function get_personal_session_course_list($user_id)
{
// Database Table Definitions
$tbl_course = Database::get_main_table(TABLE_MAIN_COURSE);
$tbl_user = Database::get_main_table(TABLE_MAIN_USER);
$tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
$tbl_session_user = Database::get_main_table(TABLE_MAIN_SESSION_USER);
$tbl_course_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
$tbl_session_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
if ($user_id != strval(intval($user_id))) {
return array();
}
// We filter the courses from the URL
$join_access_url = $where_access_url = '';
if (api_get_multiple_access_url()) {
$access_url_id = api_get_current_access_url_id();
if ($access_url_id != -1) {
$tbl_url_course = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
$join_access_url = "LEFT JOIN {$tbl_url_course} url_rel_course ON url_rel_course.c_id = course.id";
$where_access_url = " AND access_url_id = {$access_url_id} ";
}
}
// Courses in which we subscribed out of any session
$tbl_user_course_category = Database::get_main_table(TABLE_USER_COURSE_CATEGORY);
$sql = "SELECT\n course.code,\n course_rel_user.status course_rel_status,\n course_rel_user.sort sort,\n course_rel_user.user_course_cat user_course_cat\n FROM " . $tbl_course_user . " course_rel_user\n LEFT JOIN " . $tbl_course . " course\n ON course.id = course_rel_user.c_id\n LEFT JOIN " . $tbl_user_course_category . " user_course_category\n ON course_rel_user.user_course_cat = user_course_category.id\n {$join_access_url}\n WHERE\n course_rel_user.user_id = '" . $user_id . "' AND\n course_rel_user.relation_type <> " . COURSE_RELATION_TYPE_RRHH . "\n {$where_access_url}\n ORDER BY user_course_category.sort, course_rel_user.sort, course.title ASC";
$course_list_sql_result = Database::query($sql);
$personal_course_list = array();
if (Database::num_rows($course_list_sql_result) > 0) {
while ($result_row = Database::fetch_array($course_list_sql_result, 'ASSOC')) {
$course_info = api_get_course_info($result_row['code']);
$result_row['course_info'] = $course_info;
$personal_course_list[] = $result_row;
}
}
$coachCourseConditions = null;
// Getting sessions that are related to a coach in the session_rel_course_rel_user table
if (api_is_allowed_to_create_course()) {
$sessionListFromCourseCoach = array();
$sql = " SELECT DISTINCT session_id\n FROM {$tbl_session_course_user}\n WHERE user_id = {$user_id} AND status = 2 ";
$result = Database::query($sql);
if (Database::num_rows($result)) {
$result = Database::store_result($result);
foreach ($result as $session) {
$sessionListFromCourseCoach[] = $session['session_id'];
}
}
if (!empty($sessionListFromCourseCoach)) {
$condition = implode("','", $sessionListFromCourseCoach);
$coachCourseConditions = " OR ( s.id IN ('{$condition}'))";
}
}
// Get the list of sessions where the user is subscribed
// This is divided into two different queries
$sessions = array();
$sql = "SELECT DISTINCT s.id, name, access_start_date, access_end_date\n FROM {$tbl_session_user}, {$tbl_session} s\n WHERE (\n session_id = s.id AND\n user_id = {$user_id} AND\n relation_type <> " . SESSION_RELATION_TYPE_RRHH . "\n )\n {$coachCourseConditions}\n ORDER BY access_start_date, access_end_date, name";
$result = Database::query($sql);
if (Database::num_rows($result) > 0) {
while ($row = Database::fetch_assoc($result)) {
$sessions[$row['id']] = $row;
}
}
$sql = "SELECT DISTINCT\n id, name, access_start_date, access_end_date\n FROM {$tbl_session} s\n WHERE (\n id_coach = {$user_id}\n )\n {$coachCourseConditions}\n ORDER BY access_start_date, access_end_date, name";
$result = Database::query($sql);
if (Database::num_rows($result) > 0) {
while ($row = Database::fetch_assoc($result)) {
if (empty($sessions[$row['id']])) {
$sessions[$row['id']] = $row;
}
}
}
if (api_is_allowed_to_create_course()) {
foreach ($sessions as $enreg) {
$session_id = $enreg['id'];
$session_visibility = api_get_session_visibility($session_id);
if ($session_visibility == SESSION_INVISIBLE) {
continue;
}
// This query is horribly slow when more than a few thousand
// users and just a few sessions to which they are subscribed
$id_session = $enreg['id'];
$personal_course_list_sql = "SELECT DISTINCT\n course.code code,\n course.title i,\n " . (api_is_western_name_order() ? "CONCAT(user.firstname,' ',user.lastname)" : "CONCAT(user.lastname,' ',user.firstname)") . " t,\n email, course.course_language l,\n 1 sort,\n category_code user_course_cat,\n access_start_date,\n access_end_date,\n session.id as session_id,\n session.name as session_name\n FROM {$tbl_session_course_user} as session_course_user\n INNER JOIN {$tbl_course} AS course\n ON course.id = session_course_user.c_id\n INNER JOIN {$tbl_session} as session\n ON session.id = session_course_user.session_id\n LEFT JOIN {$tbl_user} as user\n ON user.id = session_course_user.user_id OR session.id_coach = user.id\n WHERE\n session_course_user.session_id = {$id_session} AND (\n (session_course_user.user_id = {$user_id} AND session_course_user.status = 2)\n OR session.id_coach = {$user_id}\n )\n ORDER BY i";
$course_list_sql_result = Database::query($personal_course_list_sql);
while ($result_row = Database::fetch_array($course_list_sql_result, 'ASSOC')) {
$result_row['course_info'] = api_get_course_info($result_row['code']);
$key = $result_row['session_id'] . ' - ' . $result_row['code'];
$personal_course_list[$key] = $result_row;
}
}
}
foreach ($sessions as $enreg) {
$session_id = $enreg['id'];
$session_visibility = api_get_session_visibility($session_id);
if ($session_visibility == SESSION_INVISIBLE) {
continue;
}
/* This query is very similar to the above query,
but it will check the session_rel_course_user table if there are courses registered to our user or not */
$personal_course_list_sql = "SELECT DISTINCT\n course.code code,\n course.title i, CONCAT(user.lastname,' ',user.firstname) t,\n email,\n course.course_language l,\n 1 sort,\n category_code user_course_cat,\n access_start_date,\n access_end_date,\n session.id as session_id,\n session.name as session_name,\n IF((session_course_user.user_id = 3 AND session_course_user.status=2),'2', '5')\n FROM {$tbl_session_course_user} as session_course_user\n INNER JOIN {$tbl_course} AS course\n ON course.id = session_course_user.c_id AND session_course_user.session_id = {$session_id}\n INNER JOIN {$tbl_session} as session ON session_course_user.session_id = session.id\n LEFT JOIN {$tbl_user} as user ON user.id = session_course_user.user_id\n WHERE session_course_user.user_id = {$user_id}\n ORDER BY i";
$course_list_sql_result = Database::query($personal_course_list_sql);
while ($result_row = Database::fetch_array($course_list_sql_result, 'ASSOC')) {
$result_row['course_info'] = api_get_course_info($result_row['code']);
$key = $result_row['session_id'] . ' - ' . $result_row['code'];
if (!isset($personal_course_list[$key])) {
$personal_course_list[$key] = $result_row;
}
}
}
return $personal_course_list;
}
/**
* Get list of courses based on users of a group for a group admin
* @param int $userId The user id
* @return array
*/
public static function getCoursesFollowedByGroupAdmin($userId)
{
$coursesList = [];
$courseTable = Database::get_main_table(TABLE_MAIN_COURSE);
$courseUserTable = Database::get_main_table(TABLE_MAIN_COURSE_USER);
$userGroup = new UserGroup();
$userIdList = $userGroup->getGroupUsersByUser($userId);
if (empty($userIdList)) {
return [];
}
$sql = "SELECT DISTINCT(c.id), c.title\n FROM {$courseTable} c\n INNER JOIN {$courseUserTable} cru ON c.id = cru.c_id\n WHERE (\n cru.user_id IN (" . implode(', ', $userIdList) . ")\n AND cru.relation_type = 0\n )";
if (api_is_multiple_url_enabled()) {
$courseAccessUrlTable = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
$accessUrlId = api_get_current_access_url_id();
if ($accessUrlId != -1) {
$sql = "SELECT DISTINCT(c.id), c.title\n FROM {$courseTable} c\n INNER JOIN {$courseUserTable} cru ON c.id = cru.c_id\n INNER JOIN {$courseAccessUrlTable} crau ON c.id = crau.c_id\n WHERE crau.access_url_id = {$accessUrlId}\n AND (\n cru.id_user IN (" . implode(', ', $userIdList) . ") AND\n cru.relation_type = 0\n )";
}
}
$result = Database::query($sql);
while ($row = Database::fetch_assoc($result)) {
$coursesList[] = $row;
}
return $coursesList;
}
/**
* Get the progress of a exercise
* @param int $sessionId The session ID (session.id)
* @param int $courseId The course ID (course.id)
* @param int $exerciseId The quiz ID (c_quiz.id)
* @param int $answer The answer status (0 = incorrect, 1 = correct, 2 = both)
* @param array $options An array of options you can pass to the query (limit, where and order)
* @return array An array with the data of exercise(s) progress
*/
public static function get_exercise_progress($sessionId = 0, $courseId = 0, $exerciseId = 0, $date_from = null, $date_to = null, $options = array())
{
$sessionId = intval($sessionId);
$courseId = intval($courseId);
$exerciseId = intval($exerciseId);
$date_from = Database::escape_string($date_from);
$date_to = Database::escape_string($date_to);
/*
* This method gets the data by blocks, as previous attempts at one single
* query made it take ages. The logic of query division is described below
*/
// Get tables names
$tuser = Database::get_main_table(TABLE_MAIN_USER);
$tquiz = Database::get_course_table(TABLE_QUIZ_TEST);
$tquiz_answer = Database::get_course_table(TABLE_QUIZ_ANSWER);
$tquiz_question = Database::get_course_table(TABLE_QUIZ_QUESTION);
$tquiz_rel_question = Database::get_course_table(TABLE_QUIZ_TEST_QUESTION);
$ttrack_exercises = Database::get_main_table(TABLE_STATISTIC_TRACK_E_EXERCISES);
$ttrack_attempt = Database::get_main_table(TABLE_STATISTIC_TRACK_E_ATTEMPT);
$sessions = array();
$courses = array();
// if session ID is defined but course ID is empty, get all the courses
// from that session
if (!empty($sessionId) && empty($courseId)) {
// $courses is an array of course int id as index and course details hash as value
$courses = SessionManager::get_course_list_by_session_id($sessionId);
$sessions[$sessionId] = api_get_session_info($sessionId);
} elseif (empty($sessionId) && !empty($courseId)) {
// if, to the contrary, course is defined but not sessions, get the sessions that include this course
// $sessions is an array like: [0] => ('id' => 3, 'name' => 'Session 35'), [1] => () etc;
$course = api_get_course_info_by_id($courseId);
$sessionsTemp = SessionManager::get_session_by_course($courseId);
$courses[$courseId] = $course;
foreach ($sessionsTemp as $sessionItem) {
$sessions[$sessionItem['id']] = $sessionItem;
}
} elseif (!empty($courseId) && !empty($sessionId)) {
//none is empty
$course = api_get_course_info_by_id($courseId);
$courses[$courseId] = array($course['code']);
$courses[$courseId]['code'] = $course['code'];
$sessions[$sessionId] = api_get_session_info($sessionId);
} else {
//both are empty, not enough data, return an empty array
return array();
}
// Now we have two arrays of courses and sessions with enough data to proceed
// If no course could be found, we shouldn't return anything.
// Sessions can be empty (then we only return the pure-course-context results)
if (count($courses) < 1) {
return array();
}
$data = array();
// The following loop is less expensive than what it seems:
// - if a course was defined, then we only loop through sessions
// - if a session was defined, then we only loop through courses
// - if a session and a course were defined, then we only loop once
foreach ($courses as $courseIdx => $courseData) {
$where = '';
$whereParams = array();
$whereSessionParams = '';
if (count($sessions > 0)) {
foreach ($sessions as $sessionIdx => $sessionData) {
if (!empty($sessionIdx)) {
$whereSessionParams .= $sessionIdx . ',';
}
}
$whereSessionParams = substr($whereSessionParams, 0, -1);
}
if (!empty($exerciseId)) {
$exerciseId = intval($exerciseId);
$where .= ' AND q.id = %d ';
$whereParams[] = $exerciseId;
}
/*
* This feature has been disabled for now, to avoid having to
* join two very large tables
//2 = show all questions (wrong and correct answered)
if ($answer != 2) {
$answer = intval($answer);
//$where .= ' AND qa.correct = %d';
//$whereParams[] = $answer;
}
*/
$limit = '';
if (!empty($options['limit'])) {
$limit = " LIMIT " . $options['limit'];
}
if (!empty($options['where'])) {
$where .= ' AND ' . Database::escape_string($options['where']);
}
$order = '';
if (!empty($options['order'])) {
$order = " ORDER BY " . $options['order'];
}
if (!empty($date_to) && !empty($date_from)) {
$where .= sprintf(" AND (te.start_date BETWEEN '%s 00:00:00' AND '%s 23:59:59')", $date_from, $date_to);
}
$sql = "SELECT\n te.session_id,\n ta.id as attempt_id,\n te.exe_user_id as user_id,\n te.exe_id as exercise_attempt_id,\n ta.question_id,\n ta.answer as answer_id,\n ta.tms as time,\n te.exe_exo_id as quiz_id,\n CONCAT ('c', q.c_id, '_e', q.id) as exercise_id,\n q.title as quiz_title,\n qq.description as description\n FROM {$ttrack_exercises} te\n INNER JOIN {$ttrack_attempt} ta ON ta.exe_id = te.exe_id\n INNER JOIN {$tquiz} q ON q.id = te.exe_exo_id\n INNER JOIN {$tquiz_rel_question} rq ON rq.exercice_id = q.id AND rq.c_id = q.c_id\n INNER JOIN {$tquiz_question} qq\n ON\n qq.id = rq.question_id AND\n qq.c_id = rq.c_id AND\n qq.position = rq.question_order AND\n ta.question_id = rq.question_id\n WHERE\n te.c_id = {$courseIdx} " . (empty($whereSessionParams) ? '' : "AND te.session_id IN ({$whereSessionParams})") . "\n AND q.c_id = {$courseIdx}\n {$where} {$order} {$limit}";
$sql_query = vsprintf($sql, $whereParams);
// Now browse through the results and get the data
$rs = Database::query($sql_query);
$userIds = array();
$questionIds = array();
$answerIds = array();
while ($row = Database::fetch_array($rs)) {
//only show if exercise is visible
if (api_get_item_visibility($courseData, 'quiz', $row['exercise_id'])) {
$userIds[$row['user_id']] = $row['user_id'];
$questionIds[$row['question_id']] = $row['question_id'];
$answerIds[$row['question_id']][$row['answer_id']] = $row['answer_id'];
$row['session'] = $sessions[$row['session_id']];
$data[] = $row;
}
}
// Now fill questions data. Query all questions and answers for this test to avoid
$sqlQuestions = "SELECT tq.c_id, tq.id as question_id, tq.question, tqa.id_auto,\n tqa.answer, tqa.correct, tq.position, tqa.id_auto as answer_id\n FROM {$tquiz_question} tq, {$tquiz_answer} tqa\n WHERE\n tqa.question_id = tq.id AND\n tqa.c_id = tq.c_id AND\n tq.c_id = {$courseIdx} AND\n tq.id IN (" . implode(',', $questionIds) . ")";
$resQuestions = Database::query($sqlQuestions);
$answer = array();
$question = array();
while ($rowQuestion = Database::fetch_assoc($resQuestions)) {
$questionId = $rowQuestion['question_id'];
$answerId = $rowQuestion['answer_id'];
$answer[$questionId][$answerId] = array('position' => $rowQuestion['position'], 'question' => $rowQuestion['question'], 'answer' => $rowQuestion['answer'], 'correct' => $rowQuestion['correct']);
$question[$questionId]['question'] = $rowQuestion['question'];
}
// Now fill users data
$sqlUsers = "SELECT user_id, username, lastname, firstname\n FROM {$tuser}\n WHERE user_id IN (" . implode(',', $userIds) . ")";
$resUsers = Database::query($sqlUsers);
while ($rowUser = Database::fetch_assoc($resUsers)) {
$users[$rowUser['user_id']] = $rowUser;
}
foreach ($data as $id => $row) {
$rowQuestId = $row['question_id'];
$rowAnsId = $row['answer_id'];
$data[$id]['session'] = $sessions[$row['session_id']]['name'];
$data[$id]['firstname'] = $users[$row['user_id']]['firstname'];
$data[$id]['lastname'] = $users[$row['user_id']]['lastname'];
$data[$id]['username'] = $users[$row['user_id']]['username'];
$data[$id]['answer'] = $answer[$rowQuestId][$rowAnsId]['answer'];
$data[$id]['correct'] = $answer[$rowQuestId][$rowAnsId]['correct'] == 0 ? get_lang('No') : get_lang('Yes');
$data[$id]['question'] = $question[$rowQuestId]['question'];
$data[$id]['question_id'] = $rowQuestId;
$data[$id]['description'] = $row['description'];
}
/*
The minimum expected array structure at the end is:
attempt_id,
session name,
exercise_id,
quiz_title,
username,
lastname,
firstname,
time,
question_id,
question,
answer,
*/
}
return $data;
}
$userManager = UserManager::getManager();
$repository = UserManager::getRepository();
/**
* E-mails list loop
*/
foreach ($list as $mail) {
$mail = trim($mail);
$sql = "SELECT user_id, official_code, firstname, lastname, email, username, language\n FROM {$users} WHERE email = '{$mail}'\n";
$res = Database::query($sql);
if ($res === false) {
echo 'Error in database with email ' . $mail . "\n";
}
if (Database::num_rows($res) == 0) {
echo '[Error] Email not found in database: ' . $row['email'] . "\n";
} else {
$row = Database::fetch_assoc($res);
$pass = api_substr($row['username'], 0, 4) . rand(0, 9) . rand(0, 9);
if ($user) {
/** @var User $user */
$user = $repository->find($row['user_id']);
$user->setPlainPassword($pass);
$userManager->updateUser($user, true);
} else {
echo "[Error] Error updating password. Skipping {$mail}\n";
continue;
}
$user = array('FirstName' => $row['firstname'], 'LastName' => $row['lastname'], 'UserName' => $row['username'], 'Password' => $pass, 'Email' => $mail);
$l = api_get_interface_language();
if (!empty($row['language'])) {
$l = $row['language'];
}
/**
* Gets a list of pending orders
* @result array List of orders
* @todo Enable pagination
*/
function pendingList($bcCodetext)
{
$tableBuyTemporal = $bcCodetext === 'THIS_IS_A_SESSION' ? Database::get_main_table(TABLE_BUY_SESSION_TEMPORARY) : Database::get_main_table(TABLE_BUY_COURSE_TEMPORAL);
$sql = "SELECT * FROM {$tableBuyTemporal};";
$res = Database::query($sql);
$aux = array();
while ($row = Database::fetch_assoc($res)) {
$aux[] = $row;
}
return $aux;
}
public function build_session_course()
{
$tbl_session = Database::get_main_table(TABLE_MAIN_SESSION);
$tbl_session_course = Database::get_main_table(TABLE_MAIN_SESSION_COURSE);
$list_course = CourseManager::get_course_list();
$list = array();
foreach ($list_course as $_course) {
$this->course = new Course();
$this->course->code = $_course['code'];
$this->course->type = 'partial';
$this->course->path = api_get_path(SYS_COURSE_PATH) . $_course['directory'] . '/';
$this->course->backup_path = api_get_path(SYS_COURSE_PATH) . $_course['directory'];
$this->course->encoding = api_get_system_encoding();
//current platform encoding
$code_course = $_course['code'];
$courseId = $_course['real_id'];
$sql_session = "SELECT s.id, name, c_id\n FROM {$tbl_session_course} sc\n INNER JOIN {$tbl_session} s\n ON sc.session_id = s.id\n WHERE sc.c_id = '{$courseId}' ";
$query_session = Database::query($sql_session);
while ($rows_session = Database::fetch_assoc($query_session)) {
$session = new CourseSession($rows_session['id'], $rows_session['name']);
$this->course->add_resource($session);
}
$list[] = $this->course;
}
return $list;
}
/**
* Init
*/
$rights_full = array("article_add", "article_delete", "article_edit", "article_rate", "article_comments_add", "article_comments_delete", "article_comments_rate", "task_management", "member_management", "role_management");
$rights_limited = array("Add", "Edit", "Delete");
$rights_blog = array("article_add", "article_delete", "article_edit", "article_rate", "article_comments_add", "article_comments_delete", "article_comments_rate", "task_management", "member_management", "role_management");
$course_tool_table = Database::get_course_table(TABLE_TOOL_LIST);
// Get all user
$blog_users = Blog::get_blog_users($_GET['blog_id']);
$course_id = api_get_course_int_id();
// Remove the blog creater because he has all the rights automatically
// and we want to keep it that way.
$tbl_course_rel_user = $table = Database::get_main_table(TABLE_MAIN_COURSE_USER);
$sql = "SELECT user_id FROM {$tbl_course_rel_user} WHERE status = '1' AND course_code = '" . $_SESSION['_cid'] . "'";
$result = Database::query($sql);
while ($user = Database::fetch_assoc($result)) {
unset($blog_users[$user['user_id']]);
}
//$user_id=$userIdViewed;
if ($mainUserInfo['status'] == 1) {
$course_admin = 1;
}
include_once 'permissions_functions.inc.php';
// ACTIONS
if (isset($_GET['do'])) {
if (isset($_GET['permission']) and isset($_GET['tool']) and ($_GET['do'] == 'grant' or $_GET['do'] == 'revoke')) {
$result_message = store_one_permission('user', $_GET['do'], $_GET['user_id'], $_GET['tool'], $_GET['permission']);
}
if (isset($_GET['role']) and ($_GET['do'] == 'grant' or $_GET['do'] == 'revoke')) {
$result_message = assign_role('user', $_GET['do'], $user_id, $_GET['role'], $_GET['scope']);
}
}
$tableRows[] = $tableRow;
}
$tplPath = 'skill/student_report.html.twig';
} else {
if ($isStudentBoss) {
$selectedStudent = isset($_REQUEST['student']) ? intval($_REQUEST['student']) : 0;
$tableRows = array();
$followedStudents = UserManager::getUsersFollowedByStudentBoss($userId);
foreach ($followedStudents as &$student) {
$student['completeName'] = api_get_person_name($student['firstname'], $student['lastname']);
}
if ($selectedStudent > 0) {
$sql = "SELECT s.name, sru.acquired_skill_at, c.title, c.directory\n FROM {$skillTable} s\n INNER JOIN {$skillRelUserTable} sru\n ON s.id = sru.skill_id\n INNER JOIN {$courseTable} c\n ON sru.course_id = c.id\n WHERE sru.user_id = {$selectedStudent}\n ";
$result = Database::query($sql);
while ($resultData = Database::fetch_assoc($result)) {
$tableRow = array('completeName' => $followedStudents[$selectedStudent]['completeName'], 'skillName' => $resultData['name'], 'achievedAt' => api_format_date($resultData['acquired_skill_at'], DATE_FORMAT_NUMBER), 'courseImage' => Display::return_icon('course.png', null, null, ICON_SIZE_MEDIUM, null, true), 'courseName' => $resultData['title']);
$imageSysPath = sprintf("%s%s/course-pic.png", api_get_path(SYS_COURSE_PATH), $resultData['directory']);
if (file_exists($imageSysPath)) {
$thumbSysPath = sprintf("%s%s/course-pic32.png", api_get_path(SYS_COURSE_PATH), $resultData['directory']);
$thumbWebPath = sprintf("%s%s/course-pic32.png", api_get_path(WEB_COURSE_PATH), $resultData['directory']);
if (!file_exists($thumbSysPath)) {
$courseImageThumb = new Image($imageSysPath);
$courseImageThumb->resize(32);
$courseImageThumb->send_image($thumbSysPath);
}
$tableRow['courseImage'] = $thumbWebPath;
}
$tableRows[] = $tableRow;
}
}
$courseInfo = api_get_course_info();
allowOnlySubscribedUser(api_get_user_id(), $work_id, $courseInfo['real_id']);
$userCondition = null;
// All users
if ($courseInfo['show_score'] == 0) {
// Do another filter
} else {
// Only teachers
$userCondition = " AND props.insert_user_id = " . api_get_user_id();
}
//for other users, we need to create a zipfile with only visible files and folders
$sql = "SELECT DISTINCT url, title, description, insert_user_id, insert_date, contains_file\n FROM {$tbl_student_publication} AS work INNER JOIN {$prop_table} AS props\n ON (props.c_id = {$course_id} AND\n work.c_id = {$course_id} AND\n work.id = props.ref)\n WHERE\n props.tool='work' AND\n work.accepted = 1 AND\n work.active = 1 AND\n work.parent_id = {$work_id} AND\n work.filetype = 'file' AND\n props.visibility = '1' AND\n work.post_group_id = {$groupId}\n {$userCondition}\n ";
}
$query = Database::query($sql);
//add tem to the zip file
while ($not_deleted_file = Database::fetch_assoc($query)) {
$user_info = api_get_user_info($not_deleted_file['insert_user_id']);
$insert_date = api_get_local_time($not_deleted_file['insert_date']);
$insert_date = str_replace(array(':', '-', ' '), '_', $insert_date);
$filename = $insert_date . '_' . $user_info['username'] . '_' . basename($not_deleted_file['title']);
if (file_exists($sys_course_path . $_course['path'] . '/' . $not_deleted_file['url']) && !empty($not_deleted_file['url'])) {
$files[basename($not_deleted_file['url'])] = $filename;
$zip_folder->add($sys_course_path . $_course['path'] . '/' . $not_deleted_file['url'], PCLZIP_OPT_REMOVE_PATH, $sys_course_path . $_course['path'] . '/work', PCLZIP_CB_PRE_ADD, 'my_pre_add_callback');
}
//Convert texts in html files
if ($not_deleted_file['contains_file'] == 0) {
$filename = trim($filename) . ".html";
$work_temp = api_get_path(SYS_ARCHIVE_PATH) . api_get_unique_id() . '_' . $filename;
file_put_contents($work_temp, $not_deleted_file['description']);
$files[basename($work_temp)] = $filename;
$zip_folder->add($work_temp, PCLZIP_OPT_REMOVE_PATH, api_get_path(SYS_ARCHIVE_PATH), PCLZIP_CB_PRE_ADD, 'my_pre_add_callback');
/**
* Returns true if user exists in the platform when asking the password
*
* @param string $username (email or username)
* @return array|boolean
*/
public static function get_user_accounts_by_username($username)
{
if (strpos($username, '@')) {
$username = api_strtolower($username);
$email = true;
} else {
$username = api_strtolower($username);
$email = false;
}
if ($email) {
$condition = "LOWER(email) = '" . Database::escape_string($username) . "' ";
} else {
$condition = "LOWER(username) = '" . Database::escape_string($username) . "'";
}
$tbl_user = Database::get_main_table(TABLE_MAIN_USER);
$query = "SELECT user_id AS uid, lastname AS lastName, firstname AS firstName, username AS loginName, password, email,\n status AS status, official_code, phone, picture_uri, creator_id\n\t\t\t\t FROM {$tbl_user}\n\t\t\t\t WHERE ( {$condition} AND active = 1) ";
$result = Database::query($query);
$num_rows = Database::num_rows($result);
if ($result && $num_rows > 0) {
return Database::fetch_assoc($result);
}
return false;
}