def change_pass():
if request.method == 'POST':
current = request.form['inputCPass']
new = request.form['inputNPass']
verify = request.form['inputVPass']
db = get_db()
error = None
if not current:
error = 'Current password is required.'
elif not new:
error = 'New password is required.'
elif not verify:
error = 'Password confirmation is required.'
elif not new == verify:
error = 'Password and confirmation password does not match.'
elif not check_password_hash(g.user['password_hash'], current):
error = 'Incorrect current password. Please try again.'
if error is None:
db.execute(
'UPDATE bt_users SET password_hash = ?'
' WHERE user_id = ?',
(generate_password_hash(new), g.user['user_id']))
db.commit()
return redirect(url_for('user.profile'))
flash(error, 'error')
return render_template('user/change_password.html')
def users():
db = get_db()
where_clause = ""
if g.user['user_role'] == 'Administrator':
where_clause = " WHERE u.user_role not in ('Administrator')"
elif g.user['user_role'] == 'Manager':
where_clause = " WHERE u.user_role not in ('Administrator', 'Manager')"
elif g.user['user_role'] == 'Lead':
where_clause = " WHERE u.user_role not in ('Administrator', 'Manager', 'Lead') AND u.assigned_project = '{}'".format(
g.user['assigned_project'])
elif g.user['user_role'] == 'Member':
where_clause = " WHERE u.username = '******'".format(g.user['username'])
query = (
"SELECT u.*, p.project_name as `project_name`" +
" FROM bt_users u left join bt_projects p on u.assigned_project == p.project_id"
+ where_clause + " ORDER BY created_on DESC;")
users = db.execute(query).fetchall()
access_create = check_access_create()
return render_template('user/users.html',
users=users,
access_create=access_create)
def delete(id):
get_project(id)
db = get_db()
db.execute('DELETE FROM bt_projects WHERE project_id = ?', (id, ))
db.commit()
return redirect(url_for('project.projects'))
def profile():
user = get_user(g.user['user_id'])
if request.method == 'POST':
first_name = request.form['inputName']
last_name = request.form['inputLast']
email = request.form['inputEmail']
error = None
db = get_db()
if not first_name:
error = 'User first name is required.'
elif not last_name:
error = 'User last name is required.'
elif not email:
error = 'User email is required.'
if error is not None:
flash(error, 'error')
else:
modified_on = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
db.execute(
'UPDATE bt_users SET first_name = ?, last_name = ?, email = ?, modified_on = ?, modified_by = ?'
' WHERE user_id = ?',
(first_name, last_name, email, modified_on, g.user['username'],
g.user['user_id']))
db.commit()
load_logged_in_user()
user = get_user(g.user['user_id'])
#return redirect(url_for('dashboard.index'))
return render_template('user/profile.html', user=user)
def edit(id):
issue = get_issue(id)
access_delete = check_access_delete(id)
projects = get_projects()
users = get_users()
if request.method == 'POST':
issue_subject = request.form['inpuSubject']
issue_desc = request.form['inputDesc']
issue_project = request.form['inputProject']
issue_ident_by = request.form['inputIdentBy']
issue_ident_on = request.form['inputIdentOn']
issue_assigned_to = request.form['inputAssigned']
issue_status = request.form['inputStatus']
issue_priority = request.form['inputPriority']
issue_target_date = request.form['inputDueDate']
issue_progress = request.form['inputProgress']
issue_actual_date = request.form['inputEndDate']
issue_end_summary = request.form['inputEndSum']
if not issue_target_date or issue_target_date == 'None':
issue_target_date = None
if not issue_actual_date or issue_actual_date == 'None':
issue_actual_date = None
db = get_db()
error = None
if not issue_subject:
error = 'Issue summary is required.'
elif not issue_project:
error = 'Related project is required.'
elif not issue_ident_by:
error = 'Identified by is required.'
elif not issue_ident_on:
error = 'Identified date is required.'
elif not issue_status:
error = 'Issue status is required.'
elif not issue_priority:
error = 'Issue priority is required.'
elif issue_actual_date and issue_actual_date < issue_target_date:
error = 'Actual resolution date must be same or after to target resolution date.'
if error is not None:
flash(error, 'error')
else:
modified_on = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
db.execute(
'UPDATE bt_issues SET issue_subject = ?, issue_desc = ?, project_id = ?, identified_by = ?,'
' identified_on = ?, assigned_to = ?, status = ?, priority = ?, target_resolution_date = ?,'
' issue_progress = ?, actual_resolution_date = ?, resolution_summary = ?, modified_on = ?, modified_by = ?'
' WHERE issue_id = ?',
(issue_subject, issue_desc, issue_project, issue_ident_by, issue_ident_on, issue_assigned_to, issue_status, issue_priority, issue_target_date, issue_progress, issue_actual_date, issue_end_summary, modified_on, g.user['username'], id)
)
db.commit()
return redirect(url_for('issue.issues'))
return render_template('issue/edit.html', issue=issue, access_delete=access_delete, projects=projects, users=users)
def load_logged_in_user():
user_id = session.get('user_id')
if user_id is None:
g.user = None
else:
g.user = get_db().execute('SELECT * FROM bt_users WHERE user_id = ?',
(user_id, )).fetchone()
def create():
projects = get_projects()
users = get_users()
if request.method == 'POST':
issue_subject = request.form['inpuSubject']
issue_desc = request.form['inputDesc']
issue_project = request.form['inputProject']
issue_ident_by = request.form['inputIdentBy']
issue_ident_on = request.form['inputIdentOn']
issue_assigned_to = request.form['inputAssigned']
issue_status = request.form['inputStatus']
issue_priority = request.form['inputPriority']
issue_target_date = request.form['inputDueDate']
issue_progress = request.form['inputProgress']
issue_actual_date = request.form['inputEndDate']
issue_end_summary = request.form['inputEndSum']
if not issue_target_date:
issue_target_date = None
if not issue_actual_date:
issue_actual_date = None
db = get_db()
error = None
if not issue_subject:
error = 'Issue summary is required.'
elif not issue_project:
error = 'Related project is required.'
elif not issue_ident_by:
error = 'Identified by is required.'
elif not issue_ident_on:
error = 'Identified date is required.'
elif not issue_status:
error = 'Issue status is required.'
elif not issue_priority:
error = 'Issue priority is required.'
elif issue_actual_date and issue_actual_date < issue_target_date:
error = 'Actual resolution date must be same or after to target resolution date.'
if error is not None:
flash(error, 'error')
else:
created_on = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
db.execute(
'INSERT INTO bt_issues (issue_subject, issue_desc, project_id, identified_by, identified_on, assigned_to, status, priority, target_resolution_date, issue_progress, actual_resolution_date, resolution_summary, created_on, created_by)'
' VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
(issue_subject, issue_desc, issue_project, issue_ident_by, issue_ident_on, issue_assigned_to, issue_status, issue_priority, issue_target_date, issue_progress, issue_actual_date, issue_end_summary, created_on, g.user['username'])
)
db.commit()
return redirect(url_for('issue.issues'))
return render_template('issue/create.html', projects=projects, users=users)
def delete(id):
get_user(id)
db = get_db()
if check_access_delete(id):
db.execute('DELETE FROM bt_users WHERE user_id = ?', (id,))
db.commit()
else:
abort(403, "Access denied, only administrator or managers can delete an user")
return redirect(url_for('user.users'))
def check_access_delete(issue_id):
access_delete = True
issue = get_db().execute(
'SELECT *'
' FROM bt_issues'
' WHERE issue_id = ?',
(issue_id,)
).fetchone()
if g.user['user_role'] in ['Member', 'Lead']:
if issue['created_by'] != g.user['username']:
access_delete = False
return access_delete
def register():
form_data = {'email': '', 'name': '', 'last': '', 'username': ''}
if request.method == 'POST':
email = request.form['email']
name = request.form['name']
last = request.form['last']
username = request.form['username']
password = request.form['password']
verify = request.form['verify']
form_data = request.form.to_dict()
db = get_db()
error = None
if not email:
error = 'Email name is required.'
elif not name:
error = 'First name is required.'
elif not last:
error = 'Last name is required.'
elif not username:
error = 'Username is required.'
elif not password:
error = 'Password is required.'
elif not verify:
error = 'Password confirmation is required.'
elif not password == verify:
error = 'Password and confirmation password does not match.'
elif db.execute('SELECT user_id FROM bt_users WHERE username = ?',
(username, )).fetchone() is not None:
error = 'User {} is already registered.'.format(username)
if error is None:
db.execute(
'INSERT INTO bt_users (first_name, last_name, email, username, password_hash, user_role, created_by) VALUES (?, ?, ?, ?, ?, ?, ?)',
(name, last, email, username, generate_password_hash(password),
'Manager', 'Admin'))
db.commit()
return render_template(
'auth/register.html',
success=
'User created please. <a class="alert-link" href="/login">Log In</a>.'
)
#return redirect(url_for('auth.login'))
flash(error, 'error')
return render_template('auth/register.html', form=form_data)
def get_projects(check_owner=True):
where_clause = ""
if g.user['user_role'] == 'Manager':
where_clause = " WHERE p.created_by = '{}'".format(g.user['username'])
elif g.user['user_role'] in ['Lead', 'Member']:
where_clause = " WHERE p.project_id = '{}'".format(g.user['assigned_project'])
query = ("SELECT p.project_name, p.project_id" +
" FROM bt_projects p" +
where_clause +
" ORDER BY p.created_on DESC;")
projects = get_db().execute(query).fetchall()
return projects
def get_user(id, check_login=True):
user = get_db().execute('SELECT *'
' FROM bt_users'
' WHERE user_id = ?', (id, )).fetchone()
if user is None:
abort(404, "User id {0} doesn't exist.".format(id))
if not g.user['user_role'] in ['Administrator', 'Manager']:
if check_login and user['user_id'] != g.user['user_id']:
abort(
403,
"Access denied, only administrator and managers can manage user accounts"
)
return user
def get_users(check_login=True):
where_clause = " WHERE u.user_role not in ('Administrator', 'Manager')"
if g.user['user_role'] == 'Lead':
where_clause += " AND u.assigned_project = '{}'".format(g.user['assigned_project'])
elif g.user['user_role'] == 'Member':
where_clause += " AND u.user_id = '{}'".format(g.user['user_id'])
query = ("SELECT u.first_name, u.last_name, u.user_id" +
" FROM bt_users u" +
where_clause +
" ORDER BY u.first_name DESC;")
users = get_db().execute(query).fetchall()
return users
def get_project(id, check_owner=True):
project = get_db().execute(
'SELECT *'
' FROM bt_projects'
' WHERE project_id = ?', (id, )).fetchone()
if project is None:
abort(404, "Project id {0} doesn't exist.".format(id))
if g.user['user_role'] != 'Administrator':
if check_owner and project['created_by'] != g.user['username']:
abort(
403,
"Access denied, only the administrator or project manager can access"
)
return project
def projects():
db = get_db()
where_clause = ""
if g.user['user_role'] in ['Lead', 'Member']:
where_clause = " WHERE project_id = '{}'".format(
g.user['assigned_project'])
query = ("SELECT * FROM bt_projects" + where_clause +
" ORDER BY created_on DESC")
projects = db.execute(query).fetchall()
access_create = check_access_create()
return render_template('project/projects.html',
projects=projects,
access_create=access_create)
def edit(id):
user = get_user(id)
access_delete = check_access_delete()
projects = get_projects()
if request.method == 'POST':
first_name = request.form['inputName']
last_name = request.form['inputLast']
email = request.form['inputEmail']
user_role = request.form['inputRole']
assigned_project = None
if not user_role in ['Administrator', 'Manager']:
assigned_project = request.form['inputProject']
error = None
db = get_db()
if not first_name:
error = 'User first name is required.'
elif not last_name:
error = 'User last name is required.'
elif not email:
error = 'User email is required.'
elif not user_role:
error = 'User role is required.'
if error is not None:
flash(error, 'error')
else:
modified_on = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
db.execute(
'UPDATE bt_users SET first_name = ?, last_name = ?, email = ?, user_role = ?, assigned_project = ?'
' , modified_on = ?, modified_by = ?'
' WHERE user_id = ?',
(first_name, last_name, email, user_role, assigned_project,
modified_on, g.user['username'], id))
db.commit()
return redirect(url_for('user.users'))
return render_template('user/edit.html',
projects=projects,
user=user,
access_delete=access_delete)
def create():
projects = get_projects()
if request.method == 'POST':
first_name = request.form['inputName']
last_name = request.form['inputLast']
email = request.form['inputEmail']
username = request.form['inputUsername']
user_role = request.form['inputRole']
assigned_project = request.form['inputProject']
db = get_db()
error = None
if not first_name:
error = 'User first name is required.'
elif not last_name:
error = 'User last name is required.'
elif not email:
error = 'User email is required.'
elif not user_role:
error = 'User role is required.'
elif not username:
error = 'Username is required.'
elif db.execute('SELECT user_id FROM bt_users WHERE username = ?',
(username, )).fetchone() is not None:
error = 'User {} already exist.'.format(username)
if error is not None:
flash(error, 'error')
else:
created_on = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
db.execute(
'INSERT INTO bt_users (first_name, last_name, email, username, password_hash, user_role, assigned_project, created_on, created_by)'
' VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)',
(first_name, last_name, email, username,
generate_password_hash('password'), user_role,
assigned_project, created_on, g.user['username']))
db.commit()
return redirect(url_for('user.users'))
return render_template('user/create.html', projects=projects)
def get_projects(check_owner=True):
where_clause = ""
if g.user['user_role'] == 'Manager':
where_clause = " WHERE p.created_by = '{}'".format(g.user['username'])
elif g.user['user_role'] == 'Lead':
where_clause = " WHERE p.project_id = '{}'".format(
g.user['assigned_project'])
query = ("SELECT p.project_name, p.project_id" + " FROM bt_projects p" +
where_clause + " ORDER BY p.created_on DESC;")
projects = get_db().execute(query).fetchall()
if check_owner and not g.user['user_role'] in [
'Administrator', 'Manager', 'Lead'
]:
abort(403, "Access denied, only administrator and managers can access")
return projects
def issues():
db = get_db()
where_clause = ""
if g.user['user_role'] in ['Lead', 'Member']:
where_clause = " WHERE i.assigned_to = '{}' OR i.project_id = '{}'".format(g.user['user_id'], g.user['assigned_project'])
query = ("SELECT i.issue_id, i.issue_subject, i.identified_on, i.status, i.priority, i.target_resolution_date," +
" i.issue_progress, i.actual_resolution_date, ui.first_name || ' ' || ui.last_name AS identified_by," +
" p.project_name, ua.first_name || ' ' || ua.last_name AS assigned_to" +
" FROM bt_issues i " +
" INNER JOIN bt_projects p ON p.project_id = i.project_id " +
" LEFT JOIN bt_users ui ON ui.user_id = i.identified_by" +
" LEFT JOIN bt_users ua ON ua.user_id = i.assigned_to" +
where_clause +
" ORDER BY i.issue_id DESC")
issues = db.execute(query).fetchall()
return render_template('issue/issues.html', issues=issues)
def create():
if request.method == 'POST':
name = request.form['inputPName']
desc = request.form['inputPDesc']
start_date = request.form['inputSDate']
target_date = request.form['inputTDate']
end_date = request.form['inputEDate']
if not end_date:
end_date = None
db = get_db()
error = None
if not name:
error = 'Project name is required.'
elif not start_date:
error = 'Project start date is required.'
elif not target_date:
error = 'Project target date is required.'
elif end_date and end_date < start_date:
error = 'Actual End Date must be same or after Start Date..'
elif db.execute(
'SELECT project_id FROM bt_projects WHERE project_name = ?',
(name, )).fetchone() is not None:
error = 'Project {} already exist.'.format(name)
if error is not None:
flash(error, 'error')
else:
created_on = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
db.execute(
'INSERT INTO bt_projects (project_name, project_desc, start_date, target_end_date, actual_end_date, created_on, created_by)'
' VALUES (?, ?, ?, ?, ?, ?, ?)',
(name, desc, start_date, target_date, end_date, created_on,
g.user['username']))
db.commit()
return redirect(url_for('project.projects'))
return render_template('project/create.html')
def get_issue(id, check_login=True):
issue = get_db().execute(
'SELECT *'
' FROM bt_issues'
' WHERE issue_id = ?',
(id,)
).fetchone()
if issue is None:
abort(404, "Issue id {0} doesn't exist.".format(id))
if check_login and g.user['user_role'] == 'Member':
if issue['assigned_to'] != g.user['user_id']:
abort(403, "Access denied, only administrator and managers can manage user accounts")
if check_login and g.user['user_role'] == 'Lead':
if issue['assigned_to'] != g.user['user_id'] and issue['created_by'] != g.user['username']:
abort(403, "Access denied, only administrator and managers can manage user accounts")
return issue
def edit(id):
project = get_project(id)
access_delete = check_access_delete(id)
if request.method == 'POST':
name = request.form['inputPName']
desc = request.form['inputPDesc']
start_date = request.form['inputSDate']
target_date = request.form['inputTDate']
end_date = request.form['inputEDate']
error = None
db = get_db()
if not end_date or end_date == 'None':
end_date = None
if not name:
error = 'Project name is required.'
elif not start_date:
error = 'Project start date is required.'
elif not target_date:
error = 'Project target date is required.'
if error is not None:
flash(error, 'error')
else:
modified_on = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
db.execute(
'UPDATE bt_projects SET project_name = ?, project_desc = ?, start_date = ?, target_end_date = ?, actual_end_date = ?'
' , modified_on = ?, modified_by = ?'
' WHERE project_id = ?',
(name, desc, start_date, target_date, end_date, modified_on,
g.user['username'], id))
db.commit()
return redirect(url_for('project.projects'))
return render_template('project/edit.html',
project=project,
access_delete=access_delete)
def login():
if request.method == 'POST':
username = request.form['username']
password = request.form['password']
db = get_db()
error = None
user = db.execute('SELECT * FROM bt_users WHERE username = ?',
(username, )).fetchone()
if user is None:
error = 'Incorrect username.'
elif not check_password_hash(user['password_hash'], password):
error = 'Incorrect password.'
if error is None:
session.clear()
session['user_id'] = user['user_id']
return redirect(url_for('dashboard.index'))
flash(error, 'error')
return render_template('auth/login.html')
def index():
db = get_db()
dash_stats = dict()
dash_stats['users_total'] = db.execute(
"SELECT count(*) from bt_users where user_role <> 'Administrator'"
).fetchone()
dash_stats['projects_total'] = db.execute(
"SELECT count(*) from bt_projects").fetchone()
dash_stats['issues_total'] = db.execute(
"SELECT count(*) from bt_issues").fetchone()
dash_stats['issues_by_status'] = db.execute(
'select SUM(CASE When status="Open" Then 1 Else 0 End ) as `sum_open`,'
' SUM(CASE When status="On-Hold" Then 1 Else 0 End ) as `sum_on_hold`,'
' SUM(CASE When status="Closed" Then 1 Else 0 End ) as `sum_closed`'
' from bt_issues;').fetchone()
dash_stats['issues_stats'] = db.execute(
'select SUM(CASE When target_resolution_date < DATE("now") AND status = "Open" Then 1 Else 0 End ) as `sum_overdue`,'
' SUM(CASE When assigned_to is NULL Then 1 Else 0 End ) as `sum_unassigned`'
' from bt_issues;').fetchone()
query = (
"SELECT i.issue_id, i.issue_subject, i.priority, i.target_resolution_date,"
+ " p.project_name, ua.first_name || ' ' || ua.last_name AS assignee" +
" FROM bt_issues i " +
" INNER JOIN bt_projects p ON p.project_id = i.project_id " +
" LEFT JOIN bt_users ui ON ui.user_id = i.identified_by" +
" LEFT JOIN bt_users ua ON ua.user_id = i.assigned_to" +
" WHERE i.target_resolution_date < DATE('now') AND i.status = 'Open'" +
" ORDER BY i.issue_id DESC")
issues_overdue = db.execute(query).fetchall()
return render_template('dashboard/index.html',
dash_stats=dash_stats,
issues_overdue=issues_overdue)