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
}
