<?php    
$page_title = 'Summary Scan In';    
require_once('includes/load.php');    
page_require_level(3);    

// Set default timezone    
date_default_timezone_set('Asia/Jakarta');    

// Mengambil input tanggal dari pengguna jika ada, jika tidak gunakan tanggal hari ini    
$start_date = isset($_GET['start_date']) ? $_GET['start_date'] : date('Y-m-d');    
$end_date = isset($_GET['end_date']) ? $_GET['end_date'] : date('Y-m-d');    

// Query untuk mendapatkan ringkasan scan_in berdasarkan part_number, filter tanggal, urutkan berdasarkan total_quantity    
$sql  = "SELECT awb.part_number, SUM(awb.quantity) AS total_quantity, products.name, ";    
$sql .= "CASE ";    
$sql .= "WHEN awb.loc = 'Jakarta' THEN 'Jakarta' ";    
$sql .= "WHEN awb.loc = 'Makassar' THEN 'Makassar' ";    
$sql .= "WHEN awb.loc = 'Bali' THEN 'Bali' ";    
$sql .= "WHEN awb.loc = 'Semarang' THEN 'Semarang' ";    
$sql .= "WHEN awb.loc = 'Bandung' THEN 'Bandung' ";    
$sql .= "ELSE 'Surabaya' END AS lokasi ";    
$sql .= "FROM awb ";    
$sql .= "LEFT JOIN products ON awb.part_number = products.part_number ";    
$sql .= "WHERE DATE(awb.scan_in) BETWEEN '{$start_date}' AND '{$end_date}' ";    
$sql .= "GROUP BY awb.part_number, products.name, lokasi ";    
$sql .= "ORDER BY lokasi, total_quantity DESC";    
$result = $db->query($sql);    

// Query khusus untuk tab All Cities (group by part_number saja)
$sql_all = "SELECT awb.part_number, SUM(awb.quantity) AS total_quantity, products.name
            FROM awb
            LEFT JOIN products ON awb.part_number = products.part_number
            WHERE DATE(awb.scan_in) BETWEEN '{$start_date}' AND '{$end_date}'
            GROUP BY awb.part_number, products.name
            ORDER BY total_quantity DESC";
$result_all = $db->query($sql_all);
$all_data = $result_all->fetch_all(MYSQLI_ASSOC);

include_once('layouts/header.php');    

// Inisialisasi array untuk menyimpan data berdasarkan lokasi    
$data_jakarta = [];    
$data_surabaya = [];    
$data_makassar = [];    
$data_bali = [];    
$data_semarang = [];    
$data_bandung = [];    

// Mengelompokkan hasil ke dalam array berdasarkan lokasi    
while($row = $result->fetch_assoc()) {    
    if ($row['lokasi'] === 'Jakarta') {    
        $data_jakarta[] = $row;    
    } elseif ($row['lokasi'] === 'Makassar') {    
        $data_makassar[] = $row;    
    } elseif ($row['lokasi'] === 'Bali') {    
        $data_bali[] = $row;    
    } elseif ($row['lokasi'] === 'Semarang') {    
        $data_semarang[] = $row;    
    } elseif ($row['lokasi'] === 'Bandung') {    
        $data_bandung[] = $row;    
    } else {    
        $data_surabaya[] = $row;    
    }    
}    
?>    

