def load_comments(short_question, course_code, lang, fiscal_year, department_code, stars, limit, offset):
"""Return all comments of a given type (e.g. general comments) for a
given course code.
"""
field_name = 'offering_city_{0}'.format(lang)
query = """
SELECT text_answer, course_code, learner_classif, {0}, fiscal_year, quarter, overall_satisfaction, stars, magnitude, nanos
FROM comments
WHERE
short_question = %s
AND
(course_code = %s OR %s = '')
AND
(fiscal_year = %s OR %s = '')
AND
(learner_dept_code = %s OR %s = '')
AND
(stars = %s OR %s = '')
ORDER BY 5 DESC, 6 DESC
LIMIT %s OFFSET %s;
""".format(field_name)
results = query_mysql(query, (short_question, course_code, course_code, fiscal_year,
fiscal_year, department_code, department_code, stars,
stars, int(limit), int(offset)))
# Munge raw data with Pandas
results_processed = _munge_comments(results, lang)
return results_processed
def load_training_locations(lang, department_code):
"""Query names and counts of cities in which a department's learners
took training.
"""
field_name = 'offering_city_{0}'.format(lang)
# GROUP BY city name as well as latitude and longitude in case cities in
# different provinces share same name
# ORDER BY COUNT() DESC so that the largest cities appear and are logged first in func '_combine_overlapping_cities_hashed'
query = """
SELECT {0} AS offering_city, offering_lat, offering_lng, COUNT(reg_id) AS count
FROM lsr_this_year
WHERE
billing_dept_code = %s
AND
reg_status = 'Confirmed'
GROUP BY 1, 2, 3
ORDER BY 4 DESC;
""".format(field_name)
results = query_mysql(query, (department_code, ), dict_=True)
# Cast any values of dtype 'Decimal' to float so can be JSONified
results_processed = [
_dict_decimal_to_float(my_dict) for my_dict in results
]
# Replace 'None' with empty string for consistency
results_processed = [_dict_remove_none(my_dict) for my_dict in results]
# Combine nearby cities to avoid clogging map e.g. Kanata, Vanier -> Ottawa
results_processed = _combine_overlapping_cities_hashed(results_processed)
return results_processed
def _load_mandatory(department_code): """Query list of department's mandatory courses.""" query = """ SELECT course_code FROM mandatory_courses WHERE dept_code = %s; """ results = query_mysql(query, (department_code,)) return results
def load_all_attrs(course_code):
"""Return all tombstone information for a given course code."""
query = """
SELECT *
FROM product_info
WHERE course_code = %s
LIMIT 1;
"""
results = query_mysql(query, (course_code, ), dict_=True)
return results[0]
def load_attr(course_attr, course_code):
"""Return tombstone information for a given course code."""
query = """
SELECT {0}
FROM product_info
WHERE course_code = %s
LIMIT 1;
""".format(course_attr)
results = query_mysql(query, (course_code, ))
# String returned inside a tuple inside a list
if not results or not results[0][0]:
return ''
return results[0][0]
def load_offering_info(date_1, date_2, offering_status, course_code, instructor_name, business_line, clients_only, limit, offset, lang):
"""Return info for all offerings matching user criteria."""
# Add percent signs to var 'instructor_name' for LIKE statement
instructor_name = '{0}{1}{0}'.format('%', instructor_name)
# Add clause to see only client requests
clients_only = "AND (a.client != '')" if clients_only == 'true' else ''
query = """
SELECT a.offering_id, a.course_title_{0} AS course_title, a.course_code, a.instructor_names,
a.confirmed_count, a.cancelled_count, a.waitlisted_count, a.no_show_count, a.business_type,
a.event_description, a.start_date, a.end_date, c.business_line_{0} AS business_line,
a.client AS client_dept_code, b.dept_name_{0} AS client_dept_name, a.offering_status,
a.offering_language, a.offering_region_{0} AS offering_region, a.offering_province_{0} AS offering_province,
a.offering_city_{0} AS offering_city, a.offering_lat, a.offering_lng
FROM offerings AS a
LEFT OUTER JOIN departments AS b
ON a.client = b.dept_code
LEFT OUTER JOIN product_info AS c
ON a.course_code = c.course_code
WHERE
(
(a.start_date BETWEEN %s AND %s)
OR
(a.end_date BETWEEN %s AND %s)
OR
(a.start_date <= %s AND a.end_date >= %s)
)
AND a.offering_status IN (%s, %s, %s)
AND (a.course_code = %s OR %s = '')
AND (a.instructor_names LIKE %s OR %s = '%%')
AND (c.business_line_{0} = %s OR %s = '')
{1}
ORDER BY 20 ASC, 3 ASC
LIMIT %s OFFSET %s;
""".format(lang, clients_only)
results = query_mysql(query, (date_1, date_2, date_1, date_2, date_1, date_2, offering_status[0], offering_status[1],
offering_status[2], course_code, course_code, instructor_name,
instructor_name, business_line, business_line, int(limit), int(offset)), dict_=True)
# Cast any values of dtype 'Decimal' to float so can be JSONified
results_processed = [_dict_decimal_to_float(my_dict) for my_dict in results]
# Replace 'None' with empty string for consistency
results_processed = [_dict_remove_none(my_dict) for my_dict in results]
# Fix date formats = use standard ISO format
results_processed = [_dict_fix_dates(my_dict) for my_dict in results]
# Add background colours
# Processing Python-side for consistency as dates as implementation-specific in JS
results_processed = [_dict_assign_background_color(my_dict) for my_dict in results]
# Fix French edge cases
results_processed = [_dict_fix_lang(my_dict, lang) for my_dict in results]
return results_processed
def load_course_codes(short_question, fiscal_year, department_code): """Return list of course codes that match criteria.""" query = """ SELECT DISTINCT course_code FROM comments WHERE short_question = %s AND (fiscal_year = %s OR %s = '') AND (learner_dept_code = %s OR %s = '') ORDER BY 1 ASC; """ results = query_mysql(query, (short_question, fiscal_year, fiscal_year, department_code, department_code)) results_processed = _unpack_tuples(results) return results_processed
def load_classifications():
"""Return all classifications inputted by learners."""
query = """
SELECT a.learner_classif
FROM (
SELECT DISTINCT learner_classif
FROM lsr_this_year
UNION
SELECT DISTINCT learner_classif
FROM lsr_last_year
) AS a;
"""
results = query_mysql(query)
results_processed = _unpack_tuples(results)
results_processed = _remove_duplicates_and_sort(results_processed)
return results_processed
def load_cities(lang):
"""Return all cities inputted by learners."""
query = """
SELECT a.learner_city_{0} AS learner_city, a.learner_province_{0} AS learner_province
FROM (
SELECT DISTINCT learner_city_{0}, learner_province_{0}
FROM lsr_this_year
UNION
SELECT DISTINCT learner_city_{0}, learner_province_{0}
FROM lsr_last_year
) AS a;
""".format(lang)
results = query_mysql(query)
results_processed = _concatenate_city_and_province(results)
results_processed = _clean_cities(results_processed)
results_processed = _remove_duplicates_and_sort(results_processed)
return results_processed
def load_departments(lang):
"""Return all departments inputted by learners."""
query = """
SELECT a.billing_dept_name_{0}
FROM (
SELECT DISTINCT billing_dept_name_{0}
FROM lsr_this_year
UNION
SELECT DISTINCT billing_dept_name_{0}
FROM lsr_last_year
) AS a;
""".format(lang)
results = query_mysql(query)
results_processed = _unpack_tuples(results)
results_processed = _clean_departments(results_processed)
results_processed = _remove_duplicates_and_sort(results_processed)
return results_processed
def load_department_codes(lang):
"""Query list of all department codes and their names as seen
in the LSR.
"""
field_name = 'dept_name_{0}'.format(lang)
query = """
SELECT dept_code AS department_code, {0} AS department_name
FROM departments;
""".format(field_name)
results = query_mysql(query, dict_=True)
results_processed = [
_dict_clean_departments(my_dict) for my_dict in results
if my_dict['department_code'] not in fields.JUNK_DEPT_CODES
]
# Sort list now that certain departments have been renamed
results_processed = sorted(results_processed,
key=lambda my_dict: my_dict['department_name'])
return results_processed
def load_offering_counts(date_1, date_2, offering_status, course_code, instructor_name, business_line, clients_only, lang):
"""Return counts by city for all offerings matching user criteria."""
# Add percent signs to var 'instructor_name' for LIKE statement
instructor_name = '{0}{1}{0}'.format('%', instructor_name)
# Add clause to see only client requests
clients_only = "AND (a.client != '')" if clients_only == 'true' else ''
# GROUP BY city name as well as latitude and longitude in case cities in
# different provinces share same name
# ORDER BY COUNT() DESC so that the largest cities appear and are logged first in func '_combine_overlapping_cities_hashed'
query = """
SELECT a.offering_city_{0} AS offering_city, a.offering_lat, a.offering_lng, COUNT(a.offering_id) AS count
FROM offerings AS a
LEFT OUTER JOIN product_info AS c
ON a.course_code = c.course_code
WHERE
(
(a.start_date BETWEEN %s AND %s)
OR
(a.end_date BETWEEN %s AND %s)
OR
(a.start_date <= %s AND a.end_date >= %s)
)
AND a.offering_status IN (%s, %s, %s)
AND (a.course_code = %s OR %s = '')
AND (a.instructor_names LIKE %s OR %s = '%%')
AND (c.business_line_{0} = %s OR %s = '')
{1}
GROUP BY 1, 2, 3
ORDER BY 4 DESC;
""".format(lang, clients_only)
results = query_mysql(query, (date_1, date_2, date_1, date_2, date_1, date_2, offering_status[0], offering_status[1],
offering_status[2], course_code, course_code, instructor_name,
instructor_name, business_line, business_line), dict_=True)
# Cast any values of dtype 'Decimal' to float so can be JSONified
results_processed = [_dict_decimal_to_float(my_dict) for my_dict in results]
# Replace 'None' with empty string for consistency
results_processed = [_dict_remove_none(my_dict) for my_dict in results]
# Combine nearby cities to avoid clogging map e.g. Kanata, Vanier -> Ottawa
results_processed = _combine_overlapping_cities_hashed(results_processed)
return results_processed
def load_course_codes(lang):
"""Query list of all course codes and their titles as seen
in the LSR.
"""
field_name = 'course_title_{0}'.format(lang)
query = """
SELECT a.course_code, a.{0} AS course_title
FROM (
SELECT DISTINCT course_code, {0}
FROM lsr_last_year
UNION
SELECT DISTINCT course_code, {0}
FROM lsr_this_year
) AS a
ORDER BY 1 ASC;
""".format(field_name)
results = query_mysql(query, dict_=True)
results_processed = [
_dict_clean_course_title(my_dict) for my_dict in results
]
return results_processed
def load_counts(short_question, course_code, fiscal_year, department_code):
"""Return number of comments by star for a given short question, course code,
and fiscal year.
"""
query = """
SELECT stars, COUNT(survey_id)
FROM comments
WHERE
short_question = %s
AND
(course_code = %s OR %s = '')
AND
(fiscal_year = %s OR %s = '')
AND
(learner_dept_code = %s OR %s = '')
GROUP BY 1;
"""
results = query_mysql(query, (short_question, course_code, course_code, fiscal_year, fiscal_year, department_code, department_code))
results = dict(results)
# Ensure all stars from 1-5 present in dict
stars = range(1, 6)
results_processed = {star: results.get(star, 0) for star in stars}
return results_processed
