public static IEnumerable<BatchDataModel> GetSavedBatches(IList<RecipeDataModel> availableRecipes)
{
using (SQLiteConnection connection = DatabaseUtility.GetNewConnection())
{
using (SQLiteCommand getBatchesCommand = connection.CreateCommand())
{
getBatchesCommand.CommandText = "SELECT * FROM Batches";
using (SQLiteDataReader reader = getBatchesCommand.ExecuteReader())
{
while (reader.Read())
{
int batchId = reader.GetInt32(0);
int recipeId = reader.GetInt32(4);
BatchDataModel batch = new BatchDataModel(batchId)
{
BrewerName = reader[1].ToString(),
AssistantBrewerName = reader[2].ToString(),
Recipe = availableRecipes.FirstOrDefault(recipe => recipe.RecipeId == recipeId)
};
DateTime brewingDate;
if (DateTime.TryParse(reader.GetString(3), out brewingDate))
batch.BrewingDate = brewingDate;
// get gravity readings for batch
using (SQLiteCommand getGravityReadingsForBatchCommand = connection.CreateCommand())
{
getGravityReadingsForBatchCommand.CommandText = "SELECT GravityReadings.id, GravityReadings.specificGravity, GravityReadings.date FROM GravityReadings " +
"JOIN GravityReadingsInBatch ON GravityReadingsInBatch.gravityReading = GravityReadings.id AND GravityReadingsInBatch.batch = @batchId";
getGravityReadingsForBatchCommand.Parameters.AddWithValue("batchId", batchId);
using (SQLiteDataReader gravityReadingsReader = getGravityReadingsForBatchCommand.ExecuteReader())
{
while (gravityReadingsReader.Read())
{
GravityReadingDataModel gravityReading = new GravityReadingDataModel(gravityReadingsReader.GetInt32(0)) { Value = gravityReadingsReader.GetDouble(1) };
DateTime gravityReadingDate;
if (DateTime.TryParse(gravityReadingsReader.GetString(2), out gravityReadingDate))
gravityReading.Date = gravityReadingDate;
gravityReading.PropertyChanged += batch.Ingredient_PropertyChanged;
batch.RecordedGravityReadings.Add(gravityReading);
}
}
}
yield return batch;
}
}
}
connection.Close();
}
}
private void DeleteGravityReading(GravityReadingDataModel gravityReading)
{
CurrentBatch.RecordedGravityReadings.Remove(gravityReading);
BatchUtility.DeleteGravityReading(gravityReading.GravityReadingId);
}
internal static GravityReadingDataModel CreateGravityReading(int batchId, SQLiteConnection connection)
{
DateTime date = DateTime.Now;
using (SQLiteCommand insertGravityReadingCommand = connection.CreateCommand())
{
insertGravityReadingCommand.CommandText = "INSERT INTO GravityReadings (specificGravity, date) VALUES(0, @date)";
insertGravityReadingCommand.Parameters.AddWithValue("date", date.ToString());
insertGravityReadingCommand.ExecuteNonQuery();
}
GravityReadingDataModel gravityReading = new GravityReadingDataModel(DatabaseUtility.GetLastInsertedRowId(connection)) { Date = date };
using (SQLiteCommand insertJunctionCommand = connection.CreateCommand())
{
insertJunctionCommand.CommandText = "INSERT INTO GravityReadingsInBatch (gravityReading, batch) VALUES(@gravityReading, @batch)";
insertJunctionCommand.Parameters.AddWithValue("gravityReading", gravityReading.GravityReadingId);
insertJunctionCommand.Parameters.AddWithValue("batch", batchId);
insertJunctionCommand.ExecuteNonQuery();
}
return gravityReading;
}