public List <HydrantEntity> SelectAll()
{
List <HydrantEntity> lstHydrant = null;
string SQL = @"SELECT h.HydrantID,h.Addr,h.longitude,h.latitude,d.Operate,d.PressValue,d.OpenAngle,d.IsAlarm
FROM Hydrant h LEFT JOIN
(SELECT a.TerminalID,a.Operate,a.PressValue,a.OpenAngle,a.IsAlarm FROM [Hydrant_Real] a,(SELECT TerminalID,MAX(colltime)
AS colltime FROM Hydrant_Real GROUP BY TerminalID) b WHERE
a.TerminalID=b.TerminalID AND a.CollTime = b.colltime) d
ON h.HydrantID=d.TerminalID";
using (SqlDataReader reader = SQLHelper.ExecuteReader(SQL, null))
{
lstHydrant = new List <HydrantEntity>();
while (reader.Read())
{
HydrantEntity entity = new HydrantEntity();
entity.HydrantID = reader["HydrantID"].ToString();
entity.Addr = reader["Addr"].ToString();
entity.Longtitude = reader["longitude"].ToString();
entity.Latitude = reader["latitude"].ToString();
entity.OptType = reader["Operate"] != DBNull.Value ? (HydrantOptType)Convert.ToInt32(reader["Operate"]) : HydrantOptType.Close;
entity.PressValue = reader["PressValue"] != DBNull.Value ? Convert.ToSingle(reader["PressValue"]) : 0f;
entity.OpenAngle = reader["OpenAngle"] != DBNull.Value ? Convert.ToInt32(reader["OpenAngle"]) : 0;
entity.IsAlarm = reader["IsAlarm"] != DBNull.Value ? (Convert.ToInt32(reader["IsAlarm"]) == 1 ? true : false) : false;
lstHydrant.Add(entity);
}
}
return(lstHydrant);
}
public List <HydrantEntity> GetHydrantDetail(string HydrantID, int opt, DateTime minTime, DateTime maxTime, int interval)
{
string SQL = @"SELECT Operate,PressValue,OpenAngle,CollTime FROM Hydrant_Real
WHERE CollTime BETWEEN @mintime AND @maxtime AND DATEDIFF(minute,@mintime,CollTime) %@interval = 0 AND TerminalID=@TerId ";
if (opt != -1)
{
SQL += "AND Operate=" + opt;
}
SQL += "ORDER BY CollTime";
SqlParameter[] parms = new SqlParameter[] {
new SqlParameter("@TerId", SqlDbType.Int),
new SqlParameter("@mintime", SqlDbType.DateTime),
new SqlParameter("@maxtime", SqlDbType.DateTime),
new SqlParameter("@interval", SqlDbType.Int)
};
parms[0].Value = HydrantID;
parms[1].Value = minTime;
parms[2].Value = maxTime;
parms[3].Value = interval;
using (SqlDataReader reader = SQLHelper.ExecuteReader(SQL, parms))
{
List <HydrantEntity> lstData = new List <HydrantEntity>();
while (reader.Read())
{
HydrantEntity entity = new HydrantEntity();
entity.HydrantID = HydrantID;
entity.OptType = (HydrantOptType)Convert.ToInt32(reader["Operate"]);
entity.PressValue = reader["PressValue"] != DBNull.Value ? Convert.ToSingle(reader["PressValue"]) : 0f;
entity.OpenAngle = reader["OpenAngle"] != DBNull.Value ? Convert.ToInt32(reader["OpenAngle"]) : 0;
entity.CollTime = reader["CollTime"] != DBNull.Value ? Convert.ToDateTime(reader["CollTime"]) : ConstValue.MinDateTime;
lstData.Add(entity);
}
return(lstData);
}
return(null);
}
