class Querier:
def setup(self):
self.connection = Connection(host=os.environ["POSTGRES_HOST"],
dbname=os.environ["POSTGRES_DB"],
user=os.environ["POSTGRES_USER"],
password=os.environ["POSTGRES_PASS"])
self.connection.connect()
self.cursor = self.connection.cursor()
self.cm = CrsMapper("locations.json")
def get_some_data(self, origin, destination, _type, _time):
connection = self.connection
cursor = self.cursor
d = (datetime.datetime.now() - datetime.timedelta(days=9)).date()
# Set the query up appropriately for the data we have received.
if origin is not None and destination is not None:
cursor.execute(
"select ps.id, ps.uid, psl1.id, psl2.id from pro_schedule ps INNER JOIN pro_schedule_location psl1 on (ps.id=psl1.pro_schedule_id and psl1.crs=%s and psl1.scheduled_departure_time >= %s) INNER JOIN pro_schedule_location psl2 on (ps.id=psl2.pro_schedule_id and psl2.crs=%s and psl1.scheduled_departure_time < psl2.scheduled_arrival_time) where ps.{} > %s ORDER BY psl1.scheduled_departure_time ASC LIMIT 10"
.format("weekday" if _type == "weekdays" else "weekend"),
(origin, _time, destination, d))
elif origin is not None and destination is None:
cursor.execute(
"select ps.id, ps.uid, psl1.id from pro_schedule ps INNER JOIN pro_schedule_location psl1 on (ps.id=psl1.pro_schedule_id and psl1.crs=%s and psl1.scheduled_departure_time >= %s) where ps.{} > %s ORDER BY psl1.scheduled_departure_time ASC LIMIT 10"
.format("weekday" if _type == "weekdays" else "weekend"),
(origin, _time, d))
else:
cursor.execute(
"select ps.id, ps.uid, psl1.id from pro_schedule ps INNER JOIN pro_schedule_location psl1 on (ps.id=psl1.pro_schedule_id and psl1.crs=%s and psl1.scheduled_arrival_time >= %s) where ps.{} > %s ORDER BY psl1.scheduled_arrival_time ASC LIMIT 10"
.format("weekday" if _type == "weekdays" else "weekend"),
(destination, _time, d))
rows = cursor.fetchall()
routes = []
segments = []
trains = []
stations = []
for row in rows:
cursor.execute(
"SELECT id, pro_schedule_id, crs, scheduled_arrival_time, scheduled_departure_time, type, position, date_last_seen from pro_schedule_location where pro_schedule_id=%s ORDER BY position ASC",
(row[0], ))
rs = cursor.fetchall()
previous = None
route = Route(id=uuid.uuid4())
passed_journey_destination = False
dt = None
orr = None
departure_time = None
arrival_time = None
slid_for_train_lateness = None
for r in rs:
if orr is None:
if r[5] == 'OR':
orr = r[2]
if passed_journey_destination is True:
if r[5] == 'DT':
dt = r[2]
continue
# We haven't reached the origin yet. Iterate until we get there.
if previous is None:
# If this is not the origin, continue to the next iteration.
if r[0] != row[2] and origin is not None:
continue
# It's the origin.
stations.append(
Station(r[2], self.cm.name(r[2]), 1, self.cm.lat(r[2]),
self.cm.lon(r[2])))
route.stations.append(r[2])
previous = r
departure_time = r[4]
#if destination is None:
# slid_for_train_lateness = r[0]
continue
# Create a station for where we've arrived, and create a segment from previous to here.
stations.append(
Station(r[2], self.cm.name(r[2]), 1, self.cm.lat(r[2]),
self.cm.lon(r[2])))
route.stations.append(r[2])
seglate = get_lateness_value_on_arrival(
self.connection.cursor(), r[0])
segments.append(
Segment("{}{}".format(previous[2], r[2]), previous[2],
r[2], seglate, [
route.id,
]))
previous = r
# If we've reached the destination. Don't break out of the loop though cos we need
# to know the train's final destination.
if destination is not None and origin is not None:
if r[0] == row[3]:
slid_for_train_lateness = r[0]
passed_journey_destination = True
if r[5] == 'DT':
dt = r[2]
continue
else:
if origin is None and arrival_time is None:
if r[0] == row[2]:
slid_for_train_lateness = r[0]
arrival_time = r[3]
passed_journey_destination = True
dt = r[2]
# Temporary hack
slid_for_train_lateness = r[0]
# Add the route to the list.
routes.append(route)
#print(slid_for_train_lateness)
if destination is None and origin is not None:
#use_departure=True
use_departure = False
else:
use_departure = False
lb = LatenessBuilder(self.connection.cursor(),
slid_for_train_lateness,
use_departure=use_departure)
lateness = lb.build()
if origin is not None:
train_name = "{} to {}".format(
departure_time.strftime("%H:%M"), self.cm.name(dt))
else:
train_name = "{} from {}".format(
arrival_time.strftime("%H:%M"), self.cm.name(orr))
trains.append(
Train(row[1], route.id, train_name,
"Data collected over last 4 weeks", lateness))
# Deduplicate routes, segments, and stations. Trains don't need it as they are queried uniquely from DB.
new_stations = {}
for i in stations:
if i.id not in new_stations:
new_stations[i.id] = i
stations = list(new_stations.values())
new_segments = {}
for i in segments:
if i.id not in new_segments:
i.l_acc = []
if i.lateness is not None:
i.l_acc.append(i.lateness)
new_segments[i.id] = i
else:
[new_segments[i.id].routes.append(a) for a in i.routes]
if i.lateness is not None:
new_segments[i.id].l_acc.append(i.lateness)
segments = list(new_segments.values())
# Collapse new segment latenesses.
for _, v in new_segments.items():
if len(v.l_acc) == 0:
v.lateness == None
else:
acc = sum(v.l_acc)
v.lateness = (float(acc) / len(v.l_acc)) / 60
new_routes = {}
for i in routes:
key = "-".join(i.stations)
if key not in new_routes:
new_routes[key] = i
else:
for j in segments:
if i.id in j.routes:
j.routes.remove(i.id)
if new_routes[key].id not in j.routes:
j.routes.append(new_routes[key].id)
for k in trains:
if i.id == k.route:
k.route = new_routes[key].id
routes = list(new_routes.values())
# Turn it into JSON.
j = {}
j["routes"] = routes
j["segments"] = segments
j["trains"] = trains
j["stations"] = stations
if origin is not None:
j["origin_text"] = self.cm.name(origin) + " [" + origin + "]"
if destination is not None:
j["destination_text"] = self.cm.name(
destination) + " [" + destination + "]"
return json.dumps(j, cls=JsonEncoder, indent=4)
def get_calling_points(self, origin, destination, uid):
cursor = self.cursor
cursor.execute(
"select pro_schedule_location.id, crs, scheduled_arrival_time, scheduled_departure_time, type from pro_schedule, pro_schedule_location where uid=%s and pro_schedule.id=pro_schedule_location.pro_schedule_id ORDER BY position ASC",
(uid, ))
rows = cursor.fetchall()
points = []
reached_origin = False if origin is not None else True
reached_destination = False
for row in rows:
if reached_origin is False and row[1] != origin:
continue
reached_origin = True
lb = LatenessBuilder(self.connection.cursor(),
row[0],
use_departure=True if row[4] == 'OR'
or row[1] == origin else False)
lateness = lb.build()
data = {
"name":
self.cm.name(row[1]),
"id":
row[0],
"time":
row[2].strftime("%H:%M")
if row[2] is not None else row[3].strftime("%H:%M"),
"lateness":
lateness,
}
points.append(data)
# If we've reached the destination, break out of the loop.
if destination != None and row[1] == destination:
break
data = {uid: {"callingPoints": points}}
return json.dumps(data, cls=JsonEncoder, indent=4)
class Querier:
def setup(self):
self.connection = Connection(host=os.environ["POSTGRES_HOST"],
dbname=os.environ["POSTGRES_DB"],
user=os.environ["POSTGRES_USER"],
password=os.environ["POSTGRES_PASS"])
self.connection.connect()
self.cursor = self.connection.cursor()
self.cm = CrsMapper("locations.json")
def get_some_data(self, origin, destination, _type, _time):
connection = self.connection
cursor = self.cursor
d=(datetime.datetime.now() - datetime.timedelta(days=9)).date()
# Set the query up appropriately for the data we have received.
if origin is not None and destination is not None:
cursor.execute("select ps.id, ps.uid, psl1.id, psl2.id from pro_schedule ps INNER JOIN pro_schedule_location psl1 on (ps.id=psl1.pro_schedule_id and psl1.crs=%s and psl1.scheduled_departure_time >= %s) INNER JOIN pro_schedule_location psl2 on (ps.id=psl2.pro_schedule_id and psl2.crs=%s and psl1.scheduled_departure_time < psl2.scheduled_arrival_time) where ps.{} > %s ORDER BY psl1.scheduled_departure_time ASC LIMIT 10".format("weekday" if _type == "weekdays" else "weekend"), (origin, _time, destination, d))
elif origin is not None and destination is None:
cursor.execute("select ps.id, ps.uid, psl1.id from pro_schedule ps INNER JOIN pro_schedule_location psl1 on (ps.id=psl1.pro_schedule_id and psl1.crs=%s and psl1.scheduled_departure_time >= %s) where ps.{} > %s ORDER BY psl1.scheduled_departure_time ASC LIMIT 10".format("weekday" if _type == "weekdays" else "weekend"), (origin, _time, d))
else:
cursor.execute("select ps.id, ps.uid, psl1.id from pro_schedule ps INNER JOIN pro_schedule_location psl1 on (ps.id=psl1.pro_schedule_id and psl1.crs=%s and psl1.scheduled_arrival_time >= %s) where ps.{} > %s ORDER BY psl1.scheduled_arrival_time ASC LIMIT 10".format("weekday" if _type == "weekdays" else "weekend"), (destination, _time, d))
rows = cursor.fetchall()
routes = []
segments = []
trains = []
stations = []
for row in rows:
cursor.execute("SELECT id, pro_schedule_id, crs, scheduled_arrival_time, scheduled_departure_time, type, position, date_last_seen from pro_schedule_location where pro_schedule_id=%s ORDER BY position ASC", (row[0],))
rs = cursor.fetchall()
previous = None
route = Route(id=uuid.uuid4())
passed_journey_destination = False
dt = None
orr = None
departure_time = None
arrival_time = None
slid_for_train_lateness = None
for r in rs:
if orr is None:
if r[5] == 'OR':
orr = r[2]
if passed_journey_destination is True:
if r[5] == 'DT':
dt = r[2]
continue
# We haven't reached the origin yet. Iterate until we get there.
if previous is None:
# If this is not the origin, continue to the next iteration.
if r[0] != row[2] and origin is not None:
continue
# It's the origin.
stations.append(Station(r[2], self.cm.name(r[2]), 1, self.cm.lat(r[2]), self.cm.lon(r[2])))
route.stations.append(r[2])
previous = r
departure_time = r[4]
#if destination is None:
# slid_for_train_lateness = r[0]
continue
# Create a station for where we've arrived, and create a segment from previous to here.
stations.append(Station(r[2], self.cm.name(r[2]), 1, self.cm.lat(r[2]), self.cm.lon(r[2])))
route.stations.append(r[2])
seglate = get_lateness_value_on_arrival(self.connection.cursor(), r[0])
segments.append(Segment("{}{}".format(previous[2],r[2]), previous[2], r[2], seglate, [route.id,]))
previous = r
# If we've reached the destination. Don't break out of the loop though cos we need
# to know the train's final destination.
if destination is not None and origin is not None:
if r[0] == row[3]:
slid_for_train_lateness = r[0]
passed_journey_destination = True
if r[5] == 'DT':
dt = r[2]
continue
else:
if origin is None and arrival_time is None:
if r[0] == row[2]:
slid_for_train_lateness = r[0]
arrival_time = r[3]
passed_journey_destination = True
dt = r[2]
# Temporary hack
slid_for_train_lateness = r[0]
# Add the route to the list.
routes.append(route)
#print(slid_for_train_lateness)
if destination is None and origin is not None:
#use_departure=True
use_departure=False
else:
use_departure=False
lb = LatenessBuilder(self.connection.cursor(), slid_for_train_lateness, use_departure=use_departure)
lateness = lb.build()
if origin is not None:
train_name = "{} to {}".format(departure_time.strftime("%H:%M"), self.cm.name(dt))
else:
train_name = "{} from {}".format(arrival_time.strftime("%H:%M"), self.cm.name(orr))
trains.append(Train(row[1], route.id, train_name, "Data collected over last 4 weeks", lateness))
# Deduplicate routes, segments, and stations. Trains don't need it as they are queried uniquely from DB.
new_stations = {}
for i in stations:
if i.id not in new_stations:
new_stations[i.id] = i
stations = list(new_stations.values())
new_segments = {}
for i in segments:
if i.id not in new_segments:
i.l_acc = []
if i.lateness is not None:
i.l_acc.append(i.lateness)
new_segments[i.id] = i
else:
[new_segments[i.id].routes.append(a) for a in i.routes]
if i.lateness is not None:
new_segments[i.id].l_acc.append(i.lateness)
segments = list(new_segments.values())
# Collapse new segment latenesses.
for _,v in new_segments.items():
if len(v.l_acc) == 0:
v.lateness == None
else:
acc = sum(v.l_acc)
v.lateness = (float(acc)/len(v.l_acc))/60
new_routes = {}
for i in routes:
key = "-".join(i.stations)
if key not in new_routes:
new_routes[key] = i
else:
for j in segments:
if i.id in j.routes:
j.routes.remove(i.id)
if new_routes[key].id not in j.routes:
j.routes.append(new_routes[key].id)
for k in trains:
if i.id == k.route:
k.route = new_routes[key].id
routes = list(new_routes.values())
# Turn it into JSON.
j = {}
j["routes"] = routes
j["segments"] = segments
j["trains"] = trains
j["stations"] = stations
if origin is not None:
j["origin_text"] = self.cm.name(origin)+" ["+origin+"]"
if destination is not None:
j["destination_text"] = self.cm.name(destination)+" ["+destination+"]"
return json.dumps(j, cls=JsonEncoder, indent=4)
def get_calling_points(self, origin, destination, uid):
cursor = self.cursor
cursor.execute("select pro_schedule_location.id, crs, scheduled_arrival_time, scheduled_departure_time, type from pro_schedule, pro_schedule_location where uid=%s and pro_schedule.id=pro_schedule_location.pro_schedule_id ORDER BY position ASC", (uid,))
rows = cursor.fetchall()
points = []
reached_origin = False if origin is not None else True
reached_destination = False
for row in rows:
if reached_origin is False and row[1] != origin:
continue
reached_origin = True
lb = LatenessBuilder(self.connection.cursor(), row[0], use_departure=True if row[4] == 'OR' or row[1] == origin else False)
lateness = lb.build()
data = {
"name": self.cm.name(row[1]),
"id": row[0],
"time": row[2].strftime("%H:%M") if row[2] is not None else row[3].strftime("%H:%M"),
"lateness": lateness,
}
points.append(data)
# If we've reached the destination, break out of the loop.
if destination != None and row[1] == destination:
break
data = {
uid: {
"callingPoints": points
}
}
return json.dumps(data, cls=JsonEncoder, indent=4)