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