/** * @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); }