Example #1
0
 /**
  * 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;
 }
Example #3
0
 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";
     };
 }
Example #4
0
 /**
  * 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];
 }
Example #5
0
 /**
  * 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";
 }
Example #6
0
 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();
Example #8
0
 /**
  * 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;
 }
Example #9
0
 /**
  * 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;
 }
Example #10
0
 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;
 }
Example #11
0
 /**
  * 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;
 }
Example #12
0
 /**
  * Method used to get the list of issues to be displayed in the grid layout.
  *
  * @param   integer $prj_id The current project ID
  * @param   array $options The search parameters
  * @param   integer $current_row The current page number
  * @param   integer $max The maximum number of rows per page. 'ALL' for unlimited.
  * @return  array The list of issues to be displayed
  */
 public static function getListing($prj_id, $options, $current_row = 0, $max = 5)
 {
     if (strtoupper($max) == 'ALL') {
         $max = 9999999;
     }
     $start = $current_row * $max;
     // get the current user's role
     $usr_id = Auth::getUserID();
     $role_id = User::getRoleByUser($usr_id, $prj_id);
     $usr_details = User::getDetails($usr_id);
     // get any custom fields that should be displayed
     $custom_fields = Custom_Field::getFieldsToBeListed($prj_id);
     $stmt = 'SELECT
                 iss_id,
                 iss_grp_id,
                 iss_prj_id,
                 iss_sta_id,
                 iss_customer_id,
                 iss_customer_contract_id,
                 iss_created_date,
                 iss_updated_date,
                 iss_last_response_date,
                 iss_closed_date,
                 iss_last_customer_action_date,
                 iss_usr_id,
                 iss_summary,
                 pri_title,
                 prc_title,
                 sta_title,
                 sta_color status_color,
                 sta_id,
                 iqu_status,
                 grp_name,
                 pre_title,
                 iss_last_public_action_date,
                 iss_last_public_action_type,
                 iss_last_internal_action_date,
                 iss_last_internal_action_type,
                 ' . Issue::getLastActionFields() . ",\n                    CASE WHEN iss_last_internal_action_date > iss_last_public_action_date THEN 'internal' ELSE 'public' END AS action_type,\n                    iss_private,\n                    usr_full_name,\n                    iss_percent_complete,\n                    iss_dev_time,\n                    iss_expected_resolution_date,\n                    sev_title\n                 FROM\n                    (\n                    {{%issue}},\n                    {{%user}}";
     // join custom fields if we are searching by custom fields
     if (is_array($options['custom_field']) && count($options['custom_field']) > 0) {
         foreach ($options['custom_field'] as $fld_id => $search_value) {
             if (empty($search_value)) {
                 continue;
             }
             $field = Custom_Field::getDetails($fld_id);
             if ($field['fld_type'] == 'date' && (empty($search_value['Year']) || empty($search_value['Month']) || empty($search_value['Day']))) {
                 continue;
             }
             if ($field['fld_type'] == 'integer' && empty($search_value['value'])) {
                 continue;
             }
             if ($field['fld_type'] == 'multiple') {
                 $search_value = Misc::escapeString($search_value);
                 foreach ($search_value as $cfo_id) {
                     $stmt .= ",\n{{%issue_custom_field}} as cf" . $fld_id . '_' . $cfo_id . "\n";
                 }
             } else {
                 $stmt .= ",\n{{%issue_custom_field}} as cf" . $fld_id . "\n";
             }
         }
     }
     $stmt .= ')';
     // check for the custom fields we want to sort by
     if (strstr($options['sort_by'], 'custom_field') !== false) {
         $fld_id = str_replace('custom_field_', '', $options['sort_by']);
         $stmt .= "\n LEFT JOIN \n\n                    {{%issue_custom_field}} as cf_sort\n                ON\n                    (cf_sort.icf_iss_id = iss_id AND cf_sort.icf_fld_id = {$fld_id}) \n";
     }
     if (!empty($options['users']) || $options['sort_by'] === 'isu_usr_id') {
         $stmt .= '
              LEFT JOIN
                 {{%issue_user}}
              ON
                 isu_iss_id=iss_id';
     }
     if (!empty($usr_details['usr_par_code'])) {
         // restrict partners
         $stmt .= '
              LEFT JOIN
                 {{%issue_partner}}
              ON
                 ipa_iss_id=iss_id';
     }
     if (!empty($options['show_authorized_issues']) || $role_id == User::getRoleID('Reporter') && Project::getSegregateReporters($prj_id)) {
         $stmt .= '
              LEFT JOIN
                 {{%issue_user_replier}}
              ON
                 iur_iss_id=iss_id';
     }
     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) {
         $excerpts = self::getFullTextExcerpts();
     }
     foreach ($res as &$row) {
         $issue_id = $row['iss_id'];
         $row['time_spent'] = Misc::getFormattedTime($row['time_spent']);
         $row['iss_created_date'] = Date_Helper::getFormattedDate($row['iss_created_date']);
         $row['iss_expected_resolution_date'] = Date_Helper::getSimpleDate($row['iss_expected_resolution_date'], false);
         $row['excerpts'] = isset($excerpts[$issue_id]) ? $excerpts[$issue_id] : '';
         $fields = array();
         foreach (array_keys($columns_to_display) as $col_key) {
             switch ($col_key) {
                 case 'pri_rank':
                     $col_key = 'pri_title';
                     break;
                 case 'assigned':
                     $col_key = 'assigned_users';
                     break;
                 case 'sta_rank':
                     $col_key = 'sta_title';
                     break;
                 case 'sta_change_date':
                     $col_key = 'status_change_date';
                     break;
                 case 'sev_rank':
                     $col_key = 'sev_title';
                     break;
             }
             if ($col_key == 'custom_fields' && count($custom_fields) > 0) {
                 $custom_field_values = Custom_Field::getListByIssue($prj_id, $row['iss_id']);
                 foreach ($custom_field_values as $this_field) {
                     if (!empty($custom_fields[$this_field['fld_id']])) {
                         $row['custom_field'][$this_field['fld_id']] = $this_field['value'];
                         $fields[] = $this_field['value'];
                     }
                 }
             } else {
                 $fields[] = isset($row[$col_key]) ? $row[$col_key] : '';
             }
         }
         if (CRM::hasCustomerIntegration($prj_id)) {
             // check if current user is a customer and has a per incident contract.
             // if so, check if issue is redeemed.
             if (User::getRoleByUser($usr_id, $prj_id) == User::getRoleID('Customer')) {
                 // TODOCRM: Fix per incident usage
                 //                    if ((Customer::hasPerIncidentContract($prj_id, Issue::getCustomerID($res[$i]['iss_id'])) &&
                 //                            (Customer::isRedeemedIncident($prj_id, $res[$i]['iss_id'])))) {
                 //                        $res[$i]['redeemed'] = true;
                 //                    }
             }
         }
         $csv[] = @implode("\t", $fields);
     }
     $total_pages = ceil($total_rows / $max);
     $last_page = $total_pages - 1;
     return array('list' => $res, 'info' => array('current_page' => $current_row, 'start_offset' => $start, 'end_offset' => $start + count($res), 'total_rows' => $total_rows, 'total_pages' => $total_pages, 'previous_page' => $current_row == 0 ? '-1' : $current_row - 1, 'next_page' => $current_row == $last_page ? '-1' : $current_row + 1, 'last_page' => $last_page, 'custom_fields' => $custom_fields), 'csv' => @implode("\n", $csv));
 }
