public static function render_instance(BlockInstance $instance, $editing = false)
 {
     global $USER;
     $configdata = $instance->get('configdata');
     $limit = isset($configdata['count']) ? (int) $configdata['count'] : 10;
     $userid = $USER->get('id');
     $smarty = smarty_core();
     $sql = '
         SELECT v.id, v.title, v.owner, v.group, v.institution, v.ownerformat, v.urlid, v.ctime, v.mtime
         FROM {view} v
         JOIN {usr_watchlist_view} wv ON wv.view = v.id
         WHERE wv.usr = ?
         ORDER BY v.title
         LIMIT ?';
     $results = get_records_sql_assoc($sql, array($userid, $limit));
     // if there are no watched views, notify the user
     if (!$results) {
         $smarty->assign('watchlistempty', true);
         return $smarty->fetch('blocktype:watchlist:watchlist.tpl');
     }
     View::get_extra_view_info($results, false, false);
     foreach ($results as &$r) {
         $r = (object) $r;
     }
     $smarty->assign('loggedin', $USER->is_logged_in());
     $smarty->assign('views', array_values($results));
     return $smarty->fetch('blocktype:watchlist:watchlist.tpl');
 }

 function findByWwwroot($wwwroot)
 {
     $len = strlen($wwwroot);
     if (!is_string($wwwroot) || $len < 1 || $len > 255) {
         throw new ParamOutOfRangeException('WWWROOT: ' . addslashes($wwwroot) . ' is out of range');
     }
     $sql = 'SELECT
                 h2.*
             FROM
                 {host} h1,
                 {host} h2
             WHERE
                 h1.institution = h2.institution AND
                 h1.wwwroot = ?
             ORDER BY
                 h2.wwwroot';
     $this->resultset = get_records_sql_assoc($sql, array('wwwroot' => $wwwroot));
     if (false == $this->resultset) {
         throw new ParamOutOfRangeException('Unknown wwwroot: ' . addslashes($wwwroot));
     }
 }

