/** * Get all the show instances in the given time range (inclusive). * * @param DateTime $start_timestamp * In UTC time. * @param DateTime $end_timestamp * In UTC time. * @param unknown_type $excludeInstance * @param boolean $onlyRecord * @return array */ public static function getShows($start_timestamp, $end_timestamp, $onlyRecord = FALSE) { //UTC DateTime object $showsPopUntil = Application_Model_Preference::GetShowsPopulatedUntil(); //if application is requesting shows past our previous populated until date, generate shows up until this point. if (is_null($showsPopUntil) || $showsPopUntil->getTimestamp() < $end_timestamp->getTimestamp()) { Application_Model_Show::populateAllShowsInRange($showsPopUntil, $end_timestamp); Application_Model_Preference::SetShowsPopulatedUntil($end_timestamp); } $sql = <<<SQL SELECT si1.starts AS starts, si1.ends AS ends, si1.record AS record, si1.rebroadcast AS rebroadcast, si2.starts AS parent_starts, si1.instance_id AS record_id, si1.show_id AS show_id, show.name AS name, show.color AS color, show.background_color AS background_color, si1.file_id AS file_id, si1.id AS instance_id, si1.created AS created, si1.last_scheduled AS last_scheduled, si1.time_filled AS time_filled, f.soundcloud_id FROM cc_show_instances AS si1 LEFT JOIN cc_show_instances AS si2 ON si1.instance_id = si2.id LEFT JOIN cc_show AS show ON show.id = si1.show_id LEFT JOIN cc_files AS f ON f.id = si1.file_id WHERE si1.modified_instance = FALSE SQL; //only want shows that are starting at the time or later. $start_string = $start_timestamp->format("Y-m-d H:i:s"); $end_string = $end_timestamp->format("Y-m-d H:i:s"); if ($onlyRecord) { $sql .= " AND (si1.starts >= :start::TIMESTAMP AND si1.starts < :end::TIMESTAMP)"; $sql .= " AND (si1.record = 1)"; return Application_Common_Database::prepareAndExecute($sql, array(':start' => $start_string, ':end' => $end_string), 'all'); } else { $sql .= " " . <<<SQL AND ((si1.starts >= :start1::TIMESTAMP AND si1.starts < :end1::TIMESTAMP) OR (si1.ends > :start2::TIMESTAMP AND si1.ends <= :end2::TIMESTAMP) OR (si1.starts <= :start3::TIMESTAMP AND si1.ends >= :end3::TIMESTAMP)) SQL; return Application_Common_Database::prepareAndExecute($sql, array('start1' => $start_string, 'start2' => $start_string, 'start3' => $start_string, 'end1' => $end_string, 'end2' => $end_string, 'end3' => $end_string), 'all'); } }