1<?php
2
3namespace dokuwiki\plugin\structat\meta;
4
5use dokuwiki\plugin\struct\meta\Column;
6use dokuwiki\plugin\struct\meta\QueryBuilder;
7use dokuwiki\plugin\struct\meta\SearchConfig;
8use dokuwiki\plugin\struct\meta\StructException;
9
10/**
11 * Class SearchConfig
12 *
13 * The same as @see SearchConfig but supports at parameter
14 *
15 * @package dokuwiki\plugin\structat\meta
16 */
17class SearchConfigAt extends SearchConfig
18{
19    /** @var  int show rows at this timestamp */
20    protected $at = 0;
21
22    /**
23     * SearchConfig constructor.
24     * @param array $config The parsed configuration for this search
25     */
26    public function __construct($config)
27    {
28        parent::__construct($config);
29        // apply dynamic paramters
30        $this->dynamicParameters = new SearchConfigAtParameters($this);
31        $config = $this->dynamicParameters->updateConfig($config);
32
33        if (!empty($config['at'])) {
34            $this->setAt($config['at']);
35        }
36
37        $this->config = $config;
38    }
39
40    /**
41     * Set the at parameter
42     */
43    public function setAt($at)
44    {
45        $this->at = $at;
46    }
47
48    /**
49     * Transform the set search parameters into a statement
50     *
51     * @return array ($sql, $opts) The SQL and parameters to execute
52     */
53    public function getSQL()
54    {
55        if(!$this->columns) throw new StructException('nocolname');
56
57        $QB = new QueryBuilder();
58
59        // basic tables
60        $first_table = '';
61        foreach($this->schemas as $schema) {
62            $datatable = 'data_' . $schema->getTable();
63            if($first_table) {
64                // follow up tables
65                $QB->addLeftJoin($first_table, $datatable, $datatable, "$first_table.pid = $datatable.pid");
66            } else {
67                // first table
68                $QB->addTable($datatable);
69
70                // add conditional page clauses if pid has a value
71                $subAnd = $QB->filters()->whereSubAnd();
72                $subAnd->whereAnd("$datatable.pid = ''");
73                $subOr = $subAnd->whereSubOr();
74                $subOr->whereAnd("GETACCESSLEVEL($datatable.pid) > 0");
75                $subOr->whereAnd("PAGEEXISTS($datatable.pid) = 1");
76                $subOr->whereAnd('(ASSIGNED = 1 OR ASSIGNED IS NULL)');
77
78                // add conditional schema assignment check
79                $QB->addLeftJoin(
80                    $datatable,
81                    'schema_assignments',
82                    '',
83                    "$datatable.pid != ''
84                    AND $datatable.pid = schema_assignments.pid
85                    AND schema_assignments.tbl = '{$schema->getTable()}'"
86                );
87
88                $QB->addSelectColumn($datatable, 'rid');
89                $QB->addSelectColumn($datatable, 'pid', 'PID');
90                $QB->addSelectColumn($datatable, 'rev');
91                $QB->addSelectColumn('schema_assignments', 'assigned', 'ASSIGNED');
92                $QB->addGroupByColumn($datatable, 'pid');
93                $QB->addGroupByColumn($datatable, 'rid');
94
95                $first_table = $datatable;
96            }
97            if ($this->at) {
98                $QB->filters()->whereAnd("$datatable.rev =
99                (SELECT MAX(SUB.rev) FROM $datatable SUB
100                    WHERE SUB.pid=$datatable.pid AND SUB.rev <= '$this->at')");
101            } else {
102                $QB->filters()->whereAnd("$datatable.latest = 1");
103            }
104        }
105
106        // columns to select, handling multis
107        $sep = self::CONCAT_SEPARATOR;
108        $n   = 0;
109        foreach($this->columns as $col) {
110            $CN = 'C' . $n++;
111
112            if($col->isMulti()) {
113                $datatable  = "data_{$col->getTable()}";
114                $multitable = "multi_{$col->getTable()}";
115                $MN         = $QB->generateTableAlias('M');
116
117                $QB->addLeftJoin(
118                    $datatable,
119                    $multitable,
120                    $MN,
121                    "$datatable.pid = $MN.pid AND $datatable.rid = $MN.rid AND
122                     $datatable.rev = $MN.rev AND
123                     $MN.colref = {$col->getColref()}"
124                );
125
126                $col->getType()->select($QB, $MN, 'value', $CN);
127                $sel = $QB->getSelectStatement($CN);
128                $QB->addSelectStatement("GROUP_CONCAT($sel, '$sep')", $CN);
129            } else {
130                $col->getType()->select($QB, 'data_' . $col->getTable(), $col->getColName(), $CN);
131                $QB->addGroupByStatement($CN);
132            }
133        }
134
135        // where clauses
136        if(!empty($this->filter)) {
137            $userWHERE = $QB->filters()->where('AND');
138        }
139        foreach($this->filter as $filter) {
140            /** @var Column $col */
141            list($col, $value, $comp, $op) = $filter;
142
143            $datatable  = "data_{$col->getTable()}";
144            $multitable = "multi_{$col->getTable()}";
145
146            /** @var $col Column */
147            if($col->isMulti()) {
148                $MN = $QB->generateTableAlias('MN');
149
150                $QB->addLeftJoin(
151                    $datatable,
152                    $multitable,
153                    $MN,
154                    "$datatable.pid = $MN.pid AND $datatable.rid = $MN.rid AND
155                     $datatable.rev = $MN.rev AND
156                     $MN.colref = {$col->getColref()}"
157                );
158                $coltbl = $MN;
159                $colnam = 'value';
160            } else {
161                $coltbl = $datatable;
162                $colnam = $col->getColName();
163            }
164
165            $col->getType()->filter($userWHERE, $coltbl, $colnam, $comp, $value, $op); // type based filter
166        }
167
168        // sorting - we always sort by the single val column
169        foreach($this->sortby as $sort) {
170            list($col, $asc, $nc) = $sort;
171            /** @var $col Column */
172            $colname = $col->getColName(false);
173            if($nc) $colname .= ' COLLATE NOCASE';
174            $col->getType()->sort($QB, 'data_' . $col->getTable(), $colname, $asc ? 'ASC' : 'DESC');
175        }
176
177        return $QB->getSQL();
178    }
179}
180