PHP driver_interface::sql_like_expression方法代码示例

 public function memberlist_modify_query($event)
 {
     $sql_from = $event['sql_from'];
     $sql_where = $event['sql_where'];
     $user_from = $this->request->variable('user_from', '', true);
     $user_id = $this->request->variable('user_id', '');
     $this->template->assign_vars(array('USER_FROM' => $user_from, 'USER_ID' => (int) $user_id));
     if ($user_from) {
         $sql_from .= ', ' . PROFILE_FIELDS_DATA_TABLE . ' pf ';
         $pieces = explode(' ', $user_from);
         $sql_where .= ' AND (pf.pf_phpbb_location COLLATE utf8_general_ci ';
         $sql_where .= $this->db->sql_like_expression(str_replace('*', $this->db->get_any_char(), $pieces[0]));
         for ($i = 1; $i < sizeof($pieces); $i++) {
             $sql_where .= ' OR pf.pf_phpbb_location COLLATE utf8_general_ci ';
             $sql_where .= $this->db->sql_like_expression(str_replace('*', $this->db->get_any_char(), $pieces[$i]));
         }
         $sql_where .= ') AND u.user_id = pf.user_id';
         $event['sql_where'] = $sql_where;
         $event['sql_from'] = $sql_from;
     }
     if ((int) $user_id) {
         $sql_where .= ' AND u.user_id = ' . $user_id . '';
         $event['sql_where'] = $sql_where;
     }
 }

    /**
     * Display the output for this extension
     *
     * @return null
     * @access public
     */
    public function display_output()
    {
        // Add the language file
        $this->language->add_lang('acp_activesessions', 'david63/activesessions');
        // Start initial var setup
        $action = $this->request->variable('action', '');
        $start = $this->request->variable('start', 0);
        $fc = $this->request->variable('fc', '');
        $sort_key = $this->request->variable('sk', 's');
        $sd = $sort_dir = $this->request->variable('sd', 'd');
        $sort_dir = $sort_dir == 'd' ? ' DESC' : ' ASC';
        $order_ary = array('i' => 's.session_ip' . $sort_dir . ', u.username_clean ASC', 's' => 's.session_start' . $sort_dir . ', u.username_clean ASC', 'u' => 'u.username_clean' . $sort_dir);
        $filter_by = '';
        if ($fc == 'other') {
            for ($i = ord($this->language->lang('START_CHARACTER')); $i <= ord($this->language->lang('END_CHARACTER')); $i++) {
                $filter_by .= ' AND u.username_clean ' . $this->db->sql_not_like_expression(utf8_clean_string(chr($i)) . $this->db->get_any_char());
            }
        } else {
            if ($fc) {
                $filter_by .= ' AND u.username_clean ' . $this->db->sql_like_expression(utf8_clean_string(substr($fc, 0, 1)) . $this->db->get_any_char());
            }
        }
        $sql = $this->db->sql_build_query('SELECT', array('SELECT' => 'u.user_id, u.username, u.username_clean, u.user_colour, s.*, f.forum_id, f.forum_name', 'FROM' => array(USERS_TABLE => 'u', SESSIONS_TABLE => 's'), 'LEFT_JOIN' => array(array('FROM' => array(FORUMS_TABLE => 'f'), 'ON' => 's.session_forum_id = f.forum_id')), 'WHERE' => 'u.user_id = s.session_user_id
				AND s.session_time >= ' . (time() - $this->config['session_length'] * 60) . $filter_by, 'ORDER_BY' => $sort_key == '' ? 'u.username_clean' : $order_ary[$sort_key]));
        $result = $this->db->sql_query_limit($sql, $this->config['topics_per_page'], $start);
        while ($row = $this->db->sql_fetchrow($result)) {
            $this->template->assign_block_vars('active_sessions', array('ADMIN' => $row['session_admin'] ? $this->language->lang('YES') : $this->language->lang('NO'), 'AUTO_LOGIN' => $row['session_autologin'] ? $this->language->lang('YES') : $this->language->lang('NO'), 'BROWSER' => $row['session_browser'], 'FORUM' => $row['forum_id'] > 0 ? $row['forum_name'] : '', 'LAST_VISIT' => $this->user->format_date($row['session_last_visit']), 'SESSION_FORWARD' => $row['session_forwarded_for'], 'SESSION_ID' => $row['session_id'], 'SESSION_IP' => $row['session_ip'], 'SESSION_KEY' => $row['session_id'] . $row['user_id'], 'SESSION_ONLINE' => $row['session_viewonline'] ? $this->language->lang('YES') : $this->language->lang('NO'), 'SESSION_PAGE' => $row['session_page'], 'SESSION_START' => $this->user->format_date($row['session_start']), 'SESSION_TIME' => $this->user->format_date($row['session_time']), 'USERNAME' => get_username_string('full', $row['user_id'], $row['username'], $row['user_colour'])));
        }
        $this->db->sql_freeresult($result);
        $sort_by_text = array('u' => $this->language->lang('SORT_USERNAME'), 'i' => $this->language->lang('SESSION_IP'), 's' => $this->language->lang('SESSION_START'));
        $limit_days = array();
        $s_sort_key = $s_limit_days = $s_sort_dir = $u_sort_param = '';
        gen_sort_selects($limit_days, $sort_by_text, $sort_days, $sort_key, $sd, $s_limit_days, $s_sort_key, $s_sort_dir, $u_sort_param);
        // Get total session count for output
        $sql = $this->db->sql_build_query('SELECT', array('SELECT' => 'COUNT(s.session_id) AS total_sessions', 'FROM' => array(USERS_TABLE => 'u', SESSIONS_TABLE => 's'), 'WHERE' => 'u.user_id = s.session_user_id' . $filter_by));
        $result = $this->db->sql_query($sql);
        $session_count = (int) $this->db->sql_fetchfield('total_sessions');
        $this->db->sql_freeresult($result);
        $action = "{$this->u_action}&amp;sk={$sort_key}&amp;sd={$sd}";
        $link = $session_count ? adm_back_link($action . '&amp;start=' . $start) : '';
        if ($session_count == 0) {
            trigger_error($this->language->lang('NO_SESSION_DATA') . $link);
        }
        $start = $this->pagination->validate_start($start, $this->config['topics_per_page'], $session_count);
        $this->pagination->generate_template_pagination($action, 'pagination', 'start', $session_count, $this->config['topics_per_page'], $start);
        $first_characters = array();
        $first_characters[''] = $this->language->lang('ALL');
        for ($i = ord($this->language->lang('START_CHARACTER')); $i <= ord($this->language->lang('END_CHARACTER')); $i++) {
            $first_characters[chr($i)] = chr($i);
        }
        $first_characters['other'] = $this->language->lang('OTHER');
        foreach ($first_characters as $char => $desc) {
            $this->template->assign_block_vars('first_char', array('DESC' => $desc, 'U_SORT' => $action . '&amp;fc=' . $char));
        }
        $this->template->assign_vars(array('ACTIVE_SESSIONS_VERSION' => ext::ACTIVE_SESSIONS_VERSION, 'S_SORT_DIR' => $s_sort_dir, 'S_SORT_KEY' => $s_sort_key, 'TOTAL_USERS' => $this->language->lang('TOTAL_SESSIONS', (int) $session_count), 'U_ACTION' => $action));
    }

    /**
     * Get a list of all users on the board that can be mentioned. Keys are the usernames utf8_cleaned.
     * Data is cached after the first call.
     * 
     * @param string|bool $query_string False, if all users should be retrieved. Otherwise a string wich should be searched for.
     * @return array Array containing data of all users
     */
    public function get_userlist($query_string = false)
    {
        // If we need the complete list and it is cached, we can return it.
        if ($query_string == false && self::$user_list) {
            return self::$user_list;
        }
        $cache_time = 300;
        $sql_ary = array('SELECT' => '*', 'FROM' => array(USERS_TABLE => 'u'), 'WHERE' => 'user_posts >= ' . $this->config['wolfsblvt.mentions.min_posts_suggest'] . '
											AND user_type <> ' . USER_IGNORE, 'ORDER_BY' => 'username');
        if ($query_string) {
            $escaped_query_string_clean = $this->db->sql_escape(utf8_clean_string($query_string));
            $query_string['WHERE'] .= ' username_clean ' . $this->db->sql_like_expression($escaped_query_string_clean . $this->db->get_any_char());
        }
        $sql = $this->db->sql_build_query('SELECT', $sql_ary);
        $result = $this->db->sql_query($sql, $cache_time);
        $user_list = array();
        while ($row = $this->db->sql_fetchrow($result)) {
            $user_data = array('name' => $row['username'], 'user_id' => $row['user_id'], 'posts' => $row['user_posts'], 'colour' => $row['user_colour'], 'avatar' => phpbb_get_user_avatar($row), 'username_full' => get_username_string('full', $row['user_id'], $row['username'], $row['user_colour']), 'username_no_profile' => get_username_string('no_profile', $row['user_id'], $row['username'], $row['user_colour']));
            if ($user_data['avatar'] == '') {
                $default_avatar_url = $this->path_helper->get_web_root_path() . $this->ext_root_path . '/styles/' . $this->user->style['style_path'] . '/theme' . '/images/no_avatar.gif';
                // Check if file exists, otherwise take from "/all" folder. The administrator hasn't chosen a specific no_avatar avatar for this style then
                if (!file_exists($default_avatar_url)) {
                    $default_avatar_url = $this->path_helper->get_web_root_path() . $this->ext_root_path . '/styles/all/theme' . '/images/no_avatar.gif';
                }
                $user_data['avatar'] = '<img src="' . $default_avatar_url . '" width="100" height="100" alt="' . $this->user->lang['USER_AVATAR'] . '">';
            }
            $user_list[$row['username_clean']] = $user_data;
        }
        $this->db->sql_freeresult($result);
        // If we have the complete list, we can cache it.
        if ($query_string == false) {
            self::$user_list = $user_list;
        }
        return $user_list;
    }

    /**
     * Performs a search on keywords depending on display specific params. You have to run split_keywords() first
     *
     * @param	array		$keywords_ary		contains each words to search
     * @param	string		$fields				contains either titleonly (link titles should be searched), desconly (only description bodies should be searched)
     * @param	string		$terms				is either 'all' (use query as entered, words without prefix should default to "have to be in field") or 'any' (ignore search query parts and just return all posts that contain any of the specified words)
     * @param	array		$sort_by_sql		contains SQL code for the ORDER BY part of a query
     * @param	string		$sort_key			is the key of $sort_by_sql for the selected sorting
     * @param	string		$sort_dir			is either a or d representing ASC and DESC
     * @param	string		$sort_days			specifies the maximum amount of days a post may be old
     * @param	array		$ex_cid_ary			specifies an array of category ids which should not be searched
     * @param	int			$cat_id				is set to 0 or a topic id, if it is not 0 then only posts in this topic should be searched
     * @param	array		&$id_ary			passed by reference, to be filled with ids for the page specified by $start and $per_page, should be ordered
     * @param	int			$start				indicates the first index of the page
     * @param	int			$per_page			number of ids each page is supposed to contain
     * @return	int								total number of results
     */
    public function keyword_search($keywords_ary, $fields, $terms, $sort_by_sql, $sort_key, $sort_dir, $sort_days, $ex_cid_ary, $cat_id, &$id_ary, $start, $per_page)
    {
        $matches = array();
        switch ($fields) {
            case 'titleonly':
                $matches[] = 'l.link_name';
                break;
            case 'desconly':
                $matches[] = 'l.link_description';
                break;
            default:
                $matches = array('l.link_name', 'l.link_description');
        }
        $search_query = '';
        foreach ($keywords_ary as $word) {
            $match_search_query = '';
            foreach ($matches as $match) {
                $match_search_query .= ($match_search_query ? ' OR ' : '') . 'LOWER(' . $match . ') ';
                $match_search_query .= $this->db->sql_like_expression(str_replace('*', $this->db->get_any_char(), $this->db->get_any_char() . strtolower($word) . $this->db->get_any_char()));
            }
            $search_query .= (!$search_query ? '' : ($terms == 'all' ? ' AND ' : ' OR ')) . '(' . $match_search_query . ')';
        }
        $direction = $sort_dir == 'd' ? 'DESC' : 'ASC';
        if (is_array($sort_by_sql[$sort_key])) {
            $sql_sort_order = implode(' ' . $direction . ', ', $sort_by_sql[$sort_key]) . ' ' . $direction;
        } else {
            $sql_sort_order = $sort_by_sql[$sort_key] . ' ' . $direction;
        }
        $sql_array = array('SELECT' => 'l.link_id', 'FROM' => array(DIR_LINK_TABLE => 'l'), 'WHERE' => 'l.link_active = 1
				' . ($search_query ? 'AND (' . $search_query . ')' : '') . '
				' . (sizeof($ex_cid_ary) ? ' AND ' . $this->db->sql_in_set('l.link_cat', $ex_cid_ary, true) : '') . '
				' . ($cat_id ? ' AND ' . $this->db->sql_in_set('l.link_cat', $cat_id) : '') . '
				' . ($sort_days ? ' AND l.link_time >= ' . (time() - $sort_days * 86400) : ''), 'ORDER_BY' => $sql_sort_order);
        if ($sql_sort_order[0] == 'u') {
            $sql_array['LEFT_JOIN'][] = array('FROM' => array(USERS_TABLE => 'u'), 'ON' => 'u.user_id = l.link_user_id');
        }
        $sql = $this->db->sql_build_query('SELECT', $sql_array);
        $result = $this->db->sql_query($sql);
        while ($row = $this->db->sql_fetchrow($result)) {
            $id_ary[] = $row['link_id'];
        }
        $this->db->sql_freeresult($result);
        $total_match_count = sizeof($id_ary);
        $id_ary = array_slice($id_ary, $start, (int) $per_page);
        return $total_match_count;
    }

 /**
  * {@inheritdoc}
  */
 public function get_template_side($module_id)
 {
     // Generate birthday list if required ... / borrowed from index.php 3.0.6
     $birthday_list = $birthday_ahead_list = '';
     if ($this->config['load_birthdays'] && $this->config['allow_birthdays']) {
         $time = $this->user->create_datetime();
         $now = phpbb_gmgetdate($time->getTimestamp() + $time->getOffset());
         $cache_days = $this->config['board3_birthdays_ahead_' . $module_id];
         $sql_days = '';
         while ($cache_days > 0) {
             $day = phpbb_gmgetdate($time->getTimestamp() + 86400 * $cache_days + $time->getOffset());
             $like_expression = $this->db->sql_like_expression($this->db->get_any_char() . sprintf('%2d-%2d-', $day['mday'], $day['mon']) . $this->db->get_any_char());
             $sql_days .= " OR u.user_birthday " . $like_expression . "";
             $cache_days--;
         }
         switch ($this->db->get_sql_layer()) {
             case 'mssql':
             case 'mssql_odbc':
                 $order_by = 'u.user_birthday ASC';
                 break;
             default:
                 $order_by = 'SUBSTRING(u.user_birthday FROM 4 FOR 2) ASC, SUBSTRING(u.user_birthday FROM 1 FOR 2) ASC, u.username_clean ASC';
                 break;
         }
         $sql_array = array('SELECT' => 'u.user_id, u.username, u.user_colour, u.user_birthday', 'FROM' => array(USERS_TABLE => 'u'), 'LEFT_JOIN' => array(array('FROM' => array(BANLIST_TABLE => 'b'), 'ON' => 'u.user_id = b.ban_userid')), 'WHERE' => "(b.ban_id IS NULL\n\t\t\t\t\t\tOR b.ban_exclude = 1)\n\t\t\t\t\tAND (u.user_birthday " . $this->db->sql_like_expression($this->db->get_any_char() . sprintf('%2d-%2d-', $now['mday'], $now['mon']) . $this->db->get_any_char()) . " {$sql_days})\n\t\t\t\t\tAND " . $this->db->sql_in_set('u.user_type', array(USER_NORMAL, USER_FOUNDER)), 'ORDER BY' => $order_by);
         $sql = $this->db->sql_build_query('SELECT', $sql_array);
         $result = $this->db->sql_query($sql, 3600);
         $today = sprintf('%2d-%2d-', $now['mday'], $now['mon']);
         while ($row = $this->db->sql_fetchrow($result)) {
             if (substr($row['user_birthday'], 0, 6) == $today) {
                 $birthday_list = true;
                 $this->template->assign_block_vars('board3_birthday_list', array('USER' => get_username_string('full', $row['user_id'], $row['username'], $row['user_colour']), 'AGE' => ($age = (int) substr($row['user_birthday'], -4)) ? ' (' . ($now['year'] - $age) . ')' : ''));
             } else {
                 if ($this->config['board3_birthdays_ahead_' . $module_id] > 0) {
                     $birthday_ahead_list = true;
                     $this->template->assign_block_vars('board3_birthday_ahead_list', array('USER' => get_username_string('full', $row['user_id'], $row['username'], $row['user_colour']), 'AGE' => ($age = (int) substr($row['user_birthday'], -4)) ? ' (' . ($now['year'] - $age) . ')' : '', 'DATE' => $this->format_birthday($this->user, $row['user_birthday'], 'd M')));
                 }
             }
         }
         $this->db->sql_freeresult($result);
     }
     // Assign index specific vars
     $this->template->assign_vars(array('BIRTHDAY_LIST' => $birthday_list, 'BIRTHDAYS_AHEAD_LIST' => $this->config['board3_birthdays_ahead_' . $module_id] ? $birthday_ahead_list : '', 'L_BIRTHDAYS_AHEAD' => sprintf($this->user->lang['BIRTHDAYS_AHEAD'], $this->config['board3_birthdays_ahead_' . $module_id]), 'S_DISPLAY_BIRTHDAY_LIST' => $this->config['load_birthdays'] ? true : false, 'S_DISPLAY_BIRTHDAY_AHEAD_LIST' => $this->config['board3_birthdays_ahead_' . $module_id] > 0 ? true : false));
     return 'birthdays_side.html';
 }

 private function live_search_user()
 {
     $q = utf8_strtoupper(utf8_normalize_nfc($this->request->variable('q', '', true)));
     $sql = "SELECT user_id, username  FROM " . $this->users_table . " WHERE user_type <> " . USER_IGNORE . " AND username_clean " . $this->db->sql_like_expression(utf8_clean_string($this->db->sql_escape($q)) . $this->db->get_any_char());
     " ORDER BY username";
     $result = $this->db->sql_query($sql);
     $message = '';
     while ($row = $this->db->sql_fetchrow($result)) {
         $user_id = $row['user_id'];
         $key = htmlspecialchars_decode($row['username']);
         $message .= $key . "|{$user_id}\n";
     }
     $json_response = new \phpbb\json_response();
     $json_response->send($message);
 }

 /**
  * Gets $count tags that start with $query, ordered by their usage count (desc).
  * Note: that $query needs to be at least 3 characters long.
  *
  * @param $query prefix of tags to search
  * @param $exclude array of tags that should be ignored
  * @param $count count of tags to return
  * @return array (array('text' => '...'), array('text' => '...'))
  */
 public function get_tag_suggestions($query, $exclude, $count)
 {
     if (utf8_strlen($query) < 3) {
         return array();
     }
     $exclude_sql = '';
     if (!empty($exclude)) {
         $exclude_sql = ' AND ' . $this->db->sql_in_set('t.tag', $exclude, true, true);
     }
     $sql_array = array('SELECT' => 't.tag, t.count', 'FROM' => array($this->table_prefix . tables::TAGS => 't'), 'WHERE' => 't.tag ' . $this->db->sql_like_expression($query . $this->db->get_any_char()) . "\n\t\t\t\t\t\t\t{$exclude_sql}", 'ORDER_BY' => 't.count DESC');
     $sql = $this->db->sql_build_query('SELECT_DISTINCT', $sql_array);
     $result = $this->db->sql_query_limit($sql, $count);
     $tags = array();
     while ($row = $this->db->sql_fetchrow($result)) {
         $tags[] = array('text' => $row['tag']);
     }
     $this->db->sql_freeresult($result);
     return $tags;
 }

/**
* Cache moderators. Called whenever permissions are changed
* via admin_permissions. Changes of usernames and group names
* must be carried through for the moderators table.
*
* @param \phpbb\db\driver\driver_interface $db Database connection
* @param \phpbb\cache\driver\driver_interface Cache driver
* @param \phpbb\auth\auth $auth Authentication object
* @return null
*/
function phpbb_cache_moderators($db, $cache, $auth)
{
    // Remove cached sql results
    $cache->destroy('sql', MODERATOR_CACHE_TABLE);
    // Clear table
    switch ($db->get_sql_layer()) {
        case 'sqlite':
        case 'sqlite3':
            $db->sql_query('DELETE FROM ' . MODERATOR_CACHE_TABLE);
            break;
        default:
            $db->sql_query('TRUNCATE TABLE ' . MODERATOR_CACHE_TABLE);
            break;
    }
    // We add moderators who have forum moderator permissions without an explicit ACL_NEVER setting
    $sql_ary = array();
    // Grab all users having moderative options...
    $hold_ary = $auth->acl_user_raw_data(false, 'm_%', false);
    // Add users?
    if (sizeof($hold_ary)) {
        // At least one moderative option warrants a display
        $ug_id_ary = array_keys($hold_ary);
        // Remove users who have group memberships with DENY moderator permissions
        $sql_ary_deny = array('SELECT' => 'a.forum_id, ug.user_id, g.group_id', 'FROM' => array(ACL_OPTIONS_TABLE => 'o', USER_GROUP_TABLE => 'ug', GROUPS_TABLE => 'g', ACL_GROUPS_TABLE => 'a'), 'LEFT_JOIN' => array(array('FROM' => array(ACL_ROLES_DATA_TABLE => 'r'), 'ON' => 'a.auth_role_id = r.role_id')), 'WHERE' => '(o.auth_option_id = a.auth_option_id OR o.auth_option_id = r.auth_option_id)
				AND ((a.auth_setting = ' . ACL_NEVER . ' AND r.auth_setting IS NULL)
					OR r.auth_setting = ' . ACL_NEVER . ')
				AND a.group_id = ug.group_id
				AND g.group_id = ug.group_id
				AND NOT (ug.group_leader = 1 AND g.group_skip_auth = 1)
				AND ' . $db->sql_in_set('ug.user_id', $ug_id_ary) . "\n\t\t\t\tAND ug.user_pending = 0\n\t\t\t\tAND o.auth_option " . $db->sql_like_expression('m_' . $db->get_any_char()));
        $sql = $db->sql_build_query('SELECT', $sql_ary_deny);
        $result = $db->sql_query($sql);
        while ($row = $db->sql_fetchrow($result)) {
            if (isset($hold_ary[$row['user_id']][$row['forum_id']])) {
                unset($hold_ary[$row['user_id']][$row['forum_id']]);
            }
        }
        $db->sql_freeresult($result);
        if (sizeof($hold_ary)) {
            // Get usernames...
            $sql = 'SELECT user_id, username
				FROM ' . USERS_TABLE . '
				WHERE ' . $db->sql_in_set('user_id', array_keys($hold_ary));
            $result = $db->sql_query($sql);
            $usernames_ary = array();
            while ($row = $db->sql_fetchrow($result)) {
                $usernames_ary[$row['user_id']] = $row['username'];
            }
            $db->sql_freeresult($result);
            foreach ($hold_ary as $user_id => $forum_id_ary) {
                // Do not continue if user does not exist
                if (!isset($usernames_ary[$user_id])) {
                    continue;
                }
                foreach ($forum_id_ary as $forum_id => $auth_ary) {
                    $sql_ary[] = array('forum_id' => (int) $forum_id, 'user_id' => (int) $user_id, 'username' => (string) $usernames_ary[$user_id], 'group_id' => 0, 'group_name' => '');
                }
            }
        }
    }
    // Now to the groups...
    $hold_ary = $auth->acl_group_raw_data(false, 'm_%', false);
    if (sizeof($hold_ary)) {
        $ug_id_ary = array_keys($hold_ary);
        // Make sure not hidden or special groups are involved...
        $sql = 'SELECT group_name, group_id, group_type
			FROM ' . GROUPS_TABLE . '
			WHERE ' . $db->sql_in_set('group_id', $ug_id_ary);
        $result = $db->sql_query($sql);
        $groupnames_ary = array();
        while ($row = $db->sql_fetchrow($result)) {
            if ($row['group_type'] == GROUP_HIDDEN || $row['group_type'] == GROUP_SPECIAL) {
                unset($hold_ary[$row['group_id']]);
            }
            $groupnames_ary[$row['group_id']] = $row['group_name'];
        }
        $db->sql_freeresult($result);
        foreach ($hold_ary as $group_id => $forum_id_ary) {
            // If there is no group, we do not assign it...
            if (!isset($groupnames_ary[$group_id])) {
                continue;
            }
            foreach ($forum_id_ary as $forum_id => $auth_ary) {
                $flag = false;
                foreach ($auth_ary as $auth_option => $setting) {
                    // Make sure at least one ACL_YES option is set...
                    if ($setting == ACL_YES) {
                        $flag = true;
                        break;
                    }
//.........这里部分代码省略.........

    /**
     * Display the output for this extension
     *
     * @return null
     * @access public
     */
    public function display_output()
    {
        // Check that the user has permission to access here
        if (!$this->auth->acl_get('a_comms_pm_stats')) {
            trigger_error('NOT_AUTHORISED', E_USER_WARNING);
        }
        // Add the language file
        $this->language->add_lang('acp_pmstats', 'david63/pmstats');
        // Get message count
        $sql = 'SELECT COUNT(msg_id) AS total_msg
			FROM ' . PRIVMSGS_TO_TABLE;
        $result = $this->db->sql_query($sql);
        $total_msg = (int) $this->db->sql_fetchfield('total_msg');
        $this->db->sql_freeresult($result);
        // If no data then no point going any further
        if ($total_msg == 0) {
            trigger_error($this->language->lang('NO_PM_DATA'));
        }
        // Start initial var setup
        $action = $this->request->variable('action', '');
        $fc = $this->request->variable('fc', '');
        $sort_key = $this->request->variable('sk', 't');
        $start = $this->request->variable('start', 0);
        $sd = $sort_dir = $this->request->variable('sd', 'd');
        $sort_dir = $sort_dir == 'd' ? ' DESC' : ' ASC';
        $order_ary = array('d' => 'p.pm_deleted' . $sort_dir . ', u.username_clean ASC', 'f' => 'p.pm_forwarded' . $sort_dir . ', u.username_clean ASC', 'h' => 'holdbox' . $sort_dir . ', u.username_clean ASC', 'i' => 'inbox' . $sort_dir . ', u.username_clean ASC', 'm' => 'p.pm_marked' . $sort_dir . ', u.username_clean ASC', 'n' => 'p.pm_new' . $sort_dir . ', u.username_clean ASC', 'nb' => 'nobox' . $sort_dir . ', u.username_clean ASC', 't' => 'total' . $sort_dir . ', u.username_clean ASC', 'o' => 'outbox' . $sort_dir . ', u.username_clean ASC', 'r' => 'p.pm_replied' . $sort_dir . ', u.username_clean ASC', 's' => 'sentbox' . $sort_dir . ', u.username_clean ASC', 'sv' => 'savedbox' . $sort_dir . ', u.username_clean ASC', 'u' => 'u.username_clean' . $sort_dir, 'un' => 'p.pm_unread' . $sort_dir . ', u.username_clean ASC');
        $filter_by = '';
        if ($fc == 'other') {
            for ($i = ord($this->language->lang('START_CHARACTER')); $i <= ord($this->language->lang('END_CHARACTER')); $i++) {
                $filter_by .= ' AND u.username_clean ' . $this->db->sql_not_like_expression(utf8_clean_string(chr($i)) . $this->db->get_any_char());
            }
        } else {
            if ($fc) {
                $filter_by .= ' AND u.username_clean ' . $this->db->sql_like_expression(utf8_clean_string(substr($fc, 0, 1)) . $this->db->get_any_char());
            }
        }
        $sql = $this->db->sql_build_query('SELECT', array('SELECT' => 'u.user_id, u.username, u.username_clean, u.user_colour, p.pm_deleted, p.pm_new, p.pm_unread, p.pm_replied, p.pm_marked, p.pm_forwarded, SUM(IF(p.folder_id = ' . PRIVMSGS_INBOX . ', 1, 0)) AS inbox, SUM(IF(p.folder_id = ' . PRIVMSGS_SENTBOX . ', 1, 0)) AS sentbox, SUM(IF(p.folder_id = ' . PRIVMSGS_OUTBOX . ', 1, 0)) AS outbox, SUM(IF(p.folder_id = ' . PRIVMSGS_NO_BOX . ', 1, 0)) AS nobox, SUM(IF(p.folder_id = ' . PRIVMSGS_HOLD_BOX . ', 1, 0)) AS holdbox, SUM(IF(p.folder_id > ' . PRIVMSGS_INBOX . ', 1, 0)) AS savedbox, COUNT(p.folder_id) AS total', 'FROM' => array(USERS_TABLE => 'u'), 'LEFT_JOIN' => array(array('FROM' => array(PRIVMSGS_TO_TABLE => 'p'), 'ON' => 'u.user_id = p.user_id')), 'WHERE' => 'u.user_type <> ' . USER_IGNORE . $filter_by, 'ORDER_BY' => $sort_key == '' ? 'total DESC' : $order_ary[$sort_key], 'GROUP_BY' => 'u.username_clean'));
        $result = $this->db->sql_query_limit($sql, $this->config['topics_per_page'], $start);
        while ($row = $this->db->sql_fetchrow($result)) {
            $this->template->assign_block_vars('pm_statistics', array('DELETED' => (int) $row['pm_deleted'], 'FORWARDED' => (int) $row['pm_forwarded'], 'HOLDBOX' => $row['holdbox'], 'INBOX' => $row['inbox'], 'MARKED' => (int) $row['pm_marked'], 'NEW' => (int) $row['pm_new'], 'NOBOX' => $row['nobox'], 'OUTBOX' => $row['outbox'], 'REPLIED' => (int) $row['pm_replied'], 'SAVEDBOX' => $row['savedbox'], 'SENTBOX' => $row['sentbox'], 'UNREAD' => (int) $row['pm_unread'], 'USERNAME' => get_username_string('full', $row['user_id'], $row['username'], $row['user_colour']), 'TOTAL' => $row['total']));
        }
        $this->db->sql_freeresult($result);
        $sort_by_text = array('u' => $this->language->lang('SORT_USERNAME'), 't' => $this->language->lang('SORT_TOTAL_MESSAGES'), 'i' => $this->language->lang('SORT_INBOX'), 'n' => $this->language->lang('SORT_NEW'), 'un' => $this->language->lang('SORT_UNREAD'), 'o' => $this->language->lang('SORT_OUTBOX'), 's' => $this->language->lang('SORT_SENT'), 'sv' => $this->language->lang('SORT_SAVED'), 'h' => $this->language->lang('SORT_HOLD'), 'd' => $this->language->lang('SORT_DELETED'), 'm' => $this->language->lang('SORT_MARKED'), 'r' => $this->language->lang('SORT_REPLY'), 'f' => $this->language->lang('SORT_FORWARDED'), 'nb' => $this->language->lang('SORT_NO_BOX'));
        $limit_days = array();
        $s_sort_key = $s_limit_days = $s_sort_dir = $u_sort_param = '';
        gen_sort_selects($limit_days, $sort_by_text, $sort_days, $sort_key, $sd, $s_limit_days, $s_sort_key, $s_sort_dir, $u_sort_param);
        // Get total user count for pagination
        $sql = 'SELECT COUNT(u.user_id) AS total_users
			FROM ' . USERS_TABLE . ' u
			WHERE u.user_type <> ' . USER_IGNORE . $filter_by;
        $result = $this->db->sql_query($sql);
        $user_count = (int) $this->db->sql_fetchfield('total_users');
        $this->db->sql_freeresult($result);
        $action = $this->u_action . '&amp;sk=' . $sort_key . '&amp;sd=' . $sd;
        $this->pagination->generate_template_pagination($action, 'pagination', 'start', $user_count, $this->config['topics_per_page'], $start);
        $first_characters = array();
        $first_characters[''] = $this->language->lang('ALL');
        for ($i = ord($this->language->lang('START_CHARACTER')); $i <= ord($this->language->lang('END_CHARACTER')); $i++) {
            $first_characters[chr($i)] = chr($i);
        }
        $first_characters['other'] = $this->language->lang('OTHER');
        foreach ($first_characters as $char => $desc) {
            $this->template->assign_block_vars('first_char', array('DESC' => $desc, 'U_SORT' => $action . '&amp;fc=' . $char));
        }
        $this->template->assign_vars(array('MESSAGE_COUNT' => $total_msg, 'S_SORT_DIR' => $s_sort_dir, 'S_SORT_KEY' => $s_sort_key, 'U_ACTION' => $action, 'PM_STATS_VERSION' => ext::PM_STATS_VERSION));
    }

    function main($id, $mode)
    {
        global $db, $user, $template, $request, $phpbb_container, $config, $phpbb_root_path, $phpEx;
        $this->config = $config;
        $this->db = $db;
        $this->request = $request;
        $this->template = $template;
        $this->user = $user;
        $this->phpbb_container = $phpbb_container;
        $this->tpl_name = 'friends_and_foes';
        $this->page_title = $user->lang('FRIENDS_AND_FOES');
        $form_key = 'friends_and_foes';
        add_form_key($form_key);
        if ($this->request->is_set_post('submit')) {
            if (!check_form_key($form_key)) {
                trigger_error('FORM_INVALID');
            }
        }
        // Start initial var setup
        $action = $this->request->variable('action', '');
        $start = $this->request->variable('start', 0);
        $fc = $this->request->variable('fc', '');
        $sort_key = $this->request->variable('sk', 'u');
        $sd = $sort_dir = $this->request->variable('sd', 'a');
        $sort_dir = $sort_dir == 'd' ? ' DESC' : ' ASC';
        $order_ary = array('f' => 'z.friend' . $sort_dir . ', u.username_clean ASC', 'o' => 'z.foe' . $sort_dir . ', u.username_clean ASC', 'u' => 'u.username_clean' . $sort_dir);
        $filter_by = '';
        if ($fc == 'other') {
            for ($i = 97; $i < 123; $i++) {
                $filter_by .= ' AND u.username_clean ' . $this->db->sql_not_like_expression(utf8_clean_string(chr($i)) . $this->db->get_any_char());
            }
        } else {
            if ($fc) {
                $filter_by .= ' AND u.username_clean ' . $this->db->sql_like_expression(utf8_clean_string(substr($fc, 0, 1)) . $this->db->get_any_char());
            }
        }
        $sql = $this->db->sql_build_query('SELECT', array('SELECT' => 'u.user_id, u.username, u.username_clean, u.user_colour, z.*', 'FROM' => array(USERS_TABLE => 'u', ZEBRA_TABLE => 'z'), 'WHERE' => 'u.user_id = z.user_id' . $filter_by, 'ORDER_BY' => $sort_key == '' ? 'u.username_clean' : $order_ary[$sort_key]));
        $result = $this->db->sql_query_limit($sql, $this->config['topics_per_page'], $start);
        while ($row = $this->db->sql_fetchrow($result)) {
            $rowset[] = $row;
        }
        $this->db->sql_freeresult($result);
        if (!empty($rowset)) {
            foreach ($rowset as $rowdata) {
                $sql = 'SELECT user_id, username
					FROM ' . USERS_TABLE . '
					WHERE user_id = ' . $rowdata['zebra_id'];
                $result = $this->db->sql_query($sql);
                $row = $this->db->sql_fetchrow($result);
                $this->template->assign_block_vars('friends_foes', array('FOE' => $rowdata['foe'] == 0 ? '' : get_username_string('full', $row['user_id'], $row['username'], 'CC3300'), 'FRIEND' => $rowdata['friend'] == 0 ? '' : get_username_string('full', $row['user_id'], $row['username'], '006600'), 'USERNAME' => get_username_string('full', $rowdata['user_id'], $rowdata['username'], $rowdata['user_colour'])));
            }
        }
        $this->db->sql_freeresult($result);
        $sort_by_text = array('u' => $this->user->lang['SORT_USERNAME'], 'f' => $this->user->lang['SORT_FRIEND'], 'o' => $this->user->lang['SORT_FOE']);
        $limit_days = array();
        $s_sort_key = $s_limit_days = $s_sort_dir = $u_sort_param = '';
        gen_sort_selects($limit_days, $sort_by_text, $sort_days, $sort_key, $sd, $s_limit_days, $s_sort_key, $s_sort_dir, $u_sort_param);
        // Are there any friends & foes?
        $sql = $this->db->sql_build_query('SELECT', array('SELECT' => 'COUNT(z.user_id) AS total_users', 'FROM' => array(USERS_TABLE => 'u', ZEBRA_TABLE => 'z'), 'WHERE' => 'u.user_id = z.user_id'));
        $result = $this->db->sql_query($sql);
        $fandf_count = (int) $this->db->sql_fetchfield('total_users');
        $this->db->sql_freeresult($result);
        // Get total user count for pagination
        $sql = $this->db->sql_build_query('SELECT', array('SELECT' => 'COUNT(z.user_id) AS total_users', 'FROM' => array(USERS_TABLE => 'u', ZEBRA_TABLE => 'z'), 'WHERE' => 'u.user_id = z.user_id' . $filter_by));
        $result = $this->db->sql_query($sql);
        $user_count = (int) $this->db->sql_fetchfield('total_users');
        $this->db->sql_freeresult($result);
        $action = $this->u_action . '&amp;sk=' . $sort_key . '&amp;sd=' . $sd;
        $link = $fandf_count ? adm_back_link($this->u_action . '&amp;sk=' . $sort_key . '&amp;sd=' . $sd . '&amp;start=' . $start) : '';
        if ($user_count == 0) {
            trigger_error($this->user->lang('NO_FF_DATA') . $link);
        }
        $pagination = $this->phpbb_container->get('pagination');
        $start = $pagination->validate_start($start, $this->config['topics_per_page'], $user_count);
        $pagination->generate_template_pagination($action, 'pagination', 'start', $user_count, $this->config['topics_per_page'], $start);
        $first_characters = array();
        $first_characters[''] = $this->user->lang['ALL'];
        for ($i = 97; $i < 123; $i++) {
            $first_characters[chr($i)] = chr($i - 32);
        }
        $first_characters['other'] = $this->user->lang['OTHER'];
        foreach ($first_characters as $char => $desc) {
            $template->assign_block_vars('first_char', array('DESC' => $desc, 'VALUE' => $char, 'U_SORT' => $action . '&amp;fc=' . $char));
        }
        $this->template->assign_vars(array('S_SORT_DIR' => $s_sort_dir, 'S_SORT_KEY' => $s_sort_key, 'TOTAL_USERS' => $this->user->lang('TOTAL_USERS', (int) $user_count), 'U_ACTION' => $action));
    }

    /**
     * Display the output for this extension
     *
     * @return null
     * @access public
     */
    public function display_output()
    {
        // Start initial var setup
        $action = $this->request->variable('action', '');
        $start = $this->request->variable('start', 0);
        $fce = $this->request->variable('fce', '');
        $fcu = $this->request->variable('fcu', '');
        $sort_key = $this->request->variable('sk', 'u');
        $sd = $sort_dir = $this->request->variable('sd', 'a');
        $sort_dir = $sort_dir == 'd' ? ' DESC' : ' ASC';
        $order_ary = array('e' => 'user_email' . $sort_dir . ', user_email ASC', 'j' => 'user_jabber' . $sort_dir . ', user_jabber ASC', 'u' => 'username_clean' . $sort_dir);
        $filter_by = '';
        if ($fcu == 'other') {
            for ($i = 97; $i < 123; $i++) {
                $filter_by .= ' AND username_clean ' . $this->db->sql_not_like_expression(utf8_clean_string(chr($i)) . $this->db->get_any_char());
            }
        } else {
            if ($fcu) {
                $filter_by .= ' AND username_clean ' . $this->db->sql_like_expression(utf8_clean_string(substr($fcu, 0, 1)) . $this->db->get_any_char());
            }
        }
        if ($fce == 'other') {
            for ($i = 97; $i < 123; $i++) {
                $filter_by .= ' AND user_email ' . $this->db->sql_not_like_expression(utf8_clean_string(chr($i)) . $this->db->get_any_char());
            }
        } else {
            if ($fce) {
                $filter_by .= ' AND user_email ' . $this->db->sql_like_expression(utf8_clean_string(substr($fce, 0, 1)) . $this->db->get_any_char());
            }
        }
        $order_by = $sort_key == '' ? 'username_clean' : $order_ary[$sort_key];
        $sql = 'SELECT user_id, username, username_clean, user_colour, user_email, user_jabber
			FROM ' . USERS_TABLE . '
				WHERE user_type <> ' . USER_IGNORE . "\n\t\t\t\t{$filter_by}\n\t\t\tORDER BY {$order_by}";
        $result = $this->db->sql_query_limit($sql, $this->config['topics_per_page'], $start);
        while ($row = $this->db->sql_fetchrow($result)) {
            $this->template->assign_block_vars('emaillist', array('EMAIL' => $row['user_email'], 'JABBER' => $this->config['jab_enable'] ? $row['user_jabber'] : '', 'USERNAME' => get_username_string('full', $row['user_id'], $row['username'], $row['user_colour'])));
        }
        $this->db->sql_freeresult($result);
        $sort_by_text = array('u' => $this->user->lang('SORT_USERNAME'), 'e' => $this->user->lang('SORT_EMAIL'));
        if ($this->config['jab_enable']) {
            $sort_by_text['j'] = $this->user->lang('SORT_JABBER');
        }
        $limit_days = array();
        $s_sort_key = $s_limit_days = $s_sort_dir = $u_sort_param = '';
        gen_sort_selects($limit_days, $sort_by_text, $sort_days, $sort_key, $sd, $s_limit_days, $s_sort_key, $s_sort_dir, $u_sort_param);
        // Get total user count for pagination
        $sql = 'SELECT COUNT(user_id) AS total_users
			FROM ' . USERS_TABLE . '
				WHERE user_type <> ' . USER_IGNORE . "\n\t\t\t\t{$filter_by}\n\t\t\tORDER BY {$order_by}";
        $result = $this->db->sql_query($sql);
        $user_count = (int) $this->db->sql_fetchfield('total_users');
        $this->db->sql_freeresult($result);
        $action = "{$this->u_action}&amp;sk={$sort_key}&amp;sd={$sd}";
        $pagination = $this->container->get('pagination');
        $start = $pagination->validate_start($start, $this->config['topics_per_page'], $user_count);
        $pagination->generate_template_pagination($action, 'pagination', 'start', $user_count, $this->config['topics_per_page'], $start);
        $first_characters = array();
        $first_characters[''] = $this->user->lang('ALL');
        for ($i = ord('a'); $i <= ord('z'); $i++) {
            $first_characters[chr($i)] = chr($i - 32);
        }
        $first_characters['other'] = $this->user->lang('OTHER');
        foreach ($first_characters as $char => $desc) {
            $this->template->assign_block_vars('first_char_user', array('DESC' => $desc, 'U_SORT' => $action . '&amp;fcu=' . $char));
            $this->template->assign_block_vars('first_char_email', array('DESC' => $desc, 'U_SORT' => $action . '&amp;fce=' . $char));
        }
        $this->template->assign_vars(array('S_JAB_ENABLE' => $this->config['jab_enable'], 'S_SORT_DIR' => $s_sort_dir, 'S_SORT_KEY' => $s_sort_key, 'TOTAL_USERS' => $this->user->lang('TOTAL_USERS', (int) $user_count), 'U_ACTION' => $action));
    }

    public function upcoming_birthdays()
    {
        $time = $this->user->create_datetime();
        $now = phpbb_gmgetdate($time->getTimestamp() + $time->getOffset());
        $today = mktime(0, 0, 0, $now['mon'], $now['mday'], $now['year']);
        // Number of seconds per day
        $secs_per_day = 24 * 60 * 60;
        // We will use the timezone offset for our cache name
        $cache_name = $time->getOffset();
        $cache_name = str_replace('-', 'minus_', $cache_name);
        $cache_name = $cache_name . '_ubl';
        if (($upcomingbirthdays = $this->cache->get('_' . $cache_name)) === false) {
            // Only care about dates ahead of today.  Start date is always tomorrow
            $date_start = $now[0] + $secs_per_day;
            $date_end = $date_start + (int) $this->config['allow_birthdays_ahead'] * $secs_per_day;
            $sql_array = array();
            while ($date_start <= $date_end) {
                $day = date('j', $date_start);
                $month = date('n', $date_start);
                $date = $this->db->sql_escape(sprintf('%2d-%2d-', $day, $month));
                $sql_array[] = "u.user_birthday " . $this->db->sql_like_expression($date . $this->db->get_any_char());
                $date_start = $date_start + $secs_per_day;
            }
            $sql = 'SELECT u.user_id, u.username, u.user_colour, u.user_birthday, b.ban_id
				FROM ' . USERS_TABLE . ' u
				LEFT JOIN ' . BANLIST_TABLE . " b ON (u.user_id = b.ban_userid)\n\t\t\t\tWHERE (b.ban_id IS NULL\n\t\t\t\t\tOR b.ban_exclude = 1)\n\t\t\t\t\tAND (" . implode(' OR ', $sql_array) . ")\n\t\t\t\t\tAND " . $this->db->sql_in_set('u.user_type', array(USER_NORMAL, USER_FOUNDER));
            $result = $this->db->sql_query($sql);
            $upcomingbirthdays = array();
            while ($row = $this->db->sql_fetchrow($result)) {
                $bdday = $bdmonth = 0;
                list($bdday, $bdmonth) = array_map('intval', explode('-', $row['user_birthday']));
                $bdcheck = strtotime(gmdate('Y') . '-' . (int) trim($bdmonth) . '-' . (int) trim($bdday) . ' UTC');
                $bdyear = $bdcheck < $today ? (int) gmdate('Y') + 1 : (int) gmdate('Y');
                $bddate = $bdyear . '-' . (int) $bdmonth . '-' . (int) $bdday;
                // re-write those who have feb 29th as a birthday but only on non leap years
                if ((int) trim($bdday) == 29 && (int) trim($bdmonth) == 2) {
                    if (!$this->is_leap_year($bdyear) && !$time->format('L')) {
                        $bdday = 28;
                        $bddate = $bdyear . '-' . (int) trim($bdmonth) . '-' . (int) trim($bdday);
                    }
                }
                $upcomingbirthdays[] = array('user_birthday_tstamp' => strtotime($bddate . ' UTC'), 'username' => $row['username'], 'user_birthdayyear' => $bdyear, 'user_birthday' => $row['user_birthday'], 'user_id' => $row['user_id'], 'user_colour' => $row['user_colour']);
            }
            $this->db->sql_freeresult($result);
            // cache this data for five minutes, this improves performance
            $this->cache->put('_' . $cache_name, $upcomingbirthdays, 300);
        }
        sort($upcomingbirthdays);
        $birthday_ahead_list = '';
        $tomorrow = mktime(0, 0, 0, $now['mon'], $now['mday'] + 1, $now['year']);
        for ($i = 0, $end = sizeof($upcomingbirthdays); $i < $end; $i++) {
            if ($upcomingbirthdays[$i]['user_birthday_tstamp'] >= $tomorrow && $upcomingbirthdays[$i]['user_birthday_tstamp'] <= $today + $this->config['allow_birthdays_ahead'] * $secs_per_day) {
                $user_link = get_username_string('full', $upcomingbirthdays[$i]['user_id'], $upcomingbirthdays[$i]['username'], $upcomingbirthdays[$i]['user_colour']);
                $birthdate = getdate($upcomingbirthdays[$i]['user_birthday_tstamp']);
                //lets add to the birthday_ahead list.
                $birthday_ahead_list .= ($birthday_ahead_list != '' ? $this->user->lang['COMMA_SEPARATOR'] : '') . '<span title="' . $birthdate['mday'] . '-' . $birthdate['mon'] . '-' . $birthdate['year'] . '">' . $user_link . '</span>';
                if ($age = (int) substr($upcomingbirthdays[$i]['user_birthday'], -4)) {
                    $birthday_ahead_list .= ' (' . ($upcomingbirthdays[$i]['user_birthdayyear'] - $age) . ')';
                }
            }
        }
        // Assign index specific vars
        $this->template->assign_vars(array('BIRTHDAYS_AHEAD_LIST' => $birthday_ahead_list, 'L_BIRTHDAYS_AHEAD' => $this->user->lang('BIRTHDAYS_AHEAD', $this->config['allow_birthdays_ahead'])));
    }