def get_positions(check_submitter=True):
query = (
'SELECT p.id, ps.name as status, rs.name as recruitment_status, number, pl.name as pillar, c.name as company, er.rate as rate, d.name as department,'
'f.name as function, title, functional_reporting_line, disciplinary_reporting_line, holder,'
'hours, start_date, end_date, salary, fringe_benefit, social_security_contribution, performance_bonus, super_bonus, management_bonus'
' FROM position p'
' JOIN company c ON p.company_id = c.id'
' JOIN exchange_rate er on c.exchange_rate_id = er.id'
' JOIN pillar pl ON p.pillar_id = pl.id'
' JOIN department d ON p.department_id = d.id'
' JOIN function f ON p.function_id = f.id'
' JOIN position_status ps ON p.status_id = ps.id'
' JOIN recruitment_status rs ON p.recruitment_status_id = rs.id'
' WHERE isBudget = 0')
if check_submitter and g.user['type'] != 'ADMIN':
user_functions = get_user_function(session['user_id'])
if user_functions:
function_ids = [
function['function_id'] for function in user_functions
]
query += ' AND f.id IN (?' + ', ?' * (len(function_ids) - 1) + ')'
return get_db().execute(query, tuple(function_ids)).fetchall()
abort(403, 'You are not assigned to any functions')
return get_db().execute(query).fetchall()
def create_exchange_rate():
name = request.form['exchangeRateName']
code = request.form['exchangeRateCode']
rate = request.form['exchangeRateRate']
errors = []
if not name:
errors.append('Name is required.')
if not code:
errors.append('Code is required.')
if not rate:
errors.append('Rate is required.')
if errors:
[flash(error, category='create') for error in errors]
return jsonify(status='error')
else:
db = get_db()
db.execute(
'INSERT INTO exchange_rate (name, code, rate) VALUES (?, ?, ?)',
(name, code, rate))
db.commit()
return jsonify(status='ok')
def faq():
"""Show help page content."""
name = 'faq'
faq = get_db().execute('SELECT * FROM page WHERE name = ?',
(name, )).fetchall()
return render_template('admin/faq.html', page=faq)
def department():
"""Show all departments."""
db = get_db()
departments = db.execute('SELECT *' ' FROM department').fetchall()
return render_template('admin/department.html', departments=departments)
def function():
"""Show all functions."""
db = get_db()
functions = db.execute('SELECT *' ' FROM function').fetchall()
return render_template('admin/function.html', functions=functions)
def help():
"""Show help page content."""
name = 'help'
help = get_db().execute('SELECT body FROM page WHERE name = ?',
(name, )).fetchone()
return render_template('admin/help.html', page=help)
def create_faq():
name = 'faq'
title = request.form['question']
body = request.form['editordata']
errors = []
if not title:
errors.append('Question is required.')
if not body:
errors.append('Answer is required.')
if errors:
[flash([error, id], category='update') for error in errors]
return jsonify(status='error')
else:
db = get_db()
db.execute(
'INSERT INTO page (name, title, body, edited)'
' VALUES (?, ?, ?, ?)',
(name, title, body, datetime.fromtimestamp(
time()).strftime('%Y-%m-%d %H:%M:%S')))
db.commit()
return jsonify(status='ok')
def recruitment_status():
"""Show all functions."""
recruitment_statuses = get_db().execute(
'SELECT * FROM recruitment_status').fetchall()
return render_template('admin/recruitment_status.html',
recruitment_statuses=recruitment_statuses)
def update_submission_change(submission_id, submission_change_id):
submission_change = get_submission_change(submission_change_id,
check_submitter=False)
submission_reasons = get_submission_reasons()
data = {
"submissionChange": dict(submission_change),
"submissionReasons": dict(submission_reasons)
}
if request.method == 'POST':
reason_id = request.form['reason']
change = request.form['change']
rationale = request.form['rationale']
error = None
if not reason_id:
error = 'Reason is required.'
if error is not None:
flash(error)
else:
db = get_db()
db.execute(
'UPDATE submission_change SET reason_id = ?, effective_date, rationale = ? WHERE id = ?',
(reason_id, change, rationale, submission_id))
db.commit()
return jsonify(status='ok')
return jsonify(dict(data))
def get_position(id, check_submitter=True):
position = get_db().execute(
'SELECT p.id, ps.name as status, rs.name as recruitment_status, number, pl.name as pillar, c.name as company, d.name as department,'
'f.id as function_id, f.name as function, title, functional_reporting_line, disciplinary_reporting_line, holder,'
'hours, start_date, end_date, salary, social_security_contribution, fringe_benefit, performance_bonus, super_bonus, management_bonus'
' FROM position p'
' JOIN company c ON p.company_id = c.id'
' JOIN pillar pl ON p.pillar_id = pl.id'
' JOIN department d ON p.department_id = d.id'
' JOIN function f ON p.function_id = f.id'
' JOIN position_status ps ON p.status_id = ps.id'
' JOIN recruitment_status rs ON p.recruitment_status_id = rs.id'
' WHERE p.number = ? AND p.isBudget = 0', (id, )).fetchone()
if position is None:
abort(404, "Position id {0} doesn't exist.".format(id))
user_functions = get_user_function(session['user_id'])
function_ids = [function['function_id'] for function in user_functions]
if check_submitter and g.user['type'] != 'ADMIN' and position[
'function_id'] not in function_ids:
abort(403)
return position
def pillar():
"""Show all pillars."""
db = get_db()
pillars = db.execute('SELECT *' ' FROM pillar').fetchall()
return render_template('admin/pillar.html', pillars=pillars)
def update_company(id):
company = get_company(id)
currencies = get_exchange_rates()
# extract columns we need and convert back to tuple
currencies = [(currency['id'], currency['name'])
for currency in currencies]
data = {"company": dict(company), "currencies": dict(currencies)}
if request.method == 'POST':
name = request.form['companyName']
exchange_rate_id = request.form['currency']
errors = []
if not name:
errors.append('Name is required.')
if not exchange_rate_id:
errors.append('Exchange rate required.')
if errors:
[flash([error, id], category='update') for error in errors]
# return flashed messages
return jsonify(status='error')
else:
db = get_db()
db.execute(
'UPDATE company SET name = ?, exchange_rate_id = ? WHERE id = ?',
(name, exchange_rate_id, id))
db.commit()
return jsonify(status='ok')
return jsonify(dict(data))
def create_company():
currencies = get_exchange_rates()
# extract columns we need and convert back to tuple
currencies = [(currency['id'], currency['name'])
for currency in currencies]
if request.method == 'POST':
name = request.form['companyName']
exchange_rate_id = request.form['currency']
errors = []
if not name:
errors.append('Name is required.')
if not exchange_rate_id:
errors.append('Exchange rate required.')
if errors:
[flash(error, category='create') for error in errors]
# return flashed messages
return jsonify(status='error')
else:
db = get_db()
db.execute(
'INSERT INTO company (exchange_rate_id, name) VALUES (?, ?)',
(exchange_rate_id, name))
db.commit()
return jsonify(status='ok')
return jsonify(dict(currencies))
def get_companies():
companies = get_db().execute(
'SELECT c.id, c.name, e.name as currency'
' FROM company c'
' JOIN exchange_rate e on c.exchange_rate_id = e.id').fetchall()
return companies
def update_exchange_rate(id):
exchange_rate = get_exchange_rate(id)
if request.method == 'POST':
name = request.form['exchangeRateName']
code = request.form['exchangeRateCode']
rate = request.form['exchangeRateRate']
errors = []
if not name:
errors.append('Name is required.')
if not code:
errors.append('Code is required.')
if not rate:
errors.append('Rate is required.')
if errors:
[flash([error, id], category='update') for error in errors]
return jsonify(status='error')
else:
db = get_db()
db.execute(
'UPDATE exchange_rate SET name = ?, code = ?, rate = ? WHERE id = ?',
(name, code, rate, id))
db.commit()
return jsonify(status='ok')
return jsonify(dict(exchange_rate))
def get_departments():
departments = get_db().execute(
'SELECT *'
' FROM department '
).fetchall()
return departments
def upload_budget():
import_type = request.form['importType']
delimeter = request.form['delimeter']
date_format = request.form['dateFormat']
day_first = True if date_format == 'dd/mm/yyy' else False
file = request.files['file']
df = pd.read_csv(
file,
sep='{}'.format(delimeter),
keep_default_na=False,
parse_dates=True,
dayfirst=day_first,
encoding='utf-8') if file and allowed_file(file.filename) else None
df['Start Date'] = pd.to_datetime(df['Start Date'],
errors='coerce').dt.date.astype(object)
df['End Date'] = pd.to_datetime(df['End Date'],
errors='coerce').dt.date.astype(object)
errors = []
if not import_type:
errors.append('Name is required.')
if not file:
errors.append('File is required.')
if errors:
[flash(error, category='upload') for error in errors]
# return flashed messages
return jsonify(status='error')
else:
if is_upload_valid:
for index, row in df.iterrows():
db = get_db()
# testing exception rollback
with db:
db.execute(
'REPLACE INTO position'
'('
'status_id, recruitment_status_id, number, pillar_id, company_id, department_id,'
'function_id, isBudget, title, functional_reporting_line, disciplinary_reporting_line, holder,'
'hours, start_date, end_date, salary, social_security_contribution,'
'fringe_benefit, performance_bonus, super_bonus, management_bonus'
')'
' VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
(get_position_status_by_name(row['Status'])['id'],
get_recruitment_status_by_name(
row['Recruitment Status'])['id'], row['Number'],
get_pillar_by_name(row['Pillar'])['id'],
get_company_by_name(row['Company'])['id'],
get_department_by_name(row['Department'])['id'],
get_function_by_name(row['Function'])['id'], 1,
row['Title'], row['Functional Reporting Line'],
row['Disciplinary Reporting Line'], row['Holder'],
row['Hours'], row['Start Date'], row['End Date'],
row['Salary'], row['Social Security Contribution'],
row['Fringe Benefit'], row['Performance Bonus'],
row['Super Bonus'], row['Management Bonus']))
return jsonify(status='ok')
def get_user(id):
user = get_db().execute('SELECT * FROM user WHERE id = ?',
(id, )).fetchone()
if user is None:
abort(404, "User id {0} doesn't exist.".format(id))
return user
def get_user_from_email(email):
user = get_db().execute('SELECT * FROM user WHERE email = ?',
(email, )).fetchone()
if user is None:
abort(404, "User email {0} doesn't exist.".format(email))
return user
def get_company_by_name(name):
company = get_db().execute('SELECT * FROM company WHERE name = ?',
(name, )).fetchone()
if company is None:
abort(404, "Company name {0} doesn't exist.".format(name))
return company
def get_submissions():
return get_db().execute(
'SELECT s.id, s.submitted, s.position_id, s.effective_date, u.email, s.submitter_id, u.id, gk.date as gatekeeping_date, st.name as status, sr.name as reason, s.rationale'
' FROM submission s'
' JOIN user u ON s.submitter_id = u.id'
' JOIN gatekeeping gk ON s.gatekeeping_id = gk.id'
' JOIN submission_status st ON s.status_id = st.id'
' JOIN submission_reason sr ON s.reason_id = sr.id').fetchall()
def exchange_rate():
"""Show all departments."""
db = get_db()
exchange_rates = db.execute('SELECT *' ' FROM exchange_rate').fetchall()
return render_template('admin/exchange_rate.html',
exchange_rates=exchange_rates)
def update_submission(id):
submission = get_submission(id, check_submitter=False)
submission_statuses = get_submission_statuses()
reasons = get_submission_reasons()
data = {
"submission": dict(submission),
"submissionStatuses": dict(submission_statuses),
"submissionReasons": dict(reasons)
}
# convert date to string for displaying
data['submission']['effective_date'] = str(submission['effective_date'])
data['submission']['gatekeeping_date'] = str(
submission['gatekeeping_date'])
if request.method == 'POST':
position_id = request.form['positionId']
effective_date = request.form['effectiveDate']
gatekeeping_date = request.form['gatekeepingDate']
reason_id = get_submission_reason_by_name(request.form['reason'])['id']
rationale = request.form['rationale']
status = request.form['status']
comment = request.form['comment']
errors = []
if not position_id:
errors.append('Position Id is required.')
if not effective_date:
errors.append('Effective date is required.')
if errors:
[flash(error, category='update') for error in errors]
return jsonify(status='error')
else:
db = get_db()
db.execute(
'UPDATE submission SET position_id = ?, effective_date, submission_status_id = ? WHERE id = ?',
(position_id, effective_date, status, id))
db.commit()
user = get_user(submission['submittor_id'])
send_mail(send_from='*****@*****.**',
send_to=user['email'],
subject='Submission reviewed',
text='Hi,\n\n'
f'Your submission has been reviewed.\n'
f'Position number: {position_id}\n'
f'Status: {status}\n'
f'Comment: {comment}\n\n',
server='smtp.office365.com')
return jsonify(status='ok')
return jsonify(dict(data))
def submission_status():
"""Show all functions."""
db = get_db()
submission_statuses = db.execute('SELECT *'
' FROM submission_status').fetchall()
return render_template('admin/submission_status.html',
submission_statuses=submission_statuses)
def get_next_gatekeeping():
gatekeeping = get_db().execute(
'SELECT *'
' FROM gatekeeping'
' WHERE date >= ?'
' ORDER BY date ASC LIMIT 1',
(str(datetime.now().date()), )).fetchone()
return gatekeeping
def get_user_from_password_reset_token(token):
user = get_db().execute(
'SELECT * FROM user WHERE password_reset_token = ?',
(token, )).fetchone()
if user is None:
abort(404, "User token {0} doesn't exist.".format(token))
return user
def has_user_function(id):
user_function = get_db().execute(
'SELECT count(*) FROM user_function where user_id = ?',
(id, )).fetchall()
if user_function:
return True
return False
def get_pillar(id):
pillar = get_db().execute('SELECT *'
' FROM pillar '
' WHERE id = ?', (id, )).fetchone()
if pillar is None:
abort(404, "Pillar id {0} doesn't exist.".format(id))
return pillar
def get_pillar_by_name(name):
pillar = get_db().execute('SELECT *'
' FROM pillar '
' WHERE name = ?', (name, )).fetchone()
if pillar is None:
abort(404, "Pillar id {0} doesn't exist.".format(name))
return pillar
def position_status():
"""Show all functions."""
db = get_db()
position_statuses = db.execute('SELECT *'
' FROM position_status').fetchall()
return render_template('admin/position_status.html',
position_statuses=position_statuses)