<?php
/**
* This file implements the CommentQuery class.
*
* This file is part of the b2evolution/evocms project - {@link http://b2evolution.net/}.
* See also {@link https://github.com/b2evolution/b2evolution}.
*
* @license GNU GPL v2 - {@link http://b2evolution.net/about/gnu-gpl-license}
*
* @copyright (c)2003-2020 by Francois Planque - {@link http://fplanque.com/}.
*
* @license http://b2evolution.net/about/license.html GNU General Public License (GPL)
*
* @package evocore
*/
if( !defined('EVO_MAIN_INIT') ) die( 'Please, do not access this page directly.' );
load_class( '_core/model/db/_sql.class.php', 'SQL' );
/**
* CommentQuery: help constructing queries on Comments
* @package evocore
*/
class CommentQuery extends SQL
{
var $c;
var $cl;
var $post;
var $author;
var $author_login;
var $author_email;
var $author_url;
var $url_match;
var $include_emptyurl;
var $author_IP;
var $rating_toshow;
var $rating_turn;
var $rating_limit;
var $show_statuses;
var $types;
var $keywords;
var $phrase;
var $exact;
var $Blog;
/**
* Constructor.
*
* @param string Name of table in database
* @param string Prefix of fields in the table
* @param string Name of the ID field (including prefix)
*/
function __construct( $dbtablename = 'T_comments', $dbprefix = 'comment_', $dbIDname = 'comment_ID' )
{
$this->dbtablename = $dbtablename;
$this->dbprefix = $dbprefix;
$this->dbIDname = $dbIDname;
$this->FROM( $this->dbtablename );
}
/**
* Restrict to a specific comment
*/
function where_ID( $c = '', $author = '' )
{
$r = false;
$this->c = $c;
if( empty( $this->author ) )
{ // Change $this->author only if it is empty, because possible that it was set previously
$this->author = $author;
}
// if a comment number is specified, load that comment
if( !empty($c) )
{
$this->WHERE_and( $this->dbIDname.' = '. intval($c) );
$r = true;
}
// if a comment author is specified, load that comment
if( !empty( $author ) )
{
global $DB;
$this->WHERE_and( $this->dbprefix.'author = '.$DB->quote($author) );
$r = true;
}
return $r;
}
/**
* Restrict to a specific list of comments
*/
function where_ID_list( $cl = '' )
{
$this->cl = clear_ids_list( $cl );
if( empty( $this->cl ) )
{ // Nothing to filter:
return;
}
if( substr( $cl, 0, 1 ) == '-' )
{ // List starts with MINUS sign:
$eq = 'NOT IN';
}
else
{
$eq = 'IN';
}
$this->WHERE_and( $this->dbIDname.' '.$eq.'( '.$this->cl.' )' );
}
/**
* Restrict to a specific post comments
*/
function where_post_ID( $post )
{
$this->post = $post;
if( empty( $post ) )
{
return;
}
if( substr( $post, 0, 1 ) == '-' )
{ // List starts with MINUS sign:
$eq = 'NOT IN';
$post_list = substr( $post, 1 );
}
else
{
$eq = 'IN';
$post_list = $post;
}
// Validate post ID list
$post_ids = array();
$post_list = explode( ',', $post_list );
foreach( $post_list as $p_id )
{
$post_ids[] = intval( $p_id );// make sure they're all numbers
}
$this->post = implode( ',', $post_ids );
$this->WHERE_and( $this->dbprefix.'item_ID '.$eq.' ('.$this->post.')' );
}
/**
* Restrict to a specific comment date
*/
function where_comment_date( $timestamp_min, $timestamp_max )
{
global $time_difference, $DB;
if( empty( $timestamp_min ) && empty ( $timestamp_max ) )
{ // Don't restrict
return;
}
if( $timestamp_min )
{
$date_min = date( 'Y-m-d H:i:s', $timestamp_min + $time_difference );
$this->WHERE_and( $this->dbprefix.'date >= '.$DB->quote( $date_min ) );
}
if( $timestamp_max )
{
$date_max = date( 'Y-m-d H:i:s', $timestamp_max + $time_difference );
$this->WHERE_and( $this->dbprefix.'date <= '.$DB->quote( $date_max ) );
}
}
/**
* Restrict to a specific post comments by post_datestart
*
* @param timestamp min - Do not show comments from posts before this timestamp
* @param timestamp max - Do not show comments from posts after this timestamp
*/
function where_post_datestart( $timestamp_min, $timestamp_max )
{
if( empty( $timestamp_min ) && empty ( $timestamp_max ) )
{ // Don't restrict
return;
}
$dbtable = 'T_items__item';
$dbprefix = 'post_';
$dbIDname = 'ID';
$ItemQuery = new ItemQuery( $dbtable, $dbprefix, $dbIDname );
$ItemQuery->where_datestart( '', '', '', '', $timestamp_min, $timestamp_max );
$this->WHERE_and( $ItemQuery->get_where( '' ) );
}
/**
* Restrict to specific authors
*
* @param string List of authors (author IDs) to restrict to (must have been previously validated)
*/
function where_author( $author )
{
$this->author = clear_ids_list( $author );
if( empty( $this->author ) )
{
return;
}
if( substr( $author, 0, 1 ) == '-' )
{ // List starts with MINUS sign:
$eq = 'NOT IN';
}
else
{
$eq = 'IN';
}
$this->WHERE_and( $this->dbprefix.'author_user_ID '.$eq.' ('.$this->author.')' );
}
/**
* Restrict to specific authors by users logins
*
* @param string List of authors logins to restrict to (must have been previously validated)
*/
function where_author_logins( $author_logins )
{
$this->author_login = $author_logins;
if( empty( $this->author_login ) )
{
return;
}
if( substr( $this->author_login, 0, 1 ) == '-' )
{ // Exclude the users IF a list starts with MINUS sign:
$eq = 'NOT IN';
$users_IDs = get_users_IDs_by_logins( substr( $this->author_login, 1 ) );
}
else
{ // Include the users:
$eq = 'IN';
$users_IDs = get_users_IDs_by_logins( $this->author_login );
if( empty( $users_IDs ) )
{ // If users are not found with login, we should not allow selection with all users:
$users_IDs = '-1';
}
}
if( ! empty( $users_IDs ) )
{
$this->WHERE_and( $this->dbprefix.'author_user_ID '.$eq.' ( '.$users_IDs.' )' );
}
}
/**
* Restrict to specific authors email
*
* @param string List of authors email to restrict to (must have been previously validated)
*/
function where_author_email( $author_email )
{
global $DB;
$this->author_email = $author_email;
if( empty( $author_email ) )
{
return;
}
if( substr( $author_email, 0, 1 ) == '-' )
{ // List starts with MINUS sign:
$eq = 'NOT IN';
$author_email_list = explode( ',', substr( $author_email, 1 ) );
}
else
{
$eq = 'IN';
$author_email_list = explode( ',', $author_email );
}
$this->WHERE_and( $this->dbprefix.'author_email '.$eq.' ( '.$DB->quote( $author_email_list ).' )' );
}
/**
* Restrict to specific author url
*
* @param string authors url to restrict to
* @param string equal or not equal restriction
* @param boolean include or not comments, with no url
*/
function where_author_url( $author_url, $url_match, $include_emptyurl )
{
global $DB;
$this->author_url = $author_url;
$this->url_match = $url_match;
$this->include_emptyurl = $include_emptyurl;
if( empty( $author_url ) )
{
return;
}
if( empty( $url_match) )
{
$url_match = "IN";
}
elseif( $url_match == '=' )
{
$author_url = '%'.$author_url.'%';
$url_match = 'LIKE';
}
elseif( $url_match == '!=' )
{
$author_url = '%'.$author_url.'%';
$url_match = 'NOT LIKE';
}
$include_empty = '';
if( $include_emptyurl )
{ // include comments with no url
$include_empty = ' OR '.$this->dbprefix.'author_url IS NULL';
}
$this->WHERE_and( $this->dbprefix.'author_url '.$url_match.' ('.$DB->quote( $author_url ).')'.$include_empty );
}
/**
* Restrict to specific author IPs
*
* @param string List of authors IPs to restrict to (must have been previously validated)
*/
function where_author_IP( $author_IP )
{
$this->author_IP = $author_IP;
if( empty( $author_IP ) )
{
return;
}
global $DB;
$this->WHERE_and( $this->dbprefix.'author_IP LIKE '.$DB->quote( $author_IP ) );
}
/**
* Restrict to specific rating or rating interval
*
* @param array show none rated comments or show comments with specific rating values or none/both
* @param string search above, below or exact rating values
* @param integer rating value - limit
*/
function where_rating( $rating_toshow, $rating_turn, $rating_limit )
{
$this->rating_toshow = $rating_toshow;
$this->rating_turn = $rating_turn;
$this->rating_limit = $rating_limit;
if( empty( $rating_toshow) )
{ // no nead where condition for ratings
return;
}
$search = '';
if( in_array( 'norating', $rating_toshow ) )
{ // include comments with no rating
$search = $this->dbprefix.'rating IS NULL';
}
if( in_array( 'haverating', $rating_toshow ) )
{ // specify search direction
switch( $rating_turn )
{
case 'exact':
$comp = '=';
break;
case 'above':
$comp = '>=';
break;
case 'below':
$comp = '<=';
break;
default:
debug_die( 'Unknown search direction!' );
}
if( $search != '' )
{
$search = ' '.$search.' OR ';
$where_end = ' )';
}
$search = $search.$this->dbprefix.'rating '.$comp.' '.$rating_limit;
}
$this->WHERE_and( $search );
}
/**
* 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.')' );
}
}
/**
* Restrict to specific post types
*
* @param string List of types to restrict to (must have been previously validated)
*/
function where_types( $types )
{
global $DB;
$this->types = $types;
if( empty( $types ) )
{
return;
}
$list = '';
$sep = '';
foreach( $types as $type )
{
$list .= $sep.$DB->quote( $type );
$sep = ',';
}
$this->WHERE_and( $this->dbprefix.'type IN ('.$list.')' );
}
/**
* Restrict with keywords
*
* @param string Keyword search string
* @param mixed Search for entire phrase or for individual words
* @param mixed Require exact match of author or contents
*/
function where_keywords( $keywords, $phrase, $exact )
{
global $DB;
$this->keywords = $keywords;
$this->phrase = $phrase;
$this->exact = $exact;
if( empty($keywords) )
{
return;
}
$search = '';
if( $exact )
{ // We want exact match of author or contents
$n = '';
}
else
{ // The words/sentence are/is to be included in in the author or the contents
$n = '%';
}
if( ($phrase == '1') or ($phrase == 'sentence') )
{ // Sentence search
$keywords = $DB->escape(trim($keywords));
$search .= '('.$this->dbprefix.'author LIKE \''. $n. $keywords. $n. '\') OR ('.$this->dbprefix.'content LIKE \''. $n. $keywords. $n.'\')';
}
else
{ // Word search
if( strtoupper( $phrase ) == 'OR' )
$swords = 'OR';
else
$swords = 'AND';
// puts spaces instead of commas
$keywords = preg_replace('/, +/', '', $keywords);
$keywords = str_replace(',', ' ', $keywords);
$keywords = str_replace('"', ' ', $keywords);
$keywords = trim($keywords);
$keyword_array = explode(' ',$keywords);
$join = '';
for ( $i = 0; $i < count($keyword_array); $i++)
{
$search .= ' '. $join. ' ( ('.$this->dbprefix.'author LIKE \''. $n. $DB->escape($keyword_array[$i]). $n. '\')
OR ('.$this->dbprefix.'content LIKE \''. $n. $DB->escape($keyword_array[$i]). $n.'\') ) ';
$join = $swords;
}
}
//echo $search;
$this->WHERE_and( $search );
}
/**
* Restrict to specific blog
*
* @param integer blog to restrict to
*/
function blog_restrict( $Blog )
{
$this->Blog = $Blog;
if( empty($Blog) )
{
return;
}
$this->FROM_add( 'INNER JOIN T_postcats ON '.$this->dbprefix.'item_ID = postcat_post_ID
INNER JOIN T_categories othercats ON postcat_cat_ID = othercats.cat_ID ' );
$this->WHERE_and( $Blog->get_sql_where_aggregate_coll_IDs('othercats.cat_blog_ID') );
}
/**
* Restrict to show or hide active/expired comments ( ecpired comments are older then the post expiry delay value )
* By default only active comments will be allowed
*
* @param array expiry statuses to show
*/
function expiry_restrict( $expiry_statuses )
{
global $localtimenow, $DB;
$show_active = empty( $expiry_statuses ) || in_array( 'active', $expiry_statuses );
$show_expired = !empty( $expiry_statuses ) && in_array( 'expired', $expiry_statuses );
if( !$show_expired )
{
$this->FROM_add( 'LEFT JOIN T_items__item_settings as expiry_setting ON iset_item_ID = comment_item_ID AND iset_name = "comment_expiry_delay"' );
$this->WHERE_and( 'expiry_setting.iset_value IS NULL OR expiry_setting.iset_value = "" OR TIMESTAMPDIFF(SECOND, comment_date, '.$DB->quote( date2mysql( $localtimenow ) ).') < expiry_setting.iset_value' );
}
elseif( !$show_active )
{
$this->FROM_add( 'LEFT JOIN T_items__item_settings as expiry_setting ON iset_item_ID = comment_item_ID AND iset_name = "comment_expiry_delay"' );
$this->WHERE_and( 'expiry_setting.iset_value IS NOT NULL AND expiry_setting.iset_value <> "" AND TIMESTAMPDIFF(SECOND, comment_date, '.$DB->quote( date2mysql( $localtimenow ) ).') >= expiry_setting.iset_value' );
}
}
/**
* Restrict to show only those comments where user has some specific permission
*
* @param string the required permission to check
* @param integer the blog ID
*/
function user_perm_restrict( $user_perm, $blog_ID )
{
global $current_User, $DB;
if( !is_logged_in( false ) )
{ // Anonymous users can see only published comments, no need further restriction
return;
}
if( ( $user_perm !== 'moderate' ) && ( $user_perm !== 'edit' ) )
{ // No need furhter restriciton because the user doesn't want to edit/moderate these comments
return;
}
if( check_user_perm( 'blogs', 'editall' ) )
{ // User has global permission one ach blog
return;
}
$BlogCache = & get_BlogCache();
$Collection = $Blog = $BlogCache->get_by_ID( $blog_ID );
if( $current_User->ID == $Blog->get( 'owner_user_ID' ) )
{ // User is the blog owner, so has permission on edit/moderate each comment
return;
}
if( ! $Blog->get( 'advanced_perms' ) )
{ // Blog advanced perm settings is not enabled, user has no permission to edit/moderate comments
$this->WHERE_and( 'FALSE' );
return;
}
$SQL = new SQL( 'Get comment edit permissions for current User' );
$SQL->SELECT( 'IF( IFNULL( bloguser_perm_edit_cmt + 0, 0 ) > IFNULL( bloggroup_perm_edit_cmt + 0, 0 ), bloguser_perm_edit_cmt, bloggroup_perm_edit_cmt ) as perm_edit_cmt' );
$SQL->FROM( 'T_blogs' );
$SQL->FROM_add( 'LEFT JOIN T_coll_user_perms ON bloguser_blog_ID = blog_ID AND bloguser_user_ID = '.$current_User->ID );
$SQL->FROM_add( 'LEFT JOIN T_coll_group_perms ON bloggroup_blog_ID = blog_ID
AND ( bloggroup_group_ID = '.$current_User->grp_ID.'
OR bloggroup_group_ID IN ( SELECT sug_grp_ID FROM T_users__secondary_user_groups WHERE sug_user_ID = '.$current_User->ID.' ) )' );
$SQL->WHERE( 'blog_ID = '.$blog_ID );
$perm_edit_cmt = $DB->get_var( $SQL );
$user_level = $current_User->level;
$condition = '';
switch( $perm_edit_cmt )
{
case 'le':
case 'lt':
$operator = ( $perm_edit_cmt == 'le' ) ? '<=' : '<';
$condition = '( comment_author_user_ID IN ( SELECT user_ID FROM T_users WHERE user_level '.$operator.' '.$current_User->level.' ) )';
$condition .= ' OR ';
case 'anon':
$condition .= 'comment_author_user_ID IS NULL';
break;
case 'own':
$condition = 'comment_author_user_ID = '.$current_User->ID;
break;
case 'no':
$condtion = 'FALSE';
break;
case 'all': // In this case we don't add any specific permission check because everything is permitted
break;
default:
debug_die('This value of edit permission is not implemented!');
}
$this->WHERE_and( $condition );
}
/**
* Restrict by min and max dates
*
* @param string Date in format YYYYMMDDHHMMSS to start at
* @param string Date in format YYYYMMDDHHMMSS to stop at
*/
function where_dates( $ymdhms_min, $ymdhms_max )
{
global $DB;
$this->ymdhms_min = $ymdhms_min;
$this->ymdhms_max = $ymdhms_max;
if( ! empty( $this->ymdhms_min ) )
{ // Restrict by min date:
$dstart0 = $this->ymdhms_min.substr( '00000101000000', strlen( $this->ymdhms_min ) );
// Start date in MySQL format: seconds get omitted (rounded to lower to minute for caching purposes):
$dstart_mysql = substr( $dstart0, 0, 4 ).'-'.substr( $dstart0, 4, 2 ).'-'.substr( $dstart0, 6, 2 ).' '
.substr( $dstart0, 8, 2 ).':'.substr( $dstart0, 10, 2 );
$this->WHERE_and( $this->dbprefix.'date >= '.$DB->quote( $dstart_mysql ) );
}
if( ! empty( $this->ymdhms_max ) )
{ // Restrict by max date:
$dstart0 = $this->ymdhms_max.substr( '00001231235959', strlen( $this->ymdhms_max ) );
// Start date in MySQL format: seconds get omitted (rounded to lower to minute for caching purposes):
$dstart_mysql = substr( $dstart0, 0, 4 ).'-'.substr( $dstart0, 4, 2 ).'-'.substr( $dstart0, 6, 2 ).' '
.substr( $dstart0, 8, 2 ).':'.substr( $dstart0, 10, 2 );
$this->WHERE_and( $this->dbprefix.'date <= '.$DB->quote( $dstart_mysql ) );
}
}
}
?>