<?php /*

 Composr
 Copyright (c) ocProducts, 2004-2016

 See text/EN/licence.txt for full licencing information.


 NOTE TO PROGRAMMERS:
   Do not edit this file. If you need to make changes, save your changed file to the appropriate *_custom folder
   **** If you ignore this advice, then your website upgrades (e.g. for bug fixes) will likely kill your changes ****

*/

/**
 * @license    http://opensource.org/licenses/cpal_1.0 Common Public Attribution License
 * @copyright  ocProducts Ltd
 * @package    core_forum_drivers
 */

/**
 * Base class for VB forum drivers.
 *
 * @package    core_forum_drivers
 */

/**
 * Forum driver class.
 *
 * @package    core_forum_drivers
 */
class Forum_driver_vb_shared extends Forum_driver_base
{
    /**
     * Check the connected DB is valid for this forum driver.
     *
     * @return boolean Whether it is valid
     */
    public function check_db()
    {
        $test = $this->connection->query('SELECT COUNT(*) FROM ' . $this->connection->get_table_prefix() . 'user', null, null, true);
        return !is_null($test);
    }

    /**
     * Get the rows for the top given number of posters on the forum.
     *
     * @param  integer $limit The limit to the number of top posters to fetch
     * @return array The rows for the given number of top posters in the forum
     */
    public function get_top_posters($limit)
    {
        return $this->connection->query('SELECT * FROM ' . $this->connection->get_table_prefix() . 'user WHERE userid<>' . strval($this->get_guest_id()) . ' ORDER BY posts DESC', $limit);
    }

    /**
     * Attempt to to find the member's language from their forum profile. It converts between language-identifiers using a map (lang/map.ini).
     *
     * @param  MEMBER $member The member who's language needs to be fetched
     * @return ?LANGUAGE_NAME The member's language (null: unknown)
     */
    public function forum_get_lang($member)
    {
        return null;
    }

    /**
     * Find if the login cookie contains the login name instead of the member ID.
     *
     * @return boolean Whether the login cookie contains a login name or a member ID
     */
    public function is_cookie_login_name()
    {
        return false;
    }

    /**
     * Find the member ID of the forum guest member.
     *
     * @return MEMBER The member ID of the forum guest member
     */
    public function get_guest_id()
    {
        return 0;
    }

    /**
     * Add the specified custom field to the forum (some forums implemented this using proper custom profile fields, others through adding a new field).
     *
     * @param  string $name The name of the new custom field
     * @param  integer $length The length of the new custom field
     * @param  BINARY $locked Whether the field is locked
     * @param  BINARY $viewable Whether the field is for viewing
     * @param  BINARY $settable Whether the field is for setting
     * @param  BINARY $required Whether the field is required
     * @return boolean Whether the custom field was created successfully
     */
    public function install_create_custom_field($name, $length, $locked = 1, $viewable = 0, $settable = 0, $required = 0)
    {
        if (!array_key_exists('vb_table_prefix', $_POST)) {
            $_POST['vb_table_prefix'] = ''; // for now
        }

        $name = 'cms_' . $name;
        if ((!isset($GLOBALS['SITE_INFO']['vb_version'])) || ($GLOBALS['SITE_INFO']['vb_version'] >= 3.6)) {
            $r = $this->connection->query('SELECT f.profilefieldid FROM ' . $this->connection->get_table_prefix() . 'profilefield f LEFT JOIN ' . $this->connection->get_table_prefix() . 'phrase p ON (' . db_string_equal_to('product', 'vbulletin') . ' AND p.varname=' . db_function('CONCAT', array('\'field\'', 'f.profilefieldid', '\'_title\'')) . ') WHERE ' . db_string_equal_to('p.text', $name));
        } else {
            $r = $this->connection->query('SELECT profilefieldid FROM ' . $_POST['vb_table_prefix'] . 'profilefield WHERE ' . db_string_equal_to('title', $name) . '\'');
        }

        if (!array_key_exists(0, $r)) {
            if ((!isset($GLOBALS['SITE_INFO']['vb_version'])) || ($GLOBALS['SITE_INFO']['vb_version'] >= 3.6)) {
                $key = $this->connection->query_insert('profilefield', array('required' => $required, 'hidden' => 1 - $viewable, 'maxlength' => $length, 'size' => $length, 'editable' => $settable), true);
                $this->connection->query_insert('phrase', array('languageid' => 0, 'varname' => 'field' . strval($key) . '_title', 'fieldname' => 'cprofilefield', 'text' => $name, 'product' => 'vbulletin', 'username' => '', 'dateline' => 0, 'version' => ''));
            } else {
                $this->connection->query('INSERT INTO ' . $_POST['vb_table_prefix'] . 'profilefield (title,description,required,hidden,maxlength,size,editable) VALUES (\'' . db_escape_string($name) . '\',\'\',' . strval(intval($required)) . ',' . strval(intval(1 - $viewable)) . ',\'' . strval($length) . '\',\'' . strval($length) . '\',' . strval(intval($settable)) . ')');
                $_key = $this->connection->query('SELECT MAX(profilefieldid) AS v FROM ' . $_POST['vb_table_prefix'] . 'profilefield');
                $key = $_key[0]['v'];
            }
            $this->connection->query('ALTER TABLE ' . $_POST['vb_table_prefix'] . 'userfield ADD field' . strval($key) . ' TEXT', null, null, true);
            return true;
        }
        return false;
    }

