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