}
$result = CourseManager::get_user_list_from_course_code(null, null, "LIMIT {$start}, {$limit}", null, null, null, true, true, array('ruc'), $courseCodeList, $userIdList, null, $sessionIdList);
$new_result = array();
if (!empty($result)) {
foreach ($result as $row) {
$row['training_hours'] = api_time_to_hms($row['training_hours']);
$row['average_hours_per_user'] = api_time_to_hms($row['average_hours_per_user']);
$new_result[] = $row;
}
$result = $new_result;
}
break;
case 'get_user_course_report':
$columns = array('course', 'user', 'email', 'time', 'certificate', 'progress_100', 'progress');
$column_names = array(get_lang('Course'), get_lang('User'), get_lang('Email'), get_lang('ManHours'), get_lang('CertificateGenerated'), get_lang('Approved'), get_lang('CourseAdvance'));
$extra_fields = UserManager::get_extra_fields(0, 100, null, null, true, true);
if (!empty($extra_fields)) {
foreach ($extra_fields as $extra) {
$columns[] = $extra['1'];
$column_names[] = $extra['3'];
}
}
if (api_is_student_boss()) {
$columns[] = 'group';
$column_names[] = get_lang('Group');
}
if (!in_array($sidx, array('title'))) {
$sidx = 'title';
}
if (api_is_student_boss() && empty($userIdList)) {
$result = [];
/**
* @param FormValidator $form
* @param $extra_data
* @param $form_name
* @param bool $admin_permissions
* @param null $user_id
* @deprecated
* @return array
*/
static function set_extra_fields_in_form($form, $extra_data, $admin_permissions = false, $user_id = null)
{
$user_id = intval($user_id);
// EXTRA FIELDS
$extra = UserManager::get_extra_fields(0, 50, 5, 'ASC');
$jquery_ready_content = null;
foreach ($extra as $field_details) {
if (!$admin_permissions) {
if ($field_details[6] == 0) {
continue;
}
}
switch ($field_details[2]) {
case ExtraField::FIELD_TYPE_TEXT:
$form->addElement('text', 'extra_' . $field_details[1], $field_details[3], array('size' => 40));
$form->applyFilter('extra_' . $field_details[1], 'stripslashes');
$form->applyFilter('extra_' . $field_details[1], 'trim');
$form->applyFilter('extra_' . $field_details[1], 'html_filter');
if (!$admin_permissions) {
if ($field_details[7] == 0) {
$form->freeze('extra_' . $field_details[1]);
}
}
break;
case ExtraField::FIELD_TYPE_TEXTAREA:
$form->addHtmlEditor('extra_' . $field_details[1], $field_details[3], false, false, array('ToolbarSet' => 'Profile', 'Width' => '100%', 'Height' => '130'));
$form->applyFilter('extra_' . $field_details[1], 'stripslashes');
$form->applyFilter('extra_' . $field_details[1], 'trim');
if (!$admin_permissions) {
if ($field_details[7] == 0) {
$form->freeze('extra_' . $field_details[1]);
}
}
break;
case ExtraField::FIELD_TYPE_RADIO:
$group = array();
foreach ($field_details[9] as $option_id => $option_details) {
$options[$option_details[1]] = $option_details[2];
$group[] = $form->createElement('radio', 'extra_' . $field_details[1], $option_details[1], $option_details[2] . '<br />', $option_details[1]);
}
$form->addGroup($group, 'extra_' . $field_details[1], $field_details[3], '');
if (!$admin_permissions) {
if ($field_details[7] == 0) {
$form->freeze('extra_' . $field_details[1]);
}
}
break;
case ExtraField::FIELD_TYPE_SELECT:
$get_lang_variables = false;
if (in_array($field_details[1], array('mail_notify_message', 'mail_notify_invitation', 'mail_notify_group_message'))) {
$get_lang_variables = true;
}
$options = array();
foreach ($field_details[9] as $option_id => $option_details) {
if ($get_lang_variables) {
$options[$option_details[1]] = get_lang($option_details[2]);
} else {
$options[$option_details[1]] = $option_details[2];
}
}
if ($get_lang_variables) {
$field_details[3] = get_lang($field_details[3]);
}
$form->addElement('select', 'extra_' . $field_details[1], $field_details[3], $options, array('id' => 'extra_' . $field_details[1]));
if (!$admin_permissions) {
if ($field_details[7] == 0) {
$form->freeze('extra_' . $field_details[1]);
}
}
break;
case ExtraField::FIELD_TYPE_SELECT_MULTIPLE:
$options = array();
foreach ($field_details[9] as $option_id => $option_details) {
$options[$option_details[1]] = $option_details[2];
}
$form->addElement('select', 'extra_' . $field_details[1], $field_details[3], $options, array('multiple' => 'multiple'));
if (!$admin_permissions) {
if ($field_details[7] == 0) {
$form->freeze('extra_' . $field_details[1]);
}
}
break;
case ExtraField::FIELD_TYPE_DATE:
$form->addDatePicker('extra_' . $field_details[1], $field_details[3]);
$defaults['extra_' . $field_details[1]] = date('Y-m-d 12:00:00');
$form->setDefaults($defaults);
if (!$admin_permissions) {
if ($field_details[7] == 0) {
$form->freeze('extra_' . $field_details[1]);
}
}
$form->applyFilter('theme', 'trim');
break;
case ExtraField::FIELD_TYPE_DATETIME:
$form->addDateTimePicker('extra_' . $field_details[1], $field_details[3]);
$defaults['extra_' . $field_details[1]] = date('Y-m-d 12:00:00');
$form->setDefaults($defaults);
if (!$admin_permissions) {
if ($field_details[7] == 0) {
$form->freeze('extra_' . $field_details[1]);
}
}
$form->applyFilter('theme', 'trim');
break;
case ExtraField::FIELD_TYPE_DOUBLE_SELECT:
foreach ($field_details[9] as $key => $element) {
if ($element[2][0] == '*') {
$values['*'][$element[0]] = str_replace('*', '', $element[2]);
} else {
$values[0][$element[0]] = $element[2];
}
}
$group = '';
$group[] = $form->createElement('select', 'extra_' . $field_details[1], '', $values[0], '');
$group[] = $form->createElement('select', 'extra_' . $field_details[1] . '*', '', $values['*'], '');
$form->addGroup($group, 'extra_' . $field_details[1], $field_details[3], ' ');
if (!$admin_permissions) {
if ($field_details[7] == 0) {
$form->freeze('extra_' . $field_details[1]);
}
}
/* Recoding the selected values for double : if the user has
selected certain values, we have to assign them to the
correct select form */
if (array_key_exists('extra_' . $field_details[1], $extra_data)) {
// exploding all the selected values (of both select forms)
$selected_values = explode(';', $extra_data['extra_' . $field_details[1]]);
$extra_data['extra_' . $field_details[1]] = array();
// looping through the selected values and assigning the selected values to either the first or second select form
foreach ($selected_values as $key => $selected_value) {
if (array_key_exists($selected_value, $values[0])) {
$extra_data['extra_' . $field_details[1]]['extra_' . $field_details[1]] = $selected_value;
} else {
$extra_data['extra_' . $field_details[1]]['extra_' . $field_details[1] . '*'] = $selected_value;
}
}
}
break;
case ExtraField::FIELD_TYPE_DIVIDER:
$form->addElement('static', $field_details[1], '<br /><strong>' . $field_details[3] . '</strong>');
break;
case ExtraField::FIELD_TYPE_TAG:
//the magic should be here
$user_tags = UserManager::get_user_tags($user_id, $field_details[0]);
$tag_list = '';
if (is_array($user_tags) && count($user_tags) > 0) {
foreach ($user_tags as $tag) {
$tag_list .= '<option value="' . $tag['tag'] . '" class="selected">' . $tag['tag'] . '</option>';
}
}
$multi_select = '<select id="extra_' . $field_details[1] . '" name="extra_' . $field_details[1] . '">
' . $tag_list . '
</select>';
$form->addElement('label', $field_details[3], $multi_select);
$url = api_get_path(WEB_AJAX_PATH) . 'user_manager.ajax.php';
$complete_text = get_lang('StartToType');
//if cache is set to true the jquery will be called 1 time
$jquery_ready_content = <<<EOF
\$("#extra_{$field_details['1']}").fcbkcomplete({
json_url: "{$url}?a=search_tags&field_id={$field_details['0']}",
cache: false,
filter_case: true,
filter_hide: true,
complete_text:"{$complete_text}",
firstselected: true,
//onremove: "testme",
//onselect: "testme",
filter_selected: true,
newel: true
});
EOF;
break;
case ExtraField::FIELD_TYPE_TIMEZONE:
$form->addElement('select', 'extra_' . $field_details[1], $field_details[3], api_get_timezones(), '');
if ($field_details[7] == 0) {
$form->freeze('extra_' . $field_details[1]);
}
break;
case ExtraField::FIELD_TYPE_SOCIAL_PROFILE:
// get the social network's favicon
$icon_path = UserManager::get_favicon_from_url($extra_data['extra_' . $field_details[1]], $field_details[4]);
// special hack for hi5
$leftpad = '1.7';
$top = '0.4';
$domain = parse_url($icon_path, PHP_URL_HOST);
if ($domain == 'www.hi5.com' or $domain == 'hi5.com') {
$leftpad = '3';
$top = '0';
}
// print the input field
$form->addElement('text', 'extra_' . $field_details[1], $field_details[3], array('size' => 60, 'style' => 'background-image: url(\'' . $icon_path . '\'); background-repeat: no-repeat; background-position: 0.4em ' . $top . 'em; padding-left: ' . $leftpad . 'em; '));
$form->applyFilter('extra_' . $field_details[1], 'stripslashes');
$form->applyFilter('extra_' . $field_details[1], 'trim');
if ($field_details[7] == 0) {
$form->freeze('extra_' . $field_details[1]);
}
break;
case ExtraField::FIELD_TYPE_FILE:
$extra_field = 'extra_' . $field_details[1];
$form->addElement('file', $extra_field, $field_details[3], null, '');
if ($extra_file_list = UserManager::build_user_extra_file_list($user_id, $field_details[1], '', true)) {
$form->addElement('static', $extra_field . '_list', null, $extra_file_list);
}
if ($field_details[7] == 0) {
$form->freeze($extra_field);
}
break;
case ExtraField::FIELD_TYPE_MOBILE_PHONE_NUMBER:
$form->addElement('text', 'extra_' . $field_details[1], $field_details[3] . " (" . get_lang('CountryDialCode') . ")", array('size' => 40, 'placeholder' => '(xx)xxxxxxxxx'));
$form->applyFilter('extra_' . $field_details[1], 'stripslashes');
$form->applyFilter('extra_' . $field_details[1], 'trim');
$form->applyFilter('extra_' . $field_details[1], 'mobile_phone_number_filter');
$form->addRule('extra_' . $field_details[1], get_lang('MobilePhoneNumberWrong'), 'mobile_phone_number');
if (!$admin_permissions) {
if ($field_details[7] == 0) {
$form->freeze('extra_' . $field_details[1]);
}
}
break;
}
}
$return = array();
$return['jquery_ready_content'] = $jquery_ready_content;
return $return;
}
public static function display_additional_profile_fields()
{
// getting all the extra profile fields that are defined by the platform administrator
$extra_fields = UserManager::get_extra_fields(0, 50, 5, 'ASC');
// creating the form
$return = '<form action="courseLog.php" method="get" name="additional_profile_field_form" id="additional_profile_field_form">';
// the select field with the additional user profile fields (= this is where we select the field of which we want to see
// the information the users have entered or selected.
$return .= '<select name="additional_profile_field">';
$return .= '<option value="-">' . get_lang('SelectFieldToAdd') . '</option>';
$extra_fields_to_show = 0;
foreach ($extra_fields as $key => $field) {
// show only extra fields that are visible + and can be filtered, added by J.Montoya
if ($field[6] == 1 && $field[8] == 1) {
if (isset($_GET['additional_profile_field']) && $field[0] == $_GET['additional_profile_field']) {
$selected = 'selected="selected"';
} else {
$selected = '';
}
$extra_fields_to_show++;
$return .= '<option value="' . $field[0] . '" ' . $selected . '>' . $field[3] . '</option>';
}
}
$return .= '</select>';
// the form elements for the $_GET parameters (because the form is passed through GET
foreach ($_GET as $key => $value) {
if ($key != 'additional_profile_field') {
$return .= '<input type="hidden" name="' . Security::remove_XSS($key) . '" value="' . Security::remove_XSS($value) . '" />';
}
}
// the submit button
$return .= '<button class="save" type="submit">' . get_lang('AddAdditionalProfileField') . '</button>';
$return .= '</form>';
if ($extra_fields_to_show > 0) {
return $return;
} else {
return '';
}
}
/**
* Creates a multi array with the user fields that we can show. We look the visibility with the api_get_setting function
* The username is always NOT able to change it.
* @author Julio Montoya Armas <*****@*****.**>, Chamilo: Personality Test modification
* @return array[value_name][name]
* array[value_name][visibilty]
*/
static function make_field_list()
{
// LAST NAME and FIRST NAME
$field_list_array = array();
$field_list_array['lastname']['name'] = get_lang('LastName');
$field_list_array['firstname']['name'] = get_lang('FirstName');
if (api_get_setting('profile', 'name') != 'true') {
$field_list_array['firstname']['visibility'] = 0;
$field_list_array['lastname']['visibility'] = 0;
} else {
$field_list_array['firstname']['visibility'] = 1;
$field_list_array['lastname']['visibility'] = 1;
}
$field_list_array['username']['name'] = get_lang('Username');
$field_list_array['username']['visibility'] = 0;
// OFFICIAL CODE
$field_list_array['official_code']['name'] = get_lang('OfficialCode');
if (api_get_setting('profile', 'officialcode') != 'true') {
$field_list_array['official_code']['visibility'] = 1;
} else {
$field_list_array['official_code']['visibility'] = 0;
}
// EMAIL
$field_list_array['email']['name'] = get_lang('Email');
if (api_get_setting('profile', 'email') != 'true') {
$field_list_array['email']['visibility'] = 1;
} else {
$field_list_array['email']['visibility'] = 0;
}
// PHONE
$field_list_array['phone']['name'] = get_lang('Phone');
if (api_get_setting('profile', 'phone') != 'true') {
$field_list_array['phone']['visibility'] = 0;
} else {
$field_list_array['phone']['visibility'] = 1;
}
// LANGUAGE
$field_list_array['language']['name'] = get_lang('Language');
if (api_get_setting('profile', 'language') != 'true') {
$field_list_array['language']['visibility'] = 0;
} else {
$field_list_array['language']['visibility'] = 1;
}
// EXTRA FIELDS
$extra = UserManager::get_extra_fields(0, 50, 5, 'ASC');
foreach ($extra as $id => $field_details) {
if ($field_details[6] == 0) {
continue;
}
switch ($field_details[2]) {
case UserManager::USER_FIELD_TYPE_TEXT:
$field_list_array['extra_' . $field_details[1]]['name'] = $field_details[3];
if ($field_details[7] == 0) {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 0;
} else {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 1;
}
break;
case UserManager::USER_FIELD_TYPE_TEXTAREA:
$field_list_array['extra_' . $field_details[1]]['name'] = $field_details[3];
if ($field_details[7] == 0) {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 0;
} else {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 1;
}
break;
case UserManager::USER_FIELD_TYPE_RADIO:
$field_list_array['extra_' . $field_details[1]]['name'] = $field_details[3];
if ($field_details[7] == 0) {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 0;
} else {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 1;
}
break;
case UserManager::USER_FIELD_TYPE_SELECT:
$get_lang_variables = false;
if (in_array($field_details[1], array('mail_notify_message', 'mail_notify_invitation', 'mail_notify_group_message'))) {
$get_lang_variables = true;
}
if ($get_lang_variables) {
$field_list_array['extra_' . $field_details[1]]['name'] = get_lang($field_details[3]);
} else {
$field_list_array['extra_' . $field_details[1]]['name'] = $field_details[3];
}
if ($field_details[7] == 0) {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 0;
} else {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 1;
}
break;
case UserManager::USER_FIELD_TYPE_SELECT_MULTIPLE:
$field_list_array['extra_' . $field_details[1]]['name'] = $field_details[3];
if ($field_details[7] == 0) {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 0;
} else {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 1;
}
break;
case UserManager::USER_FIELD_TYPE_DATE:
$field_list_array['extra_' . $field_details[1]]['name'] = $field_details[3];
if ($field_details[7] == 0) {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 0;
} else {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 1;
}
break;
case UserManager::USER_FIELD_TYPE_DATETIME:
$field_list_array['extra_' . $field_details[1]]['name'] = $field_details[3];
if ($field_details[7] == 0) {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 0;
} else {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 1;
}
break;
case UserManager::USER_FIELD_TYPE_DOUBLE_SELECT:
$field_list_array['extra_' . $field_details[1]]['name'] = $field_details[3];
if ($field_details[7] == 0) {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 0;
} else {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 1;
}
break;
case UserManager::USER_FIELD_TYPE_DIVIDER:
//$form->addElement('static',$field_details[1], '<br /><strong>'.$field_details[3].'</strong>');
break;
}
}
return $field_list_array;
}
/**
* @param FormValidator $form
* @param array $extraData
* @param string $form_name
* @param bool $admin_permissions
* @param int $user_id
* @param array $extra
* @param int $itemId
*
* @return array
*/
public function set_extra_fields_in_form($form, $extraData, $form_name, $admin_permissions = false, $user_id = null, $extra = array(), $itemId = null)
{
$user_id = intval($user_id);
$type = $this->type;
// User extra fields
if ($type == 'user') {
$extra = UserManager::get_extra_fields(0, 50, 5, 'ASC', true, null, true);
}
$jquery_ready_content = null;
if (!empty($extra)) {
foreach ($extra as $field_details) {
// Getting default value id if is set
$defaultValueId = null;
if (isset($field_details['options']) && !empty($field_details['options'])) {
$valueToFind = null;
if (isset($field_details['field_default_value'])) {
$valueToFind = $field_details['field_default_value'];
}
// If a value is found we override the default value
if (isset($extraData['extra_' . $field_details['field_variable']])) {
$valueToFind = $extraData['extra_' . $field_details['field_variable']];
}
foreach ($field_details['options'] as $option) {
if ($option['option_value'] == $valueToFind) {
$defaultValueId = $option['id'];
}
}
}
if (!$admin_permissions) {
if ($field_details['field_visible'] == 0) {
continue;
}
}
switch ($field_details['field_type']) {
case ExtraField::FIELD_TYPE_TEXT:
$form->addElement('text', 'extra_' . $field_details['field_variable'], $field_details['field_display_text'], array('class' => 'span4'));
$form->applyFilter('extra_' . $field_details['field_variable'], 'stripslashes');
$form->applyFilter('extra_' . $field_details['field_variable'], 'trim');
if (!$admin_permissions) {
if ($field_details['field_visible'] == 0) {
$form->freeze('extra_' . $field_details['field_variable']);
}
}
break;
case ExtraField::FIELD_TYPE_TEXTAREA:
$form->add_html_editor('extra_' . $field_details['field_variable'], $field_details['field_display_text'], false, false, array('ToolbarSet' => 'Profile', 'Width' => '100%', 'Height' => '130'));
$form->applyFilter('extra_' . $field_details['field_variable'], 'stripslashes');
$form->applyFilter('extra_' . $field_details['field_variable'], 'trim');
if (!$admin_permissions) {
if ($field_details['field_visible'] == 0) {
$form->freeze('extra_' . $field_details['field_variable']);
}
}
break;
case ExtraField::FIELD_TYPE_RADIO:
$group = array();
if (isset($field_details['options']) && !empty($field_details['options'])) {
foreach ($field_details['options'] as $option_details) {
$options[$option_details['option_value']] = $option_details['option_display_text'];
$group[] = $form->createElement('radio', 'extra_' . $field_details['field_variable'], $option_details['option_value'], $option_details['option_display_text'] . '<br />', $option_details['option_value']);
}
}
$form->addGroup($group, 'extra_' . $field_details['field_variable'], $field_details['field_display_text'], '');
if (!$admin_permissions) {
if ($field_details['field_visible'] == 0) {
$form->freeze('extra_' . $field_details['field_variable']);
}
}
break;
case ExtraField::FIELD_TYPE_CHECKBOX:
$group = array();
if (isset($field_details['options']) && !empty($field_details['options'])) {
foreach ($field_details['options'] as $option_details) {
$options[$option_details['option_value']] = $option_details['option_display_text'];
$group[] = $form->createElement('checkbox', 'extra_' . $field_details['field_variable'], $option_details['option_value'], $option_details['option_display_text'] . '<br />', $option_details['option_value']);
}
} else {
// We assume that is a switch on/off with 1 and 0 as values
$group[] = $form->createElement('checkbox', 'extra_' . $field_details['field_variable'], null, 'Yes <br />', null);
}
$form->addGroup($group, 'extra_' . $field_details['field_variable'], $field_details['field_display_text'], '');
if (!$admin_permissions) {
if ($field_details['field_visible'] == 0) {
$form->freeze('extra_' . $field_details['field_variable']);
}
}
break;
case ExtraField::FIELD_TYPE_SELECT:
$get_lang_variables = false;
if (in_array($field_details['field_variable'], array('mail_notify_message', 'mail_notify_invitation', 'mail_notify_group_message'))) {
$get_lang_variables = true;
}
// Get extra field workflow
$userInfo = api_get_user_info();
$addOptions = array();
$optionsExists = Database::getManager()->getRepository('ChamiloCoreBundle:ExtraFieldOptionRelFieldOption')->findOneBy(array('fieldId' => $field_details['id']));
if ($optionsExists) {
if (isset($userInfo['status']) && !empty($userInfo['status'])) {
$fieldWorkFlow = Database::getManager()->getRepository('ChamiloCoreBundle:ExtraFieldOptionRelFieldOption')->findBy(array('fieldId' => $field_details['id'], 'relatedFieldOptionId' => $defaultValueId, 'roleId' => $userInfo['status']));
foreach ($fieldWorkFlow as $item) {
$addOptions[] = $item->getFieldOptionId();
}
}
}
$options = array();
if (empty($defaultValueId)) {
$options[''] = get_lang('SelectAnOption');
}
$optionList = array();
if (!empty($field_details['options'])) {
foreach ($field_details['options'] as $option_details) {
$optionList[$option_details['id']] = $option_details;
if ($get_lang_variables) {
$options[$option_details['option_value']] = get_lang($option_details['option_display_text']);
} else {
if ($optionsExists) {
// Adding always the default value
if ($option_details['id'] == $defaultValueId) {
$options[$option_details['option_value']] = $option_details['option_display_text'];
} else {
if (isset($addOptions) && !empty($addOptions)) {
// Parsing filters
if (in_array($option_details['id'], $addOptions)) {
$options[$option_details['option_value']] = $option_details['option_display_text'];
}
}
}
} else {
// Normal behaviour
$options[$option_details['option_value']] = $option_details['option_display_text'];
}
}
}
if (isset($optionList[$defaultValueId])) {
if (isset($optionList[$defaultValueId]['option_value']) && $optionList[$defaultValueId]['option_value'] == 'aprobada') {
if (api_is_question_manager() == false) {
$form->freeze();
}
}
}
// Setting priority message
if (isset($optionList[$defaultValueId]) && isset($optionList[$defaultValueId]['priority'])) {
if (!empty($optionList[$defaultValueId]['priority'])) {
$priorityId = $optionList[$defaultValueId]['priority'];
$option = new ExtraFieldOption($this->type);
$messageType = $option->getPriorityMessageType($priorityId);
$form->addElement('label', null, Display::return_message($optionList[$defaultValueId]['priority_message'], $messageType));
}
}
}
if ($get_lang_variables) {
$field_details['field_display_text'] = get_lang($field_details['field_display_text']);
}
// chzn-select doesn't work for sessions??
$form->addElement('select', 'extra_' . $field_details['field_variable'], $field_details['field_display_text'], $options, array('id' => 'extra_' . $field_details['field_variable']));
if ($optionsExists && $field_details['field_loggeable'] && !empty($defaultValueId)) {
$form->addElement('textarea', 'extra_' . $field_details['field_variable'] . '_comment', $field_details['field_display_text'] . ' ' . get_lang('Comment'));
$em = Database::getManager();
$extraFieldValue = new ExtraFieldValue($this->type);
$repo = $em->getRepository($extraFieldValue->entityName);
$repoLog = $em->getRepository('Gedmo\\Loggable\\Entity\\LogEntry');
$newEntity = $repo->findOneBy(array($this->handlerEntityId => $itemId, 'fieldId' => $field_details['id']));
// @todo move this in a function inside the class
if ($newEntity) {
$logs = $repoLog->getLogEntries($newEntity);
if (!empty($logs)) {
$html = '<b>' . get_lang('LatestChanges') . '</b><br /><br />';
$table = new HTML_Table(array('class' => 'data_table'));
$table->setHeaderContents(0, 0, get_lang('Value'));
$table->setHeaderContents(0, 1, get_lang('Comment'));
$table->setHeaderContents(0, 2, get_lang('ModifyDate'));
$table->setHeaderContents(0, 3, get_lang('Username'));
$row = 1;
foreach ($logs as $log) {
$column = 0;
$data = $log->getData();
$fieldValue = isset($data['fieldValue']) ? $data['fieldValue'] : null;
$comment = isset($data['comment']) ? $data['comment'] : null;
$table->setCellContents($row, $column, $fieldValue);
$column++;
$table->setCellContents($row, $column, $comment);
$column++;
$table->setCellContents($row, $column, api_get_local_time($log->getLoggedAt()->format('Y-m-d H:i:s')));
$column++;
$table->setCellContents($row, $column, $log->getUsername());
$row++;
}
$form->addElement('label', null, $html . $table->toHtml());
}
}
}
if (!$admin_permissions) {
if ($field_details['field_visible'] == 0) {
$form->freeze('extra_' . $field_details['field_variable']);
}
}
break;
case ExtraField::FIELD_TYPE_SELECT_MULTIPLE:
$options = array();
foreach ($field_details['options'] as $option_id => $option_details) {
$options[$option_details['option_value']] = $option_details['option_display_text'];
}
$form->addElement('select', 'extra_' . $field_details['field_variable'], $field_details['field_display_text'], $options, array('multiple' => 'multiple'));
if (!$admin_permissions) {
if ($field_details['field_visible'] == 0) {
$form->freeze('extra_' . $field_details['field_variable']);
}
}
break;
case ExtraField::FIELD_TYPE_DATE:
$form->addElement('datepickerdate', 'extra_' . $field_details['field_variable'], $field_details['field_display_text'], array('form_name' => $form_name));
$form->_elements[$form->_elementIndex['extra_' . $field_details['field_variable']]]->setLocalOption('minYear', 1900);
$defaults['extra_' . $field_details['field_variable']] = date('Y-m-d 12:00:00');
if (!isset($form->_defaultValues['extra_' . $field_details['field_variable']])) {
$form->setDefaults($defaults);
}
if (!$admin_permissions) {
if ($field_details['field_visible'] == 0) {
$form->freeze('extra_' . $field_details['field_variable']);
}
}
$form->applyFilter('theme', 'trim');
break;
case ExtraField::FIELD_TYPE_DATETIME:
$form->addElement('datepicker', 'extra_' . $field_details['field_variable'], $field_details['field_display_text'], array('form_name' => $form_name));
$form->_elements[$form->_elementIndex['extra_' . $field_details['field_variable']]]->setLocalOption('minYear', 1900);
$defaults['extra_' . $field_details['field_variable']] = date('Y-m-d 12:00:00');
if (!isset($form->_defaultValues['extra_' . $field_details['field_variable']])) {
$form->setDefaults($defaults);
}
if (!$admin_permissions) {
if ($field_details['field_visible'] == 0) {
$form->freeze('extra_' . $field_details['field_variable']);
}
}
$form->applyFilter('theme', 'trim');
break;
case ExtraField::FIELD_TYPE_DOUBLE_SELECT:
$first_select_id = 'first_extra_' . $field_details['field_variable'];
$url = api_get_path(WEB_AJAX_PATH) . 'extra_field.ajax.php?1=1';
$jquery_ready_content .= '
$("#' . $first_select_id . '").on("change", function() {
var id = $(this).val();
if (id) {
$.ajax({
url: "' . $url . '&a=get_second_select_options",
dataType: "json",
data: "type=' . $type . '&field_id=' . $field_details['id'] . '&option_value_id="+id,
success: function(data) {
$("#second_extra_' . $field_details['field_variable'] . '").empty();
$.each(data, function(index, value) {
$("#second_extra_' . $field_details['field_variable'] . '").append($("<option/>", {
value: index,
text: value
}));
});
},
});
} else {
$("#second_extra_' . $field_details['field_variable'] . '").empty();
}
});';
$first_id = null;
$second_id = null;
if (!empty($extraData)) {
$first_id = $extraData['extra_' . $field_details['field_variable']]['extra_' . $field_details['field_variable']];
$second_id = $extraData['extra_' . $field_details['field_variable']]['extra_' . $field_details['field_variable'] . '_second'];
}
$options = ExtraField::extra_field_double_select_convert_array_to_ordered_array($field_details['options']);
$values = array('' => get_lang('Select'));
$second_values = array();
if (!empty($options)) {
foreach ($options as $option) {
foreach ($option as $sub_option) {
if ($sub_option['option_value'] == '0') {
$values[$sub_option['id']] = $sub_option['option_display_text'];
} else {
if ($first_id === $sub_option['option_value']) {
$second_values[$sub_option['id']] = $sub_option['option_display_text'];
}
}
}
}
}
$group = array();
$group[] = $form->createElement('select', 'extra_' . $field_details['field_variable'], null, $values, array('id' => $first_select_id));
$group[] = $form->createElement('select', 'extra_' . $field_details['field_variable'] . '_second', null, $second_values, array('id' => 'second_extra_' . $field_details['field_variable']));
$form->addGroup($group, 'extra_' . $field_details['field_variable'], $field_details['field_display_text'], ' ');
if (!$admin_permissions) {
if ($field_details['field_visible'] == 0) {
$form->freeze('extra_' . $field_details['field_variable']);
}
}
break;
case ExtraField::FIELD_TYPE_DIVIDER:
$form->addElement('static', $field_details['field_variable'], '<br /><strong>' . $field_details['field_display_text'] . '</strong>');
break;
case ExtraField::FIELD_TYPE_TAG:
$field_variable = $field_details['field_variable'];
$field_id = $field_details['id'];
if ($this->type == 'user') {
// The magic should be here
$user_tags = UserManager::get_user_tags($user_id, $field_details['id']);
$tag_list = '';
if (is_array($user_tags) && count($user_tags) > 0) {
foreach ($user_tags as $tag) {
$tag_list .= '<option value="' . $tag['tag'] . '" class="selected">' . $tag['tag'] . '</option>';
}
}
$url = api_get_path(WEB_AJAX_PATH) . 'user_manager.ajax.php?';
} else {
$extraFieldValue = new ExtraFieldValue($this->type);
$tags = array();
if (!empty($itemId)) {
$tags = $extraFieldValue->getAllValuesByItemAndField($itemId, $field_id);
}
$tag_list = '';
if (is_array($tags) && count($tags) > 0) {
$extraFieldOption = new ExtraFieldOption($this->type);
foreach ($tags as $tag) {
$option = $extraFieldOption->get($tag['field_value']);
$tag_list .= '<option value="' . $option['id'] . '" class="selected">' . $option['option_display_text'] . '</option>';
}
}
$url = api_get_path(WEB_AJAX_PATH) . 'extra_field.ajax.php';
}
$form->addElement('hidden', 'extra_' . $field_details['field_variable'] . '__persist__', 1);
$multiSelect = '<select id="extra_' . $field_details['field_variable'] . '" name="extra_' . $field_details['field_variable'] . '">
' . $tag_list . '
</select>';
$form->addElement('label', $field_details['field_display_text'], $multiSelect);
$complete_text = get_lang('StartToType');
//if cache is set to true the jquery will be called 1 time
$jquery_ready_content .= <<<EOF
\$("#extra_{$field_variable}").fcbkcomplete({
json_url: "{$url}?a=search_tags&field_id={$field_id}&type={$this->type}",
cache: false,
filter_case: true,
filter_hide: true,
complete_text:"{$complete_text}",
firstselected: false,
filter_selected: true,
newel: true
});
EOF;
$jquery_ready_content = null;
break;
case ExtraField::FIELD_TYPE_TIMEZONE:
$form->addElement('select', 'extra_' . $field_details['field_variable'], $field_details['field_display_text'], api_get_timezones(), '');
if ($field_details['field_visible'] == 0) {
$form->freeze('extra_' . $field_details['field_variable']);
}
break;
case ExtraField::FIELD_TYPE_SOCIAL_PROFILE:
// get the social network's favicon
$icon_path = UserManager::get_favicon_from_url($extraData['extra_' . $field_details['field_variable']], $field_details['field_default_value']);
// special hack for hi5
$leftpad = '1.7';
$top = '0.4';
$domain = parse_url($icon_path, PHP_URL_HOST);
if ($domain == 'www.hi5.com' or $domain == 'hi5.com') {
$leftpad = '3';
$top = '0';
}
// print the input field
$form->addElement('text', 'extra_' . $field_details['field_variable'], $field_details['field_display_text'], array('size' => 60, 'style' => 'background-image: url(\'' . $icon_path . '\'); background-repeat: no-repeat; background-position: 0.4em ' . $top . 'em; padding-left: ' . $leftpad . 'em; '));
$form->applyFilter('extra_' . $field_details['field_variable'], 'stripslashes');
$form->applyFilter('extra_' . $field_details['field_variable'], 'trim');
if ($field_details['field_visible'] == 0) {
$form->freeze('extra_' . $field_details['field_variable']);
}
break;
}
}
}
$return = array();
$return['jquery_ready_content'] = $jquery_ready_content;
return $return;
}
/**
* Creates a multi array with the user fields that we can show. We look the visibility with the api_get_setting function
* The username is always NOT able to change it.
* @author Julio Montoya Armas <*****@*****.**>, Chamilo: Personality Test modification
* @return array[value_name][name]
* array[value_name][visibilty]
*/
static function make_field_list()
{
// LAST NAME and FIRST NAME
$field_list_array = array();
$field_list_array['lastname']['name'] = get_lang('Lastname');
$field_list_array['firstname']['name'] = get_lang('Firstname');
if (api_get_setting('profile', 'name') != 'true') {
$field_list_array['firstname']['visibility'] = 0;
$field_list_array['lastname']['visibility'] = 0;
} else {
$field_list_array['firstname']['visibility'] = 1;
$field_list_array['lastname']['visibility'] = 1;
}
$field_list_array['username']['name'] = get_lang('Username');
$field_list_array['username']['visibility'] = 0;
// OFFICIAL CODE
$field_list_array['official_code']['name'] = get_lang('OfficialCode');
if (api_get_setting('profile', 'officialcode') != 'true') {
$field_list_array['official_code']['visibility'] = 1;
} else {
$field_list_array['official_code']['visibility'] = 0;
}
// EMAIL
$field_list_array['email']['name'] = get_lang('Email');
if (api_get_setting('profile', 'email') != 'true') {
$field_list_array['email']['visibility'] = 1;
} else {
$field_list_array['email']['visibility'] = 0;
}
// OPENID URL
//$field_list_array[] = 'openid_authentication';
/*
if (is_profile_editable() && api_get_setting('openid_authentication') == 'true') {
$form->addElement('text', 'openid', get_lang('OpenIDURL'), array('size' => 40));
if (api_get_setting('profile', 'openid') != 'true') {
$form->freeze('openid');
}
$form->applyFilter('openid', 'trim');
//if (api_get_setting('registration', 'openid') == 'true')
// $form->addRule('openid', get_lang('ThisFieldIsRequired'), 'required');
}*/
// PHONE
$field_list_array['phone']['name'] = get_lang('Phone');
if (api_get_setting('profile', 'phone') != 'true') {
$field_list_array['phone']['visibility'] = 0;
} else {
$field_list_array['phone']['visibility'] = 1;
}
// LANGUAGE
$field_list_array['language']['name'] = get_lang('Language');
if (api_get_setting('profile', 'language') != 'true') {
$field_list_array['language']['visibility'] = 0;
} else {
$field_list_array['language']['visibility'] = 1;
}
// EXTRA FIELDS
$extra = UserManager::get_extra_fields(0, 50, 5, 'ASC');
$extra_data = UserManager::get_extra_user_data(api_get_user_id(), true);
foreach ($extra as $id => $field_details) {
if ($field_details[6] == 0) {
continue;
}
switch ($field_details[2]) {
case UserManager::USER_FIELD_TYPE_TEXT:
$field_list_array['extra_' . $field_details[1]]['name'] = $field_details[3];
if ($field_details[7] == 0) {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 0;
} else {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 1;
}
break;
case UserManager::USER_FIELD_TYPE_TEXTAREA:
$field_list_array['extra_' . $field_details[1]]['name'] = $field_details[3];
if ($field_details[7] == 0) {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 0;
} else {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 1;
}
break;
case UserManager::USER_FIELD_TYPE_RADIO:
$field_list_array['extra_' . $field_details[1]]['name'] = $field_details[3];
if ($field_details[7] == 0) {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 0;
} else {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 1;
}
break;
case UserManager::USER_FIELD_TYPE_SELECT:
$field_list_array['extra_' . $field_details[1]]['name'] = $field_details[3];
if ($field_details[7] == 0) {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 0;
} else {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 1;
}
break;
case UserManager::USER_FIELD_TYPE_SELECT_MULTIPLE:
$field_list_array['extra_' . $field_details[1]]['name'] = $field_details[3];
if ($field_details[7] == 0) {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 0;
} else {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 1;
}
break;
case UserManager::USER_FIELD_TYPE_DATE:
$field_list_array['extra_' . $field_details[1]]['name'] = $field_details[3];
if ($field_details[7] == 0) {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 0;
} else {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 1;
}
break;
case UserManager::USER_FIELD_TYPE_DATETIME:
$field_list_array['extra_' . $field_details[1]]['name'] = $field_details[3];
if ($field_details[7] == 0) {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 0;
} else {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 1;
}
break;
case UserManager::USER_FIELD_TYPE_DOUBLE_SELECT:
$field_list_array['extra_' . $field_details[1]]['name'] = $field_details[3];
if ($field_details[7] == 0) {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 0;
} else {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 1;
}
/*
foreach ($field_details[8] as $key => $element) {
if ($element[2][0] == '*') {
$values['*'][$element[0]] = str_replace('*','',$element[2]);
} else {
$values[0][$element[0]] = $element[2];
}
}
$group = '';
$group[] =& HTML_QuickForm::createElement('select', 'extra_'.$field_details[1], '', $values[0], '');
$group[] =& HTML_QuickForm::createElement('select', 'extra_'.$field_details[1].'*', '', $values['*'], '');
$form->addGroup($group, 'extra_'.$field_details[1], $field_details[3], ' ');
if ($field_details[7] == 0) $form->freeze('extra_'.$field_details[1]);
// Recoding the selected values for double : if the user has selected certain values, we have to assign them to the correct select form
if (key_exists('extra_'.$field_details[1], $extra_data)) {
// Exploding all the selected values (of both select forms)
$selected_values = explode(';',$extra_data['extra_'.$field_details[1]]);
$extra_data['extra_'.$field_details[1]] = array();
// Looping through the selected values and assigning the selected values to either the first or second select form
foreach ($selected_values as $key => $selected_value) {
if (key_exists($selected_value, $values[0])) {
$extra_data['extra_'.$field_details[1]]['extra_'.$field_details[1]] = $selected_value;
} else {
$extra_data['extra_'.$field_details[1]]['extra_'.$field_details[1].'*'] = $selected_value;
}
}
}*/
break;
case UserManager::USER_FIELD_TYPE_DIVIDER:
//$form->addElement('static',$field_details[1], '<br /><strong>'.$field_details[3].'</strong>');
break;
}
}
return $field_list_array;
}
/**
* This function displays a dropdown list with all the additional user profile fields defined by the platform administrator in
* platform administration > profiling. Only the fields that have predefined fields are usefull for such a filter.
*
*/
function display_extra_profile_fields_filter($type)
{
// getting all the additional user profile fields
$extra = UserManager::get_extra_fields(0, 50, 5, 'ASC');
$return = '<option value="">' . get_lang('SelectFilter') . '</option>';
// looping through the additional user profile fields
foreach ($extra as $id => $field_details) {
// $field_details[2] contains the type of the additional user profile field
switch ($field_details[2]) {
// text fields cannot be used as a filter
case ExtraField::FIELD_TYPE_TEXT:
break;
// text area fields cannot be used as a filter
// text area fields cannot be used as a filter
case ExtraField::FIELD_TYPE_TEXTAREA:
break;
case ExtraField::FIELD_TYPE_RADIO:
case ExtraField::FIELD_TYPE_SELECT:
case ExtraField::FIELD_TYPE_SELECT_MULTIPLE:
$return .= '<optgroup label="' . $field_details[3] . '">';
foreach ($field_details[9] as $option_id => $option_details) {
if ($_GET['subscribe_user_filter_value'] == $field_details[0] . '*' . $option_details[1]) {
$selected = 'selected="selected"';
} else {
$selected = false;
}
$return .= '<option value="' . $field_details[0] . '*' . $option_details[1] . '" ' . $selected . '>' . $option_details[2] . '</option>';
}
$return .= '</optgroup>';
break;
}
}
echo '<form id="subscribe_user_filter" name="subscribe_user_filter" method="get" action="' . api_get_self() . '?api_get_cidreq" style="float:left;">';
echo ' <input type="hidden" name="type" id="type" value="' . $type . '" />';
echo '<select name="subscribe_user_filter_value" id="subscribe_user_filter_value">' . $return . '</select>';
echo '<button type="submit" name="submit_filter" id="submit_filter" value="" class="search">' . get_lang('Filter') . '</button>';
echo '</form>';
}
/**
* 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;
}
}
if ($with_column_user) {
$worksheet->write($line, $column, get_lang('Email'));
$column++;
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 ($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'));
$line++;
foreach ($this->results as $row) {
$column = 0;
if ($with_column_user) {
$worksheet->write($line, $column, api_html_entity_decode(strip_tags($row['email']), ENT_QUOTES, $charset));
$column++;
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 ($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']);
$line++;
}
$workbook->close();
return true;
}
/**
* Return user info array of all users registered in a course
* This only returns the users that are registered in this actual course, not linked courses.
* @param string $course_code
* @param int $session_id
* @param string $limit
* @param string $order_by the field to order the users by.
* Valid values are 'lastname', 'firstname', 'username', 'email', 'official_code' OR a part of a SQL statement
* that starts with ORDER BY ...
* @param null $filter_by_status if using the session_id: 0 or 2 (student, coach),
* if using session_id = 0 STUDENT or COURSEMANAGER
* @param null $return_count
* @param bool $add_reports
* @param bool $resumed_report
* @param array $extra_field
* @param array $courseCodeList
* @param array $userIdList
* @param string $filterByActive
* @param array $sessionIdList
* @return array|int
*/
public static function get_user_list_from_course_code($course_code = null, $session_id = 0, $limit = null, $order_by = null, $filter_by_status = null, $return_count = null, $add_reports = false, $resumed_report = false, $extra_field = array(), $courseCodeList = array(), $userIdList = array(), $filterByActive = null, $sessionIdList = array())
{
$course_table = Database::get_main_table(TABLE_MAIN_COURSE);
$sessionTable = Database::get_main_table(TABLE_MAIN_SESSION);
$session_id = intval($session_id);
$course_code = Database::escape_string($course_code);
$courseInfo = api_get_course_info($course_code);
$courseId = 0;
if (!empty($courseInfo)) {
$courseId = $courseInfo['real_id'];
}
$where = array();
if (empty($order_by)) {
$order_by = 'user.lastname, user.firstname';
if (api_is_western_name_order()) {
$order_by = 'user.firstname, user.lastname';
}
}
// if the $order_by does not contain 'ORDER BY'
// we have to check if it is a valid field that can be sorted on
if (!strstr($order_by, 'ORDER BY')) {
if (!empty($order_by)) {
$order_by = 'ORDER BY ' . $order_by;
} else {
$order_by = '';
}
}
$filter_by_status_condition = null;
if (!empty($session_id) || !empty($sessionIdList)) {
$sql = 'SELECT DISTINCT
user.user_id,
user.email,
session_course_user.status as status_session,
session_id,
user.*,
course.*,
session.name as session_name
';
if ($return_count) {
$sql = " SELECT COUNT(user.user_id) as count";
}
$sessionCondition = " session_course_user.session_id = {$session_id}";
if (!empty($sessionIdList)) {
$sessionIdListTostring = implode("','", array_map('intval', $sessionIdList));
$sessionCondition = " session_course_user.session_id IN ('{$sessionIdListTostring}') ";
}
$courseCondition = " course.id = {$courseId}";
if (!empty($courseCodeList)) {
$courseCodeListForSession = array_map(array('Database', 'escape_string'), $courseCodeList);
$courseCodeListForSession = implode('","', $courseCodeListForSession);
$courseCondition = ' course.code IN ("' . $courseCodeListForSession . '") ';
}
$sql .= ' FROM ' . Database::get_main_table(TABLE_MAIN_USER) . ' as user ';
$sql .= " LEFT JOIN " . Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER) . " as session_course_user\n ON\n user.user_id = session_course_user.user_id AND\n {$sessionCondition}\n INNER JOIN {$course_table} course ON session_course_user.c_id = course.id AND\n {$courseCondition}\n INNER JOIN {$sessionTable} session ON session_course_user.session_id = session.id\n ";
$where[] = ' session_course_user.c_id IS NOT NULL ';
// 2 = coach
// 0 = student
if (isset($filter_by_status)) {
$filter_by_status = intval($filter_by_status);
$filter_by_status_condition = " session_course_user.status = {$filter_by_status} AND ";
}
} else {
if ($return_count) {
$sql = " SELECT COUNT(*) as count";
if ($resumed_report) {
//$sql = " SELECT count(field_id) ";
}
} else {
if (empty($course_code)) {
$sql = 'SELECT DISTINCT
course.title,
course.code,
course_rel_user.status as status_rel,
user.user_id,
user.email,
course_rel_user.is_tutor,
user.* ';
} else {
$sql = 'SELECT DISTINCT
course_rel_user.status as status_rel,
user.user_id,
user.email,
course_rel_user.is_tutor,
user.* ';
}
}
$sql .= ' FROM ' . Database::get_main_table(TABLE_MAIN_USER) . ' as user ';
$sql .= ' LEFT JOIN ' . Database::get_main_table(TABLE_MAIN_COURSE_USER) . ' as course_rel_user
ON user.user_id = course_rel_user.user_id AND
course_rel_user.relation_type <> ' . COURSE_RELATION_TYPE_RRHH . ' ';
$sql .= " INNER JOIN {$course_table} course ON course_rel_user.c_id = course.id ";
if (!empty($course_code)) {
$sql .= ' AND course_rel_user.c_id="' . $courseId . '"';
}
$where[] = ' course_rel_user.c_id IS NOT NULL ';
if (isset($filter_by_status) && is_numeric($filter_by_status)) {
$filter_by_status = intval($filter_by_status);
$filter_by_status_condition = " course_rel_user.status = {$filter_by_status} AND ";
}
}
$multiple_access_url = api_get_multiple_access_url();
if ($multiple_access_url) {
$sql .= ' LEFT JOIN ' . Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER) . ' au
ON (au.user_id = user.user_id) ';
}
$extraFieldWasAdded = false;
if ($return_count && $resumed_report) {
foreach ($extra_field as $extraField) {
$extraFieldInfo = UserManager::get_extra_field_information_by_name($extraField);
if (!empty($extraFieldInfo)) {
$fieldValuesTable = Database::get_main_table(TABLE_EXTRA_FIELD_VALUES);
$sql .= ' LEFT JOIN ' . $fieldValuesTable . ' as ufv
ON (
user.user_id = ufv.item_id AND
(field_id = ' . $extraFieldInfo['id'] . ' OR field_id IS NULL)
)';
$extraFieldWasAdded = true;
}
}
}
$sql .= ' WHERE ' . $filter_by_status_condition . ' ' . implode(' OR ', $where);
if ($multiple_access_url) {
$current_access_url_id = api_get_current_access_url_id();
$sql .= " AND (access_url_id = {$current_access_url_id} ) ";
}
if ($return_count && $resumed_report && $extraFieldWasAdded) {
$sql .= ' AND field_id IS NOT NULL GROUP BY value ';
}
if (!empty($courseCodeList)) {
$courseCodeList = array_map(array('Database', 'escape_string'), $courseCodeList);
$courseCodeList = implode('","', $courseCodeList);
if (empty($sessionIdList)) {
$sql .= ' AND course.code IN ("' . $courseCodeList . '")';
}
}
if (!empty($userIdList)) {
$userIdList = array_map('intval', $userIdList);
$userIdList = implode('","', $userIdList);
$sql .= ' AND user.user_id IN ("' . $userIdList . '")';
}
if (isset($filterByActive)) {
$filterByActive = intval($filterByActive);
$sql .= ' AND user.active = ' . $filterByActive;
}
$sql .= ' ' . $order_by . ' ' . $limit;
$rs = Database::query($sql);
$users = array();
$extra_fields = UserManager::get_extra_fields(0, 100, null, null, true, true);
$counter = 1;
$count_rows = Database::num_rows($rs);
if ($return_count && $resumed_report) {
return $count_rows;
}
$table_user_field_value = Database::get_main_table(TABLE_EXTRA_FIELD_VALUES);
$tableExtraField = Database::get_main_table(TABLE_EXTRA_FIELD);
if ($count_rows) {
while ($user = Database::fetch_array($rs)) {
if ($return_count) {
return $user['count'];
}
$report_info = array();
$user_info = $user;
$user_info['status'] = $user['status'];
if (isset($user['is_tutor'])) {
$user_info['is_tutor'] = $user['is_tutor'];
}
if (!empty($session_id)) {
$user_info['status_session'] = $user['status_session'];
}
$sessionId = isset($user['session_id']) ? $user['session_id'] : 0;
$course_code = isset($user['code']) ? $user['code'] : null;
if ($add_reports) {
if ($resumed_report) {
$extra = array();
if (!empty($extra_fields)) {
foreach ($extra_fields as $extra) {
if (in_array($extra['1'], $extra_field)) {
$user_data = UserManager::get_extra_user_data_by_field($user['user_id'], $extra['1']);
break;
}
}
}
$row_key = '-1';
$name = '-';
if (!empty($extra)) {
if (!empty($user_data[$extra['1']])) {
$row_key = $user_data[$extra['1']];
$name = $user_data[$extra['1']];
$users[$row_key]['extra_' . $extra['1']] = $name;
}
}
$users[$row_key]['training_hours'] += Tracking::get_time_spent_on_the_course($user['user_id'], $courseId, $sessionId);
$users[$row_key]['count_users'] += $counter;
$registered_users_with_extra_field = 0;
if (!empty($name) && $name != '-') {
$extraFieldType = EntityExtraField::COURSE_FIELD_TYPE;
$name = Database::escape_string($name);
$sql = "SELECT count(v.item_id) as count\n FROM {$table_user_field_value} v INNER JOIN\n {$tableExtraField} f\n ON (f.id = v.field_id)\n WHERE value = '{$name}' AND extra_field_type = {$extraFieldType}";
$result_count = Database::query($sql);
if (Database::num_rows($result_count)) {
$row_count = Database::fetch_array($result_count);
$registered_users_with_extra_field = $row_count['count'];
}
}
$users[$row_key]['count_users_registered'] = $registered_users_with_extra_field;
$users[$row_key]['average_hours_per_user'] = $users[$row_key]['training_hours'] / $users[$row_key]['count_users'];
$category = Category::load(null, null, $course_code, null, null, $sessionId);
if (!isset($users[$row_key]['count_certificates'])) {
$users[$row_key]['count_certificates'] = 0;
}
if (isset($category[0]) && $category[0]->is_certificate_available($user['user_id'])) {
$users[$row_key]['count_certificates']++;
}
foreach ($extra_fields as $extra) {
if ($extra['1'] == 'ruc') {
continue;
}
if (!isset($users[$row_key][$extra['1']])) {
$user_data = UserManager::get_extra_user_data_by_field($user['user_id'], $extra['1']);
if (!empty($user_data[$extra['1']])) {
$users[$row_key][$extra['1']] = $user_data[$extra['1']];
}
}
}
} else {
$sessionName = !empty($sessionId) ? ' - ' . $user['session_name'] : '';
$report_info['course'] = $user['title'] . $sessionName;
$report_info['user'] = api_get_person_name($user['firstname'], $user['lastname']);
$report_info['email'] = $user['email'];
$report_info['time'] = api_time_to_hms(Tracking::get_time_spent_on_the_course($user['user_id'], $courseId, $sessionId));
$category = Category::load(null, null, $course_code, null, null, $sessionId);
$report_info['certificate'] = Display::label(get_lang('No'));
if (isset($category[0]) && $category[0]->is_certificate_available($user['user_id'])) {
$report_info['certificate'] = Display::label(get_lang('Yes'), 'success');
}
$progress = intval(Tracking::get_avg_student_progress($user['user_id'], $course_code, array(), $sessionId));
$report_info['progress_100'] = $progress == 100 ? Display::label(get_lang('Yes'), 'success') : Display::label(get_lang('No'));
$report_info['progress'] = $progress . "%";
foreach ($extra_fields as $extra) {
$user_data = UserManager::get_extra_user_data_by_field($user['user_id'], $extra['1']);
$report_info[$extra['1']] = $user_data[$extra['1']];
}
$report_info['user_id'] = $user['user_id'];
$users[] = $report_info;
}
} else {
$users[$user['user_id']] = $user_info;
}
}
}
return $users;
}
/**
* This function exports the table that we see in display_tracking_user_overview()
*
* @author Patrick Cool <*****@*****.**>, Ghent University, Belgium
* @version Dokeos 1.8.6
* @since October 2008
*/
public static function export_tracking_user_overview()
{
// database table definitions
$tbl_course_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
$is_western_name_order = api_is_western_name_order(PERSON_NAME_DATA_EXPORT);
$sort_by_first_name = api_sort_by_first_name();
// the values of the sortable table
if ($_GET['tracking_user_overview_page_nr']) {
$from = $_GET['tracking_user_overview_page_nr'];
} else {
$from = 0;
}
if ($_GET['tracking_user_overview_column']) {
$orderby = $_GET['tracking_user_overview_column'];
} else {
$orderby = 0;
}
if ($is_western_name_order != api_is_western_name_order() && ($orderby == 1 || $orderby == 2)) {
// Swapping the sorting column if name order for export is different than the common name order.
$orderby = 3 - $orderby;
}
if ($_GET['tracking_user_overview_direction']) {
$direction = $_GET['tracking_user_overview_direction'];
} else {
$direction = 'ASC';
}
$user_data = MySpace::get_user_data_tracking_overview($from, 1000, $orderby, $direction);
// the first line of the csv file with the column headers
$csv_row = array();
$csv_row[] = get_lang('OfficialCode');
if ($is_western_name_order) {
$csv_row[] = get_lang('FirstName', '');
$csv_row[] = get_lang('LastName', '');
} else {
$csv_row[] = get_lang('LastName', '');
$csv_row[] = get_lang('FirstName', '');
}
$csv_row[] = get_lang('LoginName');
$csv_row[] = get_lang('CourseCode');
// the additional user defined fields (only those that were selected to be exported)
$fields = UserManager::get_extra_fields(0, 50, 5, 'ASC');
if (is_array($_SESSION['additional_export_fields'])) {
foreach ($_SESSION['additional_export_fields'] as $key => $extra_field_export) {
$csv_row[] = $fields[$extra_field_export][3];
$field_names_to_be_exported[] = 'extra_' . $fields[$extra_field_export][1];
}
}
$csv_row[] = get_lang('AvgTimeSpentInTheCourse', '');
$csv_row[] = get_lang('AvgStudentsProgress', '');
$csv_row[] = get_lang('AvgCourseScore', '');
$csv_row[] = get_lang('AvgExercisesScore', '');
$csv_row[] = get_lang('AvgMessages', '');
$csv_row[] = get_lang('AvgAssignments', '');
$csv_row[] = get_lang('TotalExercisesScoreObtained', '');
$csv_row[] = get_lang('TotalExercisesScorePossible', '');
$csv_row[] = get_lang('TotalExercisesAnswered', '');
$csv_row[] = get_lang('TotalExercisesScorePercentage', '');
$csv_row[] = get_lang('FirstLogin', '');
$csv_row[] = get_lang('LatestLogin', '');
$csv_content[] = $csv_row;
// the other lines (the data)
foreach ($user_data as $key => $user) {
// getting all the courses of the user
$sql = "SELECT * FROM {$tbl_course_user}\n WHERE user_id = '" . intval($user[4]) . "' AND relation_type<>" . COURSE_RELATION_TYPE_RRHH . " ";
$result = Database::query($sql);
while ($row = Database::fetch_row($result)) {
$courseInfo = api_get_course_info($row['course_code']);
$courseId = $courseInfo['real_id'];
$csv_row = array();
// user official code
$csv_row[] = $user[0];
// user first|last name
$csv_row[] = $user[1];
// user last|first name
$csv_row[] = $user[2];
// user login name
$csv_row[] = $user[3];
// course code
$csv_row[] = $row[0];
// the additional defined user fields
$extra_fields = MySpace::get_user_overview_export_extra_fields($user[4]);
if (is_array($field_names_to_be_exported)) {
foreach ($field_names_to_be_exported as $key => $extra_field_export) {
$csv_row[] = $extra_fields[$extra_field_export];
}
}
// time spent in the course
$csv_row[] = api_time_to_hms(Tracking::get_time_spent_on_the_course($user[4], $courseId));
// student progress in course
$csv_row[] = round(Tracking::get_avg_student_progress($user[4], $row[0]), 2);
// student score
$csv_row[] = round(Tracking::get_avg_student_score($user[4], $row[0]), 2);
// student tes score
$csv_row[] = round(Tracking::get_avg_student_exercise_score($user[4], $row[0]), 2);
// student messages
$csv_row[] = Tracking::count_student_messages($user[4], $row[0]);
// student assignments
$csv_row[] = Tracking::count_student_assignments($user[4], $row[0]);
// student exercises results
$exercises_results = MySpace::exercises_results($user[4], $row[0]);
$csv_row[] = $exercises_results['score_obtained'];
$csv_row[] = $exercises_results['score_possible'];
$csv_row[] = $exercises_results['questions_answered'];
$csv_row[] = $exercises_results['percentage'];
// first connection
$csv_row[] = Tracking::get_first_connection_date_on_the_course($user[4], $courseId);
// last connection
$csv_row[] = strip_tags(Tracking::get_last_connection_date_on_the_course($user[4], $courseInfo));
$csv_content[] = $csv_row;
}
}
Export::arrayToCsv($csv_content, 'reporting_user_overview');
exit;
}
function get_extra_fields($f, $n, $o, $d)
{
return UserManager::get_extra_fields($f, $n, $o, $d);
}
/**
* Return user info array of all users registered in the specified course
* This only returns the users that are registered in this actual course, not linked courses.
*
* @param string $course_code the code of the course
* @param boolean $with_session determines if the course is used in a session or not
* @param integer $session_id the id of the session
* @param string $limit the LIMIT statement of the sql statement
* @param string $order_by the field to order the users by. Valid values are 'lastname', 'firstname', 'username', 'email', 'official_code' OR a part of a SQL statement that starts with ORDER BY ...
* @param int if using the session_id: 0 or 2 (student, coach), if using session_id = 0 STUDENT or COURSEMANAGER
* @return array
*/
public static function get_user_list_from_course_code($course_code = null, $session_id = 0, $limit = null, $order_by = null, $filter_by_status = null, $return_count = null, $add_reports = false, $resumed_report = false, $extra_field = null)
{
// variable initialisation
$session_id = intval($session_id);
$course_code = Database::escape_string($course_code);
$where = array();
// if the $order_by does not contain 'ORDER BY' we have to check if it is a valid field that can be sorted on
if (!strstr($order_by, 'ORDER BY')) {
//if (!empty($order_by) AND in_array($order_by, array('lastname', 'firstname', 'username', 'email', 'official_code'))) {
if (!empty($order_by)) {
$order_by = 'ORDER BY ' . $order_by;
} else {
$order_by = '';
}
}
$courseInfo = api_get_course_info($course_code);
$courseId = null;
if ($courseInfo) {
$courseId = $courseInfo['real_id'];
}
$filter_by_status_condition = null;
if (!empty($session_id) && !empty($courseId)) {
$sql = 'SELECT DISTINCT user.user_id, session_course_user.status as status_session, user.* ';
$sql .= ' FROM ' . Database::get_main_table(TABLE_MAIN_USER) . ' as user ';
$sql .= ' LEFT JOIN ' . Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER) . ' as session_course_user
ON user.user_id = session_course_user.id_user
AND session_course_user.c_id="' . $courseId . '"
AND session_course_user.id_session = ' . $session_id;
$where[] = ' session_course_user.c_id IS NOT NULL ';
// 2 = coach
// 0 = student
if (isset($filter_by_status)) {
$filter_by_status = intval($filter_by_status);
$filter_by_status_condition = " session_course_user.status = {$filter_by_status} AND ";
}
} else {
if ($return_count) {
$sql = " SELECT COUNT(*) as count";
if ($resumed_report) {
//$sql = " SELECT count(field_id) ";
}
} else {
if (empty($course_code)) {
$sql = 'SELECT DISTINCT course.title, course.code, course_rel_user.status as status_rel, user.user_id, course_rel_user.role, course_rel_user.tutor_id, user.* ';
} else {
$sql = 'SELECT DISTINCT course_rel_user.status as status_rel, user.user_id, course_rel_user.role, course_rel_user.tutor_id, user.* ';
}
}
$sql .= ' FROM ' . Database::get_main_table(TABLE_MAIN_USER) . ' as user ';
$sql .= ' LEFT JOIN ' . Database::get_main_table(TABLE_MAIN_COURSE_USER) . ' as course_rel_user
ON user.user_id = course_rel_user.user_id AND
course_rel_user.relation_type <> ' . COURSE_RELATION_TYPE_RRHH;
if (!empty($courseInfo)) {
$sql .= " AND course_rel_user.c_id = " . $courseId;
} else {
$course_table = Database::get_main_table(TABLE_MAIN_COURSE);
$sql .= " INNER JOIN {$course_table} course ON course_rel_user.c_id = course.id";
}
$where[] = ' course_rel_user.c_id IS NOT NULL ';
if (isset($filter_by_status) && $filter_by_status != '') {
$filter_by_status = intval($filter_by_status);
$filter_by_status_condition = " course_rel_user.status = {$filter_by_status} AND ";
}
}
$multiple_access_url = api_get_multiple_access_url();
if ($multiple_access_url) {
$sql .= ' LEFT JOIN ' . Database::get_main_table(TABLE_MAIN_ACCESS_URL_REL_USER) . ' au ON (au.user_id = user.user_id) ';
}
if ($return_count && $resumed_report) {
$extra_field_info = UserManager::get_extra_field_information_by_name($extra_field);
$sql .= ' LEFT JOIN ' . Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES) . ' as ufv ON (user.user_id = ufv.user_id AND (field_id = ' . $extra_field_info['id'] . ' OR field_id IS NULL ) )';
}
$sql .= ' WHERE ' . $filter_by_status_condition . ' ' . implode(' OR ', $where);
if ($multiple_access_url) {
$current_access_url_id = api_get_current_access_url_id();
$sql .= " AND (access_url_id = {$current_access_url_id} ) ";
}
if ($return_count && $resumed_report) {
$sql .= ' AND field_id IS NOT NULL GROUP BY field_value ';
}
$sql .= ' ' . $order_by . ' ' . $limit;
$rs = Database::query($sql);
$users = array();
if ($add_reports) {
$extra_fields = UserManager::get_extra_fields(0, 100, null, null, true, true);
}
$counter = 1;
$count_rows = Database::num_rows($rs);
if ($return_count && $resumed_report) {
return $count_rows;
}
$table_user_field_value = Database::get_main_table(TABLE_MAIN_USER_FIELD_VALUES);
if ($count_rows) {
while ($user = Database::fetch_array($rs)) {
$report_info = array();
if ($return_count) {
return $user['count'];
}
$user_info = $user;
$user_info['status'] = $user['status'];
if (isset($user['role'])) {
$user_info['role'] = $user['role'];
}
if (isset($user['tutor_id'])) {
$user_info['tutor_id'] = $user['tutor_id'];
}
if (!empty($session_id)) {
$user_info['status_session'] = $user['status_session'];
}
$user_info['complete_name'] = api_get_person_name($user_info['firstname'], $user_info['lastname']);
if ($add_reports) {
$course_code = $user['code'];
if ($resumed_report) {
foreach ($extra_fields as $extra) {
if ($extra['1'] == $extra_field) {
$user_data = UserManager::get_extra_user_data_by_field($user['user_id'], $extra['1']);
break;
}
}
if (empty($user_data[$extra['1']])) {
$row_key = '-1';
$name = '-';
} else {
$row_key = $user_data[$extra['1']];
$name = $user_data[$extra['1']];
}
$users[$row_key]['extra_' . $extra['1']] = $name;
$users[$row_key]['training_hours'] += Tracking::get_time_spent_on_the_course($user['user_id'], $courseId, 0);
$users[$row_key]['count_users'] += $counter;
$registered_users_with_extra_field = 0;
if (!empty($name) && $name != '-') {
$name = Database::escape_string($name);
$sql = "SELECT count(user_id) as count FROM {$table_user_field_value} WHERE field_value = '{$name}'";
$result_count = Database::query($sql);
if (Database::num_rows($result_count)) {
$row_count = Database::fetch_array($result_count);
$registered_users_with_extra_field = $row_count['count'];
}
}
$users[$row_key]['count_users_registered'] = $registered_users_with_extra_field;
$users[$row_key]['average_hours_per_user'] = $users[$row_key]['training_hours'] / $users[$row_key]['count_users'];
$category = Category::load(null, null, $course_code);
if (!isset($users[$row_key]['count_certificates'])) {
$users[$row_key]['count_certificates'] = 0;
}
if (isset($category[0]) && $category[0]->is_certificate_available($user['user_id'])) {
$users[$row_key]['count_certificates']++;
}
} else {
$report_info['course'] = $user['title'];
$report_info['user'] = api_get_person_name($user['firstname'], $user['lastname']);
$report_info['time'] = api_time_to_hms(Tracking::get_time_spent_on_the_course($user['user_id'], $courseId, 0));
$category = Category::load(null, null, $course_code);
$report_info['certificate'] = Display::label(get_lang('No'));
if (isset($category[0]) && $category[0]->is_certificate_available($user['user_id'])) {
$report_info['certificate'] = Display::label(get_lang('Yes'), 'success');
}
//$report_info['score'] = Tracking::get_avg_student_score($user['user_id'], $courseId, array(), 0);
$progress = intval(Tracking::get_avg_student_progress($user['user_id'], $courseId, array(), 0));
$report_info['progress_100'] = $progress == 100 ? Display::label(get_lang('Yes'), 'success') : Display::label(get_lang('No'));
$report_info['progress'] = $progress . "%";
foreach ($extra_fields as $extra) {
$user_data = UserManager::get_extra_user_data_by_field($user['user_id'], $extra['1']);
$report_info[$extra['1']] = $user_data[$extra['1']];
}
$users[] = $report_info;
}
} else {
$users[$user['user_id']] = $user_info;
}
}
$counter++;
}
return $users;
}
/**
* @param Application $app
* @return string
*/
public function indexAction(Application $app)
{
$request = $app['request'];
$language_file = array('admin', 'exercice', 'gradebook', 'tracking');
// 1. Setting variables needed by jqgrid
$action = $request->get('a');
$page = $request->get('page');
//page
$limit = $request->get('rows');
//quantity of rows
$sidx = $request->get('sidx');
//index (field) to filter
$sord = $request->get('sord');
//asc or desc
if (strpos(strtolower($sidx), 'asc') !== false) {
$sidx = str_replace(array('asc', ','), '', $sidx);
$sord = 'asc';
}
if (strpos(strtolower($sidx), 'desc') !== false) {
$sidx = str_replace(array('desc', ','), '', $sidx);
$sord = 'desc';
}
if (!in_array($sord, array('asc', 'desc'))) {
$sord = 'desc';
}
if (!in_array($action, array('get_exercise_results', 'get_hotpotatoes_exercise_results', 'get_work_user_list', 'get_timelines', 'get_user_skill_ranking', 'get_usergroups_teacher', 'get_question_list', 'get_user_list_plugin_widescale'))) {
api_protect_admin_script(true);
}
if ($action == 'get_user_list_plugin_widescale') {
$allowed = api_is_drh() || api_is_platform_admin();
if (!$allowed) {
api_not_allowed();
}
}
// Search features.
// If there is no search request sent by jqgrid, $where should be empty.
$where_condition = "";
$operation = $request->get('oper');
$export_format = $request->get('export_format');
$search_field = $request->get('searchField');
$search_oper = $request->get('searchOper');
$search_string = $request->get('searchString');
$isSearch = $request->get('_search');
$filters = $request->get('filters');
$type = $request->get('type');
$extra_fields = array();
$questionFields = array();
if ($isSearch == 'true') {
$where_condition = ' 1 = 1 ';
$where_condition_in_form = $this->getWhereClause($search_field, $search_oper, $search_string);
if (!empty($where_condition_in_form)) {
$where_condition .= ' AND ' . $where_condition_in_form;
}
$filters = isset($filters) ? json_decode($filters) : false;
// for now
if (!empty($filters)) {
switch ($action) {
case 'get_questions':
$extraFieldtype = 'question';
break;
case 'get_sessions':
$extraFieldtype = 'session';
break;
}
// Extra field.
$extraField = new \ExtraField($extraFieldtype);
$result = $extraField->getExtraFieldRules($filters, 'extra_');
$extra_fields = $result['extra_fields'];
$condition_array = $result['condition_array'];
if (!empty($condition_array)) {
$where_condition .= ' AND ( ';
$where_condition .= implode($filters->groupOp, $condition_array);
$where_condition .= ' ) ';
}
// Question field.
$resultQuestion = $extraField->getExtraFieldRules($filters, 'question_');
$questionFields = $resultQuestion['extra_fields'];
$condition_array = $resultQuestion['condition_array'];
if (!empty($condition_array)) {
$where_condition .= ' AND ( ';
$where_condition .= implode($filters->groupOp, $condition_array);
$where_condition .= ' ) ';
}
}
}
// get index row - i.e. user click to sort $sord = $_GET['sord'];
// get the direction
if (!$sidx) {
$sidx = 1;
}
//2. Selecting the count FIRST
//@todo rework this
switch ($action) {
case 'get_questions':
$categoryId = $request->get('categoryId');
$exerciseId = $request->get('exerciseId');
//$courseId = null; //$request->get('courseId');
$courseId = $request->get('courseId');
// Question manager can view all questions
if (api_is_question_manager()) {
$courseId = null;
}
$count = \Question::getQuestions($app, $categoryId, $exerciseId, $courseId, array('where' => $where_condition, 'extra' => $extra_fields, 'question' => $questionFields), true);
break;
case 'get_user_list_plugin_widescale':
$count = \UserManager::get_user_data(null, null, null, null, true);
break;
case 'get_question_list':
require_once api_get_path(SYS_CODE_PATH) . 'exercice/exercise.class.php';
$exerciseId = $request->get('exerciseId');
$exercise = new \Exercise(api_get_course_int_id());
$exercise->read($exerciseId);
$count = $exercise->selectNbrQuestions();
break;
case 'get_group_reporting':
$course_id = $request->get('course_id');
$group_id = $request->get('gidReq');
$count = \Tracking::get_group_reporting($course_id, $group_id, 'count');
break;
case 'get_user_course_report_resumed':
$count = \CourseManager::get_count_user_list_from_course_code(true, 'ruc');
break;
case 'get_user_course_report':
$count = \CourseManager::get_count_user_list_from_course_code(false);
break;
case 'get_course_exercise_medias':
$course_id = api_get_course_int_id();
$count = \Question::get_count_course_medias($course_id);
break;
case 'get_user_skill_ranking':
$skill = new \Skill();
$count = $skill->get_user_list_skill_ranking_count();
break;
case 'get_work_user_list':
require_once api_get_path(SYS_CODE_PATH) . 'work/work.lib.php';
$work_id = $request->get('work_id');
//$_REQUEST['work_id'];
$count = get_count_work($work_id);
break;
case 'get_exercise_results':
$exercise_id = $request->get('exerciseId');
//$_REQUEST['exerciseId'];
$filter_by_user = $request->get('filter_by_user');
if (isset($filter_by_user) && !empty($filter_by_user)) {
$filter_user = intval($filter_by_user);
if ($where_condition == "") {
$where_condition .= " te.exe_user_id = '{$filter_user}'";
} else {
$where_condition .= " AND te.exe_user_id = '{$filter_user}'";
}
}
$count = \ExerciseLib::get_count_exam_results($exercise_id, $where_condition);
break;
case 'get_hotpotatoes_exercise_results':
$hotpot_path = $request->get('path');
//$_REQUEST['path'];
$count = \ExerciseLib::get_count_exam_hotpotatoes_results($hotpot_path);
break;
case 'get_sessions':
$list_type = $request->get('list_type');
if ($list_type == 'simple' || empty($list_type)) {
$count = \SessionManager::get_sessions_admin(array('where' => $where_condition, 'extra' => $extra_fields), true);
} else {
$count = \SessionManager::get_count_admin_complete(array('where' => $where_condition, 'extra' => $extra_fields));
}
break;
case 'get_extra_fields':
$obj = new \ExtraField($type);
$count = $obj->get_count();
break;
case 'get_extra_field_options':
$field_id = $request->get('field_id');
$obj = new \ExtraFieldOption($type);
$count = $obj->get_count_by_field_id($field_id);
break;
case 'get_timelines':
$obj = new \Timeline();
$count = $obj->get_count();
break;
case 'get_gradebooks':
$obj = new \Gradebook();
$count = $obj->get_count();
break;
case 'get_event_email_template':
$obj = new \EventEmailTemplate();
$count = $obj->get_count();
break;
case 'get_careers':
$obj = new \Career();
$count = $obj->get_count();
break;
case 'get_promotions':
$obj = new \Promotion();
$count = $obj->get_count();
break;
case 'get_grade_models':
$obj = new \GradeModel();
$count = $obj->get_count();
break;
case 'get_usergroups':
$obj = new \UserGroup();
$count = $obj->get_count();
break;
case 'get_usergroups_teacher':
$obj = new \UserGroup();
$course_id = api_get_course_int_id();
if ($type == 'registered') {
$count = $obj->get_usergroup_by_course_with_data_count($course_id);
} else {
$count = $obj->get_count();
}
break;
default:
exit;
}
//3. Calculating first, end, etc
$total_pages = 0;
if ((int) $count > 0) {
if (!empty($limit)) {
$total_pages = ceil($count / $limit);
}
}
if ($page > $total_pages) {
$page = $total_pages;
}
$start = $limit * $page - $limit;
if ($start < 0) {
$start = 0;
}
//4. Deleting an element if the user wants to
if ($operation == 'del') {
$obj->delete($request->get('id'));
}
$is_allowedToEdit = api_is_allowed_to_edit(null, true) || api_is_allowed_to_edit(true) || api_is_drh();
//5. Querying the DB for the elements
$columns = array();
switch ($action) {
case 'get_questions':
$columns = \Question::getQuestionColumns(api_get_course_id(), $extra_fields, $questionFields, true);
$columns = $columns['simple_column_name'];
$result = \Question::getQuestions($app, $categoryId, $exerciseId, $courseId, array('where' => $where_condition, 'order' => "{$sidx} {$sord}", 'extra' => $extra_fields, 'question' => $questionFields, 'limit' => "{$start} , {$limit}"));
//var_dump($result);
break;
case 'get_user_list_plugin_widescale':
$columns = array('username', 'firstname', 'lastname', 'exam_password');
$column_names = array(get_lang('Username'), get_lang('Firstname'), get_lang('Lastname'), get_lang('Password'));
$result = \UserManager::get_user_data($start, $limit, $sidx, $sord);
break;
case 'get_question_list':
if (isset($exercise) && !empty($exercise)) {
$columns = array('question', 'type', 'category', 'level', 'score', 'actions');
$result = $exercise->getQuestionListPagination($start, $limit, $sidx, $sord, $where_condition);
}
break;
case 'get_group_reporting':
$columns = array('name', 'time', 'progress', 'score', 'works', 'messages', 'actions');
$result = \Tracking::get_group_reporting($course_id, $group_id, 'all', $start, $limit, $sidx, $sord, $where_condition);
break;
case 'get_course_exercise_medias':
$columns = array('question');
$result = \Question::get_course_medias($course_id, $start, $limit, $sidx, $sord, $where_condition);
if (!empty($result)) {
foreach ($result as &$media) {
$media['id'] = $media['iid'];
}
}
break;
case 'get_user_course_report_resumed':
$columns = array('extra_ruc', 'training_hours', 'count_users', 'count_users_registered', 'average_hours_per_user', 'count_certificates');
$column_names = array(get_lang('Company'), get_lang('TrainingHoursAccumulated'), get_lang('CountOfSubscriptions'), get_lang('CountOfUsers'), get_lang('AverageHoursPerStudent'), get_lang('CountCertificates'));
$result = \CourseManager::get_user_list_from_course_code(null, null, "LIMIT {$start}, {$limit}", " {$sidx} {$sord}", null, null, true, true, 'ruc');
$new_result = array();
if (!empty($result)) {
foreach ($result as $row) {
$row['training_hours'] = api_time_to_hms($row['training_hours']);
$row['average_hours_per_user'] = api_time_to_hms($row['average_hours_per_user']);
$new_result[] = $row;
}
$result = $new_result;
}
break;
case 'get_user_course_report':
$columns = array('course', 'user', 'time', 'certificate', 'progress_100', 'progress');
$column_names = array(get_lang('Course'), get_lang('User'), get_lang('ManHours'), get_lang('CertificateGenerated'), get_lang('Approved'), get_lang('CourseAdvance'));
$extra_fields = \UserManager::get_extra_fields(0, 100, null, null, true, true);
if (!empty($extra_fields)) {
foreach ($extra_fields as $extra) {
$columns[] = $extra['1'];
$column_names[] = $extra['3'];
}
}
$result = \CourseManager::get_user_list_from_course_code(null, null, "LIMIT {$start}, {$limit}", " {$sidx} {$sord}", null, null, true);
break;
case 'get_user_skill_ranking':
$columns = array('photo', 'firstname', 'lastname', 'skills_acquired', 'currently_learning', 'rank');
$result = $skill->get_user_list_skill_ranking($start, $limit, $sidx, $sord, $where_condition);
$result = \ArrayClass::msort($result, 'skills_acquired', 'asc');
$skills_in_course = array();
if (!empty($result)) {
//$counter = 1;
foreach ($result as &$item) {
$user_info = api_get_user_info($item['user_id']);
$personal_course_list = \UserManager::get_personal_session_course_list($item['user_id']);
$count_skill_by_course = array();
foreach ($personal_course_list as $course_item) {
if (!isset($skills_in_course[$course_item['code']])) {
$count_skill_by_course[$course_item['code']] = $skill->get_count_skills_by_course($course_item['code']);
$skills_in_course[$course_item['code']] = $count_skill_by_course[$course_item['code']];
} else {
$count_skill_by_course[$course_item['code']] = $skills_in_course[$course_item['code']];
}
}
$item['photo'] = \Display::img($user_info['avatar_small']);
$item['currently_learning'] = !empty($count_skill_by_course) ? array_sum($count_skill_by_course) : 0;
}
}
break;
case 'get_work_user_list':
if (isset($type) && $type == 'simple') {
$columns = array('type', 'firstname', 'lastname', 'username', 'title', 'qualification', 'sent_date', 'qualificator_id', 'actions');
} else {
$columns = array('type', 'firstname', 'lastname', 'username', 'title', 'sent_date', 'actions');
}
$result = get_work_user_list($start, $limit, $sidx, $sord, $work_id, $where_condition);
break;
case 'get_exercise_results':
$course = api_get_course_info();
//used inside get_exam_results_data()
$documentPath = api_get_path(SYS_COURSE_PATH) . $course['path'] . "/document";
if ($is_allowedToEdit) {
$columns = array('firstname', 'lastname', 'username', 'group_name', 'exe_duration', 'start_date', 'exe_date', 'score', 'status', 'lp', 'actions');
} else {
//$columns = array('exe_duration', 'start_date', 'exe_date', 'score', 'status', 'actions');
}
$result = \ExerciseLib::get_exam_results_data($start, $limit, $sidx, $sord, $exercise_id, $where_condition);
break;
case 'get_hotpotatoes_exercise_results':
$course = api_get_course_info();
//used inside get_exam_results_data()
$documentPath = api_get_path(SYS_COURSE_PATH) . $course['path'] . "/document";
$columns = array('firstname', 'lastname', 'username', 'group_name', 'exe_date', 'score', 'actions');
$result = ExerciseLib::get_exam_results_hotpotatoes_data($start, $limit, $sidx, $sord, $hotpot_path, $where_condition);
//get_exam_results_data($start, $limit, $sidx, $sord, $exercise_id, $where_condition);
break;
case 'get_sessions':
$session_columns = \SessionManager::get_session_columns($list_type);
$columns = $session_columns['simple_column_name'];
if ($list_type == 'simple') {
$result = SessionManager::get_sessions_admin(array('where' => $where_condition, 'order' => "{$sidx} {$sord}", 'extra' => $extra_fields, 'limit' => "{$start} , {$limit}"), false);
} else {
$result = SessionManager::get_sessions_admin_complete(array('where' => $where_condition, 'order' => "{$sidx} {$sord}", 'extra' => $extra_fields, 'limit' => "{$start} , {$limit}"));
}
break;
case 'get_timelines':
$columns = array('headline', 'actions');
//$columns = array('headline', 'type', 'start_date', 'end_date', 'text', 'media', 'media_credit', 'media_caption', 'title_slide', 'parent_id');
if (!in_array($sidx, $columns)) {
$sidx = 'headline';
}
$course_id = api_get_course_int_id();
$result = Database::select('*', $obj->table, array('where' => array('parent_id = ? AND c_id = ?' => array('0', $course_id)), 'order' => "{$sidx} {$sord}", 'LIMIT' => "{$start} , {$limit}"));
$new_result = array();
foreach ($result as $item) {
if (!$item['status']) {
$item['name'] = '<font style="color:#AAA">' . $item['name'] . '</font>';
}
$item['headline'] = Display::url($item['headline'], api_get_path(WEB_CODE_PATH) . 'timeline/view.php?id=' . $item['id']);
$item['actions'] = Display::url(Display::return_icon('add.png', get_lang('AddItems')), api_get_path(WEB_CODE_PATH) . 'timeline/?action=add_item&parent_id=' . $item['id']);
$item['actions'] .= Display::url(Display::return_icon('edit.png', get_lang('Edit')), api_get_path(WEB_CODE_PATH) . 'timeline/?action=edit&id=' . $item['id']);
$item['actions'] .= Display::url(Display::return_icon('delete.png', get_lang('Delete')), api_get_path(WEB_CODE_PATH) . 'timeline/?action=delete&id=' . $item['id']);
$new_result[] = $item;
}
$result = $new_result;
break;
case 'get_gradebooks':
$columns = array('name', 'certificates', 'skills', 'actions', 'has_certificates');
if (!in_array($sidx, $columns)) {
$sidx = 'name';
}
$result = Database::select('*', $obj->table, array('order' => "{$sidx} {$sord}", 'LIMIT' => "{$start} , {$limit}"));
$new_result = array();
foreach ($result as $item) {
if ($item['parent_id'] != 0) {
continue;
}
$skills = $obj->get_skills_by_gradebook($item['id']);
//Fixes bug when gradebook doesn't have names
if (empty($item['name'])) {
$item['name'] = $item['course_code'];
} else {
//$item['name'] = $item['name'].' ['.$item['course_code'].']';
}
$item['name'] = Display::url($item['name'], api_get_path(WEB_CODE_PATH) . 'gradebook/index.php?id_session=0&cidReq=' . $item['course_code']);
if (!empty($item['certif_min_score']) && !empty($item['document_id'])) {
$item['certificates'] = Display::return_icon('accept.png', get_lang('WithCertificate'), array(), ICON_SIZE_SMALL);
$item['has_certificates'] = '1';
} else {
$item['certificates'] = Display::return_icon('warning.png', get_lang('NoCertificate'), array(), ICON_SIZE_SMALL);
$item['has_certificates'] = '0';
}
if (!empty($skills)) {
foreach ($skills as $skill) {
$item['skills'] .= Display::span($skill['name'], array('class' => 'label_tag skill'));
}
}
$new_result[] = $item;
}
$result = $new_result;
break;
case 'get_event_email_template':
$columns = array('subject', 'event_type_name', 'language_id', 'activated', 'actions');
if (!in_array($sidx, $columns)) {
$sidx = 'subject';
}
$result = Database::select('*', $obj->table, array('order' => "{$sidx} {$sord}", 'LIMIT' => "{$start} , {$limit}"));
$new_result = array();
foreach ($result as $item) {
$language_info = api_get_language_info($item['language_id']);
$item['language_id'] = $language_info['english_name'];
$item['actions'] = Display::url(Display::return_icon('edit.png', get_lang('Edit')), api_get_path(WEB_CODE_PATH) . 'admin/event_type.php?action=edit&event_type_name=' . $item['event_type_name']);
$item['actions'] .= Display::url(Display::return_icon('delete.png', get_lang('Delete')), api_get_path(WEB_CODE_PATH) . 'admin/event_controller.php?action=delete&id=' . $item['id']);
/*if (!$item['status']) {
$item['name'] = '<font style="color:#AAA">'.$item['subject'].'</font>';
}*/
$new_result[] = $item;
}
$result = $new_result;
break;
case 'get_careers':
$columns = array('name', 'description', 'actions');
if (!in_array($sidx, $columns)) {
$sidx = 'name';
}
$result = Database::select('*', $obj->table, array('order' => "{$sidx} {$sord}", 'LIMIT' => "{$start} , {$limit}"));
$new_result = array();
foreach ($result as $item) {
if (!$item['status']) {
$item['name'] = '<font style="color:#AAA">' . $item['name'] . '</font>';
}
$new_result[] = $item;
}
$result = $new_result;
break;
case 'get_promotions':
$columns = array('name', 'career', 'description', 'actions');
if (!in_array($sidx, $columns)) {
$sidx = 'name';
}
$result = Database::select('p.id,p.name, p.description, c.name as career, p.status', "{$obj->table} p LEFT JOIN " . Database::get_main_table(TABLE_CAREER) . " c ON c.id = p.career_id ", array('order' => "{$sidx} {$sord}", 'LIMIT' => "{$start} , {$limit}"));
$new_result = array();
foreach ($result as $item) {
if (!$item['status']) {
$item['name'] = '<font style="color:#AAA">' . $item['name'] . '</font>';
}
$new_result[] = $item;
}
$result = $new_result;
break;
case 'get_grade_models':
$columns = array('name', 'description', 'actions');
if (!in_array($sidx, $columns)) {
$sidx = 'name';
}
$result = Database::select('*', "{$obj->table} ", array('order' => "{$sidx} {$sord}", 'LIMIT' => "{$start} , {$limit}"));
$new_result = array();
foreach ($result as $item) {
$new_result[] = $item;
}
$result = $new_result;
break;
case 'get_usergroups':
$columns = array('name', 'users', 'courses', 'sessions', 'group_type', 'actions');
$result = Database::select('*', $obj->table, array('order' => "name {$sord}", 'LIMIT' => "{$start} , {$limit}"));
$new_result = array();
if (!empty($result)) {
foreach ($result as $group) {
$group['sessions'] = count($obj->get_sessions_by_usergroup($group['id']));
$group['courses'] = count($obj->get_courses_by_usergroup($group['id']));
$group['users'] = count($obj->get_users_by_usergroup($group['id']));
switch ($group['group_type']) {
case '0':
$group['group_type'] = Display::label(get_lang('Class'), 'info');
break;
case '1':
$group['group_type'] = Display::label(get_lang('Social'), 'success');
break;
}
$new_result[] = $group;
}
$result = $new_result;
}
$columns = array('name', 'users', 'courses', 'sessions', 'group_type');
if (!in_array($sidx, $columns)) {
$sidx = 'name';
}
//Multidimensional sort
ArrayClass::msort($result, $sidx);
break;
case 'get_extra_fields':
$obj = new \ExtraField($type);
$columns = array('field_display_text', 'field_variable', 'field_type', 'field_changeable', 'field_visible', 'field_filter', 'field_order');
$result = \Database::select('*', $obj->table, array('order' => "{$sidx} {$sord}", 'LIMIT' => "{$start} , {$limit}"));
$new_result = array();
if (!empty($result)) {
foreach ($result as $item) {
$item['field_type'] = $obj->get_field_type_by_id($item['field_type']);
$item['field_changeable'] = $item['field_changeable'] ? Display::return_icon('right.gif') : Display::return_icon('wrong.gif');
$item['field_visible'] = $item['field_visible'] ? Display::return_icon('right.gif') : Display::return_icon('wrong.gif');
$item['field_filter'] = $item['field_filter'] ? Display::return_icon('right.gif') : Display::return_icon('wrong.gif');
$new_result[] = $item;
}
$result = $new_result;
}
break;
case 'get_extra_field_options':
$obj = new \ExtraFieldOption($type);
$columns = array('option_display_text', 'option_value', 'option_order');
$result = \Database::select('*', $obj->table, array('where' => array("field_id = ? " => $field_id), 'order' => "{$sidx} {$sord}", 'LIMIT' => "{$start} , {$limit}"));
/*$new_result = array();
if (!empty($result)) {
foreach ($result as $item) {
$item['field_type'] = $obj->get_field_type_by_id($item['field_type']);
$item['field_changeable'] = $item['field_changeable'] ? Display::return_icon('right.gif') : Display::return_icon('wrong.gif');
$item['field_visible'] = $item['field_visible'] ? Display::return_icon('right.gif') : Display::return_icon('wrong.gif');
$item['field_filter'] = $item['field_filter'] ? Display::return_icon('right.gif') : Display::return_icon('wrong.gif');
$new_result[] = $item;
}
$result = $new_result;
}*/
break;
case 'get_usergroups_teacher':
$columns = array('name', 'users', 'actions');
$options = array('order' => "name {$sord}", 'LIMIT' => "{$start} , {$limit}");
$options['course_id'] = $course_id;
switch ($type) {
case 'not_registered':
$options['where'] = array(" (course_id IS NULL OR course_id != ?) " => $course_id);
$result = $obj->get_usergroup_not_in_course($options);
break;
case 'registered':
$options['where'] = array(" usergroup.course_id = ? " => $course_id);
$result = $obj->get_usergroup_in_course($options);
break;
}
$new_result = array();
if (!empty($result)) {
foreach ($result as $group) {
$group['users'] = count($obj->get_users_by_usergroup($group['id']));
if ($obj->usergroup_was_added_in_course($group['id'], $course_id)) {
$url = 'class.php?action=remove_class_from_course&id=' . $group['id'];
$icon = Display::return_icon('delete.png', get_lang('Remove'));
} else {
$url = 'class.php?action=add_class_to_course&id=' . $group['id'];
$icon = Display::return_icon('add.png', get_lang('Add'));
}
$group['actions'] = Display::url($icon, $url);
$new_result[] = $group;
}
$result = $new_result;
}
if (!in_array($sidx, $columns)) {
$sidx = 'name';
}
//Multidimensional sort
\ArrayClass::msort($result, $sidx);
break;
default:
exit;
}
$allowed_actions = array('get_careers', 'get_promotions', 'get_usergroups', 'get_usergroups_teacher', 'get_gradebooks', 'get_sessions', 'get_exercise_results', 'get_hotpotatoes_exercise_results', 'get_work_user_list', 'get_timelines', 'get_grade_models', 'get_event_email_template', 'get_user_skill_ranking', 'get_extra_fields', 'get_extra_field_options', 'get_course_exercise_medias', 'get_user_course_report', 'get_user_course_report_resumed', 'get_group_reporting', 'get_question_list', 'get_user_list_plugin_widescale', 'get_questions');
//5. Creating an obj to return a json
if (in_array($action, $allowed_actions)) {
$response = new \stdClass();
$response->page = $page;
$response->total = $total_pages;
$response->records = $count;
if ($operation && $operation == 'excel') {
$j = 1;
$array = array();
if (empty($column_names)) {
$column_names = $columns;
}
//Headers
foreach ($column_names as $col) {
$array[0][] = $col;
}
foreach ($result as $row) {
foreach ($columns as $col) {
$array[$j][] = strip_tags($row[$col]);
}
$j++;
}
switch ($export_format) {
case 'xls':
Export::export_table_xls($array, 'company_report');
break;
case 'csv':
default:
Export::export_table_csv($array, 'company_report');
break;
}
exit;
}
$i = 0;
if (!empty($result)) {
foreach ($result as $row) {
//print_r($row);
// if results tab give not id, set id to $i otherwise id="null" for all <tr> of the jqgrid - ref #4235
if (!isset($row['id']) || isset($row['id']) && $row['id'] == "") {
$response->rows[$i]['id'] = $i;
} else {
$response->rows[$i]['id'] = $row['id'];
}
$array = array();
foreach ($columns as $col) {
$array[] = isset($row[$col]) ? $row[$col] : null;
}
$response->rows[$i]['cell'] = $array;
$i++;
}
}
return json_encode($response);
}
}
/**
* 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;
}
xml_set_character_data_handler($parser, 'character_data');
xml_parser_set_option($parser, XML_OPTION_CASE_FOLDING, false);
xml_parse($parser, api_utf8_encode_xml(file_get_contents($file)));
xml_parser_free($parser);
return $users;
}
$this_section = SECTION_PLATFORM_ADMIN;
api_protect_admin_script(true, null, 'login');
$defined_auth_sources[] = PLATFORM_AUTH_SOURCE;
if (isset($extAuthSource) && is_array($extAuthSource)) {
$defined_auth_sources = array_merge($defined_auth_sources, array_keys($extAuthSource));
}
$tool_name = get_lang('ImportUserListXMLCSV');
$interbreadcrumb[] = array('url' => Container::getRouter()->generate('administration'), "name" => get_lang('PlatformAdmin'));
set_time_limit(0);
$extra_fields = UserManager::get_extra_fields(0, 0, 5, 'ASC', true);
$user_id_error = array();
$error_message = '';
if (isset($_POST['formSent']) && $_POST['formSent'] and $_FILES['import_file']['size'] !== 0) {
$file_type = 'csv';
Security::clear_token();
$tok = Security::get_token();
$allowed_file_mimetype = array('csv', 'xml');
$error_kind_file = false;
$uploadInfo = pathinfo($_FILES['import_file']['name']);
$ext_import_file = $uploadInfo['extension'];
if (in_array($ext_import_file, $allowed_file_mimetype)) {
if (strcmp($file_type, 'csv') === 0 && $ext_import_file == $allowed_file_mimetype[0]) {
$users = parse_csv_data($_FILES['import_file']['tmp_name']);
$errors = validate_data($users);
$error_kind_file = false;
/**
* Creates a multi array with the user fields that we can show. We look the visibility with the api_get_setting function
* The username is always NOT able to change it.
* @author Julio Montoya Armas <*****@*****.**>, Chamilo: Personality Test modification
* @return array[value_name][name]
* array[value_name][visibilty]
*/
static function make_field_list()
{
// LAST NAME and FIRST NAME
$field_list_array = array();
$field_list_array['lastname']['name'] = get_lang('Lastname');
$field_list_array['firstname']['name'] = get_lang('Firstname');
if (api_get_setting('profile', 'name') != 'true') {
$field_list_array['firstname']['visibility'] = 0;
$field_list_array['lastname']['visibility'] = 0;
} else {
$field_list_array['firstname']['visibility'] = 1;
$field_list_array['lastname']['visibility'] = 1;
}
$field_list_array['username']['name'] = get_lang('Username');
$field_list_array['username']['visibility'] = 0;
// OFFICIAL CODE
$field_list_array['official_code']['name'] = get_lang('OfficialCode');
if (api_get_setting('profile', 'officialcode') != 'true') {
$field_list_array['official_code']['visibility'] = 1;
} else {
$field_list_array['official_code']['visibility'] = 0;
}
// EMAIL
$field_list_array['email']['name'] = get_lang('Email');
if (api_get_setting('profile', 'email') != 'true') {
$field_list_array['email']['visibility'] = 1;
} else {
$field_list_array['email']['visibility'] = 0;
}
// OPENID URL
//$field_list_array[] = 'openid_authentication';
/*
if (is_profile_editable() && api_get_setting('openid_authentication') == 'true') {
$form->addElement('text', 'openid', get_lang('OpenIDURL'), array('size' => 40));
if (api_get_setting('profile', 'openid') != 'true') {
$form->freeze('openid');
}
$form->applyFilter('openid', 'trim');
//if (api_get_setting('registration', 'openid') == 'true')
// $form->addRule('openid', get_lang('ThisFieldIsRequired'), 'required');
} */
// PHONE
$field_list_array['phone']['name'] = get_lang('Phone');
if (api_get_setting('profile', 'phone') != 'true') {
$field_list_array['phone']['visibility'] = 0;
} else {
$field_list_array['phone']['visibility'] = 1;
}
// LANGUAGE
$field_list_array['language']['name'] = get_lang('Language');
if (api_get_setting('profile', 'language') != 'true') {
$field_list_array['language']['visibility'] = 0;
} else {
$field_list_array['language']['visibility'] = 1;
}
// EXTRA FIELDS
$extra = UserManager::get_extra_fields(0, 50, 5, 'ASC');
$extra_data = UserManager::get_extra_user_data(api_get_user_id(), true);
foreach ($extra as $id => $field_details) {
if ($field_details[6] == 0) {
continue;
}
switch ($field_details[2]) {
case ExtraField::FIELD_TYPE_TEXT:
$field_list_array['extra_' . $field_details[1]]['name'] = $field_details[3];
if ($field_details[7] == 0) {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 0;
} else {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 1;
}
break;
case ExtraField::FIELD_TYPE_TEXTAREA:
$field_list_array['extra_' . $field_details[1]]['name'] = $field_details[3];
if ($field_details[7] == 0) {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 0;
} else {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 1;
}
break;
case ExtraField::FIELD_TYPE_RADIO:
$field_list_array['extra_' . $field_details[1]]['name'] = $field_details[3];
if ($field_details[7] == 0) {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 0;
} else {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 1;
}
break;
case ExtraField::FIELD_TYPE_SELECT:
$field_list_array['extra_' . $field_details[1]]['name'] = $field_details[3];
if ($field_details[7] == 0) {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 0;
} else {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 1;
}
break;
case ExtraField::FIELD_TYPE_SELECT_MULTIPLE:
$field_list_array['extra_' . $field_details[1]]['name'] = $field_details[3];
if ($field_details[7] == 0) {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 0;
} else {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 1;
}
break;
case ExtraField::FIELD_TYPE_DATE:
$field_list_array['extra_' . $field_details[1]]['name'] = $field_details[3];
if ($field_details[7] == 0) {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 0;
} else {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 1;
}
break;
case ExtraField::FIELD_TYPE_DATETIME:
$field_list_array['extra_' . $field_details[1]]['name'] = $field_details[3];
if ($field_details[7] == 0) {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 0;
} else {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 1;
}
break;
case ExtraField::FIELD_TYPE_DOUBLE_SELECT:
$field_list_array['extra_' . $field_details[1]]['name'] = $field_details[3];
if ($field_details[7] == 0) {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 0;
} else {
$field_list_array['extra_' . $field_details[1]]['visibility'] = 1;
}
break;
case ExtraField::FIELD_TYPE_DIVIDER:
//$form->addElement('static',$field_details[1], '<br /><strong>'.$field_details[3].'</strong>');
break;
}
}
return $field_list_array;
}
$htmlHeadXtra[] = '
<script type="text/javascript">
function validate_filter() {
document.formulaire.form_sent.value=0;
document.formulaire.submit();
}
</script>';
// displaying the header
Display::display_header($tool_name);
$link_add_group = '<a href="usergroups.php">' . Display::return_icon('multiple.gif', get_lang('RegistrationByUsersGroups')) . get_lang('RegistrationByUsersGroups') . '</a>';
echo '<div class="actions">' . $link_add_group . '</div>';
$form = new FormValidator('subscribe_user2course');
$form->addElement('header', '', $tool_name);
$form->display();
//checking for extra field with filter on
$extra_field_list = UserManager::get_extra_fields();
$new_field_list = array();
if (is_array($extra_field_list)) {
foreach ($extra_field_list as $extra_field) {
//if is enabled to filter and is a "<select>" field type
if ($extra_field[8] == 1 && $extra_field[2] == 4) {
$new_field_list[] = array('name' => $extra_field[3], 'variable' => $extra_field[1], 'data' => $extra_field[9]);
}
}
}
/* React on POSTed request */
if (isset($_POST['form_sent']) && $_POST['form_sent']) {
$form_sent = $_POST['form_sent'];
$users = is_array($_POST['UserList']) ? $_POST['UserList'] : array();
$courses = is_array($_POST['CourseList']) ? $_POST['CourseList'] : array();
$first_letter_user = $_POST['firstLetterUser'];
/**
* Displays a form with all the additionally defined user fields of the profile
* and give you the opportunity to include these in the CSV export
*
* @author Patrick Cool <*****@*****.**>, Ghent University, Belgium
* @version Dokeos 1.8.6
* @since November 2008
*/
function display_user_overview_export_options()
{
// include the user manager and formvalidator library
require_once api_get_path(LIBRARY_PATH) . 'usermanager.lib.php';
require_once api_get_path(LIBRARY_PATH) . 'formvalidator/FormValidator.class.php';
if ($_GET['export'] == 'options') {
// get all the defined extra fields
$extrafields = UserManager::get_extra_fields(0, 50, 5, 'ASC');
// creating the form with all the defined extra fields
$form = new FormValidator('exportextrafields', 'post', api_get_self() . "?view=" . Security::remove_XSS($_GET['view']) . '&display=' . Security::remove_XSS($_GET['display']) . '&export=' . Security::remove_XSS($_GET['export']));
foreach ($extrafields as $key => $extra) {
$form->addElement('checkbox', 'extra_export_field' . $extra[0], '', $extra[3]);
}
$form->addElement('submit', null, get_lang('Ok'));
// setting the default values for the form that contains all the extra fields
if (is_array($_SESSION['additional_export_fields'])) {
foreach ($_SESSION['additional_export_fields'] as $key => $value) {
$defaults['extra_export_field' . $value] = 1;
}
}
$form->setDefaults($defaults);
if ($form->validate()) {
// exporting the form values
$values = $form->exportValues();
// re-initialising the session that contains the additional fields that need to be exported
$_SESSION['additional_export_fields'] = array();
// adding the fields that are checked to the session
$message = '';
foreach ($values as $field_ids => $value) {
if ($value == 1 and strstr($field_ids, 'extra_export_field')) {
$_SESSION['additional_export_fields'][] = str_replace('extra_export_field', '', $field_ids);
}
}
// adding the fields that will be also exported to a message string
foreach ($_SESSION['additional_export_fields'] as $key => $extra_field_export) {
$message .= '<li>' . $extrafields[$extra_field_export][3] . '</li>';
}
// Displaying a feedback message
if (!empty($_SESSION['additional_export_fields'])) {
Display::display_confirmation_message(get_lang('FollowingFieldsWillAlsoBeExported') . ': <br /><ul>' . $message . '</ul>', false);
} else {
Display::display_confirmation_message(get_lang('NoAdditionalFieldsWillBeExported'), false);
}
$message = '';
} else {
$form->display();
}
} else {
if (!empty($_SESSION['additional_export_fields'])) {
// get all the defined extra fields
$extrafields = UserManager::get_extra_fields(0, 50, 5, 'ASC');
foreach ($_SESSION['additional_export_fields'] as $key => $extra_field_export) {
$message .= '<li>' . $extrafields[$extra_field_export][3] . '</li>';
}
Display::display_normal_message(get_lang('FollowingFieldsWillAlsoBeExported') . ': <br /><ul>' . $message . '</ul>', false);
$message = '';
}
}
}
