function JLMS_REP_exportXLS($hits, $tot_hits = array(), $users, $courses, $reporting_header)
{
global $JLMS_DB, $JLMS_CONFIG, $task, $option;
$results = array();
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();
$results['title_courses'] = array();
$i = 0;
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) {
$results['title_courses'][$i]['course_name'] = $c_name;
$i++;
}
}
$results['results'] = array();
$results['results_hit'] = array();
$j = 0;
foreach ($users as $usr_id) {
$JLMS_DB->setQuery('SELECT username, name, email FROM #__users WHERE id =' . $usr_id);
$usrname = $JLMS_DB->LoadObject();
$results['results'][$j]['username'] = $usrname->username;
$results['results'][$j]['name'] = $usrname->name;
//$results['results'][$j]['email'] = $usrname->email;
$count = 0;
$k = 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
} else {
if ($hits[$i]->c_id == $course_id && $usr_id == 'total') {
$hit_num = $hits[$i]->hits;
break;
}
}
}
$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) {
$results['results_hit'][$j][$k] = $hit_num;
}
$k++;
}
$j++;
}
$results_total_hits = array();
$k = 0;
foreach ($courses as $course_id) {
$results_total_hits[$k]['hits'] = 0;
foreach ($tot_hits as $hit) {
if ($course_id == $hit->c_id) {
$results_total_hits[$k]['hits'] = $hit->hits;
}
}
$k++;
}
$results['results_total_hits'] = $results_total_hits;
break;
case 'report_certif':
$users_str = implode(',', $users);
$courses_str = implode(',', $courses);
if (!$courses_str) {
$courses_str = '0';
}
if (!$users_str) {
$users_str = '0';
}
$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();
$results['title_courses'] = array();
$i = 0;
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) {
$results['title_courses'][$i]['course_name'] = $c_name;
$i++;
}
}
$results['results'] = array();
$results['results_hit'] = array();
$j = 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;
$results['results'][$j]['username'] = $usrname->username;
$results['results'][$j]['name'] = $usrname->name;
//$results['results'][$j]['email'] = $usrname->email;
$count = 0;
$k = 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) {
$results['results_hit'][$j][$k] = $hit_num;
$k++;
}
}
$j++;
}
break;
case 'report_grade':
$course_info = array();
$course_info_hits = array();
$title_headers = array();
$data_grade = array();
$rows = array();
if (isset($reporting_header['data_grade']['rowz']) && isset($reporting_header['data_grade']['lists']) && count($reporting_header['data_grade']['rowz']) && count($reporting_header['data_grade']['lists'])) {
$rows = $reporting_header['data_grade']['rowz'];
$lists = $reporting_header['data_grade']['lists'];
for ($i = 0; $i < count($rows); $i++) {
$row = $rows[$i];
$course_info[$i]['course_name'] = $row->course_name;
$course_info[$i]['hits'] = $lists['hits'][$i];
/*
$course_info[$i][] = $row->course_name;
$course_info[$i][] = $lists['hits'][$i];
$course_info_hits[$i][] = $lists['hits'][$i];
*/
$title_headers[$i][] = _JLMS_REPORTS_CONCLUSION_ROW;
$data_grade[$i][] = $row->user_certificate ? _CMN_YES : _CMN_NO;
$sc_num = 0;
$sc_num2 = 0;
foreach ($lists['sc_rows'][$i] as $sc_row) {
if ($sc_row->show_in_gradebook) {
$title_headers[$i][] = $sc_row->lpath_name;
$sc_num++;
$j = 0;
while ($j < count($row->scorm_info)) {
if ($row->scorm_info[$j]->gbi_id == $sc_row->item_id) {
if ($sc_num2 < $sc_num) {
if ($row->scorm_info[$j]->user_status == -1) {
$data_grade[$i][] = '-';
} else {
$user_status = '';
$user_status .= $row->scorm_info[$j]->user_status ? _CMN_YES : _CMN_NO;
$user_status .= isset($row->scorm_info[$j]->user_grade) ? ' ' . $row->scorm_info[$j]->user_grade : '';
$user_status .= isset($row->scorm_info[$j]->user_pts) ? ' (' . $row->scorm_info[$j]->user_pts . ')' : '';
$data_grade[$i][] = $user_status;
}
$sc_num2++;
}
}
$j++;
}
}
}
foreach ($lists['quiz_rows'][$i] as $quiz_row) {
$title_headers[$i][] = $quiz_row->c_title;
$j = 0;
while ($j < count($row->quiz_info)) {
if ($row->quiz_info[$j]->gbi_id == $quiz_row->c_id) {
if ($row->quiz_info[$j]->user_status == -1) {
$data_grade[$i][] = '-';
} else {
$user_status = '';
// $user_status .= $row->quiz_info[$j]->user_status ? _CMN_YES : _CMN_NO;
// $user_status .= ' '.$row->quiz_info[$j]->user_grade.' ';
// $user_status .= '('.$row->quiz_info[$j]->user_pts_full.')';
$user_status = JLMS_showQuizStatus($row->quiz_info[$j], '', 1);
$data_grade[$i][] = $user_status;
}
}
$j++;
}
}
foreach ($lists['gb_rows'][$i] as $gb_row) {
$title_headers[$i][] = $gb_row->gbi_name;
}
$j = 0;
while ($j < count($row->grade_info)) {
$data_grade[$i][] = $row->grade_info[$j]->user_grade;
$j++;
}
}
}
$results['course_info'] = $course_info;
$results['title_headers'] = $title_headers;
$results['data_grade'] = $data_grade;
$results['data'] = array();
foreach ($course_info as $n => $ci) {
$results['data'][$n][] = $title_headers[$n];
$results['data'][$n][] = $data_grade[$n];
}
break;
}
//echo '<pre>';
//print_r($results);
//print_r($reporting_header);
//echo '</pre>';
//die;
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);
}
}
}
JLMS_reporting::exportXLS($results, $reporting_header, $tmpl_name, $prefix_title);
}