    /**
     * Edit a custom field
     *
     * @param  string $old_name The name of the current custom field
     * @param  string $new_name The new name of the custom field (blank: do not rename)
     * @param  integer $length The length of the new custom field
     * @param  BINARY $locked Whether the field is locked
     * @param  BINARY $viewable Whether the field is for viewing
     * @param  BINARY $settable Whether the field is for setting
     * @param  BINARY $required Whether the field is required
     * @return boolean Whether the custom field was created successfully
     */
    public function install_edit_custom_field($old_name, $new_name, $length, $locked = 1, $viewable = 0, $settable = 0, $required = 0)
    {
        if (!array_key_exists('vb_table_prefix', $_POST)) {
            $_POST['vb_table_prefix'] = ''; // for now
        }

        $old_name = 'cms_' . $old_name;
        if ($new_name != '') {
            $new_name = 'cms_' . $new_name;
        } else {
            $new_name = 'cms_' . $old_name;
        }

        if ((!isset($GLOBALS['SITE_INFO']['vb_version'])) || ($GLOBALS['SITE_INFO']['vb_version'] >= 3.6)) {
            $r = $this->connection->query('SELECT f.profilefieldid FROM ' . $this->connection->get_table_prefix() . 'profilefield f LEFT JOIN ' . $this->connection->get_table_prefix() . 'phrase p ON (' . db_string_equal_to('product', 'vbulletin') . ' AND p.varname=' . db_function('CONCAT', array('\'field\'', 'f.profilefieldid', '\'_title\'')) . ') WHERE ' . db_string_equal_to('p.text', $old_name));
        } else {
            $r = $this->connection->query('SELECT profilefieldid FROM ' . $_POST['vb_table_prefix'] . 'profilefield WHERE ' . db_string_equal_to('title', $old_name) . '\'');
        }

        if (array_key_exists(0, $r)) {
            if ((!isset($GLOBALS['SITE_INFO']['vb_version'])) || ($GLOBALS['SITE_INFO']['vb_version'] >= 3.6)) {
                $this->connection->query_update('profilefield', array('required' => $required, 'hidden' => 1 - $viewable, 'maxlength' => $length, 'size' => $length, 'editable' => $settable), array('profilefieldid' => $r[0]['profilefieldid']), true);
                $this->connection->query_update('phrase', array('text' => $new_name), array('fieldname' => 'cprofilefield', 'varname' => 'field' . strval($r[0]['profilefieldid']) . '_title'));
            } else {
                $this->connection->query('UPDATE ' . $_POST['vb_table_prefix'] . 'profilefield SET title=\'' . db_escape_string($new_name) . '\', required=' . strval(intval($required)) . ', hidden=' . strval(intval(1 - $viewable)) . ', maxlength=\'' . strval($length) . '\', size=\'' . strval($length) . '\', editable=' . strval(intval($settable)) . ' WHERE profilefieldid=' . strval($r['profilefieldid']));
            }
            return true;
        }
        return false;
    }

    /**
     * Get the forums' table prefix for the database.
     *
     * @return string The forum database table prefix
     */
    public function get_drivered_table_prefix()
    {
        global $SITE_INFO;
        return $SITE_INFO['vb_table_prefix'];
    }

    /**
     * Get an emoticon chooser template.
     *
     * @param  string $field_name The ID of the form field the emoticon chooser adds to
     * @return Tempcode The emoticon chooser template
     */
    public function get_emoticon_chooser($field_name = 'post')
    {
        require_code('comcode_compiler');
        $emoticons = $this->connection->query_select('smilie', array('*'));
        $em = new Tempcode();
        foreach ($emoticons as $emo) {
            $code = $emo['smilietext'];
            $em->attach(do_template('EMOTICON_CLICK_CODE', array('_GUID' => 'a9dabba90bc5e781de02ab57ebfc6e8d', 'FIELD_NAME' => $field_name, 'CODE' => $code, 'IMAGE' => apply_emoticons($code))));
        }

        return $em;
    }

    /**
     * Pin a topic.
     *
     * @param  AUTO_LINK $id The topic ID
     * @param  boolean $pin True: pin it, False: unpin it
     */
    public function pin_topic($id, $pin = true)
    {
        $this->connection->query_update('threads', array('sticky' => $pin ? 1 : 0), array('threadid' => $id), '', 1);
    }

    /**
     * Get a member row for the member of the given name.
     *
     * @param  SHORT_TEXT $name The member name
     * @return ?array The profile-row (null: could not find)
     */
    public function get_mrow($name)
    {
        $rows = $this->connection->query_select('user', array('*'), array('username' => $name), '', 1);
        if (!array_key_exists(0, $rows)) {
            return null;
        }
        return $rows[0];
    }

    /**
     * From a member row, get the member's primary usergroup.
     *
     * @param  array $r The profile-row
     * @return GROUP The member's primary usergroup
     */
    public function mrow_group($r)
    {
        return $r['usergroupid'];
    }

    /**
     * From a member row, get the member's member ID.
     *
     * @param  array $r The profile-row
     * @return MEMBER The member ID
     */
    public function mrow_id($r)
    {
        return $r['userid'];
    }

