/** * Queries for draft posts, including necessary joins with other fields. * @param string $where Text of WHERE clause e.g. 'fdr.id=14'. May refer * to aliases fdr (drafts), fd (discussions), fp (posts; post being * replied to), fpfirst (first post in discussion), and u (user being * replied to) * @return array Array of mod_forumng_draft objects (empty if none) */ public static function query_drafts($where, $whereparams) { global $DB; $result = array(); $rs = $DB->get_recordset_sql("\nSELECT\n fdr.*, fd.id AS discussionid, fpfirst.subject AS discussionsubject,\n f.course AS courseid,\n " . mod_forumng_utils::select_username_fields('u', false) . "\nFROM\n {forumng_drafts} fdr\n LEFT JOIN {forumng_posts} fp ON fdr.parentpostid = fp.id\n LEFT JOIN {forumng_discussions} fd ON fp.discussionid = fd.id\n LEFT JOIN {forumng_posts} fpfirst ON fd.postid = fpfirst.id\n LEFT JOIN {user} u ON fp.userid = u.id\n INNER JOIN {forumng} f ON fdr.forumngid = f.id\nWHERE\n {$where}\nORDER BY\n fdr.saved DESC", $whereparams); foreach ($rs as $rec) { $result[] = new mod_forumng_draft($rec); } $rs->close(); return $result; }
/** * Creates the mail queue and runs query to obtain list of posts that should * be mailed. * @param bool $tracetimes True if it should call mtrace to display * performance information */ function __construct($tracetimes) { global $DB, $CFG; $this->time = time(); $this->forum = null; $this->discussion = null; $this->storedrecord = null; $this->postcount = 0; // Check if an earlier run got aborted. In that case we mark all // messages as mailed anyway because it's better to skip some than // to send out double-posts. if ($pending = get_config('forumng', $this->get_pending_flag_name())) { $this->mark_mailed($pending); } // Note that we are mid-run set_config($this->get_pending_flag_name(), $this->time, 'forumng'); list($wheresql, $whereparams) = $this->get_query_where($this->time); $querychunk = $this->get_query_from() . $wheresql; $this->rs = $DB->get_recordset_sql($sql = "\nSELECT\n " . mod_forumng_utils::select_mod_forumng_fields('f') . ",\n " . mod_forumng_utils::select_discussion_fields('fd') . ",\n " . mod_forumng_utils::select_post_fields('discussionpost') . ",\n " . mod_forumng_utils::select_post_fields('fp') . ",\n " . mod_forumng_utils::select_post_fields('reply') . ",\n " . mod_forumng_utils::select_course_module_fields('cm') . ",\n " . mod_forumng_utils::select_context_fields('x') . ",\n " . mod_forumng_utils::select_username_fields('u', true) . ",\n " . mod_forumng_utils::select_username_fields('eu') . ",\n " . mod_forumng_utils::select_username_fields('replyu') . ",\n " . mod_forumng_utils::select_username_fields('replyeu') . ",\n " . mod_forumng_utils::select_course_fields('c') . ",\n clonecm.id AS cloneid\n{$querychunk}\nORDER BY\n clonecm.course, f.id, fd.id, fp.id", $whereparams); if (!empty($CFG->forumng_cronultradebug)) { $easyread = mod_forumng_utils::debug_query_for_reading($sql, $whereparams); mtrace("\n\n" . $easyread . "\n\n"); } }
/** * Obtains a list of everybody who has read this discussion (only works * if the discussion is within the 'read' period). The list is in date order * (most recent first). Each returned item has ->time (time last read) and * ->user (Moodle user object) fields. * @param int $groupid Group ID or mod_forumng::ALL_GROUPS * @return array Array of information about readers * @throws coding_exception If you try to call it in a shared forum (not supported) */ public function get_readers($groupid = mod_forumng::ALL_GROUPS) { global $DB; if ($this->get_forum()->is_shared()) { throw new coding_exception('get_readers not supported in shared forums'); } list($sql, $params) = get_enrolled_sql($this->get_forum()->get_context(), '', $groupid ? $groupid : 0, true); $now = round(time(), -2); $params['discussionid'] = $this->discussionfields->id; $result = $DB->get_records_sql($sql = "\nSELECT\n fr.id,\n " . mod_forumng_utils::select_username_fields('u', false) . ",\n fr.time,\n u.idnumber AS u_idnumber\nFROM\n {forumng_read} fr\n INNER JOIN {user} u ON u.id = fr.userid\nWHERE\n fr.userid IN ({$sql})\n AND fr.discussionid = :discussionid\nORDER BY\n fr.time DESC", $params); foreach ($result as $item) { $item->user = mod_forumng_utils::extract_subobject($item, 'u_'); } return $result; }
/** * Obtains list of forum subscribers. * @param int $groupid If specified, restricts list to this group id * @return array Array of partial user objects (with enough info to send * email and display them); additionally, if the forum is in group mode, * this includes an ->accessallgroups boolean */ public function get_subscribers($groupid = self::ALL_GROUPS) { global $DB; // Array that will contain result $users = array(); // Get permitted groups $groups = $this->get_permitted_groups(); $subscriptionoption = $this->get_effective_subscription_option(); switch ($subscriptionoption) { case self::SUBSCRIPTION_NOT_PERMITTED: return array(); case self::SUBSCRIPTION_FORCED: case self::SUBSCRIPTION_INITIALLY_SUBSCRIBED: $users = $this->get_auto_subscribers($groupid); // Add $wholeforum = 1 and an empty array() for discussionid // for people who initially subscribed. foreach ($users as $user) { $user->wholeforum = true; $user->discussionids = array(); $user->groupids = array(); } break; default: // The other two cases (initial subscribe, and manual subscribe) // fall through to the standard code below. } $context = $this->get_context(); // For shared forums, we only return the subscribers for the current // clone $clonecheck = ""; if ($this->is_shared()) { $clonecheck = 'AND s.clonecmid = ' . $this->get_course_module_id(); } // Obtain the list of users who have access all groups on the forum, // unless it's in no-groups mode $groupmode = $this->get_group_mode(); if ($groupmode) { // Get a list of user who can access all groups. $aagusers = get_users_by_capability($context, 'moodle/site:accessallgroups', 'u.id'); mod_forumng_utils::add_admin_users($aagusers); } // Get the list of subscribed users. if ($groupid == self::ALL_GROUPS || $groupid == self::NO_GROUPS) { $groupcheck = ''; $groupparams = array(); } else { $groupcheck = "INNER JOIN {groups_members} gm ON gm.userid = u.id AND gm.groupid = ?"; $groupparams = array($groupid); } $rs = $DB->get_recordset_sql($sql = "\nSELECT\n " . mod_forumng_utils::select_username_fields('u', true) . ",\n s.subscribed, s.discussionid, s.groupid, fd.groupid AS discussiongroupid,\n discussiongm.id AS discussiongroupmember, subscriptiongm.id AS subscriptiongroupmember\nFROM\n {forumng_subscriptions} s\n INNER JOIN {user} u ON u.id = s.userid\n {$groupcheck}\n LEFT JOIN {forumng_discussions} fd ON fd.id = s.discussionid\n LEFT JOIN {groups_members} discussiongm ON fd.groupid = discussiongm.groupid\n AND s.userid = discussiongm.userid\n LEFT JOIN {groups_members} subscriptiongm ON s.groupid = subscriptiongm.groupid\n AND s.userid = subscriptiongm.userid\nWHERE\n s.forumngid = ?\n AND (fd.forumngid = ? OR s.discussionid IS NULL)\n {$clonecheck}", array_merge($groupparams, array($this->forumfields->id, $this->forumfields->id))); // Filter the result against the list of allowed users $allowedusers = null; foreach ($rs as $rec) { // Subscribed to the whole forum when subscribed == 1 and disucssionid ==''; // *** Put the allowedusers checks in same part of code so not duplicated if ($rec->subscribed) { // This is a 'subscribe' request if (!$allowedusers) { // Obtain the list of users who are allowed to see the forum. // As get_users_by_capability can be expensive, we only do this // once we know there actually are subscribers (and force rasing memory). raise_memory_limit(MEMORY_EXTRA); $allowedusers = get_users_by_capability($context, 'mod/forumng:viewdiscussion', 'u.id', '', '', '', $groups, '', 0, 0, true); // Filter possible users by activity availability. $avail = new \core_availability\info_module($this->get_course_module()); $allowedusers = $avail->filter_user_list($allowedusers); mod_forumng_utils::add_admin_users($allowedusers); } // Get reference to current user, or make new object if required if (!array_key_exists($rec->u_id, $users)) { $user = mod_forumng_utils::extract_subobject($rec, 'u_'); $user->wholeforum = false; $user->discussionids = array(); $user->groupids = array(); $newuser = true; } else { $user = $users[$rec->u_id]; $newuser = false; } $ok = false; // Subscribed to a discussion. if ($rec->discussionid) { $groupok = !$rec->discussiongroupid || $rec->discussiongroupmember || $groupmode == VISIBLEGROUPS || array_key_exists($user->id, $aagusers); if (array_key_exists($user->id, $allowedusers) && $groupok) { $ok = true; $user->discussionids[$rec->discussionid] = $rec->discussiongroupid; } // Subscribed to a group. } else { if ($rec->groupid) { $groupok = $groupmode == VISIBLEGROUPS || $groupmode == SEPARATEGROUPS && ($rec->subscriptiongroupmember || array_key_exists($user->id, $aagusers)); if (array_key_exists($user->id, $allowedusers) && $groupok) { $user->groupids[$rec->groupid] = $rec->groupid; $ok = true; } // Subscribed to the whole forum. } else { // extra conditions for forum not separate groups or accessallgroups $groupok = $groupmode != SEPARATEGROUPS || array_key_exists($user->id, $aagusers); if (array_key_exists($user->id, $allowedusers) && $groupok) { $user->wholeforum = true; $ok = true; } } } // If this is a new user object, add it to the array provided the row was valid if ($newuser && $ok) { $users[$user->id] = $user; } } else { // This is an 'unsubscribe' request. These are only allowed // for initial-subscription, otherwise ignored if ($subscriptionoption == self::SUBSCRIPTION_INITIALLY_SUBSCRIBED && array_key_exists($user->id, $users)) { // set wholeforum = false for user (if they are in the array) $users[$rec->u_id]->unsubscribe = true; $users[$rec->u_id]->wholeforum = false; } } } $rs->close(); $allowedusers = null; // 1. loop through array and clear the discussions/groupids array if wholeforum is true. // 2. Find any user unsubscribed from initial subscribed forum. If the user has been // subscribed to discussions/groups, remove the $user->unsubscribe flag; // Otherwise remove the user from the list. foreach ($users as $key => $user) { if ($user->wholeforum) { $user->discussionids = array(); $user->groupids = array(); } // Remove discussionids for discussions that are already covered by group subscriptions // TODO if (count($user->discussionids) != 0 && count($user->groupids) != 0) { foreach ($user->discussionids as $id => $dgroupid) { if (!$dgroupid || array_key_exists($dgroupid, $user->groupids)) { unset($user->discussionids[$id]); } } } // If the user has unsubscribed from an initial subscription, then remove the entry // from the results array unless there are s subscriptions to discussions or groups if (!empty($user->unsubscribe)) { // Remove the unsubscribe as the user is likely to // subscribed to discussions or groups unset($user->unsubscribe); if (count($user->discussionids) == 0 && count($user->groupids) == 0) { unset($users[$key]); } } } // Add access-all-groups information if applicable if ($groupmode) { foreach ($users as $key => $user) { $user->accessallgroups = array_key_exists($user->id, $aagusers); } } return $users; }
/** * Internal function. Queries for posts. * @param string $where Where clause (fp is alias for post table) * @param array $whereparams Parameters (values for ? parameters) in where clause * @param string $order Sort order; the default is fp.id - note this is preferable * to fp.timecreated because it works correctly if there are two posts in * the same second * @param bool $ratings True if ratings should be included in the query * @param bool $flags True if flags should be included in the query * @param bool $effectivesubjects True if the query should include the * (complicated!) logic to obtain the 'effective subject'. This may result * in additional queries afterward for posts which are very deeply nested. * @param int $userid 0 = current user (at present this is only used for * flags) * @param bool $read True if read post record (time) is sought * @return array Resulting posts as array of Moodle records, empty array * if none */ public static function query_posts($where, $whereparams, $order = 'fp.id', $ratings = true, $flags = false, $effectivesubjects = false, $userid = 0, $joindiscussion = false, $discussionsubject = false, $limitfrom = '', $limitnum = '', $read = false) { global $DB, $USER; $userid = mod_forumng_utils::get_real_userid($userid); $queryparams = array(); // We include ratings if these are enabled, otherwise save the database // some effort and don't bother if ($ratings) { $ratingsquery = ",\n(SELECT AVG(rating) FROM {forumng_ratings}\n WHERE postid = fp.id) AS averagerating,\n(SELECT COUNT(1) FROM {forumng_ratings}\n WHERE postid = fp.id) AS numratings,\n(SELECT rating FROM {forumng_ratings}\n WHERE postid = fp.id AND userid = ?) AS ownrating"; // Add parameter to start of params list $queryparams[] = $USER->id; } else { $ratingsquery = ''; } if ($flags) { $flagsjoin = "\n LEFT JOIN {forumng_flags} ff ON ff.postid = fp.id AND ff.userid = ?"; $flagsquery = ", ff.flagged"; $queryparams[] = $userid; } else { $flagsjoin = ''; $flagsquery = ''; } if ($joindiscussion) { $discussionjoin = "\n INNER JOIN {forumng_discussions} fd ON fp.discussionid = fd.id"; $discussionquery = ',' . mod_forumng_utils::select_discussion_fields('fd'); if ($discussionsubject) { $discussionjoin .= "\n INNER JOIN {forumng_posts} fdfp ON fd.postid = fdfp.id"; $discussionquery .= ', fdfp.subject AS fd_subject'; } } else { $discussionjoin = ''; $discussionquery = ''; } if ($effectivesubjects) { $maxdepth = self::PARENTPOST_DEPTH_PER_QUERY; $subjectsjoin = ''; $subjectsquery = ", p{$maxdepth}.parentpostid AS nextparent "; for ($depth = 2; $depth <= $maxdepth; $depth++) { $subjectsquery .= ", p{$depth}.subject AS s{$depth}, p{$depth}.deleted AS d{$depth}"; $prev = 'p' . ($depth - 1); if ($prev == 'p1') { $prev = 'fp'; } $subjectsjoin .= "LEFT JOIN {forumng_posts} p{$depth}\n ON p{$depth}.id = {$prev}.parentpostid "; } } else { $subjectsjoin = ''; $subjectsquery = ''; } if ($read) { $readquery = ', fr.time AS uread'; $readjoin = "LEFT JOIN {forumng_read_posts} fr ON fr.postid = fp.id AND fr.userid = ?"; $queryparams[] = $userid; } else { $readquery = ''; $readjoin = ''; } // Retrieve posts from discussion with incorporated user information // and ratings info if specified $results = $DB->get_records_sql("\nSELECT\n fp.*,\n " . mod_forumng_utils::select_username_fields('u', true) . ",\n " . mod_forumng_utils::select_username_fields('eu') . ",\n " . mod_forumng_utils::select_username_fields('du') . "\n {$ratingsquery}\n {$flagsquery}\n {$subjectsquery}\n {$discussionquery}\n {$readquery}\nFROM\n {forumng_posts} fp\n INNER JOIN {user} u ON fp.userid = u.id\n LEFT JOIN {user} eu ON fp.edituserid = eu.id\n LEFT JOIN {user} du ON fp.deleteuserid = du.id\n {$discussionjoin}\n {$flagsjoin}\n {$subjectsjoin}\n {$readjoin}\nWHERE\n {$where}\nORDER BY\n {$order}\n", array_merge($queryparams, $whereparams), $limitfrom, $limitnum); if ($effectivesubjects) { // Figure out the effective subject for each result foreach ($results as $result) { $got = false; if ($result->subject !== null) { $result->effectivesubject = $result->subject; $got = true; continue; } for ($depth = 2; $depth <= $maxdepth; $depth++) { $var = "s{$depth}"; $var2 = "d{$depth}"; if (!$got && $result->{$var} !== null && $result->{$var2} == 0) { $result->effectivesubject = get_string('re', 'forumng', $result->{$var}); $got = true; } unset($result->{$var}); unset($result->{$var2}); } if (!$got) { // Do extra queries to pick up subjects for posts where it // was unknown within the default depth. We can use the // 'nextparent' to get the ID of the parent post of the last // one that we checked already $result->effectivesubject = self::inner_get_recursive_subject($result->nextparent); } } } return $results; }