public void WriteActual(DatabaseWeather weather)
{
var roundedTime = weather.TimeOfWeather;
var timeofCalculation = weather.TimeOfCalculation;
var cityId = weather.CityId;
var temperatur = weather.Temperature.ToString(CultureInfo.InvariantCulture);
var windSpeed = weather.WindSpeed.ToString(CultureInfo.InvariantCulture);
var windDegree = weather.WindDegree;
var pressure = weather.Pressure;
var humidity = weather.Humidity;
try
{
OpenDb();
var command = connection.CreateCommand();
// Daten werden nur alle paar Minuten neu berechnet.
command.CommandText = $"INSERT IGNORE INTO actual VALUES('{roundedTime:yyyy-MM-dd HH:mm:ss}', "
+ $"'{timeofCalculation:yyyy-MM-dd HH:mm:ss}', "
+ $"{cityId}, {temperatur}, {windSpeed}, {windDegree}, {pressure}, {humidity})";
command.ExecuteNonQuery();
Console.WriteLine("Actual written.");
}
catch (MySqlException ex)
{
MessageBox.Show("WriteActual: " + ex.Message);
}
finally
{
CloseDb();
}
}
public Accuracies(DatabaseWeather actualWeather, DatabaseWeather forecast)
{
this.TemperatureAccuracy = CalculateAccuracy(actualWeather.Temperature, forecast.Temperature);
this.WindSpeedAccuracy = CalculateAccuracy(actualWeather.WindSpeed, forecast.WindSpeed);
this.WindDegreeAccuracy = CalculateAccuracy(actualWeather.WindDegree, forecast.WindDegree);
this.PressureAccuracy = CalculateAccuracy(actualWeather.Pressure, forecast.Pressure);
this.HumidityAccuracy = CalculateAccuracy(actualWeather.Humidity, forecast.Humidity);
}
public List <DatabaseWeather> GetForecastListByName(string city)
{
var forecastCollection = this.GetApiForecastByName(city);
var forecastList = new List <DatabaseWeather>();
foreach (var forecastObject in forecastCollection.List)
{
var databaseWeather = new DatabaseWeather(forecastObject, forecastCollection.City.Id);
forecastList.Add(databaseWeather);
}
return(forecastList);
}
public List <DatabaseWeather> GetWeatherByIdAndDateTime(int cityId, DateTime dateTime)
{
var weatherList = new List <DatabaseWeather>();
var formattedDateTime = dateTime.ToString("yyyy-MM-dd HH:mm:ss");
try
{
OpenDb();
var command = connection.CreateCommand();
command.CommandText = $"(SELECT * , 0 as t_minus FROM actual WHERE city_id = {cityId} AND rounded_time = '{formattedDateTime}') "
+ $"UNION (SELECT * , TIME_FORMAT(TIMEDIFF(forecast_time, rounded_time_of_request), '%H') / 24 AS t_minus FROM forecast HAVING city_id = {cityId} AND forecast_time = '{formattedDateTime}' AND t_minus > 0) ORDER BY t_minus";
var reader = command.ExecuteReader();
while (reader.Read())
{
var weahter = new DatabaseWeather(
reader.GetDateTime(0),
reader.GetDateTime(1),
reader.GetInt32(8),
reader.GetInt32(2),
!reader.IsDBNull(3) ? reader.GetDouble(3) : 0.0,
!reader.IsDBNull(4) ? reader.GetDouble(4) : 0.0,
!reader.IsDBNull(5) ? reader.GetInt32(5) : 0,
!reader.IsDBNull(6) ? reader.GetInt32(6) : 0,
!reader.IsDBNull(7) ? reader.GetInt32(7) : 0
);
weatherList.Add(weahter);
}
}
catch (MySqlException ex)
{
MessageBox.Show(System.Reflection.MethodBase.GetCurrentMethod().Name + ":\n\n" + ex.Message);
}
finally
{
CloseDb();
}
return(weatherList);
}
public List <DatabaseWeather> GetForecastListById(int cityId)
{
var forecastList = new List <DatabaseWeather>();
try
{
OpenDb();
var command = connection.CreateCommand();
//command.CommandText = $"SELECT *, FROM forecast WHERE city_id = {cityId} ORDER BY forecast_time";
command.CommandText = $"SELECT * , TIME_FORMAT(TIMEDIFF(forecast_time, rounded_time_of_request), '%H') / 24 AS t_minus FROM forecast HAVING city_id = {cityId} ORDER BY forecast_time";
var reader = command.ExecuteReader();
while (reader.Read())
{
var forecast = new DatabaseWeather(
reader.GetDateTime(0),
reader.GetDateTime(1),
reader.GetInt32(8),
reader.GetInt32(2),
!reader.IsDBNull(3) ? reader.GetDouble(3) : 0.0,
!reader.IsDBNull(4) ? reader.GetDouble(4) : 0.0,
!reader.IsDBNull(5) ? reader.GetInt32(5) : 0,
!reader.IsDBNull(6) ? reader.GetInt32(6) : 0,
!reader.IsDBNull(7) ? reader.GetInt32(7) : 0
);
forecastList.Add(forecast);
}
}
catch (MySqlException ex)
{
MessageBox.Show(System.Reflection.MethodBase.GetCurrentMethod().Name + ":\n\n" + ex.Message);
}
finally
{
CloseDb();
}
return(forecastList);
}
public WeatherStatistics(DatabaseWeather actualWeather, DatabaseWeather forecast)
{
this.Forecast = forecast;
this.Accuracies = new Accuracies(actualWeather, forecast);
}
