/**
* 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));
}
/**
* 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));
}