265 lines
9.2 KiB
Dart
265 lines
9.2 KiB
Dart
import 'package:supabase_flutter/supabase_flutter.dart';
|
|
import 'package:intl/intl.dart';
|
|
|
|
import 'holiday_service.dart';
|
|
|
|
class DbAbsensi {
|
|
final SupabaseClient _supabase = Supabase.instance.client;
|
|
|
|
Future<List<Map<String, dynamic>>> getAbsensi(String nisOrId) async {
|
|
try {
|
|
String idMurid = nisOrId;
|
|
String? studentNis;
|
|
String? userId;
|
|
String? studentName;
|
|
|
|
final isNumeric = RegExp(r'^\d+$').hasMatch(nisOrId);
|
|
if (isNumeric) {
|
|
final res = await _supabase
|
|
.from('murid')
|
|
.select('id_tabel, user_id, nis, nama')
|
|
.eq('nis', nisOrId)
|
|
.maybeSingle();
|
|
if (res != null) {
|
|
idMurid = res['id_tabel'].toString();
|
|
userId = res['user_id']?.toString();
|
|
studentNis = res['nis']?.toString();
|
|
studentName = res['nama']?.toString();
|
|
}
|
|
} else {
|
|
final res = await _supabase
|
|
.from('murid')
|
|
.select('user_id, nis, nama')
|
|
.eq('id_tabel', nisOrId)
|
|
.maybeSingle();
|
|
if (res != null) {
|
|
userId = res['user_id']?.toString();
|
|
studentNis = res['nis']?.toString();
|
|
studentName = res['nama']?.toString();
|
|
}
|
|
}
|
|
|
|
// Query absen tanpa join access_bridge (tidak dipakai lagi)
|
|
final response = await _supabase
|
|
.from('absen')
|
|
.select('*, leave_request:id_izin(*), output_alat:id_output_alat(*), murid:id_murid(*), pengumuman:id_pengumuman(*)')
|
|
.eq('id_murid', idMurid)
|
|
.order('date', ascending: false);
|
|
|
|
// Fetch approved leave requests
|
|
List<Map<String, dynamic>> leaveResponse = [];
|
|
if (idMurid.isNotEmpty) {
|
|
try {
|
|
final res = await _supabase
|
|
.from('leave_request')
|
|
.select('*')
|
|
.eq('id_murid', idMurid)
|
|
.eq('verif', true);
|
|
leaveResponse = List<Map<String, dynamic>>.from(res);
|
|
} catch (_) {}
|
|
}
|
|
|
|
final Map<String, Map<String, dynamic>> consolidatedLeave = {};
|
|
for (var req in leaveResponse) {
|
|
final startStr = req['tanggal_mulai']?.toString();
|
|
final endStr = req['tanggal_selesai']?.toString();
|
|
if (startStr == null || endStr == null) continue;
|
|
try {
|
|
final start = DateTime.parse(startStr);
|
|
final end = DateTime.parse(endStr);
|
|
for (int d = 0; d <= end.difference(start).inDays; d++) {
|
|
final currentDate = start.add(Duration(days: d));
|
|
final dateKey = DateFormat('yyyy-MM-dd').format(currentDate);
|
|
consolidatedLeave[dateKey] = {
|
|
'id_tabel': req['id_tabel']?.toString(),
|
|
'ket_opsi': req['ket_opsi']?.toString() ?? 'izin',
|
|
};
|
|
}
|
|
} catch (_) {}
|
|
}
|
|
|
|
final Map<String, dynamic> studentMuridMap = {
|
|
'id_tabel': idMurid,
|
|
'user_id': userId,
|
|
'nis': studentNis,
|
|
'nama': studentName,
|
|
};
|
|
|
|
return _mergeAbsenData(response, idMurid, studentMuridMap, consolidatedLeave);
|
|
} catch (e) {
|
|
throw Exception('Gagal memuat data absensi: $e');
|
|
}
|
|
}
|
|
|
|
List<Map<String, dynamic>> _mergeAbsenData(
|
|
List<dynamic> absenResponse,
|
|
String idMurid,
|
|
Map<String, dynamic> studentMuridMap,
|
|
Map<String, Map<String, dynamic>> consolidatedLeave) {
|
|
|
|
final List<Map<String, dynamic>> mergedList = [];
|
|
final Set<String> processedDates = {};
|
|
|
|
for (var item in absenResponse) {
|
|
final row = Map<String, dynamic>.from(item);
|
|
final dateKey = row['date']?.toString();
|
|
|
|
if (dateKey != null) {
|
|
processedDates.add(dateKey);
|
|
|
|
// Ambil jam masuk dari output_alat.created_at
|
|
final outputAlat = row['output_alat'];
|
|
if (outputAlat != null && outputAlat['created_at'] != null) {
|
|
final formattedMasuk = _formatTime(outputAlat['created_at']);
|
|
if (formattedMasuk != null) {
|
|
row['masuk'] = formattedMasuk;
|
|
}
|
|
}
|
|
|
|
// Inject leave_request jika belum ada dari FK
|
|
if (row['leave_request'] == null && consolidatedLeave.containsKey(dateKey)) {
|
|
final leave = consolidatedLeave[dateKey]!;
|
|
row['leave_request'] = {
|
|
'id_tabel': leave['id_tabel'],
|
|
'ket_opsi': leave['ket_opsi'],
|
|
'verif': true,
|
|
};
|
|
}
|
|
}
|
|
mergedList.add(row);
|
|
}
|
|
|
|
// Tambahkan record izin yang tidak punya row absen
|
|
consolidatedLeave.forEach((dateKey, leave) {
|
|
if (!processedDates.contains(dateKey)) {
|
|
processedDates.add(dateKey);
|
|
mergedList.add({
|
|
'date': dateKey,
|
|
'id_murid': idMurid,
|
|
'masuk': null,
|
|
'keluar': null,
|
|
'output_alat': null,
|
|
'murid': studentMuridMap,
|
|
'leave_request': {
|
|
'id_tabel': leave['id_tabel'],
|
|
'ket_opsi': leave['ket_opsi'],
|
|
'verif': true,
|
|
},
|
|
'pengumuman': null,
|
|
});
|
|
}
|
|
});
|
|
|
|
mergedList.sort((a, b) {
|
|
final dateA = a['date']?.toString() ?? '';
|
|
final dateB = b['date']?.toString() ?? '';
|
|
return dateB.compareTo(dateA);
|
|
});
|
|
|
|
return mergedList;
|
|
}
|
|
|
|
static String? _formatTime(dynamic timeVal) {
|
|
if (timeVal == null) return null;
|
|
final str = timeVal.toString().trim();
|
|
if (str.isEmpty) return null;
|
|
|
|
final parsed = DateTime.tryParse(str);
|
|
if (parsed != null) {
|
|
return DateFormat('HH:mm').format(parsed.toLocal());
|
|
}
|
|
|
|
final parts = str.split(':');
|
|
if (parts.length >= 2) {
|
|
var hour = parts[0];
|
|
if (hour.contains('T')) hour = hour.split('T').last;
|
|
hour = hour.padLeft(2, '0');
|
|
var minute = parts[1];
|
|
if (minute.length > 2) minute = minute.substring(0, 2);
|
|
minute = minute.padLeft(2, '0');
|
|
return '$hour:$minute';
|
|
}
|
|
return str;
|
|
}
|
|
|
|
static String getStatus(Map<String, dynamic> row) {
|
|
// 0. Manual override (prioritas tertinggi)
|
|
if (row['Null_data']?.toString() == 'Belum Absen') return 'Belum Absen';
|
|
if (row['ket_sakit']?.toString().startsWith('MANUAL_') ?? false) return 'Sakit';
|
|
if (row['ket_izin']?.toString().startsWith('MANUAL_') ?? false) return 'Izin';
|
|
if (row['ket_libur']?.toString().startsWith('MANUAL_') ?? false) return 'Libur';
|
|
if (row['ket_alpha']?.toString().startsWith('MANUAL_') ?? false) return 'Alpha';
|
|
if (row['ket_hadir']?.toString().startsWith('MANUAL_') ?? false) {
|
|
final kh = row['ket_hadir'].toString();
|
|
if (kh.toLowerCase().contains('terlambat')) return 'Terlambat';
|
|
return 'Hadir';
|
|
}
|
|
|
|
// 1. Izin dan Sakit dari leave_request yang terverifikasi
|
|
if (row['leave_request'] != null && row['leave_request']['verif'] == true) {
|
|
final ketOpsi = (row['leave_request']['ket_opsi'] ?? '').toString().toLowerCase();
|
|
if (ketOpsi == 'sakit') return 'Sakit';
|
|
if (ketOpsi == 'izin') return 'Izin';
|
|
final ket = (row['leave_request']['keterangan'] ?? '').toString().toLowerCase();
|
|
if (ket.contains('sakit')) return 'Sakit';
|
|
return 'Izin';
|
|
}
|
|
|
|
if (row['ket_sakit'] != null && row['ket_sakit'].toString().isNotEmpty) return 'Sakit';
|
|
if (row['ket_izin'] != null && row['ket_izin'].toString().isNotEmpty) return 'Izin';
|
|
|
|
// 2. Libur dari hari Minggu / pengumuman / tabel absen
|
|
final dateStr = row['date']?.toString() ?? '';
|
|
bool isSundayOrHoliday = false;
|
|
if (dateStr.isNotEmpty) {
|
|
try {
|
|
final date = DateTime.parse(dateStr);
|
|
if (date.weekday == DateTime.sunday || HolidayService.isHoliday(date)) {
|
|
isSundayOrHoliday = true;
|
|
}
|
|
} catch (_) {}
|
|
}
|
|
|
|
bool isPengumumanLibur = false;
|
|
if (row['pengumuman'] != null) {
|
|
final title = (row['pengumuman']['title'] ?? '').toString().toLowerCase();
|
|
final keterangan = (row['pengumuman']['keterangan'] ?? '').toString().toLowerCase();
|
|
if (title.contains('libur') || keterangan.contains('libur')) {
|
|
isPengumumanLibur = true;
|
|
}
|
|
}
|
|
|
|
if (isSundayOrHoliday || isPengumumanLibur) return 'Libur';
|
|
if (row['ket_libur'] != null && row['ket_libur'].toString().isNotEmpty) return 'Libur';
|
|
|
|
// 3. Hadir / Terlambat dari output_alat.created_at
|
|
final outputAlat = row['output_alat'];
|
|
if (outputAlat != null && outputAlat['created_at'] != null) {
|
|
final formattedMasuk = _formatTime(outputAlat['created_at']) ?? '07:00';
|
|
if (formattedMasuk.trim().isNotEmpty) {
|
|
final parts = formattedMasuk.split(':');
|
|
if (parts.length >= 2) {
|
|
final hour = int.tryParse(parts[0]) ?? 0;
|
|
final minute = int.tryParse(parts[1]) ?? 0;
|
|
if (hour > 7 || (hour == 7 && minute > 0)) return 'Terlambat';
|
|
}
|
|
return 'Hadir';
|
|
}
|
|
}
|
|
|
|
// Fallback dari kolom absen langsung
|
|
if (row['ket_hadir'] != null && row['ket_hadir'].toString().isNotEmpty) {
|
|
final kh = row['ket_hadir'].toString();
|
|
if (kh.toLowerCase().contains('terlambat')) return 'Terlambat';
|
|
return 'Hadir';
|
|
}
|
|
if (row['terlambat'] != null && row['terlambat'].toString().isNotEmpty) return 'Terlambat';
|
|
if (row['masuk'] != null && row['masuk'].toString().isNotEmpty) return 'Hadir';
|
|
|
|
// 4. Alpha
|
|
if (row['ket_alpha'] != null && row['ket_alpha'].toString().isNotEmpty) return 'Alpha';
|
|
|
|
return 'Belum Absen';
|
|
}
|
|
}
|