def create_manifest(self):
db = Database_OOP()
db.connect_sql()
cursor = db.connect_sql()
manifest_input = input("Which flight number would you like to create a manifest for? ")
if manifest_input == "1321":
flightnumber = "1321"
elif manifest_input == "4321":
flightnumber = "4321"
elif manifest_input == "2221":
flightnumber = "2221"
query = f"""SELECT p.passengerID, p.first_name, p.last_name, p.passport_number, p.dob, p.upcoming_flight, p.flight_number, f.destination, f.timeslot, f.plane_model, p.airmiles, p.previous_flights
FROM passengers p
INNER JOIN flights f ON p.flight_number = f.flight_number
WHERE p.flight_number = '{flightnumber}'"""
rows = cursor.execute(query)
passengerid = []
first_name = [] # Initialise an empty list for each column
last_name = []
passport_number = []
date_of_birth = []
upcoming_flight = []
flight_number = []
destination = []
time_slot = []
plane_model = []
airmiles = []
previous_flight = []
for passengerID, firstname, lastname, passportno, dob, upcoming, flightnumber, dest, timeslot, planemodel, air_miles, previousflight in rows:
passengerid.append(passengerID)
first_name.append(firstname)
last_name.append(lastname)
passport_number.append(passportno)
date_of_birth.append(dob)
upcoming_flight.append(upcoming)
flight_number.append(flightnumber)
destination.append(dest)
time_slot.append(timeslot)
plane_model.append(planemodel)
airmiles.append(air_miles)
previous_flight.append(previousflight)
df_passengers = pd.DataFrame()
df_passengers['First Name'] = first_name
df_passengers['Last Name'] = last_name
df_passengers['Passport Number'] = passport_number
df_passengers['Date of Birth'] = date_of_birth
df_passengers['Upcoming Flight'] = upcoming_flight
df_passengers['Flight Number'] = flight_number
df_passengers['Destination'] = destination
df_passengers['Time slot'] = time_slot
df_passengers['Plane model'] = plane_model
df_passengers['Air Miles'] = airmiles
df_passengers['Previous Flight'] = previous_flight
return df_passengers
def existing_customers(self):
a = Database_OOP()
cursor = a.connect_sql()
query = '''
INSERT
INTO
passengers(first_name, last_name, passport_number, dob, upcoming_flight, flight_number, airmiles,
previous_flights)
VALUES
('Ibrahim', 'Bocus', '170994GLPR', '02/09/1996', 'True', '1321', '2500', 'london'),
('Daniel', 'Teegan', '150193FRHT', '17/08/1999', 'True', '4321', '0', 'london'),
('Mehdi', 'Shamaa', '020390AQWS', '22/02/2000', 'True', '1321', '3487', 'Dublin'),
('Tony', 'Hawk', '210799LQRV', '20/12/1991', 'True', '4321', '2087', 'Lisbon'),
('Jack', 'Bauer', '221290QPWR', '29/09/1987', 'True', '1321', '9023', 'N/A'),
('Jon', 'Jones', '150984LLPR', '02/09/1996', 'True', '4321', '2500', 'london'),
('Max', 'Holloway', '220198LPTT', '17/08/1999', 'True', '1321', '0', 'london'),
('Sammy', 'Loqbeti', '340991MPWS', '22/02/2003', 'False', 'N/A', '3487', 'Dublin'),
('Jorge', 'Masvidal', '020991LPWS', '22/02/2003', 'True', '1321', '3487', 'Dublin'),
('Tony', 'Ferguson', '110779MZAH', '20/12/1991', 'True', '4321', '2087', 'Lisbon'),
('Jamie', 'Footy', '340661MPWS', '22/02/2000', 'False', 'N/A', '3187', 'Dublin'),
('Dustin', 'Poirer', '991290MJUT', '29/09/1987', 'True', '2221', '9023', 'N/A'),
('Rush', 'Holloway', '990779MZAH', '20/12/1991', 'True', '4321', '2087', 'Lisbon'),
('Dustin', 'Smith', '011290MLUT', '29/09/1987', 'True', '2221', '9023', 'N/A'),
('Kamaru', 'Usman', '300561NQWS', '22/02/1988', 'False', 'N/A', '3187', 'Dublin')
'''
cursor.execute(query)
cursor.commit()
def plamemodel_input(self):
self.display = self.findChild(QtWidgets.QLabel, 'Qcreate_flight_prompts')
self.display.setText("information has been stored in database")
plane_model = self.input.text()
print(plane_model)
flight_info.append(plane_model)
print(flight_info)
db = Database_OOP()
db.connect_sql()
cursor = db.connect_sql()
query = f'''
INSERT INTO flights(flight_number, plane_model, destination, timeslot)
VALUES
('{flight_info[0]}', '{flight_info[3]}', '{flight_info[1]}', '{flight_info[2]}')
'''
cursor.execute(query)
cursor.commit()
return
def id_checker(self):
db = Database_OOP()
db.connect_sql()
cursor = db.connect_sql()
# flight_number = input("What flight number are you checking?") # THIS NEEDS TO BE UPDATED IN THE CREATE TABLE AND EXISTING CUSTOMERS INFO AND INSERT PASSENGER INFO
# query = f"SELECT * FROM passengers WHERE "
flightnumber = input("What flight number are you checking")
query = f"""SELECT p.passengerID, p.first_name, p.last_name, p.passport_number, p.dob, p.upcoming_flight, p.flight_number, f.destination, f.timeslot, f.plane_model, p.airmiles, p.previous_flights
FROM passengers p
INNER JOIN flights f ON p.flight_number = f.flight_number
WHERE p.flight_number = '{flightnumber}'"""
first_name = str(input("What is the first name of the passenger? "))
rows = cursor.execute(query)
fname = []
for pportid, firstname, lastname, passportno, dob, upcoming, flightnumber, air_miles, lastflight, fill1, fill2, fill3 in rows:
fname.append(firstname)
if first_name in fname:
print("Success! ID matches with customer")
else:
manifest = FlightManifest()
manifest.call_police()
def create_table(self):
db = Database_OOP() # fix naming convetions
db.connect_sql()
cursor = db.connect_sql()
table_name = "passengers"
sql_command = f"""
CREATE TABLE {table_name}
(
passengerID int IDENTITY (1,1) NOT NULL,
first_name VARCHAR(300),
last_name VARCHAR(300),
passport_number VARCHAR(300),
dob VARCHAR(300),
upcoming_flight VARCHAR(300),
flight_number VARCHAR(300),
airmiles VARCHAR(300),
previous_flights VARCHAR(300),
)
"""
cursor.execute(sql_command)
cursor.commit()
def select_passengers(self):
db = Database_OOP()
db.connect_sql()
db.connect_sql()
cursor2 = db.connect_sql()
query = "SELECT * FROM passengers"
rows = cursor2.execute(query) # execute query using connection/ cursor
passportid = []
first_name = [] # Initialise an empty list for each column
last_name = []
passport_number = []
date_of_birth = []
upcoming_flight = []
flight_number = []
airmiles = []
last_flight = []
for pportid, firstname, lastname, passportno, dob, upcoming, flightnumber, air_miles, lastflight in rows:
passportid.append(pportid) # a slightly different name goes here
first_name.append(firstname) # list goes first, slightly different name goes in brackets
last_name.append(lastname)
passport_number.append(passportno)
date_of_birth.append(dob)
upcoming_flight.append(upcoming)
flight_number.append(flightnumber)
airmiles.append(air_miles)
last_flight.append(lastflight)
df_passengers = pd.DataFrame()
df_passengers[
'First Name'] = first_name # name a column 'First Name' then make it equal to the list you initialised
df_passengers['Last Name'] = last_name
df_passengers['Passport Number'] = passport_number
df_passengers['Date of Birth'] = date_of_birth
df_passengers['Upcoming Flight'] = upcoming_flight
df_passengers['Flight Number'] = flight_number
df_passengers['Air Miles'] = airmiles
df_passengers['Previous Flight Destination'] = last_flight
# Naming the column 'Date' and filling it with the data from the list previously created
return df_passengers
def insert_passenger_info_app(self, first_name, last_name, passport_number, dob, upcoming_flight, flight_number,
airmiles, previous_flights):
db = Database_OOP()
cursor = db.connect_sql()
query1 = f"""
INSERT INTO passengers
(
first_name, last_name, passport_number, dob, upcoming_flight, flight_number, airmiles, previous_flights
)
VALUES
(
'{first_name}', '{last_name}', '{passport_number}', '{dob}', '{upcoming_flight}', '{flight_number}','{airmiles}', '{previous_flights}'
)
"""
cursor.execute(query1)
cursor.commit()
def insert_passenger_info(self):
db = Database_OOP()
cursor = db.connect_sql()
p = Passengers()
enter_info = True
while enter_info:
while True:
try:
# First name user input
first_name = input("Please enter the customers First Name: ")
if len(first_name) <= 2 or len(first_name) > 20:
raise ValueError
break
except ValueError:
print("ERROR: Please enter a valid first name")
while True:
try:
# Second name user input
last_name = input("Please enter the customers Last Name: ")
if len(last_name) <= 2 or len(last_name) > 20:
raise ValueError
break
except ValueError:
print("ERROR: Please enter a valid last name")
while True:
try:
# Passport number user input
passport_number = input("Please enter the customers 10-character Passport Number: ")
if len(passport_number) < 10 or len(passport_number) > 10:
raise ValueError
break
except ValueError:
print("ERROR: Please enter a valid passport number (10 characters) ")
while True:
try:
# Date of birth user input
dob = input("Please enter the customers Date of Birth in the following format (DD/MM/YYYY): ")
if len(str(dob)) < 10 or len(str(dob)) > 10:
raise ValueError
break
except ValueError:
print("ERROR: Please enter a valid date of birth. In the following format (DD/MM/YYYY)")
while True:
try:
# Upcoming flight user input
upcoming_flight = input("Does the customer have an upcoming flight? (TRUE or FALSE): ")
if (upcoming_flight != "TRUE" and "FALSE") and (
len(upcoming_flight) < 2 or len(upcoming_flight)) > 6:
raise ValueError
break
except ValueError:
print("ERROR: Please enter a valid choice TRUE or FALSE: ")
while True:
try:
# Upcoming flight number user input
flight_number = input("Please enter the customers Flight number: ")
if len(flight_number) < 2 or len(flight_number) > 30:
raise ValueError
break
except ValueError:
print("ERROR: Please enter a valid destination")
while True:
try:
# Airmiles user input
airmiles = (input(
"How many Air Miles has the customer accumulated: "))
if len(airmiles) > 10:
raise ValueError
break
except ValueError:
print("ERROR: please check and try again")
while True:
try:
# Last flight user input
previous_flights = input(
"Please enter the customers previous flight with MID Airlines. If its their first flight enter N/A: ")
if len(previous_flights) < 3 or len(previous_flights) > 30:
raise ValueError
break
except ValueError:
print("ERROR: Please enter a valid destination")
query1 = f"""
INSERT INTO passengers
(
first_name, last_name, passport_number, dob, upcoming_flight, flight_number, airmiles, previous_flights
)
VALUES
(
'{first_name}', '{last_name}', '{passport_number}', '{dob}', '{upcoming_flight}', '{flight_number}','{airmiles}', '{previous_flights}'
)
"""
cursor.execute(query1)
cursor.commit()
break_loop = input("Would you like to continue?: Y/N")
if break_loop == "N":
enter_info = False
else:
p.insert_passenger_info()