public List <InputTagEntity> GetInputTags(int PageIndex, int PageSize, String TagCategory, out int RecordCount) { String SqlText = @"WITH CTE AS( SELECT A.*,B.ConstantName TagCategory, ROW_NUMBER() OVER (ORDER BY InputIndex) AS R FROM KPI_InputTag A JOIN KPI_Constant B ON A.InputType = B.ConstantValue WHERE B.ConstantCode='M' AND A.InputType=@TagCategory) SELECT * FROM CTE WHERE R BETWEEN (@pageindex - 1) * @pagesize + 1 AND (@pageindex - 1) * @pagesize + @pagesize"; IDbDataParameter[] Parameters = new SqlParameter[] { new SqlParameter("@pageindex", SqlDbType.Int), new SqlParameter("@pagesize", SqlDbType.Int), new SqlParameter("@TagCategory", SqlDbType.Int) }; Parameters[0].Value = PageIndex; Parameters[1].Value = PageSize; Parameters[2].Value = TagCategory; List <InputTagEntity> Result; using (IDataReader Reader = m_DataAccess.ExecuteReader(CommandType.Text, SqlText, Parameters)) { Result = Reader.FillGenericList <InputTagEntity>(); }; SqlText = @"SELECT COUNT(InputID) FROM KPI_InputTag A JOIN KPI_Constant B ON A.InputType = B.ConstantValue WHERE B.ConstantCode='M' AND A.InputType=@TagCategory"; RecordCount = (int)m_DataAccess.ExecuteScalar(SqlText, Parameters); return(Result); }
public List <ManagementScoreEntity> GetManagementScores(int PageIndex, int PageSize, String Shift, String YearMonth, out int RecordCount) { List <ManagementScoreEntity> Result = null; string SqlText = @"WITH CTE AS( SELECT A.*,B.InputDesc TagName, ROW_NUMBER() OVER(ORDER BY A.TagID,A.PersonID) AS R FROM KPI_ManagementScore A JOIN KPI_InputTag B ON A.TagID=B.InputID WHERE CONVERT(varchar(7),A.CheckDate,120)=@YearMonth AND Shift=@Shift) SELECT * FROM CTE WHERE R BETWEEN (@pageindex - 1) * @pagesize + 1 AND (@pageindex - 1) * @pagesize + @pagesize"; IDbDataParameter[] Parameters = new SqlParameter[] { new SqlParameter("@pageindex", SqlDbType.Int), new SqlParameter("@pagesize", SqlDbType.Int), new SqlParameter("@YearMonth", SqlDbType.VarChar), new SqlParameter("@Shift", SqlDbType.Int) }; Parameters[0].Value = PageIndex; Parameters[1].Value = PageSize; Parameters[2].Value = YearMonth; Parameters[3].Value = Shift; using (IDataReader DataReader = m_DataAccess.ExecuteReader(CommandType.Text, SqlText, Parameters)) { Result = DataReader.FillGenericList <ManagementScoreEntity>(); }; SqlText = @"SELECT COUNT(PersonID) FROM KPI_ManagementScore A JOIN KPI_InputTag B ON A.TagID=B.InputID WHERE CONVERT(varchar(7),A.CheckDate,120)=@YearMonth AND Shift=@Shift"; RecordCount = (int)m_DataAccess.ExecuteScalar(SqlText, Parameters); return(Result); }
/// <summary> /// 返回经济指标日数据 /// </summary> /// <param name="ECID">指标编码</param> /// <param name="StartDate">开始日期</param> /// <param name="EndDate">结束日期</param> /// <param name="PageIndex">页码</param> /// <param name="PageSize">页面大小</param> /// <param name="RecordCount">记录数</param> /// <returns></returns> public List <ECHistoryDataEntity> GetECDayData(String ECID, DateTime StartDate, DateTime EndDate, int PageIndex, int PageSize, out int RecordCount) { RelaInterface DataBase = DBAccess.GetRelation(); List <ECHistoryDataEntity> Result = null; IDbDataParameter[] parames = new SqlParameter[] { new SqlParameter("@ECID", DbType.String), new SqlParameter("@StartDate", DbType.DateTime), new SqlParameter("@EndDate", DbType.DateTime), new SqlParameter("@PageIndex", DbType.Int32), new SqlParameter("@PageSize", DbType.Int32) }; parames[0].Value = ECID; parames[1].Value = StartDate; parames[2].Value = EndDate; parames[3].Value = PageIndex; parames[4].Value = PageSize; String CTE = @"WITH CTE AS( SELECT A.*, B.ECName,C.EngunitName,ROW_NUMBER() OVER(ORDER BY CheckDate) OrderNo FROM KPI_ECDayData A JOIN KPI_ECTag B ON A.ECID=B.ECID JOIN KPI_Engunit C ON B.EngunitID = C.EngunitID WHERE A.ECID=@ECID AND CheckDate BETWEEN @StartDate AND @EndDate)"; String RecordCountSql = CTE + "SELECT COUNT(ECID) FROM CTE"; String SqlText = CTE + @"SELECT * FROM CTE WHERE OrderNo BETWEEN (@PageIndex -1)*@PageSize AND @PageIndex * @PageSize"; RecordCount = Convert.ToInt32(DataBase.ExecuteScalar(RecordCountSql, parames)); using (IDataReader Reader = DataBase.ExecuteReader(CommandType.Text, SqlText, parames)) { Result = Reader.FillGenericList <ECHistoryDataEntity>(); } return(Result); }
/// <summary> /// 判断安全指标与测点对应关系表数据是否存在 /// </summary> /// <param name="SATagMap">安全指标与测点对应关系表实体</param> /// <returns>数据存在则返回true否则返回false</returns> private bool Exists(SATagMapEntity SATagMap) { string SqlText = "SELECT SAID FROM KPI_SATagMap WHERE SAID=@SAID AND RealID=@RealID "; SqlParameter[] parames = new SqlParameter[] { new SqlParameter("@SAID", SqlDbType.VarChar), new SqlParameter("@RealID", SqlDbType.VarChar) }; parames[0].Value = SATagMap.SAID; parames[1].Value = SATagMap.RealID; return(m_DB.ExecuteScalar(SqlText, parames) != null); }
public List <KPI_PersonEntity> GetPersons(string specialField, string unitID, string shift, int PageIndex, int PageSize, out int RecordCount) { List <KPI_PersonEntity> Result = null; String Where = ""; String SqlText = @"SELECT A.*,B.UnitName,C.PositionName, ROW_NUMBER() OVER(ORDER BY A.PersonID) OrderNo FROM KPI_Person A LEFT JOIN KPI_Unit B ON A.UnitID=B.UnitID LEFT JOIN KPI_Position C ON A.PositionID = C.PositionID"; if (!String.IsNullOrEmpty(unitID)) { Where = string.Format(" A.UnitID = '{0}'", unitID); } if (!String.IsNullOrEmpty(shift)) { Where += string.Format(" AND A.Shift = '{0}'", shift); } if (!String.IsNullOrEmpty(specialField)) { Where += string.Format(" AND A.specialField = '{0}'", specialField); } if ((Where.Length > 5) && (Where.Substring(1, 4) == "AND ")) { Where = Where.Substring(4, Where.Length - 4); } if (!string.IsNullOrEmpty(Where)) { Where = " WHERE " + Where; } SqlText += Where; String RecordCountCTE = "WITH CTE AS (" + SqlText + " ) SELECT COUNT(PersonID) FROM CTE"; RecordCount = Convert.ToInt32(m_DB.ExecuteScalar(RecordCountCTE)); SqlText = @"WITH CTE AS (" + SqlText + " ) " + @"SELECT * FROM CTE WHERE OrderNo BETWEEN (@PageIndex -1)*@PageSize AND @PageIndex * @PageSize"; IDbDataParameter[] parames = new SqlParameter[] { new SqlParameter("@PageIndex", DbType.Int32), new SqlParameter("@PageSize", DbType.Int32) }; parames[0].Value = PageIndex; parames[1].Value = PageSize; using (IDataReader Reader = m_DB.ExecuteReader(CommandType.Text, SqlText, parames)) { Result = Reader.FillGenericList <KPI_PersonEntity>(); } return(Result); }
public bool ExistsOverLimitRecord(KPI_OverLimitRecordEntity item) { bool Result = false; string SqlText = @"SELECT TagID FROM KPI_OverLimitRecord WHERE (TagID=@TagID AND AlarmType=@AlarmType) AND (AlarmEndTime IS NULL)" ; IDbDataParameter TagIDParam = new SqlParameter("@TagID", item.TagID); IDbDataParameter AlarmTypeParam = new SqlParameter("@AlarmType", item.AlarmType); //RelaInterface DB = DBAccess.GetRelation(); try { Object obj = m_DB.ExecuteScalar(SqlText, TagIDParam, AlarmTypeParam); Result = obj != null; } catch (Exception ex) { throw ex; } return(Result); }