math_cms/scripts/report_teacher_weekly_stats.php

<?php

/**
 * 近 7 天老师组卷 + 学情分析套数（exam_analysis_results 按 paper_id 去重，一套卷计 1）
 * 用法：
 *   php scripts/report_teacher_weekly_stats.php
 *   php scripts/report_teacher_weekly_stats.php > storage/app/reports/teacher-weekly-stats-$(date +%Y-%m-%d)_$(date +%H%M%S).md
 */

require __DIR__ . '/../vendor/autoload.php';

$app = require_once __DIR__ . '/../bootstrap/app.php';
$app->make(\Illuminate\Contracts\Console\Kernel::class)->bootstrap();

$endCurrent = now();
$startCurrent = now()->subDays(7);
$startPrev = now()->subDays(14);
$endPrev = $startCurrent;

$db = \Illuminate\Support\Facades\DB::class;

$sumPapers = static function ($from, $toExclusive) use ($db) {
    $q = $db::table('papers')
        ->whereNotNull('teacher_id')
        ->where('teacher_id', '!=', '')
        ->where('created_at', '>=', $from);
    if ($toExclusive !== null) {
        $q->where('created_at', '<', $toExclusive);
    }

    return (int) $q->count();
};

/** 学情分析：以卷子为单位，同一 paper_id 在区间内多条记录只计 1 */
$countDistinctAnalysisPapers = static function ($from, $toExclusive) use ($db) {
    $q = $db::table('exam_analysis_results as ear')
        ->join('papers as p', 'p.paper_id', '=', 'ear.paper_id')
        ->whereNotNull('p.teacher_id')
        ->where('p.teacher_id', '!=', '')
        ->where('ear.created_at', '>=', $from);
    if ($toExclusive !== null) {
        $q->where('ear.created_at', '<', $toExclusive);
    }

    return (int) $q->distinct()->count('ear.paper_id');
};

$countActiveTeachers = static function ($from, $toExclusive) use ($db) {
    $q = $db::table('papers')
        ->whereNotNull('teacher_id')
        ->where('teacher_id', '!=', '')
        ->where('created_at', '>=', $from);
    if ($toExclusive !== null) {
        $q->where('created_at', '<', $toExclusive);
    }

    return (int) $q->distinct()->count('teacher_id');
};

$totalPapersCur = $sumPapers($startCurrent, null);
$totalPapersPrev = $sumPapers($startPrev, $endPrev);
$totalAnalysisCur = $countDistinctAnalysisPapers($startCurrent, null);
$totalAnalysisPrev = $countDistinctAnalysisPapers($startPrev, $endPrev);
$teachersCur = $countActiveTeachers($startCurrent, null);
$teachersPrev = $countActiveTeachers($startPrev, $endPrev);

$wowLine = static function (int $cur, int $prev): string {
    $delta = $cur - $prev;
    if ($prev === 0) {
        if ($cur === 0) {
            return '0';
        }

        return sprintf('+%d（上周期0）', $delta);
    }
    $pct = round(($delta / $prev) * 100, 2);
    $sign = $delta >= 0 ? '+' : '';
    $dir = match (true) {
        $delta > 0 => '↑',
        $delta < 0 => '↓',
        default => '→',
    };

    return sprintf('%s%d（%s%.2f%%）%s', $sign, $delta, $sign, $pct, $dir);
};

/** 总量表环比列：仅增长标绿 */
$wowLineHtml = static function (int $cur, int $prev) use ($wowLine): string {
    $plain = $wowLine($cur, $prev);
    if ($cur > $prev) {
        return '<span style="color:#16a34a;font-weight:600;">'.$plain.'</span>';
    }

    return $plain;
};

/** 明细环比列：仅增长标绿 */
$compareCellHtml = static function (int $cur, int $prev): string {
    $d = $cur - $prev;
    if ($d === 0) {
        return '0';
    }
    if ($prev === 0) {
        if ($cur === 0) {
            return '0';
        }

        return '<span style="color:#16a34a;font-weight:600;">+'.$d.'（上0）</span>';
    }
    $pct = round(($d / $prev) * 100, 1);
    $sign = $d > 0 ? '+' : '';
    $text = sprintf('%s%d（%s%.1f%%）', $sign, $d, $sign, $pct);
    if ($d > 0) {
        return '<span style="color:#16a34a;font-weight:600;">'.$text.'</span>';
    }

    return $text;
};

$byTeacher = \Illuminate\Support\Facades\DB::table('papers')
    ->whereNotNull('teacher_id')
    ->where('teacher_id', '!=', '')
    ->where('created_at', '>=', $startCurrent)
    ->selectRaw('teacher_id, COUNT(*) as paper_count')
    ->groupBy('teacher_id')
    ->get();

// 近 7 天产生学情分析的试卷套数：按 ear.paper_id 去重后归到 papers.teacher_id
$analysisByTeacher = \Illuminate\Support\Facades\DB::table('exam_analysis_results as ear')
    ->join('papers as p', 'p.paper_id', '=', 'ear.paper_id')
    ->whereNotNull('p.teacher_id')
    ->where('p.teacher_id', '!=', '')
    ->where('ear.created_at', '>=', $startCurrent)
    ->selectRaw('p.teacher_id, COUNT(DISTINCT ear.paper_id) AS paper_set_count')
    ->groupBy('p.teacher_id')
    ->get();

