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 = '&find='.urlencode($find).'&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 = '&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 .= "&sortby=$sortby&sortorder=$sortorder&unconfirmed=$unconfirmed&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').'" /> <a href="./?page=users&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&start=$start&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"], " ", $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').': '.$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.' '.$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