sbnews/server/app/adminapi/lists/community/CommunityCommentLists.php

<?php

namespace app\adminapi\lists\community;

use app\adminapi\lists\BaseAdminDataLists;
use app\common\lists\ListsSearchInterface;
use think\facade\Db;

class CommunityCommentLists extends BaseAdminDataLists implements ListsSearchInterface
{
    protected string $prefix = '';

    public function setSearch(): array
    {
        return [];
    }

    public function lists(): array
    {
        [$sql, $bind] = $this->buildUnionSql();
        if ($sql === '') {
            return [];
        }

        $lists = Db::query(
            "SELECT * FROM ({$sql}) comment_union ORDER BY create_time DESC, id DESC LIMIT {$this->limitOffset}, {$this->limitLength}",
            $bind
        );

        foreach ($lists as &$item) {
            $item['nickname'] = $item['nickname'] ?: '-';
            $item['account'] = $item['account'] ?: '-';
            $item['target_owner_nickname'] = $item['target_owner_nickname'] ?: '-';
            $item['target_summary'] = $item['target_summary'] ?: '-';
            $item['create_time'] = is_numeric($item['create_time']) ? date('Y-m-d H:i:s', (int) $item['create_time']) : $item['create_time'];
            $item['is_virtual'] = (int) $item['is_virtual'];
            $item['status'] = (int) $item['status'];
        }

        return $lists;
    }

    public function count(): int
    {
        [$sql, $bind] = $this->buildUnionSql();
        if ($sql === '') {
            return 0;
        }
        $result = Db::query("SELECT COUNT(*) AS count FROM ({$sql}) comment_union", $bind);
        return (int) ($result[0]['count'] ?? 0);
    }

    protected function buildUnionSql(): array
    {
        $commentType = trim((string) ($this->params['comment_type'] ?? ''));
        $sqlParts = [];
        $bind = [];

        if ($commentType === '' || $commentType === 'article') {
            [$articleSql, $articleBind] = $this->buildArticleSql();
            $sqlParts[] = $articleSql;
            $bind = array_merge($bind, $articleBind);
        }

        if ($commentType === '' || $commentType === 'post') {
            [$postSql, $postBind] = $this->buildPostSql();
            $sqlParts[] = $postSql;
            $bind = array_merge($bind, $postBind);
        }

        return [implode(' UNION ALL ', $sqlParts), $bind];
    }

    protected function buildArticleSql(): array
    {
        $articleCommentTable = $this->table('article_comment');
        $articleTable = $this->table('article');
        $userTable = $this->table('user');
        $where = ['c.delete_time IS NULL'];
        $bind = [];
        $targetId = (int) ($this->params['target_id'] ?? 0);
        $status = $this->params['status'] ?? '';
        $isVirtual = $this->params['is_virtual'] ?? '';
        $keyword = trim((string) ($this->params['keyword'] ?? ''));

        if ($targetId > 0) {
            $where[] = 'c.article_id = ?';
            $bind[] = $targetId;
        }
        if ($status !== '') {
            $where[] = 'c.is_show = ?';
            $bind[] = (int) $status;
        }
        if ($isVirtual !== '') {
            $where[] = (int) $isVirtual === 1
                ? "IFNULL(u.account, '') LIKE 'ai_comment_%'"
                : "IFNULL(u.account, '') NOT LIKE 'ai_comment_%'";
        }
        if ($keyword !== '') {
            $like = '%' . $keyword . '%';
            $where[] = '(c.content LIKE ? OR IFNULL(a.title, \'\') LIKE ? OR IFNULL(a.author, \'\') LIKE ? OR IFNULL(u.nickname, \'\') LIKE ? OR IFNULL(u.account, \'\') LIKE ?)';
            array_push($bind, $like, $like, $like, $like, $like);
        }

        $sql = "
            SELECT
                c.id,
                'article' AS comment_type,
                '资讯评论' AS source_type_desc,
                c.article_id AS target_id,
                COALESCE(a.title, CONCAT('文章#', c.article_id)) AS target_title,
                LEFT(REPLACE(REPLACE(IFNULL(a.`desc`, ''), '\r', ' '), '\n', ' '), 60) AS target_summary,
                IFNULL(a.author, '-') AS target_owner_nickname,
                c.user_id,
                IFNULL(u.nickname, '-') AS nickname,
                IFNULL(u.account, '') AS account,
                CASE WHEN IFNULL(u.account, '') LIKE 'ai_comment_%' THEN 1 ELSE 0 END AS is_virtual,
                CASE WHEN IFNULL(u.account, '') LIKE 'ai_comment_%' THEN '虚拟评论' ELSE '真实评论' END AS user_type_desc,
                c.content,
                c.like_count,
                c.is_show AS status,
                c.create_time
            FROM {$articleCommentTable} c
            LEFT JOIN {$articleTable} a ON a.id = c.article_id
            LEFT JOIN {$userTable} u ON u.id = c.user_id
            WHERE " . implode(' AND ', $where);

        return [$sql, $bind];
    }