    /**
     * From a member row, get the member's name.
     *
     * @param  array $r The profile-row
     * @return string The member name
     */
    public function mrow_username($r)
    {
        return $r['username'];
    }

    /**
     * From a member row, get the member's e-mail address.
     *
     * @param  array $r The profile-row
     * @return SHORT_TEXT The member e-mail address
     */
    public function mrow_email($r)
    {
        return $r['email'];
    }

    /**
     * Get a URL to the specified member's home (control panel).
     *
     * @param  MEMBER $id The member ID
     * @return URLPATH The URL to the members home
     */
    public function member_home_url($id)
    {
        return get_forum_base_url() . '/usercp.php';
    }

    /**
     * Get the photo thumbnail URL for the specified member ID.
     *
     * @param  MEMBER $member The member ID
     * @return URLPATH The URL (blank: none)
     */
    public function get_member_photo_url($member)
    {
        return get_forum_base_url() . '/image.php?u=' . strval($member) . '&type=profile';
    }

    /**
     * Get the avatar URL for the specified member ID.
     *
     * @param  MEMBER $member The member ID
     * @return URLPATH The URL (blank: none)
     */
    public function get_member_avatar_url($member)
    {
        return get_forum_base_url() . '/image.php?u=' . strval($member);
    }

    /**
     * Get a URL to the specified member's profile.
     *
     * @param  MEMBER $id The member ID
     * @return URLPATH The URL to the member profile
     */
    protected function _member_profile_url($id)
    {
        return get_forum_base_url() . '/member.php?action=getinfo&userid=' . strval($id);
    }

    /**
     * Get a URL to the registration page (for people to create member accounts).
     *
     * @return URLPATH The URL to the registration page
     */
    protected function _join_url()
    {
        return get_forum_base_url() . '/register.php?action=signup';
    }

    /**
     * Get a URL to the members-online page.
     *
     * @return URLPATH The URL to the members-online page
     */
    protected function _users_online_url()
    {
        return get_forum_base_url() . '/online.php';
    }

    /**
     * Get a URL to send a private/personal message to the given member.
     *
     * @param  MEMBER $id The member ID
     * @return URLPATH The URL to the private/personal message page
     */
    protected function _member_pm_url($id)
    {
        return get_forum_base_url() . '/private.php?action=newmessage&userid=' . strval($id);
    }

    /**
     * Get a URL to the specified forum.
     *
     * @param  integer $id The forum ID
     * @return URLPATH The URL to the specified forum
     */
    protected function _forum_url($id)
    {
        return get_forum_base_url() . '/forumdisplay.php?forumid=' . strval($id);
    }

    /**
     * Get the forum ID from a forum name.
     *
     * @param  SHORT_TEXT $forum_name The forum name
     * @return ?integer The forum ID (null: not found)
     */
    public function forum_id_from_name($forum_name)
    {
        return is_numeric($forum_name) ? intval($forum_name) : $this->connection->query_select_value_if_there('forum', 'forumid', array('title' => $forum_name));
    }

    /**
     * Get the topic ID from a topic identifier in the specified forum. It is used by comment topics, which means that the unique-topic-name assumption holds valid.
     *
     * @param  string $forum The forum name / ID
     * @param  SHORT_TEXT $topic_identifier The topic identifier
     * @return ?integer The topic ID (null: not found)
     */
    public function find_topic_id_for_topic_identifier($forum, $topic_identifier)
    {
        if (is_integer($forum)) {
            $forum_id = $forum;
        } else {
            $forum_id = $this->forum_id_from_name($forum);
        }
        return $this->connection->query_value_if_there('SELECT threadid FROM ' . $this->connection->get_table_prefix() . 'thread WHERE forumid=' . strval($forum_id) . ' AND (' . db_string_equal_to('title', $topic_identifier) . ' OR title LIKE \'%: #' . db_encode_like($topic_identifier) . '\')');
    }

