<?php
session_start();

// Cek apakah user sudah login
if (!isset($_SESSION['loggedin']) || $_SESSION['loggedin'] !== true) {
  // Jika belum login, redirect ke halaman login
  header("Location: login.php");
  exit();
}

// require 'vendor/autoload.php';
require 'PhpSpreadsheet-1.10.1/src/Bootstrap.php';
include 'connection.php';

use Shuchkin\SimpleXLSXGen;

if (isset($_POST['startDate']) && isset($_POST['endDate'])) {
  $startDate = $_POST['startDate'];
  $endDate = $_POST['endDate'];

  // Sheet 1: serviceinformation data
  $headers1 = ['serviceinformation_id', 'time_input', 'yss_code', 'site_id', 'point_code', 'reg_date', 'order_type', 'plate_no', 'order_no', 'order_status', 'cancel_reason', 'invoice_no', 'rework_flag', 'customer_name', 'ktp', 'npwp_no', 'npwp_name', 'address', 'telephone_no', 'brand', 'model_category', 'model_name', 'model_code', 'frame_no', 'engine_no', 'milage', 'color', 'assembly_date', 'sold_date', 'sp7_kb', 'sp7_gbk', 'sp7_ksr', 'sp7_frddb', 'sp7_kom', 'sp7_fk', 'sp7_ptb', 'service_category', 'down_payment', 'total_labor_amount', 'total_part_amount', 'total_oil_amount', 'retail_parts_total', 'retail_oil_total', 'total_amount', 'walkin_time', 'registration_time', 'start_time', 'finish_time', 'invoice_time', 'total_waiting_time', 'total_suspend_time', 'actual_waiting_time', 'invoice_by', 'technician_name', 'pit', 'satisfaction', 'ksg_amount', 'claim_amount', 'cash_amount', 'debit_amount', 'epayment_type1', 'transaction_code1', 'epayment_amount1', 'epayment_type2', 'transaction_code2', 'epayment_amount2', 'epayment_type3', 'transaction_code3', 'epayment_amount3'];

  $data1 = [$headers1];
  $query1 = "SELECT * FROM `serviceinformation` WHERE `order_status` = 'ZZ. Completed' AND `invoice_time` BETWEEN '$startDate 00:00:00' AND '$endDate 23:59:59'";
  $result1 = $conn->query($query1);
  while ($data = $result1->fetch_assoc()) {
    $data1[] = array_values($data);
  }

  // Sheet 2: serviceinformation_job_detail data
  $headers2 = ['jdetail_si_id', 'serviceinformation_id', 'service_package', 'labor_cost_service'];
  $data2 = [$headers2];
  $query2 = "SELECT * FROM `serviceinformation_job_detail` WHERE `serviceinformation_id` IN (SELECT `serviceinformation_id` FROM `serviceinformation` WHERE `order_status` = 'ZZ. Completed' AND `invoice_time` BETWEEN '$startDate 00:00:00' AND '$endDate 23:59:59')";
  $result2 = $conn->query($query2);
  while ($data = $result2->fetch_assoc()) {
    $data2[] = array_values($data);
  }

  // Sheet 3: serviceinformation_parts_detail data
  $headers3 = ['pdetail_si_id', 'serviceinformation_id', 'service_package', 'parts_no', 'parts_name', 'qty', 'price', 'category'];
  $data3 = [$headers3];
  $query3 = "SELECT * FROM `serviceinformation_parts_detail` WHERE `serviceinformation_id` IN (SELECT `serviceinformation_id` FROM `serviceinformation` WHERE `order_status` = 'ZZ. Completed' AND `invoice_time` BETWEEN '$startDate 00:00:00' AND '$endDate 23:59:59')";
  $result3 = $conn->query($query3);
  while ($data = $result3->fetch_assoc()) {
    $data3[] = array_values($data);
  }

  // Membuat file Excel dengan beberapa sheet
  $xlsx = new SimpleXLSXGen();
  $xlsx->addSheet($data1, 'Serviceinformation')
    ->addSheet($data2, 'Job_Details')
    ->addSheet($data3, 'Parts_Details');

  $fileName = "ServiceInformation_" . $startDate . "_" . $endDate . ".xlsx";
  $xlsx->downloadAs($fileName);
  exit;
}

// use PhpOffice\PhpSpreadsheet\Spreadsheet;
// use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
// use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

// if (isset($_POST['startDate']) && isset($_POST['endDate'])) {
//   $startDate = $_POST['startDate'];
//   $endDate = $_POST['endDate'];

