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;
 }
Example #2
0
        $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;
}