def response_vacancy():
try:
database = Database()
except TypeError:
return jsonify({"messageError": "Нет подключения к БД"})
directories = [(None, "firstname"), (None, "lastname"),
(None, "number_phone"), (None, "skills"),
(None, "technologies_and_tools"), (None, "vacancy_id")]
response_vacancy_data = request.get_json(silent=True)
if not response_vacancy_data:
return jsonify(text="JSON не найден"), 204
check = check_fields(directories, response_vacancy_data)
if type(check) == list:
return "В json отсутсвуют поля: {}".format(",".join(i for i in check))
candidate_id = insert_candidate(database, response_vacancy_data)
check = union_candidate_and_skills(database, candidate_id,
response_vacancy_data)
if response_vacancy_data.get('answers'):
check = write_answers(database, candidate_id,
response_vacancy_data['answers'])
database.close()
return jsonify(check)
def registration():
user_data = request.get_json(silent=True)
if not user_data:
return jsonify(text="JSON не найден"), 204
database = None
try:
database = Database()
valid = valid_user_data(database, user_data)
if valid != True:
return jsonify(valid), 400
salt = uuid.uuid4().hex
user_data['password'] = hash_password(user_data['password'], salt)
valid = add_to_database(database, user_data)
if valid != True:
return jsonify(valid), 500
user_data["uuid"] = get_user_id(database, user_data['email'])[0][0]
valid = insert_salt_for_user(database, user_data["uuid"], salt)
if valid != True:
return jsonify(valid), 500
except TypeError:
return jsonify({"messageError": "Нет подключения к БД"}), 500
finally:
database.close()
return jsonify("Пользователь успешно зарегестрирован")
def change_status_vacancy():
user = check_auth(request.headers, __name__)
if user != True:
return user
user = authorize.get(request.headers.get('UserToken'))
try:
database = Database()
except TypeError:
return jsonify({"messageError": "Нет подключения к БД"})
change_status_data = request.get_json(silent=True)
if not change_status_data:
return jsonify(text="JSON не найден"), 204
directories = [(None, "status_id"), (None, "vacancy_id")]
check = check_fields(directories, change_status_data)
if type(check) == list:
return "В json отсутсвуют поля: {}".format(",".join(i for i in check))
result = database.insert_data(
sql.SQL(
"UPDATE vacancy SET status_id={status_id} WHERE id={vacancy_id}").
format(status_id=sql.Literal(change_status_data['status_id']),
vacancy_id=sql.Literal(change_status_data['vacancy_id'])))
database.close()
return jsonify(result)
def get_directories_for_vacancies():
user = check_auth(request.headers, __name__)
if user != True:
return user
user = authorize.get(request.headers.get('UserToken'))
try:
database = Database()
except TypeError:
return jsonify({"messageError": "Нет подключения к БД"})
result = {"professional_area": {}}
directories = [(["id", "title"], "skills"),
(["id", "title"], "work_address"),
(["id", "title"], "type_employment"),
(["id", "title"], "soft_requirements"),
(["id", "title"], "technologies_and_tools"),
(["id", "title", "description"], "grade"),
(["id", "title", "description"], "working_conditions"),
(["id", "title", "description"], "job_responsibilities"),
(["id", "title", "description"], "special_advantages")]
for fields, table in directories:
result[table] = select_info(database, fields, table)
res = database.select_data("""
SELECT
s.id,
s.title,
s.description,
pa.id,
pa.title
FROM specializations s
LEFT JOIN professional_area pa on pa.id = s.professional_area_id
""")
for id, title, description, pa_id, pa_title in res:
if not result["professional_area"].get(pa_title):
result["professional_area"][pa_title] = {
"specializations": [],
"title": pa_title,
"id": pa_id
}
result["professional_area"][pa_title]["specializations"].append({
"id":
id,
"title":
title,
"description":
description
})
result["professional_area"] = [
result["professional_area"][i] for i in result["professional_area"]
]
database.close()
return jsonify(result)
def create_new_vacancy():
user = check_auth(request.headers, __name__)
if user != True:
return user
user = authorize.get(request.headers.get('UserToken'))
try:
database = Database()
except TypeError:
return jsonify({"messageError": "Нет подключения к БД"})
vacancy_data = request.get_json(silent=True)
if not vacancy_data:
return jsonify(text="JSON не найден"), 204
directories = [(["title"], "skills"), (["title"], "work_address"),
(["title"], "type_employment"),
(["title"], "soft_requirements"),
(["title"], "professional_area"),
(["title"], "technologies_and_tools"),
(["title", "description"], "grade"),
(["title", "description"], "working_conditions"),
(["title", "description"], "job_responsibilities"),
(["title", "description"], "special_advantages"),
(["title", "description",
"professional_area_id"], "specializations")]
check = check_fields(directories, vacancy_data)
if type(check) == list:
return "В json отсутсвуют поля: {}".format(",".join(i for i in check))
values = {}
for fields, table in directories:
for value in vacancy_data[table]:
if not values.get(table):
values[table] = []
if type(value) == int:
values[table].append(value)
elif type(value) == list:
if table == "specializations":
value.append(values["professional_area"][0])
id = insert_new_data_in_directory(database, fields, value,
table)
if type(id) == int:
values[table].append(id)
elif type(value) == str:
id = insert_new_data_in_directory(database, fields, [value],
table)
if type(id) == int:
values[table].append(id)
values["is_testing"] = bool(vacancy_data['is_testing'])
check = insert_vacancy(database, values, user)
database.close()
return jsonify(check)
def get_all_vacancy():
user = check_auth(request.headers, __name__)
if user == True:
user = authorize.get(request.headers.get('UserToken'))
try:
database = Database()
except TypeError:
return jsonify({"messageError": "Нет подключения к БД"})
status_id = [2]
fields = [("v", "id"), ("v", "create_date"), ("sp", "title"),
("pa", "title"), ("sv", "title")]
if type(user) != tuple:
if user.get_role() == 1:
status_id = [1, 2, 3]
result = []
res = database.select_data(
sql.SQL("""
SELECT
{fields}
FROM vacancy v
LEFT JOIN specializations sp on sp.id = v.specializations_id
LEFT JOIN professional_area pa on pa.id = sp.professional_area_id
LEFT JOIN statuses_vacancy sv on sv.id = v.status_id
WHERE
status_id in ({status_id})
""").format(fields=sql.SQL(",").join(
sql.Identifier(i[0], i[1]) for i in fields),
status_id=sql.SQL(",").join(sql.Literal(i)
for i in status_id)))
if res:
for id, dt, specialization, professional_area, status in res:
result.append({
"id":
id,
"dt":
time.mktime(
datetime.datetime.strptime(dt.strftime("%d/%m/%Y"),
"%d/%m/%Y").timetuple()),
"specialization":
specialization,
"professional_area":
professional_area
})
if type(user) != tuple:
if user.get_role() == 1:
result[-1]['status'] = status
database.close()
return jsonify(result)
def get_questions_for_teseting(vacancy_id):
try:
database = Database()
except TypeError:
return jsonify({"messageError": "Нет подключения к БД"})
result = {}
questons = database.select_data(sql.SQL("""
SELECT
q.id questons_id,
qt.title question_type_title,
qt.description question_type_description,
g.title grade_title,
s.title skills_title,
q.title questons_title,
q.question questons_question,
aoq.id answer_id,
aoq.answer answer_text
FROM questons q
LEFT JOIN grade g on q.grade_id = g.id
LEFT JOIN skills s on q.skill_id = s.id
LEFT JOIN question_types qt on q.question_type_id = qt.id
LEFT JOIN answers_on_question aoq on q.id = aoq.question_id
WHERE
s.id in (SELECT skill_id FROM vacancy v WHERE v.id={vacancy_id});
""").format(vacancy_id=sql.Literal(vacancy_id)))
for quest in questons:
if result.get(quest['questons_id']):
result[quest['questons_id']]['answers'].append((quest['answer_id'], quest['answer_text']))
else:
res = {
"answers": []
}
for key in quest.keys():
if not key.split('_')[0] == 'answer':
res[key] = quest[key]
res["answers"].append((quest['answer_id'], quest['answer_text']))
result[res['questons_id']] = res
if len(result) == 4:
result.pop(res['questons_id'])
break
result = [result[key] for key in result]
database.close()
return jsonify(result)
def get_directories_for_response_vacancy():
try:
database = Database()
except TypeError:
return jsonify({"messageError": "Нет подключения к БД"})
result = {}
directories = [(["id", "title"], "skills"),
(["id", "title"], "technologies_and_tools")]
for fields, table in directories:
result[table] = select_info(database, fields, table)
database.close()
return jsonify(result)
def get_statuses_vacancy():
user = check_auth(request.headers, __name__)
if user != True:
return user
user = authorize.get(request.headers.get('UserToken'))
try:
database = Database()
except TypeError:
return jsonify({"messageError": "Нет подключения к БД"})
result = []
statuses = database.select_data("SELECT id, title FROM statuses_vacancy")
for id, title in statuses:
result.append({"id": id, "title": title})
database.close()
return jsonify(result)
def get_directories_for_questions():
user = check_auth(request.headers, __name__)
if user != True:
return user
user = authorize.get(request.headers.get('UserToken'))
try:
database = Database()
except TypeError:
return jsonify({"messageError": "Нет подключения к БД"})
result = {}
directories = [(["id", "title"], "skills"),
(["id", "title", "description"], "grade"),
(["id", "title", "description"], "question_types")]
for fields, table in directories:
result[table] = select_info(database, fields, table)
database.close()
return jsonify(result)
def create_questions():
user = check_auth(request.headers, __name__)
if user != True:
return user
user = authorize.get(request.headers.get('UserToken'))
try:
database = Database()
except TypeError:
return jsonify({"messageError": "Нет подключения к БД"})
vacancy_data = request.get_json(silent=True)
if not vacancy_data:
return jsonify(text="JSON не найден"), 204
directories = [(["title"], "skills"), (["title", "description"], "grade"),
(["title", "description"], "question_types"),
(None, "title"), (None, "question"),
(None, "answers_on_question")]
check = check_fields(directories, vacancy_data)
if type(check) == list:
return "В json отсутсвуют поля: {}".format(",".join(i for i in check))
values = {}
for fields, table in directories:
values[table] = []
if table in ["title", "question", "skills", "grade", "question_types"]:
values[table] = vacancy_data[table]
elif table == 'answers_on_question':
for value in vacancy_data[table]:
values[table].append(value)
question_id = insert_questions(database, values)
check = insert_answers(database, question_id,
values['answers_on_question'])
database.close()
return jsonify(check)
def analiz_all_candidates():
user = check_auth(request.headers, __name__)
if user != True:
return user
user = authorize.get(request.headers.get('UserToken'))
try:
database = Database()
except TypeError:
return jsonify({"messageError": "Нет подключения к БД"})
result = {}
candidates = database.select_data("""
SELECT
c.id candidates_id,
c.firstname candidates_firstname,
c.lastname candidates_lastname,
v.id vacancy_id,
s.title specializations_title,
sc.title status_title,
get_score_candidate(c.id) score
FROM candidates c
LEFT JOIN answer_on_question_candidate aoqc on c.id = aoqc.candidate_id
LEFT JOIN vacancy v on c.vacancy_id = v.id
LEFT JOIN specializations s on v.specializations_id = s.id
LEFT JOIN statuses_candidate sc on c.status_id = sc.id
""")
for candidat in candidates:
candidates_id = candidat['candidates_id']
result[candidates_id] = {}
for key in candidat.keys():
result[candidates_id][key] = candidat[key]
result = [result[key] for key in result]
database.close()
return jsonify(result)
def authorization():
user_data = request.get_json(silent=True)
if not user_data:
return jsonify(text="JSON не найден"), 204
login = user_data.get("login")
password = user_data.get("password")
database = None
try:
database = Database()
user_data = database.login(login)
if type(user_data) == str:
return jsonify({"messageError": user_data}), 500
if user_data:
if check_password(user_data, password):
if user_data["status_active"] == True:
user = User(id=user_data["id"],
username=user_data["username"],
role=user_data["role"])
authorize[user.get_token()] = user
database.insert_data("UPDATE users SET last_login=now() \
WHERE username='******'".format(
username=user.get_username()))
return jsonify({
"UserToken": user.get_token(),
"role": user.get_role()
})
return jsonify({'message': 'Пользователь заблокирован'}), 401
return jsonify({'message': 'Неправильный логин или пароль'}), 401
except TypeError:
return jsonify({"messageError": "Нет подключения к БД"}), 500
finally:
database.close()
return jsonify(True)
def get_vacancy(vacancy_id):
try:
database = Database()
except TypeError:
return jsonify({"messageError": "Нет подключения к БД"})
result = {"specialization": {}, "grade": {}}
res = database.select_data(
sql.SQL("""
SELECT
v.create_date dt,
sp.title specialization_title,
sp.description specialization_description,
g.title grade_title,
g.description grade_description,
wa.title work_address,
pa.title professional_area
FROM vacancy v
LEFT JOIN specializations sp on sp.id = v.specializations_id
LEFT JOIN grade g on g.id = v.grade_id
LEFT JOIN work_address wa on wa.id = v.work_address_id
LEFT JOIN professional_area pa on pa.id = sp.professional_area_id
WHERE
v.id in ({})
""").format(sql.Literal(vacancy_id)))
if res:
for row in res:
for key in row.keys():
if key.split("_")[-1] in ["title", "description"]:
result["_".join(i for i in (
key.split("_")[:-1]))][key.split("_")[-1]] = row[key]
elif not key == 'dt':
result[key] = row[key]
else:
result[key] = time.mktime(
datetime.datetime.strptime(
row[key].strftime("%d/%m/%Y"),
"%d/%m/%Y").timetuple())
else:
return jsonify({"message": "Вакансия не найдена"}), 404
params_data = [{
"fields": ["title"],
"table": "skills_for_a_vacancy",
"module_table": "skills",
"module": "skill_id"
}, {
"fields": ["title"],
"table": "type_employment_for_a_vacancy",
"module_table": "type_employment",
"module": "type_employment_id"
}, {
"fields": ["title"],
"table": "soft_requirement_for_a_vacancy",
"module_table": "soft_requirements",
"module": "soft_requirement_id"
}, {
"fields": ["title"],
"table": "technologies_and_tools_for_a_vacancy",
"module_table": "technologies_and_tools",
"module": "technologies_and_tools_id"
}, {
"fields": ["title", "description"],
"table": "working_condition_for_a_vacancy",
"module_table": "working_conditions",
"module": "working_condition_id"
}, {
"fields": ["title", "description"],
"table": "job_responsibilities_for_a_vacancy",
"module_table": "job_responsibilities",
"module": "job_responsibilities_id"
}, {
"fields": ["title", "description"],
"table": "special_advantage_for_a_vacancy",
"module_table": "special_advantages",
"module": "special_advantage_id"
}]
for param in params_data:
result[param['module_table']] = []
res = get_data_from_directory(database, param, vacancy_id)
for row in res:
if len(param['fields']) == 1:
result[param['module_table']].append(row[0])
else:
module = {}
for key in row.keys():
module[key] = row[key]
result[param['module_table']].append(module)
database.close()
return jsonify(result)