/**
* processes searches and sorts to build the listing query
*
* @param string $submit the value of the submit field
*/
private static function _process_search($submit)
{
switch ($submit) {
case self::$i18n['sort']:
case self::$i18n['filter']:
case self::$i18n['search']:
self::$list_query = 'SELECT * FROM ' . Participants_Db::$participants_table . ' p ';
$delimiter = array("'", "'");
switch (self::$filter['operator']) {
case 'LIKE':
$operator = 'LIKE';
$delimiter = array('"%', '%"');
break;
case 'gt':
$operator = '>';
break;
case 'lt':
$operator = '<';
break;
default:
$operator = mysql_real_escape_string(self::$filter['operator']);
}
if (self::$filter['value'] !== '') {
// if the field searched is a "date" field, convert the search string to a date
$field_atts = Participants_Db::get_field_atts(self::$filter['search_field']);
$value = self::$filter['value'];
if ($field_atts->form_element == 'timestamp') {
$value = self::$filter['value'];
$value2 = false;
if (strpos(self::$filter['value'], ' to ')) {
list($value, $value2) = explode('to', self::$filter['value']);
}
$value = Participants_Db::parse_date($value, $field_atts, false);
if ($value2) {
$value2 = Participants_Db::parse_date($value2, $field_atts, $field_atts->form_element == 'date');
}
if ($value !== false) {
$stored_date = "DATE(p." . mysql_real_escape_string(self::$filter['search_field']) . ")";
if ($value2 !== false and !empty($value2)) {
self::$list_query .= " WHERE " . $stored_date . " > DATE_ADD(FROM_UNIXTIME(0), interval " . mysql_real_escape_string($value) . " second) AND " . $stored_date . " < DATE_ADD(FROM_UNIXTIME(0), interval " . mysql_real_escape_string($value2) . " second)";
} else {
if ($operator == 'LIKE') {
$operator = '=';
}
self::$list_query .= " WHERE " . $stored_date . " " . $operator . " DATE_ADD(FROM_UNIXTIME(0), interval " . mysql_real_escape_string($value) . " second) ";
}
}
} elseif ($field_atts->form_element == 'date') {
$value = self::$filter['value'];
$value2 = false;
if (strpos(self::$filter['value'], ' to ')) {
list($value, $value2) = explode('to', self::$filter['value']);
}
$value = Participants_Db::parse_date($value, $field_atts, true);
if ($value2) {
$value2 = Participants_Db::parse_date($value2, $field_atts, $field_atts->form_element == 'date');
}
if ($value !== false) {
$stored_date = "CAST(p." . mysql_real_escape_string(self::$filter['search_field']) . " AS SIGNED)";
if ($value2 !== false and !empty($value2)) {
self::$list_query .= " WHERE " . $stored_date . " > CAST(" . mysql_real_escape_string($value) . " AS SIGNED) AND " . $stored_date . " < CAST(" . mysql_real_escape_string($value2) . " AS SIGNED)";
} else {
if ($operator == 'LIKE') {
$operator = '=';
}
self::$list_query .= " WHERE " . $stored_date . " " . $operator . " CAST(" . mysql_real_escape_string($value) . " AS SIGNED)";
}
}
} else {
self::$list_query .= ' WHERE p.' . mysql_real_escape_string(self::$filter['search_field']) . ' ' . $operator . " " . $delimiter[0] . mysql_real_escape_string($value) . $delimiter[1] . " ";
}
}
// add the sorting
self::$list_query .= ' ORDER BY p.' . mysql_real_escape_string(self::$filter['sortBy']) . ' ' . mysql_real_escape_string(self::$filter['ascdesc']);
// go back to the first page to display the newly sorted/filtered list
if (isset($_POST['submit-button'])) {
$_GET[self::$list_page] = 1;
}
break;
case self::$i18n['clear']:
self::$filter['value'] = '';
self::$filter['search_field'] = 'none';
// go back to the first page if the search has just been submitted
$_GET[self::$list_page] = 1;
self::$filter['submit-button'] = '';
default:
self::$list_query = 'SELECT * FROM ' . Participants_Db::$participants_table . ' ORDER BY `' . mysql_real_escape_string(self::$filter['sortBy']) . '` ' . mysql_real_escape_string(self::$filter['ascdesc']);
}
}
/**
* processes searches and sorts to build the listing query
*
* @param string $submit the value of the submit field
*/
private static function _process_search()
{
$submit = filter_input(INPUT_POST, 'submit-button', FILTER_SANITIZE_STRING);
switch ($submit) {
case self::$i18n['clear']:
for ($i = 0; $i < self::$filter['list_filter_count']; $i++) {
self::$filter['search'][$i] = self::$default_filter['search'][0];
}
self::save_filter(self::$filter);
case self::$i18n['sort']:
case self::$i18n['filter']:
case self::$i18n['search']:
// go back to the first page to display the newly sorted/filtered list
$_GET[self::$list_page] = 1;
default:
self::$list_query = 'SELECT * FROM ' . Participants_Db::$participants_table . ' p ';
if (count(self::$filter['search']) === 1 && (self::$filter['search'][0]['search_field'] === 'none' || self::$filter['search'][0]['search_field'] === '')) {
// do nothing, no search performed
} else {
self::$list_query .= 'WHERE ';
for ($i = 0; $i <= count(self::$filter['search']) - 1; $i++) {
if (self::$filter['search'][$i]['search_field'] !== 'none' && self::$filter['search'][$i]['search_field'] !== '') {
self::_add_where_clause(self::$filter['search'][$i]);
}
if ($i === count(self::$filter['search']) - 1) {
if (self::$inparens) {
self::$list_query .= ') ';
self::$inparens = false;
}
} elseif (self::$filter['search'][$i + 1]['search_field'] !== 'none' && self::$filter['search'][$i + 1]['search_field'] !== '') {
self::$list_query .= self::$filter['search'][$i]['logic'] . ' ';
}
}
// if no where clauses were added, remove the WHERE operator
if (preg_match('/WHERE $/', self::$list_query)) {
self::$list_query = str_replace('WHERE', '', self::$list_query);
}
}
// add the sorting
self::$list_query .= ' ORDER BY p.' . esc_sql(self::$filter['sortBy']) . ' ' . esc_sql(self::$filter['ascdesc']);
}
}
