<?php
//header("Access-Control-Allow-Origin: *");
//header("Access-Control-Allow-Headers: X-Requested-With, Content-Type, Origin, Cache-Control");
//header('Access-Control-Allow-Credentials: true');
//header('Access-Control-Allow-Headers:access-control-allow-origin, content-type');
//header('Content-type: application/json');

header("Access-Control-Allow-Origin: *");
    header("Access-Control-Allow-Methods: PUT, GET, POST");
    header("Access-Control-Allow-Headers: Origin, X-Requested-With, Content-Type, Accept");

use PhpOffice\PhpSpreadsheet\Reader\Xlsx;

require('dbConfig.php');
$connection = dbConfig();
require_once ('../vendor/autoload.php');

if (isset($_FILES["file"]["name"])) {

    $allowedFileType = [
        'application/vnd.ms-excel',
        'text/xls',
        'text/xlsx',
        'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    ];

    $tracker = 0;

    if (in_array($_FILES["file"]["type"], $allowedFileType)) {

        /*$targetPath = 'uploads/' . $_FILES['file']['name'];
        move_uploaded_file($_FILES['file']['tmp_name'], $targetPath);*/

        $Reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();

        $spreadSheet = $Reader->load($_FILES['file']['tmp_name']);
        $excelSheet = $spreadSheet->getActiveSheet();
        $spreadSheetAry = $excelSheet->toArray();
        $sheetCount = count($spreadSheetAry);

        $truncate_sql = "TRUNCATE TABLE chart_data";
        $isTruncated = mysqli_query($connection, $truncate_sql);
                
        if (! empty($isTruncated)) {
	        for ($i = 1; $i < $sheetCount; $i ++) {
	            $position_no = "";
	            if (isset($spreadSheetAry[$i][0])) {
	                $position_no = mysqli_real_escape_string($connection, $spreadSheetAry[$i][0]);
	            }
	            $reporting_to_position_no = "";
	            if (isset($spreadSheetAry[$i][1])) {
	                $reporting_to_position_no = mysqli_real_escape_string($connection, $spreadSheetAry[$i][1]);
	            }
	            $n_level = "";
	            if (isset($spreadSheetAry[$i][2])) {
	                $n_level = mysqli_real_escape_string($connection, $spreadSheetAry[$i][2]);
	            }
	            $position_code = "";
	            if (isset($spreadSheetAry[$i][3])) {
	                $position_code = mysqli_real_escape_string($connection, $spreadSheetAry[$i][3]);
	            }
	            $reporting_to_position_code = "";
	            if (isset($spreadSheetAry[$i][4])) {
	                $reporting_to_position_code = mysqli_real_escape_string($connection, $spreadSheetAry[$i][4]);
	            }
	            $group_n1 = "";
	            if (isset($spreadSheetAry[$i][5])) {
	                $group_n1 = mysqli_real_escape_string($connection, $spreadSheetAry[$i][5]);
	            }
	            $sub_division_n3 = "";
	            if (isset($spreadSheetAry[$i][6])) {
	                $sub_division_n3 = mysqli_real_escape_string($connection, $spreadSheetAry[$i][6]);
	            }
	            $department_n4 = "";
	            if (isset($spreadSheetAry[$i][7])) {
	                $department_n4 = mysqli_real_escape_string($connection, $spreadSheetAry[$i][7]);
	            }

	            if (! empty($position_no) || ! empty($reporting_to_position_no) || ! empty($n_level) || ! empty($position_code) || ! empty($reporting_to_position_code) || ! empty($group_n1) || ! empty($sub_division_n3) || ! empty($department_n4)) {

            	    $query = "INSERT into chart_data(position_no, reporting_to_position_no, n_level, position_code, reporting_to_position_code, group_n1, sub_division_n3, department_n4) values('" . $position_no . "', '" . $reporting_to_position_no . "', '" . $n_level . "', '" . $position_code . "', '" . $reporting_to_position_code . "', '" . $group_n1 . "', '" . $sub_division_n3 . "', '" . $department_n4 . "')";
	                $insertId = mysqli_query($connection, $query);

	                if (! empty($insertId)) {
	                	$tracker = 1;
	                    $type = "success";
	                    $message = "Excel Data Imported into the Database";
	                } else {
	                    $type = "error";
	                    $message = "Problem in Importing Excel Data";
	                }        
	            }
	        }
	    } else {
        	$type = "error";
            $message = "Problem in Deletion of Old Excel Data";
        }
        if ($tracker) {
        	$file_name = $_FILES['file']['name'];
        	$insertQuery = "INSERT into data_record(file_name) values('" . $file_name . "')";
	        $insertData = mysqli_query($connection, $insertQuery);

	        if (!$insertData) {
                $type = "error";
                $message = "Problem in insertion of file details";
            }
        }
        echo json_encode(array("type"=>$type, "message"=>$message));
    } else {
        $type = "error";
        $message = "Invalid File Type. Upload Excel File.";
        echo json_encode(array("type"=>$type, "message"=>$message));
    }
}
?>