/**
* Method used to remove user-selected categories from the
* database.
*
* @return boolean Whether the removal worked or not
*/
public static function remove()
{
$items = $_POST['items'];
$stmt = 'DELETE FROM
{{%project_category}}
WHERE
prc_id IN (' . DB_Helper::buildList($items) . ')';
try {
DB_Helper::getInstance()->query($stmt, $items);
} catch (DbException $e) {
return false;
}
return true;
}
/**
* Method used to remove a specific list of checkins
*
* @param int[] $items list to remove
* @return integer 1 if the update worked, -1 otherwise
*/
public static function remove($items)
{
$itemlist = DB_Helper::buildList($items);
$stmt = "SELECT\n isc_iss_id\n FROM\n {{%issue_checkin}}\n WHERE\n isc_id IN ({$itemlist})";
$issue_id = DB_Helper::getInstance()->getOne($stmt, $items);
$stmt = "DELETE FROM\n {{%issue_checkin}}\n WHERE\n isc_id IN ({$itemlist})";
try {
DB_Helper::getInstance()->query($stmt, $items);
} catch (DbException $e) {
return -1;
}
// need to mark this issue as updated
Issue::markAsUpdated($issue_id);
$usr_id = Auth::getUserID();
History::add($issue_id, $usr_id, 'scm_checkin_removed', 'SCM Checkins removed by {user}', array('user' => User::getFullName($usr_id)));
return 1;
}
/**
* Method used to remove resolutions by using the administrative
* interface of the system.
*
* @return boolean
*/
public static function remove()
{
$items = $_POST['items'];
$itemlist = DB_Helper::buildList($items);
// gotta fix the issues before removing the resolution
$stmt = "UPDATE\n {{%issue}}\n SET\n iss_res_id=0\n WHERE\n iss_res_id IN ({$itemlist})";
try {
DB_Helper::getInstance()->query($stmt, $items);
} catch (DbException $e) {
return false;
}
$stmt = "DELETE FROM\n {{%resolution}}\n WHERE\n res_id IN ({$itemlist})";
try {
DB_Helper::getInstance()->query($stmt, $items);
} catch (DbException $e) {
return false;
}
return true;
}
/**
* Removes the specified authorized replier
*
* @param integer[] $iur_ids The ids of the authorized repliers
* @return int
*/
public static function removeRepliers($iur_ids)
{
$iur_list = DB_Helper::buildList($iur_ids);
// get issue_id for logging
$stmt = "SELECT\n iur_iss_id\n FROM\n {{%issue_user_replier}}\n WHERE\n iur_id IN ({$iur_list})";
try {
$issue_id = DB_Helper::getInstance()->getOne($stmt, $iur_ids);
} catch (DbException $e) {
return false;
}
foreach ($iur_ids as $id) {
$replier = self::getReplier($id);
$stmt = "DELETE FROM\n {{%issue_user_replier}}\n WHERE\n iur_id IN ({$iur_list})";
try {
DB_Helper::getInstance()->query($stmt, $iur_ids);
} catch (DbException $e) {
return -1;
}
$usr_id = Auth::getUserID();
History::add($issue_id, $usr_id, 'replier_removed', 'Authorized replier {replier} removed by {user}', array('replier' => $replier, 'user' => User::getFullName($usr_id)));
return 1;
}
}
public static function remove($ids)
{
$sql = 'DELETE FROM
{{%product}}
WHERE
pro_id IN (' . DB_Helper::buildList($ids) . ')';
try {
DB_Helper::getInstance()->query($sql, $ids);
} catch (DbException $e) {
return -1;
}
return 1;
}
/**
* Removes the selected notes from the database.
*
* @param array $ids An array of cno_id's to be deleted.
* @return int
*/
public static function removeNotes($ids)
{
$stmt = 'DELETE FROM
{{%customer_note}}
WHERE
cno_id IN (' . DB_Helper::buildList($ids) . ')';
try {
DB_Helper::getInstance()->query($stmt, $ids);
} catch (DbException $e) {
return -1;
}
return 1;
}
/**
* Method used to remove user-selected priorities from the
* database.
*
* @param array $sev_ids Severity ids to remove
* @return boolean Whether the removal worked or not
*/
public static function remove($sev_ids)
{
if (count($sev_ids) < 1) {
return true;
}
$items = DB_Helper::buildList($sev_ids);
$sql = "DELETE FROM\n {{%project_severity}}\n WHERE\n sev_id IN ({$items})";
try {
DB_Helper::getInstance()->query($sql, $sev_ids);
} catch (DbException $e) {
return false;
}
return true;
}
/**
* Method used to remove reminder conditions by using the administrative
* interface of the system.
*
* @return boolean
*/
public static function remove()
{
$items = $_POST['items'];
$stmt = 'DELETE FROM
{{%reminder_level_condition}}
WHERE
rlc_id IN (' . DB_Helper::buildList($items) . ')';
DB_Helper::getInstance()->query($stmt, $items);
}
/**
* Method used to remove all project/user associations for a given
* set of projects.
*
* @param array $ids The project IDs
* @param array $users_to_not_remove Users that should not be removed
* @return boolean
*/
public static function removeUserByProjects($ids, $users_to_not_remove = null)
{
$stmt = 'DELETE FROM
{{%project_user}}
WHERE
pru_prj_id IN (' . DB_Helper::buildList($ids) . ')';
$params = $ids;
if ($users_to_not_remove) {
$stmt .= " AND\n pru_usr_id NOT IN (" . DB_Helper::buildList($users_to_not_remove) . ')';
$params = array_merge($params, $users_to_not_remove);
}
try {
DB_Helper::getInstance()->query($stmt, $params);
} catch (DbException $e) {
return false;
}
return true;
}
/**
* Method used to change the status of users, making them inactive
* or active.
*
* @param int[] $usr_ids
* @param string $status
* @return boolean
*/
public static function changeStatus($usr_ids, $status)
{
// check if the user being inactivated is the last one
if ($status == self::USER_STATUS_INACTIVE) {
$stmt = 'SELECT
COUNT(*)
FROM
{{%user}}
WHERE
usr_status=?';
$total_active = DB_Helper::getInstance()->getOne($stmt, array(self::USER_STATUS_ACTIVE));
if ($total_active < 2) {
return false;
}
}
$usr_ids = (array) $usr_ids;
$items = DB_Helper::buildList($usr_ids);
$stmt = "UPDATE\n {{%user}}\n SET\n usr_status=?\n WHERE\n usr_id IN ({$items})";
$params = array_merge(array($status), $usr_ids);
try {
DB_Helper::getInstance()->query($stmt, $params);
} catch (DbException $e) {
return false;
}
return true;
}
/**
* Method used to remove user-selected priorities from the
* database.
*
* @return boolean Whether the removal worked or not
*/
public static function remove()
{
$items = $_POST['items'];
$itemlist = DB_Helper::buildList($items);
$stmt = "DELETE FROM\n {{%project_priority}}\n WHERE\n pri_id IN ({$itemlist})";
try {
DB_Helper::getInstance()->query($stmt, $items);
} catch (DbException $e) {
return false;
}
return true;
}
/**
* Associate uploaded files to an "attachment".
* Additionally cleanups stale uploads.
*
* @param int $attachment_id
* @param int[] $iaf_ids
*/
private static function associateFiles($attachment_id, $iaf_ids)
{
// TODO: verify that all $iaf_ids actually existed, not expired
$list = DB_Helper::buildList($iaf_ids);
$stmt = "UPDATE {{%issue_attachment_file}} SET iaf_iat_id=? WHERE iaf_id in ({$list})";
$params = $iaf_ids;
array_unshift($params, $attachment_id);
DB_Helper::getInstance()->query($stmt, $params);
// run cleanup of stale uploads
$stmt = "DELETE FROM {{%issue_attachment_file}} WHERE iaf_iat_id=0 AND iaf_created_date>'0000-00-00 00:00:00' AND iaf_created_date < ?";
$expire_date = time() - self::ATTACHMENT_EXPIRE_TIME;
$params = array(Date_Helper::convertDateGMT($expire_date));
DB_Helper::getInstance()->query($stmt, $params);
}
/**
* Method used to remove all custom fields associated with
* a given set of projects.
*
* @param array $ids The array of project IDs
* @return boolean
*/
public static function removeByProjects($ids)
{
$stmt = 'DELETE FROM
{{%project_custom_field}}
WHERE
pcf_prj_id IN (' . DB_Helper::buildList($ids) . ')';
try {
DB_Helper::getInstance()->query($stmt, $ids);
} catch (DbException $e) {
return false;
}
return true;
}
/**
* Method used to remove the project associations for a given
* email response entry.
*
* @param integer $ere_id The email response ID
* @param integer $prj_id The project ID
* @return boolean
*/
public function removeProjectAssociations($ere_id, $prj_id = null)
{
if (!is_array($ere_id)) {
$ere_id = array($ere_id);
}
$stmt = 'DELETE FROM
{{%project_email_response}}
WHERE
per_ere_id IN (' . DB_Helper::buildList($ere_id) . ')';
$params = $ere_id;
if ($prj_id) {
$stmt .= ' AND per_prj_id=?';
$params[] = $prj_id;
}
try {
DB_Helper::getInstance()->query($stmt, $params);
} catch (DbException $e) {
return false;
}
return true;
}
public static function getMessageRecipients($types, $type_id)
{
if (!is_array($types)) {
$types = array($types);
}
$types_list = DB_Helper::buildList($types);
$sql = "SELECT\n maq_recipient\n FROM\n {{%mail_queue}}\n WHERE\n maq_type IN ({$types_list}) AND\n maq_type_id = ?";
$params = $types;
$params[] = $type_id;
try {
$res = DB_Helper::getInstance()->getColumn($sql, $params);
} catch (DbException $e) {
return false;
}
foreach ($res as &$row) {
// FIXME: what does quote stripping fix here
$row = Mime_Helper::decodeAddress(str_replace('"', '', $row));
}
return $res;
}
/**
* Method used to remove a round robin entry from the system.
*
* @return boolean
*/
public static function remove()
{
$items = $_POST['items'];
$itemlist = DB_Helper::buildList($items);
$stmt = "DELETE FROM\n {{%project_round_robin}}\n WHERE\n prr_id IN ({$itemlist})";
try {
DB_Helper::getInstance()->query($stmt, $items);
} catch (DbException $e) {
return false;
}
self::removeUserAssociations($items);
return true;
}
/**
* Returns the given list of issues with only the issues that
* were last triggered for the given reminder action ID.
*
* @param array $issues The list of issue IDs
* @param integer $rma_id The reminder action ID
* @return array The list of issue IDs
*/
public static function getRepeatActions($issues, $rma_id)
{
if (count($issues) == 0) {
return $issues;
}
$idlist = DB_Helper::buildList($issues);
$stmt = "SELECT\n rta_iss_id,\n rta_rma_id\n FROM\n {{%reminder_triggered_action}}\n WHERE\n rta_iss_id IN ({$idlist})";
try {
$triggered_actions = DB_Helper::getInstance()->getPair($stmt, $issues);
} catch (DbException $e) {
return $issues;
}
$repeat_issues = array();
foreach ($issues as $issue_id) {
// if the issue was already triggered and the last triggered
// action was the given one, then add it to the list of repeat issues
if (in_array($issue_id, array_keys($triggered_actions)) && $triggered_actions[$issue_id] == $rma_id) {
$repeat_issues[] = $issue_id;
}
}
return $repeat_issues;
}
/**
* Method used to get an associative array of the support email
* accounts in the format of account ID => account title.
*
* @param integer $projects An array of project IDs
* @return array The list of accounts
*/
public static function getAssocList($projects, $include_project_title = false)
{
if (!is_array($projects)) {
$projects = array($projects);
}
if ($include_project_title) {
$title_sql = "CONCAT(prj_title, ': ', ema_username, '@', ema_hostname, ' ', ema_folder)";
} else {
$title_sql = "CONCAT(ema_username, '@', ema_hostname, ' ', ema_folder)";
}
$stmt = "SELECT\n ema_id,\n {$title_sql} AS ema_title\n FROM\n {{%email_account}},\n {{%project}}\n WHERE\n prj_id = ema_prj_id AND\n ema_prj_id IN (" . DB_Helper::buildList($projects) . ')
ORDER BY
ema_title';
try {
$res = DB_Helper::getInstance()->getPair($stmt, $projects);
} catch (DbException $e) {
return '';
}
return $res;
}
/**
* Method used to remove the association of all support emails
* for a given issue.
*
* @return integer 1 if it worked, -1 otherwise
*/
public static function removeAssociation()
{
$items = $_POST['item'];
$list = DB_Helper::buildList($items);
$stmt = "SELECT\n sup_iss_id\n FROM\n {{%support_email}}\n WHERE\n sup_id IN ({$list})";
$issue_id = DB_Helper::getInstance()->getOne($stmt, $items);
$stmt = "UPDATE\n {{%support_email}}\n SET\n sup_iss_id=0\n WHERE\n sup_id IN ({$list})";
try {
DB_Helper::getInstance()->query($stmt, $items);
} catch (DbException $e) {
return -1;
}
Issue::markAsUpdated($issue_id);
// save a history entry for each email being associated to this issue
$stmt = "SELECT\n sup_id,\n sup_subject\n FROM\n {{%support_email}}\n WHERE\n sup_id IN ({$list})";
$subjects = DB_Helper::getInstance()->fetchAssoc($stmt, $items);
$usr_id = Auth::getUserID();
foreach ($items as $item) {
History::add($issue_id, $usr_id, 'email_disassociated', "Email (subject: '{subject}') disassociated by {user}", array('subject' => $subjects[$item], 'user' => User::getFullName($usr_id)));
}
return 1;
}
/**
* Method used to remove all notes associated with a specific set
* of issues.
*
* @param array $ids The list of issues
* @return boolean
*/
public static function removeByIssues($ids)
{
$items = DB_Helper::buildList($ids);
$stmt = "DELETE FROM\n {{%note}}\n WHERE\n not_iss_id IN ({$items})";
try {
DB_Helper::getInstance()->query($stmt, $ids);
} catch (DbException $e) {
return false;
}
return true;
}
/**
* Method used to remove the support level associations for a given
* FAQ entry.
*
* @param integer $faq_id The FAQ ID
* @return boolean
*/
public function removeSupportLevelAssociations($faq_id)
{
if (!is_array($faq_id)) {
$faq_id = array($faq_id);
}
$stmt = 'DELETE FROM
{{%faq_support_level}}
WHERE
fsl_faq_id IN (' . DB_Helper::buildList($faq_id) . ')';
try {
DB_Helper::getInstance()->query($stmt, $faq_id);
} catch (DbException $e) {
return false;
}
return true;
}
/**
* Returns data for the custom fields weekly report, based on the field and options passed in.
*
* @param integer $fld_id The id of the custom field.
* @param array $cfo_ids An array of option ids.
* @param string $start_date
* @param string $end_date
* @param boolean $per_user Show time spent per user
* @return array An array of data.
*/
public static function getCustomFieldWeeklyReport($fld_id, $cfo_ids, $start_date, $end_date, $per_user = false)
{
$fld_id = (int) $fld_id;
$cfo_ids = (array) $cfo_ids;
// get field values
$options = Custom_Field::getOptions($fld_id, $cfo_ids);
$params = array();
$sql = 'SELECT
iss_id,
SUM(ttr_time_spent) ttr_time_spent_sum,
iss_summary,
iss_customer_id,
iss_private
';
if ($per_user) {
$sql .= ', usr_full_name ';
}
$sql .= '
FROM
{{%time_tracking}},';
if ($per_user) {
$sql .= '{{%user}}, ';
}
$sql .= '
{{%issue}}
WHERE
iss_prj_id=? AND
ttr_created_date BETWEEN ? AND ? AND
ttr_iss_id = iss_id AND
';
$params[] = Auth::getCurrentProject();
$params[] = "{$start_date} 00:00:00";
$params[] = "{$end_date} 23:59:59";
if ($per_user) {
$sql .= ' usr_id = ttr_usr_id AND ';
}
$sql .= '
ttr_iss_id = iss_id
';
if (count($options) > 0) {
$ids = array_keys($options);
$list = DB_Helper::buildList($ids);
$sql .= " AND (\n SELECT\n count(*)\n FROM\n {{%issue_custom_field}} a\n WHERE\n a.icf_fld_id = ? AND\n a.icf_value IN({$list}) AND\n a.icf_iss_id = ttr_iss_id\n ) > 0";
$params[] = $fld_id;
$params = array_merge($params, $ids);
}
if ($per_user) {
$sql .= '
GROUP BY
iss_id, ttr_usr_id';
} else {
$sql .= '
GROUP BY
iss_id';
}
try {
$res = DB_Helper::getInstance()->getAll($sql, $params);
} catch (DbException $e) {
return array();
}
foreach ($res as &$row) {
$row['field_value'] = Custom_Field::getDisplayValue($row['iss_id'], $fld_id);
$row['ttr_time_spent_sum_formatted'] = Misc::getFormattedTime($row['ttr_time_spent_sum'], false);
}
return $res;
}
/**
* Method used to delete all user assignments for a specific issue.
*
* @param int|array $issue_id The issue ID
* @param integer $usr_id The user ID of the person performing the change
* @return int
*/
public static function deleteUserAssociations($issue_id, $usr_id = null)
{
$issues = (array) $issue_id;
$list = DB_Helper::buildList($issues);
$stmt = "DELETE FROM\n {{%issue_user}}\n WHERE\n isu_iss_id IN ({$list})";
try {
DB_Helper::getInstance()->query($stmt, $issues);
} catch (DbException $e) {
return -1;
}
if ($usr_id) {
History::add($issue_id, $usr_id, 'user_all_unassociated', 'Issue assignments removed by {user}', array('user' => User::getFullName($usr_id)));
}
return 1;
}
/**
* Method used to remove reminders by using the administrative
* interface of the system.
*
* @return boolean
*/
public static function remove()
{
$items = $_POST['items'];
$itemlist = DB_Helper::buildList($items);
$stmt = "DELETE FROM\n {{%reminder_level}}\n WHERE\n rem_id IN ({$itemlist})";
try {
DB_Helper::getInstance()->query($stmt, $items);
} catch (DbException $e) {
return false;
}
self::removeAllAssociations($items);
$stmt = "SELECT\n rma_id\n FROM\n {{%reminder_action}}\n WHERE\n rma_rem_id IN ({$itemlist})";
$actions = DB_Helper::getInstance()->getColumn($stmt, $items);
if (count($actions) > 0) {
Reminder_Action::remove($actions);
}
return true;
}
/**
* Removes specified projects from all groups.
*
* @param array $projects An array of projects to remove from all groups.
* @return integer 1 if successful, -1 otherwise
*/
public static function disassociateProjects($projects)
{
// delete all current associations
$stmt = 'DELETE FROM
{{%project_group}}
WHERE
pgr_prj_id IN (' . DB_Helper::buildList($projects) . ')';
try {
DB_Helper::getInstance()->query($stmt, $projects);
} catch (DbException $e) {
return -1;
}
return 1;
}
/**
* Method used to add time spent on issue to a list of user issues.
*
* @param array $res User issues
* @param string $usr_id The ID of the user this report is for.
* @param integer $start The timestamp of the beginning of the report.
* @param integer $end The timestamp of the end of this report.
* @return void
*/
public static function fillTimeSpentByIssueAndTime(&$res, $usr_id, $start, $end)
{
$issue_ids = array();
foreach ($res as $row) {
$issue_ids[] = $row['iss_id'];
}
$stmt = 'SELECT
ttr_iss_id, sum(ttr_time_spent)
FROM
{{%time_tracking}}
WHERE
ttr_usr_id = ? AND
ttr_created_date BETWEEN ? AND ? AND
ttr_iss_id in (' . DB_Helper::buildList($issue_ids) . ')
GROUP BY ttr_iss_id';
$params = array($usr_id, $start, $end);
$params = array_merge($params, $issue_ids);
try {
$result = DB_Helper::getInstance()->getPair($stmt, $params);
} catch (DbException $e) {
return;
}
foreach ($res as $key => $item) {
@($res[$key]['it_spent'] = $result[$item['iss_id']]);
@($res[$key]['time_spent'] = Misc::getFormattedTime($result[$item['iss_id']], false));
}
}
/**
* Method used to remove releases by using the administrative
* interface of the system.
*
* @return boolean
*/
public static function remove()
{
$items = $_POST['items'];
$itemlist = DB_Helper::buildList($items);
// gotta fix the issues that are using this release
$stmt = "UPDATE\n {{%issue}}\n SET\n iss_pre_id=0\n WHERE\n iss_pre_id IN ({$itemlist})";
try {
DB_Helper::getInstance()->query($stmt, $items);
} catch (DbException $e) {
return false;
}
$stmt = "DELETE FROM\n {{%project_release}}\n WHERE\n pre_id IN ({$itemlist})";
try {
DB_Helper::getInstance()->query($stmt, $items);
} catch (DbException $e) {
return false;
}
return true;
}
/**
* Method used to get the list of available statuses as an associative array
* in the style of (id => title)
*
* @param array|int $prj_id List of project IDs
* @param boolean $show_closed Whether to show closed context statuses or not
* @return array The list of statuses
*/
public static function getAssocStatusList($prj_id, $show_closed = true)
{
if (!is_array($prj_id)) {
$prj_id = array($prj_id);
}
$stmt = 'SELECT
sta_id,
sta_title
FROM
{{%status}},
{{%project_status}}
WHERE
prs_prj_id IN (' . DB_Helper::buildList($prj_id) . ') AND
prs_sta_id=sta_id';
if (!$show_closed) {
$stmt .= ' AND sta_is_closed=0 ';
}
$stmt .= '
ORDER BY
sta_rank ASC';
try {
$res = DB_Helper::getInstance()->getPair($stmt, $prj_id);
} catch (DbException $e) {
return '';
}
return $res;
}
/**
* Method used to remove the project associations for a given
* news entry.
*
* @param integer $nws_id The news ID
* @param integer $prj_id The project ID
* @return boolean
*/
public function removeProjectAssociations($nws_id, $prj_id = false)
{
if (!is_array($nws_id)) {
$nws_id = array($nws_id);
}
$items = DB_Helper::buildList($nws_id);
$stmt = "DELETE FROM\n {{%project_news}}\n WHERE\n prn_nws_id IN ({$items})";
$params = $nws_id;
if ($prj_id) {
$stmt .= ' AND prn_prj_id=?';
$params[] = $prj_id;
}
try {
DB_Helper::getInstance()->query($stmt, $params);
} catch (DbException $e) {
return false;
}
return true;
}
/**
* Method used to remove all rows associated with a set of
* subscription IDs
*
* @param array $items The list of subscription IDs
* @return boolean
*/
public static function remove($items)
{
$itemlist = DB_Helper::buildList($items);
$stmt = "SELECT\n sub_iss_id\n FROM\n {{%subscription}}\n WHERE\n sub_id IN ({$itemlist})";
$issue_id = DB_Helper::getInstance()->getOne($stmt, $items);
$usr_id = Auth::getUserID();
$user_fullname = User::getFullName($usr_id);
$htt_id = History::getTypeID('notification_removed');
foreach ($items as $sub_id) {
$subscriber = self::getSubscriber($sub_id);
$stmt = 'DELETE FROM
{{%subscription}}
WHERE
sub_id=?';
DB_Helper::getInstance()->query($stmt, array($sub_id));
$stmt = 'DELETE FROM
{{%subscription_type}}
WHERE
sbt_sub_id=?';
DB_Helper::getInstance()->query($stmt, array($sub_id));
History::add($issue_id, $usr_id, $htt_id, 'Notification list entry ({email}) removed by {user}', array('email' => $subscriber, 'user' => $user_fullname));
}
Issue::markAsUpdated($issue_id);
return true;
}
