"$dbname", "UID" => "$username", "PWD" => "$password", "CharacterSet" => "UTF-8");
$conn = sqlsrv_connect($hostname, $connectionInfo);
if ($conn === false) {
die(print_r(sqlsrv_errors(), true));
}
$fileinfo = array(array("計畫編號", "工程類別", "此計畫是否匯入過"));
$file = '00000.xlsx';
if (isset($_GET['file'])) {
$file = "uploads/{$_GET['file']}";
}
$isImport = '是';
$isImportArray = array();
$project_id = $_GET['projectId'];
$category_id = $_GET['categoryId'];
$sql = "SELECT TOP 1 [project_id] FROM [21000X].[dbo].[File_Table] WHERE [project_id] = '{$project_id}'";
$fetchResult = sqlsrv_query($conn, $sql);
$row = sqlsrv_fetch_array($fetchResult);
if (empty($row)) {
$isImport = '否';
}
$sql = "SELECT [category_name] FROM [Construction_Category] WHERE [category_id] = '{$category_id}'";
$fetchResult = sqlsrv_query($conn, $sql);
$category = sqlsrv_fetch_array($fetchResult)[0];
array_push($fileinfo, array($project_id, $category, $isImport));
$importinfo = array(array("類別", "選擇檔案類型", "是否為新的類型"));
$ajax["0_匯入資訊"] = $fileinfo;
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$reader->setReadDataOnly(true);
$reader->setReadEmptyCells(false);
$spreadsheet = $reader->load($file);
$sheetCount = $spreadsheet->getSheetCount();
$all = $spreadsheet->getSheetNames();
for ($s = 0; $s < $sheetCount; $s++) {
if (str_contains($all[$s], "_")) {
$Category = explode("_", $all[$s])[1];
} else if (str_contains($all[$s], ".")) {
$Category = explode(".", $all[$s])[1];
} else {
$Category = $all[$s];
}
$sql = "IF NOT EXISTS (SELECT [type_id] FROM [File_Category] WHERE [type_name] = '{$Category}')
BEGIN
SELECT '是'
END
ELSE
BEGIN
SELECT '否'
END";
$fetchResult = sqlsrv_query($conn, $sql);
while ($row = sqlsrv_fetch_array($fetchResult)) {
array_push($isImportArray, $row[0]);
}
}
for ($s = 0; $s < $sheetCount; $s++) {
$normalCheck = "";
$blueprintCheck = "";
$cancelCheck = "";
$sheet = $spreadsheet->getSheet($s);
$sheetName = $spreadsheet->getSheetNames()[$s];
if (str_contains($sheetName, '成果圖') || str_contains($sheetName, '設計圖')) {
$blueprintCheck = "checked='checked'";
} else {
$normalCheck = "checked='checked'";
}
if (str_contains($sheetName, '目錄') || str_contains($sheetName, '注意')) {
$cancelCheck = "checked='checked'";
}
$cellCollection = $sheet->getCellCollection();
$column = $cellCollection->getHighestRowAndColumn();
$data = array();
for ($i = 1; $i <= $column['row']; $i++) { //行
$row = array();
for ($j = 'A'; $j <= $column['column']; $j++) { //列
$key = $j . $i;
$value = $sheet->getCell($key)->getValue();
array_push($row, $value);
}
array_push($data, $row);
}
$ajax[$sheetName] = $data;
array_push($importinfo, array($sheetName, "
", $isImportArray[$s]));
}
$data["table"] = $ajax;
$data["info"] = $importinfo;
echo json_encode($data, JSON_UNESCAPED_UNICODE);
sqlsrv_close($conn);