def get_record(id, mode='normal'):
'''Get a record by id.
Checks that the id exists and optionally record belong the current user or
the department which the Administrator has the permission.
:param id: id of record to get
:param mode: check belongs by the current user or the Administrator
:return: the record information
:raise 404: if a record with the given id doesn't exist
:raise 403: if the record not belong the current user or the Administrator
'''
record = get_db().execute(
'SELECT r.id, date, ABS(duration) duration, r.type, describe, d.dept_name, empl_id, status, r.dept_id'
' FROM record r JOIN employee e ON r.empl_id = e.id JOIN department d ON r.dept_id = d.id'
' WHERE r.id = ?', (id, )).fetchone()
if not record:
abort(404, "Record id {0} doesn't exist.".format(id))
if mode == 'normal':
if record['empl_id'] != g.user['id']:
abort(403)
else:
if str(record['dept_id']) not in g.user['permission'].split(','):
abort(403)
return record
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 empl_index():
'''Show all the records match the filter and belong the current user, most recent first.'''
if not g.user['id']:
return redirect(url_for('record.dept_index'))
db = get_db()
year = request.args.get('year')
month = request.args.get('month')
type = request.args.get('type')
status = request.args.get('status')
page = request.args.get('page') or 1
per_page = request.args.get('per_page') or 10
args = dict(year=year,
month=month,
type=type,
status=status,
page=page,
per_page=per_page)
years = db.execute(
"SELECT DISTINCT strftime('%Y', date) year FROM record"
' WHERE empl_id = ? ORDER BY year DESC', (g.user['id'], )).fetchall()
condition = ''
if year:
if not month:
condition += " AND strftime('%Y', date) = '{}'".format(year)
else:
condition += " AND strftime('%Y%m', date) = '{}'".format(year +
month)
if type:
condition += ' AND r.type = {}'.format(type)
if status:
condition += ' AND status = {}'.format(status)
record = len(
db.execute(
'SELECT r.id, date, ABS(duration) duration, r.type, describe, d.dept_name, empl_id, realname, created, status'
' FROM record r JOIN employee e ON r.empl_id = e.id LEFT JOIN department d ON r.dept_id = d.id'
' WHERE r.empl_id = ? {}'
' ORDER BY created DESC'.format(condition),
(g.user['id'], )).fetchall())
limit = 'LIMIT {}, {}'.format((int(page) - 1) * int(per_page),
int(per_page))
records = db.execute(
'SELECT r.id, date, ABS(duration) duration, r.type, describe, d.dept_name, empl_id, realname, created, status'
' FROM record r JOIN employee e ON r.empl_id = e.id LEFT JOIN department d ON r.dept_id = d.id'
' WHERE r.empl_id = ? {}'
' ORDER BY created DESC {}'.format(condition, limit),
(g.user['id'], )).fetchall()
pagination = Pagination(per_page=int(per_page),
page=int(page),
record=record)
return render_template('record/index.html',
records=records,
years=years,
args=args,
pagination=pagination,
mode='empl')
def empl_index():
'''Show all the statistics match the filter and belong the current user.'''
if not g.user['id']:
return redirect(url_for('stats.dept_index'))
db = get_db()
period = request.args.get('period')
year = request.args.get('year')
month = request.args.get('month')
page = request.args.get('page') or 1
per_page = request.args.get('per_page') or 10
args = dict(period=period,
year=year,
month=month,
page=page,
per_page=per_page)
years = db.execute(
'SELECT DISTINCT substr(period,1,4) year FROM statistics'
' WHERE empl_id = ? ORDER BY year DESC', (g.user['id'], )).fetchall()
if not period or period == 'month':
query = 'SELECT * FROM statistics WHERE empl_id = ? {}'
if not month:
if not year:
condition = ''
else:
condition = "AND substr(period,1,4) = '{}'".format(year)
else:
condition = "AND period = '{}'".format(year + '-' + month)
elif period == 'year':
query = (
'SELECT substr(period,1,4) year, dept_name, realname, sum(overtime) overtime, sum(leave) leave, sum(summary) summary'
' FROM statistics WHERE empl_id = ? {}'
' GROUP BY year, dept_id, empl_id'
' ORDER BY year DESC')
if not year:
condition = ''
else:
condition = "AND year = '{}'".format(year)
record = len(
db.execute(query.format(condition), (g.user['id'], )).fetchall())
limit = ' LIMIT {}, {}'.format((int(page) - 1) * int(per_page),
int(per_page))
records = db.execute(query.format(condition) + limit,
(g.user['id'], )).fetchall()
pagination = Pagination(per_page=int(per_page),
page=int(page),
record=record)
return render_template('stats/index.html',
records=records,
years=years,
args=args,
pagination=pagination,
mode='empl')
def get_dept(id):
'''Get a department by id.
Checks that the id exists.
:param id: id of department to get
:return: the department information
:raise 404: if a department with the given id doesn't exist
'''
dept = get_db().execute('SELECT * FROM department WHERE id = ?',
(id, )).fetchone()
if dept is None:
abort(404, "Department id {0} doesn't exist.".format(id))
return dept
def exportCSV(query, fieldnames):
db = get_db()
try:
rows = db.execute(query).fetchall()
except:
rows = []
stringIO = StringIO(newline='')
csv = DictWriter(stringIO, fieldnames, extrasaction='ignore')
csv.writeheader()
csv.writerows(rows)
bytesIO = BytesIO()
bytesIO.write(stringIO.getvalue().encode('utf-8-sig'))
bytesIO.seek(0)
return bytesIO
def get_empl(id):
'''Get a employee by id.
Checks that the id exists.
:param id: id of post to get
:return: the employee information
:raise 404: if a employee with the given id doesn't exist
'''
db = get_db()
empl = db.execute('SELECT * FROM employee WHERE id = ?', (id,)).fetchone()
if empl is None:
abort(404, "Employee id {0} doesn't exist.".format(id))
return empl
def load_logged_in_user():
'''If a user id is stored in the session, load the user object from
the database into ``g.user``.'''
user_id = session.get('user_id')
last = request.cookies.get('Last')
ip = request.remote_addr
db = get_db()
if user_id is None:
g.user = None
else:
g.user = db.execute(
'SELECT * FROM employee WHERE id = ?', (user_id,)).fetchone()
if not session.get('_permanent') and last and time()-float(last) > current_app.config.get('SESSION_COOKIE_LIFETIME'):
session.clear()
flash('Session timeout. Please re-login!')
current_app.log.info('UID:%s(%s) %s%s',
{'UID': user_id, 'IP': ip, 'action': 'time out', 'data': {'last': strftime('%Y-%m-%d %H:%M:%S', localtime(float(last)))}})
return redirect(url_for('auth.login'))
def get():
'''Get employee list by dept_id.
return json format
raise 403 if current user doesn't have the permission
'''
dept_id = request.form.get('dept')
try:
permission_list = g.user['permission'].split(',')
except ValueError:
permission_list = []
if dept_id not in permission_list:
abort(403)
db = get_db()
empl = db.execute(
'SELECT id, realname FROM employee where dept_id = ? ORDER BY realname', (dept_id,)).fetchall()
empl = dict((i['id'], i['realname']) for i in empl)
return jsonify(empl)
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 index():
'''Show all employees match the filter and belong the departments which the Administrator has the permission.'''
db = get_db()
dept_id = request.args.get('dept')
type = request.args.get('type')
page = request.args.get('page') or 1
per_page = request.args.get('per_page') or 10
args = dict(dept_id=dept_id, type=type, page=page, per_page=per_page)
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()
condition = ''
if dept_id:
if dept_id not in permission_list:
abort(403)
condition += 'e.dept_id = {}'.format(dept_id)
else:
condition += 'e.dept_id IN ({})'.format(','.join(permission_list))
if type:
condition += ' AND type = {}'.format(type)
else:
condition += ''
record = len(db.execute(
'SELECT e.*, dept_name FROM employee e'
' JOIN department d ON e.dept_id = d.id'
' WHERE {}'.format(condition)).fetchall())
limit = 'LIMIT {}, {}'.format((int(page)-1)*int(per_page), int(per_page))
empls = db.execute(
'SELECT e.*, dept_name FROM employee e'
' JOIN department d ON e.dept_id = d.id'
' WHERE {} ORDER BY dept_name, realname {}'.format(condition, limit)).fetchall()
pagination = Pagination(per_page=int(per_page),
page=int(page), record=record)
return render_template('empl/index.html', empls=empls, depts=depts, args=args, pagination=pagination)
def dept_stats():
'''Export all the statistics match the filter and belong the departments which the Administrator has the permission.'''
dept_id = request.args.get('dept')
empl_id = request.args.get('empl')
period = request.args.get('period')
year = request.args.get('year')
month = request.args.get('month')
action = request.args.get('action')
try:
permission_list = g.user['permission'].split(',')
except ValueError:
permission_list = []
if not period or period == 'month':
if action == 'Export':
query = ('SELECT period Period, dept_name Department, realname Realname,'
' overtime Overtime, leave Leave, summary Summary'
' FROM statistics WHERE 1=1 {}')
elif action == '导出':
query = ('SELECT period 周期, dept_name 部门, realname 姓名,'
' overtime 加班, leave 休假, summary 汇总'
' FROM statistics WHERE 1=1 {}')
else:
query = '{}'
if not month:
if not year:
condition = ''
else:
condition = "AND substr(period,1,4) = '{}'".format(year)
else:
condition = "AND period = '{}'".format(year+'-'+month)
elif period == 'year':
if action == 'Export':
query = ('SELECT substr(period,1,4) Period, dept_name Department, realname Realname,'
' sum(overtime) Overtime, sum(leave) Leave, sum(summary) Summary'
' FROM statistics WHERE 1=1 {}'
' GROUP BY Period, dept_id, empl_id'
' ORDER BY Period DESC')
elif action == '导出':
query = ('SELECT substr(period,1,4) Period, dept_name Department, realname Realname,'
' sum(overtime) Overtime, sum(leave) Leave, sum(summary) Summary'
' FROM statistics WHERE 1=1 {}'
' GROUP BY Period, dept_id, empl_id'
' ORDER BY Period DESC')
else:
query = '{}'
if not year:
condition = ''
else:
condition = "AND year = '{}'".format(year)
db = get_db()
if dept_id and not empl_id:
if str(dept_id) not in permission_list:
abort(403)
prefix = '-' + db.execute('SELECT dept_name FROM department'
' WHERE id = ?', (dept_id,)).fetchone()['dept_name']
condition += 'AND dept_id = {}'.format(dept_id)
elif empl_id:
if str(db.execute('SELECT * FROM employee WHERE id = ?', (empl_id,)).fetchone()['dept_id']) != dept_id:
abort(403)
prefix = '-' + db.execute('SELECT realname FROM employee'
' WHERE id = ?', (empl_id,)).fetchone()['realname']
condition += 'AND empl_id = {}'.format(empl_id)
else:
prefix = ''
condition += 'AND dept_id IN ({})'.format(','.join(permission_list))
if action == '导出':
fieldnames = ['周期', '部门', '姓名', '加班', '休假', '汇总']
else:
fieldnames = ['Period', 'Department',
'Realname', 'Overtime', 'Leave', 'Summary']
download_file = exportCSV(query.format(condition), fieldnames)
if action == '导出':
filename = f'部门员工统计{prefix}{year}{month}.csv'.replace('None', '')
else:
filename = f'DeptStats{prefix}{year}{month}.csv'.replace('None', '')
return send_file(download_file, attachment_filename=filename, as_attachment=True)
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)
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 index():
'''Show all the departments.'''
db = get_db()
depts = db.execute('SELECT * FROM department').fetchall()
return render_template('dept/index.html', depts=depts)
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 dept_index():
'''Show all the statistics match the filter and belong the departments which the Administrator has the permission.'''
db = get_db()
dept_id = request.args.get('dept')
empl_id = request.args.get('empl') or ''
period = request.args.get('period')
year = request.args.get('year')
month = request.args.get('month')
page = request.args.get('page') or 1
per_page = request.args.get('per_page') or 10
args = dict(dept_id=dept_id,
empl_id=empl_id,
period=period,
year=year,
month=month,
page=page,
per_page=per_page)
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()
years = db.execute(
'SELECT DISTINCT substr(period,1,4) year FROM statistics'
' WHERE dept_id IN ({}) ORDER BY year DESC'.format(
','.join(permission_list))).fetchall()
if not period or period == 'month':
query = 'SELECT * FROM statistics WHERE 1=1 {}'
if not month:
if not year:
condition = ''
else:
condition = "AND substr(period,1,4) = '{}'".format(year)
else:
condition = "AND period = '{}'".format(year + '-' + month)
elif period == 'year':
query = (
'SELECT substr(period,1,4) year, dept_name, realname, sum(overtime) overtime, sum(leave) leave, sum(summary) summary'
' FROM statistics WHERE 1=1 {}'
' GROUP BY year, dept_id, empl_id'
' ORDER BY year DESC')
if not year:
condition = ''
else:
condition = "AND year = '{}'".format(year)
if dept_id and not empl_id:
if dept_id not in permission_list:
abort(403)
condition += 'AND dept_id = {}'.format(dept_id)
elif empl_id:
if str(
db.execute('SELECT * FROM employee WHERE id = ?',
(empl_id, )).fetchone()['dept_id']) != dept_id:
abort(403)
condition += 'AND empl_id = {}'.format(empl_id)
else:
condition += 'AND dept_id IN ({})'.format(','.join(permission_list))
record = len(db.execute(query.format(condition)).fetchall())
limit = ' LIMIT {}, {}'.format((int(page) - 1) * int(per_page),
int(per_page))
records = db.execute(query.format(condition) + limit).fetchall()
pagination = Pagination(per_page=int(per_page),
page=int(page),
record=record)
return render_template('stats/index.html',
records=records,
depts=depts,
years=years,
args=args,
pagination=pagination,
mode='dept')
def admin_index(mode=None):
db = get_db()
dept_id = request.args.get('dept')
empl_id = request.args.get('empl') or ''
year = request.args.get('year')
month = request.args.get('month')
type = request.args.get('type')
status = request.args.get('status')
page = request.args.get('page') or 1
per_page = request.args.get('per_page') or 10
args = dict(dept_id=dept_id,
empl_id=empl_id,
year=year,
month=month,
type=type,
status=status,
page=page,
per_page=per_page)
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()
years = db.execute("SELECT DISTINCT strftime('%Y', date) year FROM record"
' WHERE dept_id IN ({}) ORDER BY year DESC'.format(
','.join(permission_list))).fetchall()
if dept_id and not empl_id:
if dept_id not in permission_list:
abort(403)
condition1 = 'r.dept_id = {}'.format(dept_id)
elif empl_id:
if str(
db.execute('SELECT * FROM employee WHERE id = ?',
(empl_id, )).fetchone()['dept_id']) != dept_id:
abort(403)
condition1 = 'empl_id = {}'.format(empl_id)
else:
condition1 = 'r.dept_id IN ({})'.format(','.join(permission_list))
condition2 = ''
if year:
if not month:
condition2 += " AND strftime('%Y', date) = '{}'".format(year)
else:
condition2 += " AND strftime('%Y%m', date) = '{}'".format(year +
month)
if type:
condition2 += ' AND r.type = {}'.format(type)
if status:
condition2 += ' AND status = {}'.format(status)
record = len(
db.execute(
'SELECT r.id, date, ABS(duration) duration, r.type, describe, d.dept_name, empl_id, realname, created, status'
' FROM record r JOIN employee e ON empl_id = e.id JOIN department d ON r.dept_id = d.id'
' WHERE {}{}'
' ORDER BY created DESC'.format(condition1,
condition2)).fetchall())
limit = 'LIMIT {}, {}'.format((int(page) - 1) * int(per_page),
int(per_page))
records = db.execute(
'SELECT r.id, date, ABS(duration) duration, r.type, describe, d.dept_name, empl_id, realname, created, status'
' FROM record r JOIN employee e ON empl_id = e.id JOIN department d ON r.dept_id = d.id'
' WHERE {}{}'
' ORDER BY created DESC {}'.format(condition1, condition2,
limit)).fetchall()
pagination = Pagination(per_page=int(per_page),
page=int(page),
record=record)
return render_template('record/index.html',
records=records,
depts=depts,
years=years,
args=args,
pagination=pagination,
mode=mode)
def dept_records():
'''Export all the records match the filter and belong the departments which the Administrator has
the permission, most recent first.'''
dept_id = request.args.get('dept')
empl_id = request.args.get('empl')
year = request.args.get('year')
month = request.args.get('month')
type = request.args.get('type')
status = request.args.get('status')
action = request.args.get('action')
try:
permission_list = g.user['permission'].split(',')
except ValueError:
permission_list = []
if action == 'Export':
query = ('SELECT d.dept_name Department, realname Realname, date Date,'
" CASE r.type WHEN 1 THEN 'Overtime' WHEN 0 THEN 'Leave' END Type,"
' ABS(duration) Duration, describe Describe, created Created,'
" CASE status WHEN 0 THEN 'Unverified' WHEN 1 THEN 'Verified' WHEN 2 THEN 'Rejected' END Status"
' FROM record r JOIN employee e ON empl_id = e.id JOIN department d ON r.dept_id = d.id'
' WHERE {} {}'
' ORDER BY created DESC')
elif action == '导出':
query = ('SELECT d.dept_name 部门, realname 姓名, date 日期,'
" CASE r.type WHEN 1 THEN '加班' WHEN 0 THEN '休假' END 类型,"
' ABS(duration) 时长, describe 描述, created 创建日期,'
" CASE status WHEN 0 THEN '未审核' WHEN 1 THEN '已通过' WHEN 2 THEN '已驳回' END 状态"
' FROM record r JOIN employee e ON empl_id = e.id JOIN department d ON r.dept_id = d.id'
' WHERE {} {}'
' ORDER BY created DESC')
else:
query = '{}{}'
db = get_db()
if dept_id and not empl_id:
if dept_id not in permission_list:
abort(403)
prefix = '-' + db.execute('SELECT dept_name FROM department'
' WHERE id = ?', (dept_id,)).fetchone()['dept_name']
condition1 = 'r.dept_id = {}'.format(dept_id)
elif empl_id:
if str(db.execute('SELECT * FROM employee WHERE id = ?', (empl_id,)).fetchone()['dept_id']) != dept_id:
abort(403)
prefix = '-' + db.execute('SELECT realname FROM employee'
' WHERE id = ?', (empl_id,)).fetchone()['realname']
condition1 = 'empl_id = {}'.format(empl_id)
else:
prefix = ''
condition1 = 'r.dept_id IN ({})'.format(','.join(permission_list))
condition2 = ''
if year:
if not month:
condition2 += "AND strftime('%Y', date) = '{}'".format(year)
else:
condition2 += "AND strftime('%Y%m', date) = '{}'".format(year+month)
if type:
condition2 += ' AND r.type = {}'.format(type)
if status:
condition2 += ' AND status = {}'.format(status)
if action == '导出':
fieldnames = ['部门', '姓名', '日期', '类型', '时长', '描述', '创建日期', '状态']
else:
fieldnames = ['Department', 'Realname', 'Date', 'Type',
'Duration', 'Describe', 'Created', 'Status']
download_file = exportCSV(query.format(condition1, condition2), fieldnames)
if action == '导出':
filename = f'部门员工记录{prefix}{year}{month}.csv'.replace('None', '')
else:
filename = f'DeptRecords{prefix}{year}{month}.csv'.replace('None', '')
return send_file(download_file, attachment_filename=filename, as_attachment=True)
