<?php

error_reporting(E_ALL^(E_NOTICE|E_WARNING));
ini_set('display_errors',1);
error_reporting(0);
?>

<!-- Content Header (Page header) -->
<div class="content-header">
  <div class="container-fluid">
    <div class="row mb-1">
      <div class="col-sm-12">
        <!-- <h1 class="m-0 text-dark">Laporan Neraca []</h1> -->
      </div><!-- /.col -->
    </div><!-- /.row -->
  </div><!-- /.container-fluid -->
</div>
<!-- /.content-header -->

<!-- Main content -->
    <section class="content">
      <div class="container-fluid">
        <div class="row">
          <div class="col-15">
            <!-- Default box -->
            <div class="card">
              <div class="card-header bg-info">
                <h3 class="card-title ">Laporan Tunggakan</h3>
                <div class="card-tools">
                  <button type="button" class="btn btn-tool" data-card-widget="collapse" title="Collapse">
                    <i class="fas fa-minus"></i>
                  </button>
                  <button type="button" class="btn btn-tool" data-card-widget="remove" title="Remove">
                    <i class="fas fa-times"></i>
                  </button>
                </div>
              </div>

              <!-- /.card-header -->
        <div class="card-body">
          <?php
          $kode  = $_SESSION['kode'];
            if(isset($_POST['par2'])){
            $par2=date('Y-m-d',strtotime($_POST['par2']));
            if($_POST['par2']=='')
              $where="where  b.tgl<='".$par2."' AND a.kode='$kode' group by b.rekening having bakidebet<>0 ";
            
            else
              $where="where b.tgl<='$par2' AND a.kode='$kode' group by b.rekening having bakidebet<>0 ";
            
          }else{
            
            $par2="";

            $where="where b.tgl='$par2' ";
           
          }
          ?>
          <form role="form" id="quickForm" action="home.php?home=<?php echo md5("tunggakan");?>" method="post">
            <div class="row">
              <div class="col-sm-10">
            <div class="form-group row">
              <!-- <label for="nama" class="col-2 m-3">Cari Tunggakan</label>
              <input type="text" name="t_rekening" id="t_rekening"  class="form-control col-sm-2" placeholder="ketik Nama/Alamat/Rekening"> -->
              <label  for="nama" class="col-2 m-2">Tanggal</label>
              <!-- <input type="date" name="par1" id="par1" class="form-control col-2" value="<?=@$par1?>"> -->
              <input type="date" name="par2" id="par2" class="form-control col-2" value="<?=@$par2?>"><!-- required="" -->
              <!-- <div class="form-group row"> -->

                <button type="submit" class="  btn btn-info"  ><i class="fas fa-search"></i></button>
                </div>
              </div>
                <?php if(isset($_POST['par2'])){ ?>

                  <div class="form-group col-2">
                  
                  <!-- <div class="col-2 m-2"> -->
                  <?php
                  $_SESSION['tuggakan']['periode2'] = date('d-m-Y',strtotime($_POST['par2']));
                              
                ?>
                <a href="pdf/nunggak_angsuranpdf.php" target="_blank" style="float: right;margin-top: 10px;" class="btn btn-success"><i class="fa fa-print"></i> PDF</a>
              </div>
            </div>
                <?php } ?>
               
              
                     
          </form>                   
          <hr>

          <!-- <table class="table table-bordered table-hover" width="148%"> -->
            <table id="sortTable" width="100%" class="table table-bordered table-hover">
            <thead>
              <tr align="center">
                <th >No</th>
                <th >Tanggal</th>
                <th >Rekening</th>
                <th >Nama</th>
                <!-- <th >Alamat</th> -->
                <th >Plafond</th>
                <th >Ujr</th>
                <th >Bakidebet</th>
                <th >Barang</th>
                <th >Jaminan</th>
                <th >T Pokok</th>
                <th >T Ujroh</th>
                <th >Jml</th>
                <th >Kol</th>
              </tr>
            </thead>
            <tbody>
              <?php
              if(isset($_POST['par2'])){
              include 'koneksi.php';

              function kol($kolbln){
                    if($kolbln<="15"){
                     return 0;
                    }elseif($kolbln<="30"){
                    return 1;
                    }elseif($kolbln<="45"){
                        return 2;
                    }else{return 3;
                    } 
                    }

              $queryt = "SELECT a.plafond*a.margin/100*a.jangkawaktu-SUM(b.margin) as k_margin, SUM(b.pokok) as tpokok,sum(b.margin) as tmargin,c.nama as nama,c.alamat as alamat, a.rekening AS rekening, a.plafond AS plafond,a.plafond-sum(b.pokok)as bakidebet,a.margin AS margin,a.jangkawaktu AS jangkawaktu,a.tgl AS tgl,a.barang as barang,a.jaminan AS jaminan FROM pinjaman a LEFT JOIN angsuran b ON a.rekening=b.rekening LEFT JOIN registrasi c ON a.register=c.id $where";
                $querya       =mysqli_query($koneksi,$queryt);
                if(mysqli_num_rows($querya)){
                  $_SESSION['tuggakan']['kode']['sqlt']=$queryt;
                $no=0;
                
                while ($data = mysqli_fetch_assoc($querya)) {
                  extract($data);
                  //flast
                    $jumlah_pinjaman = $plafond;
                    $lama_pinjaman   = $jangkawaktu;
                    $margin_perbulan  = $margin;
                    $tgl  = $tgl;
                    $angsuran_margin=$jumlah_pinjaman*$margin_perbulan/100;
                    $angsuran_pokok = $jumlah_pinjaman/$lama_pinjaman;
                    $jumlahangsuran = $angsuran_pokok+$angsuran_margin;
                  $tpokok2=0;
                  $tmargin2=0;
                  $tot2=0;
                  $tpokok2+=$angsuran_pokok;
                  $tmargin2+=$angsuran_margin;
                  $tot2+=$jumlahangsuran;
                  $date2 = $par2;
                
                $timeStart2 = new DateTime($tgl);
                $timeEnd2 = new DateTime($date2);
                $hitung = $timeStart2->diff($timeEnd2);
                $hasil= $hitung->format('%a');
                /*https://www.belajarwebpedia.com/2019/11/cara-menghitung-selisih-tanggal-dengan-php.html#:~:text=%3C%3Fphp%20%24tanggal1%20%3D%20new%20DateTime%20%28%272019-11-11%27%29%3B%20%24tanggal2%20%3D,berapa%20hari%20dari%20kedua%20tanggal%20tersebut.%20%2A%2F%20%3F%3E*/
                 /*Flat*/        
                 
                 $flat_t_margin=$angsuran_margin*kol($hasil)-$tmargin;
                 $flat_t_pokok=$angsuran_pokok*kol($hasil)-$tpokok;
                 $tot_tunggakan= $flat_t_margin+$flat_t_pokok;

                 $gab_plafond += $plafond;
                 $gab_bakidebet += $bakidebet;
                 $gab_margin +=$angsuran_margin*kol($hasil)-$tmargin;
                 $gab_pokok += $angsuran_pokok*kol($hasil)-$tpokok;
                 $gab_total += $flat_t_margin+$flat_t_pokok;

                 $no++;
                  ?>                                  
                  <tr>
                    <td align="center"><?php echo $no; ?></td>
                    <!-- <td><?php echo date('d-m-Y',strtotime($tanggal)); ?></td> -->
                    <td><?php echo date('d-m-Y',strtotime($tgl)); ?></td>
                    <td><?php echo $rekening; ?></td>
                    <td><?php echo $nama; ?></td>
                    <!-- <td><?php echo $alamat ?></td> -->
                   
                    <td align="right"><?php echo number_format($plafond); ?></td>
                   <td><?php echo $margin; ?></td>
                  <!--   <td><?php echo $jangkawaktu; ?></td> -->
                    
                     <td align="right"><?php echo number_format($bakidebet,0); ?></td>
                     <td align="right"><?php echo $barang; ?></td>
                     <td align="left"><?php echo $jaminan; ?></td>
                    <td align="left"><?php echo number_format($flat_t_pokok,0); ?></td>
                    <td align="right"><?php echo number_format($flat_t_margin,0); ?></td>
                    <td align="right"><?php echo number_format($tot_tunggakan,0); ?></td>
                      <td align="right"><?php if($flat_t_margin<=0){ echo "L";}else{
                      if($hasil<=30) echo "D";elseif($hasil<=45)echo "D";else echo "M";} ?></td>                    
                  </tr>
                  <?php }}?>
                  
                  
              </tbody>
              <tr>
                    <td colspan="4"><b>Total</b></td>
                    <td><b><?php echo number_format($gab_plafond,0); ?></b></td>
                    <td></td>
                    <td><b><?php echo number_format($gab_bakidebet,0); ?></b></td>
                    <td></td>
                    <td><b><?php echo number_format($gab_margin,0); ?></b></td>
                    <td><b><?php echo number_format($gab_pokok,0); ?></b></td>
                    <td><b><?php echo number_format($gab_total,0); ?></b></td>
                  </tr> 
              </table>
              
            <?php }?>
             
            <script type="text/javascript" charset="utf8" src="./assets/jquery.dataTables.js"></script>
              <!-- <script type="text/javascript" charset="utf8" src="./assets/jquery-1.11.0.min"></script> -->
              <link rel="stylesheet" type="text/css" href="./assets/jquery.dataTables.css">
                  <script>
              $('#sortTable').DataTable(); //https://www.sahretech.com/2020/04/cara-membuat-pencarian-sorting-dan.html?m=1/ cara buat sorter table
              </script>
              
            </div>
            <!-- /.card-body -->
          </div>
          <!-- /.card -->
        </div>
        <!-- /.col -->
      </div>

      <!-- /.row -->
    </section>
    <!-- /.content -->


