Sistem Absensi Guru QR Code

Langkah 1: Membuat Database di phpMyAdmin

Pertama, buat database baru di phpMyAdmin dengan nama db_absensi_guru.

CREATE DATABASE db_absensi_guru;
USE db_absensi_guru;
                    

Kemudian buat dua tabel utama: teachers dan attendance.

-- Tabel untuk data guru
CREATE TABLE teachers (
    id VARCHAR(20) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    status VARCHAR(50) NOT NULL,
    gender CHAR(1) NOT NULL,
    photo_url VARCHAR(255)
);

-- Tabel untuk data absensi
CREATE TABLE attendance (
    attendance_id INT AUTO_INCREMENT PRIMARY KEY,
    teacher_id VARCHAR(20) NOT NULL,
    date DATE NOT NULL,
    time TIME NOT NULL,
    timestamp BIGINT NOT NULL,
    FOREIGN KEY (teacher_id) REFERENCES teachers(id)
);

-- Indeks untuk mempercepat pencarian
CREATE INDEX idx_attendance_date ON attendance(date);
CREATE INDEX idx_attendance_teacher ON attendance(teacher_id);
                    

Langkah 2: Mengisi Data Guru

Masukkan data guru ke dalam tabel teachers:

INSERT INTO teachers (id, name, status, gender, photo_url) VALUES
('RON03740897', 'Sahroni, S.Pd.', 'PTK', 'L', 'https://i.imgur.com/gckwi58.jpg'),
('SAID06660791', 'M. Said', 'PTK', 'L', 'https://i.imgur.com/4g3qTZf.png');
                    

Langkah 3: Membuat File Koneksi Database

Buat file config.php untuk koneksi database:

<?php
// config.php - File konfigurasi database

$host = 'localhost';
$username = 'root';     // Ganti dengan username database Anda
$password = '';         // Ganti dengan password database Anda
$database = 'db_absensi_guru';

// Membuat koneksi
$conn = new mysqli($host, $username, $password, $database);

// Cek koneksi
if ($conn->connect_error) {
    die("Koneksi gagal: " . $conn->connect_error);
}

// Set karakter encoding
$conn->set_charset("utf8");
?>
                    

Langkah 4: Membuat API Endpoint untuk Sinkronisasi

Buat file sync_attendance.php untuk menerima data absensi dari aplikasi:

<?php
// sync_attendance.php - API endpoint untuk sinkronisasi data absensi

header('Content-Type: application/json');
require_once 'config.php';

// Cek apakah request adalah POST
if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
    http_response_code(405);
    echo json_encode(['status' => 'error', 'message' => 'Method not allowed']);
    exit;
}

// Ambil data JSON dari request
$json_data = file_get_contents('php://input');
$data = json_decode($json_data, true);

if (!$data || !isset($data['attendance'])) {
    http_response_code(400);
    echo json_encode(['status' => 'error', 'message' => 'Invalid data format']);
    exit;
}

$attendance_records = $data['attendance'];
$success_count = 0;
$error_count = 0;

// Mulai transaksi
$conn->begin_transaction();

