protected function roomSearch($gender = FALSE, $lifestyle = FALSE, $pbuilding = FALSE, $pfloor = FALSE, $proom = FALSE) { $pre_sql = "SELECT hms_room.*, sub.banner_building_code FROM (SELECT hms_room.id, hms_residence_hall.banner_building_code , (count(hms_bed.id)) AS bed_count FROM hms_residence_hall INNER JOIN hms_floor ON hms_residence_hall.id = hms_floor.residence_hall_id INNER JOIN hms_room ON hms_floor.id = hms_room.floor_id INNER JOIN hms_bed ON hms_room.id = hms_bed.room_id LEFT OUTER JOIN hms_assignment ON hms_bed.id = hms_assignment.bed_id WHERE hms_assignment.asu_username IS NULL AND hms_room.offline = 0 AND hms_room.reserved = 0 AND hms_room.ra = 0 AND hms_room.overflow = 0 AND hms_room.private = 0 AND hms_room.parlor = 0 AND hms_room.term = '{$this->term}' AND hms_floor.is_online = 1 AND hms_residence_hall.is_online = 1 "; $post_sql = " GROUP BY hms_residence_hall.banner_building_code, hms_room.id) AS sub INNER JOIN hms_room ON sub.id = hms_room.id WHERE sub.bed_count = 2 ORDER BY random() LIMIT 1"; // Limit to selection $moar = array(); if ($gender !== FALSE) { $moar[] = " hms_room.gender_type = {$gender} "; } if ($lifestyle !== FALSE) { $lf = $lifestyle == 2 ? '=' : '!='; $moar[] = " hms_residence_hall.gender_type {$lf} 2 "; } if ($pbuilding !== FALSE) { $moar[] = " hms_residence_hall.banner_building_code = '{$pbuilding}' "; } if ($pfloor !== FALSE) { $moar[] = " hms_floor.floor_number = '{$pfloor}' "; } if ($proom !== FALSE) { $moar[] = " hms_room.room_number = '{$proom}' "; } // Assemble SQL $sql = $pre_sql . (count($moar) > 0 ? ' AND ' . implode(' AND ', $moar) : '') . $post_sql; $db = new PHPWS_DB(); $db->setSQLQuery($sql); $result = $db->select('row'); if (PHPWS_Error::logIfError($result)) { throw new DatabaseException($result->getMessage()); } if (is_null($result)) { return null; } $room = new HMS_Room(); PHPWS_Core::plugObject($room, $result); return $room; }