<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;
$koneksi = mysqli_connect('localhost', 'root', '', 'billing_collective');
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Lapo?</title>
</head>
<body style="font-family:arial;">
<center><h2>PBB TOOL</h2>
<table>
    <tr>
        <td>
<div class="container" style="width:100%;">
        <div style="border-style:solid;height:115px;padding:5px;">
            <center><h3 style="margin-top:0px;padding-top:10px;">NGUNGGAHNO KWITANSI PBB</h3></center>
            <form action="" method="post" enctype="multipart/form-data">
            nama file : <input type="file" name="file" id="">
            <br>
            <center><button style="margin-top:17px" name="start" type="submit">Gass!</button></center>
            </form>
        </div>
        <?php

$nomor_permintaan = mysqli_fetch_assoc(mysqli_query($koneksi, "SELECT Nomor_permintaan FROM bc_manifest_detil ORDER BY Nomor_permintaan DESC LIMIT 1"))['Nomor_permintaan']+1;
if(isset($_POST['start'])){
    $nama_file = $_FILES['file']['name'];
    $lokasi = "C://xampp/htdocs/bc/upload_file_excel/".$_FILES['file']['name'];
    $pindah = move_uploaded_file($_FILES['file']['tmp_name'], $lokasi);
    if($pindah){
        print "pindah file selesai , mohon ditunggu :";
    }
    $excel = \PhpOffice\PhpSpreadsheet\IOFactory::load($lokasi); 
    $per = 2;
    while($excel->getActiveSheet()->getCell("A".$per) != ''){
        $per=$per+1;
        //berhenti jika menemukan nilai ksong
        if ($excel->getActiveSheet()->getCell("A".$per) == '' ) {
        print " <b>sudah selesai</b> ";
        break;
        }
        $v['a_kode_kecamatan'] = $excel->getActiveSheet()->getCell("C".$per);
	if(true){

        $kode_propinsi = $excel->getActiveSheet()->getCell("A".$per);
        $kode_dati2 = $excel->getActiveSheet()->getCell("B".$per);
        $kode_kecamatan = $excel->getActiveSheet()->getCell("C".$per);
        $kode_kelurahan = $excel->getActiveSheet()->getCell("D".$per);
        $kode_blok = $excel->getActiveSheet()->getCell("E".$per);
        $no_urut = $excel->getActiveSheet()->getCell("F".$per);
        $kode_jns_op = $excel->getActiveSheet()->getCell("G".$per);
        $tahun_pajak_sppt = $excel->getActiveSheet()->getCell("H".$per);
        $nama_wp = str_replace("'","", $excel->getActiveSheet()->getCell("I".$per));
        $total_pokok = $excel->getActiveSheet()->getCell("J".$per);
        $total_denda = $excel->getActiveSheet()->getCell("K".$per);
        
        //$nomor_billing = $excel->getActiveSheet()->getCell("L".$per);

        $kode_pembayaran = $excel->getActiveSheet()->getCell("M".$per);
        $tanggal_billing = $excel->getActiveSheet()->getCell("O".$per);
        $tangal_pembayaran = $excel->getActiveSheet()->getCell("N".$per);
        $tpembayaran= explode("-",$tangal_pembayaran);
        $nomor_billing = $kode_propinsi.$kode_dati2.$kode_kecamatan.$excel->getActiveSheet()->getCell("L".$per).$tpembayaran[2];

        $query = mysqli_query($koneksi, "INSERT INTO bc_manifest_detil (Nomor_Permintaan, Kd_Propinsi, Kd_Dati2, Kd_Kecamatan, Kd_Kelurahan, Kd_Blok, No_Urut, Kd_Jns_Op, Thn_Pajak_SPPT, Nama_WP, Kelurahan_WP, Total_Pokok, Total_Denda, Nomor_Billing, Kode_Pembayaran, isValid, State, UserCreate, DateCreate, UserUpdate, DateUpdate, Tanggal_Pembayaran, Jam_Pembayaran, Tanggal_Billing) VALUES ('$nomor_permintaan', '$kode_propinsi', '$kode_dati2', '$kode_kecamatan', '$kode_kelurahan', '$kode_blok', '$no_urut', '$kode_jns_op', '$tahun_pajak_sppt', '$nama_wp', NULL, '$total_pokok', '$total_denda', '$nomor_billing', '$kode_pembayaran', '1', NULL, NULL, NULL, NULL, NULL, '$tangal_pembayaran', '00:00:00', '$tanggal_billing')");

        if ($query) {
        }else{
            print "gagal";
            print $nama_wp." : ".$nomor_billing;
        }
}


    }
    //print $excel->getActiveSheet()->getCell('B8');
}

