/**
* Validates imported data.
*/
function validate_data($user_classes)
{
global $purification_option_for_usernames;
$errors = array();
$classcodes = array();
if (!isset($_POST['subscribe']) && !isset($_POST['subscribe'])) {
$user_class['error'] = get_lang('SelectAnAction');
$errors[] = $user_class;
return $errors;
}
foreach ($user_classes as $index => $user_class) {
$user_class['line'] = $index + 1;
// 1. Check whether mandatory fields are set.
$mandatory_fields = array('UserName', 'ClassName');
foreach ($mandatory_fields as $key => $field) {
if (!isset($user_class[$field]) || strlen($user_class[$field]) == 0) {
$user_class['error'] = get_lang($field . 'Mandatory');
$errors[] = $user_class;
}
}
// 2. Check whether classcode exists.
if (isset($user_class['ClassName']) && strlen($user_class['ClassName']) != 0) {
// 2.1 Check whether code has been allready used in this CVS-file.
if (!isset($classcodes[$user_class['ClassName']])) {
// 2.1.1 Check whether code exists in DB.
$class_table = Database::get_main_table(TABLE_MAIN_CLASS);
$sql = "SELECT * FROM {$class_table} WHERE name = '" . Database::escape_string($user_class['ClassName']) . "'";
$res = Database::query($sql);
if (Database::num_rows($res) == 0) {
$user_class['error'] = get_lang('CodeDoesNotExists') . ': ' . $user_class['ClassName'];
$errors[] = $user_class;
} else {
$classcodes[$user_class['CourseCode']] = 1;
}
}
}
// 3. Check username, first, check whether it is empty.
if (!UserManager::is_username_empty($user_class['UserName'])) {
// 3.1. Check whether username is too long.
if (UserManager::is_username_too_long($user_class['UserName'])) {
$user_class['error'] = get_lang('UserNameTooLong') . ': ' . $user_class['UserName'];
$errors[] = $user_class;
}
$username = UserManager::purify_username($user_class['UserName'], $purification_option_for_usernames);
// 3.2. Check whether username exists.
if (UserManager::is_username_available($username)) {
$user_class['error'] = get_lang('UnknownUser') . ': ' . $username;
$errors[] = $user_class;
}
}
}
return $errors;
}
function checkLogin($login, $pass)
{
$db = new Database();
//Traigo el usuario
$q = "select salt from jugador where login='******' limit 1";
$r = $db->query($q);
//Controlo que exista el usuario con el login $login
if ($db->num_rows($r) > 0) {
//Traigo el registro
$data = $db->fetch_array($r);
$salt_db = $data['salt'];
//Genero el mismo hash que se creo al registrar jugador
$hashedpass = hash('sha512', $pass . $salt_db);
$q2 = "select * from jugador where login='******' and pass=PASSWORD('{$hashedpass}')";
$r2 = $db->query($q2);
if ($db->num_rows($r2) > 0) {
return 1;
} else {
return 0;
}
} else {
alertMessage('El usuario no existe');
exit;
}
$db->close();
}
function reports_template_exercicesMultiCourses_getSQL()
{
// foreach quiz
$result = array();
$columns = Database::query('select r.id as kid, c.title as course, ' . 'r.child_name as test from ' . Database::get_main_table(TABLE_MAIN_REPORTS_KEYS) . ' r, ' . Database::get_main_table(TABLE_MAIN_COURSE) . ' c ' . 'where r.course_id=c.id and r.tool_id=' . reports_getToolId(TOOL_QUIZ) . ' order by r.course_id, r.child_name');
if (Database::num_rows($columns) == 0) {
die('<b>' . get_lang('no data found') . '</b>');
}
$query = 'select u.lastname Name, u.firstname Firstname';
$columns = Database::store_result($columns);
if ($_REQUEST['tattempt'] == 'min' || $_REQUEST['tattempt'] == 'max') {
$function = $_REQUEST['tattempt'];
} else {
$function = 'avg';
}
foreach ($columns as $key => $column) {
$query .= ', ' . $function . '(k' . $key . '.score) as `' . $column['course'] . ' - ' . $column['test'] . '` ';
}
$query .= ' from ' . Database::get_main_table(TABLE_MAIN_USER) . ' u ';
foreach ($columns as $key => $column) {
// fixme sessions
$query .= 'left outer join ' . Database::get_main_table(TABLE_MAIN_REPORTS_VALUES) . ' k' . $key . ' on k' . $key . '.key_id = ' . $column['kid'] . ' and k' . $key . '.user_id = u.user_id ';
}
$query .= ' group by ';
foreach ($columns as $key => $column) {
// grouping attempt
$query .= 'k' . $key . '.attempt, ';
}
$query = substr($query, 0, -2);
// removing last ', ';
return $query;
}
/**
* @Route("/edit/{tool}")
* @Method({"GET"})
*
* @param string $tool
* @return Response
*/
public function editAction($tool)
{
$message = null;
// @todo use proper functions not api functions.
$courseId = api_get_course_int_id();
$sessionId = api_get_session_id();
$tool = \Database::escape_string($tool);
$TBL_INTRODUCTION = \Database::get_course_table(TABLE_TOOL_INTRO);
$url = $this->generateUrl('introduction.controller:editAction', array('tool' => $tool, 'course' => api_get_course_id()));
$form = $this->getForm($url, $tool);
if ($form->validate()) {
$values = $form->exportValues();
$content = $values['content'];
$sql = "REPLACE {$TBL_INTRODUCTION}\n SET c_id = {$courseId},\n id = '{$tool}',\n intro_text='" . \Database::escape_string($content) . "',\n session_id='" . intval($sessionId) . "'";
\Database::query($sql);
$message = \Display::return_message(get_lang('IntroductionTextUpdated'), 'confirmation', false);
} else {
$sql = "SELECT intro_text FROM {$TBL_INTRODUCTION}\n WHERE c_id = {$courseId} AND id='" . $tool . "' AND session_id = '" . intval($sessionId) . "'";
$result = \Database::query($sql);
$content = null;
if (\Database::num_rows($result) > 0) {
$row = \Database::fetch_array($result);
$content = $row['intro_text'];
}
$form->setDefaults(array('content' => $content));
}
$this->getTemplate()->assign('content', $form->return_form());
$this->getTemplate()->assign('message', $message);
$response = $this->getTemplate()->renderLayout('layout_1_col.tpl');
return new Response($response, 200, array());
}
public static function hasRight($handler)
{
if (array_key_exists($handler, self::$rights_cache)) {
return self::$rights_cache[$handler];
}
if (!array_key_exists($handler, self::$rights)) {
return true;
}
// handler does not exists
if (self::$rights[$handler]['type'] == 'sql') {
$result = Database::query(self::$rights[$handler]['sql']);
if (Database::num_rows($result) > 0) {
$result = true;
} else {
$result = false;
}
} else {
if (self::$rights[$handler]['type'] == 'const') {
$result = self::$rights[$handler]['const'];
} else {
if (self::$rights[$handler]['type'] == 'func') {
$result = self::$rights[$handler]['func']();
} else {
// handler type not implemented
return true;
}
}
}
self::$rights_cache[$handler] = $result;
return $result;
}
/**
* Update the file or directory path in the document db document table
*
* @author - Hugues Peeters <*****@*****.**>
* @param - action (string) - action type require : 'delete' or 'update'
* @param - old_path (string) - old path info stored to change
* @param - new_path (string) - new path info to substitute
* @desc Update the file or directory path in the document db document table
*
*/
function update_db_info($action, $old_path, $new_path = '')
{
$dbTable = Database::get_course_table(TABLE_DOCUMENT);
$course_id = api_get_course_int_id();
switch ($action) {
case 'delete':
$old_path = Database::escape_string($old_path);
$to_delete = "WHERE c_id = {$course_id} AND (path LIKE BINARY '" . $old_path . "' OR path LIKE BINARY '" . $old_path . "/%')";
$query = "DELETE FROM {$dbTable} " . $to_delete;
$result = Database::query("SELECT id FROM {$dbTable} " . $to_delete);
if (Database::num_rows($result)) {
require_once api_get_path(INCLUDE_PATH) . '../metadata/md_funcs.php';
$mdStore = new mdstore(TRUE);
// create if needed
$md_type = substr($dbTable, -13) == 'scormdocument' ? 'Scorm' : 'Document';
while ($row = Database::fetch_array($result)) {
$eid = $md_type . '.' . $row['id'];
$mdStore->mds_delete($eid);
$mdStore->mds_delete_offspring($eid);
}
}
Database::query($query);
break;
case 'update':
if ($new_path[0] == '.') {
$new_path = substr($new_path, 1);
}
$new_path = str_replace('//', '/', $new_path);
// Attempt to update - tested & working for root dir
$new_path = Database::escape_string($new_path);
$query = "UPDATE {$dbTable} SET\n path = CONCAT('" . $new_path . "', SUBSTRING(path, LENGTH('" . $old_path . "')+1) )\n WHERE c_id = {$course_id} AND (path LIKE BINARY '" . $old_path . "' OR path LIKE BINARY '" . $old_path . "/%')";
Database::query($query);
break;
}
}
/**
* store the default index-file in a given destination folder
*
* @param string $loginname customers loginname
* @param string $destination path where to create the file
* @param object $logger FroxlorLogger object
* @param boolean $force force creation whatever the settings say (needed for task #2, create new user)
*
* @return null
*/
function storeDefaultIndex($loginname = null, $destination = null, $logger = null, $force = false)
{
if ($force || (int) Settings::Get('system.store_index_file_subs') == 1) {
$result_stmt = Database::prepare("\n\t\t\tSELECT `t`.`value`, `c`.`email` AS `customer_email`, `a`.`email` AS `admin_email`, `c`.`loginname` AS `customer_login`, `a`.`loginname` AS `admin_login`\n\t\t\tFROM `" . TABLE_PANEL_CUSTOMERS . "` AS `c` INNER JOIN `" . TABLE_PANEL_ADMINS . "` AS `a`\n\t\t\tON `c`.`adminid` = `a`.`adminid`\n\t\t\tINNER JOIN `" . TABLE_PANEL_TEMPLATES . "` AS `t`\n\t\t\tON `a`.`adminid` = `t`.`adminid`\n\t\t\tWHERE `varname` = 'index_html' AND `c`.`loginname` = :loginname");
Database::pexecute($result_stmt, array('loginname' => $loginname));
if (Database::num_rows() > 0) {
$template = $result_stmt->fetch(PDO::FETCH_ASSOC);
$replace_arr = array('SERVERNAME' => Settings::Get('system.hostname'), 'CUSTOMER' => $template['customer_login'], 'ADMIN' => $template['admin_login'], 'CUSTOMER_EMAIL' => $template['customer_email'], 'ADMIN_EMAIL' => $template['admin_email']);
$htmlcontent = replace_variables($template['value'], $replace_arr);
$indexhtmlpath = makeCorrectFile($destination . '/index.' . Settings::Get('system.index_file_extension'));
$index_html_handler = fopen($indexhtmlpath, 'w');
fwrite($index_html_handler, $htmlcontent);
fclose($index_html_handler);
if ($logger !== null) {
$logger->logAction(CRON_ACTION, LOG_NOTICE, 'Creating \'index.' . Settings::Get('system.index_file_extension') . '\' for Customer \'' . $template['customer_login'] . '\' based on template in directory ' . escapeshellarg($indexhtmlpath));
}
} else {
$destination = makeCorrectDir($destination);
if ($logger !== null) {
$logger->logAction(CRON_ACTION, LOG_NOTICE, 'Running: cp -a ' . FROXLOR_INSTALL_DIR . '/templates/misc/standardcustomer/* ' . escapeshellarg($destination));
}
safe_exec('cp -a ' . FROXLOR_INSTALL_DIR . '/templates/misc/standardcustomer/* ' . escapeshellarg($destination));
}
}
return;
}
public function __construct($in_c_id = 0, $in_id = 0)
{
if ($in_c_id > 0 && $in_id > 0) {
$item_view_table = Database::get_course_table(TABLE_LP_ITEM);
$sql = "SELECT * FROM {$item_view_table}\n WHERE\n c_id=" . intval($in_c_id) . " AND\n id=" . intval($in_id);
$res = Database::query($sql);
$data = Database::fetch_array($res);
if (Database::num_rows($res) > 0) {
$this->c_id = $data['c_id'];
$this->id = $data['id'];
$this->lp_id = $data['lp_id'];
$this->item_type = $data['item_type'];
$this->ref = $data['ref'];
$this->title = $data['title'];
$this->description = $data['description'];
$this->path = $data['path'];
$this->min_score = $data['min_score'];
$this->max_score = $data['max_score'];
$this->mastery_score = $data['mastery_score'];
$this->parent_item_id = $data['parent_item_id'];
$this->previous_item_id = $data['previous_item_id'];
$this->next_item_id = $data['next_item_id'];
$this->display_order = $data['display_order'];
$this->prerequisite = $data['prerequisite'];
$this->parameters = $data['parameters'];
$this->launch_data = $data['launch_data'];
$this->max_time_allowed = $data['max_time_allowed'];
$this->terms = $data['terms'];
$this->search_did = $data['search_did'];
$this->audio = $data['audio'];
}
}
}
/**
* @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_by_id($id)
{
$table = Database::get_main_table(TABLE_MAIN_USER_API_KEY);
$sql = "SELECT * FROM {$table} WHERE id={$id}";
$res = Database::query($sql);
if (Database::num_rows($res) < 1) {
return false;
}
$result = Database::fetch_array($res, 'ASSOC');
return $result;
}
public function xId($id)
{
$db = new Database();
$q = "select * from invitado where id = '{$id}'";
$r = $db->query($q);
//Si existe ese login
if ($db->num_rows($r) > 0) {
$data = $db->fetch_array($r);
$this->dbToObject($data);
}
$db->close();
}
/**
* Function to check if a username is available
* @see HTML_QuickForm_Rule
* @param string $username Wanted username
* @param string $current_username
* @return boolean True if username is available
*/
function validate($username, $current_username = null)
{
$user_table = Database::get_main_table(TABLE_MAIN_USER);
$username = Database::escape_string($username);
$current_username = Database::escape_string($current_username);
$sql = "SELECT * FROM {$user_table} WHERE username = '******'";
if (!is_null($current_username)) {
$sql .= " AND username != '{$current_username}'";
}
$res = Database::query($sql);
$number = Database::num_rows($res);
return $number == 0;
}
/**
* check whether an email account is to be deleted
* reference: #1519
*
* @return bool true if the domain is to be deleted, false otherwise
*
*/
function checkMailAccDeletionState($email_addr = null)
{
// example data of task 7: a:2:{s:9:"loginname";s:4:"webX";s:5:"email";s:20:"*****@*****.**";}
// check for task
$result_tasks_stmt = Database::prepare("\n\t\tSELECT * FROM `" . TABLE_PANEL_TASKS . "` WHERE `type` = '7' AND `data` LIKE :emailaddr\n\t");
Database::pexecute($result_tasks_stmt, array('emailaddr' => "%" . $email_addr . "%"));
$num_results = Database::num_rows();
// is there a task for deleting this email account?
if ($num_results > 0) {
return true;
}
return false;
}
/**
* This file is part of the Froxlor project.
* Copyright (c) 2003-2009 the SysCP Team (see authors).
* Copyright (c) 2010 the Froxlor Team (see authors).
*
* For the full copyright and license information, please view the COPYING
* file that was distributed with this source code. You can also view the
* COPYING file online at http://files.froxlor.org/misc/COPYING.txt
*
* @copyright (c) the authors
* @author Froxlor team <*****@*****.**> (2010-)
* @license GPLv2 http://files.froxlor.org/misc/COPYING.txt
* @package Functions
*
*/
function storeSettingResetCatchall($fieldname, $fielddata, $newfieldvalue)
{
$returnvalue = storeSettingField($fieldname, $fielddata, $newfieldvalue);
if ($returnvalue !== false && is_array($fielddata) && isset($fielddata['settinggroup']) && $fielddata['settinggroup'] == 'catchall' && isset($fielddata['varname']) && $fielddata['varname'] == 'catchall_enabled' && $newfieldvalue == '0') {
$result_stmt = Database::query("\n\t\t\tSELECT `id`, `email`, `email_full`, `iscatchall` FROM `" . TABLE_MAIL_VIRTUAL . "`\n\t\t\tWHERE `iscatchall` = '1'\n\t\t");
if (Database::num_rows() > 0) {
$upd_stmt = Database::prepare("\n\t\t\t\tUPDATE `" . TABLE_MAIL_VIRTUAL . "` SET `email` = :email, `iscatchall` = '0' WHERE `id` = :id\n\t\t\t");
while ($result_row = $result_stmt->fetch(PDO::FETCH_ASSOC)) {
Database::pexecute($upd_stmt, array('email' => $result_row['email_full'], 'id' => $result_row['id']));
}
}
}
return $returnvalue;
}
/**
* @param int $notebook_id
* @return array|mixed
*/
static function get_note_information($notebook_id)
{
if (empty($notebook_id)) {
return array();
}
// Database table definition
$t_notebook = Database::get_course_table(TABLE_NOTEBOOK);
$course_id = api_get_course_int_id();
$sql = "SELECT\n notebook_id \t\tAS notebook_id,\n title\t\t\t\tAS note_title,\n description \t\tAS note_comment,\n session_id\t\t\tAS session_id\n FROM {$t_notebook}\n WHERE c_id = {$course_id} AND notebook_id = '" . intval($notebook_id) . "' ";
$result = Database::query($sql);
if (Database::num_rows($result) != 1) {
return array();
}
return Database::fetch_array($result);
}
/**
* Validates the imported data.
*/
function validate_data($users_courses)
{
$errors = array();
$coursecodes = array();
foreach ($users_courses as $index => $user_course) {
$user_course['line'] = $index + 1;
// 1. Check whether mandatory fields are set.
$mandatory_fields = array('Email', 'CourseCode', 'Status');
foreach ($mandatory_fields as $key => $field) {
if (!isset($user_course[$field]) || strlen($user_course[$field]) == 0) {
$user_course['error'] = get_lang($field . 'Mandatory');
$errors[] = $user_course;
}
}
// 2. Check whether coursecode exists.
if (isset($user_course['CourseCode']) && strlen($user_course['CourseCode']) != 0) {
// 2.1 Check whethher code has been allready used by this CVS-file.
if (!isset($coursecodes[$user_course['CourseCode']])) {
// 2.1.1 Check whether course with this code exists in the system.
$course_table = Database::get_main_table(TABLE_MAIN_COURSE);
$sql = "SELECT * FROM {$course_table}\n WHERE code = '" . Database::escape_string($user_course['CourseCode']) . "'";
$res = Database::query($sql);
if (Database::num_rows($res) == 0) {
$user_course['error'] = get_lang('CodeDoesNotExists');
$errors[] = $user_course;
} else {
$coursecodes[$user_course['CourseCode']] = 1;
}
}
}
// 3. Check whether Email exists.
if (isset($user_course['Email']) && strlen($user_course['Email']) != 0) {
$user = api_get_user_info_from_email($user_course['Email']);
if (empty($user)) {
$user_course['error'] = get_lang('UnknownUser');
$errors[] = $user_course;
}
}
// 4. Check whether status is valid.
if (isset($user_course['Status']) && strlen($user_course['Status']) != 0) {
if ($user_course['Status'] != COURSEMANAGER && $user_course['Status'] != STUDENT) {
$user_course['error'] = get_lang('UnknownStatus');
$errors[] = $user_course;
}
}
}
return $errors;
}
function cloud_createCloudRegistration($cid, $lp_id, $lp_view_id)
{
$userInfo = api_get_user_info();
$userId = $userInfo['user_id'];
$ScormService = cloud_getScormEngineService();
$regService = $ScormService->getRegistrationService();
$tbl_scorm_cloud = Database::get_main_table('scorm_cloud');
$sql_cloud_get_course = "Select cloud_course_id from {$tbl_scorm_cloud} " . "WHERE course_code = '{$cid}' AND lp_id = {$lp_id} ";
//echo $sql_cloud_get_course.'<br/>';
$res = api_sql_query($sql_cloud_get_course, __FILE__, __LINE__);
if (Database::num_rows($res) > 0) {
$row = Database::fetch_array($res);
$cloud_courseId = $row['cloud_course_id'];
}
$regService->CreateRegistration(cloud_getRegId($cid, $lp_view_id), $cloud_courseId, $userId, $userInfo['firstName'], $userInfo['lastName']);
}
/**
* 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;
}
/**
* Defines the four needed keys to create a course based on several parameters.
* @param string The code you want for this course
* @param string Prefix added for ALL keys
* @param string Prefix added for databases only
* @param string Prefix added for paths only
* @param bool Add unique prefix
* @param bool Use code-independent keys
* @return array An array with the needed keys ['currentCourseCode'], ['currentCourseId'], ['currentCourseDbName'], ['currentCourseRepository']
* @todo Eliminate the global variables.
* @assert (null) === false
*/
public static function define_course_keys($wanted_code, $prefix_for_all = '', $prefix_for_base_name = '', $prefix_for_path = '', $add_unique_prefix = false, $use_code_indepedent_keys = true)
{
global $prefixAntiNumber, $_configuration;
$course_table = Database::get_main_table(TABLE_MAIN_COURSE);
$wanted_code = CourseManager::generate_course_code($wanted_code);
$keys_course_code = $wanted_code;
if (!$use_code_indepedent_keys) {
$wanted_code = '';
}
if ($add_unique_prefix) {
$unique_prefix = substr(md5(uniqid(rand())), 0, 10);
} else {
$unique_prefix = '';
}
$keys = array();
$final_suffix = array('CourseId' => '', 'CourseDb' => '', 'CourseDir' => '');
$limit_numb_try = 100;
$keys_are_unique = false;
$try_new_fsc_id = $try_new_fsc_db = $try_new_fsc_dir = 0;
while (!$keys_are_unique) {
$keys_course_id = $prefix_for_all . $unique_prefix . $wanted_code . $final_suffix['CourseId'];
//$keys_course_db_name = $prefix_for_base_name . $unique_prefix . strtoupper($keys_course_id) . $final_suffix['CourseDb'];
$keys_course_repository = $prefix_for_path . $unique_prefix . $wanted_code . $final_suffix['CourseDir'];
$keys_are_unique = true;
// Check whether they are unique.
$query = "SELECT 1 FROM " . $course_table . " WHERE code='" . $keys_course_id . "' LIMIT 0,1";
$result = Database::query($query);
if (Database::num_rows($result)) {
$keys_are_unique = false;
$try_new_fsc_id++;
$final_suffix['CourseId'] = substr(md5(uniqid(rand())), 0, 4);
}
if (file_exists(api_get_path(SYS_COURSE_PATH) . $keys_course_repository)) {
$keys_are_unique = false;
$try_new_fsc_dir++;
$final_suffix['CourseDir'] = substr(md5(uniqid(rand())), 0, 4);
}
if ($try_new_fsc_id + $try_new_fsc_db + $try_new_fsc_dir > $limit_numb_try) {
return $keys;
}
}
$keys['currentCourseCode'] = $keys_course_code;
$keys['currentCourseId'] = $keys_course_id;
$keys['currentCourseRepository'] = $keys_course_repository;
return $keys;
}
public static function user()
{
$id = isset($_COOKIE['id']) ? $_COOKIE['id'] : '';
$hash = isset($_COOKIE['hash']) ? $_COOKIE['hash'] : '';
$query = Database::query("SELECT * FROM " . self::$tUsers . " WHERE id = ? AND session = ?", [$id, $hash]);
if (Database::num_rows($query)) {
$fetch = Database::fetch_object($query);
$user = new StdClass();
$user->id = $fetch->id;
$user->email = $fetch->email;
$user->username = $fetch->username;
if (!empty($fetch->profile)) {
$profile = json_decode($fetch->profile);
foreach ($profile as $field => $value) {
$user->{$field} = htmlspecialchars($value);
}
}
return $user;
}
}
/**
* * Get the number of classes to display on the current page.
*/
function get_number_of_classes()
{
$em = Database::getManager();
$class_table = Database::get_main_table(TABLE_MAIN_CLASS);
$courseId = api_get_course_int_id();
$res = $em->getRepository('ChamiloCoreBundle:CourseRelClass')->findBy(['courseId' => $courseId]);
$subscribed_classes = array();
foreach ($res as $obj) {
$subscribed_classes[] = $obj->getClassId();
}
$sql = "SELECT c.id\tFROM {$class_table} c WHERE 1 = 1";
if (isset($_GET['keyword'])) {
$keyword = Database::escape_string(trim($_GET['keyword']));
$sql .= " AND (c.name LIKE '%" . $keyword . "%')";
}
if (count($subscribed_classes) > 0) {
$sql .= " AND c.id NOT IN ('" . implode("','", $subscribed_classes) . "')";
}
$res = Database::query($sql);
$result = Database::num_rows($res);
return $result;
}
function reports_template_courseTime_getSQL()
{
// fetch columns
$result = array();
$query = 'select r.id as kid, c.title as course ' . 'from ' . Database::get_main_table(TABLE_MAIN_REPORTS_KEYS) . ' r, ' . Database::get_main_table(TABLE_MAIN_COURSE) . ' c ' . 'where r.course_id=c.id and r.tool_id is null and r.child_id is null' . ' order by c.title';
$columns = Database::query($query);
if (Database::num_rows($columns) == 0) {
die('<b>' . get_lang('no data found: ' . $query) . '</b>');
}
$columns = Database::store_result($columns);
// fetch data
$query = 'select u.lastname Name, u.firstname Firstname';
foreach ($columns as $key => $column) {
$query .= ', sec_to_time(k' . $key . '.report_time) as `' . $column['course'] . '` ';
}
$query .= ' from ' . Database::get_main_table(TABLE_MAIN_USER) . ' u ';
foreach ($columns as $key => $column) {
// fixme sessions
$query .= 'left outer join ' . Database::get_main_table(TABLE_MAIN_REPORTS_VALUES) . ' k' . $key . ' on k' . $key . '.key_id = ' . $column['kid'] . ' and k' . $key . '.user_id = u.user_id ';
}
return $query;
}
/**
* * Get the number of classes to display on the current page.
*/
function get_number_of_classes()
{
$class_table = Database::get_main_table(TABLE_MAIN_CLASS);
$course_class_table = Database::get_main_table(TABLE_MAIN_COURSE_CLASS);
$sql = "SELECT * FROM {$course_class_table} WHERE course_code = '" . $_SESSION['_course']['id'] . "'";
$res = Database::query($sql);
$subscribed_classes = array();
while ($obj = Database::fetch_object($res)) {
$subscribed_classes[] = $obj->class_id;
}
$sql = "SELECT c.id\tFROM {$class_table} c WHERE 1 = 1";
if (isset($_GET['keyword'])) {
$keyword = Database::escape_string(trim($_GET['keyword']));
$sql .= " AND (c.name LIKE '%" . $keyword . "%')";
}
if (count($subscribed_classes) > 0) {
$sql .= " AND c.id NOT IN ('" . implode("','", $subscribed_classes) . "')";
}
$res = Database::query($sql);
$result = Database::num_rows($res);
return $result;
}
/**
* This function returns false if there is at least one item in the path
* @param Learnpath ID
* @return boolean True if nothing was found, false otherwise
*/
function is_empty($id)
{
$tbl_learnpath_item = Database::get_course_table(TABLE_LEARNPATH_ITEM);
$tbl_learnpath_chapter = Database::get_course_table(TABLE_LEARNPATH_CHAPTER);
$course_id = api_get_course_int_id();
$sql = "SELECT * FROM {$tbl_learnpath_chapter} WHERE c_id = {$course_id} AND lp_id={$id} ORDER BY display_order ASC";
$result = Database::query($sql);
$num_modules = Database::num_rows($result);
$empty = true;
if ($num_modules != 0) {
while ($row = Database::fetch_array($result)) {
$num_items = 0;
$parent_item_id = $row['id'];
$sql2 = "SELECT * FROM {$tbl_learnpath_item} WHERE c_id = {$course_id} AND (parent_item_id={$parent_item_id}) ORDER BY display_order ASC";
$result2 = Database::query($sql2);
$num_items = Database::num_rows($result2);
if ($num_items > 0) {
$empty = false;
}
}
}
return $empty;
}
/**
* @param int $courseId
* @param int $sessionId
* @param string $order
* @return array
*/
public function getUserAgreementList($courseId, $sessionId, $order = null)
{
$courseId = intval($courseId);
$sessionId = intval($sessionId);
$table = Database::get_main_table('session_rel_course_rel_user_legal');
$userTable = Database::get_main_table(TABLE_MAIN_USER);
$sql = "SELECT *\n FROM {$table} s INNER JOIN {$userTable} u\n ON u.user_id = s.user_id\n WHERE c_id = {$courseId} AND session_id = {$sessionId} ";
if (!empty($order)) {
$sql .= $order;
}
$result = Database::query($sql);
$data = array();
if (Database::num_rows($result) > 0) {
$data = Database::store_result($result, 'ASSOC');
}
return $data;
}
/**
* Shows statistics about the time of last visit to each course.
*/
static function print_course_last_visit()
{
$access_url_rel_course_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
$current_url_id = api_get_current_access_url_id();
$columns[0] = 'c_id';
$columns[1] = 'access_date';
$sql_order[SORT_ASC] = 'ASC';
$sql_order[SORT_DESC] = 'DESC';
$per_page = isset($_GET['per_page']) ? intval($_GET['per_page']) : 10;
$page_nr = isset($_GET['page_nr']) ? intval($_GET['page_nr']) : 1;
$column = isset($_GET['column']) ? intval($_GET['column']) : 0;
$date_diff = isset($_GET['date_diff']) ? intval($_GET['date_diff']) : 60;
if (!in_array($_GET['direction'], array(SORT_ASC, SORT_DESC))) {
$direction = SORT_ASC;
} else {
$direction = isset($_GET['direction']) ? $_GET['direction'] : SORT_ASC;
}
$form = new FormValidator('courselastvisit', 'get');
$form->addElement('hidden', 'report', 'courselastvisit');
$form->add_textfield('date_diff', get_lang('Days'), true);
$form->addRule('date_diff', 'InvalidNumber', 'numeric');
$form->addElement('style_submit_button', 'submit', get_lang('Search'), 'class="search"');
if (!isset($_GET['date_diff'])) {
$defaults['date_diff'] = 60;
} else {
$defaults['date_diff'] = Security::remove_XSS($_GET['date_diff']);
}
$form->setDefaults($defaults);
$form->display();
$values = $form->exportValues();
$date_diff = $values['date_diff'];
$table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LASTACCESS);
$tableCourse = Database::get_main_table(TABLE_MAIN_COURSE);
if (api_is_multiple_url_enabled()) {
$sql = "SELECT access_date, c.code FROM {$table} s , {$access_url_rel_course_table} u, {$tableCourse} c\n WHERE c.id = u.c_id AND c.id = s.c_id AND access_url_id='" . $current_url_id . "' " . "GROUP BY access_cours_code " . "HAVING s.c_id <> '' " . "AND DATEDIFF( '" . date('Y-m-d h:i:s') . "' , access_date ) <= " . $date_diff;
} else {
$sql = "SELECT access_date, c.code FROM {$table} , {$tableCourse} c\n WHERE c_id = c.id\n GROUP BY c_id\n HAVING c_id <> ''AND\n DATEDIFF( '" . date('Y-m-d h:i:s') . "' , access_date ) <= " . $date_diff;
}
$res = Database::query($sql);
$number_of_courses = Database::num_rows($res);
$sql .= ' ORDER BY ' . $columns[$column] . ' ' . $sql_order[$direction];
$from = ($page_nr - 1) * $per_page;
$sql .= ' LIMIT ' . $from . ',' . $per_page;
echo '<p>' . get_lang('LastAccess') . ' >= ' . $date_diff . ' ' . get_lang('Days') . '</p>';
$res = Database::query($sql);
if (Database::num_rows($res) > 0) {
$courses = array();
while ($obj = Database::fetch_object($res)) {
$course = array();
$course[] = '<a href="' . api_get_path(WEB_PATH) . 'courses/' . $obj->code . '">' . $obj->code . ' <a>';
//Allow sort by date hiding the numerical date
$course[] = '<span style="display:none;">' . $obj->access_date . '</span>' . api_convert_and_format_date($obj->access_date);
$courses[] = $course;
}
$parameters['date_diff'] = $date_diff;
$parameters['report'] = 'courselastvisit';
$table_header[] = array(get_lang("CourseCode"), true);
$table_header[] = array(get_lang("LastAccess"), true);
Display::display_sortable_table($table_header, $courses, array('column' => $column, 'direction' => $direction), array(), $parameters);
} else {
echo get_lang('NoSearchResults');
}
}
/**
*
* @param $exerciseId
* @param $mediaId
* @return array|bool
*/
public function getQuestionsPerMediaWithCategories($exerciseId, $mediaId)
{
$exerciseId = intval($exerciseId);
$mediaId = intval($mediaId);
$questionTable = Database::get_course_table(TABLE_QUIZ_QUESTION);
$questionRelExerciseTable = Database::get_course_table(TABLE_QUIZ_TEST_QUESTION);
$sql = "SELECT q.* FROM {$questionTable} q INNER JOIN {$questionRelExerciseTable} r ON (q.iid = r.question_id)\n WHERE (r.exercice_id = {$exerciseId} AND q.parent_id = {$mediaId}) ";
$result = Database::query($sql);
if (Database::num_rows($result)) {
return Database::store_result($result, 'ASSOC');
}
return false;
}
/**
* Checks whether additional information about the given course request has been asked.
* @param int/string $id The database primary id of the given request.
* @return bool Returns TRUE if additional information has been asked or FALSE otherwise.
*/
public static function additional_info_asked($id)
{
$id = (int) $id;
$sql = "SELECT id FROM " . Database::get_main_table(TABLE_MAIN_COURSE_REQUEST) . "\n WHERE (id = " . $id . " AND info > 0)";
$result = Database::num_rows(Database::query($sql));
return !empty($result);
}
$result_stmt = Database::prepare("\n\t\t\tSELECT `d`.*, `c`.`customerid` FROM `" . TABLE_PANEL_DOMAINS . "` `d` LEFT JOIN `" . TABLE_PANEL_CUSTOMERS . "` `c` USING(`customerid`)\n\t\t\tWHERE `d`.`parentdomainid` = '0' AND `d`.`id` = :id" . ($userinfo['customers_see_all'] ? '' : " AND `d`.`adminid` = :adminid"));
$params = array('id' => $id);
if ($userinfo['customers_see_all'] == '0') {
$params['adminid'] = $userinfo['adminid'];
}
$result = Database::pexecute_first($result_stmt, $params);
if ($result['domain'] != '') {
$subdomains_stmt = Database::prepare("\n\t\t\t\tSELECT COUNT(`id`) AS count FROM `" . TABLE_PANEL_DOMAINS . "` WHERE\n\t\t\t\t`parentdomainid` = :resultid\n\t\t\t");
$subdomains = Database::pexecute_first($subdomains_stmt, array('resultid' => $result['id']));
$subdomains = $subdomains['count'];
$alias_check_stmt = Database::prepare("\n\t\t\t\tSELECT COUNT(`id`) AS count FROM `" . TABLE_PANEL_DOMAINS . "` WHERE\n\t\t\t\t`aliasdomain` = :resultid\n\t\t\t");
$alias_check = Database::pexecute_first($alias_check_stmt, array('resultid' => $result['id']));
$alias_check = $alias_check['count'];
$domain_emails_result_stmt = Database::prepare("\n\t\t\t\tSELECT `email`, `email_full`, `destination`, `popaccountid` AS `number_email_forwarders`\n\t\t\t\tFROM `" . TABLE_MAIL_VIRTUAL . "` WHERE `customerid` = :customerid AND `domainid` = :id\n\t\t\t");
Database::pexecute($domain_emails_result_stmt, array('customerid' => $result['customerid'], 'id' => $result['id']));
$emails = Database::num_rows();
$email_forwarders = 0;
$email_accounts = 0;
while ($domain_emails_row = $domain_emails_result_stmt->fetch(PDO::FETCH_ASSOC)) {
if ($domain_emails_row['destination'] != '') {
$domain_emails_row['destination'] = explode(' ', makeCorrectDestination($domain_emails_row['destination']));
$email_forwarders += count($domain_emails_row['destination']);
if (in_array($domain_emails_row['email_full'], $domain_emails_row['destination'])) {
$email_forwarders -= 1;
$email_accounts++;
}
}
}
$ipsresult_stmt = Database::prepare("\n\t\t\t\tSELECT `id_ipandports` FROM `" . TABLE_DOMAINTOIP . "` WHERE `id_domain` = :id\n\t\t\t");
Database::pexecute($ipsresult_stmt, array('id' => $result['id']));
$usedips = array();
/**
* @param int $exercise_id
* @param int $course_id
* @param string $order
* @return array
*/
public function getCategoryExerciseTree($exercise_id, $course_id, $order = null, $shuffle = false, $excludeCategoryWithNoQuestions = true)
{
$table = Database::get_course_table(TABLE_QUIZ_REL_CATEGORY);
$table_category = Database::get_course_table(TABLE_QUIZ_CATEGORY);
$sql = "SELECT * FROM {$table} qc INNER JOIN {$table_category} c ON (category_id = c.iid)\n WHERE exercise_id = {$exercise_id} ";
if (!empty($order)) {
$sql .= "ORDER BY {$order}";
}
$categories = array();
$result = Database::query($sql);
if (Database::num_rows($result)) {
while ($row = Database::fetch_array($result, 'ASSOC')) {
if ($excludeCategoryWithNoQuestions) {
if ($row['count_questions'] == 0) {
continue;
}
}
$categories[$row['category_id']] = $row;
}
}
if ($shuffle) {
ArrayClass::shuffle_assoc($categories);
}
return $categories;
}