class AcceptedQuery(DB2_Query):
title = "Accepted Students and Genders"
description = "The list of students we accepted in specific plans who start next semester, and their gender."
query = string.Template("""
SELECT pers.emplid, pers.sex, c.descrshort AS citizen
FROM PS_PERSONAL_DATA pers
JOIN ps_citizenship cit ON cit.emplid=pers.emplid
JOIN ps_country_tbl c ON cit.country=c.country
WHERE
pers.emplid IN
(SELECT DISTINCT (plan.EMPLID) from PS_ACAD_PLAN plan where REQ_TERM=$strm AND ACAD_PLAN IN $acad_plans);
""")
plans_list = [
'CMPTMAJ', 'DCMPT', 'CMPTMIN', 'CMPTHON', 'CMPTJMA', 'CMPTJHO',
'SOSYMAJ', 'ZUSFU'
]
default_arguments = {
'strm': current_semester().increment(1),
'acad_plans': plans_list
}
def __init__(self, query_args):
for arg in AcceptedQuery.default_arguments.keys():
if arg not in query_args:
query_args[arg] = AcceptedQuery.default_arguments[arg]
self.title = "Accepted Students and Genders - " + Semester(
query_args["strm"]).long_form()
super(AcceptedQuery, self).__init__(query_args)
class EnrolledQuery(DB2_Query):
title = "Enrolled Students and Genders"
description = "The list of students enrolled in specific programs who start next semester, and their gender."
query = string.Template("""
SELECT pers.emplid, pers.sex, c.descrshort AS citizentest
FROM PS_PERSONAL_DATA pers
JOIN ps_citizenship cit ON cit.emplid=pers.emplid
JOIN ps_country_tbl c ON cit.country=c.country
WHERE pers.EMPLID IN (SELECT
DISTINCT (EMPLID) from PS_ACAD_PROG
WHERE REQ_TERM=$strm AND PROG_STATUS='AC' AND PROG_ACTION='MATR' AND ACAD_PROG in $acad_progs );
""")
progs_list = ['CMPT', 'CMPT2']
default_arguments = {
'strm': str(current_semester().increment(1)),
'acad_progrs': progs_list
}
def __init__(self, query_args):
for arg in EnrolledQuery.default_arguments.keys():
if arg not in query_args:
query_args[arg] = EnrolledQuery.default_arguments[arg]
self.title = "Enrolled Students and Genders - " + Semester(
query_args["strm"]).long_form()
super(EnrolledQuery, self).__init__(query_args)
def run(self):
semester = current_semester()
taking_165_query = CMPT165_after_CMPT_Query({'strm': semester})
taking_165 = taking_165_query.result()
email = EmailQuery().result()
email.filter(EmailQuery.campus_email)
email.remove_column("PREF_EMAIL_FLAG")
email.remove_column("E_ADDR_TYPE")
taking_165.left_join(email, "EMPLID")
names = NameQuery().result()
taking_165.left_join(names, "EMPLID")
self.artifacts.append(taking_165)
class CMPT165_after_CMPT_Query(DB2_Query):
title = "Students taking CMPT 165 after a CMPT >=200 course"
description = "Students taking CMPT 165 after a 200-or-above CMPT course."
query = string.Template("""
SELECT se.emplid, ct.strm, ct.subject, ct.catalog_nbr, ct.class_section, se.crse_grade_off
FROM ps_class_tbl ct
INNER JOIN ps_stdnt_enrl se ON ct.class_nbr=se.class_nbr and ct.strm=se.strm and se.enrl_status_reason IN ('ENRL','EWAT')
WHERE
ct.strm<='$strm' AND ct.subject='CMPT'
AND (ct.catalog_nbr LIKE ' 2%' OR ct.catalog_nbr LIKE ' 3%' OR ct.catalog_nbr LIKE ' 4%' or ct.catalog_nbr LIKE '%125%' or ct.catalog_nbr LIKE '%135%')
AND ct.class_type='E'
AND se.emplid in (SELECT se.emplid FROM ps_class_tbl ct
INNER JOIN ps_stdnt_enrl se ON ct.class_nbr=se.class_nbr and ct.strm=se.strm and se.enrl_status_reason IN ('ENRL','EWAT')
WHERE ct.strm=$strm AND ct.subject='CMPT' AND ct.catalog_nbr LIKE '%165%')
ORDER BY se.emplid, ct.strm, ct.subject, ct.catalog_nbr
""")
default_arguments = {'strm': current_semester()}
def run(self):
semester = current_semester()
# Get the full list of plans in each offering
plans_query = PlansInCoursesQuery({'strm': semester})
plans = plans_query.result()
# create a table with the counts of plans, not individual student info
programs = Table()
programs.append_column('SEMESTER')
programs.append_column('SUBJECT')
programs.append_column('CATALOG_NBR')
programs.append_column('CLASS_SECTION')
programs.append_column('CAMPUS')
programs.append_column('ENRL_TOTAL')
programs.append_column('ENRL_CAP')
programs.append_column('PLANS')
# group plans by offering
offering_plans = (
(offering, (r['ACAD_PLAN'] for r in rows))
for offering, rows in itertools.groupby(plans.row_maps(), _rowkey))
# count for each offering
found_plans = set()
for (subj, nbr, sect, campus, tot, cap), plans in offering_plans:
plans = list(plans)
found_plans |= set(plans)
count = counter(plans)
count = [(n, plan) for plan, n in count.iteritems()]
count.sort()
count.reverse()
count_str = ', '.join("%i*%s" % (n, plan) for n, plan in count)
programs.append_row(("%04i" % (semester), subj, nbr, sect, campus,
tot, cap, count_str))
self.artifacts.append(programs)
# get a cheat-sheet of the plan codes
found_plans = list(found_plans)
found_plans.sort()
descr = PlansDescriptionQuery({'plans': found_plans}).result()
self.artifacts.append(descr)
def run(self):
semester = current_semester()
# Get the full list of plans in each offering
plans_query = PlansInCoursesQuery({'strm': semester})
plans = plans_query.result()
# create a table with the counts of plans, not individual student info
programs = Table()
programs.append_column('SEMESTER')
programs.append_column('SUBJECT')
programs.append_column('CATALOG_NBR')
programs.append_column('CLASS_SECTION')
programs.append_column('CAMPUS')
programs.append_column('ENRL_TOTAL')
programs.append_column('ENRL_CAP')
programs.append_column('PLANS')
# group plans by offering
offering_plans = (
(offering, (r['ACAD_PLAN'] for r in rows))
for offering, rows
in itertools.groupby(plans.row_maps(), _rowkey))
# count for each offering
found_plans = set()
for (subj, nbr, sect, campus, tot, cap), plans in offering_plans:
plans = list(plans)
found_plans |= set(plans)
count = counter(plans)
count = [(n,plan) for plan,n in count.items()]
count.sort()
count.reverse()
count_str = ', '.join("%i*%s" % (n,plan) for n,plan in count)
programs.append_row(("%04i"%(semester), subj, nbr, sect, campus, tot, cap, count_str))
self.artifacts.append(programs)
# get a cheat-sheet of the plan codes
found_plans = list(found_plans)
found_plans.sort()
descr = PlansDescriptionQuery({'plans': found_plans}).result()
self.artifacts.append(descr)
def run(self):
semesters = list(semester_range(Semester(1071), current_semester()))
# Get the full list of plans in each offering
plans_query = PlansInCoursesQuery({'strm': semesters})
plans = plans_query.result()
# create a table with the counts of plans, not individual student info
programs = Table()
programs.append_column('SEMESTER')
programs.append_column('SUBJECT')
programs.append_column('CATALOG_NBR')
programs.append_column('CLASS_SECTION')
programs.append_column('CAMPUS')
programs.append_column('ENRL_TOTAL')
programs.append_column('ENRL_CAP')
programs.append_column('PLANS')
# group plans by offering
offering_plans = (
(offering, (r['ACAD_PLAN'] for r in rows))
for offering, rows
in itertools.groupby(plans.row_maps(), _rowkey))
# count for each offering
found_plans = set()
count = {}
# Build an object to count the instance of each gender in each plan for each semester
for (strm, subj, nbr, sect, campus, tot, cap, sex), plans in offering_plans:
plans = list(plans)
found_plans |= set(plans)
# We want to count too many things, let's store it in an object:
if strm not in count:
count[strm] = {}
current_strm = count.get(strm)
for plan in plans:
if plan not in current_strm:
current_strm[plan] = {}
current_plan = current_strm[plan]
current_plan['TOTAL'] = current_plan.get('TOTAL', 0) + 1
if sex == 'M':
current_plan['M'] = current_plan.get('M', 0) + 1
elif sex == 'F':
current_plan['F'] = current_plan.get('F', 0) +1
# Let's iterate through our counter object and just add one row per semester, concatenating the string with
# the breakdown of males/females in each plan.
for strm in count:
count_str = ''
for plan in count[strm]:
m_count = count[strm].get(plan).get('M', 0)
f_count = count[strm].get(plan).get('F', 0)
total_count = count[strm].get(plan).get('TOTAL', 0)
count_str += str("%s Total: %i, M: %i, F: %i " % (plan, total_count, m_count, f_count))
programs.append_row((strm, subj, nbr, sect, campus, tot, cap, count_str))
self.artifacts.append(programs)
# get a cheat-sheet of the plan codes
found_plans = list(found_plans)
found_plans.sort()
descr = PlansDescriptionQuery({'plans': found_plans}).result()
self.artifacts.append(descr)
def run(self):
AcceptedStudentsQuery = AcceptedQuery({
'strm':
str(current_semester().increment(1)),
'acad_plans': [
'CMPTMAJ', 'DCMPT', 'CMPTMIN', 'CMPTHON', 'CMPTJMA', 'CMPTJHO',
'SOSYMAJ', 'ZUSFU'
]
})
AcceptedStudents = AcceptedStudentsQuery.result()
EnrolledStudentsQuery = EnrolledQuery({
'strm':
str(current_semester().increment(1)),
'acad_progs': ['CMPT', 'CMPT2']
})
EnrolledStudents = EnrolledStudentsQuery.result()
# Let's calculate our totals so we can display those numbers as well.
accepted_list = AcceptedStudents.column_as_list("SEX")
accepted_total = len(accepted_list)
accepted_m_count = len([i for i in accepted_list if i == 'M'])
accepted_f_count = len([i for i in accepted_list if i == 'F'])
accepted_u_count = len([i for i in accepted_list if i == 'U'])
enrolled_list = EnrolledStudents.column_as_list("SEX")
enrolled_total = len(enrolled_list)
enrolled_m_count = len([i for i in enrolled_list if i == 'M'])
enrolled_f_count = len([i for i in enrolled_list if i == 'F'])
enrolled_u_count = len([i for i in enrolled_list if i == 'U'])
# Let's create two new tables to display these results.
accepted_totals = Table()
accepted_totals.append_column('TOTAL_COUNT')
accepted_totals.append_column('M_COUNT')
accepted_totals.append_column('M_PERCENT')
accepted_totals.append_column('F_TOTAL')
accepted_totals.append_column('F_PERCENT')
accepted_totals.append_column('U_COUNT')
accepted_totals.append_column('U_PERCENT')
accepted_totals.append_row([
accepted_total, accepted_m_count,
100.0 * accepted_m_count / accepted_total, accepted_f_count,
100.0 * accepted_f_count / accepted_total, accepted_u_count,
100.0 * accepted_u_count / accepted_total
])
enrolled_totals = Table()
enrolled_totals.append_column('TOTAL_COUNT')
enrolled_totals.append_column('M_COUNT')
enrolled_totals.append_column('M_PERCENT')
enrolled_totals.append_column('F_TOTAL')
enrolled_totals.append_column('F_PERCENT')
enrolled_totals.append_column('U_COUNT')
enrolled_totals.append_column('U_PERCENT')
enrolled_totals.append_row([
enrolled_total, enrolled_m_count,
100.0 * enrolled_m_count / enrolled_total, enrolled_f_count,
100.0 * enrolled_f_count / enrolled_total, enrolled_u_count,
100.0 * enrolled_u_count / enrolled_total
])
self.artifacts.append(AcceptedStudents)
self.artifacts.append(accepted_totals)
self.artifacts.append(EnrolledStudents)
self.artifacts.append(enrolled_totals)