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)