    /**
     * Makes a post in the specified forum, in the specified topic according to the given specifications. If the topic doesn't exist, it is created along with a spacer-post.
     * Spacer posts exist in order to allow staff to delete the first true post in a topic. Without spacers, this would not be possible with most forum systems. They also serve to provide meta information on the topic that cannot be encoded in the title (such as a link to the content being commented upon).
     *
     * @param  SHORT_TEXT $forum_name The forum name
     * @param  SHORT_TEXT $topic_identifier The topic identifier (usually <content-type>_<content-id>)
     * @param  MEMBER $member The member ID
     * @param  LONG_TEXT $post_title The post title
     * @param  LONG_TEXT $post The post content in Comcode format
     * @param  string $content_title The topic title; must be same as content title if this is for a comment topic
     * @param  string $topic_identifier_encapsulation_prefix This is put together with the topic identifier to make a more-human-readable topic title or topic description (hopefully the latter and a $content_title title, but only if the forum supports descriptions)
     * @param  ?URLPATH $content_url URL to the content (null: do not make spacer post)
     * @param  ?TIME $time The post time (null: use current time)
     * @param  ?IP $ip The post IP address (null: use current members IP address)
     * @param  ?BINARY $validated Whether the post is validated (null: unknown, find whether it needs to be marked unvalidated initially). This only works with the Conversr driver.
     * @param  ?BINARY $topic_validated Whether the topic is validated (null: unknown, find whether it needs to be marked unvalidated initially). This only works with the Conversr driver.
     * @param  boolean $skip_post_checks Whether to skip post checks
     * @param  SHORT_TEXT $poster_name_if_guest The name of the poster
     * @param  ?AUTO_LINK $parent_id ID of post being replied to (null: N/A)
     * @param  boolean $staff_only Whether the reply is only visible to staff
     * @return array Topic ID (may be null), and whether a hidden post has been made
     */
    public function make_post_forum_topic($forum_name, $topic_identifier, $member, $post_title, $post, $content_title, $topic_identifier_encapsulation_prefix, $content_url = null, $time = null, $ip = null, $validated = null, $topic_validated = 1, $skip_post_checks = false, $poster_name_if_guest = '', $parent_id = null, $staff_only = false)
    {
        if (is_null($time)) {
            $time = time();
        }
        if (is_null($ip)) {
            $ip = get_ip_address();
        }
        $forum_id = $this->forum_id_from_name($forum_name);
        if (is_null($forum_id)) {
            warn_exit(do_lang_tempcode('MISSING_FORUM', escape_html($forum_name)));
        }
        $username = $this->get_username($member);
        $topic_id = $this->find_topic_id_for_topic_identifier($forum_name, $topic_identifier);
        $is_new = is_null($topic_id);
        if ($is_new) {
            $topic_id = $this->connection->query_insert('thread', array('title' => $content_title . ', ' . $topic_identifier_encapsulation_prefix . ': #' . $topic_identifier, 'lastpost' => $time, 'forumid' => $forum_id, 'open' => 1, 'postusername' => $username, 'postuserid' => $member, 'lastposter' => $username, 'dateline' => $time, 'visible' => 1), true);
            $home_link = hyperlink($content_url, $content_title, false, true);
            $this->connection->query_insert('post', array('threadid' => $topic_id, 'username' => do_lang('SYSTEM', '', '', '', get_site_default_lang()), 'userid' => 0, 'title' => '', 'dateline' => $time, 'pagetext' => do_lang('SPACER_POST', $home_link->evaluate(), '', '', get_site_default_lang()), 'allowsmilie' => 1, 'ipaddress' => '127.0.0.1', 'visible' => 1));
            $this->connection->query('UPDATE ' . $this->connection->get_table_prefix() . 'forum SET threadcount=(threadcount+1) WHERE forumid=' . strval($forum_id), 1);
        }

        $GLOBALS['LAST_TOPIC_ID'] = $topic_id;
        $GLOBALS['LAST_TOPIC_IS_NEW'] = $is_new;

        if ($post == '') {
            return array($topic_id, false);
        }

        $last_post_id = $this->connection->query_insert('post', array('threadid' => $topic_id, 'username' => $username, 'userid' => $member, 'title' => $post_title, 'dateline' => $time, 'pagetext' => $post, 'allowsmilie' => 1, 'ipaddress' => $ip, 'visible' => 1), true);
        $this->connection->query('UPDATE ' . $this->connection->get_table_prefix() . 'forum SET replycount=(replycount+1), lastpost=' . strval($time) . ', lastposter=\'' . db_escape_string($username) . '\' WHERE forumid=' . strval($forum_id), 1);
        if ((!isset($GLOBALS['SITE_INFO']['vb_version'])) || ($GLOBALS['SITE_INFO']['vb_version'] >= 3.6)) {
            $this->connection->query('UPDATE ' . $this->connection->get_table_prefix() . 'thread SET lastpostid=' . strval($last_post_id) . ', replycount=(replycount+1), lastpost=' . strval($time) . ', lastposter=\'' . db_escape_string($username) . '\' WHERE threadid=' . strval($topic_id), 1);
        } else {
            $this->connection->query('UPDATE ' . $this->connection->get_table_prefix() . 'thread SET replycount=(replycount+1), lastpost=' . strval($time) . ', lastposter=\'' . db_escape_string($username) . '\' WHERE threadid=' . strval($topic_id), 1);
        }

        return array($topic_id, false);
    }

    /**
     * Get an array of maps for the topic in the given forum.
     *
     * @param  integer $topic_id The topic ID
     * @param  integer $count The comment count will be returned here by reference
     * @param  integer $max Maximum comments to returned
     * @param  integer $start Comment to start at
     * @param  boolean $mark_read Whether to mark the topic read (ignored for this forum driver)
     * @param  boolean $reverse Whether to show in reverse
     * @return mixed The array of maps (Each map is: title, message, member, date) (-1 for no such forum, -2 for no such topic)
     */
    public function get_forum_topic_posts($topic_id, &$count, $max = 100, $start = 0, $mark_read = true, $reverse = false)
    {
        if (is_null($topic_id)) {
            return (-2);
        }
        $order = $reverse ? 'dateline DESC' : 'dateline';
        $rows = $this->connection->query('SELECT * FROM ' . $this->connection->get_table_prefix() . 'post WHERE threadid=' . strval($topic_id) . ' AND pagetext NOT LIKE \'' . db_encode_like(substr(do_lang('SPACER_POST', '', '', '', get_site_default_lang()), 0, 20) . '%') . '\' ORDER BY ' . $order, $max, $start);
        $count = $this->connection->query_value_if_there('SELECT COUNT(*) FROM ' . $this->connection->get_table_prefix() . 'post WHERE threadid=' . strval($topic_id) . ' AND pagetext NOT LIKE \'' . db_encode_like(substr(do_lang('SPACER_POST', '', '', '', get_site_default_lang()), 0, 20) . '%') . '\'');
        $out = array();
        foreach ($rows as $myrow) {
            $temp = array();
            $temp['title'] = $myrow['title'];
            global $LAX_COMCODE;
            $temp2 = $LAX_COMCODE;
            $LAX_COMCODE = true;
            $temp['message'] = comcode_to_tempcode(@html_entity_decode($myrow['pagetext'], ENT_QUOTES, get_charset()), $myrow['userid']);
            $LAX_COMCODE = $temp2;
            $temp['member'] = $myrow['userid'];
            $temp['date'] = $myrow['dateline'];

            $out[] = $temp;
        }

        return $out;
    }

