/**
* Method used to get the support email entry details.
*
* @access public
* @param integer $ema_id The support email account ID
* @param integer $sup_id The support email ID
* @return array The email entry details
*/
function getEmailDetails($ema_id, $sup_id)
{
$stmt = "SELECT\n " . APP_TABLE_PREFIX . "support_email.*,\n " . APP_TABLE_PREFIX . "support_email_body.*\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "support_email,\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "support_email_body\n WHERE\n sup_id=seb_sup_id AND\n sup_id=" . Misc::escapeInteger($sup_id) . " AND\n sup_ema_id=" . Misc::escapeInteger($ema_id);
$res = $GLOBALS["db_api"]->dbh->getRow($stmt, DB_FETCHMODE_ASSOC);
if (PEAR::isError($res)) {
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
return "";
} else {
// gotta parse MIME based emails now
$output = Mime_Helper::decode($res["seb_full_email"], true);
$res["message"] = Mime_Helper::getMessageBody($output);
// XXX: check which code relies on this var
$res["attachments"] = Mime_Helper::getAttachmentCIDs($res["seb_full_email"]);
$res["timestamp"] = Date_API::getUnixTimestamp($res['sup_date'], 'GMT');
$res["sup_date"] = Date_API::getFormattedDate($res["sup_date"]);
$res["sup_subject"] = Mime_Helper::fixEncoding($res["sup_subject"]);
// remove extra 'Re: ' from subject
$res['reply_subject'] = Mail_API::removeExcessRe('Re: ' . $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_API::formatSubject($res['sup_iss_id'], $res['reply_subject']);
}
return $res;
}
}
/**
* Method used to get all open issues and group them by user.
*
* @access public
* @param integer $prj_id The project ID
* @param integer $cutoff_days The number of days to use as a cutoff period
* @return array The list of issues
*/
function getOpenIssuesByUser($prj_id, $cutoff_days)
{
$prj_id = Misc::escapeInteger($prj_id);
$cutoff_days = Misc::escapeInteger($cutoff_days);
$ts = Date_API::getCurrentUnixTimestampGMT();
$cutoff_ts = $ts - $cutoff_days * DAY;
$stmt = "SELECT\n usr_full_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 " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue,\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue_user,\n " . ETEL_USER_TABLE . "\n )\n LEFT JOIN\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "status\n ON\n iss_sta_id=sta_id\n WHERE\n sta_is_closed=0 AND\n iss_prj_id={$prj_id} AND\n iss_id=isu_iss_id AND\n isu_usr_id=usr_id AND\n UNIX_TIMESTAMP(iss_created_date) < {$cutoff_ts}\n ORDER BY\n usr_full_name";
$res = $GLOBALS["db_api"]->dbh->getAll($stmt, DB_FETCHMODE_ASSOC);
if (PEAR::isError($res)) {
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
return "";
} else {
Time_Tracking::getTimeSpentByIssues($res);
$issues = array();
for ($i = 0; $i < count($res); $i++) {
if (empty($res[$i]['iss_updated_date'])) {
$res[$i]['iss_updated_date'] = $res[$i]['iss_created_date'];
}
if (empty($res[$i]['iss_last_response_date'])) {
$res[$i]['iss_last_response_date'] = $res[$i]['iss_created_date'];
}
$issues[$res[$i]['usr_full_name']][$res[$i]['iss_id']] = array('iss_summary' => $res[$i]['iss_summary'], 'sta_title' => $res[$i]['sta_title'], 'iss_created_date' => Date_API::getFormattedDate($res[$i]['iss_created_date']), 'time_spent' => Misc::getFormattedTime($res[$i]['time_spent']), 'status_color' => $res[$i]['sta_color'], 'last_update' => Date_API::getFormattedDateDiff($ts, Date_API::getUnixTimestamp($res[$i]['iss_updated_date'], Date_API::getDefaultTimezone())), 'last_email_response' => Date_API::getFormattedDateDiff($ts, Date_API::getUnixTimestamp($res[$i]['iss_last_response_date'], Date_API::getDefaultTimezone())));
}
return $issues;
}
}
/**
* Returns the mail queue for a specific issue.
*
* @access public
* @param integer $issue_is The issue ID
* @return array An array of emails from the queue
*/
function getListByIssueID($issue_id)
{
$issue_id = Misc::escapeInteger($issue_id);
$stmt = "SELECT\n maq_id,\n maq_queued_date,\n maq_status,\n maq_recipient,\n maq_subject\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "mail_queue\n WHERE\n maq_iss_id = " . Misc::escapeInteger($issue_id) . "\n ORDER BY\n maq_queued_date ASC";
$res = $GLOBALS["db_api"]->dbh->getAll($stmt, DB_FETCHMODE_ASSOC);
if (PEAR::isError($res)) {
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
return false;
}
if (count($res) > 0) {
for ($i = 0; $i < count($res); $i++) {
$res[$i]['maq_recipient'] = Mime_Helper::decodeAddress($res[$i]['maq_recipient']);
$res[$i]['maq_queued_date'] = Date_API::getFormattedDate(Date_API::getUnixTimestamp($res[$i]['maq_queued_date'], 'GMT'));
$res[$i]['maq_subject'] = Mime_Helper::fixEncoding($res[$i]['maq_subject']);
}
}
return $res;
}
/**
* Retrieves the details about a given note.
*
* @access public
* @param integer $note_id The note ID
* @return array The note details
*/
function getDetails($note_id)
{
$note_id = Misc::escapeInteger($note_id);
$stmt = "SELECT\n " . APP_TABLE_PREFIX . "note.*,\n not_created_date,\n not_blocked_message,\n usr_full_name\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "note,\n " . ETEL_USER_TABLE . "\n WHERE\n not_usr_id=usr_id AND\n not_id='{$note_id}'";
$res = $GLOBALS["db_api"]->dbh->getRow($stmt, DB_FETCHMODE_ASSOC);
if (PEAR::isError($res)) {
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
return '';
} else {
if (count($res) > 0) {
$res['timestamp'] = Date_API::getUnixTimestamp($res['not_created_date'], 'GMT');
$res['not_created_date'] = Date_API::getFormattedDate($res['not_created_date']);
if (!empty($res['not_blocked_message'])) {
$res['has_blocked_message'] = true;
$res["attachments"] = Mime_Helper::getAttachmentCIDs($res['not_blocked_message']);
} 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']);
}
return $res;
} else {
return '';
}
}
}
/**
* Returns the status of a quarantine.
*
* @param integer $issue_id The issue ID
* @return integer Indicates what the current state of quarantine is.
*/
function getQuarantineInfo($issue_id)
{
$stmt = "SELECT\n iqu_status,\n iqu_expiration\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue_quarantine\n WHERE\n iqu_iss_id = " . Misc::escapeInteger($issue_id) . " AND\n (iqu_expiration > '" . Date_API::getCurrentDateGMT() . "' OR\n iqu_expiration IS NULL)";
$res = $GLOBALS["db_api"]->dbh->getRow($stmt, DB_FETCHMODE_ASSOC);
if (PEAR::isError($res)) {
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
return array();
} else {
if (!empty($res["iqu_expiration"])) {
$expiration_ts = Date_API::getUnixTimestamp($res['iqu_expiration'], Date_API::getDefaultTimezone());
$res["time_till_expiration"] = Date_API::getFormattedDateDiff($expiration_ts, Date_API::getCurrentUnixTimestampGMT());
}
return $res;
}
}