<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-1BmE4kWBq78iYhFldvKuhfTAU6auU8tT94WrHftjDbrCEXSU1oBoqyl2QvZ6jIW3" crossorigin="anonymous">
<?php 
    include 'header.php'; 
?>            
<div class="container">

  <div class="my-4 border-bottom">
    <h4 class="fw-semibold fs-4">Laporan Penarikan Donasi 
    </h4>  
  </div>
  <section class="content">
    <div class="row">
      <section class="col-lg-14">
        <div class="box box-info">
          <div class="box-header">
          <div class="box-body">
            <form method="get" action="">
              <div class="row">
                <div class="col-md-2">
                  <div class="form-group">
                    <label>Dari</label>
                    <input type="date" class="form-control" name="tanggal_d" required="required" placeholder="Tanggal dari ..">
                  </div>
                </div>

                <div class="col-md-2">
                  <div class="form-group">
                    <label>Sampai</label>
                    <input type="date" class="form-control" name="tanggal_s" required="required" placeholder="Sampai tanggal ..">
                  </div>
                </div>

                <div class="col-md-2">
                  <div class="form-group">
                    <br/>
                    <input type="submit" value="TAMPILKAN LAPORAN" class="btn btn-sm btn-primary">
                  </div>
                </div>
              </div>
            </form>
          </div>
        </div>
        
        <div class="box box-info">
          <div class="box-header">
            <h6 class="box-title"></h6>
          </div>
          <div class="box-body">
            <?php 
            if(isset($_GET['tanggal_s']) && isset($_GET['tanggal_d'])){
              $tgl_d = $_GET['tanggal_d'];
              $tgl_s = $_GET['tanggal_s'];
              
              $tgl_pertama    =strtotime("$tgl_d");
              $tgl_kemarin    =date('Y-m-d', strtotime("-1 day", $tgl_pertama));       
            } else { ;
                $tgl_d    = '2024-01-01';
                $tgl_s    = $_SESSION['tgl_sd'];
            }
            $nosub='1101';
                $sql1 = mysqli_query($koneksi, "SELECT sum(awald) as tn_awal FROM nawal WHERE left(noperk,4)='$nosub'");
                    list($tn_awal) = mysqli_fetch_array($sql1);
                    
                $sql2 = mysqli_query($koneksi, "SELECT sum(mutasid-mutasik) as tj_awal FROM jor WHERE left(noperk,4)='$nosub' and tgljornal<='$tgl_d' ");
                    list($tj_awal) = mysqli_fetch_array($sql2);                    
                
                $tsaldo_awal=$tn_awal+$tj_awal;

$tanggal_awal = $tgl_d;
$tanggal_akhir = $tgl_s;
                
              ?>

              <div class="row">
                <div class="col-lg-6">
                  <table class="table table-bordered">
                    <tr>
                      <th width="30%">DARI TANGGAL</th>
                      <th width="1%">:</th>
                      <td><?php echo date('d-m-Y', strtotime($tgl_d)); ?></td>
                    </tr>
                    <tr>
                      <th>SAMPAI TANGGAL</th>
                      <th>:</th>
                      <td><?php echo date('d-m-Y', strtotime($tgl_s)); ?></td>
                    </tr>
                    <tr>
                      <th>Lanjutan </th>
                      <th>:</th>
                      <td>
<!--                          
                          <a href="lap_a_kas_tgl_sd_tgl_lihat_unit.php?xunit=01" class="btn btn-success btn-sm"><i class="bi bi-eyeglasses"></i> &nbsp KAS ZAKAT</a>
                          <a href="lap_a_kas_tgl_sd_tgl_lihat_unit.php?xunit=02" class="btn btn-success btn-sm"><i class="bi bi-eyeglasses"></i> &nbsp KAS NON ZAKAT</a>
                          <a href="index.php" class="btn btn-info btn-sm pull-right"><i class="bi bi-reply"></i> &nbsp; Kembali</a>
-->
                       </td>
                    </tr>                    
                  </table>
                </div>
              </div>

              <div class="table-responsive">
                <table class="table table-bordered table-striped" id="table-datatable">

  <thead>
    <tr>
      <th style="border-top: 2px solid black; border-bottom: 1px solid black; border-right: 0px solid white; text-align:center" colspan="3" bgcolor="#B0C4DE">Keterangan</th>
      <th style="border-top: 2px solid black; border-bottom: 1px solid black; border-right: 0px solid white; text-align:center" colspan="1" bgcolor="#B0C4DE">Jumlah Semua</th>
      <th style="border-top: 2px solid black; border-bottom: 1px solid black; border-right: 0px solid white; text-align:center" colspan="1" bgcolor="#B0C4DE">Belum Dibukukan</th>
      <th style="border-top: 2px solid black; border-bottom: 1px solid black; border-right: 0px solid white; text-align:center" colspan="1" bgcolor="#B0C4DE">Opsi</th>
    </tr>
  </thead>
  <tbody>
<?php
$no = 1; 
// Koneksi ke database
$conn = new mysqli($host, $username, $password, $database);

// Cek koneksi
if ($conn->connect_error) {
    die("Koneksi gagal: " . $conn->connect_error);
}

