<commit_before><?php

require_once 'formatters/LengthFormatter.php';

class Application_Service_HistoryService
{
	private $con;
	private $timezone;
	
	const TEMPLATE_TYPE_ITEM = "item";
	const TEMPLATE_TYPE_AGGREGATE = "aggregate";

	private $mDataPropMap = array (
		"artist" => "artist_name",
		"title" => "track_title",
		"played" => "played",
		"length" => "length",
		"composer" => "composer",
		"copyright" => "copyright",
	    "starts" => "starts",
	    "ends" => "ends"
	);

	public function __construct()
	{
		$this->con = isset($con) ? $con : Propel::getConnection(CcPlayoutHistoryPeer::DATABASE_NAME);
		$this->timezone = Application_Model_Preference::GetTimezone();
	}

	/*
	 * map front end mDataProp labels to proper column names for searching etc.
	*/
	private function translateColumns($opts)
	{
		for ($i = 0; $i < $opts["iColumns"]; $i++) {

			if ($opts["bSearchable_{$i}"] === "true") {
				$opts["mDataProp_{$i}"] = $this->mDataPropMap[$opts["mDataProp_{$i}"]];
			}
		}
	}
	
	//opts is from datatables.
	public function getPlayedItemData($startDT, $endDT, $opts)
	{
		$mainSqlQuery = "";
		$paramMap = array();
		$sqlTypes = $this->getSqlTypes();
		
		$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->getConfiguredItemTemplate();
		$fields = $template["fields"];
		$required = $this->mandatoryItemFields();		
		
		$fields_filemd = array();
		$filemd_keys = array();
		$fields_general = array();
		$general_keys = array();
		
		foreach ($fields as $index=>$field) {
			
			if (in_array($field["name"], $required)) {
				continue;
			}
			
			if ($field["isFileMd"]) {
				$fields_filemd[] = $field;
				$filemd_keys[] = $field["name"];
			}
			else {
				$fields_general[] = $field;
				$general_keys[] = $field["name"];
			}
		}
		
		$historyRange = "(".
		"SELECT history.starts, history.ends, history.id AS history_id".
		" FROM cc_playout_history as history".
		" WHERE history.starts >= :starts and history.starts < :ends".
		") AS history_range";
				
		$manualMeta = "(".
		"SELECT %KEY%.value AS %KEY%, %KEY%.history_id".
		" FROM (".
		" SELECT * from cc_playout_history_metadata AS phm WHERE phm.key = :meta_%KEY%".
		" ) AS %KEY%".
		" ) AS %KEY%_filter";
		
		$mainSelect = array("history_range.starts", "history_range.ends", "history_range.history_id");
		$mdFilters = array();
		
		$numFileMdFields = count($fields_filemd);
		
		if ($numFileMdFields > 0) {
		
			//these 3 selects are only needed if $fields_filemd has some fields.
			$fileSelect = array("history_file.history_id");
			$nonNullFileSelect = array("file.id as file_id");
			$nullFileSelect = array("null_file.history_id");
			
			$fileMdFilters = array();
			
			//populate the different dynamic selects with file info.
			for ($i = 0; $i < $numFileMdFields; $i++) {
				
				$field = $fields_filemd[$i];
				$key = $field["name"];
				$type = $sqlTypes[$field["type"]];
				
				$fileSelect[] = "file_md.{$key}::{$type}";
				$nonNullFileSelect[] = "file.{$key}::{$type}";
				$nullFileSelect[] = "{$key}_filter.{$key}::{$type}";
				$mainSelect[] = "file_info.{$key}::{$type}";
				
				$fileMdFilters[] = str_replace("%KEY%", $key, $manualMeta);
				$paramMap["meta_{$key}"] = $key;
			}
			
			//the files associated with scheduled playback in Airtime.
			$historyFile = "(".
			"SELECT history.id AS history_id, history.file_id".
			" FROM cc_playout_history AS history".
			" WHERE history.file_id IS NOT NULL".
			") AS history_file";
			
			$fileMd = "(".
			"SELECT %NON_NULL_FILE_SELECT%".
			" FROM cc_files AS file".
			") AS file_md";
			
			$fileMd = str_replace("%NON_NULL_FILE_SELECT%", join(", ", $nonNullFileSelect), $fileMd);
			
			//null files are from manually added data (filling in webstream info etc)
			$nullFile = "(".
			"SELECT history.id AS history_id".
			" FROM cc_playout_history AS history".
			" WHERE history.file_id IS NULL".
			") AS null_file";
			
			
			//----------------------------------
			//building the file inner query
			
			$fileSqlQuery = 
			"SELECT ".join(", ", $fileSelect).
			" FROM {$historyFile}".
			" LEFT JOIN {$fileMd} USING (file_id)".
			" UNION".
			" SELECT ".join(", ", $nullFileSelect).
			" FROM {$nullFile}";
			
			foreach ($fileMdFilters as $filter) {
				
				$fileSqlQuery.=
				" LEFT JOIN {$filter} USING(history_id)";
			}
				
		}
		
		for ($i = 0, $len = count($fields_general); $i < $len; $i++) {
			
			$field = $fields_general[$i];
			$key = $field["name"];
			$type = $sqlTypes[$field["type"]];
			
			$mdFilters[] = str_replace("%KEY%", $key, $manualMeta);
			$paramMap["meta_{$key}"] = $key;
			$mainSelect[] = "{$key}_filter.{$key}::{$type}";
		}
		
		$mainSqlQuery.=
		"SELECT ".join(", ", $mainSelect).
		" FROM {$historyRange}";
		
		if (isset($fileSqlQuery)) {
			
			$mainSqlQuery.=
			" LEFT JOIN ( {$fileSqlQuery} ) as file_info USING(history_id)";
		}
		
		foreach ($mdFilters as $filter) {
		
			$mainSqlQuery.=
			" LEFT JOIN {$filter} USING(history_id)";
		}
		
		//------------------------------------------------------------------------
		//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];
			
			if (in_array($key, $required)) {

				$orderBys[] = "history_range.{$key} {$sortDir}";
			}
			else if (in_array($key, $filemd_keys)) {
			
				$orderBys[] = "file_info.{$key} {$sortDir}";
			}
			else if (in_array($key, $general_keys)) {

				$orderBys[] = "{$key}_filter.{$key} {$sortDir}";
			}
			else {
				throw new Exception("Error: $key is not part of the template.");
			}
		}
		
