/**
* Returns a PdoFactory instance
* @return PdoFactory $pdo A PdoInstance object
*/
public static function getInstance()
{
if (!isset(self::$factory)) {
self::$factory = new PdoFactory();
}
return self::$factory;
}
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
}
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 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;
}
public function execute()
{
PHPWS_Core::initModClass('hms', 'PdoFactory.php');
$db = PdoFactory::getInstance()->getPdo();
$query = "SELECT requestor, requestee from hms_roommate\n LEFT OUTER JOIN\n (select asu_username, hms_room.id as rmid\n FROM hms_assignment\n JOIN hms_bed ON hms_assignment.bed_id = hms_bed.id\n JOIN hms_room ON hms_bed.room_id = hms_room.id\n WHERE hms_assignment.term = :term)\n as requestor_room_id ON requestor_room_id.asu_username = requestor\n LEFT OUTER JOIN\n (select asu_username, hms_room.id as rmid\n FROM hms_assignment\n JOIN hms_bed ON hms_assignment.bed_id = hms_bed.id\n JOIN hms_room ON hms_bed.room_id = hms_room.id\n WHERE hms_assignment.term = :term)\n AS requestee_room_id ON requestee_room_id.asu_username = requestee\n WHERE hms_roommate.term = :term and confirmed = 1 AND\n requestor_room_id.rmid != requestee_room_id.rmid";
// $query = "SELECT hms_assignment.term, hms_assignment.banner_id, hms_hall_structure.banner_building_code, hms_hall_structure.banner_id
// as bed_code, hms_new_application.meal_plan FROM hms_assignment
// JOIN hms_hall_structure ON
// hms_assignment.bed_id = hms_hall_structure.bedid
// LEFT OUTER JOIN hms_new_application ON
// (hms_assignment.banner_id = hms_new_application.banner_id
// AND hms_assignment.term = hms_new_application.term)
// WHERE hms_assignment.term IN (:term) ORDER BY hms_assignment.term";
$stmt = $db->prepare($query);
$stmt->execute(array('term' => $this->term));
$queryResult = $stmt->fetchAll(PDO::FETCH_ASSOC);
$result = array();
$i = 0;
foreach ($queryResult as $row) {
$requestee = StudentFactory::getStudentByUsername($row['requestee'], $this->term);
$requestor = StudentFactory::getStudentByUsername($row['requestor'], $this->term);
$row['requestee_banner'] = $requestee->getBannerId();
$row['requestor_banner'] = $requestor->getBannerId();
$row['requestee_name'] = $requestee->getFullName();
$row['requestor_name'] = $requestor->getFullName();
$result[$i] = $row;
$i++;
}
$this->data = $result;
$this->mismatchCount = $i;
}
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 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 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()
{
PHPWS_Core::initModClass('hms', 'PdoFactory.php');
$db = PdoFactory::getInstance()->getPdo();
$query = "SELECT hms_assignment.term, hms_assignment.banner_id, hms_hall_structure.banner_building_code, hms_hall_structure.banner_id as bed_code, hms_new_application.meal_plan FROM hms_assignment JOIN hms_hall_structure ON hms_assignment.bed_id = hms_hall_structure.bedid LEFT OUTER JOIN hms_new_application ON (hms_assignment.banner_id = hms_new_application.banner_id AND hms_assignment.term = hms_new_application.term) WHERE hms_assignment.term IN (:term) ORDER BY hms_assignment.term";
$stmt = $db->prepare($query);
$stmt->execute(array('term' => $this->term));
$this->rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
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)
{
$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 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 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 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 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;
}
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;
}
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;
}
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 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'));
}
}
public function execute()
{
PHPWS_Core::initModClass('hms', 'PdoFactory.php');
$db = PdoFactory::getInstance()->getPdo();
$query = 'SELECT hms_assignment.banner_id, hms_assignment.reason,
hms_hall_structure.hall_name, hms_hall_structure.room_number, hms_hall_structure.bed_letter
FROM hms_assignment
JOIN hms_hall_structure ON hms_assignment.bed_id = hms_hall_structure.bedid
WHERE hms_assignment.banner_id
NOT IN (SELECT hms_assignment.banner_id
FROM hms_assignment
JOIN hms_bed ON hms_assignment.bed_id = hms_bed.id
JOIN hms_checkin ON hms_bed.persistent_id = hms_checkin.bed_persistent_id
WHERE hms_assignment.bed_id = hms_checkin.bed_id
AND hms_assignment.banner_id = hms_checkin.banner_id
AND hms_assignment.term = :term)
AND hms_assignment.term = :term';
$stmt = $db->prepare($query);
$stmt->execute(array('term' => $this->term));
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
$rows = array();
$i = 0;
foreach ($results as $row) {
$rowVals = array();
$student = StudentFactory::getStudentByBannerID($row['banner_id'], $this->term);
$rowVals['banner_id'] = $row['banner_id'];
$rowVals['username'] = $student->getUsername();
$rowVals['name'] = $student->getFullName();
$rowVals['class'] = $student->getClass();
$rowVals['reason'] = constant($row['reason']);
$rowVals['hall_name'] = $row['hall_name'];
$rowVals['room_number'] = $row['room_number'];
$rowVals['bed_letter'] = $row['bed_letter'];
$rows[$i] = $rowVals;
$i++;
}
$this->total = $i;
$this->data = $rows;
}
public function execute()
{
PHPWS_Core::initModClass('hms', 'PdoFactory.php');
PHPWS_Core::initModClass('hms', 'StudentFactory.php');
$db = PdoFactory::getInstance()->getPdo();
$query = " select hms_assignment.banner_id, hall_name, floor_number, room_number from hms_assignment join hms_bed on hms_assignment.bed_id = hms_bed.id join hms_room on hms_bed.room_id = hms_room.id join hms_floor on hms_room.floor_id = hms_floor.id join hms_residence_hall on hms_floor.residence_hall_id = hms_residence_hall.id where hms_bed.ra = 1 and hms_assignment.term = :term";
$stmt = $db->prepare($query);
$stmt->execute(array('term' => $this->term));
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($results as $row) {
try {
$student = StudentFactory::getStudentByBannerId($row['banner_id'], $this->term);
$row['first_name'] = $student->getFirstName();
$row['last_name'] = $student->getLastName();
$row['email'] = $student->getUsername();
} catch (StudentNotFoundException $e) {
$row['first_name'] = 'NOT FOUND';
$row['last_name'] = 'NOT FOUND';
$row['email'] = 'NOT FOUND';
}
$this->rows[] = $row;
}
}
public function execute()
{
PHPWS_Core::initModClass('hms', 'PdoFactory.php');
PHPWS_Core::initModClass('hms', 'StudentFactory.php');
$db = PdoFactory::getInstance()->getPdo();
$query = "select hall_name, room_number, hms_checkin.banner_id, to_timestamp(checkout_date), checkout_by from hms_checkin JOIN hms_bed ON (hms_checkin.bed_persistent_id = hms_bed.persistent_id AND hms_checkin.term = hms_bed.term) JOIN hms_room ON hms_bed.room_id = hms_room.id JOIN hms_floor ON hms_room.floor_id = hms_floor.id JOIN hms_residence_hall ON hms_floor.residence_hall_id = hms_residence_hall.id WHERE hms_checkin.term = :term and key_not_returned = 1 ORDER BY checkout_date";
$stmt = $db->prepare($query);
$stmt->execute(array('term' => $this->term));
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($results as $row) {
try {
$student = StudentFactory::getStudentByBannerId($row['banner_id'], $this->term);
$row['first_name'] = $student->getFirstName();
$row['last_name'] = $student->getLastName();
$row['email'] = $student->getUsername();
} catch (StudentNotFoundException $e) {
$row['first_name'] = 'NOT FOUND';
$row['last_name'] = 'NOT FOUND';
$row['email'] = 'NOT FOUND';
}
$this->rows[] = $row;
}
}
public function execute()
{
PHPWS_Core::initModClass('hms', 'PdoFactory.php');
PHPWS_Core::initModClass('hms', 'StudentFactory.php');
$db = PdoFactory::getInstance()->getPdo();
$query = "select hall_name, room_number, hms_room_damage_responsibility.banner_id, amount, hms_damage_type.description, to_timestamp(CAST(assessed_on as integer)), assessed_by, hms_room_damage.note from hms_room_damage_responsibility JOIN hms_room_damage ON hms_room_damage_responsibility.damage_id = hms_room_damage.id JOIN hms_room ON (hms_room.persistent_id = hms_room_damage.room_persistent_id AND hms_room_damage.term = hms_room.term) JOIN hms_floor ON hms_room.floor_id = hms_floor.id JOIN hms_residence_hall ON hms_floor.residence_hall_id = hms_residence_hall.id JOIN hms_damage_type ON hms_room_damage.damage_type = hms_damage_type.id WHERE hms_room_damage.term = :term and state = 'assessed' and amount != 0 ORDER BY assessed_on, banner_id";
$stmt = $db->prepare($query);
$stmt->execute(array('term' => $this->term));
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($results as $row) {
try {
$student = StudentFactory::getStudentByBannerId($row['banner_id'], $this->term);
$row['first_name'] = $student->getFirstName();
$row['last_name'] = $student->getLastName();
$row['email'] = $student->getUsername();
} catch (StudentNotFoundException $e) {
$row['first_name'] = 'NOT FOUND';
$row['last_name'] = 'NOT FOUND';
$row['email'] = 'NOT FOUND';
}
$this->rows[] = $row;
}
}