PHP PSU::db方法代码示例

 /**
  * retrieve receivables for a person
  */
 public function get()
 {
     $args = array('pidm' => $this->pidm);
     $sql = "SELECT * FROM tbraccd WHERE tbraccd_pidm = :pidm";
     $rset = \PSU::db('banner')->Execute($sql, $args);
     return $rset ? $rset : array();
 }

 /**
  * retrieve phones for a person
  */
 public function get()
 {
     $args = array('wp_id' => $this->wp_id);
     $sql = "\n\t\t\tSELECT * \n\t\t\t  FROM person_phone\n\t\t\t WHERE wp_id = ?\n\t\t\t ORDER BY id DESC\n\t\t";
     $rset = \PSU::db('emergency_notification')->Execute($sql, $args);
     return $rset ? $rset : array();
 }

 public function get()
 {
     $sql = "\n\t\t\tSELECT *\n\t\t\t  FROM (\n\t\t\t\t\t\t\tSELECT f.*,\n\t\t\t\t\t\t\t\t\t\t Dense_Rank() OVER (PARTITION BY file_type ORDER BY id DESC) rank\t\n\t\t\t\t\t\t\t\tFROM payment_plan_feed f\n\t\t\t\t\t\t\t ORDER BY id DESC\n\t\t\t\t\t\t )\n\t\t\t WHERE file_type = :file_type\n\t\t\t   AND rank <= :num\n\t\t";
     $args = array('num' => $this->num, 'file_type' => $this->type);
     $results = \PSU::db('banner')->GetAll($sql, $args);
     return $results;
 }

 /**
  * prepares arguments for DML
  */
 protected function _prep_args()
 {
     // this is the data prepared for binding.
     // these fields are ordered as they are in the table
     $args = array('the_id' => $this->id, 'psu_id' => $this->psu_id, 'name' => $this->name, 'report_group' => $this->report_group, 'contract_balance' => $this->contract_balance, 'account_status' => $this->account_status, 'record_type' => $this->record_type, 'plan_type' => $this->plan_type, 'fund_not_disbursed' => $this->fund_not_disbursed, 'tms_customer_number' => $this->tms_customer_number, 'file_id' => $this->file_id, 'date_parsed' => $this->date_parsed ? \PSU::db('banner')->BindDate($this->date_parsed_timestamp()) : null, 'date_processed' => $this->date_processed ? \PSU::db('banner')->BindDate($this->date_processed_timestamp()) : null, 'summer_contract_balance' => $this->summer_contract_balance, 'fall_contract_balance' => $this->fall_contract_balance, 'winter_contract_balance' => $this->winter_contract_balance, 'spring_contract_balance' => $this->spring_contract_balance);
     return $args;
 }

 /**
  * Return the highest aid year for a person.
  */
 public function max_aid_year($pidm)
 {
     $sql = "\n\t\t\tSELECT rcrapp1_aidy_code\n\t\t\tFROM rcrapp1\n\t\t\tWHERE rcrapp1_pidm = :pidm\n\t\t\tORDER BY rcrapp1_aidy_code DESC\n\t\t";
     $args = array('pidm' => $pidm);
     $aid_year = PSU::db('banner')->GetOne($sql, $args);
     return $aid_year ?: null;
 }

 /**
  * retrieve contracts
  */
 public function get()
 {
     $args = array();
     if ($this->psu_id) {
         $args['psu_id'] = $this->psu_id;
         $where .= " AND c.psu_id = :psu_id";
     }
     //end if
     if ($this->processed) {
         $where .= " AND c.date_processed IS NOT NULL";
     } elseif (!$this->include_processed) {
         $where .= " AND c.date_processed IS NULL";
     }
     //end if
     if ($this->num_rows) {
         $where .= " AND rownum <= :num_rows";
         $args['num_rows'] = $this->num_rows;
     }
     //end if
     if ($this->file_id) {
         $where .= " AND c.file_id = :file_id";
         $args['file_id'] = $this->file_id;
     }
     //end if
     $sql = "\n\t\t\tSELECT c.*, \n\t\t\t       b.pidm,\n\t\t         f.file_name,\n\t\t         f.file_type,\n\t\t\t\t\t\t f.file_sub_type,\n\t\t\t\t\t\t f.file_date\n\t\t\t\tFROM payment_plan_contract c\n\t\t\t\t\t\t JOIN payment_plan_feed f\n\t\t\t         ON f.id = c.file_id\n\t\t         LEFT JOIN v_bio b\n\t\t\t         ON b.id = psu_id\n\t\t\t\t\t\t\tAND REGEXP_LIKE( b.id, '[0-9]{9}' )\n\t\t\t WHERE 1 = 1 {$where} \n\t\t\t ORDER BY UPPER(b.last_name), UPPER(b.first_name), b.middle_name, file_id, c.id";
     $results = \PSU::db('banner')->Execute($sql, $args);
     return $results ? $results : array();
 }

 public function get()
 {
     $sql = "\n\t\t\tSELECT \n\t\t\t\td.date_processed,\n\t\t\t\tt.*\n\t\t\tFROM\n\t\t\t\tpayment_plan_disbursement d\n\t\t\t\tJOIN v_bio b\n\t\t\t\t  ON b.id = d.psu_id\n\t\t\t\tJOIN tbraccd t\n\t\t\t\t  ON t.tbraccd_pidm = b.pidm\n\t\t\t\t AND t.tbraccd_document_number = :document_number\n\t\t\tWHERE\n\t\t\t\td.file_id = :file_id\n\t\t";
     $args = array('document_number' => \PSU\AR\PaymentPlan::document_number($this->file_id), 'file_id' => $this->file_id);
     $results = \PSU::db('banner')->Execute($sql, $args);
     return $results;
 }

 public function get_rules()
 {
     $args = array('aidy' => $this->aid_year);
     $sql = "\n\t\t\tSELECT rorwebr_coa_ind,\n\t\t\t\t\t\t rorwebr_need_calc_ind,\n\t\t\t\t\t\t rorwebr_cum_loan_ind,\n\t\t\t\t\t\t rorwebr_detail_resource_ind,\n\t\t\t\t\t\t rorwebr_acpt_partial_amt_ind,\n\t\t\t\t\t\t rorwebr_acpt_all_awards_ind,\n\t\t\t\t\t\t rorwebr_resource_info_ind,\n\t\t\t\t\t\t rorwebr_award_info_ind,\n\t\t\t\t\t\t rorwebr_enrollment_status,\n\t\t\t\t\t\t rorwebr_housing_status_ind,\n\t\t\t\t\t\t rorwebr_term_zero_awrd_ind,\n\t\t\t\t\t\t rorwebr_fund_zero_amt_ind,\n\t\t\t\t\t\t rorwebr_resource_tab_ind,\n\t\t\t\t\t\t rorwebr_terms_tab_ind,\n\t\t\t\t\t\t rorwebr_award_acpt_tab_ind,\n\t\t\t\t\t\t rorwebr_special_msg_tab_ind,\n\t\t\t\t\t\t rorwebr_terms_cond_print_ind\n\t\t\t\tFROM rorwebr\n\t\t\t WHERE rorwebr_aidy_code = :aidy\n\t\t";
     $data = PSU::db('banner')->GetRow($sql, $args);
     return $data;
 }

 public function process()
 {
     $success = false;
     if ($this->psu_status == 'eod') {
         PSU::db('banner')->StartTrans();
         $person = PSUPerson::get($this->ordernumber);
         if ($person->pidm) {
             if ($this->status_flag == 'success') {
                 $appl_no = PSU::db('banner')->GetOne("SELECT appl_no FROM psu.v_ug_app WHERE pidm = " . $person->pidm);
                 if ($appl_no) {
                     $sql = "UPDATE sarchkl SET sarchkl_receive_date = sysdate WHERE sarchkl_pidm = " . $person->pidm . " AND sarchkl_appl_no = " . $appl_no . " AND sarchkl_admr_code = 'APFE'";
                     PSU::db('banner')->Execute($sql);
                 }
                 //end if
             }
             //end if
             $this->psu_status = 'loaded';
             $this->save();
             return PSU::db('banner')->CompleteTrans() ? $this->totalamount / 100 : false;
         }
         //end if
     }
     //end if
     PSU::db('banner')->CompleteTrans(false);
     return false;
 }

 public function unenrollXML()
 {
     $date_str = date("Y-m-d\\TH:i:s");
     $import_xml = "<enterprise>\n\t\t\t\t\t\t<properties>\n\t\t\t\t\t\t\t<datasource>Plymouth State University SCT Banner</datasource>\n\t\t\t\t\t\t\t<datetime>" . $date_str . "</datetime>\n\t\t\t\t\t\t<properties>\n";
     $pre_id = "<membership>\n\t\t\t\t\t\t<sourcedid>\n\t\t\t\t\t\t\t<source>Plymouth State University SCT Banner</source>\n\t\t\t\t\t\t\t<id>" . $this->course . "</id>\n\t\t\t\t\t\t</sourcedid>\n\t\t\t\t\t\t<member>\n\t\t\t\t\t\t\t<sourcedid>\n\t\t\t\t\t\t\t\t<source>Plymouth State University SCT Banner</source>\n";
     $post_id = "\t\t</sourcedid>\n\t\t\t\t\t\t\t<idtype>1</idtype>\n\t\t\t\t\t\t\t<role recstatus = \"3\" roletype = \"01\">\n\t\t\t\t\t\t\t\t<status>0</status>\n\t\t\t\t\t\t\t</role>\n\t\t\t\t\t\t</member>\n\t\t\t\t\t\t</membership>\n";
     $current_enrol_query = "SELECT distinct personsourcedid\n\t\t\t\t\t\t\t\tFROM mdl_lmb_enrolments \n\t\t\t\t\t\t\t\tWHERE coursesourcedid = ? \n\t\t\t\t\t\t\t\tAND role=?";
     $currently_enrolled = PSU::db('moodle')->GetCol($current_enrol_query, array($this->course, 1));
     $to_be_enrolled = array();
     //If no one is currently enrolled, then don't worry about deletes
     if (!$currently_enrolled) {
         return '';
     }
     //end if
     $this->population->query($this->args);
     foreach ($this->population as $id) {
         $to_be_enrolled[] = $id->scalar;
     }
     //end foreach
     $to_unenroll = array_diff($currently_enrolled, $to_be_enrolled);
     foreach ($to_unenroll as $id) {
         $import_xml .= $pre_id . "<id>" . $id . "</id>\n" . $post_id;
     }
     //end foreach
     $import_xml .= "</enterprise>\n</enterprise>\n";
     $this->import_xml = $import_xml;
     return $import_xml;
 }

 private function _get_components()
 {
     $args = array('pidm' => $this->pidm, 'aidy' => $this->aid_year, 'code' => null);
     $sql = "\n\t\t\tSELECT RTVCOMP_DESC,\n\t\t\t\t   NVL(RBRACMP_AMT, 0) RBRACMP_AMT\n\t\t\t  FROM RBRACMP, RTVCOMP\n\t\t\t WHERE RBRACMP_AIDY_CODE = :aidy\n\t\t\t   AND RBRACMP_PIDM      = :pidm\n\t\t\t   AND RBRACMP_COMP_CODE = RTVCOMP_CODE\n\t\t\t   -- 080500-5\n\t\t\t   AND ((  :code IS NULL\n\t\t\t\t   AND NOT EXISTS\n\t\t\t\t\t  (SELECT 'X'\n\t\t\t\t\t\t FROM RTVBTYP\n\t\t\t\t\t\tWHERE RTVBTYP_PELL_IND  = 'Y'\n\t\t\t\t\t\t  AND RBRACMP_BTYP_CODE = RTVBTYP_CODE))\n\t\t\t\t\tOR\n\t\t\t\t\t(  :code = 'PELL'\n\t\t\t\t   AND EXISTS\n\t\t\t\t\t  (SELECT 'X'\n\t\t\t\t\t\t FROM RTVBTYP\n\t\t\t\t\t\tWHERE RTVBTYP_PELL_IND  = 'Y'\n\t\t\t\t\t\t  AND RBRACMP_BTYP_CODE = RTVBTYP_CODE)))\n\t\t\tORDER BY RTVCOMP_PRINT_SEQ_NO\n\t\t";
     $rset = PSU::db('banner')->Execute($sql, $args);
     return $rset;
 }

 public function query($args = array())
 {
     $args = \PSU::params($args, $defaults);
     $sql = "\n\t\t\tSELECT DISTINCT pidm\n\t\t\t  FROM v_account\n\t\t";
     $results = \PSU::db('banner')->GetCol($sql, $args);
     return $results;
 }

 public function get()
 {
     $args = array('pidm' => $this->pidm);
     $sql = "\n\t\t\tSELECT rprauth_pidm pidm,\n\t\t\t\t\t\t rprauth_term_code term_code,\n\t\t\t\t\t\t rfrbase_detail_code detail_code,\n\t\t\t\t\t\t rprauth_amount amount\n\t\t\tFROM   rfrbase, rprauth\n\t\t\tWHERE  rprauth_pidm             = :pidm\n\t\t\t\tAND  rfrbase_fund_code        = rprauth_fund_code\n\t\t";
     $rset = \PSU::db('banner')->Execute($sql, $args);
     return $rset;
 }

 public function get()
 {
     $sql = "\n\t\t\tSELECT *\n\t\t\t  FROM sfrrgfe\n\t\t\t WHERE sfrrgfe_term_code = :term_code\n\t\t\t   AND sfrrgfe_type = 'STUDENT'\n\t\t\t\t AND sfrrgfe_flat_fee_amount IS NOT NULL\n\t\t";
     $args = array('term_code' => $this->term_code);
     $results = \PSU::db('banner')->Execute($sql, $args);
     return $results;
 }

 public function delete()
 {
     //this function will delete a note
     $sql = "UPDATE psu_identity.person_notes \n\t\t\t\t SET deleted=1 \n                   WHERE id = :note_id";
     $data = array('note_id' => $this->id);
     return \PSU::db('banner')->Execute($sql, $data);
 }