$analysisMap = [];
foreach ($analysisByTeacher as $r) {
    $analysisMap[(string) $r->teacher_id] = (int) $r->paper_set_count;
}

$paperMap = [];
foreach ($byTeacher as $r) {
    $paperMap[(string) $r->teacher_id] = (int) $r->paper_count;
}

$byTeacherPrev = $db::table('papers')
    ->whereNotNull('teacher_id')
    ->where('teacher_id', '!=', '')
    ->where('created_at', '>=', $startPrev)
    ->where('created_at', '<', $endPrev)
    ->selectRaw('teacher_id, COUNT(*) as paper_count')
    ->groupBy('teacher_id')
    ->get();

$analysisByTeacherPrev = $db::table('exam_analysis_results as ear')
    ->join('papers as p', 'p.paper_id', '=', 'ear.paper_id')
    ->whereNotNull('p.teacher_id')
    ->where('p.teacher_id', '!=', '')
    ->where('ear.created_at', '>=', $startPrev)
    ->where('ear.created_at', '<', $endPrev)
    ->selectRaw('p.teacher_id, COUNT(DISTINCT ear.paper_id) AS paper_set_count')
    ->groupBy('p.teacher_id')
    ->get();

$paperMapPrev = [];
foreach ($byTeacherPrev as $r) {
    $paperMapPrev[(string) $r->teacher_id] = (int) $r->paper_count;
}

$analysisMapPrev = [];
foreach ($analysisByTeacherPrev as $r) {
    $analysisMapPrev[(string) $r->teacher_id] = (int) $r->paper_set_count;
}

$names = \Illuminate\Support\Facades\DB::table('teachers')->pluck('name', 'teacher_id');
$nameStrMap = [];
foreach ($names as $tid => $nm) {
    $nameStrMap[(string) $tid] = $nm;
}

$rows = [];
foreach ($paperMap as $tid => $paperCount) {
    $rows[] = [
        'teacher_id' => $tid,
        'name' => (string) ($nameStrMap[$tid] ?? $tid),
        'papers' => $paperCount,
        'analysis_sets' => (int) ($analysisMap[$tid] ?? 0),
        'papers_prev' => (int) ($paperMapPrev[$tid] ?? 0),
        'analysis_sets_prev' => (int) ($analysisMapPrev[$tid] ?? 0),
    ];
}

usort($rows, static fn ($a, $b) => $b['papers'] <=> $a['papers']);

$windowCur = sprintf(
    '%s ~ %s',
    $startCurrent->format('Y-m-d H:i:s'),
    $endCurrent->format('Y-m-d H:i:s')
);
$windowPrev = sprintf(
    '%s ~ %s',
    $startPrev->format('Y-m-d H:i:s'),
    $endPrev->format('Y-m-d H:i:s')
);

$generatedAt = $endCurrent->format('Y-m-d H:i:s');
$tz = (string) config('app.timezone', 'UTC');

echo "## 老师组卷与学情分析（近7天）\n\n";
echo "> 生成 {$generatedAt} · {$tz} · 本 {$windowCur} · 上 {$windowPrev}\n\n";

echo "### 总量\n\n";
echo "| 指标 | 本周期 | 上周期 | 环比 |\n";
echo "| --- | ---: | ---: | --- |\n";
echo sprintf("| 组卷总套数 | %d | %d | %s |\n", $totalPapersCur, $totalPapersPrev, $wowLineHtml($totalPapersCur, $totalPapersPrev));
echo sprintf("| 学情分析套数（卷去重） | %d | %d | %s |\n", $totalAnalysisCur, $totalAnalysisPrev, $wowLineHtml($totalAnalysisCur, $totalAnalysisPrev));
echo sprintf("| 有组卷老师数 | %d | %d | %s |\n", $teachersCur, $teachersPrev, $wowLineHtml($teachersCur, $teachersPrev));

echo "\n### 按老师\n\n";

echo "| 排名 | 老师 | teacher_id | 组卷·本 | 组卷·上 | 组卷·环比 | 学情·本 | 学情·上 | 学情·环比 |\n";
echo "| ---: | --- | --- | ---: | ---: | --- | ---: | ---: | --- |\n";

$i = 1;
foreach ($rows as $r) {
    $nm = str_replace(['|', "\n", '<', '>'], ['/', ' ', '', ''], $r['name']);
    $pc = $r['papers'];
    $pp = $r['papers_prev'];
    $ac = $r['analysis_sets'];
    $ap = $r['analysis_sets_prev'];
    echo sprintf(
        "| %d | %s | %s | %d | %d | %s | %d | %d | %s |\n",
        $i++,
        $nm,
        $r['teacher_id'],
        $pc,
        $pp,
        $compareCellHtml($pc, $pp),
        $ac,
        $ap,
        $compareCellHtml($ac, $ap)
    );
}

echo sprintf("\n本周期有组卷 **%d** 人。\n", count($rows));