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;
}
