def CampusDirectoryZEM001SELECTVWCX (self, emplid):
request = self.REQUEST
RESPONSE = request.RESPONSE
remote_addr = request.REMOTE_ADDR
if remote_addr in [Randy_Loch, Kim_Nguyen_G5, '127.0.0.1', ]:
file = open('/opt/Plone-2.5.5/zeocluster/client1/Extensions/Oracle_Database_Connection_Campus_Directory.txt', 'r')
for line in file.readlines():
if line <> "" and not line.startswith('#'):
connString = line
file.close()
connection = cx_Oracle.connect(connString)
cursor = connection.cursor()
cursor.execute("""select * from PS_ZEM001SELECTVW where emplid = :arg1""", arg1=emplid)
retlist = []
for c1, c2, c3 in cursor:
retlist.append([c1, c2, c3, ])
myMarshaller = xmlrpclib.Marshaller()
return myMarshaller.dumps(retlist)
tablename = 'PS_ZEM001SELECTVW'
querystr = "select * from %s where emplid = '%s'" % (tablename, emplid)
try:
retlist = dbc.query (querystr)
except:
conn.connect(connstr)
dbc = conn()
retlist = dbc.query (querystr)
myMarshaller = xmlrpclib.Marshaller()
return myMarshaller.dumps(retlist[1])
def CampusDirectoryZEM001ADDRVW(self, emplid):
request = self.REQUEST
RESPONSE = request.RESPONSE
remote_addr = request.REMOTE_ADDR
if remote_addr in [
'192.168.0.1',
Kim_Nguyen_G5,
'127.0.0.1',
]:
conn = getattr(self, 'Oracle_Database_Connection_Campus_Directory')
connstr = conn.connection_string
try:
if not conn.connected():
conn.connect(connstr)
except:
conn.connect(connstr)
dbc = conn()
tablename = 'PS_ZEM001ADDRVW'
querystr = "select * from %s where emplid = '%s'" % (tablename, emplid)
try:
retlist = dbc.query(querystr)
except:
conn.connect(connstr)
dbc = conn()
retlist = dbc.query(querystr)
myMarshaller = xmlrpclib.Marshaller()
return myMarshaller.dumps(retlist[1])
def CampusDirectoryZEM001VALUOVWCX(self, org_unit='None'):
request = self.REQUEST
RESPONSE = request.RESPONSE
remote_addr = request.REMOTE_ADDR
if remote_addr in [
Randy_Loch,
Kim_Nguyen_G5,
'127.0.0.1',
]:
file = open(
'/opt/Plone-2.5.5/zeocluster/client1/Extensions/Oracle_Database_Connection_Campus_Directory.txt',
'r')
for line in file.readlines():
if line <> "" and not line.startswith('#'):
connString = line
file.close()
connection = cx_Oracle.connect(connString)
cursor = connection.cursor()
if org_unit == 'None':
cursor.execute("""select * from PS_ZEM001VALUOVW """)
else:
cursor.execute(
"""select * from PS_ZEM001VALUOVW where z_univ_org = :arg1""",
arg1=org_unit)
retlist = []
for c1, c2 in cursor:
retlist.append([
c1,
c2,
])
myMarshaller = xmlrpclib.Marshaller()
return myMarshaller.dumps(retlist)
def getAllSubjectsCX(self, usexml):
request = self.REQUEST
RESPONSE = request.RESPONSE
remote_addr = request.REMOTE_ADDR
if remote_addr in [
Kim_Nguyen_iMac, Kim_Nguyen_MacBook, '127.0.0.1', Plone3
]:
file = open(
'/opt/Plone-2.5.5/zeocluster/client1/Extensions/Oracle_Database_Connection_NGUYEN_PRD.txt',
'r')
for line in file.readlines():
if line <> "" and not line.startswith('#'):
connString = line
file.close()
connection = cx_Oracle.connect(connString)
cursor = connection.cursor()
#cursor.execute("""select distinct(subject) from ps_class_tbl order by subject""")
cursor.execute(
"""select distinct subject, descr from ps_subject_tbl s1 where s1.eff_status = 'A' and s1.effdt = (select max(s2.effdt) from ps_subject_tbl s2 where s1.subject = s2.subject and s2.eff_status = 'A' and s1.institution = s2.institution and s1.acad_org = s2.acad_org)"""
)
retlist = []
for column_1, column_2 in cursor:
retlist.append([
column_1,
column_2,
])
if usexml == "0":
return retlist
else:
myMarshaller = xmlrpclib.Marshaller()
return myMarshaller.dumps(retlist)
def getEnrolledClasses (self, emplid, strm):
request = self.REQUEST
RESPONSE = request.RESPONSE
remote_addr = request.REMOTE_ADDR
if remote_addr in [Randy_Loch, Kim_Nguyen_G5, Kim_Nguyen_Air, '192.168.0.1', '127.0.0.1', CMF2, Plone1, Plone3 ]:
conn = getattr(self, 'Oracle_Database_Connection_NGUYEN_PRD')
connstr = conn.connection_string
try:
if not conn.connected():
conn.connect(connstr)
except:
conn.connect(connstr)
dbc = conn()
querystr = "select c.subject, c.catalog_nbr, c.descr, c.class_section, c.crse_id, n.first_name, n.last_name, e.email_addr, i.emplid from ps_email_addresses e, ps_class_tbl c, ps_class_instr i, ps_names n where c.class_nbr in (select class_nbr from PS_STDNT_ENRL_VW where emplid = '%s' and strm = c.strm and institution = c.institution) and c.strm = '%s' and c.institution = 'UWOSH' and c.crse_id = i.crse_id and i.strm = c.strm and i.class_section = c.class_section and n.emplid = i.emplid and n.eff_status = 'A' and n.name_type = 'PRI' and n.effdt = (select max(effdt) from ps_names n2 where n2.emplid = n.emplid and n2.eff_status = n.eff_status and n2.name_type = n.name_type) and e.emplid = i.emplid and e_addr_type = 'CAMP'" % (emplid, strm)
try:
retlist = dbc.query (querystr)
except:
conn.connect(connstr)
dbc = conn()
retlist = dbc.query (querystr)
# if len(retlist) == 2:
# if len(retlist[1]) == 1:
# if len(retlist[1][0]) == 1:
# return retlist[1][0][0]
# return "error: no semester code found matching today's date"
#logger.info("Got retlist '%s'" % retlist)
myMarshaller = xmlrpclib.Marshaller()
return myMarshaller.dumps(retlist)
def CampusDirectoryZEM001VALUOVW (self, org_unit='None'):
request = self.REQUEST
RESPONSE = request.RESPONSE
remote_addr = request.REMOTE_ADDR
if remote_addr in [Randy_Loch, Kim_Nguyen_G5, '127.0.0.1', ]:
conn = getattr(self, 'Oracle_Database_Connection_Campus_Directory')
connstr = conn.connection_string
try:
if not conn.connected():
conn.connect(connstr)
except:
conn.connect(connstr)
dbc = conn()
tablename = 'PS_ZEM001VALUOVW'
if org_unit == 'None':
querystr = "select * from %s" % (tablename)
else:
querystr = "select * from %s where z_univ_org = '%s'" % (tablename, org_unit)
try:
retlist = dbc.query (querystr)
except:
conn.connect(connstr)
dbc = conn()
retlist = dbc.query (querystr)
myMarshaller = xmlrpclib.Marshaller()
return myMarshaller.dumps(retlist[1])
def getClassesBySubjectTermCX(self, subject, strm):
request = self.REQUEST
RESPONSE = request.RESPONSE
remote_addr = request.REMOTE_ADDR
if remote_addr in [Kim_Nguyen_iMac, '127.0.0.1', Plone3]:
file = open(
'/opt/Plone-2.5.5/zeocluster/client1/Extensions/Oracle_Database_Connection_NGUYEN_PRD.txt',
'r')
for line in file.readlines():
if line <> "" and not line.startswith('#'):
connString = line
file.close()
connection = cx_Oracle.connect(connString)
cursor = connection.cursor()
cursor.execute(
"""select c.subject, c.catalog_nbr, c.descr, c.class_section, c.crse_id, n.first_name, n.last_name, e.email_addr, i.emplid from ps_email_addresses e, ps_class_tbl c, ps_class_instr i, ps_names n where c.class_nbr in (select class_nbr from PS_STDNT_ENRL_VW where emplid = :arg1 and strm = c.strm and institution = c.institution) and c.strm = :arg2 and c.institution = 'UWOSH' and c.crse_id = i.crse_id and i.strm = c.strm and i.class_section = c.class_section and n.emplid = i.emplid and n.eff_status = 'A' and n.name_type = 'PRI' and n.effdt = (select max(effdt) from ps_names n2 where n2.emplid = n.emplid and n2.eff_status = n.eff_status and n2.name_type = n.name_type) and e.emplid = i.emplid and e_addr_type = 'CAMP'""",
arg1=emplid,
arg2=strm)
retlist = []
for column_1, column_2, column_3, column_4, column_5, column_6, column_7, column_8, column_9 in cursor:
retlist.append([
column_1,
column_2,
column_3,
column_4,
column_5,
column_6,
column_7,
column_8,
column_9,
])
myMarshaller = xmlrpclib.Marshaller()
return myMarshaller.dumps(retlist)
def test_dump_fault(self):
f = xmlrpclib.Fault(42, 'Test Fault')
s = xmlrpclib.dumps((f,))
(newf,), m = xmlrpclib.loads(s)
self.assertEqual(newf, {'faultCode': 42, 'faultString': 'Test Fault'})
self.assertEqual(m, None)
s = xmlrpclib.Marshaller().dumps(f)
self.assertRaises(xmlrpclib.Fault, xmlrpclib.loads, s)
def xmlrpc_respond(self, args):
m = xmlrpclib.Marshaller("US-ASCII", 1)
self.ofunc("<methodResponse>")
if type(args) == type({}):
m.dump_struct(args, self.ofunc)
elif type(args) == type([]):
m.dump_array(args, self.ofunc)
self.ofunc("</methodResponse>")
def getClassByClassNumberCX (self, class_nbr, strm):
request = self.REQUEST
RESPONSE = request.RESPONSE
remote_addr = request.REMOTE_ADDR
if remote_addr in [www1_webcluster_uwosh_edu, Kim_Nguyen_G5, '127.0.0.1', Maccabee_Levine_laptop]:
file = open('/opt/Plone-2.5.5/zeocluster/client1/Extensions/Oracle_Database_Connection_ZCC041WEBSVCUSER.txt', 'r')
for line in file.readlines():
if line <> "" and not line.startswith('#'):
connString = line
file.close()
connection = cx_Oracle.connect(connString)
cursor = connection.cursor()
cursor.execute("""select * from PS_ZCC041WEBSVCVW where class_nbr = :arg_1 and strm = :arg_2""",
arg_1 = class_nbr,
arg_2 = strm)
myMarshaller = xmlrpclib.Marshaller()
for column_1, column_2, column_3, column_4, column_5, column_6, column_7, column_8, column_9, column_10, column_11 in cursor:
return myMarshaller.dumps([column_1, column_2, column_3, column_4, column_5, column_6, column_7, column_8, column_9, column_10, column_11,])
def getClassByClassNumberLongDescrTopicCX(self, class_nbr, strm):
request = self.REQUEST
RESPONSE = request.RESPONSE
remote_addr = request.REMOTE_ADDR
if remote_addr in [
www1_webcluster_uwosh_edu, Kim_Nguyen_G5, '127.0.0.1',
Maccabee_Levine_laptop, polk_uwosh_edu, wwwtest_polk_uwosh_edu
]:
file = open(
'/opt/Plone-2.5.5/zeocluster/client1/Extensions/Oracle_Database_Connection_NGUYEN_PRD.txt',
'r')
for line in file.readlines():
if line <> "" and not line.startswith('#'):
connString = line
file.close()
connection = cx_Oracle.connect(connString)
cursor = connection.cursor()
cursor.execute(
"""SELECT C.SUBJECT, C.CATALOG_NBR, C.DESCR, C.CLASS_SECTION,
C.CRSE_ID, C.CLASS_NBR, C.STRM, N.FIRST_NAME, N.LAST_NAME,
I.EMPLID, e.email_addr, cc.course_title_long, t.descr
FROM PS_CLASS_TBL C left join ps_crse_topics t on c.crse_id = t.crse_id and c.crs_topic_id = t.crs_topic_id , PS_CLASS_INSTR I, PS_NAMES N, ps_email_addresses e, ps_crse_catalog cc
WHERE C.INSTITUTION = 'UWOSH'
AND I.CRSE_ID = C.CRSE_ID
AND I.STRM = C.STRM
AND I.CLASS_SECTION = C.CLASS_SECTION
AND N.EMPLID = I.EMPLID
AND N.EFF_STATUS = 'A'
AND N.NAME_TYPE = 'PRI'
AND N.EFFDT = (SELECT MAX(EFFDT) FROM PS_NAMES N2 WHERE N2.EMPLID = N.EMPLID AND N2.EFF_STATUS = N.EFF_STATUS AND N2.NAME_TYPE = N.NAME_TYPE)
and e.emplid = i.emplid and e_addr_type = 'CAMP'
and c.crse_id = cc.crse_id and cc.effdt = (select max(effdt) from ps_crse_catalog where crse_id = cc.crse_id and eff_status = 'A') and cc.eff_status = 'A'
and c.class_nbr = :arg_1 and c.strm = :arg_2 and rownum < 2 order by t.effdt desc""",
arg_1=class_nbr,
arg_2=strm)
myMarshaller = xmlrpclib.Marshaller(allow_none=1)
for column_1, column_2, column_3, column_4, column_5, column_6, column_7, column_8, column_9, column_10, column_11, column_12, column_13 in cursor:
return myMarshaller.dumps([
column_1, column_2, column_3, column_4, column_5, column_6,
column_7, column_8, column_9, column_10, column_11, column_12,
column_13
])
def getThreeSemestersCX(self):
file = open(
'/opt/Plone-2.5.5/zeocluster/client1/Extensions/Oracle_Database_Connection_NGUYEN_PRD.txt',
'r')
for line in file.readlines():
if line <> "" and not line.startswith('#'):
connString = line
file.close()
connection = cx_Oracle.connect(connString)
cursor = connection.cursor()
cursor.execute(
"""select * from (select strm, descr from ps_term_tbl where institution = 'UWOSH' and acad_career = 'UGRD' and term_begin_dt <= sysdate and term_end_dt >= sysdate union select t1.strm, t1.descr from ps_term_tbl t1 where t1.institution = 'UWOSH' and t1.acad_career = 'UGRD' and t1.term_begin_dt >= (select min(term_begin_dt) from ps_term_tbl t2 where t2.institution = t1.institution and t2.acad_career = t1.acad_career and term_begin_dt > sysdate)) where rownum <= 3"""
)
retlist = []
for column_1, column_2 in cursor:
retlist.append([
column_1,
column_2,
])
myMarshaller = xmlrpclib.Marshaller(allow_none=True)
return myMarshaller.dumps(retlist)
def getCatalogNumbersAndSectionsByTermAndSubjectCX(self, strm, subject):
request = self.REQUEST
RESPONSE = request.RESPONSE
remote_addr = request.REMOTE_ADDR
if remote_addr in [
Kim_Nguyen_iMac, '127.0.0.1', Plone3, ws_it_uwosh_edu,
Kim_Nguyen_MacBook, Kim_Nguyen_G5, John_Hren_MBP
]:
file = open(
'/opt/Plone-2.5.5/zeocluster/client2/Extensions/Oracle_Database_Connection_NGUYEN_PRD.txt',
'r')
for line in file.readlines():
if line <> "" and not line.startswith('#'):
connString = line
file.close()
connection = cx_Oracle.connect(connString)
cursor = connection.cursor()
cursor.execute("""
select
c.catalog_nbr,
c.class_section,
c.class_nbr
from
ps_class_tbl c
where
c.strm = :arg1
and c.institution = 'UWOSH'
and c.subject = :arg2
""",
arg1=strm,
arg2=subject)
retlist = []
for column_1, column_2, column_3 in cursor:
retlist.append([
column_1,
column_2,
column_3,
])
myMarshaller = xmlrpclib.Marshaller(allow_none=True)
return myMarshaller.dumps(retlist)
class XMLRPCTestCase(unittest.TestCase):
def test_dump_load(self):
self.assertEqual(alist,
xmlrpclib.loads(xmlrpclib.dumps((alist,)))[0][0])
def test_dump_bare_datetime(self):
# This checks that an unwrapped datetime.date object can be handled
# by the marshalling code. This can't be done via test_dump_load()
# since with use_datetime set to 1 the unmarshaller would create
# datetime objects for the 'datetime[123]' keys as well
dt = datetime.datetime(2005, 02, 10, 11, 41, 23)
s = xmlrpclib.dumps((dt,))
(newdt,), m = xmlrpclib.loads(s, use_datetime=1)
self.assertEqual(newdt, dt)
self.assertEqual(m, None)
(newdt,), m = xmlrpclib.loads(s, use_datetime=0)
self.assertEqual(newdt, xmlrpclib.DateTime('20050210T11:41:23'))
def test_datetime_before_1900(self):
# same as before but with a date before 1900
dt = datetime.datetime(1, 02, 10, 11, 41, 23)
s = xmlrpclib.dumps((dt,))
(newdt,), m = xmlrpclib.loads(s, use_datetime=1)
self.assertEqual(newdt, dt)
self.assertEqual(m, None)
(newdt,), m = xmlrpclib.loads(s, use_datetime=0)
self.assertEqual(newdt, xmlrpclib.DateTime('00010210T11:41:23'))
def test_cmp_datetime_DateTime(self):
now = datetime.datetime.now()
dt = xmlrpclib.DateTime(now.timetuple())
self.assertTrue(dt == now)
self.assertTrue(now == dt)
then = now + datetime.timedelta(seconds=4)
self.assertTrue(then >= dt)
self.assertTrue(dt < then)
def test_bug_1164912 (self):
d = xmlrpclib.DateTime()
((new_d,), dummy) = xmlrpclib.loads(xmlrpclib.dumps((d,),
methodresponse=True))
self.assertIsInstance(new_d.value, str)
# Check that the output of dumps() is still an 8-bit string
s = xmlrpclib.dumps((new_d,), methodresponse=True)
self.assertIsInstance(s, str)
def test_newstyle_class(self):
class T(object):
pass
t = T()
t.x = 100
t.y = "Hello"
((t2,), dummy) = xmlrpclib.loads(xmlrpclib.dumps((t,)))
self.assertEqual(t2, t.__dict__)
def test_dump_big_long(self):
self.assertRaises(OverflowError, xmlrpclib.dumps, (2L**99,))
def test_dump_bad_dict(self):
self.assertRaises(TypeError, xmlrpclib.dumps, ({(1,2,3): 1},))
def test_dump_recursive_seq(self):
l = [1,2,3]
t = [3,4,5,l]
l.append(t)
self.assertRaises(TypeError, xmlrpclib.dumps, (l,))
def test_dump_recursive_dict(self):
d = {'1':1, '2':1}
t = {'3':3, 'd':d}
d['t'] = t
self.assertRaises(TypeError, xmlrpclib.dumps, (d,))
def test_dump_big_int(self):
if sys.maxint > 2L**31-1:
self.assertRaises(OverflowError, xmlrpclib.dumps,
(int(2L**34),))
xmlrpclib.dumps((xmlrpclib.MAXINT, xmlrpclib.MININT))
self.assertRaises(OverflowError, xmlrpclib.dumps, (xmlrpclib.MAXINT+1,))
self.assertRaises(OverflowError, xmlrpclib.dumps, (xmlrpclib.MININT-1,))
def dummy_write(s):
pass
m = xmlrpclib.Marshaller()
m.dump_int(xmlrpclib.MAXINT, dummy_write)
m.dump_int(xmlrpclib.MININT, dummy_write)
self.assertRaises(OverflowError, m.dump_int, xmlrpclib.MAXINT+1, dummy_write)
self.assertRaises(OverflowError, m.dump_int, xmlrpclib.MININT-1, dummy_write)
# good examples are class_nbr = '90143' and strm = '0630'
querystr = """
select * from PS_ZCC041WEBSVCVW where class_nbr = '%s' and strm = '%s'
""" % (class_nbr, strm)
try:
logger.info(
"7.5. About to call query() with class_nbr = %s and strm = %s"
% (class_nbr, strm))
before = datetime.now()
retlist = dbc.query(querystr)
logger.info("8. got query results ok, elapsed time was %s (%s)" %
(datetime.now() - before, debugstr))
logger.info("8.5. result set length is %s (%s)" %
(len(retlist[1]), debugstr))
except Exception, e:
logger.info(
"9. exception %s when trying to query, elapsed time was %s (%s)"
% (e, datetime.now() - before, debugstr))
conn.connect(connstr)
logger.info("10. connected ok (%s)" % debugstr)
dbc = conn()
logger.info("11. got dbc ok (%s)" % debugstr)
before2 = datetime.now()
retlist = dbc.query(querystr)
logger.info("12. got query results ok, elapsed time was %s (%s)" %
(datetime.now() - before2, debugstr))
logger.info("12.5. result set length is %s (%s)" %
(len(retlist[1]), debugstr))
myMarshaller = xmlrpclib.Marshaller()
return myMarshaller.dumps(retlist)
def getClassByClassNumberInternal(self, class_nbr, strm):
request = self.REQUEST
RESPONSE = request.RESPONSE
remote_addr = request.REMOTE_ADDR
debugstr = "%s/%s" % (class_nbr, strm)
if remote_addr in [
www1_webcluster_uwosh_edu,
Kim_Nguyen_G5,
'127.0.0.1',
]:
connId = 'Oracle_Database_Connection_ZCC041WEBSVCUSER'
conn = getattr(self, connId)
logger.info("1. got conn %s ok (%s)" % (connId, debugstr))
connstr = conn.connection_string
logger.info("2. got connection_string ok (%s)" % debugstr)
try:
if not conn.connected():
logger.info("3. not connected (%s)" % debugstr)
conn.connect(connstr)
logger.info("4. connected ok (%s)" % debugstr)
except Exception, e:
estr = str(e)
estr = re.sub(
r'Invalid connection string: </strong><CODE>(.+)</CODE>',
'Invalid connection string: </strong><CODE>[omitted]</CODE>',
estr)
logger.info("5. exception %s when checking if connected (%s)" %
(estr, debugstr))
if estr.find('Invalid connection string'):
logger.info(
"5.5 exiting, unable to connect to Oracle; may not be available (%s)"
% debugstr)
myMarshaller = xmlrpclib.Marshaller()
return myMarshaller.dumps(
['unable to connect to Oracle; may not be available'])
conn.connect(connstr)
logger.info("6. connected ok after exception (%s)" % debugstr)
dbc = conn()
logger.info("7. got dbc ok (%s)" % debugstr)
# good examples are class_nbr = '90143' and strm = '0630'
querystr = """
select * from PS_ZCC041WEBSVCVW where class_nbr = '%s' and strm = '%s'
""" % (class_nbr, strm)
try:
logger.info(
"7.5. About to call query() with class_nbr = %s and strm = %s"
% (class_nbr, strm))
before = datetime.now()
retlist = dbc.query(querystr)
logger.info("8. got query results ok, elapsed time was %s (%s)" %
(datetime.now() - before, debugstr))
logger.info("8.5. result set length is %s (%s)" %
(len(retlist[1]), debugstr))
except Exception, e:
logger.info(
"9. exception %s when trying to query, elapsed time was %s (%s)"
% (e, datetime.now() - before, debugstr))
conn.connect(connstr)
logger.info("10. connected ok (%s)" % debugstr)
dbc = conn()
logger.info("11. got dbc ok (%s)" % debugstr)
before2 = datetime.now()
retlist = dbc.query(querystr)
logger.info("12. got query results ok, elapsed time was %s (%s)" %
(datetime.now() - before2, debugstr))
logger.info("12.5. result set length is %s (%s)" %
(len(retlist[1]), debugstr))
def getClassesWithInstructorsByTermCX(self, strm):
request = self.REQUEST
RESPONSE = request.RESPONSE
remote_addr = request.REMOTE_ADDR
if remote_addr in [
Kim_Nguyen_iMac, Joel_Herron_iMac, '127.0.0.1', Plone3,
ws_it_uwosh_edu, Maccabee_Levine, David_Hietpas, MIO_Helios_Server,
Kim_Nguyen_MacBook, Kim_Nguyen_MacBook_IDEA_Lab
]:
file = open(
'/opt/Plone-2.5.5/zeocluster/client2/Extensions/Oracle_Database_Connection_NGUYEN_PRD.txt',
'r')
for line in file.readlines():
if line <> "" and not line.startswith('#'):
connString = line
file.close()
connection = cx_Oracle.connect(connString)
cursor = connection.cursor()
cursor.execute("""
select
c.subject,
c.catalog_nbr,
c.descr,
c.class_section,
c.crse_id,
c.session_code,
c.acad_group,
c.class_nbr,
n.first_name,
n.last_name,
e.email_addr,
cc.course_title_long,
nvl(t.descr, ''),
im.instruction_mode,
im.descr
from
ps_class_tbl c left join ps_crse_topics t on c.crse_id = t.crse_id and c.crs_topic_id = t.crs_topic_id,
ps_names n,
ps_class_instr i,
ps_email_addresses e,
ps_crse_catalog cc,
ps_instruct_mode im
where
c.strm = :arg1
and c.institution = 'UWOSH'
and c.crse_id = i.crse_id
and i.strm = c.strm
and i.class_section = c.class_section
and n.emplid = i.emplid
and n.eff_status = 'A'
and n.name_type = 'PRI'
and n.effdt = (select max(effdt) from ps_names n2 where n2.emplid = n.emplid
and n2.eff_status = n.eff_status
and n2.name_type = n.name_type)
and e.emplid = i.emplid
and e_addr_type = 'CAMP'
and c.crse_id = cc.crse_id
and cc.effdt = (select max(effdt) from ps_crse_catalog where crse_id = cc.crse_id and eff_status = 'A')
and cc.eff_status = 'A'
and c.institution = im.institution
and c.instruction_mode = im.instruction_mode
and im.eff_status = 'A'
and im.effdt = (select max(im2.effdt) from ps_instruct_mode im2 where im2.instruction_mode = im.instruction_mode and eff_status = 'A')
-- AND c.subject = 'ART'
--order by c.subject, c.catalog_nbr, c.class_section
""",
arg1=strm)
retlist = []
for column_1, column_2, column_3, column_4, column_5, column_6, column_7, column_8, column_9, column_10, column_11, column_12, column_13, column_14, column_15 in cursor:
retlist.append([
column_1,
column_2,
column_3,
column_4,
column_5,
column_6,
column_7,
column_8,
column_9,
column_10,
column_11,
column_12,
column_13,
column_14,
column_15,
])
myMarshaller = xmlrpclib.Marshaller(allow_none=True)
return myMarshaller.dumps(retlist)
def getClassesWithInstructorsByTerm(self, strm):
request = self.REQUEST
RESPONSE = request.RESPONSE
remote_addr = request.REMOTE_ADDR
if remote_addr in [
Plone1,
Kim_Nguyen_Air,
Kim_Nguyen_G5,
Kim_Nguyen_iMac,
John_Hren_MBP,
Plone3,
Joel_Herron_iMac,
'127.0.0.1',
]:
conn = getattr(self, 'Oracle_Database_Connection_NGUYEN_PRD')
connstr = conn.connection_string
try:
if not conn.connected():
conn.connect(connstr)
except:
conn.connect(connstr)
dbc = conn()
querystr = """
select
c.subject,
c.catalog_nbr,
c.descr,
c.class_section,
c.crse_id,
c.session_code,
c.acad_group,
c.class_nbr,
c.strm,
n.first_name,
n.last_name,
e.email_addr,
cc.course_title_long
from
ps_class_tbl c,
ps_names n,
ps_class_instr i,
ps_email_addresses e,
ps_crse_catalog cc
where
c.strm = '%s'
and c.institution = 'UWOSH'
and c.crse_id = i.crse_id
and i.strm = c.strm
and i.class_section = c.class_section
and n.emplid = i.emplid
and n.eff_status = 'A'
and n.name_type = 'PRI'
and n.effdt = (select max(effdt) from ps_names n2 where n2.emplid = n.emplid
and n2.eff_status = n.eff_status
and n2.name_type = n.name_type)
and e.emplid = i.emplid
and e_addr_type = 'CAMP'
and c.crse_id = cc.crse_id
and cc.effdt = (select max(effdt) from ps_crse_catalog where crse_id = cc.crse_id and eff_status = 'A')
and cc.eff_status = 'A'
""" % strm
try:
retlist = dbc.query(querystr)
except:
# try the query a second time since it can fail to connect the first time
conn.connect(connstr)
dbc = conn()
retlist = dbc.query(querystr)
myMarshaller = xmlrpclib.Marshaller()
return myMarshaller.dumps(retlist)
def getNextSemestersWithDescription(self):
request = self.REQUEST
RESPONSE = request.RESPONSE
remote_addr = request.REMOTE_ADDR
if remote_addr in [
Plone1,
CMF2,
Randy_Loch,
Kim_Nguyen_Air,
Kim_Nguyen_G5,
Kim_Nguyen_iMac,
John_Hren_MBP,
Plone3,
'127.0.0.1',
]:
conn = getattr(self, 'Oracle_Database_Connection_NGUYEN_PRD')
connstr = conn.connection_string
try:
if not conn.connected():
conn.connect(connstr)
except:
conn.connect(connstr)
dbc = conn()
querystr = "select strm, descr from ps_term_tbl where institution = 'UWOSH' and acad_career = 'UGRD' and term_begin_dt <= sysdate and term_end_dt >= sysdate"
try:
retlist = dbc.query(querystr)
except:
# try the query a second time since it can fail to connect the first time
conn.connect(connstr)
dbc = conn()
retlist = dbc.query(querystr)
if len(retlist) == 2:
if len(retlist[1]) == 1:
if len(retlist[1][0]) == 2:
current_semester = retlist[1][0][0]
current_semester_descr = retlist[1][0][1]
if current_semester:
# now grab the next semester's code, knowing the current semester code
querystr2 = "select t1.strm, t1.descr from ps_term_tbl t1 where t1.institution = 'UWOSH' and t1.acad_career = 'UGRD' and t1.strm = (select min(strm) from ps_term_tbl t2 where t2.institution = t1.institution and t2.acad_career = t1.acad_career and t2.strm > '%s')" % current_semester
else:
# grab the next semester code, a bit differently from above because we are not currently in a semester
querystr2 = "select t1.strm, t1.descr from ps_term_tbl t1 where t1.institution = 'UWOSH' and t1.acad_career = 'UGRD' and t1.term_begin_dt = (select min(term_begin_dt) from ps_term_tbl t2 where t2.institution = t1.institution and t2.acad_career = t1.acad_career and term_begin_dt > sysdate)"
try:
retlist = dbc.query(querystr2)
except:
# try the query a second time since it can fail to connect the first time
conn.connect(connstr)
dbc = conn()
retlist = dbc.query(querystr2)
if len(retlist) == 2:
if len(retlist[1]) == 1:
if len(retlist[1][0]) == 2:
next_semester = retlist[1][0][0]
next_semester_descr = retlist[1][0][1]
myMarshaller = xmlrpclib.Marshaller()
if current_semester:
# return array of both semester data
return myMarshaller.dumps([
(current_semester, current_semester_descr),
(next_semester, next_semester_descr),
])
#return([(current_semester, current_semester_descr), (next_semester, next_semester_descr),])
else:
if next_semester:
# return array of just next semester data
return myMarshaller.dumps([
(next_semester, next_semester_descr),
])
#return([(next_semester, next_semester_descr),])
else:
return "error: unable to determine the next semester code"
