/**
* Report lists rooms in each residence hall that are still available, along with
* the available beds in the room. Also, show the number of beds allocated to the
* lotter for each residence hall.
*
*/
public static function reappAvailability()
{
$term = Term::getSelectedTerm();
// Available rooms in each residence hall.
$db = new PHPWS_DB('hms_bed');
$db->addJoin('LEFT', 'hms_bed', 'hms_room', 'room_id', 'id');
$db->addJoin('LEFT', 'hms_room', 'hms_floor', 'floor_id', 'id');
$db->addJoin('LEFT', 'hms_floor', 'hms_residence_hall', 'residence_hall_id', 'id');
//$db->addWhere('hms_bed.ra_bed', 0);
$db->addWhere('hms_room.private', 0);
$db->addWhere('hms_room.overflow', 0);
$db->addWhere('hms_room.reserved', 0);
$db->addWhere('hms_room.offline', 0);
$db->addWhere('hms_bed.term', $term);
$db->addColumn('hms_room.room_number');
$db->addColumn('hms_bed.bed_letter', null, null, True);
$db->addColumn('hms_residence_hall.hall_name');
$db->addGroupBy('hms_residence_hall.hall_name');
$db->addGroupBy('hms_room.room_number');
$db->addOrder('hms_residence_hall.hall_name');
$availRooms = $db->select();
// Allocated beds for lottery.
$db = new PHPWS_DB('hms_bed');
$db->addJoin('LEFT', 'hms_bed', 'hms_room', 'room_id', 'id');
$db->addJoin('LEFT', 'hms_room', 'hms_floor', 'floor_id', 'id');
$db->addJoin('LEFT', 'hms_floor', 'hms_residence_hall', 'residence_hall_id', 'id');
$db->addJoin('RIGHT', 'hms_bed', 'hms_lottery_reservation', 'id', 'bed_id');
$db->addWhere('hms_lottery_reservation.term', $term);
$db->addColumn('hms_residence_hall.hall_name');
$db->addColumn('hms_bed.id', null, null, True);
$db->addGroupBy('hms_residence_hall.hall_name');
$db->setIndexBy('hall_name');
$lotteryBeds = $db->select();
$tpl = new PHPWS_Template('hms');
$tpl->setFile('admin/reports/reapp_availability.tpl');
//
// "The parent row must be parsed after the child rows."
// Preload currHall with first residence hall name
$currHall = $availRooms[0]['hall_name'];
foreach ($availRooms as $row) {
// Change halls, create new block.
if ($currHall != $row['hall_name'] || $currHall == null) {
$tpl->setCurrentBlock('halls');
// Get allocated beds for the residence hall.
$lottCount = isset($lotteryBeds[$currHall]['count']) ? $lotteryBeds[$currHall]['count'] : 0;
$tpl->setData(array('HALL_NAME' => $currHall, 'LOTTERY_BEDS' => $lottCount));
$tpl->parseCurrentBlock();
$currHall = $row['hall_name'];
}
// Add room to residence hall template block.
$tpl->setCurrentBlock('rooms');
$tpl->setData(array('ROOM_NUM' => $row['room_number'], 'BED_COUNT' => $row['count']));
$tpl->parseCurrentBlock();
}
// Get last residence hall. Can't parse parent before child with template class.
$tpl->setCurrentBlock('halls');
$tpl->setData(array('HALL_NAME' => $currHall));
$tpl->parseCurrentBlock();
return $tpl->get();
}
public function execute()
{
PHPWS_Core::initModClass('hms', 'HousingApplicationFactory.php');
PHPWS_Core::initModClass('hms', 'HMS_Assignment.php');
PHPWS_Core::initModClass('hms', 'StudentFactory.php');
$db = new PHPWS_DB('hms_new_application');
$db->addColumn('hms_new_application.*');
$db->addWhere('term', $this->term);
$db->addWhere('cancelled', 0);
$term = Term::getTermSem($this->term);
if ($term == TERM_FALL) {
$db->addJoin('LEFT', 'hms_new_application', 'hms_fall_application', 'id', 'id');
$db->addColumn('hms_fall_application.*');
} else {
if ($term == TERM_SUMMER1 || $term == TERM_SUMMER2) {
$db->addJoin('LEFT', 'hms_new_application', 'hms_summer_application', 'id', 'id');
$db->addColumn('hms_summer_application.*');
}
}
$result = $db->select();
$app = array();
foreach ($result as $app) {
$username = $app['username'];
$bannerId = $app['banner_id'];
$type = $app['student_type'];
$cellPhone = $app['cell_phone'];
$date = date('n/j/Y', $app['created_on']);
$assignment = HMS_Assignment::getAssignmentByBannerId($bannerId, $this->term);
if (!is_null($assignment)) {
$room = $assignment->where_am_i();
} else {
$room = '';
}
$student = StudentFactory::getStudentByBannerId($bannerId, $this->term);
$first = $student->getFirstName();
$middle = $student->getMiddleName();
$last = $student->getLastName();
$gender = $student->getPrintableGender();
$birthday = date("m/d/Y", $student->getDobDateTime()->getTimestamp());
$address = $student->getAddress(NULL);
if ($term == TERM_SPRING || $term == TERM_FALL) {
$lifestyle = $app['lifestyle_option'] == 1 ? 'Single Gender' : 'Co-Ed';
} else {
$lifestyle = $app['room_type'] == 1 ? 'Single Room' : 'Double Room';
}
if (!is_null($address) && $address !== false) {
$this->rows[] = array($username, $bannerId, $first, $middle, $last, $gender, $type, $cellPhone, $room, $date, $address->line1, $address->line2, $address->line3, $address->city, $address->state, $address->zip, $birthday, $lifestyle);
} else {
$this->rows[] = array($username, $bannerId, $first, $middle, $last, '', $type, $cellPhone, $room, $date, '', '', '', '', '', '', $lifestyle);
}
}
}
public function execute()
{
$db = new PHPWS_DB('hms_room');
$db->addColumn('hms_residence_hall.hall_name');
$db->addColumn('hms_floor.floor_number');
$db->addColumn('hms_room.room_number');
$db->addJoin('LEFT', 'hms_room', 'hms_floor', 'floor_id', 'id');
$db->addJoin('LEFT', 'hms_floor', 'hms_residence_hall', 'residence_hall_id', 'id');
$db->addWhere('hms_room.term', $this->term);
$db->addWhere('hms_room.gender_type', COED);
$results = $db->select();
$this->totalCoed = sizeof($results);
$this->rows = $results;
}
public function execute()
{
PHPWS_Core::initModClass('hms', 'HMS_Util.php');
$term = $this->term;
$db = new PHPWS_DB('hms_checkin');
// Join hall structure
$db->addJoin('', 'hms_checkin', 'hms_hall_structure', 'bed_id', 'bedid');
$db->addColumn('hms_checkin.banner_id');
$db->addColumn('hms_checkin.checkin_date');
$db->addColumn('hms_hall_structure.hall_name');
$db->addColumn('hms_hall_structure.room_number');
$db->addWhere('hms_checkin.term', $term);
$db->addWhere('hms_checkin.checkout_date', null, 'IS NULL');
// Sort by hall, then room number
$db->addOrder(array('hms_hall_structure.hall_name ASC', 'hms_hall_structure.room_number ASC'));
$results = $db->select();
if (PHPWS_Error::isError($results)) {
throw new DatabaseException($results->toString());
}
// Post-processing, cleanup, making it pretty
foreach ($results as $row) {
// Updates counts
$this->total++;
$row['checkin_date'] = HMS_Util::get_short_date_time($row['checkin_date']);
// Copy the cleaned up row to the member var for data
$this->data[] = $row;
}
}
public function init()
{
$db = new PHPWS_DB('checkin_staff');
$db->addJoin('left', 'checkin_staff', 'users', 'user_id', 'id');
$db->addColumn('users.display_name');
$db->addColumn('*');
return $db->loadObject($this);
}
public function execute()
{
if (!isset($this->term) || is_null($this->term)) {
throw new InvalidArgumentException('Missing term.');
}
/*****
* Total Beds
*/
$db = new PHPWS_DB('hms_bed');
$db->addJoin('', 'hms_bed', 'hms_room', 'room_id', 'id');
$db->addJoin('', 'hms_room', 'hms_floor', 'floor_id', 'id');
$db->addJoin('', 'hms_floor', 'hms_residence_hall', 'residence_hall_id', 'id');
$db->addWhere('hms_bed.term', $this->term);
$this->totalBedCount = $db->count();
if (PHPWS_Error::logIfError($this->totalBedCount)) {
PHPWS_Core::initModClass('hms', 'exception', 'DatabaseException.php');
throw new DatabaseException($this->totalBedCount->toString());
}
/*******
* Unavailable Beds
*/
$db = new PHPWS_DB('hms_bed');
$db->addColumn('hms_residence_hall.hall_name');
$db->addColumn('hms_bed.*');
$db->addColumn('hms_room.*');
$db->addJoin('', 'hms_bed', 'hms_room', 'room_id', 'id');
$db->addJoin('', 'hms_room', 'hms_floor', 'floor_id', 'id');
$db->addJoin('', 'hms_floor', 'hms_residence_hall', 'residence_hall_id', 'id');
$db->addWhere('hms_room.reserved', 1, null, 'OR', 'foo');
$db->addWhere('hms_room.ra', 1, null, 'OR', 'foo');
$db->addWhere('hms_room.private', 1, null, 'OR', 'foo');
$db->addWhere('hms_room.overflow', 1, null, 'OR', 'foo');
$db->addWhere('hms_room.parlor', 1, null, 'OR', 'foo');
$db->addWhere('hms_room.offline', 1, null, 'OR', 'foo');
$db->addWhere('hms_bed.ra_roommate', 1, null, 'OR', 'foo');
$db->addWhere('hms_bed.international_reserved', 1, null, 'OR', 'foo');
$db->addWhere('hms_bed.term', $this->term);
$db->addOrder(array('hms_residence_hall.hall_name', 'hms_room.room_number', 'bed_letter'));
$this->unavailableBeds = $db->select();
if (PHPWS_Error::logIfError($this->unavailableBeds)) {
PHPWS_Core::initModClass('hms', 'exception/DatabaseException.php');
throw new DatabaseException($this->unavailableBeds->toString());
}
}
public static function getAssignmentsByTermStateType($term, $state, $type)
{
$db = new PHPWS_DB('hms_learning_community_applications');
$db->addColumn('hms_learning_community_applications.*');
$db->addColumn('hms_learning_community_assignment.*');
$db->addJoin('', 'hms_learning_community_applications', 'hms_learning_community_assignment', 'id', 'application_id');
$db->addWhere('term', $term);
$db->addWhere('hms_learning_community_assignment.state', $state);
$db->addWhere('application_type', $type);
return $db->getObjects('HMS_RLC_Assignment');
}
public function execute()
{
PHPWS_Core::initModClass('hms', 'HMS_Util.php');
PHPWS_Core::initModClass('hms', 'HousingApplication.php');
PHPWS_Core::initModClass('hms', 'SpringApplication.php');
PHPWS_Core::initModClass('hms', 'SummerApplication.php');
PHPWS_Core::initModClass('hms', 'FallApplication.php');
PHPWS_Core::initModClass('hms', 'LotteryApplication.php');
PHPWS_Core::initModClass('hms', 'HMS_Roommate.php');
$term = $this->term;
$sem = Term::getTermSem($term);
$db = new PHPWS_DB('hms_new_application');
$db->addColumn('hms_new_application.*');
$applicationClassName = '';
// Join for additional application data based on semester
switch ($sem) {
case TERM_SUMMER1:
case TERM_SUMMER2:
$db->addJoin('', 'hms_new_application', 'hms_summer_application', 'id', 'id');
$db->addColumn('hms_summer_application.*');
$applicationClassName = 'SummerApplication';
//$db->addWhere('application_type', 'summer');
break;
case TERM_FALL:
$db->addJoin('', 'hms_new_application', 'hms_fall_application', 'id', 'id');
$db->addColumn('hms_fall_application.*');
$applicationClassName = 'FallApplication';
//$db->addWhere('application_type', 'fall');
break;
case TERM_SPRING:
$db->addJoin('', 'hms_new_application', 'hms_spring_application', 'id', 'id');
$db->addColumn('hms_spring_application.*');
$applicationClassName = 'SpringApplication';
//$db->addWhere('application_type', 'spring');
break;
default:
// error
throw new InvalidArgumentException('Invalid term specified.');
}
// Limit to the given term
$db->addWhere('hms_new_application.term', $term);
// Join for un-assigned students
$db->addJoin('LEFT OUTER', 'hms_new_application', 'hms_assignment', 'banner_id', 'banner_id AND hms_new_application.term = hms_assignment.term');
$db->addWhere('hms_assignment.banner_id', 'NULL');
// Don't show students who are type 'W' or have cancelled applications
$db->addWhere('hms_new_application.cancelled', 0);
// Sort by gender, then application date (earliest to latest)
$db->addOrder(array('student_type ASC', 'gender ASC', 'created_on ASC'));
$results = $db->getObjects($applicationClassName);
if (PHPWS_Error::isError($results)) {
throw new DatabaseException($results->toString());
}
// Post-processing, cleanup, making it pretty
foreach ($results as $app) {
// Updates counts
$this->total++;
if ($app->getGender() == MALE) {
$this->male++;
} else {
if ($app->getGender() == FEMALE) {
$this->female++;
}
}
// Copy the cleaned up row to the member var for data
$this->data[] = $app->unassignedStudentsFields();
}
}
public function execute()
{
PHPWS_Core::initModClass('hms', 'StudentFactory.php');
$db = new PHPWS_DB('hms_assignment');
$db->addColumn('hms_assignment.banner_id');
$db->addColumn('hms_assignment.reason');
$db->addColumn('hms_residence_hall.hall_name');
$db->addColumn('hms_room.room_number');
$db->addColumn('hms_new_application.cell_phone');
$db->addWhere('hms_assignment.term', $this->term);
$db->addJoin('LEFT OUTER', 'hms_assignment', 'hms_bed', 'bed_id', 'id');
$db->addJoin('LEFT OUTER', 'hms_bed', 'hms_room', 'room_id', 'id');
$db->addJoin('LEFT OUTER', 'hms_room', 'hms_floor', 'floor_id', 'id');
$db->addJoin('LEFT OUTER', 'hms_floor', 'hms_residence_hall', 'residence_hall_id', 'id');
$db->addJoin('LEFT OUTER', 'hms_assignment', 'hms_new_application', 'banner_id', 'banner_id AND hms_assignment.term = hms_new_application.term');
$results = $db->select();
if (PHPWS_Error::logIfError($results)) {
return $results;
}
foreach ($results as $row) {
try {
$student = StudentFactory::getStudentByBannerId($row['banner_id'], $this->term);
$bannerId = $student->getBannerId();
$username = $student->getUsername();
$first = $student->getFirstName();
$middle = $student->getMiddleName();
$last = $student->getLastName();
$type = $student->getType();
$appTerm = $student->getApplicationTerm();
$cellPhone = $row['cell_phone'];
$assignmentType = $row['reason'];
$gender = HMS_Util::formatGender($student->getGender());
$dob = $student->getDob();
$room = $row['hall_name'] . ' ' . $row['room_number'];
$address = $student->getAddress(NULL);
if (!$address || !isset($address) || is_null($address)) {
$line1 = "";
$line2 = "";
$line3 = "";
$city = "";
$state = "";
$zip = "";
} else {
$line1 = $address->line1;
$line2 = $address->line2;
$line3 = $address->line3;
$city = $address->city;
$state = $address->state;
$zip = $address->zip;
}
} catch (StudentNotFoundException $e) {
$bannerId = $row['banner_id'];
$username = '';
$first = '';
$middle = '';
$last = '';
$gender = '';
$dob = '';
$type = '';
$cellPhone = '';
$line1 = '';
$line2 = '';
$line3 = '';
$city = '';
$state = '';
$zip = '';
$appTerm = '';
$assignmentType = '';
$room = '';
}
$this->rows[] = array($username, $bannerId, $first, $middle, $last, $gender, $dob, $type, $appTerm, $cellPhone, $assignmentType, $room, $line1, $line2, $line3, $city, $state, $zip);
}
}
private function getAssignmentsByHall($hallId)
{
$db = new PHPWS_DB('hms_assignment');
$db->addColumn('hms_assignment.banner_id');
// Limit to just the requested hall id
$db->addWhere('hms_residence_hall.id', $hallId);
// Join the assignment all the way up to the hall
$db->addJoin('LEFT OUTER', 'hms_assignment', 'hms_bed', 'bed_id', 'id');
$db->addJoin('LEFT OUTER', 'hms_bed', 'hms_room', 'room_id', 'id');
$db->addJoin('LEFT OUTER', 'hms_room', 'hms_floor', 'floor_id', 'id');
$db->addJoin('LEFT OUTER', 'hms_floor', 'hms_residence_hall', 'residence_hall_id', 'id');
// Don't report on anything that's not online
$db->addWhere('hms_room.offline', 0);
$db->addWhere('hms_floor.is_online', 1);
$db->addWhere('hms_residence_hall.is_online', 1);
$assignments = $db->select();
if (PHPWS_Error::logIfError($assignments)) {
throw new DatabaseException($assignments->toString());
}
return $assignments;
}
public function joinDb(PHPWS_DB &$db)
{
$db->addJoin('left outer', 'analytics_tracker', 'analytics_tracker_piwik', 'id', 'id');
$db->addColumn('analytics_tracker_piwik.piwik_id');
$db->addColumn('analytics_tracker_piwik.piwik_url');
}
public static function check_two_bed_and_empty_by_id($room)
{
$db = new PHPWS_DB('hms_bed');
$db->addJoin('LEFT OUTER', 'hms_bed', 'hms_assignment', 'id', 'bed_id');
$db->addColumn('hms_assignment.id', NULL, 'ass_id');
$db->addWhere('room_id', $room);
$db->addWhere('hms_bed.term', Term::getSelectedTerm());
$result = $db->select('col');
if (PHPWS_Error::logIfError($result)) {
throw new DatabaseException($result->toString());
}
// If not two-bedroom, toss it out
if (count($result) != 2) {
return false;
}
foreach ($result as $r) {
// If anyone is assigned, toss it out
if ($r != NULL) {
return false;
}
}
// Looks like we're good.
return true;
}
public function execute()
{
if (!isset($this->term) || is_null($this->term)) {
throw new InvalidArgumentException('Missing term.');
}
$sem = Term::getTermSem($this->term);
switch ($sem) {
case TERM_FALL:
$db = new PHPWS_DB('hms_fall_application');
$db->addJoin('LEFT OUTER', 'hms_fall_application', 'hms_new_application', 'id', 'id');
break;
case TERM_SPRING:
$db = new PHPWS_DB('hms_spring_application');
$db->addJoin('LEFT OUTER', 'hms_spring_application', 'hms_new_application', 'id', 'id');
break;
case TERM_SUMMER1:
case TERM_SUMMER2:
$db = new PHPWS_DB('hms_summer_application');
$db->addJoin('LEFT OUTER', 'hms_summer_application', 'hms_new_application', 'id', 'id');
break;
}
$db->addColumn('hms_new_application.*');
// Only applications for the selected term
$db->addWhere('hms_new_application.term', $this->term);
// Only non-cancelled applications
$db->addWhere('hms_new_application.cancelled', 0);
$results = $db->select();
if (PHPWS_Error::logIfError($results)) {
throw new DatabaseException($results->toString());
}
$types = array(TYPE_FRESHMEN, TYPE_TRANSFER, TYPE_CONTINUING, TYPE_NONDEGREE);
$genders = array(MALE, FEMALE);
// Initalize the array for totals
foreach ($types as $t) {
foreach ($genders as $g) {
$this->applicationTotals[$t][$g] = 0;
}
}
foreach ($genders as $g) {
$this->cancelledTotals[$g] = 0;
}
// Calculate the sub-totals
foreach ($results as $application) {
// Adjust the student types to count 'readmit' and 'returning' as 'continuing' instead
if ($application['student_type'] == TYPE_READMIT || $application['student_type'] == TYPE_RETURNING) {
$studentType = TYPE_CONTINUING;
} else {
$studentType = $application['student_type'];
}
$this->applicationTotals[$studentType][$application['gender']]++;
}
// Male sub-total
foreach ($types as $type) {
$this->maleTotals[] = $this->applicationTotals[$type][MALE];
$this->maleSubTotal += $this->applicationTotals[$type][MALE];
}
// Female sub-total
foreach ($types as $type) {
$this->femaleTotals[] = $this->applicationTotals[$type][FEMALE];
$this->femaleSubTotal += $this->applicationTotals[$type][FEMALE];
}
// Type sums
foreach ($types as $type) {
$this->typeTotals[$type] = array_sum($this->applicationTotals[$type]);
}
// Sub-total
$this->subTotal = $this->femaleSubTotal + $this->maleSubTotal;
/****
* Count the cancelled applications
*/
$db->resetWhere();
// Only applications for the selected term
$db->addWhere('hms_new_application.term', $this->term);
// Only cancelled applications
$db->addWhere('hms_new_application.cancelled', 1);
$results = $db->select();
if (PHPWS_Error::logIfError($results)) {
throw new DatabaseException($results->toString());
}
foreach ($results as $application) {
$this->cancelledTotals[$application['gender']]++;
}
// Cancelled sub-total
$this->cancelledSubTotal = $this->cancelledTotals[FEMALE] + $this->cancelledTotals[MALE];
// Gender totals
$this->maleGrandTotal = $this->maleSubTotal + $this->cancelledTotals[MALE];
$this->femaleGrandTotal = $this->femaleSubTotal + $this->cancelledTotals[FEMALE];
// Grand total
$this->total = $this->subTotal + $this->cancelledSubTotal;
}
public function joinDb(PHPWS_DB &$db)
{
$db->addJoin('left outer', 'analytics_tracker', 'analytics_tracker_owa', 'id', 'id');
$db->addColumn('analytics_tracker_owa.owa_url');
$db->addColumn('analytics_tracker_owa.owa_site_id');
$db->addColumn('analytics_tracker_owa.owa_track_page_view');
$db->addColumn('analytics_tracker_owa.owa_track_clicks');
$db->addColumn('analytics_tracker_owa.owa_track_domstream');
}
public function joinDb(PHPWS_DB &$db)
{
$db->addJoin('left outer', 'analytics_tracker', 'analytics_tracker_google', 'id', 'id');
$db->addColumn('analytics_tracker_google.account');
}
public function get_number_of_assignees()
{
$db = new PHPWS_DB('hms_assignment');
$db->addJoin('LEFT OUTER', 'hms_assignment', 'hms_bed', 'bed_id', 'id');
$db->addJoin('LEFT OUTER', 'hms_bed', 'hms_room', 'room_id', 'id');
$db->addJoin('LEFT OUTER', 'hms_room', 'hms_floor', 'floor_id', 'id');
$db->addWhere('hms_floor.id', $this->id);
$result = $db->select('count');
if ($result == 0) {
return $result;
}
if (PHPWS_Error::logIfError($result)) {
throw new DatabaseException($result->toString());
}
return $result;
}
public function execute()
{
$semester = Term::getTermSem(Term::getSelectedTerm());
if ($semester != TERM_FALL && $semester != TERM_SPRING) {
throw new InvalidArgumentException('Term must be Fall or Spring.');
}
PHPWS_Core::initModClass('hms', 'FallApplication.php');
PHPWS_Core::initModClass('hms', 'SpringApplication.php');
$table2 = $semester == TERM_FALL ? 'hms_fall_application' : 'hms_spring_application';
/*
* Male Coed total
*/
$db = new PHPWS_DB('hms_new_application');
$db->addJoin('left', 'hms_new_application', $table2, 'id', 'id');
$db->addWhere($table2 . '.lifestyle_option', COED);
$db->addWhere('term', $this->term);
$db->addWhere('gender', MALE);
$db->addWhere('student_type', TYPE_FRESHMEN);
$db->addColumn('id', null, 'total', TRUE);
$result = $db->select('row');
if (PHPWS_Error::logIfError($result)) {
throw new DatabaseException($result->toString());
}
$this->maleCoed = $result['total'];
/*
* Male Single Gender total
*/
$db = new PHPWS_DB('hms_new_application');
$db->addJoin('left', 'hms_new_application', $table2, 'id', 'id');
$db->addWhere($table2 . '.lifestyle_option', COED, '<>');
// <> == '!=';
$db->addWhere('term', $this->term);
$db->addWhere('gender', MALE);
$db->addWhere('student_type', TYPE_FRESHMEN);
$db->addColumn('id', null, 'total', TRUE);
$result = $db->select('row');
if (PHPWS_Error::logIfError($result)) {
throw new DatabaseException($result->toString());
}
$this->maleSingle = $result['total'];
/*
* Female Coed total
*/
$db = new PHPWS_DB('hms_new_application');
$db->addJoin('left', 'hms_new_application', $table2, 'id', 'id');
$db->addWhere($table2 . '.lifestyle_option', COED);
$db->addWhere('term', $this->term);
$db->addWhere('gender', FEMALE);
$db->addWhere('student_type', TYPE_FRESHMEN);
$db->addColumn('id', null, 'total', TRUE);
$result = $db->select('row');
if (PHPWS_Error::logIfError($result)) {
throw new DatabaseException($result->toString());
}
$this->femaleCoed = $result['total'];
/*
* Female Single Gender
*/
$db = new PHPWS_DB('hms_new_application');
$db->addJoin('left', 'hms_new_application', $table2, 'id', 'id');
$db->addWhere($table2 . '.lifestyle_option', COED, '<>');
// <> == '!=';
$db->addWhere('term', $this->term);
$db->addWhere('gender', FEMALE);
$db->addWhere('student_type', TYPE_FRESHMEN);
$db->addColumn('id', null, 'total', TRUE);
$result = $db->select('row');
if (PHPWS_Error::logIfError($result)) {
throw new DatabaseException($result->toString());
}
$this->femaleSingle = $result['total'];
}
/**
* Returns an array of IDs of free beds (which can be auto_assigned)
* Returns FALSE if there are no more free beds
*/
public static function get_all_free_beds($term, $gender, $randomize = FALSE, $banner = FALSE)
{
$db = new PHPWS_DB('hms_bed');
if ($banner) {
$db->addColumn('hms_bed.banner_id');
$db->addColumn('hms_residence_hall.banner_building_code');
}
$db->addColumn('id');
// Only get free beds
$db->addJoin('LEFT OUTER', 'hms_bed', 'hms_assignment', 'id', 'bed_id');
$db->addWhere('hms_assignment.asu_username', NULL);
// Join other tables so we can do the other 'assignable' checks
$db->addJoin('LEFT OUTER', 'hms_bed', 'hms_room', 'room_id', 'id');
$db->addJoin('LEFT OUTER', 'hms_room', 'hms_floor', 'floor_id', 'id');
$db->addJoin('LEFT OUTER', 'hms_floor', 'hms_residence_hall', 'residence_hall_id', 'id');
// Term
$db->addWhere('hms_bed.term', $term);
// Gender
$db->addWhere('hms_room.gender_type', $gender);
// Make sure everything is online
$db->addWhere('hms_room.offline', 0);
$db->addWhere('hms_floor.is_online', 1);
$db->addWhere('hms_residence_hall.is_online', 1);
// Make sure nothing is reserved
$db->addWhere('hms_room.reserved', 0);
// $db->addWhere('hms_room.is_medical', 0);
// Don't get RA beds
$db->addWhere('hms_room.ra', 0);
// Don't get lobbies
$db->addWhere('hms_room.overflow', 0);
// Don't get private rooms
$db->addWhere('hms_room.private', 0);
// Don't get rooms on floors reserved for an RLC
$db->addWhere('hms_floor.rlc_id', NULL);
// Randomize if necessary
if ($randomize) {
$db->addOrder('random');
}
// $db->setTestMode();
if ($banner) {
$result = $db->select();
if (PHPWS_Error::logIfError($result)) {
throw new DatabaseException($result->toString());
}
return $result;
}
$result = $db->select('col');
// In case of an error, log it and return it
if (PHPWS_Error::logIfError($result)) {
throw new DatabaseException($result->toString());
}
// Return FALSE if there were no results
if (sizeof($result) <= 0) {
return FALSE;
}
return $result;
}
public static function whatsnewBlock()
{
if (PHPWS_Settings::get('whatsnew', 'cache_timeout') > 0) {
$cache_key = 'whatsnew_cache_key';
$content = PHPWS_Cache::get($cache_key, PHPWS_Settings::get('whatsnew', 'cache_timeout'));
if (!empty($content)) {
return $content;
}
}
$link = null;
$summary = null;
$date = null;
$module_name = null;
$exclude = unserialize(PHPWS_Settings::get('whatsnew', 'exclude'));
$db = new PHPWS_DB('phpws_key');
$db->addJoin('left', 'phpws_key', 'modules', 'module', 'title');
$db->addWhere('active', 1);
$db->addWhere('restricted', 0);
if ($exclude) {
foreach ($exclude as $module) {
$db->addWhere('module', $module, '!=');
}
}
$db->addOrder('update_date desc');
$db->setLimit(PHPWS_Settings::get('whatsnew', 'qty_items'));
$db->setIndexBy('id');
$db->addColumn('phpws_key.url');
$db->addColumn('phpws_key.title');
$db->addColumn('phpws_key.summary');
$db->addColumn('phpws_key.update_date');
$db->addColumn('modules.title', null, 'module_title');
$db->addColumn('modules.proper_name');
// $db->setTestMode();
$result = $db->select();
$tpl['TITLE'] = PHPWS_Text::parseOutput(PHPWS_Settings::get('whatsnew', 'title'));
$tpl['TEXT'] = PHPWS_Text::parseOutput(PHPWS_Settings::get('whatsnew', 'text'));
if (!PHPWS_Error::logIfError($result) && !empty($result)) {
foreach ($result as $item) {
$link = '<a href="' . $item['url'] . '">' . $item['title'] . '</a>';
if (PHPWS_Settings::get('whatsnew', 'show_summaries')) {
$summary = PHPWS_Text::parseOutput($item['summary']);
}
if (PHPWS_Settings::get('whatsnew', 'show_dates')) {
$date = strftime(WHATSNEW_DATE_FORMAT, $item['update_date']);
}
if (PHPWS_Settings::get('whatsnew', 'show_source_modules')) {
$module_name = dgettext($item['module_title'], PHPWS_Text::parseOutput($item['proper_name']));
}
$tpl['new-items'][] = array('LINK' => $link, 'SUMMARY' => $summary, 'DATE' => $date, 'MODULE_NAME' => $module_name);
}
} else {
$tpl['new-items'][] = array('LINK' => dgettext('whatsnew', 'Sorry, no results'));
}
$content = PHPWS_Template::process($tpl, 'whatsnew', 'block.tpl');
if (PHPWS_Settings::get('whatsnew', 'cache_timeout') > 0 && !Current_User::isLogged() && !Current_User::allow('whatsnew')) {
PHPWS_Cache::save($cache_key, $content);
}
return $content;
}
/**
* Returns the earliest check-in for the given student, in the given hall, which the student
* has not checked out of yet.
* //TODO update for persistent ID
*/
public static function getPendingCheckoutForStudentByHall(Student $student, HMS_Residence_Hall $hall)
{
$db = new PHPWS_DB('hms_checkin');
// Join the hall structure
$db->addJoin('', 'hms_checkin', 'hms_hall_structure', 'bed_id', 'bedid');
$db->addWhere('banner_id', $student->getBannerId());
// Smarter term logic: If it's Spring or Summer 2 then we can also look in the previous term
$term = $hall->getTerm();
$sem = Term::getTermSem($term);
if ($sem == TERM_SPRING || $sem == TERM_SUMMER2) {
$db->addWhere('term', $term, '=', 'OR', 'term_group');
$db->addWhere('term', Term::getPrevTerm($term), '=', 'OR', 'term_group');
} else {
$db->addWhere('term', $term);
}
// Checkin bed ID must be in the request hall
//$db->addWhere('hms_hall_structure.hallid', $hall->getId());
$db->addWhere('checkout_date', null, 'IS NULL');
$db->addOrder(array('hms_checkin.checkin_date ASC'));
// Earliest checkin first
$checkin = new RestoredCheckin();
$result = $db->loadObject($checkin);
if (PHPWS_Error::logIfError($result)) {
throw new DatabaseException($result->toString());
}
if ($checkin->getId() == null) {
return null;
}
return $checkin;
}
/**
* Determines the number of beds per room in a hall. If the count varies for some rooms,
* then return the count that applies to the majority of the rooms.
* @deprecated -- Unused as far as I can tell
*
*/
public function count_beds_per_room()
{
$total = array();
// stores the number of rooms with that many beds
// Get a list of all the rooms in the hall
$rdb = new PHPWS_DB('hms_room');
$rdb->addJoin('LEFT OUTER', 'hms_room', 'hms_floor', 'floor_id', 'id');
$rdb->addJoin('LEFT OUTER', 'hms_floor', 'hms_residence_hall', 'residence_hall_id', 'id');
$rdb->addWhere('hms_residence_hall.id', $this->id);
$result = $rdb->select();
if (PHPWS_Error::logIfError($result)) {
throw new DatabaseException($result->toString());
}
// and for each room get a list of the beds
foreach ($result as $room) {
$db = new PHPWS_DB('hms_bed');
$db->addJoin('LEFT OUTER', 'hms_bed', 'hms_room', 'room_id', 'id');
$db->addWhere('hms_room.id', $room['id']);
$result = $db->select('count');
if (PHPWS_Error::logIfError($result)) {
throw new DatabaseException($result->toString());
}
// and increment the count of the number of rooms with that many
// beds in this hall
if ($result) {
$total[$result] = empty($total[$result]) ? 1 : $total[$result] + 1;
}
}
asort($total);
// Sort the bed totals by the number of rooms that have each total
if (!end($total)) {
// Jump to the end of the array, return false if array is empty
return key($total);
// return the last key (the greatest number of beds)
} else {
return null;
// There aren't any beds, so we can't find the max
}
}
public function execute()
{
PHPWS_Core::initModClass('hms', 'HMS_Util.php');
PHPWS_Core::initModClass('hms', 'HousingApplication.php');
PHPWS_Core::initModClass('hms', 'SpringApplication.php');
PHPWS_Core::initModClass('hms', 'SummerApplication.php');
PHPWS_Core::initModClass('hms', 'FallApplication.php');
PHPWS_Core::initModClass('hms', 'HMS_Roommate.php');
$term = $this->term;
$sem = Term::getTermSem($term);
// List of student 'application terms' which we'll consider as 'Freshmen' for term we're looking at
// E.g. Students with an applicationt erm in Summer 1, Summer 2, and Fall all count as Freshmen for Fall.
$applicationTerms = array();
$db = new PHPWS_DB('hms_new_application');
$db->addColumn('hms_new_application.banner_id');
$db->addColumn('hms_new_application.username');
$db->addColumn('hms_new_application.term');
$db->addColumn('hms_new_application.gender');
$db->addColumn('hms_new_application.application_term');
$db->addColumn('hms_new_application.student_type');
$db->addColumn('hms_new_application.cell_phone');
$db->addColumn('hms_new_application.meal_plan');
$db->addColumn('hms_new_application.physical_disability');
$db->addColumn('hms_new_application.psych_disability');
$db->addColumn('hms_new_application.medical_need');
$db->addColumn('hms_new_application.gender_need');
$db->addColumn('hms_new_application.international');
$db->addColumn('hms_new_application.created_on');
// Join for additional application data based on semester
switch ($sem) {
case TERM_SUMMER1:
case TERM_SUMMER2:
$db->addJoin('', 'hms_new_application', 'hms_summer_application', 'id', 'id');
$db->addColumn('hms_summer_application.*');
$applicationTerms[] = $term;
$db->addWhere('application_type', 'summer');
break;
case TERM_FALL:
$db->addJoin('', 'hms_new_application', 'hms_fall_application', 'id', 'id');
$db->addColumn('hms_fall_application.*');
// Add the summer 1 and summe 2 application terms
$summer2 = Term::getPrevTerm($term);
$summer1 = Term::getPrevTerm($summer2);
$applicationTerms[] = $summer1;
$applicationTerms[] = $summer2;
$applicationTerms[] = $term;
$db->addWhere('application_type', 'fall');
break;
case TERM_SPRING:
$db->addJoin('', 'hms_new_application', 'hms_spring_application', 'id', 'id');
$db->addColumn('hms_spring_application.*');
$applicationTerms[] = $term;
$db->addWhere('application_type', 'spring');
break;
default:
// error
throw new InvalidArgumentException('Invalid term specified.');
}
// Join for un-assigned students
$db->addJoin('LEFT OUTER', 'hms_new_application', 'hms_assignment', 'banner_id', 'banner_id AND hms_new_application.term = hms_assignment.term');
$db->addWhere('hms_assignment.banner_id', 'NULL');
$db->addWhere('hms_new_application.term', $term);
$db->addWhere('hms_new_application.student_type', 'F');
// Don't show students who have cancelled applications
$db->addWhere('hms_new_application.cancelled', 0);
// Limit by application term
foreach ($applicationTerms as $t) {
$db->addWhere('application_term', $t, '=', 'OR', 'app_term_group');
}
// Sort by gender, then application date (earliest to latest)
$db->addOrder(array('gender ASC', 'created_on ASC'));
$results = $db->select();
if (PHPWS_Error::isError($results)) {
throw new DatabaseException($results->toString());
}
// Post-processing, cleanup, making it pretty
foreach ($results as $row) {
// Updates counts
$this->total++;
if ($row['gender'] == MALE) {
$this->male++;
} else {
if ($row['gender'] == FEMALE) {
$this->female++;
}
}
$row['application_term'] = Term::toString($row['application_term']);
$row['gender'] = HMS_Util::formatGender($row['gender']);
$row['created_on'] = HMS_Util::get_short_date_time($row['created_on']);
$row['meal_plan'] = HMS_Util::formatMealOption($row['meal_plan']);
$row['lifestyle_option'] = HMS_Util::formatLifestyle($row['lifestyle_option']);
$row['room_condition'] = HMS_Util::formatRoomCondition($row['room_condition']);
$row['preferred_bedtime'] = HMS_Util::formatBedtime($row['preferred_bedtime']);
// Roommates
$roommie = HMS_Roommate::get_confirmed_roommate($row['username'], $this->term);
if (!is_null($roommie)) {
$row['roommate'] = $roommie->getUsername();
$row['roommate_banner_id'] = $roommie->getBannerId();
}
// Copy the cleaned up row to the member var for data
$this->data[] = $row;
}
}
/**
* Check to see if an assignment already exists for the specified user. Returns FALSE if no assignment
* exists. If an assignment does exist, a db object containing that row is returned. In the case of a db
* error, a PEAR error object is returned.
* TODO: Deprecate this and/or move to RlcMembershipFactory
* @see RlcMembershipFactory
*
*/
public static function checkForAssignment($username, $term)
{
$db = new PHPWS_DB('hms_learning_community_applications');
$db->addJoin('LEFT OUTER', 'hms_learning_community_assignment', 'hms_learning_community_applications', 'application_id', 'id');
$db->addWhere('hms_learning_community_assignment.id', null, 'IS NOT');
$db->addWhere('hms_learning_community_applications.username', $username, 'ILIKE');
$db->addWhere('hms_learning_community_applications.term', $term);
$db->addColumn('hms_learning_community_assignment.*');
$db->addColumn('hms_learning_community_applications.*');
$result = $db->select('row');
if (PHPWS_Error::logIfError($result)) {
throw new DatabaseException("Could not check for assignment - {$username} {$term} " . $result->toString());
}
if (sizeof($result) > 1) {
return $result;
} else {
return FALSE;
}
}
public function execute()
{
PHPWS_Core::initModClass('hms', 'StudentFactory.php');
PHPWS_Core::initModClass('hms', 'HMS_Residence_Hall.php');
if (!isset($this->term) || is_null($this->term)) {
throw new InvalidArgumentException('Missing term.');
}
// Get all of the residence halls for this term
$halls = ResidenceHallFactory::getHallsForTerm($this->term);
foreach ($halls as $hall) {
$hallName = $hall->hall_name;
$maxOccupancy = $hall->get_number_of_online_nonoverflow_beds();
$currOccupancy = $hall->get_number_of_assignees();
$this->totalCurrOccupancy += $currOccupancy;
$males = 0;
$females = 0;
$coed = 0;
// Get all the assignments for this hall, joined up to the
// room level so we can determine gender, and joined up to the
// hall level so we can limit by hall
$db = new PHPWS_DB('hms_assignment');
$db->addColumn('hms_assignment.*');
$db->addColumn('hms_room.gender_type');
$db->addJoin('LEFT', 'hms_assignment', 'hms_bed', 'bed_id', 'id');
$db->addJoin('LEFT', 'hms_bed', 'hms_room', 'room_id', 'id');
$db->addJoin('LEFT', 'hms_room', 'hms_floor', 'floor_id', 'id');
$db->addJoin('LEFT', 'hms_floor', 'hms_residence_hall', 'residence_hall_id', 'id');
$db->addWhere('hms_assignment.term', $this->term);
$db->addWhere('hms_residence_hall.id', $hall->id);
$results = $db->select();
if (empty($results)) {
continue;
} elseif (PEAR::isError($results)) {
throw new DatabaseException($results->toString());
}
// foreach assignment, tally up the genders
foreach ($results as $assign) {
if ($assign['gender_type'] == MALE) {
$males++;
$this->totalMales++;
} else {
if ($assign['gender_type'] == FEMALE) {
$females++;
$this->totalFemales++;
} else {
if ($assign['gender_type'] == COED) {
$coed++;
$this->totalCoed++;
}
}
}
}
if ($males == 0) {
$malePercent = 0;
} else {
$malePercent = round($males / $currOccupancy * 100, 1);
}
if ($females == 0) {
$femalePercent = 0;
} else {
$femalePercent = round($females / $currOccupancy * 100, 1);
}
if ($coed == 0) {
$coedPercent = 0;
} else {
$coedPercent = round($coed / $currOccupancy * 100, 1);
}
$this->rows[] = array('hallName' => $hallName, 'maxOccupancy' => $maxOccupancy, 'currOccupancy' => $currOccupancy, 'males' => $males, 'malePercent' => $malePercent, 'females' => $females, 'femalePercent' => $femalePercent, 'coed' => $coed, 'coedPercent' => $coedPercent);
}
$this->totalMalePercent = round($this->totalMales / $this->totalCurrOccupancy * 100, 1);
$this->totalFemalePercent = round($this->totalFemales / $this->totalCurrOccupancy * 100, 1);
$this->totalCoedPercent = round($this->totalCoed / $this->totalCurrOccupancy * 100, 1);
}
/**
* Returns applications for all unassigned freshmen.
*
* @param unknown $term
* @param unknown $gender
*
* @throws InvalidTermException
* @throws DatabaseException
* @return mixed
*/
public static function getUnassignedFreshmenApplications($term, $gender)
{
PHPWS_Core::initModClass('hms', 'Term.php');
$db = new PHPWS_DB('hms_new_application');
$db->addWhere('student_type', 'F');
$db->addWhere('term', $term);
$db->addWhere('cancelled', 0);
// $db->addWhere('gender', $gender);
// Add join for extra application fields (sub-class fields)
switch (Term::getTermSem($term)) {
case TERM_SUMMER1:
case TERM_SUMMER2:
PHPWS_Core::initModClass('hms', 'SummerApplication.php');
$db->addJoin('LEFT OUTER', 'hms_new_application', 'hms_summer_application', 'id', 'id');
$db->addColumn('hms_new_application.*');
//TODO addColumns for joined table
$result = $db->getObjects('SummerApplication');
break;
case TERM_FALL:
PHPWS_Core::initModClass('hms', 'FallApplication.php');
$db->addJoin('LEFT OUTER', 'hms_new_application', 'hms_fall_application', 'id', 'id');
// Add columns for joined table
$db->addColumn('hms_new_application.*');
$db->addColumn('hms_fall_application.lifestyle_option');
$db->addColumn('hms_fall_application.preferred_bedtime');
$db->addColumn('hms_fall_application.room_condition');
$db->addColumn('hms_fall_application.smoking_preference');
$result = $db->getObjects('FallApplication');
break;
case TERM_SPRING:
PHPWS_Core::initModClass('hms', 'SpringApplication.php');
$db->addJoin('LEFT OUTER', 'hms_new_application', 'hms_spring_application', 'id', 'id');
$db->addColumn('hms_new_application.*');
//TODO addColumns for joined table
$result = $db->getObjects('SpringApplication');
break;
default:
PHPWS_Core::initModClass('hms', 'exception/InvalidTermException.php');
throw new InvalidTermException($term);
}
if (PHPWS_Error::logIfError($result)) {
throw new DatabaseException($result->toString());
}
// The following is a hack to overcome shortcomings in the Database class. What should happen
// is a left outer join on (SELECT id, asu_username FROM hms_assignment WHERE term=201040)
// where id is null.
$db = new PHPWS_DB('hms_assignment');
$db->addWhere('term', $term);
$db->addColumn('asu_username');
$assignments = $db->select('col');
$newresult = array();
$resultCount = count($result);
for ($count = 0; $count < $resultCount; $count++) {
$app = $result[$count];
if (!in_array($app->username, $assignments)) {
//unset($result[$count]);
$newresult[$app->username] = $app;
}
}
return $newresult;
}
