/**
* Returns the total number of rows for a specific query. It is used to
* calculate the total number of pages of data.
*
* @param string $stmt The SQL statement
* @return int The total number of rows
*/
public static function getTotalRows($stmt)
{
$stmt = str_replace("\n", '', $stmt);
$stmt = str_replace("\r", '', $stmt);
if (stristr($stmt, 'GROUP BY')) {
// go the extra mile and try to use the grouped by column in the count() call
preg_match("/.*\\s+GROUP BY\\s+(\\w*)\\s+.*/i", $stmt, $matches);
if (!empty($matches[1])) {
$stmt = preg_replace('/SELECT (.*?) FROM /si', 'SELECT COUNT(DISTINCT ' . $matches[1] . ') AS total_rows FROM ', $stmt);
}
} else {
$stmt = preg_replace('/SELECT (.*?) FROM /si', 'SELECT COUNT(*) AS total_rows FROM ', $stmt);
}
// remove any order by clauses
$stmt = preg_replace("/(.*)(ORDER BY\\s+\\w+\\s+\\w+)(?:,\\s+\\w+\\s+\\w+)*(.*)/si", '\\1\\3', $stmt);
try {
$rows = DB_Helper::getInstance()->getAll($stmt);
} catch (DbException $e) {
return 0;
}
if (empty($rows)) {
return 0;
}
// the query above works only if there is no left join or any other complex queries
if (count($rows) == 1) {
return $rows[0]['total_rows'];
}
return count($rows);
}
/**
* Modifies an Issue's Reporter.
*
* @param integer $issue_id The id of the issue.
* @param string $fullname The id of the user.
* @param boolean $add_history If this should be logged.
* @return int
*/
public static function update($issue_id, $email, $add_history = true)
{
$email = strtolower(Mail_Helper::getEmailAddress($email));
$usr_id = User::getUserIDByEmail($email, true);
// If no valid user found reset to system account
if (!$usr_id) {
$usr_id = APP_SYSTEM_USER_ID;
}
$sql = 'UPDATE
{{%issue}}
SET
iss_usr_id = ?
WHERE
iss_id = ?';
try {
DB_Helper::getInstance()->query($sql, array($usr_id, $issue_id));
} catch (DbException $e) {
return -1;
}
if ($add_history) {
// TRANSLATORS: %1: email, %2: full name
$current_usr_id = Auth::getUserID();
History::add($issue_id, $current_usr_id, 'issue_updated', 'Reporter was changed to {email} by {user}', array('email' => $email, 'user' => User::getFullName($current_usr_id)));
}
// Add new user to notification list
if ($usr_id > 0) {
Notification::subscribeEmail($usr_id, $issue_id, $email, Notification::getDefaultActions());
}
return 1;
}
public function __construct($schema_dir)
{
$this->db = DB_Helper::getInstance();
$this->dir = $schema_dir;
$this->config = DB_Helper::getConfig();
$this->table_prefix = $this->config['table_prefix'];
$this->logger = function ($e) {
echo $e, "\n";
};
}
/**
* Returns the name of the workflow backend for the specified project.
*
* @param integer $prj_id The id of the project to lookup.
* @return string The name of the customer backend.
*/
private static function _getBackendNameByProject($prj_id)
{
static $backends;
if (isset($backends[$prj_id])) {
return $backends[$prj_id];
}
$stmt = 'SELECT
prj_id,
prj_workflow_backend
FROM
{{%project}}
ORDER BY
prj_id';
try {
$res = DB_Helper::getInstance()->getPair($stmt);
} catch (DbException $e) {
return '';
}
$backends = $res;
return @$backends[$prj_id];
}
/**
* Called when issue is closed.
*
* @param integer $prj_id The project ID
* @param integer $issue_id The ID of the issue.
* @param boolean $send_notification Whether to send a notification about this action or not
* @param integer $resolution_id The resolution ID
* @param integer $status_id The status ID
* @param string $reason The reason for closing this issue
* @return void
*/
function handleIssueClosed($prj_id, $issue_id, $send_notification, $resolution_id, $status_id, $reason)
{
$sql = "UPDATE\n {{%issue}}\n SET\n iss_percent_complete = '100%'\n WHERE\n iss_id = ?";
try {
DB_Helper::getInstance()->query($sql, array($issue_id));
} catch (DbException $e) {
return;
}
echo "Workflow: handleIssueClosed<br />\n";
}
public static function unlock($usr_id)
{
$stmt = 'UPDATE
{{%user}}
SET
usr_failed_logins = 0
WHERE
usr_id=?';
try {
DB_Helper::getInstance()->query($stmt, array($usr_id));
} catch (DbException $e) {
return false;
}
return true;
}
Auth::checkAuthentication();
if (!Access::canAccessReports(Auth::getUserID())) {
echo 'Invalid role';
exit;
}
// TODO: move this query to some class
$prj_id = Auth::getCurrentProject();
$categories = Category::getAssocList($prj_id);
$statuses = Status::getAssocStatusList($prj_id, true);
$data = array();
foreach ($categories as $cat_id => $cat_title) {
$data[$cat_id] = array('title' => $cat_title, 'statuses' => array());
foreach ($statuses as $sta_id => $sta_title) {
$sql = 'SELECT
count(*)
FROM
{{%issue}}
WHERE
iss_prj_id = ? AND
iss_sta_id = ? AND
iss_prc_id = ?';
try {
$res = DB_Helper::getInstance()->getOne($sql, array($prj_id, $sta_id, $cat_id));
} catch (DbException $e) {
break 2;
}
$data[$cat_id]['statuses'][$sta_id] = array('title' => $sta_title, 'count' => $res);
}
}
$tpl->assign(array('statuses' => $statuses, 'categories' => $categories, 'data' => $data));
$tpl->displayTemplate();
/**
* Checks if a message already is downloaded..
*
* @param string $message_id The Message-ID header
* @return boolean
*/
public static function exists($message_id)
{
$sql = 'SELECT
count(*)
FROM
{{%note}}
WHERE
not_message_id = ?';
try {
$res = DB_Helper::getInstance()->getOne($sql, array($message_id));
} catch (DbException $e) {
return false;
}
if ($res > 0) {
return true;
}
return false;
}
/**
* Method used to add a new resolution by using the administrative
* interface of the system.
*
* @return integer 1 if the update worked, -1 or -2 otherwise
*/
public static function insert()
{
if (Validation::isWhitespace($_POST['title'])) {
return -2;
}
$stmt = 'INSERT INTO
{{%resolution}}
(
res_title,
res_rank,
res_created_date
) VALUES (
?, ?, ?
)';
$params = array($_POST['title'], $_POST['rank'], Date_Helper::getCurrentDateGMT());
try {
DB_Helper::getInstance()->query($stmt, $params);
} catch (DbException $e) {
return -1;
}
return 1;
}
private function reminderActivity()
{
$sql = "SELECT\n {{%reminder_history}}.*,\n iss_summary,\n sta_color,\n rma_title\n FROM\n {{%reminder_history}},\n {{%reminder_action}},\n {{%issue}},\n {{%status}}\n WHERE\n iss_sta_id = sta_id AND\n rmh_iss_id = iss_id AND\n rmh_rma_id = rma_id AND\n iss_prj_id = ? AND\n";
$params = array($this->prj_id);
$this->createWhereClause($sql, $params, 'rmh_created_date');
$res = DB_Helper::getInstance()->getAll($sql, $params);
$this->processResult($res, 'rmh_created_date', 'rmh_iss_id');
return $res;
}
/**
* Analyzes the contents of the issue_custom_field and updates
* contents based on the fld_type.
*
* @param integer $fld_id
* @return bool
*/
public static function updateValuesForNewType($fld_id)
{
$details = self::getDetails($fld_id, true);
$db_field_name = self::getDBValueFieldNameByType($details['fld_type']);
$sql = 'UPDATE
{{%issue_custom_field}}
SET
';
if ($details['fld_type'] == 'integer') {
$sql .= "{$db_field_name} = IFNULL(icf_value, IFNULL(icf_value_date, NULL)),\n icf_value = NULL,\n icf_value_date = NULL";
} elseif ($details['fld_type'] == 'date') {
$sql .= "{$db_field_name} = IFNULL(icf_value, IFNULL(icf_value_date, NULL)),\n icf_value = NULL,\n icf_value_integer = NULL";
} else {
$sql .= "{$db_field_name} = IFNULL(icf_value_integer, IFNULL(icf_value_date, NULL)),\n icf_value_integer = NULL,\n icf_value_date = NULL";
}
$sql .= "\n WHERE\n {$db_field_name} IS NULL AND\n icf_fld_id = ?";
$params = array($fld_id);
try {
DB_Helper::getInstance()->query($sql, $params);
} catch (DbException $e) {
return false;
}
return true;
}
/**
* Method used to get the list of issues to be displayed in the grid layout.
*
* @param integer $prj_id The current project ID
* @param array $options The search parameters
* @param integer $current_row The current page number
* @param integer $max The maximum number of rows per page. 'ALL' for unlimited.
* @return array The list of issues to be displayed
*/
public static function getListing($prj_id, $options, $current_row = 0, $max = 5)
{
if (strtoupper($max) == 'ALL') {
$max = 9999999;
}
$start = $current_row * $max;
// get the current user's role
$usr_id = Auth::getUserID();
$role_id = User::getRoleByUser($usr_id, $prj_id);
$usr_details = User::getDetails($usr_id);
// get any custom fields that should be displayed
$custom_fields = Custom_Field::getFieldsToBeListed($prj_id);
$stmt = 'SELECT
iss_id,
iss_grp_id,
iss_prj_id,
iss_sta_id,
iss_customer_id,
iss_customer_contract_id,
iss_created_date,
iss_updated_date,
iss_last_response_date,
iss_closed_date,
iss_last_customer_action_date,
iss_usr_id,
iss_summary,
pri_title,
prc_title,
sta_title,
sta_color status_color,
sta_id,
iqu_status,
grp_name,
pre_title,
iss_last_public_action_date,
iss_last_public_action_type,
iss_last_internal_action_date,
iss_last_internal_action_type,
' . Issue::getLastActionFields() . ",\n CASE WHEN iss_last_internal_action_date > iss_last_public_action_date THEN 'internal' ELSE 'public' END AS action_type,\n iss_private,\n usr_full_name,\n iss_percent_complete,\n iss_dev_time,\n iss_expected_resolution_date,\n sev_title\n FROM\n (\n {{%issue}},\n {{%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 \n\n {{%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($usr_details['usr_par_code'])) {
// restrict partners
$stmt .= '
LEFT JOIN
{{%issue_partner}}
ON
ipa_iss_id=iss_id';
}
if (!empty($options['show_authorized_issues']) || $role_id == User::getRoleID('Reporter') && Project::getSegregateReporters($prj_id)) {
$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';
}
$stmt .= "\n LEFT JOIN\n {{%group}}\n ON\n iss_grp_id=grp_id\n LEFT JOIN\n {{%project_category}}\n ON\n iss_prc_id=prc_id\n LEFT JOIN\n {{%project_release}}\n ON\n iss_pre_id = pre_id\n LEFT JOIN\n {{%status}}\n ON\n iss_sta_id=sta_id\n LEFT JOIN\n {{%project_priority}}\n ON\n iss_pri_id=pri_id\n LEFT JOIN\n {{%project_severity}}\n ON\n iss_sev_id=sev_id\n LEFT JOIN\n {{%issue_quarantine}}\n ON\n iss_id=iqu_iss_id AND\n (iqu_expiration > '" . Date_Helper::getCurrentDateGMT() . "' OR iqu_expiration IS NULL)\n WHERE\n iss_prj_id= " . Misc::escapeInteger($prj_id);
$stmt .= self::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';
$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' => null, 'info' => null, 'csv' => null);
}
if (count($res) > 0) {
Issue::getAssignedUsersByIssues($res);
Time_Tracking::fillTimeSpentByIssues($res);
// need to get the customer titles for all of these issues...
if (CRM::hasCustomerIntegration($prj_id)) {
$crm = CRM::getInstance($prj_id);
$crm->processListIssuesResult($res);
}
Issue::formatLastActionDates($res);
Issue::getLastStatusChangeDates($prj_id, $res);
} elseif ($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("list.php?pagerRow=0&rows={$max}");
}
$groups = Group::getAssocList($prj_id);
$categories = Category::getAssocList($prj_id);
$column_headings = array();
$columns_to_display = Display_Column::getColumnsToDisplay($prj_id, 'list_issues');
foreach ($columns_to_display as $col_key => $column) {
if ($col_key == 'custom_fields' && count($custom_fields) > 0) {
foreach ($custom_fields as $fld_id => $fld_title) {
$column_headings['cstm_' . $fld_id] = $fld_title;
}
} else {
$column_headings[$col_key] = $column['title'];
}
}
$csv[] = @implode("\t", $column_headings);
if (@$options['hide_excerpts'] != 1 && self::doesBackendSupportExcerpts() == true) {
$excerpts = self::getFullTextExcerpts();
}
foreach ($res as &$row) {
$issue_id = $row['iss_id'];
$row['time_spent'] = Misc::getFormattedTime($row['time_spent']);
$row['iss_created_date'] = Date_Helper::getFormattedDate($row['iss_created_date']);
$row['iss_expected_resolution_date'] = Date_Helper::getSimpleDate($row['iss_expected_resolution_date'], false);
$row['excerpts'] = isset($excerpts[$issue_id]) ? $excerpts[$issue_id] : '';
$fields = array();
foreach (array_keys($columns_to_display) as $col_key) {
switch ($col_key) {
case 'pri_rank':
$col_key = 'pri_title';
break;
case 'assigned':
$col_key = 'assigned_users';
break;
case 'sta_rank':
$col_key = 'sta_title';
break;
case 'sta_change_date':
$col_key = 'status_change_date';
break;
case 'sev_rank':
$col_key = 'sev_title';
break;
}
if ($col_key == 'custom_fields' && count($custom_fields) > 0) {
$custom_field_values = Custom_Field::getListByIssue($prj_id, $row['iss_id']);
foreach ($custom_field_values as $this_field) {
if (!empty($custom_fields[$this_field['fld_id']])) {
$row['custom_field'][$this_field['fld_id']] = $this_field['value'];
$fields[] = $this_field['value'];
}
}
} else {
$fields[] = isset($row[$col_key]) ? $row[$col_key] : '';
}
}
if (CRM::hasCustomerIntegration($prj_id)) {
// check if current user is a customer and has a per incident contract.
// if so, check if issue is redeemed.
if (User::getRoleByUser($usr_id, $prj_id) == User::getRoleID('Customer')) {
// TODOCRM: Fix per incident usage
// if ((Customer::hasPerIncidentContract($prj_id, Issue::getCustomerID($res[$i]['iss_id'])) &&
// (Customer::isRedeemedIncident($prj_id, $res[$i]['iss_id'])))) {
// $res[$i]['redeemed'] = true;
// }
}
}
$csv[] = @implode("\t", $fields);
}
$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, 'custom_fields' => $custom_fields), 'csv' => @implode("\n", $csv));
}
public static function updateProductAndVersion($ipv_id, $pro_id, $version)
{
if ($pro_id == -1) {
$sql = 'DELETE FROM
{{%issue_product_version}}
WHERE
ipv_id = ?';
$params = array($ipv_id);
} else {
$sql = 'UPDATE
{{%issue_product_version}}
SET
ipv_pro_id = ?,
ipv_version = ?
WHERE
ipv_id = ?';
$params = array($pro_id, $version, $ipv_id);
}
try {
DB_Helper::getInstance()->query($sql, $params);
} catch (DbException $e) {
return false;
}
return true;
}
/**
* Method used to get the sev_id of a project by severity title.
*
* @param integer $prj_id The project ID
* @param integer $sev_id The severity ID
* @param string $sev_title The severity title
* @return integer $sev_id The severity ID
*/
public static function getID($prj_id, $sev_title)
{
$sql = 'SELECT
sev_id
FROM
{{%project_severity}}
WHERE
sev_prj_id=?
AND sev_title = ?';
try {
$res = DB_Helper::getInstance()->getOne($sql, array($prj_id, $sev_title));
} catch (DbException $e) {
return false;
}
return $res;
}
/**
* Method used to escape a string before using it in a query.
*
* @param string|array $input The original string
* @return string|array The escaped (or not) string
* @deprecated Using this is bad design, must use placeholders in query
*/
public static function escapeString($input, $add_quotes = false)
{
if (is_array($input)) {
foreach ($input as $key => $value) {
$input[$key] = self::escapeString($value, $add_quotes);
}
} else {
$input = DB_Helper::escapeString($input, $add_quotes);
}
return $input;
}
public static function getProjectsForPartner($par_code)
{
$sql = 'SELECT
pap_prj_id,
prj_title
FROM
{{%partner_project}},
{{%project}}
WHERE
pap_prj_id = prj_id AND
pap_par_code = ?';
try {
$res = DB_Helper::getInstance()->getPair($sql, array($par_code));
} catch (DbException $e) {
return array();
}
return $res;
}
/**
* Method used to remove all custom filters associated with some
* specific projects.
*
* @param array $ids List of projects to remove from
* @return boolean Whether the removal worked properly or not
*/
public static function removeByProjects($ids)
{
$stmt = 'DELETE FROM
{{%custom_filter}}
WHERE
cst_prj_id IN (' . DB_Helper::buildList($ids) . ')';
try {
DB_Helper::getInstance()->query($stmt, $ids);
} catch (DbException $e) {
return false;
}
return true;
}
/**
* Method used to set the preferences for a specific user.
*
* @param integer $usr_id The user ID
* @param array $preferences An array of preferences
* @return integer 1 if the update worked, -1 otherwise
*/
public static function set($usr_id, $preferences)
{
// set global preferences
$sql = 'REPLACE INTO
{{%user_preference}}
SET
upr_usr_id = ?,
upr_timezone = ?,
upr_week_firstday = ?,
upr_list_refresh_rate = ?,
upr_email_refresh_rate = ?,
upr_email_signature = ?,
upr_auto_append_email_sig = ?,
upr_auto_append_note_sig = ?,
upr_auto_close_popup_window = ?';
try {
DB_Helper::getInstance()->query($sql, array($usr_id, @$preferences['timezone'], @$preferences['week_firstday'], @$preferences['list_refresh_rate'], @$preferences['email_refresh_rate'], @$preferences['email_signature'], @$preferences['auto_append_email_sig'], @$preferences['auto_append_note_sig'], @$preferences['close_popup_windows']));
} catch (DbException $e) {
return -1;
}
// set per project preferences
$projects = Project::getAssocList($usr_id);
foreach ($projects as $prj_id => $project_name) {
$sql = 'REPLACE INTO
{{%user_project_preference}}
SET
upp_usr_id = ?,
upp_prj_id = ?,
upp_receive_assigned_email = ?,
upp_receive_new_issue_email = ?,
upp_receive_copy_of_own_action = ?';
try {
DB_Helper::getInstance()->query($sql, array($usr_id, $prj_id, $preferences['receive_assigned_email'][$prj_id], $preferences['receive_new_issue_email'][$prj_id], $preferences['receive_copy_of_own_action'][$prj_id]));
} catch (DbException $e) {
return -1;
}
}
return 1;
}
/**
* Returns the issue ID of the issue with the specified root message ID, or false
* @param string $msg_id The Message ID
* @return integer The ID of the issue
*/
public static function getIssueByRootMessageID($msg_id)
{
static $returns;
if (!empty($returns[$msg_id])) {
return $returns[$msg_id];
}
$sql = 'SELECT
iss_id
FROM
{{%issue}}
WHERE
iss_root_message_id = ?';
try {
$res = DB_Helper::getInstance()->getOne($sql, array($msg_id));
} catch (DbException $e) {
return false;
}
if (empty($res)) {
$returns[$msg_id] = false;
} else {
$returns[$msg_id] = $res;
}
return $returns[$msg_id];
}
/**
* Returns the sequential number of the specified email ID.
*
* @param integer $sup_id The email ID
* @return integer The sequence number of the email
*/
public static function getSequenceByID($sup_id)
{
if (empty($sup_id)) {
return '';
}
try {
DB_Helper::getInstance()->query('SET @sup_seq = 0');
} catch (DbException $e) {
return 0;
}
$issue_id = Support::getIssueFromEmail($sup_id);
$sql = 'SELECT
sup_id,
@sup_seq := @sup_seq+1
FROM
{{%support_email}}
WHERE
sup_iss_id = ?
ORDER BY
sup_id ASC';
try {
$res = DB_Helper::getInstance()->getPair($sql, array($issue_id));
} catch (DbException $e) {
return 0;
}
return @$res[$sup_id];
}
/**
* Method used to get the title for a specific project category.
*
* @param integer $prc_id The category ID
* @return string The category title
*/
public static function getTitle($prc_id)
{
$stmt = 'SELECT
prc_title
FROM
{{%project_category}}
WHERE
prc_id=?';
try {
$res = DB_Helper::getInstance()->getOne($stmt, array($prc_id));
} catch (DbException $e) {
return '';
}
return $res;
}
/**
* Method used to get the list of technical account managers for
* a given customer ID.
*
* @return array The list of account managers
*/
public function getEventumAccountManagers()
{
$stmt = 'SELECT
cam_usr_id,
usr_email,
cam_type
FROM
{{%customer_account_manager}},
{{%user}}
WHERE
cam_usr_id=usr_id AND
cam_prj_id=? AND
cam_customer_id=?';
$params = array($this->crm->getProjectID(), $this->customer_id);
try {
$res = DB_Helper::getInstance()->getAll($stmt, $params);
} catch (DbException $e) {
return array();
}
if (empty($res)) {
return array();
}
return $res;
}
define('INSTALL_PATH', dirname(__FILE__) . '/..');
define('CONFIG_PATH', INSTALL_PATH . '/config');
// avoid setup redirecting us
if (!file_exists(CONFIG_PATH . '/setup.php') || !filesize(CONFIG_PATH . '/setup.php') || !is_readable(CONFIG_PATH . '/setup.php')) {
error_log("ERROR: Can't get setup.php in '" . CONFIG_PATH . "'");
error_log('Did you forgot to copy config from old install? Is file readable?');
exit(1);
}
// load init only if no autoloader present
if (!class_exists('DB_Helper')) {
require_once INSTALL_PATH . '/init.php';
}
$in_setup = defined('IN_SETUP');
global $dbconfig, $db;
$dbconfig = DB_Helper::getConfig();
$db = DB_Helper::getInstance();
function exec_sql_file($input_file)
{
if (!file_exists($input_file) && !is_readable($input_file)) {
throw new RuntimeException("Can't read file: {$input_file}");
}
global $dbconfig, $db;
// use *.php for complex updates
if (substr($input_file, -4) == '.php') {
$queries = array();
require $input_file;
} else {
$queries = explode(';', file_get_contents($input_file));
}
foreach ($queries as $query) {
$query = trim($query);
/**
* Returns an associative array with the list of faq entry
* IDs and their respective ranking.
*
* @return array The list of faq entries
*/
private function _getRanking()
{
$stmt = 'SELECT
faq_id,
faq_rank
FROM
{{%faq}}
ORDER BY
faq_rank ASC';
try {
$res = DB_Helper::getInstance()->getPair($stmt);
} catch (DbException $e) {
return array();
}
return $res;
}
/**
* Returns the replier based on the given issue and email address combo.
*
* @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
*/
public function getReplierIDByEmail($issue_id, $email)
{
$stmt = 'SELECT
iur_id
FROM
{{%issue_user_replier}}
LEFT JOIN
{{%user}}
ON
iur_usr_id = usr_id
WHERE
iur_iss_id = ? AND
(iur_email = ? OR usr_email = ?)';
try {
$res = DB_Helper::getInstance()->getOne($stmt, array($issue_id, $email, $email));
} catch (DbException $e) {
return 0;
}
return $res;
}
/**
* Returns workload information for the specified date range and interval.
*
* @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.
* @param integer $category_id The category to restrict this report to
* @return array An array containing workload data.
*/
public static function getWorkloadByDateRange($interval, $type, $start, $end, $category_id)
{
$data = array();
$category_id = (int) $category_id;
// 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 = "CASE WHEN DATE_FORMAT(%1\$s, '%%w') = 0 THEN 7 ELSE DATE_FORMAT(%1\$s, '%%w') END";
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;
default:
throw new LogicException('Invalid interval');
}
// get issue counts
$stmt = 'SELECT
DATE_FORMAT(iss_created_date, ?),
count(*)
FROM
{{%issue}}
WHERE
iss_prj_id=? AND
iss_created_date BETWEEN ? AND ?';
$params = array($format, Auth::getCurrentProject(), $start, $end);
if (!empty($category_id)) {
$stmt .= ' AND
iss_prc_id = ?';
$params[] = $category_id;
}
$stmt .= '
GROUP BY
DATE_FORMAT(iss_created_date, ?)';
$params[] = $format;
if (!empty($order_by)) {
$stmt .= "\nORDER BY " . sprintf($order_by, 'iss_created_date');
}
try {
$res = DB_Helper::getInstance()->fetchAssoc($stmt, $params);
} catch (DbException $e) {
return array();
}
$data['issues']['points'] = $res;
$data['issues']['stats'] = array('total' => 0, 'avg' => 0, 'median' => 0, 'max' => 0);
if ($res) {
$stats = new Math_Stats();
$stats->setData($res);
$data['issues']['stats'] = array('total' => $stats->sum(), 'avg' => $stats->mean(), 'median' => $stats->median(), 'max' => $stats->max());
}
// get email counts
$params = array();
$stmt = 'SELECT
DATE_FORMAT(sup_date, ?),
count(*)
FROM
{{%support_email}},
{{%email_account}}';
$params[] = $format;
if (!empty($category_id)) {
$stmt .= ',
{{%issue}}';
}
$stmt .= '
WHERE
sup_ema_id=ema_id AND
ema_prj_id=? AND
sup_date BETWEEN ? AND ?';
$params[] = Auth::getCurrentProject();
$params[] = $start;
$params[] = $end;
if (!empty($category_id)) {
$stmt .= ' AND
sup_iss_id = iss_id AND
iss_prc_id = ?';
$params[] = $category_id;
}
$stmt .= '
GROUP BY
DATE_FORMAT(sup_date, ?)';
$params[] = $format;
if (!empty($order_by)) {
$stmt .= "\nORDER BY " . sprintf($order_by, 'sup_date');
}
try {
$res = DB_Helper::getInstance()->fetchAssoc($stmt, $params);
} catch (DbException $e) {
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;
}
/**
* Method used to get a list as an associative array of the
* releases.
*
* @param integer $prj_id The project ID
* @param boolean $show_all_dates If true all releases, not just those with future dates will be returned
* @return array The list of releases
*/
public static function getAssocList($prj_id, $show_all_dates = false)
{
$stmt = 'SELECT
pre_id,
pre_title
FROM
{{%project_release}}
WHERE
pre_prj_id=? AND
(
pre_status=?';
$params = array($prj_id, 'available');
if ($show_all_dates != true) {
$stmt .= ' AND
pre_scheduled_date >= ?';
$params[] = gmdate('Y-m-d');
}
$stmt .= '
)
ORDER BY
pre_scheduled_date ASC';
try {
$res = DB_Helper::getInstance()->getPair($stmt, $params);
} catch (DbException $e) {
return '';
}
return $res;
}
function write_config()
{
$config_file_path = APP_CONFIG_PATH . '/config.php';
// disable the full-text search feature for certain mysql server users
/** @var DbInterface $conn */
$mysql_version = DB_Helper::getInstance(false)->getOne('SELECT VERSION()');
preg_match('/(\\d{1,2}\\.\\d{1,2}\\.\\d{1,2})/', $mysql_version, $matches);
$enable_fulltext = $matches[1] > '4.0.23';
$replace = array("'%{APP_HOSTNAME}%'" => e($_POST['hostname']), "'%{CHARSET}%'" => e(APP_CHARSET), "'%{APP_RELATIVE_URL}%'" => e($_POST['relative_url']), "'%{APP_DEFAULT_TIMEZONE}%'" => e($_POST['default_timezone']), "'%{APP_DEFAULT_WEEKDAY}%'" => (int) $_POST['default_weekday'], "'%{PROTOCOL_TYPE}%'" => e(@$_POST['is_ssl'] == 'yes' ? 'https://' : 'http://'), "'%{APP_ENABLE_FULLTEXT}%'" => e($enable_fulltext));
$config_contents = file_get_contents(APP_CONFIG_PATH . '/config.dist.php');
$config_contents = str_replace(array_keys($replace), array_values($replace), $config_contents);
write_file($config_file_path, $config_contents);
}
/**
* Method used to get the list of associated projects for a given
* news entry.
*
* @param integer $nws_id The news ID
* @return array The list of projects
*/
public function getAssociatedProjects($nws_id)
{
$stmt = 'SELECT
prj_id,
prj_title
FROM
{{%project}},
{{%project_news}}
WHERE
prj_id=prn_prj_id AND
prn_nws_id=?';
try {
$res = DB_Helper::getInstance()->getPair($stmt, array($nws_id));
} catch (DbException $e) {
return array();
}
return $res;
}
exit;
}
// TODO: move this query to some class
$sql = 'SELECT
prc_id,
prc_title,
SUM(iss_dev_time) as dev_time
FROM
{{%issue}},
{{%project_category}},
{{%status}}
WHERE
iss_prc_id = prc_id AND
iss_sta_id = sta_id AND
sta_is_closed != 1 AND
iss_prj_id = ?
GROUP BY
iss_prc_id';
try {
$res = DB_Helper::getInstance()->getAll($sql, array(Auth::getCurrentProject()));
} catch (DbException $e) {
return false;
}
$total = 0;
foreach ($res as $id => $row) {
$total += $row['dev_time'];
$res[$id]['dev_time'] = str_replace(' ', ' ', str_pad($row['dev_time'], 5, ' ', STR_PAD_LEFT));
}
$res[] = array('dev_time' => str_replace(' ', ' ', str_pad($total, 5, ' ', STR_PAD_LEFT)), 'prc_title' => 'Total');
$tpl->assign('data', $res);
$tpl->displayTemplate();