-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathadd.py
1452 lines (1140 loc) · 45.1 KB
/
add.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
import datetime
import subprocess as sp
import pymysql
import pymysql.cursors
import sys
colors_dict_1 = {
"BLUE": "\033[1;34m",
"RED": "\033[1;31m",
"CYAN": "\033[1;36m",
"GREEN": "\033[0;32m",
"RESET": "\033[0;0m",
"BOLD": "\033[;1m",
"REVERSE": "\033[;7m",
"ERROR":"\033[;7m"+"\033[1;31m"
}
def offload_commit(con):
#con.commit()
return
def debug_print(msg):
decorate_output("REVERSE")
#print(msg)
decorate_output("RESET")
def error_print(msg):
decorate_output("RED")
print(msg)
decorate_output("RESET")
def add_yes_print():
decorate_output("GREEN")
print("Insertion successful")
decorate_output("RESET")
def success_print(msg):
decorate_output("GREEN")
print(msg)
decorate_output("RESET")
def decorate_output(color_str):
#print("Decorated")
sys.stdout.write(colors_dict_1[color_str])
def part():
print("-----------------------------------------------------------------")
return
def part2():
print("=====================================================================")
return
def part3():
print("@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@")
return
def date_less_cur(date_str):
today_date = datetime.date.today()
today_date_str = today_date.strftime("%Y-%m-%d")
if date_str < today_date_str:
return 1
else:
return 0
def date_more_cur(date_str):
today_date = datetime.date.today()
today_date_str = today_date.strftime("%Y-%m-%d")
if date_str > today_date_str:
return 1
else:
return 0
def numeric_check(ch):
if ch>='0' and ch<='9':
return 1
else:
return 0
def dep_ahead_arv(arrival_str, depart_str):
if len(arrival_str)!=5 or len(depart_str)!=5:
error_print('Invalid format')
return -1
if numeric_check(arrival_str[0])+numeric_check(arrival_str[1])+numeric_check(arrival_str[3])+numeric_check(arrival_str[4])!=4:
error_print("Error: Enter only digits for mm and hh in arrival time")
return -1
if numeric_check(depart_str[0])+numeric_check(depart_str[1])+numeric_check(depart_str[3])+numeric_check(depart_str[4])!=4:
error_print("Error: Enter only digits for mm and hh in departure time")
return -1
arrival_hrs = int(arrival_str[0:2])
arrival_min = int(arrival_str[3:5])
depart_hrs = int(depart_str[0:2])
depart_min = int(depart_str[3:5])
if depart_hrs > arrival_hrs:
error_print('Arrival time can not be ahead of departure time')
return -1
elif depart_hrs == arrival_hrs and depart_min > arrival_min:
error_print('Arrival time can not be ahead of departure time')
return -1
else:
return 0
def is_valid_time_zone(time_str):
# +05:30
if len(time_str)!=6:
error_print("Invalid format")
return -1
if numeric_check(time_str[1])+numeric_check(time_str[2])+numeric_check(time_str[4])+numeric_check(time_str[5])!=4:
error_print("Enter only digits for mm and hh")
return -1
hrs=int(time_str[1:3])
mins=int(time_str[4:6])
if (time_str[0]!='+' and time_str[0]!='-') or time_str[3]!=':':
error_print("Invalid format")
return -1
if hrs>12:
error_print("hrs not valid")
return -1
if mins>60:
error_print("mins not valid")
return -1
return 0
def print_err_date(state):
if state == 1:
print('The date entered cannot be after current date. Current date: ' + str(datetime.date.today()))
elif state == -1:
print('The date entered cannot be before current date. Current date: ' + str(datetime.date.today()))
def get_query_atoms(attr):
keys_str = ""
values_str = ""
dict_len = len(attr.keys())
i = -1
for key, value in attr.items():
i += 1
if value == '' or value == 'NULL':
continue
keys_str += "`"+key+"`"
values_str += '"'+value+'" '
if i != dict_len-1:
keys_str += ", "
values_str += ", "
if keys_str[-2]==',':
keys_str=keys_str[:-2]
if values_str[-2]==',':
values_str=values_str[:-2]
return (keys_str, values_str)
############################################################################################
# Done-------------
def add_airline(cur, con):
#print("inside add_airline function")
table_name = "`Airline`"
attr = {}
print('Enter details of the new airline:')
attr['IATA airline designators'] = input('Enter 2-character IATA airline designator code * :')
attr['Company Name'] = input('Enter airline name *: ')
attr['num_aircrafts_owned'] = input('Enter number of aircrafts currently owned by the airline: ')
tmp = input('Enter 1 if airline is active, 0 otherwise: ')
if tmp == 1:
attr['is_active'] = True
elif tmp == 0:
attr['is_active'] = False
attr['country_of_ownership'] = input('Enter country of ownership of airline: ')
keys_str, values_str = get_query_atoms(attr)
# print('Table Name:' + keys_str)
#print(values_str)
query_str = 'INSERT INTO '+table_name + \
" ( "+keys_str+" ) VALUES"+" ( "+values_str+" );"
try:
cur.execute(query_str)
add_yes_print()
con.commit()
except Exception as e:
print('Failed to insert into the database.')
con.rollback()
print(e)
input('Press any key to continue.')
return
################################################################################################
# done ---------- CHECKED
def add_aircraft(cur, con):
debug_print("inside add_aircraft function")
table_name = "`Aircraft`"
attr = {}
print('Enter details of the new aircraft: ')
attr['registration_num'] = input("Enter registration number of aircraft: ")
attr['fk_to_capacity_Manufacturer'] = input("Enter manufacturer of aircraft: ")
attr['fk_to_capacity_Model'] = input("Enter model of aircraft: ")
attr['Distance Travelled'] = input("Enter distance travelled: ")
attr['Flight ID'] = input("Enter Flight ID: ")
attr['Maintanence check date'] = input("Enter maintanence check date: [YYYY-MM-DD]: ")
if date_more_cur(attr['Maintanence check date']) == 1:
print_err_date(1)
con.rollback()
return
attr['fk_to_airline_owner_airline_IATA_code'] = input("Enter IATA code of owner airline: ")
keys_str, values_str = get_query_atoms(attr)
#print(keys_str)
#print(values_str)
query_str = 'INSERT INTO '+table_name + \
" ( "+keys_str+" ) VALUES"+" ( "+values_str+" );"
try:
cur.execute(query_str)
add_yes_print()
#con.commit()
except Exception as e:
error_print('Failed to insert into the database.')
con.rollback()
print(e)
input('Press any key to continue.')
return
query_str2 = 'UPDATE Airline, Aircraft SET num_aircrafts_owned = num_aircrafts_owned - 1 WHERE fk_to_airline_owner_airline_IATA_code = `IATA airline designators`'
try:
cur.execute(query_str2)
con.commit()
except Exception as e:
error_print('Failed to increment number of aricrafts owned by airline the database.')
con.rollback()
print(e)
input('Press any key to continue.')
return
#####################################################################################
# done -> CHECKED
def add_emer_contact(cur, con, aadhar_relative):
#print("inside emer_contact_func")
table_name = "`emer_contact`"
attr = {}
print('Enter details of the emergency contact entry:')
attr['fk_to_passenger_Aadhar_card_number'] = aadhar_relative
attr["name"] = input('Name*: ')
attr['Phone No'] = input('Phone No: ')
keys_str, values_str = get_query_atoms(attr)
print(keys_str)
print(values_str)
query_str = 'INSERT INTO '+table_name + \
" ( "+keys_str+" ) VALUES"+" ( "+values_str+" );"
#print("query str is %s->", query_str)
try:
cur.execute(query_str)
offload_commit(con)
success_print("Emergency contact added")
return 0
except Exception as e:
error_print('Failed to insert into the database.')
con.rollback()
print(e)
input('Press any key to continue.')
return -1
# done #checked
def add_passenger(cur, con):
#print("inside add_passenger function")
table_name = "`Passenger`"
attr = {}
print('Enter details of the new passenger: ')
attr["Aadhar_card_number"] = input("Enter aadhar card number 12 digit number: ")
tmp_name = input("Enter name: ")
name_list = tmp_name.split(' ')
if len(name_list) >= 3:
attr['First Name'] = name_list[0]
attr['Middle Name'] = ' '.join(name_list[1:-1])
attr['Last Name'] = name_list[-1]
elif len(name_list) == 2:
attr['First Name'] = name_list[0]
attr['Middle Name'] = ''
attr['Last Name'] = name_list[1]
elif len(name_list) == 1:
attr['First Name'] = name_list[0]
attr['Middle Name'] = ''
attr['Last Name'] = ''
else:
print('Error: Incorrect format of name entered')
input('Press any key to continue.')
return
attr["DOB"] = input("Enter Date of birth in format YYYY-MM-DD: ")
if date_more_cur(attr['DOB']) == 1:
print_err_date(1)
con.rollback()
return
year = int(attr["DOB"][0:4])
if 2020-year>60:
attr["Senior Citizen"] ="1"
else:
attr["Senior Citizen"] = "0"
attr["Gender"] = int(input("Enter \n1 for Male \n2 for Female \n3 for Others\n"))
if attr['Gender'] == 1:
attr['Gender'] = 'Male'
elif attr['Gender'] == 2:
attr['Gender'] = 'Female'
elif attr['Gender'] == 3:
attr['Gender'] = 'Others'
else:
print("Invalid number inserted. Please try again")
con.rollback()
return
attr["House Number"] = input("Enter house number of residence: ")
attr["Building"] = input("Enter building number of residence: ")
attr["City"] = input("Enter city of residence: ")
attr["Email-ID"] = input("Enter email-ID: ")
# attr["Senior Citizen"] = input(
# "Enter 0. non senior citizen\n 1. senior citizen\n")
attr["Nationality"] = input("Enter nationality of passenger eg.Indian: ")
keys_str, values_str = get_query_atoms(attr)
#print(keys_str)
#print(values_str)
query_str = 'INSERT INTO '+table_name + \
" ( "+keys_str+" ) VALUES"+" ( "+values_str+" );"
try:
cur.execute(query_str)
offload_commit(con)
num_emer_contacts = int(input("Enter number of emergency contacts you want to add between 0 and 3: "))
if num_emer_contacts > 3:
error_print("ERROR: Only upto 3 contacts allowed")
return
else:
for i in range(num_emer_contacts):
if add_emer_contact(cur, con, attr["Aadhar_card_number"]) == -1:
return
con.commit()
add_yes_print()
except Exception as e:
error_print('Failed to insert into the database.')
con.rollback()
error_print(e)
input('Press any key to continue.')
return
##############################################################################################
# Done checked
def add_airport(cur, con):
#print("inside add_airport function")
table_name = "`Airport`"
attr = {}
print('Enter details of the new airport: ')
attr["IATA airport codes"] = input(" Enter 3 character IATA code *: ")
attr["Altitude"] = input(" Enter in metres the Altitude: ")
######################################################
attr["Time Zone"] = input(" Enter in +hh:mm or -hh:mm format , note mm has to be between 0 and 60 and divisible by 15, hh between 0 and 12: ")
if is_valid_time_zone(attr["Time Zone"])==-1:
error_print("error in time zone entered by user")
return
########################################################
attr["Airport Name"] = input("Enter name of airport: ")
attr["City"] = input("Enter city where airport is situated: ")
attr["Country"] = input("Enter country where airport is situated: ")
attr["Latitude"] = input("Enter latitude: ")
if float(attr['Latitude']) > 90 or float(attr['Latitude']) < -90:
error_print("ERROR : Invalid latitudes")
return
attr["Longitude"] = input("Enter longitude: ")
if float(attr['Longitude']) > 180 or float(attr['Longitude']) < -180:
error_print("ERROR : Invalid longitudes")
return
keys_str, values_str = get_query_atoms(attr)
#print(keys_str)
# print(values_str)
query_str = 'INSERT INTO '+table_name + \
" ( "+keys_str+" ) VALUES"+" ( "+values_str+" );"
try:
cur.execute(query_str)
add_yes_print()
con.commit()
except Exception as e:
error_print('Failed to insert into the database.')
con.rollback()
error_print(e)
input('Press any key to continue.')
return
##########################################################################################
# Done
def add_runway(cur, con):
debug_print("inside add_runway function")
table_name = "`Runway`"
attr = {}
print('Enter details of the new runway: ')
attr["fk_to_airport_IATA_airport_codes"] = input(
"Enter IATA airport code of corresponding airport: ")
attr["Runway ID"] = input("Enter runway ID: ")
attr["length_ft"] = input("Enter length in feet: ")
attr["width_ft"] = input("Enter width in feet: ")
################DISPLAY A MENU HERE########################################
stat_choice = int(input("Enter status 1. Assigned\n 2. available\n 3. Disfunctional\n"))
attr["Status"] = ""
if stat_choice == 1:
attr["Status"] = 'Assigned'
elif stat_choice == 2:
attr["Status"] = 'Available'
elif stat_choice == 3:
attr["Status"] = 'Disfunctional'
######################################################
keys_str, values_str = get_query_atoms(attr)
# print(keys_str)
#print(values_str)
query_str = 'INSERT INTO '+table_name + \
" ( "+keys_str+" ) VALUES"+" ( "+values_str+" );"
try:
cur.execute(query_str)
add_yes_print()
con.commit()
except Exception as e:
error_print('Failed to insert into the database.')
con.rollback()
print(e)
input('Press any key to continue.')
return
# Done
def add_terminal(cur, con):
#print("inside add_terminal function")
table_name = "`Terminal`"
attr = {}
print('Enter details of the new terminal: ')
attr["fk_to_airport_IATA_airport_codes"] = input("Enter IATA code of corresponding airport: ")
attr["Terminal ID"] = input("Enter Terminal ID: ")
attr["Airplane Handling capacity"] = input("Enter airplane handling capacity: ")
attr["Floor Area"] = input("Enter floor area: ")
keys_str, values_str = get_query_atoms(attr)
#print(keys_str)
#print(values_str)
query_str = 'INSERT INTO '+table_name + \
" ( "+keys_str+" ) VALUES"+" ( "+values_str+" );"
try:
cur.execute(query_str)
add_yes_print()
con.commit()
except Exception as e:
error_print('Failed to insert Terminal into the database.')
con.rollback()
print(e)
input('Press any key to continue.')
return
###################################################################################################
# Done
def add_stopover_airports(cur,con,route_id):
#print("inside stopover_airports function")
table_name = "`stopover_airports_on_route`"
attr = {}
print('Enter details of the new stopover airport: ')
attr['fk_route_id'] = route_id
attr['fk_iata_stopover_airport'] = input("Enter iata code of stopover airport: ")
keys_str, values_str = get_query_atoms(attr)
#print(keys_str)
#print(values_str)
query_str = 'INSERT INTO '+table_name + \
" ( "+keys_str+" ) VALUES"+" ( "+values_str+" );"
#print("in stopover query str is %s->", query_str)
try:
cur.execute(query_str)
offload_commit(con)
print("Stopover executed")
#add_yes_print()
return 0
except Exception as e:
error_print('Failed to insert Stopover airport into the database.')
con.rollback()
print(e)
input('Press any key to continue.')
return -1
def add_flight_crew_serves_on_route(cur,con,route_id,aadhar_num):
debug_print("inside flight_crew_serves_on_route")
table_name = "`flight_crew_serves_on_route`"
attr = {}
#print('Enter details of the new runway: ')
attr["fk_to_route_Route ID"] = route_id
attr["fk_to_flight_crew_Aadhar_card_number"]=aadhar_num
keys_str, values_str = get_query_atoms(attr)
# print(keys_str)
#print(values_str)
query_str = 'INSERT INTO '+table_name + \
" ( "+keys_str+" ) VALUES"+" ( "+values_str+" );"
#print("query str is %s->", query_str)
try:
cur.execute(query_str)
offload_commit(con)
#add_yes_print()
return 0
except Exception as e:
error_print('Failed to insert crew member into the database.')
con.rollback()
print(e)
input('Press any key to continue.')
return -1
def add_crew_has_worked_together(cur,con,a1,a2,a3,a4):
debug_print("INSIDE crew_has_worked_together")
table_name = "`crew_has_worked_together`"
attr = {}
#print('Enter details of the new runway: ')
attr["Pilot captain Aadhar_card_number"] = a1
attr["Pilot first officer Aadhar_card_number"] = a2
attr["flight_attendant Aadhar_card_number"] = a3
attr["flight_engineer Aadhar_card_number"] = a4
query_init=f'''SELECT DISTINCT `Language_name`
FROM `Languages spoken by airline employee`
WHERE (`fk_Aadhar_card_number`={a1})
OR(`fk_Aadhar_card_number`={a2})
OR(`fk_Aadhar_card_number`={a3})
OR(`fk_Aadhar_card_number`={a4})
'''
cur.execute(query_init)
result = cur.fetchall()
#print("Result len is {result}")
attr["Number of Languages spoken overall"]=str(len(result))
keys_str, values_str = get_query_atoms(attr)
# print(keys_str)
#print(values_str)
query_str = 'INSERT INTO '+table_name + \
" ( "+keys_str+" ) VALUES"+" ( "+values_str+" );"
#print("query str is %s->", query_str)
try:
cur.execute(query_str)
offload_commit(con)
#add_yes_print()
return 0
except Exception as e:
error_print('Failed to insert TEAM INTO DATABASES.')
con.rollback()
print(e)
input('Press any key to continue.')
return -1
def add_route(cur, con):
# print("inside add_Route function")
table_name = "`Route`"
attr = {}
print('Enter details of the new route: ')
attr['Route ID'] = input('Route ID: ')
attr['fk_to_airport_src_iata_code'] = input('source airport iata code: ')
attr['fk_to_airport_dest_iata_code'] = input('Destination airport iata code: ')
if attr['fk_to_airport_src_iata_code'] == attr['fk_to_airport_dest_iata_code']:
error_print('source airport iata code can not be same as destination iata code')
return
#################################################
attr['Date'] = input('Date: [YYYY-MM-DD] (Press enter for today\'s date): ')
if attr['Date'] == '':
attr['Date'] = datetime.date.today().strftime('%Y-%m-%d')
###################################################
if date_less_cur(attr['Date']) == 1:
print_err_date(-1)
con.rollback()
return
###########################################
#job 2
attr['Scheduled arrival'] = input('Scheduled arrival (Press enter if information not available): [HH:MM]: ')
attr['Scheduled Departure'] = input('Scheduled Departure: [HH:MM] (Press enter if information not available): ')
if attr['Scheduled arrival']!='' and attr['Scheduled Departure']!='':
if dep_ahead_arv(attr['Scheduled arrival'], attr['Scheduled Departure']) == -1:
return
############################################################################
error_print("Press enter if information for an attribute is not available")
attr['Time duration'] = input('Time duration [HH:MM]: ')
attr['fk_to_runway_Take off runway id'] = input('Take off runway id: ')
attr['Distance Travelled'] = input('Distance Travelled: ')
attr['fk_to_runway_Landing runway ID'] = input('Landing runway ID: ')
attr['fk_to_aircraft_registration_num'] = input('aircraft registration number: ')
attr['Status'] = input('Current_Status: [Departed, Boarding, On_route, Delayed, Arrived, Check-in, Not_applicable]: ')
#print(keys_str)
#print(values_str)
#print("query str is %s->", query_str)
attr['Pilot captain Aadhar_card_number'] = input('Enter Pilot captain\'s Aadhar card number: ')
attr['Chief_flight_attendant Aadhar_card_number'] = input('Enter Chief flight attendant\'s Aadhar card number: ')
keys_str, values_str = get_query_atoms(attr)
query_str = 'INSERT INTO '+table_name + \
" ( "+keys_str+" ) VALUES"+" ( "+values_str+" );"
try:
cur.execute(query_str)
offload_commit(con)
add_yes_print()
######################################################################
num_stopover = int(input('Enter number of stopover airports encountered in the route: '))
for i in range(num_stopover):
if add_stopover_airports(cur,con,attr['Route ID'])==-1:
return
####################################################################
# ADD CREW
# Take Captain input
if add_flight_crew_serves_on_route(cur,con,attr['Route ID'],attr['Pilot captain Aadhar_card_number'] )==-1:
return
# Take chief flight attendant input
if add_flight_crew_serves_on_route(cur,con,attr['Route ID'],attr['Chief_flight_attendant Aadhar_card_number'])==-1:
return
# Take first officer input
aadhar_first_officer = input('Enter First Officer\'s Aadhar card number: ')
if add_flight_crew_serves_on_route(cur,con,attr['Route ID'],aadhar_first_officer)==-1:
return
# Take Flight engineer input
aadhar_engineer = input('Enter Flight Engineer\'s Aadhar card number: ')
if add_flight_crew_serves_on_route(cur,con,attr['Route ID'],aadhar_engineer)==-1:
return
num_hostess=int(input("Enter number of non-chief flight attendants: "))
for i in range(num_hostess):
aadhar_to_add=input("Input aadhar of non-chief flight attendant: ")
if add_flight_crew_serves_on_route(cur,con,attr['Route ID'],aadhar_to_add)==-1:
return
if add_crew_has_worked_together(cur,con,attr['Pilot captain Aadhar_card_number'],aadhar_first_officer,
attr['Chief_flight_attendant Aadhar_card_number'],aadhar_engineer)==-1:
return
con.commit()
###################################################################
except Exception as e:
error_print('Failed to insert into the database.')
con.rollback()
print(e)
input('Press any key to continue.')
return
#################################################################################################
# Done
def add_pnr_info_deduction(cur, con, pnr_of_boarding_pass):
table_name = "`PNR info deduction`"
attr = {}
#print('Enter details of the PNR info deduction: ')
#########################################################################################
attr["PNR_number"] = pnr_of_boarding_pass
attr["fk_to_route_Route ID"] = input("Enter Route ID: ")
attr["Scheduled Boarding Time"] = input("Enter schdeuled boarding time allotted to you based \
on your seat, class of traveletc.: ")
attr["class_of_travel"] = input("Enter Travel class business/economy: ")
attr["fk_to_airport_src_iata_code"] = input("Enter source airport IATA code: ")
########################################################################
keys_str, values_str = get_query_atoms(attr)
#print(keys_str)
#print(values_str)
query_str = 'INSERT INTO '+table_name + \
" ( "+keys_str+" ) VALUES"+" ( "+values_str+" );"
#print("query str is %s->", query_str)
try:
cur.execute(query_str)
offload_commit(con)
return 0
except Exception as e:
print('Failed to insert into the database.')
con.rollback()
print(e)
input('Press any key to continue.')
return -1
# Done
def add_special_services(cur, con, barcode_number, special_service_to_add):
#print("inside special_services")
table_name = "`special_services`"
attr = {}
#print('Enter details of the new special_services: ')
attr["fk_Barcode number"] = barcode_number
attr["Special services"] = special_service_to_add
keys_str, values_str = get_query_atoms(attr)
#print(keys_str)
# print(values_str)
query_str = 'INSERT INTO '+table_name + \
" ( "+keys_str+" ) VALUES"+" ( "+values_str+" );"
#print("query str is %s->", query_str)
try:
cur.execute(query_str)
offload_commit(con)
success_print("Special services inserted")
return 0
except Exception as e:
print('Failed to insert into the database.')
con.rollback()
print(e)
input('Press any key to continue.')
return -1
# done
def add_luggage(cur, con, barcode_number):
#print("inside luggage")
table_name = "`luggage`"
attr = {}
#print('Enter details of the new luggage:')
attr["Baggage ID"] = input("Enter Baggage ID")
attr["fk_to_Barcode number"] = barcode_number
keys_str, values_str = get_query_atoms(attr)
#print(keys_str)
#print(values_str)
query_str = 'INSERT INTO '+table_name + \
" ( "+keys_str+" ) VALUES"+" ( "+values_str+" );"
try:
cur.execute(query_str)
offload_commit(con)
success_print("Luggage inserted")
return 0
except Exception as e:
print('Failed to insert into the database.')
con.rollback()
print(e)
input('Press any key to continue.')
return -1
# Done
def add_boarding_pass_details(cur, con):
# print("inside add_boarding pass function")
table_name = "`boarding_pass`"
attr = {}
print('Enter details of the Boarding pass entry: ')
attr["Barcode number"] = input("Enter 12 char boarding pass barcode number: ")
attr["fk_PNR_number"] = input("Enter PNR number to which boarding pass belongs: ")
attr["Seat"] = input("Enter seat: ")
attr["fk_to_passenger_Aadhar_card_number"] = input("Enter 12 digit Aadhar Card Number: ")
keys_str, values_str = get_query_atoms(attr)
print(keys_str)
print(values_str)
query_str = 'INSERT INTO '+table_name + \
" ( "+keys_str+" ) VALUES"+" ( "+values_str+" );"
try:
cur.execute(query_str)
offload_commit(con)
add_yes_print()
#############################################################################################
num_luggages = int(input("Enter number of luggages you want to link to this boarding pass: "))
for i in range(num_luggages):
if add_luggage(cur, con, attr["Barcode number"]) == -1:
return
##########################################################################################
add_more_details = int(input('''
Press 1 if you want to add additional details like class_of_travel, Src airport etc.,
\n
Press 0 if you have already added these details for another boarding pass on the same PNR'''))
if add_more_details == 1:
if add_pnr_info_deduction(cur, con, attr["fk_PNR_number"]) == -1:
return
#########################################################################################
#############################################################################################
print("List of available special services that can be added are \nA. Wheelchair \nB. Disability Assistance \nC. XL seats \nD. Priority Boarding\n")
ss_dict = {
"A": "Wheelchair",
"B": "Disability Assistance",
"C": "XL seats",
"D": "Priority Boarding"
}
ss_str = input("Enter special services\nEg: enter AC for Wheelchair and XL seats: ")
for i in range(len(ss_str)):
add_ss = ss_dict[ss_str[i]]
if add_special_services(cur, con, attr["Barcode number"], add_ss) == -1:
return
##########################################################################################
con.commit()
except Exception as e:
print('Failed to insert into the database.')
con.rollback()
print(e)
input('Press any key to continue.')
return
#######################################################################################
#done
def add_on_ground_emp(cur, con, aadhar_num):
debug_print("inside add_on_ground_emp")
table_name = "`On_ground`"
attr = {}
print('Enter details of the add_on_ground_emp: ')
attr['fk_to_airline_crew_Aadhar_card_number'] = aadhar_num
attr['Job title'] = input("Job title: ")
keys_str, values_str = get_query_atoms(attr)
# print(keys_str)
# print(values_str)
query_str = 'INSERT INTO '+table_name + \
" ( "+keys_str+" ) VALUES"+" ( "+values_str+" );"
#print("query str is %s->", query_str)
try:
cur.execute(query_str)
offload_commit(con)
success_print("On_ground employee inserted")
return 0
except Exception as e:
print('Failed to insert into the database.')
con.rollback()
error_print(e)
input('Press any key to continue.')
return -1
#done
def add_pilot(cur, con, aadhar_num):
debug_print("inside Pilot")
table_name = "`Pilot`"
attr = {}
print('Enter details of the Pilot: ')
attr['fk_to_flight_crew_Aadhar_card_number'] = aadhar_num
attr["Pilot license number"] = input("Enter Pilot license number: ")
attr["Number of flying hours"] = input("Enter number of flying hours: ")
#########################################################################
keys_str, values_str = get_query_atoms(attr)
#print(keys_str)
#print(values_str)
query_str = 'INSERT INTO '+table_name + \
" ( "+keys_str+" ) VALUES"+" ( "+values_str+" );"
# print("query str is %s->", query_str)
try:
cur.execute(query_str)
offload_commit(con)
success_print("added pilot")
return 0
except Exception as e:
print('Failed to insert into the database.')
con.rollback()
error_print(e)
input('Press any key to continue.')
return -1
#done
def add_flight_attendant(cur, con, aadhar_num):
debug_print("inside flight_attendant")
table_name = "`flight_attendant`"
attr = {}
print('Enter details of the flight_attendant: ')
attr['fk_to_flight_crew_Aadhar_card_number'] = aadhar_num