<?php
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
require 'config.php';
$insertMessage = '';
$insertCount = 0;
//UNGGAH PBB
if (isset($_POST['start']) && isset($_FILES['file']) && $_FILES['file']['error'] == 0) {
    $nama_file = $_FILES['file']['name'];
    $lokasi = "C:/xampp/htdocs/bc/upload_file_excel/" . basename($nama_file);
    $pindah = move_uploaded_file($_FILES['file']['tmp_name'], $lokasi);

    if ($pindah) {
        $excel = IOFactory::load($lokasi); 
        $per = 2;

        $result = mysqli_query($koneksi, "SELECT Nomor_permintaan FROM bc_manifest_detil ORDER BY Nomor_permintaan DESC LIMIT 1");
        $row = mysqli_fetch_assoc($result);
        $nomor_permintaan = isset($row['Nomor_permintaan']) ? $row['Nomor_permintaan'] + 1 : 1;

        while ($excel->getActiveSheet()->getCell("A".$per)->getValue() != '') {
            $kode_propinsi = $excel->getActiveSheet()->getCell("A".$per)->getValue();
            $kode_dati2 = $excel->getActiveSheet()->getCell("B".$per)->getValue();
            $kode_kecamatan = $excel->getActiveSheet()->getCell("C".$per)->getValue();
            $kode_kelurahan = $excel->getActiveSheet()->getCell("D".$per)->getValue();
            $kode_blok = $excel->getActiveSheet()->getCell("E".$per)->getValue();
            $no_urut = $excel->getActiveSheet()->getCell("F".$per)->getValue();
            $kode_jns_op = $excel->getActiveSheet()->getCell("G".$per)->getValue();
            $tahun_pajak_sppt = $excel->getActiveSheet()->getCell("H".$per)->getValue();
            $nama_wp = str_replace("'", "", $excel->getActiveSheet()->getCell("I".$per)->getValue());
            $total_pokok = $excel->getActiveSheet()->getCell("J".$per)->getValue();
            $total_denda = $excel->getActiveSheet()->getCell("K".$per)->getValue();
            $kode_pembayaran = $excel->getActiveSheet()->getCell("M".$per)->getValue();
            $tanggal_billing = $excel->getActiveSheet()->getCell("O".$per)->getFormattedValue();
            $tangal_pembayaran = $excel->getActiveSheet()->getCell("N".$per)->getFormattedValue();

            if (empty($tangal_pembayaran)) {
                $per++;
                continue;
            }

            $tpembayaran = explode("-", $tangal_pembayaran);
            $tahun = isset($tpembayaran[2]) ? $tpembayaran[2] : '0000'; // fallback kalau index 2 tidak ada
            $nomor_billing = $kode_propinsi . $kode_dati2 . $kode_kecamatan .
                            $excel->getActiveSheet()->getCell("L".$per)->getValue() . $tahun;


            $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) {
                $insertCount++;
            }

            $per++;
        }

        if ($insertCount > 0) {
            $insertMessage = "<div class='mt-4 p-4 bg-green-100 border border-green-300 rounded text-green-700 text-sm'>
                Berhasil insert <strong>$insertCount</strong> data ke database!
            </div>";
        } else {
            $insertMessage = "<div class='mt-4 p-4 bg-yellow-100 border border-yellow-300 rounded text-yellow-700 text-sm'>
                Tidak ada data yang berhasil di-insert.
            </div>";
        }
    } else {
        $insertMessage = "<div class='mt-4 p-4 bg-red-100 border border-red-300 rounded text-red-700 text-sm'>
            Gagal mengunggah file.
        </div>";
    }
  }
// MANIFEST

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) {
            $totalSukses++; // ⬅️ Tambahan
        }
    }
        $per++;
    }
   
}

//cetakpdf

$tgl='';
if(isset($_POST['tgl'])){
$tgl = addslashes($_POST['tgl']);
}else{
$tgl = date('Y-m-d');
}
if(isset($_GET['nop'])){
    $nop = addslashes(trim($_GET['nop']));
    $kuery_data = mysqli_query($koneksi, "SELECT * FROM bc_manifest_detil WHERE concat(Kd_Propinsi,Kd_Dati2,Kd_Kecamatan,Kd_Kelurahan,Kd_Blok,No_Urut,Kd_Jns_Op)='$nop'");
}

$kuery_data1 = mysqli_query($koneksi, "SELECT * FROM bc_manifest_detil WHERE Tanggal_Pembayaran='$tgl'");


$kuery_data_kec = mysqli_query($koneksi, "SELECT * FROM bc_kd_kec");
$data_kec = [];
while($row = mysqli_fetch_assoc($kuery_data_kec)){
    $data_kec[$row['kd']] = $row['keterangan'];
}


?>