def saveEmployeeRoles(self, fullname, managername, rolename, startdate,
enddate):
try:
if (enddate == None):
print("Must Specify an end date")
return False
roleid = DBUtils.getRow(
self._conn, "select roleid from roles where rolename like '" +
str(rolename) + "'")
employeeid = DBUtils.getRow(
self._conn,
"select employeeid from (SELECT E.EmployeeID, E.FirstName, E.LastName, CONCAT(E.FirstName, ' ' , E.LastName) AS FULLNAME FROM Employees E JOIN EmployeeRoles ER ON E.EmployeeID = ER.EmployeeID JOIN Roles R ON ER.RoleID = R.RoleID WHERE E.isEmployeed = 0) as data where fullname like '"
+ str(fullname) + "'")
managerid = DBUtils.getRow(
self._conn,
"select ManagerID from (SELECT E.ManagerID, (SELECT FullName FROM Managers where ManagerID = E.ManagerID) AS ManagerName FROM Employees E JOIN EmployeeRoles ER ON E.EmployeeID = ER.EmployeeID JOIN Roles R ON ER.RoleID = R.RoleID WHERE E.isEmployeed = 0) as data where ManagerName like '"
+ str(managername) + "'")
query = "update employeeroles set roleid = " + str(
roleid[0]) + ", startdate = '" + str(
startdate) + "', enddate = '" + str(
enddate) + "' where employeeid = " + str(employeeid[0])
DBUtils.executeUpdate(self._conn, query)
query = "update employees set managerid = " + str(
managerid[0]) + " where employeeid = " + str(employeeid[0])
DBUtils.executeUpdate(self._conn, query)
return True
except psycopg2.Error as e:
print(e)
return False
def getEmRo(self, fullname):
query1 = "select employeeid from employeewithrole where lower(fullname) like lower('" + str(
fullname) + "')"
employeeid = DBUtils.getRow(self._conn, query1)
#query = "select * from EmployeeRolesView where employeeid = " + str(employeeid[0])
query = "SELECT E.EmployeeID, E.FirstName, E.LastName, CONCAT(E.FirstName, ' ' , E.LastName) AS FULLNAME, E.isEmployeed, E.ManagerID, (SELECT FullName FROM Managers where ManagerID = E.ManagerID) AS ManagerName, R.RoleName, ER.StartDate StartDate, ER.EndDate EndDate FROM Employees E JOIN EmployeeRoles ER ON E.EmployeeID = ER.EmployeeID JOIN Roles R ON ER.RoleID = R.RoleID where employeeid = " + str(
employeeid[0])
return DBUtils.getAllRows(self._conn, query)
def getEmployeeData(self, fullname):
employeeRoles = None
#query = "select * from EmployeeRolesView where lower(fullname) like lower('" + str(fullname) + "%')"
query = "select * from (SELECT E.EmployeeID,E.FirstName,E.LastName,CONCAT(E.FirstName, ' ' , E.LastName) AS FULLNAME, E.isEmployeed,E.ManagerID,(SELECT FullName FROM Managers where ManagerID = E.ManagerID) AS ManagerName,R.RoleName,ER.StartDate StartDate,ER.EndDate EndDate FROM Employees E JOIN EmployeeRoles ER ON E.EmployeeID = ER.EmployeeID JOIN Roles R ON ER.RoleID = R.RoleID) as data where lower(fullname) like lower('" + str(
fullname) + "%')"
row = DBUtils.getRow(self._conn, query)
employeeRoles = EmployeeRole(row[0], row[1], row[2], row[3], row[4],
row[5], row[6], row[7], row[8], row[9])
return employeeRoles
def getRoleComponent(self, rolename):
if (rolename == "All"):
rolename = "%"
role = None
query = "select * from roles where rolename like '" + str(
rolename) + "'"
row = DBUtils.getRow(self._conn, query)
role = Role(row[0], row[1], row[2], row[3], row[4], row[5], row[6],
row[7], row[8], row[9], row[10], row[11], row[12], row[13],
row[14])
return role
