public function count_avail_lottery_rooms($gender, $rlcId = null) { $now = time(); // Calculate the number of non-full male/female rooms in this hall $query = "SELECT DISTINCT COUNT(hms_room.id) FROM hms_room\n JOIN hms_bed ON hms_bed.room_id = hms_room.id\n JOIN hms_floor ON hms_room.floor_id = hms_floor.id\n WHERE (hms_bed.id NOT IN (SELECT bed_id FROM hms_lottery_reservation WHERE term = {$this->term} AND expires_on > {$now})\n AND hms_bed.id NOT IN (SELECT bed_id FROM hms_assignment WHERE term = {$this->term}))\n AND hms_floor.id = {$this->id}\n \t\t\tAND hms_floor.rlc_id IS null\n \t\t\tAND hms_floor.is_online = 1\n AND hms_room.gender_type IN ({$gender}, 3)\n AND hms_room.reserved = 0\n AND hms_room.offline = 0\n AND hms_room.private = 0\n AND hms_room.overflow = 0\n AND hms_room.parlor = 0 "; if ($rlcId != null) { $query .= "AND hms_room.reserved_rlc_id = {$rlcId} "; } else { $query .= "AND hms_room.reserved_rlc_id IS NULL "; } $query .= "AND hms_bed.international_reserved = 0\n AND hms_bed.ra = 0\n AND hms_bed.ra_roommate = 0"; $avail_rooms = PHPWS_DB::getOne($query); if (PHPWS_Error::logIfError($avail_rooms)) { throw new DatabaseException($result->toString()); } return $avail_rooms; }
/** * * @deprecated * * @throws DatabaseException * @return unknown */ public static function getSizeOfOnCampusWaitList() { $term = PHPWS_Settings::get('hms', 'lottery_term'); // Get the list of user names still on the waiting list, sorted by ID (first come, first served) $sql = "SELECT count(*) 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 hms_assignment.term={$term}) as foo ON hms_new_application.username = foo.asu_username\n WHERE foo.asu_username IS NULL\n AND hms_new_application.term = {$term}\n AND special_interest IS NULL\n AND waiting_list_hide = 0"; $count = PHPWS_DB::getOne($sql); if (PHPWS_Error::logIfError($count)) { throw new DatabaseException($count->toString()); } return $count; }
public function count_avail_lottery_beds() { $now = time(); // Count the number of beds which are free in this room $query = "SELECT DISTINCT COUNT(hms_bed.id) FROM hms_bed\n JOIN hms_room ON hms_bed.room_id = hms_room.id\n WHERE (hms_bed.id NOT IN (SELECT bed_id FROM hms_lottery_reservation WHERE term = {$this->term} AND expires_on > {$now})\n AND hms_bed.id NOT IN (SELECT bed_id FROM hms_assignment WHERE term = {$this->term}))\n AND hms_room.id = {$this->id}\n AND hms_room.reserved = 0\n AND hms_room.offline = 0\n AND hms_room.private = 0\n AND hms_room.overflow = 0\n AND hms_room.parlor = 0\n AND hms_bed.international_reserved = 0\n AND hms_bed.ra = 0\n AND hms_bed.ra_roommate = 0"; $avail_rooms = PHPWS_DB::getOne($query); if (PHPWS_Error::logIfError($avail_rooms)) { throw new DatabaseException($result->toString()); } return $avail_rooms; }
/** * Checks whether a given pair are involved in a roommate request already. * * @returns true if so, false if not * * @param a A user to check on * @param b Another user to check on */ public function have_requested_each_other($a, $b, $term) { $ttl = time() - ROOMMATE_REQ_TIMEOUT; $query = "SELECT COUNT(*) FROM hms_roommate WHERE hms_roommate.term = {$term} AND hms_roommate.confirmed = 0 AND hms_roommate.requested_on >= {$ttl} AND ((hms_roommate.requestor ILIKE '{$a}' AND hms_roommate.requestee ILIKE '{$b}') OR (hms_roommate.requestor ILIKE '{$b}' AND hms_roommate.requestee ILIKE '{$a}'))"; $result = PHPWS_DB::getOne($query); if ($result > 1) { // TODO: Log Weird Situation } return $result > 0 ? true : false; }
public static function countRemainingApplicationsByClassGender($term, $class, $gender = null) { $query = "SELECT count(*) 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 hms_assignment.term={$term}) as foo ON hms_new_application.username = foo.asu_username\n WHERE foo.asu_username IS NULL AND hms_lottery_application.invited_on IS NULL\n AND hms_new_application.term = {$term}\n AND special_interest IS NULL\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 = {$term} and state IN ('confirmed', 'selfselect-assigned')) "; if (isset($gender)) { $query .= "AND hms_new_application.gender = {$gender} "; } $term_year = Term::getTermYear($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 '; } } $remainingApplications = PHPWS_DB::getOne($query); if (PHPWS_Error::logIfError($remainingApplications)) { throw new DatabaseException($remainingApplications->toString()); } return $remainingApplications; }