def report_blueprint_root():
current_role = get_current_role()
if current_role not in ['admin']:
return redirect('/menu')
return render_template('report_root.html')
def interview_blueprint_route_appointment_pick_interview():
current_role = get_current_role()
if current_role != 'admin':
return redirect('/menu')
if 'appointment' not in session:
session['appointment'] = {'interview_id': None, 'candidate_ids': []}
if request.method == 'POST':
session['appointment'] = {
'interview_id': int(request.form.get('interview_id')),
'candidate_ids': session['appointment']['candidate_ids']
}
return redirect('/interview/appointed')
elif request.method == 'GET':
appointment = session['appointment'] \
if 'appointment' in session \
else {}
current_interview_id = appointment['interview_id'] \
if 'interview_id' in appointment \
else None
try:
with UseDatabase(current_app.config['db'][current_role]) as cursor:
get_interviews_query = """
select
i.iv_id,
i.salary,
e.name as employee,
i.iv_date,
v.position
from interview i
join employee e on e.emp_id = i.emp_id
join vacancy v on i.v_id = v.v_id
where iv_id not in (select distinct iv_id from interview_result) and iv_id != 4
order by i.iv_id
"""
cursor.execute(get_interviews_query)
interviews = make_dict_list_from_rows(cursor)
return render_template(
'interview_appointment_pick_interview_admin.html',
interviews=interviews,
interview_id=current_interview_id)
except DBConnectionError as e:
return 'Произошла ошибка соединения'
except DBCredentialError as e:
return 'Не удается войти в бд'
except DBBaseError as e:
return 'Произошла непредвиденная ошибка бд'
except DBSQLError as e:
return 'Ошибка базы данных'
else:
return 'Unknown action'
def query_blueprint_route_root():
current_role = get_current_role()
if current_role not in ['admin', 'worker']:
return redirect('/menu')
is_admin = current_role == 'admin'
return render_template('query_root.html', is_admin=is_admin)
def interview_blueprint_route_appointment_pick_candidate():
current_role = get_current_role()
if current_role != 'admin':
return redirect('/menu')
if 'appointment' not in session:
session['appointment'] = {'interview_id': None, 'candidate_ids': []}
appointment = session['appointment']
candidate_ids = appointment['candidate_ids']
if request.method == 'POST':
new_candidate_id = int(request.form.get('candidate_id'))
candidate_ids.append(new_candidate_id)
session['appointment'] = {
'interview_id': appointment['interview_id'],
'candidate_ids': candidate_ids
}
return redirect('/interview/appointed')
elif request.method == 'GET':
try:
with UseDatabase(current_app.config['db'][current_role]) as cursor:
if candidate_ids:
query_chunk = " where c_id not in (" +\
','.join(str(id) for id in candidate_ids) +\
')'
else:
query_chunk = ""
get_candidates_query = f"""
select c_id, name, age, gender, address from candidate {query_chunk}
"""
cursor.execute(get_candidates_query)
candidates = make_dict_list_from_rows(cursor)
return render_template(
'interview_appointment_pick_candidates_admin.html',
candidates=candidates if candidates else None)
except DBConnectionError as e:
return 'Произошла ошибка соединения'
except DBCredentialError as e:
return 'Не удается войти в бд'
except DBBaseError as e:
return 'Произошла непредвиденная ошибка бд'
except DBSQLError as e:
return 'Ошибка базы данных'
else:
return 'Unknown action'
def query_blueprint_route_interviews():
current_role = get_current_role()
if current_role not in ['admin']:
return redirect('/menu')
form = request.form if request.method == 'POST' else None
try:
with UseDatabase(current_app.config['db'][current_role]) as cursor:
if form and 'sign' in form.keys():
sign = form.get('sign')
else:
sign = '>'
if form and 'rating' in form.keys():
rating = form.get('rating')
else:
rating = 0
get_interviews_query = f"""
select
c.c_id as cd_id, c.name as c_name,
i.iv_id, i.salary as i_salary, iv_date,
v.v_id, position,
e.emp_id, e.name as e_name,
ir.rating, ir.result
from interview_result ir
join candidate c on c.c_id = ir.c_id
join interview i on i.iv_id = ir.iv_id
join vacancy v on v.v_id = i.v_id
join employee e on i.emp_id = e.emp_id
where ir.rating {sign} {rating};
"""
cursor.execute(get_interviews_query)
interviews = make_dict_list_from_rows(cursor)
return render_template('query_interviews.html',
interviews=interviews,
sign=sign,
rating=rating)
except DBConnectionError as e:
return 'Произошла ошибка соединения'
except DBCredentialError as e:
return 'Не удается войти в бд'
except DBBaseError as e:
return 'Произошла непредвиденная ошибка бд'
except DBSQLError as e:
return 'Ошибка базы данных'
def interview_blueprint_route_root():
current_role = get_current_role()
if request.method != 'GET':
return 'Unknown action'
if current_role == 'guest':
return redirect('/menu')
elif current_role == 'admin':
return render_template('interview_menu.html')
elif current_role == 'worker':
return redirect('/interview/list')
else:
return 'You are not permitted to see this page'
def interview_blueprint_route_appointment_confirm():
current_role = get_current_role()
if current_role != 'admin':
return redirect('/menu')
if request.method != 'POST':
return 'Unknown action'
if 'appointment' not in session:
return redirect('/interview/list')
appointment = session['appointment']
interview_id = appointment['interview_id']
candidate_ids = appointment['candidate_ids']
if not interview_id or not candidate_ids:
return redirect('/interview/list')
try:
with UseDatabase(current_app.config['db'][current_role]) as cursor:
for candidate_id in candidate_ids:
appoint_candidates_query = f"""
insert
into interview_result (c_id, iv_id)
values ({candidate_id}, {interview_id})
"""
cursor.execute(appoint_candidates_query)
except DBConnectionError as e:
return 'Произошла ошибка соединения'
except DBCredentialError as e:
return 'Не удается войти в бд'
except DBBaseError as e:
return 'Произошла непредвиденная ошибка бд'
except DBSQLError as e:
return 'Ошибка базы данных'
session.pop('appointment')
return redirect('/interview/list')
def interview_blueprint_rout_appointment_clear_candidates():
current_role = get_current_role()
if current_role != 'admin':
return redirect('/menu')
if request.method != 'POST':
return 'Unknown action'
if 'appointment' not in session:
session['appointment'] = {'interview_id': None, 'candidate_ids': []}
appointment = session['appointment']
interview_id = appointment['interview_id']
session['appointment'] = {
'interview_id': interview_id,
'candidate_ids': []
}
return redirect('/interview/appointed')
def query_blueprint_route_employees():
current_role = get_current_role()
if current_role not in ['admin', 'worker']:
return redirect('/menu')
form = request.form if request.method == 'POST' else None
try:
with UseDatabase(current_app.config['db'][current_role]) as cursor:
if form and 'year' in form:
year = form.get('year')
else:
year = None
where_clause_query_chunk = \
f'and YEAR(layoff_date) = {year}' if year \
else ''
get_employees_query = f"""
select emp_id, name, salary, education, reception_date, layoff_date
from employee
where layoff_date is not null
{where_clause_query_chunk}
"""
cursor.execute(get_employees_query)
emps = make_dict_list_from_rows(cursor)
return render_template('query_employees.html',
emps=emps,
year=year)
except DBConnectionError as e:
return 'Произошла ошибка соединения'
except DBCredentialError as e:
return 'Не удается войти в бд'
except DBBaseError as e:
return 'Произошла непредвиденная ошибка бд'
except DBSQLError as e:
return 'Ошибка базы данных'
def query_blueprint_route_vacancies():
current_role = get_current_role()
if current_role not in ['admin', 'worker']:
return redirect('/menu')
form = request.form if request.method == 'POST' else None
try:
with UseDatabase(current_app.config['db'][current_role]) as cursor:
if form and 'year' in form:
year = form.get('year')
else:
year = None
where_clause_query_chunk = \
f'where YEAR(opening_date) = {year}' if year \
else ''
get_vacancies_query = f"""
select v_id, position, opening_date, closing_date
from vacancy
{where_clause_query_chunk}
"""
cursor.execute(get_vacancies_query)
vacancies = make_dict_list_from_rows(cursor)
return render_template('query_vacancies.html',
vacancies=vacancies,
year=year)
except DBConnectionError as e:
return 'Произошла ошибка соединения'
except DBCredentialError as e:
return 'Не удается войти в бд'
except DBBaseError as e:
return 'Произошла непредвиденная ошибка бд'
except DBSQLError as e:
return 'Ошибка базы данных'
def interview_blueprint_route_appointment_remove_candidate():
current_role = get_current_role()
if current_role != 'admin':
return redirect('/menu')
if 'appointment' not in session:
session['appointment'] = {'interview_id': None, 'candidate_ids': []}
appointment = session['appointment']
candidate_ids = appointment['candidate_ids']
candidate_id_to_remove = int(request.form.get('candidate_id'))
if candidate_id_to_remove in candidate_ids:
candidate_ids.remove(candidate_id_to_remove)
session['appointment'] = {
'interview_id': appointment['interview_id'],
'candidate_ids': candidate_ids
}
return redirect('/interview/appointed')
def report_blueprint_root():
if request.method != 'POST':
return 'Unknown action'
current_role = get_current_role()
if current_role not in ['admin']:
return redirect('/menu')
year = request.form.get('year')
try:
with UseDatabase(current_app.config['db'][current_role]) as cursor:
call_staff_turnover_query = f'call kursach.staff_turnover(\'{year}\')'
get_staff_turnover_records_count_query = f'select COUNT(*) as count from staff_turnover where st_year = {year}'
get_staff_turnover_records_query = f'select * from staff_turnover where st_year = {year}'
cursor.execute(get_staff_turnover_records_count_query)
result = make_dict_list_from_rows(cursor)
if not result[0]['count']:
cursor.execute(call_staff_turnover_query)
cursor.execute(get_staff_turnover_records_query)
records = make_dict_list_from_rows(cursor)
return render_template('report_result.html',
records=records,
year=year)
except DBConnectionError as e:
return 'Произошла ошибка соединения'
except DBCredentialError as e:
return 'Не удается войти в бд'
except DBBaseError as e:
return 'Произошла непредвиденная ошибка бд'
except DBSQLError as e:
return 'Ошибка базы данных'
def interview_blueprint_route_list():
current_role = get_current_role()
if request.method != 'GET':
return 'Unknown action'
if current_role not in ['admin', 'worker']:
return redirect('/menu')
try:
with UseDatabase(current_app.config['db'][current_role]) as cursor:
if current_role == 'admin':
get_interviews_query = """
select
i.iv_id,
i.salary,
e.name as employee,
i.iv_date,
v.position
from interview i
join employee e on e.emp_id = i.emp_id
join vacancy v on i.v_id = v.v_id
where iv_id in (select distinct iv_id from interview_result)
order by i.iv_id
"""
cursor.execute(get_interviews_query)
interviews = make_dict_list_from_rows(cursor)
get_candidates_query = """
select
ir.iv_id,
c.c_id,
c.name,
c.age,
c.gender,
c.address
from interview_result ir
join candidate c on ir.c_id = c.c_id
order by ir.iv_id;
"""
cursor.execute(get_candidates_query)
candidates = make_dict_list_from_rows(cursor)
for interview in interviews:
interview['candidates'] = [
candidate for candidate in candidates
if candidate['iv_id'] == interview['iv_id']
]
return render_template('interview_list_admin.html',
interviews=interviews)
elif current_role == 'worker':
# Добавить, чтоб выводились собсеседования конкретного employee
worker_id = get_current_user_id()
get_interview_query = f"""
SELECT
i.iv_date as date,
v.position,
i.salary,
c.name as candidate,
iv.result,
iv.rating
from interview_result iv
join interview i on iv.iv_id = i.iv_id
join candidate c on iv.c_id = c.c_id
join employee e on i.emp_id = e.emp_id
join vacancy v on i.v_id = v.v_id
where e.emp_id = {worker_id}
"""
cursor.execute(get_interview_query)
interviews = make_dict_list_from_rows(cursor)
return render_template('interview_list_worker.html',
interviews=interviews)
else:
return 'Error. You are not permitted to view this page'
except DBConnectionError as e:
return 'Произошла ошибка соединения'
except DBCredentialError as e:
return 'Не удается войти в бд'
except DBBaseError as e:
return 'Произошла непредвиденная ошибка бд'
except DBSQLError as e:
return 'Ошибка базы данных'
def interview_blueprint_route_appointed():
current_role = get_current_role()
if current_role != 'admin':
return redirect('/menu')
if request.method != 'GET':
return 'Unknown action'
if 'appointment' not in session:
session['appointment'] = {'interview_id': None, 'candidate_ids': []}
current_interview_id = session['appointment']['interview_id']
candidate_ids = session['appointment']['candidate_ids']
try:
with UseDatabase(current_app.config['db'][current_role]) as cursor:
if current_interview_id:
get_interviews_query = f"""
select
i.iv_id,
i.salary,
e.name as employee,
i.iv_date,
v.position
from interview i
join employee e on e.emp_id = i.emp_id
join vacancy v on i.v_id = v.v_id
where
iv_id = {current_interview_id}
order by i.iv_id;
"""
cursor.execute(get_interviews_query)
result_row = make_dict_list_from_rows(cursor)
interview = result_row[0] if result_row else None
else:
interview = None
if candidate_ids:
get_appointed_candidates_query = f"""
select c_id, name, age, gender, address
from candidate
where c_id in ({','.join(str(id) for id in candidate_ids)})
"""
cursor.execute(get_appointed_candidates_query)
candidates = make_dict_list_from_rows(cursor)
else:
candidates = []
except DBConnectionError as e:
return 'Произошла ошибка соединения'
except DBCredentialError as e:
return 'Не удается войти в бд'
except DBBaseError as e:
return 'Произошла непредвиденная ошибка бд'
except DBSQLError as e:
return 'Ошибка базы данных'
return render_template('interview_appointed_admin.html',
interview=interview,
candidates=candidates if candidates else None)