def mutate(self, info, courses, **kwargs):
owner = Admin.objects.get(user=info.context.user)
business_id = owner.business.id
business = Business.objects.get(id=business_id)
xls = pd.ExcelFile(courses.read())
# check all spreadsheets exist
spreadsheet_names = ["Step 1 - Subject Categories", "Step 2 - Classes"]
if not all(name in xls.sheet_names for name in spreadsheet_names):
raise GraphQLError("Please include all spreadsheets: " +
str(spreadsheet_names))
# extract spreadsheets and skip first comment row
subjects_df = pd.read_excel(xls,
sheet_name="Step 1 - Subject Categories",
header=1)
courses_df = pd.read_excel(xls,
sheet_name="Step 2 - Classes",
header=1)
# check all column headers present
subjects_ws_missing_columns = set(
COURSE_SHEET_NAME_TO_REQUIRED_FIELDS["subjects"]) - set(
subjects_df.columns.values)
if len(subjects_ws_missing_columns) > 0:
raise GraphQLError("Missing columns in subjects worksheet: " +
str(subjects_ws_missing_columns))
courses_ws_missing_columns = set(
COURSE_SHEET_NAME_TO_REQUIRED_FIELDS["courses"]) - set(
courses_df.columns.values)
if len(courses_ws_missing_columns) > 0:
raise GraphQLError("Missing columns in courses workshet: " +
str(courses_ws_missing_columns))
# create subjects
subjects_df = subjects_df.dropna(how="all")
subjects_df = subjects_df.where(pd.notnull(subjects_df),
None) # cast np.Nan to None
subjects_error_df = []
for _index, row in subjects_df.iloc[1:].iterrows():
required_fields_check = check_course_sheet_row(row, "subjects")
if required_fields_check:
subjects_error_df.append(row.to_dict())
subjects_error_df[-1]["Error Message"] = required_fields_check
continue
# include valid subjects in error file for courses that rely on them
subjects_error_df.append(row.to_dict())
subjects_error_df[-1]["Error Message"] = ""
try:
# ignore subjects that already exist
if CourseCategory.objects.filter(
name=row.get("Subjects")).exists():
continue
course_category = CourseCategory(
name=row.get("Subjects"),
description=row.get("Description"))
course_category.save()
except Exception as e:
subjects_error_df[-1]["Error Message"] = str(e)
continue
LogEntry.objects.log_action(
user_id=info.context.user.id,
content_type_id=ContentType.objects.get_for_model(
CourseCategory).pk,
object_id=course_category.id,
object_repr=course_category.name,
action_flag=ADDITION,
)
# create courses
def extract_from_parenthesis(s):
if s:
return s[s.find("(") + 1:s.find(")")]
else:
return s
academic_level_to_enum_str = {
"Elementary": "elementary_lvl",
"Middle School": "middle_lvl",
"High School": "high_lvl",
"College": "college_lvl",
}
courses_df = courses_df.dropna(how="all")
courses_df = courses_df.where(pd.notnull(courses_df),
None) # cast np.Nan to None
courses_error_df = []
dropdown_subject_names = set(subjects_df["Subjects"])
for _index, row in courses_df.iloc[1:].iterrows():
orig_instructor_field = row["Instructor"]
row["Instructor"] = extract_from_parenthesis(row["Instructor"])
required_fields_check = check_course_sheet_row(
row, "courses_minimum", business_id, dropdown_subject_names)
if required_fields_check:
courses_error_df.append(row.to_dict())
courses_error_df[-1]["Instructor"] = orig_instructor_field
courses_error_df[-1]["Error Message"] = required_fields_check
continue
try:
course = Course(
business=business,
title=row.get("Course Name"),
course_category=CourseCategory.objects.get(
name=row.get("Subject")),
description=row.get("Course Description"),
total_tuition=row.get("Total Tuition"),
instructor=Instructor.objects.get(
user__email=row.get("Instructor")),
is_confirmed=row.get("Instructor Confirmed? (Y/N)") == "Y",
academic_level=academic_level_to_enum_str[row.get(
"Academic Level")],
room=row.get("Room Location"),
start_date=row.get("Start Date"),
end_date=row.get("End Date"),
course_type="class",
max_capacity=int(row.get("Enrollment Capacity (>=4)")),
)
course.save()
except Exception as e:
courses_error_df.append(row.to_dict())
courses_error_df[-1]["Instructor"] = orig_instructor_field
courses_error_df[-1]["Error Message"] = str(e)
continue
# parse course availabilities
availabilities = [{
"day_of_week":
row.get(f"Session Day {i+1}").lower(),
"start_time":
row.get(f"Start Time {i+1}"),
"end_time":
row.get(f"End Time {i+1}"),
} for i in range(5) if row.get(f"Session Day {i+1}")]
# populate sessions and availabilities
course_availabilities = create_availabilities_and_sessions(
course, availabilities)
# calculate total hours across all sessions
total_hours = decimal.Decimal("0.0")
for availability in course_availabilities:
duration_sec = (
datetime.combine(date.min, availability.end_time) -
datetime.combine(date.min,
availability.start_time)).seconds
duration_hours = decimal.Decimal(duration_sec) / (60 * 60)
total_hours += duration_hours * availability.num_sessions
course.hourly_tuition = course.total_tuition / total_hours
course.save()
course.refresh_from_db()
LogEntry.objects.log_action(
user_id=info.context.user.id,
content_type_id=ContentType.objects.get_for_model(Course).pk,
object_id=course.id,
object_repr=course.title,
action_flag=ADDITION,
)
total = subjects_df.shape[0] - 1 + courses_df.shape[0] - 1
total_failure = sum(1 for row in subjects_error_df
if row["Error Message"]) + len(courses_error_df)
# construct error excel
error_excel = ""
if total_failure > 0:
wb = create_course_templates(business_id, show_errors=True)
categories_ws = wb.get_sheet_by_name("Step 1 - Subject Categories")
categories_column_order = [cell.value for cell in categories_ws[2]]
for index, row_error in enumerate(subjects_error_df):
for col in range(len(categories_column_order)):
categories_ws.cell(
row=4 + index, column=1 +
col).value = row_error[categories_column_order[col]]
autosize_ws_columns(categories_ws)
course_ws = wb.get_sheet_by_name("Step 2 - Classes")
course_column_order = [cell.value for cell in course_ws[2]]
for index, row_error in enumerate(courses_error_df):
for col in range(len(course_column_order)):
course_ws.cell(
row=4 + index, column=1 +
col).value = row_error[course_column_order[col]]
autosize_ws_columns(course_ws)
error_excel = workbook_to_base64(wb)
return UploadCoursesMutation(
total_success=total - total_failure,
total_failure=total_failure,
error_excel=error_excel,
)
