Location: PHPKode > projects > phlyMail Lite > phlymail/handlers/email/indexer.mysql.php
<?php
/**
 * handlers/email/index.mysql.php
 * Proivdes indexing functions for use with a mySQL database
 *
 * @package phlyMail Nahariya 4.0+
 * @subpackage handler email
 * @author Matthias Sommerfeld
 * @copyright 2004-2010 phlyLabs, Berlin (http://phlylabs.de)
 * @version 4.4.3 2010-10-31
 */
// Only valid within phlyMail
if (!defined('_IN_PHM_')) die();

class indexer {
    private $error = array();
    // Constructor
    public function __construct()
    {
        $this->DB = &$GLOBALS['DB'];
        $pref = &$this->DB->DB['db_pref'];
        $this->DB->Tbl['email_filter'] = $pref.'email_filters';
        $this->DB->Tbl['email_filter_rule'] = $pref.'email_filterrules';
        $this->DB->Tbl['email_folder'] = $pref.'email_folders';
        $this->DB->Tbl['email_index'] = $pref.'email_index';
        $this->DB->Tbl['email_thread'] = $pref.'email_threads';
        $this->DB->Tbl['email_thread_items'] = $pref.'email_thread_items';
        $this->DB->Tbl['email_uidlcache'] = $pref.'email_uidlcache';
        $this->DB->Tbl['email_whitelist'] = $pref.'email_whitelist';
        return true;
    }

    public function affected() { return $this->DB->affected(); }

    private function set_error($error)
    {
        if (isset($this->append_errors) && $this->append_errors) {
            $this->error[] = $error;
        } else {
            $this->error[0] = $error;
        }
    }

    public function get_errors($nl = "\n")
    {
        $error = implode($nl, $this->error);
        if (!isset($this->retain_erros) || !$this->retain_erros) $this->error = array();
        return $error;
    }

    /**
     * Removes all information about a user from the index
     *
     * @param  int  User ID affected
     * @return  boolean  TRUE on success, FALSE otherwise
     * @since 0.2.6
     */
    public function remove_user($uid)
    {
        $uid = (float) $uid;
        if (!$uid) return false;
        // Get profiles of the user - if any
        $profiles = array();
        $qid = $this->DB->query('SELECT id FROM '.$this->DB->Tbl['profiles'].' WHERE uid='.$uid);
        while (list($profile) = $this->DB->fetchrow($qid)) $profiles[] = (float) $profile;
        if (!empty($profiles)) {
            $this->DB->query('DELETE FROM '.$this->DB->Tbl['email_uidlcache'].' WHERE profile IN('.implode(',', $profiles).')');
        }
        // Remove filter rules
        foreach ($this->filters_getlist($uid, 'all') as $filter) $this->filters_removefilter($uid, $filter['id']);
        // And the base tables' contents
        return ($this->DB->query('DELETE FROM '.$this->DB->Tbl['email_folder'].' WHERE uid='.$uid)
                && $this->DB->query('DELETE FROM '.$this->DB->Tbl['email_index'].' WHERE uid='.$uid)
                && $this->DB->query('DELETE FROM '.$this->DB->Tbl['email_thread'].' WHERE uid='.$uid)
                && $this->DB->query('DELETE FROM '.$this->DB->Tbl['email_thread_items'].' WHERE uid='.$uid)
                && $this->DB->query('DELETE FROM '.$this->DB->Tbl['email_whitelist'].' WHERE uid='.$uid)
                && $this->DB->query('DELETE FROM '.$this->DB->Tbl['user_foldersettings'].' WHERE `handler`="email" AND `uid`='.$uid));
    }

    public function get_imapboxes($uid = 0)
    {
        if (false === $uid) return array();
        $return = array();
        $qh = $this->DB->query('SELECT idx id,layered_id, childof, folder_path, friendly_name FROM '
                .$this->DB->Tbl['email_folder'].' WHERE uid='.intval($uid).' AND att_type="10" AND att_icon=":imapbox"');
        while ($line = $this->DB->fetchassoc($qh)) $return[] = $line;
        return $return;
    }

    /**
     * Get the folder structure that a user sees
     *
     * @param int $uid  UserID to get the folder structure for
     * @return array
     */
    public function get_folder_structure($uid = 0)
    {
        if (false === $uid) return false;
        $uid = intval($uid);
        $return = array();
        $syssorter = 'CASE att_icon WHEN ":inbox" THEN 0 WHEN ":outbox" THEN 1 WHEN ":drafts" THEN 2 WHEN ":templates" THEN 3'
                .' WHEN ":sent" THEN 4 WHEN ":waste" THEN 5 WHEN ":junk" THEN 6 WHEN ":mailbox" THEN 0 WHEN ":imapbox" THEN 1 ELSE 7 END';
        $qh = $this->DB->query
                ('SELECT idx id,layered_id, childof, folder_path, friendly_name, att_type `type`, att_icon `icon`,'
                .'att_has_folders has_folders, att_has_items has_items, mailnum,mailsize,`unread`,`unseen`,`stale`,`visible`,`secure`'
                .',IF (att_type in(0,10,20), CONCAT('.$syssorter.', "_", IF(att_type IN(0,10,20), 1000+layered_id, friendly_name)), friendly_name) namesorter'
                .' FROM '.$this->DB->Tbl['email_folder'].' WHERE uid='.$uid.' ORDER BY childof ASC, namesorter ASC'
                );
        $layered_0 = 0;
        while ($line = $this->DB->fetchassoc($qh)) {
            if ($line['childof'] == 0 && $line['layered_id'] > $layered_0) $layered_0 = $line['layered_id'];
            $return[$line['childof']][$line['id']] = $line;
        }
        if (!$this->DB->features['shares']) return $return;

        $groups = $this->DB->get_usergrouplist($uid, true);
        $gAdd = empty($groups) ? '' : (count($groups) == 1 ? ' OR gid='.intval($groups[0]) : ' OR gid IN('.implode(',', $groups).')');
        $qh = $this->DB->query('SELECT fid FROM '.$this->DB->Tbl['share_folder'].' WHERE handler="email" AND (uid='.$uid.$gAdd.') GROUP BY fid');
        if (!$this->DB->numrows($qh)) return $return;

        $return[0]['shareroot'] = array
                ('layered_id' => (++$layered_0), 'childof' => 0
                ,'folder_path' => 'shareroot', 'friendly_name' => 'Shared Folders'
                ,'type' => 2, 'icon' => ':sharedbox' ,'has_folders' => 1, 'has_items' => 0
                ,'mailnum' => 0, 'mailsize' => 0, 'unread' => 0, 'unseen' => 0
                ,'stale' => 0, 'visible' => 1, 'secure' => 0
                );
        $layered_1 = 0;
        $sortnames = array();
        while ($line = $this->DB->fetchassoc($qh)) { $sortnames[$line['fid']] = $line['fid']; }
        foreach ($sortnames as $k => $v) {
            $fi = $this->get_folder_info(null, $k);
            $return['shareroot'][$k] = array
                    ('layered_id' => (++$layered_1), 'childof' => 'shareroot'
                    ,'folder_path' => $fi['folder_path'], 'friendly_name' => $fi['foldername']
                    ,'type' => 2, 'icon' => $fi['icon']
                    ,'has_folders' => $fi['has_folders'], 'has_items' => $fi['has_items']
                    ,'mailnum' => $fi['mailnum'], 'mailsize' => $fi['mailsize']
                    ,'unread' => $fi['unread'], 'unseen' => $fi['unseen']
                    ,'stale' => 0, 'visible' => $fi['visible'], 'secure' => $fi['secure']
                    );
            $sortnames[$k] = $fi['foldername'];
        }
        return $return;
    }

    /**
     * Retrieve the list of unread items for all folders of given user
     * @param int User ID
     * @return array Containing folder IDs as keys, unread items and unseen flag in array as values
     * @since 0.1.4
     */
    public function folders_get_unread($uid)
    {
        $return = array();
        $query = 'SELECT idx, unread, unseen FROM '.$this->DB->Tbl['email_folder'].' WHERE uid='.intval($uid).' ORDER BY idx ASC';
        $qid = $this->DB->query($query);
        while (list ($id, $num, $unseen) = $this->DB->fetchrow($qid)) {
            $return[$id] = array('unread' => $num, 'unseen' => ($unseen));
        }
        // Include shared folders from other users
        if (!$this->DB->features['shares']) return $return;
        $groups = $this->DB->get_usergrouplist($uid, true);
        $gAdd = empty($groups) ? '' : (count($groups) == 1 ? ' OR sf.gid='.intval($groups[0]) : ' OR sf.gid IN('.implode(',', $groups).')');
        $qh = $this->DB->query('SELECT f.idx,f.unread,f.unseen FROM '.$this->DB->Tbl['share_folder'].' sf'
                .','.$this->DB->Tbl['email_folder'].' f WHERE sf.handler="email" AND (sf.uid='.$uid.$gAdd.')'
                .' AND f.idx=sf.fid GROUP BY sf.fid');
        if (!$this->DB->numrows($qh)) return $return;
        while ($line = $this->DB->fetchassoc($qh)) {
            $return[$line['idx']] = array('unread' => $line['unread'], 'unseen' => /*($line['unseen']) ? true :*/ false);
        }
        return $return;
    }

    /**
     * Reset unseen flag for all folders of given user
     *
     * @param int $uid
     * @since 4.2.9
     */
    public function folders_set_seen($uid)
    {
        $this->DB->query('UPDATE '.$this->DB->Tbl['email_folder'].' SET unseen="0" WHERE uid='.intval($uid));
    }

    public function folder_mark_secure($uid, $profile, $secure)
    {
        $this->DB->query('UPDATE '.$this->DB->Tbl['email_folder'].' SET secure="'.($secure == 1 ? 1 : 0).'"'
                .' WHERE uid='.intval($uid).' AND folder_path="'.intval($profile).':"');
    }

    public function get_folder_info($uid = 0, $folder = 0)
    {
        if (false === $folder) return false;
        if (!$this->check_perm_folder($uid, $folder)) return false;
        $query = 'SELECT friendly_name foldername, uid, uuid, childof, folder_path, att_type `type`, att_icon icon'
                .', att_big_icon big_icon, att_has_folders has_folders, att_has_items has_items'
                .', att_filter filter, mailnum, mailsize,`unread`,`unseen`,`visible`,`secure`'
                .' FROM '.$this->DB->Tbl['email_folder'].' WHERE idx='.intval($folder);
        $return = $this->DB->fetchassoc($this->DB->query($query));
        $return['settings'] = $this->DB->foldersetting_get('email', $folder, $uid);
        return $return;
    }