    /**
     * Get a URL to the specified topic ID. Most forums don't require the second parameter, but some do, so it is required in the interface.
     *
     * @param  integer $id The topic ID
     * @param  string $forum The forum ID
     * @return URLPATH The URL to the topic
     */
    public function topic_url($id, $forum)
    {
        return get_forum_base_url() . '/showthread.php?threadid=' . strval($id);
    }

    /**
     * Get a URL to the specified post ID.
     *
     * @param  integer $id The post ID
     * @param  string $forum The forum ID
     * @return URLPATH The URL to the post
     */
    public function post_url($id, $forum)
    {
        return get_forum_base_url() . '/showpost.php?p=' . strval($id);
    }

    /**
     * Get an array of topics in the given forum. Each topic is an array with the following attributes:
     * - id, the topic ID
     * - title, the topic title
     * - lastusername, the username of the last poster
     * - lasttime, the timestamp of the last reply
     * - closed, a Boolean for whether the topic is currently closed or not
     * - firsttitle, the title of the first post
     * - firstpost, the first post (only set if $show_first_posts was true)
     *
     * @param  mixed $name The forum name or an array of forum IDs
     * @param  integer $limit The limit
     * @param  integer $start The start position
     * @param  integer $max_rows The total rows (not a parameter: returns by reference)
     * @param  SHORT_TEXT $filter_topic_title The topic title filter
     * @param  boolean $show_first_posts Whether to show the first posts
     * @param  string $date_key The date key to sort by
     * @set    lasttime firsttime
     * @param  boolean $hot Whether to limit to hot topics
     * @param  SHORT_TEXT $filter_topic_description The topic description filter
     * @return ?array The array of topics (null: error)
     */
    public function show_forum_topics($name, $limit, $start, &$max_rows, $filter_topic_title = '', $show_first_posts = false, $date_key = 'lasttime', $hot = false, $filter_topic_description = '')
    {
        if (is_integer($name)) {
            $id_list = 'forumid=' . strval($name);
        } elseif (!is_array($name)) {
            $id = $this->forum_id_from_name($name);
            if (is_null($id)) {
                return null;
            }
            $id_list = 'forumid=' . strval($id);
        } else {
            $id_list = '';
            foreach (array_keys($name) as $id) {
                if ($id_list != '') {
                    $id_list .= ' OR ';
                }
                $id_list .= 'forumid=' . strval($id);
            }
            if ($id_list == '') {
                return null;
            }
        }

        $topic_filter = ($filter_topic_title != '') ? ('AND title LIKE \'' . db_encode_like($filter_topic_title) . '\'') : '';
        $rows = $this->connection->query('SELECT * FROM ' . $this->connection->get_table_prefix() . 'thread WHERE (' . $id_list . ') ' . $topic_filter . ' ORDER BY ' . (($date_key == 'lastpost') ? 'last_post' : 'dateline') . ' DESC', $limit, $start);
        $max_rows = $this->connection->query_value_if_there('SELECT COUNT(*) FROM ' . $this->connection->get_table_prefix() . 'thread WHERE (' . $id_list . ') ' . $topic_filter);
        $out = array();
        foreach ($rows as $i => $r) {
            $out[$i] = array();
            $out[$i]['id'] = $r['threadid'];
            $out[$i]['num'] = $r['replycount'] + 1;
            $out[$i]['title'] = $r['title'];
            $out[$i]['description'] = $r['title'];
            $out[$i]['firstusername'] = $r['postusername'];
            $out[$i]['lastusername'] = $r['lastposter'];
            $out[$i]['firstmemberid'] = $r['postuserid'];
            $out[$i]['firsttime'] = $r['dateline'];
            $out[$i]['lasttime'] = $r['lastpost'];
            $out[$i]['closed'] = ($r['open'] == 0);
            $fp_rows = $this->connection->query('SELECT title,pagetext,userid FROM ' . $this->connection->get_table_prefix() . 'post WHERE pagetext NOT LIKE \'' . db_encode_like(do_lang('SPACER_POST', '', '', '', get_site_default_lang()) . '%') . '\' AND threadid=' . strval($out[$i]['id']) . ' ORDER BY dateline', 1);
            if (!array_key_exists(0, $fp_rows)) {
                unset($out[$i]);
                continue;
            }
            $out[$i]['firsttitle'] = $fp_rows[0]['title'];
            if ($show_first_posts) {
                global $LAX_COMCODE;
                $temp = $LAX_COMCODE;
                $LAX_COMCODE = true;
                $out[$i]['firstpost'] = comcode_to_tempcode(@html_entity_decode($fp_rows[0]['pagetext'], ENT_QUOTES, get_charset()), $fp_rows[0]['userid']);
                $LAX_COMCODE = $temp;
            }
            $fp_rows = $this->connection->query('SELECT title,pagetext,userid FROM ' . $this->connection->get_table_prefix() . 'post WHERE pagetext NOT LIKE \'' . db_encode_like(do_lang('SPACER_POST', '', '', '', get_site_default_lang()) . '%') . '\' AND threadid=' . strval($out[$i]['id']) . ' ORDER BY dateline DESC', 1);
            $out[$i]['lastmemberid'] = $fp_rows[0]['userid'];
        }
        if (count($out) != 0) {
            return $out;
        }
        return null;
    }

