function JLMS_GB_getUsersGrades($id, $uids, &$rows, &$lists, $cycle = 0)
{
$db =& JFactory::getDbo();
$JLMS_ACL =& JLMSFactory::getACL();
$is_teacher = $JLMS_ACL->isTeacher();
$uids_str = implode(',', $uids);
if (count($uids)) {
$query = "SELECT a.*, b.scale_name FROM #__lms_gradebook as a LEFT JOIN #__lms_gradebook_scale as b ON a.gb_points=b.id AND b.course_id = {$id} WHERE a.course_id = '" . $id . "' AND a.user_id IN ({$uids_str})";
$db->SetQuery($query);
$gb_rows = $db->LoadObjectList();
$query = "SELECT * FROM #__lms_certificate_users WHERE course_id = '" . $id . "' AND user_id IN ({$uids_str})";
$db->SetQuery($query);
$crt_rows = $db->LoadObjectList();
} else {
$crt_rows = array();
$gb_rows = array();
}
$query = "SELECT * FROM #__lms_gradebook_scale WHERE course_id = {$id} ORDER BY ordering, scale_name";
$db->SetQuery($query);
$scale_rows = $db->LoadObjectList();
if (count($uids) == 1) {
if (count($crt_rows) > 1) {
$del_ids_tmp = array();
$del_ids = array();
foreach ($crt_rows as $crt_row) {
if ($crt_row->crt_date == '0000-00-00 00:00:00') {
$del_ids_tmp[] = $crt_row->id;
}
}
if (count($del_ids) == count($crt_rows)) {
$del_ii = 1;
while ($del_ii < count($del_ids_tmp)) {
$del_ids[] = $del_ids_tmp[$del_ii];
$del_ii++;
}
} else {
$del_ids = $del_ids_tmp;
}
$del_ids_str = implode(',', $del_ids);
$query = "DELETE FROM #__lms_certificate_users WHERE id IN ({$del_ids_str}) AND course_id = '" . $id . "' AND user_id IN ({$uids_str}) AND crt_date = '0000-00-00 00:00:00'";
$db->SetQuery($query);
$db->query();
}
}
$query = "SELECT * FROM #__lms_gradebook_items WHERE course_id = '" . $id . "' ORDER BY ordering, gbi_name";
$db->SetQuery($query);
$irows = $db->LoadObjectList();
$query = "SELECT * FROM #__lms_learn_paths WHERE course_id = '" . $id . "' AND item_id <> '0' AND item_id <> '' AND published = '1' ORDER BY ordering";
$db->SetQuery($query);
$scorm_rows = $db->LoadObjectList();
$scorm_ans = array();
$scorm_n_ans = array();
if (count($scorm_rows)) {
$scids = array();
$scn_ids = array();
$scrows_i = 0;
foreach ($scorm_rows as $scorm_row) {
$tmp_params = new JLMSParameters($scorm_row->lp_params);
if ($tmp_params->get('show_in_gradebook', 1)) {
if ($scorm_row->lp_type == 1 || $scorm_row->lp_type == 2) {
$scn_ids[] = $scorm_row->item_id;
} else {
$scids[] = $scorm_row->item_id;
}
}
$scorm_rows[$scrows_i]->show_in_gradebook = $tmp_params->get('show_in_gradebook', 1);
$scrows_i++;
}
if ($cycle) {
$query = "SELECT params FROM #__lms_courses WHERE id = '" . $id . "'";
//Atention!
$db->setQuery($query);
//Atention!
$course_params = $db->loadResult();
//Atention!
} else {
global $JLMS_CONFIG;
$course_params = $JLMS_CONFIG->get('course_params');
}
$params = new JLMSParameters($course_params);
if (count($scids) || count($scn_ids)) {
require_once _JOOMLMS_FRONT_HOME . "/includes/lms_scorm.lib.php";
if (count($scids)) {
//nothing here
$scorm_ans =& JLMS_GetSCORM_userResults($uids, $scids);
}
if (count($scn_ids)) {
$scorm_n_ans = array();
$uids_groups = array();
$uids_group = array();
//group user IDs by 5
$uid_i = 1;
foreach ($uids as $uid) {
$uids_group[] = $uid;
$uid_i++;
if ($uid_i > 5) {
$uids_groups[] = $uids_group;
$uids_group = array();
$uid_i = 1;
}
}
if (count($uids_group)) {
$uids_groups[] = $uids_group;
}
foreach ($uids_groups as $uids5) {
foreach ($scn_ids as $scn_id) {
$scn_ids_new_array = array();
$scn_ids_new_array[] = $scn_id;
$scorm_n_ans1 =& JLMS_Get_N_SCORM_userResults($uids5, $scn_ids_new_array, $params->get('track_type', 0));
$scorm_n_ans = array_merge($scorm_n_ans, $scorm_n_ans1);
}
}
}
}
}
$query = "SELECT * FROM #__lms_quiz_t_quiz WHERE course_id = '" . $id . "' AND c_gradebook = 1 ORDER BY c_title";
$db->SetQuery($query);
$quiz_rows = $db->LoadObjectList();
$quiz_ans = array();
if (count($quiz_rows)) {
if (count($uids)) {
$query = "SELECT a.*, b.c_full_score FROM #__lms_quiz_results as a, #__lms_quiz_t_quiz as b WHERE a.course_id = '" . $id . "'" . "\n AND a.quiz_id = b.c_id AND a.user_id IN ( {$uids_str} ) ORDER BY a.user_id, a.quiz_id";
$db->SetQuery($query);
$quiz_ans = $db->LoadObjectList();
} else {
$quiz_ans = array();
}
}
$i = 0;
while ($i < count($rows)) {
$p = array();
foreach ($irows as $irow) {
$pp = new stdClass();
$pp->gbi_id = $irow->id;
$pp->gbi_type = 1;
// 1 - gb_item, 2 - scorm; 3 - quiz;
$pp->user_grade = '-';
$pp->scale_id = 0;
$p[] = $pp;
}
$j = 0;
while ($j < count($gb_rows)) {
if ($gb_rows[$j]->user_id == $rows[$i]->user_id) {
$k = 0;
while ($k < count($p)) {
if ($p[$k]->gbi_id == $gb_rows[$j]->gbi_id && $p[$k]->gbi_type == 1) {
$p[$k]->user_grade = $gb_rows[$j]->scale_name ? $gb_rows[$j]->scale_name : '-';
//$gb_rows[$j]->gb_points;
$p[$k]->scale_id = $gb_rows[$j]->gb_points ? $gb_rows[$j]->gb_points : '-';
}
$k++;
}
}
$j++;
}
$rows[$i]->user_certificate = 0;
$rows[$i]->user_certificate_date = '';
$j = 0;
while ($j < count($crt_rows)) {
if ($crt_rows[$j]->user_id == $rows[$i]->user_id) {
$rows[$i]->user_certificate = $crt_rows[$j]->crt_option;
$rows[$i]->user_certificate_date = $crt_rows[$j]->crt_date;
}
$j++;
}
$rows[$i]->grade_info = $p;
//scorm
$p = array();
foreach ($scorm_rows as $srow) {
$pp = new stdClass();
$pp->gbi_id = $srow->item_id;
$pp->lp_type = $srow->lp_type;
$pp->gbi_type = 2;
// 1 - gb_item, 2 - scorm; 3 - quiz;
$pp->user_grade = '-';
$pp->user_pts = 0;
$pp->user_status = -1;
$p[] = $pp;
}
$j = 0;
while ($j < count($scorm_ans)) {
if ($scorm_ans[$j]->user_id == $rows[$i]->user_id) {
$k = 0;
while ($k < count($p)) {
if ($p[$k]->gbi_id == $scorm_ans[$j]->content_id && $p[$k]->gbi_type == 2 && !$p[$k]->lp_type) {
/*$p[$k]->user_grade = $scorm_ans[$j]->score;
$p[$k]->user_status = $scorm_ans[$j]->status;*/
$user_per = $scorm_ans[$j]->score > 100 ? 100 : $scorm_ans[$j]->score;
$sc_i = count($scale_rows) - 1;
while ($sc_i >= 0) {
if ($scale_rows[$sc_i]->min_val <= $user_per && $scale_rows[$sc_i]->max_val >= $user_per) {
$p[$k]->user_grade = $scale_rows[$sc_i]->scale_name;
break;
}
$sc_i--;
}
$p[$k]->user_pts = $scorm_ans[$j]->score;
$p[$k]->user_status = $scorm_ans[$j]->status;
}
$k++;
}
}
$j++;
}
$j = 0;
while ($j < count($scorm_n_ans)) {
if ($scorm_n_ans[$j]->user_id == $rows[$i]->user_id) {
$k = 0;
while ($k < count($p)) {
if ($p[$k]->gbi_id == $scorm_n_ans[$j]->content_id && $p[$k]->gbi_type == 2 && ($p[$k]->lp_type == 1 || $p[$k]->lp_type == 2)) {
/*$p[$k]->user_grade = $scorm_n_ans[$j]->score;
$p[$k]->user_status = $scorm_n_ans[$j]->status;*/
$user_per = $scorm_n_ans[$j]->score > 100 ? 100 : $scorm_n_ans[$j]->score;
//$user_per = intval($quiz_ans[$j]->user_score * 100 / $quiz_ans[$j]->c_full_score);
$sc_i = count($scale_rows) - 1;
while ($sc_i >= 0) {
if ($scale_rows[$sc_i]->min_val <= $user_per && $scale_rows[$sc_i]->max_val >= $user_per) {
$p[$k]->user_grade = $scale_rows[$sc_i]->scale_name;
break;
}
$sc_i--;
}
$p[$k]->user_pts = $scorm_n_ans[$j]->score;
$p[$k]->user_status = $scorm_n_ans[$j]->status;
}
$k++;
}
}
$j++;
}
$rows[$i]->scorm_info = $p;
//quizzes
$p = array();
foreach ($quiz_rows as $qrow) {
$pp = new stdClass();
$pp->gbi_id = $qrow->c_id;
$pp->allow_user_pdf_print = $qrow->c_enable_print;
$pp->gbi_type = 3;
// 1 - gb_item, 2 - scorm; 3 - quiz;
$pp->user_grade = '-';
$pp->user_pts = 0;
$pp->user_pts_full = '0';
$pp->user_status = -1;
$pp->user_passed = -1;
$pp->user_score = 0;
$pp->quiz_max_score = 0;
/*Integration Plugin Percentiles*/
$_JLMS_PLUGINS =& JLMSFactory::getPlugins();
$_JLMS_PLUGINS->loadBotGroup('system');
$data_plugin = new stdClass();
$data_plugin->course_id = $rows[$i]->course_id;
$data_plugin->quiz_id = $qrow->c_id;
$data_plugin->user_id = $rows[$i]->user_id;
if ($out_plugin = $_JLMS_PLUGINS->trigger('onQuizFinish', array($data_plugin))) {
if (count($out_plugin)) {
$percentiles = $out_plugin[0];
if ($percentiles->percent >= 0) {
$percent = $percentiles->percent . '%';
$pp->user_percentile = $percent;
}
}
}
/*Integration Plugin Percentiles*/
$p[] = $pp;
}
$j = 0;
while ($j < count($quiz_ans)) {
if ($quiz_ans[$j]->user_id == $rows[$i]->user_id) {
$k = 0;
while ($k < count($p)) {
if ($p[$k]->gbi_id == $quiz_ans[$j]->quiz_id && $p[$k]->gbi_type == 3) {
if ($quiz_ans[$j]->quiz_max_score) {
$user_per = intval($quiz_ans[$j]->user_score * 100 / $quiz_ans[$j]->quiz_max_score);
} elseif ($quiz_ans[$j]->c_full_score) {
$user_per = intval($quiz_ans[$j]->user_score * 100 / $quiz_ans[$j]->c_full_score);
} else {
if ($quiz_ans[$j]->user_score) {
// ...strange ... HOW?
$user_per = 100;
} else {
$user_per = 0;
}
}
$sc_i = count($scale_rows) - 1;
while ($sc_i >= 0) {
if ($scale_rows[$sc_i]->min_val <= $user_per && $scale_rows[$sc_i]->max_val >= $user_per) {
$p[$k]->user_grade = $scale_rows[$sc_i]->scale_name;
break;
}
$sc_i--;
}
$p[$k]->user_pts = $quiz_ans[$j]->user_score;
$p[$k]->user_pts_full = $quiz_ans[$j]->user_score . '/' . $quiz_ans[$j]->quiz_max_score;
$p[$k]->user_status = $quiz_ans[$j]->user_passed;
$p[$k]->user_passed = $quiz_ans[$j]->user_passed;
$p[$k]->user_score = $quiz_ans[$j]->user_score;
$p[$k]->quiz_max_score = $quiz_ans[$j]->quiz_max_score;
}
$k++;
}
}
$j++;
}
$rows[$i]->quiz_info = $p;
$i++;
}
global $my;
//--------------------------
for ($i = 0; $i < count($scorm_rows); $i++) {
if ($scorm_rows[$i]->lp_type == 2) {
$query = "SELECT scorm_package FROM #__lms_n_scorm WHERE id = '" . $scorm_rows[$i]->item_id . "'";
$db->SetQuery($query);
$scorm_package = $db->LoadResult();
$query = "SELECT id FROM #__lms_n_scorm WHERE scorm_package = '" . $scorm_package . "' AND course_id = 0";
$db->SetQuery($query);
$scorm_lib_id = $db->LoadResult();
$outer_doc = null;
$query = "SELECT outdoc_share, owner_id, allow_link FROM #__lms_outer_documents WHERE file_id = '" . $scorm_lib_id . "' AND folder_flag = 3";
$db->SetQuery($query);
$outer_doc = $db->LoadObject();
if (is_object($outer_doc) && isset($outer_doc->allow_link) && $outer_doc->allow_link == 1) {
} else {
unset($scorm_rows[$i]);
}
}
}
$mas = array();
foreach ($scorm_rows as $k => $v) {
$mas[] = $scorm_rows[$k];
}
unset($scorm_rows);
$scorm_rows = $mas;
//---------------------------------
if ($cycle) {
$lists['gb_rows'][] = $irows;
$lists['sc_rows'][] = $scorm_rows;
$lists['quiz_rows'][] = $quiz_rows;
} else {
$lists['gb_rows'] = $irows;
$lists['sc_rows'] = $scorm_rows;
$lists['quiz_rows'] = $quiz_rows;
}
}
function JLMS_Get_N_SCORM_SCO_userResults(&$user_ids, $scorm_id, $ttype = 0)
{
global $JLMS_DB;
$uids_str = implode(',', $user_ids);
$query = "SELECT id FROM #__lms_n_scorm_scoes WHERE scorm = {$scorm_id}";
$JLMS_DB->SetQuery($query);
$scorm_scoes_ids = $JLMS_DB->LoadResultArray();
if (empty($scorm_scoes_ids)) {
$scorm_scoes_ids = array(0);
}
$scorm_scoes_ids_str = implode(',', $scorm_scoes_ids);
$query = "SELECT * FROM #__lms_n_scorm_scoes_track WHERE scormid = {$scorm_id} AND userid IN ( {$uids_str} ) AND scoid IN ( {$scorm_scoes_ids_str} )" . "\n AND " . $JLMS_DB->NameQuote("element") . " <> 'cmi.suspend_data'" . "\n AND " . $JLMS_DB->NameQuote("element") . " <> 'cmi.location'" . "\n ORDER BY userid, attempt";
$JLMS_DB->SetQuery($query);
$scorm_ans_pre = $JLMS_DB->LoadObjectList();
// tracking type checks
$scorm_ids = array();
$scorm_ids[] = $scorm_id;
$ttt =& JLMS_Get_N_SCORM_userResults($user_ids, $scorm_ids, $ttype);
// best results by attempts (in the case of $ttype)
$scorm_ans_pre_real = array();
for ($i = 0; $i < count($scorm_ans_pre); $i++) {
$do_add = false;
foreach ($ttt as $tt1) {
if ($tt1->user_id == $scorm_ans_pre[$i]->userid && $tt1->content_id == $scorm_ans_pre[$i]->scormid && $tt1->attempt == $scorm_ans_pre[$i]->attempt) {
$do_add = true;
break;
}
}
if ($do_add) {
$p = new stdClass();
$p = $scorm_ans_pre[$i];
$scorm_ans_pre_real[] = $p;
}
}
$query = "SELECT identifier as sco_identifier, scorm, id, title FROM #__lms_n_scorm_scoes WHERE scorm = {$scorm_id} AND scormtype <> '' ORDER BY id";
$JLMS_DB->SetQuery($query);
$scorm_scos = $JLMS_DB->LoadObjectList();
$scos_count = count($scorm_scos);
for ($i = 0; $i < $scos_count; $i++) {
$scorm_scos[$i]->track_data = array();
foreach ($scorm_ans_pre_real as $sap) {
if ($sap->scoid == $scorm_scos[$i]->id) {
$p = new stdClass();
$p->element = $sap->element;
$p->value = $sap->value;
$p->user_id = $sap->userid;
$scorm_scos[$i]->track_data[] = $p;
}
}
}
return $scorm_scos;
/*$scorm_ans = array();
if ($scos_count == 1) {
$h = 0;
$first_step = true;
$p = new stdClass();$p->status = 1;$p->score = 0;$p->user_id = 0;$p->content_id = 0;
while ( $h < count($scorm_ans_pre) ) {
if ( $first_step || ($p->user_id != $scorm_ans_pre[$h]->userid) || ($p->content_id != $scorm_ans_pre[$h]->scoid) ) {
if ($first_step) {
$first_step = false;
} else {
$scorm_ans[] = $p;
}
$p = new stdClass();
$p->status = 1;
$p->score = 0;
$p->sco_count = $scos_count;
$p->sco_identifier = '';
$p->sco_title = '';
$p->user_id = $scorm_ans_pre[$h]->userid;
$p->content_id = $scorm_ans_pre[$h]->scoid;
}
$p->score = $p->score + intval($scorm_ans_pre[$h]->score);
if ($scorm_ans_pre[$h]->status != 'completed' && $scorm_ans_pre[$h]->status != 'passed') { $p->status = 0; }
if ($h == (count($scorm_ans_pre) - 1)) { $scorm_ans[] = $p; }
$h ++;
}
} else {
$h = 0;
$first_step = true;
//$p = new stdClass();$p->status = 1;$p->score = 0;$p->user_id = 0;$p->content_id = 0;
while ( $h < count($scorm_ans_pre) ) {
$p = new stdClass();
$p->status = 1;
$p->score = 0;
$p->sco_count = $scos_count;
$p->sco_identifier = $scorm_ans_pre[$h]->sco_identifier;
$p->sco_title = $scorm_ans_pre[$h]->sco_title;
$p->user_id = $scorm_ans_pre[$h]->user_id;
$p->content_id = $scorm_ans_pre[$h]->content_id;
$p->score = intval($scorm_ans_pre[$h]->score);
if ($scorm_ans_pre[$h]->status != 'completed' && $scorm_ans_pre[$h]->status != 'passed') { $p->status = 0; }
$scorm_ans[] = $p;
$h ++;
}
}
return $scorm_ans;*/
}
function JLMS_sreportScorm($option, $is_full = 0)
{
global $JLMS_DB, $JLMS_CONFIG, $JLMS_SESSION, $task, $option, $my, $Itemid;
$JLMS_ACL =& JLMSFactory::getACL();
$view = mosGetParam($_REQUEST, 'view', '');
if ($view == 'csv' || $view == 'xls') {
$is_full = 1;
}
$limit = intval(mosGetParam($_GET, 'limit', $JLMS_SESSION->get('list_limit', $JLMS_CONFIG->get('list_limit'))));
$JLMS_SESSION->set('list_limit', $limit);
$limitstart = intval(mosGetParam($_GET, 'limitstart', 0));
$filt_group = intval(mosGetParam($_REQUEST, 'filt_group', $JLMS_SESSION->get('filt_group', 0)));
$filt_cat = intval(mosGetParam($_REQUEST, 'filt_cat', 0));
$filt_course_id = intval(mosGetParam($_REQUEST, 'filt_course_id', 0));
$start_date = mosGetParam($_REQUEST, 'start_date', "");
$end_date = mosGetParam($_REQUEST, 'end_date', "");
$s_date_db = '';
$start_date = $start_date == "-" ? "" : $start_date;
if ($start_date) {
$start_date = JLMS_dateToDB($start_date);
$s_date = explode('-', $start_date);
$s_date_db = date("Y-m-d", mktime(0, 0, 0, $s_date[1], $s_date[2], $s_date[0]));
}
$e_date_db = '';
$end_date = $end_date == "-" ? "" : $end_date;
if ($end_date) {
$end_date = JLMS_dateToDB($end_date);
$e_date = explode('-', $end_date);
$e_date_db = date("Y-m-d", mktime(23, 59, 0, $e_date[1], $e_date[2], $e_date[0]));
}
$lists = array();
//FLMS multicat
$levels = array();
if ($JLMS_CONFIG->get('multicat_use', 0)) {
$query = "SELECT * FROM #__lms_course_cats_config ORDER BY id";
$JLMS_DB->setQuery($query);
$levels = $JLMS_DB->loadObjectList();
if (count($levels) == 0) {
for ($i = 0; $i < 5; $i++) {
if ($i > 0) {
$levels[$i]->cat_name = _JLMS_COURSES_COURSES_GROUPS;
} else {
$levels[$i]->cat_name = _JLMS_COURSES_COURSES_GROUPS;
}
}
}
$level_id = array();
for ($i = 0; $i < count($levels); $i++) {
if ($i == 0) {
$level_id[$i] = intval(mosGetParam($_REQUEST, 'filter_id_' . $i . '', $JLMS_SESSION->get('FLMS_filter_id_' . $i . '', 0)));
$_REQUEST['filter_id_' . $i] = $level_id[$i];
$JLMS_SESSION->set('FLMS_filter_id_' . $i . '', $level_id[$i]);
} else {
$level_id[$i] = intval(mosGetParam($_REQUEST, 'filter_id_' . $i . '', $JLMS_SESSION->get('FLMS_filter_id_' . $i . '', 0)));
$_REQUEST['filter_id_' . $i] = $level_id[$i];
$JLMS_SESSION->set('FLMS_filter_id_' . $i . '', $level_id[$i]);
}
if ($i == 0) {
$parent_id[$i] = 0;
} else {
$parent_id[$i] = $level_id[$i - 1];
}
if ($i == 0 || $parent_id[$i]) {
//(Max): extra requests
$query = "SELECT count(id) FROM `#__lms_course_cats` WHERE parent = '" . $parent_id[$i] . "' ORDER BY c_category";
$JLMS_DB->setQuery($query);
$groups = $JLMS_DB->loadResult();
if ($groups == 0) {
$level_id[$i] = 0;
$JLMS_SESSION->set('FLMS_filter_id_' . $i . '', $level_id[$i]);
}
}
}
for ($i = 0; $i < count($levels); $i++) {
if ($i > 0 && $level_id[$i - 1] == 0) {
$level_id[$i] = 0;
$_REQUEST['filter_id_' . $i] = $level_id[$i];
$JLMS_SESSION->set('FLMS_filter_id_' . $i . '', $level_id[$i]);
$parent_id[$i] = 0;
} elseif ($i == 0 && $level_id[$i] == 0) {
$level_id[$i] = 0;
$_REQUEST['filter_id_' . $i] = $level_id[$i];
$JLMS_SESSION->set('FLMS_filter_id_' . $i . '', $level_id[$i]);
$parent_id[$i] = 0;
}
}
$javascript = 'onclick="javascript:read_filter();" onchange="javascript:write_filter();submitFormView(\'\');"';
if (class_exists('JFactory')) {
$user = JLMSFactory::getUser();
$my->id = $user->id;
}
$lists['user_id'] = $my->id;
$query1 = "SELECT group_id FROM `#__lms_users_in_global_groups` WHERE user_id = '" . $my->id . "'";
$JLMS_DB->setQuery($query1);
$user_group_ids = $JLMS_DB->loadResultArray();
$categories_reporting = array();
$name_categories_reporting = array();
for ($i = 0; $i < count($levels); $i++) {
if ($i == 0 || $parent_id[$i]) {
//(Max): extra requests
if ($parent_id[$i] == 0 && !$JLMS_ACL->CheckPermissions('lms', 'create_course')) {
$query = "SELECT * FROM `#__lms_course_cats` WHERE `parent` = '0'";
$query .= "\n AND (";
if (count($user_group_ids)) {
$query .= "( `restricted` = 1 AND ( `groups` LIKE '%|{$user_group_ids['0']}|%'";
for ($i1 = 1; $i1 < count($user_group_ids); $i1++) {
$query .= "\n OR `groups` like '%|{$user_group_ids[$i1]}|%'";
}
$query .= "\n ) ) \n OR ";
}
$query .= "(`restricted` = 0 )) ";
$query .= "\n ORDER BY `c_category`";
} else {
$query = "SELECT * FROM `#__lms_course_cats` WHERE parent = '" . $parent_id[$i] . "' ORDER BY c_category";
}
$JLMS_DB->setQuery($query);
$groups = $JLMS_DB->loadObjectList();
if ($parent_id[$i] && $i > 0 && count($groups)) {
$type_level[$i][] = mosHTML::makeOption(0, ' ');
foreach ($groups as $group) {
$type_level[$i][] = mosHTML::makeOption($group->id, $group->c_category);
if ($group->id == $level_id[$i]) {
$name_categories_reporting[] = $levels[$i]->cat_name;
$categories_reporting[] = $group->c_category;
}
}
$lists['filter_' . $i . ''] = mosHTML::selectList($type_level[$i], 'filter_id_' . $i . '', 'class="inputbox" size="1" style="width: 100%;" ' . $javascript, 'value', 'text', $level_id[$i]);
//onchange="document.location.href=\''. $link_multi .'\';"
} elseif ($i == 0) {
$type_level[$i][] = mosHTML::makeOption(0, ' ');
foreach ($groups as $group) {
$type_level[$i][] = mosHTML::makeOption($group->id, $group->c_category);
if ($group->id == $level_id[$i]) {
$name_categories_reporting[] = $levels[$i]->cat_name;
$categories_reporting[] = $group->c_category;
}
}
$lists['filter_' . $i . ''] = mosHTML::selectList($type_level[$i], 'filter_id_' . $i . '', 'class="inputbox" size="1" style="width: 100%;" ' . $javascript, 'value', 'text', $level_id[$i]);
//onchange="document.location.href=\''. $link_multi .'\';"
}
}
}
$reporting_header['name_categories'] = $name_categories_reporting;
$reporting_header['categories'] = $categories_reporting;
}
//FLMS multicat
global $JLMS_DB, $my;
if (class_exists('Jfactory')) {
$user = JLMSFactory::getUser();
$my->id = $user->id;
}
$is_ceo = $JLMS_ACL->isStaff();
$courses = array();
if ($JLMS_ACL->isTeacher()) {
$courses = $JLMS_CONFIG->get('teacher_in_courses', array());
} else {
if ($is_ceo) {
$query = "SELECT user_id FROM #__lms_user_parents WHERE parent_id = '" . $my->id . "'";
$JLMS_DB->setQuery($query);
$users = $JLMS_DB->loadResultArray();
if (count($users)) {
$query = "SELECT course_id FROM #__lms_users_in_groups WHERE user_id IN (" . implode(",", $users) . ")";
$JLMS_DB->setQuery($query);
$courses = $JLMS_DB->loadResultArray();
}
}
}
if (count($courses)) {
$where = "";
if ($JLMS_CONFIG->get('multicat_use', 0)) {
//NEW MUSLTICATS
// $tmp_level = array();
$last_catid = 0;
$tmp_cats_filter = JLMS_getFilterMulticategories($last_catid);
/*
$i=0;
foreach($_REQUEST as $key=>$item){
if(preg_match('#filter_id_(\d+)#', $key, $result)){
if($item){
$tmp_level[$i] = $result;
$last_catid = $item;
$i++;
}
}
}
$filt_cat = $last_catid;
$query = "SELECT * FROM #__lms_course_cats ORDER BY id";
$JLMS_DB->setQuery($query);
$all_cats = $JLMS_DB->loadObjectList();
$tmp_cats_filter = array();
$children = array();
foreach($all_cats as $cat){
$pt = $cat->parent;
$list = @$children[$pt] ? $children[$pt] : array();
array_push($list, $cat->id);
$children[$pt] = $list;
}
$tmp_cats_filter[0] = $last_catid;
$i=1;
foreach($children as $key=>$childs){
if($last_catid == $key){
foreach($children[$key] as $v){
if(!in_array($v, $tmp_cats_filter)){
$tmp_cats_filter[$i] = $v;
$i++;
}
}
}
}
foreach($children as $key=>$childs){
if(in_array($key, $tmp_cats_filter)){
foreach($children[$key] as $v){
if(!in_array($v, $tmp_cats_filter)){
$tmp_cats_filter[$i] = $v;
$i++;
}
}
}
}
$tmp_cats_filter = array_unique($tmp_cats_filter);
*/
$catids = implode(",", $tmp_cats_filter);
if ($last_catid && count($tmp_cats_filter)) {
$where .= "\n AND ( cat_id IN (" . $catids . ")";
if ($JLMS_CONFIG->get('sec_cat_use', 0)) {
foreach ($tmp_cats_filter as $tmp_cats_filter_one) {
$where .= "\n OR sec_cat LIKE '%|" . $tmp_cats_filter_one . "|%'";
}
}
$where .= "\n )";
}
//NEW MUSLTICATS
}
$courses_str = implode(",", $courses);
$query = "SELECT id FROM #__lms_courses WHERE id IN(" . $courses_str . ")" . $where . "\n ORDER BY id";
$JLMS_DB->setQuery($query);
$courses = $JLMS_DB->loadResultArray();
$javascript2 = 'onchange="javascript:submitFormView();"';
$query = "SELECT id as value, course_name as text FROM #__lms_courses WHERE id IN (" . $courses_str . ")" . $where . "\n ORDER BY " . ($JLMS_CONFIG->get('lms_courses_sortby') ? "ordering" : "course_name");
$JLMS_DB->setQuery($query);
$list_courses = $JLMS_DB->loadObjectList();
$f_courses = array();
$f_courses[] = mosHTML::makeOption(0, ' ');
$f_courses = array_merge($f_courses, $list_courses);
$lists['filt_course'] = mosHTML::selectList($f_courses, 'filt_course_id', 'class="inputbox" size="1" style="width: 100%;" ' . $javascript2, 'value', 'text', $filt_course_id);
$g_items = array();
$g_items[] = mosHTML::makeOption(0, _JLMS_ATT_FILTER_ALL_GROUPS);
if ($JLMS_CONFIG->get('use_global_groups', 1)) {
if ($is_ceo) {
$cid = $users;
} else {
if (!count($courses)) {
$courses = array(0);
}
$query = "SELECT user_id FROM #__lms_users_in_groups WHERE course_id IN (" . implode(',', $courses) . ")";
$JLMS_DB->setQuery($query);
$cid = $JLMS_DB->loadResultArray();
}
if (!$cid) {
$cid = array(-1);
}
$query = "SELECT group_id FROM #__lms_users_in_global_groups WHERE user_id IN (" . implode(',', $cid) . ")";
$JLMS_DB->setQuery($query);
$gid = $JLMS_DB->loadResultArray();
if (!$gid) {
$gid = array(-1);
}
$query = "SELECT distinct id AS value, ug_name AS text FROM #__lms_usergroups WHERE id IN (" . implode(',', $gid) . ") AND course_id = 0 ORDER BY text";
//course id check just in case))
$JLMS_DB->setQuery($query);
$groups = $JLMS_DB->loadObjectList();
} else {
if (!count($courses)) {
$courses = array(0);
}
$query = "SELECT distinct a.id as value, a.ug_name as text FROM #__lms_usergroups as a, #__lms_users_in_groups as b" . "\n WHERE a.course_id IN (" . implode(',', $courses) . ") AND b.group_id = a.id ORDER BY a.ug_name";
$JLMS_DB->SetQuery($query);
$groups = $JLMS_DB->LoadObjectList();
}
$g_items = array_merge($g_items, $groups);
$lists['filt_group'] = mosHTML::selectList($g_items, 'filt_group', 'class="inputbox" size="1" style="width: 100%;" ' . $javascript2, 'value', 'text', $filt_group);
if ($filt_group) {
if ($JLMS_CONFIG->get('use_global_groups', 1)) {
$query = "SELECT user_id" . "\n FROM #__lms_users_in_global_groups ugg" . "\n WHERE 1" . "\n AND group_id = '" . $filt_group . "'";
$JLMS_DB->setQuery($query);
$filt_grp_users = $JLMS_DB->loadResultArray();
} else {
$query = "SELECT user_id" . "\n FROM #__lms_user_in_groups" . "\n WHERE 1" . "\n AND group_id = '" . $filt_group . "'";
$JLMS_DB->setQuery($query);
$filt_grp_users = $JLMS_DB->loadResultArray();
}
}
$query = "SELECT COUNT(*)" . "\n FROM #__lms_courses as c, #__lms_users_in_groups as ug" . "\n LEFT JOIN #__lms_certificate_users as cu ON cu.course_id = ug.course_id AND cu.user_id = ug.user_id" . "\n, #__lms_learn_paths as lp, #__users as u" . "\n WHERE 1" . "\n AND c.id = ug.course_id" . "\n AND c.id = lp.course_id" . "\n AND lp.item_id <> 0" . "\n AND lp.lp_type = 1" . "\n AND lp.published = 1" . "\n AND ug.user_id = u.id" . "\n AND c.id IN (" . implode(",", $courses) . ")" . ($filt_course_id ? "\n AND c.id = '" . $filt_course_id . "'" : "") . ($filt_group ? "\n AND ug.user_id IN (" . implode(",", $filt_grp_users) . ")" : "") . ($is_ceo ? "\n AND ug.user_id IN (" . implode(",", $users) . ")" : "");
$JLMS_DB->setQuery($query);
$total = $JLMS_DB->loadResult();
require_once _JOOMLMS_FRONT_HOME . DS . "includes" . DS . "classes" . DS . "lms.pagination.php";
$pageNav = new JLMSPageNav($total, $limitstart, $limit);
$query = "SELECT" . "\n c.id as course_id, c.course_name" . "\n, lp.id, lp.lpath_name, lp.item_id" . "\n, u.id as user_id, u.username, u.name, u.email" . "\n, cu.crt_option, cu.crt_date" . "\n FROM #__lms_courses as c, #__lms_users_in_groups as ug" . "\n LEFT JOIN #__lms_certificate_users as cu ON cu.course_id = ug.course_id AND cu.user_id = ug.user_id" . "\n, #__lms_learn_paths as lp, #__users as u" . "\n WHERE 1" . "\n AND c.id = ug.course_id" . "\n AND c.id = lp.course_id" . "\n AND lp.item_id <> 0" . "\n AND lp.lp_type = 1" . "\n AND lp.published = 1" . "\n AND ug.user_id = u.id" . "\n AND c.id IN (" . implode(",", $courses) . ")" . ($filt_course_id ? "\n AND c.id = '" . $filt_course_id . "'" : "") . ($filt_group ? "\n AND ug.user_id IN (" . implode(",", $filt_grp_users) . ")" : "") . ($is_ceo ? "\n AND ug.user_id IN (" . implode(",", $users) . ")" : "") . "\n ORDER BY " . ($JLMS_CONFIG->get('lms_courses_sortby') ? "c.ordering" : "c.course_name") . "\n, u.username, lp.lpath_name";
if (strlen($s_date_db) || strlen($e_date_db)) {
$JLMS_DB->setQuery($query);
} else {
if ($is_full) {
$JLMS_DB->setQuery($query);
} else {
$JLMS_DB->setQuery($query, $limitstart, $limit);
}
}
$scorm_list = $JLMS_DB->loadObjectList();
$user_scorms = array();
foreach ($scorm_list as $scorm) {
$user_scorms[$scorm->user_id][] = $scorm->item_id;
}
require_once _JOOMLMS_FRONT_HOME . "/includes/lms_scorm.lib.php";
$result = array();
foreach ($user_scorms as $user_id => $scorms) {
$uids = array();
$uids[] = $user_id;
$scn_ids = array();
$scn_ids = $scorms;
$result[$user_id]->scorm_data =& JLMS_Get_N_SCORM_userResults($uids, $scn_ids, 0);
}
$new_scorm_list = array();
foreach ($scorm_list as $n => $scorm) {
$new_scorm_list[$n] = $scorm;
$new_scorm_list[$n]->course_status = 0;
if (isset($scorm->crt_option) && $scorm->crt_option) {
$new_scorm_list[$n]->course_status = 1;
}
if (isset($result[$scorm->user_id]->scorm_data)) {
$tmp = $result[$scorm->user_id]->scorm_data;
foreach ($tmp as $t) {
if ($t->content_id == $scorm->item_id) {
$scorm_data = new stdClass();
$scorm_data = $t;
$scorm_data->start = $t->at_start;
$scorm_data->end = $t->scn_timemodified;
$new_scorm_list[$n]->scorm_data = $scorm_data;
}
}
}
}
if (strlen($s_date_db) || strlen($e_date_db)) {
$start = strtotime($s_date_db);
$end = strtotime($e_date_db);
$tmp = array();
foreach ($new_scorm_list as $scorm) {
$add = false;
if ($start) {
$add = false;
if (isset($scorm->scorm_data->end) && $scorm->scorm_data->end && $start <= $scorm->scorm_data->end) {
$add = true;
}
}
if ($end) {
$add = false;
if (isset($scorm->scorm_data->end) && $scorm->scorm_data->end && $end >= $scorm->scorm_data->end) {
$add = true;
}
}
if ($add) {
$tmp[] = $scorm;
}
}
if (count($tmp)) {
$new_scorm_list = array();
$new_scorm_list = $tmp;
}
$total = count($new_scorm_list);
$pageNav = new JLMSPageNav($total, $limitstart, $limit);
if (!$is_full) {
$tmp = array();
for ($i = $limitstart - 1; $i < $limitstart + $limit; $i++) {
if (isset($new_scorm_list[$i])) {
$tmp[] = $new_scorm_list[$i];
}
}
if (count($tmp)) {
$new_scorm_list = array();
$new_scorm_list = $tmp;
}
}
}
if (count($new_scorm_list)) {
$rowz = $new_scorm_list;
}
if ($view == 'csv') {
JLMS_REP_exportCsv(array(), array(), array(), $rowz, $pageNav, $lists, $levels, $filt_cat, $filt_group, $option, 1);
} else {
JLMS_reports_html::JLMS_sreportScorm($option, $rowz, $start_date, $end_date, $pageNav, $lists, $levels, $filt_cat, $filt_group, $is_full);
}
}
}
