PHP PHPWS_DB::setIndexBy方法代码示例

 /**
  * 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 loadReasons($include_summary = false)
 {
     $db = new PHPWS_DB('checkin_reasons');
     $db->addWhere('checkin_rtos.staff_id', $this->id);
     $db->addWhere('id', 'checkin_rtos.reason_id');
     $db->addColumn('id');
     if ($include_summary) {
         $db->addColumn('summary');
         $db->setIndexBy('id');
     }
     $result = $db->select('col');
     if (!PHPWS_Error::logIfError($result)) {
         $this->_reasons =& $result;
     }
 }

 public static function getAllowedStates()
 {
     $db = new \PHPWS_DB('intern_state');
     $db->addWhere('active', 1);
     $db->addColumn('abbr');
     $db->addColumn('full_name');
     $db->setIndexBy('abbr');
     $db->addOrder('full_name ASC');
     $states = $db->select('col');
     if (empty($states)) {
         \NQ::simple('intern', \Intern\UI\NotifyUI::ERROR, 'The list of allowed US states for internship locations has not been configured. Please use the administrative options to <a href="index.php?module=intern&action=edit_states">add allowed states.</a>');
         \NQ::close();
         PHPWS_Core::goBack();
     }
     return $states;
 }

 public static function getModList()
 {
     $db = new PHPWS_DB('search');
     $db->addColumn('module', null, null, false, true);
     $db->addColumn('modules.proper_name');
     $db->addGroupBy('modules.proper_name');
     $db->addWhere('search.module', 'modules.title');
     $db->setIndexBy('module');
     $result = $db->select('col');
     if (PHPWS_Error::isError($result)) {
         PHPWS_Error::log($result);
         $result = NULL;
     }
     $mod_list = array('all' => dgettext('search', 'All modules'));
     if (!empty($result)) {
         $mod_list = array_merge($mod_list, $result);
     }
     return $mod_list;
 }

 public static function getAllowedStates()
 {
     $db = new PHPWS_DB('intern_state');
     $db->addWhere('active', 1);
     $db->addColumn('abbr');
     $db->addColumn('full_name');
     $db->setIndexBy('abbr');
     // get backwards because we flip it
     $db->addOrder('full_name desc');
     $states = $db->select('col');
     if (empty($states)) {
         NQ::simple('intern', INTERN_ERROR, 'The list of allowed US states for internship locations has not been configured. Please use the administrative options to <a href="index.php?module=intern&action=edit_states">add allowed states.</a>');
         NQ::close();
         PHPWS_Core::goBack();
     }
     $states[-1] = 'Select a state';
     $states = array_reverse($states, true);
     return $states;
 }

 public function getStaffList($as_object = false, $available_only = false, $active_only = false)
 {
     $db = new PHPWS_DB('checkin_staff');
     if ($available_only) {
         $db->addWhere('status', 1, '!=');
     }
     $db->addWhere('user_id', 'users.id');
     if ($active_only) {
         $db->addWhere('active', 1);
     }
     $db->addColumn('users.display_name');
     if ($as_object) {
         PHPWS_Core::initModClass('checkin', 'Staff.php');
         $db->addColumn('*');
         return $db->getObjects('Checkin_Staff');
     } else {
         $db->addColumn('id');
         $db->setIndexBy('id');
         return $db->select('col');
     }
 }

function properties_update(&$content, $currentVersion)
{
    switch ($currentVersion) {
        case version_compare($currentVersion, '1.1.0', '<'):
            $db = new PHPWS_DB('properties');
            $result = $db->addTableColumn('efficiency', 'smallint not null default 0');
            if (PHPWS_Error::isError($result)) {
                PHPWS_Error::log($result);
                $content[] = 'ERROR - could not add efficiency column';
                return false;
            }
            $content[] = '<pre>1.1.0 updates
---------------
+ Added efficiency option</pre>';
        case version_compare($currentVersion, '1.1.1', '<'):
            $db = new PHPWS_DB('prop_contacts');
            $result = $db->addTableColumn('company_url', 'VARCHAR( 255 ) NULL');
            if (PHPWS_Error::isError($result)) {
                PHPWS_Error::log($result);
                $content[] = 'ERROR - could not add company_url column';
                return false;
            }
            $content[] = '<pre>1.1.1 updates
---------------
+ Added company url
+ Property listing divided into tabs.</pre>';
        case version_compare($currentVersion, '1.2.0', '<'):
            $db = new PHPWS_DB('properties');
            $db->addWhere('pets_allowed', 1);
            $db->addColumn('id');
            $db->addColumn('pet_type');
            $db->setIndexBy('id');
            $cols = $db->select('col');
            if (!empty($cols)) {
                foreach ($cols as $id => $pets) {
                    if (empty($pets)) {
                        continue;
                    }
                    $db->reset();
                    $pets_array = null;
                    $pets_array = @unserialize($pets);
                    if (!is_array($pets_array)) {
                        continue;
                    } else {
                        $pets = implode(', ', $pets_array);
                    }
                    $db->addWhere('id', $id);
                    $db->addValue('pet_type', $pets);
                    $db->update();
                }
            }
            $db->reset();
            $result = $db->addTableColumn('pet_fee', 'int not null default 0');
            if (PHPWS_Error::isError($result)) {
                PHPWS_Error::log($result);
                $content[] = 'ERROR - could not add pet_fee column';
                return false;
            }
            $db->reset();
            $result = $db->addTableColumn('airconditioning', 'smallint not null default 0');
            if (PHPWS_Error::isError($result)) {
                PHPWS_Error::log($result);
                $content[] = 'ERROR - could not add airconditioning column';
                return false;
            }
            $db->reset();
            $result = $db->addTableColumn('heat_type', 'varchar(255) default null');
            if (PHPWS_Error::isError($result)) {
                PHPWS_Error::log($result);
                $content[] = 'ERROR - could not add heat_type column';
                return false;
            }
        case version_compare($currentVersion, '1.2.1', '<'):
            $content[] = '<pre>1.2.1 updates
---------------
- Improved look with Bootstrapping.</pre>';
        case version_compare($currentVersion, '1.2.2', '<'):
            $content[] = '<pre>1.2.2 updates
---------------
+ Added gas heat and fiber internet/tv.
</pre>';
        case version_compare($currentVersion, '1.3.0', '<'):
            $content[] = <<<EOF
<pre>1.3.0 updates
-----------------
+ Changed login box for IE users
+ Added ability to view properties by contact.
+ Contacts list compacted. Email links to contact name.
+ Fixed bad function call on error page.
+ Added error checks in case 1) the property does not exists or not active or
  2) the image files are not present.
+ Test for incactive properties preventing error.
+ Added Bootstrap styling and overhauled to work on mobile devices.
+ Last logged defaults to creation date.
+ Fixed Shared Bedroom and Bathroom settings on roommates page.
+ Fixed active/inactive buttons.
</pre>
EOF;
    }
    return true;
//.........这里部分代码省略.........

 public static function assignDefaultGroup(PHPWS_User $user)
 {
     $db = new PHPWS_DB('users_auth_scripts');
     $db->addColumn('default_group');
     $db->addColumn('id');
     $db->setIndexBy('id');
     $scripts = $db->select('col');
     $default_group = $scripts[$user->authorize];
     $group = new PHPWS_Group($default_group);
     if (!$group->id) {
         return false;
     }
     $group->addMember($user->_user_group);
     $group->save();
     return true;
 }

//.........这里部分代码省略.........
     // sizeof($depts) == 2 because of the 'Select Deparmtnet' option
     if (sizeof($depts) == 2) {
         $keys = array_keys($depts);
         $form->setMatch('dept', $keys[1]);
     }
     // Student level radio button
     javascript('jquery');
     javascriptMod('intern', 'majorSelector', array('form_id' => $form->id));
     $levels = array('-1' => 'Any Level', 'ugrad' => 'Undergraduate', 'grad' => 'Graduate');
     $form->addSelect('student_level', $levels);
     $form->setLabel('student_level', 'Level');
     $form->setClass('student_level', 'form-control');
     // Student Major dummy box (gets replaced by dropdowns below using JS when student_level is selected)
     $levels = array('-1' => 'Choose student level first');
     $form->addDropBox('student_major', $levels);
     $form->setLabel('student_major', 'Major / Program');
     $form->addCssClass('student_major', 'form-control');
     // Undergrad major drop down
     if (isset($s)) {
         $majors = Major::getMajorsAssoc($s->ugrad_major);
     } else {
         $majors = Major::getMajorsAssoc();
     }
     $form->addSelect('ugrad_major', $majors);
     $form->setLabel('ugrad_major', 'Undergraduate Majors & Certificate Programs');
     $form->setClass('ugrad_major', 'form-control');
     // Graduate major drop down
     if (isset($s)) {
         $progs = GradProgram::getGradProgsAssoc($s->grad_prog);
     } else {
         $progs = GradProgram::getGradProgsAssoc();
     }
     $form->addSelect('grad_prog', $progs);
     $form->setLabel('grad_prog', 'Graduate Majors & Certificate Programs');
     $form->setClass('grad_prog', 'form-control');
     // Campus
     $campuses = array('main_campus' => 'Main Campus', 'distance_ed' => 'Distance Ed');
     $form->addRadioAssoc('campus', $campuses);
     /***************
      * Course Info *
      ***************/
     $subjects = Subject::getSubjects();
     $form->addSelect('course_subj', $subjects);
     $form->setLabel('course_subj', 'Subject');
     $form->setClass('course_subj', 'form-control');
     $form->addText('course_no');
     $form->setLabel('course_no', 'Course Number');
     $form->setSize('course_no', 6);
     $form->setMaxSize('course_no', 4);
     $form->setClass('course_no', 'form-control');
     $form->addText('course_sect');
     $form->setLabel('course_sect', 'Section');
     $form->setSize('course_sect', 6);
     $form->setMaxSize('course_sect', 4);
     $form->setClass('course_sect', 'form-control');
     // Internship types.
     $types = Internship::getTypesAssoc();
     $form->addRadioAssoc('type', $types);
     // Location
     $loc = array('domestic' => 'Domestic', 'internat' => 'International');
     $form->addRadioAssoc('loc', $loc);
     /* State search */
     $db = new PHPWS_DB('intern_state');
     $db->addWhere('active', 1);
     $db->addColumn('abbr');
     $db->addColumn('full_name');
     $db->setIndexBy('abbr');
     // get backwards because we flip it
     $db->addOrder('full_name desc');
     $states = $db->select('col');
     if (empty($states)) {
         NQ::simple('intern', INTERN_ERROR, 'The list of allowed US states for internship locations has not been configured. Please use the administrative options to <a href="index.php?module=intern&action=edit_states">add allowed states.</a>');
         NQ::close();
         PHPWS_Core::goBack();
     }
     $states[-1] = 'Select state';
     $states = array_reverse($states, true);
     $form->addSelect('state', $states);
     $form->setLabel('state', 'State');
     $form->setClass('state', 'form-control');
     /* Province search */
     $form->addText('prov');
     $form->setLabel('prov', 'Province/Territory');
     $form->setClass('prov', 'form-control');
     // Workflow states
     $workflowStates = WorkflowStateFactory::getStatesAssoc();
     unset($workflowStates['CreationState']);
     // Remove this state, since it's not valid (internal only state for initial creation)
     $form->addCheckAssoc('workflow_state', $workflowStates);
     unset($_REQUEST['module']);
     unset($_REQUEST['action']);
     unset($_REQUEST['submit']);
     //test($_REQUEST,1);
     $form->plugIn($_REQUEST);
     $form->addSubmit('submit', 'Search');
     // Javascript...
     javascript('jquery');
     javascriptMod('intern', 'resetSearch');
     return PHPWS_Template::process($form->getTemplate(), 'intern', 'search.tpl');
 }

 public function moveDown()
 {
     $db = new PHPWS_DB('controlpanel_link');
     $db->setIndexBy('link_order');
     $db->addWhere('tab', $this->tab);
     $db->addOrder('link_order');
     $allLinks = $db->getObjects('PHPWS_Panel_Link');
     $number_of_links = count($allLinks);
     $current_order = $this->getLinkOrder();
     if ($current_order == $number_of_links) {
         unset($allLinks[$current_order]);
         array_unshift($allLinks, $this);
     } else {
         $tempObj = $allLinks[$current_order + 1];
         $allLinks[$current_order] = $tempObj;
         $allLinks[$current_order + 1] = $this;
     }
     $count = 1;
     foreach ($allLinks as $link) {
         $link->setLinkOrder($count);
         $link->save();
         $count++;
     }
 }

 /**
  * Called from the three file type managers. Adds a file listing
  * to move files from one folder to another
  */
 public static function moveToForm(PHPWS_Form $form, $folder)
 {
     $db = new PHPWS_DB('folders');
     $db->addWhere('id', $folder->id, '!=');
     $db->addWhere('ftype', $folder->ftype);
     $db->addColumn('id');
     $db->addColumn('title');
     $db->setIndexBy('id');
     $folders = $db->select('col');
     if (!empty($folders)) {
         $folders = array(0 => '') + $folders;
         $form->addSelect('move_to_folder', $folders);
         $form->setLabel('move_to_folder', dgettext('filecabinet', 'Move to folder'));
     }
 }

 private function summaryReport()
 {
     javascript('datepicker');
     $form = new PHPWS_Form('report-date');
     $form->setMethod('get');
     $form->addHidden('module', 'checkin');
     $form->addHidden('aop', 'report');
     $form->addHidden('summary_report', 1);
     $form->addText('start_date', $_GET['start_date']);
     $form->setLabel('start_date', 'Start date');
     $form->setSize('start_date', 10);
     $form->setExtra('start_date', 'class="datepicker"');
     $form->addText('end_date', $_GET['end_date']);
     $form->setLabel('end_date', 'End date');
     $form->setSize('end_date', 10);
     $form->setExtra('end_date', 'class="datepicker"');
     if (!empty($_GET['visitor_name'])) {
         $name = trim(strip_tags($_GET['visitor_name']));
     } else {
         $name = null;
     }
     $form->addText('visitor_name', $name);
     $form->setLabel('visitor_name', 'Visitor name');
     $form->addSubmit(dgettext('checkin', 'Summary report'));
     $db = new PHPWS_DB('checkin_staff');
     $db->addColumn('checkin_staff.id');
     $db->addColumn('users.display_name');
     $db->addWhere('checkin_staff.user_id', 'users.id');
     $db->setIndexBy('id');
     $db->addOrder('users.display_name desc');
     $assigned = $db->select('col');
     $assigned[0] = dgettext('checkin', 'Show all');
     $assigned = array_reverse($assigned, true);
     $form->addSelect('assigned', $assigned);
     $form->setLabel('assigned', 'By staff');
     if (isset($_GET['assigned'])) {
         $staff_id = (int) $_GET['assigned'];
         $form->setMatch('assigned', $staff_id);
     } else {
         $staff_id = 0;
     }
     $tpl = $form->getTemplate();
     $start_date = strtotime($_GET['start_date']);
     $end_date = strtotime($_GET['end_date']);
     if (empty($start_date) || empty($end_date) || $start_date > $end_date) {
         $tpl['EMPTY'] = 'Please enter your date range again.';
     } else {
         $this->title = 'Visitors from ' . $_GET['start_date'] . ' to ' . $_GET['end_date'];
         $db = new PHPWS_DB('checkin_visitor');
         $db->addWhere('arrival_time', $start_date, '>=');
         $db->addWhere('arrival_time', $end_date, '<=');
         $db->addColumn('id');
         $db->addColumn('arrival_time');
         $db->addColumn('firstname');
         $db->addColumn('lastname');
         $db->addColumn('start_meeting');
         $db->addColumn('end_meeting');
         if ($staff_id) {
             $db->addWhere('assigned', $staff_id);
         }
         if (!empty($name)) {
             $name = strtolower($name);
             if (strlen($name) == 1) {
                 $db->addWhere('firstname', "{$name}%", 'like', 'and', 'name');
                 $db->addWhere('lastname', "{$name}%", 'like', 'or', 'name');
             } else {
                 $db->addWhere('firstname', "%{$name}%", 'like', 'and', 'name');
                 $db->addWhere('lastname', "%{$name}%", 'like', 'or', 'name');
             }
         }
         $result = $db->select();
         $total_visits = 0;
         $total_wait = 0;
         $total_meeting = 0;
         $total_days = 0;
         $incomplete_visits = 0;
         $current_day = null;
         foreach ($result as $visit) {
             extract($visit);
             $arrival_day = date('MdY', $arrival_time);
             if ($current_day != $arrival_day) {
                 $current_day = $arrival_day;
                 $total_days++;
             }
             $row = array();
             if (!$start_meeting || !$end_meeting) {
                 $incomplete_visits++;
                 continue;
             }
             $total_visits++;
             $twaited = $start_meeting - $arrival_time;
             $waited = Checkin::timeWaiting($twaited);
             if ($end_meeting) {
                 $tmeeting = $end_meeting - $start_meeting;
             }
             $meeting = Checkin::timeWaiting($tmeeting);
             $row['VISIT'] = $total_visits;
             $row['VISITOR'] = "{$firstname} {$lastname}";
             $row['DATE'] = date('g:ia m.d.Y', $arrival_time);
             $row['WAITED'] = $waited;
//.........这里部分代码省略.........

 public static function allowDeny()
 {
     if (!PHPWS_Settings::get('access', 'allow_deny_enabled')) {
         $_SESSION['Access_Allow_Deny'] = true;
         return;
     }
     $address = Access::inflateIp($_SERVER['REMOTE_ADDR']);
     $allow_all = PHPWS_Settings::get('access', 'allow_all');
     $deny_all = PHPWS_Settings::get('access', 'deny_all');
     $db = new PHPWS_DB('access_allow_deny');
     $db->addWhere('active', 1);
     $db->addColumn('allow_or_deny');
     $db->addColumn('ip_address');
     $db->setIndexBy('allow_or_deny');
     $perms = $db->select('col');
     if (isset($perms[1]) && ($allow_all || !empty($perms[1]) && Access::comparePermissions($perms[1], $address))) {
         $_SESSION['Access_Allow_Deny'] = true;
     }
     if (isset($perms[0]) && ($deny_all || !empty($perms[0]) && Access::comparePermissions($perms[0], $address))) {
         $_SESSION['Access_Allow_Deny'] = false;
         return;
     }
     $_SESSION['Access_Allow_Deny'] = true;
     return;
 }

 public function modulesInUse()
 {
     $db = new PHPWS_DB('phpws_key');
     $db->addColumn('module');
     $db->addColumn('modules.proper_name');
     $db->addWhere('module', 'modules.title');
     $db->addOrder('phpws_key.module');
     $db->setIndexBy('module');
     $db->setDistinct(true);
     return $db->select('col');
 }

 private function pageLinks(&$tpl)
 {
     $db = new PHPWS_DB('ps_page');
     $db->addWhere('deleted', 0);
     $db->addColumn('id');
     $db->addColumn('page_order');
     $db->setIndexBy('page_order');
     $db->addOrder('page_order asc');
     if ($this->parent_page) {
         $db->addWhere('id', $this->parent_page);
         $db->addWhere('parent_page', $this->parent_page, null, 'or');
     } else {
         $db->addWhere('parent_page', $this->id);
     }
     $pages = $db->select('col');
     if (PHPWS_Error::logIfError($pages) || empty($pages)) {
         return;
     }
     if (!$this->parent_page) {
         array_unshift($pages, $this->id);
     }
     if ($this->page_order) {
         $prev_page = $pages[$this->page_order - 1];
     } else {
         $prev_page = 0;
     }
     foreach ($pages as $page_no => $id) {
         if ($page_no == 0 && $prev_page) {
             $link = new PHPWS_Link('<span>&lt;&lt;</span>&#160;' . dgettext('pagesmith', 'Previous'), 'pagesmith', array('id' => $prev_page));
             $links[] = $link->get();
         }
         if ($id == $this->id) {
             $links[] = $page_no + 1;
             if (isset($pages[$page_no + 1])) {
                 $next_page = $pages[$page_no + 1];
             } else {
                 $next_page = null;
             }
         } else {
             $link = new PHPWS_Link($page_no + 1, 'pagesmith', array('id' => $id));
             $link->setRewrite();
             $links[] = $link->get();
         }
     }
     if ($next_page) {
         $link->setLabel(dgettext('pagesmith', 'Next') . '&#160;<span>&gt;&gt;</span>');
         $link->setValue('id', $next_page);
         $links[] = $link->get();
     }
     $tpl['PAGE_LINKS'] = implode('&#160;|&#160;', $links);
 }