/**
* 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;
}
}
/**
* Method used to get the list of custom fields and custom field
* values associated with a given issue ID. If usr_id is false method
* defaults to current user.
*
* @param integer $prj_id The project ID
* @param integer $iss_id The issue ID
* @param integer $usr_id The ID of the user who is going to be viewing this list.
* @param mixed $form_type The name of the form this is for or if this is an array the ids of the fields to return
* @return array The list of custom fields
*/
public static function getListByIssue($prj_id, $iss_id, $usr_id = null, $form_type = false)
{
if (!$usr_id) {
$usr_id = Auth::getUserID();
}
$usr_role = User::getRoleByUser($usr_id, $prj_id);
if (empty($usr_role)) {
$usr_role = 0;
}
$stmt = 'SELECT
fld_id,
fld_title,
fld_type,
fld_report_form_required,
fld_anonymous_form_required,
fld_close_form_required,
' . self::getDBValueFieldSQL() . ' as value,
icf_value,
icf_value_date,
icf_value_integer,
fld_min_role,
fld_description
FROM
(
{{%custom_field}},
{{%project_custom_field}}
)
LEFT JOIN
{{%issue_custom_field}}
ON
pcf_fld_id=icf_fld_id AND
icf_iss_id=?
WHERE
pcf_fld_id=fld_id AND
pcf_prj_id=? AND
fld_min_role <= ?';
$params = array($iss_id, $prj_id, $usr_role);
if ($form_type != false) {
if (is_array($form_type)) {
$stmt .= ' AND fld_id IN(' . DB_Helper::buildList($form_type) . ')';
$params = array_merge($params, $form_type);
} else {
$fld_name = 'fld_' . Misc::escapeString($form_type);
$stmt .= " AND {$fld_name}=1";
}
}
$stmt .= '
ORDER BY
fld_rank ASC';
try {
$res = DB_Helper::getInstance()->getAll($stmt, $params);
} catch (DbException $e) {
return array();
}
if (count($res) == 0) {
return array();
}
$fields = array();
foreach ($res as &$row) {
if ($row['fld_type'] == 'combo') {
$row['selected_cfo_id'] = $row['value'];
$row['original_value'] = $row['value'];
$row['value'] = self::getOptionValue($row['fld_id'], $row['value']);
$row['field_options'] = self::getOptions($row['fld_id'], false, $iss_id);
// add the select option to the list of values if it isn't on the list (useful for fields with active and non-active items)
if (!empty($row['original_value']) && !isset($row['field_options'][$row['original_value']])) {
$row['field_options'][$row['original_value']] = self::getOptionValue($row['fld_id'], $row['original_value']);
}
$fields[] = $row;
} elseif ($row['fld_type'] == 'multiple' || $row['fld_type'] == 'checkbox') {
// check whether this field is already in the array
$found = 0;
foreach ($fields as $y => $field) {
if ($field['fld_id'] == $row['fld_id']) {
$found = 1;
$found_index = $y;
}
}
$original_value = $row['value'];
if (!$found) {
$row['selected_cfo_id'] = array($row['value']);
$row['value'] = self::getOptionValue($row['fld_id'], $row['value']);
$row['field_options'] = self::getOptions($row['fld_id']);
$fields[] = $row;
$found_index = count($fields) - 1;
} else {
$fields[$found_index]['value'] .= ', ' . self::getOptionValue($row['fld_id'], $row['value']);
$fields[$found_index]['selected_cfo_id'][] = $row['value'];
}
// add the select option to the list of values if it isn't on the list (useful for fields with active and non-active items)
if ($original_value !== null && !in_array($original_value, $fields[$found_index]['field_options'])) {
$fields[$found_index]['field_options'][$original_value] = self::getOptionValue($row['fld_id'], $original_value);
}
} else {
$row['value'] = $row[self::getDBValueFieldNameByType($row['fld_type'])];
$fields[] = $row;
}
}
foreach ($fields as $key => $field) {
$backend = self::getBackend($field['fld_id']);
if (is_object($backend) && is_subclass_of($backend, 'Dynamic_Custom_Field_Backend')) {
$fields[$key]['dynamic_options'] = $backend->getStructuredData();
$fields[$key]['controlling_field_id'] = $backend->getControllingCustomFieldID();
$fields[$key]['controlling_field_name'] = $backend->getControllingCustomFieldName();
$fields[$key]['hide_when_no_options'] = $backend->hideWhenNoOptions();
$fields[$key]['lookup_method'] = $backend->lookupMethod();
}
// check if the backend implements "isRequired"
if (is_object($backend) && method_exists($backend, 'isRequired')) {
$fields[$key]['fld_report_form_required'] = $backend->isRequired($fields[$key]['fld_id'], 'report', $iss_id);
$fields[$key]['fld_anonymous_form_required'] = $backend->isRequired($fields[$key]['fld_id'], 'anonymous', $iss_id);
$fields[$key]['fld_close_form_required'] = $backend->isRequired($fields[$key]['fld_id'], 'close', $iss_id);
}
if (is_object($backend) && method_exists($backend, 'getValidationJS')) {
$fields[$key]['validation_js'] = $backend->getValidationJS($fields[$key]['fld_id'], $form_type, $iss_id);
} else {
$fields[$key]['validation_js'] = '';
}
}
return $fields;
}
/**
* Method used to update a support email account details.
*
* @access public
* @return integer 1 if the update worked, -1 otherwise
*/
function update()
{
global $HTTP_POST_VARS;
if (empty($HTTP_POST_VARS["get_only_new"])) {
$HTTP_POST_VARS["get_only_new"] = 0;
}
if (empty($HTTP_POST_VARS["leave_copy"])) {
$HTTP_POST_VARS["leave_copy"] = 0;
}
if (empty($HTTP_POST_VARS["use_routing"])) {
$HTTP_POST_VARS["use_routing"] = 0;
} elseif ($HTTP_POST_VARS['use_routing'] == 1) {
// if an account will be used for routing, you can't leave the message on the server
$HTTP_POST_VARS['leave_copy'] = 0;
}
$stmt = "UPDATE\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "email_account\n SET\n ema_prj_id=" . Misc::escapeInteger($HTTP_POST_VARS["project"]) . ",\n ema_type='" . Misc::escapeString($HTTP_POST_VARS["type"]) . "',\n ema_hostname='" . Misc::escapeString($HTTP_POST_VARS["hostname"]) . "',\n ema_port='" . Misc::escapeString($HTTP_POST_VARS["port"]) . "',\n ema_folder='" . Misc::escapeString(@$HTTP_POST_VARS["folder"]) . "',\n ema_username='******',\n ema_password='******',\n ema_get_only_new=" . Misc::escapeInteger($HTTP_POST_VARS["get_only_new"]) . ",\n ema_leave_copy=" . Misc::escapeInteger($HTTP_POST_VARS["leave_copy"]) . ",\n ema_use_routing=" . Misc::escapeInteger($HTTP_POST_VARS["use_routing"]) . "\n WHERE\n ema_id=" . $HTTP_POST_VARS["id"];
$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;
}
}
function getUser($name)
{
$sql = "SELECT\n usr_id\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "user\n WHERE\n usr_full_name = '" . trim(Misc::escapeString($name)) . "'";
return $GLOBALS["db_api"]->dbh->getOne($sql);
}
/**
* Method used to prepare a set of fields and values for a boolean search
*
* @access public
* @param string $field The field name
* @param string $value The value for that field
* @return string The prepared boolean search string
*/
function prepareBooleanSearch($field, $value)
{
$boolean = array();
$pieces = explode(" ", $value);
for ($i = 0; $i < count($pieces); $i++) {
$boolean[] = "{$field} LIKE '%" . Misc::escapeString($pieces[$i]) . "%'";
}
return "(" . implode(" OR ", $boolean) . ")";
}
/**
* 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;
}
// | Free Software Foundation, Inc. |
// | 51 Franklin Street, Suite 330 |
// | Boston, MA 02110-1301, USA. |
// +----------------------------------------------------------------------+
// | Authors: Bryan Alsdorf <*****@*****.**> |
// | Authors: Elan Ruusamäe <*****@*****.**> |
// +----------------------------------------------------------------------+
require_once dirname(__FILE__) . '/../init.php';
Auth::checkAuthentication(APP_COOKIE);
$usr_id = Auth::getUserID();
/*
* This page is used to return a single content to the expandable table using
* httpClient library or jQuery.
*/
$valid_functions = array('email' => 'getEmail', 'note' => 'getNote', 'draft' => 'getDraft', 'phone' => 'getPhoneSupport', 'mailqueue' => 'getMailQueue', 'description' => 'getIssueDescription');
$action = Misc::escapeString($_REQUEST['action']);
if (in_array($action, array_keys($valid_functions))) {
$method = $valid_functions[$action];
$res = $method($_REQUEST['list_id']);
} else {
$res = 'ERROR: Unable to call function ' . htmlspecialchars($action);
}
$callback = !empty($_GET['callback']) ? $_GET['callback'] : null;
// convert to wanted format
$res = array('ec_id' => $_REQUEST['ec_id'], 'list_id' => $_REQUEST['list_id'], 'message' => $res);
if ($callback) {
echo $callback, '(', json_encode($res), ')';
} else {
echo $res['message'];
}
exit;
/**
* Method used to update the details of a specific reminder.
*
* @access public
* @return integer 1 if the update worked, -1 or -2 otherwise
*/
function update()
{
global $HTTP_POST_VARS;
$stmt = "UPDATE\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "reminder_level\n SET\n rem_last_updated_date='" . Date_API::getCurrentDateGMT() . "',\n rem_rank=" . Misc::escapeInteger($HTTP_POST_VARS['rank']) . ",\n rem_title='" . Misc::escapeString($HTTP_POST_VARS['title']) . "',\n rem_prj_id=" . Misc::escapeInteger($HTTP_POST_VARS['project']) . ",\n rem_skip_weekend=" . Misc::escapeInteger($HTTP_POST_VARS['skip_weekend']) . "\n WHERE\n rem_id=" . Misc::escapeInteger($HTTP_POST_VARS['id']);
$res = $GLOBALS["db_api"]->dbh->query($stmt);
if (PEAR::isError($res)) {
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
return -1;
} else {
Reminder::removeAllAssociations($HTTP_POST_VARS['id']);
// map the reminder requirements now
if (@$HTTP_POST_VARS['reminder_type'] == 'support_level' && count($HTTP_POST_VARS['support_levels']) > 0) {
for ($i = 0; $i < count($HTTP_POST_VARS['support_levels']); $i++) {
Reminder::addSupportLevelAssociation($HTTP_POST_VARS['id'], $HTTP_POST_VARS['support_levels'][$i]);
}
} elseif (@$HTTP_POST_VARS['reminder_type'] == 'issue' && count($HTTP_POST_VARS['issues']) > 0) {
for ($i = 0; $i < count($HTTP_POST_VARS['issues']); $i++) {
Reminder::addIssueAssociation($HTTP_POST_VARS['id'], $HTTP_POST_VARS['issues'][$i]);
}
} elseif (@$HTTP_POST_VARS['reminder_type'] == 'customer' && count($HTTP_POST_VARS['customers']) > 0) {
for ($i = 0; $i < count($HTTP_POST_VARS['customers']); $i++) {
Reminder::addCustomerAssociation($HTTP_POST_VARS['id'], $HTTP_POST_VARS['customers'][$i]);
}
} elseif (@$HTTP_POST_VARS['reminder_type'] == 'all_issues') {
Reminder::associateAllIssues($HTTP_POST_VARS['id']);
}
if (@$HTTP_POST_VARS['check_priority'] == 'yes' && count($HTTP_POST_VARS['priorities']) > 0) {
for ($i = 0; $i < count($HTTP_POST_VARS['priorities']); $i++) {
Reminder::addPriorityAssociation($HTTP_POST_VARS['id'], $HTTP_POST_VARS['priorities'][$i]);
}
}
return 1;
}
}
/**
* Method used to update the details of a specific reminder action.
*
* @access public
* @return integer 1 if the update worked, -1 or -2 otherwise
*/
function update()
{
global $HTTP_POST_VARS;
$stmt = "UPDATE\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "reminder_action\n SET\n rma_last_updated_date='" . Date_API::getCurrentDateGMT() . "',\n rma_rank='" . Misc::escapeInteger($HTTP_POST_VARS['rank']) . "',\n rma_title='" . Misc::escapeString($HTTP_POST_VARS['title']) . "',\n rma_rmt_id=" . Misc::escapeInteger($HTTP_POST_VARS['type']) . ",\n rma_alert_irc=" . Misc::escapeInteger($HTTP_POST_VARS['alert_irc']) . ",\n rma_alert_group_leader=" . Misc::escapeInteger($HTTP_POST_VARS['alert_group_leader']) . ",\n rma_boilerplate='" . Misc::escapeString($HTTP_POST_VARS['boilerplate']) . "'\n WHERE\n rma_id=" . Misc::escapeInteger($HTTP_POST_VARS['id']);
$res = $GLOBALS["db_api"]->dbh->query($stmt);
if (PEAR::isError($res)) {
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
return -1;
} else {
// remove any user list associated with this reminder action
Reminder_Action::clearActionUserList($HTTP_POST_VARS['id']);
// add the user list back in, if appropriate
if (Reminder_Action::isUserList($HTTP_POST_VARS['type'])) {
Reminder_Action::associateUserList($HTTP_POST_VARS['id'], $HTTP_POST_VARS['user_list']);
}
return 1;
}
}
/**
* Method used to update the details of a specific reminder condition.
*
* @access public
* @return integer 1 if the update worked, -1 or -2 otherwise
*/
function update()
{
global $HTTP_POST_VARS;
$stmt = "UPDATE\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "reminder_level_condition\n SET\n rlc_last_updated_date='" . Date_API::getCurrentDateGMT() . "',\n rlc_rmf_id=" . Misc::escapeInteger($HTTP_POST_VARS['field']) . ",\n rlc_rmo_id=" . Misc::escapeInteger($HTTP_POST_VARS['operator']) . ",\n rlc_value='" . Misc::escapeString(@$HTTP_POST_VARS['value']) . "',\n rlc_comparison_rmf_id = '" . Misc::escapeInteger(@$HTTP_POST_VARS['comparison_field']) . "'\n WHERE\n rlc_id=" . Misc::escapeInteger($HTTP_POST_VARS['id']);
$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;
}
}
/**
* 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;
}
/**
* Returns the number of issues for the specified user that are currently set to the specified status(es).
*
* @access public
* @param integer $usr_id The id of the user.
* @param date $start The start date
* @param date $end The end date
* @param array $statuses An array of status abreviations to return counts for.
* @return array An array containing the number of issues for the user set tothe specified statuses.
*/
function getTouchedIssueCountByStatus($usr_id, $start, $end, $statuses = false)
{
$stmt = "SELECT\n sta_title,\n count(DISTINCT iss_id) as total\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue,\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "status,\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue_history\n WHERE\n his_iss_id = iss_id AND\n iss_sta_id = sta_id AND\n his_usr_id = " . Misc::escapeInteger($usr_id) . " AND\n his_created_date BETWEEN '" . Misc::escapeString($start) . "' AND '" . Misc::escapeString($end) . "'";
if ($statuses != false) {
$stmt .= " AND\n (\n sta_abbreviation IN('" . join("','", $statuses) . "') OR\n sta_is_closed = 1\n )";
}
$stmt .= "\n GROUP BY\n sta_title\n ORDER BY\n sta_rank";
$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 {
return $res;
}
}
/**
* Checks if a message already is downloaded..
*
* @access public
* @param string $message_id The Message-ID header
* @return boolean
*/
function exists($message_id)
{
$sql = "SELECT\n count(*)\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "note\n WHERE\n not_message_id ='" . Misc::escapeString($message_id) . "'";
$res = $GLOBALS['db_api']->dbh->getOne($sql);
if (PEAR::isError($res)) {
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
return false;
}
if ($res > 0) {
return true;
} else {
return false;
}
}
/**
* Method used to associate a recipient with a given email
* draft response.
*
* @access public
* @param integer $emd_id The email draft ID
* @param string $email The recipient's email address
* @param boolean $is_cc Whether this recipient is in the Cc list for the given draft
* @return boolean
*/
function addEmailRecipient($emd_id, $email, $is_cc)
{
$emd_id = Misc::escapeInteger($emd_id);
if (!$is_cc) {
$is_cc = 0;
} else {
$is_cc = 1;
}
$email = trim($email);
$stmt = "INSERT INTO\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "email_draft_recipient\n (\n edr_emd_id,\n edr_is_cc,\n edr_email\n ) VALUES (\n {$emd_id},\n {$is_cc},\n '" . Misc::escapeString($email) . "'\n )";
$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 save the changes made to an existing custom
* filter, or to create a new custom filter.
*
* @return integer 1 if the update worked properly, any other value otherwise
*/
public static function save()
{
$cst_id = self::getFilterID($_POST['title']);
// loop through all available date fields and prepare the values for the sql query
$date_fields = array('created_date', 'updated_date', 'last_response_date', 'first_response_date', 'closed_date');
/**
* @var $created_date
* @var $created_date_filter_type
* @var $created_date_end
* @var $updated_date
* @var $updated_date_filter_type
* @var $updated_date_end
* @var $last_response_date
* @var $last_response_date_filter_type
* @var $last_response_date_end
* @var $first_response_date
* @var $first_response_date_filter_type
* @var $first_response_date_end
* @var $closed_date
* @var $closed_date_filter_type
* @var $closed_date_end
*/
foreach ($date_fields as $field_name) {
$date_var = $field_name;
$filter_type_var = $field_name . '_filter_type';
$date_end_var = $field_name . '_end';
if (@$_POST['filter'][$field_name] == 'yes') {
${$date_var} = "'" . Misc::escapeString($_POST[$field_name]['Year'] . '-' . $_POST[$field_name]['Month'] . '-' . $_POST[$field_name]['Day']) . "'";
${$filter_type_var} = "'" . $_POST[$field_name]['filter_type'] . "'";
if (${$filter_type_var} == "'between'") {
${$date_end_var} = "'" . Misc::escapeString($_POST[$date_end_var]['Year'] . '-' . $_POST[$date_end_var]['Month'] . '-' . $_POST[$date_end_var]['Day']) . "'";
} elseif (${$filter_type_var} == "'null'" || ${$filter_type_var} == "'in_past'") {
${$date_var} = null;
${$date_end_var} = null;
} else {
${$date_end_var} = null;
}
} else {
${$date_var} = null;
${$filter_type_var} = null;
${$date_end_var} = null;
}
}
// save custom fields to search
if (is_array($_POST['custom_field']) && count($_POST['custom_field']) > 0) {
foreach ($_POST['custom_field'] as $fld_id => $search_value) {
if (empty($search_value)) {
unset($_POST[$fld_id]);
}
}
$custom_field_string = serialize($_POST['custom_field']);
} else {
$custom_field_string = '';
}
if (empty($_POST['is_global'])) {
$is_global_filter = 0;
} else {
$is_global_filter = $_POST['is_global'];
}
if ($cst_id != 0) {
$stmt = 'UPDATE
{{%custom_filter}}
SET
cst_iss_pri_id=?,
cst_iss_sev_id=?,
cst_keywords=?,
cst_users=?,
cst_reporter=?,
cst_iss_sta_id=?,
cst_iss_pre_id=?,
cst_iss_prc_id=?,
cst_pro_id=?,
cst_rows=?,
cst_sort_by=?,
cst_sort_order=?,
cst_hide_closed=?,
cst_show_authorized=?,
cst_show_notification_list=?,
cst_created_date=?,
cst_created_date_filter_type=?,
cst_created_date_time_period=?,
cst_created_date_end=?,
cst_updated_date=?,
cst_updated_date_filter_type=?,
cst_updated_date_time_period=?,
cst_updated_date_end=?,
cst_last_response_date=?,
cst_last_response_date_filter_type=?,
cst_last_response_date_time_period=?,
cst_last_response_date_end=?,
cst_first_response_date=?,
cst_first_response_date_filter_type=?,
cst_first_response_date_time_period=?,
cst_first_response_date_end=?,
cst_closed_date=?,
cst_closed_date_filter_type=?,
cst_closed_date_time_period=?,
cst_closed_date_end=?,
cst_is_global=?,
cst_search_type=?,
cst_custom_field=?
WHERE
cst_id=?';
$params = array(@$_POST['priority'], @$_POST['severity'], $_POST['keywords'], $_POST['users'], $_POST['reporter'], $_POST['status'], @$_POST['release'], @$_POST['category'], @$_POST['product'], $_POST['rows'], $_POST['sort_by'], $_POST['sort_order'], @$_POST['hide_closed'], @$_POST['show_authorized_issues'], @$_POST['show_notification_list_issues'], $created_date, $created_date_filter_type, @$_REQUEST['created_date']['time_period'], $created_date_end, $updated_date, $updated_date_filter_type, @$_REQUEST['updated_date']['time_period'], $updated_date_end, $last_response_date, $last_response_date_filter_type, @$_REQUEST['last_response_date']['time_period'], $last_response_date_end, $first_response_date, $first_response_date_filter_type, @$_REQUEST['first_response_date']['time_period'], $first_response_date_end, $closed_date, $closed_date_filter_type, @$_REQUEST['closed_date']['time_period'], $closed_date_end, $is_global_filter, $_POST['search_type'], $custom_field_string, $cst_id);
} else {
$stmt = 'INSERT INTO
{{%custom_filter}}
(
cst_usr_id,
cst_prj_id,
cst_title,
cst_iss_pri_id,
cst_iss_sev_id,
cst_keywords,
cst_users,
cst_reporter,
cst_iss_sta_id,
cst_iss_pre_id,
cst_iss_prc_id,
cst_pro_id,
cst_rows,
cst_sort_by,
cst_sort_order,
cst_hide_closed,
cst_show_authorized,
cst_show_notification_list,
cst_created_date,
cst_created_date_filter_type,
cst_created_date_time_period,
cst_created_date_end,
cst_updated_date,
cst_updated_date_filter_type,
cst_updated_date_time_period,
cst_updated_date_end,
cst_last_response_date,
cst_last_response_date_filter_type,
cst_last_response_date_time_period,
cst_last_response_date_end,
cst_first_response_date,
cst_first_response_date_filter_type,
cst_first_response_date_time_period,
cst_first_response_date_end,
cst_closed_date,
cst_closed_date_filter_type,
cst_closed_date_time_period,
cst_closed_date_end,
cst_is_global,
cst_search_type,
cst_custom_field
) VALUES (
?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?
)';
$params = array(Auth::getUserID(), Auth::getCurrentProject(), $_POST['title'], @$_POST['priority'], @$_POST['severity'], $_POST['keywords'], $_POST['users'], $_POST['reporter'], $_POST['status'], @$_POST['release'], @$_POST['category'], @$_POST['product'], $_POST['rows'], $_POST['sort_by'], $_POST['sort_order'], @$_POST['hide_closed'], @$_POST['show_authorized_issues'], @$_POST['show_notification_list_issues'], $created_date, $created_date_filter_type, @$_REQUEST['created_date']['time_period'], $created_date_end, $updated_date, $updated_date_filter_type, @$_REQUEST['updated_date']['time_period'], $updated_date_end, $last_response_date, $last_response_date_filter_type, @$_REQUEST['response_date']['time_period'], $last_response_date_end, $first_response_date, $first_response_date_filter_type, @$_REQUEST['first_response_date']['time_period'], $first_response_date_end, $closed_date, $closed_date_filter_type, @$_REQUEST['closed_date']['time_period'], $closed_date_end, $is_global_filter, $_POST['search_type'], $custom_field_string);
}
try {
DB_Helper::getInstance()->query($stmt, $params);
} catch (DbException $e) {
return -1;
}
return 1;
}
/**
* 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;
}
function isCorrectPassword($email, $password)
{
$stmt = "SELECT\r\n\t\t\t\t\ten_username,\r\n en_password\r\n FROM\r\n " . ETEL_USER_TABLE_NOSUB . "\r\n WHERE\r\n en_email='" . Misc::escapeString($email) . "'";
$info = $GLOBALS["db_api"]->dbh->getRow($stmt);
if (PEAR::isError($info)) {
Error_Handler::logError(array($passwd->getMessage(), $passwd->getDebugInfo()), __FILE__, __LINE__);
return false;
} else {
if ($info[1] != Auth::hashPassword($info[0] . $password)) {
return false;
} else {
return true;
}
}
}
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 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));
}
/**
* Returns the number of emails sent by a user in a time range.
*
* @access public
* @param string $usr_id The ID of the user
* @param integer $start The timestamp of the start date
* @param integer $end The timestanp of the end date
* @param boolean $associated If this should return emails associated with issues or non associated emails.
* @return integer The number of emails sent by the user.
*/
function getSentEmailCountByUser($usr_id, $start, $end, $associated)
{
$usr_info = User::getNameEmail($usr_id);
$stmt = "SELECT\n COUNT(sup_id)\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "support_email\n WHERE\n sup_date BETWEEN '" . Misc::escapeString($start) . "' AND '" . Misc::escapeString($end) . "' AND\n sup_from LIKE '%" . Misc::escapeString($usr_info["usr_email"]) . "%' AND\n sup_iss_id ";
if ($associated == true) {
$stmt .= "!= 0";
} else {
$stmt .= "= 0";
}
$res = $GLOBALS["db_api"]->dbh->getOne($stmt);
if (PEAR::isError($res)) {
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
return "";
}
return $res;
}
/**
* Method used to update a round robin entry in the system.
*
* @access public
* @return integer 1 if the update worked, -1 otherwise
*/
function update()
{
global $HTTP_POST_VARS;
$blackout_start = $HTTP_POST_VARS['blackout_start']['Hour'] . ':' . $HTTP_POST_VARS['blackout_start']['Minute'] . ':00';
$blackout_end = $HTTP_POST_VARS['blackout_end']['Hour'] . ':' . $HTTP_POST_VARS['blackout_end']['Minute'] . ':00';
$stmt = "UPDATE\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "project_round_robin\n SET\n prr_prj_id=" . Misc::escapeInteger($HTTP_POST_VARS["project"]) . ",\n prr_blackout_start='" . Misc::escapeString($blackout_start) . "',\n prr_blackout_end='" . Misc::escapeString($blackout_end) . "'\n WHERE\n prr_id=" . Misc::escapeInteger($HTTP_POST_VARS["id"]);
$res = $GLOBALS["db_api"]->dbh->query($stmt);
if (PEAR::isError($res)) {
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
return -1;
} else {
// remove all of the associations with users, then add them all again
Round_Robin::removeUserAssociations($HTTP_POST_VARS['id']);
foreach ($HTTP_POST_VARS['users'] as $usr_id) {
Round_Robin::addUserAssociation($HTTP_POST_VARS['id'], $usr_id);
}
return 1;
}
}
/**
* Method used to update a canned email response in the system.
*
* @access public
* @return integer 1 if the update worked, -1 otherwise
*/
function update()
{
global $HTTP_POST_VARS;
$HTTP_POST_VARS['id'] = Misc::escapeInteger($HTTP_POST_VARS['id']);
if (Validation::isWhitespace($HTTP_POST_VARS["title"])) {
return -2;
}
$stmt = "UPDATE\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "email_response\n SET\n ere_title='" . Misc::escapeString($HTTP_POST_VARS["title"]) . "',\n ere_response_body='" . Misc::escapeString($HTTP_POST_VARS["response_body"]) . "'\n WHERE\n ere_id=" . $HTTP_POST_VARS["id"];
$res = $GLOBALS["db_api"]->dbh->query($stmt);
if (PEAR::isError($res)) {
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
return -1;
} else {
// remove all of the associations with projects, then add them all again
Email_Response::removeProjectAssociations($HTTP_POST_VARS['id']);
foreach ($HTTP_POST_VARS['projects'] as $prj_id) {
Email_Response::addProjectAssociation($HTTP_POST_VARS['id'], $prj_id);
}
return 1;
}
}
/**
* Sets the minimum role needed to view a specific field on the issue creation form.
*
* @access public
* @param integer $prj_id The project ID.
* @param array $settings An array of fields and role is required to view them.
* @return integer 1 if the update worked, -1 otherwise.
*/
function updateFieldDisplaySettings($prj_id, $settings)
{
// delete current settings
$stmt = "DELETE 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->query($stmt);
if (PEAR::isError($res)) {
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
return -1;
}
// insert new values
foreach ($settings as $field => $min_role) {
$stmt = "INSERT INTO\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "project_field_display\n (\n pfd_prj_id,\n pfd_field,\n pfd_min_role\n ) VALUES (\n " . Misc::escapeInteger($prj_id) . ",\n '" . Misc::escapeString($field) . "',\n " . Misc::escapeInteger($min_role) . "\n )";
$res = $GLOBALS["db_api"]->dbh->query($stmt);
if (PEAR::isError($res)) {
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
return -1;
}
}
return 1;
}
/**
* Method used to add a new release by using the administrative
* interface of the system.
*
* @access public
* @return integer 1 if the update worked, -1 or -2 otherwise
*/
function insert()
{
global $HTTP_POST_VARS;
if (Validation::isWhitespace($HTTP_POST_VARS["title"])) {
return -2;
}
$scheduled_date = $HTTP_POST_VARS["scheduled_date"]["Year"] . "-" . $HTTP_POST_VARS["scheduled_date"]["Month"] . "-" . $HTTP_POST_VARS["scheduled_date"]["Day"];
$stmt = "INSERT INTO\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "project_release\n (\n pre_prj_id,\n pre_title,\n pre_scheduled_date,\n pre_status\n ) VALUES (\n " . Misc::escapeInteger($HTTP_POST_VARS["prj_id"]) . ",\n '" . Misc::escapeString($HTTP_POST_VARS["title"]) . "',\n '" . Misc::escapeString($scheduled_date) . "',\n '" . Misc::escapeString($HTTP_POST_VARS["status"]) . "'\n )";
$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;
}
}
/**
* Method used to get the previous and next issues that are available
* according to the current search parameters.
*
* @param integer $issue_id The issue ID
* @param array $options The search parameters
* @return array The list of issues
*/
public static function getSides($issue_id, $options)
{
$usr_id = Auth::getUserID();
$role_id = Auth::getCurrentRole();
$usr_details = User::getDetails($usr_id);
$stmt = 'SELECT
iss_id,
' . self::getLastActionFields() . '
FROM
(
{{%issue}},
{{%user}}';
// join custom fields if we are searching by 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);
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) {
$stmt .= ",\n {{%issue_custom_field}} as cf" . $fld_id . '_' . $cfo_id . "\n";
}
} else {
$stmt .= ",\n {{%issue_custom_field}} as cf" . $fld_id . "\n";
}
}
}
$stmt .= ')';
// check for the custom fields we want to sort by
if (strstr($options['sort_by'], 'custom_field') !== false) {
$fld_id = str_replace('custom_field_', '', $options['sort_by']);
$stmt .= "\n LEFT JOIN {{%issue_custom_field}} as cf_sort\n ON\n (cf_sort.icf_iss_id = iss_id AND cf_sort.icf_fld_id = {$fld_id}) \n";
}
if (!empty($options['users']) || @$options['sort_by'] == 'isu_usr_id') {
$stmt .= '
LEFT JOIN
{{%issue_user}}
ON
isu_iss_id=iss_id';
}
if (!empty($options['show_authorized_issues']) || $role_id == User::ROLE_REPORTER && Project::getSegregateReporters(Auth::getCurrentProject())) {
$stmt .= '
LEFT JOIN
{{%issue_user_replier}}
ON
iur_iss_id=iss_id';
}
if (!empty($options['show_notification_list_issues'])) {
$stmt .= '
LEFT JOIN
{{%subscription}}
ON
sub_iss_id=iss_id';
}
if (!empty($options['product'])) {
$stmt .= '
LEFT JOIN
{{%issue_product_version}}
ON
ipv_iss_id=iss_id';
}
if (@$options['sort_by'] == 'pre_scheduled_date') {
$stmt .= '
LEFT JOIN
{{%project_release}}
ON
iss_pre_id = pre_id';
}
if (@$options['sort_by'] == 'prc_title') {
$stmt .= '
LEFT JOIN
{{%project_category}}
ON
iss_prc_id = prc_id';
}
if (!empty($usr_details['usr_par_code'])) {
// restrict partners
$stmt .= '
LEFT JOIN
{{%issue_partner}}
ON
ipa_iss_id=iss_id';
}
$stmt .= '
LEFT JOIN
{{%status}}
ON
iss_sta_id=sta_id
LEFT JOIN
{{%project_priority}}
ON
iss_pri_id=pri_id
LEFT JOIN
{{%project_severity}}
ON
iss_sev_id=sev_id
WHERE
iss_prj_id=' . Auth::getCurrentProject();
$stmt .= Search::buildWhereClause($options);
if (strstr($options['sort_by'], 'custom_field') !== false) {
$fld_details = Custom_Field::getDetails($fld_id);
$sort_by = 'cf_sort.' . Custom_Field::getDBValueFieldNameByType($fld_details['fld_type']);
} else {
$sort_by = Misc::escapeString($options['sort_by']);
}
$stmt .= '
GROUP BY
iss_id
ORDER BY
' . $sort_by . ' ' . Misc::escapeString($options['sort_order']) . ',
iss_id DESC';
try {
$res = DB_Helper::getInstance()->getColumn($stmt);
} catch (DbException $e) {
return '';
}
$index = array_search($issue_id, $res);
if (!empty($res[$index + 1])) {
$next = $res[$index + 1];
}
if (!empty($res[$index - 1])) {
$previous = $res[$index - 1];
}
return array('next' => @$next, 'previous' => @$previous);
}
*/
include_once "../../../config.inc.php";
include_once APP_INC_PATH . "db_access.php";
include_once APP_INC_PATH . "class.issue.php";
$stmt = "SELECT\n iss_id\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue\n WHERE\n iss_root_message_id IS NULL";
$issues = $GLOBALS["db_api"]->dbh->getCol($stmt);
foreach ($issues as $issue_id) {
$sql = "SELECT\n sup_message_id\n FROM\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "support_email\n WHERE\n sup_iss_id = {$issue_id}\n ORDER BY\n sup_date ASC\n LIMIT 1";
$res = $GLOBALS["db_api"]->dbh->getOne($sql);
if (PEAR::isError($res)) {
echo "<pre>";
print_r($res);
echo "</pre>";
exit;
}
if (empty($res)) {
$msg_id = Mail_API::generateMessageID();
} else {
$msg_id = $res;
}
$sql = "UPDATE\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "issue\n SET\n iss_root_message_id = '" . Misc::escapeString($msg_id) . "'\n WHERE\n iss_id = {$issue_id}";
$res = $GLOBALS["db_api"]->dbh->query($sql);
if (PEAR::isError($res)) {
echo "<pre>";
print_r($res);
echo "</pre>";
exit;
}
}
?>
done
/**
* Method used to get all open issues and group them by user.
*
* @param integer $prj_id The project ID
* @param array $users
* @param array $status
* @param string $before_date
* @param string $after_date
* @param string $sort_order
* @return array The list of issues
*/
public static function getStalledIssuesByUser($prj_id, $users, $status, $before_date, $after_date, $sort_order)
{
$prj_id = (int) $prj_id;
$ts = time();
$before_ts = strtotime($before_date);
$after_ts = strtotime($after_date);
// split groups out of users array
$groups = array();
if (count($users) > 0) {
foreach ($users as $key => $value) {
if (substr($value, 0, 3) == 'grp') {
$groups[] = substr($value, 4);
unset($users[$key]);
}
}
}
$stmt = 'SELECT
usr_full_name,
iss_id,
iss_summary,
sta_title,
iss_sta_id,
iss_created_date,
iss_updated_date,
iss_last_response_date,
sta_color,
iss_private
FROM
(
{{%issue}},
{{%issue_user}},
{{%user}}
)
LEFT JOIN
{{%status}}
ON
iss_sta_id=sta_id
WHERE
sta_is_closed=0 AND
iss_prj_id=? AND
iss_id=isu_iss_id AND
isu_usr_id=usr_id AND
UNIX_TIMESTAMP(iss_last_response_date) < ? AND
UNIX_TIMESTAMP(iss_last_response_date) > ?';
$params = array($prj_id, $before_ts, $after_ts);
if ($users) {
$ids = (array) $users;
$list = DB_Helper::buildList($ids);
$params = array_merge($params, $ids);
$stmt .= " AND\nisu_usr_id IN({$list})";
}
if ($groups) {
$ids = (array) $groups;
$list = DB_Helper::buildList($ids);
$params = array_merge($params, $ids);
$stmt .= " AND\nusr_grp_id IN({$list})";
}
if ($status) {
$ids = (array) $status;
$list = DB_Helper::buildList($ids);
$params = array_merge($params, $ids);
$stmt .= " AND\niss_sta_id IN({$list})";
}
$sort_order = Misc::escapeString($sort_order);
$stmt .= '
ORDER BY
usr_full_name,
iss_last_response_date ' . $sort_order;
try {
$res = DB_Helper::getInstance()->getAll($stmt, $params);
} catch (DbException $e) {
return '';
}
Time_Tracking::fillTimeSpentByIssues($res);
$issues = array();
foreach ($res as &$row) {
if (empty($row['iss_updated_date'])) {
$row['iss_updated_date'] = $row['iss_created_date'];
}
if (empty($row['iss_last_response_date'])) {
$row['iss_last_response_date'] = $row['iss_created_date'];
}
$updated_date_ts = Date_Helper::getUnixTimestamp($row['iss_updated_date'], Date_Helper::getDefaultTimezone());
$last_response_ts = Date_Helper::getUnixTimestamp($row['iss_last_response_date'], Date_Helper::getDefaultTimezone());
$issues[$row['usr_full_name']][$row['iss_id']] = array('iss_summary' => $row['iss_summary'], 'sta_title' => $row['sta_title'], 'iss_created_date' => Date_Helper::getFormattedDate($row['iss_created_date']), 'iss_last_response_date' => Date_Helper::getFormattedDate($row['iss_last_response_date']), 'time_spent' => Misc::getFormattedTime($row['time_spent']), 'status_color' => $row['sta_color'], 'last_update' => Date_Helper::getFormattedDateDiff($ts, $updated_date_ts), 'last_email_response' => Date_Helper::getFormattedDateDiff($ts, $last_response_ts));
}
return $issues;
}
/**
* Method used to add a new category to the application.
*
* @access public
* @return integer 1 if the update worked properly, any other value otherwise
*/
function insert()
{
global $HTTP_POST_VARS;
if (Validation::isWhitespace($HTTP_POST_VARS["title"])) {
return -2;
}
$stmt = "INSERT INTO\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "project_category\n (\n prc_prj_id,\n prc_title\n ) VALUES (\n " . Misc::escapeInteger($HTTP_POST_VARS["prj_id"]) . ",\n '" . Misc::escapeString($HTTP_POST_VARS["title"]) . "'\n )";
$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;
}
}
/**
* Method used to add a FAQ entry to the system.
*
* @access public
* @return integer 1 if the insert worked, -1 otherwise
*/
function insert()
{
global $HTTP_POST_VARS;
if (Validation::isWhitespace($HTTP_POST_VARS["title"])) {
return -2;
}
if (Validation::isWhitespace($HTTP_POST_VARS["message"])) {
return -3;
}
$stmt = "INSERT INTO\n " . APP_DEFAULT_DB . "." . APP_TABLE_PREFIX . "faq\n (\n faq_prj_id,\n faq_usr_id,\n faq_created_date,\n faq_title,\n faq_message,\n faq_rank\n ) VALUES (\n " . $HTTP_POST_VARS['project'] . ",\n " . Auth::getUserID() . ",\n '" . Date_API::getCurrentDateGMT() . "',\n '" . Misc::escapeString($HTTP_POST_VARS["title"]) . "',\n '" . Misc::escapeString($HTTP_POST_VARS["message"]) . "',\n " . $HTTP_POST_VARS['rank'] . "\n )";
$res = $GLOBALS["db_api"]->dbh->query($stmt);
if (PEAR::isError($res)) {
Error_Handler::logError(array($res->getMessage(), $res->getDebugInfo()), __FILE__, __LINE__);
return -1;
} else {
$new_faq_id = $GLOBALS["db_api"]->get_last_insert_id();
if (Customer::doesBackendUseSupportLevels(Misc::escapeInteger($HTTP_POST_VARS['project']))) {
// now populate the faq-support level mapping table
foreach ($HTTP_POST_VARS['support_levels'] as $support_level_id) {
FAQ::addSupportLevelAssociation($new_faq_id, $support_level_id);
}
}
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;
}