// Variabel input
$tanggal_awal = $tgl_d;
$tanggal_akhir = $tgl_s;

    // Query untuk mengambil data karyawan
    $query_karyawan = "SELECT * FROM karyawan WHERE jabatan = 'LAPANGAN'";
    $result_karyawan = $conn->query($query_karyawan);
    // Loop data karyawan
    $no = 1;
    while ($row_karyawan = $result_karyawan->fetch_assoc()) {
      ?>
      <tr>
        <td colspan="6"><?php echo $no . ". NAMA : " . $row_karyawan['nama'] . " - JABATAN : " . $row_karyawan['jabatan'] ." (Id : ". $row_karyawan['id'] . ")"; ?></td>
      </tr>
      <?php
      // Query untuk mengambil data nawal
      $query_nawal = "SELECT * FROM nawal WHERE kt = 'D'";
      $result_nawal = $conn->query($query_nawal);
      // Loop data nawal
      while ($row_nawal = $result_nawal->fetch_assoc()) {
        $jumlah = 0;
        // Query untuk mengambil data anggota_trans_ao
        $query_trans = "SELECT SUM(masuk) AS jumlah FROM anggota_trans_ao WHERE kdj = '" . $row_nawal['noperk'] . "' AND ao = '" . $row_karyawan['id'] . "' AND tanggal BETWEEN '$tanggal_awal' AND '$tanggal_akhir'AND hapus<>'HAPUS' ";
        $result_trans = $conn->query($query_trans);
        $row_trans = $result_trans->fetch_assoc();
        $jumlah = $row_trans['jumlah'];
        $total_per_nama += $jumlah;
        
        $jumlah_belum = 0;
        // Query untuk mengambil data anggota_trans_ao
        $query_trans = "SELECT SUM(masuk) AS jumlah_belum FROM anggota_trans_ao WHERE kdj = '" . $row_nawal['noperk'] . "' AND ao = '" . $row_karyawan['id'] . "' AND tanggal BETWEEN '$tanggal_awal' AND '$tanggal_akhir' AND notrans=0 AND hapus<>'HAPUS' ";
        $result_trans = $conn->query($query_trans);
        $row_trans = $result_trans->fetch_assoc();
        $jumlah_belum = $row_trans['jumlah_belum'];
        $total_per_nama_belum += $jumlah_belum;  
        
        $xid= $row_karyawan['id'] . $row_nawal['noperk'];
        ?>
        <tr>
          <td colspan="3">&nbsp;&nbsp;&nbsp;&nbsp;~~ <?php echo $row_nawal['noperk']; ?> - <?php echo $row_nawal['perk']; ?></td>
          <td style="text-align:right; font-size: 12pt; font-weight:normal;"><?php echo "" . number_format("$jumlah", 0, ",", "."); ?></td>
          <td style="text-align:right; font-size: 12pt; font-weight:normal;"><?php echo "" . number_format("$jumlah_belum", 0, ",", "."); ?></td>
          <td>
            <a data-toggle='tooltip' data-placement='top' title='Buka Data <?php echo $row_nawal['perk'] ?> a/n <?php echo $row_karyawan['nama'] ; ?>' style='margin-right:3px' class="btn btn-warning btn-sm" href="tugas_lap_trans_tgl_sd_tgl_per_ao.php?ao=<?php echo $row_karyawan['id'] ?>&kdj=<?php echo $row_nawal['noperk'] ?>&tgl_d=<?php echo $tgl_d ?>&tgl_s=<?php echo $tgl_s ?>" ><i class="bi bi-eye"></i></a>
          </td>
        </tr>
        <?php
      }
      ?>
      <tr>
        <td style="border-top: 2px solid black; border-bottom: 1px solid black;font-size: 14pt; " colspan="3">&nbsp;&nbsp;&nbsp;&nbsp;Total <?php echo $row_karyawan['nama']; ?> : </td>
        <td style="border-top: 2px solid black; border-bottom: 1px solid black; text-align:right; font-size: 14pt; font-weight:normal;"><?php echo number_format($total_per_nama, 0, ',', '.'); ?></td>
        <td style="border-top: 2px solid black; border-bottom: 1px solid black; text-align:right; font-size: 14pt; font-weight:normal;"><?php echo number_format($total_per_nama_belum, 0, ',', '.'); ?></td>
        <td style="border-top: 2px solid black; border-bottom: 1px solid black; text-align:right; font-size: 14pt; font-weight:bold;" colspan="1"></td>
      </tr>
      <?php
      $total_seluruhnya += $total_per_nama;
      $total_seluruhnya_belum += $total_per_nama_belum;
      $no++;
    }
    ?>
    <tr>
      <td style="border-top: 2px solid black; border-bottom: 1px solid black; text-align:right; font-size: 14pt; font-weight:bold;" colspan="3">Total Seluruhnya</td>
      <td style="border-top: 2px solid black; border-bottom: 1px solid black; text-align:right; font-size: 14pt; font-weight:bold;"><?php echo number_format($total_seluruhnya, 0, ',', '.'); ?></td>
      <td style="border-top: 2px solid black; border-bottom: 1px solid black; text-align:right; font-size: 14pt; font-weight:bold;"><?php echo number_format($total_seluruhnya_belum, 0, ',', '.'); ?></td>
      <td style="border-top: 2px solid black; border-bottom: 1px solid black; text-align:right; font-size: 14pt; font-weight:bold;" colspan="1"></td>
    </tr>
    
    




    <?php
    // Tutup koneksi
    //$conn->close();
    ?>
  </tbody>
</table>
