///
/// <param name="property">Module2Property to be written</param>
/// <param name="timestamp">Time when data arrived to module</param>
public void WriteProperty(IModule2Property property)
{
logger.LogNewInfo("Writing new property to database.");
Dataset set = DatasetRepository.GetDataset(property.Code);
string tableName = DatabaseTableNamesRepository.GetTableNameByDataset(set);
string signalCode = property.Code.ToString();
double value = property.Value;
string query = @"INSERT INTO " + tableName + "(signalCode, signalValue) VALUES (@code, @value)";
using (SQLiteCommand command = new SQLiteCommand(query, databaseConnection))
{
command.Parameters.AddWithValue("@code", signalCode);
command.Parameters.AddWithValue("@value", value);
if (command.ExecuteNonQuery() == 0)
{
logger.LogNewWarning("Could not write to database.");
}
else
{
logger.LogNewInfo("Property written successfully.");
}
}
}
///
/// <param name="property">Module 1 property</param>
public void WriteProperty(IModule1Property property)
{
logger.LogNewInfo(string.Format("Trying to write property with signal code {0} and value {1} to database", property.Code, property.Module1Value));
Dataset set = DatasetRepository.GetDataset(property.Code);
string tableName = DatabaseTableNamesRepository.GetTableNameByDataset(set);
string signalCode = property.Code.ToString();
double value = property.Module1Value;
string query = @"DELETE FROM " + tableName + " WHERE signalCode=@codeToDelete; INSERT INTO " + tableName + " (signalCode, signalValue) VALUES(@codeToInsert, @value)";
using (SQLiteCommand command = new SQLiteCommand(query, databaseConnection))
{
command.Parameters.AddWithValue("@codeToDelete", signalCode);
command.Parameters.AddWithValue("@codeToInsert", signalCode);
command.Parameters.AddWithValue("@value", value);
if (command.ExecuteNonQuery() == 0)
{
logger.LogNewWarning("Could not write to database.");
}
else
{
logger.LogNewInfo("Property written successfully.");
}
}
}
///
/// <param name="periodStart">Beginning of the search period</param>
/// <param name="periodEnd">End of the search period</param>
public List<IModule2Property> ReadPropertiesByTimeframe(DateTime periodStart, DateTime periodEnd, SignalCode code)
{
List<IModule2Property> returnList = new List<IModule2Property>();
logger.LogNewInfo(string.Format("Reading properties for code {0} with starting date {1}, end date {2}", code, periodEnd, periodEnd));
Dataset set = DatasetRepository.GetDataset(code);
string tableName = DatabaseTableNamesRepository.GetTableNameByDataset(set);
string signalCode = code.ToString();
string dateStart= periodStart.ToUniversalTime().ToString("yyyy'-'MM'-'dd' 'HH':'mm':'ss");
string dateEnd = periodEnd.ToUniversalTime().ToString("yyyy'-'MM'-'dd' 'HH':'mm':'ss");
string query = @"SELECT signalCode, signalValue FROM " + tableName + " WHERE signalCode=@code AND " +
"strftime('%s', timestamp) BETWEEN strftime('%s', @startDate) AND strftime('%s', @endDate) " +
"ORDER BY timestamp DESC ";
using (SQLiteCommand command = new SQLiteCommand(query, databaseConnection))
{
command.Parameters.AddWithValue("@code", signalCode);
command.Parameters.AddWithValue("@startDate", dateStart);
command.Parameters.AddWithValue("@endDate", dateEnd);
try
{
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
string retrievedSignal = reader["signalCode"].ToString();
string value = reader["signalValue"].ToString();
SignalCode retrievedCode = (SignalCode)Enum.Parse(typeof(SignalCode), retrievedSignal);
double valueRetrieved = double.Parse(value);
Module2Property property = new Module2Property(retrievedCode, valueRetrieved);
returnList.Add(property);
}
}
catch (Exception ex)
{
logger.LogNewWarning(string.Format("ERROR occured reading database. MESSAGE: {0}", ex.Message));
return returnList;
}
}
logger.LogNewInfo(string.Format("Found {0} properties within the requested timeframe", returnList.Count));
return returnList;
}
///
/// <param name="code">Signal code</param>
public IModule2Property ReadLastByCode(SignalCode code)
{
Dataset set = DatasetRepository.GetDataset(code);
string tableName = DatabaseTableNamesRepository.GetTableNameByDataset(set);
string signalCode = code.ToString();
string query = "SELECT ID, signalCode, signalValue FROM " + tableName + " WHERE(signalCode=@code) " +
"ORDER BY timestamp DESC LIMIT 1";
using (SQLiteCommand command = new SQLiteCommand(query, databaseConnection))
{
command.Parameters.AddWithValue("@code", signalCode);
try
{
using (SQLiteDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
string retrievedSignal = reader["signalCode"].ToString();
string value = reader["signalValue"].ToString();
SignalCode retrievedCode = (SignalCode)Enum.Parse(typeof(SignalCode), retrievedSignal);
double valueRetrieved = double.Parse(value);
Module2Property property = new Module2Property(retrievedCode, valueRetrieved);
return property;
}
return null;
}
}catch(Exception ex)
{
logger.LogNewWarning(string.Format("ERROR occured reading database. MESSAGE: {0}", ex.Message));
return null;
}
}
}
public string GetTableNameByDataset_AllDatasets_AssertName(Dataset set)
{
return(DatabaseTableNamesRepository.GetTableNameByDataset(set));
}
