def get_hierarchy():
conn = Database.dbConnection()
hierarchy = {}
hierarchy_list = []
try:
cursor = conn.cursor(dictionary=True)
# sql = "SELECT DISTINCT REPORTSTO,FIRSTNAME,LASTNAME FROM CLASSICMODELS.EMPLOYEES WHERE REPORTSTO <> 'NULL'"
sql = "SELECT FIRSTNAME,LASTNAME,EMPLOYEENUMBER,REPORTSTO FROM EMPLOYEES"
cursor.execute(sql)
reports_to = cursor.fetchall()
for i in range(0, len(reports_to)):
hierarchy_dict = {}
employee_number = reports_to[i]['EMPLOYEENUMBER']
sql = "SELECT FIRSTNAME,LASTNAME,EMPLOYEENUMBER FROM CLASSICMODELS.EMPLOYEES WHERE REPORTSTO = {}".format(
employee_number)
cursor.execute(sql)
employee_details = cursor.fetchall()
hierarchy_dict['head'] = reports_to[i]
hierarchy_dict['staff'] = employee_details
hierarchy_list.append(hierarchy_dict)
hierarchy['hierarchy'] = hierarchy_list
return jsonify(hierarchy)
except Exception as exception:
hierarchy['error'] = str(exception)
return jsonify(hierarchy)
finally:
conn.close()
def get_employees():
conn = Database.dbConnection()
employees_dictionary = {}
try:
cursor = conn.cursor(dictionary=True)
sql = "SELECT * FROM EMPLOYEES"
cursor.execute(sql)
rs = cursor.fetchall()
employees_dictionary['employees'] = rs
return jsonify(employees_dictionary)
except Error as exception:
employees_dictionary['error'] = exception
return jsonify(str(employees_dictionary))
finally:
conn.close()
def get_job_titles():
conn = Database.dbConnection()
job_dictionary = {}
try:
cursor = conn.cursor(dictionary=True)
sql = "SELECT DISTINCT JOBTITLE FROM EMPLOYEES"
cursor.execute(sql)
job_dictionary['job_titles'] = cursor.fetchall()
return jsonify(job_dictionary)
except Exception as exception:
job_dictionary['error'] = str(exception)
return jsonify(job_dictionary)
finally:
conn.close()
def update_employee(request):
conn = Database.dbConnection()
update = {}
try:
first_name = request['FIRST_NAME']
last_name = request['LAST_NAME']
employee_number = request['EMPLOYEE_NUMBER']
cursor = conn.cursor()
sql = "UPDATE EMPLOYEES SET FIRSTNAME = '{}',LASTNAME = '{}' WHERE EMPLOYEENUMBER={}".format(
first_name, last_name, employee_number)
cursor.execute(sql)
conn.commit()
update['success'] = "operation successful"
return jsonify(update)
except Exception as exception:
update['error'] = str(exception)
return jsonify(update)
finally:
conn.close()
