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, })