public static function get($paramarray = array())
{
$params = array();
$fns = array('get_results', 'get_row', 'get_value');
$select = '';
// what to select -- by default, everything
foreach (Comment::default_fields() as $field => $value) {
$select .= '' == $select ? "{comments}.{$field} as {$field}" : ", {comments}.{$field} as {$field}";
}
// defaults
$orderby = 'date DESC';
$limit = Options::get('pagination');
// Put incoming parameters into the local scope
$paramarray = Utils::get_params($paramarray);
// let plugins alter the param array before we use it. could be useful for modifying search results, etc.
$paramarray = Plugins::filter('comments_get_paramarray', $paramarray);
$join_params = array();
// Transact on possible multiple sets of where information that is to be OR'ed
if (isset($paramarray['where']) && is_array($paramarray['where'])) {
$wheresets = $paramarray['where'];
} else {
$wheresets = array(array());
}
$wheres = array();
$joins = array();
if (isset($paramarray['where']) && is_string($paramarray['where'])) {
$wheres[] = $paramarray['where'];
} else {
foreach ($wheresets as $paramset) {
// safety mechanism to prevent empty queries
$where = array('1=1');
$paramset = array_merge((array) $paramarray, (array) $paramset);
if (isset($paramset['id']) && (is_numeric($paramset['id']) || is_array($paramset['id']))) {
if (is_numeric($paramset['id'])) {
$where[] = "{comments}.id= ?";
$params[] = $paramset['id'];
} else {
if (is_array($paramset['id']) && !empty($paramset['id'])) {
$id_list = implode(',', $paramset['id']);
// Clean up the id list - remove all non-numeric or comma information
$id_list = preg_replace("/[^0-9,]/", "", $id_list);
// You're paranoid, ringmaster! :P
$limit = count($paramset['id']);
$where[] = '{comments}.id IN (' . addslashes($id_list) . ')';
}
}
}
if (isset($paramset['status']) && false !== $paramset['status']) {
if (is_array($paramset['status'])) {
$paramset['status'] = array_diff($paramset['status'], array('any'));
array_walk($paramset['status'], function (&$a) {
$a = Comment::status($a);
});
$where[] = "{comments}.status IN (" . Utils::placeholder_string(count($paramset['status'])) . ")";
$params = array_merge($params, $paramset['status']);
} else {
$where[] = "{comments}.status= ?";
$params[] = Comment::status($paramset['status']);
}
}
if (isset($paramset['type']) && false !== $paramset['type']) {
if (is_array($paramset['type'])) {
$paramset['type'] = array_diff($paramset['type'], array('any'));
array_walk($paramset['type'], function (&$a) {
$a = Comment::type($a);
});
$where[] = "type IN (" . Utils::placeholder_string(count($paramset['type'])) . ")";
$params = array_merge($params, $paramset['type']);
} else {
$where[] = "type= ?";
$params[] = Comment::type($paramset['type']);
}
}
if (isset($paramset['name'])) {
$where[] = "LOWER( name ) = ?";
$params[] = MultiByte::strtolower($paramset['name']);
}
if (isset($paramset['email'])) {
$where[] = "LOWER( email ) = ?";
$params[] = MultiByte::strtolower($paramset['email']);
}
if (isset($paramset['url'])) {
$where[] = "LOWER( url ) = ?";
$params[] = MultiByte::strtolower($paramset['url']);
}
if (isset($paramset['post_id'])) {
$where[] = "{comments}.post_id= ?";
$params[] = $paramset['post_id'];
}
if (isset($paramset['ip'])) {
$where[] = "ip= ?";
$params[] = $paramset['ip'];
}
/* do searching */
if (isset($paramset['post_author'])) {
$joins['posts'] = ' INNER JOIN {posts} ON {comments}.post_id = {posts}.id';
if (is_array($paramset['post_author'])) {
$where[] = "{posts}.user_id IN (" . implode(',', array_fill(0, count($paramset['post_author']), '?')) . ")";
$params = array_merge($params, $paramset['post_author']);
} else {
$where[] = '{posts}.user_id = ?';
$params[] = (string) $paramset['post_author'];
}
}
if (isset($paramset['criteria'])) {
if (isset($paramset['criteria_fields'])) {
// Support 'criteria_fields' => 'author,ip' rather than 'criteria_fields' => array( 'author', 'ip' )
if (!is_array($paramset['criteria_fields']) && is_string($paramset['criteria_fields'])) {
$paramset['criteria_fields'] = explode(',', $paramset['criteria_fields']);
}
} else {
$paramset['criteria_fields'] = array('content');
}
$paramset['criteria_fields'] = array_unique($paramset['criteria_fields']);
preg_match_all('/(?<=")([\\p{L}\\p{N}]+[^"]*)(?=")|([\\p{L}\\p{N}]+)/u', $paramset['criteria'], $matches);
$where_search = array();
foreach ($matches[0] as $word) {
foreach ($paramset['criteria_fields'] as $criteria_field) {
$where_search[] .= "( LOWER( {comments}.{$criteria_field} ) LIKE ? )";
$params[] = '%' . MultiByte::strtolower($word) . '%';
}
}
if (count($where_search) > 0) {
$where[] = '(' . implode(" \nOR\n ", $where_search) . ')';
}
}
/*
* Build the pubdate
* If we've got the day, then get the date.
* If we've got the month, but no date, get the month.
* If we've only got the year, get the whole year.
* @todo Ensure that we've actually got all the needed parts when we query on them
* @todo Ensure that the value passed in is valid to insert into a SQL date (ie '04' and not '4')
*/
if (isset($paramset['day'])) {
/* Got the full date */
$where[] = 'date BETWEEN ? AND ?';
$start_date = sprintf('%d-%02d-%02d', $paramset['year'], $paramset['month'], $paramset['day']);
$start_date = DateTime::create($start_date);
$params[] = $start_date->sql;
$params[] = $start_date->modify('+1 day')->sql;
} elseif (isset($paramset['month'])) {
$where[] = 'date BETWEEN ? AND ?';
$start_date = sprintf('%d-%02d-%02d', $paramset['year'], $paramset['month'], 1);
$start_date = DateTime::create($start_date);
$params[] = $start_date->sql;
$params[] = $start_date->modify('+1 month')->sql;
} elseif (isset($paramset['year'])) {
$where[] = 'date BETWEEN ? AND ?';
$start_date = sprintf('%d-%02d-%02d', $paramset['year'], 1, 1);
$start_date = DateTime::create($start_date);
$params[] = $start_date->sql;
$params[] = $start_date->modify('+1 year')->sql;
}
// Concatenate the WHERE clauses
if (count($where) > 0) {
$wheres[] = ' (' . implode(' AND ', $where) . ') ';
}
}
}
// Only show comments to which the current user has permission to read the associated post
if (isset($paramset['ignore_permissions'])) {
$master_perm_where = '';
// Set up the merge params
$merge_params = array($join_params, $params);
$params = call_user_func_array('array_merge', $merge_params);
} else {
// This set of wheres will be used to generate a list of comment_ids that this user can read
$perm_where = array();
$perm_where_denied = array();
$params_where = array();
$where = array();
// every condition here will require a join with the posts table
$joins['posts'] = 'INNER JOIN {posts} ON {comments}.post_id={posts}.id';
// Get the tokens that this user is granted or denied access to read
$read_tokens = isset($paramset['read_tokens']) ? $paramset['read_tokens'] : ACL::user_tokens(User::identify(), 'read', true);
$deny_tokens = isset($paramset['deny_tokens']) ? $paramset['deny_tokens'] : ACL::user_tokens(User::identify(), 'deny', true);
// If a user can read his own posts, let him
if (User::identify()->can('own_posts', 'read')) {
$perm_where['own_posts_id'] = '{posts}.user_id = ?';
$params_where[] = User::identify()->id;
}
// If a user can read any post type, let him
if (User::identify()->can('post_any', 'read')) {
$perm_where = array('post_any' => '(1=1)');
$params_where = array();
} else {
// If a user can read specific post types, let him
$permitted_post_types = array();
foreach (Post::list_active_post_types() as $name => $posttype) {
if (User::identify()->can('post_' . Utils::slugify($name), 'read')) {
$permitted_post_types[] = $posttype;
}
}
if (count($permitted_post_types) > 0) {
$perm_where[] = '{posts}.content_type IN (' . implode(',', $permitted_post_types) . ')';
}
// If a user can read posts with specific tokens, let him see comments on those posts
if (count($read_tokens) > 0) {
$joins['post_tokens__allowed'] = ' LEFT JOIN {post_tokens} pt_allowed ON {posts}.id= pt_allowed.post_id AND pt_allowed.token_id IN (' . implode(',', $read_tokens) . ')';
$perm_where['perms_join_null'] = 'pt_allowed.post_id IS NOT NULL';
}
}
// If a user is denied access to all posts, do so
if (User::identify()->cannot('post_any')) {
$perm_where_denied = array('(0=1)');
} else {
// If a user is denied read access to specific post types, deny him
$denied_post_types = array();
foreach (Post::list_active_post_types() as $name => $posttype) {
if (User::identify()->cannot('post_' . Utils::slugify($name))) {
$denied_post_types[] = $posttype;
}
}
if (count($denied_post_types) > 0) {
$perm_where_denied[] = '{posts}.content_type NOT IN (' . implode(',', $denied_post_types) . ')';
}
}
// If there are granted permissions to check, add them to the where clause
if (count($perm_where) == 0 && !isset($joins['post_tokens__allowed'])) {
// You have no grants. You get no comments.
$where['perms_granted'] = '(0=1)';
} elseif (count($perm_where) > 0) {
$where['perms_granted'] = '
(' . implode(' OR ', $perm_where) . ')
';
$params = array_merge($params, $params_where);
}
if (count($deny_tokens) > 0) {
$joins['post_tokens__denied'] = ' LEFT JOIN {post_tokens} pt_denied ON {posts}.id= pt_denied.post_id AND pt_denied.token_id IN (' . implode(',', $deny_tokens) . ')';
$perm_where_denied['perms_join_null'] = 'pt_denied.post_id IS NULL';
}
// If there are denied permissions to check, add them to the where clause
if (count($perm_where_denied) > 0) {
$where['perms_denied'] = '
(' . implode(' AND ', $perm_where_denied) . ')
';
}
$master_perm_where = implode(' AND ', $where);
}
// Get any full-query parameters
$possible = array('page', 'fetch_fn', 'count', 'month_cts', 'nolimit', 'limit', 'offset', 'orderby');
foreach ($possible as $varname) {
if (isset($paramarray[$varname])) {
${$varname} = $paramarray[$varname];
}
}
if (isset($page) && is_numeric($page)) {
$offset = (intval($page) - 1) * intval($limit);
}
if (isset($fetch_fn)) {
if (!in_array($fetch_fn, $fns)) {
$fetch_fn = $fns[0];
}
} else {
$fetch_fn = $fns[0];
}
// is a count being request?
if (isset($count)) {
$select = "COUNT( 1 )";
$fetch_fn = 'get_value';
$orderby = '';
}
// is a count of comments by month being requested?
$groupby = '';
if (isset($month_cts)) {
$select = 'MONTH(FROM_UNIXTIME(date)) AS month, YEAR(FROM_UNIXTIME(date)) AS year, COUNT({comments}.id) AS ct';
$groupby = 'year, month';
$orderby = 'year, month';
}
if (isset($limit)) {
$limit = " LIMIT {$limit}";
if (isset($offset)) {
$limit .= " OFFSET {$offset}";
}
}
if (isset($nolimit) || isset($month_cts)) {
$limit = '';
}
// Build the final SQL statement
$query = '
SELECT DISTINCT ' . $select . ' FROM {comments} ' . implode(' ', $joins);
if (count($wheres) > 0) {
$query .= ' WHERE (' . implode(" \nOR\n ", $wheres) . ')';
$query .= $master_perm_where == '' ? '' : ' AND (' . $master_perm_where . ')';
} elseif ($master_perm_where != '') {
$query .= ' WHERE (' . $master_perm_where . ')';
}
$query .= $groupby == '' ? '' : ' GROUP BY ' . $groupby;
$query .= ($orderby == '' ? '' : ' ORDER BY ' . $orderby) . $limit;
DB::set_fetch_mode(\PDO::FETCH_CLASS);
DB::set_fetch_class('Comment');
$results = DB::$fetch_fn($query, $params, 'Comment');
if ('get_results' != $fetch_fn) {
// return the results
return $results;
} elseif (is_array($results)) {
$c = __CLASS__;
$return_value = new $c($results);
$return_value->get_param_cache = $paramarray;
return $return_value;
}
return false;
}
