PHP DB_Helper::getInstance方法代码示例

 /**
  * 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];
 }

 /**
  * Logs the error condition to a specific file and if asked and possible
  * queue error in mail queue for reporting.
  *
  * @param  mixed $error_msg The error message
  * @param  string $script The script name where the error happened
  * @param  integer $line The line number where the error happened
  * @param  boolean $notify_error Whether error should be notified by email.
  */
 public static function logError($error_msg = 'unknown', $script = 'unknown', $line = 0, $notify_error = true)
 {
     $msg =& self::_createErrorReport($error_msg, $script, $line);
     if (is_resource(APP_ERROR_LOG)) {
         fwrite(APP_ERROR_LOG, date('[D M d H:i:s Y] '));
         fwrite(APP_ERROR_LOG, $msg);
     } else {
         file_put_contents(APP_ERROR_LOG, array(date('[D M d H:i:s Y] '), $msg), FILE_APPEND);
     }
     // if there's no database connection, then we cannot possibly queue up the error emails
     $dbh = DB_Helper::getInstance();
     if ($notify_error === false || !$dbh) {
         return;
     }
     $setup = Setup::get();
     if (isset($setup['email_error']['status']) && $setup['email_error']['status'] == 'enabled') {
         $notify_list = trim($setup['email_error']['addresses']);
         if (empty($notify_list)) {
             return;
         }
         self::_notify($msg, $setup['smtp']['from'], $notify_list);
     }
 }

 /**
  * Method used to set the preferences for a specific user.
  *
  * @param   integer $usr_id The user ID
  * @param   array   $preferences An array of preferences
  * @return  integer 1 if the update worked, -1 otherwise
  */
 public static function set($usr_id, $preferences)
 {
     // set global preferences
     $sql = 'REPLACE INTO
                 {{%user_preference}}
             SET
                 upr_usr_id = ?,
                 upr_timezone = ?,
                 upr_week_firstday = ?,
                 upr_list_refresh_rate = ?,
                 upr_email_refresh_rate = ?,
                 upr_email_signature = ?,
                 upr_auto_append_email_sig = ?,
                 upr_auto_append_note_sig = ?,
                 upr_auto_close_popup_window = ?';
     try {
         DB_Helper::getInstance()->query($sql, array($usr_id, @$preferences['timezone'], @$preferences['week_firstday'], @$preferences['list_refresh_rate'], @$preferences['email_refresh_rate'], @$preferences['email_signature'], @$preferences['auto_append_email_sig'], @$preferences['auto_append_note_sig'], @$preferences['close_popup_windows']));
     } catch (DbException $e) {
         return -1;
     }
     // set per project preferences
     $projects = Project::getAssocList($usr_id);
     foreach ($projects as $prj_id => $project_name) {
         $sql = 'REPLACE INTO
                     {{%user_project_preference}}
                 SET
                     upp_usr_id = ?,
                     upp_prj_id = ?,
                     upp_receive_assigned_email = ?,
                     upp_receive_new_issue_email = ?,
                     upp_receive_copy_of_own_action = ?';
         try {
             DB_Helper::getInstance()->query($sql, array($usr_id, $prj_id, $preferences['receive_assigned_email'][$prj_id], $preferences['receive_new_issue_email'][$prj_id], $preferences['receive_copy_of_own_action'][$prj_id]));
         } catch (DbException $e) {
             return -1;
         }
     }
     return 1;
 }

 /**
  * Returns the issue ID of the issue with the specified root message ID, or false
  * @param   string $msg_id The Message ID
  * @return  integer The ID of the issue
  */
 public static function getIssueByRootMessageID($msg_id)
 {
     static $returns;
     if (!empty($returns[$msg_id])) {
         return $returns[$msg_id];
     }
     $sql = 'SELECT
                 iss_id
             FROM
                 {{%issue}}
             WHERE
                 iss_root_message_id = ?';
     try {
         $res = DB_Helper::getInstance()->getOne($sql, array($msg_id));
     } catch (DbException $e) {
         return false;
     }
     if (empty($res)) {
         $returns[$msg_id] = false;
     } else {
         $returns[$msg_id] = $res;
     }
     return $returns[$msg_id];
 }

 /**
  * Method used to remove all custom filters associated with some
  * specific projects.
  *
  * @param   array $ids List of projects to remove from
  * @return  boolean Whether the removal worked properly or not
  */
 public static function removeByProjects($ids)
 {
     $stmt = 'DELETE FROM
                 {{%custom_filter}}
              WHERE
                 cst_prj_id IN (' . DB_Helper::buildList($ids) . ')';
     try {
         DB_Helper::getInstance()->query($stmt, $ids);
     } catch (DbException $e) {
         return false;
     }
     return true;
 }

 /**
  * 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";
 }

 /**
  * 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;
 }

 /**
  * 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 get the list of technical account managers for
  * a given customer ID.
  *
  * @return  array The list of account managers
  */
 public function getEventumAccountManagers()
 {
     $stmt = 'SELECT
                 cam_usr_id,
                 usr_email,
                 cam_type
              FROM
                 {{%customer_account_manager}},
                 {{%user}}
              WHERE
                 cam_usr_id=usr_id AND
                 cam_prj_id=? AND
                 cam_customer_id=?';
     $params = array($this->crm->getProjectID(), $this->customer_id);
     try {
         $res = DB_Helper::getInstance()->getAll($stmt, $params);
     } catch (DbException $e) {
         return array();
     }
     if (empty($res)) {
         return array();
     }
     return $res;
 }

 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;
 }

//.........这里部分代码省略.........
     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) {

 /**
  * Returns the sequential number of the specified email ID.
  *
  * @param   integer $sup_id The email ID
  * @return  integer The sequence number of the email
  */
 public static function getSequenceByID($sup_id)
 {
     if (empty($sup_id)) {
         return '';
     }
     try {
         DB_Helper::getInstance()->query('SET @sup_seq = 0');
     } catch (DbException $e) {
         return 0;
     }
     $issue_id = Support::getIssueFromEmail($sup_id);
     $sql = 'SELECT
                 sup_id,
                 @sup_seq := @sup_seq+1
             FROM
                 {{%support_email}}
             WHERE
                 sup_iss_id = ?
             ORDER BY
                 sup_id ASC';
     try {
         $res = DB_Helper::getInstance()->getPair($sql, array($issue_id));
     } catch (DbException $e) {
         return 0;
     }
     return @$res[$sup_id];
 }