/// <summary>
/// SQL Statemnet for getting a passengers ID
/// </summary>
/// <param name="FirstName"></param>
/// <param name="LastName"></param>
/// <returns></returns>
public string GetPassengerID(string FirstName, string LastName)
{
try
{
//SQL statement for getting a passengers ID
string sSQL = "SELECT Passenger_ID from Passenger where First_Name = '" + FirstName + "' AND Last_Name = '" + LastName + "'";
db = new clsDataAccess();
//excute
string tempID = db.ExecuteScalarSQL(sSQL);
//if the passenger is not null
if (passenger != null)
{
return(passenger.sID = db.ExecuteScalarSQL(sSQL));
}
else
{
return(tempID);
}
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
/// <summary>
/// Add a passenger with the desired first name and last name into the database
/// </summary>
/// <param name="firstName"></param>
/// <param name="lastName"></param>
public void AddPassenger(string firstName, string lastName)
{
try
{
string sSQL = $"INSERT INTO PASSENGER(First_Name, Last_Name) VALUES('{firstName}','{lastName}')";
clsData.ExecuteScalarSQL(sSQL);
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
/// <summary>
/// Insert a passenger into the database
/// </summary>
/// <param name="passenger"></param>
/// <returns></returns>
internal static int InsertPassenger(Passenger passenger)
{
try
{
var repository = new clsDataAccess();
var sql = "select max(passenger_id) from passenger";
var result = repository.ExecuteScalarSQL(sql);
if (!int.TryParse(result, out var passengerId))
{
passengerId = 1;
}
else
{
++passengerId;
}
sql = $"Insert into Passenger (passenger_id, first_name, last_name) values ({passengerId}, '{passenger.FirstName}', '{passenger.LastName}')";
repository.ExecuteNonQuery(sql);
return(passengerId);
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
public string GetPassengerSeatNumber(int PassengerID)
{
//SQL Statement for getting passengers first name by passenger ID
string sSQL = "SELECT Seat_Number from Passenger_Link where Passenger_ID = " + PassengerID;
db = new clsDataAccess();
string temp = db.ExecuteScalarSQL(sSQL);
return(temp);
}
/// <summary>
/// Find the passengers Id based on first and last name
/// </summary>
/// <param name="first">Passenger First name</param>
/// <param name="last">Passenger Last name</param>
/// <returns>Passenger Id as string</returns>
public string FindPassengersId(string first, string last)
{
try
{
string pId = accessDatabase.ExecuteScalarSQL(SQL.getPassengerId(first, last));
return(pId);
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
/// <summary>
/// Returns true if the passenger for the passengerId exists
/// </summary>
/// <param name="passengerId"></param>
/// <returns></returns>
public static bool PassengerExists(int passengerId)
{
try
{
var repository = new clsDataAccess();
var sql = $"select 1 from passenger where passenger_id = {passengerId}";
var exists = repository.ExecuteScalarSQL(sql);
return(!string.IsNullOrWhiteSpace(exists));
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
/// <summary>
/// Returns a list of taken seats
/// </summary>
/// <param name="FlightId"></param>
/// <returns></returns>
private int GetTakenSeats(int Passenger)
{
DataSet ds;
//List<int> TakenSeats = new List<int>();
int Seat;
string sSQL;
int iCount = 0;
string test;
sSQL = "Select Seat_Number FROM Flight_Passenger_Link where passenger_ID =" + Passenger;
// ds = db.ExecuteScalarSQL(sSQL, ref iCount);
Int32.TryParse(db.ExecuteScalarSQL(sSQL), out Seat);
return(Seat);
}
/// <summary>
/// Method to get Passenger ID
/// </summary>
/// <param name="firstName"></param>
/// <param name="lastName"></param>
/// <returns></returns>
public string GetPassengerID(string firstName, string lastName)
{
try
{
clsDataAccess dbPassengersID = new clsDataAccess();
string passengerID;
string SQLPassengersID;
SQLPassengersID = SQL.PassengersID(firstName, lastName);
passengerID = dbPassengersID.ExecuteScalarSQL(SQLPassengersID);
return(passengerID);
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." +
MethodBase.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
/// <summary>
/// SQL Statement for getting a passengers last name by passenger ID
/// </summary>
/// <param name="PassengerID"></param>
/// <returns></returns>
public string GetPassengerLastName(int PassengerID)
{
try
{
//SQL Statement for geting a passengers last name by passenger ID
string sSQL = "SELECT Last_Name from Passenger where Passenger_ID = " + PassengerID;
db = new clsDataAccess();
string temp = db.ExecuteScalarSQL(sSQL);
return(temp);
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
