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 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 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_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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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_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 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 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_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 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 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 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 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(): 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 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 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]