<?php
//載入匯入 excel 外掛
require_once("./vendor/autoload.php");
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;

$uploadFolder = "./temp/";
if(is_uploaded_file($_FILES['upload']['tmp_name'])){
	move_uploaded_file($_FILES['upload']['tmp_name'], $uploadFolder.$_FILES['upload']['name']);
}

//取得檔案名稱
$fileName = $_FILES['upload']['name'];

//取得副檔名
$extension = pathinfo($fileName, PATHINFO_EXTENSION);

$exportHtml = "";
if($extension=="xlsx" || $extension=="xls"){
	if($extension == "xlsx"){
		$readerLibrary = "Xlsx";
	}elseif($extension == "xls"){
		$readerLibrary = "Xls";
	}
	
	$callStartTime = microtime(true);
	
	try{
		//讀取路徑的 excel 檔案
		$reader = IOFactory::createReader($readerLibrary);
		$reader->setReadDataOnly(TRUE);
		$spreadsheet = $reader->load($uploadFolder . $fileName);
	}catch(Exception $e){
		die('<span style="color:red;">' . date("Y-m-d H:i:s") . ' 資料讀入失敗: ' . $e->getMessage() . '</span><br />');
	}
	$saveDB = true;
	
	$worksheet = $spreadsheet->getActiveSheet();
	
	//總行數
	$highestRow = $worksheet->getHighestRow();
	
	$supplierInventory = array();
	$inventoryListArray = array();
	$category = $spreadsheet->getActiveSheet()->getCell("A1");
	if($category == "SchemaName"){
		for($row = 21; $row <= $highestRow; $row++){
			$create_date = $worksheet->getCellByColumnAndRow(1, $row)->getValue();
		}
	}elseif($category == "Interface Type:"){
		$vendorCd = $spreadsheet->getActiveSheet()->getCell("B2")->getValue();
		$partner = $spreadsheet->getActiveSheet()->getCell("B3")->getValue();
		
		for($row = 14; $row <= $highestRow; $row++){
			$dataprovptr = $worksheet->getCellByColumnAndRow(1, $row)->getValue();
			$location = $worksheet->getCellByColumnAndRow(2, $row)->getValue();
			$product = $worksheet->getCellByColumnAndRow(3, $row)->getValue();
			$yvendorbatch = $worksheet->getCellByColumnAndRow(4, $row)->getValue();
			$yypartnerProdNr = $worksheet->getCellByColumnAndRow(5, $row)->getValue();
			$uom = $worksheet->getCellByColumnAndRow(6, $row)->getValue();
			$qtyUnres = $worksheet->getCellByColumnAndRow(7, $row)->getValue();
			$inTransfer = $worksheet->getCellByColumnAndRow(8, $row)->getValue();
			$yyshelflife = $worksheet->getCellByColumnAndRow(9, $row)->getValue();
			$yyestdt = $worksheet->getCellByColumnAndRow(10, $row)->getValue();
			$yywip1 = $worksheet->getCellByColumnAndRow(11, $row)->getValue();
			$yywip2 = $worksheet->getCellByColumnAndRow(12, $row)->getValue();
			$yywip3 = $worksheet->getCellByColumnAndRow(13, $row)->getValue();
			$yywip4 = $worksheet->getCellByColumnAndRow(14, $row)->getValue();
			$yylqtyws = $worksheet->getCellByColumnAndRow(15, $row)->getValue();
			$yylqtyw0 = $worksheet->getCellByColumnAndRow(16, $row)->getValue();
			$yylqtyw1 = $worksheet->getCellByColumnAndRow(17, $row)->getValue();
			$yylqtyw2 = $worksheet->getCellByColumnAndRow(18, $row)->getValue();
			$yylqtyw3 = $worksheet->getCellByColumnAndRow(19, $row)->getValue();
			$yylqtyw4 = $worksheet->getCellByColumnAndRow(20, $row)->getValue();
			$yylqtyw5 = $worksheet->getCellByColumnAndRow(21, $row)->getValue();
			$yylqtyw6 = $worksheet->getCellByColumnAndRow(22, $row)->getValue();
			$yylqtyw7 = $worksheet->getCellByColumnAndRow(23, $row)->getValue();
			$yylqtyw8 = $worksheet->getCellByColumnAndRow(24, $row)->getValue();
			$yyshipidW0 = $worksheet->getCellByColumnAndRow(25, $row)->getValue();
			$yyshipidW1 = $worksheet->getCellByColumnAndRow(26, $row)->getValue();
			$yyshipidW2 = $worksheet->getCellByColumnAndRow(27, $row)->getValue();
			$yyshipidW3 = $worksheet->getCellByColumnAndRow(28, $row)->getValue();
			$yyshipidW4 = $worksheet->getCellByColumnAndRow(29, $row)->getValue();
			$yyshipidW5 = $worksheet->getCellByColumnAndRow(30, $row)->getValue();
			$yyshipidW6 = $worksheet->getCellByColumnAndRow(31, $row)->getValue();
			$yyshipidW7 = $worksheet->getCellByColumnAndRow(32, $row)->getValue();
			$yyshipidW8 = $worksheet->getCellByColumnAndRow(33, $row)->getValue();
			$yyblNumW0 = $worksheet->getCellByColumnAndRow(34, $row)->getValue();
			$yyblNumW1 = $worksheet->getCellByColumnAndRow(35, $row)->getValue();
			$yyblNumW2 = $worksheet->getCellByColumnAndRow(36, $row)->getValue();
			$yyblNumW3 = $worksheet->getCellByColumnAndRow(37, $row)->getValue();
			$yyblNumW4 = $worksheet->getCellByColumnAndRow(38, $row)->getValue();
			$yyblNumW5 = $worksheet->getCellByColumnAndRow(39, $row)->getValue();
			$yyblNumW6 = $worksheet->getCellByColumnAndRow(40, $row)->getValue();
			$yyblNumW7 = $worksheet->getCellByColumnAndRow(41, $row)->getValue();
			$yyblNumW8 = $worksheet->getCellByColumnAndRow(42, $row)->getValue();
			
			$inventoryListArray[] = array(
				"dataprovptr" => $dataprovptr,
				"location" => $location,
				"product" => $product,
				"yvendorbatch" => $yvendorbatch,
				"yypartnerProdNr" => $yypartnerProdNr,
				"uom" => $uom,
				"qtyUnres" => $qtyUnres,
				"inTransfer" => $inTransfer,
				"yyshelflife" => $yyshelflife,
				"yyestdt" => $yyestdt,
				"yywip1" => $yywip1,
				"yywip2" => $yywip2,
				"yywip3" => $yywip3,
				"yywip4" => $yywip4,
				"yylqtyws" => $yylqtyws,
				"yylqtyw0" => $yylqtyw0,
				"yylqtyw1" => $yylqtyw1,
				"yylqtyw2" => $yylqtyw2,
				"yylqtyw3" => $yylqtyw3,
				"yylqtyw4" => $yylqtyw4,
				"yylqtyw5" => $yylqtyw5,
				"yylqtyw6" => $yylqtyw6,
				"yylqtyw7" => $yylqtyw7,
				"yylqtyw8" => $yylqtyw8,
				"yyshipidW0" => $yyshipidW0,
				"yyshipidW1" => $yyshipidW1,
				"yyshipidW2" => $yyshipidW2,
				"yyshipidW3" => $yyshipidW3,
				"yyshipidW4" => $yyshipidW4,
				"yyshipidW5" => $yyshipidW5,
				"yyshipidW6" => $yyshipidW6,
				"yyshipidW7" => $yyshipidW7,
				"yyshipidW8" => $yyshipidW8,
				"yyblNumW0" => $yyblNumW0,
				"yyblNumW1" => $yyblNumW1,
				"yyblNumW2" => $yyblNumW2,
				"yyblNumW3" => $yyblNumW3,
				"yyblNumW4" => $yyblNumW4,
				"yyblNumW5" => $yyblNumW5,
				"yyblNumW6" => $yyblNumW6,
				"yyblNumW7" => $yyblNumW7,
				"yyblNumW8" => $yyblNumW8,
				"yisotankid" => null
			);
		}
		
		$supplierInventory = array(
			"partner" => $partner,
			"vendorCd" => $vendorCd
		);
	}
	
	unlink($uploadFolder.$_FILES['upload']['name']);
	
	echo json_encode(
		array(
			"success"=>true,
			"supplierInventory"=>json_encode($supplierInventory),
			"inventoryList" => json_encode($inventoryListArray)
		)
	);
}else{
	echo json_encode(array("msg"=>"檔案需為 Xlsx 或 Xls"));
}