public bool DeleteById(int ReservationID)
{
dBConnection.OpenConnection();
SqlCommand cmd = new SqlCommand();
// responsible for cancelling reservation with the given res. id.
_rowsAffected = 0;
try
{
// create the query
cmd.CommandText = DBCommandCreator.EXEC(new string[] { "rezID" }, "SP_rezIptal");
DBCommandCreator.AddParameter(cmd, "@rezID", DbType.Int32, ParameterDirection.Input, ReservationID);
//execute the command
_rowsAffected = dBConnection.ExecuteQueries(cmd);
return(_rowsAffected > 0);
}
catch (Exception ex)
{
throw new Exception("An error occured in DeleteById() func. in SpiceApp.DataAccessLayer.ReservationRepository", ex);
}
finally
{
// close connection
dBConnection.CloseConnection();
}
}
public bool Insert(Reservation entity)
{
/* Car.CarID, User.UserID, DateTime startingDate, DateTime endDate fields need to be given in the parameter */
SqlCommand cmd = new SqlCommand();
dBConnection.OpenConnection();
_rowsAffected = 0;
try
{
cmd.CommandText = DBCommandCreator.EXEC(new string[] { "aracID", "kullaniciID", "basTarih", "bitisTarih" }, "SP_rezYap");
DBCommandCreator.AddParameter(cmd, "@aracID", DbType.Int32, ParameterDirection.Input, entity.Car.CarID);
DBCommandCreator.AddParameter(cmd, "@kullaniciID", DbType.Int32, ParameterDirection.Input, entity.User.UserID);
DBCommandCreator.AddParameter(cmd, "@basTarih", DbType.String, ParameterDirection.Input, DateConverter.ToDatabase(entity.StartingDate));
DBCommandCreator.AddParameter(cmd, "@bitisTarih", DbType.String, ParameterDirection.Input, DateConverter.ToDatabase(entity.EndDate));
_rowsAffected = dBConnection.ExecuteQueries(cmd);
return(_rowsAffected > 0);
}
catch (Exception ex)
{
throw new Exception("An error occured while executing MakeReservation() function in SpiceApp.DataAccessLayer.CarRepository", ex);
}
finally
{
dBConnection.CloseConnection();
}
}
public bool ReActivateCarById(int CarID)
{
// brings back the deleted car (incase a car is not available for a short period for the reasons like fixing issues so then it can be available and active again).
_rowsAffected = 0;
SqlCommand cmd = new SqlCommand();
dBConnection.OpenConnection();
try
{
cmd.CommandText = DBCommandCreator.EXEC(new string[] { "aracID" }, "SP_aracAktif");
DBCommandCreator.AddParameter(cmd, "@aracID", DbType.Int32, ParameterDirection.Input, CarID);
_rowsAffected = dBConnection.ExecuteQueries(cmd);
return(_rowsAffected > 0);
}
catch (Exception ex)
{
throw new Exception("An error occured in ReActivateCarById() in SpiceApp.DataAccessLayer.CarRepository", ex);
}
finally
{
dBConnection.CloseConnection();
}
}
public bool DeleteById(int CarID)
{
// This function is responsible for deleting a car by carID
_rowsAffected = 0;
//open connection
dBConnection.OpenConnection();
SqlCommand cmd = new SqlCommand();
try
{
// create the query
cmd.CommandText = DBCommandCreator.EXEC(new string[] { "aracID" }, "SP_aracPasif");
DBCommandCreator.AddParameter(cmd, "@aracID", DbType.Int32, ParameterDirection.Input, CarID);
//execute the query
_rowsAffected = dBConnection.ExecuteQueries(cmd);
return(_rowsAffected > 0);
}
catch (Exception ex)
{
throw new Exception("An error occured while executing DeleteById() in SpiceApp.DataAccessLayer.CarRepository", ex);
}
finally
{
dBConnection.CloseConnection();
}
}
public bool Insert(RentDetail entity)
{
// responsible for adding new rent detail to the db.
// clean the attribute. otherwise values left from previous operations may cause conflict
_rowsAffected = 0;
//open connection
SqlCommand cmd = new SqlCommand();
dBConnection.OpenConnection();
try
{
cmd.CommandText = DBCommandCreator.EXEC(new string[] { "rezID" }, "SP_anahtarTeslim");
DBCommandCreator.AddParameter(cmd, "@rezID", DbType.Int32, ParameterDirection.Input, entity.RentID);
// execute the query
_rowsAffected = dBConnection.ExecuteQueries(cmd);
// if added, affected rows will be greater than 0
return(_rowsAffected > 0);
}
catch (Exception ex)
{
throw new Exception("An error occured in ReservaationRepository in SpiceApp.DataAccessLayer.ReservationRepository ", ex);
}
finally
{
dBConnection.CloseConnection();
}
}
public bool ReturnCarToCompany(int RentID, int KmInfo, int Score)
{
// responsible for completing the whole rent process. When the customer return the car to company, rent process ends.
// clean the attribute. otherwise values left from previous operations may cause conflict
_rowsAffected = 0;
dBConnection.OpenConnection();
SqlCommand cmd = new SqlCommand();
try
{
cmd.CommandText = DBCommandCreator.EXEC(new string[] { "kiraID", "anlikKm", "puan" }, "SP_aracIade");
DBCommandCreator.AddParameter(cmd, "@kiraID", DbType.Int32, ParameterDirection.Input, RentID);
DBCommandCreator.AddParameter(cmd, "@anlikKm", DbType.Int32, ParameterDirection.Input, KmInfo);
DBCommandCreator.AddParameter(cmd, "@puan", DbType.Int32, ParameterDirection.Input, Score);
_rowsAffected = dBConnection.ExecuteQueries(cmd);
// if succesful, affected rows will be greater than 0
return(_rowsAffected > 0);
}
catch (Exception ex)
{
throw new Exception("An error occured in ReturnCarToCompany() in SpiceApp.DataAccessLayer.RentDetailRepository", ex);
}
finally
{
dBConnection.CloseConnection();
}
}
public List <RentDetail> FetchAllRentDetail(int UserID)
{
// check for out dated reservations, if there is then cancel them
DBAdjuster.AdjustReservations();
//responsible for fetching all rent details from db according to given userID.
// if userID belongs to a customer, stored procedure will return customer's rent details
// if userID belongs to a employee, stored procedure will return employee's company's rent details
dBConnection.OpenConnection();
SqlCommand cmd = new SqlCommand();
SqlDataReader reader = null;
try
{
List <RentDetail> rents = new List <RentDetail>();
// define command text with the help of DBCommandCreator utility class then give user ıd parameter to the query.
cmd.CommandText = DBCommandCreator.EXEC(new string[] { "kullaniciID" }, "SP_kiraGoruntule");
DBCommandCreator.AddParameter(cmd, "@kullaniciID", DbType.Int32, ParameterDirection.Input, UserID);
reader = dBConnection.DataReader(cmd);
if (reader.HasRows)
{
while (reader.Read())
{
// create rent detail instance
var entity = new RentDetail()
{
StartingDate = reader.GetDateTime(3),
EndDate = reader.GetDateTime(4),
KmUsed = reader.GetInt32(5),
Cost = reader.GetInt32(6),
RentID = reader.GetInt32(0),
isCarRecievedBack = reader.GetBoolean(7),
RecievedBackAt = reader.IsDBNull(8) ? new DateTime(1111, 11, 11) : reader.GetDateTime(8)
};
entity.Car = new CarRepository().FetchById(reader.GetInt32(1));
entity.User = new UserRepository().FetchById(reader.GetInt32(2));
// add the instance to the list
rents.Add(entity);
}
}
return(rents);
}
catch (Exception ex)
{
throw new Exception("An error occured in FetchAllRentDetail() func. in SpiceApp.DataAccessLayer.RentDetailRepository", ex);
}
finally
{
if (reader != null)
{
reader.Close();
}
dBConnection.CloseConnection();
}
}
public List <Car> FetchAvailableCarsForResv(int UserID, DateTime startingDate, DateTime endDate)
{ // This function is responsible for fetching all available cars by userID for available reservation
// will hold valid cars for reservation criterias (startingdate, endtime, user's driver license exp. and user's age)
List <Car> cars = new List <Car>();
SqlDataReader reader = null;
SqlCommand cmd = new SqlCommand();
dBConnection.OpenConnection();
//first, adjust previous reservations and discard them.
DBAdjuster.AdjustReservations();
try
{
cmd.CommandText = DBCommandCreator.EXEC(new string[] { "kisiID", "basTarih", "bitisTarih" }, "SP_uygunArac");
DBCommandCreator.AddParameter(cmd, "@kisiID", DbType.Int32, ParameterDirection.Input, UserID);
DBCommandCreator.AddParameter(cmd, "@basTarih", DbType.String, ParameterDirection.Input, DateConverter.ToDatabase(startingDate));
DBCommandCreator.AddParameter(cmd, "@bitisTarih", DbType.String, ParameterDirection.Input, DateConverter.ToDatabase(endDate));
reader = dBConnection.DataReader(cmd);
if (reader.HasRows)
{
while (reader.Read())
{
// creating car instances for every row in the response table
var car = new Car()
{
CarID = reader.GetInt32(0),
CarModel = reader.GetString(1),
RequiredDriverLicenceExp = reader.GetInt32(2),
RequiredAge = reader.GetInt32(3),
KmInfo = reader.GetInt32(5),
HasAirbag = (bool)reader.GetBoolean(6),
BaggageCapacity = reader.GetString(7),
DailyCost = (decimal)reader.GetSqlMoney(8),
Company = new CompanyRepository().FetchById(reader.GetInt32(9)),
Brand = new BrandRepository().FetchById(reader.GetInt32(10))
};
cars.Add(car);
}
}
return(cars);
}
catch (Exception ex)
{
throw new Exception("An error occured in FetchAllByUserForResv() in SpiceApp.DataAccessLayer.CarRepository", ex);
}
finally
{
if (reader != null)
{
reader.Close();
}
dBConnection.CloseConnection();
}
}
public List <Reservation> FetchAllByUserId(int UserID)
{
// check for out dated reservations, if there is then cancel them.
DBAdjuster.AdjustReservations();
//this function will be used for showing reservations either for user or employee. If the given ID belongs to employee, function will show companies reservations.
List <Reservation> list = new List <Reservation>();
SqlCommand cmd = new SqlCommand();
SqlDataReader reader = null;
try
{
dBConnection.OpenConnection();
// defining the command text and giving input parameter's value
cmd.CommandText = DBCommandCreator.EXEC(new string[] { "kullaniciID" }, "SP_rezGoruntule");
DBCommandCreator.AddParameter(cmd, "@kullaniciID", DbType.Int32, ParameterDirection.Input, UserID);
using (reader = dBConnection.DataReader(cmd))
{
if (reader.HasRows)
{
while (reader.Read())
{
// create reservation instance
var entity = new Reservation()
{
Car = new CarRepository().FetchById(reader.GetInt32(1)),
User = new UserRepository().FetchById(UserID),
StartingDate = reader.GetDateTime(3),
EndDate = reader.GetDateTime(4),
ReservationID = reader.GetInt32(0),
ReservationMadeAt = reader.GetDateTime(5),
};
// To define reservation state, we have 3 different boolean values in database.
// So ConvertResvState function composes these 3 values to one to make client's work easier.
entity.ReservationState = ResvAttrConverter.ConvertResvState(reader.GetBoolean(6), reader.GetBoolean(7), reader.GetBoolean(8));
entity.Company = entity.Car.Company;
list.Add(entity);
}
}
}
return(list);
}
catch (Exception ex)
{
throw new Exception("An error occured in FetchAllByUserId() func. in SpiceApp.DataAccessLayer.ReservationRepository", ex);
}
finally
{
if (reader != null)
{
reader.Close();
}
dBConnection.CloseConnection();
}
}
public List <DailyKmInfo> DailyKmReport(int userID)
{
List <DailyKmInfo> data = new List <DailyKmInfo>();
SqlDataReader reader = null;
try
{
dBConnection.OpenConnection();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = DBCommandCreator.EXEC(new string[] { "kullaniciID" }, "SP_gunlukKmListele");
DBCommandCreator.AddParameter(cmd, "@kullaniciID", DbType.Int32, ParameterDirection.Input, userID);
reader = dBConnection.DataReader(cmd);
if (reader.HasRows)
{
while (reader.Read())
{
var temp = new DailyKmInfo()
{
RentID = reader.GetInt32(0),
BrandName = reader.GetString(1),
CarModel = reader.GetString(2),
DailyKm = reader.GetInt32(3),
Date = reader.GetDateTime(4),
State = "Bilgi Yok"
};
data.Add(temp);
}
}
return(data);
}
catch (Exception ex)
{
throw new Exception("An error occured in DailyKmReport() func. in SpiceApp.DataAccessLayer.ReportRepository", ex);
}
finally
{
if (reader != null)
{
reader.Close();
}
dBConnection.CloseConnection();
}
}
public List <RentRate> MonthlyRentRate(int UserID, DateTime Term)
{
List <RentRate> data = new List <RentRate>();
SqlDataReader reader = null;
try
{
dBConnection.OpenConnection();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = DBCommandCreator.EXEC(new string[] { "kullaniciID", "term" }, "SP_aylikAracOran");
DBCommandCreator.AddParameter(cmd, "@kullaniciID", DbType.Int32, ParameterDirection.Input, UserID);
DBCommandCreator.AddParameter(cmd, "@term", DbType.DateTime, ParameterDirection.Input, DateConverter.ToDatabase(Term));
reader = dBConnection.DataReader(cmd);
if (reader.HasRows)
{
while (reader.Read())
{
var temp = new RentRate()
{
CarID = reader.GetInt32(0),
BrandName = reader.GetString(1),
CarModel = reader.GetString(2),
MonthRate = reader.GetDecimal(3),
Term = reader.GetString(4)
};
data.Add(temp);
}
}
return(data);
}
catch (Exception ex)
{
throw new Exception("An error occured in MonthlyRentRate() func. in SpiceApp.DataAccessLayer.ReportRepository", ex);
}
finally
{
if (reader != null)
{
reader.Close();
}
dBConnection.CloseConnection();
}
}
public List <OverKmInfo> OverKmInfo(int UserID)
{
SqlDataReader reader = null;
List <OverKmInfo> data = new List <OverKmInfo>();
try
{
dBConnection.OpenConnection();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = DBCommandCreator.EXEC(new string[] { "kullaniciID" }, "SP_gunlukAsimOran ");
DBCommandCreator.AddParameter(cmd, "@kullaniciID", DbType.Int32, ParameterDirection.Input, UserID);
reader = dBConnection.DataReader(cmd);
if (reader.HasRows)
{
while (reader.Read())
{
var temp = new OverKmInfo()
{
CompanyName = reader.GetString(0),
CompanyBalance = reader.GetDecimal(1),
Score = reader.GetDecimal(2),
OverKmRate = reader.GetDecimal(3),
Term = reader.GetString(4)
};
data.Add(temp);
}
}
return(data);
}
catch (Exception ex)
{
throw new Exception("An error occured in OverKmInfo() func. in SpiceApp.DataAccessLayer.ReportRepository", ex);
}
finally
{
if (reader != null)
{
reader.Close();
}
dBConnection.CloseConnection();
}
}
public List <CompanyBalanceInfo> CompanyBalanceInfo(int UserID)
{
List <CompanyBalanceInfo> data = new List <CompanyBalanceInfo>();
SqlDataReader reader = null;
try
{
dBConnection.OpenConnection();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = DBCommandCreator.EXEC(new string[] { "kullaniciID" }, "SP_sirketDurum");
DBCommandCreator.AddParameter(cmd, "@kullaniciID", DbType.Int32, ParameterDirection.Input, UserID);
reader = dBConnection.DataReader(cmd);
if (reader.HasRows)
{
while (reader.Read())
{
var temp = new CompanyBalanceInfo()
{
CarCount = reader.GetInt32(0),
TotalIncome = reader.GetDecimal(1),
TotalExpenses = reader.GetDecimal(2),
NetValue = reader.GetDecimal(3)
};
data.Add(temp);
}
}
return(data);
}
catch (Exception ex)
{
throw new Exception("An error occured in CompanyBalanceInfo() func. in SpiceApp.DataAccessLayer.ReportRepository", ex);
}
finally
{
if (reader != null)
{
reader.Close();
}
dBConnection.CloseConnection();
}
}
private bool InsertPerson(Person entity)
{
_rowsAffected = 0;
SqlCommand cmd = new SqlCommand();
dBConnection.OpenConnection();
// responsible for adding new person to the db
try
{
cmd.CommandText = DBCommandCreator.EXEC(new string[] { "ad", "soyad", "adres", "cepTel", "email", "ehliyet", "dogTarih" }, "SP_kisiKayit");
// give input parameters' values to the query.
DBCommandCreator.AddParameter(cmd, "@ad", DbType.String, ParameterDirection.Input, entity.Name);
DBCommandCreator.AddParameter(cmd, "@soyad", DbType.String, ParameterDirection.Input, entity.Surname);
DBCommandCreator.AddParameter(cmd, "@email", DbType.String, ParameterDirection.Input, entity.Email);
DBCommandCreator.AddParameter(cmd, "@adres", DbType.String, ParameterDirection.Input, entity.Address);
DBCommandCreator.AddParameter(cmd, "@cepTel", DbType.String, ParameterDirection.Input, entity.Phone);
DBCommandCreator.AddParameter(cmd, "@ehliyet", DbType.Date, ParameterDirection.Input, DateConverter.ToDatabase(entity.DriverLicenseDate));
DBCommandCreator.AddParameter(cmd, "@dogTarih", DbType.Date, ParameterDirection.Input, DateConverter.ToDatabase(entity.Birthday));
// execute the stored procedure.
_rowsAffected = dBConnection.ExecuteQueries(cmd);
return(_rowsAffected > 0);
}
catch (Exception ex)
{
throw new Exception("An error occured in InsertPerson function, SpiceApp.DataAccessLayer.UserRepository", ex);
}
finally
{
dBConnection.CloseConnection();
}
}
public Company FetchById(int CompanyID)
{
//responsible for getting company info with given company id.
Company entity = null;
// we will execute 2 queries so we need another conn.
DBConnection tempDB = new DBConnection();
tempDB.OpenConnection();
dBConnection.OpenConnection();
SqlCommand cmd = new SqlCommand();
SqlCommand cmdForScore = new SqlCommand();
SqlDataReader reader = null;
try
{
cmd.CommandText = DBCommandCreator.SELECT(new string[] { "sirketID", "sirketAd", "tel", "sehir", "adres", "aracSayisi", "sirketPuan" }, DBTableNames.Company, "WHERE sirketID = @CompanyID");
DBCommandCreator.AddParameter(cmd, "@CompanyID", DbType.Int32, ParameterDirection.Input, CompanyID);
reader = dBConnection.DataReader(cmd);
if (reader.HasRows)
{
while (reader.Read())
{
// create company instance with fetched info
entity = new Company()
{
CompanyID = reader.GetInt32(0),
CompanyName = reader.GetString(1),
Phone = reader.GetString(2),
City = reader.GetString(3),
Address = reader.GetString(4),
CarCount = reader.GetInt32(5),
};
// getting score value from a pre-defined stored procedure
cmdForScore.CommandText = DBCommandCreator.EXEC(new string[] { "sirketID" }, "SP_puan");
DBCommandCreator.AddParameter(cmdForScore, "@sirketID", DbType.Int32, ParameterDirection.Input, entity.CompanyID);
entity.Score = Convert.ToString(tempDB.ExecuteScalar(cmdForScore));
}
}
return(entity);
}
catch (Exception ex)
{
throw new Exception("An error occured while executing FetchByID() in SpiceApp.BusinessLayer.CompanyRepository", ex);
}
finally
{
if (reader != null)
{
reader.Close();
}
dBConnection.CloseConnection();
tempDB.CloseConnection();
}
}
public bool Insert(User entity)
{
// responsible for adding new users to the db. Takes a parameter which is type of User.
SqlCommand cmd = new SqlCommand();
DBConnection tempDB = new DBConnection();
SqlDataReader reader = null;
dBConnection.OpenConnection();
tempDB.OpenConnection();
// clean the attribute. otherwise values left from previous operations may cause conflict
_rowsAffected = 0;
try
{
// first add the person object to the db. It holds detailed information of user.
InsertPerson(entity.Person);
dBConnection.OpenConnection();
int id = 0; // will hold the last added person's id
// the last added person will have the greatest id value so bring it from the db.
cmd.CommandText = DBCommandCreator.SELECT(new string[] { "kisiID" }, DBTableNames.Person, "WHERE kisiID = (SELECT MAX(kisiID) FROM tblKisi)");
reader = dBConnection.DataReader(cmd);
if (reader.HasRows)
{
while (reader.Read())
{
id = reader.GetInt32(0);
}
}
SqlCommand cmdUser = new SqlCommand();
// call a stored proc. to add newly created user to the db.
cmdUser.CommandText = DBCommandCreator.EXEC(new string[] { "kullaniciAd", "sifre", "kisiID" }, "SP_kullaniciKayit");
DBCommandCreator.AddParameter(cmdUser, "@kullaniciAd", DbType.String, ParameterDirection.Input, entity.Username);
DBCommandCreator.AddParameter(cmdUser, "@sifre", DbType.String, ParameterDirection.Input, entity.Password);
DBCommandCreator.AddParameter(cmdUser, "@kisiID", DbType.Int32, ParameterDirection.Input, id);
_rowsAffected = tempDB.ExecuteQueries(cmdUser);
// if added, affected rows will be greater than 0
return(_rowsAffected > 0);
}
catch (Exception ex)
{
throw new Exception("An error occured while executing Insert() in SpiceApp.DataAccessLayer.UserRepository", ex);
}
finally
{
if (reader != null)
{
reader.Close();
}
dBConnection.CloseConnection();
tempDB.CloseConnection();
}
}