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