def general_tab(course_code):
"""Query raw data used for the General tab."""
query = """
SELECT course_code, course_description_en, course_description_fr,
business_type_en, business_type_fr, provider_en, provider_fr,
displayed_on_gccampus_en, displayed_on_gccampus_fr, duration,
main_topic_en, main_topic_fr, business_line_en, business_line_fr,
required_training_en, required_training_fr, communities_en, communities_fr,
point_of_contact, director, program_manager, project_lead
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, course_code)
return file
def browse_tab(tab_name):
"""Query contents of the 'product_info' table."""
query = """
SELECT course_code, course_description_en, course_description_fr,
business_type_en, business_type_fr, provider_en, provider_fr,
displayed_on_gccampus_en, displayed_on_gccampus_fr, duration,
main_topic_en, main_topic_fr, business_line_en, business_line_fr,
required_training_en, required_training_fr, communities_en, communities_fr,
point_of_contact, director, program_manager, project_lead
FROM product_info
ORDER BY 1 ASC;
"""
# Run query
results = query_mysql(query, dict_=True)
results_processed = _dicts_to_lists(results)
# Create file
file = _create_file(results_processed, tab_name)
return file
def _offering_status_counts(self):
"""Query number of offerings by status for a given fiscal year."""
query = """
SELECT offering_status, COUNT(offering_id)
FROM offerings
WHERE course_code = %s AND fiscal_year = %s
GROUP BY 1;
"""
results = query_mysql(query, (self.course_code, self.fiscal_year))
# 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 _calc_regs_and_no_shows_per_month(self):
"""Query number of confirmed regisrations and no-shows per
month; include months that have 0 of both.
"""
field_name = 'month_{0}'.format(self.lang)
table_name = 'lsr_{0}'.format(self.fiscal_year)
# Cast SUM to dtype unsigned to prevent MySQL Python connector from
# returning dtype decimal
query = """
SELECT
{0},
COUNT(CASE WHEN (reg_status = 'Confirmed') THEN reg_id END),
CAST(SUM(no_show) AS UNSIGNED)
FROM {1}
WHERE course_code = %s
GROUP BY 1;
""".format(field_name, table_name)
results = query_mysql(query, (self.course_code,))
results = {tup[0]: (tup[1], tup[2]) for tup in results}
# Process results into format required by Highcharts
results_processed_regs = []
results_processed_no_shows = []
# 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:
counts = results.get(month, (0, 0))
results_processed_regs.append([month, counts[0]])
results_processed_no_shows.append([month, counts[1]])
self.regs_per_month = results_processed_regs
self.no_shows_per_month = results_processed_no_shows
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 _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_categorical(self):
"""Query the DB and extract all categorical question data for a given course code."""
field_name = 'text_answer_{0}'.format(self.lang)
query = """
SELECT original_question, {0}, COUNT({0})
FROM ratings
WHERE
course_code = %s
AND
original_question IN (
'12. Expectations Met', '13. Recommend learning Activity', '14. GCCampus Usage',
'15. Videos', '16. Blogs', '17. Forums', '18. Job aids'
)
GROUP BY 1, 2
ORDER BY 1 ASC;
""".format(field_name)
results = query_mysql(query, (self.course_code,))
results = pd.DataFrame(results, columns=['original_question', 'text_answer', 'count'])
# Return False if course has received no feedback
return False if results.empty else results
def offerings_per_region_and_quarter(lang, fiscal_year, course_code):
field_name = 'offering_region_{0}'.format(lang)
query = """
SELECT {0}, quarter, COUNT(offering_id)
FROM offerings
WHERE
course_code = %s
AND
fiscal_year = %s
AND
offering_status IN ('Open - Normal', 'Delivered - Normal')
GROUP BY 1, 2;
""".format(field_name)
results = query_mysql(query, (course_code, fiscal_year))
# Process into nested dictionaries of format {'Atlantic': {'Q1': 2}, ...}
results_processed = { tup[0]: {} for tup in results }
for tup in results:
results_processed[tup[0]][tup[1]] = tup[2]
return 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 _load_raw(self):
"""Query the DB and extract all overall satisfaction data for a
given course code.
"""
original_question = 'Overall Satisfaction' if self.old_survey else '1. Satisfaction Overall'
query = """
SELECT month_en, AVG(numerical_answer), COUNT(survey_id)
FROM ratings
WHERE
course_code = %s
AND
fiscal_year = %s
AND
original_question = '{0}'
GROUP BY 1;
""".format(original_question)
results = query_mysql(query, (self.course_code, self.fiscal_year))
results = pd.DataFrame(results, columns=['month', 'average', 'count'])
# Return False if course has received no feedback
return False if results.empty else results
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)
field_name_3 = 'offering_city_{0}'.format(self.lang)
query = """
SELECT {0}, {1}, {2}, COUNT(offering_id)
FROM offerings
WHERE
course_code = %s
AND
offering_status IN ('Open - Normal', 'Delivered - Normal')
AND
fiscal_year = %s
GROUP BY 1, 2, 3;
""".format(field_name_1, field_name_2, field_name_3)
results = query_mysql(query, (self.course_code, self.fiscal_year))
results = pd.DataFrame(results, columns=['offering_region', 'offering_province', 'offering_city', 'count'])
self.data = results
def offerings_per_lang(fiscal_year, course_code):
query = """
SELECT offering_language, COUNT(offering_id)
FROM offerings
WHERE
course_code = %s
AND
offering_status IN ('Open - Normal', 'Delivered - Normal')
AND
fiscal_year = %s
GROUP BY 1;
"""
results = query_mysql(query, (course_code, fiscal_year))
# 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 dashboard_tab(course_code):
"""Query raw data used for the Dashboard and Maps tabs."""
table_name = 'lsr_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 validate_course_code(args):
"""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
query = """
SELECT
EXISTS (
SELECT course_code
FROM lsr_last_year
WHERE course_code = %s
LIMIT 1
)
OR
EXISTS (
SELECT course_code
FROM lsr_this_year
WHERE course_code = %s
LIMIT 1
);
"""
course_check = query_mysql(query, (course_code, course_code))
course_check = as_string(course_check)
return course_code if course_check else False
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 offerings_cancelled(fiscal_year, course_code): query = """ SELECT SUM(a.Mars / b.Mars) FROM ( SELECT COUNT(offering_id) AS Mars FROM offerings WHERE course_code = %s AND offering_status = 'Cancelled - Normal' AND fiscal_year = %s ) AS a, ( SELECT COUNT(offering_id) AS Mars FROM offerings WHERE course_code = %s AND fiscal_year = %s ) AS b; """ results = query_mysql(query, (course_code, fiscal_year, course_code, fiscal_year)) return as_percent(results)
def offerings_cancelled_global(fiscal_year): query = """ SELECT SUM(a.Mars / b.Mars) FROM ( SELECT COUNT(offering_id) AS Mars FROM offerings WHERE business_type = 'Instructor-Led' AND offering_status = 'Cancelled - Normal' AND fiscal_year = %s ) AS a, ( SELECT COUNT(offering_id) AS Mars FROM offerings WHERE business_type = 'Instructor-Led' AND fiscal_year = %s ) AS b; """ results = query_mysql(query, (fiscal_year, fiscal_year)) return as_percent(results)