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)