public static function findEntries($con, $displayColumns, $fromTable, $data, $dataProp = "aaData") { $librarySetting = Application_Model_Preference::getCurrentLibraryTableColumnMap(); //$displayColumns[] = 'owner'; // map that maps original column position to db name $current2dbname = array(); // array of search terms $orig2searchTerm = array(); foreach ($data as $key => $d) { if (strstr($key, "mDataProp_")) { list($dump, $index) = explode("_", $key); $current2dbname[$index] = $d; } elseif (strstr($key, "sSearch_")) { list($dump, $index) = explode("_", $key); $orig2searchTerm[$index] = $d; } } // map that maps dbname to searchTerm $dbname2searchTerm = array(); 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\n printing as much debugging as possible..."); $debug = array('$new_index' => $new_index, '$currentPos' => $currentPos, '$orig2searchTerm' => $orig2searchTerm); Logging::warn($debug); } } $where = array(); /* Holds the parameters for binding after the statement has been prepared */ $params = array(); if (isset($data['advSearch']) && $data['advSearch'] === 'true') { $advancedWhere = self::buildWhereClauseForAdvancedSearch($dbname2searchTerm); if (!empty($advancedWhere['clause'])) { $where[] = join(" AND ", $advancedWhere['clause']); $params = $advancedWhere['params']; } } if ($data["sSearch"] !== "") { $searchTerms = explode(" ", $data["sSearch"]); } $selectorCount = "SELECT COUNT(*) "; $selectorRows = "SELECT " . join(",", $displayColumns) . " "; $sql = $selectorCount . " FROM " . $fromTable; $sqlTotalRows = $sql; if (isset($searchTerms)) { $searchCols = array(); for ($i = 0; $i < $data["iColumns"]; $i++) { if ($data["bSearchable_" . $i] == "true") { $searchCols[] = $data["mDataProp_{$i}"]; } } $outerCond = array(); $simpleWhere = array(); 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 = array(); for ($i = 0; $i < $data["iSortingCols"]; $i++) { $num = $data["iSortCol_" . $i]; $orderby[] = $data["mDataProp_{$num}"] . " " . $data["sSortDir_" . $i]; } $orderby[] = "id"; $orderby = join(",", $orderby); // End Order By clause $displayLength = intval($data["iDisplayLength"]); $needToBind = false; if (count($where) > 0) { $needToBind = true; $where = join(" OR ", $where); $sql = $selectorCount . " FROM " . $fromTable . " WHERE " . $where; $sqlTotalDisplayRows = $sql; $sql = $selectorRows . " FROM " . $fromTable . " WHERE " . $where . " ORDER BY " . $orderby; //limit the results returned. if ($displayLength !== -1) { $sql .= " OFFSET " . $data["iDisplayStart"] . " LIMIT " . $displayLength; } } else { $sql = $selectorRows . " FROM " . $fromTable . " ORDER BY " . $orderby; //limit the results returned. if ($displayLength !== -1) { $sql .= " OFFSET " . $data["iDisplayStart"] . " LIMIT " . $displayLength; } } try { $r = $con->query($sqlTotalRows); $totalRows = $r->fetchColumn(0); if (isset($sqlTotalDisplayRows)) { $totalDisplayRows = Application_Common_Database::prepareAndExecute($sqlTotalDisplayRows, $params, 'column'); } else { $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(); } // we need to go over all items and fix length for playlist // in case the playlist contains dynamic block foreach ($results as &$r) { //this function is also called for Manage Users so in //this case there will be no 'ftype' if (isset($r['ftype'])) { if ($r['ftype'] == 'playlist') { $pl = new Application_Model_Playlist($r['id']); $r['length'] = $pl->getLength(); } elseif ($r['ftype'] == "block") { $bl = new Application_Model_Block($r['id']); $r['bl_type'] = $bl->isStatic() ? 'static' : 'dynamic'; $r['length'] = $bl->getLength(); } } } } catch (Exception $e) { Logging::debug($e->getMessage()); } return array("sEcho" => intval($data["sEcho"]), "iTotalDisplayRecords" => intval($totalDisplayRows), "iTotalRecords" => intval($totalRows), $dataProp => $results); }
public static function searchLibraryFiles($datatables) { $baseUrl = Application_Common_OsPath::getBaseDir(); $con = Propel::getConnection(CcFilesPeer::DATABASE_NAME); $displayColumns = self::getLibraryColumns(); $plSelect = array(); $blSelect = array(); $fileSelect = array(); $streamSelect = array(); foreach ($displayColumns as $key) { if ($key === "id") { $plSelect[] = "PL.id AS " . $key; $blSelect[] = "BL.id AS " . $key; $fileSelect[] = "FILES.id AS {$key}"; $streamSelect[] = "ws.id AS " . $key; } elseif ($key === "track_title") { $plSelect[] = "name AS " . $key; $blSelect[] = "name AS " . $key; $fileSelect[] = $key; $streamSelect[] = "name AS " . $key; } elseif ($key === "ftype") { $plSelect[] = "'playlist'::varchar AS " . $key; $blSelect[] = "'block'::varchar AS " . $key; $fileSelect[] = $key; $streamSelect[] = "'stream'::varchar AS " . $key; } elseif ($key === "artist_name") { $plSelect[] = "login AS " . $key; $blSelect[] = "login AS " . $key; $fileSelect[] = $key; $streamSelect[] = "login AS " . $key; } elseif ($key === "owner_id") { $plSelect[] = "login AS " . $key; $blSelect[] = "login AS " . $key; $fileSelect[] = "sub.login AS {$key}"; $streamSelect[] = "login AS " . $key; } elseif ($key === "replay_gain") { $plSelect[] = "NULL::NUMERIC AS " . $key; $blSelect[] = "NULL::NUMERIC AS " . $key; $fileSelect[] = $key; $streamSelect[] = "NULL::NUMERIC AS " . $key; } elseif ($key === "lptime") { $plSelect[] = "NULL::TIMESTAMP AS " . $key; $blSelect[] = "NULL::TIMESTAMP AS " . $key; $fileSelect[] = $key; $streamSelect[] = $key; } elseif ($key === "is_scheduled" || $key === "is_playlist") { $plSelect[] = "NULL::boolean AS " . $key; $blSelect[] = "NULL::boolean AS " . $key; $fileSelect[] = $key; $streamSelect[] = "NULL::boolean AS " . $key; } elseif ($key === "cuein" || $key === "cueout") { $plSelect[] = "NULL::INTERVAL AS " . $key; $blSelect[] = "NULL::INTERVAL AS " . $key; $fileSelect[] = $key; $streamSelect[] = "NULL::INTERVAL AS " . $key; } else { if ($key === "length") { $plSelect[] = $key; $blSelect[] = $key; $fileSelect[] = "(cueout - cuein)::INTERVAL AS length"; $streamSelect[] = $key; } else { if (in_array($key, array("utime", "mtime"))) { $plSelect[] = $key; $blSelect[] = $key; $fileSelect[] = $key; $streamSelect[] = $key; } elseif ($key === "year") { $plSelect[] = "EXTRACT(YEAR FROM utime)::varchar AS " . $key; $blSelect[] = "EXTRACT(YEAR FROM utime)::varchar AS " . $key; $fileSelect[] = "year AS " . $key; $streamSelect[] = "EXTRACT(YEAR FROM utime)::varchar AS " . $key; } else { if (in_array($key, array("track_number", "bit_rate", "sample_rate", "bpm"))) { $plSelect[] = "NULL::int AS " . $key; $blSelect[] = "NULL::int AS " . $key; $fileSelect[] = $key; $streamSelect[] = "NULL::int AS " . $key; } elseif ($key === "filepath") { $plSelect[] = "NULL::VARCHAR AS " . $key; $blSelect[] = "NULL::VARCHAR AS " . $key; $fileSelect[] = $key; $streamSelect[] = "url AS " . $key; } else { if ($key == "mime") { $plSelect[] = "NULL::VARCHAR AS " . $key; $blSelect[] = "NULL::VARCHAR AS " . $key; $fileSelect[] = $key; $streamSelect[] = $key; } else { $plSelect[] = "NULL::text AS " . $key; $blSelect[] = "NULL::text AS " . $key; $fileSelect[] = $key; $streamSelect[] = "NULL::text AS " . $key; } } } } } } $plSelect = "SELECT " . join(",", $plSelect); $blSelect = "SELECT " . join(",", $blSelect); $fileSelect = "SELECT " . join(",", $fileSelect); $streamSelect = "SELECT " . join(",", $streamSelect); $type = intval($datatables["type"]); $plTable = "({$plSelect} FROM cc_playlist AS PL LEFT JOIN cc_subjs AS sub ON (sub.id = PL.creator_id))"; $blTable = "({$blSelect} FROM cc_block AS BL LEFT JOIN cc_subjs AS sub ON (sub.id = BL.creator_id))"; $fileTable = "({$fileSelect} FROM cc_files AS FILES LEFT JOIN cc_subjs AS sub ON (sub.id = FILES.owner_id) WHERE file_exists = 'TRUE' AND hidden='FALSE')"; //$fileTable = "({$fileSelect} FROM cc_files AS FILES WHERE file_exists = 'TRUE')"; $streamTable = "({$streamSelect} FROM cc_webstream AS ws LEFT JOIN cc_subjs AS sub ON (sub.id = ws.creator_id))"; $unionTable = "({$plTable} UNION {$blTable} UNION {$fileTable} UNION {$streamTable}) AS RESULTS"; //choose which table we need to select data from. // TODO : use constants instead of numbers -- RG switch ($type) { case 0: $fromTable = $unionTable; break; case 1: $fromTable = $fileTable . " AS File"; //need an alias for the table if it's standalone. break; case 2: $fromTable = $plTable . " AS Playlist"; //need an alias for the table if it's standalone. break; case 3: $fromTable = $blTable . " AS Block"; //need an alias for the table if it's standalone. break; case 4: $fromTable = $streamTable . " AS StreamTable"; //need an alias for the table if it's standalone. break; default: $fromTable = $unionTable; } // update is_scheduled to false for tracks that // have already played out self::updatePastFilesIsScheduled(); $results = Application_Model_Datatables::findEntries($con, $displayColumns, $fromTable, $datatables); $displayTimezone = new DateTimeZone(Application_Model_Preference::GetUserTimezone()); $utcTimezone = new DateTimeZone("UTC"); foreach ($results['aaData'] as &$row) { $row['id'] = intval($row['id']); //taken from Datatables.php, needs to be cleaned up there. if (isset($r['ftype'])) { if ($r['ftype'] == 'playlist') { $pl = new Application_Model_Playlist($r['id']); $r['length'] = $pl->getLength(); } elseif ($r['ftype'] == "block") { $bl = new Application_Model_Block($r['id']); $r['bl_type'] = $bl->isStatic() ? 'static' : 'dynamic'; $r['length'] = $bl->getLength(); } } if ($row['ftype'] === "audioclip") { $cuein_formatter = new LengthFormatter($row["cuein"]); $row["cuein"] = $cuein_formatter->format(); $cueout_formatter = new LengthFormatter($row["cueout"]); $row["cueout"] = $cueout_formatter->format(); $cuein = Application_Common_DateHelper::playlistTimeToSeconds($row["cuein"]); $cueout = Application_Common_DateHelper::playlistTimeToSeconds($row["cueout"]); $row_length = Application_Common_DateHelper::secondsToPlaylistTime($cueout - $cuein); $formatter = new SamplerateFormatter($row['sample_rate']); $row['sample_rate'] = $formatter->format(); $formatter = new BitrateFormatter($row['bit_rate']); $row['bit_rate'] = $formatter->format(); //soundcloud status $file = Application_Model_StoredFile::RecallById($row['id']); $row['soundcloud_id'] = $file->getSoundCloudId(); // for audio preview $row['audioFile'] = $row['id'] . "." . pathinfo($row['filepath'], PATHINFO_EXTENSION); } else { $row['audioFile'] = $row['id']; $row_length = $row['length']; } $len_formatter = new LengthFormatter($row_length); $row['length'] = $len_formatter->format(); //convert mtime and utime to localtime $row['mtime'] = new DateTime($row['mtime'], $utcTimezone); $row['mtime']->setTimeZone($displayTimezone); $row['mtime'] = $row['mtime']->format('Y-m-d H:i:s'); $row['utime'] = new DateTime($row['utime'], $utcTimezone); $row['utime']->setTimeZone($displayTimezone); $row['utime'] = $row['utime']->format('Y-m-d H:i:s'); //need to convert last played to localtime if it exists. if (isset($row['lptime'])) { $row['lptime'] = new DateTime($row['lptime'], $utcTimezone); $row['lptime']->setTimeZone($displayTimezone); $row['lptime'] = $row['lptime']->format('Y-m-d H:i:s'); } // we need to initalize the checkbox and image row because we do not retrieve // any data from the db for these and datatables will complain $row['checkbox'] = ""; $row['image'] = ""; $type = substr($row['ftype'], 0, 2); $row['tr_id'] = "{$type}_{$row['id']}"; } return $results; }