Ejemplo n.º 1
0
 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;
 }
Ejemplo n.º 2
0
 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;
 }
Ejemplo n.º 3
0
 public static function getUsersDataTablesInfo($datatables)
 {
     $con = Propel::getConnection(CcSubjsPeer::DATABASE_NAME);
     $displayColumns = array("id", "login", "first_name", "last_name", "type");
     $fromTable = "cc_subjs";
     // get current user
     $username = "";
     $auth = Zend_Auth::getInstance();
     if ($auth->hasIdentity()) {
         $username = $auth->getIdentity()->login;
     }
     $res = Application_Model_Datatables::findEntries($con, $displayColumns, $fromTable, $datatables);
     // mark record which is for the current user
     foreach ($res['aaData'] as &$record) {
         if ($record['login'] == $username) {
             $record['delete'] = "self";
         } else {
             $record['delete'] = "";
         }
         $record = array_map('htmlspecialchars', $record);
     }
     return $res;
 }
Ejemplo n.º 4
0
 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;
 }