BIM-Monitor/script/php/get_abnormal.php

<?php
include("./connect_sql.php");
$json = file_get_contents('../../r03/equipment_list.json');
$equipment_list = json_decode($json, true);
$type = "";
$normal_count = 0;
$abnormal_count = 0;
$ajax = [];
$wellTBA = [];

if (isset($_GET["type"])) {
    $type = $_GET["type"];
}

switch ($type) {
    case "SIS":
    case "SID":
        $sql = "SELECT [EquipmentID] FROM [BIMMonitor].[dbo].[{$type}_Data]
        WHERE [WarningValue] <> 'NULL' AND [{$type}_Data].[Date] = (SELECT TOP 1 [{$type}_Data].[Date] FROM [{$type}_Data] GROUP BY [Date]  ORDER BY [Date] desc)
        AND (try_convert(float,[TotalDisplacement]) > try_convert(float,(SELECT TOP 1 RIGHT([WarningValue], len([WarningValue])-1) FROM [{$type}_Data] WHERE [WarningValue] <> 'NULL'))
        OR try_convert(float,[TotalDisplacement]) < -try_convert(float,(SELECT TOP 1 RIGHT([WarningValue], len([WarningValue])-1) FROM [{$type}_Data] WHERE [WarningValue] <> 'NULL')))
        GROUP BY [EquipmentID]";
        break;

    case "TI":
        $sql = "SELECT [EquipmentID]
        FROM [BIMMonitor].[dbo].[TI_Data]
        WHERE [TI_Data].[Date] = (SELECT TOP 1 [TI_Data].[Date] FROM [TI_Data] GROUP BY [Date]  ORDER BY [Date] desc)
        AND [TotalTilt] <> null
        AND (try_convert(float,[TotalTilt]) > 264 OR try_convert(float,[TotalTilt]) < -264 ) GROUP BY [EquipmentID]";
        break;

    case "SB":
        $sql = "SELECT [SB_Data].[EquipmentID]
        FROM [SB_Data] JOIN [SB_Warning] ON  [SB_Data].[EquipmentID] = [SB_Warning].[EquipmentID] 
        WHERE [SB_Data].[Date] = (SELECT TOP 1 [SB_Data].[Date] FROM [SB_Data] GROUP BY [Date]  ORDER BY [Date] desc)
        AND (try_convert(float,[TotalSettlement]) > try_convert(float,(SELECT TOP 1 RIGHT([WarningValue], len([WarningValue])-1) FROM [SB_Warning]))
        OR try_convert(float,[TotalSettlement]) < -try_convert(float,(SELECT TOP 1 RIGHT([WarningValue], len([WarningValue])-1) FROM [SB_Warning])))";
        break;

    case "SM":
        $sql = "SELECT [EquipmentID] FROM [SM_Data]
        WHERE [SM_Data].[Date] = (SELECT TOP 1 [SM_Data].[Date] FROM [SM_Data] GROUP BY [Date]  ORDER BY [Date] desc)
        AND (try_convert(float,[TotalSettlement]) > 30 OR try_convert(float,[TotalSettlement]) < -30 ) GROUP BY [EquipmentID]";
        break;
    case "SP":
        $sql = "SELECT [EquipmentID] FROM [SP_Data]
        WHERE [SP_Data].[Date] = (SELECT TOP 1 [SP_Data].[Date] FROM [SP_Data] GROUP BY [Date]  ORDER BY [Date] desc)
        AND (try_convert(float,[WaterPressure]) > (SELECT TOP 1 [HighWarningValue] FROM [SP_Warning]) OR try_convert(float,[WaterPressure]) < (SELECT TOP 1 [LowWarningValue] FROM [SP_Warning]) )
        AND [WaterPressure] NOT like '%--%' GROUP BY [EquipmentID];";
        break;
}

$fetchResult = sqlsrv_query($conn, $sql);
while ($row = sqlsrv_fetch_array($fetchResult, SQLSRV_FETCH_NUMERIC)) {
    if (in_array($row[0], $equipment_list[$type])) {
        array_push($wellTBA, $row);
        $abnormal_count++;
    }
}
$all_count = 0;
/*$sql = "SELECT [EquipmentID] FROM [BIMMonitor].[dbo].[{$type}_Data] GROUP BY [EquipmentID];";
$fetchResult = sqlsrv_query($conn, $sql);
while ($row = sqlsrv_fetch_array($fetchResult, SQLSRV_FETCH_NUMERIC)) {
    $all_count++;
}*/
$all_count = count($equipment_list[$type]);
$normal_count = $all_count - $abnormal_count;
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['wellTBA'] = $wellTBA;
$ajax['normal_count'] = $normal_count;
$ajax['abnormal_count'] = $abnormal_count;
echo json_encode($ajax);