def search(searchType,searchValue = None):
searchType = int(searchType)
if searchType == 1:## sort acedmic seach
query = "SELECT id FROM `candidateInformation` WHERE `sortAcademic` == {0}".format(int(searchValue))
if sqllite.countRow(sqllite.query(query)) == 0:
global_var.main_output["add"]("status", 15)
global_var.main_output["add_log"]("0 row found for this searching query.")
else:
global_var.main_output["add"]("status", 16)
global_var.main_output["add_log"]("{0} row found for this searching query.".format(sqllite.countRow(sqllite.query(query))))
resualt = ""
rows = sqllite.query(query).fetchall()
for row in rows:
resualt += str(row[0]) +","
resualt = resualt[:-1]
global_var.main_output["add"]("result",resualt)
rows = sqllite.query(query).fetchall()
elif searchType == 2:## academic history search
query = "SELECT id FROM `researchersAndProject` WHERE `description` == {0}".format(int(searchValue))
if sqllite.countRow(sqllite.query(query)) == 0:
global_var.main_output["add"]("status", 15)
global_var.main_output["add_log"]("0 row found for this searching query.")
else:
pass
elif searchType == 3:
pass
def check_user_exist(userName):
query = "select * from candidate_users where user='******'"
if sqllite.countRow(sqllite.query(query)) == 1:
return True
query = "select * from HR_users where user='******'"
if sqllite.countRow(sqllite.query(query)) == 1:
return True
return False
def sign_up_multi(file_path):
""""
function sign up multiply user with one json data file which generate with mockaroo service
@:param: file_path: location to the mockaroo json file
@:type: str
"""
data = jsonLoad.import_json(file_path)
for row in data:
signup.sign_up(row['userName'],str(row['userPassword']))
query = "select id from candidate_users where user='******'userName']) + "" \
"' and password='******'userPassword'])) + "'"
candidate.insert_candidate_information(row,(sqllite.query(query).fetchone())[0])
def authentication(userName, password):
global_var.user_name = userName
global_var.user_password = str(password_encrypet(password))
query1 = "select id from HR_users where user='******' and password='******'"
query2 = "select id from candidate_users where user='******' and password='******'"
if sqllite.countRow(sqllite.query(query1)) == 1 or sqllite.countRow(
sqllite.query(query2)) == 1:
if (sqllite.countRow(sqllite.query(query1)) == 1):
global_var.user_id = (sqllite.query(query1).fetchone()[0])
elif sqllite.countRow(sqllite.query(query2)) == 1:
global_var.user_id = (sqllite.query(query2).fetchone()[0])
return True
return False
def insert_candidate_information(data):
""""
function storing data in the database
"""
######################################## delete old record ############################################
query = "DELETE FROM `education` WHERE `mid`== {0}".format(global_var.user_id)
sqllite.query(query)
query = "DELETE FROM `notes` WHERE `mid`== {0}".format(global_var.user_id)
sqllite.query(query)
query = "DELETE FROM `previousCareer` WHERE `mid`== {0}".format(global_var.user_id)
sqllite.query(query)
query = "DELETE FROM `researchersAndProject` WHERE `mid`== {0}".format(global_var.user_id)
sqllite.query(query)
query = "DELETE FROM `specillaztion` WHERE `mid`== {0}".format(global_var.user_id)
sqllite.query(query)
############################# AcadmicHistory - >education #######################
i = 1
for k, v in data['AcadmicHistory']['education'].items():
query1 = "INSERT INTO `education`(`id`,`date`,`description`,`mid`,`sn`) VALUES (NULL,'{0}','{1}','{2}',{3});".format(
v["date"], v["description"], global_var.user_id, i)
i += 1
sqllite.insert__update_query(query1)
############################# AcadmicHistory->researchersAndProject ##############################################
i = 1
for k, v in data['AcadmicHistory']['researchersAndProject'].items():
query1 = "INSERT INTO `researchersAndProject`(`id`,`date`,`description`,`mid`,`sn`) VALUES (NULL,'{0}','{1}','{2}',{3});".format(
v["date"], v["description"], global_var.user_id, i)
i += 1
sqllite.insert__update_query(query1)
############################# expAndSkill->previousCareer ##############################################
i = 1
for k, v in data['expAndSkill']['previousCareer'].items():
query1 = "INSERT INTO `previousCareer`(`id`,`date`,`description`,`mid`,`sn`) VALUES (NULL,'{0}','{1}','{2}',{3});".format(
v["date"], v["description"], global_var.user_id, i)
i += 1
sqllite.insert__update_query(query1)
############################# expAndSkill->specillaztion ##############################################
i = 1
for k, v in data['expAndSkill']['specillaztion'].items():
query1 = "INSERT INTO `specillaztion`(`id`,`date`,`description`,`mid`,`sn`) VALUES (NULL,'{0}','{1}','{2}',{3});".format(
v["date"], v["description"], global_var.user_id, i)
i += 1
sqllite.insert__update_query(query1)
############################# note ##############################################
i = 1
for k, v in data['notes'].items():
query1 = "INSERT INTO `notes`(`id`,`date`,`description`,`mid`,`sn`) VALUES (NULL,NULL,'{0}','{1}',{2});".format(
v["description"], global_var.user_id, i)
i += 1
sqllite.insert__update_query(query1)
query = "SELECT * FROM `candidateInformation` WHERE `mid` == '{0}'".format(global_var.user_id)
if sqllite.countRow(sqllite.query(query)) == 0:
query = "INSERT INTO `candidateInformation`(`id`,`name`,`email`,`phoneNumber`," \
"`status`,`mid`) " \
"VALUES (NULL ,'{0}','{1}','{2}',0,'{3}');".format(data['name'],
data['email'],
data['phoneNumber'],
global_var.user_id)
sqllite.insert__update_query(query)
else:
query = "UPDATE `candidateInformation` SET `name` ='{0}', `email` ='{1}', `phoneNumber`='{2}'" \
" WHERE `mid`='{3}';".format(data['name'], data['email'], data['phoneNumber'], global_var.user_id)
sqllite.insert__update_query(query)
def get_candidate_card(id):
json_data = {'notes': {}, 'image': 'https://imagelocation',
'expAndSkill': {'specillaztion': {}, 'previousCareer': {}},
'sortAcademic': {}, 'phoneNumber': '',
'AcadmicHistory': {'education': {}, 'researchersAndProject': {}},
'status': {'1': {'hired': 0}, '3': {'onhold': 1}, '2': {'rejected': 0}},
'email': '*****@*****.**',
'name': 'YoureFullName'}
query = "SELECT * FROM `candidateInformation` WHERE `mid` == '{0}'".format(id)
if sqllite.countRow(sqllite.query(query)) == 0:
global_var.main_output["add"]("status", 10)
global_var.main_output["add_log"](
"candidate {0} not exist.".format(id))
return
row = sqllite.query(query).fetchone()
json_data['id'] = row[5]
json_data['name'] = row[1]
json_data['email'] = row[2]
json_data['phoneNumber'] = row[3]
json_data['image'] = row[6]
filename = "candidate." + str(id)
################################## AcadmicHistory->education ###########################################
query = "SELECT * FROM `education` WHERE `mid` == '{0}'".format(id)
tmp = {}
i = 1
for row in sqllite.query(query).fetchall():
tmp[i] = {}
tmp[i]["date"] = row[1]
tmp[i]["description"] = row[2]
i += 1
json_data["AcadmicHistory"]["education"] = tmp
################################## AcadmicHistory->researchersAndProject ###########################################
query = "SELECT * FROM `researchersAndProject` WHERE `mid` == '{0}'".format(id)
tmp = {}
i = 1
for row in sqllite.query(query).fetchall():
tmp[i] = {}
tmp[i]["date"] = row[1]
tmp[i]["description"] = row[2]
i += 1
json_data["AcadmicHistory"]["researchersAndProject"] = tmp
################################## expAndSkill->specillaztion ###########################################
query = "SELECT * FROM `specillaztion` WHERE `mid` == '{0}'".format(id)
tmp = {}
i = 1
for row in sqllite.query(query).fetchall():
tmp[i] = {}
tmp[i]["date"] = row[1]
tmp[i]["description"] = row[2]
i += 1
json_data["expAndSkill"]["specillaztion"] = tmp
################################## expAndSkill->previousCareer ###########################################
query = "SELECT * FROM `previousCareer` WHERE `mid` == '{0}'".format(id)
tmp = {}
i = 1
for row in sqllite.query(query).fetchall():
tmp[i] = {}
tmp[i]["date"] = row[1]
tmp[i]["description"] = row[2]
i += 1
json_data["expAndSkill"]["specillaztion"] = tmp
################################## notes ###########################################
query = "SELECT * FROM `notes` WHERE `mid` == '{0}'".format(id)
tmp = {}
i = 1
for row in sqllite.query(query).fetchall():
tmp[i] = {}
tmp[i]["date"] = row[1]
tmp[i]["description"] = row[2]
i += 1
json_data["notes"] = tmp
############################# save json file in files folder ###############################
with open(("files/" + filename + '.json'), 'w') as outfile:
json.dump(json_data, outfile, indent=2)
############################# log event ####################################################
global_var.main_output["add"]("status", 9)
global_var.main_output["add"]("file_location", "files/" + filename+".json")
global_var.main_output["add_log"](
"candidate file saves as {0}.json in folder files.".format(filename))
def insert_candidate_information(data):
""""
function storing data in the database
"""
######################################## delete old record ############################################
query = "DELETE FROM `education` WHERE `mid`== {0}".format(global_var.user_id)
sqllite.query(query)
query = "DELETE FROM `notes` WHERE `mid`== {0}".format(global_var.user_id)
sqllite.query(query)
query = "DELETE FROM `previousCareer` WHERE `mid`== {0}".format(global_var.user_id)
sqllite.query(query)
query = "DELETE FROM `researchersAndProject` WHERE `mid`== {0}".format(global_var.user_id)
sqllite.query(query)
query = "DELETE FROM `specillaztion` WHERE `mid`== {0}".format(global_var.user_id)
sqllite.query(query)
############################# AcadmicHistory - >education #######################
i = 1
for k, v in data['AcadmicHistory']['education'].items():
query1 = "INSERT INTO `education`(`id`,`date`,`description`,`mid`,`sn`) VALUES (NULL,'{0}','{1}','{2}',{3});".format(
v["date"], v["description"], global_var.user_id, i)
i += 1
sqllite.insert__update_query(query1)
############################# AcadmicHistory->researchersAndProject ##############################################
i = 1
for k, v in data['AcadmicHistory']['researchersAndProject'].items():
query1 = "INSERT INTO `researchersAndProject`(`id`,`date`,`description`,`mid`,`sn`) VALUES (NULL,'{0}','{1}','{2}',{3});".format(
v["date"], v["description"], global_var.user_id, i)
i += 1
sqllite.insert__update_query(query1)
############################# expAndSkill->previousCareer ##############################################
i = 1
for k, v in data['expAndSkill']['previousCareer'].items():
query1 = "INSERT INTO `previousCareer`(`id`,`date`,`description`,`mid`,`sn`) VALUES (NULL,'{0}','{1}','{2}',{3});".format(
v["date"], v["description"], global_var.user_id, i)
i += 1
sqllite.insert__update_query(query1)
############################# expAndSkill->specillaztion ##############################################
i = 1
for k, v in data['expAndSkill']['specillaztion'].items():
query1 = "INSERT INTO `specillaztion`(`id`,`date`,`description`,`mid`,`sn`) VALUES (NULL,'{0}','{1}','{2}',{3});".format(
v["date"], v["description"], global_var.user_id, i)
i += 1
sqllite.insert__update_query(query1)
############################# note ##############################################
i = 1
for k, v in data['notes'].items():
query1 = "INSERT INTO `notes`(`id`,`date`,`description`,`mid`,`sn`) VALUES (NULL,NULL,'{0}','{1}',{2});".format(
v["description"], global_var.user_id, i)
i += 1
sqllite.insert__update_query(query1)
query = "SELECT * FROM `candidateInformation` WHERE `mid` == '{0}'".format(global_var.user_id)
if sqllite.countRow(sqllite.query(query)) == 0:
query = "INSERT INTO `candidateInformation`(`id`,`name`,`email`,`phoneNumber`," \
"`status`,`mid`) " \
"VALUES (NULL ,'{0}','{1}','{2}',0,'{3}');".format(data['name'],
data['email'],
data['phoneNumber'],
global_var.user_id)
sqllite.insert__update_query(query)
else:
query = "UPDATE `candidateInformation` SET `name` ='{0}', `email` ='{1}', `phoneNumber`='{2}'" \
" WHERE `mid`='{3}';".format(data['name'], data['email'], data['phoneNumber'], global_var.user_id)
sqllite.insert__update_query(query)
def export_json_file():
json_data = {'notes': {}, 'image': 'https://imagelocation',
'expAndSkill': {'specillaztion': {}, 'previousCareer': {}},
'sortAcademic': {}, 'phoneNumber': '',
'AcadmicHistory': {'education': {}, 'researchersAndProject': {}},
'status': {'1': {'hired': 0}, '3': {'onhold': 1}, '2': {'rejected': 0}},
'email': '*****@*****.**',
'name': 'YoureFullName'}
query = "SELECT * FROM `candidateInformation` WHERE `mid` == '{0}'".format(global_var.user_id)
row = sqllite.query(query).fetchone()
print(row[1])
json_data['name'] = row[1]
json_data['email'] = row[2]
json_data['phoneNumber'] = row[3]
json_data['image'] = row[6]
filename = "candidate." + str(global_var.user_id)
################################## AcadmicHistory->education ###########################################
query = "SELECT * FROM `education` WHERE `mid` == '{0}'".format(global_var.user_id)
tmp = {}
i = 1
for row in sqllite.query(query).fetchall():
print(row[2])
tmp[i] = {}
tmp[i]["date"] = row[1]
tmp[i]["description"] = row[2]
i+=1
json_data["AcadmicHistory"]["education"] = tmp
################################## AcadmicHistory->researchersAndProject ###########################################
query = "SELECT * FROM `researchersAndProject` WHERE `mid` == '{0}'".format(global_var.user_id)
tmp = {}
i = 1
for row in sqllite.query(query).fetchall():
print(row[2])
tmp[i] = {}
tmp[i]["date"] = row[1]
tmp[i]["description"] = row[2]
i+=1
json_data["AcadmicHistory"]["researchersAndProject"] = tmp
################################## expAndSkill->specillaztion ###########################################
query = "SELECT * FROM `specillaztion` WHERE `mid` == '{0}'".format(global_var.user_id)
tmp = {}
i = 1
for row in sqllite.query(query).fetchall():
print(row[2])
tmp[i] = {}
tmp[i]["date"] = row[1]
tmp[i]["description"] = row[2]
i += 1
json_data["expAndSkill"]["specillaztion"] = tmp
################################## expAndSkill->previousCareer ###########################################
query = "SELECT * FROM `previousCareer` WHERE `mid` == '{0}'".format(global_var.user_id)
tmp = {}
i = 1
for row in sqllite.query(query).fetchall():
print(row[2])
tmp[i] = {}
tmp[i]["date"] = row[1]
tmp[i]["description"] = row[2]
i += 1
json_data["expAndSkill"]["specillaztion"] = tmp
################################## notes ###########################################
query = "SELECT * FROM `notes` WHERE `mid` == '{0}'".format(global_var.user_id)
tmp = {}
i = 1
for row in sqllite.query(query).fetchall():
print(row[2])
tmp[i] = {}
tmp[i]["date"] = row[1]
tmp[i]["description"] = row[2]
i += 1
json_data["notes"] = tmp
############################# save json file in files folder ###############################
with open(("files/" + filename + '.json'), 'w') as outfile:
json.dump(json_data, outfile, indent=2)
############################# log event ####################################################
global_var.main_output["add"]("status", 9)
global_var.main_output["add_log"](
"candidate file saves as {0}.json in folder files.".format(filename))
global_var.main_output["add"]("status", 7)
global_var.main_output["add_log"](
"template file saves as template.json, fill the template and import the file back.")
def check_perrsmisson(userName, password):
query = "select id from HR_users where user='******' and password='******'"
if sqllite.countRow(sqllite.query(query)) == 1:
return True
return False
def insert_candidate_information(data, id=None):
""""
function storing data in the database
"""
if (not validate_input.check_full_name(data['name'])):
global_var.main_output["add_error"](
"the full name need to be with first name,space and last name")
return
if (not validate_input.check_phone(data['phoneNumber'])):
global_var.main_output["add_error"](
"the phone number need to be 10 digits")
return
if (not validate_input.check_mail(data['email'])):
global_var.main_output["add_error"]("the email is not valid")
return
######################################## delete old record ############################################
if id == None:
id = global_var.user_id
query = "DELETE FROM `education` WHERE `mid`== {0}".format(id)
sqllite.query(query)
query = "DELETE FROM `notes` WHERE `mid`== {0}".format(id)
sqllite.query(query)
query = "DELETE FROM `previousCareer` WHERE `mid`== {0}".format(id)
sqllite.query(query)
query = "DELETE FROM `researchersAndProject` WHERE `mid`== {0}".format(id)
sqllite.query(query)
query = "DELETE FROM `specillaztion` WHERE `mid`== {0}".format(id)
sqllite.query(query)
############################# AcadmicHistory->education #######################
i = 1
for k, v in data['AcadmicHistory']['education'].items():
query1 = "INSERT INTO `education`(`id`,`date`,`description`,`mid`,`sn`) VALUES (NULL,'{0}','{1}','{2}',{3});".format(
v["date"], v["description"], id, i)
i += 1
sqllite.insert__update_query(query1)
############################# AcadmicHistory->researchersAndProject ##############################################
i = 1
for k, v in data['AcadmicHistory']['researchersAndProject'].items():
query1 = "INSERT INTO `researchersAndProject`(`id`,`date`,`description`,`mid`,`sn`) VALUES (NULL,'{0}','{1}','{2}',{3});".format(
v["date"], v["description"], id, i)
i += 1
sqllite.insert__update_query(query1)
############################# expAndSkill->previousCareer ##############################################
i = 1
for k, v in data['expAndSkill']['previousCareer'].items():
query1 = "INSERT INTO `previousCareer`(`id`,`date`,`description`,`mid`,`sn`) VALUES (NULL,'{0}','{1}','{2}',{3});".format(
v["date"], v["description"], id, i)
i += 1
sqllite.insert__update_query(query1)
############################# expAndSkill->specillaztion ##############################################
i = 1
for k, v in data['expAndSkill']['specillaztion'].items():
query1 = "INSERT INTO `specillaztion`(`id`,`date`,`description`,`mid`,`sn`) VALUES (NULL,'{0}','{1}','{2}',{3});".format(
v["date"], v["description"], id, i)
i += 1
sqllite.insert__update_query(query1)
############################# note ##############################################
i = 1
for k, v in data['notes'].items():
query1 = "INSERT INTO `notes`(`id`,`date`,`description`,`mid`,`sn`) VALUES (NULL,NULL,'{0}','{1}',{2});".format(
v["description"], id, i)
i += 1
sqllite.insert__update_query(query1)
query = "SELECT * FROM `candidateInformation` WHERE `mid` == '{0}'".format(
id)
if sqllite.countRow(sqllite.query(query)) == 0:
query = "INSERT INTO `candidateInformation`(`id`,`name`,`email`,`phoneNumber`," \
"`status`,`mid`,`image`) " \
"VALUES (NULL ,'{0}','{1}','{2}',0,'{3}','{4}');".format(data['name'],
data['email'],
data['phoneNumber'],
id,data['image'])
sqllite.insert__update_query(query)
#cloudinary.uploadImg(data['image'])
else:
query = "UPDATE `candidateInformation` SET `name` ='{0}', `email` ='{1}', `phoneNumber`='{2}', `image`='{3}'" \
" WHERE `mid`='{4}';".format(data['name'], data['email'], data['phoneNumber'], data['image'], id)
sqllite.insert__update_query(query)
