penjadwalan-ga/app/Exports/MasterDataExport.php

<?php

namespace App\Exports;

use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Concerns\WithMultipleSheets;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithTitle;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Maatwebsite\Excel\Concerns\WithStyles;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class MasterDataExport implements WithMultipleSheets
{
    public function sheets(): array
    {
        return [
            new MasterGuruSheet(),
            new MasterKelasSheet(),
            new MasterRuanganSheet(),
            new MasterLevelSheet(),
            new MasterSesiSheet(),
            new MasterHariSheet(),
            new MasterSekolahSheet(),
            new MasterKetersediaanGuruSheet(),
        ];
    }
}

class MasterGuruSheet implements FromCollection, WithHeadings, WithTitle, ShouldAutoSize, WithStyles
{
    public function title(): string
    {
        return 'Master Guru';
    }

    public function headings(): array
    {
        return ['ID Guru', 'Nama Guru', 'Status', 'Level Maksimal Mengajar', 'Divisi Guru'];
    }

    public function collection()
    {
        return DB::table('guru')
            ->select(
                'guru.id',
                'guru.nama',
                'guru.status',
                'guru.level_maksimal',
                'guru.divisi'
            )
            ->orderBy('guru.id')
            ->get();
    }

    public function styles(Worksheet $sheet)
    {
        return [
            1 => ['font' => ['bold' => true]],
        ];
    }
}

class MasterKelasSheet implements FromCollection, WithHeadings, WithTitle, ShouldAutoSize, WithStyles
{
    public function title(): string
    {
        return 'Master Kelas';
    }

    public function headings(): array
    {
        return ['ID Kelas', 'Nama Kelas', 'Level', 'Level Number', 'Tipe Kelas', 'Frekuensi/Minggu', 'Sekolah', 'Hari Fix', 'Sesi Fix', 'Ruangan Fix'];
    }

    public function collection()
    {
        $hariMap = [1 => 'Senin', 2 => 'Selasa', 3 => 'Rabu', 4 => 'Kamis', 5 => 'Jumat', 6 => 'Sabtu', 7 => 'Minggu'];

        $rows = DB::table('kelas')
            ->leftJoin('programs', 'kelas.program_id', '=', 'programs.id')
            ->leftJoin('sekolah', 'kelas.sekolah_id', '=', 'sekolah.id')
            ->leftJoin('ruangan', 'kelas.ruangan_fix_id', '=', 'ruangan.id')
            ->leftJoin('sesi', 'kelas.sesi_fix', '=', 'sesi.id')
            ->select(
                'kelas.id',
                'kelas.nama_kelas',
                'programs.level',
                'programs.kelas as program_kelas',
                'programs.level_number',
                'kelas.is_ekstrakurikuler',
                'kelas.frekuensi_per_minggu',
                'sekolah.nama_sekolah',
                'kelas.hari_fix',
                'sesi.nama_sesi',
                'ruangan.nama_ruangan'
            )
            ->where('kelas.status', 'Aktif')
            ->orderBy('kelas.id')
            ->get();

        return $rows->map(function ($r) use ($hariMap) {
            $namaLevel = $r->level ? ('Level ' . $r->level . ($r->program_kelas ? ' (' . $r->program_kelas . ')' : '')) : '-';
            return [
                'id' => $r->id,
                'nama_kelas' => $r->nama_kelas,
                'nama_level' => $namaLevel,
                'level_number' => $r->level_number ?? '-',
                'tipe_kelas' => $r->is_ekstrakurikuler ? 'Ekstrakurikuler' : 'Reguler',
                'frekuensi_per_minggu' => $r->frekuensi_per_minggu . 'x',
                'nama_sekolah' => $r->nama_sekolah ?? 'LKP Robotku',
                'hari_fix' => $r->hari_fix ? ($hariMap[$r->hari_fix] ?? $r->hari_fix) : '-',
                'sesi_fix' => $r->nama_sesi ?? '-',
                'ruangan_fix' => $r->nama_ruangan ?? '-',
            ];
        });
    }

    public function styles(Worksheet $sheet)
    {
        return [
            1 => ['font' => ['bold' => true]],
        ];
    }
}

class MasterRuanganSheet implements FromCollection, WithHeadings, WithTitle, ShouldAutoSize, WithStyles
{
    public function title(): string
    {
        return 'Master Ruangan';
    }

    public function headings(): array
    {
        return ['ID Ruangan', 'Nama Ruangan', 'Tipe Lokasi', 'Status'];
    }

