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 <BatteryDTO> FindAll()
{
string query = "select capacity, idb, power, state, time from batteries";
List <BatteryDTO> batteryList = new List <BatteryDTO>();
using (IDbConnection connection = ConnectionUtil_Pooling.GetConnection())
{
connection.Open();
using (IDbCommand command = connection.CreateCommand())
{
command.CommandText = query;
command.Prepare();
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);
}
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 List <Tuple <string, int> > GetIdsForInit()
{
List <Tuple <string, int> > ids = new List <Tuple <string, int> >();
string query = "select idb,MAX(vreme) from batteries group by idb";
using (IDbConnection connection = ConnectionUtil_Pooling.GetConnection())
{
connection.Open();
using (IDbCommand command = connection.CreateCommand())
{
command.CommandText = query;
command.Prepare();
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Tuple <string, int> b = new Tuple <string, int>(reader.GetString(0), reader.GetInt32(1));
ids.Add(b);
}
}
}
}
return(ids);
}
public List <DateTime> GetDates()
{
String Sql = "select datum from shes";
List <DateTime> list = new List <DateTime>();
using (IDbConnection connection = ConnectionUtil_Pooling.GetConnection())
{
connection.Open();
using (IDbCommand command = connection.CreateCommand())
{
command.CommandText = Sql;
command.Prepare();
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
string date = reader.GetString(0);
string[] temp = date.Split('/');
int day = int.Parse(temp[0]);
int month = int.Parse(temp[1]);
int year = int.Parse(temp[2]);
DateTime d = new DateTime(year, month, day);
list.Add(d);
}
}
}
}
return(list);
}
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 IEnumerable <Consumer> FindAll()
{
string query = "select power, idc, state from consumers";
List <Consumer> consumerList = new List <Consumer>();
using (IDbConnection connection = ConnectionUtil_Pooling.GetConnection())
{
connection.Open();
using (IDbCommand command = connection.CreateCommand())
{
command.CommandText = query;
command.Prepare();
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Consumer cons = new Consumer(reader.GetDouble(0), reader.GetString(1),
(Enums.ConsumerRezim)Enum.Parse(typeof(Enums.ConsumerRezim), reader.GetString(3)));
consumerList.Add(cons);
}
}
}
}
return(consumerList);
}
public IEnumerable <SolarPanel> FindAll()
{
string query = "select idsp, power from solarpanels";
List <SolarPanel> solarPanelList = new List <SolarPanel>();
using (IDbConnection connection = ConnectionUtil_Pooling.GetConnection())
{
connection.Open();
using (IDbCommand command = connection.CreateCommand())
{
command.CommandText = query;
command.Prepare();
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
SolarPanel sp = new SolarPanel(reader.GetString(0),
reader.GetDouble(1));
solarPanelList.Add(sp);
}
}
}
}
return(solarPanelList);
}
public IEnumerable <EVCharger> FindAll()
{
string query = "select capacity, idevc, power, state, charge, connected from evcharger";
List <EVCharger> eVChargers = new List <EVCharger>();
using (IDbConnection connection = ConnectionUtil_Pooling.GetConnection())
{
connection.Open();
using (IDbCommand command = connection.CreateCommand())
{
command.CommandText = query;
command.Prepare();
using (IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
EVCharger 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)));
eVChargers.Add(evc);
}
}
}
}
return(eVChargers);
}
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 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 <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 void Save(EVCharger entity)
{
using (IDbConnection connection = ConnectionUtil_Pooling.GetConnection())
{
connection.Open();
Save(entity, connection);
}
}
public void Save(Consumer entity, int time)
{
using (IDbConnection connection = ConnectionUtil_Pooling.GetConnection())
{
connection.Open();
Save(entity, time, connection);
}
}
public void Save(SolarPanel entity)
{
using (IDbConnection connection = ConnectionUtil_Pooling.GetConnection())
{
connection.Open();
Save(entity, connection);
}
}
public void Save(BatteryDTO entity)
{
using (IDbConnection connection = ConnectionUtil_Pooling.GetConnection())
{
connection.Open();
Save(entity, connection);
}
}
public void SaveAll(IEnumerable <Consumer> entities, int time)
{
using (IDbConnection connection = ConnectionUtil_Pooling.GetConnection())
{
connection.Open();
IDbTransaction transaction = connection.BeginTransaction();
foreach (Consumer entity in entities)
{
Save(entity, time, connection);
}
transaction.Commit();
}
}
public double GetCurrentPrice()
{
string query = "select price from utility";
using (IDbConnection connection = ConnectionUtil_Pooling.GetConnection()) {
connection.Open();
using (IDbCommand command = connection.CreateCommand()) {
command.CommandText = query;
command.Prepare();
return(Convert.ToDouble(command.ExecuteScalar()));
}
}
}
public void SaveAll(IEnumerable <BatteryDTO> entities)
{
using (IDbConnection connection = ConnectionUtil_Pooling.GetConnection())
{
connection.Open();
IDbTransaction transaction = connection.BeginTransaction();
foreach (BatteryDTO entity in entities)
{
Save(entity, connection);
}
transaction.Commit();
}
}
public void DeleteAll()
{
string query = "delete from batteries";
using (IDbConnection connection = ConnectionUtil_Pooling.GetConnection())
{
connection.Open();
using (IDbCommand command = connection.CreateCommand())
{
command.CommandText = query;
command.Prepare();
command.ExecuteNonQuery();
}
}
}
public int Count()
{
string query = "select count(*) from batteries";
using (IDbConnection connection = ConnectionUtil_Pooling.GetConnection())
{
connection.Open();
using (IDbCommand command = connection.CreateCommand())
{
command.CommandText = query;
command.Prepare();
return(Convert.ToInt32(command.ExecuteScalar()));
}
}
}
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(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 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 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 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);
}