/**
* Builds an array containing the filters value and condition
*
* @param string $value Initial value
* @param string $condition Initial condition e.g. LIKE, =
* @param string $eval How the value should be handled
*
* @return array (value condition)
*/
public function getFilterValue($value, $condition, $eval)
{
$condition = JString::strtolower($condition);
$this->escapeQueryValue($condition, $value);
$db = FabrikWorker::getDbo();
if (is_array($value)) {
// Ranged search
list($value, $condition) = $this->getRangedFilterValue($value, $condition);
} else {
switch ($condition) {
case 'notequals':
case '<>':
$condition = "<>";
// 2 = sub-query so don't quote
$value = $eval == FABRIKFILTER_QUERY ? '(' . $value . ')' : $db->q($value);
break;
case 'equals':
case '=':
$condition = "=";
$value = $eval == FABRIKFILTER_QUERY ? '(' . $value . ')' : $db->q($value);
break;
case 'begins':
case 'begins with':
$condition = "LIKE";
$value = $eval == FABRIKFILTER_QUERY ? '(' . $value . ')' : $db->q($value . '%');
break;
case 'ends':
case 'ends with':
// @TODO test this with subquery
$condition = "LIKE";
$value = $eval == FABRIKFILTER_QUERY ? '(' . $value . ')' : $db->q('%' . $value);
break;
case 'contains':
case 'like':
// @TODO test this with subquery
$condition = "LIKE";
$value = $eval == FABRIKFILTER_QUERY ? '(' . $value . ')' : $db->q('%' . $value . '%');
break;
case '>':
case '>':
case 'greaterthan':
$condition = '>';
break;
case '<':
case '<':
case 'lessthan':
$condition = '<';
break;
case '>=':
case '>=':
case 'greaterthanequals':
$condition = '>=';
break;
case '<=':
case '<=':
case 'lessthanequals':
$condition = '<=';
break;
case 'in':
$condition = 'IN';
$value = FabrikString::safeQuote($value, true);
$value = $eval == FABRIKFILTER_QUERY ? '(' . $value . ')' : '(' . $value . ')';
break;
case 'not_in':
$condition = 'NOT IN';
$value = FabrikString::safeQuote($value, true);
$value = $eval == FABRIKFILTER_QUERY ? '(' . $value . ')' : '(' . $value . ')';
break;
}
switch ($condition) {
case '>':
case '<':
case '>=':
case '<=':
if ($eval == FABRIKFILTER_QUERY) {
$value = '(' . $value . ')';
} else {
if (!is_numeric($value)) {
$value = $db->q($value);
}
}
break;
}
// $$$ hugh - if 'noquotes' (3) selected, strip off the quotes again!
if ($eval == FABRKFILTER_NOQUOTES) {
// $$$ hugh - darn, this is stripping the ' of the end of things like "select & from foo where bar = '123'"
$value = JString::ltrim($value, "'");
$value = JString::rtrim($value, "'");
}
if ($condition == '=' && $value == "'_null_'") {
$condition = " IS NULL ";
$value = '';
}
}
return array($value, $condition);
}
/**
* Create the sql query used to get the possible selectable value/labels used to create
* the drop-down/checkboxes
*
* @param array $data data
* @param bool $incWhere include where
* @param array $opts query options
*
* @return mixed JDatabaseQuery or false if query can't be built
*/
protected function buildQuery($data = array(), $incWhere = true, $opts = array())
{
$input = $this->app->input;
$sig = isset($this->autocomplete_where) ? $this->autocomplete_where . '.' . $incWhere : $incWhere;
$sig .= '.' . serialize($opts);
$repeatCounter = FArrayHelper::getValue($opts, 'repeatCounter', 0);
$db = FabrikWorker::getDbo();
if (isset($this->sql[$sig])) {
return $this->sql[$sig];
}
$params = $this->getParams();
$watch = $this->getWatchFullName();
$whereVal = null;
$groups = $this->getFormModel()->getGroupsHiarachy();
$formModel = $this->getFormModel();
$watchElement = $this->getWatchElement();
// Test for ajax update
if ($input->get('fabrik_cascade_ajax_update') == 1) {
// Allow for multiple values - e.g. when observing a db join rendered as a checkbox
$whereVal = $input->get('v', array(), 'array');
} else {
if (isset($formModel->data) || isset($formModel->formData)) {
$watchOpts = array('raw' => 1);
if (isset($formModel->data)) {
if ($watchElement->isJoin()) {
$id = $watchElement->getFullName(true, false) . '_id';
$whereVal = FArrayHelper::getValue($formModel->data, $id);
} else {
$whereVal = $watchElement->getValue($formModel->data, $repeatCounter, $watchOpts);
}
} else {
/*
* If we're running onAfterProcess, formData will have short names in it, which means getValue()
* won't find the watch element, as it's looking for full names. So if it exists, use formDataWithTableName.
*/
if (is_array($formModel->formDataWithTableName) && array_key_exists($watch, $formModel->formDataWithTableName)) {
$whereVal = $watchElement->getValue($formModel->formDataWithTableName, $repeatCounter, $watchOpts);
} else {
$whereVal = $watchElement->getValue($formModel->formData, $repeatCounter, $watchOpts);
}
}
// $$$ hugh - if not set, set to '' to avoid selecting entire table
if (!isset($whereVal)) {
$whereVal = '';
}
} else {
// $$$ hugh - probably rendering table view ...
$watchRaw = $watch . '_raw';
if (isset($data[$watchRaw])) {
$whereVal = $data[$watchRaw];
} else {
// $$$ hugh ::sigh:: might be coming in via swapLabelsForvalues in pre_process phase
// and join array in data will have been flattened. So try regular element name for watch.
$noJoinWatchRaw = $watchElement->getFullName(true, false) . '_raw';
if (isset($data[$noJoinWatchRaw])) {
$whereVal = $data[$noJoinWatchRaw];
} else {
// $$$ hugh - if watched element has no value, we have been selecting all rows from CDD table
// but should probably select none.
// Unless its a cdd autocomplete list filter - seems sensible to populate that with the values matching the search term
if ($this->app->input->get('method') !== 'autocomplete_options') {
$whereVal = '';
}
}
}
}
}
$where = '';
$whereKey = $params->get('cascadingdropdown_key');
if (!is_null($whereVal) && $whereKey != '') {
$whereBits = strstr($whereKey, '___') ? explode('___', $whereKey) : explode('.', $whereKey);
$whereKey = array_pop($whereBits);
if (is_array($whereVal)) {
foreach ($whereVal as &$v) {
// Jaanus: Solving bug: imploded arrays when chbx in repeated group
if (is_array($v)) {
foreach ($v as &$vchild) {
$vchild = FabrikString::safeQuote($vchild);
}
$v = implode(',', $v);
} else {
$v = FabrikString::safeQuote($v);
}
}
// Jaanus: if count of where values is 0 or if there are no letters or numbers, only commas in imploded array
$where .= count($whereVal) == 0 || !preg_match('/\\w/', implode(',', $whereVal)) ? '4 = -4' : $whereKey . ' IN ' . '(' . str_replace(',,', ',\'\',', implode(',', $whereVal)) . ')';
} else {
$where .= $whereKey . ' = ' . $db->quote($whereVal);
}
}
$filter = $params->get('cascadingdropdown_filter');
if (!empty($this->autocomplete_where)) {
$where .= $where !== '' ? ' AND ' . $this->autocomplete_where : $this->autocomplete_where;
}
/* $$$ hugh - temporary hack to work around this issue:
* http://fabrikar.com/forums/showthread.php?p=71288#post71288
* ... which is basically that if they are using {placeholders} in their
* filter query, there's no point trying to apply that filter if we
* aren't in form view, for instance when building a search filter
* or in table view when the cdd is in a repeat group, 'cos there won't
* be any {placeholder} data to use.
* So ... for now, if the filter contains {...}, and view!=form ... skip it
* $$$ testing fix for the bandaid, ccd JS should not be submitting data from form
*/
if (trim($filter) != '') {
$where .= $where == '' ? ' ' : ' AND ';
$where .= $filter;
}
$w = new FabrikWorker();
// $$$ hugh - add some useful stuff to search data
$placeholders = is_null($whereVal) ? array() : array('whereval' => $whereVal, 'wherekey' => $whereKey);
$join = $this->getJoin();
$where = $this->parseThisTable($where, $join);
$data = array_merge($data, $placeholders);
$where = $w->parseMessageForRepeats($where, $data, $this, $repeatCounter);
$where = $w->parseMessageForPlaceHolder($where, $data);
$table = $this->getDbName();
$key = $this->queryKey();
$orderBy = 'text';
$tables = $this->getFormModel()->getLinkedFabrikLists($params->get('join_db_name'));
$listModel = JModelLegacy::getInstance('List', 'FabrikFEModel');
$val = $params->get('cascadingdropdown_label_concat');
if (!empty($val)) {
$val = $this->parseThisTable($val, $join);
$val = $w->parseMessageForPlaceHolder($val, $data);
$val = 'CONCAT_WS(\'\', ' . $val . ')';
$orderBy = $val;
} else {
$val = FabrikString::safeColName($params->get($this->labelParam));
$val = preg_replace("#^`({$table})`\\.#", $db->qn($join->table_join_alias) . '.', $val);
foreach ($tables as $tid) {
$listModel->setId($tid);
$listModel->getTable();
$formModel = $this->getFormModel();
$formModel->getGroupsHiarachy();
$orderBy = $val;
// See if any of the tables elements match the db joins val/text
foreach ($groups as $groupModel) {
$elementModels = $groupModel->getPublishedElements();
foreach ($elementModels as $elementModel) {
$element = $elementModel->element;
if ($element->name == $val) {
$val = $elementModel->modifyJoinQuery($val);
}
}
}
}
}
$val = str_replace($db->qn($table), $db->qn($join->table_join_alias), $val);
$query = $db->getQuery(true);
$query->select('DISTINCT(' . $key . ') AS value, ' . $val . 'AS text');
$desc = $params->get('cdd_desc_column', '');
if ($desc !== '') {
$query->select(FabrikString::safeColName($desc) . ' AS description');
}
$query->from($db->qn($table) . ' AS ' . $db->qn($join->table_join_alias));
$query = $this->buildQueryJoin($query);
$where = FabrikString::rtrimword($where);
if ($where !== '') {
$query->where($where);
}
if (!JString::stristr($where, 'order by')) {
$query->order($orderBy . ' ASC');
}
$this->sql[$sig] = $query;
FabrikHelperHTML::debug((string) $this->sql[$sig]);
return $this->sql[$sig];
}
