PHP check_db_result函数代码示例

    /**
     * Execute this call
     *
     * @return void
     * @access public
     */
    function run()
    {
        $db = $GLOBALS['db'];
        if (!empty($_REQUEST['personid'])) {
            $obj = $GLOBALS['system']->getDBObject('person', (int) $_REQUEST['personid']);
            $SQL = 'SELECT photodata FROM person_photo WHERE personid = ' . $obj->id;
        } else {
            if (!empty($_REQUEST['familyid'])) {
                $obj = $GLOBALS['system']->getDBObject('family', (int) $_REQUEST['familyid']);
                // for single-member families, treat person photo as family photo
                $SQL = 'SELECT COALESCE(fp.photodata, IF(count(p.id) = 1, pp.photodata, NULL)) as photodata
					FROM family f
					LEFT JOIN family_photo fp ON fp.familyid = f.id
					LEFT JOIN person p ON p.familyid = f.id
					LEFT JOIN person_photo pp ON pp.personid = p.id
					WHERE f.id = ' . (int) $obj->id . '
					GROUP BY f.id';
            }
        }
        if ($obj) {
            $res = $db->queryRow($SQL);
            check_db_result($res);
            if ($res && $res['photodata']) {
                header('Content-type: image/jpeg');
                echo $res['photodata'];
                return;
            }
        }
        header('Content-type: image/gif');
        $placeholder = !empty($_REQUEST['personid']) ? 'unknown.gif' : 'unknown_family.gif';
        readfile(dirname(dirname(__FILE__)) . '/resources/img/' . $placeholder);
    }

    function processView()
    {
        if (!empty($_POST['datetypename'])) {
            $to_add = $to_delete = $to_update = array();
            foreach ($_POST['datetypename'] as $id => $name) {
                if ($id == '_new_') {
                    foreach ($name as $n) {
                        if ($n) {
                            $to_add[] = $n;
                        }
                    }
                } else {
                    if ($name) {
                        $to_update[$id] = $name;
                    }
                }
            }
            foreach ($to_update as $id => $name) {
                $SQL = 'UPDATE date_type
						SET name = ' . $GLOBALS['db']->quote($name) . '
						WHERE id = ' . (int) $id;
                $res = $GLOBALS['db']->query($SQL);
                check_db_result($res);
            }
            $res = $GLOBALS['db']->query('DELETE FROM date_type WHERE id NOT IN (' . implode(',', array_merge(array_keys($to_update))) . ')');
            foreach ($to_add as $name) {
                $SQL = 'INSERT INTO date_type (name)
						VALUES (' . $GLOBALS['db']->quote($name) . ')';
                $res = $GLOBALS['db']->query($SQL);
                check_db_result($res);
            }
            add_message("Date types updated");
        }
    }

 public static function getDataURL($type, $id)
 {
     $SQL = 'SELECT photodata FROM ' . $type . '_photo WHERE ' . $type . 'id = ' . (int) $id;
     $res = $GLOBALS['db']->queryOne($SQL);
     check_db_result($res);
     return 'data:image/jpg;base64,' . base64_encode($res);
 }

 function delete()
 {
     $GLOBALS['system']->doTransaction('BEGIN');
     parent::delete();
     $sql = 'UPDATE person_group SET categoryid = 0 WHERE categoryid = ' . (int) $this->id;
     $res = $GLOBALS['db']->query($sql);
     check_db_result($res);
     $GLOBALS['system']->doTransaction('COMMIT');
 }

    static function getCongregations()
    {
        $SQL = 'SELECT c.id, c.name
				from congregation c
				join member m on m.congregationid = c.id
				group by c.id';
        $res = $GLOBALS['db']->queryAll($SQL, null, null, true, false);
        check_db_result($res);
        return $res;
    }

    public static function fetchAverage($entitytype, $entityid, $fromDate, $toDate)
    {
        self::checkEntityType($entitytype);
        $db = $GLOBALS['db'];
        $SQL = 'SELECT AVG(number) FROM ' . $entitytype . '_headcount
				WHERE (`date` BETWEEN ' . $db->quote($fromDate) . ' AND ' . $db->quote($toDate) . ')
				AND ' . $entitytype . 'id = ' . $db->quote($entityid);
        $res = $db->queryOne($SQL);
        check_db_result($res);
        return $res;
    }

    public function processView()
    {
        $db = $GLOBALS['db'];
        if (!empty($_POST['group_membership_statuses_submitted'])) {
            $i = 0;
            $saved_default = false;
            $rankMap = $_REQUEST['membership_status_ranking'];
            foreach ($rankMap as $k => $v) {
                if ($v == '') {
                    $rankMap[$k] = max($rankMap) + 1;
                }
            }
            $ranks = array_flip($rankMap);
            while (isset($_POST['membership_status_' . $i . '_label'])) {
                $sql = null;
                $is_default = (int) ($_POST['membership_status_default_rank'] == $i);
                if (empty($_POST['membership_status_' . $i . '_id'])) {
                    if (!empty($_POST['membership_status_' . $i . '_label'])) {
                        $sql = 'INSERT INTO person_group_membership_status (label, rank, is_default)
								VALUES (' . $db->quote($_POST['membership_status_' . $i . '_label']) . ', ' . (int) $ranks[$i] . ',' . $is_default . ')';
                    }
                } else {
                    if (!in_array($_POST['membership_status_' . $i . '_id'], array_get($_POST, 'membership_status_delete', array()))) {
                        $sql = 'UPDATE person_group_membership_status
							SET label = ' . $db->quote($_POST['membership_status_' . $i . '_label']) . ',
							is_default = ' . $is_default . ',
							rank = ' . (int) $ranks[$i] . '
							WHERE id = ' . (int) $_POST['membership_status_' . $i . '_id'];
                    }
                }
                if ($sql) {
                    $res = $db->query($sql);
                    check_db_result($res);
                    if ($is_default) {
                        $saved_default = true;
                    }
                }
                $i++;
            }
            if (!empty($_POST['membership_status_delete'])) {
                $sql = 'DELETE FROM person_group_membership_status WHERE id IN (' . implode(',', array_map(array($db, 'quote'), $_POST['membership_status_delete'])) . ')';
                $res = $db->query($sql);
                check_db_result($res);
            }
            if (!$saved_default) {
                $db->query('UPDATE person_group_membership_status SET is_default = 1 ORDER BY label LIMIT 1');
                check_db_result($res);
            }
            $db->query('UPDATE person_group_membership SET membership_status = (SELECT id FROM person_group_membership_status WHERE is_default) WHERE membership_status IS NULL');
            check_db_result($res);
        }
    }

 /**
  * Execute this call
  *
  * @return void
  * @access public
  */
 function run()
 {
     $db = $GLOBALS['db'];
     $person = $GLOBALS['system']->getDBObject('person', (int) $_REQUEST['personid']);
     if ($person) {
         $sql = 'SELECT * FROM person_photo WHERE personid = ' . (int) $person->id;
         $res = $db->queryRow($sql);
         check_db_result($res);
         if ($res) {
             header('Content-type: image/jpeg');
             // FIXME
             echo $res['photodata'];
             return;
         }
     }
     header('Content-type: image/gif');
     readfile(dirname(dirname(dirname(__FILE__))) . '/resources/img/unknown.gif');
 }

    static function getUpcomingAssignments($personid, $timeframe = '4 weeks')
    {
        $end_date = date('Y-m-d', strtotime('+' . $timeframe));
        $sql = 'SELECT rra.assignment_date, COALESCE(c.name, "") as cong, rr.title, rr.id
			FROM roster_role_assignment rra
				JOIN roster_role rr ON rra.roster_role_id = rr.id
				LEFT OUTER JOIN congregation c ON rr.congregationid = c.id
			WHERE rra.personid = ' . $GLOBALS['db']->quote($personid);
        if (!empty($timeframe)) {
            $sql .= '
			AND rra.assignment_date BETWEEN  DATE(NOW()) AND ' . $GLOBALS['db']->quote($end_date);
        } else {
            $sql .= '
			AND rra.assignment_date >= DATE(NOW())';
        }
        $sql .= '
			ORDER BY rra.assignment_date ASC, c.meeting_time';
        $res = $GLOBALS['db']->queryAll($sql, NULL, NULL, true, false, true);
        check_db_result($res);
        return $res;
    }

 function _insertRestrictions()
 {
     if (empty($this->id)) {
         trigger_error("Don't have an ID, can't insert restrictions", E_USER_ERROR);
     }
     foreach (array('congregation', 'group') as $type) {
         if (!empty($this->_restrictions[$type])) {
             $rows = array();
             foreach ($this->_restrictions[$type] as $id) {
                 // TODO: only insert new restrictions!!!!
                 $rows[] = '(' . (int) $this->id . ',' . (int) $id . ')';
             }
             $res = $GLOBALS['db']->query('INSERT IGNORE INTO account_' . $type . '_restriction (personid, ' . $type . 'id) VALUES ' . implode(',', $rows));
             check_db_result($res);
         }
     }
 }

    public function updateMembershipStatuses($vals)
    {
        $GLOBALS['system']->doTransaction('BEGIN');
        list($options, $default) = self::getMembershipStatusOptionsAndDefault();
        foreach ($vals as $personid => $status) {
            if (!isset($options[$status])) {
                trigger_error("Invalid person status {$status} not saved");
                continue;
            }
            $res = $GLOBALS['db']->query('UPDATE person_group_membership
										SET membership_status = ' . $GLOBALS['db']->quote($status) . '
										WHERE groupid = ' . (int) $this->id . '
											AND personid = ' . (int) $personid);
            check_db_result($res);
        }
        $GLOBALS['system']->doTransaction('COMMIT');
        return TRUE;
    }

    /**
     * Get Attendance data for the specified criteria
     * @param array $congregationids
     * @param int $groupid
     * @param array $params		Parameters to restrict person records, eg age bracket and status
     * @param string $start_date
     * @param string $end_date
     * @return array
     */
    public static function getAttendances($congregationids, $groupid, $params, $start_date, $end_date)
    {
        $SQL = 'SELECT person.id, person.last_name, person.first_name, ' . ($groupid ? 'pgms.label AS membership_status, ' : '') . ' person.status, ar.date, ar.present
				FROM person person
				JOIN family f ON person.familyid = f.id
				';
        if ($groupid) {
            $SQL .= '
				JOIN person_group_membership pgm ON pgm.personid = person.id AND pgm.groupid = ' . (int) $groupid;
        }
        // restricting the attendance dates within a subquery improves performance significantly.
        $SQL .= '
				LEFT JOIN (
					SELECT personid, date, present
					FROM attendance_record ar
					WHERE ar.date BETWEEN ' . $GLOBALS['db']->quote($start_date) . ' AND ' . $GLOBALS['db']->quote($end_date) . '
					AND ar.groupid = ' . (int) $groupid . '
				) ar ON ar.personid = person.id';
        if ($groupid) {
            $SQL .= '
				LEFT JOIN person_group_membership_status pgms ON pgms.id = pgm.membership_status';
        }
        $SQL .= '
				WHERE ((person.status <> "archived") OR (ar.present IS NOT NULL)) ';
        if ($congregationids) {
            $SQL .= '
				 AND person.congregationid IN (' . implode(', ', array_map(array($GLOBALS['db'], 'quote'), $congregationids)) . ') ';
        }
        if (!empty($params['(age_bracket'])) {
            $SQL .= '
				AND person.age_bracket IN (' . implode(',', array_map(array($GLOBALS['db'], 'quote'), $params['(age_bracket'])) . ')';
        }
        $statuses = array_get($params, '(status', array());
        if (isset($params['status'])) {
            $statuses[] = $params['status'];
        }
        $statusClauses = array();
        foreach ($statuses as $status) {
            if (strlen($status)) {
                list($statusType, $statusID) = explode('-', $status);
                if ($statusType == 'g' && empty($groupid)) {
                    trigger_error("Cannot filter by group membership status for congregational attendance");
                    return array(array(), array(), array());
                }
                switch ($statusType) {
                    case 'g':
                        $statusClauses[] = 'pgm.membership_status = ' . $GLOBALS['db']->quote($statusID);
                        break;
                    case 'p':
                        $statusClauses[] = 'person.status = ' . $GLOBALS['db']->quote($statusID);
                        break;
                }
            }
        }
        if ($statusClauses) {
            $SQL .= 'AND ((' . implode(') OR (', $statusClauses) . '))';
        }
        $order = defined('ATTENDANCE_LIST_ORDER') ? constant('ATTENDANCE_LIST_ORDER') : self::LIST_ORDER_DEFAULT;
        if ($congregationids) {
            $order = preg_replace("/(^|[^.])status(\$| |,)/", '\\1person.status\\2', $order);
        } else {
            $order = preg_replace("/(^|[^.])status(\$| |,)/", '\\1pgms.rank\\2', $order);
        }
        $SQL .= '
				ORDER BY ' . $order;
        $dates = array();
        $attendances = array();
        $totals = array();
        $res = $GLOBALS['db']->query($SQL);
        check_db_result($res);
        while ($row = $res->fetchRow()) {
            if (!empty($row['date'])) {
                $dates[$row['date']] = 1;
            }
            foreach (array('last_name', 'first_name', 'membership_status', 'status') as $f) {
                if (array_key_exists($f, $row)) {
                    $attendances[$row['id']][$f] = $row[$f];
                }
            }
            $attendances[$row['id']][$row['date']] = $row['present'];
            if (!isset($totals[$row['date']]) || !isset($totals[$row['date']][$row['present']])) {
                $totals[$row['date']][$row['present']] = 0;
            }
            $totals[$row['date']][$row['present']]++;
        }
        $dates = array_keys($dates);
        sort($dates);
        return array($dates, $attendances, $totals);
    }

    public function getItems($withContent = FALSE, $ofCategoryID = NULL)
    {
        $SQL = 'SELECT si.*, sc.title, sc.alt_title, sc.is_numbered, ' . ($withContent ? 'sc.content_html, sc.credits, ' : '') . '
					IF(LENGTH(sc.runsheet_title_format) = 0, scc.runsheet_title_format, sc.runsheet_title_format) AS runsheet_title_format,
					IF(LENGTH(sc.handout_title_format) = 0, scc.handout_title_format, sc.handout_title_format) AS handout_title_format
				FROM service_item si
				LEFT JOIN service_component sc ON si.componentid = sc.id
				LEFT JOIN service_component_category scc ON sc.categoryid = scc.id
				WHERE si.serviceid = ' . (int) $this->id . '
				';
        if (!empty($ofCategoryID)) {
            $SQL .= ' AND sc.categoryid = ' . (int) $ofCategoryID . "\n";
        }
        $SQL .= ' ORDER BY rank';
        $res = $GLOBALS['db']->queryAll($SQL);
        check_db_result($res);
        return $res;
    }

    protected function _getInstancesData($query_bits)
    {
        $db = $GLOBALS['db'];
        $sql = 'SELECT ' . implode(', ', $query_bits['select']) . '
				FROM ' . $query_bits['from'];
        if (!empty($query_bits['where'])) {
            $sql .= '
					WHERE ' . $query_bits['where'];
        }
        if (!empty($query_bits['group_by'])) {
            $sql .= '
					GROUP BY ' . $query_bits['group_by'];
        }
        if (!empty($query_bits['order_by'])) {
            $sql .= '
					ORDER BY ' . $query_bits['order_by'];
        }
        $res = $db->queryAll($sql, null, null, true, true);
        // 5th param forces array even if one col
        check_db_result($res);
        return $res;
    }

    static function getDateTypes()
    {
        $sql = 'SELECT id, name
				FROM date_type
				ORDER BY name';
        $res = $GLOBALS['db']->queryAll($sql, NULL, NULL, true);
        check_db_result($res);
        return $res;
    }