    protected function buildPostSql(): array
    {
        $communityCommentTable = $this->table('community_comment');
        $communityPostTable = $this->table('community_post');
        $userTable = $this->table('user');
        $where = ['c.delete_time IS NULL'];
        $bind = [];
        $targetId = (int) ($this->params['target_id'] ?? 0);
        $status = $this->params['status'] ?? '';
        $isVirtual = $this->params['is_virtual'] ?? '';
        $keyword = trim((string) ($this->params['keyword'] ?? ''));

        if ($targetId > 0) {
            $where[] = 'c.post_id = ?';
            $bind[] = $targetId;
        }
        if ($status !== '') {
            $where[] = 'c.status = ?';
            $bind[] = (int) $status;
        }
        if ($isVirtual !== '') {
            $where[] = (int) $isVirtual === 1
                ? "IFNULL(u.account, '') LIKE 'ai_comment_%'"
                : "IFNULL(u.account, '') NOT LIKE 'ai_comment_%'";
        }
        if ($keyword !== '') {
            $like = '%' . $keyword . '%';
            $where[] = '(c.content LIKE ? OR IFNULL(p.content, \'\') LIKE ? OR IFNULL(post_user.nickname, \'\') LIKE ? OR IFNULL(u.nickname, \'\') LIKE ? OR IFNULL(u.account, \'\') LIKE ?)';
            array_push($bind, $like, $like, $like, $like, $like);
        }

        $sql = "
            SELECT
                c.id,
                'post' AS comment_type,
                '社区评论' AS source_type_desc,
                c.post_id AS target_id,
                CONCAT('帖子#', c.post_id) AS target_title,
                LEFT(REPLACE(REPLACE(IFNULL(p.content, ''), '\r', ' '), '\n', ' '), 60) AS target_summary,
                IFNULL(post_user.nickname, '-') AS target_owner_nickname,
                c.user_id,
                IFNULL(u.nickname, '-') AS nickname,
                IFNULL(u.account, '') AS account,
                CASE WHEN IFNULL(u.account, '') LIKE 'ai_comment_%' THEN 1 ELSE 0 END AS is_virtual,
                CASE WHEN IFNULL(u.account, '') LIKE 'ai_comment_%' THEN '虚拟评论' ELSE '真实评论' END AS user_type_desc,
                c.content,
                c.like_count,
                c.status,
                c.create_time
            FROM {$communityCommentTable} c
            LEFT JOIN {$communityPostTable} p ON p.id = c.post_id
            LEFT JOIN {$userTable} u ON u.id = c.user_id
            LEFT JOIN {$userTable} post_user ON post_user.id = p.user_id
            WHERE " . implode(' AND ', $where);

        return [$sql, $bind];
    }

    protected function table(string $name): string
    {
        if (empty($this->prefix)) {
            $this->prefix = env('database.prefix', 'la_');
        }
        return sprintf('`%s%s`', $this->prefix, $name);
    }
}