private function getCumulativeCountsByTerm($term)
{
// If the report is for fall, we really want Summer 1 and Summer 2 applications terms too.
// So, build a list of extra application terms we should use.
$extraTerms = array();
if (Term::getTermSem($term) == TERM_FALL) {
// Compute the Summer 2 term
$t = Term::getPrevTerm($term);
$extraTerms[] = $t;
// Computer the SUmmer 1 term
$t = Term::getPrevTerm($t);
$extraTerms[] = $t;
}
// Create the where clause, start by adding the requested term
$termClause = "application_term = {$term}";
// Add any extra terms, if any.
if (count($extraTerms) > 0) {
foreach ($extraTerms as $t) {
$termClause .= " OR application_term = {$t}";
}
}
// Build the query
/* Query with human readable dates
$query = "select
to_char(date_trunc('day',timestamp 'epoch' + created_on * interval '1 second'), 'Mon DD, YYYY') as date,
count(created_on) as daily_count, sum(count(created_on)) OVER (ORDER BY to_char(date_trunc('day',timestamp 'epoch' + created_on * interval '1 second'), 'Mon DD, YYYY')) as running_total
FROM hms_new_application
WHERE term = 201340
AND ($termClause)
AND student_type = 'F'
AND application_type = 'fall'
GROUP BY date
ORDER BY date";
*/
PHPWS_Core::initModClass('hms', 'PdoFactory.php');
$db = PdoFactory::getInstance()->getPdo();
$query = "SELECT\n date_part('epoch', date_trunc('day',timestamp 'epoch' + created_on * interval '1 second')) as date,\n SUM(COUNT(created_on)) OVER (ORDER BY date_part('epoch', date_trunc('day',timestamp 'epoch' + created_on * interval '1 second'))) as running_total\n FROM hms_new_application\n WHERE term = :term\n AND ({$termClause})\n AND student_type = 'F'\n AND cancelled = 0\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;
}
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 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;
}
}
/**
* 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;
}
public function execute(CommandContext $context)
{
PHPWS_Core::initModClass('hms', 'StudentFactory.php');
PHPWS_Core::initModClass('hms', 'HMS_Learning_Community.php');
PHPWS_Core::initModClass('hms', 'HMS_RLC_Application.php');
PHPWS_Core::initModClass('hms', 'HMS_RLC_Assignment.php');
PHPWS_Core::initModClass('hms', 'HousingApplication.php');
$term = $context->get('term');
$student = StudentFactory::getStudentByUsername(UserStatus::getUsername(), $term);
// Commands for re-directing later
$formCmd = CommandFactory::getCommand('ShowRlcReapplication');
$formCmd->setTerm($term);
// $menuCmd = CommandFactory::getCommand('ShowStudentMenu');
// Pull in data for local use
$rlcOpt = $context->get('rlc_opt');
$rlcChoice1 = $context->get('rlc_choice_1');
$rlcChoice2 = $context->get('rlc_choice_2');
$rlcChoice3 = $context->get('rlc_choice_3');
$why = $context->get('why_this_rlc');
$contribute = $context->get('contribute_gain');
// Change any 'none's into null
if ($rlcChoice2 == 'none') {
$rlcChoice2 = null;
}
if ($rlcChoice3 == 'none') {
$rlcChoice3 = null;
}
# Get the list of RLCs that the student is eligible for
# Note: hard coded to 'C' because we know they're continuing at this point.
# This accounts for freshmen addmitted in the spring, who will still have the 'F' type.
$communities = HMS_Learning_Community::getRlcListReapplication(false, 'C');
# Look up any existing RLC assignment (for the current term, should be the Spring term)
$rlcAssignment = HMS_RLC_Assignment::getAssignmentByUsername($student->getUsername(), Term::getPrevTerm(Term::getCurrentTerm()));
// Sanity checking on user-supplied data
// If the student is already in an RLC, and the student is eligible to reapply for that RLC (RLC always takes returners,
// or the RLC is in the list of communities this student is eligible for), then check to make the user chose something for the re-apply option.
if (!is_null($rlcAssignment) && (array_key_exists($rlcAssignment->getRlcId(), $communities) || $rlcAssignment->getRlc()->getMembersReapply() == 1) && is_null($rlcOpt)) {
NQ::simple('hms', hms\NotificationView::ERROR, 'Please choose whether you would like to continue in your currnet RLC, or apply for a different community.');
$formCmd->redirect();
}
// If the user is 'contining' in his/her current RLC, then figure that out and set it
if (!is_null($rlcOpt) && $rlcOpt == 'continue') {
$rlcChoice1 = $rlcAssignment->getRLC()->get_id();
$rlcChoice2 = NULL;
$rlcChoice3 = NULL;
} else {
// User either can't 'continue' or didn't want to. Check that the user supplied rankings isstead.
// Make sure a first choice was made
if ($rlcChoice1 == 'select') {
NQ::simple('hms', hms\NotificationView::ERROR, 'You must choose a community as your "first choice".');
$formCmd->redirect();
}
if (isset($rlcChoice2) && $rlcChoice1 == $rlcChoice2 || isset($rlcChoice2) && isset($rlcChoice3) && $rlcChoice2 == $rlcChoice3 || isset($rlcChoice3) && $rlcChoice1 == $rlcChoice3) {
NQ::simple('hms', hms\NotificationView::ERROR, 'You cannot choose the same community twice.');
$formCmd->redirect();
}
}
// Check the short answer questions
if (empty($why) || empty($contribute)) {
NQ::simple('hms', hms\NotificationView::ERROR, 'Please respond to both of the short answer questions.');
$formCmd->redirect();
}
$wordLimit = 500;
if (str_word_count($why) > $wordLimit) {
NQ::simple('hms', hms\NotificationView::ERROR, 'Your answer to question number one is too long. Please limit your response to 500 words or less.');
$formCmd->redirect();
}
$wordLimit = 500;
if (str_word_count($contribute) > $wordLimit) {
NQ::simple('hms', hms\NotificationView::ERROR, 'Your answer to question number two is too long. Please limit your response to 500 words or less.');
$formCmd->redirect();
}
$app = new HMS_RLC_Application();
$app->setUsername($student->getUsername());
$app->setFirstChoice($rlcChoice1);
$app->setSecondChoice($rlcChoice2);
$app->setThirdChoice($rlcChoice3);
$app->setWhySpecificCommunities($why);
$app->setStrengthsWeaknesses($contribute);
$_SESSION['RLC_REAPP'] = $app;
// Redirect to the page 2 view command
$page2cmd = CommandFactory::getCommand('ShowRlcReapplicationPageTwo');
$page2cmd->setTerm($term);
$page2cmd->redirect();
}
public function execute(CommandContext $context)
{
PHPWS_Core::initModClass('hms', 'HousingApplication.php');
PHPWS_Core::initModClass('hms', 'StudentFactory.php');
PHPWS_Core::initModClass('hms', 'RlcReapplicationView.php');
PHPWS_Core::initModClass('hms', 'HMS_Learning_Community.php');
PHPWS_Core::initModClass('hms', 'HMS_RLC_Application.php');
PHPWS_Core::initModClass('hms', 'HMS_RLC_Assignment.php');
$errorCmd = CommandFactory::getCommand('ShowStudentMenu');
$term = $context->get('term');
$student = StudentFactory::getStudentByUsername(UserStatus::getUsername(), $term);
// Check deadlines
PHPWS_Core::initModClass('hms', 'ApplicationFeature.php');
$feature = ApplicationFeature::getInstanceByNameAndTerm('RlcReapplication', $term);
if (is_null($feature) || !$feature->isEnabled()) {
NQ::simple('hms', hms\NotificationView::ERROR, "Sorry, RLC re-applications are not avaialable for this term.");
$errorCmd->redirect();
}
if ($feature->getStartDate() > time()) {
NQ::simple('hms', hms\NotificationView::ERROR, "Sorry, it is too soon to submit a RLC re-application.");
$errorCmd->redirect();
} else {
if ($feature->getEndDate() < time()) {
NQ::simple('hms', hms\NotificationView::ERROR, "Sorry, the RLC re-application deadline has already passed. Please contact University Housing if you are interested in applying for a RLC.");
$errorCmd->redirect();
}
}
// Double check the the student is eligible
$housingApp = HousingApplication::getApplicationByUser($student->getUsername(), $term);
if (!$housingApp instanceof LotteryApplication) {
NQ::simple('hms', hms\NotificationView::ERROR, 'You are not eligible to re-apply for a Residential Learning Community.');
$errorCmd->redirect();
}
// Make sure that the student has not already applied for this term
$rlcApp = HMS_RLC_Application::getApplicationByUsername($student->getUsername(), $term);
if (!is_null($rlcApp)) {
NQ::simple('hms', hms\NotificationView::ERROR, 'You have already re-applied for a Residential Learning Community for this term.');
$errorCmd->redirect();
}
// Look up any existing RLC assignment (for the fall term; current term should be the Spring term, so the previous term should be the Fall)
$rlcAssignment = HMS_RLC_Assignment::getAssignmentByUsername($student->getUsername(), Term::getPrevTerm(Term::getCurrentTerm()));
// Get the list of RLCs that the student is eligible for
// Note: hard coded to 'C' because we know they're continuing at this point.
// This accounts for freshmen addmitted in the spring, who will still have the 'F' type.
$communities = HMS_Learning_Community::getRlcListReapplication(false, 'C');
// If the student has an existing assignment, and that community always allows returning students, then make sure the community is in the list (if it's not already)
if (isset($rlcAssignment)) {
// Load the RLC
$rlc = $rlcAssignment->getRlc();
// If members can always reapply, make sure community id exists as an array index
if ($rlc->getMembersReapply() == 1 && !isset($communities[$rlc->get_id()])) {
$communities[$rlc->get_id()] = $rlc->get_community_name();
}
}
session_write_close();
session_start();
if (isset($_SESSION['RLC_REAPP'])) {
$reApp = $_SESSION['RLC_REAPP'];
} else {
$reApp = null;
}
$view = new RlcReapplicationView($student, $term, $rlcAssignment, $communities, $reApp);
$context->setContent($view->show());
}
