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); } }