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;
}