Example #13
0
 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;
 }
Example #14
0
    /**
     * 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;
    }
Example #15
0
 /**
  * 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;
 }
Example #16
0
 public static function getProjectsForPartner($par_code)
 {
     $sql = 'SELECT
                 pap_prj_id,
                 prj_title
             FROM
                 {{%partner_project}},
                 {{%project}}
             WHERE
                 pap_prj_id = prj_id AND
                 pap_par_code = ?';
     try {
         $res = DB_Helper::getInstance()->getPair($sql, array($par_code));
     } catch (DbException $e) {
         return array();
     }
     return $res;
 }
Example #17
0
 /**
  * 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;
 }
Example #18
0
 /**
  * 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;
 }
Example #19
0
 /**
  * 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];
 }
Example #20
0
 /**
  * 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];
 }
Example #21
0
 /**
  * Method used to get the title for a specific project category.
  *
  * @param   integer $prc_id The category ID
  * @return  string The category title
  */
 public static function getTitle($prc_id)
 {
     $stmt = 'SELECT
                 prc_title
              FROM
                 {{%project_category}}
              WHERE
                 prc_id=?';
     try {
         $res = DB_Helper::getInstance()->getOne($stmt, array($prc_id));
     } catch (DbException $e) {
         return '';
     }
     return $res;
 }
