public static function onCCK_StoragePrepareSearch(&$field, $match, $value, $name, $name2, $target)
{
$sql = '';
$TA = '(::' . $name . $name2 . '::)';
$TZ = '(::/' . $name . $name2 . '::)';
//if ( !empty( $field->match_target ) && $field->match_target != '~' ) {
// $target = self::_getTarget( $name, $field->match_target );
// $TA = '';
// $TZ = '';
//}
switch ($match) {
case 'exact':
$sql = !$TA ? $target . ' = "' . $TA . $value . $TZ . '"' : $target . ' REGEXP "' . $TA . $value . $TZ . '"';
break;
case 'empty':
$sql = $target . ' REGEXP "' . $TA . $TZ . '"';
break;
case 'alpha':
$sql = $target . ' REGEXP "' . $TA . $value . '.*' . $TZ . '"';
break;
case 'zeta':
/* Zeta is not the last letter of Greek alphabet but.. this won't be an issue here. */
$sql = $target . ' REGEXP "' . $TA . '.*' . $value . $TZ . '"';
break;
case 'any':
$separator = $field->match_value ? $field->match_value : ' ';
$values = explode($separator, $value);
if (count($values)) {
$fragments = array();
foreach ($values as $v) {
if (strlen($v) > 0) {
$fragments[] = $target . ' REGEXP "' . $TA . '.*' . $v . '.*' . $TZ . '"';
}
}
if (count($fragments)) {
$sql = '((' . implode(') OR (', $fragments) . '))';
}
}
break;
case 'any_exact':
$separator = $field->match_value ? $field->match_value : ' ';
$values = explode($separator, $value);
if (count($values)) {
$fragments = array();
foreach ($values as $v) {
if (strlen($v) > 0) {
$fragments[] = !$TA ? $target . ' = "' . $TA . $v . $TZ . '"' : $target . ' REGEXP "' . $TA . $v . $TZ . '"';
}
}
if (count($fragments)) {
$sql = '((' . implode(') OR (', $fragments) . '))';
}
}
break;
case 'each':
case 'each_exact':
$separator = $field->match_value ? $field->match_value : ' ';
$values = explode($separator, $value);
if (count($values)) {
$fragments = array();
if ($match == 'each_exact') {
foreach ($values as $v) {
if (strlen($v) > 0) {
$fragments[] = (!$TA ? $target . ' = "' . $TA . $v . $TZ . '"' : $target . ' REGEXP "' . $TA . $v . $TZ . '"') . $target . ' REGEXP "' . $TA . $v . $separator . '.*' . $TZ . '"' . $target . ' REGEXP "' . $TA . '.*' . $separator . $v . $separator . '.*' . $TZ . '"' . $target . ' REGEXP "' . $TA . '.*' . $separator . $v . $TZ . '"';
}
}
} else {
foreach ($values as $v) {
if (strlen($v) > 0) {
$fragments[] = $target . ' REGEXP "' . $TA . '.*' . $v . '.*' . $TZ . '"';
}
}
}
if (count($fragments)) {
$sql = '((' . implode(') AND (', $fragments) . '))';
}
}
break;
case 'date_past_only':
case 'date_past':
case 'date_future':
case 'date_future_only':
JFactory::getApplication()->enqueueMessage(JText::_('COM_CCK_DATE_AND_TIME_MATCH_ONLY_WITH_STANDARD'), 'notice');
break;
case 'nested_exact':
$table = $field->match_options ? $field->match_options->get('table', $field->storage_table) : $field->storage_table;
$values = JCckDevHelper::getBranch($table, $value);
if (count($values)) {
$fragments = array();
foreach ($values as $v) {
if ($v != '') {
$fragments[] = !$TA ? $target . ' = "' . $TA . $v . $TZ . '"' : $target . ' REGEXP "' . $TA . $v . $TZ . '"';
}
}
if (count($fragments)) {
$sql = '((' . implode(') OR (', $fragments) . '))';
}
}
break;
case 'num_higher':
$range = '';
$max = $field->match_value ? $field->match_value : 99999;
if ($value <= $max) {
$range = CCK_List::generateRange($value, $max);
}
$range = '[[:<:]](' . $range . ')[[:>:]]';
$sql = $target . ' REGEXP "' . $TA . $range . $TZ . '"';
break;
case 'num_higher_only':
$range = '';
$max = $field->match_value ? $field->match_value : 99999;
if ($value <= $max) {
$range = CCK_List::generateRange($value, $max);
}
$range = '[[:<:]](' . $range . ')[[:>:]]';
$sql = $target . ' REGEXP "' . $TA . $range . $TZ . '"';
break;
case 'num_lower':
$range = '';
$min = $field->match_value ? $field->match_value : 0;
if ($value >= $min) {
$range = CCK_List::generateRange($min, $value);
}
$range = '[[:<:]](' . $range . ')[[:>:]]';
$sql = $target . ' REGEXP "' . $TA . $range . $TZ . '"';
break;
case 'num_lower_only':
$range = '';
$min = $field->match_value ? $field->match_value : 0;
if ($value >= $min) {
$range = CCK_List::generateRange($min, $value);
}
$range = '[[:<:]](' . $range . ')[[:>:]]';
$sql = $target . ' REGEXP "' . $TA . $range . $TZ . '"';
break;
case 'not_alpha':
$sql = $target . ' NOT REGEXP "' . $TA . $value . '.*' . $TZ . '"';
break;
case 'not_any_exact':
// todo
break;
case 'not_zeta':
/* Zeta is not the last letter of Greek alphabet but.. this won't be an issue here. */
$sql = $target . ' NOT REGEXP "' . $TA . '.*' . $value . $TZ . '"';
break;
case 'not_empty':
$sql = $target . ' NOT REGEXP "' . $TA . $TZ . '"';
break;
case 'not_equal':
$sql = $target . ' NOT REGEXP "' . $TA . $value . $TZ . '"';
break;
case 'not_like':
$sql = $target . ' NOT REGEXP "' . $TA . '.*' . $value . '.*' . $TZ . '"';
break;
case 'not_null':
$sql = $target . ' NOT REGEXP "' . $TA . '0' . $TZ . '"';
break;
case 'is_null':
// todo
break;
case 'is_not_null':
// todo
break;
case 'radius_higher':
case 'radius_lower':
JFactory::getApplication()->enqueueMessage(JText::_('COM_CCK_RADIUS_MATCH_ONLY_WITH_STANDARD'), 'notice');
break;
case 'none':
return;
break;
default:
$sql = $target . ' REGEXP "' . $TA . '.*' . $value . '.*' . $TZ . '"';
break;
}
return $sql;
}
public static function onCCK_StoragePrepareSearch(&$field, $match, $value, $name, $name2, $target, $fields = array(), &$config = array())
{
$sql = '';
switch ($match) {
case 'exact':
$sql = $target . ' = ' . JCckDatabase::quote($value);
break;
case 'empty':
$sql = $target . ' = ""';
break;
case 'alpha':
$sql = $target . ' LIKE ' . JCckDatabase::quote(JCckDatabase::escape($value, true) . '%', false);
break;
case 'zeta':
/* Zeta is not the last letter of Greek alphabet but.. this won't be an issue here. */
$sql = $target . ' LIKE ' . JCckDatabase::quote('%' . JCckDatabase::escape($value, true), false);
break;
case 'any':
$separator = $field->match_value ? $field->match_value : ' ';
$values = explode($separator, $value);
if (count($values)) {
$fragments = array();
foreach ($values as $v) {
if (strlen($v) > 0) {
$fragments[] = $target . ' LIKE ' . JCckDatabase::quote('%' . JCckDatabase::escape($v, true) . '%', false);
}
}
if (count($fragments)) {
$sql = '((' . implode(') OR (', $fragments) . '))';
}
}
break;
case 'any_exact':
$separator = $field->match_value ? $field->match_value : ' ';
$values = explode($separator, $value);
if (count($values)) {
$fragments = array();
$var_type = $field->match_options ? $field->match_options->get('var_type', 1) : 1;
if (!$var_type) {
foreach ($values as $v) {
if (strlen($v) > 0) {
$fragments[] = $v;
}
}
} else {
foreach ($values as $v) {
if (strlen($v) > 0) {
$fragments[] = JCckDatabase::quote($v);
}
}
}
if (count($fragments)) {
$sql = $target . ' IN (' . implode(',', $fragments) . ')';
}
}
break;
case 'each':
case 'each_exact':
$separator = $field->match_value ? $field->match_value : ' ';
$values = explode($separator, $value);
if (count($values)) {
$fragments = array();
if ($match == 'each_exact') {
foreach ($values as $v) {
if (strlen($v) > 0) {
$fragments[] = $target . ' = ' . JCckDatabase::quote($v) . ' OR ' . $target . ' LIKE ' . JCckDatabase::quote(JCckDatabase::escape($v, true) . $separator . '%', false) . ' OR ' . $target . ' LIKE ' . JCckDatabase::quote('%' . $separator . JCckDatabase::escape($v, true) . $separator . '%', false) . ' OR ' . $target . ' LIKE ' . JCckDatabase::quote('%' . $separator . JCckDatabase::escape($v, true), false);
}
}
} else {
foreach ($values as $v) {
if (strlen($v) > 0) {
$fragments[] = $target . ' LIKE ' . JCckDatabase::quote('%' . JCckDatabase::escape($v, true) . '%', false);
}
}
}
if (count($fragments)) {
$sql = '((' . implode(') AND (', $fragments) . '))';
}
}
break;
case 'date_past_only':
$sql = $target . ' < ' . JCckDatabase::quote($value);
break;
case 'date_past':
$sql = $target . ' <= ' . JCckDatabase::quote($value);
break;
case 'date_future':
$sql = $target . ' >= ' . JCckDatabase::quote($value);
break;
case 'date_future_only':
$sql = $target . ' > ' . JCckDatabase::quote($value);
break;
case 'nested_exact':
$table = $field->match_options ? $field->match_options->get('table', $field->storage_table) : $field->storage_table;
$column = 'id';
$values = JCckDevHelper::getBranch($table, $value);
if ($column != 'id') {
if (count($values)) {
$fragments = array();
foreach ($values as $v) {
if ($v != '') {
$fragments[] = JCckDatabase::quote($v);
}
}
if (count($fragments)) {
$sql = $target . ' IN (' . implode(',', $fragments) . ')';
}
}
} else {
if (count($values)) {
$sql = $target . ' IN (' . implode(',', $values) . ')';
}
}
break;
case 'num_higher':
$sql = $target . ' >= ' . JCckDatabase::quote($value);
break;
case 'num_higher_only':
$sql = $target . ' > ' . JCckDatabase::quote($value);
break;
case 'num_lower':
$sql = $target . ' <= ' . JCckDatabase::quote($value);
break;
case 'num_lower_only':
$sql = $target . ' < ' . JCckDatabase::quote($value);
break;
case 'not_alpha':
$sql = $target . ' NOT LIKE ' . JCckDatabase::quote(JCckDatabase::escape($value, true) . '%', false);
break;
case 'not_any_exact':
$separator = $field->match_value ? $field->match_value : ' ';
$values = explode($separator, $value);
if (count($values)) {
$fragments = array();
foreach ($values as $v) {
if (strlen($v) > 0) {
$fragments[] = JCckDatabase::quote($v);
}
}
if (count($fragments)) {
$sql = $target . ' NOT IN (' . implode(',', $fragments) . ')';
}
}
break;
case 'not_zeta':
/* Zeta is not the last letter of Greek alphabet but.. this won't be an issue here. */
$sql = $target . ' NOT LIKE ' . JCckDatabase::quote('%' . JCckDatabase::escape($value, true), false);
break;
case 'not_empty':
$sql = $target . ' != ""';
break;
case 'not_equal':
$sql = $target . ' != ' . JCckDatabase::quote($value);
break;
case 'not_like':
$sql = $target . ' NOT LIKE ' . JCckDatabase::quote('%' . JCckDatabase::escape($value, true) . '%', false);
break;
case 'not_null':
$sql = $target . ' != "0"';
break;
case 'is_null':
$sql = $target . ' IS NULL';
break;
case 'is_not_null':
$sql = $target . ' IS NOT NULL';
break;
case 'radius_higher':
case 'radius_lower':
$t = explode('.', $target);
$f_lat = $field->match_options->get('fieldname1', '');
$f_lng = $field->match_options->get('fieldname2', '');
$f_rad = $field->match_options->get('fieldname3', '');
$lat = isset($fields[$f_lat]) ? $fields[$f_lat]->value : '';
$lng = isset($fields[$f_lng]) ? $fields[$f_lng]->value : '';
$s_lat = isset($fields[$f_lat]->storage_field) && $fields[$f_lat]->storage_field ? $fields[$f_lat]->storage_field : $f_lat;
$s_lng = isset($fields[$f_lng]->storage_field) && $fields[$f_lng]->storage_field ? $fields[$f_lng]->storage_field : $f_lng;
if ($lat != '' && $lng != '') {
$alias = 'distance';
$mod = $field->match_options->get('var_unit', '1') ? '' : '*1.609344';
$radius = isset($fields[$f_rad]) ? $fields[$f_rad]->value : '';
$sign = $match == 'radius_higher' ? '>' : '<';
$config['query_parts']['select'][] = '(((acos(sin((' . (double) $lat . '*pi()/180)) * sin((' . $t[0] . '.' . $s_lat . '*pi()/180))+cos((' . (double) $lat . '*pi()/180)) * cos((' . $t[0] . '.' . $s_lat . '*pi()/180)) * cos(((' . (double) $lng . '- ' . $t[0] . '.' . $s_lng . ')*pi()/180))))*180/pi())*60*1.1515' . $mod . ') AS ' . $alias;
if ((int) $radius > 0) {
$config['query_parts']['having'][] = $alias . ' ' . $sign . ' ' . $radius;
$sql = '()';
// todo
} else {
$lat = number_format($lat, 8);
$lng = number_format($lng, 8);
$sql = '(' . $t[0] . '.' . $s_lat . ' = ' . JCckDatabase::quote($lat) . ' AND ' . $t[0] . '.' . $s_lng . ' = ' . JCckDatabase::quote($lng) . ')';
}
} else {
$sql = '()';
// todo
}
break;
case 'none':
return;
break;
default:
$sql = $target . ' LIKE ' . JCckDatabase::quote('%' . JCckDatabase::escape($value, true) . '%', false);
break;
}
return $sql;
}
public static function onCCK_StoragePrepareSearch(&$field, $match, $value, $name, $name2, $target)
{
$sql = '';
switch ($match) {
case 'exact':
$sql = $target . ' = ' . JCckDatabase::quote($value);
break;
case 'alpha':
$sql = $target . ' LIKE ' . JCckDatabase::quote(JCckDatabase::escape($value, true) . '%', false);
break;
case 'zeta':
/* Zeta is not the last letter of Greek alphabet but.. this won't be an issue here. */
$sql = $target . ' LIKE ' . JCckDatabase::quote('%' . JCckDatabase::escape($value, true), false);
break;
case 'any':
$separator = $field->match_value ? $field->match_value : ' ';
$values = explode($separator, $value);
if (count($values)) {
$fragments = array();
foreach ($values as $v) {
if (strlen($v) > 0) {
$fragments[] = $target . ' LIKE ' . JCckDatabase::quote('%' . JCckDatabase::escape($v, true) . '%', false);
}
}
if (count($fragments)) {
$sql = '((' . implode(') OR (', $fragments) . '))';
}
}
break;
case 'any_exact':
$separator = $field->match_value ? $field->match_value : ' ';
$values = explode($separator, $value);
if (count($values)) {
$fragments = array();
$var_type = $field->match_options ? $field->match_options->get('var_type', 1) : 1;
if (!$var_type) {
foreach ($values as $v) {
if (strlen($v) > 0) {
$fragments[] = $v;
}
}
} else {
foreach ($values as $v) {
if (strlen($v) > 0) {
$fragments[] = JCckDatabase::quote($v);
}
}
}
if (count($fragments)) {
$sql = $target . ' IN (' . implode(',', $fragments) . ')';
}
}
break;
case 'each':
$separator = $field->match_value ? $field->match_value : ' ';
$values = explode($separator, $value);
if (count($values)) {
$fragments = array();
foreach ($values as $v) {
if (strlen($v) > 0) {
$fragments[] = $target . ' LIKE ' . JCckDatabase::quote('%' . JCckDatabase::escape($v, true) . '%', false);
}
}
if (count($fragments)) {
$sql = '((' . implode(') AND (', $fragments) . '))';
}
}
break;
case 'date_past_only':
$sql = $target . ' < ' . JCckDatabase::quote($value);
break;
case 'date_past':
$sql = $target . ' <= ' . JCckDatabase::quote($value);
break;
case 'date_future':
$sql = $target . ' >= ' . JCckDatabase::quote($value);
break;
case 'date_future_only':
$sql = $target . ' > ' . JCckDatabase::quote($value);
break;
case 'nested_exact':
$table = $field->match_options ? $field->match_options->get('table', $field->storage_table) : $field->storage_table;
$column = 'id';
$values = JCckDevHelper::getBranch($table, $value);
if ($column != 'id') {
if (count($values)) {
$fragments = array();
foreach ($values as $v) {
if ($v != '') {
$fragments[] = JCckDatabase::quote($v);
}
}
if (count($fragments)) {
$sql = $target . ' IN (' . implode(',', $fragments) . ')';
}
}
} else {
if (count($values)) {
$sql = $target . ' IN (' . implode(',', $values) . ')';
}
}
break;
case 'num_higher':
$sql = $target . ' >= ' . JCckDatabase::quote($value);
break;
case 'num_higher_only':
$sql = $target . ' > ' . JCckDatabase::quote($value);
break;
case 'num_lower':
$sql = $target . ' <= ' . JCckDatabase::quote($value);
break;
case 'num_lower_only':
$sql = $target . ' < ' . JCckDatabase::quote($value);
break;
case 'not_alpha':
$sql = $target . ' NOT LIKE ' . JCckDatabase::quote(JCckDatabase::escape($value, true) . '%', false);
break;
case 'not_any_exact':
$separator = $field->match_value ? $field->match_value : ' ';
$values = explode($separator, $value);
if (count($values)) {
$fragments = array();
foreach ($values as $v) {
if (strlen($v) > 0) {
$fragments[] = JCckDatabase::quote($v);
}
}
if (count($fragments)) {
$sql = $target . ' NOT IN (' . implode(',', $fragments) . ')';
}
}
break;
case 'not_zeta':
/* Zeta is not the last letter of Greek alphabet but.. this won't be an issue here. */
$sql = $target . ' NOT LIKE ' . JCckDatabase::quote('%' . JCckDatabase::escape($value, true), false);
break;
case 'not_empty':
$sql = $target . ' != ""';
break;
case 'not_equal':
$sql = $target . ' != ' . JCckDatabase::quote($value);
break;
case 'not_like':
$sql = $target . ' NOT LIKE ' . JCckDatabase::quote('%' . JCckDatabase::escape($value, true) . '%', false);
break;
case 'not_null':
$sql = $target . ' != "0"';
break;
case 'is_null':
$sql = $target . ' IS NULL';
break;
case 'is_not_null':
$sql = $target . ' IS NOT NULL';
break;
case 'none':
return;
break;
default:
$sql = $target . ' LIKE ' . JCckDatabase::quote('%' . JCckDatabase::escape($value, true) . '%', false);
break;
}
return $sql;
}
