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)