def test_add_to_roster(client, app, auth):
auth.login()
assert client.get('/auth/add_to_roster').status_code == 200
response = auth.add_to_roster('testOrg1', 'test')
assert response.headers['Location'] == 'http://localhost/'
with app.app_context():
db = get_db()
cur = db.cursor()
cur.execute('SELECT * FROM roster WHERE org_id = 2')
result = cur.fetchone()
assert result is not None
#Test that new member can make a avail slot
# make an avail request as old user
auth.make_avail_request(org_id=2)
cur.execute('''
SELECT * FROM availability_request
''')
result = cur.fetchone()
assert result is not None
# log in new user
client.get('/logout')
auth.login('other', 'test')
# join testOrg and make an availability slot
response = auth.add_to_roster('testOrg1', 'test')
# test that new member has succesfully joined the org
db = get_db()
cur = db.cursor()
cur.execute('SELECT * FROM roster WHERE member_id = 2 AND org_id = 2')
result = cur.fetchone()
assert result is not None
assert client.get('/1/org_page').status_code == 200
cur.execute('SELECT * FROM member_request WHERE member_id = 2')
result = cur.fetchone()
assert result is not None
auth.add_avail_slot()
cur.execute('''
SELECT * FROM availability_slot
''')
result = cur.fetchone()
assert result is not None
def register():
if request.method == 'POST':
username = request.form['username']
password = request.form['password']
db = get_db()
cur = db.cursor()
error = None
if not username:
error = 'Username is required.'
elif not password:
error = 'Password is required.'
else:
cur.execute('SELECT member_id FROM member WHERE username = %s',
(username, ))
result = cur.fetchone()
if result is not None:
error = 'User {} is already registered.'.format(username)
if error is None:
cur.execute(
'INSERT INTO member (username, password) VALUES (%s, %s)',
(username, generate_password_hash(password)))
db.commit()
return redirect(url_for('auth.login'))
flash(error)
return render_template('auth/register.html')
def get_member_orgs(member_id):
'''create a lis of dicts with information from all of the organizations a member
belongs to to be displayed on the home page. Each dict contains the keys
'org_name' and 'org_id' '''
db = get_db()
cur = db.cursor()
cur.execute(
'''
SELECT
organization.org_name,
organization.org_id
FROM organization
WHERE organization.org_id
IN (
SELECT roster.org_id FROM roster WHERE member_id = %s
)
''', (member_id, ))
orgs_from_db = cur.fetchall()
orgs = []
for o in orgs_from_db:
org = {}
org['org_name'] = o[0]
org['org_id'] = o[1]
orgs.append(org)
return orgs
def login():
if request.method == 'POST':
username = request.form['username']
password = request.form['password']
db = get_db()
cur = db.cursor()
error = None
cur.execute('SELECT * FROM member WHERE username = %s', (username, ))
member = cur.fetchone()
member_password = None
if member is None:
error = 'Incorrect username.'
else:
member_password = member[2]
if member_password is not None and not check_password_hash(
member_password, password):
error = 'Incorrect password.'
if error is None:
session.clear()
session['member_id'] = member[0]
return redirect(url_for('index'))
flash(error)
return render_template('auth/login.html')
def test_org_page(auth, client, app):
auth.login()
auth.add_to_roster('testOrg1', 'test')
auth.make_avail_request(org_id=2)
response = client.get('/2/org_page')
assert response.status_code == 200
assert b'testAR' in response.data
assert b'Not completed' in response.data #no availability slots have been filled
auth.add_avail_slot()
auth.book()
response = client.get('/2/org_page')
assert response.status_code == 200
assert b'testAR' in response.data
assert b'Completed' in response.data #all availability slots have been filled
assert b'from 1/1/2030 10:00AM ' in response.data #test time conversions
assert b'until 1/1/2030 1:00PM' in response.data
assert b'America/Los_Angeles' in response.data
response = client.get('/99/org_page')
assert response.headers['Location'] == 'http://localhost/'
auth.make_avail_request()
with app.app_context():
db = get_db()
cur = db.cursor()
cur.execute(
"SELECT * FROM availability_request WHERE avail_request_name ='testAR'",
)
result = cur.fetchone()
assert result is not None
def test_delete_booked_date(auth, client, app):
auth.login()
auth.make_avail_request()
auth.book()
assert client.get('/1/book').status_code == 200
with app.app_context():
db = get_db()
cur = db.cursor()
cur.execute('''
SELECT *
FROM booked_date
WHERE booked_date.booked_date_id = 1
''')
result = cur.fetchone()
assert result is not None
request = client.get('/1/delete_booked_date')
assert request.headers['location'] == 'http://localhost/1/org_page'
cur.execute('''
SELECT *
FROM booked_date
WHERE booked_date.booked_date_id = 1
''')
result = cur.fetchone()
assert result is None
def test_delete_avail_slot(auth, client, app):
auth.login()
auth.make_avail_request()
auth.add_avail_slot()
assert client.get('/1/avail_request').status_code == 200
with app.app_context():
db = get_db()
cur = db.cursor()
cur.execute('''
SELECT *
FROM availability_slot
WHERE availability_slot.avail_slot_id = 1
''')
result = cur.fetchone()
assert result is not None
request = client.get('/1/delete_avail_slot')
# test for correct redirect
assert request.headers[
'location'] == 'http://localhost/1/avail_request'
# avail slot should not be deleted
cur.execute('''
SELECT *
FROM availability_slot
WHERE availability_slot.avail_slot_id = 1
''')
result = cur.fetchone()
assert result is None
def test_delete_availability_request(auth, client, app):
auth.login()
auth.make_avail_request()
assert client.get('/1/avail_request').status_code == 200
with app.app_context():
db = get_db()
cur = db.cursor()
cur.execute('''
SELECT *
FROM availability_request
WHERE availability_request.avail_request_id = 1
''')
result = cur.fetchone()
assert result is not None
response = client.get('/1/delete_avail_request')
assert response.headers['location'] == 'http://localhost/1/org_page'
cur.execute('''
SELECT *
FROM availability_request
WHERE availability_request.avail_request_id = 1
''')
result = cur.fetchone()
assert result is None
def update_availability_requests_by_org(org_id):
'''delete all availability requests that are older than
datetime.utcnow() and update availability request session data for
all availability requests associated with an organization'''
db = get_db()
cur = db.cursor()
# delete old availability requests
cur.execute(
'''
DELETE FROM availability_request
WHERE availability_request.avail_request_id
IN (
SELECT availability_request.avail_request_id
FROM availability_request
WHERE availability_request.org_id = %s
)
AND availability_request.end_request < %s
''',
(org_id, datetime.utcnow())
)
db.commit()
#update session data
session['avail_requests'] = get_avail_requests_data(session['member_id'])
session['org_avail_requests'] = get_org_avail_requests(org_id)
session.modified = True
def check_if_complete(avail_request_id):
''' check if an availability request has been answered by all
members of its associated organization. '''
db = get_db()
cur = db.cursor()
cur.execute(
'''
SELECT answered FROM member_request WHERE member_request.avail_request_id = %s
''',
(avail_request_id,)
)
answered_list = cur.fetchall()
for answer in answered_list:
if answer[0] == 0:
return False #there is at least one member who has not responded to the request
#all members have answered so availability request is complete, update the database
cur.execute(
'''
UPDATE availability_request SET completed = TRUE
WHERE availability_request.avail_request_id = %s
''',
(avail_request_id,)
)
db.commit()
return True
def get_roster(org_id):
'''get a list of all member_id's associated with an organization'''
db = get_db()
cur = db.cursor()
#TODO: add member_id to this and make a dict
cur.execute(
'''
SELECT member.username, member.member_id
FROM member
WHERE member.member_id
IN(
SELECT roster.member_id
FROM roster
WHERE roster.org_id = %s
)
''', (org_id, ))
roster_from_db = cur.fetchall()
roster = []
for r in roster_from_db:
member = {}
member['username'] = r[0]
member['member_id'] = r[1]
roster.append(member)
return roster
def update_availability_requests_by_member(member_id):
'''delete all of the availability requests associated that are
older than datetime.utcnow() and update the session data for all
availability requests associated with a member '''
db = get_db()
cur = db.cursor()
# delete old availability requests
cur.execute(
'''
DELETE FROM availability_request
WHERE availability_request.avail_request_id
IN (
SELECT member_request.avail_request_id
FROM member_request
WHERE member_request.member_id = %s
)
AND availability_request.end_request < %s
''',
(member_id, datetime.utcnow())
)
db.commit()
#update session data
session['avail_requests'] = get_avail_requests_data(member_id)
if session.get('active_org') is not None:
session['org_avail_requests'] = get_org_avail_requests(session['active_org']['org_id'])
session.modified = True
def test_update_booked_dates_by_member(app, auth, client):
org_id = 1
timezone = "America/Los_Angeles"
with app.app_context():
db = get_db()
cur = db.cursor()
auth.login()
auth.make_avail_request()
# book a date that is not old
auth.book(start_date='1/1/2030',
start_time='6:00a',
end_date='1/1/2030',
end_time='7:00a')
# check that booked date was added
cur.execute('''
SELECT * FROM booked_date
WHERE booked_date_id = 1
''')
result = cur.fetchone()
assert result is not None
client.get('/')
# booked date was not old so should not be deleted
cur.execute('''
SELECT * FROM booked_date
WHERE booked_date_id = 1
''')
result = cur.fetchone()
assert result is not None
# book an old date
auth.book(start_date='1/1/2019',
start_time='6:00a',
end_date='1/1/2019',
end_time='7:00a')
# home page has not been visited so this should not be deleted
cur.execute('''
SELECT * FROM booked_date
WHERE booked_date_id = 2
''')
result = cur.fetchone()
assert result is not None
client.get('/')
# booked date was old so it should be deleted
cur.execute('''
SELECT * FROM booked_date
WHERE booked_date_id = 2
''')
result = cur.fetchone()
assert result is None
def org_page(org_id):
'''display organization information and allow user to create an
availability request'''
db = get_db()
# ensure that member is in the organization
if check_org_membership(org_id) == False:
flash(
"You are not in the organization, ask the organization for the password to join"
)
return redirect(url_for('index'))
# get data from the organization
session['active_org'] = get_org_info(org_id)
session['roster'] = get_roster(org_id)
# delete old avail_requests and booked dates and get session data
update_availability_requests_by_org(org_id)
update_booked_dates_by_org(org_id)
# NOTE: At the moment grabbing this information is redundant because all of
# the availability requests for this organization is stored in
# session['availability_request'] and session['booked_dates']. However we are
# getting this information seperately both because it is organizationally consistent
# with the organization of the app and because in the future we might have a scenario
# where we want there to be availability requests and booked dates that
# do not include all members of an organization
if request.method == 'POST':
#get availability request data from form
tz = request.form['tz']
avail_request_name = request.form['avail_request_name']
start_date = request.form['start_date']
start_time = request.form['start_time']
end_date = request.form['end_date']
end_time = request.form['end_time']
flash(f'''Availability Request {avail_request_name} from {start_date}
starting at {start_time} to {end_date} ending at
{end_time} in {tz} being created...''')
# validate input
error = validate_availability_request_input(tz, avail_request_name,
start_date, start_time,
end_date, end_time)
if error is None:
# insert the availability request into the databse
insert_availability_request(org_id, tz, avail_request_name,
start_date, start_time, end_date,
end_time)
else:
flash(error)
return render_template(
'organization/org_page.html/',
common_timezones=common_timezones,
)
def insert_availability_request(
org_id,
tz,
avail_request_name,
start_date,
start_time,
end_date,
end_time
):
'''insert an availability request into the database'''
db = get_db()
cur = db.cursor()
# get datetime objects for start and end
start_request = return_datetime(start_date, start_time, tz)
end_request = return_datetime(end_date, end_time, tz)
# insert new avaiability request into the database
cur.execute(
''' INSERT INTO availability_request
(avail_request_name, start_request, end_request,
timezone, org_id, completed) VALUES (%s, %s, %s, %s, %s, %s)
RETURNING avail_request_id''',
(avail_request_name, start_request, end_request, tz, org_id, False)
)
# in order to add to member_request we need to avail_request_id of the
# availability request we just created
member_id = session.get('member_id')
avail_request_id = cur.fetchone()
#insert everyone in the organization into member_request
cur.execute(
'SELECT member_id FROM roster WHERE roster.org_id = %s',
(org_id,)
)
members_in_org = cur.fetchall()
for member in members_in_org:
org_member_id = member[0] #member_id of a member in the org
# insert this member and the avail_request id of the newly created
# availability request into member_id
cur.execute(
'''
INSERT INTO member_request (member_id, avail_request_id, answered)
VALUES (%s, %s, %s)
''',
(org_member_id, avail_request_id[0], False)
)
db.commit()
#update session data
session['avail_requests'] = get_avail_requests_data(session['member_id'])
session['org_avail_requests'] = get_org_avail_requests(session['active_org']['org_id'])
session.modified = True
def get_org_avail_requests(org_id):
'''create a list of dicts containing information from all of the
availability requests associated with a particular organization to be displayed
on the organization page. Each dict contains the keys 'avail_request_id',
'avail_request_name', 'completed' and 'members_not_answered' '''
db = get_db()
cur = db.cursor()
# get the desired fields from all
# availability requests associated with an organization
cur.execute(
'''
SELECT
availability_request.avail_request_id,
availability_request.avail_request_name,
availability_request.completed
FROM availability_request
WHERE availability_request.org_id = %s
''', (org_id, ))
org_avail_requests_from_db = cur.fetchall()
org_avail_requests = []
# create the dict object for each availability request and append them to the
# list that will be returned
for org_avail_request in org_avail_requests_from_db:
avail_request = {}
avail_request['avail_request_id'] = org_avail_request[0]
avail_request['avail_request_name'] = org_avail_request[1]
avail_request['completed'] = org_avail_request[2]
# get a list of all of the members who have not answered for the
cur.execute(
'''
SELECT member.username
FROM member
WHERE member.member_id
IN(
SELECT member_request.member_id FROM member_request
WHERE member_request.avail_request_id = %s
AND member_request.answered = FALSE
)
''', (org_avail_request[0], ))
members_not_answered_from_db = cur.fetchall()
members_not_answered = []
for member in members_not_answered_from_db:
members_not_answered.append(member[0])
avail_request['members_not_answered'] = members_not_answered
org_avail_requests.append(avail_request)
return org_avail_requests
def add_to_roster():
if request.method == 'POST':
org_name = request.form['org_name']
password = request.form['password']
db = get_db()
cur = db.cursor()
error = None
cur.execute('SELECT * FROM organization WHERE org_name = %s',
(org_name, ))
organization = cur.fetchone()
member_id = session['member_id']
org_pword = None
org_id = None
if organization is None:
error = 'This organization does not exist.'
else:
org_pword = organization[2]
org_id = organization[0]
cur.execute(
'SELECT * FROM roster WHERE org_id = %s AND member_id = %s',
(org_id, session['member_id']))
org_check = cur.fetchone()
if org_pword is not None and not check_password_hash(
org_pword, password):
error = 'Incorrect password.'
elif org_check is not None:
error = 'You are already in the organization.'
if error is None:
cur.execute(
'INSERT INTO roster (org_id, member_id) VALUES (%s, %s)', (
org_id,
member_id,
))
db.commit()
# update session data
session['active_org'] = get_org_info(org_id)
session['roster'] = get_roster(org_id)
session['org_avail_requests'] = get_org_avail_requests(org_id)
session.modified = True
#update member_request
update_member_request()
flash("join successful!")
return redirect(url_for('index'))
flash(error)
return render_template('auth/add_to_roster.html')
def load_logged_in_user():
member_id = session.get('member_id')
if member_id is None:
g.member = None
else:
db = get_db()
cur = db.cursor()
cur.execute('SELECT * FROM member WHERE member_id = %s', (member_id, ))
g.member = cur.fetchone()
def get_org_id_from_avail_request(avail_request_id):
db = get_db()
cur = db.cursor()
cur.execute(
'''
SELECT availability_request.org_id
FROM availability_request
WHERE availability_request.avail_request_id = %s
''', (avail_request_id, ))
org_id = cur.fetchone()[0]
return org_id
def check_org_membership(org_id):
'''check if a member is in an organization'''
db = get_db()
cur = db.cursor()
cur.execute('SELECT * FROM roster WHERE org_id = %s AND member_id = %s', (
org_id,
session['member_id'],
))
result = cur.fetchone()
if result is None: #member not contained in roster
return False
else:
return True
def delete_availability_slot(avail_slot_id):
'''Delete an availability slot from the database'''
db = get_db()
cur = db.cursor()
cur.execute(
'''
DELETE FROM availability_slot
WHERE availability_slot.avail_slot_id = %s
''',
(avail_slot_id,)
)
db.commit()
def remove_from_org(member_id, org_id):
'''Remove a member from an Organization'''
db = get_db()
cur = db.cursor()
# delete the member from the roster
cur.execute(
'''
DELETE FROM roster
WHERE member_id = %s
AND org_id = %s
''',
(member_id, org_id,)
)
# delete all availability slots made by the member for availability requests
# associated with the organization
cur.execute(
'''
DELETE FROM availability_slot
WHERE availability_slot.member_id = %s
AND availability_slot.avail_request_id
IN (
SELECT availability_request.avail_request_id
FROM availability_request
WHERE availability_request.org_id = %s
)
''',
(member_id, org_id,)
)
# delete all member request data for all availability requests associated with
# this member and this organization
cur.execute(
'''
DELETE FROM member_request
WHERE member_request.member_id = %s
AND member_request.avail_request_id
IN (
SELECT availability_request.avail_request_id
FROM availability_request
WHERE availability_request.org_id = %s
)
''',
(member_id, org_id,)
)
db.commit()
#update session object
session['roster'] = get_roster(org_id)
def update_member_request():
'''update member requests after a member joins an organization'''
db = get_db()
cur = db.cursor()
for avail_request in session['org_avail_requests']:
cur.execute(
'''
INSERT INTO member_request (member_id, avail_request_id, answered)
VALUES (%s, %s, %s)
''',
(session['member_id'], avail_request['avail_request_id'], 'FALSE')
)
db.commit()
def test_register(client, app):
assert client.get('/auth/register').status_code == 200
response = client.post('/auth/register',
data={
'username': '******',
'password': '******'
})
assert 'http://localhost/auth/login' == response.headers['Location']
with app.app_context():
db = get_db()
cur = db.cursor()
cur.execute("select * from member where username = '******'", )
result = cur.fetchone()
assert result is not None
def index():
'''display the organizations and availability requests associated with a member
if a member is logged in and allows for registration and member login if
a member is not logged in'''
db = get_db()
member_id = session.get('member_id')
if member_id is not None:
# delete old avail requests and booked dates and get session data
update_availability_requests_by_member(member_id)
update_booked_dates_by_member(member_id)
session['orgs'] = get_member_orgs(member_id)
session.modified = True
return render_template('member/index.html', )
def test_register_org(client, app, auth):
auth.login()
assert client.get('/auth/register_org').status_code == 200
response = client.post('/auth/register_org',
data={
'org_name': 'a',
'password': '******'
})
assert response.headers['Location'] == 'http://localhost/'
with app.app_context():
db = get_db()
cur = db.cursor()
cur.execute("SELECT * FROM organization WHERE org_name = 'a'", )
result = cur.fetchone()
assert result is not None
def get_org_info(org_id):
'''create a dict with keys org_id and org_name'''
db = get_db()
cur = db.cursor()
cur.execute(
'''SELECT organization.org_id, organization.org_name
FROM organization WHERE org_id = %s''', (org_id, ))
org_from_db = cur.fetchone()
org = {}
org['org_id'] = org_from_db[0]
org['org_name'] = org_from_db[1]
return org
def check_avail_request_membership(avail_request_id):
'''check if a user is associated with a partiular availability request'''
db = get_db()
cur = db.cursor()
cur.execute(
'SELECT * FROM member_request WHERE avail_request_id = %s AND member_id = %s',
(
avail_request_id,
session['member_id'],
))
result = cur.fetchone()
if result is None:
return False
else:
return True
def register_org():
''' Insert a new organization into the database. '''
if request.method == 'POST':
org_name = request.form['org_name']
password = request.form['password']
db = get_db()
cur = db.cursor()
error = None
if not org_name:
error = 'Organization name is required.'
elif not password:
error = 'Password is required.'
else:
cur.execute('SELECT org_id FROM organization WHERE org_name = %s',
(org_name, ))
result = cur.fetchone()
if result is not None:
error = '{} is already registered.'.format(org_name)
if error is None:
cur.execute(
'INSERT INTO organization (org_name, password) VALUES (%s, %s)',
(org_name, generate_password_hash(password)))
db.commit()
#automatically add current user to roster
cur.execute('SELECT org_id FROM organization WHERE org_name = %s',
(org_name, ))
org_id = cur.fetchone()[0]
member_id = session['member_id']
cur.execute(
'INSERT INTO roster (org_id, member_id) VALUES (%s, %s)', (
org_id,
member_id,
))
db.commit()
flash("New Organization registered")
return redirect(url_for('member.index'))
flash(error)
return render_template('auth/register_org.html')
def delete_booked_date(booked_date_id):
'''Delete a booked date from the database and update session'''
db = get_db()
cur = db.cursor()
cur.execute(
'''
DELETE FROM booked_date
WHERE booked_date.booked_date_id = %s
''',
(booked_date_id,)
)
db.commit()
# update session data
session['booked_dates'] = get_member_booked_dates(session['member_id'])
session['org_booked_dates'] = get_org_booked_dates(session['active_org']['org_id'])
session.modified = True
