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