    /**
     * Get an array of members who are in at least one of the given array of usergroups.
     *
     * @param  array $groups The array of usergroups
     * @param  ?integer $max Return up to this many entries for primary members and this many entries for secondary members (null: no limit, only use no limit if querying very restricted usergroups!)
     * @param  integer $start Return primary members after this offset and secondary members after this offset
     * @return ?array The array of members (null: no members)
     */
    public function member_group_query($groups, $max = null, $start = 0)
    {
        $_groups = '';
        foreach ($groups as $group) {
            if ($_groups != '') {
                $_groups .= ' OR ';
            }
            $_groups .= 'usergroupid=' . strval($group);
        }
        return $this->connection->query('SELECT * FROM ' . $this->connection->get_table_prefix() . 'user WHERE ' . $_groups . ' ORDER BY usergroupid,userid ASC', $max, $start);
    }

    /**
     * This is the opposite of the get_next_member function.
     *
     * @param  MEMBER $member The member ID to decrement
     * @return ?MEMBER The previous member ID (null: no previous member)
     */
    public function get_previous_member($member)
    {
        $tempid = $this->connection->query_value_if_there('SELECT userid FROM ' . $this->connection->get_table_prefix() . 'user WHERE userid<' . strval($member) . ' AND userid<>0 ORDER BY userid DESC');
        return $tempid;
    }

    /**
     * Get the member ID of the next member after the given one, or null.
     * It cannot be assumed there are no gaps in member IDs, as members may be deleted.
     *
     * @param  MEMBER $member The member ID to increment
     * @return ?MEMBER The next member ID (null: no next member)
     */
    public function get_next_member($member)
    {
        $tempid = $this->connection->query_value_if_there('SELECT userid FROM ' . $this->connection->get_table_prefix() . 'user WHERE userid>' . strval($member) . ' ORDER BY userid');
        return $tempid;
    }

    /**
     * Try to find a member with the given IP address
     *
     * @param  IP $ip The IP address
     * @return array The distinct rows found
     */
    public function probe_ip($ip)
    {
        $a = $this->connection->query_select('user', array('DISTINCT userid AS id'), array('ipaddress' => $ip));
        $b = $this->connection->query_select('post', array('DISTINCT userid AS id'), array('ipaddress' => $ip));
        return array_merge($a, $b);
    }

    /**
     * Get the name relating to the specified member ID.
     * If this returns null, then the member has been deleted. Always take potential null output into account.
     *
     * @param  MEMBER $member The member ID
     * @return ?SHORT_TEXT The member name (null: member deleted)
     */
    protected function _get_username($member)
    {
        if ($member == $this->get_guest_id()) {
            return do_lang('GUEST');
        }
        return $this->get_member_row_field($member, 'username');
    }

    /**
     * Get the e-mail address for the specified member ID.
     *
     * @param  MEMBER $member The member ID
     * @return SHORT_TEXT The e-mail address
     */
    protected function _get_member_email_address($member)
    {
        return $this->get_member_row_field($member, 'email');
    }

    /**
     * Find if this member may have e-mails sent to them
     *
     * @param  MEMBER $member The member ID
     * @return boolean Whether the member may have e-mails sent to them
     */
    public function get_member_email_allowed($member)
    {
        return ($this->get_member_row_field($member, 'options') & 16) != 0;
    }

    /**
     * Get the timestamp of a member's join date.
     *
     * @param  MEMBER $member The member ID
     * @return TIME The timestamp
     */
    public function get_member_join_timestamp($member)
    {
        return $this->get_member_row_field($member, 'joindate');
    }

    /**
     * Find all members with a name matching the given SQL LIKE string.
     *
     * @param  string $pattern The pattern
     * @param  ?integer $limit Maximum number to return (limits to the most recent active) (null: no limit)
     * @return ?array The array of matched members (null: none found)
     */
    public function get_matching_members($pattern, $limit = null)
    {
        $rows = $this->connection->query('SELECT * FROM ' . $this->connection->get_table_prefix() . 'user WHERE username LIKE \'' . db_encode_like($pattern) . '\' AND userid<>' . strval($this->get_guest_id()) . ' ORDER BY lastactivity DESC', $limit);
        sort_maps_by($rows, 'username');
        return $rows;
    }

