/**
* Build SQL for Data Source filter
*
* @param array $data
* @param string $joins
* @param string $where
* @param boolean $andOperation
*/
function buildDSRetrivalSQL($data, &$joins, &$where, $andOperation = FALSE)
{
$field_id = $this->get('id');
if (!is_array($data)) {
$data = array($data);
}
foreach ($data as &$value) {
$value = SearchIndex::manipulateKeywords($this->cleanValue($value));
}
$this->_key++;
if (is_array($data)) {
$data = implode(" ", $data);
}
$joins .= " LEFT JOIN `tbl_search_index` AS search_index ON (e.id = search_index.entry_id) ";
$where .= " AND MATCH(search_index.data) AGAINST ('{$data}' IN BOOLEAN MODE) ";
return TRUE;
}
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;
}
