/**
* @param $a_topic_id
* @param bool $is_moderator
* @param int $limit
* @param int $offset
* @return array
*/
public function getAllThreads($a_topic_id, $is_moderator = false, $limit = 0, $offset = 0)
{
global $ilDB, $ilUser;
// Count all threads for the passed forum
$query = 'SELECT COUNT(thr_pk) cnt
FROM frm_threads
WHERE thr_top_fk = %s';
$res = $ilDB->queryF($query, array('integer'), array($a_topic_id));
$data = $ilDB->fetchAssoc($res);
$cnt = (int) $data['cnt'];
$threads = array();
$data = array();
$data_types = array();
$active_query = '';
$active_inner_query = '';
if (!$is_moderator) {
$active_query = ' AND (pos_status = %s OR pos_usr_id = %s) ';
$active_inner_query = ' AND (ipos.pos_status = %s OR ipos.pos_usr_id = %s) ';
}
if (!$ilUser->isAnonymous()) {
$query = "SELECT\n\t\t\t\t (CASE WHEN COUNT(DISTINCT(notification_id)) > 0 THEN 1 ELSE 0 END) usr_notification_is_enabled,\n\t\t\t\t MAX(pos_date) post_date,\n\t\t\t\t COUNT(DISTINCT(pos_pk)) num_posts,\n\t\t\t\t \n\t\t\t\t COUNT(DISTINCT(pos_pk)) - COUNT(DISTINCT(postread.post_id)) num_unread_posts,\n\t\t\t\t \n\t\t\t\t (\n\t\t\t\t \tSELECT COUNT(DISTINCT(ipos.pos_pk))\n\t\t\t\t\tFROM frm_posts ipos\n\t\t\t\t\tLEFT JOIN frm_user_read iread ON iread.post_id = ipos.pos_pk AND iread.usr_id = %s\n\t\t\t\t\tLEFT JOIN frm_thread_access iacc ON (iacc.thread_id = ipos.pos_thr_fk AND iacc.usr_id = %s)\n\t\t\t\t\tWHERE ipos.pos_thr_fk = thr_pk\n\t\t\t\t\t \n\t\t\t\t\tAND (\n\t\t\t\t\t\t(ipos.pos_date > iacc.access_old_ts OR ipos.pos_update > iacc.access_old_ts)\n\t\t\t\t\t\tOR\n\t\t\t\t\t\t(iacc.access_old IS NULL AND (ipos.pos_date > " . $ilDB->quote(date('Y-m-d H:i:s', NEW_DEADLINE), 'timestamp') . " OR ipos.pos_update > " . $ilDB->quote(date('Y-m-d H:i:s', NEW_DEADLINE), 'timestamp') . "))\n\t\t\t\t\t\t)\n\t\t\t\t\t \n\t\t\t\t\tAND ipos.pos_usr_id != %s\n\t\t\t\t\tAND iread.usr_id IS NULL {$active_inner_query}\n\t\t\t\t ) num_new_posts,\n\t\t\t\t \n\t\t\t\t thr_pk, thr_top_fk, thr_subject, thr_usr_id, thr_usr_alias, thr_num_posts, thr_last_post, thr_date, thr_update, visits, frm_threads.import_name, is_sticky, is_closed\n\t\t\t\t \n\t\t\t\t FROM frm_threads\n\t\t\t\t \n\t\t\t\t LEFT JOIN frm_notification\n\t\t\t\t \tON frm_notification.thread_id = thr_pk\n\t\t\t\t \tAND frm_notification.user_id = %s\n\t\t\t\t \n\t\t\t\t LEFT JOIN frm_posts\n\t\t\t\t \tON pos_thr_fk = thr_pk {$active_query}\n\t\t\t\t \n\t\t\t\t LEFT JOIN frm_user_read postread\n\t\t\t\t \tON postread.post_id = pos_pk\n\t\t\t\t \tAND postread.usr_id = %s\n\t\t\t\t \n\t\t\t\t WHERE thr_top_fk = %s\n\t\t\t\t\tGROUP BY thr_pk, thr_top_fk, thr_subject, thr_usr_id, thr_usr_alias, thr_num_posts, thr_last_post, thr_date, thr_update, visits, frm_threads.import_name, is_sticky, is_closed\n\t\t\t\t\tORDER BY is_sticky DESC, post_date DESC, thr_date DESC";
$data_types[] = 'integer';
$data_types[] = 'integer';
$data_types[] = 'integer';
if (!$is_moderator) {
array_push($data_types, 'integer', 'integer');
}
$data_types[] = 'integer';
if (!$is_moderator) {
array_push($data_types, 'integer', 'integer');
}
$data_types[] = 'integer';
$data_types[] = 'integer';
$data[] = $ilUser->getId();
$data[] = $ilUser->getId();
$data[] = $ilUser->getId();
if (!$is_moderator) {
array_push($data, '1', $ilUser->getId());
}
$data[] = $ilUser->getId();
if (!$is_moderator) {
array_push($data, '1', $ilUser->getId());
}
$data[] = $ilUser->getId();
$data[] = $a_topic_id;
} else {
$query = "SELECT\n\t\t\t\t\t 0 usr_notification_is_enabled,\n\t\t\t\t\t MAX(pos_date) post_date,\n\t\t\t\t\t COUNT(DISTINCT(pos_pk)) num_posts,\n\t\t\t\t\t COUNT(DISTINCT(pos_pk)) num_unread_posts,\n\t\t\t\t\t COUNT(DISTINCT(pos_pk)) num_new_posts,\n\t\t\t\t\t thr_pk, thr_top_fk, thr_subject, thr_usr_id, thr_usr_alias, thr_num_posts, thr_last_post, thr_date, thr_update, visits, frm_threads.import_name, is_sticky, is_closed\n\t\t\t\t\t FROM frm_threads\n\t\t\t\t\t \n\t\t\t\t\t LEFT JOIN frm_posts\n\t\t\t\t\t\tON pos_thr_fk = thr_pk {$active_query}";
$query .= " WHERE thr_top_fk = %s\n\t\t\t\t\t\tGROUP BY thr_pk, thr_top_fk, thr_subject, thr_usr_id, thr_usr_alias, thr_num_posts, thr_last_post, thr_date, thr_update, visits, frm_threads.import_name, is_sticky, is_closed\n\t\t\t\t\t\tORDER BY is_sticky DESC, thr_date DESC";
if (!$is_moderator) {
array_push($data_types, 'integer', 'integer');
}
$data_types[] = 'integer';
if (!$is_moderator) {
array_push($data, '1', $ilUser->getId());
}
$data[] = $a_topic_id;
}
if ($limit || $offset) {
$ilDB->setLimit($limit, $offset);
}
$res = $ilDB->queryF($query, $data_types, $data);
while ($row = $ilDB->fetchAssoc($res)) {
$thread = new ilForumTopic($row['thr_pk'], $is_moderator, true);
$thread->assignData($row);
$threads[] = $thread;
}
return array('items' => $threads, 'cnt' => $cnt);
}
/**
* @param $a_topic_id
* @param array $params
* @param int $limit
* @param int $offset
* @return array
*/
public function getAllThreads($a_topic_id, array $params = array(), $limit = 0, $offset = 0)
{
/**
* @var $ilDB ilDB
* @var $ilUser ilObjUser
* @var $ilSetting ilSetting
*/
global $ilDB, $ilUser, $ilSetting;
$frm_overview_setting = (int) $ilSetting::_lookupValue('frma', 'forum_overview');
$frm_props = ilForumProperties::getInstance($this->getForumId());
$excluded_ids_condition = '';
if (isset($params['excluded_ids']) && is_array($params['excluded_ids']) && $params['excluded_ids']) {
$excluded_ids_condition = ' AND ' . $ilDB->in('thr_pk', $params['excluded_ids'], true, 'integer') . ' ';
}
if (!in_array(strtolower($params['order_column']), array('lp_date', 'rating'))) {
$params['order_column'] = 'post_date';
}
if (!in_array(strtolower($params['order_direction']), array('asc', 'desc'))) {
$params['order_direction'] = 'desc';
}
// Count all threads for the passed forum
$query = "SELECT COUNT(thr_pk) cnt\n\t\t\t\t FROM frm_threads\n\t\t\t\t WHERE thr_top_fk = %s {$excluded_ids_condition}";
$res = $ilDB->queryF($query, array('integer'), array($a_topic_id));
$data = $ilDB->fetchAssoc($res);
$cnt = (int) $data['cnt'];
$threads = array();
$data = array();
$data_types = array();
$active_query = '';
$active_inner_query = '';
$is_post_activation_enabled = $frm_props->isPostActivationEnabled();
if ($is_post_activation_enabled && !$params['is_moderator']) {
$active_query = ' AND (pos_status = %s OR pos_author_id = %s) ';
$active_inner_query = ' AND (ipos.pos_status = %s OR ipos.pos_author_id = %s) ';
}
$optional_fields = '';
if ($frm_props->isIsThreadRatingEnabled()) {
$optional_fields = ',avg_rating';
}
if ($frm_props->getThreadSorting() == 1) {
$optional_fields = ',thread_sorting';
}
$additional_sort = '';
if ($frm_props->getThreadSorting()) {
$additional_sort .= ' ,thread_sorting ASC ';
}
$dynamic_columns = array(' ,post_date ' . $params['order_direction']);
if ($frm_props->isIsThreadRatingEnabled()) {
$dynamic_columns[] = ' ,avg_rating ' . $params['order_direction'];
}
if ('rating' == strtolower($params['order_column'])) {
$dynamic_columns = array_reverse($dynamic_columns);
}
$additional_sort .= implode(' ', $dynamic_columns);
if (!$ilUser->isAnonymous()) {
$query = "SELECT\n\t\t\t\t\t (CASE WHEN COUNT(DISTINCT(notification_id)) > 0 THEN 1 ELSE 0 END) usr_notification_is_enabled,\n\t\t\t\t\t MAX(pos_date) post_date,\n\t\t\t\t\t COUNT(DISTINCT(pos_pk)) num_posts, \n\t\t\t\t\t COUNT(DISTINCT(pos_pk)) - COUNT(DISTINCT(postread.post_id)) num_unread_posts, ";
// new posts query
if ($frm_overview_setting == ilForumProperties::FORUM_OVERVIEW_WITH_NEW_POSTS) {
$query .= "\n\t\t\t\t\t (SELECT COUNT(DISTINCT(ipos.pos_pk))\n\t\t\t\t\t\tFROM frm_posts ipos\n\t\t\t\t\t\tLEFT JOIN frm_user_read iread ON iread.post_id = ipos.pos_pk AND iread.usr_id = %s\n\t\t\t\t\t\tLEFT JOIN frm_thread_access iacc ON (iacc.thread_id = ipos.pos_thr_fk AND iacc.usr_id = %s)\n\t\t\t\t\t\tWHERE ipos.pos_thr_fk = thr_pk\n\t\t\t\t\t\t \n\t\t\t\t\t\tAND (ipos.pos_update > iacc.access_old_ts\n\t\t\t\t\t\t\tOR\n\t\t\t\t\t\t\t(iacc.access_old IS NULL AND (ipos.pos_update > " . $ilDB->quote(date('Y-m-d H:i:s', NEW_DEADLINE), 'timestamp') . "))\n\t\t\t\t\t\t\t)\n\t\t\t\t\t\t \n\t\t\t\t\t\tAND ipos.pos_author_id != %s\n\t\t\t\t\t\tAND iread.usr_id IS NULL {$active_inner_query}\n\t\t\t\t\t ) num_new_posts, ";
}
$query .= " thr_pk, thr_top_fk, thr_subject, thr_author_id, thr_display_user_id, thr_usr_alias, thr_num_posts, thr_last_post, thr_date, thr_update, visits, frm_threads.import_name, is_sticky, is_closed\n\t\t\t\t\t {$optional_fields}\n\t\t\t\t\t FROM frm_threads\n\t\t\t\t\t \n\t\t\t\t\t LEFT JOIN frm_notification\n\t\t\t\t\t\tON frm_notification.thread_id = thr_pk\n\t\t\t\t\t\tAND frm_notification.user_id = %s\n\t\t\t\t\t \n\t\t\t\t\t LEFT JOIN frm_posts\n\t\t\t\t\t\tON pos_thr_fk = thr_pk {$active_query}\n\t\t\t\t\t \n\t\t\t\t\t LEFT JOIN frm_user_read postread\n\t\t\t\t\t\tON postread.post_id = pos_pk\n\t\t\t\t\t\tAND postread.usr_id = %s";
$query .= " WHERE thr_top_fk = %s\n\t\t\t\t\t\t{$excluded_ids_condition}\n\t\t\t\t\t\tGROUP BY thr_pk, thr_top_fk, thr_subject, thr_author_id, thr_display_user_id, thr_usr_alias, thr_num_posts, thr_last_post, thr_date, thr_update, visits, frm_threads.import_name, is_sticky, is_closed\n\t\t\t\t\t\t{$optional_fields}\n\t\t\t\t\t\tORDER BY is_sticky DESC {$additional_sort}, thr_date DESC";
// data_types for new posts query and $active_inner_query
if ($frm_overview_setting == ilForumProperties::FORUM_OVERVIEW_WITH_NEW_POSTS) {
$data_types[] = 'integer';
$data_types[] = 'integer';
$data_types[] = 'integer';
if ($is_post_activation_enabled && !$params['is_moderator']) {
array_push($data_types, 'integer', 'integer');
}
}
$data_types[] = 'integer';
if ($is_post_activation_enabled && !$params['is_moderator']) {
array_push($data_types, 'integer', 'integer');
}
$data_types[] = 'integer';
$data_types[] = 'integer';
// data_values for new posts query and $active_inner_query
if ($frm_overview_setting == ilForumProperties::FORUM_OVERVIEW_WITH_NEW_POSTS) {
$data[] = $ilUser->getId();
$data[] = $ilUser->getId();
$data[] = $ilUser->getId();
if ($is_post_activation_enabled && !$params['is_moderator']) {
array_push($data, '1', $ilUser->getId());
}
}
$data[] = $ilUser->getId();
if ($is_post_activation_enabled && !$params['is_moderator']) {
array_push($data, '1', $ilUser->getId());
}
$data[] = $ilUser->getId();
$data[] = $a_topic_id;
} else {
$query = "SELECT\n\t\t\t\t\t 0 usr_notification_is_enabled,\n\t\t\t\t\t MAX(pos_date) post_date,\n\t\t\t\t\t COUNT(DISTINCT(pos_pk)) num_posts,\n\t\t\t\t\t COUNT(DISTINCT(pos_pk)) num_unread_posts,\n\t\t\t\t\t COUNT(DISTINCT(pos_pk)) num_new_posts,\n\t\t\t\t\t thr_pk, thr_top_fk, thr_subject, thr_author_id, thr_display_user_id, thr_usr_alias, thr_num_posts, thr_last_post, thr_date, thr_update, visits, frm_threads.import_name, is_sticky, is_closed\n\t\t\t\t\t {$optional_fields}\n\t\t\t\t\t FROM frm_threads\n\t\t\t\t\t \n\t\t\t\t\t LEFT JOIN frm_posts\n\t\t\t\t\t\tON pos_thr_fk = thr_pk {$active_query}";
$query .= " WHERE thr_top_fk = %s\n\t\t\t\t\t\t{$excluded_ids_condition}\n\t\t\t\t\t\tGROUP BY thr_pk, thr_top_fk, thr_subject, thr_author_id, thr_display_user_id, thr_usr_alias, thr_num_posts, thr_last_post, thr_date, thr_update, visits, frm_threads.import_name, is_sticky, is_closed\n\t\t\t\t\t\t{$optional_fields}\n\t\t\t\t\t\tORDER BY is_sticky DESC {$additional_sort}, thr_date DESC";
if ($is_post_activation_enabled && !$params['is_moderator']) {
array_push($data_types, 'integer', 'integer');
}
$data_types[] = 'integer';
if ($is_post_activation_enabled && !$params['is_moderator']) {
array_push($data, '1', $ilUser->getId());
}
$data[] = $a_topic_id;
}
if ($limit || $offset) {
$ilDB->setLimit($limit, $offset);
}
$res = $ilDB->queryF($query, $data_types, $data);
while ($row = $ilDB->fetchAssoc($res)) {
$thread = new ilForumTopic($row['thr_pk'], $params['is_moderator'], true);
$thread->assignData($row);
$threads[] = $thread;
}
return array('items' => $threads, 'cnt' => $cnt);
}
