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