try {
    foreach ($attendance_records as $record) {
        // Cek apakah guru ada di database
        $stmt = $conn->prepare("SELECT id FROM teachers WHERE id = ?");
        $stmt->bind_param("s", $record['id']);
        $stmt->execute();
        $result = $stmt->get_result();
        
        if ($result->num_rows === 0) {
            // Guru tidak ditemukan, tambahkan ke database
            $stmt = $conn->prepare("INSERT INTO teachers (id, name, status, gender, photo_url) VALUES (?, ?, ?, ?, ?)");
            $stmt->bind_param("sssss", $record['id'], $record['name'], $record['status'], $record['gender'], $record['photo']);
            $stmt->execute();
        }
        
        // Format tanggal dari format Indonesia ke format MySQL (YYYY-MM-DD)
        $date_parts = explode('/', $record['date']);
        if (count($date_parts) === 3) {
            $mysql_date = $date_parts[2] . '-' . $date_parts[1] . '-' . $date_parts[0];
        } else {
            // Jika format tanggal tidak sesuai, gunakan tanggal hari ini
            $mysql_date = date('Y-m-d');
        }
        
        // Cek apakah absensi sudah ada untuk guru dan tanggal tersebut
        $stmt = $conn->prepare("SELECT attendance_id FROM attendance WHERE teacher_id = ? AND date = ?");
        $stmt->bind_param("ss", $record['id'], $mysql_date);
        $stmt->execute();
        $result = $stmt->get_result();
        
        if ($result->num_rows === 0) {
            // Absensi belum ada, tambahkan ke database
            $stmt = $conn->prepare("INSERT INTO attendance (teacher_id, date, time, timestamp) VALUES (?, ?, ?, ?)");
            $stmt->bind_param("sssi", $record['id'], $mysql_date, $record['time'], $record['timestamp']);
            
            if ($stmt->execute()) {
                $success_count++;
            } else {
                $error_count++;
            }
        } else {
            // Absensi sudah ada, update waktu jika perlu
            $stmt = $conn->prepare("UPDATE attendance SET time = ?, timestamp = ? WHERE teacher_id = ? AND date = ?");
            $stmt->bind_param("siss", $record['time'], $record['timestamp'], $record['id'], $mysql_date);
            
            if ($stmt->execute()) {
                $success_count++;
            } else {
                $error_count++;
            }
        }
    }
    
    // Commit transaksi jika semua berhasil
    $conn->commit();
    
    echo json_encode([
        'status' => 'success',
        'message' => "Sinkronisasi berhasil: $success_count data berhasil, $error_count gagal",
        'success_count' => $success_count,
        'error_count' => $error_count
    ]);
    
} catch (Exception $e) {
    // Rollback transaksi jika terjadi error
    $conn->rollback();
    
    http_response_code(500);
    echo json_encode([
        'status' => 'error',
        'message' => 'Terjadi kesalahan: ' . $e->getMessage()
    ]);
}

// Tutup koneksi
$conn->close();
?>
                    

Langkah 5: Membuat API untuk Mengambil Data

Buat file get_attendance.php untuk mengambil data absensi dari server:

<?php
// get_attendance.php - API endpoint untuk mengambil data absensi

header('Content-Type: application/json');
require_once 'config.php';

// Parameter filter (opsional)
$date_filter = isset($_GET['date']) ? $_GET['date'] : null;
$teacher_filter = isset($_GET['teacher_id']) ? $_GET['teacher_id'] : null;

// Buat query dasar
$query = "SELECT a.attendance_id, a.teacher_id, t.name, t.status, t.gender, 
          t.photo_url, a.date, a.time, a.timestamp 
          FROM attendance a 
          JOIN teachers t ON a.teacher_id = t.id";

// Tambahkan filter jika ada
$where_clauses = [];
$params = [];
$types = "";

if ($date_filter) {
    $where_clauses[] = "a.date = ?";
    $params[] = $date_filter;
    $types .= "s";
}

if ($teacher_filter) {
    $where_clauses[] = "a.teacher_id = ?";
    $params[] = $teacher_filter;
    $types .= "s";
}

if (!empty($where_clauses)) {
    $query .= " WHERE " . implode(" AND ", $where_clauses);
}

// Urutkan berdasarkan timestamp terbaru
$query .= " ORDER BY a.timestamp DESC";

// Prepare dan execute query
$stmt = $conn->prepare($query);

if (!empty($params)) {
    $stmt->bind_param($types, ...$params);
}

$stmt->execute();
$result = $stmt->get_result();

$attendance_data = [];
while ($row = $result->fetch_assoc()) {
    // Format tanggal ke format Indonesia (DD/MM/YYYY)
    $date = new DateTime($row['date']);
    $formatted_date = $date->format('d/m/Y');
    
    $attendance_data[] = [
        'id' => $row['teacher_id'],
        'name' => $row['name'],
        'status' => $row['status'],
        'gender' => $row['gender'],
        'photo' => $row['photo_url'],
        'date' => $formatted_date,
        'time' => $row['time'],
        'timestamp' => (int)$row['timestamp']
    ];
}

