<?php
// Koneksi ke database
$connection = new mysqli('192.168.81.237', 'fadhil', 'fadhil123', 'ppak');


$query = "SELECT DISTINCT nama_tipe, warna FROM `allocation_tipewarna`";
$result = $connection->query($query);

// Menyusun array tipe dengan warna terkaitnya
$tipeWarna = [];
while ($row = $result->fetch_assoc()) {
    $tipeWarna[$row['nama_tipe']][] = $row['warna'];
}

// Hitung jumlah tipe dan warna untuk pengaturan colspan
$jumlah_tipe = count($tipeWarna);
$colspan_per_tipe = 4; // Asumsi 4 kolom per warna
$colspan_total = $colspan_per_tipe * $jumlah_tipe;
?>
<?php
// Mengambil semua data dealer dan qty dalam satu query menggunakan JOIN
$query = "
    SELECT d.subyek, d.kode_lks, a.tipe, a.warna, a.qty 
    FROM fkt_dealer d
    LEFT JOIN allocation_stock a 
    ON d.kode_lks = a.dealer_id
    WHERE d.grup='LT' AND d.nonaktif=0 
    AND d.kode_lks NOT IN ('FLEET01','GUDANG UNIT','MD') 
    ORDER BY d.urutan";

$result = $connection->query($query);

// Menyusun array dealer dengan tipe, warna, dan qty
$dealerData = [];
while ($row = $result->fetch_assoc()) {
    $dealerData[$row['kode_lks']]['subyek'] = $row['subyek'];
    $dealerData[$row['kode_lks']]['qty'][$row['tipe']][$row['warna']] = $row['qty'] ?? 0; // Jika qty null, tampilkan 0
}
?>

<!-- Kode HTML untuk tabel -->
<table  border=1>
    <thead>
        <tr>
            <th>No</th>
            <th>Dealer</th>
            <!-- Looping header untuk tipe dan warna -->
            <?php foreach ($tipeWarna as $tipe => $warnas): ?>
                <th colspan="<?php echo count($warnas) * 4; ?>"><?php echo $tipe; ?></th>
            <?php endforeach; ?>
        </tr>
    </thead>
    <tbody>
        <?php
        $no = 1;
        foreach ($dealerData as $kode_lks => $data): ?>
            <tr>
                <th><?php echo $no++; ?></th>
                <td><?php echo $data['subyek']; ?></td>
                <!-- Loop tipe dan warna untuk menampilkan data qty -->
                <?php foreach ($tipeWarna as $tipe => $warnas): ?>
                    <?php foreach ($warnas as $warna): ?>
                        <td>0</td> <!-- SL -->
                        <td>
                            <?php 
                            // Menampilkan qty, jika tidak ada tampilkan 0
                            echo $data['qty'][$tipe][$warna] ?? 0;
                            ?>
                        </td> <!-- ST -->
                        <td>0</td> <!-- RS -->
                        <td>0</td> <!-- AL -->
                    <?php endforeach; ?>
                <?php endforeach; ?>
            </tr>
        <?php endforeach; ?>
    </tbody>
</table>

