*/
// must be run within Dokuwiki
if(!defined('DOKU_INC')) die();
/**
* Class syntax_plugin_dataau_related
*/
class syntax_plugin_dataau_related extends syntax_plugin_dataau_table {
/**
* Connect pattern to lexer
*/
function connectTo($mode) {
$this->Lexer->addSpecialPattern('----+ *datarelated(?: [ a-zA-Z0-9_]*)?-+\n.*?\n----+', $mode, 'plugin_dataau_related');
}
/**
* Handles the actual output creation.
*
* @param string $format output format being rendered
* @param Doku_Renderer $renderer the current renderer object
* @param array $dataau data created by handler()
* @return boolean rendered correctly? (however, returned value is not used at the moment)
*/
function render($format, Doku_Renderer $renderer, $dataau) {
if($format != 'xhtml') return false;
if(is_null($dataau)) return false;
if(!$this->dthlp->ready()) return false;
$sqlite = $this->dthlp->_getDB();
if(!$sqlite) return false;
if(!$dataau['sql']) return true; // sql build
$this->dthlp->_replacePlaceholdersInSQL($dataau);
$res = $sqlite->query($dataau['sql']);
if(!$sqlite->res2count($res)) return true; // no rows matched
$rows = $sqlite->res2arr($res);
$renderer->doc .= '
';
$renderer->doc .= '- ' . htmlspecialchars($dataau['title']) . '
';
$renderer->doc .= '- ';
$renderer->listu_open();
foreach($rows as $row) {
$renderer->listitem_open(1);
$renderer->internallink($row['page']);
$renderer->listitem_close();
}
$renderer->listu_close();
$renderer->doc .= '
';
$renderer->doc .= '
';
return true;
}
/**
* Builds the SQL query from the given data
*/
function _buildSQL(&$dataau, $id = null) {
global $ID;
if(is_null($id)) $id = $ID;
$cnt = 1;
$tables = array();
$cond = array();
$from = '';
$where = '';
$sqlite = $this->dthlp->_getDB();
if(!$sqlite) return false;
// prepare the columns to match against
$found = false;
foreach(array_keys($dataau['cols']) as $col) {
// get values for current page:
$values = array();
$sql = "SELECT A.value
FROM dataau A, pages B
WHERE key = ?
AND A.pid = B.pid
AND B.page = ?";
$res = $sqlite->query($sql, $col, $id);
while($value = $sqlite->res_fetch_assoc($res)) {
$values[] = $value['value'];
}
if(!count($values)) continue; // no values? ignore the column.
$found = true;
$cond[] = " ( T1.key = " . $sqlite->quote_string($col) .
" AND T1.value IN (" . $sqlite->quote_and_join($values, ',') . ") )\n";
}
$where .= ' AND (' . join(' OR ', $cond) . ') ';
// any tags to compare?
if(!$found) return false;
// prepare sorting
if($dataau['sort'][0]) {
$col = $dataau['sort'][0];
if($col == '%pageid%') {
$order = ', pages.page ' . $dataau['sort'][1];
} elseif($col == '%title%') {
$order = ', pages.title ' . $dataau['sort'][1];
} else {
// sort by hidden column?
if(!$tables[$col]) {
$tables[$col] = 'T' . (++$cnt);
$from .= ' LEFT JOIN dataau AS ' . $tables[$col] . ' ON ' . $tables[$col] . '.pid = pages.pid';
$from .= ' AND ' . $tables[$col] . ".key = " . $sqlite->quote_string($col);
}
$order = ', ' . $tables[$col] . '.value ' . $dataau['sort'][1];
}
} else {
$order = ', pages.page';
}
// add filters
if(is_array($dataau['filter']) && count($dataau['filter'])) {
$where .= ' AND ( 1=1 ';
foreach($dataau['filter'] as $filter) {
$col = $filter['key'];
$closecompare = ($filter['compare'] == 'IN(' ? ')' : '');
if($col == '%pageid%') {
$where .= " " . $filter['logic'] . " pages.page " . $filter['compare'] . " '" . $filter['value'] . "'" . $closecompare;
} elseif($col == '%title%') {
$where .= " " . $filter['logic'] . " pages.title " . $filter['compare'] . " '" . $filter['value'] . "'" . $closecompare;
} else {
// filter by hidden column?
if(!$tables[$col]) {
$tables[$col] = 'T' . (++$cnt);
$from .= ' LEFT JOIN dataau AS ' . $tables[$col] . ' ON ' . $tables[$col] . '.pid = pages.pid';
$from .= ' AND ' . $tables[$col] . ".key = " . $sqlite->quote_string($col);
}
$where .= ' ' . $filter['logic'] . ' ' . $tables[$col] . '.value ' . $filter['compare'] .
" '" . $filter['value'] . "'" . $closecompare; //value is already escaped
}
}
$where .= ' ) ';
}
// build the query
$sql = "SELECT pages.pid, pages.page as page, pages.title as title, COUNT(*) as rel
FROM pages, dataau as T1 $from
WHERE pages.pid = T1.pid
AND pages.page != " . $sqlite->quote_string($id) . "
$where
GROUP BY pages.pid
ORDER BY rel DESC$order";
// limit
if($dataau['limit']) {
$sql .= ' LIMIT ' . ($dataau['limit']);
}
return $sql;
}
}