This is used by subclasses to add extra SQL options to the end of a query.
| public static ProcessOptions ( string $p_queryStr, array $p_sqlOptions ) : string | ||
| $p_queryStr | string | The current SQL query. |
| $p_sqlOptions | array | Available options:
'LIMIT' => [max_rows_to_fetch]
'LIMIT' => array('START'=>[Starting_index],'MAX_ROWS'=>[Max_rows_to_fetch]
'ORDER BY' => [column_name]
'ORDER BY' => array([column_name_1], [column_name_2], ...)
'ORDER BY' => array([column_name_1]=>[ASC|DESC], [column_name_2]=>[ASC|DESC], ...)
|
| return | string | Original SQL query with the options appended at the end. |
/**
* Return all publications as an array of Publication objects.
*
* @param string $p_name
* @param int $p_aliasId
* @param array $p_sqlOptions
*
* @return array
*/
public static function GetPublications($p_name = null, $p_aliasId = null, $p_sqlOptions = null)
{
global $g_ado_db;
if (is_null($p_sqlOptions)) {
$p_sqlOptions = array();
}
if (!isset($p_sqlOptions["ORDER BY"])) {
$p_sqlOptions["ORDER BY"] = array("Name" => "ASC");
}
$tmpPub = new Publication();
$columns = $tmpPub->getColumnNames(true);
$queryStr = 'SELECT ' . implode(',', $columns) . ', Aliases.Name as Alias' . ', URLTypes.Name as URLType' . ', Languages.OrigName as NativeName' . ' FROM Publications, Languages, Aliases, URLTypes' . ' WHERE Publications.IdDefaultAlias = Aliases.Id ' . ' AND Publications.IdURLType = URLTypes.Id ' . ' AND Publications.IdDefaultLanguage = Languages.Id ';
if (is_string($p_name)) {
$queryStr .= " AND Publications.Name=" . $g_ado_db->escape($p_name);
}
if (is_numeric($p_aliasId)) {
$queryStr .= " AND Publications.IdDefaultAlias={$p_aliasId}";
}
$queryStr = DatabaseObject::ProcessOptions($queryStr, $p_sqlOptions);
$publications = DbObjectArray::Create('Publication', $queryStr);
return $publications;
}
/**
* @param array $p_sqlOptions
* @param boolean $p_update
* @param boolean $p_useFilter
* filter templates matching setting in SystemPrefs
* @param boolean $p_strict
* if true, retrieves only template (tpl) files
*
* @return array
*/
public static function GetAllTemplates($p_sqlOptions = null, $p_update = true, $p_useFilter = false, $p_strict = false)
{
if ($p_update) {
self::UpdateStatus();
}
$queryStr = 'SELECT * FROM Templates';
if ($p_strict) {
$queryStr .= ' WHERE Type < 5';
}
if ($p_useFilter && ($rexeg = self::GetTemplateFilterRegex(true))) {
$queryStr .= $p_strict == false ? ' WHERE ' : ' AND ';
$queryStr .= 'Name NOT REGEXP "' . self::GetTemplateFilterRegex(true) . '"';
}
if (!is_null($p_sqlOptions)) {
$queryStr = DatabaseObject::ProcessOptions($queryStr, $p_sqlOptions);
} else {
$queryStr .= ' ORDER BY Level ASC, Name ASC';
}
$templates = DbObjectArray::Create('Template', $queryStr);
return $templates;
}
/**
* Get a list of articles. You can be as specific or as general as you
* like with the parameters: e.g. specifying only p_publication will get
* you all the articles in a particular publication. Specifying all
* parameters will get you all the articles in a particular section with
* the given language.
*
* This function differs from GetArticles in that any LIMIT set
* in $p_sqlOptions will be interpreted as the number of articles to
* return regardless of how many times an article has been translated.
* E.g. an article translated three times would be counted as one
* article, but counted as three articles in GetArticles().
*
* @param int $p_publicationId -
* The publication ID.
*
* @param int $p_issueNumber -
* The issue number.
*
* @param int $p_sectionNumber -
* The section number.
*
* @param int $p_languageId -
* The language ID.
*
* @param int $p_preferredLanguage -
* If specified, list the articles in this language before others.
*
* @param array $p_sqlOptions
*
* @param boolean $p_countOnly
* Whether to run just the number of articles that match the
* search criteria.
*
* @return array
* Return an array of Article objects.
*/
public static function GetArticlesGrouped($p_publicationId = null,
$p_issueNumber = null,
$p_sectionNumber = null,
$p_languageId = null,
$p_preferredLanguage = null,
$p_sqlOptions = null,
$p_countOnly = false)
{
global $g_ado_db;
// Constraints
$whereClause = array();
if (!is_null($p_publicationId)) {
$whereClause[] = "IdPublication=$p_publicationId";
}
if (!is_null($p_issueNumber)) {
$whereClause[] = "NrIssue=$p_issueNumber";
}
if (!is_null($p_sectionNumber)) {
$whereClause[] = "NrSection=$p_sectionNumber";
}
if (!is_null($p_languageId)) {
$whereClause[] = "IdLanguage=$p_languageId";
}
$selectStr = "DISTINCT(Number)";
if ($p_countOnly) {
$selectStr = "COUNT(DISTINCT(Number))";
}
// Get the list of unique article numbers
$queryStr1 = "SELECT $selectStr FROM Articles ";
if (count($whereClause) > 0) {
$queryStr1 .= ' WHERE '. implode(' AND ', $whereClause);
}
if ($p_countOnly) {
$count = $g_ado_db->GetOne($queryStr1);
return $count;
}
if (is_null($p_sqlOptions)) {
$p_sqlOptions = array();
}
if (!isset($p_sqlOptions['ORDER BY'])) {
$p_sqlOptions['ORDER BY'] = array("ArticleOrder" => "ASC",
"Number"=> "DESC");
}
$queryStr1 = DatabaseObject::ProcessOptions($queryStr1, $p_sqlOptions);
$uniqueArticleNumbers = $g_ado_db->GetCol($queryStr1);
// Get the articles
$queryStr2 = 'SELECT *';
// This causes the preferred language to be listed first.
if (!is_null($p_preferredLanguage)) {
$queryStr2 .= ", abs($p_preferredLanguage - IdLanguage) as LanguageOrder ";
}
$queryStr2 .= ' FROM Articles';
$uniqueRowsClause = '';
if (count($uniqueArticleNumbers) > 0) {
$uniqueRowsClause = '(Number=' .implode(' OR Number=', $uniqueArticleNumbers).')';
}
// Add the WHERE clause.
if ((count($whereClause) > 0) || ($uniqueRowsClause != '')) {
$queryStr2 .= ' WHERE ';
if (count($whereClause) > 0) {
$queryStr2 .= '(' . implode(' AND ', $whereClause) .')';
}
if ($uniqueRowsClause != '') {
if (count($whereClause) > 0) {
$queryStr2 .= ' AND ';
}
$queryStr2 .= $uniqueRowsClause;
}
}
// ORDER BY clause
if (!is_null($p_preferredLanguage)) {
$p_sqlOptions['ORDER BY']['LanguageOrder'] = "ASC";
$p_sqlOptions['ORDER BY']['IdLanguage'] = "ASC";
}
unset($p_sqlOptions['LIMIT']);
$queryStr2 = DatabaseObject::ProcessOptions($queryStr2, $p_sqlOptions);
$articles = DbObjectArray::Create('Article', $queryStr2);
return $articles;
} // fn GetUniqueArticles
/**
* Get all the issues in the given publication as return them as an array
* of Issue objects.
*
* @param int $p_publicationId
* The publication ID.
*
* @param int $p_languageId
* (Optional) Only return issues with this language.
*
* @param int $p_issueId
* (Optional) Only return issues with this Issue ID.
*
* @param string $p_urlName
* (Optional) Only return issues that match this URL Name.
*
* @param int $p_preferredLanguage
* (Optional) List this language before others. This will override any 'ORDER BY' sql
* options you have.
*
* @param array $p_sqlOptions
*
* @return array
*/
public static function GetIssues($p_publicationId = null,
$p_languageId = null,
$p_issueNumber = null,
$p_urlName = null,
$p_preferredLanguage = null,
$p_publishedOnly = false,
$p_sqlOptions = null, $p_skipCache = false)
{
global $g_ado_db;
if (!$p_skipCache && CampCache::IsEnabled()) {
$paramsArray['publication_id'] = (is_null($p_publicationId)) ? 'null' : $p_publicationId;
$paramsArray['language_id'] = (is_null($p_languageId)) ? 'null' : $p_languageId;
$paramsArray['issue_number'] = (is_null($p_issueNumber)) ? 'null' : $p_issueNumber;
$paramsArray['url_name'] = (is_null($p_urlName)) ? 'null' : $p_urlName;
$paramsArray['preferred_language'] = (is_null($p_preferredLanguage)) ? 'null' : $p_preferredLanguage;
$paramsArray['published_only'] = $p_publishedOnly ? 'true' : 'false';
$paramsArray['sql_options'] = (is_null($p_sqlOptions)) ? 'null' : $p_sqlOptions;
$cacheListObj = new CampCacheList($paramsArray, __METHOD__);
$issuesList = $cacheListObj->fetchFromCache();
if ($issuesList !== false && is_array($issuesList)) {
return $issuesList;
}
}
$tmpIssue = new Issue();
$columnNames = $tmpIssue->getColumnNames(true);
$queryStr = 'SELECT '.implode(',', $columnNames);
if (!is_null($p_preferredLanguage)) {
$queryStr .= ", abs(IdLanguage-$p_preferredLanguage) as LanguageOrder";
$p_sqlOptions['ORDER BY'] = array('Number' => 'DESC', 'LanguageOrder' => 'ASC');
}
// We have to display the language name so oftern that we might
// as well fetch it by default.
$queryStr .= ', Languages.OrigName as LanguageName';
$queryStr .= ' FROM Issues, Languages ';
$whereClause = array();
$whereClause[] = "Issues.IdLanguage=Languages.Id";
if (!is_null($p_publicationId)) {
$whereClause[] = "Issues.IdPublication=$p_publicationId";
}
if (!is_null($p_languageId)) {
$whereClause[] = "Issues.IdLanguage=$p_languageId";
}
if (!is_null($p_issueNumber)) {
$whereClause[] = "Issues.Number=$p_issueNumber";
}
if (!is_null($p_urlName)) {
$whereClause[] = "Issues.ShortName='".$g_ado_db->escape($p_urlName)."'";
}
if ($p_publishedOnly) {
$whereClause[] = "Issues.Published = 'Y'";
}
if (count($whereClause) > 0) {
$queryStr .= ' WHERE '.implode(' AND ', $whereClause);
}
$queryStr = DatabaseObject::ProcessOptions($queryStr, $p_sqlOptions);
$issues = array();
$rows = $g_ado_db->GetAll($queryStr);
if (is_array($rows)) {
foreach ($rows as $row) {
$tmpObj = new Issue();
$tmpObj->fetch($row);
$tmpObj->m_languageName = $row['LanguageName'];
$issues[] = $tmpObj;
}
}
if (!$p_skipCache && CampCache::IsEnabled()) {
$cacheListObj->storeInCache($issues);
}
return $issues;
} // fn GetIssues
/**
* Get the logs.
*
* @param int $p_eventId
* @param array $p_sqlOptions
*
* @return array
*/
public static function GetLogs($p_eventId = null, $p_sqlOptions = null)
{
if (is_null($p_sqlOptions) || !isset($p_sqlOptions['ORDER BY'])) {
$p_sqlOptions['ORDER BY'] = array('time_created' => 'DESC');
}
$tmpLog = new Log();
$columns = $tmpLog->getColumnNames(true);
$queryStr = "SELECT " . implode(", ", $columns) . ", INET_NTOA(Log.user_ip) AS user_ip_addr" . ", liveuser_users.Name as full_name" . ", liveuser_users.UName as user_name" . " FROM Log" . " LEFT JOIN liveuser_users" . " ON Log.fk_user_id = liveuser_users.Id";
if (!is_null($p_eventId)) {
$queryStr .= " WHERE Log.fk_event_id={$p_eventId}";
}
$queryStr = DatabaseObject::ProcessOptions($queryStr, $p_sqlOptions);
$logLines = DbObjectArray::Create('Log', $queryStr);
return $logLines;
}
/**
* 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;
}
/**
* Get the comments and their associated articles.
*
* @param string $p_status
* Can be 'approved' or 'unapproved'.
* @param boolean $p_getTotal
* If TRUE, return the number of comments that match the search
* criteria and not the actual records.
* @param string $p_searchString
* A string to search for.
* @param array $p_sqlOptions
* See DatabaseObject::ProcessOptions().
* @return array
*/
public static function GetComments($p_status = 'approved', $p_getTotal = false,
$p_searchString = '', $p_sqlOptions = null)
{
global $PHORUM;
global $g_ado_db;
$messageTable = $PHORUM['message_table'];
$selectClause = "*";
if ($p_getTotal) {
$selectClause = "COUNT(*)";
}
$baseQuery = "SELECT $selectClause FROM ($messageTable"
." LEFT JOIN ArticleComments "
." ON $messageTable". ".thread=ArticleComments.fk_comment_id)"
." LEFT JOIN Articles ON ArticleComments.fk_article_number=Articles.Number"
." AND ArticleComments.fk_language_id=Articles.IdLanguage";
$whereQuery = "$messageTable.message_id != $messageTable.thread";
if ($p_status == 'approved') {
$whereQuery .= " AND status > 0";
} elseif ($p_status == 'unapproved') {
$whereQuery .= " AND status < 0";
}
if (!empty($p_searchString)) {
$p_searchString = mysql_real_escape_string($p_searchString);
if (!empty($whereQuery)) {
$whereQuery .= " AND ";
}
$search_columns = array(
'subject',
'body',
'email',
'author',
'ip',
'Name',
);
$whereAry = array();
foreach ($search_columns as $column) {
$whereAry[] = $column . " LIKE '%$p_searchString%'";
}
$whereQuery .= '(' . implode(' OR ', $whereAry) . ')';
}
if (!empty($whereQuery)) {
$baseQuery .= " WHERE ".$whereQuery;
}
// Default ORDER BY clause
if (is_null($p_sqlOptions) || !isset($p_sqlOptions['ORDER BY'])) {
$baseQuery .= " ORDER BY ".$PHORUM['message_table'].".message_id";
}
//echo $baseQuery."<br><br>";
if ($p_getTotal) {
$numComments = $g_ado_db->GetOne($baseQuery);
return $numComments;
} else {
$queryStr = DatabaseObject::ProcessOptions($baseQuery, $p_sqlOptions);
$rows = $g_ado_db->GetAll($queryStr);
$returnArray = array();
if (is_array($rows)) {
foreach ($rows as $row) {
$comment = new Phorum_message();
$comment->fetch($row);
$article = new Article();
$article->fetch($row);
$returnArray[] = array("comment" => $comment, "article" => $article);
}
}
return $returnArray;
}
} // fn GetComments
/**
* Do a simple search.
*
* @param array $p_columns
* Array of arrays of two strings: column name and search value.
* @param array $p_sqlOptions
* See ProcessOptions().
*
* @return array
*/
public static function Search($p_className, $p_columns = null, $p_sqlOptions = null)
{
global $g_ado_db;
if (!class_exists($p_className)) {
return array();
}
$tmpObj = new $p_className();
$queryStr = "SELECT * FROM " . $tmpObj->m_dbTableName;
if (is_array($p_columns) && count($p_columns) > 0) {
$contraints = array();
foreach ($p_columns as $item) {
if (count($item) == 2) {
list($columnName, $value) = $item;
$contraints[] = $g_ado_db->escapeKeyVal($columnName, $value);
}
}
$queryStr .= " WHERE " . implode(" AND ", $contraints);
}
$queryStr = DatabaseObject::ProcessOptions($queryStr, $p_sqlOptions);
$dbObjects = DbObjectArray::Create($p_className, $queryStr);
return $dbObjects;
}