Example #22
0
 /**
  * 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;
 }
Example #23
0
define('INSTALL_PATH', dirname(__FILE__) . '/..');
define('CONFIG_PATH', INSTALL_PATH . '/config');
// avoid setup redirecting us
if (!file_exists(CONFIG_PATH . '/setup.php') || !filesize(CONFIG_PATH . '/setup.php') || !is_readable(CONFIG_PATH . '/setup.php')) {
    error_log("ERROR: Can't get setup.php in '" . CONFIG_PATH . "'");
    error_log('Did you forgot to copy config from old install? Is file readable?');
    exit(1);
}
// load init only if no autoloader present
if (!class_exists('DB_Helper')) {
    require_once INSTALL_PATH . '/init.php';
}
$in_setup = defined('IN_SETUP');
global $dbconfig, $db;
$dbconfig = DB_Helper::getConfig();
$db = DB_Helper::getInstance();
function exec_sql_file($input_file)
{
    if (!file_exists($input_file) && !is_readable($input_file)) {
        throw new RuntimeException("Can't read file: {$input_file}");
    }
    global $dbconfig, $db;
    // use *.php for complex updates
    if (substr($input_file, -4) == '.php') {
        $queries = array();
        require $input_file;
    } else {
        $queries = explode(';', file_get_contents($input_file));
    }
    foreach ($queries as $query) {
        $query = trim($query);
Example #24
0
 /**
  * Returns an associative array with the list of faq entry
  * IDs and their respective ranking.
  *
  * @return  array The list of faq entries
  */
 private function _getRanking()
 {
     $stmt = 'SELECT
                 faq_id,
                 faq_rank
              FROM
                 {{%faq}}
              ORDER BY
                 faq_rank ASC';
     try {
         $res = DB_Helper::getInstance()->getPair($stmt);
     } catch (DbException $e) {
         return array();
     }
     return $res;
 }
 /**
  * Returns the replier based on the given issue and email address combo.
  *
  * @param   integer $issue_id The id of the issue.
  * @param   string $email The email address of the user
  * @return  integer The id of the replier
  */
 public function getReplierIDByEmail($issue_id, $email)
 {
     $stmt = 'SELECT
                 iur_id
              FROM
                 {{%issue_user_replier}}
                 LEFT JOIN
                     {{%user}}
                 ON
                     iur_usr_id = usr_id
              WHERE
                 iur_iss_id = ? AND
                 (iur_email = ? OR usr_email = ?)';
     try {
         $res = DB_Helper::getInstance()->getOne($stmt, array($issue_id, $email, $email));
     } catch (DbException $e) {
         return 0;
     }
     return $res;
 }
