def gen_runner_report(timestamp, report_path):
users = ChallengeSqlDB.get_all_users()
with open(report_path, "w", newline="") as csvfile:
fieldnames = [
"timestamp", "id", "strava_id", "first_name", "last_name",
"intania", "ranger", "created_at"
]
writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
writer.writeheader()
n_user = 0
for user in users:
row = vars(user)
row["timestamp"] = timestamp
row["created_at"] = row["created_at"].strftime(TIME_STRING_FORMAT)
# Customise intania and ranger fields
if user.clubs:
row["intania"] = user.clubs[0].intania
else:
row["intania"] = ""
if user.registration and user.registration.foundation:
row["ranger"] = user.registration.foundation.name
else:
row["ranger"] = ""
# Filter only wanted fields
row = {key: row[key] for key in fieldnames if key in row}
writer.writerow(row)
n_user += 1
print("Total Runners:", n_user)
print("Generated report to", report_path)
def update_run_spread_ranger(challenge_spread, spread_key, sheet_name):
rows = ChallengeSqlDB.get_summary_ranger_distance()
run_data = []
for row in rows:
# row.total_distance type is Decimal
run_data.append((row.name, int(row.total_distance) / 1000.0,
row.total_user, row.total_run))
challenge_spread.update_summary_run(spread_key, sheet_name, run_data)
def update_runner_spread_intania(challenge_spread, spread_key, sheet_name):
users = ChallengeSqlDB.get_all_intania_users()
runner_data = []
for user in users:
if user.clubs:
intania = user.clubs[0].intania
else:
intania = "N/A"
runner_data.append((user.first_name, user.last_name, intania))
challenge_spread.update_runner(spread_key, sheet_name, runner_data)
def gen_run_report(timestamp, report_path):
runs = ChallengeSqlDB.get_all_runs()
with open(report_path, "w", newline="") as csvfile:
fieldnames = [
"timestamp", "start_date", "start_date_local", "strava_id", "name",
"distance", "moving_time", "elapsed_time", "elev_high", "elev_low",
"total_elevation_gain", "manual", "promo_comment",
"promo_multiplier", "user_strava_id", "user_first_name",
"user_last_name", "user_intania", "user_ranger", "created_at"
]
writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
writer.writeheader()
n_run = 0
for run in runs:
row = vars(run)
row["timestamp"] = timestamp
row["start_date"] = row["start_date"].strftime(TIME_STRING_FORMAT)
row["start_date_local"] = row["start_date_local"].strftime(
TIME_STRING_FORMAT)
row["created_at"] = row["created_at"].strftime(TIME_STRING_FORMAT)
# Customise user info
user = run.user
row["user_strava_id"] = user.strava_id
row["user_first_name"] = user.first_name
row["user_last_name"] = user.last_name
if user.clubs:
row["user_intania"] = user.clubs[0].intania
else:
row["user_intania"] = ""
if user.registration and user.registration.foundation:
row["user_ranger"] = user.registration.foundation.name
else:
row["user_ranger"] = ""
# Filter only wanted fields
row = {key: row[key] for key in fieldnames if key in row}
writer.writerow(row)
n_run += 1
print("Total Runs:", n_run)
print("Generated report to", report_path)
# Update in batch
worksheet.update_cells(cell_list, "USER_ENTERED")
# Reuired environment
TIME_STRING_FORMAT = "%Y-%m-%d %H:%M:%S"
DEFAULT_OUTPUT_DIR = "./"
MYSQL_HOST = os.environ["MYSQL_HOST"]
MYSQL_USERNAME = os.environ["MYSQL_USERNAME"]
MYSQL_PASSWORD = os.environ["MYSQL_PASSWORD"]
MYSQL_DB_NAME = os.environ["MYSQL_DB_NAME"]
ChallengeSqlDB.init(MYSQL_HOST, MYSQL_USERNAME, MYSQL_PASSWORD, MYSQL_DB_NAME)
def update_runner_spread_intania(challenge_spread, spread_key, sheet_name):
users = ChallengeSqlDB.get_all_intania_users()
runner_data = []
for user in users:
if user.clubs:
intania = user.clubs[0].intania
else:
intania = "N/A"
runner_data.append((user.first_name, user.last_name, intania))
challenge_spread.update_runner(spread_key, sheet_name, runner_data)
def main():
ChallengeSqlDB.init(MYSQL_HOST, MYSQL_USERNAME, MYSQL_PASSWORD,
MYSQL_DB_NAME)
print("Challenge start date:", CHALLENGE_START_DATE)
print("Challenge end date:", CHALLENGE_END_DATE)
# Read all runners that have intania from DB
print("Get all runners from db")
if SERVICE_TYPE.lower() == 'ranger':
print("Service type: %s" % ('RANGER'))
users = ChallengeSqlDB.get_all_ranger_users()
else:
print("Service type: %s" % ('INTANIA'))
users = ChallengeSqlDB.get_all_intania_users()
n_user = len(users)
print("Total runners: %d" % (n_user))
# For each runners get their activities
runs = []
for idx, user in enumerate(users):
# if user.clubs is None or not user.clubs:
# print("Skip runner with None intania: id=%s displayname='%s %s'" %
# (user.strava_id, user.first_name, user.last_name))
# continue
if not user.credentials:
print(
"Skip runner with empty credentials: id=%s displayname='%s %s'"
% (user.strava_id, user.first_name, user.last_name))
continue
refresh_token = None
for cred in user.credentials:
if cred.strava_client == CLIENT_ID:
refresh_token = cred.strava_refresh
if refresh_token is None:
print(
"Skip runner with empty credentials for client_id=%s : id=%s displayname='%s %s'"
% (CLIENT_ID, user.strava_id, user.first_name, user.last_name))
continue
print('Found refresh_token for the user ...')
try:
client = Client()
# Get new access token
refresh_response = client.refresh_access_token(
client_id=CLIENT_ID,
client_secret=CLIENT_SECRET,
refresh_token=refresh_token)
# Set up user's access token and ready to fetch Strava data
client.access_token = refresh_response['access_token']
except Exception as e:
continue
if user.clubs:
intania = user.clubs[0].intania
else:
intania = 0
if user.registration and user.registration.foundation:
ranger_team = user.registration.foundation.name
else:
ranger_team = None
time.sleep(0.25)
activities = client.get_activities(after=CHALLENGE_START_DATE,
before=CHALLENGE_END_DATE)
print(
"Get activities: idx=%d/%d id=%s displayname='%s %s' intania='%s' ranger='%s'"
% (idx + 1, n_user, user.strava_id, user.first_name,
user.last_name, intania, ranger_team))
n_run = 0
try:
for act in activities:
if act.type not in [Activity.RUN, Activity.WALK]:
continue
n_run += 1
run = Run.from_activity(act, user.id)
# Adjust promo multiplier
adjust_run_promo(run)
# Try to save activity to DB
try:
if ChallengeSqlDB.get_run_by_strava_id(
run.strava_id) is None:
# New run activity
ChallengeSqlDB.insert_run(run)
except Exception as e:
ChallengeSqlDB.insert_run(run)
runs.append(run)
except Exception as e:
print(e)
print('Got run acitivities: %d' % (n_run))
print("Total run activities: %d" % (len(runs)))
def main():
ChallengeSqlDB.init(MYSQL_HOST, MYSQL_USERNAME, MYSQL_PASSWORD,
MYSQL_DB_NAME)
print("Get all runners from db")
users = ChallengeSqlDB.get_all_users()
intania_clubs = ChallengeSqlDB.get_all_intania_clubs()
# Map strava_id -> club.id
intania_clubs_dict = {}
for intania_club in intania_clubs:
intania_clubs_dict[intania_club.strava_id] = intania_club
# Find intania for
for user in users:
# User already has basic data in the database
print("User: strava_id=%s displayname='%s %s'" %
(user.strava_id, user.first_name, user.last_name))
if not user.credentials:
print(
"Skip runner with empty credentials: id=%s displayname='%s %s'"
% (user.strava_id, user.first_name, user.last_name))
continue
refresh_token = None
for cred in user.credentials:
if cred.strava_client == CLIENT_ID:
refresh_token = cred.strava_refresh
if refresh_token is None:
print(
"Skip runner with empty credentials for client_id=%s : id=%s displayname='%s %s'"
% (CLIENT_ID, user.strava_id, user.first_name, user.last_name))
continue
print('Found refresh_token for the user ...')
client = Client()
# Get new access token
refresh_response = client.refresh_access_token(
client_id=CLIENT_ID,
client_secret=CLIENT_SECRET,
refresh_token=refresh_token)
# Set up user's access token and ready to fetch Strava data
client.access_token = refresh_response['access_token']
# stravalib.exc.RateLimitExceeded
try:
athlete = client.get_athlete()
except Exception as e:
print('Error: failed to fetch Strava profile')
print(e)
continue
joined_clubs = athlete.clubs
if not (user.clubs is None or not user.clubs):
print("%s %s is in '%s' club, skip club update..." %
(user.first_name, user.last_name, user.clubs[0].name))
elif joined_clubs is None:
print(
"Error: failed to fetch clubs for %s %s, skip club update..." %
(user.first_name, user.last_name))
else:
for club in joined_clubs:
# print("id:", club.id, "Club name:", club.name)
club_strava_id = str(club.id)
if club_strava_id in intania_clubs_dict:
# update in database
intania_club = intania_clubs_dict[club_strava_id]
print('Update intania club (%s) for %s %s' %
(intania_club.name, user.first_name, user.last_name))
ChallengeSqlDB.update_user_intania(user.id,
intania_club.id)
# Update first & last name
try:
ChallengeSqlDB.update_user_name(user.id, athlete.firstname,
athlete.lastname)
except Exception as e:
print(
'Error: failed to update user entity: id=%d displayname=%s %s'
% (user.id, athlete.firstname, athlete.lastname))
print(e)
time.sleep(0.2)
def test_init(self, mysql_config):
host = mysql_config['host']
username = mysql_config['username']
password = mysql_config['password']
ChallengeSqlDB.init(host, username, password)