public static function saveObject(DbStorable $o) { $vars = $o->extractVars(); $tableName = $o::getTableName(); // Check if the key already exists $query = "SELECT * FROM {$tableName} WHERE id = {$vars['id']}"; $result = \PHPWS_DB::getAll($query); if (count($result) > 0) { $exists = true; } else { $exists = false; } $db = new \PHPWS_DB($o->getTableName()); foreach ($vars as $key => $value) { $db->addValue($key, $value); } if ($exists) { $db->addWhere('id', $vars['id']); $result = $db->update(); } else { $result = $db->insert(false); } if (\PHPWS_Error::logIfError($result)) { throw new \Exception($result->toString()); } }
public function execute() { PHPWS_Core::initModClass('hms', 'HMS_Residence_Hall.php'); if (!isset($this->term) || is_null($this->term)) { throw new InvalidArgumentException('Missing term.'); } // Get all of the residence halls for this term $halls = HMS_Residence_Hall::get_halls($this->term); $hallRow = array(); foreach ($halls as $hall) { $hallName = $hall->hall_name; $maxOccupancy = $hall->get_number_of_online_nonoverflow_beds(); $currOccupancy = $hall->get_number_of_assignees(); $offline = ""; // If the hall is offline, make a note of that if ($hall->is_online == 0) { $offline = '(Offline)'; } $query = "select * from hms_room JOIN (SELECT hms_room.id, count(*) as c FROM hms_residence_hall JOIN hms_floor ON hms_residence_hall.id = hms_floor.residence_hall_id JOIN hms_room ON hms_floor.id = hms_room.floor_id JOIN hms_bed ON hms_room.id = hms_bed.room_id LEFT OUTER JOIN hms_assignment ON hms_bed.id = hms_assignment.bed_id WHERE ( hms_assignment.id IS NULL AND hms_residence_hall.id = '{$hall->id}' AND hms_room.offline = 0 AND hms_room.overflow = 0 AND hms_room.reserved = 0 AND hms_room.private = 0 AND hms_room.parlor = 0 AND hms_room.ra = 0 AND hms_bed.room_change_reserved = 0 AND hms_bed.ra_roommate = 0 AND hms_bed.international_reserved = 0) GROUP BY hms_room.id) as foo ON foo.id = hms_room.id ORDER BY hms_room.room_number"; $results = PHPWS_DB::getAll($query); $maleRoomList = array(); $femaleRoomList = array(); $coedRoomList = array(); // each room on this hall foreach ($results as $room) { $this->totalRooms++; $roomNum = $room['room_number']; // If the room has more than one avaialble bed, note that in the room number if ($room['c'] > 1) { $roomNum .= '(x' . $room['c'] . ')'; } // catagorize it by gender if ($room['gender_type'] == MALE) { $maleRoomList[] = $roomNum; $this->male += $room['c']; $this->totalBeds += $room['c']; } else { if ($room['gender_type'] == FEMALE) { $femaleRoomList[] = $roomNum; $this->female += $room['c']; $this->totalBeds += $room['c']; } else { if ($room['gender_type'] == COED || $room['gender_type'] == AUTO) { $coedRoomList[] = $roomNum; $this->coed += $room['c']; $this->totalBeds += $room['c']; } else { throw new InvalidArgumentException('Bad room gender. Room id: ' . $room['id']); } } } } $hallRow[] = array('hallName' => $hallName . $offline, 'maxOccupancy' => $maxOccupancy, 'currOccupancy' => $currOccupancy, 'maleRooms' => implode(", ", $maleRoomList), 'femaleRooms' => implode(", ", $femaleRoomList), 'coedRooms' => implode(", ", $coedRoomList)); } $this->data = $hallRow; }
public function execute(CommandContext $context) { $this->searchString = $context->get('studentSearchQuery'); // NB: this is the *search term*, not the semester $this->hmsTerm = Term::getCurrentTerm(); // If the search string is empty, just return an empty json array if (!isset($this->searchString) || $this->searchString == '') { echo json_encode(array()); exit; } // Strip any non-alphanumeric characters, escape slashes $this->searchString = pg_escape_string($this->searchString); // Check for a direct banner ID match if (preg_match("/^[0-9]{9}/", $this->searchString)) { // String is all-numeric, probably a Banner ID // If the seach string is exactly 9 digits, then try to find a match $sql = $this->getBannerIdSearchSql(); } else { // Do fancy string matching instead $sql = $this->getFuzzyTextSql(); } // TODO join for only assigned students / applied students in current/future terms // Add a limit on the number of results $sql .= " LIMIT " . self::resultLimit; //test($sql,1); $this->db = new PHPWS_DB('hms_student_autocomplete'); $results = PHPWS_DB::getAll($sql); //test($results,1); if (is_null($results)) { echo json_encode(); exit; } // Log any DB errors and echo an empty result if (PHPWS_Error::logIfError($results)) { echo json_encode(array()); exit; } $resultObjects = array(); foreach ($results as $row) { $obj = new stdClass(); $obj->banner_id = $row['banner_id']; $obj->name = $row['first_name'] . ' ' . $row['last_name']; $obj->username = $row['username']; $resultObjects[] = $obj; } $jsonResult = json_encode($resultObjects); //test($jsonResult,1); echo $jsonResult; exit; // NB: using setContent adds escape characters to quotes in the JSON string... WRONG. //$context->setContent(json_encode($jsonResult)); }
public function execute() { PHPWS_Core::initModClass('hms', 'StudentFactory.php'); $this->data = array(); $query = "SELECT hms_assignment.id, hms_assignment.banner_id, hms_assignment.asu_username, hms_new_application.cell_phone, hms_room.room_number, hms_floor.floor_number, hms_residence_hall.hall_name FROM hms_assignment LEFT JOIN (SELECT username, MAX(term) AS mterm FROM hms_new_application GROUP BY username) AS a ON hms_assignment.asu_username = a.username LEFT JOIN hms_new_application ON a.username = hms_new_application.username AND a.mterm = hms_new_application.term LEFT JOIN hms_bed ON hms_assignment.bed_id = hms_bed.id LEFT JOIN hms_room ON hms_bed.room_id = hms_room.id LEFT JOIN hms_floor ON hms_room.floor_id = hms_floor.id LEFT JOIN hms_residence_hall ON hms_floor.residence_hall_id = hms_residence_hall.id WHERE ( hms_assignment.term = {$this->term}) ORDER BY hms_residence_hall.id ASC"; $results = PHPWS_DB::getAll($query); if (PHPWS_Error::logIfError($results)) { throw new DatabaseException($results->toString()); } foreach ($results as $result) { try { $student = StudentFactory::getStudentByBannerId($result['banner_id'], $this->term); } catch (Exception $e) { $this->data[] = array($result['hall_name'], $result['floor_number'], $result['room_number'], 'ERROR', 'ERROR', 'ERROR', $result['cell_phone'], $result['asu_username'] . "@appstate.edu"); continue; } $this->data[] = array($result['hall_name'], $result['floor_number'], $result['room_number'], $student->getLastName(), $student->getFirstName(), $result['banner_id'], $result['cell_phone'], $result['asu_username'] . "@appstate.edu"); } }
/** * Returns an array of Faculty objects for the given department. * @param Department $department * @return Array List of faculty for requested department. */ public static function getFacultyByDepartmentAssoc(Department $department) { $sql = "SELECT intern_faculty.* FROM intern_faculty JOIN intern_faculty_department ON intern_faculty.id = intern_faculty_department.faculty_id WHERE intern_faculty_department.department_id = {$department->getId()} ORDER BY last_name ASC"; $result = \PHPWS_DB::getAll($sql); return $result; }
/** * boost install file for users * * @author Matthew McNaney <mcnaney at gmail dot com> * @version $Id$ */ function users_install(&$content) { PHPWS_Core::initModClass('users', 'Users.php'); PHPWS_Core::initModClass('users', 'Action.php'); PHPWS_Core::configRequireOnce('users', 'config.php'); if (isset($_REQUEST['module']) && $_REQUEST['module'] == 'branch') { $db = new PHPWS_DB(); PHPWS_Settings::clear(); if (!createLocalAuthScript()) { $content[] = 'Could not create authorization script.'; return false; } Branch::loadHubDB(); $db = new PHPWS_DB('mod_settings'); $db->addWhere('module', 'users'); $db->addWhere('setting_name', 'site_contact'); $db->addColumn('small_char'); $site_contact = $db->select('one'); $db = new PHPWS_DB('users'); $sql = 'select a.password, b.* from user_authorization as a, users as b where b.deity = 1 and a.username = b.username'; $deities = $db->getAll($sql); if (PHPWS_Error::isError($deities)) { PHPWS_Error::log($deities); $content[] = dgettext('users', 'Could not access hub database.'); Branch::restoreBranchDB(); return FALSE; } elseif (empty($deities)) { $content[] = dgettext('users', 'Could not find any hub deities.'); Branch::restoreBranchDB(); return FALSE; } else { Branch::restoreBranchDB(); PHPWS_Settings::set('users', 'site_contact', $site_contact); PHPWS_Settings::save('users'); $auth_db = new PHPWS_DB('user_authorization'); $user_db = new PHPWS_DB('users'); $group_db = new PHPWS_DB('users_groups'); foreach ($deities as $deity) { $auth_db->addValue('username', $deity['username']); $auth_db->addValue('password', $deity['password']); $result = $auth_db->insert(); if (PHPWS_Error::isError($result)) { PHPWS_Error::log($result); $content[] = dgettext('users', 'Unable to copy deity login to branch.'); continue; } unset($deity['password']); $user_db->addValue($deity); $result = $user_db->insert(); if (PHPWS_Error::isError($result)) { PHPWS_Error::log($result); $content[] = dgettext('users', 'Unable to copy deity users to branch.'); Branch::loadBranchDB(); return FALSE; } $group_db->addValue('active', 1); $group_db->addValue('name', $deity['username']); $group_db->addValue('user_id', $result); if (PHPWS_Error::logIfError($group_db->insert())) { $content[] = dgettext('users', 'Unable to copy deity user group to branch.'); Branch::loadBranchDB(); return FALSE; } $group_db->reset(); $auth_db->reset(); $user_db->reset(); } $content[] = dgettext('users', 'Deity users copied to branch.'); } return TRUE; } if (!createLocalAuthScript()) { $content[] = 'Could not create local authorization script.'; return false; } $authorize_id = PHPWS_Settings::get('users', 'local_script'); $user = new PHPWS_User(); $content[] = '<hr />'; return TRUE; }
function loadUserData() { $sql = 'SELECT * FROM ' . $this->getTableName() . ' WHERE id=\'' . $this->_dataId . '\''; $result = PHPWS_DB::getAll($sql); $this->_userData = $result[0]; $this->_position = 0; }
/** * @version $Id$ * @author Adam Morton * @author Steven Levin */ function export($formId = NULL) { if (!isset($formId)) { $message = dgettext('phatform', 'No form ID was passed'); return new PHPWS_Error('phatform', 'export()', $message, 'continue', PHAT_DEBUG_MODE); } $exportDir = PHPWS_HOME_DIR . 'files/phatform/export/'; $path = $exportDir; clearstatcache(); if (!is_dir($path)) { if (is_writeable($exportDir)) { PHPWS_File::makeDir($path); } else { return PHPWS_Error::get(PHATFORM_EXPORT_PATH, 'phatform', 'Export.php::export()'); } } elseif (!is_writeable($path)) { return PHPWS_Error::get(PHATFORM_EXPORT_PATH, 'phatform', 'Export.php::export()'); } $sql = 'SELECT * FROM mod_phatform_form_' . $formId; $result = PHPWS_DB::getAll($sql); if (sizeof($result) > 0) { $data = ''; foreach ($result[0] as $key => $value) { if ($key != 'position') { $data .= $key . "\t"; } } foreach ($result as $entry) { $data .= "\n"; foreach ($entry as $key => $value) { if ($key != 'position') { if ($key == 'updated') { $value = date(PHPWS_DATE_FORMAT . ' ' . PHPWS_TIME_FORMAT, $value); } else { $value = str_replace("\t", " ", $value); $value = str_replace("\r\n", '', $value); $value = str_replace("\n", '', $value); $temp = $value; if (is_array($temp)) { $value = implode(',', $temp); } else { if (preg_match('/^[ao]:\\d+:/', $temp)) { // unserialize data $unsTemp = unserialize($temp); if (is_array($unsTemp)) { $value = implode(',', $unsTemp); } else { $value = $unsTemp; } } } } $data .= "{$value}\t"; } } } } $filename = 'form_' . $formId . '_export.' . time() . '.csv'; $file = fopen($path . $filename, 'w'); fwrite($file, $data); fclose($file); $goCode = 'zip -qmj ' . $path . $filename . '.zip ' . $path . $filename; system($goCode); $filename = $filename . '.zip'; $filepath = 'files/phatform/export/' . $filename; header('Pragma: public'); header('Expires: 0'); header('Cache-Control: must-revalidate, post-check=0, pre-check=0'); header('Last-Modified: ' . gmdate('D, d M Y H:i:s', filemtime($filepath)) . ' GMT'); header('Cache-Control: private', false); header('Content-Type: application/zip'); header('Content-Disposition: attachment; filename="' . $filename . '"'); header('Content-Transfer-Encoding: binary'); header('Content-Length: ' . filesize($filepath)); header('Connection: close'); readfile($filepath); exit; }
/** * * * * */ function setIncomplete() { $sql = 'SELECT count(id) FROM ' . $this->getFormTable() . " WHERE position!='-1'"; $result = PHPWS_DB::getAll($sql); if (PHPWS_Error::isError($result)) { return $result; } $this->_incompleteEntries = $result[0]['count(id)']; }
public static function send_roommate_reminder_emails($term) { PHPWS_Core::initModClass('hms', 'HMS_Bed.php'); PHPWS_Core::initModclass('hms', 'StudentFactory.php'); // Get a list of outstanding roommate requests, send them reminder emails $query = "select hms_lottery_reservation.* FROM hms_lottery_reservation\n LEFT OUTER JOIN (SELECT asu_username FROM hms_assignment WHERE term={$term} AND lottery = 1) as foo ON hms_lottery_reservation.asu_username = foo.asu_username\n WHERE foo.asu_username IS NULL\n AND hms_lottery_reservation.expires_on > " . time(); $result = PHPWS_DB::getAll($query); if (PEAR::isError($result)) { PHPWS_Error::log($result); test($result, 1); } $year = Term::toString($term) . ' - ' . Term::toString(Term::getNextTerm($term)); foreach ($result as $row) { $student = StudentFactory::getStudentByUsername($row['asu_username'], $term); $requestor = StudentFactory::getStudentByUsername($row['requestor'], $term); $bed = new HMS_Bed($row['bed_id']); $hall_room = $bed->where_am_i(); HMS_Email::send_lottery_roommate_reminder($row['asu_username'], $student->getName(), $row['expires_on'], $requestor->getName(), $hall_room, $year); HMS_Activity_Log::log_activity($row['asu_username'], ACTIVITY_LOTTERY_ROOMMATE_REMINDED, 'hms'); } }
/** * Creates a 2 dimensional array of items from the current table. * * This function creates an sql statement based on variables currently set in * this object. The statement is then executed on the current table and it's * result is returned as the list of current items. * * @param boolean $filterGroups Flag whether or not to filter items that are not * associated with a users group * @return mixed A 2-dimentional array of items or FALSE on failure. * @access public * @see getList() */ function getItems($ids = NULL, $filterGroups = FALSE, $everything = FALSE) { if (isset($this->_table)) { $table = $this->_table; } else { $table = $this->_tables[$this->listName]; } /* Make sure the table name is set before continuing */ if (isset($table)) { if (is_array($this->_listColumns[$this->listName])) { if ($everything) { $sql = 'SELECT *'; } else { $sql = 'SELECT id, '; foreach ($this->_listColumns[$this->listName] as $listColumn => $listLabel) { if ($listColumn != 'id') { $sql .= $listColumn . ', '; } } $sql = substr($sql, 0, strlen($sql) - 2); } $sql .= ' FROM ' . $table; } else { $error = new PHPWS_Error('core', 'PHPWS_Manager:getItems()', 'Format error in config file.', 'exit', 1); $error->message(NULL); } } else { $error = new PHPWS_Error('core', 'PHPWS_Manager:getItems()', 'Table not set!', 'exit', 1); $error->message(NULL); } $whereFlag = FALSE; $sort = $this->getSort(); if (isset($sort)) { $sql .= $sort; $whereFlag = TRUE; } if (is_array($ids) && sizeof($ids) > 0) { if ($whereFlag) { $sql .= ' AND ('; } else { $sql .= ' WHERE ('; } foreach ($ids as $id) { $sql .= " id='{$id}' OR "; } $sql = substr($sql, 0, strlen($sql) - 4) . ')'; } $order = $this->getOrder(); if (isset($order)) { $sql .= $order; } /* Set associative mode for db and execute query */ $result = PHPWS_DB::getAll($sql); if ($filterGroups) { $size = sizeof($result); for ($i = 0; $i < $size; $i++) { $groups = unserialize($result[$i]['groups']); if (is_array($groups)) { foreach ($groups as $value) { if (!$_SESSION['OBJ_user']->userInGroup($value)) { unset($result[$i]); } } } } $result = PHPWS_Array::reIndex($result); } /* Return result of query */ return $result; }
private function chooseWinner($class, $gender) { $query = "SELECT * FROM hms_new_application JOIN hms_lottery_application ON hms_new_application.id = hms_lottery_application.id\n LEFT OUTER JOIN (SELECT asu_username FROM hms_assignment WHERE term={$this->term}) as foo ON hms_new_application.username = foo.asu_username\n WHERE foo.asu_username IS NULL\n AND hms_lottery_application.invited_on IS NULL\n AND hms_new_application.term = {$this->term}\n AND hms_lottery_application.magic_winner = 0\n AND hms_lottery_application.special_interest IS NULL\n AND hms_new_application.gender = {$gender}\n AND hms_new_application.username NOT IN (SELECT username FROM hms_learning_community_applications JOIN hms_learning_community_assignment ON hms_learning_community_applications.id = hms_learning_community_assignment.application_id WHERE term = {$this->term} and state IN ('confirmed', 'selfselect-assigned')) "; $term_year = Term::getTermYear($this->term); if ($class == CLASS_SOPHOMORE) { // Choose a rising sophmore (summer 1 thru fall of the previous year, plus spring of the same year) $query .= 'AND (application_term = ' . ($term_year - 1) . '20 '; $query .= 'OR application_term = ' . ($term_year - 1) . '30 '; $query .= 'OR application_term = ' . ($term_year - 1) . '40 '; $query .= 'OR application_term = ' . $term_year . '10'; $query .= ') '; } else { if ($class == CLASS_JUNIOR) { // Choose a rising jr $query .= 'AND (application_term = ' . ($term_year - 2) . '20 '; $query .= 'OR application_term = ' . ($term_year - 2) . '30 '; $query .= 'OR application_term = ' . ($term_year - 2) . '40 '; $query .= 'OR application_term = ' . ($term_year - 1) . '10'; $query .= ') '; } else { // Choose a rising senior or beyond $query .= 'AND application_term <= ' . ($term_year - 2) . '10 '; } } $result = PHPWS_DB::getAll($query); if (PHPWS_Error::logIfError($result)) { throw new DatabaseException($result->toString()); } if (sizeof($result) < 1) { return null; } // Randomly pick a student from result $winningRow = $result[mt_rand(0, sizeof($result) - 1)]; return $winningRow; }
private function fullNameSearch($string) { $sql = $this->getFuzzyTextSql($string); $results = \PHPWS_DB::getAll($sql); $students = array(); foreach ($results as $result) { try { $students[] = $this->studentIdSearch($result['banner_id']); } catch (\Intern\Exception\StudentNotFoundException $e) { // Skip any students that are returned from the database, but don't exist // in the student info web service continue; } } return $students; }