Example #26
0
 /**
  * Returns workload information for the specified date range and interval.
  *
  * @param   string $interval The interval to use in this report.
  * @param   string $type If this report is aggregate or individual
  * @param   string $start The start date of this report.
  * @param   string $end The end date of this report.
  * @param   integer $category_id The category to restrict this report to
  * @return  array An array containing workload data.
  */
 public static function getWorkloadByDateRange($interval, $type, $start, $end, $category_id)
 {
     $data = array();
     $category_id = (int) $category_id;
     // figure out the correct format code
     switch ($interval) {
         case 'day':
             $format = '%m/%d/%y';
             $order_by = "%1\$s";
             break;
         case 'dow':
             $format = '%W';
             $order_by = "CASE WHEN DATE_FORMAT(%1\$s, '%%w') = 0 THEN 7 ELSE DATE_FORMAT(%1\$s, '%%w') END";
             break;
         case 'week':
             if ($type == 'aggregate') {
                 $format = '%v';
             } else {
                 $format = '%v/%y';
             }
             $order_by = "%1\$s";
             break;
         case 'dom':
             $format = '%d';
             break;
         case 'month':
             if ($type == 'aggregate') {
                 $format = '%b';
                 $order_by = "DATE_FORMAT(%1\$s, '%%m')";
             } else {
                 $format = '%b/%y';
                 $order_by = "%1\$s";
             }
             break;
         default:
             throw new LogicException('Invalid interval');
     }
     // get issue counts
     $stmt = 'SELECT
                 DATE_FORMAT(iss_created_date, ?),
                 count(*)
              FROM
                 {{%issue}}
              WHERE
                 iss_prj_id=? AND
                 iss_created_date BETWEEN ? AND ?';
     $params = array($format, Auth::getCurrentProject(), $start, $end);
     if (!empty($category_id)) {
         $stmt .= ' AND
                 iss_prc_id = ?';
         $params[] = $category_id;
     }
     $stmt .= '
              GROUP BY
                 DATE_FORMAT(iss_created_date, ?)';
     $params[] = $format;
     if (!empty($order_by)) {
         $stmt .= "\nORDER BY " . sprintf($order_by, 'iss_created_date');
     }
     try {
         $res = DB_Helper::getInstance()->fetchAssoc($stmt, $params);
     } catch (DbException $e) {
         return array();
     }
     $data['issues']['points'] = $res;
     $data['issues']['stats'] = array('total' => 0, 'avg' => 0, 'median' => 0, 'max' => 0);
     if ($res) {
         $stats = new Math_Stats();
         $stats->setData($res);
         $data['issues']['stats'] = array('total' => $stats->sum(), 'avg' => $stats->mean(), 'median' => $stats->median(), 'max' => $stats->max());
     }
     // get email counts
     $params = array();
     $stmt = 'SELECT
                 DATE_FORMAT(sup_date, ?),
                 count(*)
              FROM
                 {{%support_email}},
                 {{%email_account}}';
     $params[] = $format;
     if (!empty($category_id)) {
         $stmt .= ',
                  {{%issue}}';
     }
     $stmt .= '
              WHERE
                 sup_ema_id=ema_id AND
                 ema_prj_id=? AND
                 sup_date BETWEEN ? AND ?';
     $params[] = Auth::getCurrentProject();
     $params[] = $start;
     $params[] = $end;
     if (!empty($category_id)) {
         $stmt .= ' AND
                 sup_iss_id = iss_id AND
                 iss_prc_id = ?';
         $params[] = $category_id;
     }
     $stmt .= '
              GROUP BY
                 DATE_FORMAT(sup_date, ?)';
     $params[] = $format;
     if (!empty($order_by)) {
         $stmt .= "\nORDER BY " . sprintf($order_by, 'sup_date');
     }
     try {
         $res = DB_Helper::getInstance()->fetchAssoc($stmt, $params);
     } catch (DbException $e) {
         return array();
     }
     $data['emails']['points'] = $res;
     if (count($res) > 0) {
         $stats = new Math_Stats();
         $stats->setData($res);
         $data['emails']['stats'] = array('total' => $stats->sum(), 'avg' => $stats->mean(), 'median' => $stats->median(), 'max' => $stats->max());
     } else {
         $data['emails']['stats'] = array('total' => 0, 'avg' => 0, 'median' => 0, 'max' => 0);
     }
     return $data;
 }
