BIM-Monitor/script/php/get_monitor_chart.php

<?php
include("./connect_sql.php");
$json = file_get_contents('../../r03/display_info.json');
$display_info = json_decode($json, true);
$type = "";
$unit = "";
$label = "";
$ajax = [];

if (isset($_GET["monitor"])) {
    $monitor = $_GET["monitor"];
    $type = explode('-', $monitor)[0];
}
$chart = [];
$key = array_search($type, array_column($display_info, 'type'));
$unit = $display_info[$key]["unit"];
$label = $display_info[$key]["label"];
switch ($type) {
    case "SIS":
    case "SID":
        //$display .= '],[Depth';
        $sql = "  SELECT [Date],count(DISTINCT [Depth])
        FROM [BIMMonitor].[dbo].[{$type}_Data] WHERE [EquipmentID] = '{$monitor}' GROUP BY [Date]";
        $stmt = sqlsrv_query($conn, $sql);
        $depth = "";
        while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_NUMERIC)) {
            $depthCount = $row[1];
        }
        for ($i = 2; $i < $depthCount; $i++) {
            $depth = $depth . ",[" . $i * 0.5 . "]";
        }
        $sql = "SELECT *
        FROM (
                    SELECT [Date], [TotalDisplacement], [Depth], [WarningValue], [ActionValue]
            FROM [BIMMonitor].[dbo].[{$type}_Data]
            WHERE [EquipmentID] = '{$monitor}' AND [WarningValue] <> 'NULL' AND [WarningValue] <> '---'
                ) o
                PIVOT (
                    MAX([TotalDisplacement])
                    FOR [Depth] IN ([0.5] ${depth})
                ) n;";
        break;

    case "TI":
        $sql = "SELECT *
        FROM (
                    SELECT [Date], [Direction], [TotalTilt], '±264' AS [WarningValue], '±330' AS [ActionValue]
            FROM [BIMMonitor].[dbo].[TI_Data]
            WHERE [EquipmentID] like '%{$monitor}%'
                ) o
                PIVOT (
                    MAX([TotalTilt])
                    FOR [Direction] IN ([1-3], [2-4])
                ) n;";
        break;

    case "SB":
        $sql = "SELECT [SB_Data].[Date], [TotalSettlement], [WarningValue], [ActionValue]
        FROM [SB_Data] JOIN [SB_Warning] ON  [SB_Data].[EquipmentID] = [SB_Warning].[EquipmentID]
        WHERE [SB_Data].[EquipmentID] = '{$monitor}';";
        break;

    case "SM":
        $sql = "SELECT [Date], [TotalSettlement], '±30' AS [WarningValue], '±50' AS [ActionValue]
        FROM [SM_Data]
        WHERE [EquipmentID] = '{$monitor}';";
        break;
    case "SP":
        $sql = "SELECT [SP_Data].[Date], [WaterPressure], [HighWarningValue], [LowWarningValue],[HighActionValue],[LowActionValue]
        FROM [SP_Data] JOIN [SP_Warning] ON  [SP_Data].[EquipmentID] = [SP_Warning].[EquipmentID]
        WHERE [SP_Data].[EquipmentID] = '{$monitor}';";
        break;
}

$fetchResult = sqlsrv_query($conn, $sql);
while ($row = sqlsrv_fetch_array($fetchResult, SQLSRV_FETCH_ASSOC)) {
    array_push($chart, $row);
}

if ($fetchResult === 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["label"] = $label;
$ajax["unit"] = $unit;
$ajax["chart"] = $chart;
echo (json_encode($ajax));