public ActionResult <IEnumerable <HistoryElement> > GetRange([FromQuery(Name = "primaryKey")] DateTime primaryKey, [FromQuery(Name = "takeAmount")] int takeAmount)
{
List <HistoryElement> returnval = new List <HistoryElement>();
using (ConnectionPool.ConnectionUsable usable = new ConnectionPool.ConnectionUsable())
{
using (NpgsqlCommand command = usable.Connection.CreateCommand())
{
if (primaryKey == default(DateTime))
{
command.CommandText = "select * from t_history order by msgtimestamp desc limit @amount;";
}
else
{
command.CommandText = "select * from t_history where msgtimestamp <= @fromTime order by msgtimestamp desc limit @amount;";
command.Parameters.AddWithValue("@fromTime", NpgsqlDbType.Timestamp, primaryKey);
}
command.Parameters.AddWithValue("@amount", NpgsqlDbType.Integer, takeAmount);
using (NpgsqlDataReader dataReader = command.ExecuteReader())
{
while (dataReader.Read())
{
returnval.Add(new HistoryElement((DateTime)dataReader[0], (string)dataReader[1], (string)dataReader[2], (string)dataReader[3]));
}
}
}
}
return(Ok(returnval));
}
public static void AddLogEntry(HistoryElement element)
{
using (ConnectionPool.ConnectionUsable usable = new ConnectionPool.ConnectionUsable())
{
using (NpgsqlCommand command = usable.Connection.CreateCommand())
{
command.CommandText = $"INSERT INTO t_history(msgtimestamp, source, status, message) Values (@time, '{element.Source}', '{element.Status}', '{element.Message}');";
command.Parameters.AddWithValue("@time", NpgsqlDbType.Timestamp, element.TimeStamp);
command.ExecuteNonQuery();
}
}
}
public ActionResult Put([FromBody] SettingsData settingsData)
{
using (ConnectionPool.ConnectionUsable usable = new ConnectionPool.ConnectionUsable())
{
using (NpgsqlCommand command = usable.Connection.CreateCommand())
{
command.Transaction = usable.Connection.BeginTransaction();
command.CommandText = "select 1 from t_settings;";
NpgsqlDataReader reader = command.ExecuteReader();
if (reader.Read() == false)
{
reader.Dispose();
command.CommandText = $@"
INSERT INTO t_settings (id, enabled, location, location_friendly_name, override_value, weather_enabled)
VALUES (DEFAULT, false, '', '', 100, false);";
command.ExecuteNonQuery();
}
else
{
reader.Dispose();
}
// select where id == 1 if none, then insert
command.CommandText = $@"
UPDATE t_settings
SET enabled = {settingsData.Enabled},
location = '{settingsData.Location}',
location_friendly_name = '{settingsData.LocationFriendlyName}',
override_value = {settingsData.OverrideValue},
weather_enabled = {settingsData.WeatherEnabled}
WHERE id = 1;";
command.ExecuteNonQuery();
command.CommandText = @"
TRUNCATE TABLE ONLY t_humidity_sensor;";
command.ExecuteNonQuery();
command.CommandText = "INSERT INTO t_humidity_sensor (settings_id, friendly_name, real_name) VALUES (1, @friendlyName, @realName);";
foreach ((string realSensorName, string friendlySensorName) in settingsData.HumiditySensors)
{
command.Parameters.AddWithValue("@friendlyName", NpgsqlDbType.Text, friendlySensorName);
command.Parameters.AddWithValue("@realName", NpgsqlDbType.Text, realSensorName);
command.ExecuteNonQuery();
}
command.Transaction.Commit();
}
}
return(NoContent());
}
public static T LoadFromDataBase <T>(Func <T> computeDefaultValue = null)
{
using (ConnectionPool.ConnectionUsable usable = new ConnectionPool.ConnectionUsable())
using (NpgsqlCommand command = usable.Connection.CreateCommand())
{
InitTableRow <T>(command);
command.CommandText = "SELECT serialized_object FROM t_object_storage WHERE data_type_name = @type_name;";
command.Parameters.AddWithValue("@type_name", NpgsqlDbType.Text, GetTypeName <T>());
using (NpgsqlDataReader reader = command.ExecuteReader())
{
return(reader.Read() == false
? (computeDefaultValue == null ? default(T) : computeDefaultValue())
: JsonConvert.DeserializeObject <T>(reader.GetString(0)));
}
}
}
public ActionResult <int> GetAmount()
{
using (ConnectionPool.ConnectionUsable usable = new ConnectionPool.ConnectionUsable())
{
using (NpgsqlCommand command = usable.Connection.CreateCommand())
{
command.CommandText = "select count(*) from t_history;";
using (NpgsqlDataReader dataReader = command.ExecuteReader())
{
while (dataReader.Read())
{
return(Ok(dataReader[0]));
}
}
}
}
throw new Exception("Dieser Punkt darf nicht erreicht werden!");
}
private static double GetGlobalOverride()
{
using (ConnectionPool.ConnectionUsable usable = new ConnectionPool.ConnectionUsable())
{
using (NpgsqlCommand command = usable.Connection.CreateCommand())
{
command.CommandText = "select override_value from t_settings;";
using (NpgsqlDataReader dataReader = command.ExecuteReader())
{
if (dataReader.Read() == false)
{
throw new Exception("Es existieren keine SettingsDaten!");
}
return((double)dataReader.GetInt32(0) / 100);
}
}
}
}
public ActionResult <HistoryElement> Get(DateTime primaryKey)
{
using (ConnectionPool.ConnectionUsable usable = new ConnectionPool.ConnectionUsable())
{
using (NpgsqlCommand command = usable.Connection.CreateCommand())
{
command.CommandText = "select * from t_history where msgtimestamp == @primaryKey;";
command.Parameters.AddWithValue("@primaryKey", NpgsqlDbType.Timestamp, primaryKey);
using (NpgsqlDataReader dataReader = command.ExecuteReader())
{
while (dataReader.Read())
{
return(Ok(new HistoryElement((DateTime)dataReader[0], (string)dataReader[1], (string)dataReader[2], (string)dataReader[3])));
}
}
}
}
throw new Exception("Dieser Punkt darf nicht erreicht werden!");
}
private static bool GetGlobalScheduleEnabled()
{
using (ConnectionPool.ConnectionUsable usable = new ConnectionPool.ConnectionUsable())
{
using (NpgsqlCommand command = usable.Connection.CreateCommand())
{
command.CommandText = "select enabled from t_settings;";
using (NpgsqlDataReader dataReader = command.ExecuteReader())
{
if (dataReader.Read() == false)
{
throw new Exception("Es existieren keine SettingsDaten!");
}
return(dataReader.GetBoolean(0));
}
}
}
}
public ActionResult CreateNewEntry(HistoryElement element)
{
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
using (ConnectionPool.ConnectionUsable usable = new ConnectionPool.ConnectionUsable())
{
using (NpgsqlCommand command = usable.Connection.CreateCommand())
{
command.CommandText = $"INSERT INTO t_history(msgtimestamp, source, status, message) Values (@time, @source, @status, @message);";
command.Parameters.AddWithValue("@time", NpgsqlDbType.Timestamp, element.TimeStamp);
command.Parameters.AddWithValue("@source", NpgsqlDbType.Text, element.Source);
command.Parameters.AddWithValue("@status", NpgsqlDbType.Text, element.Status);
command.Parameters.AddWithValue("@message", NpgsqlDbType.Text, element.Message);
command.ExecuteNonQuery();
}
}
return(Ok());
}
public ActionResult <IEnumerable <HistoryElement> > GetSearched([FromQuery(Name = "searchString")] string searchString, [FromQuery(Name = "collumn")] int collumn, [FromQuery(Name = "amount")] int amount)
{
List <HistoryElement> returnval = new List <HistoryElement>();
using (ConnectionPool.ConnectionUsable usable = new ConnectionPool.ConnectionUsable())
{
using (NpgsqlCommand command = usable.Connection.CreateCommand())
{
command.CommandText = "select * from t_history where lower(" + GetCollumnName(collumn) + "::text) like lower('%" + searchString + "%') order by msgtimestamp desc limit @amount;";
//command.Parameters.AddWithValue("@tableName", GetCollumnName(collumn));
//command.Parameters.AddWithValue("@searchstring", searchString);
command.Parameters.AddWithValue("@amount", NpgsqlDbType.Integer, amount);
using (NpgsqlDataReader dataReader = command.ExecuteReader())
{
while (dataReader.Read())
{
returnval.Add(new HistoryElement((DateTime)dataReader[0], (string)dataReader[1], (string)dataReader[2], (string)dataReader[3]));
}
}
}
}
return(Ok(returnval));
}
public ActionResult <SettingsData> Get()
{
SettingsData data = new SettingsData
{
Valid = true,
HumiditySensors = new Dictionary <string, string>()
};
using (ConnectionPool.ConnectionUsable usable = new ConnectionPool.ConnectionUsable())
{
using (NpgsqlCommand command = usable.Connection.CreateCommand())
{
command.CommandText = "select enabled, location, location_friendly_name, override_value, weather_enabled from t_settings;";
using (NpgsqlDataReader dataReader = command.ExecuteReader())
{
if (dataReader.Read() == false)
{
return(NoContent());
}
data.Enabled = dataReader.GetBoolean(0);
data.Location = dataReader.GetString(1);
data.LocationFriendlyName = dataReader.GetString(2);
data.OverrideValue = dataReader.GetInt32(3);
data.WeatherEnabled = dataReader.GetBoolean(4);
}
command.CommandText = "select friendly_name, real_name from t_humidity_sensor where settings_id = 1;";
using (NpgsqlDataReader dataReader = command.ExecuteReader())
{
while (dataReader.Read())
{
data.HumiditySensors.Add(dataReader.GetString(1), dataReader.GetString(0));
}
}
}
}
return(Ok(data));
}
public ActionResult <HistoryData> Get()
{
HistoryData data = new HistoryData
{
Valid = true
};
using (ConnectionPool.ConnectionUsable usable = new ConnectionPool.ConnectionUsable())
{
using (NpgsqlCommand command = usable.Connection.CreateCommand())
{
command.CommandText = "select * from t_history order by msgtimestamp desc;";
using (NpgsqlDataReader dataReader = command.ExecuteReader())
{
while (dataReader.Read())
{
data.HistoryElements.Add(new HistoryElement((DateTime)dataReader[0], (string)dataReader[1], (string)dataReader[2], (string)dataReader[3]));
}
}
}
return(Ok(data));
}
}
