public function execute() { $db = PdoFactory::getPdoInstance(); $query = "SELECT hms_assignment.banner_id, hms_hall_structure.room_number, hms_hall_structure.hall_name\n FROM hms_assignment\n JOIN hms_hall_structure\n ON hms_assignment.bed_id = hms_hall_structure.bedid\n WHERE\n hms_assignment.term = :term and\n roomid IN (SELECT room_id\n FROM hms_learning_community_assignment\n JOIN hms_learning_community_applications\n ON hms_learning_community_assignment.application_id = hms_learning_community_applications.id\n JOIN hms_assignment\n ON (hms_learning_community_applications.username = hms_assignment.asu_username AND hms_learning_community_applications.term = hms_assignment.term)\n JOIN hms_bed\n ON hms_assignment.bed_id = hms_bed.id\n JOIN hms_room\n ON hms_bed.room_id = hms_room.id\n WHERE\n hms_learning_community_applications.term = :term)\n ORDER BY roomid"; $stmt = $db->prepare($query); $params = array('term' => $this->term); $stmt->execute($params); $queryResult = $stmt->fetchAll(PDO::FETCH_ASSOC); $results = array(); $i = 0; $count = 0; foreach ($queryResult as $result) { $tplVals = array(); $tplVals['BANNER'] = $result['banner_id']; $student = StudentFactory::getStudentByBannerID($result['banner_id'], $this->term); $tplVals['USERNAME'] = $student->getUsername(); $tplVals['FIRST_NAME'] = $student->getFirstName(); $tplVals['LAST_NAME'] = $student->getLastName(); $membership = RlcMembershipFactory::getMembership($student, $this->term); if ($membership) { $tplVals['COMMUNITY'] = $membership->getRlcName(); $count++; } else { $tplVals['COMMUNITY'] = ''; } $tplVals['HALL'] = $result['hall_name']; $tplVals['ROOM'] = $result['room_number']; $results[$i] = $tplVals; $i++; } $this->memberCount = $count; $this->data = $results; }
private function addResponsible($residents) { $respNames = array(); foreach ($residents as $r) { $respNames[$r['studentId']] = $r['name']; } $persistent_id = $this->room->getPersistentId(); $query = "select banner_id, damage_id from hms_room_damage_responsibility as t1 left join hms_room_damage as t2 on t1.damage_id=t2.id\n\twhere t2.room_persistent_id='{$persistent_id}'"; $pdo = PdoFactory::getPdoInstance(); $result = $pdo->query($query, PDO::FETCH_ASSOC); $rows = $result->fetchAll(); $sdamage = array(); foreach ($rows as $i) { if (isset($respNames[$i['banner_id']])) { $sdamage[$i['damage_id']][] = $respNames[$i['banner_id']]; } } foreach ($this->damages as $key => $value) { if (isset($sdamage[$value->id])) { foreach ($sdamage[$value->id] as $name) { $this->damages[$key]->residents[] = array('name' => $name); } } } }
public function save() { $db = PdoFactory::getPdoInstance(); // Begin a new transaction $db->beginTransaction(); if ($this->id == 0) { // Insert for new record $params = array('request_id' => $this->getRequestId(), 'banner_id' => $this->getBannerId(), 'from_bed' => $this->getFromBed(), 'to_bed' => $this->getToBed(), 'hall_pref1' => $this->getHallPref1(), 'hall_pref2' => $this->getHallPref2(), 'cell_phone' => $this->getCellPhone()); $query = "INSERT INTO hms_room_change_participant (id, request_id, banner_id, from_bed, to_bed, hall_pref1, hall_pref2, cell_phone) VALUES (nextval('hms_room_change_participant_seq'), :request_id, :banner_id, :from_bed, :to_bed, :hall_pref1, :hall_pref2, :cell_phone)"; } else { // Update for existing record $params = array('id' => $this->getId(), 'to_bed' => $this->getToBed(), 'hall_pref1' => $this->getHallPref1(), 'hall_pref2' => $this->getHallPref2(), 'cell_phone' => $this->getCellPhone()); $query = "UPDATE hms_room_change_participant SET (to_bed, hall_pref1, hall_pref2, cell_phone) = (:to_bed, :hall_pref1, :hall_pref2, :cell_phone) WHERE id = :id"; } $stmt = $db->prepare($query); $stmt->execute($params); // If this request doesn't have an ID, then save the ID of the row inserted if ($this->id == 0) { $this->id = $db->lastInsertId('hms_room_change_participant_seq'); $this->state->setParticipantId($this->id); } // If state changed, save the new state if ($this->stateChanged()) { $this->state->save(); } // Close the transaction $db->commit(); return true; // will throw an exception on failure, only returns true for backwards compatability }
public function execute() { $pdo = PdoFactory::getPdoInstance(); $sql = "select reason, count(*) from hms_assignment where term = :term group by reason order by reason"; $stmt = $pdo->prepare($sql); $stmt->execute(array('term' => $this->term)); $this->typeCounts = $stmt->fetchAll(PDO::FETCH_ASSOC); }
public function update() { $db = PdoFactory::getPdoInstance(); $query = "UPDATE hms_room_change_participant_state SET effective_until_date = :effectiveUntilDate WHERE participant_id = :participantId AND state_name = :state AND effective_date = :effectiveDate"; $stmt = $db->prepare($query); $params = array('participantId' => $this->getParticipantId(), 'state' => $this->getName(), 'effectiveDate' => $this->getEffectiveDate(), 'effectiveUntilDate' => $this->getEffectiveUntilDate()); $stmt->execute($params); }
public static function getApplication(Student $student, $term) { $db = PdoFactory::getPdoInstance(); $query = "SELECT * FROM hms_learning_community_applications where username = :username and term = :term"; $stmt = $db->prepare($query); $stmt->execute(array('username' => $student->getUsername(), 'term' => $term)); $stmt->setFetchMode(PDO::FETCH_CLASS, 'RlcApplicationRestored'); return $stmt->fetch(); }
public function execute(CommandContext $context) { $term = Term::getSelectedTerm(); $pdo = PdoFactory::getPdoInstance(); $prep = $pdo->prepare("select id, hall_name as title from hms_residence_hall where term=? and is_online=1 order by hall_name"); $prep->execute(array($term)); $halls = $prep->fetchAll(PDO::FETCH_ASSOC); $context->setContent(json_encode($halls)); }
public static function getMembership(Student $student, $term) { $db = PdoFactory::getPdoInstance(); $query = "select hms_learning_community_assignment.* from hms_learning_community_assignment JOIN hms_learning_community_applications ON hms_learning_community_assignment.application_id = hms_learning_community_applications.id where term = :term and username = :username;"; $stmt = $db->prepare($query); $stmt->execute(array('username' => $student->getUsername(), 'term' => $term)); $stmt->setFetchMode(PDO::FETCH_CLASS, 'RlcMembershipRestored'); return $stmt->fetch(); }
public function execute(CommandContext $context) { $newrows = array(); $pdo = PdoFactory::getPdoInstance(); $floor_id = (int) $context->get('floorId'); $query = <<<EOF select\troom.id as room_id, \troom.room_number, \troom.gender_type, \tbed.id as bed_id, \tbed.bedroom_label, \tbed.bed_letter, \tassign.banner_id, \tassign.meal_option, assign.asu_username from \thms_room as room \tfull join \t\thms_bed as bed on room.id=bed.room_id \tfull join \t\thms_assignment as assign on bed.id=assign.bed_id where\troom.floor_id = :floor_id order by room_number asc, bedroom_label, bed_letter; EOF; $prep = $pdo->prepare($query); $prep->execute(array(':floor_id' => $floor_id)); $rows = $prep->fetchAll(PDO::FETCH_ASSOC); if (empty($rows)) { $context->setContent(json_encode(array())); return; } $count = -1; $room_number_track = 0; foreach ($rows as $k => $v) { $gender = HMS_Util::formatGender($v['gender_type']); if ($v['banner_id']) { $student = StudentFactory::getStudentByBannerID($v['banner_id'], Term::getSelectedTerm()); if ($student) { $v['student'] = $student->first_name . ' ' . $student->last_name; } else { $v['student'] = null; } } else { $v['student'] = null; } if ($v['room_number'] != $room_number_track) { $count++; $newrows[$count]['room_number'] = $v['room_number']; $newrows[$count]['gender'] = $gender; $newrows[$count]['beds'][] = $v; $room_number_track = $v['room_number']; } else { $newrows[$count]['beds'][] = $v; } } $context->setContent(json_encode($newrows)); }
public static function getCurrentState(RoomChangeRequest $request) { $db = PdoFactory::getPdoInstance(); $query = "SELECT * FROM hms_room_change_curr_request WHERE id = :requestId"; $stmt = $db->prepare($query); $stmt->execute(array('requestId' => $request->getId())); $result = $stmt->fetch(PDO::FETCH_ASSOC); $className = 'RoomChangeState' . $result['state_name']; return new $className($request, $result['effective_date'], $result['effective_until_date'], $result['committed_by']); }
public static function getParticipantByRequestStudent(RoomChangeRequest $request, Student $student) { $db = PdoFactory::getPdoInstance(); $query = "SELECT * FROM hms_room_change_curr_participant WHERE request_id = :request_id and banner_id = :bannerId"; $stmt = $db->prepare($query); $params = array('request_id' => $request->getId(), 'bannerId' => $student->getBannerId()); $stmt->execute($params); $stmt->setFetchMode(PDO::FETCH_CLASS, 'RoomChangeParticipantRestored'); return $stmt->fetch(); }
public static function getBedByPersistentId($persistentId, $term) { PHPWS_Core::initModClass('hms', 'PdoFactory.php'); PHPWS_Core::initModClass('hms', 'HMS_Bed.php'); $db = PdoFactory::getPdoInstance(); $query = "select * from hms_bed where persistent_id = :persistentId AND term = :term"; $stmt = $db->prepare($query); $params = array('persistentId' => $persistentId, 'term' => $term); $stmt->execute($params); $results = $stmt->fetchAll(PDO::FETCH_CLASS, 'BedRestored'); return $results[0]; }
public function execute() { PHPWS_Core::initModClass('hms', 'HMS_Util.php'); $db = PdoFactory::getPdoInstance(); $query = 'SELECT hms_new_application.username, hms_new_application.banner_id, hms_lottery_application.early_release FROM hms_new_application JOIN hms_lottery_application ON hms_new_application.id = hms_lottery_application.id WHERE (hms_new_application.term = :term AND hms_lottery_application.early_release IS NOT NULL) ORDER BY hms_lottery_application.early_release ASC, hms_new_application.username ASC'; $stmt = $db->prepare($query); $stmt->execute(array('term' => $this->term)); $results = $stmt->fetchAll(PDO::FETCH_ASSOC); foreach ($results as $row) { $this->total++; if ($row['early_release'] == 'transfer') { $row['early_release'] = 'Transferring to another university'; $this->transferTotal++; } else { if ($row['early_release'] == 'grad') { $row['early_release'] = 'Graduating in December'; $this->gradTotal++; } else { if ($row['early_release'] == 'student_teaching') { $row['early_release'] = 'Student Teaching'; $this->teachingTotal++; } else { if ($row['early_release'] == 'internship') { $row['early_release'] = 'Internship'; $this->internTotal++; } else { if ($row['early_release'] == 'withdraw') { $row['early_release'] = 'Withdrawal'; $this->withdrawTotal++; } else { if ($row['early_release'] == 'marriage') { $row['early_release'] = 'Getting Married'; $this->marriageTotal++; } else { if ($row['early_release'] == 'study_abroad') { $row['early_release'] = 'Studying abroad for the Spring'; $this->abroadTotal++; } else { if ($row['early_release'] == 'intl_exchange') { $row['early_release'] = 'International Exchange Ending'; $this->internationalTotal++; } } } } } } } } $row['name'] = StudentFactory::getStudentByBannerId($row['banner_id'], $this->term)->getFullName(); $this->data[] = $row; } }
/** * Returns an associative array of damage type objects. * * @throws DatabaseException * @return Array Associative array of damage types */ public static function getDamageTypeAssoc() { PHPWS_Core::initModClass('hms', 'PdoFactory.php'); $db = PdoFactory::getPdoInstance(); $query = "SELECT * FROM hms_damage_type ORDER BY category ASC, description ASC"; $stmt = $db->prepare($query); $stmt->execute(); $result = $stmt->fetchAll(PDO::FETCH_ASSOC); $resultById = array(); foreach ($result as $row) { $resultById[$row['id']] = $row; } return $resultById; }
public function execute(CommandContext $context) { $term = Term::getCurrentTerm(); $gender = $context->get('gender'); if (!isset($gender)) { echo "Missing gender!"; } $db = PdoFactory::getPdoInstance(); $query = "select bedid, hall_name, room_number\n FROM hms_hall_structure\n LEFT OUTER JOIN hms_assignment ON hms_assignment.bed_id = hms_hall_structure.bedid\n WHERE\n bed_term = :term and\n hms_assignment.bed_id IS NULL and\n (room_gender = :gender OR room_gender = 2) and\n offline = 0 and\n overflow = 0 and\n parlor = 0 and\n ra_roommate = 0 and\n private = 0 and\n reserved = 0 and\n room_change_reserved = 0\n ORDER BY hall_name, room_number"; $stmt = $db->prepare($query); $params = array('term' => $term, 'gender' => $gender); $stmt->execute($params); echo json_encode($stmt->fetchAll(PDO::FETCH_ASSOC)); exit; }
public function execute(CommandContext $context) { $pdo = PdoFactory::getPdoInstance(); $hall_id = (int) $context->get('hallId'); $prep = $pdo->prepare('select id, floor_number, gender_type from hms_floor where residence_hall_id=? and is_online=1 order by floor_number'); $prep->execute(array($hall_id)); $rows = $prep->fetchAll(PDO::FETCH_ASSOC); if (empty($rows)) { return null; } foreach ($rows as $k => $r) { $gender = HMS_Util::formatGender($r['gender_type']); $rows[$k]['title'] = $r['floor_number'] . ' - ' . $gender; } $context->setContent(json_encode($rows)); }
public static function getUsersInRoleForInstance($roleName, $instance) { PHPWS_Core::initModClass('hms', 'PdoFactory.php'); $pdo = PdoFactory::getPdoInstance(); $query = "SELECT user_id FROM hms_user_role JOIN hms_role ON hms_user_role.role = hms_role.id WHERE hms_role.name = :roleName AND class = :className AND instance = :instanceId"; $stmt = $pdo->prepare($query); $params = array('roleName' => $roleName, 'className' => strtolower(get_class($instance)), 'instanceId' => $instance->getId()); $stmt->execute($params); $userIds = $stmt->fetchAll(PDO::FETCH_COLUMN, 'user_id'); if (sizeof($userIds) <= 0) { return null; } $users = array(); foreach ($userIds as $id) { $users[] = new PHPWS_User($id); } return $users; }
public static function getSubjects($mustIncludeId = null) { $db = PdoFactory::getPdoInstance(); $params = array(); $query = 'SELECT * from intern_subject WHERE active = 1'; if (!is_null($mustIncludeId)) { $query .= ' OR id = :mustIncludeId'; $params['mustIncludeId'] = $mustIncludeId; } $query .= ' ORDER BY abbreviation ASC'; $stmt = $db->prepare($query); $stmt->execute($params); $results = $stmt->fetchAll(\PDO::FETCH_ASSOC); foreach ($results as $row) { $subjects[$row['id']] = $row['abbreviation'] . ' - ' . $row['description']; } return $subjects; }
public static function save(RoomDamageResponsibility $resp) { $db = PdoFactory::getPdoInstance(); $id = $resp->getId(); if (isset($id)) { $query = "UPDATE hms_room_damage_responsibility SET (state, amount, assessed_on, assessed_by) = (:state, :amount, :assessedOn, :assessedBy) WHERE id = :id and damage_id = :damageId and banner_id = :bannerId"; $params = array('id' => $resp->getId(), 'damageId' => $resp->getDamageId(), 'bannerId' => $resp->getBannerId(), 'state' => $resp->getState(), 'amount' => $resp->getAmount(), 'assessedBy' => $resp->getAssessedBy(), 'assessedOn' => $resp->getAssessedOn()); } else { // Insert $query = "INSERT INTO hms_room_damage_responsibility (id, damage_id, banner_id, state, amount) VALUES (nextval('hms_room_damage_responsibility_seq'), :damageId, :bannerId, :state, :amount)"; $params = array('damageId' => $resp->getDamageId(), 'bannerId' => $resp->getBannerId(), 'state' => $resp->getState(), 'amount' => $resp->getAmount()); } $stmt = $db->prepare($query); $stmt->execute($params); // Update ID for a new object if (!isset($id)) { $resp->setId($db->lastInsertId('hms_room_damage_responsibility_seq')); } }
public static function getStateHistory(RoomChangeParticipant $participant) { $db = PdoFactory::getPdoInstance(); $query = "SELECT * FROM hms_room_change_participant_state WHERE participant_id = :participantId ORDER BY effective_date ASC"; $stmt = $db->prepare($query); $stmt->execute(array('participantId' => $participant->getId())); $results = $stmt->fetchAll(PDO::FETCH_ASSOC); // If no results, just return here if (sizeof($results) <= 0) { return null; } // Create a ParticipantState object for each result $states = array(); foreach ($results as $row) { $className = 'ParticipantState' . $row['state_name']; $states[] = new $className($participant, $row['effective_date'], $row['effective_until_date'], $row['committed_by']); } return $states; }
public static function save($contract) { $db = PdoFactory::getPdoInstance(); $id = $contract->getId(); if (isset($id)) { $query = "UPDATE hms_contract SET (banner_id, term, envelope_id) = (:bannerId, :term, :envelopeId) WHERE id = :id"; $params = array('bannerId' => $contract->getBannerId(), 'term' => $contract->getTerm(), 'envelopeId' => $contract->getEnvelopeId()); } else { // Insert $query = "INSERT INTO hms_contract (id, banner_id, term, envelope_id) VALUES (nextval('hms_contract_seq'), :bannerId, :term, :envelopeId)"; $params = array('bannerId' => $contract->getBannerId(), 'term' => $contract->getTerm(), 'envelopeId' => $contract->getEnvelopeId()); } //var_dump($params); //var_dump($query);exit; $stmt = $db->prepare($query); $stmt->execute($params); //var_dump($db->errorInfo());exit; // Update ID for a new object if (!isset($id)) { $contract->setId($db->lastInsertId('hms_contract_seq')); } }
/** * Returns the *latest* timestamp of a state change on this, * or on any of its participants */ public function getLastUpdatedTimestamp() { $db = PdoFactory::getPdoInstance(); $query = "select effective_date from hms_room_change_curr_participant where request_id = :requestId order by effective_date DESC LIMIT 1"; $stmt = $db->prepare($query); $stmt->execute(array('requestId' => $this->getId())); $stmt->setFetchMode(PDO::FETCH_ASSOC); $value = $stmt->fetch(); return $value['effective_date']; }
public static function getRequestPendingCheckout(Student $student, $term) { $db = PdoFactory::getPdoInstance(); $query = "SELECT hms_room_change_curr_request.* FROM hms_room_change_curr_request\n JOIN hms_room_change_curr_participant ON hms_room_change_curr_request.id = hms_room_change_curr_participant.request_id\n WHERE\n term = :term AND\n banner_id = :bannerId AND\n hms_room_change_curr_request.state_name = 'Approved' AND\n hms_room_change_curr_participant.state_name = 'InProcess'"; $stmt = $db->prepare($query); $stmt->execute(array('term' => $term, 'bannerId' => $student->getBannerId())); $results = $stmt->fetchAll(PDO::FETCH_CLASS, 'RoomChangeRequestRestored'); // If more than one pending request is found, throw an exception if (sizeof($results) > 1) { throw new InvalidArgumentException('More than one pending room change detected.'); } else { if (sizeof($results) == 0) { return null; } else { return $results[0]; } } }
/** * Main public function for getting student info. * Used by the rest of the "get" public functions * @return SOAP response object * @throws InvalidArgumentException, SOAPException */ public function getStudentProfile($bannerId, $term) { // Sanity checking on the username if (empty($bannerId) || is_null($bannerId) || !isset($bannerId)) { throw new InvalidArgumentException('Bad BannerId.'); } // Sanity checking on the term if (empty($term) || is_null($term) || !isset($term)) { throw new InvalidArgumentException('Bad term'); } $student = new stdClass(); $db = PdoFactory::getPdoInstance(); $query = "SELECT * FROM fake_soap WHERE banner_id = :banner_id"; $stmt = $db->prepare($query); $params = array('banner_id' => $bannerId); $stmt->execute($params); $result = $stmt->fetch(PDO::FETCH_ASSOC); if (empty($result)) { require_once PHPWS_SOURCE_DIR . 'mod/hms/class/exception/StudentNotFoundException.php'; throw new StudentNotFoundException('User not found', 0, $bannerId); } $student = new stdClass(); $student->banner_id = $result['banner_id']; $student->user_name = $result['username']; $student->last_name = $result['last_name']; $student->first_name = $result['first_name']; $student->middle_name = $result['middle_name']; $student->pref_name = $result['pref_name']; $student->dob = $result['dob']; $student->gender = $result['gender']; $student->deposit_date = ''; // unused but present $student->deposit_waived = 'false'; // unused but present $student->confid = 'Y'; $student->international = $result['international']; $student->student_level = $result['student_level']; // U-undergrad, G-Graduat']e $student->app_decision_code = '1*'; $student->honors = $result['honors']; $student->teaching_fellow = $result['teaching_fellow']; $student->watauga_member = $result['watauga_member']; $student->greek = $result['greek']; $student->disabled_pin = false; $student->housing_waiver = $result['housing_waiver']; $student->student_type = $result['student_type']; $student->application_term = $result['application_term']; $student->projected_class = $result['projected_class']; $student->credhrs_completed = $result['credhrs_completed']; $student->credhrs_for_term = $result['credhrs_for_term']; $student->on_campus = 'false'; // unused $address_array = unserialize($result['address']); $address_object_array = array(); foreach ($address_array as $add) { $address_object_array[] = (object) $add; } $phone_array = unserialize($result['phone']); $phone_object_array = array(); foreach ($phone_array as $p) { $phone_object_array[] = (object) $p; } $student->address = $address_object_array; $student->error_num = 0; $student->error_desc = null; $student->phone = $phone_object_array; $this->createDelay(); return $student; }
public static function getAssessedDamagesStudentTotals($term) { $db = PdoFactory::getPdoInstance(); $query = "select banner_id, sum(amount) from hms_room_damage JOIN hms_room_damage_responsibility ON hms_room_damage.id = hms_room_damage_responsibility.damage_id where term = :term and state = 'assessed' group by banner_id"; $params = array('term' => $term); $stmt = $db->prepare($query); $stmt->execute($params); return $stmt->fetchAll(PDO::FETCH_ASSOC); }