public Battery FindById(string id, int time)
{
string query = "select capacity ,idb, power, state from batteries where idb = :idb and vreme =:vreme";
Battery battery = null;
using (IDbConnection connection = ConnectionUtil_Pooling.GetConnection())
{
connection.Open();
using (IDbCommand command = connection.CreateCommand())
{
command.CommandText = query;
ParameterUtil.AddParameter(command, "idb", DbType.String);
ParameterUtil.AddParameter(command, "vreme", DbType.Int32);
command.Prepare();
ParameterUtil.SetParameterValue(command, "vreme", time);
ParameterUtil.SetParameterValue(command, "idb", id);
using (IDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
battery = new Battery(reader.GetDouble(0), reader.GetString(1),
reader.GetDouble(2), (Enums.BatteryRezim)Enum.Parse(typeof(Enums.BatteryRezim), reader.GetString(3)));
}
}
}
}
return(battery);
}
public void Save(EVCharger entity, IDbConnection connection)
{
String insertSql = "insert into evcharger (capacity,power,state,charge,connected,idevc) " +
"values (:c ,:p,:s, :charge,:connected, :idevc)";
String updateSql = "update evcharger set capacity = :c, power = :p, state =:s," +
"charge =:charge, connected=:connected where idevc =:idevc";
using (IDbCommand command = connection.CreateCommand())
{
command.CommandText = ExistsById(entity.Id, connection) ? updateSql : insertSql;
ParameterUtil.AddParameter(command, "c", DbType.Double);
ParameterUtil.AddParameter(command, "p", DbType.Double);
ParameterUtil.AddParameter(command, "s", DbType.String);
ParameterUtil.AddParameter(command, "charge", DbType.String);
ParameterUtil.AddParameter(command, "connected", DbType.String);
ParameterUtil.AddParameter(command, "idevc", DbType.String);
ParameterUtil.SetParameterValue(command, "idevc", entity.Id);
ParameterUtil.SetParameterValue(command, "connected", entity.Connected);
ParameterUtil.SetParameterValue(command, "charge", entity.Charge);
ParameterUtil.SetParameterValue(command, "s", entity.State);
ParameterUtil.SetParameterValue(command, "p", entity.MaxPower);
ParameterUtil.SetParameterValue(command, "c", entity.Capacity);
command.ExecuteNonQuery();
}
}
public Consumer FindById(string id, int time)
{
string query = "select power,idc,state from consumers where idc = :idc and vreme =:vreme";
Consumer consumer = null;
using (IDbConnection connection = ConnectionUtil_Pooling.GetConnection())
{
connection.Open();
using (IDbCommand command = connection.CreateCommand())
{
command.CommandText = query;
ParameterUtil.AddParameter(command, "idc", DbType.String);
ParameterUtil.AddParameter(command, "vreme", DbType.Int32);
command.Prepare();
ParameterUtil.SetParameterValue(command, "vreme", time);
ParameterUtil.SetParameterValue(command, "idc", id);
using (IDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
consumer = new Consumer(reader.GetDouble(0), reader.GetString(1),
(Enums.ConsumerRezim)Enum.Parse(typeof(Enums.ConsumerRezim), reader.GetString(2)));
}
}
}
}
return(consumer);
}
public EVCharger FindById(string id)
{
string query = "select capacity, idevc, power, state, charge, connected from evcharger where idevc = :idevc";
EVCharger evc = new EVCharger();
using (IDbConnection connection = ConnectionUtil_Pooling.GetConnection())
{
connection.Open();
using (IDbCommand command = connection.CreateCommand())
{
command.CommandText = query;
ParameterUtil.AddParameter(command, "idevc", DbType.String);
command.Prepare();
ParameterUtil.SetParameterValue(command, "idevc", id);
using (IDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
evc = new EVCharger(reader.GetDouble(0), reader.GetString(1), reader.GetDouble(2), (Enums.BatteryRezim)Enum.Parse(typeof(Enums.BatteryRezim), reader.GetString(3)), bool.Parse(reader.GetString(4)),
bool.Parse(reader.GetString(5)));
}
}
}
}
return(evc);
}
public SolarPanel FindById(string id)
{
string query = "select idsp, power from solarpanels where idsp = :idsp";
SolarPanel sp = null;
using (IDbConnection connection = ConnectionUtil_Pooling.GetConnection())
{
connection.Open();
using (IDbCommand command = connection.CreateCommand())
{
command.CommandText = query;
ParameterUtil.AddParameter(command, "idsp", DbType.String);
command.Prepare();
ParameterUtil.SetParameterValue(command, "idsp", id);
using (IDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
sp = new SolarPanel(reader.GetString(0), reader.GetDouble(1));
}
}
}
}
return(sp);
}
public IEnumerable <ConsumersDTO> FindAll(int start, int end)
{
string query = "select vreme, SUM(power) from consumers " +
"where vreme>= :s and vreme <= :e and state = 'ON' " +
"group by vreme " +
"order by vreme asc";
List <ConsumersDTO> consumerList = new List <ConsumersDTO>();
using (IDbConnection connection = ConnectionUtil_Pooling.GetConnection())
{
connection.Open();
using (IDbCommand command = connection.CreateCommand())
{
command.CommandText = query;
ParameterUtil.AddParameter(command, "s", DbType.Int32);
ParameterUtil.AddParameter(command, "e", DbType.Int32);
command.Prepare();
ParameterUtil.SetParameterValue(command, "s", start);
ParameterUtil.SetParameterValue(command, "e", end);
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
ConsumersDTO cons = new ConsumersDTO(reader.GetInt32(0), reader.GetDouble(1));
consumerList.Add(cons);
}
}
}
}
return(consumerList);
}
public IEnumerable <SolarPanelsDTO> FindAll(int start, int end)
{
string query = "select power, time from spproduction " +
"where time>= :s and time <= :e " +
"order by time asc";
List <SolarPanelsDTO> consumerList = new List <SolarPanelsDTO>();
using (IDbConnection connection = ConnectionUtil_Pooling.GetConnection())
{
connection.Open();
using (IDbCommand command = connection.CreateCommand())
{
command.CommandText = query;
ParameterUtil.AddParameter(command, "s", DbType.Int32);
ParameterUtil.AddParameter(command, "e", DbType.Int32);
command.Prepare();
ParameterUtil.SetParameterValue(command, "s", start);
ParameterUtil.SetParameterValue(command, "e", end);
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
SolarPanelsDTO cons = new SolarPanelsDTO(reader.GetDouble(0), reader.GetInt32(1));
consumerList.Add(cons);
}
}
}
}
return(consumerList);
}
public bool ExistsById(string id, IDbConnection connection)
{
string query = "select * from batteries where idb=:idb";
using (IDbCommand command = connection.CreateCommand())
{
command.CommandText = query;
ParameterUtil.AddParameter(command, "idb", DbType.String);
command.Prepare();
ParameterUtil.SetParameterValue(command, "idb", id);
return(command.ExecuteScalar() != null);
}
}
public void Save(SolarPanel entity, IDbConnection connection)
{
String insertSql = "insert into solarpanels(power,idsp) values(:p, :idsp)";
String updateSql = "update solarpanels set power=:p where idsp =:idsp";
using (IDbCommand command = connection.CreateCommand())
{
command.CommandText = ExistsById(entity.Id, connection) ? updateSql : insertSql;
ParameterUtil.AddParameter(command, "p", DbType.Double);
ParameterUtil.AddParameter(command, "idsp", DbType.String);
ParameterUtil.SetParameterValue(command, "idsp", entity.Id);
ParameterUtil.SetParameterValue(command, "p", entity.MaxPower);
command.ExecuteNonQuery();
}
}
public void Save(string date, double total)
{
String insertSql = "insert into shes (total,datum) values (:total, :datum)";
using (IDbConnection connection = ConnectionUtil_Pooling.GetConnection())
{
connection.Open();
using (IDbCommand command = connection.CreateCommand())
{
command.CommandText = insertSql;
ParameterUtil.AddParameter(command, "total", DbType.Double);
ParameterUtil.AddParameter(command, "datum", DbType.String);
ParameterUtil.SetParameterValue(command, "datum", date);
ParameterUtil.SetParameterValue(command, "total", total);
command.ExecuteNonQuery();
}
}
}
public void SaveProduction(double power, int time)
{
String insertSql = "insert into spproduction (power,time) values (:power, :t)";
using (IDbConnection connection = ConnectionUtil_Pooling.GetConnection())
{
connection.Open();
using (IDbCommand command = connection.CreateCommand())
{
command.CommandText = insertSql;
ParameterUtil.AddParameter(command, "power", DbType.Double);
ParameterUtil.AddParameter(command, "t", DbType.Int32);
ParameterUtil.SetParameterValue(command, "t", time);
ParameterUtil.SetParameterValue(command, "power", power);
command.ExecuteNonQuery();
}
}
}
public void SaveProduction(Common.Utility utility, int time)
{
using (IDbConnection connection = ConnectionUtil_Pooling.GetConnection())
{
String insertSql = "insert into uproduction (power,vreme) values (:power, :t)";
//String updateSql = "update uproduction set power= :powerwhere time =:time";
connection.Open();
using (IDbCommand command = connection.CreateCommand())
{
command.CommandText = insertSql;
ParameterUtil.AddParameter(command, "power", DbType.Double);
ParameterUtil.AddParameter(command, "t", DbType.Int32);
ParameterUtil.SetParameterValue(command, "t", time);
ParameterUtil.SetParameterValue(command, "power", utility.Power);
command.ExecuteNonQuery();
}
}
}
public void Save(Common.Utility entity)
{
using (IDbConnection connection = ConnectionUtil_Pooling.GetConnection())
{
String insertSql = "insert into utility (price,idu) values (:price, :idu)";
String updateSql = "update utility set price= :price where idu =:idu";
connection.Open();
using (IDbCommand command = connection.CreateCommand())
{
command.CommandText = ExistsById("1", connection) ? updateSql : insertSql;
ParameterUtil.AddParameter(command, "price", DbType.Double);
ParameterUtil.AddParameter(command, "idu", DbType.String);
ParameterUtil.SetParameterValue(command, "idu", "1");
ParameterUtil.SetParameterValue(command, "price", entity.Price);
command.ExecuteNonQuery();
}
}
}
public void Save(Consumer entity, int time, IDbConnection connection)
{
String insertSql = "insert into consumers (power,state,vreme,idc) values (:power, :state,:vreme, :idc)";
// String updateSql = "update consumers set power = :power, state = :state where idc =:idc";
using (IDbCommand command = connection.CreateCommand())
{
command.CommandText = insertSql;
ParameterUtil.AddParameter(command, "power", DbType.Double);
ParameterUtil.AddParameter(command, "state", DbType.String);
ParameterUtil.AddParameter(command, "vreme", DbType.Int32);
ParameterUtil.AddParameter(command, "idc", DbType.String);
ParameterUtil.SetParameterValue(command, "idc", entity.Id);
ParameterUtil.SetParameterValue(command, "vreme", time);
ParameterUtil.SetParameterValue(command, "state", entity.Rezim);
ParameterUtil.SetParameterValue(command, "power", entity.EnergyConsumption);
command.ExecuteNonQuery();
}
}
public void Save(BatteryDTO entity, IDbConnection connection)
{
String insertSql = "insert into batteries (capacity,power,state,vreme,idb) values (:capacity, :power, :state,:vreme, :idb)";
// String updateSql = "update batteries set capacity=:capacity, power = :power, state = :state, time=:time where idb =:idb";
using (IDbCommand command = connection.CreateCommand())
{
// command.CommandText = ExistsById(entity.Id,connection) ? updateSql : insertSql;
command.CommandText = insertSql;
ParameterUtil.AddParameter(command, "capacity", DbType.Double);
ParameterUtil.AddParameter(command, "power", DbType.Double);
ParameterUtil.AddParameter(command, "state", DbType.String);
ParameterUtil.AddParameter(command, "vreme", DbType.Int32);
ParameterUtil.AddParameter(command, "idb", DbType.String);
ParameterUtil.SetParameterValue(command, "idb", entity.Id);
ParameterUtil.SetParameterValue(command, "vreme", entity.Time);
ParameterUtil.SetParameterValue(command, "state", entity.State.ToString());
ParameterUtil.SetParameterValue(command, "power", entity.MaxPower);
ParameterUtil.SetParameterValue(command, "capacity", entity.Capacity);
command.ExecuteNonQuery();
}
}
public IEnumerable <BatteryDTO> FindAllById(string id, int secondsStart, int secondsEnd)
{
string query = "select capacity, idb, power, state, vreme from batteries " +
" where idb =:idb and vreme >=:s and vreme <=:e " +
"order by vreme asc";
List <BatteryDTO> batteryList = new List <BatteryDTO>();
using (IDbConnection connection = ConnectionUtil_Pooling.GetConnection())
{
connection.Open();
using (IDbCommand command = connection.CreateCommand())
{
command.CommandText = query;
ParameterUtil.AddParameter(command, "idb", DbType.String);
ParameterUtil.AddParameter(command, "s", DbType.Int32);
ParameterUtil.AddParameter(command, "e", DbType.Int32);
command.Prepare();
ParameterUtil.SetParameterValue(command, "e", secondsEnd);
ParameterUtil.SetParameterValue(command, "s", secondsStart);
ParameterUtil.SetParameterValue(command, "idb", id);
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
BatteryDTO b = new BatteryDTO(reader.GetDouble(0), reader.GetString(1),
reader.GetDouble(2), (Enums.BatteryRezim)Enum.Parse(typeof(Enums.BatteryRezim), reader.GetString(3)), reader.GetInt32(4));
batteryList.Add(b);
}
}
}
}
return(batteryList);
}