511 lines
17 KiB
PHP
511 lines
17 KiB
PHP
<?php
|
|
|
|
namespace App\Http\Controllers;
|
|
|
|
use App\Models\Department;
|
|
use App\Models\StudentAcademicHistory;
|
|
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 DepartmentController extends Controller
|
|
{
|
|
public function index(Request $request): View
|
|
{
|
|
$search = trim((string) $request->get('search'));
|
|
|
|
$departments = Department::withCount(['classes', 'students'])
|
|
->when($search !== '', function ($query) use ($search) {
|
|
$query->where('code', 'like', "%{$search}%")
|
|
->orWhere('name', 'like', "%{$search}%")
|
|
->orWhere('description', 'like', "%{$search}%");
|
|
})
|
|
->latest()
|
|
->paginate(10)
|
|
->withQueryString();
|
|
|
|
return view('departments.index', [
|
|
'departments' => $departments,
|
|
'search' => $search,
|
|
'importPreview' => $request->session()->get('department_import_preview'),
|
|
]);
|
|
}
|
|
|
|
public function create(): View
|
|
{
|
|
return view('departments.create');
|
|
}
|
|
|
|
public function store(Request $request): RedirectResponse
|
|
{
|
|
$data = $request->validate([
|
|
'code' => ['required', 'string', 'max:20', 'unique:departments,code'],
|
|
'name' => ['required', 'string', 'max:100'],
|
|
'description' => ['nullable', 'string'],
|
|
'is_active' => ['nullable', 'boolean'],
|
|
]);
|
|
|
|
$data['is_active'] = $request->boolean('is_active');
|
|
$data['code'] = strtoupper($data['code']);
|
|
|
|
Department::create($data);
|
|
|
|
return redirect()->route('departments.index')->with('success', 'Program keahlian berhasil ditambahkan.');
|
|
}
|
|
|
|
public function show(Department $department): RedirectResponse
|
|
{
|
|
return redirect()->route('departments.edit', $department);
|
|
}
|
|
|
|
public function edit(Department $department): View
|
|
{
|
|
return view('departments.edit', [
|
|
'department' => $department,
|
|
]);
|
|
}
|
|
|
|
public function update(Request $request, Department $department): RedirectResponse
|
|
{
|
|
$data = $request->validate([
|
|
'code' => ['required', 'string', 'max:20', Rule::unique('departments', 'code')->ignore($department->id)],
|
|
'name' => ['required', 'string', 'max:100'],
|
|
'description' => ['nullable', 'string'],
|
|
'is_active' => ['nullable', 'boolean'],
|
|
]);
|
|
|
|
$data['is_active'] = $request->boolean('is_active');
|
|
$data['code'] = strtoupper($data['code']);
|
|
|
|
$department->update($data);
|
|
|
|
return redirect()->route('departments.index')->with('success', 'Program keahlian berhasil diperbarui.');
|
|
}
|
|
|
|
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 program keahlian 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('department_import_preview', $preview);
|
|
|
|
return redirect()->route('departments.index');
|
|
}
|
|
|
|
public function import(Request $request): RedirectResponse
|
|
{
|
|
$request->validate([
|
|
'preview_token' => ['required', 'string'],
|
|
]);
|
|
|
|
$preview = $request->session()->get('department_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'], ['code', 'name', 'description', 'is_active']);
|
|
$department = Department::query()->where('code', $payload['code'])->first();
|
|
|
|
if (! $department) {
|
|
$department = Department::query()->where('name', $payload['name'])->first();
|
|
}
|
|
|
|
if ($department) {
|
|
$department->update($payload);
|
|
$updated++;
|
|
continue;
|
|
}
|
|
|
|
Department::create($payload);
|
|
$inserted++;
|
|
}
|
|
});
|
|
|
|
$request->session()->forget('department_import_preview');
|
|
|
|
Alert::success('Berhasil', "Import program keahlian selesai. {$inserted} data baru ditambahkan, {$updated} data diperbarui.");
|
|
|
|
return redirect()->route('departments.index')->with('success', "Import program keahlian selesai. {$inserted} data baru ditambahkan, {$updated} data diperbarui.");
|
|
}
|
|
|
|
public function downloadImportTemplate()
|
|
{
|
|
$spreadsheet = new Spreadsheet();
|
|
$sheet = $spreadsheet->getActiveSheet();
|
|
$sheet->setTitle('Import Program Keahlian');
|
|
|
|
$headers = ['Kode', 'Nama Program Keahlian', 'Deskripsi', 'Status'];
|
|
$sheet->fromArray($headers, null, 'A1');
|
|
$sheet->fromArray(['TKJ', 'Teknik Komputer dan Jaringan', 'Jurusan jaringan dan komputer', 'Aktif'], null, 'A2');
|
|
|
|
foreach (range(1, count($headers)) as $columnIndex) {
|
|
$sheet->getColumnDimensionByColumn($columnIndex)->setAutoSize(true);
|
|
}
|
|
|
|
$guideSheet = $spreadsheet->createSheet();
|
|
$guideSheet->setTitle('Panduan');
|
|
$guideSheet->fromArray([
|
|
['Kolom', 'Keterangan'],
|
|
['Kode', 'Wajib, unik. Akan disimpan dalam huruf besar.'],
|
|
['Nama Program Keahlian', 'Wajib diisi.'],
|
|
['Deskripsi', 'Opsional.'],
|
|
['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-program-keahlian.xlsx"',
|
|
]);
|
|
}
|
|
|
|
public function destroy(Department $department): RedirectResponse
|
|
{
|
|
$department->loadCount('students');
|
|
$classesWithStudentsCount = $department->classes()->withCount('students')->get()->where('students_count', '>', 0)->count();
|
|
$emptyClassesCount = $department->classes()->withCount('students')->get()->where('students_count', 0)->count();
|
|
|
|
if ($department->students_count > 0 || $classesWithStudentsCount > 0) {
|
|
Alert::error('Tidak bisa dihapus', 'Program keahlian ini masih digunakan oleh data siswa aktif atau kelas yang masih berisi siswa.');
|
|
|
|
return redirect()->route('departments.index')->withErrors([
|
|
'delete' => 'Program keahlian tidak bisa dihapus karena masih digunakan oleh data siswa aktif atau kelas yang masih berisi siswa.',
|
|
]);
|
|
}
|
|
|
|
DB::transaction(function () use ($department) {
|
|
$classIds = $department->classes()->pluck('id');
|
|
|
|
StudentAcademicHistory::query()
|
|
->whereIn('class_id', $classIds)
|
|
->delete();
|
|
|
|
$department->classes()->delete();
|
|
$department->delete();
|
|
});
|
|
|
|
$message = $emptyClassesCount > 0
|
|
? "Program keahlian berhasil dihapus beserta {$emptyClassesCount} kelas kosong terkait."
|
|
: 'Program keahlian berhasil dihapus.';
|
|
|
|
Alert::success('Berhasil', $message);
|
|
|
|
return redirect()->route('departments.index')->with('success', $message);
|
|
}
|
|
|
|
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, [
|
|
'code' => ['required', 'string', 'max:20'],
|
|
'name' => ['required', 'string', 'max:100'],
|
|
'description' => ['nullable', 'string'],
|
|
'is_active' => ['required', 'boolean'],
|
|
], [], [
|
|
'code' => 'kode',
|
|
'name' => 'nama program keahlian',
|
|
'description' => 'deskripsi',
|
|
'is_active' => 'status',
|
|
]);
|
|
|
|
$errorMessages = [];
|
|
$action = 'insert';
|
|
|
|
if ($validator->fails()) {
|
|
$errorMessages = $validator->errors()->all();
|
|
$errors[] = 'Baris ' . $rowNumber . ': ' . implode(' ', $errorMessages);
|
|
} else {
|
|
$payload = $validator->validated();
|
|
$lookup = Department::query()->where('code', $payload['code'])->first();
|
|
|
|
if (! $lookup) {
|
|
$lookup = Department::query()->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;
|
|
}
|
|
|
|
return [
|
|
'code' => strtoupper((string) ($this->nullIfBlank($normalized['code'] ?? ($normalized['kode'] ?? null)) ?? '')),
|
|
'name' => $this->nullIfBlank($normalized['name'] ?? ($normalized['nama_program_keahlian'] ?? null)),
|
|
'description' => $this->nullIfBlank($normalized['description'] ?? ($normalized['deskripsi'] ?? null)),
|
|
'is_active' => $this->normalizeImportBoolean($normalized['is_active'] ?? ($normalized['status'] ?? null)),
|
|
];
|
|
}
|
|
|
|
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 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,
|
|
};
|
|
}
|
|
}
|