-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathmain.py
146 lines (130 loc) · 7.35 KB
/
main.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
import datetime
import flask
from flask_sqlalchemy import SQLAlchemy
from SQL_queries import *
app = flask.Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///main.db'
MAX_EACH_TYPE_LIMIT = 100
MAX_RESULTS = 100
str_time_delta = lambda time_delta: (str(time_delta).replace(':', ' hrs ', 1)[:-3]+' mins').replace('00 mins', '')
db = SQLAlchemy(app)
class Train(db.Model):
__tablename__ = 'trains'
ID = db.Column(db.String, primary_key=1)
Name = db.Column(db.String)
RunsOn = db.Column(db.Integer, nullable=0)
def __repr__(self):
run_days = []
weekdays = ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun')
for idx in range(len(weekdays)):
if self.RunsOn & (1 <<idx):
run_days.append(weekdays[idx])
run_days_str = ', '.join(run_days)
return f'<Train (ID="{self.ID}", Name="{self.Name}", RunsOn="{run_days_str}")>'
class Stop(db.Model):
__tablename__ = 'stops'
train_no = db.Column(db.String, db.ForeignKey('trains.ID'), primary_key=1)
route_no = db.Column(db.Integer, primary_key=1)
serial_no = db.Column(db.Integer, primary_key=1)
station_code = db.Column(db.String)
station_name = db.Column(db.String)
distance = db.Column(db.Integer)
arr_day_cnt = db.Column(db.Integer)
arr_time = db.Column(db.String)
dept_day_cnt = db.Column(db.Integer)
dept_time = db.Column(db.String)
halt_time = db.Column(db.String)
def __repr__(self):
return f'<Stop (train_no="{self.train_no}, route_no="{self.route_no}", serial_no="{self.serial_no}", station_code="{self.station_code}") >'
@app.route("/")
def homepage():
return flask.redirect(flask.url_for('search'))
@app.route("/enquiry", methods=['GET','POST'])
def search():
fromStn = ''
toStn = ''
startDate = datetime.datetime.now().isoformat()[:10]
stations = None
search_results = None
with db.engine.connect() as db_cnx:
stations = db_cnx.execute(SQL_STATIONS_LIST).all()
if flask.request.method == 'POST':
fromStn = flask.request.form.get('fromStn')
toStn = flask.request.form.get('toStn')
startDate = datetime.date.fromisoformat(flask.request.form.get('startDate'))
weekday = startDate.weekday()
search_results = []
for cur_db_row in db_cnx.execute(SQL_DIRECT_TRAINS, {'fromStn':fromStn[:fromStn.index(' - ')], 'toStn':toStn[:toStn.index(' - ')], 'weekday':weekday}):
cur_row = { 'type' : 1 }
for idx in range(len(cur_db_row)):
cur_row[list(cur_db_row.keys())[idx]] = cur_db_row[idx]
cur_row['src_date'] = startDate.strftime('%d %b (%a)')
cur_row['dest_date'] = (startDate+datetime.timedelta(days=cur_db_row['dest_day_cnt']-cur_db_row['src_day_cnt'])).strftime('%d %b (%a)')
cur_row['total_time'] = ( datetime.datetime.strptime(f"{cur_db_row['dest_day_cnt']} {cur_db_row['dest_arr_time']}", "%d %H:%M") - datetime.datetime.strptime(f"{cur_db_row['src_day_cnt']} {cur_db_row['src_dept_time']}", "%d %H:%M") )
cur_row['total_time_str'] = str_time_delta(cur_row['total_time'])
cur_row['total_time_in_seconds'] = cur_row['total_time'].total_seconds()
search_results.append(cur_row)
cnt = 0
for cur_db_row in db_cnx.execute(SQL_TWO_CONNECTING_TRAINS, {'fromStn':fromStn[:fromStn.index(' - ')], 'toStn':toStn[:toStn.index(' - ')], 'weekday':weekday}):
if cnt >= MAX_EACH_TYPE_LIMIT or len(search_results) > MAX_RESULTS:
break
days_delta = cur_db_row['train1_dest_day_cnt'] - cur_db_row['train1_src_day_cnt'] + (cur_db_row['train1_dest_arr_time']>cur_db_row['train2_src_dept_time'])
train2_src_date = startDate+datetime.timedelta(days=days_delta)
if not (cur_db_row['train2_runson'] & (1<<train2_src_date.weekday())):
continue
cur_row = { 'type' : 2 }
for idx in range(len(cur_db_row)):
cur_row[list(cur_db_row.keys())[idx]] = cur_db_row[idx]
cur_row['train1_src_date'] = startDate.strftime('%d %b (%a)')
cur_row['train1_dest_date'] = (startDate+datetime.timedelta(days=cur_db_row['train1_dest_day_cnt']-cur_db_row['train1_src_day_cnt'])).strftime('%d %b (%a)')
cur_row['train1_time'] = ( datetime.datetime.strptime(f"{cur_db_row['train1_dest_day_cnt']} {cur_db_row['train1_dest_arr_time']}", "%d %H:%M") - datetime.datetime.strptime(f"{cur_db_row['train1_src_day_cnt']} {cur_db_row['train1_src_dept_time']}", "%d %H:%M") )
cur_row['train1_time_str'] = str_time_delta(cur_row['train1_time'])
cur_row['train2_src_date'] = train2_src_date.strftime('%d %b (%a)')
cur_row['train2_dest_date'] = (train2_src_date+datetime.timedelta(days=cur_db_row['train2_dest_day_cnt']-cur_db_row['train2_src_day_cnt'])).strftime('%d %b (%a)')
cur_row['train2_time'] = ( datetime.datetime.strptime(f"{cur_db_row['train2_dest_day_cnt']} {cur_db_row['train2_dest_arr_time']}", "%d %H:%M") - datetime.datetime.strptime(f"{cur_db_row['train2_src_day_cnt']} {cur_db_row['train2_src_dept_time']}", "%d %H:%M") )
cur_row['train2_time_str'] = str_time_delta(cur_row['train2_time'])
cur_row['wait_time'] = ( datetime.datetime.strptime(f"{1+int(cur_db_row['train2_src_dept_time'] < cur_db_row['train1_dest_arr_time'])} {cur_db_row['train2_src_dept_time']}", "%d %H:%M") - datetime.datetime.strptime(f"1 {cur_db_row['train1_dest_arr_time']}", "%d %H:%M") )
cur_row['total_time'] = cur_row['train1_time'] + cur_row['train2_time'] + cur_row['wait_time']
cur_row['total_time_str'] = str_time_delta(cur_row['total_time'])
cur_row['total_time_in_seconds'] = cur_row['total_time'].total_seconds()
search_results.append(cur_row)
cnt+=1
search_results = sorted(search_results, key= lambda pkt: pkt['total_time_in_seconds'])
return flask.render_template(
'search.html',
fromStn = fromStn,
toStn = toStn,
startDate = startDate,
stations= stations,
search_results= search_results
)
@app.route("/facts", methods=['GET'])
def facts():
FACTS_MAP = {
'5': {
'QUEST' : 'Top 10 stations with most trains stop by',
'SQL_QRY':SQL_STATION_WITH_MOST_TRAINS
},
'1': {
'QUEST': 'Top 10 trains with most stops',
'SQL_QRY': SQL_TRAIN_WITH_MOST_STOPS
},
'4': {
'QUEST' : 'Top 10 trains that travel most distance in its trip',
'SQL_QRY':SQL_TRAIN_WITH_MAX_DISTANCE
},
'2': {
'QUEST': 'Top 10 trains with least stops',
'SQL_QRY': SQL_TRAIN_WITH_LEAST_STOPS
},
'3': {
'QUEST' : 'Top 10 trains that travel least in its trip',
'SQL_QRY': SQL_TRAIN_WITH_LEAST_DISTANCE
},
}
with db.engine.connect() as db_cnx:
for qid, pkt in FACTS_MAP.items():
FACTS_MAP[qid]['ANS'] = [ cur_db_row['statement'] for cur_db_row in db_cnx.execute(pkt['SQL_QRY'], {'limit':10}) ]
return flask.render_template('facts.html', goback=1,facts=FACTS_MAP)
if __name__ == "__main__":
app.run(host='0.0.0.0', port=80, debug=1)