def all_ratings(course_code, lang): # Get list of questions answered for given course code field_name_1 = 'short_question_{0}'.format(lang) field_name_2 = 'long_question_{0}'.format(lang) questions_query = """ SELECT DISTINCT {0}, {1} FROM ratings WHERE course_code = %s ORDER BY 1 ASC; """.format(field_name_1, field_name_2) questions = query_mysql(questions_query, (course_code,)) # Account courses with no feedback if not questions: return False # Query each question for monthly results query = """ SELECT month, numerical_answer, count FROM ratings WHERE course_code = %s AND {0} = %s; """.format(field_name_1) # Return a list of dictionaries return_list = [] for question in questions: results = query_mysql(query, (course_code, question[0])) # Convert 'results' from format [(April, numerical_answer, count), ...] to {April: (numerical_answer, count), May: ...} results = [(tup[0], {'y': tup[1], 'count': tup[2]}) for tup in results] results = dict(results) results_processed = _add_months(results) return_list.append((question[0], question[1], results_processed)) return return_list
def all_course_codes(fiscal_year): table_name = 'lsr{}'.format(fiscal_year) query = """ SELECT DISTINCT course_code FROM {0} ORDER BY 1 ASC; """.format(table_name) results = query_mysql(query) results = [tup[0] for tup in results] return results
def top_5_classifs(fiscal_year, course_code): table_name = 'lsr{0}'.format(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) return query_mysql(query, (course_code, ))
def top_5_depts(lang, fiscal_year, course_code): field_name = 'billing_dept_name_{0}'.format(lang) table_name = 'lsr{0}'.format(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) return query_mysql(query, (course_code, ))
def offering_city_counts(fiscal_year, course_code): table_name = 'lsr{0}'.format(fiscal_year) 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; """.format(table_name) results = query_mysql(query, (course_code,)) # Make 'results' a list of lists so can be manipulated by JavaScript results = [[element for element in tup] for tup in results if tup[2] is not None] return results
def learner_city_counts(fiscal_year, course_code): table_name = 'lsr{0}'.format(fiscal_year) query = """ SELECT learner_city, COUNT(DISTINCT offering_id), learner_lat, learner_lng FROM {0} WHERE course_code = %s AND reg_status = 'Confirmed' GROUP BY learner_city; """.format(table_name) results = query_mysql(query, (course_code,)) # Make 'results' a list of lists so can be manipulated by JavaScript results = [[element for element in tup] for tup in results if tup[2] is not None] return results
def fetch_comments(course_code, question): query = """ SELECT text_answer, stars, learner_classif, offering_city, fiscal_year, quarter FROM comments WHERE course_code = %s AND short_question = %s ORDER BY RAND(); """ results = query_mysql(query, (course_code, question)) # Correct city names e.g. NORTH YORK -> North York via str.title() results = [(tup[0], tup[1], tup[2].replace(' - Unknown', ''), tup[3].title().replace('(Ncr)', '(NCR)').replace("'S", "'s"), tup[4].replace('-20', '-'), tup[5]) for tup in results] return results
def course_title_form(lang, form_name): field_name = 'course_title_{0}'.format(lang) table_name = 'lsr{}'.format(Config.THIS_YEAR) query = """ SELECT DISTINCT course_code, {0} FROM {1} ORDER BY 2 ASC; """.format(field_name, table_name) results = query_mysql(query) # SelectField takes list of tuples (pass_value, display_value) choices = [(tup[0], tup[1]) for tup in results] class CourseForm(Form): course_selection = SelectField(form_name, choices=choices) return CourseForm
def fetch_categorical(course_code, question): query = """ SELECT text_answer, COUNT(text_answer) FROM comments WHERE course_code = %s AND short_question = %s GROUP BY text_answer ORDER BY 1 ASC; """ results = query_mysql(query, (course_code, question)) # Process results into format required by Highcharts results_processed = [] for tup in results: results_processed.append({'name': tup[0], 'y': tup[1]}) return results_processed if results_processed else [{ 'name': 'No response', 'y': 1 }]
def regs_per_month(fiscal_year, course_code): table_name = 'lsr{0}'.format(fiscal_year) query = """ SELECT month, COUNT(reg_id) FROM {0} WHERE course_code = %s AND reg_status = 'Confirmed' GROUP BY month; """.format(table_name) results = query_mysql(query, (course_code, )) results = dict(results) # Process results into format required by Highcharts results_processed = [] months = [ 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December', 'January', 'February', 'March' ] for month in months: count = results.get(month, 0) results_processed.append({'name': month, 'y': count}) return results_processed
def _query_product_info(field, lang, course_code): field_name = field query = "SELECT {0} FROM product_info WHERE course_code = %s LIMIT 1;".format(field_name) result = query_mysql(query, (course_code,)) return as_string(result, error_msg='<awaiting mapping>')
def course_description(lang, course_code): field_name = 'course_description' query_description = "SELECT {0} FROM product_info WHERE course_code = %s LIMIT 1;".format(field_name) description = query_mysql(query_description, (course_code,)) return as_string(description, error_msg='Apologies, this course is currently catalogued without a description.')
def online_course(fiscal_year, course_code): table_name = 'lsr{0}'.format(fiscal_year) query = "SELECT business_type FROM {0} WHERE course_code = %s LIMIT 1;".format(table_name) business_type = query_mysql(query, (course_code,)) business_type = as_string(business_type) return True if (business_type == 'Online') else False
def course_title(lang, fiscal_year, course_code): field_name = 'course_title_{0}'.format(lang) table_name = 'lsr{0}'.format(fiscal_year) query = "SELECT {0} FROM {1} WHERE course_code = %s LIMIT 1;".format(field_name, table_name) course_title = query_mysql(query, (course_code,)) return as_string(course_title, error_msg=False)