/**
* Retun an array of maps containing the keys, 'ID' and 'ParentID' for each page to be displayed
* in the search.
*
* @return Array
*/
public function pagesIncluded()
{
$sng = singleton('SiteTree');
$ids = array();
$query = new DataQuery('SiteTree');
$query->setQueriedColumns(array('ID', 'ParentID'));
foreach ($this->params as $name => $val) {
$SQL_val = Convert::raw2sql($val);
switch ($name) {
case 'Term':
$query->whereAny(array("\"URLSegment\" LIKE '%{$SQL_val}%'", "\"Title\" LIKE '%{$SQL_val}%'", "\"MenuTitle\" LIKE '%{$SQL_val}%'", "\"Content\" LIKE '%{$SQL_val}%'"));
break;
case 'LastEditedFrom':
$fromDate = new DateField(null, null, $SQL_val);
$query->where("\"LastEdited\" >= '{$fromDate->dataValue()}'");
break;
case 'LastEditedTo':
$toDate = new DateField(null, null, $SQL_val);
$query->where("\"LastEdited\" <= '{$toDate->dataValue()}'");
break;
case 'ClassName':
if ($val && $val != 'All') {
$query->where("\"ClassName\" = '{$SQL_val}'");
}
break;
default:
if (!empty($val) && $sng->hasDatabaseField($name)) {
$filter = $sng->dbObject($name)->defaultSearchFilter();
$filter->setValue($val);
$filter->apply($query);
}
}
}
foreach ($query->execute() as $row) {
$ids[] = array('ID' => $row['ID'], 'ParentID' => $row['ParentID']);
}
return $ids;
}
public function getDataObjects()
{
Versioned::reading_stage('Stage');
$rows = array();
$tables = $this->getQueryTables();
$allFields = $this->getReportableFields();
$selectedFields = $this->ReportFields->getValues();
$fields = array();
$sum = $this->SumFields->getValues();
$sum = $sum ? $sum : array();
if ($selectedFields) {
foreach ($selectedFields as $field) {
if (!isset($allFields[$field])) {
continue;
}
$as = $this->dottedFieldToUnique($allFields[$field]);
$fields[$as] = $field;
}
}
$baseTable = null;
// stores the relationship name-to-aliasname mapping
$relatedTables = array();
// stores the tbl_{one}_{two} alias name-to-class-type mapping
$aliasToDataType = array();
foreach ($tables as $typeName => $alias) {
if (strpos($typeName, '.')) {
$relatedTables[$typeName] = $alias;
} else {
$baseTable = $typeName;
}
}
if (!$baseTable) {
throw new Exception("All freeform reports must have a base data type selected");
}
$multiValue = array();
// go through and capture all the multivalue fields
// at the same time, remap Type.Field structures to
// TableName.Field
$remappedFields = array();
$simpleFields = array();
foreach ($fields as $alias => $name) {
$class = '';
if (strpos($name, '.')) {
list($class, $field) = explode('.', $name);
} else {
$class = $baseTable;
$field = $name;
}
$typeFields = array();
if (class_exists($class)) {
$instance = singleton($class);
$typeFields = method_exists($instance, 'getAdvancedReportableFields') ? $instance->getAdvancedReportableFields() : array();
} else {
if ($dataType = $this->fieldAliasToDataType($class)) {
$instance = singleton($dataType);
$typeFields = method_exists($instance, 'getAdvancedReportableFields') ? $instance->getAdvancedReportableFields($class . '.') : array();
}
}
$fieldAlias = '';
// if the name is prefixed, we need to figure out what the actual $class is from the
// remote join
if (strpos($name, 'tbl_') === 0) {
$fieldAlias = $this->dottedFieldToUnique($name);
$selectField = '"' . Convert::raw2sql($class) . '"."' . Convert::raw2sql($field) . '"';
if (isset($typeFields[$field])) {
$selectField = $typeFields[$field];
}
$remappedFields[$fieldAlias] = $selectField;
if (in_array($name, $sum)) {
$remappedFields[$fieldAlias] = 'SUM(' . $remappedFields[$fieldAlias] . ')';
}
} else {
if (isset($typeFields[$name])) {
$remappedFields[$name] = $typeFields[$name];
} else {
if (in_array($name, $sum)) {
$remappedFields[$field] = 'SUM(' . $field . ')';
} else {
// just store it as is
$simpleFields[$alias] = $field;
}
}
}
$field = preg_replace('/Value$/', '', $field);
$db = Config::inst()->get($class, 'db');
if (isset($db[$field]) && $db[$field] == 'MultiValueField') {
$multiValue[] = $alias;
}
}
$dataQuery = new DataQuery($baseTable);
$dataQuery->setQueriedColumns($simpleFields);
// converts all the fields being queried into the appropriate
// tables for querying.
$query = $dataQuery->getFinalisedQuery();
// explicit fields that we want to query against, that come from joins.
// we need to do it this way to ensure that a) the field names in the results match up to
// the header labels specified and b) because dataQuery by default doesn't return fields from
// joined tables, it merely allows for fields from the base dataClass
foreach ($remappedFields as $alias => $name) {
$query->selectField($name, $alias);
}
// and here's where those joins are added. This is somewhat copied from dataQuery,
// but modified so that our table aliases are used properly to avoid the bug described at
// https://github.com/silverstripe/silverstripe-framework/issues/3518
// it also ensures the alias names are in a format that our header assignment and field retrieval works as
// expected
foreach (array_keys($relatedTables) as $relation) {
$this->applyRelation($baseTable, $query, $relation);
}
$sort = $this->getSort();
$query->setOrderBy($sort);
$filter = $this->getConditions();
$where = $this->getWhereClause($filter, $baseTable);
$query->setWhere($where);
$groupBy = $this->GroupBy->getValues();
if (count($groupBy)) {
$query->setGroupBy($groupBy);
}
$sql = $query->sql();
$out = $query->execute();
$rows = array();
$headers = $this->getHeaders();
foreach ($out as $row) {
foreach ($multiValue as $field) {
$row[$field] = implode("\n", (array) unserialize($row[$field]));
}
$rows[] = $row;
}
return ArrayList::create($rows);
}