//   // Create new Spreadsheet object
//   $spreadsheet = new Spreadsheet();

//   // Sheet 1: serviceinformation data
//   $sheet1 = $spreadsheet->setActiveSheetIndex(0);
//   $sheet1->setTitle('Serviceinformation');
//   $query1 = "SELECT * FROM `serviceinformation` WHERE `order_status` = 'ZZ. Completed' AND `invoice_time` BETWEEN '$startDate 00:00:00' AND '$endDate 23:59:59'";
//   $result1 = $conn->query($query1);

//   // Set column headers for serviceinformation sheet
//   $headers1 = array('serviceinformation_id', 'time_input', 'yss_code', 'site_id', 'point_code', 'reg_date', 'order_type', 'plate_no', 'order_no', 'order_status', 'cancel_reason', 'invoice_no', 'rework_flag', 'customer_name', 'ktp', 'npwp_no', 'npwp_name', 'address', 'telephone_no', 'brand', 'model_category', 'model_name', 'model_code', 'frame_no', 'engine_no', 'milage', 'color', 'assembly_date', 'sold_date', 'sp7_kb', 'sp7_gbk', 'sp7_ksr', 'sp7_frddb', 'sp7_kom', 'sp7_fk', 'sp7_ptb', 'service_category', 'down_payment', 'total_labor_amount', 'total_part_amount', 'total_oil_amount', 'retail_parts_total', 'retail_oil_total', 'total_amount', 'walkin_time', 'registration_time', 'start_time', 'finish_time', 'invoice_time', 'total_waiting_time', 'total_suspend_time', 'actual_waiting_time', 'invoice_by', 'technician_name', 'pit', 'satisfaction', 'ksg_amount', 'claim_amount', 'cash_amount', 'debit_amount', 'epayment_type1', 'transaction_code1', 'epayment_amount1', 'epayment_type2', 'transaction_code2', 'epayment_amount2', 'epayment_type3', 'transaction_code3', 'epayment_amount3');
//   $sheet1->fromArray($headers1, NULL, 'A1');

//   // Apply bold font to header row
//   $lastColumn = Coordinate::stringFromColumnIndex(count($headers1)); // Calculate the last column
//   $sheet1->getStyle("A1:{$lastColumn}1")->getFont()->setBold(true);

//   // Fill data for serviceinformation sheet
//   $row = 2;
//   while ($data = $result1->fetch_assoc()) {
//     $sheet1->fromArray(array_values($data), NULL, 'A' . $row++);
//   }

//   // Sheet 2: serviceinformation_job_detail data
//   $sheet2 = $spreadsheet->createSheet();
//   $sheet2->setTitle('job_details');
//   $query2 = "SELECT * FROM `serviceinformation_job_detail` WHERE `serviceinformation_id` IN (SELECT `serviceinformation_id` FROM `serviceinformation` WHERE `order_status` = 'ZZ. Completed' AND `invoice_time` BETWEEN '$startDate 00:00:00' AND '$endDate 23:59:59')";
//   $result2 = $conn->query($query2);

//   // Set column headers for job details sheet
//   $headers2 = array('jdetail_si_id', 'serviceinformation_id', 'service_package', 'labor_cost_service');
//   $sheet2->fromArray($headers2, NULL, 'A1');

//   // Apply bold font to header row
//   $lastColumn2 = Coordinate::stringFromColumnIndex(count($headers2)); // Calculate the last column
//   $sheet2->getStyle("A1:{$lastColumn2}1")->getFont()->setBold(true);

//   // Fill data for job details sheet
//   $row = 2;
//   while ($data = $result2->fetch_assoc()) {
//     $sheet2->fromArray(array_values($data), NULL, 'A' . $row++);
//   }

//   // Sheet 3: serviceinformation_parts_detail data
//   $sheet3 = $spreadsheet->createSheet();
//   $sheet3->setTitle('parts_details');
//   $query3 = "SELECT * FROM `serviceinformation_parts_detail` WHERE `serviceinformation_id` IN (SELECT `serviceinformation_id` FROM `serviceinformation` WHERE `order_status` = 'ZZ. Completed' AND `invoice_time` BETWEEN '$startDate 00:00:00' AND '$endDate 23:59:59')";
//   $result3 = $conn->query($query3);

//   // Set column headers for parts details sheet
//   $headers3 = array('pdetail_si_id', 'serviceinformation_id', 'service_package', 'parts_no', 'parts_name', 'qty', 'price', 'category');
//   $sheet3->fromArray($headers3, NULL, 'A1');

