/**
* Get the entire playlist as a two dimentional array, sorted in order of play.
* @param boolean $filterFiles if this is true, it will only return files that has
* file_exists flag set to true
* @return array
*/
public function getContents($filterFiles = false)
{
$sql = <<<SQL
SELECT *
FROM (
(SELECT pc.id AS id,
pc.type,
pc.position,
pc.cliplength AS LENGTH,
pc.cuein,
pc.cueout,
pc.fadein,
pc.fadeout,
pc.trackoffset,
f.id AS item_id,
f.track_title,
f.artist_name AS creator,
f.file_exists AS EXISTS,
f.filepath AS path,
f.length AS orig_length,
f.mime AS mime
FROM cc_playlistcontents AS pc
JOIN cc_files AS f ON pc.file_id=f.id
WHERE pc.playlist_id = :playlist_id1
SQL;
if ($filterFiles) {
$sql .= <<<SQL
AND f.file_exists = :file_exists
SQL;
}
$sql .= <<<SQL
AND TYPE = 0)
UNION ALL
(SELECT pc.id AS id,
pc.TYPE, pc.position,
pc.cliplength AS LENGTH,
pc.cuein,
pc.cueout,
pc.fadein,
pc.fadeout,
pc.trackoffset,
ws.id AS item_id,
(ws.name || ': ' || ws.url) AS title,
sub.login AS creator,
't'::boolean AS EXISTS,
ws.url AS path,
ws.length AS orig_length,
ws.mime as mime
FROM cc_playlistcontents AS pc
JOIN cc_webstream AS ws ON pc.stream_id=ws.id
LEFT JOIN cc_subjs AS sub ON sub.id = ws.creator_id
WHERE pc.playlist_id = :playlist_id2
AND pc.TYPE = 1)
UNION ALL
(SELECT pc.id AS id,
pc.TYPE, pc.position,
pc.cliplength AS LENGTH,
pc.cuein,
pc.cueout,
pc.fadein,
pc.fadeout,
pc.trackoffset,
bl.id AS item_id,
bl.name AS title,
sbj.login AS creator,
't'::boolean AS EXISTS,
NULL::text AS path,
bl.length AS orig_length,
NULL::text as mime
FROM cc_playlistcontents AS pc
JOIN cc_block AS bl ON pc.block_id=bl.id
JOIN cc_subjs AS sbj ON bl.creator_id=sbj.id
WHERE pc.playlist_id = :playlist_id3
AND pc.TYPE = 2)) AS temp
ORDER BY temp.position;
SQL;
//Logging::info($sql);
$params = array(':playlist_id1' => $this->id, ':playlist_id2' => $this->id, ':playlist_id3' => $this->id);
if ($filterFiles) {
$params[':file_exists'] = $filterFiles;
}
$rows = Application_Common_Database::prepareAndExecute($sql, $params);
$offset = 0;
foreach ($rows as &$row) {
//Logging::info($row);
$clipSec = Application_Common_DateHelper::playlistTimeToSeconds($row['length']);
$row['trackSec'] = $clipSec;
$row['cueInSec'] = Application_Common_DateHelper::playlistTimeToSeconds($row['cuein']);
$row['cueOutSec'] = Application_Common_DateHelper::playlistTimeToSeconds($row['cueout']);
$trackoffset = $row['trackoffset'];
$offset += $clipSec;
$offset -= $trackoffset;
$offset_cliplength = Application_Common_DateHelper::secondsToPlaylistTime($offset);
//format the length for UI.
if ($row['type'] == 2) {
$bl = new Application_Model_Block($row['item_id']);
$formatter = new LengthFormatter($bl->getLength());
} else {
$formatter = new LengthFormatter($row['length']);
}
$row['length'] = $formatter->format();
$formatter = new LengthFormatter($offset_cliplength);
$row['offset'] = $formatter->format();
//format the fades in format 00(.000000)
$fades = $this->getFadeInfo($row['position']);
$row['fadein'] = $fades[0];
$row['fadeout'] = $fades[1];
// format the cues in format 00:00:00(.0)
// we need to add the '.0' for cues and not fades
// because propel takes care of this for us
// (we use propel to fetch the fades)
$row['cuein'] = str_pad(substr($row['cuein'], 0, 10), 10, '.0');
$row['cueout'] = str_pad(substr($row['cueout'], 0, 10), 10, '.0');
//format original length
$formatter = new LengthFormatter($row['orig_length']);
$row['orig_length'] = $formatter->format();
// XSS exploit prevention
$row["track_title"] = htmlspecialchars($row["track_title"]);
$row["creator"] = htmlspecialchars($row["creator"]);
}
return $rows;
}
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;
}
