/**
* This function generates a list of records from the liberty_content database for use in a list page
*/
function getList(&$pParamHash)
{
global $gBitSystem, $gBitUser;
$BIT_DB_PREFIX = BIT_DB_PREFIX;
// this makes sure parameters used later on are set
LibertyMime::prepGetList($pParamHash);
$selectSql = $joinSql = $whereSql = '';
$bindVars = array();
$pParamHash['include_comments'] = 'y';
$this->getServicesSql('content_list_sql_function', $selectSql, $joinSql, $whereSql, $bindVars, NULL, $pParamHash);
// this will set $find, $sort_mode, $max_records and $offset
extract($pParamHash);
if (empty($find)) {
} elseif (is_array($find)) {
// you can use an array of pages
$whereSql .= " AND flc.`title` IN( " . implode(',', array_fill(0, count($find), '?')) . " )";
$bindVars = array_merge($bindVars, $find);
} elseif (is_string($find)) {
// or a string
$bindVars[] = '%' . strtoupper($find) . '%';
$whereSql .= " AND UPPER( lc.`title` ) LIKE ?";
}
// if we have the board's board_id (b) we use that, or if we have its content_id we can use that
if (!empty($pParamHash['b']) && $this->verifyId($pParamHash['b']) || !empty($pParamHash['content_id']) && $this->verifyId($pParamHash['content_id'])) {
$joinSql .= " INNER JOIN `{$BIT_DB_PREFIX}boards_map` map ON (map.`topic_content_id` = lcom.`root_id`)";
$joinSql .= " INNER JOIN `{$BIT_DB_PREFIX}boards` b ON (b.`content_id` = map.`board_content_id`)";
if (!empty($pParamHash['b'])) {
$whereSql .= " AND b.`board_id` = ?";
$bindVars[] = (int) $pParamHash['b'];
} else {
$whereSql .= " AND b.`content_id` = ?";
$bindVars[] = (int) $pParamHash['content_id'];
}
}
BitBoardTopic::loadTrack($selectSql, $joinSql);
// use adodb's substr property
$substr = $this->mDb->substr();
if ($this->mDb->mType == 'firebird') {
$substrSql = "SUBSTRING(s_lcom.`thread_forward_sequence` FROM 1 FOR 10) LIKE SUBSTRING(lcom.`thread_forward_sequence` FROM 1 FOR 10)";
} else {
$substrSql = "{$substr}(s_lcom.`thread_forward_sequence`, 1, 10) LIKE {$substr}(lcom.`thread_forward_sequence`, 1, 10)";
}
if ($gBitSystem->isFeatureActive('boards_posts_anon_moderation') && !($gBitUser->hasPermission('p_boards_update') || $gBitUser->hasPermission('p_boards_post_update'))) {
$whereSql .= " AND ((post.`is_approved` = 1) OR (lc.`user_id` >= 0))";
}
if ($gBitSystem->isFeatureActive('boards_posts_anon_moderation') && ($gBitUser->hasPermission('p_boards_update') || $gBitUser->hasPermission('p_boards_post_update'))) {
$selectSql .= ", ( SELECT COUNT(*)\n\t\t\t\t\t\t\t\tFROM `{$BIT_DB_PREFIX}liberty_comments` AS s_lcom\n\t\t\t\t\t\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` s_lc ON (s_lcom.`content_id` = s_lc.`content_id`)\n\t\t\t\t\t\t\t\t\tLEFT JOIN `{$BIT_DB_PREFIX}boards_posts` s ON( s_lcom.`comment_id` = s.`comment_id` )\n\t\t\t\t\t\t\t\tWHERE (" . $substrSql . ") AND ((s_lc.`user_id` < 0) AND (s.`is_approved` = 0 OR s.`is_approved` IS NULL))) AS unreg";
} else {
$selectSql .= ", 0 AS unreg";
}
$sort_sql = "flc." . $this->mDb->convertSortmode($sort_mode);
$query = "SELECT\n\t\t\t\t\t\tlc.`user_id` AS flc_user_id,\n\t\t\t\t\t\tlc.`created` AS flc_created,\n\t\t\t\t\t\tlc.`last_modified` AS flc_last_modified,\n\t\t\t\t\t\tlc.`title` AS title,\n\t\t\t\t\t\tlc.`content_id` AS flc_content_id,\n\n\t\t\t\t\t\tCOALESCE(post.`is_approved`,0) AS first_approved,\n\t\t\t\t\t\tlcom.`anon_name`,\n\n\t\t\t\t\t\tth.`parent_id` AS th_first_id,\n\t\t\t\t\t\tCOALESCE(th.`is_locked`,0) AS th_is_locked,\n\t\t\t\t\t\tCOALESCE(th.`is_moved`,0) AS th_is_moved,\n\t\t\t\t\t\tCOALESCE(th.`is_sticky`,0) AS th_is_sticky,\n\n\t\t\t\t\t\tlcom.`comment_id` AS th_thread_id,\n\t\t\t\t\t\tlcom.`root_id` AS th_root_id,\n\n\t\t\t\t\t\tlcom.`root_id` AS content_id,\n\t\t\t\t\t\tlc.`content_type_guid` AS content_type_guid,\n\t\t\t\t\t\tlc.`content_status_id` AS content_status_id,\n\n\t\t\t\t\t\t(\n\t\t\t\t\t\t\tSELECT COUNT(*)\n\t\t\t\t\t\t\tFROM `" . BIT_DB_PREFIX . "liberty_comments` s_lcom\n\t\t\t\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` s_lc ON (s_lcom.`content_id` = s_lc.`content_id`)\n\t\t\t\t\t\t\tWHERE (" . $substrSql . ")\n\t\t\t\t\t\t) AS post_count,\n\n\t\t\t\t\t\t(\n\t\t\t\t\t\t\tSELECT MAX(s_lc.created)\n\t\t\t\t\t\t\tFROM `" . BIT_DB_PREFIX . "liberty_comments` s_lcom\n\t\t\t\t\t\t\tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` s_lc ON (s_lcom.`content_id` = s_lc.`content_id`)\n\t\t\t\t\t\t\tWHERE (" . $substrSql . ")\n\t\t\t\t\t\t) AS last_post\n\n\t\t\t\t\t\t{$selectSql}\n\t\t\t\t\t\t\tFROM `{$BIT_DB_PREFIX}liberty_comments` lcom\n\t\t\t\t\t\t\tINNER JOIN `{$BIT_DB_PREFIX}liberty_content` lc ON( lc.`content_id` = lcom.`content_id` )\n\t\t\t\t\t\t\tLEFT JOIN `{$BIT_DB_PREFIX}boards_topics` th ON (th.`parent_id`=lcom.`content_id`)\n\t\t\t\t\t\t\tLEFT JOIN `{$BIT_DB_PREFIX}boards_posts` post ON (post.`comment_id` = lcom.`comment_id`)\n\t\t\t\t\t\t\t{$joinSql}\n\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\tlcom.`root_id`=lcom.`parent_id`\n\t\t\t\t\t\t\t{$whereSql}\n\t\t\t\t\t\tORDER BY\n\t\t\t\t\t\t\t11 DESC,\n\t\t\t\t\t\t\t10 ASC,\n\t\t\t\t\t\t\tlast_post DESC,\n\t\t\t\t\t\t\tlc.created DESC\n\t\t\t\t\t\t";
$query_cant = "SELECT count(*)\n\t\t\t\t\t\tFROM `{$BIT_DB_PREFIX}liberty_comments` lcom\n\t\t\t\t\t\t\tINNER JOIN `{$BIT_DB_PREFIX}liberty_content` lc ON( lc.`content_id` = lcom.`content_id` )\n\t\t\t\t\t\t\tLEFT JOIN `{$BIT_DB_PREFIX}boards_topics` th ON (th.`parent_id`=lcom.`content_id`)\n\t\t\t\t\t\t\tLEFT JOIN `{$BIT_DB_PREFIX}boards_posts` post ON (post.`comment_id` = lcom.`comment_id`)\n\t\t\t\t\t\t\t{$joinSql}\n\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\tlcom.`root_id`=lcom.`parent_id`\n\t\t\t\t\t\t\t{$whereSql}";
$result = $this->mDb->query($query, $bindVars, $max_records, $offset);
$ret = array();
while ($res = $result->fetchRow()) {
if (empty($res['anon_name'])) {
$res['anon_name'] = "Anonymous";
}
if ($res['th_is_moved'] > 0) {
$res['url'] = BOARDS_PKG_URL . "index.php?t=" . $res['th_is_moved'];
} else {
$res['url'] = BOARDS_PKG_URL . "index.php?t=" . $res['th_thread_id'];
}
$llc_data = BitBoardTopic::getLastPost($res);
$res = array_merge($res, $llc_data);
BitBoardTopic::track($res);
$res['flip'] = BitBoardTopic::getFlipFlop($res);
if (empty($res['title'])) {
$res['title'] = "[Thread " . $res['th_thread_id'] . "]";
}
$ret[] = $res;
}
$pParamHash["cant"] = $this->mDb->getOne($query_cant, $bindVars);
// add all pagination info to pParamHash
LibertyMime::postGetList($pParamHash);
return $ret;
}
