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, ))
query_regs = """
SELECT COUNT(reg_id)
FROM {0}
WHERE course_code = %s AND reg_status = 'Confirmed';
""".format(table_name)
regs = query_mysql(query_regs, (self.course_code, ))
query_no_shows = """
SELECT SUM(no_show)
FROM {0}
WHERE course_code = %s;
""".format(table_name)
no_shows = query_mysql(query_no_shows, (self.course_code, ))
results = [(gettext('Client Requests'), as_int(client_reqs)),
(gettext('Registrations'), as_int(regs)),
(gettext('No-Shows'), as_int(no_shows))]
self.counts.extend(results)
def _calc_regs_per_month(self):
"""Query number of regisrations per month; include months
that have 0 registrations.
"""
field_name = 'month_{0}'.format(self.lang)
table_name = 'lsr{0}'.format(self.fiscal_year)
query = """
SELECT {0}, COUNT(reg_id)
FROM {1}
WHERE course_code = %s AND reg_status = 'Confirmed'
GROUP BY {0};
""".format(field_name, table_name)
results = query_mysql(query, (self.course_code, ))
results = dict(results)
# Process results into format required by Highcharts
results_processed = []
# Ensure every month is returned, even if count 0
months = [
gettext('April'),
gettext('May'),
gettext('June'),
gettext('July'),
gettext('August'),
gettext('September'),
gettext('October'),
gettext('November'),
gettext('December'),
gettext('January'),
gettext('February'),
gettext('March')
]
for month in months:
count = results.get(month, 0)
results_processed.append({'name': month, 'y': count})
self.regs_per_month = results_processed
def _calc_top_classifs(self):
"""Query the top classifications by number of registrations."""
table_name = 'lsr{0}'.format(self.fiscal_year)
query = """
SELECT learner_classif, COUNT(learner_classif)
FROM {0}
WHERE course_code = %s AND reg_status = 'Confirmed'
GROUP BY learner_classif
ORDER BY 2 DESC
LIMIT 5;
""".format(table_name)
results = query_mysql(query, (self.course_code, ))
self.top_classifs = results
def general_tab(course_code): """Query raw data used for the General tab.""" query = """ SELECT * FROM product_info WHERE course_code = %s; """ # Run query results = query_mysql(query, (course_code,), dict_=True) results_processed = _dicts_to_lists(results) # Create file file = _create_file(results_processed) return file
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 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 _calc_top_depts(self):
"""Query the top departments by number of registrations."""
field_name = 'billing_dept_name_{0}'.format(self.lang)
table_name = 'lsr{0}'.format(self.fiscal_year)
query = """
SELECT {0}, COUNT({0})
FROM {1}
WHERE course_code = %s AND reg_status = 'Confirmed'
GROUP BY {0}
ORDER BY 2 DESC
LIMIT 5;
""".format(field_name, table_name)
results = query_mysql(query, (self.course_code, ))
self.top_depts = results
def offerings_cancelled_global(fiscal_year):
table_name = 'lsr{0}'.format(fiscal_year)
query = """
SELECT SUM(a.Mars / b.Mars)
FROM
(SELECT COUNT(DISTINCT offering_id) AS Mars
FROM {0}
WHERE business_type = 'Instructor-Led' AND offering_status = 'Cancelled - Normal') AS a,
(SELECT COUNT(DISTINCT offering_id) AS Mars
FROM {0}
WHERE business_type = 'Instructor-Led') AS b;
""".format(table_name)
results = query_mysql(query)
return as_percent(results)
def avg_no_shows_global(fiscal_year):
table_name = 'lsr{0}'.format(fiscal_year)
query = """
SELECT SUM(a.Mars / b.Mars)
FROM
(SELECT SUM(no_show) AS Mars
FROM {0}
WHERE no_show = 1) AS a,
(SELECT COUNT(DISTINCT offering_id) AS Mars
FROM {0}
WHERE business_type = 'Instructor-Led' AND offering_status IN ('Open - Normal', 'Delivered - Normal')) AS b;
""".format(table_name)
results = query_mysql(query)
return as_float(results)
def avg_no_shows(fiscal_year, course_code):
table_name = 'lsr{0}'.format(fiscal_year)
query = """
SELECT SUM(a.Mars / b.Mars)
FROM
(SELECT SUM(no_show) AS Mars
FROM {0}
WHERE course_code = %s) AS a,
(SELECT COUNT(DISTINCT offering_id) AS Mars
FROM {0}
WHERE course_code = %s AND offering_status IN ('Open - Normal', 'Delivered - Normal')) AS b;
""".format(table_name)
results = query_mysql(query, (course_code, course_code))
return as_float(results)
def offerings_cancelled(fiscal_year, course_code):
table_name = 'lsr{0}'.format(fiscal_year)
query = """
SELECT SUM(a.Mars / b.Mars)
FROM
(SELECT COUNT(DISTINCT offering_id) AS Mars
FROM {0}
WHERE course_code = %s AND offering_status = 'Cancelled - Normal') AS a,
(SELECT COUNT(DISTINCT offering_id) AS Mars
FROM {0}
WHERE course_code = %s) AS b;
""".format(table_name)
results = query_mysql(query, (course_code, course_code))
return as_percent(results)
def _get_course_tile(self):
"""Get course title. In this module as should come from
the lsr_fiscal_year table rather than the product_info
table in case the course has registrations but has yet
to be catalogued by CM."""
field_name = 'course_title_{0}'.format(self.lang)
table_name = 'lsr{0}'.format(self.fiscal_year)
query = """
SELECT {0}
FROM {1}
WHERE course_code = %s
LIMIT 1;
""".format(field_name, table_name)
results = query_mysql(query, (self.course_code, ))
results = as_string(results)
self.course_title = results
def validate_course_code(args, fiscal_year):
"""Check if course code exists in LSR."""
course_code = str(args.get('course_code', False)).upper()
# Check if found in DB; automatically escaped in MySQL via %s
table_name = 'lsr{0}'.format(fiscal_year)
query = """
SELECT EXISTS (
SELECT course_code
FROM {0}
WHERE course_code = %s
LIMIT 1
);
""".format(table_name)
course_check = query_mysql(query, (course_code,))
course_check = as_string(course_check)
return course_code if course_check else False
def _load_all_categorical(self):
"""Query the DB and extract all categorical question data for a given course code."""
field_name = 'text_answer_fr' if self.lang == 'fr' else 'text_answer'
query = """
SELECT short_question, {0}, COUNT({0})
FROM comments
WHERE
course_code = %s
AND
short_question IN ('Reason to Participate', 'Technical Issues', 'OL Available', 'GCcampus Tools Used', 'Prep')
GROUP BY short_question, {0}
ORDER BY 1 ASC;
""".format(field_name)
results = query_mysql(query, (self.course_code, ))
results = pd.DataFrame(
results, columns=['short_question', 'text_answer', 'count'])
# Return False if course has received no feedback
self.categorical_data = False if results.empty else results
def _load_courses(self):
"""Query the DB and store results in DataFrame."""
# Get course codes from LSR to ensure course has usage and will
# therefore have an entry in the catalogue i.e. no dead links
query = """
SELECT DISTINCT b.provider_{0}, b.business_line_{0}, a.course_code, a.course_title_{0}
FROM lsr{1} AS a
LEFT OUTER JOIN product_info AS b
ON a.course_code = b.course_code
ORDER BY BINARY 1, 2, 3 ASC;
""".format(self.lang, self.fiscal_year)
results = query_mysql(query)
results = pd.DataFrame(results,
columns=[
'provider', 'business_line', 'course_code',
'course_title'
])
self.data = results
def _load_ratings(self):
"""Query the DB and extract all ratings data for a given course code."""
field_name_1 = 'short_question_{0}'.format(self.lang)
field_name_2 = 'long_question_{0}'.format(self.lang)
query = """
SELECT {0}, {1}, month, numerical_answer, count
FROM ratings
WHERE course_code = %s
ORDER BY {0};
""".format(field_name_1, field_name_2)
results = query_mysql(query, (self.course_code, ))
results = pd.DataFrame(results,
columns=[
'short_question', 'long_question', 'month',
'average', 'count'
])
# Return False if course has received no feedback
self.data = False if results.empty else results
def offerings_per_lang(fiscal_year, course_code):
table_name = 'lsr{0}'.format(fiscal_year)
query = """
SELECT offering_language, COUNT(DISTINCT offering_id)
FROM {0}
WHERE course_code = %s AND offering_status IN ('Open - Normal', 'Delivered - Normal')
GROUP BY offering_language;
""".format(table_name)
results = query_mysql(query, (course_code, ))
# Force 'English', 'French', and 'Bilingual' to be returned within dict
results = dict(results)
if 'English' not in results:
results['English'] = 0
if 'French' not in results:
results['French'] = 0
if 'Bilingual' not in results:
results['Bilingual'] = 0
return results
def _offering_status_counts(self):
"""Query number of offerings by status for a given fiscal year."""
table_name = 'lsr{0}'.format(self.fiscal_year)
query = """
SELECT offering_status, COUNT(DISTINCT offering_id)
FROM {0}
WHERE course_code = %s
GROUP BY offering_status;
""".format(table_name)
results = query_mysql(query, (self.course_code, ))
# Ensure all possible statuses returned
results = dict(results)
statuses = {
gettext('Open Offerings'): 'Open - Normal',
gettext('Delivered Offerings'): 'Delivered - Normal',
gettext('Cancelled Offerings'): 'Cancelled - Normal'
}
results_processed = [(key, results.get(val, 0))
for (key, val) in statuses.items()]
self.counts.extend(results_processed)
def _load_all_locations(self):
"""Query the DB and extract all offering location data for a given
course code.
"""
field_name_1 = 'offering_region_{0}'.format(self.lang)
field_name_2 = 'offering_province_{0}'.format(self.lang)
table_name = 'lsr{0}'.format(self.fiscal_year)
query = """
SELECT {0}, {1}, offering_city, COUNT(DISTINCT offering_id)
FROM {2}
WHERE course_code = %s AND offering_status IN ('Open - Normal', 'Delivered - Normal')
GROUP BY {0}, {1}, offering_city;
""".format(field_name_1, field_name_2, table_name)
results = query_mysql(query, (self.course_code, ))
results = pd.DataFrame(results,
columns=[
'offering_region', 'offering_province',
'offering_city', 'count'
])
self.data = results
def _learner_cities(self):
"""Returns a list of cities in which learners are located. Each nested
list holds city name, number of learners, latitude, and longitude.
"""
table_name = 'lsr{0}'.format(self.fiscal_year)
# Sort by count so that when overlapping markers are combined by function
# _combine_overlapping_cities_hashed, it's the city with the largest count into
# which all others all merged
query = """
SELECT learner_city, COUNT(DISTINCT learner_id), learner_lat, learner_lng
FROM {0}
WHERE course_code = %s AND reg_status = 'Confirmed'
GROUP BY learner_city
ORDER BY 2 DESC;
""".format(table_name)
results = query_mysql(query, (self.course_code, ))
# Process into format required by Highcharts
results = [[element for element in tup] for tup in results
if tup[2] is not None]
results = self._combine_overlapping_cities_hashed(results)
self.learners = results
def _offering_cities(self):
"""Returns a list of cities in which offerings took place. Each nested
list holds city name, number of offerings, latitude, and longitude.
"""
table_name = 'lsr{0}'.format(self.fiscal_year)
# Sort by count so that when overlapping markers are combined by function
# _combine_overlapping_cities_hashed, it's the city with the largest count into
# which all others all merged
query = """
SELECT offering_city, COUNT(DISTINCT offering_id), offering_lat, offering_lng
FROM {0}
WHERE course_code = %s AND offering_status IN ('Open - Normal', 'Delivered - Normal')
GROUP BY offering_city
ORDER BY 2 DESC;
""".format(table_name)
results = query_mysql(query, (self.course_code, ))
# Process into format required by Highcharts
results = [[element for element in tup] for tup in results
if tup[2] is not None]
results = self._combine_overlapping_cities_hashed(results)
self.offerings = results
def _load_all_comments(self):
"""Query the DB and extract all comment data for a given course code."""
field_name = 'offering_city_{0}'.format(self.lang)
query = """
SELECT short_question, text_answer, stars, learner_classif, {0}, fiscal_year, quarter
FROM comments
WHERE
course_code = %s
AND
short_question IN ('Comment - General', 'Comment - Technical', 'Comment - OL', 'Comment - Performance')
ORDER BY RAND();
""".format(field_name)
results = query_mysql(query, (self.course_code, ))
results = pd.DataFrame(results,
columns=[
'short_question', 'text_answer', 'stars',
'learner_classif', 'offering_city',
'fiscal_year', 'quarter'
])
# Account for learners who didn't submit stars with their comments
results['stars'].fillna(0, inplace=True)
# Return False if course has received no feedback
self.comment_data = False if results.empty else results
def dashboard_tab(course_code):
"""Query raw data used for the Dashboard and Maps tabs."""
THIS_YEAR = Config.THIS_YEAR
table_name = 'lsr{0}'.format(THIS_YEAR)
# Exclude course_title_en and course_title_fr
# Saves a LOT of time, space for huge courses
query = """
SELECT course_code, business_type, offering_id, month_en,
month_fr, client, offering_status, offering_language,
offering_region_en, offering_region_fr, offering_province_en,
offering_province_fr, offering_city, offering_lat,
offering_lng, learner_province, learner_city, learner_lat,
learner_lng, reg_id, reg_status, no_show, learner_id,
learner_language, learner_classif, billing_dept_name_en,
billing_dept_name_fr
FROM {0}
WHERE course_code = %s;
""".format(table_name)
# Run query
results = query_mysql(query, (course_code,), dict_=True)
results_processed = _dicts_to_lists(results)
# Create file
file = _create_file(results_processed)
return file
def load(self):
"""Query course's info from table 'product_info' and format for
display in General tab.
"""
# Explicitely list field names to avoid anti-pattern 'SELECT *' + to
# future proof if columns change order
if self.lang == 'fr':
fields = """
course_description_fr, business_type_fr, provider_fr,
displayed_on_gccampus_fr, duration, main_topic_fr, business_line_fr,
required_training_fr, communities_fr, point_of_contact, director,
program_manager, project_lead
"""
else:
fields = """
course_description_en, business_type_en, provider_en,
displayed_on_gccampus_en, duration, main_topic_en, business_line_en,
required_training_en, communities_en, point_of_contact, director,
program_manager, project_lead
"""
query = "SELECT {0} FROM product_info WHERE course_code = %s LIMIT 1;".format(
fields)
results = query_mysql(query, (self.course_code, ), dict_=True)
# Account for new courses that have registrations but have yet to be catalogued
# Return empty dict as templates use method dict.pop to handle missing vals
if not results:
self.course_info = {}
return self
# Format keys for displaying on page
results_processed = {
self._clean_key(key): val
for (key, val) in results[0].items()
}
self.course_info = results_processed
# Return self to allow method chaining
return self
def course_form(lang, fiscal_year):
"""Query list of all course codes and their titles as seen
in the LSR. Pass to WTForms to make a dropdown menu."""
field_name = 'course_title_{0}'.format(lang)
table_name = 'lsr{0}'.format(fiscal_year)
query = """
SELECT DISTINCT course_code, {0}
FROM {1}
WHERE course_code != 'A230'
ORDER BY 1 ASC;
""".format(field_name, table_name)
results = query_mysql(query)
# SelectField takes list of tuples (pass_value, display_value)
choices = [(tup[0].upper(), '{0}: {1}'.format(tup[0].upper(),
_clean_title(tup[1])))
for tup in results]
class CourseForm(Form):
# Displaying form_name disabled in 'templates/includes/_formhelpers.html', so pass empty string
form_name = ''
course_code = SelectField(form_name, choices=choices)
return CourseForm
