function getManualMailingListMembers($list) { $data = array(); $sql = "SELECT * FROM pzrattr,spriden WHERE spriden_pidm=pzrattr_pidm AND spriden_change_ind is null AND pzrattr_attr_code='{$list}' AND pzrattr_manual='Y' ORDER BY spriden_last_name,spriden_first_name"; if ($results = $GLOBALS['BANNER']->Execute($sql)) { while ($row = $results->FetchRow()) { $row = PSUTools::cleanKeys(array('pzvattr_', 'spriden_'), '', $row); $data[] = $row; } //end while } //end if return $data; }
/** * BannerPrepMax * * getAllCurrentTRCourses Returns all transfer courses seen to date per institution using ceeb number. * * @since version 1.0.0 * @params string ceeb - which ceeb number * @access public * @return array */ function getAllCurrentTRCourses($ceeb) { $data = array(); $query = "SELECT SHBTATC_SBGI_CODE, \r\n\t\t\t\t\t\t\t\t\t\tSHBTATC_PROGRAM, \r\n\t\t\t\t\t\t\t\t\t\tSHBTATC_TLVL_CODE, \r\n\t\t\t\t\t\t\t\t\t\tSHBTATC_SUBJ_CODE_TRNS, \r\n\t\t\t\t\t\t\t\t\t\tSHBTATC_CRSE_NUMB_TRNS, \r\n\t\t\t\t\t\t\t\t\t\tSHBTATC_TERM_CODE_EFF_TRNS, \r\n\t\t\t\t\t\t\t\t\t\tSHBTATC_ACTIVITY_DATE, \r\n\t\t\t\t\t\t\t\t\t\tSHBTATC_TRNS_TITLE, \r\n\t\t\t\t\t\t\t\t\t\tSHBTATC_TRNS_LOW_HRS, \r\n\t\t\t\t\t\t\t\t\t\tSHBTATC_TRNS_HIGH_HRS, \r\n\t\t\t\t\t\t\t\t\t\tSHBTATC_TRNS_REVIEW_IND, \r\n\t\t\t\t\t\t\t\t\t\tSHBTATC_TAST_CODE, \r\n\t\t\t\t\t\t\t\t\t\tSHBTATC_TRNS_CATALOG, \r\n\t\t\t\t\t\t\t\t\t\tSHBTATC_TGRD_CODE_MIN, \r\n\t\t\t\t\t\t\t\t\t\tSHBTATC_GROUP, \r\n\t\t\t\t\t\t\t\t\t\tSHBTATC_GROUP_PRIMARY_IND, \r\n\t\t\t\t\t\t\t\t\t\tSHRTATC_SBGI_CODE SHBTATC_RSBGI_CODE, \r\n\t\t\t\t\t\t\t\t\t\tSHRTATC_PROGRAM SHBTATC_RPROGRAM, \r\n\t\t\t\t\t\t\t\t\t\tSHRTATC_TLVL_CODE SHBTATC_RTLVL_CODE, \r\n\t\t\t\t\t\t\t\t\t\tSHRTATC_SUBJ_CODE_TRNS SHBTATC_RSUBJ_CODE_TRNS, \r\n\t\t\t\t\t\t\t\t\t\tSHRTATC_CRSE_NUMB_TRNS SHBTATC_RCRSE_NUMB_TRNS, \r\n\t\t\t\t\t\t\t\t\t\tSHRTATC_TERM_CODE_EFF_TRNS SHBTATC_RTERM_CODE_EFF_TRNS, \r\n\t\t\t\t\t\t\t\t\t\tSHRTATC_SEQNO SHBTATC_RSEQNO, \r\n\t\t\t\t\t\t\t\t\t\tSHRTATC_ACTIVITY_DATE SHBTATC_RACTIVITY_DATE, \r\n\t\t\t\t\t\t\t\t\t\tSHRTATC_CONNECTOR SHBTATC_R_CONNECTOR, \r\n\t\t\t\t\t\t\t\t\t\tSHRTATC_INST_LPAREN_CONN SHBTATC_R_INST_LPAREN_CONN, \r\n\t\t\t\t\t\t\t\t\t\tSHRTATC_SUBJ_CODE_INST SHBTATC_RSUBJ_CODE_INST, \r\n\t\t\t\t\t\t\t\t\t\tSHRTATC_CRSE_NUMB_INST SHBTATC_RCRSE_NUMB_INST, \r\n\t\t\t\t\t\t\t\t\t\tSHRTATC_INST_TITLE SHBTATC_RINST_TITLE, \r\n\t\t\t\t\t\t\t\t\t\tSHRTATC_INST_CREDITS_USED SHBTATC_RINST_CREDITS_USED, \r\n\t\t\t\t\t\t\t\t\t\tSHRTATC_INST_RPAREN SHBTATC_RINST_RPAREN, \r\n\t\t\t\t\t\t\t\t\t\tSHRTATC_GROUP SHBTATC_RGROUP\r\n\t\t\t\t\t\t\t\t\tFROM SHBTATC, SHRTATC \r\n\t\t\t\t\t\t\t\t\tWHERE SHBTATC_TERM_CODE_EFF_TRNS = SHRTATC_TERM_CODE_EFF_TRNS \r\n\t\t\t\t\t\t\t\t\t\tAND SHBTATC_CRSE_NUMB_TRNS = SHRTATC_CRSE_NUMB_TRNS\r\n\t\t\t\t\t\t\t\t\t\tAND SHBTATC_TLVL_CODE = SHRTATC_TLVL_CODE\r\n\t\t\t\t\t\t\t\t\t\tAND SHBTATC_SBGI_CODE = SHRTATC_SBGI_CODE\r\n\t\t\t\t\t\t\t\t\t\tAND ((SHBTATC_GROUP = SHRTATC_GROUP)OR(SHBTATC_GROUP IS NULL AND SHRTATC_GROUP IS NULL))\r\n\t\t\t\t\t\t\t\t\t\tAND SHBTATC_SBGI_CODE='{$ceeb}'\r\n\t\t\t\t\t\t\t\t\tORDER BY SHBTATC_TRNS_TITLE, SHBTATC_SUBJ_CODE_TRNS, SHBTATC_CRSE_NUMB_TRNS"; $results = $this->_ADOdb->Execute($query); if ($results) { while ($row = $results->FetchRow()) { $data[] = PSUTools::cleanKeys('shbtatc_', 'r_', $row); } // end while } // end if return $data; }
/** * getResidentialData * * returns an array of student room assignments * * @since version 1.0.0 * @param string $pid Banner pidm * @param string $termcode Termcode to check (default is the globally defined termcode) * @return array */ function getResidentialData($pidm, $termcode = '') { $data = array(); $termcode = $termcode ? $termcode : $this->termcode; if (!is_array($termcode)) { $terms = array($termcode); } else { $terms = $termcode; } $sql = "SELECT stvbldg_desc, slrrasg_room_number, slrrasg_bldg_code, slrrasg_term_code\n\t\t\t\t\t\tFROM slrrasg, stvbldg\n\t\t\t\t\tWHERE slrrasg_pidm = " . $pidm . "\n\t\t and slrrasg_bldg_code = stvbldg_code\n\t\t and slrrasg_ascd_code = 'AC'\n\t\t and slrrasg_term_code in ('" . implode("','", $terms) . "')"; if ($results = $this->_ADOdb->Execute($sql)) { for ($num = 0; $row = $results->FetchRow(); $num++) { $row = PSUTools::cleanKeys(array('stvbldg_', 'slrrasg_'), 'r_', $row); $data[$row['r_term_code']] = $row; } //end for } //end if return $data; }
/** * search * * returns matching persons by a given name * * @since version 1.0.5 * @access public * @param string $name Name to be searched * @param string $type The type of search to conduct * @return array */ function search($name, $type = 'name') { $joins = ''; // no additional joins or selects by default $select = ''; $name = stripslashes($name); $people = array(); if (preg_match('/[0-9]{9}/', $name)) { $where = " s1.spriden_id='{$name}'"; } elseif (is_int($name) || ctype_digit($name)) { $name = (int) $name; $where = "s1.spriden_pidm = {$name}"; } elseif ($type == 'email') { $name = strtolower($name); $joins = 'LEFT JOIN goremal ON s1.spriden_pidm = goremal_pidm'; $where = "goremal_email_address = " . $this->_ADOdb->qstr($name) . " AND goremal_status_ind = 'A'"; $select = 'goremal_email_address,goremal_emal_code'; } else { $name = preg_replace("/[^A-Za-z0-9\\.%,_]/", "", $name); $name = str_replace('*', '%', $name); $name = strtoupper($name); $name = explode(',', $name); $where = " s1.spriden_search_last_name LIKE " . $this->_ADOdb->qstr($name[0]); $additional = "SELECT 1 FROM spriden s2 WHERE s2.spriden_pidm = s1.spriden_pidm AND s2.spriden_search_last_name LIKE " . $this->_ADOdb->qstr($name[0]); if (count($name) > 1) { $where .= " AND s1.spriden_search_first_name like " . $this->_ADOdb->qstr($name[1]); $additional .= " AND s2.spriden_search_first_name like " . $this->_ADOdb->qstr($name[1]); $where = "((" . $where . ") OR EXISTS(" . $additional . "))"; } else { $where = "((" . $where . ") OR EXISTS(" . $additional . ") OR (LOWER(gobtpac_ldap_user) like " . strtolower($this->_ADOdb->qstr($name[0])) . " OR LOWER(gobtpac_ldap_user) like " . strtolower($this->_ADOdb->qstr('app.' . $name[0])) . "))"; } //end else } //end else $name_count = 0; if ($select) { $select = ',' . $select; // prepend comma for column list } $query = "SELECT s1.spriden_pidm,s1.spriden_id,s1.spriden_first_name,s1.spriden_mi,s1.spriden_last_name,gobtpac_external_user,gobtpac_ldap_user {$select} FROM spriden s1 LEFT OUTER JOIN gobtpac ON gobtpac_pidm=s1.spriden_pidm {$joins} WHERE s1.spriden_change_ind is NULL AND {$where} ORDER BY s1.spriden_last_name,s1.spriden_first_name,s1.spriden_mi,s1.spriden_id"; if ($results = $this->_ADOdb->Execute($query)) { while ($row = $results->FetchRow()) { $row = PSUTools::cleanKeys(array('spriden_', 'gobtpac_', 'goremal_'), 'r_', $row); $sql = "SELECT DISTINCT spriden_last_name FROM spriden WHERE spriden_pidm = :pidm AND spriden_last_name <> :last_name"; $row['last_names'] = $this->_ADOdb->GetCol($sql, array('pidm' => $row['r_pidm'], 'last_name' => $row['r_last_name'])); $people[] = $row; } //end while } //end if return $people; }
/** * getMinors * * get Minors * * @access public * @param string $termcode the term code for the courses * @return array $data of minors with fields r_code, r_desc */ function getMinors($termcode = '') { $termcode = $termcode ? $termcode : $this->termcode; $data = array(); $query = "SELECT stvmajr_code, stvmajr_desc\n\t\t\t\t\t\t\tFROM stvmajr, sorcmjr s1\n\t\t\t\t\t\t\tWHERE stvmajr_valid_minor_ind='Y'\n\t\t\t\t\t\t\tORDER BY stvmajr_desc"; if ($results = $this->_ADOdb->Execute($query)) { while ($row = $results->FetchRow()) { $row = PSUTools::cleanKeys(array('stvmajr_'), array('r_'), $row); $data[$row['r_code']] = $row; } //end while } //end if return $data; }
/** * getSimilarNames * * getSimilarNames returns a list SPRIDEN records of people from Banner that have similar names to the passed in parameters * * @since version 1.0.0 * @access public * @param string $first_name First Name * @param string $middle_name Middle Name * @param string $last_name Last Name * @return array */ function getSimilarNames($first_name, $middle_name, $last_name) { $data = array(); $sql = "SELECT * \n\t\t FROM spriden,spbpers \n\t\t WHERE spriden_pidm=spbpers_pidm \n\t\t AND spriden_change_ind is null \n\t\t AND spriden_first_name like '" . substr($first_name, 0, 1) . "%' \n\t\t AND spriden_last_name like '" . substr($last_name, 0, 10) . "%' \n\t\t AND spbpers_ssn IS NULL"; if ($results = $this->_ADOdb->Execute($sql)) { while ($row = $results->FetchRow()) { $data[] = PSUTools::cleanKeys('spriden_', 'r_', $row); } //end while } //end if return $data; }
/** * unsubscribeAll * * unsubscribe a user from all lists * * @param string $emailaddr email address of user */ function unsubscribeAll($emailaddr) { // loop unsubscribe user through all listIds for that user. $sql = "SELECT subscriberid, listid FROM {$this->table['list_subscribers']} WHERE emailaddress='{$emailaddr}'"; if ($results = $this->db->Execute($sql)) { while ($row = $results->FetchRow()) { PSUTools::cleanKeys('', '', $row); $this->dropMember($emailaddr, $row['listid']); } } }
/** * noLoad * * function to handle the case where there are no files to load * called in processIntoTables function * * @since version 1.0.0 * @access public **/ function noLoad() { $c_txbepay_no_load = "SELECT *\n\t\t\t\t\t\tFROM txbepay\n\t\t\t\t\t\tWHERE txbepay_load_date is null\n\t\t\t\t\t\t\tAND txbepay_load_status is null\n\t\t\t\t\t\t\tAND txbepay_pidm is not null\n\t\t\t\t\t\t\tAND (\n\t\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\t\t(txbepay_trans_type=1 OR txbepay_trans_type=2)\n\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\ttxbepay_trans_status<>1\n\t\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\t\tOR\n\t\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\t\ttxbepay_trans_type=3\n\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t(txbepay_trans_status<>5 AND txbepay_trans_status<>6 AND txbepay_trans_status<>8)\n\t\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\t)"; if ($cv_trans = $this->db->Execute($c_txbepay_no_load)) { while ($row = $cv_trans->FetchRow()) { $row = PSUTools::cleanKeys('txbepay_', 'r_', $row); if (!$this->db->Execute("UPDATE txbepay SET txbepay_load_status='no load necessary' WHERE txbepay_trans_id=" . $row['r_trans_id'] . "")) { throw new PSUECommerceException(PSUECommerceException::LEGACY_TRANSACTION_STATUS, ': Could not set transaction [trans_id: ' . $row['r_trans_id'] . '] to "no load necessary"'); } //end if } } }
/** *setApplication * *updates all variables with the information for the current application * *@param string $id *@param string $user_id */ function setApplication($id = '', $user_id = '') { if (!$user_id) { $user_id = $this->user_id; } if ($id) { $app = $this->db->GetRow("SELECT * FROM {$this->table['application']} WHERE user_id={$user_id} AND id={$id}"); } else { $app = $this->db->GetRow("SELECT * FROM {$this->table['application']} WHERE user_id={$user_id} AND application_status='U'"); } //end else $app = PSUTools::cleanKeys('', '', $app); if (count($app) > 0) { $this->id = $app['id']; $this->status = $app['application_status']; $this->percent = ''; } //end if $this->setUser($user_id); }
/** *setForm * * sets the form to new status * *@param string $id *@param integer $days_to_complete *@param boolean $seasonal *@param string $user_id */ function setForm($id = '', $days_to_complete = 0, $seasonal = true, $user_id = '') { $discovered_id = false; if (!$user_id) { $user_id = $this->user_id; //$this->error.='no user_id passed into setForm. user_id is now '.$user_id.' '; } //end if if ($id) { $form = $this->checkUserForm($id, $user_id, $days_to_complete, $seasonal); if (!$form) { $form = $this->findUserForm($user_id, $days_to_complete, $seasonal); } } else { $form = $this->findUserForm($user_id, $days_to_complete, $seasonal); } //end else $form = PSUTools::cleanKeys('', '', $form); if (count($form) > 0) { $this->id = $form['id']; $this->status = $form['form_status']; $this->percent = ''; } //end if if ($previous_submission = $this->getPreviousSubmission($user_id, $this->id)) { $this->previous_submission_id = $previous_submission; } if ($this->status != "U") { $this->view_only = true; } $this->setUser($user_id); }
/** * select * * select records with the passed parameters. combines multiple records with identical primary * keys into a single record. * * @since version 1.0.0 * @access public * @param mixed $params Parameter list (either query string or array) * @return mixed */ function select($params = array()) { $params = $this->buildParams($params, false); $where = ''; $args = array('script' => $this->script); if ($params['primary_field']) { $where .= "AND primary_field = :primary_field AND primary_field_data = :primary_field_data "; $args['primary_field'] = $params['primary_field']; $args['primary_field_data'] = $params['primary_field_data'] && $params['primary_field_data'] != "NULL" ? $params['primary_field_data'] : null; } //end if if ($params['field']) { $where .= "AND field = :field AND field_data = :field_data "; $args['field'] = $params['field']; $args['field_data'] = $params['field_data'] && $params['field_data'] != "NULL" ? $params['field_data'] : null; } //end if if ($params['flag'] === null) { $where .= "AND flag IS NULL "; } else { $where .= "AND flag = :flag "; $args['flag'] = $params['flag'] && $params['flag'] != "NULL" ? $params['flag'] : null; } //end else $data = array(); $sql = "SELECT * FROM {$this->table['utility']} WHERE script = :script {$where}"; if ($results = $this->db->Execute($sql, $args)) { while ($row = $results->FetchRow()) { $row = PSUTools::cleanKeys('', '', $row); if (!isset($data[$row['primary_field_data']])) { $data[$row['primary_field_data']] = array(); } //end if $data[$row['primary_field_data']][$row['primary_field']] = $row['primary_field_data']; $data[$row['primary_field_data']][$row['field']] = $row['field_data']; $data[$row['primary_field_data']][$row['field'] . '_flag'] = $row['flag']; } //end while } //end if return $data; }
/** * getSectionData * * get the section data of a course * * @access public * @param integer $crn crn of course * @param string $termcode termcode of course, defaults to '' * @return array $course array of data about course section with fields r_attributes, and r_text */ function getSectionData($crn, $termcode = '') { $termcode = $termcode ? $termcode : $this->termcode; $course = array(); $query = "SELECT *\n\t\t\t\t\t\t\tFROM ssbsect,scbcrse s1\n\t\t\t\t\t\t WHERE ssbsect_crn={$crn}\n\t\t\t\t\t\t\t AND ssbsect_term_code='{$termcode}'\n\t\t\t\t\t\t\t AND ssbsect_subj_code=s1.scbcrse_subj_code\n\t\t\t\t\t\t\t AND ssbsect_crse_numb=s1.scbcrse_crse_numb\n\t\t\t\t\t\t\t AND s1.scbcrse_eff_term=(SELECT max(s2.scbcrse_eff_term) FROM scbcrse s2 WHERE s2.scbcrse_subj_code=s1.scbcrse_subj_code AND s2.scbcrse_crse_numb=s1.scbcrse_crse_numb AND s2.scbcrse_eff_term<='{$termcode}')"; if ($row = $this->_ADOdb->GetRow($query)) { $row = PSUTools::cleanKeys(array('ssbsect_', 'scbcrse_'), array('r_', 'r_'), $row); $row['r_attributes'] = $this->getCourseAttributes($row['r_subj_code'], $row['r_crse_numb'], $termcode); $row['r_text'] = $this->getCourseText($row['r_subj_code'], $row['r_crse_numb'], $termcode); $course = $row; } //end if return $course; }
/** * getActiveTerms * * returns an array of active LDI terms * * @since version 1.0.0 * @access public * @return array */ function getActiveTerms() { $data = array(); $sql = "begin :cursorvar := gb_integ_config.f_query_all(p_sqpr_code => :sqpr_code,p_icsn_code => :icsn_code); end;"; if ($results = $this->db->ExecuteCursor($sql, 'cursorvar', array('sqpr_code' => 'ELEARNING', 'icsn_code' => 'ACTIVE_TERM'))) { while ($row = $results->FetchRow()) { $row = PSUTools::cleanKeys('goriccr_', 'r_', $row); $data[] = $row['r_value']; } //end while } //end if return $data; }
function getAllUserIdentifiers($input_id, $input_type) { return PSUTools::cleanKeys('', '', $this->_ADOdb->GetRow("SELECT * FROM {$this->_idm_tables['person']} WHERE {$input_type}='{$input_id}'")); }
/** *setApplication * *sets application specific vars to a specific applications information * *@access public *@param string $id *@param string @user_id */ function setApplication($id = '', $user_id = '') { if ($user_id) { $this->setUser($user_id); } if ($id) { $app = $this->db->GetRow("SELECT * FROM {$this->table['application']} WHERE user_id={$this->user_id} AND id={$id}"); } else { $app = $this->db->GetRow("SELECT * FROM {$this->table['application']} WHERE user_id={$this->user_id} AND application_status='U'"); } //end else $app = PSUTools::cleanKeys('', '', $app); if (count($app) > 0) { $this->id = $id; if ($app['application_status'] != 'U') { $this->fields = $this->getFields($this->user_id, false, $this->id); $this->field_values = $this->getFields($this->user_id, true, $this->id); $this->repeating_fields = $this->getRepeatingFields($this->id); $this->buildFriendlyPhone(); } //end if } //end if }