public static function GetContentList($parameters)
{
$logger = \Swiftriver\Core\Setup::GetLogger();
$logger->log("Core::Modules::DataContext::MySql_V2::DataContext::GetContentList [Method invoked]", \PEAR_LOG_DEBUG);
$baseSql = "from SC_Content content left join SC_Sources source on content.sourceId = source.id";
$filters = array();
$time = \key_exists("time", $parameters) ? $parameters["time"] : \time();
$filters[] = "content.date < {$time}";
$state = key_exists("state", $parameters) ? $parameters["state"] : null;
if ($state != null) {
$filters[] = "content.state = '{$state}'";
}
$minVeracity = key_exists("minVeracity", $parameters) ? $parameters["minVeracity"] : null;
if ($minVeracity != null || $minVeracity === 0) {
$filters[] = $minVeracity === 0 ? "(source.score >= {$minVeracity} OR source.score IS NULL)" : "source.score >= {$minVeracity}";
}
$maxVeracity = key_exists("maxVeracity", $parameters) ? $parameters["maxVeracity"] : null;
if ($maxVeracity != null) {
$filters[] = $minVeracity === 0 ? "(source.score <= {$maxVeracity} OR source.score IS NULL)" : "source.score <= {$maxVeracity}";
}
$type = key_exists("type", $parameters) ? $parameters["type"] : null;
if ($type != null) {
$filters[] = "source.type = '{$type}'";
}
$subType = key_exists("subType", $parameters) ? $parameters["subType"] : null;
if ($subType != null) {
$filters[] = "source.subType = '{$subType}'";
}
$source = key_exists("source", $parameters) ? $parameters["source"] : null;
if ($source != null) {
$filters[] = "source.id = '{$source}'";
}
$tags = \key_exists("tags", $parameters) ? $parameters["tags"] : null;
if ($tags != null && \is_array($tags)) {
foreach ($tags as $tag) {
$filters[] = "content.id in (select ct.contentId from SC_Content_Tags ct join SC_Tags t on ct.tagId = t.id where t.text = '{$tag}')";
}
}
$pageSize = key_exists("pageSize", $parameters) ? $parameters["pageSize"] : null;
$pageStart = key_exists("pageStart", $parameters) ? $parameters["pageStart"] : null;
$pagination = $pageSize != null ? "limit " . ($pageStart == null ? "0" : $pageStart) . ", {$pageSize}" : "";
$orderBy = "date desc";
$sql = $baseSql;
for ($i = 0; $i < count($filters); $i++) {
$addition = $i == 0 ? "WHERE" : "AND";
$sql .= " " . $addition . " " . $filters[$i];
}
$countSql = "select count(content.id) " . $sql;
try {
$db = self::PDOConnection();
$countStatement = $db->prepare($countSql);
$result = $countStatement->execute();
if ($result === false) {
$logger->log("Core::Modules::DataContext::MySQL_V2::DataContext::GetContentList [An Exception was thrown by the PDO framwork]", \PEAR_LOG_ERR);
$errorInfo = $countStatement->errorInfo();
$errorMessage = $errorInfo[2];
$logger->log("Core::Modules::DataContext::MySQL_V2::DataContext::GetContentList [{$errorMessage}]", \PEAR_LOG_ERR);
}
$totalCount = (int) $countStatement->fetchColumn();
$selectSql = "select content.id " . $sql . " order by content." . $orderBy . " " . $pagination;
$navigation = array();
$tagsSql = "SELECT t.text as name, t.text as id, count(t.text) as count FROM SC_Tags t join SC_Content_Tags ct ON t.id = ct.tagId WHERE ct.contentId in (SELECT content.id " . $sql . ") GROUP BY t.text ORDER BY count DESC";
$tagsStatement = $db->prepare($tagsSql);
$tagsStatement->execute();
$results = $tagsStatement->fetchAll(\PDO::FETCH_ASSOC);
$types = array("type" => "list", "key" => "tags", "selected" => $type != null, "facets" => $results);
$navigation["Tags"] = $types;
if ($subType == null) {
$typeSql = "select source.type as name, source.type as id, count(source.type) as count " . $sql . " group by source.type order by count desc";
$typeStatement = $db->prepare($typeSql);
$typeStatement->execute();
$results = $typeStatement->fetchAll(\PDO::FETCH_ASSOC);
$types = array("type" => "list", "key" => "type", "selected" => $type != null, "facets" => $results);
$navigation["Channels"] = $types;
}
if ($type != null && $source == null) {
$subTypeSql = "select source.subType as name, source.subType as id, count(source.subType) as count " . $sql . " group by source.subType order by count desc";
$subTypeStatement = $db->prepare($subTypeSql);
$subTypeStatement->execute();
$results = $subTypeStatement->fetchAll(\PDO::FETCH_ASSOC);
$subTypes = array("type" => "list", "key" => "subType", "selected" => $subType != null, "facets" => $results);
$navigation["Sub Channels"] = $subTypes;
}
if ($subType != null && $type != null) {
$sourceSql = "select source.name as name, source.textId as id, count(source.name) as count " . $sql . " group by source.name order by count desc";
$sourceStatement = $db->prepare($sourceSql);
$sourceStatement->execute();
$results = $sourceStatement->fetchAll(\PDO::FETCH_ASSOC);
$sources = array("type" => "list", "key" => "source", "selected" => $source != null, "facets" => $results);
$navigation["Sources"] = $sources;
}
$ids = array();
foreach ($db->query($selectSql) as $row) {
$ids[] = $row[0];
}
$content = self::GetContent($ids, $orderBy);
} catch (\PDOException $e) {
$logger->log("Core::Modules::DataContext::MySQL_V2::DataContext::GetContentList [An Exception was thrown:]", \PEAR_LOG_ERR);
$logger->log("Core::Modules::DataContext::MySQL_V2::DataContext::GetContentList [{$e}]", \PEAR_LOG_ERR);
}
$logger->log("Core::Modules::DataContext::MySql_V2::DataContext::GetContentList [Method finished]", \PEAR_LOG_DEBUG);
return array("totalCount" => $totalCount, "contentItems" => $content, "navigation" => $navigation);
}