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}&sk={$sort_key}&sd={$sd}";
$link = $session_count ? adm_back_link($action . '&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 . '&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;
}
}
if (!$flag) {
continue;
}
$sql_ary[] = array('forum_id' => (int) $forum_id, 'user_id' => 0, 'username' => '', 'group_id' => (int) $group_id, 'group_name' => (string) $groupnames_ary[$group_id]);
}
}
}
$db->sql_multi_insert(MODERATOR_CACHE_TABLE, $sql_ary);
}
/**
* 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 . '&sk=' . $sort_key . '&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 . '&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 . '&sk=' . $sort_key . '&sd=' . $sd;
$link = $fandf_count ? adm_back_link($this->u_action . '&sk=' . $sort_key . '&sd=' . $sd . '&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 . '&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}&sk={$sort_key}&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 . '&fcu=' . $char));
$this->template->assign_block_vars('first_char_email', array('DESC' => $desc, 'U_SORT' => $action . '&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'])));
}
