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 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_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 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_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_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_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