629 lines
31 KiB
PHP
629 lines
31 KiB
PHP
<?php
|
||
|
||
namespace App\Http\Controllers\Admin;
|
||
|
||
use App\Http\Controllers\Controller;
|
||
use App\Models\AbsensiKegiatan;
|
||
use App\Models\Kegiatan;
|
||
use App\Models\KategoriKegiatan;
|
||
use App\Models\Santri;
|
||
use App\Models\Kelas;
|
||
use App\Models\KelompokKelas;
|
||
use App\Models\SantriKelas;
|
||
use Illuminate\Http\Request;
|
||
use Illuminate\Support\Facades\DB;
|
||
use Illuminate\Support\Facades\Cache;
|
||
use Carbon\Carbon;
|
||
|
||
class LaporanKegiatanController extends Controller
|
||
{
|
||
// ════════════════════════════════════════════════════════
|
||
// A. INDEX
|
||
// ════════════════════════════════════════════════════════
|
||
public function index(Request $request)
|
||
{
|
||
$periode = $request->get('periode', 'minggu_ini');
|
||
[$startDate, $endDate] = $this->getPeriodeRange($periode, $request);
|
||
[$prevStart, $prevEnd] = $this->getPreviousPeriodeRange($periode, $startDate, $endDate);
|
||
$periodeLabel = $this->getPeriodeLabel($periode, $startDate, $endDate);
|
||
|
||
$kpi = $this->calculateKpi($startDate, $endDate);
|
||
$kpiPrev = $this->calculateKpi($prevStart, $prevEnd);
|
||
|
||
$kpiComparison = [
|
||
'avg_kehadiran' => round($kpi['avg_kehadiran'] - $kpiPrev['avg_kehadiran'], 1),
|
||
'santri_perlu_perhatian' => $kpi['santri_perlu_perhatian'] - $kpiPrev['santri_perlu_perhatian'],
|
||
];
|
||
|
||
$trendData = $this->getTrendData($startDate, $endDate);
|
||
$topKegiatan = $this->getTopBottomKegiatan($startDate, $endDate, 'top', 5);
|
||
$bottomKegiatan = $this->getTopBottomKegiatan($startDate, $endDate, 'bottom', 5);
|
||
$patterns = Cache::remember('laporan_patterns_' . now()->format('YmdH'), 3600, fn() => $this->buildPatterns());
|
||
$santriPerluPerhatianList = $this->getSantriPerluPerhatianList($startDate, $endDate, 8);
|
||
$kelasRingkasan = $this->getKelasRingkasan($startDate, $endDate);
|
||
$distribusiSantri = $this->getDistribusiSantri($startDate, $endDate);
|
||
|
||
return view('admin.kegiatan.laporan.index', compact(
|
||
'periode', 'startDate', 'endDate', 'periodeLabel',
|
||
'kpi', 'kpiComparison',
|
||
'trendData', 'topKegiatan', 'bottomKegiatan',
|
||
'patterns', 'santriPerluPerhatianList',
|
||
'kelasRingkasan', 'distribusiSantri'
|
||
));
|
||
}
|
||
|
||
// ════════════════════════════════════════════════════════
|
||
// B. DETAIL SANTRI → redirect
|
||
// ════════════════════════════════════════════════════════
|
||
public function detailSantri($id_santri, Request $request)
|
||
{
|
||
return redirect()->route('admin.riwayat-kegiatan.detail-santri', $id_santri)
|
||
->with('info', 'Detail santri tersedia di Riwayat Kegiatan.');
|
||
}
|
||
|
||
// ════════════════════════════════════════════════════════
|
||
// C. ANALISIS KEGIATAN → redirect
|
||
// ════════════════════════════════════════════════════════
|
||
public function analisKegiatan($kegiatan_id, Request $request)
|
||
{
|
||
$kegiatan = Kegiatan::where('kegiatan_id', $kegiatan_id)->firstOrFail();
|
||
return redirect()->route('admin.riwayat-kegiatan.show', $kegiatan->id)
|
||
->with('info', 'Detail kegiatan tersedia di Riwayat Kegiatan.');
|
||
}
|
||
|
||
// ════════════════════════════════════════════════════════
|
||
// D. SANTRI PERLU PERHATIAN (halaman penuh)
|
||
// ════════════════════════════════════════════════════════
|
||
public function santriPerluPerhatian(Request $request)
|
||
{
|
||
[$startDate, $endDate] = $this->getPeriodeRange($request->get('periode', 'bulan_ini'), $request);
|
||
$periodeLabel = $this->getPeriodeLabel($request->get('periode', 'bulan_ini'), $startDate, $endDate);
|
||
$totalSantriAktif = Santri::where('status', 'Aktif')->count();
|
||
|
||
$query = AbsensiKegiatan::whereBetween('tanggal', [$startDate, $endDate])
|
||
->join('santris', 'absensi_kegiatans.id_santri', '=', 'santris.id_santri')
|
||
->where('santris.status', 'Aktif')
|
||
->select(
|
||
'santris.id_santri', 'santris.nama_lengkap',
|
||
DB::raw('COUNT(*) as total'),
|
||
DB::raw('SUM(CASE WHEN absensi_kegiatans.status IN ("Hadir","Terlambat") THEN 1 ELSE 0 END) as hadir'),
|
||
DB::raw('SUM(CASE WHEN absensi_kegiatans.status = "Terlambat" THEN 1 ELSE 0 END) as terlambat'),
|
||
DB::raw('SUM(CASE WHEN absensi_kegiatans.status = "Alpa" THEN 1 ELSE 0 END) as alpa'),
|
||
DB::raw('SUM(CASE WHEN absensi_kegiatans.status = "Izin" THEN 1 ELSE 0 END) as izin'),
|
||
DB::raw('SUM(CASE WHEN absensi_kegiatans.status = "Sakit" THEN 1 ELSE 0 END) as sakit'),
|
||
DB::raw('ROUND(SUM(CASE WHEN absensi_kegiatans.status IN ("Hadir","Terlambat") THEN 1 ELSE 0 END)/COUNT(*)*100,1) as persen')
|
||
)
|
||
->groupBy('santris.id_santri', 'santris.nama_lengkap')
|
||
->having('persen', '<', 70)
|
||
->orderBy('persen', 'asc');
|
||
|
||
if ($request->filled('id_kelas')) {
|
||
$sIds = SantriKelas::where('id_kelas', $request->id_kelas)->pluck('id_santri');
|
||
$query->whereIn('santris.id_santri', $sIds);
|
||
}
|
||
|
||
$santris = $query->paginate(20)->appends(request()->query());
|
||
$kelasList = Kelas::active()->ordered()->with('kelompok')->get();
|
||
|
||
return view('admin.kegiatan.laporan.santri-perlu-perhatian', compact(
|
||
'santris', 'kelasList', 'startDate', 'endDate',
|
||
'periodeLabel', 'totalSantriAktif'
|
||
));
|
||
}
|
||
|
||
// ════════════════════════════════════════════════════════
|
||
// E. PATTERN DETECTION
|
||
// ════════════════════════════════════════════════════════
|
||
public function patternDetection(Request $request)
|
||
{
|
||
$patterns = $this->buildPatterns();
|
||
if ($request->ajax()) return response()->json($patterns);
|
||
return $patterns;
|
||
}
|
||
|
||
// ════════════════════════════════════════════════════════
|
||
// F. EXPORT CSV
|
||
// ════════════════════════════════════════════════════════
|
||
public function exportExcel(Request $request)
|
||
{
|
||
[$startDate, $endDate] = $this->getPeriodeRange($request->get('periode', 'bulan_ini'), $request);
|
||
$filename = 'laporan_kegiatan_' . $startDate->format('Ymd') . '_' . $endDate->format('Ymd') . '.csv';
|
||
|
||
return response()->streamDownload(function () use ($startDate, $endDate) {
|
||
$handle = fopen('php://output', 'w');
|
||
fprintf($handle, chr(0xEF) . chr(0xBB) . chr(0xBF));
|
||
|
||
$kpi = $this->calculateKpi($startDate, $endDate);
|
||
|
||
fputcsv($handle, ['=== RINGKASAN LAPORAN KEGIATAN ===']);
|
||
fputcsv($handle, ['Periode', $startDate->format('d/m/Y') . ' s/d ' . $endDate->format('d/m/Y')]);
|
||
fputcsv($handle, ['Total Kegiatan Unik', $kpi['total_kegiatan']]);
|
||
fputcsv($handle, ['Total Absensi Tercatat', $kpi['total_absensi']]);
|
||
fputcsv($handle, ['Hadir Efektif', $kpi['total_hadir'] . ' dari ' . $kpi['total_absensi']]);
|
||
fputcsv($handle, ['Rata-rata Kehadiran', $kpi['avg_kehadiran'] . '%']);
|
||
fputcsv($handle, ['Santri Perlu Perhatian', $kpi['santri_perlu_perhatian'] . ' dari ' . $kpi['total_santri_aktif'] . ' santri aktif']);
|
||
fputcsv($handle, []);
|
||
|
||
fputcsv($handle, ['=== KEHADIRAN PER KEGIATAN ===']);
|
||
fputcsv($handle, ['Kegiatan', 'Kategori', 'Hari', 'Total Absensi', 'Hadir Efektif', 'Terlambat', 'Izin', 'Sakit', 'Alpa', '% Hadir']);
|
||
$perKeg = $this->getTopBottomKegiatan($startDate, $endDate, 'top', 999);
|
||
foreach ($perKeg as $k) {
|
||
fputcsv($handle, [
|
||
$k['nama_kegiatan'], $k['nama_kategori'] ?? '-', $k['hari'] ?? '-',
|
||
$k['total'], $k['hadir'], $k['terlambat'] ?? 0,
|
||
$k['izin'] ?? 0, $k['sakit'] ?? 0, $k['alpa'] ?? 0,
|
||
$k['persen'] . '%'
|
||
]);
|
||
}
|
||
fputcsv($handle, []);
|
||
|
||
fputcsv($handle, ['=== DETAIL PER SANTRI ===']);
|
||
fputcsv($handle, ['ID Santri', 'Nama', 'Total Absensi', 'Hadir Efektif', 'Terlambat', 'Izin', 'Sakit', 'Alpa', '% Hadir']);
|
||
$perSantri = AbsensiKegiatan::whereBetween('tanggal', [$startDate, $endDate])
|
||
->join('santris', 'absensi_kegiatans.id_santri', '=', 'santris.id_santri')
|
||
->where('santris.status', 'Aktif')
|
||
->select(
|
||
'santris.id_santri', 'santris.nama_lengkap',
|
||
DB::raw('COUNT(*) as total'),
|
||
DB::raw('SUM(CASE WHEN absensi_kegiatans.status IN ("Hadir","Terlambat") THEN 1 ELSE 0 END) as hadir'),
|
||
DB::raw('SUM(CASE WHEN absensi_kegiatans.status = "Terlambat" THEN 1 ELSE 0 END) as terlambat'),
|
||
DB::raw('SUM(CASE WHEN absensi_kegiatans.status = "Izin" THEN 1 ELSE 0 END) as izin'),
|
||
DB::raw('SUM(CASE WHEN absensi_kegiatans.status = "Sakit" THEN 1 ELSE 0 END) as sakit'),
|
||
DB::raw('SUM(CASE WHEN absensi_kegiatans.status = "Alpa" THEN 1 ELSE 0 END) as alpa')
|
||
)
|
||
->groupBy('santris.id_santri', 'santris.nama_lengkap')
|
||
->orderBy('santris.nama_lengkap')
|
||
->get();
|
||
|
||
foreach ($perSantri as $s) {
|
||
$persen = $s->total > 0 ? round($s->hadir / $s->total * 100, 1) : 0;
|
||
fputcsv($handle, [
|
||
$s->id_santri, $s->nama_lengkap, $s->total,
|
||
$s->hadir, $s->terlambat, $s->izin, $s->sakit, $s->alpa,
|
||
$persen . '%'
|
||
]);
|
||
}
|
||
fclose($handle);
|
||
}, $filename, ['Content-Type' => 'text/csv']);
|
||
}
|
||
|
||
// ════════════════════════════════════════════════════════
|
||
// PRIVATE HELPERS
|
||
// ════════════════════════════════════════════════════════
|
||
|
||
private function getPeriodeRange($periode, Request $request = null)
|
||
{
|
||
switch ($periode) {
|
||
case 'hari_ini': return [Carbon::today(), Carbon::today()];
|
||
case 'minggu_ini': return [Carbon::now()->startOfWeek(), Carbon::now()->endOfWeek()];
|
||
case 'bulan_ini': return [Carbon::now()->startOfMonth(), Carbon::now()->endOfMonth()];
|
||
case 'semester_ini':
|
||
$m = Carbon::now()->month;
|
||
return $m >= 7
|
||
? [Carbon::create(now()->year, 7, 1), Carbon::create(now()->year, 12, 31)]
|
||
: [Carbon::create(now()->year, 1, 1), Carbon::create(now()->year, 6, 30)];
|
||
case 'custom':
|
||
$dari = $request?->get('tanggal_dari', now()->startOfMonth()->format('Y-m-d'));
|
||
$sampai = $request?->get('tanggal_sampai', now()->format('Y-m-d'));
|
||
return [Carbon::parse($dari), Carbon::parse($sampai)];
|
||
default:
|
||
return [Carbon::now()->startOfWeek(), Carbon::now()->endOfWeek()];
|
||
}
|
||
}
|
||
|
||
private function getPreviousPeriodeRange($periode, $startDate, $endDate)
|
||
{
|
||
$diff = $startDate->diffInDays($endDate) + 1;
|
||
return [
|
||
Carbon::parse($startDate)->subDays($diff),
|
||
Carbon::parse($endDate)->subDays($diff),
|
||
];
|
||
}
|
||
|
||
private function getPeriodeLabel($periode, $startDate, $endDate)
|
||
{
|
||
return match ($periode) {
|
||
'hari_ini' => 'Hari Ini (' . Carbon::today()->locale('id')->isoFormat('D MMMM Y') . ')',
|
||
'minggu_ini' => 'Minggu Ini (' . $startDate->locale('id')->isoFormat('D MMM') . '–' . $endDate->locale('id')->isoFormat('D MMM Y') . ')',
|
||
'bulan_ini' => Carbon::now()->locale('id')->isoFormat('MMMM Y'),
|
||
'semester_ini' => 'Semester ' . (now()->month >= 7 ? 'Ganjil' : 'Genap') . ' ' . now()->year,
|
||
'custom' => $startDate->format('d/m/Y') . ' – ' . $endDate->format('d/m/Y'),
|
||
default => $startDate->format('d/m/Y') . ' – ' . $endDate->format('d/m/Y'),
|
||
};
|
||
}
|
||
|
||
private function calculateKpi($startDate, $endDate)
|
||
{
|
||
$totalSantriAktif = Santri::where('status', 'Aktif')->count();
|
||
|
||
$totalKegiatan = AbsensiKegiatan::whereBetween('tanggal', [$startDate, $endDate])
|
||
->distinct('kegiatan_id')->count('kegiatan_id');
|
||
|
||
$agg = AbsensiKegiatan::whereBetween('tanggal', [$startDate, $endDate])
|
||
->selectRaw('
|
||
COUNT(*) as total,
|
||
SUM(CASE WHEN status IN ("Hadir","Terlambat") THEN 1 ELSE 0 END) as hadir,
|
||
SUM(CASE WHEN status = "Terlambat" THEN 1 ELSE 0 END) as terlambat,
|
||
SUM(CASE WHEN status = "Alpa" THEN 1 ELSE 0 END) as alpa,
|
||
SUM(CASE WHEN status = "Izin" THEN 1 ELSE 0 END) as izin,
|
||
SUM(CASE WHEN status = "Sakit" THEN 1 ELSE 0 END) as sakit
|
||
')->first();
|
||
|
||
$totalAbsensi = $agg->total ?? 0;
|
||
$totalHadir = $agg->hadir ?? 0;
|
||
$avgKehadiran = $totalAbsensi > 0 ? round($totalHadir / $totalAbsensi * 100, 1) : 0;
|
||
|
||
$kegiatanTerbaik = AbsensiKegiatan::whereBetween('tanggal', [$startDate, $endDate])
|
||
->join('kegiatans', 'absensi_kegiatans.kegiatan_id', '=', 'kegiatans.kegiatan_id')
|
||
->select(
|
||
'kegiatans.kegiatan_id', 'kegiatans.nama_kegiatan',
|
||
DB::raw('COUNT(*) as total'),
|
||
DB::raw('SUM(CASE WHEN absensi_kegiatans.status IN ("Hadir","Terlambat") THEN 1 ELSE 0 END) as hadir'),
|
||
DB::raw('ROUND(SUM(CASE WHEN absensi_kegiatans.status IN ("Hadir","Terlambat") THEN 1 ELSE 0 END)/COUNT(*)*100,1) as persen')
|
||
)
|
||
->groupBy('kegiatans.kegiatan_id', 'kegiatans.nama_kegiatan')
|
||
->having('total', '>=', 5)
|
||
->orderByDesc('persen')->first();
|
||
|
||
// COUNT via subquery — tidak load semua baris ke PHP
|
||
$santriPerluPerhatian = DB::table(function ($q) use ($startDate, $endDate) {
|
||
$q->from('absensi_kegiatans')
|
||
->whereBetween('absensi_kegiatans.tanggal', [$startDate, $endDate])
|
||
->join('santris', 'absensi_kegiatans.id_santri', '=', 'santris.id_santri')
|
||
->where('santris.status', 'Aktif')
|
||
->select(
|
||
'santris.id_santri',
|
||
DB::raw('ROUND(SUM(CASE WHEN absensi_kegiatans.status IN ("Hadir","Terlambat") THEN 1 ELSE 0 END)/COUNT(*)*100,1) as persen')
|
||
)
|
||
->groupBy('santris.id_santri')
|
||
->having('persen', '<', 70);
|
||
}, 'sub')->count();
|
||
|
||
return [
|
||
'total_kegiatan' => $totalKegiatan,
|
||
'total_absensi' => $totalAbsensi,
|
||
'total_hadir' => $totalHadir,
|
||
'total_terlambat' => $agg->terlambat ?? 0,
|
||
'total_alpa' => $agg->alpa ?? 0,
|
||
'total_izin' => $agg->izin ?? 0,
|
||
'total_sakit' => $agg->sakit ?? 0,
|
||
'avg_kehadiran' => $avgKehadiran,
|
||
'total_santri_aktif' => $totalSantriAktif,
|
||
'santri_perlu_perhatian' => $santriPerluPerhatian,
|
||
'kegiatan_terbaik' => $kegiatanTerbaik ? [
|
||
'nama' => $kegiatanTerbaik->nama_kegiatan,
|
||
'persen'=> $kegiatanTerbaik->persen,
|
||
'hadir' => $kegiatanTerbaik->hadir,
|
||
'total' => $kegiatanTerbaik->total,
|
||
] : ['nama' => '-', 'persen' => 0, 'hadir' => 0, 'total' => 0],
|
||
];
|
||
}
|
||
|
||
/**
|
||
* 1 query bulk GROUP BY tanggal+kategori, pivot di PHP.
|
||
*/
|
||
private function getTrendData($startDate, $endDate)
|
||
{
|
||
$diffDays = $startDate->diffInDays($endDate);
|
||
$byWeek = $diffDays > 14;
|
||
|
||
$dateExpr = $byWeek
|
||
? "DATE_FORMAT(absensi_kegiatans.tanggal, '%Y-%u')"
|
||
: "DATE(absensi_kegiatans.tanggal)";
|
||
|
||
$rows = DB::table('absensi_kegiatans')
|
||
->join('kegiatans', 'absensi_kegiatans.kegiatan_id', '=', 'kegiatans.kegiatan_id')
|
||
->join('kategori_kegiatans', 'kegiatans.kategori_id', '=', 'kategori_kegiatans.kategori_id')
|
||
->whereBetween('absensi_kegiatans.tanggal', [$startDate, $endDate])
|
||
->selectRaw("
|
||
{$dateExpr} as periode_key,
|
||
MIN(absensi_kegiatans.tanggal) as periode_date,
|
||
kategori_kegiatans.nama_kategori,
|
||
COUNT(*) as total,
|
||
SUM(CASE WHEN absensi_kegiatans.status IN ('Hadir','Terlambat') THEN 1 ELSE 0 END) as hadir
|
||
")
|
||
->groupByRaw("{$dateExpr}, kategori_kegiatans.nama_kategori")
|
||
->orderBy('periode_date')
|
||
->get();
|
||
|
||
$periodeKeys = $rows->pluck('periode_key')->unique()->sort()->values();
|
||
$kategoris = $rows->pluck('nama_kategori')->unique()->values();
|
||
|
||
$lookup = [];
|
||
foreach ($rows as $r) {
|
||
$lookup[$r->periode_key][$r->nama_kategori] =
|
||
$r->total > 0 ? round($r->hadir / $r->total * 100, 1) : null;
|
||
}
|
||
|
||
$labels = [];
|
||
if ($byWeek) {
|
||
foreach ($periodeKeys as $i => $_) { $labels[] = 'Mg ' . ($i + 1); }
|
||
} else {
|
||
foreach ($periodeKeys as $key) { $labels[] = Carbon::parse($key)->format('d/m'); }
|
||
}
|
||
|
||
$datasets = [];
|
||
foreach ($kategoris as $kat) {
|
||
$data = [];
|
||
foreach ($periodeKeys as $key) { $data[] = $lookup[$key][$kat] ?? null; }
|
||
$datasets[] = ['kategori' => $kat, 'data' => $data];
|
||
}
|
||
|
||
return ['labels' => $labels, 'datasets' => $datasets];
|
||
}
|
||
|
||
private function getTopBottomKegiatan($startDate, $endDate, $type = 'top', $limit = 5)
|
||
{
|
||
return AbsensiKegiatan::whereBetween('tanggal', [$startDate, $endDate])
|
||
->join('kegiatans', 'absensi_kegiatans.kegiatan_id', '=', 'kegiatans.kegiatan_id')
|
||
->leftJoin('kategori_kegiatans', 'kegiatans.kategori_id', '=', 'kategori_kegiatans.kategori_id')
|
||
->select(
|
||
'kegiatans.id',
|
||
'kegiatans.kegiatan_id', 'kegiatans.nama_kegiatan', 'kegiatans.hari',
|
||
'kategori_kegiatans.nama_kategori',
|
||
DB::raw('COUNT(*) as total'),
|
||
DB::raw('SUM(CASE WHEN absensi_kegiatans.status IN ("Hadir","Terlambat") THEN 1 ELSE 0 END) as hadir'),
|
||
DB::raw('SUM(CASE WHEN absensi_kegiatans.status = "Terlambat" THEN 1 ELSE 0 END) as terlambat'),
|
||
DB::raw('SUM(CASE WHEN absensi_kegiatans.status = "Alpa" THEN 1 ELSE 0 END) as alpa'),
|
||
DB::raw('SUM(CASE WHEN absensi_kegiatans.status = "Izin" THEN 1 ELSE 0 END) as izin'),
|
||
DB::raw('SUM(CASE WHEN absensi_kegiatans.status = "Sakit" THEN 1 ELSE 0 END) as sakit'),
|
||
DB::raw('ROUND(SUM(CASE WHEN absensi_kegiatans.status IN ("Hadir","Terlambat") THEN 1 ELSE 0 END)/COUNT(*)*100,1) as persen')
|
||
)
|
||
->groupBy('kegiatans.id', 'kegiatans.kegiatan_id', 'kegiatans.nama_kegiatan', 'kegiatans.hari', 'kategori_kegiatans.nama_kategori')
|
||
->having('total', '>=', 3)
|
||
->orderBy('persen', $type === 'top' ? 'desc' : 'asc')
|
||
->limit($limit)
|
||
->get()->toArray();
|
||
}
|
||
|
||
private function getKelasRingkasan($startDate, $endDate)
|
||
{
|
||
$kelompoks = KelompokKelas::active()->ordered()
|
||
->with(['kelas' => fn($q) => $q->active()->ordered()])
|
||
->get();
|
||
|
||
$allKelasIds = $kelompoks->flatMap(fn($k) => $k->kelas->pluck('id'));
|
||
if ($allKelasIds->isEmpty()) return [];
|
||
|
||
// 1 query: semua santri di semua kelas
|
||
$santriPerKelas = DB::table('santri_kelas')
|
||
->whereIn('id_kelas', $allKelasIds)
|
||
->select('id_kelas', 'id_santri')
|
||
->get()
|
||
->groupBy('id_kelas');
|
||
|
||
$allSantriIds = DB::table('santri_kelas')
|
||
->whereIn('id_kelas', $allKelasIds)
|
||
->pluck('id_santri')->unique();
|
||
|
||
if ($allSantriIds->isEmpty()) return [];
|
||
|
||
// 1 query: absensi semua santri sekaligus
|
||
$absensiRows = DB::table('absensi_kegiatans')
|
||
->whereIn('id_santri', $allSantriIds)
|
||
->whereBetween('tanggal', [$startDate, $endDate])
|
||
->select('id_santri',
|
||
DB::raw('COUNT(*) as total'),
|
||
DB::raw('SUM(CASE WHEN status IN ("Hadir","Terlambat") THEN 1 ELSE 0 END) as hadir')
|
||
)
|
||
->groupBy('id_santri')
|
||
->get()
|
||
->keyBy('id_santri');
|
||
|
||
$result = [];
|
||
foreach ($kelompoks as $kelompok) {
|
||
$kelasData = [];
|
||
foreach ($kelompok->kelas as $kelas) {
|
||
$sIds = $santriPerKelas->get($kelas->id, collect())->pluck('id_santri');
|
||
if ($sIds->isEmpty()) continue;
|
||
|
||
$total = 0; $hadir = 0;
|
||
foreach ($sIds as $sid) {
|
||
if (isset($absensiRows[$sid])) {
|
||
$total += $absensiRows[$sid]->total;
|
||
$hadir += $absensiRows[$sid]->hadir;
|
||
}
|
||
}
|
||
if ($total === 0) continue;
|
||
|
||
$kelasData[] = [
|
||
'id' => $kelas->id,
|
||
'nama_kelas' => $kelas->nama_kelas,
|
||
'hadir' => $hadir,
|
||
'total' => $total,
|
||
'persen' => round($hadir / $total * 100, 1),
|
||
];
|
||
}
|
||
if (!empty($kelasData)) {
|
||
$result[] = ['nama_kelompok' => $kelompok->nama_kelompok, 'kelas' => $kelasData];
|
||
}
|
||
}
|
||
return $result;
|
||
}
|
||
|
||
private function getSantriPerluPerhatianList($startDate, $endDate, $limit = 8)
|
||
{
|
||
return AbsensiKegiatan::whereBetween('tanggal', [$startDate, $endDate])
|
||
->join('santris', 'absensi_kegiatans.id_santri', '=', 'santris.id_santri')
|
||
->where('santris.status', 'Aktif')
|
||
->select(
|
||
'santris.id_santri', 'santris.nama_lengkap',
|
||
DB::raw('COUNT(*) as total'),
|
||
DB::raw('SUM(CASE WHEN absensi_kegiatans.status IN ("Hadir","Terlambat") THEN 1 ELSE 0 END) as hadir'),
|
||
DB::raw('SUM(CASE WHEN absensi_kegiatans.status = "Alpa" THEN 1 ELSE 0 END) as alpa'),
|
||
DB::raw('ROUND(SUM(CASE WHEN absensi_kegiatans.status IN ("Hadir","Terlambat") THEN 1 ELSE 0 END)/COUNT(*)*100,1) as persen')
|
||
)
|
||
->groupBy('santris.id_santri', 'santris.nama_lengkap')
|
||
->having('persen', '<', 70)
|
||
->orderBy('persen', 'asc')
|
||
->limit($limit)->get();
|
||
}
|
||
|
||
private function getDistribusiSantri($startDate, $endDate): array
|
||
{
|
||
// Bucketing langsung di SQL
|
||
$agg = DB::table(function ($q) use ($startDate, $endDate) {
|
||
$q->from('absensi_kegiatans')
|
||
->whereBetween('absensi_kegiatans.tanggal', [$startDate, $endDate])
|
||
->join('santris', 'absensi_kegiatans.id_santri', '=', 'santris.id_santri')
|
||
->where('santris.status', 'Aktif')
|
||
->select(
|
||
'santris.id_santri',
|
||
DB::raw('ROUND(SUM(CASE WHEN absensi_kegiatans.status IN ("Hadir","Terlambat") THEN 1 ELSE 0 END)/COUNT(*)*100,1) as persen')
|
||
)
|
||
->groupBy('santris.id_santri');
|
||
}, 'sub')
|
||
->selectRaw('
|
||
COUNT(*) as total,
|
||
SUM(CASE WHEN persen >= 95 THEN 1 ELSE 0 END) as sangat_baik,
|
||
SUM(CASE WHEN persen >= 85 AND persen < 95 THEN 1 ELSE 0 END) as baik,
|
||
SUM(CASE WHEN persen >= 70 AND persen < 85 THEN 1 ELSE 0 END) as cukup,
|
||
SUM(CASE WHEN persen < 70 THEN 1 ELSE 0 END) as perlu_perhatian
|
||
')
|
||
->first();
|
||
|
||
$total = $agg->total ?? 0;
|
||
$defs = [
|
||
['label' => 'Sangat Baik', 'key' => 'sangat_baik', 'color' => '#10B981'],
|
||
['label' => 'Baik', 'key' => 'baik', 'color' => '#34D399'],
|
||
['label' => 'Cukup', 'key' => 'cukup', 'color' => '#F59E0B'],
|
||
['label' => 'Perlu Perhatian','key' => 'perlu_perhatian','color' => '#EF4444'],
|
||
];
|
||
|
||
$buckets = [];
|
||
foreach ($defs as $d) {
|
||
$count = (int)($agg->{$d['key']} ?? 0);
|
||
$buckets[] = [
|
||
'label' => $d['label'],
|
||
'color' => $d['color'],
|
||
'count' => $count,
|
||
'persen' => $total > 0 ? round($count / $total * 100, 1) : 0,
|
||
];
|
||
}
|
||
|
||
return ['buckets' => $buckets, 'total' => $total];
|
||
}
|
||
|
||
private function buildPatterns(): array
|
||
{
|
||
$patterns = [];
|
||
|
||
// 1. Kegiatan konsisten rendah — sertakan kegiatans.id agar tidak query ulang
|
||
$lowAttendance = AbsensiKegiatan::where('tanggal', '>=', Carbon::now()->subWeeks(3))
|
||
->join('kegiatans', 'absensi_kegiatans.kegiatan_id', '=', 'kegiatans.kegiatan_id')
|
||
->select(
|
||
'kegiatans.id',
|
||
'kegiatans.kegiatan_id', 'kegiatans.nama_kegiatan',
|
||
DB::raw('COUNT(*) as total'),
|
||
DB::raw('SUM(CASE WHEN absensi_kegiatans.status IN ("Hadir","Terlambat") THEN 1 ELSE 0 END) as hadir'),
|
||
DB::raw('ROUND(SUM(CASE WHEN absensi_kegiatans.status IN ("Hadir","Terlambat") THEN 1 ELSE 0 END)/COUNT(*)*100,1) as persen')
|
||
)
|
||
->groupBy('kegiatans.id', 'kegiatans.kegiatan_id', 'kegiatans.nama_kegiatan')
|
||
->having('persen', '<', 75)
|
||
->having('total', '>=', 10)
|
||
->get();
|
||
|
||
foreach ($lowAttendance as $la) {
|
||
$patterns[] = [
|
||
'type' => 'warning',
|
||
'title' => $la->nama_kegiatan,
|
||
'description' => "Kehadiran {$la->persen}% ({$la->hadir} hadir dari {$la->total} tercatat) — konsisten di bawah 75% selama 3 minggu.",
|
||
'action_url' => route('admin.riwayat-kegiatan.show', $la->id),
|
||
'action_text' => 'Lihat Santri',
|
||
];
|
||
}
|
||
|
||
// 2. Hari tertentu rendah — 1 query
|
||
$dayStats = AbsensiKegiatan::where('tanggal', '>=', Carbon::now()->subMonth())
|
||
->join('kegiatans', 'absensi_kegiatans.kegiatan_id', '=', 'kegiatans.kegiatan_id')
|
||
->select(
|
||
'kegiatans.hari',
|
||
DB::raw('COUNT(*) as total'),
|
||
DB::raw('SUM(CASE WHEN absensi_kegiatans.status IN ("Hadir","Terlambat") THEN 1 ELSE 0 END) as hadir'),
|
||
DB::raw('ROUND(SUM(CASE WHEN absensi_kegiatans.status IN ("Hadir","Terlambat") THEN 1 ELSE 0 END)/COUNT(*)*100,1) as persen')
|
||
)
|
||
->groupBy('kegiatans.hari')
|
||
->having('persen', '<', 70)
|
||
->having('total', '>=', 5)
|
||
->get();
|
||
|
||
foreach ($dayStats as $ds) {
|
||
$patterns[] = [
|
||
'type' => 'info',
|
||
'title' => "Hari {$ds->hari}",
|
||
'description' => "Rata-rata kehadiran {$ds->persen}% ({$ds->hadir} hadir dari {$ds->total} tercatat) dalam sebulan terakhir.",
|
||
'action_url' => null,
|
||
'action_text' => null,
|
||
];
|
||
}
|
||
|
||
// 3. Kelas turun >10% — JOIN langsung, tanpa whereIn besar
|
||
$tw = [Carbon::now()->startOfWeek()->format('Y-m-d'), Carbon::now()->endOfWeek()->format('Y-m-d')];
|
||
$lw = [Carbon::now()->subWeek()->startOfWeek()->format('Y-m-d'), Carbon::now()->subWeek()->endOfWeek()->format('Y-m-d')];
|
||
|
||
$buildKelasStats = function ($range) {
|
||
return DB::table('absensi_kegiatans')
|
||
->join('santri_kelas', 'absensi_kegiatans.id_santri', '=', 'santri_kelas.id_santri')
|
||
->join('kelas', 'santri_kelas.id_kelas', '=', 'kelas.id')
|
||
->where('kelas.is_active', true)
|
||
->whereBetween('absensi_kegiatans.tanggal', $range)
|
||
->select(
|
||
'kelas.id',
|
||
'kelas.nama_kelas',
|
||
DB::raw('COUNT(DISTINCT absensi_kegiatans.id) as total'),
|
||
DB::raw('SUM(CASE WHEN absensi_kegiatans.status IN ("Hadir","Terlambat") THEN 1 ELSE 0 END) as hadir')
|
||
)
|
||
->groupBy('kelas.id', 'kelas.nama_kelas')
|
||
->get()
|
||
->keyBy('id');
|
||
};
|
||
|
||
$twStats = $buildKelasStats($tw);
|
||
$lwStats = $buildKelasStats($lw);
|
||
|
||
foreach ($twStats as $kelasId => $tw_) {
|
||
if (!isset($lwStats[$kelasId])) continue;
|
||
$lw_ = $lwStats[$kelasId];
|
||
|
||
$pTw = $tw_->total > 0 ? round($tw_->hadir / $tw_->total * 100, 1) : null;
|
||
$pLw = $lw_->total > 0 ? round($lw_->hadir / $lw_->total * 100, 1) : null;
|
||
|
||
if ($pTw !== null && $pLw !== null && ($pLw - $pTw) > 10) {
|
||
$drop = round($pLw - $pTw, 1);
|
||
$patterns[] = [
|
||
'type' => 'danger',
|
||
'title' => "Kelas {$tw_->nama_kelas} turun {$drop}%",
|
||
'description' => "Dari {$pLw}% (minggu lalu) → {$pTw}% (minggu ini). Penurunan signifikan.",
|
||
'action_url' => null,
|
||
'action_text' => null,
|
||
];
|
||
}
|
||
}
|
||
|
||
// 4. Santri ≥3 Alpa dalam 2 minggu — 1 query
|
||
$streaks = DB::select("
|
||
SELECT s.id_santri, s.nama_lengkap, COUNT(*) as jml_alpa
|
||
FROM absensi_kegiatans a
|
||
JOIN santris s ON a.id_santri = s.id_santri
|
||
WHERE a.status = 'Alpa'
|
||
AND a.tanggal >= ?
|
||
AND s.status = 'Aktif'
|
||
GROUP BY s.id_santri, s.nama_lengkap
|
||
HAVING jml_alpa >= 3
|
||
ORDER BY jml_alpa DESC
|
||
LIMIT 10
|
||
", [Carbon::now()->subWeeks(2)->format('Y-m-d')]);
|
||
|
||
foreach ($streaks as $s) {
|
||
$patterns[] = [
|
||
'type' => 'danger',
|
||
'title' => $s->nama_lengkap,
|
||
'description' => "{$s->jml_alpa}× Alpa dalam 2 minggu terakhir. Perlu tindak lanjut segera.",
|
||
'action_url' => route('admin.riwayat-kegiatan.detail-santri', $s->id_santri),
|
||
'action_text' => 'Lihat Riwayat',
|
||
];
|
||
}
|
||
|
||
return $patterns;
|
||
}
|
||
} |