function getList(&$pListHash)
{
global $gBitDb;
$bindVars = array();
parent::prepGetList($pListHash);
$mid = '';
if (@BitBase::verifyId($pListHash['nl_id'])) {
$mid .= (empty($mid) ? 'WHERE' : 'AND') . ' n.nl_id=? ';
$bindVars[] = $pListHash['nl_id'];
}
if ($pListHash['find']) {
$findesc = '%' . $pListHash['find'] . '%';
$mid .= (empty($mid) ? 'WHERE' : 'AND') . ' (lc.`title` like ? or lc.`data` like ?)';
$bindVars[] = $findesc;
$bindVars[] = $findesc;
}
$query = "SELECT `edition_id` AS `hash_key`, ne.*, lc.*, lc2.`title` AS `newsletter_title`\n\t\t\t\t FROM `" . BIT_DB_PREFIX . "newsletters_editions` ne\n\t\t\t\t \tINNER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc ON( lc.`content_id`=ne.`content_id` )\n\t\t\t\t \tINNER JOIN `" . BIT_DB_PREFIX . "newsletters` n ON( ne.`nl_content_id`=n.`content_id` )\n\t\t\t\t \tLEFT OUTER JOIN `" . BIT_DB_PREFIX . "liberty_content` lc2 ON( n.`content_id`=lc2.`content_id` )\n\t\t\t\t {$mid} ORDER BY " . $gBitDb->convertSortmode($pListHash['sort_mode']);
$query_cant = "select count(*) from `" . BIT_DB_PREFIX . "newsletters` n INNER JOIN `" . BIT_DB_PREFIX . "newsletters_editions` ne ON(n.`content_id`=ne.`nl_content_id`) {$mid}";
$ret = $gBitDb->getAssoc($query, $bindVars, $pListHash['max_records'], $pListHash['offset']);
foreach (array_keys($ret) as $k) {
$ret[$k]['display_url'] = BitNewsletterEdition::getDisplayUrlFromHash($ret[$k]);
// remove formating tags
$data = preg_replace('/{[^{}]*}/', '', $ret[$k]['data']);
// $ret[$k]['parsed'] = BitNewsletterEdition::parseData( $data, $ret[$k]['format_guid'] );
}
$pListHash['total_records'] = $gBitDb->getOne($query_cant, $bindVars);
$pListHash['block_pages'] = 5;
$pListHash['total_pages'] = ceil($pListHash['total_records'] / $pListHash['max_records']);
$pListHash['current_page'] = !empty($pListHash['offset']) ? floor($pListHash['offset'] / $pListHash['max_records']) + 1 : 1;
return $ret;
}
/**
* 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;
}
public static function prepGetList(&$pListHash)
{
// keep a copy of user_id for later...
$userId = parent::getParameter($pListHash, 'user_id');
parent::prepGetList($pListHash);
if (empty($pListHash['query_string'])) {
$pListHash['query_string'] = '';
}
$dynamicParams = array('page', 'max_records', 'sort_mode');
foreach ($_GET as $key => $value) {
if (!in_array($key, $dynamicParams)) {
$pListHash['query_string'] .= "&{$key}={$value}";
}
}
if (!empty($userId)) {
// LibertyContent clobbers user_id for security reasons base on list_content. For Commerce, we want to loosen this up.
$pListHash['user_id'] = $userId;
}
}