BIM-Monitor/script/php/read_excel.php

<?php
set_time_limit(600);
include($_SERVER['DOCUMENT_ROOT'] . "/Authorization/script/php/permission/check_right.php");
include("./API/equipment.php");
include("./connect_sql.php");
require '../../assets/vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

if (isset($_FILES["excel"])) {
    if (0 < $_FILES['excel']['error']) {
        echo 'Error: ' . $_FILES['excel']['error'] . '<br>';
    } 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 ((str_contains($sheetName, "SIS") || str_contains($sheetName, "SID")) && $j == 'C' && (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'] . "<br />";
                                echo "code: " . $error['code'] . "<br />";
                                echo "message: " . $error['message'] . "<br />";
                                echo ($sql . "<br />");
                                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'] . "<br />";
                echo "code: " . $error['code'] . "<br />";
                echo "message: " . $error['message'] . "<br />";
                echo ($sql . "<br />");
                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;
}