    /**
     * Try to find a folder by its display name. If the third parameter childof is specified,
     * the search is limited to that folder.
     * This method makes most sense in finding out, whether a folder name is unique, thus avoiding
     * the use of duplicate folder names within the same folder.
     *
     * @param  int  $uid  ID of the affected user, give 0 for global folders
     * @param  string  $folder  Display name of the folder to search for
     * [@param  int  $childof  ID of the folder to limit the search to]
     * @return  int  ID of the folder, if found; FALSE otherwise
     * @since v0.0.4
     */
    public function folder_exists($uid = 0, $folder, $childof = false)
    {
        $query = 'SELECT idx FROM '.$this->DB->Tbl['email_folder'].' WHERE uid='.intval($uid).' AND friendly_name="'.$this->DB->escape($folder).'"';
        if ($childof) $query .= ' AND childof='.intval($childof);
        list ($return) = $this->DB->fetchrow($this->DB->query($query));
        return ($return) ? $return : false;
    }

    /**
     * Try to find the ID of a folder by specifying its path within the docroot
     *
     * @param  int  ID of the affected user, 0 for global folders
     * @param  string  folder path to look for
     *[@param  bool  Check for roles and return these (like WASTE, ...); default: false]
     * @return  int  ID of the folder, if found, FALSE otherwise
     * @since 0.0.9
     */
    public function get_folder_id_from_path($uid = 0, $path, $roles = false)
    {
        $query = (!$roles)
                ? 'SELECT idx FROM '.$this->DB->Tbl['email_folder'].' WHERE uid='.intval($uid).' AND folder_path="'.$this->DB->escape($path).'"'
                : 'SELECT idx FROM '.$this->DB->Tbl['email_folder'].' WHERE uid='.intval($uid).' AND att_icon="'.$this->DB->escape(':'.$path).'"';
        list ($return) = $this->DB->fetchrow($this->DB->query($query));
        return ($return) ? $return : false;
    }

    /**
     * Returns ID or list of IDs for a certain type of system folder. Valid types at the moment
     * are: waste, sent, junk, drafts, templates, inbox, imapbox and mailbox. If the optional profile is given,
     * then only the relevant system folder for that IMAP profile is returned. To query the local
     * system folder, specify profile 0.
     *
     * @param  int  ID of the affected user
     * @param string $type  Type of the folder, see description
     *[@param int $profile  Id of the IMAP profile, 0 for a local folder]
     * @return array  An array consisting of arrays, which hold folder ID, folder path and profile ID
     * @since 0.6.1
     */
    public function get_system_folder($uid = 0, $type, $profile = false)
    {
        if (false === $profile) {
            $return = array();
            $res = $this->DB->query('SELECT idx, folder_path FROM '.$this->DB->Tbl['email_folder'].' where uid='.intval($uid)
                    .' AND att_icon="'.$this->DB->escape(':'.$type).'"');
            while (list($idx, $path) = $this->DB->fetchrow($res)) {
                if (preg_match('!^(\d+)\:.*$!', $path, $found)) { // Matches syntax for IMAP folder
                    $return[] = array('idx' => $idx, 'path' => $path, 'profile' => $found[1]);
                } else {
                    $return[] = array('idx' => $idx, 'path' => $path, 'profile' => 0);
                }
            }
            return $return;
        } elseif (0 != $profile) {
            $res = $this->DB->query('SELECT idx, folder_path FROM '.$this->DB->Tbl['email_folder'].' where uid='.intval($uid)
                    .' AND att_icon="'.$this->DB->escape(':'.$type).'"'
                    .' AND folder_path LIKE "'.intval($profile).':%"');
            list($idx, $path) = $this->DB->fetchrow($res);
            return $idx;
        } else {
            $res = $this->DB->query('SELECT idx, folder_path FROM '.$this->DB->Tbl['email_folder'].' where uid='.intval($uid)
                    .' AND att_icon="'.$this->DB->escape(':'.$type).'"');
            while (list($idx, $path) = $this->DB->fetchrow($res)) {
                if (preg_match('!^(\d+)\:.*$!', $path)) continue; // Matches syntax for IMAP folder
                return $idx;
            }
        }
    }

    /**
     * Create index information about a new folder
     *
     * @param array $pass Array holding all necessary information
     * @return int $id Unique ID fo the newly created index entry; false on failure
     */
    public function create_folder($pass)
    {
        // On clean implementation you should not fail here
        if (!isset($pass['uid']))           return false;
        if (!isset($pass['friendly_name'])) return false;
        if (!isset($pass['folder_path']))   return false;
        if (!isset($pass['childof']))       return false;
        if (!isset($pass['type']))          return false;
        // If not specified, set default values
        if (!isset($pass['icon']))        $pass['icon'] = '';
        if (!isset($pass['filter']))      $pass['filter'] = '';
        if (!isset($pass['has_folders'])) $pass['has_folders'] = true;
        if (!isset($pass['has_items']))   $pass['has_items'] = true;
        if (!isset($pass['visible']))     $pass['visible'] = 1;
        // These settings are useful for IMAP only
        if (!isset($pass['mailnum']))     $pass['mailnum'] = 0;
        if (!isset($pass['mailsize']))    $pass['mailsize'] = 0;
        if (!isset($pass['unread']))      $pass['unread'] = 0;
        // A bit lousy, should better be done by subquerying...
        $qid = $this->DB->query('SELECT max(layered_id) FROM '.$this->DB->Tbl['email_folder'].' WHERE childof='.intval($pass['childof']));
        list ($max_layered) = $this->DB->fetchrow($qid);
        $query = 'INSERT INTO '.$this->DB->Tbl['email_folder'].' SET uid='.intval($pass['uid']).',`uuid`="'.basics::uuid().'",att_big_icon=""'
                .',friendly_name="'.$this->DB->escape($pass['friendly_name']).'"'
                .',folder_path="'.$this->DB->escape($pass['folder_path']).'"'
                .',childof='.intval($pass['childof']).',layered_id='.($max_layered+1)
                .',att_type="'.$this->DB->escape($pass['type']).'"'
                .',att_icon="'.$this->DB->escape($pass['icon']).'"'
                .',att_filter="'.$this->DB->escape($pass['filter']).'"'
                .',att_has_folders="'.($pass['has_folders'] ? 1 : 0).'"'
                .',att_has_items="'.($pass['has_items'] ? 1 : 0).'"'
                .',mailnum='.intval($pass['mailnum'])
                .',mailsize='.intval($pass['mailsize'])
                .',unread='.intval($pass['unread'])
                .',visible="'.($pass['visible'] ? 1 : 0).'"';
        if (!$this->DB->query($query)) {
            $this->set_error($this->DB->error());
            return false;
        }
        return $this->DB->insertid();
    }

    /**
     * Update index information about a folder
     *
     * @param array $pass Array holding all necessary information
     * @return bool   TRUE on success, FALSE on failure
     * @since v0.0.6
     */
    public function update_folder($pass)
    {
        // On clean implementation you should not fail here
        if (!isset($pass['uid'])) return false;
        if (!isset($pass['id'])) return false;
        // Construct query string
        $out = array();
        foreach (array
                (array('pass' => 'friendly_name', 'key' => 'friendly_name', 'quote' => true)
                ,array('pass' => 'folder_path', 'key' => 'folder_path', 'quote' => true)
                ,array('pass' => 'childof', 'key' => 'childof', 'quote' => false)
                ,array('pass' => 'layered_id', 'key' => 'layered_id', 'quote' => false)
                ,array('pass' => 'type', 'key' => 'att_type', 'quote' => true)
                ,array('pass' => 'icon', 'key' => 'att_icon', 'quote' => true)
                ,array('pass' => 'big_icon', 'key' => 'att_big_icon', 'quote' => true)
                ,array('pass' => 'filter', 'key' => 'att_filter', 'quote' => true)
                ,array('pass' => 'has_folders', 'key' => 'att_has_folders', 'quote' => true)
                ,array('pass' => 'has_items', 'key' => 'att_has_items', 'quote' => true)
                ,array('pass' => 'mailnum', 'key' => 'mailnum', 'quote' => false)
                ,array('pass' => 'mailsize', 'key' => 'mailsize', 'quote' => false)
                ,array('pass' => 'unread', 'key' => 'unread', 'quote' => true)
                ,array('pass' => 'unseen', 'key' => 'unseen', 'quote' => true)
                ,array('pass' => 'stale', 'key' => 'stale', 'quote' => true)
                ,array('pass' => 'visible', 'key' => 'visible', 'quote' => true)
                ) as $v) {
            if (!isset($pass[$v['pass']])) continue;
            $out[] = $v['key'].'='.($v['quote'] ? '"'.$this->DB->escape($pass[$v['pass']]).'"' : intval($pass[$v['pass']]));
        }
        $query = implode(',', $out);
        unset($out);
        // If nothing found to update, return as false
        if (!$query) return false;
        return $this->DB->query('UPDATE '.$this->DB->Tbl['email_folder'].' SET `uuid`="'.basics::uuid().'",'.$query
                .' WHERE idx='.intval($pass['id']).' AND uid='.intval($pass['uid']));
    }

    /**
     * Sets visibility status of a given folder id
     *
     * @param int $uid
     * @param int $id
     * @param bool $visible
     * @return bool
     * @since 1.0.2
     */
    public function hide_folder($uid, $id, $visible)
    {
        return $this->DB->query('UPDATE '.$this->DB->Tbl['email_folder'].' SET visible="'.($visible ? 1 : 0).'" WHERE idx='.intval($id).' AND uid='.intval($uid));
    }

    /**
     * Delete index information about a given folder
     *
     * @param int $uid  User ID of the given folder
     * @param int $id   Unique ID of the folder to remove
     * [@param bool $with_childs  Whether to also delete the information about any subfolder - if set to
     *                           TRUE, subfolder information will also be removed; Default: FALSE]
     * [@param bool $with_mails  Whether to also delete the mails in this folder from the index database;
     *                           if set to TRUE, mail information will also be removed; Default: TRUE]
     * @return bool  TRUE on succes, FALSE on failure
     */
    public function remove_folder($uid, $id, $with_childs = false, $with_mails = true)
    {
        $affected = false;
        if ($with_childs) {
            $qid = $this->DB->query('SELECT idx FROM '.$this->DB->Tbl['email_folder'].' WHERE childof='.intval($id).' AND uid='.intval($uid));
            while (list($child) = $this->DB->fetchrow($qid)) {
                $affected = $this->remove_folder($uid, $child, true);
                if (!$affected) return false;
            }
        }
        if ($with_mails) $this->mail_delete($uid, false, $id);
        return ($this->DB->query('DELETE FROM '.$this->DB->Tbl['email_folder'].' WHERE idx='.intval($id).' AND uid='.intval($uid))
                && $this->DB->query('DELETE FROM '.$this->DB->Tbl['user_foldersettings'].' WHERE `handler`="email" AND `fid`='.intval($id)));
    }

