/**
* 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));
}
/**
* Method used to get the full listing of time entries in the system for a
* specific issue
*
* @param integer $issue_id The issue ID
* @return array The full list of time entries
*/
public static function getTimeEntryListing($issue_id)
{
$stmt = 'SELECT
ttr_id,
ttr_created_date,
ttr_summary,
ttr_time_spent,
ttc_title,
ttr_usr_id,
usr_full_name
FROM
{{%time_tracking}},
{{%time_tracking_category}},
{{%user}}
WHERE
ttr_ttc_id=ttc_id AND
ttr_usr_id=usr_id AND
ttr_iss_id=?
ORDER BY
ttr_created_date ASC';
try {
$res = DB_Helper::getInstance()->getAll($stmt, array($issue_id));
} catch (DbException $e) {
return 0;
}
$total_time_spent = 0;
$total_time_by_user = array();
foreach ($res as &$row) {
$row['ttr_summary'] = Link_Filter::processText(Issue::getProjectID($issue_id), nl2br(htmlspecialchars($row['ttr_summary'])));
$row['formatted_time'] = Misc::getFormattedTime($row['ttr_time_spent']);
$row['ttr_created_date'] = Date_Helper::getFormattedDate($row['ttr_created_date']);
if (isset($total_time_by_user[$row['ttr_usr_id']])) {
$total_time_by_user[$row['ttr_usr_id']]['time_spent'] += $row['ttr_time_spent'];
} else {
$total_time_by_user[$row['ttr_usr_id']] = array('usr_full_name' => $row['usr_full_name'], 'time_spent' => $row['ttr_time_spent']);
}
$total_time_spent += $row['ttr_time_spent'];
}
usort($total_time_by_user, function ($a, $b) {
return $a['time_spent'] < $b['time_spent'];
});
foreach ($total_time_by_user as &$item) {
$item['time_spent'] = Misc::getFormattedTime($item['time_spent']);
}
return array('total_time_spent' => Misc::getFormattedTime($total_time_spent), 'total_time_by_user' => $total_time_by_user, 'list' => $res);
}
/**
* Format "On ... Wrote:" reply preamble. Helper for translations.
*
* @param string $date
* @param string $sender
* @return string
*/
public static function formatReplyPreamble($date, $sender)
{
$date = Date_Helper::getFormattedDate($date);
// TRANSLATORS: %1: date, %2: sender
$line = ev_gettext('On %1$s, %2$s wrote:', $date, $sender);
return "\n\n\n{$line}\n>\n";
}
/**
* Returns a list of drafts associated with an issue.
*
* @param integer $issue_id The ID of the issue.
* @param boolean $show_all If all draft statuses should be shown
* @return array An array of drafts.
*/
public static function getList($issue_id, $show_all = false)
{
$stmt = "SELECT\n emd_id,\n emd_usr_id,\n emd_subject,\n emd_updated_date,\n emd_unknown_user,\n emd_status\n FROM\n {{%email_draft}}\n WHERE\n emd_iss_id=?\n";
$params = array($issue_id);
if ($show_all == false) {
$stmt .= "AND emd_status = 'pending'\n";
}
$stmt .= 'ORDER BY
emd_id';
try {
$res = DB_Helper::getInstance()->getAll($stmt, $params);
} catch (DbException $e) {
return '';
}
foreach ($res as &$row) {
$row['emd_updated_date'] = Date_Helper::getFormattedDate($row['emd_updated_date']);
if (!empty($row['emd_unknown_user'])) {
$row['from'] = $row['emd_unknown_user'];
} else {
$row['from'] = User::getFromHeader($row['emd_usr_id']);
}
list($row['to']) = self::getEmailRecipients($row['emd_id']);
if (empty($row['to'])) {
$row['to'] = 'Notification List';
}
}
return $res;
}
/**
* Method used to print a confirmation prompt with the current details
* of the given issue. The $command parameter can be used to determine what type of
* confirmation to show to the user.
*
* @param RemoteApi $client The connection resource
* @param array $auth Array of authentication information (email, password)
* @param integer $issue_id The issue ID
* @param string $args The arguments passed to this script
*/
public static function promptConfirmation($client, $auth, $issue_id, $args)
{
// this is needed to prevent multiple confirmations from being shown to the user
$GLOBALS['_displayed_confirmation'] = true;
// get summary, customer status and assignment of issue, then show confirmation prompt to user
$details = $client->getSimpleIssueDetails($auth[0], $auth[1], $issue_id);
switch ($args[2]) {
case 'convert-note':
case 'cn':
$note_details = self::getNote($client, $auth, $issue_id, $args[3]);
$not_created_date = Date_Helper::getFormattedDate($note_details['not_created_date']);
$msg = "These are the current details for issue #{$issue_id}, note #" . $args[3] . ":\n" . ' Date: ' . $not_created_date . "\n" . ' From: ' . $note_details['not_from'] . "\n" . ' Title: ' . $note_details['not_title'] . "\n" . 'Are you sure you want to convert this note into a ' . $args[4] . '?';
break;
default:
$msg = "These are the current details for issue #{$issue_id}:\n" . ' Summary: ' . $details['summary'] . "\n";
if (@(!empty($details['customer']))) {
$msg .= ' Customer: ' . $details['customer'] . "\n";
}
$msg .= ' Status: ' . $details['status'] . "\n" . ' Assignment: ' . $details['assignments'] . "\n" . ' Auth. Repliers: ' . $details['authorized_names'] . "\n" . 'Are you sure you want to change this issue?';
}
$ret = CLI_Misc::prompt($msg, 'y');
if (strtolower($ret) != 'y') {
exit;
}
}
private function processResult(&$data, $date_field, $issue_field)
{
$timezone = Date_Helper::getPreferredTimezone($this->usr_id);
foreach ($data as &$res) {
if (!Issue::canAccess($res[$issue_field], $this->usr_id)) {
continue;
}
$res['customer'] = null;
if ($this->crm) {
try {
$customer = $this->crm->getCustomer(Issue::getCustomerID($res[$issue_field]));
$res['customer'] = $customer->getName();
} catch (CRMException $e) {
}
}
$res['date'] = Date_Helper::getFormattedDate($res[$date_field], $timezone);
// need to decode From:, To: mail headers
if (isset($res['sup_from'])) {
$res['sup_from'] = Mime_Helper::fixEncoding($res['sup_from']);
}
if (isset($res['sup_to'])) {
$res['sup_to'] = Mime_Helper::fixEncoding($res['sup_to']);
}
}
}
/**
* Smarty helper formatting date value suitable for jquery-timeago
*
* @param string $date
* @return string
*/
public static function formatTimeAgo($date)
{
$formatted_date = Date_Helper::getFormattedDate($date);
$gmt_date = self::getDateTime($date, 'GMT')->format('Y-m-d\\TH:i:s\\Z');
return sprintf('<abbr class="timeago" title="%s">%s</abbr>', $gmt_date, $formatted_date);
}
/**
* Method used to get the details of a FAQ entry for a given FAQ ID.
*
* @param integer $faq_id The FAQ entry ID
* @return array The FAQ entry details
*/
public static function getDetails($faq_id)
{
$stmt = 'SELECT
*
FROM
{{%faq}}
WHERE
faq_id=?';
try {
$res = DB_Helper::getInstance()->getRow($stmt, array($faq_id));
} catch (DbException $e) {
return '';
}
if ($res == null) {
return '';
}
$res['support_levels'] = array_keys(self::getAssociatedSupportLevels($res['faq_prj_id'], $res['faq_id']));
if (empty($res['faq_updated_date'])) {
$res['faq_updated_date'] = $res['faq_created_date'];
}
$res['faq_updated_date'] = Date_Helper::getFormattedDate($res['faq_updated_date']);
$res['message'] = Misc::activateLinks(nl2br(htmlspecialchars($res['faq_message'])));
return $res;
}
/**
* Method used to get the list of changes made against a specific issue.
*
* @param integer $iss_id The issue ID
* @param string $order_by The order to sort the history
* @return array The list of changes
*/
public static function getListing($iss_id, $order_by = 'DESC')
{
$order_by = DB_Helper::orderBy($order_by);
$stmt = "SELECT\n *\n FROM\n {{%issue_history}},\n {{%history_type}}\n WHERE\n htt_id = his_htt_id AND\n his_is_hidden != 1 AND\n his_iss_id=? AND\n htt_role <= ?\n ORDER BY\n his_id {$order_by}";
$params = array($iss_id, Auth::getCurrentRole());
try {
$res = DB_Helper::getInstance()->getAll($stmt, $params);
} catch (DbException $e) {
return '';
}
foreach ($res as &$row) {
$row['his_created_date'] = Date_Helper::getFormattedDate($row['his_created_date']);
$row['his_summary'] = Misc::processTokens(ev_gettext($row['his_summary']), $row['his_context']);
}
return $res;
}
/**
* Method used to get the full listing of notes associated with
* a specific issue.
*
* @param integer $issue_id The issue ID
* @return array The list of notes
*/
public static function getListing($issue_id)
{
$stmt = 'SELECT
not_id,
not_created_date,
not_title,
not_usr_id,
not_unknown_user,
not_has_attachment,
not_is_blocked AS has_blocked_message,
usr_full_name
FROM
{{%note}},
{{%user}}
WHERE
not_usr_id=usr_id AND
not_iss_id=? AND
not_removed = 0
ORDER BY
not_created_date ASC';
try {
$res = DB_Helper::getInstance()->getAll($stmt, array($issue_id));
} catch (DbException $e) {
return '';
}
// only show the internal notes for users with the appropriate permission level
$role_id = Auth::getCurrentRole();
$user_role_id = User::getRoleID('standard user');
$t = array();
foreach ($res as &$row) {
if ($role_id < $user_role_id) {
continue;
}
// Display not_unknown_user instead of usr_full_name if not null.
// This is so the original sender of a blocked email is displayed on the note.
if (!empty($row['not_unknown_user'])) {
$row['usr_full_name'] = $row['not_unknown_user'];
}
$row['not_created_date'] = Date_Helper::getFormattedDate($row['not_created_date']);
$t[] = $row;
unset($row);
}
return $t;
}
}
$tpl->assign('issue', $details);
$tpl->assign('extra_title', ev_gettext('Update Issue #%1$s', $issue_id));
// in the case of a customer user, also need to check if that customer has access to this issue
if ($role_id == User::getRoleID('customer') && (empty($details) || User::getCustomerID($usr_id) != $details['iss_customer_id']) || !Issue::canAccess($issue_id, $usr_id) || !($role_id > User::getRoleID('Reporter')) || !Issue::canUpdate($issue_id, $usr_id)) {
$tpl->setTemplate('base_full.tpl.html');
Misc::setMessage(ev_gettext('Sorry, you do not have the required privileges to update this issue.'), Misc::MSG_ERROR);
$tpl->displayTemplate();
exit;
}
if (Issue_Lock::acquire($issue_id, $usr_id)) {
$issue_lock = false;
} else {
$issue_lock = Issue_Lock::getInfo($issue_id);
$issue_lock['locker'] = User::getDetails($issue_lock['usr_id']);
$issue_lock['expires_formatted_time'] = Date_Helper::getFormattedDate($issue_lock['expires']);
}
$tpl->assign('issue_lock', $issue_lock);
$new_prj_id = Issue::getProjectID($issue_id);
$cancel_update = isset($_POST['cancel']);
if ($cancel_update) {
// be sure not to unlock somebody else's lock
if (!$issue_lock) {
Issue_Lock::release($issue_id);
Misc::setMessage(ev_gettext('Cancelled Issue #%1$s update.', $issue_id), Misc::MSG_INFO);
}
Auth::redirect(APP_RELATIVE_URL . 'view.php?id=' . $issue_id);
exit;
} elseif (@$_POST['cat'] == 'update') {
if ($issue_lock) {
Misc::setMessage(ev_gettext("Sorry, you can't update issue if it's locked by another user"), Misc::MSG_ERROR);
/**
* Method used to get the full listing of phone support entries
* associated with a specific issue.
*
* @param integer $issue_id The issue ID
* @return array The list of notes
*/
public static function getListing($issue_id)
{
$stmt = 'SELECT
{{%phone_support}}.*,
usr_full_name,
phc_title,
iss_prj_id
FROM
{{%phone_support}},
{{%project_phone_category}},
{{%user}},
{{%issue}}
WHERE
phs_iss_id=iss_id AND
iss_prj_id=phc_prj_id AND
phs_phc_id=phc_id AND
phs_usr_id=usr_id AND
phs_iss_id=?
ORDER BY
phs_created_date ASC';
try {
$res = DB_Helper::getInstance()->getAll($stmt, array($issue_id));
} catch (DbException $e) {
return '';
}
foreach ($res as &$row) {
$row['phs_description'] = Misc::activateLinks(nl2br(htmlspecialchars($row['phs_description'])));
$row['phs_description'] = Link_Filter::processText($row['iss_prj_id'], $row['phs_description']);
$row['phs_created_date'] = Date_Helper::getFormattedDate($row['phs_created_date']);
}
return $res;
}
// | it under the terms of the GNU General Public License as published by |
// | the Free Software Foundation; either version 2 of the License, or |
// | (at your option) any later version. |
// | |
// | This program is distributed in the hope that it will be useful, |
// | but WITHOUT ANY WARRANTY; without even the implied warranty of |
// | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the |
// | GNU General Public License for more details. |
// | |
// | You should have received a copy of the GNU General Public License |
// | along with this program; if not, write to: |
// | |
// | Free Software Foundation, Inc. |
// | 51 Franklin Street, Suite 330 |
// | Boston, MA 02110-1301, USA. |
// +----------------------------------------------------------------------+
// | Authors: João Prado Maia <*****@*****.**> |
// +----------------------------------------------------------------------+
require_once dirname(__FILE__) . '/../init.php';
$tpl = new Template_Helper();
$tpl->setTemplate('news.tpl.html');
Auth::checkAuthentication(APP_COOKIE, 'index.php?err=5', true);
$prj_id = Auth::getCurrentProject();
if (!empty($_GET['id'])) {
$t = News::getDetails($_GET['id']);
$t['nws_created_date'] = Date_Helper::getFormattedDate($t['nws_created_date']);
$tpl->assign('news', array($t));
} else {
$tpl->assign('news', News::getListByProject($prj_id, true));
}
$tpl->displayTemplate();
/**
* Method used to list the history of triggered reminder actions
* for a given issue.
*
* @param integer $iss_id The issue ID
* @return array The list of triggered reminder actions
*/
public static function getHistoryList($iss_id)
{
$stmt = 'SELECT
rmh_created_date,
rma_title
FROM
{{%reminder_history}},
{{%reminder_action}}
WHERE
rmh_iss_id=? AND
rmh_rma_id=rma_id
ORDER BY
rmh_created_date DESC';
try {
$res = DB_Helper::getInstance()->getAll($stmt, array($iss_id));
} catch (DbException $e) {
return array();
}
foreach ($res as &$row) {
$row['rmh_created_date'] = Date_Helper::getFormattedDate($row['rmh_created_date']);
}
return $res;
}
/**
* Method used to get all of the support email entries associated
* with a given issue.
*
* @param integer $issue_id The issue ID
* @return array The list of support emails
*/
public static function getEmailsByIssue($issue_id)
{
$stmt = "SELECT\n sup_id,\n sup_ema_id,\n sup_from,\n sup_to,\n sup_cc,\n sup_date,\n UNIX_TIMESTAMP(sup_date) as date_ts,\n sup_subject,\n sup_has_attachment,\n CONCAT(sup_ema_id, '-', sup_id) AS composite_id\n FROM\n {{%support_email}}\n WHERE\n sup_iss_id=?\n ORDER BY\n sup_id ASC";
try {
$res = DB_Helper::getInstance()->getAll($stmt, array($issue_id));
} catch (DbException $e) {
return '';
}
if (count($res) == 0) {
return array();
}
foreach ($res as &$row) {
$row['sup_date'] = Date_Helper::getFormattedDate($row['sup_date']);
$row['sup_subject'] = Mime_Helper::fixEncoding($row['sup_subject']);
$row['sup_from'] = Mime_Helper::fixEncoding($row['sup_from']);
$row['sup_to'] = Mime_Helper::fixEncoding($row['sup_to']);
$row['sup_cc'] = Mime_Helper::fixEncoding($row['sup_cc']);
}
return $res;
}
/**
* Method used to return the full list of attachments related to a specific
* issue in the database.
*
* @param integer $issue_id The issue ID
* @return array The full list of attachments
*/
public static function getList($issue_id)
{
$usr_id = Auth::getUserID();
$prj_id = Issue::getProjectID($issue_id);
$stmt = 'SELECT
iat_id,
iat_usr_id,
usr_full_name,
iat_created_date,
iat_description,
iat_unknown_user,
iat_status
FROM
{{%issue_attachment}},
{{%user}}
WHERE
iat_iss_id=? AND
iat_usr_id=usr_id';
if (User::getRoleByUser($usr_id, $prj_id) <= User::getRoleID('Customer')) {
$stmt .= " AND iat_status='public' ";
}
$stmt .= '
ORDER BY
iat_created_date ASC';
$params = array($issue_id);
try {
$res = DB_Helper::getInstance()->getAll($stmt, $params);
} catch (DbException $e) {
return '';
}
foreach ($res as &$row) {
$row['iat_description'] = Link_Filter::processText($prj_id, nl2br(htmlspecialchars($row['iat_description'])));
$row['files'] = self::getFileList($row['iat_id']);
$row['iat_created_date'] = Date_Helper::getFormattedDate($row['iat_created_date']);
// if there is an unknown user, user that instead of the user_full_name
if (!empty($row['iat_unknown_user'])) {
$row['usr_full_name'] = $row['iat_unknown_user'];
}
}
return $res;
}
/**
* Method used to get the details for a specific issue.
*
* @param integer $issue_id The issue ID
* @param boolean $force_refresh If the cache should not be used.
* @return array The details for the specified issue
*/
public static function getDetails($issue_id, $force_refresh = false)
{
static $returns;
if (empty($issue_id)) {
return '';
}
if (!empty($returns[$issue_id]) && $force_refresh != true) {
return $returns[$issue_id];
}
$stmt = 'SELECT
{{%issue}}.*,
prj_title,
prc_title,
pre_title,
pri_title,
sev_title,
sta_title,
sta_abbreviation,
sta_color status_color,
sta_is_closed
FROM
(
{{%issue}},
{{%project}}
)
LEFT JOIN
{{%project_priority}}
ON
iss_pri_id=pri_id
LEFT JOIN
{{%project_severity}}
ON
iss_sev_id=sev_id
LEFT JOIN
{{%status}}
ON
iss_sta_id=sta_id
LEFT JOIN
{{%project_category}}
ON
iss_prc_id=prc_id
LEFT JOIN
{{%project_release}}
ON
iss_pre_id=pre_id
WHERE
iss_id=? AND
iss_prj_id=prj_id';
try {
$res = DB_Helper::getInstance()->getRow($stmt, array($issue_id));
} catch (DbException $e) {
return '';
}
if (empty($res)) {
return '';
}
$created_date_ts = Date_Helper::getUnixTimestamp($res['iss_created_date'], Date_Helper::getDefaultTimezone());
// get customer information, if any
if (!empty($res['iss_customer_id']) && CRM::hasCustomerIntegration($res['iss_prj_id'])) {
$crm = CRM::getInstance($res['iss_prj_id']);
try {
$customer = $crm->getCustomer($res['iss_customer_id']);
$contract = $crm->getContract($res['iss_customer_contract_id']);
$res['contact_local_time'] = Date_Helper::getFormattedDate(Date_Helper::getCurrentDateGMT(), $res['iss_contact_timezone']);
$res['customer'] = $customer;
$res['contract'] = $contract;
$res['contact'] = $crm->getContact($res['iss_customer_contact_id']);
// TODOCRM: Deal with incidents
// $res['redeemed_incidents'] = Customer::getRedeemedIncidentDetails($res['iss_prj_id'], $res['iss_id']);
$max_first_response_time = $contract->getMaximumFirstResponseTime($issue_id);
$res['max_first_response_time'] = Misc::getFormattedTime($max_first_response_time / 60);
if (empty($res['iss_first_response_date'])) {
$first_response_deadline = $created_date_ts + $max_first_response_time;
if (time() <= $first_response_deadline) {
$res['max_first_response_time_left'] = Date_Helper::getFormattedDateDiff($first_response_deadline, time());
} else {
$res['overdue_first_response_time'] = Date_Helper::getFormattedDateDiff(time(), $first_response_deadline);
}
}
} catch (CRMException $e) {
// TODOCRM: Log exception?
}
}
$res['iss_original_description'] = $res['iss_description'];
$res['iss_original_percent_complete'] = $res['iss_percent_complete'];
$res['iss_description'] = nl2br(htmlspecialchars($res['iss_description']));
$res['iss_resolution'] = Resolution::getTitle($res['iss_res_id']);
$res['iss_impact_analysis'] = nl2br(htmlspecialchars($res['iss_impact_analysis']));
$res['iss_created_date_ts'] = $created_date_ts;
$res['assignments'] = @implode(', ', array_values(self::getAssignedUsers($res['iss_id'])));
list($res['authorized_names'], $res['authorized_repliers']) = Authorized_Replier::getAuthorizedRepliers($res['iss_id']);
$temp = self::getAssignedUsersStatus($res['iss_id']);
$res['has_inactive_users'] = 0;
$res['assigned_users'] = array();
$res['assigned_inactive_users'] = array();
foreach ($temp as $usr_id => $usr_status) {
if (!User::isActiveStatus($usr_status)) {
$res['assigned_inactive_users'][] = $usr_id;
$res['has_inactive_users'] = 1;
} else {
$res['assigned_users'][] = $usr_id;
}
}
if (@in_array(Auth::getUserID(), $res['assigned_users'])) {
$res['is_current_user_assigned'] = 1;
} else {
$res['is_current_user_assigned'] = 0;
}
$res['associated_issues_details'] = self::getAssociatedIssuesDetails($res['iss_id']);
$res['associated_issues'] = self::getAssociatedIssues($res['iss_id']);
$res['reporter'] = User::getFullName($res['iss_usr_id']);
if (empty($res['iss_updated_date'])) {
$res['iss_updated_date'] = $res['iss_created_date'];
}
$res['estimated_formatted_time'] = Misc::getFormattedTime($res['iss_dev_time']);
if (Release::isAssignable($res['iss_pre_id'])) {
$release = Release::getDetails($res['iss_pre_id']);
$res['pre_title'] = $release['pre_title'];
$res['pre_status'] = $release['pre_status'];
}
// need to return the list of issues that are duplicates of this one
$res['duplicates'] = self::getDuplicateList($res['iss_id']);
$res['duplicates_details'] = self::getDuplicateDetailsList($res['iss_id']);
// also get the issue title of the duplicated issue
if (!empty($res['iss_duplicated_iss_id'])) {
$res['duplicated_issue'] = self::getDuplicatedDetails($res['iss_duplicated_iss_id']);
}
// get group information
if (!empty($res['iss_grp_id'])) {
$res['group'] = Group::getDetails($res['iss_grp_id']);
}
// get quarantine issue
$res['quarantine'] = self::getQuarantineInfo($res['iss_id']);
$res['products'] = Product::getProductsByIssue($res['iss_id']);
$returns[$issue_id] = $res;
return $res;
}
/**
* Returns the mail queue for a specific issue.
*
* @param integer $issue_id The issue ID
* @return array An array of emails from the queue
*/
public static function getListByIssueID($issue_id)
{
$stmt = 'SELECT
maq_id,
maq_queued_date,
maq_status,
maq_recipient,
maq_subject
FROM
{{%mail_queue}}
WHERE
maq_iss_id = ?
ORDER BY
maq_queued_date ASC';
try {
$res = DB_Helper::getInstance()->getAll($stmt, array($issue_id));
} catch (DbException $e) {
return false;
}
if (count($res) > 0) {
foreach ($res as &$row) {
$row['maq_recipient'] = Mime_Helper::decodeAddress($row['maq_recipient']);
$row['maq_queued_date'] = Date_Helper::getFormattedDate(Date_Helper::getUnixTimestamp($row['maq_queued_date'], 'GMT'));
$row['maq_subject'] = Mime_Helper::fixEncoding($row['maq_subject']);
}
}
return $res;
}
/**
* Method used to get the list of issues in a project, and group
* them by the assignee.
*
* @param integer $prj_id The project ID
* @return array The list of issues
*/
public static function getIssuesByUser($prj_id)
{
$stmt = 'SELECT
usr_full_name,
iss_id,
iss_summary,
sta_title,
iss_sta_id,
iss_created_date,
sta_color
FROM
(
{{%issue}},
{{%issue_user}},
{{%user}}
)
LEFT JOIN
{{%status}}
ON
iss_sta_id=sta_id
WHERE
iss_prj_id=? AND
iss_id=isu_iss_id AND
isu_usr_id=usr_id
ORDER BY
usr_full_name';
try {
$res = DB_Helper::getInstance()->getAll($stmt, array($prj_id));
} catch (DbException $e) {
return '';
}
Time_Tracking::fillTimeSpentByIssues($res);
$issues = array();
foreach ($res as $row) {
$issues[$row['usr_full_name']][$row['iss_id']] = array('iss_summary' => $row['iss_summary'], 'sta_title' => $row['sta_title'], 'iss_created_date' => Date_Helper::getFormattedDate($row['iss_created_date']), 'time_spent' => Misc::getFormattedTime($row['time_spent']), 'status_color' => $row['sta_color']);
}
return $issues;
}
/**
* Method used to get the full list of checkins associated with an issue.
*
* @param integer $issue_id The issue ID
* @return array The list of checkins
*/
public static function getCheckinList($issue_id)
{
$stmt = 'SELECT
*
FROM
{{%issue_checkin}}
WHERE
isc_iss_id=?
ORDER BY
isc_created_date ASC';
try {
$res = DB_Helper::getInstance()->getAll($stmt, array($issue_id));
} catch (DbException $e) {
return array();
}
if (empty($res)) {
return array();
}
foreach ($res as $i => &$checkin) {
$scm = self::getScmCheckinByName($checkin['isc_reponame']);
// add ADDED and REMOVED fields
$checkin['added'] = !isset($checkin['isc_old_version']);
$checkin['removed'] = !isset($checkin['isc_new_version']);
$checkin['isc_commit_msg'] = Link_Filter::processText(Issue::getProjectID($issue_id), nl2br(htmlspecialchars($checkin['isc_commit_msg'])));
$checkin['checkout_url'] = $scm->getCheckoutUrl($checkin);
$checkin['diff_url'] = $scm->getDiffUrl($checkin);
$checkin['scm_log_url'] = $scm->getLogUrl($checkin);
$checkin['isc_created_date'] = Date_Helper::getFormattedDate($checkin['isc_created_date']);
}
return $res;
}
