1<?php
2
3require_once dirname(__FILE__).'/accesscheck.php';
4
5$columns = array('messages', 'lists', 'bounces', 'blacklist');
6
7if (!defined('PHPLISTINIT')) {
8    exit;
9}
10
11$filterpanel = $countpanel = $paging = '';
12
13if (!isset($_SESSION['userlistfilter']) || !$_SESSION['userlistfilter']) {
14    $_SESSION['userlistfilter'] = array();
15}
16if (isset($_GET['sortby'])) {
17    $sortby = removeXss($_GET['sortby']);
18    //# only allow spaces and word chars
19    $sortby = preg_replace('/[^\w ]+/', '', $sortby);
20} else {
21    $sortby = '';
22}
23if (strtolower($sortby) == 'password') {
24    $sortby = '';
25}
26
27if (isset($_GET['delete'])) {
28    $delete = sprintf('%d', $_GET['delete']);
29} else {
30    $delete = 0;
31}
32//print $_GET["delete"].' '.$delete .isSuperUser();exit;
33
34if (isset($_GET['start'])) {
35    $start = sprintf('%d', $_GET['start']);
36} else {
37    $start = 0;
38}
39
40$searchdone = 1;
41if (!empty($_GET['start'])) {
42    $start = sprintf('%d', $_GET['start']);
43} else {
44    $start = 0;
45}
46$unconfirmed = !empty($_GET['unconfirmed']) ? sprintf('%d', $_GET['unconfirmed']) : 0;
47$blacklisted = !empty($_GET['blacklisted']) ? sprintf('%d', $_GET['blacklisted']) : 0;
48if (isset($_GET['sortorder'])) {
49    if ($_GET['sortorder'] == 'asc') {
50        $sortorder = 'asc';
51    } else {
52        $sortorder = 'desc';
53    }
54} else {
55    $sortorder = 'desc';
56}
57if (isset($_GET['listid'])) {
58    $listid = sprintf('%d', $_GET['listid']);
59} else {
60    $listid = 0;
61}
62if (isset($_GET['find'])) {
63    if (!isset($_GET['findby'])) {
64        $_GET['findby'] = '';
65    }
66
67    if ($_GET['find'] == 'NULL') {
68        $_SESSION['userlistfilter']['find'] = '';
69        $_SESSION['userlistfilter']['findby'] = '';
70    } else {
71        $_SESSION['userlistfilter']['find'] = removeXss($_GET['find']);
72        $_SESSION['userlistfilter']['findby'] = removeXss($_GET['findby']);
73    }
74} else {
75    $_SESSION['userlistfilter']['find'] = '';
76    $_SESSION['userlistfilter']['findby'] = '';
77}
78
79$find = trim($_SESSION['userlistfilter']['find']);
80$findby = $_SESSION['userlistfilter']['findby'];
81if (!$findby) {
82    $findby = 'email';
83}
84$findtables = '';
85$findbyselect = '';
86$findfield = '';
87$findfieldname = '';
88$find_url = '';
89
90// hmm interesting, if they select a findby but not a find, use the Sql wildcard:
91if ($findby && !$find) {
92    // this is very slow, so instead erase the findby.
93    //  $find = '%';
94    $findby = '';
95}
96
97$system_findby = array(
98    'email',
99    'foreignkey',
100    'uniqid',
101);
102
103if ($findby && $find && !in_array($findby, $system_findby)) {
104    $find_url = '&amp;find='.urlencode($find).'&amp;findby='.urlencode($findby);
105    $findatt = Sql_Fetch_Array_Query(sprintf('select id,tablename,type,name from %s where id = %d',
106        $tables['attribute'], $findby));
107    switch ($findatt['type']) {
108        case 'textline':
109        case 'hidden':
110            $findtables = ','.$tables['user_attribute'];
111            $findbyselect = sprintf(' %s.userid = %s.id and
112              %s.attributeid = %d and %s.value like "%%%s%%"', $tables['user_attribute'], $tables['user'],
113                $tables['user_attribute'], $findby, $tables['user_attribute'], sql_escape($find));
114            $findfield = $tables['user_attribute'].'.value as display, '.$tables['user'].'.bouncecount';
115            $findfieldname = $findatt['name'];
116            break;
117        case 'select':
118        case 'radio':
119            $findtables = ','.$tables['user_attribute'].','.$table_prefix.'listattr_'.$findatt['tablename'];
120            $findbyselect = sprintf(' %s.userid = %s.id and
121              %s.attributeid = %d and %s.value = %s.id and
122              %s.name like "%%%s%%"', $tables['user_attribute'], $tables['user'], $tables['user_attribute'], $findby,
123                $tables['user_attribute'], $table_prefix.
124                'listattr_'.$findatt['tablename'], $table_prefix.
125                'listattr_'.$findatt['tablename'], sql_escape($find));
126            $findfield = $table_prefix.'listattr_'.$findatt['tablename'].'.name as display, '.$tables['user'].'.bouncecount';
127            $findfieldname = $findatt['name'];
128            break;
129    }
130} else {
131    $findtables = '';
132    $findbyselect = sprintf(' %s like "%%%s%%"', $findby, sql_escape($find));
133    $findfield = $tables['user'].'.bouncecount,'.$tables['user'].'.foreignkey';
134    $findfieldname = 'Email';
135    $find_url = '&amp;find='.urlencode($find);
136}
137
138if (empty($findfield)) {
139    $findfield = 'email';
140}
141
142if ($require_login && !isSuperUser()) {
143    $access = accessLevel('users');
144    switch ($access) {
145        case 'owner':
146            $table_list = $tables['user'].','.$tables['listuser'].','.$tables['list'].$findtables;
147            $subselect = "{$tables['user']}.id = {$tables['listuser']}.userid and {$tables['listuser']}.listid = {$tables['list']}.id and {$tables['list']}.owner = ".$_SESSION['logindetails']['id'];
148            if ($unconfirmed) {
149                $subselect .= ' and !confirmed ';
150            }
151            if ($blacklisted) {
152                $subselect .= ' and blacklisted ';
153            }
154            if ($find && $findbyselect) {
155                $listquery = "select DISTINCT {$tables['user']}.email,{$tables['user']}.id,$findfield,confirmed from ".$table_list." where $subselect and $findbyselect";
156                $count = Sql_query("SELECT count(distinct {$tables['user']}.id) FROM ".$table_list." where $subselect and $findbyselect");
157                $unconfirmedcount = Sql_query("SELECT count(distinct {$tables['user']}.id) FROM ".$table_list." where $subselect and !confirmed and $findbyselect");
158            } else {
159                $listquery = "SELECT DISTINCT {$tables['user']}.email,{$tables['user']}.id,$findfield,confirmed FROM ".$table_list." WHERE $subselect";
160                $count = Sql_query("SELECT count(distinct {$tables['user']}.id) FROM ".$table_list." WHERE $subselect");
161                $unconfirmedcount = Sql_query("SELECT count(distinct {$tables['user']}.id) FROM ".$table_list." WHERE !confirmed and $subselect");
162            }
163            break;
164        case 'all':
165        case 'view':
166            $table_list = $tables['user'].$findtables;
167            if ($find && $findbyselect) {
168                if ($unconfirmed) {
169                    $findbyselect .= ' and !confirmed ';
170                }
171                if ($blacklisted) {
172                    $findbyselect .= ' and blacklisted ';
173                }
174                $listquery = "select DISTINCT {$tables['user']}.email,{$tables['user']}.id,$findfield,{$tables['user']}.confirmed from ".$table_list." where $findbyselect";
175                $count = Sql_query('SELECT count(*) FROM '.$table_list." where $findbyselect");
176                $unconfirmedcount = Sql_query('SELECT count(*) FROM '.$table_list." where !confirmed && $findbyselect");
177            } else {
178                $listquery = "select DISTINCT {$tables['user']}.email,{$tables['user']}.id,$findfield,{$tables['user']}.confirmed from ".$table_list;
179                $count = Sql_query('SELECT count(*) FROM '.$table_list);
180                $unconfirmedcount = Sql_query('SELECT count(*) FROM '.$table_list.' where !confirmed');
181                $searchdone = 0;
182            }
183            $delete_message = '<br />'.$GLOBALS['I18N']->get('Delete will delete user and all listmemberships').'<br />';
184            break;
185        case 'none':
186        default:
187            print Error($GLOBALS['I18N']->get('Your privileges for this page are insufficient'));
188
189            return;
190    }
191    $delete_message = '<br />'.$GLOBALS['I18N']->get('Delete will delete user from the list').'<br />';
192} else {
193    //# is superuser
194    $table_list = $tables['user'].$findtables;
195    if ($find && $findbyselect) {
196        if ($unconfirmed) {
197            $findbyselect .= ' and !confirmed ';
198        }
199        if ($blacklisted) {
200            $findbyselect .= ' and blacklisted ';
201        }
202        $listquery = "select {$tables['user']}.email,{$tables['user']}.id,$findfield,{$tables['user']}.confirmed from ".$table_list." where $findbyselect";
203        $count = Sql_query('SELECT count(*) FROM '.$table_list." where $findbyselect");
204        $unconfirmedcount = Sql_query('SELECT count(*) FROM '.$table_list." where !confirmed and $findbyselect");
205    } else {
206        $subselect = '';
207        if ($unconfirmed || $blacklisted) {
208            $subselect = ' where ';
209            if ($unconfirmed && $blacklisted) {
210                $subselect .= ' !confirmed and blacklisted ';
211            } elseif ($unconfirmed) {
212                $subselect .= ' !confirmed ';
213            } else {
214                $subselect .= ' blacklisted';
215            }
216        } else {
217            $searchdone = 0;
218        }
219        $listquery = "select {$tables['user']}.email,{$tables['user']}.id,$findfield,{$tables['user']}.confirmed from ".$table_list.' '.$subselect;
220        $count = Sql_query('SELECT count(*) FROM '.$table_list.' '.$subselect);
221        $unconfirmedcount = Sql_query('SELECT count(*) FROM '.$table_list.' where !confirmed');
222    }
223    $delete_message = '<br />'.$GLOBALS['I18N']->get('Delete will delete user and all listmemberships').'<br />';
224}
225
226$totalres = Sql_fetch_Row($unconfirmedcount);
227$totalunconfirmed = $totalres[0];
228$totalres = Sql_fetch_Row($count);
229$total = $totalres[0];
230
231if ($start > $total) {
232    $start = 0;
233}
234
235if (!empty($delete) && isSuperUser()) {
236    verifyCsrfGetToken();
237    // delete the index in delete
238    $action_result = $GLOBALS['I18N']->get('deleting')." $delete ..\n";
239    deleteUser($delete);
240
241    $action_result .= '..'.$GLOBALS['I18N']->get('Done').'<br/><hr/>';
242    $previous_search = '';
243    if (!$find == '') {
244        $previous_search = "&start=$start&find=$find&findby=$findby";
245    }
246
247    $_SESSION['action_result'] = $action_result;
248    Redirect("users$previous_search");
249} elseif (!empty($delete)) {
250    echo ActionResult(s('Sorry, only super users can delete users'));
251}
252
253if (isset($add)) {
254    if (isset($new)) {
255        $query = 'insert into '.$tables['user']." (email,entered) values(\"$new\",now())";
256        $result = Sql_query($query);
257        $userid = Sql_insert_id();
258        $query = 'insert into '.$tables['listuser']." (userid,listid,entered) values($userid,$id,now())";
259        $result = Sql_query($query);
260    }
261    echo ActionResult($GLOBALS['I18N']->get('User added'));
262}
263
264// Make the totals human readable
265$totalFormatted = number_format($total);
266$totalunconfirmedFormatted = number_format($totalunconfirmed);
267
268// Add messages to panel
269$countpanel .= s('%s subscribers in total', $totalFormatted);
270$countpanel .= '<br/>'.s('Subscribers with a red icon are either unconfirmed or blacklisted or both')." ($totalunconfirmedFormatted)<br/>";
271
272$url = getenv('REQUEST_URI');
273if ($unconfirmed) {
274    $unc = 'checked="checked"';
275} else {
276    $unc = '';
277}
278if ($blacklisted) {
279    $bll = 'checked="checked"';
280} else {
281    $bll = '';
282}
283if (!isset($start)) {
284    $start = 0;
285}
286
287$filterpanel .= '<div class="filter">';
288$filterpanel .= sprintf('<form method="get" name="listcontrol" action="">
289  <input type="hidden" name="page" value="users" />
290  <input type="hidden" name="start" value="%d" />
291  <input type="hidden" name="find" value="%s" />
292  <input type="hidden" name="findby" value="%s" />
293  <label for="unconfirmed">%s:<input type="checkbox" name="unconfirmed" value="1" %s /></label>
294  <label for="blacklisted">%s:<input type="checkbox" name="blacklisted" value="1" %s /></label>',
295    $start,
296    htmlspecialchars(stripslashes($find)),
297    htmlspecialchars(stripslashes($findby)),
298    $GLOBALS['I18N']->get('Show only unconfirmed users'),
299    $unc,
300    $GLOBALS['I18N']->get('Show only blacklisted users'),
301    $bll);
302//print '</td><td valign="top">';
303$select = '';
304foreach (array(
305             'email',
306             'bouncecount',
307             'entered',
308             'modified',
309             'foreignkey',
310         ) as $item) {
311    $select .= sprintf('     <option value="%s" %s>%s</option>', $item, $item == $sortby ? 'selected="selected"' : '',
312        $GLOBALS['I18N']->get($item));
313}
314
315$filterpanel .= sprintf('
316  <label for="sortby">%s: <select name="sortby" onchange="document.listcontrol.submit();">
317  <option value="0">-- default</option>
318  %s
319  </select></label>
320  <label for="sortdesc">%s: <input type="radio" name="sortorder" id="sortdesc" value="desc" %s onchange="document.listcontrol.submit();" /></label>
321  <label for="sortasc">%s: <input type="radio" name="sortorder" id="sortasc" value="asc" %s onchange="document.listcontrol.submit();" /></label>
322  <input class="submit" type="submit" name="change" value="%s" />
323  ',
324    $GLOBALS['I18N']->get('Sort by'), $select,
325    $GLOBALS['I18N']->get('desc'), $sortorder == 'desc' ? 'checked="checked"' : '',
326    $GLOBALS['I18N']->get('asc'), $sortorder == 'asc' ? 'checked="checked"' : '',
327    $GLOBALS['I18N']->get('Go'));
328$filterpanel .= '</div>';
329
330$order = '';
331if ($sortby) {
332    $order = ' order by '.$tables['user'].'.'.$sortby;
333    if ($sortorder == 'asc') {
334        $order .= ' asc';
335    } else {
336        $order .= ' desc';
337    }
338}
339$find_url .= "&amp;sortby=$sortby&amp;sortorder=$sortorder&amp;unconfirmed=$unconfirmed&amp;blacklisted=$blacklisted";
340
341$listing = '';
342$dolist = 1;
343if (true || $total > MAX_USER_PP) {
344    if (isset($start) && $start) {
345        $totalUserCount = number_format($start + MAX_USER_PP);
346        $listing = sprintf($GLOBALS['I18N']->get('Listing user %d to %d'), $start, $totalUserCount);
347        $limit = "limit $start,".MAX_USER_PP;
348    } else {
349        if ($total < USERSPAGE_MAX || $searchdone) {
350            $listing = sprintf($GLOBALS['I18N']->get('Listing user %d to %d'), 1, 50);
351            $limit = 'limit 0,50';
352            $start = 0;
353            $dolist = 1;
354        } else {
355            $dolist = 0;
356        }
357    }
358    if ($dolist) {
359        $paging = simplePaging('users'.$find_url, $start, $total, MAX_USER_PP, $GLOBALS['I18N']->get('Subscribers'));
360        $result = Sql_query("$listquery $order $limit");
361    } else {
362        //    print Info($GLOBALS['I18N']->get('too many subscribers, use a search query to list some'),1);
363        $result = 0;
364    }
365} else {
366    $result = Sql_Query("$listquery $order");
367}
368
369$filterpanel .= '
370<div class="usersFind">
371<input type="hidden" name="id" value="' .$listid.'" />';
372
373$filterpanel .= '<label for="find">'.$GLOBALS['I18N']->get('Find a user').'</label>';
374$filterpanel .= '<input type="text" name="find" value="';
375$filterpanel .= $find != '%' ? htmlspecialchars(stripslashes($find)) : '';
376$filterpanel .= '" size="30" />';
377
378$filterpanel .= '<select name="findby">';
379
380$filterpanel .= '<option value="email" ';
381$filterpanel .= $findby == 'email' ? 'selected="selected"' : '';
382$filterpanel .= '>'.$GLOBALS['I18N']->get('Email').'</option>';
383$filterpanel .= '<option value="foreignkey" ';
384$filterpanel .= $findby == 'foreignkey' ? 'selected="selected"' : '';
385$filterpanel .= '>'.$GLOBALS['I18N']->get('Foreign Key').'</option>';
386$filterpanel .= '<option value="uniqid" ';
387$filterpanel .= $findby == 'uniqid' ? 'selected="selected"' : '';
388$filterpanel .= '>'.$GLOBALS['I18N']->get('Unique ID').'</option>';
389
390$att_req = Sql_Query('select id,name from '.$tables['attribute'].' where type = "hidden" or type = "textline" or type = "select"');
391while ($row = Sql_Fetch_Array($att_req)) {
392    $filterpanel .= sprintf('<option value="%d" %s>%s</option>', $row['id'],
393        $row['id'] == $findby ? 'selected="selected"' : '', substr($row['name'], 0, 20));
394}
395
396$filterpanel .= '</select><input class="submit" type="submit" value="'.s('Go').'" />&nbsp;&nbsp;<a href="./?page=users&amp;find=NULL" class="reset">'.s('reset').'</a>';
397$filterpanel .= '</form></div>';
398//$filterpanel .= '<tr><td colspan="4"></td></tr>
399//</table>';
400
401echo Info($countpanel);
402
403$panel = new UIPanel($GLOBALS['I18N']->get('Find subscribers'), $filterpanel);
404echo $panel->display();
405
406//if (($require_login && isSuperUser()) || !$require_login)
407echo '<div class="actions">';
408echo '<div id="add-csv-button" class="pull-left">'.PageLinkButton('dlusers', $GLOBALS['I18N']->get('Download all users as CSV file'),
409        'nocache='.uniqid('')).'</div>';
410echo '<div id="add-user-button" class="pull-right">'.PageLinkButton('adduser', $GLOBALS['I18N']->get('Add a User')).'</div>';
411echo '</div><div class="clearfix"></div>';
412
413$some = 0;
414
415$ls = new WebblerListing(s('subscribers'));
416$ls->setElementHeading(s('subscriber'));
417$ls->usePanel($paging);
418if ($result) {
419    while ($user = Sql_fetch_array($result)) {
420        $some = 1;
421        $element = htmlspecialchars($user['email']);
422        $ls->addElement($element, PageURL2("user&amp;start=$start&amp;id=".$user['id'].$find_url));
423        $ls->setClass($element, 'row1');
424
425        //# we make one column with the subscriber status being "on" or "off"
426        //# two columns are too confusing and really unnecessary
427        // ON = confirmed &&  !blacklisted
428
429//    $ls->addColumn($user["email"], $GLOBALS['I18N']->get('confirmed'), $user["confirmed"] ? $GLOBALS["img_tick"] : $GLOBALS["img_cross"]);
430        //   if (in_array("blacklist", $columns)) {
431        $onblacklist = isBlackListed($element);
432        //    $ls->addColumn($user["email"], $GLOBALS['I18N']->get('bl l'), $onblacklist ? $GLOBALS["img_tick"] : $GLOBALS["img_cross"]);
433        //  }
434
435        if ($user['confirmed'] && !$onblacklist) {
436            $ls_confirmed = $GLOBALS['img_tick'];
437        } else {
438            $ls_confirmed = $GLOBALS['img_cross'];
439        }
440
441        $ls_del = '';
442//    $ls->addColumn($user["email"], $GLOBALS['I18N']->get('del'), sprintf('<a href="%s" onclick="return deleteRec(\'%s\');">del</a>',PageUrl2('users'.$find_url), PageURL2("users&start=$start&delete=" .$user["id"])));
443        if (isSuperUser()) {
444            $ls_del = sprintf('<a href="javascript:deleteRec(\'%s\');" class="del">del</a>',
445                PageURL2("users&start=$start&find=$find&findby=$findby&delete=".$user['id']));
446        }
447        /*    if (isset ($user['foreignkey'])) {
448              $ls->addColumn($user["email"], $GLOBALS['I18N']->get('key'), $user["foreignkey"]);
449            }
450            if (isset ($user["display"])) {
451              $ls->addColumn($user["email"], "&nbsp;", $user["display"]);
452            }
453        */
454        if (in_array('lists', $columns)) {
455            $lists = Sql_query('SELECT count(*) FROM '.$tables['listuser'].','.$tables['list'].' where userid = '.$user['id'].' and '.$tables['listuser'].'.listid = '.$tables['list'].'.id');
456            $membership = Sql_fetch_row($lists);
457            $ls->addColumn($element, $GLOBALS['I18N']->get('lists'), $membership[0]);
458        }
459
460        if (in_array('messages', $columns)) {
461            $msgs = Sql_query('SELECT count(*) FROM '.$tables['usermessage'].' where userid = '.$user['id'].' and status = "sent"');
462            $nummsgs = Sql_fetch_row($msgs);
463            $ls_msgs = $GLOBALS['I18N']->get('msgs').':&nbsp;'.$nummsgs[0];
464        }
465
466        //## allow plugins to add columns
467        if (isset($GLOBALS['plugins']) && is_array($GLOBALS['plugins'])) {
468            foreach ($GLOBALS['plugins'] as $plugin) {
469                if (method_exists($plugin, 'displayUsers')) {
470                    $plugin->displayUsers($user, $element, $ls);
471                }
472            }
473        }
474
475        $ls_bncs = '';
476        if (in_array('bounces', $columns) && !empty($user['bouncecount'])) {
477            $ls_bncs = $GLOBALS['I18N']->get('bncs').': '.$user['bouncecount'];
478        }
479        $ls->addRow($element, "<div class='listinghdname gray'>".$ls_msgs.'<br />'.$ls_bncs.'</div>',
480            $ls_del.'&nbsp;'.$ls_confirmed);
481    }
482    echo $ls->display();
483    if (!$some && !$total) {
484        $p = new UIPanel($GLOBALS['I18N']->get('no results'), $GLOBALS['I18N']->get('No users apply'));
485        echo $p->display();
486    }
487}
488