def view_log() -> str:
"""Display the contents of the log file as a String table.
Don't import mysql errors here, since it will tightly couple your code with MySQL code.
All the code specific to Database should be inside dbcm.py only.
"""
try:
with UseDatabase(app.config['dbconfig']) as cursor:
_SQL = """select phrase, letters, ip, browser_string, results
from log"""
cursor.execute(_SQL)
contents = cursor.fetchall()
titles = ('Phrase', 'Letters', 'Remote Address', 'User agent',
'Results')
return render_template(
'viewlog.html',
the_title='View Log',
the_row_titles=titles,
the_data=contents,
)
except DBConnectionError as err:
print('Is your database switched on? Error:', str(err))
except CredentialsError as err:
print('User-id/Password issues. Error:', str(err))
except SQLError as err:
print('Is your query correct? Error:', str(err))
except Exception as err:
print('Something went wrong:', str(err))
return 'Error'
def request1():
_SQL = """SELECT id_struct, unit_vac, COUNT(*) as count
FROM `recruiting`.`vacancy`
WHERE YEAR(date_open) = %s
GROUP BY id_struct;
"""
if request.args.get('return'):
return redirect(url_for('requests_menu'))
year = request.form.get('year')
if year:
try:
with UseDatabase(session['db_config']) as cursor:
keys = ['id_struct', 'unit_vac', 'count']
values = (year, )
result = make_request(cursor, _SQL, values, keys)
return render_template('request1_result.html',
report=result,
year=year)
except DatabaseError:
return render_template(
'error.html',
error_msg="Не удалось подключиться к базе данных.")
except InterfaceError:
return render_template('error.html', error_msg="Ошибка.")
except ProgrammingError:
return render_template('error.html',
error_msg="Не удалось выполнить запрос!")
return render_template('params_form_enter_year.html', request=1)
def save_interview():
if not request.form.get('save'):
return redirect(url_for('interview_bp.add_interview'))
try:
with UseDatabase(session['db_config']) as cursor:
_SQL = f"""INSERT INTO `recruiting`.`interview`(`date_int`, interview.`id_worker`, interview.`id_cand`, interview.`id_vac`)
VALUES('{session['date']}', {session['recruiter'][0]}, {session['candidate'][0]}, {session['vacancy'][0]});"""
print(_SQL)
cursor.execute(_SQL)
except ProgrammingError:
return render_template('error.html',
error_msg="Не удалось записать протокол!")
except InterfaceError:
return render_template('error.html', error_msg="Ошибка.")
except DatabaseError:
return render_template(
'error.html', error_msg="Не удалось подключиться к базе данных.")
session.pop('recruiter')
session.pop('vacancy')
session.pop('candidate')
session.pop('date')
return render_template('result.html')
def check_user(login, password):
with open('data_files/config.json') as f:
config = json.load(f)
with UseDatabase(config) as cursor:
_SQL = """
select `group_login`, `group_pass`
from recruiting.check_users
where user_login=%s and user_pass=%s
"""
cursor.execute(_SQL, (login, password))
result = cursor.fetchall()
keys = ['login', 'password']
result = [dict(zip(keys, user)) for user in result]
session['db_config'] = config
return result
def request2():
_SQL = """
SELECT `co-worker`.*
FROM `recruiting`.`co-worker`
JOIN staffing_table ON `co-worker`.id_struct = staffing_table.id_struct
WHERE unit=%s AND date_dis IS NULL
ORDER BY birth DESC
LIMIT 1;
"""
if request.args.get('return'):
return redirect(url_for('requests_menu'))
text = request.form.get('text')
if text:
try:
with UseDatabase(session['db_config']) as cursor:
keys = [
'id_work', 'FName_worker', 'birth', 'address_w', 'educat',
'salary', 'date_res', 'date_dis', 'id_struct'
]
values = (text, )
result = make_request(cursor, _SQL, values, keys)
return render_template('request2_result.html',
report=result,
text=text)
except DatabaseError:
return render_template(
'error.html',
error_msg="Не удалось подключиться к базе данных.")
except InterfaceError:
return render_template('error.html', error_msg="Ошибка.")
except ProgrammingError:
return render_template('error.html',
error_msg="Не удалось записать протокол!")
return render_template('params_form_enter_depart_name.html', request=2)
def request3():
_SQL = """
SELECT staffing_table.*
FROM `recruiting`.`staffing_table`
LEFT JOIN (SELECT *
FROM `recruiting`.`vacancy`
WHERE YEAR(vacancy.date_open) = %s) AS year_vacancy
ON staffing_table.id_struct = year_vacancy.id_struct
WHERE year_vacancy.id_struct IS NULL;
"""
if request.args.get('return'):
return redirect(url_for('requests_menu'))
year = request.form.get('year')
if year:
try:
with UseDatabase(session['db_config']) as cursor:
keys = ['id_struct', 'unit', 'minmax_salary']
values = (year, )
result = make_request(cursor, _SQL, values, keys)
return render_template('request3_result.html',
report=result,
year=year)
except DatabaseError:
return render_template(
'error.html',
error_msg="Не удалось подключиться к базе данных.")
except InterfaceError:
return render_template('error.html', error_msg="Ошибка.")
except ProgrammingError:
return render_template('error.html',
error_msg="Не удалось записать протокол!")
return render_template('params_form_enter_year.html', request=3)
def log_request(req: 'flask_request', res: str) -> None:
"""Log details of the web request and the results."""
with open(LOG_FILE, 'a') as app_log:
print(req.form,
req.remote_addr,
req.user_agent,
req.form['phrase'],
req.form['letters'],
res,
file=app_log,
sep='|')
with UseDatabase(app.config['dbconfig']) as cursor:
_SQL = """insert into log
(phrase, letters, ip, browser_string, results)
values
(%s, %s, %s, %s, %s)"""
cursor.execute(_SQL, (
req.form['phrase'],
req.form['letters'],
req.remote_addr,
req.user_agent.browser,
res,
))
def proc():
year = request.form.get('year')
if year:
try:
with UseDatabase(session['db_config']) as cursor:
strnum = proccheck(cursor, year)
if strnum == 0:
cursor.callproc('annual_report', (year, ))
result = get_report(cursor, year)
return render_template('report.html', table=result)
except ProgrammingError:
return render_template('error.html',
error_msg="Не удалось записать протокол!")
except InterfaceError:
return render_template('error.html', error_msg="Ошибка.")
except DatabaseError:
return render_template(
'error.html',
error_msg="Не удалось подключиться к базе данных.")
return render_template('proc.html')
def add_interview():
try:
with UseDatabase(session['db_config']) as cursor:
return_to = request.args.get('return')
if return_to:
return to_page(cursor, return_to)
recruiter = request.form.get('recruiter')
vacancy = request.form.get('vacancy')
candidate = request.form.get('candidate')
date = request.form.get('date')
if recruiter:
print('---recr')
session['recruiter'][1] = recruiter
session['recruiter'][0] = get_id_from_session(
session['recruiter'][0], recruiter)
vacancy = get_vacancy(cursor)
session['vacancy'] = ['', '']
session['vacancy'][0] = vacancy
print(session['recruiter'])
session.update()
return render_template('form_parts.html',
part='vacancy',
vacancy=vacancy)
elif vacancy:
print('---vac')
session['vacancy'][1] = vacancy
session['vacancy'][0] = get_id_from_session(
session['vacancy'][0], vacancy)
candidate = get_candidate(cursor)
session['candidate'] = ['', '']
session['candidate'][0] = candidate
print(session['recruiter'])
print(session['vacancy'])
print(session['candidate'])
session.update()
return render_template('form_parts.html',
part='candidate',
candidate=candidate)
elif candidate:
print('---cand')
session['candidate'][1] = candidate
session['candidate'][0] = get_id_from_session(
session['candidate'][0], candidate)
print(session['recruiter'])
print(session['vacancy'])
print(session['candidate'])
session.update()
return render_template('form_parts.html', part='date')
elif date:
print('---date')
session['date'] = date
print(session['recruiter'])
print(session['vacancy'])
print(session['candidate'])
print(session['date'])
return render_template('confirm.html',
recruiter=session['recruiter'][1],
vacancy=session['vacancy'][1],
candidate=session['candidate'][1],
date=session.get('date'))
else:
recruiter = get_recruiter(cursor)
session['recruiter'] = ['', '']
session['recruiter'][0] = recruiter
session.update()
return render_template('form_parts.html',
part='recruiter',
recruiter=recruiter)
except ProgrammingError:
return render_template('error.html',
error_msg="Не удалось записать протокол!")
except InterfaceError:
return render_template('error.html', error_msg="Ошибка.")
except DatabaseError:
return render_template(
'error.html', error_msg="Не удалось подключиться к базе данных.")