public string CheckIfCountryExist(Country t)
{
StringBuilder sb = new StringBuilder();
string SQL1 = $"SELECT COUNT(*) FROM Countries WHERE COUNTRY_NAME = '{t.COUNTRY_NAME}'";
string res = DL.ExecuteSqlScalarStatement(SQL1);
return(res);
}
public string CheckIfAdministratorExist(Administrator t)
{
StringBuilder sb = new StringBuilder();
string SQL1 = $"SELECT COUNT(*) FROM Administrators WHERE USER_NAME = '{t.USER_NAME}'";
string res = DL.ExecuteSqlScalarStatement(SQL1);
return(res);
}
public string CheckIfTicketExist(Ticket t)
{
StringBuilder sb = new StringBuilder();
string SQL1 = $"SELECT COUNT(*) FROM Tickets WHERE FLIGHT_ID = {t.FLIGHT_ID} AND CUSTOMER_ID = {t.CUSTOMER_ID}";
string res = DL.ExecuteSqlScalarStatement(SQL1);
return(res);
}
public string CheckIfCustomerExist(Customer t)
{
StringBuilder sb = new StringBuilder();
string SQL1 = $"SELECT COUNT(*) FROM Customers WHERE USER_NAME = '{t.USER_NAME}' OR EXISTS (SELECT USER_NAME FROM AirlineCompanies WHERE USER_NAME = '{t.USER_NAME}')";
string res = DL.ExecuteSqlScalarStatement(SQL1);
return(res);
}
public string CheckIfFlightExist(Flight t)
{
StringBuilder sb = new StringBuilder();
sb.Append($"SELECT COUNT(*) FROM Flights WHERE ID = {t.ID } AND AIRLINECOMPANY_ID = {t.AIRLINECOMPANY_ID}");
string SQL1 = sb.ToString();
string res = DL.ExecuteSqlScalarStatement(SQL1);
return(res);
}
public long Add(Country t)
{
StringBuilder sb = new StringBuilder();
string SQL1 = $"SELECT COUNT(*) FROM Countries WHERE COUNTRY_NAME = '{t.COUNTRY_NAME}'";
string res = DL.ExecuteSqlScalarStatement(SQL1);
if (res == "0")
{
sb = new StringBuilder();
sb.Append($"INSERT INTO Countries(COUNTRY_NAME)");
sb.Append($" values('{ t.COUNTRY_NAME}')");
string SQL = sb.ToString();
DL.ExecuteSqlNonQuery(SQL);
SQL = $"SELECT ID FROM Countries WHERE COUNTRY_NAME ='{t.COUNTRY_NAME}'";
return(Int64.Parse(DL.ExecuteSqlScalarStatement(SQL)));
}
else
{
throw new CountryAlreadyExistException("Country already exists");
}
}
public long Add(Ticket t)
{
StringBuilder sb = new StringBuilder();
string SQL1 = $"SELECT COUNT(*) FROM Tickets WHERE FLIGHT_ID = {t.FLIGHT_ID} AND CUSTOMER_ID = {t.CUSTOMER_ID}";
string res = DL.ExecuteSqlScalarStatement(SQL1);
if (res == "0")
{
sb = new StringBuilder();
sb.Append($"INSERT INTO Tickets(FLIGHT_ID, CUSTOMER_ID)");
sb.Append($" values({ t.FLIGHT_ID}, { t.CUSTOMER_ID})");
string SQL = sb.ToString();
DL.ExecuteSqlNonQuery(SQL);
SQL = $"SELECT ID FROM Tickets WHERE FLIGHT_ID = {t.FLIGHT_ID} AND CUSTOMER_ID = {t.CUSTOMER_ID}";
return(Int64.Parse(DL.ExecuteSqlScalarStatement(SQL)));
}
else
{
throw new TicketAlreadyExistException("Ticket already exists");
}
}
public long Add(Customer t)
{
StringBuilder sb = new StringBuilder();
string SQL1 = $"SELECT COUNT(*) FROM Customers WHERE USER_NAME = '{t.USER_NAME}' OR EXISTS (SELECT USER_NAME FROM AirlineCompanies WHERE USER_NAME = '{t.USER_NAME}')";
string res = DL.ExecuteSqlScalarStatement(SQL1);
if (res == "0")
{
sb = new StringBuilder();
sb.Append($"INSERT INTO Customers(USER_NAME, PASSWORD, FIRST_NAME, LAST_NAME, ADDRESS, PHONE_NO, CREDIT_CARD_NUMBER)");
sb.Append($" values('{ t.USER_NAME}', '{ t.PASSWORD}', '{ t.FIRST_NAME}', '{ t.LAST_NAME}','{ t.ADDRESS}', '{ t.PHONE_NO}', '{ t.CREDIT_CARD_NUMBER}')");
string SQL = sb.ToString();
DL.ExecuteSqlNonQuery(SQL);
SQL = $"SELECT ID FROM Customers WHERE USER_NAME = '{t.USER_NAME}'";
return(Int64.Parse(DL.ExecuteSqlScalarStatement(SQL)));
}
else
{
throw new CustomerAlreadyExistException("Customer already exists");
}
}
public long Add(Administrator t)
{
StringBuilder sb = new StringBuilder();
string SQL1 = $"SELECT COUNT(*) FROM Administrators WHERE USER_NAME = '{t.USER_NAME}'";
string res = DL.ExecuteSqlScalarStatement(SQL1);
if (res == "0")
{
sb = new StringBuilder();
sb.Append($"INSERT INTO Administrators (FIRST_NAME, LAST_NAME, USER_NAME, PASSWORD)");
sb.Append($" values('{ t.FIRST_NAME}', '{ t.LAST_NAME}', '{ t.USER_NAME}', '{ t.PASSWORD}')");
string SQL = sb.ToString();
DL.ExecuteSqlNonQuery(SQL);
SQL = $"SELECT ID FROM Administrators WHERE USER_NAME ='{t.USER_NAME}'";
return(Int64.Parse(DL.ExecuteSqlScalarStatement(SQL)));
}
else
{
throw new AdministratorAlreadyExistException("Administrator already exists");
}
}
public long Add(AirlineCompany t)
{
StringBuilder sb = new StringBuilder();
string SQL1 = $"SELECT COUNT(*) FROM AirlineCompanies WHERE USER_NAME = '{t.USER_NAME}' OR EXISTS (SELECT USER_NAME FROM Customers WHERE USER_NAME = '{t.USER_NAME}')";
string res = DL.ExecuteSqlScalarStatement(SQL1);
if (res == "0")
{
sb = new StringBuilder();
sb.Append($"INSERT INTO AirlineCompanies(AIRLINE_NAME, USER_NAME, PASSWORD, COUNTRY_CODE)");
sb.Append($" values('{ t.AIRLINE_NAME}', '{ t.USER_NAME}', '{ t.PASSWORD}', { t.COUNTRY_CODE})");
string SQL2 = sb.ToString();
DL.ExecuteSqlNonQuery(SQL2);
SQL2 = $"SELECT ID FROM AirlineCompanies WHERE USER_NAME = '{t.USER_NAME}'";
return(Int64.Parse(DL.ExecuteSqlScalarStatement(SQL2)));
}
else
{
throw new AirlineCompanyAlreadyExistException("AirlineCompany already exists");
}
}
public long Add(Flight t)
{
StringBuilder sb = new StringBuilder();
sb.Append($"SELECT COUNT(*) FROM Flights WHERE AIRLINECOMPANY_ID = {t.AIRLINECOMPANY_ID}");
sb.Append($" AND ORIGIN_COUNTRY_CODE = {t.ORIGIN_COUNTRY_CODE}");
sb.Append($" AND DESTINATION_COUNTRY_CODE = {t.DESTINATION_COUNTRY_CODE}");
sb.Append($" AND CONVERT(char(16),DEPARTURE_TIME,120) = '{t.DEPARTURE_TIME.ToString("yyyy-MM-dd HH:mm")}'");
sb.Append($" AND CONVERT(char(16),LANDING_TIME,120) = '{t.LANDING_TIME.ToString("yyyy-MM-dd HH:mm")}'");
sb.Append($" AND REMANING_TICKETS = {t.REMANING_TICKETS}");
sb.Append($" AND TOTAL_TICKETS = {t.TOTAL_TICKETS}");
string SQL1 = sb.ToString();
string res = DL.ExecuteSqlScalarStatement(SQL1);
if (res == "0")
{
sb = new StringBuilder();
sb.Append($"INSERT INTO Flights(AIRLINECOMPANY_ID, ORIGIN_COUNTRY_CODE, DESTINATION_COUNTRY_CODE, DEPARTURE_TIME, LANDING_TIME, REMANING_TICKETS, TOTAL_TICKETS)");
sb.Append($" values({ t.AIRLINECOMPANY_ID}, { t.ORIGIN_COUNTRY_CODE}, { t.DESTINATION_COUNTRY_CODE}, '{ t.DEPARTURE_TIME.ToString("yyyy-MM-dd HH:mm:ss")}', '{t.LANDING_TIME.ToString("yyyy-MM-dd HH:mm:ss")}', { t.REMANING_TICKETS}, {t.TOTAL_TICKETS})");
string SQL2 = sb.ToString();
DL.ExecuteSqlNonQuery(SQL2);
sb = new StringBuilder();
sb.Append($"SELECT ID FROM Flights WHERE AIRLINECOMPANY_ID = {t.AIRLINECOMPANY_ID}");
sb.Append($" AND ORIGIN_COUNTRY_CODE = {t.ORIGIN_COUNTRY_CODE}");
sb.Append($" AND DESTINATION_COUNTRY_CODE = {t.DESTINATION_COUNTRY_CODE}");
sb.Append($" AND CONVERT(char(16),DEPARTURE_TIME,120) = '{t.DEPARTURE_TIME.ToString("yyyy-MM-dd HH:mm")}'");
sb.Append($" AND CONVERT(char(16),LANDING_TIME,120) = '{t.LANDING_TIME.ToString("yyyy-MM-dd HH:mm")}'");
sb.Append($" AND REMANING_TICKETS = {t.REMANING_TICKETS}");
sb.Append($" AND TOTAL_TICKETS = {t.TOTAL_TICKETS}");
string SQL3 = sb.ToString();
return(Int64.Parse(DL.ExecuteSqlScalarStatement(SQL3)));
}
else
{
throw new FlightAlreadyExistException("Flight already exists");
}
}