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)