<table class="table table-hover table-bordered align-middle text-center" border=1>
    <thead class="align-middle text-center">
        <tr>
            <th rowspan="2">No</th>
            <th rowspan="2">Tanggal: <?php echo date('d/m/Y'); ?></th>
            <!-- Loop Tipe -->
            <?php foreach ($tipeWarna as $tipe => $warnas): ?>
                <th colspan="<?php echo $colspan_per_tipe * count($warnas); ?>"><?php echo $tipe; ?></th>
                <th colspan="4" class="table-secondary">TOTAL</th>
            <?php endforeach; ?>
        </tr>
        <tr>
            <!-- Loop Tipe dan Warna -->
            <?php foreach ($tipeWarna as $tipe => $warnas): ?>
                <?php foreach ($warnas as $warna): ?>
                    <th colspan="4"><?php echo $warna; ?></th>
                <?php endforeach; ?>
                <th colspan="4" class="table-secondary"><?php echo $tipe; ?></th>
            <?php endforeach; ?>
        </tr>
    </thead>
    <tbody>
        <!-- Data baris A hingga E -->
        <?php
        $data_baris = ['A' => 'STOCK GUDANG', 'B' => 'IN', 'C' => 'STOCK IN HAND', 'D' => 'DISTRIBUSI HARIAN', 'E' => 'SISA STICK GUDANG'];
        foreach ($data_baris as $kode => $nama_baris): ?>
            <tr>
                <th scope="row"><?php echo $kode; ?></th>
                <td><?php echo $nama_baris; ?></td>
                <!-- Loop untuk mengisi data per tipe dan warna -->
                <?php foreach ($tipeWarna as $tipe => $warnas): ?>
                    <?php foreach ($warnas as $warna): ?>
                        <td>0</td>
                        <td>0</td>
                        <td>0</td>
                        <td>0</td>
                    <?php endforeach; ?>
                    <td class="table-secondary">0</td>
                    <td class="table-secondary">0</td>
                    <td class="table-secondary">0</td>
                    <td class="table-secondary">0</td>
                <?php endforeach; ?>
            </tr>
        <?php endforeach; ?>

        <!-- Baris DEALER -->
        <tr>
            <th scope="row"></th>
            <td><strong>DEALER</strong></td>
            <?php foreach ($tipeWarna as $tipe => $warnas): ?>
                <?php foreach ($warnas as $warna): ?>
                    <td><strong><span data-bs-toggle="tooltip" data-bs-placement="top" title="DO">SL</span></strong></td>
                    <td><strong><span data-bs-toggle="tooltip" data-bs-placement="top" title="STOCK">ST</span></strong></td>
                    <td><strong><span data-bs-toggle="tooltip" data-bs-placement="top" title="RASIO">RS</span></strong></td>
                    <td><strong><span data-bs-toggle="tooltip" data-bs-placement="top" title="ALOKASI">AL</span></strong></td>
                <?php endforeach; ?>
                <td class="table-secondary"><strong><span data-bs-toggle="tooltip" data-bs-placement="top" title="TOTAL DO">SL</span></strong></td>
                <td class="table-secondary"><strong><span data-bs-toggle="tooltip" data-bs-placement="top" title="TOTAL STOCK">ST</span></strong></td>
                <td class="table-secondary"><strong><span data-bs-toggle="tooltip" data-bs-placement="top" title="TOTAL RASIO">RS</span></strong></td>
                <td class="table-secondary"><strong><span data-bs-toggle="tooltip" data-bs-placement="top" title="TOTAL ALOKASI">AL</span></strong></td>
            <?php endforeach; ?>
        </tr>

        <?php
        // Query untuk mendapatkan data dealer
        $dealerQuery = "SELECT subyek, kode_lks 
                        FROM fkt_dealer 
                        WHERE grup='LT' 
                        AND nonaktif=0 
                        AND kode_lks NOT IN ('FLEET01','GUDANG UNIT') 
                        ORDER BY urutan";
        $dealerResult = $connection->query($dealerQuery);

        // Loop untuk menampilkan baris dealer
        if ($dealerResult->num_rows > 0) {
            $no = 1; // Variabel untuk nomor urut dealer
            while ($dealerRow = $dealerResult->fetch_assoc()) {
        ?>
        <tr>
          <th scope="row"><?php echo $no++; ?></th>
          <td class="text-start"><?php echo $dealerRow['subyek']; ?></td>
          <!-- Loop Tipe -->
          <?php foreach ($tipeWarna as $tipe => $warnas): ?>
            <!-- Loop Warna -->
            <?php foreach ($warnas as $warna): ?>
              <td>0</td> <!-- SL -->
              <td>0</td> <!-- ST -->
              <td>0</td> <!-- RS -->
              <td>0</td> <!-- AL -->
            <?php endforeach; ?>
            <!-- Total -->
            <td class="table-secondary">0</td> <!-- TOTAL SL -->
            <td class="table-secondary">0</td> <!-- TOTAL ST -->
            <td class="table-secondary">0</td> <!-- TOTAL RS -->
            <td class="table-secondary">0</td> <!-- TOTAL AL -->
          <?php endforeach; ?>
        </tr>
        <?php
          }
        }
        ?>

    </tbody>
</table>






<?php
// Dummy data untuk testing, ini berasal dari database seharusnya
// SELECT nama_tipe, warna FROM `allocation_tipewarna`
$allocation_tipewarna = [
    ['nama_tipe' => 'Tipe A', 'warna' => ['Merah', 'Biru']],
    ['nama_tipe' => 'Tipe B', 'warna' => ['Hijau', 'Kuning']],
];

// Menghitung total kolom berdasarkan jumlah tipe dan warna
$totalWarna = 0;
foreach ($allocation_tipewarna as $tipe) {
    $totalWarna += count($tipe['warna']);
}
$totalColspan = $totalWarna + count($allocation_tipewarna) * 4;

?>

