def grad_metadata(emplids):
"""
Metadata about a grad student: application email address, native language, citizenship, work visa status.
LEFT JOINs many things onto ps_personal_data to get lots out of the way in one query.
"""
db = SIMSConn()
# The s1, s2 column are to sort so we get the "good" language/country first: let speaking English or being Canadian
# win (since they might be better for that student's TA/RA appointments later).
# Other sort orders just to make sure we get the same record tomorrow if there are other duplicates (visa can duplicate)
db.execute(
"""
SELECT 'GradMetadata', p.emplid, e.email_addr, a.accomplishment, cit.country, v.visa_permit_type, v.effdt,
case when (a.accomplishment='ENG') then 0 else 1 end s1,
case when (cit.country='CAN') then 0 else 1 end s2
FROM ps_personal_data p
LEFT JOIN ps_email_addresses e
ON (p.emplid=e.emplid AND e.pref_email_flag='Y' and e.e_addr_type<>'INAC')
LEFT JOIN ps_accomplishments a
ON (a.emplid=p.emplid AND a.native_language='Y')
LEFT JOIN ps_citizenship cit
ON (cit.emplid=p.emplid)
LEFT JOIN ps_visa_pmt_data v
ON (p.emplid=v.emplid
AND v.effdt = (SELECT MAX(tmp.effdt)
FROM ps_visa_pmt_data tmp
WHERE tmp.emplid = v.emplid
AND tmp.effdt <= current date ))
WHERE p.emplid IN %s
ORDER BY s1, s2, e.email_addr, a.accomplishment, cit.country, v.visa_permit_type""",
(emplids, ))
return list(db)
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 grad_appl_program_changes(acad_prog):
"""
ps_adm_appl_data records where the fee has actually been paid: we don't bother looking at them until then.
Rows become ApplProgramChange objects.
Many of these will duplicate ps_acad_prog: the ProgramStatusChange is smart enough to identify them.
The 13th null argument has been added because ApplProgramChange subclasses ProgramStatusChange, which now requires
an extra degr_chkout_stat argument to find the grad application/approved statuses.
"""
db = SIMSConn()
db.execute(
"""
SELECT 'ApplProgramChange', prog.emplid, prog.stdnt_car_nbr, prog.adm_appl_nbr, prog.acad_prog, prog.prog_status, prog.prog_action, prog.prog_reason,
prog.effdt, prog.effseq, prog.admit_term, prog.exp_grad_term, null, plan.ACAD_SUB_PLAN
FROM ps_adm_appl_prog prog
LEFT JOIN ps_acad_subplan plan ON prog.emplid=plan.emplid AND prog.EFFDT=plan.effdt
LEFT JOIN dbcsown.ps_adm_appl_data data
ON prog.emplid=data.emplid AND prog.acad_career=data.acad_career AND prog.stdnt_car_nbr=data.stdnt_car_nbr AND prog.adm_appl_nbr=data.adm_appl_nbr
WHERE prog.acad_career='GRAD' AND prog.acad_prog=%s AND prog.effdt>=%s AND prog.admit_term>=%s
AND ( data.appl_fee_status in ('REC', 'WVD')
OR data.adm_appl_ctr in ('GRAW') )
ORDER BY prog.effdt, prog.effseq
""", (acad_prog, IMPORT_START_DATE, IMPORT_START_SEMESTER))
return list(db)
def program_headcount(strm):
db = SIMSConn()
query = """WITH STDNT_CAR_TERM AS
( SELECT emplid
FROM CS.PS_STDNT_CAR_TERM
WHERE withdraw_code='NWD' AND strm=%s
), ACAD_PROG AS
( SELECT emplid, stdnt_car_nbr
FROM CS.PS_ACAD_PROG A
WHERE prog_status='AC'
AND effdt=(SELECT MAX(effdt) FROM CS.PS_ACAD_PROG WHERE emplid=A.emplid)
AND effseq=(SELECT MAX(effseq) FROM CS.PS_ACAD_PROG WHERE emplid=A.emplid AND effdt=A.effdt)
), ACAD_PLAN AS
( SELECT emplid, stdnt_car_nbr, acad_plan
FROM CS.PS_ACAD_PLAN A
WHERE effdt=(SELECT MAX(effdt) FROM CS.PS_ACAD_PLAN WHERE emplid=A.emplid)
AND effseq=(SELECT MAX(effseq) FROM CS.PS_ACAD_PLAN WHERE emplid=A.emplid AND effdt=A.effdt)
), ACAD_PLAN_TBL AS
( SELECT acad_plan, acad_plan_type, study_field, trnscr_descr descr
FROM CS.PS_ACAD_PLAN_TBL A
WHERE effdt=(SELECT MAX(effdt) FROM CS.PS_ACAD_PLAN_TBL WHERE acad_plan=A.acad_plan)
)
SELECT APL.acad_plan, APLT.descr, count(SCT.emplid) as headcount
FROM STDNT_CAR_TERM SCT INNER JOIN ACAD_PROG APR ON SCT.emplid=APR.emplid
INNER JOIN ACAD_PLAN APL ON APR.emplid=APL.emplid AND APR.stdnt_car_nbr=APL.stdnt_car_nbr
INNER JOIN ACAD_PLAN_TBL APLT ON APL.acad_plan=APLT.acad_plan
INNER JOIN CS.PS_PERSONAL_DATA P ON SCT.emplid=P.emplid
LEFT JOIN CS.PS_EMAIL_ADDRESSES E ON SCT.emplid=E.emplid
WHERE (APLT.study_field IN ('CMPT', 'ENSC', 'MSE') or APLT.acad_plan LIKE 'MSE%%')
AND E.pref_email_flag = 'Y'
GROUP BY APL.acad_plan, APLT.descr
"""
db.execute(query, (strm,))
return list(db)
def import_all_instructors(strm, extra_where='1=1', offering_map=None):
if not offering_map:
offering_map = crseid_offering_map(strm)
Member.objects.filter(added_reason="AUTO",
offering__semester__name=strm,
role="INST").update(role='DROP')
db = SIMSConn()
db.execute("SELECT crse_id, class_section, strm, emplid, instr_role, sched_print_instr FROM ps_class_instr WHERE " \
"strm=%s AND instr_role IN ('PI', 'SI') AND " + extra_where,
(strm,))
for crse_id, class_section, strm, emplid, instr_role, sched_print_instr in db.rows(
):
if not emplid or (strm, crse_id, class_section) not in offering_map:
continue
offering = offering_map[(strm, crse_id, class_section)]
p = get_person(emplid)
ensure_member(p,
offering,
"INST",
0,
"AUTO",
"NONS",
sched_print_instr=sched_print_instr)
def grad_metadata(emplids):
"""
Metadata about a grad student: application email address, native language, citizenship, work visa status.
LEFT JOINs many things onto ps_personal_data to get lots out of the way in one query.
"""
db = SIMSConn()
# The s1, s2 column are to sort so we get the "good" language/country first: let speaking English or being Canadian
# win (since they might be better for that student's TA/RA appointments later).
# Other sort orders just to make sure we get the same record tomorrow if there are other duplicates (visa can duplicate)
db.execute("""
SELECT 'GradMetadata', p.emplid, e.email_addr, a.accomplishment, cit.country, v.visa_permit_type, v.effdt,
case when (a.accomplishment='ENG') then 0 else 1 end s1,
case when (cit.country='CAN') then 0 else 1 end s2
FROM ps_personal_data p
LEFT JOIN ps_email_addresses e
ON (p.emplid=e.emplid AND e.pref_email_flag='Y' and e.e_addr_type<>'INAC')
LEFT JOIN ps_accomplishments a
ON (a.emplid=p.emplid AND a.native_language='Y')
LEFT JOIN ps_citizenship cit
ON (cit.emplid=p.emplid)
LEFT JOIN ps_visa_pmt_data v
ON (p.emplid=v.emplid
AND v.effdt = (SELECT MAX(tmp.effdt)
FROM ps_visa_pmt_data tmp
WHERE tmp.emplid = v.emplid
AND tmp.effdt <= current date ))
WHERE p.emplid IN %s
ORDER BY s1, s2, e.email_addr, a.accomplishment, cit.country, v.visa_permit_type""", (emplids,))
return list(db)
def import_offerings(extra_where='1=1',
import_semesters=import_semesters,
cancel_missing=False,
create_units=False):
db = SIMSConn()
db.execute(
CLASS_TBL_QUERY + " AND ct.strm IN %s "
" AND (" + extra_where + ")", (import_semesters(), ))
imported_offerings = set()
for row in db.rows():
o = import_offering(*row, create_units=create_units)
if o:
imported_offerings.add(o)
if cancel_missing:
# mark any offerings not found during the import as cancelled: handles sections that just disappear from
# ps_class_tbl, because that can happen, apparently.
all_off = CourseOffering.objects.filter(semester__name__in=import_semesters()) \
.exclude(component='CAN').exclude(flags=CourseOffering.flags.combined)
all_off = set(all_off)
for o in all_off - imported_offerings:
o.component = 'CAN'
o.save()
return imported_offerings
def check_sims_connection():
if settings.DISABLE_REPORTING_DB:
return
from coredata.queries import SIMSConn, SIMSProblem
db = SIMSConn()
db.execute("SELECT descr FROM dbcsown.PS_TERM_TBL WHERE strm='1111'", ())
if len(list(db)) == 0:
raise SIMSProblem("Didn't get any data back from SIMS query.")
def semester_first_day():
" First day of classes"
db = SIMSConn()
db.execute("""
SELECT strm, sess_begin_dt
FROM ps_session_tbl
WHERE acad_career='UGRD' AND session_code='1'""", ())
return dict(db)
def semester_first_day():
" First day of classes"
db = SIMSConn()
db.execute("""
SELECT strm, sess_begin_dt
FROM ps_session_tbl
WHERE acad_career='UGRD' AND session_code='1'""", ())
return dict(db)
def all_courses(strm):
db = SIMSConn()
db.execute("""SELECT subject, catalog_nbr, class_section, class_nbr, campus, trm.term_begin_dt FROM ps_class_tbl cls, ps_term_tbl trm WHERE
cls.strm=trm.strm AND class_section LIKE '%%00' AND trm.acad_career='UGRD'
AND cls.cancel_dt IS NULL AND cls.acad_org='COMP SCI'
AND cls.strm=%s
ORDER BY subject ASC, catalog_nbr ASC, class_section ASC""",
(strm,))
return list(db)
def all_courses(strm):
db = SIMSConn()
db.execute(
"""SELECT subject, catalog_nbr, class_section, class_nbr, campus, trm.term_begin_dt FROM ps_class_tbl cls, ps_term_tbl trm WHERE
cls.strm=trm.strm AND class_section LIKE '%%00' AND trm.acad_career='UGRD'
AND cls.cancel_dt IS NULL AND cls.acad_org='COMP SCI'
AND cls.strm=%s
ORDER BY subject ASC, catalog_nbr ASC, class_section ASC""",
(strm, ))
return list(db)
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 research_areas(emplids):
"""
Research areas from these students' applications.
"""
db = SIMSConn()
db.execute("""
SELECT 'GradResearchArea', emplid, adm_appl_nbr, acad_org, sfu_ga_res_area, sfu_ga_reschoices
FROM ps_sfu_ga_res_det data
WHERE data.emplid in %s""", (emplids,))
return list(db)
def semester_last_day():
"""
Dict of strm -> last day of the semester's classes
"""
# Why 250? Because "SELECT * FROM psxlatitem WHERE fieldname='TIME_PERIOD'"
db = SIMSConn()
db.execute("""
SELECT strm, end_dt
FROM ps_sess_time_perod
WHERE time_period=250 AND acad_career='UGRD' AND session_code='1'""", ())
return dict(db)
def research_areas(emplids):
"""
Research areas from these students' applications.
"""
db = SIMSConn()
db.execute(
"""
SELECT 'GradResearchArea', emplid, adm_appl_nbr, acad_org, sfu_ga_res_area, sfu_ga_reschoices
FROM ps_sfu_ga_res_det data
WHERE data.emplid in %s""", (emplids, ))
return list(db)
def semester_last_day():
"""
Dict of strm -> last day of the semester's classes
"""
# Why 250? Because "SELECT * FROM psxlatitem WHERE fieldname='TIME_PERIOD'"
db = SIMSConn()
db.execute("""
SELECT strm, end_dt
FROM ps_sess_time_perod
WHERE time_period=250 AND acad_career='UGRD' AND session_code='1'""", ())
return dict(db)
def import_instructors(offering):
"Import instructors for this offering"
Member.objects.filter(added_reason="AUTO", offering=offering, role="INST").update(role='DROP')
db = SIMSConn()
db.execute("SELECT emplid, instr_role, sched_print_instr FROM ps_class_instr WHERE " \
"crse_id=%s and class_section=%s and strm=%s and instr_role IN ('PI', 'SI')",
("%06i" % (int(offering.crse_id)), offering.section, offering.semester.name))
for emplid, _, sched_print_instr in db.rows():
if not emplid:
continue
p = get_person(emplid)
ensure_member(p, offering, "INST", 0, "AUTO", "NONS", sched_print_instr=sched_print_instr)
def import_instructors(offering):
"Import instructors for this offering"
Member.objects.filter(added_reason="AUTO", offering=offering, role="INST").update(role='DROP')
db = SIMSConn()
db.execute("SELECT emplid, instr_role, sched_print_instr FROM ps_class_instr WHERE " \
"crse_id=%s and class_section=%s and strm=%s and instr_role='PI' and sched_print_instr='Y'",
("%06i" % (int(offering.crse_id)), offering.section, offering.semester.name))
for emplid, _, _ in db.rows():
if not emplid:
continue
p = get_person(emplid)
ensure_member(p, offering, "INST", 0, "AUTO", "NONS")
def import_all_meeting_times(strm, extra_where='1=1', offering_map=None):
if not offering_map:
offering_map = crseid_offering_map(strm)
db = SIMSConn()
db.execute("""SELECT crse_id, class_section, strm, meeting_time_start, meeting_time_end, facility_id, mon,tues,wed,thurs,fri,sat,sun,
start_dt, end_dt, stnd_mtg_pat FROM ps_class_mtg_pat WHERE strm=%s AND """ + extra_where,
(strm,))
# keep track of meetings we've found, so we can remove old (non-importing semesters and changed/gone)
found_mtg = set()
for crse_id, class_section, strm, start, end, room, mon, tues, wed, thurs, fri, sat, sun, start_dt, end_dt, stnd_mtg_pat in db:
try:
offering = offering_map[(strm, crse_id, class_section)]
except KeyError:
continue
# dates come in as strings from DB2/reporting DB
start_dt = datetime.datetime.strptime(start_dt, "%Y-%m-%d").date()
end_dt = datetime.datetime.strptime(end_dt, "%Y-%m-%d").date()
if not start or not end:
# some meeting times exist with no start/end time
continue
wkdays = [n for n, day in zip(range(7), (mon, tues, wed, thurs, fri, sat, sun)) if day == 'Y']
labtut_section, mtg_type = fix_mtg_info(class_section, stnd_mtg_pat)
for wkd in wkdays:
m_old = MeetingTime.objects.filter(offering=offering, weekday=wkd, start_time=start, end_time=end,
labtut_section=labtut_section, room=room)
if len(m_old) > 1:
raise KeyError, "Already duplicate meeting: %r" % (m_old)
elif len(m_old) == 1:
# new data: just replace.
m_old = m_old[0]
if m_old.start_day == start_dt and m_old.end_day == end_dt and m_old.room == room \
and m_old.meeting_type == mtg_type and m_old.labtut_section == labtut_section:
# unchanged: leave it.
found_mtg.add(m_old.id)
continue
else:
# it has changed: remove and replace.
m_old.delete()
m = MeetingTime(offering=offering, weekday=wkd, start_day=start_dt, end_day=end_dt,
start_time=start, end_time=end, room=room, labtut_section=labtut_section)
m.meeting_type = mtg_type
m.save()
found_mtg.add(m.id)
# delete any meeting times we haven't found in the DB
if extra_where == '1=1':
MeetingTime.objects.filter(offering__semester__name=strm).exclude(id__in=found_mtg).delete()
def plans_desc(acad_plans):
db = SIMSConn()
query = """SELECT acad_plan, trnscr_descr
FROM PS_ACAD_PLAN_TBL apt
WHERE eff_status='A' AND acad_plan IN %s
AND effdt=(SELECT MAX(effdt) FROM PS_ACAD_PLAN_TBL WHERE acad_plan=apt.acad_plan)
ORDER BY acad_plan"""
acad_plans = list(acad_plans)
db.execute(query, (acad_plans, ))
out = csv.writer(open("programs.csv", 'wb'))
out.writerows(db)
def plans_desc(acad_plans):
db = SIMSConn()
query = """SELECT acad_plan, trnscr_descr
FROM PS_ACAD_PLAN_TBL apt
WHERE eff_status='A' AND acad_plan IN %s
AND effdt=(SELECT MAX(effdt) FROM PS_ACAD_PLAN_TBL WHERE acad_plan=apt.acad_plan)
ORDER BY acad_plan"""
acad_plans = list(acad_plans)
db.execute(query, (acad_plans,))
out = csv.writer(open("programs.csv", 'wb'))
out.writerows(db)
def grad_program_changes(acad_prog):
"""
Records from ps_acad_prog about students' progress in this program. Rows become ProgramStatusChange objects.
"""
db = SIMSConn()
db.execute("""
SELECT 'ProgramStatusChange', emplid, stdnt_car_nbr, adm_appl_nbr, acad_prog, prog_status, prog_action, prog_reason,
effdt, effseq, admit_term, exp_grad_term
FROM ps_acad_prog
WHERE acad_career='GRAD' AND acad_prog=%s AND effdt>=%s AND admit_term>=%s
ORDER BY effdt, effseq
""", (acad_prog, IMPORT_START_DATE, IMPORT_START_SEMESTER))
return list(db)
def grad_semesters(emplids):
"""
Semesters when the student was taking classes: use to mark them active (since sometimes ps_acad_prog doesn't).
"""
db = SIMSConn()
db.execute("""
SELECT 'GradSemester', emplid, strm, stdnt_car_nbr, withdraw_code, acad_prog_primary, unt_taken_prgrss
FROM ps_stdnt_car_term
WHERE acad_career='GRAD' AND emplid in %s AND strm>=%s
AND unt_taken_prgrss>0
ORDER BY strm
""", (emplids, IMPORT_START_SEMESTER))
return list(db)
def grad_program_changes(acad_prog):
"""
Records from ps_acad_prog about students' progress in this program. Rows become ProgramStatusChange objects.
"""
db = SIMSConn()
db.execute(
"""
SELECT 'ProgramStatusChange', emplid, stdnt_car_nbr, adm_appl_nbr, acad_prog, prog_status, prog_action, prog_reason,
effdt, effseq, admit_term, exp_grad_term
FROM ps_acad_prog
WHERE acad_career='GRAD' AND acad_prog=%s AND effdt>=%s AND admit_term>=%s
ORDER BY effdt, effseq
""", (acad_prog, IMPORT_START_DATE, IMPORT_START_SEMESTER))
return list(db)
def plans_as_of(dt, emplids):
db = SIMSConn()
if not emplids:
return []
query = """SELECT emplid, stdnt_car_nbr, acad_plan
FROM CS.PS_ACAD_PLAN A
WHERE
effdt=(SELECT MAX(effdt) FROM CS.PS_ACAD_PLAN WHERE emplid=A.emplid AND effdt<=%s)
AND effseq=(SELECT MAX(effseq) FROM CS.PS_ACAD_PLAN WHERE emplid=A.emplid AND effdt=A.effdt)
AND emplid IN %s
ORDER BY emplid, plan_sequence"""
db.execute(query, (dt, emplids))
return [(emplid,[prog[2] for prog in progit]) for emplid,progit in itertools.groupby(db, lambda t: t[0])]
def grad_semesters(emplids):
"""
Semesters when the student was taking classes: use to mark them active (since sometimes ps_acad_prog doesn't).
"""
db = SIMSConn()
db.execute(
"""
SELECT 'GradSemester', emplid, strm, stdnt_car_nbr, withdraw_code, acad_prog_primary, unt_taken_prgrss
FROM ps_stdnt_car_term
WHERE acad_career='GRAD' AND emplid in %s AND strm>=%s
AND unt_taken_prgrss>0
ORDER BY strm
""", (emplids, IMPORT_START_SEMESTER))
return list(db)
def import_all_meeting_times(strm, extra_where='1=1', offering_map=None):
if not offering_map:
offering_map = crseid_offering_map(strm)
db = SIMSConn()
db.execute("""SELECT crse_id, class_section, strm, meeting_time_start, meeting_time_end, facility_id, mon,tues,wed,thurs,fri,sat,sun,
start_dt, end_dt, stnd_mtg_pat FROM ps_class_mtg_pat WHERE strm=%s AND """ + extra_where,
(strm,))
# keep track of meetings we've found, so we can remove old (non-importing semesters and changed/gone)
found_mtg = set()
for crse_id, class_section, strm, start, end, room, mon, tues, wed, thurs, fri, sat, sun, start_dt, end_dt, stnd_mtg_pat in db:
try:
offering = offering_map[(strm, crse_id, class_section)]
except KeyError:
continue
if not start or not end:
# some meeting times exist with no start/end time
continue
wkdays = [n for n, day in zip(list(range(7)), (mon, tues, wed, thurs, fri, sat, sun)) if day == 'Y']
labtut_section, mtg_type = fix_mtg_info(class_section, stnd_mtg_pat)
for wkd in wkdays:
m_old = MeetingTime.objects.filter(offering=offering, weekday=wkd, start_time=start, end_time=end,
labtut_section=labtut_section, room=room)
if len(m_old) > 1:
raise KeyError("Already duplicate meeting: %r" % (m_old))
elif len(m_old) == 1:
# new data: just replace.
m_old = m_old[0]
if m_old.start_day == start_dt and m_old.end_day == end_dt and m_old.room == room \
and m_old.meeting_type == mtg_type and m_old.labtut_section == labtut_section:
# unchanged: leave it.
found_mtg.add(m_old.id)
continue
else:
# it has changed: remove and replace.
m_old.delete()
m = MeetingTime(offering=offering, weekday=wkd, start_day=start_dt, end_day=end_dt,
start_time=start, end_time=end, room=room, labtut_section=labtut_section)
m.meeting_type = mtg_type
m.save()
found_mtg.add(m.id)
# delete any meeting times we haven't found in the DB
if extra_where == '1=1':
MeetingTime.objects.filter(offering__semester__name=strm).exclude(id__in=found_mtg).delete()
def plans_as_of(dt, emplids):
db = SIMSConn()
if not emplids:
return []
query = """SELECT emplid, stdnt_car_nbr, acad_plan
FROM CS.PS_ACAD_PLAN A
WHERE
effdt=(SELECT MAX(effdt) FROM CS.PS_ACAD_PLAN WHERE emplid=A.emplid AND effdt<=%s)
AND effseq=(SELECT MAX(effseq) FROM CS.PS_ACAD_PLAN WHERE emplid=A.emplid AND effdt=A.effdt)
AND emplid IN %s
ORDER BY emplid, plan_sequence"""
db.execute(query, (dt, emplids))
return [(emplid, [prog[2] for prog in progit])
for emplid, progit in itertools.groupby(db, lambda t: t[0])]
def grad_program_changes(acad_prog):
"""
Records from ps_acad_prog about students' progress in this program. Rows become ProgramStatusChange objects.
"""
db = SIMSConn()
db.execute("""
SELECT 'ProgramStatusChange', prog.emplid, prog.stdnt_car_nbr, adm_appl_nbr, prog.acad_prog, prog.prog_status,
prog.prog_action, prog.prog_reason, prog.effdt, prog.effseq, prog.admit_term, prog.exp_grad_term,
prog.degr_chkout_stat, plan.acad_sub_plan
FROM ps_acad_prog prog
LEFT JOIN ps_acad_subplan plan ON prog.emplid=plan.emplid AND prog.EFFDT=plan.effdt
WHERE prog.acad_career='GRAD' AND prog.acad_prog=%s AND prog.effdt>=%s AND prog.admit_term>=%s
ORDER BY effdt, effseq
""", (acad_prog, IMPORT_START_DATE, IMPORT_START_SEMESTER))
return list(db)
def import_joint(extra_where='1=1'):
"""
Find combined sections and set CourseOffering.config['joint_with'] appropriately.
"""
db = SIMSConn()
db.execute("SELECT strm, class_nbr, sctn_combined_id FROM ps_sctn_cmbnd c WHERE c.strm IN %s "
" AND ("+extra_where+")", (import_semesters(),))
for k,v in itertools.groupby(db, lambda d: (d[0], d[2])):
# for each combined offering...
strm, _ = k
class_nbrs = [int(class_nbr) for _,class_nbr,_ in v]
offerings = CourseOffering.objects.filter(semester__name=strm, class_nbr__in=class_nbrs)
for offering in offerings:
offering.set_joint_with([o.slug for o in offerings if o != offering])
offering.save()
def grad_program_changes(acad_prog):
"""
Records from ps_acad_prog about students' progress in this program. Rows become ProgramStatusChange objects.
"""
db = SIMSConn()
db.execute(
"""
SELECT 'ProgramStatusChange', prog.emplid, prog.stdnt_car_nbr, adm_appl_nbr, prog.acad_prog, prog.prog_status,
prog.prog_action, prog.prog_reason, prog.effdt, prog.effseq, prog.admit_term, prog.exp_grad_term,
prog.degr_chkout_stat, plan.acad_sub_plan
FROM ps_acad_prog prog
LEFT JOIN ps_acad_subplan plan ON prog.emplid=plan.emplid AND prog.EFFDT=plan.effdt
WHERE prog.acad_career='GRAD' AND prog.acad_prog=%s AND prog.effdt>=%s AND prog.admit_term>=%s
ORDER BY effdt, effseq
""", (acad_prog, IMPORT_START_DATE, IMPORT_START_SEMESTER))
return list(db)
def import_joint(extra_where='1=1'):
"""
Find combined sections and set CourseOffering.config['joint_with'] appropriately.
"""
db = SIMSConn()
db.execute("SELECT strm, class_nbr, sctn_combined_id FROM ps_sctn_cmbnd c WHERE c.strm IN %s "
" AND ("+extra_where+")", (import_semesters(),))
for k,v in itertools.groupby(db, lambda d: (d[0], d[2])):
# for each combined offering...
strm, _ = k
class_nbrs = [int(class_nbr) for _,class_nbr,_ in v]
offerings = CourseOffering.objects.filter(semester__name=strm, class_nbr__in=class_nbrs)
for offering in offerings:
offering.set_joint_with([o.slug for o in offerings if o != offering])
offering.save()
def import_all_instructors(strm, extra_where='1=1', offering_map=None):
if not offering_map:
offering_map = crseid_offering_map(strm)
Member.objects.filter(added_reason="AUTO", offering__semester__name=strm, role="INST").update(role='DROP')
db = SIMSConn()
db.execute("SELECT crse_id, class_section, strm, emplid, instr_role, sched_print_instr FROM ps_class_instr WHERE " \
"strm=%s AND instr_role IN ('PI', 'SI') AND " + extra_where,
(strm,))
for crse_id, class_section, strm, emplid, instr_role, sched_print_instr in db.rows():
if not emplid or (strm, crse_id, class_section) not in offering_map:
continue
offering = offering_map[(strm, crse_id, class_section)]
p = get_person(emplid)
ensure_member(p, offering, "INST", 0, "AUTO", "NONS", sched_print_instr=sched_print_instr)
def import_meeting_times(offering):
"""
Import course meeting times
"""
db = SIMSConn()
db.execute("SELECT meeting_time_start, meeting_time_end, facility_id, mon,tues,wed,thurs,fri,sat,sun, "
"start_dt, end_dt, stnd_mtg_pat, class_section FROM ps_class_mtg_pat "
"WHERE crse_id=%s and class_section like %s and strm=%s",
("%06i" % (int(offering.crse_id)), offering.section[0:2]+"%", offering.semester.name))
# keep track of meetings we've found, so we can remove old (non-importing semesters and changed/gone)
found_mtg = set()
for start,end, room, mon,tues,wed,thurs,fri,sat,sun, start_dt,end_dt, stnd_mtg_pat, class_section in db:
# dates come in as strings from DB2/reporting DB
start_dt = datetime.datetime.strptime(start_dt, "%Y-%m-%d").date()
end_dt = datetime.datetime.strptime(end_dt, "%Y-%m-%d").date()
if not start or not end:
# some meeting times exist with no start/end time
continue
wkdays = [n for n, day in zip(range(7), (mon,tues,wed,thurs,fri,sat,sun)) if day=='Y']
labtut_section, mtg_type = fix_mtg_info(class_section, stnd_mtg_pat)
for wkd in wkdays:
m_old = MeetingTime.objects.filter(offering=offering, weekday=wkd, start_time=start, end_time=end, labtut_section=labtut_section, room=room)
if len(m_old)>1:
raise KeyError, "Already duplicate meeting: %r" % (m_old)
elif len(m_old)==1:
# new data: just replace.
m_old = m_old[0]
if m_old.start_day==start_dt and m_old.end_day==end_dt and m_old.room==room \
and m_old.meeting_type==mtg_type and m_old.labtut_section==labtut_section:
# unchanged: leave it.
found_mtg.add(m_old.id)
continue
else:
# it has changed: remove and replace.
m_old.delete()
m = MeetingTime(offering=offering, weekday=wkd, start_day=start_dt, end_day=end_dt,
start_time=start, end_time=end, room=room, labtut_section=labtut_section)
m.meeting_type = mtg_type
m.save()
found_mtg.add(m.id)
# delete any meeting times we haven't found in the DB
MeetingTime.objects.filter(offering=offering).exclude(id__in=found_mtg).delete()
def import_all_students(strm, extra_where='1=1', offering_map=None):
if not offering_map:
offering_map = crseid_offering_map(strm)
Member.objects.filter(added_reason="AUTO",
offering__semester__name=strm,
role="STUD").update(role='DROP')
db = SIMSConn()
def import_meeting_times(offering):
"""
Import course meeting times
"""
db = SIMSConn()
db.execute("SELECT meeting_time_start, meeting_time_end, facility_id, mon,tues,wed,thurs,fri,sat,sun, "
"start_dt, end_dt, stnd_mtg_pat, class_section FROM ps_class_mtg_pat "
"WHERE crse_id=%s and class_section like %s and strm=%s",
("%06i" % (int(offering.crse_id)), offering.section[0:2]+"%", offering.semester.name))
# keep track of meetings we've found, so we can remove old (non-importing semesters and changed/gone)
found_mtg = set()
for start,end, room, mon,tues,wed,thurs,fri,sat,sun, start_dt,end_dt, stnd_mtg_pat, class_section in db:
# dates come in as strings from DB2/reporting DB
start_dt = datetime.datetime.strptime(start_dt, "%Y-%m-%d").date()
end_dt = datetime.datetime.strptime(end_dt, "%Y-%m-%d").date()
if not start or not end:
# some meeting times exist with no start/end time
continue
wkdays = [n for n, day in zip(range(7), (mon,tues,wed,thurs,fri,sat,sun)) if day=='Y']
labtut_section, mtg_type = fix_mtg_info(class_section, stnd_mtg_pat)
for wkd in wkdays:
m_old = MeetingTime.objects.filter(offering=offering, weekday=wkd, start_time=start, end_time=end, labtut_section=labtut_section, room=room)
if len(m_old)>1:
raise KeyError, "Already duplicate meeting: %r" % (m_old)
elif len(m_old)==1:
# new data: just replace.
m_old = m_old[0]
if m_old.start_day==start_dt and m_old.end_day==end_dt and m_old.room==room \
and m_old.meeting_type==mtg_type and m_old.labtut_section==labtut_section:
# unchanged: leave it.
found_mtg.add(m_old.id)
continue
else:
# it has changed: remove and replace.
m_old.delete()
m = MeetingTime(offering=offering, weekday=wkd, start_day=start_dt, end_day=end_dt,
start_time=start, end_time=end, room=room, labtut_section=labtut_section)
m.meeting_type = mtg_type
m.save()
found_mtg.add(m.id)
# delete any meeting times we haven't found in the DB
MeetingTime.objects.filter(offering=offering).exclude(id__in=found_mtg).delete()
def import_students(offering):
Member.objects.filter(added_reason="AUTO", offering=offering, role="STUD").update(role='DROP')
db = SIMSConn()
# find any lab/tutorial sections
# c1 original lecture section
# c2 related lab/tutorial section
# s students in c2
# WHERE lines: (1) match lab/tut sections of c1 class (2) students in those
# lab/tut sections (3) with c1 matching offering
query = "SELECT s.emplid, c2.class_section " \
"FROM ps_class_tbl c1, ps_class_tbl c2, ps_stdnt_enrl s " \
"WHERE c1.subject=c2.subject and c1.catalog_nbr=c2.catalog_nbr and c2.strm=c1.strm " \
"and s.class_nbr=c2.class_nbr and s.strm=c2.strm and s.enrl_status_reason IN ('ENRL','EWAT') " \
"and c1.class_nbr=%s and c1.strm=%s and c2.class_section LIKE %s"
db.execute(query, (offering.class_nbr, offering.semester.name, offering.section[0:2]+"%"))
labtut = {}
for emplid, section in db:
if section == offering.section:
# not interested in lecture section now.
continue
labtut[emplid] = section
db.execute("SELECT e.emplid, e.acad_career, e.unt_taken, e.crse_grade_off, r.crse_grade_input "
"FROM ps_stdnt_enrl e LEFT JOIN ps_grade_roster r "
"ON e.strm=r.strm and e.acad_career=r.acad_career and e.emplid=r.emplid and e.class_nbr=r.class_nbr "
"WHERE e.class_nbr=%s and e.strm=%s and e.stdnt_enrl_status='E'", (offering.class_nbr, offering.semester.name))
for emplid, acad_career, unt_taken, grade_official, grade_roster in db.rows():
p = get_person(emplid)
sec = labtut.get(emplid, None)
grade = grade_official or grade_roster
ensure_member(p, offering, "STUD", unt_taken, "AUTO", acad_career, labtut_section=sec, grade=grade)
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 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 grad_appl_program_changes(acad_prog):
"""
ps_adm_appl_data records where the fee has actually been paid: we don't bother looking at them until then.
Rows become ApplProgramChange objects.
Many of these will duplicate ps_acad_prog: the ProgramStatusChange is smart enough to identify them.
"""
db = SIMSConn()
db.execute("""
SELECT 'ApplProgramChange', prog.emplid, prog.stdnt_car_nbr, prog.adm_appl_nbr, prog.acad_prog, prog.prog_status, prog.prog_action, prog.prog_reason,
prog.effdt, prog.effseq, prog.admit_term, prog.exp_grad_term
FROM ps_adm_appl_prog prog
LEFT JOIN dbcsown.ps_adm_appl_data data
ON prog.emplid=data.emplid AND prog.acad_career=data.acad_career AND prog.stdnt_car_nbr=data.stdnt_car_nbr AND prog.adm_appl_nbr=data.adm_appl_nbr
WHERE prog.acad_career='GRAD' AND prog.acad_prog=%s AND prog.effdt>=%s AND prog.admit_term>=%s
AND ( data.appl_fee_status in ('REC', 'WVD')
OR data.adm_appl_ctr in ('GRAW') )
ORDER BY prog.effdt, prog.effseq
""", (acad_prog, IMPORT_START_DATE, IMPORT_START_SEMESTER))
return list(db)
def committee_members(emplids):
"""
Grad committee members for this person.
I suspect the JOIN is too broad: possibly should be maximizing effdt in ps_stdnt_advr_hist?
"""
db = SIMSConn()
db.execute("""
SELECT 'CommitteeMembership', st.emplid, st.committee_id, st.acad_prog, com.effdt, com.committee_type, mem.emplid, mem.committee_role
FROM
ps_stdnt_advr_hist st
JOIN ps_committee com
ON (com.institution=st.institution AND com.committee_id=st.committee_id AND st.effdt<=com.effdt)
JOIN ps_committee_membr mem
ON (mem.institution=st.institution AND mem.committee_id=st.committee_id AND com.effdt=mem.effdt)
WHERE
st.emplid in %s
ORDER BY com.effdt""",
(emplids,))
return list(db)
def update_program_info(advisor_visit_ids):
visits = AdvisorVisit.objects.filter(id__in=advisor_visit_ids) \
.exclude(student__isnull=True).select_related('student')
emplids = set(v.student.emplid for v in visits)
# find plans and subplans for these students
programs = defaultdict(list)
db = SIMSConn()
db.execute(PLAN_QUERY.substitute({'where': 'prog.emplid IN %s'}), (emplids,))
for emplid, planid, _, _ in db:
programs[emplid].append(planid)
db.execute(SUBPLAN_QUERY.substitute({'where': 'prog.emplid IN %s'}), (emplids,))
for emplid, planid, _, _ in db:
programs[emplid].append(planid)
# add them to the visits we're trying to update
for v in visits:
v.config['sims_programs'] = programs[str(v.student.emplid)]
v.save()
def committee_members(emplids):
"""
Grad committee members for this person.
I suspect the JOIN is too broad: possibly should be maximizing effdt in ps_stdnt_advr_hist?
"""
db = SIMSConn()
db.execute(
"""
SELECT 'CommitteeMembership', st.emplid, st.committee_id, st.acad_prog, com.effdt, com.committee_type, mem.emplid, mem.committee_role
FROM
ps_stdnt_advr_hist st
JOIN ps_committee com
ON (com.institution=st.institution AND com.committee_id=st.committee_id AND st.effdt<=com.effdt)
JOIN ps_committee_membr mem
ON (mem.institution=st.institution AND mem.committee_id=st.committee_id AND com.effdt=mem.effdt)
WHERE
st.emplid in %s
ORDER BY com.effdt""", (emplids, ))
return list(db)
def grad_appl_program_changes(acad_prog):
"""
ps_adm_appl_data records where the fee has actually been paid: we don't bother looking at them until then.
Rows become ApplProgramChange objects.
Many of these will duplicate ps_acad_prog: the ProgramStatusChange is smart enough to identify them.
"""
db = SIMSConn()
db.execute(
"""
SELECT 'ApplProgramChange', prog.emplid, prog.stdnt_car_nbr, prog.adm_appl_nbr, prog.acad_prog, prog.prog_status, prog.prog_action, prog.prog_reason,
prog.effdt, prog.effseq, prog.admit_term, prog.exp_grad_term
FROM ps_adm_appl_prog prog
LEFT JOIN dbcsown.ps_adm_appl_data data
ON prog.emplid=data.emplid AND prog.acad_career=data.acad_career AND prog.stdnt_car_nbr=data.stdnt_car_nbr AND prog.adm_appl_nbr=data.adm_appl_nbr
WHERE prog.acad_career='GRAD' AND prog.acad_prog=%s AND prog.effdt>=%s AND prog.admit_term>=%s
AND ( data.appl_fee_status in ('REC', 'WVD')
OR data.adm_appl_ctr in ('GRAW') )
ORDER BY prog.effdt, prog.effseq
""", (acad_prog, IMPORT_START_DATE, IMPORT_START_SEMESTER))
return list(db)
def import_offerings(extra_where='1=1', import_semesters=import_semesters, cancel_missing=False, create_units=False):
db = SIMSConn()
db.execute(CLASS_TBL_QUERY + " AND ct.strm IN %s "
" AND ("+extra_where+")", (import_semesters(),))
imported_offerings = set()
for row in db.rows():
o = import_offering(*row, create_units=create_units)
if o:
imported_offerings.add(o)
if cancel_missing:
# mark any offerings not found during the import as cancelled: handles sections that just disappear from
# ps_class_tbl, because that can happen, apparently.
all_off = CourseOffering.objects.filter(semester__name__in=import_semesters()) \
.exclude(component='CAN').exclude(flags=CourseOffering.flags.combined)
all_off = set(all_off)
for o in all_off - imported_offerings:
o.component = 'CAN'
o.save()
return imported_offerings
def research_translation_tables():
"""
Translation tables of SIMS values to english. Fetched once into a dict to save joining many things later.
"""
db = SIMSConn()
db.execute(
"""
SELECT acad_org, sfu_ga_res_area, descr50
FROM ps_sfu_ga_resareas areas
WHERE areas.eff_status='A'
AND areas.effdt = (SELECT max(effdt) FROM ps_sfu_ga_resareas tmp
WHERE areas.acad_org=tmp.acad_org AND areas.sfu_ga_res_area=tmp.sfu_ga_res_area)""",
())
areas = dict(((acad_org, area), descr) for acad_org, area, descr in db)
db.execute(
"""
SELECT acad_org, sfu_ga_res_area, sfu_ga_reschoices, descr50
FROM ps_sfu_ga_reschoic choices
WHERE choices.effdt = (SELECT max(effdt) FROM ps_sfu_ga_reschoic tmp
WHERE choices.acad_org=tmp.acad_org AND choices.sfu_ga_res_area=tmp.sfu_ga_res_area
AND choices.sfu_ga_reschoices=tmp.sfu_ga_reschoices)""", ())
choices = dict(((acad_org, area, choice), descr)
for acad_org, area, choice, descr in db)
return areas, choices
def import_students(offering):
Member.objects.filter(added_reason="AUTO", offering=offering, role="STUD").update(role='DROP')
db = SIMSConn()
# find any lab/tutorial sections
# c1 original lecture section
# c2 related lab/tutorial section
# s students in c2
# WHERE lines: (1) match lab/tut sections of c1 class (2) students in those
# lab/tut sections (3) with c1 matching offering
query = "SELECT s.emplid, c2.class_section " \
"FROM ps_class_tbl c1, ps_class_tbl c2, ps_stdnt_enrl s " \
"WHERE c1.subject=c2.subject and c1.catalog_nbr=c2.catalog_nbr and c2.strm=c1.strm " \
"and s.class_nbr=c2.class_nbr and s.strm=c2.strm and s.enrl_status_reason IN ('ENRL','EWAT') " \
"and c1.class_nbr=%s and c1.strm=%s and c2.class_section LIKE %s"
db.execute(query, (offering.class_nbr, offering.semester.name, offering.section[0:2]+"%"))
labtut = {}
for emplid, section in db:
if section == offering.section:
# not interested in lecture section now.
continue
labtut[emplid] = section
db.execute("SELECT e.emplid, e.acad_career, e.unt_taken, e.crse_grade_off, r.crse_grade_input "
"FROM ps_stdnt_enrl e LEFT JOIN ps_grade_roster r "
"ON e.strm=r.strm and e.acad_career=r.acad_career and e.emplid=r.emplid and e.class_nbr=r.class_nbr "
"WHERE e.class_nbr=%s and e.strm=%s and e.stdnt_enrl_status='E' and "
"e.enrl_status_reason IN ('ENRL','EWAT')", (offering.class_nbr, offering.semester.name))
for emplid, acad_career, unt_taken, grade_official, grade_roster in db.rows():
p = get_person(emplid)
sec = labtut.get(emplid, None)
grade = grade_official or grade_roster
ensure_member(p, offering, "STUD", unt_taken, "AUTO", acad_career, labtut_section=sec, grade=grade)
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 research_translation_tables():
"""
Translation tables of SIMS values to english. Fetched once into a dict to save joining many things later.
"""
db = SIMSConn()
db.execute("""
SELECT acad_org, sfu_ga_res_area, descr50
FROM ps_sfu_ga_resareas areas
WHERE areas.eff_status='A'
AND areas.effdt = (SELECT max(effdt) FROM ps_sfu_ga_resareas tmp
WHERE areas.acad_org=tmp.acad_org AND areas.sfu_ga_res_area=tmp.sfu_ga_res_area)""", ())
areas = dict(((acad_org, area), descr) for acad_org, area, descr in db)
db.execute("""
SELECT acad_org, sfu_ga_res_area, sfu_ga_reschoices, descr50
FROM ps_sfu_ga_reschoic choices
WHERE choices.effdt = (SELECT max(effdt) FROM ps_sfu_ga_reschoic tmp
WHERE choices.acad_org=tmp.acad_org AND choices.sfu_ga_res_area=tmp.sfu_ga_res_area
AND choices.sfu_ga_reschoices=tmp.sfu_ga_reschoices)""", ())
choices = dict(((acad_org, area, choice), descr) for acad_org, area, choice, descr in db)
return areas, choices
def check_sims_task() -> Optional[str]:
"""
Check SIMS queries for sanity, when run in a Celery task. Returns None if successful, or an error message.
"""
try:
db = SIMSConn()
db.execute("SELECT last_name FROM ps_names WHERE emplid=301355288", ())
result = list(db)
# whoever this is, they have non-ASCII in their name: let's hope they don't change it.
lname = result[0][0]
if not isinstance(lname, str):
return 'string result not a string: check Unicode decoding'
elif lname[1] != u'\u00e4':
return 'returned incorrectly-decoded Unicode'
elif len(result) == 0:
return 'query inexplicably returned nothing'
else:
return None
except SIMSProblem as e:
return 'SIMSProblem, %s' % (str(e),)
except ImportError:
return "couldn't import DB2 module"
except Exception as e:
return 'Generic exception, %s' % (str(e))
def grad_appl_program_changes(acad_prog):
"""
ps_adm_appl_data records where the fee has actually been paid: we don't bother looking at them until then.
Rows become ApplProgramChange objects.
Many of these will duplicate ps_acad_prog: the ProgramStatusChange is smart enough to identify them.
The 13th null argument has been added because ApplProgramChange subclasses ProgramStatusChange, which now requires
an extra degr_chkout_stat argument to find the grad application/approved statuses.
"""
db = SIMSConn()
db.execute("""
SELECT 'ApplProgramChange', prog.emplid, prog.stdnt_car_nbr, prog.adm_appl_nbr, prog.acad_prog, prog.prog_status, prog.prog_action, prog.prog_reason,
prog.effdt, prog.effseq, prog.admit_term, prog.exp_grad_term, null, plan.ACAD_SUB_PLAN
FROM ps_adm_appl_prog prog
LEFT JOIN ps_acad_subplan plan ON prog.emplid=plan.emplid AND prog.EFFDT=plan.effdt
LEFT JOIN dbcsown.ps_adm_appl_data data
ON prog.emplid=data.emplid AND prog.acad_career=data.acad_career AND prog.stdnt_car_nbr=data.stdnt_car_nbr AND prog.adm_appl_nbr=data.adm_appl_nbr
WHERE prog.acad_career='GRAD' AND prog.acad_prog=%s AND prog.effdt>=%s AND prog.admit_term>=%s
AND ( data.appl_fee_status in ('REC', 'WVD')
OR data.adm_appl_ctr in ('GRAW') )
ORDER BY prog.effdt, prog.effseq
""", (acad_prog, IMPORT_START_DATE, IMPORT_START_SEMESTER))
return list(db)
def main():
load_cache()
db = SIMSConn()
out = csv.writer(open("course_majors.csv", 'wb'))
out.writerow([
'Semester', 'Subject', 'Number', 'Section', 'Campus', 'Enrol',
'Programs'
])
programs_seen = set()
for strm in [1111, 1114, 1117, 1121, 1124, 1127, 1131, 1134, 1137]:
progs = semester_students(out, strm)
programs_seen |= progs
plans_desc(programs_seen)
dump_cache()
return
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 metadata_translation_tables():
"""
Translation tables of SIMS values to english. Fetched once into a dict to save joining many things later.
"""
db = SIMSConn()
db.execute("""
SELECT atbl.accomplishment, atbl.descr
FROM ps_accomp_tbl atbl
WHERE atbl.accomp_category='LNG'""", ())
langs = dict(db)
db.execute("""
SELECT country, descr FROM ps_country_tbl""", ())
countries = dict(db)
db.execute("""
SELECT visa_permit_type, visa_permit_class, descrshort FROM ps_visa_permit_tbl WHERE eff_status='A'""", ())
visas = dict((typ, (cls, desc)) for typ, cls, desc in db)
return langs, countries, visas
def check_sims_connection():
from coredata.queries import SIMSConn, SIMSProblem
db = SIMSConn()
db.execute("SELECT descr FROM dbcsown.PS_TERM_TBL WHERE strm='1111'", ())
if len(list(db)) == 0:
raise SIMSProblem("Didn't get any data back from SIMS query.")
def course_members(class_nbr, strm):
db = SIMSConn()
query = """SELECT s.emplid FROM ps_stdnt_enrl s
WHERE s.class_nbr=%s and s.strm=%s and s.enrl_status_reason IN ('ENRL','EWAT')"""
db.execute(query, (class_nbr, strm))
return [emplid for emplid, in db]
