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 static function count_assignments_by_class($term, $class)
{
$term_year = Term::getTermYear($term);
$query = "SELECT count(*) from hms_assignment\n JOIN hms_new_application ON hms_assignment.asu_username = hms_new_application.username\n JOIN hms_lottery_application ON hms_new_application.id = hms_lottery_application.id\n WHERE hms_assignment.term = {$term}\n AND hms_assignment.lottery = 1\n AND hms_new_application.term = {$term} ";
if ($class == CLASS_SOPHOMORE) {
$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) {
$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 {
$query .= 'AND application_term <= ' . ($term_year - 2) . '10';
}
}
$result = PHPWS_DB::getOne($query);
if (PEAR::isError($result)) {
PHPWS_Error::log($result);
return false;
} else {
return $result;
}
}
/**
* Returns a string representation of the integer form of a term.
* @param Integer $term
* @param Boolean $concat Whether or not to concatenate the year and term together (can return a array instead).
* @throws InvalidTermException
*/
public static function toString($term, $concat = true)
{
$result = array();
// Grab the year from the entry_term
$result['year'] = Term::getTermYear($term);
// Grab the term from the entry_term
$sem = Term::getTermSem($term);
if ($sem == TERM_SPRING) {
$result['term'] = SPRING;
} else {
if ($sem == TERM_SUMMER1) {
$result['term'] = SUMMER1;
} else {
if ($sem == TERM_SUMMER2) {
$result['term'] = SUMMER2;
} else {
if ($sem == TERM_FALL) {
$result['term'] = FALL;
} else {
PHPWS_Core::initModClass('hms', 'exception/InvalidTermException.php');
throw new InvalidTermException("Bad term: {$term}");
}
}
}
}
if ($concat) {
return $result['term'] . ' ' . $result['year'];
} else {
return $result;
}
}
public function can_live_together()
{
$requestor = strToLower($this->requestor);
$requestee = strToLower($this->requestee);
$term = $this->term;
// Check if the requestor has a confirmed roommate
if (HMS_Roommate::has_confirmed_roommate($requestor, $term)) {
return E_ROOMMATE_ALREADY_CONFIRMED;
}
// Check if the requestee has a confirmed roommate
if (HMS_Roommate::has_confirmed_roommate($requestee, $term)) {
return E_ROOMMATE_REQUESTED_CONFIRMED;
}
// Use SOAP for the rest of the checks
$requestor_info = StudentFactory::getStudentByUsername($requestor, $term);
// Make sure the requestee is actually a user
try {
$requestee_info = StudentFactory::getStudentByUsername($requestee, $term);
} catch (StudentNotFoundException $snfe) {
return E_ROOMMATE_USER_NOINFO;
}
// Make sure we have compatible genders
if ($requestor_info->getGender() != $requestee_info->getGender()) {
return E_ROOMMATE_GENDER_MISMATCH;
}
PHPWS_Core::initModClass('hms', 'HousingApplication.php');
// Make sure the requestee has filled out an application
if (HousingApplication::checkForApplication($requestee, $term) === false) {
return E_ROOMMATE_NO_APPLICATION;
}
// Students can only request a student of the same "type"
// This is based on the application term (because students starting
// in the summer will have different types). The students must have
// the same application term, unless either student's application
// term is a summer session of the same year
/*
if ($requestor_info->getType() != $requestee_info->getType()) {
return E_ROOMMATE_TYPE_MISMATCH;
}*/
$aTerm = $requestor_info->getApplicationTerm();
$aYear = Term::getTermYear($aTerm);
$aSem = Term::getTermSem($aTerm);
$bTerm = $requestee_info->getApplicationTerm();
$bYear = Term::getTermYear($bTerm);
$bSem = Term::getTermSem($bTerm);
// There's a mismatch if the years don't match OR (the years match AND (either student started in the Spring))
// This allows people with summer application terms to request each other, but prevents continuing students from requesting each other
// (even if the one student started in the Spring and has a 'F' student type at the time the request is made)
if ($aYear != $bYear || $aYear == $bYear && ($aSem == TERM_SPRING && $bSem != TERM_SPRING || $bSem == TERM_SPRING && $aSem != TERM_SPRING)) {
return E_ROOMMATE_TYPE_MISMATCH;
}
// Transfer students can only request other transfers - Prevents freshmen from requesting transfers and vice versa
if ($requestor_info->getType() == TYPE_TRANSFER && $requestee_info->getType() != TYPE_TRANSFER || $requestee_info->getType() == TYPE_TRANSFER && $requestor_info->getType() != TYPE_TRANSFER) {
return E_ROOMMATE_TYPE_MISMATCH;
}
/*
// Make sure RLC Applications are compatible
if (!$this->check_rlc_applications()) {
return E_ROOMMATE_RLC_APPLICATION;
}
// If either student is assigned to an RLC, do not allow the request
if (!$this->check_rlc_assignments()) {
return E_ROOMMATE_RLC_ASSIGNMENT;
}
*/
return E_SUCCESS;
}
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;
}