    /**
     * Resyncs index fields with real amount of messages, unread and unseen states
     *
     * @param int $uid  User ID of the given folder
     * @param int $id   Unique ID of the folder to resync
     * @return bool  TRUE on succes, FALSE on failure
     * @since 0.3.9
     */
    public function resync_folder($uid, $id)
    {
        if (!$this->check_perm_folder($uid, $id)) return false;
    	$query = 'SELECT sum(hsize), count(*), sum(if(`read`="0",1,0)) FROM '.$this->DB->Tbl['email_index'].' WHERE folder_id='.intval($id);
        list ($size, $mailnum, $unread) = $this->DB->fetchrow($this->DB->query($query));
    	$query = 'UPDATE '.$this->DB->Tbl['email_folder'].' SET `uuid`="'.basics::uuid().'",`mailsize`='.($size+0).',`mailnum`='.($mailnum+0)
    	       .',`unread`='.($unread+0).($unread == 0 ? ',`unseen`="0"' : '')
    	       .' WHERE idx='.intval($id);
        return $this->DB->query($query);
    }

    /**
     * Marks all mails in a folder as "seen". This does not affect the (un)read flag.
     *
     * @param integer $id  folder id
     * @return true (Does not care about errors right now)
     * @since 4.1.5
     */
    public function folder_setseen($uid, $id)
    {
        $id = intval($id);
        $uid = intval($uid);
        if (!$this->check_perm_folder($uid, $id, 'w')) return false;
        $this->DB->query('UPDATE '.$this->DB->Tbl['email_folder'].' SET `uuid`="'.basics::uuid().'",unseen="0" WHERE idx='.$id);
        $this->DB->query('UPDATE '.$this->DB->Tbl['email_index'].' SET seen="1" WHERE folder_id='.$id);
        return true;
    }

    /**
     * Save the settings for an individual folder (preview, which fields to show ...)
     *
     * @param int $uid  User ID of the given folder
     * @param int $id   Unique ID of the folder to save the settings for
     * @param string Serialized folder settings, take care for updates by yourself please
     * @since 0.4.3
     */
    public function set_folder_settings($uid, $id, $settings)
    {
        if (!$this->check_perm_folder($uid, $id, 'r')) return false;
        // Delete everything since this saves us from iterating over both the current DB and the passed argument
        $this->DB->foldersetting_del('email', $id, $uid);
        // Push into DB
        return $this->DB->foldersetting_set('email', $id, $uid, $settings);
    }

    /**
     * Retrieves a list of all UIDLs of a specific folder, mainly interesting for IMAP, when passing
     * $retfield = 'uidl' also for checking mails in the file system for their existance in the DB index
     *
     * @param int $uid  User ID for the given folder
     * @param int $id   Unique ID of the folder
     *[@param string $orderby  Order by this DB field]
     *[@param ASC|DESC $orderdir Order direction]
     * @param string|array  Return this DB field, by deafult "ouidl" is used, optionally an array of diels can be passed
     * @return array
     * @since 0.5.0
     */
    public function get_folder_uidllist($uid, $id, $orderby = false, $orderdir = false, $retfield = 'ouidl')
    {
        $return = array();
        $orderadd = '';
        $id = intval($id);
        $uid = intval($uid);
        if (!$this->check_perm_folder($uid, $id)) return false;
        if (false !== $orderby) $orderadd = ' ORDER BY `'.$this->DB->escape($orderby).'` '.('ASC' == $orderdir ? 'ASC' : 'DESC');
        // Automatically drop doublettes
        $qid = $this->DB->query('SELECT idx,profile,ouidl, count(*) zahl FROM '.$this->DB->Tbl['email_index'].' WHERE folder_id='.$id
                .' GROUP BY CONCAT(profile, ".", ouidl) HAVING zahl > 1');
        if ($this->DB->numrows($qid)) {
            while (list($idx, $profile, $uidl) = $this->DB->fetchrow($qid)) {
                if (!$profile || !$uidl) continue; // Prevent killing entries without profile or uidl data contained
                $qid2 = $this->DB->query('DELETE FROM '.$this->DB->Tbl['email_index'].' WHERE CONCAT(profile,".",ouidl)="'.$profile.'.'.$uidl.'" AND idx!='.$idx);
            }
        }
        // Finally fetch the list
        if (is_array($retfield)) {
            foreach ($retfield as $k => $v) $retfield[$k] = '`'.$this->DB->escape($v).'`';
            $retfield = implode(',', $retfield);
            $retmode = 1;
        } else {
            $retfield = $this->DB->escape($retfield);
            $retmode = 0;
        }
        $qid = $this->DB->query('SELECT idx, '.$retfield.' FROM '.$this->DB->Tbl['email_index'].' WHERE folder_id='.$id.$orderadd);
        if (!$this->DB->numrows($qid)) return $return;
        if (0 == $retmode) {
            while (list($idx, $ret) = $this->DB->fetchrow($qid)) $return[$idx] = $ret;
        } else {
            while ($res = $this->DB->fetchassoc($qid)) {
                $idx = $res['idx'];
                unset($res['idx']);
                $return[$idx] = $res;
            }
        }
        return $return;
    }

    /**
     * Returns all folders belonging to a speficif IMAP profile
     *
     * @param int $uid  User ID to perform the query under
     * @param int $profile ID of the profile to get the folder list for
     * @return array
     * @since 0.5.2
     */
    public function get_imapkids($uid, $profile)
    {
        $return = array();
        $qid = $this->DB->query('SELECT idx, folder_path FROM '.$this->DB->Tbl['email_folder'].' WHERE uid='.intval($uid)
                .' AND folder_path LIKE "'.intval($profile).':%" ORDER by folder_path ASC');
        while (list($idx, $path) = $this->DB->fetchrow($qid)) $return[$idx] = $path;
        return $return;
    }

    /**
     * Retrieve the mail index of a given folder and return as array
     *
     * @param int  ID of the affected user, give 0 for global folders
     * @param int  ID of the folder
     *[@param mixed  Skimming option, pass a FALSE value for no skimming, nonngeative integer for an offset]
     *[@param mixed  Skimming option, pass a FALSE value for no skimming, nonngeative integer for a pagesize]
     *[@param string  name of the DB field for ordering by; Default: hdate_sent]
     *[@param 'ASC'|'DESC'  Direction to order; Default: ASC]
     *[@param int  ID of a mail for getting only this mail's information, omit everything else, pass folder ID 0 then]
     *[@param string  Search criteria to match mails against, also pass pattern then]
     *[@param string  Search pattern to match mails against, also pass criteria]
     */
    public function get_mail_list($uid = 0, $folder = 0, $offset = false, $pagesize = false, $ordby = false, $orddir = 'ASC'
            ,$idx = false, $criteria = false, $pattern = false, $flags = null)
    {
        if (false === $uid) return false;
        if (false === $folder && false === $idx) return false;
        if ($folder !== false && $folder && !$this->check_perm_folder($uid, $folder)) return false;

        $return = array();
        $q_r = '';
        $valiCrit = array('from' => 'hfrom', 'to' => array('hto', 'hcc', 'hbcc')
                ,'cc' => 'hcc', 'bcc' => 'hbcc', 'subject' => 'hsubject', 'ouidl' => 'ouidl'
                ,'allheaders' => array('hfrom', 'hto', 'hcc', 'hbcc', 'hsubject')
                );
        if (!$ordby) {
            $ordby = 'i.`hdate_sent`';
        } elseif ($ordby == 'status') {
            $ordby = 'i.`read`';
        }

        // Limit result set to mails matching search criteria and search pattern
        if ($criteria !== false && $pattern !== false && isset($valiCrit[$criteria])) {
            if (is_array($valiCrit[$criteria])) {
                $searches = array();
                $pattern = $this->DB->escape($pattern);
                foreach ($valiCrit[$criteria] as $crit) {
                    $searches[] = $crit.' LIKE "%'.str_replace('%', '\%', str_replace('_', '\_', $pattern)).'%"';
                }
                $q_r .= ' AND ('.implode(' OR ', $searches).')';
            } elseif (is_array($pattern) && count($pattern)) {
                foreach ($pattern as $k => $v) $pattern[$k] = '"'.$this->DB->escape($v).'"';
                $q_r .= ' AND '.$valiCrit[$criteria].' IN('.implode(',', $pattern).')';
            } else {
                $pattern = $this->DB->escape($pattern);
                $q_r .= ' AND '.$valiCrit[$criteria].' LIKE "%'.str_replace('%', '\%', str_replace('_', '\_', $pattern)).'%"';
            }
        } elseif ($criteria == '@@thread@@') { // Use with care!
            $q_r .= ' AND `thread_id`='.floatval($pattern);
            if (isset($GLOBALS['ignore'])) $q_r .= ' AND i.idx!='.intval($GLOBALS['ignore']);

            $folder = $offset = $pagesize = 0;
        } elseif ($criteria == '@@internal@@') { // Use with care!
            $q_r .= $pattern;
        }
        if (!is_null($flags) && !empty($flags)) {
            foreach (array('unread' => '`read`="0"', 'forwarded' => '`forwarded`="1"', 'answered' => '`answered`="1"'
                    ,'bounced' => '`bounced`="1"', 'attachments' => '`attachments`="1"'
                    ,'coloured' => '(`colour` IS NOT NULL AND `colour`!="")') as $k => $v) {
                if (isset($flags[$k])) $q_r .= ' AND '.$v;
            }
        }

        // Either select data for a single mail or optionally ordered, skimmable data from a folder
        if ($idx) {
            $q_r .= ' AND i.idx='.intval($idx);
        } else {
            if ($folder) {
                $q_r .= ' AND i.folder_id='.intval($folder);
            } else {
                $q_r .= ' AND i.`uid`='.intval($uid);
            }
            $q_r .= ' ORDER BY `'.$this->DB->escape($ordby).'` '.($orddir == 'ASC' ? 'ASC' : 'DESC');
        }
        $query = 'SELECT i.idx, uidl, folder_id, hfrom, hto, hcc, hbcc, hsubject, hdate_recv, hdate_sent, hsize, hpriority'
                .',`attachments`,`read`,`answered`,`forwarded`,`bounced`,`type`,`ouidl`,`profile`,`dsn_sent`'
                .',`cached`, if(`colour` IS NULL, "", `colour`) `colour`,`htmlunblocked`,ti.`thread_id`, t.`known_mails`'
                .' FROM '.$this->DB->Tbl['email_index'].' i'
                .' LEFT JOIN '.$this->DB->Tbl['email_thread_items'].' ti ON ti.`mail_id`=i.`idx` AND ti.`uid`=i.`uid`'
                .' LEFT JOIN '.$this->DB->Tbl['email_thread'].' t ON ti.`thread_id`=t.`idx`'
                .' WHERE 1'.$q_r;
        if ($offset && $idx === false) {
            $query .= ' LIMIT '.(($pagesize) ? intval($offset).', '.intval($pagesize) : intval($offset));
        } elseif ($idx === false && $pagesize) {
            $query .= ' LIMIT '.intval($pagesize);
        }
        $qh = $this->DB->query($query);
        $mailcounter = ($offset) ? $offset : 0;
        while ($line = $this->DB->fetchassoc($qh)) {
            $return[$mailcounter] = array
                    ('id' => $line['idx']
                    ,'uidl' => $line['uidl']
                    ,'folder_id' => $line['folder_id']
                    ,'from' => $line['hfrom']
                    ,'to' => $line['hto']
                    ,'cc' => $line['hcc']
                    ,'bcc' => $line['hbcc']
                    ,'subject' => $line['hsubject']
                    ,'date_received' => $line['hdate_recv']
                    ,'date_sent' => $line['hdate_sent']
                    ,'size' => $line['hsize']
                    ,'priority' => $line['hpriority']
                    ,'attachments' => $line['attachments'] == 1 ? 1 : 0
                    ,'status' => $line['read'] == 1 ? 1 : 0
                    ,'answered' => $line['answered'] == 1 ? 1 : 0
                    ,'forwarded' => $line['forwarded'] == 1 ? 1 : 0
                    ,'bounced' => $line['bounced'] == 1 ? 1 : 0
                    ,'type' => $line['type']
                    ,'ouidl' => $line['ouidl']
                    ,'profile' => $line['profile']
                    ,'dsn_sent' => $line['dsn_sent']
                    ,'cached' => $line['cached']
                    ,'colour' => $line['colour']
                    ,'htmlunblocked' => $line['htmlunblocked']
                    ,'thread_id' => ($line['known_mails']>1) ? $line['thread_id'] : null
                    );
            ++$mailcounter;
        }
        return $return;
    }

