Python queries.SIMSConn类代码示例

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_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 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 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 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 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 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', 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)