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 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_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 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 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)