//   // Apply bold font to header row
//   $lastColumn3 = Coordinate::stringFromColumnIndex(count($headers3)); // Calculate the last column
//   $sheet3->getStyle("A1:{$lastColumn3}1")->getFont()->setBold(true);

//   // Fill data for parts details sheet
//   $row = 2;
//   while ($data = $result3->fetch_assoc()) {
//     $sheet3->fromArray(array_values($data), NULL, 'A' . $row++);
//   }

//   // Redirect to download file
//   $fileName = "ServiceInformation_" . $startDate . "_" . $endDate . ".xlsx";
//   header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
//   header("Content-Disposition: attachment; filename=\"$fileName\"");
//   $writer = new Xlsx($spreadsheet);
//   $writer->save("php://output");
//   exit;
// }

// Mendapatkan data target_service1 untuk isi
$target_service1 = "SELECT * FROM target_service1";
$target_result = $conn->query($target_service1);

// Mendapatkan data claim untuk isi
$claim = "SELECT * FROM claim";
$result_claim = $conn->query($claim);

// Mendapatkan data dealer untuk dropdown
$dealer_query = "SELECT name_dealer, code_dealer FROM dealer";
$dealer_result = $conn->query($dealer_query);



?>

<!DOCTYPE html>
<html lang="en">

<head>
  <meta charset="utf-8">
  <meta content="width=device-width, initial-scale=1.0" name="viewport">
  <title>Query Master Service | Target</title>
  <meta content="" name="description">
  <meta content="" name="keywords">

  <!-- Bootstrap CSS -->
  <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha3/dist/css/bootstrap.min.css" rel="stylesheet">

  <!-- Select2 Bootstrap Theme -->
  <link href="https://cdn.jsdelivr.net/npm/select2-bootstrap-5-theme@1.3.0/dist/select2-bootstrap-5-theme.min.css" rel="stylesheet" />

  <!-- DataTables CSS -->
  <link rel="stylesheet" href="https://cdn.datatables.net/1.13.6/css/dataTables.bootstrap5.min.css">

  <!-- Google Fonts -->
  <link href="https://fonts.gstatic.com" rel="preconnect">
  <link href="https://fonts.googleapis.com/css?family=Open+Sans:300,300i,400,400i,600,600i,700,700i|Nunito:300,300i,400,400i,600,600i,700,700i|Poppins:300,300i,400,400i,500,500i,600,600i,700,700i" rel="stylesheet">

  <!-- Vendor CSS Files -->
  <link href="assets/vendor/bootstrap-icons/bootstrap-icons.css" rel="stylesheet">
  <link href="assets/vendor/boxicons/css/boxicons.min.css" rel="stylesheet">
  <link href="assets/vendor/quill/quill.snow.css" rel="stylesheet">
  <link href="assets/vendor/quill/quill.bubble.css" rel="stylesheet">
  <link href="assets/vendor/remixicon/remixicon.css" rel="stylesheet">
  <link href="assets/vendor/simple-datatables/style.css" rel="stylesheet">

  <!-- Template Main CSS File -->
  <link href="assets/css/style.css" rel="stylesheet">

  <!-- Favicons -->
  <link href="assets/img/favicon.png" rel="icon">
  <link href="assets/img/apple-touch-icon.png" rel="apple-touch-icon">

  <!-- UNICONS -->
  <link rel="stylesheet" href="https://unicons.iconscout.com/release/v4.0.8/css/line.css">

  <style>
    thead th {
      border: 1px solid rgb(230, 230, 230) !important;
    }

    table {
      overflow-y: auto;
      overflow-x: auto !important;
      min-height: 40px;
    }

    .top {
      display: flex;
      justify-content: space-between;
      align-items: center;
    }

    .bottom {
      display: flex;
      justify-content: space-between;
      align-items: center;
    }
  </style>
</head>


