def deletePatterns(req: deletePatterns_payload):
cf.logmessage("deletePatterns api call")
space_id = int(os.environ.get('SPACE_ID', 1))
patternsSQL = cf.quoteNcomma(req.patterns)
d1 = f"delete from pattern_stops where space_id={space_id} and pattern_id in ({patternsSQL})"
d2 = f"delete from patterns where space_id={space_id} and id in ({patternsSQL})"
d1Count = dbconnect.execSQL(d1)
d2Count = dbconnect.execSQL(d2)
# also trips and timings under the pattern
d3 = f"delete from stop_times where space_id={space_id} and trip_id in (select id from trips where pattern_id in ({patternsSQL}) )"
d4 = f"delete from trips where where space_id={space_id} and pattern_id in ({patternsSQL})"
d3Count = dbconnect.execSQL(d3)
d4Count = dbconnect.execSQL(d4)
returnD = {
'message': "success",
"pattern_stops_deleted": d1Count,
"patterns_deleted": d2Count,
"trips_deleted": d4Count,
"stop_times_deleted": d4Count
}
cf.logmessage(returnD)
return returnD
def diagnoseStops(req: diagnoseStops_payload):
'''
Diagnose stops for deleting
Fetch each stop's patterns, routes
'''
cf.logmessage("diagnoseStops api call")
idsListSQL = cf.quoteNcomma(req.idsList)
s1 = f"""
select t1.stop_id, t1.pattern_id,
t2.route_id, t2.name as pattern_name,
t3.depot, t3.name, t3.description
from pattern_stops as t1
left join patterns as t2
on t1.pattern_id = t2.id
left join routes as t3
on t2.route_id = t3.id
where t1.stop_id in ({idsListSQL})
"""
df1 = dbconnect.makeQuery(s1, output='df', fillna=False)
returnD = {"message": "success"}
if not len(df1):
returnD['patternCount'] = 0
return returnD
# returnD['stops'] = df1.to_dict(orient='records')
# print(df1)
# print(df1['route_id'])
# return returnD
# print("uniques:",df1['route_id'].unique())
returnD['patterns'] = [
x for x in df1['pattern_id'].unique() if x is not None
]
returnD['patternCount'] = len(returnD['patterns'])
returnD['routes'] = [x for x in df1['route_id'].unique() if x is not None]
returnD['routeCount'] = len(returnD['routes'])
return returnD
def deleteStops(req: deleteStops_payload):
"""
Delete stops
"""
cf.logmessage("deleteStops api call")
idsList = req.idsList
space_id = int(os.environ.get('SPACE_ID', 1))
# dependencyStatus, details = checkStopDependency(idsList, space_id)
# if not dependencyStatus:
idsListSQL = cf.quoteNcomma(idsList)
d1 = f"delete from stops_master where id in ({idsListSQL})"
dCount = dbconnect.execSQL(d1)
returnD = {
"message": "success",
"deleted": dCount,
"confirmation_required": False
}
if dCount:
return returnD
else:
raise HTTPException(status_code=400, detail="Nothing to delete")
def deleteStopsConfirm(req: deleteStopsConfirm_payload):
cf.logmessage("deleteStopsConfirm api call")
idsListSQL = cf.quoteNcomma(req.idsList)
space_id = int(os.environ.get('SPACE_ID', 1))
returnD = {"message": "success"}
# find the patterns
s1 = f"""select distinct pattern_id from pattern_stops
where space_id = {space_id}
and stop_id in ({idsListSQL})"""
patternsList = dbconnect.makeQuery(s1, output='column')
if len(patternsList):
# find which routes affected
patternsListSQL = cf.quoteNcomma(patternsList)
s4 = f"""select distinct route_id from patterns
where space_id = {space_id}
and id in ({patternsListSQL})"""
routesList = dbconnect.makeQuery(s4, output='column')
returnD['routeCount'] = len(routesList)
# delete stop's entries from pattern_stops
d1 = f"""delete from pattern_stops
where space_id = {space_id}
and stop_id in ({idsListSQL})"""
pattern_deleted = dbconnect.execSQL(d1)
returnD['patternCount'] = pattern_deleted
# now, update job in all the patterns where this stop was.
for pN, pattern_id in enumerate(patternsList):
s2 = f"""select id, stop_id, stop_sequence from pattern_stops
where space_id = {space_id}
and pattern_id='{pattern_id}'
order by stop_sequence
"""
pattern_stops = dbconnect.makeQuery(s2,
output='list',
fillna=False)
# cf.logmessage(f"Pattern {pattern_id}: {len(pattern_stops)} stops originally")
counter = 0
for row in pattern_stops:
if row['stop_id'] in req.idsList:
# pattern_deleted += 1
continue
counter += 1
if row['stop_sequence'] == counter:
# sequence is as-is, do nothing
continue
else:
u1 = f"""update pattern_stops set stop_sequence={counter}
where id='{row['id']}'
"""
ustatus = dbconnect.execSQL(u1)
if counter > 0:
cf.logmessage(
f"Changed pattern {pattern_id}, stops count changed to {counter}"
)
# else if the stop isn't in any patterns, proceed to delete from stops_master
# now delete from stops master
d3 = f"delete from stops_master where id in ({idsListSQL})"
dcount = dbconnect.execSQL(d3)
returnD['stopCount'] = dcount
return returnD
async def saveTimings(req: Request):
cf.logmessage("saveTimings api call")
space_id = int(os.environ.get('SPACE_ID', 1))
returnD = {'message': "success"}
# making the api take a custom json array
# from https://stackoverflow.com/a/64379772/4355695 (that and other answers)
reqD = await req.json()
# print(reqD)
if (not len(reqD.get('data', []))) or (not isinstance(
reqD.get('data', []), list)):
raise HTTPException(status_code=400, detail="No data")
df1 = pd.DataFrame(reqD['data']).fillna('')
if 'stop_id' in df1.columns: del df1['stop_id']
if 'name' in df1.columns: del df1['name']
keepcols = ['stop_sequence']
df2 = pd.melt(df1,
id_vars=keepcols,
var_name='trip_id',
value_name='arrival_time').sort_values(
['trip_id', 'stop_sequence']).reset_index(drop=True)
# df2.to_csv('stop_times.csv',index=False)
df2['id'] = cf.assignUID(df2, length=7)
df2['space_id'] = space_id
# TO DO: time validation
for N in range(len(df2)):
if df2.at[N, 'arrival_time'] == '':
df2.at[N, 'arrival_time'] = None
tripsList = df2['trip_id'].unique().tolist()
if not len(tripsList):
raise HTTPException(status_code=400, detail="No tripIds in data")
trip_idSQL = cf.quoteNcomma(tripsList)
# fully delete existing stop_times for this pattern and replace with new
d1 = f"""delete from stop_times
where space_id = {space_id}
and trip_id in ({trip_idSQL})"""
dCount1 = dbconnect.execSQL(d1)
returnD['old_count'] = dCount1
# df2.to_csv('sample.csv')
iStatus1 = dbconnect.addTable(df2, 'stop_times')
if not iStatus1:
returnD['new_count'] = 0
raise HTTPException(status_code=400,
detail="Failed to add stop_times data in DB")
# update trips data
# get all start times
start_times_lookup = df2[df2['stop_sequence'] == 1][[
'trip_id', 'arrival_time'
]].copy().set_index('trip_id').to_dict(orient='index')
print(start_times_lookup)
# to do later: get all end times also. Or, decide to drop that and don't bother.
returnD['trips_updated'] = 0
for trip_id in tripsList:
uList = []
uList.append(f"last_updated = CURRENT_TIMESTAMP")
uList.append(f"modified_by = 'admin'")
if start_times_lookup.get(trip_id, False):
start_time = start_times_lookup[trip_id]['arrival_time']
uList.append(f"start_time = '{start_time}'")
uList.append(f"name = '{trip_id}_{start_time}'")
else:
uList.append(f"start_time = NULL")
uList.append(f"name = '{trip_id}'")
u1 = f"""update trips
set {', '.join(uList)}
where space_id = {space_id}
and id = '{trip_id}'
"""
uCount = dbconnect.execSQL(u1)
returnD['trips_updated'] += uCount
returnD['new_count'] = len(df2)
return returnD
def updateTimingsForPattern(pattern_id, pattern_length):
# to do: if a pattern's length has changed, then update timings entries for it
space_id = int(os.environ.get('SPACE_ID', 1))
totalAdded = totalRemoved = 0
# find all trips for the pattern
s1 = f"""select t1.id as trip_id, t2.id, t2.stop_sequence
from trips as t1
left join stop_times as t2
on t1.id = t2.trip_id
where t1.space_id = {space_id}
and t1.pattern_id = '{pattern_id}'
and t2.space_id = {space_id}
order by t2.trip_id, t2.stop_sequence
"""
df_exist_all = dbconnect.makeQuery(s1, output='df', keepCols=True)
# tripsList = dbconnect.makeQuery(s1, output='column')
if not len(df_exist_all):
return 0, 0, 0
tripsList = df_exist_all['trip_id'].unique().tolist()
# if not len(tripsList):
# return len(tripsList), totalAdded, totalRemoved
all_delIds = []
all_df_new = []
for trip_id in tripsList:
# get existing
cf.logmessage(f"trip_id: {trip_id}")
df_exist = df_exist_all[df_exist_all['trip_id'] ==
trip_id].copy().reset_index(drop=True)
# space_id = int(os.environ.get('SPACE_ID',1))
# s1 = f"""select id, stop_sequence from stop_times
# where space_id = {space_id}
# and trip_id = '{trip_id}'
# order by stop_sequence
# """
# df_exist = dbconnect.makeQuery(s1, output='df', keepCols=True)
if len(df_exist) == pattern_length:
# no change needed!
continue
elif len(df_exist) > pattern_length:
# delete flow
delIds = df_exist[pattern_length:]['id'].tolist()
if len(delIds): all_delIds += delIds
else:
# add flow
newSeq = list(range(len(df_exist) + 1, pattern_length + 1))
df_new = pd.DataFrame({'stop_sequence': newSeq})
df_new['id'] = cf.assignUID(df_new, length=7)
df_new['space_id'] = space_id
df_new['trip_id'] = trip_id
all_df_new.append(df_new)
# delete at once
if len(all_delIds):
delIdsSQL = cf.quoteNcomma(all_delIds)
cf.logmessage(f"ids to delete: {all_delIds}")
d1 = f"""delete from stop_times
where id in ({delIdsSQL})
"""
totalRemoved = dbconnect.execSQL(d1)
# add at once
if len(all_df_new):
add_df = pd.concat(all_df_new, sort=False, ignore_index=True)
print("add_df:")
print(add_df)
totalAdded = dbconnect.addTable(add_df, 'stop_times')
return len(tripsList), totalAdded, totalRemoved
def loadTimings(req: loadTimings_payload):
cf.logmessage("loadTimings api call")
space_id = int(os.environ.get('SPACE_ID', 1))
pattern_id = req.pattern_id
returnD = {'message': "success", "stops": [], "trips": [], "num_trips": 0}
# stops
s1 = f"""select t1.stop_sequence, t1.stop_id, t2.name
from pattern_stops as t1
left join stops_master as t2
on t1.stop_id = t2.id
where t1.space_id={space_id}
and t1.pattern_id = '{pattern_id}'
order by t1.stop_sequence
"""
df1 = dbconnect.makeQuery(s1, output='df', keepCols=True, fillna=True)
returnD['stops'] = df1.to_dict(orient='records')
# trips
s2 = f"""select * from trips
where space_id={space_id}
and pattern_id = '{pattern_id}'
order by start_time
"""
df2 = dbconnect.makeQuery(s2, output='df', keepCols=True, fillna=True)
df2['start_time'] = df2['start_time'].apply(lambda x: str(x)[:5])
returnD['num_trips'] = len(df2)
returnD['trips'] = df2.to_dict(orient='records')
# timings
if len(df2):
trip_idSQL = cf.quoteNcomma(df2['id'].tolist())
s3 = f"""select trip_id, stop_sequence, arrival_time from stop_times
where space_id={space_id}
and trip_id in ({trip_idSQL})
order by trip_id, stop_sequence
"""
df3 = dbconnect.makeQuery(s3, output='df', keepCols=True, fillna=True)
# df3['trip_id'] = df3['trip_id'].apply(lambda x: f"trip_{x}")
df3['arrival_time'] = df3['arrival_time'].apply(lambda x: str(x)[:5])
else:
df3 = pd.DataFrame(
columns=['trip_id', 'stop_sequence', 'arrival_time'])
# pivot by trip_id
df4 = df3.pivot(index='stop_sequence',
columns='trip_id',
values='arrival_time').fillna('').reset_index()
# merge in stop ids, names
df5 = pd.merge(df1, df4, on='stop_sequence', how='left')
# sort by start timings
allCols = list(df5.columns)
tripCols = [
x for x in allCols if x not in ('stop_sequence', 'stop_id', 'name')
]
newCols = ['stop_sequence', 'stop_id', 'name'] + sorted(tripCols)
returnD['stop_times'] = df5[newCols].to_dict(orient='records')
# TO DO: calc stop times offset from first trip or so
cf.logmessage(f"Got {len(df2)} trips, {len(df3)} timings total")
return returnD