/** * 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; }