function workshop_get_submit_logs($course, $timestart)
{
// get the "submit" entries and add the first and last names...
global $CFG, $USER;
$timethen = time() - $CFG->maxeditingtime;
return get_records_sql("SELECT l.time, l.url, u.firstname, u.lastname, l.info as workshopid, e.name\n FROM {$CFG->prefix}log l,\n {$CFG->prefix}workshop e,\n {$CFG->prefix}user u\n WHERE l.time > {$timestart} AND l.time < {$timethen}\n AND l.course = {$course->id} AND l.module = 'workshop'\n AND l.action = 'submit'\n AND e.id = " . sql_cast_char2int('l.info') . "\n AND u.id = l.userid");
}
/**
* Get rows from the log related to the user's "add entry" activity
*
* Get the "add entry" entries and add the first and last names, we are not
* interested in the entries make by this user (the last condition)!
* Called from get_recent_activity()
* @param object $course
* @param timestamp $timestart filter by entries later than this date/time
* @return array of log fields and related user fields
* @todo sql_cast_char2int will choke if l.info is a string, its usually an int, but need to review this
*/
function dialogue_get_add_entry_logs($course, $timestart)
{
global $CFG, $USER;
if (!isset($USER->id)) {
return false;
}
return get_records_sql("SELECT l.time, l.url, u.firstname, u.lastname, e.dialogueid, d.name, c.subject, l.userid " . "FROM {$CFG->prefix}log l," . "{$CFG->prefix}dialogue d," . "{$CFG->prefix}dialogue_conversations c," . "{$CFG->prefix}dialogue_entries e," . "{$CFG->prefix}user u" . " WHERE l.time > {$timestart} AND l.course = {$course->id} AND l.module = 'dialogue'" . " AND l.action = 'add entry'" . " AND e.id = " . sql_cast_char2int('l.info') . " AND c.id = e.conversationid " . " AND (c.userid = {$USER->id} or c.recipientid = {$USER->id}) " . " AND d.id = e.dialogueid" . " AND u.id = e.userid" . " AND e.userid <> '{$USER->id}'");
}
/**
* Searches logs to find all enrolments since a certain date
*
* used to print recent activity
*
* @uses $CFG
* @param int $courseid The course in question.
* @return object|false {@link $USER} records or false if error.
* @todo Finish documenting this function
*/
function get_recent_enrolments($courseid, $timestart)
{
global $CFG;
$context = get_context_instance(CONTEXT_COURSE, $courseid);
return get_records_sql("SELECT DISTINCT u.id, u.firstname, u.lastname, l.time\n FROM {$CFG->prefix}user u,\n {$CFG->prefix}role_assignments ra,\n {$CFG->prefix}log l\n WHERE l.time > '{$timestart}'\n AND l.course = '{$courseid}'\n AND l.module = 'course'\n AND l.action = 'enrol'\n AND " . sql_cast_char2int('l.info') . " = u.id\n AND u.id = ra.userid\n AND ra.contextid " . get_related_contexts_string($context) . "\n ORDER BY l.time ASC");
}
function insert_category_and_question_ids($course, $backup_unique_code, $instances = null)
{
global $CFG;
// Create missing categories and reasign orphaned questions.
fix_orphaned_questions($course);
// First, all categories from this course's context.
$coursecontext = get_context_instance(CONTEXT_COURSE, $course);
$status = execute_sql("INSERT INTO {$CFG->prefix}backup_ids\n (backup_code, table_name, old_id, info)\n SELECT '{$backup_unique_code}', 'question_categories', qc.id, ''\n FROM {$CFG->prefix}question_categories qc\n WHERE qc.contextid = {$coursecontext->id}", false);
// then, all categories from this course's modules' contexts.
// using 'dummykeyname' in sql because otherwise get_records_sql_menu returns an error
// if two key names are the same.
$cmcontexts = get_records_sql_menu("SELECT c.id, c.id AS dummykeyname FROM {$CFG->prefix}modules m,\n {$CFG->prefix}course_modules cm,\n {$CFG->prefix}context c\n WHERE m.name = 'quiz' AND m.id = cm.module AND cm.id = c.instanceid\n AND c.contextlevel = " . CONTEXT_MODULE . " AND cm.course = {$course}");
if ($cmcontexts) {
$status = $status && execute_sql("INSERT INTO {$CFG->prefix}backup_ids\n (backup_code, table_name, old_id, info)\n SELECT '{$backup_unique_code}', 'question_categories', qc.id, ''\n FROM {$CFG->prefix}question_categories qc\n WHERE qc.contextid IN (" . join(array_keys($cmcontexts), ', ') . ")", false);
}
//put the ids of the questions from all these categories into the db.
$status = $status && execute_sql("INSERT INTO {$CFG->prefix}backup_ids\n (backup_code, table_name, old_id, info)\n SELECT '{$backup_unique_code}', 'question', q.id, ''\n FROM {$CFG->prefix}question q, {$CFG->prefix}backup_ids bk\n WHERE q.category = bk.old_id AND bk.table_name = 'question_categories' AND\n bk.backup_code = '{$backup_unique_code}'", false);
// Then categories from parent contexts used by the quizzes we are backing up.
//TODO this will need generalising when we have modules other than quiz using shared questions above course level.
$parentcontexts = get_parent_contexts($coursecontext);
$from = "{$CFG->prefix}quiz quiz,";
$where = "AND quiz.course = '{$course}'\n AND qqi.quiz = quiz.id";
if (!empty($instances) && is_array($instances) && count($instances)) {
$questionselectsqlfrom = '';
$questionselectsqlwhere = 'AND qqi.quiz IN (' . implode(',', array_keys($instances)) . ')';
} else {
$questionselectsqlfrom = "{$CFG->prefix}quiz quiz,";
$questionselectsqlwhere = "AND quiz.course = '{$course}'\n AND qqi.quiz = quiz.id";
}
$categories = get_records_sql("\n SELECT id, parent, 0 AS childrendone\n FROM {$CFG->prefix}question_categories\n WHERE contextid IN (" . join($parentcontexts, ', ') . ")\n AND id IN (\n SELECT DISTINCT question.category\n FROM {$CFG->prefix}question question,\n {$questionselectsqlfrom}\n {$CFG->prefix}quiz_question_instances qqi\n WHERE qqi.question = question.id\n {$questionselectsqlwhere}\n )", false);
if (!$categories) {
$categories = array();
} else {
//put the ids of the used questions from all these categories into the db.
$status = $status && execute_sql("INSERT INTO {$CFG->prefix}backup_ids\n (backup_code, table_name, old_id, info)\n SELECT DISTINCT {$backup_unique_code}, 'question', q.id, ''\n FROM {$CFG->prefix}question q,\n {$from}\n {$CFG->prefix}question_categories qc,\n {$CFG->prefix}quiz_question_instances qqi\n WHERE (qqi.question = q.id\n OR qqi.question = q.parent)\n AND q.category = qc.id\n AND qc.contextid IN (" . join($parentcontexts, ', ') . ")\n {$where}", false);
// Add the parent categories, of these categories up to the top of the category tree.
// not backing up the questions in these categories.
foreach ($categories as $category) {
while ($category->parent != 0) {
if (array_key_exists($category->parent, $categories)) {
// Parent category already on the list.
break;
}
$currentid = $category->id;
$category = get_record('question_categories', 'id', $category->parent, '', '', '', '', 'id, parent, 0 AS childrendone');
if ($category) {
$categories[$category->id] = $category;
} else {
// Parent not found: this indicates an error, but just fix it.
set_field('question_categories', 'parent', 0, 'id', $currentid);
break;
}
}
}
// Now we look for categories from other courses containing random questions
// in our quizzes that select from the category and its subcategories. That implies
// those subcategories also need to be backed up. (The categories themselves
// and their parents will already have been included.)
$categorieswithrandom = get_records_sql("\n SELECT question.category AS id, SUM(" . sql_cast_char2int('questiontext', true) . ") AS numqsusingsubcategories\n FROM {$CFG->prefix}quiz_question_instances qqi,\n {$from}\n {$CFG->prefix}question question\n WHERE question.id = qqi.question\n AND question.qtype = '" . RANDOM . "'\n {$where}\n GROUP BY question.category\n ");
$randomselectedquestions = array();
if ($categorieswithrandom) {
foreach ($categorieswithrandom as $category) {
if ($category->numqsusingsubcategories > 0) {
$status = $status && quiz_backup_add_sub_categories($categories, $randomselectedquestions, $category->id);
}
}
$returnval = get_records_sql("\n SELECT question.id\n FROM {$CFG->prefix}question question\n WHERE question.category IN (" . join(array_keys($categorieswithrandom), ', ') . ")");
if ($returnval) {
$randomselectedquestions += $returnval;
}
}
// Finally, add all these extra categories to the backup_ids table.
foreach ($categories as $category) {
$status = $status && backup_putid($backup_unique_code, 'question_categories', $category->id, 0);
}
// Finally, add all these extra categories to the backup_ids table.
foreach ($randomselectedquestions as $question) {
$status = $status && backup_putid($backup_unique_code, 'question', $question->id, 0);
}
}
return $status;
}
function get_sort_sql($fieldname)
{
return sql_cast_char2int($fieldname, true);
}
function dialogue_get_add_entry_logs($course, $timestart)
{
// get the "add entry" entries and add the first and last names, we are not interested in the entries
// make by this user (the last condition)!
global $CFG, $USER;
if (!isset($USER->id)) {
return false;
}
return get_records_sql("SELECT l.time, l.url, u.firstname, u.lastname, e.dialogueid, d.name, c.subject, l.userid\n FROM {$CFG->prefix}log l,\n {$CFG->prefix}dialogue d, \n {$CFG->prefix}dialogue_conversations c, \n {$CFG->prefix}dialogue_entries e, \n {$CFG->prefix}user u\n WHERE l.time > {$timestart} AND l.course = {$course->id} AND l.module = 'dialogue'\n AND l.action = 'add entry'\n AND e.id = " . sql_cast_char2int('l.info') . " \n AND c.id = e.conversationid\n AND (c.userid = {$USER->id} or c.recipientid = {$USER->id})\n AND d.id = e.dialogueid\n AND u.id = e.userid \n AND e.userid <> {$USER->id}");
}