    public function collection()
    {
        return DB::table('ruangan')
            ->select('id', 'nama_ruangan', 'lokasi_tipe', 'status')
            ->orderBy('id')
            ->get();
    }

    public function styles(Worksheet $sheet)
    {
        return [
            1 => ['font' => ['bold' => true]],
        ];
    }
}

class MasterLevelSheet implements FromCollection, WithHeadings, WithTitle, ShouldAutoSize, WithStyles
{
    public function title(): string
    {
        return 'Master Level';
    }

    public function headings(): array
    {
        return ['ID Level', 'Level', 'Kategori Rombel', 'Level Number', 'Goal', 'Penjelasan'];
    }

    public function collection()
    {
        return DB::table('programs')
            ->select('id', 'level', 'kelas', 'level_number', 'goal', 'penjelasan')
            ->orderBy('id')
            ->get();
    }

    public function styles(Worksheet $sheet)
    {
        return [
            1 => ['font' => ['bold' => true]],
        ];
    }
}

class MasterSesiSheet implements FromCollection, WithHeadings, WithTitle, ShouldAutoSize, WithStyles
{
    public function title(): string
    {
        return 'Master Sesi';
    }

    public function headings(): array
    {
        return ['ID Sesi', 'Nama Sesi', 'Jam Operasional'];
    }

    public function collection()
    {
        return DB::table('sesi')
            ->select('id', 'nama_sesi', 'jam')
            ->orderBy('id')
            ->get();
    }

    public function styles(Worksheet $sheet)
    {
        return [
            1 => ['font' => ['bold' => true]],
        ];
    }
}

class MasterHariSheet implements FromCollection, WithHeadings, WithTitle, ShouldAutoSize, WithStyles
{
    public function title(): string
    {
        return 'Master Hari';
    }

    public function headings(): array
    {
        return ['ID Hari', 'Nama Hari'];
    }

    public function collection()
    {
        return DB::table('hari')
            ->select('id', 'nama_hari')
            ->orderBy('id')
            ->get();
    }

    public function styles(Worksheet $sheet)
    {
        return [
            1 => ['font' => ['bold' => true]],
        ];
    }
}

class MasterSekolahSheet implements FromCollection, WithHeadings, WithTitle, ShouldAutoSize, WithStyles
{
    public function title(): string
    {
        return 'Master Sekolah';
    }

    public function headings(): array
    {
        return ['ID Sekolah', 'Nama Sekolah', 'Alamat'];
    }

    public function collection()
    {
        return DB::table('sekolah')
            ->select('id', 'nama_sekolah', 'alamat_sekolah')
            ->orderBy('id')
            ->get();
    }

    public function styles(Worksheet $sheet)
    {
        return [
            1 => ['font' => ['bold' => true]],
        ];
    }
}

class MasterKetersediaanGuruSheet implements FromCollection, WithHeadings, WithTitle, ShouldAutoSize, WithStyles
{
    public function title(): string
    {
        return 'Ketersediaan Mengajar Guru';
    }

    public function headings(): array
    {
        return ['Guru ID', 'Nama Guru', 'Hari', 'Sesi ID', 'Nama Sesi', 'Jam'];
    }

    public function collection()
    {
        $hariMap = [1 => 'Senin', 2 => 'Selasa', 3 => 'Rabu', 4 => 'Kamis', 5 => 'Jumat', 6 => 'Sabtu', 7 => 'Minggu'];

        $rows = DB::table('ketersediaan_mengajars')
            ->join('guru', 'ketersediaan_mengajars.guru_id', '=', 'guru.id')
            ->join('sesi', 'ketersediaan_mengajars.sesi_id', '=', 'sesi.id')
            ->select(
                'guru.id as guru_id',
                'guru.nama as nama_guru',
                'ketersediaan_mengajars.hari',
                'sesi.id as sesi_id',
                'sesi.nama_sesi',
                'sesi.jam'
            )
            ->orderBy('guru.id')
            ->orderBy('ketersediaan_mengajars.hari')
            ->orderBy('sesi.id')
            ->get();

        return $rows->map(function ($r) use ($hariMap) {
            return [
                'guru_id' => $r->guru_id,
                'nama_guru' => $r->nama_guru,
                'hari' => $hariMap[$r->hari] ?? $r->hari,
                'sesi_id' => $r->sesi_id,
                'nama_sesi' => $r->nama_sesi,
                'jam' => $r->jam,
            ];
        });
    }

    public function styles(Worksheet $sheet)
    {
        return [
            1 => ['font' => ['bold' => true]],
        ];
    }
}