<?php
include 'koneksi.php';

// Menangani input pencarian tahun dan bulan
$tahun = isset($_POST['tahun']) ? $_POST['tahun'] : date('Y');
$bulan = isset($_POST['bulan']) ? $_POST['bulan'] : date('m');

// Query untuk menghitung total anggota
$query_total_anggota = "SELECT COUNT(*) AS total_anggota FROM anggota"; // Asumsikan tabel anggota ada
$result_total_anggota = mysqli_query($conn, $query_total_anggota);
$total_anggota = mysqli_fetch_assoc($result_total_anggota)['total_anggota'] ?? 0;

// Query untuk menghitung total iuran
$query_total_iuran = "SELECT SUM(nominal) AS total_iuran FROM iuran WHERE YEAR(tanggal_iuran) = '$tahun' AND MONTH(tanggal_iuran) = '$bulan'";
$result_total_iuran = mysqli_query($conn, $query_total_iuran);
$total_iuran = mysqli_fetch_assoc($result_total_iuran)['total_iuran'];

// Query untuk menghitung total denda
$query_total_denda = "SELECT SUM(nominal) AS total_denda FROM denda WHERE YEAR(tanggal_bayar) = '$tahun' AND MONTH(tanggal_bayar) = '$bulan'";
$result_total_denda = mysqli_query($conn, $query_total_denda);
$total_denda = mysqli_fetch_assoc($result_total_denda)['total_denda'];

// Query untuk menghitung total bunga
$query_total_bunga = "SELECT SUM(nominal) AS total_bunga FROM bunga WHERE YEAR(tanggal_bayar) = '$tahun' AND MONTH(tanggal_bayar) = '$bulan'";
$result_total_bunga = mysqli_query($conn, $query_total_bunga);
$total_bunga = mysqli_fetch_assoc($result_total_bunga)['total_bunga'];

// Query untuk menghitung total pinjaman
$query_total_pinjaman = "SELECT SUM(nominal) AS total_pinjaman FROM pinjaman WHERE YEAR(tanggal_pinjam) = '$tahun' AND MONTH(tanggal_pinjam) = '$bulan'";
$result_total_pinjaman = mysqli_query($conn, $query_total_pinjaman);
$total_pinjaman = mysqli_fetch_assoc($result_total_pinjaman)['total_pinjaman'];

// Menyusun nama bulan yang dipilih
$bulan_nama = date('F', mktime(0, 0, 0, $bulan, 1));  // Mengubah angka bulan menjadi nama bulan

// Total seluruh uang kas (tanpa filter tahun/bulan)
$query_total_iuran_all = "SELECT SUM(nominal) AS total FROM iuran";
$result_total_iuran_all = mysqli_query($conn, $query_total_iuran_all);
$total_iuran_all = mysqli_fetch_assoc($result_total_iuran_all)['total'] ?? 0;

$query_total_denda_all = "SELECT SUM(nominal) AS total FROM denda";
$result_total_denda_all = mysqli_query($conn, $query_total_denda_all);
$total_denda_all = mysqli_fetch_assoc($result_total_denda_all)['total'] ?? 0;

$query_total_bunga_all = "SELECT SUM(nominal) AS total FROM bunga";
$result_total_bunga_all = mysqli_query($conn, $query_total_bunga_all);
$total_bunga_all = mysqli_fetch_assoc($result_total_bunga_all)['total'] ?? 0;

$query_total_pengeluaran_all = "SELECT SUM(nominal) AS total FROM pengeluaran";
$result_total_pengeluaran_all = mysqli_query($conn, $query_total_pengeluaran_all);
$total_pengeluaran_all = mysqli_fetch_assoc($result_total_pengeluaran_all)['total'] ?? 0;

// Query untuk menghitung total pinjaman yang statusnya "Belum Lunas"
$query_total_pinjamanall = "SELECT SUM(nominal) AS total_pinjaman FROM pinjaman WHERE status = 'Belum Lunas'";
$result_total_pinjamanall = mysqli_query($conn, $query_total_pinjamanall);
$total_pinjamanall = mysqli_fetch_assoc($result_total_pinjamanall)['total_pinjaman'];

// Total uang kas keseluruhan setelah dikurangi pengeluaran
$total_uang_kas_all = $total_iuran_all + $total_denda_all + $total_bunga_all - $total_pengeluaran_all;


