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