<?php
session_start();
include 'koneksi.php';
require './kalenderhijriah.php';

// Initialize message variables
$message = '';
$alert_type = '';

// Proses simpan data ke tabel message
if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_POST['proses_data'])) {
    $dataTable = mysqli_real_escape_string($koneksi, $_POST['dataTable']);
    $statusBayar = mysqli_real_escape_string($koneksi, $_POST['statusBayar']);
    $bulaN = mysqli_real_escape_string($koneksi, date('m', strtotime($_POST['par2'])));
    $tahuN = mysqli_real_escape_string($koneksi, date('Y', strtotime($_POST['par2'])));
    
    // Validasi input yang diperlukan
    if (empty($dataTable) || empty($statusBayar) || empty($tahuN)) {
        $message = "❌ Data input tidak lengkap.";
        $alert_type = "danger";
    } else {
        // Build where condition sama seperti query utama
        $where = " WHERE 1=1 ";
        if ($statusBayar == "lunas") {
            $where .= " AND DATE_FORMAT(a.tglmasehi, '%Y-%m') <= CONCAT('$tahuN', '-', '$bulaN') 
                       GROUP BY a.rekGab
                       HAVING SUM(a.bayar) >= SUM(a.kewajiban)";
        } elseif ($statusBayar == "bayar") {
            $where .= " AND DATE_FORMAT(a.tglmasehi, '%Y-%m') <= CONCAT('$tahuN', '-', '$bulaN')
                       GROUP BY a.rekGab
                       HAVING SUM(a.bayar) > 0 AND SUM(a.bayar) < SUM(a.kewajiban)";
        } else { // belum bayar
            $where .= " AND DATE_FORMAT(a.tglmasehi, '%Y-%m') <= CONCAT('$tahuN', '-', '$bulaN') 
                       GROUP BY a.rekGab
                       HAVING SUM(a.bayar) = 0";
        }
        
        // Query untuk mengambil data yang akan diproses
        $queryProses = "SELECT a.id AS id, a.nim AS nim, b.nama AS nama, a.tglmasehi AS tglmasehi, 
                MONTH(a.tglmasehi) AS month1, YEAR(a.tglmasehi) AS year1, a.no_kls AS no_kls, 
                SUM(a.kewajiban) AS kewajiban, SUM(a.bayar) AS bayar, 
                SUM(a.bayar - a.kewajiban) AS sisa, a.keterangan AS keterangan, a.username AS username,
                b.nohp AS nohp
                FROM $dataTable a 
                LEFT JOIN registrasi b ON a.id = b.id 
                $where";
                
                
        
        // Debug query jika diperlukan
        // error_log("Query Proses: " . $queryProses);
        
        $resultProses = mysqli_query($koneksi, $queryProses);
        
        if (!$resultProses) {
            $message = "❌ Error dalam query data: " . mysqli_error($koneksi);
            $alert_type = "danger";
        } elseif (mysqli_num_rows($resultProses) > 0) {
            $berhasil = 0;
            $gagal = 0;
            $error_messages = [];
            
            // Begin transaction untuk memastikan konsistensi data
            mysqli_autocommit($koneksi, FALSE);
            
            while ($row = mysqli_fetch_assoc($resultProses)) {
                // Validasi dan sanitasi data dengan nilai default
                $nohp = mysqli_real_escape_string($koneksi, 
                    !empty($row['nohp']) ? $row['nohp'] : '0000000000');
                $tglmasehi2 = $tglmasehi2 = $hijri[2]. '-' . $calendar->monthName($hijri[0]). '-' . sprintf('%02d', $hijri[1]);
                $kls = mysqli_real_escape_string($koneksi, 
                    !empty($row['no_kls']) ? $row['no_kls'] : 'N/A');
                $penErima = mysqli_real_escape_string($koneksi, 
                    !empty($row['nama']) ? $row['nama'] : 'Unknown');
                $kantor = 'System';
                $nim = mysqli_real_escape_string($koneksi, 
                    !empty($row['nim']) ? $row['nim'] : 'N/A');
                $judul = "MMU A-39";
                $tagihan = "*TAGIHAN ". strtoupper($dataTable)."*";
                // Konversi dan validasi angka
                $kewajiban = is_numeric($row['kewajiban']) ? (float)$row['kewajiban'] : 0.0;
                $sisaKewajiban = is_numeric($row['sisa']) ? (float)$row['sisa'] : 0.0;
                $bayar = is_numeric($row['bayar']) ? (float)$row['bayar'] : 0.0;
                
                $keteranganpesan = mysqli_real_escape_string($koneksi, 
                    "Laporan " . ucfirst($dataTable) . " - " . 
                    (!empty($row['keterangan']) ? $row['keterangan'] : 'No Description'));
                
                // Cek apakah data sudah ada untuk mencegah duplikasi
                 /* $checkDuplicate = "SELECT COUNT(*) as count FROM message_tes 
                                  WHERE rekening = '$nim' 
                                  AND penErima = '$penErima' 
                                  AND keterangan = '$keteranganpesan'
                                  AND DATE(tgl) = CURDATE()";
                
                $duplicateResult = mysqli_query($koneksi, $checkDuplicate);
                $duplicateCount = mysqli_fetch_assoc($duplicateResult)['count'];
                
                if ($duplicateCount > 0) {
                    $gagal++;
                    $error_messages[] = "Data untuk NIM $nim sudah ada hari ini";
                    continue;
                } 
                 */
                // Insert ke tabel message dengan prepared statement untuk keamanan
                $insertQuery = "INSERT INTO message
                               (number, tgl, judul, penErima, PenGirim, rekening, kewajiban, sisaKewajiban, nominal, keterangan, metodeBayar, kodeFilTer) 
                               VALUES 
                               (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, '$tagihan', 'NonSyahriah')";
                
                $stmt = mysqli_prepare($koneksi, $insertQuery);
                
                if ($stmt) {
                    mysqli_stmt_bind_param($stmt, "ssssssddds", 
                        $nohp, $tglmasehi2, $judul, $penErima, $kls, 
                        $nim, $kewajiban, $bayar,$sisaKewajiban, $keteranganpesan);
                    
                    $waTagihan = mysqli_stmt_execute($stmt);
                    
                    if ($waTagihan) {
                        $berhasil++;
                    } else {
                        $gagal++;
                        $error_messages[] = "Error insert untuk NIM $nim: " . mysqli_stmt_error($stmt);
                    }
                    
                    mysqli_stmt_close($stmt);
                } else {
                    $gagal++;
                    $error_messages[] = "Error prepare statement untuk NIM $nim: " . mysqli_error($koneksi);
                }
            }
            
            // Commit atau rollback transaksi
            if ($berhasil > 0 && $gagal == 0) {
                mysqli_commit($koneksi);
                $message = "✅ Berhasil memproses $berhasil data ke tabel message.";
                $alert_type = "success";
            } elseif ($berhasil > 0 && $gagal > 0) {
                mysqli_commit($koneksi);
                $message = "⚠️ Berhasil memproses $berhasil data, $gagal data gagal diproses.";
                if (!empty($error_messages)) {
                    $message .= "<br>Detail error: " . implode(", ", array_slice($error_messages, 0, 3));
                    if (count($error_messages) > 3) {
                        $message .= " dan " . (count($error_messages) - 3) . " error lainnya.";
                    }
                }
                $alert_type = "warning";
            } else {
                mysqli_rollback($koneksi);
                $message = "❌ Gagal memproses semua data ke tabel message.";
                if (!empty($error_messages)) {
                    $message .= "<br>Detail error: " . implode(", ", array_slice($error_messages, 0, 3));
                }
                $alert_type = "danger";
            }
            
            // Restore autocommit
            mysqli_autocommit($koneksi, TRUE);
            
        } else {
            $message = "⚠️ Tidak ada data yang ditemukan untuk diproses dengan kriteria yang diberikan.";
            $alert_type = "warning";
        }
    }
}
?>


