/** * 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]; }