internal static bool GetHighsAndLows(DateTime observationDate, ref HighLowObservation highsAndLows)
{
SqliteConnection connection = createConnection();
SqliteCommand command = connection.CreateCommand();
command.CommandText = "SELECT EntryID," +
"ObservationDate," +
"HighTemp," +
"LowTemp," +
"HighHeatIndex," +
"LowHeatIndex," +
"HighWindChill," +
"LowWindChill," +
"HighWindSpeed," +
"HighHumidity," +
"LowHumidity," +
"HighDewPoint," +
"LowDewPoint," +
"HighPressure," +
"LowPressure," +
"DayRain," +
"HighRainRate " +
"FROM HighsAndLows WHERE Date(ObservationDate) = Date('" + observationDate.ToString("yyyy-MM-dd") + "')";
SqliteDataReader reader = command.ExecuteReader();
bool resultFound;
if (reader.Read())
{
highsAndLows.ObservationDate = DateTime.Parse(reader.GetString(1));
highsAndLows.HighTemp = reader.GetFloat(2);
highsAndLows.LowTemp = reader.GetFloat(3);
highsAndLows.HighHeatIndex = reader.GetFloat(4);
highsAndLows.LowHeatIndex = reader.GetFloat(5);
highsAndLows.HighWindChill = reader.GetFloat(6);
highsAndLows.LowWindChill = reader.GetFloat(7);
highsAndLows.HighWindSpeed = reader.GetFloat(8);
highsAndLows.HighHumidity = reader.GetFloat(9);
highsAndLows.LowHumidity = reader.GetFloat(10);
highsAndLows.HighDewPoint = reader.GetFloat(11);
highsAndLows.LowDewPoint = reader.GetFloat(12);
highsAndLows.HighPressure = reader.GetFloat(13);
highsAndLows.LowPressure = reader.GetFloat(14);
highsAndLows.DayRain = reader.GetFloat(15);
highsAndLows.HighRainRate = reader.GetFloat(16);
resultFound = true;
}
else
{
resultFound = false;
}
connection.Close();
return(resultFound);
}
internal static List <HighLowObservation> GetHighsAndLows(DateTime observationStartDate, DateTime observationEndDate)
{
SqliteConnection connection = createConnection();
SqliteCommand command = connection.CreateCommand();
command.CommandText = "SELECT EntryID," +
"ObservationDate," +
"HighTemp," +
"LowTemp," +
"HighHeatIndex," +
"LowHeatIndex," +
"HighWindChill," +
"LowWindChill," +
"HighWindSpeed," +
"HighHumidity," +
"LowHumidity," +
"HighDewPoint," +
"LowDewPoint," +
"HighPressure," +
"LowPressure," +
"DayRain," +
"HighRainRate " +
"FROM HighsAndLows WHERE Date(ObservationDate) BETWEEN " +
"Date('" + observationStartDate.ToString("yyyy-MM-dd") + "') AND Date('" + observationEndDate.ToString("yyyy-MM-dd") + "')";
SqliteDataReader reader = command.ExecuteReader();
List <HighLowObservation> results = new List <HighLowObservation>();
while (reader.Read())
{
HighLowObservation highsAndLows = new HighLowObservation();
highsAndLows.ObservationDate = DateTime.Parse(reader.GetString(1));
highsAndLows.HighTemp = reader.GetFloat(2);
highsAndLows.LowTemp = reader.GetFloat(3);
highsAndLows.HighHeatIndex = reader.GetFloat(4);
highsAndLows.LowHeatIndex = reader.GetFloat(5);
highsAndLows.HighWindChill = reader.GetFloat(6);
highsAndLows.LowWindChill = reader.GetFloat(7);
highsAndLows.HighWindSpeed = reader.GetFloat(8);
highsAndLows.HighHumidity = reader.GetFloat(9);
highsAndLows.LowHumidity = reader.GetFloat(10);
highsAndLows.HighDewPoint = reader.GetFloat(11);
highsAndLows.LowDewPoint = reader.GetFloat(12);
highsAndLows.HighPressure = reader.GetFloat(13);
highsAndLows.LowPressure = reader.GetFloat(14);
highsAndLows.DayRain = reader.GetFloat(15);
highsAndLows.HighRainRate = reader.GetFloat(16);
results.Add(highsAndLows);
}
connection.Close();
return(results);
}
internal static void CreateHighsAndLows(HighLowObservation highsAndLows)
{
SqliteConnection connection = createConnection();
SqliteCommand command = connection.CreateCommand();
command.CommandText = "INSERT INTO HighsAndLows (" +
"ObservationDate," +
"HighTemp," +
"LowTemp," +
"HighHeatIndex," +
"LowHeatIndex," +
"HighWindChill," +
"LowWindChill," +
"HighWindSpeed," +
"HighHumidity," +
"LowHumidity," +
"HighDewPoint," +
"LowDewPoint," +
"HighPressure," +
"LowPressure," +
"DayRain," +
"HighRainRate " +
") VALUES(@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13,@14,@15,@16)";
command.Prepare();
command.Parameters.AddWithValue("@1", highsAndLows.ObservationDate.ToString("yyyy-MM-dd"));
command.Parameters.AddWithValue("@2", highsAndLows.HighTemp.ToString());
command.Parameters.AddWithValue("@3", highsAndLows.LowTemp.ToString());
command.Parameters.AddWithValue("@4", highsAndLows.HighHeatIndex.ToString());
command.Parameters.AddWithValue("@5", highsAndLows.LowHeatIndex.ToString());
command.Parameters.AddWithValue("@6", highsAndLows.HighWindChill.ToString());
command.Parameters.AddWithValue("@7", highsAndLows.LowWindChill.ToString());
command.Parameters.AddWithValue("@8", highsAndLows.HighWindSpeed.ToString());
command.Parameters.AddWithValue("@9", highsAndLows.HighHumidity.ToString());
command.Parameters.AddWithValue("@10", highsAndLows.LowHumidity.ToString());
command.Parameters.AddWithValue("@11", highsAndLows.HighDewPoint.ToString());
command.Parameters.AddWithValue("@12", highsAndLows.LowDewPoint.ToString());
command.Parameters.AddWithValue("@13", highsAndLows.HighPressure.ToString());
command.Parameters.AddWithValue("@14", highsAndLows.LowPressure.ToString());
command.Parameters.AddWithValue("@15", highsAndLows.DayRain.ToString());
command.Parameters.AddWithValue("@16", highsAndLows.HighRainRate.ToString());
command.ExecuteNonQuery();
connection.Close();
}
internal static void UpdateHighsAndLows(HighLowObservation highsAndLows)
{
SqliteConnection connection = createConnection();
SqliteCommand command = connection.CreateCommand();
command.CommandText = "UPDATE HighsAndLows SET " +
"HighTemp = @1," +
"LowTemp = @2," +
"HighHeatIndex = @3," +
"LowHeatIndex = @4," +
"HighWindChill = @5," +
"LowWindChill = @6," +
"HighWindSpeed = @7," +
"HighHumidity = @8," +
"LowHumidity = @9," +
"HighDewPoint = @10," +
"LowDewPoint = @11," +
"HighPressure = @12," +
"LowPressure = @13," +
"DayRain = @14," +
"HighRainRate = @15 " +
"WHERE Date(ObservationDate) = Date('" + highsAndLows.ObservationDate.ToString("yyyy-MM-dd") + "')";
command.Prepare();
command.Parameters.AddWithValue("@1", highsAndLows.HighTemp.ToString());
command.Parameters.AddWithValue("@2", highsAndLows.LowTemp.ToString());
command.Parameters.AddWithValue("@3", highsAndLows.HighHeatIndex.ToString());
command.Parameters.AddWithValue("@4", highsAndLows.LowHeatIndex.ToString());
command.Parameters.AddWithValue("@5", highsAndLows.HighWindChill.ToString());
command.Parameters.AddWithValue("@6", highsAndLows.LowWindChill.ToString());
command.Parameters.AddWithValue("@7", highsAndLows.HighWindSpeed.ToString());
command.Parameters.AddWithValue("@8", highsAndLows.HighHumidity.ToString());
command.Parameters.AddWithValue("@9", highsAndLows.LowHumidity.ToString());
command.Parameters.AddWithValue("@10", highsAndLows.HighDewPoint.ToString());
command.Parameters.AddWithValue("@11", highsAndLows.LowDewPoint.ToString());
command.Parameters.AddWithValue("@12", highsAndLows.HighPressure.ToString());
command.Parameters.AddWithValue("@13", highsAndLows.LowPressure.ToString());
command.Parameters.AddWithValue("@14", highsAndLows.DayRain.ToString());
command.Parameters.AddWithValue("@15", highsAndLows.HighRainRate.ToString());
command.ExecuteNonQuery();
connection.Close();
}
private static void CheckHighsAndLows(Observation observation)
{
MainWindow.LogMessage("Checking Highs and Lows");
HighLowObservation highsAndLows = new HighLowObservation();
bool exists = DatabaseManager.GetHighsAndLows(observation.ObservationDate, ref highsAndLows);
if (exists && highsAndLows.CompareToObservation(observation))
{
MainWindow.LogMessage("New high/low values found, updating");
DatabaseManager.UpdateHighsAndLows(highsAndLows);
MainWindow.LogMessage("New high/low update complete");
}
else if (!exists)
{
MainWindow.LogMessage("No high/low entry found, creating");
highsAndLows.Initialize(observation);
DatabaseManager.CreateHighsAndLows(highsAndLows);
MainWindow.LogMessage("New high/low entry created");
}
else
{
MainWindow.LogMessage("No changes to highs and lows detected");
}
}