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();
}
}