public static List <OriginEnergyData> GetUnCalcedEnergyDataList(string connectString, DateTime startTime, DateTime endTime) { List <OriginEnergyData> list = new List <OriginEnergyData>(); string sql = string.Format(@"select * from t_ov_origvalue where F_Time between '{0}' and '{1}' and F_Calced = 0", startTime.ToString("yyyy-MM-dd HH:mm:ss"), endTime.ToString("yyyy-MM-dd HH:mm:ss")); using (MySqlConnection connection = new MySqlConnection(connectString)) { connection.Open(); MySqlCommand command = new MySqlCommand(sql, connection); MySqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { OriginEnergyData originEnergyData = new OriginEnergyData(); originEnergyData.BuildID = reader["F_BuildID"].ToString(); originEnergyData.MeterCode = reader["F_MeterCode"].ToString(); originEnergyData.Time = Convert.ToDateTime(reader["F_Time"].ToString()); originEnergyData.Value = Convert.ToSingle(reader["F_Value"]); originEnergyData.Calced = Convert.ToInt32(reader["F_Calced"]) == 0?false:true; list.Add(originEnergyData); } } return(list); }
public void TestGenreateSQL() { string connString = "Server=127.0.0.1;Port=3306;Database=energydb;Uid=root;Pwd=Fight4benben"; List <DateTime> times = MySQLHelper.GetUnCalculatedDataTimeList(connString); List <OriginEnergyData> list = MySQLHelper.GetUnCalcedEnergyDataList(connString, times[0], times[1]); DateTime nextTime = times.Find(t => t > times[0]); List <OriginEnergyData> currentList = list.FindAll(data => data.Time == times[0]); List <OriginEnergyData> nextList = list.FindAll(data => data.Time == nextTime); List <CalcEnergyData> CalcedList = new List <CalcEnergyData>(); //遍历当前currentList,根据主键内容取出nextList中的数据 foreach (var item in currentList) { OriginEnergyData next = nextList.Find(data => data.BuildID == item.BuildID && data.MeterCode == item.MeterCode); if (next == null) { continue; } if (next.Value == null || item.Value == null) { continue; } if ((next.Value - item.Value) < 0) { continue; } CalcedList.Add(new CalcEnergyData() { BuildID = item.BuildID, MeterCode = item.MeterCode, Time = item.Time, Value = next.Value - item.Value }); } Console.WriteLine("Minute:" + Runtime.GenerateMinuteSQL(CalcedList)); Console.WriteLine("Hour:" + Runtime.GenreateHourSQL(CalcedList)); Console.WriteLine("Day:" + Runtime.GenreateDaySQL(CalcedList)); }
public void TestInsertData() { string connString = "Server=127.0.0.1;Port=3306;Database=energydb;Uid=root;Pwd=Fight4benben"; List <DateTime> times = MySQLHelper.GetUnCalculatedDataTimeList(connString); List <OriginEnergyData> list = MySQLHelper.GetUnCalcedEnergyDataList(connString, times[0], times[1]); DateTime nextTime = times.Find(t => t > times[0]); List <OriginEnergyData> currentList = list.FindAll(data => data.Time == times[0]); List <OriginEnergyData> nextList = list.FindAll(data => data.Time == nextTime); List <CalcEnergyData> CalcedList = new List <CalcEnergyData>(); //遍历当前currentList,根据主键内容取出nextList中的数据 foreach (var item in currentList) { OriginEnergyData next = nextList.Find(data => data.BuildID == item.BuildID && data.MeterCode == item.MeterCode); if (next == null) { continue; } if (next.Value == null || item.Value == null) { continue; } if ((next.Value - item.Value) < 0) { continue; } CalcedList.Add(new CalcEnergyData() { BuildID = item.BuildID, MeterCode = item.MeterCode, Time = item.Time, Value = next.Value - item.Value }); } Console.WriteLine(CalcedList.Count); //生成SQL语句,批量插入 StringBuilder builder = new StringBuilder(); if (CalcedList.Count == 0) { Console.WriteLine(builder.ToString()); } else { builder.Append("INSERT INTO t_ec_minutevalue(F_BuildID,F_MeterCode,F_Time,F_Value) VALUES"); for (int i = 0; i < CalcedList.Count; i++) { if (i == 0) { builder.Append(string.Format("('{0}','{1}','{2}',{3})", CalcedList[i].BuildID, CalcedList[i].MeterCode, CalcedList[i].Time.ToString("yyyy-MM-dd HH:mm:ss"), CalcedList[i].Value)); } else { builder.Append(string.Format(",('{0}','{1}','{2}',{3})", CalcedList[i].BuildID, CalcedList[i].MeterCode, CalcedList[i].Time.ToString("yyyy-MM-dd HH:mm:ss"), CalcedList[i].Value)); } } builder.Append(" ON DUPLICATE KEY UPDATE F_Value=VALUES(F_Value);"); } Console.WriteLine(builder.ToString()); }
/// <summary> /// 计算能耗值 /// </summary> private static void CalculateEnergyData() { ShowLog("能耗计算线程已启动。"); Runtime.m_Logger.Info("能耗计算线程已启动。"); while (true) { if (DateTime.Now.Minute % 5 == 2) { List <DateTime> times = MySQLHelper.GetUnCalculatedDataTimeList(Runtime.MySqlConnectString); //获取该时间段内未处理的数据 if (times.Count <= 1) { Thread.Sleep(1000 * 60); continue; } List <OriginEnergyData> list = MySQLHelper.GetUnCalcedEnergyDataList(Runtime.MySqlConnectString, times[0], times[times.Count - 1]); foreach (DateTime time in times) { DateTime nextTime = times.Find(t => t > time); if (nextTime == null) { continue; } List <OriginEnergyData> currentList = list.FindAll(data => data.Time == time); List <OriginEnergyData> nextList = list.FindAll(data => data.Time == nextTime); if (nextList.Count == 0) { continue; } List <CalcEnergyData> CalcedList = new List <CalcEnergyData>(); //遍历当前currentList,根据主键内容取出nextList中的数据 foreach (var item in currentList) { OriginEnergyData next = nextList.Find(data => data.BuildID == item.BuildID && data.MeterCode == item.MeterCode); if (next == null) { continue; } if (next.Value == null || item.Value == null) { continue; } if ((next.Value - item.Value) < 0) { continue; } CalcedList.Add(new CalcEnergyData() { BuildID = item.BuildID, MeterCode = item.MeterCode, Time = item.Time, Value = next.Value - item.Value }); } //批量插入CalcedList到数据库T_EC_XXXValue数据库表 List <string> sqls = new List <string>(); sqls.Add(Runtime.GenerateMinuteSQL(CalcedList)); sqls.Add(Runtime.GenreateHourSQL(CalcedList)); sqls.Add(Runtime.GenreateDaySQL(CalcedList)); sqls.Add(Runtime.GenerateOrigStateSQL(time)); bool result = MySQLHelper.InsertDataTable(Runtime.MySqlConnectString, sqls); if (result) { ShowLog(string.Format("能耗值计算成功,数据时间为{0}", time.ToString("yyyy-MM-dd HH:mm:ss"))); Runtime.m_Logger.Info(string.Format("能耗值计算成功,数据时间为{0}", time.ToString("yyyy-MM-dd HH:mm:ss"))); } else { ShowLog(string.Format("能耗值计算失败,数据时间为{0}", time.ToString("yyyy-MM-dd HH:mm:ss"))); Runtime.m_Logger.Info(string.Format("能耗值计算失败,数据时间为{0}", time.ToString("yyyy-MM-dd HH:mm:ss"))); } } } ShowLog("当前未到能耗计算时间,请稍后..."); Runtime.m_Logger.Info("当前未到能耗计算时间,请稍后..."); System.Threading.Thread.Sleep(1000 * 60); } }