<table class="table table-hover table-bordered align-middle text-center">
    <thead class="align-middle text-center">
        <tr>
            <th rowspan="2">No</th>
            <th rowspan="2">Tanggal: <?php echo date('d/m/Y'); ?></th>

            <!-- Loop Tipe untuk header tipe -->
            <?php foreach ($allocation_tipewarna as $tipe): ?>
                <th colspan="<?php echo count($tipe['warna']) * 4; ?>"><?php echo $tipe['nama_tipe']; ?></th>
            <?php endforeach; ?>
            <!-- Akhir Loop Tipe -->

            <th colspan="4" class="table-secondary">TOTAL</th>
        </tr>
        <tr>
            <!-- Loop Warna untuk header warna -->
            <?php foreach ($allocation_tipewarna as $tipe): ?>
                <?php foreach ($tipe['warna'] as $warna): ?>
                    <th colspan="4"><?php echo $warna; ?></th>
                <?php endforeach; ?>
            <?php endforeach; ?>
            <!-- Akhir Loop Warna -->

            <th class="table-secondary">SL</th>
            <th class="table-secondary">ST</th>
            <th class="table-secondary">RS</th>
            <th class="table-secondary">AL</th>
        </tr>
    </thead>

    <tbody>
        <!-- Bagian Stock Gudang -->
        <tr>
            <th scope="row">A</th>
            <td>STOCK GUDANG</td>
            <!-- Loop tipe dan warna untuk isi data -->
            <?php foreach ($allocation_tipewarna as $tipe): ?>
                <?php foreach ($tipe['warna'] as $warna): ?>
                    <td>0</td>
                <?php endforeach; ?>
            <?php endforeach; ?>
            <!-- Akhir Loop tipe dan warna -->
            <td class="table-secondary">0</td>
            <td class="table-secondary">0</td>
            <td class="table-secondary">0</td>
            <td class="table-secondary">0</td>
        </tr>

        <!-- Bagian IN -->
        <tr>
            <th scope="row">B</th>
            <td>IN</td>
            <!-- Loop tipe dan warna untuk isi data -->
            <?php foreach ($allocation_tipewarna as $tipe): ?>
                <?php foreach ($tipe['warna'] as $warna): ?>
                    <td>0</td>
                <?php endforeach; ?>
            <?php endforeach; ?>
            <!-- Akhir Loop tipe dan warna -->
            <td class="table-secondary">0</td>
            <td class="table-secondary">0</td>
            <td class="table-secondary">0</td>
            <td class="table-secondary">0</td>
        </tr>

        <!-- Bagian Stock in Hand -->
        <tr>
            <th scope="row">C</th>
            <td>STOCK IN HAND</td>
            <!-- Loop tipe dan warna untuk isi data -->
            <?php foreach ($allocation_tipewarna as $tipe): ?>
                <?php foreach ($tipe['warna'] as $warna): ?>
                    <td>0</td>
                <?php endforeach; ?>
            <?php endforeach; ?>
            <!-- Akhir Loop tipe dan warna -->
            <td class="table-secondary">0</td>
            <td class="table-secondary">0</td>
            <td class="table-secondary">0</td>
            <td class="table-secondary">0</td>
        </tr>

        <!-- Bagian Distribusi Harian -->
        <tr>
            <th scope="row">D</th>
            <td>DISTRIBUSI HARIAN</td>
            <!-- Loop tipe dan warna untuk isi data -->
            <?php foreach ($allocation_tipewarna as $tipe): ?>
                <?php foreach ($tipe['warna'] as $warna): ?>
                    <td>0</td>
                <?php endforeach; ?>
            <?php endforeach; ?>
            <!-- Akhir Loop tipe dan warna -->
            <td class="table-secondary">0</td>
            <td class="table-secondary">0</td>
            <td class="table-secondary">0</td>
            <td class="table-secondary">0</td>
        </tr>

        <!-- Bagian Sisa Stick Gudang -->
        <tr>
            <th scope="row">E</th>
            <td>SISA STICK GUDANG</td>
            <!-- Loop tipe dan warna untuk isi data -->
            <?php foreach ($allocation_tipewarna as $tipe): ?>
                <?php foreach ($tipe['warna'] as $warna): ?>
                    <td>0</td>
                <?php endforeach; ?>
            <?php endforeach; ?>
            <!-- Akhir Loop tipe dan warna -->
            <td class="table-secondary">0</td>
            <td class="table-secondary">0</td>
            <td class="table-secondary">0</td>
            <td class="table-secondary">0</td>
        </tr>

        <!-- Bagian Dealer -->
        <tr>
            <th scope="row"></th>
            <td><strong>DEALER</strong></td>
            <!-- Loop tipe dan warna untuk kolom judul -->
            <?php foreach ($allocation_tipewarna as $tipe): ?>
                <?php foreach ($tipe['warna'] as $warna): ?>
                    <td><strong>SL</strong></td>
                    <td><strong>ST</strong></td>
                    <td><strong>RS</strong></td>
                    <td><strong>AL</strong></td>
                <?php endforeach; ?>
            <?php endforeach; ?>
            <!-- Akhir Loop tipe dan warna -->
            <td class="table-secondary"><strong>SL</strong></td>
            <td class="table-secondary"><strong>ST</strong></td>
            <td class="table-secondary"><strong>RS</strong></td>
            <td class="table-secondary"><strong>AL</strong></td>
        </tr>

        <!-- Contoh Loop Dealer -->
        <tr>
            <th scope="row">1</th>
            <td class="text-start">Nama DEALER</td>
            <!-- Loop tipe dan warna untuk isi data dealer -->
            <?php foreach ($allocation_tipewarna as $tipe): ?>
                <?php foreach ($tipe['warna'] as $warna): ?>
                    <td>0</td>
                    <td>0</td>
                    <td>0</td>
                    <td>0</td>
                <?php endforeach; ?>
            <?php endforeach; ?>
            <!-- Akhir Loop tipe dan warna -->
            <td>0</td>
            <td>0</td>
            <td>0</td>
            <td>0</td>
        </tr>
    </tbody>
</table>
