PHP PHPWS_DB::getAll方法代码示例

 public static function saveObject(DbStorable $o)
 {
     $vars = $o->extractVars();
     $tableName = $o::getTableName();
     // Check if the key already exists
     $query = "SELECT * FROM {$tableName} WHERE id = {$vars['id']}";
     $result = \PHPWS_DB::getAll($query);
     if (count($result) > 0) {
         $exists = true;
     } else {
         $exists = false;
     }
     $db = new \PHPWS_DB($o->getTableName());
     foreach ($vars as $key => $value) {
         $db->addValue($key, $value);
     }
     if ($exists) {
         $db->addWhere('id', $vars['id']);
         $result = $db->update();
     } else {
         $result = $db->insert(false);
     }
     if (\PHPWS_Error::logIfError($result)) {
         throw new \Exception($result->toString());
     }
 }

 public function execute()
 {
     PHPWS_Core::initModClass('hms', 'HMS_Residence_Hall.php');
     if (!isset($this->term) || is_null($this->term)) {
         throw new InvalidArgumentException('Missing term.');
     }
     // Get all of the residence halls for this term
     $halls = HMS_Residence_Hall::get_halls($this->term);
     $hallRow = array();
     foreach ($halls as $hall) {
         $hallName = $hall->hall_name;
         $maxOccupancy = $hall->get_number_of_online_nonoverflow_beds();
         $currOccupancy = $hall->get_number_of_assignees();
         $offline = "";
         // If the hall is offline, make a note of that
         if ($hall->is_online == 0) {
             $offline = '(Offline)';
         }
         $query = "select * from hms_room JOIN (SELECT hms_room.id, count(*) as c FROM hms_residence_hall JOIN hms_floor ON hms_residence_hall.id = hms_floor.residence_hall_id JOIN hms_room ON hms_floor.id = hms_room.floor_id JOIN hms_bed ON hms_room.id = hms_bed.room_id LEFT OUTER JOIN hms_assignment ON hms_bed.id = hms_assignment.bed_id WHERE ( hms_assignment.id IS NULL AND hms_residence_hall.id = '{$hall->id}' AND hms_room.offline = 0 AND hms_room.overflow = 0 AND hms_room.reserved = 0 AND hms_room.private = 0 AND hms_room.parlor = 0 AND hms_room.ra = 0 AND hms_bed.room_change_reserved = 0 AND hms_bed.ra_roommate = 0 AND hms_bed.international_reserved = 0) GROUP BY hms_room.id) as foo ON foo.id = hms_room.id ORDER BY hms_room.room_number";
         $results = PHPWS_DB::getAll($query);
         $maleRoomList = array();
         $femaleRoomList = array();
         $coedRoomList = array();
         // each room on this hall
         foreach ($results as $room) {
             $this->totalRooms++;
             $roomNum = $room['room_number'];
             // If the room has more than one avaialble bed, note that in the room number
             if ($room['c'] > 1) {
                 $roomNum .= '(x' . $room['c'] . ')';
             }
             // catagorize it by gender
             if ($room['gender_type'] == MALE) {
                 $maleRoomList[] = $roomNum;
                 $this->male += $room['c'];
                 $this->totalBeds += $room['c'];
             } else {
                 if ($room['gender_type'] == FEMALE) {
                     $femaleRoomList[] = $roomNum;
                     $this->female += $room['c'];
                     $this->totalBeds += $room['c'];
                 } else {
                     if ($room['gender_type'] == COED || $room['gender_type'] == AUTO) {
                         $coedRoomList[] = $roomNum;
                         $this->coed += $room['c'];
                         $this->totalBeds += $room['c'];
                     } else {
                         throw new InvalidArgumentException('Bad room gender. Room id: ' . $room['id']);
                     }
                 }
             }
         }
         $hallRow[] = array('hallName' => $hallName . $offline, 'maxOccupancy' => $maxOccupancy, 'currOccupancy' => $currOccupancy, 'maleRooms' => implode(", ", $maleRoomList), 'femaleRooms' => implode(", ", $femaleRoomList), 'coedRooms' => implode(", ", $coedRoomList));
     }
     $this->data = $hallRow;
 }

 public function execute(CommandContext $context)
 {
     $this->searchString = $context->get('studentSearchQuery');
     // NB: this is the *search term*, not the semester
     $this->hmsTerm = Term::getCurrentTerm();
     // If the search string is empty, just return an empty json array
     if (!isset($this->searchString) || $this->searchString == '') {
         echo json_encode(array());
         exit;
     }
     // Strip any non-alphanumeric characters, escape slashes
     $this->searchString = pg_escape_string($this->searchString);
     // Check for a direct banner ID match
     if (preg_match("/^[0-9]{9}/", $this->searchString)) {
         // String is all-numeric, probably a Banner ID
         // If the seach string is exactly 9 digits, then try to find a match
         $sql = $this->getBannerIdSearchSql();
     } else {
         // Do fancy string matching instead
         $sql = $this->getFuzzyTextSql();
     }
     // TODO join for only assigned students / applied students in current/future terms
     // Add a limit on the number of results
     $sql .= " LIMIT " . self::resultLimit;
     //test($sql,1);
     $this->db = new PHPWS_DB('hms_student_autocomplete');
     $results = PHPWS_DB::getAll($sql);
     //test($results,1);
     if (is_null($results)) {
         echo json_encode();
         exit;
     }
     // Log any DB errors and echo an empty result
     if (PHPWS_Error::logIfError($results)) {
         echo json_encode(array());
         exit;
     }
     $resultObjects = array();
     foreach ($results as $row) {
         $obj = new stdClass();
         $obj->banner_id = $row['banner_id'];
         $obj->name = $row['first_name'] . ' ' . $row['last_name'];
         $obj->username = $row['username'];
         $resultObjects[] = $obj;
     }
     $jsonResult = json_encode($resultObjects);
     //test($jsonResult,1);
     echo $jsonResult;
     exit;
     // NB: using setContent adds escape characters to quotes in the JSON string... WRONG.
     //$context->setContent(json_encode($jsonResult));
 }

 public function execute()
 {
     PHPWS_Core::initModClass('hms', 'StudentFactory.php');
     $this->data = array();
     $query = "SELECT hms_assignment.id, hms_assignment.banner_id, hms_assignment.asu_username, hms_new_application.cell_phone, hms_room.room_number, hms_floor.floor_number, hms_residence_hall.hall_name FROM hms_assignment LEFT JOIN (SELECT username, MAX(term) AS mterm FROM hms_new_application GROUP BY username) AS a ON hms_assignment.asu_username = a.username LEFT JOIN hms_new_application ON a.username = hms_new_application.username AND a.mterm = hms_new_application.term LEFT JOIN hms_bed ON hms_assignment.bed_id = hms_bed.id LEFT JOIN hms_room ON hms_bed.room_id = hms_room.id LEFT JOIN hms_floor ON hms_room.floor_id = hms_floor.id LEFT JOIN hms_residence_hall ON hms_floor.residence_hall_id = hms_residence_hall.id WHERE ( hms_assignment.term = {$this->term}) ORDER BY hms_residence_hall.id ASC";
     $results = PHPWS_DB::getAll($query);
     if (PHPWS_Error::logIfError($results)) {
         throw new DatabaseException($results->toString());
     }
     foreach ($results as $result) {
         try {
             $student = StudentFactory::getStudentByBannerId($result['banner_id'], $this->term);
         } catch (Exception $e) {
             $this->data[] = array($result['hall_name'], $result['floor_number'], $result['room_number'], 'ERROR', 'ERROR', 'ERROR', $result['cell_phone'], $result['asu_username'] . "@appstate.edu");
             continue;
         }
         $this->data[] = array($result['hall_name'], $result['floor_number'], $result['room_number'], $student->getLastName(), $student->getFirstName(), $result['banner_id'], $result['cell_phone'], $result['asu_username'] . "@appstate.edu");
     }
 }

 /**
  * Returns an array of Faculty objects for the given department.
  * @param Department $department
  * @return Array List of faculty for requested department.
  */
 public static function getFacultyByDepartmentAssoc(Department $department)
 {
     $sql = "SELECT intern_faculty.* FROM intern_faculty JOIN intern_faculty_department ON intern_faculty.id = intern_faculty_department.faculty_id WHERE intern_faculty_department.department_id = {$department->getId()} ORDER BY last_name ASC";
     $result = \PHPWS_DB::getAll($sql);
     return $result;
 }

