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 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 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 _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())