<?php
$page_title = 'Product Stock Real Time';
require_once('includes/load.php');
// Checkin What level user has permission to view this page
page_require_level(1);


$redis_key_semarang = 'stok:semarang';
$redis_key_surabaya = 'stok:surabaya';
$redis_key_jakarta = 'stok:jakarta';
$redis_key_bali = 'stok:bali';
$redis_key_makassar = 'stok:makassar';
$redis_key_bandung = 'stok:bandung';


$data_semarang = redisGet($redis_key_semarang);
$data_surabaya = redisGet($redis_key_surabaya);
$data_jakarta = redisGet($redis_key_jakarta);
$data_bali = redisGet($redis_key_bali);
$data_makassar = redisGet($redis_key_makassar);
$data_bandung = redisGet($redis_key_bandung);

// SQL query for Bandung

$sql_bandung = "
SELECT
    COALESCE(gpn.part_number, wj.part_number) AS part_number,
    wj.UoM,
    c.name AS category_name,
    MAX(wj.quantity + COALESCE(opj.quantity, 0)) AS max_total_quantity,
    MAX(wj.quantity + COALESCE(opj.quantity, 0) - COALESCE(awb.quantity, 0)) AS max_total_quantity_ongoing
FROM 
  wh_bandung wj 
  LEFT JOIN online_products_bandung opj ON wj.part_number = opj.part_number 
  LEFT JOIN awb ON awb.part_number = wj.part_number AND awb.scan_status IS NULL AND loc = 'bandung'
  LEFT JOIN group_part_number gpn ON wj.part_number = gpn.part_number_child  
  LEFT JOIN categories c ON wj.categorie_id = c.id
GROUP BY
    COALESCE(gpn.part_number, wj.part_number),
    wj.UoM,
    c.name;";

// SQL query for Jakarta
$sql_jakarta = "
SELECT
    COALESCE(gpn.part_number, wj.part_number) AS part_number,
    wj.UoM,
    c.name AS category_name,
    MAX(wj.quantity + COALESCE(opj.quantity, 0)) AS max_total_quantity,
    MAX(wj.quantity + COALESCE(opj.quantity, 0) - COALESCE(awb.quantity, 0)) AS max_total_quantity_ongoing
FROM 
  wh_jakarta wj 
  LEFT JOIN online_products_jakarta opj ON wj.part_number = opj.part_number 
  LEFT JOIN awb ON awb.part_number = wj.part_number AND awb.scan_status IS NULL AND loc = 'jakarta'
  LEFT JOIN group_part_number gpn ON wj.part_number = gpn.part_number_child  
  LEFT JOIN categories c ON wj.categorie_id = c.id
GROUP BY
    COALESCE(gpn.part_number, wj.part_number),
    wj.UoM,
    c.name;";

// SQL query for Surabaya
$sql_surabaya = "
SELECT 
    COALESCE(gpn.part_number, wj.part_number) AS part_number,
    wj.UoM,
    c.name AS category_name,
    MAX(wj.quantity + COALESCE(opj.quantity, 0)) AS max_total_quantity,
    MAX(wj.quantity + COALESCE(opj.quantity, 0) - COALESCE(awb.quantity, 0)) AS max_total_quantity_ongoing
FROM 
  products wj 
  LEFT JOIN online_products opj ON wj.part_number = opj.part_number 
  LEFT JOIN awb ON awb.part_number = wj.part_number AND awb.scan_status IS NULL AND loc is null
  LEFT JOIN group_part_number gpn ON wj.part_number = gpn.part_number_child  
  LEFT JOIN categories c ON wj.categorie_id = c.id
GROUP BY
    COALESCE(gpn.part_number, wj.part_number),
    wj.UoM,
    c.name;";

// SQL query for Bali
$sql_bali = "
SELECT 
    COALESCE(gpn.part_number, wj.part_number) AS part_number,
    wj.UoM,
    c.name AS category_name,
    MAX(wj.quantity + COALESCE(opj.quantity, 0)) AS max_total_quantity,
    MAX(wj.quantity + COALESCE(opj.quantity, 0) - COALESCE(awb.quantity, 0)) AS max_total_quantity_ongoing
FROM 
  wh_bali wj 
  LEFT JOIN online_products_bali opj ON wj.part_number = opj.part_number 
  LEFT JOIN awb ON awb.part_number = wj.part_number AND awb.scan_status IS NULL AND loc = 'bali'
  LEFT JOIN group_part_number gpn ON wj.part_number = gpn.part_number_child  
  LEFT JOIN categories c ON wj.categorie_id = c.id
GROUP BY
    COALESCE(gpn.part_number, wj.part_number),
    wj.UoM,
    c.name;";