    /**
     * This method emulates the search for certain mails (in fact: it searches for all the mails) but does not impose the page size
     * and offset parameters of get_mail_list(). For the meanings of the parameters see get_mail_list() above
     *
     * @param unknown_type $uid
     * @param unknown_type $folder
     * @param unknown_type $idx
     * @param unknown_type $criteria
     * @param unknown_type $pattern
     */
    public function mail_aggregate_search($uid = 0, $folder = 0, $idx = false, $criteria = false, $pattern = false, $flags = null)
    {
        if (!$this->check_perm_folder($uid, $folder)) return false;
        $q_r = '';
        $valid_criteria = array('from' => 'hfrom', 'to' => array('hto', 'hcc', 'hbcc')
                ,'cc' => 'hcc', 'bcc' => 'hbcc', 'subject' => 'hsubject', 'ouidl' => 'ouidl'
                ,'allheaders' => array('hfrom', 'hto', 'hcc', 'hbcc', 'hsubject')
                );
        // Limit result set to mails matching search criteria and search pattern
        if ($criteria !== false && $pattern !== false && isset($valid_criteria[$criteria])) {
            $pattern = $this->DB->escape($pattern);
            if (is_array($valid_criteria[$criteria])) {
                $searches = array();
                foreach ($valid_criteria[$criteria] as $crit) {
                    $searches[] = $crit.' LIKE "%'.str_replace('%', '\%', str_replace('_', '\_', $pattern)).'%"';
                }
                $q_r .= ' AND ('.implode(' OR ', $searches).')';
            } else {
                $q_r .= ' AND '.$valid_criteria[$criteria].' LIKE "%'.str_replace('%', '\%', str_replace('_', '\_', $pattern)).'%"';
            }
        } elseif ($criteria == '@@thread@@') { // Use with care!
            $q_r .= ' AND `thread_id`='.floatval($pattern);
            $folder = 0;
        }
        if (!is_null($flags) && !empty($flags)) {
            foreach (array('unread' => '`read`="0"', 'forwarded' => '`forwarded`="1"', 'answered' => '`answered`="1"'
                    ,'bounced' => '`bounced`="1"', 'attachments' => '`attachments`="1"'
                    ,'coloured' => '(`colour` IS NOT NULL AND `colour`!="")') as $k => $v) {
                if (isset($flags[$k])) $q_r .= ' AND '.$v;
            }
        }
        if ($idx) {
            $q_r .= ' AND i.idx='.intval($idx);
        } elseif ($folder) {
            $q_r .= ' AND i.folder_id='.intval($folder);
        } else {
            $q_r .= ' AND i.`uid`='.intval($uid);
        }
        $query = 'SELECT COUNT(*) `mails`, SUM(i.hsize) `size` FROM '.$this->DB->Tbl['email_index'].' i'
                .' LEFT JOIN '.$this->DB->Tbl['email_thread_items'].' ti ON ti.`mail_id`=i.`idx` AND ti.`uid`=i.`uid`'
                .' WHERE 1'.$q_r;
        $qh = $this->DB->query($query);
        return $this->DB->fetchassoc($qh);
    }

    /**
     * Retrieves a list of the latest received mails for the pinboard view
     * @param int $uid  ID of the user
     * @return array @see get_mail_list()
     * @since 4.3.4
     */
    public function mail_pinboard_digest($uid)
    {
        $folders = $this->DB->foldersettings_find('email', $uid, 'not_in_pinboard');
        $sqlAdd = (!empty($folders)) ? ' AND folder_id NOT IN('.implode(',', $folders).')' : '';
        return $this->get_mail_list($uid, 0, 0, 10, 'hdate_sent', 'DESC', false, '@@internal@@', $sqlAdd);
    }

    /**
     * Allows to check for the type of a given mail
     *
     * @param int|null $uid  User id who checks (might be null for global queries
     * @param int $mail  IDX of the mail
     * @return string  One of 'mail','sms','ems','mms','fax','appointment','away','receipt','sysmail'
     * @since 4.4.7
     */
    public function mail_get_type($uid = null, $mail)
    {
        $maylook = false;
        $qid = $this->DB->query('SELECT `folder_id`,`type` FROM '.$this->DB->Tbl['email_index'].' WHERE idx='.intval($mail));
        list ($fid, $type) = $this->DB->fetchrow($qid);
        if ($fid) {
            $maylook = $this->check_perm_folder($uid, $fid, 'r');
        }
        return ($maylook) ? $type : false;
    }

    public function mail_set_status($uid = 0, $mail, $rd = null, $aw = null, $fw = null, $bn = null)
    {
        if (is_null($rd) && is_null($aw) && is_null($fw) && is_null($bn)) return true;
        list ($folder) = $this->DB->fetchrow($this->DB->query('SELECT folder_id FROM '.$this->DB->Tbl['email_index'].' WHERE idx='.intval($mail)));
        if (!$this->check_perm_folder($uid, $folder, 'w')) return false;
        $stat = $this->DB->query('UPDATE '.$this->DB->Tbl['email_index'].' SET uid=uid'
                .',`read`='.(is_null($rd) ? '`read`' : ($rd ? '"1"' : '"0"'))
                .',answered='.(is_null($aw) ? 'answered' : ($aw ? '"1"' : '"0"'))
                .',forwarded='.(is_null($fw) ? 'forwarded' : ($fw ? '"1"' : '"0"'))
                .',bounced='.(is_null($bn) ? 'bounced' : ($bn ? '"1"' : '"0"'))
                .' WHERE idx='.intval($mail));
        return $this->resync_folder($uid, $folder);
    }

    public function mail_set_dsnsent($uid = 0, $mail = 0, $status)
    {
        list ($folder) = $this->DB->fetchrow($this->DB->query('SELECT folder_id FROM '.$this->DB->Tbl['email_index'].' WHERE idx='.intval($mail)));
        if (!$this->check_perm_folder($uid, $folder, 'w')) return false;
        return $this->DB->query('UPDATE '.$this->DB->Tbl['email_index'].' SET dsn_sent="'.(($status) ? 1 : 0).'" WHERE idx='.intval($mail));
    }

    public function mail_set_htmlunblocked($uid = 0, $mail = 0, $status)
    {
        list ($folder) = $this->DB->fetchrow($this->DB->query('SELECT folder_id FROM '.$this->DB->Tbl['email_index'].' WHERE idx='.intval($mail)));
        if (!$this->check_perm_folder($uid, $folder, 'w')) return false;
        return $this->DB->query('UPDATE '.$this->DB->Tbl['email_index'].' SET htmlunblocked="'.(($status) ? 1 : 0).'" WHERE idx='.intval($mail));
    }

    public function mail_set_colour($uid = 0, $mail = 0, $colour)
    {
        list ($folder) = $this->DB->fetchrow($this->DB->query('SELECT folder_id FROM '.$this->DB->Tbl['email_index'].' WHERE idx='.intval($mail)));
        if (!$this->check_perm_folder($uid, $folder, 'w')) return false;
        return $this->DB->query('UPDATE '.$this->DB->Tbl['email_index']
                .' SET colour='.(($colour !== false) ? '"'.$this->DB->escape($colour).'"' : 'NULL').' WHERE idx='.intval($mail));
    }

    public function mail_get_real_location($uid = 0, $mail = 0)
    {
        $return = false;
        $qid = $this->DB->query('SELECT f.uid, f.folder_path, m.uidl FROM '.$this->DB->Tbl['email_index'].' m'
                .', '.$this->DB->Tbl['email_folder'].' f WHERE m.idx='.intval($mail).' AND m.folder_id=f.idx');
        $return = $this->DB->fetchrow($qid);
        return $return;
    }

    /**
     * Returns the mail structure from index
     *
     * @param  int  $uid  The user ID to perform the operation for
     * @param  int  $mail  The mail to get the structure of
     * @return string  serialized mail structure
     * @since 0.1.6
     */
    public function mail_get_structure($uid = 0, $mail = 0)
    {
        list ($folder) = $this->DB->fetchrow($this->DB->query('SELECT folder_id FROM '.$this->DB->Tbl['email_index'].' WHERE idx='.intval($mail)));
        if (!$this->check_perm_folder($uid, $folder)) return false;
        $query = 'SELECT struct FROM '.$this->DB->Tbl['email_index'].' WHERE idx='.intval($mail);
        list ($struct) = $this->DB->fetchrow($this->DB->query($query));
        return $struct;
    }

    /**
     * Set the mail structure in index
     *
     * @param  int  $uid  The user ID to perform the operation for
     * @param  int  $mail  The mail to set the structure of
     * @param string  serialized mail structure
     * @since 0.5.5
     */
    public function mail_set_structure($uid = 0, $mail = 0, $struct)
    {
        list ($folder) = $this->DB->fetchrow($this->DB->query('SELECT folder_id FROM '.$this->DB->Tbl['email_index'].' WHERE idx='.intval($mail)));
        if (!$this->check_perm_folder($uid, $folder)) return false;
        return $this->DB->query('UPDATE '.$this->DB->Tbl['email_index'].' set struct="'.$this->DB->escape($struct).'" WHERE idx='.intval($mail));
    }