<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Laporan Pembayaran</title>
    
    <!-- Bootstrap CSS -->
    <!-- <link href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap/5.3.0/css/bootstrap.min.css" rel="stylesheet"> -->
    <!-- Font Awesome -->
    <!-- <link href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css" rel="stylesheet"> -->
    <!-- DataTables CSS -->
    <!-- <link href="https://cdnjs.cloudflare.com/ajax/libs/datatables/1.10.21/css/dataTables.bootstrap5.min.css" rel="stylesheet"> -->
    
    <style>
        :root {
            --primary-gradient: linear-gradient(135deg,rgb(97, 119, 218) 0%,rgb(85, 106, 182) 100%);
            --success-gradient: linear-gradient(135deg, #11998e 0%, #38ef7d 100%);
            --danger-gradient: linear-gradient(135deg, #fc466b 0%, #3f5efb 100%);
            --warning-gradient: linear-gradient(135deg, #FDBB2D 0%, #22C1C3 100%);
            --card-shadow: 0 10px 30px rgba(0,0,0,0.1);
            --hover-shadow: 0 15px 40px rgba(0,0,0,0.2);
        }

        body {
            background: linear-gradient(135deg, #f5f7fa 0%, #c3cfe2 100%);
            font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
            min-height: 100vh;
        }

        .main-container {
            padding: 2rem 1rem;
        }

        .page-header {
            background: var(--primary-gradient);
            color: white;
            padding: 1rem;
            border-radius: 15px;
            margin-bottom: 2rem;
            box-shadow: var(--card-shadow);
            position: relative;
            overflow: hidden;
        }

        .page-header::before {
            content: '';
            position: absolute;
            top: 0;
            right: 0;
            width: 200px;
            height: 200px;
            background: rgba(255,255,255,0.1);
            border-radius: 50%;
            transform: translate(50px, -50px);
        }

        .page-header h1 {
            font-size: 2rem;
            font-weight: 700;
            margin: 0;
            text-shadow: 2px 2px 4px rgba(0,0,0,0.3);
        }

        .card {
            border: none;
            border-radius: 15px;
            box-shadow: var(--card-shadow);
            transition: all 0.3s ease;
            overflow: hidden;
        }

        .card:hover {
            box-shadow: var(--hover-shadow);
            transform: translateY(-5px);
        }

        .card-header {
            background: var(--primary-gradient);
            color: white;
            border: none;
            padding: 1.5rem;
        }

        .card-title {
            font-size: 1.5rem;
            font-weight: 600;
            margin: 0;
        }

        .form-section {
            background: white;
            padding: 2rem;
            border-radius: 15px;
            box-shadow: var(--card-shadow);
            margin-bottom: 2rem;
        }

        .form-control, .form-select {
            border: 2px solid #e9ecef;
            border-radius: 10px;
            padding: 0.75rem 1rem;
            transition: all 0.3s ease;
            font-size: 0.95rem;
        }

        .form-control:focus, .form-select:focus {
            border-color: #667eea;
            box-shadow: 0 0 0 0.2rem rgba(102, 126, 234, 0.25);
            transform: translateY(-2px);
        }

        .btn {
            border-radius: 10px;
            padding: 0.75rem 1.5rem;
            font-weight: 600;
            transition: all 0.3s ease;
            border: none;
            position: relative;
            overflow: hidden;
        }

        .btn::before {
            content: '';
            position: absolute;
            top: 0;
            left: -100%;
            width: 100%;
            height: 100%;
            background: linear-gradient(90deg, transparent, rgba(255,255,255,0.3), transparent);
            transition: left 0.5s;
        }

        .btn:hover::before {
            left: 100%;
        }

        .btn-search {
            background: var(--primary-gradient);
            color: white;
            min-width: 120px;
        }

        .btn-search:hover {
            transform: translateY(-3px);
            box-shadow: 0 8px 25px rgba(102, 126, 234, 0.4);
            color: white;
        }

        .btn-process {
            background: var(--success-gradient);
            color: white;
            min-width: 200px;
        }

        .btn-process:hover {
            transform: translateY(-3px);
            box-shadow: 0 8px 25px rgba(17, 153, 142, 0.4);
            color: white;
        }

        .btn-process:disabled {
            opacity: 0.7;
            transform: none;
        }

        .export-section {
            display: flex;
            gap: 1rem;
            align-items: center;
            margin: 1rem 0;
        }

        .export-btn {
            display: inline-flex;
            align-items: center;
            justify-content: center;
            width: 50px;
            height: 50px;
            border-radius: 12px;
            transition: all 0.3s ease;
            text-decoration: none;
            box-shadow: 0 4px 15px rgba(0,0,0,0.1);
        }

        .export-btn:hover {
            transform: translateY(-5px) scale(1.1);
            box-shadow: 0 8px 25px rgba(0,0,0,0.2);
        }

        .export-btn.excel {
            background: linear-gradient(135deg, #1D976C 0%, #93F9B9 100%);
        }

        .export-btn.pdf {
            background: linear-gradient(135deg, #DA4453 0%, #89216B 100%);
        }

        .table-container {
            background: white;
            border-radius: 15px;
            padding: 1.5rem;
            box-shadow: var(--card-shadow);
            overflow: hidden;
        }

        .table {
            margin: 0;
        }

        .table thead th {
            background: var(--primary-gradient);
            color: white;
            border: none;
            padding: 1rem;
            font-weight: 600;
            text-align: center;
            position: sticky;
            top: 0;
            z-index: 10;
        }

        .table tbody tr {
            transition: all 0.3s ease;
        }

        .table tbody tr:hover {
            background: linear-gradient(135deg, #f8f9ff 0%, #e8f4ff 100%);
            transform: scale(1.01);
        }

        .table td {
            padding: 1rem;
            vertical-align: middle;
            border-color: #e9ecef;
        }

        .total-row {
            background: var(--primary-gradient);
            color: white;
            font-weight: 700;
        }

        .total-row td {
            border-color: rgba(255,255,255,0.2);
        }

        .loading-spinner {
            width: 20px;
            height: 20px;
            border: 3px solid #f3f3f3;
            border-top: 3px solid #667eea;
            border-radius: 50%;
            animation: spin 1s linear infinite;
            margin-left: 10px;
            display: none;
        }

        @keyframes spin {
            0% { transform: rotate(0deg); }
            100% { transform: rotate(360deg); }
        }

        .alert {
            border: none;
            border-radius: 12px;
            padding: 1rem 1.5rem;
            margin-bottom: 2rem;
            box-shadow: 0 4px 20px rgba(0,0,0,0.1);
        }

        .alert-success {
            background: var(--success-gradient);
            color: white;
        }

        .alert-danger {
            background: var(--danger-gradient);
            color: white;
        }

        .alert-warning {
            background: var(--warning-gradient);
            color: white;
        }

        .datepicker-container {
            position: relative;
            display: inline-block;
        }

        .datepicker-input {
            padding-right: 40px;
        }

        .datepicker-icon {
            position: absolute;
            right: 10px;
            top: 50%;
            transform: translateY(-50%);
            cursor: pointer;
            color: #667eea;
            font-size: 1.2rem;
        }

        .filter-section {
            display: flex;
            gap: 1rem;
            align-items: end;
            flex-wrap: wrap;
            margin-bottom: 1.5rem;
        }

        .filter-group {
            flex: 1;
            min-width: 150px;
        }

        .filter-label {
            font-weight: 600;
            color: #495057;
            margin-bottom: 0.5rem;
            display: block;
        }

        .stats-container {
            display: flex;
            gap: 1rem;
            margin-bottom: 2rem;
            flex-wrap: wrap;
        }

        .stat-card {
            flex: 1;
            min-width: 200px;
            background: white;
            padding: 1.5rem;
            border-radius: 12px;
            box-shadow: var(--card-shadow);
            text-align: center;
        }

        .stat-value {
            font-size: 2rem;
            font-weight: 700;
            margin-bottom: 0.5rem;
        }

        .stat-label {
            color: #6c757d;
            font-size: 0.9rem;
        }

        .datepicker {
            position: absolute;
            z-index: 1000;
            background: white;
            border-radius: 10px;
            box-shadow: 0 10px 40px rgba(0,0,0,0.2);
        }

        @media (max-width: 768px) {
            .filter-section {
                flex-direction: column;
            }
            
            .filter-group {
                width: 100%;
            }
            
            .stats-container {
                flex-direction: column;
            }
            
            .page-header h1 {
                font-size: 2rem;
            }
        }

        .fade-in {
            animation: fadeIn 0.5s ease-in;
        }

        @keyframes fadeIn {
            from { opacity: 0; transform: translateY(20px); }
            to { opacity: 1; transform: translateY(0); }
        }
    </style>
</head>
<body>
    <div class="main-container">
        <!-- Page Header -->
        <div class="page-header fade-in">
            <h1><i class="fas fa-chart-bar me-3"></i>Laporan Pembayaran</h1>
            <p class="mb-0 mt-2">Kelola dan pantau pembayaran dengan mudah</p>
        </div>

        <!-- Alert Message -->
        <?php if (!empty($message)): ?>
        <div class="alert alert-<?php echo $alert_type; ?> alert-dismissible fade show fade-in" role="alert">
            <?php echo $message; ?>
            <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>
        </div>
        <?php endif; ?>

        <!-- Filter Section -->
        <div class="form-section fade-in">
            <h5 class="mb-4"><i class="fas fa-filter me-2"></i>Filter Data</h5>
            
            <form role="form" id="quickForm" action="home.php?home=<?php echo md5("lapbayar_nonsyahriah");?>" method="POST">
                <div class="filter-section">
                    <div class="filter-group">
                        <label class="filter-label">Jenis Pembayaran</label>
                        <select class="form-select" name="dataTable" id="dataTable">
                            <option value="bulanan" <?php echo (isset($_POST['dataTable']) && $_POST['dataTable']=='bulanan') ? 'selected' : ''; ?>>Syahriah</option>
                            <option value="ikhtibar" <?php echo (isset($_POST['dataTable']) && $_POST['dataTable']=='ikhtibar') ? 'selected' : ''; ?>>Ikhtibar</option>
                            <option value="kitab" <?php echo (isset($_POST['dataTable']) && $_POST['dataTable']=='kitab') ? 'selected' : ''; ?>>Kitab</option>
                            <option value="imda1" <?php echo (isset($_POST['dataTable']) && $_POST['dataTable']=='imda1') ? 'selected' : ''; ?>>Imda-I</option>
                            <option value="imda2" <?php echo (isset($_POST['dataTable']) && $_POST['dataTable']=='imda2') ? 'selected' : ''; ?>>Imda-II</option>
                            <option value="imda3" <?php echo (isset($_POST['dataTable']) && $_POST['dataTable']=='imda3') ? 'selected' : ''; ?>>Imda-III</option>
                            <option value="seragam" <?php echo (isset($_POST['dataTable']) && $_POST['dataTable']=='seragam') ? 'selected' : ''; ?>>Seragam</option>
                        </select>
                    </div>

                    <div class="filter-group">
                        <label class="filter-label">Status Bayar</label>
                        <select class="form-select" name="statusBayar" id="statusBayar">
                            <option value="lunas" <?php echo (isset($_POST['statusBayar']) && $_POST['statusBayar']=='lunas') ? 'selected' : ''; ?>>Lunas</option>
                            <option value="bayar" <?php echo (isset($_POST['statusBayar']) && $_POST['statusBayar']=='bayar') ? 'selected' : ''; ?>>Bayar Sebagian</option>
                            <option value="tidak" <?php echo (isset($_POST['statusBayar']) && $_POST['statusBayar']=='tidak') ? 'selected' : ''; ?>>Belum Bayar</option>
                        </select>
                    </div>

                   <!--  <div class="filter-group">
                        <label class="filter-label">Tanggal Dari</label>
                        <div class="datepicker-container" id="datepicker1">
                            <input id="par1" name="par1" type="text" class="form-control datepicker-input" 
                                   value="<A?php if(isset($_POST['par1'])){ echo $_POST['par1']; }else{ echo sprintf('%02d', $hijri[1]) . '-' . $calendar->monthName($hijri[0]) . '-' . $hijri[2];} ?>"  />
                            <i class="fas fa-calendar datepicker-icon" onclick="pickADate1();"></i>
                            <div id="calendar-popup1" class="datepicker" style="display:none;"></div>
                        </div>
                    </div> -->

                    <div class="filter-group">
                        <label class="filter-label">Tanggal Sampai</label>
                        <div class="datepicker-container" id="datepicker2">
                            <input id="par2" name="par2" type="text" class="form-control datepicker-input" 
                                   value="<?php if(isset($_POST['par2'])){ echo $_POST['par2']; }else{ echo sprintf('%02d', $hijri[1]) . '-' . $calendar->monthName($hijri[0]) . '-' . $hijri[2];} ?>"  />
                            <i class="fas fa-calendar datepicker-icon" onclick="pickADate2();"></i>
                            <div id="calendar-popup2" class="datepicker" style="display:none;"></div>
                        </div>
                    </div>

                    <div class="filter-group">
                        <button type="submit" class="btn btn-search">
                            <i class="fas fa-search me-2"></i>Cari Data
                        </button>
                    </div>
                </div>
            </form>

            <!-- Process Button & Export -->
            <?php if(isset($_POST['dataTable']) && !isset($_POST['proses_data'])): ?>
            <div class="d-flex justify-content-between align-items-center mt-4">
                <form method="POST" action="home.php?home=<?php echo md5("lapbayar_nonsyahriah");?>" class="d-inline">
                    <input type="hidden" name="dataTable" value="<?php echo $_POST['dataTable']; ?>">
                    <input type="hidden" name="statusBayar" value="<?php echo $_POST['statusBayar']; ?>">
                    <input type="hidden" name="par1" value="<?php echo $_POST['par1']; ?>">
                    <input type="hidden" name="par2" value="<?php echo $_POST['par2']; ?>">
                    <button type="submit" name="proses_data" class="btn btn-process" id="prosesBtn">
                        <i class="fas fa-sign-out-alt me-2"></i>Kirim WA
                        <span class="loading-spinner" id="loadingSpinner"></span>
                    </button>
                </form>

                <?php if(isset($_POST['par2'])): ?>
                <?php
                $DeleteWA = mysqli_query($koneksi, "DELETE FROM message WHERE status='1' OR status='2' ") or die(mysqli_error($koneksi));
                ?>
                <div class="export-section">
                    <span class="me-3 fw-bold">Export:</span>
                    <a href="exportexcell/bayar_nonsyahriah_murid.php" target="_blank" class="export-btn excel" title="Download Excel">
                        <i class="fas fa-file-excel fa-lg text-white"></i>
                    </a>
                    <a href="pdf/lap_bayar_kewajiban_muridpdf.php" target="_blank" class="export-btn pdf" title="Download PDF">
                        <i class="fas fa-file-pdf fa-lg text-white"></i>
                    </a>
                </div>
                <?php endif; ?>
            </div>
            <?php endif; ?>
        </div>

        <?php
        /*  $queryb = mysqli_query($koneksi, $queryProses);
        while ($barisA = mysqli_fetch_assoc( $queryb)) {
                $t_kwajiban += $barisA['kewajiban'];
                $t_bayar += $barisA['bayar'];
                 $t_sisa += $barisA['sisa'];
        }  */
          
        ?>

        <!-- Statistics Cards -->
       <!--  <?php if(isset($_POST['dataTable']) && !isset($_POST['proses_data'])): ?>
        <div class="stats-container fade-in">
            <div class="stat-card">
                <div class="stat-value text-primary">
                    <?php echo isset($t_kwajiban) ? 'Rp'.number_format($t_kwajiban, 0, ',', '.') : '0'; ?>
                </div>
                <div class="stat-label">Total Kewajiban</div>
            </div>
            <div class="stat-card">
                <div class="stat-value text-success">
                    <?php echo isset($t_bayar) ? 'Rp'.number_format($t_bayar, 0, ',', '.') : '0'; ?>
                </div>
                <div class="stat-label">Total Bayar</div>
            </div>
            <div class="stat-card">
                <div class="stat-value text-warning">
                    <?php echo isset($t_sisa) ? 'Rp'.number_format($t_sisa, 0, ',', '.') : '0'; ?>
                </div>
                <div class="stat-label">Total Sisa</div>
            </div>
        </div>
       
        <?php endif; ?> -->

        <!-- Data Table -->
        <div class="table-container fade-in">
            <div class="table-responsive">
                <table id="sortTable" class="table table-striped">
                    <thead>
                        <tr>
                            <th>No</th>
                            <th>ID</th>
                            <th>NIM</th>
                            <th>Tanggal</th>
                            <th>Nama</th>
                            <th>Kewajiban</th>
                            <th>Bayar</th>
                            <th>Sisa</th>
                            <th>Kelas</th>
                            <th>Keterangan</th>
                            <th>Username</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php
                        $where = " WHERE 1=1 ";
                        $dataTable = '';
                        $statusBayar = '';
                        $par1 = '';
                        $par2 = '';

                        if ($_SERVER['REQUEST_METHOD'] == 'POST' && isset($_POST['dataTable']) && !isset($_POST['proses_data'])) {
                            $dataTable = $_POST['dataTable'];
                            $statusBayar = $_POST['statusBayar'];
                            $bulaN = mysqli_real_escape_string($koneksi, date('m', strtotime($_POST['par2'])));
                            $tahuN = mysqli_real_escape_string($koneksi, date('Y', strtotime($_POST['par2'])));

                            if ($statusBayar == "lunas") {
                                $where .= " AND DATE_FORMAT(a.tglmasehi, '%Y-%m') <= CONCAT('$tahuN', '-', '$bulaN') 
                       GROUP BY a.rekGab
                                  HAVING SUM(a.bayar) >= SUM(a.kewajiban)";
                            } elseif ($statusBayar == "bayar") {
                                $where .= " AND DATE_FORMAT(a.tglmasehi, '%Y-%m') <= CONCAT('$tahuN', '-', '$bulaN') 
                       GROUP BY a.rekGab
                                  HAVING SUM(a.bayar) > 0 AND SUM(a.bayar) < SUM(a.kewajiban)";
                            } else {
                                $where .= " AND DATE_FORMAT(a.tglmasehi, '%Y-%m') <= CONCAT('$tahuN', '-', '$bulaN') 
                       GROUP BY a.rekGab
                                  HAVING SUM(a.bayar) = 0";
                            }

                            if (!empty($dataTable)) {
                                $queryba = "SELECT a.id AS id, a.nim AS nim, b.nama AS nama, a.tglmasehi AS tglmasehi, 
                                    MONTH(a.tglmasehi) AS month1, YEAR(a.tglmasehi) AS year1, a.no_kls AS no_kls, 
                                    SUM(a.kewajiban) AS kewajiban, SUM(a.bayar) AS bayar, 
                                    SUM(a.bayar - a.kewajiban) AS sisa, a.keterangan AS keterangan, a.username AS username
                                    FROM $dataTable a 
                                    LEFT JOIN registrasi b ON a.id = b.id 
                                    $where";
                                
                                $querya = mysqli_query($koneksi, $queryba);
                                $_SESSION['bayar_nonsyahriah']['kode']['sqlbyr'] = $queryba;
                                $_SESSION['bayar_kewajiban']['periode'] = date('d-m-Y', strtotime($_POST['par1'])) . " S/d " . date('d-m-Y', strtotime($_POST['par2']));
                                $_SESSION['bayar_kewajiban']['namaKewajiban'] = $dataTable;

                                if ($querya) {
                                    $t_kwajiban = 0;
                                    $t_bayar = 0;
                                    $t_sisa = 0;
                                    $no = 0;

                                    if (mysqli_num_rows($querya) > 0) {
                                        while ($barisD = mysqli_fetch_assoc($querya)) {
                                            $t_kwajiban += $barisD['kewajiban'];
                                            $t_bayar += $barisD['bayar'];
                                            $t_sisa += $barisD['sisa'];
                                            $no++;
                                            ?>
                                            <tr>
                                                <td class="text-center"><?php echo $no; ?></td>
                                                <td><?php echo htmlspecialchars($barisD['id']); ?></td>
                                                <td><?php echo htmlspecialchars($barisD['nim']); ?></td>
                                                <td><?php echo date('d-m-Y', strtotime($barisD['tglmasehi'])); ?></td>
                                                <td><?php echo htmlspecialchars($barisD['nama']); ?></td>
                                                <td class="text-end"><?php echo number_format($barisD['kewajiban'], 0, ',', '.'); ?></td>
                                                <td class="text-end"><?php echo number_format($barisD['bayar'], 0, ',', '.'); ?></td>
                                                <td class="text-end"><?php echo number_format($barisD['sisa'], 0, ',', '.'); ?></td>
                                                <td><?php echo htmlspecialchars($barisD['no_kls']); ?></td>
                                                <td><?php echo htmlspecialchars($barisD['keterangan']); ?></td>
                                                <td><?php echo htmlspecialchars($barisD['username']); ?></td>
                                            </tr>
                                            <?php
                                        }
                                    } else {
                                        echo "<tr><td colspan='11' class='text-center py-4'>
                                                <i class='fas fa-search fa-3x text-muted mb-3'></i>
                                                <h5 class='text-muted'>Tidak ada data ditemukan</h5>
                                                <p class='text-muted'>Silakan ubah filter pencarian Anda</p>
                                              </td></tr>";
                                    }
                                } else {
                                    echo "<tr><td colspan='11' class='text-center text-danger py-4'>
                                            <i class='fas fa-exclamation-triangle fa-2x mb-3'></i>
                                            <h5>Error dalam mengeksekusi query</h5>
                                            <p>" . mysqli_error($koneksi) . "</p>
                                          </td></tr>";
                                }
                            }
                        } else {
                            echo "<tr><td colspan='11' class='text-center py-5'>
                                    <i class='fas fa-filter fa-3x text-muted mb-3'></i>
                                    <h5 class='text-muted'>Silakan pilih filter dan klik tombol Cari</h5>
                                    <p class='text-muted'>untuk menampilkan data pembayaran</p>
                                  </td></tr>";
                        }
                        ?>
                    </tbody>
                    <?php if(isset($t_kwajiban)): ?>
                    <tfoot>
                        <tr class="total-row">
                            <td colspan="5" class="text-center"><strong>TOTAL</strong></td>
                            <td class="text-end"><strong> <?php echo number_format($t_kwajiban, 0, ',', '.'); ?></strong></td>
                            <td class="text-end"><strong> <?php echo number_format($t_bayar, 0, ',', '.'); ?></strong></td>
                            <td class="text-end"><strong> <?php echo number_format($t_sisa, 0, ',', '.'); ?></strong></td>
                            <td colspan="3"></td>
                        </tr>
                    </tfoot>
                    <?php endif; ?>
                </table>
            </div>
        </div>
    </div>

    <!-- Scripts -->
    <!-- <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap/5.3.0/js/bootstrap.bundle.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/datatables/1.10.21/js/jquery.dataTables.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/datatables/1.10.21/js/dataTables.bootstrap5.min.js"></script> -->
    
    <!-- Calendar Scripts -->
    <link rel="stylesheet" href="calendar.css"/>
    <script type="text/javascript" src="hijri-date.js"></script>
    <script type="text/javascript" src="calendar.js"></script>

    <script>
        $(document).ready(function() {
            // Initialize DataTable with enhanced features
            $('#sortTable').DataTable({
                responsive: true,
                pageLength: 25,
                lengthMenu: [[10, 25, 50, 100, -1], [10, 25, 50, 100, "Semua"]],
                language: {
                    url: '//cdn.datatables.net/plug-ins/1.10.21/i18n/Indonesian.json'
                },
                dom: '<"row"<"col-sm-12 col-md-6"l><"col-sm-12 col-md-6"f>>' +
                     '<"row"<"col-sm-12"tr>>' +
                     '<"row"<"col-sm-12 col-md-5"i><"col-sm-12 col-md-7"p>>',
                order: [[0, 'asc']],
                columnDefs: [
                    { targets: [5, 6, 7], className: 'text-end' },
                    { targets: [0], className: 'text-center' }
                ]
            });

            // Process button functionality
            $('#prosesBtn').on('click', function(e) {
                e.preventDefault();
                
                Swal.fire({
                    title: 'Konfirmasi Proses Data',
                    text: 'Apakah Anda yakin ingin memproses semua data ke tabel message?',
                    icon: 'question',
                    showCancelButton: true,
                    confirmButtonColor: '#11998e',
                    cancelButtonColor: '#fc466b',
                    confirmButtonText: 'Ya, Proses!',
                    cancelButtonText: 'Batal',
                    reverseButtons: true
                }).then((result) => {
                    if (result.isConfirmed) {
                        showLoading(this);
                        $(this).closest('form').submit();
                    }
                });
            });

            // Show loading function
            function showLoading(button) {
                const spinner = $(button).find('.loading-spinner');
                spinner.show();
                $(button).prop('disabled', true);
                $(button).find('i').removeClass('fa-cogs').addClass('fa-spinner fa-spin');
            }

            // Enhanced hover effects
            $('.btn').hover(
                function() { $(this).addClass('shadow-lg'); },
                function() { $(this).removeClass('shadow-lg'); }
            );

            // Smooth scroll for alerts
            if ($('.alert').length) {
                $('html, body').animate({
                    scrollTop: $('.alert').offset().top - 100
                }, 500);
            }
        });

       
        // Form validation
        $('#quickForm').on('submit', function(e) {
            var dataTable = $('#dataTable').val();
            var statusBayar = $('#statusBayar').val();
            var par1 = $('#par1').val();
            var par2 = $('#par2').val();

            if (!dataTable || !statusBayar || !par1 || !par2) {
                e.preventDefault();
                Swal.fire({
                    icon: 'warning',
                    title: 'Data Tidak Lengkap',
                    text: 'Mohon lengkapi semua field filter sebelum mencari data.',
                    confirmButtonColor: '#667eea'
                });
                return false;
            }

            // Show loading on search button
            var searchBtn = $(this).find('button[type="submit"]');
            searchBtn.html('<i class="fas fa-spinner fa-spin me-2"></i>Mencari...');
            searchBtn.prop('disabled', true);
        });

          // Auto-hide alerts after 5 seconds
        setTimeout(function() {
            $('.alert').fadeOut('slow');
        }, 5000);

       /*  // Add SweetAlert2 for better alerts
        if (typeof Swal === 'undefined') {
            var script = document.createElement('script');
            script.src = 'https://cdnjs.cloudflare.com/ajax/libs/sweetalert2/11.7.20/sweetalert2.min.js';
            document.head.appendChild(script);
            
            var link = document.createElement('link');
            link.rel = 'stylesheet';
            link.href = 'https://cdnjs.cloudflare.com/ajax/libs/sweetalert2/11.7.20/sweetalert2.min.css';
            document.head.appendChild(link);
        } 
    </script>
    <script>
          // Calendar functionality
        var pickedTxt1 = document.getElementById('par1'),
            datepicker1 = new Calendar(true, 0, false, true);
        document.getElementById('datepicker1').appendChild(datepicker1.getElement());
        datepicker1.getElement().style.marginTop = '10px';
        
        datepicker1.callback = function() {
            var selectedDate = datepicker1.getDate();
            var day = selectedDate.getDate().toString().padStart(2, '0');
            var month = (selectedDate.getMonth() + 1).toString().padStart(2, '0');
            var year = selectedDate.getFullYear();
            pickedTxt1.value = day + '-' + month + '-' + year;
            pickedTxt1.selectionStart = 0;
            pickedTxt1.selectionEnd = pickedTxt1.value.length;
            pickedTxt1.focus();
            datepicker1.hide();
        };

        function pickADate1() {
            datepicker1.show();
            datepicker1.getElement().style.position = 'absolute';
        }

        var pickedTxt2 = document.getElementById('par2'),
            datepicker2 = new Calendar(true, 0, false, true);
        document.getElementById('datepicker2').appendChild(datepicker2.getElement());
        datepicker2.getElement().style.marginTop = '10px';
        
        datepicker2.callback = function() {
            var selectedDate = datepicker2.getDate();
            var day = selectedDate.getDate().toString().padStart(2, '0');
            var month = (selectedDate.getMonth() + 1).toString().padStart(2, '0');
            var year = selectedDate.getFullYear();
            pickedTxt2.value = day + '-' + month + '-' + year;
            pickedTxt2.selectionStart = 0;
            pickedTxt2.selectionEnd = pickedTxt2.value.length;
            pickedTxt2.focus();
            datepicker2.hide();
        };

        function pickADate2() {
            datepicker2.show();
            datepicker2.getElement().style.position = 'absolute';
        }

        // Click outside to close calendar
        document.addEventListener('click', function(event) {
            var isClickInsideCalendar1 = document.getElementById('datepicker1').contains(event.target);
            var isClickInsideCalendar2 = document.getElementById('datepicker2').contains(event.target);
            
            if (!isClickInsideCalendar1) {
                datepicker1.hide();
            }
            if (!isClickInsideCalendar2) {
                datepicker2.hide();
            }
        });

        </script>
</body>
</html>