def updateCourse(courseObj):
con = util.getCon()
try:
sno = getattr(courseObj, 'sno')
print sno
instituteId = domain.Institute.instId # act as foreignKey. domain is package in which Institute is class andwe get static variable instId
courseName = getattr(courseObj, 'courseName')
courseId = getattr(courseObj, 'courseId')
courseFee = getattr(courseObj, 'courseFee')
sql = ""
sql = sql + " update course set courseName='" + str(
courseName) + "' , courseId = '" + str(
courseId) + "', courseFee='" + str(
courseFee) + "' where sno='" + str(sno) + "'"
print sql
cursor = util.getCursor()
cursor.execute(sql)
con.commit()
JOptionPane.showMessageDialog(None,
"Successfully course has been updated")
return True
except:
con.rollback()
JOptionPane.showMessageDialog(None, "Failed to the update the course ")
return False
def updateTeacherService(trObj):
con = util.getCon()
try:
teacherId = getattr(trObj, 'teacherId')
teacherName = getattr(trObj, 'teacherName')
teacherPhone = getattr(trObj, 'teacherPhone')
teacherEmail = getattr(trObj, 'teacherEmail')
teacherAddress = getattr(trObj, 'teacherAddress')
teacherCourse = getattr(trObj, 'teacherCourse')
teacherPayment = getattr(trObj, 'teacherPayment')
teacherLoginId = getattr(trObj, 'teacherLoginId')
sql = " update teacher set teacherName='" + str(
teacherName) + "' , teacherPhone = '" + str(
teacherPhone) + "', teacherEmail='" + str(
teacherEmail) + "', teacherAddress='" + str(
teacherAddress) + "', teacherCourse='" + str(
teacherCourse) + "', teacherPayment='" + str(
teacherPayment) + "',teacherLoginId='" + str(
teacherLoginId
) + "' where teacherId='" + str(
teacherId) + "'"
print sql
cursor = util.getCursor()
cursor.execute(sql)
con.commit()
JOptionPane.showMessageDialog(None,
"Successfully teacher has been Updated")
return True
except:
con.rollback()
JOptionPane.showMessageDialog(None, "Failed to update the teacher ")
return False
def addTeacherService(trObj):
con = util.getCon()
try:
instituteId = domain.Institute.instId # act as foreignKey. domain is package in which Institute is class andwe get static variable instId
teacherName = getattr(trObj, 'teacherName')
teacherPhone = getattr(trObj, 'teacherPhone')
teacherEmail = getattr(trObj, 'teacherEmail')
teacherAddress = getattr(trObj, 'teacherAddress')
teacherCourse = getattr(trObj, 'teacherCourse')
teacherPayment = getattr(trObj, 'teacherPayment')
teacherLoginId = getattr(trObj, 'teacherLoginId')
teacherPassword = getattr(trObj, 'teacherPassword')
print "hello"
sql = "insert into teacher (instituteId,teacherName,teacherPhone,teacherEmail,teacherAddress,teacherCourse,teacherPayment,teacherLoginId,teacherPassword) values ('" + str(
instituteId
) + "','" + str(teacherName) + "','" + str(teacherPhone) + "','" + str(
teacherEmail) + "','" + str(teacherAddress) + "','" + str(
teacherCourse) + "','" + str(teacherPayment) + "','" + str(
teacherLoginId) + "','" + str(teacherPassword) + "')"
print sql
cursor = util.getCursor()
cursor.execute(sql)
con.commit()
JOptionPane.showMessageDialog(None,
"Successfully teacher has been added")
return True
except:
con.rollback()
JOptionPane.showMessageDialog(None, "Failed to add the teacher ")
return False
def teacherLogin(loginId, password):
try:
sql = " Select * from teacher where teacherLoginId='" + loginId + "' AND teacherPassword ='******'"
print sql
cursor = util.getCursor()
cursor.execute(sql)
row = cursor.fetchone()
print row
if (row == None):
JOptionPane.showMessageDialog(
None, "No Teacher of that loginId or password ")
return False
else:
# now admin login so we have to store its id in static variable to do more task ans store lsit of details
domain.Teacher.teacherId = row[0]
domain.Teacher.instId = row[1]
domain.Teacher.teacherObj = row
teObj = domain.Teacher()
setattr(teObj, 'teacherId', row[0])
setattr(teObj, 'instituteId', row[1])
setattr(teObj, 'teacherName', row[2].encode('ascii'))
setattr(teObj, 'teacherPhone', row[3].encode('ascii'))
setattr(teObj, 'teacherEmail', row[9].encode('ascii'))
setattr(teObj, 'teacherAddress', row[4].encode('ascii'))
setattr(teObj, 'teacherCourse', row[5].encode('ascii'))
setattr(teObj, 'teacherPayment', row[6])
JOptionPane.showMessageDialog(
None, row[2].encode('ascii') + " is logined successfully")
return teObj
except:
JOptionPane.showMessageDialog(None, "Failed to teacher login")
return False
def addStudentService(stObj):
con = util.getCon()
try:
instituteId = domain.Institute.instId # act as foreignKey. domain is package in which Institute is class andwe get static variable instId
studentName = getattr(stObj,'studentName')
studentPhone = getattr(stObj,'studentPhone')
studentEmail = getattr(stObj,'studentEmail')
studentAddress = getattr(stObj,'studentAddress')
courseName = getattr(stObj,'courseName')
courseFee = getattr(stObj,'courseFee')
studentAssignTeacher = getattr(stObj,'studentAssignTeacher')
studentLoginId = getattr(stObj,'studentLoginId')
studentPassword = getattr(stObj,'studentPassword')
sql = "insert into student (instituteId,studentName,studentPhone,studentEmail,studentAddress,courseName,courseFee,studentAssignTeacher,studentLoginId,studentPassword) values ('" + str(instituteId) + "','" + str(studentName) + "','" + str(studentPhone) + "','" + str(studentEmail) +"','"+str(studentAddress)+"','"+str(courseName)+"','"+str(courseFee)+"','"+str(studentAssignTeacher)+"','"+str(studentLoginId)+"','"+str(studentPassword)+"')"
print sql
cursor = util.getCursor()
cursor.execute(sql)
con.commit()
JOptionPane.showMessageDialog(None,"Successfully student has been added")
return True
except:
con.rollback()
JOptionPane.showMessageDialog(None,"Failed to add the student ")
return False
def instituteLogin(loginId, password):
try:
sql = " Select * from institute where adminLoginId='"+loginId+"' AND adminPassword ='******'"
print sql
cursor=util.getCursor()
cursor.execute(sql)
row = cursor.fetchone()
print row
if(row == None):
JOptionPane.showMessageDialog(None,"No Admin of that loginId or password ")
return False
else:
# now admin login so we have to store its id in static variable to do more task ans store lsit of details
domain.Institute.instId=row[0]
domain.Institute.instObj=row
instObj= domain.Institute()
setattr(instObj,'instituteId',row[0])
setattr(instObj,'name',row[1].encode('ascii'))
setattr(instObj,'phone',row[2].encode('ascii'))
setattr(instObj,'address',row[3].encode('ascii'))
JOptionPane.showMessageDialog(None,row[1].encode('ascii')+" is logined successfully")
return instObj
except:
JOptionPane.showMessageDialog(None,"Failed to login ")
return False
def getTeacherPrimaryKey(teacherObj):
try:
instituteId = domain.Institute.instId # Logined insitute id
teacherName = getattr(teacherObj, 'teacherName')
teacherEmail = getattr(teacherObj, 'teacherEmail')
teacherPhone = getattr(teacherObj, 'teacherPhone')
teacherAddress = getattr(teacherObj, 'teacherAddress')
teacherCourse = getattr(teacherObj, 'teacherCourse')
teacherPayment = getattr(teacherObj, 'teacherPayment')
sql = "select teacherId from teacher where instituteId ='" + str(
instituteId) + "' AND teacherName ='" + str(
teacherName) + "' AND teacherEmail ='" + str(
teacherEmail) + "' AND teacherPhone='" + str(
teacherPhone) + "' AND teacherAddress ='" + str(
teacherAddress) + "' AND teacherCourse ='" + str(
teacherCourse
) + "' AND teacherPayment='" + str(
teacherPayment) + "'"
print sql
cursor = util.getCursor()
cursor.execute(sql)
rows = cursor.fetchone() # it return in tuple
return rows
except:
return False
def studentLogin(loginId, password):
try:
sql = " Select * from student where studentLoginId='jayesh@gmail' AND studentPassword ='******'"
print sql
cursor=util.getCursor()
cursor.execute(sql)
row = cursor.fetchone()
print row
if(row == None):
JOptionPane.showMessageDialog(None,"No Student of that loginId or password ")
return False
else:
# now admin login so we have to store its id in static variable to do more task ans store lsit of details
domain.Student.studentId=row[0]
domain.Student.instId=row[1]
domain.Student.studentObj=row
stObj= domain.Student()
setattr(stObj,'studentId',row[0])
setattr(stObj,'instituteId',row[1])
setattr(stObj,'studentName',row[2].encode('ascii'))
setattr(stObj,'studentPhone',row[3].encode('ascii'))
setattr(stObj,'studentEmail',row[4].encode('ascii'))
setattr(stObj,'studentAddress',row[5].encode('ascii'))
setattr(stObj,'studentAssignTeacher',row[10].encode('ascii'))
setattr(stObj,'courseName',row[6].encode('ascii'))
setattr(stObj,'courseFee',row[7])
JOptionPane.showMessageDialog(None,row[2].encode('ascii')+" is logined successfully")
return stObj
except:
JOptionPane.showMessageDialog(None,"Failed to student login")
return False
def getStudentFeeDetailsByStudentId(studentId):
try:
sql = "select studentId,studentName,totalAmount,paidAmount,remainingAmount from studentfee where studentId ='"+str(studentId)+"'"
cursor = util.getCursor()
cursor.execute(sql)
rows = cursor.fetchone() # it return in list of list
return rows
except:
return False
def showCourseList():
try:
instituteId = domain.Institute.instId # Logined insitute id
sql = "select * from course where instituteId =" + str(instituteId)
cursor = util.getCursor()
cursor.execute(sql)
rows = cursor.fetchall() # it return in list of list
return rows
except:
return False
def getStudentInfo():
try:
studentId = domain.Student.studentId # Logined insitute id
sql = "select studentName,studentPhone,studentEmail,studentAddress,courseName,courseFee,studentAssignTeacher from student where studentId ='"+str(studentId)+"'"
cursor = util.getCursor()
cursor.execute(sql)
rows = cursor.fetchone() # it return in list of list
return rows
except:
return False
def getSpecificStudentAttendenceInMonth(instituteId,studentName,month,year):
try:
sql = "select studentId,studentName,date,present from studentattendence where instituteId ='"+str(instituteId)+"' And MONTH(date)='"+str(month)+"' AND YEAR(date)='"+str(year)+"' AND studentName ='"+str(studentName)+"'"
print sql
cursor = util.getCursor()
cursor.execute(sql)
rows = cursor.fetchall() # it return in list of list
return rows
except:
return False
def getStudentIdPassword(studentName,courseName):
try:
instituteId = domain.Institute.instId # Logined insitute id
sql = "select studentLoginId,studentPassword from student where instituteId ='"+str(instituteId) +"' And courseName ='"+str(courseName)+"' AND studentName='"+str(studentName)+"'"
cursor = util.getCursor()
cursor.execute(sql)
rows = cursor.fetchone() # it return in list of list
return rows
except:
return False
def getStudentIdAndNameByTeacherName(teacherName):
try:
instituteId = domain.Teacher.instId # Logined insitute id
sql = "select studentId,studentName from student where instituteId ='"+str(instituteId) +"' AND studentAssignTeacher ='"+str(teacherName)+"'"
cursor = util.getCursor()
cursor.execute(sql)
rows = cursor.fetchall() # it return in list of list
return rows
except:
return False
def getStudentIdsOfSpecificCourse(courseName):
try:
instituteId = domain.Institute.instId # Logined insitute id
sql = "select studentId from student where instituteId ='"+str(instituteId) +"' And courseName ='"+str(courseName)+"'"
cursor = util.getCursor()
cursor.execute(sql)
rows = cursor.fetchall() # it return in list of list
return rows
except:
return False
def getStudentDetailsByStudentName(studentName):
try:
instituteId = domain.Institute.instId # Logined insitute id
sql = "select studentId,studentName,studentPhone,studentEmail,studentAddress,courseName from student where instituteId ='"+str(instituteId) +"' AND studentName ='"+str(studentName)+"'"
cursor = util.getCursor()
cursor.execute(sql)
rows = cursor.fetchall() # it return in list of list
return rows
except:
return False
def showStudentChoiceCourse(course):
try:
instituteId = domain.Institute.instId # Logined insitute id
sql = "select teacherName from teacher where instituteId ='"+str(instituteId)+"' AND teacherCourse ='"+str(course)+"'"
cursor = util.getCursor()
cursor.execute(sql)
rows = cursor.fetchall() # it return in list of list
return rows
except:
return False
def showSpecificStudentsByTeacher(teacherName):
try:
instituteId = domain.Institute.instId # Logined insitute id
sql = "select * from student where instituteId ='"+str(instituteId) +"' And studentAssignTeacher ='"+str(teacherName)+"'"
cursor = util.getCursor()
cursor.execute(sql)
rows = cursor.fetchall() # it return in list of list
return rows
except:
return False
def getStudentPassword(studentId):
try:
instituteId = domain.Student.instId # Logined insitute id
sql = "select studentPassword from student where instituteId ='"+str(instituteId)+"' AND studentId ='"+str(studentId)+"'"
print sql
cursor = util.getCursor()
cursor.execute(sql)
rows = cursor.fetchone() # it return in list of list
return rows
except:
return False
def getStudentIdsOfSpecificTeacher(teacherName):
try:
sql = "select studentId from student where studentAssignTeacher ='"+str(teacherName)+"'"
print sql
cursor = util.getCursor()
cursor.execute(sql)
rows = cursor.fetchall() # it return in list of list
print rows
return rows
except:
return False
def getTeacherDetails():
try:
instituteId = domain.Institute.instId # Logined insitute id
sql = "select teacherId,teacherName from teacher where instituteId =" + str(
instituteId)
cursor = util.getCursor()
cursor.execute(sql)
rows = cursor.fetchall() # it return in list of list
return rows
except:
return False
def showAllStudentList():
try:
instituteId = domain.Institute.instId # Logined insitute id
sql = "select * from student where instituteId ='"+str(instituteId)+"'"
print sql
cursor = util.getCursor()
cursor.execute(sql)
rows = cursor.fetchall() # it return in list of list
return rows
except:
return False
def getTeacherIdPassword(teacherName):
try:
instituteId = domain.Institute.instId # Logined insitute id
sql = "select teacherloginId,teacherPassword from teacher where instituteId ='" + str(
instituteId) + "' AND teacherName ='" + str(teacherName) + "'"
cursor = util.getCursor()
cursor.execute(sql)
rows = cursor.fetchone() # it return in list of list
return rows
except:
return False
def getTeacherInfo():
try:
teacherId = domain.Teacher.teacherId # Logined insitute id
sql = "select teacherName,teacherPhone,teacherEmail,teacherAddress,teacherCourse,teacherPayment from teacher where teacherId ='" + str(
teacherId) + "'"
cursor = util.getCursor()
cursor.execute(sql)
rows = cursor.fetchone() # it return in list of list
return rows
except:
return False
def getSpecificTeacherAttendence(teacherName):
try:
instituteId = domain.Institute.instId # Logined insitute id
sql = "select teacherId,teacherName,date,present from teacherattendence where instituteId ='" + str(
instituteId) + "' And teacherName='" + str(teacherName) + "'"
cursor = util.getCursor()
cursor.execute(sql)
rows = cursor.fetchall() # it return in list of list
return rows
except:
return False
def getAllStudentAttendenceStatisticsInMonth(month,year):
try:
instituteId = domain.Institute.instId # Logined insitute id
sql =" select t1.studentId,t1.studentName,t1.totaldays,IFNULL(t2.NoOfPresent,0)as NoOfPresent,IFNULL(t3.NoOfAbsent,0) as NoOfAbsent from (select studentId,studentName,count(date) as totaldays from studentattendence where instituteId='"+str(instituteId)+"' AND MONTH(date) ='"+str(month)+"' AND YEAR(date)='"+str(year)+"' group by studentId,studentName ) as t1 left outer join (select studentId ,count(present) as NoOfPresent from studentattendence where instituteId='"+str(instituteId)+"' And MONTH(date) ='"+str(month)+"' AND YEAR(date) ='"+str(year)+"' AND present='P' group by studentId) as t2 on t1.studentId=t2.studentId left outer join (select studentId,count(present) as NoOfAbsent from studentattendence where instituteId ='"+str(instituteId)+"' AND MONTH(date)='"+str(month)+"' AND YEAR(date)='"+str(year)+"' AND present='A' group by studentId ) as t3 on t1.studentId=t3.studentId"
print sql
cursor = util.getCursor()
cursor.execute(sql)
rows = cursor.fetchall() # it return in list of list
return rows
except:
return False
def getFee(courseName):
try:
instituteId = domain.Institute.instId # Logined insitute id
sql = "select courseFee from course where instituteId ='" + str(
instituteId) + "' AND courseName ='" + str(courseName) + "'"
cursor = util.getCursor()
cursor.execute(sql)
row = cursor.fetchone() # it return in list of list
return row
except:
return False
def getSpecificStudentAttendencInMonthByStudentId(month,year):
try:
studentId = domain.Student.studentId # Logined insitute id
sql = "select studentId,studentName,date,present from studentattendence where studentId ='"+str(studentId)+"' And MONTH(date)='"+str(month)+"' AND YEAR(date)='"+str(year)+"'"
print sql
cursor = util.getCursor()
cursor.execute(sql)
rows = cursor.fetchall() # it return in list of list
return rows
except:
return False
def getTeacherPassword(teacherId):
try:
print teacherId
sql = "select teacherPassword from teacher where teacherId ='" + str(
teacherId) + "'"
print sql
cursor = util.getCursor()
cursor.execute(sql)
rows = cursor.fetchone() # it return in list of list
return rows
except:
return False
def getAllTeacherAttendenceInMonth(month, year):
try:
instituteId = domain.Institute.instId # Logined insitute id
sql = "select teacherId,teacherName,date,present from teacherattendence where instituteId ='" + str(
instituteId) + "' And MONTH(date)='" + str(
month) + "' AND YEAR(date)='" + str(year) + "'"
cursor = util.getCursor()
cursor.execute(sql)
rows = cursor.fetchall() # it return in list of list
return rows
except:
return False
def flight_request(request, empid):
t = {}
cur = util.getCursorFlight()
#get bn's userid first
sqlora = """
select trim(entity)
from msv020
where employee_id = '%s'
""" % empid
curell = util.getCursor()
curell.execute(util.sqlToOpenQuery(sqlora))
emp = curell.fetchone()
if emp:
sql = """
select a.ta_no, a.name, a.employer, a.approved, b.flightdate, b.flightfrom, b.flightto, b.flightstatus, a.user_session, a.reasondescription
from flight_ta a
left join flight_transaction b
on a.ta_no = b.ta_no
where a.createdby = '%s'
order by b.flightdate desc
""" % emp[0]
cur.execute(sql)
t['flight_created'] = cur.fetchall()
sql = """
select a.ta_no, a.name, a.employer, a.approved, b.flightdate, b.flightfrom, b.flightto, b.flightstatus, a.user_session, a.reasondescription
from flight_ta a
left join flight_transaction b
on a.ta_no = b.ta_no
where a.badge_no = '%s'
order by b.flightdate desc
""" % util.bnLong2Short(empid.upper())
cur.execute(sql)
t['myflight'] = cur.fetchall()
sql = """
select a.ta_no, a.name, a.employer, a.approved, b.flightdate, b.flightfrom, b.flightto, b.flightstatus, a.user_session, a.reasondescription
from flight_ta a
left join flight_transaction b
on a.ta_no = b.ta_no
where a.Approver_1_BadgeNo = '%s' or a.Approver_2_BadgeNo = '%s'
order by b.flightdate desc
""" % (empid.upper(), empid.upper())
cur.execute(sql)
t['flight_approved'] = cur.fetchall()
return render_to_response('ad/flight_list.html', t)
def test123(request):
if not request.POST.get('action',None):
return render_to_response('updateCC.html', {})
import shutil
shutil.copy("\\\\ppp-intranet\\CostCenterSummary\\SummaryCostCenter2007_Prod.xls", "\\\\ppp-utilities\\temp$\\")
con,cur = util.getCursor(for_update=True)
try:
sql = """
CREATE TABLE [dbo].[CCSupvtemp] (
[CostCenter] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CC# Supervisor Name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BadgeNo] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Department] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DP] [int] NULL
) ON [PRIMARY]
"""
cur.execute(sql)
except:
pass
sql = "delete from CCSupvtemp"
cur.execute(sql)
sql = "insert into CCSupvtemp select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=f:\\dody\\temp\\SummaryCostCenter2007_Prod.xls', 'SELECT [Cost Center] as CostCenter, Description, [CC# Supervisor Name], [Badge No#] as BadgeNo, Department, DP FROM [CC-Summary$]')"
cur.execute(sql)
#connect to onlineca
cur2 = util.getCursorOnlineCA()
#~ sql = "delete from CCSupv"
#~ cur2.execute(sql)
sql = "insert into CCSupv22 select * FROM OPENROWSET('MSDASQL', 'DRIVER={SQL Server};SERVER=ppp-UTILITIES\\TESTING;UID=sa;PWD=xxxxx', tempdb.dbo.ccsupvtemp)"
cur2.execute(sql)
#connect to globalservice
#~ cur3 = util.getCursorGlobalService()
#~ sql = "delete from CCSupv"
#~ sql = "insert into MsCCSupervisor select * FROM OPENROWSET('MSDASQL', 'DRIVER={SQL Server};SERVER=ppp-UTILITIES\\TESTING;UID=sa;PWD=xxxxx', tempdb.dbo.ccsupvtemp)"
return render_to_response('updateCCFinished.html', {})
def ell_emp_detail_notfound(request, empid):
cur = util.getCursor()
sqlora = """
select a.formatted_name, a.employee_id
from msv810 a
left join msv020 b
on a.employee_id = b.employee_id
where a.employee_id like '%%%s%%'
""" % empid.upper()
cur.execute(util.sqlToOpenQuery(sqlora))
emps = cur.fetchall()
if not emps:
return render_to_response('ad/ell_emp_detail.html', {'emp': emps})
elif len(emps) == 1:
# redirect
return HttpResponseRedirect("/ell/emp/%s" % emps[0][1])
else:
# display list of valid badge num (and name) to pick
return render_to_response('ad/ell_emp_list.html', {'emps': emps})
def search_name_ell(request, name = None):
if not name:
if request.method == "POST" and request.POST.get("name") != "":
return HttpResponseRedirect("/ell/search/name/%s" % request.POST.get("name"))
else:
return render_to_response('ad/ell_user_list.html', {})
else:
sqlora = """
select a.formatted_name, a.employee_id
from msv810 a
left join msv020 b
on a.employee_id = b.employee_id
where a.formatted_name like '%%%s%%'
""" % name.upper()
cur = util.getCursor()
cur.execute(util.sqlToOpenQuery(sqlora))
users = cur.fetchall()
if not users:
return render_to_response('ad/ell_emp_detail.html', {'emp': users})
elif len(users) == 1:
return HttpResponseRedirect("/ell/emp/%s/" % users[0][1])
else:
return render_to_response('ad/ell_user_list.html', {'users': users})
def ell_emp_detail(request, empid):
cur = util.getCursor()
sqlora = """
select a.formatted_name, a.employee_id, trim(a.email_address), trim(a.home_phone_no), trim(a.mobile_no), trim(b.entity), c.work_loc, c.expat_ind, c.emp_status
from msv810 a
left join msv020 b
on a.employee_id = b.employee_id
left join msv760 c
on a.employee_id = c.employee_id
where a.employee_id='%s'
""" % empid
cur.execute(util.sqlToOpenQuery(sqlora))
emp = cur.fetchone()
if not emp:
return ell_emp_detail_notfound(request, empid)
#current position
sqlora = """
select c.position_id, d.pos_title, c.primary_pos, c.inv_str_date, c.pos_stop_date
from msv878 c
left join msv870 d
on c.position_id = d.position_id
where c.employee_id='%s'
and c.inv_str_date <= to_char(sysdate,'YYYYMMDD')
and (c.pos_stop_date >= to_char(sysdate,'YYYYMMDD') or pos_stop_date = '00000000')
order by c.primary_pos, c.inv_str_date desc
""" % empid
cur.execute(util.sqlToOpenQuery(sqlora))
currpos = cur.fetchall()
#pos history
sqlora = """
select c.position_id, d.pos_title, c.primary_pos, c.inv_str_date, c.pos_stop_date
from msv878 c
left join msv870 d
on c.position_id = d.position_id
where c.employee_id='%s'
and c.inv_str_date <= to_char(sysdate,'YYYYMMDD')
and c.pos_stop_date < to_char(sysdate,'YYYYMMDD')
and pos_stop_date != '00000000'
order by c.inv_str_date desc
""" % empid
cur.execute(util.sqlToOpenQuery(sqlora))
poshist = cur.fetchall()
#get doa info from sharepoint @ ppp-intranet2
#bn used there for number bn type is in short mode (9665 vs 0000009665)
# current/future delegate to
sql = """
SELECT
nvarchar2 AS DELEGATEFROM,
nvarchar3 AS BN,
--RIGHT('0000000000' + nvarchar3, 10) AS BN,
--nvarchar4 AS JOBTITLE,
nvarchar5 AS DELEGATEDNAME,
nvarchar6 AS DELEGATETOBN,
--RIGHT('0000000000' + nvarchar6, 10) AS DELEGATEDBN,
--ntext2 AS REASON,
--datetime1 AS DATEREQUEST,
datetime2 AS DATEEFFECTIVE,
datetime3 AS DATEEND
--tp_Created AS DATECREATED,
--tp_Modified AS DATEMMODIFIED,
--tp_ListId AS LISTID
FROM [ppp-SQL01].WSS_pppINTRANETWSS_PROD.dbo.UserData
WHERE
tp_ListId = '{24206258-7FCE-4E4C-8B2E-91FC055E2E10}'
and nvarchar3 like '%%%s%%'
and datetime3 >= CONVERT(nvarchar(10), GETDATE(), 101)
order by datetime2 desc
""" % util.bnLong2Short(empid)
cur.execute(sql)
currdelegateto = cur.fetchall()
# current/future delegating
sql = """
SELECT
nvarchar2 AS DELEGATEFROM,
nvarchar3 AS BN,
--RIGHT('0000000000' + nvarchar3, 10) AS BN,
--nvarchar4 AS JOBTITLE,
nvarchar5 AS DELEGATEDNAME,
nvarchar6 AS DELEGATETOBN,
--RIGHT('0000000000' + nvarchar6, 10) AS DELEGATEDBN,
--ntext2 AS REASON,
--datetime1 AS DATEREQUEST,
datetime2 AS DATEEFFECTIVE,
datetime3 AS DATEEND
--tp_Created AS DATECREATED,
--tp_Modified AS DATEMMODIFIED,
--tp_ListId AS LISTID
FROM [ppp-SQL01].WSS_pppINTRANETWSS_PROD.dbo.UserData
WHERE
tp_ListId = '{24206258-7FCE-4E4C-8B2E-91FC055E2E10}'
and nvarchar6 like '%%%s%%'
and datetime3 >= CONVERT(nvarchar(10), GETDATE(), 101)
order by datetime2 desc
""" % util.bnLong2Short(empid)
cur.execute(sql)
currdelegating = cur.fetchall()
# delegation history
sql = """
SELECT
nvarchar3 AS BN,
nvarchar5 AS DELEGATEDNAME,
nvarchar6 AS DELEGATETOBN,
datetime2 AS DATEEFFECTIVE,
datetime3 AS DATEEND
FROM [ppp-SQL01].WSS_pppINTRANETWSS_PROD.dbo.UserData
WHERE
tp_ListId = '{24206258-7FCE-4E4C-8B2E-91FC055E2E10}'
and (nvarchar3 like '%%%s%%')
and datetime3 < CONVERT(nvarchar(10), GETDATE(), 101)
order by datetime2 desc
""" % util.bnShort2Long(empid)
cur.execute(sql)
doahist = cur.fetchall()
return render_to_response('ad/ell_emp_detail.html', {
'emp': emp,
'currpos': currpos,
'poshist': poshist,
'currdelegateto': currdelegateto,
'currdelegating': currdelegating,
})
def ell_pos_detail(request, posid):
cur = util.getCursor()
# position
sqlora = """
select d.position_id, d.pos_title, d.occup_status, d.posn_status, d.pos_stat_date, z.table_desc
from msv870 d
left join msv010 z
on d.posn_status = z.table_code
and z.table_type = 'PST'
where d.position_id ='%s'
""" % posid
cur.execute(util.sqlToOpenQuery(sqlora))
pos = list(cur.fetchone())
occdesc = dict(O='OCCUPIED', D='DELETED', V='VACANT')
pos[2] = occdesc[pos[2]]
#current position
sqlora = """
select c.employee_id, g.formatted_name, c.primary_pos, c.inv_str_date, c.pos_stop_date
from msv878 c
left join msv810 g
on c.employee_id = g.employee_id
where c.position_id = '%s'
and c.inv_str_date <= to_char(sysdate,'YYYYMMDD')
and (c.pos_stop_date >= to_char(sysdate,'YYYYMMDD') or pos_stop_date = '00000000')
order by c.primary_pos, c.inv_str_date desc
""" % posid
cur.execute(util.sqlToOpenQuery(sqlora))
currpos = cur.fetchall()
#pos history
sqlora = """
select c.employee_id, g.formatted_name, c.primary_pos, c.inv_str_date, c.pos_stop_date
from msv878 c
left join msv810 g
on c.employee_id = g.employee_id
where c.position_id ='%s'
and c.inv_str_date <= to_char(sysdate,'YYYYMMDD')
and c.pos_stop_date < to_char(sysdate,'YYYYMMDD')
and pos_stop_date != '00000000'
order by c.inv_str_date desc
""" % posid
cur.execute(util.sqlToOpenQuery(sqlora))
poshist = cur.fetchall()
#pos structure
sqlora = """
select level, a.position_id, d.pos_title, a.actual_encumbs
from msv875 a
left join msv870 d
on a.position_id = d.position_id
where a.hier_version = '004'
connect by prior a.superior_id = a.position_id
start with a.position_id = '%s'
""" % posid
cur.execute(util.sqlToOpenQuery(sqlora))
posstruct = cur.fetchall()
#pos subordinates
sqlora = """
select a.position_id, d.pos_title, a.actual_encumbs
from msv875 a
left join msv870 d
on a.position_id = d.position_id
where a.hier_version = '004'
and a.superior_id = '%s'
""" % posid
cur.execute(util.sqlToOpenQuery(sqlora))
possubordinate = cur.fetchall()
return render_to_response('ad/ell_pos_detail.html', {
'pos': pos,
'currpos': currpos,
'poshist': poshist,
'posstruct': posstruct,
'possubordinate': possubordinate,
})
