<?php
ini_set("display_errors", "On");

include "helpers.php";

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

//取出指定資料夾所有檔案
//$path = __DIR__."/uploadFiles/";
$path = "D:\B2B_Inventory Update\/";
$files = scandir($path);
$files = array_diff(scandir($path), array('.', '..', 'Thumbs.db'));
$filesArray = array();
$array = array();
$supplierInventory = array();
$inventoryListArray = array();
$inventoryListArray2 = array();
$over500 = false;
foreach($files as $file){
	//取得副檔名
	$extension = pathinfo($file, PATHINFO_EXTENSION);
	
	if($extension=="xlsx"){
		$readerLibrary = "Xlsx";
	}else{
		$readerLibrary = "Xls";
	}
	
	$callStartTime = microtime(true);
	
	try{
		//讀取路徑的 excel 檔案
		$reader = IOFactory::createReader($readerLibrary);
		$reader->setReadDataOnly(TRUE);
		$spreadsheet = $reader->load($path.$file);
		
		$worksheet = $spreadsheet->getActiveSheet();
	}catch(Exception $e){
		die('<span style="color:red;">' . date("Y-m-d H:i:s") . ' 資料讀入失敗: ' . $e->getMessage() . '</span><br />');
	}
	
	//總行數
	$highestRow = $worksheet->getHighestRow();
	
	$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
		);
		
		if(count($inventoryListArray)>=500){
			$over500 = true;
			
			$inventoryListArray2[] = 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
			);
		}
	}
}

$array[] = array(
	"supplierInventory" => $supplierInventory,
	"inventoryList" => $inventoryListArray,
	"filename" => $file
);

if($over500==true){
	$array[] = array(
		"supplierInventory" => $supplierInventory,
		"inventoryList" => $inventoryListArray2,
		"filename" => $file
	);
}
?>
<html>
<head>
<title></title>
</head>
<body>
<input type="hidden" name="data" value='<?php echo json_encode($array, JSON_UNESCAPED_UNICODE);?>'>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.6.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jsrsasign/10.4.1/jsrsasign-all-min.js"></script>
<script>
let data = document.getElementsByName("data")[0].value;
$.each(JSON.parse(data), function(index, json){
	let dataJSON = {};
	let itemArray = [];
	
	$.each(json.supplierInventory, function(key, value){
		if(value===1 || value===null){
			dataJSON[key] = null;
		}else if(typeof value=="number"){
			dataJSON[key] = ""+value+"";
		}else{
			dataJSON[key] = value;
		}
	});
	
	$.each(json.inventoryList, function(index, json){
		let itemJSON = {};
		$.each(json, function(key, value){
			if(value===1 || value===null){
				itemJSON[key] = null;
			}else if(typeof value=="number"){
				itemJSON[key] = ""+value+"";
			}else{
				itemJSON[key] = value;
			}
		});
		itemArray.push(itemJSON);
	});
	dataJSON['inventoryList'] = itemArray;
	
	$.ajax({
		type: "POST",
		url: "/tok/modules/TSMCUPLOAD/send2.php",
		data: {
			action: "import",
			file_name: json.filename,
			encData: encryptData(JSON.stringify(dataJSON)),
			supplierInventory: JSON.stringify(dataJSON)
		},
		dataType: "json",
		success: function(data){
			window.close();
		},
		error: function(xhr){
			console.log(xhr.responseText);
		}
	});
});

