'NULL' AND [{$type}_Data].[Date] = (SELECT TOP 1 [{$type}_Data].[Date] FROM [{$type}_Data] GROUP BY [Date] ORDER BY [Date] desc)
AND (convert(float,[TotalDisplacement]) > convert(float,(SELECT TOP 1 RIGHT([WarningValue], len([WarningValue])-1) FROM [{$type}_Data] WHERE [WarningValue] <> 'NULL'))
OR convert(float,[TotalDisplacement]) < -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 (convert(float,[TotalTilt]) > 264 OR 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 (convert(float,[TotalSettlement]) > convert(float,(SELECT TOP 1 RIGHT([WarningValue], len([WarningValue])-1) FROM [SB_Warning]))
OR convert(float,[TotalSettlement]) < -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 (convert(float,[TotalSettlement]) > 30 OR 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 (convert(float,[WaterPressure]) > (SELECT TOP 1 [HighWarningValue] FROM [SP_Warning]) OR convert(float,[WaterPressure]) < (SELECT TOP 1 [LowWarningValue] FROM [SP_Warning]) )
AND [WaterPressure] <> '---' 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'] . "
";
echo "code: " . $error['code'] . "
";
echo "message: " . $error['message'] . "
";
echo ($sql . "
");
var_dump($row);
}
}
}
$ajax['wellTBA'] = $wellTBA;
$ajax['normal_count'] = $normal_count;
$ajax['abnormal_count'] = $abnormal_count;
echo json_encode($ajax);