<?php
error_reporting(0);
ini_set('display_errors', 0);

// Helper function for percentage calculation
function calculatePercentage($value1, $value2) {
    if ($value1 == 0 || $value2 == 0) {
        return "0.00";
    }
    return round(($value1 / $value2) * 100, 2);
}

// Helper function for fetching data
function fetchData($koneksi, $query) {
    $result = mysqli_query($koneksi, $query);
    return mysqli_fetch_array($result);
}

?>
<!-- Content Header -->
<div class="content-header">
    <div class="container-fluid">
        <div class="row mb-2">
            <div class="col-sm-12">
                <h1 class="m-0 text-dark"></h1>
            </div>
        </div>
    </div>
</div>

<!-- Main content -->
<section class="content">
    <div class="container-fluid">
        <div class="row">
            <div class="col-12">
                <div class="card">
                    <div class="card-header bg-info">
                        <h3 class="card-title">Rekap Opname Harian</h3>
                        <div class="card-tools">
                            <button type="button" class="btn btn-tool" data-card-widget="collapse">
                                <i class="fas fa-minus"></i>
                            </button>
                            <button type="button" class="btn btn-tool" data-card-widget="remove">
                                <i class="fas fa-times"></i>
                            </button>
                        </div>
                    </div>
                    <div class="card-body">
                        <?php
                        $par1 = isset($_POST['par1']) ? date('Y-m-d', strtotime($_POST['par1'])) : '';
                        $par2 = isset($_POST['par2']) ? date('Y-m-d', strtotime($_POST['par2'])) : '';
                        $kode = $_SESSION['kode'];
                        ?>

                        <form role="form" action="home.php?home=<?php echo md5("lap_rekap"); ?>" method="post">
                            <div class="form-group row">
                                <label for="nama" class="col-2 m-2">Periode Tanggal</label>
                                <input type="date" name="par1" class="form-control col-2" value="<?= $par1 ?>" required>
                                <input type="date" name="par2" class="form-control col-2" value="<?= $par2 ?>" required>
                                <div class="col-1">
                                    <input type="submit" name="proses" class="btn btn-info" value="Preview">
                                </div>
                                <?php if (isset($_POST['par2'])) { ?>
                                    <a href="./pdf/lap_rekappdf.php?tgl1=<?= $_POST['par1'] ?>&tgl2=<?= $_POST['par2'] ?>">
                                        <i class="fas fa-file-pdf" style="font-size: 36px;"></i>
                                    </a>
                                <?php } ?>
                            </div>
                        </form>

                        <?php
                        if (isset($_POST['par2'])) {
                            include 'koneksi.php';

                            $par2m = date('m', strtotime($par2));
                            $par1m = date('m', strtotime($par1)) - 1;
                            $parth = date('Y', strtotime($par2));

                            // Optimize queries by combining them
                            $baseQuery = "SELECT 
                                SUM(CASE WHEN kode_akun LIKE '1.100.%' THEN debet-kredit ELSE 0 END) as kas,
                                SUM(CASE WHEN kode_akun = '1.105.10' THEN debet-kredit ELSE 0 END) as bca,
                                SUM(CASE WHEN kode_akun = '1.105.20' THEN debet-kredit ELSE 0 END) as bri,
                                SUM(CASE WHEN kode_akun = '1.120.10' THEN debet-kredit ELSE 0 END) as bakidebet,
                                SUM(CASE WHEN kode_akun LIKE '4.4%' THEN kredit-debet ELSE 0 END) as pendapatan,
                                SUM(CASE WHEN kode_akun LIKE '5.5%' THEN debet-kredit ELSE 0 END) as beban
                                FROM bukubesar 
                                WHERE kode = '$kode'";

                            // Get data for first period
                            $data1 = fetchData($koneksi, $baseQuery . " AND tanggal < '$par1'");
                            
                            // Get data for second period
                            $data2 = fetchData($koneksi, $baseQuery . " AND tanggal <= '$par2'");

                            // Get collateral data in one query
                            $jaminanQuery = "SELECT 
                                jaminan,
                                COUNT(CASE WHEN tgl < '$par1' AND bakidebet > 0 THEN 1 END) as count1,
                                COUNT(CASE WHEN tgl <= '$par2' AND bakidebet > 0 THEN 1 END) as count2
                                FROM pinjaman 
                                WHERE kode = '$kode'
                                GROUP BY jaminan";

                            $jaminanResult = mysqli_query($koneksi, $jaminanQuery);
                            $jaminanData = [];
                            while ($row = mysqli_fetch_assoc($jaminanResult)) {
                                $jaminanData[$row['jaminan']] = [
                                    'count1' => $row['count1'],
                                    'count2' => $row['count2']
                                ];
                            }
                            ?>

                            <table id="sortTable" class="table table-bordered table-hover">
                                <thead>
                                    <tr>
                                        <th width="6%">No</th>
                                        <th width="12%">Nama</th>
                                        <th width="13%" style="text-align:center;">
                                            <?php echo date('d-m-Y', strtotime('-1 days', strtotime($par1))); ?>
                                        </th>
                                        <th width="13%" style="text-align:center;"><?php echo $par2; ?></th>
                                        <th width="13%" style="text-align:center;">Tumbuh</th>
                                        <th width="13%" style="text-align:center;">%</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php
                                    // Display financial data
                                    $items = [
                                        ['Kas', $data1['kas'], $data2['kas']],
                                        ['Bank BCA', $data1['bca'], $data2['bca']],
                                        ['Bank BRI', $data1['bri'], $data2['bri']],
                                        ['Baki Debet', $data1['bakidebet'], $data2['bakidebet']],
                                        ['Pendapatan', $data1['pendapatan'], $data2['pendapatan']],
                                        ['Beban', $data1['beban'], $data2['beban']]
                                    ];

                                    $no = 1;
                                    foreach ($items as $item) {
                                        $growth = $item[2] - $item[1];
                                        $percentage = calculatePercentage($growth, $item[2]);
                                        ?>
                                        <tr>
                                            <td><?php echo $no++; ?></td>
                                            <td><?php echo $item[0]; ?></td>
                                            <td align="right"><?php echo number_format($item[1], 0); ?></td>
                                            <td align="right"><?php echo number_format($item[2], 0); ?></td>
                                            <td align="right"><?php echo number_format($growth, 0); ?></td>
                                            <td align="right"><?php echo $percentage; ?></td>
                                        </tr>
                                        <?php
                                    }

                                    // Display collateral data
                                    foreach ($jaminanData as $jaminan => $counts) {
                                        $growth = $counts['count2'] - $counts['count1'];
                                        $percentage = calculatePercentage($growth, $counts['count2']);
                                        ?>
                                        <tr>
                                            <td><?php echo $no++; ?></td>
                                            <td><?php echo $jaminan; ?></td>
                                            <td align="right"><?php echo number_format($counts['count1'], 0); ?></td>
                                            <td align="right"><?php echo number_format($counts['count2'], 0); ?></td>
                                            <td align="right"><?php echo number_format($growth, 0); ?></td>
                                            <td align="right"><?php echo $percentage; ?></td>
                                        </tr>
                                        <?php
                                    }
                                    ?>
                                </tbody>
                            </table>

                            <script type="text/javascript" charset="utf8" src="./assets/jquery.dataTables.js"></script>
                            <link rel="stylesheet" type="text/css" href="./assets/jquery.dataTables.css">
                            <script>
                                $('#sortTable').DataTable();
                            </script>
                        <?php } ?>
                    </div>
                </div>
            </div>
        </div>
    </div>
</section>