echo json_encode([
    'status' => 'success',
    'count' => count($attendance_data),
    'data' => $attendance_data
]);

// Tutup koneksi
$conn->close();
?>
                    

Langkah 6: Mengubah Kode JavaScript untuk Sinkronisasi

Ubah fungsi syncToServer di aplikasi untuk mengirim data ke server:

// Fungsi untuk sinkronisasi data ke server
function syncToServer() {
    const syncButton = document.getElementById('syncToServer');
    const syncButtonText = document.getElementById('syncButtonText');
    const originalText = syncButtonText.textContent;
    
    // Tampilkan indikator loading
    syncButtonText.innerHTML = '<span class="sync-spinner"></span> Sinkronisasi...';
    syncButton.disabled = true;
    
    // Data yang akan dikirim ke server
    const dataToSync = {
        attendance: attendanceData
    };
    
    // URL API endpoint
    const apiUrl = 'https://your-server.com/api/sync_attendance.php';
    
    // Kirim data ke server
    fetch(apiUrl, {
        method: 'POST',
        headers: {
            'Content-Type': 'application/json',
        },
        body: JSON.stringify(dataToSync)
    })
    .then(response => {
        if (!response.ok) {
            throw new Error('Network response was not ok');
        }
        return response.json();
    })
    .then(data => {
        if (data.status === 'success') {
            // Sinkronisasi berhasil
            syncButtonText.innerHTML = '✅ Berhasil';
            
            // Tampilkan notifikasi
            showNotification('Sinkronisasi berhasil', `${data.success_count} data berhasil disinkronkan.`, 'success');
            
            // Kembalikan teks tombol setelah beberapa detik
            setTimeout(() => {
                syncButtonText.textContent = originalText;
                syncButton.disabled = false;
            }, 3000);
        } else {
            throw new Error(data.message || 'Unknown error');
        }
    })
    .catch(error => {
        console.error('Error syncing data:', error);
        
        // Sinkronisasi gagal
        syncButtonText.innerHTML = '❌ Gagal';
        
        // Tampilkan notifikasi error
        showNotification('Sinkronisasi gagal', error.message, 'error');
        
        // Kembalikan teks tombol setelah beberapa detik
        setTimeout(() => {
            syncButtonText.textContent = originalText;
            syncButton.disabled = false;
        }, 3000);
    });
}

// Fungsi untuk menampilkan notifikasi
function showNotification(title, message, type) {
    // Implementasi notifikasi sesuai kebutuhan
    // Contoh sederhana menggunakan alert
    alert(`${title}: ${message}`);
}
                    

Langkah 7: Membuat Halaman Admin

Buat file admin.php untuk mengelola data absensi:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Admin - Sistem Absensi Guru</title>
    <link href="https://cdn.jsdelivr.net/npm/tailwindcss@2.2.19/dist/tailwind.min.css" rel="stylesheet">
