def test_get_close_db(app):
with app.app_context():
db = get_db()
assert db is get_db()
with pytest.raises(sqlite3.ProgrammingError) as e:
db.execute('SELECT 1')
assert 'closed' in str(e)
def create(id=None):
db = get_db()
templates = db.execute('SELECT * FROM Templates T').fetchall()
if request.method == 'POST':
title = request.form['job_title']
body = request.form['job_desc']
qualifications = request.form['job_credentials']
begin_date = request.form['job_date_beg']
end_date = request.form['job_date_beg']
begin_time = request.form['job_time_beg']
end_time = request.form['job_time_end']
job_city = request.form['job_city']
job_state = request.form['job_state']
job_zip = request.form['job_zip']
error = None
if not title:
error = 'Job title is required.'
elif not body:
error = 'Job description is required.'
elif not begin_date or not end_date:
error = "Both Job Begin and End Date are required."
elif not begin_time or not end_time:
error = "Both Job Begin Time and Job End time are required."
elif not job_city or not job_state or not job_zip:
error = " A city, state and zip code are required for all jobs. "
if error is not None:
flash(error)
else:
db = get_db()
# g.user['u_id']
db.execute(
'INSERT INTO Job (job_title, job_desc, job_credentials, job_date_beg, job_date_end, job_time_beg, job_time_end, job_city, job_state, job_zip, m_id)'
' VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
(title, body, qualifications, begin_date, end_date,
begin_time, end_time, job_city, job_state, job_zip, 1)
)
# print(title, body, qualifications, begin_date, end_date,
# begin_time, end_time, job_city, job_state, job_zip, 1)
db.commit()
return redirect(url_for('dashboard.manager_dashboard'))
return render_template('dashboard/create.html', templates=templates)
def manager_dashboard():
db = get_db()
jobs = db.execute(
'SELECT * FROM Job j'
' ORDER BY created DESC'
).fetchall()
##########################################################################
# NEED TO VERIFY IF THE INFORMATION ON THE NOTIFICATIONS IS CORRECT!
#
# ALSO - need to do 'init-db' each time, else same set of notifications will get added to the table everytime the page is refreshed
##########################################################################
addNotifications = True
# MANAGERS
if addNotifications:
db.execute('INSERT INTO Notification (type, message, priority, created ) VALUES (?, ?, ? , ?)',
('manager', 'Joe David accepted Delivery Job #.', 'info', '2018-01-01 01:07:03'))
db.execute('INSERT INTO Notification (type, message, priority, created ) VALUES (?, ?, ? , ?)',
('manager', '2 Jobs scheduled for today are without employees.', 'alert', '2018-01-01 12:12:22'))
db.execute('INSERT INTO Notification (type, message, priority, created ) VALUES (?, ?, ? , ?)',
('manager', '3 Jobs for tomorrow are without employees.', 'warning', '2018-01-01 01:12:54'))
db.execute('INSERT INTO Notification (type, message, priority, created ) VALUES (?, ?, ? , ?)',
('manager', 'All scheduled jobs for today were successfully assigned.', 'success', '2018-01-01 02:01:01'))
db.commit()
addNotifications = False
notifications = db.execute('SELECT * FROM Notification n').fetchall()
return render_template('dashboard/manager_dashboard.html', jobs=jobs, notifications=notifications)
def app():
db_fd, db_path = tempfile.mkstemp()
app = create_app({
'TESTING': True,
'DATABASE': db_path,
})
with app.app_context():
init_db()
get_db().executescript(_data_sql)
yield app
os.close(db_fd)
os.unlink(db_path)
def login():
if request.method == 'POST':
db = get_db()
username = request.form['username']
password = request.form['password']
user_m_check = db.execute(
'SELECT * FROM Manager WHERE m_username = ?', (username,)).fetchone()
user_e_check = db.execute(
'SELECT * FROM Employee WHERE e_username = ?', (username,)).fetchone()
print("Manager: ", user_m_check)
print("Employee: ", user_e_check)
error = None
isManager = False
u_id = None
if user_m_check is not None:
user = user_m_check
u_id = user_m_check['m_id']
u_name = user_m_check['m_fullname']
isManager = True
elif user_e_check is not None:
user = user_e_check
u_id = user_e_check['e_id']
u_name = user_e_check['e_fullname']
isManager = False
elif user_m_check is None and user_e_check is None:
error = 'Incorrect username.'
elif not check_password_hash(user['password'], password):
error = 'Incorrect password.'
# if user is None:
# error = 'Incorrect username.'
# elif not check_password_hash(user['password'], password):
# error = 'Incorrect password.'
if error is None:
session.clear()
session['u_id'] = u_id
session['username'] = username
session['isManager'] = isManager
session['name'] = u_name
print("isManager:", isManager)
if isManager:
return redirect(url_for('dashboard.manager_dashboard'))
else:
return redirect(url_for('dashboard.index'))
flash(error)
return render_template('login.html')
def test_delete(client, auth, app):
auth.login()
response = client.post('/1/delete')
assert response.headers['Location'] == 'http://localhost/'
with app.app_context():
db = get_db()
job = db.execute('SELECT * FROM job WHERE id = 1').fetchone()
assert job is None
def test_create(client, auth, app):
auth.login()
assert client.get('/create').status_code == 200
client.post('/create', data={'job_title': 'created', 'job_desc': 'description'})
with app.app_context():
db = get_db()
count = db.execute('SELECT COUNT(id) FROM job').fetchone()[0]
assert count == 2
def available_jobs():
db = get_db()
jobs = db.execute(
'SELECT * FROM Job j'
' ORDER BY created DESC'
).fetchall()
return render_template('dashboard/available_jobs.html', jobs=jobs)
def test_update(client, auth, app):
auth.login()
assert client.get('/1/update').status_code == 200
client.post('/1/update', data={'job_title': 'updated', 'job_desc': 'description'})
with app.app_context():
db = get_db()
job = db.execute('SELECT * FROM job WHERE id = 1').fetchone()
assert job['job_title'] == 'updated'
assert job['job_desc'] == 'description'
def get_template(id):
template = get_db().execute(
'SELECT * FROM Templates WHERE template_id = ?',
(id,)
).fetchone()
if template is None:
abort(404, "Template id {0} doesn't exist.".format(id))
return template
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():
assert get_db().execute(
"select * from user where username = '******'", ).fetchone() is not None
def test_author_required(app, client, auth):
# change the post author to another user
with app.app_context():
db = get_db()
db.execute('UPDATE job SET author_id = 2 WHERE id = 1')
db.commit()
auth.login()
# current user can't modify other user's post
assert client.post('/1/update').status_code == 403
assert client.post('/1/delete').status_code == 403
# current user doesn't see edit link
assert b'href="/1/update"' not in client.get('/').data
def register():
if request.method == 'POST':
db = get_db()
u_id = request.form['u_id']
username = request.form['username']
password = request.form['password']
error = None
isManager = False
if not username or not password or not u_id:
error = "Please provide valid information"
flash(error)
elif (db.execute('SELECT * FROM Manager WHERE m_username = ?', (username,)).fetchone()) is not error:
error = "Username is already taken"
flash(error)
elif (db.execute('SELECT * FROM Employee WHERE e_username = ?', (username,)).fetchone()) is not error:
error = "Username is already taken"
flash(error)
if error is None and isManager == False:
db.execute(
'INSERT INTO Employee (e_id, e_username, e_password) VALUES (?, ? , ?)',
(u_id, username, generate_password_hash(password))
)
db.commit()
return redirect(url_for('auth.login'))
# Temporarily insert manager and password into database
if error is None and isManager == True:
db.execute(
'INSERT INTO Manager (m_id, m_username, m_password) VALUES (?, ? , ?)',
(u_id, username, generate_password_hash(password))
)
db.commit()
return redirect(url_for('auth.login'))
# check = None
# if error is None:
# if (db.execute('SELECT * FROM Manager WHERE m_id = ?', (u_id,)).fetchone()) is not check:
# db.execute('''UPDATE Manager SET m_username = ?, m_password = ? WHERE m_id = ?''',
# (username, generate_password_hash(password), u_id))
# else:
# db.execute('''UPDATE Employee SET e_username = ?, e_password = ? WHERE e_id = ?''',
# (username, generate_password_hash(password), u_id))
# db.commit()
# return redirect(url_for('auth.login'))
return render_template('register.html')
def get_job(id, check_author=True):
job = get_db().execute(
'SELECT * FROM Job j WHERE job_id = ?',
(id,)
).fetchone()
if job is None:
abort(404, "Job id {0} doesn't exist.".format(id))
# if check_author and job['m_id'] != g.user['u_id']:
# 403 Error means Forbidden
# abort(403)
return job
def load_logged_in_user():
u_id = session.get('u_id')
username = session.get('username')
isManager = session.get('isManager')
u_name = session.get('name')
print("u_id: ", u_id)
print("Username: "******"Manager: ", isManager)
print('Fullname:', u_name)
if u_id is None:
g.user = None
elif isManager == False:
g.user = get_db().execute(
'SELECT * FROM Employee WHERE e_id = ?', (u_id,)
).fetchone()
print("Employee: ", g.user)
elif isManager == True:
g.user = get_db().execute(
'SELECT * FROM Manager WHERE m_id = ?', (u_id,)
).fetchone()
print("Manager: ", g.user)
def update(id):
job = get_job(id)
print(job['job_id'])
print(job['job_desc'])
print(job['job_credentials'])
if request.method == 'POST':
title = request.form['job_title']
body = request.form['job_desc']
qualifications = request.form['job_credentials']
begin_date = request.form['job_date_beg']
end_date = request.form['job_date_beg']
begin_time = request.form['job_time_beg']
end_time = request.form['job_time_end']
job_city = request.form['job_city']
job_state = request.form['job_state']
job_zip = request.form['job_zip']
error = None
if not title:
error = 'Job title is required.'
elif not body:
error = 'Job description is required.'
elif not begin_date or not end_date:
error = "Both Job Begin and End Date are required."
elif not begin_time or not end_time:
error = "Both Job Begin Time and Job End time are required."
elif not job_city or not job_state or not job_zip:
error = " A city, state and zip code are required for all jobs. "
if error is not None:
flash(error)
else:
db = get_db()
db.execute(
'UPDATE Job SET job_title = ?, job_desc = ?, job_credentials = ?, job_date_beg = ?, job_date_end = ?, job_time_beg = ?, job_time_beg = ?, job_city = ?, job_state = ?, job_zip = ?, m_id = ?'
' WHERE job_id = ?',
(title, body, qualifications, begin_date, end_date,
begin_time, end_time, job_city, job_state, job_zip, 1, id)
)
db.commit()
return redirect(url_for('dashboard.manager_dashboard'))
return render_template('dashboard/update.html', job=job)
def index():
db = get_db()
# jobs = db.execute(
# 'SELECT j.id, job_title, job_desc, created, author_id, username'
# ' FROM job j JOIN user u ON j.author_id = u.id'
# ' ORDER BY created DESC'
# ).fetchall()
jobs = db.execute(
'SELECT * FROM Job j'
' ORDER BY created DESC'
).fetchall()
##########################################################################
# NEED TO VERIFY IF THE INFORMATION ON THE NOTIFICATIONS IS CORRECT!
#
# ALSO - need to do 'init-db' each time, else same set of notifications will get added to the table everytime the page is refreshed
##########################################################################
# EMPLOYEES
db.execute('INSERT INTO Notification (type, message, priority, created ) VALUES (?, ?, ? , ?)',
('employee', 'New Delivery Job Posted! ', 'info', '2018-01-01 09:01:17'))
db.execute('INSERT INTO Notification (type, message, priority, created ) VALUES (?, ?, ? , ?)',
('employee', 'Reminder: Delivery Job # is scheduled for today.', 'alert', '2018-01-01 09:01:22'))
db.execute('INSERT INTO Notification (type, message, priority, created ) VALUES (?, ?, ? , ?)',
('employee', 'You have not scheduled any jobs for this week.', 'warning', '2018-01-01 10:02:09'))
db.execute('INSERT INTO Notification (type, message, priority, created ) VALUES (?, ?, ? , ?)',
('employee', 'Job # was successfully added to your schedule.', 'success', '2018-01-01 10:12:12'))
db.execute('INSERT INTO Notification (type, message, priority, created ) VALUES (?, ?, ? , ?)',
('employee', 'Scheduled Job # was cancelled!', 'alert', '2018-01-01 11:02:43'))
db.commit()
notifications = db.execute('SELECT * FROM Notification n').fetchall()
return render_template('dashboard/index.html', jobs=jobs, notifications=notifications)
def delete(id):
get_job(id)
db = get_db()
db.execute('DELETE FROM job WHERE job_id = ?', (id,))
db.commit()
return redirect(url_for('dashboard.index'))