		if ($numOrderColumns > 0) {
			
			$orders = join(", ", $orderBys);
			
			$mainSqlQuery.=
			" ORDER BY {$orders}";
		}
			
		$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());
			Logging::info($msg);
			throw new Exception("Error: $msg");
		}
		
		$totalRows = count($rows);
		
		//-----------------------------------------------------------------------
		//processing results.
		
		$timezoneUTC = new DateTimeZone("UTC");
		$timezoneLocal = new DateTimeZone($this->timezone);
		
		//need to display the results in the station's timezone.
		foreach ($rows as $index => &$result) {
		
			$dateTime = new DateTime($result["starts"], $timezoneUTC);
			$dateTime->setTimezone($timezoneLocal);
			$result["starts"] = $dateTime->format("Y-m-d H:i:s");
		
			$dateTime = new DateTime($result["ends"], $timezoneUTC);
			$dateTime->setTimezone($timezoneLocal);
			$result["ends"] = $dateTime->format("Y-m-d H:i:s");
		}
		<commit_msg>pagination working properly (but not with filtering, have removed serach for now anyways.)<commit_after><?php

require_once 'formatters/LengthFormatter.php';

class Application_Service_HistoryService
{
	private $con;
	private $timezone;
	
	const TEMPLATE_TYPE_ITEM = "item";
	const TEMPLATE_TYPE_AGGREGATE = "aggregate";