</head>
<body class="bg-gray-100">
    <div class="container mx-auto px-4 py-8">
        <h1 class="text-3xl font-bold text-gray-800 mb-6">Admin Panel - Sistem Absensi Guru</h1>
        
        <div class="bg-white p-6 rounded-xl shadow-lg mb-8">
            <h2 class="text-2xl font-semibold text-gray-700 mb-4">Filter Data</h2>
            <form id="filterForm" class="flex flex-wrap gap-4">
                <div class="w-full md:w-auto">
                    <label class="block text-gray-700 mb-2">Tanggal:</label>
                    <input type="date" name="date" class="p-2 border border-gray-300 rounded-lg">
                </div>
                <div class="w-full md:w-auto">
                    <label class="block text-gray-700 mb-2">Guru:</label>
                    <select name="teacher_id" class="p-2 border border-gray-300 rounded-lg">
                        <option value="">Semua Guru</option>
                        <?php
                        require_once 'config.php';
                        $query = "SELECT id, name FROM teachers ORDER BY name";
                        $result = $conn->query($query);
                        while ($row = $result->fetch_assoc()) {
                            echo "<option value='{$row['id']}'>{$row['name']}</option>";
                        }
                        ?>
                    </select>
                </div>
                <div class="w-full md:w-auto flex items-end">
                    <button type="submit" class="bg-indigo-600 text-white py-2 px-4 rounded-lg hover:bg-indigo-700">
                        Filter
                    </button>
                </div>
            </form>
        </div>
        
        <div class="bg-white p-6 rounded-xl shadow-lg">
            <h2 class="text-2xl font-semibold text-gray-700 mb-4">Data Absensi Guru</h2>
            <div class="overflow-x-auto">
                <table class="min-w-full bg-white">
                    <thead>
                        <tr class="bg-indigo-600 text-white">
                            <th class="py-3 px-4 text-left">Foto</th>
                            <th class="py-3 px-4 text-left">ID</th>
                            <th class="py-3 px-4 text-left">Nama</th>
                            <th class="py-3 px-4 text-left">Status</th>
                            <th class="py-3 px-4 text-left">L/P</th>
                            <th class="py-3 px-4 text-left">Tanggal</th>
                            <th class="py-3 px-4 text-left">Waktu Absen</th>
                            <th class="py-3 px-4 text-left">Aksi</th>
                        </tr>
                    </thead>
                    <tbody id="attendanceTableBody">
                        <?php
                        // Ambil parameter filter
                        $date_filter = isset($_GET['date']) ? $_GET['date'] : null;
                        $teacher_filter = isset($_GET['teacher_id']) ? $_GET['teacher_id'] : null;
                        
                        // Buat query dasar
                        $query = "SELECT a.attendance_id, a.teacher_id, t.name, t.status, t.gender, 
                                  t.photo_url, a.date, a.time, a.timestamp 
                                  FROM attendance a 
                                  JOIN teachers t ON a.teacher_id = t.id";
                        
                        // Tambahkan filter jika ada
                        $where_clauses = [];
                        $params = [];
                        $types = "";
                        
                        if ($date_filter) {
                            $where_clauses[] = "a.date = ?";
                            $params[] = $date_filter;
                            $types .= "s";
                        }
                        
                        if ($teacher_filter) {
                            $where_clauses[] = "a.teacher_id = ?";
                            $params[] = $teacher_filter;
                            $types .= "s";
                        }
                        
                        if (!empty($where_clauses)) {
                            $query .= " WHERE " . implode(" AND ", $where_clauses);
                        }
                        
                        // Urutkan berdasarkan timestamp terbaru
                        $query .= " ORDER BY a.timestamp DESC LIMIT 100";
                        
                        // Prepare dan execute query
                        $stmt = $conn->prepare($query);
                        
                        if (!empty($params)) {
                            $stmt->bind_param($types, ...$params);
                        }
                        
                        $stmt->execute();
                        $result = $stmt->get_result();
                        
                        if ($result->num_rows === 0) {
                            echo "<tr><td colspan='8' class='py-4 px-4 text-center text-gray-500'>Tidak ada data absensi</td></tr>";
                        } else {
                            while ($row = $result->fetch_assoc()) {
                                $date = new DateTime($row['date']);
                                $formatted_date = $date->format('d/m/Y');
                                
                                echo "<tr class='border-b hover:bg-gray-50'>";
                                echo "<td class='py-3 px-4'><img src='{$row['photo_url']}' alt='{$row['name']}' class='w-12 h-12 rounded-full object-cover'></td>";
                                echo "<td class='py-3 px-4'>{$row['teacher_id']}</td>";
                                echo "<td class='py-3 px-4'>{$row['name']}</td>";
                                echo "<td class='py-3 px-4'>{$row['status']}</td>";
                                echo "<td class='py-3 px-4'>{$row['gender']}</td>";
                                echo "<td class='py-3 px-4'>{$formatted_date}</td>";
                                echo "<td class='py-3 px-4'>{$row['time']}</td>";
                                echo "<td class='py-3 px-4'>";
                                echo "<button class='bg-red-500 text-white py-1 px-2 rounded hover:bg-red-600' onclick='deleteAttendance({$row['attendance_id']})'>Hapus</button>";
                                echo "</td>";
                                echo "</tr>";
                            }
                        }
                        
                        // Tutup koneksi
                        $conn->close();
                        ?>
                    </tbody>
                </table>
            </div>
            
            <div class="mt-4 flex justify-end">
                <a href="export_csv.php" class="bg-green-600 text-white py-2 px-4 rounded-lg hover:bg-green-700 transition duration-300">
                    Export CSV
                </a>
            </div>
        </div>
    </div>
    
    <script>
        // Fungsi untuk menghapus data absensi
        function deleteAttendance(id) {
            if (confirm('Apakah Anda yakin ingin menghapus data absensi ini?')) {
                window.location.href = `delete_attendance.php?id=${id}`;
            }
        }
    </script>
