<?php
if ($_FILES['excel']['error'] === UPLOAD_ERR_OK) {
    if (file_exists('./upload/' . $_FILES['excel']['name'])) {
        echo '檔案已存在。<br/>';
    } else {
        $file = $_FILES['excel']['tmp_name'];
        echo $file;
        echo $_FILES['excel']['name'];
        $dest = './upload/' . $_FILES['excel']['name'];

        # 將檔案移至指定位置
        move_uploaded_file($file, $dest);
        $project_id = $_POST["project_id"];
        include("sql.php");
        $account = "maabim";
        try {
            $pdo = new PDO('sqlsrv:Server=' . $hostname . ';Database=' . $db_name, $username, $password);
            $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch (PDOException $e) {
            echo $e;
            echo json_encode('Error connecting to the server.');
            die();
        }

        /** PHPExcel */
        require_once "../../lib/excel/PHPExcel.php";
        /** PHPExcel_IOFactory */
        require_once "../../lib/excel/PHPExcel/IOFactory.php";
        $reader = PHPExcel_IOFactory::createReader('Excel2007');
        $PHPExcel = $reader->load($dest);

        for ($i = 1; $i <= 7; $i++) {
            if ($i != 3) {
                $sheet = $PHPExcel->getSheet($i); // 讀取第一個工作表(編號從 0 開始)
                $highestRow = $sheet->getHighestRow(); // 取得總列數
                for ($row = 2; $row <= $highestRow; $row++) {
                    $data = [];
                    for ($column = 0; $column <= 2; $column++) {
                        $val = $sheet->getCellByColumnAndRow($column, $row)->getValue();
                        if ($column == 2 && $val === NULL || $val === '') {
                            break 2;
                        }
                        $data[$column] = $val;

                        echo $data[$column] . ' ';
                    }
                    $sth = $pdo->prepare("INSERT INTO [21000X].[dbo].[File_Table]
                   ([project_id]
                   ,[SN]
                   ,[type_id]
                   ,[original_name]
                   ,[filename])
             VALUES
                   ('".$project_id."',
                   '" . $data[0] . "',
                   '" . $i . "',
                   '" . $data[1] . "',
                   '" . $data[2] . "')");
                    $sth->execute();
                    echo "<br />";
                }
            } else if ($i == 3) {
                $sheet = $PHPExcel->getSheet($i); // 讀取第一個工作表(編號從 0 開始)
                $highestRow = $sheet->getHighestRow(); // 取得總列數
                for ($row = 2; $row <= $highestRow; $row++) {
                    $data = [];
                    for ($column = 0; $column <= 5; $column++) {
                        $val = $sheet->getCellByColumnAndRow($column, $row)->getValue();
                        if ($column == 2 && $val === NULL || $val === '') {
                            break 2;
                        }
                        $data[$column] = $val;

                        echo $data[$column] . ' ';
                    }
                    $sth = $pdo->prepare("INSERT INTO [21000X].[dbo].[Blueprint]
                   ([project_id]
                   ,[SN]
                   ,[blueprint_id]
                   ,[type_id]
                   ,[original_name]
                   ,[keyword]
                   ,[notes]
                   ,[filename]
                   )
             VALUES
                   ('".$project_id."',
                   '" . $data[0] . "',
                   '" . $data[1] . "',
                   '" . $i . "',
                   '" . $data[2] . "',
                   '" . $data[3] . "',
                   '" . $data[4] . "',
                   '" . $data[5] . "')");
                    $sth->execute();
                    echo "<br />";
                }
            }
        }
    }
}