<div class="row">    
    <div class="col-md-12">    
        <div class="panel panel-default">    
            <div class="panel-heading">    
                <h3 class="panel-title">Summary Scan In</h3>    
            </div>    
            <div class="panel-body">    
                <!-- Filter Tanggal -->    
                <form method="GET" action="summary_scan_in.php">    
                    <div class="form-group">    
                        <label for="start_date">Start Date:</label>    
                        <input type="date" name="start_date" class="form-control" value="<?php echo $start_date; ?>">    
                    </div>    
                    <div class="form-group">    
                        <label for="end_date">End Date:</label>    
                        <input type="date" name="end_date" class="form-control" value="<?php echo $end_date; ?>">    
                    </div>    
                    <button type="submit" class="btn btn-primary">Filter</button>    
                    <button type="button" id="export-excel" class="btn btn-success">Export to Excel</button>    
                </form>    

                <!-- Tab Navigation -->
                <ul class="nav nav-tabs" id="cityTabs" role="tablist">
                    <li class="nav-item">
                        <a class="nav-link active" id="all-tab" data-toggle="tab" href="#all" role="tab" aria-controls="all" aria-selected="true">All Cities</a>
                    </li>
                    <li class="nav-item">
                        <a class="nav-link" id="jakarta-tab" data-toggle="tab" href="#jakarta" role="tab" aria-controls="jakarta" aria-selected="false">Jakarta</a>
                    </li>
                    <li class="nav-item">
                        <a class="nav-link" id="makassar-tab" data-toggle="tab" href="#makassar" role="tab" aria-controls="makassar" aria-selected="false">Makassar</a>
                    </li>
                    <li class="nav-item">
                        <a class="nav-link" id="bali-tab" data-toggle="tab" href="#bali" role="tab" aria-controls="bali" aria-selected="false">Bali</a>
                    </li>
                    <li class="nav-item">
                        <a class="nav-link" id="semarang-tab" data-toggle="tab" href="#semarang" role="tab" aria-controls="semarang" aria-selected="false">Semarang</a>
                    </li>
                    <li class="nav-item">
                        <a class="nav-link" id="bandung-tab" data-toggle="tab" href="#bandung" role="tab" aria-controls="bandung" aria-selected="false">Bandung</a>
                    </li>
                    <li class="nav-item">
                        <a class="nav-link" id="surabaya-tab" data-toggle="tab" href="#surabaya" role="tab" aria-controls="surabaya" aria-selected="false">Surabaya</a>
                    </li>
                </ul>

                <!-- Tab Content -->
                <div class="tab-content" id="cityTabsContent">
                    <!-- Tab Content for All Cities -->
                    <div class="tab-pane fade show active" id="all" role="tabpanel" aria-labelledby="all-tab">
                        <h4 style="color: #6610f2;">All Cities Summary</h4>
                        <table id="summary-table-all" class="table table-bordered table-striped" style="margin-top: 20px; background-color: #f9f9f9;">
                            <thead style="background-color: #6610f2; color: white;">
                                <tr>
                                    <th>Part Number</th>
                                    <th>Name</th>
                                    <th>Total Quantity</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php foreach($all_data as $row): ?>
                                <tr>
                                    <td><?php echo remove_junk($row['part_number']); ?></td>
                                    <td>
                                        <a href="view_product.php?part_number=<?php echo urlencode($row['part_number']); ?>">
                                            <?php echo remove_junk($row['name']); ?>
                                        </a>
                                    </td>
                                    <td><?php echo remove_junk($row['total_quantity']); ?></td>
                                </tr>
                                <?php endforeach; ?>
                            </tbody>
                        </table>
                    </div>

                    <!-- Tab Content for Jakarta -->
                    <div class="tab-pane fade" id="jakarta" role="tabpanel" aria-labelledby="jakarta-tab">
                        <h4 style="color: #007bff;">Data Jakarta</h4>
                        <table id="summary-table-jakarta" class="table table-bordered table-striped" style="margin-top: 20px; background-color: #f9f9f9;">
                            <thead style="background-color: #007bff; color: white;">
                                <tr>
                                    <th>Part Number</th>
                                    <th>Name</th>
                                    <th>Total Quantity</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php foreach($data_jakarta as $row): ?>
                                <tr>
                                    <td><?php echo remove_junk($row['part_number']); ?></td>
                                    <td>
                                        <a href="view_product.php?part_number=<?php echo urlencode($row['part_number']); ?>">
                                            <?php echo remove_junk($row['name']); ?>
                                        </a>
                                    </td>
                                    <td><?php echo remove_junk($row['total_quantity']); ?></td>
                                </tr>
                                <?php endforeach; ?>
                            </tbody>
                        </table>
                    </div>

                    <!-- Tab Content for Makassar -->
                    <div class="tab-pane fade" id="makassar" role="tabpanel" aria-labelledby="makassar-tab">
                        <h4 style="color: #28a745;">Data Makassar</h4>
                        <table id="summary-table-makassar" class="table table-bordered table-striped" style="margin-top: 20px; background-color: #e7f9e7;">
                            <thead style="background-color: #28a745; color: white;">
                                <tr>
                                    <th>Part Number</th>
                                    <th>Name</th>
                                    <th>Total Quantity</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php foreach($data_makassar as $row): ?>
                                <tr>
                                    <td><?php echo remove_junk($row['part_number']); ?></td>
                                    <td>
                                        <a href="view_product.php?part_number=<?php echo urlencode($row['part_number']); ?>">
                                            <?php echo remove_junk($row['name']); ?>
                                        </a>
                                    </td>
                                    <td><?php echo remove_junk($row['total_quantity']); ?></td>
                                </tr>
                                <?php endforeach; ?>
                            </tbody>
                        </table>
                    </div>

                    <!-- Tab Content for Bali -->
                    <div class="tab-pane fade" id="bali" role="tabpanel" aria-labelledby="bali-tab">
                        <h4 style="color: #17a2b8;">Data Bali</h4>
                        <table id="summary-table-bali" class="table table-bordered table-striped" style="margin-top: 20px; background-color: #e0f7f9;">
                            <thead style="background-color: #17a2b8; color: white;">
                                <tr>
                                    <th>Part Number</th>
                                    <th>Name</th>
                                    <th>Total Quantity</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php foreach($data_bali as $row): ?>
                                <tr>
                                    <td><?php echo remove_junk($row['part_number']); ?></td>
                                    <td>
                                        <a href="view_product.php?part_number=<?php echo urlencode($row['part_number']); ?>">
                                            <?php echo remove_junk($row['name']); ?>
                                        </a>
                                    </td>
                                    <td><?php echo remove_junk($row['total_quantity']); ?></td>
                                </tr>
                                <?php endforeach; ?>
                            </tbody>
                        </table>
                    </div>

                    <!-- Tab Content for Semarang -->
                    <div class="tab-pane fade" id="semarang" role="tabpanel" aria-labelledby="semarang-tab">
                        <h4 style="color: #ff9933;">Data Semarang</h4>
                        <table id="summary-table-semarang" class="table table-bordered table-striped" style="margin-top: 20px; background-color: #fff9e6;">
                            <thead style="background-color: #ff9933; color: white;">
                                <tr>
                                    <th>Part Number</th>
                                    <th>Name</th>
                                    <th>Total Quantity</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php foreach($data_semarang as $row): ?>
                                <tr>
                                    <td><?php echo remove_junk($row['part_number']); ?></td>
                                    <td>
                                        <a href="view_product.php?part_number=<?php echo urlencode($row['part_number']); ?>">
                                            <?php echo remove_junk($row['name']); ?>
                                        </a>
                                    </td>
                                    <td><?php echo remove_junk($row['total_quantity']); ?></td>
                                </tr>
                                <?php endforeach; ?>
                            </tbody>
                        </table>
                    </div>

                    <!-- Tab Content for Bandung -->
                    <div class="tab-pane fade" id="bandung" role="tabpanel" aria-labelledby="bandung-tab">
                        <h4 style="color: #6c757d;">Data Bandung</h4>
                        <table id="summary-table-bandung" class="table table-bordered table-striped" style="margin-top: 20px; background-color: #f8f9fa;">
                            <thead style="background-color: #6c757d; color: white;">
                                <tr>
                                    <th>Part Number</th>
                                    <th>Name</th>
                                    <th>Total Quantity</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php foreach($data_bandung as $row): ?>
                                <tr>
                                    <td><?php echo remove_junk($row['part_number']); ?></td>
                                    <td>
                                        <a href="view_product.php?part_number=<?php echo urlencode($row['part_number']); ?>">
                                            <?php echo remove_junk($row['name']); ?>
                                        </a>
                                    </td>
                                    <td><?php echo remove_junk($row['total_quantity']); ?></td>
                                </tr>
                                <?php endforeach; ?>
                            </tbody>
                        </table>
                    </div>

                    <!-- Tab Content for Surabaya -->
                    <div class="tab-pane fade" id="surabaya" role="tabpanel" aria-labelledby="surabaya-tab">
                        <h4 style="color: #dc3545;">Data Surabaya</h4>
                        <table id="summary-table-surabaya" class="table table-bordered table-striped" style="margin-top: 20px; background-color: #f2f2f2;">
                            <thead style="background-color: #dc3545; color: white;">
                                <tr>
                                    <th>Part Number</th>
                                    <th>Name</th>
                                    <th>Total Quantity</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php foreach($data_surabaya as $row): ?>
                                <tr>
                                    <td><?php echo remove_junk($row['part_number']); ?></td>
                                    <td>
                                        <a href="view_product.php?part_number=<?php echo urlencode($row['part_number']); ?>">
                                            <?php echo remove_junk($row['name']); ?>
                                        </a>
                                    </td>
                                    <td><?php echo remove_junk($row['total_quantity']); ?></td>
                                </tr>
                                <?php endforeach; ?>
                            </tbody>
                        </table>
                    </div>
                </div>
            </div>    
        </div>    
    </div>    
