PHP DBQuery函数代码示例

function GetCurrentMP($mp, $date, $error = true)
{
    global $_openSIS;
    switch ($mp) {
        case 'FY':
            $table = 'school_years';
            break;
        case 'SEM':
            $table = 'school_semesters';
            break;
        case 'QTR':
            $table = 'school_quarters';
            break;
        case 'PRO':
            $table = 'school_progress_periods';
            break;
    }
    if (!$_openSIS['GetCurrentMP'][$date][$mp]) {
        $_openSIS['GetCurrentMP'][$date][$mp] = DBGet(DBQuery('SELECT MARKING_PERIOD_ID FROM ' . $table . ' WHERE \'' . $date . '\' BETWEEN START_DATE AND END_DATE AND SYEAR=\'' . UserSyear() . '\' AND SCHOOL_ID=\'' . UserSchool() . '\''));
    }
    if ($_openSIS['GetCurrentMP'][$date][$mp][1]['MARKING_PERIOD_ID']) {
        return $_openSIS['GetCurrentMP'][$date][$mp][1]['MARKING_PERIOD_ID'];
    } elseif (strpos($_SERVER['PHP_SELF'], 'Side.php') === false && $error == true) {
        ErrorMessage(array('' . _("You are not currently in a marking period") . ''));
    }
    //ShowErr("You are not currently in a marking period");
}