// SQL query for Makassar
$sql_makassar = "
SELECT 
    COALESCE(gpn.part_number, wj.part_number) AS part_number,
    wj.UoM,
    c.name AS category_name,
    MAX(wj.quantity + COALESCE(opj.quantity, 0)) AS max_total_quantity,
    MAX(wj.quantity + COALESCE(opj.quantity, 0) - COALESCE(awb.quantity, 0)) AS max_total_quantity_ongoing
FROM 
  wh_makassar wj 
  LEFT JOIN online_products_makassar opj ON wj.part_number = opj.part_number 
  LEFT JOIN awb ON awb.part_number = wj.part_number AND awb.scan_status IS NULL AND loc = 'makassar' 
  LEFT JOIN group_part_number gpn ON wj.part_number = gpn.part_number_child 
  LEFT JOIN categories c ON wj.categorie_id = c.id
GROUP BY
    COALESCE(gpn.part_number, wj.part_number),
    wj.UoM,
    c.name;";

// SQL query for Semarang
$sql_semarang = "
SELECT 
    COALESCE(gpn.part_number, wj.part_number) AS part_number,
    wj.UoM,
    c.name AS category_name,
    MAX(wj.quantity + COALESCE(opj.quantity, 0)) AS max_total_quantity,
    MAX(wj.quantity + COALESCE(opj.quantity, 0) - COALESCE(awb.quantity, 0)) AS max_total_quantity_ongoing
FROM 
  wh_semarang wj 
  LEFT JOIN online_products_semarang opj ON wj.part_number = opj.part_number 
  LEFT JOIN awb ON awb.part_number = wj.part_number AND awb.scan_status IS NULL AND loc = 'semarang' 
  LEFT JOIN group_part_number gpn ON wj.part_number = gpn.part_number_child 
  LEFT JOIN categories c ON wj.categorie_id = c.id
GROUP BY
    COALESCE(gpn.part_number, wj.part_number),
    wj.UoM,
    c.name;";

if (!$data_bandung) {
  $result_bandung = $db->query($sql_bandung);
  $data_bandung = [];
  while ($row = $result_bandung->fetch_assoc()) {
    $data_bandung[] = $row;
  }
  redisSet($redis_key_bandung, $data_bandung, 200);
}

if (!$data_jakarta) {
  $result_jakarta = $db->query($sql_jakarta);
  $data_jakarta = [];
  while ($row = $result_jakarta->fetch_assoc()) {
    $data_jakarta[] = $row;
  }
  redisSet($redis_key_jakarta, $data_jakarta, 100);
}
if (!$data_surabaya) {
  $result_surabaya = $db->query($sql_surabaya);
  $data_surabaya = [];
  while ($row = $result_surabaya->fetch_assoc()) {
    $data_surabaya[] = $row;
  }
  redisSet($redis_key_surabaya, $data_surabaya, 300);
}

if (!$data_bali) {
  $result_bali = $db->query($sql_bali);
  $data_bali = [];
  while ($row = $result_bali->fetch_assoc()) {
    $data_bali[] = $row;
  }
  redisSet($redis_key_bali, $data_bali, 200);
}

if (!$data_makassar) {
  $result_makassar = $db->query($sql_makassar);
  $data_makassar = [];
  while ($row = $result_makassar->fetch_assoc()) {
    $data_makassar[] = $row;
  }
  redisSet($redis_key_makassar, $data_makassar, 100);
}

if (!$data_semarang) {
  $result_semarang = $db->query($sql_semarang);
  $data_semarang = [];
  while ($row = $result_semarang->fetch_assoc()) {
    $data_semarang[] = $row;
  }
  redisSet($redis_key_semarang, $data_semarang, 300);
}

?>

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

<div class="row">
  <div class="col-md-12">
    <?php echo display_msg($msg); ?>
  </div>
</div>
<!-- Tabel bandung -->
<div class="row">
  <div class="col-md-12">
    <div class="panel panel-default">
      <div class="panel-heading clearfix">
        <strong>
          <span class="glyphicon glyphicon-th"></span>
          <span>Product Stock bandung</span>
        </strong>
        <button type="button" class="btn btn-success pull-right" id="exportExcelBtn">Export to Excel</button>
      </div>
      <div class="panel-body">
        <table class="table table-bordered" id="datatable_bandung">
          <thead>
            <tr>
              <th>Part Number</th>
              <th>UoM</th>
              <th>Kategori</th>
              <th>Total Quantity</th>
              <th>Total Quantity Ongoing</th>
            </tr>
          </thead>
          <tbody>
            <?php foreach ($data_bandung as $row): ?>
              <tr>
                <td><?php echo $row['part_number']; ?></td>
                <td><?php echo $row['UoM']; ?></td>
                <td><?php echo $row['category_name']; ?></td>
                <td><?php echo $row['max_total_quantity']; ?></td>
                <td><?php echo $row['max_total_quantity_ongoing']; ?></td>
              </tr>
            <?php endforeach; ?>
          </tbody>
        </table>
      </div>
    </div>
  </div>
