<?php
require '../../../vendor/autoload.php';
include("../sql.php");

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

$type = array();
$i = 0;
while (isset($_POST["type{$i}"])) {
    $type[$i] = $_POST["type{$i}"];
    $i++;
}


$ajax = array();
$connectionInfo = array("Database" => "$dbname", "UID" => "$username", "PWD" => "$password", "CharacterSet" => "UTF-8");
$conn = sqlsrv_connect($hostname, $connectionInfo);
if ($conn === false) {
    die(print_r(sqlsrv_errors(), true));
}

$Category_List = [];
if (isset($_POST['file'])) {
    $file = $_POST['file'];
    $project_id = $_POST['projectId'];
    $project_name = $_POST['projectName'];
    $category_id = $_POST['categoryId'];
} else {
    header("Location: ./upload.html#error");
}

$filename = "uploads/{$file}";
$importinfo = array();

$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(false);
$spreadsheet = $reader->load($filename);
$sheetCount = $spreadsheet->getSheetCount();
$all = $spreadsheet->getSheetNames();

$sql = "DELETE FROM [Blueprint] WHERE [project_id] = ?";
$stmt = sqlsrv_query($conn, $sql, array($project_id));
$sql = "DELETE FROM [File_table] WHERE [project_id] = ?";
$stmt = sqlsrv_query($conn, $sql, array($project_id));

$sql =  "IF NOT EXISTS (SELECT [project_id] FROM [Project_Table] WHERE [project_id] = '{$project_id}')
            BEGIN
                INSERT INTO [dbo].[Project_Table] ([project_id],[project_name],[category_id])
                VALUES
                (?,?,?)
            END";
$stmt = sqlsrv_query($conn, $sql, array($project_id,$project_name,$category_id));
for ($s = 0; $s < $sheetCount; $s++) {
    if ($type[$s] != 'cancel') {

        if (str_contains($all[$s], "_")) {
            $Category = explode("_", $all[$s])[1];
        } else 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 =  "DECLARE @filetype_id INT;
                IF NOT EXISTS (SELECT [type_id] FROM [File_Category] WHERE [type_name] = '{$Category}')
                BEGIN
                    SET @filetype_id = (SELECT [filetype_id] FROM [21000X].[dbo].[File_Type_Table] WHERE [filetype_name] = '{$type[$s]}')
                    INSERT INTO [File_Category] ([type_name],[filetype_id]) VALUES ('{$Category}',@filetype_id);
                END";
        $fetchResult = sqlsrv_query($conn, $sql);

        $sql = "SELECT [type_id] FROM [File_Category] WHERE [type_name] = '{$Category}';";

        $fetchResult = sqlsrv_query($conn, $sql);
        $type_id = 0;
        while ($row = sqlsrv_fetch_array($fetchResult)) {
            $type_id = $row[0];
        }
        array_push($Category_List, $type_id);

        $sheet = $spreadsheet->getSheet($s);
        $cellCollection = $sheet->getCellCollection();
        $column = $cellCollection->getHighestRowAndColumn();
        $data = array();

        for ($i = 2; $i <= $column['row']; $i++) { //行
            $row = array($project_id, $type_id);
            for ($j = 'A'; $j <= $column['column']; $j++) { //列
                $key = $j . $i;
                $value = $sheet->getCell($key)->getValue();
                array_push($row, $value);
            }
            if ($type[$s] == '[Blueprint]') {
                $sql = "INSERT INTO [Blueprint]
                            ([project_id]
                            ,[type_id]
                            ,[SN]
                            ,[blueprint_id]
                            ,[original_name]
                            ,[keyword]
                            ,[notes]
                            ,[filename])
                        VALUES
                            (?,?,?,?,?,?,?,?)";
            } else if ($type[$s] == '[File_Table]') {
                $sql = "INSERT INTO [File_Table]
                            ([project_id]
                            ,[type_id]
                            ,[SN]
                            ,[original_name]
                            ,[filename]
                            ,[keyword])
                        VALUES
                            (?,?,?,?,?,?)";
                array_push($row, 'NULL');
            }
            $stmt = sqlsrv_query($conn, $sql, $row);
            echo "<br>";
        }
        echo "<br>";
        echo "<br>";
    }
}

$Category_List_s = implode(",", $Category_List);
$sql = "IF NOT EXISTS (SELECT [list_id] FROM [Category_List] WHERE [category_list] = '{$Category_List_s}')
            BEGIN
                INSERT INTO [Category_List] ([category_list])
                VALUES ('{$Category_List_s}')
            END";
$fetchResult = sqlsrv_query($conn, $sql);
sqlsrv_close($conn);
header("Location: ./upload.html");