def delete(id):
'''Delete a department by id.
Ensures that the department exists.
'''
get_dept(id)
ip = request.remote_addr
score = reCAPTCHA().verify
if not score or score < reCAPTCHA().level:
flash(reCAPTCHA().failed)
return redirect(url_for('dept.update', id=id))
db = get_db()
db.execute('DELETE FROM department WHERE id = ?', (id, ))
g.db.commit()
current_app.log.info(
'UID:%s(%s) %s%s(score:%s)', {
'UID': g.user['id'],
'IP': ip,
'action': 'delete department',
'data': {
'id': id
},
'score': score
})
return redirect(url_for('dept.index'))
def delete(id):
'''Delete a record by id if it belong the current user.
Ensures that the record is not verified.
'''
record = get_record(id)
ip = request.remote_addr
error = None
if record['status'] != 0:
error = 'You can only delete record which is not verified.'
score = reCAPTCHA().verify
if not score or score < reCAPTCHA().level:
error = reCAPTCHA().failed
if error:
flash(error)
return redirect(url_for('record.update', id=id))
else:
db = get_db()
db.execute('DELETE FROM record WHERE id = ?', (id, ))
g.db.commit()
current_app.log.info(
'UID:%s(%s) %s%s(score:%s)', {
'UID': g.user['id'],
'IP': ip,
'action': 'delete record',
'data': {
'id': id
},
'score': score
})
return redirect(url_for('record.empl_index'))
def update(id):
'''Update a record by id if it belongs the current user.
Ensures that the record is not verified.
'''
record = get_record(id)
if request.method == 'POST':
ip = request.remote_addr
date = request.form.get('date')
error = None
try:
type = int(request.form.get('type'))
except:
error = 'Type is required.'
try:
if type == 1:
duration = int(request.form.get('duration'))
if duration < 1:
raise ValueError
else:
duration = 0 - int(request.form.get('duration'))
if duration > -1:
raise ValueError
except:
error = 'Duration is required.'
describe = request.form.get('describe')
if not date:
error = 'Date is required.'
if record['status'] != 0:
error = 'You can only update record which is not verified.'
score = reCAPTCHA().verify
if not score or score < reCAPTCHA().level:
error = reCAPTCHA().failed
if error:
flash(error)
else:
db = get_db()
db.execute(
'UPDATE record SET date = ?, type = ?, duration = ?, describe = ? WHERE id = ?',
(date, type, duration, describe, id))
g.db.commit()
current_app.log.info(
'UID:%s(%s) %s%s(score:%s)', {
'UID': g.user['id'],
'IP': ip,
'action': 'update record',
'data': {
'id': id,
'date': date,
'type': type,
'duration': duration
},
'score': score
})
return redirect(url_for('record.empl_index'))
return render_template('record/update.html', record=record)
def login():
'''Log in a user by adding the user id to the session.'''
last_visit = request.cookies.get('LastVisit')
if g.user:
if last_visit:
return redirect(last_visit)
return redirect(url_for('index'))
if request.method == 'POST':
ip = request.remote_addr
username = request.form.get('username')
password = request.form.get('password')
rememberme = request.form.get('rememberme')
db = get_db()
error = None
try:
user = db.execute('SELECT * FROM employee WHERE username = ?',
(username,)).fetchone()
except:
tables = db.execute('SELECT name FROM sqlite_master').fetchall()
if tables == []:
init_db()
flash('Detected first time running. Initialized the database.')
else:
flash('Critical Error! Please contact your system administrator.')
return render_template('auth/login.html')
score = reCAPTCHA().verify
if not score or score < reCAPTCHA().level:
error = reCAPTCHA().failed
if user is None:
error = 'Incorrect username.'
elif not check_password_hash(user['password'], password) and user['password'] != password:
error = 'Incorrect password.'
current_app.log.info('UID:%s(%s) %s(score:%s)',
{'UID': user['id'], 'IP': ip, 'action': 'password failed', 'score': score})
if error is None:
# store the user id in a new session and return to the index
session.clear()
session['user_id'] = user['id']
if rememberme == 'on':
session.permanent = True
else:
session.permanent = False
current_app.log.info('UID:%s(%s) %s(score:%s)',
{'UID': user['id'], 'IP': ip, 'action': 'log in', 'score': score})
if last_visit:
return redirect(last_visit)
if user['id']:
return redirect(url_for('index'))
else:
return redirect(url_for('record.dept_index'))
flash(error)
response = make_response(render_template('auth/login.html'))
response.delete_cookie('Last')
return response
def create():
'''Create a new record for the current user.'''
if request.method == 'POST':
ip = request.remote_addr
date = request.form.get('date')
error = None
try:
type = int(request.form.get('type'))
except:
error = 'Type is required.'
try:
if type == 1:
duration = int(request.form.get('duration'))
if duration < 1:
raise ValueError
else:
duration = 0 - int(request.form.get('duration'))
if duration > -1:
raise ValueError
except:
error = 'Duration is required.'
describe = request.form.get('describe')
if not date:
error = 'Date is required.'
if g.user['id'] == 0:
error = 'Super Administrator cannot create personal record.'
score = reCAPTCHA().verify
if not score or score < reCAPTCHA().level:
error = reCAPTCHA().failed
if error:
flash(error)
else:
db = get_db()
db.execute(
'INSERT INTO record (date, type, duration, describe, dept_id, empl_id, createdby)'
' VALUES (?, ?, ?, ?, ?, ?, ?)',
(date, type, duration, describe, g.user['dept_id'],
g.user['id'], f"{g.user['id']}-{ip}"))
g.db.commit()
current_app.log.info(
'UID:%s(%s) %s%s(score:%s)', {
'UID': g.user['id'],
'IP': ip,
'action': 'create record',
'data': {
'id': db.lastrowid,
'date': date,
'type': type,
'duration': duration
},
'score': score
})
return redirect(url_for('record.empl_index'))
return render_template('record/create.html')
def update(id):
'''Update a employee by id.
Ensures that the employee exists.
'''
empl = get_empl(id)
try:
permission_list = list(map(int, empl['permission'].split(',')))
except ValueError:
permission_list = []
db = get_db()
depts = db.execute(
'SELECT * from department ORDER BY dept_name').fetchall()
if request.method == 'POST':
ip = request.remote_addr
username = request.form.get('username').strip()
realname = request.form.get('realname').strip()
if realname == '':
realname = username
password = request.form.get('password')
dept_id = request.form.get('dept')
type = request.form.get('type')
permission = ','.join(request.form.getlist('permission'))
error = None
if not username:
error = 'Username is required.'
if db.execute('SELECT id FROM employee WHERE username = ? and id != ?',
(username, id)).fetchone() is not None:
error = 'Username {0} is already existed.'.format(username)
if not dept_id:
error = 'Department is required.'
if not type:
error = 'Type is required.'
if type == '1' and permission == '':
error = 'At least one permission must be selected for Administrator.'
score = reCAPTCHA().verify
if not score or score < reCAPTCHA().level:
error = reCAPTCHA().failed
if error is not None:
flash(error)
else:
db.execute(
'UPDATE employee'
' SET username = ?, realname = ?, dept_id = ?, type = ?, permission = ?'
' WHERE id = ?',
(username, realname, dept_id, type, permission, id))
if password != '':
db.execute('UPDATE employee SET password = ? WHERE id = ?',
(generate_password_hash(password), id))
g.db.commit()
current_app.log.info('UID:%s(%s) %s%s}(score:%s)',
{'UID': g.user['id'], 'IP': ip, 'action': 'update employee', 'data': {'id': id, 'username': username, 'realname': realname, 'dept_id': int(dept_id)}, 'score': score})
return redirect(url_for('empl.index'))
return render_template('empl/update.html', empl=empl, permission=permission_list, depts=depts)
def verify(id):
'''Verify a record belong the departments which the Administrator has the permission.
Ensures that the record is not verified.
'''
record = get_record(id, mode='dept')
if request.method == 'POST':
ip = request.remote_addr
error = None
if request.form.get('status') == '1':
status = 1
elif request.form.get('status') == '2':
status = 2
else:
error = 'Unknow status.'
if record['status'] != 0:
error = 'The record is already verified.'
score = reCAPTCHA().verify
if not score or score < reCAPTCHA().level:
error = reCAPTCHA().failed
if error:
flash(error)
else:
db = get_db()
db.execute(
'UPDATE record SET status = ?, verifiedby = ? WHERE id = ?',
(status, f"{g.user['id']}-{ip}", id))
g.db.commit()
current_app.log.info(
'UID:%s(%s) %s%s(score:%s)', {
'UID': g.user['id'],
'IP': ip,
'action': 'verify record',
'data': {
'id': id,
'status': status
},
'score': score
})
return redirect(url_for('record.dept_index'))
return render_template('record/verify.html', record=record)
def add():
'''Add a new department.
Validates that the department name is not already taken.
'''
if request.method == 'POST':
ip = request.remote_addr
department = request.form.get('dept').strip()
db = get_db()
error = None
if not department:
error = 'Department name is required.'
if db.execute('SELECT id FROM department WHERE dept_name = ?',
(department, )).fetchone() is not None:
error = 'Department {0} is already existed.'.format(department)
score = reCAPTCHA().verify
if not score or score < reCAPTCHA().level:
error = reCAPTCHA().failed
if error is not None:
flash(error)
else:
# the department name is available, store it in the database
db.execute('INSERT INTO department (dept_name) VALUES (?)',
(department, ))
db.execute(
'UPDATE employee SET permission = (SELECT group_concat(id) FROM department) WHERE id = 0'
)
g.db.commit()
current_app.log.info(
'UID:%s(%s) %s%s(score:%s)', {
'UID': g.user['id'],
'IP': ip,
'action': 'add department',
'data': {
'id': db.lastrowid,
'dept_name': department
},
'score': score
})
return redirect(url_for('dept.index'))
return render_template('dept/add.html')
def update(id):
'''Update a department by id.
Ensures that the department exists.
'''
dept = get_dept(id)
if request.method == 'POST':
ip = request.remote_addr
department = request.form.get('dept').strip()
error = None
db = get_db()
if not department:
error = 'Department name is required.'
if db.execute(
'SELECT id FROM department WHERE dept_name = ? and id != ?',
(department, id)).fetchone() is not None:
error = 'Department {0} is already existed.'.format(department)
score = reCAPTCHA().verify
if not score or score < reCAPTCHA().level:
error = reCAPTCHA().failed
if error is not None:
flash(error)
else:
db.execute('UPDATE department SET dept_name = ? WHERE id = ?',
(department, id))
g.db.commit()
current_app.log.info(
'UID:%s(%s) %s%s(score:%s)', {
'UID': g.user['id'],
'IP': ip,
'action': 'update department',
'data': {
'id': id,
'dept_name': department
},
'score': score
})
return redirect(url_for('dept.index'))
return render_template('dept/update.html', dept=dept)
def setting():
'''Change current user's password.'''
if request.method == 'POST':
ip = request.remote_addr
password = request.form.get('password')
password1 = request.form.get('password1')
password2 = request.form.get('password2')
db = get_db()
error = None
user = db.execute('SELECT password FROM employee WHERE id = ?',
(g.user['id'],)).fetchone()
if not check_password_hash(user['password'], password) and user['password'] != password:
error = 'Incorrect password.'
elif password1 != password2:
error = "Confirm password doesn't match new password."
elif password1 == password:
error = 'New password cannot be the same as your current password.'
elif password1 is None or password1 == '':
error = 'New password cannot be blank.'
score = reCAPTCHA().verify
if not score or score < reCAPTCHA().level:
error = reCAPTCHA().failed
if error is None:
# Store new password in the database and go to
# the login page
db.execute(
'UPDATE employee SET password = ? WHERE id = ?',
(generate_password_hash(password1), g.user['id']),
)
g.db.commit()
current_app.log.info('UID:%s(%s) %s(score:%s)',
{'UID': g.user['id'], 'IP': ip, 'action': 'change password', 'score': score})
session.clear()
flash('Password Changed. Please Re-login!')
return redirect(url_for('auth.login'))
flash(error)
return render_template('auth/setting.html')
def manage_delete(id):
'''Delete a record by Super Administrator.'''
get_record(id, mode='super')
ip = request.remote_addr
score = reCAPTCHA().verify
if not score or score < reCAPTCHA().level:
flash(reCAPTCHA().failed)
return redirect(url_for('record.manage_update', id=id))
db = get_db()
db.execute('DELETE FROM record WHERE id = ?', (id, ))
g.db.commit()
current_app.log.info(
'UID:%s(%s) %s%s(score:%s)', {
'UID': g.user['id'],
'IP': ip,
'action': 'manage delete record',
'data': {
'id': id
},
'score': score
})
return redirect(url_for('record.super_index'))
def embed_recaptcha():
return dict(recaptcha=reCAPTCHA())
def manage_update(id):
'''Update a record by Super Administrator.'''
record = get_record(id, mode='super')
db = get_db()
depts = db.execute(
'SELECT * FROM department ORDER BY dept_name').fetchall()
empls = db.execute(
'SELECT * FROM employee WHERE id != 0 ORDER BY realname').fetchall()
if request.method == 'POST':
ip = request.remote_addr
dept_id = request.form.get('dept')
empl_id = request.form.get('empl')
date = request.form.get('date')
error = None
try:
type = int(request.form.get('type'))
except:
error = 'Type is required.'
try:
if type == 1:
duration = int(request.form.get('duration'))
if duration < 1:
raise ValueError
else:
duration = 0 - int(request.form.get('duration'))
if duration > -1:
raise ValueError
except:
error = 'Duration is required.'
status = request.form.get('status')
describe = request.form.get('describe')
if not empl_id:
error = 'Employee is required.'
if not dept_id:
error = 'Department is required.'
if not date:
error = 'Date is required.'
if not status:
error = 'Status is required.'
score = reCAPTCHA().verify
if not score or score < reCAPTCHA().level:
error = reCAPTCHA().failed
if error:
flash(error)
else:
db.execute(
'UPDATE record SET empl_id = ?, dept_id = ?, date = ?, type = ?, duration = ?, status = ?, describe = ? WHERE id = ?',
(empl_id, dept_id, date, type, duration, status, describe, id))
g.db.commit()
current_app.log.info(
'UID:%s(%s) %s%s(score:%s)', {
'UID': g.user['id'],
'IP': ip,
'action': 'manage update record',
'data': {
'id': id,
'empl_id': int(empl_id),
'dept_id': int(dept_id),
'date': date,
'type': type,
'duration': duration,
'status': int(status)
},
'score': score
})
return redirect(url_for('record.super_index'))
return render_template('record/update.html',
record=record,
empls=empls,
depts=depts,
mode='super')
def manage_create():
'''Create a new record for a employee who belong the department
which the Administrator has the permission.'''
db = get_db()
try:
permission_list = g.user['permission'].split(',')
except ValueError:
permission_list = []
depts = db.execute('SELECT * FROM department'
' WHERE id IN ({}) ORDER BY dept_name'.format(
','.join(permission_list))).fetchall()
if request.method == 'POST':
ip = request.remote_addr
dept_id = request.form.get('dept')
empl_id = request.form.get('empl')
date = request.form.get('date')
error = None
try:
type = int(request.form.get('type'))
except:
error = 'Type is required.'
try:
if type == 1:
duration = int(request.form.get('duration'))
if duration < 1:
raise ValueError
else:
duration = 0 - int(request.form.get('duration'))
if duration > -1:
raise ValueError
except:
error = 'Duration is required.'
describe = request.form.get('describe')
if not date:
error = 'Date is required.'
if not dept_id:
error = 'Department is required.'
elif dept_id not in permission_list:
abort(403)
else:
if not empl_id:
error = 'Employee is required.'
elif str(
db.execute('SELECT * FROM employee WHERE id = ?',
(empl_id, )).fetchone()['dept_id']) != dept_id:
abort(403)
score = reCAPTCHA().verify
if not score or score < reCAPTCHA().level:
error = reCAPTCHA().failed
if error:
flash(error)
else:
db.execute(
'INSERT INTO record (dept_id, empl_id, date, type, duration, describe, status, createdby, verifiedby)'
' VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)',
(dept_id, empl_id, date, type, duration, describe, 1,
f"{g.user['id']}-{ip}", f"{g.user['id']}-{ip}"))
g.db.commit()
current_app.log.info(
'UID:%s(%s) %s%s(score:%s)', {
'UID': g.user['id'],
'IP': ip,
'action': 'manage create record',
'data': {
'id': db.lastrowid,
'dept_id': int(dept_id),
'empl_id': int(empl_id),
'date': date,
'type': type,
'duration': duration
},
'score': score
})
return redirect(url_for('record.dept_index'))
return render_template('record/create.html', depts=depts, mode='admin')
def add():
'''Add a new employee.
Validates that the username is not already taken. Default password is 123456
and hashes the password for security.
'''
db = get_db()
depts = []
try:
permission_list = g.user['permission'].split(',')
except ValueError:
permission_list = []
depts = db.execute('SELECT * FROM department'
' WHERE id IN ({}) ORDER BY dept_name'.format(','.join(permission_list))).fetchall()
if request.method == 'POST':
ip = request.remote_addr
username = request.form.get('username').strip()
realname = request.form.get('realname').strip()
if realname == '':
realname = username
dept_id = request.form.get('dept')
db = get_db()
error = None
if not username:
error = 'Username is required.'
if db.execute('SELECT id FROM employee WHERE username = ?',
(username,)).fetchone() is not None:
error = 'Username {0} is already existed.'.format(username)
if not dept_id:
error = 'Department is required.'
if g.user['id'] == 0:
type = request.form.get('type')
permission = ','.join(request.form.getlist('permission'))
if not type:
error = 'Type is required.'
if type == '1' and permission == '':
error = 'At least one permission must be selected for Administrator.'
score = reCAPTCHA().verify
if not score or score < reCAPTCHA().level:
error = reCAPTCHA().failed
if error is None:
# the name is available, store it in the database
if g.user['id'] != 0:
db.execute(
'INSERT INTO employee (username, realname, dept_id) VALUES (?, ?, ?)',
(username, realname, dept_id))
else:
db.execute(
'INSERT INTO employee (username, realname, dept_id, type, permission)'
' VALUES (?, ?, ?, ?, ?)',
(username, realname, dept_id, type, permission))
g.db.commit()
current_app.log.info('UID:%s(%s) %s%s(score:%s)',
{'UID': g.user['id'], 'IP': ip, 'action': 'add employee', 'data': {'id': db.lastrowid, 'username': username, 'realname': realname, 'dept_id': int(dept_id)}, 'score': score})
return redirect(url_for('empl.index'))
flash(error)
return render_template('empl/add.html', depts=depts)