<?php
$txtTable = [];


$root    = './assets/API/Universal/Revit/';
//$folder = $_GET["folder"];
$paths = array_diff(scandir($root), array('.', '..', 'Thumbs.db','MEP','other'));
foreach ($paths as $value) {
    $path = scandir($root . "/" . $value);
    foreach ($path as $txt) {
        if (str_contains($txt, '.txt')) {
            $txt = $root . "/" . $value."/".$txt;
            if (file_exists($txt)) {
                $str = file_get_contents($txt);
                $str = str_replace("、",",",$str);
                $array = explode("：", $str);
                $data = [];
                for ($i = 1; $i < 10; $i++) {
                    
                    if ($i != 9) {
                        $values = explode("\r\n", $array[$i]);
                        array_push($data,$values[0]);
                        
                    } else {                        
                        array_push($data,$array[$i]);
                    }
                    
                }
                array_push($data,$root . "/" . $value);
                array_push($txtTable,$data);
                
            }            
        }
    }
}
print_r($txtTable);
$serverName = "GMGIS\SQLEXPRESS"; //test server 201
$username = 'BIMuser';
$password = 'Component3444';
$dbname = 'BIMComponents';

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

for ($i = 0; $i < count($txtTable); $i++) {      
    $sql = "INSERT INTO [BIMComponents].[dbo].[API_Table]
([APIName]
,[software]
,[detail]
,[revitVersion]
,[operate]
,[type]
,[keyword]
,[source]
,[notes]
,[filePath]
)
VALUES
('".$txtTable[$i][0]."'
,'".$txtTable[$i][1]."'
,'".$txtTable[$i][2]."'
,'".$txtTable[$i][3]."'
,'".$txtTable[$i][4]."'
,'".$txtTable[$i][5]."'
,'".$txtTable[$i][6]."'
,'".$txtTable[$i][7]."'
,'".$txtTable[$i][8]."'
,'".$txtTable[$i][9]."'
);";

    //sqlsrv_query($conn, $sql);
    echo($sql."<br>");
}

sqlsrv_close($conn);