public bool loginUser(Employee employee)
{
log.InfoFormat("Entered loginUser");
SQLiteConnection connection = JDBCUtils.getConnection();
var command = connection.CreateCommand();
command.CommandText = "Select * from Angajati where Username=@username";
IDbDataParameter parameter = command.CreateParameter();
parameter.ParameterName = "@username";
parameter.Value = employee.Username;
command.Parameters.Add(parameter);
log.InfoFormat("Preparing to execute query.");
using (var dataR = command.ExecuteReader())
{
if (dataR.Read())
{
string password = dataR.GetString(2);
if (employee.Password != password)
{
log.InfoFormat("password incorrect!");
//throw new RepositoryException("password incorrect!");
return(false);
}
log.InfoFormat("query finished successfuly.");
return(true);
}
return(false);
}
}
public int findOne(string landmark, int leavingHour)
{
log.InfoFormat("Entered findOne");
//using (SQLiteConnection connection = JDBCUtils.getConnection())
//{
// try
// {
// string query = "Select ID from Excursii where (Landmark = @landmark) and (LeavingHour = @leavingHour)";
// SQLiteCommand command = new SQLiteCommand(query, connection);
// command.Parameters.AddWithValue("@landmark", landmark);
// command.Parameters.AddWithValue("@leavingHour", leavingHour);
// int id = -1;
// log.InfoFormat("Preparing to execute query.");
// using (SQLiteDataReader reader = command.ExecuteReader())
// {
// if (reader.Read())
// {
// id = reader.GetInt32(0);
// }
// }
// //connection.Close();
// log.InfoFormat("Query finished successfuly.");
// return id;
// }
// catch (Exception ex)
// {
// //connection.Close();
// log.InfoFormat("Exception thrown!");
// Console.WriteLine(ex.StackTrace);
// }
// return -1;
//}
SQLiteConnection connection = JDBCUtils.getConnection();
string query = "Select ID from Excursii where (Landmark = @landmark) and (LeavingHour = @leavingHour)";
SQLiteCommand command = new SQLiteCommand(query, connection);
command.Parameters.AddWithValue("@landmark", landmark);
command.Parameters.AddWithValue("@leavingHour", leavingHour);
int id = -1;
log.InfoFormat("Preparing to execute query.");
using (SQLiteDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
id = reader.GetInt32(0);
}
}
//connection.Close();
log.InfoFormat("Query finished successfuly.");
return(id);
}
public void addBooking(int idClient, int idExcursion, int nrTickets)
{
log.InfoFormat("Entered addBooking");
//using (SQLiteConnection connection = JDBCUtils.getConnection())
//{
// try
// {
// string query = "Insert into Rezervari(IDClient, IDExcursie, NrTickets) values(@idClient,@idExcursion,@nrTickets)";
// SQLiteCommand command = new SQLiteCommand(query, connection);
// command.Parameters.AddWithValue("@idClient", idClient);
// command.Parameters.AddWithValue("@idExcursion", idExcursion);
// command.Parameters.AddWithValue("@nrTickets", nrTickets);
// log.InfoFormat("Preparing to execute query.");
// int successful = command.ExecuteNonQuery();
// if (successful == -1)
// {
// log.InfoFormat("Couldn't add record to database.");
// }
// else
// {
// log.InfoFormat("Query finished successfuly.");
// }
// //connection.Close();
// }
// catch (Exception ex)
// {
// //connection.Close();
// log.InfoFormat("Exception thrown!");
// Console.WriteLine(ex.StackTrace);
// }
//}
SQLiteConnection connection = JDBCUtils.getConnection();
string query = "Insert into Rezervari(IDClient, IDExcursie, NrTickets) values(@idClient,@idExcursion,@nrTickets)";
SQLiteCommand command = new SQLiteCommand(query, connection);
command.Parameters.AddWithValue("@idClient", idClient);
command.Parameters.AddWithValue("@idExcursion", idExcursion);
command.Parameters.AddWithValue("@nrTickets", nrTickets);
log.InfoFormat("Preparing to execute query.");
int successful = command.ExecuteNonQuery();
if (successful == -1)
{
log.InfoFormat("Couldn't add record to database.");
}
else
{
log.InfoFormat("Query finished successfuly.");
}
}
public void deleteBooking(int idClient, int idExcursion)
{
log.InfoFormat("Entered deleteBooking");
//using (SQLiteConnection connection = JDBCUtils.getConnection())
//{
// try
// {
// string query = "Delete from Rezervari where IDClient = @idClient and IDExcursie = @idExcursion";
// SQLiteCommand command = new SQLiteCommand(query, connection);
// command.Parameters.AddWithValue("@idClient", idClient);
// command.Parameters.AddWithValue("@idExcursion", idExcursion);
// log.InfoFormat("Preparing to execute query.");
// int successful = command.ExecuteNonQuery();
// if (successful == -1)
// {
// log.InfoFormat("Couldn't delete record to database.");
// }
// else
// {
// log.InfoFormat("Query finished successfuly.");
// }
// //connection.Close();
// }
// catch (Exception ex)
// {
// //connection.Close();
// log.InfoFormat("Exception thrown!");
// Console.WriteLine(ex.StackTrace);
// }
//}
SQLiteConnection connection = JDBCUtils.getConnection();
string query = "Delete from Rezervari where IDClient = @idClient and IDExcursie = @idExcursion";
SQLiteCommand command = new SQLiteCommand(query, connection);
command.Parameters.AddWithValue("@idClient", idClient);
command.Parameters.AddWithValue("@idExcursion", idExcursion);
log.InfoFormat("Preparing to execute query.");
int successful = command.ExecuteNonQuery();
if (successful == -1)
{
log.InfoFormat("Couldn't delete record to database.");
}
else
{
log.InfoFormat("Query finished successfuly.");
}
}
public int findOne(Client client)
{
log.InfoFormat("Entered findClient");
//using (SQLiteConnection connection = JDBCUtils.getConnection())
//{
// try
// {
// string query = "Select ID from Clienti where LastName = @lastName and FirstName = @firstName and TelephoneNr = @telephoneNr";
// SQLiteCommand command = new SQLiteCommand(query, connection);
// command.Parameters.AddWithValue("@lastName", client.LastName);
// command.Parameters.AddWithValue("@firstName", client.FirstName);
// command.Parameters.AddWithValue("@telephoneNr", client.TelephoneNr);
// int id = -1;
// using (SQLiteDataReader reader = command.ExecuteReader())
// {
// if (reader.Read())
// {
// id = reader.GetInt32(0);
// }
// else
// {
// string queryAdd = "Insert into Clienti(LastName, FirstName, TelephoneNr) values (@lastName,@firstName,@telephoneNr)";
// SQLiteCommand commandAdd = new SQLiteCommand(queryAdd, connection);
// commandAdd.Parameters.AddWithValue("@lastName", client.LastName);
// commandAdd.Parameters.AddWithValue("@firstName", client.FirstName);
// commandAdd.Parameters.AddWithValue("@telephoneNr", client.TelephoneNr);
// commandAdd.ExecuteNonQuery();
// string querySearch = "Select ID from Clienti where LastName = @lastName and FirstName = @firstName and TelephoneNr = @telephoneNr";
// SQLiteCommand commandSearch = new SQLiteCommand(querySearch, connection);
// commandSearch.Parameters.AddWithValue("@lastName", client.LastName);
// commandSearch.Parameters.AddWithValue("@firstName", client.FirstName);
// commandSearch.Parameters.AddWithValue("@telephoneNr", client.TelephoneNr);
// using (SQLiteDataReader reader2 = commandSearch.ExecuteReader())
// {
// if (reader2.Read())
// {
// id = reader.GetInt32(0);
// }
// }
// }
// }
// //connection.Close();
// return id;
// }
// catch (Exception ex)
// {
// //connection.Close();
// log.InfoFormat("Exception thrown!");
// Console.WriteLine(ex.StackTrace);
// }
// return -1;
//}
SQLiteConnection connection = JDBCUtils.getConnection();
string query = "Select ID from Clienti where LastName = @lastName and FirstName = @firstName and TelephoneNr = @telephoneNr";
SQLiteCommand command = new SQLiteCommand(query, connection);
command.Parameters.AddWithValue("@lastName", client.LastName);
command.Parameters.AddWithValue("@firstName", client.FirstName);
command.Parameters.AddWithValue("@telephoneNr", client.TelephoneNr);
int id = -1;
log.InfoFormat("Preparing to execute query.");
using (SQLiteDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
id = reader.GetInt32(0);
}
else
{
string queryAdd = "Insert into Clienti(LastName, FirstName, TelephoneNr) values (@lastName,@firstName,@telephoneNr)";
SQLiteCommand commandAdd = new SQLiteCommand(queryAdd, connection);
commandAdd.Parameters.AddWithValue("@lastName", client.LastName);
commandAdd.Parameters.AddWithValue("@firstName", client.FirstName);
commandAdd.Parameters.AddWithValue("@telephoneNr", client.TelephoneNr);
commandAdd.ExecuteNonQuery();
string querySearch = "Select ID from Clienti where LastName = @lastName and FirstName = @firstName and TelephoneNr = @telephoneNr";
SQLiteCommand commandSearch = new SQLiteCommand(querySearch, connection);
commandSearch.Parameters.AddWithValue("@lastName", client.LastName);
commandSearch.Parameters.AddWithValue("@firstName", client.FirstName);
commandSearch.Parameters.AddWithValue("@telephoneNr", client.TelephoneNr);
log.InfoFormat("Preparing to execute add query of new client.");
using (SQLiteDataReader reader2 = commandSearch.ExecuteReader())
{
if (reader2.Read())
{
id = reader.GetInt32(0);
}
}
log.InfoFormat("Query finished successfuly.");
}
}
return(id);
}
public List <Excursion> findAllExcursions()
{
log.InfoFormat("Entered findAll");
//using (SQLiteConnection connection = JDBCUtils.getConnection())
//{
// try
// {
// string query = "Select * from Excursii";
// SQLiteCommand command = new SQLiteCommand(query, connection);
// List<Excursion> excursions = new List<Excursion>();
// log.InfoFormat("Preparing to execute query.");
// using (SQLiteDataReader reader = command.ExecuteReader())
// {
// while (reader.Read())
// {
// string land = reader.GetString(1);
// int leavingHour = reader.GetInt32(2);
// int availablePlaces = reader.GetInt32(3);
// string transportCompany = reader.GetString(4);
// double price = reader.GetDouble(5);
// Excursion excursion = new Excursion(land, leavingHour, availablePlaces, transportCompany, price);
// excursions.Add(excursion);
// }
// }
// //connection.Close();
// log.InfoFormat("Query finished successfuly.");
// return excursions;
// }
// catch (Exception ex)
// {
// //connection.Close();
// log.InfoFormat("Exception thrown!");
// Console.WriteLine(ex.StackTrace);
// }
// return null;
//}
SQLiteConnection connection = JDBCUtils.getConnection();
string query = "Select * from Excursii";
SQLiteCommand command = new SQLiteCommand(query, connection);
List <Excursion> excursions = new List <Excursion>();
log.InfoFormat("Preparing to execute query.");
using (SQLiteDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
string land = reader.GetString(1);
int leavingHour = Int32.Parse(reader.GetString(2));
int availablePlaces = reader.GetInt32(3);
string transportCompany = reader.GetString(4);
double price = reader.GetDouble(5);
Excursion excursion = new Excursion(land, leavingHour, availablePlaces, transportCompany, price);
excursions.Add(excursion);
}
}
log.InfoFormat("Query finished successfuly.");
return(excursions);
}
public void updateTickets(int idExcursion, int nrTickets)
{
log.InfoFormat("Entered updateTickets");
//using (SQLiteConnection connection = JDBCUtils.getConnection())
//{
// try
// {
// string queryFind = "Select AvailablePlaces from Excursii where ID = @id";
// SQLiteCommand commandFind = new SQLiteCommand(queryFind, connection);
// commandFind.Parameters.AddWithValue("@id", idExcursion);
// int availableP = 0;
// log.InfoFormat("Preparing to execute query.");
// using (SQLiteDataReader reader = commandFind.ExecuteReader())
// {
// if (reader.Read())
// {
// availableP = reader.GetInt32(0);
// }
// }
// string query = "Update Excursii set AvailablePlaces = @availablePlaces where ID = @id";
// SQLiteCommand command = new SQLiteCommand(query, connection);
// int newPlaces = availableP - nrTickets;
// command.Parameters.AddWithValue("@availablePlaces", newPlaces);
// command.Parameters.AddWithValue("@id", idExcursion);
// command.ExecuteNonQuery();
// //connection.Close();
// log.InfoFormat("Query finished successfuly.");
// }
// catch (Exception ex)
// {
// //connection.Close();
// log.InfoFormat("Exception thrown!");
// Console.WriteLine(ex.StackTrace);
// }
//}
SQLiteConnection connection = JDBCUtils.getConnection();
string queryFind = "Select AvailablePlaces from Excursii where ID = @id";
SQLiteCommand commandFind = new SQLiteCommand(queryFind, connection);
commandFind.Parameters.AddWithValue("@id", idExcursion);
int availableP = 0;
log.InfoFormat("Preparing to execute query.");
using (SQLiteDataReader reader = commandFind.ExecuteReader())
{
if (reader.Read())
{
availableP = reader.GetInt32(0);
}
}
string query = "Update Excursii set AvailablePlaces = @availablePlaces where ID = @id";
SQLiteCommand command = new SQLiteCommand(query, connection);
int newPlaces = availableP - nrTickets;
command.Parameters.AddWithValue("@availablePlaces", newPlaces);
command.Parameters.AddWithValue("@id", idExcursion);
command.ExecuteNonQuery();
log.InfoFormat("Query finished successfuly.");
}