/**
* adds a where clause to the query
*
* the filter set has the structure:
* 'search_field' => name of the field to search on
* 'value' => search term
* 'operator' => mysql operator
* 'logic' => join to next statement (AND or OR)
*
* @param array $filter_set
* @return null
*/
protected static function _add_where_clause($filter_set)
{
if ($filter_set['logic'] === 'OR' && !self::$inparens) {
self::$list_query .= ' (';
self::$inparens = true;
}
$filter_set['value'] = str_replace('*', '%', $filter_set['value']);
$delimiter = array("'", "'");
switch ($filter_set['operator']) {
case 'gt':
$operator = '>';
break;
case 'lt':
$operator = '<';
break;
case '=':
$operator = '=';
if ($filter_set['value'] === '') {
$filter_set['value'] = 'null';
} elseif (strpos($filter_set['value'], '%') !== false) {
$operator = 'LIKE';
$delimiter = array("'", "'");
}
break;
case 'NOT LIKE':
case '!=':
case 'LIKE':
default:
$operator = esc_sql($filter_set['operator']);
if (stripos($operator, 'LIKE') !== false) {
$delimiter = array('"%', '%"');
}
if ($filter_set['value'] === '') {
$filter_set['value'] = 'null';
$operator = '<>';
} elseif (strpos($filter_set['value'], '%') !== false) {
$delimiter = array("'", "'");
}
}
// get the attributes of the field being searched
$field_atts = Participants_Db::get_field_atts($filter_set['search_field']);
$value = PDb_FormElement::get_title_value($filter_set['value'], $filter_set['search_field']);
if ($field_atts->form_element == 'timestamp') {
$value = $filter_set['value'];
$value2 = false;
if (strpos($filter_set['value'], ' to ')) {
list($value, $value2) = explode('to', $filter_set['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." . esc_sql($filter_set['search_field']) . ")";
if ($value2 !== false and !empty($value2)) {
self::$list_query .= " " . $stored_date . " > DATE_ADD(FROM_UNIXTIME(0), interval " . esc_sql($value) . " second) AND " . $stored_date . " < DATE_ADD(FROM_UNIXTIME(0), interval " . esc_sql($value2) . " second)";
} else {
if ($operator == 'LIKE') {
$operator = '=';
}
self::$list_query .= " " . $stored_date . " " . $operator . " DATE_ADD(FROM_UNIXTIME(0), interval " . esc_sql($value) . " second) ";
}
}
} elseif ($field_atts->form_element == 'date') {
$value = $filter_set['value'];
$value2 = false;
if (strpos($filter_set['value'], ' to ')) {
list($value, $value2) = explode('to', $filter_set['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." . esc_sql($filter_set['search_field']) . " AS SIGNED)";
if ($value2 !== false and !empty($value2)) {
self::$list_query .= " " . $stored_date . " > CAST(" . esc_sql($value) . " AS SIGNED) AND " . $stored_date . " < CAST(" . esc_sql($value2) . " AS SIGNED)";
} else {
if ($operator == 'LIKE') {
$operator = '=';
}
self::$list_query .= " " . $stored_date . " " . $operator . " CAST(" . esc_sql($value) . " AS SIGNED)";
}
}
} elseif ($filter_set['value'] === 'null') {
switch ($filter_set['operator']) {
case '<>':
case '!=':
case 'NOT LIKE':
self::$list_query .= ' (p.' . esc_sql($filter_set['search_field']) . ' IS NOT NULL AND p.' . esc_sql($filter_set['search_field']) . ' <> "")';
break;
case 'LIKE':
case '=':
default:
self::$list_query .= ' (p.' . esc_sql($filter_set['search_field']) . ' IS NULL OR p.' . esc_sql($filter_set['search_field']) . ' = "")';
break;
}
} else {
self::$list_query .= ' p.' . esc_sql($filter_set['search_field']) . ' ' . $operator . " " . $delimiter[0] . esc_sql($value) . $delimiter[1];
}
if ($filter_set['logic'] === 'AND' && self::$inparens) {
self::$list_query .= ') ';
self::$inparens = false;
}
self::$list_query .= ' ';
}
