break; case 'get_survey_overview': $sessionId = 0; if (!empty($_GET['session_id']) && !empty($_GET['course_id']) && !empty($_GET['survey_id'])) { $sessionId = intval($_GET['session_id']); $courseId = intval($_GET['course_id']); $surveyId = intval($_GET['survey_id']); $date_from = $_GET['date_from']; $date_to = $_GET['date_to']; //$course = api_get_course_info_by_id($courseId); } /** * Add lessons of course */ $columns = array('username', 'firstname', 'lastname'); $questions = SurveyManager::get_questions($surveyId, $courseId); foreach ($questions as $question_id => $question) { $columns[] = $question_id; } $result = SessionManager::get_survey_overview($sessionId, $courseId, $surveyId, $date_from, $date_to, array('where' => $whereCondition, 'order' => "{$sidx} {$sord}", 'limit' => "{$start} , {$limit}")); break; case 'get_session_progress': $columns = array('lastname', 'firstname', 'username', 'total', 'courses', 'lessons', 'exercises', 'forums', 'homeworks', 'wikis', 'surveys', 'lessons_total', 'lessons_done', 'lessons_left', 'lessons_progress', 'exercises_total', 'exercises_done', 'exercises_left', 'exercises_progress', 'forums_total', 'forums_done', 'forums_left', 'forums_progress', 'assignments_total', 'assignments_done', 'assignments_left', 'assignments_progress', 'wiki_total', 'wiki_revisions', 'wiki_read', 'wiki_unread', 'wiki_progress', 'surveys_total', 'surveys_done', 'surveys_left', 'surveys_progress'); $sessionId = 0; if (!empty($_GET['course_id']) && !empty($_GET['session_id'])) { $sessionId = intval($_GET['session_id']); $courseId = intval($_GET['course_id']); } $result = SessionManager::get_session_progress($sessionId, $courseId, null, null, array('where' => $whereCondition, 'order' => "{$sidx} {$sord}", 'limit' => "{$start} , {$limit}")); break; case 'get_session_access_overview':
/** * Gets the survey answers * @param int session id * @param int course id * @param int survey id * @param array options order and limit keys * @todo fix the query * @return array table with user name, lp name, progress */ public static function get_survey_overview($sessionId = 0, $courseId = 0, $surveyId = 0, $date_from, $date_to, $options) { //escaping vars $sessionId = intval($sessionId); $courseId = intval($courseId); $surveyId = intval($surveyId); $date_from = Database::escape_string($date_from); $date_to = Database::escape_string($date_to); //tables $session_course_user = Database::get_main_table(TABLE_MAIN_SESSION_COURSE_USER); $user = Database::get_main_table(TABLE_MAIN_USER); $tbl_course_lp_view = Database::get_course_table(TABLE_LP_VIEW); $c_survey = Database::get_course_table(TABLE_SURVEY); $c_survey_answer = Database::get_course_table(TABLE_SURVEY_ANSWER); $c_survey_question = Database::get_course_table(TABLE_SURVEY_QUESTION); $c_survey_question_option = Database::get_course_table(TABLE_SURVEY_QUESTION_OPTION); $course = api_get_course_info_by_id($courseId); $where = " WHERE c_id = '%s' AND s.status <> 2 AND session_id = %s"; $limit = null; if (!empty($options['limit'])) { $limit = " LIMIT " . $options['limit']; } if (!empty($options['where'])) { $where .= ' ' . $options['where']; } $order = null; if (!empty($options['order'])) { $order = " ORDER BY " . $options['order']; } $sql = "SELECT u.user_id, u.lastname, u.firstname, u.username, u.email, s.c_id\n FROM {$session_course_user} s\n INNER JOIN {$user} u ON u.user_id = s.user_id\n {$where} {$order} {$limit}"; $sql_query = sprintf($sql, intval($course['real_id']), $sessionId); $rs = Database::query($sql_query); while ($user = Database::fetch_array($rs)) { $users[$user['user_id']] = $user; } //Get survey questions $questions = SurveyManager::get_questions($surveyId, $courseId); //Survey is anonymous? $result = Database::query(sprintf("SELECT anonymous FROM {$c_survey} WHERE survey_id = %d", $surveyId)); $row = Database::fetch_array($result); $anonymous = $row['anonymous'] == 1 ? true : false; $table = array(); foreach ($users as $user) { $data = array('lastname' => $anonymous ? '***' : $user[1], 'firstname' => $anonymous ? '***' : $user[2], 'username' => $anonymous ? '***' : $user[3]); //Get questions by user $sql = "SELECT sa.question_id, sa.option_id, sqo.option_text, sq.type\n FROM {$c_survey_answer} sa\n INNER JOIN {$c_survey_question} sq\n ON sq.question_id = sa.question_id\n LEFT JOIN {$c_survey_question_option} sqo\n ON\n sqo.c_id = sa.c_id AND\n sqo.question_id = sq.question_id AND\n sqo.question_option_id = sa.option_id AND\n sqo.survey_id = sq.survey_id\n WHERE\n sa.survey_id = %d AND\n sa.c_id = %d AND\n sa.user = %d\n "; //. $where_survey; $sql_query = sprintf($sql, $surveyId, $courseId, $user['user_id']); $result = Database::query($sql_query); $user_questions = array(); while ($row = Database::fetch_array($result)) { $user_questions[$row['question_id']] = $row; } //Match course lessons with user progress foreach ($questions as $question_id => $question) { $option_text = 'option_text'; if ($user_questions[$question_id]['type'] == 'open') { $option_text = 'option_id'; } $data[$question_id] = $user_questions[$question_id][$option_text]; } $table[] = $data; } return $table; }
/** * This function displays the comparative report which allows you to compare two questions * A comparative report creates a table where one question is on the x axis and a second question is on the y axis. * In the intersection is the number of people who have answerd positive on both options. * * @return string HTML code * * @author Patrick Cool <*****@*****.**>, Ghent University * @version February 2007 */ public static function display_comparative_report() { // Allowed question types for comparative report $allowed_question_types = array('yesno', 'multiplechoice', 'multipleresponse', 'dropdown', 'percentage', 'score'); // Getting all the questions $questions = SurveyManager::get_questions($_GET['survey_id']); // Actions bar echo '<div class="actions">'; echo '<a href="' . api_get_path(WEB_CODE_PATH) . 'survey/reporting.php?survey_id=' . intval($_GET['survey_id']) . '&' . api_get_cidreq() . '">' . Display::return_icon('back.png', get_lang('BackTo') . ' ' . get_lang('ReportingOverview'), '', ICON_SIZE_MEDIUM) . '</a>'; echo '</div>'; // Displaying an information message that only the questions with predefined answers can be used in a comparative report Display::display_normal_message(get_lang('OnlyQuestionsWithPredefinedAnswers'), false); // The form for selecting the axis of the table echo '<form id="form1" name="form1" method="get" action="' . api_get_self() . '?action=' . Security::remove_XSS($_GET['action']) . '&survey_id=' . intval($_GET['survey_id']) . '&xaxis=' . Security::remove_XSS($_GET['xaxis']) . '&y=' . Security::remove_XSS($_GET['yaxis']) . '">'; // Survey_id echo '<input type="hidden" name="action" value="' . Security::remove_XSS($_GET['action']) . '"/>'; echo '<input type="hidden" name="survey_id" value="' . intval($_GET['survey_id']) . '"/>'; // X axis echo get_lang('SelectXAxis') . ': '; echo '<select name="xaxis">'; echo '<option value="">---</option>'; foreach ($questions as $key => &$question) { if (is_array($allowed_question_types)) { if (in_array($question['type'], $allowed_question_types)) { echo '<option value="' . $question['question_id'] . '"'; if (isset($_GET['xaxis']) && $_GET['xaxis'] == $question['question_id']) { echo ' selected="selected"'; } echo '">' . api_substr(strip_tags($question['question']), 0, 50) . '</option>'; } } } echo '</select><br /><br />'; // Y axis echo get_lang('SelectYAxis') . ': '; echo '<select name="yaxis">'; echo '<option value="">---</option>'; foreach ($questions as $key => &$question) { if (in_array($question['type'], $allowed_question_types)) { echo '<option value="' . $question['question_id'] . '"'; if (isset($_GET['yaxis']) && $_GET['yaxis'] == $question['question_id']) { echo ' selected="selected"'; } echo '">' . api_substr(strip_tags($question['question']), 0, 50) . '</option>'; } } echo '</select><br /><br />'; echo '<button class="save" type="submit" name="Submit" value="Submit">' . get_lang('CompareQuestions') . '</button>'; echo '</form>'; // Getting all the information of the x axis if (isset($_GET['xaxis']) && is_numeric($_GET['xaxis'])) { $question_x = SurveyManager::get_question($_GET['xaxis']); } // Getting all the information of the y axis if (isset($_GET['yaxis']) && is_numeric($_GET['yaxis'])) { $question_y = SurveyManager::get_question($_GET['yaxis']); } if (isset($_GET['xaxis']) && is_numeric($_GET['xaxis']) && isset($_GET['yaxis']) && is_numeric($_GET['yaxis'])) { // Getting the answers of the two questions $answers_x = SurveyUtil::get_answers_of_question_by_user($_GET['survey_id'], $_GET['xaxis']); $answers_y = SurveyUtil::get_answers_of_question_by_user($_GET['survey_id'], $_GET['yaxis']); // Displaying the table $tableHtml = '<table border="1" class="data_table">'; $xOptions = array(); // The header $tableHtml .= ' <tr>'; for ($ii = 0; $ii <= count($question_x['answers']); $ii++) { if ($ii == 0) { $tableHtml .= ' <th> </th>'; } else { if ($question_x['type'] == 'score') { for ($x = 1; $x <= $question_x['maximum_score']; $x++) { $tableHtml .= ' <th>' . $question_x['answers'][$ii - 1] . '<br />' . $x . '</th>'; } $x = ''; } else { $tableHtml .= ' <th>' . $question_x['answers'][$ii - 1] . '</th>'; } $optionText = strip_tags($question_x['answers'][$ii - 1]); $optionText = html_entity_decode($optionText); array_push($xOptions, trim($optionText)); } } $tableHtml .= ' </tr>'; $chartData = array(); // The main part for ($ij = 0; $ij < count($question_y['answers']); $ij++) { $currentYQuestion = strip_tags($question_y['answers'][$ij]); $currentYQuestion = html_entity_decode($currentYQuestion); // The Y axis is a scoring question type so we have more rows than the options (actually options * maximum score) if ($question_y['type'] == 'score') { for ($y = 1; $y <= $question_y['maximum_score']; $y++) { $tableHtml .= ' <tr>'; for ($ii = 0; $ii <= count($question_x['answers']); $ii++) { if ($question_x['type'] == 'score') { for ($x = 1; $x <= $question_x['maximum_score']; $x++) { if ($ii == 0) { $tableHtml .= ' <th>' . $question_y['answers'][$ij] . ' ' . $y . '</th>'; break; } else { $tableHtml .= ' <td align="center">'; $votes = SurveyUtil::comparative_check($answers_x, $answers_y, $question_x['answersid'][$ii - 1], $question_y['answersid'][$ij], $x, $y); $tableHtml .= $votes; array_push($chartData, array('serie' => array($currentYQuestion, $xOptions[$ii - 1]), 'option' => $x, 'votes' => $votes)); $tableHtml .= '</td>'; } } } else { if ($ii == 0) { $tableHtml .= '<th>' . $question_y['answers'][$ij] . ' ' . $y . '</th>'; } else { $tableHtml .= '<td align="center">'; $votes = SurveyUtil::comparative_check($answers_x, $answers_y, $question_x['answersid'][$ii - 1], $question_y['answersid'][$ij], 0, $y); $tableHtml .= $votes; array_push($chartData, array('serie' => array($currentYQuestion, $xOptions[$ii - 1]), 'option' => $y, 'votes' => $votes)); $tableHtml .= '</td>'; } } } $tableHtml .= ' </tr>'; } } else { $tableHtml .= ' <tr>'; for ($ii = 0; $ii <= count($question_x['answers']); $ii++) { if ($question_x['type'] == 'score') { for ($x = 1; $x <= $question_x['maximum_score']; $x++) { if ($ii == 0) { $tableHtml .= ' <th>' . $question_y['answers'][$ij] . '</th>'; break; } else { $tableHtml .= ' <td align="center">'; $votes = SurveyUtil::comparative_check($answers_x, $answers_y, $question_x['answersid'][$ii - 1], $question_y['answersid'][$ij], $x, 0); $tableHtml .= $votes; array_push($chartData, array('serie' => array($currentYQuestion, $xOptions[$ii - 1]), 'option' => $x, 'votes' => $votes)); $tableHtml .= '</td>'; } } } else { if ($ii == 0) { $tableHtml .= ' <th>' . $question_y['answers'][$ij] . '</th>'; } else { $tableHtml .= ' <td align="center">'; $votes = SurveyUtil::comparative_check($answers_x, $answers_y, $question_x['answersid'][$ii - 1], $question_y['answersid'][$ij]); $tableHtml .= $votes; array_push($chartData, array('serie' => $xOptions[$ii - 1], 'option' => $currentYQuestion, 'votes' => $votes)); $tableHtml .= '</td>'; } } } $tableHtml .= ' </tr>'; } } $tableHtml .= '</table>'; echo '<div id="chartContainer" class="col-md-12">'; echo self::drawChart($chartData, true); echo '</div>'; echo $tableHtml; } }
/** * Display a sortable table that contains an overview off all the progress of the user in a session * @author César Perales <*****@*****.**>, Beeznest Team */ function display_survey_overview($sessionId = 0, $courseId = 0, $surveyId = 0, $date_from, $date_to) { /** * Column name * The order is important you need to check the $column variable in the model.ajax.php file */ $columns = array(get_lang('Username'), get_lang('FirstName'), get_lang('LastName')); //add lessons of course $questions = SurveyManager::get_questions($surveyId, $courseId); foreach ($questions as $question) { $columns[] = $question['question']; } /** * Column config */ $column_model = array(array('name' => 'username', 'index' => 'username', 'align' => 'left', 'search' => 'true', 'wrap_cell' => "true"), array('name' => 'firstname', 'index' => 'firstname', 'align' => 'left', 'search' => 'true'), array('name' => 'lastname', 'index' => 'lastname', 'align' => 'left', 'search' => 'true')); //get dinamic column names foreach ($questions as $question_id => $question) { $column_model[] = array('name' => $question_id, 'index' => $question_id, 'width' => '70', 'align' => 'left', 'search' => 'true'); } $action_links = ''; // jqgrid will use this URL to do the selects $url = api_get_path(WEB_AJAX_PATH) . 'model.ajax.php?a=get_survey_overview&session_id=' . $sessionId . '&course_id=' . $courseId . '&survey_id=' . $surveyId . '&date_to=' . $date_to . '&date_from=' . $date_from; // Table Id $tableId = 'lpProgress'; //Autowidth $extra_params['autowidth'] = 'true'; // height auto $extra_params['height'] = 'auto'; $table = Display::grid_js($tableId, $url, $columns, $column_model, $extra_params, array(), $action_links, true); $return = '<script>$(function() {' . $table . 'jQuery("#' . $tableId . '").jqGrid("navGrid","#' . $tableId . '_pager",{view:false, edit:false, add:false, del:false, search:false, excel:true}); jQuery("#' . $tableId . '").jqGrid("navButtonAdd","#' . $tableId . '_pager",{ caption:"", title:"' . get_lang('ExportExcel') . '", onClickButton : function () { jQuery("#' . $tableId . '").jqGrid("excelExport",{"url":"' . $url . '&export_format=xls"}); } }); });</script>'; $return .= Display::grid_html($tableId); return $return; }