* @param int field id
* @param array list of user ids
* @return array
* @since Nov 2009
* @version 1.8.6.2
*/
function get_addtional_profile_information_of_field_by_user($field_id, $users) {
// Database table definition
$table_user = Database::get_main_table(TABLE_MAIN_USER);
$table_user_field_values = Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES);
$result_extra_field = UserManager::get_extra_field_information($field_id);
if (!empty($users)) {
if ($result_extra_field['field_type'] == USER_FIELD_TYPE_TAG ) {
foreach($users as $user_id) {
$user_result = UserManager::get_user_tags($user_id, $field_id);
$tag_list = array();
foreach($user_result as $item) {
$tag_list[] = $item['tag'];
}
$return[$user_id][] = implode(', ',$tag_list);
}
} else {
$new_user_array = array();
foreach($users as $user_id) {
$new_user_array[]= "'".$user_id."'";
}
$users = implode(',',$new_user_array);
//selecting only the necessary information NOT ALL the user list
$sql = "SELECT user.user_id, field.field_value FROM $table_user user INNER JOIN $table_user_field_values field
ON (user.user_id = field.user_id)
WHERE field.field_id=".intval($field_id)." AND user.user_id IN ($users)";
$result = api_sql_query($sql);
while($row = Database::fetch_array($result)) {
// get option value for field type double select by id
if (!empty($row['field_value'])) {
if ($result_extra_field['field_type'] == USER_FIELD_TYPE_DOUBLE_SELECT) {
$id_double_select = explode(';',$row['field_value']);
if (is_array($id_double_select)) {
$value1 = $result_extra_field['options'][$id_double_select[0]]['option_value'];
$value2 = $result_extra_field['options'][$id_double_select[1]]['option_value'];
$row['field_value'] = ($value1.';'.$value2);
}
}
}
// get other value from extra field
$return[$row['user_id']][] = $row['field_value'];
}
}
}
return $return;
}
/**
* count the number of students in this course (used for SortableTable)
* Deprecated
*/
function count_student_in_course() {
global $nbStudents;
return $nbStudents;
}
function sort_users($a, $b) {
return strcmp(trim(api_strtolower($a[$_SESSION['tracking_column']])), trim(api_strtolower($b[$_SESSION['tracking_column']])));
}
function sort_users_desc($a, $b) {
return strcmp( trim(api_strtolower($b[$_SESSION['tracking_column']])), trim(api_strtolower($a[$_SESSION['tracking_column']])));
}
/**
* Get number of users for sortable with pagination
* @return int
*/
function get_number_of_users() {
global $user_ids;
return count($user_ids);
}
/**
* Get data for users list in sortable with pagination
* @return array
*/
function get_user_data($from, $number_of_items, $column, $direction) {
global $user_ids, $course_code, $additional_user_profile_info, $export_csv, $is_western_name_order, $csv_content;
$course_code = Database::escape_string($course_code);
$course_info = CourseManager :: get_course_information($course_code);
$tbl_track_cours_access = Database :: get_statistic_table(TABLE_STATISTIC_TRACK_E_COURSE_ACCESS);
$tbl_user = Database :: get_main_table(TABLE_MAIN_USER);
$tbl_item_property = Database :: get_course_table(TABLE_ITEM_PROPERTY, $course_info['db_name']);
$tbl_forum_post = Database :: get_course_table(TABLE_FORUM_POST, $course_info['db_name']);
$tbl_course_lp_view = Database :: get_course_table(TABLE_LP_VIEW, $course_info['db_name']);
$tbl_course_lp = Database :: get_course_table(TABLE_LP_MAIN, $course_info['db_name']);
// get all users data from a course for sortable with limit
$condition_user = "";
if (is_array($user_ids)) {
$condition_user = " WHERE user.user_id IN (".implode(',',$user_ids).") ";
} else {
$condition_user = " WHERE user.user_id = '$user_ids' ";
}
$sql = "SELECT user.user_id as col0,
user.official_code as col1,
user.lastname as col2,
user.firstname as col3
FROM $tbl_user as user
$condition_user ";
if (!in_array($direction, array('ASC','DESC'))) {
$direction = 'ASC';
}
$column = intval($column);
$from = intval($from);
$number_of_items = intval($number_of_items);
$sql .= " ORDER BY col$column $direction ";
$sql .= " LIMIT $from,$number_of_items";
$res = Database::query($sql);
$users = array ();
$t = time();
$row = array();
while ($user = Database::fetch_row($res)) {
$row[0] = $user[1];
if ($is_western_name_order) {
$row[1] = $user[3];
$row[2] = $user[2];
} else {
$row[1] = $user[2];
$row[2] = $user[3];
}
$row[3] = api_time_to_hms(Tracking::get_time_spent_on_the_course($user[0], $course_code));
$avg_student_score = Tracking::get_average_test_scorm_and_lp($user[0], $course_code);
$avg_student_progress = Tracking::get_avg_student_progress($user[0], $course_code);
if (empty($avg_student_score)) {$avg_student_score=0;}
if (empty($avg_student_progress)) {$avg_student_progress=0;}
$row[4] = $avg_student_progress.'%';
$row[5] = $avg_student_score.'%';
$row[6] = Tracking::count_student_assignments($user[0], $course_code);$user[4];
$row[7] = Tracking::count_student_messages($user[0], $course_code);//$user[5];
$row[8] = Tracking::get_first_connection_date_on_the_course($user[0], $course_code);
$row[9] = Tracking::get_last_connection_date_on_the_course($user[0], $course_code);
// we need to display an additional profile field
if (isset($_GET['additional_profile_field']) AND is_numeric($_GET['additional_profile_field'])) {
if (is_array($additional_user_profile_info[$user[0]])) {
$row[10]=implode(', ', $additional_user_profile_info[$user[0]]);
} else {
$row[10]=' ';
}
}
$row[11] = '
';
if ($export_csv) {
$row[8] = strip_tags($row[8]);
$row[9] = strip_tags($row[9]);
unset($row[10]);
unset($row[11]);
$csv_content[] = $row;
}
// store columns in array $users
$users[] = array($row[0],$row[1],$row[2],$row[3],$row[4],$row[5],$row[6],$row[7],$row[8],$row[9],$row[10],$row[11]);
}
return $users;
}
}
class TrackingUserLog {
/**
* Displays the number of logins every month for a specific user in a specific course.
*/
function display_login_tracking_info($view, $user_id, $course_id)
{
$MonthsLong = $GLOBALS['MonthsLong'];
$track_access_table = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ACCESS);
$tempView = $view;
if(substr($view,0,1) == '1') {
$new_view = substr_replace($view,'0',0,1);
echo "
- " .
"".get_lang('LoginsAndAccessTools')." [".get_lang('Close')."] [".get_lang('ExportAsCSV')."]
|
";
echo "".get_lang('LoginsDetails')." ";
$sql = "SELECT UNIX_TIMESTAMP(access_date), count(access_date)
FROM $track_access_table
WHERE access_user_id = '".Database::escape_string($user_id)."'
AND access_cours_code = '".Database::escape_string($course_id)."'
GROUP BY YEAR(access_date),MONTH(access_date)
ORDER BY YEAR(access_date),MONTH(access_date) ASC";
echo " |
";
//$results = getManyResults2Col($sql);
$results = getManyResults3Col($sql);
echo "";
echo " |
";
} else {
$new_view = substr_replace($view,'1',0,1);
echo "
+ ".get_lang('LoginsAndAccessTools')."
|
";
}
}
/**
* Displays the exercise results for a specific user in a specific course.
* @todo remove globals
*/
function display_exercise_tracking_info($view, $user_id, $course_id)
{
global $TABLECOURSE_EXERCICES, $TABLETRACK_EXERCICES;
if(substr($view,1,1) == '1')
{
$new_view = substr_replace($view,'0',1,1);
echo "
- ".get_lang('ExercicesResults')." [".get_lang('Close')."] [".get_lang('ExportAsCSV')."]
|
";
echo "".get_lang('ExercicesDetails')." ";
$sql = "SELECT ce.title, te.exe_result , te.exe_weighting, UNIX_TIMESTAMP(te.exe_date)
FROM $TABLECOURSE_EXERCICES AS ce , $TABLETRACK_EXERCICES AS te
WHERE te.exe_cours_id = '".Database::escape_string($course_id)."'
AND te.exe_user_id = '".Database::escape_string($user_id)."'
AND te.exe_exo_id = ce.id
ORDER BY ce.title ASC, te.exe_date ASC";
$hpsql = "SELECT te.exe_name, te.exe_result , te.exe_weighting, UNIX_TIMESTAMP(te.exe_date)
FROM $TBL_TRACK_HOTPOTATOES AS te
WHERE te.exe_user_id = '".Database::escape_string($user_id)."' AND te.exe_cours_id = '".Database::escape_string($course_id)."'
ORDER BY te.exe_cours_id ASC, te.exe_date ASC";
$hpresults = getManyResultsXCol($hpsql, 4);
$NoTestRes = 0;
$NoHPTestRes = 0;
echo " |
\n\n";
$results = getManyResultsXCol($sql, 4);
echo "\n";
echo "
".get_lang('ExercicesTitleExerciceColumn')."
|
".get_lang('Date')."
|
".get_lang('ExercicesTitleScoreColumn')."
|
";
if (is_array($results)) {
for($i = 0; $i < sizeof($results); $i++) {
$display_date = format_locale_date(get_lang('dateTimeFormatLong'), $results[$i][3]);
echo "\n";
echo "".$results[$i][0]." | \n";
echo "".$display_date." | \n";
echo "".$results[$i][1]." / ".$results[$i][2]." | \n";
echo " \n";
}
} else {
// istvan begin
$NoTestRes = 1;
}
// The Result of Tests
if(is_array($hpresults)) {
for($i = 0; $i < sizeof($hpresults); $i++) {
$title = GetQuizName($hpresults[$i][0],'');
if ($title == '')
$title = basename($hpresults[$i][0]);
$display_date = format_locale_date(get_lang('dateTimeFormatLong'), $hpresults[$i][3]);
?>
|
|
/ |
\n";
echo "".get_lang('NoResult')." | \n";
echo "\n";
}
echo " ";
echo " | \n
\n";
} else {
$new_view = substr_replace($view,'1',1,1);
echo "
+ ".get_lang('ExercicesResults')."
|
";
}
}
/**
* Displays the student publications for a specific user in a specific course.
* @todo remove globals
*/
function display_student_publications_tracking_info($view, $user_id, $course_id)
{
global $TABLETRACK_UPLOADS, $TABLECOURSE_WORK, $dateTimeFormatLong, $_course;
if(substr($view,2,1) == '1') {
$new_view = substr_replace($view,'0',2,1);
echo "
- ".get_lang('WorkUploads')." [".get_lang('Close')."] [".get_lang('ExportAsCSV')."]
|
";
echo "".get_lang('WorksDetails')." ";
$sql = "SELECT u.upload_date, w.title, w.author,w.url
FROM $TABLETRACK_UPLOADS u , $TABLECOURSE_WORK w
WHERE u.upload_work_id = w.id
AND u.upload_user_id = '".Database::escape_string($user_id)."'
AND u.upload_cours_id = '".Database::escape_string($course_id)."'
ORDER BY u.upload_date DESC";
echo " |
";
$results = getManyResultsXCol($sql,4);
echo "";
echo "
".get_lang('WorkTitle')."
|
".get_lang('WorkAuthors')."
|
".get_lang('Date')."
|
";
if (is_array($results)) {
for($j = 0 ; $j < count($results) ; $j++) {
$pathToFile = api_get_path(WEB_COURSE_PATH).$_course['path']."/".$results[$j][3];
$timestamp = strtotime($results[$j][0]);
$beautifulDate = format_locale_date($dateTimeFormatLong,$timestamp);
echo "";
echo ""
."".$results[$j][1].""
." | ";
echo "".$results[$j][2]." | ";
echo "".$beautifulDate." | ";
echo" ";
}
} else {
echo "";
echo "".get_lang('NoResult')." | ";
echo" ";
}
echo " ";
echo " |
";
} else {
$new_view = substr_replace($view,'1',2,1);
echo "
+ ".get_lang('WorkUploads')."
|
";
}
}
/**
* Displays the links followed for a specific user in a specific course.
* @todo remove globals
*/
function display_links_tracking_info($view, $user_id, $course_id)
{
global $TABLETRACK_LINKS, $TABLECOURSE_LINKS;
if(substr($view,3,1) == '1') {
$new_view = substr_replace($view,'0',3,1);
echo "
- ".get_lang('LinksAccess')." [".get_lang('Close')."] [".get_lang('ExportAsCSV')."]
|
";
echo "".get_lang('LinksDetails')." ";
$sql = "SELECT cl.title, cl.url
FROM $TABLETRACK_LINKS AS sl, $TABLECOURSE_LINKS AS cl
WHERE sl.links_link_id = cl.id
AND sl.links_cours_id = '".Database::escape_string($course_id)."'
AND sl.links_user_id = '".Database::escape_string($user_id)."'
GROUP BY cl.title, cl.url";
echo " |
";
$results = getManyResults2Col($sql);
echo "";
echo "
".get_lang('LinksTitleLinkColumn')."
|
";
if (is_array($results)) {
for($j = 0 ; $j < count($results) ; $j++) {
echo "";
echo "".$results[$j][0]." | ";
echo" ";
}
} else {
echo "";
echo "".get_lang('NoResult')." | ";
echo" ";
}
echo " ";
echo " |
";
} else {
$new_view = substr_replace($view,'1',3,1);
echo "
+ ".get_lang('LinksAccess')."
|
";
}
}
/**
* Displays the documents downloaded for a specific user in a specific course.
*/
function display_document_tracking_info($view, $user_id, $course_id)
{
$downloads_table = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_DOWNLOADS);
if(substr($view,4,1) == '1')
{
$new_view = substr_replace($view,'0',4,1);
echo "
- ".get_lang('DocumentsAccess')." [".get_lang('Close')."] [".get_lang('ExportAsCSV')."]
|
";
echo "".get_lang('DocumentsDetails')." ";
$sql = "SELECT down_doc_path
FROM $downloads_table
WHERE down_cours_id = '".Database::escape_string($course_id)."'
AND down_user_id = '".Database::escape_string($user_id)."'
GROUP BY down_doc_path";
echo " |
";
$results = getManyResults1Col($sql);
echo "";
echo "
".get_lang('DocumentsTitleDocumentColumn')."
|
";
if (is_array($results)) {
for($j = 0 ; $j < count($results) ; $j++) {
echo "";
echo "".$results[$j]." | ";
echo" ";
}
} else {
echo "";
echo "".get_lang('NoResult')." | ";
echo" ";
}
echo " ";
echo " |
";
} else {
$new_view = substr_replace($view,'1',4,1);
echo "
+ ".get_lang('DocumentsAccess')."
|
";
}
}
}
class TrackingUserLogCSV {
/**
* Displays the number of logins every month for a specific user in a specific course.
*/
function display_login_tracking_info($view, $user_id, $course_id)
{
$MonthsLong = $GLOBALS['MonthsLong'];
$track_access_table = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_ACCESS);
$tempView = $view;
if(substr($view,0,1) == '1')
{
$new_view = substr_replace($view,'0',0,1);
$title[1]= get_lang('LoginsAndAccessTools').get_lang('LoginsDetails');
$sql = "SELECT UNIX_TIMESTAMP(`access_date`), count(`access_date`)
FROM $track_access_table
WHERE `access_user_id` = '$user_id'
AND `access_cours_code` = '".$course_id."'
GROUP BY YEAR(`access_date`),MONTH(`access_date`)
ORDER BY YEAR(`access_date`),MONTH(`access_date`) ASC";
//$results = getManyResults2Col($sql);
$results = getManyResults3Col($sql);
$title_line= get_lang('LoginsTitleMonthColumn').';'.get_lang('LoginsTitleCountColumn')."\n";
$line='';
$total = 0;
if (is_array($results))
{
for($j = 0 ; $j < count($results) ; $j++)
{
$line .= $results[$j][0].';'.$results[$j][1]."\n";
$total = $total + $results[$j][1];
}
$line .= get_lang('Total').";".$total."\n";
}
else
{
$line= get_lang('NoResult')."";
}
}
else
{
$new_view = substr_replace($view,'1',0,1);
}
return array($title_line, $line);
}
/**
* Displays the exercise results for a specific user in a specific course.
* @todo remove globals
*/
function display_exercise_tracking_info($view, $user_id, $course_id)
{
global $TABLECOURSE_EXERCICES, $TABLETRACK_EXERCICES, $TABLETRACK_HOTPOTATOES;
if(substr($view,1,1) == '1')
{
$new_view = substr_replace($view,'0',1,1);
$title[1]= get_lang('ExercicesDetails');
$line='';
$sql = "SELECT `ce`.`title`, `te`.`exe_result` , `te`.`exe_weighting`, UNIX_TIMESTAMP(`te`.`exe_date`)
FROM $TABLECOURSE_EXERCICES AS ce , `$TABLETRACK_EXERCICES` AS `te`
WHERE `te`.`exe_cours_id` = '$course_id'
AND `te`.`exe_user_id` = '$user_id'
AND `te`.`exe_exo_id` = `ce`.`id`
ORDER BY `ce`.`title` ASC, `te`.`exe_date` ASC";
$hpsql = "SELECT `te`.`exe_name`, `te`.`exe_result` , `te`.`exe_weighting`, UNIX_TIMESTAMP(`te`.`exe_date`)
FROM `$TABLETRACK_HOTPOTATOES` AS te
WHERE `te`.`exe_user_id` = '$user_id' AND `te`.`exe_cours_id` = '$course_id'
ORDER BY `te`.`exe_cours_id` ASC, `te`.`exe_date` ASC";
$hpresults = getManyResultsXCol($hpsql, 4);
$NoTestRes = 0;
$NoHPTestRes = 0;
$results = getManyResultsXCol($sql, 4);
$title_line=get_lang('ExercicesTitleExerciceColumn').";".get_lang('Date').';'.get_lang('ExercicesTitleScoreColumn')."\n";
if (is_array($results))
{
for($i = 0; $i < sizeof($results); $i++)
{
$display_date = format_locale_date(get_lang('dateTimeFormatLong'), $results[$i][3]);
$line .= $results[$i][0].";".$display_date.";".$results[$i][1]." / ".$results[$i][2]."\n";
}
}
else // istvan begin
{
$NoTestRes = 1;
}
// The Result of Tests
if(is_array($hpresults))
{
for($i = 0; $i < sizeof($hpresults); $i++)
{
$title = GetQuizName($hpresults[$i][0],'');
if ($title == '')
$title = basename($hpresults[$i][0]);
$display_date = format_locale_date(get_lang('dateTimeFormatLong'), $hpresults[$i][3]);
$line .= $title.';'.$display_date.';'.$hpresults[$i][1].'/'.$hpresults[$i][2]."\n";
}
}
else
{
$NoHPTestRes = 1;
}
if ($NoTestRes == 1 && $NoHPTestRes == 1)
{
$line=get_lang('NoResult');
}
}
else
{
$new_view = substr_replace($view,'1',1,1);
}
return array($title_line, $line);
}
/**
* Displays the student publications for a specific user in a specific course.
* @todo remove globals
*/
function display_student_publications_tracking_info($view, $user_id, $course_id)
{
global $TABLETRACK_UPLOADS, $TABLECOURSE_WORK, $dateTimeFormatLong;
if(substr($view,2,1) == '1')
{
$new_view = substr_replace($view,'0',2,1);
$sql = "SELECT `u`.`upload_date`, `w`.`title`, `w`.`author`,`w`.`url`
FROM `$TABLETRACK_UPLOADS` `u` , $TABLECOURSE_WORK `w`
WHERE `u`.`upload_work_id` = `w`.`id`
AND `u`.`upload_user_id` = '$user_id'
AND `u`.`upload_cours_id` = '$course_id'
ORDER BY `u`.`upload_date` DESC";
$results = getManyResultsXCol($sql,4);
$title[1]=get_lang('WorksDetails');
$line='';
$title_line=get_lang('WorkTitle').";".get_lang('WorkAuthors').";".get_lang('Date')."\n";
if (is_array($results))
{
for($j = 0 ; $j < count($results) ; $j++)
{
$pathToFile = api_get_path(WEB_COURSE_PATH).$_course['path']."/".$results[$j][3];
$timestamp = strtotime($results[$j][0]);
$beautifulDate = format_locale_date($dateTimeFormatLong,$timestamp);
$line .= $results[$j][1].";".$results[$j][2].";".$beautifulDate."\n";
}
}
else
{
$line= get_lang('NoResult');
}
}
else
{
$new_view = substr_replace($view,'1',2,1);
}
return array($title_line, $line);
}
/**
* Displays the links followed for a specific user in a specific course.
* @todo remove globals
*/
function display_links_tracking_info($view, $user_id, $course_id)
{
global $TABLETRACK_LINKS, $TABLECOURSE_LINKS;
if(substr($view,3,1) == '1')
{
$new_view = substr_replace($view,'0',3,1);
$title[1]=get_lang('LinksDetails');
$sql = "SELECT `cl`.`title`, `cl`.`url`
FROM `$TABLETRACK_LINKS` AS sl, $TABLECOURSE_LINKS AS cl
WHERE `sl`.`links_link_id` = `cl`.`id`
AND `sl`.`links_cours_id` = '$course_id'
AND `sl`.`links_user_id` = '$user_id'
GROUP BY `cl`.`title`, `cl`.`url`";
$results = getManyResults2Col($sql);
$title_line= get_lang('LinksTitleLinkColumn')."\n";
if (is_array($results))
{
for($j = 0 ; $j < count($results) ; $j++)
{
$line .= $results[$j][0]."\n";
}
}
else
{
$line=get_lang('NoResult');
}
}
else
{
$new_view = substr_replace($view,'1',3,1);
}
return array($title_line, $line);
}
/**
* Displays the documents downloaded for a specific user in a specific course.
*/
function display_document_tracking_info($view, $user_id, $course_id)
{
$downloads_table = Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_DOWNLOADS);
if(substr($view,4,1) == '1')
{
$new_view = substr_replace($view,'0',4,1);
$title[1]= get_lang('DocumentsDetails');
$sql = "SELECT `down_doc_path`
FROM $downloads_table
WHERE `down_cours_id` = '$course_id'
AND `down_user_id` = '$user_id'
GROUP BY `down_doc_path`";
$results = getManyResults1Col($sql);
$title_line = get_lang('DocumentsTitleDocumentColumn')."\n";
if (is_array($results))
{
for($j = 0 ; $j < count($results) ; $j++)
{
$line .= $results[$j]."\n";
}
}
else
{
$line=get_lang('NoResult');
}
}
else
{
$new_view = substr_replace($view,'1',4,1);
}
return array($title_line, $line);
}
}
?>