/// <summary> /// 以巷道为单位 /// </summary> /// <returns></returns> public static List <PreWarningResultQuery> QueryHoldWarningResult() { List <PreWarningResultQuery> lastedResultEnts = new List <PreWarningResultQuery>(); // 一条巷道,有的记录有两条“突出和超限”,有的记录只有一条“”,我们把两条的合并成一条 Dictionary <int, PreWarningResultQuery> container = new Dictionary <int, PreWarningResultQuery>(); StringBuilder strSql = new StringBuilder(); strSql.Append("SELECT * FROM "); strSql.Append(PreWarningResultViewDbConstNames.VIEW_NAME); strSql.Append(" WHERE "); strSql.Append(PreWarningResultDBConstNames.WARNING_RESULT); strSql.Append(" < 2 "); strSql.Append(" AND " + PreWarningResultDBConstNames.HANDLE_STATUS + " < 3"); // 3指 HANDLED strSql.Append(" AND WARNING_STATUS = 1"); //strSql.Append(" AND " + PreWarningResultDBConstNames.TUNNEL_ID + " IN (SELECT TUNNEL_ID FROM " + PreWarningResultViewDbConstNames.VIEW_NAME + " WHERE WARNING_STATUS=1)"); //strSql.Append(" ORDER BY "); //strSql.Append(PreWarningResultDBConstNames.TUNNEL_ID); ManageDataBase database = new ManageDataBase(DATABASE_TYPE.WarningManagementDB); database.Open(); DataTable dt = database.ReturnDSNotOpenAndClose(strSql.ToString()).Tables[0]; if (dt != null) { int rowCount = dt.Rows.Count; PreWarningResultQuery ent = null; for (int i = 0; i < rowCount; i++) { //巷道名称 int tunelId = LibCommon.Const.INVALID_ID; int.TryParse(dt.Rows[i][PreWarningResultDBConstNames.TUNNEL_ID].ToString(), out tunelId); if (container.ContainsKey(tunelId)) { ent = container[tunelId];// 巷道已经存在字典中 } else { ent = new PreWarningResultQuery(); container.Add(tunelId, ent); } ent.TunnelID = tunelId; Tunnel entTunnel = Tunnel.Find(tunelId); ent.TunelName = entTunnel.WorkingFace.WorkingfaceTypeEnum == WorkingfaceTypeEnum.JJ ? entTunnel.WorkingFace.WorkingFaceName : entTunnel.WorkingFace.WorkingFaceName + " - " + entTunnel.TunnelName; //ent.TunelName = GetTunelNameByTunelID(tunelId); ent.WorkingfaceId = dt.Rows[i][WorkingFaceDbConstNames.WORKINGFACE_ID] != DBNull.Value ? Convert.ToInt32(dt.Rows[i][WorkingFaceDbConstNames.WORKINGFACE_ID]) : 0; ent.WorkingfaceName = dt.Rows[i][WorkingFaceDbConstNames.WORKINGFACE_ID] != DBNull.Value ? dt.Rows[i][WorkingFaceDbConstNames.WORKINGFACE_NAME].ToString() : ""; //日期 ent.DateTime = Convert.ToDateTime(dt.Rows[i][PreWarningResultDBConstNames.DATA_TIME]); //班次 ent.Date_Shift = dt.Rows[i][PreWarningResultDBConstNames.DATE_SHIFT].ToString(); int defultValue = (int)WarningResult.NULL; //突出预警结果 if ((WarningType)(dt.Rows[i][PreWarningResultDBConstNames.WARNING_TYPE]) == WarningType.OUTBURST) { WarningResultEnt outburstEnt = new WarningResultEnt(); outburstEnt.ID = dt.Rows[i][PreWarningResultDBConstNames.ID].ToString(); int.TryParse(dt.Rows[i][PreWarningResultDBConstNames.WARNING_RESULT].ToString(), out defultValue); outburstEnt.WarningResult = defultValue; defultValue = (int)WarningResult.NULL; int.TryParse(dt.Rows[i][PreWarningResultDBConstNames.GAS].ToString(), out defultValue); if (defultValue < outburstEnt.Gas) { outburstEnt.Gas = defultValue; } defultValue = (int)WarningResult.NULL; int.TryParse(dt.Rows[i][PreWarningResultDBConstNames.COAL].ToString(), out defultValue); if (defultValue < outburstEnt.Coal) { outburstEnt.Coal = defultValue; } defultValue = (int)WarningResult.NULL; int.TryParse(dt.Rows[i][PreWarningResultDBConstNames.GEOLOGY].ToString(), out defultValue); if (defultValue < outburstEnt.Geology) { outburstEnt.Geology = defultValue; } defultValue = (int)WarningResult.NULL; int.TryParse(dt.Rows[i][PreWarningResultDBConstNames.VENTILATION].ToString(), out defultValue); if (defultValue < outburstEnt.Ventilation) { outburstEnt.Ventilation = defultValue; } defultValue = (int)WarningResult.NULL; int.TryParse(dt.Rows[i][PreWarningResultDBConstNames.MANAGEMENT].ToString(), out defultValue); if (defultValue < outburstEnt.Management) { outburstEnt.Management = defultValue; } // 处理状态 outburstEnt.HandleStatus = Convert.ToInt32(dt.Rows[i][PreWarningResultDBConstNames.HANDLE_STATUS]); ent.OutBrustWarningResult = outburstEnt; } //超限预警结果 if ((WarningType)(dt.Rows[i][PreWarningResultDBConstNames.WARNING_TYPE]) == WarningType.OVER_LIMIT) { WarningResultEnt overlimitEnt = new WarningResultEnt(); overlimitEnt.ID = dt.Rows[i][PreWarningResultDBConstNames.ID].ToString(); int.TryParse(dt.Rows[i][PreWarningResultDBConstNames.WARNING_RESULT].ToString(), out defultValue); if (defultValue < overlimitEnt.WarningResult) { overlimitEnt.WarningResult = defultValue; } defultValue = (int)WarningResult.NULL; int.TryParse(dt.Rows[i][PreWarningResultDBConstNames.GAS].ToString(), out defultValue); if (defultValue < overlimitEnt.Gas) { overlimitEnt.Gas = defultValue; } defultValue = (int)WarningResult.NULL; int.TryParse(dt.Rows[i][PreWarningResultDBConstNames.COAL].ToString(), out defultValue); if (defultValue < overlimitEnt.Coal) { overlimitEnt.Coal = defultValue; } defultValue = (int)WarningResult.NULL; int.TryParse(dt.Rows[i][PreWarningResultDBConstNames.GEOLOGY].ToString(), out defultValue); if (defultValue < overlimitEnt.Geology) { overlimitEnt.Geology = defultValue; } defultValue = (int)WarningResult.NULL; int.TryParse(dt.Rows[i][PreWarningResultDBConstNames.VENTILATION].ToString(), out defultValue); if (defultValue < overlimitEnt.Ventilation) { overlimitEnt.Ventilation = defultValue; } defultValue = (int)WarningResult.NULL; int.TryParse(dt.Rows[i][PreWarningResultDBConstNames.MANAGEMENT].ToString(), out defultValue); if (defultValue < overlimitEnt.Management) { overlimitEnt.Management = defultValue; } overlimitEnt.HandleStatus = Convert.ToInt32(dt.Rows[i][PreWarningResultDBConstNames.HANDLE_STATUS].ToString()); ent.OverLimitWarningResult = overlimitEnt; } if (ent.OutBrustWarningResult == null && ent.OverLimitWarningResult == null) { continue; } //lastedResultEnts.Add(ent); } foreach (KeyValuePair <int, PreWarningResultQuery> kvp in container) { lastedResultEnts.Add(kvp.Value); } } database.Close(); return(lastedResultEnts); }
SelectPreWarningResultInOneDateShift(string date, string date_shift, string tunnel_ID, string warning_type) { List <PreWarningResultQuery> ents = new List <PreWarningResultQuery>(); DateTime[] times = WorkingTime.GetDateShiftTimes(date_shift); if (times.Length != 2) { return(null); } StringBuilder strSql = new StringBuilder(); strSql.Append("SELECT * FROM("); strSql.Append("SELECT ROW_NUMBER() OVER (ORDER BY "); strSql.Append(PreWarningResultDBConstNames.DATA_TIME); strSql.Append(" DESC) AS rowID "); strSql.Append(" , * FROM "); strSql.Append(PreWarningResultDBConstNames.TABLE_NAME); strSql.Append(" WHERE "); strSql.Append(PreWarningResultDBConstNames.DATA_TIME); strSql.Append(" >= "); strSql.Append("'" + times[0] + "'"); strSql.Append(" AND "); strSql.Append(PreWarningResultDBConstNames.DATA_TIME); strSql.Append(" <= "); strSql.Append("'" + times[1] + "'"); strSql.Append(" AND "); strSql.Append(PreWarningResultDBConstNames.TUNNEL_ID); strSql.Append(" = "); strSql.Append(tunnel_ID); strSql.Append(" AND "); strSql.Append("( "); strSql.Append(PreWarningResultDBConstNames.WARNING_RESULT); strSql.Append(" < "); strSql.Append((int)LibCommon.WarningResult.GREEN); strSql.Append(") )AS T"); DataSet ds = _database.ReturnDSNotOpenAndClose(strSql.ToString()); if (ds != null) { DataTable dt = ds.Tables[0]; if (dt.Rows.Count > 0) { ents = new List <PreWarningResultQuery>(); int rowCount = dt.Rows.Count; for (int i = 0; i < rowCount; i++) { #region 实体赋值 PreWarningResultQuery ent = new PreWarningResultQuery(); ent.TunelName = GetTunelNameByTunelID(tunnel_ID); ent.DateTime = Convert.ToDateTime(dt.Rows[i][PreWarningResultDBConstNames.DATA_TIME]); ent.Date_Shift = dt.Rows[i][PreWarningResultDBConstNames.DATE_SHIFT].ToString(); int defultValue = (int)WarningResult.NULL; if (dt.Rows[i][PreWarningResultDBConstNames.WARNING_TYPE].ToString() == LibCommon.WarningType.OVER_LIMIT.ToString()) { ent.OverLimitWarningResult.ID = dt.Rows[i][PreWarningResultDBConstNames.ID].ToString(); int.TryParse(dt.Rows[i][LibBusiness.PreWarningResultDBConstNames.WARNING_RESULT].ToString(), out defultValue); ent.OverLimitWarningResult.WarningResult = defultValue; defultValue = (int)WarningResult.NULL; int.TryParse(dt.Rows[i][LibBusiness.PreWarningResultDBConstNames.GAS].ToString(), out defultValue); ent.OverLimitWarningResult.Gas = defultValue; defultValue = (int)WarningResult.NULL; int.TryParse(dt.Rows[i][LibBusiness.PreWarningResultDBConstNames.COAL].ToString(), out defultValue); ent.OverLimitWarningResult.Coal = defultValue; defultValue = (int)WarningResult.NULL; int.TryParse(dt.Rows[i][LibBusiness.PreWarningResultDBConstNames.GEOLOGY].ToString(), out defultValue); ent.OverLimitWarningResult.Geology = defultValue; defultValue = (int)WarningResult.NULL; int.TryParse(dt.Rows[i][LibBusiness.PreWarningResultDBConstNames.VENTILATION].ToString(), out defultValue); ent.OverLimitWarningResult.Ventilation = defultValue; defultValue = (int)WarningResult.NULL; int.TryParse(dt.Rows[i][LibBusiness.PreWarningResultDBConstNames.MANAGEMENT].ToString(), out defultValue); ent.OverLimitWarningResult.Management = defultValue; } if (dt.Rows[i][PreWarningResultDBConstNames.WARNING_TYPE].ToString() == LibCommon.WarningType.OUTBURST.ToString()) { ent.OutBrustWarningResult.ID = dt.Rows[i][PreWarningResultDBConstNames.ID].ToString(); int.TryParse(dt.Rows[i][LibBusiness.PreWarningResultDBConstNames.WARNING_RESULT].ToString(), out defultValue); ent.OutBrustWarningResult.WarningResult = defultValue; defultValue = (int)WarningResult.NULL; int.TryParse(dt.Rows[i][LibBusiness.PreWarningResultDBConstNames.GAS].ToString(), out defultValue); ent.OutBrustWarningResult.Gas = defultValue; defultValue = (int)WarningResult.NULL; int.TryParse(dt.Rows[i][LibBusiness.PreWarningResultDBConstNames.COAL].ToString(), out defultValue); ent.OutBrustWarningResult.Coal = defultValue; defultValue = (int)WarningResult.NULL; int.TryParse(dt.Rows[i][LibBusiness.PreWarningResultDBConstNames.GEOLOGY].ToString(), out defultValue); ent.OutBrustWarningResult.Geology = defultValue; defultValue = (int)WarningResult.NULL; int.TryParse(dt.Rows[i][LibBusiness.PreWarningResultDBConstNames.VENTILATION].ToString(), out defultValue); ent.OutBrustWarningResult.Ventilation = defultValue; defultValue = (int)WarningResult.NULL; int.TryParse(dt.Rows[i][LibBusiness.PreWarningResultDBConstNames.MANAGEMENT].ToString(), out defultValue); ent.OutBrustWarningResult.Management = defultValue; } ents.Add(ent); #endregion } } } return(ents); }