public static List <Trip> GetTripsInTripTable(string city, string company)
{
List <Trip> trips = new List <Trip>();
using (var db = new AirlinesEntities())
{
if (city != null && company != null)
{
db.Trip.Where(c => c.Company.name.Equals(company) && c.town_from.Equals(city)).ToList().ForEach(t => trips.Add(t));
db.Trip.Where(c => c.Company.name.Equals(company) && c.town_to.Equals(city)).ToList().ForEach(t => trips.Add(t));
}
else if (city != null && company == null)
{
db.Trip.Where(c => c.town_from.Equals(city)).ToList().ForEach(t => trips.Add(t));
db.Trip.Where(c => c.town_to.Equals(city)).ToList().ForEach(t => trips.Add(t));
}
else if (city == null && company != null)
{
trips = db.Trip.Where(c => c.Company.name.Equals(company)).ToList();
}
else if (city == null && company == null)
{
db.Trip.ToList().ForEach(t => trips.Add(t));
}
}
return(trips);
}
private static void EditTripInDB(Trip trip)
{
using (var db = new AirlinesEntities())
{
using (var transaction = db.Database.BeginTransaction())
{
try
{
foreach (var t in db.Trip)
{
if (t.trip_no.Equals(trip.trip_no))
{
t.ID_comp = trip.ID_comp;
t.plane = trip.plane;
t.time_in = trip.time_in;
t.time_out = trip.time_out;
t.town_from = trip.town_from;
t.town_to = trip.town_to;
break;
}
}
db.SaveChanges();
transaction.Commit();
}
catch (Exception ex) { transaction.Rollback(); }
}
}
}
public static int GetPassengerIDByName(string passName)
{
int idPass = 0;
using (var db = new AirlinesEntities())
{
idPass = db.Passenger.Where(p => p.name == passName).Select(id => id.ID_psg).FirstOrDefault();
}
return(idPass);
}
public static Pass_in_trip GetPass_In_Trip(int tripNo, DateTime TripDate, int PassId)
{
Pass_in_trip pass;
using (var db = new AirlinesEntities())
{
pass = db.Pass_in_trip.Where(t => (t.trip_no == tripNo) && (t.date == TripDate) && (t.ID_psg == PassId)).FirstOrDefault();
}
return(pass);
}
public static List <int> GetTripsNumbers()
{
List <int> numbers = new List <int>();
using (var db = new AirlinesEntities())
{
db.Trip.ToList().ForEach(t => numbers.Add(t.trip_no));
}
return(numbers.Distinct().ToList());
}
public static List <string> GetAllPlanes()
{
List <string> planes = new List <string>();
using (var db = new AirlinesEntities())
{
db.Trip.ToList().ForEach(t => planes.Add(t.plane));
}
return(planes.Distinct().ToList());
}
public static List <MainView> GetFlightsByTripNumber(int tripN)
{
List <MainView> list = new List <MainView>();
using (var db = new AirlinesEntities())
{
db.MainView.Where(t => t.Trip_number == tripN).ToList().ForEach(f => list.Add(f));
}
return(list);
}
public static List <PassengersInTripView> GetPassengersInTripView(int TripNo)
{
List <PassengersInTripView> pass_In_Trips = new List <PassengersInTripView>();
using (var db = new AirlinesEntities())
{
pass_In_Trips = db.PassengersInTripView.Where(tn => tn.trip_no == TripNo).ToList();
}
return(pass_In_Trips);
}
public static List <Company> GetAllCompanies()
{
List <Company> companies = new List <Company>();
using (var db = new AirlinesEntities())
{
companies = db.Company.ToList();
}
return(companies);
}
public static List <Passenger> GetAllPassengersData()
{
List <Passenger> passengers = new List <Passenger>();
using (var db = new AirlinesEntities())
{
foreach (var p in db.Passenger)
{
p.name.Trim(); passengers.Add(p);
}
}
return(passengers);
}
private static void AddNewTripToDB(Trip trip)
{
using (var db = new AirlinesEntities())
{
using (var transaction = db.Database.BeginTransaction())
{
try
{
db.Trip.Add(trip);
db.SaveChanges();
transaction.Commit();
}
catch (Exception Ex)
{
transaction.Rollback();
}
}
}
}
public static List <string> GetAllCities()
{
List <string> data = new List <string>();
List <Trip> deps = new List <Trip>();
using (var db = new AirlinesEntities())
{
foreach (var c in db.Trip)
{
data.Add(c.town_from.Trim());
}
foreach (var c in db.Trip)
{
data.Add(c.town_to.Trim());
}
}
return(data.Distinct().ToList());
}
private static void AddPassengerToDB(Passenger pass)
{
using (var db = new AirlinesEntities())
{
using (var transaction = db.Database.BeginTransaction())
{
try
{
db.Passenger.Add(pass);
db.SaveChanges();
transaction.Commit();
}
catch
{
transaction.Rollback();
}
}
}
}
private static void DeleteTripFromDB(Trip trip)
{
using (var db = new AirlinesEntities())
{
using (var transaction = db.Database.BeginTransaction())
{
try
{
db.Trip.Remove(trip);
db.SaveChanges();
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
}
}
}
}
private static void AddNewPassInTripToDB(Pass_in_trip pass_In_Trip)
{
using (var db = new AirlinesEntities())
{
using (var transaction = db.Database.BeginTransaction())
{
try
{
db.Pass_in_trip.Add(pass_In_Trip);
db.SaveChanges();
transaction.Commit();
}
catch
{
transaction.Rollback();
}
}
}
}
private static void AddNewCompanyToDB(Company company)
{
using (var db = new AirlinesEntities())
{
using (var transaction = db.Database.BeginTransaction())
{
try
{
db.Company.Add(company);
db.SaveChanges();
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
}
}
}
}
private static void DeletePassengerFromPass_In_Flight(Pass_in_trip pass)
{
using (var db = new AirlinesEntities())
{
Pass_in_trip pt = db.Pass_in_trip.Where(p => p.trip_no.Equals(pass.trip_no) && p.date.Equals(pass.date) && p.ID_psg.Equals(pass.ID_psg)).FirstOrDefault();
using (var transaction = db.Database.BeginTransaction())
{
try
{
db.Pass_in_trip.Remove(pt);
db.SaveChanges();
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
}
}
}
}
private static void AddNewPassInTripToDB(int _TripN, DateTime _tripDate, int _passID, string _passSeat)
{
using (var db = new AirlinesEntities())
{
using (var transaction = db.Database.BeginTransaction())
{
try
{
db.Pass_in_trip.Add(new Pass_in_trip {
trip_no = _TripN, date = _tripDate, ID_psg = _passID, place = _passSeat
});
db.SaveChanges();
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
}
}
}
}
private static void EditPassengerDataInDB(string newPassPlace, string oldPassName, string newPassName, Pass_in_trip trip)
{
using (var db = new AirlinesEntities())
{
using (var transaction = db.Database.BeginTransaction())
{
try
{
Passenger passTochange = db.Passenger.Where(pass => pass.name == oldPassName).First();
passTochange.name = newPassName;
Pass_in_trip pass_in_tripToChange = db.Pass_in_trip.Where(pit => pit.trip_no.Equals(trip.trip_no) && pit.date.Equals(trip.date) && pit.ID_psg.Equals(trip.ID_psg)).First();
pass_in_tripToChange.place = newPassPlace;
db.SaveChanges();
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
}
}
}
}