public function getItems()
{
$this->translateColumns();
$select = array("file.track_title as title", "file.artist_name as artist", "playout.played", "playout.file_id", "file.composer", "file.copyright", "file.length");
$start = $this->startDT->format("Y-m-d H:i:s");
$end = $this->endDT->format("Y-m-d H:i:s");
$historyTable = "(\n select count(schedule.file_id) as played, schedule.file_id as file_id\n from cc_schedule as schedule\n where schedule.starts >= '{$start}' and schedule.starts < '{$end}'\n and schedule.playout_status > 0 and schedule.media_item_played != FALSE and schedule.broadcasted = 1\n group by schedule.file_id\n )\n AS playout left join cc_files as file on (file.id = playout.file_id)";
$results = Application_Model_Datatables::findEntries($this->con, $select, $historyTable, $this->opts, "history");
foreach ($results["history"] as &$row) {
$formatter = new LengthFormatter($row['length']);
$row['length'] = $formatter->format();
}
return $results;
}
private function createFullResponse($obj = null, $isJson = false, $formIsValid = false)
{
$isBlock = false;
$viewPath = 'playlist/playlist.phtml';
if ($obj instanceof Application_Model_Block) {
$isBlock = true;
$viewPath = 'playlist/smart-block.phtml';
}
if (isset($obj)) {
$formatter = new LengthFormatter($obj->getLength());
$this->view->length = $formatter->format();
if ($isBlock) {
$form = new Application_Form_SmartBlockCriteria();
$form->removeDecorator('DtDdWrapper');
$form->startForm($obj->getId(), $formIsValid);
$this->view->form = $form;
$this->view->obj = $obj;
$this->view->id = $obj->getId();
if ($isJson) {
return $this->view->render($viewPath);
} else {
$this->view->html = $this->view->render($viewPath);
}
} else {
$this->view->obj = $obj;
$this->view->id = $obj->getId();
if ($isJson) {
return $this->view->html = $this->view->render($viewPath);
} else {
$this->view->html = $this->view->render($viewPath);
}
unset($this->view->obj);
}
} else {
if ($isJson) {
return $this->view->render($viewPath);
} else {
$this->view->html = $this->view->render($viewPath);
}
}
}
public function getShowListContent()
{
$con = Propel::getConnection();
$sql = <<<SQL
SELECT *
FROM (
(SELECT s.starts,
0::INTEGER as type ,
f.id AS item_id,
f.track_title,
f.album_title AS album,
f.genre AS genre,
f.length AS length,
f.artist_name AS creator,
f.file_exists AS EXISTS,
f.filepath AS filepath,
f.mime AS mime
FROM cc_schedule AS s
LEFT JOIN cc_files AS f ON f.id = s.file_id
WHERE s.instance_id = :instance_id1
AND s.playout_status >= 0
AND s.file_id IS NOT NULL)
UNION
(SELECT s.starts,
1::INTEGER as type,
ws.id AS item_id,
(ws.name || ': ' || ws.url) AS title,
null AS album,
null AS genre,
ws.length AS length,
sub.login AS creator,
't'::boolean AS EXISTS,
ws.url AS filepath,
ws.mime as mime
FROM cc_schedule AS s
LEFT JOIN cc_webstream AS ws ON ws.id = s.stream_id
LEFT JOIN cc_subjs AS sub ON ws.creator_id = sub.id
WHERE s.instance_id = :instance_id2
AND s.playout_status >= 0
AND s.stream_id IS NOT NULL)) AS temp
ORDER BY starts;
SQL;
$stmt = $con->prepare($sql);
$stmt->execute(array(':instance_id1' => $this->_instanceId, ':instance_id2' => $this->_instanceId));
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($results as &$row) {
$dt = new DateTime($row["starts"], new DateTimeZone("UTC"));
$dt->setTimezone(new DateTimeZone(date_default_timezone_get()));
$row["starts"] = $dt->format("Y-m-d H:i:s");
if (isset($row['length'])) {
$formatter = new LengthFormatter($row["length"]);
$row["length"] = $formatter->format();
}
}
return $results;
}
public static function searchLibraryFiles($datatables)
{
$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;
} else {
if (in_array($key, array("length", "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 {
$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')";
//$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;
}
$results = Application_Model_Datatables::findEntries($con, $displayColumns, $fromTable, $datatables);
//Used by the audio preview functionality in the library.
foreach ($results['aaData'] as &$row) {
$row['id'] = intval($row['id']);
$formatter = new LengthFormatter($row['length']);
$row['length'] = $formatter->format();
if ($row['ftype'] === "audioclip") {
$formatter = new SamplerateFormatter($row['sample_rate']);
$row['sample_rate'] = $formatter->format();
$formatter = new BitrateFormatter($row['bit_rate']);
$row['bit_rate'] = $formatter->format();
}
//convert mtime and utime to localtime
$row['mtime'] = new DateTime($row['mtime'], new DateTimeZone('UTC'));
$row['mtime']->setTimeZone(new DateTimeZone(date_default_timezone_get()));
$row['mtime'] = $row['mtime']->format('Y-m-d H:i:s');
$row['utime'] = new DateTime($row['utime'], new DateTimeZone('UTC'));
$row['utime']->setTimeZone(new DateTimeZone(date_default_timezone_get()));
$row['utime'] = $row['utime']->format('Y-m-d H:i:s');
// add checkbox row
$row['checkbox'] = "<input type='checkbox' name='cb_" . $row['id'] . "'>";
$type = substr($row['ftype'], 0, 2);
$row['tr_id'] = "{$type}_{$row['id']}";
//TODO url like this to work on both playlist/showbuilder
//screens. datatable stuff really needs to be pulled out and
//generalized within the project access to zend view methods
//to access url helpers is needed.
// TODO : why is there inline html here? breaks abstraction and is
// ugly
if ($type == "au") {
$row['audioFile'] = $row['id'] . "." . pathinfo($row['filepath'], PATHINFO_EXTENSION);
$row['image'] = '<img title="Track preview" src="/css/images/icon_audioclip.png">';
} elseif ($type == "pl") {
$row['image'] = '<img title="Playlist preview" src="/css/images/icon_playlist.png">';
} elseif ($type == "st") {
$row['audioFile'] = $row['id'];
$row['image'] = '<img title="Webstream preview" src="/css/images/icon_webstream.png">';
} elseif ($type == "bl") {
$row['image'] = '<img title="Smart Block" src="/css/images/icon_smart-block.png">';
}
}
return $results;
}
private function makeScheduledItemRow($p_item)
{
$row = $this->defaultRowArray;
if (isset($p_item["sched_starts"])) {
$schedStartDT = new DateTime($p_item["sched_starts"], new DateTimeZone("UTC"));
$schedStartDT->setTimezone(new DateTimeZone($this->timezone));
$schedEndDT = new DateTime($p_item["sched_ends"], new DateTimeZone("UTC"));
$schedEndDT->setTimezone(new DateTimeZone($this->timezone));
$showEndDT = new DateTime($p_item["si_ends"], new DateTimeZone("UTC"));
$this->getItemStatus($p_item, $row);
$startsEpoch = floatval($schedStartDT->format("U.u"));
$endsEpoch = floatval($schedEndDT->format("U.u"));
$showEndEpoch = floatval($showEndDT->format("U.u"));
//don't want an overbooked item to stay marked as current.
$this->getScheduledStatus($startsEpoch, min($endsEpoch, $showEndEpoch), $row);
$row["id"] = intval($p_item["sched_id"]);
$row["image"] = $p_item["file_exists"];
$row["instance"] = intval($p_item["si_id"]);
$row["starts"] = $schedStartDT->format("H:i:s");
$row["ends"] = $schedEndDT->format("H:i:s");
$cue_out = Application_Common_DateHelper::playlistTimeToSeconds($p_item['cue_out']);
$cue_in = Application_Common_DateHelper::playlistTimeToSeconds($p_item['cue_in']);
$run_time = $cue_out - $cue_in;
$formatter = new LengthFormatter(Application_Common_DateHelper::secondsToPlaylistTime($run_time));
$row['runtime'] = $formatter->format();
$row["title"] = htmlspecialchars($p_item["file_track_title"]);
$row["creator"] = htmlspecialchars($p_item["file_artist_name"]);
$row["album"] = htmlspecialchars($p_item["file_album_title"]);
$row["cuein"] = $p_item["cue_in"];
$row["cueout"] = $p_item["cue_out"];
$row["fadein"] = round(substr($p_item["fade_in"], 6), 6);
$row["fadeout"] = round(substr($p_item["fade_out"], 6), 6);
$row["mime"] = $p_item["file_mime"];
$row["pos"] = $this->pos++;
$this->contentDT = $schedEndDT;
} else {
if (intval($p_item["si_record"]) === 1) {
$row["record"] = true;
$row["instance"] = intval($p_item["si_id"]);
$showStartDT = new DateTime($p_item["si_starts"], new DateTimeZone("UTC"));
$showEndDT = new DateTime($p_item["si_ends"], new DateTimeZone("UTC"));
$startsEpoch = floatval($showStartDT->format("U.u"));
$endsEpoch = floatval($showEndDT->format("U.u"));
$this->getScheduledStatus($startsEpoch, $endsEpoch, $row);
} else {
$row["empty"] = true;
$row["id"] = 0;
$row["instance"] = intval($p_item["si_id"]);
}
}
if (intval($p_item["si_rebroadcast"]) === 1) {
$row["rebroadcast"] = true;
}
if ($this->currentShow === true) {
$row["currentShow"] = true;
}
$this->getItemColor($p_item, $row);
$this->getRowTimestamp($p_item, $row);
$this->isAllowed($p_item, $row);
return $row;
}
public function getLength()
{
if ($this->hasDynamicBlock()) {
$ids = $this->getIdsOfDynamicBlocks();
$length = $this->pl->getDbLength();
foreach ($ids as $id) {
$bl = new Application_Model_Block($id['id']);
if ($bl->hasItemLimit()) {
return "N/A";
}
}
$formatter = new LengthFormatter($length);
return "~" . $formatter->format();
} else {
return $this->pl->getDbLength();
}
}
public function getLength()
{
$this->block->reload();
$prepend = "";
if ($this->isStatic()) {
$length = $this->block->getDbLength();
} else {
$length = $this->getDynamicBlockLength();
if (!$this->hasItemLimit()) {
$prepend = "~";
}
}
$formatter = new LengthFormatter($length);
$length = $prepend . $formatter->format();
return $length;
}
public function getFileSummaryData($startDT, $endDT, $opts)
{
$select = array("summary.played", "summary.file_id", "summary." . MDATA_KEY_TITLE, "summary." . MDATA_KEY_CREATOR);
$mainSqlQuery = "";
$paramMap = array();
$start = $startDT->format("Y-m-d H:i:s");
$end = $endDT->format("Y-m-d H:i:s");
$paramMap["starts"] = $start;
$paramMap["ends"] = $end;
$template = $this->getConfiguredFileTemplate();
$fields = $template["fields"];
$required = $this->mandatoryFileFields();
foreach ($fields as $index => $field) {
$key = $field["name"];
if (in_array($field["name"], $required)) {
continue;
}
$select[] = "summary.{$key}";
}
$fileSummaryTable = "((\n\t\t\tSELECT COUNT(history.file_id) as played, history.file_id as file_id\n\t\t\tFROM cc_playout_history AS history\n\t\t\tWHERE history.starts >= :starts AND history.starts < :ends\n\t\t\tAND history.file_id IS NOT NULL\n\t\t\tGROUP BY history.file_id\n\t\t) AS playout\n\t\tLEFT JOIN cc_files AS file ON (file.id = playout.file_id)) AS summary";
$mainSqlQuery .= "SELECT " . join(", ", $select) . " FROM {$fileSummaryTable}";
//-------------------------------------------------------------------------
//need to count the total rows to tell Datatables.
$stmt = $this->con->prepare($mainSqlQuery);
foreach ($paramMap as $param => $v) {
$stmt->bindValue($param, $v);
}
if ($stmt->execute()) {
$totalRows = $stmt->rowCount();
} else {
$msg = implode(',', $stmt->errorInfo());
throw new Exception("Error: {$msg}");
}
//------------------------------------------------------------------------
//Using Datatables parameters to sort the data.
$numOrderColumns = $opts["iSortingCols"];
$orderBys = array();
for ($i = 0; $i < $numOrderColumns; $i++) {
$colNum = $opts["iSortCol_" . $i];
$key = $opts["mDataProp_" . $colNum];
$sortDir = $opts["sSortDir_" . $i];
$orderBys[] = "summary.{$key} {$sortDir}";
}
if ($numOrderColumns > 0) {
$orders = join(", ", $orderBys);
$mainSqlQuery .= " ORDER BY {$orders}";
}
//------------------------------------------------------------
//using datatables params to add limits/offsets
$displayLength = intval($opts["iDisplayLength"]);
if ($displayLength !== -1) {
$mainSqlQuery .= " OFFSET :offset LIMIT :limit";
$paramMap["offset"] = $opts["iDisplayStart"];
$paramMap["limit"] = $displayLength;
}
$stmt = $this->con->prepare($mainSqlQuery);
foreach ($paramMap as $param => $v) {
$stmt->bindValue($param, $v);
}
$rows = array();
if ($stmt->execute()) {
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
} else {
$msg = implode(',', $stmt->errorInfo());
throw new Exception("Error: {$msg}");
}
//-----------------------------------------------------------------
//processing the results
foreach ($rows as &$row) {
if (isset($row[MDATA_KEY_DURATION])) {
$formatter = new LengthFormatter($row[MDATA_KEY_DURATION]);
$row[MDATA_KEY_DURATION] = $formatter->format();
}
}
return array("sEcho" => intval($opts["sEcho"]), "iTotalDisplayRecords" => intval($totalRows), "iTotalRecords" => intval($totalRows), "history" => $rows);
}
public function getFileMetadataAction()
{
$id = $this->_getParam('id');
$type = $this->_getParam('type');
try {
if ($type == "audioclip") {
$file = Application_Model_StoredFile::Recall($id);
$this->view->type = $type;
$md = $file->getMetadata();
foreach ($md as $key => $value) {
if ($key == 'MDATA_KEY_DIRECTORY') {
$musicDir = Application_Model_MusicDir::getDirByPK($value);
$md['MDATA_KEY_FILEPATH'] = Application_Common_OsPath::join($musicDir->getDirectory(), $md['MDATA_KEY_FILEPATH']);
}
}
$formatter = new SamplerateFormatter($md["MDATA_KEY_SAMPLERATE"]);
$md["MDATA_KEY_SAMPLERATE"] = $formatter->format();
$formatter = new BitrateFormatter($md["MDATA_KEY_BITRATE"]);
$md["MDATA_KEY_BITRATE"] = $formatter->format();
$formatter = new LengthFormatter($md["MDATA_KEY_DURATION"]);
$md["MDATA_KEY_DURATION"] = $formatter->format();
$this->view->md = $md;
} elseif ($type == "playlist") {
$file = new Application_Model_Playlist($id);
$this->view->type = $type;
$md = $file->getAllPLMetaData();
$formatter = new LengthFormatter($md["dcterms:extent"]);
$md["dcterms:extent"] = $formatter->format();
$this->view->md = $md;
$this->view->contents = $file->getContents();
} elseif ($type == "block") {
$block = new Application_Model_Block($id);
$this->view->type = $type;
$md = $block->getAllPLMetaData();
$formatter = new LengthFormatter($md["dcterms:extent"]);
$md["dcterms:extent"] = $formatter->format();
$this->view->md = $md;
if ($block->isStatic()) {
$this->view->blType = 'Static';
$this->view->contents = $block->getContents();
} else {
$this->view->blType = 'Dynamic';
$this->view->contents = $block->getCriteria();
}
$this->view->block = $block;
} elseif ($type == "stream") {
$webstream = CcWebstreamQuery::create()->findPK($id);
$ws = new Application_Model_Webstream($webstream);
$md = $ws->getMetadata();
$this->view->md = $md;
$this->view->type = $type;
}
} catch (Exception $e) {
Logging::info($e->getMessage());
}
}
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;
}
