// delete an existing airline from the system
// first delete from ticktes after that from flights and at the end from airline
// remove from data base
public void RemoveAirline(LoginToken <Administrator> token, AirLineCompany airline)
{
if (ValidUserToken(token))
{
_airlineDAO.Remove(airline);
}
}
/// <summary>
/// Creates a new airline company and adds it to the table in the DB.
/// </summary>
public void CreateNewAirline(LoginToken <Administrator> token, AirLineCompany airline)
{
if (ValidUserToken(token))
{
_airlineDAO.ADD(airline);
}
}
// delete an existing airline from the system
// first delete from ticktes after that from flights and at the end from airline
// remove from data base
public void RemoveAirline(LoginToken <Administrator> token, AirLineCompany airline)
{
if (ValidUserToken(token))
{
//IList<Flight> flights = _flightDAO.GetAll();
//IList<Ticket> tickets = _ticketDAO.GetAll();
//foreach (Flight f in flights)
//{
// if (f.AirLineCompanyID == airline.ID)
// {
// foreach (Ticket t in tickets)
// {
// if (t.FlightID == f.ID)
// {
// _ticketDAO.Remove(t);
// }
// }
// _flightDAO.Remove(f);
// }
//} ANOTHER WAY BUT TO HEAVY
_airlineDAO.Remove(airline);
}
}
public AirLineCompany GetAirlineByUsername(string username)
{
StringBuilder sb = new StringBuilder();
using (SqlConnection conn = new SqlConnection(FlightCenterConfig.DAO_CON))
{
AirLineCompany airLineCompany = new AirLineCompany();
sb.Append($"SELECT * FROM AirlineCompanies");
sb.Append($"WHERE(USER_NAME = {username})");
string SQL = sb.ToString();
SqlCommand cmd = new SqlCommand(SQL, conn);
//SqlDataReader sqlDataReader = cmd.ExecuteReader();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
AirLineCompany a = new AirLineCompany
{
ID = (Int64)reader["ID"],
AirLineName = (string)reader["AIRLINE_NAME"],
UserName = (string)reader["USER_NAME"],
Password = (string)reader["PASSWORD"],
CountryCode = (Int64)reader["COUNTRY_CODE"]
};
airLineCompany = a;
}
}
return(airLineCompany);
}
}
public AirLineCompany GetAirlineByUsername(string username)
{
AirLineCompany airLineCompany = null;
using (cmd.Connection = new SqlConnection(FlightCenterConfig.DAO_CON))
{
cmd.Connection.Open();
cmd.CommandType = CommandType.Text;
cmd.CommandText = $"SELECT * FROM AirlineCompanies WHERE USER_NAME = '{username}'";
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
AirLineCompany a = new AirLineCompany
{
ID = (long)reader["ID"],
AirLineName = (string)reader["AIRLINE_NAME"],
UserName = (string)reader["USER_NAME"],
Password = (string)reader["PASSWORD"],
CountryCode = (long)reader["COUNTRY_CODE"]
};
airLineCompany = a;
}
if (airLineCompany == null)
{
return(null);
}
return(airLineCompany);
}
}
}
public IList <AirLineCompany> GetAll()
{
StringBuilder sb = new StringBuilder();
using (SqlConnection conn = new SqlConnection(FlightCenterConfig.DAO_CON))
{
IList <AirLineCompany> airLineCompanys = new List <AirLineCompany>();
sb.Append($"SELECT * FROM AirlineCompanies");
string SQL = sb.ToString();
SqlCommand cmd = new SqlCommand(SQL, conn);
//SqlDataReader sqlDataReader = cmd.ExecuteReader();
using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.Default))
//using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
AirLineCompany airLineCompany = new AirLineCompany
{
ID = (Int64)reader["ID"],
AirLineName = (string)reader["AIRLINE_NAME"],
UserName = (string)reader["USER_NAME"],
Password = (string)reader["PASSWORD"],
CountryCode = (Int64)reader["COUNTRY_CODE"]
};
airLineCompanys.Add(airLineCompany);
}
}
return(airLineCompanys);
}
}
public long ADD(AirLineCompany t)
{
SqlCommand cmd2 = new SqlCommand();
lock (key)
{
{
using (cmd.Connection = new SqlConnection(FlightCenterConfig.DAO_CON))
{
cmd.Connection.Open();
cmd.CommandType = CommandType.Text;
cmd.CommandText = $"SELECT COUNT(*) FROM AirlineCompanies WHERE USER_NAME = '{t.UserName}' OR EXISTS (SELECT USER_NAME FROM Customers WHERE USER_NAME = '{t.UserName}')";
string res = cmd.ExecuteScalar().ToString();
if (res != "0")
{
throw new AlreadyExistException($"AirlineCompany user name {t.UserName} already exists");
}
cmd.Connection.Close();
}
using (cmd2.Connection = new SqlConnection(FlightCenterConfig.DAO_CON))
{
cmd2.Connection.Open();
cmd2.CommandType = CommandType.Text;
cmd2.CommandText = $"INSERT INTO AirlineCompanies(AIRLINE_NAME, USER_NAME, PASSWORD, COUNTRY_CODE)" +
$"values('{ t.AirLineName}', '{ t.UserName}', '{ t.Password}', { t.CountryCode});" +
$"SELECT ID FROM AirlineCompanies WHERE USER_NAME = '{t.UserName}'";
t.ID = (long)cmd2.ExecuteScalar();
}
return(t.ID);
}
}
}
public IList <AirLineCompany> GetAllAirlineByCountry(long countryid)
{
IList <AirLineCompany> airLineCompanys = new List <AirLineCompany>();
using (cmd.Connection = new SqlConnection(FlightCenterConfig.DAO_CON))
{
cmd.Connection.Open();
cmd.CommandType = CommandType.Text;
cmd.CommandText = $"SELECT * FROM AirlineCompanies WHERE COUNTRY_CODE = {countryid} ";
using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.Default))
{
while (reader.Read())
{
AirLineCompany airLineCompany = new AirLineCompany
{
ID = (long)reader["ID"],
AirLineName = (string)reader["AIRLINE_NAME"],
UserName = (string)reader["USER_NAME"],
Password = (string)reader["PASSWORD"],
CountryCode = (long)reader["COUNTRY_CODE"]
};
airLineCompanys.Add(airLineCompany);
}
}
return(airLineCompanys);
}
}
//// update airline details in db
public void ModifyAirlineDetails(LoginToken <AirLineCompany> token, AirLineCompany airline)
{
if (ValidUserToken(token))
{
_airlineDAO.Update(airline);
}
}
//// updating the info of an airline company in the system
// updating the table in the database
public void UpdateAirlineDetails(LoginToken <Administrator> token, AirLineCompany airline)
{
if (ValidUserToken(token))
{
_airlineDAO.Update(airline);
}
}
/// <summary>
/// Creates a new airline company and adds it to the table in the DB amd take out his id
/// </summary>
public long CreateNewAirline(LoginToken <Administrator> token, AirLineCompany airline)
{
if (ValidUserToken(token))
{
airline.ID = _airlineDAO.ADD(airline);
}
return(airline.ID);
}
public override bool Equals(object obj)
{
if (ReferenceEquals(obj, null))
{
return(false);
}
AirLineCompany country = obj as AirLineCompany;
return(this.ID == country.ID);
}
public void Update(AirLineCompany t)
{
using (cmd.Connection = new SqlConnection(FlightCenterConfig.DAO_CON))
{
cmd.Connection.Open();
cmd.CommandType = CommandType.Text;
cmd.CommandText = $"UPDATE AirlineCompanies SET AIRLINE_NAME = '{t.AirLineName}', USER_NAME = '{t.UserName}', PASSWORD = '******', COUNTRY_CODE = {t.CountryCode}" +
$" WHERE ID = {t.ID} ";
cmd.ExecuteNonQuery();
}
}
public void Update(AirLineCompany t)
{
StringBuilder sb = new StringBuilder();
using (SqlConnection conn = new SqlConnection(FlightCenterConfig.DAO_CON))
{
sb.Append($"UPDATE AirlineCompanies SET AIRLINE_NAME = '{t.AirLineName}', USER_NAME = '{t.UserName}', PASSWORD = '******', COUNTRY_CODE = {t.CountryCode}");
sb.Append($"WHERE(ID = {t.ID} ) ");
string SQL = sb.ToString();
SqlCommand cmd = new SqlCommand(SQL, conn);
}
}
public void ADD(AirLineCompany t)
{
StringBuilder sb = new StringBuilder();
using (SqlConnection conn = new SqlConnection(FlightCenterConfig.DAO_CON))
{
sb.Append($"INSERT INTO AirlineCompanies(AIRLINE_NAME, USER_NAME, PASSWORD, COUNTRY_CODE)");
sb.Append($"values ({t.AirLineName}, {t.UserName}, {t.Password}, {t.CountryCode})");
string SQL = sb.ToString();
SqlCommand cmd = new SqlCommand(SQL, conn);
}
}
public void Remove(AirLineCompany airlinecompany)
{
using (cmd.Connection = new SqlConnection(FlightCenterConfig.DAO_CON))
{
cmd.Connection.Open();
cmd.CommandType = CommandType.Text;
cmd.CommandText = $"DELETE FROM Tickets WHERE Tickets.FLIGHT_ID = (select Flights.ID from Flights WHERE Flights.AIRLINE_COMPANY_ID = { airlinecompany.ID});" +
$"DELETE FROM Flights WHERE Flights.AIRLINE_COMPANY_ID = {airlinecompany.ID});" +
$"DELETE FROM AirlineCompanies WHERE (ID = {airlinecompany.ID});";
cmd.ExecuteNonQuery();
}
}
public void Remove(AirLineCompany airlinecompany)
{
StringBuilder sb = new StringBuilder();
using (SqlConnection conn = new SqlConnection(FlightCenterConfig.DAO_CON))
{
sb.Append($"DELETE FROM Tickets WHERE Tickets.FLIGHT_ID = (select Flights.ID from Flights WHERE Flights.AIRLINE_COMPANY_ID = {airlinecompany.ID}");
sb.Append($"DELETE FROM Flights WHERE Flights.AIRLINE_COMPANY_ID = {airlinecompany.ID}");
sb.Append($"DELETE FROM AirlineCompanies WHERE (ID = {airlinecompany.ID} )");
string SQL = sb.ToString();
SqlCommand cmd = new SqlCommand(SQL, conn);
}
}
public bool TryAirLineLogin(string username, string password, out LoginToken <AirLineCompany> token)
{
AirLineCompany company = _airlineDAO.GetAirlineByUsername(username);
if (company != null)
{
if (company.Password == password)
{
token = new LoginToken <AirLineCompany>()
{
User = company
};
return(true);
}
throw new WrongPasswordException("airline company wrong password");
}
token = null;
return(false);
}
public void ChangePassword(AirLineCompany airlinecompany)
{
}