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 ''.$plain.''; } 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 '+'.$d.'（上0）'; } $pct = round(($d / $prev) * 100, 1); $sign = $d > 0 ? '+' : ''; $text = sprintf('%s%d（%s%.1f%%）', $sign, $d, $sign, $pct); if ($d > 0) { return ''.$text.''; } return $text; }; /** 「本 / 上」并列显示；仅当本 > 上时整体标绿 */ $slashPairGreenHtml = static function (int $cur, int $prev): string { $text = $cur.' / '.$prev; if ($cur > $prev) { return ''.$text.''; } 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, papers: list, analysis: list} */ $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( '', $x, $y, $stroke ); } $poly = implode(' ', $pts); $dashAttr = $dash !== '' ? ' stroke-dasharray="'.$dash.'"' : ''; return ''.$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( '%s', $x, $H - 10, $lab ); } $yTick = ''; for ($k = 0; $k <= 4; $k++) { $v = (int) round($maxY * $k / 4); $y = $yAt($v); $yTick .= sprintf( '', $padL, $y, $padL + $gw, $y ); $yTick .= sprintf( '%d', 4, $y + 3, $v ); } $legend = ''; $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( '', $lx, $yy, $lx + 18, $yy, $it[0], $it[2] !== '' ? 'stroke-dasharray="'.$it[2].'"' : '' ); $legend .= sprintf( '%s', $lx + 24, $yy + 4, htmlspecialchars($it[1], ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8') ); } $legend .= ''; $svg = sprintf( ''; 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 '

'; echo $chartSvg; echo "

\n\n"; echo "### 按老师\n\n"; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo "\n\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.' ('.$tidEsc.')'; $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 ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo "\n"; } echo "

排名	老师	学案数量	学案·环比	分析数量	学情·环比	学生数
'.((string) $i++).'	'.$nameWithId.'	'.$slashPairGreenHtml($pc, $pp).'	'.$compareCellHtml($pc, $pp).'	'.$slashPairGreenHtml($ac, $ap).'	'.$compareCellHtml($ac, $ap).'	'.$slashPairGreenHtml($stuC, $stuP).'

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