def JobTitleAll():
sql = "SELECT a.*, b.DEPARTMENT_NAME \
FROM JOB_TITLE a \
INNER JOIN DEPARTMENT b \
ON a.DEPARTMENT_CODE=b.DEPARTMENT_CODE "
return con.connection(sql, None)
def EmployeeAll():
sql =" SELECT a.* ,b.DEPARTMENT_NAME as DEPTNAME, c.JOB_TITLE_NAME as JOB_TITLE_NAME\
FROM Employee a \
INNER JOIN JOB_TITLE c \
on a.JOB_TITLE_CODE=c.JOB_TITLE_CODE\
INNER JOIN DEPARTMENT b \
on b.DEPARTMENT_CODE=c.DEPARTMENT_CODE"
return con.connection(sql, None)
def JobTitleDept(DptName):
sql = "SELECT * from JOB_TITLE \
WHERE DEPARTMENT_CODE \
IN ( SELECT DEPARTMENT_CODE \
FROM DEPARTMENT \
WHERE DEPARTMENT_NAME like :DptName)"
return con.connection(sql, [DptName])
def DeptNameEmployee(DeptName):
sql=" SELECT a.* ,b.DEPARTMENT_NAME as DEPTNAME, c.JOB_TITLE_NAME as JOB_TITLE_NAME\
FROM Employee a \
INNER JOIN JOB_TITLE c \
on a.JOB_TITLE_CODE=c.JOB_TITLE_CODE\
INNER JOIN DEPARTMENT b \
on b.DEPARTMENT_CODE=c.DEPARTMENT_CODE\
WHERE b.DEPARTMENT_NAME like :DeptName "
print((DeptName))
return con.connection(sql,DeptName)
def ActiveEmployee():
sql=" SELECT a.* ,b.DEPARTMENT_NAME as DEPTNAME, c.JOB_TITLE_NAME as JOB_TITLE_NAME\
FROM Employee a \
INNER JOIN JOB_TITLE c \
on a.JOB_TITLE_CODE=c.JOB_TITLE_CODE\
INNER JOIN DEPARTMENT b \
on b.DEPARTMENT_CODE=c.DEPARTMENT_CODE\
WHERE a.leave_date is Null \
OR a.leave_date < trunc(sysdate) "
return con.connection(sql, None)
def DepartmentAll():
sql = "SELECT * from DEPARTMENT"
return con.connection(sql, None)
def DptNameAll(DptName):
sql = "SELECT * from DEPARTMENT where DEPARTMENT_NAME like :DptName"
return con.connection(sql, DptName)
def BadgeAll():
sql ="SELECT * from BADGE"
return con.connection(sql, None)
def BadgeNumbers(badge):
query = """SELECT * FROM BADGE where badge_number in ('{}') """.format("','".join(map(str, badge)))
return con.connection(query,None)
def BadgeActive():
sql="SELECT * from BADGE\
WHERE BADGE_STATUS=:status \
AND BADGE_EXPIRY_DATE > trunc(sysdate)"
return con.connection(sql,['Active'])