</div>

<!-- Tabel Jakarta -->
<div class="row">
  <div class="col-md-12">
    <div class="panel panel-default">
      <div class="panel-heading clearfix">
        <strong>
          <span class="glyphicon glyphicon-th"></span>
          <span>Product Stock Jakarta</span>
        </strong>
      </div>
      <div class="panel-body">
        <table class="table table-bordered" id="datatable_jakarta">
          <thead>
            <tr>
              <th>Part Number</th>
              <th>UoM</th>
              <th>Kategori</th>
              <th>Total Quantity</th>
              <th>Total Quantity Ongoing</th>
            </tr>
          </thead>
          <tbody>
            <?php foreach ($data_jakarta as $row): ?>
              <tr>
                <td><?php echo $row['part_number']; ?></td>
                <td><?php echo $row['UoM']; ?></td>
                <td><?php echo $row['category_name']; ?></td>
                <td><?php echo $row['max_total_quantity']; ?></td>
                <td><?php echo $row['max_total_quantity_ongoing']; ?></td>
              </tr>
            <?php endforeach; ?>
          </tbody>
        </table>
      </div>
    </div>
  </div>
</div>

<!-- Tabel Surabaya -->
<div class="row">
  <div class="col-md-12">
    <div class="panel panel-default">
      <div class="panel-heading clearfix">
        <strong>
          <span class="glyphicon glyphicon-th"></span>
          <span>Product Stock Surabaya</span>
        </strong>
      </div>
      <div class="panel-body">
        <table class="table table-bordered" id="datatable_surabaya">
          <thead>
            <tr>
              <th>Part Number</th>
              <th>UoM</th>
              <th>Kategori</th>
              <th>Total Quantity</th>
              <th>Total Quantity Ongoing</th>
            </tr>
          </thead>
          <tbody>
            <?php foreach ($data_surabaya as $row): ?>
              <tr>
                <td><?php echo $row['part_number']; ?></td>
                <td><?php echo $row['UoM']; ?></td>
                <td><?php echo $row['category_name']; ?></td>
                <td><?php echo $row['max_total_quantity']; ?></td>
                <td><?php echo $row['max_total_quantity_ongoing']; ?></td>
              </tr>
            <?php endforeach; ?>
          </tbody>
        </table>
      </div>
    </div>
  </div>
</div>

<!-- Tabel Bali -->
<div class="row">
  <div class="col-md-12">
    <div class="panel panel-default">
      <div class="panel-heading clearfix">
        <strong>
          <span class="glyphicon glyphicon-th"></span>
          <span>Product Stock Bali</span>
        </strong>
      </div>
      <div class="panel-body">
        <table class="table table-bordered" id="datatable_bali">
          <thead>
            <tr>
              <th>Part Number</th>
              <th>UoM</th>
              <th>Kategori</th>
              <th>Total Quantity</th>
              <th>Total Quantity Ongoing</th>
            </tr>
          </thead>
          <tbody>
            <?php foreach ($data_bali as $row): ?>
              <tr>
                <td><?php echo $row['part_number']; ?></td>
                <td><?php echo $row['UoM']; ?></td>
                <td><?php echo $row['category_name']; ?></td>
                <td><?php echo $row['max_total_quantity']; ?></td>
                <td><?php echo $row['max_total_quantity_ongoing']; ?></td>
              </tr>
            <?php endforeach; ?>
          </tbody>
        </table>
      </div>
    </div>
  </div>
</div>

<!-- Tabel Makassar -->
<div class="row">
  <div class="col-md-12">
    <div class="panel panel-default">
      <div class="panel-heading clearfix">
        <strong>
          <span class="glyphicon glyphicon-th"></span>
          <span>Product Stock Makassar</span>
        </strong>
      </div>
      <div class="panel-body">
        <table class="table table-bordered" id="datatable_makassar">
          <thead>
            <tr>
              <th>Part Number</th>
              <th>UoM</th>
              <th>Kategori</th>
              <th>Total Quantity</th>
              <th>Total Quantity Ongoing</th>
            </tr>
          </thead>
          <tbody>
            <?php foreach ($data_makassar as $row): ?>
              <tr>
                <td><?php echo $row['part_number']; ?></td>
                <td><?php echo $row['UoM']; ?></td>
                <td><?php echo $row['category_name']; ?></td>
                <td><?php echo $row['max_total_quantity']; ?></td>
                <td><?php echo $row['max_total_quantity_ongoing']; ?></td>
              </tr>
            <?php endforeach; ?>
          </tbody>
        </table>
      </div>
    </div>
  </div>
</div>



