';
} else {
$filepath = 'uploads/' . $_FILES['excel']['name'];
move_uploaded_file($_FILES['excel']['tmp_name'], $filepath);
$projectName = $_POST['projectName'];
$constructionName = $_POST['constructionName'];
}
$excelData = array();
$file = $filepath;
$extension = pathinfo($file, PATHINFO_EXTENSION);
if ('csv' == $extension) {
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Csv();
} else if ('xls' == $extension) {
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
} else {
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
}
$reader->setReadDataOnly(true);
$reader->setReadEmptyCells(true);
$spreadsheet = $reader->load($file);
$sheetCount = $spreadsheet->getSheetCount();
$header = $spreadsheet->getSheetByName("表頭");
$date_value = $header->getCell('J2')->getValue();
$timestamp = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToTimestamp($date_value);
$date = date('Y/m/d', $timestamp);
$equipment_name = "";
for ($s = 0; $s < $sheetCount; $s++) {
$data = array();
$sheet = $spreadsheet->getSheet($s);
$sheetName = $spreadsheet->getSheetNames()[$s];
if (array_search($sheetName, $not_read) === false) {
//echo ("資料表:" . $sheetName . " ");
$cellCollection = $sheet->getCellCollection();
$column = $cellCollection->getHighestRowAndColumn();
$index_key = check_sheet($sheetName, $equipment_data);
$type = $equipment_data[$index_key]["type"]; //儀器類型
$startC = $equipment_data[$index_key]["startC"]; //起始欄位 EX:A
$endC = $equipment_data[$index_key]["endC"]; //結尾欄位 EX:H
$skipC = $equipment_data[$index_key]["skipC"]; //跳過欄位 EX:G
$startR = $equipment_data[$index_key]["startR"]; //起始列數 EX:10
$endR = $equipment_data[$index_key]["endR"] !== 0 ? $equipment_data[$index_key]["endR"] : $column['row']; //結尾列數 若為0則總列數
for ($i = $startR; $i <= $endR; $i++) {
$skip = false;
$first = true;
$row = array();
$col_count = 0;
array_push($row, $date);
if (str_contains($sheetName, "SIS") || str_contains($sheetName, "SID")) { //若為SIS取出儀器名稱
if (str_contains($sheetName, "區")) {
$equipment_name = explode("區", $sheetName)[1];
}
if (str_contains($sheetName, "(")) {
$temp = explode("(", $sheetName);
$equipment_name = $temp[0] . $temp[1];
}
$equipment_name = str_replace(" ", "", $equipment_name);
$col_count++;
array_push($row, $equipment_name);
}
for ($j = $startC; $j <= $endC; $j++) {
if ($skipC != $j) {
if($first){
$first = false;
}
$col_count++;
$key = $j . $i;
$value = $sheet->getCell($key)->getCalculatedValue();
if ((str_contains($sheetName, "SIS") || str_contains($sheetName, "SID")) && $j == 'C' && $value == null) { //若為SIS且C行為空，則跳過
$skip = true;
break;
} else if ($j == 'A' && (!str_contains($value, "-") || str_contains($value, "說明"))) { //若儀器資料不含'-'且含'說明'則跳過
$skip = true;
break;
} else if ($j == 'A' && str_contains($sheetName, "TI")) {
$value = str_replace(".", "", $value);
}
array_push($row, $value);
}
}
if (!$skip) {
array_push($data, $row);
$row_merge = str_repeat(", ?", $col_count);
$sql = "BEGIN
IF NOT EXISTS (SELECT [date] FROM [{$type}_Data] WHERE [date] = '{$date}' AND [EquipmentID] = '')
BEGIN
INSERT INTO [{$type}_Data] VALUES (? {$row_merge})
END
END";
$stmt = sqlsrv_query($conn, $sql, $row);
if ($stmt === false) {
if (($errors = sqlsrv_errors()) != null) {
foreach ($errors as $error) {
echo "SQLSTATE: " . $error['SQLSTATE'] . "
";
echo "code: " . $error['code'] . "
";
echo "message: " . $error['message'] . "
";
echo ($sql . "
");
var_dump($row);
}
}
}
}
}
$excelData[$sheetName] = $data;
}
}
if (isset($_SESSION['UserID'])) {
$user_id = $_SESSION['UserID'];
} else {
$user_id = "0000";
}
$sql = "INSERT INTO [Update_Time] ([UserID]) VALUES (?);";
$stmt = sqlsrv_query($conn, $sql, array($_SESSION['UserID']));
if ($stmt === false) {
if (($errors = sqlsrv_errors()) != null) {
foreach ($errors as $error) {
echo "SQLSTATE: " . $error['SQLSTATE'] . "
";
echo "code: " . $error['code'] . "
";
echo "message: " . $error['message'] . "
";
echo ($sql . "
");
var_dump($row);
}
}
}
header("Location: ../../Upload.php?status=success");
} else {
header("Location: ../../Upload.php?status=failed");
}
function check_sheet($sheetName, $equipment_data)
{
$equipment_types = ["SIS", "SID", "SB", "SM", "TI", "VG", "RB", "OW", "SP", "EP", "SS", "EXM", "ETi", "BS"];
foreach ($equipment_types as $type) {
if (str_contains($sheetName, $type)) {
$key = array_search($type, array_column($equipment_data, 'type'));
return $key;
}
}
return null;
}