def find_emplid(userid):
"""
Find emplid from userid by looking at email addresses: incorrect in general but works for the few people needed here.
"""
db = SIMSConn()
db.execute("""SELECT emplid FROM ps_email_addresses WHERE email_addr=%s""", (userid+'@sfu.ca',))
return db.fetchone()[0]
def find_emplid(userid):
"""
Find emplid from userid by looking at email addresses: incorrect in general but works for the few people needed here.
"""
db = SIMSConn()
db.execute("""SELECT emplid FROM ps_email_addresses WHERE email_addr=%s""", (userid+'@sfu.ca',))
return db.fetchone()[0]
def guess_userid(emplid):
"""
Find userid from emplid by looking at email addresses: incorrect in general but works enough for some test data.
"""
db = SIMSConn()
db.execute("""SELECT email_addr FROM ps_email_addresses WHERE emplid=%s AND email_addr LIKE '%%@sfu.ca %%'""", (emplid,))
row = db.fetchone()
if row:
email = row[0]
return email[:-7]
def guess_userid(emplid):
"""
Find userid from emplid by looking at email addresses: incorrect in general but works enough for some test data.
"""
db = SIMSConn()
db.execute("""SELECT email_addr FROM ps_email_addresses WHERE emplid=%s AND email_addr LIKE '%%@sfu.ca %%'""", (emplid,))
row = db.fetchone()
if row:
email = row[0]
return email[:-7]
def main():
db = SIMSConn()
out = csv.writer(open(acad_plan + ".csv", 'wb'))
out.writerow(['Emplid', 'Name', 'Attempted Cred', 'As Of', 'CGPA'])
query = "SELECT ap.emplid " \
"FROM ps_acad_plan ap " \
"WHERE ap.acad_plan=%s " \
"GROUP BY ap.emplid ORDER BY ap.emplid"
db.execute(query, (acad_plan, ))
all_emplids = [row[0] for row in list(db)]
#all_emplids = all_emplids[:30]
count = 0
for emplid in all_emplids:
query = "SELECT ap.stdnt_car_nbr " \
"FROM ps_acad_plan ap, " + max_effdt + " as maxeffdt " \
"WHERE maxeffdt.emplid=ap.emplid " \
" AND maxeffdt.stdnt_car_nbr=ap.stdnt_car_nbr" \
" AND ap.effdt=maxeffdt.effdt AND ap.acad_plan=%s " \
"GROUP BY ap.stdnt_car_nbr"
db.execute(query, (emplid, acad_plan))
#print emplid, list(db)
for (car_nbr, ) in list(db):
query = "SELECT ct.cum_gpa, ct.strm, ct.tot_taken_gpa, pd.name " \
"FROM ps_stdnt_car_term ct, ps_personal_data pd " \
"WHERE ct.emplid=pd.emplid AND ct.emplid=%s " \
" AND stdnt_car_nbr=%s " \
" AND tot_taken_gpa>0 " \
"ORDER BY strm DESC FETCH FIRST 1 ROWS ONLY"
db.execute(query, (emplid, car_nbr))
row = db.fetchone()
#print emplid, car_nbr, row
if not row:
continue
gpa, strm, taken, name = row
out.writerow([emplid, name, taken, strm, gpa])
count += 1
out.writerow(
['Average', None, None, None,
"=AVERAGE(E2:E%i)" % (count + 1)])
def get_unit(acad_org, create=False):
"""
Get the corresponding Unit
"""
# there are some inconsistent acad_org values: normalize.
if acad_org == 'GERON':
acad_org = 'GERONTOL'
elif acad_org == 'GEOG':
acad_org = 'GEOGRAPH'
elif acad_org == 'BUS':
acad_org = 'BUS ADMIN'
elif acad_org == 'HUM':
acad_org = 'HUMANITIES'
elif acad_org == 'EVSC':
acad_org = 'ENVIRO SCI'
try:
unit = Unit.objects.get(acad_org=acad_org)
except Unit.DoesNotExist:
db = SIMSConn()
db.execute(
"SELECT descrformal FROM ps_acad_org_tbl "
"WHERE eff_status='A' and acad_org=%s", (acad_org, ))
name, = db.fetchone()
if acad_org == 'COMP SCI': # for test/demo imports
label = 'CMPT'
elif acad_org == 'ENG SCI': # for test/demo imports
label = 'ENSC'
elif acad_org == 'ENVIRONMEN': # for test/demo imports
label = 'FENV'
elif acad_org == 'DEAN GRAD': # for test/demo imports
label = 'GRAD'
else:
label = acad_org[:4].strip()
if create:
unit = Unit(acad_org=acad_org, label=label, name=name, parent=None)
unit.save()
else:
raise KeyError("Unknown unit: acad_org=%s, label~=%s, name~=%s." %
(acad_org, label, name))
return unit
def main():
db = SIMSConn()
out = csv.writer(open(acad_plan+".csv", 'wb'))
out.writerow(['Emplid', 'Name', 'Attempted Cred', 'As Of', 'CGPA'])
query = "SELECT ap.emplid " \
"FROM ps_acad_plan ap " \
"WHERE ap.acad_plan=%s " \
"GROUP BY ap.emplid ORDER BY ap.emplid"
db.execute(query, (acad_plan,))
all_emplids = [row[0] for row in list(db)]
#all_emplids = all_emplids[:30]
count = 0
for emplid in all_emplids:
query = "SELECT ap.stdnt_car_nbr " \
"FROM ps_acad_plan ap, " + max_effdt + " as maxeffdt " \
"WHERE maxeffdt.emplid=ap.emplid " \
" AND maxeffdt.stdnt_car_nbr=ap.stdnt_car_nbr" \
" AND ap.effdt=maxeffdt.effdt AND ap.acad_plan=%s " \
"GROUP BY ap.stdnt_car_nbr"
db.execute(query, (emplid,acad_plan))
#print emplid, list(db)
for (car_nbr,) in list(db):
query = "SELECT ct.cum_gpa, ct.strm, ct.tot_taken_gpa, pd.name " \
"FROM ps_stdnt_car_term ct, ps_personal_data pd " \
"WHERE ct.emplid=pd.emplid AND ct.emplid=%s " \
" AND stdnt_car_nbr=%s " \
" AND tot_taken_gpa>0 " \
"ORDER BY strm DESC FETCH FIRST 1 ROWS ONLY"
db.execute(query, (emplid,car_nbr))
row = db.fetchone()
#print emplid, car_nbr, row
if not row:
continue
gpa, strm, taken, name = row
out.writerow([emplid, name, taken, strm, gpa])
count += 1
out.writerow(['Average', None, None, None, "=AVERAGE(E2:E%i)"%(count+1)])
def get_unit(acad_org, create=False):
"""
Get the corresponding Unit
"""
# there are some inconsistent acad_org values: normalize.
if acad_org == 'GERON':
acad_org = 'GERONTOL'
elif acad_org == 'GEOG':
acad_org = 'GEOGRAPH'
elif acad_org == 'BUS':
acad_org = 'BUS ADMIN'
elif acad_org == 'HUM':
acad_org = 'HUMANITIES'
elif acad_org == 'EVSC':
acad_org = 'ENVIRO SCI'
try:
unit = Unit.objects.get(acad_org=acad_org)
except Unit.DoesNotExist:
db = SIMSConn()
db.execute("SELECT descrformal FROM ps_acad_org_tbl "
"WHERE eff_status='A' and acad_org=%s", (acad_org,))
name, = db.fetchone()
if acad_org == 'COMP SCI': # for test/demo imports
label = 'CMPT'
elif acad_org == 'ENG SCI': # for test/demo imports
label = 'ENSC'
elif acad_org == 'ENVIRONMEN': # for test/demo imports
label = 'FENV'
elif acad_org == 'DEAN GRAD': # for test/demo imports
label = 'GRAD'
else:
label = acad_org[:4].strip()
if create:
unit = Unit(acad_org=acad_org, label=label, name=name, parent=None)
unit.save()
else:
raise KeyError("Unknown unit: acad_org=%s, label~=%s, name~=%s." % (acad_org, label, name))
return unit