function groupquotasform_submit(Pieform $form, $values)
{
    global $SESSION;
    $oldquota = get_field('group', 'quota', 'id', $values['groupid']);
    $group = new StdClass();
    $group->id = $values['groupid'];
    $group->quota = $values['quota'];
    update_record('group', $group);
    if (!empty($values['quota']) && $values['quota'] != $oldquota) {
        // We need to alert group admins that the group may now be over the threshold
        $quotanotifylimit = get_config_plugin('artefact', 'file', 'quotanotifylimit');
        $sqlwhere = " ((g.quotaused / g.quota) * 100) ";
        if (is_postgres()) {
            $sqlwhere = " ((CAST(g.quotaused AS float) / CAST(g.quota AS float)) * 100) ";
        }
        if ($groups = get_records_sql_assoc("SELECT g.id, g.name, g.quota, " . $sqlwhere . " AS quotausedpercent FROM {group} g WHERE " . $sqlwhere . " >= ? AND id = ?", array($quotanotifylimit, $values['groupid']))) {
            require_once get_config('docroot') . 'artefact/file/lib.php';
            ArtefactTypeFile::notify_groups_threshold_exceeded($groups);
        }
    }
    $SESSION->add_ok_msg(get_string('groupquotaupdated', 'admin'));
    redirect(get_config('wwwroot') . 'admin/groups/groups.php');
}

 /**
  * synchronize Mahara's groups with groups defined on a LDAP server
  *
  * @param boolean $dryrun dummy execution. Do not perform any database operations
  * @return boolean
  */
 function sync_groups($dryrun = false)
 {
     global $USER;
     log_info('---------- started groupsync auth instance ' . $this->instanceid . ' at ' . date('r', time()) . ' ----------');
     if (!$this->get_config('syncgroupscron')) {
         log_info('Not set to sync groups, so exiting');
         return true;
     }
     // We need to tell the session that we are the admin user, so that we have permission to manipulate groups
     $USER->reanimate(1, 1);
     $syncbyattribute = $this->get_config('syncgroupsbyuserfield') && $this->get_config('syncgroupsgroupattribute');
     $syncbyclass = $this->get_config('syncgroupsbyclass') && $this->get_config('syncgroupsgroupclass') && $this->get_config('syncgroupsgroupattribute') && $this->get_config('syncgroupsmemberattribute');
     $excludelist = $this->get_config('syncgroupsexcludelist');
     $includelist = $this->get_config('syncgroupsincludelist');
     $searchsub = $this->get_config('syncgroupssearchsub');
     $grouptype = $this->get_config('syncgroupsgrouptype');
     $groupattribute = $this->get_config('syncgroupsgroupattribute');
     $docreate = $this->get_config('syncgroupsautocreate');
     // If neither one is set, return
     if (!$syncbyattribute && !$syncbyclass) {
         log_info('not set to sync by user attribute or by group objects, so exiting');
         return true;
     }
     if (get_config('auth_ldap_debug_sync_cron')) {
         log_debug("exclusion list : ");
         var_dump($excludelist);
         log_debug("inclusion list : ");
         var_dump($includelist);
     }
     // fetch userids of current members of that institution
     if ($this->institution == 'mahara') {
         $currentmembers = get_records_sql_assoc('select u.username as username, u.id as id from {usr} u where u.deleted=0 and not exists (select 1 from {usr_institution} ui where ui.usr=u.id)', array());
     } else {
         $currentmembers = get_records_sql_assoc('select u.username as username, u.id as id from {usr} u inner join {usr_institution} ui on u.id=ui.usr where u.deleted=0 and ui.institution=?', array($this->institution));
     }
     if (get_config('auth_ldap_debug_sync_cron')) {
         log_debug("current members : " . count($currentmembers));
         var_dump($currentmembers);
     }
     if (get_config('auth_ldap_debug_sync_cron')) {
         log_debug("config. LDAP : ");
         var_dump($this->get_config());
     }
     $groups = array();
     if ($syncbyattribute) {
         // get the distinct values of the used attribute by a LDAP search
         // that may be restricted by flags -c or -o
         $groups = array_merge($groups, $this->get_attribute_distinct_values($searchsub));
     }
     if ($syncbyclass) {
         $groups = array_merge($groups, $this->ldap_get_grouplist('*', $searchsub));
     }
     if (get_config('auth_ldap_debug_sync_cron')) {
         log_debug("Found LDAP groups  : ");
         var_dump($groups);
     }
     $nbadded = 0;
     foreach ($groups as $group) {
         $nomatch = false;
         log_debug("Processing group '{$group}'");
         if (!ldap_sync_filter_name($group, $includelist, $excludelist)) {
             continue;
         }
         if (get_config('auth_ldap_debug_sync_cron')) {
             log_debug("processing group  : ");
             var_dump($group);
         }
         $ldapusers = array();
         if ($syncbyattribute) {
             $ldapusers = array_merge($ldapusers, $this->get_users_having_attribute_value($group));
         }
         if ($syncbyclass) {
             $ldapusers = array_merge($ldapusers, $this->ldap_get_group_members($group));
         }
         // test whether this group exists within the institution
         // group.shortname is limited to 255 characters. Unlikely anyone will hit this, but why not?
         $shortname = substr($group, 0, 255);
         if (!($dbgroup = get_record('group', 'shortname', $shortname, 'institution', $this->institution))) {
             if (!$docreate) {
                 log_debug('autocreation is off so skipping Mahara not existing group ' . $group);
                 continue;
             }
             if (count($ldapusers) == 0) {
                 log_debug('will not autocreate an empty Mahara group ' . $group);
                 continue;
             }
             try {
                 log_info('creating group ' . $group);
                 // Make sure the name is unique (across all institutions)
                 // group.name only allows 128 characters. In the event of
                 // really long group names, we'll arbitrarily truncate them
                 $basename = $this->institution . ' : ' . $group;
                 $name = substr($basename, 0, 128);
                 $n = 0;
//.........这里部分代码省略.........

/**
 * get the views that a user can see belonging
 * to the given users
 *
 * @param array $users users to fetch views owned by
 * @param int $userlooking (optional, defaults to logged in user)
 * @param int $limit grab this many views. (setting this null means get all)
 *
 * @return array Associative array keyed by userid, of arrays of view ids
 */
function get_views($users, $userlooking = null, $limit = 5, $type = null)
{
    $userlooking = optional_userid($userlooking);
    if (is_int($users)) {
        $users = array($users);
    }
    $list = array();
    if (count($users) == 0) {
        return $list;
    }
    $users = array_flip($users);
    $dbnow = db_format_timestamp(time());
    if ($friends = get_records_sql_array('SELECT
            CASE WHEN usr1=? THEN usr2 ELSE usr1 END AS id
        FROM
            {usr_friend} f
        WHERE
            ( usr1=? AND usr2 IN (' . join(',', array_map('db_quote', array_keys($users))) . ') )
            OR
            ( usr2=? AND usr1 IN (' . join(',', array_map('db_quote', array_keys($users))) . ') )
        ', array($userlooking, $userlooking, $userlooking))) {
        foreach ($friends as $user_id) {
            $users[$user_id->id] = 'friend';
        }
    }
    if (is_null($type)) {
        $typesql = "AND v.type != 'profile'";
    } else {
        $typesql = 'AND v.type = ' . db_quote($type);
    }
    $data = array();
    $done = false;
    // public, logged in, or friends' views
    if ($results = get_records_sql_assoc('SELECT
            v.*,
            ' . db_format_tsfield('atime') . ',
            ' . db_format_tsfield('mtime') . ',
            ' . db_format_tsfield('v.ctime', 'ctime') . '
        FROM
            {view} v
            INNER JOIN {view_access} a ON
                v.id=a.view
                AND (
                    accesstype IN (\'public\',\'loggedin\')
            ' . (count(preg_grep('/^friend$/', $users)) > 0 ? 'OR (
                            accesstype = \'friends\'
                            AND v.owner IN (' . join(',', array_map('db_quote', array_keys(preg_grep('/^friend$/', $users)))) . ')
                        )' : '') . '
                )
        WHERE
            v.owner IN (' . join(',', array_map('db_quote', array_keys($users))) . ')
            AND ( v.startdate IS NULL OR v.startdate < ? )
            AND ( v.stopdate IS NULL OR v.stopdate > ? )
        ' . $typesql, array($dbnow, $dbnow))) {
        foreach ($results as $row) {
            $list[$row->owner][$row->id] = $row->id;
        }
        $data = $results;
        // bail if we've filled all users to the limit
        $done = _get_views_trim_list($list, $users, $limit, $data);
    }
    // check individual user access
    if (!$done && ($results = get_records_sql_assoc('SELECT
            v.*,
            ' . db_format_tsfield('atime') . ',
            ' . db_format_tsfield('mtime') . ',
            ' . db_format_tsfield('v.ctime', 'ctime') . '
        FROM
            {view} v
            INNER JOIN {view_access} a ON v.id=a.view AND a.usr=?
        WHERE
            v.owner IN (' . join(',', array_map('db_quote', array_keys($users))) . ')
            AND ( v.startdate IS NULL OR v.startdate < ? )
            AND ( v.stopdate IS NULL OR v.stopdate > ? )
        ' . $typesql, array($userlooking, $dbnow, $dbnow)))) {
        foreach ($results as &$row) {
            $list[$row->owner][$row->id] = $row->id;
        }
        $data = array_merge($data, $results);
        // bail if we've filled all users to the limit
        $done = $done && _get_views_trim_list($list, $users, $limit, $data);
    }
    // check group access
    if (!$done && ($results = get_records_sql_assoc('SELECT
            v.*,
            ' . db_format_tsfield('v.atime', 'atime') . ',
            ' . db_format_tsfield('v.mtime', 'mtime') . ',
            ' . db_format_tsfield('v.ctime', 'ctime') . '
        FROM
            {view} v
//.........这里部分代码省略.........

//.........这里部分代码省略.........
        $table->addKeyInfo('primary', XMLDB_KEY_PRIMARY, array('id'));
        $table->addKeyInfo('groupfk', XMLDB_KEY_FOREIGN, array('group'), 'group', array('id'));
        $table->addKeyInfo('archivefk', XMLDB_KEY_FOREIGN, array('archiveid'), 'export_archive', array('id'));
        create_table($table);
        // install the cronjob to process export queue
        $cron = new StdClass();
        $cron->callfunction = 'export_process_queue';
        $cron->minute = '*/6';
        $cron->hour = '*';
        $cron->day = '*';
        $cron->month = '*';
        $cron->dayofweek = '*';
        ensure_record_exists('cron', $cron, $cron);
        // install the cronjob to clean up deleted archived submissions items
        $cron = new StdClass();
        $cron->callfunction = 'submissions_delete_removed_archive';
        $cron->minute = '15';
        $cron->hour = '1';
        $cron->day = '1';
        $cron->month = '*';
        $cron->dayofweek = '*';
        ensure_record_exists('cron', $cron, $cron);
    }
    if ($oldversion < 2014092300) {
        log_debug('Add the socialprofile artefacttype');
        // Need to insert directly into the table instead of running upgrade_plugin(), so that we can transition
        // all the old social network artefact types into the new unified socialprofile type before deleting
        // the old types from artefact_installed_type
        insert_record('artefact_installed_type', (object) array('name' => 'socialprofile', 'plugin' => 'internal'));
        // Convert existing messaging types to socialprofile types.
        $oldmessagingfieldsarray = array('icqnumber', 'msnnumber', 'aimscreenname', 'yahoochat', 'skypeusername', 'jabberusername');
        $oldmessagingfields = implode(',', array_map('db_quote', $oldmessagingfieldsarray));
        $sql = "SELECT * FROM {artefact}\n                WHERE artefacttype IN (" . $oldmessagingfields . ")";
        if ($results = get_records_sql_assoc($sql, array())) {
            $count = 0;
            $limit = 1000;
            $total = count($results);
            safe_require('artefact', 'internal');
            foreach ($results as $result) {
                $i = new ArtefactTypeSocialprofile($result->id, (array) $result);
                $i->set('artefacttype', 'socialprofile');
                switch ($result->artefacttype) {
                    case 'aimscreenname':
                        $i->set('note', 'aim');
                        $i->set('description', get_string('aim', 'artefact.internal'));
                        break;
                    case 'icqnumber':
                        $i->set('note', 'icq');
                        $i->set('description', get_string('icq', 'artefact.internal'));
                        break;
                    case 'jabberusername':
                        $i->set('note', 'jabber');
                        $i->set('description', get_string('jabber', 'artefact.internal'));
                        break;
                    case 'msnnumber':
                    case 'skypeusername':
                        // MSN no longer exists and has been replaced by Skype.
                        $i->set('note', 'skype');
                        $i->set('description', get_string('skype', 'artefact.internal'));
                        break;
                    case 'yahoochat':
                        $i->set('note', 'yahoo');
                        $i->set('description', get_string('yahoo', 'artefact.internal'));
                        break;
                }
                $i->set('title', $result->title);

        $type = 'all';
    }
}
$searchform = pieform(array('name' => 'search', 'method' => 'post', 'renderer' => 'oneline', 'elements' => array('query' => array('type' => 'text', 'defaultvalue' => $query), 'filter' => array('type' => 'select', 'options' => array('notmember' => get_string('groupsnotin', 'group'), 'member' => get_string('groupsimin', 'group'), 'all' => get_string('allgroups', 'group')), 'defaultvalue' => $filter), 'search' => array('type' => 'submit', 'value' => get_string('search')))));
$groups = search_group($query, $groupsperpage, $offset, $type);
// gets more data about the groups found by search_group
// including type if the user is associated with the group in some way
// and the first three members by id
if ($groups['data']) {
    $groupids = array();
    foreach ($groups['data'] as $group) {
        $groupids[] = $group->id;
    }
    $groups['data'] = get_records_sql_assoc("SELECT g1.id, g1.name, g1.description, g1.jointype, g1.membershiptype, g1.membercount, COUNT(gmr.member) AS requests\n        FROM (\n            SELECT g.id, g.name, g.description, g.jointype, t.membershiptype, COUNT(gm.member) AS membercount\n            FROM {group} g\n            LEFT JOIN {group_member} gm ON (gm.group = g.id)\n            LEFT JOIN (\n                SELECT g.id, 'admin' AS membershiptype\n                FROM {group} g\n                INNER JOIN {group_member} gm ON (gm.group = g.id AND gm.member = ? AND gm.role = 'admin')\n                UNION\n                SELECT g.id, 'member' AS membershiptype\n                FROM {group} g\n                INNER JOIN {group_member} gm ON (g.id = gm.group AND gm.member = ? AND gm.role != 'admin')\n                UNION\n                SELECT g.id, 'invite' AS membershiptype\n                FROM {group} g\n                INNER JOIN {group_member_invite} gmi ON (gmi.group = g.id AND gmi.member = ?)\n                UNION\n                SELECT g.id, 'request' AS membershiptype\n                FROM {group} g\n                INNER JOIN {group_member_request} gmr ON (gmr.group = g.id AND gmr.member = ?)\n            ) t ON t.id = g.id\n            WHERE g.id IN (" . implode($groupids, ',') . ')
            GROUP BY g.id, g.name, g.description, g.jointype, t.membershiptype
            ORDER BY g.name
        ) g1
        LEFT JOIN {group_member_request} gmr ON (gmr.group = g1.id)
        GROUP BY g1.id, g1.name, g1.description, g1.jointype, g1.membershiptype, g1.membercount', array($USER->get('id'), $USER->get('id'), $USER->get('id'), $USER->get('id')));
    if ($groups['data']) {
        // Get 3 members from each group in a separate query -- mysql doesn't like including them as subqueries with limit 1 in the above query
        $members = get_records_sql_array("\n            SELECT m1.group, m1.member, u.* FROM {group_member} m1\n            INNER JOIN {usr} u ON (m1.member = u.id AND u.deleted = 0)\n            WHERE 3 > (\n                SELECT COUNT(m2.member)\n                FROM {group_member} m2\n                WHERE m1.group = m2.group AND m2.member < m1.member\n            )\n            AND m1.group IN (" . implode($groupids, ',') . ')', array());
        if ($members) {
            foreach ($members as $m) {
                $groups['data'][$m->group]->members[] = (object) array('id' => $m->id, 'name' => display_name($m));
            }
        }
    }
    $groups['data'] = array_values($groups['data']);
}
group_prepare_usergroups_for_display($groups['data'], 'find');
$pagination = build_pagination(array('url' => get_config('wwwroot') . 'group/find.php?filter=' . $filter . '&amp;query=' . $query, 'count' => $groups['count'], 'limit' => $groupsperpage, 'offset' => $offset, 'resultcounttextsingular' => get_string('group', 'group'), 'resultcounttextplural' => get_string('groups', 'group')));

 /**
  * Get all views for a (user,group,institution), grouping views
  * into their collections.  Empty collections not returned.
  *
  * @param mixed   $owner integer userid or array of userids
  * @param mixed   $group integer groupid or array of groupids
  * @param mixed   $institution string institution name or array of institution names
  * @param string  $matchconfig record all matches with given config hash (see set_access)
  * @param boolean $includeprofile include profile view
  * @param integer $submittedgroup return only views & collections submitted to this group
  * @param $string $sort Order to sort by (defaults to 'c.name, v.title')
  *
  * @return array, array
  */
 function get_views_and_collections($owner = null, $group = null, $institution = null, $matchconfig = null, $includeprofile = true, $submittedgroup = null, $sort = null)
 {
     $excludelocked = $group && group_user_access($group) != 'admin';
     // Anonymous public viewing of a group with 'Allow submissions' checked needs to avoid including the dummy root profile page.
     if ($owner == '0') {
         $includeprofile = false;
     }
     $sql = "\n            SELECT v.id, v.type, v.title, v.accessconf, v.ownerformat, v.startdate, v.stopdate, v.template,\n                v.owner, v.group, v.institution, v.urlid, v.submittedgroup, v.submittedhost, " . db_format_tsfield('v.submittedtime', 'submittedtime') . ", v.submittedstatus,\n                c.id AS cid, c.name AS cname,\n                c.submittedgroup AS csubmitgroup, c.submittedhost AS csubmithost, " . db_format_tsfield('c.submittedtime', 'csubmittime') . ", c.submittedstatus AS csubmitstatus\n            FROM {view} v\n                LEFT JOIN {collection_view} cv ON v.id = cv.view\n                LEFT JOIN {collection} c ON cv.collection = c.id\n            WHERE  v.type IN ('portfolio'";
     $sql .= $includeprofile ? ", 'profile') " : ') ';
     $sql .= $excludelocked ? 'AND v.locked != 1 ' : '';
     if (is_null($owner) && is_null($group) && is_null($institution)) {
         $values = array();
     } else {
         list($ownersql, $values) = self::multiple_owner_sql((object) array('owner' => $owner, 'group' => $group, 'institution' => $institution));
         $sql .= "AND v.{$ownersql} ";
     }
     if ($submittedgroup) {
         $sql .= 'AND v.submittedgroup = ? ';
         $values[] = (int) $submittedgroup;
     }
     if ($sort == null) {
         $sql .= 'ORDER BY c.name, v.title';
     } else {
         $sql .= "ORDER BY {$sort}";
     }
     $records = get_records_sql_assoc($sql, $values);
     $collections = array();
     $views = array();
     if (!$records) {
         return array($collections, $views);
     }
     self::get_extra_view_info($records, false, false);
     foreach ($records as &$r) {
         $vid = $r['id'];
         $cid = $r['cid'];
         $v = array('id' => $vid, 'type' => $r['type'], 'name' => $r['displaytitle'], 'url' => $r['fullurl'], 'startdate' => $r['startdate'], 'stopdate' => $r['stopdate'], 'template' => $r['template'], 'owner' => $r['owner'], 'submittedgroup' => $r['submittedgroup'], 'submittedhost' => $r['submittedhost'], 'submittedtime' => $r['submittedtime'], 'submittedstatus' => $r['submittedstatus']);
         if (isset($r['user'])) {
             $v['ownername'] = display_name($r['user']);
             $v['ownerurl'] = profile_url($r['user']);
         }
         // If filtering by submitted views, and the view is submitted, but the collection isn't,
         // then ignore the collection and return the view by itself.
         if ($cid && (!$submittedgroup || $r['csubmitgroup'] == $r['submittedgroup'])) {
             if (!isset($collections[$cid])) {
                 $collections[$cid] = array('id' => $cid, 'name' => $r['cname'], 'url' => $r['fullurl'], 'owner' => $r['owner'], 'group' => $r['group'], 'institution' => $r['institution'], 'submittedgroup' => $r['csubmitgroup'], 'submittedhost' => $r['csubmithost'], 'submittedtime' => $r['csubmittime'], 'submittedstatus' => $r['csubmitstatus'], 'template' => $r['template'], 'views' => array());
                 if (isset($r['user'])) {
                     $collections[$cid]['ownername'] = $v['ownername'];
                     $collections[$cid]['ownerurl'] = $v['ownerurl'];
                 }
                 if ($matchconfig && $matchconfig == $r['accessconf']) {
                     $collections[$cid]['match'] = true;
                 }
             }
             $collections[$cid]['views'][$vid] = $v;
         } else {
             $views[$vid] = $v;
             if ($matchconfig && $matchconfig == $r['accessconf']) {
                 $views[$vid]['match'] = true;
             }
         }
     }
     return array($collections, $views);
 }

 public function __construct($data, $cron = false)
 {
     parent::__construct($data, $cron);
     $this->overridemessagecontents = true;
     $post = get_record_sql('
         SELECT
             p.subject, p.body, p.poster, p.parent, ' . db_format_tsfield('p.ctime', 'ctime') . ',
             t.id AS topicid, fp.subject AS topicsubject, f.title AS forumtitle, g.name AS groupname, f.id AS forumid
         FROM {interaction_forum_post} p
         INNER JOIN {interaction_forum_topic} t ON (t.id = p.topic AND t.deleted = 0)
         INNER JOIN {interaction_forum_post} fp ON (fp.parent IS NULL AND fp.topic = t.id)
         INNER JOIN {interaction_instance} f ON (t.forum = f.id AND f.deleted = 0)
         INNER JOIN {group} g ON (f.group = g.id AND g.deleted = 0)
         WHERE p.id = ? AND p.deleted = 0', array($this->postid));
     // The post may have been deleted during the activity delay
     if (!$post) {
         $this->users = array();
         return;
     }
     // A user may be subscribed via the forum or the specific topic. If they're subscribed to both, we want
     // to focus on the topic subscription because it's more specific.
     $sql = '
         SELECT
             subq2.subscriber,
             (CASE WHEN subq2.topickey IS NOT NULL THEN subq2.topickey ELSE subq2.forumkey END) AS "key",
             (CASE WHEN subq2.topickey IS NOT NULL THEN \'topic\' ELSE \'forum\' END) AS "type"
         FROM (
             SELECT subq1.subscriber, max(topickey) AS topickey, max(forumkey) AS forumkey
             FROM (
                 SELECT "user" AS subscriber, "key" AS topickey, NULL AS forumkey FROM {interaction_forum_subscription_topic} WHERE topic = ?
                 UNION ALL
                 SELECT "user" AS subscriber, NULL AS topickey, "key" AS forumkey FROM {interaction_forum_subscription_forum} WHERE forum = ?
             ) subq1
             GROUP BY subq1.subscriber
         ) subq2
         INNER JOIN {usr} u ON subq2.subscriber = u.id
         WHERE u.deleted = 0
     ';
     $params = array($post->topicid, $post->forumid);
     if ($cron) {
         $sql .= ' AND subq2.subscriber > ? ';
         $params[] = (int) $data->last_processed_userid;
         $limitfrom = 0;
         $limitnum = self::USERCHUNK_SIZE;
     } else {
         $limitfrom = '';
         $limitnum = '';
     }
     $sql .= ' ORDER BY subq2.subscriber';
     $subscribers = get_records_sql_assoc($sql, $params, $limitfrom, $limitnum);
     $this->users = $subscribers ? activity_get_users($this->get_id(), array_keys($subscribers)) : array();
     $this->fromuser = $post->poster;
     // When emailing forum posts, create Message-Id headers for threaded display by email clients
     $urlinfo = parse_url(get_config('wwwroot'));
     $hostname = $urlinfo['host'];
     $cleanforumname = clean_email_headers($post->forumtitle);
     $cleangroupname = clean_email_headers($post->groupname);
     $cleanforumname = str_replace('"', "'", strip_tags($cleanforumname));
     $cleangroupname = str_replace('"', "'", strip_tags($cleangroupname));
     $this->customheaders = array('List-Id: "' . $cleanforumname . '" <forum' . $post->forumid . '@' . $hostname . '>', 'List-Help: ' . get_config('wwwroot') . 'interaction/forum/view.php?id=' . $post->forumid, 'Message-ID: <forumpost' . $this->postid . '@' . $hostname . '>', 'X-Mahara-Group: ' . $cleangroupname, 'X-Mahara-Forum: ' . $cleanforumname);
     if ($post->parent) {
         $this->customheaders[] = 'In-Reply-To: <forumpost' . $post->parent . '@' . $hostname . '>';
         $this->customheaders[] = 'References: <forumpost' . $post->parent . '@' . $hostname . '>';
     }
     $post->posttime = strftime(get_string('strftimedaydatetime'), $post->ctime);
     // Some messages are all html and when they're 'cleaned' with
     // strip_tags(str_shorten_html($post->body, 200, true)) for display,
     // they are left empty. Use html2text instead.
     $this->message = str_shorten_text(trim(html2text($post->body)), 200, true);
     // For internal notifications.
     $post->textbody = trim(html2text($post->body));
     $post->htmlbody = clean_html($post->body);
     $this->url = 'interaction/forum/topic.php?id=' . $post->topicid . '&post=' . $this->postid;
     $this->add_urltext(array('key' => 'Topic', 'section' => 'interaction.forum'));
     $this->strings->subject = (object) array('key' => 'newforumpostnotificationsubjectline', 'section' => 'interaction.forum', 'args' => array($post->subject ? $post->subject : get_string('Re:', 'interaction.forum') . ($post->parent ? get_ancestorpostsubject($post->parent, true) : $post->topicsubject)));
     foreach ($this->users as &$user) {
         $user->subscribetype = $subscribers[$user->id]->type;
         $user->unsubscribekey = $subscribers[$user->id]->key;
     }
     $this->temp = (object) array('post' => $post);
 }

    FROM (
        SELECT u.id
        FROM {usr} u
        LEFT OUTER JOIN {usr_institution} ui ON (ui.usr = u.id)
        WHERE ' . ($type == 'expired' ? 'u.expiry < current_timestamp' : 'suspendedcusr IS NOT NULL') . '
        AND deleted = 0 ' . $instsql . '
        GROUP BY u.id
    ) AS a');
$data = get_records_sql_assoc('
    SELECT
        u.id, u.firstname, u.lastname, u.studentid, u.suspendedctime, u.suspendedreason AS reason,
        ua.firstname AS cusrfirstname, ua.lastname AS cusrlastname, ' . db_format_tsfield('u.expiry', 'expiry') . '
    FROM {usr} u
    LEFT JOIN {usr} ua on (ua.id = u.suspendedcusr)
    LEFT OUTER JOIN {usr_institution} ui ON (ui.usr = u.id)
    WHERE ' . ($type == 'expired' ? 'u.expiry < current_timestamp' : 'u.suspendedcusr IS NOT NULL') . '
    AND u.deleted = 0 ' . $instsql . '
    GROUP BY
        u.id, u.firstname, u.lastname, u.studentid, u.suspendedctime, u.suspendedreason,
        ua.firstname, ua.lastname, u.expiry
    ORDER BY ' . ($type == 'expired' ? 'u.expiry' : 'u.suspendedctime') . ', u.id
    LIMIT ?
    OFFSET ?', array($limit, $offset));
if (!$data) {
    $data = array();
} else {
    $institutions = get_records_sql_array('
        SELECT ui.usr, ui.studentid, i.displayname
        FROM {usr_institution} ui INNER JOIN {institution} i ON ui.institution = i.name
        WHERE ui.usr IN (' . join(',', array_keys($data)) . ')', null);
    if ($institutions) {

 public function get_attachments($assoc = false)
 {
     $list = get_records_sql_assoc('SELECT a.id, a.artefacttype, a.title, a.description
         FROM {artefact_attachment} aa
         INNER JOIN {artefact} a ON a.id = aa.attachment
         WHERE aa.artefact = ?
         ORDER BY a.title', array($this->id));
     // load tags
     if ($list) {
         $tags = get_records_select_array('artefact_tag', 'artefact IN (' . join(',', array_keys($list)) . ')');
         if ($tags) {
             foreach ($tags as $t) {
                 $list[$t->artefact]->tags[] = $t->tag;
             }
             foreach ($list as &$attachment) {
                 if (!empty($attachment->tags)) {
                     $attachment->tags = join(', ', $attachment->tags);
                 }
             }
         }
     } else {
         return array();
     }
     if ($assoc) {
         // Remove once tablerenderers are gone.
         return $list;
     }
     return array_values($list);
 }

 public static function get_admin_files($public)
 {
     $pubfolder = ArtefactTypeFolder::admin_public_folder_id();
     $artefacts = get_records_sql_assoc("\n            SELECT\n                a.id, a.title, a.parent, a.artefacttype\n            FROM {artefact} a\n                INNER JOIN {artefact_file_files} f ON f.artefact = a.id\n            WHERE a.institution = 'mahara'", array());
     $files = array();
     if (!empty($artefacts)) {
         foreach ($artefacts as $a) {
             if ($a->artefacttype != 'folder') {
                 $title = $a->title;
                 $parent = $a->parent;
                 while (!empty($parent)) {
                     if ($public && $parent == $pubfolder) {
                         $files[] = array('name' => $title, 'id' => $a->id);
                         continue 2;
                     }
                     $title = $artefacts[$parent]->title . '/' . $title;
                     $parent = $artefacts[$parent]->parent;
                 }
                 if (!$public) {
                     $files[] = array('name' => $title, 'id' => $a->id);
                 }
             }
         }
     }
     return $files;
 }

/**
 * Given a USER, get all Service Providers for that User, based on child auth
 * instances of its canonical auth instance
 */
function get_service_providers($instance)
{
    static $cache = array();
    if (defined('INSTALLER')) {
        return array();
    }
    if (array_key_exists($instance, $cache)) {
        return $cache[$instance];
    }
    $query = "\n        SELECT\n            h.name,\n            a.ssolandurl,\n            h.wwwroot,\n            aic.instance\n        FROM\n            {auth_instance_config} aic,\n            {auth_instance_config} aic2,\n            {auth_instance_config} aic3,\n            {host} h,\n            {application} a\n        WHERE\n          ((aic.value = '1' AND\n            aic.field = 'theyautocreateusers' ) OR\n           (aic.value = ?  AND\n            aic.field = 'parent')) AND\n\n            aic.instance = aic2.instance AND\n            aic2.field = 'wwwroot' AND\n            aic2.value = h.wwwroot AND\n\n            aic.instance = aic3.instance AND\n            aic3.field = 'wessoout' AND\n            aic3.value = '1' AND\n\n            a.name = h.appname";
    try {
        $results = get_records_sql_assoc($query, array('value' => $instance));
    } catch (SQLException $e) {
        // Table doesn't exist yet
        return array();
    }
    if (false == $results) {
        $results = array();
    }
    foreach ($results as $key => $result) {
        $results[$key] = get_object_vars($result);
    }
    $cache[$instance] = $results;
    return $cache[$instance];
}

 private static function get_allowed_mimetype_filetypes()
 {
     if ($data = self::get_allowed_filetypes()) {
         if ($mimetypes = get_records_sql_assoc('
             SELECT mimetype, description
             FROM {artefact_file_mime_types}
             WHERE description IN (' . join(',', array_map('db_quote', $data)) . ')', array())) {
             foreach ($mimetypes as &$m) {
                 $m = $m->description;
             }
             // Hack to allow .wmv and .wma files to also use the .asf mimetype as well
             // See http://en.wikipedia.org/wiki/Advanced_Systems_Format
             if (in_array('wmv', $data)) {
                 $mimetypes['video/x-ms-asf'] = 'wmv';
             }
             if (in_array('wma', $data)) {
                 $mimetypes['video/x-ms-asf'] = 'wma';
             }
             return $mimetypes;
         }
     }
     return array();
 }

 public static function save_config_options($form, $values)
 {
     global $USER;
     $updatingquota = false;
     $oldquotalimit = get_config_plugin('artefact', 'file', 'quotanotifylimit');
     if ($values['updateuserquotas'] && $values['defaultquota']) {
         set_field('usr', 'quota', $values['defaultquota'], 'deleted', 0);
         $updatingquota = true;
     }
     if ($values['updategroupquotas'] && $values['defaultgroupquota']) {
         set_field('group', 'quota', $values['defaultgroupquota'], 'deleted', 0);
         // We need to alert group admins that the group may now be over the threshold that wasn't before
         $sqlwhere = " ((g.quotaused / g.quota) * 100) ";
         if (is_postgres()) {
             $sqlwhere = " ((CAST(g.quotaused AS float) / CAST(g.quota AS float)) * 100) ";
         }
         if ($groups = get_records_sql_assoc("SELECT g.id, g.name, g.quota, " . $sqlwhere . " AS quotausedpercent FROM {group} g WHERE " . $sqlwhere . " >= ?", array($values['quotanotifylimit']))) {
             ArtefactTypeFile::notify_groups_threshold_exceeded($groups);
         }
     }
     set_config_plugin('artefact', 'file', 'defaultquota', $values['defaultquota']);
     set_config_plugin('artefact', 'file', 'defaultgroupquota', $values['defaultgroupquota']);
     set_config_plugin('artefact', 'file', 'institutionaloverride', $values['institutionaloverride']);
     set_config_plugin('artefact', 'file', 'maxquota', $values['maxquota']);
     set_config_plugin('artefact', 'file', 'maxquotaenabled', $values['maxquotaenabled']);
     set_config_plugin('artefact', 'file', 'profileiconwidth', $values['profileiconwidth']);
     set_config_plugin('artefact', 'file', 'profileiconheight', $values['profileiconheight']);
     set_config_plugin('artefact', 'file', 'uploadagreement', $values['uploadagreement']);
     set_config_plugin('artefact', 'file', 'usecustomagreement', $values['usecustomagreement']);
     set_config_plugin('artefact', 'file', 'resizeonuploadenable', $values['resizeonuploadenable']);
     set_config_plugin('artefact', 'file', 'resizeonuploaduseroption', $values['resizeonuploaduseroption']);
     set_config_plugin('artefact', 'file', 'resizeonuploadmaxwidth', $values['resizeonuploadmaxwidth']);
     set_config_plugin('artefact', 'file', 'resizeonuploadmaxheight', $values['resizeonuploadmaxheight']);
     set_config_plugin('artefact', 'file', 'folderdownloadkeepzipfor', $values['folderdownloadkeepzipfor']);
     set_config_plugin('artefact', 'file', 'quotanotifylimit', $values['quotanotifylimit']);
     set_config_plugin('artefact', 'file', 'quotanotifyadmin', $values['quotanotifyadmin']);
     if ($oldquotalimit != $values['quotanotifylimit'] || $updatingquota) {
         // We need to alert anyone that may now be over the threshold that wasn't before
         $sqlwhere = " ((u.quotaused / u.quota) * 100) ";
         if (is_postgres()) {
             $sqlwhere = " ((CAST(u.quotaused AS float) / CAST(u.quota AS float)) * 100) ";
         }
         if ($users = get_records_sql_assoc("SELECT u.id, u.quota, " . $sqlwhere . " AS quotausedpercent FROM {usr} u WHERE " . $sqlwhere . " >= ?", array($values['quotanotifylimit']))) {
             $notifyadmin = get_config_plugin('artefact', 'file', 'quotanotifyadmin');
             ArtefactTypeFile::notify_users_threshold_exceeded($users, $notifyadmin);
         } else {
             if ($users = get_records_sql_assoc("SELECT * FROM {usr} u, {usr_account_preference} uap WHERE " . $sqlwhere . " < ? AND uap.usr = u.id AND uap.field = ? AND uap.value = ?", array($values['quotanotifylimit'], 'quota_exceeded_notified', '1'))) {
                 foreach ($users as $user) {
                     set_account_preference($user->id, 'quota_exceeded_notified', false);
                 }
             }
         }
     }
     $data = new StdClass();
     $data->name = 'uploadcopyright';
     $data->content = $values['customagreement'];
     $data->mtime = db_format_timestamp(time());
     $data->mauthor = $USER->get('id');
     $data->institution = 'mahara';
     if (record_exists('site_content', 'name', $data->name, 'institution', $data->institution)) {
         update_record('site_content', $data, array('name', 'institution'));
     } else {
         $data->ctime = db_format_timestamp(time());
         insert_record('site_content', $data);
     }
     foreach (PluginArtefactFile::get_artefact_types() as $at) {
         set_config_plugin('artefact', 'file', 'commentsallowed' . $at, (int) in_array($at, $values['commentdefault']));
     }
 }

 /** 
  * Get views submitted to a group
  */
 public static function get_submitted_views($groupid, $userid = null)
 {
     $values = array($groupid);
     $where = 'submittedgroup = ?';
     if (!empty($userid)) {
         // Filter by view owner
         $values[] = (int) $userid;
         $where .= ' AND owner = ?';
     }
     $viewdata = get_records_sql_assoc('
         SELECT
             id, title, description, "owner", ownerformat, "group", institution,
             ' . db_format_tsfield('submittedtime') . '
         FROM {view}
         WHERE ' . $where . '
         ORDER BY title, id', $values);
     if ($viewdata) {
         View::get_extra_view_info($viewdata, false);
         return array_values($viewdata);
     }
     return false;
 }