    /**
     * Delete either a single mail or all mails within a given folder from index
     *
     * @param  int  $uid  The user ID to perform the operation for
     * @param  int  $mail  Optionally the mail to delete; if FALSE, one MUST specify the affected folder
     * @param  int  $folder  Optionally the folder to delete all mails for; if FALSE, one MUST specify the mail ID
     * @return  bool  TRUE on success, FALSE on failure
     * @since 0.0.7
     */
    public function mail_delete($uid = 0, $mail = false, $folder = false, $ouidl = false)
    {
        if ($mail === false && $folder === false && $ouidl === false) {
            $this->set_error('Please either specify the mail, its original UIDL or the folder, where all mails should be killed');
            return false;
        }
        if ($mail !== false) {
            $mail = floatval($mail);

            $query = 'SELECT folder_id,hsize,`read`,`ouidl`,`profile` FROM '.$this->DB->Tbl['email_index'].' WHERE idx='.$mail;
            list ($fid, $size, $is_read, $suidl, $sprofile) = $this->DB->fetchrow($this->DB->query($query));
            if (!$this->check_perm_folder($uid, $fid, 'w')) return false;

            $query = 'DELETE FROM '.$this->DB->Tbl['email_index'].' WHERE idx='.$mail;
            if ($this->DB->query($query)) {
                // Unregister from thread index
                $this->DB->query('UPDATE '.$this->DB->Tbl['email_thread'].' t,'.$this->DB->Tbl['email_thread_items'].' ti'
                        .' SET t.known_mails=IF(t.known_mails=0, 0, t.known_mails-1)'
                        .' WHERE ti.mail_id='.$mail.' AND ti.thread_id=t.idx');
                $this->DB->query('DELETE FROM '.$this->DB->Tbl['email_thread_items'].' WHERE mail_id='.$mail);
                // Update folder information
                $query = 'UPDATE '.$this->DB->Tbl['email_folder'].' SET mailnum=mailnum-1'
                        .', mailsize=mailsize-'.($size+0).', unread=IF(unread=0, 0, unread-'.(1-$is_read).')'
                        .',unseen=IF(unread=0, "0", unseen) WHERE idx='.$fid;
                $this->DB->query($query);
                if ($suidl && $sprofile) $this->uidlcache_markdeleted($sprofile, $suidl);
                return true;
            }
            return false;
        } elseif ($ouidl !== false) {
            $query = 'SELECT idx,hsize,`read`,ouidl,profile FROM '.$this->DB->Tbl['email_index']
                    .' WHERE folder_id='.intval($folder).' AND ouidl="'.$this->DB->escape($ouidl).'"';
            list ($mail, $size, $is_read, $suidl, $sprofile) = $this->DB->fetchrow($this->DB->query($query));
            if (!$this->check_perm_folder($uid, $folder, 'w')) return false;
            if (!$mail) return false;

            $query = 'DELETE FROM '.$this->DB->Tbl['email_index'].' WHERE idx='.intval($mail);
            if ($this->DB->query($query)) {
                list ($mailnum) = $this->DB->fetchrow($this->DB->query('SELECT mailnum FROM '.$this->DB->Tbl['email_folder'].' WHERE idx='.intval($folder)));
                if ($mailnum < 2) {
                    $query = 'UPDATE '.$this->DB->Tbl['email_folder'].' SET mailnum=0,mailsize=0,unread=0,unseen="0" WHERE idx='.intval($folder);
                } else {
                    $query = 'UPDATE '.$this->DB->Tbl['email_folder'].' SET mailnum=mailnum-1'
                            .', mailsize=mailsize-'.($size+0).', unread=IF(unread=0, 0, unread-'.(1-$is_read).')'
                            .',unseen=IF(unread=0, "0", unseen) WHERE idx='.intval($folder);
                }
                $this->DB->query($query);
                if ($suidl && $sprofile) $this->uidlcache_markdeleted($sprofile, $suidl);
                return true;
            }
            return false;
        } elseif ($folder !== false) {
            if (!$this->check_perm_folder($uid, $folder, 'w')) return false;
            $uidls = array();
            $qid = $this->DB->query('SELECT profile, ouidl FROM '.$this->DB->Tbl['email_index'].' WHERE folder_id='.intval($folder));
            while (list ($sprofile, $suidl) = $this->DB->fetchrow($qid)) {
                if (!$sprofile || !$suidl) continue;
                $this->uidlcache_markdeleted($sprofile, $suidl);
            }
            $query = 'DELETE FROM '.$this->DB->Tbl['email_index'].' WHERE folder_id='.intval($folder);
            if ($this->DB->query($query)) {
                $query = 'UPDATE '.$this->DB->Tbl['email_folder'].' SET mailnum=0,mailsize=0,unread=0,unseen="0" WHERE idx='.intval($folder);
                $this->DB->query($query);
                $err = $this->DB->error();
                if ($err) {
                    $this->set_error($err);
                    return false;
                }
                return true;
            }
            return false;
        }
        // We fall down here, if some bogus input was given
        return false;
    }

    /**
     * Move a mail from one folder to another. As of phlyMail 4 the owner id is
     * changed according to the owner id of the folder the mail now belongs to.
     * Additionally the uid is no longer checked!
     *
     * @param  int  $uid  The user ID to perform the operation for (actually ignored)
     * @param  int  $mail  Mail ID to move
     * @param  int  $folder  The destination folder to move the mail to
     * @param  string  $newname  In case the file name changed, pass it here, Default: null
     * @param  bool $cached Whether this mail is cached locally (POP3) or not (IMAP)
     * @param  string $newouidl  In case the UIDL of an IMAP mail changes, pass it here, Default: null
     * @return  bool  TRUE on success, FALSE on failure
     * @since 0.0.8
     */
    public function mail_move($uid = 0, $mail = false, $folder = false, $newname = null, $cached = true, $newouidl = null)
    {
        if ($mail === false || $folder === false) {
            $this->set_error('Please specify the mail to move and its target folder');
            return false;
        }
        $mail = intval($mail);
        $folder = intval($folder);
        // Get current folder and mailsize for updating the meta data of old and new folder
        $query = 'SELECT m.folder_id,m.hsize,m.`read`,f.uid FROM '.$this->DB->Tbl['email_index'].' m'
                .','.$this->DB->Tbl['email_folder'].' f WHERE m.idx='.$mail.' AND f.idx=m.folder_id';
        list ($fid, $hsize, $is_read, $newowner) = $this->DB->fetchrow($this->DB->query($query));
        if (!$fid || !$hsize) return false;

        $query = 'UPDATE '.$this->DB->Tbl['email_index'].' SET folder_id='.$folder
                .', uidl='.(!is_null($newname) ? '"'.$this->DB->escape($newname).'"' : 'uidl')
                .', ouidl='.(!is_null($newouidl) ? '"'.$this->DB->escape($newouidl).'"' : 'ouidl')
                .', cached="'.($cached ? 1 : 0).'", uid='.$newowner
                .' WHERE idx='.$mail;
        if ($this->DB->query($query)) {
            // Remove from old folder
            $query = 'UPDATE '.$this->DB->Tbl['email_folder'].' SET mailnum=mailnum-1,mailsize=mailsize-'.($hsize+0)
                    .',unread=unread-'.(1-$is_read).' WHERE idx='.$fid;
            $this->DB->query($query);
            $err = $this->DB->error();
            if ($err) {
                $this->set_error($err);
                return false;
            }
            // Add to new folder
            $query = 'UPDATE '.$this->DB->Tbl['email_folder'].' SET mailnum=mailnum+1,mailsize=mailsize+'.($hsize+0)
                    .',unread=unread+'.(1-$is_read).' WHERE idx='.$folder;
            $this->DB->query($query);
            $err = $this->DB->error();
            if ($err) {
                $this->set_error($err);
                return false;
            }
            return true;
        }
        return false;
    }

    /**
     * Copy a mail from one folder to another As of phlyMail 4 the owner id is
     * changed according to the owner id of the folder the mail now belongs to.
     * Additionally the uid is no longer checked!
     *
     * @param  int  $uid  The user ID to perform the operation for (actually ignored)
     * @param  int  $mail  Mail ID to copy
     * @param  int  $folder  The destination folder to copy the mail to
     * @param  string  $newname  In case the file name changed, pass it here, Default: null
     * @param  bool $cached Whether this mail is cached locally (POP3) or not (IMAP)
     * @param  string $newouidl  In case the UIDL of an IMAP mail changes, pass it here, Default: null
     * @return  bool  TRUE on success, FALSE on failure
     * @since 0.1.4
     */
    public function mail_copy($uid = 0, $mail = false, $folder = false, $newname = null, $cached = true, $newouidl = null)
    {
        if ($mail === false || $folder === false || $newname === false) {
            $this->set_error('Please specify the mail to copy and its target folder');
            return false;
        }
        // Get current folder and mailsize for updating the meta data of old and new folder
        $query = 'SELECT * FROM '.$this->DB->Tbl['email_index'].' WHERE idx='.intval($mail);
        $from = $this->DB->fetchassoc($this->DB->query($query));
        if (!$from || empty($from)) return false;
        $is_read = $from['read'] ? 1 : 0;
        unset($from['idx']);
        list ($newowner) = $this->DB->fetchrow($this->DB->query('SELECT uid FROM '.$this->DB->Tbl['email_folder']
                .' WHERE idx='.intval($from['folder_id'])));
        foreach ($from as $k => $v) $from[$k] = $this->DB->escape($v);
        // Duplicate mail in index
        $query = 'INSERT '.$this->DB->Tbl['email_index'].' SET folder_id='.$folder.',uid='.$newowner
                .', uidl='.(!is_null($newname) ? '"'.$this->DB->escape($newname).'"' : 'uidl')
                .', ouidl='.(!is_null($newouidl) ? '"'.$this->DB->escape($newouidl).'"' : 'ouidl')
                .',hfrom="'.$from['hfrom'].'",hto="'.$from['hto'].'",hsubject="'.$from['hsubject'].'"'
                .',hdate_sent="'.$from['hdate_sent'].'",hdate_recv="'.$from['hdate_recv'].'",hcc="'.$from['hcc'].'"'
                .',hbcc="'.$from['hbcc'].'",hsize="'.$from['hsize'].'",hpriority="'.$from['hpriority'].'"'
                .',attachments="'.$from['attachments'].'",`read`="'.$from['read'].'"'
                .',hmessage_id="'.uniqid(time().'.').'@phlymail.local",answered="'.$from['answered'].'"'
                .',forwarded="'.$from['forwarded'].'",bounced="'.$from['bounced'].'"'
                .',struct="'.$from['struct'].'",type="'.$from['type'].'",dsn_sent="1", cached="'.($cached ? 1 : 0).'"'
                .',colour='.($from['colour'] == 'NULL' || $from['colour'] == '0' ? 'NULL' : '"'.$from['colour'].'"')
                .',htmlunblocked="'.$from['htmlunblocked'].'"';
        if ($this->DB->query($query)) {
            // Add to new folder
            $query = 'UPDATE '.$this->DB->Tbl['email_folder'].' SET mailnum=mailnum+1'
                    .',mailsize=mailsize+'.($from['hsize']+0).',unread=unread+'.(1-$is_read)
                    .' WHERE idx='.intval($folder);
            $this->DB->query($query);
            $err = $this->DB->error();
            if ($err) {
                $this->set_error($err);
                return false;
            }
            return true;
        }
        return false;
    }

