def _offering_additional_counts(self):
"""Additional offering counts used by School analysts."""
table_name = 'lsr_{0}'.format(self.fiscal_year)
query_client_reqs = """
SELECT COUNT(DISTINCT offering_id)
FROM {0}
WHERE course_code = %s AND client != '' AND offering_status IN ('Open - Normal', 'Delivered - Normal');
""".format(table_name)
client_reqs = query_mysql(query_client_reqs, (self.course_code, ))
results = (gettext('Client Requests'), as_int(client_reqs))
self.counts.append(results)
def _calc_total_no_shows(self):
"""Query total number of no-shows."""
table_name = 'lsr_{0}'.format(self.fiscal_year)
query = """
SELECT SUM(no_show)
FROM {0}
WHERE course_code = %s;
""".format(table_name)
results = query_mysql(query, (self.course_code,))
results_processed = (gettext('No-Shows'), as_int(results))
self.counts.append(results_processed)
def _calc_unique_learners(self):
"""Query total number of unique learners."""
table_name = 'lsr_{0}'.format(self.fiscal_year)
query = """
SELECT COUNT(DISTINCT learner_id)
FROM {0}
WHERE course_code = %s AND reg_status = 'Confirmed';
""".format(table_name)
results = query_mysql(query, (self.course_code,))
results_processed = (gettext('Unique Learners'), as_int(results))
self.counts.append(results_processed)
def _calc_total_regs(self):
"""Query total number of confirmed registrations."""
table_name = 'lsr_{0}'.format(self.fiscal_year)
query = """
SELECT COUNT(reg_id)
FROM {0}
WHERE course_code = %s AND reg_status = 'Confirmed';
""".format(table_name)
results = query_mysql(query, (self.course_code,))
results_processed = (gettext('Registrations'), as_int(results))
self.counts.append(results_processed)
def avg_class_size_global(fiscal_year):
table_name = 'lsr_{0}'.format(fiscal_year)
query = """
SELECT AVG(class_size)
FROM(
SELECT COUNT(reg_id) AS class_size
FROM {0}
WHERE reg_status= 'Confirmed' AND business_type = 'Instructor-Led'
GROUP BY offering_id
) AS sub_table;
""".format(table_name)
results = query_mysql(query)
return as_int(results)
def avg_class_size(fiscal_year, course_code):
table_name = 'lsr_{0}'.format(fiscal_year)
query = """
SELECT AVG(class_size)
FROM(
SELECT COUNT(reg_id) AS class_size
FROM {0}
WHERE course_code = %s AND reg_status= 'Confirmed'
GROUP BY offering_id
) AS sub_table;
""".format(table_name)
results = query_mysql(query, (course_code, ))
return as_int(results)
def _offering_additional_counts(self):
"""Additional offering counts used by School analysts."""
query_client_reqs = """
SELECT COUNT(offering_id)
FROM offerings
WHERE
course_code = %s
AND
client != ''
AND
offering_status IN ('Open - Normal', 'Delivered - Normal')
AND
fiscal_year = %s;
"""
client_reqs = query_mysql(query_client_reqs, (self.course_code, self.fiscal_year))
results = (gettext('Client Requests'), as_int(client_reqs))
self.counts.append(results)