</body>
</html>
                    

Langkah 8: Membuat File untuk Menghapus Data

Buat file delete_attendance.php untuk menghapus data absensi:

<?php
// delete_attendance.php - File untuk menghapus data absensi

require_once 'config.php';

// Cek apakah ID absensi diberikan
if (!isset($_GET['id']) || empty($_GET['id'])) {
    header('Location: admin.php?error=id_required');
    exit;
}

$attendance_id = (int)$_GET['id'];

// Hapus data absensi
$stmt = $conn->prepare("DELETE FROM attendance WHERE attendance_id = ?");
$stmt->bind_param("i", $attendance_id);

if ($stmt->execute()) {
    header('Location: admin.php?success=deleted');
} else {
    header('Location: admin.php?error=delete_failed');
}

// Tutup koneksi
$conn->close();
?>
                    

Langkah 9: Membuat File untuk Export CSV

Buat file export_csv.php untuk mengekspor data absensi ke CSV:

<?php
// export_csv.php - File untuk mengekspor data absensi ke CSV

require_once 'config.php';

// Set header untuk download file CSV
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=absensi_guru_' . date('Y-m-d') . '.csv');

// Buat file pointer untuk output
$output = fopen('php://output', 'w');

// Tambahkan BOM untuk UTF-8
fprintf($output, chr(0xEF).chr(0xBB).chr(0xBF));

// Tambahkan header CSV
fputcsv($output, ['ID', 'Nama', 'Status', 'Gender', 'Tanggal', 'Waktu Absen']);

// Ambil parameter filter
$date_filter = isset($_GET['date']) ? $_GET['date'] : null;
$teacher_filter = isset($_GET['teacher_id']) ? $_GET['teacher_id'] : null;

// Buat query dasar
$query = "SELECT a.teacher_id, t.name, t.status, t.gender, a.date, a.time 
          FROM attendance a 
          JOIN teachers t ON a.teacher_id = t.id";

// Tambahkan filter jika ada
$where_clauses = [];
$params = [];
$types = "";

if ($date_filter) {
    $where_clauses[] = "a.date = ?";
    $params[] = $date_filter;
    $types .= "s";
}

if ($teacher_filter) {
    $where_clauses[] = "a.teacher_id = ?";
    $params[] = $teacher_filter;
    $types .= "s";
}

if (!empty($where_clauses)) {
    $query .= " WHERE " . implode(" AND ", $where_clauses);
}

// Urutkan berdasarkan tanggal dan waktu
$query .= " ORDER BY a.date DESC, a.time DESC";

// Prepare dan execute query
$stmt = $conn->prepare($query);

if (!empty($params)) {
    $stmt->bind_param($types, ...$params);
}

$stmt->execute();
$result = $stmt->get_result();

// Output data ke CSV
while ($row = $result->fetch_assoc()) {
    $date = new DateTime($row['date']);
    $formatted_date = $date->format('d/m/Y');
    
    fputcsv($output, [
        $row['teacher_id'],
        $row['name'],
        $row['status'],
        $row['gender'],
        $formatted_date,
        $row['time']
    ]);
}

// Tutup koneksi
$conn->close();
?>
                    