public function grab(&$param_pool)
{
$result = new XMLElement($this->dsParamROOTELEMENT);
self::__init();
$db = ASDCLoader::instance();
$sql = "SELECT SQL_CALC_FOUND_ROWS \n\t\t\t\t\t\tpinned.entry_id AS `id`, \n\t\t\t\t\t\tpinned.value AS `pinned`, \n\t\t\t\t\t\tclosed.value AS `closed`, \n\t\t\t\t\t\tcreation_date.local AS `creation-date`,\n\t\t\t\t\t\tlast_active.local AS `last-active`,\t\t\t\t\t\t\t\n\t\t\t\t\t\tcreated_by.member_id AS `created-by-member-id`,\n\t\t\t\t\t\tcreated_by.username AS `created-by-username`,\n\t\t\t\t\t\tlast_post.member_id AS `last-post-member-id`,\n\t\t\t\t\t\tlast_post.username AS `last-post-username`,\t\t\t\t\t\t\t\n\t\t\t\t\t\ttopic.value AS `topic`,\n\t\t\t\t\t\tCOUNT(comments.relation_id) AS `comments`\n\t\t\t\t\t\n\t\t\t\t\tFROM `tbl_entries_data_%d` AS `pinned`\n\t\t\t\t\tLEFT JOIN `tbl_entries_data_%d` AS `closed` ON pinned.entry_id = closed.entry_id\n\t\t\t\t\tLEFT JOIN `tbl_entries_data_%d` AS `creation_date` ON pinned.entry_id = creation_date.entry_id\t\n\t\t\t\t\tLEFT JOIN `tbl_entries_data_%d` AS `last_active` ON pinned.entry_id = last_active.entry_id\t\t\t\t\t\n\t\t\t\t\tLEFT JOIN `tbl_entries_data_%d` AS `created_by` ON pinned.entry_id = created_by.entry_id\t\n\t\t\t\t\tLEFT JOIN `tbl_entries_data_%d` AS `last_post` ON pinned.entry_id = last_post.entry_id\t\n\t\t\t\t\tLEFT JOIN `tbl_entries_data_%d` AS `topic` ON pinned.entry_id = topic.entry_id\n\t\t\t\t\tLEFT JOIN `tbl_entries_data_%d` AS `comments` ON pinned.entry_id = comments.relation_id\n\t\t\t\t\tWHERE 1 %s\n\t\t\t\t\tGROUP BY pinned.entry_id\n\t\t\t\t\tORDER BY pinned.value ASC, last_active.local DESC\n\t\t\t\t\tLIMIT %d, %d";
try {
$rows = $db->query(sprintf($sql, self::findFieldID('pinned', 'discussions'), self::findFieldID('closed', 'discussions'), self::findFieldID('creation-date', 'discussions'), self::findFieldID('last-active', 'discussions'), self::findFieldID('created-by', 'discussions'), self::findFieldID('last-post', 'discussions'), self::findFieldID('topic', 'discussions'), self::findFieldID('parent-id', 'comments'), isset($this->dsParamFILTERS['id']) && (int) $this->dsParamFILTERS['id'] > 0 ? " AND pinned.entry_id = " . (int) $this->dsParamFILTERS['id'] : NULL, max(0, ($this->dsParamSTARTPAGE - 1) * $this->dsParamLIMIT), $this->dsParamLIMIT));
} catch (Exception $e) {
$result->appendChild(new XMLElement('error', General::sanitize(vsprintf('%d: %s on query %s', $db->lastError()))));
return $result;
}
if ($rows->length() == 0 && strlen(trim($dsParamFILTERS['id'])) > 0) {
$this->__redirectToErrorPage();
} elseif ($rows->length() == 0) {
return $this->emptyXMLSet();
}
$total = $db->query('SELECT FOUND_ROWS() AS `total`;')->current()->total;
$result->prependChild(General::buildPaginationElement($total, ceil($total * (1 / $this->dsParamLIMIT)), $this->dsParamLIMIT, $this->dsParamSTARTPAGE));
/*
stdClass Object
(
[id] => 666
[pinned] => yes
[closed] => no
[creation-date] => 1233599808
[last-active] => 1237161637
[created-by-member-id] => 2126
[created-by-username] => Lewis
[last-post-member-id] => 2126
[last-post-username] => Lewis
[topic] => Symphony 2 Documentation
[comments] => 18
)
<entry id="595" comments="7">
<created-by id="2150">newnomad</created-by>
<closed>No</closed>
<last-active time="18:30" weekday="1">2009-02-09</last-active>
<last-post id="2150">newnomad</last-post>
<pinned>No</pinned>
<topic handle="viewing-feeds">viewing feeds</topic>
<creation-date time="19:31" weekday="3">2009-01-07</creation-date>
</entry>
*/
$param_pool['ds-' . $this->dsParamROOTELEMENT] = DatabaseUtilities::resultColumn($rows, 'id');
foreach ($rows as $r) {
$entry = new XMLElement('entry', NULL, array('id' => $r->id, 'comments' => $r->comments));
$entry->appendChild(new XMLElement('created-by', General::sanitize($r->{'created-by-username'}), array('id' => $r->{'created-by-member-id'})));
$entry->appendChild(new XMLElement('last-post', General::sanitize($r->{'last-post-username'}), array('id' => $r->{'last-post-member-id'})));
$entry->appendChild(new XMLElement('closed', ucfirst($r->closed)));
$entry->appendChild(new XMLElement('pinned', ucfirst($r->pinned)));
$entry->appendChild(new XMLElement('topic', General::sanitize($r->topic)));
$entry->appendChild(General::createXMLDateObject($r->{'creation-date'}, 'creation-date'));
$entry->appendChild(General::createXMLDateObject($r->{'last-active'}, 'last-active'));
$result->appendChild($entry);
}
return $result;
}
public function grab(&$param_pool)
{
self::__init();
/*
var $dsParamINCLUDEDELEMENTS = array(
'system:pagination',
'comment',
'date',
'created-by'
);
<pagination total-entries="28" total-pages="2" entries-per-page="20" current-page="2" />
<section id="39" handle="comments">Comments</section>
*/
$result = new XMLElement($this->dsParamROOTELEMENT);
try {
$comments = ASDCLoader::instance()->query(sprintf("SELECT SQL_CALC_FOUND_ROWS\n\t\t\t\t\t\t\tcomment.entry_id AS `id`,\n\t\t\t\t\t\t\tcomment.value_formatted AS `comment`, \n\t\t\t\t\t\t\tcreated_by.member_id, \n\t\t\t\t\t\t\tcreated_by.username, \n\t\t\t\t\t\t\tdate.local AS `date`,\n\t\t\t\t\t\t\temail.value AS `email`\n\n\t\t\t\t\t\tFROM `tbl_entries_data_%d` AS `comment`\n\t\t\t\t\t\tLEFT JOIN `tbl_entries_data_%d` AS `created_by` ON comment.entry_id = created_by.entry_id\n\t\t\t\t\t\tLEFT JOIN `tbl_entries_data_%d` AS `date` ON comment.entry_id = date.entry_id\n\t\t\t\t\t\tLEFT JOIN `tbl_entries_data_%d` AS `email` ON created_by.member_id = email.entry_id\n\t\t\t\t\t\tLEFT JOIN `tbl_entries_data_%d` AS `discussion` ON comment.entry_id = discussion.entry_id\n\t\t\t\t\t\tWHERE discussion.relation_id = %d\n\t\t\t\t\t\tORDER BY date.local ASC\n\t\t\t\t\t\tLIMIT %d, %d", self::findFieldID('comment', 'comments'), self::findFieldID('created-by', 'comments'), self::findFieldID('date', 'comments'), self::findFieldID('email-address', 'members'), self::findFieldID('parent-id', 'comments'), (int) $this->dsParamFILTERS['discussion_id'], max(0, ($this->dsParamSTARTPAGE - 1) * $this->dsParamLIMIT), (int) $this->dsParamLIMIT));
} catch (Exception $e) {
$result->appendChild(new XMLElement('error', General::sanitize(vsprintf('%d: %s on query %s', ASDCLoader::instance()->lastError()))));
return $result;
}
if ($comments->length() == 0) {
$this->__redirectToErrorPage();
}
$total = ASDCLoader::instance()->query('SELECT FOUND_ROWS() AS `total`;')->current()->total;
$result->prependChild(General::buildPaginationElement($total, ceil($total * (1 / $this->dsParamLIMIT)), $this->dsParamLIMIT, $this->dsParamSTARTPAGE));
foreach ($comments as $c) {
/*
stdClass Object
(
[id] => 20589
[comment] => <p>blah blah</p>
[member_id] => 2103
[username] => Alistair
[date] => 1241576727
[email] => alistair@21degrees.com.au
)
<entry id="20515">
<date time="01:32" weekday="3">2009-05-06</date>
<comment word-count="6"><p>This site looks awesome guys! Congrats!</p></comment>
<created-by id="2694">davethegr8</created-by>
</entry>
*/
$entry = new XMLElement('entry', NULL, array('id' => $c->id));
$entry->appendChild(new XMLElement('created-by', General::sanitize($c->username), array('email-address-hash' => md5($c->email), 'email-address' => General::sanitize($c->email), 'id' => $c->member_id)));
$entry->appendChild(General::createXMLDateObject($c->date, 'date'));
$c->comment = str_replace(array('<script', '</script'), array('<script', '</script'), $c->comment);
$entry->appendChild(new XMLElement('comment', trim($c->comment)));
$result->appendChild($entry);
}
return $result;
}
private function __search($query)
{
$result = new XMLElement($this->dsParamROOTELEMENT);
if (strlen(trim($query)) == 0) {
return $this->emptyXMLSet($result);
}
$db = ASDCLoader::instance();
$result->appendChild(new XMLElement('query-string', General::sanitize($query), array('encoded' => urlencode($query))));
$sql = "SELECT SQL_CALC_FOUND_ROWS \n\t\t\t\t\t\tMATCH(comment.value) AGAINST ('%6\$s') AS `score`,\n\t\t\t\t\t\tcomment.entry_id AS `id`,\n\t\t\t\t\t\tdate.local AS `date`,\n\t\t\t\t\t\tcomment.value_formatted AS `description`,\n\t\t\t\t\t\tmember.member_id AS `member-id`, \n\t\t\t\t\t\tmember.username AS `username`,\n\t\t\t\t\t\ttopic.value AS `topic`,\n\t\t\t\t\t\tparent.relation_id AS `discussion-id`\n\t\t\t\t\t\t\n\t\t\t\t\tFROM `tbl_entries_data_%1\$d` AS `date`\n\t\t\t\t\tLEFT JOIN `tbl_entries_data_%2\$d` AS `comment` ON date.entry_id = comment.entry_id\n\t\t\t\t\tLEFT JOIN `tbl_entries_data_%3\$d` AS `member` ON date.entry_id = member.entry_id\n\t\t\t\t\tLEFT JOIN `tbl_entries_data_%4\$d` AS `parent` ON date.entry_id = parent.entry_id\n\t\t\t\t\tLEFT JOIN `tbl_entries_data_%5\$d` AS `topic` ON parent.relation_id = topic.entry_id\n\n\t\t\t\t\tWHERE MATCH(comment.value) AGAINST ('%6\$s')\n\t\t\t\t\tORDER BY `score` DESC\n\t\t\t\t\tLIMIT %7\$d, %8\$d";
//MATCH(comment.value) AGAINST ('%s') AS `score`,
//OR MATCH(comment.value) AGAINST ('%1\$s')
//WITH QUERY EXPANSION
//member.username = '******' OR comment.value LIKE '%%%6\$s%%' OR topic.value LIKE '%%%6\$s%%'
try {
$rows = $db->query(sprintf($sql, self::findFieldID('date', 'comments'), self::findFieldID('comment', 'comments'), self::findFieldID('created-by', 'comments'), self::findFieldID('parent-id', 'comments'), self::findFieldID('topic', 'discussions'), $db->escape($query), max(0, ($this->dsParamSTARTPAGE - 1) * $this->dsParamLIMIT), $this->dsParamLIMIT));
} catch (Exception $e) {
$result->appendChild(new XMLElement('error', General::sanitize(vsprintf('%d: %s on query %s', $db->lastError()))));
return $result;
}
if ($rows->length() == 0) {
return $this->emptyXMLSet($result);
}
$total = $db->query('SELECT FOUND_ROWS() AS `total`;')->current()->total;
$result->prependChild(General::buildPaginationElement($total, ceil($total * (1 / $this->dsParamLIMIT)), $this->dsParamLIMIT, $this->dsParamSTARTPAGE));
/*
<entry id="19753">
<name>Section Schema</name>
<member id="2101">Allen</member>
<description><p>Sect ... ollow).</p></description>
</entry>
*/
foreach ($rows as $r) {
$entry = new XMLElement('entry', NULL, array('discussion-id' => $r->{'discussion-id'}, 'id' => $r->id, 'score' => number_format($r->score, 3)));
// Topic
$entry->appendChild(new XMLElement('topic', General::sanitize($r->topic)));
// Date
$entry->appendChild(General::createXMLDateObject($r->date, 'date'));
// Member
$entry->appendChild(new XMLElement('member', General::sanitize($r->{'username'}), array('id' => $r->{'member-id'})));
// Comment
$entry->appendChild(new XMLElement('comment', trim($r->description)));
$result->appendChild($entry);
}
return $result;
}
public function execute(array &$param_pool)
{
$result = new XMLElement($this->dsParamROOTELEMENT);
$this->_param_pool = $param_pool;
$where = NULL;
$joins = NULL;
$group = false;
include_once TOOLKIT . '/class.entrymanager.php';
if (!($section = SectionManager::fetch((int) $this->getSource()))) {
$about = $this->about();
trigger_error(__('The section associated with the data source %s could not be found.', array('<code>' . $about['name'] . '</code>')), E_USER_ERROR);
}
$sectioninfo = new XMLElement('section', General::sanitize($section->get('name')), array('id' => $section->get('id'), 'handle' => $section->get('handle')));
if ($this->_force_empty_result == true) {
$this->_force_empty_result = false;
//this is so the section info element doesn't disappear.
$result = $this->emptyXMLSet();
$result->prependChild($sectioninfo);
return;
}
if (is_array($this->dsParamINCLUDEDELEMENTS)) {
$include_pagination_element = in_array('system:pagination', $this->dsParamINCLUDEDELEMENTS);
} else {
$this->dsParamINCLUDEDELEMENTS = array();
}
if (isset($this->dsParamPARAMOUTPUT) && !is_array($this->dsParamPARAMOUTPUT)) {
$this->dsParamPARAMOUTPUT = array($this->dsParamPARAMOUTPUT);
}
$this->_can_process_system_parameters = $this->canProcessSystemParameters();
if (!isset($this->dsParamPAGINATERESULTS)) {
$this->dsParamPAGINATERESULTS = 'yes';
}
// Process Filters
$this->processFilters($where, $joins, $group);
// Process Sorting
if ($this->dsParamSORT == 'system:id') {
EntryManager::setFetchSorting('id', $this->dsParamORDER);
} else {
if ($this->dsParamSORT == 'system:date') {
EntryManager::setFetchSorting('date', $this->dsParamORDER);
} else {
EntryManager::setFetchSorting(FieldManager::fetchFieldIDFromElementName($this->dsParamSORT, $this->getSource()), $this->dsParamORDER);
}
}
// combine `INCLUDEDELEMENTS`, `PARAMOUTPUT` and `GROUP` into an
// array of field handles to optimise the `EntryManager` queries
$datasource_schema = $this->dsParamINCLUDEDELEMENTS;
if (is_array($this->dsParamPARAMOUTPUT)) {
$datasource_schema = array_merge($datasource_schema, $this->dsParamPARAMOUTPUT);
}
if ($this->dsParamGROUP) {
$datasource_schema[] = FieldManager::fetchHandleFromID($this->dsParamGROUP);
}
$entries = EntryManager::fetchByPage($this->dsParamPAGINATERESULTS == 'yes' && $this->dsParamSTARTPAGE > 0 ? $this->dsParamSTARTPAGE : 1, $this->getSource(), $this->dsParamPAGINATERESULTS == 'yes' && $this->dsParamLIMIT >= 0 ? $this->dsParamLIMIT : NULL, $where, $joins, $group, !$include_pagination_element ? true : false, true, array_unique($datasource_schema));
/**
* Immediately after building entries allow modification of the Data Source entry list
*
* @delegate DataSourceEntriesBuilt
* @param string $context
* '/frontend/'
* @param Datasource $datasource
* @param array $entries
* @param array $filters
*/
Symphony::ExtensionManager()->notifyMembers('DataSourceEntriesBuilt', '/frontend/', array('datasource' => &$this, 'entries' => &$entries, 'filters' => $this->dsParamFILTERS));
if (($entries['total-entries'] <= 0 || $include_pagination_element === true) && (!is_array($entries['records']) || empty($entries['records'])) || $this->dsParamSTARTPAGE == '0') {
if ($this->dsParamREDIRECTONEMPTY == 'yes') {
throw new FrontendPageNotFoundException();
}
$this->_force_empty_result = false;
$result = $this->emptyXMLSet();
$result->prependChild($sectioninfo);
if ($include_pagination_element) {
$pagination_element = General::buildPaginationElement();
if ($pagination_element instanceof XMLElement && $result instanceof XMLElement) {
$result->prependChild($pagination_element);
}
}
} else {
if (!$this->_param_output_only) {
$result->appendChild($sectioninfo);
if ($include_pagination_element) {
$t = $this->dsParamPAGINATERESULTS == 'yes' && isset($this->dsParamLIMIT) && $this->dsParamLIMIT >= 0 ? $this->dsParamLIMIT : $entries['total-entries'];
$pagination_element = General::buildPaginationElement($entries['total-entries'], $entries['total-pages'], $t, $this->dsParamPAGINATERESULTS == 'yes' && $this->dsParamSTARTPAGE > 0 ? $this->dsParamSTARTPAGE : 1);
if ($pagination_element instanceof XMLElement && $result instanceof XMLElement) {
$result->prependChild($pagination_element);
}
}
}
// If this datasource has a Limit greater than 0 or the Limit is not set
if (!isset($this->dsParamLIMIT) || $this->dsParamLIMIT > 0) {
if (!isset($this->dsParamASSOCIATEDENTRYCOUNTS) || $this->dsParamASSOCIATEDENTRYCOUNTS == 'yes') {
$this->_associated_sections = $section->fetchAssociatedSections();
}
// If the datasource require's GROUPING
if (isset($this->dsParamGROUP)) {
self::$_fieldPool[$this->dsParamGROUP] =& FieldManager::fetch($this->dsParamGROUP);
$groups = self::$_fieldPool[$this->dsParamGROUP]->groupRecords($entries['records']);
foreach ($groups as $element => $group) {
foreach ($group as $g) {
$result->appendChild($this->processRecordGroup($element, $g));
}
}
} else {
if (isset($entries['records'][0])) {
$data = $entries['records'][0]->getData();
$pool = FieldManager::fetch(array_keys($data));
self::$_fieldPool += $pool;
}
foreach ($entries['records'] as $entry) {
$xEntry = $this->processEntry($entry);
if ($xEntry instanceof XMLElement) {
$result->appendChild($xEntry);
}
}
}
}
}
$param_pool = $this->_param_pool;
return $result;
}
if ($include_pagination_element) {
$pagination_element = General::buildPaginationElement();
if ($pagination_element instanceof XMLElement && $result instanceof XMLElement) {
$result->prependChild($pagination_element);
}
}
if (isset($this->dsParamPARAMOUTPUT)) {
$param_pool[$key][] = '';
}
} else {
if (!$this->_param_output_only) {
$result = new XMLElement($this->dsParamROOTELEMENT);
$result->appendChild($sectioninfo);
if ($include_pagination_element) {
$t = $this->dsParamPAGINATERESULTS == 'yes' && isset($this->dsParamLIMIT) && $this->dsParamLIMIT >= 0 ? $this->dsParamLIMIT : $entries['total-entries'];
$pagination_element = General::buildPaginationElement($entries['total-entries'], $entries['total-pages'], $t, $this->dsParamPAGINATERESULTS == 'yes' && $this->dsParamSTARTPAGE > 0 ? $this->dsParamSTARTPAGE : 1);
if ($pagination_element instanceof XMLElement && $result instanceof XMLElement) {
$result->prependChild($pagination_element);
}
}
}
if (isset($this->dsParamPARAMOUTPUT) && !is_array($param_pool[$key])) {
$param_pool[$key] = array();
}
if (!isset($this->dsParamLIMIT) || $this->dsParamLIMIT > 0) {
if (isset($this->dsParamGROUP)) {
$fieldPool[$this->dsParamGROUP] =& $entryManager->fieldManager->fetch($this->dsParamGROUP);
$groups = $fieldPool[$this->dsParamGROUP]->groupRecords($entries['records']);
foreach ($groups as $element => $group) {
foreach ($group as $g) {
processRecordGroup($result, $element, $g, $this, $entryManager, $fieldPool, $param_pool, $this->_param_output_only);
public function processResults(SymRead $query, Section $section, array $pagination, array $entries)
{
$section_xml = new XMLElement('section', $section->get('name'));
$section_xml->setAttribute('id', $section->get('id'));
$section_xml->setAttribute('handle', $section->get('handle'));
$this->appendChild($section_xml);
$this->appendChild(General::buildPaginationElement($pagination['total-entries'], $pagination['total-pages'], $pagination['entries-per-page'], $pagination['current-page']));
foreach ($entries as $entry) {
$entry_xml = new XMLElement('entry');
$entry_xml->setAttribute('id', $entry->get('id'));
foreach ($query->selects as $select) {
$field = $select->get('object');
if ($field == SymQuery::SYSTEM_DATE) {
$entry_xml->appendChild(General::createXMLDateObject(strtotime($entry->creationDate), 'system-date'));
}
if (!$field instanceof Field) {
continue;
}
$data = $entry->getData($field->get('id'));
if ($select->has('mode')) {
$field->appendFormattedElement($entry_xml, $data, false, $select->get('mode'));
} else {
$field->appendFormattedElement($entry_xml, $data, false);
}
}
$this->appendChild($entry_xml);
}
return $this;
}
public static function run(SymQLQuery $query, $output = SymQL::RETURN_XML)
{
self::init();
self::getQueryCount();
// stores all config locally so that the same SymQLManager can be used for mutliple queries
$section = null;
$section_fields = array();
$where = null;
$joins = null;
$entry_ids = array();
// resolve section
$resolved_section = self::getResolvedSection($query->section);
if (is_null($resolved_section)) {
$section = $query->section;
if (!is_numeric($query->section)) {
$section = self::$_sectionManager->fetchIDFromHandle($query->section);
}
$section = self::$_sectionManager->fetch($section);
if (!$section instanceof Section) {
throw new Exception(sprintf("%s: section '%s' does not not exist", __CLASS__, $query->section));
}
$fields = $section->fetchFields();
self::$_resolved_sections[] = $section;
self::$_resolved_fields[$section->get('id')] = $fields;
} else {
$section = $resolved_section;
$fields = self::getResolvedFields($section->get('id'));
}
self::$_debug['queries']['Resolve section and fields'] = self::getQueryCount();
// cache list of field objects in this section (id => object)
foreach ($fields as $field) {
$section_fields[] = $field->get('id');
}
$section_fields = self::indexFieldsByID($section_fields, $fields, true);
// resolve list of fields from SELECT statement
if ($query->fields == '*') {
foreach ($fields as $field) {
$select_fields[] = $field->get('element_name');
}
} else {
$select_fields = $query->fields;
}
$select_fields = self::indexFieldsByID($select_fields, $fields);
// resolve list of fields from WHERE statements (filters)
$filters = array();
if (is_array($query->filters)) {
foreach ($query->filters as $i => $filter) {
$field = self::indexFieldsByID($filter['field'], $fields);
if ($field) {
$filters[$i][reset(array_keys($field))]['value'] = $filter['value'];
$filters[$i][reset(array_keys($field))]['type'] = $filter['type'];
}
}
}
// resolve sort field
if (in_array($query->sort_field, self::$_reserved_fields)) {
$handle_exploded = explode(':', $query->sort_field);
if (count($handle_exploded) == 2) {
self::$_entryManager->setFetchSorting(end($handle_exploded), $query->sort_direction);
}
} else {
$sort_field = self::indexFieldsByID($query->sort_field, $fields);
$sort_field = $section_fields[reset(array_keys($sort_field))];
if ($sort_field && $sort_field->isSortable()) {
self::$_entryManager->setFetchSorting($sort_field->get('id'), $query->sort_direction);
}
}
$where = null;
$joins = null;
foreach ($filters as $filter) {
$field_id = reset(array_keys($filter));
$filter = reset($filter);
if ($field_id == 'system:id') {
$entry_ids[] = (int) $filter['value'];
continue;
}
// get the cached field object
$field = $section_fields[$field_id];
if (!$field) {
throw new Exception(sprintf("%s: field '%s' does not not exist", __CLASS__, $field_id));
}
if (!$field->canFilter() || !method_exists($field, 'buildDSRetrivalSQL')) {
throw new Exception(sprintf("%s: field '%s' can not be used as a filter", __CLASS__, $field_id));
}
// local
$_where = null;
$_joins = null;
$filter_type = false === strpos($filter['value'], '+') ? self::DS_FILTER_OR : self::DS_FILTER_AND;
$value = preg_split('/' . ($filter_type == self::DS_FILTER_AND ? '\\+' : ',') . '\\s*/', $filter['value'], -1, PREG_SPLIT_NO_EMPTY);
if (!is_array($value)) {
$value = array($value);
}
// Get the WHERE and JOIN from the field
$where_before = $_where;
$field->buildDSRetrivalSQL($value, $_joins, $_where, $filter_type == self::DS_FILTER_AND ? true : false);
// HACK: if this is an OR statement, strip the first AND from the returned SQL
// and replace with OR
if ($filter['type'] == SymQL::DS_FILTER_OR) {
$_where_after = substr($_where, strlen($_where_before), strlen($where));
$_where_after = preg_replace('/^AND/', 'OR', trim($_where_after));
$_where = $_where_before . $_where_after;
}
$joins .= $_joins;
$where .= $_where;
}
// resolve the SELECT type and fetch entries
if (reset(array_keys($select_fields)) == 'system:count') {
$select_type = SymQL::SELECT_COUNT;
$fetch_result = (int) self::$_entryManager->fetchCount($section->get('id'), $where, $joins);
} else {
if (count($entry_ids) > 0) {
$select_type = SymQL::SELECT_ENTRY_ID;
$fetch_result = self::$_entryManager->fetch($entry_ids, $section->get('id'), $query->per_page, null, null, false, false, true, array_values($select_fields));
} else {
$select_type = SymQL::SELECT_ENTRIES;
$fetch_result = self::$_entryManager->fetchByPage($query->page, $section->get('id'), $query->per_page, $where, $joins, false, false, true, array_values($select_fields));
}
}
self::$_debug['sql']['joins'] = $joins;
self::$_debug['sql']['where'] = $where;
self::$_debug['queries']['Fetch entries'] = self::getQueryCount();
// section metadata
$section_metadata = array('name' => $section->get('name'), 'id' => $section->get('id'), 'handle' => $section->get('handle'));
// build pagination metadata
if ($select_type == SymQL::SELECT_ENTRIES) {
$pagination = array('total-entries' => (int) $fetch_result['total-entries'], 'total-pages' => (int) $fetch_result['total-pages'], 'per-page' => (int) $fetch_result['limit'], 'current-page' => (int) $query->page);
}
// find the array of entries returned from EntryManager fetch
$entries = array();
switch ($select_type) {
case SymQL::SELECT_ENTRY_ID:
$entries = $fetch_result;
break;
case SymQL::SELECT_ENTRIES:
$entries = $fetch_result['records'];
break;
case SymQL::SELECT_COUNT:
$count = $fetch_result;
break;
}
// set up result container depending on return type
switch ($output) {
case SymQL::RETURN_ARRAY:
case SymQL::RETURN_RAW_COLUMNS:
case SymQL::RETURN_ENTRY_OBJECTS:
$result = array();
$result['section'] = $section_metadata;
if ($pagination) {
$result['pagination'] = $pagination;
}
break;
case SymQL::RETURN_XML:
$result = new XMLElement($query->root_element ? $query->root_element : 'symql');
$result->appendChild(new XMLElement('section', $section_metadata['name'], array('id' => $section_metadata['id'], 'handle' => $section_metadata['handle'])));
if ($pagination) {
$result->appendChild(General::buildPaginationElement($pagination['total-entries'], $pagination['total-pages'], $pagination['per-page'], $pagination['current-page']));
}
break;
}
// append returned entries to results container
if ($select_type == SymQL::SELECT_ENTRY_ID || $select_type == SymQL::SELECT_ENTRIES) {
foreach ($entries as $entry) {
switch ($output) {
case SymQL::RETURN_RAW_COLUMNS:
$fields = array();
foreach ($entry->getData() as $field_id => $values) {
$field = $section_fields[$field_id];
$fields[$field->get('element_name')] = $values;
}
$result['entries'][$entry->get('id')] = $fields;
break;
case SymQL::RETURN_ENTRY_OBJECTS:
$result['entries'][$entry->get('id')] = $entry;
break;
case SymQL::RETURN_XML:
case SymQL::RETURN_ARRAY:
$xml_entry = new XMLElement('entry');
$xml_entry->setAttribute('id', $entry->get('id'));
foreach ($entry->getData() as $field_id => $values) {
$field = $section_fields[$field_id];
$handle = $field->get('element_name');
$handle_exploded = explode(':', $select_fields[$field_id]);
if (count($handle_exploded) == 2) {
$mode = end($handle_exploded);
}
$field->appendFormattedElement($xml_entry, $values, $encode, $mode);
}
if ($output == SymQL::RETURN_ARRAY) {
$result['entries'][] = XMLToArray::convert($xml_entry->generate());
} else {
$result->appendChild($xml_entry);
}
break;
}
}
} elseif ($select_type == SymQL::SELECT_COUNT) {
switch ($output) {
case SymQL::RETURN_ARRAY:
case SymQL::RETURN_RAW_COLUMNS:
case SymQL::RETURN_ENTRY_OBJECTS:
$result['count'] = $count;
break;
case SymQL::RETURN_XML:
$xml_entry = new XMLElement('count', $count);
$result->appendChild($xml_entry);
break;
}
}
self::$_debug['queries']['Total'] = self::$_context->Database->queryCount() - self::$_base_querycount;
// reset for the next query
self::$_entryManager->setFetchSorting(null, null);
self::$_base_querycount = null;
self::$_cumulative_querycount = null;
return $result;
}
public function grab(&$param_pool)
{
$Members = Frontend::instance()->ExtensionManager->create('members');
$Members->initialiseCookie();
if ($Members->isLoggedIn() !== true) {
// Oi! you can't be here
redirect(URL . '/forbidden/');
exit;
}
$result = new XMLElement($this->dsParamROOTELEMENT);
self::__init();
$db = ASDCLoader::instance();
$sql = 'SELECT SQL_CALC_FOUND_ROWS
pinned.entry_id AS `id`,
pinned.value AS `pinned`,
closed.value AS `closed`,
creation_date.local AS `creation-date`,
last_active.local AS `last-active`,
created_by.member_id AS `created-by-member-id`,
created_by.username AS `created-by-username`,
last_post.member_id AS `last-post-member-id`,
last_post.username AS `last-post-username`,
topic.value AS `topic`
FROM `tbl_entries_data_%d` AS `pinned`
LEFT JOIN `tbl_entries_data_%d` AS `closed` ON pinned.entry_id = closed.entry_id
LEFT JOIN `tbl_entries_data_%d` AS `creation_date` ON pinned.entry_id = creation_date.entry_id
LEFT JOIN `tbl_entries_data_%d` AS `last_active` ON pinned.entry_id = last_active.entry_id
LEFT JOIN `tbl_entries_data_%d` AS `created_by` ON pinned.entry_id = created_by.entry_id
LEFT JOIN `tbl_entries_data_%d` AS `last_post` ON pinned.entry_id = last_post.entry_id
LEFT JOIN `tbl_entries_data_%d` AS `topic` ON pinned.entry_id = topic.entry_id
LEFT JOIN `tbl_entries_data_%d` AS `comments` ON pinned.entry_id = comments.relation_id
LEFT JOIN `tbl_entries_data_%d` AS `discussion_comments_member` ON comments.entry_id = discussion_comments_member.entry_id
WHERE 1 %s
AND (created_by.member_id = %11$d || discussion_comments_member.member_id = %11$d)
GROUP BY pinned.entry_id
ORDER BY pinned.value ASC, last_active.local DESC
LIMIT %12$d, %13$d';
try {
$rows = $db->query(sprintf($sql, self::findFieldID('pinned', 'discussions'), self::findFieldID('closed', 'discussions'), self::findFieldID('creation-date', 'discussions'), self::findFieldID('last-active', 'discussions'), self::findFieldID('created-by', 'discussions'), self::findFieldID('last-post', 'discussions'), self::findFieldID('topic', 'discussions'), self::findFieldID('parent-id', 'comments'), self::findFieldID('created-by', 'comments'), isset($this->dsParamFILTERS['id']) && (int) $this->dsParamFILTERS['id'] > 0 ? " AND pinned.entry_id = " . (int) $this->dsParamFILTERS['id'] : NULL, (int) $Members->Member->get('id'), max(0, ($this->dsParamSTARTPAGE - 1) * $this->dsParamLIMIT), $this->dsParamLIMIT));
} catch (Exception $e) {
$result->appendChild(new XMLElement('error', General::sanitize(vsprintf('%d: %s on query %s', $db->lastError()))));
return $result;
}
if ($rows->length() == 0) {
return $this->emptyXMLSet();
}
$total = $db->query('SELECT FOUND_ROWS() AS `total`;')->current()->total;
$result->prependChild(General::buildPaginationElement($total, ceil($total * (1 / $this->dsParamLIMIT)), $this->dsParamLIMIT, $this->dsParamSTARTPAGE));
/*
stdClass Object
(
[id] => 666
[pinned] => yes
[closed] => no
[creation-date] => 1233599808
[last-active] => 1237161637
[created-by-member-id] => 2126
[created-by-username] => Lewis
[last-post-member-id] => 2126
[last-post-username] => Lewis
[topic] => Symphony 2 Documentation
[comments] => 18
)
<entry id="595" comments="7">
<created-by id="2150">newnomad</created-by>
<closed>No</closed>
<last-active time="18:30" weekday="1">2009-02-09</last-active>
<last-post id="2150">newnomad</last-post>
<pinned>No</pinned>
<topic handle="viewing-feeds">viewing feeds</topic>
<creation-date time="19:31" weekday="3">2009-01-07</creation-date>
</entry>
*/
$param_pool['ds-' . $this->dsParamROOTELEMENT] = DatabaseUtilities::resultColumn($rows, 'id');
foreach ($rows as $r) {
// Need to do a seperate query to find the comment counts.
try {
$comments = $db->query(sprintf("SELECT COUNT(*) AS `count` FROM `tbl_entries_data_%d` WHERE `relation_id` = %d ", self::findFieldID('parent-id', 'comments'), $r->id))->current()->count;
} catch (Exception $e) {
$result->appendChild(new XMLElement('error', General::sanitize(vsprintf('%d: %s on query %s', $db->lastError()))));
return $result;
}
$entry = new XMLElement('entry', NULL, array('id' => $r->id, 'comments' => $comments));
$entry->appendChild(new XMLElement('created-by', General::sanitize($r->{'created-by-username'}), array('id' => $r->{'created-by-member-id'})));
$entry->appendChild(new XMLElement('last-post', General::sanitize($r->{'last-post-username'}), array('id' => $r->{'last-post-member-id'})));
$entry->appendChild(new XMLElement('closed', ucfirst($r->closed)));
$entry->appendChild(new XMLElement('pinned', ucfirst($r->pinned)));
$entry->appendChild(new XMLElement('topic', General::sanitize($r->topic)));
$entry->appendChild(General::createXMLDateObject($r->{'creation-date'}, 'creation-date'));
$entry->appendChild(General::createXMLDateObject($r->{'last-active'}, 'last-active'));
$result->appendChild($entry);
}
return $result;
}
public function execute(array &$param_pool = null)
{
$result = new XMLElement($this->dsParamROOTELEMENT);
$config = (object) Symphony::Configuration()->get('search_index');
// Setup
/*-----------------------------------------------------------------------*/
// look for key in GET array if it's specified
if (!empty($config->{'get-param-prefix'})) {
if ($config->{'get-param-prefix'} == 'param_pool') {
$_GET = $this->_env['param'];
} else {
$_GET = $_GET[$config->{'get-param-prefix'}];
}
}
// get input parameters from GET request
$param_keywords = isset($_GET[$config->{'get-param-keywords'}]) ? trim($_GET[$config->{'get-param-keywords'}]) : '';
$param_sort = isset($_GET[$config->{'get-param-sort'}]) ? $_GET[$config->{'get-param-sort'}] : $config->{'default-sort'};
$param_direction = isset($_GET[$config->{'get-param-direction'}]) ? strtolower($_GET[$config->{'get-param-direction'}]) : $config->{'default-direction'};
// set pagination on the data source
$this->dsParamSTARTPAGE = isset($_GET[$config->{'get-param-page'}]) ? (int) $_GET[$config->{'get-param-page'}] : $this->dsParamSTARTPAGE;
$this->dsParamLIMIT = isset($_GET[$config->{'get-param-per-page'}]) && (int) $_GET[$config->{'get-param-per-page'}] > 0 ? (int) $_GET[$config->{'get-param-per-page'}] : $config->{'default-per-page'};
// build ORDER BY statement for later
switch ($param_sort) {
case 'date':
$sql_order_by = "e.creation_date {$param_direction}";
break;
case 'id':
$sql_order_by = "e.id {$param_direction}";
break;
default:
$sql_order_by = "score {$param_direction}";
break;
}
// Find valid sections to query
/*-----------------------------------------------------------------------*/
if (isset($_GET[$config->{'get-param-sections'}]) && !empty($_GET[$config->{'get-param-sections'}])) {
$param_sections = $_GET[$config->{'get-param-sections'}];
// allow sections to be sent as an array if the user wishes (multi-select or checkboxes)
if (is_array($param_sections)) {
implode(',', $param_sections);
}
} elseif (!empty($config->{'default-sections'})) {
$param_sections = $config->{'default-sections'};
} else {
$param_sections = '';
}
$sections = array();
foreach (array_map('trim', explode(',', $param_sections)) as $handle) {
$section = Symphony::Database()->fetchRow(0, sprintf("SELECT `id`, `name` FROM `tbl_sections` WHERE handle = '%s' LIMIT 1", Symphony::Database()->cleanValue($handle)));
if ($section) {
$sections[$section['id']] = array('handle' => $handle, 'name' => $section['name']);
}
}
if (count($sections) == 0) {
return $this->errorXML('Invalid search sections');
}
// Set up and manipulate keywords
/*-----------------------------------------------------------------------*/
// should we apply word stemming?
$do_stemming = $config->{'stem-words'} == 'yes' ? TRUE : FALSE;
// replace synonyms
$keywords = SearchIndex::applySynonyms($param_keywords);
$keywords_boolean = SearchIndex::parseKeywordString($keywords, $do_stemming);
$keywords_highlight = trim(implode(' ', $keywords_boolean['highlight']), '"');
// Set up weighting
/*-----------------------------------------------------------------------*/
$sql_weighting = '';
foreach (SearchIndex::getIndexes() as $section_id => $index) {
$weight = isset($index['weighting']) ? $index['weighting'] : 2;
switch ($weight) {
case 0:
$weight = 4;
break;
// highest
// highest
case 1:
$weight = 2;
break;
// high
// high
case 2:
$weight = 1;
break;
// none
// none
case 3:
$weight = 0.5;
break;
// low
// low
case 4:
$weight = 0.25;
break;
// lowest
}
$sql_weighting .= sprintf("WHEN e.section_id = %d THEN %d \n", $section_id, $weight);
}
// Build search SQL
/*-----------------------------------------------------------------------*/
$mode = !is_null($config->{'mode'}) ? $config->{'mode'} : 'like';
$mode = strtoupper($mode);
switch ($mode) {
case 'FULLTEXT':
$sql = sprintf("SELECT\n\t\t\t\t\t\t\tSQL_CALC_FOUND_ROWS\n\t\t\t\t\t\t\te.id as `entry_id`,\n\t\t\t\t\t\t\tdata,\n\t\t\t\t\t\t\te.section_id as `section_id`,\n\t\t\t\t\t\t\tUNIX_TIMESTAMP(e.creation_date) AS `creation_date`,\n\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\tMATCH(index.data) AGAINST ('%1\$s') *\n\t\t\t\t\t\t\t\tCASE\n\t\t\t\t\t\t\t\t\t%2\$s\n\t\t\t\t\t\t\t\t\tELSE 1\n\t\t\t\t\t\t\t\tEND\n\t\t\t\t\t\t\t\t%3\$s\n\t\t\t\t\t\t\t) AS `score`\n\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\ttbl_search_index as `index`\n\t\t\t\t\t\t\tJOIN tbl_entries as `e` ON (index.entry_id = e.id)\n\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\tMATCH(index.data) AGAINST ('%4\$s' IN BOOLEAN MODE)\n\t\t\t\t\t\t\tAND e.section_id IN ('%5\$s')\n\t\t\t\t\t\tORDER BY\n\t\t\t\t\t\t\t%6\$s\n\t\t\t\t\t\tLIMIT %7\$d, %8\$d", Symphony::Database()->cleanValue($keywords), $sql_weighting, $param_sort == 'score-recency' ? '/ SQRT(GREATEST(1, DATEDIFF(NOW(), creation_date)))' : '', Symphony::Database()->cleanValue($keywords), implode("','", array_keys($sections)), Symphony::Database()->cleanValue($sql_order_by), max(0, ($this->dsParamSTARTPAGE - 1) * $this->dsParamLIMIT), (int) $this->dsParamLIMIT);
break;
case 'LIKE':
case 'REGEXP':
$sql_locate = '';
$sql_replace = '';
$sql_where = '';
// by default, no wildcard separators
$prefix = '';
$suffix = '';
// append wildcard for LIKE
if ($mode == 'LIKE') {
$prefix = $suffix = '%';
}
// apply word boundary separator
if ($mode == 'REGEXP') {
$prefix = '[[:<:]]';
$suffix = '[[:>:]]';
}
// all words to include in the query (single words and phrases)
foreach ($keywords_boolean['include-words-all'] as $keyword) {
$keyword_stem = NULL;
$keyword = Symphony::Database()->cleanValue($keyword);
if ($do_stemming) {
$keyword_stem = Symphony::Database()->cleanValue(PorterStemmer::Stem($keyword));
}
// if the word can be stemmed, look for the word or the stem version
if ($do_stemming && $keyword_stem != $keyword) {
$sql_where .= "(index.data {$mode} '{$prefix}{$keyword}{$suffix}' OR index.data {$mode} '{$prefix}{$keyword}{$suffix}') AND ";
} else {
$sql_where .= "index.data {$mode} '{$prefix}{$keyword}{$suffix}' AND ";
}
// if this keyword exists in the entry contents, add 1 to "keywords_matched"
// which represents number of unique keywords in the search string that are found
$sql_locate .= "IF(LOCATE('{$keyword}', LOWER(`data`)) > 0, 1, 0) + ";
// see how many times this word is found in the entry contents by removing it from
// the column text then compare length to see how many times it was removed
$sql_replace .= "(LENGTH(`data`) - LENGTH(REPLACE(LOWER(`data`),LOWER('{$keyword}'),''))) / LENGTH('{$keyword}') + ";
}
// all words or phrases that we do not want
foreach ($keywords_boolean['exclude-words-all'] as $keyword) {
$keyword = Symphony::Database()->cleanValue($keyword);
$sql_where .= "index.data NOT {$mode} '{$prefix}{$keyword}{$suffix}' AND ";
}
// append to complete SQL
$sql_locate = $sql_locate == '' ? $sql_locate = '1' : ($sql_locate .= '0');
$sql_replace = $sql_replace == '' ? $sql_replace = '1' : ($sql_replace .= '0');
$sql_where = $sql_where == '' ? $sql_where = 'NOT 1' : $sql_where;
// trim unnecessary boolean conditions from SQL
$sql_where = preg_replace("/ OR \$/", "", $sql_where);
$sql_where = preg_replace("/ AND \$/", "", $sql_where);
// if ordering by score, use a function of the two columns
// we are calculating rather than just "score"
if (preg_match("/^score/", $sql_order_by)) {
$sql_order_by = preg_replace("/^score/", "(keywords_matched * score)", $sql_order_by);
}
$sql = sprintf("SELECT\n\t\t\t\t\t\t\tSQL_CALC_FOUND_ROWS\n\t\t\t\t\t\t\te.id as `entry_id`,\n\t\t\t\t\t\t\tdata,\n\t\t\t\t\t\t\te.section_id as `section_id`,\n\t\t\t\t\t\t\tUNIX_TIMESTAMP(e.creation_date) AS `creation_date`,\n\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t%1\$s\n\t\t\t\t\t\t\t) AS keywords_matched,\n\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t(%2\$s)\n\t\t\t\t\t\t\t\t*\n\t\t\t\t\t\t\t\tCASE\n\t\t\t\t\t\t\t\t\t%3\$s\n\t\t\t\t\t\t\t\t\tELSE 1\n\t\t\t\t\t\t\t\tEND\n\t\t\t\t\t\t\t\t%4\$s\n\t\t\t\t\t\t\t) AS score\n\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\ttbl_search_index as `index`\n\t\t\t\t\t\t\tJOIN tbl_entries as `e` ON (index.entry_id = e.id)\n\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t%5\$s\n\t\t\t\t\t\t\tAND e.section_id IN ('%6\$s')\n\t\t\t\t\t\tORDER BY\n\t\t\t\t\t\t\t%7\$s\n\t\t\t\t\t\tLIMIT\n\t\t\t\t\t\t\t%8\$d, %9\$d", $sql_locate, $sql_replace, $sql_weighting, $param_sort == 'score-recency' ? '/ SQRT(GREATEST(1, DATEDIFF(NOW(), creation_date)))' : '', $sql_where, implode("','", array_keys($sections)), Symphony::Database()->cleanValue($sql_order_by), max(0, ($this->dsParamSTARTPAGE - 1) * $this->dsParamLIMIT), (int) $this->dsParamLIMIT);
//echo $sql;die;
break;
}
// Add soundalikes ("did you mean?") to XML
/*-----------------------------------------------------------------------*/
// we have search words, check for soundalikes
if (count($keywords_boolean['include-words-all']) > 0) {
$sounds_like = array();
foreach ($keywords_boolean['include-words-all'] as $word) {
$soundalikes = Symphony::Database()->fetchCol('keyword', sprintf("SELECT keyword FROM tbl_search_index_keywords WHERE SOUNDEX(keyword) = SOUNDEX('%s')", Symphony::Database()->cleanValue($word)));
foreach ($soundalikes as $i => &$soundalike) {
if ($soundalike == $word) {
unset($soundalikes[$i]);
continue;
}
$soundalike = array('word' => $soundalike, 'distance' => levenshtein($soundalike, $word));
}
usort($soundalikes, array('datasourcesearch', 'sortWordDistance'));
$sounds_like[$word] = $soundalikes[0]['word'];
}
// add words to XML
if (count($sounds_like) > 0) {
$alternative_spelling = new XMLElement('alternative-keywords');
foreach ($sounds_like as $word => $soundalike) {
$alternative_spelling->appendChild(new XMLElement('keyword', NULL, array('original' => $word, 'alternative' => $soundalike, 'distance' => levenshtein($soundalike, $word))));
}
$result->appendChild($alternative_spelling);
}
}
// Run search SQL!
/*-----------------------------------------------------------------------*/
// get our entries, returns entry IDs
$entries = Symphony::Database()->fetch($sql);
$total_entries = Symphony::Database()->fetchVar('total', 0, 'SELECT FOUND_ROWS() AS `total`');
// append input values
$result->setAttributeArray(array('keywords' => General::sanitize($keywords), 'sort' => General::sanitize($param_sort), 'direction' => General::sanitize($param_direction)));
// append pagination
$result->appendChild(General::buildPaginationElement($total_entries, ceil($total_entries * (1 / $this->dsParamLIMIT)), $this->dsParamLIMIT, $this->dsParamSTARTPAGE));
// append list of sections
$sections_xml = new XMLElement('sections');
foreach ($sections as $id => $section) {
$sections_xml->appendChild(new XMLElement('section', General::sanitize($section['name']), array('id' => $id, 'handle' => $section['handle'])));
}
$result->appendChild($sections_xml);
// Append entries to XML, build if desired
/*-----------------------------------------------------------------------*/
// if true then the entire entry will be appended to the XML. If not, only
// a "stub" of the entry ID is provided, allowing other data sources to
// supplement with the necessary fields
$build_entries = $config->{'build-entries'} == 'yes' ? TRUE : FALSE;
if ($build_entries) {
$field_pool = array();
}
// container for entry ID output parameter
$param_output = array();
foreach ($entries as $entry) {
$param_output[] = $entry['entry_id'];
$entry_xml = new XMLElement('entry', NULL, array('id' => $entry['entry_id'], 'section' => $sections[$entry['section_id']]['handle']));
// add excerpt with highlighted search terms
$excerpt = SearchIndex::parseExcerpt($keywords_highlight, $entry['data']);
$excerpt = $this->fixEncoding($excerpt);
$entry_xml->appendChild(new XMLElement('excerpt', $excerpt));
// build and append entry data
if ($build_entries) {
$e = reset(EntryManager::fetch($entry['entry_id']));
$data = $e->getData();
foreach ($data as $field_id => $values) {
if (!isset($field_pool[$field_id]) || !is_object($field_pool[$field_id])) {
$field_pool[$field_id] = FieldManager::fetch($field_id);
}
$field_pool[$field_id]->appendFormattedElement($entry_xml, $values, FALSE, !empty($values['value_formatted']) ? 'formatted' : null, $e->get('id'));
}
}
$result->appendChild($entry_xml);
}
// send entry IDs as Output Parameterss
$param_pool['ds-' . $this->dsParamROOTELEMENT . '.id'] = $param_output;
$param_pool['ds-' . $this->dsParamROOTELEMENT] = $param_output;
// Log query
/*-----------------------------------------------------------------------*/
if ($config->{'log-keywords'} == 'yes' && trim($keywords)) {
$section_handles = array_map('reset', array_values($sections));
// has this search (keywords+sections) already been logged this session?
$already_logged = Symphony::Database()->fetch(sprintf("SELECT * FROM `tbl_search_index_logs` WHERE keywords='%s' AND sections='%s' AND session_id='%s'", Symphony::Database()->cleanValue($param_keywords), Symphony::Database()->cleanValue(implode(',', $section_handles)), session_id()));
$log_sql = sprintf("INSERT INTO `tbl_search_index_logs`\n\t\t\t\t\t(date, keywords, keywords_manipulated, sections, page, results, session_id)\n\t\t\t\t\tVALUES('%s', '%s', '%s', '%s', %d, %d, '%s')", date('Y-m-d H:i:s', time()), Symphony::Database()->cleanValue($param_keywords), Symphony::Database()->cleanValue($keywords), Symphony::Database()->cleanValue(implode(',', $section_handles)), $this->dsParamSTARTPAGE, $total_entries, session_id());
Symphony::Database()->query($log_sql);
}
return $result;
}
$result = $this->emptyXMLSet();
$result->prependChild($sectioninfo);
if ($include_pagination_element) {
$pagination_element = General::buildPaginationElement();
if ($pagination_element instanceof XMLElement && $result instanceof XMLElement) {
$result->prependChild($pagination_element);
}
}
$param_pool[$key][] = '';
} else {
if (!$this->_param_output_only) {
$result = new XMLElement($this->dsParamROOTELEMENT);
$result->appendChild($sectioninfo);
if ($include_pagination_element) {
$t = $this->dsParamLIMIT >= 0 ? $this->dsParamLIMIT : $entries['total-entries'];
$pagination_element = General::buildPaginationElement($entries['total-entries'], $entries['total-pages'], $t, $this->dsParamSTARTPAGE);
if ($pagination_element instanceof XMLElement && $result instanceof XMLElement) {
$result->prependChild($pagination_element);
}
}
}
if (isset($this->dsParamPARAMOUTPUT) && !is_array($param_pool[$key])) {
$param_pool[$key] = array();
}
if ($this->dsParamLIMIT > 0) {
if (isset($this->dsParamGROUP)) {
$fieldPool[$this->dsParamGROUP] =& $entryManager->fieldManager->fetch($this->dsParamGROUP);
$groups = $fieldPool[$this->dsParamGROUP]->groupRecords($entries['records']);
foreach ($groups as $element => $group) {
foreach ($group as $g) {
processRecordGroup($result, $element, $g, $this, $this->_Parent, $entryManager, $fieldPool, $param_pool, $this->_param_output_only);
public function render(Register $ParameterOutput, $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)) {
foreach ($this->parameters()->conditions as $condition) {
if (preg_match('/:/', $condition['parameter'])) {
$c = Datasource::replaceParametersInString($condition['parameter'], $ParameterOutput);
} else {
$c = Datasource::resolveParameter($condition['parameter'], $ParameterOutput);
}
// 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) {
} catch (Exception $e) {
}
$pagination = (object) array('total-entries' => NULL, 'entries-per-page' => max(1, (int) self::replaceParametersInString($this->parameters()->limit, $ParameterOutput)), 'total-pages' => NULL, 'current-page' => max(1, (int) self::replaceParametersInString($this->parameters()->page, $ParameterOutput)));
$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';
// 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'], $ParameterOutput);
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, $ParameterOutput);
}
}
}
}
// Escape percent symbold:
$where = array_map(create_function('$string', 'return str_replace(\'%\', \'%%\', $string);'), $where);
$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', $joins, $section->handle, is_array($where) && !empty($where) ? 'AND (' . implode($filter_operation_type == self::FILTER_AND ? ' AND ' : ' OR ', $where) . ')' : NULL, $order, $pagination->{'record-start'}, $pagination->{'entries-per-page'});
try {
$entries = Symphony::Database()->query($query, array($section->handle, $section->{'publish-order-handle'}), 'EntryResult');
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);
}
$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;
$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'})) {
$parameter_output = (object) array('system' => array(), 'fields' => array());
foreach ($this->parameters()->{'parameter-output'} as $element) {
if (preg_match('/^system:/i', $element)) {
$parameter_output->system[preg_replace('/^system:/i', NULL, $element)] = array();
} else {
$schema[] = $element;
$parameter_output->fields[$element] = array();
}
}
}
$entries->setSchema($schema);
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::fromGMT($e->creation_date), 'creation-date'));
break;
case 'modification-date':
$entry->appendChild(General::createXMLDateObject($result, DateTimeObj::fromGMT($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 ($parameter_output->system as $field => $existing_values) {
switch ($field) {
case 'id':
$parameter_output->system[$field][] = $e->id;
break;
case 'creation-date':
$parameter_output->system[$field][] = DateTimeObj::get('Y-m-d H:i:s', DateTimeObj::fromGMT($e->creation_date));
break;
case 'modification-date':
$parameter_output->system[$field][] = DateTimeObj::get('Y-m-d H:i:s', DateTimeObj::fromGMT($e->modification_date));
break;
case 'user':
$parameter_output->system[$field][] = $e->user_id;
break;
}
}
foreach ($parameter_output->fields as $field => $existing_values) {
if (!isset($e->data()->{$field}) or is_null($e->data()->{$field})) {
continue;
}
$o = $section->fetchFieldByHandle($field)->getParameterOutputValue($e->data()->{$field}, $e);
if (is_array($o)) {
$parameter_output->fields[$field] = array_merge($o, $parameter_output->fields[$field]);
} else {
$parameter_output->fields[$field][] = $o;
}
}
}
}
// Add in the param output values to the ParameterOutput object
if (is_array($this->parameters()->{'parameter-output'}) && !empty($this->parameters()->{'parameter-output'})) {
foreach ($parameter_output->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)) {
$ParameterOutput->{$key} = array_unique($values);
}
}
foreach ($parameter_output->fields as $field => $values) {
$key = sprintf('ds-%s.%s', $this->parameters()->{'root-element'}, $field);
$values = array_filter($values);
if (is_array($values) && !empty($values)) {
$ParameterOutput->{$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;
}
public function grab(&$param_pool = NULL)
{
$config = (object) Symphony::Configuration()->get('elasticsearch');
// build an object of runtime parameters
$params = (object) array('keywords' => isset($_GET['keywords']) ? $_GET['keywords'] : '', 'current-page' => isset($_GET['page']) && is_numeric($_GET['page']) ? (int) $_GET['page'] : 1, 'per-page' => isset($_GET['per-page']) && is_numeric($_GET['per-page']) ? (int) $_GET['per-page'] : $config->{'per-page'}, 'sort' => isset($_GET['sort']) ? $_GET['sort'] : $config->sort, 'direction' => isset($_GET['direction']) && in_array($_GET['direction'], array('asc', 'desc')) ? $_GET['direction'] : $config->direction, 'sections' => isset($_GET['sections']) && !empty($_GET['sections']) ? array_map('trim', explode(',', $_GET['sections'])) : NULL, 'default-sections' => !empty($config->{'default-sections'}) ? explode(',', $config->{'default-sections'}) : NULL, 'language' => isset($_GET['language']) && !empty($_GET['language']) ? array_map('trim', explode(',', $_GET['language'])) : NULL, 'default-language' => !empty($config->{'default-language'}) ? explode(',', $config->{'default-language'}) : NULL);
$params->{'keywords-raw'} = $params->keywords;
$params->keywords = ElasticSearch::filterKeywords($params->keywords);
// don't run search if not searching for anything
if (empty($params->keywords)) {
return;
}
// check valid page number
if ($params->{'current-page'} < 1) {
$params->{'current-page'} = 1;
}
// if no language passed but there are defaults, use the defaults
if ($params->{'language'} === NULL && count($params->{'default-language'})) {
$params->{'language'} = $params->{'default-language'};
}
// include this extension's own library
ElasticSearch::init();
// a query_string search type in ES accepts common (Lucene) search syntax such as
// prefixing terms with +/- and surrounding exact phrases with quotes
$query_querystring = new Elastica_Query_QueryString();
// all terms are required
$query_querystring->setDefaultOperator('AND');
// pass in keywords
$query_querystring->setQueryString($params->keywords);
// only apply the search to fields mapped as multi-type with a sub-type named "symphony_fulltext"
// this allows us to exclude fields from this generic full-site search but search them elsewhere
if ($params->{'language'}) {
$fields = array();
foreach ($params->{'language'} as $language) {
$fields[] = '*_' . $language . '.symphony_fulltext';
}
$query_querystring->setFields($fields);
} else {
$query_querystring->setFields(array('*.symphony_fulltext'));
}
// create the parent query object (a factory) into which the query_string is passed
$query = new Elastica_Query($query_querystring);
$query->setLimit($params->{'per-page'});
// TODO: check this. should it be + 1?
$query->setFrom($params->{'per-page'} * ($params->{'current-page'} - 1));
$query->setSort(array($params->{'sort'} => $params->{'direction'}));
// build a search object, this wraps an Elastica_Client and handles requests to and from the ElasticSearch server
$search = new Elastica_Search(ElasticSearch::getClient());
// search on our site index only (in case the server is running multiple indexes)
$search->addIndex(ElasticSearch::getIndex());
// create a new facet on the entry _type (section handle). this will return a list
// of sections in which the matching entries reside, and a count of matches in each
$facet = new Elastica_Facet_Terms('filtered-sections');
$facet->setField('_type');
$query->addFacet($facet);
// we also want a list of _all_ sections and their total entry counts. facets run within the context
// of the query they are attached to, so we want a new query that searches within the specified sections
// but doesn't search on the keywords (so it finds everything). ES supports this with a match_all query
// which Elastica creates by default when you create a plain query object
$query_all = new Elastica_Query();
$facet = new Elastica_Facet_Terms('all-sections');
$facet->setField('_type');
$query_all->addFacet($facet);
// build an array of all valid section handles that have mappings
$all_mapped_sections = array();
$section_full_names = array();
foreach (ElasticSearch::getAllTypes() as $type) {
// if using default config sections, check that the type exists in the default
if (count($params->{'default-sections'}) > 0 && !in_array($type->section->get('handle'), $params->{'default-sections'})) {
continue;
}
$all_mapped_sections[] = $type->section->get('handle');
// cache an array of section names indexed by their handles, quick lookup later
$section_full_names[$type->section->get('handle')] = $type->section->get('name');
}
$sections = array();
// no specified sections were sent in the params, so default to all available sections
if ($params->sections === NULL) {
$sections = $all_mapped_sections;
} else {
foreach ($params->sections as $handle) {
if (!in_array($handle, $all_mapped_sections)) {
continue;
}
$sections[] = $handle;
}
}
// a filter is an additional set of filtering that can be added to a query. filters are run
// after the query has executed, so run over the resultset and remove documents that don't
// match the criteria. they are fast and are cached by ES. we want to restrict the search
// results to within the specified sections only, so we add a filter on the _type (section handle)
// field. the filter is of type "terms" (an array of exact-match strings)
$filter = new Elastica_Filter_Terms('_type');
// build an array of field handles which should be highlighted in search results, used for building
// the excerpt on results pages. a field is marked as highlightable by giving it a "symphony_fulltext"
// field in the section mappings
$highlights = array();
// iterate over each valid section, adding it as a filter and finding any highlighted fields within
foreach ($sections as $section) {
// add these sections to the entry search
$filter->addTerm($section);
// read the section's mapping JSON from disk
$mapping = json_decode(ElasticSearch::getTypeByHandle($section)->mapping_json, FALSE);
// find fields that have symphony_highlight
foreach ($mapping->{$section}->properties as $field => $properties) {
if (!$properties->fields->symphony_fulltext) {
continue;
}
$highlights[] = array($field => (object) array());
}
}
// add the section filter to both queries (keyword search and the all entries facet search)
$query->setFilter($filter);
$query_all->setFilter($filter);
// configure highlighting for the keyword search
$query->setHighlight(array('fields' => $highlights, 'encoder' => 'html', 'fragment_size' => $config->{'highlight-fragment-size'}, 'number_of_fragments' => $config->{'highlight-per-field'}, 'pre_tags' => array('<strong class="highlight">'), 'post_tags' => array('</strong>')));
// run both queries!
$query_result = $search->search($query);
$query_all_result = $search->search($query_all);
// build root XMK element
$xml = new XMLElement($this->dsParamROOTELEMENT, NULL, array('took' => $query_result->getResponse()->getEngineTime() . 'ms', 'max-score' => round($query_result->getMaxScore(), 4)));
// append keywords to the XML
$xml_keywords = new XMLElement('keywords');
$xml_keywords->appendChild(new XMLElement('raw', General::sanitize($params->{'keywords-raw'})));
$xml_keywords->appendChild(new XMLElement('filtered', General::sanitize($params->{'keywords'})));
$xml->appendChild($xml_keywords);
// build pagination
$xml->appendChild(General::buildPaginationElement($query_result->getTotalHits(), ceil($query_result->getTotalHits() * (1 / $params->{'per-page'})), $params->{'per-page'}, $params->{'current-page'}));
// build facets
$xml_facets = new XMLElement('facets');
// merge the facets from both queries so they appear as one
$facets = array_merge($query_result->getFacets(), $query_all_result->getFacets());
foreach ($facets as $handle => $facet) {
$xml_facet = new XMLElement('facet', NULL, array('handle' => $handle));
foreach ($facet['terms'] as $term) {
// only show sections that are in default config, if it is being used
if (!in_array($term['term'], $all_mapped_sections)) {
continue;
}
$xml_facet_term = new XMLElement('term', $section_full_names[$term['term']], array('handle' => $term['term'], 'entries' => $term['count'], 'active' => in_array($term['term'], $sections) ? 'yes' : 'no'));
$xml_facet->appendChild($xml_facet_term);
}
$xml_facets->appendChild($xml_facet);
}
$xml->appendChild($xml_facets);
// if each entry is to have its full XML built and appended to the result,
// create a new EntryManager for using later on
if ($config->{'build-entry-xml'} === 'yes') {
$em = new EntryManager(Frontend::instance());
$field_pool = array();
}
// append entries
$xml_entries = new XMLElement('entries');
foreach ($query_result->getResults() as $data) {
$entry = new XMLElement('entry', NULL, array('id' => $data->getId(), 'section' => $data->getType(), 'score' => is_array($data->getScore()) ? reset($data->getScore()) : round($data->getScore(), 4)));
// append field highlights
foreach ($data->getHighlights() as $field => $highlight) {
foreach ($highlight as $html) {
$entry->appendChild(new XMLElement('highlight', $html, array('field' => $field)));
}
}
// build and append entry data
// this was pinched from Symphony's datasource class
if ($config->{'build-entry-xml'} === 'yes') {
$e = reset($em->fetch($data->getId()));
$field_data = $e->getData();
foreach ($field_data as $field_id => $values) {
if (!isset($field_pool[$field_id]) || !is_object($field_pool[$field_id])) {
$field_pool[$field_id] = FieldManager::fetch($field_id);
}
$field_pool[$field_id]->appendFormattedElement($entry, $values, FALSE, NULL, $e->get('id'));
}
}
$xml_entries->appendChild($entry);
// put each entry ID into the param pool for chaining
$param_pool['ds-elasticsearch'][] = $data->getId();
}
$xml->appendChild($xml_entries);
// log query if logging is enabled
if ($config->{'log-searches'} === 'yes') {
ElasticSearchLogs::save($params->keywords, $params->{'keywords-raw'}, $sections, $params->{'current-page'}, $query_result->getTotalHits());
}
return $xml;
}
public function grab(&$param_pool)
{
$result = new XMLElement($this->dsParamROOTELEMENT);
$param_output = array();
$get = $_GET;
// look for key in GET array if it's specified
if (Symphony::Configuration()->get('get-param-prefix', 'search_index') != '') {
if (Symphony::Configuration()->get('get-param-prefix', 'search_index') == 'param_pool') {
$get = $this->_env['param'];
} else {
$get = $get[Symphony::Configuration()->get('get-param-prefix', 'search_index')];
}
}
$param_keywords = Symphony::Configuration()->get('get-param-keywords', 'search_index');
$param_per_page = Symphony::Configuration()->get('get-param-per-page', 'search_index');
$param_sort = Symphony::Configuration()->get('get-param-sort', 'search_index');
$param_direction = Symphony::Configuration()->get('get-param-direction', 'search_index');
$param_sections = Symphony::Configuration()->get('get-param-sections', 'search_index');
$param_page = Symphony::Configuration()->get('get-param-page', 'search_index');
$keywords = $get[$param_keywords];
$this->dsParamLIMIT = isset($get[$param_per_page]) && (int) $get[$param_per_page] > 0 ? (int) $get[$param_per_page] : $this->dsParamLIMIT;
$sort = isset($get[$param_sort]) ? $get[$param_sort] : 'score';
$direction = isset($get[$param_direction]) ? strtolower($get[$param_direction]) : 'desc';
$sections = isset($get[$param_sections]) ? $get[$param_sections] : NULL;
if ($sections == NULL && Symphony::Configuration()->get('default-sections', 'search_index') != '') {
$sections = Symphony::Configuration()->get('default-sections', 'search_index');
}
$this->dsParamSTARTPAGE = isset($get[$param_page]) ? (int) $get[$param_page] : $this->dsParamSTARTPAGE;
if (is_null($sections)) {
return $this->errorXML('Invalid search sections');
} else {
$section_handles = explode(',', $sections);
$sections = array();
foreach ($section_handles as $handle) {
$section = Symphony::Database()->fetchRow(0, sprintf("SELECT `id`, `name` FROM `tbl_sections` WHERE handle = '%s' LIMIT 1", Symphony::Database()->cleanValue($handle)));
if ($section) {
$sections[$section['id']] = array('handle' => $handle, 'name' => $section['name']);
}
}
if (count($sections) == 0) {
return $this->errorXML('Invalid search sections');
}
}
if ($sort == 'date') {
$order_by = "e.creation_date {$direction}";
} else {
if ($sort == 'id') {
$order_by = "e.id {$direction}";
} else {
$order_by = "score {$direction}";
}
}
$weighting = '';
$indexed_sections = SearchIndex::getIndexes();
foreach ($indexed_sections as $section_id => $index) {
$weight = is_null($index['weighting']) ? 2 : $index['weighting'];
switch ($weight) {
case 0:
$weight = 4;
break;
// highest
// highest
case 1:
$weight = 2;
break;
// high
// high
case 2:
$weight = 1;
break;
// none
// none
case 3:
$weight = 0.5;
break;
// low
// low
case 4:
$weight = 0.25;
break;
// lowest
}
$weighting .= sprintf("WHEN e.section_id = %d THEN %d \n", $section_id, $weight);
}
/* MULTILANGUAGE SUPPORT: */
require_once TOOLKIT . '/class.extensionmanager.php';
$extensionManager = new ExtensionManager($this);
$status = $extensionManager->fetchStatus('multilanguage');
$languageSuffix = '';
if ($status == EXTENSION_ENABLED) {
$languages = explode(',', file_get_contents(MANIFEST . '/multilanguage-languages'));
if (count($languages) > 0) {
$code = isset($_GET['language-code']) ? strtolower($_GET['language-code']) : $languages[0];
// Override: if the parameter &lang is also in the URL, search that language instead:
$code = isset($_GET['lang']) ? strtolower($_GET['lang']) : $code;
$languageSuffix = '_' . $code;
// SQL injection prevention:
if (in_array($code, $languages)) {
$languageSuffix = '_' . $code;
}
}
}
$sql = sprintf("SELECT \n\t\t\t\t\tSQL_CALC_FOUND_ROWS \n\t\t\t\t\te.id as `entry_id`,\n\t\t\t\t\tdata" . $languageSuffix . ",\n\t\t\t\t\te.section_id as `section_id`,\n\t\t\t\t\tUNIX_TIMESTAMP(e.creation_date) AS `creation_date`,\n\t\t\t\t\t(\n\t\t\t\t\t\tMATCH(index.data" . $languageSuffix . ") AGAINST ('%1\$s') * \n\t\t\t\t\t\tCASE\n\t\t\t\t\t\t\t%2\$s\n\t\t\t\t\t\t\tELSE 1\n\t\t\t\t\t\tEND\n\t\t\t\t\t\t%3\$s\t\t\t\t\t\t\n\t\t\t\t\t) AS `score`\n\t\t\t\tFROM\n\t\t\t\t\ttbl_search_index as `index`\n\t\t\t\t\tJOIN tbl_entries as `e` ON (index.entry_id = e.id)\n\t\t\t\tWHERE\n\t\t\t\t\tMATCH(index.data" . $languageSuffix . ") AGAINST ('%4\$s' IN BOOLEAN MODE)\n\t\t\t\t\tAND e.section_id IN ('%5\$s')\n\t\t\t\tORDER BY\n\t\t\t\t\t%6\$s\n\t\t\t\tLIMIT %7\$d, %8\$d", Symphony::Database()->cleanValue($keywords), $weighting, $sort == 'score-recency' ? '/ SQRT(GREATEST(1, DATEDIFF(NOW(), creation_date)))' : '', Symphony::Database()->cleanValue(SearchIndex::manipulateKeywords($keywords)), implode("','", array_keys($sections)), Symphony::Database()->cleanValue($order_by), max(0, ($this->dsParamSTARTPAGE - 1) * $this->dsParamLIMIT), (int) $this->dsParamLIMIT);
/* END MULTILANGUAGE SUPPORT */
//echo $sql;die;
$result->setAttributeArray(array('keywords' => General::sanitize($keywords), 'sort' => $sort, 'direction' => $direction));
// get our entries!
$entries = Symphony::Database()->fetch($sql);
$total_entries = Symphony::Database()->fetchVar('total', 0, 'SELECT FOUND_ROWS() AS `total`');
$result->appendChild(General::buildPaginationElement($total_entries, ceil($total_entries * (1 / $this->dsParamLIMIT)), $this->dsParamLIMIT, $this->dsParamSTARTPAGE));
$sections_xml = new XMLElement('sections');
foreach ($sections as $id => $section) {
$sections_xml->appendChild(new XMLElement('section', General::sanitize($section['name']), array('id' => $id, 'handle' => $section['handle'])));
}
$result->appendChild($sections_xml);
foreach ($entries as $entry) {
$param_output[] = $entry['entry_id'];
$result->appendChild(new XMLElement('entry', General::sanitize(SearchIndex::parseExcerpt($keywords, $entry['data'])), array('id' => $entry['entry_id'], 'section' => $sections[$entry['section_id']]['handle'], 'score' => round($entry['score'], 3))));
}
// send entry IDs as Output Parameterss
$param_pool['ds-' . $this->dsParamROOTELEMENT] = $param_output;
$log_sql = sprintf("INSERT INTO `tbl_search_index_logs`\n\t\t\t\t(date, keywords, sections, page, results, session_id)\n\t\t\t\tVALUES('%s', '%s', '%s', %d, %d, '%s')", date('Y-m-d H:i:s', time()), Symphony::Database()->cleanValue($keywords), Symphony::Database()->cleanValue(implode(',', $section_handles)), $this->dsParamSTARTPAGE, $total_entries, session_id());
if ($this->log === TRUE) {
Symphony::Database()->query($log_sql);
}
return $result;
}
public function grab(&$param_pool)
{
$result = new XMLElement($this->dsParamROOTELEMENT);
$param_output = array();
$get = $_GET;
// look for key in GET array if it's specified
if (Symphony::Configuration()->get('get-param-prefix', 'search_index') != '') {
$get = $get[Symphony::Configuration()->get('get-param-prefix', 'search_index')];
}
$param_keywords = Symphony::Configuration()->get('get-param-keywords', 'search_index');
$param_per_page = Symphony::Configuration()->get('get-param-per-page', 'search_index');
$param_sort = Symphony::Configuration()->get('get-param-sort', 'search_index');
$param_direction = Symphony::Configuration()->get('get-param-direction', 'search_index');
$param_sections = Symphony::Configuration()->get('get-param-sections', 'search_index');
$param_page = Symphony::Configuration()->get('get-param-page', 'search_index');
$keywords = $get[$param_keywords];
$this->dsParamLIMIT = isset($get[$param_per_page]) && (int) $get[$param_per_page] > 0 ? (int) $get[$param_per_page] : $this->dsParamLIMIT;
$sort = isset($get[$param_sort]) ? $get[$param_sort] : 'score';
$direction = isset($get[$param_direction]) ? strtolower($get[$param_direction]) : 'desc';
$sections = isset($get[$param_sections]) ? $get[$param_sections] : null;
$language = isset($_REQUEST['language']) ? $_REQUEST['language'] : '%';
$this->dsParamSTARTPAGE = isset($get[$param_page]) ? (int) $get[$param_page] : $this->dsParamSTARTPAGE;
if (is_null($sections)) {
return $this->errorXML('Invalid search sections');
} else {
$section_handles = explode(',', $sections);
$sections = array();
foreach ($section_handles as $handle) {
$section = Symphony::Database()->fetchRow(0, sprintf("SELECT `id`, `name` FROM `tbl_sections` WHERE handle = '%s' LIMIT 1", Symphony::Database()->cleanValue($handle)));
if ($section) {
$sections[$section['id']] = array('handle' => $handle, 'name' => $section['name']);
}
}
if (count($sections) == 0) {
return $this->errorXML('Invalid search sections');
}
}
if ($sort == 'date') {
$order_by = "e.creation_date {$direction}";
} else {
if ($sort == 'id') {
$order_by = "e.id {$direction}";
} else {
$order_by = "score {$direction}";
}
}
$sql = sprintf("SELECT\n\t\t\t\t\tSQL_CALC_FOUND_ROWS \n\t\t\t\t\tMATCH(index.data) AGAINST ('%1\$s' IN BOOLEAN MODE) AS `score`,\n\t\t\t\t\te.id as `entry_id`,\n\t\t\t\t\te.section_id as `section_id`,\n\t\t\t\t\tUNIX_TIMESTAMP(e.creation_date) AS `creation_date`\n\t\t\t\tFROM\n\t\t\t\t\tsym_search_index as `index`\n\t\t\t\t\tJOIN sym_entries as `e` ON (index.entry_id = e.id)\n\t\t\t\tWHERE\n\t\t\t\t\tMATCH(index.data) AGAINST ('%1\$s' IN BOOLEAN MODE)\n\t\t\t\t\tAND e.section_id IN ('%2\$s')\n\t\t\t\t\tAND (index.language LIKE '%6\$s' OR index.language IS NULL)\n\t\t\t\tORDER BY\n\t\t\t\t\t%3\$s\n\t\t\t\tLIMIT %4\$d, %5\$d", Symphony::Database()->cleanValue(SearchIndex::wildcardSearchKeywords($keywords)), implode("','", array_keys($sections)), Symphony::Database()->cleanValue($order_by), max(0, ($this->dsParamSTARTPAGE - 1) * $this->dsParamLIMIT), (int) $this->dsParamLIMIT, $language);
$result->setAttributeArray(array('keywords' => General::sanitize($keywords), 'sort' => $sort, 'direction' => $direction));
// get our entries!
$entries = Symphony::Database()->fetch($sql);
$total_entries = Symphony::Database()->fetchVar('total', 0, 'SELECT FOUND_ROWS() AS `total`');
$result->appendChild(General::buildPaginationElement($total_entries, ceil($total_entries * (1 / $this->dsParamLIMIT)), $this->dsParamLIMIT, $this->dsParamSTARTPAGE));
$sections_xml = new XMLElement('sections');
foreach ($sections as $id => $section) {
$sections_xml->appendChild(new XMLElement('section', General::sanitize($section['name']), array('id' => $id, 'handle' => $section['handle'])));
}
$result->appendChild($sections_xml);
//$highest_score = null;
foreach ($entries as $entry) {
//if (is_null($highest_score)) $highest_score = $entry['score'];
$param_output[] = $entry['entry_id'];
$result->appendChild(new XMLElement('entry', null, array('id' => $entry['entry_id'], 'section' => $sections[$entry['section_id']]['handle'])));
}
// send entry IDs as Output Parameterss
$param_pool['ds-' . $this->dsParamROOTELEMENT] = implode(', ', $param_output);
return $result;
}
