PHP PHPWS_DB::addJoin方法代码示例

 /**
  * 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');
 }