async def deleteTrip(req: deleteTrip_payload):
cf.logmessage("deleteTrip api call")
space_id = int(os.environ.get('SPACE_ID', 1))
pattern_id = req.pattern_id
trip_id = req.trip_id
# check if its there in trips table and stop_times table
s1 = f"""select count(*) from trips
where space_id = {space_id}
and pattern_id = '{pattern_id}'
and id = '{trip_id}'
"""
c1 = dbconnect.makeQuery(s1, output='oneValue')
s2 = f"""select count(*) from stop_times
where space_id = {space_id}
and trip_id = '{trip_id}'
"""
c2 = dbconnect.makeQuery(s2, output='oneValue')
returnD = {
"message": "success",
"trips_deleted": 0,
"stop_times_deleted": 0
}
if c1:
d1 = f"""delete from trips
where space_id = {space_id}
and pattern_id = '{pattern_id}'
and id = '{trip_id}'
"""
d1Count = dbconnect.execSQL(d1)
returnD['trips_deleted'] = d1Count
if c2:
d2 = f"""delete from stop_times
where space_id = {space_id}
and trip_id = '{trip_id}'
"""
d2Count = dbconnect.execSQL(d2)
returnD['stop_times_deleted'] = d2Count
return returnD
async def deleteTrip(req: addTrip_payload):
cf.logmessage("addTrip api call")
space_id = int(os.environ.get('SPACE_ID', 1))
pattern_id = req.pattern_id
start_time = req.start_time
trip_id = cf.makeUID()
i1 = f"""insert into trips
(space_id, id, pattern_id, start_time, name) values
({space_id}, '{trip_id}', '{pattern_id}', '{start_time}', '{trip_id}_{start_time}')
"""
s1 = f"""select stop_sequence, stop_id from pattern_stops
where space_id = {space_id}
and pattern_id = '{pattern_id}'
order by stop_sequence
"""
df1 = dbconnect.makeQuery(s1, output='df')
df2 = df1[['stop_sequence']].copy()
df2['space_id'] = space_id
df2['trip_id'] = trip_id
df2['id'] = cf.assignUID(df1, length=7)
df2['arrival_time'] = None
df2.at[0, 'arrival_time'] = start_time
# to do: populate remaining arrival times also, taking a default speed
# and calculating lat-long distance / routed distance
status1 = dbconnect.execSQL(i1)
status2 = dbconnect.addTable(df2, 'stop_times')
returnD = {"message": "success"}
returnD['trip_id'] = trip_id
returnD['added_stop_times'] = 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