<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$koneksi = mysqli_connect('172.19.23.68', 'admin', 'admin', 'billing_collective');
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Document</title>
</head>
<body>
    <div style="text-align:center;border-style:solid; border-color:brown; padding:12px;width:35%">
    <h3 style="margin-top:0px">CEK MANIFEST</h3>
        <form action="" method="post" enctype="multipart/form-data">
        Masukkan File Excel : <input type="file" name="cekmanifest" id=""><br><br>
        <button type="submit" name="start_manifest">Gass!</button>
        </form>
    </div>
    <div>

    <?php
        
        if(isset($_POST['start_manifest'])){
    
            $nama_file = $_FILES['cekmanifest']['name'];
            $lokasi = "C://xampp3/htdocs/bc/upload_file_excel_manifest/".$_FILES['cekmanifest']['name'];
            $pindah = move_uploaded_file($_FILES['cekmanifest']['tmp_name'], $lokasi);
            if($pindah){
                print "pindah file selesai , mohon ditunggu :";
            }
            $excel = \PhpOffice\PhpSpreadsheet\IOFactory::load($lokasi); 
            $per = 2;
            $hitungan = 1;
            $nomor_billing_temp = "";
            $data_tampungan = [];
            $temp = "test";
            $jumlah_kolom = $excel->getActiveSheet()->getHighestRow();
            $excel->getActiveSheet()->setCellValue("L".($jumlah_kolom+1),"stop");
            while($excel->getActiveSheet()->getCell("L".$per) != ''){
                
                $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);
                $tangal_pembayaran = $excel->getActiveSheet()->getCell("N".$per);
                $tanggal_billing = $excel->getActiveSheet()->getCell("O".$per);

                if($nomor_billing != $temp){
                    $hitungbilling = $excel->getActiveSheet()->getCell("L".($per-1));
                    $qkode_propinsi = $excel->getActiveSheet()->getCell("A".($per-1));
                    $qkode_dati2 = $excel->getActiveSheet()->getCell("B".($per-1));
                    $qkode_kecamatan = $excel->getActiveSheet()->getCell("C".($per-1));
                    $qkode_kelurahan = $excel->getActiveSheet()->getCell("D".($per-1));
                    $qkode_blok = $excel->getActiveSheet()->getCell("E".($per-1));
                    $qno_urut = $excel->getActiveSheet()->getCell("F".($per-1));
                    $qkode_jns_op = $excel->getActiveSheet()->getCell("G".($per-1));
                    $qtahun_pajak_sppt = $excel->getActiveSheet()->getCell("H".($per-1));
                    $qnama_wp = str_replace("'", "", $excel->getActiveSheet()->getCell("I".($per-1)));
                    $qtotal_pokok = $excel->getActiveSheet()->getCell("J".($per-1));
                    $qtotal_denda = $excel->getActiveSheet()->getCell("K".($per-1));
                    $qnomor_billing = $excel->getActiveSheet()->getCell("L".($per-1));
                    $qkode_pembayaran = $excel->getActiveSheet()->getCell("M".($per-1));
                    $qtangal_pembayaran = $excel->getActiveSheet()->getCell("N".($per-1));
                    $qtanggal_billing = $excel->getActiveSheet()->getCell("O".($per-1));

                    $data_tampungan[] = [
                        "$hitungbilling" => $hitungan,
                        "a_kode_propinsi" => "$qkode_propinsi",
                        "a_kode_dati2" => "$qkode_dati2",
                        'a_kode_kecamatan' => "$qkode_kecamatan",
                        'a_kode_kelurahan' => "$qkode_kelurahan",
                        'a_kode_blok' => "$qkode_blok",
                        'a_no_urut' => "$qno_urut",
                        'a_kd_jenis_op' => "$qkode_jns_op",
                        'a_tahun_pajak_sppt' => "$qtahun_pajak_sppt",
                        'a_nama_wp' => "$qnama_wp",
                        'a_total_pokok' => "$qtotal_pokok",
                        'a_total_denda' => "$qtotal_denda",
                        'a_nomor_billing' => "$qnomor_billing",
                        'a_kode_pembayaran' => "$qkode_pembayaran",
                        'a_tanggal_pembayaran' => "$qtangal_pembayaran",
                        'a_tanggal_billing' => "$qtanggal_billing"

                    ];
                    $hitungan = 0;
                }
                $temp = $nomor_billing;

                $per++;
                $hitungan++;

            }
            unset($data_tampungan[0]);
            
            $teh = 0;
            foreach ($data_tampungan as $v) {
                # code...
                
                $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 ('".$v["a_kode_propinsi"]."', '".$v["a_kode_dati2"]."', '".$v['a_kode_kecamatan']."', '".$v['a_kode_kelurahan']."', '', '".$v['a_nomor_billing']."', '".$v['a_tanggal_billing']."', '0000000000000', '".$v['a_tanggal_billing']."', '".$v['a_kode_pembayaran']."', '".$v['a_tanggal_billing']."', '".$v['a_total_denda']."', '1', '', '1', '0', '0000-00-00 00:00:00', 'admin', '".$v['a_tanggal_billing']."', 'system', '".$v['a_tanggal_billing']."')";
                
                $kuery = mysqli_query($koneksi, $k);
                if($kuery){
                    print "|";

                    print "<b>Selesai</b>";
                }else{
                    print "X";
                }
            }
        }

        
    ?>
    </div>
</body>
</html>