/**
* Get the classes to display on the current page.
*/
function get_class_data($from, $number_of_items, $column, $direction)
{
$class_table = Database::get_main_table(TABLE_MAIN_CLASS);
$course_class_table = Database::get_main_table(TABLE_MAIN_COURSE_CLASS);
$class_user_table = Database::get_main_table(TABLE_MAIN_CLASS_USER);
$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\n\t\t\t\t\t\t\tc.id AS col0,\n\t\t\t\t\t\t\tc.name AS col1,\n\t\t\t\t\t\t\tCOUNT(cu.user_id) AS col2,\n\t\t\t\t\t\t\tc.id AS col3\n\t\t\t\t\t\tFROM {$class_table} c\n\t\t\t\t\t\t";
$sql .= " LEFT JOIN {$class_user_table} cu ON cu.class_id = c.id";
$sql .= " 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) . "')";
}
$sql .= " GROUP BY c.id, c.name ";
$sql .= " ORDER BY col{$column} {$direction} ";
$sql .= " LIMIT {$from},{$number_of_items}";
$res = Database::query($sql);
$classes = array();
while ($class = Database::fetch_row($res)) {
$classes[] = $class;
}
return $classes;
}
/**
* Get the number of courses which will be displayed
*/
function get_number_of_courses()
{
$course_table = Database::get_main_table(TABLE_MAIN_COURSE);
$sql = "SELECT COUNT(code) AS total_number_of_items FROM {$course_table}";
if ((api_is_platform_admin() || api_is_session_admin()) && api_is_multiple_url_enabled() && api_get_current_access_url_id() != -1) {
$access_url_rel_course_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
$sql .= " INNER JOIN {$access_url_rel_course_table} url_rel_course ON (code=url_rel_course.course_code)";
}
if (isset($_GET['keyword'])) {
$keyword = Database::escape_string("%" . $_GET['keyword'] . "%");
$sql .= " WHERE (\n title LIKE '" . $keyword . "' OR\n code LIKE '" . $keyword . "' OR\n visual_code LIKE '" . $keyword . "'\n )\n ";
} elseif (isset($_GET['keyword_code'])) {
$keyword_code = Database::escape_string("%" . $_GET['keyword_code'] . "%");
$keyword_title = Database::escape_string("%" . $_GET['keyword_title'] . "%");
$keyword_category = Database::escape_string("%" . $_GET['keyword_category'] . "%");
$keyword_language = Database::escape_string("%" . $_GET['keyword_language'] . "%");
$keyword_visibility = Database::escape_string("%" . $_GET['keyword_visibility'] . "%");
$keyword_subscribe = Database::escape_string($_GET['keyword_subscribe']);
$keyword_unsubscribe = Database::escape_string($_GET['keyword_unsubscribe']);
$sql .= " WHERE\n (code LIKE '" . $keyword_code . "' OR visual_code LIKE '" . $keyword_code . "') AND\n title LIKE '" . $keyword_title . "' AND\n category_code LIKE '" . $keyword_category . "' AND\n course_language LIKE '" . $keyword_language . "' AND\n visibility LIKE '" . $keyword_visibility . "' AND\n subscribe LIKE '" . $keyword_subscribe . "' AND\n unsubscribe LIKE '" . $keyword_unsubscribe . "'\n ";
}
// adding the filter to see the user's only of the current access_url
if ((api_is_platform_admin() || api_is_session_admin()) && api_is_multiple_url_enabled() && api_get_current_access_url_id() != -1) {
$sql .= " AND url_rel_course.access_url_id=" . api_get_current_access_url_id();
}
$res = Database::query($sql);
$obj = Database::fetch_object($res);
return $obj->total_number_of_items;
}
/**
*
*/
function get_course_usage($course_code, $session_id = 0)
{
$table = Database::get_main_table(TABLE_MAIN_COURSE);
$course_code = Database::escape_string($course_code);
$sql = "SELECT * FROM {$table} WHERE code='" . $course_code . "'";
$res = Database::query($sql);
$course = Database::fetch_object($res);
// Learnpaths
$table = Database::get_course_table(TABLE_LP_MAIN);
$usage[] = array(get_lang(ucfirst(TOOL_LEARNPATH)), CourseManager::count_rows_course_table($table, $session_id, $course->id));
// Forums
$table = Database::get_course_table(TABLE_FORUM);
$usage[] = array(get_lang('Forums'), CourseManager::count_rows_course_table($table, $session_id, $course->id));
// Quizzes
$table = Database::get_course_table(TABLE_QUIZ_TEST);
$usage[] = array(get_lang(ucfirst(TOOL_QUIZ)), CourseManager::count_rows_course_table($table, $session_id, $course->id));
// Documents
$table = Database::get_course_table(TABLE_DOCUMENT);
$usage[] = array(get_lang(ucfirst(TOOL_DOCUMENT)), CourseManager::count_rows_course_table($table, $session_id, $course->id));
// Groups
$table = Database::get_course_table(TABLE_GROUP);
$usage[] = array(get_lang(ucfirst(TOOL_GROUP)), CourseManager::count_rows_course_table($table, $session_id, $course->id));
// Calendar
$table = Database::get_course_table(TABLE_AGENDA);
$usage[] = array(get_lang(ucfirst(TOOL_CALENDAR_EVENT)), CourseManager::count_rows_course_table($table, $session_id, $course->id));
// Link
$table = Database::get_course_table(TABLE_LINK);
$usage[] = array(get_lang(ucfirst(TOOL_LINK)), CourseManager::count_rows_course_table($table, $session_id, $course->id));
// Announcements
$table = Database::get_course_table(TABLE_ANNOUNCEMENT);
$usage[] = array(get_lang(ucfirst(TOOL_ANNOUNCEMENT)), CourseManager::count_rows_course_table($table, $session_id, $course->id));
return $usage;
}
/**
* Get the total number of users on the platform
* @see SortableTable#get_total_number_of_items()
*/
function get_number_of_groups()
{
$group_table = Database::get_main_table(TABLE_MAIN_GROUP);
$sql = "SELECT COUNT(g.id) AS total_number_of_items FROM {$group_table} g";
// adding the filter to see the user's only of the current access_url
/*
global $_configuration;
if ((api_is_platform_admin() || api_is_session_admin()) && $_configuration['multiple_access_urls'] && api_get_current_access_url_id()!=-1) {
$access_url_rel_user_table= Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
$sql.= " INNER JOIN $access_url_rel_user_table url_rel_user ON (u.user_id=url_rel_user.user_id)";
}
*/
if (isset($_GET['keyword'])) {
$keyword = Database::escape_string(trim($_GET['keyword']));
$sql .= " WHERE (g.name LIKE '%" . $keyword . "%' OR g.description LIKE '%" . $keyword . "%' OR g.url LIKE '%" . $keyword . "%' )";
}
// adding the filter to see the user's only of the current access_url
/*
if ((api_is_platform_admin() || api_is_session_admin()) && $_configuration['multiple_access_urls'] && api_get_current_access_url_id()!=-1) {
$sql.= " AND url_rel_user.access_url_id=".api_get_current_access_url_id();
} */
$res = Database::query($sql);
$obj = Database::fetch_object($res);
return $obj->total_number_of_items;
}
/**
* @param $course_code
* @return array|bool
*/
function initializeReport($course_code)
{
$course_info = api_get_course_info($course_code);
$table_reporte_semanas = Database::get_main_table('rp_reporte_semanas');
$table_students_report = Database::get_main_table('rp_students_report');
$table_semanas_curso = Database::get_main_table('rp_semanas_curso');
$table_course_rel_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
$table_post = Database::get_course_table(TABLE_FORUM_POST, $course_info['dbName']);
$table_work = Database::get_course_table(TABLE_STUDENT_PUBLICATION, $course_info['dbName']);
$course_code = Database::escape_string($course_code);
$res = Database::query("SELECT COUNT(*) as cant FROM $table_reporte_semanas WHERE course_code = '" . $course_code . "'");
$sqlWeeks = "SELECT semanas FROM $table_semanas_curso WHERE course_code = '$course_code'";
$resWeeks = Database::query($sqlWeeks);
$weeks = Database::fetch_object($resWeeks);
$obj = Database::fetch_object($res);
$weeksCount = (!isset($_POST['weeksNumber'])) ? (($weeks->semanas == 0) ? 7 : $weeks->semanas) : $_POST['weeksNumber'];
$weeksCount = Database::escape_string($weeksCount);
Database::query("REPLACE INTO $table_semanas_curso (course_code , semanas) VALUES ('$course_code','$weeksCount')");
if (intval($obj->cant) != $weeksCount) {
if (intval($obj->cant) > $weeksCount) {
$sql = "DELETE FROM $table_reporte_semanas WHERE week_id > $weeksCount AND course_code = '$course_code'";
Database::query("DELETE FROM $table_reporte_semanas WHERE week_id > $weeksCount AND course_code = '$course_code'");
} else {
for ($i = $obj->cant + 1; $i <= $weeksCount; $i++) {
if (!Database::query("INSERT INTO $table_reporte_semanas (week_id, course_code, forum_id, work_id, quiz_id, pc_id)
VALUES ($i, '$course_code', '0', '0', '0', '0' )")) {
return false;
}
}
}
}
$sql = "REPLACE INTO $table_students_report (user_id, week_report_id, work_ok , thread_ok , quiz_ok , pc_ok)
SELECT cu.user_id, rs.id, 0, 0, 0, 0
FROM $table_course_rel_user cu
LEFT JOIN $table_reporte_semanas rs ON cu.course_code = rs.course_code
WHERE cu.status = 5 AND rs.course_code = '$course_code'
ORDER BY cu.user_id, rs.id";
if (!Database::query($sql)) {
return false;
} else {
$page = (!isset($_GET['page'])) ? 1 : $_GET['page'];
Database::query("UPDATE $table_students_report sr SET sr.work_ok = 1
WHERE CONCAT (sr.user_id,',',sr.week_report_id)
IN (SELECT DISTINCT CONCAT(w.user_id,',',rs.id)
FROM $table_work w JOIN $table_reporte_semanas rs ON w.parent_id = rs.work_id)");
Database::query("UPDATE $table_students_report sr SET sr.thread_ok = 1
WHERE CONCAT (sr.user_id,',',sr.week_report_id)
IN (SELECT DISTINCT CONCAT(f.poster_id,',',rs.id)
FROM $table_post f JOIN $table_reporte_semanas rs ON f.thread_id = rs.forum_id)");
return showResults($course_info, $weeksCount, $page);
}
}
/**
* Gets the total number of classes.
*/
function get_number_of_classes()
{
$tbl_class = Database::get_main_table(TABLE_MAIN_CLASS);
$sql = "SELECT COUNT(*) AS number_of_classes FROM {$tbl_class}";
if (isset($_GET['keyword'])) {
$sql .= " WHERE (name LIKE '%" . Database::escape_string(trim($_GET['keyword'])) . "%')";
}
$res = Database::query($sql);
$obj = Database::fetch_object($res);
return $obj->number_of_classes;
}
/**
* Get the total number of attendance inside current course and current session
* @see SortableTable#get_total_number_of_items()
*/
static function get_number_of_attendances()
{
$tbl_attendance = Database::get_course_table(TABLE_ATTENDANCE);
$session_id = api_get_session_id();
$condition_session = api_get_session_condition($session_id);
$course_id = api_get_course_int_id();
$sql = "SELECT COUNT(att.id) AS total_number_of_items FROM {$tbl_attendance} att\n\t\t WHERE c_id = {$course_id} AND att.active = 1 {$condition_session} ";
$res = Database::query($sql);
$obj = Database::fetch_object($res);
return $obj->total_number_of_items;
}
/**
* Saves imported data.
*/
function save_data($users_courses)
{
$user_table = Database::get_main_table(TABLE_MAIN_USER);
$course_user_table = Database::get_main_table(TABLE_MAIN_COURSE_USER);
$csv_data = array();
$inserted_in_course = array();
foreach ($users_courses as $user_course) {
$csv_data[$user_course['Email']][$user_course['CourseCode']] = $user_course['Status'];
}
foreach ($csv_data as $email => $csv_subscriptions) {
$sql = "SELECT * FROM {$user_table} u\n WHERE u.email = '" . Database::escape_string($email) . "' LIMIT 1";
$res = Database::query($sql);
$obj = Database::fetch_object($res);
$user_id = $obj->user_id;
$sql = "SELECT * FROM {$course_user_table} cu\n WHERE cu.user_id = {$user_id} AND cu.relation_type <> " . COURSE_RELATION_TYPE_RRHH . " ";
$res = Database::query($sql);
$db_subscriptions = array();
while ($obj = Database::fetch_object($res)) {
$db_subscriptions[$obj->c_id] = $obj->status;
}
$to_subscribe = array_diff(array_keys($csv_subscriptions), array_keys($db_subscriptions));
$to_unsubscribe = array_diff(array_keys($db_subscriptions), array_keys($csv_subscriptions));
if ($_POST['subscribe']) {
foreach ($to_subscribe as $courseId) {
$courseInfo = api_get_course_info_by_id($courseId);
$course_code = $courseInfo['code'];
if (CourseManager::course_exists($course_code)) {
$course_info = CourseManager::get_course_information($course_code);
$inserted_in_course[$course_code] = $course_info['title'];
CourseManager::subscribe_user($user_id, $course_code, $csv_subscriptions[$course_code]);
$inserted_in_course[$course_info['code']] = $course_info['title'];
}
}
}
if ($_POST['unsubscribe']) {
foreach ($to_unsubscribe as $courseId) {
$courseInfo = api_get_course_info_by_id($courseId);
$course_code = $courseInfo['code'];
if (CourseManager::course_exists($course_code)) {
CourseManager::unsubscribe_user($user_id, $course_code);
$course_info = CourseManager::get_course_information($course_code);
CourseManager::unsubscribe_user($user_id, $course_code);
$inserted_in_course[$course_info['code']] = $course_info['title'];
}
}
}
}
return $inserted_in_course;
}
/**
* Saves imported data.
*/
function save_data($users_courses)
{
$user_table = Database::get_main_table(TABLE_MAIN_USER);
$course_user_table = Database::get_main_table(TABLE_MAIN_COURSE_USER);
$csv_data = array();
foreach ($users_courses as $user_course) {
$csv_data[$user_course['UserName']][$user_course['CourseCode']] = $user_course['Status'];
}
foreach ($csv_data as $username => $csv_subscriptions) {
$sql = "SELECT * FROM {$user_table} u WHERE u.username = '******'";
$res = Database::query($sql);
$obj = Database::fetch_object($res);
$user_id = $obj->user_id;
$sql = "SELECT * FROM {$course_user_table} cu\n\t\t WHERE cu.user_id = {$user_id} AND cu.relation_type<>" . COURSE_RELATION_TYPE_RRHH . " ";
$res = Database::query($sql);
$db_subscriptions = array();
while ($obj = Database::fetch_object($res)) {
$db_subscriptions[$obj->c_id] = $obj->status;
}
$csvCourseList = array();
foreach ($csv_subscriptions as $courseCode => $status) {
$courseInfo = api_get_course_info($courseCode);
if ($courseInfo) {
$csvCourseList[$courseInfo['real_id']] = $status;
}
}
$to_subscribe = array_diff(array_keys($csvCourseList), array_keys($db_subscriptions));
$to_unsubscribe = array_diff(array_keys($db_subscriptions), array_keys($csvCourseList));
global $inserted_in_course;
if (!isset($inserted_in_course)) {
$inserted_in_course = array();
}
if (isset($_POST['subscribe']) && $_POST['subscribe']) {
foreach ($to_subscribe as $courseId) {
CourseManager::add_user_to_course($user_id, $courseId, $csvCourseList[$courseId]);
$course_info = api_get_course_info_by_id($courseId);
$inserted_in_course[$courseId] = $course_info['title'];
}
}
if (isset($_POST['unsubscribe']) && $_POST['unsubscribe']) {
foreach ($to_unsubscribe as $courseId) {
CourseManager::unsubscribe_user($user_id, $courseId);
$course_info = api_get_course_info_by_id($courseId);
$inserted_in_course[$courseId] = $course_info['title'];
}
}
}
}
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;
}
}
/**
* @param $course_code
* @return array|bool
*/
function initializeReport($course_code)
{
$course_info = api_get_course_info($course_code);
$table_reporte_semanas = Database::get_main_table('rp_reporte_semanas');
$table_students_report = Database::get_main_table('rp_students_report');
$table_semanas_curso = Database::get_main_table('rp_semanas_curso');
$courseTable = Database::get_main_table(TABLE_MAIN_COURSE);
$table_course_rel_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
$table_post = Database::get_course_table(TABLE_FORUM_POST);
$table_work = Database::get_course_table(TABLE_STUDENT_PUBLICATION);
$course_code = Database::escape_string($course_code);
$res = Database::query("SELECT COUNT(*) as cant FROM {$table_reporte_semanas} WHERE course_code = '" . $course_code . "'");
$sqlWeeks = "SELECT semanas FROM {$table_semanas_curso} WHERE course_code = '{$course_code}'";
$resWeeks = Database::query($sqlWeeks);
$weeks = Database::fetch_object($resWeeks);
$obj = Database::fetch_object($res);
$weeksCount = !isset($_POST['weeksNumber']) ? $weeks->semanas == 0 ? 7 : $weeks->semanas : $_POST['weeksNumber'];
$weeksCount = Database::escape_string($weeksCount);
Database::query("REPLACE INTO {$table_semanas_curso} (course_code , semanas) VALUES ('{$course_code}','{$weeksCount}')");
if (intval($obj->cant) != $weeksCount) {
if (intval($obj->cant) > $weeksCount) {
$sql = "DELETE FROM {$table_reporte_semanas} WHERE week_id > {$weeksCount} AND course_code = '{$course_code}'";
Database::query("DELETE FROM {$table_reporte_semanas} WHERE week_id > {$weeksCount} AND course_code = '{$course_code}'");
} else {
for ($i = $obj->cant + 1; $i <= $weeksCount; $i++) {
if (!Database::query("INSERT INTO {$table_reporte_semanas} (week_id, course_code, forum_id, work_id, quiz_id, pc_id)\n\t\t\t\t\t\tVALUES ({$i}, '{$course_code}', '0', '0', '0', '0' )")) {
return false;
}
}
}
}
$sql = "REPLACE INTO {$table_students_report} (user_id, week_report_id, work_ok , thread_ok , quiz_ok , pc_ok)\n\t\t\tSELECT cu.user_id, rs.id, 0, 0, 0, 0\n\t\t\tFROM {$table_course_rel_user} cu\n\t\t\tINNER JOIN {$courseTable} c\n\t\t\tON (c.id = cu.c_id)\n\t\t\tLEFT JOIN {$table_reporte_semanas} rs ON c.code = rs.course_code\n\t\t\tWHERE cu.status = 5 AND rs.course_code = '{$course_code}'\n\t\t\tORDER BY cu.user_id, rs.id";
if (!Database::query($sql)) {
return false;
} else {
$page = !isset($_GET['page']) ? 1 : $_GET['page'];
Database::query("UPDATE {$table_students_report} sr SET sr.work_ok = 1\n\t\tWHERE CONCAT (sr.user_id,',',sr.week_report_id)\n\t\tIN (SELECT DISTINCT CONCAT(w.user_id,',',rs.id)\n\t\tFROM {$table_work} w JOIN {$table_reporte_semanas} rs ON w.parent_id = rs.work_id)");
Database::query("UPDATE {$table_students_report} sr SET sr.thread_ok = 1\n\t\tWHERE CONCAT (sr.user_id,',',sr.week_report_id)\n\t\tIN (SELECT DISTINCT CONCAT(f.poster_id,',',rs.id)\n\t\tFROM {$table_post} f JOIN {$table_reporte_semanas} rs ON f.thread_id = rs.forum_id)");
return showResults($course_info, $weeksCount, $page);
}
}
function inicializarReporte($course_code)
{
$course_info = api_get_course_info($course_code);
$table_reporte_semanas = Database::get_main_table('rp_reporte_semanas');
$table_students_report = Database::get_main_table('rp_students_report');
$table_semanas_curso = Database::get_main_table('rp_semanas_curso');
$table_course_rel_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
$table_post = Database::get_course_table(TABLE_FORUM_POST, $course_info['dbName']);
$table_work = Database::get_course_table(TABLE_STUDENT_PUBLICATION, $course_info['dbName']);
$res = Database::query("SELECT COUNT(*) as cant FROM {$table_reporte_semanas} WHERE course_code = '" . $course_code . "'");
$sql_semanas = "SELECT semanas FROM {$table_semanas_curso} WHERE course_code = '{$course_code}'";
$res_semanas = Database::query($sql_semanas);
$semanas = Database::fetch_object($res_semanas);
$obj = Database::fetch_object($res);
$numero_semanas = !isset($_POST['numerosemanas']) ? $semanas->semanas == 0 ? 7 : $semanas->semanas : $_POST['numerosemanas'];
Database::query("REPLACE INTO {$table_semanas_curso} (course_code , semanas) VALUES ('{$course_code}','{$numero_semanas}')");
if (intval($obj->cant) != $numero_semanas) {
if (intval($obj->cant) > $numero_semanas) {
$sql = "DELETE FROM {$table_reporte_semanas} WHERE week_id > {$numero_semanas} AND course_code = '{$course_code}'";
Database::query("DELETE FROM {$table_reporte_semanas} WHERE week_id > {$numero_semanas} AND course_code = '{$course_code}'");
} else {
for ($i = $obj->cant + 1; $i <= $numero_semanas; $i++) {
if (!Database::query("INSERT INTO {$table_reporte_semanas} (week_id,course_code,forum_id,work_id,quiz_id,pc_id)\n\t\t\t\t\t\tVALUES ('{$i}','{$course_code}','0','0','0','0' )")) {
return false;
}
}
}
}
$sql = "REPLACE INTO {$table_students_report} (user_id,week_report_id, work_ok , thread_ok , quiz_ok , pc_ok)\n\t\t\tSELECT cu.user_id, rs.id, 0, 0, 0, 0\n\t\t\tFROM {$table_course_rel_user} cu\n\t\t\tLEFT JOIN {$table_reporte_semanas} rs ON cu.course_code = rs.course_code\n\t\t\tWHERE cu.status = '5' AND rs.course_code = '{$course_code}'\n\t\t\tORDER BY cu.user_id, rs.id";
if (!Database::query($sql)) {
return false;
} else {
$pagina = !isset($_GET['page']) ? 1 : $_GET['page'];
Database::query("UPDATE {$table_students_report} sr SET sr.work_ok = 1\n\t\tWHERE CONCAT (sr.user_id,',',sr.week_report_id)\n\t\tIN (SELECT DISTINCT CONCAT(w.user_id,',',rs.id)\n\t\tFROM {$table_work} w JOIN {$table_reporte_semanas} rs ON w.parent_id = rs.work_id)");
Database::query("UPDATE {$table_students_report} sr SET sr.thread_ok = 1\n\t\tWHERE CONCAT (sr.user_id,',',sr.week_report_id)\n\t\tIN (SELECT DISTINCT CONCAT(f.poster_id,',',rs.id)\n\t\tFROM {$table_post} f JOIN {$table_reporte_semanas} rs ON f.thread_id = rs.forum_id)");
return mostrarResultados($course_info, $numero_semanas, $pagina);
}
}
/**
* Saves imported data.
*/
function save_data($users_classes, $deleteUsersNotInList = false)
{
global $purification_option_for_usernames;
// Table definitions.
$user_table = Database::get_main_table(TABLE_MAIN_USER);
$usergroup = new UserGroup();
// Data parsing: purification + conversion (UserName, ClassName) --> (user_is, class_id)
$csv_data = array();
if (!empty($users_classes)) {
foreach ($users_classes as $user_class) {
$sql1 = "SELECT user_id FROM {$user_table}\n WHERE username = '******'UserName'], $purification_option_for_usernames)) . "'";
$res1 = Database::query($sql1);
$obj1 = Database::fetch_object($res1);
$usergroup = new UserGroup();
$id = $usergroup->get_id_by_name($user_class['ClassName']);
if ($obj1 && $id) {
$csv_data[$id]['user_list'][] = $obj1->user_id;
$csv_data[$id]['user_list_name'][] = $user_class['UserName'];
$csv_data[$id]['class_name'] = $user_class['ClassName'];
}
}
}
// Logic for processing the request (data + UI options).
$message = null;
if (!empty($csv_data)) {
foreach ($csv_data as $class_id => $user_data) {
$user_list = $user_data['user_list'];
$class_name = $user_data['class_name'];
$user_list_name = $user_data['user_list_name'];
$usergroup->subscribe_users_to_usergroup($class_id, $user_list, $deleteUsersNotInList);
$message .= Display::return_message(get_lang('Class') . ': ' . $class_name . '<br />', 'normal', false);
$message .= Display::return_message(get_lang('Users') . ': ' . implode(', ', $user_list_name));
}
}
return $message;
}
cu.user_id = ' . $user['user_id'] . ' AND
cu.c_id = c.id AND
cu.relation_type <> ' . COURSE_RELATION_TYPE_RRHH . ' ';
$res = Database::query($sql);
if (Database::num_rows($res) > 0) {
$header = array(array(get_lang('Code'), true), array(get_lang('Title'), true), array(get_lang('Status'), true), array(get_lang('TimeSpentInTheCourse'), true), array(get_lang('TotalPostsInAllForums'), true), array('', false));
$headerList = array();
foreach ($header as $item) {
$headerList[] = $item[0];
}
$csvContent[] = array();
$csvContent[] = array(get_lang('Courses'));
$csvContent[] = $headerList;
$data = array();
$courseToolInformationTotal = null;
while ($course = Database::fetch_object($res)) {
$courseInfo = api_get_course_info_by_id($course->c_id);
$courseCode = $courseInfo['code'];
$courseToolInformation = null;
$tools = '<a href="course_information.php?code=' . $courseCode . '">' . Display::return_icon('synthese_view.gif', get_lang('Overview')) . '</a>' . '<a href="' . $courseInfo['course_public_url'] . '">' . Display::return_icon('course_home.gif', get_lang('CourseHomepage')) . '</a>' . '<a href="course_edit.php?course_code=' . $courseCode . '">' . Display::return_icon('edit.gif', get_lang('Edit')) . '</a>';
if ($course->status == STUDENT) {
$tools .= '<a href="user_information.php?action=unsubscribe&course_code=' . $courseCode . '&user_id=' . $user['user_id'] . '">' . Display::return_icon('delete.png', get_lang('Delete')) . '</a>';
}
$timeSpent = api_time_to_hms(Tracking::get_time_spent_on_the_course($user['user_id'], $courseInfo['real_id'], 0));
$totalForumMessages = CourseManager::getCountPostInForumPerUser($user['user_id'], $course->id, 0);
$row = array(Display::url($courseCode, $courseInfo['course_public_url']), $course->title, $course->status == STUDENT ? get_lang('Student') : get_lang('Teacher'), $timeSpent, $totalForumMessages, $tools);
$csvContent[] = array_map('strip_tags', $row);
$data[] = $row;
$result = TrackingUserLogCSV::getToolInformation($user['user_id'], $courseInfo, 0);
$courseToolInformationTotal .= $result['html'];
$csvContent = array_merge($csvContent, $result['array']);
/**
* Restore Quiz
*/
function restore_quizzes($session_id = 0, $respect_base_content = false)
{
if ($this->course->has_resources(RESOURCE_QUIZ)) {
$table_qui = Database::get_course_table(TABLE_QUIZ_TEST);
$table_rel = Database::get_course_table(TABLE_QUIZ_TEST_QUESTION);
$table_doc = Database::get_course_table(TABLE_DOCUMENT);
$resources = $this->course->resources;
foreach ($resources[RESOURCE_QUIZ] as $id => $quiz) {
if (isset($quiz->obj)) {
//For new imports
$quiz = $quiz->obj;
} else {
//For backward compatibility
$quiz->obj = $quiz;
}
$doc = '';
if (!empty($quiz->sound)) {
if (isset($this->course->resources[RESOURCE_DOCUMENT][$quiz->sound]) && $this->course->resources[RESOURCE_DOCUMENT][$quiz->sound]->is_restored()) {
$sql = "SELECT path FROM " . $table_doc . " WHERE c_id = " . $this->destination_course_id . " AND id = " . $resources[RESOURCE_DOCUMENT][$quiz->sound]->destination_id;
$doc = Database::query($sql);
$doc = Database::fetch_object($doc);
$doc = str_replace('/audio/', '', $doc->path);
}
}
if ($id != -1) {
// check resources inside html from fckeditor tool and copy correct urls into recipient course
$quiz->description = DocumentManager::replace_urls_inside_content_html_from_copy_course($quiz->description, $this->course->code, $this->course->destination_path, $this->course->backup_path, $this->course->info['path']);
global $_custom;
if (isset($_custom['exercises_clean_dates_when_restoring']) && $_custom['exercises_clean_dates_when_restoring']) {
$quiz->start_time = null;
$quiz->end_time = null;
}
$params = array('c_id' => $this->destination_course_id, 'title' => self::DBUTF8($quiz->title), 'description' => self::DBUTF8($quiz->description), 'type' => isset($quiz->quiz_type) ? $quiz->quiz_type : $quiz->type, 'random' => $quiz->random, 'active' => $quiz->active, 'sound' => self::DBUTF8($doc), 'max_attempt' => (int) $quiz->max_attempt, 'results_disabled' => (int) $quiz->results_disabled, 'access_condition' => $quiz->access_condition, 'start_time' => $quiz->start_time, 'pass_percentage' => $quiz->pass_percentage, 'end_time' => $quiz->end_time, 'feedback_type' => (int) $quiz->feedback_type, 'random_answers' => (int) $quiz->random_answers, 'random_by_category' => $quiz->random_by_category, 'review_answers' => $quiz->review_answers, 'propagate_neg' => $quiz->propagate_neg, 'text_when_finished' => $quiz->text_when_finished, 'expired_time' => (int) $quiz->expired_time, 'end_button' => (int) $quiz->end_button);
if ($respect_base_content) {
$my_session_id = $quiz->session_id;
if (!empty($quiz->session_id)) {
$my_session_id = $session_id;
}
$params['session_id'] = $my_session_id;
} else {
if (!empty($session_id)) {
$session_id = intval($session_id);
$params['session_id'] = $session_id;
}
}
$new_id = Database::insert($table_qui, $params);
} else {
// $id = -1 identifies the fictionary test for collecting orphan questions. We do not store it in the database.
$new_id = -1;
}
if ($new_id && $new_id != -1) {
// Updates the question position
$exercise = new Exercise($this->destination_course_id);
$exercise->read($new_id);
$exercise->addExerciseToOrderTable();
$this->course->resources[RESOURCE_QUIZ][$id]->obj->destination_id = $new_id;
$order = 0;
if (!empty($quiz->question_ids)) {
foreach ($quiz->question_ids as $index => $question_id) {
$qid = $this->restore_quiz_question($question_id);
$question_order = $quiz->question_orders[$index] ? $quiz->question_orders[$index] : ++$order;
$sql = "INSERT IGNORE INTO " . $table_rel . " SET c_id = " . $this->destination_course_id . ", question_id = " . $qid . ", exercice_id = " . $new_id . ", question_order = " . $question_order;
Database::query($sql);
}
}
if (isset($quiz->categorie) && $quiz->categories) {
$exercise_obj = new Exercise($this->destination_course_id);
$exercise_obj->read($new_id);
$cats = array();
foreach ($quiz->categories as $cat) {
$cat_from_db = new Testcategory($cat['category_id']);
/*$cat_from_db = new Testcategory($cat['category_id']);
if ($cat_from_db && $cat_from_db->title == $cat['title']) {
echo '1';
//use the same id
$cats[$cat_from_db->id] = $cat['count_questions'];
} else {*/
$cat_from_db = $cat_from_db->get_category_by_title($cat['title'], $this->destination_course_id);
if (empty($cat_from_db)) {
//Create a new category in this portal
if ($cat['category_id'] == 0) {
$category_c_id = 0;
} else {
$category_c_id = $this->destination_course_id;
}
$new_cat = new Testcategory(null, $cat['title'], $cat['description'], null, 'simple', $category_c_id);
$new_cat_id = $new_cat->addCategoryInBDD();
$cats[$new_cat_id] = $cat['count_questions'];
} else {
$cats[$cat_from_db['iid']] = $cat['count_questions'];
}
//}
}
$exercise_obj->save_categories_in_exercise($cats);
}
}
}
}
}
/**
* @param $from
* @param $number_of_items
* @param $column
* @param $direction
* @return array
*/
public static function get_item_resources_data($from, $number_of_items, $column, $direction)
{
$session_id = api_get_session_id();
$course_id = api_get_course_int_id();
$table_item_property = Database::get_course_table(TABLE_ITEM_PROPERTY);
$table_user = Database::get_main_table(TABLE_MAIN_USER);
$table_session = Database::get_main_table(TABLE_MAIN_SESSION);
$session_id = intval($session_id);
$sql = "SELECT\n tool as col0,\n lastedit_type as col1,\n ref as ref,\n user.username as col3,\n insert_date as col5,\n visibility as col6,\n user.user_id as user_id\n FROM {$table_item_property} track_resource, {$table_user} user\n WHERE\n track_resource.c_id = {$course_id} AND\n track_resource.insert_user_id = user.user_id AND\n session_id = {$session_id} ";
if (isset($_GET['keyword'])) {
$keyword = Database::escape_string(trim($_GET['keyword']));
$sql .= " AND (\n \t\t user.username LIKE '%" . $keyword . "%' OR\n \t\t lastedit_type LIKE '%" . $keyword . "%' OR\n \t\t tool LIKE '%" . $keyword . "%'\n ) ";
}
$sql .= " AND tool IN (\n \t 'document',\n \t 'learnpath',\n \t 'quiz',\n \t 'glossary',\n \t 'link',\n \t 'course_description',\n \t 'announcement',\n \t 'thematic',\n \t 'thematic_advance',\n \t 'thematic_plan'\n )";
if ($column == 0) {
$column = '0';
}
if ($column != '' && $direction != '') {
if ($column != 2 && $column != 4) {
$sql .= " ORDER BY col{$column} {$direction}";
}
} else {
$sql .= " ORDER BY col5 DESC ";
}
$from = intval($from);
$number_of_items = intval($number_of_items);
$sql .= " LIMIT {$from}, {$number_of_items} ";
$res = Database::query($sql);
$resources = array();
$thematic_tools = array('thematic', 'thematic_advance', 'thematic_plan');
while ($row = Database::fetch_array($res)) {
$ref = $row['ref'];
$table_name = TrackingCourseLog::get_tool_name_table($row['col0']);
$table_tool = Database::get_course_table($table_name['table_name']);
$id = $table_name['id_tool'];
$recorset = false;
if (in_array($row['col0'], array('thematic_plan', 'thematic_advance'))) {
$tbl_thematic = Database::get_course_table(TABLE_THEMATIC);
$sql = "SELECT thematic_id FROM {$table_tool}\n \t\t\t WHERE c_id = {$course_id} AND id = {$ref}";
$rs_thematic = Database::query($sql);
if (Database::num_rows($rs_thematic)) {
$row_thematic = Database::fetch_array($rs_thematic);
$thematic_id = $row_thematic['thematic_id'];
$sql = "SELECT session.id, session.name, user.username\n FROM {$tbl_thematic} t, {$table_session} session, {$table_user} user\n WHERE\n t.c_id = {$course_id} AND\n t.session_id = session.id AND\n session.id_coach = user.user_id AND\n t.id = {$thematic_id}";
$recorset = Database::query($sql);
}
} else {
$sql = "SELECT session.id, session.name, user.username\n FROM {$table_tool} tool, {$table_session} session, {$table_user} user\n \t\t\t WHERE\n \t\t\t tool.c_id = {$course_id} AND\n \t\t\t tool.session_id = session.id AND\n \t\t\t session.id_coach = user.user_id AND\n \t\t\t tool.{$id} = {$ref}";
$recorset = Database::query($sql);
}
if (!empty($recorset)) {
$obj = Database::fetch_object($recorset);
$name_session = '';
$coach_name = '';
if (!empty($obj)) {
$name_session = $obj->name;
$coach_name = $obj->username;
}
$url_tool = api_get_path(WEB_CODE_PATH) . $table_name['link_tool'];
$row[0] = '';
if ($row['col6'] != 2) {
if (in_array($row['col0'], $thematic_tools)) {
$exp_thematic_tool = explode('_', $row['col0']);
$thematic_tool_title = '';
if (is_array($exp_thematic_tool)) {
foreach ($exp_thematic_tool as $exp) {
$thematic_tool_title .= api_ucfirst($exp);
}
} else {
$thematic_tool_title = api_ucfirst($row['col0']);
}
$row[0] = '<a href="' . $url_tool . '?' . api_get_cidreq() . '&action=thematic_details">' . get_lang($thematic_tool_title) . '</a>';
} else {
$row[0] = '<a href="' . $url_tool . '?' . api_get_cidreq() . '">' . get_lang('Tool' . api_ucfirst($row['col0'])) . '</a>';
}
} else {
$row[0] = api_ucfirst($row['col0']);
}
$row[1] = get_lang($row[1]);
$row[6] = api_convert_and_format_date($row['col5'], null, date_default_timezone_get());
$row[5] = '';
//@todo Improve this code please
switch ($table_name['table_name']) {
case 'document':
$sql = "SELECT tool.title as title FROM {$table_tool} tool\n WHERE c_id = {$course_id} AND id = {$ref}";
$rs_document = Database::query($sql);
$obj_document = Database::fetch_object($rs_document);
$row[5] = $obj_document->title;
break;
case 'announcement':
$sql = "SELECT title FROM {$table_tool}\n WHERE c_id = {$course_id} AND id = {$ref}";
$rs_document = Database::query($sql);
$obj_document = Database::fetch_object($rs_document);
if ($obj_document) {
$row[5] = $obj_document->title;
}
break;
case 'glossary':
$sql = "SELECT name FROM {$table_tool}\n \t\t\t\t\t WHERE c_id = {$course_id} AND glossary_id = {$ref}";
$rs_document = Database::query($sql);
$obj_document = Database::fetch_object($rs_document);
if ($obj_document) {
$row[5] = $obj_document->name;
}
break;
case 'lp':
$sql = "SELECT name\n FROM {$table_tool} WHERE c_id = {$course_id} AND id = {$ref}";
$rs_document = Database::query($sql);
$obj_document = Database::fetch_object($rs_document);
$row[5] = $obj_document->name;
break;
case 'quiz':
$sql = "SELECT title FROM {$table_tool}\n WHERE c_id = {$course_id} AND id = {$ref}";
$rs_document = Database::query($sql);
$obj_document = Database::fetch_object($rs_document);
if ($obj_document) {
$row[5] = $obj_document->title;
}
break;
case 'course_description':
$sql = "SELECT title FROM {$table_tool}\n WHERE c_id = {$course_id} AND id = {$ref}";
$rs_document = Database::query($sql);
$obj_document = Database::fetch_object($rs_document);
if ($obj_document) {
$row[5] = $obj_document->title;
}
break;
case 'thematic':
$rs = Database::query("SELECT title FROM {$table_tool} WHERE c_id = {$course_id} AND id = {$ref}");
if (Database::num_rows($rs) > 0) {
$obj = Database::fetch_object($rs);
$row[5] = $obj->title;
}
break;
case 'thematic_advance':
$rs = Database::query("SELECT content FROM {$table_tool} WHERE c_id = {$course_id} AND id = {$ref}");
if (Database::num_rows($rs) > 0) {
$obj = Database::fetch_object($rs);
$row[5] = $obj->content;
}
break;
case 'thematic_plan':
$rs = Database::query("SELECT title FROM {$table_tool} WHERE c_id = {$course_id} AND id = {$ref}");
if (Database::num_rows($rs) > 0) {
$obj = Database::fetch_object($rs);
$row[5] = $obj->title;
}
break;
default:
break;
}
$row2 = $name_session;
if (!empty($coach_name)) {
$row2 .= '<br />' . get_lang('Coach') . ': ' . $coach_name;
}
$row[2] = $row2;
if (!empty($row['col3'])) {
$userInfo = api_get_user_info($row['user_id']);
$row['col3'] = Display::url($row['col3'], $userInfo['profile_url']);
$row[3] = $row['col3'];
$ip = TrackingUserLog::get_ip_from_user_event($row['user_id'], $row['col5'], true);
if (empty($ip)) {
$ip = get_lang('Unknown');
}
$row[4] = $ip;
}
$resources[] = $row;
}
}
return $resources;
}
/**
* Displays announcements as an slideshow
* @param int $visible VISIBLE_GUEST, VISIBLE_STUDENT or VISIBLE_TEACHER
* @param int $id The identifier of the announcement to display
*/
public static function display_announcements_slider($visible, $id = null)
{
$user_selected_language = Database::escape_string(api_get_interface_language());
$table = Database::get_main_table(TABLE_MAIN_SYSTEM_ANNOUNCEMENTS);
$cut_size = 500;
$now = api_get_utc_datetime();
$sql = "SELECT * FROM " . $table . "\n\t\t\t\tWHERE\n\t\t\t\t (lang = '{$user_selected_language}' OR lang IS NULL) AND\n\t\t\t\t ('{$now}' >= date_start AND '{$now}' <= date_end) ";
switch ($visible) {
case self::VISIBLE_GUEST:
$sql .= " AND visible_guest = 1 ";
break;
case self::VISIBLE_STUDENT:
$sql .= " AND visible_student = 1 ";
break;
case self::VISIBLE_TEACHER:
$sql .= " AND visible_teacher = 1 ";
break;
}
if (isset($id) && !empty($id)) {
$id = intval($id);
$sql .= " AND id = {$id} ";
}
if (api_is_multiple_url_enabled()) {
$current_url_id = api_get_current_access_url_id();
$sql .= " AND access_url_id IN ('1', '{$current_url_id}') ";
}
$sql .= " ORDER BY date_start DESC";
$result = Database::query($sql);
$announcements = [];
if (Database::num_rows($result) > 0) {
while ($announcement = Database::fetch_object($result)) {
$announcementData = ['id' => $announcement->id, 'title' => $announcement->title, 'content' => $announcement->content, 'readMore' => null];
if (empty($id)) {
if (api_strlen(strip_tags($announcement->content)) > $cut_size) {
$announcementData['content'] = cut($announcement->content, $cut_size);
$announcementData['readMore'] = true;
}
}
$announcements[] = $announcementData;
}
}
if (count($announcements) === 0) {
return null;
}
$template = new Template(null, false, false);
$template->assign('announcements', $announcements);
return $template->fetch('default/announcement/slider.tpl');
}
/**
* Get the session id based on the original id and field name in the extra fields. Returns 0 if session was not found
*
* @param string Original session id
* @param string Original field name
* @return int Session id
*/
public static function get_session_id_from_original_id($original_session_id_value, $original_session_id_name)
{
$t_sfv = Database::get_main_table(TABLE_MAIN_SESSION_FIELD_VALUES);
$table_field = Database::get_main_table(TABLE_MAIN_SESSION_FIELD);
$sql_session = "SELECT session_id FROM {$table_field} sf INNER JOIN {$t_sfv} sfv ON sfv.field_id=sf.id WHERE field_variable='{$original_session_id_name}' AND field_value='{$original_session_id_value}'";
$res_session = Database::query($sql_session);
$row = Database::fetch_object($res_session);
if ($row) {
return $row->session_id;
} else {
return 0;
}
}
/**
* This function calculates the total number of surveys
*
* @return integer Total number of surveys
*
* @author Patrick Cool <*****@*****.**>, Ghent University
* @version January 2007
*/
static function get_number_of_surveys()
{
$table_survey = Database::get_course_table(TABLE_SURVEY);
$course_id = api_get_course_int_id();
$search_restriction = SurveyUtil::survey_search_restriction();
if ($search_restriction) {
$search_restriction = 'WHERE c_id = ' . $course_id . ' AND ' . $search_restriction;
} else {
$search_restriction = "WHERE c_id = {$course_id}";
}
$sql = "SELECT count(survey_id) AS total_number_of_items\n\t\t FROM " . $table_survey . ' ' . $search_restriction;
$res = Database::query($sql);
$obj = Database::fetch_object($res);
return $obj->total_number_of_items;
}
/**
* @author Patrick Cool <*****@*****.**>, Ghent University
* Get all subscribed tutors of a group
* @param int $group_id
* @return array An array with information of all users from the given group.
* (user_id, firstname, lastname, email)
*/
public static function get_subscribed_tutors($group_id, $id_only = false)
{
$table_user = Database::get_main_table(TABLE_MAIN_USER);
$table_group_tutor = Database::get_course_table(TABLE_GROUP_TUTOR);
$order_clause = api_sort_by_first_name() ? ' ORDER BY u.firstname, u.lastname' : ' ORDER BY u.lastname, u.firstname';
$group_id = Database::escape_string($group_id);
$course_id = api_get_course_int_id();
$sql = "SELECT tg.id, u.user_id, u.lastname, u.firstname, u.email\n FROM " . $table_user . " u, " . $table_group_tutor . " tg\n WHERE tg.c_id = {$course_id} AND\n tg.group_id='" . $group_id . "' AND\n tg.user_id=u.user_id" . $order_clause;
$db_result = Database::query($sql);
$users = array();
while ($user = Database::fetch_object($db_result)) {
if (!$id_only) {
$member['user_id'] = $user->user_id;
$member['firstname'] = $user->firstname;
$member['lastname'] = $user->lastname;
$member['email'] = $user->email;
$users[] = $member;
} else {
$users[] = $user->user_id;
}
}
return $users;
}
}
//Admin setting for Hide/Show Default folders to all users
if (api_get_setting('show_default_folders') == 'false' && ($val == '/images' || $val == '/flash' || $val == '/audio' || $val == '/video' || strstr($val, '/images/gallery') || $val == '/video/flv')) {
continue;
}
//Admin setting for Hide/Show chat history folder
if (api_get_setting('show_chat_folder') == 'false' && $val == '/chat_files') {
continue;
}
$escaped_folders[$key] = Database::escape_string($val);
}
$folder_sql = implode("','", $escaped_folders);
$sql = "SELECT * FROM {$doc_table} WHERE c_id = {$course_id} AND filetype='folder' AND path IN ('" . $folder_sql . "')";
$res = Database::query($sql);
$folder_titles = array();
while ($obj = Database::fetch_object($res)) {
$folder_titles[$obj->path] = $obj->title;
}
}
if (empty($group_dir)) {
$parent_select->addOption(get_lang('HomeDirectory'), '/');
if (is_array($folders)) {
foreach ($folders as &$folder) {
//Hide some folders
if ($folder == '/HotPotatoes_files' || $folder == '/certificates' || basename($folder) == 'css') {
continue;
}
//Admin setting for Hide/Show the folders of all users
if (api_get_setting('show_users_folders') == 'false' && (strstr($folder, '/shared_folder') || strstr($folder, 'shared_folder_session_'))) {
continue;
}
/**
* Get the number of glossary terms in the course (or course+session)
* @param int Session ID filter (optional)
* @return integer Count of glossary terms
*
* @author Patrick Cool <*****@*****.**>, Ghent University, Belgium
* @version januari 2009, dokeos 1.8.6
*/
public static function get_number_glossary_terms($session_id = 0)
{
// Database table definition
$t_glossary = Database::get_course_table(TABLE_GLOSSARY);
$course_id = api_get_course_int_id();
$session_id = intval($session_id);
$sql_filter = api_get_session_condition($session_id, true, true);
$sql = "SELECT count(glossary_id) as total\n FROM {$t_glossary} WHERE c_id = {$course_id} {$sql_filter}";
$res = Database::query($sql);
if ($res === false) {
return 0;
}
$obj = Database::fetch_object($res);
return $obj->total;
}
/**
* Saves one answer to the database
* @param int $id The ID of the answer (has to be calculated for this course)
* @param int $question_id The question ID (to which the answer is attached)
* @param string $title The text of the answer
* @param string $comment The feedback for the answer
* @param float|null $score The score you get when picking this answer
* @param int|null $correct Whether this answer is considered *the* correct one (this is the unique answer type)
*/
public function addAnswer($id, $question_id, $title, $comment, $score = 0.0, $correct = 0)
{
$tbl_quiz_answer = Database::get_course_table(TABLE_QUIZ_ANSWER);
$tbl_quiz_question = Database::get_course_table(TABLE_QUIZ_QUESTION);
$course_id = api_get_course_int_id();
$question_id = intval($question_id);
$score = floatval($score);
$correct = intval($correct);
$title = Database::escape_string($title);
$comment = Database::escape_string($comment);
// Get the max position.
$sql = "SELECT max(position) as max_position\n FROM {$tbl_quiz_answer}\n WHERE\n c_id = {$course_id} AND\n question_id = {$question_id}";
$rs_max = Database::query($sql);
$row_max = Database::fetch_object($rs_max);
$position = $row_max->max_position + 1;
// Insert a new answer
$sql = "INSERT INTO {$tbl_quiz_answer} (\n c_id,\n id,\n question_id,\n answer,\n correct,\n comment,\n ponderation,\n position,\n destination\n ) VALUES (\n {$course_id},\n {$id},\n {$question_id},\n '" . $title . "',\n {$correct},\n '" . $comment . "',\n '{$score}', {$position},\n '0@@0@@0@@0'\n )";
Database::query($sql);
if ($correct) {
$sql = "UPDATE {$tbl_quiz_question}\n SET ponderation = (ponderation + {$score})\n WHERE c_id = {$course_id} AND id = " . $question_id;
Database::query($sql);
}
}
/**
* returns the array with the question ID list
*
* @author Olivier Brouckaert
* @return array - question ID list
*/
public function selectQuestionList($from_db = false)
{
if ($from_db && !empty($this->id)) {
$TBL_EXERCISE_QUESTION = Database::get_course_table(TABLE_QUIZ_TEST_QUESTION);
$TBL_QUESTIONS = Database::get_course_table(TABLE_QUIZ_QUESTION);
$sql = "SELECT DISTINCT e.question_order\n FROM {$TBL_EXERCISE_QUESTION} e\n INNER JOIN {$TBL_QUESTIONS} q\n ON (e.question_id = q.id AND e.c_id = " . $this->course_id . " AND q.c_id = " . $this->course_id . ")\n\t\t\t\t\tWHERE e.exercice_id\t= " . intval($this->id);
$result = Database::query($sql);
$count_question_orders = Database::num_rows($result);
$sql = "SELECT DISTINCT e.question_id, e.question_order\n FROM {$TBL_EXERCISE_QUESTION} e\n INNER JOIN {$TBL_QUESTIONS} q\n ON (e.question_id= q.id AND e.c_id = " . $this->course_id . " AND q.c_id = " . $this->course_id . ")\n\t\t\t\t\tWHERE e.exercice_id\t= " . intval($this->id) . "\n\t\t\t\t\tORDER BY question_order";
$result = Database::query($sql);
// fills the array with the question ID for this exercise
// the key of the array is the question position
$temp_question_list = array();
$counter = 1;
$question_list = array();
while ($new_object = Database::fetch_object($result)) {
$question_list[$new_object->question_order] = $new_object->question_id;
$temp_question_list[$counter] = $new_object->question_id;
$counter++;
}
if (!empty($temp_question_list)) {
if (count($temp_question_list) != $count_question_orders) {
$question_list = $temp_question_list;
}
}
return $question_list;
}
return $this->questionList;
}
/**
* Get the title of any resource(quiz, forum, survey, student publication, link...) by the resource ID
* @param integer $resource_id
* @return integer
*/
function get_resource_title_by_resource_id($resource_type, $resource_id)
{
switch ($resource_type) {
case TOOL_LINK:
$tbl_link = Database::get_course_table(TABLE_LINK);
$sql_link = "SELECT title FROM " . $tbl_link . " WHERE id = " . Database::escape_string($resource_id);
$result = Database::query($sql_link, __FILE__, __LINE__);
$row = Database::fetch_array($result);
$title = $row['title'];
break;
case TOOL_STUDENTPUBLICATION:
// Get the Student publication title
$tbl_work = Database::get_course_table(TABLE_STUDENT_PUBLICATION);
$sql = "SELECT url FROM {$tbl_work} WHERE id = '" . Database::escape_string($resource_id) . "'";
$rs = Database::query($sql, __FILE__, __LINE__);
$row = Database::fetch_object($rs);
$title = substr($row->url, 1);
break;
case TOOL_QUIZ:
// Get the Quiz title
$tbl_quiz = Database::get_course_table(TABLE_QUIZ_TEST);
$sql = "SELECT title FROM {$tbl_quiz} WHERE id = '" . Database::escape_string($resource_id) . "'";
$rs = Database::query($sql, __FILE__, __LINE__);
$row = Database::fetch_object($rs);
$title = $row->title;
break;
case TOOL_DOCUMENT:
// Get the document title
$tbl_document = Database::get_course_table(TABLE_DOCUMENT);
$sql = "SELECT title FROM {$tbl_document} WHERE id = '" . Database::escape_string($resource_id) . "'";
$rs = Database::query($sql, __FILE__, __LINE__);
$row = Database::fetch_object($rs);
$title = $row->title;
break;
case TOOL_FORUM:
// Get the Forum title
$tbl_forum = Database::get_course_table(TABLE_FORUM);
$sql = "SELECT forum_title as title FROM {$tbl_forum} WHERE forum_id = '" . Database::escape_string($resource_id) . "'";
$rs = Database::query($sql, __FILE__, __LINE__);
$row = Database::fetch_object($rs);
$title = $row->title;
break;
case 'thread':
// Get the Thread title
$tbl_forum = Database::get_course_table(TABLE_FORUM_THREAD);
$sql = "SELECT thread_title as title FROM {$tbl_forum} WHERE thread_id = '" . Database::escape_string($resource_id) . "'";
$rs = Database::query($sql, __FILE__, __LINE__);
$row = Database::fetch_object($rs);
$title = $row->title;
break;
case TOOL_SURVEY:
// Get the Survey title
$tbl_survey = Database::get_course_table(TABLE_MAIN_SURVEY);
$sql = "SELECT title FROM {$tbl_survey} WHERE survey_id = '" . Database::escape_string($resource_id) . "'";
$rs = Database::query($sql, __FILE__, __LINE__);
$row = Database::fetch_object($rs);
$title = $row->title;
break;
}
return $title;
}
/**
* Gets previous_item_id for the next element of the lp_item table
* @author Isaac flores paz
* @return integer Previous item ID
*/
function select_previous_item_id()
{
$course_id = api_get_course_int_id();
if ($this->debug > 0) {
error_log('New LP - In learnpath::select_previous_item_id()', 0);
}
$table_lp_item = Database::get_course_table(TABLE_LP_ITEM);
// Get the max order of the items
$sql_max_order = "SELECT max(display_order) AS display_order FROM {$table_lp_item} WHERE c_id = {$course_id} AND lp_id = '" . $this->lp_id . "'";
$rs_max_order = Database::query($sql_max_order);
$row_max_order = Database::fetch_object($rs_max_order);
$max_order = $row_max_order->display_order;
// Get the previous item ID
$sql_max = "SELECT id as previous FROM {$table_lp_item} WHERE c_id = {$course_id} AND lp_id = '" . $this->lp_id . "' AND display_order = '" . $max_order . "' ";
$rs_max = Database::query($sql_max, __FILE__, __LINE__);
$row_max = Database::fetch_object($rs_max);
// Return the previous item ID
return $row_max->previous;
}
/**
* Create a question from a set of parameters
* @param int Quiz ID
* @param string Question name
* @param int Maximum result for the question
* @param int Type of question (see constants at beginning of question.class.php)
* @param int Question level/category
*/
public function create_question($quiz_id, $question_name, $max_score = 0, $type = 1, $level = 1)
{
$course_id = api_get_course_int_id();
$tbl_quiz_question = Database::get_course_table(TABLE_QUIZ_QUESTION);
$tbl_quiz_rel_question = Database::get_course_table(TABLE_QUIZ_TEST_QUESTION);
$quiz_id = intval($quiz_id);
$max_score = (double) $max_score;
$type = intval($type);
$level = intval($level);
// Get the max position
$sql = "SELECT max(position) as max_position" . " FROM {$tbl_quiz_question} q INNER JOIN {$tbl_quiz_rel_question} r" . " ON q.iid = r.question_id" . " AND exercice_id = {$quiz_id} AND q.c_id = {$course_id} AND r.c_id = {$course_id}";
$rs_max = Database::query($sql);
$row_max = Database::fetch_object($rs_max);
$max_position = $row_max->max_position + 1;
// Insert the new question
$sql = "INSERT INTO {$tbl_quiz_question} (c_id, question, ponderation, position, type, level)\n VALUES ({$course_id}, '" . Database::escape_string($question_name) . "', '{$max_score}', {$max_position}, {$type}, {$level})";
Database::query($sql);
// Get the question ID
$question_id = Database::insert_id();
// Get the max question_order
$sql = "SELECT max(question_order) as max_order FROM {$tbl_quiz_rel_question}\n WHERE c_id = {$course_id} AND exercice_id = {$quiz_id} ";
$rs_max_order = Database::query($sql);
$row_max_order = Database::fetch_object($rs_max_order);
$max_order = $row_max_order->max_order + 1;
// Attach questions to quiz
$sql = "INSERT INTO {$tbl_quiz_rel_question} (c_id, question_id, exercice_id, question_order)\n VALUES ({$course_id}, {$question_id}, {$quiz_id}, {$max_order})";
Database::query($sql);
return $question_id;
}
/**
* Builds the form that enables the user to
* select a directory to browse/upload in
*
* @param array An array containing the folders we want to be able to select
* @param string The current folder (path inside of the "document" directory, including the prefix "/")
* @param string Group directory, if empty, prevents documents to be uploaded (because group documents cannot be uploaded in root)
* @param boolean Whether to change the renderer (this will add a template <span> to the QuickForm object displaying the form)
* @todo this funcionality is really bad : jmontoya
* @return string html form
*/
public static function build_directory_selector($folders, $document_id, $group_dir = '', $change_renderer = false)
{
$doc_table = Database::get_course_table(TABLE_DOCUMENT);
$course_id = api_get_course_int_id();
$folder_titles = array();
if (is_array($folders)) {
$escaped_folders = array();
foreach ($folders as $key => &$val) {
$escaped_folders[$key] = Database::escape_string($val);
}
$folder_sql = implode("','", $escaped_folders);
$sql = "SELECT * FROM {$doc_table}\n WHERE filetype = 'folder' AND c_id = {$course_id} AND path IN ('" . $folder_sql . "')";
$res = Database::query($sql);
$folder_titles = array();
while ($obj = Database::fetch_object($res)) {
$folder_titles[$obj->path] = $obj->title;
}
}
$form = new FormValidator('selector', 'GET', api_get_self() . '?' . api_get_cidreq());
$form->addElement('hidden', 'cidReq', api_get_course_id());
$parent_select = $form->addSelect('id', get_lang('CurrentDirectory'), '', array('onchange' => 'javascript: document.selector.submit();'));
if ($change_renderer) {
$renderer = $form->defaultRenderer();
$renderer->setElementTemplate('<span>{label} : {element}</span> ', 'curdirpath');
}
// Group documents cannot be uploaded in the root
if (empty($group_dir)) {
$parent_select->addOption(get_lang('Documents'), '/');
if (is_array($folders)) {
foreach ($folders as $folder_id => &$folder) {
$selected = $document_id == $folder_id ? ' selected="selected"' : '';
$path_parts = explode('/', $folder);
$folder_titles[$folder] = cut($folder_titles[$folder], 80);
$counter = count($path_parts) - 2;
if ($counter > 0) {
$label = str_repeat(' ', $counter) . ' — ' . $folder_titles[$folder];
} else {
$label = ' — ' . $folder_titles[$folder];
}
$parent_select->addOption($label, $folder_id);
if ($selected != '') {
$parent_select->setSelected($folder_id);
}
}
}
} else {
if (!empty($folders)) {
foreach ($folders as $folder_id => &$folder) {
$selected = $document_id == $folder_id ? ' selected="selected"' : '';
$label = $folder_titles[$folder];
if ($folder == $group_dir) {
$label = get_lang('Documents');
} else {
$path_parts = explode('/', str_replace($group_dir, '', $folder));
$label = cut($label, 80);
$label = str_repeat(' ', count($path_parts) - 2) . ' — ' . $label;
}
$parent_select->addOption($label, $folder_id);
if ($selected != '') {
$parent_select->setSelected($folder_id);
}
}
}
}
$html = $form->toHtml();
return $html;
}
/**
* Print the number of users that didn't login for a certain period of time
*/
static function print_users_not_logged_in_stats()
{
$total_logins = array();
$table = Database::get_main_table(TABLE_STATISTIC_TRACK_E_LOGIN);
$access_url_rel_user_table = Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER);
$current_url_id = api_get_current_access_url_id();
$total = self::count_users();
if (api_is_multiple_url_enabled()) {
$table_url = ", {$access_url_rel_user_table}";
$where_url = " AND login_user_id=user_id AND access_url_id='" . $current_url_id . "'";
} else {
$table_url = '';
$where_url = '';
}
$sql[get_lang('Thisday')] = "SELECT count(distinct(login_user_id)) AS number " . " FROM {$table} {$table_url} " . " WHERE DATE_ADD(login_date, INTERVAL 1 DAY) >= NOW() {$where_url}";
$sql[get_lang('Last7days')] = "SELECT count(distinct(login_user_id)) AS number " . " FROM {$table} {$table_url} " . " WHERE DATE_ADD(login_date, INTERVAL 7 DAY) >= NOW() {$where_url}";
$sql[get_lang('Last31days')] = "SELECT count(distinct(login_user_id)) AS number " . " FROM {$table} {$table_url} " . " WHERE DATE_ADD(login_date, INTERVAL 31 DAY) >= NOW() {$where_url}";
$sql[sprintf(get_lang('LastXMonths'), 6)] = "SELECT count(distinct(login_user_id)) AS number " . " FROM {$table} {$table_url} " . " WHERE DATE_ADD(login_date, INTERVAL 6 MONTH) >= NOW() {$where_url}";
$sql[get_lang('NeverConnected')] = "SELECT count(distinct(login_user_id)) AS number " . " FROM {$table} {$table_url} WHERE 1=1 {$where_url}";
foreach ($sql as $index => $query) {
$res = Database::query($query);
$obj = Database::fetch_object($res);
$r = $total - $obj->number;
$total_logins[$index] = $r < 0 ? 0 : $r;
}
Statistics::print_stats(get_lang('StatsUsersDidNotLoginInLastPeriods'), $total_logins, false);
}
/**
* Get users whose name matches $firstname and $lastname
* @param string $firstname Firstname to search
* @param string $lastname Lastname to search
* @return array The user list
*/
public static function getUserByName($firstname, $lastname)
{
$firstname = Database::escape_string($firstname);
$lastname = Database::escape_string($lastname);
$userTable = Database::get_main_table(TABLE_MAIN_USER);
$sql = <<<SQL
SELECT id, username, lastname, firstname
FROM {$userTable}
WHERE firstname LIKE '{$firstname}%' AND
lastname LIKE '{$lastname}%'
SQL;
$result = Database::query($sql);
$users = [];
while ($resultData = Database::fetch_object($result)) {
$users[] = $resultData;
}
return $users;
}