    /**
     * Add a mail to the index
     *
     * @param  int  ID of the user to add the mail to
     * @param  int  Folder ID to add the mail to
     * @param  array  detailed header and meta information about the mail
     * @return  mixed  new ID of the mail on success, FALSE otherwise
     * @since 0.0.9
     */
    public function mail_add($uid = 0, $folder, $data)
    {
        $query = 'SELECT 1 FROM '.$this->DB->Tbl['email_folder'].' WHERE uid='.intval($uid).' AND idx='.intval($folder).' LIMIT 1';
        list ($fold_exists) = $this->DB->fetchrow($this->DB->query($query));
        if (!$fold_exists) {
            $this->set_error('Folder not owned by this user');
            return false;
        }
        if (!isset($data['status'])) $data['status'] = 0;
        if (!isset($data['type'])) $data['type'] = 'mail';
        if (!isset($data['priority']) || !is_numeric($data['priority'])) $data['priority'] = 3;
        $fields = '';
        foreach (array('uidl' => 'uidl', 'from' => 'hfrom', 'to' => 'hto', 'cc' => 'hcc'
                ,'bcc' => 'hbcc', 'subject' => 'hsubject', 'date_received' => 'hdate_recv'
                ,'date_sent' => 'hdate_sent', 'size' => 'hsize', 'priority' => 'hpriority'
                ,'attachments' => 'attachments', 'type' => 'type', 'status' => 'read'
                ,'answered' => 'answered', 'forwarded' => 'forwarded', 'bounced' => 'bounced'
                ,'struct' => 'struct', 'message_id' => 'hmessage_id', 'ouidl' => 'ouidl'
                ,'profile' => 'profile', 'cached' => 'cached', 'colour' => 'colour'
                ) as $k => $v) {
            $fields .= ',`'.$v.'`="'.(isset($data[$k]) ? $this->DB->escape($data[$k]) : '').'"';
        }
        $is_read = (isset($data['status']) && $data['status']) ? 0 : 1;
        $unseen = (isset($data['unseen']) && !$data['unseen']) ? 0 : 1;
        $query = 'INSERT '.$this->DB->Tbl['email_index'].' SET uid='.intval($uid).', folder_id='.intval($folder).$fields;
        $res = $this->DB->query($query);
        if ($res) {
            $newID = $this->DB->insertid();
            $query = 'UPDATE '.$this->DB->Tbl['email_folder']
                    .' SET mailnum=mailnum+1, mailsize=mailsize+'.intval($data['size'])
                    .',unread=unread+'.(1-$is_read).',unseen="'.$unseen.'"'
                    .' WHERE idx='.intval($folder);
            $this->DB->query($query);
            // If belongs to a thread put relation into DB
            $this->thread_add_item($newID, $data, $uid);
            // return
            return $newID;
        }
        return false;
    }

    /**
     * Automatically adding emails to the internal thread index to allow
     * an quick and comfortable Thread View
     *
     * @param int $id   ID of the mail in the database
     * @param array $header  Message header, at least having Message-ID, References and In-Reply-To
     * @param int $uid  ID of the user this mail (and adjacent threads) belong to
     * @return bool  True, if added to thread, False, if not added to any thread
     * @since 4.3.7
     */
    public function thread_add_item($id, $header, $uid = 0)
    {
        // We need the message ID for safely identifying thread members
        // This does NOT check, whether the message ID is sensible and no duplicate
        if (empty($header['message_id'])) return false;

        $uid = intval($uid);
        // Remove < and > around Message-IDs
        foreach (array('message_id', 'inreplyto', 'references') as $h) {
            if (empty($header[$h])) continue;
            $header[$h] = str_replace(array('><', '<', '>', '  ', '  ', '  '), ' ', trim($header[$h]));
        }
        // Either In-Reply-To or References mail headers should be present (and not empty!)
        if (!empty($header['inreplyto']) || !empty($header['references'])) {
            // Unify header to check
            if (empty($header['references'])) {
                $header['references'] = (!empty($header['inreplyto'])) ? array($header['inreplyto']) : array();
            } else {
                $header['references'] = explode(' ', $header['references']);
            }
            // This mail belongs to the thread, too
            $header['references'][] = $header['message_id'];

            $toAdd = $mIdList = array();
            foreach ($header['references'] as $h) {
                $h = trim($h);
                if (empty($h)) continue; // Do not process empty reference headers!
                $mIdList[] = '"'.$this->DB->escape($h).'"';
                $toAdd[$h] = 1;
            }
            $myThread = false;
            $sql = 'SELECT `thread_id`, `hmessage_id` FROM '.$this->DB->Tbl['email_thread_items']
                    .' WHERE `hmessage_id` IN('.implode(',', $mIdList).') AND `uid`='.$uid;
            $qid = $this->DB->query($sql);
            while ($line = $this->DB->fetchassoc($qid)) {
                $myThread = $line['thread_id'];
                unset($toAdd[$line['hmessage_id']]);
            }
            if (false === $myThread) { // No thread exists yet
                $this->DB->query('INSERT '.$this->DB->Tbl['email_thread'].' SET `uid`='.$uid
                        .',`date_first`="'.$this->DB->escape($header['date_sent']).'"'
                        .',`date_last`="'.$this->DB->escape($header['date_sent']).'"'
                        .',`last_message_id`="'.$this->DB->escape($header['message_id']).'"');
                $myThread = $this->DB->insertid();
            } else { // Updating existing thread
                $this->DB->query('UPDATE '.$this->DB->Tbl['email_thread'].' SET '
                        .'`date_last`="'.$this->DB->escape($header['date_sent']).'"'
                        .',`last_message_id`="'.$this->DB->escape($header['message_id']).'"'
                        .' WHERE `idx='.intval($myThread).' AND `uid`='.$uid);
            }
            // Add mail(s) to the thread
            foreach (array_keys($toAdd) as $msgid) {
                $this->DB->query('INSERT '.$this->DB->Tbl['email_thread_items'].' SET `uid`='.$uid
                        .',`hmessage_id`="'.$this->DB->escape($msgid).'"'
                        .',`thread_id`='.intval($myThread));
            }
            // This mail is known to belong to an index, but maybe the mail index ID is missing
            $this->DB->query('UPDATE '.$this->DB->Tbl['email_thread_items'].' SET `mail_id`='.intval($id)
                    .' WHERE `hmessage_id`="'.$this->DB->escape($header['message_id']).'"'
                    .' AND `thread_id`='.intval($myThread));
        } else {
            $mIdList = array('"'.$this->DB->escape(trim($header['message_id'])).'"');
        }
        // Bind together loose ends ...
        if (!empty($mIdList)) {
            $this->DB->query('UPDATE IGNORE '.$this->DB->Tbl['email_thread_items'].' ti, '.$this->DB->Tbl['email_index'].' i SET ti.`mail_id`=i.idx'
                    .' WHERE ti.`mail_id` IS NULL AND i.hmessage_id!="" AND i.idx IS NOT NULL AND ti.`uid`=i.`uid`'
                    .' AND ti.`hmessage_id`=i.`hmessage_id` AND i.`hmessage_id` IN('.implode(',', $mIdList).')');
        }
        $this->DB->query('UPDATE '.$this->DB->Tbl['email_thread'].' t'
                .' SET t.`known_mails`=(SELECT COUNT(ti.hmessage_id) FROM '.$this->DB->Tbl['email_thread_items'].' ti'
                .' WHERE t.`idx`=ti.`thread_id` AND ti.`mail_id` IS NOT NULL)');
        return true;
    }

    /**
     * Retrieve the list of filters defined for the current user
     * @param  int  user id affected
     * @param  string  One of incoming, outgoing, system
     * @return  array  List of all filters, each line containing an array:
     * - id  int  databse ID of the filter
     * - name  string  user defined name of that filter
     * - active  bool  whether this filter is currently active
     * - layered_id  number of that filter to allow sorting
     * @since 0.3.5
     */
    public function filters_getlist($uid = 0, $type = 'incoming')
    {
    	$return = array();
    	$query = 'SELECT filter `id`,name,active,layered_id,`type` FROM '.$this->DB->Tbl['email_filter']
    	       .' WHERE uid='.intval($uid).($type != 'all' ? ' AND `type`="'.$this->DB->escape($type).'"' : '')
    	       .' ORDER BY layered_id ASC';
    	$qid = $this->DB->query($query);
    	while ($line = $this->DB->fetchassoc($qid)) $return[] = $line;
    	return $return;
    }

    /**
     * Retrieve basic data and all rules for a specific filter
     *
     * @param  int  user id affected
     * @param  int  id of the filter
     * @return  array
     * - id  int  databse ID of the filter
     * - name  string  user defined name of that filter
     * - active  bool  whether this filter is currently active
     * - layered_id  number of that filter to allow sorting
     * - match  string  any|all
     * - move  bool  Whether to move mails matching
     * - move_to  string  Identifier of the target folder (app dependent)
     * - copy  bool  Whether to copy mails matching
     * - copy_to  string  Identifier of the target folder (app dependent)
     * - set_prio  bool  Whether to set a priority for matching mails
     * - new_prio  int  Numeric representation of the priority to set
     * - mark_read  bool  Whether to change the read status of the mail
     * - markread_status  int  1|3|5
     * - set_colour bool Whether to give mails a colour mark
     * - new_colour string HTML complinat hex colour value, e.g. FAFAFA
     * - mark_junk  bool  Whether to mark a matching mail as junk
     * - delete  bool  Whether to delete a matching mail
     * - rules  array  A list of all defined rules with their properties:
     *   - id  int  database ID of the filter rule
     *   - field  string  identifier of the mail header field to match
     *   - operator  string  identifier of the comparison operation to perform
     *   - search  string  String to match
     * @since 0.3.5
     */
    public function filters_getfilter($uid = 0, $filter = 0)
    {
        $query = 'SELECT `filter` as `id`, `name`, `active`, `layered_id`, `match`, `move`, `move_to`, `copy`, `copy_to`, `bounce`, `bounce_to`'
                .',`forward`, `forward_to`,`set_prio`, `new_prio`, `mark_read`, `markread_status`, `set_colour`, `new_colour`, `mark_junk`'
                .',`delete`, `alert_sms`, `sms_to`, `sms_timeframe`, `sms_minpause`, unix_timestamp(`sms_lastuse`) `sms_lastuse`'
                .',`alert_email`, `email_to`, `email_timeframe`, `email_minpause`, unix_timestamp(`email_lastuse`) `email_lastuse`'
                .' FROM '.$this->DB->Tbl['email_filter'].' WHERE `uid`='.intval($uid).' AND `filter`='.intval($filter);
    	$return = $this->DB->fetchassoc($this->DB->query($query));
    	if (!isset($return) || empty($return) || !is_array($return)) return false;
    	$query = 'SELECT `id`, `field`, `operator`, `search` FROM '.$this->DB->Tbl['email_filter_rule'].' WHERE `filter`='.intval($filter);
    	$qid = $this->DB->query($query);
    	while ($line = $this->DB->fetchassoc($qid)) $return['rules'][] = $line;
    	return $return;
    }

