<?php

if (isset($_GET['search'])) {

    include("sql.php");
    //$project_id = $_GET['project_id'];
    $search = $_GET['search'];

    $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));
    }
/*[project_id] = '".$project_id."' AND  */
    $sql = "SELECT [Blueprint].[project_id],[SN],[original_name],[filename],[project_name],[type_id],[category_name],[notes],[keyword] FROM [21000X].[dbo].[Blueprint] 
    INNER JOIN [Project_Table] ON [Blueprint].[project_id] = [Project_Table].[project_id] 
    INNER JOIN [Construction_Category] ON [Project_Table].[category_id] = [Construction_Category].[category_id]
      WHERE CONCAT(
          [Blueprint].[project_id] ,' '
          ,[SN] ,' '
          ,[blueprint_id] ,' '
          ,[original_name] ,' '
          ,[keyword] ,' '
          ,[notes] ,' '
          ,[filename]) like '%".$search."%'
          UNION
    SELECT [File_Table].[project_id],[SN],[original_name],[filename],[project_name],[type_id],[category_name],null,[keyword] FROM [21000X].[dbo].[File_Table] 
    INNER JOIN [Project_Table] ON [File_Table].[project_id] = [Project_Table].[project_id]
    INNER JOIN [Construction_Category] ON [Project_Table].[category_id] = [Construction_Category].[category_id]
            AND CONCAT(
            [File_Table].[project_id],' '
          ,[SN],' '
          ,[type_id],' '
          ,[original_name],' '
          ,[filename],' '
            ) like '%".$search."%';";


    $fetchResult = sqlsrv_query($conn, $sql);
    //$array = sqlsrv_fetch_array($fetchResult);
    while ($row = sqlsrv_fetch_array($fetchResult)) {
        array_push($array, $row);
    }
    echo json_encode($array, JSON_UNESCAPED_UNICODE);
}