/**
* prepares the error messages and CSS for a main database submission
*
* @return array indexed array of error messages
*/
public function get_validation_errors()
{
// check for errors
if (!$this->errors_exist()) {
return array();
}
$output = '';
$error_messages = array();
$this->error_CSS = array();
foreach ($this->errors as $field => $error) {
$field_atts = Participants_Db::get_field_atts($field);
switch ($field_atts->form_element) {
case 'rich-text':
case 'text-area':
case 'textarea':
$element = 'textarea';
break;
case 'link':
case 'captcha':
$field_atts->name .= '[]';
case 'text':
case 'text-line':
case 'date':
$element = 'input';
break;
case 'image-upload':
case 'file-upload':
$element = 'input';
break;
default:
$element = false;
}
$this->error_CSS[] = '[class*="' . Participants_Db::$prefix . '"] [name="' . $field_atts->name . '"]';
if (isset($this->error_messages[$error])) {
$error_messages[] = $error == 'nonmatching' ? sprintf($this->error_messages[$error], $field_atts->title, Participants_Db::column_title($field_atts->validation)) : sprintf($this->error_messages[$error], $field_atts->title);
$this->error_class = Participants_Db::$prefix . 'error';
} else {
$error_messages[] = $error;
$this->error_class = empty($field) ? Participants_Db::$prefix . 'message' : Participants_Db::$prefix . 'error';
}
}
// $this->errors
return $error_messages;
}
/**
* 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 .= ' ';
}
/**
* 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']);
}
}
/**
* get the column form element type
*
*/
public function get_field_type($column)
{
$column_atts = Participants_Db::get_field_atts($column, '`form_element`,`default`');
return $column_atts->form_element;
}
