public InfluxDBHistoryResult DbQuery_Alarms(List <string> DeviceIDs, string timespan = "1d", string AlarmType = "", string AlarmLevel = "", int PageSize = 2000, int PageIndex = 1)
{
if (AlarmType == "0")
{
AlarmType = "";
}
if (AlarmLevel == "0")
{
AlarmLevel = "";
}
///查询的数量最大为10000条，influxdb系统限制最大查询数量
if (PageSize > 10000)
{
PageSize = 10000;
}
try
{
string sql = "SELECT * FROM " + _fakeMeasurementPrefix + "_" + _AlarmMeasurement;
string where = " WHERE time > now() - " + timespan;
if (!string.IsNullOrWhiteSpace(AlarmType))
{
where += " and tag_level='" + AlarmType + "'";
}
if (!string.IsNullOrWhiteSpace(AlarmLevel))
{
where += " and tag_type='" + AlarmLevel + "'";
}
if (DeviceIDs != null && DeviceIDs.Count > 0)
{
string str = "";
for (int i = 0; i < DeviceIDs.Count; i++)
{
str += " tag_did='" + DeviceIDs[i] + "' or";
}
if (str != "")
{
str = str.Remove(str.Length - 2, 2);
where += " and (" + str + ")";
}
}
sql += where;
sql += " ORDER BY time DESC LIMIT " + PageSize + " OFFSET " + (PageIndex - 1) * PageSize;
InfluxDBHistoryResult datas = new InfluxDBHistoryResult();
datas.PageSize = PageSize;
datas.PageIndex = PageIndex;
try
{
string queryCount = "SELECT COUNT(field_io_alarm_date) FROM " + _fakeMeasurementPrefix + "_" + _AlarmMeasurement + " " + where;
var readerCountResponse = _influx.Client.QueryAsync(queryCount, _dbName);
string query = sql;
if (readerCountResponse != null && readerCountResponse.Result.Count() > 0)
{
Serie s = readerCountResponse.Result.Last();
if (s != null && s.Values.Count == 1)
{
if (s.Values[0][1] != null)
{
datas.RecordCount = int.Parse(s.Values[0][1].ToString());
}
}
}
datas.PageCount = datas.RecordCount / datas.PageSize;
if (datas.RecordCount % datas.PageSize != 0)
{
datas.PageCount++;
}
if (datas.PageCount == 0)
{
datas.PageIndex = 0;
}
var readerResponse = _influx.Client.QueryAsync(query, _dbName);
datas.Seres = readerResponse.Result;
datas.ReturnResult = true;
if (+datas.RecordCount > 0)
{
datas.Msg = "共计有" + datas.RecordCount + "条数据,当前" + datas.PageIndex + "/" + datas.PageCount + ",每页显示" + datas.PageSize;
}
else
{
datas.Msg = "没有符合查询条件的数据";
}
}
catch
{
datas.ReturnResult = false;
datas.Msg = "查询异常";
}
return(datas);
}
catch (Exception ex)
{
DisplayException(new Exception("ERR10015" + ex.Message));
return(null);
}
}
/// <summary>
/// 读取某个设备报警配置记录
/// </summary>
/// <param name="server"></param>
/// <param name="communication"></param>
/// <param name="device"></param>
/// <param name="SDate"></param>
/// <param name="EDate"></param>
/// <param name="PageSize"></param>
/// <param name="PageIndex">从1开始</param>
/// <returns></returns>
public async Task <InfluxDBHistoryResult> DbQuery_AlarmConfigs(string serverid, string communicationid, string deviceid, DateTime SDate, DateTime EDate, int PageSize, int PageIndex)
{
if (PageSize > 10000)
{
PageSize = 10000;
}
try
{
InfluxDBHistoryResult datas = new InfluxDBHistoryResult();
datas.PageSize = PageSize;
datas.PageIndex = PageIndex;
string where = "time>='" + SDate.ToString("yyyy-MM-dd HH:mm:ss") + "' and time<='" + EDate.ToString("yyyy-MM-dd HH:mm:ss") + "' and tag_sid='" + serverid + "' and tag_cid='" + communicationid + "' and tag_did='" + deviceid + "'";
try
{
string queryCount = "SELECT COUNT(field_command_date) FROM " + _fakeMeasurementPrefix + "_" + _AlarmConfigMeasurement + " where " + where;
var readerCountResponse = await _influx.Client.QueryAsync(queryCount, _dbName);
string query = "select * from " + _fakeMeasurementPrefix + "_" + _AlarmConfigMeasurement + " where " + where + " ORDER BY time DESC LIMIT " + PageSize + " OFFSET " + (PageIndex - 1) * PageSize + " ";
if (readerCountResponse != null && readerCountResponse.Count() > 0)
{
Serie s = readerCountResponse.Last();
if (s != null && s.Values.Count == 1)
{
if (s.Values[0][1] != null)
{
datas.RecordCount = int.Parse(s.Values[0][1].ToString());
}
}
}
datas.PageCount = datas.RecordCount / datas.PageSize;
if (datas.RecordCount % datas.PageSize != 0)
{
datas.PageCount++;
}
if (datas.PageCount == 0)
{
datas.PageIndex = 0;
}
var readerResponse = await _influx.Client.QueryAsync(query, _dbName);
datas.Seres = readerResponse;
datas.ReturnResult = true;
if (+datas.RecordCount > 0)
{
datas.Msg = "共计有" + datas.RecordCount + "条数据,当前" + datas.PageIndex + "/" + datas.PageCount + ",每页显示" + datas.PageSize;
}
else
{
datas.Msg = "没有符合查询条件的数据";
}
}
catch
{
datas.ReturnResult = false;
datas.Msg = "查询异常";
}
return(datas);
}
catch (Exception ex)
{
DisplayException(new Exception("ERR10015" + ex.Message));
return(null);
}
}
/// <summary>
/// 读取某个设备的历史统计数据
/// </summary>
/// <param name="server"></param>
/// <param name="communication"></param>
/// <param name="device"></param>
/// <param name="SDate"></param>
/// <param name="EDate"></param>
/// <param name="PageSize"></param>
/// <param name="PageIndex">从1开始</param>
/// <returns></returns>
public InfluxDBHistoryResult DbQuery_HistoryStatics(string serverid, string communicationid, string deviceid, DateTime SDate, DateTime EDate, int PageSize, int PageIndex, string orderAction, string timespan, string returnfields)
{
string tablename = serverid + "_" + deviceid;
//条件限制最大页是10000条，超过此数量，强制归为最大数量
if (PageSize > 10000)
{
PageSize = 10000;
}
try
{
InfluxDBHistoryResult datas = new InfluxDBHistoryResult();
datas.PageSize = PageSize;
datas.PageIndex = PageIndex;
try
{
string queryCount = "SELECT COUNT(*) FROM " + _fakeMeasurementPrefix + "_" + tablename + " where time>='" + SDate.ToString("yyyy-MM-dd HH:mm:ss") + "' and time<='" + EDate.ToString("yyyy-MM-dd HH:mm:ss") + "' group by time(" + timespan + ")";
var readerCountResponse = _influx.Client.QueryAsync(queryCount, _dbName).Result;
string query = "select " + returnfields + " from " + _fakeMeasurementPrefix + "_" + tablename + " where time>='" + SDate.ToString("yyyy-MM-dd HH:mm:ss") + "' and time<='" + EDate.ToString("yyyy-MM-dd HH:mm:ss") + "' group by time(" + timespan + ") ORDER BY time " + orderAction + " LIMIT " + PageSize + " OFFSET " + (PageIndex - 1) * PageSize + " ";
if (readerCountResponse != null && readerCountResponse.Count() > 0)
{
Serie s = readerCountResponse.Last();
if (s != null && s.Values.Count >= 1)
{
datas.RecordCount = s.Values.Count;
}
}
datas.PageCount = datas.RecordCount / datas.PageSize;
if (datas.RecordCount % datas.PageSize != 0)
{
datas.PageCount++;
}
if (datas.PageCount == 0)
{
datas.PageIndex = 0;
}
var readerResponse = _influx.Client.QueryAsync(query, _dbName).Result;
datas.Seres = readerResponse;
datas.ReturnResult = true;
if (+datas.RecordCount > 0)
{
datas.Msg = "共计有" + datas.RecordCount + "条数据,当前" + datas.PageIndex + "/" + datas.PageCount + ",每页显示" + datas.PageSize;
}
else
{
datas.Msg = "没有符合查询条件的数据";
}
}
catch
{
datas.ReturnResult = false;
datas.Msg = "查询异常";
}
return(datas);
}
catch (Exception ex)
{
DisplayException(new Exception("ERR10015" + ex.Message));
return(null);
}
}
