/// <summary>
/// 根据时间区间查询
/// </summary>
/// <param name="StartTime">开始时间</param>
/// <param name="EndTime">结束时间</param>
public static List<Divice> GetDataByTime(DateTime StartTime, DateTime EndTime)
{
//根据时间区间查询
if ((conn != null && conn.State == ConnectionState.Open) && (comm != null))
{
CreateDataBase();
}
String sqlStr = "select * from ReceiveData where CreateDate>=datetime('" + StartTime.Year + "-" + (StartTime.Month > 9 ? ("" + StartTime.Month) : ("0" + StartTime.Month)) +
"-" + (StartTime.Day > 9 ? ("" + StartTime.Day) : ("0" + StartTime.Day)) + "') and " +
"CreateDate<= datetime('" + EndTime.Year + "-" + (EndTime.Month > 9 ? ("" + EndTime.Month) : ("0" + EndTime.Month)) +
"-" + (EndTime.Day > 9 ? ("" + EndTime.Day) : ("0" + EndTime.Day)) + "') order by CreateDate;";
Console.Write(sqlStr);
comm.CommandText = sqlStr;
DbDataReader dr = comm.ExecuteReader();
List<Divice> lDivice = new List<Divice>();
if (dr != null)
{
while (dr.Read())
{
Divice divice = new Divice();
divice.DiviceNum = dr.GetInt32(0);
divice.Temperature = dr.GetInt32(1);
divice.Hum = dr.GetInt32(2);
divice.Wo = dr.GetInt32(3);
divice.MotorState = dr.GetInt32(4);
divice.ErrorNum = dr.GetInt32(5);
String tmp = dr.GetString(6);
divice.CreateTime = Convert.ToDateTime(tmp);
lDivice.Add(divice);
}
}
return lDivice;
}
private void FillNullData(List<Divice> lDivice, DisplayState State, DateTime StartTime, DateTime EndTime)
{
if (lDivice == null || lDivice.Count == 0)
{
return;
}
List<Divice> tmpLDivice = new List<Divice>(); ;
//根据不同的显示状态对数据进行显示的格式化
switch (State)
{
//根据时间显示
case DisplayState.TIME:
//设置时间下标
curve.PSubscript = DisplayDay;
//将数据根据小时格式化
int minHour = lDivice[0].CreateTime.Hour;
int maxHour = lDivice[lDivice.Count - 1].CreateTime.Hour;
//根据小时数循环
for (int i = 0; i < 24; i++)
{
//循环所有数据
for (int j = 0; j < lDivice.Count; j++)
{
//若该数据是该时间区域的
if (lDivice[j].CreateTime.Hour == i)
{
tmpLDivice.Add(lDivice[j]);
}
}
//该时间段内的数据为0则增加一个0点数据
if (tmpLDivice.Count == 0)
{
//若最小不如当前时间小, 则增加在头
if (minHour > i)
{
Divice tmpDivice = new Divice();
tmpDivice.CreateTime = new DateTime(StartTime.Year, StartTime.Month, StartTime.Day, i, 0, 0);
tmpDivice.Hum = 0;
tmpDivice.Temperature = 0;
tmpDivice.Wo = 0;
lDivice.Insert(0, tmpDivice);
}
else
//若最大时间不如当前时间大,则增加在尾.
if (maxHour < i)
{
Divice tmpDivice = new Divice();
tmpDivice.CreateTime = new DateTime(StartTime.Year, StartTime.Month, StartTime.Day, i, 0, 0);
tmpDivice.Hum = 0;
tmpDivice.Temperature = 0;
tmpDivice.Wo = 0;
lDivice.Add(tmpDivice);
}
else
//否则在中间,需要循环所有数据插入数据
//算法:根据时间区间插入0数据
{
for (int l = 0; l < lDivice.Count - 1; l++)
{
if (lDivice[l].CreateTime.Hour < i && lDivice[l + 1].CreateTime.Hour > i)
{
Divice tmpDivice = new Divice();
tmpDivice.CreateTime = new DateTime(StartTime.Year, StartTime.Month, StartTime.Day, i, 0, 0);
tmpDivice.Hum = 0;
tmpDivice.Temperature = 0;
tmpDivice.Wo = 0;
lDivice.Insert(l + 1, tmpDivice);
}
}
}
}
else
//该时间段内的数据多于1则取一个平均值
//可能有问题, 待测试-------------------------------
if (tmpLDivice.Count > 1)
{
int count = tmpLDivice.Count;
for (int k = 1; k < tmpLDivice.Count; k++)
{
tmpLDivice[0].Hum = (tmpLDivice[0].Hum + tmpLDivice[k].Hum);
tmpLDivice[0].Temperature = (tmpLDivice[0].Temperature + tmpLDivice[k].Temperature);
lDivice.Remove(tmpLDivice[k]);
}
tmpLDivice[0].Hum = tmpLDivice[0].Hum / count;
tmpLDivice[0].Temperature = tmpLDivice[0].Temperature / count;
}
tmpLDivice.Clear();
}
break;
//根据天显示
case DisplayState.DAY:
//设置动态日期的下标
//在这里直接操作startTime可能会有问题------------------------------------
int days = EndTime.Subtract(StartTime).Days + 1;
String[] DisplayDays = new String[days];
DateTime tmpDate = new DateTime(StartTime.Year, StartTime.Month, StartTime.Day);
//List<Divice> DiviceBackUp = new List<Divice>();
//DiviceBackUp.AddRange(lDivice);
//将月日加入下标
for (int i = 0; i < DisplayDays.Length; i++)
{
DisplayDays[i] = "" + tmpDate.Month + "-" + tmpDate.Day;
tmpDate = tmpDate.AddDays(1);
}
//第一个加上年份, 若中间有其他年份的日期(跨年)则在这几天的1月1号加上年份
DisplayDays[0] = "" + StartTime.Year + "-" + DisplayDays[0];
curve.PSubscript = DisplayDays;
int daysCount = EndTime.Subtract(StartTime).Days + 1;
//将数据根据日格式化
for (int i = 1; i <= daysCount; i++)
{
for (int j = 0; j < lDivice.Count; j++)
{
DateTime tmpDiviceTime = new DateTime(lDivice[j].CreateTime.Year, lDivice[j].CreateTime.Month, lDivice[j].CreateTime.Day);
//若日期等于今天则加入临时list
if (tmpDiviceTime.CompareTo(StartTime) == 0)
{
tmpLDivice.Add(lDivice[j]);
}
}
//若没有数据则补0数据
if (tmpLDivice.Count == 0)
{
int index = 0;
for (int l = 0; l < lDivice.Count; l++)
{
if (lDivice[l].CreateTime.CompareTo(StartTime) < 0 && l < lDivice.Count - 1)//&& lDivice[l + 1].CreateTime.CompareTo(StartTime) > 0)
{
index = l;
}
else
if (l == lDivice.Count - 1)
{
index = l + 1;
}
else
{
break;
}
}
Divice tmpDivice = new Divice();
tmpDivice.CreateTime = new DateTime(StartTime.Year, StartTime.Month, StartTime.Day, 0, 0, 0);
tmpDivice.Hum = 0;
tmpDivice.Temperature = 0;
tmpDivice.Wo = 0;
lDivice.Insert(index, tmpDivice);
//InsertZeroData(lDivice, index, 0, i, StartTime);
}
else
//循环处理同一天数据
if (tmpLDivice.Count > 1)
{
int count = tmpLDivice.Count;
int index = lDivice.IndexOf(tmpLDivice[0], 0);
//将同一天数据求平均值存为1条
lDivice.Remove(tmpLDivice[0]);
for (int k = 1; k < tmpLDivice.Count; k++)
{
tmpLDivice[0].Hum = (tmpLDivice[0].Hum + tmpLDivice[k].Hum);
tmpLDivice[0].Temperature = (tmpLDivice[0].Temperature + tmpLDivice[k].Temperature);
lDivice.Remove(tmpLDivice[k]);
}
//将数据替换为days个数个平均值加入list
//tmpLDivice = GetAverByCount(tmpLDivice, days);
//lDivice.InsertRange(index, tmpLDivice);
tmpLDivice[0].Hum = tmpLDivice[0].Hum / count;
tmpLDivice[0].Temperature = tmpLDivice[0].Temperature / count;
lDivice.Insert(index, tmpLDivice[0]);
}
StartTime = StartTime.AddDays(1);
tmpLDivice.Clear();
}
break;
//根据月显示
//case DisplayState.MONTH:
// //设置动态月下标
// int years = EndTime.Year - StartTime.Year;
// int year = StartTime.Year;
// int months = EndTime.Month - StartTime.Month + (years * 12);
// String[] DisplayMonth = new String[months];
// for (int i = 0; i < months; i++)
// {
// DisplayMonth[i] = "";
// }
// //将数据根据月格式化
// break;
}
////由于根据时间进行从小到大排序多以第一个是几点就将几个0数据放入头当空数据
//Divice firstDivice = lDivice[0];
////获得零点时间
//DateTime date = new DateTime(firstDivice.CreateTime.Year, firstDivice.CreateTime.Month, firstDivice.CreateTime.Day);
//int hours = firstDivice.CreateTime.Subtract(date).Hours;
//hours = hours > 0 ? hours - 1 : 0;
////根据时间间隔为1小时增加零点数据
//for (int i = 0; i < hours; i++)
//{
// Divice tmpDivice = new Divice();
// tmpDivice.CreateTime = new DateTime(firstDivice.CreateTime.Year, firstDivice.CreateTime.Month, firstDivice.CreateTime.Day, i, 0, 0);
// tmpDivice.Hum = 0;
// tmpDivice.Temperature = 0;
// tmpDivice.Wo = 0;
// lDivice.Insert(i, tmpDivice);
//}
////循环数据监测数据间时间的差距, 差距过大插0数据
//for (int i = 0; i < lDivice.Count - 1; i++)
//{
// Divice divice = lDivice[i];
// //若两个数据点在同一个时间段(1小时)内则取平均值
// if (lDivice[i].CreateTime.Hour == lDivice[i + 1].CreateTime.Hour)
// {
// lDivice[i].Hum = (lDivice[i].Hum + lDivice[i + 1].Hum) / 2;
// lDivice[i].Temperature = (lDivice[i].Temperature + lDivice[i + 1].Temperature) / 2;
// lDivice.Remove(lDivice[i + 1]);
// }
// //若两个数据点之间的时间超过1个小时(2小时以上)则在中间补0数据
// if (lDivice[i].CreateTime.Hour - lDivice[i + 1].CreateTime.Hour > 1)
// { }
//}
////由于根据时间进行从小到大排序多以最后一个是几点就将几个0数据放入尾当空数据
//Divice lastDivice = lDivice[lDivice.Count - 1];
////获得零点时间
//date = new DateTime(firstDivice.CreateTime.Year, firstDivice.CreateTime.Month, firstDivice.CreateTime.Day);
//hours = lastDivice.CreateTime.Subtract(date).Hours + 1;
//int count = 24 - hours;
////根据时间间隔为1小时增加零点数据
//for (int i = 0; i < count; i++)
//{
// Divice tmpDivice = new Divice();
// tmpDivice.CreateTime = new DateTime(lastDivice.CreateTime.Year, lastDivice.CreateTime.Month, lastDivice.CreateTime.Day, hours + i, 0, 0);
// tmpDivice.Hum = 0;
// tmpDivice.Temperature = 0;
// tmpDivice.Wo = 0;
// lDivice.Add(tmpDivice);
//}
}
