<?php

if (isset($_GET['project_id'])) {
    include("sql.php");
    $project_id = implode("', '", $_GET['project_id']);
    $type_id = implode("', '", $_GET['type_id']);
    $notes_name = implode("', '", $_GET['notes_name']);
    $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));
    }

    if(in_array("3", $_GET['type_id'])) {
        $sql = "SELECT DISTINCT [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 [Blueprint].[project_id] IN ('$project_id') AND [type_id] IN ('$type_id') AND [notes] IN ('$notes_name') 
            AND CONCAT(
                [Blueprint].[project_id] ,' '
                ,[SN] ,' '
                ,[blueprint_id] ,' '
                ,[original_name] ,' '
                ,[keyword] ,' '
                ,[notes] ,' '
                ,[filename]) like '%".$search."%'
            UNION
            SELECT DISTINCT [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]
            WHERE [File_Table].[project_id] IN ('$project_id') AND [type_id] IN ('$type_id')
            AND CONCAT(
                [File_Table].[project_id],' '
                ,[SN],' '
                ,[type_id],' '
                ,[original_name],' '
                ,[filename],' ') like '%".$search."%';";
    } else {
        $sql = "SELECT DISTINCT [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]
            WHERE [File_Table].[project_id] IN ('$project_id') AND [type_id] IN ('$type_id')
            AND CONCAT(
                [File_Table].[project_id],' '
                ,[SN],' '
                ,[type_id],' '
                ,[original_name],' '
                ,[filename],' ') like '%".$search."%';";
    }
    
    $fetchResult = sqlsrv_query($conn, $sql);
    while ($row = sqlsrv_fetch_array($fetchResult, SQLSRV_FETCH_ASSOC)) {
        array_push($array ,$row);
    }
    echo json_encode($array,JSON_UNESCAPED_UNICODE);
} else {
    die("post error!");
}

?>