function sp_groupview_stats_query($posts)
    {
        if (empty($posts)) {
            return;
        }
        global $spThisUser;
        $WHERE = SFPOSTS . '.post_id IN (';
        $pcount = count($posts);
        $done = 0;
        foreach ($posts as $post) {
            $WHERE .= $post;
            $done++;
            if ($done < $pcount) {
                $WHERE .= ',';
            }
        }
        $WHERE .= ')';
        $spdb = new spdbComplex();
        $spdb->table = SFPOSTS;
        $spdb->fields = SFPOSTS . '.post_id, ' . SFPOSTS . '.topic_id, topic_name, ' . SFPOSTS . '.forum_id, ' . spdb_zone_datetime('post_date') . ',
								guest_name, guest_email, ' . SFPOSTS . '.user_id, post_content, post_status, ' . SFMEMBERS . '.display_name,
								post_index, topic_slug';
        $spdb->left_join = array(SFTOPICS . ' ON ' . SFPOSTS . '.topic_id = ' . SFTOPICS . '.topic_id', SFMEMBERS . ' ON ' . SFPOSTS . '.user_id = ' . SFMEMBERS . '.user_id');
        $spdb->where = $WHERE;
        $spdb = apply_filters('sph_groupview_stats_query', $spdb, $this);
        if (!empty($spThisUser->inspect['q_spGroupViewStats'])) {
            $spdb->inspect = 'spGroupViewStats';
            $spdb->show = true;
        }
        $records = $spdb->select();
        if ($records) {
            # sort them into forum ids
            foreach ($records as $r) {
                $f[$r->forum_id] = $r;
            }
        }
        return $f;
    }
    function sp_topicview_query($topicid = 0, $cPage = 1, $forumid = 0)
    {
        global $spGlobals, $spThisUser, $spVars;
        # do we have a valid topic id
        if ($topicid == 0) {
            $this->topicViewStatus = 'no data';
            return;
        } else {
            $WHERE = SFTOPICS . '.topic_id=' . $topicid;
        }
        # default to no access
        $this->topicViewStatus = 'no access';
        # some setup vars
        $startlimit = 0;
        $lastpage = 0;
        # how many posts per page?
        $ppaged = $spGlobals['display']['posts']['perpage'];
        if (!$ppaged) {
            $ppaged = 10;
        }
        # setup where we are in the post list (paging)
        if ($cPage != 1) {
            $startlimit = ($cPage - 1) * $ppaged;
        }
        $LIMIT = $startlimit . ', ' . $ppaged;
        # Set up order by
        $setSort = false;
        $reverse = false;
        $setSort = $spGlobals['display']['posts']['sortdesc'];
        if (isset($spGlobals['sort_order']['topic'])) {
            $reverse = array_search($topicid, (array) $spGlobals['sort_order']['topic']) !== false ? true : false;
        }
        if (isset($spThisUser->postDESC) && $spThisUser->postDESC) {
            $reverse = !$reverse;
        }
        if ($setSort xor $reverse) {
            $ORDER = 'post_pinned DESC, ' . SFPOSTS . ".post_id DESC";
        } else {
            $ORDER = 'post_pinned DESC, ' . SFPOSTS . ".post_id ASC";
        }
        # add newpost/sfwaiting support for admins
        $waitCheck = ', NULL AS new_post';
        if ($spThisUser->admin || $spThisUser->moderator) {
            $waitCheck = ', ' . SFWAITING . '.post_count AS new_post';
        }
        # Discover if this topic is in users new post list
        $maybeNewPost = false;
        if ($spThisUser->member && sp_is_in_users_newposts($topicid)) {
            $maybeNewPost = true;
        }
        # retrieve topic and post records
        $spdb = new spdbComplex();
        $spdb->table = SFTOPICS;
        $spdb->found_rows = true;
        $spdb->fields = 'group_id, ' . SFTOPICS . '.topic_id, ' . SFTOPICS . '.forum_id, topic_name, topic_slug, topic_status, topic_pinned, topic_icon, topic_opened, ' . SFTOPICS . '.post_count, forum_name, forum_slug, forum_status,
							  forum_disabled, forum_rss_private, ' . SFPOSTS . '.post_id, ' . spdb_zone_datetime('post_date') . ', ' . SFPOSTS . '.user_id, ' . SFTOPICS . '.user_id AS topic_starter,
							  guest_name, guest_email, post_status, post_pinned, post_index, post_edit, poster_ip, source, post_content' . $waitCheck;
        $spdb->join = array(SFPOSTS . ' ON ' . SFTOPICS . '.topic_id=' . SFPOSTS . '.topic_id', SFFORUMS . ' ON ' . SFTOPICS . '.forum_id=' . SFFORUMS . '.forum_id');
        if ($spThisUser->admin || $spThisUser->moderator) {
            $spdb->left_join = array(SFWAITING . ' ON ' . SFPOSTS . '.post_id=' . SFWAITING . '.post_id');
        }
        $spdb->where = $WHERE;
        $spdb->orderby = $ORDER;
        $spdb->limits = $LIMIT;
        $spdb = apply_filters('sph_topicview_query', $spdb, $this);
        if (!empty($spThisUser->inspect['q_spTopicView'])) {
            $spdb->inspect = 'spTopicView';
            $spdb->show = true;
        }
        $records = $spdb->select();
        $t = array();
        if ($records) {
            $tidx = $topicid;
            $pidx = 0;
            $r = current($records);
            if (sp_get_auth('view_forum', $r->forum_id)) {
                $this->topicViewStatus = 'data';
                # construct the parent topic object
                $t[$tidx] = new stdClass();
                $t[$tidx]->topic_id = $r->topic_id;
                $t[$tidx]->forum_id = $r->forum_id;
                $t[$tidx]->group_id = $r->group_id;
                $t[$tidx]->forum_name = sp_filter_title_display($r->forum_name);
                $t[$tidx]->topic_name = sp_filter_title_display($r->topic_name);
                $t[$tidx]->topic_slug = $r->topic_slug;
                $t[$tidx]->topic_opened = $r->topic_opened;
                $t[$tidx]->forum_status = $r->forum_status;
                $t[$tidx]->topic_pinned = $r->topic_pinned;
                $t[$tidx]->forum_disabled = $r->forum_disabled;
                $t[$tidx]->forum_slug = $r->forum_slug;
                $t[$tidx]->forum_rss_private = $r->forum_rss_private;
                $t[$tidx]->topic_permalink = sp_build_url($r->forum_slug, $r->topic_slug, 1, 0);
                $t[$tidx]->topic_status = $r->topic_status;
                $t[$tidx]->topic_icon = sanitize_file_name($r->topic_icon);
                $t[$tidx]->rss = '';
                $t[$tidx]->editmode = 0;
                $t[$tidx]->tools_flag = 1;
                $t[$tidx]->display_page = $this->topicPage;
                $t[$tidx]->posts_per_page = $ppaged;
                $t[$tidx]->unread = 0;
                # user calc_rows and nor post_count as - for example - some posts may be hiodden by choice.
                $t[$tidx]->post_count = spdb_select('var', 'SELECT FOUND_ROWS()');
                # Can the user create new topics or should we lock the forum?
                $t[$tidx]->start_topics = sp_get_auth('start_topics', $r->forum_id);
                $t[$tidx]->reply_topics = sp_get_auth('reply_topics', $r->forum_id);
                $t[$tidx]->reply_own_topics = sp_get_auth('reply_own_topics', $r->forum_id);
                # grab topic start info
                $t[$tidx]->topic_starter = $r->topic_starter;
                $totalPages = $r->post_count / $ppaged;
                if (!is_int($totalPages)) {
                    $totalPages = intval($totalPages) + 1;
                }
                $t[$tidx]->total_pages = $totalPages;
                if ($setSort xor $reverse) {
                    if ($cPage == 1) {
                        $lastpage = true;
                    }
                } else {
                    if ($cPage == $totalPages) {
                        $lastpage = true;
                    }
                }
                $t[$tidx]->last_page = $lastpage;
                $t[$tidx] = apply_filters('sph_topicview_topic_record', $t[$tidx], $r);
                reset($records);
                unset($r);
                # now loop through the post records
                $newPostFlag = false;
                $firstPostPage = 1;
                $pinned = 0;
                # define post id and post user id arrays for plugins to use in combined filter
                $p = array();
                $u = array();
                foreach ($records as $r) {
                    $pidx = $r->post_id;
                    $p[] = $pidx;
                    # prepare for user object
                    $cUser = $spThisUser->ID == $r->user_id;
                    $cSmall = !$cUser;
                    $t[$tidx]->posts[$pidx] = new stdClass();
                    $t[$tidx]->posts[$pidx]->post_id = $r->post_id;
                    $t[$tidx]->posts[$pidx]->post_date = $r->post_date;
                    $t[$tidx]->posts[$pidx]->user_id = $r->user_id;
                    $t[$tidx]->posts[$pidx]->guest_name = sp_filter_name_display($r->guest_name);
                    $t[$tidx]->posts[$pidx]->guest_email = sp_filter_email_display($r->guest_email);
                    $t[$tidx]->posts[$pidx]->post_status = $r->post_status;
                    $t[$tidx]->posts[$pidx]->post_pinned = $r->post_pinned;
                    $t[$tidx]->posts[$pidx]->post_index = $r->post_index;
                    $t[$tidx]->posts[$pidx]->poster_ip = $r->poster_ip;
                    $t[$tidx]->posts[$pidx]->source = $r->source;
                    $t[$tidx]->posts[$pidx]->post_permalink = sp_build_url($r->forum_slug, $r->topic_slug, $cPage, $r->post_id);
                    $t[$tidx]->posts[$pidx]->edits = '';
                    $t[$tidx]->posts[$pidx]->last_post = 0;
                    $t[$tidx]->posts[$pidx]->last_post_on_page = 0;
                    $t[$tidx]->posts[$pidx]->first_post_on_page = $firstPostPage;
                    $t[$tidx]->posts[$pidx]->editmode = 0;
                    $t[$tidx]->posts[$pidx]->post_content = sp_filter_content_display($r->post_content);
                    $t[$tidx]->posts[$pidx]->first_pinned = 0;
                    $t[$tidx]->posts[$pidx]->last_pinned = 0;
                    $t[$tidx]->posts[$pidx]->postUser = new stdClass();
                    $t[$tidx]->posts[$pidx]->postUser = clone sp_get_user($r->user_id, $cUser, $cSmall);
                    # populate the user guest name and email in case the poster is a guest
                    if ($r->user_id == 0) {
                        $t[$tidx]->posts[$pidx]->postUser->guest_name = $t[$tidx]->posts[$pidx]->guest_name;
                        $t[$tidx]->posts[$pidx]->postUser->guest_email = $t[$tidx]->posts[$pidx]->guest_email;
                        $t[$tidx]->posts[$pidx]->postUser->display_name = $t[$tidx]->posts[$pidx]->guest_name;
                        $t[$tidx]->posts[$pidx]->postUser->ip = $t[$tidx]->posts[$pidx]->poster_ip;
                    }
                    # pinned status
                    if ($firstPostPage == 1 && $r->post_pinned) {
                        $t[$tidx]->posts[$pidx]->first_pinned = true;
                        $pinned = $pidx;
                    }
                    if ($firstPostPage == 0 && $pinned > 0 && $r->post_pinned == false) {
                        $t[$tidx]->posts[$pinned]->last_pinned = true;
                    } elseif ($r->post_pinned) {
                        $pinned = $pidx;
                    }
                    $firstPostPage = 0;
                    # Is this a new post for the current user?
                    if ($spThisUser->guest) {
                        $newPostFlag = false;
                    } else {
                        if ($maybeNewPost && strtotime($r->post_date) > strtotime($spThisUser->lastvisit)) {
                            $newPostFlag = true;
                        }
                        if (isset($r->new_post)) {
                            $newPostFlag = true;
                        }
                    }
                    $t[$tidx]->posts[$pidx]->new_post = $newPostFlag;
                    # do we need to hide an admin post?
                    if (!sp_get_auth('view_admin_posts', $r->forum_id) && sp_is_forum_admin($r->user_id)) {
                        $adminview = sp_get_sfmeta('adminview', 'message');
                        if ($adminview) {
                            $t[$tidx]->posts[$pidx]->post_content = '<div class="spMessage">';
                            $t[$tidx]->posts[$pidx]->post_content .= sp_filter_text_display($adminview[0]['meta_value']);
                            $t[$tidx]->posts[$pidx]->post_content .= '</div>';
                        } else {
                            $t[$tidx]->posts[$pidx]->post_content = '';
                        }
                    }
                    # do we need to hide an others posts?
                    if (sp_get_auth('view_own_admin_posts', $r->forum_id) && !sp_is_forum_admin($r->user_id) && !sp_is_forum_mod($r->user_id) && $spThisUser->ID != $r->user_id) {
                        $userview = sp_get_sfmeta('userview', 'message');
                        if ($userview) {
                            $t[$tidx]->posts[$pidx]->post_content = '<div class="spMessage">';
                            $t[$tidx]->posts[$pidx]->post_content .= sp_filter_text_display($userview[0]['meta_value']);
                            $t[$tidx]->posts[$pidx]->post_content .= '</div>';
                        } else {
                            $t[$tidx]->posts[$pidx]->post_content = '';
                        }
                    }
                    # Is this post to be edited?
                    if ($spVars['displaymode'] == 'edit' && $spVars['postedit'] == $r->post_id) {
                        $t[$tidx]->editmode = 1;
                        $t[$tidx]->editpost_id = $r->post_id;
                        $t[$tidx]->editpost_content = sp_filter_content_edit($r->post_content);
                        $t[$tidx]->posts[$pidx]->editmode = 1;
                    }
                    # Add edit history
                    if (!empty($r->post_edit) && is_serialized($r->post_edit)) {
                        $edits = unserialize($r->post_edit);
                        $eidx = 0;
                        foreach ($edits as $e) {
                            $t[$tidx]->posts[$pidx]->edits[$eidx] = new stdClass();
                            $t[$tidx]->posts[$pidx]->edits[$eidx]->by = $e['by'];
                            $t[$tidx]->posts[$pidx]->edits[$eidx]->at = $e['at'];
                            $eidx++;
                        }
                    }
                    if (!in_array($r->user_id, $u)) {
                        $u[] = $r->user_id;
                    }
                    $t[$tidx]->posts[$pidx] = apply_filters('sph_topicview_post_records', $t[$tidx]->posts[$pidx], $r);
                }
                # index of post IDs with position in listing
                $t[$tidx]->post_keys = $p;
                $t[$tidx]->posts[$pidx]->last_post = $lastpage;
                $t[$tidx]->posts[$pidx]->last_post_on_page = 1;
                # save last post on page id
                $t[$tidx]->last_post_id = $r->post_id;
                # allow plugins to add more data to combined topic/post data structure
                $t[$tidx] = apply_filters('sph_topicview_combined_data', $t[$tidx], $p, $u);
                unset($records);
            } else {
                # check for view forum lists but not topic lists
                if (sp_can_view($r->forum_id, 'forum-title')) {
                    $this->topicViewStatus = 'sneak peek';
                }
            }
        }
        return $t;
    }
    function sp_postlistview_query($where, $order, $count, $view, $type)
    {
        global $spGlobals, $spThisUser;
        # If no WHERE clause then return empty
        if (empty($where)) {
            return;
        }
        # build list of forums user can view
        $fids = sp_user_visible_forums($view);
        if (!empty($fids)) {
            $fids = implode(',', $fids);
            $where .= ' AND ' . SFPOSTS . ".forum_id IN ({$fids})";
        }
        # Check order
        if (empty($order)) {
            $order = SFPOSTS . '.post_id DESC';
        }
        $spdb = new spdbComplex();
        $spdb->table = SFPOSTS;
        $spdb->fields = SFPOSTS . '.post_id, post_content, ' . spdb_zone_datetime('post_date') . ', ' . SFPOSTS . '.topic_id, ' . SFPOSTS . '.forum_id,
								  ' . SFPOSTS . '.user_id, guest_name, post_status, post_index, forum_name, forum_slug, forum_disabled, ' . SFFORUMS . '.group_id, group_name,
								  topic_name, topic_slug, ' . SFTOPICS . '.post_count, topic_opened, display_name';
        $spdb->join = array(SFFORUMS . ' ON ' . SFFORUMS . '.forum_id = ' . SFPOSTS . '.forum_id', SFGROUPS . ' ON ' . SFGROUPS . '.group_id = ' . SFFORUMS . '.group_id', SFTOPICS . ' ON ' . SFTOPICS . '.topic_id = ' . SFPOSTS . '.topic_id');
        $spdb->left_join = array(SFMEMBERS . ' ON ' . SFMEMBERS . '.user_id = ' . SFPOSTS . '.user_id');
        $spdb->where = $where;
        $spdb->orderby = $order;
        if ($count) {
            $spdb->limits = $count;
        }
        $spdb = apply_filters('sph_post_list_query', $spdb, $this, $type);
        if (!empty($spThisUser->inspect['q_spPostListView'])) {
            $spdb->inspect = 'spPostListView';
            $spdb->show = true;
        }
        $records = $spdb->select();
        # Now check authorisations and clean up the object
        $list = array();
        # Some values we need
        # How many topics to a page?
        $ppaged = $spGlobals['display']['posts']['perpage'];
        if (empty($ppaged) || $ppaged == 0) {
            $ppaged = 20;
        }
        # establish topic sort order
        $porder = 'ASC';
        # default
        if ($spGlobals['display']['posts']['sortdesc']) {
            $porder = 'DESC';
        }
        # global override
        if ($records) {
            $listPos = 1;
            foreach ($records as $r) {
                if (sp_can_view($r->forum_id, 'forum-title')) {
                    if ($r->post_status == 0 || sp_get_auth('moderate_posts', $r->forum_id)) {
                        $p = $r->post_id;
                        $list[$p] = $r;
                        # Now apply any necessary filters and data changes
                        $list[$p]->post_content = sp_filter_content_display($r->post_content);
                        $list[$p]->post_content_raw = $r->post_content;
                        $list[$p]->forum_name = sp_filter_title_display($r->forum_name);
                        $list[$p]->forum_disabled = $r->forum_disabled;
                        $list[$p]->forum_permalink = sp_build_url($r->forum_slug, '', 1, 0);
                        $list[$p]->topic_permalink = sp_build_url($r->forum_slug, $r->topic_slug, 1, 0);
                        $list[$p]->topic_name = sp_filter_title_display($r->topic_name);
                        $list[$p]->topic_opened = $r->topic_opened;
                        $list[$p]->group_name = sp_filter_title_display($r->group_name);
                        if (sp_can_view($r->forum_id, 'post-content', $spThisUser->ID, $r->user_id, $r->topic_id, $r->post_id)) {
                            $list[$p]->post_tip = $r->post_status ? sp_text('Post awaiting moderation') : sp_filter_tooltip_display($r->post_content, $r->post_status);
                        } else {
                            $list[$p]->post_tip = '';
                        }
                        # Ensure display name is populated
                        if (empty($r->display_name)) {
                            $list[$p]->display_name = $list[$p]->guest_name;
                        }
                        $list[$p]->display_name = sp_filter_name_display($list[$p]->display_name);
                        # determine the page for the post permalink
                        if ($porder == 'ASC') {
                            $page = $r->post_index / $ppaged;
                            if (!is_int($page)) {
                                $page = intval($page + 1);
                            }
                        } else {
                            $page = $r->post_count - $r->post_index;
                            $page = $page / $ppaged;
                            $page = intval($page + 1);
                        }
                        $list[$p]->post_permalink = sp_build_url($r->forum_slug, $r->topic_slug, $page, $r->post_id, $r->post_index);
                        $list[$p]->list_position = $listPos;
                        $list[$p] = apply_filters('sph_post_list_record', $list[$p], $r, $type);
                    }
                }
                $listPos++;
            }
        }
        return $list;
    }
 function sp_listview_populate_firstposts($topicIds)
 {
     $first = array();
     $spdb = new spdbComplex();
     $spdb->table = SFPOSTS;
     $spdb->fields = 'post_id, topic_id, ' . spdb_zone_datetime('post_date') . ', ' . SFPOSTS . '.user_id, guest_name, post_content, post_status, display_name';
     $spdb->left_join = array(SFMEMBERS . ' ON ' . SFMEMBERS . '.user_id = ' . SFPOSTS . '.user_id');
     $spdb->where = 'topic_id IN (' . implode(',', $topicIds) . ') AND post_index=1';
     $spdb = apply_filters('sph_listview_firstposts_query', $spdb, $this);
     $postrecords = $spdb->select();
     if ($postrecords) {
         foreach ($postrecords as $p) {
             $cTopic = $p->topic_id;
             $first[$cTopic] = new stdClass();
             $first[$cTopic]->topic_id = $cTopic;
             $first[$cTopic]->post_id = $p->post_id;
             $first[$cTopic]->post_date = $p->post_date;
             $first[$cTopic]->user_id = $p->user_id;
             $first[$cTopic]->post_status = $p->post_status;
             $first[$cTopic]->display_name = $p->display_name;
             $first[$cTopic]->guest_name = $p->guest_name;
             $first[$cTopic]->post_content = $p->post_content;
         }
     }
     return $first;
 }
