libretime/legacy/application/models/Datatables.php

222 lines
8.4 KiB
PHP
Raw Permalink Normal View History

<?php
class Application_Model_Datatables
{
private static function buildWhereClauseForAdvancedSearch($dbname2searchTerm)
{
2021-10-11 16:10:47 +02:00
$where = [];
$where['clause'] = [];
$where['params'] = [];
foreach ($dbname2searchTerm as $dbname => $term) {
$isRange = false;
if (strstr($term, '~')) {
$info = explode('~', $term);
if ($dbname == 'utime' || $dbname == 'mtime' || $dbname == 'lptime') {
try {
2021-10-11 16:10:47 +02:00
$input1 = ($info[0] != '') ? Application_Common_DateHelper::UserTimezoneStringToUTCString($info[0]) : null;
$input2 = ($info[1] != '') ? Application_Common_DateHelper::UserTimezoneStringToUTCString($info[1]) : null;
} catch (Exception $e) {
$input1 = null;
$input2 = null;
}
2021-10-11 16:10:47 +02:00
} elseif ($dbname == 'bit_rate' || $dbname == 'sample_rate') {
$input1 = isset($info[0]) ? floatval($info[0]) * 1000 : null;
$input2 = isset($info[1]) ? floatval($info[1]) * 1000 : null;
} else {
2021-10-11 16:10:47 +02:00
$input1 = isset($info[0]) ? $info[0] : null;
$input2 = isset($info[1]) ? $info[1] : null;
}
$isRange = true;
} else {
$input1 = $term;
}
if ($isRange) {
2021-10-11 16:10:47 +02:00
$sub = [];
if ($input1 != null) {
2021-10-11 16:10:47 +02:00
$sub[] = $dbname . ' >= :' . $dbname . '1';
}
if ($input2 != null) {
2021-10-11 16:10:47 +02:00
$sub[] = $dbname . ' <= :' . $dbname . '2';
}
if (!empty($sub)) {
2021-10-11 16:10:47 +02:00
$where['clause'][$dbname] = '(' . implode(' AND ', $sub) . ')';
if ($input1 != null) {
2021-10-11 16:10:47 +02:00
$where['params'][$dbname . '1'] = $input1;
}
if ($input2 != null) {
2021-10-11 16:10:47 +02:00
$where['params'][$dbname . '2'] = $input2;
}
}
} else {
2021-10-11 16:10:47 +02:00
if (trim($input1) !== '') {
if ($dbname == 'track_type_id') {
$where['clause'][$dbname] = $dbname . ' = :' . $dbname . '1';
$where['params'][$dbname . '1'] = $input1;
} else {
$where['clause'][$dbname] = $dbname . ' ILIKE :' . $dbname . '1';
$where['params'][$dbname . '1'] = '%' . $input1 . '%';
}
}
}
}
return $where;
}
2021-10-11 16:10:47 +02:00
// query used to return data for a paginated/searchable datatable.
public static function findEntries(
$con,
$displayColumns,
$fromTable,
$data,
$dataProp = 'aaData'
) {
$where = [];
2012-09-19 22:34:46 +02:00
/* Holds the parameters for binding after the statement has been
prepared */
2021-10-11 16:10:47 +02:00
$params = [];
2012-09-19 23:05:20 +02:00
if (isset($data['advSearch']) && $data['advSearch'] === 'true') {
$librarySetting = Application_Model_Preference::getCurrentLibraryTableColumnMap();
// $displayColumns[] = 'owner';
2013-07-18 07:37:39 +02:00
// map that maps original column position to db name
2021-10-11 16:10:47 +02:00
$current2dbname = [];
2013-07-18 07:37:39 +02:00
// array of search terms
2021-10-11 16:10:47 +02:00
$orig2searchTerm = [];
2013-07-18 07:37:39 +02:00
foreach ($data as $key => $d) {
2021-10-11 16:10:47 +02:00
if (strstr($key, 'mDataProp_')) {
[$dump, $index] = explode('_', $key);
2013-07-18 07:37:39 +02:00
$current2dbname[$index] = $d;
2021-10-11 16:10:47 +02:00
} elseif (strstr($key, 'sSearch_')) {
[$dump, $index] = explode('_', $key);
2013-07-18 07:37:39 +02:00
$orig2searchTerm[$index] = $d;
}
}
// map that maps dbname to searchTerm
2021-10-11 16:10:47 +02:00
$dbname2searchTerm = [];
2013-07-18 07:37:39 +02:00
foreach ($current2dbname as $currentPos => $dbname) {
$new_index = $librarySetting($currentPos);
// TODO : Fix this retarded hack later. Just a band aid for
// now at least we print some warnings so that we don't
// forget about this -- cc-4462
2021-10-11 16:10:47 +02:00
if (array_key_exists($new_index, $orig2searchTerm)) {
2013-07-18 07:37:39 +02:00
$dbname2searchTerm[$dbname] = $orig2searchTerm[$new_index];
} else {
2021-10-11 16:10:47 +02:00
Logging::warn('Trying to reorder to unknown index
printing as much debugging as possible...');
$debug = [
'$new_index' => $new_index,
'$currentPos' => $currentPos,
2022-07-07 20:01:15 +02:00
'$orig2searchTerm' => $orig2searchTerm,
];
2013-07-18 07:37:39 +02:00
Logging::warn($debug);
}
2013-07-18 07:31:20 +02:00
}
$advancedWhere = self::buildWhereClauseForAdvancedSearch($dbname2searchTerm);
if (!empty($advancedWhere['clause'])) {
$where[] = implode(' AND ', $advancedWhere['clause']);
$params = $advancedWhere['params'];
}
}
2021-10-11 16:10:47 +02:00
if ($data['sSearch'] !== '') {
$searchTerms = explode(' ', $data['sSearch']);
}
2021-10-11 16:10:47 +02:00
$selectorCount = 'SELECT COUNT(*) ';
$selectorRows = 'SELECT ' . implode(',', $displayColumns) . ' ';
2021-10-11 16:10:47 +02:00
$sql = $selectorCount . ' FROM ' . $fromTable;
$sqlTotalRows = $sql;
if (isset($searchTerms)) {
2021-10-11 16:10:47 +02:00
$searchCols = [];
for ($i = 0; $i < $data['iColumns']; ++$i) {
if ($data['bSearchable_' . $i] == 'true') {
$searchCols[] = $data["mDataProp_{$i}"];
}
}
2021-10-11 16:10:47 +02:00
$outerCond = [];
$simpleWhere = [];
foreach ($searchTerms as $term) {
foreach ($searchCols as $col) {
2021-10-11 16:10:47 +02:00
$simpleWhere['clause']['simple_' . $col] = "{$col}::text ILIKE :simple_" . $col;
$simpleWhere['params']['simple_' . $col] = '%' . $term . '%';
}
2021-10-11 16:10:47 +02:00
$outerCond[] = '(' . implode(' OR ', $simpleWhere['clause']) . ')';
}
2021-10-11 16:10:47 +02:00
$where[] = '(' . implode(' AND ', $outerCond) . ')';
$params = array_merge($params, $simpleWhere['params']);
}
// End Where clause
// Order By clause
2021-10-11 16:10:47 +02:00
$orderby = [];
for ($i = 0; $i < $data['iSortingCols']; ++$i) {
$num = $data['iSortCol_' . $i];
$orderby[] = $data["mDataProp_{$num}"] . ' ' . $data['sSortDir_' . $i];
}
2021-10-11 16:10:47 +02:00
$orderby[] = 'id';
$orderby = implode(',', $orderby);
// End Order By clause
2021-10-11 16:10:47 +02:00
$displayLength = intval($data['iDisplayLength']);
$needToBind = false;
if (count($where) > 0) {
$needToBind = true;
$where = implode(' OR ', $where);
2021-10-11 16:10:47 +02:00
$sql = $selectorCount . ' FROM ' . $fromTable . ' WHERE ' . $where;
$sqlTotalDisplayRows = $sql;
2021-10-11 16:10:47 +02:00
$sql = $selectorRows . ' FROM ' . $fromTable . ' WHERE ' . $where . ' ORDER BY ' . $orderby;
} else {
$sql = $selectorRows . ' FROM ' . $fromTable . ' ORDER BY ' . $orderby;
2013-07-18 07:31:20 +02:00
}
// limit the results returned.
2013-07-18 07:37:39 +02:00
if ($displayLength !== -1) {
2021-10-11 16:10:47 +02:00
$sql .= ' OFFSET ' . $data['iDisplayStart'] . ' LIMIT ' . $displayLength;
}
2013-07-18 07:31:20 +02:00
try {
// Logging::info($sqlTotalRows);
2013-07-18 07:31:20 +02:00
$r = $con->query($sqlTotalRows);
$totalRows = $r->fetchColumn(0);
if (isset($sqlTotalDisplayRows)) {
// Logging::info("sql is set");
// Logging::info($sqlTotalDisplayRows);
$totalDisplayRows = Application_Common_Database::prepareAndExecute($sqlTotalDisplayRows, $params, 'column');
2021-10-11 16:10:47 +02:00
} else {
// Logging::info("sql is not set.");
$totalDisplayRows = $totalRows;
}
// TODO
if ($needToBind) {
$results = Application_Common_Database::prepareAndExecute($sql, $params);
2021-10-11 16:10:47 +02:00
} else {
$stmt = $con->query($sql);
$stmt->setFetchMode(PDO::FETCH_ASSOC);
$results = $stmt->fetchAll();
}
2021-10-11 16:10:47 +02:00
} catch (Exception $e) {
2013-07-18 07:31:20 +02:00
Logging::info($e->getMessage());
}
2021-10-11 16:10:47 +02:00
return [
'sEcho' => intval($data['sEcho']),
'iTotalDisplayRecords' => intval($totalDisplayRows),
'iTotalRecords' => intval($totalRows),
$dataProp => $results,
];
}
2012-04-10 14:45:48 +02:00
}