def get_match_duration(match_id): """ Gets the duration of the match Args: match_id: id of a match in database returns: JSON record of the match duration, if match_id can't be found returns JSON with "match_id Not Found" """ hana = hana_connector.HanaConnector() connection = hana.connect() duration = pd.read_sql( """ SELECT "duration" FROM "DOTA2_TI8"."matches" WHERE "match_id" = {match_id} """.format(match_id=match_id), connection) if not duration.empty: response = make_response(duration.to_json(orient="records"), 200) else: response = make_response("{match_id Not Found}", 404) return response
def home(): """ This function just responds to the root URL Return: JSON HANA welcome Message """ hana = hana_connector.HanaConnector() hana.connect() data_set, columns = hana.execute("SELECT 'Hello Python World' FROM DUMMY") response = make_response(json.dumps(data_set), 200) return response
def get_match_ids(): """ Finds all available match_ids available in the database Returns: JSON array of all available match_ids, if no match can be found returns JSON with "No matches found" """ hana = hana_connector.HanaConnector() connection = hana.connect() ids = pd.read_sql( """ SELECT "match_id" FROM "DOTA2_TI8"."matches" ORDER BY "match_id" ASC """, connection) if not ids.empty: response = make_response(ids["match_id"].to_json(orient="values"), 200) else: response = make_response("{No Matches Found}", 404) return response
def get_kill_sequences(matchID): """ Finds kill sequences where numerous heros got killed within a time frame of 18 seconds Args: match_id: id of a match in database Returns: Pandas dataframe with scenes starts, end times """ hana = hana_connector.HanaConnector() connection = hana.connect() print("asdasd") combat_log_only_kills = pd.read_sql( """ SELECT * FROM "DOTA2_TI8"."combatlog" WHERE "match_id" = {matchID} AND "type" = 'DOTA_COMBATLOG_DEATH' AND "isTargetHero" = TRUE ORDER BY "tick" ASC """.format(matchID=matchID), connection) hana.close() combat_log_only_kills["adj_tick"] = combat_log_only_kills["tick"] - \ (combat_log_only_kills["tick"] - combat_log_only_kills["game_tick"]) combat_log_only_kills["adj_tick_prior"] = combat_log_only_kills[ "adj_tick"].shift(1) combat_log_only_kills["tick_delta"] = combat_log_only_kills["adj_tick"] - \ combat_log_only_kills["adj_tick_prior"] curr_tuple = [] number_kills = 0 kill_sequences = [] for index, kill in combat_log_only_kills.iterrows(): # curr_kill_xy = [kill['locationX_target'], kill['locationY_target']] if number_kills == 0: curr_tuple.append(kill['adj_tick']) number_kills = number_kills + 1 elif kill['tick_delta'] <= 540: number_kills = number_kills + 1 elif kill['tick_delta'] > 540: if number_kills > 2: curr_tuple.append(kill['adj_tick_prior']) kill_sequences.append(curr_tuple) number_kills = 1 curr_tuple = [kill['adj_tick']] else: curr_tuple = [kill['adj_tick']] number_kills = 1 kill_sequences_sec = pd.DataFrame() start_times = [] end_times = [] for start, end in kill_sequences: start_times.append(start / 30 - 15) # add 3 as buffer end_times.append(end / 30 + 5) # add 3 as buffer kill_sequences_sec['tick_start'] = start_times kill_sequences_sec['datetime_start'] = kill_sequences_sec[ 'tick_start'].apply(lambda x: datetime.utcfromtimestamp(x).isoformat()) kill_sequences_sec['tick_end'] = end_times kill_sequences_sec['datetime_end'] = kill_sequences_sec['tick_end'].apply( lambda x: datetime.utcfromtimestamp(x).isoformat()) kill_sequences_sec['type'] = "KILL_SEQUENCE" return kill_sequences_sec
def first_blood(match_id): """ Finds the first blood event in a given match_id Responds for example to localhost:5000/first_blood/4074440208 Args: match_id: id of a match in database Returns: Pandas dataframe with scene start, end time, attacker and target """ hana = hana_connector.HanaConnector() connection = hana.connect() first_blood = pd.read_sql( """ SELECT "tick", "type" FROM "DOTA2_TI8"."combatlog" WHERE "match_id" = {match_id} AND "type" = 'DOTA_COMBATLOG_FIRST_BLOOD' """.format(match_id=match_id), connection) # return empty dataframe if no match is found if first_blood.empty: return first_blood fb_tick = int(first_blood['tick']) first_death = pd.read_sql( """ SELECT * FROM "DOTA2_TI8"."combatlog" WHERE "match_id" = {match_id} AND "type" = 'DOTA_COMBATLOG_DEATH' AND "tick" = {tick} """.format(tick=fb_tick, match_id=match_id), connection) target_id = int(first_death['targetNameIdx']) damage_log = pd.read_sql( """ SELECT * FROM "DOTA2_TI8"."combatlog" WHERE "match_id" = {match_id} AND ( "type" = 'DOTA_COMBATLOG_DAMAGE' OR "type" = 'DOTA_COMBATLOG_CRITICAL_DAMAGE' ) AND "tick" < {tick} AND "tick" >= {tick} - 450 AND "targetNameIdx" = {target_id} ORDER BY "timestamp" ASC """.format(tick=fb_tick, target_id=target_id, match_id=match_id), connection) fb_output = pd.DataFrame(index=None) fb_output['type'] = first_blood['type'] fb_output['attacker'] = first_death['attackerName'] fb_output['target'] = first_death['targetName'] fb_output['start_time'] = datetime.utcfromtimestamp( damage_log['timestamp'].iloc[0]).isoformat() fb_output['end_time'] = datetime.utcfromtimestamp( damage_log['timestamp'].iloc[-1]).isoformat() return fb_output