Example #27
0
 /**
  * Method used to get a list as an associative array of the
  * releases.
  *
  * @param   integer $prj_id The project ID
  * @param   boolean $show_all_dates If true all releases, not just those with future dates will be returned
  * @return  array The list of releases
  */
 public static function getAssocList($prj_id, $show_all_dates = false)
 {
     $stmt = 'SELECT
                 pre_id,
                 pre_title
              FROM
                 {{%project_release}}
              WHERE
                 pre_prj_id=? AND
                 (
                   pre_status=?';
     $params = array($prj_id, 'available');
     if ($show_all_dates != true) {
         $stmt .= ' AND
                   pre_scheduled_date >= ?';
         $params[] = gmdate('Y-m-d');
     }
     $stmt .= '
                 )
              ORDER BY
                 pre_scheduled_date ASC';
     try {
         $res = DB_Helper::getInstance()->getPair($stmt, $params);
     } catch (DbException $e) {
         return '';
     }
     return $res;
 }
Example #28
0
function write_config()
{
    $config_file_path = APP_CONFIG_PATH . '/config.php';
    // disable the full-text search feature for certain mysql server users
    /** @var DbInterface $conn */
    $mysql_version = DB_Helper::getInstance(false)->getOne('SELECT VERSION()');
    preg_match('/(\\d{1,2}\\.\\d{1,2}\\.\\d{1,2})/', $mysql_version, $matches);
    $enable_fulltext = $matches[1] > '4.0.23';
    $replace = array("'%{APP_HOSTNAME}%'" => e($_POST['hostname']), "'%{CHARSET}%'" => e(APP_CHARSET), "'%{APP_RELATIVE_URL}%'" => e($_POST['relative_url']), "'%{APP_DEFAULT_TIMEZONE}%'" => e($_POST['default_timezone']), "'%{APP_DEFAULT_WEEKDAY}%'" => (int) $_POST['default_weekday'], "'%{PROTOCOL_TYPE}%'" => e(@$_POST['is_ssl'] == 'yes' ? 'https://' : 'http://'), "'%{APP_ENABLE_FULLTEXT}%'" => e($enable_fulltext));
    $config_contents = file_get_contents(APP_CONFIG_PATH . '/config.dist.php');
    $config_contents = str_replace(array_keys($replace), array_values($replace), $config_contents);
    write_file($config_file_path, $config_contents);
}
Example #29
0
 /**
  * Method used to get the list of associated projects for a given
  * news entry.
  *
  * @param   integer $nws_id The news ID
  * @return  array The list of projects
  */
 public function getAssociatedProjects($nws_id)
 {
     $stmt = 'SELECT
                 prj_id,
                 prj_title
              FROM
                 {{%project}},
                 {{%project_news}}
              WHERE
                 prj_id=prn_prj_id AND
                 prn_nws_id=?';
     try {
         $res = DB_Helper::getInstance()->getPair($stmt, array($nws_id));
     } catch (DbException $e) {
         return array();
     }
     return $res;
 }
    exit;
}
// TODO: move this query to some class
$sql = 'SELECT
            prc_id,
        	prc_title,
        	SUM(iss_dev_time) as dev_time
        FROM
        	{{%issue}},
        	{{%project_category}},
        	{{%status}}
        WHERE
        	iss_prc_id = prc_id AND
        	iss_sta_id = sta_id AND
        	sta_is_closed != 1 AND
        	iss_prj_id = ?
        GROUP BY
        	iss_prc_id';
try {
    $res = DB_Helper::getInstance()->getAll($sql, array(Auth::getCurrentProject()));
} catch (DbException $e) {
    return false;
}
$total = 0;
foreach ($res as $id => $row) {
    $total += $row['dev_time'];
    $res[$id]['dev_time'] = str_replace(' ', '&nbsp;', str_pad($row['dev_time'], 5, ' ', STR_PAD_LEFT));
}
$res[] = array('dev_time' => str_replace(' ', '&nbsp;', str_pad($total, 5, ' ', STR_PAD_LEFT)), 'prc_title' => 'Total');
$tpl->assign('data', $res);
$tpl->displayTemplate();