function search($query, $course, &$bookids, $offset, &$countentries)
{
global $CFG, $USER, $DB;
// Perform the search only in books fulfilling mod/book:read and (visible or moodle/course:viewhiddenactivities)
if (empty($bookids)) {
$bookids = book_search_get_readble_books($course);
}
// transform the search query into safe SQL queries
$searchterms = explode(" ", $query);
$parser = new search_parser();
$lexer = new search_lexer($parser);
if ($lexer->parse($query)) {
$parsearray = $parser->get_parsed_array();
list($messagesearch, $msparams) = search_generate_SQL($parsearray, 'bc.title', 'bc.content', null, null, null, null, null, null);
}
// Main query, only to allowed books and not hidden chapters.
$selectsql = "SELECT DISTINCT bc.*";
$fromsql = " FROM {book_chapters} bc, {book} b";
list($insql, $inparams) = $DB->get_in_or_equal($bookids, SQL_PARAMS_NAMED);
$params = array_merge(array('courseid' => $course->id), $inparams, $msparams);
$wheresql = " WHERE b.course = :courseid\n AND b.id {$insql} \n AND bc.bookid = b.id \n AND bc.hidden = 0\n AND {$messagesearch} ";
$ordersql = " ORDER BY bc.bookid, bc.pagenum";
// Set page limits.
$limitfrom = $offset;
$limitnum = 0;
if ($offset >= 0) {
$limitnum = BOOKMAXRESULTSPERPAGE;
}
$countentries = $DB->count_records_sql("select count(*) {$fromsql} {$wheresql}", $params);
$allentries = $DB->get_records_sql("{$selectsql} {$fromsql} {$wheresql} {$ordersql}", $params, $limitfrom, $limitnum);
return $allentries;
}
/**
* Returns a list of posts found using an array of search terms.
*
* @global object
* @global object
* @global object
* @param array $searchterms array of search terms, e.g. word +word -word
* @param int $courseid if 0, we search through the whole site
* @param int $limitfrom
* @param int $limitnum
* @param int &$totalcount
* @param string $extrasql
* @return array|bool Array of posts found or false
*/
function forum_search_posts($searchterms, $courseid=0, $limitfrom=0, $limitnum=50,
&$totalcount, $extrasql='') {
global $CFG, $DB, $USER;
require_once($CFG->libdir.'/searchlib.php');
$forums = forum_get_readable_forums($USER->id, $courseid);
if (count($forums) == 0) {
$totalcount = 0;
return false;
}
$now = round(time(), -2); // db friendly
$fullaccess = array();
$where = array();
$params = array();
foreach ($forums as $forumid => $forum) {
$select = array();
if (!$forum->viewhiddentimedposts) {
$select[] = "(d.userid = :userid{$forumid} OR (d.timestart < :timestart{$forumid} AND (d.timeend = 0 OR d.timeend > :timeend{$forumid})))";
$params = array_merge($params, array('userid'.$forumid=>$USER->id, 'timestart'.$forumid=>$now, 'timeend'.$forumid=>$now));
}
$cm = $forum->cm;
$context = $forum->context;
if ($forum->type == 'qanda'
&& !has_capability('mod/forum:viewqandawithoutposting', $context)) {
if (!empty($forum->onlydiscussions)) {
list($discussionid_sql, $discussionid_params) = $DB->get_in_or_equal($forum->onlydiscussions, SQL_PARAMS_NAMED, 'qanda'.$forumid.'_');
$params = array_merge($params, $discussionid_params);
$select[] = "(d.id $discussionid_sql OR p.parent = 0)";
} else {
$select[] = "p.parent = 0";
}
}
if (!empty($forum->onlygroups)) {
list($groupid_sql, $groupid_params) = $DB->get_in_or_equal($forum->onlygroups, SQL_PARAMS_NAMED, 'grps'.$forumid.'_');
$params = array_merge($params, $groupid_params);
$select[] = "d.groupid $groupid_sql";
}
if ($select) {
$selects = implode(" AND ", $select);
$where[] = "(d.forum = :forum{$forumid} AND $selects)";
$params['forum'.$forumid] = $forumid;
} else {
$fullaccess[] = $forumid;
}
}
if ($fullaccess) {
list($fullid_sql, $fullid_params) = $DB->get_in_or_equal($fullaccess, SQL_PARAMS_NAMED, 'fula');
$params = array_merge($params, $fullid_params);
$where[] = "(d.forum $fullid_sql)";
}
$selectdiscussion = "(".implode(" OR ", $where).")";
$messagesearch = '';
$searchstring = '';
// Need to concat these back together for parser to work.
foreach($searchterms as $searchterm){
if ($searchstring != '') {
$searchstring .= ' ';
}
$searchstring .= $searchterm;
}
// We need to allow quoted strings for the search. The quotes *should* be stripped
// by the parser, but this should be examined carefully for security implications.
$searchstring = str_replace("\\\"","\"",$searchstring);
$parser = new search_parser();
$lexer = new search_lexer($parser);
if ($lexer->parse($searchstring)) {
$parsearray = $parser->get_parsed_array();
// Experimental feature under 1.8! MDL-8830
// Use alternative text searches if defined
// This feature only works under mysql until properly implemented for other DBs
// Requires manual creation of text index for forum_posts before enabling it:
// CREATE FULLTEXT INDEX foru_post_tix ON [prefix]forum_posts (subject, message)
// Experimental feature under 1.8! MDL-8830
if (!empty($CFG->forum_usetextsearches)) {
list($messagesearch, $msparams) = search_generate_text_SQL($parsearray, 'p.message', 'p.subject',
'p.userid', 'u.id', 'u.firstname',
'u.lastname', 'p.modified', 'd.forum');
} else {
list($messagesearch, $msparams) = search_generate_SQL($parsearray, 'p.message', 'p.subject',
'p.userid', 'u.id', 'u.firstname',
'u.lastname', 'p.modified', 'd.forum');
}
$params = array_merge($params, $msparams);
}
$fromsql = "{forum_posts} p,
{forum_discussions} d,
{user} u";
$selectsql = " $messagesearch
AND p.discussion = d.id
AND p.userid = u.id
AND $selectdiscussion
$extrasql";
$countsql = "SELECT COUNT(*)
FROM $fromsql
WHERE $selectsql";
$searchsql = "SELECT p.*,
d.forum,
u.firstname,
u.lastname,
u.email,
u.picture,
u.imagealt
FROM $fromsql
WHERE $selectsql
ORDER BY p.modified DESC";
$totalcount = $DB->count_records_sql($countsql, $params);
return $DB->get_records_sql($searchsql, $params, $limitfrom, $limitnum);
}
/**
* Returns a list of posts found using an array of search terms.
*
* @global object
* @global object
* @global object
* @param array $searchterms array of search terms, e.g. word +word -word
* @param int $courseid if 0, we search through the whole site
* @param int $limitfrom
* @param int $limitnum
* @param int &$totalcount
* @param string $extrasql
* @return array|bool Array of posts found or false
*/
function forum_search_posts($searchterms, $courseid = 0, $limitfrom = 0, $limitnum = 50, &$totalcount, $extrasql = '')
{
global $CFG, $DB, $USER;
require_once $CFG->libdir . '/searchlib.php';
$forums = forum_get_readable_forums($USER->id, $courseid);
if (count($forums) == 0) {
$totalcount = 0;
return false;
}
$now = round(time(), -2);
// db friendly
$fullaccess = array();
$where = array();
$params = array();
foreach ($forums as $forumid => $forum) {
$select = array();
if (!$forum->viewhiddentimedposts) {
$select[] = "(d.userid = :userid{$forumid} OR (d.timestart < :timestart{$forumid} AND (d.timeend = 0 OR d.timeend > :timeend{$forumid})))";
$params = array_merge($params, array('userid' . $forumid => $USER->id, 'timestart' . $forumid => $now, 'timeend' . $forumid => $now));
}
$cm = $forum->cm;
$context = $forum->context;
if ($forum->type == 'qanda' && !has_capability('mod/forum:viewqandawithoutposting', $context)) {
if (!empty($forum->onlydiscussions)) {
list($discussionid_sql, $discussionid_params) = $DB->get_in_or_equal($forum->onlydiscussions, SQL_PARAMS_NAMED, 'qanda' . $forumid . '_');
$params = array_merge($params, $discussionid_params);
$select[] = "(d.id {$discussionid_sql} OR p.parent = 0)";
} else {
$select[] = "p.parent = 0";
}
}
if (!empty($forum->onlygroups)) {
list($groupid_sql, $groupid_params) = $DB->get_in_or_equal($forum->onlygroups, SQL_PARAMS_NAMED, 'grps' . $forumid . '_');
$params = array_merge($params, $groupid_params);
$select[] = "d.groupid {$groupid_sql}";
}
if ($select) {
$selects = implode(" AND ", $select);
$where[] = "(d.forum = :forum{$forumid} AND {$selects})";
$params['forum' . $forumid] = $forumid;
} else {
$fullaccess[] = $forumid;
}
}
if ($fullaccess) {
list($fullid_sql, $fullid_params) = $DB->get_in_or_equal($fullaccess, SQL_PARAMS_NAMED, 'fula');
$params = array_merge($params, $fullid_params);
$where[] = "(d.forum {$fullid_sql})";
}
$selectdiscussion = "(" . implode(" OR ", $where) . ")";
$messagesearch = '';
$searchstring = '';
// Need to concat these back together for parser to work.
foreach ($searchterms as $searchterm) {
if ($searchstring != '') {
$searchstring .= ' ';
}
$searchstring .= $searchterm;
}
// We need to allow quoted strings for the search. The quotes *should* be stripped
// by the parser, but this should be examined carefully for security implications.
$searchstring = str_replace("\\\"", "\"", $searchstring);
$parser = new search_parser();
$lexer = new search_lexer($parser);
if ($lexer->parse($searchstring)) {
$parsearray = $parser->get_parsed_array();
list($messagesearch, $msparams) = search_generate_SQL($parsearray, 'p.message', 'p.subject', 'p.userid', 'u.id', 'u.firstname', 'u.lastname', 'p.modified', 'd.forum');
$params = array_merge($params, $msparams);
}
$fromsql = "{forum_posts} p,\n {forum_discussions} d,\n {user} u";
$selectsql = " {$messagesearch}\n AND p.discussion = d.id\n AND p.userid = u.id\n AND {$selectdiscussion}\n {$extrasql}";
$countsql = "SELECT COUNT(*)\n FROM {$fromsql}\n WHERE {$selectsql}";
$allnames = get_all_user_name_fields(true, 'u');
$searchsql = "SELECT p.*,\n d.forum,\n {$allnames},\n u.email,\n u.picture,\n u.imagealt\n FROM {$fromsql}\n WHERE {$selectsql}\n ORDER BY p.modified DESC";
$totalcount = $DB->count_records_sql($countsql, $params);
return $DB->get_records_sql($searchsql, $params, $limitfrom, $limitnum);
}
/**
* Returns a list of posts found using an array of search terms.
* @param $searchterms - array of search terms, e.g. word +word -word
* @param $courseid - if 0, we search through the whole site
* @param $page
* @param $recordsperpage=50
* @param &$totalcount
* @param $extrasql
* @return array of posts found
*/
function forum_search_posts($searchterms, $courseid = 0, $limitfrom = 0, $limitnum = 50, &$totalcount, $extrasql = '')
{
global $CFG, $USER;
require_once $CFG->libdir . '/searchlib.php';
$forums = forum_get_readable_forums($USER->id, $courseid);
if (count($forums) == 0) {
$totalcount = 0;
return false;
}
$now = round(time(), -2);
// db friendly
$fullaccess = array();
$where = array();
foreach ($forums as $forumid => $forum) {
$select = array();
if (!$forum->viewhiddentimedposts) {
$select[] = "(d.userid = {$USER->id} OR (d.timestart < {$now} AND (d.timeend = 0 OR d.timeend > {$now})))";
}
if ($forum->type == 'qanda') {
if (!empty($forum->onlydiscussions)) {
$discussionsids = implode(',', $forum->onlydiscussions);
$select[] = "(d.id IN ({$discussionsids}) OR p.parent = 0)";
} else {
$select[] = "p.parent = 0";
}
}
if (!empty($forum->onlygroups)) {
$groupids = implode(',', $forum->onlygroups);
$select[] = "d.groupid IN ({$groupids})";
}
if ($select) {
$selects = implode(" AND ", $select);
$where[] = "(d.forum = {$forumid} AND {$selects})";
} else {
$fullaccess[] = $forumid;
}
}
if ($fullaccess) {
$fullids = implode(',', $fullaccess);
$where[] = "(d.forum IN ({$fullids}))";
}
$selectdiscussion = "(" . implode(" OR ", $where) . ")";
// Some differences SQL
$LIKE = sql_ilike();
$NOTLIKE = 'NOT ' . $LIKE;
if ($CFG->dbfamily == 'postgres') {
$REGEXP = '~*';
$NOTREGEXP = '!~*';
} else {
$REGEXP = 'REGEXP';
$NOTREGEXP = 'NOT REGEXP';
}
$messagesearch = '';
$searchstring = '';
// Need to concat these back together for parser to work.
foreach ($searchterms as $searchterm) {
if ($searchstring != '') {
$searchstring .= ' ';
}
$searchstring .= $searchterm;
}
// We need to allow quoted strings for the search. The quotes *should* be stripped
// by the parser, but this should be examined carefully for security implications.
$searchstring = str_replace("\\\"", "\"", $searchstring);
$parser = new search_parser();
$lexer = new search_lexer($parser);
if ($lexer->parse($searchstring)) {
$parsearray = $parser->get_parsed_array();
// Experimental feature under 1.8! MDL-8830
// Use alternative text searches if defined
// This feature only works under mysql until properly implemented for other DBs
// Requires manual creation of text index for forum_posts before enabling it:
// CREATE FULLTEXT INDEX foru_post_tix ON [prefix]forum_posts (subject, message)
// Experimental feature under 1.8! MDL-8830
if (!empty($CFG->forum_usetextsearches)) {
$messagesearch = search_generate_text_SQL($parsearray, 'p.message', 'p.subject', 'p.userid', 'u.id', 'u.firstname', 'u.lastname', 'p.modified', 'd.forum');
} else {
$messagesearch = search_generate_SQL($parsearray, 'p.message', 'p.subject', 'p.userid', 'u.id', 'u.firstname', 'u.lastname', 'p.modified', 'd.forum');
}
}
$fromsql = "{$CFG->prefix}forum_posts p,\n {$CFG->prefix}forum_discussions d,\n {$CFG->prefix}user u";
$selectsql = " {$messagesearch}\n AND p.discussion = d.id\n AND p.userid = u.id\n AND {$selectdiscussion}\n {$extrasql}";
$countsql = "SELECT COUNT(*)\n FROM {$fromsql}\n WHERE {$selectsql}";
$searchsql = "SELECT p.*,\n d.forum,\n u.firstname,\n u.lastname,\n u.email,\n u.picture,\n u.imagealt\n FROM {$fromsql}\n WHERE {$selectsql}\n ORDER BY p.modified DESC";
$totalcount = count_records_sql($countsql);
return get_records_sql($searchsql, $limitfrom, $limitnum);
}
function dcmetadata_search_metadata($searchterms, $courseid, $searchcourseid, $page = 0, $recordsperpage = 50, &$totalcountdc, $extrasql = '')
{
/// Returns a list of los metadatadc found using an array of search terms
/// eg word +word -word
///
global $CFG, $USER;
// require_once('meta_searchlib.php');
/*
if (!isteacher($courseid)) {
$notteacherforum = "AND f.type <> 'teacher'";
$forummodule = get_record("modules", "name", "forum");
$onlyvisible = "AND d.forum = f.id AND f.id = cm.instance AND cm.visible = 1 AND cm.module = $forummodule->id";
$onlyvisibletable = ", {$CFG->prefix}course_modules cm, {$CFG->prefix}forum f";
if (!empty($sepgroups)) {
$separategroups = SEPARATEGROUPS;
$selectgroup = " AND ( NOT (cm.groupmode='$separategroups'".
" OR (c.groupmode='$separategroups' AND c.groupmodeforce='1') )";//.
$selectgroup .= " OR d.groupid = '-1'"; //search inside discussions for all groups too
foreach ($sepgroups as $sepgroup){
$selectgroup .= " OR d.groupid = '$sepgroup->id'";
}
$selectgroup .= ")";
// " OR d.groupid = '$groupid')";
$selectcourse = " AND d.course = '$courseid' AND c.id='$courseid'";
$coursetable = ", {$CFG->prefix}course c";
} else {
$selectgroup = '';
$selectcourse = " AND d.course = '$courseid'";
$coursetable = '';
}
} else { */
//$notteacherforum = "";
//$selectgroup = '';
//$onlyvisible = "";
//$onlyvisibletable = "";
//$coursetable = '';
//if ($courseid == SITEID && isadmin()) {
if ($searchcourseid == '9999') {
$mycourses = get_my_courses($USER->id);
foreach ($mycourses as $mycourse) {
$mcstring = $mcstring . " d.course = '{$mycourse->id}' OR";
}
$mcstringfinal = meta_cut_final($mcstring);
$selectcourse = " AND" . $mcstringfinal;
} elseif ($searchcourseid == '0') {
$selectcourse = '';
} elseif (!$searchcourseid == '9999' | !$searchcourseid == '0' | !empty($searchcourseid)) {
$selectcourse = " AND d.course = '{$searchcourseid}'";
} else {
if ($courseid == SITEID) {
$selectcourse = '';
} else {
$selectcourse = " AND d.course = '{$courseid}'";
}
}
$timelimit = '';
// if (!empty($CFG->forum_enabletimedposts) && (!((isadmin() and !empty($CFG->admineditalways)) || isteacher($courseid)))) {
//if ((!((isadmin() and !empty($CFG->admineditalways)) || isteacher($courseid)))) {
$now = time();
// $timelimit = " AND (d.userid = $USER->id OR ((d.timestart = 0 OR d.timestart <= $now) AND (d.timeend = 0 OR d.timeend > $now)))";
$timelimit = " AND (d.userid = {$USER->id} OR ((d.timemodified = 0 OR d.timemodified <= {$now}) AND (d.timemodified = 0 OR d.timemodified > {$now})))";
// $timelimit = " AND ((d.timemodified <= $now) OR (d.timemodified > $now)))";
// }
$limit = sql_paging_limit($page, $recordsperpage);
/// Some differences in syntax for PostgreSQL
if ($CFG->dbtype == "postgres7") {
$LIKE = "ILIKE";
// case-insensitive
$NOTLIKE = "NOT ILIKE";
// case-insensitive
$REGEXP = "~*";
$NOTREGEXP = "!~*";
} else {
$LIKE = "LIKE";
$NOTLIKE = "NOT LIKE";
$REGEXP = "REGEXP";
$NOTREGEXP = "NOT REGEXP";
}
$metasearch = "";
$searchstring = "";
// Need to concat these back together for parser to work.
foreach ($searchterms as $searchterm) {
if ($searchstring != "") {
$searchstring .= " ";
}
$searchstring .= $searchterm;
}
// We need to allow quoted strings for the search. The quotes *should* be stripped
// by the parser, but this should be examined carefully for security implications.
$searchstring = str_replace("\\\"", "\"", $searchstring);
$parser = new search_parser();
$lexer = new search_lexer($parser);
if ($lexer->parse($searchstring)) {
$parsearray = $parser->get_parsed_array();
//search_generate_sql($parsetree, $datafield, $metafield, $mainidfield, $useridfield,$userfirstnamefield, $userlastnamefield, $timefield, $instancefield)
//$messagesearch = search_generate_SQL($parsearray,'p.message','p.subject','p.userid','u.id','u.firstname','u.lastname','p.modified', 'd.forum');
$metasearch = metasearch_generate_SQL($parsearray, 'd.title', 'd.alternative', 'd.type', 'd.format', 'd.audience', 'd.instructionalMethod', 'd.subject', 'd.description', 'd.abstract', 'd.tableOfContents', 'd.bibliographicCitation', 'd.coverage', 'd.creator', 'd.publisher', 'd.contributor', 'd.userid', 'd.timemodified', '');
}
//'cm.modnameid','d.timemodified','d.course');
/*
$selectsql = "{$CFG->prefix}forum_posts p,
{$CFG->prefix}forum_discussions d,
{$CFG->prefix}user u $onlyvisibletable $coursetable
WHERE ($messagesearch)
AND p.userid = u.id
AND p.discussion = d.id $selectcourse $notteacherforum $onlyvisible $selectgroup $timelimit $extrasql";
$totalcount = count_records_sql("SELECT COUNT(*) FROM $selectsql");
return get_records_sql("SELECT p.*,d.forum, u.firstname,u.lastname,u.email,u.picture FROM
$selectsql ORDER BY p.modified DESC $limit");
*/
$selectsql = "{$CFG->prefix}metadatadc d\r\n WHERE ({$metasearch})\r\n \t\t{$selectcourse} {$extrasql}";
//AND cm.course = d.course AND d.resource = cm.modnameid $selectcourse $extrasql"; {$CFG->prefix}course_module cm
$totalcountdc = count_records_sql("SELECT COUNT(*) FROM {$selectsql}");
return get_records_sql("SELECT d.* FROM\r\n {$selectsql} ORDER BY d.course, d.timemodified DESC {$limit}");
}
$sqlsubject = '';
if (!empty($search)) {
$searchstring = str_replace("\\\"", "\"", $search);
$parser = new search_parser();
$lexer = new search_lexer($parser);
if ($lexer->parse($searchstring)) {
$parsearray = $parser->get_parsed_array();
$sqlsubject = search_generate_text_SQL($parsearray, 'm.subject', '', 'm.userid', 'u.id', 'u.firstname', 'u.lastname', 'm.timecreated', '');
}
}
// BODY
$sqlbody = '';
if (!empty($search)) {
$searchstring = str_replace("\\\"", "\"", $search);
$parser = new search_parser();
$lexer = new search_lexer($parser);
if ($lexer->parse($searchstring)) {
$parsearray = $parser->get_parsed_array();
$sqlbody = search_generate_text_SQL($parsearray, 'm.body', '', 'm.userid', 'u.id', 'u.firstname', 'u.lastname', 'm.timecreated', '');
$sqlsubjectbody = !empty($sqlsubject) ? " AND ( {$sqlsubject} OR {$sqlbody} " : ' AND ' . $sqlbody;
}
} else {
if (!empty($sqlsubject)) {
$sqlsubjectbody = ' AND ' . $sqlsubject;
} else {
$sqlsubjectbody = '';
}
}
$sqlcourse = " AND s.course = m.course AND m.course = {$courseid} AND s.course = {$courseid} ";
// README: If you can search by to, this simple search mode don't get this results, you use advanced search.
// Only search by: Folder and ( Subject or Body or From).
/**
* Returns a list of posts found using an array of search terms.
* @param $searchterms - array of search terms, e.g. word +word -word
* @param $courseid - if 0, we search through the whole site
* @param $page
* @param $recordsperpage=50
* @param &$totalcount
* @param $extrasql
* @return array of posts found
*/
function forum_search_posts($searchterms, $courseid = 0, $limitfrom = 0, $limitnum = 50, &$totalcount, $extrasql = '')
{
global $CFG, $USER;
require_once $CFG->libdir . '/searchlib.php';
$forums = forum_get_readable_forums($USER->id, $courseid);
if (count($forums) == 0) {
return false;
}
for ($i = 0; $i < count($forums); $i++) {
if ($i == 0) {
$selectdiscussion = " ((d.forum = {$forums[$i]->id}";
} else {
$selectdiscussion .= " OR (d.forum = {$forums[$i]->id}";
}
if (!empty($CFG->forum_enabletimedposts) && !$forums[$i]->viewhiddentimedposts) {
$now = time();
$selectdiscussion .= " AND ( d.userid = {$USER->id}\n OR ((d.timestart = 0 OR d.timestart <= {$now})\n AND (d.timeend = 0 OR d.timeend > {$now})) )";
}
if ($forums[$i]->type == 'qanda' && isset($forums[$i]->onlydiscussions)) {
// This is a qanda forum.
if (is_array($forums[$i]->onlydiscussions)) {
// Show question posts as well as posts from discussions in
// which the user has posted a reply.
$onlydiscussions = implode(' OR d.id = ', $forums[$i]->onlydiscussions);
$selectdiscussion .= " AND ((d.id = {$onlydiscussions}) OR p.parent = 0)";
} else {
// Show only the question posts.
$selectdiscussion .= ' AND (p.parent = 0)';
}
}
if (!$forums[$i]->accessallgroups) {
if (!empty($forums[$i]->accessgroup)) {
$groups = rtrim(implode(",", $forums[$i]->accessgroup), ",");
$selectdiscussion .= " AND (d.groupid in ({$groups})";
$selectdiscussion .= ' OR d.groupid = -1)';
// -1 means open for all groups.
} else {
// User isn't in any group. Only search discussions that are
// open to all groups.
$selectdiscussion .= ' AND d.groupid = -1';
}
}
$selectdiscussion .= ")\n";
}
$selectdiscussion .= ")";
// Some differences SQL
$LIKE = sql_ilike();
$NOTLIKE = 'NOT ' . $LIKE;
if ($CFG->dbfamily == 'postgres') {
$REGEXP = '~*';
$NOTREGEXP = '!~*';
} else {
$REGEXP = 'REGEXP';
$NOTREGEXP = 'NOT REGEXP';
}
$messagesearch = '';
$searchstring = '';
// Need to concat these back together for parser to work.
foreach ($searchterms as $searchterm) {
if ($searchstring != '') {
$searchstring .= ' ';
}
$searchstring .= $searchterm;
}
// We need to allow quoted strings for the search. The quotes *should* be stripped
// by the parser, but this should be examined carefully for security implications.
$searchstring = str_replace("\\\"", "\"", $searchstring);
$parser = new search_parser();
$lexer = new search_lexer($parser);
if ($lexer->parse($searchstring)) {
$parsearray = $parser->get_parsed_array();
// Experimental feature under 1.8! MDL-8830
// Use alternative text searches if defined
// This feature only works under mysql until properly implemented for other DBs
// Requires manual creation of text index for forum_posts before enabling it:
// CREATE FULLTEXT INDEX foru_post_tix ON [prefix]forum_posts (subject, message)
// Experimental feature under 1.8! MDL-8830
if (!empty($CFG->forum_usetextsearches)) {
$messagesearch = search_generate_text_SQL($parsearray, 'p.message', 'p.subject', 'p.userid', 'u.id', 'u.firstname', 'u.lastname', 'p.modified', 'd.forum');
} else {
$messagesearch = search_generate_SQL($parsearray, 'p.message', 'p.subject', 'p.userid', 'u.id', 'u.firstname', 'u.lastname', 'p.modified', 'd.forum');
}
}
$fromsql = "{$CFG->prefix}forum_posts p,\n {$CFG->prefix}forum_discussions d,\n {$CFG->prefix}user u";
$selectsql = " {$messagesearch}\n AND p.discussion = d.id\n AND p.userid = u.id\n AND {$selectdiscussion}\n {$extrasql}";
$countsql = "SELECT COUNT(*)\n FROM {$fromsql}\n WHERE {$selectsql}";
$searchsql = "SELECT p.*,\n d.forum,\n u.firstname,\n u.lastname,\n u.email,\n u.picture,\n u.imagealt\n FROM {$fromsql}\n WHERE {$selectsql}\n ORDER BY p.modified DESC";
$totalcount = count_records_sql($countsql);
return get_records_sql($searchsql, $limitfrom, $limitnum);
}
/**
* Returns a list of posts found using an array of search terms.
*
* @global object
* @global object
* @global object
* @param array $searchterms array of search terms, e.g. word +word -word
* @param int $courseid if 0, we search through the whole site
* @param int $limitfrom
* @param int $limitnum
* @param int &$totalcount
* @param string $extrasql
* @return array|bool Array of posts found or false
*/
function hsuforum_search_posts($searchterms, $courseid = 0, $limitfrom = 0, $limitnum = 50, &$totalcount, $extrasql = '')
{
global $CFG, $DB, $USER;
require_once $CFG->libdir . '/searchlib.php';
$forums = hsuforum_get_readable_forums($USER->id, $courseid);
if (count($forums) == 0) {
$totalcount = 0;
return false;
}
$now = round(time(), -2);
// db friendly
$fullaccess = array();
$where = array();
$params = array('privatereply1' => $USER->id, 'privatereply2' => $USER->id);
foreach ($forums as $forumid => $forum) {
$select = array();
if (!$forum->viewhiddentimedposts) {
$select[] = "(d.userid = :userid{$forumid} OR (d.timestart < :timestart{$forumid} AND (d.timeend = 0 OR d.timeend > :timeend{$forumid})))";
$params = array_merge($params, array('userid' . $forumid => $USER->id, 'timestart' . $forumid => $now, 'timeend' . $forumid => $now));
}
if ($forum->type == 'qanda' && !has_capability('mod/hsuforum:viewqandawithoutposting', $forum->context)) {
if (!empty($forum->onlydiscussions)) {
list($discussionid_sql, $discussionid_params) = $DB->get_in_or_equal($forum->onlydiscussions, SQL_PARAMS_NAMED, 'qanda' . $forumid . '_');
$params = array_merge($params, $discussionid_params);
$select[] = "(d.id {$discussionid_sql} OR p.parent = 0)";
} else {
$select[] = "p.parent = 0";
}
}
if (!empty($forum->onlygroups)) {
list($groupid_sql, $groupid_params) = $DB->get_in_or_equal($forum->onlygroups, SQL_PARAMS_NAMED, 'grps' . $forumid . '_');
$params = array_merge($params, $groupid_params);
$select[] = "d.groupid {$groupid_sql}";
}
if ($select) {
$selects = implode(" AND ", $select);
$where[] = "(d.forum = :forum{$forumid} AND {$selects})";
$params['forum' . $forumid] = $forumid;
} else {
$fullaccess[] = $forumid;
}
}
if ($fullaccess) {
list($fullid_sql, $fullid_params) = $DB->get_in_or_equal($fullaccess, SQL_PARAMS_NAMED, 'fula');
$params = array_merge($params, $fullid_params);
$where[] = "(d.forum {$fullid_sql})";
}
$selectdiscussion = "(" . implode(" OR ", $where) . ")";
$messagesearch = '';
$searchstring = '';
// Need to concat these back together for parser to work.
foreach ($searchterms as $searchterm) {
if ($searchstring != '') {
$searchstring .= ' ';
}
$searchstring .= $searchterm;
}
// We need to allow quoted strings for the search. The quotes *should* be stripped
// by the parser, but this should be examined carefully for security implications.
$searchstring = str_replace("\\\"", "\"", $searchstring);
$parser = new search_parser();
$lexer = new search_lexer($parser);
if ($lexer->parse($searchstring)) {
$parsearray = $parser->get_parsed_array();
// Experimental feature under 1.8! MDL-8830
// Use alternative text searches if defined
// This feature only works under mysql until properly implemented for other DBs
// Requires manual creation of text index for hsuforum_posts before enabling it:
// CREATE FULLTEXT INDEX foru_post_tix ON [prefix]hsuforum_posts (subject, message)
// Experimental feature under 1.8! MDL-8830
$usetextsearches = get_config('hsuforum', 'usetextsearches');
if (!empty($usetextsearches)) {
list($messagesearch, $msparams) = search_generate_text_SQL($parsearray, 'p.message', 'p.subject', 'p.userid', 'u.id', 'u.firstname', 'u.lastname', 'p.modified', 'd.forum');
} else {
list($messagesearch, $msparams) = search_generate_SQL($parsearray, 'p.message', 'p.subject', 'p.userid', 'u.id', 'u.firstname', 'u.lastname', 'p.modified', 'd.forum');
}
$params = array_merge($params, $msparams);
}
$fromsql = "{hsuforum_posts} p,\n {hsuforum_discussions} d JOIN {hsuforum} f ON f.id = d.forum,\n {user} u";
foreach ($parsearray as $item) {
if ($item->getType() == TOKEN_USER || $item->getType() == TOKEN_USERID) {
// Additional user SQL for anonymous posts.
$extrasql .= " AND ((f.anonymous != 1 OR p.userid = :currentuserid) OR p.reveal = 1) ";
$params['currentuserid'] = $USER->id;
break;
}
}
$selectsql = "(p.privatereply = 0\n OR p.privatereply = :privatereply1\n OR p.userid = :privatereply2\n )\n AND {$messagesearch}\n AND p.discussion = d.id\n AND p.userid = u.id\n AND {$selectdiscussion}\n {$extrasql}";
$countsql = "SELECT COUNT(*)\n FROM {$fromsql}\n WHERE {$selectsql}";
$allnames = get_all_user_name_fields(true, 'u');
$searchsql = "SELECT p.*,\n d.forum,\n {$allnames},\n u.email,\n u.picture,\n u.imagealt,\n u.email\n FROM {$fromsql}\n WHERE {$selectsql}\n ORDER BY p.modified DESC";
$totalcount = $DB->count_records_sql($countsql, $params);
return $DB->get_records_sql($searchsql, $params, $limitfrom, $limitnum);
}
function mediagallery_search_items($searchterms, $courses, $limitfrom = 0, $limitnum = 50, $extrasql = '')
{
global $CFG, $DB, $USER;
require_once $CFG->libdir . '/searchlib.php';
$collections = mediagallery_get_readable_collections($courses);
if (count($collections) == 0) {
return array(false, 0);
}
$fullaccess = array();
$where = array();
$params = array();
foreach ($collections as $collectionid => $collection) {
$select = array();
$cm = $collection->cm;
$context = $collection->context;
if (!empty($collection->onlygroups)) {
list($groupidsql, $groupidparams) = $DB->get_in_or_equal($collection->onlygroups, SQL_PARAMS_NAMED, 'grps' . $collectionid . '_');
$params = array_merge($params, $groupidparams);
$select[] = "g.groupid {$groupidsql}";
}
if ($select) {
$selects = implode(" AND ", $select);
$where[] = "(g.instanceid = :mediagallery{$collectionid} AND {$selects})";
$params['mediagallery' . $collectionid] = $collectionid;
} else {
$fullaccess[] = $collectionid;
}
}
if ($fullaccess) {
list($fullidsql, $fullidparams) = $DB->get_in_or_equal($fullaccess, SQL_PARAMS_NAMED, 'fula');
$params = array_merge($params, $fullidparams);
$where[] = "(g.instanceid {$fullidsql})";
}
$selectgallery = "(" . implode(" OR ", $where) . ")";
$messagesearch = '';
$searchstring = '';
// Need to concat these back together for parser to work.
foreach ($searchterms as $searchterm) {
if ($searchstring != '') {
$searchstring .= ' ';
}
$searchstring .= $searchterm;
}
// We need to allow quoted strings for the search. The quotes *should* be stripped
// by the parser, but this should be examined carefully for security implications.
$searchstring = str_replace("\\\"", "\"", $searchstring);
$parser = new search_parser();
$lexer = new search_lexer($parser);
if ($lexer->parse($searchstring)) {
if ($parsearray = $parser->get_parsed_array()) {
list($messagesearch, $msparams) = mediagallery_generate_search_sql($parsearray);
$params = array_merge($params, $msparams);
}
}
$fromsql = "{mediagallery_item} i,\n {mediagallery_gallery} g,\n {user} u";
$selectsql = " {$messagesearch}\n AND i.galleryid = g.id\n AND g.userid = u.id\n AND {$selectgallery}\n {$extrasql}";
$countsql = "SELECT COUNT(*)\n FROM {$fromsql}\n WHERE {$selectsql}";
$searchsql = "SELECT i.*, g.name as galleryname, g.instanceid, u.firstname, u.lastname, u.email, u.picture, u.imagealt\n FROM {$fromsql}\n WHERE {$selectsql}\n ORDER BY i.id DESC";
$totalcount = $DB->count_records_sql($countsql, $params);
$records = $DB->get_records_sql($searchsql, $params, $limitfrom, $limitnum);
return array($records, $totalcount);
}
function metadatadc_search_metadatadc($searchterms, $courseid, $page = 0, $recordsperpage = 50, &$totalcount, $sepgroups = 0, $extrasql = '')
{
/// Returns a list of posts found using an array of search terms
/// eg word +word -word
///
global $CFG, $USER;
require_once $CFG->libdir . '/searchlib.php';
/* if (!isteacher($courseid)) {
$notteacherforum = "AND f.type <> 'teacher'";
$forummodule = get_record("modules", "name", "forum");
$onlyvisible = "AND d.forum = f.id AND f.id = cm.instance AND cm.visible = 1 AND cm.module = $forummodule->id";
$onlyvisibletable = ", {$CFG->prefix}course_modules cm, {$CFG->prefix}forum f";
if (!empty($sepgroups)) {
$separategroups = SEPARATEGROUPS;
$selectgroup = " AND ( NOT (cm.groupmode='$separategroups'".
" OR (c.groupmode='$separategroups' AND c.groupmodeforce='1') )";//.
$selectgroup .= " OR d.groupid = '-1'"; //search inside discussions for all groups too
foreach ($sepgroups as $sepgroup){
$selectgroup .= " OR d.groupid = '$sepgroup->id'";
}
$selectgroup .= ")";
// " OR d.groupid = '$groupid')";
$selectcourse = " AND d.course = '$courseid' AND c.id='$courseid'";
$coursetable = ", {$CFG->prefix}course c";
} else {
$selectgroup = '';
$selectcourse = " AND d.course = '$courseid'";
$coursetable = '';
}
} else {
$notteacherforum = "";
$selectgroup = '';
$onlyvisible = "";
$onlyvisibletable = "";
$coursetable = '';
if ($courseid == SITEID && isadmin()) {
$selectcourse = '';
} else {
$selectcourse = " AND d.course = '$courseid'";
}
}
$timelimit = '';
if (!empty($CFG->forum_enabletimedposts) && (!((isadmin() and !empty($CFG->admineditalways)) || isteacher($courseid)))) {
$now = time();
$timelimit = " AND (d.userid = $USER->id OR ((d.timestart = 0 OR d.timestart <= $now) AND (d.timeend = 0 OR d.timeend > $now)))";
}
*/
$limit = sql_paging_limit($page, $recordsperpage);
/// Some differences in syntax for PostgreSQL
if ($CFG->dbtype == "postgres7") {
$LIKE = "ILIKE";
// case-insensitive
$NOTLIKE = "NOT ILIKE";
// case-insensitive
$REGEXP = "~*";
$NOTREGEXP = "!~*";
} else {
$LIKE = "LIKE";
$NOTLIKE = "NOT LIKE";
$REGEXP = "REGEXP";
$NOTREGEXP = "NOT REGEXP";
}
$messagesearch = "";
$searchstring = "";
// Need to concat these back together for parser to work.
foreach ($searchterms as $searchterm) {
if ($searchstring != "") {
$searchstring .= " ";
}
$searchstring .= $searchterm;
}
// We need to allow quoted strings for the search. The quotes *should* be stripped
// by the parser, but this should be examined carefully for security implications.
$searchstring = str_replace("\\\"", "\"", $searchstring);
$parser = new search_parser();
$lexer = new search_lexer($parser);
if ($lexer->parse($searchstring)) {
$parsearray = $parser->get_parsed_array();
$messagesearch = search_generate_SQL($parsearray, 'p.message', 'p.subject', 'p.userid', 'u.id', 'u.firstname', 'u.lastname', 'p.modified', 'd.metadatadc');
}
$selectsql = "{$CFG->prefix}metadatadc d,\r\n {$CFG->prefix}user u {$onlyvisibletable} {$coursetable}\r\n WHERE ({$messagesearch})\r\n AND p.userid = u.id\r\n AND p.discussion = d.id {$selectcourse} {$notteacherforum} {$onlyvisible} {$selectgroup} {$timelimit} {$extrasql}";
$totalcount = count_records_sql("SELECT COUNT(*) FROM {$selectsql}");
return get_records_sql("SELECT p.*,d.forum, u.firstname,u.lastname,u.email,u.picture FROM\r\n {$selectsql} ORDER BY p.modified DESC {$limit}");
}
