function JQ_exportQuestions($option, $page, $course_id, $cid, $gqp = false) { global $JLMS_DB, $Itemid; $quiz_id = intval(mosGetParam($_REQUEST, 'quiz_id', 0)); $where = ""; if ($gqp) { $where .= "\n AND course_id = '0'"; $where .= "\n AND c_quiz_id = '0'"; } else { if ($quiz_id > 0) { $where .= "\n AND course_id = '" . $course_id . "'"; $where .= "\n AND c_quiz_id = '" . $quiz_id . "'"; } else { $where .= "\n AND course_id = '" . $course_id . "'"; $where .= "\n AND c_quiz_id = '0'"; } } $query = "SELECT *" . "\n FROM #__lms_quiz_t_question" . "\n WHERE 1" . $where . (count($cid) && $cid[0] ? "\n AND c_id IN (" . implode(",", $cid) . ")" : ""); $JLMS_DB->setQuery($query); $questions = $JLMS_DB->loadObjectList(); $query = "SELECT *" . "\n FROM #__lms_quiz_t_question" . "\n WHERE 1" . "\n AND course_id = '" . $course_id . "'" . "\n AND c_quiz_id = '0'"; $JLMS_DB->setQuery($query); $qpool = $JLMS_DB->loadObjectList(); $query = "SELECT *" . "\n FROM #__lms_quiz_t_question" . "\n WHERE 1" . "\n AND course_id = '0'" . "\n AND c_quiz_id = '0'"; $JLMS_DB->setQuery($query); $gqpool = $JLMS_DB->loadObjectList(); if (isset($questions) && count($questions)) { $tmp_questions = array(); foreach ($questions as $n => $quest) { $tmp_questions[$n] = $quest; if ($quest->c_type == 20) { if (isset($qpool) && count($qpool)) { foreach ($qpool as $item_qpool) { if ($quest->c_pool == $item_qpool->c_id) { $tmp_questions[$n]->c_id = $item_qpool->c_id; $tmp_questions[$n]->course_id = $quest->course_id; $tmp_questions[$n]->c_quiz_id = $quest->c_quiz_id; $tmp_questions[$n]->c_point = $item_qpool->c_point; $tmp_questions[$n]->c_attempts = $item_qpool->c_attempts; $tmp_questions[$n]->c_question = $item_qpool->c_question; $tmp_questions[$n]->c_type = $item_qpool->c_type; $tmp_questions[$n]->published = $quest->published; $tmp_questions[$n]->ordering = $quest->ordering; } } } } if ($quest->c_type == 21) { if (isset($gqpool) && count($gqpool)) { foreach ($gqpool as $item_gqpool) { if ($quest->c_pool_gqp == $item_gqpool->c_id) { $tmp_questions[$n]->c_id = $item_gqpool->c_id; $tmp_questions[$n]->course_id = $quest->course_id; $tmp_questions[$n]->c_quiz_id = $quest->c_quiz_id; $tmp_questions[$n]->c_point = $item_gqpool->c_point; $tmp_questions[$n]->c_attempts = $item_gqpool->c_attempts; $tmp_questions[$n]->c_question = $item_gqpool->c_question; $tmp_questions[$n]->c_type = $item_gqpool->c_type; $tmp_questions[$n]->published = $quest->published; $tmp_questions[$n]->ordering = $quest->ordering; } } } } } if (isset($tmp_questions) && count($tmp_questions)) { $questions = array(); $questions = $tmp_questions; } } // echo '<pre>'; // print_r($qpool); // print_r($gqpool); // print_r($questions); // echo '</pre>'; // die; $question_ids = array(); $questions_types = array(); foreach ($questions as $n => $question) { $question_ids[$n] = $question->c_id; $questions_types[$question->c_type][] = $question->c_id; } $variants_answers_types = array(); foreach ($questions_types as $type => $quests) { switch (intval($type)) { case '1': case '2': case '3': #case '12': #case '13': if (isset($quests) && count($quests)) { $variants_answers = array(); $str_quiestions_ids = implode(",", $quests); $query = "SELECT c_id, c_question_id, c_choice, c_right, ordering" . "\n FROM #__lms_quiz_t_choice" . "\n WHERE 1" . "\n AND c_question_id IN (" . $str_quiestions_ids . ")" . "\n ORDER BY c_question_id, ordering"; $JLMS_DB->setQuery($query); $variants_answers = $JLMS_DB->loadObjectList(); $variants_answers_types[$type] = $variants_answers; } break; case '4': case '5': #case '11': if (isset($quests) && count($quests)) { $variants_answers = array(); $str_quiestions_ids = implode(",", $quests); $query = "SELECT *" . "\n FROM #__lms_quiz_t_matching" . "\n WHERE 1" . "\n AND c_question_id IN (" . $str_quiestions_ids . ")" . "\n ORDER BY c_question_id, ordering"; $JLMS_DB->setQuery($query); $variants_answers = $JLMS_DB->loadObjectList(); $variants_answers_types[$type] = $variants_answers; } break; case '6': if (isset($quests) && count($quests)) { $variants_answers = array(); $str_quiestions_ids = implode(",", $quests); $query = "SELECT qtb.c_id, qtb.c_question_id, qtt.c_blank_id, qtt.c_text, qtt.ordering" . "\n FROM #__lms_quiz_t_blank qtb, #__lms_quiz_t_text as qtt" . "\n WHERE 1" . "\n AND qtb.c_question_id IN (" . $str_quiestions_ids . ")" . "\n AND qtb.c_id = qtt.c_blank_id" . "\n ORDER BY c_question_id, ordering"; $JLMS_DB->setQuery($query); $variants_answers = $JLMS_DB->loadObjectList(); $variants_answers_types[$type] = $variants_answers; } break; case '9': if (isset($quests) && count($quests)) { $variants_answers = array(); $str_quiestions_ids = implode(",", $quests); $query = "SELECT *" . "\n FROM #__lms_quiz_t_scale" . "\n WHERE 1" . "\n AND c_question_id IN (" . $str_quiestions_ids . ")" . "\n ORDER BY c_question_id, c_type, ordering"; $JLMS_DB->setQuery($query); $variants_answers = $JLMS_DB->loadObjectList(); $variants_answers_types[$type] = $variants_answers; } break; } } $max_items = 0; $exclude_fields = array('c_id', 'c_question_id', 'c_blank_id', 'c_right', 'ordering'); $questions_pre_csv = array(); foreach ($questions as $n => $quest) { if (isset($variants_answers_types[$quest->c_type]) && count($variants_answers_types[$quest->c_type])) { $questions_pre_csv[$n] = $quest; $v_answers = $variants_answers_types[$quest->c_type]; $questions_pre_csv[$n]->answers = array(); $i = 0; $max_fields = 0; foreach ($v_answers as $v_answ) { if ($v_answ->c_question_id == $quest->c_id) { $questions_pre_csv[$n]->answers[] = $v_answ; if (!$i) { $cfields = 0; foreach ($v_answ as $key => $item) { if (!in_array($key, $exclude_fields)) { $cfields++; } } } $i++; $max_fields = $cfields * $i; } } if ($max_items < $max_fields) { $max_items = $max_fields; } } } $question_csv = array(); foreach ($questions_pre_csv as $n => $qpcsv) { $question_csv[$n]->type = JLMS_quiz_admin_class::getQuestType($qpcsv->c_type); $question_csv[$n]->question = JLMS_processCSVField($qpcsv->c_question); $question_csv[$n]->points = $qpcsv->c_point; $question_csv[$n]->attempts = $qpcsv->c_attempts; $question_csv[$n]->published = $qpcsv->published; $question_csv[$n]->ordering = $qpcsv->ordering; $question_csv[$n]->right = JLMS_processCSVField(JLMS_quiz_admin_class::getRight($qpcsv->c_type, $qpcsv->answers)); if (isset($qpcsv->answers) && count($qpcsv->answers)) { $tmp_answers = $qpcsv->answers; $m = 1; foreach ($tmp_answers as $tmp_a) { foreach ($tmp_a as $key => $item) { $answ_item = 'item_' . $m; if (!in_array($key, $exclude_fields)) { $question_csv[$n]->{$answ_item} = JLMS_processCSVField($item); $m++; } } } if ($m < $max_items) { for ($i = $m; $i <= $max_items; $i++) { $answ_item = 'item_' . $i; $question_csv[$n]->{$answ_item} = JLMS_processCSVField(''); } } /* for($i=0;$i<$max_items;$i++){ $m = $i + 1; $answ_item = 'item_'.$m; if(isset($tmp_answers[$i])){ switch(intval($qpcsv->c_type)){ case '1': case '2': case '3': $question_csv[$n]->$answ_item = $tmp_answers[$i]->c_choice; break; case '4': case '5': $question_csv[$n]->$answ_item = $tmp_answers[$i]->c_left_text; break; case '6': $question_csv[$n]->$answ_item = $tmp_answers[$i]->c_text; break; case '9': $question_csv[$n]->$answ_item = $tmp_answers[$i]->c_field; break; } } else { $question_csv[$n]->$answ_item = ''; } } */ } } $titles = new stdClass(); $titles->type = _JLMS_QUIZ_TBL_QUEST_TYPE; //'Type'; $titles->question = str_replace(':', '', _JLMS_QUIZ_QUESTION); //'Question'; $titles->points = str_replace(':', '', _JLMS_QUIZ_QUEST_POINTS); //'Points'; $titles->attempts = str_replace(':', '', _JLMS_QUIZ_QUEST_ATTEMPTS); //'Attempts'; $titles->published = _JLMS_QUIZ_TBL_QUEST_PUBLISH; //'Published'; $titles->ordering = str_replace(':', '', _JLMS_QUIZ_QUEST_ORDERING); //'Ordering'; $titles->right = 'Correct choice'; for ($i = 1; $i <= $max_items; $i++) { $titles->{'item_' . $i} = 'Item ' . $i; } $data_csv = ''; $titles = get_object_vars($titles); if (isset($titles) && count($titles)) { foreach ($titles as $tk => $tv) { $titles[$tk] = JLMS_processCSVField($tv); } $data_csv .= implode(",", $titles) . "\n"; } if (count($question_csv)) { foreach ($question_csv as $n => $d) { $d = get_object_vars($d); $data_csv .= implode(",", $d) . "\n"; } } if ($course_id > 0 && $quiz_id > 0) { $course_name = JLMS_getCourseName($course_id); $quiz_name = JLMS_getQuizName($quiz_id); $tmpl_name = ucfirst($course_name) . '_[' . ucfirst($quiz_name) . ']_' . date('dMY'); } else { if ($course_id > 0 && $quiz_id < 0) { $course_name = JLMS_getCourseName($course_id); $tmpl_name = ucfirst($course_name) . '_[' . _JLMS_QUIZ_QUEST_POOL . ']_' . date('dMY'); } else { $tmpl_name = _JLMS_GLOBAL_QUEST_POOL . '_' . date('dMY'); } } $tmpl_name = str_replace(" ", "_", $tmpl_name); if (preg_match('/Opera(\\/| )([0-9].[0-9]{1,2})/', $_SERVER['HTTP_USER_AGENT'])) { $UserBrowser = "Opera"; } elseif (preg_match('/MSIE ([0-9].[0-9]{1,2})/', $_SERVER['HTTP_USER_AGENT'])) { $UserBrowser = "IE"; } else { $UserBrowser = ''; } header("Content-type: application/csv"); header('Expires: ' . gmdate('D, d M Y H:i:s') . ' GMT'); header("Content-Length: " . strlen(trim($data_csv))); header('Content-Disposition: attachment; filename="' . $tmpl_name . '.csv"'); if ($UserBrowser == 'IE') { header('Cache-Control: must-revalidate, post-check=0, pre-check=0'); header('Pragma: public'); } else { header('Pragma: no-cache'); } echo $data_csv; exit; echo '<pre>'; print_r($max_items); // print_r($_POST); // print_r($JLMS_DB->getQuery()); // print_r($JLMS_DB->getErrorMsg()); // print_r($variants_answers); // print_r($variants_answers_types); // print_r($questions); // print_r($questions_pre_csv); print_r($question_csv); // print_r($question_ids); // print_r($questions_types); echo '</pre>'; die; }
function JLMS_REP_exportCsv($hits, $tot_hits = array(), $users, $courses, $pageNav, $lists, $levels, $filt_cat, $filt_group, $option, $is_full) { global $JLMS_DB, $JLMS_CONFIG, $JLMS_SESSION, $task; switch ($task) { case 'report_access': $users_str = implode(',', $users); $courses_str = implode(',', $courses); $JLMS_DB->setQuery('SELECT course_name FROM #__lms_courses WHERE id IN(' . $courses_str . ') ORDER BY course_name'); $crs_name = $JLMS_DB->loadResultArray(); $JLMS_DB->setQuery('SELECT * FROM #__lms_courses WHERE id IN(' . $courses_str . ') ORDER BY course_name'); $crs_options = $JLMS_DB->loadObjectList(); $text_to_csv = 'username,name,email,'; $array_zagolovk = array(); foreach ($crs_name as $key => $c_name) { $view_is_course = 1; if ($JLMS_CONFIG->get('flms_integration', 1)) { $params = new JLMSParameters($crs_options[$key]->params); $view_is_course = $params->get('show_in_report', 1); } if ($view_is_course) { $array_zagolovk[] = JLMS_processCSVField($c_name); //echo '<th class="sectiontableheader" style="text-align:center;">'.$c_name.'</th>'; } } $zagolovk = implode(',', $array_zagolovk); if ($zagolovk) { $text_to_csv .= $zagolovk . "\n"; } else { $text_to_csv .= "\n"; } $zzz = 0; foreach ($users as $usr_id) { $JLMS_DB->setQuery('SELECT username,name, email FROM #__users WHERE id =' . $usr_id); $usrname = $JLMS_DB->LoadObject(); $course_hits = 0; $text_to_csv .= $usrname->username . ',' . JLMS_processCSVField($usrname->name) . ',' . $usrname->email . ','; $count = 0; foreach ($courses as $key => $course_id) { $count++; $hit_num = 0; for ($i = 0; $i < count($hits); $i++) { if ($hits[$i]->c_id == $course_id && $hits[$i]->usr_id == $usr_id) { $hit_num = $hits[$i]->hits; break; // by DEN } } $view_is_course = 1; if ($JLMS_CONFIG->get('flms_integration', 1)) { $params = new JLMSParameters($crs_options[$key]->params); $view_is_course = $params->get('show_in_report', 1); } if ($view_is_course) { $text_to_csv .= $hit_num; if ($count != count($courses)) { $text_to_csv .= ','; } } } $text_to_csv .= "\n"; $zzz++; } $text_to_csv .= 'Total,,,'; $results_total_hits = array(); $k = 0; foreach ($courses as $i => $course_id) { foreach ($tot_hits as $j => $hit) { if ($course_id == $hit->c_id) { foreach ($hit as $key => $tmp_hit) { if ($key == 'hits') { $results_total_hits[] = $tmp_hit; $k++; } } } } } $text_to_csv .= implode(",", $results_total_hits); $text_to_csv .= "\n"; break; case 'report_certif': $users_str = implode(',', $users); $courses_str = implode(',', $courses); $query = "SELECT h.user_id as usr_id,h.course_id as c_id FROM #__lms_certificate_users as h LEFT JOIN #__users as u ON h.user_id=u.id LEFT JOIN #__lms_courses as c ON h.course_id = c.id WHERE h.course_id IN (" . $courses_str . ") AND h.user_id IN (" . $users_str . ") ORDER BY h.course_id,h.user_id"; $JLMS_DB->SetQuery($query); $hits = $JLMS_DB->LoadObjectLIST(); $JLMS_DB->setQuery('SELECT course_name FROM #__lms_courses WHERE id IN(' . $courses_str . ') ORDER BY course_name'); $crs_name = $JLMS_DB->loadResultArray(); $JLMS_DB->setQuery('SELECT * FROM #__lms_courses WHERE id IN(' . $courses_str . ') ORDER BY course_name'); $crs_options = $JLMS_DB->loadObjectList(); $text_to_csv = 'username,name,email,'; $array_zagolovk = array(); foreach ($crs_name as $key => $c_name) { $view_is_course = 1; if ($JLMS_CONFIG->get('flms_integration', 1)) { $params = new JLMSParameters($crs_options[$key]->params); $view_is_course = $params->get('show_in_report', 1); } if ($view_is_course) { $array_zagolovk[] = JLMS_processCSVField($c_name); //echo '<th class="sectiontableheader" style="text-align:center;">'.$c_name.'</th>'; } } $zagolovk = implode(',', $array_zagolovk); if ($zagolovk) { $text_to_csv .= $zagolovk . "\n"; } else { $text_to_csv .= "\n"; } $zzz = 0; foreach ($users as $usr_id) { $JLMS_DB->setQuery('SELECT username,name, email FROM #__users WHERE id =' . $usr_id); $usrname = $JLMS_DB->LoadObject(); $course_hits = 0; $text_to_csv .= $usrname->username . ',' . JLMS_processCSVField($usrname->name) . ',' . $usrname->email . ','; $count = 0; foreach ($courses as $key => $course_id) { $count++; $hit_num = _JLMS_NO_ALT_TITLE; for ($i = 0; $i < count($hits); $i++) { if ($hits[$i]->c_id == $course_id && $hits[$i]->usr_id == $usr_id) { $hit_num = _JLMS_YES_ALT_TITLE; break; // by DEN } } $view_is_course = 1; if ($JLMS_CONFIG->get('flms_integration', 1)) { $params = new JLMSParameters($crs_options[$key]->params); $view_is_course = $params->get('show_in_report', 1); } if ($view_is_course) { $text_to_csv .= $hit_num; if ($count != count($courses)) { $text_to_csv .= ','; } } } $text_to_csv .= "\n"; $zzz++; } break; case 'report_scorm': $rows = $courses; $array_zagolovk = array(); $array_zagolovk[] = 'Username'; $array_zagolovk[] = 'Name'; $array_zagolovk[] = 'Email'; $array_zagolovk[] = 'Course Name'; $array_zagolovk[] = 'Course ID'; $array_zagolovk[] = 'Date'; $array_zagolovk[] = 'Score'; $array_zagolovk[] = 'Course Status'; $zagolovk = implode(',', $array_zagolovk); $text_to_csv = ''; if ($zagolovk) { $text_to_csv .= $zagolovk . "\n"; } else { $text_to_csv .= "\n"; } foreach ($rows as $row) { $text = array(); $text[] = $row->username; $text[] = JLMS_processCSVField($row->name); $text[] = $row->email; $text[] = JLMS_processCSVField($row->course_name); $text[] = JLMS_processCSVField($row->lpath_name); if (isset($row->scorm_data) && $row->scorm_data->status) { if ($row->scorm_data->end) { $date_end = date("Y-m-d H:i:s", $row->scorm_data->end); $text[] = JLMS_dateToDisplay($date_end); } else { $text[] = ''; } } else { $text[] = ''; } if (isset($row->scorm_data)) { $text[] = $row->scorm_data->score; } else { $text[] = ''; } if ($row->course_status) { $text[] = 'Completed'; } else { $text[] = 'Incompleted'; } $text_to_csv .= implode(",", $text); $text_to_csv .= "\n"; } break; } global $task; if ($task == 'report_access') { $tmpl_name = 'access_report'; $prefix_title = str_replace("_", " ", $tmpl_name); } else { if ($task == 'report_certif') { $tmpl_name = 'completion_report'; $prefix_title = str_replace("_", " ", $tmpl_name); } else { if ($task == 'report_grade') { $tmpl_name = 'user_report'; $prefix_title = str_replace("_", " ", $tmpl_name); } else { if ($task == 'report_scorm') { $tmpl_name = 'scorm_report'; $prefix_title = str_replace("_", " ", $tmpl_name); } } } } $ug_name = $tmpl_name . '_' . date('dMY'); if (preg_match('/Opera(\\/| )([0-9].[0-9]{1,2})/', $_SERVER['HTTP_USER_AGENT'])) { $UserBrowser = "Opera"; } elseif (preg_match('/MSIE ([0-9].[0-9]{1,2})/', $_SERVER['HTTP_USER_AGENT'])) { $UserBrowser = "IE"; } else { $UserBrowser = ''; } header("Content-type: application/csv"); header('Expires: ' . gmdate('D, d M Y H:i:s') . ' GMT'); header("Content-Length: " . strlen(trim($text_to_csv))); header('Content-Disposition: attachment; filename="' . $ug_name . '.csv"'); if ($UserBrowser == 'IE') { header('Cache-Control: must-revalidate, post-check=0, pre-check=0'); header('Pragma: public'); } else { header('Pragma: no-cache'); } echo $text_to_csv; exit; }