$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, ]; } }