private void InsertOrUpdateSpotsTable(ParkingSpot spot)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM ParkingSpots WHERE Name=@name", connection);
cmd.Parameters.AddWithValue("@name", spot.Name);
cmd.ExecuteNonQuery();
SqlDataReader reader = cmd.ExecuteReader();
bool hasRows = reader.HasRows;
reader.Close();
if (!hasRows)
{
cmd = new SqlCommand("INSERT INTO ParkingSpots(Name, Park_Id, Location, BatteryStatus, Timestamp, Value) VALUES(@name, @id, @location, @batteryStatus, @timestamp, @value)", connection);
cmd.Parameters.AddWithValue("@id", spot.Id);
cmd.Parameters.AddWithValue("@location", spot.Location);
cmd.Parameters.AddWithValue("@batteryStatus", spot.BatteryStatus);
cmd.Parameters.AddWithValue("@timestamp", spot.Status.Timestamp);
cmd.Parameters.AddWithValue("@value", spot.Status.Value);
cmd.Parameters.AddWithValue("@name", spot.Name);
cmd.ExecuteNonQuery();
string timestamp = spot.Status.Timestamp;
timestamp = timestamp.Replace("/", "-");
string[] dateAndTime = timestamp.Split(' ');
timestamp = dateAndTime[0];
string[] time = dateAndTime[1].Split(':');
string hour = time[0];
string minute = time[1];
cmd = new SqlCommand("INSERT INTO SpotsHistory(Name, Timestamp, Value, Hour, Minute) VALUES(@name, @timestamp, @value, @hour, @minute)", connection);
cmd.Parameters.AddWithValue("@timestamp", timestamp);
cmd.Parameters.AddWithValue("@value", spot.Status.Value);
cmd.Parameters.AddWithValue("@name", spot.Name);
cmd.Parameters.AddWithValue("@hour", hour);
cmd.Parameters.AddWithValue("@minute", minute);
cmd.ExecuteNonQuery();
}
else
{
/**
* Se o campo alterado for value, copiar linha para o historico e só depois fazer update
* */
cmd = new SqlCommand("SELECT Name, Timestamp, Value FROM ParkingSpots WHERE Name=@name", connection);
cmd.Parameters.AddWithValue("@name", spot.Name);
cmd.ExecuteNonQuery();
SqlDataReader valueFromDB = cmd.ExecuteReader();
valueFromDB.Read();
string value = valueFromDB.GetString(2), timestamp = spot.Status.Timestamp, name = valueFromDB.GetString(0);
timestamp = timestamp.Replace("/", "-");
string[] dateAndTime = timestamp.Split(' ');
timestamp = dateAndTime[0];
string hour = dateAndTime[1].Substring(0, 2);
string minute = dateAndTime[1].Substring(3, 2);
valueFromDB.Close();
if (value != spot.Status.Value)
{
cmd = new SqlCommand("INSERT INTO SpotsHistory(Name, Timestamp, Value, Hour, Minute) VALUES(@name, @timestamp, @value, @hour, @minute)", connection);
cmd.Parameters.AddWithValue("@timestamp", timestamp);
cmd.Parameters.AddWithValue("@value", spot.Status.Value);
cmd.Parameters.AddWithValue("@name", spot.Name);
cmd.Parameters.AddWithValue("@hour", hour);
cmd.Parameters.AddWithValue("@minute", minute);
cmd.ExecuteNonQuery();
}
cmd = new SqlCommand("UPDATE ParkingSpots SET BatteryStatus=@batteryStatus, Timestamp=@timestamp, Value=@value WHERE Name=@name", connection);
cmd.Parameters.AddWithValue("@batteryStatus", spot.BatteryStatus);
cmd.Parameters.AddWithValue("@timestamp", spot.Status.Timestamp);
cmd.Parameters.AddWithValue("@value", spot.Status.Value);
cmd.Parameters.AddWithValue("@name", spot.Name);
cmd.ExecuteNonQuery();
}
connection.Close();
}
}