</div>    

<?php include_once('layouts/footer.php'); ?>    

<!-- Include Bootstrap JS and dependencies -->
<script src="https://code.jquery.com/jquery-3.5.1.slim.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/@popperjs/core@2.9.3/dist/umd/popper.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>

<!-- Include FileSaver.js dan xlsx.js -->
<script src="js/FileSaver.js-master/src/FileSaver.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.17.4/xlsx.full.min.js"></script>

<script>
document.getElementById('export-excel').addEventListener('click', function() {    
    // Ambil tabel HTML untuk semua tab
    var wb = XLSX.utils.table_to_book(document.getElementById('summary-table-all'), {sheet: "All Cities"});
    XLSX.utils.book_append_sheet(wb, XLSX.utils.table_to_sheet(document.getElementById('summary-table-jakarta')), "Data Jakarta");
    XLSX.utils.book_append_sheet(wb, XLSX.utils.table_to_sheet(document.getElementById('summary-table-makassar')), "Data Makassar");
    XLSX.utils.book_append_sheet(wb, XLSX.utils.table_to_sheet(document.getElementById('summary-table-bali')), "Data Bali");
    XLSX.utils.book_append_sheet(wb, XLSX.utils.table_to_sheet(document.getElementById('summary-table-semarang')), "Data Semarang");
    XLSX.utils.book_append_sheet(wb, XLSX.utils.table_to_sheet(document.getElementById('summary-table-surabaya')), "Data Surabaya");
    XLSX.utils.book_append_sheet(wb, XLSX.utils.table_to_sheet(document.getElementById('summary-table-bandung')), "Data Bandung");
    XLSX.writeFile(wb, "Summary_Scan_In_<?php echo $start_date; ?>_to_<?php echo $end_date; ?>.xlsx");    
});
</script>