/**
* Returns the total number of rows for a specific query. It is used to
* calculate the total number of pages of data.
*
* @access public
* @param string $stmt The SQL statement
* @return int The total number of rows
*/
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 /sei", "'SELECT COUNT(DISTINCT " . $matches[1] . ") AS total_rows FROM '", $stmt);
}
} else {
$stmt = preg_replace("/SELECT (.*?) FROM /sei", "'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+]*(.*)/sei", "'\\1\\3'", $stmt);
$rows = $GLOBALS["db_api"]->dbh->getAll($stmt, DB_FETCHMODE_ASSOC);
if (PEAR::isError($rows)) {
Error_Handler::logError(array($rows->getMessage(), $rows->getDebugInfo()), __FILE__, __LINE__);
return 0;
} elseif (empty($rows)) {
return 0;
} else {
// 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"];
} else {
return count($rows);
}
}
}
/**
* Logs the specified error
*
* @access public
* @param mixed $error_msg The error message
* @param string $script The script name where the error happened
* @param integer $line The line number where the error happened
*/
function logError($error_msg = "", $script = "", $line = "")
{
if (REPORT_ERROR_FILE) {
Error_Handler::logToFile($error_msg, $script, $line);
}
$setup = Setup::load();
if (@$setup['email_error']['status'] == 'enabled') {
// if there's no db_api object, then we cannot
// possibly queue up the error emails
if (!is_null(@$GLOBALS["db_api"])) {
Error_Handler::_notify($error_msg, $script, $line);
}
}
}
/**
* Connects to the database and creates a data dictionary array to be used
* on database related schema dynamic lookups.
*
* @access public
*/
function DB_API()
{
$dsn = array('phptype' => APP_SQL_DBTYPE, 'hostspec' => APP_SQL_DBHOST, 'database' => APP_SQL_DBNAME, 'username' => APP_SQL_DBUSER, 'password' => APP_SQL_DBPASS);
// if we are using some non-standard mysql port, pass that value in the dsn
if (defined('APP_SQL_DBPORT') && APP_SQL_DBPORT != 3306) {
$dsn['port'] = APP_SQL_DBPORT;
}
$this->dbh = DB::connect($dsn);
if (PEAR::isError($this->dbh)) {
Error_Handler::logError(array($this->dbh->getMessage(), $this->dbh->getDebugInfo()), __FILE__, __LINE__);
$error_type = "db";
include_once APP_PATH . "offline.php";
exit;
}
}
/**
* Returns the name of the workflow backend for the specified project.
*
* @access public
* @param integer $prj_id The id of the project to lookup.
* @return string The name of the customer backend.
*/
function _getBackendNameByProject($prj_id)
{
static $backends;
if (isset($backends[$prj_id])) {
return $backends[$prj_id];
}
$stmt = "SELECT\n prj_id,\n prj_workflow_backend\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "project\n ORDER BY\n prj_id";
$res = $GLOBALS["db_api"]->dbh->getAssoc($stmt);
if (PEAR::isError($res)) {
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
return '';
} else {
$backends = $res;
return @$backends[$prj_id];
}
}
/**
* Method used to get a list as an associative array of the
* releases.
*
* @access public
* @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
*/
function getAssocList($prj_id, $show_all_dates = false)
{
$stmt = "SELECT\n pre_id,\n pre_title\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "project_release\n WHERE\n pre_prj_id=" . Misc::escapeInteger($prj_id) . " AND\n (\n pre_status='available'";
if ($show_all_dates != true) {
$stmt .= " AND\n pre_scheduled_date >= '" . gmdate('Y-m-d') . "'";
}
$stmt .= "\n )\n ORDER BY\n pre_scheduled_date ASC";
$res = $GLOBALS["db_api"]->dbh->getAssoc($stmt);
if (PEAR::isError($res)) {
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
return "";
} else {
return $res;
}
}
/**
* Method used to get the list of priorities as an associative array in the
* style of (id => title)
*
* @access public
* @param integer $prj_id The project ID
* @return array The list of priorities
*/
function getAssocList($prj_id)
{
static $list;
if (count(@$list[$prj_id]) > 0) {
return $list[$prj_id];
}
$stmt = "SELECT\n pri_id,\n pri_title\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "project_priority\n WHERE\n pri_prj_id=" . Misc::escapeInteger($prj_id) . "\n ORDER BY\n pri_rank ASC";
$res = $GLOBALS["db_api"]->dbh->getAssoc($stmt);
if (PEAR::isError($res)) {
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
return "";
} else {
$list[$prj_id] = $res;
return $res;
}
}
/**
* Method used to get an associative array of all of the canned email
* responses' bodies.
*
* @access public
* @param integer $prj_id The project ID
* @return array The list of canned email responses' bodies.
*/
function getAssocListBodies($prj_id)
{
$stmt = "SELECT\n ere_id,\n ere_response_body\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "email_response,\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "project_email_response\n WHERE\n per_ere_id=ere_id AND\n per_prj_id=" . Misc::escapeInteger($prj_id);
$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 {
// fix the newlines in the response bodies so javascript doesn't die
for ($i = 0; $i < count($res); $i++) {
$res[$i]['ere_response_body'] = Misc::escapeWhitespace($res[$i]['ere_response_body']);
$res[$i]['ere_response_body'] = str_replace('"', '\\"', $res[$i]['ere_response_body']);
}
return $res;
}
}
/**
* Returns display settings for a specific project.
*
* @access public
* @param integer $prj_id The project ID
* @return array An associative array of minimum role required to access a field.
*/
function getFieldDisplaySettings($prj_id)
{
$stmt = "SELECT\n pfd_field,\n pfd_min_role\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "project_field_display\n WHERE\n pfd_prj_id = " . Misc::escapeInteger($prj_id);
$res = $GLOBALS["db_api"]->dbh->getAssoc($stmt);
if (PEAR::isError($res)) {
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
return -1;
}
$fields = Project::getDisplayFields();
foreach ($fields as $field_name => $field_title) {
if (!isset($res[$field_name])) {
$res[$field_name] = 0;
}
}
return $res;
}
/**
* Searches a specified custom field for a string and returns any issues that match
*
* @access public
* @param integer $fld_id The ID of the custom field
* @param string $search The string to search for
* @return array An array of issue IDs
*/
function getIssuesByString($fld_id, $search)
{
$sql = "SELECT\n icf_iss_id\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue_custom_field\n WHERE\n icf_fld_id = " . Misc::escapeInteger($fld_id) . " AND\n icf_value LIKE '%" . Misc::escapeString($search) . "%'";
$res = $GLOBALS["db_api"]->dbh->getCol($sql);
if (PEAR::isError($res)) {
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
return array();
}
return $res;
}
/**
* Method used to get the information about a specific message
* from a given mailbox.
*
* XXX this function does more than that.
*
* @param resource $mbox The mailbox
* @param array $info The support email account information
* @param integer $num The index of the message
* @return void
*/
public static function getEmailInfo($mbox, $info, $num)
{
Auth::createFakeCookie(APP_SYSTEM_USER_ID);
// check if the current message was already seen
if ($info['ema_get_only_new']) {
list($overview) = @imap_fetch_overview($mbox, $num);
if ($overview->seen || $overview->deleted || $overview->answered) {
return;
}
}
$email = @imap_headerinfo($mbox, $num);
$headers = imap_fetchheader($mbox, $num);
$body = imap_body($mbox, $num);
// check for mysterious blank messages
if (empty($body) and empty($headers)) {
// XXX do some error reporting?
return;
}
$message_id = Mail_Helper::getMessageID($headers, $body);
$message = $headers . $body;
// we don't need $body anymore -- free memory
unset($body);
// if message_id already exists, return immediately -- nothing to do
if (self::exists($message_id) || Note::exists($message_id)) {
return;
}
$structure = Mime_Helper::decode($message, true, true);
$message_body = $structure->body;
if (Mime_Helper::hasAttachments($structure)) {
$has_attachments = 1;
} else {
$has_attachments = 0;
}
// pass in $email by reference so it can be modified
$workflow = Workflow::preEmailDownload($info['ema_prj_id'], $info, $mbox, $num, $message, $email, $structure);
if ($workflow === -1) {
return;
}
// route emails if necessary
if ($info['ema_use_routing'] == 1) {
$setup = Setup::load();
// we create addresses array so it can be reused
$addresses = array();
if (isset($email->to)) {
foreach ($email->to as $address) {
$addresses[] = $address->mailbox . '@' . $address->host;
}
}
if (isset($email->cc)) {
foreach ($email->cc as $address) {
$addresses[] = $address->mailbox . '@' . $address->host;
}
}
if (@$setup['email_routing']['status'] == 'enabled') {
$res = Routing::getMatchingIssueIDs($addresses, 'email');
if ($res != false) {
$return = Routing::route_emails($message);
if ($return === true) {
self::deleteMessage($info, $mbox, $num);
return;
}
// TODO: handle errors?
return;
}
}
if (@$setup['note_routing']['status'] == 'enabled') {
$res = Routing::getMatchingIssueIDs($addresses, 'note');
if ($res != false) {
$return = Routing::route_notes($message);
// if leave copy of emails on IMAP server is off we can
// bounce on note that user had no permission to write
// here.
// otherwise proper would be to create table -
// eventum_bounce: bon_id, bon_message_id, bon_error
if ($info['ema_leave_copy']) {
if ($return === true) {
self::deleteMessage($info, $mbox, $num);
}
} else {
if ($return !== true) {
// in case of error, create bounce, but still
// delete email not to send bounce in next process :)
self::bounceMessage($email, $return);
}
self::deleteMessage($info, $mbox, $num);
}
return;
}
}
if (@$setup['draft_routing']['status'] == 'enabled') {
$res = Routing::getMatchingIssueIDs($addresses, 'draft');
if ($res != false) {
$return = Routing::route_drafts($message);
// if leave copy of emails on IMAP server is off we can
// bounce on note that user had no permission to write
// here.
// otherwise proper would be to create table -
// eventum_bounce: bon_id, bon_message_id, bon_error
if ($info['ema_leave_copy']) {
if ($return === true) {
self::deleteMessage($info, $mbox, $num);
}
} else {
if ($return !== true) {
// in case of error, create bounce, but still
// delete email not to send bounce in next process :)
self::bounceMessage($email, $return);
}
self::deleteMessage($info, $mbox, $num);
}
return;
}
}
// TODO:
// disabling return here allows routing and issue auto creating from same account
// but it will download email store it in database and do nothing
// with it if it does not match support@ address.
//return;
}
$sender_email = Mail_Helper::getEmailAddress($email->fromaddress);
if (Misc::isError($sender_email)) {
$sender_email = 'Error Parsing Email <>';
}
$t = array('ema_id' => $info['ema_id'], 'message_id' => $message_id, 'date' => Date_Helper::convertDateGMTByTS($email->udate), 'from' => $sender_email, 'to' => @$email->toaddress, 'cc' => @$email->ccaddress, 'subject' => @$structure->headers['subject'], 'body' => @$message_body, 'full_email' => @$message, 'has_attachment' => $has_attachments, 'headers' => @$structure->headers);
$subject = Mime_Helper::decodeQuotedPrintable(@$structure->headers['subject']);
$should_create_array = self::createIssueFromEmail($info, $headers, $message_body, $t['date'], $sender_email, $subject, $t['to'], $t['cc']);
$should_create_issue = $should_create_array['should_create_issue'];
if (!empty($should_create_array['issue_id'])) {
$t['issue_id'] = $should_create_array['issue_id'];
// figure out if we should change to a different email account
$iss_prj_id = Issue::getProjectID($t['issue_id']);
if ($info['ema_prj_id'] != $iss_prj_id) {
$new_ema_id = Email_Account::getEmailAccount($iss_prj_id);
if (!empty($new_ema_id)) {
$t['ema_id'] = $new_ema_id;
}
}
}
if (!empty($should_create_array['customer_id'])) {
$t['customer_id'] = $should_create_array['customer_id'];
}
if (empty($t['issue_id'])) {
$t['issue_id'] = 0;
} else {
$prj_id = Issue::getProjectID($t['issue_id']);
Auth::createFakeCookie(APP_SYSTEM_USER_ID, $prj_id);
}
if ($should_create_array['type'] == 'note') {
// assume that this is not a valid note
$res = -1;
if ($t['issue_id'] != 0) {
// check if this is valid user
$usr_id = User::getUserIDByEmail($sender_email);
if (!empty($usr_id)) {
$role_id = User::getRoleByUser($usr_id, $prj_id);
if ($role_id > User::getRoleID('Customer')) {
// actually a valid user so insert the note
Auth::createFakeCookie($usr_id, $prj_id);
$users = Project::getUserEmailAssocList($prj_id, 'active', User::getRoleID('Customer'));
$user_emails = array_map(function ($s) {
return strtolower($s);
}, array_values($users));
$users = array_flip($users);
$addresses = array();
$to_addresses = Mail_Helper::getEmailAddresses(@$structure->headers['to']);
if (count($to_addresses)) {
$addresses = $to_addresses;
}
$cc_addresses = Mail_Helper::getEmailAddresses(@$structure->headers['cc']);
if (count($cc_addresses)) {
$addresses = array_merge($addresses, $cc_addresses);
}
$cc_users = array();
foreach ($addresses as $email) {
if (in_array(strtolower($email), $user_emails)) {
$cc_users[] = $users[strtolower($email)];
}
}
// XXX FIXME, this is not nice thing to do
$_POST = array('title' => Mail_Helper::removeExcessRe($t['subject']), 'note' => $t['body'], 'note_cc' => $cc_users, 'add_extra_recipients' => 'yes', 'message_id' => $t['message_id'], 'parent_id' => $should_create_array['parent_id']);
$res = Note::insertFromPost($usr_id, $t['issue_id']);
// need to handle attachments coming from notes as well
if ($res != -1) {
Support::extractAttachments($t['issue_id'], $structure, true, $res);
}
}
}
}
} else {
// check if we need to block this email
if ($should_create_issue == true || !self::blockEmailIfNeeded($t)) {
if (!empty($t['issue_id'])) {
list($t['full_email'], $t['headers']) = Mail_Helper::rewriteThreadingHeaders($t['issue_id'], $t['full_email'], $t['headers'], 'email');
}
// make variable available for workflow to be able to detect whether this email created new issue
$t['should_create_issue'] = $should_create_array['should_create_issue'];
$res = self::insertEmail($t, $structure, $sup_id);
if ($res != -1) {
// only extract the attachments from the email if we are associating the email to an issue
if (!empty($t['issue_id'])) {
self::extractAttachments($t['issue_id'], $structure);
// notifications about new emails are always external
$internal_only = false;
$assignee_only = false;
// special case when emails are bounced back, so we don't want a notification to customers about those
if (Notification::isBounceMessage($sender_email)) {
// broadcast this email only to the assignees for this issue
$internal_only = true;
$assignee_only = true;
} elseif ($should_create_issue == true) {
// if a new issue was created, only send a copy of the email to the assignee (if any), don't resend to the original TO/CC list
$assignee_only = true;
$internal_only = true;
}
if (Workflow::shouldAutoAddToNotificationList($info['ema_prj_id'])) {
self::addExtraRecipientsToNotificationList($info['ema_prj_id'], $t, $should_create_issue);
}
if (self::isAllowedToEmail($t['issue_id'], $sender_email)) {
Notification::notifyNewEmail(Auth::getUserID(), $t['issue_id'], $t, $internal_only, $assignee_only, '', $sup_id);
}
// try to get usr_id of sender, if not, use system account
$addr = Mail_Helper::getEmailAddress($structure->headers['from']);
if (Misc::isError($addr)) {
// XXX should we log or is this expected?
Error_Handler::logError(array($addr->getMessage() . " addr: {$addr}", $addr->getDebugInfo()), __FILE__, __LINE__);
$usr_id = APP_SYSTEM_USER_ID;
} else {
$usr_id = User::getUserIDByEmail($addr);
if (!$usr_id) {
$usr_id = APP_SYSTEM_USER_ID;
}
}
// mark this issue as updated
if (!empty($t['customer_id']) && $t['customer_id'] != 'NULL' && (empty($usr_id) || User::getRoleByUser($usr_id, $prj_id) == User::getRoleID('Customer'))) {
Issue::markAsUpdated($t['issue_id'], 'customer action');
} else {
if (!empty($usr_id) && User::getRoleByUser($usr_id, $prj_id) > User::getRoleID('Customer')) {
Issue::markAsUpdated($t['issue_id'], 'staff response');
} else {
Issue::markAsUpdated($t['issue_id'], 'user response');
}
}
// log routed email
History::add($t['issue_id'], $usr_id, 'email_routed', 'Email routed from {from}', array('from' => $structure->headers['from']));
}
}
} else {
$res = 1;
}
}
if ($res > 0) {
// need to delete the message from the server?
if (!$info['ema_leave_copy']) {
@imap_delete($mbox, $num);
} else {
// mark the message as already read
@imap_setflag_full($mbox, $num, '\\Seen');
}
}
}
/**
* Method used to remove a round robin entry from the system.
*
* @access public
* @return boolean
*/
function remove()
{
global $HTTP_POST_VARS;
$items = @implode(", ", Misc::escapeInteger($HTTP_POST_VARS["items"]));
$stmt = "DELETE FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "project_round_robin\n WHERE\n prr_id IN ({$items})";
$res = $GLOBALS["db_api"]->dbh->query($stmt);
if (PEAR::isError($res)) {
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
return false;
} else {
Round_Robin::removeUserAssociations($HTTP_POST_VARS['items']);
return true;
}
}
/**
* Method used to get the details of the given customer contact.
*
* @access public
* @param integer $contact_id The customer contact ID
* @return array The customer details
*/
function getContactLoginDetails($contact_id)
{
$stmt = "SELECT\n usr_email,\n usr_password,\n usr_full_name\n FROM\n " . ETEL_USER_TABLE . "\n WHERE\n usr_customer_contact_id = {$contact_id}";
$res = $GLOBALS["db_api"]->dbh->getRow($stmt);
if (PEAR::isError($res)) {
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
return -1;
} else {
if (empty($res)) {
return -2;
} else {
return $res;
}
}
}
public static function fatalDBError($e)
{
/** @var $e PEAR_Error */
Error_Handler::logError(array($e->getMessage(), $e->getDebugInfo()), __FILE__, __LINE__);
/** @global $error_type */
$error_type = 'db';
require_once APP_PATH . '/htdocs/offline.php';
exit(2);
}
/**
* Returns workload information for the specified date range and interval.
*
* @access public
* @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.
* @return array An array containing workload data.
*/
function getWorkloadByDateRange($interval, $type, $start, $end)
{
$data = array();
$start = Misc::escapeString($start);
$end = Misc::escapeString($end);
// 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 = "IF(DATE_FORMAT(%1\$s, '%%w') = 0, 7, DATE_FORMAT(%1\$s, '%%w'))";
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;
}
// get issue counts
$stmt = "SELECT\n DATE_FORMAT(iss_created_date, '{$format}'),\n count(*)\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue\n WHERE\n iss_prj_id=" . Auth::getCurrentProject() . " AND\n iss_created_date BETWEEN '{$start}' AND '{$end}'\n GROUP BY\n DATE_FORMAT(iss_created_date, '{$format}')";
if (!empty($order_by)) {
$stmt .= "\nORDER BY " . sprintf($order_by, 'iss_created_date');
}
$res = $GLOBALS["db_api"]->dbh->getAssoc($stmt);
if (PEAR::isError($res)) {
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
return array();
}
$data["issues"]["points"] = $res;
if (count($res) > 0) {
$stats = new Math_Stats();
$stats->setData($res);
$data["issues"]["stats"] = array("total" => $stats->sum(), "avg" => $stats->mean(), "median" => $stats->median(), "max" => $stats->max());
} else {
$data["issues"]["stats"] = array("total" => 0, "avg" => 0, "median" => 0, "max" => 0);
}
// get email counts
$stmt = "SELECT\n DATE_FORMAT(sup_date, '{$format}'),\n count(*)\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "support_email,\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "email_account\n WHERE\n sup_ema_id=ema_id AND\n ema_prj_id=" . Auth::getCurrentProject() . " AND\n sup_date BETWEEN '{$start}' AND '{$end}'\n GROUP BY\n DATE_FORMAT(sup_date, '{$format}')";
if (!empty($order_by)) {
$stmt .= "\nORDER BY " . sprintf($order_by, 'sup_date');
}
$res = $GLOBALS["db_api"]->dbh->getAssoc($stmt);
if (PEAR::isError($res)) {
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
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;
}
print_r($res);
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
} else {
$data['time'] = processResult($res, 'ttr_created_date', 'ttr_iss_id');
for ($i = 0; $i < count($data['time']); $i++) {
$data['time'][$i]['time_spent'] = Misc::getFormattedTime($data['time'][$i]['ttr_time_spent'], true);
}
}
}
if (empty($_REQUEST['developer']) && in_array('reminder', $_REQUEST['activity_types'])) {
$sql = "SELECT\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "reminder_history.*,\n iss_summary,\n sta_color,\n rma_title\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "reminder_history,\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "reminder_action,\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue,\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "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 = {$prj_id} AND\n";
$sql .= createWhereClause('rmh_created_date');
$res = $GLOBALS["db_api"]->dbh->getAll($sql, DB_FETCHMODE_ASSOC);
if (PEAR::isError($res)) {
print_r($res);
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
} else {
$data['reminder'] = processResult($res, 'rmh_created_date', 'rmh_iss_id');
}
}
$tpl->assign(array("unit" => $_REQUEST['unit'], "amount" => $_REQUEST['amount'], "developer" => $_REQUEST['developer'], "start_date" => @$start_date, "end_date" => @$end_date, "data" => $data));
}
function createWhereClause($date_field, $user_field = false)
{
global $start_date, $end_date;
$sql = '';
if ($_REQUEST['report_type'] == 'recent') {
$sql .= "{$date_field} >= DATE_SUB('" . Date_API::getCurrentDateGMT() . "', INTERVAL " . Misc::escapeInteger($_REQUEST['amount']) . " " . Misc::escapeString($_REQUEST['unit']) . ")";
} else {
$sql .= "{$date_field} BETWEEN '{$start_date}' AND '{$end_date}'";
}
/**
* Returns the appropriate message body for a given MIME-based decoded
* structure.
*
* @param object $output The parsed message structure
* @return string The message body
* @see self::decode()
*/
public static function getMessageBody(&$output)
{
$parts = array();
self::parse_output($output, $parts);
if (empty($parts)) {
Error_Handler::logError(array('self::parse_output failed. Corrupted MIME in email?', $output), __FILE__, __LINE__);
// we continue as if nothing happened until it's clear it's right check to do.
}
$str = '';
$is_html = false;
if (isset($parts['text'])) {
$str = implode("\n\n", $parts['text']);
} elseif (isset($parts['html'])) {
$is_html = true;
$str = implode("\n\n", $parts['html']);
// hack for inotes to prevent content from being displayed all on one line.
$str = str_replace('</DIV><DIV>', "\n", $str);
$str = str_replace(array('<br>', '<br />', '<BR>', '<BR />'), "\n", $str);
}
// XXX: do we also need to do something here about base64 encoding?
if ($is_html) {
$str = strip_tags($str);
// convert html entities. this should be done after strip tags
$str = html_entity_decode($str, ENT_QUOTES, APP_CHARSET);
}
return $str;
}
/**
* Clears the last triggered reminder for a given issue ID.
*
* @access public
* @param integer $issue_id The issue ID
* @return boolean
*/
function clearLastTriggered($issue_id)
{
$stmt = "DELETE FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "reminder_triggered_action\n WHERE\n rta_iss_id=" . Misc::escapeInteger($issue_id);
$res = $GLOBALS["db_api"]->dbh->query($stmt);
if (PEAR::isError($res)) {
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
return false;
} else {
return true;
}
}
/**
* Method used to list the history of triggered reminder actions
* for a given issue.
*
* @access public
* @param integer $iss_id The issue ID
* @return array The list of triggered reminder actions
*/
function getHistoryList($iss_id)
{
$stmt = "SELECT\n rmh_created_date,\n rma_title\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "reminder_history,\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "reminder_action\n WHERE\n rmh_iss_id=" . Misc::escapeInteger($iss_id) . " AND\n rmh_rma_id=rma_id\n ORDER BY\n rmh_created_date DESC";
$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 array();
} else {
for ($i = 0; $i < count($res); $i++) {
$res[$i]["rmh_created_date"] = Date_API::getFormattedDate($res[$i]["rmh_created_date"]);
}
return $res;
}
}
/**
* Method used to get the title for a specific project category.
*
* @access public
* @param integer $prc_id The category ID
* @return string The category title
*/
function getTitle($prc_id)
{
$stmt = "SELECT\n prc_title\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "project_category\n WHERE\n prc_id=" . Misc::escapeInteger($prc_id);
$res = $GLOBALS["db_api"]->dbh->getOne($stmt);
if (PEAR::isError($res)) {
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
return "";
} else {
return $res;
}
}
/**
* Connects to the SMTP server and sends the queued message.
*
* @param string $recipient The recipient of this message
* @param string $text_headers The full headers of this message
* @param string $body The full body of this message
* @param string $status The status of this message
* @return true, or a PEAR_Error object
*/
private function _sendEmail($recipient, $text_headers, &$body, $status)
{
$header_names = Mime_Helper::getHeaderNames($text_headers);
$_headers = self::_getHeaders($text_headers, $body);
$headers = array();
foreach ($_headers as $lowercase_name => $value) {
// need to remove the quotes to avoid a parsing problem
// on senders that have extended characters in the first
// or last words in their sender name
if ($lowercase_name == 'from') {
$value = Mime_Helper::removeQuotes($value);
}
$value = Mime_Helper::encode($value);
// add the quotes back
if ($lowercase_name == 'from') {
$value = Mime_Helper::quoteSender($value);
}
$headers[$header_names[$lowercase_name]] = $value;
}
// remove any Reply-To:/Return-Path: values from outgoing messages
unset($headers['Reply-To']);
unset($headers['Return-Path']);
// mutt sucks, so let's remove the broken Mime-Version header and add the proper one
if (in_array('Mime-Version', array_keys($headers))) {
unset($headers['Mime-Version']);
$headers['MIME-Version'] = '1.0';
}
$mail = Mail::factory('smtp', Mail_Helper::getSMTPSettings());
$res = $mail->send($recipient, $headers, $body);
if (Misc::isError($res)) {
// special handling of errors when the mail server is down
$msg = $res->getMessage();
$cant_notify = $status == 'error' || strstr($msg, 'unable to connect to smtp server') || stristr($msg, 'Failed to connect to') !== false;
Error_Handler::logError(array($msg, $res->getDebugInfo()), __FILE__, __LINE__, !$cant_notify);
return $res;
}
return true;
}
function updateAccess($id, $prj_id, $role)
{
$stmt = "INSERT IGNORE INTO\r\n\t\t\t" . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "project_user\r\n\t\t (\r\n\t\t\tpru_prj_id,\r\n\t\t\tpru_usr_id,\r\n\t\t\tpru_role\r\n\t\t ) VALUES (\r\n\t\t\t" . $prj_id . ",\r\n\t\t\t" . $id . ",\r\n\t\t\t" . $role . "\r\n\t\t )";
$res = $GLOBALS["db_api"]->dbh->query($stmt);
if (PEAR::isError($res)) {
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
return -1;
}
}
/**
* Method used to get the email account associated with the given
* issue' project.
*
* @access public
* @param integer $issue_id The issue ID
* @return integer The email account ID
*/
function getEmailAccountByIssueID($issue_id)
{
$stmt = "SELECT\n ema_id\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "email_account,\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue\n WHERE\n ema_prj_id=iss_prj_id AND\n iss_id={$issue_id}";
$res = $GLOBALS["db_api"]->dbh->getOne($stmt);
if (PEAR::isError($res)) {
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
return "";
} else {
return $res;
}
}
/**
* @return Auth_Backend_Interface
*/
public static function getAuthBackend()
{
/** @var Auth_Backend_Interface $instance */
static $instance = false;
if ($instance == false) {
$class = APP_AUTH_BACKEND;
// legacy: allow lowercase variants
if (strtolower($class) == 'mysql_auth_backend') {
$class = 'Mysql_Auth_Backend';
} elseif (strtolower($class) == 'ldap_auth_backend') {
$class = 'LDAP_Auth_Backend';
}
try {
$instance = new $class();
} catch (AuthException $e) {
$message = "Unable to use auth backend '{$class}': {$e->getMessage()}";
error_log($message);
Error_Handler::logError($message);
if (APP_AUTH_BACKEND_ALLOW_FALLBACK != true) {
die('Unable to use auth backend: ' . $class);
}
$instance = self::getFallBackAuthBackend();
}
}
return $instance;
}
/**
* Returns the last person to close the issue
*
* @param integer $issue_id The ID of the issue
* @return integer usr_id
*/
function getIssueCloser($issue_id)
{
$sql = "SELECT\n his_usr_id\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue_history\n WHERE\n his_iss_id = " . Misc::escapeInteger($issue_id) . " AND\n his_htt_id = '" . History::getTypeID('issue_closed') . "'\n ORDER BY\n his_created_date DESC\n LIMIT 1";
$res = $GLOBALS["db_api"]->dbh->getOne($sql);
if (PEAR::isError($res)) {
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
return 0;
}
return $res;
}
/**
* Method used to lookup the user ID of a given email address.
*
* @param string $email The email address associated with the user account
* @param boolean $check_aliases If user aliases should be checked as well.
* @return integer The user ID
*/
public static function getUserIDByEmail($email, $check_aliases = false)
{
static $returns;
if (!is_string($email)) {
if (Misc::isError($email)) {
Error_Handler::logError(array($email->getMessage(), $email->getDebugInfo()), __FILE__, __LINE__);
return null;
}
Error_Handler::logError('$email parameter is not a string: ' . gettype($email), __FILE__, __LINE__);
return null;
}
if (!empty($returns[$email])) {
return $returns[$email];
}
$stmt = 'SELECT
usr_id
FROM
{{%user}}
WHERE
usr_email=?';
$res = DB_Helper::getInstance()->getOne($stmt, array($email));
if (empty($res) && $check_aliases) {
$res = self::getUserIDByAlias($email);
}
$returns[$email] = $res;
return $returns[$email];
}
/**
* Check if $e is PEAR error, if so, throw as DbException
*
* @param $e PEAR_Error|array|object|int
*/
private function assertError($e, $depth = 2)
{
if (!Misc::isError($e)) {
return;
}
list($file, $line) = self::getTrace($depth);
Error_Handler::logError(array($e->getMessage(), $e->getDebugInfo()), $file, $line);
$de = new DbException($e->getMessage(), $e->getCode());
$de->setExceptionLocation($file, $line);
error_log($de->getMessage());
error_log($de->getTraceAsString());
throw $de;
}
/**
* 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 " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue\n SET\n iss_percent_complete = '100%'\n WHERE\n iss_id = {$issue_id}";
$res = $GLOBALS["db_api"]->dbh->query($sql);
if (PEAR::isError($res)) {
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
return false;
}
echo "Workflow: handleIssueClosed<br />\n";
}
/**
* Method used to see if a specific reminder field can be compared to other fields.
*
* @access public
* @param integer $field_id The reminder field ID
* @return boolean If this field can be compared to other fields.
*/
function canFieldBeCompared($field_id)
{
$stmt = "SELECT\n rmf_allow_column_compare\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "reminder_field\n WHERE\n rmf_id=" . Misc::escapeInteger($field_id);
$res = $GLOBALS["db_api"]->dbh->getOne($stmt);
if (PEAR::isError($res)) {
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
return '';
} else {
return $res;
}
}
/**
* Returns an associative array with the list of faq entry
* IDs and their respective ranking.
*
* @access private
* @return array The list of faq entries
*/
function _getRanking()
{
$stmt = "SELECT\n faq_id,\n faq_rank\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "faq\n ORDER BY\n faq_rank ASC";
$res = $GLOBALS["db_api"]->dbh->getAssoc($stmt);
if (PEAR::isError($res)) {
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
return array();
} else {
return $res;
}
}
/**
* Moves an email from one account to another.
*
* @access public
* @param integer $sup_id The ID of the message.
* @param integer $current_ema_id The ID of the account the message is currently in.
* @param integer $new_ema_id The ID of the account to move the message too.
* @return integer -1 if there was error moving the message, 1 otherwise.
*/
function moveEmail($sup_id, $current_ema_id, $new_ema_id)
{
$usr_id = Auth::getUserID();
$email = Support::getEmailDetails($current_ema_id, $sup_id);
if (!empty($email['sup_iss_id'])) {
return -1;
}
$info = Email_Account::getDetails($new_ema_id);
$full_email = Support::getFullEmail($sup_id);
$structure = Mime_Helper::decode($full_email, true, true);
$headers = '';
foreach ($structure->headers as $key => $value) {
if (is_array($value)) {
continue;
}
$headers .= "{$key}: {$value}\n";
}
// handle auto creating issues (if needed)
$should_create_array = Support::createIssueFromEmail($info, $headers, $email['seb_body'], $email['timestamp'], $email['sup_from'], $email['sup_subject']);
$should_create_issue = $should_create_array['should_create_issue'];
$associate_email = $should_create_array['associate_email'];
$issue_id = $should_create_array['issue_id'];
$customer_id = $should_create_array['customer_id'];
if (empty($issue_id)) {
$issue_id = 0;
}
if (empty($customer_id)) {
$customer_id = 'NULL';
}
$sql = "UPDATE\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "support_email\n SET\n sup_ema_id = " . Misc::escapeInteger($new_ema_id) . ",\n sup_iss_id = " . Misc::escapeInteger($issue_id) . ",\n sup_customer_id = " . Misc::escapeInteger($customer_id) . "\n WHERE\n sup_id = " . Misc::escapeInteger($sup_id) . " AND\n sup_ema_id = " . Misc::escapeInteger($current_ema_id);
$res = $GLOBALS["db_api"]->dbh->query($sql);
if (PEAR::isError($res)) {
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
return -1;
}
$row = array('customer_id' => $customer_id, 'issue_id' => $issue_id, 'ema_id' => $new_ema_id, 'message_id' => $email['sup_message_id'], 'date' => $email['timestamp'], 'from' => $email['sup_from'], 'to' => $email['sup_to'], 'cc' => $email['sup_cc'], 'subject' => $email['sup_subject'], 'body' => $email['seb_body'], 'full_email' => $email['seb_full_email'], 'has_attachment' => $email['sup_has_attachment']);
Workflow::handleNewEmail(Support::getProjectByEmailAccount($new_ema_id), $issue_id, $structure, $row);
return 1;
}
