<?php
session_start(); 

require 'vendor/autoload.php';
include "vendor/phpqrcode/qrlib.php"; 

use Gregwar\Captcha\CaptchaBuilder;

use PhpOffice\PhpSpreadsheet\Reader\Xlsx;

include 'dbconn.php';

if (isset($_SERVER['HTTP_X_REQUESTED_WITH']) && $_SERVER['HTTP_X_REQUESTED_WITH'] == 'XMLHttpRequest') {
    // ajax request

    $key = 'e188ff584c43eb3d7c4e10b4b8d3cc77';


if($_POST['capcay']!==$_SESSION['phrase']){
    $builder = new CaptchaBuilder;
    $builder->build();
    $_SESSION['phrase'] = $builder->getPhrase();
    echo 'capcay';
    
} else {
    
    if(md5(htmlspecialchars($_POST['key'])) !== $key) {

        $builder = new CaptchaBuilder;
        $builder->build();
        $_SESSION['phrase'] = $builder->getPhrase();
        echo 'key';
        
    } else {

    session_destroy();
    
        
    $valid_extensions = array('xls', 'xlsx'); // valid extensions
    $path = 'uploads/'; // upload directory
    
    $penyimpanan = "tempqr/";
    $penyimpanan_label = "templb/";
    $penyimpanan_zip = "tempzip/";

    if (!file_exists($penyimpanan))
     mkdir($penyimpanan);

     if (!file_exists($penyimpanan_label))
     mkdir($penyimpanan_label);
    
    $url = "http://localhost/qr-aset/view/?aset=";
    
    chmod($path,0777);
    if($_FILES['file'])
    {
    $fname = $_FILES['file']['name'];
    $tmp = $_FILES['file']['tmp_name'];
    // get uploaded file's extension
    $ext = strtolower(pathinfo($fname, PATHINFO_EXTENSION));
    // can upload same image using rand function
    $final_image = rand(1000,1000000).$fname;
    // check's valid format
    if(in_array($ext, $valid_extensions)) 
    { 
    $uniquename = md5(microtime());
    // $path = $path.$uniquename.'.'.$ext; 
    $path = $path.$uniquename; 
        if(move_uploaded_file($tmp,$path)) 
        {
          
    
            $Reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
    
            $spreadSheet = $Reader->load($path);
            $excelSheet = $spreadSheet->getActiveSheet();
            $spreadSheetAry = $excelSheet->toArray();
            $sheetCount = count($spreadSheetAry);
    
            
            for ($i = 13; $i <= ($sheetCount-9); $i ++) {
                $kode = "";
                if (isset($spreadSheetAry[$i][1])) {
                    $kode = htmlspecialchars(mysqli_real_escape_string($conn, $spreadSheetAry[$i][1]));
                }
    
                $nama = "";
                if (isset($spreadSheetAry[$i][2])) {
                    $nama = htmlspecialchars(mysqli_real_escape_string($conn, $spreadSheetAry[$i][2]));
                }
    
               $reg = "";
                if (isset($spreadSheetAry[$i][3])) {
                   $reg = htmlspecialchars(mysqli_real_escape_string($conn, $spreadSheetAry[$i][3]));
                }
    
               $merk = "";
                if (isset($spreadSheetAry[$i][4])) {
                   $merk = htmlspecialchars(mysqli_real_escape_string($conn, $spreadSheetAry[$i][4]));
                }
    
                $ukuran = "";
                if (isset($spreadSheetAry[$i][5])) {
                    $ukuran = htmlspecialchars(mysqli_real_escape_string($conn, $spreadSheetAry[$i][5]));
                }
    
                $bahan = "";
                if (isset($spreadSheetAry[$i][6])) {
                    $bahan = htmlspecialchars(mysqli_real_escape_string($conn, $spreadSheetAry[$i][6]));
                }
    
                $warna = "";
                if (isset($spreadSheetAry[$i][7])) {
                    $warna = htmlspecialchars(mysqli_real_escape_string($conn, $spreadSheetAry[$i][7]));
                }
    
                $tahun = "0";
                if (isset($spreadSheetAry[$i][8])) {
                    $tahun = htmlspecialchars(mysqli_real_escape_string($conn, $spreadSheetAry[$i][8]));
                }
    
                $pabrik = "";
                if (isset($spreadSheetAry[$i][9])) {
                    $pabrik = htmlspecialchars(mysqli_real_escape_string($conn, $spreadSheetAry[$i][9]));
                }
    
                $rangka = "";
                if (isset($spreadSheetAry[$i][10])) {
                    $rangka = htmlspecialchars(mysqli_real_escape_string($conn, $spreadSheetAry[$i][10]));
                }
    
                $mesin = "";
                if (isset($spreadSheetAry[$i][11])) {
                    $mesin = htmlspecialchars(mysqli_real_escape_string($conn, $spreadSheetAry[$i][11]));
                }
    
                $polisi = "";
                if (isset($spreadSheetAry[$i][12])) {
                    $polisi = htmlspecialchars(mysqli_real_escape_string($conn, $spreadSheetAry[$i][12]));
                }
    
                $bpkb = "";
                if (isset($spreadSheetAry[$i][13])) {
                    $bpkb = htmlspecialchars(mysqli_real_escape_string($conn, $spreadSheetAry[$i][13]));
                }
    
                $asal = "";
                if (isset($spreadSheetAry[$i][14])) {
                    $asal = htmlspecialchars(mysqli_real_escape_string($conn, $spreadSheetAry[$i][14]));
                }
    
                $harga = "0";
                if (isset($spreadSheetAry[$i][15])) {
                    $harga = htmlspecialchars(mysqli_real_escape_string($conn, $spreadSheetAry[$i][15]));
                }
    
                $keterangan = "";
                if (isset($spreadSheetAry[$i][16])) {
                    $keterangan = htmlspecialchars(mysqli_real_escape_string($conn, $spreadSheetAry[$i][16]));
                }
    
                if($kode !=''){
                    $sql = "INSERT INTO aset (kode, nama, reg, merk, ukuran, bahan, warna, tahun, pabrik, rangka, mesin, polisi, bpkb, asal, harga, keterangan) VALUES ('$kode', '$nama', '$reg', '$merk', '$ukuran', '$bahan','$warna', '$tahun', '$pabrik', '$rangka', '$mesin', '$polisi', '$bpkb', '$asal', '$harga', '$keterangan')";
        
                    
                    if ($conn->query($sql) === TRUE) {
                        $last_id = $conn->insert_id;
                        QRcode::png($url.base64_encode($last_id), $penyimpanan.base64_encode($last_id).".png"); 

                        $qr = imagecreatefrompng($penyimpanan.base64_encode($last_id).".png");
                        $pemkot = imagecreatefrompng("image/pemkot.png");

                        // Create the image
                        $gmb = imagecreatetruecolor(720, 120);

                        // Create some colors
                        $bg = imagecolorallocate($gmb, 255, 255, 255);
                        $txt = imagecolorallocate($gmb, 0, 0, 0);
                        imagefilledrectangle($gmb, 0, 0, 720, 120, $bg);

                        // The text to draw
                        imagestring( $gmb, 5, 120, 25, "ASET DISKOMINSTA", $txt ); 
                        imagesetthickness( $gmb, 10 ); 
  

                        // The text to draw
                        imagestring( $gmb, 5, 120, 50, $kode, $txt ); 
                        imagesetthickness( $gmb, 10 ); 

                          // The text to draw
                          imagestring( $gmb, 5, 120, 75, "No. Reg : ".$reg." Tahun :".$tahun, $txt ); 
                          imagesetthickness( $gmb, 10 ); 

                          imagecopyresampled($gmb, $qr, 620, 10, 0, 0, 100, 100, 123, 123);
                          imagecopyresampled($gmb, $pemkot, 5, 10, 0, 0, 100, 100, 123, 123);

                        imagepng($gmb,$penyimpanan_label.$kode." - ".$reg.".png");
                        imagedestroy($gmb); 

                        unlink($penyimpanan.base64_encode($last_id).".png");

                        echo "New record created successfully";
                    } else {
                        echo "Error: " . $sql . "<br>" . $conn->error;
                    }
                }
      
    
            }

            // Get real path for our folder
            $rootPath = realpath('templb');

            // Initialize archive object
            $zip = new ZipArchive();
            $zipname = 'qraset.zip';
            $zip->open($penyimpanan_zip.$zipname, ZipArchive::CREATE | ZipArchive::OVERWRITE);

            // Initialize empty "delete list"
            $filesToDelete = array();

            // Create recursive directory iterator
            /** @var SplFileInfo[] $files */
            $files = new RecursiveIteratorIterator(
                new RecursiveDirectoryIterator($rootPath),
                RecursiveIteratorIterator::LEAVES_ONLY
            );

            foreach ($files as $name => $file)
            {
                // Skip directories (they would be added automatically)
                if (!$file->isDir())
                {
                    // Get real and relative path for current file
                    $filePath = $file->getRealPath();
                    $relativePath = substr($filePath, strlen($rootPath) + 1);

                    // Add current file to archive
                    $zip->addFile($filePath, $relativePath);

                    // Add current file to "delete list"
                    // delete it later cause ZipArchive create archive only after calling close function and ZipArchive lock files until archive created)
                   
                        $filesToDelete[] = $filePath;
                    
                }
            }

            // Zip archive will be created only after closing object
            $zip->close();

            // Delete all files from "delete list"
            foreach ($filesToDelete as $file)
            {
                unlink($file);
            }
    
            unlink($path);
    
           
            // var_dump($spreadSheetAry);die();
        
        }
    } 
    else 
    {
    echo 'invalid';
    }
    }
    }

}


} else {
    // not an ajax request
    header("Location: http://localhost/qr-aset/");
}


?>