def get_cancellation_info(command):
'''
Info we need to provide:
1. cancelled time percentage
2. cancelled reasons
'''
# Analyze by origin and destination (e.g. origin = "GSP", dest = "LGA")
if command == 1:
origin = input(
"\nPlease specify the value of orgin in 3-letter abbreviation (i.e. GSP): "
)
destination = input(
"\nPlease specify the value of destination in 3-letter abbreviation (i.e. LGA): "
)
query_general = "With TotalFlights AS (SELECT count(*) as number_of_Total_Flights FROM Flight WHERE Origin = '{}' and Dest = '{}'), CancelledFlights AS (SELECT count(*) as number_of_Cancelled_Flights FROM Flight WHERE Origin = '{}' and Dest = '{}' and Cancelled = '1') SELECT a.number_of_Total_Flights, b.number_of_Cancelled_Flights, b.number_of_Cancelled_Flights/a.number_of_Total_Flights as Cancellation_Ratio FROM TotalFlights as a JOIN CancelledFlights as b;".format(
origin, destination, origin, destination)
detailed_query = "With CancelledFlight As (SELECT * FROM Flight WHERE Origin = '{}' and Dest = '{}' and Cancelled = '1') SELECT Cancellation_Code, COUNT(*) as occurence FROM Cause_of_Cancellation as a INNER JOIN CancelledFlight b ON a.Airline_Name = b.Airline_Name and a.Tail_Number = b.Tail_Number and a.Flight_Number = b.Flight_Number and a.Flight_Date = b.Flight_Date and a.Origin = b.Origin GROUP BY a.Cancellation_Code;".format(
origin, destination)
# Analyze by airline number (e.g. airline_name = "9E")
elif command == 2:
airline_name = input(
"\nPlease specify the value of Airline_Name (i.e. B6): ")
query_general = "With TotalFlights AS (SELECT count(*) as number_of_Total_Flights FROM Flight WHERE Airline_Name = '{}'), CancelledFlights AS (SELECT count(*) as number_of_Cancelled_Flights FROM Flight WHERE Airline_Name = '{}' and Cancelled = '1') SELECT a.number_of_Total_Flights, b.number_of_Cancelled_Flights, b.number_of_Cancelled_Flights/a.number_of_Total_Flights as Cancellation_Ratio FROM TotalFlights as a JOIN CancelledFlights as b;".format(
airline_name, airline_name)
detailed_query = "With CancelledFlight As (SELECT * FROM Flight WHERE Airline_Name = '{}' and Cancelled = '1') SELECT Cancellation_Code, COUNT(*) as occurence FROM Cause_of_Cancellation as a INNER JOIN CancelledFlight b ON a.Airline_Name = b.Airline_Name and a.Tail_Number = b.Tail_Number and a.Flight_Number = b.Flight_Number and a.Flight_Date = b.Flight_Date and a.Origin = b.Origin GROUP BY a.Cancellation_Code;".format(
airline_name)
print(
"\n================= General Cancellation Information ================== \n"
)
general_des, general_res = db_execute(query_general)
if len(general_res) == 0:
print(
"\nSorry, we cannot find any relevant flight information based on the data you provided!:("
)
else:
nice_sql_format(general_res, general_des)
print(
"\n================= Cancellation Code Distribution ================== \n"
)
detailed_des, detailed_res = db_execute(detailed_query)
if len(detailed_res) == 0:
print(
"\nSorry, we cannot find any relevant flight information based on the data you provided!:("
)
else:
nice_sql_format(detailed_res, detailed_des)
print("\n Cancellation Code Note: ")
print("\n A: Carrier")
print("\n B: Weather")
print("\n C: National Air System")
print("\n D: Security")
def option_2():
print("For Route checking, Please provide the following information: ")
origin = get_origin()
destination = get_destination()
query = service_option2_default_query.format(origin, destination)
res, result = db_execute(query)
if len(result) == 0:
print(
"\nSorry, we cannot find any relevant Route information based on the data you provided!:("
)
yes_or_no = continue_or_exit()
if yes_or_no.lower() in no_options:
return usr_logic()
else:
return option_2()
else:
nice_sql_format(result, res)
user_selection_for_continue = input(
"\nDo you want to continue for more information or exit? (Y/N) ")
while user_selection_for_continue.lower(
) not in yes_options and user_selection_for_continue.lower(
) not in no_options:
user_selection_for_continue = input(
"\n\nDo you want to continue for more information or exit? (Y/N) "
)
if user_selection_for_continue.lower() in no_options:
return usr_logic()
else:
num_for_details = input(
"\nWhich route do you want to view for more detailed? ")
num_for_details = check_for_numbers(num_for_details)
while num_for_details == False or num_for_details < 1 or num_for_details > len(
result):
num_for_details = input(
"\nInvalid! Please enter a valid option for view more details: "
)
num_for_details = check_for_numbers(num_for_details)
parameter = result[num_for_details - 1]
query = service_option2_details_query.format(
parameter["Airline_Name"], parameter["Flight_Number"], origin,
destination)
des, detail_result = db_execute(query)
nice_sql_format(detail_result, des)
yes_or_no = continue_or_exit()
if yes_or_no.lower() in yes_options:
option_2()
return
def option_1():
print("For Flight checking, Please provide the following information: ")
airline_name = input("\nPlease specify the airline name (i.e. 9E): ")
while airline_name_look_up(airline_name) == -1:
airline_name = input(
"\nInvalid! Please re-enter a valid airline name (i.e. 9E): ")
flight_number = input("\nPlease specify the flight number (i.e. 4628): ")
while len(flight_number) > 4:
flight_number = input(
"\nThe length of flight number should be less than 5! Please re-enter a valid flight number (i.e. 4628): "
)
origin = get_origin()
destination = get_destination()
flight_date = get_flight_date()
query = service_option1_default_query.format(airline_name, flight_number,
flight_date, origin,
destination)
des, result = db_execute(query)
if len(result) == 0:
print(
"\nSorry, we cannot find any relevant flight information based on the data you provided!:("
)
else:
nice_sql_format(result, des)
yes_or_no = continue_or_exit()
if yes_or_no.lower() in yes_options:
option_1()
return
def get_delay_info(command):
'''
Info we need to provide:
deplay ratio
average deplayed minutes per flight
Delayed minutes due to different causes
'''
# Analyze by origin and destination (e.g. origin = "GSP", dest = "LGA")
if command == 1:
origin = input(
"\nPlease specify the value of orgin in 3-letter abbreviation (i.e. GSP): "
)
destination = input(
"\nPlease specify the value of destination in 3-letter abbreviation (i.e. LGA): "
)
query_general = "With TotalFlights AS (SELECT count(*) as number_of_Total_Flights FROM Flight WHERE Origin = '{}' and Dest = '{}'), DelayedFlights AS (SELECT count(*) as number_of_Delayed_Flights, SUM(ArrDelayMinutes) as Total_Delayed_Minutes FROM Flight WHERE Origin = '{}' and Dest = '{}' and isDelayed = '1') SELECT a.number_of_Total_Flights, b.number_of_Delayed_Flights, b.number_of_Delayed_Flights/a.number_of_Total_Flights as Delay_Ratio, b.Total_Delayed_Minutes, b.Total_Delayed_Minutes/a.number_of_Total_Flights as Avg_Delayed_Minutes_per_Flight FROM TotalFlights as a JOIN DelayedFlights as b;".format(
origin, destination, origin, destination)
detailed_query = "With DelayedFlight As (SELECT * FROM Flight WHERE Origin = '{}' and Dest = '{}' and isDelayed = '1') SELECT SUM(Carrier_Delay) AS Carrier_Delay, SUM(Weather_Delay) AS Weather_Delay, SUM(NAS_Delay) AS NAS_Delay, SUM(Security_Delay) AS Security_Delay, SUM(LateAircraft_Delay) AS LateAircraft_Delay FROM Cause_of_Delay as a INNER JOIN DelayedFlight b ON a.Airline_Name = b.Airline_Name and a.Tail_Number = b.Tail_Number and a.Flight_Number = b.Flight_Number and a.Flight_Date = b.Flight_Date and a.Origin = b.Origin;".format(
origin, destination)
# Analyze by airline number (e.g. airline_name = "9E")
elif command == 2:
airline_name = input(
"\nPlease specify the value of Airline_Name (i.e. B6): ")
query_general = "With TotalFlights AS (SELECT count(*) as number_of_Total_Flights FROM Flight WHERE Airline_Name = '{}'), DelayedFlights AS (SELECT count(*) as number_of_Delayed_Flights, SUM(ArrDelayMinutes) as Total_Delayed_Minutes FROM Flight WHERE Airline_Name = '{}' and isDelayed = '1') SELECT a.number_of_Total_Flights, b.number_of_Delayed_Flights, b.number_of_Delayed_Flights/a.number_of_Total_Flights as Delay_Ratio, b.Total_Delayed_Minutes, b.Total_Delayed_Minutes/a.number_of_Total_Flights as Avg_Delayed_Minutes_per_Flight FROM TotalFlights as a JOIN DelayedFlights as b;".format(
airline_name, airline_name)
detailed_query = "With DelayedFlight As (SELECT * FROM Flight WHERE Airline_Name = '{}' and isDelayed = '1') SELECT SUM(Carrier_Delay) AS Carrier_Delay, SUM(Weather_Delay) AS Weather_Delay, SUM(NAS_Delay) AS NAS_Delay, SUM(Security_Delay) AS Security_Delay, SUM(LateAircraft_Delay) AS LateAircraft_Delay FROM Cause_of_Delay as a INNER JOIN DelayedFlight b ON a.Airline_Name = b.Airline_Name and a.Tail_Number = b.Tail_Number and a.Flight_Number = b.Flight_Number and a.Flight_Date = b.Flight_Date and a.Origin = b.Origin;".format(
airline_name)
print(
"\n================= General Delay Information ================== \n")
general_des, general_res = db_execute(query_general)
if len(general_res) == 0:
print(
"\nSorry, we cannot find any relevant flight information based on the data you provided!:("
)
else:
nice_sql_format(general_res, general_des)
print("\n================= Detailed Delay Information ==================")
print(
"The following table shows the total number of minutes for each delay reason\n"
)
detailed_des, detailed_res = db_execute(detailed_query)
if len(detailed_res) == 0:
print(
"\nSorry, we cannot find any relevant flight information based on the data you provided!:("
)
else:
nice_sql_format(detailed_res, detailed_des)
yes_or_no = continue_or_exit()
# if yes_or_no.lower() in no_options:
# return usr_logic()
# else:
# return delay_analysis_logic()
if yes_or_no.lower() in yes_options:
return delay_analysis_logic()
def admin_logic():
command = get_command()
if command == 5:
return
table = get_table()
display_column(table)
query = ""
if command == 1:
if table == 1:
query = get_flight_insert_query()
elif table == 2:
query = get_airport_insert_query()
elif table == 3:
query = get_delay_insert_query()
elif table == 4:
query = get_cancellation_insert_query()
elif command == 2:
if table == 1:
query = get_flight_delete_query()
elif table == 2:
query = get_airport_delete_query()
elif table == 3:
query = get_delay_delete_query()
elif table == 4:
query = get_cancellation_delete_query()
elif command == 3:
if table == 1:
query = get_flight_update_query()
elif table == 2:
query = get_airport_update_query()
elif table == 3:
query = get_delay_update_query()
elif table == 4:
query = get_cancellation_update_query()
elif command == 4:
if table == 1:
query = get_flight_select_query()
elif table == 2:
query = get_airport_select_query()
elif table == 3:
query = get_delay_select_query()
elif table == 4:
query = get_cancellation_select_query()
print("\nThis is the query you are specifying: {}".format(query))
while 1:
double_check = input("\nDo you really want to execute? (Y/N) ")
if double_check in yes_options:
description, result = db_execute(query)
if command != 4:
print("Modified Successfully :)")
else:
if len(result) == 0:
print("\nSorry, we cannot find any relevant flight information based on the data you provided!:(")
else:
nice_sql_format(result, description)
break
elif double_check in no_options:
print("The modification is withdrawed.")
break
print("\n\n================= Back to Admin Menu ==================")
admin_logic()
def option_3():
print("For Airport checking, Please provide the following information: ")
option3_menu()
user_option = input(
"\nWhich option would you like to choose, please enter the corresponding digit: "
)
user_option = check_for_numbers(user_option)
while user_option == False or user_option < 1 or user_option > 2:
user_option = input("Please re-enter a valid number: ")
user_option = check_for_numbers(user_option)
airport = input(
"\nPlease specify the name of the airport that you interested in 3-letter abbreviation (i.e. GSP): "
)
while airport_look_up(airport) == -1:
airport = input(
"\nInvalid! Please specify a valid airport in 3-letter abbreviation (i.e. GSP): "
)
airport = airport.upper()
general_info = ''
query2 = ''
query3 = ''
if user_option == 1:
flight_date = get_flight_date()
general_info = "\nThese are the statistics relates to the Airport {} and the Flight_date {}: ".format(
airport, flight_date)
query2 = service_option3_depature.format(airport, flight_date)
query3 = service_option3_arrival.format(airport, flight_date)
else:
option3_month()
option = input(
"\nWhich option would you like to view, please enter the corresponding digit: "
)
option = check_for_numbers(option)
while option == False or option < 1 or option > 13:
option = input("Please re-enter a valid number: ")
option = check_for_numbers(option)
year = months_available[option - 1][0]
month = months_available[option - 1][1]
general_info = "\nThese are the statistics relates to the Airport {} at Year {} and Month {}: ".format(
airport, year, month)
query2 = service_option3_depature_in_month.format(
airport, int(year), int(month))
query3 = service_option3_arrival_in_month.format(
airport, int(year), int(month))
query1 = service_option3_basic_info.format(airport)
des1, result1 = db_execute(query1)
if len(result1) == 0:
fail_to_find_info()
else:
print("\n====== The Airport {} is located at {}. ======".format(
airport, result1[0]["City"]))
des2, result2 = db_execute(query2)
des3, result3 = db_execute(query3)
if len(result2) < 2 or len(result3) < 0:
fail_to_find_info()
else:
num_of_scheduled = result2[0]["total"]
num_of_cancelled_dep = result2[1]["total"]
num_of_delayed_dep = result2[2]["total"]
num_of_ontime_dep = num_of_scheduled - num_of_cancelled_dep - num_of_delayed_dep
num_of_arrive = result3[0]["total"]
num_of_cancelled_arr = result3[1]["total"]
num_of_delayed_arr = result3[2]["total"]
num_of_ontime_arr = num_of_arrive - num_of_cancelled_arr - num_of_delayed_arr
result1 = ""
result3 = ""
if user_option == 1:
result1 = "\n1. At the date {}, there are {} flights scheduled to depature from {} airport.".format(
flight_date, num_of_scheduled, airport)
result3 = "\n3. At the date {}, there are {} flights scheduled to arrive at {} airport.".format(
flight_date, num_of_arrive, airport)
else:
result1 = "\n1. At the Year {} Month {}, there are {} flights scheduled to depature from {} airport.".format(
year, month, num_of_scheduled, airport)
result3 = "\n3. At the Year {} Month {}, there are {} flights scheduled to arrive at {} airport.".format(
year, month, num_of_arrive, airport)
print(result1)
print(
"\n2. In these {} flights, there are {} flights are depatured on time, {} flights are delayed, and {} flights are cancelled."
.format(num_of_scheduled, num_of_ontime_dep,
num_of_delayed_dep, num_of_cancelled_dep))
print(result3)
print(
"\n4. In these {} flights, there are {} flights are arrived on time, {} flights are delayed, and {} flights are cancelled."
.format(num_of_scheduled, num_of_ontime_arr,
num_of_delayed_arr, num_of_cancelled_arr))
print(
"\n======================= END of Report ====================="
)
yes_or_no = continue_or_exit()
if yes_or_no.lower() in yes_options:
option_3()
return