	private $mDataPropMap = array (
		"artist" => "artist_name",
		"title" => "track_title",
		"played" => "played",
		"length" => "length",
		"composer" => "composer",
		"copyright" => "copyright",
	    "starts" => "starts",
	    "ends" => "ends"
	);

	public function __construct()
	{
		$this->con = isset($con) ? $con : Propel::getConnection(CcPlayoutHistoryPeer::DATABASE_NAME);
		$this->timezone = Application_Model_Preference::GetTimezone();
	}

	/*
	 * map front end mDataProp labels to proper column names for searching etc.
	*/
	private function translateColumns($opts)
	{
		for ($i = 0; $i < $opts["iColumns"]; $i++) {

			if ($opts["bSearchable_{$i}"] === "true") {
				$opts["mDataProp_{$i}"] = $this->mDataPropMap[$opts["mDataProp_{$i}"]];
			}
		}
	}
	
	//opts is from datatables.
	public function getPlayedItemData($startDT, $endDT, $opts)
	{
		$mainSqlQuery = "";
		$paramMap = array();
		$sqlTypes = $this->getSqlTypes();
		
		$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->getConfiguredItemTemplate();
		$fields = $template["fields"];
		$required = $this->mandatoryItemFields();		
		
		$fields_filemd = array();
		$filemd_keys = array();
		$fields_general = array();
		$general_keys = array();
		
		foreach ($fields as $index=>$field) {
			
			if (in_array($field["name"], $required)) {
				continue;
			}
			
			if ($field["isFileMd"]) {
				$fields_filemd[] = $field;
				$filemd_keys[] = $field["name"];
			}
			else {
				$fields_general[] = $field;
				$general_keys[] = $field["name"];
			}
		}
		
		$historyRange = "(".
		"SELECT history.starts, history.ends, history.id AS history_id".
		" FROM cc_playout_history as history".
		" WHERE history.starts >= :starts and history.starts < :ends".
		") AS history_range";
				
		$manualMeta = "(".
		"SELECT %KEY%.value AS %KEY%, %KEY%.history_id".
		" FROM (".
		" SELECT * from cc_playout_history_metadata AS phm WHERE phm.key = :meta_%KEY%".
		" ) AS %KEY%".
		" ) AS %KEY%_filter";
		
		$mainSelect = array("history_range.starts", "history_range.ends", "history_range.history_id");
		$mdFilters = array();
		
		$numFileMdFields = count($fields_filemd);
		
		if ($numFileMdFields > 0) {
		
			//these 3 selects are only needed if $fields_filemd has some fields.
			$fileSelect = array("history_file.history_id");
			$nonNullFileSelect = array("file.id as file_id");
			$nullFileSelect = array("null_file.history_id");
			
			$fileMdFilters = array();
			
			//populate the different dynamic selects with file info.
			for ($i = 0; $i < $numFileMdFields; $i++) {
				
				$field = $fields_filemd[$i];
				$key = $field["name"];
				$type = $sqlTypes[$field["type"]];
				
				$fileSelect[] = "file_md.{$key}::{$type}";
				$nonNullFileSelect[] = "file.{$key}::{$type}";
				$nullFileSelect[] = "{$key}_filter.{$key}::{$type}";
				$mainSelect[] = "file_info.{$key}::{$type}";
				
				$fileMdFilters[] = str_replace("%KEY%", $key, $manualMeta);
				$paramMap["meta_{$key}"] = $key;
			}
			
			//the files associated with scheduled playback in Airtime.
			$historyFile = "(".
			"SELECT history.id AS history_id, history.file_id".
			" FROM cc_playout_history AS history".
			" WHERE history.file_id IS NOT NULL".
			") AS history_file";
			
			$fileMd = "(".
			"SELECT %NON_NULL_FILE_SELECT%".
			" FROM cc_files AS file".
			") AS file_md";
			
			$fileMd = str_replace("%NON_NULL_FILE_SELECT%", join(", ", $nonNullFileSelect), $fileMd);
			
			//null files are from manually added data (filling in webstream info etc)
			$nullFile = "(".
			"SELECT history.id AS history_id".
			" FROM cc_playout_history AS history".
			" WHERE history.file_id IS NULL".
			") AS null_file";
			
			
			//----------------------------------
			//building the file inner query
			
			$fileSqlQuery = 
			"SELECT ".join(", ", $fileSelect).
			" FROM {$historyFile}".
			" LEFT JOIN {$fileMd} USING (file_id)".
			" UNION".
			" SELECT ".join(", ", $nullFileSelect).
			" FROM {$nullFile}";
			
			foreach ($fileMdFilters as $filter) {
				
				$fileSqlQuery.=
				" LEFT JOIN {$filter} USING(history_id)";
			}
				
		}
		
		for ($i = 0, $len = count($fields_general); $i < $len; $i++) {
			
			$field = $fields_general[$i];
			$key = $field["name"];
			$type = $sqlTypes[$field["type"]];
			
			$mdFilters[] = str_replace("%KEY%", $key, $manualMeta);
			$paramMap["meta_{$key}"] = $key;
			$mainSelect[] = "{$key}_filter.{$key}::{$type}";
		}
		
		$mainSqlQuery.=
		"SELECT ".join(", ", $mainSelect).
		" FROM {$historyRange}";
		
		if (isset($fileSqlQuery)) {
			
			$mainSqlQuery.=
			" LEFT JOIN ( {$fileSqlQuery} ) as file_info USING(history_id)";
		}
		
		foreach ($mdFilters as $filter) {
		
			$mainSqlQuery.=
			" LEFT JOIN {$filter} USING(history_id)";
		}
		
		//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();
			Logging::info("Total Rows {$totalRows}");
		}
		else {
			$msg = implode(',', $stmt->errorInfo());
			Logging::info($msg);
			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];
			