// Query untuk menghitung total iuran tanpa filter bulan/tahun
$query_total_iuran_all = "SELECT SUM(nominal) AS total FROM iuran";
$result_total_iuran_all = mysqli_query($conn, $query_total_iuran_all);
$total_iuran_all = mysqli_fetch_assoc($result_total_iuran_all)['total'] ?? 0;


$total_tutup_buku = 0;
$periode_tutup_buku = '';

if (isset($_POST['tutup_buku'])) {
    $bulan_awal = $_POST['bulan_awal'];
    $bulan_akhir = $_POST['bulan_akhir'];

    $tanggal_awal = $bulan_awal . '-01';
    $tanggal_akhir = date('Y-m-t', strtotime($bulan_akhir . '-01'));

    $query_total = "
        SELECT 
            (SELECT COALESCE(SUM(nominal), 0) FROM iuran 
             WHERE tanggal_iuran BETWEEN '$tanggal_awal' AND '$tanggal_akhir') +
            (SELECT COALESCE(SUM(nominal), 0) FROM denda 
             WHERE tanggal_bayar BETWEEN '$tanggal_awal' AND '$tanggal_akhir') +
            (SELECT COALESCE(SUM(nominal), 0) FROM bunga 
             WHERE tanggal_bayar BETWEEN '$tanggal_awal' AND '$tanggal_akhir') 
        AS total_tutup_buku";

    $result = mysqli_query($conn, $query_total);
    $total_tutup_buku = mysqli_fetch_assoc($result)['total_tutup_buku'] ?? 0;

    // Simpan ke database jika belum ada periode ini
    $check = mysqli_query($conn, "SELECT * FROM tutup_buku WHERE tanggal_awal = '$tanggal_awal' AND tanggal_akhir = '$tanggal_akhir'");
    if (mysqli_num_rows($check) == 0) {
        mysqli_query($conn, "INSERT INTO tutup_buku (tanggal_awal, tanggal_akhir, total_nominal)
                             VALUES ('$tanggal_awal', '$tanggal_akhir', '$total_tutup_buku')");
    }
}



// Menghitung total sisa uang iuran yang belum dipinjam
$total_sisa_iuran = $total_uang_kas_all - $total_pinjamanall;

$query = "
    SELECT COALESCE(SUM(nominal), 0) AS total_nominal
    FROM iuran
    WHERE MONTH(date_created) = ? AND YEAR(date_created) = ?
";

$query_all_tutup = mysqli_query($conn, "SELECT * FROM tutup_buku ORDER BY tanggal_awal DESC");
$stmt = $conn->prepare($query);
$stmt->bind_param("ii", $bulan, $tahun);
$stmt->execute();
$stmt->bind_result($total_iuran_filtered);
$stmt->fetch();
$stmt->close();
$conn->close();
?>

<main class="main-content position-relative border-radius-lg ">
    <div class="container-fluid py-4">
        <h5 class="page-title">
            <span class="text-white">Dashboard Total</span>
        </h5>
        <div class="row mt-4">
            <!-- Total Anggota -->
            <div class="col-xl-3 col-sm-6 mb-4">
                <div class="card shadow-sm h-100">
                    <div class="card-body d-flex justify-content-between align-items-center">
                        <div>
                            <p class="text-sm text-uppercase text-muted font-weight-bold mb-1">Total Anggota</p>
                            <h5 class="mb-0"><?php echo number_format($total_anggota, 0, ',', '.'); ?></h5>
                        </div>
                        <div class="icon icon-shape bg-gradient-primary text-white rounded-circle text-center shadow" style="width: 45px; height: 45px;">
                            <i class="fas fa-users text-lg" aria-hidden="true"></i>
                        </div>
                    </div>
                </div>
            </div>

            <!-- Total Uang Kas -->
            <div class="col-xl-3 col-sm-6 mb-4">
                <div class="card shadow-sm h-100">
                    <div class="card-body d-flex justify-content-between align-items-center">
                        <div>
                            <p class="text-sm text-uppercase text-muted font-weight-bold mb-1">Total Uang Kas</p>
                            <h5 class="mb-0"><?php echo number_format($total_uang_kas_all, 0, ',', '.'); ?> IDR</h5>
                        </div>
                        <div class="icon icon-shape bg-gradient-danger text-white rounded-circle text-center shadow" style="width: 45px; height: 45px;">
                            <i class="fas fa-wallet text-lg" aria-hidden="true"></i>
                        </div>
                    </div>
                </div>
            </div>

            <!-- Total Pinjaman -->
            <div class="col-xl-3 col-sm-6 mb-4">
                <div class="card shadow-sm h-100">
                    <div class="card-body d-flex justify-content-between align-items-center">
                        <div>
                            <p class="text-sm text-uppercase text-muted font-weight-bold mb-1">Total Pinjaman</p>
                            <h5 class="mb-0"><?php echo number_format($total_pinjamanall, 0, ',', '.'); ?> IDR</h5>
                        </div>
                        <div class="icon icon-shape bg-gradient-success text-white rounded-circle text-center shadow" style="width: 45px; height: 45px;">
                            <i class="fas fa-hand-holding-usd text-lg" aria-hidden="true"></i>
                        </div>
                    </div>
                </div>
            </div>
            <!-- Total Pengeluaran -->
            <div class="col-xl-3 col-sm-6 mb-4">
                <div class="card shadow-sm h-100">
                    <div class="card-body d-flex justify-content-between align-items-center">
                        <div>
                            <p class="text-sm text-uppercase text-muted font-weight-bold mb-1">Total Pengeluaran</p>
                            <h5 class="mb-0"><?php echo number_format($total_pengeluaran_all, 0, ',', '.'); ?> IDR</h5>
                        </div>
                        <div class="icon icon-shape bg-gradient-danger text-white rounded-circle text-center shadow" style="width: 45px; height: 45px;">
                            <i class="fas fa-money-bill-wave text-lg" aria-hidden="true"></i>
                        </div>
                    </div>
                </div>
            </div>

            <!-- Sisa di Bendahara -->
            <div class="col-xl-3 col-sm-6 mb-4">
                <div class="card shadow-sm h-100">
                    <div class="card-body d-flex justify-content-between align-items-center">
                        <div>
                            <p class="text-sm text-uppercase text-muted font-weight-bold mb-1">Sisa di Bendahara</p>
                            <h5 class="mb-0"><?php echo number_format($total_sisa_iuran, 0, ',', '.'); ?> IDR</h5>
                        </div>
                        <div class="icon icon-shape bg-gradient-warning text-white rounded-circle text-center shadow" style="width: 45px; height: 45px;">
                            <i class="fas fa-cash-register text-lg" aria-hidden="true"></i>
                        </div>
                    </div>
                </div>
            </div>
        </div>
        <form method="POST">
            <div class="row">
                <div class="col-md-4">
                    <label style="color: primary;">Bulan Awal</label>
                    <input type="month" name="bulan_awal" class="form-control" required
                        value="<?= $_POST['bulan_awal'] ?? '' ?>">
                </div>
                <div class="col-md-4">
                    <label style="color: primary;">Bulan Akhir</label>
                    <input type="month" name="bulan_akhir" class="form-control" required
                        value="<?= $_POST['bulan_akhir'] ?? '' ?>">
                </div>
                <div class="col-md-4">
                    <label>&nbsp;</label>
                    <button type="submit" name="tutup_buku" class="btn btn-primary form-control">Tutup Buku</button>
                </div>
            </div>
        </form>
        <div class="row mt-4">
            <?php while ($row = mysqli_fetch_assoc($query_all_tutup)): ?>
                <div class="col-xl-3 col-sm-6 mb-4">
                    <div class="card shadow-sm h-100">
                        <div class="card-body d-flex flex-column justify-content-center text-center">
                            <div class="icon icon-shape bg-gradient-secondary shadow-secondary text-center rounded-circle mx-auto mb-3" style="width: 50px; height: 50px;">
                                <i class="ni ni-calendar-grid-58 text-white text-lg" aria-hidden="true"></i>
                            </div>
                            <h6 class="text-uppercase font-weight-bold mb-1 text-muted">Tutup Buku</h6>
                            <small class="text-secondary">
                                Periode:<br>
                                <?= date('F Y', strtotime($row['tanggal_awal'])) ?> – <?= date('F Y', strtotime($row['tanggal_akhir'])) ?>
                            </small>
                            <h5 class="mt-2 text-dark font-weight-bold">
                                <?= number_format($row['total_nominal'], 0, ',', '.'); ?> IDR
                            </h5>
                        </div>
                    </div>
                </div>
            <?php endwhile; ?>
        </div>

</main>