PHP DB_Helper类代码示例

 /**
  * Returns the total number of rows for a specific query. It is used to
  * calculate the total number of pages of data.
  *
  * @param   string $stmt The SQL statement
  * @return  int The total number of rows
  */
 public static function getTotalRows($stmt)
 {
     $stmt = str_replace("\n", '', $stmt);
     $stmt = str_replace("\r", '', $stmt);
     if (stristr($stmt, 'GROUP BY')) {
         // go the extra mile and try to use the grouped by column in the count() call
         preg_match("/.*\\s+GROUP BY\\s+(\\w*)\\s+.*/i", $stmt, $matches);
         if (!empty($matches[1])) {
             $stmt = preg_replace('/SELECT (.*?) FROM /si', 'SELECT COUNT(DISTINCT ' . $matches[1] . ') AS total_rows FROM ', $stmt);
         }
     } else {
         $stmt = preg_replace('/SELECT (.*?) FROM /si', 'SELECT COUNT(*) AS total_rows FROM ', $stmt);
     }
     // remove any order by clauses
     $stmt = preg_replace("/(.*)(ORDER BY\\s+\\w+\\s+\\w+)(?:,\\s+\\w+\\s+\\w+)*(.*)/si", '\\1\\3', $stmt);
     try {
         $rows = DB_Helper::getInstance()->getAll($stmt);
     } catch (DbException $e) {
         return 0;
     }
     if (empty($rows)) {
         return 0;
     }
     // the query above works only if there is no left join or any other complex queries
     if (count($rows) == 1) {
         return $rows[0]['total_rows'];
     }
     return count($rows);
 }

 /**
  * Modifies an Issue's Reporter.
  *
  * @param   integer $issue_id The id of the issue.
  * @param   string $fullname The id of the user.
  * @param   boolean $add_history If this should be logged.
  * @return int
  */
 public static function update($issue_id, $email, $add_history = true)
 {
     $email = strtolower(Mail_Helper::getEmailAddress($email));
     $usr_id = User::getUserIDByEmail($email, true);
     // If no valid user found reset to system account
     if (!$usr_id) {
         $usr_id = APP_SYSTEM_USER_ID;
     }
     $sql = 'UPDATE
                 {{%issue}}
             SET
                 iss_usr_id = ?
             WHERE
                 iss_id = ?';
     try {
         DB_Helper::getInstance()->query($sql, array($usr_id, $issue_id));
     } catch (DbException $e) {
         return -1;
     }
     if ($add_history) {
         // TRANSLATORS: %1: email, %2: full name
         $current_usr_id = Auth::getUserID();
         History::add($issue_id, $current_usr_id, 'issue_updated', 'Reporter was changed to {email} by {user}', array('email' => $email, 'user' => User::getFullName($current_usr_id)));
     }
     // Add new user to notification list
     if ($usr_id > 0) {
         Notification::subscribeEmail($usr_id, $issue_id, $email, Notification::getDefaultActions());
     }
     return 1;
 }

 public function __construct($schema_dir)
 {
     $this->db = DB_Helper::getInstance();
     $this->dir = $schema_dir;
     $this->config = DB_Helper::getConfig();
     $this->table_prefix = $this->config['table_prefix'];
     $this->logger = function ($e) {
         echo $e, "\n";
     };
 }

 /**
  * Returns the name of the workflow backend for the specified project.
  *
  * @param   integer $prj_id The id of the project to lookup.
  * @return  string The name of the customer backend.
  */
 private static function _getBackendNameByProject($prj_id)
 {
     static $backends;
     if (isset($backends[$prj_id])) {
         return $backends[$prj_id];
     }
     $stmt = 'SELECT
                 prj_id,
                 prj_workflow_backend
              FROM
                 {{%project}}
              ORDER BY
                 prj_id';
     try {
         $res = DB_Helper::getInstance()->getPair($stmt);
     } catch (DbException $e) {
         return '';
     }
     $backends = $res;
     return @$backends[$prj_id];
 }

 /**
  * Called when issue is closed.
  *
  * @param   integer $prj_id The project ID
  * @param   integer $issue_id The ID of the issue.
  * @param   boolean $send_notification Whether to send a notification about this action or not
  * @param   integer $resolution_id The resolution ID
  * @param   integer $status_id The status ID
  * @param   string $reason The reason for closing this issue
  * @return  void
  */
 function handleIssueClosed($prj_id, $issue_id, $send_notification, $resolution_id, $status_id, $reason)
 {
     $sql = "UPDATE\n                    {{%issue}}\n                SET\n                    iss_percent_complete = '100%'\n                WHERE\n                    iss_id = ?";
     try {
         DB_Helper::getInstance()->query($sql, array($issue_id));
     } catch (DbException $e) {
         return;
     }
     echo "Workflow: handleIssueClosed<br />\n";
 }

 public static function unlock($usr_id)
 {
     $stmt = 'UPDATE
                 {{%user}}
              SET
                 usr_failed_logins = 0
              WHERE
                 usr_id=?';
     try {
         DB_Helper::getInstance()->query($stmt, array($usr_id));
     } catch (DbException $e) {
         return false;
     }
     return true;
 }

