00001 <?php
00002
00016
00017
00018 import('security.Role');
00019
00020 class RoleDAO extends DAO {
00024 function RoleDAO() {
00025 parent::DAO();
00026 $this->userDao =& DAORegistry::getDAO('UserDAO');
00027 }
00028
00036 function &getRole($conferenceId, $schedConfId, $userId, $roleId) {
00037 $result =& $this->retrieve(
00038 'SELECT * FROM roles WHERE conference_id = ? AND sched_conf_id = ? AND user_id = ? AND role_id = ?',
00039 array(
00040 (int) $conferenceId,
00041 (int) $schedConfId,
00042 (int) $userId,
00043 (int) $roleId
00044 )
00045 );
00046
00047 $returner = null;
00048 if ($result->RecordCount() != 0) {
00049 $returner =& $this->_returnRoleFromRow($result->GetRowAssoc(false));
00050 }
00051
00052 $result->Close();
00053 unset($result);
00054
00055 return $returner;
00056 }
00057
00063 function &_returnRoleFromRow(&$row) {
00064 $role = new Role();
00065 $role->setConferenceId($row['conference_id']);
00066 $role->setSchedConfId($row['sched_conf_id']);
00067 $role->setUserId($row['user_id']);
00068 $role->setRoleId($row['role_id']);
00069
00070 HookRegistry::call('RoleDAO::_returnRoleFromRow', array(&$role, &$row));
00071
00072 return $role;
00073 }
00074
00079 function insertRole(&$role) {
00080 return $this->update(
00081 'INSERT INTO roles
00082 (conference_id, sched_conf_id, user_id, role_id)
00083 VALUES
00084 (?, ?, ?, ?)',
00085 array(
00086 (int) $role->getConferenceId(),
00087 (int) $role->getSchedConfId(),
00088 (int) $role->getUserId(),
00089 (int) $role->getRoleId()
00090 )
00091 );
00092 }
00093
00098 function deleteRole(&$role) {
00099 return $this->update(
00100 'DELETE FROM roles WHERE conference_id = ? AND sched_conf_id = ? AND user_id = ? AND role_id = ?',
00101 array(
00102 (int) $role->getConferenceId(),
00103 (int) $role->getSchedConfId(),
00104 (int) $role->getUserId(),
00105 (int) $role->getRoleId()
00106 )
00107 );
00108 }
00109
00117 function &getRolesByUserId($userId, $conferenceId = null, $schedConfId = null) {
00118 $roles = array();
00119 $params = array();
00120
00121 $params[] = $userId;
00122 if(isset($conferenceId)) $params[] = $conferenceId;
00123 if(isset($schedConfId)) $params[] = $schedConfId;
00124
00125 $result =& $this->retrieve('SELECT * FROM roles WHERE user_id = ?' .
00126 (isset($conferenceId) ? ' AND conference_id = ?' : '') .
00127 (isset($schedConfId) ? ' AND sched_conf_id = ?' : ''),
00128 (count($params) == 1 ? array_shift($params) : $params));
00129
00130 while (!$result->EOF) {
00131 $roles[] =& $this->_returnRoleFromRow($result->GetRowAssoc(false));
00132 $result->moveNext();
00133 }
00134
00135 $result->Close();
00136 unset($result);
00137
00138 return $roles;
00139 }
00140
00152 function &getUsersByRoleId($roleId = null, $conferenceId = null, $schedConfId = null,
00153 $searchType = null, $search = null, $searchMatch = null, $dbResultRange = null,
00154 $sortBy = null, $sortDirection = SORT_DIRECTION_ASC ) {
00155
00156 $users = array();
00157
00158 $paramArray = array('interests');
00159 if (isset($roleId)) $paramArray[] = (int) $roleId;
00160 if (isset($conferenceId)) $paramArray[] = (int) $conferenceId;
00161 if (isset($schedConfId)) $paramArray[] = (int) $schedConfId;
00162
00163
00164
00165 if ($conferenceId === null && $schedConfId === null && $roleId === null) return null;
00166
00167 $searchSql = '';
00168
00169 $searchTypeMap = array(
00170 USER_FIELD_FIRSTNAME => 'u.first_name',
00171 USER_FIELD_LASTNAME => 'u.last_name',
00172 USER_FIELD_USERNAME => 'u.username',
00173 USER_FIELD_EMAIL => 'u.email',
00174 USER_FIELD_INTERESTS => 's.setting_value'
00175 );
00176
00177 if (!empty($search) && isset($searchTypeMap[$searchType])) {
00178 $fieldName = $searchTypeMap[$searchType];
00179 switch ($searchMatch) {
00180 case 'is':
00181 $searchSql = "AND LOWER($fieldName) = LOWER(?)";
00182 $paramArray[] = $search;
00183 break;
00184 case 'contains':
00185 $searchSql = "AND LOWER($fieldName) LIKE LOWER(?)";
00186 $paramArray[] = '%' . $search . '%';
00187 break;
00188 case 'startsWith':
00189 $searchSql = "AND LOWER($fieldName) LIKE LOWER(?)";
00190 $paramArray[] = $search . '%';
00191 break;
00192 }
00193 } elseif (!empty($search)) switch ($searchType) {
00194 case USER_FIELD_USERID:
00195 $searchSql = 'AND u.user_id=?';
00196 $paramArray[] = $search;
00197 break;
00198 case USER_FIELD_INITIAL:
00199 $searchSql = 'AND LOWER(u.last_name) LIKE LOWER(?)';
00200 $paramArray[] = $search . '%';
00201 break;
00202 }
00203
00204 $searchSql .= ($sortBy?(' ORDER BY ' . $this->getSortMapping($sortBy) . ' ' . $this->getDirectionMapping($sortDirection)) : '');
00205
00206 $result =& $this->retrieveRange(
00207 'SELECT DISTINCT u.* FROM users AS u LEFT JOIN user_settings s ON (u.user_id = s.user_id AND s.setting_name = ?), roles AS r WHERE u.user_id = r.user_id ' .
00208 (isset($roleId)?'AND r.role_id = ?':'') .
00209 (isset($conferenceId) ? ' AND r.conference_id = ?' : '') .
00210 (isset($schedConfId) ? ' AND r.sched_conf_id = ?' : '') .
00211 ' ' . $searchSql,
00212 $paramArray,
00213 $dbResultRange
00214 );
00215
00216 $returner = new DAOResultFactory($result, $this->userDao, '_returnUserFromRowWithData');
00217 return $returner;
00218 }
00219
00229 function &getUsersByConferenceId($conferenceId, $searchType = null, $search = null, $searchMatch = null, $dbResultRange = null, $sortBy = null, $sortDirection = SORT_DIRECTION_ASC) {
00230 $users = array();
00231
00232 $paramArray = array('interests', (int) $conferenceId);
00233 $searchSql = '';
00234
00235 $searchTypeMap = array(
00236 USER_FIELD_FIRSTNAME => 'u.first_name',
00237 USER_FIELD_LASTNAME => 'u.last_name',
00238 USER_FIELD_USERNAME => 'u.username',
00239 USER_FIELD_EMAIL => 'u.email',
00240 USER_FIELD_INTERESTS => 's.setting_value'
00241 );
00242
00243 if (!empty($search) && isset($searchTypeMap[$searchType])) {
00244 $fieldName = $searchTypeMap[$searchType];
00245 switch ($searchMatch) {
00246 case 'is':
00247 $searchSql = "AND LOWER($fieldName) = LOWER(?)";
00248 $paramArray[] = $search;
00249 break;
00250 case 'contains':
00251 $searchSql = "AND LOWER($fieldName) LIKE LOWER(?)";
00252 $paramArray[] = '%' . $search . '%';
00253 break;
00254 case 'startsWith':
00255 $searchSql = "AND LOWER($fieldName) LIKE LOWER(?)";
00256 $paramArray[] = $search . '%';
00257 break;
00258 }
00259 } elseif (!empty($search)) switch ($searchType) {
00260 case USER_FIELD_USERID:
00261 $searchSql = 'AND u.user_id=?';
00262 $paramArray[] = $search;
00263 break;
00264 case USER_FIELD_INITIAL:
00265 $searchSql = 'AND LOWER(u.last_name) LIKE LOWER(?)';
00266 $paramArray[] = $search . '%';
00267 break;
00268 }
00269
00270 $searchSql .= ($sortBy?(' ORDER BY ' . $this->getSortMapping($sortBy) . ' ' . $this->getDirectionMapping($sortDirection)) : '');
00271
00272 $result =& $this->retrieveRange(
00273
00274 'SELECT DISTINCT u.* FROM users AS u LEFT JOIN user_settings s ON (u.user_id = s.user_id AND s.setting_name = ?), roles AS r WHERE u.user_id = r.user_id AND r.conference_id = ? ' . $searchSql,
00275 $paramArray,
00276 $dbResultRange
00277 );
00278
00279 $returner = new DAOResultFactory($result, $this->userDao, '_returnUserFromRowWithData');
00280 return $returner;
00281 }
00282
00292 function &getUsersBySchedConfId($schedConfId, $searchType = null, $search = null, $searchMatch = null, $dbResultRange = null, $sortBy = null, $sortDirection = SORT_DIRECTION_ASC) {
00293 $users = array();
00294
00295 $paramArray = array('interests', (int) $schedConfId);
00296 $searchSql = '';
00297
00298 if (!empty($search)) switch ($searchType) {
00299 case USER_FIELD_USERID:
00300 $searchSql = 'AND u.user_id=?';
00301 $paramArray[] = $search;
00302 break;
00303 case USER_FIELD_FIRSTNAME:
00304 $searchSql = 'AND LOWER(u.first_name) ' . ($searchMatch=='is'?'=':'LIKE') . ' LOWER(?)';
00305 $paramArray[] = ($searchMatch=='is'?$search:'%' . $search . '%');
00306 break;
00307 case USER_FIELD_LASTNAME:
00308 $searchSql = 'AND LOWER(u.last_name) ' . ($searchMatch=='is'?'=':'LIKE') . ' LOWER(?)';
00309 $paramArray[] = ($searchMatch=='is'?$search:'%' . $search . '%');
00310 break;
00311 case USER_FIELD_USERNAME:
00312 $searchSql = 'AND LOWER(u.username) ' . ($searchMatch=='is'?'=':'LIKE') . ' LOWER(?)';
00313 $paramArray[] = ($searchMatch=='is'?$search:'%' . $search . '%');
00314 break;
00315 case USER_FIELD_EMAIL:
00316 $searchSql = 'AND LOWER(u.email) ' . ($searchMatch=='is'?'=':'LIKE') . ' LOWER(?)';
00317 $paramArray[] = ($searchMatch=='is'?$search:'%' . $search . '%');
00318 break;
00319 case USER_FIELD_INTERESTS:
00320 $searchSql = 'AND LOWER(s.setting_value) ' . ($searchMatch=='is'?'=':'LIKE') . ' LOWER(?)';
00321 $paramArray[] = ($searchMatch=='is'?$search:'%' . $search . '%');
00322 break;
00323 case USER_FIELD_INITIAL:
00324 $searchSql = 'AND LOWER(u.last_name) LIKE LOWER(?)';
00325 $paramArray[] = $search . '%';
00326 break;
00327 }
00328
00329 $searchSql .= ($sortBy?(' ORDER BY ' . $this->getSortMapping($sortBy) . ' ' . $this->getDirectionMapping($sortDirection)) : '');
00330
00331 $result =& $this->retrieveRange(
00332
00333 'SELECT DISTINCT u.* FROM users AS u LEFT JOIN user_settings s ON (u.user_id = s.user_id AND s.setting_name = ?), roles AS r WHERE u.user_id = r.user_id AND r.sched_conf_id = ? ' . $searchSql,
00334 $paramArray,
00335 $dbResultRange
00336 );
00337
00338 $returner = new DAOResultFactory($result, $this->userDao, '_returnUserFromRowWithData');
00339 return $returner;
00340 }
00341
00347 function getConferenceUsersCount($conferenceId) {
00348 $userDao =& DAORegistry::getDAO('UserDAO');
00349
00350 $result =& $this->retrieve(
00351 'SELECT COUNT(DISTINCT(user_id)) FROM roles WHERE conference_id = ?',
00352 (int) $conferenceId
00353 );
00354
00355 $returner = $result->fields[0];
00356
00357 $result->Close();
00358 unset($result);
00359
00360 return $returner;
00361 }
00362
00369 function getSchedConfUsersCount($schedConfId, $roleId = null) {
00370 $userDao =& DAORegistry::getDAO('UserDAO');
00371
00372 $params = array((int) $schedConfId);
00373 if ($roleId !== null) $params[] = (int) $roleId;
00374
00375 $result =& $this->retrieve(
00376 'SELECT COUNT(DISTINCT(user_id)) FROM roles WHERE sched_conf_id = ?' . ($roleId === null?'':' AND role_id = ?'),
00377 $params
00378 );
00379
00380 $returner = $result->fields[0];
00381
00382 $result->Close();
00383 unset($result);
00384
00385 return $returner;
00386 }
00387
00393 function &getRolesByConferenceId($conferenceId = null, $roleId = null) {
00394 $params = array();
00395 $conditions = array();
00396 if (isset($conferenceId)) {
00397 $params[] = (int) $conferenceId;
00398 $conditions[] = 'conference_id = ?';
00399 }
00400 if (isset($roleId)) {
00401 $params[] = (int) $roleId;
00402 $conditions[] = 'role_id = ?';
00403 }
00404
00405 $result =& $this->retrieve(
00406 'SELECT * FROM roles' . (empty($conditions) ? '' : ' WHERE ' . join(' AND ', $conditions)),
00407 $params
00408 );
00409
00410 $returner = new DAOResultFactory($result, $this, '_returnRoleFromRow');
00411 return $returner;
00412 }
00413
00418 function deleteRoleByConferenceId($conferenceId) {
00419 return $this->update(
00420 'DELETE FROM roles WHERE conference_id = ?', (int) $conferenceId
00421 );
00422 }
00423
00428 function deleteRoleBySchedConfId($schedConfId) {
00429 return $this->update(
00430 'DELETE FROM roles WHERE sched_conf_id = ?', (int) $schedConfId
00431 );
00432 }
00433
00440 function deleteRoleByUserId($userId, $conferenceId = null, $roleId = null, $schedConfId = null) {
00441
00442 $args = array((int)$userId);
00443 if(isset($conferenceId)) $args[] = (int)$conferenceId;
00444 if(isset($roleId)) $args[] = (int)$roleId;
00445 if(isset($schedConfId)) $args[] = (int)$schedConfId;
00446
00447 return $this->update(
00448 'DELETE FROM roles WHERE user_id = ?' .
00449 (isset($conferenceId) ? ' AND conference_id = ?' : '') .
00450 (isset($roleId) ? ' AND role_id = ?' : '') .
00451 (isset($schedConfId) ? ' AND sched_conf_id = ?' : ''),
00452 (count($args) ? $args : shift($args)));
00453 }
00454
00462 function roleExists($conferenceId, $schedConfId, $userId, $roleId) {
00463 $result =& $this->retrieve(
00464 'SELECT COUNT(*) FROM roles WHERE conference_id = ? AND sched_conf_id = ? AND user_id = ? AND role_id = ?', array((int) $conferenceId, (int)$schedConfId, (int) $userId, (int) $roleId)
00465 );
00466 $returner = isset($result->fields[0]) && $result->fields[0] == 1 ? true : false;
00467
00468 $result->Close();
00469 unset($result);
00470
00471 return $returner;
00472 }
00473
00480 function getRoleName($roleId, $plural = false) {
00481 switch ($roleId) {
00482 case ROLE_ID_SITE_ADMIN:
00483 return 'user.role.siteAdmin' . ($plural ? 's' : '');
00484 case ROLE_ID_CONFERENCE_MANAGER:
00485 return 'user.role.manager' . ($plural ? 's' : '');
00486 case ROLE_ID_DIRECTOR:
00487 return 'user.role.director' . ($plural ? 's' : '');
00488 case ROLE_ID_TRACK_DIRECTOR:
00489 return 'user.role.trackDirector' . ($plural ? 's' : '');
00490 case ROLE_ID_REVIEWER:
00491 return 'user.role.reviewer' . ($plural ? 's' : '');
00492 case ROLE_ID_AUTHOR:
00493 return 'user.role.author' . ($plural ? 's' : '');
00494 case ROLE_ID_READER:
00495 return 'user.role.reader' . ($plural ? 's' : '');
00496 default:
00497 return '';
00498 }
00499 }
00500
00506 function getRolePath($roleId) {
00507 switch ($roleId) {
00508 case ROLE_ID_SITE_ADMIN:
00509 return ROLE_PATH_SITE_ADMIN;
00510 case ROLE_ID_CONFERENCE_MANAGER:
00511 return ROLE_PATH_CONFERENCE_MANAGER;
00512 case ROLE_ID_DIRECTOR:
00513 return ROLE_PATH_DIRECTOR;
00514 case ROLE_ID_TRACK_DIRECTOR:
00515 return ROLE_PATH_TRACK_DIRECTOR;
00516 case ROLE_ID_REVIEWER:
00517 return ROLE_PATH_REVIEWER;
00518 case ROLE_ID_AUTHOR:
00519 return ROLE_PATH_AUTHOR;
00520 case ROLE_ID_READER:
00521 return ROLE_PATH_READER;
00522 default:
00523 return '';
00524 }
00525 }
00526
00532 function getRoleIdFromPath($rolePath) {
00533 switch ($rolePath) {
00534 case ROLE_PATH_SITE_ADMIN:
00535 return ROLE_ID_SITE_ADMIN;
00536 case ROLE_PATH_CONFERENCE_MANAGER:
00537 return ROLE_ID_CONFERENCE_MANAGER;
00538 case ROLE_PATH_DIRECTOR:
00539 return ROLE_ID_DIRECTOR;
00540 case ROLE_PATH_TRACK_DIRECTOR:
00541 return ROLE_ID_TRACK_DIRECTOR;
00542 case ROLE_PATH_REVIEWER:
00543 return ROLE_ID_REVIEWER;
00544 case ROLE_PATH_AUTHOR:
00545 return ROLE_ID_AUTHOR;
00546 case ROLE_PATH_READER:
00547 return ROLE_ID_READER;
00548 default:
00549 return null;
00550 }
00551 }
00552
00558 function getSortMapping($heading) {
00559 switch ($heading) {
00560 case 'username': return 'u.username';
00561 case 'name': return 'u.last_name';
00562 case 'email': return 'u.email';
00563 case 'id': return 'u.user_id';
00564 default: return null;
00565 }
00566 }
00567 }
00568
00569 ?>