def get_users(self, course_id, user_id=None):
"""
Gets the users for a given target.
Result is returned in the form of a queryset, and may contain duplicates.
"""
staff_qset = CourseStaffRole(course_id).users_with_role()
instructor_qset = CourseInstructorRole(course_id).users_with_role()
staff_instructor_qset = (staff_qset | instructor_qset)
enrollment_qset = User.objects.filter(
is_active=True,
courseenrollment__course_id=course_id,
courseenrollment__is_active=True)
if self.target_type == SEND_TO_MYSELF:
if user_id is None:
raise ValueError(
"Must define self user to send email to self.")
user = User.objects.filter(id=user_id)
return use_read_replica_if_available(user)
elif self.target_type == SEND_TO_STAFF:
return use_read_replica_if_available(staff_instructor_qset)
elif self.target_type == SEND_TO_LEARNERS:
return use_read_replica_if_available(
enrollment_qset.exclude(id__in=staff_instructor_qset))
elif self.target_type == SEND_TO_COHORT:
return User.objects.none(
) # TODO: cohorts aren't hooked up, put that logic here
else:
raise ValueError("Unrecognized target type {}".format(
self.target_type))
def get_users(self, course_id, user_id=None):
"""
Gets the users for a given target.
Result is returned in the form of a queryset, and may contain duplicates.
"""
staff_qset = CourseStaffRole(course_id).users_with_role()
instructor_qset = CourseInstructorRole(course_id).users_with_role()
staff_instructor_qset = (staff_qset | instructor_qset)
enrollment_qset = User.objects.filter(
is_active=True,
courseenrollment__course_id=course_id,
courseenrollment__is_active=True
)
if self.target_type == SEND_TO_MYSELF:
if user_id is None:
raise ValueError("Must define self user to send email to self.")
user = User.objects.filter(id=user_id)
return use_read_replica_if_available(user)
elif self.target_type == SEND_TO_STAFF:
return use_read_replica_if_available(staff_instructor_qset)
elif self.target_type == SEND_TO_LEARNERS:
return use_read_replica_if_available(enrollment_qset.exclude(id__in=staff_instructor_qset))
elif self.target_type == SEND_TO_COHORT:
return self.cohorttarget.cohort.users.filter(id__in=enrollment_qset) # pylint: disable=no-member
elif self.target_type == SEND_TO_TRACK:
return use_read_replica_if_available(
enrollment_qset.filter(
courseenrollment__mode=self.coursemodetarget.track.mode_slug
)
)
else:
raise ValueError("Unrecognized target type {}".format(self.target_type))
def get_users(self, course_id, user_id=None):
"""
Gets the users for a given target.
Result is returned in the form of a queryset, and may contain duplicates.
"""
staff_qset = CourseStaffRole(course_id).users_with_role()
instructor_qset = CourseInstructorRole(course_id).users_with_role()
staff_instructor_qset = (staff_qset | instructor_qset)
enrollment_qset = User.objects.filter(
is_active=True,
courseenrollment__course_id=course_id,
courseenrollment__is_active=True
)
if self.target_type == SEND_TO_MYSELF:
if user_id is None:
raise ValueError("Must define self user to send email to self.")
user = User.objects.filter(id=user_id)
return use_read_replica_if_available(user)
elif self.target_type == SEND_TO_STAFF:
return use_read_replica_if_available(staff_instructor_qset)
elif self.target_type == SEND_TO_LEARNERS:
return use_read_replica_if_available(enrollment_qset.exclude(id__in=staff_instructor_qset))
elif self.target_type == SEND_TO_COHORT:
return User.objects.none() # TODO: cohorts aren't hooked up, put that logic here
else:
raise ValueError("Unrecognized target type {}".format(self.target_type))
def _get_recipient_querysets(user_id, to_option, course_id):
"""
Returns a list of query sets of email recipients corresponding to the
requested `to_option` category.
`to_option` is either SEND_TO_MYSELF, SEND_TO_STAFF, or SEND_TO_ALL.
Recipients who are in more than one category (e.g. enrolled in the course
and are staff or self) will be properly deduped.
"""
if to_option.isdigit():
if not GroupedQuery.objects.filter(id=int(to_option)).exists():
message = "Bulk email TO_OPTION query id {query_id} does not exist".format(query_id=to_option)
log.error(message)
raise Exception(message)
elif to_option not in TO_OPTIONS:
log.error("Unexpected bulk email TO_OPTION found: %s", to_option)
raise Exception("Unexpected bulk email TO_OPTION found: {0}".format(to_option))
if to_option.isdigit():
recipient_queryset = get_group_query_students(course_id, int(to_option))
return [recipient_queryset]
elif to_option == SEND_TO_MYSELF:
user = User.objects.filter(id=user_id)
return [use_read_replica_if_available(user)]
else:
staff_qset = CourseStaffRole(course_id).users_with_role()
instructor_qset = CourseInstructorRole(course_id).users_with_role()
staff_instructor_qset = (staff_qset | instructor_qset).distinct()
if to_option == SEND_TO_STAFF:
return [use_read_replica_if_available(staff_instructor_qset)]
if to_option == SEND_TO_ALL:
# We also require students to have activated their accounts to
# provide verification that the provided email address is valid.
enrollment_qset = User.objects.filter(
is_active=True,
courseenrollment__course_id=course_id,
courseenrollment__is_active=True
)
# to avoid duplicates, we only want to email unenrolled course staff
# members here
unenrolled_staff_qset = staff_instructor_qset.exclude(
courseenrollment__course_id=course_id, courseenrollment__is_active=True
)
# use read_replica if available
recipient_qsets = [
use_read_replica_if_available(unenrolled_staff_qset),
use_read_replica_if_available(enrollment_qset),
]
return recipient_qsets
def _get_recipient_querysets(user_id, to_option, course_id, custom_to_addr):
"""
Returns a list of query sets of email recipients corresponding to the
requested `to_option` category.
`to_option` is either SEND_TO_MYSELF, SEND_TO_STAFF, or SEND_TO_ALL.
Recipients who are in more than one category (e.g. enrolled in the course
and are staff or self) will be properly deduped.
"""
if to_option not in TO_OPTIONS:
log.error("Unexpected bulk email TO_OPTION found: %s", to_option)
raise Exception("Unexpected bulk email TO_OPTION found: {0}".format(to_option))
if to_option == SEND_TO_MYSELF:
user = User.objects.filter(id=user_id)
return [use_read_replica_if_available(user)]
else:
staff_qset = CourseStaffRole(course_id).users_with_role()
instructor_qset = CourseInstructorRole(course_id).users_with_role()
staff_instructor_qset = (staff_qset | instructor_qset).distinct()
if to_option == SEND_TO_STAFF:
return [use_read_replica_if_available(staff_instructor_qset)]
if to_option == SEND_TO_ALL:
# We also require students to have activated their accounts to
# provide verification that the provided email address is valid.
enrollment_qset = User.objects.filter(
is_active=True,
courseenrollment__course_id=course_id,
courseenrollment__is_active=True
)
# to avoid duplicates, we only want to email unenrolled course staff
# members here
unenrolled_staff_qset = staff_instructor_qset.exclude(
courseenrollment__course_id=course_id, courseenrollment__is_active=True
)
# use read_replica if available
recipient_qsets = [
use_read_replica_if_available(unenrolled_staff_qset),
use_read_replica_if_available(enrollment_qset),
]
return recipient_qsets
if to_option == SEND_TO_CUSTOM:
custom_to_addr = split_emails_or_throw_error(custom_to_addr)
user = User.objects.filter(email__in=custom_to_addr)
return [use_read_replica_if_available(user)]
def handle(self, *args, **options):
"""Fix newline courses in CSM!"""
if len(args) != 2:
raise CommandError('Must specify start and end dates: e.g. "2016-08-23 16:43:00" "2016-08-24 22:00:00"')
start, end = args
dry_run = options['dry_run']
log.info(
"Starting fix_student_module_newlines in %s mode!",
"dry_run" if dry_run else "real"
)
rows_to_fix = use_read_replica_if_available(
# pylint: disable=no-member
StudentModule.objects.raw(
"select * from courseware_studentmodule where modified between %s and %s and course_id like %s",
(start, end, '%\n')
)
)
results = [self.fix_row(row, dry_run=dry_run) for row in rows_to_fix]
log.info(
"Finished fix_student_module_newlines in %s mode!",
"dry_run" if dry_run else "real"
)
log.info("Stats: %s rows detected", len(results))
if results:
# Add up all the columns
aggregated_result = FixResult(*[sum(col) for col in zip(*results)])
log.info("Results: %s", aggregated_result)
def verified_certificates_contributing_more_than_minimum(cls, course_id):
return use_read_replica_if_available(
CertificateItem.objects.filter(
course_id=course_id,
mode='verified',
status='purchased',
unit_cost__gt=(CourseMode.min_course_price_for_verified_for_currency(course_id, 'usd'))).count())
def _get_unsynced_users(self, site_domain, last_synced_user, days_threshold):
"""
Args:
site_domain: site where we need unsynced users
last_synced_user: last synced user
days_threshold: number of days threshold to sync users in case we don't have last synced user
Returns: Ordered list of users needs to be synced
"""
if last_synced_user:
users = User.objects.select_related('profile').filter(id__gt=last_synced_user.id).order_by('pk')
else:
# If we don't have last synced user get all users who joined on between today and threshold days ago
start_date = datetime.now().date() - timedelta(days_threshold)
self.stdout.write(
'Started pulling unsynced contacts for site {site} from {start_date}'.format(
site=site_domain, start_date=start_date
)
)
users = User.objects.select_related('profile').filter(date_joined__date__gte=start_date).order_by('pk')
unsynced_users = [
user for user in use_read_replica_if_available(users)
if UserAttribute.get_user_attribute(user, 'created_on_site') == site_domain
]
return unsynced_users
def _get_unsynced_users(self, site_domain, last_synced_user,
days_threshold):
"""
Args:
site_domain: site where we need unsynced users
last_synced_user: last synced user
days_threshold: number of days threshold to sync users in case we don't have last synced user
Returns: Ordered list of users needs to be synced
"""
if last_synced_user:
self.stdout.write(
u'Started pulling unsynced contacts for site {site} created after user {user}'
.format(site=site_domain, user=last_synced_user))
users = User.objects.select_related('profile').filter(
id__gt=last_synced_user.id).order_by('pk')
else:
# If we don't have last synced user get all users who joined on between today and threshold days ago
start_date = datetime.now().date() - timedelta(days_threshold)
self.stdout.write(
u'Started pulling unsynced contacts for site {site} from {start_date}'
.format(site=site_domain, start_date=start_date))
users = User.objects.select_related('profile').filter(
date_joined__date__gte=start_date).order_by('pk')
for user in use_read_replica_if_available(users):
if UserAttribute.get_user_attribute(
user, 'created_on_site') == site_domain:
yield user
def _paginate_users(self,
course_key,
course_enrollment_filter=None,
related_models=None):
"""
Args:
course_key (CourseLocator): The course to retrieve grades for.
course_enrollment_filter: Optional list of Q objects to pass
to `CourseEnrollment.filter()`.
related_models: Optional list of related models to join to the CourseEnrollment table.
Returns:
A list of users, pulled from a paginated queryset of enrollments, who are enrolled in the given course.
"""
filter_args = [Q(course_id=course_key) & Q(is_active=True)]
filter_args.extend(course_enrollment_filter or [])
enrollments_in_course = use_read_replica_if_available(
CourseEnrollment.objects.filter(*filter_args))
if related_models:
enrollments_in_course = enrollments_in_course.select_related(
*related_models)
paged_enrollments = self.paginate_queryset(enrollments_in_course)
return [enrollment.user for enrollment in paged_enrollments]
def verified_certificates_contributing_more_than_minimum(cls, course_id):
return use_read_replica_if_available(
CertificateItem.objects.filter(
course_id=course_id,
mode='verified',
status='purchased',
unit_cost__gt=(CourseMode.min_course_price_for_verified_for_currency(course_id, 'usd')))).count()
def _paginate_users(self,
course_key,
course_enrollment_filter=None,
related_models=None,
annotations=None):
"""
Args:
course_key (CourseLocator): The course to retrieve grades for.
course_enrollment_filter: Optional list of Q objects to pass
to `CourseEnrollment.filter()`.
related_models: Optional list of related models to join to the CourseEnrollment table.
annotations: Optional dict of fields to add to the queryset via annotation
Returns:
A list of users, pulled from a paginated queryset of enrollments, who are enrolled in the given course.
"""
queryset = CourseEnrollment.objects
if annotations:
queryset = queryset.annotate(**annotations)
filter_args = [Q(course_id=course_key) & Q(is_active=True)]
filter_args.extend(course_enrollment_filter or [])
enrollments_in_course = use_read_replica_if_available(
queryset.filter(*filter_args))
if related_models:
enrollments_in_course = enrollments_in_course.select_related(
*related_models)
paged_enrollments = self.paginate_queryset(enrollments_in_course)
retlist = []
for enrollment in paged_enrollments:
enrollment.user.enrollment_mode = enrollment.mode
retlist.append(enrollment.user)
return retlist
def _paginate_users(self,
course_key,
course_enrollment_filter=None,
related_models=None):
"""
Args:
course_key (CourseLocator): The course to retrieve grades for.
course_enrollment_filter: Optional dictionary of keyword arguments to pass
to `CourseEnrollment.filter()`.
related_models: Optional list of related models to join to the CourseEnrollment table.
Returns:
A list of users, pulled from a paginated queryset of enrollments, who are enrolled in the given course.
"""
filter_kwargs = {
'course_id': course_key,
'is_active': True,
}
filter_kwargs.update(course_enrollment_filter or {})
enrollments_in_course = use_read_replica_if_available(
CourseEnrollment.objects.filter(**filter_kwargs))
if related_models:
enrollments_in_course = enrollments_in_course.select_related(
*related_models)
paged_enrollments = self.paginate_queryset(enrollments_in_course)
return [enrollment.user for enrollment in paged_enrollments]
def handle(self, *args, **options):
"""Fix newline courses in CSM!"""
if len(args) != 2:
raise CommandError(
'Must specify start and end dates: e.g. "2016-08-23 16:43:00" "2016-08-24 22:00:00"'
)
start, end = args
dry_run = options['dry_run']
log.info("Starting fix_student_module_newlines in %s mode!",
"dry_run" if dry_run else "real")
rows_to_fix = use_read_replica_if_available(
# pylint: disable=no-member
StudentModule.objects.raw(
"select * from courseware_studentmodule where modified between %s and %s and course_id like %s",
(start, end, '%\n')))
results = [self.fix_row(row, dry_run=dry_run) for row in rows_to_fix]
log.info("Finished fix_student_module_newlines in %s mode!",
"dry_run" if dry_run else "real")
log.info("Stats: %s rows detected", len(results))
if results:
# Add up all the columns
aggregated_result = FixResult(*[sum(col) for col in zip(*results)])
log.info("Results: %s", aggregated_result)
def get(self, request, program_uuid=None):
""" Defines the GET list endpoint for ProgramEnrollment objects. """
enrollments = use_read_replica_if_available(
ProgramEnrollment.objects.filter(program_uuid=program_uuid)
)
paginated_enrollments = self.paginate_queryset(enrollments)
serializer = ProgramEnrollmentListSerializer(paginated_enrollments, many=True)
return self.get_paginated_response(serializer.data)
def get(self, request, program_uuid=None):
""" Defines the GET list endpoint for ProgramEnrollment objects. """
enrollments = use_read_replica_if_available(
ProgramEnrollment.objects.filter(program_uuid=program_uuid))
paginated_enrollments = self.paginate_queryset(enrollments)
serializer = ProgramEnrollmentListSerializer(paginated_enrollments,
many=True)
return self.get_paginated_response(serializer.data)
def handle(self, *args, **options):
"""
Handler for the command
It creates batches of expired Software Secure Photo Verification and sends it to send_verification_expiry_email
that used edx_ace to send email to these learners
"""
resend_days = options['resend_days']
batch_size = options['batch_size']
sleep_time = options['sleep_time']
days = options['days_range']
dry_run = options['dry_run']
# If email was sent and user did not re-verify then this date will be used as the criteria for resending email
date_resend_days_ago = datetime.now(UTC) - timedelta(days=resend_days)
start_date = datetime.now(UTC) - timedelta(days=days)
# Adding an order_by() clause will override the class meta ordering as we don't need ordering here
query = SoftwareSecurePhotoVerification.objects.filter(
Q(status='approved')
& (Q(expiry_date__date__gte=start_date.date(),
expiry_date__date__lt=datetime.now(UTC).date())
| Q(expiry_email_date__lt=date_resend_days_ago.date()))
).order_by()
sspv = use_read_replica_if_available(query)
total_verification = sspv.count()
if not total_verification:
logger.info(
u"No approved expired entries found in SoftwareSecurePhotoVerification for the "
u"date range {} - {}".format(start_date.date(),
datetime.now(UTC).date()))
return
logger.info(
u"For the date range {} - {}, total Software Secure Photo verification filtered are {}"
.format(start_date.date(),
datetime.now(UTC).date(), total_verification))
batch_verifications = []
for verification in sspv:
if not verification.expiry_email_date or verification.expiry_email_date < date_resend_days_ago:
batch_verifications.append(verification)
if len(batch_verifications) == batch_size:
send_verification_expiry_email(batch_verifications,
dry_run)
time.sleep(sleep_time)
batch_verifications = []
# If selected verification in batch are less than batch_size
if batch_verifications:
send_verification_expiry_email(batch_verifications, dry_run)
def _get_recipient_queryset(user_id, to_option, course_id):
"""
Returns a query set of email recipients corresponding to the requested to_option category.
`to_option` is either SEND_TO_MYSELF, SEND_TO_STAFF, or SEND_TO_ALL.
Recipients who are in more than one category (e.g. enrolled in the course and are staff or self)
will be properly deduped.
"""
if to_option not in TO_OPTIONS:
log.error("Unexpected bulk email TO_OPTION found: %s", to_option)
raise Exception(
"Unexpected bulk email TO_OPTION found: {0}".format(to_option))
if to_option == SEND_TO_MYSELF:
recipient_qset = User.objects.filter(id=user_id)
else:
staff_qset = CourseStaffRole(course_id).users_with_role()
instructor_qset = CourseInstructorRole(course_id).users_with_role()
recipient_qset = (staff_qset | instructor_qset).distinct()
if to_option == SEND_TO_ALL:
# We also require students to have activated their accounts to
# provide verification that the provided email address is valid.
enrollment_qset = User.objects.filter(
is_active=True,
courseenrollment__course_id=course_id,
courseenrollment__is_active=True)
# Now we do some queryset sidestepping to avoid doing a DISTINCT
# query across the course staff and the enrolled students, which
# forces the creation of a temporary table in the db.
unenrolled_staff_qset = recipient_qset.exclude(
courseenrollment__course_id=course_id,
courseenrollment__is_active=True)
# use read_replica if available:
unenrolled_staff_qset = use_read_replica_if_available(
unenrolled_staff_qset)
unenrolled_staff_ids = [user.id for user in unenrolled_staff_qset]
recipient_qset = enrollment_qset | User.objects.filter(
id__in=unenrolled_staff_ids)
# again, use read_replica if available to lighten the load for large queries
return use_read_replica_if_available(recipient_qset)
def get(self, request, program_uuid=None, course_id=None):
""" Defines the GET list endpoint for ProgramCourseEnrollment objects. """
course_key = CourseKey.from_string(course_id)
enrollments = use_read_replica_if_available(
ProgramCourseEnrollment.objects.filter(
program_enrollment__program_uuid=program_uuid,
course_key=course_key).select_related('program_enrollment'))
paginated_enrollments = self.paginate_queryset(enrollments)
serializer = ProgramCourseEnrollmentListSerializer(
paginated_enrollments, many=True)
return self.get_paginated_response(serializer.data)
def _get_recipient_queryset(user_id, to_option, course_id, course_location):
"""
Returns a query set of email recipients corresponding to the requested to_option category.
`to_option` is either SEND_TO_MYSELF, SEND_TO_STAFF, or SEND_TO_ALL.
Recipients who are in more than one category (e.g. enrolled in the course and are staff or self)
will be properly deduped.
"""
if to_option not in TO_OPTIONS:
log.error("Unexpected bulk email TO_OPTION found: %s", to_option)
raise Exception("Unexpected bulk email TO_OPTION found: {0}".format(to_option))
if to_option == SEND_TO_MYSELF:
recipient_qset = User.objects.filter(id=user_id)
else:
staff_qset = CourseStaffRole(course_id).users_with_role()
instructor_qset = CourseInstructorRole(course_id).users_with_role()
recipient_qset = (staff_qset | instructor_qset).distinct()
if to_option == SEND_TO_ALL:
# We also require students to have activated their accounts to
# provide verification that the provided email address is valid.
enrollment_qset = User.objects.filter(
is_active=True,
courseenrollment__course_id=course_id,
courseenrollment__is_active=True
)
# Now we do some queryset sidestepping to avoid doing a DISTINCT
# query across the course staff and the enrolled students, which
# forces the creation of a temporary table in the db.
unenrolled_staff_qset = recipient_qset.exclude(
courseenrollment__course_id=course_id, courseenrollment__is_active=True
)
# use read_replica if available:
unenrolled_staff_qset = use_read_replica_if_available(unenrolled_staff_qset)
unenrolled_staff_ids = [user.id for user in unenrolled_staff_qset]
recipient_qset = enrollment_qset | User.objects.filter(id__in=unenrolled_staff_ids)
# again, use read_replica if available to lighten the load for large queries
return use_read_replica_if_available(recipient_qset)
def _get_users_queryset(self, initial_days):
"""
initial_days: numbers of days to go back from today
:return: users queryset
"""
start_date = datetime.now().date() - timedelta(initial_days)
end_date = datetime.now().date() - timedelta(1)
self.stdout.write(u'Getting users from {start} to {end}'.format(start=start_date, end=end_date))
users_qs = User.objects.filter(
date_joined__date__gte=start_date,
date_joined__date__lte=end_date
).order_by('id')
return use_read_replica_if_available(users_qs)
def get(self, request, program_uuid=None, course_id=None):
""" Defines the GET list endpoint for ProgramCourseEnrollment objects. """
course_key = CourseKey.from_string(course_id)
enrollments = use_read_replica_if_available(
ProgramCourseEnrollment.objects.filter(
program_enrollment__program_uuid=program_uuid, course_key=course_key
).select_related(
'program_enrollment'
)
)
paginated_enrollments = self.paginate_queryset(enrollments)
serializer = ProgramCourseEnrollmentListSerializer(paginated_enrollments, many=True)
return self.get_paginated_response(serializer.data)
def _get_users_queryset(self, initial_days):
"""
initial_days: numbers of days to go back from today
:return: users queryset
"""
start_date = datetime.now().date() - timedelta(initial_days)
end_date = datetime.now().date() - timedelta(1)
self.stdout.write(u'Getting users from {start} to {end}'.format(start=start_date, end=end_date))
users_qs = User.objects.filter(
date_joined__date__gte=start_date,
date_joined__date__lte=end_date
).order_by('id')
return use_read_replica_if_available(users_qs)
def handle(self, *args, **options):
"""
Handler for the command
It creates batches of expired Software Secure Photo Verification and sends it to send_verification_expiry_email
that used edx_ace to send email to these learners
"""
resend_days = settings.VERIFICATION_EXPIRY_EMAIL['RESEND_DAYS']
days = settings.VERIFICATION_EXPIRY_EMAIL['DAYS_RANGE']
batch_size = options['batch_size']
sleep_time = options['sleep_time']
dry_run = options['dry_run']
end_date = now().replace(hour=0, minute=0, second=0, microsecond=0)
# If email was sent and user did not re-verify then this date will be used as the criteria for resending email
date_resend_days_ago = end_date - timedelta(days=resend_days)
start_date = end_date - timedelta(days=days)
# Adding an order_by() clause will override the class meta ordering as we don't need ordering here
query = SoftwareSecurePhotoVerification.objects.filter(Q(status='approved') &
(Q(expiry_date__gte=start_date,
expiry_date__lt=end_date) |
Q(expiry_email_date__lte=date_resend_days_ago)
)).order_by()
sspv = use_read_replica_if_available(query)
total_verification = sspv.count()
if not total_verification:
logger.info(u"No approved expired entries found in SoftwareSecurePhotoVerification for the "
u"date range {} - {}".format(start_date.date(), now().date()))
return
logger.info(u"For the date range {} - {}, total Software Secure Photo verification filtered are {}"
.format(start_date.date(), now().date(), total_verification))
batch_verifications = []
for verification in sspv:
if not verification.expiry_email_date or verification.expiry_email_date <= date_resend_days_ago:
batch_verifications.append(verification)
if len(batch_verifications) == batch_size:
send_verification_expiry_email(batch_verifications, dry_run)
time.sleep(sleep_time)
batch_verifications = []
# If selected verification in batch are less than batch_size
if batch_verifications:
send_verification_expiry_email(batch_verifications, dry_run)
def enrollment_counts(cls, course_id):
"""
Returns a dictionary that stores the total enrollment count for a course, as well as the
enrollment count for each individual mode.
"""
# Unfortunately, Django's "group by"-style queries look super-awkward
query = use_read_replica_if_available(cls.objects.filter(course_id=course_id, is_active=True).values('mode').order_by().annotate(Count('mode')))
total = 0
d = defaultdict(int)
for item in query:
d[item['mode']] = item['mode__count']
total += item['mode__count']
d['total'] = total
return d
def enrollment_counts(cls, course_id, date):
query = use_read_replica_if_available(CourseEnrollment.objects.filter(
course_id=course_id,
is_active=True,
created__lte=date,
).values('mode').order_by().annotate(Count('mode')))
total = 0
enroll_dict = defaultdict(int)
for item in query:
enroll_dict[item['mode']] = item['mode__count']
total += item['mode__count']
enroll_dict['total'] = total
return enroll_dict
def enrollment_counts(cls, course_id):
"""
Returns a dictionary that stores the total enrollment count for a course, as well as the
enrollment count for each individual mode.
"""
# Unfortunately, Django's "group by"-style queries look super-awkward
query = use_read_replica_if_available(cls.objects.filter(course_id=course_id, is_active=True).values('mode').order_by().annotate(Count('mode')))
total = 0
d = defaultdict(int)
for item in query:
d[item['mode']] = item['mode__count']
total += item['mode__count']
d['total'] = total
return d
def rows(self):
query1 = use_read_replica_if_available(
CertificateItem.objects.select_related('user__profile').filter(
status="refunded",
refund_requested_time__gte=self.start_date,
refund_requested_time__lt=self.end_date,
).order_by('refund_requested_time'))
query2 = use_read_replica_if_available(
CertificateItem.objects.select_related('user__profile').filter(
status="refunded",
refund_requested_time=None,
))
query = query1 | query2
for item in query:
yield [
item.order_id,
item.user.profile.name,
item.fulfilled_time,
item.refund_requested_time,
item.line_cost,
item.service_fee,
]
def verified_certificates_monetary_field_sum(cls, course_id, status, field_to_aggregate):
"""
Returns a Decimal indicating the total sum of field_to_aggregate for all verified certificates with a particular status.
Sample usages:
- status 'refunded' and field_to_aggregate 'unit_cost' will give the total amount of money refunded for course_id
- status 'purchased' and field_to_aggregate 'service_fees' gives the sum of all service fees for purchased certificates
etc
"""
query = use_read_replica_if_available(
CertificateItem.objects.filter(course_id=course_id, mode='verified', status=status).aggregate(Sum(field_to_aggregate)))[field_to_aggregate + '__sum']
if query is None:
return Decimal(0.00)
else:
return query
def verified_certificates_monetary_field_sum(cls, course_id, status, field_to_aggregate):
"""
Returns a Decimal indicating the total sum of field_to_aggregate for all verified certificates with a particular status.
Sample usages:
- status 'refunded' and field_to_aggregate 'unit_cost' will give the total amount of money refunded for course_id
- status 'purchased' and field_to_aggregate 'service_fees' gives the sum of all service fees for purchased certificates
etc
"""
query = use_read_replica_if_available(
CertificateItem.objects.filter(course_id=course_id, mode='verified', status=status)).aggregate(Sum(field_to_aggregate))[field_to_aggregate + '__sum']
if query is None:
return Decimal(0.00)
else:
return query
def rows(self):
query1 = use_read_replica_if_available(
CertificateItem.objects.select_related('user__profile').filter(
status="refunded",
refund_requested_time__gte=self.start_date,
refund_requested_time__lt=self.end_date,
).order_by('refund_requested_time'))
query2 = use_read_replica_if_available(
CertificateItem.objects.select_related('user__profile').filter(
status="refunded",
refund_requested_time=None,
))
query = query1 | query2
for item in query:
yield [
item.order_id,
item.user.profile.name,
item.fulfilled_time,
item.refund_requested_time,
item.line_cost,
item.service_fee,
]
def _get_enrollments_queryset(self, start_index, end_index):
"""
Args:
start_index: start index or None
end_index: end index or None
Returns:
EnterpriseCourseEnrollments Queryset
"""
self.stdout.write(u'Getting enrollments from {start} to {end} index (as per command params)'
.format(start=start_index or 'start', end=end_index or 'end'))
enrollments_qs = EnterpriseCourseEnrollment.objects.filter(
source__isnull=True
).order_by('id')[start_index:end_index]
return use_read_replica_if_available(enrollments_qs)
def rows(self):
query = use_read_replica_if_available(
OrderItem.objects.filter(
status="purchased",
fulfilled_time__gte=self.start_date,
fulfilled_time__lt=self.end_date,
).order_by("fulfilled_time"))
for item in query:
yield [
item.fulfilled_time,
item.order_id, # pylint: disable=no-member
item.status,
item.qty,
item.unit_cost,
item.line_cost,
item.currency,
item.line_desc,
item.report_comments,
]
def rows(self):
query = use_read_replica_if_available(
OrderItem.objects.filter(
status="purchased",
fulfilled_time__gte=self.start_date,
fulfilled_time__lt=self.end_date,
).order_by("fulfilled_time"))
for item in query:
yield [
item.fulfilled_time,
item.order_id,
item.status,
item.qty,
item.unit_cost,
item.line_cost,
item.currency,
item.line_desc,
item.report_comments,
]
def handle(self, *args, **options):
"""
Handler for the command
It filters approved Software Secure Photo Verification and then for each distinct user it finds the most
recent approved verification and set its expiry_date
"""
batch_size = options['batch_size']
sleep_time = options['sleep_time']
query = SoftwareSecurePhotoVerification.objects.filter(
status='approved').order_by()
sspv = use_read_replica_if_available(query)
if not sspv.count():
logger.info(
"No approved entries found in SoftwareSecurePhotoVerification")
return
distinct_user_ids = set()
update_verification_ids = []
update_verification_count = 0
for verification in sspv:
if verification.user_id not in distinct_user_ids:
distinct_user_ids.add(verification.user_id)
recent_verification = self.find_recent_verification(
sspv, verification.user_id)
if not recent_verification.expiry_date:
update_verification_ids.append(recent_verification.pk)
update_verification_count += 1
if update_verification_count == batch_size:
self.bulk_update(update_verification_ids)
update_verification_count = 0
update_verification_ids = []
time.sleep(sleep_time)
if update_verification_ids:
self.bulk_update(update_verification_ids)
def handle(self, *args, **options):
"""
Handler for the command
It filters approved Software Secure Photo Verification and then for each distinct user it finds the most
recent approved verification and set its expiry_date
"""
batch_size = options['batch_size']
sleep_time = options['sleep_time']
query = SoftwareSecurePhotoVerification.objects.filter(status='approved').order_by()
sspv = use_read_replica_if_available(query)
if not sspv.count():
logger.info("No approved entries found in SoftwareSecurePhotoVerification")
return
distinct_user_ids = set()
update_verification_ids = []
update_verification_count = 0
for verification in sspv:
if verification.user_id not in distinct_user_ids:
distinct_user_ids.add(verification.user_id)
recent_verification = self.find_recent_verification(sspv, verification.user_id)
if not recent_verification.expiry_date:
update_verification_ids.append(recent_verification.pk)
update_verification_count += 1
if update_verification_count == batch_size:
self.bulk_update(update_verification_ids)
update_verification_count = 0
update_verification_ids = []
time.sleep(sleep_time)
if update_verification_ids:
self.bulk_update(update_verification_ids)
def _paginate_users(self, course_key, course_enrollment_filter=None, related_models=None):
"""
Args:
course_key (CourseLocator): The course to retrieve grades for.
course_enrollment_filter: Optional dictionary of keyword arguments to pass
to `CourseEnrollment.filter()`.
related_models: Optional list of related models to join to the CourseEnrollment table.
Returns:
A list of users, pulled from a paginated queryset of enrollments, who are enrolled in the given course.
"""
filter_kwargs = {
'course_id': course_key,
'is_active': True,
}
filter_kwargs.update(course_enrollment_filter or {})
enrollments_in_course = use_read_replica_if_available(
CourseEnrollment.objects.filter(**filter_kwargs)
)
if related_models:
enrollments_in_course = enrollments_in_course.select_related(*related_models)
paged_enrollments = self.paginate_queryset(enrollments_in_course)
return [enrollment.user for enrollment in paged_enrollments]
def handle(self, *args, **options):
"""
Handler for the command
It creates batches of expired Software Secure Photo Verification and sends it to send_verification_expiry_email
that used edx_ace to send email to these learners
"""
default_emails = settings.VERIFICATION_EXPIRY_EMAIL['DEFAULT_EMAILS']
resend_days = settings.VERIFICATION_EXPIRY_EMAIL['RESEND_DAYS']
days = settings.VERIFICATION_EXPIRY_EMAIL['DAYS_RANGE']
batch_size = options['batch_size']
sleep_time = options['sleep_time']
dry_run = options['dry_run']
if default_emails <= 0:
raise CommandError(u'DEFAULT_EMAILS must be a positive integer. If you do not wish to send emails '
u'use --dry-run flag instead.')
end_date = now().replace(hour=0, minute=0, second=0, microsecond=0)
# If email was sent and user did not re-verify then this date will be used as the criteria for resending email
date_resend_days_ago = end_date - timedelta(days=resend_days)
start_date = end_date - timedelta(days=days)
# Adding an order_by() clause will override the class meta ordering as we don't need ordering here
query = SoftwareSecurePhotoVerification.objects.filter(Q(status='approved') &
Q(expiry_date__isnull=False) &
(Q(expiry_date__gte=start_date,
expiry_date__lt=end_date) |
Q(expiry_email_date__lte=date_resend_days_ago)
)).order_by()
sspv = use_read_replica_if_available(query)
total_verification = sspv.count()
if not total_verification:
logger.info(u"No approved expired entries found in SoftwareSecurePhotoVerification for the "
u"date range {} - {}".format(start_date.date(), now().date()))
return
logger.info(u"For the date range {} - {}, total Software Secure Photo verification filtered are {}"
.format(start_date.date(), now().date(), total_verification))
batch_verifications = []
email_config = {
'resend_days': resend_days,
'dry_run': dry_run,
'default_emails': default_emails
}
success = True
for verification in sspv:
user = verification.user
if self.user_has_valid_verification(user):
continue
if not verification.expiry_email_date or verification.expiry_email_date <= date_resend_days_ago:
batch_verifications.append(verification)
if len(batch_verifications) == batch_size:
success = self.send_verification_expiry_email(batch_verifications, email_config) and success
time.sleep(sleep_time)
batch_verifications = []
# If selected verification in batch are less than batch_size
if batch_verifications:
success = self.send_verification_expiry_email(batch_verifications, email_config) and success
if not success:
raise CommandError('One or more email attempts failed. Search for "Could not send" above.')
def verified_certificates_count(cls, course_id, status):
"""Return a queryset of CertificateItem for every verified enrollment in course_id with the given status."""
return use_read_replica_if_available(
CertificateItem.objects.filter(course_id=course_id,
mode='verified',
status=status).count())
def verified_certificates_count(cls, course_id, status):
"""Return a queryset of CertificateItem for every verified enrollment in course_id with the given status."""
return use_read_replica_if_available(
CertificateItem.objects.filter(course_id=course_id, mode='verified', status=status).count())
