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)