    /**
     * Add a filter to the user's filter list
     *
     * @param  int  user id affected
     * @param  array  Payload to add to the database (@see filters_getfilters() for the desired structure, excepting the ID
     *    fields for the filter itself and the rules)
     * @return  bool  TRUE on success, FALSE on any failure
     * @since 0.3.5
     */
    public function filters_addfilter($uid = 0, $filter)
    {
        $parts = array();
        foreach (array('name', 'active', 'type', 'match', 'move', 'move_to', 'copy', 'copy_to', 'bounce', 'bounce_to', 'forward', 'forward_to'
                ,'set_prio', 'new_prio', 'mark_read', 'markread_status', 'set_colour', 'new_colour', 'mark_junk', 'delete', 'alert_sms'
    			,'sms_to', 'sms_timeframe', 'sms_minpause','alert_email', 'email_to', 'email_timeframe', 'email_minpause') as $k) {
            if (!isset($filter[$k])) continue;
    		$parts[] = '`'.$k.'`="'.(isset($filter[$k]) ? $this->DB->escape($filter[$k]) : '').'"';
    	}
    	$query = 'SELECT max(`layered_id`) FROM '.$this->DB->Tbl['email_filter'].' WHERE `uid`='.intval($uid);
    	list ($max) = $this->DB->fetchrow($this->DB->query($query));

    	$query = 'INSERT '.$this->DB->Tbl['email_filter'].' SET `uid`='.intval($uid).',`layered_id`='.($max+1).','.implode(', ', $parts);
    	if (!$this->DB->query($query)) return false;
    	$id = $this->DB->insertid();
    	foreach ($filter['rules'] as $k => $v) {
    		$query = 'INSERT '.$this->DB->Tbl['email_filter_rule'].' SET `field`="'.$this->DB->escape($v['field']).'",`filter`='.$id
    				.',`operator`="'.$this->DB->escape($v['operator']).'",`search`="'.$this->DB->escape($v['search']).'"';
    		if (!$this->DB->query($query)) return false;
    	}
    	return true;
    }

    /**
     * Update a filter
     *
     * @param  int  user id affected
     * @param  array  Payload to add to the database (@see filters_getfilters() for the desired structure)
     * @return  bool  TRUE on success, FALSE on any failure
     * @since 0.3.5
     */
    public function filters_updatefilter($uid = 0,  $filter)
    {
    	$parts = array();
    	foreach (array('name', 'active', 'type', 'match', 'move', 'move_to', 'copy', 'copy_to', 'bounce', 'bounce_to', 'forward', 'forward_to'
                ,'set_prio', 'new_prio', 'mark_read', 'markread_status', 'set_colour', 'new_colour', 'mark_junk', 'delete', 'alert_sms'
    			,'sms_to', 'sms_timeframe', 'sms_minpause','alert_email', 'email_to', 'email_timeframe', 'email_minpause') as $k) {
            if (!isset($filter[$k])) continue;
    		$parts[] = '`'.$k.'`="'.(isset($filter[$k]) ? $this->DB->escape($filter[$k]) : '').'"';
    	}
    	$query = 'UPDATE '.$this->DB->Tbl['email_filter'].' SET '.implode(', ', $parts).' WHERE uid='.intval($uid).' AND filter='.intval($filter['id']);
    	if (!$this->DB->query($query)) return false;
    	$this->DB->query('DELETE FROM '.$this->DB->Tbl['email_filter_rule'].' WHERE filter='.intval($filter['id']));
    	foreach ($filter['rules'] as $k => $v) {
    		$query = 'INSERT '.$this->DB->Tbl['email_filter_rule'].' SET field="'.$this->DB->escape($v['field']).'",filter='.intval($filter['id'])
    				.',operator="'.$this->DB->escape($v['operator']).'",search="'.$this->DB->escape($v['search']).'"';
    		if (!$this->DB->query($query)) return false;
    	}
    	return true;
    }

    /**
     * Remove a certain filter
     *
     * @param  int  user id affected
     * @param  int  id of the filter
     * @return  bool  TRUE on success, FALSE on failure
     * @since 0.3.5
     */
    public function filters_removefilter($uid = 0, $filter)
    {
    	return ($this->DB->query('DELETE FROM '.$this->DB->Tbl['email_filter'].' WHERE uid='.intval($uid).' AND filter='.intval($filter))
    			&& $this->DB->query('DELETE FROM '.$this->DB->Tbl['email_filter_rule'].' WHERE filter='.intval($filter)));
    }

    /**
     * Switch activation state of a filter
     *
     * @param  int  user id affected
     * @param  int  id of the filter
     * @return  bool  TRUE on success, FALSE on failure
     * @since 0.3.8
     */
    public function filters_activatefilter($uid = 0, $filter)
    {
    	return $this->DB->query('UPDATE '.$this->DB->Tbl['email_filter'].' SET active = CONCAT("", 1-active) WHERE uid='.intval($uid).' AND filter='.intval($filter));
    }

    /**
     * Apply a new ordering to the list of filtes
     *
     * @param  int  user id affected
     * @param  int  Filter to get moved around
     * @param  'up'|'down'  Direction to move the filter to
     * @return  bool  TRUE on succes, FALSE on any failure
     * @since 0.3.5
     */
    public function filters_reorder($uid = 0, $filter, $dir)
    {
        $uid = intval($uid);
        $filter = intval($filter);
    	$query = 'SELECT `layered_id`, `type` FROM '.$this->DB->Tbl['email_filter'].' WHERE uid='.$uid.' AND filter='.$filter;
    	list ($cur, $type) = $this->DB->fetchrow($this->DB->query($query));
    	$query = 'SELECT min(layered_id), max(layered_id) FROM '.$this->DB->Tbl['email_filter'].' WHERE uid='.$uid.' AND `type`="'.$type.'"';
    	list ($min, $max) = $this->DB->fetchrow($this->DB->query($query));
    	if ('up' == $dir) {
    		if ($cur == $min) return true;
    		$this->DB->query('UPDATE '.$this->DB->Tbl['email_filter'].' SET layered_id='.$cur.' WHERE layered_id='.($cur-1).' AND `type`="'.$type.'" AND uid='.$uid);
    		$this->DB->query('UPDATE '.$this->DB->Tbl['email_filter'].' SET layered_id='.($cur-1).' WHERE filter='.$filter.' AND `type`="'.$type.'" AND uid='.$uid);
    	} else {
    		if ($cur == $max) return true;
    		$this->DB->query('UPDATE '.$this->DB->Tbl['email_filter'].' SET layered_id='.$cur.' WHERE layered_id='.($cur+1).' AND `type`="'.$type.'" AND uid='.$uid);
    		$this->DB->query('UPDATE '.$this->DB->Tbl['email_filter'].' SET layered_id='.($cur+1).' WHERE filter='.$filter.' AND `type`="'.$type.'" AND uid='.$uid);
    	}
    	return true;
    }

    /**
     * Sets the last use timestamp for a filter, that matched and caused an email / SMS to be sent
     *
     * @param int user id
     * @param int filter id
     * @param email|sms Name of the method (Email or SMS) that was fired
     * @param int Timestamp the action took place
     * @return bool
     * @since 0.4.7
     */
    public function filters_set_lastuse($uid = 0, $filter, $mode = 'email', $time = 0)
    {
        if (!$filter) return true;
        if (!$time || !is_numeric($time)) $time = time();
        $query = 'UPDATE '.$this->DB->Tbl['email_filter'].' SET '.($mode == 'sms' ? 'sms' : 'email').'_lastuse="'.date('Y-m-d H:i:s', $time).'" WHERE filter='.intval($filter).' AND uid='.intval($uid);
        return $this->DB->query($query);
    }

    /**
     * Quota related: Returns the number of folders, the given user has created.
     * For statistical purposes we return the overall amount of folders created, the number of users who did so
     * and the uids of the users with the most and the least folders.
     *
     * @param int $uid  User ID
     * @param bool Whether to return statistics instead of the usage of the currently given user id
     * @return int|array $num Number of user defined local folders created / Statistics array
     * @since 0.7.1
     */
    public function quota_getfoldernum($uid = 0, $stats = false)
    {
        $cnt = $sum = $max_uid = $max_cnt = 0;
        if (false == $stats) {
            $query = 'SELECT count(*) FROM '.$this->DB->Tbl['email_folder'].' WHERE uid='.intval($uid).' AND att_type=1';
            list ($num) = $this->DB->fetchrow($this->DB->query($query));
            return $num;
        }
        $query = 'SELECT count(distinct uid), count(*) FROM '.$this->DB->Tbl['email_folder'].' WHERE att_type=1';
        list ($cnt, $sum) = $this->DB->fetchrow($this->DB->query($query));
        if ($cnt) {
            $query = 'SELECT uid, count(*) moep FROM '.$this->DB->Tbl['email_folder'].' WHERE att_type=1 GROUP BY uid ORDER BY moep DESC LIMIT 1';
            list ($max_uid, $max_cnt) = $this->DB->fetchrow($this->DB->query($query));
        }
        return array('count' => $cnt, 'sum' => $sum, 'max_uid' => $max_uid, 'max_count' => $max_cnt);
    }

    /**
     * Qutoa related: Returns the overall size of all mails this user has stored in his
     * local folders (including the system folders, of course).
     * @param int $uid  User ID
     * @return int $size Size of all mails in bytes
     * @since 0.7.1
     */
    public function quota_getmailsize($uid = 0, $stats = false)
    {
        $cnt = $sum = $max_uid = $max_cnt = 0;
        if (false == $stats) {
            $query = 'SELECT sum(mailsize) FROM '.$this->DB->Tbl['email_folder'].' WHERE uid='.intval($uid).' AND att_type<10';
            list ($size) = $this->DB->fetchrow($this->DB->query($query));
            return $size;
        }
        $query = 'SELECT count(distinct uid), sum(mailsize) FROM '.$this->DB->Tbl['email_folder'].' WHERE att_type<10';
        list ($cnt, $sum) = $this->DB->fetchrow($this->DB->query($query));
        if ($cnt) {
            $query = 'SELECT uid, sum(mailsize) moep FROM '.$this->DB->Tbl['email_folder'].' WHERE att_type<10 GROUP BY uid ORDER BY moep DESC LIMIT 1';
            list ($max_uid, $max_cnt) = $this->DB->fetchrow($this->DB->query($query));
        }
        return array('count' => $cnt, 'sum' => $sum, 'max_uid' => $max_uid, 'max_count' => $max_cnt);
    }

