public static function create_shibb_persistent_id_field_if_missing()
{
$db_name = Database::get_main_database();
$sql = "SELECT * FROM {$db_name}.user LIMIT 1";
$result = Database::query($sql);
$row = mysql_fetch_assoc($result);
$exists = array_key_exists('shibb_persistent_id', $row);
if ($exists) {
return false;
}
$sql = "ALTER table {$db_name}.user ADD COLUMN shibb_persistent_id varchar(255) NULL DEFAULT NULL;";
$result = api_sql_query($sql);
return (bool) $result;
}
/**
* Creates a link button with contextual JS list within all the documents
*
* @modified 2010.10.12 - adding css classes and simple a tag
* @return string
*/
function get_documents()
{
global $_course, $charset;
$tbl_doc = Database::get_course_table(TABLE_DOCUMENT);
$sql_doc = "\n\t\t\t\t\tSELECT *\n\t\t\t\t\tFROM " . $tbl_doc . "\n\t\t\t\t\tWHERE\n\t\t\t\t\t\tpath NOT LIKE '%_DELETED_%'\n\t\t\t\t\tORDER BY filetype DESC";
$res_doc = api_sql_query($sql_doc, __FILE__, __LINE__);
$document_lang_var = api_convert_encoding(get_lang('LpSelectDocument'), $charset, api_get_system_encoding());
$return = '<a href="#" onclick="javascript:popup(\'popUpDiv0\');" class="big_button four_buttons rounded grey_border select_doc_button">' . $document_lang_var . '</a>';
$doc_lang_var = api_convert_encoding(get_lang('Documents'), $charset, api_get_system_encoding());
$close_lang_var = api_convert_encoding(get_lang('Close'), $charset, api_get_system_encoding());
$nodocuments_lang_var = api_convert_encoding(get_lang('NoDocuments'), $charset, api_get_system_encoding());
$return .= '<div id="popUpDiv0" class="popUpDiv author_popup gradient rounded_10 grey_border" style="display:none;">' . '<span class="title">' . $doc_lang_var . '</span>' . '<a href="#" class="close" onclick="popup(\'popUpDiv0\')">' . $close_lang_var . '</a>';
$return .= '<div id="resDoc" class="content">';
$resources = api_store_result($res_doc);
$resources_sorted = array();
// if you want to debug it, I advise you to do "echo" on the eval statements
foreach ($resources as $resource) {
$resource_paths = explode('/', $resource['path']);
array_shift($resource_paths);
$path_to_eval = $last_path = '';
$is_file = false;
foreach ($resource_paths as $key => $resource_path) {
if (strpos($resource_path, '.') === false && $key != count($resource_paths) - 1) {
// it's a folder
$path_to_eval .= '["' . $resource_path . '"]["files"]';
} else {
if (strpos($resource_path, '.') !== false) {
$is_file = true;
}
}
$last_path = $resource_path;
}
if ($is_file) {
eval('$resources_sorted' . $path_to_eval . '[' . $resource['id'] . '] = "' . $last_path . '";');
} else {
eval('$resources_sorted' . $path_to_eval . '["' . $last_path . '"]["id"]=' . $resource['id'] . ';');
}
}
$resources_sorted = array_reverse($resources_sorted, true);
$return .= $this->write_resources_tree($resources_sorted);
$return .= '</div>';
if (Database::num_rows($res_doc) == 0) {
$return .= '<div class="lp_resource_element">' . $nodocuments_lang_var . '</div>';
}
$return .= '</div>';
// end of div#popUpDiv0
return $return;
}
if (!$lp_found) {
error_log('New LP - No learnpath given for delete', 0);
require 'lp_list.php';
} else {
/// added for cloud
if (api_get_setting('enableScormCloud', 'enableScormCloud') == 'true') {
require_once '../scorm_cloud/scorm_cloud.lib.php';
$lp_id = $_REQUEST['lp_id'];
$cid = $_REQUEST['cidReq'];
if (cloud_isCloudCourse($cid, $lp_id)) {
if (api_get_setting('enableCloudCourseSharing', 'existingCloudCourseUse') == 'true') {
cloud_deleteRegistrations($cid, $lp_id);
} else {
$tbl_scorm_cloud = Database::get_main_table('scorm_cloud');
$sql_cloud_get_course = "Select cloud_course_id from {$tbl_scorm_cloud} " . "WHERE course_code = '{$cid}' AND lp_id = {$lp_id} ";
$res = api_sql_query($sql_cloud_get_course, __FILE__, __LINE__);
if (Database::num_rows($res) > 0) {
$row = Database::fetch_array($res);
$cloud_courseId = $row['cloud_course_id'];
}
//echo $cloud_courseId.'<br/>';
cloud_deleteCourse($cloud_courseId);
}
}
}
//////////////
$_SESSION['refresh'] = 1;
//remove lp from homepage if it is there
$_SESSION['oLP']->toggle_visibility((int) $_GET['lp_id'], 'i');
$_SESSION['oLP']->delete(null, (int) $_GET['lp_id'], 'remove');
api_session_unregister('oLP');
/**
* Retrieves courses that the user is subscribed to
*
* @param int $user_id
* @return array
*/
function retrieve_user_courses($user_id = null)
{
if (is_null($user_id)) {
global $_user;
$user_id = $_user['user_id'];
}
$course_table = Database::get_main_table(TABLE_MAIN_COURSE);
$user_course_table = Database::get_main_table(TABLE_MAIN_COURSE_USER);
$user_id = intval($user_id);
$sql_select_courses = "SELECT course.code k, course.visual_code vc, course.subscribe subscr, course.unsubscribe unsubscr,\n course.title i, course.tutor_name t, course.db_name db, course.directory dir, course_rel_user.status status,\n\t\t\t\t course_rel_user.sort sort, course_rel_user.user_course_cat user_course_cat\n\t\t FROM {$course_table} course, {$user_course_table} course_rel_user\n\t\t WHERE course.id = course_rel_user.c_id\n\t\t AND course_rel_user.user_id = {$user_id}\n\t\t ORDER BY course_rel_user.sort ASC";
$result = array();
$resultset = api_sql_query($sql_select_courses, __FILE__, __LINE__);
while ($row = Database::fetch_array($resultset)) {
$code = $row['k'];
$result[$code] = array('db' => $row['db'], 'code' => $code, 'visual_code' => $row['vc'], 'title' => $row['i'], 'directory' => $row['dir'], 'status' => $row['status'], 'tutor' => $row['t'], 'subscribe' => $row['subscr'], 'unsubscribe' => $row['unsubscr'], 'sort' => $row['sort'], 'user_course_category' => $row['user_course_cat']);
}
return $result;
}
$temp = array(get_lang('NoDocumentDownloaded'), '');
$csv_content[] = $temp;
}
}
echo '</table></div>';
echo '<div class="clear"></div>';
/***************************
* LINKS
***************************/
echo '<div class="admin_section">
<h4>
<img src="../img/link.gif" align="absbottom"> ' . get_lang('LinksMostClicked') . '
</h4>
<table class="data_table">';
$sql = "SELECT cl.title, cl.url,count(DISTINCT sl.links_user_id), count(cl.title) as count_visits\n FROM {$TABLETRACK_LINKS} AS sl, {$TABLECOURSE_LINKS} AS cl\n WHERE sl.links_link_id = cl.id\n AND sl.links_cours_id = '{$_cid}'\n GROUP BY cl.title, cl.url\n\t\t\tORDER BY count_visits DESC\n\t\t\tLIMIT 0, 3";
$rs = api_sql_query($sql, __FILE__, __LINE__);
if ($export_csv) {
$temp = array(get_lang('LinksMostClicked'), '');
$csv_content[] = array('', '');
$csv_content[] = $temp;
}
if (Database::num_rows($rs) > 0) {
while ($row = Database::fetch_array($rs)) {
echo ' <tr>
<td>' . $row['title'] . '</td>
<td align="right">' . $row['count_visits'] . ' ' . get_lang('Clicks') . '</td>
</tr>';
if ($export_csv) {
$temp = array($row['title'], $row['count_visits'] . ' ' . get_lang('Clicks'));
$csv_content[] = $temp;
}
$disp = explode(",", $updateRecordsArray);
$cntdispid = sizeof($disp);
for ($i = 0; $i < $cntdispid; $i++) {
$dispid = substr($disp[$i], 8, strlen($disp[$i]));
$query = "UPDATE {$tbl_lp_item} SET display_order = " . $listingCounter . " WHERE id = " . $dispid;
$result = api_sql_query($query, __FILE__, __LINE__);
$listingCounter = $listingCounter + 1;
}
} elseif ($dispaction == "sortlp") {
$listingCounter = 1;
$disp = explode(",", $updateRecordsArray);
$cntdispid = sizeof($disp);
for ($i = 0; $i < $cntdispid; $i++) {
$dispid = substr($disp[$i], 8, strlen($disp[$i]));
$query = "UPDATE {$lp_table} SET display_order = " . $listingCounter . " WHERE id = " . $dispid;
$result = api_sql_query($query, __FILE__, __LINE__);
$listingCounter = $listingCounter + 1;
}
echo '<script type="text/javascript">window.location.href="lp_controller.php?' . api_get_cidReq() . '&action=course"</script>';
}
}
$lp_controller_touched = 1;
$lp_found = false;
if (isset($_SESSION['lpobject'])) {
if ($debug > 0) {
error_log('New LP - SESSION[lpobject] is defined', 0);
}
$oLP = unserialize($_SESSION['lpobject']);
if (is_object($oLP)) {
if ($debug > 0) {
error_log('New LP - oLP is object', 0);
function is_teacher($course_code)
{
global $_user;
$tbl_course_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
$sql = "SELECT 1 FROM {$tbl_course_user} WHERE user_id='" . $_user["user_id"] . "' AND course_code='" . Database::escape_string($course_code) . "' AND status='1'";
$result = api_sql_query($sql, __FILE__, __LINE__);
if (Database::result($result) != 1) {
return true;
} else {
return false;
}
}
/**
* Delete a template
*
* @param integer $id the id of the template that has to be deleted
*
* @author Patrick Cool <*****@*****.**>, Ghent University, Belgium
* @version August 2008
* @since Dokeos 1.8.6
*/
function delete_template($id)
{
// first we remove the image
$table_system_template = Database::get_main_table('system_template');
$sql = "SELECT * FROM {$table_system_template} WHERE id = '" . Database::escape_string($id) . "'";
$result = api_sql_query($sql, __FILE__, __LINE__);
$row = Database::fetch_array($result);
if (!empty($row['image'])) {
unlink(api_get_path(SYS_PATH) . 'home/default_platform_document/' . $row['image']);
}
// now we remove it from the database
$sql = "DELETE FROM {$table_system_template} WHERE id = '" . Database::escape_string($id) . "'";
$result = api_sql_query($sql, __FILE__, __LINE__);
// display a feedback message
Display::display_confirmation_message('TemplateDeleted');
}
function cloud_updateLMSRegistrationResults($regid, $lp_id)
{
$ScormService = cloud_getScormEngineService();
$regService = $ScormService->getRegistrationService();
$resultXmlString = $regService->GetRegistrationResult($regid, 2, 0);
$resXml = simplexml_load_string($resultXmlString);
//echo 'xml: '.$resultXmlString.':endXml<br/>';
$course_id = substr($regid, 0, strpos($regid, '_'));
$lp_view_id = substr($regid, strpos($regid, '_') + 1);
$activity = $resXml->registrationreport->activity[0];
$report = $root_activities[0];
$act_ref = $activity["id"];
$act_title = $activity->title;
//required type in order to report score
$act_type = 'sco';
$act_start_time = cloud_getCourseLaunchTime($regid);
$lp_view_table = Database::get_course_table_from_code($course_id, TABLE_LP_VIEW);
$sql = "SELECT user_id FROM {$lp_view_table} WHERE id = {$lp_view_id}";
$view_res = api_sql_query($sql, __FILE__, __LINE__);
$userRow = Database::fetch_array($view_res);
$user_id = $userRow['user_id'];
$act_total_time = cloud_getTotalCourseTime($course_id, $lp_id, $user_id);
//echo $act_total_time.'<br/>';
$act_score = cloud_getCourseScore($course_id, $lp_id, $user_id);
//get the status
$satisfied = $activity->success;
$completed = $activity->complete;
if ($satisfied == 'true') {
$act_status = 'passed';
} elseif ($completed == 'true' && $satisfied == 'false') {
$act_status = 'failed';
} else {
$act_status = 'incomplete';
}
//First add an item if necessary, otherwise get the id
$table_lp_item = Database::get_course_table_from_code($course_id, TABLE_LP_ITEM);
$sql_check_lp_item = "Select count(*) AS num_items,id FROM {$table_lp_item} WHERE lp_id = {$lp_id}";
$result = api_sql_query($sql_check_lp_item, __FILE__, __LINE__);
$num_row = Database::fetch_array($result);
//echo $num_row['num_items'];
if ($num_row['num_items'] == 0) {
$sql_item = "INSERT INTO {$table_lp_item} (lp_id,item_type,ref,title,path,parent_item_id) " . "VALUES({$lp_id},'{$act_type}','{$act_ref}','{$act_title}','',0)";
//echo $sql_item.'<br>';
$res_ins = api_sql_query($sql_item, __FILE__, __LINE__);
// $lp_item_id = Database :: get_last_insert_id();
} else {
$lp_item_id = $num_row['id'];
}
$table_lp_item_view = Database::get_course_table_from_code($course_id, TABLE_LP_ITEM_VIEW);
$sql_check_lp_item_view = "Select count(*)AS num_items FROM {$table_lp_item_view} WHERE lp_view_id = {$lp_view_id}";
$result_view = api_sql_query($sql_check_lp_item_view, __FILE__, __LINE__);
$num_row_view = Database::fetch_array($result_view);
if ($num_row_view['num_items'] == 0) {
$sql_item_view = "INSERT INTO {$table_lp_item_view} (lp_item_id,lp_view_id,start_time,total_time,score,status) " . "VALUES({$lp_item_id},{$lp_view_id},{$act_start_time},{$act_total_time},{$act_score},'{$act_status}')";
} else {
$sql_item_view = "Update {$table_lp_item_view} " . "SET total_time = {$act_total_time}, " . "start_time = {$act_start_time}, " . " score = {$act_score}," . " status = '{$act_status}'" . " WHERE lp_item_id = {$lp_item_id} " . "AND lp_view_id = {$lp_view_id}";
}
//echo $sql_item_view.'<br>';
$res_ins = api_sql_query($sql_item_view, __FILE__, __LINE__);
$scos = $resXml->xpath('//activity[not(fn:exists(children/activity))]');
//echo $scos[0]->title;
$totScos = count($scos);
$totCompl = 0;
foreach ($scos as $sco) {
if ($sco->completed == 'true') {
$totCompl++;
}
}
$progress = round($totCompl / $totScos * 100, 2);
//echo $progress;
$table_lp_view = Database::get_course_table_from_code($course_id, TABLE_LP_VIEW);
$sql_update_lp_view = "UPDATE {$table_lp_view} SET progress={$progress} WHERE id={$lp_view_id}";
$res_ins = api_sql_query($sql_update_lp_view, __FILE__, __LINE__);
}
$allResults = $courseService->GetCourseList();
echo '<div class="row"><div class="form_header">SCORM Cloud Packages</div></div>';
echo '<table class="manager_table" cellspacing="0">';
echo '<tr class="tblHeader"><td>Package Title</td><td>Total Cloud Registrations</td><td>' . get_lang('MyCourses') . ' (Trainer)</td><td> </td></tr>';
foreach ($allResults as $course) {
$ccid = $course->getCourseId();
echo '<td>';
echo $course->getTitle();
echo '</td><td>';
echo $course->getNumberOfRegistrations();
echo '</td>';
$tbl_scorm_cloud = Database::get_main_table('scorm_cloud');
$tbl_course = Database::get_main_table('course');
//select distinct c.title from course c inner join scorm_cloud sc on sc.course_code = c.code where sc.cloud_course_id = '4a8d6623c554c'
$sql_cloud = "select distinct c.title,c.tutor_name from {$tbl_course} c inner join {$tbl_scorm_cloud} sc on sc.course_code = c.code " . "where sc.cloud_course_id = '{$ccid}'";
$res = api_sql_query($sql_cloud, __FILE__, __LINE__);
echo '<td>';
while ($row = Database::fetch_array($res)) {
echo $row['title'] . ' (' . $row['tutor_name'] . ')<br/>';
}
echo '</td><td>';
if (Database::num_rows($res) > 0) {
echo '<span class="link_disable">Delete Package</span>';
} else {
echo '<a href="cloudManager.php?action=delete&ccid=' . $course->getCourseId() . '">Delete Package</a>';
}
echo '</td></tr>';
}
echo '</table>';
echo '
<br/>
/**
* Get general information about the exercise performance of the user
* the total obtained score (all the score on all the questions)
* the maximum score that could be obtained
* the number of questions answered
* the success percentage
*
* @param integer $user_id the id of the user
* @param string $course_code the course code
*
* @author Patrick Cool <*****@*****.**>, Ghent University, Belgium
* @version Dokeos 1.8.6
* @since November 2008
*/
function exercises_results($user_id, $course_code)
{
$questions_answered = 0;
$sql = 'SELECT exe_result , exe_weighting
FROM ' . Database::get_statistic_table(TABLE_STATISTIC_TRACK_E_EXERCICES) . "\r\n\t\t\t\t\tWHERE exe_cours_id = '" . Database::escape_string($course_code) . "'\r\n\t\t\t\t\tAND exe_user_id = '" . Database::escape_string($user_id) . "'";
$result = api_sql_query($sql, __FILE__, __LINE__);
$score_obtained = 0;
$score_possible = 0;
$questions_answered = 0;
while ($row = Database::fetch_array($result)) {
$score_obtained += $row['exe_result'];
$score_possible += $row['exe_weighting'];
$questions_answered++;
}
if ($score_possible != 0) {
$percentage = round($score_obtained / $score_possible * 100, 2);
}
return array('score_obtained' => $score_obtained, 'score_possible' => $score_possible, 'questions_answered' => $questions_answered, 'percentage' => $percentage);
}
$res_name = api_sql_query($check_name, __FILE__, __LINE__);
while (Database::num_rows($res_name)) {
//there is already one such name, update the current one a bit
$i++;
$newtitle = $title . ' - ' . $i;
$check_name = "SELECT * FROM {$tbl_lp} WHERE name = '{$newtitle}'";
//if($this->debug>2){error_log('New LP - Checking the name for new LP: '.$check_name,0);}
$res_name = api_sql_query($check_name, __FILE__, __LINE__);
}
$title = $newtitle;
//echo $title;
$type = 2;
$get_max = "SELECT MAX(display_order) FROM {$tbl_lp}";
$res_max = api_sql_query($get_max, __FILE__, __LINE__);
if (Database::num_rows($res_max) < 1) {
$dsp = 1;
} else {
$row = Database::fetch_array($res_max);
$dsp = $row[0] + 1;
}
$sql_insert = "INSERT INTO {$tbl_lp} " . "(lp_type,name,description,path,default_view_mod," . "default_encoding,display_order,content_maker," . "content_local) " . "VALUES ({$type},'{$title}','','','scormcloud'," . "'UTF-8','{$dsp}','{$content_maker}'," . "'remote')";
//echo $sql_insert;
$res_insert = api_sql_query($sql_insert, __FILE__, __LINE__);
$id = Database::get_last_insert_id();
$tbl_scorm_cloud = Database::get_main_table('scorm_cloud');
$sql_cloud_insert = "INSERT INTO {$tbl_scorm_cloud} " . "(course_code,lp_id,cloud_course_id)" . "VALUES ('{$cid}',{$id},'{$courseId}')";
$res_insert2 = api_sql_query($sql_cloud_insert, __FILE__, __LINE__);
$dialogtype = 'confirmation';
header('location: ../newscorm/lp_controller.php?action=list&dialog_box=' . $msg . '&dialogtype=' . $dialogtype);
exit;
}
$nameTools = get_lang("cloudUpload");
$interbreadcrumb[] = array("url" => "../newscorm/lp_controller.php?action=list", "name" => get_lang("Learnpath"));
Display::display_header($nameTools, "Path");
echo '<div class="actions">';
echo '<a href="../newscorm/lp_controller.php?cidReq=' . $_course['sysCode'] . '">' . Display::return_icon('scorm.gif', get_lang('ReturnToLearningPaths')) . ' ' . get_lang('ReturnToLearningPaths') . '</a>';
echo '</div>';
$tbl_scorm_cloud = Database::get_main_table('scorm_cloud');
$sql_cloud_get_course = "Select cloud_course_id from {$tbl_scorm_cloud} " . "WHERE course_code = '{$cid}' AND lp_id = {$lp_id} ";
//echo $sql_cloud_get_course;
$res = api_sql_query($sql_cloud_get_course, __FILE__, __LINE__);
if (Database::num_rows($res) > 0) {
$row = Database::fetch_array($res);
$cloud_courseId = $row['cloud_course_id'];
//echo $cloud_courseId;
}
$ScormService = cloud_getScormEngineService();
$courseService = $ScormService->getCourseService();
$cssUrl = api_get_path(WEB_PATH) . 'main/scorm_cloud/packageproperties.css';
$url = $courseService->GetPropertyEditorUrl($cloud_courseId, $cssUrl);
//echo $url;
$lp_table = Database::get_course_table('lp');
$sql = "SELECT name FROM {$lp_table} WHERE id = {$lp_id}";
$name_res = api_sql_query($sql, __FILE__, __LINE__);
$row = Database::fetch_array($name_res);
$title = $row['name'];
echo "<img src=\"../scorm_cloud/img/cloud_icon_sm.gif\" border=\"0\" title=\"" . get_lang('cloudViewProps') . "\">" . '<span style="font-size:125%;position:relative;top:-5px;margin-left:5px;">' . $title . '</span>';
echo "<div style=''>";
echo "<iframe src='{$url}' width='100%' height='550px' frameborder='0' framepadding='0' ></iframe>";
echo "</div>";
// footer
Display::display_footer();