function sp_do_sp_AuthorPostsTag($args = '')
{
    #check if forum displayed
    if (sp_abort_display_forum()) {
        return;
    }
    $defs = array('tagId' => 'spAuthorPostsTag', 'tagClass' => 'spLinkTag', 'authorId' => '', 'showForum' => 1, 'showDate' => 1, 'limit' => 5, 'listTags' => 0, 'echo' => 1);
    $a = wp_parse_args($args, $defs);
    $a = apply_filters('sph_AuthorPostsTag_args', $a);
    extract($a, EXTR_SKIP);
    # sanitize before use
    $tagId = esc_attr($tagId);
    $tagClass = esc_attr($tagClass);
    $authorId = (int) $authorId;
    $showForum = (int) $showForum;
    $showDate = (int) $showDate;
    $limit = (int) $limit;
    $listTags = (int) $listTags;
    $echo = (int) $echo;
    if (empty($authorId)) {
        return;
    }
    sp_forum_api_support();
    if ($limit == 0) {
        $limit = '';
    }
    # limit to viewable forums based on permissions
    $where = SFPOSTS . '.user_id = ' . $authorId . ' AND ' . SFPOSTS . '.post_status=0 ';
    $forum_ids = sp_get_forum_memberships();
    # create where clause based on forums that current user can view
    if ($forum_ids != '') {
        $where .= "AND " . SFPOSTS . ".forum_id IN (" . implode(",", $forum_ids) . ")";
    } else {
        return '';
    }
    $spdb = new spdbComplex();
    $spdb->table = SFPOSTS;
    $spdb->distinct = true;
    $spdb->fields = SFPOSTS . '.post_id, ' . SFPOSTS . '.forum_id, ' . SFPOSTS . '.topic_id, ' . spdb_zone_datetime('post_date') . ',
							  post_index, forum_slug, forum_name, topic_slug, topic_name';
    $spdb->join = array(SFTOPICS . ' ON ' . SFPOSTS . '.topic_id = ' . SFTOPICS . '.topic_id', SFFORUMS . ' ON ' . SFPOSTS . '.forum_id = ' . SFFORUMS . '.forum_id');
    $spdb->where = $where;
    $spdb->orderby = 'post_date DESC';
    $spdb->limits = $limit;
    $sfposts = $spdb->select();
    if (!$listTags) {
        $out = "<div id='{$tagId}' class='{$tagClass}'>";
        $open = '<div>';
        $close = '</div>';
    } else {
        $out = "<ul id='{$tagId}' class='{$tagClass}'>";
        $open = '<li>';
        $close = '</li>';
    }
    if ($sfposts) {
        foreach ($sfposts as $sfpost) {
            $out .= $open;
            if ($showForum) {
                $out .= sp_filter_title_display($sfpost->forum_name) . '<br />';
            }
            $out .= '<a href="' . sp_build_url($sfpost->forum_slug, $sfpost->topic_slug, 0, $sfpost->post_id, $sfpost->post_index) . '">' . sp_filter_title_display($sfpost->topic_name) . '</a><br />' . "\n";
            if ($showDate) {
                $out .= sp_date('d', $sfpost->post_date) . '<br />';
            }
            $out .= $close;
        }
    } else {
        $out .= $open . __('No posts by this author', 'sp-ttags') . $close;
    }
    if (!$listTags) {
        $out .= '</div>';
    } else {
        $out .= '</ul>';
    }
    $out = apply_filters('sph_AuthorPostsTag', $out);
    if ($echo) {
        echo $out;
    } else {
        return $out;
    }
}