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