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