/**
 * boost install file for users
 *
 * @author Matthew McNaney <mcnaney at gmail dot com>
 * @version $Id$
 */
function users_install(&$content)
{
    PHPWS_Core::initModClass('users', 'Users.php');
    PHPWS_Core::initModClass('users', 'Action.php');
    PHPWS_Core::configRequireOnce('users', 'config.php');
    if (isset($_REQUEST['module']) && $_REQUEST['module'] == 'branch') {
        $db = new PHPWS_DB();
        PHPWS_Settings::clear();
        if (!createLocalAuthScript()) {
            $content[] = 'Could not create authorization script.';
            return false;
        }
        Branch::loadHubDB();
        $db = new PHPWS_DB('mod_settings');
        $db->addWhere('module', 'users');
        $db->addWhere('setting_name', 'site_contact');
        $db->addColumn('small_char');
        $site_contact = $db->select('one');
        $db = new PHPWS_DB('users');
        $sql = 'select a.password, b.* from user_authorization as a, users as b where b.deity = 1 and a.username = b.username';
        $deities = $db->getAll($sql);
        if (PHPWS_Error::isError($deities)) {
            PHPWS_Error::log($deities);
            $content[] = dgettext('users', 'Could not access hub database.');
            Branch::restoreBranchDB();
            return FALSE;
        } elseif (empty($deities)) {
            $content[] = dgettext('users', 'Could not find any hub deities.');
            Branch::restoreBranchDB();
            return FALSE;
        } else {
            Branch::restoreBranchDB();
            PHPWS_Settings::set('users', 'site_contact', $site_contact);
            PHPWS_Settings::save('users');
            $auth_db = new PHPWS_DB('user_authorization');
            $user_db = new PHPWS_DB('users');
            $group_db = new PHPWS_DB('users_groups');
            foreach ($deities as $deity) {
                $auth_db->addValue('username', $deity['username']);
                $auth_db->addValue('password', $deity['password']);
                $result = $auth_db->insert();
                if (PHPWS_Error::isError($result)) {
                    PHPWS_Error::log($result);
                    $content[] = dgettext('users', 'Unable to copy deity login to branch.');
                    continue;
                }
                unset($deity['password']);
                $user_db->addValue($deity);
                $result = $user_db->insert();
                if (PHPWS_Error::isError($result)) {
                    PHPWS_Error::log($result);
                    $content[] = dgettext('users', 'Unable to copy deity users to branch.');
                    Branch::loadBranchDB();
                    return FALSE;
                }
                $group_db->addValue('active', 1);
                $group_db->addValue('name', $deity['username']);
                $group_db->addValue('user_id', $result);
                if (PHPWS_Error::logIfError($group_db->insert())) {
                    $content[] = dgettext('users', 'Unable to copy deity user group to branch.');
                    Branch::loadBranchDB();
                    return FALSE;
                }
                $group_db->reset();
                $auth_db->reset();
                $user_db->reset();
            }
            $content[] = dgettext('users', 'Deity users copied to branch.');
        }
        return TRUE;
    }
    if (!createLocalAuthScript()) {
        $content[] = 'Could not create local authorization script.';
        return false;
    }
    $authorize_id = PHPWS_Settings::get('users', 'local_script');
    $user = new PHPWS_User();
    $content[] = '<hr />';
    return TRUE;
}

 function loadUserData()
 {
     $sql = 'SELECT * FROM ' . $this->getTableName() . ' WHERE id=\'' . $this->_dataId . '\'';
     $result = PHPWS_DB::getAll($sql);
     $this->_userData = $result[0];
     $this->_position = 0;
 }

