public function buildFilterQuery($filter, &$joins, array &$where, Register $parameter_output = null) { $filter = $this->processFilter($filter); $db = Symphony::Database(); $statements = array(); if (!is_array($values)) { $values = array(); } // Exact matches: switch ($filter->type) { case 'is': $operator = '='; break; case 'is-not': $operator = '!='; break; case 'earlier-than': $operator = '>'; break; case 'earlier-than-or-equal': $operator = '>='; break; case 'later-than': $operator = '<'; break; case 'later-than-or-equal': $operator = '<='; break; } if (empty($this->last_handle)) { $this->join_handle = $this->buildFilterJoin($joins); } $handle = $this->join_handle; $value = DataSource::replaceParametersInString(trim($filter->value), $parameter_output); $value = DateTimeObj::toGMT($value); $statements[] = $db->prepareQuery("%d {$operator} UNIX_TIMESTAMP({$handle}.value)", array($value)); if (empty($statements)) { return true; } if ($filter_join == DataSource::FILTER_OR) { $statement = "(\n\t" . implode("\n\tOR ", $statements) . "\n)"; } else { $statement = "(\n\t" . implode("\n\tAND ", $statements) . "\n)"; } $where[] = $statement; return true; }
public function getParameterOutputValue(StdClass $data, Entry $entry = NULL) { if (is_null($d->value)) { return; } $timestamp = DateTimeObj::toGMT($data->value); return DateTimeObj::get('Y-m-d H:i:s', $timestamp); }
public function render(Register $parameter_output, $joins = NULL, array $where = array(), $filter_operation_type = self::FILTER_AND) { $execute = true; $result = new XMLDocument(); $result->appendChild($result->createElement($this->parameters()->{'root-element'})); $root = $result->documentElement; // Conditions // If any one condtion returns true (that is, do not execute), the DS will not execute at all if (is_array($this->parameters()->conditions) && !empty($this->parameters()->conditions)) { foreach ($this->parameters()->conditions as $condition) { if (isset($dump)) { $dump->append($condition, strpos(':', $condition['parameter'])); } if (strpos(':', $condition['parameter']) !== false) { $c = Datasource::replaceParametersInString($condition['parameter'], $parameter_output); } else { $c = Datasource::resolveParameter($condition['parameter'], $parameter_output); } // Is Empty if ($condition['logic'] == 'empty' && (is_null($c) || strlen($c) == 0)) { $execute = false; } elseif ($condition['logic'] == 'set' && !is_null($c)) { $execute = false; } if ($execute !== true) { return null; } } } // Grab the section try { $section = Section::loadFromHandle($this->parameters()->section); } catch (SectionException $e) { throw $e; } catch (Exception $e) { throw $e; } $pagination = (object) array('total-entries' => NULL, 'entries-per-page' => max(1, (int) self::replaceParametersInString($this->parameters()->limit, $parameter_output)), 'total-pages' => NULL, 'current-page' => max(1, (int) self::replaceParametersInString($this->parameters()->page, $parameter_output))); $pagination->{'record-start'} = max(0, ($pagination->{'current-page'} - 1) * $pagination->{'entries-per-page'}); $order = $sort = NULL; // Apply the Sorting & Direction if ($this->parameters()->{'sort-order'} == 'random') { $order = 'RAND()'; } else { $sort = strtolower($this->parameters()->{'sort-order'}) == 'asc' ? 'ASC' : 'DESC'; // Set Default sort $order = "e.id {$sort}"; // System Field if (preg_match('/^system:/i', $this->parameters()->{'sort-field'})) { switch (preg_replace('/^system:/i', NULL, $this->parameters()->{'sort-field'})) { case 'id': $order = "e.id {$sort}"; break; case 'creation-date': $order = "e.creation_date {$sort}"; break; case 'modification-date': $order = "e.modification_date {$sort}"; break; } } else { $join = NULL; $sort_field = $section->fetchFieldByHandle($this->parameters()->{'sort-field'}); if ($sort_field instanceof Field && $sort_field->isSortable() && method_exists($sort_field, "buildSortingQuery")) { $sort_field->buildSortingQuery($join, $order); $joins .= sprintf($join, $sort_field->section, $sort_field->{'element-name'}); $order = sprintf($order, $sort); } } } // Process Datasource Filters for each of the Fields if (is_array($this->parameters()->filters) && !empty($this->parameters()->filters)) { foreach ($this->parameters()->filters as $k => $filter) { if ($filter['element-name'] == 'system:id') { $filter_value = $this->prepareFilterValue($filter['value'], $parameter_output); if (!is_array($filter_value)) { continue; } $filter_value = array_map('intval', $filter_value); if (empty($filter_value)) { continue; } $where[] = sprintf("(e.id %s IN (%s))", $filter['type'] == 'is-not' ? 'NOT' : NULL, implode(',', $filter_value)); } else { $field = $section->fetchFieldByHandle($filter['element-name']); if ($field instanceof Field) { $field->buildFilterQuery($filter, $joins, $where, $parameter_output); } } } } // Escape percent symbol: $where = array_map(create_function('$string', 'return str_replace(\'%\', \'%%\', $string);'), $where); /* NOT TESTED, UNOPTIMISED // Get unique tables and their alias and join $tables = array(); $split = preg_split('/\s(LEFT OUTER JOIN)\s/', $joins, null, PREG_SPLIT_NO_EMPTY); foreach($split as $table_join) { preg_match_all('/`[a-z_]+\d?`\s/', $table_join, $matches); if(!array_key_exists($matches[0][0], $tables)) { $tables[$matches[0][0]] = array( 'alias' => $matches[0][1], 'join' => $table_join ); } } // Loop over the WHERE statements and subsitute the other alias with the first alias // we discovered for that table. $o_where = array(); $o_joins = null; foreach($tables as $tbl => $info) { $search = preg_replace('/\d+`\s/', '', $info['alias']); $o_joins .= 'LEFT OUTER JOIN ' . $info['join'] . PHP_EOL; if(is_array($where) && !empty($where)) foreach($where as $statement) { if(!$statement || !$search) continue; if(strpos($statement, $search) !== false) { $o_where[] = preg_replace('/' . $search . '\d+`/', trim($info['alias']), $statement); } } } */ $o_where = $where; $o_joins = $joins; $query = sprintf('SELECT DISTINCT SQL_CALC_FOUND_ROWS e.* FROM `tbl_entries` AS `e` %1$s WHERE `section` = "%2$s" %3$s ORDER BY %4$s LIMIT %5$d, %6$d', $o_joins, $section->handle, is_array($o_where) && !empty($o_where) ? 'AND (' . implode($filter_operation_type == self::FILTER_AND ? ' AND ' : ' OR ', $o_where) . ')' : NULL, $order, $pagination->{'record-start'}, $pagination->{'entries-per-page'}); //echo '<pre>', htmlentities($query); exit; try { $entries = Symphony::Database()->query($query, array($section->handle, $section->{'publish-order-handle'}), 'DatasourceResult'); if (isset($this->parameters()->{'append-pagination'}) && $this->parameters()->{'append-pagination'} === true) { $pagination->{'total-entries'} = (int) Symphony::Database()->query("SELECT FOUND_ROWS() AS `total`")->current()->total; $pagination->{'total-pages'} = (int) ceil($pagination->{'total-entries'} * (1 / $pagination->{'entries-per-page'})); // Pagination Element $root->appendChild(General::buildPaginationElement($result, $pagination->{'total-entries'}, $pagination->{'total-pages'}, $pagination->{'entries-per-page'}, $pagination->{'current-page'})); } if (isset($this->parameters()->{'append-sorting'}) && $this->parameters()->{'append-sorting'} === true) { $sorting = $result->createElement('sorting'); $sorting->setAttribute('field', $this->parameters()->{'sort-field'}); $sorting->setAttribute('order', $this->parameters()->{'sort-order'}); $root->appendChild($sorting); } // Output section details $root->setAttribute('section', $section->handle); $schema = array(); // Build Entry Records if ($entries->valid()) { // Do some pre-processing on the include-elements. if (is_array($this->parameters()->{'included-elements'}) && !empty($this->parameters()->{'included-elements'})) { $included_elements = (object) array('system' => array(), 'fields' => array()); foreach ($this->parameters()->{'included-elements'} as $element) { $element_name = $mode = NULL; if (preg_match_all('/^([^:]+):\\s*(.+)$/', $element, $matches, PREG_SET_ORDER)) { $element_name = $matches[0][1]; $mode = $matches[0][2]; } else { $element_name = $element; } if ($element_name == 'system') { $included_elements->system[] = $mode; } else { $field = $section->fetchFieldByHandle($element_name); if (!$field instanceof Field) { continue; } $schema[$element_name] = $field; $included_elements->fields[] = array('element-name' => $element_name, 'instance' => $field, 'mode' => !is_null($mode) > 0 ? trim($mode) : NULL); } } } // Do some pre-processing on the param output array if (is_array($this->parameters()->{'parameter-output'}) && !empty($this->parameters()->{'parameter-output'})) { $output_parameters = (object) array('system' => array(), 'fields' => array()); foreach ($this->parameters()->{'parameter-output'} as $element) { if (preg_match('/^system:/i', $element)) { $output_parameters->system[preg_replace('/^system:/i', NULL, $element)] = array(); } else { $field = $section->fetchFieldByHandle($element); if (!$field instanceof Field) { continue; } $schema[$element] = $field; $output_parameters->fields[$element] = array(); } } } $ids = array(); $data = array(); foreach ($entries as $e) { $ids[] = $e->id; } $schema = array_unique($schema); foreach ($schema as $field => $instance) { $data[$field] = $instance->loadDataFromDatabaseEntries($section->handle, $ids); } $entries->setSchema($schema); $entries->setData($data); foreach ($entries as $e) { // If there are included elements, need an entry element. if (is_array($this->parameters()->{'included-elements'}) && !empty($this->parameters()->{'included-elements'})) { $entry = $result->createElement('entry'); $entry->setAttribute('id', $e->id); $root->appendChild($entry); foreach ($included_elements->system as $field) { switch ($field) { case 'creation-date': $entry->appendChild(General::createXMLDateObject($result, DateTimeObj::toGMT($e->creation_date), 'creation-date')); break; case 'modification-date': $entry->appendChild(General::createXMLDateObject($result, DateTimeObj::toGMT($e->modification_date), 'modification-date')); break; case 'user': $obj = User::load($e->user_id); $user = $result->createElement('user', $obj->getFullName()); $user->setAttribute('id', $e->user_id); $user->setAttribute('username', $obj->username); $user->setAttribute('email-address', $obj->email); $entry->appendChild($user); break; } } foreach ($included_elements->fields as $field) { $field['instance']->appendFormattedElement($entry, $e->data()->{$field['element-name']}, false, $field['mode'], $e); } } if (is_array($this->parameters()->{'parameter-output'}) && !empty($this->parameters()->{'parameter-output'})) { foreach ($output_parameters->system as $field => $existing_values) { switch ($field) { case 'id': $output_parameters->system[$field][] = $e->id; break; case 'creation-date': $output_parameters->system[$field][] = DateTimeObj::get('Y-m-d H:i:s', DateTimeObj::toGMT($e->creation_date)); break; case 'modification-date': $output_parameters->system[$field][] = DateTimeObj::get('Y-m-d H:i:s', DateTimeObj::toGMT($e->modification_date)); break; case 'user': $output_parameters->system[$field][] = $e->user_id; break; } } foreach ($output_parameters->fields as $field => $existing_values) { if (!isset($e->data()->{$field}) or is_null($e->data()->{$field})) { continue; } if (is_null($e->data()->{$field})) { continue; } $o = $section->fetchFieldByHandle($field)->getParameterOutputValue((object) $e->data()->{$field}, $e); if (is_array($o)) { $output_parameters->fields[$field] = array_merge($o, $output_parameters->fields[$field]); } else { $output_parameters->fields[$field][] = $o; } } } } // Add in the param output values to the parameter_output object if (is_array($this->parameters()->{'parameter-output'}) && !empty($this->parameters()->{'parameter-output'})) { foreach ($output_parameters->system as $field => $values) { $key = sprintf('ds-%s.system.%s', $this->parameters()->{'root-element'}, $field); $values = array_filter($values); if (is_array($values) && !empty($values)) { $parameter_output->{$key} = array_unique($values); } } foreach ($output_parameters->fields as $field => $values) { $key = sprintf('ds-%s.%s', $this->parameters()->{'root-element'}, $field); $values = array_filter($values); if (is_array($values) && !empty($values)) { $parameter_output->{$key} = array_unique($values); } } } } elseif ($this->parameters()->{'redirect-404-on-empty'} === true) { throw new FrontendPageNotFoundException(); } else { $this->emptyXMLSet($root); } } catch (DatabaseException $e) { $root->appendChild($result->createElement('error', $e->getMessage())); } return $result; }