function MailingLabel($address_id)
{
    global $THIS_RET, $_openSIS;
    $student_id = $THIS_RET['STUDENT_ID'];
    if ($address_id && !$_openSIS['MailingLabel'][$address_id][$student_id]) {
        $p_sql = 'SELECT sa.ID AS ADDRESS_ID,p.STAFF_ID AS PERSON_ID,
                    coalesce((SELECT STREET_ADDRESS_1 FROM student_address WHERE STUDENT_ID=sa.STUDENT_ID AND TYPE =\'MAIL\'),sa.STREET_ADDRESS_1) AS ADDRESS,
                    coalesce((SELECT CITY FROM student_address WHERE STUDENT_ID=sa.STUDENT_ID AND TYPE =\'MAIL\'),sa.CITY) AS CITY,
                    coalesce((SELECT STATE FROM student_address WHERE STUDENT_ID=sa.STUDENT_ID AND TYPE =\'MAIL\'),sa.STATE) AS STATE,
                    coalesce((SELECT ZIPCODE FROM student_address WHERE STUDENT_ID=sa.STUDENT_ID AND TYPE =\'MAIL\'),sa.ZIPCODE) AS ZIPCODE,
                    s.PHONE,p.LAST_NAME,p.FIRST_NAME,p.MIDDLE_NAME
                    FROM student_address sa,people p,students s
                    WHERE p.STAFF_ID=sa.PEOPLE_ID AND s.STUDENT_ID=sa.STUDENT_ID AND sa.STUDENT_ID=\'' . $student_id . '\' AND p.CUSTODY=\'Y\'';
        $people_RET = DBGet(DBQuery($p_sql), array(), array('LAST_NAME'));
        if (count($people_RET)) {
            foreach ($people_RET as $last_name => $people) {
                for ($i = 1; $i < count($people); $i++) {
                    $return .= $people[$i]['FIRST_NAME'] . ' &amp; ';
                }
                $return .= $people[$i]['FIRST_NAME'] . ' ' . $people[$i]['LAST_NAME'] . '<BR>';
            }
            // mab - this is a bit of a kludge but insert an html comment so people and address can be split later
            $return .= '<!-- -->' . $people[$i]['ADDRESS'] . '<BR>' . $people[$i]['CITY'] . ', ' . $people[$i]['STATE'] . ' ' . $people[$i]['ZIPCODE'];
        }
        $_openSIS['MailingLabel'][$address_id][$student_id] = $return;
    }
    return $_openSIS['MailingLabel'][$address_id][$student_id];
}

function AllowUse($modname = false)
{
    global $_openSIS;
    if (!$modname) {
        $modname = $_REQUEST['modname'];
    }
    if ($modname == 'Students/Student.php' && $_REQUEST['category_id']) {
        $modname = $modname . '&category_id=' . $_REQUEST['category_id'];
    }
    if (!$_openSIS['AllowUse']) {
        if (User('PROFILE_ID') != '') {
            $_openSIS['AllowUse'] = DBGet(DBQuery("SELECT MODNAME FROM PROFILE_EXCEPTIONS WHERE PROFILE_ID='" . User('PROFILE_ID') . "' AND CAN_USE='Y'"), array(), array('MODNAME'));
        } else {
            $_openSIS['AllowUse'] = DBGet(DBQuery("SELECT MODNAME FROM STAFF_EXCEPTIONS WHERE USER_ID='" . User('STAFF_ID') . "' AND CAN_USE='Y'"), array(), array('MODNAME'));
        }
    }
    if (!$_openSIS['AllowUse']) {
        $_openSIS['AllowUse'] = array(true);
    }
    if (count($_openSIS['AllowUse'][$modname])) {
        return true;
    } else {
        return false;
    }
}

function GetSyear($date)
{
    //$RET = DBGet(DBQuery("SELECT SYEAR FROM ATTENDANCE_CALENDAR WHERE SCHOOL_DATE = '$date' AND DEFAULT_CALENDAR='Y'"));
    //$RET = DBGet(DBQuery("SELECT SYEAR FROM SCHOOL_MARKING_PERIODS WHERE MP='FY' AND '".$date."' BETWEEN START_DATE AND END_DATE"));
    $RET = DBGet(DBQuery("SELECT max(SYEAR) AS SYEAR FROM SCHOOL_MARKING_PERIODS WHERE MP='FY' AND START_DATE<='" . $date . "'"));
    return $RET[1]['SYEAR'];
}

function enrol_manual_enrol_users_object()
{
    //first, gather the necessary variables
    global $student_id, $course_period;
    //then, convert variables for the Moodle object:
    /*
    list of ( 
    	object {
    		roleid int   //Role to assign to the user
    		userid int   //The user that is going to be enrolled
    		courseid int   //The course to enrol the user role in
    		timestart int  Optionnel //Timestamp when the enrolment start
    		timeend int  Optionnel //Timestamp when the enrolment end
    		suspend int  Optionnel //set to 1 to suspend the enrolment
    	} 
    )*/
    //student's roleid = student = 5
    $roleid = 5;
    //get the Moodle user ID
    $userid = DBGet(DBQuery("SELECT moodle_id FROM moodlexrosario WHERE rosario_id='" . $student_id . "' AND \"column\"='student_id'"));
    if (count($userid)) {
        $userid = (int) $userid[1]['MOODLE_ID'];
    } else {
        return null;
    }
    //gather the Moodle course ID
    $courseid = DBGet(DBQuery("SELECT moodle_id FROM moodlexrosario WHERE rosario_id='" . $course_period['COURSE_PERIOD_ID'] . "' AND \"column\"='course_period_id'"));
    if (count($courseid)) {
        $courseid = (int) $courseid[1]['MOODLE_ID'];
    } else {
        return null;
    }
    $enrolments = array(array('roleid' => $roleid, 'userid' => $userid, 'courseid' => $courseid));
    return array($enrolments);
}

 function obtenerArancel($colegio_ID, $curso, $aran_anio)
 {
     DBConnect($this->dbh, BD_SIGA);
     $SQL = "SELECT * FROM arancel WHERE FK_colegios_colegio_ID = '{$colegio_ID}'\r\n                                            AND FK_curso = '{$curso}'\r\n                                            AND aran_anio = '{$aran_anio}'";
     $result = DBQuery($SQL, $this->dbh);
     $row = DBFetchArray($result);
     DBConnect($dbh);
     //dpr($_SESSION['colegio']->colegio_ID);
     if (!$row['aran_monto']) {
         switch ($_SESSION['colegio']->colegio_ID) {
             case "abello":
                 $row['aran_monto'] = ArancelColegio::ABELLO;
                 break;
             case "edupac":
                 $row['aran_monto'] = ArancelColegio::EDUPAC;
                 break;
             case "dor":
                 if (substr($curso, 0, -1) >= 9) {
                     $row['aran_monto'] = ArancelColegio::DOR_MEDIA;
                 } else {
                     $row['aran_monto'] = ArancelColegio::DOR_BASICA;
                 }
                 break;
         }
         //$_SESSION[''] =
     }
     return $row;
 }

function GetStaffList(&$extra)
{
    global $profiles_RET;
    $functions = array('PROFILE' => 'makeProfile');
    switch (User('PROFILE')) {
        case 'admin':
            $profiles_RET = DBGet(DBQuery("SELECT * FROM USER_PROFILES"), array(), array('ID'));
            $sql = "SELECT CONCAT(\n\t\t\t\t\tCOALESCE(s.LAST_NAME,' '),', ',COALESCE(s.FIRST_NAME,' '),' ',COALESCE(s.MIDDLE_NAME,' ')) AS FULL_NAME,\n\t\t\t\t\ts.PROFILE,s.PROFILE_ID,s.STAFF_ID,s.SCHOOLS " . $extra['SELECT'] . "\n\t\t\t\tFROM\n\t\t\t\t\tSTAFF s " . $extra['FROM'] . "\n\t\t\t\tWHERE\n\t\t\t\t\ts.SYEAR='" . UserSyear() . "'";
            if ($_REQUEST['_search_all_schools'] != 'Y') {
                $sql .= " AND (s.SCHOOLS LIKE '%," . UserSchool() . ",%' OR s.SCHOOLS IS NULL OR s.SCHOOLS='') ";
            }
            if ($_REQUEST['username']) {
                $sql .= "AND UPPER(s.USERNAME) LIKE '" . strtoupper($_REQUEST['username']) . "%' ";
            }
            if ($_REQUEST['last']) {
                $sql .= "AND UPPER(s.LAST_NAME) LIKE '" . strtoupper($_REQUEST['last']) . "%' ";
            }
            if ($_REQUEST['first']) {
                $sql .= "AND UPPER(s.FIRST_NAME) LIKE '" . strtoupper($_REQUEST['first']) . "%' ";
            }
            if ($_REQUEST['profile']) {
                $sql .= "AND s.PROFILE='" . $_REQUEST['profile'] . "' ";
            }
            $sql .= $extra['WHERE'] . ' ';
            $sql .= "ORDER BY FULL_NAME";
            if ($extra['functions']) {
                $functions += $extra['functions'];
            }
            return DBGet(DBQuery($sql), $functions);
            break;
    }
}

 function obtenerDB($db_ID)
 {
     $SQL = "SELECT * FROM db WHERE db_ID = '{$db_ID}'";
     $result = DBQuery($SQL, $this->dbh);
     $row = DBFetchArray($result);
     return (object) $row;
 }

function MailingLabel($address_id)
{
    global $THIS_RET, $_openSIS;
    $student_id = $THIS_RET['STUDENT_ID'];
    if ($address_id && !$_openSIS['MailingLabel'][$address_id][$student_id]) {
        $people_RET = DBGet(DBQuery('SELECT a.ADDRESS_ID,p.PERSON_ID,
			coalesce(a.MAIL_ADDRESS,a.ADDRESS) AS ADDRESS,coalesce(a.MAIL_CITY,a.CITY) AS CITY,coalesce(a.MAIL_STATE,a.STATE) AS STATE,coalesce(a.MAIL_ZIPCODE,a.ZIPCODE) AS ZIPCODE,a.PHONE,
				p.LAST_NAME,p.FIRST_NAME,p.MIDDLE_NAME
			FROM address a,people p,students_join_people sjp
			WHERE a.ADDRESS_ID=\'' . $address_id . '\' AND a.ADDRESS_ID=sjp.ADDRESS_ID AND p.PERSON_ID=sjp.PERSON_ID
				AND sjp.CUSTODY=\'Y\' AND sjp.STUDENT_ID=\'' . $student_id . '\''), array(), array('LAST_NAME'));
        if (count($people_RET)) {
            foreach ($people_RET as $last_name => $people) {
                for ($i = 1; $i < count($people); $i++) {
                    $return .= $people[$i]['FIRST_NAME'] . ' &amp; ';
                }
                $return .= $people[$i]['FIRST_NAME'] . ' ' . $people[$i]['LAST_NAME'] . '<BR>';
            }
            // mab - this is a bit of a kludge but insert an html comment so people and address can be split later
            $return .= '<!-- -->' . $people[$i]['ADDRESS'] . '<BR>' . $people[$i]['CITY'] . ', ' . $people[$i]['STATE'] . ' ' . $people[$i]['ZIPCODE'];
        } else {
            $address_ret = DBGet(DBQuery('SELECT coalesce(MAIL_ADDRESS,ADDRESS) as ADDRESS,coalesce(MAIL_CITY,CITY) AS CITY,coalesce(MAIL_STATE,STATE) AS STATE,coalesce(MAIL_ZIPCODE,ZIPCODE) AS ZIPCODE FROM address WHERE ADDRESS_ID=\'' . $address_id . '\''));
            $return .= '<!-- -->' . $address_ret[1]['ADDRESS'] . '<BR>' . $address_ret[1]['CITY'] . ', ' . $address_ret[1]['STATE'] . ' ' . $address_ret[1]['ZIPCODE'];
        }
        $_openSIS['MailingLabel'][$address_id][$student_id] = $return;
    }
    return $_openSIS['MailingLabel'][$address_id][$student_id];
}

function core_user_update_users_object()
{
    //first, gather the necessary variables
    global $_REQUEST;
    //gather the Moodle user ID
    if (User('PROFILE') == 'student') {
        $rosario_id = UserStudentID();
        $moodle_id = DBGet(DBQuery("SELECT moodle_id FROM moodlexrosario WHERE rosario_id='" . $rosario_id . "' AND \"column\"='student_id'"));
    } else {
        $rosario_id = User('STAFF_ID');
        $moodle_id = DBGet(DBQuery("SELECT moodle_id FROM moodlexrosario WHERE rosario_id='" . $rosario_id . "' AND \"column\"='staff_id'"));
    }
    if (count($moodle_id)) {
        $moodle_id = (double) $moodle_id[1]['MOODLE_ID'];
    } else {
        return '';
    }
    //then, convert variables for the Moodle object:
    /*
    list of ( 
    	object {
    		id double   //ID of the user
    		password string  Optional //Plain text password consisting of any characters
    		//note Francois: the password must respect the Moodle policy: 8 chars min., 1 number, 1 min, 1 maj and 1 non-alphanum at least.
    	} 
    )
    */
    $password = $_REQUEST['values']['new'];
    $users = array(array('id' => $moodle_id, 'password' => $password));
    return array($users);
}

function DeCodeds($value, $column)
{
    global $_openSIS;
    $field = substr($column, 7);
    if (!$_openSIS['DeCodeds'][$field]) {
        $select_options = DBGet(DBQuery('SELECT SELECT_OPTIONS FROM custom_fields WHERE ID=\'' . $field . '\''));
        $select_options = str_replace("\n", "\r", str_replace("\r\n", "\r", $select_options[1]['SELECT_OPTIONS']));
        $select_options = explode("\r", $select_options);
        foreach ($select_options as $option) {
            $option = explode('|', $option);
            if ($option[0] != '' && $option[1] != '') {
                $options[$option[0]] = $option[1];
            }
        }
        if (count($options)) {
            $_openSIS['DeCodeds'][$field] = $options;
        } else {
            $_openSIS['DeCodeds'][$field] = true;
        }
    }
    if ($value != '') {
        if ($_openSIS['DeCodeds'][$field][$value] != '') {
            return $_openSIS['DeCodeds'][$field][$value];
        } else {
            return "<FONT color=red>{$value}</FONT>";
        }
    } else {
        return '';
    }
}

function GetCpDet($cp_id, $key)
{
    if ($key != '' && $cp_id != '') {
        $get_det = DBGet(DBQuery('SELECT ' . strtoupper($key) . ' FROM course_periods WHERE COURSE_PERIOD_ID=' . $cp_id));
    }
    return $get_det[1][strtoupper($key)];
}

    public static function searchStudents($value)
    {
        $query = "SELECT\r\n\t\t\t\t  S.last_name,\r\n\t\t\t\t  S.first_name,\r\n\t\t\t\t  S.middle_name,\r\n\t\t\t\t  S.student_id,\r\n\t\t\t\t  GL.title\r\n\t\t\t\t  FROM\r\n\t\t\t\t  SCHOOL_GRADELEVELS GL,\r\n\t\t\t\t  STUDENTS S,\r\n\t\t\t\t  STUDENT_ENROLLMENT SE\r\n\t\t\t\t  WHERE\r\n\t\t\t\t  S.student_id = SE.student_id\r\n\t\t\t\t  and SE.grade_id = GL.id\r\n\t\t\t\t  and\r\n\t\t\t\t  (\r\n\t\t\t\t  lower(S.last_name) like lower('%{$value}%')\r\n\t\t\t\t  or lower(S.first_name) like lower('%{$value}%')\r\n\t\t\t\t  or lower(S.first_name || ' ' || S.last_name) like lower('%{$value}%')\r\n                  )";
        if (is_numeric($value)) {
            $query = $query . " or S.student_id = {$value}";
        }
        $result = DBQuery($query);
        $json = '"users":[';
        while ($row = db_fetch_row($result)) {
            $lastName = $row['LAST_NAME'];
            $firstName = $row['FIRST_NAME'];
            $middle = $row['MIDDLE_NAME'];
            $id = $row['STUDENT_ID'];
            $grade = $row['TITLE'];
            $json = $json . '{
							"id":"' . $id . '",
						    "first":"' . $firstName . '",
						    "last":"' . $lastName . '",
						    "middle":"' . $middle . '",
						    "grade":"' . $grade . '"
                            },';
        }
        $json = rtrim($json, ",");
        $json = $json . ']';
        return $json;
    }

function GetCurrentMP($mp, $date, $error = true)
{
    global $_openSIS;
    switch ($mp) {
        case 'FY':
            $table = 'SCHOOL_YEARS';
            break;
        case 'SEM':
            $table = 'SCHOOL_SEMESTERS';
            break;
        case 'QTR':
            $table = 'SCHOOL_QUARTERS';
            break;
        case 'PRO':
            $table = 'SCHOOL_PROGRESS_PERIODS';
            break;
    }
    if (!$_openSIS['GetCurrentMP'][$date][$mp]) {
        $_openSIS['GetCurrentMP'][$date][$mp] = DBGet(DBQuery("SELECT MARKING_PERIOD_ID FROM {$table} WHERE '{$date}' BETWEEN START_DATE AND END_DATE AND SYEAR='" . UserSyear() . "' AND SCHOOL_ID='" . UserSchool() . "'"));
    }
    if ($_openSIS['GetCurrentMP'][$date][$mp][1]['MARKING_PERIOD_ID']) {
        return $_openSIS['GetCurrentMP'][$date][$mp][1]['MARKING_PERIOD_ID'];
    } elseif (strpos($_SERVER['PHP_SELF'], 'Side.php') === false && $error == true) {
        ErrorMessage(array("You are not currently in a marking period"));
    }
    //ShowErr("You are not currently in a marking period");
}

function AllowUse($modname = false)
{
    global $_openSIS;
    if (!$modname) {
        $modname = $_REQUEST['modname'];
    }
    if ($modname == 'Students/Student.php' && $_REQUEST['category_id']) {
        $modname = $modname . '&category_id=' . $_REQUEST['category_id'];
    }
    if (!$_openSIS['AllowUse']) {
        if (User('PROFILE_ID') != '') {
            $_openSIS['AllowUse'] = DBGet(DBQuery('SELECT MODNAME FROM profile_exceptions WHERE PROFILE_ID=\'' . User('PROFILE_ID') . '\' AND CAN_USE=\'Y\''), array(), array('MODNAME'));
        } else {
            $_openSIS['AllowUse'] = DBGet(DBQuery('SELECT MODNAME FROM staff_exceptions WHERE USER_ID=\'' . User('STAFF_ID') . '\' AND CAN_USE=\'Y\''), array(), array('MODNAME'));
        }
    }
    if (!$_openSIS['AllowUse']) {
        $_openSIS['AllowUse'] = array(true);
    }
    if (count($_openSIS['AllowUse'][$modname])) {
        return true;
    } else {
        return false;
    }
}