<form method="POST" enctype="multipart/form-data">
    <input type="file" name="file_excel" accept=".xlsx" required>
    <button type="submit" name="import">Import</button>
</form>

<?php
include '../koneksi.php';
include "../asset/SimpleXLSX.php";

use Shuchkin\SimpleXLSX;

if (isset($_POST['import'])) {

    if ($xlsx = SimpleXLSX::parse($_FILES['file_excel']['tmp_name'])) {

        foreach ($xlsx->rows() as $key => $row) {

            // Skip header
            if ($key < 2) {
                continue;
            }

            $tanggal = trim($row[0]);
            $nama_customer = trim($row[1]);
            $telp_perusahaan = trim($row[2]);
            $nama_pic = trim($row[3]);
            $nama_pic = preg_replace(
                '/^(bapak|pak|bpk|bp|pk)\.?\s+/i',
                'Bapak ',
                $nama_pic
            );

            $nama_pic = preg_replace(
                '/^(ibu|bu)\.?\s+/i',
                'Ibu ',
                $nama_pic
            );
            $telp_pic = trim($row[4]);
            $jenis_kebutuhan = trim($row[5]);
            $sumber = trim($row[6]);
            $keterangan_sumber = trim($row[7]);
            $keperluan = trim($row[8]);
            $kebutuhan = trim($row[9]);
            $note = trim($row[10]);



            // ==================================
            // FORMAT TANGGAL
            // ==================================

            if (!empty($tanggal)) {

                $pecah = explode('/', $tanggal);

                if (count($pecah) == 3) {

                    $hari  = $pecah[0];
                    $bulan = strtolower($pecah[1]);
                    $tahun = $pecah[2];

                    // Jika bulan berupa teks
                    $bulanMap = [
                        'jan' => '01',
                        'feb' => '02',
                        'mar' => '03',
                        'apr' => '04',
                        'mei' => '05',
                        'may' => '05',
                        'jun' => '06',
                        'jul' => '07',
                        'agu' => '08',
                        'aug' => '08',
                        'sep' => '09',
                        'okt' => '10',
                        'oct' => '10',
                        'nov' => '11',
                        'des' => '12',
                        'dec' => '12'
                    ];

                    if (isset($bulanMap[$bulan])) {
                        $bulan = $bulanMap[$bulan];
                    } else {
                        $bulan = str_pad($bulan, 2, '0', STR_PAD_LEFT);
                    }

                    $hari = str_pad($hari, 2, '0', STR_PAD_LEFT);

                    $tanggal = $tahun . '-' . $bulan . '-' . $hari;
                }

            } else {

                $tanggal = date('Y-m-d');
            }



            // ==================================
            // DETEKSI BADAN USAHA
            // ==================================

            $bu = '';

            if (preg_match('/^PT\.?\s/i', $nama_customer)) {

                $bu = 'PT';
                $nama_customer = preg_replace('/^PT\.?\s/i', '', $nama_customer);

            } elseif (preg_match('/^CV\.?\s/i', $nama_customer)) {

                $bu = 'CV';
                $nama_customer = preg_replace('/^CV\.?\s/i', '', $nama_customer);

            } elseif (preg_match('/^UD\.?\s/i', $nama_customer)) {

                $bu = 'UD';
                $nama_customer = preg_replace('/^UD\.?\s/i', '', $nama_customer);

            } elseif (preg_match('/^Bapak\s/i', $nama_customer)) {

                $bu = 'Bapak';
                $nama_customer = preg_replace('/^Bapak\s/i', '', $nama_customer);

            } elseif (preg_match('/^Ibu\s/i', $nama_customer)) {

                $bu = 'Ibu';
                $nama_customer = preg_replace('/^Ibu\s/i', '', $nama_customer);
            }

            $nama_customer = trim($nama_customer);



            // ==================================
            // JIKA CUSTOMER KOSONG
            // ==================================

            if (empty($nama_customer) || $nama_customer == '-') {

                $queryNull = mysqli_query($konek, "
                    SELECT MAX(CAST(SUBSTRING(nama_customer, 5) AS UNSIGNED)) AS last_no
                    FROM customer
                    WHERE nama_customer LIKE 'NULL%'
                ");

                $dataNull = mysqli_fetch_assoc($queryNull);

                $urutanNull = ($dataNull['last_no'] ?? 0) + 1;

                $nama_customer = 'NULL' . $urutanNull;
            }



            // ==================================
            // CEK CUSTOMER
            // ==================================

            $cekCustomer = mysqli_query($konek, "
                SELECT * FROM customer 
                WHERE nama_customer = '$nama_customer'
            ");

            if (mysqli_num_rows($cekCustomer) > 0) {

                $dataCustomer = mysqli_fetch_assoc($cekCustomer);
                $idcustomer = $dataCustomer['idcustomer'];

            } else {

                // ==================================
                // GENERATE ID CUSTOMER
                // ==================================

                $query = mysqli_query($konek, "
                    SELECT MAX(idcustomer) AS last_id 
                    FROM customer
                ");

                $dataCustomer = mysqli_fetch_assoc($query);

                $lastId = $dataCustomer['last_id'];

                if ($lastId == '') {

                    $urutan = 1;

                } else {

                    $urutan = (int) substr($lastId, 3);
                    $urutan++;
                }

                $idcustomer = 'CUS' . str_pad($urutan, 5, '0', STR_PAD_LEFT);



                // ==================================
                // INSERT CUSTOMER
                // ==================================

                mysqli_query($konek, "
                    INSERT INTO customer VALUES (
                        '$idcustomer',
                        '$bu',
                        '$nama_customer',
                        '',
                        '',
                        '$telp_perusahaan',
                        '',
                        NOW()
                    )
                ");
            }



            // ==================================
            // CEK PIC
            // ==================================
            $idpic = '';
            if (empty($nama_pic) || $nama_pic == '-') {

                $nama_pic = $telp_pic;
            }

            $cekPic = mysqli_query($konek, "
                SELECT * 
                FROM pic
                WHERE nama_pic = '$nama_pic'
                AND idcustomer = '$idcustomer'
            ");

            if (mysqli_num_rows($cekPic) > 0) {

                // PIC sudah ada
                $dataPic = mysqli_fetch_assoc($cekPic);
                $idpic = $dataPic['idpic'];

            } else {

                // ==================================
                // GENERATE ID PIC
                // ==================================

                $queryPic = mysqli_query($konek, "
                    SELECT MAX(idpic) AS last_id 
                    FROM pic
                ");

                $dataPic = mysqli_fetch_assoc($queryPic);

                $lastIdPic = $dataPic['last_id'];

                if ($lastIdPic == '') {

                    $urutanPic = 1;

                } else {

                    $urutanPic = (int) substr($lastIdPic, 3);
                    $urutanPic++;
                }

                $idpic = 'PIC' . str_pad($urutanPic, 5, '0', STR_PAD_LEFT);



                // ==================================
                // INSERT PIC
                // ==================================

                mysqli_query($konek, "
                    INSERT INTO pic VALUES (
                        '$idpic',
                        '$idcustomer',
                        '$nama_pic',
                        '$telp_pic',
                        '$note'
                    )
                ");
            }

            // ==================================
            // GENERATE ID TELP
            // ==================================

            $queryTelp = mysqli_query($konek, "
                SELECT MAX(	idtelp_custraxi) AS last_id 
                FROM telp_custraxi
            ");

            $dataTelp = mysqli_fetch_assoc($queryTelp);

            $lastIdTelp = $dataTelp['last_id'];

            if ($lastIdTelp == '') {

                $urutanTelp = 1;

            } else {

                $urutanTelp = (int) substr($lastIdTelp, 3);
                $urutanTelp++;
            }

            $idtelp = 'TCR' . str_pad($urutanTelp, 5, '0', STR_PAD_LEFT);

            // ==================================
            // FORMAT TANGGAL
            // ==================================

            if (!empty($tanggal)) {

                // Jika format excel berupa angka serial
                if (is_numeric($tanggal)) {

                    $unix_date = ($tanggal - 25569) * 86400;
                    $tanggal = gmdate("Y-m-d", $unix_date);

                } else {

                    $tanggal = str_replace('/', '-', $tanggal);
                    $tanggal = date('Y-m-d', strtotime($tanggal));
                }

            } else {

                $tanggal = date('Y-m-d');
            }

            mysqli_query($konek, "
                INSERT INTO telp_custraxi VALUES (
                    '$idtelp',
                    '$tanggal',
                    '$idcustomer',
                    '$kebutuhan',
                    '$keperluan',
                    '$jenis_kebutuhan',
                    '$sumber',
                    '$keterangan_sumber',
                    '0'
                )
            ");

            // ==================================
            // GENERATE ID DETAIL TELP
            // ==================================

            $queryTelp = mysqli_query($konek, "
                SELECT MAX(	iddtelp_custraxi) AS last_id 
                FROM telp_custraxi_detail
            ");

            $dataTelp = mysqli_fetch_assoc($queryTelp);

            $lastIdTelp = $dataTelp['last_id'];

            if ($lastIdTelp == '') {

                $urutanTelp = 1;

            } else {

                $urutanTelp = (int) substr($lastIdTelp, 3);
                $urutanTelp++;
            }

            $idtelpdetail = 'TDR' . str_pad($urutanTelp, 5, '0', STR_PAD_LEFT);

            mysqli_query($konek, "
                INSERT INTO telp_custraxi_detail VALUES (
                    '$idtelpdetail',
                    '$idtelp',
                    '$idpic'
                )
            ");

            // ==================================
            // GENERATE ID FOLLOWUP
            // ==================================

            $queryFollow = mysqli_query($konek, "
                SELECT MAX(idfollowup_raxi) AS last_id
                FROM followup_raxi
            ");

            $dataFollow = mysqli_fetch_assoc($queryFollow);

            $lastIdFollow = $dataFollow['last_id'];

            if ($lastIdFollow == '') {

                $urutanFollow = 1;

            } else {

                $urutanFollow = (int) substr($lastIdFollow, 3);
                $urutanFollow++;
            }

            $idfollowup = 'FTR' . str_pad($urutanFollow, 5, '0', STR_PAD_LEFT);


            // ==================================
            // INSERT FOLLOWUP
            // ==================================

            mysqli_query($konek, "
                INSERT INTO followup_raxi VALUES (
                    '$idfollowup',
                    '$tanggal',
                    'Penawaran',
                    '$tanggal',
                    '0000-00-00',
                    '',
                    '$idtelp'
                )
            ");

        }

        echo "
        <script>
            alert('Import berhasil');
            window.location='../telp_custraxi.php';
        </script>
        ";

    } else {

        echo SimpleXLSX::parseError();
    }
}
?>