Auth::checkAuthentication();
if (!Access::canAccessReports(Auth::getUserID())) {
    echo 'Invalid role';
    exit;
}
// TODO: move this query to some class
$prj_id = Auth::getCurrentProject();
$categories = Category::getAssocList($prj_id);
$statuses = Status::getAssocStatusList($prj_id, true);
$data = array();
foreach ($categories as $cat_id => $cat_title) {
    $data[$cat_id] = array('title' => $cat_title, 'statuses' => array());
    foreach ($statuses as $sta_id => $sta_title) {
        $sql = 'SELECT
                    count(*)
                FROM
                    {{%issue}}
                WHERE
                    iss_prj_id = ? AND
                    iss_sta_id = ? AND
                    iss_prc_id = ?';
        try {
            $res = DB_Helper::getInstance()->getOne($sql, array($prj_id, $sta_id, $cat_id));
        } catch (DbException $e) {
            break 2;
        }
        $data[$cat_id]['statuses'][$sta_id] = array('title' => $sta_title, 'count' => $res);
    }
}
$tpl->assign(array('statuses' => $statuses, 'categories' => $categories, 'data' => $data));
$tpl->displayTemplate();

 /**
  * Checks if a message already is downloaded..
  *
  * @param   string $message_id The Message-ID header
  * @return  boolean
  */
 public static function exists($message_id)
 {
     $sql = 'SELECT
                 count(*)
             FROM
                 {{%note}}
             WHERE
                 not_message_id = ?';
     try {
         $res = DB_Helper::getInstance()->getOne($sql, array($message_id));
     } catch (DbException $e) {
         return false;
     }
     if ($res > 0) {
         return true;
     }
     return false;
 }

 /**
  * Method used to add a new resolution by using the administrative
  * interface of the system.
  *
  * @return  integer 1 if the update worked, -1 or -2 otherwise
  */
 public static function insert()
 {
     if (Validation::isWhitespace($_POST['title'])) {
         return -2;
     }
     $stmt = 'INSERT INTO
                 {{%resolution}}
              (
                 res_title,
                 res_rank,
                 res_created_date
              ) VALUES (
                 ?, ?, ?
              )';
     $params = array($_POST['title'], $_POST['rank'], Date_Helper::getCurrentDateGMT());
     try {
         DB_Helper::getInstance()->query($stmt, $params);
     } catch (DbException $e) {
         return -1;
     }
     return 1;
 }

 private function reminderActivity()
 {
     $sql = "SELECT\n                    {{%reminder_history}}.*,\n                    iss_summary,\n                    sta_color,\n                    rma_title\n                FROM\n                    {{%reminder_history}},\n                    {{%reminder_action}},\n                    {{%issue}},\n                    {{%status}}\n                WHERE\n                    iss_sta_id = sta_id AND\n                    rmh_iss_id = iss_id AND\n                    rmh_rma_id = rma_id AND\n                    iss_prj_id = ? AND\n";
     $params = array($this->prj_id);
     $this->createWhereClause($sql, $params, 'rmh_created_date');
     $res = DB_Helper::getInstance()->getAll($sql, $params);
     $this->processResult($res, 'rmh_created_date', 'rmh_iss_id');
     return $res;
 }

 /**
  * Analyzes the contents of the issue_custom_field and updates
  * contents based on the fld_type.
  *
  * @param   integer $fld_id
  * @return bool
  */
 public static function updateValuesForNewType($fld_id)
 {
     $details = self::getDetails($fld_id, true);
     $db_field_name = self::getDBValueFieldNameByType($details['fld_type']);
     $sql = 'UPDATE
                 {{%issue_custom_field}}
             SET
                 ';
     if ($details['fld_type'] == 'integer') {
         $sql .= "{$db_field_name} = IFNULL(icf_value, IFNULL(icf_value_date, NULL)),\n                    icf_value = NULL,\n                    icf_value_date = NULL";
     } elseif ($details['fld_type'] == 'date') {
         $sql .= "{$db_field_name} = IFNULL(icf_value, IFNULL(icf_value_date, NULL)),\n                    icf_value = NULL,\n                    icf_value_integer = NULL";
     } else {
         $sql .= "{$db_field_name} = IFNULL(icf_value_integer, IFNULL(icf_value_date, NULL)),\n                    icf_value_integer = NULL,\n                    icf_value_date = NULL";
     }
     $sql .= "\n                WHERE\n                    {$db_field_name} IS NULL AND\n                    icf_fld_id = ?";
     $params = array($fld_id);
     try {
         DB_Helper::getInstance()->query($sql, $params);
     } catch (DbException $e) {
         return false;
     }
     return true;
 }

