math_cms/app/Services/KnowledgePointQuestionStatsService.php

<?php

namespace App\Services;

use App\Models\Textbook;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

/**
 * 知识点题量统计：正式库 questions、待入库 questions_tem（剔除与 questions 题干重复）
 */
class KnowledgePointQuestionStatsService
{
    /** 下学期教材 semester 值（与 textbooks.semester 一致，默认 2） */
    public static function textbookSemesterForOrdering(): int
    {
        return (int) config('question_bank.kp_stats_semester', 2);
    }

    /**
     * 按年级下→教材章节顺序得到 kp_code → 排序权重（越小越靠前）
     *
     * @return array<string, int>
     */
    public function buildKpOrderFromTextbooks(): array
    {
        if (! Schema::hasTable('textbooks') || ! Schema::hasTable('textbook_catalog_nodes')) {
            return [];
        }

        $semester = self::textbookSemesterForOrdering();
        $textbookIds = Textbook::query()
            ->where('semester', $semester)
            ->orderBy('grade')
            ->orderBy('sort_order')
            ->orderBy('id')
            ->pluck('id')
            ->all();

        if ($textbookIds === []) {
            return [];
        }

        $diagnostic = app(DiagnosticChapterService::class);
        $order = 0;
        $map = [];
        $seen = [];

        foreach ($textbookIds as $tid) {
            $codes = $diagnostic->getTextbookKnowledgePointsInOrder((int) $tid);
            foreach ($codes as $kp) {
                if ($kp === '' || $kp === null) {
                    continue;
                }
                if (isset($seen[$kp])) {
                    continue;
                }
                $seen[$kp] = true;
                $map[$kp] = $order++;
            }
        }

        return $map;
    }

    /**
     * @return array<string, int> kp_code => questions 表题目数
     */
    public function questionsCountByKp(): array
    {
        if (! Schema::hasTable('questions')) {
            return [];
        }

        return DB::table('questions')
            ->selectRaw('kp_code, COUNT(*) as c')
            ->whereNotNull('kp_code')
            ->where('kp_code', '!=', '')
            ->groupBy('kp_code')
            ->pluck('c', 'kp_code')
            ->map(fn ($c) => (int) $c)
            ->toArray();
    }

    /**
     * questions_tem 中「与 questions 同 kp + 同题干」不重复的题目数，按 kp_code
     *
     * @return array<string, int>
     */
    public function temNonDuplicateCountByKp(): array
    {
        if (! Schema::hasTable('questions_tem') || ! Schema::hasTable('questions')) {
            return [];
        }

        $hasContent = Schema::hasColumn('questions_tem', 'content');
        $stemExpr = $hasContent
            ? 'IFNULL(NULLIF(TRIM(t.stem), \'\'), t.content)'
            : 'TRIM(t.stem)';

        $rows = DB::select(
            "SELECT t.kp_code AS kp_code, COUNT(*) AS c
             FROM questions_tem AS t
             WHERE t.kp_code IS NOT NULL AND t.kp_code != ''
             AND NOT EXISTS (
                 SELECT 1 FROM questions AS q
                 WHERE q.kp_code = t.kp_code
                 AND q.stem = ({$stemExpr})
             )
             GROUP BY t.kp_code"
        );

        $out = [];
        foreach ($rows as $row) {
            $out[(string) $row->kp_code] = (int) $row->c;
        }

        return $out;
    }

    /**
     * @return list<array{
     *   kp_code: string,
     *   kp_name: string,
     *   questions_count: int,
     *   tem_non_duplicate_count: int,
     *   sort_order: int
     * }>
     */
    public function buildRows(): array
    {
        $orderMap = $this->buildKpOrderFromTextbooks();
        $qCounts = $this->questionsCountByKp();
        $temCounts = $this->temNonDuplicateCountByKp();

        $kpCodes = array_unique(array_merge(
            array_keys($qCounts),
            array_keys($temCounts),
            Schema::hasTable('knowledge_points')
                ? DB::table('knowledge_points')->pluck('kp_code')->all()
                : []
        ));
        sort($kpCodes);

        $names = [];
        if (Schema::hasTable('knowledge_points')) {
            $names = DB::table('knowledge_points')->pluck('name', 'kp_code')->toArray();
        }

        $unmappedBase = 1_000_000;
        $rows = [];

        foreach ($kpCodes as $kp) {
            if ($kp === '' || $kp === null) {
                continue;
            }
            $kp = (string) $kp;
            $qc = (int) ($qCounts[$kp] ?? 0);
            $tc = (int) ($temCounts[$kp] ?? 0);
            if ($qc === 0 && $tc === 0) {
                continue;
            }

            $rows[] = [
                'kp_code' => $kp,
                'kp_name' => trim((string) ($names[$kp] ?? '')),
                'questions_count' => $qc,
                'tem_non_duplicate_count' => $tc,
                'sort_order' => $orderMap[$kp] ?? ($unmappedBase + (crc32($kp) % 100_000)),
            ];
        }

        usort($rows, function ($a, $b) {
            if ($a['sort_order'] !== $b['sort_order']) {
                return $a['sort_order'] <=> $b['sort_order'];
            }
            if ($a['questions_count'] !== $b['questions_count']) {
                return $a['questions_count'] <=> $b['questions_count'];
            }

            return strcmp($a['kp_code'], $b['kp_code']);
        });

        return $rows;
    }

    /**
     * Markdown 表格（含标题与说明）
     */
    public function toMarkdownTable(array $rows): string
    {
        $sem = self::textbookSemesterForOrdering();
        $lines = [];
        $lines[] = '# 知识点题量统计';
        $lines[] = '';
        $lines[] = sprintf(
            '- 排序：教材 **semester=%d**（默认下学期）按年级与章节关联知识点顺序优先，其次 **questions 题量升序**。',
            $sem
        );
        $lines[] = '- **tem 待入库（不重复）**：`questions_tem` 中与 `questions` 同 `kp_code` 且题干一致者不重复计数。';
        $lines[] = '';

        $lines[] = '| 知识点 ID | 知识点名称 | questions 题目数 | questions_tem 待入库（不含与 questions 重复） |';
        $lines[] = '| --- | --- | ---: | ---: |';

        foreach ($rows as $r) {
            $name = str_replace('|', '\\|', $r['kp_name'] !== '' ? $r['kp_name'] : '—');
            $lines[] = sprintf(
                '| `%s` | %s | %d | %d |',
                $r['kp_code'],
                $name,
                $r['questions_count'],
                $r['tem_non_duplicate_count']
            );
        }

        $lines[] = '';

        $sumQ = array_sum(array_column($rows, 'questions_count'));
        $sumT = array_sum(array_column($rows, 'tem_non_duplicate_count'));
        $lines[] = sprintf('**合计**：questions %d 题；questions_tem（不重复）%d 题。', $sumQ, $sumT);
        $lines[] = '';

        return implode("\n", $lines);
    }
}