/**
* Constructor
*/
public function __construct()
{
//Table definitions
$this->tbl_global_agenda = Database::get_main_table(TABLE_MAIN_SYSTEM_CALENDAR);
$this->tbl_personal_agenda = Database::get_user_personal_table(TABLE_PERSONAL_AGENDA);
$this->tbl_course_agenda = Database::get_course_table(TABLE_AGENDA);
$this->table_repeat = Database::get_course_table(TABLE_AGENDA_REPEAT);
//Setting the course object if we are in a course
$this->course = null;
$courseInfo = api_get_course_info();
if (!empty($courseInfo)) {
$this->course = $courseInfo;
}
$this->setSessionId(api_get_session_id());
$this->setSenderId(api_get_user_id());
$this->events = array();
// Event colors
$this->event_platform_color = 'red';
//red
$this->event_course_color = '#458B00';
//green
$this->event_group_color = '#A0522D';
//siena
$this->event_session_color = '#00496D';
// kind of green
$this->event_personal_color = 'steel blue';
//steel blue
}
/**
* This function shows all the forms that are needed form adding /editing a new personal agenda item
* when there is no $id passed in the function we are adding a new agenda item, if there is a $id
* we are editing
* attention: we have to check that the student is editing an item that belongs to him/her
*/
function show_new_personal_item_form($id = "")
{
global $year, $MonthsLong;
$tbl_personal_agenda = Database::get_user_personal_table(TABLE_PERSONAL_AGENDA);
// we construct the default time and date data (used if we are not editing a personal agenda item)
//$today = getdate();
$current_date = api_strtotime(api_get_local_time());
$year = date('Y', $current_date);
$month = date('m', $current_date);
$day = date('d', $current_date);
$hours = date('H', $current_date);
$minutes = date('i', $current_date);
//echo date('Y', $current_date);
/*
$day = $today['mday'];
$month = $today['mon'];
$year = $today['year'];
$hours = $today['hours'];
$minutes = $today['minutes'];*/
$content = stripslashes($content);
$title = stripslashes($title);
// if an $id is passed to this function this means we are editing an item
// we are loading the information here (we do this after everything else
// to overwrite the default information)
if (strlen($id) > 0 && $id != strval(intval($id))) {
return false;
//potential SQL injection
}
if ($id != "") {
$sql = "SELECT date, title, text FROM " . $tbl_personal_agenda . " WHERE user='******' AND id='" . $id . "'";
$result = Database::query($sql);
$aantal = Database::num_rows($result);
if ($aantal != 0) {
$row = Database::fetch_array($result);
$row['date'] = api_get_local_time($row['date']);
$year = substr($row['date'], 0, 4);
$month = substr($row['date'], 5, 2);
$day = substr($row['date'], 8, 2);
$hours = substr($row['date'], 11, 2);
$minutes = substr($row['date'], 14, 2);
$title = $row['title'];
$content = $row['text'];
} else {
return false;
}
}
echo '<form method="post" action="myagenda.php?action=add_personal_agenda_item&id=' . $id . '" name="newedit_form">';
echo '<div id="newedit_form">';
echo '<h2>';
echo $_GET['action'] == 'edit_personal_agenda_item' ? get_lang("ModifyPersonalCalendarItem") : get_lang("AddPersonalCalendarItem");
echo '</h2>';
echo '<div>';
echo '<br/>';
echo '' . get_lang("Date") . ': ';
// ********** The form containing the days (0->31) ********** \\
echo '<select name="frm_day">';
// small loop for filling all the dates
// 2do: the available dates should be those of the selected month => february is from 1 to 28 (or 29) and not to 31
for ($i = 1; $i <= 31; $i++) {
// values have to have double digits
if ($i <= 9) {
$value = "0" . $i;
} else {
$value = $i;
}
// the current day is indicated with [] around the date
if ($value == $day) {
echo '<option value=' . $value . ' selected>' . $i . '</option>';
} else {
echo '<option value=' . $value . '>' . $i . '</option>';
}
}
echo '</select>';
// ********** The form containing the months (jan->dec) ********** \\
echo '<!-- month: january -> december -->';
echo '<select name="frm_month">';
for ($i = 1; $i <= 12; $i++) {
// values have to have double digits
if ($i <= 9) {
$value = "0" . $i;
} else {
$value = $i;
}
// the current month is indicated with [] around the month name
if ($value == $month) {
echo '<option value=' . $value . ' selected>' . $MonthsLong[$i - 1] . '</option>';
} else {
echo '<option value=' . $value . '>' . $MonthsLong[$i - 1] . '</option>';
}
}
echo '</select>';
// ********** The form containing the years ********** \\
echo '<!-- year -->';
echo '<select name="frm_year">';
echo '<option value=' . ($year - 1) . '>' . ($year - 1) . '</option>';
echo '<option value=' . $year . ' selected>' . $year . '</option>';
for ($i = 1; $i <= 5; $i++) {
$value = $year + $i;
echo '<option value=' . $value . '>' . $value . '</option>';
}
echo '</select> ';
echo "<a title=\"Kalender\" href=\"javascript:openCalendar('newedit_form', 'frm_')\">" . Display::return_icon('calendar_select.gif', get_lang('Select'), array('style' => 'vertical-align: middle;')) . "</a>";
echo ' ';
// ********** The form containing the hours (00->23) ********** \\
echo '<!-- time: hour -->';
echo get_lang("Time") . ': ';
echo '<select name="frm_hour">';
for ($i = 1; $i <= 24; $i++) {
// values have to have double digits
if ($i <= 9) {
$value = "0" . $i;
} else {
$value = $i;
}
// the current hour is indicated with [] around the hour
if ($hours == $value) {
echo '<option value=' . $value . ' selected>' . $value . '</option>';
} else {
echo '<option value=' . $value . '> ' . $value . ' </option>';
}
}
echo '</select>';
// ********** The form containing the minutes ********** \\
echo "<select name=\"frm_minute\">";
echo "<option value=\"" . $minutes . "\">" . $minutes . "</option>";
echo "<option value=\"00\">00</option>";
echo "<option value=\"05\">05</option>";
echo "<option value=\"10\">10</option>";
echo "<option value=\"15\">15</option>";
echo "<option value=\"20\">20</option>";
echo "<option value=\"25\">25</option>";
echo "<option value=\"30\">30</option>";
echo "<option value=\"35\">35</option>";
echo "<option value=\"40\">40</option>";
echo "<option value=\"45\">45</option>";
echo "<option value=\"50\">50</option>";
echo "<option value=\"55\">55</option>";
echo '</select>';
echo '</div><br/>';
// ********** The title field ********** \\
echo '<div>';
echo '' . get_lang('Title') . ' : <input type="text" name="frm_title" size="50" value="' . $title . '" />';
echo '</div>';
// ********** The text field ********** \\
echo '<br /><div class="formw">';
require_once api_get_path(LIBRARY_PATH) . "/fckeditor/fckeditor.php";
$oFCKeditor = new FCKeditor('frm_content');
$oFCKeditor->Width = '80%';
$oFCKeditor->Height = '200';
if (!api_is_allowed_to_edit(null, true)) {
$oFCKeditor->ToolbarSet = 'AgendaStudent';
} else {
$oFCKeditor->ToolbarSet = 'Agenda';
}
$oFCKeditor->Value = $content;
$return = $oFCKeditor->CreateHtml();
echo $return;
echo '</div>';
// ********** The Submit button********** \\
echo '<div>';
echo '<br /><button type="submit" class="add" name="Submit" value="' . get_lang('AddEvent') . '" >' . get_lang('AddEvent') . '</button>';
echo '</div>';
echo '</div>';
echo '</form>';
}
/**
* This function deletes a personal agenda item
* There is an additional check to make sure that one cannot delete an item that
* does not belong to him/her
*/
function delete_personal_agenda($id)
{
$tbl_personal_agenda = Database::get_user_personal_table(TABLE_PERSONAL_AGENDA);
if ($id != strval(intval($id))) {
return false;
//potential SQL injection
}
if ($id != '') {
$sql = "SELECT * FROM " . $tbl_personal_agenda . " WHERE user='******' AND id='" . $id . "'";
$result = Database::query($sql);
$aantal = Database::num_rows($result);
if ($aantal != 0) {
$sql = "DELETE FROM " . $tbl_personal_agenda . " WHERE user='******' AND id='" . $id . "'";
$result = Database::query($sql);
}
}
}
/**
* Retrieves all the agenda items from the table
* @author: Patrick Cool <*****@*****.**>, Ghent University
* @author Yannick Warnier <*****@*****.**> - cleanup
* @author Julio Montoya - Refactoring
* @param integer $month: the integer value of the month we are viewing
* @param integer $year: the 4-digit year indication e.g. 2005
* @return array
*/
function get_calendar_items($select_month, $select_year, $select_day = false)
{
$course_info = api_get_course_info();
$select_month = intval($select_month);
$select_year = intval($select_year);
if ($select_day) {
$select_day = intval($select_day);
}
if (!empty($select_month) && !empty($select_year)) {
$show_all_current = " AND MONTH(start_date) = {$select_month} AND year(start_date) = {$select_year}";
if ($select_day) {
$show_all_current .= ' AND DAY(start_date) = ' . $select_day;
}
$show_all_current_personal = " AND MONTH(date) = {$select_month} AND year(date) = {$select_year}";
if ($select_day) {
$show_all_current_personal .= ' AND DAY(date) = ' . $select_day;
}
} else {
$show_all_current = '';
$show_all_current_personal = '';
}
// database variables
$TABLEAGENDA = Database::get_course_table(TABLE_AGENDA);
$TABLE_ITEM_PROPERTY = Database::get_course_table(TABLE_ITEM_PROPERTY);
$group_memberships = GroupManager::get_group_ids($course_info['real_id'], api_get_user_id());
$repeats = array();
/* CONSTRUCT THE SQL STATEMENT */
// by default we use the id of the current user. The course administrator can see the agenda of other users by using the user / group filter
$user_id = api_get_user_id();
if (isset($_SESSION['user'])) {
$user_id = intval($_SESSION['user']);
}
$group_id = api_get_group_id();
$session_condition = api_get_session_condition(api_get_session_id());
if (api_is_allowed_to_edit(false, true) or api_get_course_setting('allow_user_edit_agenda') && !api_is_anonymous()) {
// A.1. you are a course admin with a USER filter
// => see only the messages of this specific user + the messages of the group (s)he is member of.
if (!empty($_SESSION['user'])) {
$group_memberships = GroupManager::get_group_ids($course_info['real_id'], $_SESSION['user']);
$show_user = true;
$new_group_memberships = array();
foreach ($group_memberships as $id) {
// did i have access to the same
$has_access = GroupManager::user_has_access(api_get_user_id(), $id, GroupManager::GROUP_TOOL_CALENDAR);
$result = GroupManager::get_group_properties($id);
if ($has_access && $result['calendar_state'] != '0') {
$new_group_memberships[] = $id;
}
}
$group_memberships = $new_group_memberships;
if (is_array($group_memberships) && count($group_memberships) > 0) {
$sql = "SELECT\n agenda.*, ip.visibility, ip.to_group_id, ip.insert_user_id, ip.ref\n FROM " . $TABLEAGENDA . " agenda, " . $TABLE_ITEM_PROPERTY . " ip\n WHERE agenda.id = ip.ref " . $show_all_current . "\n AND ip.tool='" . TOOL_CALENDAR_EVENT . "'\n AND ( ip.to_user_id = {$user_id} OR ip.to_group_id IN (0, " . implode(", ", $group_memberships) . ") )\n AND ip.visibility='1'\n {$session_condition}";
} else {
//AND ( ip.to_user_id=$user_id OR ip.to_group_id='0')
$sql = "SELECT agenda.*, ip.visibility, ip.to_group_id, ip.insert_user_id, ip.ref\n FROM " . $TABLEAGENDA . " agenda, " . $TABLE_ITEM_PROPERTY . " ip\n WHERE agenda.id = ip.ref " . $show_all_current . "\n AND ip.tool='" . TOOL_CALENDAR_EVENT . "'\n AND ( ip.to_user_id = {$user_id} )\n AND ip.visibility='1'\n {$session_condition}";
}
} elseif (!empty($_SESSION['group'])) {
if (!empty($group_id)) {
$result = GroupManager::get_group_properties($group_id);
$has_access = GroupManager::user_has_access(api_get_user_id(), $group_id, GroupManager::GROUP_TOOL_CALENDAR);
//echo '<pre>';print_R($result);
// lastedit
if (!$has_access || $result['calendar_state'] == '0') {
$group_id = 0;
}
}
$sql = "SELECT agenda.*, ip.visibility, ip.to_group_id, ip.insert_user_id, ip.ref\n FROM " . $TABLEAGENDA . " agenda, " . $TABLE_ITEM_PROPERTY . " ip\n WHERE agenda.id = ip.ref " . $show_all_current . "\n AND ip.tool='" . TOOL_CALENDAR_EVENT . "'\n AND ( ip.to_group_id={$group_id})\n AND ip.lastedit_type<>'CalendareventDeleted'\n {$session_condition}\n GROUP BY ip.ref";
//removed - > AND toolitemproperties.visibility='1'
} else {
// A.3.a you are a course admin without user or group filter but WITH studentview
// => see all the messages of all the users and groups without editing possibilities
if (isset($_GET['isStudentView']) && $_GET['isStudentView'] == 'true') {
$sql = "SELECT\n agenda.*, ip.visibility, ip.to_group_id, ip.insert_user_id, ip.ref\n FROM " . $TABLEAGENDA . " agenda, " . $TABLE_ITEM_PROPERTY . " ip\n WHERE agenda.id = ip.ref " . $show_all_current . "\n AND ip.tool='" . TOOL_CALENDAR_EVENT . "'\n AND ip.visibility='1'\n {$session_condition}\n GROUP BY ip.ref";
} else {
// A.3.b.1 you are a course admin without user or group filter and WITHOUT studentview (= the normal course admin view)
// => see all the messages of all the users and groups with editing possibilities
if (api_is_course_admin()) {
$sql = "SELECT\n agenda.*, ip.visibility, ip.to_group_id, ip.insert_user_id, ip.ref\n FROM " . $TABLEAGENDA . " agenda, " . $TABLE_ITEM_PROPERTY . " ip\n WHERE agenda.id = ip.ref " . $show_all_current . "\n AND ip.tool='" . TOOL_CALENDAR_EVENT . "'\n AND ( ip.visibility='0' OR ip.visibility='1')\n {$session_condition}\n GROUP BY ip.ref";
} else {
// A.3.b.2 you are a student with no group filter possibly showall
//when showing all the events we do not show the group events
//todo showing ALL events including the groups events that are available
$sql = "SELECT\n agenda.*, ip.visibility, ip.to_group_id, ip.insert_user_id, ip.ref\n FROM " . $TABLEAGENDA . " agenda, " . $TABLE_ITEM_PROPERTY . " ip\n WHERE agenda.id = ip.ref " . $show_all_current . "\n AND ip.tool='" . TOOL_CALENDAR_EVENT . "'\n AND ( ip.visibility='0' OR ip.visibility='1')\n {$session_condition}\n GROUP BY ip.ref";
}
}
}
} else {
if (is_array($group_memberships) && count($group_memberships) > 0) {
$sql = "SELECT\n agenda.*, ip.visibility, ip.to_group_id, ip.insert_user_id, ip.ref\n FROM " . $TABLEAGENDA . " agenda, " . $TABLE_ITEM_PROPERTY . " ip\n WHERE agenda.id = ip.ref " . $show_all_current . "\n AND ip.tool='" . TOOL_CALENDAR_EVENT . "'\n AND ( ip.to_user_id={$user_id} OR ip.to_group_id IN (0, " . implode(", ", $group_memberships) . ") )\n AND ip.visibility='1'\n {$session_condition}";
} else {
if (api_get_user_id()) {
$sql = "SELECT\n agenda.*, ip.visibility, ip.to_group_id, ip.insert_user_id, ip.ref\n FROM " . $TABLEAGENDA . " agenda, " . $TABLE_ITEM_PROPERTY . " ip\n WHERE agenda.id = ip.ref " . $show_all_current . "\n AND ip.tool='" . TOOL_CALENDAR_EVENT . "'\n AND ( ip.to_user_id={$user_id} OR ip.to_group_id='0')\n AND ip.visibility='1'\n {$session_condition}";
} else {
$sql = "SELECT\n agenda.*, ip.visibility, ip.to_group_id, ip.insert_user_id, ip.ref\n FROM " . $TABLEAGENDA . " agenda, " . $TABLE_ITEM_PROPERTY . " ip\n WHERE agenda.id = ip.ref " . $show_all_current . "\n AND ip.tool='" . TOOL_CALENDAR_EVENT . "'\n AND ip.to_group_id='0'\n AND ip.visibility='1'\n {$session_condition}";
}
}
}
// you are a student
$my_events = array();
$avoid_doubles = array();
$result = Database::query($sql);
//Course venets
while ($row = Database::fetch_array($result, 'ASSOC')) {
$row['calendar_type'] = 'course';
if (!in_array($row['id'], $avoid_doubles)) {
if (!empty($row['start_date']) && $row['start_date'] != '0000-00-00 00:00:00') {
$row['start_date'] = api_get_local_time($row['start_date']);
$row['start_date_tms'] = api_strtotime($row['start_date']);
}
if (!empty($row['end_date']) && $row['end_date'] != '0000-00-00 00:00:00') {
$row['end_date'] = api_get_local_time($row['end_date']);
$row['end_date_tms'] = api_strtotime($row['end_date']);
}
$my_events[] = $row;
$avoid_doubles[] = $row['id'];
}
}
//Check my personal calendar items
if (api_get_setting('allow_personal_agenda') == 'true' && empty($_SESSION['user']) && empty($_SESSION['group'])) {
$tbl_personal_agenda = Database::get_user_personal_table(TABLE_PERSONAL_AGENDA);
// 1. creating the SQL statement for getting the personal agenda items in MONTH view
$sql = "SELECT id, title, text as content , date as start_date, enddate as end_date, parent_event_id FROM " . $tbl_personal_agenda . "\n WHERE user='******' " . $show_all_current_personal;
$result = Database::query($sql);
while ($row = Database::fetch_array($result, 'ASSOC')) {
$row['calendar_type'] = 'personal';
if (!empty($row['start_date']) && $row['start_date'] != '0000-00-00 00:00:00') {
$row['start_date'] = api_get_local_time($row['start_date']);
$row['start_date_tms'] = api_strtotime($row['start_date']);
}
if (!empty($row['end_date']) && $row['end_date'] != '0000-00-00 00:00:00') {
$row['end_date'] = api_get_local_time($row['end_date']);
$row['end_date_tms'] = api_strtotime($row['end_date']);
}
$my_events[] = $row;
}
}
//Check global agenda events
if (empty($_SESSION['user']) && empty($_SESSION['group'])) {
$table_agenda_system = Database::get_main_table(TABLE_MAIN_SYSTEM_CALENDAR);
$current_access_url_id = api_get_current_access_url_id();
$sql = "SELECT DISTINCT id, title, content , start_date, end_date FROM " . $table_agenda_system . "\n WHERE 1=1 " . $show_all_current . " AND access_url_id = {$current_access_url_id}";
$result = Database::query($sql);
while ($row = Database::fetch_array($result, 'ASSOC')) {
if (!empty($row['start_date']) && $row['start_date'] != '0000-00-00 00:00:00') {
$row['start_date'] = api_get_local_time($row['start_date']);
$row['start_date_tms'] = api_strtotime($row['start_date']);
}
if (!empty($row['end_date']) && $row['end_date'] != '0000-00-00 00:00:00') {
$row['end_date'] = api_get_local_time($row['end_date']);
$row['end_date_tms'] = api_strtotime($row['end_date']);
}
$row['calendar_type'] = 'global';
$my_events[] = $row;
}
}
return $my_events;
}
if (!empty($course_path)) {
$interbreadcrumb[] = array('url' => api_get_path(WEB_COURSE_PATH) . urlencode($course_path) . '/index.php', 'name' => Security::remove_XSS($_GET['courseCode']));
}
// this loads the javascript that is needed for the date popup selection
$htmlHeadXtra[] = to_javascript();
$htmlHeadXtra[] = "<script src=\"tbl_change.js\" type=\"text/javascript\" language=\"javascript\"></script>";
$htmlHeadXtra[] = "<script>\n\$(function() { \n \$(\".dialog\").dialog(\"destroy\"); \n \$(\".dialog\").dialog({\n autoOpen: false,\n show: \"blind\", \n resizable: false,\n height:300,\n width:550,\n modal: true\n });\n \$(\".opener\").click(function() {\n var my_id = \$(this).attr('id');\n var big_image = '#main_' + my_id;\n \$( big_image ).dialog(\"open\");\n return false;\n });\n});\n</script>\n";
// showing the header
Display::display_header(get_lang('MyAgenda'));
// SETTING SOME VARIABLES
// setting the database variables
$TABLECOURS = Database::get_main_table(TABLE_MAIN_COURSE);
$TABLECOURSUSER = Database::get_main_table(TABLE_MAIN_COURSE_USER);
$TABLEAGENDA = Database::get_course_table(TABLE_AGENDA);
$TABLE_ITEMPROPERTY = Database::get_course_table(TABLE_ITEM_PROPERTY);
$tbl_personal_agenda = Database::get_user_personal_table(TABLE_PERSONAL_AGENDA);
// the variables for the days and the months
// Defining the shorts for the days
$DaysShort = api_get_week_days_short();
// Defining the days of the week to allow translation of the days
$DaysLong = api_get_week_days_long();
// Defining the months of the year to allow translation of the months
$MonthsLong = api_get_months_long();
/*
TREATING THE URL PARAMETERS
1. The default values
2. storing it in the session
3. possible view
3.a Month view
3.b Week view
3.c day view
/**
* Retrieves the user defined course categories
* @author Patrick Cool <*****@*****.**>, Ghent University
* @return array containing all the titles of the user defined courses with the id as key of the array
*/
public static function get_user_course_categories()
{
global $_user;
$output = array();
$table_category = Database::get_user_personal_table(TABLE_USER_COURSE_CATEGORY);
$sql = "SELECT * FROM " . $table_category . " WHERE user_id='" . intval($_user['user_id']) . "'";
$result = Database::query($sql);
while ($row = Database::fetch_array($result)) {
$output[$row['id']] = $row['title'];
}
return $output;
}
/**
* stores the user course category in the chamilo_user database
* @param string Category title
* @return bool True if it success
*/
public function store_course_category($category_title)
{
$tucc = Database::get_user_personal_table(TABLE_USER_COURSE_CATEGORY);
// protect data
$current_user_id = api_get_user_id();
$category_title = Database::escape_string($category_title);
$result = false;
// step 1: we determine the max value of the user defined course categories
$sql = "SELECT sort FROM {$tucc} WHERE user_id='" . $current_user_id . "' ORDER BY sort DESC";
$rs_sort = Database::query($sql);
$maxsort = Database::fetch_array($rs_sort);
$nextsort = $maxsort['sort'] + 1;
// step 2: we check if there is already a category with this name, if not we store it, else we give an error.
$sql = "SELECT * FROM {$tucc} WHERE user_id='" . $current_user_id . "' AND title='" . $category_title . "'ORDER BY sort DESC";
$rs = Database::query($sql);
if (Database::num_rows($rs) == 0) {
$sql_insert = "INSERT INTO {$tucc} (user_id, title,sort)\n VALUES ('" . $current_user_id . "', '" . api_htmlentities($category_title, ENT_QUOTES, api_get_system_encoding()) . "', '" . $nextsort . "')";
Database::query($sql_insert);
if (Database::affected_rows()) {
$result = true;
}
} else {
$result = false;
}
return $result;
}
/**
* Gives a list of course auto-register (field special_course)
* @return array list of course
* @author Jhon Hinojosa <*****@*****.**>
* @deprecated this function is never use in chamilo, use CourseManager::get_special_course_list
* @since v1.8.6.2
*/
public static function get_special_course_list()
{
// Database Table Definitions
$tbl_course_user = Database :: get_main_table(TABLE_MAIN_COURSE_USER);
$tbl_course = Database :: get_main_table(TABLE_MAIN_COURSE);
$tbl_course_field = Database :: get_main_table(TABLE_MAIN_COURSE_FIELD);
$tbl_course_field_value = Database :: get_main_table(TABLE_MAIN_COURSE_FIELD_VALUES);
$tbl_user_course_category = Database :: get_user_personal_table(TABLE_USER_COURSE_CATEGORY);
//we filter the courses from the URL
$join_access_url = $where_access_url = '';
if (api_get_multiple_access_url()) {
$access_url_id = api_get_current_access_url_id();
$tbl_url_course = Database :: get_main_table(TABLE_MAIN_ACCESS_URL_REL_COURSE);
$join_access_url = "LEFT JOIN $tbl_url_course url_rel_course ON url_rel_course.course_code= course.code";
$where_access_url = " AND access_url_id = $access_url_id ";
}
// Filter special courses
$sql_special_course = "SELECT course_code FROM $tbl_course_field_value tcfv INNER JOIN $tbl_course_field tcf ON ".
" tcfv.field_id = tcf.id WHERE tcf.field_variable = 'special_course' AND tcfv.field_value = 1 ";
$special_course_result = Database::query($sql_special_course);
$code_special_courses = '';
if (Database::num_rows($special_course_result) > 0) {
$special_course_list = array();
while ($result_row = Database::fetch_array($special_course_result)) {
$special_course_list[] = '"'.$result_row['course_code'].'"';
}
$code_special_courses = ' course.code IN ('.join($special_course_list, ',').') ';
}
$course_list = array();
if (!empty($code_special_courses)) {
$course_list_sql = "SELECT course.code k, course.directory d, course.visual_code c, course.db_name db, course.title i, course.tutor_name t, course.course_language l, course_rel_user.status s, course_rel_user.sort sort, course_rel_user.user_course_cat user_course_cat
FROM ".$tbl_course_user." course_rel_user
LEFT JOIN ".$tbl_course." course
ON course.code = course_rel_user.course_code
LEFT JOIN ".$tbl_user_course_category." user_course_category
ON course_rel_user.user_course_cat = user_course_category.id
$join_access_url
WHERE $code_special_courses $where_access_url
GROUP BY course.code
ORDER BY user_course_category.sort,course.title,course_rel_user.sort ASC";
$course_list_sql_result = Database::query($course_list_sql);
while ($result_row = Database::fetch_array($course_list_sql_result)) {
$course_list[] = $result_row;
}
}
return $course_list;
}
