public static function getLoginAttempts($login)
{
$sql = "SELECT login_attempts FROM cc_subjs WHERE login=:login";
$map = array(":login" => $login);
$res = Application_Common_Database::prepareAndExecute($sql, $map, Application_Common_Database::COLUMN);
return $res !== false ? $res : 0;
}
public static function resetAttempts($ip)
{
$sql = "select count(*) from cc_login_attempts WHERE ip= :ip";
$res = Application_Common_Database::prepareAndExecute($sql, array(':ip' => $ip), Application_Common_Database::COLUMN);
if ($res > 0) {
$sql = "DELETE FROM cc_login_attempts WHERE ip= :ip";
Application_Common_Database::prepareAndExecute($sql, array(':ip' => $ip), Application_Common_Database::EXECUTE);
}
}
public static function getLocales()
{
$con = Propel::getConnection();
$sql = "SELECT * FROM cc_locale";
$res = Application_Common_Database::prepareAndExecute($sql);
$out = array();
foreach ($res as $r) {
$out[$r["locale_code"]] = $r["locale_lang"];
}
return $out;
}
private static function setValue($key, $value, $isUserValue = false)
{
try {
//called from a daemon process
if (!class_exists("Zend_Auth", false) || !Zend_Auth::getInstance()->hasIdentity()) {
$id = NULL;
} else {
$auth = Zend_Auth::getInstance();
$id = $auth->getIdentity()->id;
}
//Check if key already exists
$sql = "SELECT COUNT(*) FROM cc_pref" . " WHERE keystr = :key";
$paramMap = array();
$paramMap[':key'] = $key;
//For user specific preference, check if id matches as well
if ($isUserValue) {
$sql .= " AND subjid = :id";
$paramMap[':id'] = $id;
}
$result = Application_Common_Database::prepareAndExecute($sql, $paramMap, 'column');
$paramMap = array();
if ($result == 1) {
// result found
if (is_null($id) || !$isUserValue) {
// system pref
$sql = "UPDATE cc_pref" . " SET subjid = NULL, valstr = :value" . " WHERE keystr = :key";
} else {
// user pref
$sql = "UPDATE cc_pref" . " SET valstr = :value" . " WHERE keystr = :key AND subjid = :id";
$paramMap[':id'] = $id;
}
} else {
// result not found
if (is_null($id) || !$isUserValue) {
// system pref
$sql = "INSERT INTO cc_pref (keystr, valstr)" . " VALUES (:key, :value)";
} else {
// user pref
$sql = "INSERT INTO cc_pref (subjid, keystr, valstr)" . " VALUES (:id, :key, :value)";
$paramMap[':id'] = $id;
}
}
$paramMap[':key'] = $key;
$paramMap[':value'] = $value;
Application_Common_Database::prepareAndExecute($sql, $paramMap, 'execute');
} catch (Exception $e) {
header('HTTP/1.0 503 Service Unavailable');
Logging::info("Could not connect to database: " . $e->getMessage());
exit;
}
}
public static function insertDataPoints($p_dataPoints)
{
$timestamp_sql = "INSERT INTO cc_timestamp (timestamp) VALUES\n (:ts::TIMESTAMP) RETURNING id;";
$mount_name_check_sql = "SELECT id from cc_mount_name WHERE\n mount_name = :mn;";
$mount_name_insert_sql = "INSERT INTO cc_mount_name (mount_name) VALUES\n (:mn) RETURNING id;";
$stats_sql = "INSERT INTO cc_listener_count (timestamp_id,\n listener_count, mount_name_id) VALUES (:timestamp_id,\n :listener_count, :mount_name_id)";
foreach ($p_dataPoints as $dp) {
$timestamp_id = Application_Common_Database::prepareAndExecute($timestamp_sql, array('ts' => $dp['timestamp']), "column");
$mount_name_id = Application_Common_Database::prepareAndExecute($mount_name_check_sql, array('mn' => $dp['mount_name']), "column");
if (strlen($mount_name_id) == 0) {
//there is a race condition here where theoretically the row
//with value "mount_name" could appear, but this is *very*
//unlikely and won't break anything even if it happens.
$mount_name_id = Application_Common_Database::prepareAndExecute($mount_name_insert_sql, array('mn' => $dp['mount_name']), "column");
}
Application_Common_Database::prepareAndExecute($stats_sql, array('timestamp_id' => $timestamp_id, 'listener_count' => $dp["num_listeners"], 'mount_name_id' => $mount_name_id));
}
}
public static function smartPrepareAndExecute($sql, array $params, $type = 'all', $fetchType = PDO::FETCH_ASSOC)
{
$new_params = array();
$new_sql = $sql;
foreach ($params as $k => $v) {
$matches_count = substr_count($sql, $k);
if ($matches_count == 0) {
throw new Exception("Argument {$k} is not inside {$sql}");
} elseif ($matches_count == 1) {
$new_params[$k] = $new_params[$v];
} else {
foreach (range(1, $matches_count) as $i) {
preg_replace("/{$k}(\\D)/", "{$k}{$i}{${1}}", $sql, 1);
$new_params[$k . $i] = $v;
}
}
}
return Application_Common_Database::prepareAndExecute($new_sql, $new_params, $type, $fetchType);
}
public static function updatePastFilesIsScheduled()
{
/* Set the is_scheduled flag to false where it was true in the
* past, and where tracks are not scheduled in the future and do
* not belong to a show that has not ended yet. We need to check
* for show end times in case a track is overbooked, which would
* indicate it is still scheduled in the future
*/
$sql = <<<SQL
UPDATE cc_files SET is_scheduled = false
WHERE is_scheduled = true
AND id NOT IN (
SELECT s.file_id FROM cc_schedule AS s
LEFT JOIN cc_show_instances AS i
ON s.instance_id = i.id
WHERE s.ends > now() at time zone 'UTC'
AND i.ends > now() at time zone 'UTC'
)
SQL;
Application_Common_Database::prepareAndExecute($sql, array(), Application_Common_Database::EXECUTE);
}
/**
* Given a start time $timeStart and end time $timeEnd, returns the next $limit
* number of shows within the time interval
* If $timeEnd not given, shows within next 48 hours from $timeStart are returned
* If $limit not given, all shows within the intervals are returned
* Times are all in UTC time.
*
* @param String $timeStart - interval start time (in UTC)
* @param int $limit - number of shows to return
* @param String $timeEnd - interval end time (in UTC)
* @return array - the next $limit number of shows within the time interval
*/
public static function getNextShows($timeStart, $limit = "ALL", $timeEnd = "")
{
// defaults to retrieving shows from next 2 days if no end time has
// been specified
if ($timeEnd == "") {
$timeEnd = "'{$timeStart}' + INTERVAL '2 days'";
}
//TODO, returning starts + ends twice (once with an alias). Unify this after the 2.0 release. --Martin
$sql = <<<SQL
SELECT si.starts AS start_timestamp,
si.ends AS end_timestamp,
s.name,
s.id,
si.id AS instance_id,
si.record,
s.url,
starts,
ends
FROM cc_show_instances si
LEFT JOIN cc_show s
ON si.show_id = s.id
WHERE si.show_id = s.id
AND si.starts >= :timeStart::timestamp
AND si.starts < :timeEnd::timestamp
AND modified_instance != TRUE
ORDER BY si.starts
SQL;
//PDO won't accept "ALL" as a limit value (complains it is not an
//integer, and so we must completely remove the limit clause if we
//want to show all results - MK
if ($limit != "ALL") {
$sql .= PHP_EOL . "LIMIT :lim";
$params = array(':timeStart' => $timeStart, ':timeEnd' => $timeEnd, ':lim' => $limit);
} else {
$params = array(':timeStart' => $timeStart, ':timeEnd' => $timeEnd);
}
return Application_Common_Database::prepareAndExecute($sql, $params, 'all');
}
private static function replaceInstanceContentCheck($currentShowStamp, $showStamp)
{
/*$currentShowStamp = CcScheduleQuery::create()
->filterByDbInstanceId($ccShowInstance->getDbId())
->orderByDbStarts()
->find();*/
$counter = 0;
foreach ($showStamp as $item) {
if ($item["file_id"] != $currentShowStamp[$counter]["file_id"] || $item["stream_id"] != $currentShowStamp[$counter]["stream_id"]) {
/*CcScheduleQuery::create()
->filterByDbInstanceId($ccShowInstance->getDbId())
->delete();*/
$delete_sql = "DELETE FROM cc_schedule " . "WHERE instance_id = {$currentShowStamp[$counter]["instance_id"]}";
Application_Common_Database::prepareAndExecute($delete_sql, array(), Application_Common_Database::EXECUTE);
return true;
}
}
/* If we get here, the content in the show instance is the same
* as what we want to replace it with, so we can leave as is
*/
return false;
}
public static function GetAllListenerStatErrors()
{
$sql = "SELECT * FROM cc_stream_setting WHERE keyname like :p1";
$mounts = Application_Common_Database::prepareAndExecute($sql, array(':p1' => '%_mount'));
$mps = array();
foreach ($mounts as $mount) {
$mps[] = "'" . $mount["value"] . "_listener_stat_error'";
}
$in = implode(",", $mps);
$sql = "SELECT * FROM cc_stream_setting WHERE keyname IN ( {$in} )";
return Application_Common_Database::prepareAndExecute($sql, array());
}
public static function GetCountryList()
{
$sql = "SELECT * FROM cc_country";
$res = Application_Common_Database::prepareAndExecute($sql, array());
$out = array();
$out[""] = _("Select Country");
foreach ($res as $r) {
$out[$r["isocode"]] = $r["name"];
}
return $out;
}
/**
*
* Enter description here ...
* @param $scheduleItems
* cc_schedule items, where the items get inserted after
* @param $filesToInsert
* array of schedule item info, what gets inserted into cc_schedule
* @param $adjustSched
*/
private function insertAfter($scheduleItems, $mediaItems, $filesToInsert = null, $adjustSched = true, $moveAction = false)
{
try {
$affectedShowInstances = array();
//dont want to recalculate times for moved items
//only moved items have a sched_id
$excludeIds = array();
$startProfile = microtime(true);
$temp = array();
$instance = null;
/* Items in shows are ordered by position number. We need to know
* the position when adding/moving items in linked shows so they are
* added or moved in the correct position
*/
$pos = 0;
$linked = false;
foreach ($scheduleItems as $schedule) {
//reset
$this->applyCrossfades = true;
$id = intval($schedule["id"]);
/* Find out if the show where the cursor position (where an item will
* be inserted) is located is linked or not. If the show is linked,
* we need to make sure there isn't another cursor selection in one of it's
* linked shows. If there is that will cause a duplication, in the least,
* of inserted items
*/
if ($id != 0) {
$schedule_sql = "SELECT * FROM cc_schedule WHERE id = " . $id;
$ccSchedule = Application_Common_Database::prepareAndExecute($schedule_sql, array(), Application_Common_Database::SINGLE);
$show_sql = "SELECT * FROM cc_show WHERE id IN (" . "SELECT show_id FROM cc_show_instances WHERE id = " . $ccSchedule["instance_id"] . ")";
$ccShow = Application_Common_Database::prepareAndExecute($show_sql, array(), Application_Common_Database::SINGLE);
$linked = $ccShow["linked"];
if ($linked) {
$unique = $ccShow["id"] . $ccSchedule["position"];
if (!in_array($unique, $temp)) {
$temp[] = $unique;
} else {
continue;
}
}
} else {
$show_sql = "SELECT * FROM cc_show WHERE id IN (" . "SELECT show_id FROM cc_show_instances WHERE id = " . $schedule["instance"] . ")";
$ccShow = Application_Common_Database::prepareAndExecute($show_sql, array(), Application_Common_Database::SINGLE);
$linked = $ccShow["linked"];
if ($linked) {
$unique = $ccShow["id"] . "a";
if (!in_array($unique, $temp)) {
$temp[] = $unique;
} else {
continue;
}
}
}
/* If the show where the cursor position is located is linked
* we need to insert the items for each linked instance belonging
* to that show
*/
if ($linked) {
$instance_sql = "SELECT * FROM cc_show_instances " . "WHERE show_id = " . $ccShow["id"];
$instances = Application_Common_Database::prepareAndExecute($instance_sql);
} else {
$instance_sql = "SELECT * FROM cc_show_instances " . "WHERE id = " . $schedule["instance"];
$instances = Application_Common_Database::prepareAndExecute($instance_sql);
}
$excludePositions = array();
foreach ($instances as &$instance) {
//reset
$this->applyCrossfades = true;
$instanceId = $instance["id"];
if ($id !== 0) {
/* We use the selected cursor's position to find the same
* positions in every other linked instance
*/
$pos = $ccSchedule["position"];
$linkedItem_sql = "SELECT ends FROM cc_schedule " . "WHERE instance_id = {$instanceId} " . "AND position = {$pos} " . "AND playout_status != -1";
$linkedItemEnds = Application_Common_Database::prepareAndExecute($linkedItem_sql, array(), Application_Common_Database::COLUMN);
if (!$linkedItemEnds) {
//With dynamic smart blocks there may be different number of items in
//each show. In case the position does not exist we need to select
//the end time of the last position
$maxPos_sql = "SELECT max(position) from cc_schedule " . "WHERE instance_id = {$instanceId}";
$pos = Application_Common_Database::prepareAndExecute($maxPos_sql, array(), Application_Common_Database::COLUMN);
//show instance has no scheduled tracks
if (empty($pos)) {
$pos = 0;
$nextStartDT = new DateTime($instance["starts"], new DateTimeZone("UTC"));
} else {
$linkedItem_sql = "SELECT ends FROM cc_schedule " . "WHERE instance_id = {$instanceId} " . "AND position = {$pos} " . "AND playout_status != -1";
$linkedItemEnds = Application_Common_Database::prepareAndExecute($linkedItem_sql, array(), Application_Common_Database::COLUMN);
$nextStartDT = $this->findNextStartTime(new DateTime($linkedItemEnds, new DateTimeZone("UTC")), $instanceId);
}
} else {
$nextStartDT = $this->findNextStartTime(new DateTime($linkedItemEnds, new DateTimeZone("UTC")), $instanceId);
$pos++;
}
//$pos++;
} else {
$showStartDT = new DateTime($instance["starts"], new DateTimeZone("UTC"));
$nextStartDT = $this->findNextStartTime($showStartDT, $instanceId);
//first item in show so start position counter at 0
$pos = 0;
/* Show is empty so we don't need to calculate crossfades
* for the first inserted item
*/
$this->applyCrossfades = false;
}
if (!in_array($instanceId, $affectedShowInstances)) {
$affectedShowInstances[] = $instanceId;
}
/*
* $adjustSched is true if there are schedule items
* following the item just inserted, per show instance
*/
if ($adjustSched === true) {
$pstart = microtime(true);
if ($this->applyCrossfades) {
$initalStartDT = clone $this->findTimeDifference($nextStartDT, $this->crossfadeDuration);
} else {
$initalStartDT = clone $nextStartDT;
}
$pend = microtime(true);
Logging::debug("finding all following items.");
Logging::debug(floatval($pend) - floatval($pstart));
}
if (is_null($filesToInsert)) {
$filesToInsert = array();
foreach ($mediaItems as $media) {
$filesToInsert = array_merge($filesToInsert, $this->retrieveMediaFiles($media["id"], $media["type"]));
}
}
$doInsert = false;
$doUpdate = false;
$values = array();
foreach ($filesToInsert as &$file) {
//item existed previously and is being moved.
//need to keep same id for resources if we want REST.
if (isset($file['sched_id'])) {
$adjustFromDT = clone $nextStartDT;
$doUpdate = true;
$movedItem_sql = "SELECT * FROM cc_schedule " . "WHERE id = " . $file["sched_id"];
$sched = Application_Common_Database::prepareAndExecute($movedItem_sql, array(), Application_Common_Database::SINGLE);
/* We need to keep a record of the original positon a track
* is being moved from so we can use it to retrieve the correct
* items in linked instances
*/
if (!isset($originalPosition)) {
$originalPosition = $sched["position"];
}
/* If we are moving an item in a linked show we need to get
* the relative item to move in each instance. We know what the
* relative item is by its position
*/
if ($linked) {
$movedItem_sql = "SELECT * FROM cc_schedule " . "WHERE position = {$originalPosition} " . "AND instance_id = {$instanceId}";
$sched = Application_Common_Database::prepareAndExecute($movedItem_sql, array(), Application_Common_Database::SINGLE);
}
/* If we don't find a schedule item it means the linked
* shows have a different amount of items (dyanmic block)
* and we should skip the item move for this show instance
*/
if (!$sched) {
continue;
}
$excludeIds[] = intval($sched["id"]);
$file["cliplength"] = $sched["clip_length"];
$file["cuein"] = $sched["cue_in"];
$file["cueout"] = $sched["cue_out"];
$file["fadein"] = $sched["fade_in"];
$file["fadeout"] = $sched["fade_out"];
} else {
$doInsert = true;
}
// default fades are in seconds
// we need to convert to '00:00:00' format
$file['fadein'] = Application_Common_DateHelper::secondsToPlaylistTime($file['fadein']);
$file['fadeout'] = Application_Common_DateHelper::secondsToPlaylistTime($file['fadeout']);
switch ($file["type"]) {
case 0:
$fileId = $file["id"];
$streamId = "null";
break;
case 1:
$streamId = $file["id"];
$fileId = "null";
break;
default:
break;
}
if ($this->applyCrossfades) {
$nextStartDT = $this->findTimeDifference($nextStartDT, $this->crossfadeDuration);
$endTimeDT = $this->findEndTime($nextStartDT, $file['cliplength']);
$endTimeDT = $this->findTimeDifference($endTimeDT, $this->crossfadeDuration);
/* Set it to false because the rest of the crossfades
* will be applied after we insert each item
*/
$this->applyCrossfades = false;
}
$endTimeDT = $this->findEndTime($nextStartDT, $file['cliplength']);
if ($doInsert) {
$values[] = "(" . "'{$nextStartDT->format("Y-m-d H:i:s.u")}', " . "'{$endTimeDT->format("Y-m-d H:i:s.u")}', " . "'{$file["cuein"]}', " . "'{$file["cueout"]}', " . "'{$file["fadein"]}', " . "'{$file["fadeout"]}', " . "'{$file["cliplength"]}', " . "{$pos}, " . "{$instanceId}, " . "{$fileId}, " . "{$streamId})";
} elseif ($doUpdate) {
$update_sql = "UPDATE cc_schedule SET " . "starts = '{$nextStartDT->format("Y-m-d H:i:s.u")}', " . "ends = '{$endTimeDT->format("Y-m-d H:i:s.u")}', " . "cue_in = '{$file["cuein"]}', " . "cue_out = '{$file["cueout"]}', " . "fade_in = '{$file["fadein"]}', " . "fade_out = '{$file["fadeout"]}', " . "clip_length = '{$file["cliplength"]}', " . "position = {$pos} " . "WHERE id = {$sched["id"]}";
Application_Common_Database::prepareAndExecute($update_sql, array(), Application_Common_Database::EXECUTE);
}
$nextStartDT = $this->findTimeDifference($endTimeDT, $this->crossfadeDuration);
$pos++;
}
//all files have been inserted/moved
if ($doInsert) {
$insert_sql = "INSERT INTO cc_schedule " . "(starts, ends, cue_in, cue_out, fade_in, fade_out, " . "clip_length, position, instance_id, file_id, stream_id) VALUES " . implode($values, ",") . " RETURNING id";
$stmt = $this->con->prepare($insert_sql);
if ($stmt->execute()) {
foreach ($stmt->fetchAll() as $row) {
$excludeIds[] = $row["id"];
}
}
}
// update is_scheduled flag for each cc_file
$fileIds = array();
foreach ($filesToInsert as &$file) {
$fileIds[] = $file["id"];
}
$selectCriteria = new Criteria();
$selectCriteria->add(CcFilesPeer::ID, $fileIds, Criteria::IN);
$selectCriteria->addAnd(CcFilesPeer::IS_SCHEDULED, false);
$updateCriteria = new Criteria();
$updateCriteria->add(CcFilesPeer::IS_SCHEDULED, true);
BasePeer::doUpdate($selectCriteria, $updateCriteria, $this->con);
/* Reset files to insert so we can get a new set of files. We have
* to do this in case we are inserting a dynamic block
*/
if (!$moveAction) {
$filesToInsert = null;
}
if ($adjustSched === true) {
$followingItems_sql = "SELECT * FROM cc_schedule " . "WHERE starts >= '{$initalStartDT->format("Y-m-d H:i:s.u")}' " . "AND instance_id = {$instanceId} ";
if (count($excludeIds) > 0) {
$followingItems_sql .= "AND id NOT IN (" . implode($excludeIds, ",") . ") ";
}
$followingItems_sql .= "ORDER BY starts";
$followingSchedItems = Application_Common_Database::prepareAndExecute($followingItems_sql);
$pstart = microtime(true);
//recalculate the start/end times after the inserted items.
foreach ($followingSchedItems as $item) {
$endTimeDT = $this->findEndTime($nextStartDT, $item["clip_length"]);
$endTimeDT = $this->findTimeDifference($endTimeDT, $this->crossfadeDuration);
$update_sql = "UPDATE cc_schedule SET " . "starts = '{$nextStartDT->format("Y-m-d H:i:s.u")}', " . "ends = '{$endTimeDT->format("Y-m-d H:i:s.u")}', " . "position = {$pos} " . "WHERE id = {$item["id"]}";
Application_Common_Database::prepareAndExecute($update_sql, array(), Application_Common_Database::EXECUTE);
$nextStartDT = $this->findTimeDifference($endTimeDT, $this->crossfadeDuration);
$pos++;
}
$pend = microtime(true);
Logging::debug("adjusting all following items.");
Logging::debug(floatval($pend) - floatval($pstart));
}
if ($moveAction) {
$this->calculateCrossfades($instanceId);
}
}
//for each instance
}
//for each schedule location
$endProfile = microtime(true);
Logging::debug("finished adding scheduled items.");
Logging::debug(floatval($endProfile) - floatval($startProfile));
//update the status flag in cc_schedule.
$instances = CcShowInstancesQuery::create()->filterByPrimaryKeys($affectedShowInstances)->find($this->con);
$startProfile = microtime(true);
foreach ($instances as $instance) {
$instance->updateScheduleStatus($this->con);
}
$endProfile = microtime(true);
Logging::debug("updating show instances status.");
Logging::debug(floatval($endProfile) - floatval($startProfile));
$startProfile = microtime(true);
//update the last scheduled timestamp.
CcShowInstancesQuery::create()->filterByPrimaryKeys($affectedShowInstances)->update(array('DbLastScheduled' => new DateTime("now", new DateTimeZone("UTC"))), $this->con);
$endProfile = microtime(true);
Logging::debug("updating last scheduled timestamp.");
Logging::debug(floatval($endProfile) - floatval($startProfile));
} catch (Exception $e) {
Logging::debug($e->getMessage());
throw $e;
}
}
public static function getAllBlockFiles()
{
$sql = <<<SQL
SELECT distinct(file_id)
FROM cc_blockcontents
SQL;
$files = Application_Common_Database::prepareAndExecute($sql, array());
$real_files = array();
foreach ($files as $f) {
$real_files[] = $f['file_id'];
}
return $real_files;
}
public static function getMaxLengths()
{
$sql = <<<SQL
SELECT column_name, character_maximum_length FROM information_schema.columns
WHERE table_name = 'cc_show' AND character_maximum_length > 0
SQL;
$result = Application_Common_Database::prepareAndExecute($sql);
$assocArray = array();
foreach ($result as $row) {
$assocArray[$row['column_name']] = $row['character_maximum_length'];
}
return $assocArray;
}
public static function getUserData($id)
{
$sql = <<<SQL
SELECT login, first_name, last_name, type, id, email, cell_phone, skype_contact,
jabber_contact
FROM cc_subjs
WHERE id = :id
SQL;
return Application_Common_Database::prepareAndExecute($sql, array(":id" => $id), 'single');
}
/**
*
* Enter description here ...
* @param ccShowDays $showDay
* @param DateTime $showStartDate user's local time
* @param $instanceId
*/
private function createRebroadcastInstances($showDay, $showStartDate, $instanceId)
{
$currentUtcTimestamp = gmdate("Y-m-d H:i:s");
$showId = $this->ccShow->getDbId();
$sql = "SELECT * FROM cc_show_rebroadcast WHERE show_id=:show_id";
$rebroadcasts = Application_Common_Database::prepareAndExecute($sql, array(':show_id' => $showId), 'all');
foreach ($rebroadcasts as $rebroadcast) {
$days = explode(" ", $rebroadcast["day_offset"]);
$time = explode(":", $rebroadcast["start_time"]);
$offset = array("days" => $days[0], "hours" => $time[0], "mins" => $time[1]);
list($utcStartDateTime, $utcEndDateTime) = $this->createUTCStartEndDateTime($showStartDate, $showDay->getDbDuration(), $offset);
if ($utcStartDateTime->format("Y-m-d H:i:s") > $currentUtcTimestamp) {
$ccShowInstance = new CcShowInstances();
$ccShowInstance->setDbShowId($showId);
$ccShowInstance->setDbStarts($utcStartDateTime);
$ccShowInstance->setDbEnds($utcEndDateTime);
$ccShowInstance->setDbRecord(0);
$ccShowInstance->setDbRebroadcast(1);
$ccShowInstance->setDbOriginalShow($instanceId);
$ccShowInstance->save();
}
}
}
/**
* There are 2 cases where this function can be called.
* 1. When watched dir was removed
* 2. When some dir was watched, but it was unmounted
*
* In case of 1, $userAddedWatchedDir should be true
* In case of 2, $userAddedWatchedDir should be false
*
* When $userAddedWatchedDir is true, it will set "Watched" flag to false
* otherwise, it will set "Exists" flag to true
*/
public function remove($userAddedWatchedDir = true)
{
$music_dir_id = $this->getId();
$sql = <<<SQL
SELECT DISTINCT s.instance_id
FROM cc_music_dirs AS md
LEFT JOIN cc_files AS f ON f.directory = md.id
RIGHT JOIN cc_schedule AS s ON s.file_id = f.id
WHERE md.id = :musicDirId;
SQL;
$show_instances = Application_Common_Database::prepareAndExecute($sql, array(':musicDirId' => $music_dir_id), 'all');
// get all the files on this dir
$sql = <<<SQL
UPDATE cc_files
SET file_exists = 'f'
WHERE id IN
(SELECT f.id
FROM cc_music_dirs AS md
LEFT JOIN cc_files AS f ON f.directory = md.id
WHERE md.id = :musicDirId);
SQL;
$affected = Application_Common_Database::prepareAndExecute($sql, array(':musicDirId' => $music_dir_id), 'all');
// set RemovedFlag to true
if ($userAddedWatchedDir) {
self::setWatchedFlag(false);
} else {
self::setExistsFlag(false);
}
//$res = $this->_dir->delete();
foreach ($show_instances as $show_instance_row) {
$temp_show = new Application_Model_ShowInstance($show_instance_row["instance_id"]);
$temp_show->updateScheduledTime();
}
Application_Model_RabbitMq::PushSchedule();
}
public static function checkOverlappingShows($show_start, $show_end, $update = false, $instanceId = null, $showId = null)
{
$overlapping = false;
$params = array(':show_end1' => $show_end->format('Y-m-d H:i:s'), ':show_end2' => $show_end->format('Y-m-d H:i:s'), ':show_end3' => $show_end->format('Y-m-d H:i:s'));
/* If a show is being edited, exclude it from the query
* In both cases (new and edit) we only grab shows that
* are scheduled 2 days prior
*/
if ($update) {
$sql = <<<SQL
SELECT id,
starts,
ends
FROM cc_show_instances
WHERE (ends <= :show_end1
OR starts <= :show_end2)
AND date(starts) >= (date(:show_end3) - INTERVAL '2 days')
AND modified_instance = FALSE
SQL;
if (is_null($showId)) {
$sql .= <<<SQL
AND id != :instanceId
ORDER BY ends
SQL;
$params[':instanceId'] = $instanceId;
} else {
$sql .= <<<SQL
AND show_id != :showId
ORDER BY ends
SQL;
$params[':showId'] = $showId;
}
$rows = Application_Common_Database::prepareAndExecute($sql, $params, 'all');
} else {
$sql = <<<SQL
SELECT id,
starts,
ends
FROM cc_show_instances
WHERE (ends <= :show_end1
OR starts <= :show_end2)
AND date(starts) >= (date(:show_end3) - INTERVAL '2 days')
AND modified_instance = FALSE
ORDER BY ends
SQL;
$rows = Application_Common_Database::prepareAndExecute($sql, array(':show_end1' => $show_end->format('Y-m-d H:i:s'), ':show_end2' => $show_end->format('Y-m-d H:i:s'), ':show_end3' => $show_end->format('Y-m-d H:i:s')), 'all');
}
foreach ($rows as $row) {
$start = new DateTime($row["starts"], new DateTimeZone('UTC'));
$end = new DateTime($row["ends"], new DateTimeZone('UTC'));
if ($show_start->getTimestamp() < $end->getTimestamp() && $show_end->getTimestamp() > $start->getTimestamp()) {
$overlapping = true;
break;
}
}
return $overlapping;
}
/**
* Change cueIn/cueOut values for block element
*
* @param int $pos
* position of audioclip in block
* @param string $cueIn
* new value in ss.ssssss or extent format
* @param string $cueOut
* new value in ss.ssssss or extent format
* @return boolean or pear error object
*/
public function changeClipLength($id, $cueIn, $cueOut)
{
$this->con->beginTransaction();
$errArray = array();
try {
if (is_null($cueIn) && is_null($cueOut)) {
$errArray["error"] = "Cue in and cue out are null.";
return $errArray;
}
$row = CcBlockcontentsQuery::create()->joinWith(CcFilesPeer::OM_CLASS)->filterByPrimaryKey($id)->findOne($this->con);
if (is_null($row)) {
throw new Exception("Block item does not exist.");
}
$oldCueIn = $row->getDBCuein();
$oldCueOut = $row->getDbCueout();
$fadeIn = $row->getDbFadein();
$fadeOut = $row->getDbFadeout();
$file = $row->getCcFiles($this->con);
$origLength = $file->getDbLength();
if (!is_null($cueIn) && !is_null($cueOut)) {
if ($cueOut === "") {
$cueOut = $origLength;
}
$sql = "SELECT :cue_out::INTERVAL > :orig_length::INTERVAL";
$params = array(':cue_out' => $cueOut, ':orig_length' => $origLength);
$result = Application_Common_Database::prepareAndExecute($sql, $params, 'column');
if ($result) {
$errArray["error"] = "Can't set cue out to be greater than file length.";
return $errArray;
}
$sql = "SELECT :cue_in::INTERVAL > :cue_out::INTERVAL";
$params = array(':cue_in' => $cueIn, ':cue_out' => $cueOut);
$result = Application_Common_Database::prepareAndExecute($sql, $params, 'column');
if ($result) {
$errArray["error"] = "Can't set cue in to be larger than cue out.";
return $errArray;
}
$sql = "SELECT :cue_out::INTERVAL - :cue_in::INTERVAL";
$result = Application_Common_Database::prepareAndExecute($sql, $params, 'column');
$cliplength = $result;
$row->setDbCuein($cueIn);
$row->setDbCueout($cueOut);
$row->setDBCliplength($cliplength);
} elseif (!is_null($cueIn)) {
$sql = "SELECT :cue_in::INTERVAL > :old_cue_out::INTERVAL";
$params = array(':cue_in' => $cueIn, ':old_cue_out' => $oldCueOut);
$result = Application_Common_Database::prepareAndExecute($sql, $params, 'column');
if ($result) {
$errArray["error"] = "Can't set cue in to be larger than cue out.";
return $errArray;
}
$sql = "SELECT :old_cue_out::INTERVAL - :cue_in::INTERVAL";
$result = Application_Common_Database::prepareAndExecute($sql, $params, 'column');
$cliplength = $result;
$row->setDbCuein($cueIn);
$row->setDBCliplength($cliplength);
} elseif (!is_null($cueOut)) {
if ($cueOut === "") {
$cueOut = $origLength;
}
$sql = "SELECT :cue_out::INTERVAL > :orig_length::INTERVAL";
$params = array(':cue_out' => $cueOut, ':orig_length' => $origLength);
$result = Application_Common_Database::prepareAndExecute($sql, $params, 'column');
if ($result) {
$errArray["error"] = "Can't set cue out to be greater than file length.";
return $errArray;
}
$sql = "SELECT :cue_out::INTERVAL < :old_cue_in::INTERVAL";
$params = array(':cue_out' => $cueOut, ':old_cue_in' => $oldCueIn);
$result = Application_Common_Database::prepareAndExecute($sql, $params, 'column');
if ($result) {
$errArray["error"] = "Can't set cue out to be smaller than cue in.";
return $errArray;
}
$sql = "SELECT :cue_out::INTERVAL - :old_cue_in::INTERVAL";
$result = Application_Common_Database::prepareAndExecute($sql, $params, 'column');
$cliplength = $result;
$row->setDbCueout($cueOut);
$row->setDBCliplength($cliplength);
}
$cliplength = $row->getDbCliplength();
$sql = "SELECT :fade_in::INTERVAL > :clip_length::INTERVAL";
$params = array(':fade_in' => $fadeIn, ':clip_length' => $cliplength);
$result = Application_Common_Database::prepareAndExecute($sql, $params, 'column');
if ($result) {
$fadeIn = $cliplength;
$row->setDbFadein($fadeIn);
}
$sql = "SELECT :fade_out::INTERVAL > :clip_length::INTERVAL";
$params = array(':fade_out' => $fadeOut, ':clip_length' => $cliplength);
$result = Application_Common_Database::prepareAndExecute($sql, $params, 'column');
if ($result) {
$fadeOut = $cliplength;
$row->setDbFadein($fadeOut);
}
$row->save($this->con);
$this->block->setDbMtime(new DateTime("now", new DateTimeZone("UTC")));
$this->block->save($this->con);
$this->con->commit();
} catch (Exception $e) {
$this->con->rollback();
throw $e;
}
return array("cliplength" => $cliplength, "cueIn" => $cueIn, "cueOut" => $cueOut, "length" => $this->getUnformatedLength(), "fadeIn" => $fadeIn, "fadeOut" => $fadeOut);
}
public static function findEntries($con, $displayColumns, $fromTable, $data, $dataProp = "aaData")
{
$where = array();
/* Holds the parameters for binding after the statement has been
prepared */
$params = array();
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();
// 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);
}
}
$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;
} 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 array("sEcho" => intval($data["sEcho"]), "iTotalDisplayRecords" => intval($totalDisplayRows), "iTotalRecords" => intval($totalRows), $dataProp => $results);
}
public static function getAllPlaylistStreams()
{
$sql = <<<SQL
SELECT distinct(stream_id)
FROM cc_playlistcontents
WHERE stream_id is not null
SQL;
$streams = Application_Common_Database::prepareAndExecute($sql);
$real_streams = array();
foreach ($streams as $s) {
$real_streams[] = $s['stream_id'];
}
return $real_streams;
}
public static function GetEndTimeOfNextShowWithLiveDJ($p_startTime, $p_endTime)
{
$sql = <<<SQL
SELECT ends
FROM cc_show_instances AS si
JOIN cc_show AS sh ON si.show_id = sh.id
WHERE si.ends > :startTime::TIMESTAMP
AND si.ends < :endTime::TIMESTAMP
AND (sh.live_stream_using_airtime_auth
OR live_stream_using_custom_auth)
ORDER BY si.ends
SQL;
return Application_Common_Database::prepareAndExecute($sql, array(':startTime' => $p_startTime, ':endTime' => $p_endTime), 'all');
}
public static function SetEndTime($state, $dateTime, $override = false)
{
try {
$dj_live = Application_Model_Preference::GetSourceSwitchStatus('live_dj');
$master_live = Application_Model_Preference::GetSourceSwitchStatus('master_dj');
if ($dj_live == 'off' && $master_live == 'off' || $state == 'S' || $override) {
$sql = "SELECT id, state from cc_live_log" . " where id in (select max(id) from cc_live_log)";
$row = Application_Common_Database::prepareAndExecute($sql, array(), Application_Common_Database::SINGLE);
/* Only set end time if state recevied ($state)
* is the last row in cc_live_log
*/
if ($row['state'] == $state) {
$update_sql = "UPDATE CC_LIVE_LOG" . " SET end_time = :end" . " WHERE id = :id";
$params = array(':end' => $dateTime->format("Y-m-d H:i:s"), ':id' => $row['id']);
Application_Common_Database::prepareAndExecute($update_sql, $params, Application_Common_Database::EXECUTE);
}
//If live broadcasting is off, turn scheduled play on
$scheduled = Application_Model_Preference::GetSourceSwitchStatus('scheduled_play');
if ($state == 'L' && $scheduled == 'on' && !$override) {
self::SetNewLogTime('S', $dateTime);
}
}
} catch (Exception $e) {
header('HTTP/1.0 503 Service Unavailable');
Logging::info("SetEndTime - Could not connect to database.");
exit;
}
}
