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
