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