//.........这里部分代码省略.........
     if (!empty($options['show_notification_list_issues'])) {
         $stmt .= '
              LEFT JOIN
                 {{%subscription}}
              ON
                 sub_iss_id=iss_id';
     }
     if (!empty($options['product'])) {
         $stmt .= '
              LEFT JOIN
                 {{%issue_product_version}}
              ON
                 ipv_iss_id=iss_id';
     }
     $stmt .= "\n                 LEFT JOIN\n                    {{%group}}\n                 ON\n                    iss_grp_id=grp_id\n                 LEFT JOIN\n                    {{%project_category}}\n                 ON\n                    iss_prc_id=prc_id\n                 LEFT JOIN\n                    {{%project_release}}\n                 ON\n                    iss_pre_id = pre_id\n                 LEFT JOIN\n                    {{%status}}\n                 ON\n                    iss_sta_id=sta_id\n                 LEFT JOIN\n                    {{%project_priority}}\n                 ON\n                    iss_pri_id=pri_id\n                 LEFT JOIN\n                    {{%project_severity}}\n                 ON\n                    iss_sev_id=sev_id\n                 LEFT JOIN\n                    {{%issue_quarantine}}\n                 ON\n                    iss_id=iqu_iss_id AND\n                    (iqu_expiration > '" . Date_Helper::getCurrentDateGMT() . "' OR iqu_expiration IS NULL)\n                 WHERE\n                    iss_prj_id= " . Misc::escapeInteger($prj_id);
     $stmt .= self::buildWhereClause($options);
     if (strstr($options['sort_by'], 'custom_field') !== false) {
         $fld_details = Custom_Field::getDetails($fld_id);
         $sort_by = 'cf_sort.' . Custom_Field::getDBValueFieldNameByType($fld_details['fld_type']);
     } else {
         $sort_by = Misc::escapeString($options['sort_by']);
     }
     $stmt .= '
              GROUP BY
                 iss_id
              ORDER BY
                 ' . $sort_by . ' ' . Misc::escapeString($options['sort_order']) . ',
                 iss_id DESC';
     $total_rows = Pager::getTotalRows($stmt);
     $stmt .= '
              LIMIT
                 ' . Misc::escapeInteger($max) . ' OFFSET ' . Misc::escapeInteger($start);
     try {
         $res = DB_Helper::getInstance()->getAll($stmt);
     } catch (DbException $e) {
         return array('list' => null, 'info' => null, 'csv' => null);
     }
     if (count($res) > 0) {
         Issue::getAssignedUsersByIssues($res);
         Time_Tracking::fillTimeSpentByIssues($res);
         // need to get the customer titles for all of these issues...
         if (CRM::hasCustomerIntegration($prj_id)) {
             $crm = CRM::getInstance($prj_id);
             $crm->processListIssuesResult($res);
         }
         Issue::formatLastActionDates($res);
         Issue::getLastStatusChangeDates($prj_id, $res);
     } elseif ($current_row > 0) {
         // if there are no results, and the page is not the first page reset page to one and reload results
         Auth::redirect("list.php?pagerRow=0&rows={$max}");
     }
     $groups = Group::getAssocList($prj_id);
     $categories = Category::getAssocList($prj_id);
     $column_headings = array();
     $columns_to_display = Display_Column::getColumnsToDisplay($prj_id, 'list_issues');
     foreach ($columns_to_display as $col_key => $column) {
         if ($col_key == 'custom_fields' && count($custom_fields) > 0) {
             foreach ($custom_fields as $fld_id => $fld_title) {
                 $column_headings['cstm_' . $fld_id] = $fld_title;
             }
         } else {
             $column_headings[$col_key] = $column['title'];
         }
     }
     $csv[] = @implode("\t", $column_headings);
     if (@$options['hide_excerpts'] != 1 && self::doesBackendSupportExcerpts() == true) {

 public static function updateProductAndVersion($ipv_id, $pro_id, $version)
 {
     if ($pro_id == -1) {
         $sql = 'DELETE FROM
                     {{%issue_product_version}}
                 WHERE
                     ipv_id = ?';
         $params = array($ipv_id);
     } else {
         $sql = 'UPDATE
                     {{%issue_product_version}}
                 SET
                     ipv_pro_id = ?,
                     ipv_version = ?
                 WHERE
                     ipv_id = ?';
         $params = array($pro_id, $version, $ipv_id);
     }
     try {
         DB_Helper::getInstance()->query($sql, $params);
     } catch (DbException $e) {
         return false;
     }
     return true;
 }

    /**
     * Method used to get the sev_id of a project by severity title.
     *
     * @param   integer $prj_id The project ID
     * @param   integer $sev_id The severity ID
     * @param   string $sev_title The severity title
     * @return  integer $sev_id The severity ID
     */
    public static function getID($prj_id, $sev_title)
    {
        $sql = 'SELECT
                    sev_id
                 FROM
                    {{%project_severity}}
                 WHERE
                    sev_prj_id=?
					AND sev_title = ?';
        try {
            $res = DB_Helper::getInstance()->getOne($sql, array($prj_id, $sev_title));
        } catch (DbException $e) {
            return false;
        }
        return $res;
    }

 /**
  * Method used to escape a string before using it in a query.
  *
  * @param   string|array $input The original string
  * @return  string|array The escaped (or not) string
  * @deprecated Using this is bad design, must use placeholders in query
  */
 public static function escapeString($input, $add_quotes = false)
 {
     if (is_array($input)) {
         foreach ($input as $key => $value) {
             $input[$key] = self::escapeString($value, $add_quotes);
         }
     } else {
         $input = DB_Helper::escapeString($input, $add_quotes);
     }
     return $input;
 }