public List <WindRecord> GetListBetweenDate(DateTime startDate, DateTime endDate)
{
string cmdText = string.Format("select * from " + dbToUse + " WHERE imei=" + imei + " AND time between {0} order by time desc", (isMySqlDB ? "?start and ?end" : "@start and @end"));
using (DbConnection conn = GetDbConnection(GetDBConnString()))
{
List <WindRecord> list = new List <WindRecord>();
conn.Open();
using (DbCommand cmd = GetDBCommand(cmdText, conn))
{
cmd.Parameters.Add(GetDBParam("start", startDate));
cmd.Parameters.Add(GetDBParam("end", endDate));
DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
WindRecord wr = new WindRecord();
wr.Time = Convert.ToDateTime(reader["time"]);
wr.AverageDirection = int.Parse(reader["averageDir"].ToString());
wr.MaxDirection = int.Parse(reader["maxDir"].ToString());
wr.MinDirection = int.Parse(reader["minDir"].ToString());
wr.AverageSpeed = float.Parse(reader["averageSpeed"].ToString());
wr.MaxSpeed = float.Parse(reader["maxSpeed"].ToString());
wr.MinSpeed = float.Parse(reader["minSpeed"].ToString());
list.Add(wr);
}
}
return(list);
}
}
public WindRecord GetCurrentWind()
{
WindRecord currWind = new WindRecord();
using (DbConnection conn = GetDbConnection(GetDBConnString()))
using (DbCommand cmd = GetDBCommand("select * from " + dbToUse + " WHERE imei=" + imei + " order by time desc limit 0,1", conn))
{
conn.Open();
DbDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
if (reader.Read())
{
currWind.Time = Convert.ToDateTime(reader["time"]);
currWind.AverageDirection = int.Parse(reader["averageDir"].ToString());
currWind.MaxDirection = int.Parse(reader["maxDir"].ToString());
currWind.MinDirection = int.Parse(reader["minDir"].ToString());
currWind.AverageSpeed = float.Parse(reader["averageSpeed"].ToString());
currWind.MaxSpeed = float.Parse(reader["maxSpeed"].ToString());
currWind.MinSpeed = float.Parse(reader["minSpeed"].ToString());
// TODO, uncomment next two lines when the database has posts for temperature
//currWind.AverageAirTemp = int.Parse(reader["averageAirTemp"].ToString());
//currWind.AverageWaterTemp = int.Parse(reader["averageWaterTemp"].ToString());
}
}
return(currWind);
}
public List <WindRecord> GetFullList()
{
string cmdText = "select * from " + dbToUse + " imei=" + imei + "order by time";
using (DbConnection conn = GetDbConnection(GetDBConnString()))
{
conn.Open();
List <WindRecord> list = new List <WindRecord>();
using (DbCommand cmd = GetDBCommand(cmdText, conn))
{
DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
WindRecord wr = new WindRecord();
wr.Time = Convert.ToDateTime(reader["time"]);
wr.AverageDirection = int.Parse(reader["averageDir"].ToString());
wr.MaxDirection = int.Parse(reader["maxDir"].ToString());
wr.MinDirection = int.Parse(reader["minDir"].ToString());
wr.AverageSpeed = float.Parse(reader["averageSpeed"].ToString());
wr.MaxSpeed = float.Parse(reader["maxSpeed"].ToString());
wr.MinSpeed = float.Parse(reader["minSpeed"].ToString());
list.Add(wr);
}
}
return(list);
}
}
public List <WindRecord> GetListBetweenDate2(DateTime startDate, DateTime endDate)
{
TimeSpan interval = endDate.Subtract(startDate);
TimeSpan t = new TimeSpan(interval.Ticks / 50);
endDate = startDate.Add(t);
List <WindRecord> list = new List <WindRecord>();
try
{
using (DbConnection conn = GetDbConnection(GetDBConnString()))
{
conn.Open();
for (int i = 0; i < 50; i++)
{
string cmdText = string.Format("select avg(averageDir),avg(maxDir),avg(minDir),avg(averageSpeed),avg(maxSpeed),avg(minSpeed) from " + dbToUse + " WHERE imei=" + imei + " AND time >\"" + startDate.ToString() + "\" and time <\"" + endDate.ToString() + "\" order by time desc");
using (DbCommand cmd = GetDBCommand(cmdText, conn))
{
DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
WindRecord wr = new WindRecord();
//wr.Time = Convert.ToDateTime(reader["time"]);
if (reader["avg(averageDir)"] != null)
{
string test = reader["avg(averageDir)"].ToString();
if (test.Equals("null") || test.Equals(""))
{
continue;
}
wr.Time = endDate;//
wr.AverageDirection = (int)float.Parse(reader["avg(averageDir)"].ToString());
wr.MaxDirection = (int)float.Parse(reader["avg(maxDir)"].ToString());
wr.MinDirection = (int)float.Parse(reader["avg(minDir)"].ToString());
wr.AverageSpeed = float.Parse(reader["avg(averageSpeed)"].ToString());
wr.MaxSpeed = float.Parse(reader["avg(maxSpeed)"].ToString());
wr.MinSpeed = float.Parse(reader["avg(minSpeed)"].ToString());
list.Add(wr);
}
else
{
}
}
reader.Close();
}
startDate = startDate.Add(t);
endDate = endDate.Add(t);
}
list.Reverse();
}
}
catch (Exception e)
{
int a = 0;
a++;
}
return(list);
}