/**
* Gets the progress of learning paths in the given session
* @param int session id
* @param array options order and limit keys
* @return array table with user name, lp name, progress
*/
public static function get_session_lp_progress($sessionId = 0, $courseId = 0, $date_from, $date_to, $options)
{
//escaping vars
$sessionId = $sessionId == 'T' ? 'T' : intval($sessionId);
$courseId = intval($courseId);
$date_from = Database::escape_string($date_from);
$date_to = Database::escape_string($date_to);
//tables
$session_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER);
$user = Database::get_main_table(TABLE_MAIN_USER);
$tbl_course_lp_view = Database::get_course_table(TABLE_LP_VIEW);
$course = api_get_course_info_by_id($courseId);
//getting all the students of the course
//we are not using this because it only returns user ids
/* if (empty($sessionId)
{
// Registered students in a course outside session.
$users = CourseManager :: get_student_list_from_course_code($course_code);
} else {
// Registered students in session.
$users = CourseManager :: get_student_list_from_course_code($course_code, true, $sessionId);
} */
$sessionCond = 'and session_id = %s';
if ($sessionId == 'T') {
$sessionCond = "";
}
$where = " WHERE c_id = '%s' AND s.status <> 2 {$sessionCond}";
$limit = null;
if (!empty($options['limit'])) {
$limit = " LIMIT " . $options['limit'];
}
if (!empty($options['where'])) {
$where .= ' ' . $options['where'];
}
$order = null;
if (!empty($options['order'])) {
$order = " ORDER BY " . $options['order'];
}
$sql = "SELECT u.user_id, u.lastname, u.firstname, u.username, u.email, s.c_id\n FROM {$session_course_user} s\n INNER JOIN {$user} u ON u.user_id = s.user_id\n {$where}\n {$order}\n {$limit}";
$sql_query = sprintf($sql, Database::escape_string($course['real_id']), $sessionId);
$rs = Database::query($sql_query);
while ($user = Database::fetch_array($rs)) {
$users[$user['user_id']] = $user;
}
//Get lessons
$lessons = LearnpathList::get_course_lessons($course['code'], $sessionId);
$table = array();
foreach ($users as $user) {
$data = array('lastname' => $user[1], 'firstname' => $user[2], 'username' => $user[3]);
$sessionCond = 'AND v.session_id = %d';
if ($sessionId == 'T') {
$sessionCond = "";
}
//Get lessons progress by user
$sql = "SELECT v.lp_id as id, v.progress\n FROM {$tbl_course_lp_view} v\n WHERE v.c_id = %d\n AND v.user_id = %d\n {$sessionCond}";
$sql_query = sprintf($sql, intval($courseId), intval($user['user_id']), $sessionId);
$result = Database::query($sql_query);
$user_lessons = array();
while ($row = Database::fetch_array($result)) {
$user_lessons[$row['id']] = $row;
}
//Match course lessons with user progress
$progress = 0;
$count = 0;
foreach ($lessons as $lesson) {
$data[$lesson['id']] = !empty($user_lessons[$lesson['id']]['progress']) ? $user_lessons[$lesson['id']]['progress'] : 0;
$progress += $data[$lesson['id']];
$data[$lesson['id']] = $data[$lesson['id']] . '%';
$count++;
}
if ($count == 0) {
$data['total'] = 0;
} else {
$data['total'] = round($progress / $count, 2) . '%';
}
$table[] = $data;
}
return $table;
}
$columns = array('session', 'exercise_id', 'quiz_title', 'username', 'lastname', 'firstname', 'time', 'question_id', 'question', 'description', 'answer', 'correct');
$result = Tracking::get_exercise_progress($sessionId, $courseId, $exerciseId, $date_from, $date_to, array('where' => $whereCondition, 'order' => "{$sidx} {$sord}", 'limit' => "{$start} , {$limit}"));
break;
case 'get_session_lp_progress':
$sessionId = 0;
if (!empty($_GET['session_id']) && !empty($_GET['course_id'])) {
$sessionId = intval($_GET['session_id']);
$courseId = intval($_GET['course_id']);
$course = api_get_course_info_by_id($courseId);
}
/**
* Add lessons of course
*
*/
$columns = array('username', 'firstname', 'lastname');
$lessons = LearnpathList::get_course_lessons($course['code'], $sessionId);
foreach ($lessons as $lesson_id => $lesson) {
$columns[] = $lesson_id;
}
$columns[] = 'total';
$result = SessionManager::get_session_lp_progress($sessionId, $courseId, $date_from, $date_to, array('where' => $whereCondition, 'order' => "{$sidx} {$sord}", 'limit' => "{$start} , {$limit}"));
break;
case 'get_survey_overview':
$sessionId = 0;
if (!empty($_GET['session_id']) && !empty($_GET['course_id']) && !empty($_GET['survey_id'])) {
$sessionId = intval($_GET['session_id']);
$courseId = intval($_GET['course_id']);
$surveyId = intval($_GET['survey_id']);
$date_from = $_GET['date_from'];
$date_to = $_GET['date_to'];
//$course = api_get_course_info_by_id($courseId);
/**
* Display a sortable table that contains an overview off all the progress of the user in a session
* @author César Perales <*****@*****.**>, Beeznest Team
*/
public static function display_tracking_lp_progress_overview($sessionId = '', $courseId = '', $date_from, $date_to)
{
$course = api_get_course_info_by_id($courseId);
/**
* Column name
* The order is important you need to check the $column variable in the model.ajax.php file
*/
$columns = array(get_lang('Username'), get_lang('FirstName'), get_lang('LastName'));
//add lessons of course
$lessons = LearnpathList::get_course_lessons($course['code'], $sessionId);
//create columns array
foreach ($lessons as $lesson_id => $lesson) {
$columns[] = $lesson['name'];
}
$columns[] = get_lang('Total');
/**
* Column config
*/
$column_model = array(array('name' => 'username', 'index' => 'username', 'align' => 'left', 'search' => 'true', 'wrap_cell' => "true"), array('name' => 'firstname', 'index' => 'firstname', 'align' => 'left', 'search' => 'true'), array('name' => 'lastname', 'index' => 'lastname', 'align' => 'left', 'search' => 'true'));
// Get dinamic column names
foreach ($lessons as $lesson_id => $lesson) {
$column_model[] = array('name' => $lesson['id'], 'index' => $lesson['id'], 'align' => 'left', 'search' => 'true');
}
$column_model[] = array('name' => 'total', 'index' => 'total', 'align' => 'left', 'search' => 'true');
$action_links = '';
// jqgrid will use this URL to do the selects
$url = api_get_path(WEB_AJAX_PATH) . 'model.ajax.php?a=get_session_lp_progress&session_id=' . $sessionId . '&course_id=' . $courseId . '&date_to=' . $date_to . '&date_from=' . $date_from;
//Table Id
$tableId = 'lpProgress';
//Autowidth
$extra_params['autowidth'] = 'true';
//height auto
$extra_params['height'] = 'auto';
$table = Display::grid_js($tableId, $url, $columns, $column_model, $extra_params, array(), $action_links, true);
$return = '<script>$(function() {' . $table . 'jQuery("#' . $tableId . '").jqGrid("navGrid","#' . $tableId . '_pager",{view:false, edit:false, add:false, del:false, search:false, excel:true});
jQuery("#' . $tableId . '").jqGrid("navButtonAdd","#' . $tableId . '_pager",{
caption:"",
title:"' . get_lang('ExportExcel') . '",
onClickButton : function () {
jQuery("#' . $tableId . '").jqGrid("excelExport",{"url":"' . $url . '&export_format=xls"});
}
});
});</script>';
$return .= Display::grid_html($tableId);
return $return;
}