/**
 * @version $Id$
 * @author Adam Morton
 * @author Steven Levin
 */
function export($formId = NULL)
{
    if (!isset($formId)) {
        $message = dgettext('phatform', 'No form ID was passed');
        return new PHPWS_Error('phatform', 'export()', $message, 'continue', PHAT_DEBUG_MODE);
    }
    $exportDir = PHPWS_HOME_DIR . 'files/phatform/export/';
    $path = $exportDir;
    clearstatcache();
    if (!is_dir($path)) {
        if (is_writeable($exportDir)) {
            PHPWS_File::makeDir($path);
        } else {
            return PHPWS_Error::get(PHATFORM_EXPORT_PATH, 'phatform', 'Export.php::export()');
        }
    } elseif (!is_writeable($path)) {
        return PHPWS_Error::get(PHATFORM_EXPORT_PATH, 'phatform', 'Export.php::export()');
    }
    $sql = 'SELECT * FROM mod_phatform_form_' . $formId;
    $result = PHPWS_DB::getAll($sql);
    if (sizeof($result) > 0) {
        $data = '';
        foreach ($result[0] as $key => $value) {
            if ($key != 'position') {
                $data .= $key . "\t";
            }
        }
        foreach ($result as $entry) {
            $data .= "\n";
            foreach ($entry as $key => $value) {
                if ($key != 'position') {
                    if ($key == 'updated') {
                        $value = date(PHPWS_DATE_FORMAT . ' ' . PHPWS_TIME_FORMAT, $value);
                    } else {
                        $value = str_replace("\t", " ", $value);
                        $value = str_replace("\r\n", '', $value);
                        $value = str_replace("\n", '', $value);
                        $temp = $value;
                        if (is_array($temp)) {
                            $value = implode(',', $temp);
                        } else {
                            if (preg_match('/^[ao]:\\d+:/', $temp)) {
                                // unserialize data
                                $unsTemp = unserialize($temp);
                                if (is_array($unsTemp)) {
                                    $value = implode(',', $unsTemp);
                                } else {
                                    $value = $unsTemp;
                                }
                            }
                        }
                    }
                    $data .= "{$value}\t";
                }
            }
        }
    }
    $filename = 'form_' . $formId . '_export.' . time() . '.csv';
    $file = fopen($path . $filename, 'w');
    fwrite($file, $data);
    fclose($file);
    $goCode = 'zip -qmj ' . $path . $filename . '.zip ' . $path . $filename;
    system($goCode);
    $filename = $filename . '.zip';
    $filepath = 'files/phatform/export/' . $filename;
    header('Pragma: public');
    header('Expires: 0');
    header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
    header('Last-Modified: ' . gmdate('D, d M Y H:i:s', filemtime($filepath)) . ' GMT');
    header('Cache-Control: private', false);
    header('Content-Type: application/zip');
    header('Content-Disposition: attachment; filename="' . $filename . '"');
    header('Content-Transfer-Encoding: binary');
    header('Content-Length: ' . filesize($filepath));
    header('Connection: close');
    readfile($filepath);
    exit;
}

 /**
  *
  *
  *
  *
  */
 function setIncomplete()
 {
     $sql = 'SELECT count(id) FROM ' . $this->getFormTable() . " WHERE position!='-1'";
     $result = PHPWS_DB::getAll($sql);
     if (PHPWS_Error::isError($result)) {
         return $result;
     }
     $this->_incompleteEntries = $result[0]['count(id)'];
 }

 public static function send_roommate_reminder_emails($term)
 {
     PHPWS_Core::initModClass('hms', 'HMS_Bed.php');
     PHPWS_Core::initModclass('hms', 'StudentFactory.php');
     // Get a list of outstanding roommate requests, send them reminder emails
     $query = "select hms_lottery_reservation.* FROM hms_lottery_reservation\n                LEFT OUTER JOIN (SELECT asu_username FROM hms_assignment WHERE term={$term} AND lottery = 1) as foo ON hms_lottery_reservation.asu_username = foo.asu_username\n                WHERE foo.asu_username IS NULL\n                AND hms_lottery_reservation.expires_on > " . time();
     $result = PHPWS_DB::getAll($query);
     if (PEAR::isError($result)) {
         PHPWS_Error::log($result);
         test($result, 1);
     }
     $year = Term::toString($term) . ' - ' . Term::toString(Term::getNextTerm($term));
     foreach ($result as $row) {
         $student = StudentFactory::getStudentByUsername($row['asu_username'], $term);
         $requestor = StudentFactory::getStudentByUsername($row['requestor'], $term);
         $bed = new HMS_Bed($row['bed_id']);
         $hall_room = $bed->where_am_i();
         HMS_Email::send_lottery_roommate_reminder($row['asu_username'], $student->getName(), $row['expires_on'], $requestor->getName(), $hall_room, $year);
         HMS_Activity_Log::log_activity($row['asu_username'], ACTIVITY_LOTTERY_ROOMMATE_REMINDED, 'hms');
     }
 }

 /**
  * Creates a 2 dimensional array of items from the current table.
  *
  * This function creates an sql statement based on variables currently set in
  * this object.  The statement is then executed on the current table and it's
  * result is returned as the list of current items.
  *
  * @param  boolean $filterGroups Flag whether or not to filter items that are not
  *                 associated with a users group
  * @return mixed   A 2-dimentional array of items or FALSE on failure.
  * @access public
  * @see    getList()
  */
 function getItems($ids = NULL, $filterGroups = FALSE, $everything = FALSE)
 {
     if (isset($this->_table)) {
         $table = $this->_table;
     } else {
         $table = $this->_tables[$this->listName];
     }
     /* Make sure the table name is set before continuing */
     if (isset($table)) {
         if (is_array($this->_listColumns[$this->listName])) {
             if ($everything) {
                 $sql = 'SELECT *';
             } else {
                 $sql = 'SELECT id, ';
                 foreach ($this->_listColumns[$this->listName] as $listColumn => $listLabel) {
                     if ($listColumn != 'id') {
                         $sql .= $listColumn . ', ';
                     }
                 }
                 $sql = substr($sql, 0, strlen($sql) - 2);
             }
             $sql .= ' FROM ' . $table;
         } else {
             $error = new PHPWS_Error('core', 'PHPWS_Manager:getItems()', 'Format error in config file.', 'exit', 1);
             $error->message(NULL);
         }
     } else {
         $error = new PHPWS_Error('core', 'PHPWS_Manager:getItems()', 'Table not set!', 'exit', 1);
         $error->message(NULL);
     }
     $whereFlag = FALSE;
     $sort = $this->getSort();
     if (isset($sort)) {
         $sql .= $sort;
         $whereFlag = TRUE;
     }
     if (is_array($ids) && sizeof($ids) > 0) {
         if ($whereFlag) {
             $sql .= ' AND (';
         } else {
             $sql .= ' WHERE (';
         }
         foreach ($ids as $id) {
             $sql .= " id='{$id}' OR ";
         }
         $sql = substr($sql, 0, strlen($sql) - 4) . ')';
     }
     $order = $this->getOrder();
     if (isset($order)) {
         $sql .= $order;
     }
     /* Set associative mode for db and execute query */
     $result = PHPWS_DB::getAll($sql);
     if ($filterGroups) {
         $size = sizeof($result);
         for ($i = 0; $i < $size; $i++) {
             $groups = unserialize($result[$i]['groups']);
             if (is_array($groups)) {
                 foreach ($groups as $value) {
                     if (!$_SESSION['OBJ_user']->userInGroup($value)) {
                         unset($result[$i]);
                     }
                 }
             }
         }
         $result = PHPWS_Array::reIndex($result);
     }
     /* Return result of query */
     return $result;
 }

 private function chooseWinner($class, $gender)
 {
     $query = "SELECT * FROM hms_new_application JOIN hms_lottery_application ON hms_new_application.id = hms_lottery_application.id\n                    LEFT OUTER JOIN (SELECT asu_username FROM hms_assignment WHERE term={$this->term}) as foo ON hms_new_application.username = foo.asu_username\n                    WHERE foo.asu_username IS NULL\n                    AND hms_lottery_application.invited_on IS NULL\n                    AND hms_new_application.term = {$this->term}\n                    AND hms_lottery_application.magic_winner = 0\n                    AND hms_lottery_application.special_interest IS NULL\n                    AND hms_new_application.gender = {$gender}\n                    AND hms_new_application.username NOT IN (SELECT username FROM hms_learning_community_applications JOIN hms_learning_community_assignment ON hms_learning_community_applications.id = hms_learning_community_assignment.application_id WHERE term = {$this->term} and state IN ('confirmed', 'selfselect-assigned')) ";
     $term_year = Term::getTermYear($this->term);
     if ($class == CLASS_SOPHOMORE) {
         // Choose a rising sophmore (summer 1 thru fall of the previous year, plus spring of the same year)
         $query .= 'AND (application_term = ' . ($term_year - 1) . '20 ';
         $query .= 'OR application_term = ' . ($term_year - 1) . '30 ';
         $query .= 'OR application_term = ' . ($term_year - 1) . '40 ';
         $query .= 'OR application_term = ' . $term_year . '10';
         $query .= ') ';
     } else {
         if ($class == CLASS_JUNIOR) {
             // Choose a rising jr
             $query .= 'AND (application_term = ' . ($term_year - 2) . '20 ';
             $query .= 'OR application_term = ' . ($term_year - 2) . '30 ';
             $query .= 'OR application_term = ' . ($term_year - 2) . '40 ';
             $query .= 'OR application_term = ' . ($term_year - 1) . '10';
             $query .= ') ';
         } else {
             // Choose a rising senior or beyond
             $query .= 'AND application_term <= ' . ($term_year - 2) . '10 ';
         }
     }
     $result = PHPWS_DB::getAll($query);
     if (PHPWS_Error::logIfError($result)) {
         throw new DatabaseException($result->toString());
     }
     if (sizeof($result) < 1) {
         return null;
     }
     // Randomly pick a student from result
     $winningRow = $result[mt_rand(0, sizeof($result) - 1)];
     return $winningRow;
 }

 private function fullNameSearch($string)
 {
     $sql = $this->getFuzzyTextSql($string);
     $results = \PHPWS_DB::getAll($sql);
     $students = array();
     foreach ($results as $result) {
         try {
             $students[] = $this->studentIdSearch($result['banner_id']);
         } catch (\Intern\Exception\StudentNotFoundException $e) {
             // Skip any students that are returned from the database, but don't exist
             // in the student info web service
             continue;
         }
     }
     return $students;
 }