    /**
     * Get the given member's post count.
     *
     * @param  MEMBER $member The member ID
     * @return integer The post count
     */
    public function get_post_count($member)
    {
        $c = $this->get_member_row_field($member, 'posts');
        if (is_null($c)) {
            return 0;
        }
        return $c;
    }

    /**
     * Get the given member's topic count.
     *
     * @param  MEMBER $member The member ID
     * @return integer The topic count
     */
    public function get_topic_count($member)
    {
        return $this->connection->query_select_value('thread', 'COUNT(*)', array('postuserid' => $member));
    }

    /**
     * Find the base URL to the emoticons.
     *
     * @return URLPATH The base URL
     */
    public function get_emo_dir()
    {
        return get_forum_base_url() . '/';
    }

    /**
     * Get a map between emoticon codes and templates representing the HTML-image-code for this emoticon. The emoticons presented of course depend on the forum involved.
     *
     * @return array The map
     */
    public function find_emoticons()
    {
        if (!is_null($this->EMOTICON_CACHE)) {
            return $this->EMOTICON_CACHE;
        }
        $this->EMOTICON_CACHE = array();
        $rows = $this->connection->query_select('smilie', array('*'));
        $this->EMOTICON_CACHE = array();
        foreach ($rows as $myrow) {
            $src = $myrow['smiliepath'];
            if (url_is_local($src)) {
                $src = $this->get_emo_dir() . $src;
            }
            $this->EMOTICON_CACHE[$myrow['smilietext']] = array('EMOTICON_IMG_CODE_DIR', $src, $myrow['smilietext']);
        }
        uksort($this->EMOTICON_CACHE, '_strlen_sort');
        $this->EMOTICON_CACHE = array_reverse($this->EMOTICON_CACHE);
        return $this->EMOTICON_CACHE;
    }

    /**
     * Find a list of all forum skins (aka themes).
     *
     * @return array The list of skins
     */
    public function get_skin_list()
    {
        $table = 'style';
        $codename = 'title';

        $rows = $this->connection->query_select($table, array($codename));
        return collapse_1d_complexity($codename, $rows);
    }

    /**
     * Try to find the theme that the logged-in/guest member is using, and map it to a Composr theme.
     * The themes/map.ini file functions to provide this mapping between forum themes, and Composr themes, and has a slightly different meaning for different forum drivers. For example, some drivers map the forum themes theme directory to the Composr theme name, while others made the humanly readeable name.
     *
     * @param  boolean $skip_member_specific Whether to avoid member-specific lookup (i.e. find via what forum theme is currently configured as the default)
     * @param  ?MEMBER $member The member to find for (null: current member)
     * @return ID_TEXT The theme
     */
    public function _get_theme($skip_member_specific = false, $member = null)
    {
        $def = '';

        // Load in remapper
        require_code('files');
        $map = file_exists(get_file_base() . '/themes/map.ini') ? better_parse_ini_file(get_file_base() . '/themes/map.ini') : array();

        if (!$skip_member_specific) {
            // Work out
            if ($member === null) {
                $member = get_member();
            }
            if ($member > 0) {
                $skin = $this->get_member_row_field($member, 'styleid');
            } else {
                $skin = 0;
            }
            if ($skin > 0) { // User has a custom theme
                $vb = $this->connection->query_select_value_if_there('style', 'title', array('styleid' => $skin));
                if (!is_null($vb)) {
                    $def = array_key_exists($vb, $map) ? $map[$vb] : $vb;
                }
            }
        }

        // Look for a skin according to our site name (we bother with this instead of 'default' because Composr itself likes to never choose a theme when forum-theme integration is on: all forum [via map] or all Composr seems cleaner, although it is complex)
        if ((!(strlen($def) > 0)) || (!file_exists(get_custom_file_base() . '/themes/' . $def))) {
            $vb = $this->connection->query_select_value_if_there('style', 'title', array('title' => get_site_name()));
            if (!is_null($vb)) {
                $def = array_key_exists($vb, $map) ? $map[$vb] : $vb;
            }
        }

        // Hmm, just the very-default then
        if ((!(strlen($def) > 0)) || (!file_exists(get_custom_file_base() . '/themes/' . $def))) {
            $def = array_key_exists('default', $map) ? $map['default'] : 'default';
        }

        return $def;
    }

    /**
     * Get the number of members currently online on the forums.
     *
     * @return integer The number of members
     */
    public function get_num_users_forums()
    {
        return $this->connection->query_value_if_there('SELECT COUNT(DISTINCT userid) FROM ' . $this->connection->get_table_prefix() . 'session WHERE lastactivity>' . strval(time() - 60 * intval(get_option('users_online_time'))));
    }

    /**
     * Get the number of members registered on the forum.
     *
     * @return integer The number of members
     */
    public function get_members()
    {
        return $this->connection->query_select_value('user', 'COUNT(*)');
    }

    /**
     * Get the total topics ever made on the forum.
     *
     * @return integer The number of topics
     */
    public function get_topics()
    {
        return $this->connection->query_select_value('thread', 'COUNT(*)');
    }

    /**
     * Get the total posts ever made on the forum.
     *
     * @return integer The number of posts
     */
    public function get_num_forum_posts()
    {
        return $this->connection->query_select_value('post', 'COUNT(*)');
    }