//加密文字
function encryptData(string=""){
	let privateKey =
		/*
		'-----BEGIN PRIVATE KEY-----' +
		'MIIEwAIBADANBgkqhkiG9w0BAQEFAASCBKowggSmAgEAAoIBAQDB6LSy6Lw1Ft1h' +
		'5g6MLaQUxO/7EOBQSV8XKVIIzJDg1bpd4elRSJVttRa1b6UYtUp3PEAmDEtgHRcS' +
		'dZO0UlgqVFw9IIjhOoJ2Pzfm0Z8BtNuGprxS+8qPFdqVdv5VNH9JYNwjASlLXd7o' +
		'dC88z4aPWNCBpnrNNS3VC5blbsL3XgbBFK3lUv2a7TgA2HLZI1FgN+TvLoMjeBK3' +
		'QbKkH7Jf9rUVT3xlrtFkmq0YBvWOc7y9rVpyhYAf/hBGA3gCwz1YXUQMGmeRZ+Qr' +
		'zMb3zTVltq8f0zlQMA/cBSod/LEYudD6Xk64X4SVmiFWR55NQOfS6E6v+pc6GyBX' +
		'CMB2VfsbAgMBAAECggEBAI+xVz6BEv8wa2thpQQhykaNzdw6HE/Cn1G1kBHvLak/' +
		'GHT7tP9ED1+V4zcefbR6m60SG+NWFwEpbXCDaqoknyr3CsdNdcGZrxXbMRy919vW' +
		'3xrAMiw4ZFl1813xKPWRCoYg3eCeurAb5tRRTLP+gRN22ERLTxMERmtqxC5OzvGp' +
		'E2jWD2VTjr0/e4w5BeJ2w5NMMGonZZospCyzKFCdzsECgfuqw6iWTkCfeLdGTqjM' +
		'6ZTKr/jgtdd8Uq1eYlKAkhHB/b7p7b7WRHmiUBiZX8Qi5PqLuGiD772v60BCEhP6' +
		'2kmE7JxrtCPlplrMGommKKv+LUR3fHwrB+CcLHzwVtECgYEA8n043TQbrKFh97fS' +
		'MRYVKuFgs1C9Ovyxni73Br5cuXk4zd8YI75UC6EcfDjApyVs0hC3PfBhDNxES4vr' +
		'/lpXDaJpOAmjobziNV6Al5eXHN/iqUxDFj+9rO5qTyveQpsB1h/AQ2S6mK0FN9mj' +
		'Lq4OexaF6Z2O33sEub9CYfqdn8kCgYEAzLaN2K0cKIYiHHKEWcqJfjQ+G03C3efb' +
		'GdqbOM+K4OmlGLaoXcElrPp6qvubkH2TpcFP+OMmysxqHPr8diXXxov/svZY1BGz' +
		'X2gG2m9OSKb1xNhPDCCs4BIJW4hX8zohb5L4C6V+vnDFkJAuOcWOxqcVeY7cf0bn' +
		'nNILHISPncMCgYEA0NVpOJAc2VJvJcDow59qAXDyL9vz7C4d5cY6tlMRViaA9kQQ' +
		'7Q+IIC/TcWQK/qgrjMoalbxESKgWJR3O01IIMNTTWxBqHOunwSCvDF7gJFzoPrdP' +
		'+4RPnPQDGEYGd0Q5k6fqk9/XF4/Cs7O6ZoQ+MmFWWLh5iBvRGQsPZ4RHZYkCgYEA' +
		'g7LGGqlzvhZdB5Qc1W9ZeRm/Au3J+dzZtg5PYgMTQ5LMpdONCZPi2x/hnvJzqEdB' +
		'n5d4DZSFNl6/vZkypge+g3s5qzaU+wnQLgbMseZ9pHvVDShj4ld7IEQ6ROq4OkAh' +
		'lpgkf8meeoIud8xqELyqF98SD7v62t8QznDKuTQG+OUCgYEArB+3iISYCZOK2aKV' +
		'+IeCVLw/+OyyXICj2B5UWxTI9hcC5cQFqwC/Ief6SRRWM5LlzV7+oO9O0yzDpkBQ' +
		'5WNIhNrztC3X4ltPr4APGWQ5qYk60NCHNhjKjGH/iyZiMOq6Kn561m91yko9CVyv' +
		'3O0v5N+KhQw05c8v3F7QeXqpHq4=' +
		'-----END PRIVATE KEY-----';
		*/
		'-----BEGIN PRIVATE KEY-----' +
		'MIIEvAIBADANBgkqhkiG9w0BAQEFAASCBKYwggSiAgEAAoIBAQCuwsgIsd5FsfwL' +
		'v1ukUrKPk3MujZmQqSQYafW8bOqIUnva9x1KaHbsPF6LQCLzR/3gjcXwYwXAs/qj' +
		'dGvFzw+l4H2ZhEsTo/tBuDAndHZNS4KV3sPE0LqWDdbo5HwhP3wLLcPTBx+iNldf' +
		'ojfSmWPjL/YSgbF9Use/+vRjk/Y396BWb6ZAoznxW3xclpTA9sB+RDYiz0Nv2sqq' +
		'zGOJTtpHireFOt2BoDmKcFyMUkkXxcBSjwVbz45QfILZR0S9cqIYes5Dy54e/DQi' +
		'fXglEW0vwleHuJyNZlPBcJ5fyCtVbxflottzlHiRTFb148fYicwu8Sx/JjAYMJOi' +
		'm/xUMUyHAgMBAAECggEAJKT3KMuTHe4aOaRgZO5qPqzeZA6Q+sJcrTnaqy3zZ65U' +
		'Bi/21lldRl/KO0yxh227EFDqBmPdGvEiRw/sYUfvsgqGi6Av1eh2kOs8Pamv/Lku' +
		'oppZVnWmCjzQqXjvhlE3g2iPPTqrKWpXm9f5+JDtXv/5ixviybKH6/uFLzQhVs6e' +
		'sS3UUFJYNiL+4Ve8F0khneKWnyLT9OMNTGf3Q5npc5MhBxW7zDQTIGk2gyddxM6h' +
		'Qrf8cQ8RwMpJRQnCaX5pwHLrviDaAWkLCPpTtigg0QJNp7pxQKtV2ebSc8hj+a4m' +
		'Tp30dPGLJO7iCDBAB9SQp+vN06V6x9Mt993poVHi4QKBgQDlp6gynEqig2rjwOaw' +
		'hKx9yyqGTvnrIyaneYfhC1YyZfpCfUWM6n8ex9z3sWKjzWg0BPh9TFqZf+6b5r0a' +
		'6L/uhB7dDeJhKFjWLwPD9PSWgngORKTGqjj8S6PT4+gOZVuyZ7Gl50nvbUA+h/BS' +
		'WZMOiDs26vLrfsXeznu7/nPgfwKBgQDCzwjsbgL2bbGccH5ZUYqpsvwYMT/h76bL' +
		'rvPjtNSbjupGatOxDXaWQuxi36uO26CJaLnUmy8CAbJp9ZNgWnBlbvCGEv0+Qzid' +
		'ShMxs4ZV0UK13l3/e+MKFuQlKKASKz08yrnl8raoKvoLaA8ZsM3Siz43dhvTH86s' +
		'bRKMumuP+QKBgFM8PdkCDobyTKZ5fuMRYxdAsIv3SdtwZQ0kHhxxqG8hyN+aWb/m' +
		'Kea1F6BqmU63HwJjntCDAP9Lf+bfbUC4hP/kwO7rnlYKcpBGf/3g5Vk7tlmthW8/' +
		'uOGwZJNisfOtxttVe+yedIyjszo2uOKP9p24wIV6CEtUKlFD01LiuYrvAoGAGjGU' +
		'VZYFN/VP6GUH2CKqrl6unuIgYNTEbKa9jGSIIdJ5lbZPeIMC6vlcp37DppXJZDB8' +
		'rCWhkHeB8dVfV5lRe5ILLTvoaTLIrf2Dzy8AZRGaErIvmWajBHxHeOeZflfqB/6K' +
		'OG0GOl5s+JOpVB79zc2DNBnha+Xe+DhCQkHerKECgYARZjGwfZyUQd89C+osyB+a' +
		'9vuDSIm/Vmz3mNIwIA+07QrdMIvEbU80E7tjbcgK0pkP8LV0rFrnX6E4RYIxz/em' +
		'RiBpkcw4BjOv56+2lyJ6xfOVB1HbOQZzyIkvuaCMsbsAaBaWAtGP7oE/VJehgri1' +
		'BBQJ2r6Zo0jiPTnXBeWC3Q==' +
		'-----END PRIVATE KEY-----'
	
	//初始化
	let sig = new KJUR.crypto.Signature({"alg": "SHA256withRSA"});
	
	//初始化產生簽證
	sig.init(privateKey);   // rsaPrivateKey of RSAKey object
	
	//更新字串
	sig.updateString(string);
	
	//計算簽證
	let sigValueHex = sig.sign();
	return hextob64(sigValueHex);
}
</script>
</body>
</html>