<?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 Kolektibilitas</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     

          if(isset($_POST['par2'])){
             $_SESSION['kolek']['periode2']=$_POST['par2'];
           
            $par2=date('Y-m-d',strtotime($_POST['par2']));
            if($_POST['par2']=='')
              $where="where  b.tgl<='".$par2."' group by b.rekening having bakidebet<>0 ";
            
            else
              $where="where b.tgl<='$par2' group by b.rekening having bakidebet<>0 ";
            
          }else{
            
            $par2="";

            $where="where b.tgl='$par2' ";
           
          }
          ?>
          <form role="form" id="quickForm" action="" method="POST">
            <div class="row">
              <div class="col-sm-10">
               <div class="form-group row">
              <label  for="nama" class="col-2 m-2">Tanggal</label>
              <input type="date" name="par2" id="par2" class="form-control col-2" value="<?=@$par2?>">
                <button type="submit" class="  btn btn-info"  ><i class="fas fa-search"></i></button>
                </div>
              <!-- </div> -->
                
               
              
                     
          </form>                   
          <hr>

          <form action="simpan/simpan_kolektibilitas.php" method="POST">
           <!--  <table id="sortTable" width="100%" class="table table-bordered table-hover">
            <thead>
              <tr align="center">
                <th >No</th>
                <th >Tgl</th>
                <th >Rekening</th>
                <th align="center">Plafond</th>
                <th align="center">Bakidebet</th>
                <th >Kol</th>
              </tr>
            </thead>
            <tbody> -->
              <?php
              if(isset($_POST['par2'])){
                $tglm=$_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.no as no,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');    
                 
                 $flat_t_margin=$angsuran_margin*kol($hasil)-$tmargin;
                 $flat_t_pokok=$angsuran_pokok*kol($hasil)-$tpokok;
                 $tot_tunggakan= $flat_t_margin+$flat_t_pokok;

                
                 $no++;
                  echo "
                      <td><input type='hidden' name='rekening[]'value ='$rekening'checked </td>
                      <td><input type='hidden' name='tgl' value = '$tglm' </td>  
                      <td><input type='hidden' name='kol[]' value='"; if($flat_t_margin<=0){ echo "L";}else{
                      if($hasil<=30) echo "D";elseif($hasil<=45)echo "D";else echo "M";} echo "'</td>
                                      
                  </tr>";
                   }}
                   ?>
                  
                  
              <!-- </tbody> -->
               
              <!-- </table> -->
              
            <input type="submit" name="Proses" class="  btn btn-info" value="Proses">
          </div>
            <?php }?>
             </form>
            <!-- <script type="text/javascript" charset="utf8" src="./assets/jquery.dataTables.js"></script>
              <link rel="stylesheet" type="text/css" href="./assets/jquery.dataTables.css"> -->
          <!-- https://www.sahretech.com/2020/04/cara-membuat-pencarian-sorting-dan.html?m=1/ cara buat sorter table -->
             <!--  <script>
              $('#sortTable').DataTable();
              </script> -->
          
<!-- tampilkan laporan -->
<div class="row">
  <div class="col-sm-8">
<?php

          
            if(isset($_POST['kolek'])){
              $kolek = $_POST['kolek'];
            if($_POST['kolek']=='S')
              $where2=" WHERE d.rekening !='' GROUP BY d.rekening ORDER BY d.kol ASC ";      
            else
               
              $where2=" WHERE d.kol='$kolek' GROUP BY d.rekening ORDER BY d.kol ASC ";
      
          }else{
            $kolek1="";
            $where2="WHERE d.kol='$kolek1' ";
           
          }
          ?>
          <form role="form" id="quickForm" action="home.php?home=<?php echo md5("kolektibilitas");?>" method="POST">
            <div class="row">
              <div class="col-sm-8">
                <div class="form-group row">
                <label for="nama" class="col-sm-4">Kolektibilitas</label>
                <select id="kolek" name="kolek"oninput="this.className = ''" class="form-control col-sm-4 select2" onKeyDown="changeFocus(event,'jaminan')" >
                    <option value="S">Semua</option>
                    <option value="L">Lancar</option>
                    <option value="K">Kurang Lancar</option>
                    <option value="D">Diragukan</option>
                    <option value="M">Macet</option>
                  </select>
            
          <!-- </div> -->
<button type="submit" class="  btn btn-info"  >Previw</button>

 </div>

 </div> 
<?php if(isset($_POST['kolek'])){ ?>
                  <div class="col-3">                  
                <a href="pdf/lap_kolektibilitaspdf.php" target="_blank" style="float: right;margin-top: 10px;" class="btn btn-success"><i class="fa fa-print"></i> PDF</a>
              </div>
            
                <?php } ?>
 <!-- </div>   -->
 

  </form>                   


            <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 >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['kolek'])){
              include './koneksi.php';
              function kol2($kolbln){
                    if($kolbln<="15"){
                     return 0;
                    }elseif($kolbln<="30"){
                    return 1;
                    }elseif($kolbln<="45"){
                        return 2;
                    }else{return 3;
                    } 
                    }
              $queryk = "SELECT d.kol AS kol,d.tgl as tgl,a.jaminan AS jaminan,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, d.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 FROM pinjaman a LEFT JOIN angsuran b ON a.rekening=b.rekening LEFT JOIN registrasi c ON a.register=c.id LEFT JOIN kolek d ON d.rekening=a.rekening $where2";
                $querykl       =mysqli_query($koneksi,$queryk);
                if(mysqli_num_rows($querykl)){
                  $_SESSION['kolek']['kode']['sqlkl']=$queryk;
                  
                $no=0;
                
                while ($data2 = mysqli_fetch_assoc($querykl)) {
                  extract($data2);
                  //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');
                       
                 
                 $flat_t_margin=$angsuran_margin*kol2($hasil)-$tmargin;
                 $flat_t_pokok=$angsuran_pokok*kol2($hasil)-$tpokok;
                 $tot_tunggakan= $flat_t_margin+$flat_t_pokok;

                 $gab_plafond += $plafond;
                 $gab_bakidebet += $bakidebet;
                 $gab_margin +=$angsuran_margin*kol2($hasil)-$tmargin;
                 $gab_pokok += $angsuran_pokok*kol2($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($tgl)); ?></td>
  <td><?php echo $rekening; ?></td>
  <td><?php echo $nama; ?></td>                   
  <td align="right"><?php echo number_format($plafond); ?></td>
 <td><?php echo $margin; ?></td>
  
   <td align="right"><?php echo number_format($bakidebet,0); ?></td>
   <td align="left"><?php echo $barang; ?></td>
   <td align="left"><?php echo $jaminan; ?></td>
  <td align="right"><?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 echo $kol; ?></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 } }?>
            </div>
          </div>
         
            <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 -->


