/**
* Method used to get the details of a specific resolution.
*
* @access public
* @param integer $res_id The resolution ID
* @return array The details of the resolution
*/
function getDetails($res_id)
{
$stmt = "SELECT\n *\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "resolution\n WHERE\n res_id=" . Misc::escapeInteger($res_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 {
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;
}
/**
* Method used to get the first support email account associated
* with the current activated project.
*
* @access public
* @param integer $prj_id The ID of the project. If blank the currently project will be used.
* @return integer The email account ID
*/
function getEmailAccount($prj_id = false)
{
if ($prj_id == false) {
$prj_id = Auth::getCurrentProject();
}
$stmt = "SELECT\n ema_id\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "email_account\n WHERE\n ema_prj_id=" . Misc::escapeInteger($prj_id) . "\n LIMIT\n 0, 1";
$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;
}
}
/**
* Method used to get the list of issues to be displayed in the grid layout.
*
* @param array $options The search parameters
* @return string The where clause
*/
public static function buildWhereClause($options)
{
$usr_id = Auth::getUserID();
$prj_id = Auth::getCurrentProject();
$role_id = User::getRoleByUser($usr_id, $prj_id);
$usr_details = User::getDetails($usr_id);
$stmt = ' AND iss_usr_id = usr_id';
if ($role_id == User::getRoleID('Customer')) {
$crm = CRM::getInstance($prj_id);
$contact = $crm->getContact($usr_details['usr_customer_contact_id']);
$stmt .= " AND iss_customer_contract_id IN('" . implode("','", $contact->getContractIDS()) . "')";
$stmt .= " AND iss_customer_id ='" . Auth::getCurrentCustomerID() . "'";
} elseif ($role_id == User::getRoleID('Reporter') && Project::getSegregateReporters($prj_id)) {
$stmt .= " AND (\n iss_usr_id = {$usr_id} OR\n iur_usr_id = {$usr_id}\n )";
}
if (!empty($usr_details['usr_par_code'])) {
// restrict partners
$stmt .= " AND ipa_par_code = '" . Misc::escapeString($usr_details['usr_par_code']) . "'";
}
if (!empty($options['users'])) {
$stmt .= " AND (\n";
if (stristr($options['users'], 'grp') !== false) {
$chunks = explode(':', $options['users']);
$stmt .= 'iss_grp_id = ' . Misc::escapeInteger($chunks[1]);
} else {
if ($options['users'] == '-1') {
$stmt .= 'isu_usr_id IS NULL';
} elseif ($options['users'] == '-2') {
$stmt .= 'isu_usr_id IS NULL OR isu_usr_id=' . $usr_id;
} elseif ($options['users'] == '-3') {
$stmt .= 'isu_usr_id = ' . $usr_id . ' OR iss_grp_id = ' . User::getGroupID($usr_id);
} elseif ($options['users'] == '-4') {
$stmt .= 'isu_usr_id IS NULL OR isu_usr_id = ' . $usr_id . ' OR iss_grp_id = ' . User::getGroupID($usr_id);
} else {
$stmt .= 'isu_usr_id =' . Misc::escapeInteger($options['users']);
}
}
$stmt .= ')';
}
if (!empty($options['reporter'])) {
$stmt .= ' AND iss_usr_id = ' . Misc::escapeInteger($options['reporter']);
}
if (!empty($options['show_authorized_issues'])) {
$stmt .= " AND (iur_usr_id={$usr_id})";
}
if (!empty($options['show_notification_list_issues'])) {
$stmt .= " AND (sub_usr_id={$usr_id})";
}
if (!empty($options['keywords'])) {
$stmt .= " AND (\n";
if ($options['search_type'] == 'all_text' && APP_ENABLE_FULLTEXT) {
$stmt .= 'iss_id IN(' . implode(', ', self::getFullTextIssues($options)) . ')';
} elseif ($options['search_type'] == 'customer' && CRM::hasCustomerIntegration($prj_id)) {
// check if the user is trying to search by customer name / email
$crm = CRM::getInstance($prj_id);
$customer_ids = $crm->getCustomerIDsByString($options['keywords'], true);
if (count($customer_ids) > 0) {
$stmt .= ' iss_customer_id IN (' . implode(', ', $customer_ids) . ')';
} else {
// no results, kill query
$stmt .= ' iss_customer_id = -1';
}
} else {
$stmt .= '(' . Misc::prepareBooleanSearch('iss_summary', $options['keywords']);
$stmt .= ' OR ' . Misc::prepareBooleanSearch('iss_description', $options['keywords']) . ')';
}
$stmt .= "\n) ";
}
if (!empty($options['customer_id'])) {
$stmt .= " AND iss_customer_id='" . Misc::escapeString($options['customer_id']) . "'";
}
if (!empty($options['priority'])) {
$stmt .= ' AND iss_pri_id=' . Misc::escapeInteger($options['priority']);
}
if (!empty($options['status'])) {
$stmt .= ' AND iss_sta_id=' . Misc::escapeInteger($options['status']);
}
if (!empty($options['category'])) {
if (!is_array($options['category'])) {
$options['category'] = array($options['category']);
}
$stmt .= ' AND iss_prc_id IN(' . implode(', ', Misc::escapeInteger($options['category'])) . ')';
}
if (!empty($options['hide_closed'])) {
$stmt .= ' AND sta_is_closed=0';
}
if (!empty($options['release'])) {
$stmt .= ' AND iss_pre_id = ' . Misc::escapeInteger($options['release']);
}
if (!empty($options['product'])) {
$stmt .= ' AND ipv_pro_id = ' . Misc::escapeInteger($options['product']);
}
// now for the date fields
$date_fields = array('created_date', 'updated_date', 'last_response_date', 'first_response_date', 'closed_date');
foreach ($date_fields as $field_name) {
if (!empty($options[$field_name])) {
switch ($options[$field_name]['filter_type']) {
case 'greater':
$stmt .= " AND iss_{$field_name} >= '" . Misc::escapeString($options[$field_name]['start']) . "'";
break;
case 'less':
$stmt .= " AND iss_{$field_name} <= '" . Misc::escapeString($options[$field_name]['start']) . "'";
break;
case 'between':
$stmt .= " AND iss_{$field_name} BETWEEN '" . Misc::escapeString($options[$field_name]['start']) . "' AND '" . Misc::escapeString($options[$field_name]['end']) . "'";
break;
case 'null':
$stmt .= " AND iss_{$field_name} IS NULL";
break;
case 'in_past':
if (strlen($options[$field_name]['time_period']) == 0) {
$options[$field_name]['time_period'] = 0;
}
$stmt .= " AND (UNIX_TIMESTAMP('" . Date_Helper::getCurrentDateGMT() . "') - UNIX_TIMESTAMP(iss_{$field_name})) <= (" . Misc::escapeInteger($options[$field_name]['time_period']) . '*3600)';
break;
}
}
}
// 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);
$fld_db_name = Custom_Field::getDBValueFieldNameByType($field['fld_type']);
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) {
$cfo_id = Misc::escapeString($cfo_id);
$stmt .= " AND\n cf" . $fld_id . '_' . $cfo_id . '.icf_iss_id = iss_id';
$stmt .= " AND\n cf" . $fld_id . '_' . $cfo_id . ".icf_fld_id = {$fld_id}";
$stmt .= " AND\n cf" . $fld_id . '_' . $cfo_id . '.' . $fld_db_name . " = '{$cfo_id}'";
}
} elseif ($field['fld_type'] == 'date') {
if (empty($search_value['Year']) || empty($search_value['Month']) || empty($search_value['Day'])) {
continue;
}
$search_value = $search_value['Year'] . '-' . $search_value['Month'] . '-' . $search_value['Day'];
$stmt .= " AND\n (iss_id = cf" . $fld_id . '.icf_iss_id AND
cf' . $fld_id . '.' . $fld_db_name . " = '" . Misc::escapeString($search_value) . "')";
} elseif ($field['fld_type'] == 'integer') {
$value = $search_value['value'];
switch ($search_value['filter_type']) {
case 'ge':
$cmp = '>=';
break;
case 'le':
$cmp = '<=';
break;
case 'gt':
$cmp = '>';
break;
case 'lt':
$cmp = '<';
break;
default:
$cmp = '=';
break;
}
$stmt .= " AND\n (iss_id = cf" . $fld_id . '.icf_iss_id';
$stmt .= " AND\n cf" . $fld_id . ".icf_fld_id = {$fld_id}";
$stmt .= ' AND cf' . $fld_id . '.' . $fld_db_name . $cmp . Misc::escapeString($value) . ')';
} else {
$stmt .= " AND\n (iss_id = cf" . $fld_id . '.icf_iss_id';
$stmt .= " AND\n cf" . $fld_id . ".icf_fld_id = {$fld_id}";
if ($field['fld_type'] == 'combo') {
$stmt .= ' AND cf' . $fld_id . '.' . $fld_db_name . " IN('" . implode("', '", Misc::escapeString($search_value)) . "')";
} else {
$stmt .= ' AND cf' . $fld_id . '.' . $fld_db_name . " LIKE '%" . Misc::escapeString($search_value) . "%'";
}
$stmt .= ')';
}
}
}
// clear cached full-text values if we are not searching fulltext anymore
if (APP_ENABLE_FULLTEXT && @$options['search_type'] != 'all_text') {
Session::set('fulltext_string', '');
Session::set('fulltext_issues', '');
}
return $stmt;
}
/**
* Method used to get the list of emails to be displayed in the
* grid layout.
*
* @param array $options The search parameters
* @param integer $current_row The current page number
* @param integer $max The maximum number of rows per page
* @return array The list of issues to be displayed
*/
public static function getEmailListing($options, $current_row = 0, $max = 5)
{
$prj_id = Auth::getCurrentProject();
if ($max == 'ALL') {
$max = 9999999;
}
$start = $current_row * $max;
$stmt = 'SELECT
sup_id,
sup_ema_id,
sup_iss_id,
sup_customer_id,
sup_from,
sup_date,
sup_to,
sup_subject,
sup_has_attachment
FROM
(
{{%support_email}},
{{%email_account}}';
if (!empty($options['keywords'])) {
$stmt .= ', {{%support_email_body}} ';
}
$stmt .= '
)
LEFT JOIN
{{%issue}}
ON
sup_iss_id = iss_id';
$stmt .= self::buildWhereClause($options);
$stmt .= '
ORDER BY
' . Misc::escapeString($options['sort_by']) . ' ' . Misc::escapeString($options['sort_order']);
$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' => '', 'info' => '');
}
if (count($res) < 1 && $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("emails.php?pagerRow=0&rows={$max}");
}
if (CRM::hasCustomerIntegration($prj_id)) {
$crm = CRM::getInstance($prj_id);
$customer_ids = array();
foreach ($res as $row) {
if (!empty($row['sup_customer_id']) && !in_array($row['sup_customer_id'], $customer_ids)) {
$customer_ids[] = $row['sup_customer_id'];
}
}
if (count($customer_ids) > 0) {
$company_titles = $crm->getCustomerTitles($customer_ids);
}
}
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'] = implode(', ', Mail_Helper::getName($row['sup_from'], true));
if (empty($row['sup_to']) && !empty($row['sup_iss_id'])) {
$row['sup_to'] = 'Notification List';
} else {
$to = Mail_Helper::getName($row['sup_to']);
// Ignore unformattable headers
if (!Misc::isError($to)) {
$row['sup_to'] = Mime_Helper::fixEncoding($to);
}
}
if (CRM::hasCustomerIntegration($prj_id)) {
// FIXME: $company_titles maybe used uninitialied
$row['customer_title'] = $company_titles[$row['sup_customer_id']];
}
}
$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));
}
/**
* 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 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;
}
}
/**
* Returns summary information about all time spent by a user in a specified time frame.
*
* @access public
* @param string $usr_id The ID of the user this report is for.
* @param integer The timestamp of the beginning of the report.
* @param integer The timestamp of the end of this report.
* @return array An array of data containing information about time trackinge
*/
function getSummaryByUser($usr_id, $start, $end)
{
$stmt = "SELECT\n ttc_title,\n COUNT(ttr_id) as total,\n SUM(ttr_time_spent) as total_time\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "time_tracking,\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "time_tracking_category\n WHERE\n ttr_ttc_id = ttc_id AND\n ttr_usr_id = " . Misc::escapeInteger($usr_id) . " AND\n ttr_created_date BETWEEN '" . Misc::escapeString($start) . "' AND '" . Misc::escapeString($end) . "'\n GROUP BY\n ttc_title";
$res = $GLOBALS["db_api"]->dbh->getAssoc($stmt, false, array(), DB_FETCHMODE_ASSOC);
if (PEAR::isError($res)) {
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
return array();
} else {
if (count($res) > 0) {
foreach ($res as $index => $row) {
$res[$index]["formatted_time"] = Misc::getFormattedTime($res[$index]["total_time"], true);
}
}
return $res;
}
}
/**
* Converts an email to a draft and sends it.
*
* @access public
* @param integer $draft_id The id of the draft to send.
*/
function send($draft_id)
{
global $HTTP_POST_VARS;
$draft_id = Misc::escapeInteger($draft_id);
$draft = Draft::getDetails($draft_id);
$HTTP_POST_VARS["issue_id"] = $draft["emd_iss_id"];
$HTTP_POST_VARS["subject"] = $draft["emd_subject"];
$HTTP_POST_VARS["from"] = User::getFromHeader(Auth::getUserID());
$HTTP_POST_VARS["to"] = $draft["to"];
$HTTP_POST_VARS["cc"] = @join(";", $draft["cc"]);
$HTTP_POST_VARS["message"] = $draft["emd_body"];
$HTTP_POST_VARS["ema_id"] = Email_Account::getEmailAccount();
$res = Support::sendEmail();
if ($res == 1) {
Draft::remove($draft_id);
}
return $res;
}
/**
* Removes the selected notes from the database.
*
* @access public
* @param array $ids An array of cno_id's to be deleted.
*/
function removeNotes($ids)
{
$stmt = "DELETE FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "customer_note\n WHERE\n cno_id IN (" . join(", ", Misc::escapeInteger($ids)) . ")";
$res = $GLOBALS['db_api']->dbh->query($stmt);
if (PEAR::isError($res)) {
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
return -1;
} else {
return 1;
}
}
/**
* Returns the replier based on the given issue and email address combo.
*
* @access public
* @param integer $issue_id The id of the issue.
* @param string $email The email address of the user
* @return integer The id of the replier
*/
function getReplierIDByEmail($issue_id, $email)
{
$stmt = "SELECT\n iur_id\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue_user_replier\n LEFT JOIN\n " . ETEL_USER_TABLE . "\n ON\n iur_usr_id = usr_id\n WHERE\n iur_iss_id = " . Misc::escapeInteger($issue_id) . " AND\n (iur_email = '" . Misc::escapeString($email) . "' OR usr_email = '" . Misc::escapeString($email) . "')";
$res = $GLOBALS["db_api"]->dbh->getOne($stmt);
if (PEAR::isError($res)) {
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
return 0;
}
return $res;
}
/**
* Method used to remove all custom filters associated with some
* specific projects.
*
* @access public
* @param array $ids List of projects to remove from
* @return boolean Whether the removal worked properly or not
*/
function removeByProjects($ids)
{
$items = implode(", ", Misc::escapeInteger($ids));
$stmt = "DELETE FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "custom_filter\n WHERE\n cst_prj_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 {
return true;
}
}
if (Search::getParam('hide_closed', true) === '') {
$options_override['hide_closed'] = 1;
}
$_REQUEST['nosave'] = 1;
} elseif ($_REQUEST['view'] == 'customer_all' && isset($_REQUEST['customer_id'])) {
$options_override = array('customer_id' => Misc::escapeString($_REQUEST['customer_id']), 'rows' => $rows);
if (Search::getParam('hide_closed', true) === '') {
$options_override['hide_closed'] = 0;
}
$_REQUEST['nosave'] = 1;
$profile = Search_Profile::getProfile($usr_id, $prj_id, 'issue');
Search_Profile::remove($usr_id, $prj_id, 'issue');
Auth::redirect('list.php?customer_id=' . Misc::escapeString($_REQUEST['customer_id']) . "&hide_closed=1&rows={$rows}&sort_by=" . $profile['sort_by'] . '&sort_order=' . $profile['sort_order'] . '&nosave=1');
} elseif ($_REQUEST['view'] == 'reporter' && isset($_REQUEST['reporter_id'])) {
$profile = Search_Profile::getProfile($usr_id, $prj_id, 'issue');
Auth::redirect('list.php?reporter=' . Misc::escapeInteger($_REQUEST['reporter_id']) . "&hide_closed=1&rows={$rows}&sort_by=" . $profile['sort_by'] . '&sort_order=' . $profile['sort_order'] . '&nosave=1');
} elseif ($_REQUEST['view'] == 'clear') {
Search_Profile::remove($usr_id, $prj_id, 'issue');
Auth::redirect('list.php');
} elseif ($_REQUEST['view'] == 'clearandfilter') {
Search_Profile::remove($usr_id, $prj_id, 'issue');
Auth::redirect('list.php?' . str_replace('view=clearandfilter&', '', $_SERVER['QUERY_STRING']));
}
}
if (!empty($_REQUEST['nosave'])) {
$options = Search::saveSearchParams(false);
} else {
$options = Search::saveSearchParams();
}
$options += $options_override;
$options = array_merge($options, $options_override);
function getMessageRecipients($type, $type_id)
{
$sql = "SELECT\n maq_recipient\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "mail_queue\n WHERE\n maq_type = '" . Misc::escapeString($type) . "' AND\n maq_type_id = " . Misc::escapeInteger($type_id);
$res = $GLOBALS["db_api"]->dbh->getCol($sql);
if (PEAR::isError($res)) {
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
return false;
} else {
for ($i = 0; $i < count($res); $i++) {
$res[$i] = Mime_Helper::decodeAddress(str_replace('"', '', $res[$i]));
}
return $res;
}
}
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}'";
}
if ($user_field != false && !empty($_REQUEST['developer'])) {
$sql .= " AND {$user_field} = " . Misc::escapeString($_REQUEST['developer']);
}
$sql .= " ORDER BY {$date_field} " . Misc::escapeString($_REQUEST['sort_order']);
return $sql;
}
/**
* Method used to get the message-ID associated with a given note
* id.
*
* @access public
* @param integer $id The ID
* @return string The Message-ID
*/
function getMessageIDbyID($id)
{
$stmt = "SELECT\n not_message_id\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "note\n WHERE\n not_id=" . Misc::escapeInteger($id);
$res = $GLOBALS["db_api"]->dbh->getOne($stmt);
if (PEAR::isError($res)) {
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
return false;
} else {
if (empty($res)) {
return false;
} 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;
}
/**
* 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 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;
}
}
/**
* 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;
}
}
/**
* 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;
}
}
/**
* 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;
}
die('Invalid issue_id');
}
$usr_id = Auth::getUserID();
// check if user role is above "Standard User"
if (User::getRoleByUser($usr_id, Issue::getProjectID($issue_id)) < User::getRoleID('Standard User')) {
die('Forbidden');
}
// check if user can acess the issue
if (!Issue::canAccess($issue_id, $usr_id)) {
die('Forbidden');
}
switch ($field_name) {
case 'expected_resolution_date':
$day = Misc::escapeInteger($_POST['day']);
$month = Misc::escapeInteger($_POST['month']);
$year = Misc::escapeInteger($_POST['year']);
if ($day == 0 && $month == 1 && $year == 0) {
// clear button
$date = null;
} else {
$date = sprintf('%04d-%02d-%02d', $year, $month, $day);
}
$res = Issue::setExpectedResolutionDate($issue_id, $date);
if ($res == -1) {
die('Update failed');
}
if ($date !== null) {
echo Date_Helper::getSimpleDate($date, false);
}
break;
default:
/**
* 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 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;
}
}
/**
* 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;
}
}
/**
* Accepts a value and cleans it to only contain numeric values
*
* @access public
* @param mixed $input The original input.
* @return integer The input converted to an integer
*/
function escapeInteger($input)
{
if (is_array($input)) {
foreach ($input as $key => $value) {
$input[$key] = Misc::escapeInteger($value);
}
} else {
settype($input, 'integer');
}
return $input;
}
/**
* Returns data for the custom fields report, based on the field and options passed in.
*
* @access public
* @param integer $fld_id The id of the custom field.
* @param array $cfo_ids An array of option ids.
* @param string $group_by How the data should be grouped.
* @param boolean $list If the values should be listed out instead of just counted.
* @return array An array of data.
*/
function getCustomFieldReport($fld_id, $cfo_ids, $group_by = "issue", $list = false)
{
$prj_id = Auth::getCurrentProject();
$fld_id = Misc::escapeInteger($fld_id);
$cfo_ids = array_map(array('Misc', 'escapeString'), $cfo_ids);
$backend = Custom_Field::getBackend($fld_id);
if (is_object($backend)) {
$options = array();
foreach ($cfo_ids as $cfo_id) {
$options[$cfo_id] = Custom_Field::getOptionValue($fld_id, $cfo_id);
}
$in_field = 'icf_value';
} else {
// get field values
$stmt = "SELECT\n cfo_id,\n cfo_value\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "custom_field_option\n WHERE\n cfo_fld_id = {$fld_id} AND\n cfo_id IN('" . join("','", $cfo_ids) . "')\n ORDER BY\n cfo_id";
$options = $GLOBALS["db_api"]->dbh->getAssoc($stmt);
if (PEAR::isError($options)) {
Error_Handler::logError(array($options->getMessage(), $options->getDebugInfo()), __FILE__, __LINE__);
return array();
}
$in_field = 'cfo_id';
}
if ($group_by == "customer") {
$group_by_field = "iss_customer_id";
} else {
$group_by_field = "iss_id";
}
if ($list == true) {
$sql = "SELECT\n DISTINCT({$group_by_field}),\n iss_id,\n iss_summary,\n iss_customer_id,\n count(DISTINCT(iss_id)) as row_count\n FROM\n";
if (!is_object($backend)) {
$sql .= APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "custom_field_option,\n";
}
$sql .= APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue_custom_field,\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue\n WHERE\n";
if (!is_object($backend)) {
$sql .= "cfo_id = icf_value AND";
}
$sql .= "\nicf_iss_id = iss_id AND\n icf_fld_id = {$fld_id} AND\n {$in_field} IN('" . join("','", array_keys($options)) . "')\n GROUP BY\n {$group_by_field}\n ORDER BY\n row_count DESC";
$res = $GLOBALS["db_api"]->dbh->getAll($sql, DB_FETCHMODE_ASSOC);
if (PEAR::isError($res)) {
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
return array();
}
if (Customer::hasCustomerIntegration($prj_id)) {
Customer::getCustomerTitlesByIssues($prj_id, $res);
if ($group_by == "issue") {
usort($res, create_function('$a,$b', 'if ($a["customer_title"] < $b["customer_title"]) {
return -1;
} elseif ($a["customer_title"] > $b["customer_title"]) {
return 1;
} else {
return 0;
}'));
}
}
return $res;
}
$data = array();
foreach ($options as $cfo_id => $value) {
$stmt = "SELECT\n COUNT(DISTINCT {$group_by_field})\n FROM\n";
if (!is_object($backend)) {
$stmt .= APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "custom_field_option,\n";
}
$stmt .= APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue_custom_field,\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue\n WHERE\n";
if (!is_object($backend)) {
$stmt .= "cfo_id = icf_value AND";
}
$stmt .= "\nicf_iss_id = iss_id AND\n icf_fld_id = {$fld_id} AND\n {$in_field} = '" . Misc::escapeString($cfo_id) . "'";
$count = $GLOBALS["db_api"]->dbh->getOne($stmt);
if (PEAR::isError($count)) {
Error_Handler::logError(array($count->getMessage(), $count->getDebugInfo()), __FILE__, __LINE__);
return array();
}
$data[$value] = $count;
}
// include count of all other values (used in pie chart)
$stmt = "SELECT\n COUNT(DISTINCT {$group_by_field})\n FROM\n";
if (!is_object($backend)) {
$stmt .= APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "custom_field_option,\n";
}
$stmt .= APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue_custom_field,\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue\n WHERE\n";
if (!is_object($backend)) {
$stmt .= "cfo_id = icf_value AND";
}
$stmt .= "\nicf_iss_id = iss_id AND\n icf_fld_id = {$fld_id} AND\n {$in_field} NOT IN('" . join("','", $cfo_ids) . "')";
$res = $GLOBALS["db_api"]->dbh->getOne($stmt);
if (PEAR::isError($res)) {
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
return array();
}
$data["All Others"] = $res;
return $data;
}
/**
* Method used to get the list of associated support levels for a given
* FAQ entry.
*
* @access public
* @param integer $prj_id The project ID
* @param integer $faq_id The FAQ ID
* @return array The list of projects
*/
function getAssociatedSupportLevels($prj_id, $faq_id)
{
$stmt = "SELECT\n fsl_support_level_id\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "faq_support_level\n WHERE\n fsl_faq_id=" . Misc::escapeInteger($faq_id);
$ids = $GLOBALS["db_api"]->dbh->getCol($stmt);
$t = array();
$levels = Customer::getSupportLevelAssocList(Misc::escapeInteger($prj_id));
foreach ($levels as $support_level_id => $support_level) {
if (in_array($support_level_id, $ids)) {
$t[$support_level_id] = $support_level;
}
}
return $t;
}
/**
* Returns an array of projects who belong to the current group.
*
* @access public
* @param integer $grp_id The ID of the group.
* @return array An array of project ids
*/
function getProjects($grp_id)
{
$stmt = "SELECT\n pgr_prj_id,\n prj_title\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "project_group,\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "project\n WHERE\n pgr_prj_id = prj_id AND\n pgr_grp_id = " . Misc::escapeInteger($grp_id);
$res = $GLOBALS["db_api"]->dbh->getAssoc($stmt);
if (PEAR::isError($res)) {
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
return -1;
}
return $res;
}
