770 lines
28 KiB
PHP
770 lines
28 KiB
PHP
<?php
|
|
|
|
namespace App\Http\Controllers;
|
|
|
|
use App\Models\AcademicYear;
|
|
use App\Models\Department;
|
|
use App\Models\SchoolClass;
|
|
use Illuminate\Http\RedirectResponse;
|
|
use Illuminate\Http\Request;
|
|
use Illuminate\Support\Arr;
|
|
use Illuminate\Support\Facades\DB;
|
|
use Illuminate\Support\Facades\Validator;
|
|
use Illuminate\Support\Str;
|
|
use Illuminate\Validation\Rule;
|
|
use Illuminate\Validation\ValidationException;
|
|
use Illuminate\View\View;
|
|
use PhpOffice\PhpSpreadsheet\Spreadsheet;
|
|
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
|
|
use RealRashid\SweetAlert\Facades\Alert;
|
|
|
|
class SchoolClassController extends Controller
|
|
{
|
|
public function index(Request $request): View
|
|
{
|
|
$search = trim((string) $request->get('search'));
|
|
$academicYearId = $request->integer('academic_year_id') ?: null;
|
|
|
|
$classes = SchoolClass::with(['academicYear', 'department'])
|
|
->withCount(['students', 'academicHistories'])
|
|
->when($academicYearId, fn ($query) => $query->where('academic_year_id', $academicYearId), fn ($query) => $query->forActiveAcademicYear())
|
|
->when($search !== '', function ($query) use ($search) {
|
|
$query->where('name', 'like', "%{$search}%")
|
|
->orWhere('level', 'like', "%{$search}%")
|
|
->orWhere('homeroom_teacher', 'like', "%{$search}%");
|
|
})
|
|
->orderByRaw("FIELD(level, 'X', 'XI', 'XII'), name")
|
|
->paginate(10)
|
|
->withQueryString();
|
|
|
|
return view('classes.index', [
|
|
'classes' => $classes,
|
|
'search' => $search,
|
|
'academicYearId' => $academicYearId,
|
|
'academicYears' => AcademicYear::orderByDesc('start_year')->orderByDesc('end_year')->get(),
|
|
'importPreview' => $request->session()->get('class_import_preview'),
|
|
'importReferenceData' => $this->importReferenceData(),
|
|
]);
|
|
}
|
|
|
|
public function create(): View
|
|
{
|
|
return view('classes.create', [
|
|
'academicYears' => AcademicYear::where('is_active', true)->orderBy('name')->pluck('name', 'id'),
|
|
'departments' => Department::where('is_active', true)->orderBy('name')->pluck('name', 'id'),
|
|
]);
|
|
}
|
|
|
|
public function store(Request $request): RedirectResponse
|
|
{
|
|
$data = $request->validate([
|
|
'academic_year_id' => ['required', Rule::exists('academic_years', 'id')->where(fn ($query) => $query->where('is_active', true))],
|
|
'department_id' => ['required', Rule::exists('departments', 'id')->where(fn ($query) => $query->where('is_active', true))],
|
|
'name' => ['required', 'string', 'max:100', Rule::unique('classes', 'name')->where(fn ($query) => $query->where('academic_year_id', $request->academic_year_id))],
|
|
'level' => ['required', 'string', 'max:10'],
|
|
'homeroom_teacher' => ['nullable', 'string', 'max:100'],
|
|
'capacity' => ['nullable', 'integer', 'min:1'],
|
|
'is_active' => ['nullable', 'boolean'],
|
|
]);
|
|
|
|
$data['is_active'] = $request->boolean('is_active');
|
|
|
|
SchoolClass::create($data);
|
|
|
|
return redirect()->route('classes.index')->with('success', 'Kelas berhasil ditambahkan.');
|
|
}
|
|
|
|
public function generate(Request $request): RedirectResponse
|
|
{
|
|
$data = $request->validate([
|
|
'source_academic_year_id' => ['required', 'exists:academic_years,id', 'different:target_academic_year_id'],
|
|
'target_academic_year_id' => ['required', 'exists:academic_years,id'],
|
|
'generation_mode' => ['required', Rule::in(['same_level', 'promote_level'])],
|
|
]);
|
|
|
|
$sourceClasses = SchoolClass::query()
|
|
->where('academic_year_id', $data['source_academic_year_id'])
|
|
->orderBy('department_id')
|
|
->orderBy('level')
|
|
->orderBy('name')
|
|
->get();
|
|
|
|
if ($sourceClasses->isEmpty()) {
|
|
Alert::error('Gagal', 'Tidak ada kelas pada tahun ajaran sumber yang bisa digenerate.');
|
|
|
|
return redirect()->route('classes.index')->withErrors([
|
|
'generate' => 'Tidak ada kelas pada tahun ajaran sumber yang bisa digenerate.',
|
|
]);
|
|
}
|
|
|
|
$created = 0;
|
|
$skipped = 0;
|
|
|
|
DB::transaction(function () use ($sourceClasses, $data, &$created, &$skipped) {
|
|
foreach ($sourceClasses as $sourceClass) {
|
|
$targetLevel = $data['generation_mode'] === 'promote_level'
|
|
? $this->nextLevel($sourceClass->level)
|
|
: $sourceClass->level;
|
|
|
|
if ($targetLevel === null) {
|
|
$skipped++;
|
|
continue;
|
|
}
|
|
|
|
$targetName = $this->transformClassName($sourceClass->name, $sourceClass->level, $targetLevel);
|
|
|
|
$exists = SchoolClass::query()
|
|
->where('academic_year_id', $data['target_academic_year_id'])
|
|
->where('name', $targetName)
|
|
->exists();
|
|
|
|
if ($exists) {
|
|
$skipped++;
|
|
continue;
|
|
}
|
|
|
|
SchoolClass::create([
|
|
'academic_year_id' => $data['target_academic_year_id'],
|
|
'department_id' => $sourceClass->department_id,
|
|
'name' => $targetName,
|
|
'level' => $targetLevel,
|
|
'homeroom_teacher' => $sourceClass->homeroom_teacher,
|
|
'capacity' => $sourceClass->capacity,
|
|
'is_active' => true,
|
|
]);
|
|
|
|
$created++;
|
|
}
|
|
});
|
|
|
|
Alert::success('Berhasil', "Generate kelas selesai. {$created} kelas dibuat, {$skipped} dilewati.");
|
|
|
|
return redirect()->route('classes.index')->with('success', "Generate kelas selesai. {$created} kelas dibuat, {$skipped} dilewati.");
|
|
}
|
|
|
|
public function previewImport(Request $request): RedirectResponse
|
|
{
|
|
$validated = $request->validate([
|
|
'import_file' => ['required', 'file', 'mimes:xlsx,csv,txt'],
|
|
], [
|
|
'import_file.mimes' => 'File import harus berformat Excel (.xlsx) atau CSV.',
|
|
]);
|
|
|
|
$rows = $this->parseImportFile($validated['import_file']->getRealPath(), $validated['import_file']->getClientOriginalName());
|
|
|
|
if (count($rows) === 0) {
|
|
throw ValidationException::withMessages([
|
|
'import_file' => 'File tidak berisi data kelas yang bisa diproses.',
|
|
]);
|
|
}
|
|
|
|
$preview = $this->buildImportPreview($rows);
|
|
|
|
if ($preview['summary']['valid_rows'] === 0) {
|
|
throw ValidationException::withMessages([
|
|
'import_file' => array_merge(['Tidak ada baris valid yang bisa dipreview.'], array_slice($preview['errors'], 0, 10)),
|
|
]);
|
|
}
|
|
|
|
$request->session()->put('class_import_preview', $preview);
|
|
|
|
return redirect()->route('classes.index');
|
|
}
|
|
|
|
public function import(Request $request): RedirectResponse
|
|
{
|
|
$request->validate([
|
|
'preview_token' => ['required', 'string'],
|
|
]);
|
|
|
|
$preview = $request->session()->get('class_import_preview');
|
|
|
|
if (! $preview || ($preview['token'] ?? null) !== $request->string('preview_token')->value()) {
|
|
throw ValidationException::withMessages([
|
|
'preview_token' => 'Preview import sudah tidak valid. Silakan upload ulang file import.',
|
|
]);
|
|
}
|
|
|
|
$inserted = 0;
|
|
$updated = 0;
|
|
|
|
DB::transaction(function () use ($preview, &$inserted, &$updated) {
|
|
foreach ($preview['valid_rows'] as $row) {
|
|
$payload = Arr::only($row['normalized'], [
|
|
'academic_year_id',
|
|
'department_id',
|
|
'name',
|
|
'level',
|
|
'homeroom_teacher',
|
|
'capacity',
|
|
'is_active',
|
|
]);
|
|
|
|
$class = SchoolClass::query()
|
|
->where('academic_year_id', $payload['academic_year_id'])
|
|
->where('name', $payload['name'])
|
|
->first();
|
|
|
|
if ($class) {
|
|
$class->update($payload);
|
|
$updated++;
|
|
continue;
|
|
}
|
|
|
|
SchoolClass::create($payload);
|
|
$inserted++;
|
|
}
|
|
});
|
|
|
|
$request->session()->forget('class_import_preview');
|
|
|
|
Alert::success('Berhasil', "Import kelas selesai. {$inserted} data baru ditambahkan, {$updated} data diperbarui.");
|
|
|
|
return redirect()->route('classes.index')->with('success', "Import kelas selesai. {$inserted} data baru ditambahkan, {$updated} data diperbarui.");
|
|
}
|
|
|
|
public function downloadImportTemplate()
|
|
{
|
|
$spreadsheet = new Spreadsheet();
|
|
$sheet = $spreadsheet->getActiveSheet();
|
|
$sheet->setTitle('Import Kelas');
|
|
|
|
$headers = ['Tahun Ajaran', 'Program Keahlian', 'Nama Kelas', 'Tingkat', 'Wali Kelas', 'Kapasitas', 'Status'];
|
|
$sheet->fromArray($headers, null, 'A1');
|
|
$sheet->fromArray([
|
|
AcademicYear::where('is_active', true)->orderBy('name')->value('name') ?? '2025/2026',
|
|
Department::where('is_active', true)->orderBy('name')->value('name') ?? 'TKJ',
|
|
'X TKJ 1',
|
|
'X',
|
|
'Budi Santoso',
|
|
'36',
|
|
'Aktif',
|
|
], null, 'A2');
|
|
|
|
for ($columnIndex = 1; $columnIndex <= count($headers); $columnIndex++) {
|
|
$sheet->getColumnDimensionByColumn($columnIndex)->setAutoSize(true);
|
|
}
|
|
|
|
$referenceSheet = $spreadsheet->createSheet();
|
|
$referenceSheet->setTitle('Referensi');
|
|
$referenceSheet->fromArray(['Tahun Ajaran'], null, 'A1');
|
|
$referenceSheet->fromArray(AcademicYear::orderBy('name')->pluck('name')->all(), null, 'A2');
|
|
$referenceSheet->fromArray(['Program Keahlian'], null, 'C1');
|
|
$referenceSheet->fromArray(Department::orderBy('name')->pluck('name')->all(), null, 'C2');
|
|
$referenceSheet->fromArray(['Status'], null, 'E1');
|
|
$referenceSheet->fromArray([['Aktif'], ['Nonaktif']], null, 'E2');
|
|
|
|
$guideSheet = $spreadsheet->createSheet();
|
|
$guideSheet->setTitle('Panduan');
|
|
$guideSheet->fromArray([
|
|
['Kolom', 'Keterangan'],
|
|
['Tahun Ajaran', 'Wajib. Isi nama tahun ajaran persis seperti sheet Referensi.'],
|
|
['Program Keahlian', 'Wajib. Bisa isi nama jurusan atau kode jurusan.'],
|
|
['Nama Kelas', 'Wajib. Unik per tahun ajaran.'],
|
|
['Tingkat', 'Wajib. Contoh: X, XI, XII.'],
|
|
['Wali Kelas', 'Opsional.'],
|
|
['Kapasitas', 'Opsional. Angka bulat minimal 1.'],
|
|
['Status', 'Isi Aktif/Nonaktif, Yes/No, 1/0, atau Ya/Tidak. Default aktif jika kosong.'],
|
|
], null, 'A1');
|
|
|
|
foreach (range('A', 'B') as $column) {
|
|
$guideSheet->getColumnDimension($column)->setAutoSize(true);
|
|
}
|
|
|
|
$writer = new Xlsx($spreadsheet);
|
|
ob_start();
|
|
$writer->save('php://output');
|
|
$content = ob_get_clean();
|
|
|
|
return response($content, 200, [
|
|
'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
|
|
'Content-Disposition' => 'attachment; filename="template-import-kelas.xlsx"',
|
|
]);
|
|
}
|
|
|
|
public function show(SchoolClass $class): RedirectResponse
|
|
{
|
|
return redirect()->route('classes.edit', $class);
|
|
}
|
|
|
|
public function edit(SchoolClass $class): View
|
|
{
|
|
$class->loadMissing(['academicYear', 'department']);
|
|
|
|
$academicYears = AcademicYear::query()
|
|
->where(function ($query) use ($class) {
|
|
$query->where('is_active', true)
|
|
->orWhere('id', $class->academic_year_id);
|
|
})
|
|
->orderBy('name')
|
|
->pluck('name', 'id');
|
|
|
|
$departments = Department::query()
|
|
->where(function ($query) use ($class) {
|
|
$query->where('is_active', true)
|
|
->orWhere('id', $class->department_id);
|
|
})
|
|
->orderBy('name')
|
|
->pluck('name', 'id');
|
|
|
|
return view('classes.edit', [
|
|
'class' => $class,
|
|
'academicYears' => $academicYears,
|
|
'departments' => $departments
|
|
]);
|
|
}
|
|
|
|
public function update(Request $request, SchoolClass $class): RedirectResponse
|
|
{
|
|
$data = $request->validate([
|
|
'academic_year_id' => ['required', Rule::exists('academic_years', 'id')->where(fn ($query) => $query->where('is_active', true)->orWhere('id', $class->academic_year_id))],
|
|
'department_id' => ['required', Rule::exists('departments', 'id')->where(fn ($query) => $query->where('is_active', true)->orWhere('id', $class->department_id))],
|
|
'name' => ['required', 'string', 'max:100', Rule::unique('classes', 'name')->ignore($class->id)->where(fn ($query) => $query->where('academic_year_id', $request->academic_year_id))],
|
|
'level' => ['required', 'string', 'max:10'],
|
|
'homeroom_teacher' => ['nullable', 'string', 'max:100'],
|
|
'capacity' => ['nullable', 'integer', 'min:1'],
|
|
'is_active' => ['nullable', 'boolean'],
|
|
]);
|
|
|
|
$data['is_active'] = $request->boolean('is_active');
|
|
|
|
$class->update($data);
|
|
|
|
return redirect()->route('classes.index')->with('success', 'Kelas berhasil diperbarui.');
|
|
}
|
|
|
|
public function destroy(SchoolClass $class): RedirectResponse
|
|
{
|
|
$class->loadCount(['students', 'academicHistories']);
|
|
|
|
if ($class->students_count > 0) {
|
|
Alert::error('Tidak bisa dihapus', 'Kelas ini masih digunakan oleh data siswa aktif.');
|
|
|
|
return redirect()->route('classes.index')->withErrors([
|
|
'delete' => 'Kelas tidak bisa dihapus karena masih digunakan oleh data siswa aktif.',
|
|
]);
|
|
}
|
|
|
|
if ($class->academic_histories_count > 0) {
|
|
Alert::error('Tidak bisa dihapus', 'Kelas ini masih tercatat pada histori akademik siswa.');
|
|
|
|
return redirect()->route('classes.index')->withErrors([
|
|
'delete' => 'Kelas tidak bisa dihapus karena masih tercatat pada histori akademik siswa. Hapus atau ubah histori terkait terlebih dahulu.',
|
|
]);
|
|
}
|
|
|
|
Alert::success('Berhasil', 'Kelas berhasil dihapus.');
|
|
|
|
$class->delete();
|
|
|
|
return redirect()->route('classes.index')->with('success', 'Kelas berhasil dihapus.');
|
|
}
|
|
|
|
public function bulkDestroy(Request $request): RedirectResponse
|
|
{
|
|
$validated = $request->validate([
|
|
'class_ids' => ['required', 'array', 'min:1'],
|
|
'class_ids.*' => ['integer', 'exists:classes,id'],
|
|
], [
|
|
'class_ids.required' => 'Pilih minimal satu kelas untuk dihapus.',
|
|
]);
|
|
|
|
$classes = SchoolClass::withCount(['students', 'academicHistories'])
|
|
->whereIn('id', $validated['class_ids'])
|
|
->get();
|
|
|
|
$blocked = $classes->filter(fn (SchoolClass $class) => $class->students_count > 0 || $class->academic_histories_count > 0);
|
|
|
|
if ($blocked->isNotEmpty()) {
|
|
Alert::error('Tidak bisa dihapus', 'Sebagian kelas masih dipakai oleh siswa aktif atau histori akademik.');
|
|
|
|
return redirect()->route('classes.index')->withErrors([
|
|
'delete' => 'Beberapa kelas tidak bisa dihapus karena masih dipakai oleh data siswa atau histori akademik.',
|
|
]);
|
|
}
|
|
|
|
$deletedCount = 0;
|
|
|
|
foreach ($classes as $class) {
|
|
$class->delete();
|
|
$deletedCount++;
|
|
}
|
|
|
|
Alert::success('Berhasil', $deletedCount . ' kelas berhasil dihapus.');
|
|
|
|
return redirect()->route('classes.index')->with('success', $deletedCount . ' kelas berhasil dihapus.');
|
|
}
|
|
|
|
protected function importReferenceData(): array
|
|
{
|
|
return [
|
|
'academicYears' => AcademicYear::orderBy('name')->pluck('name', 'id'),
|
|
'departments' => Department::orderBy('name')->pluck('name', 'id'),
|
|
];
|
|
}
|
|
|
|
protected function buildImportPreview(array $rows): array
|
|
{
|
|
$previewRows = [];
|
|
$errors = [];
|
|
$validRows = [];
|
|
|
|
foreach ($rows as $rowNumber => $row) {
|
|
if ($this->isImportRowEmpty($row)) {
|
|
continue;
|
|
}
|
|
|
|
$normalized = $this->normalizeImportRow($row);
|
|
$validator = Validator::make($normalized, [
|
|
'academic_year_id' => ['required', Rule::exists('academic_years', 'id')],
|
|
'department_id' => ['required', Rule::exists('departments', 'id')],
|
|
'name' => ['required', 'string', 'max:100'],
|
|
'level' => ['required', 'string', 'max:10'],
|
|
'homeroom_teacher' => ['nullable', 'string', 'max:100'],
|
|
'capacity' => ['nullable', 'integer', 'min:1'],
|
|
'is_active' => ['required', 'boolean'],
|
|
], [], [
|
|
'academic_year_id' => 'tahun ajaran',
|
|
'department_id' => 'program keahlian',
|
|
'name' => 'nama kelas',
|
|
'level' => 'tingkat',
|
|
'homeroom_teacher' => 'wali kelas',
|
|
'capacity' => 'kapasitas',
|
|
'is_active' => 'status',
|
|
]);
|
|
|
|
$errorMessages = [];
|
|
$action = 'insert';
|
|
|
|
if ($validator->fails()) {
|
|
$errorMessages = $validator->errors()->all();
|
|
$errors[] = 'Baris ' . $rowNumber . ': ' . implode(' ', $errorMessages);
|
|
} else {
|
|
$payload = $validator->validated();
|
|
$lookup = SchoolClass::query()
|
|
->where('academic_year_id', $payload['academic_year_id'])
|
|
->where('name', $payload['name'])
|
|
->first();
|
|
|
|
if ($lookup) {
|
|
$action = 'update';
|
|
}
|
|
|
|
$validRows[] = [
|
|
'row_number' => $rowNumber,
|
|
'normalized' => $payload,
|
|
'action' => $action,
|
|
];
|
|
}
|
|
|
|
$previewRows[] = [
|
|
'row_number' => $rowNumber,
|
|
'data' => $normalized,
|
|
'errors' => $errorMessages,
|
|
'is_valid' => empty($errorMessages),
|
|
'action' => $action,
|
|
];
|
|
}
|
|
|
|
return [
|
|
'token' => Str::uuid()->toString(),
|
|
'rows' => $previewRows,
|
|
'valid_rows' => $validRows,
|
|
'errors' => $errors,
|
|
'summary' => [
|
|
'total_rows' => count($previewRows),
|
|
'valid_rows' => count($validRows),
|
|
'invalid_rows' => count(array_filter($previewRows, fn ($row) => ! $row['is_valid'])),
|
|
'insert_rows' => count(array_filter($previewRows, fn ($row) => $row['is_valid'] && $row['action'] === 'insert')),
|
|
'update_rows' => count(array_filter($previewRows, fn ($row) => $row['is_valid'] && $row['action'] === 'update')),
|
|
],
|
|
];
|
|
}
|
|
|
|
protected function normalizeImportRow(array $row): array
|
|
{
|
|
$normalized = [];
|
|
|
|
foreach ($row as $key => $value) {
|
|
$normalizedKey = Str::of((string) $key)
|
|
->trim()
|
|
->lower()
|
|
->replaceMatches('/[^a-z0-9]+/u', '_')
|
|
->trim('_')
|
|
->value();
|
|
|
|
$normalized[$normalizedKey] = is_string($value) ? trim($value) : $value;
|
|
}
|
|
|
|
$academicYearId = $this->resolveAcademicYearId($normalized['academic_year_id'] ?? ($normalized['tahun_ajaran'] ?? null));
|
|
$departmentId = $this->resolveDepartmentId($normalized['department_id'] ?? ($normalized['program_keahlian'] ?? null));
|
|
|
|
return [
|
|
'academic_year_id' => $academicYearId,
|
|
'department_id' => $departmentId,
|
|
'name' => $this->nullIfBlank($normalized['name'] ?? ($normalized['nama_kelas'] ?? null)),
|
|
'level' => strtoupper((string) ($this->nullIfBlank($normalized['level'] ?? ($normalized['tingkat'] ?? null)) ?? '')),
|
|
'homeroom_teacher' => $this->nullIfBlank($normalized['homeroom_teacher'] ?? ($normalized['wali_kelas'] ?? null)),
|
|
'capacity' => $this->normalizeImportInteger($normalized['capacity'] ?? ($normalized['kapasitas'] ?? null)),
|
|
'is_active' => $this->normalizeImportBoolean($normalized['is_active'] ?? ($normalized['status'] ?? null)),
|
|
];
|
|
}
|
|
|
|
protected function resolveAcademicYearId(mixed $value): mixed
|
|
{
|
|
if ($value === null || $value === '') {
|
|
return null;
|
|
}
|
|
|
|
if (is_numeric($value)) {
|
|
return $value;
|
|
}
|
|
|
|
return AcademicYear::where('name', trim((string) $value))->value('id');
|
|
}
|
|
|
|
protected function resolveDepartmentId(mixed $value): mixed
|
|
{
|
|
if ($value === null || $value === '') {
|
|
return null;
|
|
}
|
|
|
|
if (is_numeric($value)) {
|
|
return $value;
|
|
}
|
|
|
|
$normalizedValue = trim((string) $value);
|
|
|
|
return Department::query()
|
|
->where('name', $normalizedValue)
|
|
->orWhere('code', strtoupper($normalizedValue))
|
|
->value('id');
|
|
}
|
|
|
|
protected function parseImportFile(string $path, string $originalName): array
|
|
{
|
|
$extension = strtolower(pathinfo($originalName, PATHINFO_EXTENSION));
|
|
|
|
return match ($extension) {
|
|
'xlsx' => $this->parseXlsxRows($path),
|
|
default => $this->parseCsvRows($path),
|
|
};
|
|
}
|
|
|
|
protected function parseCsvRows(string $path): array
|
|
{
|
|
$handle = fopen($path, 'r');
|
|
|
|
if (! $handle) {
|
|
return [];
|
|
}
|
|
|
|
$rows = [];
|
|
$headers = null;
|
|
$rowNumber = 1;
|
|
|
|
while (($data = fgetcsv($handle, 0, ',')) !== false) {
|
|
$rowNumber++;
|
|
|
|
if ($headers === null) {
|
|
$headers = array_map(fn ($header) => trim((string) $header), $data);
|
|
continue;
|
|
}
|
|
|
|
if (count(array_filter($data, fn ($value) => trim((string) $value) !== '')) === 0) {
|
|
continue;
|
|
}
|
|
|
|
$rows[$rowNumber] = array_combine($headers, array_pad($data, count($headers), null));
|
|
}
|
|
|
|
fclose($handle);
|
|
|
|
return $rows;
|
|
}
|
|
|
|
protected function parseXlsxRows(string $path): array
|
|
{
|
|
$zip = new \ZipArchive();
|
|
|
|
if ($zip->open($path) !== true) {
|
|
throw ValidationException::withMessages([
|
|
'import_file' => 'File Excel tidak bisa dibuka.',
|
|
]);
|
|
}
|
|
|
|
$sharedStrings = [];
|
|
$sharedStringsXml = $zip->getFromName('xl/sharedStrings.xml');
|
|
if ($sharedStringsXml) {
|
|
$xml = simplexml_load_string($sharedStringsXml);
|
|
if ($xml && isset($xml->si)) {
|
|
foreach ($xml->si as $item) {
|
|
$text = '';
|
|
if (isset($item->t)) {
|
|
$text = (string) $item->t;
|
|
} elseif (isset($item->r)) {
|
|
foreach ($item->r as $run) {
|
|
$text .= (string) $run->t;
|
|
}
|
|
}
|
|
$sharedStrings[] = $text;
|
|
}
|
|
}
|
|
}
|
|
|
|
$sheetXml = $zip->getFromName('xl/worksheets/sheet1.xml');
|
|
$zip->close();
|
|
|
|
if (! $sheetXml) {
|
|
throw ValidationException::withMessages([
|
|
'import_file' => 'Worksheet pertama pada file Excel tidak ditemukan.',
|
|
]);
|
|
}
|
|
|
|
$xml = simplexml_load_string($sheetXml);
|
|
$xml->registerXPathNamespace('main', 'http://schemas.openxmlformats.org/spreadsheetml/2006/main');
|
|
$rows = [];
|
|
$headers = null;
|
|
|
|
foreach ($xml->xpath('//main:sheetData/main:row') as $rowIndex => $row) {
|
|
$values = [];
|
|
|
|
foreach ($row->c as $cell) {
|
|
$reference = (string) $cell['r'];
|
|
$column = preg_replace('/\d+/', '', $reference);
|
|
$index = $this->columnLetterToIndex($column);
|
|
$type = (string) $cell['t'];
|
|
$value = '';
|
|
|
|
if ($type === 'inlineStr' && isset($cell->is->t)) {
|
|
$value = (string) $cell->is->t;
|
|
} elseif ($type === 's') {
|
|
$sharedIndex = (int) ($cell->v ?? 0);
|
|
$value = $sharedStrings[$sharedIndex] ?? '';
|
|
} else {
|
|
$value = (string) ($cell->v ?? '');
|
|
}
|
|
|
|
$values[$index] = $value;
|
|
}
|
|
|
|
if (empty($values)) {
|
|
continue;
|
|
}
|
|
|
|
ksort($values);
|
|
$maxIndex = max(array_keys($values));
|
|
$ordered = [];
|
|
|
|
for ($index = 0; $index <= $maxIndex; $index++) {
|
|
$ordered[$index] = $values[$index] ?? null;
|
|
}
|
|
|
|
if ($headers === null) {
|
|
$headers = array_map(fn ($header) => trim((string) $header), $ordered);
|
|
continue;
|
|
}
|
|
|
|
$rows[$rowIndex + 1] = array_combine($headers, array_pad($ordered, count($headers), null));
|
|
}
|
|
|
|
return $rows;
|
|
}
|
|
|
|
protected function columnLetterToIndex(string $letters): int
|
|
{
|
|
$letters = strtoupper($letters);
|
|
$index = 0;
|
|
|
|
for ($i = 0; $i < strlen($letters); $i++) {
|
|
$index = ($index * 26) + (ord($letters[$i]) - 64);
|
|
}
|
|
|
|
return $index - 1;
|
|
}
|
|
|
|
protected function isImportRowEmpty(array $row): bool
|
|
{
|
|
return count(array_filter($row, fn ($value) => ! is_null($value) && $value !== '')) === 0;
|
|
}
|
|
|
|
protected function nullIfBlank(mixed $value): mixed
|
|
{
|
|
if (is_string($value) && trim($value) === '') {
|
|
return null;
|
|
}
|
|
|
|
return $value;
|
|
}
|
|
|
|
protected function normalizeImportInteger(mixed $value): mixed
|
|
{
|
|
$value = $this->nullIfBlank($value);
|
|
|
|
if ($value === null) {
|
|
return null;
|
|
}
|
|
|
|
if (is_int($value)) {
|
|
return $value;
|
|
}
|
|
|
|
if (is_float($value)) {
|
|
return (int) round($value);
|
|
}
|
|
|
|
if (is_numeric($value)) {
|
|
return (int) round((float) $value);
|
|
}
|
|
|
|
if (is_string($value)) {
|
|
$normalized = trim($value);
|
|
$normalized = str_replace([',', ' '], '', $normalized);
|
|
|
|
if (preg_match('/^\d+(?:\.0+)?$/', $normalized) === 1) {
|
|
return (int) round((float) $normalized);
|
|
}
|
|
}
|
|
|
|
return $value;
|
|
}
|
|
|
|
protected function normalizeImportBoolean(mixed $value): bool
|
|
{
|
|
if ($value === null || $value === '') {
|
|
return true;
|
|
}
|
|
|
|
if (is_bool($value)) {
|
|
return $value;
|
|
}
|
|
|
|
$normalized = strtolower(trim((string) $value));
|
|
|
|
return match ($normalized) {
|
|
'1', 'true', 'ya', 'y', 'yes', 'aktif' => true,
|
|
'0', 'false', 'tidak', 't', 'no', 'nonaktif', 'inactive' => false,
|
|
default => true,
|
|
};
|
|
}
|
|
|
|
protected function nextLevel(?string $level): ?string
|
|
{
|
|
return match (strtoupper((string) $level)) {
|
|
'X' => 'XI',
|
|
'XI' => 'XII',
|
|
'XII' => null,
|
|
default => $level,
|
|
};
|
|
}
|
|
|
|
protected function transformClassName(string $name, ?string $fromLevel, ?string $toLevel): string
|
|
{
|
|
if (! $fromLevel || ! $toLevel || strtoupper($fromLevel) === strtoupper($toLevel)) {
|
|
return $name;
|
|
}
|
|
|
|
return preg_replace('/^' . preg_quote($fromLevel, '/') . '\b/i', $toLevel, $name) ?: $name;
|
|
}
|
|
}
|