function _get_LO_query($type = 'html', $report_data = NULL, $other = '') { require_once "report_tableprinter.php"; function is_showed($which, $data) { if (isset($data['columns_filter'])) { return in_array($which, $data['columns_filter']['showed_columns']); } else { return false; } } if ($report_data == NULL) { $ref =& $_SESSION['report_tempdata']; } else { $ref =& $report_data; } $_rows =& $ref['rows_filter']; $_cols =& $ref['columns_filter']; $acl_man = new DoceboACLManager(); $acl_man->include_suspended = TRUE; $all_users =& $_rows['all_users']; //select root & descendants from orgchart instead $all_courses =& $_cols['all_courses']; $courses =& $_cols['selected_courses']; $types =& $_cols['lo_types']; $milestones =& $_cols['lo_milestones']; $showed =& $_cols['showed_columns']; $customcols =& $_cols['custom_fields']; $order_by = isset($_cols['order_by']) ? $_cols['order_by'] : 'userid'; $order_dir = isset($_cols['order_dir']) ? $_cols['order_dir'] : 'asc'; $suspended = isset($_cols['show_suspended']) ? (bool) $_cols['show_suspended'] : false; if ($all_users) { $users =& $acl_man->getAllUsersIdst(); } else { $users =& $acl_man->getAllUsersFromSelection($_rows['users']); } $temptypes = array(); foreach ($types as $val) { $temptypes[] = "'" . $val . "'"; } $tempmilestones = array(); foreach ($milestones as $val) { switch ($val) { case _MILESTONE_NONE: $tempmilestones[] = "''"; $tempmilestones[] = "'-'"; break; case _MILESTONE_START: $tempmilestones[] = "'start'"; break; case _MILESTONE_END: $tempmilestones[] = "'end'"; break; } } $colspans = array('user' => 0, 'course' => 0, 'lo' => 0); foreach ($this->LO_columns as $val) { if ($val['select']) { if (in_array($val['key'], $showed)) { switch ($val['key']) { case 'user_name': $colspans[$val['group']] += 2; break; default: $colspans[$val['group']]++; break; } } } else { if ($val['key'] == '_CUSTOM_FIELDS_') { //do nothing ... } else { $colspans[$val['group']]++; } } } //custom user fields require_once _adm_ . '/lib/lib.field.php'; $fman = new FieldList(); $field_values = array(); $temp_head2 = array(); foreach ($customcols as $val) { if ($val['selected']) { $colspans['user']++; $temp_head2[] = $val['label']; $field_values[$val['id']] = $fman->fieldValue((int) $val['id'], $users); } } $lang = $this->lang; $head1 = array(); $head1[] = array('colspan' => $colspans['user'], 'value' => $lang->def('_USER')); //_TH_USER $head1[] = array('colspan' => $colspans['course'], 'value' => $lang->def('_COURSE')); //_TH_COURSE $head1[] = array('colspan' => $colspans['lo'], 'value' => $lang->def('_LEARNING_OBJECTS')); if ($this->use_mail) { $head1[] = array('style' => 'img-cell', 'value' => $this->_loadEmailIcon()); } $head2 = array(); foreach ($this->LO_columns as $val) { if ($val['select']) { if (in_array($val['key'], $showed)) { switch ($val['key']) { //manages exceptions through switch case 'user_name': $head2[] = Lang::t('_LASTNAME', 'standard'); $head2[] = Lang::t('_FIRSTNAME', 'standard'); break; default: $head2[] = $val['label']; break; } } } else { if ($val['key'] == '_CUSTOM_FIELDS_') { foreach ($temp_head2 as $tval) { $head2[] = $tval; } } else { $head2[] = $val['label']; //label } } } if ($this->use_mail) { $head2[] = ''; } //'<img src="'.getPathImage().'standard/email.gif"/>';//''; //header for checkbox $buffer = new ReportTablePrinter($type); $buffer->openTable('', ''); $buffer->openHeader(); $buffer->addHeader($head1); $buffer->addHeader($head2); $buffer->closeHeader(); //retrieve LOs from courses $score_arr = array('test' => array(), 'scorm' => array()); //retrieve test score $query = "SELECT t1.idOrg, t2.idUser, t1.idCourse, t2.score, t2.bonus_score, t2.score_status " . " FROM %lms_organization AS t1 " . " JOIN %lms_testtrack AS t2 ON ( t1.objectType = 'test' " . " AND t1.idOrg = t2.idReference ), %adm_user as t3 " . "WHERE t3.idst=t2.idUser " . ($suspended ? "" : "AND t3.valid=1 ") . (!$all_courses ? " AND t1.idCourse IN (" . implode(',', $courses) . ") " : "") . (!$all_users ? " AND t2.idUser IN (" . implode(',', $users) . ") " : "") . (count($tempmilestones) > 0 ? " AND t1.milestone IN (" . implode(',', $tempmilestones) . ") " : ""); $res = sql_query($query); while ($row = mysql_fetch_assoc($res)) { $score_arr['test'][$row['idOrg']][$row['idUser']] = $row['score'] + $row['bonus_score']; } //retrievescorm score $query = "SELECT t1.idOrg, t2.idUser, t1.idCourse, t2.score_raw, t2.score_min, t2.score_max " . " FROM %lms_organization AS t1 " . " JOIN %lms_scorm_tracking AS t2 ON ( t1.objectType = 'scormorg' " . " AND t1.idOrg = t2.idReference ), %adm_user as t3 " . "WHERE t3.idst=t2.idUser " . ($suspended ? "" : "AND t3.valid=1 ") . (!$all_courses ? " AND t1.idCourse IN (" . implode(',', $courses) . ") " : "") . (!$all_users ? " AND t2.idUser IN (" . implode(',', $users) . ") " : "") . (count($tempmilestones) > 0 ? " AND t1.milestone IN (" . implode(',', $tempmilestones) . ") " : ""); $res = sql_query($query); while ($row = mysql_fetch_assoc($res)) { $score_arr['scorm'][$row['idOrg']][$row['idUser']] = $row['score_raw']; } $buffer->openBody(); //retrieve LO types translations $LO_types = $this->getLOTypesTranslations(); //retrieve LO's data $_dir = "ASC"; switch ($order_dir) { case "desc": $_dir = "DESC"; break; } $query_order_by = "t0.userid " . $_dir . ", t1.title " . $_dir; switch ($order_by) { case 'firstname': $query_order_by = "t0.firstname " . $_dir . ", t0.lastname, " . $_dir . ", t0.userid " . $_dir . ", t1.title " . $_dir; break; case 'lastname': $query_order_by = "t0.lastname " . $_dir . ", t0.firstname, " . $_dir . ", t0.userid " . $_dir . ", t1.title " . $_dir; break; case 'email': $query_order_by = "t0.email " . $_dir . ", t0.userid " . $_dir . ", t1.title " . $_dir; break; case 'course_code': $query_order_by = "t3.code " . $_dir . ", t3.name " . $_dir . ", t0.userid " . $_dir . ", t1.title " . $_dir; break; case 'course_name': $query_order_by = "t3.name " . $_dir . ", t3.code " . $_dir . ", t0.userid " . $_dir . ", t1.title " . $_dir; break; case 'object_title': $query_order_by = "t1.title " . $_dir . ", t0.userid " . $_dir; break; case 'object_type': $query_order_by = "t1.objectType " . $_dir . ", t1.title " . $_dir . ", t0.userid " . $_dir; break; case 'first_attempt': $query_order_by = "t2.firstAttempt"; break; case 'last_attempt': $query_order_by = "t2.dateAttempt"; break; } $query = "SELECT t0.idst as user_st, t0.userid, t0.firstname, t0.lastname, t0.email, t0.valid, " . " t1.idOrg, t1.objectType, t1.title, t1.idResource, t1.milestone, " . " t3.idCourse, t3.code, t3.name, t3.status as course_status, " . " t2.firstAttempt, t2.dateAttempt, t2.status " . " FROM %adm_user as t0, " . " %lms_organization as t1, " . " %lms_commontrack as t2, " . " %lms_course as t3 " . " WHERE " . " t0.idst=t2.idUser AND t1.idOrg=t2.idReference AND t1.idCourse=t3.idCourse " . ($suspended ? "" : "AND t0.valid=1 ") . (!$all_courses ? " AND t1.idCourse IN (" . implode(',', $courses) . ") " : "") . (count($temptypes) > 0 ? " AND t2.objectType IN (" . implode(',', $temptypes) . ") " : "") . (!$all_users ? " AND t2.idUser IN (" . implode(',', $users) . ") " : "") . (count($tempmilestones) > 0 ? "AND t1.milestone IN (" . implode(',', $tempmilestones) . ")" : "") . " ORDER BY " . $query_order_by; $res = sql_query($query); while ($row = mysql_fetch_assoc($res)) { $temp = array(); foreach ($this->LO_columns as $val) { switch ($val['key']) { case 'userid': $temp[] = $acl_man->relativeId($row['userid']); break; case 'user_name': if (in_array($val['key'], $showed)) { $temp[] = $row['lastname']; $temp[] = $row['firstname']; } break; case 'email': if (in_array($val['key'], $showed)) { $temp[] = $row['email']; } break; case 'suspended': if (in_array($val['key'], $showed)) { $temp[] = $row['valid'] > 0 ? Lang::t('_NO', 'standard') : Lang::t('_YES', 'standard'); } break; case '_CUSTOM_FIELDS_': foreach ($customcols as $field) { if ($field['selected']) { if (isset($field_values[$field['id']][$row['user_st']])) { $temp[] = $field_values[$field['id']][$row['user_st']]; } else { $temp[] = ''; } } } break; case 'course_code': $temp[] = $row['code']; break; case 'course_name': if (in_array($val['key'], $showed)) { $temp[] = $row['name']; } break; case 'course_status': if (in_array($val['key'], $showed)) { $temp[] = $this->_convertStatusCourse($row['course_status']); } break; case 'lo_type': if (in_array($val['key'], $showed)) { $temp[] = isset($LO_types[$row['objectType']]) ? $LO_types[$row['objectType']] : ""; } break; case 'lo_name': if (in_array($val['key'], $showed)) { $temp[] = $row['title']; } break; case 'lo_milestone': if (in_array($val['key'], $showed)) { $temp[] = $row['milestone']; } break; case 'firstAttempt': if (in_array($val['key'], $showed)) { $temp[] = $this->_convertDate($row['firstAttempt']); } break; case 'lastAttempt': if (in_array($val['key'], $showed)) { $temp[] = $this->_convertDate($row['dateAttempt']); } break; case 'lo_status': if (in_array($val['key'], $showed)) { $temp[] = Lang::t($row['status'], 'storage'); } break; case 'lo_score': if (in_array($val['key'], $showed)) { switch ($row['objectType']) { case 'test': if (isset($score_arr['test'][$row['idOrg']][$row['user_st']])) { $score_val = $score_arr['test'][$row['idOrg']][$row['user_st']]; } else { $score_val = '0'; } $temp[] = $score_val; break; case 'scormorg': if (isset($score_arr['scorm'][$row['idOrg']][$row['user_st']])) { $score_val = $score_arr['scorm'][$row['idOrg']][$row['user_st']]; } else { $score_val = '0'; } $temp[] = $score_val; break; default: $temp[] = ''; break; } } break; default: if (in_array($val['key'], $showed)) { $temp[] = ''; } break; } } //end switch - end for if ($this->use_mail) { $temp[] = '<div class="align_center">' . Form::getInputCheckbox('mail_' . $row['user_st'], 'mail_recipients[]', $row['user_st'], isset($_POST['select_all']), '') . '</div>'; } $buffer->addLine($temp); } $buffer->closeBody(); $buffer->closeTable(); $output = $buffer->get(); if ($this->use_mail) { $output .= $this->_loadEmailActions(); } return $output; }
$values = array(); switch ($id_field) { case "name": $acl_man = new DoceboACLManager(); $users = getSubscribed($id_course); $allusers_info = $acl_man->getUsers($users); while (list(, $user_info) = each($allusers_info)) { $values[$user_info[ACL_INFO_IDST]] = $user_info[ACL_INFO_LASTNAME] . ' ' . $user_info[ACL_INFO_FIRSTNAME]; } break; case "email": $acl_man = new DoceboACLManager(); $users = getSubscribed($id_course); $allusers_info = $acl_man->getUsers($users); while (list(, $user_info) = each($allusers_info)) { $values[$user_info[ACL_INFO_IDST]] = $user_info[ACL_INFO_EMAIL]; } break; default: $users = getSubscribed($id_course); $values = $fman->fieldValue((int) $id_field, $users); } if ($id_field == 'name') { } else { } require_once _base_ . '/lib/lib.json.php'; $json = new Services_JSON(); $output = $json->encode($values); aout($output); break; }