function save_rsvp(Database $db)
{
$values['people'] = $db->escape_string($_POST['people']);
$values['partysize'] = $db->escape_string($_POST['partysize']);
$values['is_attending'] = $db->escape_string($_POST['is_attending']);
$values['contact_email'] = $db->escape_string($_POST['contact_email']);
$values['extra_info'] = $db->escape_string($_POST['extra_info']);
return $db->insert("rsvp_data", $values);
}
public static function verify_user($username, $password)
{
$database = new Database();
$username = $database->escape_string($username);
$password = $database->escape_string($password);
$sql = "SELECT * FROM users WHERE ";
$sql .= "username = '******' ";
$sql .= "AND password = '******' ";
$sql .= "LIMIT 1";
$the_result_array = self::execute_query($sql);
return !empty($the_result_array) ? array_shift($the_result_array) : false;
}
/**
* Update the file or directory path in the document db document table
*
* @author - Hugues Peeters <*****@*****.**>
* @param - action (string) - action type require : 'delete' or 'update'
* @param - old_path (string) - old path info stored to change
* @param - new_path (string) - new path info to substitute
* @desc Update the file or directory path in the document db document table
*
*/
function update_db_info($action, $old_path, $new_path = '')
{
$dbTable = Database::get_course_table(TABLE_DOCUMENT);
$course_id = api_get_course_int_id();
switch ($action) {
case 'delete':
$old_path = Database::escape_string($old_path);
$to_delete = "WHERE c_id = {$course_id} AND (path LIKE BINARY '" . $old_path . "' OR path LIKE BINARY '" . $old_path . "/%')";
$query = "DELETE FROM {$dbTable} " . $to_delete;
$result = Database::query("SELECT id FROM {$dbTable} " . $to_delete);
if (Database::num_rows($result)) {
require_once api_get_path(INCLUDE_PATH) . '../metadata/md_funcs.php';
$mdStore = new mdstore(TRUE);
// create if needed
$md_type = substr($dbTable, -13) == 'scormdocument' ? 'Scorm' : 'Document';
while ($row = Database::fetch_array($result)) {
$eid = $md_type . '.' . $row['id'];
$mdStore->mds_delete($eid);
$mdStore->mds_delete_offspring($eid);
}
}
Database::query($query);
break;
case 'update':
if ($new_path[0] == '.') {
$new_path = substr($new_path, 1);
}
$new_path = str_replace('//', '/', $new_path);
// Attempt to update - tested & working for root dir
$new_path = Database::escape_string($new_path);
$query = "UPDATE {$dbTable} SET\n path = CONCAT('" . $new_path . "', SUBSTRING(path, LENGTH('" . $old_path . "')+1) )\n WHERE c_id = {$course_id} AND (path LIKE BINARY '" . $old_path . "' OR path LIKE BINARY '" . $old_path . "/%')";
Database::query($query);
break;
}
}
/**
* Get the classes to display on the current page.
*/
function get_class_data($from, $number_of_items, $column, $direction)
{
$class_table = Database::get_main_table(TABLE_MAIN_CLASS);
$course_class_table = Database::get_main_table(TABLE_MAIN_COURSE_CLASS);
$class_user_table = Database::get_main_table(TABLE_MAIN_CLASS_USER);
$courseCode = api_get_course_id();
$sql = "SELECT * FROM {$course_class_table} WHERE course_code = '" . $courseCode . "'";
$res = Database::query($sql);
$subscribed_classes = array();
while ($obj = Database::fetch_object($res)) {
$subscribed_classes[] = $obj->class_id;
}
$sql = "SELECT\n c.id AS col0,\n c.name AS col1,\n COUNT(cu.user_id) AS col2,\n c.id AS col3\n FROM {$class_table} c ";
$sql .= " LEFT JOIN {$class_user_table} cu ON cu.class_id = c.id";
$sql .= " WHERE 1 = 1";
if (isset($_GET['keyword'])) {
$keyword = Database::escape_string(trim($_GET['keyword']));
$sql .= " AND (c.name LIKE '%" . $keyword . "%')";
}
if (count($subscribed_classes) > 0) {
$sql .= " AND c.id NOT IN ('" . implode("','", $subscribed_classes) . "')";
}
$sql .= " GROUP BY c.id, c.name ";
$sql .= " ORDER BY col{$column} {$direction} ";
$sql .= " LIMIT {$from},{$number_of_items}";
$res = Database::query($sql);
$classes = array();
while ($class = Database::fetch_row($res)) {
$classes[] = $class;
}
return $classes;
}
/**
* Generate an array of attendances that a teacher hasn't created a link for.
* @return array 2-dimensional array - every element contains 2 subelements (id, name)
* @todo seems to be depracated
*/
public function get_not_created_links()
{
return false;
if (empty($this->course_code)) {
die('Error in get_not_created_links() : course code not set');
}
$tbl_grade_links = Database::get_main_table(TABLE_MAIN_GRADEBOOK_LINK);
$sql = 'SELECT att.id, att.name, att.attendance_qualify_title
FROM ' . $this->get_attendance_table() . ' att
WHERE
att.c_id = ' . $this->course_id . ' AND
att.id NOT IN (
SELECT ref_id FROM ' . $tbl_grade_links . '
WHERE
type = ' . LINK_ATTENDANCE . ' AND
course_code = "' . Database::escape_string($this->get_course_code()) . '"
)
AND att.session_id=' . api_get_session_id() . '';
$result = Database::query($sql);
$cats = array();
while ($data = Database::fetch_array($result)) {
if (isset($data['attendance_qualify_title']) && $data['attendance_qualify_title'] != '') {
$cats[] = array($data['id'], $data['attendance_qualify_title']);
} else {
$cats[] = array($data['id'], $data['name']);
}
}
return $cats;
}
protected function build_create()
{
$this->addElement('header', get_lang('MakeLink'));
$select = $this->addElement('select', 'select_link', get_lang('ChooseLink'), null, array('onchange' => 'document.create_link.submit()'));
$linktypes = LinkFactory::get_all_types();
$select->addoption('[' . get_lang('ChooseLink') . ']', 0);
$cc = $this->category_object->get_course_code();
foreach ($linktypes as $linktype) {
$link = LinkFactory::create($linktype);
if (!empty($cc)) {
$link->set_course_code($cc);
} elseif (!empty($_GET['course_code'])) {
$link->set_course_code(Database::escape_string($_GET['course_code']));
}
// disable this element if the link works with a dropdownlist
// and if there are no links left
if (!$link->needs_name_and_description() && count($link->get_all_links()) == '0') {
$select->addoption($link->get_type_name(), $linktype, 'disabled');
} else {
$select->addoption($link->get_type_name(), $linktype);
}
}
if (isset($this->extra)) {
$this->setDefaults(array('select_link' => $this->extra));
}
}
function search_sessions($needle, $type)
{
global $tbl_session_rel_access_url, $tbl_session, $user_id;
$xajax_response = new xajaxResponse();
$return = '';
if (!empty($needle) && !empty($type)) {
$needle = Database::escape_string($needle);
$assigned_sessions_to_hrm = SessionManager::get_sessions_followed_by_drh($user_id);
$assigned_sessions_id = array_keys($assigned_sessions_to_hrm);
$without_assigned_sessions = '';
if (count($assigned_sessions_id) > 0) {
$without_assigned_sessions = " AND s.id NOT IN(" . implode(',', $assigned_sessions_id) . ")";
}
if (api_is_multiple_url_enabled()) {
$sql = " SELECT s.id, s.name FROM {$tbl_session} s\n LEFT JOIN {$tbl_session_rel_access_url} a ON (s.id = a.session_id)\n WHERE s.name LIKE '{$needle}%' {$without_assigned_sessions} AND access_url_id = " . api_get_current_access_url_id() . "";
} else {
$sql = "SELECT s.id, s.name FROM {$tbl_session} s\n WHERE s.name LIKE '{$needle}%' {$without_assigned_sessions} ";
}
$rs = Database::query($sql);
$return .= '<select class="form-control" id="origin" name="NoAssignedSessionsList[]" multiple="multiple" size="20">';
while ($session = Database::fetch_array($rs)) {
$return .= '<option value="' . $session['id'] . '" title="' . htmlspecialchars($session['name'], ENT_QUOTES) . '">' . $session['name'] . '</option>';
}
$return .= '</select>';
$xajax_response->addAssign('ajax_list_sessions_multiple', 'innerHTML', api_utf8_encode($return));
}
return $xajax_response;
}
/**
* Search for a list of available courses by title or code, based on
* a given string
* @param string String to search for
* @param int Deprecated param
* @return string A formatted, xajax answer block
* @assert () === false
*/
function search_courses($needle, $id)
{
global $tbl_course;
$xajax_response = new XajaxResponse();
$return = '';
if (!empty($needle)) {
// xajax send utf8 datas... datas in db can be non-utf8 datas
$charset = api_get_system_encoding();
$needle = api_convert_encoding($needle, $charset, 'utf-8');
$needle = Database::escape_string($needle);
// search courses where username or firstname or lastname begins likes $needle
$sql = 'SELECT code, title FROM ' . $tbl_course . ' u ' . ' WHERE (title LIKE "' . $needle . '%" ' . ' OR code LIKE "' . $needle . '%" ' . ' ) ' . ' ORDER BY title, code ' . ' LIMIT 11';
$rs = Database::query($sql);
$i = 0;
while ($course = Database::fetch_array($rs)) {
$i++;
if ($i <= 10) {
$return .= '<a href="javascript: void(0);" onclick="javascript: add_user_to_url(\'' . addslashes($course['code']) . '\',\'' . addslashes($course['title']) . ' (' . addslashes($course['code']) . ')' . '\')">' . $course['title'] . ' (' . $course['code'] . ')</a><br />';
} else {
$return .= '...<br />';
}
}
}
$xajax_response->addAssign('ajax_list_courses', 'innerHTML', api_utf8_encode($return));
return $xajax_response;
}
/**
*
* @param int $user_id
* @param string $api_key
* @param string $api_service
* @param string $api_end_point
* @param int $validity_start_date
* @param int $validity_end_date
* @param string $description
* @return AccessToken
*/
public static function create_token($user_id = null, $api_key = null, $api_service = null, $api_end_point = null, $validity_start_date = null, $validity_end_date = null, $description = '')
{
$time = time();
$user_id = $user_id ? $user_id : Chamilo::user()->user_id();
$api_key = $api_key ? $api_key : uniqid('', true);
$api_service = $api_service ? $api_service : self::default_service();
$api_end_point = $api_end_point ? $api_end_point : '';
$validity_start_date = $validity_start_date ? $validity_start_date : $time;
$validity_end_date = $validity_end_date ? $validity_end_date : self::end_of_time();
$created_date = $time;
$user_id = (int) $user_id;
$api_key = Database::escape_string($api_key);
$api_service = Database::escape_string($api_service);
$api_end_point = Database::escape_string($api_end_point);
$validity_start_date = date('Y-m-d H:i:s', $validity_start_date);
$validity_end_date = date('Y-m-d H:i:s', $validity_end_date);
$created_date = date('Y-m-d H:i:s', $created_date);
$values = array();
$values['user_id'] = $user_id;
$values['api_key'] = $api_key;
$values['api_service'] = $api_service;
$values['api_end_point'] = $api_end_point;
$values['validity_start_date'] = $validity_start_date;
$values['validity_end_date'] = $validity_end_date;
$values['created_date'] = $created_date;
$table = Database::get_main_table(TABLE_MAIN_USER_API_KEY);
$id = Database::insert($table, $values);
return AccessToken::create($id, $user_id, $api_key);
}
/**
* Search users by username, firstname or lastname, based on the given
* search string
* @param string Search string
* @param int Deprecated param
* @return string Xajax response block
* @assert () === false
*/
public static function search_users($needle, $id)
{
global $tbl_user, $tbl_access_url_rel_user;
$xajax_response = new XajaxResponse();
$return = '';
if (!empty($needle)) {
// xajax send utf8 datas... datas in db can be non-utf8 datas
$charset = api_get_system_encoding();
$needle = api_convert_encoding($needle, $charset, 'utf-8');
$needle = Database::escape_string($needle);
// search users where username or firstname or lastname begins likes $needle
$order_clause = api_sort_by_first_name() ? ' ORDER BY firstname, lastname, username' : ' ORDER BY lastname, firstname, username';
$sql = 'SELECT u.user_id, username, lastname, firstname FROM ' . $tbl_user . ' u ' . ' WHERE (username LIKE "' . $needle . '%" ' . ' OR firstname LIKE "' . $needle . '%" ' . ' OR lastname LIKE "' . $needle . '%") ' . $order_clause . ' LIMIT 11';
$rs = Database::query($sql);
$i = 0;
while ($user = Database::fetch_array($rs)) {
$i++;
if ($i <= 10) {
$return .= '<a href="javascript: void(0);" onclick="javascript: add_user_to_url(\'' . addslashes($user['user_id']) . '\',\'' . api_get_person_name(addslashes($user['firstname']), addslashes($user['lastname'])) . ' (' . addslashes($user['username']) . ')' . '\')">' . api_get_person_name($user['firstname'], $user['lastname']) . ' (' . $user['username'] . ')</a><br />';
} else {
$return .= '...<br />';
}
}
}
$xajax_response->addAssign('ajax_list_users', 'innerHTML', api_utf8_encode($return));
return $xajax_response;
}
function search_courses($needle, $type)
{
global $tbl_course, $tbl_course_rel_access_url, $user_id;
$xajax_response = new xajaxResponse();
$return = '';
if (!empty($needle) && !empty($type)) {
// xajax send utf8 datas... datas in db can be non-utf8 datas
$needle = Database::escape_string($needle);
$assigned_courses_to_hrm = CourseManager::get_courses_followed_by_drh($user_id);
$assigned_courses_code = array_keys($assigned_courses_to_hrm);
foreach ($assigned_courses_code as &$value) {
$value = "'" . $value . "'";
}
$without_assigned_courses = '';
if (count($assigned_courses_code) > 0) {
$without_assigned_courses = " AND c.code NOT IN(" . implode(',', $assigned_courses_code) . ")";
}
if (api_is_multiple_url_enabled()) {
$sql = "SELECT c.code, c.title\n FROM {$tbl_course} c\n\t\t\t\t\tLEFT JOIN {$tbl_course_rel_access_url} a\n ON (a.c_id = c.id)\n \tWHERE\n \t\tc.code LIKE '{$needle}%' {$without_assigned_courses} AND\n \t\taccess_url_id = " . api_get_current_access_url_id();
} else {
$sql = "SELECT c.code, c.title\n \t\tFROM {$tbl_course} c\n \tWHERE\n \t\tc.code LIKE '{$needle}%'\n \t\t{$without_assigned_courses} ";
}
$rs = Database::query($sql);
$return .= '<select id="origin" name="NoAssignedCoursesList[]" multiple="multiple" size="20" style="width:340px;">';
while ($course = Database::fetch_array($rs)) {
$return .= '<option value="' . $course['code'] . '" title="' . htmlspecialchars($course['title'], ENT_QUOTES) . '">' . $course['title'] . ' (' . $course['code'] . ')</option>';
}
$return .= '</select>';
$xajax_response->addAssign('ajax_list_courses_multiple', 'innerHTML', api_utf8_encode($return));
}
return $xajax_response;
}
/**
* Search sessions by name, based on a search string
* @param string Search string
* @param int Deprecated param
* @return string Xajax response block
* @assert () === false
*/
function search_sessions($needle, $id)
{
global $tbl_session;
$xajax_response = new XajaxResponse();
$return = '';
if (!empty($needle)) {
// xajax send utf8 datas... datas in db can be non-utf8 datas
$charset = api_get_system_encoding();
$needle = api_convert_encoding($needle, $charset, 'utf-8');
$needle = Database::escape_string($needle);
// search sessiones where username or firstname or lastname begins likes $needle
$sql = 'SELECT id, name FROM ' . $tbl_session . ' u
WHERE (name LIKE "' . $needle . '%")
ORDER BY name, id
LIMIT 11';
$rs = Database::query($sql);
$i = 0;
while ($session = Database::fetch_array($rs)) {
$i++;
if ($i <= 10) {
$return .= '<a href="#" onclick="add_user_to_url(\'' . addslashes($session['id']) . '\',\'' . addslashes($session['name']) . ' (' . addslashes($session['id']) . ')' . '\')">' . $session['name'] . ' </a><br />';
} else {
$return .= '...<br />';
}
}
}
$xajax_response->addAssign('ajax_list_courses', 'innerHTML', api_utf8_encode($return));
return $xajax_response;
}
/**
* Get the classes to display on the current page.
*/
function get_class_data($from, $number_of_items, $column, $direction)
{
$class_table = Database::get_main_table(TABLE_MAIN_CLASS);
$class_user_table = Database::get_main_table(TABLE_MAIN_CLASS_USER);
$courseId = api_get_course_int_id();
$em = Database::getManager();
$res = $em->getRepository('ChamiloCoreBundle:CourseRelClass')->findBy(['courseId' => $courseId]);
$subscribed_classes = array();
foreach ($res as $obj) {
$subscribed_classes[] = $obj->getClassId();
}
$sql = "SELECT\n c.id AS col0,\n c.name AS col1,\n COUNT(cu.user_id) AS col2,\n c.id AS col3\n FROM {$class_table} c ";
$sql .= " LEFT JOIN {$class_user_table} cu ON cu.class_id = c.id";
$sql .= " WHERE 1 = 1";
if (isset($_GET['keyword'])) {
$keyword = Database::escape_string(trim($_GET['keyword']));
$sql .= " AND (c.name LIKE '%" . $keyword . "%')";
}
if (count($subscribed_classes) > 0) {
$sql .= " AND c.id NOT IN ('" . implode("','", $subscribed_classes) . "')";
}
$sql .= " GROUP BY c.id, c.name ";
$sql .= " ORDER BY col{$column} {$direction} ";
$sql .= " LIMIT {$from},{$number_of_items}";
$res = Database::query($sql);
$classes = array();
while ($class = Database::fetch_row($res)) {
$classes[] = $class;
}
return $classes;
}
/**
*
*/
function get_course_usage($course_code, $session_id = 0)
{
$table = Database::get_main_table(TABLE_MAIN_COURSE);
$course_code = Database::escape_string($course_code);
$sql = "SELECT * FROM {$table} WHERE code='" . $course_code . "'";
$res = Database::query($sql);
$course = Database::fetch_object($res);
// Learnpaths
$table = Database::get_course_table(TABLE_LP_MAIN);
$usage[] = array(get_lang(ucfirst(TOOL_LEARNPATH)), CourseManager::count_rows_course_table($table, $session_id, $course->id));
// Forums
$table = Database::get_course_table(TABLE_FORUM);
$usage[] = array(get_lang('Forums'), CourseManager::count_rows_course_table($table, $session_id, $course->id));
// Quizzes
$table = Database::get_course_table(TABLE_QUIZ_TEST);
$usage[] = array(get_lang(ucfirst(TOOL_QUIZ)), CourseManager::count_rows_course_table($table, $session_id, $course->id));
// Documents
$table = Database::get_course_table(TABLE_DOCUMENT);
$usage[] = array(get_lang(ucfirst(TOOL_DOCUMENT)), CourseManager::count_rows_course_table($table, $session_id, $course->id));
// Groups
$table = Database::get_course_table(TABLE_GROUP);
$usage[] = array(get_lang(ucfirst(TOOL_GROUP)), CourseManager::count_rows_course_table($table, $session_id, $course->id));
// Calendar
$table = Database::get_course_table(TABLE_AGENDA);
$usage[] = array(get_lang(ucfirst(TOOL_CALENDAR_EVENT)), CourseManager::count_rows_course_table($table, $session_id, $course->id));
// Link
$table = Database::get_course_table(TABLE_LINK);
$usage[] = array(get_lang(ucfirst(TOOL_LINK)), CourseManager::count_rows_course_table($table, $session_id, $course->id));
// Announcements
$table = Database::get_course_table(TABLE_ANNOUNCEMENT);
$usage[] = array(get_lang(ucfirst(TOOL_ANNOUNCEMENT)), CourseManager::count_rows_course_table($table, $session_id, $course->id));
return $usage;
}
/**
* Get document information
*/
private function get_information($course_id, $link_id)
{
$course_information = api_get_course_info($course_id);
$course_id = $course_information['real_id'];
$course_id_alpha = $course_information['id'];
if (!empty($course_information)) {
$item_property_table = Database::get_course_table(TABLE_ITEM_PROPERTY);
$link_id = Database::escape_string($link_id);
$sql = "SELECT insert_user_id FROM {$item_property_table}\n \t\tWHERE ref = {$link_id} AND tool = '" . TOOL_LINK . "' AND c_id = {$course_id}\n \t\tLIMIT 1";
$name = get_lang('Links');
$url = api_get_path(WEB_PATH) . 'main/link/link.php?cidReq=%s';
$url = sprintf($url, $course_id_alpha);
// Get the image path
$thumbnail = api_get_path(WEB_IMG_PATH) . 'link.gif';
$image = $thumbnail;
//FIXME: use big images
// get author
$author = '';
$item_result = Database::query($sql);
if ($row = Database::fetch_array($item_result)) {
$user_data = api_get_user_info($row['insert_user_id']);
$author = api_get_person_name($user_data['firstName'], $user_data['lastName']);
}
return array($thumbnail, $image, $name, $author, $url);
} else {
return array();
}
}
/**
* @Route("/edit/{tool}")
* @Method({"GET"})
*
* @param string $tool
* @return Response
*/
public function editAction($tool)
{
$message = null;
// @todo use proper functions not api functions.
$courseId = api_get_course_int_id();
$sessionId = api_get_session_id();
$tool = \Database::escape_string($tool);
$TBL_INTRODUCTION = \Database::get_course_table(TABLE_TOOL_INTRO);
$url = $this->generateUrl('introduction.controller:editAction', array('tool' => $tool, 'course' => api_get_course_id()));
$form = $this->getForm($url, $tool);
if ($form->validate()) {
$values = $form->exportValues();
$content = $values['content'];
$sql = "REPLACE {$TBL_INTRODUCTION}\n SET c_id = {$courseId},\n id = '{$tool}',\n intro_text='" . \Database::escape_string($content) . "',\n session_id='" . intval($sessionId) . "'";
\Database::query($sql);
$message = \Display::return_message(get_lang('IntroductionTextUpdated'), 'confirmation', false);
} else {
$sql = "SELECT intro_text FROM {$TBL_INTRODUCTION}\n WHERE c_id = {$courseId} AND id='" . $tool . "' AND session_id = '" . intval($sessionId) . "'";
$result = \Database::query($sql);
$content = null;
if (\Database::num_rows($result) > 0) {
$row = \Database::fetch_array($result);
$content = $row['intro_text'];
}
$form->setDefaults(array('content' => $content));
}
$this->getTemplate()->assign('content', $form->return_form());
$this->getTemplate()->assign('message', $message);
$response = $this->getTemplate()->renderLayout('layout_1_col.tpl');
return new Response($response, 200, array());
}
function remove()
{
$this->ga_id = str_replace(' ', '', $this->ga_id);
$this->ga_id = strtoupper($this->ga_id);
$sql = " delete from groupe_analytique where ga_id='" . Database::escape_string($this->ga_id) . "'";
$this->db->exec_sql($sql);
}
/**
* @package chamilo.survey
* @author Arnaud Ligot <*****@*****.**>
* @version $Id: $
*
* A small peace of code to enable user to access images included into survey
* which are accessible by non authenticated users. This file is included
* by document/download.php
*/
function check_download_survey($course, $invitation, $doc_url)
{
require_once 'survey.lib.php';
// Getting all the course information
$_course = CourseManager::get_course_information($course);
$course_id = $_course['real_id'];
// Database table definitions
$table_survey = Database::get_course_table(TABLE_SURVEY);
$table_survey_question = Database::get_course_table(TABLE_SURVEY_QUESTION);
$table_survey_question_option = Database::get_course_table(TABLE_SURVEY_QUESTION_OPTION);
$table_survey_invitation = Database::get_course_table(TABLE_SURVEY_INVITATION);
// Now we check if the invitationcode is valid
$sql = "SELECT * FROM {$table_survey_invitation}\n\t WHERE\n\t c_id = {$course_id} AND\n\t invitation_code = '" . Database::escape_string($invitation) . "'";
$result = Database::query($sql);
if (Database::num_rows($result) < 1) {
Display::display_error_message(get_lang('WrongInvitationCode'), false);
Display::display_footer();
exit;
}
$survey_invitation = Database::fetch_assoc($result);
// Now we check if the user already filled the survey
if ($survey_invitation['answered'] == 1) {
Display::display_error_message(get_lang('YouAlreadyFilledThisSurvey'), false);
Display::display_footer();
exit;
}
// Very basic security check: check if a text field from a survey/answer/option contains the name of the document requested
// Fetch survey ID
// If this is the case there will be a language choice
$sql = "SELECT * FROM {$table_survey}\n\t WHERE\n\t c_id = {$course_id} AND\n\t code='" . Database::escape_string($survey_invitation['survey_code']) . "'";
$result = Database::query($sql);
if (Database::num_rows($result) > 1) {
if ($_POST['language']) {
$survey_invitation['survey_id'] = $_POST['language'];
} else {
echo '<form id="language" name="language" method="POST" action="' . api_get_self() . '?course=' . $_GET['course'] . '&invitationcode=' . $_GET['invitationcode'] . '">';
echo ' <select name="language">';
while ($row = Database::fetch_assoc($result)) {
echo '<option value="' . $row['survey_id'] . '">' . $row['lang'] . '</option>';
}
echo '</select>';
echo ' <input type="submit" name="Submit" value="' . get_lang('Ok') . '" />';
echo '</form>';
display::display_footer();
exit;
}
} else {
$row = Database::fetch_assoc($result);
$survey_invitation['survey_id'] = $row['survey_id'];
}
$sql = "SELECT count(*)\n\t FROM {$table_survey}\n\t WHERE\n\t c_id = {$course_id} AND\n\t survey_id = " . $survey_invitation['survey_id'] . " AND (\n title LIKE '%{$doc_url}%'\n or subtitle LIKE '%{$doc_url}%'\n or intro LIKE '%{$doc_url}%'\n or surveythanks LIKE '%{$doc_url}%'\n )\n\t\t UNION\n\t\t SELECT count(*)\n\t\t FROM {$table_survey_question}\n\t\t WHERE\n\t\t c_id = {$course_id} AND\n\t\t survey_id = " . $survey_invitation['survey_id'] . " AND (\n survey_question LIKE '%{$doc_url}%'\n or survey_question_comment LIKE '%{$doc_url}%'\n )\n\t\t UNION\n\t\t SELECT count(*)\n\t\t FROM {$table_survey_question_option}\n\t\t WHERE\n\t\t c_id = {$course_id} AND\n\t\t survey_id = " . $survey_invitation['survey_id'] . " AND (\n option_text LIKE '%{$doc_url}%'\n )";
$result = Database::query($sql);
if (Database::num_rows($result) == 0) {
Display::display_error_message(get_lang('WrongInvitationCode'), false);
Display::display_footer();
exit;
}
return $_course;
}
/**
* Gets the data of a Term and condition by language
* @param int $language language id
* @return array all the info of a Term and condition
*/
public static function get_last_condition($language)
{
$legal_conditions_table = Database::get_main_table(TABLE_MAIN_LEGAL);
$language = Database::escape_string($language);
$sql = "SELECT * FROM {$legal_conditions_table}\n WHERE language_id = '" . $language . "'\n ORDER BY version DESC\n LIMIT 1 ";
$result = Database::query($sql);
return Database::fetch_array($result);
}
/**
* Update in database
*/
public function update_in_bdd()
{
$item_view_table = Database::get_course_table(TABLE_LP_ITEM);
if ($this->c_id > 0 && $this->id > 0) {
$sql = "UPDATE {$item_view_table} SET\n lp_id = '" . intval($this->lp_id) . "' ,\n item_type = '" . Database::escape_string($this->item_type) . "' ,\n ref = '" . Database::escape_string($this->ref) . "' ,\n title = '" . Database::escape_string($this->title) . "' ,\n description = '" . Database::escape_string($this->description) . "' ,\n path = '" . Database::escape_string($this->path) . "' ,\n min_score = '" . Database::escape_string($this->min_score) . "' ,\n max_score = '" . Database::escape_string($this->max_score) . "' ,\n mastery_score = '" . Database::escape_string($this->mastery_score) . "' ,\n parent_item_id = '" . Database::escape_string($this->parent_item_id) . "' ,\n previous_item_id = '" . Database::escape_string($this->previous_item_id) . "' ,\n next_item_id = '" . Database::escape_string($this->next_item_id) . "' ,\n display_order = '" . Database::escape_string($this->display_order) . "' ,\n prerequisite = '" . Database::escape_string($this->prerequisite) . "' ,\n parameters = '" . Database::escape_string($this->parameters) . "' ,\n launch_data = '" . Database::escape_string($this->launch_data) . "' ,\n max_time_allowed = '" . Database::escape_string($this->max_time_allowed) . "' ,\n terms = '" . Database::escape_string($this->terms) . "' ,\n search_did = '" . Database::escape_string($this->search_did) . "' ,\n audio = '" . Database::escape_string($this->audio) . "'\n WHERE c_id=" . $this->c_id . " AND id=" . $this->id;
Database::query($sql);
}
}
/**
* Get glossary term by glossary id
* @author Isaac Flores <*****@*****.**>
* @param String The glossary term name
* @return String The glossary description
*/
public static function get_glossary_term_by_glossary_name($glossary_name)
{
global $_course;
$glossary_table = Database::get_course_table(TABLE_GLOSSARY);
$sql = 'SELECT description FROM ' . $glossary_table . ' WHERE name like trim("' . Database::escape_string($glossary_name) . '") ';
$rs = Database::query($sql, __FILE__, __LINE__);
$row = Database::fetch_array($rs);
return $row['description'];
}
/**
* @param $course_code
* @return array|bool
*/
function initializeReport($course_code)
{
$course_info = api_get_course_info($course_code);
$table_reporte_semanas = Database::get_main_table('rp_reporte_semanas');
$table_students_report = Database::get_main_table('rp_students_report');
$table_semanas_curso = Database::get_main_table('rp_semanas_curso');
$table_course_rel_user = Database::get_main_table(TABLE_MAIN_COURSE_USER);
$table_post = Database::get_course_table(TABLE_FORUM_POST, $course_info['dbName']);
$table_work = Database::get_course_table(TABLE_STUDENT_PUBLICATION, $course_info['dbName']);
$course_code = Database::escape_string($course_code);
$res = Database::query("SELECT COUNT(*) as cant FROM $table_reporte_semanas WHERE course_code = '" . $course_code . "'");
$sqlWeeks = "SELECT semanas FROM $table_semanas_curso WHERE course_code = '$course_code'";
$resWeeks = Database::query($sqlWeeks);
$weeks = Database::fetch_object($resWeeks);
$obj = Database::fetch_object($res);
$weeksCount = (!isset($_POST['weeksNumber'])) ? (($weeks->semanas == 0) ? 7 : $weeks->semanas) : $_POST['weeksNumber'];
$weeksCount = Database::escape_string($weeksCount);
Database::query("REPLACE INTO $table_semanas_curso (course_code , semanas) VALUES ('$course_code','$weeksCount')");
if (intval($obj->cant) != $weeksCount) {
if (intval($obj->cant) > $weeksCount) {
$sql = "DELETE FROM $table_reporte_semanas WHERE week_id > $weeksCount AND course_code = '$course_code'";
Database::query("DELETE FROM $table_reporte_semanas WHERE week_id > $weeksCount AND course_code = '$course_code'");
} else {
for ($i = $obj->cant + 1; $i <= $weeksCount; $i++) {
if (!Database::query("INSERT INTO $table_reporte_semanas (week_id, course_code, forum_id, work_id, quiz_id, pc_id)
VALUES ($i, '$course_code', '0', '0', '0', '0' )")) {
return false;
}
}
}
}
$sql = "REPLACE INTO $table_students_report (user_id, week_report_id, work_ok , thread_ok , quiz_ok , pc_ok)
SELECT cu.user_id, rs.id, 0, 0, 0, 0
FROM $table_course_rel_user cu
LEFT JOIN $table_reporte_semanas rs ON cu.course_code = rs.course_code
WHERE cu.status = 5 AND rs.course_code = '$course_code'
ORDER BY cu.user_id, rs.id";
if (!Database::query($sql)) {
return false;
} else {
$page = (!isset($_GET['page'])) ? 1 : $_GET['page'];
Database::query("UPDATE $table_students_report sr SET sr.work_ok = 1
WHERE CONCAT (sr.user_id,',',sr.week_report_id)
IN (SELECT DISTINCT CONCAT(w.user_id,',',rs.id)
FROM $table_work w JOIN $table_reporte_semanas rs ON w.parent_id = rs.work_id)");
Database::query("UPDATE $table_students_report sr SET sr.thread_ok = 1
WHERE CONCAT (sr.user_id,',',sr.week_report_id)
IN (SELECT DISTINCT CONCAT(f.poster_id,',',rs.id)
FROM $table_post f JOIN $table_reporte_semanas rs ON f.thread_id = rs.forum_id)");
return showResults($course_info, $weeksCount, $page);
}
}
/**
* load the data
* does not return anything but give a value to this->aheader and this->arow
*/
function load_card()
{
$sql_from_poste = $this->from_poste != '' ? " and po.po_name >= upper('" . Database::escape_string($this->from_poste) . "')" : '';
$sql_to_poste = $this->to_poste != '' ? " and po.po_name <= upper('" . Database::escape_string($this->to_poste) . "')" : '';
$this->db->exec_sql('create temporary table table_analytic as select * from comptaproc.table_analytic_card(\'' . $this->from . '\',\'' . $this->to . '\')');
$header = "select distinct po_id,po_name from table_analytic\n\t\twhere\n\t\tpa_id=\$1 " . $sql_from_poste . $sql_to_poste . " order by po_name";
$this->aheader = $this->db->get_array($header, array($this->pa_id));
$this->arow = $this->db->get_array("select distinct f_id,card_account,name from table_analytic \n\t\t\twhere\n\t\tpa_id=\$1 " . $sql_from_poste . $sql_to_poste . " order by name", array($this->pa_id));
$this->sql = 'select sum_amount from table_analytic where f_id=$1 and po_id=$2 and pa_id=' . $this->pa_id . ' ' . $sql_from_poste . $sql_to_poste;
}
/**
* Class constructor. Depending of the type of construction called ('db' or 'manifest'), will create a scormItem
* object from database records or from the array given as second parameter
* @param string Type of construction needed ('db' or 'config', default = 'config')
* @param mixed Depending on the type given, DB id for the lp_item or parameters array
*/
public function aiccItem($type = 'config', $params = array(), $course_id = null)
{
if (isset($params)) {
switch ($type) {
case 'db':
parent::__construct($params, api_get_user_id(), $course_id);
$this->aicc_contact = false;
//TODO: Implement this way of metadata object creation.
return false;
case 'config': // Do the same as the default.
default:
//if($first_item->type == XML_ELEMENT_NODE) this is already check prior to the call to this function
foreach ($params as $a => $value) {
switch ($a) {
case 'system_id':
$this->identifier = Database::escape_string(strtolower($value));
break;
case 'type':
$this->au_type = Database::escape_string($value);
break;
case 'command_line':
$this->command_line = Database::escape_string($value);
break;
case 'max_time_allowed':
$this->maxtimeallowed = Database::escape_string($value);
break;
case 'time_limit_action':
$this->timelimitaction = Database::escape_string($value);
break;
case 'max_score':
$this->max_score = Database::escape_string($value);
break;
case 'core_vendor':
$this->core_vendor = Database::escape_string($value);
break;
case 'system_vendor':
$this->system_vendor = Database::escape_string($value);
break;
case 'file_name':
$this->path = Database::escape_string($value);
break;
case 'mastery_score':
$this->masteryscore = Database::escape_string($value);
break;
case 'web_launch':
$this->parameters = Database::escape_string($value);
break;
}
}
return true;
}
}
return false;
}
public static function search($keyword)
{
$keyword = Database::escape_string($keyword);
$query = "SELECT DISTINCT(SearchResult.nefub_id) as ID, name as Name, type as Type, subtitle as Subtitle, url as URL\n\t\t\t\t\tFROM SearchResult\n\t\t\t\t\tWHERE SearchResult.name LIKE '%" . $keyword . "%'\n\t\t\t\t\tAND SearchResult.type = 'team'\n\t\t\t\t\tORDER BY SearchResult.name LIKE '" . $keyword . "%' DESC, SearchResult.name ASC";
$result = Database::query($query);
$teams = Database::convertResult($result);
$query = "SELECT DISTINCT(SearchResult.nefub_id) as ID, name as Name, type as Type, subtitle as Subtitle, url as URL\n\t\t\t\t\tFROM SearchResult\n\t\t\t\t\tWHERE SearchResult.name LIKE '%" . $keyword . "%'\n\t\t\t\t\tAND SearchResult.type = 'person'\n\t\t\t\t\tORDER BY SearchResult.name LIKE '" . $keyword . "%' DESC, SearchResult.name ASC";
$result = Database::query($query);
$persons = Database::convertResult($result);
$results = array_merge($teams, $persons);
return $results;
}
/**
* Function to check if a username is available
* @see HTML_QuickForm_Rule
* @param string $username Wanted username
* @param string $current_username
* @return boolean True if username is available
*/
function validate($username, $current_username = null)
{
$user_table = Database::get_main_table(TABLE_MAIN_USER);
$username = Database::escape_string($username);
$current_username = Database::escape_string($current_username);
$sql = "SELECT * FROM {$user_table} WHERE username = '******'";
if (!is_null($current_username)) {
$sql .= " AND username != '{$current_username}'";
}
$res = Database::query($sql);
$number = Database::num_rows($res);
return $number == 0;
}
/**
*
* Returns the URL of a document
* This function is loaded when using a gradebook as a tab (gradebook = -1) see issue #2705
*/
public function get_view_url($stud_id)
{
// find a file uploaded by the given student,
// with the same title as the evaluation name
$eval = $this->get_evaluation();
$sql = 'SELECT filename FROM ' . $this->get_dropbox_table() . ' WHERE c_id = ' . $this->course_id . ' AND uploader_id = ' . intval($stud_id) . " AND title = '" . Database::escape_string($eval->get_name()) . "'";
$result = Database::query($sql);
if ($fileurl = Database::fetch_row($result)) {
return null;
} else {
return null;
}
}
/**
* Saves imported data.
*/
function save_data($users_courses)
{
$user_table = Database::get_main_table(TABLE_MAIN_USER);
$course_user_table = Database::get_main_table(TABLE_MAIN_COURSE_USER);
$csv_data = array();
$inserted_in_course = array();
foreach ($users_courses as $user_course) {
$csv_data[$user_course['Email']][$user_course['CourseCode']] = $user_course['Status'];
}
foreach ($csv_data as $email => $csv_subscriptions) {
$sql = "SELECT * FROM {$user_table} u\n WHERE u.email = '" . Database::escape_string($email) . "' LIMIT 1";
$res = Database::query($sql);
$obj = Database::fetch_object($res);
$user_id = $obj->user_id;
$sql = "SELECT * FROM {$course_user_table} cu\n WHERE cu.user_id = {$user_id} AND cu.relation_type <> " . COURSE_RELATION_TYPE_RRHH . " ";
$res = Database::query($sql);
$db_subscriptions = array();
while ($obj = Database::fetch_object($res)) {
$db_subscriptions[$obj->c_id] = $obj->status;
}
$to_subscribe = array_diff(array_keys($csv_subscriptions), array_keys($db_subscriptions));
$to_unsubscribe = array_diff(array_keys($db_subscriptions), array_keys($csv_subscriptions));
if ($_POST['subscribe']) {
foreach ($to_subscribe as $courseId) {
$courseInfo = api_get_course_info_by_id($courseId);
$course_code = $courseInfo['code'];
if (CourseManager::course_exists($course_code)) {
$course_info = CourseManager::get_course_information($course_code);
$inserted_in_course[$course_code] = $course_info['title'];
CourseManager::subscribe_user($user_id, $course_code, $csv_subscriptions[$course_code]);
$inserted_in_course[$course_info['code']] = $course_info['title'];
}
}
}
if ($_POST['unsubscribe']) {
foreach ($to_unsubscribe as $courseId) {
$courseInfo = api_get_course_info_by_id($courseId);
$course_code = $courseInfo['code'];
if (CourseManager::course_exists($course_code)) {
CourseManager::unsubscribe_user($user_id, $course_code);
$course_info = CourseManager::get_course_information($course_code);
CourseManager::unsubscribe_user($user_id, $course_code);
$inserted_in_course[$course_info['code']] = $course_info['title'];
}
}
}
}
return $inserted_in_course;
}
/**
* Generate an array of exercises that a teacher hasn't created a link for.
* @return array 2-dimensional array - every element contains 2 subelements (id, name)
*/
public function get_not_created_links()
{
return false;
if (empty($this->course_code)) {
die('Error in get_not_created_links() : course code not set');
}
$tbl_grade_links = Database::get_main_table(TABLE_MAIN_GRADEBOOK_LINK);
$sql = 'SELECT id, url from ' . $this->get_studpub_table() . ' pup WHERE c_id = ' . $this->course_id . ' AND has_properties != ' . "''" . ' AND id NOT IN' . ' (SELECT ref_id FROM ' . $tbl_grade_links . ' WHERE type = ' . LINK_STUDENTPUBLICATION . " AND course_code = '" . Database::escape_string($this->get_course_code()) . "'" . ') AND pub.session_id=' . api_get_session_id() . '';
$result = Database::query($sql);
$cats = array();
while ($data = Database::fetch_array($result)) {
$cats[] = array($data['id'], $data['url']);
}
return $cats;
}
/**
*
* Returns the URL of a document
* This function is loaded when using a gradebook as a tab (gradebook = -1)
* see issue #2705
*
*/
public function get_view_url($stud_id)
{
// find a file uploaded by the given student,
// with the same title as the evaluation name
$eval = $this->get_evaluation();
$stud_id = intval($stud_id);
$sql = 'SELECT pub.url
FROM ' . $this->get_itemprop_table() . ' prop, ' . $this->get_studpub_table() . ' pub' . " WHERE\n\t\t\t\t\tprop.c_id = " . $this->course_id . " AND\n\t\t\t\t\tpub.c_id = " . $this->course_id . " AND\n\t\t\t\t\tprop.tool = 'work'" . ' AND prop.insert_user_id = ' . $stud_id . ' AND prop.ref = pub.id' . " AND pub.title = '" . Database::escape_string($eval->get_name()) . "' AND pub.session_id=" . api_get_session_id() . "";
$result = Database::query($sql);
if ($fileurl = Database::fetch_row($result)) {
return null;
} else {
return null;
}
}
