public function getMap()
{
$this->getDimensions();
if (empty($this->map)) {
$sel = new SQLSelect($this->table);
$res = $sel->execute();
$this->map = parallelToAssoc($res[strtoupper($this->idfields[0])], $res[strtoupper($this->idfields[1])]);
}
return $this->map;
}
public function getPossLanguages()
{
if (empty($this->posslanguages)) {
$getl = new SQLSelect("translation_language");
$getl->fetchas = "row";
$res = $getl->execute();
$this->posslanguages = $res;
}
return $this->posslanguages;
}
public function processFields()
{
if (!isset($this->fields["language_keycode"]["options"])) {
$opsel = new SQLSelect("translation_language");
$opsel->selectfields = array("id" => "keycode", "label" => "name_def");
$opsel->fetchas = "row";
$res = $opsel->execute();
$options = $res;
$this->fields["language_keycode"]["options"] = $options;
}
}
/**
* Attempt to find an old/renamed page from some given the URL as an array
*
* @param array $params The array of URL, e.g. /foo/bar as array('foo', 'bar')
* @param SiteTree $parent The current parent in the recursive flow
* @param boolean $redirect Whether we've found an old page worthy of a redirect
*
* @return string|boolean False, or the new URL
*/
public static function find_old_page($params, $parent = null, $redirect = false)
{
$parent = is_numeric($parent) ? SiteTree::get()->byId($parent) : $parent;
$params = (array) $params;
$URL = rawurlencode(array_shift($params));
if (empty($URL)) {
return false;
}
if ($parent) {
$page = SiteTree::get()->filter(array('ParentID' => $parent->ID, 'URLSegment' => $URL))->First();
} else {
$page = SiteTree::get()->filter(array('URLSegment' => $URL))->First();
}
if (!$page) {
// If we haven't found a candidate, lets resort to finding an old page with this URL segment
$oldFilter = array('"SiteTree_versions"."URLSegment"' => $URL, '"SiteTree_versions"."WasPublished"' => true);
if ($parent) {
$oldFilter[] = array('"SiteTree_versions"."ParentID"' => $parent->ID);
}
$query = new SQLSelect('"RecordID"', '"SiteTree_versions"', $oldFilter, '"LastEdited" DESC', null, null, 1);
$record = $query->execute()->first();
if ($record) {
$page = SiteTree::get()->byID($record['RecordID']);
$redirect = true;
}
}
if ($page && $page->canView()) {
if (count($params)) {
// We have to go deeper!
$ret = self::find_old_page($params, $page, $redirect);
if ($ret) {
// A valid child page was found! We can return it
return $ret;
} else {
// No valid page found.
if ($redirect) {
// If we had some redirect to be done, lets do it. imagine /foo/action -> /bar/action, we still want this redirect to happen if action isn't a page
return $page->Link() . implode('/', $params);
}
}
} else {
// We've found the final, end all, page.
return $page->Link();
}
}
return false;
}
public function build_query()
{
$setstring = "";
// If insertIfAbsent is set, check for the row
if ($this->insertIfAbsent) {
$sel = new SQLSelect($this->table, $this->schema);
$sel->selectfields[] = "1";
$sel->wherearray = $this->selectors;
$val = $sel->execute();
if ($val == NULL || empty($val) || $val[1] == array()) {
$ins = new SQLInsert($this->table, $this->values);
$q = $ins->build_query();
$this->query = $q;
return $this->query;
}
}
// Perform the UPDATE
foreach ($this->values as $key => $value) {
// Skip selectors
if (isset($this->selectors[$key])) {
continue;
}
// Build 'SET' string
if ($setstring != "") {
$setstring .= ",";
}
$setstring .= $key . "=" . dbize($value, $this->get_column_datatype($key));
}
if ($setstring != "") {
$setstring = " SET " . $setstring;
}
// Add selectors to WHERE clause
foreach ($this->selectors as $key => $value) {
$this->wherefields[] = "{$key} = " . dbize($value, $this->get_column_datatype($key));
}
$wherestring = $this->wherefields_to_string();
if ($setstring != "" && $wherestring != "") {
$query = "UPDATE " . $this->table_with_schema() . $setstring . $wherestring;
$this->query = $query;
}
return $this->query;
}
/**
* Return all data object visible attributes of the specified type, with optional filters.
*
* @parameter <{DATA_OBJECT_NAME}> string
* @parameter <{LIMIT}> integer
* @parameter <{SORT}> array(string, string)
* @parameter <{FILTERS}> array
* @return array
*/
public function retrieveValidated($class, $limit = null, $sort = null, $filters = null)
{
// Validate the data object class.
$class = strtolower($class);
if (in_array($class, array_map('strtolower', ClassInfo::subclassesFor('DataObject'))) && ($configuration = DataObjectOutputConfiguration::get_one('DataObjectOutputConfiguration', array('LOWER(IsFor) = ?' => $class))) && ($temporaryClass = DataObject::get_one($class))) {
$class = ClassInfo::baseDataClass($temporaryClass->ClassName);
$visibility = $configuration->APIwesomeVisibility ? explode(',', $configuration->APIwesomeVisibility) : null;
// Validate the sort and filters.
$where = array();
$sortValid = is_array($sort) && count($sort) === 2 && ($order = strtoupper($sort[1])) && ($order === 'ASC' || $order === 'DESC');
$filterValid = is_array($filters) && count($filters);
$sorting = array();
$filtering = array();
// Grab the appropriate attributes for this data object.
$columns = array();
$from = array();
foreach (ClassInfo::subclassesFor($class) as $subclass) {
// Determine the tables to join.
$subclassFields = DataObject::database_fields($subclass);
if (ClassInfo::hasTable($subclass)) {
// Determine the versioned table.
$same = $subclass === $class;
if ($subclass::has_extension('Versioned')) {
$subclass = "{$subclass}_Live";
}
if (!$same) {
$from[] = $subclass;
}
}
// Prepend the table names.
$subclassColumns = array();
foreach ($subclassFields as $column => $type) {
$subclassColumn = "{$subclass}.{$column}";
$subclassColumns[$subclassColumn] = $type;
// Determine the tables to sort and filter on.
if ($sortValid && $sort[0] === $column) {
$sorting[] = "{$subclassColumn} {$order}";
}
if ($filterValid && isset($filters[$column])) {
$filtering[$subclassColumn] = $filters[$column];
}
}
$columns = array_merge($columns, $subclassColumns);
}
array_shift($columns);
// Determine the versioned table.
if ($class::has_extension('Versioned')) {
$class = "{$class}_Live";
}
// Determine ID based sorting and filtering, as these aren't considered database fields.
if ($sortValid && $sort[0] === 'ID') {
$sorting[] = "{$class}.ID {$order}";
}
if ($filterValid && isset($filters['ID'])) {
$where["{$class}.ID = ?"] = $filters['ID'];
}
// Make sure this data object type has visibility customisation.
if ($visibility && count($visibility) === count($columns) && in_array('1', $visibility)) {
// Apply any visibility customisation.
$select = ' ';
$iteration = 0;
foreach ($columns as $attribute => $type) {
if (isset($visibility[$iteration]) && $visibility[$iteration]) {
$select .= $attribute . ', ';
if (isset($filtering[$attribute])) {
// Apply the filter if the matching attribute is visible.
$column = is_numeric($filtering[$attribute]) ? $attribute : "LOWER({$attribute})";
$where["{$column} = ?"] = strtolower($filtering[$attribute]);
}
}
$iteration++;
}
if (isset($filtering["{$class}.ClassName"])) {
$where["LOWER({$class}.ClassName) = ?"] = strtolower($filtering["{$class}.ClassName"]);
}
// Grab all data object visible attributes.
$query = new SQLSelect("{$class}.ClassName,{$select}{$class}.ID", $class, $where, $sorting, array(), array(), is_numeric($limit) ? $limit : array());
// Determine the tables with visible attributes to join.
foreach ($from as $join) {
if (strpos($select, " {$join}.") !== false) {
$query->addLeftJoin($join, "{$class}.ID = {$join}.ID");
}
}
$objects = array();
foreach ($query->execute() as $temporary) {
// Return an array of data object maps.
$object = array();
foreach ($temporary as $attribute => $value) {
if ($value) {
$object[$attribute] = $value;
}
}
$objects[] = $object;
}
return $objects;
}
}
// The specified data object type had no visibility customisation.
return null;
}
public function testDbDatetimeDifference()
{
$offset = $this->checkPreconditions();
$clause = $this->adapter->datetimeDifferenceClause('1974-10-14 10:30:00', '1973-10-14 10:30:00');
$result = DB::query('SELECT ' . $clause)->value();
$this->matchesRoughly($result / 86400, 365, '1974 - 1973 = 365 * 86400 sec', $offset);
$clause = $this->adapter->datetimeDifferenceClause(date('Y-m-d H:i:s', strtotime('-15 seconds')), 'now');
$result = DB::query('SELECT ' . $clause)->value();
$this->matchesRoughly($result, -15, '15 seconds ago - now', $offset);
$clause = $this->adapter->datetimeDifferenceClause('now', $this->adapter->datetimeIntervalClause('now', '+45 Minutes'));
$result = DB::query('SELECT ' . $clause)->value();
$this->matchesRoughly($result, -45 * 60, 'now - 45 minutes ahead', $offset);
$query = new SQLSelect();
$query->setSelect(array());
$query->selectField($this->adapter->datetimeDifferenceClause('"LastEdited"', '"Created"'), 'test')->setFrom('"DbDateTimeTest_Team"')->setLimit(1);
$result = $query->execute()->value();
$lastedited = Dataobject::get_one('DbDateTimeTest_Team')->LastEdited;
$created = Dataobject::get_one('DbDateTimeTest_Team')->Created;
$this->matchesRoughly($result, strtotime($lastedited) - strtotime($created), 'age of HomePage record in seconds since unix epoc', $offset);
}
/**
* Link this group set to a specific member.
*
* Recursively selects all groups applied to this member, as well as any
* parent groups of any applied groups
*
* @param array|integer $id (optional) An ID or an array of IDs - if not provided, will use the current
* ids as per getForeignID
* @return array Condition In array(SQL => parameters format)
*/
public function foreignIDFilter($id = null)
{
if ($id === null) {
$id = $this->getForeignID();
}
// Find directly applied groups
$manyManyFilter = parent::foreignIDFilter($id);
$query = new SQLSelect('"Group_Members"."GroupID"', '"Group_Members"', $manyManyFilter);
$groupIDs = $query->execute()->column();
// Get all ancestors, iteratively merging these into the master set
$allGroupIDs = array();
while ($groupIDs) {
$allGroupIDs = array_merge($allGroupIDs, $groupIDs);
$groupIDs = DataObject::get("Group")->byIDs($groupIDs)->column("ParentID");
$groupIDs = array_filter($groupIDs);
}
// Add a filter to this DataList
if (!empty($allGroupIDs)) {
$allGroupIDsPlaceholders = DB::placeholders($allGroupIDs);
return array("\"Group\".\"ID\" IN ({$allGroupIDsPlaceholders})" => $allGroupIDs);
} else {
return array('"Group"."ID"' => 0);
}
}
/**
* Find the extra field data for a single row of the relationship join
* table, given the known child ID.
*
* @param string $componentName The name of the component
* @param int $itemID The ID of the child for the relationship
*
* @return array Map of fieldName => fieldValue
*/
public function getExtraData($componentName, $itemID)
{
$result = array();
// Skip if no extrafields or unsaved record
if (empty($this->extraFields) || empty($itemID)) {
return $result;
}
if (!is_numeric($itemID)) {
user_error('ComponentSet::getExtraData() passed a non-numeric child ID', E_USER_ERROR);
}
$cleanExtraFields = array();
foreach ($this->extraFields as $fieldName => $dbFieldSpec) {
$cleanExtraFields[] = "\"{$fieldName}\"";
}
$query = new SQLSelect($cleanExtraFields, "\"{$this->joinTable}\"");
$filter = $this->foreignIDWriteFilter($this->getForeignID());
if ($filter) {
$query->setWhere($filter);
} else {
user_error("Can't call ManyManyList::getExtraData() until a foreign ID is set", E_USER_WARNING);
}
$query->addWhere(array("\"{$this->localKey}\"" => $itemID));
$queryResult = $query->execute()->current();
if ($queryResult) {
foreach ($queryResult as $fieldName => $value) {
$result[$fieldName] = $value;
}
}
return $result;
}
/**
* Recursively retrieve the entire URL for the given record.
*
* @parameter <{RECORD}> array
* @parameter <{RECURSIVE_URL}> string
* @return string/boolean
*/
protected function getURLForRecord($record = null, $URL = null)
{
if (!$record) {
return false;
}
$parentID = $record['ParentID'];
$seg = $record['URLSegment'];
$URL = !$URL ? $seg : "{$seg}/{$URL}";
if ($parentID == 0) {
// The top of the chain has been reached.
return $URL;
} else {
// Retrieve the parent element which was most recently published.
$parentQuery = new SQLSelect('ID, ParentID, URLSegment, Version', $this->replayTable, "ID = {$parentID}", null, null, null, 1);
$parent = $parentQuery->execute()->first();
return $this->getURLForRecord($parent, $URL);
}
}
/**
* Get a list of languages with at least one element translated in (including the default language)
*
* @param string $className Look for languages in elements of this class
* @param string $where Optional SQL WHERE statement
* @return array Map of languages in the form locale => langName
*/
public static function get_existing_content_languages($className = 'SiteTree', $where = '')
{
$baseTable = ClassInfo::baseDataClass($className);
$query = new SQLSelect("Distinct \"Locale\"", "\"{$baseTable}\"", $where, '', "\"Locale\"");
$dbLangs = $query->execute()->column();
$langlist = array_merge((array) Translatable::default_locale(), (array) $dbLangs);
$returnMap = array();
$allCodes = array_merge(Config::inst()->get('i18n', 'all_locales'), Config::inst()->get('i18n', 'common_locales'));
foreach ($langlist as $langCode) {
if ($langCode && isset($allCodes[$langCode])) {
if (is_array($allCodes[$langCode])) {
$returnMap[$langCode] = $allCodes[$langCode]['name'];
} else {
$returnMap[$langCode] = $allCodes[$langCode];
}
}
}
return $returnMap;
}
/**
* Are new posts available?
*
* @param int $id
* @param array $data Optional: If an array is passed, the timestamp of
* the last created post and it's ID will be stored in
* it (keys: 'last_id', 'last_created')
* @param int $lastVisit Unix timestamp of the last visit (GMT)
* @param int $lastPostID ID of the last read post
* @param int $thread ID of the relevant topic (set to NULL for all
* topics)
* @return bool Returns TRUE if there are new posts available, otherwise
* FALSE.
*/
public static function new_posts_available($id, &$data = array(), $lastVisit = null, $lastPostID = null, $forumID = null, $threadID = null)
{
// last post viewed
$query = new SQLSelect(array('LastID' => 'MAX("Post"."ID")', 'LastCreated' => 'MAX("Post"."Created")'), '"Post"', array('"ForumPage"."ParentID" = ?' => $id));
$query->addInnerJoin(ForumHolder::baseForumTable(), '"Post"."ForumID" = "ForumPage"."ID"', 'ForumPage');
// Filter by parameters specified
if ($lastPostID) {
$query->addWhere(array('"Post"."ID" > ?' => $lastPostID));
}
if ($lastVisit) {
$query->addWhere(array('"Post"."Created" > ?' => $lastVisit));
}
if ($forumID) {
$query->addWhere(array('"Post"."ForumID" = ?' => $forumID));
}
if ($threadID) {
$query->addWhere(array('"Post"."ThreadID" = ?' => $threadID));
}
// Run
$version = $query->execute()->first();
if (!$version) {
return false;
}
if ($data) {
$data['last_id'] = (int) $version['LastID'];
$data['last_created'] = strtotime($version['LastCreated']);
}
$lastVisit = (int) $lastVisit;
if ($lastVisit <= 0) {
$lastVisit = false;
}
$lastPostID = (int) $lastPostID;
if ($lastPostID <= 0) {
$lastPostID = false;
}
if (!$lastVisit && !$lastPostID) {
return true;
}
if ($lastVisit && strtotime($version['LastCreated']) > $lastVisit) {
return true;
}
if ($lastPostID && (int) $version['LastID'] > $lastPostID) {
return true;
}
return false;
}
/**
* Returns true if the member is allowed to do the given action.
* See {@link extendedCan()} for a more versatile tri-state permission control.
*
* @param string $perm The permission to be checked, such as 'View'.
* @param Member $member The member whose permissions need checking. Defaults to the currently logged
* in user.
*
* @return boolean True if the the member is allowed to do the given action
*/
public function can($perm, $member = null)
{
if (!isset($member)) {
$member = Member::currentUser();
}
if (Permission::checkMember($member, "ADMIN")) {
return true;
}
if ($this->many_many('Can' . $perm)) {
if ($this->ParentID && $this->SecurityType == 'Inherit') {
if (!($p = $this->Parent)) {
return false;
}
return $this->Parent->can($perm, $member);
} else {
$permissionCache = $this->uninherited('permissionCache');
$memberID = $member ? $member->ID : 'none';
if (!isset($permissionCache[$memberID][$perm])) {
if ($member->ID) {
$groups = $member->Groups();
}
$groupList = implode(', ', $groups->column("ID"));
// TODO Fix relation table hardcoding
$query = new SQLSelect("\"Page_Can{$perm}\".PageID", array("\"Page_Can{$perm}\""), "GroupID IN ({$groupList})");
$permissionCache[$memberID][$perm] = $query->execute()->column();
if ($perm == "View") {
// TODO Fix relation table hardcoding
$query = new SQLSelect("\"SiteTree\".\"ID\"", array("\"SiteTree\"", "LEFT JOIN \"Page_CanView\" ON \"Page_CanView\".\"PageID\" = \"SiteTree\".\"ID\""), "\"Page_CanView\".\"PageID\" IS NULL");
$unsecuredPages = $query->execute()->column();
if ($permissionCache[$memberID][$perm]) {
$permissionCache[$memberID][$perm] = array_merge($permissionCache[$memberID][$perm], $unsecuredPages);
} else {
$permissionCache[$memberID][$perm] = $unsecuredPages;
}
}
Config::inst()->update($this->class, 'permissionCache', $permissionCache);
}
if ($permissionCache[$memberID][$perm]) {
return in_array($this->ID, $permissionCache[$memberID][$perm]);
}
}
} else {
return parent::can($perm, $member);
}
}
/**
* Test that multiple order elements are maintained in the given order
*/
public function testOrderByMultiple()
{
if (DB::get_conn() instanceof MySQLDatabase) {
$query = new SQLSelect();
$query->setSelect(array('"Name"', '"Meta"'));
$query->setFrom('"SQLQueryTest_DO"');
$query->setOrderBy(array('MID("Name", 8, 1) DESC', '"Name" ASC'));
$records = array();
foreach ($query->execute() as $record) {
$records[] = $record;
}
$this->assertCount(2, $records);
$this->assertEquals('Object 2', $records[0]['Name']);
$this->assertEquals('2', $records[0]['_SortColumn0']);
$this->assertEquals('Object 1', $records[1]['Name']);
$this->assertEquals('1', $records[1]['_SortColumn0']);
}
}
/**
* Copies all values from one table to another. Will override any existing values with matching ID's.
*
* @param string $fromTable Name of SOURCE table to copy values from.
* @param string $toTable Name of DESTINATION table to copy values to.
* @param array|null $fieldMapping Array of fields to copy (and ONLY these fields). Can also specify key => value
* pairs to map between old/new names (instead of just values). Note: Leave
* empty (or pass null) to automatically assume ALL fields from source table (including ID).
* @param bool $purgeDest Ensures all data in the DESTINATION table matches the source.
* @param mixed|null $where An optional filter passed directly to ->setWhere() method on SQLSelect.
* @throws MigrationException
*/
public static function copyTable($fromTable, $toTable, array $fieldMapping = null, $purgeDest = false, $where = null)
{
if (!static::tableExists($fromTable)) {
throw new MigrationException("Table '{$fromTable}' does not exist.");
}
if (!static::tableExists($toTable)) {
throw new MigrationException("Table '{$fromTable}' does not exist.");
}
// Initialize defaults.
if ($fieldMapping === null) {
$fieldMapping = array();
}
// Normalize to empty.
if ($fieldMapping === array()) {
// If empty: Use all fields from the source.
$fieldMapping = array_keys(static::getTableColumns($fromTable));
}
// Since an ID is required to prevent duplication of data, add it now if it's not already setup.
// TODO: Should this be optional?
if (!in_array('ID', $fieldMapping)) {
$fieldMapping[] = 'ID';
}
// Separate out the source/destination fields from the field mapping to help with selection and validation (correspondingly).
$sourceFields = array_map(function ($key, $value) {
if (!is_numeric($key)) {
return $key;
}
return $value;
}, array_keys($fieldMapping), array_values($fieldMapping));
$destFields = array_values($fieldMapping);
// Validate columns in the destination first and ensure they exist first before moving forward, since you
// don't want to perform a DELETE on an entire table unless you're sure the entire operation will complete.
$destActualFields = array_keys(self::getTableColumns($toTable));
$destFieldDiff = array_diff($destFields, $destActualFields);
if (count($destFieldDiff) !== 0) {
throw new MigrationException("The field(s) '" . join(', ', $destFieldDiff) . "' do not exist in the destination table '{$toTable}'.");
}
// Purge now, if specified.
if ($purgeDest) {
$delete = new SQLDelete($toTable);
$delete->execute();
}
// Begin fetching rows and copying them over now.
$select = new SQLSelect($sourceFields, $fromTable);
if ($where !== null) {
$select->setWhere($where);
}
$result = $select->execute();
while ($sourceRow = $result->next()) {
// Convert row fields based on our mapping.
$destRow = array();
foreach ($sourceRow as $field => $value) {
if (array_key_exists($field, $fieldMapping)) {
$field = $fieldMapping[$field];
}
$destRow[$field] = $value;
}
// Update table.
static::setRowValuesOnTable($toTable, $destRow, null, true);
}
}
/**
* Find the extra field data for a single row of the relationship join
* table, given the known child ID.
*
* @param string $componentName The name of the component
* @param int $itemID The ID of the child for the relationship
*
* @return array Map of fieldName => fieldValue
*/
public function getExtraData($componentName, $itemID)
{
$result = array();
if (!is_numeric($itemID)) {
user_error('ComponentSet::getExtraData() passed a non-numeric child ID', E_USER_ERROR);
}
// @todo Optimize into a single query instead of one per extra field
if ($this->extraFields) {
foreach ($this->extraFields as $fieldName => $dbFieldSpec) {
$query = new SQLSelect("\"{$fieldName}\"", "\"{$this->joinTable}\"");
if ($filter = $this->foreignIDWriteFilter($this->getForeignID())) {
$query->setWhere($filter);
} else {
user_error("Can't call ManyManyList::getExtraData() until a foreign ID is set", E_USER_WARNING);
}
$query->addWhere("\"{$this->localKey}\" = {$itemID}");
$result[$fieldName] = $query->execute()->value();
}
}
return $result;
}
public function findRow($idfields)
{
// Create a key=>value pair of the idfields
$idvals = array();
foreach ($idfields as $field) {
if (isset($this->values[$field])) {
$idvals[$field] = $this->values[$field];
} else {
// Couldn't find value for field $field
return false;
}
}
// Check for a row where the idfields are identical to the values in $values
$sqls = new SQLSelect($this->table);
$sqls->international = $this->international;
$sqls->selectfields = array("1");
foreach ($idvals as $key => $value) {
$sqls->wherefields[] = "{$key} = " . dbize($value, $this->getElementAttribute($key, "type"));
}
$ret = $sqls->execute();
if (empty($ret[1])) {
// Did not find row
//error_out("Could not find matching record");
return false;
} else {
return $idvals;
}
}
public function fetch($category_field = "")
{
global $db;
if ($category_field == "") {
$category_field = $this->idfield;
}
if (!empty($this->rows) && $this->rows != NULL) {
return $this->rows;
}
if (empty($this->rawResults)) {
$sel = new SQLSelect($this->table);
if (!empty($this->fields)) {
$sel->selectfields = array_keys($this->fields);
}
if (!empty($this->extra_selectfields)) {
$sel->selectfields = array_merge($sel->selectfields, $this->extra_selectfields);
}
$sel->fetchas = "row";
$sel->orderfields = $this->orderfields;
$resultList = $sel->execute();
// Iterate through the result set and create a Record for each row
foreach ($resultList as $idx => $row) {
$row = array_change_key_case($row);
$record = $this->makeRecord();
if ($this->idfield != "" && isset($row[$this->idfield])) {
$record->id = $row[$this->idfield];
}
if ($this->titlefield != "" && isset($row[$this->titlefield])) {
$record->title = $row[$this->titlefield];
}
$record->row = $row;
if (isset($row[$category_field])) {
$idnum = $row[$category_field];
} else {
$idnum = $idx;
}
$this->rows[$idnum] = $record;
}
}
$this->fetchChildren();
return $this->rows;
}
public function html()
{
global $db;
$html = "";
// Pull value from another table, if needed
if (isset($this->value_sqltable) && $this->value_sqltable != "") {
if (isset($this->value_sqlselectfield) && $this->value_sqlselectfield != "") {
$selectfield = $this->value_sqlselectfield;
} else {
$selectfield = $this->name;
}
$sel = new SQLSelect($this->value_sqltable);
$sel->selectfields[] = $selectfield;
if (!empty($this->value_sqlwherefields)) {
$sel->wherefields = $this->value_sqlwherefields;
}
$res = $sel->execute();
if (isset($res[strtoupper($selectfield)][0])) {
$this->value = $res[strtoupper($selectfield)][0];
}
}
// Unencode value
if (isset($this->value) && $this->value != "") {
$this->value = htmlspecialchars_decode($this->value);
}
if ($this->type == "header") {
if ($this->is_html_table) {
$html .= "<tr><td";
if ($this->colspan > 0) {
$html .= " colspan='" . $this->colspan . "'";
} else {
if ($this->maximum_fields_per_row) {
$html .= " colspan='" . $this->maximum_fields_per_row . "'";
}
}
$html .= ">";
}
$html .= "<h3>" . $this->label . "</h3>";
if ($this->helptext != "") {
$html .= "<span id='" . $this->id . "_helpblock' class='help-block " . $this->helptextclasses . "'>" . $this->helptext . "</span>";
}
if ($this->is_html_table) {
$html .= "</td></tr>";
}
return $html;
}
// Line separator, if applicable
if ($this->inline == true && $this->newline == true && $this->is_html_table == false) {
$html .= "<p>";
}
// New TR, for table form
if ($this->is_html_table && $this->type != "hidden") {
if ($this->newline) {
$html .= "<tr>";
}
$html .= "<td";
if ($this->colspan > 0) {
$html .= " colspan='" . $this->colspan . "'";
} else {
if ($this->maximum_fields_per_row) {
$html .= " colspan='" . $this->maximum_fields_per_row . "'";
}
}
$html .= ">";
}
// Open container div
if ($this->type != "hidden") {
$html .= "<div class='form-group";
if (isset($this->containerclasses)) {
$html .= " " . $this->containerclasses;
}
$html .= "'>";
}
// Label
$labelhtml = "";
if ($this->type != "hidden" && $this->type != "html" && $this->label != "") {
$labelhtml .= "<label";
if ($this->id != "") {
$labelhtml .= " for='" . $this->id . "'";
}
// Label classes
$labelhtml .= " class='form-control-label control-label";
if ($this->required == true) {
$labelhtml .= "requiredLabel";
}
if ($this->horizontal == true) {
$labelhtml .= " col-sm-2";
}
if (isset($this->labelclasses) && $this->labelclasses != "") {
$labelhtml .= " " . $this->labelclasses;
}
$labelhtml .= "'";
if (isset($this->labelattrs) && $this->labelattrs != "") {
$labelhtml .= " " . $this->labelattrs;
}
$labelhtml .= ">";
$labelhtml .= $this->label;
$labelhtml .= "</label>";
}
// Initialize input tag string
$inputhtml = "";
// Surrounding grid for horizontal
if ($this->horizontal == true && $this->type != "hidden") {
$inputhtml .= "<div class='col-sm-10'>";
}
// Prefix, if any
if (isset($this->prefix) && $this->prefix != "") {
$inputhtml .= "<div class='prefixandinput'><div class='prefix'>" . $this->prefix . "</div>";
}
// Open input tag
if ($this->type != "html") {
if ($this->type == 'textarea') {
$inputhtml .= "<textarea";
} else {
if ($this->type == 'select') {
$inputhtml .= "<select";
} else {
$inputhtml .= "<input type='" . $this->type . "'";
}
}
// Id and name
if ($this->id != "") {
$inputhtml .= " id='" . $this->id . "'";
}
if ($this->name != "") {
$inputhtml .= " name='" . $this->name . "'";
}
// Required
if ($this->type != "hidden" && $this->required == true) {
$inputhtml .= " required='" . $this->required . "'";
}
// Size & step
if ($this->type == 'number') {
$size = 3;
if ($this->size > 0) {
$size = $this->size;
$this->inputattrs .= "style='width:" . ($size * 2 + 1) . "em'";
}
$inputhtml .= " size='{$size}'";
$inputhtml .= " step='" . $this->step . "'";
}
// Minimum & maximum
if ($this->minimum !== "") {
$inputhtml .= " min='" . $this->minimum . "'";
}
if ($this->maximum !== "") {
$inputhtml .= " min='" . $this->maximum . "'";
}
// Classes
if ($this->type != 'hidden') {
$inputhtml .= " class='form-control";
if ($this->required == true) {
$inputhtml .= " required";
}
if ($this->alignright && $this->type != "textarea" && $this->type != "checkbox") {
$this->inputclasses = "alignright";
}
// Decide if align right
if ($this->type == 'textarea') {
if ($this->inputclasses != "") {
$this->inputclasses .= " ";
}
$this->inputclasses .= "block width100";
// Special for align right text area (to NOT align right and show the full one)
}
if (isset($this->inputclasses)) {
$inputhtml .= " " . $this->inputclasses;
}
$inputhtml .= "'";
}
// I'm putting the default value as a dummy attribute, which can be used for javascript resets, etc.
if (isset($this->defaultvalue) && $this->defaultvalue != "" && $this->value != "") {
$inputhtml .= ' data-defaultvalue="' . str_replace('"', '"', htmlentities($this->defaultvalue)) . '"';
}
// Calculation attributes
if (isset($this->calculate_addfields) && !empty($this->calculate_addfields)) {
$inputhtml .= ' data-calculate_addfields="' . implode(",", $this->calculate_addfields) . '"';
}
if (isset($this->calculate_subtractfields) && !empty($this->calculate_subtractfields)) {
$inputhtml .= ' data-calculate_subtractfields="' . implode(",", $this->calculate_subtractfields) . '"';
}
// Helptext association
if ($this->helptext != "") {
$inputhtml .= ' aria-described-by="' . $this->id . '_helpblock"';
}
// Value
if ($this->type == "checkbox") {
$inputhtml .= ' value="1"';
if ($this->value == 1) {
$inputhtml .= ' checked';
}
} else {
if ($this->type != 'textarea' && $this->type != 'select') {
if ($this->value != "") {
$inputhtml .= ' value="' . $db->undbize($this->value) . '"';
} else {
if ($this->defaultvalue !== "") {
$inputhtml .= ' value="' . $db->undbize($this->defaultvalue) . '"';
}
}
}
}
// Readonly
if ($this->readonly) {
$inputhtml .= ' readonly';
}
// Input attributes
if ($this->inputattrs != "") {
$inputhtml .= " " . $this->inputattrs;
}
// End of opening tag
$inputhtml .= ">";
// Textarea value
if ($this->type == 'textarea' && $this->value != "") {
$inputhtml .= $db->undbize($this->value);
}
// Select options
if ($this->type == 'select' && !empty($this->options)) {
$idx = 0;
$optionvalues = array_values($this->options);
if (isset($this->nulloptiontext)) {
$inputhtml .= "<option value=''>" . $this->nulloptiontext . "</option>";
}
foreach ($this->options as $key => $option) {
if (isset($optionvalues[$idx - 1])) {
$lastoption = $optionvalues[$idx - 1];
}
if (isset($optionvalues[$idx + 1])) {
$nextoption = $optionvalues[$idx + 1];
}
$idx++;
if (is_array($option)) {
$option = array_change_key_case($option);
if (isset($lastoption)) {
$lastoption = array_change_key_case($lastoption);
}
if (isset($nextoption)) {
$nextoption = array_change_key_case($nextoption);
}
if (isset($this->optionidkey)) {
$optionidkey = strtolower($this->optionidkey);
} else {
if (isset($option["key"])) {
$optionidkey = "key";
} else {
if (isset($option["value"])) {
$optionidkey = "value";
} else {
$optionidkey = "id";
}
}
}
if (isset($this->optiontextkey)) {
$optiontextkey = strtolower($this->optiontextkey);
} else {
if (isset($option["label"])) {
$optiontextkey = "label";
} else {
if (isset($option["name"])) {
$optiontextkey = "name";
} else {
$optiontextkey = "text";
}
}
}
if (isset($this->optiongroupkey)) {
$optiongroupkey = strtolower($this->optiongroupkey);
}
$value = $option[$optionidkey];
$text = $option[$optiontextkey];
} else {
$value = $key;
$text = $option;
}
if (isset($optiongroupkey) && isset($option[$optiongroupkey]) && (!isset($lastoption) || $option[$optiongroupkey] != $lastoption[$optiongroupkey])) {
$inputhtml .= "<optgroup label='" . $option[$optiongroupkey] . "'>";
}
$inputhtml .= "<option value='{$value}'";
if ($this->value == $value) {
$inputhtml .= " selected";
}
$inputhtml .= ">{$text}</option>";
if (isset($optiongroupkey) && isset($option[$optiongroupkey]) && (!isset($nextoption) || $option[$optiongroupkey] != $nextoption[$optiongroupkey])) {
$inputhtml .= "</optgroup>";
}
}
}
// Close tag
if ($this->type == 'textarea') {
$inputhtml .= "</textarea>";
} else {
if ($this->type == 'select') {
$inputhtml .= "</select>";
} else {
$inputhtml .= "</input>";
}
}
if ($this->postfix != "") {
$inputhtml .= " " . $this->postfix;
}
// Surrounding grid for horizontal
if ($this->horizontal == true && $this->type != "hidden") {
$inputhtml .= "</div>";
}
}
// End prefixandinput div
if (isset($this->prefix) && $this->prefix != "") {
$inputhtml .= "</div>";
}
// Put label & input in order
if ($this->type == "checkbox" && $this->horizontal == false) {
$html .= $inputhtml . " " . $labelhtml;
} else {
$html .= $labelhtml . " " . $inputhtml;
}
// HTML element content
if ($this->type == "html" && $this->content != "") {
$html .= $this->content;
}
// Tooltip
if ($this->tooltip != "") {
$html .= "<span class='badge' style='margin-left:0.5em;' data-toggle='tooltip' data-placement='auto right' title='" . $this->tooltip . "'>\r\n\t\t\t ?\r\n\t\t\t</span>";
}
// Helptext
if ($this->helptext != "") {
$html .= " <span id='" . $this->id . "_helpblock' class='help-block " . $this->helptextclasses . "'>" . $this->helptext . "</span>";
}
// End div
if ($this->type != "hidden") {
$html .= "</div>";
}
// New TR, for table form
if ($this->is_html_table && $this->type != "hidden") {
$html .= "</td>";
if ($this->newline_next) {
$html .= "</tr>";
}
}
$this->html = $html;
return $html;
}
/**
* Display the search engine specific configuration, and the search page specific analytics and suggestions.
*/
public function getCMSFields()
{
$fields = parent::getCMSFields();
Requirements::css(EXTENSIBLE_SEARCH_PATH . '/css/extensible-search.css');
// Determine the search engine extensions that are available.
$engines = array();
foreach (self::config()->search_engine_extensions as $extension => $display) {
// The search engine extensions may define an optional display title.
if (is_numeric($extension)) {
$extension = $display;
}
// Determine whether the search engine extensions have been applied correctly.
if (ClassInfo::exists($extension) && ClassInfo::exists("{$extension}_Controller") && $this->hasExtension($extension) && ModelAsController::controller_for($this)->hasExtension("{$extension}_Controller")) {
$engines[$extension] = $display;
}
}
// Determine whether the full-text search engine is available.
$configuration = Config::inst();
$classes = $configuration->get('FulltextSearchable', 'searchable_classes');
if (is_array($classes) && count($classes) > 0) {
$engines['Full-Text'] = 'Full-Text';
}
// Display the search engine selection.
$fields->addFieldToTab('Root.Main', DropdownField::create('SearchEngine', 'Search Engine', $engines)->setHasEmptyDefault(true)->setRightTitle('This needs to be saved before further customisation is available'), 'Title');
// Determine whether a search engine has been selected.
if ($this->SearchEngine && isset($engines[$this->SearchEngine])) {
// Display a search engine specific notice.
$fields->addFieldToTab('Root.Main', LiteralField::create('SearchEngineNotice', "<p class='extensible-search notice'><strong>{$engines[$this->SearchEngine]} Search Page</strong></p>"), 'Title');
// Determine whether the search engine supports hierarchy filtering.
$hierarchy = self::$supports_hierarchy;
if ($this->SearchEngine !== 'Full-Text') {
foreach ($this->extension_instances as $instance) {
if (get_class($instance) === $this->SearchEngine) {
$instance->setOwner($this);
if (isset($instance::$supports_hierarchy)) {
$hierarchy = $instance::$supports_hierarchy;
}
$instance->clearOwner();
break;
}
}
}
// The search engine may only support limited hierarchy filtering for multiple sites.
if ($hierarchy || ClassInfo::exists('Multisites')) {
// Display the search trees selection.
$fields->addFieldToTab('Root.Main', $tree = TreeMultiselectField::create('SearchTrees', 'Search Trees', 'SiteTree'), 'Content');
// Determine whether the search engine only supports limited hierarchy filtering.
if (!$hierarchy) {
// Update the search trees to reflect this.
$tree->setDisableFunction(function ($page) {
return $page->ParentID != 0;
});
$tree->setRightTitle('This <strong>search engine</strong> only supports limited hierarchy');
}
}
// Display the sorting selection.
$fields->addFieldToTab('Root.Main', DropdownField::create('SortBy', 'Sort By', $this->getSelectableFields()), 'Content');
$fields->addFieldToTab('Root.Main', DropdownField::create('SortDirection', 'Sort Direction', array('DESC' => 'Descending', 'ASC' => 'Ascending')), 'Content');
// Display the start with listing selection.
$fields->addFieldToTab('Root.Main', CheckboxField::create('StartWithListing', 'Start With Listing?')->addExtraClass('start-with-listing'), 'Content');
// Display the results per page selection.
$fields->addFieldToTab('Root.Main', NumericField::create('ResultsPerPage'), 'Content');
} else {
// The search engine has not been selected.
$fields->addFieldToTab('Root.Main', LiteralField::create('SearchEngineNotification', "<p class='extensible-search notification'><strong>Select a Search Engine</strong></p>"), 'Title');
}
// Determine whether analytics have been enabled.
if ($configuration->get('ExtensibleSearch', 'enable_analytics')) {
// Determine the search page specific analytics.
$history = $this->History();
$query = new SQLSelect("Term, COUNT(*) AS Frequency, ((COUNT(*) * 100.00) / {$history->count()}) AS FrequencyPercentage, AVG(Time) AS AverageTimeTaken, (Results > 0) AS Results", 'ExtensibleSearch', "ExtensibleSearchPageID = {$this->ID}", array('Frequency' => 'DESC', 'Term' => 'ASC'), 'Term');
// These will require display formatting.
$analytics = ArrayList::create();
foreach ($query->execute() as $result) {
$result = ArrayData::create($result);
$result->FrequencyPercentage = sprintf('%.2f %%', $result->FrequencyPercentage);
$result->AverageTimeTaken = sprintf('%.5f', $result->AverageTimeTaken);
$result->Results = $result->Results ? 'true' : 'false';
$analytics->push($result);
}
// Instantiate the analytic summary.
$fields->addFieldToTab('Root.SearchAnalytics', $summary = GridField::create('Summary', 'Summary', $analytics)->setModelClass('ExtensibleSearch'));
$summaryConfiguration = $summary->getConfig();
// Update the display columns.
$summaryDisplay = array('Term' => 'Search Term', 'Frequency' => 'Frequency', 'FrequencyPercentage' => 'Frequency %', 'AverageTimeTaken' => 'Average Time Taken (s)', 'Results' => 'Has Results?');
$summaryConfiguration->getComponentByType('GridFieldDataColumns')->setDisplayFields($summaryDisplay);
// Instantiate an export button.
$summaryConfiguration->addComponent($summaryExport = new GridFieldExportButton());
$summaryExport->setExportColumns($summaryDisplay);
// Update the custom summary fields to be sortable.
$summaryConfiguration->getComponentByType('GridFieldSortableHeader')->setFieldSorting(array('FrequencyPercentage' => 'Frequency'));
$summaryConfiguration->removeComponentsByType('GridFieldFilterHeader');
// Instantiate the analytic history.
$fields->addFieldToTab('Root.SearchAnalytics', $history = GridField::create('History', 'History', $history)->setModelClass('ExtensibleSearch'));
$historyConfiguration = $history->getConfig();
// Instantiate an export button.
$historyConfiguration->addComponent(new GridFieldExportButton());
// Update the custom summary fields to be sortable.
$historyConfiguration->getComponentByType('GridFieldSortableHeader')->setFieldSorting(array('TimeSummary' => 'Created', 'TimeTakenSummary' => 'Time', 'SearchEngineSummary' => 'SearchEngine'));
$historyConfiguration->removeComponentsByType('GridFieldFilterHeader');
}
// Determine whether suggestions have been enabled.
if ($configuration->get('ExtensibleSearchSuggestion', 'enable_suggestions')) {
// Appropriately restrict the approval functionality.
$user = Member::currentUserID();
if (Permission::checkMember($user, 'EXTENSIBLE_SEARCH_SUGGESTIONS')) {
Requirements::javascript(EXTENSIBLE_SEARCH_PATH . '/javascript/extensible-search-approval.js');
}
// Determine the search page specific suggestions.
$fields->addFieldToTab('Root.SearchSuggestions', GridField::create('Suggestions', 'Suggestions', $this->Suggestions(), $suggestionsConfiguration = GridFieldConfig_RecordEditor::create())->setModelClass('ExtensibleSearchSuggestion'));
// Update the custom summary fields to be sortable.
$suggestionsConfiguration->getComponentByType('GridFieldSortableHeader')->setFieldSorting(array('FrequencySummary' => 'Frequency', 'FrequencyPercentage' => 'Frequency', 'ApprovedField' => 'Approved'));
$suggestionsConfiguration->removeComponentsByType('GridFieldFilterHeader');
}
// Allow extension customisation.
$this->extend('updateExtensibleSearchPageCMSFields', $fields);
return $fields;
}
public function testParameterisedLeftJoins()
{
$query = new SQLSelect();
$query->setSelect(array('"SQLSelectTest_DO"."Name"', '"SubSelect"."Count"'));
$query->setFrom('"SQLSelectTest_DO"');
$query->addLeftJoin('(SELECT "Title", COUNT(*) AS "Count" FROM "SQLSelectTestBase" GROUP BY "Title" HAVING "Title" NOT LIKE ?)', '"SQLSelectTest_DO"."Name" = "SubSelect"."Title"', 'SubSelect', 20, array('%MyName%'));
$query->addWhere(array('"SQLSelectTest_DO"."Date" > ?' => '2012-08-08 12:00'));
$this->assertSQLEquals('SELECT "SQLSelectTest_DO"."Name", "SubSelect"."Count"
FROM "SQLSelectTest_DO" LEFT JOIN (SELECT "Title", COUNT(*) AS "Count" FROM "SQLSelectTestBase"
GROUP BY "Title" HAVING "Title" NOT LIKE ?) AS "SubSelect" ON "SQLSelectTest_DO"."Name" =
"SubSelect"."Title"
WHERE ("SQLSelectTest_DO"."Date" > ?)', $query->sql($parameters));
$this->assertEquals(array('%MyName%', '2012-08-08 12:00'), $parameters);
$query->execute();
}
}
if ($element_id) {
// Update the metadata
$update_ste = new SQLUpdate('survey_text_element');
$update_ste->values = $values;
$update_ste->selectors['id'] = $element_id;
$update_ste->execute();
} else {
// Update the metadata
$add_ste = new SQLInsert('survey_text_element');
$add_ste->values = $values;
$add_ste->execute();
$selectidq = new SQLSelect('survey_text_element');
$selectidq->selectfields['id'] = 'id';
$selectidq->wherearray = $values;
$res = $selectidq->execute();
$element_id = $res["ID"][0];
}
if ($element_id) {
// Update the languages
$posslanguages = $records->getPossLanguages();
foreach ($posslanguages as $language) {
$translation = filter_input(INPUT_POST, $language['KEYCODE']);
if ($translation) {
$translation = trim($translation);
if ($translation != "") {
$update_lang = new SQLUpdate('translation_text');
$update_lang->insertIfAbsent = true;
$update_lang->selectors['language_keycode'] = $language['KEYCODE'];
$update_lang->selectors['survey_text_element_id'] = $element_id;
$update_lang->values['text_translation'] = trim($translation);
