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