    /**
     * Qutoa related: Returns the number of all mails this user has stored in his
     * local folders (including the system folders, of course).
     * @param int $uid  User ID
     * @return int $size Number of all mails
     * @since 0.7.1
     */
    public function quota_getmailnum($uid = 0, $stats = false)
    {
        $cnt = $sum = $max_uid = $max_cnt = 0;
        if (false == $stats) {
            $query = 'SELECT sum(mailnum) FROM '.$this->DB->Tbl['email_folder'].' WHERE uid='.intval($uid).' AND att_type<10';
            list ($size) = $this->DB->fetchrow($this->DB->query($query));
            return $size;
        }
        $query = 'SELECT count(distinct uid), sum(mailnum) FROM '.$this->DB->Tbl['email_folder'].' WHERE att_type<10';
        list ($cnt, $sum) = $this->DB->fetchrow($this->DB->query($query));
        if ($cnt) {
            $query = 'SELECT uid, sum(mailnum) moep FROM '.$this->DB->Tbl['email_folder'].' WHERE att_type<10 GROUP BY uid ORDER BY moep DESC LIMIT 1';
            list ($max_uid, $max_cnt) = $this->DB->fetchrow($this->DB->query($query));
        }
        return array('count' => $cnt, 'sum' => $sum, 'max_uid' => $max_uid, 'max_count' => $max_cnt);
    }

    /**
     * Does a matching of the currently saved cache with the passed list
     *
     * @param int $profile  Profile ID
     * @param array List of mails to match
     *[@param array List from DB to match against, so nothing gets updated in the DB]
     * @return array Lists after matching:
     * [0] -> new elements
     * [1] -> no longer existant elements
     * @since 0.7.2
     */
    public function uidlcache_match($profile, $maillist, $dblist = false)
    {
        $profile = intval($profile);
        $cached = array();
        $q_r = '';
        if (false !== $dblist) {
            $cached = &$dblist;
        } else {
            $qid = $this->DB->query('SELECT uidl FROM '.$this->DB->Tbl['email_uidlcache'].' WHERE profile='.$profile);
            while (list ($uidl) = $this->DB->fetchrow($qid)) $cached[] = phm_stripslashes($uidl);
        }
        $olist = array_diff($cached, $maillist);
        $nlist = array_diff($maillist, $cached);
        // Drop items from the cache table, which do no longer exist on the mailserver
        if (false === $dblist && !empty($olist)) {
            foreach ($olist as $uidl) { $q_r .= ($q_r ? ',' : '').'"'.$this->DB->escape($uidl).'"'; }
            $this->DB->query('DELETE FROM '.$this->DB->Tbl['email_uidlcache'].' WHERE profile='.$profile.' AND uidl in ('.$q_r.')');
        }
        return array($nlist, $olist);
    }

    /**
     * Adds an item to the UIDL cache
     *
     * @param int $profile
     * @param string $item
     * @return bool
     * @since 0.7.2
     */
    public function uidlcache_additem($profile, $item)
    {
        return $this->DB->query('INSERT '.$this->DB->Tbl['email_uidlcache'].' SET profile='.intval($profile).', uidl="'.$this->DB->escape($item).'"');
    }

    /**
     * Enter description here...
     *
     * @param int $profile
     * @param string $item
     * @return bool  TRUE, if UIDL already in DB, FALSE otherwise
     * @since 0.7.2
     */
    public function uidlcache_checkitem($profile, $item)
    {
        $qid = $this->DB->query('SELECT 1 FROM '.$this->DB->Tbl['email_uidlcache'].' WHERE profile='.intval($profile).' AND uidl="'.$this->DB->escape($item).'" LIMIT 1');
        list ($true) = $this->DB->fetchrow($qid);
        return (1 == $true);
    }

    /**
     * Drops all information about already downloaded UIDLs for a certain profile.
     * This method is used by the fetchers when a profile is switched from "Keep on server" to not doing so.
     *
     * @param int $profile
     * @return bool
     * @since 0.7.2
     */
    public function uidlcache_remove($profile)
    {
        return $this->DB->query('DELETE FROM '.$this->DB->Tbl['email_uidlcache'].' WHERE profile='.intval($profile));
    }

    /**
     * Used for profile, where mails deleted locally shall get deleted on the POP3 server, too.
     *
     * @param int $profile
     * @param string $item
     * @return true
     * @since 0.7.3
     */
    public function uidlcache_markdeleted($profile, $item)
    {
        list ($delonserver) = $this->DB->fetchrow($this->DB->query('SELECT localkillserver FROM '.$this->DB->Tbl['profiles'].' WHERE id='.intval($profile)));
        if (!$delonserver) return true;
        return $this->DB->query('UPDATE '.$this->DB->Tbl['email_uidlcache'].' SET deleted="1" WHERE profile='.intval($profile).' AND uidl="'.$this->DB->escape($item).'"');
    }

    /**
     * Delivers a list of all UIDLs in a given profile, which were deleted locally, thus can get deleted from the server, too.
     * This applies to all POP3 profile which have leaveonserver On and localkillserver On.
     *
     * @param int $profile
     * @return array
     * @since 0.7.3
     */
    public function uidlcache_getdeleted($profile)
    {
        $return = array();
        $qid = $this->DB->query('SELECT uidl FROM '.$this->DB->Tbl['email_uidlcache'].' WHERE profile='.intval($profile).' AND deleted="1"');
        while (list($uidl) = $this->DB->fetchrow($qid)) $return[] = $uidl;
        return $return;
    }

    /**
     * Add a whitelist entry, at least one of the filter flags must be set. IF the
     * filter is already in the system, the filter flags override the stored ones.
     *
     * @param string $filter  Either email address or domain ([local]@domain)
     * @param null|0|1 $html Whether to unblock HTML mails
     * @param null|0|1 $ical Whether to auto process iCal files
     * @param null|0|1 $vcf  Whether to auto process VCFs
     */
    public function whitelist_addfilter($uid, $filter, $html = null, $ical = null, $vcf = null)
    {
        if (is_null($html) && is_null($ical) && is_null($vcf)) return false; // At least flag MUST be set
        if (!strlen($filter) || false === strpos($filter, '@')) return false;

        $qid = $this->DB->query('SELECT id FROM '.$this->DB->Tbl['email_whitelist'].' WHERE `uid`='.intval($uid).' AND `filter`="'.$this->DB->escape($filter).'"');
        if ($this->DB->numrows($qid)) { // Hit!
            $orig = $this->DB->fetchassoc($qid);
            $sql = 'UPDATE '.$this->DB->Tbl['email_whitelist'].' SET `uid`=`uid`';
            if (!is_null($html)) $sql .= ',`htmlunblocked`="'.intval($html).'"';
            if (!is_null($ical)) $sql .= ',`process_cal`="'.intval($ical).'"';
            if (!is_null($vcf))  $sql .= ',`process_vcf`="'.intval($vcf).'"';
            $sql .= ' WHERE `id`='.$orig['id'];
        } else { // New entry
            $sql = 'INSERT INTO '.$this->DB->Tbl['email_whitelist'].' SET `uid`='.intval($uid).', `filter`="'.$this->DB->escape($filter).'"';
            if (!is_null($html)) $sql .= ',`htmlunblocked`="'.intval($html).'"';
            if (!is_null($ical)) $sql .= ',`process_cal`="'.intval($ical).'"';
            if (!is_null($vcf))  $sql .= ',`process_vcf`="'.intval($vcf).'"';
        }
        return $this->DB->query($sql);
    }

    /**
     * Retrieve whitelist filter entry
     *
     * @param int $idr  ID of the filter
     * @return false|array
     */
    public function whitelist_getfilter($uid, $id)
    {
        $qid = $this->DB->query('SELECT * FROM '.$this->DB->Tbl['email_whitelist'].' WHERE `uid`='.intval($uid).' AND `id`='.intval($id));
        if (!$this->DB->numrows($qid)) {
            return false; // Nothing found
        }
        return $this->DB->fetchassoc($qid);
    }

    /**
     * Retrieve whitelist filter list
     * @return array
     */
    public function whitelist_getlist($uid)
    {
        $qid = $this->DB->query('SELECT * FROM '.$this->DB->Tbl['email_whitelist'].' WHERE `uid`='.intval($uid).' ORDER BY `filter` ASC');
        if (!$this->DB->numrows($qid)) {
            return false; // Nothing found
        }
        $return = array();
        while ($line = $this->DB->fetchassoc($qid)) {
            $return[$line['id']] = $line;
        }
        return $return;
    }

    /**
     * Search for whitelist settings for given email address.
     * If there's a specific entry for the address, this is returned.
     * If we have an entry for the domain of the email, this is returned as fallback.
     * That means: The email address always has precedence!
     *
     * @param string $email
     * @return false|array
     */
    public function whitelist_search($uid, $email)
    {
        $dom = explode('@', $email);
        $dom = '@'.$dom[1];
        $qid = $this->DB->query('SELECT * FROM '.$this->DB->Tbl['email_whitelist'].' WHERE `uid`='.intval($uid)
                .' AND `filter` IN("'.$this->DB->escape($email).'","'.$this->DB->escape($dom).'")');
        // Nothing found
        if (!$this->DB->numrows($qid)) return false;
        $res = array();
        while ($line = $this->DB->fetchassoc($qid)) {
            $res[$line['filter']] = $line;
        }
        // Got the specific entry for the email address
        if (isset($res[$email])) return $res[$email];
        // Got a generic entry for the domain
        if (isset($res[$dom])) return $res[$dom];
        // Just in case...
        return $res;
    }

    /**
     * This method checks, whether a user might access a given folder
     *
     * @param int $uid  The user ID
     * @param int $folder  The folder ID
     *[@param string $mode One of 'r' for reading operations, 'w' for writing, 'd' deleting that folder
     *     ,'di' deleting items in the folder; Currently NOT CHECKED]
     * @return bool  TRUE, if access okay; FALSE, if not
     * @since 4.1.6
     */
    protected function check_perm_folder($uid, $folder, $mode = 'r')
    {
        // Bypass in any cases, where previous operations already ruled out the user ID
        if (is_null($uid) || false === $uid) return true;
        // Directly owned by this user?
        $qh = $this->DB->query('SELECT 1 FROM '.$this->DB->Tbl['email_folder'].' WHERE idx='.intval($folder).' AND uid='.intval($uid));
        if ($this->DB->numrows($qh)) return true;
        // Do we have shares at all?
        if (!isset($this->DB->features['shares']) || !$this->DB->features['shares']) return false;
        // Shares directly assigned to the user
        $qh = $this->DB->query('SELECT 1 FROM '.$this->DB->Tbl['share_folder'].' WHERE handler="email" AND fid='.intval($folder).' AND uid='.intval($uid));
        if ($this->DB->numrows($qh)) return true;
        // Do we have groups at all?
        if (!isset($this->DB->features['groups']) || !$this->DB->features['groups']) return false;
        // Any other group share, where the groups the user is in match
        $qh = $this->DB->query('SELECT 1 FROM '.$this->DB->Tbl['share_folder'].' sf, '.$this->DB->Tbl['user_group'].' ug WHERE sf.handler="email" AND sf.fid='.intval($folder).' AND sf.gid=ug.gid AND ug.uid='.intval($uid));
        if ($this->DB->numrows($qh)) return true;
        // None of the above matched
        return false;
    }
}
?>
Return current item: phlyMail Lite