public function execute()
{
$db = PdoFactory::getPdoInstance();
$query = "SELECT hms_assignment.banner_id, hms_hall_structure.room_number, hms_hall_structure.hall_name\n FROM hms_assignment\n JOIN hms_hall_structure\n ON hms_assignment.bed_id = hms_hall_structure.bedid\n WHERE\n hms_assignment.term = :term and\n roomid IN (SELECT room_id\n FROM hms_learning_community_assignment\n JOIN hms_learning_community_applications\n ON hms_learning_community_assignment.application_id = hms_learning_community_applications.id\n JOIN hms_assignment\n ON (hms_learning_community_applications.username = hms_assignment.asu_username AND hms_learning_community_applications.term = hms_assignment.term)\n JOIN hms_bed\n ON hms_assignment.bed_id = hms_bed.id\n JOIN hms_room\n ON hms_bed.room_id = hms_room.id\n WHERE\n hms_learning_community_applications.term = :term)\n ORDER BY roomid";
$stmt = $db->prepare($query);
$params = array('term' => $this->term);
$stmt->execute($params);
$queryResult = $stmt->fetchAll(PDO::FETCH_ASSOC);
$results = array();
$i = 0;
$count = 0;
foreach ($queryResult as $result) {
$tplVals = array();
$tplVals['BANNER'] = $result['banner_id'];
$student = StudentFactory::getStudentByBannerID($result['banner_id'], $this->term);
$tplVals['USERNAME'] = $student->getUsername();
$tplVals['FIRST_NAME'] = $student->getFirstName();
$tplVals['LAST_NAME'] = $student->getLastName();
$membership = RlcMembershipFactory::getMembership($student, $this->term);
if ($membership) {
$tplVals['COMMUNITY'] = $membership->getRlcName();
$count++;
} else {
$tplVals['COMMUNITY'] = '';
}
$tplVals['HALL'] = $result['hall_name'];
$tplVals['ROOM'] = $result['room_number'];
$results[$i] = $tplVals;
$i++;
}
$this->memberCount = $count;
$this->data = $results;
}
private function addResponsible($residents)
{
$respNames = array();
foreach ($residents as $r) {
$respNames[$r['studentId']] = $r['name'];
}
$persistent_id = $this->room->getPersistentId();
$query = "select banner_id, damage_id from hms_room_damage_responsibility as t1 left join hms_room_damage as t2 on t1.damage_id=t2.id\n\twhere t2.room_persistent_id='{$persistent_id}'";
$pdo = PdoFactory::getPdoInstance();
$result = $pdo->query($query, PDO::FETCH_ASSOC);
$rows = $result->fetchAll();
$sdamage = array();
foreach ($rows as $i) {
if (isset($respNames[$i['banner_id']])) {
$sdamage[$i['damage_id']][] = $respNames[$i['banner_id']];
}
}
foreach ($this->damages as $key => $value) {
if (isset($sdamage[$value->id])) {
foreach ($sdamage[$value->id] as $name) {
$this->damages[$key]->residents[] = array('name' => $name);
}
}
}
}
public function save()
{
$db = PdoFactory::getPdoInstance();
// Begin a new transaction
$db->beginTransaction();
if ($this->id == 0) {
// Insert for new record
$params = array('request_id' => $this->getRequestId(), 'banner_id' => $this->getBannerId(), 'from_bed' => $this->getFromBed(), 'to_bed' => $this->getToBed(), 'hall_pref1' => $this->getHallPref1(), 'hall_pref2' => $this->getHallPref2(), 'cell_phone' => $this->getCellPhone());
$query = "INSERT INTO hms_room_change_participant (id, request_id, banner_id, from_bed, to_bed, hall_pref1, hall_pref2, cell_phone) VALUES (nextval('hms_room_change_participant_seq'), :request_id, :banner_id, :from_bed, :to_bed, :hall_pref1, :hall_pref2, :cell_phone)";
} else {
// Update for existing record
$params = array('id' => $this->getId(), 'to_bed' => $this->getToBed(), 'hall_pref1' => $this->getHallPref1(), 'hall_pref2' => $this->getHallPref2(), 'cell_phone' => $this->getCellPhone());
$query = "UPDATE hms_room_change_participant SET (to_bed, hall_pref1, hall_pref2, cell_phone) = (:to_bed, :hall_pref1, :hall_pref2, :cell_phone) WHERE id = :id";
}
$stmt = $db->prepare($query);
$stmt->execute($params);
// If this request doesn't have an ID, then save the ID of the row inserted
if ($this->id == 0) {
$this->id = $db->lastInsertId('hms_room_change_participant_seq');
$this->state->setParticipantId($this->id);
}
// If state changed, save the new state
if ($this->stateChanged()) {
$this->state->save();
}
// Close the transaction
$db->commit();
return true;
// will throw an exception on failure, only returns true for backwards compatability
}
public function execute()
{
$pdo = PdoFactory::getPdoInstance();
$sql = "select reason, count(*) from hms_assignment where term = :term group by reason order by reason";
$stmt = $pdo->prepare($sql);
$stmt->execute(array('term' => $this->term));
$this->typeCounts = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
public function update()
{
$db = PdoFactory::getPdoInstance();
$query = "UPDATE hms_room_change_participant_state SET effective_until_date = :effectiveUntilDate WHERE participant_id = :participantId AND state_name = :state AND effective_date = :effectiveDate";
$stmt = $db->prepare($query);
$params = array('participantId' => $this->getParticipantId(), 'state' => $this->getName(), 'effectiveDate' => $this->getEffectiveDate(), 'effectiveUntilDate' => $this->getEffectiveUntilDate());
$stmt->execute($params);
}
public static function getApplication(Student $student, $term)
{
$db = PdoFactory::getPdoInstance();
$query = "SELECT * FROM hms_learning_community_applications where username = :username and term = :term";
$stmt = $db->prepare($query);
$stmt->execute(array('username' => $student->getUsername(), 'term' => $term));
$stmt->setFetchMode(PDO::FETCH_CLASS, 'RlcApplicationRestored');
return $stmt->fetch();
}
public function execute(CommandContext $context)
{
$term = Term::getSelectedTerm();
$pdo = PdoFactory::getPdoInstance();
$prep = $pdo->prepare("select id, hall_name as title from hms_residence_hall where term=? and is_online=1 order by hall_name");
$prep->execute(array($term));
$halls = $prep->fetchAll(PDO::FETCH_ASSOC);
$context->setContent(json_encode($halls));
}
public static function getMembership(Student $student, $term)
{
$db = PdoFactory::getPdoInstance();
$query = "select hms_learning_community_assignment.* from hms_learning_community_assignment JOIN hms_learning_community_applications ON hms_learning_community_assignment.application_id = hms_learning_community_applications.id where term = :term and username = :username;";
$stmt = $db->prepare($query);
$stmt->execute(array('username' => $student->getUsername(), 'term' => $term));
$stmt->setFetchMode(PDO::FETCH_CLASS, 'RlcMembershipRestored');
return $stmt->fetch();
}
public function execute(CommandContext $context)
{
$newrows = array();
$pdo = PdoFactory::getPdoInstance();
$floor_id = (int) $context->get('floorId');
$query = <<<EOF
select\troom.id as room_id,
\troom.room_number,
\troom.gender_type,
\tbed.id as bed_id,
\tbed.bedroom_label,
\tbed.bed_letter,
\tassign.banner_id,
\tassign.meal_option,
assign.asu_username
from \thms_room as room
\tfull join
\t\thms_bed as bed on room.id=bed.room_id
\tfull join
\t\thms_assignment as assign on bed.id=assign.bed_id
where\troom.floor_id = :floor_id
order by room_number asc, bedroom_label, bed_letter;
EOF;
$prep = $pdo->prepare($query);
$prep->execute(array(':floor_id' => $floor_id));
$rows = $prep->fetchAll(PDO::FETCH_ASSOC);
if (empty($rows)) {
$context->setContent(json_encode(array()));
return;
}
$count = -1;
$room_number_track = 0;
foreach ($rows as $k => $v) {
$gender = HMS_Util::formatGender($v['gender_type']);
if ($v['banner_id']) {
$student = StudentFactory::getStudentByBannerID($v['banner_id'], Term::getSelectedTerm());
if ($student) {
$v['student'] = $student->first_name . ' ' . $student->last_name;
} else {
$v['student'] = null;
}
} else {
$v['student'] = null;
}
if ($v['room_number'] != $room_number_track) {
$count++;
$newrows[$count]['room_number'] = $v['room_number'];
$newrows[$count]['gender'] = $gender;
$newrows[$count]['beds'][] = $v;
$room_number_track = $v['room_number'];
} else {
$newrows[$count]['beds'][] = $v;
}
}
$context->setContent(json_encode($newrows));
}
public static function getCurrentState(RoomChangeRequest $request)
{
$db = PdoFactory::getPdoInstance();
$query = "SELECT * FROM hms_room_change_curr_request WHERE id = :requestId";
$stmt = $db->prepare($query);
$stmt->execute(array('requestId' => $request->getId()));
$result = $stmt->fetch(PDO::FETCH_ASSOC);
$className = 'RoomChangeState' . $result['state_name'];
return new $className($request, $result['effective_date'], $result['effective_until_date'], $result['committed_by']);
}
public static function getParticipantByRequestStudent(RoomChangeRequest $request, Student $student)
{
$db = PdoFactory::getPdoInstance();
$query = "SELECT * FROM hms_room_change_curr_participant WHERE request_id = :request_id and banner_id = :bannerId";
$stmt = $db->prepare($query);
$params = array('request_id' => $request->getId(), 'bannerId' => $student->getBannerId());
$stmt->execute($params);
$stmt->setFetchMode(PDO::FETCH_CLASS, 'RoomChangeParticipantRestored');
return $stmt->fetch();
}
public static function getBedByPersistentId($persistentId, $term)
{
PHPWS_Core::initModClass('hms', 'PdoFactory.php');
PHPWS_Core::initModClass('hms', 'HMS_Bed.php');
$db = PdoFactory::getPdoInstance();
$query = "select * from hms_bed where persistent_id = :persistentId AND term = :term";
$stmt = $db->prepare($query);
$params = array('persistentId' => $persistentId, 'term' => $term);
$stmt->execute($params);
$results = $stmt->fetchAll(PDO::FETCH_CLASS, 'BedRestored');
return $results[0];
}
public function execute()
{
PHPWS_Core::initModClass('hms', 'HMS_Util.php');
$db = PdoFactory::getPdoInstance();
$query = 'SELECT hms_new_application.username, hms_new_application.banner_id, hms_lottery_application.early_release FROM hms_new_application JOIN hms_lottery_application ON hms_new_application.id = hms_lottery_application.id WHERE (hms_new_application.term = :term AND hms_lottery_application.early_release IS NOT NULL) ORDER BY hms_lottery_application.early_release ASC, hms_new_application.username ASC';
$stmt = $db->prepare($query);
$stmt->execute(array('term' => $this->term));
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($results as $row) {
$this->total++;
if ($row['early_release'] == 'transfer') {
$row['early_release'] = 'Transferring to another university';
$this->transferTotal++;
} else {
if ($row['early_release'] == 'grad') {
$row['early_release'] = 'Graduating in December';
$this->gradTotal++;
} else {
if ($row['early_release'] == 'student_teaching') {
$row['early_release'] = 'Student Teaching';
$this->teachingTotal++;
} else {
if ($row['early_release'] == 'internship') {
$row['early_release'] = 'Internship';
$this->internTotal++;
} else {
if ($row['early_release'] == 'withdraw') {
$row['early_release'] = 'Withdrawal';
$this->withdrawTotal++;
} else {
if ($row['early_release'] == 'marriage') {
$row['early_release'] = 'Getting Married';
$this->marriageTotal++;
} else {
if ($row['early_release'] == 'study_abroad') {
$row['early_release'] = 'Studying abroad for the Spring';
$this->abroadTotal++;
} else {
if ($row['early_release'] == 'intl_exchange') {
$row['early_release'] = 'International Exchange Ending';
$this->internationalTotal++;
}
}
}
}
}
}
}
}
$row['name'] = StudentFactory::getStudentByBannerId($row['banner_id'], $this->term)->getFullName();
$this->data[] = $row;
}
}
/**
* Returns an associative array of damage type objects.
*
* @throws DatabaseException
* @return Array Associative array of damage types
*/
public static function getDamageTypeAssoc()
{
PHPWS_Core::initModClass('hms', 'PdoFactory.php');
$db = PdoFactory::getPdoInstance();
$query = "SELECT * FROM hms_damage_type ORDER BY category ASC, description ASC";
$stmt = $db->prepare($query);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
$resultById = array();
foreach ($result as $row) {
$resultById[$row['id']] = $row;
}
return $resultById;
}
public function execute(CommandContext $context)
{
$term = Term::getCurrentTerm();
$gender = $context->get('gender');
if (!isset($gender)) {
echo "Missing gender!";
}
$db = PdoFactory::getPdoInstance();
$query = "select bedid, hall_name, room_number\n FROM hms_hall_structure\n LEFT OUTER JOIN hms_assignment ON hms_assignment.bed_id = hms_hall_structure.bedid\n WHERE\n bed_term = :term and\n hms_assignment.bed_id IS NULL and\n (room_gender = :gender OR room_gender = 2) and\n offline = 0 and\n overflow = 0 and\n parlor = 0 and\n ra_roommate = 0 and\n private = 0 and\n reserved = 0 and\n room_change_reserved = 0\n ORDER BY hall_name, room_number";
$stmt = $db->prepare($query);
$params = array('term' => $term, 'gender' => $gender);
$stmt->execute($params);
echo json_encode($stmt->fetchAll(PDO::FETCH_ASSOC));
exit;
}
public function execute(CommandContext $context)
{
$pdo = PdoFactory::getPdoInstance();
$hall_id = (int) $context->get('hallId');
$prep = $pdo->prepare('select id, floor_number, gender_type from hms_floor where residence_hall_id=? and is_online=1 order by floor_number');
$prep->execute(array($hall_id));
$rows = $prep->fetchAll(PDO::FETCH_ASSOC);
if (empty($rows)) {
return null;
}
foreach ($rows as $k => $r) {
$gender = HMS_Util::formatGender($r['gender_type']);
$rows[$k]['title'] = $r['floor_number'] . ' - ' . $gender;
}
$context->setContent(json_encode($rows));
}
public static function getUsersInRoleForInstance($roleName, $instance)
{
PHPWS_Core::initModClass('hms', 'PdoFactory.php');
$pdo = PdoFactory::getPdoInstance();
$query = "SELECT user_id FROM hms_user_role JOIN hms_role ON hms_user_role.role = hms_role.id WHERE hms_role.name = :roleName AND class = :className AND instance = :instanceId";
$stmt = $pdo->prepare($query);
$params = array('roleName' => $roleName, 'className' => strtolower(get_class($instance)), 'instanceId' => $instance->getId());
$stmt->execute($params);
$userIds = $stmt->fetchAll(PDO::FETCH_COLUMN, 'user_id');
if (sizeof($userIds) <= 0) {
return null;
}
$users = array();
foreach ($userIds as $id) {
$users[] = new PHPWS_User($id);
}
return $users;
}
public static function getSubjects($mustIncludeId = null)
{
$db = PdoFactory::getPdoInstance();
$params = array();
$query = 'SELECT * from intern_subject WHERE active = 1';
if (!is_null($mustIncludeId)) {
$query .= ' OR id = :mustIncludeId';
$params['mustIncludeId'] = $mustIncludeId;
}
$query .= ' ORDER BY abbreviation ASC';
$stmt = $db->prepare($query);
$stmt->execute($params);
$results = $stmt->fetchAll(\PDO::FETCH_ASSOC);
foreach ($results as $row) {
$subjects[$row['id']] = $row['abbreviation'] . ' - ' . $row['description'];
}
return $subjects;
}
public static function save(RoomDamageResponsibility $resp)
{
$db = PdoFactory::getPdoInstance();
$id = $resp->getId();
if (isset($id)) {
$query = "UPDATE hms_room_damage_responsibility SET (state, amount, assessed_on, assessed_by) = (:state, :amount, :assessedOn, :assessedBy) WHERE id = :id and damage_id = :damageId and banner_id = :bannerId";
$params = array('id' => $resp->getId(), 'damageId' => $resp->getDamageId(), 'bannerId' => $resp->getBannerId(), 'state' => $resp->getState(), 'amount' => $resp->getAmount(), 'assessedBy' => $resp->getAssessedBy(), 'assessedOn' => $resp->getAssessedOn());
} else {
// Insert
$query = "INSERT INTO hms_room_damage_responsibility (id, damage_id, banner_id, state, amount) VALUES (nextval('hms_room_damage_responsibility_seq'), :damageId, :bannerId, :state, :amount)";
$params = array('damageId' => $resp->getDamageId(), 'bannerId' => $resp->getBannerId(), 'state' => $resp->getState(), 'amount' => $resp->getAmount());
}
$stmt = $db->prepare($query);
$stmt->execute($params);
// Update ID for a new object
if (!isset($id)) {
$resp->setId($db->lastInsertId('hms_room_damage_responsibility_seq'));
}
}
public static function getStateHistory(RoomChangeParticipant $participant)
{
$db = PdoFactory::getPdoInstance();
$query = "SELECT * FROM hms_room_change_participant_state WHERE participant_id = :participantId ORDER BY effective_date ASC";
$stmt = $db->prepare($query);
$stmt->execute(array('participantId' => $participant->getId()));
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
// If no results, just return here
if (sizeof($results) <= 0) {
return null;
}
// Create a ParticipantState object for each result
$states = array();
foreach ($results as $row) {
$className = 'ParticipantState' . $row['state_name'];
$states[] = new $className($participant, $row['effective_date'], $row['effective_until_date'], $row['committed_by']);
}
return $states;
}
public static function save($contract)
{
$db = PdoFactory::getPdoInstance();
$id = $contract->getId();
if (isset($id)) {
$query = "UPDATE hms_contract SET (banner_id, term, envelope_id) = (:bannerId, :term, :envelopeId) WHERE id = :id";
$params = array('bannerId' => $contract->getBannerId(), 'term' => $contract->getTerm(), 'envelopeId' => $contract->getEnvelopeId());
} else {
// Insert
$query = "INSERT INTO hms_contract (id, banner_id, term, envelope_id) VALUES (nextval('hms_contract_seq'), :bannerId, :term, :envelopeId)";
$params = array('bannerId' => $contract->getBannerId(), 'term' => $contract->getTerm(), 'envelopeId' => $contract->getEnvelopeId());
}
//var_dump($params);
//var_dump($query);exit;
$stmt = $db->prepare($query);
$stmt->execute($params);
//var_dump($db->errorInfo());exit;
// Update ID for a new object
if (!isset($id)) {
$contract->setId($db->lastInsertId('hms_contract_seq'));
}
}
/**
* Returns the *latest* timestamp of a state change on this,
* or on any of its participants
*/
public function getLastUpdatedTimestamp()
{
$db = PdoFactory::getPdoInstance();
$query = "select effective_date from hms_room_change_curr_participant where request_id = :requestId order by effective_date DESC LIMIT 1";
$stmt = $db->prepare($query);
$stmt->execute(array('requestId' => $this->getId()));
$stmt->setFetchMode(PDO::FETCH_ASSOC);
$value = $stmt->fetch();
return $value['effective_date'];
}
public static function getRequestPendingCheckout(Student $student, $term)
{
$db = PdoFactory::getPdoInstance();
$query = "SELECT hms_room_change_curr_request.* FROM hms_room_change_curr_request\n JOIN hms_room_change_curr_participant ON hms_room_change_curr_request.id = hms_room_change_curr_participant.request_id\n WHERE\n term = :term AND\n banner_id = :bannerId AND\n hms_room_change_curr_request.state_name = 'Approved' AND\n hms_room_change_curr_participant.state_name = 'InProcess'";
$stmt = $db->prepare($query);
$stmt->execute(array('term' => $term, 'bannerId' => $student->getBannerId()));
$results = $stmt->fetchAll(PDO::FETCH_CLASS, 'RoomChangeRequestRestored');
// If more than one pending request is found, throw an exception
if (sizeof($results) > 1) {
throw new InvalidArgumentException('More than one pending room change detected.');
} else {
if (sizeof($results) == 0) {
return null;
} else {
return $results[0];
}
}
}
/**
* Main public function for getting student info.
* Used by the rest of the "get" public functions
* @return SOAP response object
* @throws InvalidArgumentException, SOAPException
*/
public function getStudentProfile($bannerId, $term)
{
// Sanity checking on the username
if (empty($bannerId) || is_null($bannerId) || !isset($bannerId)) {
throw new InvalidArgumentException('Bad BannerId.');
}
// Sanity checking on the term
if (empty($term) || is_null($term) || !isset($term)) {
throw new InvalidArgumentException('Bad term');
}
$student = new stdClass();
$db = PdoFactory::getPdoInstance();
$query = "SELECT * FROM fake_soap WHERE banner_id = :banner_id";
$stmt = $db->prepare($query);
$params = array('banner_id' => $bannerId);
$stmt->execute($params);
$result = $stmt->fetch(PDO::FETCH_ASSOC);
if (empty($result)) {
require_once PHPWS_SOURCE_DIR . 'mod/hms/class/exception/StudentNotFoundException.php';
throw new StudentNotFoundException('User not found', 0, $bannerId);
}
$student = new stdClass();
$student->banner_id = $result['banner_id'];
$student->user_name = $result['username'];
$student->last_name = $result['last_name'];
$student->first_name = $result['first_name'];
$student->middle_name = $result['middle_name'];
$student->pref_name = $result['pref_name'];
$student->dob = $result['dob'];
$student->gender = $result['gender'];
$student->deposit_date = '';
// unused but present
$student->deposit_waived = 'false';
// unused but present
$student->confid = 'Y';
$student->international = $result['international'];
$student->student_level = $result['student_level'];
// U-undergrad, G-Graduat']e
$student->app_decision_code = '1*';
$student->honors = $result['honors'];
$student->teaching_fellow = $result['teaching_fellow'];
$student->watauga_member = $result['watauga_member'];
$student->greek = $result['greek'];
$student->disabled_pin = false;
$student->housing_waiver = $result['housing_waiver'];
$student->student_type = $result['student_type'];
$student->application_term = $result['application_term'];
$student->projected_class = $result['projected_class'];
$student->credhrs_completed = $result['credhrs_completed'];
$student->credhrs_for_term = $result['credhrs_for_term'];
$student->on_campus = 'false';
// unused
$address_array = unserialize($result['address']);
$address_object_array = array();
foreach ($address_array as $add) {
$address_object_array[] = (object) $add;
}
$phone_array = unserialize($result['phone']);
$phone_object_array = array();
foreach ($phone_array as $p) {
$phone_object_array[] = (object) $p;
}
$student->address = $address_object_array;
$student->error_num = 0;
$student->error_desc = null;
$student->phone = $phone_object_array;
$this->createDelay();
return $student;
}
public static function getAssessedDamagesStudentTotals($term)
{
$db = PdoFactory::getPdoInstance();
$query = "select banner_id, sum(amount) from hms_room_damage JOIN hms_room_damage_responsibility ON hms_room_damage.id = hms_room_damage_responsibility.damage_id where term = :term and state = 'assessed' group by banner_id";
$params = array('term' => $term);
$stmt = $db->prepare($query);
$stmt->execute($params);
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
