math_cms/scripts/report_teacher_weekly_stats.php

<?php

/**
 * 近 7 天老师组卷 + 学情分析套数（exam_analysis_results 按 paper_id 去重，一套卷计 1）。
 * 按老师：学生数·本/上 = 组卷∪学情 student_id 合并去重，分列本周期与上周期；老师列含 teacher_id，如 龚 (433)。
 * 用法：
 *   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
 *   （shell 里 %H%M%S 会展开为当前时分秒；PDF 见 scripts/report_teacher_weekly_stats_pdf.php）
 */

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;
}

/** 学生数：组卷 ∪ 学情，student_id 合并去重（按老师、时间窗） */
$studentUnionSql = <<<'SQL'
SELECT u.teacher_id, COUNT(DISTINCT u.student_id) AS c
FROM (
    SELECT teacher_id, student_id FROM papers
    WHERE teacher_id IS NOT NULL AND teacher_id != ''
    AND student_id IS NOT NULL AND student_id != ''
    AND created_at >= ? AND created_at < ?
    UNION
    SELECT p.teacher_id, ear.student_id
    FROM exam_analysis_results ear
    INNER JOIN papers p ON p.paper_id = ear.paper_id
    WHERE p.teacher_id IS NOT NULL AND p.teacher_id != ''
    AND ear.student_id IS NOT NULL AND ear.student_id != ''
    AND ear.created_at >= ? AND ear.created_at < ?
) u
GROUP BY u.teacher_id
SQL;

$studentUnionCurRows = $db::select($studentUnionSql, [$startCurrent, $endCurrent, $startCurrent, $endCurrent]);
$studentUnionPrevRows = $db::select($studentUnionSql, [$startPrev, $endPrev, $startPrev, $endPrev]);

$studentUnionCurMap = [];
foreach ($studentUnionCurRows as $row) {
    $studentUnionCurMap[(string) $row->teacher_id] = (int) $row->c;
}
$studentUnionPrevMap = [];
foreach ($studentUnionPrevRows as $row) {
    $studentUnionPrevMap[(string) $row->teacher_id] = (int) $row->c;
}

$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');

/**
 * 将 [from, to) 均分为 7 段，返回每段内组卷数、学情去重套数。
 *
 * @return array{labels: list<string>, papers: list<int>, analysis: list<int>}
 */
$dailySlices = static function (\Carbon\Carbon $from, \Carbon\Carbon $toExclusive) use ($db): array {
    $totalSec = max(1, $toExclusive->getTimestamp() - $from->getTimestamp());
    $sliceSec = $totalSec / 7;
    $labels = [];
    $papers = [];
    $analysis = [];
    for ($i = 0; $i < 7; $i++) {
        $sliceFrom = $from->copy()->addSeconds((int) floor($sliceSec * $i));
        $sliceTo = $i < 6
            ? $from->copy()->addSeconds((int) floor($sliceSec * ($i + 1)))
            : $toExclusive;
        $labels[] = $sliceFrom->format('n/j');
        $papers[] = (int) $db::table('papers')
            ->whereNotNull('teacher_id')
            ->where('teacher_id', '!=', '')
            ->where('created_at', '>=', $sliceFrom)
            ->where('created_at', '<', $sliceTo)
            ->count();
        $analysis[] = (int) $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', '>=', $sliceFrom)
            ->where('ear.created_at', '<', $sliceTo)
            ->distinct()
            ->count('ear.paper_id');
    }

    return ['labels' => $labels, 'papers' => $papers, 'analysis' => $analysis];
};

$curDaily = $dailySlices($startCurrent, $endCurrent);
$prevDaily = $dailySlices($startPrev, $endPrev);

