/**
* Retrieves the details about a given note.
*
* @param integer $note_id The note ID
* @return array The note details
*/
public static function getDetails($note_id)
{
$stmt = 'SELECT
{{%note}}.*,
not_full_message,
usr_full_name
FROM
{{%note}},
{{%user}}
WHERE
not_usr_id=usr_id AND
not_id=?';
try {
$res = DB_Helper::getInstance()->getRow($stmt, array($note_id));
} catch (DbException $e) {
return '';
}
if (count($res) > 0) {
$res['timestamp'] = Date_Helper::getUnixTimestamp($res['not_created_date'], 'GMT');
if ($res['not_is_blocked'] == 1) {
$res['has_blocked_message'] = true;
} else {
$res['has_blocked_message'] = false;
}
if (!empty($res['not_unknown_user'])) {
$res['not_from'] = $res['not_unknown_user'];
} else {
$res['not_from'] = User::getFullName($res['not_usr_id']);
}
if ($res['not_has_attachment']) {
$res['attachments'] = Mime_Helper::getAttachmentCIDs($res['not_full_message']);
}
return $res;
}
return '';
}
/**
* Method used to get all open issues and group them by assignee or reporter.
*
* @param integer $prj_id The project ID
* @param integer $cutoff_days The number of days to use as a cutoff period
* @param bool $group_by_reporter
* @return array The list of issues
*/
public static function getOpenIssuesByUser($prj_id, $cutoff_days, $group_by_reporter = false)
{
$prj_id = (int) $prj_id;
$cutoff_days = (int) $cutoff_days;
$ts = time();
$ts_diff = $cutoff_days * Date_Helper::DAY;
$stmt = "SELECT\n assignee.usr_full_name as assignee_name,\n reporter.usr_full_name as reporter_name,\n iss_id,\n iss_summary,\n sta_title,\n iss_sta_id,\n iss_created_date,\n iss_updated_date,\n iss_last_response_date,\n sta_color\n FROM\n (\n {{%issue}},\n {{%issue_user}},\n {{%user}} as assignee,\n {{%user}} as reporter\n )\n LEFT JOIN\n {{%status}}\n ON\n iss_sta_id=sta_id\n WHERE\n sta_is_closed=0 AND\n iss_prj_id=? AND\n iss_id=isu_iss_id AND\n isu_usr_id=assignee.usr_id AND\n iss_usr_id=reporter.usr_id AND\n UNIX_TIMESTAMP(iss_created_date) < (UNIX_TIMESTAMP() - ?)\n ORDER BY\n";
if ($group_by_reporter) {
$stmt .= 'reporter.usr_full_name';
} else {
$stmt .= 'assignee.usr_full_name';
}
try {
$res = DB_Helper::getInstance()->getAll($stmt, array($prj_id, $ts_diff));
} catch (DbException $e) {
return '';
}
Time_Tracking::fillTimeSpentByIssues($res);
$issues = array();
foreach ($res as &$row) {
if (empty($row['iss_updated_date'])) {
$row['iss_updated_date'] = $row['iss_created_date'];
}
if (empty($row['iss_last_response_date'])) {
$row['iss_last_response_date'] = $row['iss_created_date'];
}
if ($group_by_reporter) {
$name = $row['reporter_name'];
} else {
$name = $row['assignee_name'];
}
$update_date_ts = Date_Helper::getUnixTimestamp($row['iss_updated_date'], Date_Helper::getDefaultTimezone());
$last_response_ts = Date_Helper::getUnixTimestamp($row['iss_last_response_date'], Date_Helper::getDefaultTimezone());
$issues[$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'], 'last_update' => Date_Helper::getFormattedDateDiff($ts, $update_date_ts), 'last_email_response' => Date_Helper::getFormattedDateDiff($ts, $last_response_ts));
}
return $issues;
}
/**
* Method used to get the support email entry details.
*
* @param integer $ema_id The support email account ID
* @param integer $sup_id The support email ID
* @return array The email entry details
*/
public static function getEmailDetails($ema_id, $sup_id)
{
// $ema_id is not needed anymore and will be re-factored away in the future
$stmt = 'SELECT
{{%support_email}}.*,
{{%support_email_body}}.*
FROM
{{%support_email}},
{{%support_email_body}}
WHERE
sup_id=seb_sup_id AND
sup_id=?';
try {
$res = DB_Helper::getInstance()->getRow($stmt, array($sup_id));
} catch (DbException $e) {
return '';
}
$res['message'] = $res['seb_body'];
$res['attachments'] = Mime_Helper::getAttachmentCIDs($res['seb_full_email']);
$res['timestamp'] = Date_Helper::getUnixTimestamp($res['sup_date'], 'GMT');
$res['sup_date'] = Date_Helper::getFormattedDate($res['sup_date']);
$res['sup_subject'] = Mime_Helper::fixEncoding($res['sup_subject']);
// TRANSLATORS: %1 = email subject
$res['reply_subject'] = Mail_Helper::removeExcessRe(ev_gettext('Re: %1$s', $res['sup_subject']), true);
$res['sup_from'] = Mime_Helper::fixEncoding($res['sup_from']);
$res['sup_to'] = Mime_Helper::fixEncoding($res['sup_to']);
if (!empty($res['sup_iss_id'])) {
$res['reply_subject'] = Mail_Helper::formatSubject($res['sup_iss_id'], $res['reply_subject']);
}
return $res;
}
/**
* Returns the status of a quarantine.
*
* @param integer $issue_id The issue ID
* @return integer Indicates what the current state of quarantine is.
*/
public static function getQuarantineInfo($issue_id)
{
$stmt = 'SELECT
iqu_status,
iqu_expiration
FROM
{{%issue_quarantine}}
WHERE
iqu_iss_id = ? AND
(iqu_expiration > ? OR
iqu_expiration IS NULL)';
try {
$res = DB_Helper::getInstance()->getRow($stmt, array($issue_id, Date_Helper::getCurrentDateGMT()));
} catch (DbException $e) {
return array();
}
if (!empty($res['iqu_expiration'])) {
$expiration_ts = Date_Helper::getUnixTimestamp($res['iqu_expiration'], Date_Helper::getDefaultTimezone());
$res['time_till_expiration'] = Date_Helper::getFormattedDateDiff($expiration_ts, time());
}
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;
}
