/**
* Constructor
*
* @param Blog can pass NULL if $p is passed
* @param string
* @param array
* @param
* @param string Order ("ASC"/"DESC")
* @param string List of fields to order by (separated by " ")
* @param integer Limit
*/
function CommentList($Blog, $comment_types = "'comment'", $show_statuses = array('published'), $p = '', $author = '', $order = 'DESC', $orderby = '', $limit = '')
{
global $DB;
global $cache_categories;
global $pagenow;
// Bleh !
// Call parent constructor:
parent::DataObjectList('T_comments', 'comment_', 'comment_ID', 'Item', NULL, $limit);
$this->sql = 'SELECT DISTINCT T_comments.*
FROM T_comments INNER JOIN T_items__item ON comment_post_ID = post_ID ';
if (!empty($p)) {
// Restrict to comments on selected post
$this->sql .= 'WHERE comment_post_ID = ' . $p;
} else {
$this->sql .= 'INNER JOIN T_postcats ON post_ID = postcat_post_ID
INNER JOIN T_categories othercats ON postcat_cat_ID = othercats.cat_ID ';
$aggregate_coll_IDs = $Blog->get_setting('aggregate_coll_IDs');
if (empty($aggregate_coll_IDs)) {
// We only want posts from the current blog:
$this->sql .= 'WHERE othercats.cat_blog_ID = ' . $Blog->ID;
} else {
// We are aggregating posts from several blogs:
$this->sql .= 'WHERE othercats.cat_blog_ID IN (' . $aggregate_coll_IDs . ')';
}
}
$this->sql .= ' AND comment_type IN (' . $comment_types . ') ';
/*
* ----------------------------------------------------
* Restrict to the statuses we want to show:
* ----------------------------------------------------
*/
if (!empty($show_statuses)) {
$this->sql .= ' AND comment_status IN (\'' . implode("', '", $show_statuses) . '\')';
}
// This one restricts to post statuses, but it doesn't work completely right:
// TODO: handle status dependencies with post
$this->sql .= ' AND ' . statuses_where_clause();
// order by stuff
if (!empty($order) && !in_array(strtoupper($order), array('ASC', 'DESC', 'RANDOM'))) {
$order = 'DESC';
}
if (empty($orderby)) {
$orderby = 'comment_date ' . $order . ', comment_ID ' . $order;
} else {
$orderby_array = explode(' ', $orderby);
$orderby = $orderby_array[0] . ' ' . $order;
if (count($orderby_array) > 1) {
for ($i = 1; $i < count($orderby_array); $i++) {
$orderby .= ', comment_' . $orderby_array[$i] . ' ' . $order;
}
}
}
if ($order == 'RANDOM') {
$orderby = 'RAND()';
}
$this->sql .= "ORDER BY {$orderby}";
if (!empty($this->limit)) {
$this->sql .= ' LIMIT ' . $this->limit;
}
// echo $this->sql;
$this->rows = $DB->get_results($this->sql, ARRAY_A);
// Prebuild and cache objects:
if ($this->result_num_rows = $DB->num_rows) {
// fplanque>> why this test??
$i = 0;
foreach ($this->rows as $row) {
// Prebuild object:
$this->Obj[$i] = new Comment($row);
// COPY (function)
// To avoid potential future waste, cache this object:
// $this->DataObjectCache->add( $this->Obj[$i] );
$i++;
}
}
}
/**
* Restrict to the visibility/sharing statuses we want to show
*
* @param array Restrict to these statuses
* @param string What blogs should be used to check the status permissions:
* - Empty string to use current blog setting
* - A list separated by ','
* - Value '*' to use all blogs
* - Value '-' to use only current blog without aggregated blog
*/
function where_visibility($show_statuses, $aggregate_coll_IDs = NULL)
{
$this->show_statuses = $show_statuses;
if (!isset($this->blog)) {
debug_die('Status restriction requires to work with a specific blog first.');
}
if (empty($aggregate_coll_IDs) && !empty($this->blog)) {
// Blog IDs are not defined, Use them depending on current collection setting
// NOTE! collection can be 0, for example, on disp=usercomments|useritems where we display data from all collections
$BlogCache =& get_BlogCache();
$Blog =& $BlogCache->get_by_ID($this->blog);
$aggregate_coll_IDs = $Blog->get_setting('aggregate_coll_IDs');
}
$blog_IDs = array();
if (empty($aggregate_coll_IDs) || $aggregate_coll_IDs == '-') {
// Get status restriction only for current blog
$this->WHERE_and(statuses_where_clause($show_statuses, $this->dbprefix, $this->blog, 'blog_post!', true, $this->author));
return;
// Exit here, because we don't need to check the permissions for multiple blogs
}
// Check status permission for multiple blogs
if ($aggregate_coll_IDs == '*') {
// Get the status restrictions for all blogs
global $DB;
$blog_IDs = $DB->get_col('SELECT blog_ID FROM T_blogs ORDER BY blog_ID');
} else {
// Get the status restrictions for several blogs
$blog_IDs = explode(',', $aggregate_coll_IDs);
}
$status_restrictions = array();
foreach ($blog_IDs as $blog_ID) {
// Check status permission for each blog separately
$status_restrictions[] = 'cat_blog_ID=' . $blog_ID . ' AND ' . statuses_where_clause($show_statuses, $this->dbprefix, $blog_ID, 'blog_post!', true, $this->author);
}
$this->WHERE_and('( ' . implode(' ) OR ( ', $status_restrictions) . ' )');
}
/**
* Get # of comments for each category in a blog
*
* @param integer Category ID
* @param integer Blog ID
*/
function get_commentcount_in_category($cat_ID, $blog_ID = NULL)
{
if (is_null($blog_ID)) {
global $blog;
$blog_ID = $blog;
}
global $DB, $number_of_comments_in_cat;
if (!isset($number_of_comments_in_cat[(string) $blog_ID])) {
global $posttypes_specialtypes;
$SQL = new SQL();
$SQL->SELECT('cat_ID, COUNT( comment_ID ) c');
$SQL->FROM('T_comments');
$SQL->FROM_add('LEFT JOIN T_postcats ON comment_item_ID = postcat_post_ID');
$SQL->FROM_add('LEFT JOIN T_categories ON postcat_cat_ID = cat_id');
$SQL->FROM_add('LEFT JOIN T_items__item ON comment_item_ID = post_id');
$SQL->WHERE('cat_blog_ID = ' . $DB->quote($blog_ID));
$SQL->WHERE_and('comment_type IN ( "comment", "trackback", "pingback" )');
$SQL->WHERE_and(statuses_where_clause(get_inskin_statuses($blog_ID, 'comment'), 'comment_', $blog_ID, 'blog_comment!', true));
// add where condition to show only those posts commetns which are visible for the current User
$SQL->WHERE_and(statuses_where_clause(get_inskin_statuses($blog_ID, 'post'), 'post_', $blog_ID, 'blog_post!', true));
if (!empty($posttypes_specialtypes)) {
// Get content post types, Exclide pages, intros, sidebar links and ads
$SQL->WHERE_and('post_ityp_ID NOT IN( ' . $DB->quote($posttypes_specialtypes) . ' )');
}
$SQL->GROUP_BY('cat_ID');
$number_of_comments_in_cat[(string) $blog_ID] = $DB->get_assoc($SQL->get());
}
return isset($number_of_comments_in_cat[(string) $blog_ID][$cat_ID]) ? (int) $number_of_comments_in_cat[(string) $blog_ID][$cat_ID] : 0;
}
/**
* Construct the where clause to limit retrieved posts/comment on their status
*
* TODO: asimo> would be good to move this function to an items and comments common file
*
* @param Array statuses of posts/comments we want to get
* @param string post/comment table db prefix
* @param integer blog ID
* @param string permission prefix: 'blog_post!' or 'blog_comment!'
* @param boolean filter statuses by the current user perm and current page, by default is true. It should be false only e.g. when we have to count comments awaiting moderation.
* @return string statuses where condition
*/
function statuses_where_clause($show_statuses = NULL, $dbprefix = 'post_', $req_blog = NULL, $perm_prefix = 'blog_post!', $filter_by_perm = true, $author_filter = NULL)
{
global $current_User, $blog, $DB;
if (is_null($req_blog)) {
// try to init request blog if it was not set
global $blog;
$req_blog = $blog;
}
if (empty($show_statuses)) {
// use in-skin statuses if show_statuses is empty
$show_statuses = get_inskin_statuses();
}
// init where clauses array
$where = array();
// Check modules item statuses where condition but only in case of the items, and don't need to check in case of comments
if ($req_blog && $perm_prefix == 'blog_post!') {
// If requested blog is set, then set additional "where" clauses from modules method, before we would manipulate the $show_statuses array
$modules_condition = modules_call_method('get_item_statuses_where_clause', array('blog_ID' => $req_blog, 'statuses' => $show_statuses));
if (!empty($modules_condition)) {
foreach ($modules_condition as $condition) {
if (!empty($condition)) {
// condition is not empty
$where[] = $condition;
}
}
}
}
if (is_logged_in(false)) {
// User is logged in and the account was activated
if ($current_User->check_perm('blogs', 'editall', false)) {
// User has permission to all blogs posts and comments, we don't have to check blog specific permissions.
$allowed_statuses_cond = get_allowed_statuses_condition($show_statuses, $dbprefix, NULL, $perm_prefix);
if (!empty($allowed_statuses_cond)) {
// condition is not empty
$where[] = $allowed_statuses_cond;
}
$filter_by_perm = false;
$show_statuses = NULL;
} elseif (!empty($author_filter) && $author_filter != $current_User->ID) {
// Author filter is set, but current_User is not the filtered user, then these statuses are not visible for sure
$show_statuses = array_diff($show_statuses, array('private', 'draft'));
}
}
if ($req_blog == 0 && $filter_by_perm) {
// This is a very special case when we must check visibility statuses from each blog separately
// Note: This case should not be called frequently, it may be really slow!!
$where_condition = '';
$condition = '';
if (in_array('published', $show_statuses)) {
// 'published' status is always allowed in case of all blogs, handle this condition separately
$where_condition = '( ' . $dbprefix . 'status = "published" )';
$condition = ' OR ';
}
if (!is_logged_in(false)) {
// When user is not logged in only the 'published' status is allowed, don't check more
return $where_condition;
}
if (in_array('community', $show_statuses)) {
// 'community' status is always allowed in case of all blogs when a user is logged in, handle this condition separately
$where_condition .= $condition . '( ' . $dbprefix . 'status = "community" )';
$condition = ' OR ';
}
// Remove 'published' and 'community' statuses because those were already handled
$show_statuses = array_diff($show_statuses, array('published', 'community'));
if (empty($show_statuses)) {
// return if there are no other status
return $where_condition;
}
// Select each blog
$blog_ids = $DB->get_col('SELECT blog_ID FROM T_blogs');
$sub_condition = '';
foreach ($blog_ids as $blog_id) {
// create statuses where clause condition for each blog separately
$status_perm = statuses_where_clause($show_statuses, $dbprefix, $blog_id, $perm_prefix, $filter_by_perm, $author_filter);
if ($status_perm) {
// User has permission to view some statuses on this blog
$sub_condition .= '( ( cat_blog_ID = ' . $blog_id . ' ) AND' . $status_perm . ' ) OR ';
}
}
if ($dbprefix == 'post_') {
// Item query condition
$from_table = 'FROM T_items__item';
$reference_column = 'post_ID';
} else {
// Comment query condition
$from_table = 'FROM T_comments';
$reference_column = 'comment_post_ID';
}
// Select each object ID which corresponds to the statuses condition.
// Note: This is a very slow query when there is many post/comments. This case should not be used frequently.
$sub_query = 'SELECT ' . $dbprefix . 'ID ' . $from_table . '
INNER JOIN T_postcats ON ' . $reference_column . ' = postcat_post_ID
INNER JOIN T_categories ON postcat_cat_ID = cat_ID
WHERE (' . substr($sub_condition, 0, strlen($sub_condition) - 3) . ')';
$object_ids = implode(',', $DB->get_col($sub_query));
if ($object_ids) {
// If thre is at least one post or comment
$where_condition .= $condition . '( ' . $dbprefix . 'ID IN (' . $object_ids . ') )';
}
return $where_condition;
}
if ($filter_by_perm) {
// filter allowed statuses by the current User perms and by the current page ( front or back office )
$allowed_statuses_cond = get_allowed_statuses_condition($show_statuses, $dbprefix, $req_blog, $perm_prefix);
if (!empty($allowed_statuses_cond)) {
// condition is not empty
$where[] = $allowed_statuses_cond;
}
} elseif (count($show_statuses)) {
// we are not filtering so all status are allowed, add allowed statuses condition
$where[] = $dbprefix . 'status IN ( \'' . implode('\',\'', $show_statuses) . '\' )';
}
$where = count($where) > 0 ? ' ( ' . implode(' OR ', $where) . ' ) ' : '';
// echo $where;
return $where;
}
/**
* Restrict to specific statuses
*
* @param string List of statuses to restrict to (must have been previously validated)
* @param boolean Filter by user permission. Set to false to select each comment with the correspondong visibily statuses even if current User has no permission to view them.
*/
function where_statuses($show_statuses, $filter_by_perm = true)
{
global $DB;
if (empty($show_statuses)) {
// initialize if emty
$show_statuses = get_visibility_statuses('keys', array('trash', 'redirected'));
}
$this->show_statuses = $show_statuses;
if ($filter_by_perm) {
// show not published comments corresponding to the given blog perms
// When Blog is empty we must set blog param to 0, this way we will check all blogs
$blog = empty($this->Blog) ? 0 : $this->Blog->ID;
$this->WHERE_and(statuses_where_clause($this->show_statuses, $this->dbprefix, $blog, 'blog_comment!', true, $this->author));
} else {
$list = '';
$sep = '';
foreach ($show_statuses as $status) {
$list .= $sep . $DB->quote($status);
$sep = ',';
}
$this->WHERE_and($this->dbprefix . 'status IN (' . $list . ')');
}
}
/**
* Generic comments/trackbacks/pingbacks counting
*
* @todo check this in a multiblog page...
* @todo This should support visibility: at least in the default front office (_feedback.php), there should only the number of visible comments/trackbacks get used ({@link Item::feedback_link()}).
*
* @param integer
* @param string what to count
* @param mixed string or array to count comments with this/these status(es)
* @param boolean set true to count expired comments, leave on false otherwise
*/
function generic_ctp_number($post_id, $mode = 'comments', $status = 'published', $count_expired = false, $filter_by_perm = true)
{
global $DB, $debug, $postdata, $cache_ctp_number, $preview, $servertimenow, $blog;
if ($preview) {
// we are in preview mode, no comments yet!
return 0;
}
$show_statuses = is_admin_page() ? get_visibility_statuses('keys', array('trash', 'redirected')) : get_inskin_statuses();
$filter_index = $filter_by_perm ? 0 : 1;
if (!isset($cache_ctp_number) || !isset($cache_ctp_number[$filter_index][$post_id])) {
// we need a query to count comments
$count_SQL = new SQL();
$count_SQL->SELECT('comment_post_ID, comment_type, comment_status, COUNT(*) AS type_count');
$count_SQL->FROM('T_comments');
$count_SQL->GROUP_BY('comment_post_ID, comment_type, comment_status');
if (!empty($blog)) {
$count_SQL->WHERE(statuses_where_clause($show_statuses, 'comment_', $blog, 'blog_comment!', $filter_by_perm));
}
if (!$count_expired) {
$count_SQL->FROM_add('LEFT JOIN T_items__item_settings as expiry_setting ON comment_post_ID = iset_item_ID AND iset_name = "post_expiry_delay"');
$count_SQL->WHERE_and('expiry_setting.iset_value IS NULL OR expiry_setting.iset_value = "" OR TIMESTAMPDIFF(SECOND, comment_date, ' . $DB->quote(date2mysql($servertimenow)) . ') < expiry_setting.iset_value');
}
}
// init statuses count array
$statuses_array = array('published' => 0, 'community' => 0, 'protected' => 0, 'private' => 0, 'review' => 0, 'draft' => 0, 'deprecated' => 0, 'trash' => 0, 'total' => 0);
/*
* Make sure cache is loaded for current display list:
*/
if (!isset($cache_ctp_number) || !isset($cache_ctp_number[$filter_index])) {
global $postIDlist, $postIDarray;
// if( $debug ) echo "LOADING generic_ctp_number CACHE for posts: $postIDlist<br />";
if (!empty($postIDlist)) {
foreach ($postIDarray as $tmp_post_id) {
// Initializes each post to nocount!
$cache_ctp_number[$filter_index][$tmp_post_id] = array('comments' => $statuses_array, 'trackbacks' => $statuses_array, 'pingbacks' => $statuses_array, 'feedbacks' => $statuses_array);
}
$countall_SQL = $count_SQL;
$countall_SQL->WHERE_and('comment_post_ID IN (' . $postIDlist . ')');
foreach ($DB->get_results($countall_SQL->get()) as $row) {
// detail by status, tyep and post:
$cache_ctp_number[$filter_index][$row->comment_post_ID][$row->comment_type . 's'][$row->comment_status] = $row->type_count;
// Total for type on post:
$cache_ctp_number[$filter_index][$row->comment_post_ID][$row->comment_type . 's']['total'] += $row->type_count;
// Total for status on post:
$cache_ctp_number[$filter_index][$row->comment_post_ID]['feedbacks'][$row->comment_status] += $row->type_count;
// Total for post:
$cache_ctp_number[$filter_index][$row->comment_post_ID]['feedbacks']['total'] += $row->type_count;
}
}
}
/* else
{
echo "cache set";
}*/
if (!isset($cache_ctp_number[$filter_index][$post_id])) {
// this should be extremely rare...
// echo "CACHE not set for $post_id";
// Initializes post to nocount!
$cache_ctp_number[$filter_index][intval($post_id)] = array('comments' => $statuses_array, 'trackbacks' => $statuses_array, 'pingbacks' => $statuses_array, 'feedbacks' => $statuses_array);
$count_SQL->WHERE_and('comment_post_ID = ' . intval($post_id));
foreach ($DB->get_results($count_SQL->get()) as $row) {
// detail by status, type and post:
$cache_ctp_number[$filter_index][$row->comment_post_ID][$row->comment_type . 's'][$row->comment_status] = $row->type_count;
// Total for type on post:
$cache_ctp_number[$filter_index][$row->comment_post_ID][$row->comment_type . 's']['total'] += $row->type_count;
// Total for status on post:
$cache_ctp_number[$filter_index][$row->comment_post_ID]['feedbacks'][$row->comment_status] += $row->type_count;
// Total for post:
$cache_ctp_number[$filter_index][$row->comment_post_ID]['feedbacks']['total'] += $row->type_count;
}
}
if ($mode != 'comments' && $mode != 'trackbacks' && $mode != 'pingbacks') {
$mode = 'feedbacks';
}
if (is_array($status)) {
// $status is an array and probably contains more then one visibility status
$result = 0;
foreach ($status as $one_status) {
if (isset($cache_ctp_number[$filter_index][$post_id][$mode][$one_status])) {
$result = $result + $cache_ctp_number[$filter_index][$post_id][$mode][$one_status];
}
}
} elseif (isset($cache_ctp_number[$filter_index][$post_id][$mode][$status])) {
// $status is a string with one visibility status
$result = $cache_ctp_number[$filter_index][$post_id][$mode][$status];
} else {
// $status is not recognized return total feedback number
$result = $cache_ctp_number[$filter_index][$post_id][$mode]['total'];
}
// pre_dump( $cache_ctp_number[$filter_index][$post_id] );
return $result;
}
/**
* Restrict to the visibility/sharing statuses we want to show
*
* @param array Restrict to these statuses
*/
function where_visibility($show_statuses)
{
$this->show_statuses = $show_statuses;
if (!isset($this->blog)) {
debug_die('Status restriction requires to work with aspecific blog first.');
}
$this->WHERE_and(statuses_where_clause($show_statuses, $this->dbprefix, $this->blog));
}
/**
* Get comments awaiting moderation number
*
* @param integer blog ID
* @return integer
*/
function get_comments_awaiting_moderation_number($blog_ID)
{
global $DB;
$BlogCache =& get_BlogCache();
$Blog =& $BlogCache->get_by_ID($blog_ID, false, false);
$moderation_statuses = $Blog->get_setting('moderation_statuses');
$moderation_statuses_condition = '\'' . str_replace(',', '\',\'', $moderation_statuses) . '\'';
$sql = 'SELECT COUNT(DISTINCT(comment_ID))
FROM T_comments
INNER JOIN T_items__item ON comment_post_ID = post_ID ';
$sql .= 'INNER JOIN T_postcats ON post_ID = postcat_post_ID
INNER JOIN T_categories othercats ON postcat_cat_ID = othercats.cat_ID ';
$sql .= 'WHERE ' . $Blog->get_sql_where_aggregate_coll_IDs('othercats.cat_blog_ID');
$sql .= ' AND comment_type IN (\'comment\',\'trackback\',\'pingback\') ';
$sql .= ' AND comment_status IN ( ' . $moderation_statuses_condition . ' )';
$sql .= ' AND ' . statuses_where_clause();
return $DB->get_var($sql);
}
/**
* Get # of comments for each category in a blog
*
* @param integer Category ID
* @param integer Blog ID
*/
function get_commentcount_in_category($cat_ID, $blog_ID = NULL)
{
if (is_null($blog_ID)) {
global $blog;
$blog_ID = $blog;
}
global $DB, $number_of_comments_in_cat;
if (!isset($number_of_comments_in_cat[(string) $blog_ID])) {
$SQL = new SQL();
$SQL->SELECT('cat_ID, COUNT( comment_ID ) c');
$SQL->FROM('T_comments');
$SQL->FROM_add('LEFT JOIN T_postcats ON comment_post_ID = postcat_post_ID');
$SQL->FROM_add('LEFT JOIN T_categories ON postcat_cat_ID = cat_id');
$SQL->FROM_add('LEFT JOIN T_items__item ON comment_post_ID = post_id');
$SQL->WHERE('cat_blog_ID = ' . $DB->quote($blog_ID));
$SQL->WHERE_and(statuses_where_clause(get_inskin_statuses(), 'comment_', $blog_ID, 'blog_comment!', true));
// add where condition to show only those posts commetns which are visible for the current User
$SQL->WHERE_and(statuses_where_clause(get_inskin_statuses(), 'post_', $blog_ID, 'blog_post!', true));
$SQL->GROUP_BY('cat_ID');
$number_of_comments_in_cat[(string) $blog_ID] = $DB->get_assoc($SQL->get());
}
return isset($number_of_comments_in_cat[(string) $blog_ID][$cat_ID]) ? (int) $number_of_comments_in_cat[(string) $blog_ID][$cat_ID] : 0;
}
