Simple class providing a standard interface to fetch and store
list of objects (articles, issues, sections, attachments, etc)
generated by static methods in several classes (e.g. Articles::GetList(),
ArticleTypeField::FetchFields()).
/**
* Gets an issues list based on the given parameters.
*
* @param integer $p_context_id
* The Context Box Identifier
* @param string $p_order
* An array of columns and directions to order by
* @param integer $p_start
* The record number to start the list
* @param integer $p_limit
* The offset. How many records from $p_start will be retrieved.
* @param integer $p_count
* The total count of the elements; this count is computed without
* applying the start ($p_start) and limit parameters ($p_limit)
*
* @return array $issuesList
* An array of Issue objects
*/
public static function GetList($p_context_id, $p_order = null, $p_start = 0, $p_limit = 0, &$p_count, $p_skipCache = false)
{
global $g_ado_db;
if (!$p_skipCache && CampCache::IsEnabled()) {
$paramsArray['parameters'] = serialize($p_parameters);
$paramsArray['order'] = is_null($p_order) ? 'id desc' : $p_order;
$paramsArray['start'] = $p_start;
$paramsArray['limit'] = $p_limit;
$cacheListObj = new CampCacheList($paramsArray, __METHOD__);
$issuesList = $cacheListObj->fetchFromCache();
if ($issuesList !== false && is_array($issuesList)) {
return $issuesList;
}
}
$returnArray = array();
$sql = 'SELECT fk_article_no FROM context_articles
WHERE fk_context_id = ' . $p_context_id . '
ORDER BY id desc';
$rows = $g_ado_db->GetAll($sql);
if (is_array($rows)) {
foreach ($rows as $row) {
$returnArray[] = $row['fk_article_no'];
}
}
$p_count = count($returnArray);
return array_reverse($returnArray);
}
/**
* Gets an issues list based on the given parameters.
*
* @param integer $p_context_id
* The Context Box Identifier
* @param string $p_order
* An array of columns and directions to order by
* @param integer $p_start
* The record number to start the list
* @param integer $p_limit
* The offset. How many records from $p_start will be retrieved.
* @param integer $p_count
* The total count of the elements; this count is computed without
* applying the start ($p_start) and limit parameters ($p_limit)
*
* @return array $issuesList
* An array of Issue objects
*/
public static function GetList(array $params, $p_order = null, $p_start = 0, $p_limit = 0, &$p_count, $p_skipCache = false)
{
global $g_ado_db;
if (!$p_skipCache && CampCache::IsEnabled()) {
$paramsArray['parameters'] = serialize($params);
$paramsArray['order'] = is_null($p_order) ? 'id' : $p_order;
$paramsArray['start'] = $p_start;
$paramsArray['limit'] = $p_limit;
$cacheListObj = new CampCacheList($paramsArray, __METHOD__);
$issuesList = $cacheListObj->fetchFromCache();
if ($issuesList !== false && is_array($issuesList)) {
return $issuesList;
}
}
if (isset($params['role']) && $params['role'] == 'child') {
$sql = 'SELECT b.fk_article_no FROM context_boxes b, Articles a0' . ' WHERE a0.Number = b.fk_article_no AND ' . ' a0.Type = "dossier" AND ' . ' b.id IN (SELECT c.fk_context_id ' . ' FROM Articles a, context_articles c ' . ' WHERE c.fk_article_no = ' . $params['article'] . ' AND a.Number = c.fk_article_no)' . ' ORDER BY a0.PublishDate DESC';
} else {
$sql = 'SELECT fk_article_no FROM context_articles' . ' WHERE fk_context_id = ' . $params['context_box'] . ' ORDER BY id';
}
if ($p_limit > 0) {
$sql .= ' LIMIT ' . $p_limit;
}
$returnArray = array();
$rows = $g_ado_db->GetAll($sql);
if (is_array($rows)) {
foreach ($rows as $row) {
$returnArray[] = $row['fk_article_no'];
}
}
$p_count = count($returnArray);
return $returnArray;
}
/**
* Returns an articles list based on the given parameters.
*
* @param array $p_parameters
* An array of ComparisonOperation objects
* @param string $p_order
* An array of columns and directions to order by
* @param integer $p_start
* The record number to start the list
* @param integer $p_limit
* The offset. How many records from $p_start will be retrieved.
* @param integer $p_count
* The total count of the elements; this count is computed without
* applying the start ($p_start) and limit parameters ($p_limit)
*
* @return array $articlesList
* An array of Article objects
*/
public static function GetList(array $p_parameters, $p_order = null,
$p_start = 0, $p_limit = 0, &$p_count, $p_skipCache = false)
{
global $g_ado_db;
if (!$p_skipCache && CampCache::IsEnabled()) {
$paramsArray['parameters'] = serialize($p_parameters);
$paramsArray['order'] = (is_null($p_order)) ? 'null' : $p_order;
$paramsArray['start'] = $p_start;
$paramsArray['limit'] = $p_limit;
$cacheListObj = new CampCacheList($paramsArray, __METHOD__);
$articlesList = $cacheListObj->fetchFromCache();
if ($articlesList !== false && is_array($articlesList)) {
return $articlesList;
}
}
$matchAllTopics = false;
$hasTopics = array();
$hasNotTopics = array();
$selectClauseObj = new SQLSelectClause();
$otherTables = array();
// sets the name of the table for the this database object
$tmpArticle = new Article();
$articleTable = $tmpArticle->getDbTableName();
$selectClauseObj->setTable($articleTable);
unset($tmpArticle);
$languageId = null;
// parses the given parameters in order to build the WHERE part of
// the SQL SELECT sentence
foreach ($p_parameters as $param) {
$comparisonOperation = self::ProcessListParameters($param, $otherTables);
$leftOperand = strtolower($comparisonOperation['left']);
if ($leftOperand == 'idlanguage' && $comparisonOperation['symbol'] == '=') {
$languageId = $comparisonOperation['right'];
}
if (array_key_exists($leftOperand, Article::$s_regularParameters)) {
// regular article field, having a direct correspondent in the
// Article table fields
$whereCondition = Article::$s_regularParameters[$leftOperand]
. ' ' . $comparisonOperation['symbol']
. " '" . $g_ado_db->escape($comparisonOperation['right']) . "' ";
if ($leftOperand == 'reads'
&& strstr($comparisonOperation['symbol'], '=') !== false
&& $comparisonOperation['right'] == 0) {
$selectClauseObj->addConditionalWhere($whereCondition);
$isNullCond = Article::$s_regularParameters[$leftOperand]
. ' IS NULL';
$selectClauseObj->addConditionalWhere($isNullCond);
} elseif ($leftOperand == 'type' && $comparisonOperation['symbol'] == '=' ) {
$selectClauseObj->addConditionalWhere($whereCondition);
} else {
$selectClauseObj->addWhere($whereCondition);
}
} elseif ($leftOperand == 'matchalltopics') {
// set the matchAllTopics flag
$matchAllTopics = true;
} elseif ($leftOperand == 'topic') {
// add the topic to the list of match/do not match topics depending
// on the operator
$topic = new Topic($comparisonOperation['right']);
if ($topic->exists()) {
$topicIds = $topic->getSubtopics(true, 0);
$topicIds[] = $comparisonOperation['right'];
if ($comparisonOperation['symbol'] == '=') {
$hasTopics[] = $topicIds;
} else {
$hasNotTopics[] = $topicIds;
}
}
} elseif ($leftOperand == 'author') {
$otherTables['ArticleAuthors'] = array('__JOIN' => ',');
$author = Author::ReadName($comparisonOperation['right']);
$symbol = $comparisonOperation['symbol'];
$valModifier = strtolower($symbol) == 'like' ? '%' : '';
$firstName = $g_ado_db->escape($author['first_name']);
$lastName = $g_ado_db->escape($author['last_name']);
$whereCondition = "ArticleAuthors.fk_author_id IN
(SELECT Authors.id
FROM Authors
WHERE CONCAT(Authors.first_name, ' ', Authors.last_name) $symbol
'$valModifier$firstName $lastName$valModifier')";
$selectClauseObj->addWhere($whereCondition);
$selectClauseObj->addWhere('Articles.Number = ArticleAuthors.fk_article_number');
$selectClauseObj->addWhere('Articles.IdLanguage = ArticleAuthors.fk_language_id');
} elseif ($leftOperand == 'search_phrase') {
$searchQuery = ArticleIndex::SearchQuery($comparisonOperation['right']);
$mainClauseConstraint = "(Articles.Number, Articles.IdLanguage) IN ( $searchQuery )";
$selectClauseObj->addWhere($mainClauseConstraint);
} elseif ($leftOperand == 'location') {
$num = '[-+]?[0-9]+(?:\.[0-9]+)?';
if (preg_match("/($num) ($num), ($num) ($num)/",
trim($comparisonOperation['right']), $matches)) {
$queryLocation = Geo_Map::GetGeoSearchSQLQuery(array(
array(
'latitude' => $matches[1],
'longitude' => $matches[2],
), array(
'latitude' => $matches[3],
'longitude' => $matches[4],
),
));
$selectClauseObj->addWhere("Articles.Number IN ($queryLocation)");
}
} else {
// custom article field; has a correspondence in the X[type]
// table fields
$sqlQuery = self::ProcessCustomField($comparisonOperation, $languageId);
if (!is_null($sqlQuery)) {
$whereCondition = "Articles.Number IN (\n$sqlQuery )";
$selectClauseObj->addWhere($whereCondition);
}
}
}
if (count($hasTopics) > 0 || count($hasNotTopics) > 0) {
$typeAttributes = ArticleTypeField::FetchFields(null, null, 'topic', false,
false, false, true, $p_skipCache);
}
if (count($hasTopics) > 0) {
if ($matchAllTopics) {
foreach ($hasTopics as $topicId) {
$sqlQuery = Article::BuildTopicSelectClause($topicId, $typeAttributes);
$whereCondition = "Articles.Number IN (\n$sqlQuery )";
$selectClauseObj->addWhere($whereCondition);
}
} else {
$sqlQuery = Article::BuildTopicSelectClause($hasTopics, $typeAttributes);
$whereCondition = "Articles.Number IN (\n$sqlQuery )";
$selectClauseObj->addWhere($whereCondition);
}
}
if (count($hasNotTopics) > 0) {
$sqlQuery = Article::BuildTopicSelectClause($hasNotTopics, $typeAttributes);
$whereCondition = "Articles.Number NOT IN (\n$sqlQuery )";
$selectClauseObj->addWhere($whereCondition);
}
// create the count clause object
$countClauseObj = clone $selectClauseObj;
if (!is_array($p_order)) {
$p_order = array();
}
// sets the ORDER BY condition
$p_order = array_merge($p_order, Article::$s_defaultOrder);
$order = Article::ProcessListOrder($p_order, $otherTables, $otherWhereConditions);
foreach ($order as $orderDesc) {
$orderColumn = $orderDesc['field'];
$orderDirection = $orderDesc['dir'];
$selectClauseObj->addOrderBy($orderColumn . ' ' . $orderDirection);
}
if (count($otherTables) > 0) {
foreach ($otherTables as $table=>$fields) {
$joinType = 'LEFT JOIN';
if (isset($fields['__JOIN'])) {
$joinType = $fields['__JOIN'];
}
if (isset($fields['__TABLE_ALIAS'])) {
$tableAlias = $fields['__TABLE_ALIAS'];
$tableJoin = "\n $joinType $table AS $tableAlias \n";
} else {
$tableAlias = $table;
$tableJoin = "\n $joinType $tableAlias \n";
}
$firstCondition = true;
foreach ($fields as $parent=>$child) {
if ($parent == '__TABLE_ALIAS' || $parent == '__JOIN') {
continue;
}
$condOperator = $firstCondition ? ' ON ' : 'AND ';
if ($parent == '__CONST') {
$constTable = $child['table'];
$constField = $child['field'];
$value = $child['value'];
$negate = isset($child['negate']) ? $child['negate'] : false;
if (is_null($value)) {
$operator = $negate ? 'IS NOT' : 'IS';
$value = 'NULL';
} else {
$operator = $negate ? '!=' : '=';
$value = "'" . $g_ado_db->escape($value) . "'";
}
$tableJoin .= " $condOperator`$constTable`.`$constField` $operator $value\n";
} else {
$tableJoin .= " $condOperator`$articleTable`.`$parent` = `$tableAlias`.`$child`\n";
}
$firstCondition = false;
}
$selectClauseObj->addJoin($tableJoin);
$countClauseObj->addJoin($tableJoin);
}
}
// other where conditions needed for certain order options
foreach ($otherWhereConditions as $whereCondition) {
$selectClauseObj->addWhere($whereCondition);
$countClauseObj->addWhere($whereCondition);
}
// gets the column list to be retrieved for the database table
$selectClauseObj->addColumn('Articles.Number');
$selectClauseObj->addColumn('Articles.IdLanguage');
$countClauseObj->addColumn('COUNT(*)');
// sets the LIMIT start and offset values
$selectClauseObj->setLimit($p_start, $p_limit);
// builds the SQL query
$selectQuery = $selectClauseObj->buildQuery();
$countQuery = $countClauseObj->buildQuery();
// runs the SQL query
$articles = $g_ado_db->GetAll($selectQuery);
if (is_array($articles)) {
$p_count = $g_ado_db->GetOne($countQuery);
// builds the array of Article objects
$articlesList = array();
foreach ($articles as $article) {
$articlesList[] = new Article($article['IdLanguage'], $article['Number']);
}
} else {
$articlesList = array();
$p_count = 0;
}
// stores articles list in cache
if (!$p_skipCache && CampCache::IsEnabled()) {
$cacheListObj->storeInCache($articlesList);
}
return $articlesList;
} // fn GetList
/**
* Returns an array of fields from all article types that match
* the given conditions.
*
* @param $p_name
* if specified returns fields with the given name
* @param $p_articleType
* if specified returns fields of the given article type
* @param $p_dataType
* if specified returns the fields having the given data type
*
* @return array
*/
public static function FetchFields($p_name = null, $p_articleType = null, $p_dataType = null, $p_negateName = false, $p_negateArticleType = false, $p_negateDataType = false, $p_selectHidden = true, $p_skipCache = false)
{
global $g_ado_db;
if (!$p_skipCache && CampCache::IsEnabled()) {
$paramsArray['name'] = is_null($p_name) ? 'null' : $p_name;
$paramsArray['article_type'] = is_null($p_articleType) ? 'null' : $p_articleType;
$paramsArray['data_type'] = is_null($p_dataType) ? 'null' : $p_dataType;
$paramsArray['negate_name'] = $p_negateName == false ? 'false' : 'true';
$paramsArray['negate_article_type'] = $p_negateArticleType == false ? 'false' : 'true';
$paramsArray['negate_data_type'] = $p_negateDataType == false ? 'false' : 'true';
$paramsArray['select_hidden'] = $p_selectHidden == false ? 'false' : 'true';
$cacheListObj = new CampCacheList($paramsArray, __METHOD__);
$articleTypeFieldsList = $cacheListObj->fetchFromCache();
if ($articleTypeFieldsList !== false && is_array($articleTypeFieldsList)) {
return $articleTypeFieldsList;
}
}
$whereClauses = array();
if (isset($p_name)) {
$operator = $p_negateName ? '<>' : '=';
$whereClauses[] = "field_name {$operator} " . $g_ado_db->escape($p_name);
}
if (isset($p_articleType)) {
$operator = $p_negateArticleType ? '<>' : '=';
$whereClauses[] = "type_name {$operator} " . $g_ado_db->escape($p_articleType);
}
if (isset($p_dataType)) {
$operator = $p_negateDataType ? '<>' : '=';
$whereClauses[] = "field_type {$operator} " . $g_ado_db->escape($p_dataType);
}
if (!$p_selectHidden) {
$whereClauses[] = 'is_hidden = false';
}
$where = count($whereClauses) > 0 ? ' WHERE ' . implode(' and ', $whereClauses) : null;
$query = "SELECT * FROM `ArticleTypeMetadata` {$where} ORDER BY type_name ASC, field_weight ASC";
$rows = $g_ado_db->GetAll($query);
$fields = array();
foreach ($rows as $row) {
$field = new ArticleTypeField($row['type_name'], $row['field_name']);
if ($field->getPrintName() == '') {
$field->delete();
continue;
}
$fields[] = $field;
}
if (!$p_skipCache && CampCache::IsEnabled()) {
$cacheListObj->storeInCache($fields);
}
return $fields;
}
/**
* Returns an article topics list based on the given parameters.
*
* @param array $p_parameters
* An array of ComparisonOperation objects
* @param string $p_order
* An array of columns and directions to order by
* @param integer $p_start
* The record number to start the list
* @param integer $p_limit
* The offset. How many records from $p_start will be retrieved.
* @param integer $p_count
* The total count of the elements; this count is computed without
* applying the start ($p_start) and limit parameters ($p_limit)
*
* @return array $articleTopicsList
* An array of Topic objects
*/
public static function GetList(array $p_parameters, $p_order = null, $p_start = 0, $p_limit = 0, &$p_count, $p_skipCache = false)
{
global $g_ado_db;
if (!$p_skipCache && CampCache::IsEnabled()) {
$paramsArray['parameters'] = serialize($p_parameters);
$paramsArray['order'] = is_null($p_order) ? 'null' : $p_order;
$paramsArray['start'] = $p_start;
$paramsArray['limit'] = $p_limit;
$cacheListObj = new CampCacheList($paramsArray, __METHOD__);
$articleTopicsList = $cacheListObj->fetchFromCache();
if ($articleTopicsList !== false && is_array($articleTopicsList)) {
return $articleTopicsList;
}
}
$selectClauseObj = new SQLSelectClause();
$countClauseObj = new SQLSelectClause();
$rootTopicIds = array();
// processes the parameters
$hasArticleNr = false;
foreach ($p_parameters as $parameter) {
$comparisonOperation = self::ProcessListParameters($parameter);
if (sizeof($comparisonOperation) < 1) {
break;
}
if (strpos($comparisonOperation['left'], 'NrArticle') !== false) {
$hasArticleNr = true;
}
if (strpos($comparisonOperation['left'], 'RootTopic') !== false) {
$rootTopicIds[] = (int) $comparisonOperation['right'];
continue;
}
$whereCondition = $g_ado_db->escapeOperation($comparisonOperation);
$selectClauseObj->addWhere($whereCondition);
$countClauseObj->addWhere($whereCondition);
}
// validates whether article number was given
if ($hasArticleNr === false) {
CampTemplate::singleton()->trigger_error("missed parameter Article Number in statement list_article_topics");
return array();
}
if (count($rootTopicIds) > 0) {
$subtopicsQuery = Topic::BuildSubtopicsQueryWithoutDepth($rootTopicIds);
$whereCondition = 'TopicId IN (' . $subtopicsQuery->buildQuery() . ')';
$selectClauseObj->addWhere($whereCondition);
$countClauseObj->addWhere($whereCondition);
}
// sets the main table and columns to be fetched
$tmpArticleTopic = new ArticleTopic();
$selectClauseObj->setTable($tmpArticleTopic->getDbTableName());
$selectClauseObj->addColumn('TopicId');
$countClauseObj->setTable($tmpArticleTopic->getDbTableName());
$countClauseObj->addColumn('COUNT(*)');
unset($tmpArticleTopic);
if (!is_array($p_order)) {
$p_order = array();
}
// sets the order condition if any
foreach ($p_order as $orderColumn => $orderDirection) {
$selectClauseObj->addOrderBy($orderColumn . ' ' . $orderDirection);
}
// sets the limit
$selectClauseObj->setLimit($p_start, $p_limit);
// builds the query and executes it
$selectQuery = $selectClauseObj->buildQuery();
$topics = $g_ado_db->GetAll($selectQuery);
if (is_array($topics)) {
$countQuery = $countClauseObj->buildQuery();
$p_count = $g_ado_db->GetOne($countQuery);
// builds the array of topic objects
$articleTopicsList = array();
foreach ($topics as $topic) {
$articleTopicsList[] = $topic['TopicId'];
}
} else {
$articleTopicsList = array();
$p_count = 0;
}
if (!$p_skipCache && CampCache::IsEnabled()) {
$cacheListObj->storeInCache($articleTopicsList);
}
return $articleTopicsList;
}
/**
* Return an array of Language objects based on the given contraints.
*
* @param int $p_id
* @param string $p_languageCode
* @param string $p_name
* @param array $p_excludedLanguages
* @param array $p_order
* @return array
*/
public static function GetLanguages($p_id = null, $p_languageCode = null, $p_name = null, array $p_excludedLanguages = array(), array $p_order = array(), $p_skipCache = false)
{
global $g_ado_db;
if (!$p_skipCache && CampCache::IsEnabled()) {
$paramsArray['id'] = is_null($p_id) ? 'null' : $p_id;
$paramsArray['language_code'] = is_null($p_languageCode) ? 'null' : $p_languageCode;
$paramsArray['name'] = is_null($p_name) ? 'null' : $p_name;
$paramsArray['excluded_languages'] = $p_excludedLanguages;
$paramsArray['order'] = $p_order;
$cacheListObj = new CampCacheList($paramsArray, __METHOD__);
$languages = $cacheListObj->fetchFromCache();
if ($languages !== false && is_array($languages)) {
return $languages;
}
}
$selectClauseObj = new SQLSelectClause();
$tmpLanguage = new Language();
$selectClauseObj->setTable($tmpLanguage->getDbTableName());
if (!is_null($p_id)) {
$selectClauseObj->addWhere($g_ado_db->escapeKeyVal('Id', (int) $p_id));
}
if (!is_null($p_languageCode)) {
$selectClauseObj->addWhere($g_ado_db->escapeKeyVal('Code', $p_languageCode));
}
if (!is_null($p_name)) {
$selectClauseObj->addWhere($g_ado_db->escapeKeyVal('Name', $p_name));
}
if (count($p_excludedLanguages) > 0) {
$excludedLanguages = array();
foreach ($p_excludedLanguages as $excludedLanguage) {
$excludedLanguages[] = (int) $excludedLanguage;
}
$selectClauseObj->addWhere("Id NOT IN (" . implode(', ', $excludedLanguages) . ")");
}
$order = Language::ProcessLanguageListOrder($p_order);
foreach ($order as $orderDesc) {
$selectClauseObj->addOrderBy($orderDesc['field'] . ' ' . $orderDesc['dir']);
}
$selectClause = $selectClauseObj->buildQuery();
$languages = DbObjectArray::Create('Language', $selectClause);
if (!$p_skipCache && CampCache::IsEnabled()) {
$cacheListObj->storeInCache($languages);
}
return $languages;
}
/**
* Returns an article images list based on the given parameters.
*
* @param array $p_parameters
* An array of ComparisonOperation objects
* @param string $p_order
* An array of columns and directions to order by
* @param integer $p_start
* The record number to start the list
* @param integer $p_limit
* The offset. How many records from $p_start will be retrieved.
* @param integer $p_count
* The total count of the elements; this count is computed without
* applying the start ($p_start) and limit parameters ($p_limit)
*
* @return array $articleImagesList
* An array of Image objects
*/
public static function GetList(array $p_parameters, array $p_order = array(), $p_start = 0, $p_limit = 0, &$p_count, $p_skipCache = false)
{
global $g_ado_db;
if (!$p_skipCache && CampCache::IsEnabled()) {
$paramsArray['parameters'] = serialize($p_parameters);
$paramsArray['order'] = is_null($p_order) ? 'null' : $p_order;
$paramsArray['start'] = $p_start;
$paramsArray['limit'] = $p_limit;
$cacheListObj = new CampCacheList($paramsArray, __METHOD__);
$articleImagesList = $cacheListObj->fetchFromCache();
if ($articleImagesList !== false && is_array($articleImagesList)) {
return $articleImagesList;
}
}
$hasArticleNr = false;
$selectClauseObj = new SQLSelectClause();
$countClauseObj = new SQLSelectClause();
// sets the where conditions
foreach ($p_parameters as $param) {
$comparisonOperation = self::ProcessListParameters($param);
if (sizeof($comparisonOperation) < 3) {
break;
}
if (strpos($comparisonOperation['left'], 'NrArticle')) {
$hasArticleNr = true;
}
$whereCondition = $g_ado_db->escapeOperation($comparisonOperation);
$selectClauseObj->addWhere($whereCondition);
$countClauseObj->addWhere($whereCondition);
}
// validates whether article number was given
if ($hasArticleNr === false) {
CampTemplate::singleton()->trigger_error('Missing parameter Article ' . 'Number in statement list_article_images');
return;
}
// sets the columns to be fetched
$tmpImage = new Image();
$columnNames = $tmpImage->getColumnNames(true);
foreach ($columnNames as $columnName) {
$selectClauseObj->addColumn($columnName);
}
$countClauseObj->addColumn('COUNT(*)');
// sets the base table Attachment
$selectClauseObj->setTable($tmpImage->getDbTableName());
$countClauseObj->setTable($tmpImage->getDbTableName());
unset($tmpImage);
// adds the ArticleImages join and condition to the query
$selectClauseObj->addTableFrom('ArticleImages');
$selectClauseObj->addWhere('ArticleImages.IdImage = Images.Id');
$countClauseObj->addTableFrom('ArticleImages');
$countClauseObj->addWhere('ArticleImages.IdImage = Images.Id');
// sets the ORDER BY condition
$p_order = array_merge($p_order, self::$s_defaultOrder);
$order = self::ProcessListOrder($p_order);
foreach ($order as $orderDesc) {
$orderColumn = $orderDesc['field'];
$orderDirection = $orderDesc['dir'];
$selectClauseObj->addOrderBy($orderColumn . ' ' . $orderDirection);
}
// sets the limit
$selectClauseObj->setLimit($p_start, $p_limit);
// builds the query executes it
$selectQuery = $selectClauseObj->buildQuery();
$images = $g_ado_db->GetAll($selectQuery);
if (is_array($images)) {
$countQuery = $countClauseObj->buildQuery();
$p_count = $g_ado_db->GetOne($countQuery);
// builds the array of image objects
$articleImagesList = array();
foreach ($images as $image) {
$imgObj = new Image($image['Id']);
if ($imgObj->exists()) {
$articleImagesList[] = $imgObj;
}
}
} else {
$articleImagesList = array();
$p_count = 0;
}
if (!$p_skipCache && CampCache::IsEnabled()) {
$cacheListObj->storeInCache($articleImagesList);
}
return $articleImagesList;
}
/**
* Returns an images list based on the given parameters.
*
* @param array $p_parameters
* An array of ComparionOperation objects
* @param string $p_order
* An array of columns and directions to order by
* @param integer $p_start
* The record number to start the list
* @param integer $p_limit
* The offset. How many records from $p_start will be retrieved.
*
* @return array $issueList
* An array of Issue objects
*/
public static function GetList(array $p_parameters, array $p_order = array(),
$p_start = 0, $p_limit = 0, &$p_count, $p_skipCache = false)
{
global $g_ado_db;
if (!$p_skipCache && CampCache::IsEnabled()) {
$paramsArray['parameters'] = serialize($p_parameters);
$paramsArray['order'] = (is_null($p_order)) ? 'null' : $p_order;
$paramsArray['start'] = $p_start;
$paramsArray['limit'] = $p_limit;
$cacheListObj = new CampCacheList($paramsArray, __METHOD__);
$imagesList = $cacheListObj->fetchFromCache();
if ($imagesList !== false && is_array($imagesList)) {
return $imagesList;
}
}
$selectClauseObj = new SQLSelectClause();
$countClauseObj = new SQLSelectClause();
// sets the where conditions
foreach ($p_parameters as $param) {
$comparisonOperation = self::ProcessListParameters($param);
if (sizeof($comparisonOperation) < 1) {
break;
}
if ($comparisonOperation['symbol'] == 'match') {
$whereCondition = 'MATCH(' . $comparisonOperation['left'] . ") AGAINST('"
. $g_ado_db->escape($comparisonOperation['right']) . "' IN BOOLEAN MODE)";
} else {
$whereCondition = $comparisonOperation['left'] . ' '
. $comparisonOperation['symbol'] . " '"
. $g_ado_db->escape($comparisonOperation['right']) . "' ";
}
$selectClauseObj->addWhere($whereCondition);
$countClauseObj->addWhere($whereCondition);
}
// sets the columns to be fetched
$tmpImage = new Image();
$columnNames = $tmpImage->getColumnNames(true);
foreach ($columnNames as $columnName) {
$selectClauseObj->addColumn($columnName);
}
$countClauseObj->addColumn('COUNT(*)');
// sets the base table
$selectClauseObj->setTable($tmpImage->getDbTableName());
$countClauseObj->setTable($tmpImage->getDbTableName());
unset($tmpImage);
// sets the ORDER BY condition
$p_order = array_merge($p_order, self::$s_defaultOrder);
$order = self::ProcessListOrder($p_order);
foreach ($order as $orderDesc) {
$orderColumn = $orderDesc['field'];
$orderDirection = $orderDesc['dir'];
$selectClauseObj->addOrderBy($orderColumn . ' ' . $orderDirection);
}
// sets the limit
$selectClauseObj->setLimit($p_start, $p_limit);
// builds the query and executes it
$selectQuery = $selectClauseObj->buildQuery();
$images = $g_ado_db->GetAll($selectQuery);
if (is_array($images)) {
$countQuery = $countClauseObj->buildQuery();
$p_count = $g_ado_db->GetOne($countQuery);
// builds the array of image objects
$imagesList = array();
foreach ($images as $image) {
$imgObj = new Image($image['Id']);
if ($imgObj->exists()) {
$imagesList[] = $imgObj;
}
}
} else {
$imagesList = array();
$p_count = 0;
}
if (!$p_skipCache && CampCache::IsEnabled()) {
$cacheListObj->storeInCache($imagesList);
}
return $imagesList;
} // fn GetList
/**
* Returns an article authors list based on the given parameters.
*
* @param array $p_parameters
* An array of ComparisonOperation objects
* @param string $p_order
* An array of columns and directions to order by
* @param integer $p_start
* The record number to start the list
* @param integer $p_limit
* The offset. How many records from $p_start will be retrieved.
* @param integer $p_count
* The total count of the elements; this count is computed without
* applying the start ($p_start) and limit parameters ($p_limit)
*
* @return array $articleAuthorsList
* An array of Author objects
*/
public static function GetList(array $p_parameters, $p_order = null, $p_start = 0, $p_limit = 0, &$p_count, $p_skipCache = false)
{
global $g_ado_db;
if (!$p_skipCache && CampCache::IsEnabled()) {
$paramsArray['parameters'] = serialize($p_parameters);
$paramsArray['order'] = is_null($p_order) ? 'order' : $p_order;
$paramsArray['start'] = $p_start;
$paramsArray['limit'] = $p_limit;
$cacheListObj = new CampCacheList($paramsArray, __METHOD__);
$articleAuthorsList = $cacheListObj->fetchFromCache();
if ($articleAuthorsList !== false && is_array($articleAuthorsList)) {
return $articleAuthorsList;
}
}
$hasArticleNr = false;
$selectClauseObj = new SQLSelectClause();
$countClauseObj = new SQLSelectClause();
// sets the where conditions
foreach ($p_parameters as $param) {
if ($param->getLeftOperand() == 'type') {
$whereCondition = 'fk_type_id ' . $param->getOperator()->getSymbol() . ' (SELECT id FROM ' . AuthorType::TABLE . ' WHERE type="' . str_replace("'", "", $param->getRightOperand()) . '")';
$selectClauseObj->addWhere($whereCondition);
$countClauseObj->addWhere($whereCondition);
}
if ($param->getLeftOperand() == 'id') {
$whereCondition = 'fk_author_id ' . $param->getOperator()->getSymbol() . ' ' . $param->getRightOperand();
$selectClauseObj->addWhere($whereCondition);
$countClauseObj->addWhere($whereCondition);
}
$comparisonOperation = self::ProcessListParameters($param);
if (sizeof($comparisonOperation) < 1) {
break;
}
switch (key($comparisonOperation)) {
case 'fk_article_number':
$whereCondition = 'fk_article_number = ' . $comparisonOperation['fk_article_number'];
$hasArticleNr = true;
break;
case 'fk_language_id':
$whereCondition = '(fk_language_id IS NULL OR ' . 'fk_language_id = ' . $comparisonOperation['fk_language_id'] . ')';
break;
}
$selectClauseObj->addWhere($whereCondition);
$countClauseObj->addWhere($whereCondition);
}
// validates whether article number was given
if ($hasArticleNr === false) {
CampTemplate::singleton()->trigger_error("missed parameter Article Number in statement list_article_authors");
}
// sets the base table ArticleAuthors and the column to be fetched
$tmpArticleAuthor = new ArticleAuthor();
$selectClauseObj->setTable($tmpArticleAuthor->getDbTableName());
$selectClauseObj->addJoin('JOIN ' . Author::TABLE . ' ON fk_author_id = id');
$selectClauseObj->addColumn('fk_author_id');
$selectClauseObj->addColumn('fk_type_id');
$countClauseObj->setTable($tmpArticleAuthor->getDbTableName());
$countClauseObj->addColumn('COUNT(*)');
unset($tmpArticleAuthor);
if (!is_array($p_order)) {
$p_order = array();
}
$order = self::ProcessListOrder($p_order);
// sets the order condition if any
foreach ($order as $orderDesc) {
$orderField = $orderDesc['field'];
$orderDirection = $orderDesc['dir'];
$selectClauseObj->addOrderBy($orderField . ' ' . $orderDirection);
}
// sets the limit
$selectClauseObj->setLimit($p_start, $p_limit);
// builds the query and executes it
$selectQuery = $selectClauseObj->buildQuery();
$authors = $g_ado_db->GetAll($selectQuery);
if (is_array($authors)) {
$countQuery = $countClauseObj->buildQuery();
$p_count = $g_ado_db->GetOne($countQuery);
// builds the array of attachment objects
$authorsList = array();
foreach ($authors as $author) {
$authorObj = new Author($author['fk_author_id'], $author['fk_type_id']);
if ($authorObj->exists()) {
$authorsList[] = $authorObj;
}
}
} else {
$authorsList = array();
$p_count = 0;
}
if (!$p_skipCache && CampCache::IsEnabled()) {
$cacheListObj->storeInCache($authorsList);
}
return $authorsList;
}
/**
* Returns an article attachments list based on the given parameters.
*
* @param array $p_parameters
* An array of ComparisonOperation objects
* @param string $p_order
* An array of columns and directions to order by
* @param integer $p_start
* The record number to start the list
* @param integer $p_limit
* The offset. How many records from $p_start will be retrieved.
* @param integer $p_count
* The total count of the elements; this count is computed without
* applying the start ($p_start) and limit parameters ($p_limit)
*
* @return array $articleAttachmentsList
* An array of Attachment objects
*/
public static function GetList(array $p_parameters, $p_order = null, $p_start = 0, $p_limit = 0, &$p_count, $p_skipCache = false)
{
global $g_ado_db;
if (!$p_skipCache && CampCache::IsEnabled()) {
$paramsArray['parameters'] = serialize($p_parameters);
$paramsArray['order'] = is_null($p_order) ? 'null' : $p_order;
$paramsArray['start'] = $p_start;
$paramsArray['limit'] = $p_limit;
$cacheListObj = new CampCacheList($paramsArray, __METHOD__);
$articleAttachmentsList = $cacheListObj->fetchFromCache();
if ($articleAttachmentsList !== false && is_array($articleAttachmentsList)) {
return $articleAttachmentsList;
}
}
$hasArticleNr = false;
$selectClauseObj = new SQLSelectClause();
$countClauseObj = new SQLSelectClause();
// sets the where conditions
foreach ($p_parameters as $param) {
$comparisonOperation = self::ProcessParameters($param);
if (sizeof($comparisonOperation) < 1) {
break;
}
if (strpos($comparisonOperation['left'], 'fk_article_number')) {
$whereCondition = $g_ado_db->escapeOperation($comparisonOperation);
$hasArticleNr = true;
} elseif (strpos($comparisonOperation['left'], 'fk_language_id')) {
$whereCondition = '(' . $comparisonOperation['left'] . ' IS NULL OR ' . $comparisonOperation['left'] . " = " . $g_ado_db->escape($comparisonOperation['right']) . ")";
} else {
$whereCondition = $g_ado_db->escapeOperation($comparisonOperation);
}
$selectClauseObj->addWhere($whereCondition);
$countClauseObj->addWhere($whereCondition);
}
// validates whether article number was given
if ($hasArticleNr === false) {
CampTemplate::singleton()->trigger_error('missed parameter Article ' . 'Number in statement list_article_attachments');
return;
}
// sets the columns to be fetched
$tmpAttachment = new Attachment();
$columnNames = $tmpAttachment->getColumnNames(true);
foreach ($columnNames as $columnName) {
$selectClauseObj->addColumn($columnName);
}
$countClauseObj->addColumn('COUNT(*)');
// sets the main table for the query
$selectClauseObj->setTable($tmpAttachment->getDbTableName());
$countClauseObj->setTable($tmpAttachment->getDbTableName());
unset($tmpAttachment);
// adds the ArticleAttachments join and condition to the query
$selectClauseObj->addTableFrom('ArticleAttachments');
$selectClauseObj->addWhere('ArticleAttachments.fk_attachment_id = Attachments.id');
$countClauseObj->addTableFrom('ArticleAttachments');
$countClauseObj->addWhere('ArticleAttachments.fk_attachment_id = Attachments.id');
if (!is_array($p_order)) {
$p_order = array();
}
// sets the order condition if any
foreach ($p_order as $orderColumn => $orderDirection) {
$selectClauseObj->addOrderBy($orderColumn . ' ' . $orderDirection);
}
// sets the limit
$selectClauseObj->setLimit($p_start, $p_limit);
// builds the query and executes it
$selectQuery = $selectClauseObj->buildQuery();
$attachments = $g_ado_db->GetAll($selectQuery);
if (is_array($attachments)) {
$countQuery = $countClauseObj->buildQuery();
$p_count = $g_ado_db->GetOne($countQuery);
// builds the array of attachment objects
$articleAttachmentsList = array();
foreach ($attachments as $attachment) {
$attchObj = new Attachment($attachment['id']);
if ($attchObj->exists()) {
$articleAttachmentsList[] = $attchObj;
}
}
} else {
$articleAttachmentsList = array();
$p_count = 0;
}
if (!$p_skipCache && CampCache::IsEnabled()) {
$cacheListObj->storeInCache($articleAttachmentsList);
}
return $articleAttachmentsList;
}
/**
* Gets an issues list based on the given parameters.
*
* @param array $p_parameters
* An array of ComparisonOperation objects
* @param string $p_order
* An array of columns and directions to order by
* @param integer $p_start
* The record number to start the list
* @param integer $p_limit
* The offset. How many records from $p_start will be retrieved.
* @param integer $p_count
* The total count of the elements; this count is computed without
* applying the start ($p_start) and limit parameters ($p_limit)
*
* @return array $issuesList
* An array of Issue objects
*/
public static function GetList(array $p_parameters, $p_order = null,
$p_start = 0, $p_limit = 0, &$p_count, $p_skipCache = false)
{
global $g_ado_db;
if (!$p_skipCache && CampCache::IsEnabled()) {
$paramsArray['parameters'] = serialize($p_parameters);
$paramsArray['order'] = (is_null($p_order)) ? 'null' : $p_order;
$paramsArray['start'] = $p_start;
$paramsArray['limit'] = $p_limit;
$cacheListObj = new CampCacheList($paramsArray, __METHOD__);
$issuesList = $cacheListObj->fetchFromCache();
if ($issuesList !== false && is_array($issuesList)) {
return $issuesList;
}
}
$hasPublicationId = false;
$selectClauseObj = new SQLSelectClause();
$countClauseObj = new SQLSelectClause();
// sets the where conditions
foreach ($p_parameters as $param) {
$comparisonOperation = self::ProcessListParameters($param);
if (empty($comparisonOperation)) {
break;
}
if (strpos($comparisonOperation['left'], 'IdPublication') !== false) {
$hasPublicationId = true;
}
$whereCondition = $comparisonOperation['left'] . ' '
. $comparisonOperation['symbol'] . " '"
. $g_ado_db->escape($comparisonOperation['right']) . "' ";
$selectClauseObj->addWhere($whereCondition);
$countClauseObj->addWhere($whereCondition);
}
// validates whether publication identifier was given
if ($hasPublicationId == false) {
CampTemplate::singleton()->trigger_error('missed parameter Publication '
.'Identifier in statement list_topics');
return;
}
// sets the columns to be fetched
$tmpIssue = new Issue();
$columnNames = $tmpIssue->getColumnNames(true);
foreach ($columnNames as $columnName) {
$selectClauseObj->addColumn($columnName);
}
$countClauseObj->addColumn('COUNT(*)');
// sets the main table for the query
$selectClauseObj->setTable($tmpIssue->getDbTableName());
$countClauseObj->setTable($tmpIssue->getDbTableName());
unset($tmpIssue);
if (is_array($p_order)) {
$order = Issue::ProcessListOrder($p_order);
// sets the order condition if any
foreach ($order as $orderDesc) {
$orderField = $orderDesc['field'];
$orderDirection = $orderDesc['dir'];
$selectClauseObj->addOrderBy($orderField . ' ' . $orderDirection);
}
}
$selectClauseObj->addGroupField('Number');
$selectClauseObj->addGroupField('IdLanguage');
// sets the limit
$selectClauseObj->setLimit($p_start, $p_limit);
// builds the query and executes it
$selectQuery = $selectClauseObj->buildQuery();
$countQuery = $countClauseObj->buildQuery();
$issues = $g_ado_db->GetAll($selectQuery);
if (is_array($issues)) {
$p_count = $g_ado_db->GetOne($countQuery);
// builds the array of issue objects
$issuesList = array();
foreach ($issues as $issue) {
$issObj = new Issue($issue['IdPublication'],
$issue['IdLanguage'],
$issue['Number']);
if ($issObj->exists()) {
$issuesList[] = $issObj;
}
}
} else {
$issuesList = array();
$p_count = 0;
}
if (!$p_skipCache && CampCache::IsEnabled()) {
$cacheListObj->storeInCache($issuesList);
}
return $issuesList;
} // fn GetList
/**
* Returns an articles list based on the given parameters.
*
* @param array $p_parameters
* An array of ComparisonOperation objects
* @param string $p_order
* An array of columns and directions to order by
* @param integer $p_start
* The record number to start the list
* @param integer $p_limit
* The offset. How many records from $p_start will be retrieved.
* @param integer $p_count
* The total count of the elements; this count is computed without
* applying the start ($p_start) and limit parameters ($p_limit)
*
* @return array $articlesList
* An array of Article objects
*/
public static function GetList(array $p_parameters, $p_order = null, $p_start = 0, $p_limit = 0, &$p_count, $p_skipCache = false, $returnObjs = true)
{
global $g_ado_db;
if (!$p_skipCache && CampCache::IsEnabled()) {
$paramsArray['parameters'] = serialize($p_parameters);
$paramsArray['order'] = is_null($p_order) ? 'null' : $p_order;
$paramsArray['start'] = $p_start;
$paramsArray['limit'] = $p_limit;
$cacheListObj = new CampCacheList($paramsArray, __METHOD__);
$articlesList = $cacheListObj->fetchFromCache();
if ($articlesList !== false && is_array($articlesList)) {
return $articlesList;
}
}
$matchAllTopics = false;
$hasTopics = array();
$hasNotTopics = array();
$selectClauseObj = new SQLSelectClause();
$otherTables = array();
// sets the name of the table for the this database object
$tmpArticle = new Article();
$articleTable = $tmpArticle->getDbTableName();
$selectClauseObj->setTable($articleTable);
unset($tmpArticle);
$languageId = null;
$em = Zend_Registry::get('container')->getService('em');
$request = Zend_Registry::get('container')->getService('request');
$repository = $em->getRepository('Newscoop\\NewscoopBundle\\Entity\\Topic');
// parses the given parameters in order to build the WHERE part of
// the SQL SELECT sentence
foreach ($p_parameters as $param) {
$comparisonOperation = self::ProcessListParameters($param, $otherTables);
$leftOperand = strtolower($comparisonOperation['left']);
if ($leftOperand == 'idlanguage' && $comparisonOperation['symbol'] == '=') {
$languageId = $comparisonOperation['right'];
}
if (array_key_exists($leftOperand, Article::$s_regularParameters)) {
// regular article field, having a direct correspondent in the
// Article table fields
$whereCondition = Article::$s_regularParameters[$leftOperand] . ' ' . $comparisonOperation['symbol'] . " " . $g_ado_db->escape($comparisonOperation['right']) . " ";
if ($leftOperand == 'reads' && strstr($comparisonOperation['symbol'], '=') !== false && $comparisonOperation['right'] == 0) {
$selectClauseObj->addConditionalWhere($whereCondition);
$isNullCond = Article::$s_regularParameters[$leftOperand] . ' IS NULL';
$selectClauseObj->addConditionalWhere($isNullCond);
} elseif ($leftOperand == 'type' && $comparisonOperation['symbol'] == '=') {
$selectClauseObj->addConditionalWhere($whereCondition);
} elseif ($leftOperand == 'workflow_status' && isset($comparisonOperation['pending'])) {
$selectClauseObj->addConditionalWhere('Articles.NrIssue = 0');
$selectClauseObj->addConditionalWhere('Articles.NrSection = 0');
$selectClauseObj->addWhere($whereCondition);
} else {
$selectClauseObj->addWhere($whereCondition);
}
} elseif ($leftOperand == 'matchalltopics') {
// set the matchAllTopics flag
$matchAllTopics = true;
} elseif ($leftOperand == 'topic') {
// add the topic to the list of match/do not match topics depending
// on the operator
$topic = $repository->getTopicByIdOrName($comparisonOperation['right'], $request->getLocale())->getOneOrNullResult();
if ($topic) {
$topicIds = array();
foreach ($topic->getChildren() as $child) {
$topicIds[] = $child->getId();
}
$topicIds[] = $comparisonOperation['right'];
if ($comparisonOperation['symbol'] == '=') {
$hasTopics[] = $topicIds;
} else {
$hasNotTopics[] = $topicIds;
}
}
} elseif ($leftOperand == 'topic_strict') {
$topic = $repository->getTopicByIdOrName($comparisonOperation['right'], $request->getLocale())->getOneOrNullResult();
if ($topic) {
$topicIds[] = $comparisonOperation['right'];
if ($comparisonOperation['symbol'] == '=') {
$hasTopics[] = $topicIds;
} else {
$hasNotTopics[] = $topicIds;
}
}
} elseif ($leftOperand == 'author') {
$otherTables['ArticleAuthors'] = array('__JOIN' => ',');
$author = Author::ReadName($comparisonOperation['right']);
$symbol = $comparisonOperation['symbol'];
$valModifier = strtolower($symbol) == 'like' ? '%' : '';
$firstName = trim(trim($g_ado_db->escape($author['first_name']), "'"));
$lastName = trim(trim($g_ado_db->escape($author['last_name']), "'"));
$authors = $g_ado_db->GetAll("\n SELECT Authors.id\n FROM Authors\n WHERE CONCAT(Authors.first_name, ' ', Authors.last_name) {$symbol}\n '{$valModifier}{$firstName} {$lastName}{$valModifier}'\n ");
$authorsIds = array();
foreach ($authors as $author) {
$authorsIds[] = $author['id'];
}
$whereCondition = "ArticleAuthors.fk_author_id IN (" . implode(',', $authorsIds) . ")";
$selectClauseObj->addWhere($whereCondition);
$selectClauseObj->addWhere('Articles.Number = ArticleAuthors.fk_article_number');
$selectClauseObj->addWhere('Articles.IdLanguage = ArticleAuthors.fk_language_id');
} elseif ($leftOperand == 'search_phrase') {
$searchQuery = ArticleIndex::SearchQuery($comparisonOperation['right'], $comparisonOperation['symbol']);
if (!empty($searchQuery)) {
$otherTables["({$searchQuery})"] = array('__TABLE_ALIAS' => 'search', '__JOIN' => 'INNER JOIN', 'Number' => 'NrArticle', 'IdLanguage' => 'IdLanguage');
}
} elseif ($leftOperand == 'location') {
$num = '[-+]?[0-9]+(?:\\.[0-9]+)?';
if (preg_match("/({$num}) ({$num}), ({$num}) ({$num})/", trim($comparisonOperation['right']), $matches)) {
$queryLocation = Geo_Map::GetGeoSearchSQLQuery(array(array('latitude' => $matches[1], 'longitude' => $matches[2]), array('latitude' => $matches[3], 'longitude' => $matches[4])));
$selectClauseObj->addWhere("Articles.Number IN ({$queryLocation})");
}
} elseif ($leftOperand == 'insection') {
$selectClauseObj->addWhere("Articles.NrSection IN " . $comparisonOperation['right']);
} elseif ($leftOperand == 'complex_date') {
/* @var $param ComparisonOperation */
$fieldName = key($roper = $param->getRightOperand());
$searchValues = array();
foreach (explode(",", current($roper)) as $values) {
list($key, $value) = explode(":", $values, 2);
$searchValues[preg_replace("`(?<=[a-z])(_([a-z]))`e", "strtoupper('\\2')", trim($key))] = trim($value);
}
$repo = Zend_Registry::get('container')->getService('em')->getRepository('Newscoop\\Entity\\ArticleDatetime');
/* @var $repo \Newscoop\Entity\Repository\ArticleRepository */
$searchValues['fieldName'] = $fieldName;
$sqlQuery = $repo->findDates((object) $searchValues, true)->getFindDatesSQL('dt.articleId');
if (!is_null($sqlQuery)) {
$whereCondition = "Articles.Number IN (\n{$sqlQuery})";
$selectClauseObj->addWhere($whereCondition);
}
} else {
// custom article field; has a correspondence in the X[type]
// table fields
$sqlQuery = self::ProcessCustomField($comparisonOperation, $languageId);
if (!is_null($sqlQuery)) {
$whereCondition = "Articles.Number IN (\n{$sqlQuery} )";
$selectClauseObj->addWhere($whereCondition);
}
}
}
if (count($hasTopics) > 0 || count($hasNotTopics) > 0) {
$typeAttributes = ArticleTypeField::FetchFields(null, null, 'topic', false, false, false, true, $p_skipCache);
}
if (count($hasTopics) > 0) {
if ($matchAllTopics) {
foreach ($hasTopics as $topicId) {
$sqlQuery = Article::BuildTopicSelectClause($topicId, $typeAttributes);
$whereCondition = "Articles.Number IN (\n{$sqlQuery} )";
$selectClauseObj->addWhere($whereCondition);
}
} else {
$sqlQuery = Article::BuildTopicSelectClause($hasTopics, $typeAttributes);
$whereCondition = "Articles.Number IN (\n{$sqlQuery} )";
$selectClauseObj->addWhere($whereCondition);
}
}
if (count($hasNotTopics) > 0) {
$sqlQuery = Article::BuildTopicSelectClause($hasNotTopics, $typeAttributes);
$whereCondition = "Articles.Number NOT IN (\n{$sqlQuery} )";
$selectClauseObj->addWhere($whereCondition);
}
// create the count clause object
$countClauseObj = clone $selectClauseObj;
if (!is_array($p_order)) {
$p_order = array();
}
// sets the ORDER BY condition
$p_order = array_merge($p_order, Article::$s_defaultOrder);
$order = Article::ProcessListOrder($p_order, $otherTables, $otherWhereConditions);
foreach ($order as $orderDesc) {
$orderColumn = $orderDesc['field'];
$orderDirection = $orderDesc['dir'];
$selectClauseObj->addOrderBy($orderColumn . ' ' . $orderDirection);
}
if (count($otherTables) > 0) {
foreach ($otherTables as $table => $fields) {
$joinType = 'LEFT JOIN';
if (isset($fields['__JOIN'])) {
$joinType = $fields['__JOIN'];
}
if (isset($fields['__TABLE_ALIAS'])) {
$tableAlias = $fields['__TABLE_ALIAS'];
$tableJoin = "\n {$joinType} {$table} AS {$tableAlias} \n";
} else {
$tableAlias = $table;
$tableJoin = "\n {$joinType} {$tableAlias} \n";
}
$firstCondition = true;
foreach ($fields as $parent => $child) {
if ($parent == '__TABLE_ALIAS' || $parent == '__JOIN') {
continue;
}
$condOperator = $firstCondition ? ' ON ' : 'AND ';
if ($parent == '__CONST') {
$constTable = $child['table'];
$constField = $child['field'];
$value = $child['value'];
$negate = isset($child['negate']) ? $child['negate'] : false;
if (is_null($value)) {
$operator = $negate ? 'IS NOT' : 'IS';
$value = 'NULL';
} else {
$operator = $negate ? '!=' : '=';
$value = $g_ado_db->escape($value);
}
$tableJoin .= " {$condOperator}`{$constTable}`.`{$constField}` {$operator} {$value}\n";
} else {
$tableJoin .= " {$condOperator}`{$articleTable}`.`{$parent}` = `{$tableAlias}`.`{$child}`\n";
}
$firstCondition = false;
}
$selectClauseObj->addJoin($tableJoin);
$countClauseObj->addJoin($tableJoin);
}
}
// other where conditions needed for certain order options
foreach ($otherWhereConditions as $whereCondition) {
$selectClauseObj->addWhere($whereCondition);
$countClauseObj->addWhere($whereCondition);
}
// gets the column list to be retrieved for the database table
$selectClauseObj->addColumn('Articles.Number');
$selectClauseObj->addColumn('Articles.IdLanguage');
$countClauseObj->addColumn('COUNT(*)');
// sets the LIMIT start and offset values
$selectClauseObj->setLimit($p_start, $p_limit);
// builds the SQL query
$selectQuery = $selectClauseObj->buildQuery();
$countQuery = $countClauseObj->buildQuery();
// runs the SQL query
$articles = $g_ado_db->GetAll($selectQuery);
if (is_array($articles)) {
$p_count = $g_ado_db->GetOne($countQuery);
// builds the array of Article objects
$articlesList = array();
foreach ($articles as $article) {
if ($returnObjs) {
$articlesList[] = new Article($article['IdLanguage'], $article['Number']);
} else {
$articlesList[] = array('language_id' => $article['IdLanguage'], 'number' => $article['Number']);
}
}
} else {
$articlesList = array();
$p_count = 0;
}
// stores articles list in cache
if (!$p_skipCache && CampCache::IsEnabled()) {
$cacheListObj->storeInCache($articlesList);
}
return $articlesList;
}
/**
* Search the Topics table.
*
* @param int $p_id
* @param int $p_languageId
* @param string $p_name
* @param int $p_parentId
* @param array $p_sqlOptions
* @return array
*/
public static function GetTopics($p_id = null, $p_languageId = null, $p_name = null, $p_parentId = null, $p_depth = 1, $p_sqlOptions = null, $p_order = null, $p_countOnly = false, $p_skipCache = false)
{
global $g_ado_db;
if (!$p_skipCache && CampCache::IsEnabled()) {
$paramsArray['id'] = is_null($p_id) ? '' : $p_id;
$paramsArray['language_id'] = is_null($p_languageId) ? '' : $p_languageId;
$paramsArray['name'] = is_null($p_name) ? '' : $p_name;
$paramsArray['parent_id'] = is_null($p_parentId) ? '' : $p_parentId;
$paramsArray['depth'] = is_null($p_depth) ? '' : $p_depth;
$paramsArray['sql_options'] = $p_sqlOptions;
$paramsArray['order'] = $p_order;
$paramsArray['count_only'] = (int) $p_countOnly;
$cacheListObj = new CampCacheList($paramsArray, __METHOD__);
$topics = $cacheListObj->fetchFromCache();
if ($topics !== false && is_array($topics)) {
return $p_countOnly ? $topics['count'] : $topics;
}
}
if (!is_array($p_order) || count($p_order) == 0) {
$p_order = array(array('field' => 'default', 'dir' => 'asc'));
}
foreach ($p_order as $orderCond) {
switch (strtolower($orderCond['field'])) {
case 'default':
$order['t.node_left'] = $orderCond['dir'];
break;
case 'byname':
$order['tn.name'] = $orderCond['dir'];
break;
case 'bynumber':
$order['t.id'] = $orderCond['dir'];
break;
}
}
$p_sqlOptions['ORDER BY'] = $order;
$query = new SQLSelectClause();
$query->addColumn('t.id');
$topicObj = new Topic();
$topicNameObj = new TopicName();
if (!is_null($p_languageId) && is_numeric($p_languageId) || !is_null($p_name) || isset($order['tn.name'])) {
$query->setTable($topicObj->m_dbTableName . ' AS t LEFT JOIN ' . $topicNameObj->m_dbTableName . ' AS tn ON t.id = tn.fk_topic_id');
} else {
$query->setTable($topicObj->m_dbTableName . ' AS t');
}
$constraints = array();
if (!is_null($p_id) && is_numeric($p_id)) {
$query->addWhere("t.id = '{$p_id}'");
}
if (!is_null($p_languageId) && is_numeric($p_languageId)) {
$query->addWhere("tn.fk_language_id = '{$p_languageId}'");
}
if (!is_null($p_name)) {
$query->addWhere("tn.name = " . $g_ado_db->escape($p_name));
}
if (!is_null($p_parentId)) {
$subtopicsQuery = self::BuildSubtopicsQuery($p_parentId, $p_depth, 1);
$query->addTableFrom('(' . $subtopicsQuery->buildQuery() . ') AS in_query');
$query->addWhere("t.id = in_query.id");
}
$queryStr = $query->buildQuery();
$queryStr = DatabaseObject::ProcessOptions($queryStr, $p_sqlOptions);
if ($p_countOnly) {
$queryStr = "SELECT COUNT(*) FROM ({$queryStr}) AS topics";
$topics['count'] = $g_ado_db->GetOne($queryStr);
} else {
$topics = array();
$rows = $g_ado_db->GetAll($queryStr);
foreach ($rows as $row) {
$topics[] = new Topic($row['id']);
}
}
if (!$p_skipCache && CampCache::IsEnabled()) {
$cacheListObj->storeInCache($topics);
}
return $topics;
}
private static function DeleteCachePluginsInfo()
{
if (CampCache::IsEnabled()) {
$cacheListObj = new CampCacheList(array(), self::CACHE_KEY_PLUGINS_ALL);
$cacheListObj->deleteFromCache();
return CampCache::singleton()->delete(self::CACHE_KEY_PLUGINS_LIST);
}
return false;
}
/**
* Returns an article comments list based on the given parameters.
*
* @param array $p_parameters
* An array of ComparisonOperation objects
* @param string $p_order
* An array of columns and directions to order by
* @param integer $p_start
* The record number to start the list
* @param integer $p_limit
* The offset. How many records from $p_start will be retrieved.
* @param integer $p_count
* The total count of the elements; this count is computed without
* applying the start ($p_start) and limit parameters ($p_limit)
*
* @return array $articleCommentsList
* An array of Comment objects
*/
public static function GetList(array $p_parameters, $p_order = null,
$p_start = 0, $p_limit = 0, &$p_count, $p_skipCache = false)
{
global $g_ado_db, $PHORUM;
if (!$p_skipCache && CampCache::IsEnabled()) {
$paramsArray['parameters'] = serialize($p_parameters);
$paramsArray['order'] = (is_null($p_order)) ? 'null' : $p_order;
$paramsArray['start'] = $p_start;
$paramsArray['limit'] = $p_limit;
$cacheListObj = new CampCacheList($paramsArray, __METHOD__, self::DEFAULT_TTL);
$articleCommentsList = $cacheListObj->fetchFromCache();
if ($articleCommentsList !== false && is_array($articleCommentsList)) {
return $articleCommentsList;
}
}
$selectClauseObj = new SQLSelectClause();
$countClauseObj = new SQLSelectClause();
$messageTable = $PHORUM['message_table'];
$selectClauseObj->setTable($messageTable);
$countClauseObj->setTable($messageTable);
$articleNumber = null;
$languageId = null;
// sets the where conditions
foreach ($p_parameters as $param) {
$comparisonOperation = self::ProcessListParameters($param);
if (strtolower($comparisonOperation->getLeftOperand()) == 'fk_article_number') {
$articleNumber = $comparisonOperation->getRightOperand();
}
if (strtolower($comparisonOperation->getLeftOperand()) == 'fk_language_id') {
$languageId = $comparisonOperation->getRightOperand();
}
$parameters[] = $comparisonOperation;
}
if (!is_null($articleNumber) && !is_null($languageId)) {
// gets the thread id for the article
$threadId = ArticleComment::GetCommentThreadId($articleNumber, $languageId);
$selectClauseObj->addWhere('thread = '.$threadId);
$countClauseObj->addWhere('thread = '.$threadId);
}
$selectClauseObj->addWhere('message_id != thread');
$selectClauseObj->addWhere('status = '.PHORUM_STATUS_APPROVED);
$countClauseObj->addWhere('message_id != thread');
$countClauseObj->addWhere('status = '.PHORUM_STATUS_APPROVED);
if (!is_array($p_order) || count($p_order) == 0) {
$p_order = array('default'=>'asc');
}
// sets the order condition if any
if (is_array($p_order)) {
$order = ArticleComment::ProcessListOrder($p_order);
// sets the order condition if any
foreach ($order as $orderDesc) {
$orderField = $orderDesc['field'];
$orderDirection = $orderDesc['dir'];
$selectClauseObj->addOrderBy($orderField . ' ' . $orderDirection);
}
}
// sets the limit
$selectClauseObj->setLimit($p_start, $p_limit);
// builds the query and executes it
$selectQuery = $selectClauseObj->buildQuery();
$comments = $g_ado_db->GetAll($selectQuery);
if (is_array($comments)) {
$countClauseObj->addColumn('COUNT(*)');
$countQuery = $countClauseObj->buildQuery();
$p_count = $g_ado_db->GetOne($countQuery);
// builds the array of comment objects
$articleCommentsList = array();
foreach ($comments as $comment) {
$pmObj = new Phorum_message($comment['message_id']);
if ($pmObj->exists()) {
$articleCommentsList[] = $pmObj;
}
}
} else {
$articleCommentsList = array();
$p_count = 0;
}
if (!$p_skipCache && CampCache::IsEnabled()) {
$cacheListObj->storeInCache($articleCommentsList);
}
return $articleCommentsList;
} // fn GetList
/**
* Returns map locations list based on the given parameters.
*
* @param array $p_parameters
* An array of ComparionOperation objects
* @param array $p_order
* An array of columns and directions to order by
* @param integer $p_start
* The record number to start the list
* @param integer $p_limit
* The offset, how many records from $p_start will be retrieved
* @param integer $p_count
* Total count of POIs without p_start/p_limit limitations
* @param bool $p_skipCache
* Whether to skip caching
* @param array $p_rawData
* The variable for returning read points as raw array, used for the JS processing
*
* @return array
*/
public static function GetListExt(array $p_parameters, array $p_order = array(), $p_start = 0, $p_limit = 0, &$p_count, $p_skipCache = false, &$p_rawData = null)
{
$p_count = 0;
if (!is_numeric($p_limit)) {
$p_limit = 0;
}
if (!is_numeric($p_start)) {
$p_start = 0;
}
$ps_saveArray = false;
if (!is_null($p_rawData)) {
$ps_saveArray = true;
$p_rawData = array();
}
$list_spec = array('params' => $p_parameters, 'order' => $p_order, 'start' => $p_start, 'limit' => $p_limit, 'skip_cache' => $p_skipCache, 'raw_data' => $p_rawData);
$list_spec_str = serialize($list_spec);
if (!$p_skipCache && !empty(self::$s_found_maplocations_list_ext) && isset(self::$s_found_maplocations_list_ext[$list_spec_str])) {
$list_res_data = self::$s_found_maplocations_list_ext[$list_spec_str];
$p_count = $list_res_data['count'];
$list_obj = $list_res_data['list_obj'];
if ($ps_saveArray) {
$p_rawData = $list_res_data['list_raw'];
}
return $list_obj;
}
$ps_mapId = 0;
$ps_languageId = 0;
$ps_preview = false;
// read all the info, mostly cached at the upstream
$ps_publicationId = 0;
$mc_mapCons = false;
$mc_article_types_yes = array();
$mc_article_types_no = array();
$mc_authors_yes = array();
$mc_authors_no = array();
$mc_users_yes = array();
$mc_users_no = array();
$mc_articles_yes = array();
$mc_articles_no = array();
$mc_issues = array();
$mc_sections = array();
$mc_section_names = array();
$mc_topics = array();
$mc_topic_names = array();
$mc_topics_matchall = false;
$mc_multimedia = array();
$mc_areas = array();
$mc_areas_matchall = false;
$mc_areas_exact = true;
$mc_dates = array();
$mc_icons = array();
// process params
foreach ($p_parameters as $param) {
switch ($param->getLeftOperand()) {
case 'constrained':
$mc_mapCons = true;
case 'map':
if (is_numeric($param->getRightOperand())) {
$ps_mapId = $param->getRightOperand();
}
break;
case 'language':
if (is_numeric($param->getRightOperand())) {
$ps_languageId = 0 + $param->getRightOperand();
}
break;
case 'active_only':
case 'preview':
$ps_preview = $param->getRightOperand();
break;
case 'publication':
$ps_publicationId = (int) $param->getRightOperand();
break;
case 'article_type':
$one_arttp_value = $param->getRightOperand();
$one_arttp_type = $param->getOperator()->getName();
if ('is' == $one_arttp_type) {
$mc_article_types_yes[] = $one_arttp_value;
$mc_mapCons = true;
}
if ('not' == $one_arttp_type) {
$mc_article_types_no[] = $one_arttp_value;
$mc_mapCons = true;
}
break;
case 'author':
$one_user_value = $param->getRightOperand();
$one_user_type = $param->getOperator()->getName();
if ('is' == $one_user_type) {
$mc_authors_yes[] = $one_user_value;
$mc_mapCons = true;
}
if ('not' == $one_user_type) {
$mc_authors_no[] = $one_user_value;
$mc_mapCons = true;
}
break;
case 'article':
$one_article_value = $param->getRightOperand();
$one_article_type = $param->getOperator()->getName();
if ('is' == $one_article_type) {
$mc_articles_yes[] = $one_article_value;
$mc_mapCons = true;
}
if ('not' == $one_article_type) {
$mc_articles_no[] = $one_article_value;
$mc_mapCons = true;
}
break;
case 'issue':
$mc_issues[] = $param->getRightOperand();
$mc_mapCons = true;
break;
case 'section':
$mc_sections[] = $param->getRightOperand();
$mc_mapCons = true;
break;
case 'section_name':
$mc_section_names[] = $param->getRightOperand();
$mc_mapCons = true;
break;
case 'topic':
$mc_topics[] = $param->getRightOperand();
$mc_mapCons = true;
break;
case 'topic_name':
$mc_topic_names[] = $param->getRightOperand();
$mc_mapCons = true;
break;
case 'matchalltopics':
$mc_topics_matchall = $param->getRightOperand();
break;
case 'matchanytopic':
$mc_topics_matchall = !$param->getRightOperand();
break;
case 'multimedia':
$mc_multimedia[] = $param->getRightOperand();
$mc_mapCons = true;
break;
case 'area':
$mc_areas[] = json_decode($param->getRightOperand());
$mc_mapCons = true;
break;
case 'matchallareas':
$mc_areas_matchall = $param->getRightOperand();
break;
case 'matchanyarea':
$mc_areas_matchall = !$param->getRightOperand();
break;
case 'exactarea':
$mc_areas_exact = $param->getRightOperand();
break;
case 'date':
$mc_dates[$param->getOperator()->getName()] = $param->getRightOperand();
$mc_mapCons = true;
break;
case 'icon':
$mc_icons[] = $param->getRightOperand();
$mc_mapCons = true;
break;
default:
break;
}
}
// constrained maps ned to have set publication
if ($mc_mapCons && !$ps_publicationId) {
return array();
}
if (!$ps_languageId || 0 >= $ps_languageId) {
$ps_languageId = $Context->language->number;
}
if (!$ps_languageId || 0 >= $ps_languageId) {
return array();
}
if (!CampCache::IsEnabled()) {
$p_skipCache = true;
}
$ps_orders = array();
if (!$p_order || !is_array($p_order) || 0 == count($p_order)) {
if ($mc_mapCons) {
$ps_orders = array(array('a.Number' => 'DESC'), array('m.id' => 'DESC'));
}
} else {
$allowed_order_dirs = array('DESC' => true, 'ASC' => true);
foreach ($p_order as $one_order_column => $one_order_dir) {
$one_dir = strtoupper($one_order_dir);
if (!array_key_exists($one_dir, $allowed_order_dirs)) {
continue;
}
switch (strtolower($one_order_column)) {
case 'article':
if (!$mc_mapCons) {
break;
}
$ps_orders[] = array('a.Number' => $one_dir);
break;
case 'map':
$ps_orders[] = array('m.id' => $one_dir);
break;
case 'name':
$ps_orders[] = array('c.poi_name' => $one_dir);
break;
}
}
}
if ((0 == $ps_mapId || !$ps_mapId) && !$mc_mapCons) {
return array();
}
$mc_limit = 0 + $p_limit;
if (0 > $mc_limit) {
$mc_limit = 0;
}
$mc_start = 0 + $p_start;
if (0 > $mc_start) {
$mc_start = 0;
}
$dataArray = array();
$objsArray = array();
$cachedData = null;
$paramsArray_arr = array();
$paramsArray_obj = array();
$cacheList_arr = null;
$cacheList_obj = null;
if (!$p_skipCache) {
$paramsArray_arr['as_array'] = true;
$paramsArray_arr['map_id'] = $ps_mapId;
$paramsArray_arr['publication_id'] = $ps_publicationId;
$paramsArray_arr['language_id'] = $ps_languageId;
$paramsArray_arr['preview'] = $ps_preview;
$paramsArray_arr['map_cons'] = $mc_mapCons;
$paramsArray_arr['article_types_yes'] = $mc_article_types_yes;
$paramsArray_arr['article_types_no'] = $mc_article_types_no;
$paramsArray_arr['authors_yes'] = $mc_authors_yes;
$paramsArray_arr['authors_no'] = $mc_authors_no;
$paramsArray_arr['articles_yes'] = $mc_articles_yes;
$paramsArray_arr['articles_no'] = $mc_articles_no;
$paramsArray_arr['issues'] = $mc_issues;
$paramsArray_arr['sections'] = $mc_sections;
$paramsArray_arr['section_names'] = $mc_section_names;
$paramsArray_arr['topics'] = $mc_topics;
$paramsArray_arr['topic_names'] = $mc_topic_names;
$paramsArray_arr['topics_matchall'] = $mc_topics_matchall;
$paramsArray_arr['multimedia'] = $mc_multimedia;
$paramsArray_arr['areas'] = $mc_areas;
$paramsArray_arr['areas_matchall'] = $mc_areas_matchall;
$paramsArray_arr['areas_exact'] = $mc_areas_exact;
$paramsArray_arr['dates'] = $mc_dates;
$paramsArray_arr['icons'] = $mc_icons;
$paramsArray_arr['orders'] = $ps_orders;
$paramsArray_arr['limit'] = $mc_limit;
$paramsArray_arr['start'] = $mc_start;
$paramsArray_obj = $paramsArray_arr;
$paramsArray_obj['as_array'] = false;
$cacheList_arr = new CampCacheList($paramsArray_arr, __METHOD__);
$cacheList_obj = new CampCacheList($paramsArray_obj, __METHOD__);
if ($ps_saveArray) {
$cachedData = $cacheList_arr->fetchFromCache();
if ($cachedData !== false && is_array($cachedData)) {
$p_count = $cachedData['count'];
$p_rawData = $cachedData['data'];
}
}
$cachedData = $cacheList_obj->fetchFromCache();
if ($cachedData !== false && is_array($cachedData)) {
$p_count = $cachedData['count'];
return $cachedData['data'];
}
}
global $g_ado_db;
if (0 < count($mc_authors_yes)) {
$author_ids_req_names = ArticleAuthor::BuildAuthorIdsQuery($mc_authors_yes);
$author_ids_req_alias = AuthorAlias::BuildAuthorIdsQuery($mc_authors_yes);
if ($author_ids_req_names) {
$rows = $g_ado_db->GetAll($author_ids_req_names);
if (is_array($rows)) {
foreach ($rows as $row) {
$mc_users_yes[] = trim(strtolower($row['id']));
}
}
}
if ($author_ids_req_alias) {
$rows = $g_ado_db->GetAll($author_ids_req_alias);
if (is_array($rows)) {
foreach ($rows as $row) {
$mc_users_yes[] = trim(strtolower($row['id']));
}
}
}
if (0 == count($mc_users_yes)) {
return array();
}
}
if (0 < count($mc_authors_no)) {
$author_ids_req_names = ArticleAuthor::BuildAuthorIdsQuery($mc_authors_no);
$author_ids_req_alias = AuthorAlias::BuildAuthorIdsQuery($mc_authors_no);
if ($author_ids_req_names) {
$rows = $g_ado_db->GetAll($author_ids_req_names);
if (is_array($rows)) {
foreach ($rows as $row) {
$mc_users_no[] = trim(strtolower($row['id']));
}
}
}
if ($author_ids_req_alias) {
$rows = $g_ado_db->GetAll($author_ids_req_alias);
if (is_array($rows)) {
foreach ($rows as $row) {
$mc_users_no[] = trim(strtolower($row['id']));
}
}
}
}
if (0 < count($mc_section_names)) {
$section_ids_req_names = Section::BuildSectionIdsQuery($mc_section_names, $ps_publicationId);
if ($section_ids_req_names) {
$rows = $g_ado_db->GetAll($section_ids_req_names);
if (is_array($rows)) {
foreach ($rows as $row) {
$mc_sections[] = trim(strtolower($row['id']));
}
}
}
if (0 == count($mc_sections)) {
return array();
}
}
if (0 < count($mc_topic_names)) {
$topic_ids_req_names = TopicName::BuildTopicIdsQuery($mc_topic_names);
if ($topic_ids_req_names) {
$rows = $g_ado_db->GetAll($topic_ids_req_names);
if (is_array($rows)) {
foreach ($rows as $row) {
$mc_topics[] = trim(strtolower($row['id']));
}
}
}
if (0 == count($mc_topics)) {
return array();
}
}
$ps_orders = array();
if (!$p_order || !is_array($p_order) || 0 == count($p_order)) {
if ($mc_mapCons) {
$ps_orders = array(array('a.PublishDate' => 'DESC'), array('a.Number' => 'DESC'), array('m.id' => 'DESC'));
}
} else {
$allowed_order_dirs = array('DESC' => true, 'ASC' => true);
foreach ($p_order as $one_order_column => $one_order_dir) {
$one_dir = strtoupper($one_order_dir);
if (!array_key_exists($one_dir, $allowed_order_dirs)) {
continue;
}
switch (strtolower($one_order_column)) {
case 'publish':
case 'published':
if (!$mc_mapCons) {
break;
}
$ps_orders[] = array('a.PublishDate' => $one_dir);
break;
case 'article':
if (!$mc_mapCons) {
break;
}
$ps_orders[] = array('a.Number' => $one_dir);
break;
case 'map':
$ps_orders[] = array('m.id' => $one_dir);
break;
case 'name':
$ps_orders[] = array('c.poi_name' => $one_dir);
break;
}
}
}
if ((0 == $ps_mapId || !$ps_mapId) && !$mc_mapCons) {
return array();
}
$queryStr = '';
$queryStr_start = '';
$queryStr_start .= 'SELECT DISTINCT ml.id AS ml_id, mll.id as mll_id, ml.fk_location_id AS loc_id, mll.fk_content_id AS con_id, ';
$queryStr_start .= 'ml.poi_style AS poi_style, ml.rank AS rank, mll.poi_display AS poi_display, ';
// these few lines below are just for data for list-of-objects array
$queryStr_start .= 'l.poi_radius AS l_radius, l.IdUser AS l_user, l.time_updated AS l_updated, ';
if ($mc_mapCons) {
$queryStr_start .= 'm.id AS m_id, m.IdUser AS m_user, m.fk_article_number AS art_number, GROUP_CONCAT(DISTINCT m.fk_article_number ORDER BY m.fk_article_number DESC) AS art_numbers, ';
}
$multimedia_def = "CONCAT(mu.id, \"-\", mlmu.id)";
if ($mc_mapCons) {
$multimedia_def = 'mu.id';
}
$queryStr_start .= "(SELECT {$multimedia_def} FROM MapLocationMultimedia AS mlmu INNER JOIN Multimedia AS mu ON mlmu.fk_multimedia_id = mu.id WHERE mlmu.fk_maplocation_id = ml.id AND mu.media_type = 'image' ORDER BY mu.id LIMIT 1) AS image_mm, ";
$queryStr_start .= "(SELECT {$multimedia_def} FROM MapLocationMultimedia AS mlmu INNER JOIN Multimedia AS mu ON mlmu.fk_multimedia_id = mu.id WHERE mlmu.fk_maplocation_id = ml.id AND mu.media_type = 'video' ORDER BY mu.id LIMIT 1) AS video_mm, ";
$queryStr_start .= 'c.IdUser AS c_user, c.time_updated AS c_updated, ';
$queryStr_start .= 'AsText(l.poi_location) AS loc, l.poi_type AS poi_type, l.poi_type_style AS poi_type_style, ';
$queryStr_start .= 'c.poi_name AS poi_name, c.poi_link AS poi_link, c.poi_perex AS poi_perex, ';
$queryStr_start .= 'c.poi_content_type AS poi_content_type, c.poi_content AS poi_content, c.poi_text AS poi_text ';
$queryStr .= 'FROM MapLocations AS ml INNER JOIN MapLocationLanguages AS mll ON ml.id = mll.fk_maplocation_id ';
$queryStr .= 'INNER JOIN Locations AS l ON l.id = ml.fk_location_id ';
$queryStr .= 'INNER JOIN LocationContents AS c ON c.id = mll.fk_content_id ';
$query_mcons = '';
$article_mcons = false;
$mc_filter_mm = false;
$mc_filter_image = false;
$mc_filter_video = false;
if ($mc_mapCons) {
$queryStr .= 'INNER JOIN Maps AS m ON m.id = ml.fk_map_id ';
$queryStr .= 'INNER JOIN Articles AS a ON m.fk_article_number = a.Number ';
if (0 < count($mc_users_yes) || 0 < count($mc_users_no)) {
$queryStr .= 'INNER JOIN ArticleAuthors AS aa ON aa.fk_article_number = a.Number ';
}
$query_mcons = "a.IdPublication = {$ps_publicationId} AND ";
$article_mcons = false;
foreach ($mc_multimedia as $one_multimedia) {
if ('any' == $one_multimedia) {
$mc_filter_mm = true;
}
if ('image' == $one_multimedia) {
$mc_filter_image = true;
}
if ('video' == $one_multimedia) {
$mc_filter_video = true;
}
}
if (0 < count($mc_users_yes)) {
$query_mcons .= 'aa.fk_author_id IN (' . implode(', ', $mc_users_yes) . ') AND ';
$article_mcons = true;
}
if (0 < count($mc_users_no)) {
$query_mcons .= 'aa.fk_author_id NOT IN (' . implode(', ', $mc_users_no) . ') AND ';
$article_mcons = true;
}
if (0 < count($mc_article_types_yes)) {
$mc_correct = true;
foreach ($mc_article_types_yes as $val) {
if (false !== stripos($val, '\'')) {
$mc_correct = false;
break;
}
if (false !== stripos($val, '"')) {
$mc_correct = false;
break;
}
}
if ($mc_correct) {
$query_mcons .= 'a.Type IN (\'' . implode('\', \'', $mc_article_types_yes) . '\') AND ';
$article_mcons = true;
}
}
if (0 < count($mc_article_types_no)) {
$mc_correct = true;
foreach ($mc_article_types_no as $val) {
if (false !== stripos($val, '\'')) {
$mc_correct = false;
break;
}
if (false !== stripos($val, '"')) {
$mc_correct = false;
break;
}
}
if ($mc_correct) {
$query_mcons .= 'a.Type NOT IN (\'' . implode('\', \'', $mc_article_types_no) . '\') AND ';
$article_mcons = true;
}
}
if (0 < count($mc_articles_yes)) {
$mc_correct = true;
foreach ($mc_articles_yes as $val) {
if (!is_numeric($val)) {
$mc_correct = false;
}
}
if ($mc_correct) {
$query_mcons .= 'a.Number IN (' . implode(', ', $mc_articles_yes) . ') AND ';
$article_mcons = true;
}
}
if (0 < count($mc_articles_no)) {
$mc_correct = true;
foreach ($mc_articles_no as $val) {
if (!is_numeric($val)) {
$mc_correct = false;
}
}
if ($mc_correct) {
$query_mcons .= 'a.Number NOT IN (' . implode(', ', $mc_articles_no) . ') AND ';
$article_mcons = true;
}
}
if (0 < count($mc_issues)) {
$mc_correct = true;
foreach ($mc_issues as $val) {
if (!is_numeric($val)) {
$mc_correct = false;
}
}
if ($mc_correct) {
$query_mcons .= 'a.NrIssue IN (' . implode(', ', $mc_issues) . ') AND ';
$article_mcons = true;
}
}
if (0 < count($mc_sections)) {
$mc_correct = true;
foreach ($mc_sections as $val) {
if (!is_numeric($val)) {
$mc_correct = false;
}
}
if ($mc_correct) {
$query_mcons .= 'a.NrSection IN (' . implode(', ', $mc_sections) . ') AND ';
$article_mcons = true;
}
}
foreach ($mc_dates as $one_date_type => $one_date_value_arr) {
if (0 == count($one_date_value_arr)) {
continue;
}
$one_date_value = $one_date_value_arr[0];
$one_date_value = trim($one_date_value);
$one_date_value = trim($one_date_value, "\"'");
$one_date_value = str_replace("'", "\"", $one_date_value);
$one_date_usage = 'CAST(a.PublishDate AS DATE) ';
$one_date_known = false;
if ('smaller_equal' == $one_date_type) {
$one_date_usage .= '<= ';
$one_date_known = true;
}
if ('smaller' == $one_date_type) {
$one_date_usage .= '< ';
$one_date_known = true;
}
if ('greater_equal' == $one_date_type) {
$one_date_usage .= '>= ';
$one_date_known = true;
}
if ('greater' == $one_date_type) {
$one_date_usage .= '> ';
$one_date_known = true;
}
if ('is' == $one_date_type) {
$one_date_usage .= '= ';
$one_date_known = true;
}
if ('not' == $one_date_type) {
$one_date_usage .= '<> ';
$one_date_known = true;
}
if (!$one_date_known) {
continue;
}
$one_date_usage .= "'{$one_date_value}' AND ";
$query_mcons .= $one_date_usage;
$article_mcons = true;
}
$mc_icons_usage = array();
foreach ($mc_icons as $one_icon_value) {
$one_icon_value = str_replace("'", "\"", $one_icon_value);
if (0 < strlen($one_icon_value)) {
$mc_icons_usage[] = $one_icon_value;
}
}
if (0 < count($mc_icons_usage)) {
$query_mcons .= "ml.poi_style IN ('" . implode("', '", $mc_icons_usage) . "') AND ";
$article_mcons = true;
}
if (0 < count($mc_topics)) {
$mc_topics_list = array();
$mc_topics_conn = 'OR';
if ($mc_topics_matchall) {
$mc_topics_conn = 'AND';
}
foreach ($mc_topics as $one_topic) {
if (!is_numeric($one_topic)) {
continue;
}
$mc_topics_list[] = 'EXISTS (SELECT at.TopicId FROM ArticleTopics AS at WHERE a.Number = at.NrArticle AND at.TopicId IN (' . Topic::BuildAllSubtopicsQuery($one_topic, false) . '))';
}
if (0 < count($mc_topics_list)) {
$query_mcons .= '(' . implode(" {$mc_topics_conn} ", $mc_topics_list) . ') AND ';
$article_mcons = true;
}
}
$mc_areas_list = array();
$mc_areas_conn = 'OR';
if ($mc_areas_matchall) {
$mc_areas_conn = 'AND';
}
foreach ($mc_areas as $one_area) {
if (is_object($one_area)) {
$one_area = get_object_vars($one_area);
}
$mc_rectangle = $one_area['rectangle'];
$mc_polygon = $one_area['polygon'];
if ($mc_rectangle && 2 == count($mc_rectangle)) {
$area_cons_res = Geo_MapLocation::GetGeoSearchSQLCons($mc_rectangle, 'rectangle', 'l');
if (!$area_cons_res['error']) {
$mc_areas_list[] = $area_cons_res['cons'];
$article_mcons = true;
}
}
if ($mc_polygon && 3 <= count($mc_polygon)) {
$area_cons_res = Geo_MapLocation::GetGeoSearchSQLCons($mc_polygon, 'polygon', 'l');
$area_cons_res_finer = null;
if ($mc_areas_exact) {
$area_cons_res_finer = Geo_MapLocation::GetGeoSearchPointInPolygon($mc_polygon, 'l');
}
if (!$area_cons_res['error']) {
$one_area_cons = $area_cons_res['cons'];
if ($mc_areas_exact && !$area_cons_res_finer['error']) {
$one_area_cons = "({$one_area_cons} AND " . $area_cons_res_finer['cons'] . ')';
}
$mc_areas_list[] = $one_area_cons;
$article_mcons = true;
}
}
}
if (0 < count($mc_areas_list)) {
$query_mcons .= '(' . implode(" {$mc_areas_conn} ", $mc_areas_list) . ') AND ';
$article_mcons = true;
}
$mmu_test_join = '%%mmu_test_join%%';
$mmu_test_spec = '%%mmu_test_spec%%';
$multimedia_test_common = "EXISTS (SELECT mlmu.id FROM MapLocationMultimedia AS mlmu {$mmu_test_join} WHERE mlmu.fk_maplocation_id = ml.id {$mmu_test_spec}) AND ";
$multimedia_test_basic = $multimedia_test_common;
$multimedia_test_basic = str_replace($mmu_test_join, '', $multimedia_test_basic);
$multimedia_test_basic = str_replace($mmu_test_spec, '', $multimedia_test_basic);
$multimedia_test_spec = $multimedia_test_common;
$multimedia_test_spec = str_replace($mmu_test_join, 'INNER JOIN Multimedia AS mu ON mlmu.fk_multimedia_id = mu.id ', $multimedia_test_spec);
$multimedia_test_image = $multimedia_test_spec;
$multimedia_test_image = str_replace($mmu_test_spec, "AND mu.media_type = 'image'", $multimedia_test_image);
$multimedia_test_video = $multimedia_test_spec;
$multimedia_test_video = str_replace($mmu_test_spec, "AND mu.media_type = 'video'", $multimedia_test_video);
if ($mc_filter_image) {
$query_mcons .= $multimedia_test_image;
$article_mcons = true;
}
if ($mc_filter_video) {
$query_mcons .= $multimedia_test_video;
$article_mcons = true;
}
if ($mc_filter_mm) {
$query_mcons .= $multimedia_test_basic;
$article_mcons = true;
}
$queryStr .= 'WHERE ';
if ($article_mcons) {
$queryStr .= $query_mcons;
}
$queryStr .= "a.Published = 'Y' AND a.IdLanguage = {$ps_languageId} ";
} else {
$queryStr .= "WHERE ml.fk_map_id = {$ps_mapId} ";
}
$queryStr_mid = "";
$queryStr_mid .= "AND mll.fk_language_id = {$ps_languageId} ";
if ($ps_preview) {
$queryStr_mid .= "AND mll.poi_display = 1 ";
}
if ($mc_mapCons) {
$queryStr_mid .= 'GROUP BY ml.fk_location_id, mll.fk_content_id, ml.poi_style, mll.poi_display, image_mm, video_mm ';
}
$queryStr .= $queryStr_mid;
$queryStr = $queryStr_start . $queryStr;
$queryStr .= 'ORDER BY ';
foreach ($ps_orders as $one_order) {
foreach ($one_order as $cur_order_col => $cur_order_dir) {
$queryStr .= "{$cur_order_col} {$cur_order_dir}, ";
}
}
$queryStr .= 'ml.rank, ml.id, mll.id';
$rows = $g_ado_db->GetAll($queryStr);
if (is_array($rows)) {
$p_count = count($rows);
if ($mc_limit) {
$rows = array_slice($rows, $mc_start, $mc_limit);
}
if ($mc_start && !$mc_limit) {
$rows = array_slice($rows, $mc_start);
}
}
$mm_objs = array();
if (is_array($rows)) {
foreach ($rows as $row) {
$tmp_loc = trim(strtolower($row['loc']));
$loc_matches = array();
if (!preg_match('/^point\\((?P<latitude>[\\d.-]+)\\s(?P<longitude>[\\d.-]+)\\)$/', $tmp_loc, $loc_matches)) {
continue;
}
$tmp_latitude = $loc_matches['latitude'];
$tmp_longitude = $loc_matches['longitude'];
$tmpPoint = array();
$tmpPoint['latitude'] = $tmp_latitude;
$tmpPoint['longitude'] = $tmp_longitude;
$tmpPoint['loc_id'] = $row['ml_id'];
$tmpPoint['con_id'] = $row['mll_id'];
$tmpPoint['style'] = $row['poi_style'];
$tmpPoint['rank'] = $row['rank'];
$tmpPoint['display'] = $row['poi_display'];
$tmpPoint['title'] = $row['poi_name'];
$tmpPoint['link'] = $row['poi_link'];
$tmpPoint['perex'] = $row['poi_perex'];
$tmpPoint['content_type'] = $row['poi_content_type'];
$tmpPoint['content'] = $row['poi_content'];
$tmpPoint['text'] = $row['poi_text'];
$tmpPoint['image_mm'] = 0;
$tmpPoint['image_src'] = '';
$tmpPoint['image_width'] = '';
$tmpPoint['image_height'] = '';
$tmp_image = null;
$tmp_video = null;
if ($row['image_mm']) {
$multimedia_spec = $row['image_mm'];
$multimedia_link = $multimedia_spec;
// the dynamic maps (i.e. with mc_mapCons) have grouping by multimedia,
// while article maps have all multimedia separated (and read with concat)
if (!$mc_mapCons) {
$multimedia_spec_arr = explode('-', $multimedia_spec);
if (2 == count($multimedia_spec_arr)) {
$multimedia_spec = 0 + $multimedia_spec_arr[0];
$multimedia_link = 0 + $multimedia_spec_arr[1];
}
}
$tmpPoint['image_mm'] = $multimedia_link;
$tmp_image = new Geo_Multimedia($multimedia_spec);
if ($tmp_image) {
$tmpPoint['image_src'] = $tmp_image->getSrc();
$tmpPoint['image_width'] = $tmp_image->getWidth();
$tmpPoint['image_height'] = $tmp_image->getHeight();
}
}
$tmpPoint['video_mm'] = 0;
$tmpPoint['video_id'] = '';
$tmpPoint['video_type'] = '';
$tmpPoint['video_width'] = '';
$tmpPoint['video_height'] = '';
if ($row['video_mm']) {
$multimedia_spec = $row['video_mm'];
$multimedia_link = $multimedia_spec;
// the dynamic maps (i.e. with mc_mapCons) have grouping by multimedia,
// while article maps have all multimedia separated (and read with concat)
if (!$mc_mapCons) {
$multimedia_spec_arr = explode('-', $multimedia_spec);
if (2 == count($multimedia_spec_arr)) {
$multimedia_spec = 0 + $multimedia_spec_arr[0];
$multimedia_link = 0 + $multimedia_spec_arr[1];
}
}
$tmpPoint['video_mm'] = $multimedia_link;
$tmp_video = new Geo_Multimedia($multimedia_spec);
if ($tmp_video) {
$tmpPoint['video_id'] = $tmp_video->getSrc();
$tmpPoint['video_type'] = $tmp_video->getSpec();
$tmpPoint['video_width'] = $tmp_video->getWidth();
$tmpPoint['video_height'] = $tmp_video->getHeight();
}
}
if ($tmp_image || $tmp_video) {
$mm_objs[$row['ml_id']] = array('image' => $tmp_image, 'video' => $tmp_video);
}
// for the list-of-objects array
$tmpPoint['map_id'] = $ps_mapId;
$tmpPoint['art_number'] = 0;
$tmpPoint['art_numbers'] = '';
if ($mc_mapCons) {
$tmpPoint['map_id'] = $row['m_id'];
$tmpPoint['art_number'] = $row['art_number'];
$tmpPoint['art_numbers'] = $row['art_numbers'];
}
$tmpPoint['geo_id'] = $row['loc_id'];
$tmpPoint['geo_type'] = $row['poi_type'];
$tmpPoint['geo_style'] = $row['poi_type_style'];
$tmpPoint['geo_radius'] = $row['l_radius'];
$tmpPoint['geo_user'] = $row['l_user'];
$tmpPoint['geo_updated'] = $row['l_updated'];
$tmpPoint['txt_id'] = $row['con_id'];
$tmpPoint['txt_user'] = $row['c_user'];
$tmpPoint['txt_updated'] = $row['c_updated'];
$dataArray[] = $tmpPoint;
}
}
if (0 == count($dataArray)) {
return array();
}
$dataArray_tmp = $dataArray;
$objsArray = array();
$dataArray = array();
foreach ($dataArray_tmp as $one_poi) {
$one_poi_source = array('id' => $one_poi['loc_id'], 'fk_map_id' => $one_poi['map_id'], 'fk_location_id' => $one_poi['geo_id'], 'poi_style' => $one_poi['style'], 'rank' => $one_poi['rank']);
$one_poi_obj = new self($one_poi_source, true);
$one_geo_source = array('poi_location' => null, 'poi_type' => $one_poi['geo_type'], 'poi_type_style' => $one_poi['geo_style'], 'poi_center' => null, 'poi_radius' => $one_poi['geo_radius'], 'IdUser' => $one_poi['geo_user'], 'time_updated' => $one_poi['geo_updated'], 'latitude' => $one_poi['latitude'], 'longitude' => $one_poi['longitude']);
$one_poi_obj->location = new Geo_Location($one_geo_source, true);
$one_lan_source = array('id' => $one_poi['con_id'], 'fk_maplocation_id' => $one_poi['loc_id'], 'fk_language_id' => $ps_languageId, 'fk_content_id' => $one_poi['txt_id'], 'poi_display' => $one_poi['display']);
$one_poi_obj->setLanguage($ps_languageId, new Geo_MapLocationLanguage(NULL, 0, $one_lan_source, true));
$one_txt_source = array('id' => $one_poi['txt_id'], 'poi_name' => $one_poi['title'], 'poi_link' => $one_poi['link'], 'poi_perex' => $one_poi['perex'], 'poi_content_type' => $one_poi['content_type'], 'poi_content' => $one_poi['content'], 'poi_text' => $one_poi['text'], 'IdUser' => $one_poi['txt_user'], 'time_updated' => $one_poi['txt_updated']);
$one_poi_obj->setContent($ps_languageId, new Geo_MapLocationContent(NULL, NULL, $one_txt_source, true));
if (array_key_exists($one_poi['loc_id'], $mm_objs)) {
$poi_mm = $mm_objs[$one_poi['loc_id']];
$one_poi_obj->multimedia = array();
foreach ($poi_mm as $one_mm) {
if ($one_mm) {
$one_poi_obj->multimedia[] = $one_mm;
}
}
}
$objsArray[] = $one_poi_obj;
if (!$p_skipCache || $ps_saveArray) {
$dataArray[] = $one_poi;
}
}
if (!$p_skipCache && CampCache::IsEnabled()) {
$cacheList_arr->storeInCache(array('count' => $p_count, 'data' => $dataArray));
$cacheList_obj->storeInCache(array('count' => $p_count, 'data' => $objsArray));
}
if (empty(self::$s_found_maplocations_list_ext)) {
self::$s_found_maplocations_list_ext = array();
}
self::$s_found_maplocations_list_ext[$list_spec_str] = array('count' => $p_count, 'list_raw' => $dataArray, 'list_obj' => $objsArray);
if ($ps_saveArray) {
$p_rawData = $dataArray;
}
return $objsArray;
}
