mods/_core/users/master_list.php

   1 <?php\r
   2 /************************************************************************/\r
   3 /* ATutor                                                                                                                               */\r
   4 /************************************************************************/\r
   5 /* Copyright (c) 2002-2010                                              */\r
   6 /* Inclusive Design Institute                                           */\r
   7 /* http://atutor.ca                                                                                                             */\r
   8 /*                                                                                                                                              */\r
   9 /* This program is free software. You can redistribute it and/or        */\r
  10 /* modify it under the terms of the GNU General Public License          */\r
  11 /* as published by the Free Software Foundation.                        */\r
  12 /************************************************************************/\r
  13 // $Id: master_list.php 10197 2010-09-16 16:18:25Z greg $\r
  14 \r
  15 define('AT_INCLUDE_PATH', '../../../include/');\r
  16 require(AT_INCLUDE_PATH.'vitals.inc.php');\r
  17 admin_authenticate(AT_ADMIN_PRIV_USERS);\r
  18 \r
  19 if (!defined('AT_MASTER_LIST') || !AT_MASTER_LIST) {\r
  20         require(AT_INCLUDE_PATH.'header.inc.php');\r
  21         $msg->addInfo('MASTER_LIST_DISABLED');\r
  22         $msg->printInfos();\r
  23         require(AT_INCLUDE_PATH.'footer.inc.php');\r
  24         exit;\r
  25 }\r
  26 \r
  27 \r
  28 if (isset($_POST['submit'])) {\r
  29         if ($_FILES['file']['error'] == 1) { \r
  30                 $errors = array('FILE_MAX_SIZE', ini_get('upload_max_filesize'));\r
  31                 $msg->addError($errors);\r
  32                 header('Location: '.$_SERVER['PHP_SELF']);\r
  33                 exit;\r
  34         }\r
  35 \r
  36         if (!$_FILES['file']['name'] || (!is_uploaded_file($_FILES['file']['tmp_name']))) {\r
  37                 $msg->addError('FILE_NOT_SELECTED');\r
  38                 header('Location: '.$_SERVER['PHP_SELF']);\r
  39                 exit;\r
  40         }\r
  41 \r
  42         $fp = fopen($_FILES['file']['tmp_name'], 'r');\r
  43         if ($fp) {\r
  44                 $existing_accounts = array();\r
  45                 $number_of_updates = 0;\r
  46 \r
  47                 if ($_POST['override'] > 0) {\r
  48                         /* Delete all the un-created accounts. (There is no member to delete or disable). */\r
  49                         $sql = "DELETE FROM ".TABLE_PREFIX."master_list WHERE member_id=0";\r
  50                         $result = mysql_query($sql, $db);\r
  51 \r
  52                         /* Get all the created accounts. (They will be disabled or deleted if not in the new list). */\r
  53                         $sql = "SELECT public_field, member_id FROM ".TABLE_PREFIX."master_list";\r
  54                         $result = mysql_query($sql, $db);\r
  55                         $num_affected += mysql_affected_rows($db);\r
  56                         if ($num_affected > 0) {\r
  57                                 $number_of_updated += $num_affected;\r
  58                         }\r
  59                         while ($row = mysql_fetch_assoc($result)) {\r
  60                                 $existing_accounts[$row['public_field']] = $row['member_id'];\r
  61                         }\r
  62                 }\r
  63                 $sql = '';\r
  64                 while (($row = fgetcsv($fp, 1000, ',')) !== FALSE) {\r
  65                         if (count($row) != 2) {\r
  66                                 continue;\r
  67                         }\r
  68                         if (!$existing_accounts[$row[0]]) {\r
  69                                 $row[0] = addslashes($row[0]);\r
  70                                 $row[1] = md5($row[1]); // this may be hashed\r
  71 \r
  72                                 $sql = "INSERT INTO ".TABLE_PREFIX."master_list VALUES ('$row[0]', '$row[1]', 0)";\r
  73                                 mysql_query($sql, $db);\r
  74 \r
  75                                 write_to_log(AT_ADMIN_LOG_INSERT, 'master_list', mysql_affected_rows($db), $sql);\r
  76                                 $num_affected = mysql_affected_rows($db);\r
  77                                 if ($num_affected > 0) {\r
  78                                         $number_of_updated += $num_affected;\r
  79                                 }\r
  80                         }\r
  81                         unset($existing_accounts[$row[0]]);\r
  82                 }\r
  83                 fclose($fp);\r
  84 \r
  85                 if (($_POST['override'] == 1) && $existing_accounts) {\r
  86                         // disable missing accounts\r
  87                         $existing_accounts = implode(',', $existing_accounts);\r
  88 \r
  89                         $sql    = "UPDATE ".TABLE_PREFIX."members SET status=".AT_STATUS_DISABLED.", creation_date=creation_date, last_login=last_login WHERE member_id IN ($existing_accounts)";\r
  90                         $result = mysql_query($sql, $db);\r
  91                         \r
  92                         write_to_log(AT_ADMIN_LOG_UPDATE, 'members', mysql_affected_rows($db), $sql);\r
  93 \r
  94                         // un-enrol disabled accounts\r
  95                         $sql    = "DELETE FROM ".TABLE_PREFIX."course_enrollment WHERE member_id IN ($existing_accounts)";\r
  96                         $result = mysql_query($sql, $db);\r
  97 \r
  98                         $num_affected = mysql_affected_rows($db);\r
  99                         if ($num_affected > 0) {\r
 100                                 $number_of_updated += $num_affected;\r
 101                         }\r
 102                         write_to_log(AT_ADMIN_LOG_DELETE, 'course_enrollment', mysql_affected_rows($db), $sql);\r
 103                         \r
 104                 } else if ($_POST['override'] == 2) {\r
 105                         // delete missing accounts\r
 106                 }\r
 107 \r
 108                 if ($number_of_updated > 0) {\r
 109                         $msg->addFeedback('MASTER_LIST_UPLOADED');\r
 110                 } else {\r
 111                         $msg->addFeedback('MASTER_LIST_NO_CHANGES');\r
 112                 }\r
 113                         header('Location: '.$_SERVER['PHP_SELF']);\r
 114         }\r
 115 \r
 116         exit;\r
 117 } else if (isset($_GET['edit'], $_GET['id'])) {\r
 118         if (substr($_GET['id'], 0, 1) != '-') {\r
 119                 header('Location: '.AT_BASE_HREF.'mods/_core/users/edit_user.php?id='.$_GET['id'] . SEP . 'ml=1');\r
 120         } else {\r
 121                 header('Location: '.AT_BASE_HREF.'mods/_core/users/master_list_edit.php?id='.substr($_GET['id'], 1) . SEP . 'ml=1');\r
 122         }\r
 123         exit;\r
 124 } else if (isset($_GET['delete'], $_GET['id'])) {\r
 125         if (substr($_GET['id'], 0, 1) != '-') {\r
 126                 header('Location: '.AT_BASE_HREF.'mods/_core/users/admin_delete.php?id='.$_GET['id'] . SEP . 'ml=1');\r
 127         } else {\r
 128                 header('Location: '.AT_BASE_HREF.'mods/_core/users/master_list_delete.php?id='.substr($_GET['id'], 1) . SEP . 'ml=1');\r
 129         }\r
 130         exit;\r
 131 } else if (isset($_GET['delete']) || isset($_GET['edit'])) {\r
 132         $msg->addError('NO_ITEM_SELECTED');\r
 133 }\r
 134 \r
 135 require(AT_INCLUDE_PATH.'header.inc.php');\r
 136 \r
 137 \r
 138 if ($_GET['reset_filter']) {\r
 139         unset($_GET);\r
 140 }\r
 141 ?>\r
 142 \r
 143 <form name="importForm" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>" enctype="multipart/form-data">\r
 144 <div class="input-form">\r
 145         <div class="row">\r
 146                 <h3><?php echo _AT('update_list'); ?></h3>\r
 147                 <label for="file"><?php echo _AT('file'); ?></label><br />\r
 148                 <input type="file" name="file" size="40" id="file" />\r
 149         </div>\r
 150         \r
 151         <div class="row">\r
 152                 <?php echo _AT('master_not_in_list'); ?><br />\r
 153                 <input type="radio" name="override" id="o0" value="0" checked="checked" /><label for="o0"><?php echo _AT('leave_unchanged'); ?></label>\r
 154                 <input type="radio" name="override" id="o1" value="1" /><label for="o1"><?php echo _AT('disable');     ?></label>\r
 155         </div>\r
 156 \r
 157         <div class="row buttons">\r
 158                 <input type= "submit" name="submit" value="<?php echo _AT('upload'); ?>" />\r
 159         </div>\r
 160 </div>\r
 161 </form>\r
 162 \r
 163 <?php\r
 164 \r
 165 if (isset($_GET['status']) && ($_GET['status'] != '')) {\r
 166         if ($_GET['status'] == 1) {\r
 167                 $status = ' M.member_id=0 ';\r
 168         } else {\r
 169                 $status = ' M.member_id>0 ';\r
 170         }\r
 171         $page_string .= SEP.'status='.$_GET['status'];\r
 172 } else {\r
 173         $status = '1';\r
 174 }\r
 175 \r
 176 if ($_GET['search']) {\r
 177         $_GET['search'] = trim($_GET['search']);\r
 178         $page_string .= SEP.'search='.urlencode($_GET['search']);\r
 179         $search = $addslashes($_GET['search']);\r
 180 \r
 181         $search = explode(',', $search);\r
 182 \r
 183         $sql = '';\r
 184         foreach ($search as $term) {\r
 185                 $term = trim($term);\r
 186                 $term = str_replace(array('%','_'), array('\%', '\_'), $term);\r
 187                 if ($term) {\r
 188                         if (strpos($term, '-') === FALSE) {\r
 189                                 $term = '%'.$term.'%';\r
 190                                 $sql .= "(M.public_field LIKE '$term') OR ";\r
 191                         } else {\r
 192                                 // range search\r
 193                                 $range = explode('-', $term, 2);\r
 194                                 $range[0] = trim($range[0]);\r
 195                                 $range[1] = trim($range[1]);\r
 196                                 if (is_numeric($range[0]) && is_numeric($range[1])) {\r
 197                                         $sql .= "(M.public_field >= $range[0] AND M.public_field <= $range[1]) OR ";\r
 198                                 } else {\r
 199                                         $sql .= "(M.public_field >= '$range[0]' AND M.public_field <= '$range[1]') OR ";\r
 200                                 }\r
 201                         }\r
 202                 }\r
 203         }\r
 204         $sql = '('.substr($sql, 0, -3).')';\r
 205         $search = $sql;\r
 206 } else {\r
 207         $search = '1';\r
 208 }\r
 209 \r
 210 $sql    = "SELECT COUNT(member_id) AS cnt FROM ".TABLE_PREFIX."master_list M WHERE $status AND $search";\r
 211 \r
 212 $result = mysql_query($sql, $db);\r
 213 $row = mysql_fetch_assoc($result);\r
 214 \r
 215 $num_results = $row['cnt'];\r
 216 \r
 217 $results_per_page = 100;\r
 218 $num_pages = max(ceil($num_results / $results_per_page), 1);\r
 219 $page = intval($_GET['p']);\r
 220 if (!$page) {\r
 221         $page = 1;\r
 222 }\r
 223 $offset = ($page-1)*$results_per_page;\r
 224 \r
 225 $sql    = "SELECT M.*, B.login, B.first_name, B.second_name, B.last_name FROM ".TABLE_PREFIX."master_list M LEFT JOIN ".TABLE_PREFIX."members B USING (member_id) WHERE $status AND $search ORDER BY M.public_field LIMIT $offset, $results_per_page";\r
 226 $result = mysql_query($sql, $db);\r
 227 ?>\r
 228 \r
 229 <form method="get" action="<?php echo $_SERVER['PHP_SELF']; ?>">\r
 230         <div class="input-form">\r
 231                 <div class="row">\r
 232                         <h3><?php echo _AT('results_found', $num_results); ?></h3>\r
 233                 </div>\r
 234 \r
 235                 <div class="row">\r
 236                         <?php echo _AT('account_status'); ?><br />\r
 237                         <input type="radio" name="status" value="1" id="s0" <?php if ($_GET['status'] == 1) { echo 'checked="checked"'; } ?> /><label for="s0"><?php echo _AT('not_created'); ?></label> \r
 238 \r
 239                         <input type="radio" name="status" value="2" id="s1" <?php if ($_GET['status'] == 2) { echo 'checked="checked"'; } ?> /><label for="s1"><?php echo _AT('created'); ?></label> \r
 240 \r
 241                         <input type="radio" name="status" value="" id="s" <?php if ($_GET['status'] == '') { echo 'checked="checked"'; } ?> /><label for="s"><?php echo _AT('all'); ?></label> \r
 242                 </div>\r
 243 \r
 244                 <div class="row">\r
 245                         <label for="search"><?php echo _AT('search'); ?> (<?php echo _AT('student_id'); ?>)</label><br />\r
 246                         <input type="text" name="search" id="search" size="20" value="<?php echo htmlspecialchars($_GET['search']); ?>" />\r
 247                 </div>\r
 248 \r
 249                 <div class="row buttons">\r
 250                         <input type="submit" name="filter" value="<?php echo _AT('filter'); ?>" />\r
 251                         <input type="submit" name="reset_filter" value="<?php echo _AT('reset_filter'); ?>" />\r
 252                 </div>\r
 253         </div>\r
 254 </form>\r
 255 \r
 256 <div class="paging">\r
 257         <ul>\r
 258         <?php for ($i=1; $i<=$num_pages; $i++): ?>\r
 259                 <li>\r
 260                         <?php if ($i == $page) : ?>\r
 261                                 <a class="current" href="<?php echo $_SERVER['PHP_SELF']; ?>?p=<?php echo $i.$page_string; ?>"><strong><?php echo $i; ?></strong></a>\r
 262                         <?php else: ?>\r
 263                                 <a href="<?php echo $_SERVER['PHP_SELF']; ?>?p=<?php echo $i.$page_string; ?>"><?php echo $i; ?></a>\r
 264                         <?php endif; ?>\r
 265                 </li>\r
 266         <?php endfor; ?>\r
 267         </ul>\r
 268 </div>\r
 269 \r
 270 \r
 271 <form name="form" method="get" action="<?php echo $_SERVER['PHP_SELF']; ?>">\r
 272 <input type="hidden" name="status" value="<?php echo $_GET['status']; ?>" />\r
 273 \r
 274 <table summary="" class="data" rules="cols" style="width: 60%;">\r
 275 <thead>\r
 276 <tr>\r
 277         <th scope="col">&nbsp;</th>\r
 278         <th scope="col"><?php echo _AT('student_id'); ?></th>\r
 279         <th scope="col"><?php echo _AT('login_name'); ?></th>\r
 280         <th scope="col"><?php echo _AT('first_name'); ?></th>\r
 281         <th scope="col"><?php echo _AT('second_name'); ?></th>\r
 282         <th scope="col"><?php echo _AT('last_name'); ?></th>\r
 283 </tr>\r
 284 </thead>\r
 285 <?php if ($num_results > 0): ?>\r
 286 <tfoot>\r
 287 <tr>\r
 288         <td colspan="6"><input type="submit" name="edit" value="<?php echo _AT('edit'); ?>" /> <input type="submit" name="delete" value="<?php echo _AT('delete'); ?>" /></td>\r
 289 </tr>\r
 290 </tfoot>\r
 291 <tbody>\r
 292         <?php while($row = mysql_fetch_assoc($result)): ?>\r
 293                 <tr onmousedown="document.form['m<?php echo $row['public_field']; ?>'].checked = true;rowselect(this);" id="r_<?php echo $row['public_field']; ?>">\r
 294                         <td><input type="radio" name="id" value="<?php \r
 295                                 if ($row['member_id']) {\r
 296                                         echo $row['member_id'];\r
 297                                 } else {\r
 298                                         echo '-'.$row['public_field'];\r
 299                                 }\r
 300                                 ?>" id="m<?php echo $row['public_field']; ?>" /></td>\r
 301                         <td><label for="m<?php echo $row['public_field']; ?>"><?php echo $row['public_field']; ?></label></td>\r
 302                         <td><?php\r
 303                                 if ($row['member_id']) {\r
 304                                         echo $row['login'];\r
 305                                 } else {\r
 306                                         echo '-';\r
 307                                 }\r
 308                                 ?></td>\r
 309                         <td><?php\r
 310                                 if ($row['member_id']) {\r
 311                                         echo $row['first_name'];\r
 312                                 } else {\r
 313                                         echo '-';\r
 314                                 }\r
 315                                 ?></td>\r
 316                         <td><?php\r
 317                                 if ($row['member_id']) {\r
 318                                         echo $row['second_name'];\r
 319                                 } else {\r
 320                                         echo '-';\r
 321                                 }\r
 322                                 ?></td>\r
 323                         <td><?php\r
 324                                 if ($row['member_id']) {\r
 325                                         echo $row['last_name'];\r
 326                                 } else {\r
 327                                         echo '-';\r
 328                                 }\r
 329                                 ?></td>\r
 330                 </tr>\r
 331         <?php endwhile; ?>\r
 332 </tbody>\r
 333 <?php else: ?>\r
 334         <tr>\r
 335                 <td colspan="6"><?php echo _AT('none_found'); ?></td>\r
 336         </tr>\r
 337 <?php endif; ?>\r
 338 </table>\r
 339 </form>\r
 340 <?php require(AT_INCLUDE_PATH.'footer.inc.php'); ?>