BIM-Monitor/script/php/get_monitor_table.php

<?php
include("./connect_sql.php");
$json = file_get_contents('../../r03/display_info.json');
$display_info = json_decode($json, true);

$type = "";

if (isset($_GET["monitor"])) {
    $monitor = $_GET["monitor"];
    $type = explode('-', $monitor)[0];
    /*if ($type == "SIS" || $type == "SID") {
        $monitor .= "A";
    }*/
}

$table = [];
$unit = "";
$haveDepth = false;
$haveDirection = false;
$key = array_search($type, array_column($display_info, 'type'));
if ($key !== false) {
    $display = $display_info[$key]["display"];
    $unit = $display_info[$key]["unit"];
    $haveDepth = $display_info[$key]["haveDepth"];
    $haveDirection = $display_info[$key]["haveDirection"];
    if ($haveDepth) {
        $display .= '],[Depth';
        $sql = "  SELECT [Date],count(DISTINCT [Depth])
        FROM [BIMMonitor].[dbo].[{$type}_Data] WHERE [EquipmentID] = '{$monitor}' GROUP BY [Date]";


        $stmt = sqlsrv_query($conn, $sql);
        $fetchResult = sqlsrv_query($conn, $sql);
        $depth = "";
        while ($row = sqlsrv_fetch_array($fetchResult, SQLSRV_FETCH_NUMERIC)) {
            $depthCount = $row[1];
        }
        for ($i = 1; $i < $depthCount; $i++) {
            $depth = $depth . ",[" . $i * 0.5 . "]";
        }
        $sql = "  SELECT *
        FROM (
            SELECT [Date],[TotalDisplacement],[Depth]
          FROM [BIMMonitor].[dbo].[{$type}_Data] WHERE [EquipmentID] = '{$monitor}'
        ) o
        PIVOT (
            MAX([TotalDisplacement])
            FOR [Depth] IN ([TOP] ${depth})
        ) n;";
    } else if ($haveDirection) {
        $sql = "  SELECT *
        FROM (
            SELECT [Date],[Direction],[TotalTilt]
          FROM [BIMMonitor].[dbo].[{$type}_Data] WHERE [EquipmentID] like '%{$monitor}%'
        ) o
        PIVOT (
            MAX([TotalTilt])
            FOR [Direction] IN ([1-3], [2-4])
        ) n;";
    } else {
        $sql = "SELECT [Date],[{$display}] FROM [{$type}_Data] WHERE [EquipmentID] = '{$monitor}';";
    }

    $stmt = sqlsrv_query($conn, $sql);
    $fetchResult = sqlsrv_query($conn, $sql);
    while ($row = sqlsrv_fetch_array($fetchResult, SQLSRV_FETCH_NUMERIC)) {
        array_push($table, $row);
    }
    if ($stmt === false) {
        if (($errors = sqlsrv_errors()) != null) {
            foreach ($errors as $error) {
                echo "SQLSTATE: " . $error['SQLSTATE'] . "<br />";
                echo "code: " . $error['code'] . "<br />";
                echo "message: " . $error['message'] . "<br />";
                echo ($sql . "<br />");
                var_dump($row);
            }
        }
    }
}

$ajax["table"] = $table;
$ajax["unit"] = $unit;
$ajax["haveDepth"] = $haveDepth;
$ajax["haveDirection"] = $haveDirection;
echo (json_encode($ajax));