1 | <?php |
||
2 | /** |
||
3 | * Handles DB QBE search |
||
4 | */ |
||
5 | |||
6 | declare(strict_types=1); |
||
7 | |||
8 | namespace PhpMyAdmin\Database; |
||
9 | |||
10 | use PhpMyAdmin\Core; |
||
11 | use PhpMyAdmin\DatabaseInterface; |
||
12 | use PhpMyAdmin\Html\Generator; |
||
13 | use PhpMyAdmin\Message; |
||
14 | use PhpMyAdmin\Relation; |
||
15 | use PhpMyAdmin\SavedSearches; |
||
16 | use PhpMyAdmin\Table; |
||
17 | use PhpMyAdmin\Template; |
||
18 | use PhpMyAdmin\Util; |
||
19 | use function array_diff; |
||
20 | use function array_fill; |
||
21 | use function array_keys; |
||
22 | use function array_map; |
||
23 | use function array_multisort; |
||
24 | use function count; |
||
25 | use function explode; |
||
26 | use function htmlspecialchars; |
||
27 | use function implode; |
||
28 | use function in_array; |
||
29 | use function key; |
||
30 | use function max; |
||
31 | use function mb_strlen; |
||
32 | use function mb_strtoupper; |
||
33 | use function mb_substr; |
||
34 | use function min; |
||
35 | use function reset; |
||
36 | use function str_replace; |
||
37 | use function stripos; |
||
38 | use function strlen; |
||
39 | |||
40 | /** |
||
41 | * Class to handle database QBE search |
||
42 | */ |
||
43 | class Qbe |
||
44 | { |
||
45 | /** |
||
46 | * Database name |
||
47 | * |
||
48 | * @access private |
||
49 | * @var string |
||
50 | */ |
||
51 | private $_db; |
||
52 | /** |
||
53 | * Table Names (selected/non-selected) |
||
54 | * |
||
55 | * @access private |
||
56 | * @var array |
||
57 | */ |
||
58 | private $_criteriaTables; |
||
59 | /** |
||
60 | * Column Names |
||
61 | * |
||
62 | * @access private |
||
63 | * @var array |
||
64 | */ |
||
65 | private $_columnNames; |
||
66 | /** |
||
67 | * Number of columns |
||
68 | * |
||
69 | * @access private |
||
70 | * @var int |
||
71 | */ |
||
72 | private $_criteria_column_count; |
||
73 | /** |
||
74 | * Number of Rows |
||
75 | * |
||
76 | * @access private |
||
77 | * @var int |
||
78 | */ |
||
79 | private $_criteria_row_count; |
||
80 | /** |
||
81 | * Whether to insert a new column |
||
82 | * |
||
83 | * @access private |
||
84 | * @var array |
||
85 | */ |
||
86 | private $_criteriaColumnInsert; |
||
87 | /** |
||
88 | * Whether to delete a column |
||
89 | * |
||
90 | * @access private |
||
91 | * @var array |
||
92 | */ |
||
93 | private $_criteriaColumnDelete; |
||
94 | /** |
||
95 | * Whether to insert a new row |
||
96 | * |
||
97 | * @access private |
||
98 | * @var array |
||
99 | */ |
||
100 | private $_criteriaRowInsert; |
||
101 | /** |
||
102 | * Whether to delete a row |
||
103 | * |
||
104 | * @access private |
||
105 | * @var array |
||
106 | */ |
||
107 | private $_criteriaRowDelete; |
||
108 | /** |
||
109 | * Already set criteria values |
||
110 | * |
||
111 | * @access private |
||
112 | * @var array |
||
113 | */ |
||
114 | private $_criteria; |
||
115 | /** |
||
116 | * Previously set criteria values |
||
117 | * |
||
118 | * @access private |
||
119 | * @var array |
||
120 | */ |
||
121 | private $_prev_criteria; |
||
122 | /** |
||
123 | * AND/OR relation b/w criteria columns |
||
124 | * |
||
125 | * @access private |
||
126 | * @var array |
||
127 | */ |
||
128 | private $_criteriaAndOrColumn; |
||
129 | /** |
||
130 | * AND/OR relation b/w criteria rows |
||
131 | * |
||
132 | * @access private |
||
133 | * @var array |
||
134 | */ |
||
135 | private $_criteriaAndOrRow; |
||
136 | /** |
||
137 | * Large width of a column |
||
138 | * |
||
139 | * @access private |
||
140 | * @var string |
||
141 | */ |
||
142 | private $_realwidth; |
||
143 | /** |
||
144 | * Minimum width of a column |
||
145 | * |
||
146 | * @access private |
||
147 | * @var int |
||
148 | */ |
||
149 | private $_form_column_width; |
||
150 | /** |
||
151 | * Selected columns in the form |
||
152 | * |
||
153 | * @access private |
||
154 | * @var array |
||
155 | */ |
||
156 | private $_formColumns; |
||
157 | /** |
||
158 | * Entered aliases in the form |
||
159 | * |
||
160 | * @access private |
||
161 | * @var array |
||
162 | */ |
||
163 | private $_formAliases; |
||
164 | /** |
||
165 | * Chosen sort options in the form |
||
166 | * |
||
167 | * @access private |
||
168 | * @var array |
||
169 | */ |
||
170 | private $_formSorts; |
||
171 | /** |
||
172 | * Chosen sort orders in the form |
||
173 | * |
||
174 | * @access private |
||
175 | * @var array |
||
176 | */ |
||
177 | private $_formSortOrders; |
||
178 | /** |
||
179 | * Show checkboxes in the form |
||
180 | * |
||
181 | * @access private |
||
182 | * @var array |
||
183 | */ |
||
184 | private $_formShows; |
||
185 | /** |
||
186 | * Entered criteria values in the form |
||
187 | * |
||
188 | * @access private |
||
189 | * @var array |
||
190 | */ |
||
191 | private $_formCriterions; |
||
192 | /** |
||
193 | * AND/OR column radio buttons in the form |
||
194 | * |
||
195 | * @access private |
||
196 | * @var array |
||
197 | */ |
||
198 | private $_formAndOrCols; |
||
199 | /** |
||
200 | * AND/OR row radio buttons in the form |
||
201 | * |
||
202 | * @access private |
||
203 | * @var array |
||
204 | */ |
||
205 | private $_formAndOrRows; |
||
206 | /** |
||
207 | * New column count in case of add/delete |
||
208 | * |
||
209 | * @access private |
||
210 | * @var int |
||
211 | */ |
||
212 | private $_new_column_count; |
||
213 | /** |
||
214 | * New row count in case of add/delete |
||
215 | * |
||
216 | * @access private |
||
217 | * @var int |
||
218 | */ |
||
219 | private $_new_row_count; |
||
220 | /** |
||
221 | * List of saved searches |
||
222 | * |
||
223 | * @access private |
||
224 | * @var array |
||
225 | */ |
||
226 | private $_savedSearchList = null; |
||
227 | /** |
||
228 | * Current search |
||
229 | * |
||
230 | * @access private |
||
231 | * @var SavedSearches |
||
232 | */ |
||
233 | private $_currentSearch = null; |
||
234 | |||
235 | /** @var Relation */ |
||
236 | private $relation; |
||
237 | |||
238 | /** @var DatabaseInterface */ |
||
239 | public $dbi; |
||
240 | |||
241 | /** @var Template */ |
||
242 | public $template; |
||
243 | |||
244 | /** |
||
245 | * @param Relation $relation Relation object |
||
246 | * @param Template $template Template object |
||
247 | * @param DatabaseInterface $dbi DatabaseInterface object |
||
248 | * @param string $dbname Database name |
||
249 | * @param array $savedSearchList List of saved searches |
||
250 | * @param SavedSearches $currentSearch Current search id |
||
251 | */ |
||
252 | 68 | public function __construct( |
|
253 | Relation $relation, |
||
254 | Template $template, |
||
255 | $dbi, |
||
256 | $dbname, |
||
257 | array $savedSearchList = [], |
||
258 | $currentSearch = null |
||
259 | ) { |
||
260 | 68 | $this->_db = $dbname; |
|
261 | 68 | $this->_savedSearchList = $savedSearchList; |
|
262 | 68 | $this->_currentSearch = $currentSearch; |
|
263 | 68 | $this->dbi = $dbi; |
|
264 | 68 | $this->relation = $relation; |
|
265 | 68 | $this->template = $template; |
|
266 | |||
267 | 68 | $this->loadCriterias(); |
|
268 | // Sets criteria parameters |
||
269 | 68 | $this->setSearchParams(); |
|
270 | 68 | $this->setCriteriaTablesAndColumns(); |
|
271 | 68 | } |
|
272 | |||
273 | /** |
||
274 | * Initialize criterias |
||
275 | * |
||
276 | * @return static |
||
277 | */ |
||
278 | 68 | private function loadCriterias() |
|
279 | { |
||
280 | 68 | if ($this->_currentSearch === null |
|
281 | 68 | || $this->_currentSearch->getCriterias() === null |
|
282 | ) { |
||
283 | 68 | return $this; |
|
284 | } |
||
285 | |||
286 | $criterias = $this->_currentSearch->getCriterias(); |
||
287 | $_POST = $criterias + $_POST; |
||
288 | |||
289 | return $this; |
||
290 | } |
||
291 | |||
292 | /** |
||
293 | * Getter for current search |
||
294 | * |
||
295 | * @return SavedSearches |
||
296 | */ |
||
297 | private function getCurrentSearch() |
||
298 | { |
||
299 | return $this->_currentSearch; |
||
300 | } |
||
301 | |||
302 | /** |
||
303 | * Sets search parameters |
||
304 | * |
||
305 | * @return void |
||
306 | */ |
||
307 | 68 | private function setSearchParams() |
|
308 | { |
||
309 | 68 | $criteriaColumnCount = $this->initializeCriteriasCount(); |
|
310 | |||
311 | 68 | $this->_criteriaColumnInsert = Core::ifSetOr( |
|
312 | 68 | $_POST['criteriaColumnInsert'], |
|
313 | 68 | null, |
|
314 | 68 | 'array' |
|
315 | ); |
||
316 | 68 | $this->_criteriaColumnDelete = Core::ifSetOr( |
|
317 | 68 | $_POST['criteriaColumnDelete'], |
|
318 | 68 | null, |
|
319 | 68 | 'array' |
|
320 | ); |
||
321 | |||
322 | 68 | $this->_prev_criteria = $_POST['prev_criteria'] ?? []; |
|
323 | 68 | $this->_criteria = $_POST['criteria'] ?? array_fill(0, $criteriaColumnCount, ''); |
|
324 | |||
325 | 68 | $this->_criteriaRowInsert = $_POST['criteriaRowInsert'] ?? array_fill(0, $criteriaColumnCount, ''); |
|
326 | 68 | $this->_criteriaRowDelete = $_POST['criteriaRowDelete'] ?? array_fill(0, $criteriaColumnCount, ''); |
|
327 | 68 | $this->_criteriaAndOrRow = $_POST['criteriaAndOrRow'] ?? array_fill(0, $criteriaColumnCount, ''); |
|
328 | 68 | $this->_criteriaAndOrColumn = $_POST['criteriaAndOrColumn'] ?? array_fill(0, $criteriaColumnCount, ''); |
|
329 | // sets minimum width |
||
330 | 68 | $this->_form_column_width = 12; |
|
331 | 68 | $this->_formColumns = []; |
|
332 | 68 | $this->_formSorts = []; |
|
333 | 68 | $this->_formShows = []; |
|
334 | 68 | $this->_formCriterions = []; |
|
335 | 68 | $this->_formAndOrRows = []; |
|
336 | 68 | $this->_formAndOrCols = []; |
|
337 | 68 | } |
|
338 | |||
339 | /** |
||
340 | * Sets criteria tables and columns |
||
341 | * |
||
342 | * @return void |
||
343 | */ |
||
344 | 68 | private function setCriteriaTablesAndColumns() |
|
345 | { |
||
346 | // The tables list sent by a previously submitted form |
||
347 | 68 | if (Core::isValid($_POST['TableList'], 'array')) { |
|
348 | foreach ($_POST['TableList'] as $each_table) { |
||
349 | $this->_criteriaTables[$each_table] = ' selected="selected"'; |
||
350 | } |
||
351 | } // end if |
||
352 | 68 | $all_tables = $this->dbi->query( |
|
353 | 68 | 'SHOW TABLES FROM ' . Util::backquote($this->_db) . ';', |
|
354 | 68 | DatabaseInterface::CONNECT_USER, |
|
355 | 68 | DatabaseInterface::QUERY_STORE |
|
356 | ); |
||
357 | 68 | $all_tables_count = $this->dbi->numRows($all_tables); |
|
358 | 68 | if ($all_tables_count == 0) { |
|
359 | Message::error(__('No tables found in database.'))->display(); |
||
360 | exit; |
||
361 | } |
||
362 | // The tables list gets from MySQL |
||
363 | 68 | while ([$table] = $this->dbi->fetchRow($all_tables)) { |
|
364 | 68 | $columns = $this->dbi->getColumns($this->_db, $table); |
|
365 | |||
366 | 68 | if (empty($this->_criteriaTables[$table]) |
|
367 | 68 | && ! empty($_POST['TableList']) |
|
368 | ) { |
||
369 | $this->_criteriaTables[$table] = ''; |
||
370 | } else { |
||
371 | 68 | $this->_criteriaTables[$table] = ' selected="selected"'; |
|
372 | } // end if |
||
373 | |||
374 | // The fields list per selected tables |
||
375 | 68 | if ($this->_criteriaTables[$table] == ' selected="selected"') { |
|
376 | 68 | $each_table = Util::backquote($table); |
|
377 | 68 | $this->_columnNames[] = $each_table . '.*'; |
|
378 | 68 | foreach ($columns as $each_column) { |
|
379 | 68 | $each_column = $each_table . '.' |
|
380 | 68 | . Util::backquote($each_column['Field']); |
|
381 | 68 | $this->_columnNames[] = $each_column; |
|
382 | // increase the width if necessary |
||
383 | 68 | $this->_form_column_width = max( |
|
384 | 68 | mb_strlen($each_column), |
|
385 | 68 | $this->_form_column_width |
|
386 | ); |
||
387 | } // end foreach |
||
388 | } // end if |
||
389 | } // end while |
||
390 | 68 | $this->dbi->freeResult($all_tables); |
|
391 | |||
392 | // sets the largest width found |
||
393 | 68 | $this->_realwidth = $this->_form_column_width . 'ex'; |
|
394 | 68 | } |
|
395 | |||
396 | /** |
||
397 | * Provides select options list containing column names |
||
398 | * |
||
399 | * @param int $column_number Column Number (0,1,2) or more |
||
400 | * @param string $selected Selected criteria column name |
||
401 | * |
||
402 | * @return string HTML for select options |
||
403 | */ |
||
404 | private function showColumnSelectCell($column_number, $selected = '') |
||
405 | { |
||
406 | return $this->template->render('database/qbe/column_select_cell', [ |
||
407 | 'column_number' => $column_number, |
||
408 | 'column_names' => $this->_columnNames, |
||
409 | 'selected' => $selected, |
||
410 | ]); |
||
411 | } |
||
412 | |||
413 | /** |
||
414 | * Provides select options list containing sort options (ASC/DESC) |
||
415 | * |
||
416 | * @param int $columnNumber Column Number (0,1,2) or more |
||
417 | * @param string $selected Selected criteria 'ASC' or 'DESC' |
||
418 | * |
||
419 | * @return string HTML for select options |
||
420 | */ |
||
421 | 8 | private function getSortSelectCell( |
|
422 | $columnNumber, |
||
423 | $selected = '' |
||
424 | ) { |
||
425 | 8 | return $this->template->render('database/qbe/sort_select_cell', [ |
|
426 | 8 | 'real_width' => $this->_realwidth, |
|
427 | 8 | 'column_number' => $columnNumber, |
|
428 | 8 | 'selected' => $selected, |
|
429 | ]); |
||
430 | } |
||
431 | |||
432 | /** |
||
433 | * Provides select options list containing sort order |
||
434 | * |
||
435 | * @param int $columnNumber Column Number (0,1,2) or more |
||
436 | * @param int $sortOrder Sort order |
||
437 | * |
||
438 | * @return string HTML for select options |
||
439 | */ |
||
440 | private function getSortOrderSelectCell($columnNumber, $sortOrder) |
||
441 | { |
||
442 | $totalColumnCount = $this->getNewColumnCount(); |
||
443 | |||
444 | return $this->template->render('database/qbe/sort_order_select_cell', [ |
||
445 | 'total_column_count' => $totalColumnCount, |
||
446 | 'column_number' => $columnNumber, |
||
447 | 'sort_order' => $sortOrder, |
||
448 | ]); |
||
449 | } |
||
450 | |||
451 | /** |
||
452 | * Returns the new column count after adding and removing columns as instructed |
||
453 | * |
||
454 | * @return int new column count |
||
455 | */ |
||
456 | private function getNewColumnCount() |
||
457 | { |
||
458 | $totalColumnCount = $this->_criteria_column_count; |
||
459 | if (! empty($this->_criteriaColumnInsert)) { |
||
460 | $totalColumnCount += count($this->_criteriaColumnInsert); |
||
461 | } |
||
462 | if (! empty($this->_criteriaColumnDelete)) { |
||
463 | $totalColumnCount -= count($this->_criteriaColumnDelete); |
||
464 | } |
||
465 | |||
466 | return $totalColumnCount; |
||
467 | } |
||
468 | |||
469 | /** |
||
470 | * Provides search form's row containing column select options |
||
471 | * |
||
472 | * @return string HTML for search table's row |
||
473 | */ |
||
474 | private function getColumnNamesRow() |
||
475 | { |
||
476 | $html_output = ''; |
||
477 | |||
478 | $new_column_count = 0; |
||
479 | for ($column_index = 0; $column_index < $this->_criteria_column_count; $column_index++) { |
||
480 | if (isset($this->_criteriaColumnInsert[$column_index]) |
||
481 | && $this->_criteriaColumnInsert[$column_index] == 'on' |
||
482 | ) { |
||
483 | $html_output .= $this->showColumnSelectCell( |
||
484 | $new_column_count |
||
485 | ); |
||
486 | $new_column_count++; |
||
487 | } |
||
488 | if (! empty($this->_criteriaColumnDelete) |
||
489 | && isset($this->_criteriaColumnDelete[$column_index]) |
||
490 | && $this->_criteriaColumnDelete[$column_index] == 'on' |
||
491 | ) { |
||
492 | continue; |
||
493 | } |
||
494 | $selected = ''; |
||
495 | if (isset($_POST['criteriaColumn'][$column_index])) { |
||
496 | $selected = $_POST['criteriaColumn'][$column_index]; |
||
497 | $this->_formColumns[$new_column_count] |
||
498 | = $_POST['criteriaColumn'][$column_index]; |
||
499 | } |
||
500 | $html_output .= $this->showColumnSelectCell( |
||
501 | $new_column_count, |
||
502 | $selected |
||
503 | ); |
||
504 | $new_column_count++; |
||
505 | } // end for |
||
506 | $this->_new_column_count = $new_column_count; |
||
507 | |||
508 | return $html_output; |
||
509 | } |
||
510 | |||
511 | /** |
||
512 | * Provides search form's row containing column aliases |
||
513 | * |
||
514 | * @return string HTML for search table's row |
||
515 | */ |
||
516 | private function getColumnAliasRow() |
||
517 | { |
||
518 | $html_output = ''; |
||
519 | |||
520 | $new_column_count = 0; |
||
521 | |||
522 | for ($colInd = 0; $colInd < $this->_criteria_column_count; $colInd++) { |
||
523 | if (! empty($this->_criteriaColumnInsert) |
||
524 | && isset($this->_criteriaColumnInsert[$colInd]) |
||
525 | && $this->_criteriaColumnInsert[$colInd] == 'on' |
||
526 | ) { |
||
527 | $html_output .= '<td class="text-center">'; |
||
528 | $html_output .= '<input type="text"' |
||
529 | . ' name="criteriaAlias[' . $new_column_count . ']">'; |
||
530 | $html_output .= '</td>'; |
||
531 | $new_column_count++; |
||
532 | } // end if |
||
533 | |||
534 | if (! empty($this->_criteriaColumnDelete) |
||
535 | && isset($this->_criteriaColumnDelete[$colInd]) |
||
536 | && $this->_criteriaColumnDelete[$colInd] == 'on' |
||
537 | ) { |
||
538 | continue; |
||
539 | } |
||
540 | |||
541 | $tmp_alias = ''; |
||
542 | if (! empty($_POST['criteriaAlias'][$colInd])) { |
||
543 | $tmp_alias |
||
544 | = $this->_formAliases[$new_column_count] |
||
545 | = $_POST['criteriaAlias'][$colInd]; |
||
546 | }// end if |
||
547 | |||
548 | $html_output .= '<td class="text-center">'; |
||
549 | $html_output .= '<input type="text"' |
||
550 | . ' name="criteriaAlias[' . $new_column_count . ']"' |
||
551 | . ' value="' . htmlspecialchars($tmp_alias) . '">'; |
||
552 | $html_output .= '</td>'; |
||
553 | $new_column_count++; |
||
554 | } |
||
555 | |||
556 | return $html_output; |
||
557 | } |
||
558 | |||
559 | /** |
||
560 | * Provides search form's row containing sort(ASC/DESC) select options |
||
561 | * |
||
562 | * @return string HTML for search table's row |
||
563 | */ |
||
564 | 4 | private function getSortRow() |
|
565 | { |
||
566 | 4 | $html_output = ''; |
|
567 | |||
568 | 4 | $new_column_count = 0; |
|
569 | |||
570 | 4 | for ($colInd = 0; $colInd < $this->_criteria_column_count; $colInd++) { |
|
571 | 4 | if (! empty($this->_criteriaColumnInsert) |
|
572 | 4 | && isset($this->_criteriaColumnInsert[$colInd]) |
|
573 | 4 | && $this->_criteriaColumnInsert[$colInd] == 'on' |
|
574 | ) { |
||
575 | $html_output .= $this->getSortSelectCell($new_column_count); |
||
576 | $new_column_count++; |
||
577 | } // end if |
||
578 | |||
579 | 4 | if (! empty($this->_criteriaColumnDelete) |
|
580 | 4 | && isset($this->_criteriaColumnDelete[$colInd]) |
|
581 | 4 | && $this->_criteriaColumnDelete[$colInd] == 'on' |
|
582 | ) { |
||
583 | continue; |
||
584 | } |
||
585 | // If they have chosen all fields using the * selector, |
||
586 | // then sorting is not available, Fix for Bug #570698 |
||
587 | 4 | if (isset($_POST['criteriaSort'][$colInd], $_POST['criteriaColumn'][$colInd]) |
|
588 | 4 | && mb_substr($_POST['criteriaColumn'][$colInd], -2) == '.*' |
|
589 | ) { |
||
590 | $_POST['criteriaSort'][$colInd] = ''; |
||
591 | } //end if |
||
592 | |||
593 | 4 | $selected = ''; |
|
594 | 4 | if (isset($_POST['criteriaSort'][$colInd])) { |
|
595 | $this->_formSorts[$new_column_count] |
||
596 | = $_POST['criteriaSort'][$colInd]; |
||
597 | |||
598 | if ($_POST['criteriaSort'][$colInd] == 'ASC') { |
||
599 | $selected = 'ASC'; |
||
600 | } elseif ($_POST['criteriaSort'][$colInd] == 'DESC') { |
||
601 | $selected = 'DESC'; |
||
602 | } |
||
603 | } else { |
||
604 | 4 | $this->_formSorts[$new_column_count] = ''; |
|
605 | } |
||
606 | |||
607 | 4 | $html_output .= $this->getSortSelectCell( |
|
608 | 4 | $new_column_count, |
|
609 | 4 | $selected |
|
610 | ); |
||
611 | 4 | $new_column_count++; |
|
612 | } |
||
613 | |||
614 | 4 | return $html_output; |
|
615 | } |
||
616 | |||
617 | /** |
||
618 | * Provides search form's row containing sort order |
||
619 | * |
||
620 | * @return string HTML for search table's row |
||
621 | */ |
||
622 | private function getSortOrder() |
||
623 | { |
||
624 | $html_output = ''; |
||
625 | |||
626 | $new_column_count = 0; |
||
627 | |||
628 | for ($colInd = 0; $colInd < $this->_criteria_column_count; $colInd++) { |
||
629 | if (! empty($this->_criteriaColumnInsert) |
||
630 | && isset($this->_criteriaColumnInsert[$colInd]) |
||
631 | && $this->_criteriaColumnInsert[$colInd] == 'on' |
||
632 | ) { |
||
633 | $html_output .= $this->getSortOrderSelectCell( |
||
634 | $new_column_count, |
||
635 | null |
||
636 | ); |
||
637 | $new_column_count++; |
||
638 | } // end if |
||
639 | |||
640 | if (! empty($this->_criteriaColumnDelete) |
||
641 | && isset($this->_criteriaColumnDelete[$colInd]) |
||
642 | && $this->_criteriaColumnDelete[$colInd] == 'on' |
||
643 | ) { |
||
644 | continue; |
||
645 | } |
||
646 | |||
647 | $sortOrder = null; |
||
648 | if (! empty($_POST['criteriaSortOrder'][$colInd])) { |
||
649 | $sortOrder |
||
650 | = $this->_formSortOrders[$new_column_count] |
||
651 | = $_POST['criteriaSortOrder'][$colInd]; |
||
652 | } |
||
653 | |||
654 | $html_output .= $this->getSortOrderSelectCell( |
||
655 | $new_column_count, |
||
656 | $sortOrder |
||
657 | ); |
||
658 | $new_column_count++; |
||
659 | } |
||
660 | |||
661 | return $html_output; |
||
662 | } |
||
663 | |||
664 | /** |
||
665 | * Provides search form's row containing SHOW checkboxes |
||
666 | * |
||
667 | * @return string HTML for search table's row |
||
668 | */ |
||
669 | 4 | private function getShowRow() |
|
670 | { |
||
671 | 4 | $html_output = ''; |
|
672 | |||
673 | 4 | $new_column_count = 0; |
|
674 | 4 | for ($column_index = 0; $column_index < $this->_criteria_column_count; $column_index++) { |
|
675 | 4 | if (! empty($this->_criteriaColumnInsert) |
|
676 | 4 | && isset($this->_criteriaColumnInsert[$column_index]) |
|
677 | 4 | && $this->_criteriaColumnInsert[$column_index] == 'on' |
|
678 | ) { |
||
679 | $html_output .= '<td class="text-center">'; |
||
680 | $html_output .= '<input type="checkbox"' |
||
681 | . ' name="criteriaShow[' . $new_column_count . ']">'; |
||
682 | $html_output .= '</td>'; |
||
683 | $new_column_count++; |
||
684 | } // end if |
||
685 | 4 | if (! empty($this->_criteriaColumnDelete) |
|
686 | 4 | && isset($this->_criteriaColumnDelete[$column_index]) |
|
687 | 4 | && $this->_criteriaColumnDelete[$column_index] == 'on' |
|
688 | ) { |
||
689 | continue; |
||
690 | } |
||
691 | 4 | if (isset($_POST['criteriaShow'][$column_index])) { |
|
692 | $checked_options = ' checked="checked"'; |
||
693 | $this->_formShows[$new_column_count] |
||
694 | = $_POST['criteriaShow'][$column_index]; |
||
695 | } else { |
||
696 | 4 | $checked_options = ''; |
|
697 | } |
||
698 | 4 | $html_output .= '<td class="text-center">'; |
|
699 | $html_output .= '<input type="checkbox"' |
||
700 | 4 | . ' name="criteriaShow[' . $new_column_count . ']"' |
|
701 | 4 | . $checked_options . '>'; |
|
702 | 4 | $html_output .= '</td>'; |
|
703 | 4 | $new_column_count++; |
|
704 | } |
||
705 | |||
706 | 4 | return $html_output; |
|
707 | } |
||
708 | |||
709 | /** |
||
710 | * Provides search form's row containing criteria Inputboxes |
||
711 | * |
||
712 | * @return string HTML for search table's row |
||
713 | */ |
||
714 | 4 | private function getCriteriaInputboxRow() |
|
715 | { |
||
716 | 4 | $html_output = ''; |
|
717 | |||
718 | 4 | $new_column_count = 0; |
|
719 | 4 | for ($column_index = 0; $column_index < $this->_criteria_column_count; $column_index++) { |
|
720 | 4 | if (! empty($this->_criteriaColumnInsert) |
|
721 | 4 | && isset($this->_criteriaColumnInsert[$column_index]) |
|
722 | 4 | && $this->_criteriaColumnInsert[$column_index] == 'on' |
|
723 | ) { |
||
724 | $html_output .= '<td class="text-center">'; |
||
725 | $html_output .= '<input type="text"' |
||
726 | . ' name="criteria[' . $new_column_count . ']"' |
||
727 | . ' class="textfield"' |
||
728 | . ' style="width: ' . $this->_realwidth . '"' |
||
729 | . ' size="20">'; |
||
730 | $html_output .= '</td>'; |
||
731 | $new_column_count++; |
||
732 | } // end if |
||
733 | 4 | if (! empty($this->_criteriaColumnDelete) |
|
734 | 4 | && isset($this->_criteriaColumnDelete[$column_index]) |
|
735 | 4 | && $this->_criteriaColumnDelete[$column_index] == 'on' |
|
736 | ) { |
||
737 | continue; |
||
738 | } |
||
739 | 4 | $tmp_criteria = ''; |
|
740 | 4 | if (isset($this->_criteria[$column_index])) { |
|
741 | 4 | $tmp_criteria = $this->_criteria[$column_index]; |
|
742 | } |
||
743 | 4 | if ((empty($this->_prev_criteria) |
|
744 | || ! isset($this->_prev_criteria[$column_index])) |
||
745 | 4 | || $this->_prev_criteria[$column_index] != htmlspecialchars($tmp_criteria) |
|
746 | ) { |
||
747 | 4 | $this->_formCriterions[$new_column_count] = $tmp_criteria; |
|
748 | } else { |
||
749 | $this->_formCriterions[$new_column_count] |
||
750 | = $this->_prev_criteria[$column_index]; |
||
751 | } |
||
752 | 4 | $html_output .= '<td class="text-center">'; |
|
753 | $html_output .= '<input type="hidden"' |
||
754 | 4 | . ' name="prev_criteria[' . $new_column_count . ']"' |
|
755 | 4 | . ' value="' |
|
756 | 4 | . htmlspecialchars($this->_formCriterions[$new_column_count]) |
|
757 | 4 | . '">'; |
|
758 | $html_output .= '<input type="text"' |
||
759 | 4 | . ' name="criteria[' . $new_column_count . ']"' |
|
760 | 4 | . ' value="' . htmlspecialchars($tmp_criteria) . '"' |
|
761 | 4 | . ' class="textfield"' |
|
762 | 4 | . ' style="width: ' . $this->_realwidth . '"' |
|
763 | 4 | . ' size="20">'; |
|
764 | 4 | $html_output .= '</td>'; |
|
765 | 4 | $new_column_count++; |
|
766 | } |
||
767 | |||
768 | 4 | return $html_output; |
|
769 | } |
||
770 | |||
771 | /** |
||
772 | * Provides And/Or modification cell along with Insert/Delete options |
||
773 | * (For modifying search form's table columns) |
||
774 | * |
||
775 | * @param int $column_number Column Number (0,1,2) or more |
||
776 | * @param array|null $selected Selected criteria column name |
||
777 | * @param bool $last_column Whether this is the last column |
||
778 | * |
||
779 | * @return string HTML for modification cell |
||
780 | */ |
||
781 | 8 | private function getAndOrColCell( |
|
782 | $column_number, |
||
783 | $selected = null, |
||
784 | $last_column = false |
||
785 | ) { |
||
786 | 8 | $html_output = '<td class="text-center">'; |
|
787 | 8 | if (! $last_column) { |
|
788 | 8 | $html_output .= '<strong>' . __('Or:') . '</strong>'; |
|
789 | $html_output .= '<input type="radio"' |
||
790 | 8 | . ' name="criteriaAndOrColumn[' . $column_number . ']"' |
|
791 | 8 | . ' value="or"' . ($selected['or'] ?? '') . '>'; |
|
792 | 8 | $html_output .= ' <strong>' . __('And:') . '</strong>'; |
|
793 | $html_output .= '<input type="radio"' |
||
794 | 8 | . ' name="criteriaAndOrColumn[' . $column_number . ']"' |
|
795 | 8 | . ' value="and"' . ($selected['and'] ?? '') . '>'; |
|
796 | } |
||
797 | 8 | $html_output .= '<br>' . __('Ins'); |
|
798 | $html_output .= '<input type="checkbox"' |
||
799 | 8 | . ' name="criteriaColumnInsert[' . $column_number . ']">'; |
|
800 | 8 | $html_output .= ' ' . __('Del'); |
|
801 | $html_output .= '<input type="checkbox"' |
||
802 | 8 | . ' name="criteriaColumnDelete[' . $column_number . ']">'; |
|
803 | 8 | $html_output .= '</td>'; |
|
804 | |||
805 | 8 | return $html_output; |
|
806 | } |
||
807 | |||
808 | /** |
||
809 | * Provides search form's row containing column modifications options |
||
810 | * (For modifying search form's table columns) |
||
811 | * |
||
812 | * @return string HTML for search table's row |
||
813 | */ |
||
814 | 4 | private function getModifyColumnsRow() |
|
815 | { |
||
816 | 4 | $html_output = ''; |
|
817 | |||
818 | 4 | $new_column_count = 0; |
|
819 | 4 | for ($column_index = 0; $column_index < $this->_criteria_column_count; $column_index++) { |
|
820 | 4 | if (! empty($this->_criteriaColumnInsert) |
|
821 | 4 | && isset($this->_criteriaColumnInsert[$column_index]) |
|
822 | 4 | && $this->_criteriaColumnInsert[$column_index] == 'on' |
|
823 | ) { |
||
824 | $html_output .= $this->getAndOrColCell($new_column_count); |
||
825 | $new_column_count++; |
||
826 | } // end if |
||
827 | |||
828 | 4 | if (! empty($this->_criteriaColumnDelete) |
|
829 | 4 | && isset($this->_criteriaColumnDelete[$column_index]) |
|
830 | 4 | && $this->_criteriaColumnDelete[$column_index] == 'on' |
|
831 | ) { |
||
832 | continue; |
||
833 | } |
||
834 | |||
835 | 4 | if (isset($this->_criteriaAndOrColumn[$column_index])) { |
|
836 | 4 | $this->_formAndOrCols[$new_column_count] |
|
837 | 4 | = $this->_criteriaAndOrColumn[$column_index]; |
|
838 | } |
||
839 | 4 | $checked_options = []; |
|
840 | 4 | if (isset($this->_criteriaAndOrColumn[$column_index]) |
|
841 | 4 | && $this->_criteriaAndOrColumn[$column_index] == 'or' |
|
842 | ) { |
||
843 | $checked_options['or'] = ' checked="checked"'; |
||
844 | $checked_options['and'] = ''; |
||
845 | } else { |
||
846 | 4 | $checked_options['and'] = ' checked="checked"'; |
|
847 | 4 | $checked_options['or'] = ''; |
|
848 | } |
||
849 | 4 | $html_output .= $this->getAndOrColCell( |
|
850 | 4 | $new_column_count, |
|
851 | 4 | $checked_options, |
|
852 | 4 | $column_index + 1 == $this->_criteria_column_count |
|
853 | ); |
||
854 | 4 | $new_column_count++; |
|
855 | } |
||
856 | |||
857 | 4 | return $html_output; |
|
858 | } |
||
859 | |||
860 | /** |
||
861 | * Provides rows for criteria inputbox Insert/Delete options |
||
862 | * with AND/OR relationship modification options |
||
863 | * |
||
864 | * @param int $new_row_index New row index if rows are added/deleted |
||
865 | * |
||
866 | * @return string HTML table rows |
||
867 | */ |
||
868 | 8 | private function getInputboxRow($new_row_index) |
|
869 | { |
||
870 | 8 | $html_output = ''; |
|
871 | 8 | $new_column_count = 0; |
|
872 | 8 | for ($column_index = 0; $column_index < $this->_criteria_column_count; $column_index++) { |
|
873 | 8 | if (! empty($this->_criteriaColumnInsert) |
|
874 | 8 | && isset($this->_criteriaColumnInsert[$column_index]) |
|
875 | 8 | && $this->_criteriaColumnInsert[$column_index] == 'on' |
|
876 | ) { |
||
877 | $orFieldName = 'Or' . $new_row_index . '[' . $new_column_count . ']'; |
||
878 | $html_output .= '<td class="text-center">'; |
||
879 | $html_output .= '<input type="text"' |
||
880 | . ' name="Or' . $orFieldName . '" class="textfield"' |
||
881 | . ' style="width: ' . $this->_realwidth . '" size="20">'; |
||
882 | $html_output .= '</td>'; |
||
883 | $new_column_count++; |
||
884 | } // end if |
||
885 | 8 | if (! empty($this->_criteriaColumnDelete) |
|
886 | 8 | && isset($this->_criteriaColumnDelete[$column_index]) |
|
887 | 8 | && $this->_criteriaColumnDelete[$column_index] == 'on' |
|
888 | ) { |
||
889 | continue; |
||
890 | } |
||
891 | 8 | $or = 'Or' . $new_row_index; |
|
892 | 8 | if (! empty($_POST[$or]) && isset($_POST[$or][$column_index])) { |
|
893 | $tmp_or = $_POST[$or][$column_index]; |
||
894 | } else { |
||
895 | 8 | $tmp_or = ''; |
|
896 | } |
||
897 | 8 | $html_output .= '<td class="text-center">'; |
|
898 | $html_output .= '<input type="text"' |
||
899 | 8 | . ' name="Or' . $new_row_index . '[' . $new_column_count . ']"' |
|
900 | 8 | . ' value="' . htmlspecialchars($tmp_or) . '" class="textfield"' |
|
901 | 8 | . ' style="width: ' . $this->_realwidth . '" size="20">'; |
|
902 | 8 | $html_output .= '</td>'; |
|
903 | 8 | if (! empty(${$or}) && isset(${$or}[$column_index])) { |
|
904 | $GLOBALS[${'cur' . $or}][$new_column_count] |
||
905 | = ${$or}[$column_index]; |
||
906 | } |
||
907 | 8 | $new_column_count++; |
|
908 | } // end for |
||
909 | |||
910 | 8 | return $html_output; |
|
911 | } |
||
912 | |||
913 | /** |
||
914 | * Provides rows for criteria inputbox Insert/Delete options |
||
915 | * with AND/OR relationship modification options |
||
916 | * |
||
917 | * @return string HTML table rows |
||
918 | */ |
||
919 | 4 | private function getInsDelAndOrCriteriaRows() |
|
920 | { |
||
921 | 4 | $html_output = ''; |
|
922 | 4 | $new_row_count = 0; |
|
923 | 4 | $checked_options = []; |
|
924 | 4 | for ($row_index = 0; $row_index <= $this->_criteria_row_count; $row_index++) { |
|
925 | 4 | if (isset($this->_criteriaRowInsert[$row_index]) |
|
926 | 4 | && $this->_criteriaRowInsert[$row_index] == 'on' |
|
927 | ) { |
||
928 | $checked_options['or'] = true; |
||
929 | $checked_options['and'] = false; |
||
930 | $html_output .= '<tr class="noclick">'; |
||
931 | $html_output .= $this->template->render('database/qbe/ins_del_and_or_cell', [ |
||
932 | 'row_index' => $new_row_count, |
||
933 | 'checked_options' => $checked_options, |
||
934 | ]); |
||
935 | $html_output .= $this->getInputboxRow( |
||
936 | $new_row_count |
||
937 | ); |
||
938 | $new_row_count++; |
||
939 | $html_output .= '</tr>'; |
||
940 | } // end if |
||
941 | 4 | if (isset($this->_criteriaRowDelete[$row_index]) |
|
942 | 4 | && $this->_criteriaRowDelete[$row_index] == 'on' |
|
943 | ) { |
||
944 | continue; |
||
945 | } |
||
946 | 4 | if (isset($this->_criteriaAndOrRow[$row_index])) { |
|
947 | 4 | $this->_formAndOrRows[$new_row_count] |
|
948 | 4 | = $this->_criteriaAndOrRow[$row_index]; |
|
949 | } |
||
950 | 4 | if (isset($this->_criteriaAndOrRow[$row_index]) |
|
951 | 4 | && $this->_criteriaAndOrRow[$row_index] == 'and' |
|
952 | ) { |
||
953 | $checked_options['and'] = true; |
||
954 | $checked_options['or'] = false; |
||
955 | } else { |
||
956 | 4 | $checked_options['or'] = true; |
|
957 | 4 | $checked_options['and'] = false; |
|
958 | } |
||
959 | 4 | $html_output .= '<tr class="noclick">'; |
|
960 | 4 | $html_output .= $this->template->render('database/qbe/ins_del_and_or_cell', [ |
|
961 | 4 | 'row_index' => $new_row_count, |
|
962 | 4 | 'checked_options' => $checked_options, |
|
963 | ]); |
||
964 | 4 | $html_output .= $this->getInputboxRow( |
|
965 | 4 | $new_row_count |
|
966 | ); |
||
967 | 4 | $new_row_count++; |
|
968 | 4 | $html_output .= '</tr>'; |
|
969 | } // end for |
||
970 | 4 | $this->_new_row_count = $new_row_count; |
|
971 | |||
972 | 4 | return $html_output; |
|
973 | } |
||
974 | |||
975 | /** |
||
976 | * Provides SELECT clause for building SQL query |
||
977 | * |
||
978 | * @return string Select clause |
||
979 | */ |
||
980 | 8 | private function getSelectClause() |
|
981 | { |
||
982 | 8 | $select_clause = ''; |
|
983 | 8 | $select_clauses = []; |
|
984 | 8 | for ($column_index = 0; $column_index < $this->_criteria_column_count; $column_index++) { |
|
985 | 8 | if (empty($this->_formColumns[$column_index]) |
|
986 | || ! isset($this->_formShows[$column_index]) |
||
987 | 8 | || $this->_formShows[$column_index] != 'on' |
|
988 | ) { |
||
989 | 8 | continue; |
|
990 | } |
||
991 | |||
992 | $select = $this->_formColumns[$column_index]; |
||
993 | if (! empty($this->_formAliases[$column_index])) { |
||
994 | $select .= ' AS ' |
||
995 | . Util::backquote($this->_formAliases[$column_index]); |
||
996 | } |
||
997 | $select_clauses[] = $select; |
||
998 | } // end for |
||
999 | 8 | if (! empty($select_clauses)) { |
|
1000 | $select_clause = 'SELECT ' |
||
1001 | . htmlspecialchars(implode(', ', $select_clauses)) . "\n"; |
||
1002 | } |
||
1003 | |||
1004 | 8 | return $select_clause; |
|
1005 | } |
||
1006 | |||
1007 | /** |
||
1008 | * Provides WHERE clause for building SQL query |
||
1009 | * |
||
1010 | * @return string Where clause |
||
1011 | */ |
||
1012 | 8 | private function getWhereClause() |
|
1013 | { |
||
1014 | 8 | $where_clause = ''; |
|
1015 | 8 | $criteria_cnt = 0; |
|
1016 | 8 | for ($column_index = 0; $column_index < $this->_criteria_column_count; $column_index++) { |
|
1017 | 8 | if (isset($last_where, $this->_formAndOrCols) |
|
1018 | 8 | && ! empty($this->_formColumns[$column_index]) |
|
1019 | 8 | && ! empty($this->_formCriterions[$column_index]) |
|
1020 | 8 | && $column_index |
|
1021 | ) { |
||
1022 | $where_clause .= ' ' |
||
1023 | . mb_strtoupper($this->_formAndOrCols[$last_where]) |
||
1024 | . ' '; |
||
1025 | } |
||
1026 | 8 | if (empty($this->_formColumns[$column_index]) |
|
1027 | 8 | || empty($this->_formCriterions[$column_index]) |
|
1028 | ) { |
||
1029 | 8 | continue; |
|
1030 | } |
||
1031 | |||
1032 | $where_clause .= '(' . $this->_formColumns[$column_index] . ' ' |
||
1033 | . $this->_formCriterions[$column_index] . ')'; |
||
1034 | $last_where = $column_index; |
||
1035 | $criteria_cnt++; |
||
1036 | } // end for |
||
1037 | 8 | if ($criteria_cnt > 1) { |
|
1038 | $where_clause = '(' . $where_clause . ')'; |
||
1039 | } |
||
1040 | // OR rows ${'cur' . $or}[$column_index] |
||
1041 | 8 | if (! isset($this->_formAndOrRows)) { |
|
1042 | $this->_formAndOrRows = []; |
||
1043 | } |
||
1044 | 8 | for ($row_index = 0; $row_index <= $this->_criteria_row_count; $row_index++) { |
|
1045 | 8 | $criteria_cnt = 0; |
|
1046 | 8 | $qry_orwhere = ''; |
|
1047 | 8 | $last_orwhere = ''; |
|
1048 | 8 | for ($column_index = 0; $column_index < $this->_criteria_column_count; $column_index++) { |
|
1049 | 8 | if (! empty($this->_formColumns[$column_index]) |
|
1050 | 8 | && ! empty($_POST['Or' . $row_index][$column_index]) |
|
1051 | 8 | && $column_index |
|
1052 | ) { |
||
1053 | $qry_orwhere .= ' ' |
||
1054 | . mb_strtoupper( |
||
1055 | $this->_formAndOrCols[$last_orwhere] |
||
1056 | ) |
||
1057 | . ' '; |
||
1058 | } |
||
1059 | 8 | if (empty($this->_formColumns[$column_index]) |
|
1060 | 8 | || empty($_POST['Or' . $row_index][$column_index]) |
|
1061 | ) { |
||
1062 | 8 | continue; |
|
1063 | } |
||
1064 | |||
1065 | $qry_orwhere .= '(' . $this->_formColumns[$column_index] |
||
1066 | . ' ' |
||
1067 | . $_POST['Or' . $row_index][$column_index] |
||
1068 | . ')'; |
||
1069 | $last_orwhere = $column_index; |
||
1070 | $criteria_cnt++; |
||
1071 | } // end for |
||
1072 | 8 | if ($criteria_cnt > 1) { |
|
1073 | $qry_orwhere = '(' . $qry_orwhere . ')'; |
||
1074 | } |
||
1075 | 8 | if (! empty($qry_orwhere)) { |
|
1076 | $where_clause .= "\n" |
||
1077 | . mb_strtoupper( |
||
1078 | isset($this->_formAndOrRows[$row_index]) |
||
1079 | ? $this->_formAndOrRows[$row_index] . ' ' |
||
1080 | : '' |
||
1081 | ) |
||
1082 | . $qry_orwhere; |
||
1083 | } // end if |
||
1084 | } // end for |
||
1085 | |||
1086 | 8 | if (! empty($where_clause) && $where_clause != '()') { |
|
1087 | $where_clause = 'WHERE ' . $where_clause . "\n"; |
||
1088 | } // end if |
||
1089 | |||
1090 | 8 | return $where_clause; |
|
1091 | } |
||
1092 | |||
1093 | /** |
||
1094 | * Provides ORDER BY clause for building SQL query |
||
1095 | * |
||
1096 | * @return string Order By clause |
||
1097 | */ |
||
1098 | 8 | private function getOrderByClause() |
|
1099 | { |
||
1100 | 8 | $orderby_clause = ''; |
|
1101 | 8 | $orderby_clauses = []; |
|
1102 | |||
1103 | // Create copy of instance variables |
||
1104 | 8 | $columns = $this->_formColumns; |
|
1105 | 8 | $sort = $this->_formSorts; |
|
1106 | 8 | $sortOrder = $this->_formSortOrders; |
|
1107 | 8 | if (! empty($sortOrder) |
|
1108 | 8 | && count($sortOrder) == count($sort) |
|
1109 | 8 | && count($sortOrder) == count($columns) |
|
1110 | ) { |
||
1111 | // Sort all three arrays based on sort order |
||
1112 | array_multisort($sortOrder, $sort, $columns); |
||
1113 | } |
||
1114 | |||
1115 | 8 | for ($column_index = 0; $column_index < $this->_criteria_column_count; $column_index++) { |
|
1116 | // if all columns are chosen with * selector, |
||
1117 | // then sorting isn't available |
||
1118 | // Fix for Bug #570698 |
||
1119 | 8 | if (empty($columns[$column_index]) |
|
1120 | 8 | && empty($sort[$column_index]) |
|
1121 | ) { |
||
1122 | 8 | continue; |
|
1123 | } |
||
1124 | |||
1125 | if (mb_substr($columns[$column_index], -2) == '.*') { |
||
1126 | continue; |
||
1127 | } |
||
1128 | |||
1129 | if (empty($sort[$column_index])) { |
||
1130 | continue; |
||
1131 | } |
||
1132 | |||
1133 | $orderby_clauses[] = $columns[$column_index] . ' ' |
||
1134 | . $sort[$column_index]; |
||
1135 | } // end for |
||
1136 | 8 | if (! empty($orderby_clauses)) { |
|
1137 | $orderby_clause = 'ORDER BY ' |
||
1138 | . htmlspecialchars(implode(', ', $orderby_clauses)) . "\n"; |
||
1139 | } |
||
1140 | |||
1141 | 8 | return $orderby_clause; |
|
1142 | } |
||
1143 | |||
1144 | /** |
||
1145 | * Provides UNIQUE columns and INDEX columns present in criteria tables |
||
1146 | * |
||
1147 | * @param array $search_tables Tables involved in the search |
||
1148 | * @param array $search_columns Columns involved in the search |
||
1149 | * @param array $where_clause_columns Columns having criteria where clause |
||
1150 | * |
||
1151 | * @return array having UNIQUE and INDEX columns |
||
1152 | */ |
||
1153 | 16 | private function getIndexes( |
|
1154 | array $search_tables, |
||
1155 | array $search_columns, |
||
1156 | array $where_clause_columns |
||
1157 | ) { |
||
1158 | 16 | $unique_columns = []; |
|
1159 | 16 | $index_columns = []; |
|
1160 | |||
1161 | 16 | foreach ($search_tables as $table) { |
|
1162 | 16 | $indexes = $this->dbi->getTableIndexes($this->_db, $table); |
|
1163 | 16 | foreach ($indexes as $index) { |
|
1164 | $column = $table . '.' . $index['Column_name']; |
||
1165 | if (! isset($search_columns[$column])) { |
||
1166 | continue; |
||
1167 | } |
||
1168 | |||
1169 | if ($index['Non_unique'] == 0) { |
||
1170 | if (isset($where_clause_columns[$column])) { |
||
1171 | $unique_columns[$column] = 'Y'; |
||
1172 | } else { |
||
1173 | $unique_columns[$column] = 'N'; |
||
1174 | } |
||
1175 | } else { |
||
1176 | if (isset($where_clause_columns[$column])) { |
||
1177 | $index_columns[$column] = 'Y'; |
||
1178 | } else { |
||
1179 | 8 | $index_columns[$column] = 'N'; |
|
1180 | } |
||
1181 | } |
||
1182 | } // end while (each index of a table) |
||
1183 | } // end while (each table) |
||
1184 | |||
1185 | return [ |
||
1186 | 16 | 'unique' => $unique_columns, |
|
1187 | 16 | 'index' => $index_columns, |
|
1188 | ]; |
||
1189 | } |
||
1190 | |||
1191 | /** |
||
1192 | * Provides UNIQUE columns and INDEX columns present in criteria tables |
||
1193 | * |
||
1194 | * @param array $search_tables Tables involved in the search |
||
1195 | * @param array $search_columns Columns involved in the search |
||
1196 | * @param array $where_clause_columns Columns having criteria where clause |
||
1197 | * |
||
1198 | * @return array having UNIQUE and INDEX columns |
||
1199 | */ |
||
1200 | 12 | private function getLeftJoinColumnCandidates( |
|
1201 | array $search_tables, |
||
1202 | array $search_columns, |
||
1203 | array $where_clause_columns |
||
1204 | ) { |
||
1205 | 12 | $this->dbi->selectDb($this->_db); |
|
1206 | |||
1207 | // Get unique columns and index columns |
||
1208 | 12 | $indexes = $this->getIndexes( |
|
1209 | 12 | $search_tables, |
|
1210 | 12 | $search_columns, |
|
1211 | 12 | $where_clause_columns |
|
1212 | ); |
||
1213 | 12 | $unique_columns = $indexes['unique']; |
|
1214 | 12 | $index_columns = $indexes['index']; |
|
1215 | |||
1216 | [$candidate_columns, $needsort] |
||
1217 | 12 | = $this->getLeftJoinColumnCandidatesBest( |
|
1218 | 12 | $search_tables, |
|
1219 | 9 | $where_clause_columns, |
|
1220 | 9 | $unique_columns, |
|
1221 | 9 | $index_columns |
|
1222 | ); |
||
1223 | |||
1224 | // If we came up with $unique_columns (very good) or $index_columns (still |
||
1225 | // good) as $candidate_columns we want to check if we have any 'Y' there |
||
1226 | // (that would mean that they were also found in the whereclauses |
||
1227 | // which would be great). if yes, we take only those |
||
1228 | 12 | if ($needsort != 1) { |
|
1229 | 12 | return $candidate_columns; |
|
1230 | } |
||
1231 | |||
1232 | $very_good = []; |
||
1233 | $still_good = []; |
||
1234 | foreach ($candidate_columns as $column => $is_where) { |
||
1235 | $table = explode('.', $column); |
||
1236 | $table = $table[0]; |
||
1237 | if ($is_where == 'Y') { |
||
1238 | $very_good[$column] = $table; |
||
1239 | } else { |
||
1240 | $still_good[$column] = $table; |
||
1241 | } |
||
1242 | } |
||
1243 | if (count($very_good) > 0) { |
||
1244 | $candidate_columns = $very_good; |
||
1245 | // Candidates restricted in index+where |
||
1246 | } else { |
||
1247 | $candidate_columns = $still_good; |
||
1248 | // None of the candidates where in a where-clause |
||
1249 | } |
||
1250 | |||
1251 | return $candidate_columns; |
||
1252 | } |
||
1253 | |||
1254 | /** |
||
1255 | * Provides the main table to form the LEFT JOIN clause |
||
1256 | * |
||
1257 | * @param array $search_tables Tables involved in the search |
||
1258 | * @param array $search_columns Columns involved in the search |
||
1259 | * @param array $where_clause_columns Columns having criteria where clause |
||
1260 | * @param array $where_clause_tables Tables having criteria where clause |
||
1261 | * |
||
1262 | * @return string table name |
||
1263 | */ |
||
1264 | 12 | private function getMasterTable( |
|
1265 | array $search_tables, |
||
1266 | array $search_columns, |
||
1267 | array $where_clause_columns, |
||
1268 | array $where_clause_tables |
||
1269 | ) { |
||
1270 | 12 | if (count($where_clause_tables) === 1) { |
|
1271 | // If there is exactly one column that has a decent where-clause |
||
1272 | // we will just use this |
||
1273 | 4 | return key($where_clause_tables); |
|
1274 | } |
||
1275 | |||
1276 | // Now let's find out which of the tables has an index |
||
1277 | // (When the control user is the same as the normal user |
||
1278 | // because they are using one of their databases as pmadb, |
||
1279 | // the last db selected is not always the one where we need to work) |
||
1280 | 8 | $candidate_columns = $this->getLeftJoinColumnCandidates( |
|
1281 | 8 | $search_tables, |
|
1282 | 8 | $search_columns, |
|
1283 | 8 | $where_clause_columns |
|
1284 | ); |
||
1285 | |||
1286 | // Generally, we need to display all the rows of foreign (referenced) |
||
1287 | // table, whether they have any matching row in child table or not. |
||
1288 | // So we select candidate tables which are foreign tables. |
||
1289 | 8 | $foreign_tables = []; |
|
1290 | 8 | foreach ($candidate_columns as $one_table) { |
|
1291 | 8 | $foreigners = $this->relation->getForeigners($this->_db, $one_table); |
|
1292 | 8 | foreach ($foreigners as $key => $foreigner) { |
|
1293 | 8 | if ($key != 'foreign_keys_data') { |
|
1294 | if (in_array($foreigner['foreign_table'], $candidate_columns)) { |
||
1295 | $foreign_tables[$foreigner['foreign_table']] |
||
1296 | = $foreigner['foreign_table']; |
||
1297 | } |
||
1298 | continue; |
||
1299 | } |
||
1300 | 8 | foreach ($foreigner as $one_key) { |
|
1301 | if (! in_array($one_key['ref_table_name'], $candidate_columns)) { |
||
1302 | continue; |
||
1303 | } |
||
1304 | |||
1305 | $foreign_tables[$one_key['ref_table_name']] |
||
1306 | 4 | = $one_key['ref_table_name']; |
|
1307 | } |
||
1308 | } |
||
1309 | } |
||
1310 | 8 | if (count($foreign_tables)) { |
|
1311 | $candidate_columns = $foreign_tables; |
||
1312 | } |
||
1313 | |||
1314 | // If our array of candidates has more than one member we'll just |
||
1315 | // find the smallest table. |
||
1316 | // Of course the actual query would be faster if we check for |
||
1317 | // the Criteria which gives the smallest result set in its table, |
||
1318 | // but it would take too much time to check this |
||
1319 | 8 | if (! (count($candidate_columns) > 1)) { |
|
1320 | // Only one single candidate |
||
1321 | 8 | return reset($candidate_columns); |
|
1322 | } |
||
1323 | |||
1324 | // Of course we only want to check each table once |
||
1325 | $checked_tables = $candidate_columns; |
||
1326 | $tsize = []; |
||
1327 | $maxsize = -1; |
||
1328 | $result = ''; |
||
1329 | foreach ($candidate_columns as $table) { |
||
1330 | if ($checked_tables[$table] != 1) { |
||
1331 | $_table = new Table($table, $this->_db); |
||
1332 | $tsize[$table] = $_table->countRecords(); |
||
1333 | $checked_tables[$table] = 1; |
||
1334 | } |
||
1335 | if ($tsize[$table] <= $maxsize) { |
||
1336 | continue; |
||
1337 | } |
||
1338 | |||
1339 | $maxsize = $tsize[$table]; |
||
1340 | $result = $table; |
||
1341 | } |
||
1342 | |||
1343 | // Return largest table |
||
1344 | return $result; |
||
1345 | } |
||
1346 | |||
1347 | /** |
||
1348 | * Provides columns and tables that have valid where clause criteria |
||
1349 | * |
||
1350 | * @return array |
||
1351 | */ |
||
1352 | 12 | private function getWhereClauseTablesAndColumns() |
|
1353 | { |
||
1354 | 12 | $where_clause_columns = []; |
|
1355 | 12 | $where_clause_tables = []; |
|
1356 | |||
1357 | // Now we need all tables that we have in the where clause |
||
1358 | 12 | for ($column_index = 0, $nb = count($this->_criteria); $column_index < $nb; $column_index++) { |
|
1359 | 12 | $current_table = explode('.', $_POST['criteriaColumn'][$column_index]); |
|
1360 | 12 | if (empty($current_table[0]) || empty($current_table[1])) { |
|
1361 | continue; |
||
1362 | } // end if |
||
1363 | 12 | $table = str_replace('`', '', $current_table[0]); |
|
1364 | 12 | $column = str_replace('`', '', $current_table[1]); |
|
1365 | 12 | $column = $table . '.' . $column; |
|
1366 | // Now we know that our array has the same numbers as $criteria |
||
1367 | // we can check which of our columns has a where clause |
||
1368 | 12 | if (! empty($this->_criteria[$column_index])) { |
|
1369 | if (mb_substr($this->_criteria[$column_index], 0, 1) == '=' |
||
1370 | || stripos($this->_criteria[$column_index], 'is') !== false |
||
1371 | ) { |
||
1372 | $where_clause_columns[$column] = $column; |
||
1373 | $where_clause_tables[$table] = $table; |
||
1374 | } |
||
1375 | } // end if |
||
1376 | } // end for |
||
1377 | |||
1378 | return [ |
||
1379 | 12 | 'where_clause_tables' => $where_clause_tables, |
|
1380 | 12 | 'where_clause_columns' => $where_clause_columns, |
|
1381 | ]; |
||
1382 | } |
||
1383 | |||
1384 | /** |
||
1385 | * Provides FROM clause for building SQL query |
||
1386 | * |
||
1387 | * @param array $formColumns List of selected columns in the form |
||
1388 | * |
||
1389 | * @return string FROM clause |
||
1390 | */ |
||
1391 | 8 | private function getFromClause(array $formColumns) |
|
1392 | { |
||
1393 | 8 | $from_clause = ''; |
|
1394 | 8 | if (empty($formColumns)) { |
|
1395 | return $from_clause; |
||
1396 | } |
||
1397 | |||
1398 | // Initialize some variables |
||
1399 | 8 | $search_tables = $search_columns = []; |
|
1400 | |||
1401 | // We only start this if we have fields, otherwise it would be dumb |
||
1402 | 8 | foreach ($formColumns as $value) { |
|
1403 | 8 | $parts = explode('.', $value); |
|
1404 | 8 | if (empty($parts[0]) || empty($parts[1])) { |
|
1405 | continue; |
||
1406 | } |
||
1407 | |||
1408 | 8 | $table = str_replace('`', '', $parts[0]); |
|
1409 | 8 | $search_tables[$table] = $table; |
|
1410 | 8 | $search_columns[] = $table . '.' . str_replace( |
|
1411 | 8 | '`', |
|
1412 | 8 | '', |
|
1413 | 8 | $parts[1] |
|
1414 | ); |
||
1415 | } // end while |
||
1416 | |||
1417 | // Create LEFT JOINS out of Relations |
||
1418 | 8 | $from_clause = $this->getJoinForFromClause( |
|
1419 | 8 | $search_tables, |
|
1420 | 6 | $search_columns |
|
1421 | ); |
||
1422 | |||
1423 | // In case relations are not defined, just generate the FROM clause |
||
1424 | // from the list of tables, however we don't generate any JOIN |
||
1425 | 8 | if (empty($from_clause)) { |
|
1426 | // Create cartesian product |
||
1427 | $from_clause = implode( |
||
1428 | ', ', |
||
1429 | array_map([Util::class, 'backquote'], $search_tables) |
||
1430 | ); |
||
1431 | } |
||
1432 | |||
1433 | 8 | return $from_clause; |
|
1434 | } |
||
1435 | |||
1436 | /** |
||
1437 | * Formulates the WHERE clause by JOINing tables |
||
1438 | * |
||
1439 | * @param array $searchTables Tables involved in the search |
||
1440 | * @param array $searchColumns Columns involved in the search |
||
1441 | * |
||
1442 | * @return string table name |
||
1443 | */ |
||
1444 | 8 | private function getJoinForFromClause(array $searchTables, array $searchColumns) |
|
1445 | { |
||
1446 | // $relations[master_table][foreign_table] => clause |
||
1447 | 8 | $relations = []; |
|
1448 | |||
1449 | // Fill $relations with inter table relationship data |
||
1450 | 8 | foreach ($searchTables as $oneTable) { |
|
1451 | 8 | $this->loadRelationsForTable($relations, $oneTable); |
|
1452 | } |
||
1453 | |||
1454 | // Get tables and columns with valid where clauses |
||
1455 | 8 | $validWhereClauses = $this->getWhereClauseTablesAndColumns(); |
|
1456 | 8 | $whereClauseTables = $validWhereClauses['where_clause_tables']; |
|
1457 | 8 | $whereClauseColumns = $validWhereClauses['where_clause_columns']; |
|
1458 | |||
1459 | // Get master table |
||
1460 | 8 | $master = $this->getMasterTable( |
|
1461 | 8 | $searchTables, |
|
1462 | 8 | $searchColumns, |
|
1463 | 8 | $whereClauseColumns, |
|
1464 | 8 | $whereClauseTables |
|
1465 | ); |
||
1466 | |||
1467 | // Will include master tables and all tables that can be combined into |
||
1468 | // a cluster by their relation |
||
1469 | 8 | $finalized = []; |
|
1470 | 8 | if (strlen((string) $master) > 0) { |
|
1471 | // Add master tables |
||
1472 | 8 | $finalized[$master] = ''; |
|
1473 | } |
||
1474 | // Fill the $finalized array with JOIN clauses for each table |
||
1475 | 8 | $this->fillJoinClauses($finalized, $relations, $searchTables); |
|
1476 | |||
1477 | // JOIN clause |
||
1478 | 8 | $join = ''; |
|
1479 | |||
1480 | // Tables that can not be combined with the table cluster |
||
1481 | // which includes master table |
||
1482 | 8 | $unfinalized = array_diff($searchTables, array_keys($finalized)); |
|
1483 | 8 | if (count($unfinalized) > 0) { |
|
1484 | // We need to look for intermediary tables to JOIN unfinalized tables |
||
1485 | // Heuristic to chose intermediary tables is to look for tables |
||
1486 | // having relationships with unfinalized tables |
||
1487 | foreach ($unfinalized as $oneTable) { |
||
1488 | $references = $this->relation->getChildReferences($this->_db, $oneTable); |
||
1489 | foreach ($references as $column => $columnReferences) { |
||
1490 | foreach ($columnReferences as $reference) { |
||
1491 | // Only from this schema |
||
1492 | if ($reference['table_schema'] != $this->_db) { |
||
1493 | continue; |
||
1494 | } |
||
1495 | |||
1496 | $table = $reference['table_name']; |
||
1497 | |||
1498 | $this->loadRelationsForTable($relations, $table); |
||
1499 | |||
1500 | // Make copies |
||
1501 | $tempFinalized = $finalized; |
||
1502 | $tempSearchTables = $searchTables; |
||
1503 | $tempSearchTables[] = $table; |
||
1504 | |||
1505 | // Try joining with the added table |
||
1506 | $this->fillJoinClauses( |
||
1507 | $tempFinalized, |
||
1508 | $relations, |
||
1509 | $tempSearchTables |
||
1510 | ); |
||
1511 | |||
1512 | $tempUnfinalized = array_diff( |
||
1513 | $tempSearchTables, |
||
1514 | array_keys($tempFinalized) |
||
1515 | ); |
||
1516 | // Take greedy approach. |
||
1517 | // If the unfinalized count drops we keep the new table |
||
1518 | // and switch temporary varibles with the original ones |
||
1519 | if (count($tempUnfinalized) < count($unfinalized)) { |
||
1520 | $finalized = $tempFinalized; |
||
1521 | $searchTables = $tempSearchTables; |
||
1522 | } |
||
1523 | |||
1524 | // We are done if no unfinalized tables anymore |
||
1525 | if (count($tempUnfinalized) === 0) { |
||
1526 | break 3; |
||
1527 | } |
||
1528 | } |
||
1529 | } |
||
1530 | } |
||
1531 | |||
1532 | $unfinalized = array_diff($searchTables, array_keys($finalized)); |
||
1533 | // If there are still unfinalized tables |
||
1534 | if (count($unfinalized) > 0) { |
||
1535 | // Add these tables as cartesian product before joined tables |
||
1536 | $join .= implode( |
||
1537 | ', ', |
||
1538 | array_map([Util::class, 'backquote'], $unfinalized) |
||
1539 | ); |
||
1540 | } |
||
1541 | } |
||
1542 | |||
1543 | 8 | $first = true; |
|
1544 | // Add joined tables |
||
1545 | 8 | foreach ($finalized as $table => $clause) { |
|
1546 | 8 | if ($first) { |
|
1547 | 8 | if (! empty($join)) { |
|
1548 | $join .= ', '; |
||
1549 | } |
||
1550 | 8 | $join .= Util::backquote($table); |
|
1551 | 8 | $first = false; |
|
1552 | } else { |
||
1553 | $join .= "\n LEFT JOIN " . Util::backquote( |
||
1554 | $table |
||
1555 | 4 | ) . ' ON ' . $clause; |
|
1556 | } |
||
1557 | } |
||
1558 | |||
1559 | 8 | return $join; |
|
1560 | } |
||
1561 | |||
1562 | /** |
||
1563 | * Loads relations for a given table into the $relations array |
||
1564 | * |
||
1565 | * @param array $relations array of relations |
||
1566 | * @param string $oneTable the table |
||
1567 | * |
||
1568 | * @return void |
||
1569 | */ |
||
1570 | 8 | private function loadRelationsForTable(array &$relations, $oneTable) |
|
1571 | { |
||
1572 | 8 | $relations[$oneTable] = []; |
|
1573 | |||
1574 | 8 | $foreigners = $this->relation->getForeigners($GLOBALS['db'], $oneTable); |
|
1575 | 8 | foreach ($foreigners as $field => $foreigner) { |
|
1576 | // Foreign keys data |
||
1577 | 8 | if ($field == 'foreign_keys_data') { |
|
1578 | 8 | foreach ($foreigner as $oneKey) { |
|
1579 | $clauses = []; |
||
1580 | // There may be multiple column relations |
||
1581 | foreach ($oneKey['index_list'] as $index => $oneField) { |
||
1582 | $clauses[] |
||
1583 | = Util::backquote($oneTable) . '.' |
||
1584 | . Util::backquote($oneField) . ' = ' |
||
1585 | . Util::backquote($oneKey['ref_table_name']) . '.' |
||
1586 | . Util::backquote($oneKey['ref_index_list'][$index]); |
||
1587 | } |
||
1588 | // Combine multiple column relations with AND |
||
1589 | $relations[$oneTable][$oneKey['ref_table_name']] |
||
1590 | 4 | = implode(' AND ', $clauses); |
|
1591 | } |
||
1592 | } else { // Internal relations |
||
1593 | $relations[$oneTable][$foreigner['foreign_table']] |
||
1594 | = Util::backquote($oneTable) . '.' |
||
1595 | . Util::backquote($field) . ' = ' |
||
0 ignored issues
–
show
Bug
introduced
by
Loading history...
|
|||
1596 | . Util::backquote($foreigner['foreign_table']) . '.' |
||
1597 | 4 | . Util::backquote($foreigner['foreign_field']); |
|
1598 | } |
||
1599 | } |
||
1600 | 8 | } |
|
1601 | |||
1602 | /** |
||
1603 | * Fills the $finalized arrays with JOIN clauses for each of the tables |
||
1604 | * |
||
1605 | * @param array $finalized JOIN clauses for each table |
||
1606 | * @param array $relations Relations among tables |
||
1607 | * @param array $searchTables Tables involved in the search |
||
1608 | * |
||
1609 | * @return void |
||
1610 | */ |
||
1611 | 8 | private function fillJoinClauses(array &$finalized, array $relations, array $searchTables) |
|
1612 | { |
||
1613 | 8 | while (true) { |
|
1614 | 8 | $added = false; |
|
1615 | 8 | foreach ($searchTables as $masterTable) { |
|
1616 | 8 | $foreignData = $relations[$masterTable]; |
|
1617 | 8 | foreach ($foreignData as $foreignTable => $clause) { |
|
1618 | if (! isset($finalized[$masterTable]) |
||
1619 | && isset($finalized[$foreignTable]) |
||
1620 | ) { |
||
1621 | $finalized[$masterTable] = $clause; |
||
1622 | $added = true; |
||
1623 | } elseif (! isset($finalized[$foreignTable]) |
||
1624 | && isset($finalized[$masterTable]) |
||
1625 | && in_array($foreignTable, $searchTables) |
||
1626 | ) { |
||
1627 | $finalized[$foreignTable] = $clause; |
||
1628 | $added = true; |
||
1629 | } |
||
1630 | if (! $added) { |
||
1631 | continue; |
||
1632 | } |
||
1633 | |||
1634 | // We are done if all tables are in $finalized |
||
1635 | if (count($finalized) == count($searchTables)) { |
||
1636 | 4 | return; |
|
1637 | } |
||
1638 | } |
||
1639 | } |
||
1640 | // If no new tables were added during this iteration, break; |
||
1641 | 8 | if (! $added) { |
|
1642 | 8 | return; |
|
1643 | } |
||
1644 | } |
||
1645 | } |
||
1646 | |||
1647 | /** |
||
1648 | * Provides the generated SQL query |
||
1649 | * |
||
1650 | * @param array $formColumns List of selected columns in the form |
||
1651 | * |
||
1652 | * @return string SQL query |
||
1653 | */ |
||
1654 | 4 | private function getSQLQuery(array $formColumns) |
|
1655 | { |
||
1656 | 4 | $sql_query = ''; |
|
1657 | // get SELECT clause |
||
1658 | 4 | $sql_query .= $this->getSelectClause(); |
|
1659 | // get FROM clause |
||
1660 | 4 | $from_clause = $this->getFromClause($formColumns); |
|
1661 | 4 | if (! empty($from_clause)) { |
|
1662 | 4 | $sql_query .= 'FROM ' . htmlspecialchars($from_clause) . "\n"; |
|
1663 | } |
||
1664 | // get WHERE clause |
||
1665 | 4 | $sql_query .= $this->getWhereClause(); |
|
1666 | // get ORDER BY clause |
||
1667 | 4 | $sql_query .= $this->getOrderByClause(); |
|
1668 | |||
1669 | 4 | return $sql_query; |
|
1670 | } |
||
1671 | |||
1672 | public function getSelectionForm(): string |
||
1673 | { |
||
1674 | global $cfgRelation; |
||
1675 | |||
1676 | $savedSearchesField = $cfgRelation['savedsearcheswork'] ? $this->getSavedSearchesField() : ''; |
||
1677 | |||
1678 | $columnNamesRow = $this->getColumnNamesRow(); |
||
1679 | $columnAliasRow = $this->getColumnAliasRow(); |
||
1680 | $showRow = $this->getShowRow(); |
||
1681 | $sortRow = $this->getSortRow(); |
||
1682 | $sortOrder = $this->getSortOrder(); |
||
1683 | $criteriaInputBoxRow = $this->getCriteriaInputboxRow(); |
||
1684 | $insDelAndOrCriteriaRows = $this->getInsDelAndOrCriteriaRows(); |
||
1685 | $modifyColumnsRow = $this->getModifyColumnsRow(); |
||
1686 | |||
1687 | $this->_new_row_count--; |
||
1688 | $url_params = []; |
||
1689 | $url_params['db'] = $this->_db; |
||
1690 | $url_params['criteriaColumnCount'] = $this->_new_column_count; |
||
1691 | $url_params['rows'] = $this->_new_row_count; |
||
1692 | |||
1693 | if (empty($this->_formColumns)) { |
||
1694 | $this->_formColumns = []; |
||
1695 | } |
||
1696 | $sqlQuery = $this->getSQLQuery($this->_formColumns); |
||
1697 | |||
1698 | return $this->template->render('database/qbe/selection_form', [ |
||
1699 | 'db' => $this->_db, |
||
1700 | 'url_params' => $url_params, |
||
1701 | 'db_link' => Generator::getDbLink($this->_db), |
||
1702 | 'criteria_tables' => $this->_criteriaTables, |
||
1703 | 'saved_searches_field' => $savedSearchesField, |
||
1704 | 'column_names_row' => $columnNamesRow, |
||
1705 | 'column_alias_row' => $columnAliasRow, |
||
1706 | 'show_row' => $showRow, |
||
1707 | 'sort_row' => $sortRow, |
||
1708 | 'sort_order' => $sortOrder, |
||
1709 | 'criteria_input_box_row' => $criteriaInputBoxRow, |
||
1710 | 'ins_del_and_or_criteria_rows' => $insDelAndOrCriteriaRows, |
||
1711 | 'modify_columns_row' => $modifyColumnsRow, |
||
1712 | 'sql_query' => $sqlQuery, |
||
1713 | ]); |
||
1714 | } |
||
1715 | |||
1716 | /** |
||
1717 | * Get fields to display |
||
1718 | * |
||
1719 | * @return string |
||
1720 | */ |
||
1721 | private function getSavedSearchesField() |
||
1722 | { |
||
1723 | $html_output = __('Saved bookmarked search:'); |
||
1724 | $html_output .= ' <select name="searchId" id="searchId">'; |
||
1725 | $html_output .= '<option value="">' . __('New bookmark') . '</option>'; |
||
1726 | |||
1727 | $currentSearch = $this->getCurrentSearch(); |
||
1728 | $currentSearchId = null; |
||
1729 | $currentSearchName = null; |
||
1730 | if ($currentSearch != null) { |
||
1731 | $currentSearchId = $currentSearch->getId(); |
||
1732 | $currentSearchName = $currentSearch->getSearchName(); |
||
1733 | } |
||
1734 | |||
1735 | foreach ($this->_savedSearchList as $id => $name) { |
||
1736 | $html_output .= '<option value="' . htmlspecialchars($id) |
||
1737 | . '" ' . ( |
||
1738 | $id == $currentSearchId |
||
1739 | ? 'selected="selected" ' |
||
1740 | : '' |
||
1741 | ) |
||
1742 | . '>' |
||
1743 | . htmlspecialchars($name) |
||
1744 | . '</option>'; |
||
1745 | } |
||
1746 | $html_output .= '</select>'; |
||
1747 | $html_output .= '<input type="text" name="searchName" id="searchName" ' |
||
1748 | . 'value="' . htmlspecialchars((string) $currentSearchName) . '">'; |
||
1749 | $html_output .= '<input type="hidden" name="action" id="action" value="">'; |
||
1750 | $html_output .= '<input class="btn btn-secondary" type="submit" name="saveSearch" id="saveSearch" ' |
||
1751 | . 'value="' . __('Create bookmark') . '">'; |
||
1752 | if ($currentSearchId !== null) { |
||
1753 | $html_output .= '<input class="btn btn-secondary" type="submit" name="updateSearch" ' |
||
1754 | . 'id="updateSearch" value="' . __('Update bookmark') . '">'; |
||
1755 | $html_output .= '<input class="btn btn-secondary" type="submit" name="deleteSearch" ' |
||
1756 | . 'id="deleteSearch" value="' . __('Delete bookmark') . '">'; |
||
1757 | } |
||
1758 | |||
1759 | return $html_output; |
||
1760 | } |
||
1761 | |||
1762 | /** |
||
1763 | * Initialize _criteria_column_count |
||
1764 | * |
||
1765 | * @return int Previous number of columns |
||
1766 | */ |
||
1767 | 68 | private function initializeCriteriasCount(): int |
|
1768 | { |
||
1769 | // sets column count |
||
1770 | 68 | $criteriaColumnCount = Core::ifSetOr( |
|
1771 | 68 | $_POST['criteriaColumnCount'], |
|
1772 | 68 | 3, |
|
1773 | 68 | 'numeric' |
|
1774 | ); |
||
1775 | 68 | $criteriaColumnAdd = Core::ifSetOr( |
|
1776 | 68 | $_POST['criteriaColumnAdd'], |
|
1777 | 68 | 0, |
|
1778 | 68 | 'numeric' |
|
1779 | ); |
||
1780 | 68 | $this->_criteria_column_count = max( |
|
1781 | 68 | $criteriaColumnCount + $criteriaColumnAdd, |
|
1782 | 0 |
||
1783 | ); |
||
1784 | |||
1785 | // sets row count |
||
1786 | 68 | $rows = Core::ifSetOr($_POST['rows'], 0, 'numeric'); |
|
1787 | 68 | $criteriaRowAdd = Core::ifSetOr($_POST['criteriaRowAdd'], 0, 'numeric'); |
|
1788 | 68 | $this->_criteria_row_count = min( |
|
1789 | 68 | 100, |
|
1790 | 68 | max($rows + $criteriaRowAdd, 0) |
|
1791 | ); |
||
1792 | |||
1793 | 68 | return (int) $criteriaColumnCount; |
|
1794 | } |
||
1795 | |||
1796 | /** |
||
1797 | * Get best |
||
1798 | * |
||
1799 | * @param array $search_tables Tables involved in the search |
||
1800 | * @param array|null $where_clause_columns Columns with where clause |
||
1801 | * @param array|null $unique_columns Unique columns |
||
1802 | * @param array|null $index_columns Indexed columns |
||
1803 | * |
||
1804 | * @return array |
||
1805 | */ |
||
1806 | 12 | private function getLeftJoinColumnCandidatesBest( |
|
1807 | array $search_tables, |
||
1808 | ?array $where_clause_columns, |
||
1809 | ?array $unique_columns, |
||
1810 | ?array $index_columns |
||
1811 | ) { |
||
1812 | // now we want to find the best. |
||
1813 | 12 | if (isset($unique_columns) && count($unique_columns) > 0) { |
|
1814 | $candidate_columns = $unique_columns; |
||
1815 | $needsort = 1; |
||
1816 | |||
1817 | return [ |
||
1818 | $candidate_columns, |
||
1819 | $needsort, |
||
1820 | ]; |
||
1821 | } |
||
1822 | |||
1823 | 12 | if (isset($index_columns) && count($index_columns) > 0) { |
|
1824 | $candidate_columns = $index_columns; |
||
1825 | $needsort = 1; |
||
1826 | |||
1827 | return [ |
||
1828 | $candidate_columns, |
||
1829 | $needsort, |
||
1830 | ]; |
||
1831 | } |
||
1832 | |||
1833 | 12 | if (isset($where_clause_columns) && count($where_clause_columns) > 0) { |
|
1834 | 4 | $candidate_columns = $where_clause_columns; |
|
1835 | 4 | $needsort = 0; |
|
1836 | |||
1837 | return [ |
||
1838 | 4 | $candidate_columns, |
|
1839 | 4 | $needsort, |
|
1840 | ]; |
||
1841 | } |
||
1842 | |||
1843 | 8 | $candidate_columns = $search_tables; |
|
1844 | 8 | $needsort = 0; |
|
1845 | |||
1846 | return [ |
||
1847 | 8 | $candidate_columns, |
|
1848 | 8 | $needsort, |
|
1849 | ]; |
||
1850 | } |
||
1851 | } |
||
1852 |