def test():
creds = db.execute("SELECT user_id, token, refresh_token, token_uri, client_id, client_secret, scopes FROM credentials WHERE user_id=?", session["user_id"])
creds = credentials_to_dict(creds[0])
for test in creds:
print(test)
print(creds[test])
print(type(creds[test]))
return redirect ("/")
def export():
if request.method == "POST" or session.get("event_id"):
# Get the event_id
if session.get("event_id"):
ID = session["event_id"]
start = session["start"]
end = session["end"]
del session["event_id"]
del session["start"]
del session["end"]
else:
try:
ID = request.form.get("id")
start = request.form.get("start")
end = request.form.get("end")
except:
return render_template("apology.html", message="Invalid Format")
# Only the host should be able to finalize an event
host = db.execute("SELECT * FROM members WHERE event_id=? AND user_id=? AND host=1",
ID, session["user_id"])
if len(host) == 0:
flash("Only the host can finalize an event")
return redirect("/")
# Get the credentials
creds = db.execute(
"SELECT token, refresh_token, token_uri, client_id, client_secret, scopes FROM credentials WHERE user_id=?", session["user_id"])
if len(creds) != 1:
session["next"] = flask.url_for("export")
session["event_id"] = request.form.get("id")
session["start"] = request.form.get("start")
session["end"] = request.form.get("end")
return flask.redirect('authorize')
creds = credentials_to_dict(creds[0])
# Load credentials from the session.
credentials = google.oauth2.credentials.Credentials(**creds)
# Save credentials back to the database in case access token was refreshed.
update_credentials(credentials, session["user_id"])
# Build the API service
service = googleapiclient.discovery.build(API_SERVICE_NAME, API_VERSION,
credentials=credentials, cache_discovery=False, developerKey=API_KEY)
members = db.execute(
"SELECT * FROM users WHERE id IN (SELECT user_id FROM members JOIN events ON members.event_id=events.id WHERE events.id=?)", ID)
event = db.execute("SELECT * FROM events WHERE id=?", ID)[0]
# Create a list of dictionaries with emails associated to the key "email"
emails = []
for member in members:
tmp = {}
tmp["email"] = member["email"]
if member["id"] == session["user_id"]:
tmp["organizer"] = True
emails.append(tmp)
if start.find("T") != -1:
# Form the https request body (template from https://developers.google.com/calendar/v3/reference/events/insert)
calendar_event = {
'creator': {
'self': True
},
'organizer': {
'self': True
},
'summary': event["name"],
'start': {
'dateTime': start,
},
'end': {
'dateTime': end,
},
'attendees': emails,
'reminders': {
'useDefault': False,
'overrides': [
{'method': 'email', 'minutes': 24 * 60},
{'method': 'popup', 'minutes': 10},
],
},
}
else:
tz = service.settings().get(setting='timezone').execute()["value"]
# Form the https request body (template from https://developers.google.com/calendar/v3/reference/events/insert)
calendar_event = {
'creator': {
'self': True
},
'organizer': {
'self': True
},
'summary': event["name"],
'start': {
'date': start,
'timezone': tz,
},
'end': {
'date': end,
'timezone': tz,
},
'attendees': emails,
'reminders': {
'useDefault': False,
'overrides': [
{'method': 'email', 'minutes': 24 * 60},
{'method': 'popup', 'minutes': 10},
],
},
}
# Add the event to the google calendar
service.events().insert(calendarId='primary', body=calendar_event, sendUpdates="all").execute()
# Update the event in the database to show that is finalized
db.execute("UPDATE events SET start=?, end=? WHERE id=?", start, end, event["id"])
return redirect(flask.url_for("view", id=event["id"]))
# Check valid event
event = db.execute("SELECT * FROM events WHERE id=?", request.args.get("id"))
if len(event) == 0:
return render_template("apology.html", message="Invalid event.")
event = event[0]
conflicts = db.execute(
"SELECT * FROM conflicts WHERE id IN (SELECT conflict_id FROM event_conflicts WHERE event_id=?)", request.args.get("id"))
members = db.execute(
"SELECT * FROM users WHERE id IN (SELECT members.user_id FROM members JOIN events ON events.id=members.event_id WHERE events.id=?)", event["id"])
# Only the host should be able to finalize an event
host = db.execute("SELECT * FROM members WHERE event_id=? AND user_id=? AND host=1",
request.args.get("id"), session["user_id"])
if len(host) == 0:
flash("Only the host can finalize an event")
return redirect("/")
tz = get_timezone(event["timezone"])
# Check if the event is an all/multiple day event
if request.args.get("event_date") != None:
try:
# Convert the dates and times to python date and datetime objects
date = request.args.get("event_date").split("/")
event_date = datetime.date(int(date[2]), int(date[0]), int(date[1]))
start_time = datetime.time.fromisoformat(str(int(request.args.get(
"start_time_hours")) + int(request.args.get("start_time_noon"))).zfill(2) + ":" + request.args.get("start_time_minutes").zfill(2))
end_time = datetime.time.fromisoformat(str(int(request.args.get(
"end_time_hours")) + int(request.args.get("end_time_noon"))).zfill(2) + ":" + request.args.get("end_time_minutes").zfill(2))
start_datetime = datetime.datetime.combine(event_date, start_time, tz)
end_datetime = datetime.datetime.combine(event_date, end_time, tz)
except:
return render_template("apology.html", message="Please enter an event date, start time and end time.")
if start_time >= end_time:
return render_template("apology.html", message="Start time cannot be after end time.")
# find_conflicts returns a set of members who are unavailable during the given time period
unavailable = find_conflicts(start_datetime, end_datetime, conflicts, tz)
# Create a set of members who are available
available = set()
for member in members:
if member["id"] not in unavailable:
available.add(member["name"])
else:
unavailable.add(member["name"])
unavailable.remove(member["id"])
return render_template("export.html", event_date=event_date.strftime("%A, %B %d, %Y"), start_time=start_time.strftime("%I:%M %p"), end_time=end_time.strftime("%I:%M %p"), available=list(available), unavailable=list(unavailable), name=event["name"], start=start_datetime.isoformat(), end=end_datetime.isoformat(), ID=event["id"])
else:
try:
# Convert the dates to python date objects
date = request.args.get("event_start_date").split("/")
start_date = datetime.date(int(date[2]), int(date[0]), int(date[1]))
date = request.args.get("event_end_date").split("/")
end_date = datetime.date(int(date[2]), int(date[0]), int(date[1]))
except:
return render_template("apology.html", message="Incorrect date format")
if start_date >= end_date:
return render_template("apology.html", message="Start date cannot be after end date")
# find_conflicts returns a set of members who are unavailable during the given dates
unavailable = find_conflicts_allday(start_date, end_date, conflicts, tz)
# Create a set of members who are available
available = set()
for member in members:
if member["id"] not in unavailable:
available.add(member["name"])
else:
unavailable.add(member["name"])
unavailable.remove(member["id"])
return render_template("export.html", start_date=start_date.strftime("%A, %B %d, %Y"), end_date=end_date.strftime("%A, %B %d, %Y"), available=list(available), unavailable=list(unavailable), name=event["name"], start=start_date.isoformat(), end=end_date.isoformat(), ID=event["id"])
def view():
if request.method == "POST": #AKA If they submit the form to add their GCal...
creds = db.execute("SELECT token, refresh_token, token_uri, client_id, client_secret, scopes FROM credentials WHERE user_id=?", session["user_id"])
if len(creds) != 1:
return flask.redirect('authorize')
creds = credentials_to_dict(creds[0])
# Load credentials from the session.
credentials = google.oauth2.credentials.Credentials(
**creds)
# Save credentials back to session in case access token was refreshed.
# ACTION ITEM: In a production app, you likely want to save these
# credentials in a persistent database instead.
update_credentials(credentials, session["user_id"])
service = googleapiclient.discovery.build(
API_SERVICE_NAME, API_VERSION, credentials=credentials, cache_discovery=False)
# Gather all calendar IDs
calendars = []
page_token = None
while True:
calendar_list = service.calendarList().list(pageToken=page_token).execute()
for calendar_list_entry in calendar_list['items']:
calendars.append(calendar_list_entry['id'])
page_token = calendar_list.get('nextPageToken')
if not page_token:
break
event_row = db.execute("SELECT * FROM events WHERE id=?", request.form.get("id"))
if len(event_row) == 0:
flash("Not a valid event")
return redirect("/")
start_date = event_row[0]["start_date"] + "T00:00:00" + event_row[0]["timezone"]
end_date = event_row[0]["end_date"] + "T23:59:59" + event_row[0]["timezone"]
for calendar in calendars:
events_result = service.events().list(calendarId=calendar, timeMax=end_date, timeMin=start_date, singleEvents=True, orderBy='startTime', timeZone=event_row[0]["timezone"]).execute()
events = events_result.get('items', [])
# "start": { # The (inclusive) start time of the event. For a recurring event, this is the start time of the first instance.
# "dateTime": "A String", # The time, as a combined date-time value (formatted according to RFC3339). A time zone offset is required unless a time zone is explicitly specified in timeZone.
# "date": "A String", # The date, in the format "yyyy-mm-dd", if this is an all-day event.
# "timeZone": "A String", # The time zone in which the time is specified. (Formatted as an IANA Time Zone Database name, e.g. "Europe/Zurich".) For recurring events this field is required and specifies the time zone in which the recurrence is expanded. For single events this field is optional and indicates a custom time zone for the event start/end.
# },
for event in events:
row = db.execute("SELECT * FROM conflicts WHERE id=?", event["id"])
if len(row) == 0:
start = event['start'].get('dateTime')
if start == None:
start = event['start'].get('date') + "T00:00:00"
else:
start = start[:19]
end = event['end'].get('dateTime')
if end == None:
end = event['end'].get('date') + "T00:00:00"
else:
end = end[:19]
# Put event into database
db.execute("INSERT INTO conflicts (event_id, user_id, start_time, end_time, id) VALUES(?,?,?,?,?)", event_row[0]['id'], session["user_id"], start, end, event["id"])
return redirect(flask.url_for("view", id=request.form.get("id")))
event = db.execute("SELECT * FROM events WHERE id=? AND id IN (SELECT event_id FROM members JOIN users ON members.user_id=users.id WHERE users.id=?)", request.args.get("id"), session["user_id"])
if len(event) == 0:
flash("Not a valid event")
return redirect("/")
# Have to pass in the join url
# Have to pass through the best event times by GET
rows = db.execute("SELECT * FROM members WHERE event_id=? AND user_id=? AND host=1", event[0]["id"], session["user_id"])
# rows will only exist if host exists
if len(rows) != 0:
host = True
else:
host = False
# Determine the best times
conflicts = db.execute("SELECT * FROM conflicts WHERE event_id=?", request.args.get("id"))
# Stores number of conflicts for each time interval we have
num_conflicts = {}
# Creates a start_date date object (Y, M, D)
start_date = datetime.date.fromisoformat(event[0]["start_date"])
# Same as start_date
end_date = datetime.date.fromisoformat(event[0]["end_date"])
# For while loop to increment the date
date = start_date
# 10 minute intervals
interval = 10
delta = datetime.timedelta(minutes=interval)
# The starting time boundary for the event (earliest the event can start on a given day)
start_time = datetime.time.fromisoformat(event[0]["start_time"])
# The ending time boundary for the event (latest time an event can end)
end_time = datetime.time.fromisoformat(event[0]["end_time"])
# Store duration of the event
duration = datetime.timedelta(minutes=int(event[0]["duration"]))
# For each day
while date <= end_date:
# start time of the interval
dtime = datetime.datetime.combine(date, start_time)
# max value for dtime
end = datetime.datetime.combine(date, end_time) - duration
# For each datetime incrementation
while dtime < end:
num_conflicts[dtime] = 0
for conflict in conflicts:
# logic from https://stackoverflow.com/questions/13513932/algorithm-to-detect-overlapping-periods
if datetime.datetime.fromisoformat(conflict["start_time"]) < dtime + duration and datetime.datetime.fromisoformat(conflict["end_time"]) > dtime:
num_conflicts[dtime] += 1
dtime += delta
date += datetime.timedelta(days=1)
print(num_conflicts)
return render_template("view.html", event=event[0], host=host)
def view():
""" View an event """
# If the user is attempting to import their google calendar
if request.method == "POST" or session.get("event_id"):
# Get the event_id
if session.get("event_id"):
ID = session["event_id"]
del session["event_id"]
else:
try:
ID = request.form.get("id")
except:
return render_template("apology.html", message="Invalid Event")
# Check that the event ID is valid
event = db.execute("SELECT * FROM events WHERE id=? AND id IN (SELECT event_id FROM members JOIN users ON members.user_id=users.id WHERE users.id=?)",
ID, session["user_id"])
if len(event) == 0:
flash("Not a valid event")
return redirect("/")
event = event[0]
# Grab the google credentials
creds = db.execute(
"SELECT token, refresh_token, token_uri, client_id, client_secret, scopes FROM credentials WHERE user_id=?", session["user_id"])
if len(creds) != 1:
session["next"] = flask.url_for("view", id=event["id"])
session["event_id"] = event["id"]
return flask.redirect('authorize')
# Load credentials from the database
creds = credentials_to_dict(creds[0])
credentials = google.oauth2.credentials.Credentials(**creds)
# Save credentials back to the database in case access token was refreshed.
update_credentials(credentials, session["user_id"])
# Build the API requests object
service = googleapiclient.discovery.build(API_SERVICE_NAME, API_VERSION,
credentials=credentials, cache_discovery=False, developerKey=API_KEY)
# Gather all calendar IDs
calendars = []
page_token = None
while True:
calendar_list = service.calendarList().list(pageToken=page_token).execute()
for calendar_list_entry in calendar_list['items']:
calendars.append(calendar_list_entry['id'])
page_token = calendar_list.get('nextPageToken')
if not page_token:
break
# Get all calendar events between the beginning of the start_date and the end of the end_date
start_date = event["start_date"] + "T00:00:00" + event["timezone"]
end_date = event["end_date"] + "T23:59:59" + event["timezone"]
for calendar in calendars:
# Execute the https request to get all of the calendar events
events_result = service.events().list(calendarId=calendar, timeMax=end_date, timeMin=start_date,
singleEvents=True, orderBy='startTime', timeZone=event["timezone"]).execute()
cal_events = events_result.get('items', [])
# Add each conflict to the database
for cal_event in cal_events:
# Ensure that duplicate conflicts are not added to the database
row = db.execute("SELECT * FROM conflicts WHERE google_id=?", cal_event["id"])
if len(row) == 0:
start = cal_event['start'].get('dateTime')
# If the conflict is all day, format so that the conflict starts at 12 AM
if start == None:
start = cal_event['start'].get('date') + "T00:00:00" + event["timezone"]
end = cal_event['end'].get('dateTime')
# If the conflict is all day, format so that the conflict ends at 12PM
if end == None:
end = (datetime.date.fromisoformat(cal_event['end'].get(
'date')) - datetime.timedelta(days=1)).isoformat() + "T23:59:59" + event["timezone"]
# Put the conflict into database
conflict_id = db.execute(
"INSERT INTO conflicts (user_id, start_time, end_time, google_id) VALUES(?,?,?,?)", session["user_id"], start, end, cal_event["id"])
else:
conflict_id = row[0]["id"]
# Check that the conflict is not already associated with an event
event_conflict = db.execute("SELECT * FROM event_conflicts WHERE conflict_id=? AND event_id=?",
conflict_id, event["id"])
if len(event_conflict) == 0:
# Add the conflict into the event
db.execute("INSERT INTO event_conflicts (conflict_id, event_id) VALUES(?,?)",
conflict_id, event["id"])
# Update members to track that the user has imported their calendar
db.execute("UPDATE members SET imported=1 WHERE user_id=? AND event_id=?", session["user_id"], event["id"])
return redirect(flask.url_for("view", id=event["id"]))
# Check that the event ID is valid
event = db.execute("SELECT * FROM events WHERE id=? AND id IN (SELECT event_id FROM members JOIN users ON members.user_id=users.id WHERE users.id=?)",
request.args.get("id"), session["user_id"])
if len(event) == 0:
flash("Not a valid event")
return redirect("/")
event = event[0]
# Check if the user is the host
rows = db.execute("SELECT * FROM members WHERE event_id=? AND user_id=? AND host=1", event["id"], session["user_id"])
if len(rows) != 0:
host = True
else:
host = False
# Check if the event has been finalized
if event["start"] != None:
# Check if it is an all/multiple day event
if event["start"].find("T") != -1:
# Store a string representing the event date and times
event_period = datetime.datetime.fromisoformat(event["start"]).strftime(
"%A, %B %d, %Y from %I:%M %p") + " to " + datetime.datetime.fromisoformat(event["end"]).strftime("%I:%M %p")
else:
# Store a string representing only the event dates
event_period = datetime.date.fromisoformat(event["start"]).strftime(
"%A, %B %d, %Y") + " to " + datetime.date.fromisoformat(event["end"]).strftime("%A, %B %d, %Y")
return render_template("view.html", event=event, host=host, event_period=event_period)
conflicts = db.execute(
"SELECT * FROM conflicts WHERE id IN (SELECT conflict_id FROM event_conflicts WHERE event_id=?)", request.args.get("id"))
rows = db.execute(
"SELECT * FROM users WHERE id IN (SELECT members.user_id FROM members JOIN events ON events.id=members.event_id WHERE events.id=?)", event["id"])
# Create a dictionary of IDs to names so that we can use it translate IDs to names
names = {}
for row in rows:
names[row["id"]] = row["name"]
# Create a list of members who have not imported their calendar
not_imported = db.execute(
"SELECT DISTINCT(name) FROM users JOIN members ON users.id=members.user_id WHERE members.event_id=? AND imported=0", event["id"])
if len(not_imported) == 0:
not_imported = False
else:
for i in range(len(not_imported)):
not_imported[i] = not_imported[i]["name"]
# Create a dictionary with keys as dates/datetimes and values as a set of people who cannot make the event at that time
unavailable = {}
# Check if this is an all/multiple day event
if event["start_time"] == None:
# best_times_allday returns a dictionary with keys as dates and values as a set of IDs
# We convert the dates to ISO format and the IDs to names
people = best_times_allday(event, conflicts)
print(people)
for time in sorted(people):
date = datetime.date(time.year, time.month, time.day).isoformat()
if date not in unavailable:
unavailable[date] = []
period = {}
period["start"] = time.strftime("%A, %B %d, %Y")
period["end"] = (time + datetime.timedelta(days=int(event["duration"]))).strftime("%A, %B %d, %Y")
period["people"] = []
for user_id in people[time]:
period["people"].append(names[user_id])
unavailable[date].append(period)
print(unavailable)
return render_template("view.html", event=event, host=host, unavailable=unavailable, names=names.values(), not_imported=not_imported)
else:
if request.args.get("interval") == None or request.args.get("interval") == "":
# Default value for interval
interval = int(math.ceil(int(event["duration"]) / 120) * 10)
else:
try:
interval = int(request.args.get("interval"))
except:
return render_template("apology.html", message="Invalid duration")
# best_times returns a dictionary with keys as datetimes and values as a set of IDs
people = best_times(event, conflicts, interval)
# Check if the search request is valid
if request.args.get("max_events") == None or request.args.get("start_time_hours") == None or request.args.get("start_time_minutes") == None or request.args.get("start_time_noon") == None or request.args.get("max_events") == "" or request.args.get("start_time_hours") == "" or request.args.get("start_time_minutes") == "" or request.args.get("start_time_noon") == "":
# We sort the dictionary by the best availability on each day and then convert the datetimes to ISO format and the IDs to name
for time in sorted(people, key=lambda key: (len(people[key]), key)):
date = datetime.date(time.year, time.month, time.day).isoformat()
if date not in unavailable:
unavailable[date] = []
period = {}
period["start"] = time.strftime("%I:%M %p")
period["end"] = (time + datetime.timedelta(minutes=int(event["duration"]))).strftime("%I:%M %p")
period["people"] = []
for user_id in people[time]:
period["people"].append(names[user_id])
unavailable[date].append(period)
return render_template("view.html", event=event, host=host, unavailable=unavailable, names=names.values(), search=True, not_imported=not_imported)
else:
try:
start_time = datetime.timedelta(hours=int(request.args.get(
"start_time_hours")) + int(request.args.get("start_time_noon")), minutes=int(request.args.get("start_time_minutes")))
max_events = int(request.args.get("max_events"))
except:
return render_template("apology", message="Invalid search query.")
# Create a list of length max_events with the events closest in time to start_time
sort = []
# We sort the dictionary first be availability and then by closeness to start_time and then we add the best events to sort
for time in sorted(people, key=lambda key: (len(people[key]), abs(datetime.timedelta(hours=key.hour, minutes=key.minute) - start_time))):
period = {}
period["start"] = time.strftime("%I:%M %p %A, %B %d, %Y")
period["end"] = (time + datetime.timedelta(minutes=int(event["duration"]))).strftime("%I:%M %p %A, %B %d, %Y")
period["people"] = []
for user_id in people[time]:
period["people"].append(names[user_id])
sort.append(period)
if len(sort) >= max_events:
break
return render_template("view.html", event=event, host=host, sort=sort, names=names.values(), search=True, not_imported=not_imported)