<!-- Tabel Semarang -->
<div class="row">
  <div class="col-md-12">
    <div class="panel panel-default">
      <div class="panel-heading clearfix">
        <strong>
          <span class="glyphicon glyphicon-th"></span>
          <span>Product Stock Semarang</span>
        </strong>
      </div>
      <div class="panel-body">
        <table class="table table-bordered" id="datatable_semarang">
          <thead>
            <tr>
              <th>Part Number</th>
              <th>UoM</th>
              <th>Kategori</th>
              <th>Total Quantity</th>
              <th>Total Quantity Ongoing</th>
            </tr>
          </thead>
          <tbody>
            <?php foreach ($data_semarang as $row): ?>
              <tr>
                <td><?php echo $row['part_number']; ?></td>
                <td><?php echo $row['UoM']; ?></td>
                <td><?php echo $row['category_name']; ?></td>
                <td><?php echo $row['max_total_quantity']; ?></td>
                <td><?php echo $row['max_total_quantity_ongoing']; ?></td>
              </tr>
            <?php endforeach; ?>
          </tbody>
        </table>
      </div>
    </div>
  </div>
</div>
<script>
  $.noConflict();
  jQuery(document).ready(function($) {
    // Initialize DataTable with options to disable sorting on the filter row
    $('#datatable_makassar').DataTable({
      "paging": true,
      "pageLength": 20, // Default rows per page
      "lengthMenu": [
        [10, 20, 100, 1000, -1],
        [10, 20, 100, 1000, "All"]
      ],
      "searching": true,
      "info": true
    });

    $('#datatable_jakarta').DataTable({
      "paging": true,
      "pageLength": 20, // Default rows per page
      "lengthMenu": [
        [10, 20, 100, 1000, -1],
        [10, 20, 100, 1000, "All"]
      ],
      "searching": true,
      "info": true
    });

    $('#datatable_bandung').DataTable({
      "paging": true,
      "pageLength": 20, // Default rows per page
      "lengthMenu": [
        [10, 20, 100, 1000, -1],
        [10, 20, 100, 1000, "All"]
      ],
      "searching": true,
      "info": true
    });

    $('#datatable_surabaya').DataTable({
      "paging": true,
      "pageLength": 20, // Default rows per page
      "lengthMenu": [
        [10, 20, 100, 1000, -1],
        [10, 20, 100, 1000, "All"]
      ],
      "searching": true,
      "info": true
    });

    $('#datatable_bali').DataTable({
      "paging": true,
      "pageLength": 20, // Default rows per page
      "lengthMenu": [
        [10, 20, 100, 1000, -1],
        [10, 20, 100, 1000, "All"]
      ],
      "searching": true,
      "info": true
    });

    $('#datatable_semarang').DataTable({
      "paging": true,
      "pageLength": 20, // Default rows per page
      "lengthMenu": [
        [10, 20, 100, 1000, -1],
        [10, 20, 100, 1000, "All"]
      ],
      "searching": true,
      "info": true
    });
  });

  document.getElementById('exportExcelBtn').addEventListener('click', function() {
    var wb = XLSX.utils.book_new(); // Membuat workbook baru      

    // Fungsi untuk menambahkan sheet dari data penjualan      
    function addSheetForLocation(sheetName, stokData, location) {
      var ws_data = [
        ['Part Number', 'UoM', 'Kategori', 'Qty']
      ];

      // Menambahkan data ke sheet      
      stokData.forEach(function(total) {
        ws_data.push([
          total.part_number,
          total.UoM,
          total.category_name,
          total.max_total_quantity
        ]);
      });

      var ws = XLSX.utils.aoa_to_sheet(ws_data); // Konversi data menjadi worksheet      
      XLSX.utils.book_append_sheet(wb, ws, sheetName); // Tambahkan sheet ke workbook      
    }

    // Data lokasi penjualan      
    var surabayaData = <?php echo json_encode($data_surabaya); ?>;
    var jakartaData = <?php echo json_encode($data_jakarta); ?>;
    var bandungData = <?php echo json_encode($data_bandung); ?>;
    var makassarData = <?php echo json_encode($data_makassar); ?>;
    var baliData = <?php echo json_encode($data_bali); ?>;
    var semarangData = <?php echo json_encode($data_semarang); ?>;

    // Tambahkan sheet untuk masing-masing lokasi      
    addSheetForLocation('Surabaya', surabayaData, 'surabaya');
    addSheetForLocation('Jakarta', jakartaData, 'jakarta');
    addSheetForLocation('Bandung', bandungData, 'bandung');
    addSheetForLocation('Makassar', makassarData, 'makassar');
    addSheetForLocation('Bali', baliData, 'bali');
    addSheetForLocation('Semarang', semarangData, 'semarang');

    // Simpan file Excel      
    XLSX.writeFile(wb, "StokReview.xlsx");
  });
</script>