public function execute() { PHPWS_Core::initModClass('hms', 'PdoFactory.php'); $db = PdoFactory::getInstance()->getPdo(); $query = "SELECT requestor, requestee from hms_roommate\n LEFT OUTER JOIN\n (select asu_username, hms_room.id as rmid\n FROM hms_assignment\n JOIN hms_bed ON hms_assignment.bed_id = hms_bed.id\n JOIN hms_room ON hms_bed.room_id = hms_room.id\n WHERE hms_assignment.term = :term)\n as requestor_room_id ON requestor_room_id.asu_username = requestor\n LEFT OUTER JOIN\n (select asu_username, hms_room.id as rmid\n FROM hms_assignment\n JOIN hms_bed ON hms_assignment.bed_id = hms_bed.id\n JOIN hms_room ON hms_bed.room_id = hms_room.id\n WHERE hms_assignment.term = :term)\n AS requestee_room_id ON requestee_room_id.asu_username = requestee\n WHERE hms_roommate.term = :term and confirmed = 1 AND\n requestor_room_id.rmid != requestee_room_id.rmid"; // $query = "SELECT hms_assignment.term, hms_assignment.banner_id, hms_hall_structure.banner_building_code, hms_hall_structure.banner_id // as bed_code, hms_new_application.meal_plan FROM hms_assignment // JOIN hms_hall_structure ON // hms_assignment.bed_id = hms_hall_structure.bedid // LEFT OUTER JOIN hms_new_application ON // (hms_assignment.banner_id = hms_new_application.banner_id // AND hms_assignment.term = hms_new_application.term) // WHERE hms_assignment.term IN (:term) ORDER BY hms_assignment.term"; $stmt = $db->prepare($query); $stmt->execute(array('term' => $this->term)); $queryResult = $stmt->fetchAll(PDO::FETCH_ASSOC); $result = array(); $i = 0; foreach ($queryResult as $row) { $requestee = StudentFactory::getStudentByUsername($row['requestee'], $this->term); $requestor = StudentFactory::getStudentByUsername($row['requestor'], $this->term); $row['requestee_banner'] = $requestee->getBannerId(); $row['requestor_banner'] = $requestor->getBannerId(); $row['requestee_name'] = $requestee->getFullName(); $row['requestor_name'] = $requestor->getFullName(); $result[$i] = $row; $i++; } $this->data = $result; $this->mismatchCount = $i; }
public function execute() { PHPWS_Core::initModClass('hms', 'PdoFactory.php'); $db = PdoFactory::getInstance()->getPdo(); $query = "SELECT hms_assignment.term, hms_assignment.banner_id, hms_hall_structure.banner_building_code, hms_hall_structure.banner_id as bed_code, hms_new_application.meal_plan FROM hms_assignment JOIN hms_hall_structure ON hms_assignment.bed_id = hms_hall_structure.bedid LEFT OUTER JOIN hms_new_application ON (hms_assignment.banner_id = hms_new_application.banner_id AND hms_assignment.term = hms_new_application.term) WHERE hms_assignment.term IN (:term) ORDER BY hms_assignment.term"; $stmt = $db->prepare($query); $stmt->execute(array('term' => $this->term)); $this->rows = $stmt->fetchAll(PDO::FETCH_ASSOC); }
private function getCumulativeCountsByTerm($term) { // If the report is for fall, we really want Summer 1 and Summer 2 applications terms too. // So, build a list of extra application terms we should use. $extraTerms = array(); if (Term::getTermSem($term) == TERM_FALL) { // Compute the Summer 2 term $t = Term::getPrevTerm($term); $extraTerms[] = $t; // Computer the SUmmer 1 term $t = Term::getPrevTerm($t); $extraTerms[] = $t; } // Create the where clause, start by adding the requested term $termClause = "application_term = {$term}"; // Add any extra terms, if any. if (count($extraTerms) > 0) { foreach ($extraTerms as $t) { $termClause .= " OR application_term = {$t}"; } } // Build the query /* Query with human readable dates $query = "select to_char(date_trunc('day',timestamp 'epoch' + created_on * interval '1 second'), 'Mon DD, YYYY') as date, count(created_on) as daily_count, sum(count(created_on)) OVER (ORDER BY to_char(date_trunc('day',timestamp 'epoch' + created_on * interval '1 second'), 'Mon DD, YYYY')) as running_total FROM hms_new_application WHERE term = 201340 AND ($termClause) AND student_type = 'F' AND application_type = 'fall' GROUP BY date ORDER BY date"; */ PHPWS_Core::initModClass('hms', 'PdoFactory.php'); $db = PdoFactory::getInstance()->getPdo(); $query = "SELECT\n date_part('epoch', date_trunc('day',timestamp 'epoch' + created_on * interval '1 second')) as date,\n SUM(COUNT(created_on)) OVER (ORDER BY date_part('epoch', date_trunc('day',timestamp 'epoch' + created_on * interval '1 second'))) as running_total\n FROM hms_new_application\n WHERE term = :term\n AND ({$termClause})\n AND student_type = 'F'\n AND cancelled = 0\n GROUP BY date\n ORDER BY date"; $stmt = $db->prepare($query); $stmt->bindParam(':term', $term); $stmt->execute(); $result = $stmt->fetchAll(PDO::FETCH_ASSOC); return $result; }
private function getCumulativeCountsByTerm($term) { // If the report is for the fall, we want continuing students with // application terms <= the spring term. if (Term::getTermSem($term) == TERM_FALL) { $year = Term::getTermYear($term); $applicationTerm = $year . TERM_SPRING; } else { // For any other term, we want the application term <= the previous term $applicationTerm = Term::getPrevTerm($term); } PHPWS_Core::initModClass('hms', 'PdoFactory.php'); $db = PdoFactory::getInstance()->getPdo(); $query = "SELECT\n date_part('epoch', date_trunc('day',timestamp 'epoch' + cancelled_on * interval '1 second')) as date,\n SUM(COUNT(cancelled_on)) OVER (ORDER BY date_part('epoch', date_trunc('day',timestamp 'epoch' + cancelled_on * interval '1 second'))) as running_total\n FROM hms_new_application\n WHERE term = :term\n and application_term <= {$applicationTerm}\n and cancelled = 1\n and cancelled_reason NOT IN ('offer_made', 'before_assignment')\n GROUP BY date\n ORDER BY date;"; $stmt = $db->prepare($query); $stmt->bindParam(':term', $term); $stmt->execute(); $result = $stmt->fetchAll(PDO::FETCH_ASSOC); return $result; }
public function execute() { PHPWS_Core::initModClass('hms', 'PdoFactory.php'); $db = PdoFactory::getInstance()->getPdo(); $query = 'SELECT hms_assignment.banner_id, hms_assignment.reason, hms_hall_structure.hall_name, hms_hall_structure.room_number, hms_hall_structure.bed_letter FROM hms_assignment JOIN hms_hall_structure ON hms_assignment.bed_id = hms_hall_structure.bedid WHERE hms_assignment.banner_id NOT IN (SELECT hms_assignment.banner_id FROM hms_assignment JOIN hms_bed ON hms_assignment.bed_id = hms_bed.id JOIN hms_checkin ON hms_bed.persistent_id = hms_checkin.bed_persistent_id WHERE hms_assignment.bed_id = hms_checkin.bed_id AND hms_assignment.banner_id = hms_checkin.banner_id AND hms_assignment.term = :term) AND hms_assignment.term = :term'; $stmt = $db->prepare($query); $stmt->execute(array('term' => $this->term)); $results = $stmt->fetchAll(PDO::FETCH_ASSOC); $rows = array(); $i = 0; foreach ($results as $row) { $rowVals = array(); $student = StudentFactory::getStudentByBannerID($row['banner_id'], $this->term); $rowVals['banner_id'] = $row['banner_id']; $rowVals['username'] = $student->getUsername(); $rowVals['name'] = $student->getFullName(); $rowVals['class'] = $student->getClass(); $rowVals['reason'] = constant($row['reason']); $rowVals['hall_name'] = $row['hall_name']; $rowVals['room_number'] = $row['room_number']; $rowVals['bed_letter'] = $row['bed_letter']; $rows[$i] = $rowVals; $i++; } $this->total = $i; $this->data = $rows; }
public function execute() { PHPWS_Core::initModClass('hms', 'PdoFactory.php'); PHPWS_Core::initModClass('hms', 'StudentFactory.php'); $db = PdoFactory::getInstance()->getPdo(); $query = " select hms_assignment.banner_id, hall_name, floor_number, room_number from hms_assignment join hms_bed on hms_assignment.bed_id = hms_bed.id join hms_room on hms_bed.room_id = hms_room.id join hms_floor on hms_room.floor_id = hms_floor.id join hms_residence_hall on hms_floor.residence_hall_id = hms_residence_hall.id where hms_bed.ra = 1 and hms_assignment.term = :term"; $stmt = $db->prepare($query); $stmt->execute(array('term' => $this->term)); $results = $stmt->fetchAll(PDO::FETCH_ASSOC); foreach ($results as $row) { try { $student = StudentFactory::getStudentByBannerId($row['banner_id'], $this->term); $row['first_name'] = $student->getFirstName(); $row['last_name'] = $student->getLastName(); $row['email'] = $student->getUsername(); } catch (StudentNotFoundException $e) { $row['first_name'] = 'NOT FOUND'; $row['last_name'] = 'NOT FOUND'; $row['email'] = 'NOT FOUND'; } $this->rows[] = $row; } }
public function execute() { PHPWS_Core::initModClass('hms', 'PdoFactory.php'); PHPWS_Core::initModClass('hms', 'StudentFactory.php'); $db = PdoFactory::getInstance()->getPdo(); $query = "select hall_name, room_number, hms_checkin.banner_id, to_timestamp(checkout_date), checkout_by from hms_checkin JOIN hms_bed ON (hms_checkin.bed_persistent_id = hms_bed.persistent_id AND hms_checkin.term = hms_bed.term) JOIN hms_room ON hms_bed.room_id = hms_room.id JOIN hms_floor ON hms_room.floor_id = hms_floor.id JOIN hms_residence_hall ON hms_floor.residence_hall_id = hms_residence_hall.id WHERE hms_checkin.term = :term and key_not_returned = 1 ORDER BY checkout_date"; $stmt = $db->prepare($query); $stmt->execute(array('term' => $this->term)); $results = $stmt->fetchAll(PDO::FETCH_ASSOC); foreach ($results as $row) { try { $student = StudentFactory::getStudentByBannerId($row['banner_id'], $this->term); $row['first_name'] = $student->getFirstName(); $row['last_name'] = $student->getLastName(); $row['email'] = $student->getUsername(); } catch (StudentNotFoundException $e) { $row['first_name'] = 'NOT FOUND'; $row['last_name'] = 'NOT FOUND'; $row['email'] = 'NOT FOUND'; } $this->rows[] = $row; } }
public function execute() { PHPWS_Core::initModClass('hms', 'PdoFactory.php'); PHPWS_Core::initModClass('hms', 'StudentFactory.php'); $db = PdoFactory::getInstance()->getPdo(); $query = "select hall_name, room_number, hms_room_damage_responsibility.banner_id, amount, hms_damage_type.description, to_timestamp(CAST(assessed_on as integer)), assessed_by, hms_room_damage.note from hms_room_damage_responsibility JOIN hms_room_damage ON hms_room_damage_responsibility.damage_id = hms_room_damage.id JOIN hms_room ON (hms_room.persistent_id = hms_room_damage.room_persistent_id AND hms_room_damage.term = hms_room.term) JOIN hms_floor ON hms_room.floor_id = hms_floor.id JOIN hms_residence_hall ON hms_floor.residence_hall_id = hms_residence_hall.id JOIN hms_damage_type ON hms_room_damage.damage_type = hms_damage_type.id WHERE hms_room_damage.term = :term and state = 'assessed' and amount != 0 ORDER BY assessed_on, banner_id"; $stmt = $db->prepare($query); $stmt->execute(array('term' => $this->term)); $results = $stmt->fetchAll(PDO::FETCH_ASSOC); foreach ($results as $row) { try { $student = StudentFactory::getStudentByBannerId($row['banner_id'], $this->term); $row['first_name'] = $student->getFirstName(); $row['last_name'] = $student->getLastName(); $row['email'] = $student->getUsername(); } catch (StudentNotFoundException $e) { $row['first_name'] = 'NOT FOUND'; $row['last_name'] = 'NOT FOUND'; $row['email'] = 'NOT FOUND'; } $this->rows[] = $row; } }
/** * Removes all pending requests. THIS DOES WORK SO BE CAREFUL. Used when roommates are confirmed. * Logs each individual removal to cover our butts. */ public static function removeOutstandingRequests($asu_username, $term) { /* $db = new PHPWS_DB('hms_roommate'); $db->addWhere('requestee', $asu_username, 'ILIKE', NULL, 'username_group'); $db->addWhere('requestor', $asu_username, 'ILIKE', 'OR', 'username_group'); $db->setGroupConj('username_group', 'AND'); $db->addWhere('confirmed', 0); $db->addWhere('term', $term); $requests = $db->getObjects('HMS_Roommate'); if (PHPWS_Error::logIfError($requests)) { throw new DatabaseException('Could not remove outstanding requests'); } */ PHPWS_Core::initModClass('hms', 'PdoFactory.php'); $db = PdoFactory::getInstance()->getPdo(); $query = $db->prepare("SELECT * FROM hms_roommate WHERE (requestee ILIKE :user OR requestor ILIKE :user) AND term = :term AND confirmed = 0"); $query->bindParam(':term', $term); $query->bindParam(':user', $asu_username); $query->execute(); $requests = $query->fetchAll(PDO::FETCH_CLASS, "HMS_Roommate"); if ($requests == null) { return true; } PHPWS_Core::initModClass('hms', 'HMS_Activity_Log.php'); foreach ($requests as $request) { HMS_Activity_Log::log_activity($request->requestor, ACTIVITY_AUTO_CANCEL_ROOMMATE_REQ, UserStatus::getUsername(), "{$request->requestee}: Due to confirmed roommate"); HMS_Activity_Log::log_activity($request->requestee, ACTIVITY_AUTO_CANCEL_ROOMMATE_REQ, UserStatus::getUsername(), "{$request->requestor}: Due to confirmed roommate"); $request->delete(); } return true; }