?>	
    </div>
    </td>
        <td>
    <div >
    <div style="text-align:center;border-style:solid; border-color:brown; padding:12px;">
    <h3 style="margin-top:0px">CEK MANIFEST</h3>
        <form action="" method="post" enctype="multipart/form-data">
        nama file : <input type="file" name="cekmanifest" id=""><br><br>
        <button type="submit" name="start_manifest">Gass!</button>
        </form>
    </div>
    

    <?php
        
        if(isset($_POST['start_manifest'])){
    
            $nama_file = $_FILES['cekmanifest']['name'];
            $lokasi = "C://xampp/htdocs/bc/upload_file_excel_manifest/".$_FILES['cekmanifest']['name'];
            $pindah = move_uploaded_file($_FILES['cekmanifest']['tmp_name'], $lokasi);
            if($pindah){
                print "(manifest) pindah file selesai , mohon ditunggu :";
            }
            $excel = \PhpOffice\PhpSpreadsheet\IOFactory::load($lokasi); 
            $per = 3;
            $hitungan = 1;
            $nomor_billing_temp = "";
            
            $temp = "tes";
            $jumlah_kolom = $excel->getActiveSheet()->getHighestRow();
            $excel->getActiveSheet()->setCellValue("L".($jumlah_kolom+1),"stop");
		//ngecek kode kecamatan
            while($excel->getActiveSheet()->getCell("C".$per) != ''){
                $kode_propinsi = $excel->getActiveSheet()->getCell("A".$per);
                $kode_dati2 = $excel->getActiveSheet()->getCell("B".$per);
                $kode_kecamatan = (string)$excel->getActiveSheet()->getCell("C".$per);
                $kode_kelurahan = $excel->getActiveSheet()->getCell("D".$per);
                $kode_blok = $excel->getActiveSheet()->getCell("E".$per);
                $no_urut = $excel->getActiveSheet()->getCell("F".$per);
                $kode_jns_op = $excel->getActiveSheet()->getCell("G".$per);
                $tahun_pajak_sppt = $excel->getActiveSheet()->getCell("H".$per);
                $nama_wp = str_replace("'", "", $excel->getActiveSheet()->getCell("I".$per));
                $total_pokok = $excel->getActiveSheet()->getCell("J".$per);
                $total_denda = $excel->getActiveSheet()->getCell("K".$per);
                $nomor_billing = $excel->getActiveSheet()->getCell("L".$per);
                $kode_pembayaran = $excel->getActiveSheet()->getCell("M".$per);
                $tangal_pembayaran = $excel->getActiveSheet()->getCell("N".$per);
                $tanggal_billing = $excel->getActiveSheet()->getCell("O".$per);
                $tangal_pembayaran = explode(" ",$excel->getActiveSheet()->getCell("N".$per))[0];
                $tpembayaran = explode("-",$tangal_pembayaran);
                $nomor_billing_final = $kode_propinsi.$kode_dati2.$kode_kecamatan.$nomor_billing.$tpembayaran[2];
                if($kode_kecamatan != $temp){
                    $temp = $kode_kecamatan;
                    
                        $k = "INSERT INTO bc_manifest (Kd_Propinsi, Kd_Dati2, Kd_Kecamatan, Kd_Kelurahan, Nama_File, Nomor_Billing, Tanggal_Billing, Nomor_Permintaan, Tanggal_Permintaan, Kode_Pembayaran, Tanggal_Pembayaran, Total_Denda, Total_Pokok, Response_Message, Jml_Data, Status_Reversal, Tanggal_Reversal, UserCreate, DateCreate, UserUpdate, DateUpdate) VALUES ('".$kode_propinsi."', '".$kode_dati2."', '".$kode_kecamatan."', '".$kode_kelurahan."', '', '".$nomor_billing_final."', '".$tanggal_billing."', '0000000000000', '".$tanggal_billing."', '".$kode_pembayaran."', '".$tanggal_billing."', '".$total_denda."', '1', '', '1', '0', '0000-00-00 00:00:00', 'admin', '".$tanggal_billing."', 'system', '".$tanggal_billing."')";
                        $kuery = mysqli_query($koneksi, $k);
                        if($kuery){
                            print "|";
                            print "<b>Selesai</b>";
                        }else{
                            print "X";
                        }	
                }
                $per++;
            }
           
        }

        
    ?>
    </div>
    </td>
    </tr>
    
</table>
</body>
</html>
