Добавляем поиск по телу писем в Roundcube

Без плагинов, своими руками, с ограничением по пользователю и iframe сверху

📦 Шаг 1: Подготовка базы данных

Создай таблицу для хранения индексированных писем:

CREATE TABLE IF NOT EXISTS email_index (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    path VARCHAR(512) NOT NULL,
    subject TEXT,
    from_email TEXT,
    body LONGTEXT,
    message_id VARCHAR(255),
    file_name VARCHAR(255),
    uid INT UNSIGNED DEFAULT NULL,
    FULLTEXT (body)
);

⚙️ Шаг 2: Парсер Maildir + MySQL

Сохраните этот скрипт как /usr/local/bin/maildir_multiuser_indexer.py:

# maildir_multiuser_indexer.py
#!/opt/maildir_venv/bin/python
import os
import email
from email.parser import BytesParser
from email import policy
import mysql.connector
import chardet

MAILDIR_DOMAIN_PATH = "/var/mail/ваш_домен.ru"
DB_CONFIG = {
    "host": "localhost",
    "user": "roundcube",
    "password": "roundcube123456",
    "database": "roundcubemail"
}

def get_decoded_body(msg):
    if msg.is_multipart():
        for part in msg.walk():
            if part.get_content_type() == "text/plain":
                payload = part.get_payload(decode=True)
                if payload:
                    encoding = chardet.detect(payload)['encoding'] or 'utf-8'
                    return payload.decode(encoding, errors='replace')
    else:
        payload = msg.get_payload(decode=True)
        if payload:
            encoding = chardet.detect(payload)['encoding'] or 'utf-8'
            return payload.decode(encoding, errors='replace')
    return ""

def connect_db():
    return mysql.connector.connect(**DB_CONFIG)

def already_indexed(cursor, file_path):
    cursor.execute("SELECT 1 FROM email_index WHERE path = %s", (file_path,))
    return cursor.fetchone() is not None

def index_mail_file(cursor, file_path, user_id):
    try:
        with open(file_path, 'rb') as f:
            raw_email = f.read()
        msg = BytesParser(policy=policy.default).parsebytes(raw_email)
        subject = msg['subject']
        from_email = msg['from']
        body = get_decoded_body(msg)
        message_id = msg['message-id'] or ''
        file_name = os.path.basename(file_path)

        if not body:
            print(f"⚠️ Не удалось извлечь текст из {file_path}")
            return

        cursor.execute("""
            INSERT INTO email_index (user_id, path, subject, from_email, body, message_id, file_name)
            VALUES (%s, %s, %s, %s, %s, %s, %s)
        """, (user_id, file_path, subject, from_email, body, message_id, file_name))

        print(f"✅ Проиндексировано: {file_path}")

    except Exception as e:
        print(f"❌ Ошибка при обработке {file_path}: {e}")

def get_user_id(cursor, email_address):
    cursor.execute("SELECT user_id FROM users WHERE username = %s LIMIT 1", (email_address,))
    result = cursor.fetchone()
    return result[0] if result else None

def main():
    db = connect_db()
    cursor = db.cursor()

    indexed_count = 0

    if not os.path.exists(MAILDIR_DOMAIN_PATH):
        print(f"❌ Папка домена не найдена: {MAILDIR_DOMAIN_PATH}")
        return

    print("\n📬 Обработка всех пользователей домена")

    for user_email_dir in os.listdir(MAILDIR_DOMAIN_PATH):
        user_email_path = os.path.join(MAILDIR_DOMAIN_PATH, user_email_dir)
        if not os.path.isdir(user_email_path):
            continue

        print(f"\n📧 Обработка пользователя: {user_email_dir}")

        user_id = get_user_id(cursor, user_email_dir)
        if not user_id:
            print(f"❌ Пользователь {user_email_dir} не найден в базе")
            continue

        for folder in ["cur", "new"]:
            folder_path = os.path.join(user_email_path, folder)
            if not os.path.exists(folder_path):
                continue

            print(f"📂 Читаю папку: {folder_path}")

            for filename in os.listdir(folder_path):
                file_path = os.path.join(folder_path, filename)
                if not os.path.isfile(file_path):
                    continue

                if already_indexed(cursor, file_path):
                    continue

                index_mail_file(cursor, file_path, user_id)
                indexed_count += 1

    db.commit()
    cursor.close()
    db.close()

    print(f"\n✅ Индексация завершена. Новых писем: {indexed_count}")

if __name__ == "__main__":
    main()

🛠 Запуск парсера

sudo -u www-data /opt/maildir_venv/bin/python /usr/local/bin/maildir_multiuser_indexer.py

🔐 Шаг 3: Сохраняем логин пользователя при входе в Roundcube

Правим файл:

После строки:

$_SESSION['user_id'] = $user_id;

Добавляем:

file_put_contents("/tmp/roundcube_login_{$_SERVER['REMOTE_ADDR']}.txt", $this->get_user_name());

🔎 Шаг 4: PHP-скрипт поиска — `search_email_best.php`

<?php
$ip = $_SERVER['REMOTE_ADDR'];
$login_file = "/tmp/roundcube_login_{$ip}.txt";

if (!file_exists($login_file)) {
    die("❌ Вы не авторизованы");
}
$email = trim(file_get_contents($login_file));

try {
    $pdo = new PDO("mysql:host=localhost;dbname=roundcubemail;charset=utf8mb4", "roundcube", "roundcube123");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (\PDOException $e) {
    die('Ошибка подключения к БД: ' . $e->getMessage());
}

$search = $_GET['q'] ?? '';
$results = [];

if ($search && strlen($search) >= 3) {
    $stmt = $pdo->prepare("
        SELECT
            ei.id,
            u.username AS email,
            ei.subject,
            ei.from_email,
            ei.body,
            MATCH(ei.body) AGAINST(:search IN NATURAL LANGUAGE MODE) AS relevance
        FROM email_index ei
        JOIN users u ON ei.user_id = u.user_id
        WHERE MATCH(ei.body) AGAINST(:search IN NATURAL LANGUAGE MODE)
          AND u.username = :email
        ORDER BY relevance DESC
        LIMIT 50
    ");
    $stmt->execute([':search' => $search, ':email' => $email]);
    $results = $stmt->fetchAll();
}

🧩 Шаг 5: Встраиваем поиск как iframe в Roundcube

Редактируем тему Roundcube:

/var/www/html/webmail/skins/elastic/templates/mail.html

Вставляем перед

<iframe src="/webmail/search_email_best.php"
         style="width:100%; height:150px; border:none;"
         scrolling="yes">
</iframe>

0 2 * * * sudo -u www-data /opt/maildir_venv/bin/python /usr/local/bin/maildir_multiuser_indexer.py