public IActionResult PostCuber(Cuber c)
{
if (c.Id < 0)
{
CuberService.AddCuber(c);
}
else
{
CuberService.SaveCuber(c);
}
return(Created("/api/cuber/" + c.Id, c));
}
/// <summary>
/// Updates an existing cuber
/// </summary>
/// <param name="cuber">The cuber to update</param>
public void SaveCuber(Cuber cuber)
{
var conn = new MySqlConnection(ConnectionString);
conn.Open();
var query = @"UPDATE cuber SET name = @name WHERE id = @id";
var cmd = new MySqlCommand(query, conn);
cmd.Parameters.AddWithValue("@name", cuber.Name);
cmd.Parameters.AddWithValue("@id", cuber.Id);
cmd.ExecuteNonQuery();
UpdateSolves(cuber, conn);
UpdateMetadata(cuber, conn);
conn.Close();
}
/// <summary>
/// Adds a new cuber to the database
/// </summary>
/// <param name="cuber">The cuber to add to the database</param>
public void AddCuber(Cuber cuber)
{
var conn = new MySqlConnection(ConnectionString);
conn.Open();
var query = @"INSERT INTO Cuber (name) VALUES (@name)";
var cmd = new MySqlCommand(query, conn);
cmd.Parameters.AddWithValue("@name", cuber.Name);
cmd.ExecuteNonQuery();
int id = (int)(cmd.LastInsertedId);
cuber.Id = id;
UpdateSolves(cuber, conn);
UpdateMetadata(cuber, conn);
conn.Close();
}
private void UpdateMetadata(Cuber cuber, MySqlConnection conn)
{
// delete old metadata
var deleteQuery = @"DELETE FROM metadata WHERE cuber_id = @id";
var deleteCmd = new MySqlCommand(deleteQuery, conn);
deleteCmd.Parameters.AddWithValue("@id", cuber.Id);
deleteCmd.ExecuteNonQuery();
// add updated metadata
var insertQuery = @"INSERT INTO metadata (mkey, mvalue, cuber_id) VALUES (@key, @value, @cuberId)";
foreach (var meta in cuber.Metadata)
{
var insertCmd = new MySqlCommand(insertQuery, conn);
insertCmd.Parameters.AddWithValue("@key", meta.Key);
insertCmd.Parameters.AddWithValue("@value", meta.Value);
insertCmd.Parameters.AddWithValue("@cuberId", cuber.Id);
insertCmd.ExecuteNonQuery();
}
}
private void UpdateSolves(Cuber cuber, MySqlConnection conn)
{
// remove existing solves
var deleteQuery = @"DELETE FROM solve WHERE cuber_id = @id";
var deleteCmd = new MySqlCommand(deleteQuery, conn);
deleteCmd.Parameters.AddWithValue("@id", cuber.Id);
deleteCmd.ExecuteNonQuery();
// add current solves
var insertQuery = @"INSERT INTO solve (event, time, cuber_id) values (@event, @time, @cuberId)";
foreach (var time in cuber.Solves3x3)
{
var insertCmd = new MySqlCommand(insertQuery, conn);
insertCmd.Parameters.AddWithValue("@event", "3x3");
insertCmd.Parameters.AddWithValue("@time", time);
insertCmd.Parameters.AddWithValue("@cuberId", cuber.Id);
insertCmd.ExecuteNonQuery();
}
foreach (var time in cuber.SolvesOh)
{
var insertCmd = new MySqlCommand(insertQuery, conn);
insertCmd.Parameters.AddWithValue("@event", "OH");
insertCmd.Parameters.AddWithValue("@time", time);
insertCmd.Parameters.AddWithValue("@cuberId", cuber.Id);
insertCmd.ExecuteNonQuery();
}
foreach (var time in cuber.Solves4x4)
{
var insertCmd = new MySqlCommand(insertQuery, conn);
insertCmd.Parameters.AddWithValue("@event", "4x4");
insertCmd.Parameters.AddWithValue("@time", time);
insertCmd.Parameters.AddWithValue("@cuberId", cuber.Id);
insertCmd.ExecuteNonQuery();
}
}
/// <summary>
/// Gets a cuber, and its solves and metatata
/// </summary>
/// <param name="id">The cuber's id</param>
/// <returns></returns>
public Cuber GetCuber(int id)
{
var conn = new MySqlConnection(ConnectionString);
conn.Open();
// get name
var getCuberQuery = "Select name FROM cuber WHERE id = @id";
var getCuberCmd = new MySqlCommand(getCuberQuery, conn);
getCuberCmd.Parameters.AddWithValue("@id", id);
var cuberReader = getCuberCmd.ExecuteReader();
cuberReader.Read();
var cuber = new Cuber()
{
Name = cuberReader[0].ToString(), Id = id
};
cuberReader.Close();
// get solves
var getSolvesQuery = @"SELECT event, time FROM solve where cuber_id = @id";
var getSolvesCmd = new MySqlCommand(getSolvesQuery, conn);
getSolvesCmd.Parameters.AddWithValue("@id", id);
var solvesReader = getSolvesCmd.ExecuteReader();
while (solvesReader.Read())
{
var evt = solvesReader[0].ToString();
var time = Math.Round(Convert.ToDouble(solvesReader[1]) * 100) / 100;
if (evt.Equals("3x3"))
{
cuber.Solves3x3.Add(time);
}
else if (evt.Equals("OH"))
{
cuber.SolvesOh.Add(time);
}
else if (evt.Equals("4x4"))
{
cuber.Solves4x4.Add(time);
}
}
solvesReader.Close();
// get metadata
var metadataQuery = @"SELECT mkey, mvalue FROM metadata WHERE cuber_id = @id";
var metadataCmd = new MySqlCommand(metadataQuery, conn);
metadataCmd.Parameters.AddWithValue("@id", id);
var metadataRader = metadataCmd.ExecuteReader();
while (metadataRader.Read())
{
cuber.Metadata.Add(new CuberMetadata {
Key = metadataRader[0].ToString(), Value = metadataRader[1].ToString()
});
}
metadataRader.Close();
conn.Close();
return(cuber);
}