<body>

  <?php
  include 'header.php';
  ?>

  <?php
  $activeMenu = 'export_api'; // Sesuaikan dengan halaman yang sedang dibuka
  include 'sidebar.php';
  ?>


  <main id="main" class="main">

    <div class="pagetitle">
      <h1>Export API</h1>
      <nav>
        <ol class="breadcrumb">
          <li class="breadcrumb-item"><a href="index.html">Home</a></li>
          <li class="breadcrumb-item active">Export API</li>
        </ol>
      </nav>
    </div><!-- End Page Title -->

    <section class="section dashboard">

      <div class="card">
        <div class="card-body">

          <h5 class="card-title">Data Service</h5>
          <p>Silahkan filter tanggal yang ingin anda Download</p>
          <div class="alert alert-light text-center mt-4 mb-1" role="alert">
            <form method="post" class="row g-3 justify-content-center mt-2 mb-3 align-items-end">
              <div class="col-md-4">
                <label for="startDate" class="form-label">Tanggal Mulai</label>
                <input type="date" name="startDate" id="startDate" class="form-control" required>
              </div>
              <div class="col-md-4">
                <label for="endDate" class="form-label">Tanggal Akhir</label>
                <input type="date" name="endDate" id="endDate" class="form-control" required>
              </div>
              <div class="col-md-2">
                <button type="submit" class="btn btn-success w-100"><i class="uil uil-file-download"></i>Download Excel</button>
              </div>
            </form>
          </div>

          <!-- <div class="alert alert-warning text-center mt-4 mb-1" role="alert">
                <h3 class="card-title mt-2 mb-2">Silahkan pilih Tanggal Mulai dan Tanggal Akhir</h3>
              </div> -->
          <!-- <div class="alert alert-secondary text-center mt-4 mb-1" role="alert">
                <div class="spinner-border text-success mt-3 mb-3" role="status">
                  <span class="visually">Loading...</span>
                </div>
              </div> -->
          <!-- <div class="alert alert-light text-center mt-4 mb-1" role="alert">
                <div class="d-grid gap-2 mt-3 mb-3">
                  <button class="btn btn-success" type="button"><i class="uil uil-file-download"></i>Download Excel</button>
                </div>
              </div> -->
          <!-- Default Tabs -->
          <!-- <ul class="nav mt-4 nav-tabs d-flex" id="myTabjustified" role="tablist">

            <li class="nav-item flex-fill" role="presentation">
              <button class="nav-link w-100 active" id="data-claim-tab" data-bs-toggle="tab" data-bs-target="#data-claim-justified" type="button" role="tab" aria-controls="data-claim" aria-selected="false">API Service</button>
            </li>
            <li class="nav-item flex-fill" role="presentation">
              <button class="nav-link w-100" id="input-claim-tab" data-bs-toggle="tab" data-bs-target="#input-claim-justified" type="button" role="tab" aria-controls="input-claim" aria-selected="true">API Part</button>
            </li>
          </ul>
          <div class="tab-content pt-2" id="myTabjustifiedContent">

            <div class="tab-pane fade show active" id="data-claim-justified" role="tabpanel" aria-labelledby="data-claim-tab">
              <h5 class="card-title">Data Service</h5>
              <p>Silahkan filter tanggal yang ingin anda export</p>
              <form method="post" class="row g-3 justify-content-center mb-5 align-items-end">
                <div class="col-md-4">
                  <label for="startDate" class="form-label">Tanggal Mulai</label>
                  <input type="date" name="startDate" id="startDate" class="form-control" required>
                </div>
                <div class="col-md-4">
                  <label for="endDate" class="form-label">Tanggal Akhir</label>
                  <input type="date" name="endDate" id="endDate" class="form-control" required>
                </div>
                <div class="col-md-3">
                  <button type="submit" class="btn btn-success w-100"><i class="uil uil-file-download"></i>Download Excel</button>
                </div>
                <div class="col-md-1">
                  <button type="reset" class="btn btn-secondary w-100"><i class="uil uil-repeat"></i></button>
                </div>
              </form>

              <div class="alert alert-warning text-center mt-4 mb-1" role="alert">
                <h3 class="card-title mt-2 mb-2">Silahkan pilih Tanggal Mulai dan Tanggal Akhir</h3>
              </div>
              <div class="alert alert-secondary text-center mt-4 mb-1" role="alert">
                <div class="spinner-border text-success mt-3 mb-3" role="status">
                  <span class="visually">Loading...</span>
                </div>
              </div>
              <div class="alert alert-light text-center mt-4 mb-1" role="alert">
                <div class="d-grid gap-2 mt-3 mb-3">
                  <button class="btn btn-success" type="button"><i class="uil uil-file-download"></i>Download Excel</button>
                </div>
              </div>

            </div>
            <div class="tab-pane fade" id="input-claim-justified" role="tabpanel" aria-labelledby="input-claim-tab">
              <h5 class="card-title">Data Part</h5>

              
              <form method="POST" onsubmit="return confirmSubmit()">
                <div class="row mb-3">
                  <label class="col-sm-2 col-form-label">Dealer</label>
                  <div class="col-sm-10">
                    <select name="dealer_code" id="dealer" class="form-control select2" required>
                      <option value="" disabled selected hidden>Pilih Dealer</option>
                      <?php
                      if ($dealer_result->num_rows > 0) {
                        while ($dealer = $dealer_result->fetch_assoc()) {
                      ?>
                          <option value="<?php echo $dealer['code_dealer']; ?>"
                            data-name="<?php echo htmlspecialchars($dealer['name_dealer']); ?>">
                            <?php echo htmlspecialchars($dealer['name_dealer']); ?>
                          </option>
                      <?php
                        }
                      }
                      ?>
                    </select>
                  </div>
                </div>

                <div class="col-12 d-flex justify-content-start">
                  <form action="claim_tambah.php" method="GET">
                    <input type="hidden" name="dealer_code" value="<?php echo $target['site_id']; ?>">
                    <input type="hidden" name="name_dealer" value="<?php echo $target['name_dealer']; ?>">
                    <button type="submit" class="btn btn-primary mt-3 me-2">Tambah Data</button>
                  </form>
                </div>
              </form>
            </div>

          </div> -->
          <!-- End Default Tabs -->


        </div>
      </div>

    </section>

  </main><!-- End #main -->


  <!-- ======= Footer ======= -->
  <footer id="footer" class="footer">
    <div class="copyright">
      IT Lautan Teduh Interniaga <strong><span>&copy; 2024</span></strong>
    </div>
    <div class="credits">
      Created by <a href="https://github.com/Rizky-Boolean/"><strong>Fadhil IT</strong></a><strong></strong>
    </div>
  </footer><!-- End Footer -->

  <a href="#" class="back-to-top d-flex align-items-center justify-content-center"><i class="bi bi-arrow-up-short"></i></a>

  <!-- Vendor JS Files -->
  <script src="assets/vendor/apexcharts/apexcharts.min.js"></script>
  <script src="assets/vendor/bootstrap/js/bootstrap.bundle.min.js"></script>
  <script src="assets/vendor/chart.js/chart.umd.js"></script>
  <script src="assets/vendor/echarts/echarts.min.js"></script>
  <script src="assets/vendor/quill/quill.js"></script>
  <script src="assets/vendor/simple-datatables/simple-datatables.js"></script>
  <script src="assets/vendor/tinymce/tinymce.min.js"></script>
  <script src="assets/vendor/php-email-form/validate.js"></script>

  <!-- jQuery (harus sebelum DataTables) -->
  <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>

  <!-- DataTables JS -->
  <script src="https://cdn.datatables.net/1.13.6/js/jquery.dataTables.min.js"></script>
  <script src="https://cdn.datatables.net/1.13.6/js/dataTables.bootstrap5.min.js"></script>



  <!-- Main JS File -->
  <script src="assets/js/main.js"></script>

  <script>
    $(document).ready(function() {
      // Initialize select2 for dealer dropdown
      $('#dealer').select2({
        theme: 'bootstrap-5',
        placeholder: "Pilih Dealer",
        allowClear: true
      });

      // Event listener ketika dealer dipilih
      $('#dealer').on('change', function() {
        var selectedDealer = $(this).find('option:selected');
        var dealerCode = selectedDealer.val();
        var dealerName = selectedDealer.data('name');

        // Set value ke input site_id dan name_dealer
        $('#site_id').val(dealerCode);
        $('#name_dealer').val(dealerName);
      });
    });

    $(document).ready(function() {
      $('#targetTable').DataTable({
        "lengthMenu": [
          [10, 25, 50, -1],
          [10, 25, 50, "All"]
        ], // Mengubah teks lengthMenu
        "paging": true, // Enable pagination
        "searching": true, // Enable searching
        "ordering": true, // Enable column ordering
        "language": {
          "info": "Menampilkan _START_ sampai _END_ dari _TOTAL_ entri",
          "infoEmpty": "Menampilkan 0 sampai 0 dari 0 entri",
          "search": "Cari:", // Mengubah teks pencarian
          "lengthMenu": "Tampilkan _MENU_ data",
          "paginate": {
            "previous": "<span aria-hidden='true'>&laquo;</span>",
            "next": "<span aria-hidden='true'>&raquo;</span>"
          }
        },
        "pagingType": "full_numbers", // Gaya pagination full dengan Bootstrap
        "dom": '<"top"lf>rt<"bottom"ip><"clear">' // Menempatkan lengthMenu (l) dan searching (f) di atas
      });
    });
  </script>


</body>

</html>