$buildChartSvg = static function (array $curDaily, array $prevDaily): string {
    $labels = $curDaily['labels'];
    $pc = $curDaily['papers'];
    $ac = $curDaily['analysis'];
    $pp = $prevDaily['papers'];
    $ap = $prevDaily['analysis'];
    $maxY = max(1, ...$pc, ...$ac, ...$pp, ...$ap);
    $W = 480;
    $H = 200;
    $padL = 44;
    $padR = 12;
    $padT = 16;
    $padB = 36;
    $gw = $W - $padL - $padR;
    $gh = $H - $padT - $padB;
    $n = 7;
    $xAt = static function (int $i) use ($padL, $gw, $n): float {
        return $padL + ($n <= 1 ? $gw / 2 : $gw * $i / ($n - 1));
    };
    $yAt = static function (int $v) use ($padT, $gh, $maxY): float {
        return $padT + $gh - ($v / $maxY) * $gh;
    };
    $lineWithDots = static function (array $vals, string $stroke, string $dash, float $sw = 1.6) use ($xAt, $yAt, $n): string {
        $pts = [];
        $circles = '';
        for ($i = 0; $i < $n; $i++) {
            $x = $xAt($i);
            $y = $yAt((int) $vals[$i]);
            $pts[] = round($x, 1).','.round($y, 1);
            $circles .= sprintf(
                '<circle cx="%.1f" cy="%.1f" r="3.2" fill="%s" stroke="#fff" stroke-width="1"/>',
                $x,
                $y,
                $stroke
            );
        }

        $poly = implode(' ', $pts);
        $dashAttr = $dash !== '' ? ' stroke-dasharray="'.$dash.'"' : '';

        return '<polyline fill="none" stroke="'.$stroke.'" stroke-width="'.$sw.'"'.$dashAttr.' points="'.$poly.'" />'.$circles;
    };

    $xAxisY = $padT + $gh;
    $tickTxt = '';
    for ($i = 0; $i < $n; $i++) {
        $x = $xAt($i);
        $lab = htmlspecialchars((string) ($labels[$i] ?? ''), ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
        $tickTxt .= sprintf(
            '<text x="%.1f" y="%d" text-anchor="middle" font-size="9" fill="#374151" font-family="sun-exta,sans-serif">%s</text>',
            $x,
            $H - 10,
            $lab
        );
    }

    $yTick = '';
    for ($k = 0; $k <= 4; $k++) {
        $v = (int) round($maxY * $k / 4);
        $y = $yAt($v);
        $yTick .= sprintf(
            '<line x1="%d" y1="%.1f" x2="%d" y2="%.1f" stroke="#e5e7eb" stroke-width="1"/>',
            $padL,
            $y,
            $padL + $gw,
            $y
        );
        $yTick .= sprintf(
            '<text x="%d" y="%.1f" font-size="8" fill="#6b7280" font-family="sun-exta,sans-serif">%d</text>',
            4,
            $y + 3,
            $v
        );
    }

    $legend = '<g font-family="sun-exta,sans-serif" font-size="9">';
    $lx = $padL + $gw - 168;
    $ly = $padT + 4;
    $items = [
        ['#2563eb', '组卷·本周期', ''],
        ['#ea580c', '学情·本周期', ''],
        ['#93c5fd', '组卷·上周期', '6,4'],
        ['#fdba74', '学情·上周期', '6,4'],
    ];
    foreach ($items as $idx => $it) {
        $yy = $ly + $idx * 13;
        $legend .= sprintf(
            '<line x1="%d" y1="%d" x2="%d" y2="%d" stroke="%s" stroke-width="2" %s/>',
            $lx,
            $yy,
            $lx + 18,
            $yy,
            $it[0],
            $it[2] !== '' ? 'stroke-dasharray="'.$it[2].'"' : ''
        );
        $legend .= sprintf(
            '<text x="%d" y="%d" fill="#111">%s</text>',
            $lx + 24,
            $yy + 4,
            htmlspecialchars($it[1], ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8')
        );
    }
    $legend .= '</g>';

    $svg = sprintf(
        '<svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 %d %d" width="100%%" height="auto" style="max-width:520px;">',
        $W,
        $H
    );
    $svg .= sprintf('<rect x="0" y="0" width="%d" height="%d" fill="#fafafa"/>', $W, $H);
    $svg .= $yTick;
    $svg .= sprintf('<line x1="%d" y1="%.1f" x2="%.1f" y2="%.1f" stroke="#9ca3af" stroke-width="1"/>', $padL, $xAxisY, $padL + $gw, $xAxisY);
    $svg .= $lineWithDots($pp, '#93c5fd', '6,4', 1.4);
    $svg .= $lineWithDots($ap, '#fdba74', '6,4', 1.4);
    $svg .= $lineWithDots($pc, '#2563eb', '', 1.8);
    $svg .= $lineWithDots($ac, '#ea580c', '', 1.8);
    $svg .= $tickTxt;
    $svg .= $legend;
    $svg .= '</svg>';

    return $svg;
};

$chartSvg = $buildChartSvg($curDaily, $prevDaily);

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### 近7段每日对比（本周期与上周期时间轴对齐）\n\n";
echo '<div class="weekly-chart">';
echo '<p style="font-size:9pt;color:#4b5563;margin:0 0 6px 0;">横轴为统计窗口均分的 7 段，数字为每段「组卷套数 / 学情分析套数（卷去重）」；实线本周期，虚线上一同期。</p>';
echo $chartSvg;
echo "</div>\n\n";

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

echo '<table class="weekly-teacher-table">';
echo '<colgroup>';
echo '<col style="width:4%" /><col style="width:11%" />';
echo '<col style="width:7%" /><col style="width:7%" /><col style="width:8%" />';
echo '<col class="col-an" style="width:9%" /><col class="col-an" style="width:9%" /><col style="width:8%" />';
echo '<col class="col-stu" style="width:8%" /><col class="col-stu" style="width:8%" />';
echo '</colgroup>';
echo '<thead><tr>';
echo '<th>排名</th><th>老师</th><th>组卷·本</th><th>组卷·上</th><th>组卷·环比</th><th>学情·本</th><th>学情·上</th><th>学情·环比</th><th>学生数·本</th><th>学生数·上</th>';
echo "</tr></thead>\n<tbody>\n";

$i = 1;
foreach ($rows as $r) {
    $tidKey = (string) $r['teacher_id'];
    $nmRaw = (string) $r['name'];
    $nmEsc = htmlspecialchars($nmRaw, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
    $tidEsc = htmlspecialchars($tidKey, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
    $nameWithId = $nmEsc.' <span class="teacher-id">('.$tidEsc.')</span>';
    $pc = $r['papers'];
    $pp = $r['papers_prev'];
    $ac = $r['analysis_sets'];
    $ap = $r['analysis_sets_prev'];
    $stuC = (int) ($studentUnionCurMap[$tidKey] ?? 0);
    $stuP = (int) ($studentUnionPrevMap[$tidKey] ?? 0);
    echo '<tr>';
    echo '<td style="text-align:right">'.((string) $i++).'</td>';
    echo '<td class="td-name">'.$nameWithId.'</td>';
    echo '<td style="text-align:right">'.((string) $pc).'</td>';
    echo '<td style="text-align:right">'.((string) $pp).'</td>';
    echo '<td>'.$compareCellHtml($pc, $pp).'</td>';
    echo '<td style="text-align:right" class="td-an">'.((string) $ac).'</td>';
    echo '<td style="text-align:right" class="td-an">'.((string) $ap).'</td>';
    echo '<td>'.$compareCellHtml($ac, $ap).'</td>';
    echo '<td style="text-align:right" class="td-stu" title="组卷∪学情，学生合并去重">'.$stuC.'</td>';
    echo '<td style="text-align:right" class="td-stu" title="组卷∪学情，学生合并去重">'.$stuP.'</td>';
    echo "</tr>\n";
}
echo "</tbody></table>\n";

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