<?php

include("sql.php");
$category = [];
$project = [];
$type = [];
$notes = [];
$category_type = [];

$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));
}

//get category table
$sql = "SELECT DISTINCT * FROM [21000X].[dbo].[Construction_Category];";
$fetchResult = sqlsrv_query($conn, $sql);
while ($row = sqlsrv_fetch_array($fetchResult, SQLSRV_FETCH_ASSOC)) {
    array_push($category ,$row);
}

//get project table
$sql = "SELECT DISTINCT * FROM [21000X].[dbo].[Project_Table];";
$fetchResult = sqlsrv_query($conn, $sql);
while ($row = sqlsrv_fetch_array($fetchResult, SQLSRV_FETCH_ASSOC)) {
    array_push($project ,$row);
}

//get type table
$sql = "SELECT DISTINCT * FROM [21000X].[dbo].[File_Category];";
$fetchResult = sqlsrv_query($conn, $sql);
while ($row = sqlsrv_fetch_array($fetchResult, SQLSRV_FETCH_ASSOC)) {
    array_push($type ,$row);
}

//get notes table
$sql = "SELECT DISTINCT [Project_Table].[project_id], [notes] FROM [21000X].[dbo].[Project_Table]
    RIGHT JOIN [Blueprint] ON [Project_Table].[project_id] = [Blueprint].[project_id];";
$fetchResult = sqlsrv_query($conn, $sql);
while ($row = sqlsrv_fetch_array($fetchResult, SQLSRV_FETCH_ASSOC)) {
    array_push($notes ,$row);
}

//get category_type table
$sql = "SELECT DISTINCT * FROM [21000X].[dbo].[Category_List];";
$fetchResult = sqlsrv_query($conn, $sql);
while ($row = sqlsrv_fetch_array($fetchResult, SQLSRV_FETCH_ASSOC)) {
    array_push($category_type ,$row);
}

$data["category"] = $category;
$data["project"] = $project;
$data["type"] = $type;
$data["notes"] = $notes;
$data["category_type"] = $category_type;

echo json_encode($data,JSON_UNESCAPED_UNICODE);
sqlsrv_close($conn);  
?>