    /**
     * Get the number of new forum posts.
     *
     * @return integer The number of posts
     */
    protected function _get_num_new_forum_posts()
    {
        return $this->connection->query_value_if_there('SELECT COUNT(*) FROM ' . $this->connection->get_table_prefix() . 'post WHERE dateline>' . strval(time() - 60 * 60 * 24));
    }

    /**
     * Set a custom profile field's value, if the custom field exists. Only works on specially-named (titled) fields.
     *
     * @param  MEMBER $member The member ID
     * @param  string $field The field name (e.g. "firstname" for the CPF with a title of "cms_firstname")
     * @param  string $value The value
     */
    public function set_custom_field($member, $field, $value)
    {
        if ((!isset($GLOBALS['SITE_INFO']['vb_version'])) || ($GLOBALS['SITE_INFO']['vb_version'] >= 3.6)) {
            $id = $this->connection->query_value_if_there('SELECT f.profilefieldid FROM ' . $this->connection->get_table_prefix() . 'profilefield f LEFT JOIN ' . $this->connection->get_table_prefix() . 'phrase p ON (' . db_string_equal_to('product', 'vbulletin') . ' AND p.varname=' . db_function('CONCAT', array('\'field\'', 'f.profilefieldid', '\'_title\'')) . ') WHERE ' . db_string_equal_to('p.text', 'cms_' . $field));
        } else {
            $id = $this->connection->query_select_value_if_there('profilefield', 'profilefieldid', array('title' => 'cms_' . $field));
        }
        if (is_null($id)) {
            return;
        }
        $old = $this->connection->query_select_value_if_there('userfield', 'userid', array('userid' => $member));
        if (is_null($old)) {
            $this->connection->query_insert('userfield', array('userid' => $member));
        }
        $this->connection->query_update('userfield', array('field' . strval($id) => $value), array('userid' => $member), '', 1);
    }

    /**
     * Get custom profile fields values for all 'cms_' prefixed keys.
     *
     * @param  MEMBER $member The member ID
     * @return ?array A map of the custom profile fields, key_suffix=>value (null: no fields)
     */
    public function get_custom_fields($member)
    {
        if ((!isset($GLOBALS['SITE_INFO']['vb_version'])) || ($GLOBALS['SITE_INFO']['vb_version'] >= 3.6)) {
            $rows = $this->connection->query('SELECT f.profilefieldid,p.text AS title FROM ' . $this->connection->get_table_prefix() . 'profilefield f LEFT JOIN ' . $this->connection->get_table_prefix() . 'phrase p ON (' . db_string_equal_to('product', 'vbulletin') . ' AND p.varname=' . db_function('CONCAT', array('\'field\'', 'f.profilefieldid', '\'_title\'')) . ') WHERE p.text LIKE \'' . db_encode_like('cms\_%') . '\'');
        } else {
            $rows = $this->connection->query('SELECT profilefieldid,title FROM ' . $this->connection->get_table_prefix() . 'profilefield WHERE title LIKE \'' . db_encode_like('cms\_%') . '\'');
        }
        $values = $this->connection->query_select('userfield', array('*'), array('userid' => $member), '', 1);
        if (!array_key_exists(0, $values)) {
            return null;
        }

        $out = array();
        foreach ($rows as $row) {
            $title = substr($row['title'], 4);
            $out[$title] = $values[0]['field' . strval($row['profilefieldid'])];
        }
        return $out;
    }

    /**
     * Get a member ID from the given member's username.
     *
     * @param  SHORT_TEXT $name The member name
     * @return MEMBER The member ID
     */
    public function get_member_from_username($name)
    {
        if ($name == do_lang('GUEST')) {
            return $this->get_guest_id();
        }

        return $this->connection->query_select_value_if_there('user', 'userid', array('username' => $name));
    }

    /**
     * Get a first known IP address of the given member.
     *
     * @param  MEMBER $member The member ID
     * @return IP The IP address
     */
    public function get_member_ip($member)
    {
        return $this->get_member_row_field($member, 'ipaddress');
    }

    /**
     * Gets a whole member row from the database.
     *
     * @param  MEMBER $member The member ID
     * @return ?array The member row (null: no such member)
     */
    public function get_member_row($member)
    {
        if (array_key_exists($member, $this->MEMBER_ROWS_CACHED)) {
            return $this->MEMBER_ROWS_CACHED[$member];
        }

        $rows = $this->connection->query_select('user', array('*'), array('userid' => $member), '', 1);
        if ($member == $this->get_guest_id()) {
            $rows[0]['username'] = do_lang('GUEST');
            $rows[0]['email'] = null;
            $rows[0]['emailnotification'] = 0;
            $rows[0]['joindate'] = time();
            $rows[0]['posts'] = 0;
            $rows[0]['styleid'] = null;
            $rows[0]['usergroupid'] = 1;
        }
        if (!array_key_exists(0, $rows)) {
            return null;
        }
        $this->MEMBER_ROWS_CACHED[$member] = $rows[0];
        return $this->MEMBER_ROWS_CACHED[$member];
    }

    /**
     * Gets a named field of a member row from the database.
     *
     * @param  MEMBER $member The member ID
     * @param  string $field The field identifier
     * @return mixed The field
     */
    public function get_member_row_field($member, $field)
    {
        $row = $this->get_member_row($member);
        return is_null($row) ? null : $row[$field];
    }
}