			if (in_array($key, $required)) {

				$orderBys[] = "history_range.{$key} {$sortDir}";
			}
			else if (in_array($key, $filemd_keys)) {
			
				$orderBys[] = "file_info.{$key} {$sortDir}";
			}
			else if (in_array($key, $general_keys)) {

				$orderBys[] = "{$key}_filter.{$key} {$sortDir}";
			}
			else {
				throw new Exception("Error: $key is not part of the template.");
			}
		}
		
		if ($numOrderColumns > 0) {
			
			$orders = join(", ", $orderBys);
			
			$mainSqlQuery.=
			" ORDER BY {$orders}";
		}
		
		$displayLength = intval($opts["iDisplayLength"]);
		//limit the results returned.
		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());
			Logging::info($msg);
			throw new Exception("Error: $msg");
		}
		
		//-----------------------------------------------------------------------
		//processing results.
		
		$timezoneUTC = new DateTimeZone("UTC");
		$timezoneLocal = new DateTimeZone($this->timezone);
		
		//need to display the results in the station's timezone.
		foreach ($rows as $index => &$result) {
		
			$dateTime = new DateTime($result["starts"], $timezoneUTC);
			$dateTime->setTimezone($timezoneLocal);
			$result["starts"] = $dateTime->format("Y-m-d H:i:s");
		
			$dateTime = new DateTime($result["ends"], $timezoneUTC);
			$dateTime->setTimezone($timezoneLocal);
			$result["ends"] = $dateTime->format("Y-m-d H:i:s");
		}
		
{'fraction_non_alphanumeric': 0.21165101401932762, 'fraction_numerical': 0.0010208248264597796, 'mean_word_length': 3.1047486033519553, 'pattern_counts': {'":': 0, '<': 15, '<?xml version=': 0, '>': 76, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6433938', 'n_tokens_mistral': 6132, 'n_tokens_neox': 5099, 'n_words': 1064}