222 lines
8.4 KiB
PHP
222 lines
8.4 KiB
PHP
<?php
|
|
|
|
class Application_Model_Datatables
|
|
{
|
|
private static function buildWhereClauseForAdvancedSearch($dbname2searchTerm)
|
|
{
|
|
$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 {
|
|
$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;
|
|
}
|
|
} 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 {
|
|
$input1 = isset($info[0]) ? $info[0] : null;
|
|
$input2 = isset($info[1]) ? $info[1] : null;
|
|
}
|
|
$isRange = true;
|
|
} else {
|
|
$input1 = $term;
|
|
}
|
|
|
|
if ($isRange) {
|
|
$sub = [];
|
|
if ($input1 != null) {
|
|
$sub[] = $dbname . ' >= :' . $dbname . '1';
|
|
}
|
|
if ($input2 != null) {
|
|
$sub[] = $dbname . ' <= :' . $dbname . '2';
|
|
}
|
|
if (!empty($sub)) {
|
|
$where['clause'][$dbname] = '(' . implode(' AND ', $sub) . ')';
|
|
if ($input1 != null) {
|
|
$where['params'][$dbname . '1'] = $input1;
|
|
}
|
|
if ($input2 != null) {
|
|
$where['params'][$dbname . '2'] = $input2;
|
|
}
|
|
}
|
|
} else {
|
|
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;
|
|
}
|
|
|
|
// query used to return data for a paginated/searchable datatable.
|
|
public static function findEntries(
|
|
$con,
|
|
$displayColumns,
|
|
$fromTable,
|
|
$data,
|
|
$dataProp = 'aaData'
|
|
) {
|
|
$where = [];
|
|
/* Holds the parameters for binding after the statement has been
|
|
prepared */
|
|
$params = [];
|
|
|
|
if (isset($data['advSearch']) && $data['advSearch'] === 'true') {
|
|
$librarySetting = Application_Model_Preference::getCurrentLibraryTableColumnMap();
|
|
// $displayColumns[] = 'owner';
|
|
|
|
// map that maps original column position to db name
|
|
$current2dbname = [];
|
|
// array of search terms
|
|
$orig2searchTerm = [];
|
|
foreach ($data as $key => $d) {
|
|
if (strstr($key, 'mDataProp_')) {
|
|
[$dump, $index] = explode('_', $key);
|
|
$current2dbname[$index] = $d;
|
|
} elseif (strstr($key, 'sSearch_')) {
|
|
[$dump, $index] = explode('_', $key);
|
|
$orig2searchTerm[$index] = $d;
|
|
}
|
|
}
|
|
|
|
// map that maps dbname to searchTerm
|
|
$dbname2searchTerm = [];
|
|
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
|
|
if (array_key_exists($new_index, $orig2searchTerm)) {
|
|
$dbname2searchTerm[$dbname] = $orig2searchTerm[$new_index];
|
|
} else {
|
|
Logging::warn('Trying to reorder to unknown index
|
|
printing as much debugging as possible...');
|
|
$debug = [
|
|
'$new_index' => $new_index,
|
|
'$currentPos' => $currentPos,
|
|
'$orig2searchTerm' => $orig2searchTerm,
|
|
];
|
|
Logging::warn($debug);
|
|
}
|
|
}
|
|
|
|
$advancedWhere = self::buildWhereClauseForAdvancedSearch($dbname2searchTerm);
|
|
if (!empty($advancedWhere['clause'])) {
|
|
$where[] = implode(' AND ', $advancedWhere['clause']);
|
|
$params = $advancedWhere['params'];
|
|
}
|
|
}
|
|
|
|
if ($data['sSearch'] !== '') {
|
|
$searchTerms = explode(' ', $data['sSearch']);
|
|
}
|
|
|
|
$selectorCount = 'SELECT COUNT(*) ';
|
|
$selectorRows = 'SELECT ' . implode(',', $displayColumns) . ' ';
|
|
|
|
$sql = $selectorCount . ' FROM ' . $fromTable;
|
|
$sqlTotalRows = $sql;
|
|
|
|
if (isset($searchTerms)) {
|
|
$searchCols = [];
|
|
for ($i = 0; $i < $data['iColumns']; ++$i) {
|
|
if ($data['bSearchable_' . $i] == 'true') {
|
|
$searchCols[] = $data["mDataProp_{$i}"];
|
|
}
|
|
}
|
|
|
|
$outerCond = [];
|
|
$simpleWhere = [];
|
|
|
|
foreach ($searchTerms as $term) {
|
|
foreach ($searchCols as $col) {
|
|
$simpleWhere['clause']['simple_' . $col] = "{$col}::text ILIKE :simple_" . $col;
|
|
$simpleWhere['params']['simple_' . $col] = '%' . $term . '%';
|
|
}
|
|
$outerCond[] = '(' . implode(' OR ', $simpleWhere['clause']) . ')';
|
|
}
|
|
$where[] = '(' . implode(' AND ', $outerCond) . ')';
|
|
$params = array_merge($params, $simpleWhere['params']);
|
|
}
|
|
// End Where clause
|
|
|
|
// Order By clause
|
|
$orderby = [];
|
|
for ($i = 0; $i < $data['iSortingCols']; ++$i) {
|
|
$num = $data['iSortCol_' . $i];
|
|
$orderby[] = $data["mDataProp_{$num}"] . ' ' . $data['sSortDir_' . $i];
|
|
}
|
|
$orderby[] = 'id';
|
|
$orderby = implode(',', $orderby);
|
|
// End Order By clause
|
|
|
|
$displayLength = intval($data['iDisplayLength']);
|
|
$needToBind = false;
|
|
if (count($where) > 0) {
|
|
$needToBind = true;
|
|
$where = implode(' OR ', $where);
|
|
$sql = $selectorCount . ' FROM ' . $fromTable . ' WHERE ' . $where;
|
|
$sqlTotalDisplayRows = $sql;
|
|
|
|
$sql = $selectorRows . ' FROM ' . $fromTable . ' WHERE ' . $where . ' ORDER BY ' . $orderby;
|
|
} else {
|
|
$sql = $selectorRows . ' FROM ' . $fromTable . ' ORDER BY ' . $orderby;
|
|
}
|
|
|
|
// limit the results returned.
|
|
if ($displayLength !== -1) {
|
|
$sql .= ' OFFSET ' . $data['iDisplayStart'] . ' LIMIT ' . $displayLength;
|
|
}
|
|
|
|
try {
|
|
// Logging::info($sqlTotalRows);
|
|
|
|
$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');
|
|
} else {
|
|
// Logging::info("sql is not set.");
|
|
$totalDisplayRows = $totalRows;
|
|
}
|
|
|
|
// TODO
|
|
if ($needToBind) {
|
|
$results = Application_Common_Database::prepareAndExecute($sql, $params);
|
|
} else {
|
|
$stmt = $con->query($sql);
|
|
$stmt->setFetchMode(PDO::FETCH_ASSOC);
|
|
$results = $stmt->fetchAll();
|
|
}
|
|
} catch (Exception $e) {
|
|
Logging::info($e->getMessage());
|
|
}
|
|
|
|
return [
|
|
'sEcho' => intval($data['sEcho']),
|
|
'iTotalDisplayRecords' => intval($totalDisplayRows),
|
|
'iTotalRecords' => intval($totalRows),
|
|
$dataProp => $results,
|
|
];
|
|
}
|
|
}
|