/**
* Get the users to display on the current page.
*/
function get_user_data($from, $number_of_items, $column, $direction)
{
global $origin;
global $course_info;
global $is_western_name_order;
global $session_id;
$a_users = array();
// limit
$limit = 'LIMIT ' . intval($from) . ',' . intval($number_of_items);
if (!in_array($direction, array('ASC', 'DESC'))) {
$direction = 'ASC';
}
switch ($column) {
case 2:
//official code
$order_by = 'ORDER BY user.official_code ' . $direction;
break;
case 3:
if ($is_western_name_order) {
$order_by = 'ORDER BY user.firstname ' . $direction . ', user.lastname ' . $direction;
} else {
$order_by = 'ORDER BY user.lastname ' . $direction . ', user.firstname ' . $direction;
}
break;
case 4:
if ($is_western_name_order) {
$order_by = 'ORDER BY user.lastname ' . $direction . ', user.firstname ' . $direction;
} else {
$order_by = 'ORDER BY user.firstname ' . $direction . ', user.lastname ' . $direction;
}
break;
case 5:
//username
$order_by = 'ORDER BY user.username ' . $direction;
break;
default:
if ($is_western_name_order) {
$order_by = 'ORDER BY user.lastname ' . $direction . ', user.firstname ' . $direction;
} else {
$order_by = 'ORDER BY user.firstname ' . $direction . ', user.lastname ' . $direction;
}
break;
}
$session_id = api_get_session_id();
$course_code = api_get_course_id();
$keyword = isset($_REQUEST['keyword']) ? $_REQUEST['keyword'] : null;
$a_course_users = CourseManager::get_user_list_from_course_code($course_code, $session_id, $limit, $order_by, null, $keyword);
foreach ($a_course_users as $user_id => $o_course_user) {
$groups_name = GroupManager::get_user_group_name($user_id);
$temp = array();
if (api_is_allowed_to_edit(null, true)) {
//if (api_get_setting('allow_user_course_subscription_by_course_admin') == 'true') {
$temp[] = $user_id;
//}
$image_path = UserManager::get_user_picture_path_by_id($user_id, 'web', false, true);
$user_profile = UserManager::get_picture_user($user_id, $image_path['file'], 22, USER_IMAGE_SIZE_SMALL, ' width="22" height="22" ');
if (!api_is_anonymous()) {
$photo = '<a href="userInfo.php?' . api_get_cidreq() . '&origin=' . $origin . '&uInfo=' . $user_id . '" title="' . get_lang('Info') . '" ><img src="' . $user_profile['file'] . '" ' . $user_profile['style'] . ' alt="' . api_get_person_name($o_course_user['firstname'], $o_course_user['lastname']) . '" title="' . api_get_person_name($o_course_user['firstname'], $o_course_user['lastname']) . '" /></a>';
} else {
$photo = '<img src="' . $user_profile['file'] . '" ' . $user_profile['style'] . ' alt="' . api_get_person_name($o_course_user['firstname'], $o_course_user['lastname']) . '" title="' . api_get_person_name($o_course_user['firstname'], $o_course_user['lastname']) . '" />';
}
$temp[] = $photo;
$temp[] = $o_course_user['official_code'];
if ($is_western_name_order) {
$temp[] = $o_course_user['firstname'];
$temp[] = $o_course_user['lastname'];
} else {
$temp[] = $o_course_user['lastname'];
$temp[] = $o_course_user['firstname'];
}
$temp[] = $o_course_user['username'];
$temp[] = isset($o_course_user['role']) ? $o_course_user['role'] : null;
//Description
$temp[] = implode(', ', $groups_name);
//Group
// Status
$default_status = '-';
if (isset($o_course_user['status_rel']) && $o_course_user['status_rel'] == 1 || isset($o_course_user['status_session']) && $o_course_user['status_session'] == 2) {
$default_status = get_lang('CourseManager');
} elseif (isset($o_course_user['tutor_id']) && $o_course_user['tutor_id'] == 1) {
$default_status = get_lang('Tutor');
}
$temp[] = $default_status;
//Active
$temp[] = $o_course_user['active'];
//User id for actions
$temp[] = $user_id;
} else {
$image_path = UserManager::get_user_picture_path_by_id($user_id, 'web', false, true);
$image_repository = $image_path['dir'];
$existing_image = $image_path['file'];
if (!api_is_anonymous()) {
$photo = '<a href="userInfo.php?' . api_get_cidreq() . '&origin=' . $origin . '&uInfo=' . $user_id . '" title="' . get_lang('Info') . '" ><img src="' . $image_repository . $existing_image . '" alt="' . api_get_person_name($o_course_user['firstname'], $o_course_user['lastname']) . '" width="22" height="22" title="' . api_get_person_name($o_course_user['firstname'], $o_course_user['lastname']) . '" /></a>';
} else {
$photo = '<img src="' . $image_repository . $existing_image . '" alt="' . api_get_person_name($o_course_user['firstname'], $o_course_user['lastname']) . '" width="22" height="22" title="' . api_get_person_name($o_course_user['firstname'], $o_course_user['lastname']) . '" />';
}
$temp[] = $user_id;
$temp[] = $photo;
$temp[] = $o_course_user['official_code'];
if ($is_western_name_order) {
$temp[] = $o_course_user['firstname'];
$temp[] = $o_course_user['lastname'];
} else {
$temp[] = $o_course_user['lastname'];
$temp[] = $o_course_user['firstname'];
}
$temp[] = $o_course_user['username'];
$temp[] = $o_course_user['role'];
$temp[] = implode(', ', $groups_name);
//Group
if ($course_info['unsubscribe'] == 1) {
//User id for actions
$temp[] = $user_id;
}
//$temp[] = $o_course_user['official_code'];
}
$a_users[$user_id] = $temp;
}
return $a_users;
}
/**
* @param int $in_from
* @param int $in_number_of_items
* @param int $in_column
* @param int $in_direction
* @param string $in_hotpot_path
* @param bool $in_get_count
* @param null $where_condition
* @return array|int
*/
public static function get_exam_results_hotpotatoes_data($in_from, $in_number_of_items, $in_column, $in_direction, $in_hotpot_path, $in_get_count = false, $where_condition = null)
{
$courseId = api_get_course_int_id();
/* by default in_column = 1 If parameters given,
it is the name of the column witch is the bdd field name*/
if ($in_column == 1) {
$in_column = 'firstname';
}
$in_hotpot_path = Database::escape_string($in_hotpot_path);
$in_direction = Database::escape_string($in_direction);
$in_column = Database::escape_string($in_column);
$in_number_of_items = intval($in_number_of_items);
$in_from = intval($in_from);
$TBL_TRACK_HOTPOTATOES = Database::get_main_table(TABLE_STATISTIC_TRACK_E_HOTPOTATOES);
$TBL_USER = Database::get_main_table(TABLE_MAIN_USER);
$sql = "SELECT * FROM {$TBL_TRACK_HOTPOTATOES} thp\n JOIN {$TBL_USER} u ON thp.exe_user_id = u.user_id\n WHERE thp.c_id = {$courseId} AND exe_name LIKE '{$in_hotpot_path}%'";
// just count how many answers
if ($in_get_count) {
$res = Database::query($sql);
return Database::num_rows($res);
}
// get a number of sorted results
$sql .= " {$where_condition}\n ORDER BY {$in_column} {$in_direction}\n LIMIT {$in_from}, {$in_number_of_items}";
$res = Database::query($sql);
$result = array();
$apiIsAllowedToEdit = api_is_allowed_to_edit();
$urlBase = api_get_path(WEB_CODE_PATH) . 'exercice/hotpotatoes_exercise_report.php?action=delete&' . api_get_cidreq() . '&id=';
while ($data = Database::fetch_array($res)) {
$actions = null;
if ($apiIsAllowedToEdit) {
$url = $urlBase . $data['id'] . '&path=' . $data['exe_name'];
$actions = Display::url(Display::return_icon('delete.png', get_lang('Delete')), $url);
}
$result[] = array('firstname' => $data['firstname'], 'lastname' => $data['lastname'], 'username' => $data['username'], 'group_name' => implode("<br/>", GroupManager::get_user_group_name($data['user_id'])), 'exe_date' => $data['exe_date'], 'score' => $data['exe_result'] . ' / ' . $data['exe_weighting'], 'actions' => $actions);
}
return $result;
}
/**
* Exports the complete report as an XLS file
* @return boolean False on error
*/
public function exportCompleteReportXLS($document_path = '', $user_id = null, $export_user_fields = false, $export_filter = 0, $exercise_id = 0, $hotpotato_name = null)
{
global $charset;
$this->getExercisesReporting($document_path, $user_id, $export_filter, $exercise_id, $hotpotato_name);
$filename = 'exercise_results_' . date('YmdGis') . '.xls';
if (!empty($user_id)) {
$filename = 'exercise_results_user_' . $user_id . '_' . date('YmdGis') . '.xls';
}
$workbook = new Spreadsheet_Excel_Writer();
$workbook->setTempDir(api_get_path(SYS_ARCHIVE_PATH));
$workbook->setVersion(8);
// BIFF8
$workbook->send($filename);
$worksheet =& $workbook->addWorksheet('Report ' . date('YmdGis'));
$worksheet->setInputEncoding(api_get_system_encoding());
$line = 0;
$column = 0;
//skip the first column (row titles)
// check if exists column 'user'
$with_column_user = false;
foreach ($this->results as $result) {
if (!empty($result['last_name']) && !empty($result['first_name'])) {
$with_column_user = true;
break;
}
}
$officialCodeInList = api_get_configuration_value('show_official_code_exercise_result_list');
if ($with_column_user) {
if (api_is_western_name_order()) {
$worksheet->write($line, $column, get_lang('FirstName'));
$column++;
$worksheet->write($line, $column, get_lang('LastName'));
$column++;
} else {
$worksheet->write($line, $column, get_lang('LastName'));
$column++;
$worksheet->write($line, $column, get_lang('FirstName'));
$column++;
}
if ($officialCodeInList) {
$worksheet->write($line, $column, get_lang('OfficialCode'));
$column++;
}
$worksheet->write($line, $column, get_lang('Email'));
$column++;
}
$worksheet->write($line, $column, get_lang('Groups'));
$column++;
if ($export_user_fields) {
//show user fields section with a big th colspan that spans over all fields
$extra_user_fields = UserManager::get_extra_fields(0, 1000, 5, 'ASC', false, 1);
//show the fields names for user fields
foreach ($extra_user_fields as $field) {
$worksheet->write($line, $column, api_html_entity_decode(strip_tags($field[3]), ENT_QUOTES, $charset));
$column++;
}
}
$worksheet->write($line, $column, get_lang('Title'));
$column++;
$worksheet->write($line, $column, get_lang('StartDate'));
$column++;
$worksheet->write($line, $column, get_lang('EndDate'));
$column++;
$worksheet->write($line, $column, get_lang('Duration') . ' (' . get_lang('MinMinutes') . ')');
$column++;
$worksheet->write($line, $column, get_lang('Score'));
$column++;
$worksheet->write($line, $column, get_lang('Total'));
$column++;
$worksheet->write($line, $column, get_lang('Status'));
$column++;
$worksheet->write($line, $column, get_lang('ToolLearnpath'));
$column++;
$worksheet->write($line, $column, get_lang('UserIsCurrentlySubscribed'));
$line++;
foreach ($this->results as $row) {
$column = 0;
if ($with_column_user) {
if (api_is_western_name_order()) {
$worksheet->write($line, $column, api_html_entity_decode(strip_tags($row['first_name']), ENT_QUOTES, $charset));
$column++;
$worksheet->write($line, $column, api_html_entity_decode(strip_tags($row['last_name']), ENT_QUOTES, $charset));
$column++;
} else {
$worksheet->write($line, $column, api_html_entity_decode(strip_tags($row['last_name']), ENT_QUOTES, $charset));
$column++;
$worksheet->write($line, $column, api_html_entity_decode(strip_tags($row['first_name']), ENT_QUOTES, $charset));
$column++;
}
if ($officialCodeInList) {
$worksheet->write($line, $column, api_html_entity_decode(strip_tags($row['official_code']), ENT_QUOTES, $charset));
$column++;
}
$worksheet->write($line, $column, api_html_entity_decode(strip_tags($row['email']), ENT_QUOTES, $charset));
$column++;
}
$worksheet->write($line, $column, api_html_entity_decode(strip_tags(implode(", ", GroupManager::get_user_group_name($row['user_id']))), ENT_QUOTES, $charset));
$column++;
if ($export_user_fields) {
//show user fields data, if any, for this user
$user_fields_values = UserManager::get_extra_user_data($row['user_id'], false, false, false, true);
foreach ($user_fields_values as $value) {
$worksheet->write($line, $column, api_html_entity_decode(strip_tags($value), ENT_QUOTES, $charset));
$column++;
}
}
$worksheet->write($line, $column, api_html_entity_decode(strip_tags($row['title']), ENT_QUOTES, $charset));
$column++;
$worksheet->write($line, $column, $row['start_date']);
$column++;
$worksheet->write($line, $column, $row['end_date']);
$column++;
$worksheet->write($line, $column, $row['duration']);
$column++;
$worksheet->write($line, $column, $row['result']);
$column++;
$worksheet->write($line, $column, $row['max']);
$column++;
$worksheet->write($line, $column, $row['status']);
$column++;
$worksheet->write($line, $column, $row['lp_name']);
$column++;
$worksheet->write($line, $column, $row['is_user_subscribed']);
$line++;
}
//output the results
$workbook->close();
return true;
}
/**
* Return a list of group for user with user_id=in_userid separated with in_separator
* @deprecated ?
*/
function displayGroupsForUser($in_separator, $in_userid)
{
$res = implode($in_separator, GroupManager::get_user_group_name($in_userid));
if ($res == "") {
$res = "<div style='text-align:center'>-</div>";
}
return $res;
}
/**
* Exports the complete report as an XLS file
* @return boolean False on error
*/
public function exportCompleteReportXLS($document_path = '', $user_id = null, $export_user_fields = false, $export_filter = 0, $exercise_id = 0, $hotpotato_name = null)
{
global $charset;
$this->getExercisesReporting($document_path, $user_id, $export_filter, $exercise_id, $hotpotato_name);
$filename = 'exercise_results_' . api_get_local_time() . '.xls';
if (!empty($user_id)) {
$filename = 'exercise_results_user_' . $user_id . '_' . api_get_local_time() . '.xls';
}
$spreadsheet = new PHPExcel();
$spreadsheet->setActiveSheetIndex(0);
$worksheet = $spreadsheet->getActiveSheet();
$line = 0;
$column = 0;
//skip the first column (row titles)
// check if exists column 'user'
$with_column_user = false;
foreach ($this->results as $result) {
if (!empty($result['last_name']) && !empty($result['first_name'])) {
$with_column_user = true;
break;
}
}
$officialCodeInList = api_get_setting('exercise.show_official_code_exercise_result_list');
if ($with_column_user) {
if (api_is_western_name_order()) {
$worksheet->SetCellValueByColumnAndRow($line, $column, get_lang('FirstName'));
$column++;
$worksheet->SetCellValueByColumnAndRow($line, $column, get_lang('LastName'));
$column++;
} else {
$worksheet->SetCellValueByColumnAndRow($line, $column, get_lang('LastName'));
$column++;
$worksheet->SetCellValueByColumnAndRow($line, $column, get_lang('FirstName'));
$column++;
}
if ($officialCodeInList === 'true') {
$worksheet->SetCellValueByColumnAndRow($line, $column, get_lang('OfficialCode'));
$column++;
}
$worksheet->SetCellValueByColumnAndRow($line, $column, get_lang('Email'));
$column++;
}
$worksheet->SetCellValueByColumnAndRow($line, $column, get_lang('Groups'));
$column++;
if ($export_user_fields) {
//show user fields section with a big th colspan that spans over all fields
$extra_user_fields = UserManager::get_extra_fields(0, 1000, 5, 'ASC', false, 1);
//show the fields names for user fields
foreach ($extra_user_fields as $field) {
$worksheet->SetCellValueByColumnAndRow($line, $column, api_html_entity_decode(strip_tags($field[3]), ENT_QUOTES, $charset));
$column++;
}
}
$worksheet->SetCellValueByColumnAndRow($line, $column, get_lang('Title'));
$column++;
$worksheet->SetCellValueByColumnAndRow($line, $column, get_lang('StartDate'));
$column++;
$worksheet->SetCellValueByColumnAndRow($line, $column, get_lang('EndDate'));
$column++;
$worksheet->SetCellValueByColumnAndRow($line, $column, get_lang('Duration') . ' (' . get_lang('MinMinutes') . ')');
$column++;
$worksheet->SetCellValueByColumnAndRow($line, $column, get_lang('Score'));
$column++;
$worksheet->SetCellValueByColumnAndRow($line, $column, get_lang('Total'));
$column++;
$worksheet->SetCellValueByColumnAndRow($line, $column, get_lang('Status'));
$column++;
$worksheet->SetCellValueByColumnAndRow($line, $column, get_lang('ToolLearnpath'));
$column++;
$worksheet->SetCellValueByColumnAndRow($line, $column, get_lang('UserIsCurrentlySubscribed'));
$line++;
foreach ($this->results as $row) {
$column = 0;
if ($with_column_user) {
if (api_is_western_name_order()) {
$worksheet->SetCellValueByColumnAndRow($line, $column, api_html_entity_decode(strip_tags($row['first_name']), ENT_QUOTES, $charset));
$column++;
$worksheet->SetCellValueByColumnAndRow($line, $column, api_html_entity_decode(strip_tags($row['last_name']), ENT_QUOTES, $charset));
$column++;
} else {
$worksheet->SetCellValueByColumnAndRow($line, $column, api_html_entity_decode(strip_tags($row['last_name']), ENT_QUOTES, $charset));
$column++;
$worksheet->SetCellValueByColumnAndRow($line, $column, api_html_entity_decode(strip_tags($row['first_name']), ENT_QUOTES, $charset));
$column++;
}
if ($officialCodeInList === 'true') {
$worksheet->SetCellValueByColumnAndRow($line, $column, api_html_entity_decode(strip_tags($row['official_code']), ENT_QUOTES, $charset));
$column++;
}
$worksheet->SetCellValueByColumnAndRow($line, $column, api_html_entity_decode(strip_tags($row['email']), ENT_QUOTES, $charset));
$column++;
}
$worksheet->SetCellValueByColumnAndRow($line, $column, api_html_entity_decode(strip_tags(implode(", ", GroupManager::get_user_group_name($row['user_id']))), ENT_QUOTES, $charset));
$column++;
if ($export_user_fields) {
//show user fields data, if any, for this user
$user_fields_values = UserManager::get_extra_user_data($row['user_id'], false, false, false, true);
foreach ($user_fields_values as $value) {
$worksheet->SetCellValueByColumnAndRow($line, $column, api_html_entity_decode(strip_tags($value), ENT_QUOTES, $charset));
$column++;
}
}
$worksheet->SetCellValueByColumnAndRow($line, $column, api_html_entity_decode(strip_tags($row['title']), ENT_QUOTES, $charset));
$column++;
$worksheet->SetCellValueByColumnAndRow($line, $column, $row['start_date']);
$column++;
$worksheet->SetCellValueByColumnAndRow($line, $column, $row['end_date']);
$column++;
$worksheet->SetCellValueByColumnAndRow($line, $column, $row['duration']);
$column++;
$worksheet->SetCellValueByColumnAndRow($line, $column, $row['result']);
$column++;
$worksheet->SetCellValueByColumnAndRow($line, $column, $row['max']);
$column++;
$worksheet->SetCellValueByColumnAndRow($line, $column, $row['status']);
$column++;
$worksheet->SetCellValueByColumnAndRow($line, $column, $row['lp_name']);
$column++;
$worksheet->SetCellValueByColumnAndRow($line, $column, $row['is_user_subscribed']);
$line++;
}
$file = api_get_path(SYS_ARCHIVE_PATH) . api_replace_dangerous_char($filename);
$writer = new PHPExcel_Writer_Excel2007($spreadsheet);
$writer->save($file);
DocumentManager::file_send_for_download($file, true, $filename);
return true;
}
