public int size()
{
log.Info("Returnare dimensiune");
IDbConnection con = DBUtils.getConnection();
using (var comm = con.CreateCommand())
{
comm.CommandText = "select count(*) from rezervare";
using (var dataR = comm.ExecuteReader())
{
if (dataR.Read())
{
int nr = dataR.GetInt32(0);
log.Info("Am reusit returnare dimensiune");
return(nr);
}
}
}
return(0);
}
public Entry FindOne(Models.Tuple <long, long> id)
{
log.InfoFormat("Entering FindOne with value {0}", id);
IDbConnection con = DBUtils.getConnection();
using (var comm = con.CreateCommand())
{
comm.CommandText = "select kid , cid , date from Entries where kid = @kid and cid = @cid";
IDbDataParameter paramKId = comm.CreateParameter();
paramKId.ParameterName = "@kid";
paramKId.Value = id.Left;
comm.Parameters.Add(paramKId);
IDbDataParameter paramCId = comm.CreateParameter();
paramCId.ParameterName = "@cid";
paramCId.Value = id.Right;
comm.Parameters.Add(paramCId);
using (var dataR = comm.ExecuteReader())
{
if (dataR.Read())
{
long kidE = dataR.GetInt64(0);
long cidE = dataR.GetInt64(1);
DateTime date = dataR.GetDateTime(2);
Models.Tuple <long, long> idE = new Models.Tuple <long, long>(kidE, cidE);
Child child = childRepo.FindOne(kidE);
Challenge challenge = challengeRepo.FindOne(cidE);
Entry entry = new Entry(date, child, challenge);
entry.Id = idE;
log.InfoFormat("Exiting FindOne with value {0}", entry);
return(entry);
}
}
}
log.InfoFormat("Exiting FindOne with value {0}", null);
return(null);
}
public List <Excursie> findAllObiectivOra(String obiectiv, String dupa, String inainte)
{
IDbConnection con = DBUtils.getConnection();
List <Excursie> excursii = new List <Excursie>();
using (var comm = con.CreateCommand())
{
comm.CommandText = "SELECT * FROM excursie WHERE numeObiectiv=@numeObiectiv AND @dupa<=oraPlecarii AND oraPlecarii<=@inainte";
var paramId = comm.CreateParameter();
paramId.ParameterName = "@numeObiectiv";
paramId.Value = obiectiv;
comm.Parameters.Add(paramId);
var paramName = comm.CreateParameter();
paramName.ParameterName = "@dupa";
paramName.Value = dupa;
comm.Parameters.Add(paramName);
var paramNumeF = comm.CreateParameter();
paramNumeF.ParameterName = "@inainte";
paramNumeF.Value = inainte;
comm.Parameters.Add(paramNumeF);
using (var dataR = comm.ExecuteReader())
{
while (dataR.Read())
{
int id = dataR.GetInt32(0);
string numeObiectiv = dataR.GetString(1);
string numeFirma = dataR.GetString(2);
string oraPlecarii = dataR.GetString(3);
float pret = dataR.GetFloat(4);
int nrLocuri = dataR.GetInt32(5);
Excursie excursie = new Excursie(id, numeObiectiv, numeFirma, oraPlecarii, pret, nrLocuri);
excursii.Add(excursie);
}
}
}
return(excursii);
}
public long Save(Child child)
{
IDbConnection con = DBUtils.getConnection();
using (var comm = con.CreateCommand())
{
comm.CommandText = "INSERT INTO Child (Name, Age) VALUES (@name, @age); SELECT last_insert_rowid()";
IDbDataParameter paramName = comm.CreateParameter();
paramName.ParameterName = "@name";
paramName.Value = child.Name;
comm.Parameters.Add(paramName);
IDbDataParameter paramAge = comm.CreateParameter();
paramAge.ParameterName = "@age";
paramAge.Value = child.Age.ToString();
comm.Parameters.Add(paramAge);
long IDGenerated = (long)comm.ExecuteScalar();
return(IDGenerated);
}
}
public IEnumerable <ChildNoEventsDTO> GetChildrenNoEvents(Event e, AgeGroup a)
{
IDbConnection con = DBUtils.getConnection();
IList <ChildNoEventsDTO> children = new List <ChildNoEventsDTO>();
using (var comm = con.CreateCommand())
{
comm.CommandText = "SELECT C.ID, C.Name, C.Age, CASE WHEN Event2 != 'NONE' THEN 2 ELSE 1 END AS NoEvents FROM Entry E INNER JOIN Child C on C.ID = E.IDChild WHERE (Event1 = @event OR Event2 = @event) AND AgeGroup = @ageGroup;";
IDbDataParameter paramEvent = comm.CreateParameter();
paramEvent.ParameterName = "@event";
paramEvent.Value = e.ToString();
comm.Parameters.Add(paramEvent);
IDbDataParameter paramAgeGroup = comm.CreateParameter();
paramAgeGroup.ParameterName = "@ageGroup";
paramAgeGroup.Value = a.ToString();
comm.Parameters.Add(paramAgeGroup);
using (var dataR = comm.ExecuteReader())
{
while (dataR.Read())
{
long id = dataR.GetInt64(0);
string name = dataR.GetString(1);
int age = dataR.GetInt32(2);
int noEvents = dataR.GetInt32(3);
Child ch = new Child {
Id = id, Name = name, Age = age
};
ChildNoEventsDTO child = new ChildNoEventsDTO {
Child = ch, NoEvents = noEvents
};
children.Add(child);
}
}
}
return(children);
}
public List <User> findAll()
{
IDbConnection con = DBUtils.getConnection();
List <User> users = new List <User>();
using (var comm = con.CreateCommand())
{
comm.CommandText = "select * from user";
using (var dataR = comm.ExecuteReader())
{
if (dataR.Read())
{
string username = dataR.GetString(0);
string password = dataR.GetString(1);
User user = new User(username, password);
users.Add(user);
}
}
}
return(users);
}
public Entry Update(Entry entry)
{
log.InfoFormat("Entering Update with Entry {0}", entry);
var con = DBUtils.getConnection();
using (var comm = con.CreateCommand())
{
comm.CommandText = "update Entries set date = @date where kid = @kid and cid = @cid)";
var paramDate = comm.CreateParameter();
paramDate.ParameterName = "@date";
paramDate.Value = entry.Date;
comm.Parameters.Add(paramDate);
IDbDataParameter paramKId = comm.CreateParameter();
paramKId.ParameterName = "@kid";
paramKId.Value = entry.Id.Left;
comm.Parameters.Add(paramKId);
IDbDataParameter paramCId = comm.CreateParameter();
paramCId.ParameterName = "@cid";
paramCId.Value = entry.Id.Right;
comm.Parameters.Add(paramCId);
var result = comm.ExecuteNonQuery();
if (result == 0)
{
log.InfoFormat("Exiting Update with value {0}", entry);
return(entry);
}
else
{
log.InfoFormat("Exiting Update with value {0}", null);
return(null);
}
}
}
public Employee Update(Employee employee)
{
log.InfoFormat("Entering Update with Employee {0}", employee);
var con = DBUtils.getConnection();
using (var comm = con.CreateCommand())
{
comm.CommandText = "update Employees set username = @username , password = @password where id = @id)";
var paramUsername = comm.CreateParameter();
paramUsername.ParameterName = "@username";
paramUsername.Value = employee.Username;
comm.Parameters.Add(paramUsername);
var paramPassowrd = comm.CreateParameter();
paramPassowrd.ParameterName = "@password";
paramPassowrd.Value = employee.Password;
comm.Parameters.Add(paramPassowrd);
IDbDataParameter paramId = comm.CreateParameter();
paramId.ParameterName = "@id";
paramId.Value = employee.Id;
comm.Parameters.Add(paramId);
var result = comm.ExecuteNonQuery();
if (result == 0)
{
log.InfoFormat("Exiting Update with value {0}", employee);
return(employee);
}
else
{
log.InfoFormat("Exiting Update with value {0}", null);
return(null);
}
}
}
public Child Update(Child child)
{
log.InfoFormat("Entering Update with Child {0}", child);
var con = DBUtils.getConnection();
using (var comm = con.CreateCommand())
{
comm.CommandText = "update Child set name = @name , age = @age where id = @id)";
var paramName = comm.CreateParameter();
paramName.ParameterName = "@name";
paramName.Value = child.Name;
comm.Parameters.Add(paramName);
var paramAge = comm.CreateParameter();
paramAge.ParameterName = "@age";
paramAge.Value = child.Age;
comm.Parameters.Add(paramAge);
IDbDataParameter paramId = comm.CreateParameter();
paramId.ParameterName = "@id";
paramId.Value = child.Id;
comm.Parameters.Add(paramId);
var result = comm.ExecuteNonQuery();
if (result == 0)
{
log.InfoFormat("Exiting Update with value {0}", child);
return(child);
}
else
{
log.InfoFormat("Exiting Update with value {0}", null);
return(null);
}
}
}
public void delete(int idExcursie)
{
log.Info("Stergere excursie");
IDbConnection con = DBUtils.getConnection();
using (var comm = con.CreateCommand())
{
comm.CommandText = "delete from excursie where idExcursie=@idExcursie";
IDbDataParameter paramId = comm.CreateParameter();
paramId.ParameterName = "@idExcursie";
paramId.Value = idExcursie;
comm.Parameters.Add(paramId);
var dataR = comm.ExecuteNonQuery();
if (dataR == 0)
{
log.Info("Nu a fost stearsa excursia");
}
else
{
log.Info("Excursia a fost stearsa");
}
}
}
public void delete(string username)
{
log.Info("Stergere user");
IDbConnection con = DBUtils.getConnection();
using (var comm = con.CreateCommand())
{
comm.CommandText = "delete from user where username=@username";
IDbDataParameter paramId = comm.CreateParameter();
paramId.ParameterName = "@username";
paramId.Value = username;
comm.Parameters.Add(paramId);
var dataR = comm.ExecuteNonQuery();
if (dataR == 0)
{
log.Info("Nu a fost sters user-ul");
}
else
{
log.Info("User sters cu succes");
}
}
}
public Challenge Save(Challenge challenge)
{
log.InfoFormat("Entering Save with Challenge {0}", challenge);
var con = DBUtils.getConnection();
using (var comm = con.CreateCommand())
{
comm.CommandText = "insert into Challenge(minimum_age,maximum_age,name) values (@minimum_age , @maximum_age , @name)";
var paramMinimumAge = comm.CreateParameter();
paramMinimumAge.ParameterName = "@minimum_age";
paramMinimumAge.Value = challenge.MinimumAge;
comm.Parameters.Add(paramMinimumAge);
var paramMaximumAge = comm.CreateParameter();
paramMaximumAge.ParameterName = "@maximum_age";
paramMaximumAge.Value = challenge.MaximumAge;
comm.Parameters.Add(paramMaximumAge);
var paramName = comm.CreateParameter();
paramName.ParameterName = "@name";
paramName.Value = challenge.Name;
comm.Parameters.Add(paramName);
var result = comm.ExecuteNonQuery();
if (result == 0)
{
log.InfoFormat("Exiting Save with value {0}", challenge);
return(challenge);
}
else
{
log.InfoFormat("Exiting Save with value {0}", null);
return(null);
}
}
}
public void save(User user)
{
log.Info("Adaugare user");
var con = DBUtils.getConnection();
if (findOne(user.Username) == new User(null, null))
{
using (var comm = con.CreateCommand())
{
comm.CommandText = "insert into user values (@username, @password)";
var paramName = comm.CreateParameter();
paramName.ParameterName = "@username";
paramName.Value = user.Username;
comm.Parameters.Add(paramName);
var paramPassword = comm.CreateParameter();
paramPassword.ParameterName = "@password";
paramPassword.Value = user.Password;
comm.Parameters.Add(paramPassword);
var result = comm.ExecuteNonQuery();
if (result == 0)
{
log.Info("Nu a fost adaugat user-ul");
}
else
{
log.Info("User adaugat cu succes");
}
}
}
else
{
log.Info("Exista deja user cu acest username");
}
}
public Entry Save(Entry entry)
{
log.InfoFormat("Entering Save with Entry {0}", entry);
var con = DBUtils.getConnection();
using (var comm = con.CreateCommand())
{
comm.CommandText = "insert into Entries values (@kid, @cid, @date)";
var paramKId = comm.CreateParameter();
paramKId.ParameterName = "@kid";
paramKId.Value = entry.Id.Left;
comm.Parameters.Add(paramKId);
var paramCId = comm.CreateParameter();
paramCId.ParameterName = "@cid";
paramCId.Value = entry.Id.Right;
comm.Parameters.Add(paramCId);
var paramDate = comm.CreateParameter();
paramDate.ParameterName = "@date";
paramDate.Value = entry.Date;
comm.Parameters.Add(paramDate);
var result = comm.ExecuteNonQuery();
if (result == 0)
{
log.InfoFormat("Exiting Save with value {0}", entry);
return(entry);
}
else
{
log.InfoFormat("Exiting Save with value {0}", null);
return(null);
}
}
}
public long Save(Entry entry)
{
IDbConnection con = DBUtils.getConnection();
using (var comm = con.CreateCommand())
{
comm.CommandText = "INSERT INTO Entry (IDChild, Event1, Event2, AgeGroup) VALUES (@idChild, @event1, @event2, @ageGroup);";
IDbDataParameter paramIdChild = comm.CreateParameter();
paramIdChild.ParameterName = "@idChild";
paramIdChild.Value = entry.Child.Id.ToString();
comm.Parameters.Add(paramIdChild);
IDbDataParameter paramEvent1 = comm.CreateParameter();
paramEvent1.ParameterName = "@event1";
paramEvent1.Value = entry.Event1.ToString();
comm.Parameters.Add(paramEvent1);
IDbDataParameter paramEvent2 = comm.CreateParameter();
paramEvent2.ParameterName = "@event2";
paramEvent2.Value = entry.Event2.ToString();
comm.Parameters.Add(paramEvent2);
IDbDataParameter paramAgeGroup = comm.CreateParameter();
paramAgeGroup.ParameterName = "@ageGroup";
paramAgeGroup.Value = entry.AgeGroup.ToString();
comm.Parameters.Add(paramAgeGroup);
var result = comm.ExecuteNonQuery();
if (result == 0)
{
throw new Exception("No task added!");
}
return(0);
}
}
public Child FindByProperties(string name, int age)
{
log.InfoFormat("Entering FindByProperties with name {0} , age {1}", name, age);
IDbConnection con = DBUtils.getConnection();
using (var comm = con.CreateCommand())
{
comm.CommandText = "select id , name, age from Child where name = @name and age = @age";
IDbDataParameter paramName = comm.CreateParameter();
paramName.ParameterName = "@name";
paramName.Value = name;
comm.Parameters.Add(paramName);
IDbDataParameter paramAge = comm.CreateParameter();
paramAge.ParameterName = "@age";
paramAge.Value = age;
comm.Parameters.Add(paramAge);
using (var dataR = comm.ExecuteReader())
{
if (dataR.Read())
{
long id = dataR.GetInt64(0);
String the_name = dataR.GetString(1);
int the_age = dataR.GetInt32(2);
Child child = new Child(the_name, the_age);
child.Id = id;
log.InfoFormat("Exiting FindByProperties with value {0}", child);
return(child);
}
}
}
log.InfoFormat("Exiting FindByProperties with value {0}", null);
return(null);
}