print $ls->display();
return;
} else {
if (!$find) {
$result = Sql_query('SELECT count(*) FROM ' . $tables['admin']);
} else {
$result = Sql_query('SELECT count(*) FROM ' . $tables['admin'] . " where loginname like \"%{$find}%\" or email like \"%{$find}%\"");
}
$totalres = Sql_fetch_Row($result);
$total = $totalres[0];
}
print '<p class="info">' . $total . ' ' . $GLOBALS['I18N']->get('Administrators');
print $find ? ' ' . $GLOBALS['I18N']->get('found') . '</p>' : '</p>';
$paging = '';
if ($total > MAX_USER_PP) {
$paging = simplePaging("admins{$remember_find}", $start, $total, MAX_USER_PP, $GLOBALS['I18N']->get('Administrators'));
}
$limit = '';
if ($total > MAX_USER_PP) {
if (isset($start) && $start) {
$limit = "limit {$start}," . MAX_USER_PP;
} else {
$limit = 'limit 0,50';
$start = 0;
}
}
if ($find) {
$result = Sql_query('SELECT id,loginname,email FROM ' . $tables['admin'] . ' where loginname like "%' . sql_escape($find) . '%" or email like "%' . sql_escape($find) . "%\" order by loginname {$limit}");
} else {
$result = Sql_query('SELECT id,loginname,email FROM ' . $tables['admin'] . " order by loginname {$limit}");
}
$start = 0;
$limit = "limit 0," . MAX_USER_PP;
}
## hmm, this needs more work, as it'll run out of memory, because it's building the entire
## listing before pushing it out.
## would be best to not have a limit, but putting one to avoid that
if ($download) {
$limit = ' limit 100000';
}
if ($id) {
$url_keep = '&id=' . $id;
} else {
$url_keep = '';
}
if ($total) {
$paging = simplePaging("mviews{$url_keep}", $start, $total, MAX_USER_PP, $GLOBALS['I18N']->get("Entries"));
$ls->usePanel($paging);
}
$req = Sql_Query(sprintf('select userid,email,um.entered as sent,min(um.viewed) as firstview,
max(um.viewed) as lastview, count(um.viewed) as viewcount,
abs(unix_timestamp(um.entered) - unix_timestamp(um.viewed)) as responsetime
from %s um, %s user, %s msg where um.messageid = %d and um.messageid = msg.id and um.userid = user.id and um.status = "sent" and um.viewed is not null %s
group by userid %s', $GLOBALS['tables']['usermessage'], $GLOBALS['tables']['user'], $GLOBALS['tables']['message'], $id, $subselect, $limit));
$summary = array();
while ($row = Sql_Fetch_Array($req)) {
if ($download) {
## with download, the 50 per page limit is not there.
set_time_limit(60);
$element = $row['email'];
} else {
$element = shortenTextDisplay($row['email'], 15);
$sortBySql = 'order by embargo desc, entered desc';
}
$req = Sql_query('select count(*) from ' . $tables['message'] . $whereClause . ' ' . $sortBySql);
$total_req = Sql_Fetch_Row($req);
$total = $total_req[0];
## Browse buttons table
$limit = $_SESSION['messagenumpp'];
$offset = 0;
if (isset($start) && $start > 0) {
$offset = $start;
} else {
$start = 0;
}
$paging = '';
if ($total > $_SESSION['messagenumpp']) {
$paging = simplePaging("messages{$url_keep}", $start, $total, $_SESSION['messagenumpp'], $GLOBALS['I18N']->get('Campaigns'));
}
$ls = new WebblerListing(s('Campaigns'));
$ls->usePanel($paging);
## messages table
if ($total) {
$result = Sql_query('SELECT * FROM ' . $tables['message'] . " {$whereClause} {$sortBySql} limit {$limit} offset {$offset}");
while ($msg = Sql_fetch_array($result)) {
$editlink = '';
$messagedata = loadMessageData($msg['id']);
if ($messagedata['subject'] != $messagedata['campaigntitle']) {
$listingelement = '<!--' . $msg['id'] . '-->' . stripslashes($messagedata['campaigntitle']) . '<br/><strong>' . stripslashes($messagedata['subject']) . '</strong>';
} else {
$listingelement = '<!--' . $msg['id'] . '-->' . stripslashes($messagedata['subject']);
}
# $listingelement = '<!--'.$msg['id'].'-->'.stripslashes($messagedata["campaigntitle"]);
if (isset($start) && $start) {
$totalUserCount = number_format($start + MAX_USER_PP);
$listing = sprintf($GLOBALS['I18N']->get('Listing user %d to %d'), $start, $totalUserCount);
$limit = "limit {$start}," . MAX_USER_PP;
} else {
if ($total < USERSPAGE_MAX || $searchdone) {
$listing = sprintf($GLOBALS['I18N']->get('Listing user %d to %d'), 1, 50);
$limit = 'limit 0,50';
$start = 0;
$dolist = 1;
} else {
$dolist = 0;
}
}
if ($dolist) {
$paging = simplePaging('users' . $find_url, $start, $total, MAX_USER_PP, $GLOBALS['I18N']->get('Subscribers'));
$result = Sql_query("{$listquery} {$order} {$limit}");
} else {
# print Info($GLOBALS['I18N']->get('too many subscribers, use a search query to list some'),1);
$result = 0;
}
} else {
$result = Sql_Query("{$listquery} {$order}");
}
$filterpanel .= '
<div class="usersFind">
<input type="hidden" name="id" value="' . $listid . '" />';
$filterpanel .= '<label for="find">' . $GLOBALS['I18N']->get('Find a user') . '</label>';
$filterpanel .= '<input type="text" name="find" value="';
$filterpanel .= $find != '%' ? htmlspecialchars(stripslashes($find)) : '';
$filterpanel .= '" size="30" />';
break;
}
}
# view events
$count = Sql_Query("select count(*) from {$tables['eventlog']} {$where}");
$totalres = Sql_fetch_Row($count);
$total = $totalres[0];
print $total . ' ' . $GLOBALS['I18N']->get('Events') . '<br/>';
if ($total > MAX_USER_PP) {
if (isset($start) && $start) {
$limit = "limit {$start}," . MAX_USER_PP;
} else {
$limit = "limit 0,50";
$start = 0;
}
print simplePaging("eventlog{$find_url}", $start, $total, MAX_USER_PP);
$query = 'select * from %s %s order by entered desc, id desc %s';
$query = sprintf($query, $tables['eventlog'], $where, $limit);
$result = Sql_query($query);
} else {
$query = 'select * from %s %s order by entered desc, id desc';
$query = sprintf($query, $tables['eventlog'], $where);
$result = Sql_Query($query);
}
$buttons = new ButtonGroup(new Button(PageURL2("eventlog"), 'delete'));
$buttons->addButton(new ConfirmButton($GLOBALS['I18N']->get('Are you sure you want to delete all events older than 2 months?'), PageURL2("eventlog", "Delete", "start={$start}&action=deleteprocessed"), $GLOBALS['I18N']->get('Delete all (> 2 months old)')));
$buttons->addButton(new ConfirmButton($GLOBALS['I18N']->get('Are you sure you want to delete all events matching this filter?'), PageURL2("eventlog", "Delete", "start={$start}&action=deleteall{$find_url}"), $GLOBALS['I18N']->get('Delete all')));
print $buttons->show();
if (!Sql_Num_Rows($result)) {
print '<p class="information">' . $GLOBALS['I18N']->get('No events available') . '</p>';
}
if (isset($_GET['start'])) {
$start = sprintf('%d', $_GET['start']);
} else {
$start = 0;
}
$offset = $start;
$baseurl = "bounces&start={$start}&tab={$currentTab}";
$limit = MAX_USER_PP;
if (!empty($actionresult)) {
$_SESSION['action_result'] = $actionresult;
Header('Location: ./?page=' . $baseurl);
exit;
# print '<div id="actionresult" class="result">'.$actionresult .'</div>';
}
if ($total > MAX_USER_PP) {
$paging = simplePaging("bounces&tab={$currentTab}", $start, $total, MAX_USER_PP, $status . ' ' . $GLOBALS['I18N']->get('bounces'));
$query = sprintf('select * from %s where status %s "unidentified bounce" order by date desc limit %s offset %s', $tables['bounce'], $status_compare, $limit, $offset);
$result = Sql_Query($query);
} else {
$paging = '';
$query = sprintf('select * from %s where status ' . $status_compare . ' "unidentified bounce" order by date desc', $tables['bounce']);
$result = Sql_Query($query);
}
$buttons = new ButtonGroup(new Button(PageURL2('bounces'), s('delete')));
$buttons->addButton(new ConfirmButton($GLOBALS['I18N']->get('are you sure you want to delete all unidentified bounces older than 2 months') . '?', PageURL2("{$baseurl}&action=deleteunidentified"), $GLOBALS['I18N']->get('delete all unidentified (> 2 months old)')));
$buttons->addButton(new ConfirmButton($GLOBALS['I18N']->get('are you sure you want to delete all bounces older than 2 months') . '?', PageURL2("{$baseurl}&action=deleteprocessed"), $GLOBALS['I18N']->get('delete all processed (> 2 months old)')));
$buttons->addButton(new ConfirmButton($GLOBALS['I18N']->get('are you sure you want to delete all bounces') . '?', PageURL2("{$baseurl}&action=deleteall"), $GLOBALS['I18N']->get('Delete all')));
print "<div class='actions'>\n";
print "<div class='minitabs'>\n";
print $tabs->display();
print "</div>\n";
if (count($aListCategories) > 1) {
print $tabs->display();
}
}
$countquery = ' select *' . ' from ' . $tables['list'] . $subselect;
$countresult = Sql_query($countquery);
$total = Sql_Num_Rows($countresult);
if ($total == 0 && count($aListCategories) && $current == '' && empty($_GET['tab'])) {
## reload to first category, if none found by default (ie all lists are categorised)
if (!empty($aListCategories[0])) {
Redirect('list&tab=' . $aListCategories[0]);
}
}
print '<p class="total">' . $total . ' ' . $GLOBALS['I18N']->get('Lists') . '</p>';
if ($total > 30 && empty($_SESSION['showalllists'])) {
$paging = simplePaging('list', $s, $total, 10, ' ');
$limit = " limit {$s},10";
} else {
$limit = '';
}
$result = Sql_query('select * from ' . $tables['list'] . ' ' . $subselect . ' order by listorder ' . $limit);
$numlists = Sql_Affected_Rows($result);
$ls = new WebblerListing(s('Lists'));
$ls->usePanel($paging);
/* Always Show a "list" of all subscribers
* https://mantis.phplist.com/view.php?id=17433
* many users are confused when they have more subscribers than members of lists
* this will avoid that confusion
* we can only do this for superusers of course
* */
if (SHOW_LIST_OFALL_SUBSCRIBERS && isSuperUser()) {
if (!$id) {
print '<p>' . $GLOBALS['I18N']->get('Select Message to view') . '</p>';
if (empty($start)) {
print '<div class="actions">' . PageLinkButton('statsoverview&dl=true', $GLOBALS['I18N']->get('Download as CSV file')) . '</div>';
}
$timerange = ' and msg.entered > date_sub(current_timestamp,interval 12 month)';
#$timerange = '';
$query = sprintf('select msg.owner,msg.id as messageid,count(um.viewed) as views,
count(um.status) as total,subject,date_format(sent,"%%e %%b %%Y") as sent,
bouncecount as bounced from %s um,%s msg where um.messageid = msg.id and um.status = "sent" %s %s %s
group by msg.id order by msg.entered desc', $GLOBALS['tables']['usermessage'], $GLOBALS['tables']['message'], $subselect, $timerange, $ownership);
$req = Sql_Query($query);
$total = Sql_Num_Rows($req);
if ($total > 10 && !$download) {
#print Paging(PageUrl2('statsoverview'),$start,$total,10);
$paging = simplePaging('statsoverview', $start, $total, 10);
$query .= $limit;
$req = Sql_Query($query);
}
if (!Sql_Affected_Rows()) {
print '<p class="information">' . $GLOBALS['I18N']->get('There are currently no messages to view') . '</p>';
}
$ls = new WebblerListing($GLOBALS['I18N']->get('Campaigns in the last year'));
$ls->usePanel($paging);
while ($row = Sql_Fetch_Array($req)) {
$element = '<!--' . $row['messageid'] . '-->' . shortenTextDisplay($row['subject'], 30);
$fwded = Sql_Fetch_Row_Query(sprintf('select count(id) from %s where message = %d', $GLOBALS['tables']['user_message_forward'], $row['messageid']));
$ls->addElement($element, PageURL2('statsoverview&id=' . $row['messageid']));
#,PageURL2('message&id='.$row['messageid']));
$ls->setClass($element, 'row1');
# $ls->addColumn($element,$GLOBALS['I18N']->get('owner'),$row['owner']);
}
# view bounces
$count = Sql_Query(sprintf('select count(*) from %s where status ' . $status_compare . ' "unidentified bounce"', $tables["bounce"]));
$totalres = Sql_fetch_Row($count);
$total = $totalres[0];
$find_url = '';
if (isset($_GET['start'])) {
$start = sprintf('%d', $_GET['start']);
} else {
$start = 0;
}
$offset = $start;
$baseurl = "bounces&start={$start}";
if ($total > MAX_USER_PP) {
$limit = MAX_USER_PP;
$paging = simplePaging("bounces", $start, $total, MAX_USER_PP, $status . ' ' . $GLOBALS['I18N']->get('bounces'));
$query = sprintf("select * from %s where status {$status_compare} ? order by date desc limit {$limit} offset {$offset}", $tables['bounce']);
$result = Sql_Query_Params($query, array('unidentified bounce'));
} else {
$paging = '';
$query = sprintf('select * from %s where status ' . $status_compare . ' ? order by date desc', $tables['bounce']);
$result = Sql_Query_Params($query, array('unidentified bounce'));
}
print '<div class="actions">';
print PageLinkButton('listbounces', $GLOBALS['I18N']->get('view bounces by list'));
$buttons = new ButtonGroup(new Button(PageURL2("bounces"), 'delete'));
$buttons->addButton(new ConfirmButton($GLOBALS['I18N']->get('are you sure you want to delete all unidentified bounces older than 2 months') . "?", PageURL2("{$baseurl}&action=deleteunidentified"), $GLOBALS['I18N']->get('delete all unidentified (> 2 months old)')));
$buttons->addButton(new ConfirmButton($GLOBALS['I18N']->get('are you sure you want to delete all bounces older than 2 months') . "?", PageURL2("{$baseurl}&action=deleteprocessed"), $GLOBALS['I18N']->get('delete all processed (> 2 months old)')));
$buttons->addButton(new ConfirmButton($GLOBALS['I18N']->get('are you sure you want to delete all bounces') . "?", PageURL2("{$baseurl}&action=deleteall"), $GLOBALS['I18N']->get('Delete all')));
if (ALLOW_DELETEBOUNCE) {
print $buttons->show();
$query = ' select count(*)' . ' from %s lu' . ' join %s u' . ' on lu.userid = u.id' . ' where lu.listid = ? ' . ' and ' . $confirmedSelection;
$query = sprintf($query, $tables['listuser'], $tables['user']);
$result = Sql_Query_Params($query, array($id));
$row = Sql_Fetch_row($result);
$total = $row[0];
$offset = $start;
$paging = '';
if ($total > MAX_USER_PP) {
if ($start > 0) {
$listing = sprintf(s("Listing subscriber %d to %d", $start, $start + MAX_USER_PP));
$limit = "limit {$start}," . MAX_USER_PP;
} else {
$listing = s("Listing subscriber 1 to 50");
$limit = "limit 0,50";
}
$paging = simplePaging("members&{$pagingKeep}&id=" . $id, $start, $total, MAX_USER_PP, $GLOBALS['I18N']->get('subscribers'));
}
$query = ' select u.*' . " from %s lu" . " join %s u" . ' on lu.userid = u.id' . ' where lu.listid = ?' . ' and ' . $confirmedSelection . ' order by confirmed desc, email' . ' limit ' . MAX_USER_PP . ' offset ' . $offset;
// TODO Consider using a subselect. select user where uid in select uid from list
$query = sprintf($query, $tables['listuser'], $tables['user']);
$result = Sql_Query_Params($query, array($id));
$tabs = new WebblerTabs();
$tabs->addTab(s("confirmed"), PageUrl2("members&id=" . $id), 'confirmed');
$tabs->addTab(s("unconfirmed"), PageUrl2("members&tab=unconfirmed&id=" . $id), 'unconfirmed');
if (!empty($_GET['tab'])) {
$tabs->setCurrent($_GET["tab"]);
} else {
$_GET['tab'] = 'confirmed';
$tabs->setCurrent('confirmed');
}
print $tabs->display();
$selectOtherlist = new buttonGroup(new Button(PageUrl2('listbounces'), $GLOBALS['I18N']->get('Select another list')));
$lists = Sql_Query(sprintf('select id,name from %s list %s order by listorder', $tables['list'], $isowner_where));
while ($list = Sql_Fetch_Assoc($lists)) {
$selectOtherlist->addButton(new Button(PageUrl2('listbounces') . '&id=' . $list['id'], htmlspecialchars($list['name'])));
}
print $selectOtherlist->show();
if ($total) {
print PageLinkButton('listbounces&type=dl&id=' . $listid, 'Download emails');
}
print '<p>' . s('%d bounces to list %s', $total, listName($listid)) . '</p>';
$start = empty($_GET['start']) ? 0 : sprintf('%d', $_GET['start']);
if ($total > $numpp && !$download) {
# print Paging2('listbounces&id='.$listid,$total,$numpp,'Page');
# $listing = sprintf($GLOBALS['I18N']->get("Listing %s to %s"),$s,$s+$numpp);
$limit = "limit {$start}," . $numpp;
print simplePaging('listbounces&id=' . $listid, $start, $total, $numpp);
$query .= $limit;
$req = Sql_Query($query);
}
if ($download) {
ob_end_clean();
Header('Content-type: text/plain');
$filename = 'Bounces on ' . listName($listid);
header("Content-disposition: attachment; filename=\"{$filename}\"");
}
$ls = new WebblerListing($GLOBALS['I18N']->get('Bounces on') . ' ' . listName($listid));
$ls->noShader();
while ($row = Sql_Fetch_Array($req)) {
$userdata = Sql_Fetch_Array_Query(sprintf('select * from %s where id = %d', $GLOBALS['tables']['user'], $row['userid']));
if (!empty($userdata['email'])) {
if ($download) {
$query = sprintf('select distinct user.email,user.id as userid,firstclick,date_format(latestclick,
"%%e %%b %%Y %%H:%%i") as latestclick,clicked from %s as uml_click, %s as user where uml_click.userid = user.id
and uml_click.messageid = %d group by user.email', $GLOBALS['tables']['linktrack_uml_click'], $GLOBALS['tables']['user'], $msgid);
} elseif ($userid) {
print '<h3>' . $GLOBALS['I18N']->get('Clicks of a subscriber') . '</h3>';
print s('Subscriber') . ' ' . PageLink2('user&id=' . $userid, $userdata['email']);
$query = sprintf('select sum(htmlclicked) as htmlclicked,sum(textclicked) as textclicked,user.email,user.id as userid,min(firstclick) as firstclick,date_format(max(latestclick),
"%%e %%b %%Y %%H:%%i") as latestclick,sum(clicked) as clicked,messageid,forwardid,url from %s as uml_click, %s as user, %s as forward where uml_click.userid = user.id
and uml_click.userid = %d and forward.id = uml_click.forwardid group by url', $GLOBALS['tables']['linktrack_uml_click'], $GLOBALS['tables']['user'], $GLOBALS['tables']['linktrack_forward'], $userid);
}
#ob_end_flush();
#flush();
$req = Sql_Query($query);
$total = Sql_Num_Rows($req);
if ($total > 100 && !$download) {
print simplePaging('userclicks&msgid=' . $msgid . '&fwdid=' . $fwdid . '&userid=' . $userid, $start, $total, 100, s('Subscribers'));
$limit = ' limit ' . $start . ', 100';
$req = Sql_Query($query . ' ' . $limit);
}
$summary = array();
$summary['totalclicks'] = 0;
while ($row = Sql_Fetch_Array($req)) {
# print $row['email'] . "<br/>";
if ($download) {
$downloadContent .= $row['email'] . PHP_EOL;
} else {
if (!$userid) {
$element = shortenTextDisplay($row['email']);
$ls->addElement($element, PageUrl2('userhistory&id=' . $row['userid']));
$ls->setClass($element, 'row1');
} else {
if (sizeof($aListCategories) > 1) {
print $tabs->display();
}
}
$countquery = ' select *' . ' from ' . $tables['list'] . $subselect;
$countresult = Sql_query($countquery);
$total = Sql_Num_Rows($countresult);
if ($total == 0 && sizeof($aListCategories) && $current == '' && empty($_GET['tab'])) {
## reload to first category, if none found by default (ie all lists are categorised)
if (!empty($aListCategories[0])) {
Redirect('list&tab=' . $aListCategories[0]);
}
}
print '<p class="total">' . $total . ' ' . $GLOBALS['I18N']->get('Lists') . '</p>';
if ($total > 30 && empty($_SESSION['showalllists'])) {
$paging = simplePaging("list", $s, $total, 10, ' ');
$limit = " limit {$s},10";
} else {
$limit = '';
}
$result = Sql_query('select * from ' . $tables['list'] . ' ' . $subselect . ' order by listorder ' . $limit);
$numlists = Sql_Affected_Rows($result);
$ls = new WebblerListing(s('Lists'));
$ls->usePanel($paging);
/** Always Show a "list" of all subscribers
* https://mantis.phplist.com/view.php?id=17433
* many users are confused when they have more subscribers than members of lists
* this will avoid that confusion
* we can only do this for superusers of course
* */
if (SHOW_LIST_OFALL_SUBSCRIBERS && isSuperUser()) {