/**
* Generate search query based on set filters and obtain count of results
*/
function query_init()
{
global $current_User;
// Call reset to init the ItemQuery
// This prevents from adding the same conditions twice if the ItemQuery was already initialized
$this->reset();
if (empty($this->filters)) {
// Filters have not been set before, we'll use the default filterset:
// If there is a preset filter, we need to activate its specific defaults:
$this->filters['filter_preset'] = param($this->param_prefix . 'filter_preset', 'string', $this->default_filters['filter_preset'], true);
$this->activate_preset_filters();
// Use the default filters:
$this->set_filters($this->default_filters);
}
// echo '<br />ItemListLight query';
//pre_dump( $this->filters );
// GENERATE THE QUERY:
/*
* Filtering stuff:
*/
if (!is_null($this->Blog)) {
// Get the posts only for current Blog
$this->ItemQuery->where_chapter2($this->Blog, $this->filters['cat_array'], $this->filters['cat_modifier'], $this->filters['cat_focus'], $this->filters['coll_IDs']);
} else {
// If we want to get the posts from all blogs
// Save for future use (permission checks..)
$this->ItemQuery->blog = 0;
$this->ItemQuery->Blog = $this->Blog;
}
$this->ItemQuery->where_tags($this->filters['tags']);
$this->ItemQuery->where_author($this->filters['authors']);
$this->ItemQuery->where_author_logins($this->filters['authors_login']);
$this->ItemQuery->where_assignees($this->filters['assignees']);
$this->ItemQuery->where_assignees_logins($this->filters['assignees_login']);
$this->ItemQuery->where_author_assignee($this->filters['author_assignee']);
$this->ItemQuery->where_locale($this->filters['lc']);
$this->ItemQuery->where_statuses($this->filters['statuses']);
$this->ItemQuery->where_types($this->filters['types']);
$this->ItemQuery->where_keywords($this->filters['keywords'], $this->filters['phrase'], $this->filters['exact'], $this->filters['keyword_scope']);
$this->ItemQuery->where_ID($this->filters['post_ID'], $this->filters['post_title']);
$this->ItemQuery->where_ID_list($this->filters['post_ID_list']);
$this->ItemQuery->where_datestart($this->filters['ymdhms'], $this->filters['week'], $this->filters['ymdhms_min'], $this->filters['ymdhms_max'], $this->filters['ts_min'], $this->filters['ts_max']);
$this->ItemQuery->where_datecreated($this->filters['ts_created_max']);
$this->ItemQuery->where_visibility($this->filters['visibility_array'], $this->filters['coll_IDs']);
$this->ItemQuery->where_featured($this->filters['featured']);
/*
* ORDER BY stuff:
*/
if ($this->filters['post_ID_list'] && $this->filters['orderby'] == 'ID_list') {
$order_by = 'FIELD(' . $this->Cache->dbIDname . ', ' . $this->filters['post_ID_list'] . ')';
} elseif ($this->filters['orderby'] == 'ID_list') {
// Use blog setting here because 'orderby' might be set to 'ID_list' as default filter
$this->filters['orderby'] = $this->Blog->get_setting('orderby');
}
if (empty($order_by)) {
$available_fields = array_keys(get_available_sort_options());
// Extend general list to allow order posts by these fields as well for some special cases
$available_fields[] = 'creator_user_ID';
$available_fields[] = 'assigned_user_ID';
$available_fields[] = 'pst_ID';
$available_fields[] = 'datedeadline';
$available_fields[] = 'T_categories.cat_name';
$available_fields[] = 'T_categories.cat_order';
$order_by = gen_order_clause($this->filters['orderby'], $this->filters['order'], $this->Cache->dbprefix, $this->Cache->dbIDname, $available_fields);
}
$this->ItemQuery->order_by($order_by);
/*
* GET TOTAL ROW COUNT:
*/
if ($this->single_post) {
// Single post: no paging required!
$this->total_rows = 1;
$this->total_pages = 1;
$this->page = 1;
} elseif ($this->filters['unit'] == 'days' || $this->filters['unit'] == 'all') {
$this->total_rows = NULL;
// unknown!
$this->total_pages = 1;
$this->page = 1;
} elseif ($this->filters['unit'] == 'posts') {
// Calculate a count of the posts
if ($this->ItemQuery->get_group_by() == '') {
// SQL query without GROUP BY clause
$sql_count = 'SELECT COUNT( DISTINCT ' . $this->Cache->dbIDname . ' )' . $this->ItemQuery->get_from() . $this->ItemQuery->get_where() . $this->ItemQuery->get_limit();
} else {
// SQL query with GROUP BY clause, Summarize a count of each grouped result
$sql_count = 'SELECT SUM( cnt_tbl.cnt ) FROM (
SELECT COUNT( DISTINCT ' . $this->Cache->dbIDname . ' ) AS cnt ' . $this->ItemQuery->get_from() . $this->ItemQuery->get_where() . $this->ItemQuery->get_group_by() . $this->ItemQuery->get_limit() . '
) AS cnt_tbl ';
}
parent::count_total_rows($sql_count);
} else {
debug_die('Unhandled LIMITING mode in ItemList:' . $this->filters['unit'] . ' (paged mode is obsolete)');
}
/*
* Paging LIMITs:
*/
if ($this->single_post) {
// Single post: no paging required!
} elseif ($this->filters['unit'] == 'all') {
// We want ALL results!
} elseif ($this->filters['unit'] == 'posts') {
// TODO: dh> check if $limit is NULL!? - though it should not arrive at $page>1 then..
// echo 'LIMIT POSTS ';
$pgstrt = '';
if ($this->page > 1) {
// We have requested a specific page number
$pgstrt = (intval($this->page) - 1) * $this->limit . ', ';
}
$this->ItemQuery->LIMIT($pgstrt . $this->limit);
} elseif ($this->filters['unit'] == 'days') {
// We are going to limit to x days:
// echo 'LIMIT DAYS ';
if (empty($this->filters['ymdhms_min'])) {
// We have no start date, we'll display the last x days:
if (!empty($this->filters['keywords']) || !empty($this->filters['cat_array']) || !empty($this->filters['authors'])) {
// We are in DAYS mode but we can't restrict on these! (TODO: ?)
$limits = '';
} else {
// We are going to limit to LAST x days:
$lastpostdate = $this->get_lastpostdate();
$lastpostdate = mysql2date('Y-m-d 00:00:00', $lastpostdate);
$lastpostdate = mysql2date('U', $lastpostdate);
// go back x days
$otherdate = date('Y-m-d H:i:s', $lastpostdate - ($this->limit - 1) * 86400);
$this->ItemQuery->WHERE_and($this->Cache->dbprefix . 'datestart > \'' . $otherdate . '\'');
}
} else {
// We have a start date, we'll display x days starting from that point:
// $dstart_mysql has been calculated earlier
// TODO: this is redundant with previous dstart processing:
// Add trailing 0s: YYYYMMDDHHMMSS
$dstart0 = $this->filters['ymdhms_min'] . '00000000000000';
$dstart_mysql = substr($dstart0, 0, 4) . '-' . substr($dstart0, 4, 2) . '-' . substr($dstart0, 6, 2) . ' ' . substr($dstart0, 8, 2) . ':' . substr($dstart0, 10, 2) . ':' . substr($dstart0, 12, 2);
$dstart_ts = mysql2timestamp($dstart_mysql);
// go forward x days
$enddate_ts = date('Y-m-d H:i:s', $dstart_ts + $this->limit * 86400);
$this->ItemQuery->WHERE_and($this->Cache->dbprefix . 'datestart < \'' . $enddate_ts . '\'');
}
} else {
debug_die('Unhandled LIMITING mode in ItemList:' . $this->filters['unit'] . ' (paged mode is obsolete)');
}
}
/**
* Run Query: GET DATA ROWS *** HEAVY ***
*/
function query()
{
global $DB, $Session, $localtimenow;
if (!is_null($this->rows)) {
// Query has already executed:
return;
}
// INIT THE QUERY:
$this->query_init();
// We are going to proceed in two steps (we simulate a subquery)
// 1) we get the IDs we need
// 2) we get all the other fields matching these IDs
// This is more efficient than manipulating all fields at once.
// *** STEP 1 ***
$user_IDs = isset($this->filters['users']) ? $this->filters['users'] : array();
if ($this->refresh_query || $localtimenow - $Session->get($this->filterset_name . '_refresh_time') > 7200) {
// We should create new list of user IDs
global $Timer;
$Timer->start('Users_IDs', false);
$step1_SQL = new SQL();
$step1_SQL->SELECT('T_users.user_ID, IF( user_avatar_file_ID IS NOT NULL, 1, 0 ) as has_picture, COUNT( DISTINCT blog_ID ) AS nb_blogs');
if (!empty($this->filters['reported']) && $this->filters['reported']) {
// Filter is set to 'Reported users'
$step1_SQL->SELECT_add(', COUNT( DISTINCT urep_reporter_ID ) AS user_rep');
}
$step1_SQL->FROM($this->UserQuery->get_from(''));
$step1_SQL->WHERE($this->UserQuery->get_where(''));
$step1_SQL->GROUP_BY($this->UserQuery->get_group_by(''));
$step1_SQL->ORDER_BY($this->UserQuery->get_order_by(''));
$step1_SQL->LIMIT(0);
// Get list of the IDs we need:
$user_IDs = $DB->get_col($step1_SQL->get(), 0, 'UserList::Query() Step 1: Get ID list');
// Update filter with user IDs
$this->filters['users'] = $user_IDs;
$this->save_filterset();
$Timer->stop('Users_IDs');
}
// GET TOTAL ROW COUNT:
parent::count_total_rows(count($user_IDs));
// Pagination, Get user IDs from array for current page
$user_IDs_paged = array_slice($user_IDs, ($this->page - 1) * $this->limit, $this->limit);
// *** STEP 2 ***
$step2_SQL = $this->UserQuery;
if (!empty($user_IDs_paged)) {
// Init sql query to get users by IDs
$step2_SQL->WHERE($this->Cache->dbIDname . ' IN (' . implode(',', $user_IDs_paged) . ') ');
$step2_SQL->ORDER_BY('FIND_IN_SET( user_ID, "' . implode(',', $user_IDs_paged) . '" )');
} else {
// No users
$step2_SQL->WHERE('user_ID IS NULL');
}
$this->sql = $step2_SQL->get();
// ATTENTION: we skip the parent on purpose here!! fp> refactor
DataObjectList2::query(false, false, false, 'UserList::Query() Step 2');
}
/**
*
*
* @todo count?
*/
function query_init()
{
global $current_User;
if (empty($this->filters)) {
// Filters have not been set before, we'll use the default filterset:
// If there is a preset filter, we need to activate its specific defaults:
$this->filters['filter_preset'] = param($this->param_prefix . 'filter_preset', 'string', $this->default_filters['filter_preset'], true);
$this->activate_preset_filters();
// Use the default filters:
$this->set_filters($this->default_filters);
}
// echo '<br />ItemListLight query';
//pre_dump( $this->filters );
// GENERATE THE QUERY:
/*
* filtering stuff:
*/
if (!is_null($this->Blog)) {
// Get the posts only for current Blog
$this->ItemQuery->where_chapter2($this->Blog, $this->filters['cat_array'], $this->filters['cat_modifier'], $this->filters['cat_focus']);
} else {
// If we want to get the posts from all blogs
// Save for future use (permission checks..)
$this->ItemQuery->blog = 0;
$this->ItemQuery->Blog = $this->Blog;
}
$this->ItemQuery->where_tags($this->filters['tags']);
$this->ItemQuery->where_author($this->filters['authors']);
$this->ItemQuery->where_author_logins($this->filters['authors_login']);
$this->ItemQuery->where_assignees($this->filters['assignees']);
$this->ItemQuery->where_assignees_logins($this->filters['assignees_login']);
$this->ItemQuery->where_author_assignee($this->filters['author_assignee']);
$this->ItemQuery->where_locale($this->filters['lc']);
$this->ItemQuery->where_statuses($this->filters['statuses']);
$this->ItemQuery->where_types($this->filters['types']);
$this->ItemQuery->where_keywords($this->filters['keywords'], $this->filters['phrase'], $this->filters['exact']);
$this->ItemQuery->where_ID($this->filters['post_ID'], $this->filters['post_title']);
$this->ItemQuery->where_ID_list($this->filters['post_ID_list']);
$this->ItemQuery->where_datestart($this->filters['ymdhms'], $this->filters['week'], $this->filters['ymdhms_min'], $this->filters['ymdhms_max'], $this->filters['ts_min'], $this->filters['ts_max']);
$this->ItemQuery->where_datecreated($this->filters['ts_created_max']);
$this->ItemQuery->where_visibility($this->filters['visibility_array']);
$this->ItemQuery->where_featured($this->filters['featured']);
/*
* ORDER BY stuff:
*/
if ($this->filters['post_ID_list'] && $this->filters['orderby'] == 'ID_list') {
$order_by = 'FIELD(' . $this->Cache->dbIDname . ', ' . $this->filters['post_ID_list'] . ')';
} elseif ($this->filters['orderby'] == 'ID_list') {
// Use blog setting here because 'orderby' might be set to 'ID_list' as default filter
$this->filters['orderby'] = $this->Blog->get_setting('orderby');
}
if (empty($order_by)) {
$order_by = gen_order_clause($this->filters['orderby'], $this->filters['order'], $this->Cache->dbprefix, $this->Cache->dbIDname);
}
$this->ItemQuery->order_by($order_by);
/*
* GET TOTAL ROW COUNT:
*/
if ($this->single_post) {
// Single post: no paging required!
$this->total_rows = 1;
$this->total_pages = 1;
$this->page = 1;
} elseif ($this->filters['unit'] == 'days' || $this->filters['unit'] == 'all') {
$this->total_rows = NULL;
// unknown!
$this->total_pages = 1;
$this->page = 1;
} elseif ($this->filters['unit'] == 'posts') {
/*
* TODO: The result is incorrect when using AND on categories
* We would need to use a HAVING close and thyen COUNT, which would be a subquery
* This is nto compatible with mysql 3.23
* We need fallback code.
*/
$sql_count = '
SELECT COUNT( DISTINCT ' . $this->Cache->dbIDname . ') ' . $this->ItemQuery->get_from() . $this->ItemQuery->get_where();
//echo $DB->format_query( $sql_count );
parent::count_total_rows($sql_count);
//echo '<br />'.$this->total_rows;
} else {
debug_die('Unhandled LIMITING mode in ItemList:' . $this->filters['unit'] . ' (paged mode is obsolete)');
}
/*
* Paging LIMITs:
*/
if ($this->single_post) {
// Single post: no paging required!
} elseif ($this->filters['unit'] == 'all') {
// We want ALL results!
} elseif ($this->filters['unit'] == 'posts') {
// TODO: dh> check if $limit is NULL!? - though it should not arrive at $page>1 then..
// echo 'LIMIT POSTS ';
$pgstrt = '';
if ($this->page > 1) {
// We have requested a specific page number
$pgstrt = (intval($this->page) - 1) * $this->limit . ', ';
}
$this->ItemQuery->LIMIT($pgstrt . $this->limit);
} elseif ($this->filters['unit'] == 'days') {
// We are going to limit to x days:
// echo 'LIMIT DAYS ';
if (empty($this->filters['ymdhms_min'])) {
// We have no start date, we'll display the last x days:
if (!empty($this->filters['keywords']) || !empty($this->filters['cat_array']) || !empty($this->filters['authors'])) {
// We are in DAYS mode but we can't restrict on these! (TODO: ?)
$limits = '';
} else {
// We are going to limit to LAST x days:
$lastpostdate = $this->get_lastpostdate();
$lastpostdate = mysql2date('Y-m-d 00:00:00', $lastpostdate);
$lastpostdate = mysql2date('U', $lastpostdate);
// go back x days
$otherdate = date('Y-m-d H:i:s', $lastpostdate - ($this->limit - 1) * 86400);
$this->ItemQuery->WHERE_and($this->Cache->dbprefix . 'datestart > \'' . $otherdate . '\'');
}
} else {
// We have a start date, we'll display x days starting from that point:
// $dstart_mysql has been calculated earlier
// TODO: this is redundant with previous dstart processing:
// Add trailing 0s: YYYYMMDDHHMMSS
$dstart0 = $this->filters['ymdhms_min'] . '00000000000000';
$dstart_mysql = substr($dstart0, 0, 4) . '-' . substr($dstart0, 4, 2) . '-' . substr($dstart0, 6, 2) . ' ' . substr($dstart0, 8, 2) . ':' . substr($dstart0, 10, 2) . ':' . substr($dstart0, 12, 2);
$dstart_ts = mysql2timestamp($dstart_mysql);
// go forward x days
$enddate_ts = date('Y-m-d H:i:s', $dstart_ts + $this->limit * 86400);
$this->ItemQuery->WHERE_and($this->Cache->dbprefix . 'datestart < \'' . $enddate_ts . '\'');
}
} else {
debug_die('Unhandled LIMITING mode in ItemList:' . $this->filters['unit'] . ' (paged mode is obsolete)');
}
}
/**
* Initialize sql query
*
* @todo count?
*
* @param boolean
*/
function query_init($force_init = false)
{
global $DB;
if (!$force_init && !empty($this->query_is_initialized)) {
// Don't initialize query because it was already done
return;
}
// Save to know the query init was done
$this->query_is_initialized = true;
if (empty($this->filters)) {
// Filters have not been set before, we'll use the default filterset:
// If there is a preset filter, we need to activate its specific defaults:
$this->filters['filter_preset'] = param($this->param_prefix . 'filter_preset', 'string', $this->default_filters['filter_preset'], true);
$this->activate_preset_filters();
// Use the default filters:
$this->set_filters($this->default_filters);
}
// GENERATE THE QUERY:
/*
* Resrict to selected blog
*/
// If we dont have specific comment or post ids, we have to restric to blog
if (!is_null($this->Blog) && ($this->filters['post_ID'] == NULL || !empty($this->filters['post_ID']) && substr($this->filters['post_ID'], 0, 1) == '-') && ($this->filters['comment_ID'] == NULL || !empty($this->filters['comment_ID']) && substr($this->filters['comment_ID'], 0, 1) == '-') && ($this->filters['comment_ID_list'] == NULL || !empty($this->filters['comment_ID_list']) && substr($this->filters['comment_ID_list'], 0, 1) == '-')) {
// restriction for blog
$this->ItemQuery->where_chapter($this->Blog->ID);
}
/*
* filtering stuff:
*/
$this->CommentQuery->where_author($this->filters['author_IDs']);
$this->CommentQuery->where_author_email($this->filters['author_email']);
$this->CommentQuery->where_author_url($this->filters['author_url'], $this->filters['url_match'], $this->filters['include_emptyurl']);
$this->CommentQuery->where_author_IP($this->filters['author_IP']);
$this->ItemQuery->where_ID($this->filters['post_ID']);
$this->CommentQuery->where_ID($this->filters['comment_ID'], $this->filters['author']);
$this->CommentQuery->where_ID_list($this->filters['comment_ID_list']);
$this->CommentQuery->where_rating($this->filters['rating_toshow'], $this->filters['rating_turn'], $this->filters['rating_limit']);
$this->CommentQuery->where_keywords($this->filters['keywords'], $this->filters['phrase'], $this->filters['exact']);
$this->CommentQuery->where_statuses($this->filters['statuses']);
$this->CommentQuery->where_types($this->filters['types']);
$this->ItemQuery->where_datestart('', '', '', '', $this->filters['timestamp_min'], $this->filters['timestamp_max']);
if (!is_null($this->Blog) && isset($this->filters['user_perm'])) {
// If Blog and required user permission is set, add the corresponding restriction
$this->CommentQuery->user_perm_restrict($this->filters['user_perm'], $this->Blog->ID);
}
/*
* ORDER BY stuff:
*/
$available_sort_options = array('date', 'type', 'author', 'author_url', 'author_email', 'author_IP', 'spam_karma', 'status', 'item_ID');
$order_by = gen_order_clause($this->filters['orderby'], $this->filters['order'], $this->Cache->dbprefix, $this->Cache->dbIDname, $available_sort_options);
if ($this->filters['threaded_comments']) {
// In mode "Threaded comments" we should get all replies in the begining of the list
$order_by = $this->Cache->dbprefix . 'in_reply_to_cmt_ID DESC, ' . $order_by;
}
$this->CommentQuery->order_by($order_by);
// GET Item IDs, this way we don't have to JOIN two times the items and the categories table into the comment query
if (isset($this->filters['post_statuses'])) {
// Set post statuses by filters
$post_show_statuses = $this->filters['post_statuses'];
} elseif (is_admin_page()) {
// Allow all kind of post status ( This statuses will be filtered later by user perms )
$post_show_statuses = get_visibility_statuses('keys');
} else {
// Allow only inskin statuses for posts
$post_show_statuses = get_inskin_statuses(isset($this->Blog) ? $this->Blog->ID : NULL, 'post');
}
// Restrict post filters to available statuses. When blog = 0 we will check visibility statuses for each blog separately ( on the same query ).
$this->ItemQuery->where_visibility($post_show_statuses);
$sql_item_IDs = 'SELECT DISTINCT post_ID' . $this->ItemQuery->get_from();
if (strpos($this->ItemQuery->get_from(), 'T_categories') === false && strpos($this->ItemQuery->get_where(), 'cat_blog_ID') !== false) {
// Join categories table because it is required here for the field "cat_blog_ID"
$sql_item_IDs .= ' INNER JOIN T_categories ON post_main_cat_ID = cat_ID ';
}
$sql_item_IDs .= $this->ItemQuery->get_where();
$item_IDs = $DB->get_col($sql_item_IDs, 0, 'Get CommentQuery Item IDs');
if (empty($item_IDs)) {
// There is no item which belongs to the given blog and user may view it, so there are no comments either
parent::count_total_rows(0);
$this->CommentQuery->WHERE_and('FALSE');
return;
}
$this->CommentQuery->where_post_ID(implode(',', $item_IDs));
/*
* Restrict to active comments by default, show expired comments only if it was requested
* Note: This condition makes the CommentQuery a lot slower!
*/
$this->CommentQuery->expiry_restrict($this->filters['expiry_statuses']);
/*
* GET TOTAL ROW COUNT:
*/
$sql_count = '
SELECT COUNT( ' . $this->Cache->dbIDname . ') ' . $this->CommentQuery->get_from() . $this->CommentQuery->get_where();
parent::count_total_rows($sql_count);
/*
* Page set up:
*/
if ($this->page > 1) {
// We have requested a specific page number
if ($this->limit > 0) {
$pgstrt = '';
$pgstrt = (intval($this->page) - 1) * $this->limit . ', ';
$this->CommentQuery->LIMIT($pgstrt . $this->limit);
}
} else {
$this->CommentQuery->LIMIT($this->limit);
}
}
