from django.contrib.auth import get_user_model from django.contrib.auth.models import User from django.db.models import Count from django.utils import timezone from datetime import timedelta from rest_framework import viewsets, filters, views, permissions, status from rest_framework.response import Response from rest_framework.permissions import IsAuthenticated from rest_framework.pagination import PageNumberPagination from django_filters.rest_framework import DjangoFilterBackend from rest_pandas import PandasViewSet from rest_pandas.renderers import PandasCSVRenderer, PandasJSONRenderer import pandas as pd from discussion.models import Comment, CommentLike, Topic, TopicLike from baquara.users.models import UserLogin from courses.models import Course, CourseStudent, StudentProgress from courses.classes.models import Class from courses_learning_objects.models import LearningObject, Answer from courses.reports.serializers import ( UserInDetailSerializer, UsersByClassSerializer, UserStatsSerializer ) from django.core.exceptions import ObjectDoesNotExist User = get_user_model() class SummaryViewSet(viewsets.ViewSet): permission_classes = [IsAuthenticated] def list_new(self, request): courses = Course.objects.all().prefetch_related('class_set', 'course_students') stats = {} activities = {} unit_set = {} all_answers = {} all_progress = {} for course in courses: stats[course.slug] = { 'name': course.name, 'user_count': course.course_students.filter(user__is_active=True).count(), 'user_finished_course_count': 0, 'classes' : [] } unit_set[course.slug] = course.unit_set.count() le_activities = LearningObject.objects \ .filter(unit__lesson__in=course.lessons .filter(status='published'), type="discussion") all_answers[course.slug] = {} all_progress[course.slug] = {} activities[course.slug] = le_activities.count() aux = Answer.objects \ .filter(activity__in=le_activities, user__is_active=True)\ .values('user')\ .order_by('user')\ .annotate(Count('user')) for ans in aux: all_answers[course.slug][ans['user']] = ans['user__count'] aux = StudentProgress.objects\ .exclude(complete=None) \ .filter(unit__lesson__course=course, user__is_active=True)\ .values('user')\ .order_by('user').\ annotate(Count('user')) for progress in aux: all_progress[course.slug][progress['user']] = progress['user__count'] def __plpc_course_finished(activities, answers, units_len, units_done_len, min_percent_to_complete): return __percent_progress(units_len, units_done_len) >= min_percent_to_complete and \ activities == answers def __percent_progress(units_len, units_done_len): if units_len <= 0: return 0 return int(100.0 * units_done_len / units_len) def __can_emmit_receipt(cclass, certificate, answers, user): course_finished = __plpc_course_finished(activities[cclass.course.slug], answers, unit_set[cclass.course.slug], all_progress[cclass.course.slug].get(user, 0), cclass.course.min_percent_to_complete) if not cclass.user_can_certificate and not course_finished: return False if cclass.user_can_certificate_even_without_progress and certificate: return True return course_finished classes = Class.objects.filter(course__in=courses).prefetch_related('students').select_related('course') for cclass in classes: certified = cclass.get_students.filter(certificate__type='certificate', user__is_active=True).select_related('course', 'user') not_certified = cclass.get_students.exclude(certificate__type='certificate', user__is_active=False).select_related('course', 'user') cclass_stats = { 'name': cclass.name, 'user_count': cclass.get_students.filter(user__is_active=True).count(), 'certificate_count': certified.count(), 'user_finished': 0 } for cs in certified: answers = all_answers[cclass.course.slug].get(cs.user.id, 0) if __can_emmit_receipt(cclass, True, answers, cs.user.id): stats[cclass.course.slug]['user_finished_course_count'] += 1 cclass_stats['user_finished'] += 1 for cs in not_certified: answers = all_answers[cclass.course.slug].get(cs.user.id, 0) if __can_emmit_receipt(cclass, False, answers, cs.user.id): stats[cclass.course.slug]['user_finished_course_count'] += 1 cclass_stats['user_finished'] += 1 stats[cclass.course.slug]['classes'].append(cclass_stats) stats = list(stats.values()) users_active = User.objects.filter(is_active=True) users_director_total = users_active.filter(groups__name='Diretor Escolar').count() users_director_login = users_active.filter(groups__name='Diretor Escolar').exclude(last_login=None).count() users_coordinator_total = users_active.filter(groups__name='Coordenação Pedagógica').count() users_coordinator_login = users_active.filter(groups__name='Coordenação Pedagógica').exclude(last_login=None).count() users_technical_total = users_active.filter(groups__name='Equipe Técnica').count() users_technical_login = users_active.filter(groups__name='Equipe Técnica').exclude(last_login=None).count() users_formadores_total = users_active.filter(groups__name='Formadores').count() users_formadores_login = users_active.filter(groups__name='Formadores').exclude(last_login=None).count() users_apoio_total = users_active.filter(groups__name='Apoio Pedagógico').count() users_apoio_login = users_active.filter(groups__name='Apoio Pedagógico').exclude(last_login=None).count() response = Response({ 'users_director_total': users_director_total, 'users_director_login': users_director_login, 'users_coordinator_total': users_coordinator_total, 'users_coordinator_login': users_coordinator_login, 'users_technical_total': users_technical_total, 'users_technical_login': users_technical_login, 'users_formadores_total': users_formadores_total, 'users_formadores_login': users_formadores_login, 'users_apoio_total': users_apoio_total, 'users_apoio_login': users_apoio_login, 'user_count': User.objects.filter(is_active=True).count(), 'user_inactive_count': User.objects.filter(is_active=False).count(), 'total_number_of_topics': Topic.objects.filter(author__is_active=True).count(), 'total_number_of_comments': Comment.objects.filter(author__is_active=True).count(), 'total_number_of_likes': TopicLike.objects.filter(user__is_active=True).count() + CommentLike.objects.filter(user__is_active=True).count(), 'statistics_per_course': stats}) return response def list(self, request): return self.list_new(request) def list_old(self, request): courses = Course.objects.all() stats = [] for course in courses: course_stats = { 'name': course.name, 'user_count': course.course_students.count(), 'user_finished_course_count': 0 } classes = Class.objects.filter(course=course) classes_stats = [] for cclass in classes: cclass_stats = { 'name': cclass.name, 'user_count': cclass.get_students.count(), 'certificate_count': 0, 'user_finished': 0 } course_students = cclass.get_students.all() certified = course_students.filter(certificate__type='certificate') cclass_stats['certificate_count'] += certified.count() for cs in course_students: # if cs.course_finished: # cclass_stats['user_finished'] += 1 if cs.can_emmit_receipt(): course_stats['user_finished_course_count'] += 1 cclass_stats['user_finished'] += 1 classes_stats.append(cclass_stats) course_stats['classes'] = classes_stats stats.append(course_stats) response = Response({ 'user_count': User.objects.count(), 'total_number_of_topics': Topic.objects.count(), 'total_number_of_comments': Comment.objects.count(), 'total_number_of_likes': TopicLike.objects.count() + CommentLike.objects.count(), 'statistics_per_course': stats}) return response class UsersByGroupViewSet(PandasViewSet): permission_classes = [IsAuthenticated] renderer_classes = [PandasCSVRenderer, PandasJSONRenderer] queryset = User.objects.all() def get_queryset(self): self.queryset = super(UsersByGroupViewSet, self).get_queryset() groups = self.request.query_params.get('group', None) if groups is not None: self.queryset = self.queryset.filter(groups__name__in=groups.split(',')) self.queryset = self.queryset.prefetch_related('groups', 'coursestudent_set') return self.queryset def list(self, request, format=None): queryset = self.get_queryset() serializer = UserInDetailSerializer(queryset, many=True) # in order to get the data in the wanted column form, I'll need to make some transformations return self.update_pandas_headers(Response(self.transform_data(serializer.data))) def transform_data(self, data): response = [] for user in data: for course in user.get('courses'): user.update({ u'{} - progresso'.format(course['course_name']): course['percent_progress'], u'{} - nome da turma'.format(course['course_name']): course['class_name'], u'{} - concluiu'.format(course['course_name']): course['course_finished'], u'{} - possui certificado'.format(course['course_name']): course['has_certificate'] }) user.pop('courses', None) response.append(user) return pd.DataFrame.from_dict(response) def get_pandas_filename(self, request, format): return 'Relatório de atividades dos usuários' class UsersByClassViewSet(PandasViewSet): permission_classes = [IsAuthenticated] queryset = CourseStudent.objects.all() def get_queryset(self): self.queryset = super(UsersByClassViewSet, self).get_queryset() ids = self.request.query_params.get('id', []) if ids: ids = [int(i) for i in ids.split(',')] classes = Class.objects.all().filter(pk__in=ids).prefetch_related('students') students = [cls.students.all() for cls in classes] students = [s for cls in students for s in cls] courses = [cls['course_id'] for cls in classes.values()] self.queryset = self.queryset \ .filter(user__in=students, course__id__in=courses) self.queryset = self.queryset.select_related('user', 'course', 'certificate') self.queryset = self.queryset.prefetch_related('course__lessons__units') return self.queryset def list(self, request, format=None): queryset = self.get_queryset() serializer = UsersByClassSerializer(queryset, many=True) return self.update_pandas_headers(Response(pd.DataFrame .from_dict(self.transform_data(serializer.data)))) # .set_index('cpf')) def transform_data(self, data): for coursestudent in data: for lesson in coursestudent.get(u'percent_progress_by_lesson', None): coursestudent.update({ u'Lição {} - Progresso'.format(lesson['name']): lesson['progress'] }) for activity in lesson.get(u'activities', None): coursestudent.update({ u'Lição {} - Atividade {} realizada'.format(lesson['name'], activity['name']): activity['done'] }) coursestudent.pop('percent_progress_by_lesson', None) return data def get_pandas_filename(self, request, format): return 'Relatório de progresso dos usuários' class ReportPagination(PageNumberPagination): page_size = 50 page_size_query_param = 'page_size' max_page_size = 100 class UserStatsViewSet(viewsets.ReadOnlyModelViewSet): model = User queryset = User.objects.all().order_by('name') group_id = '' serializer_class = UserStatsSerializer filterset_fields = ['groups'] permission_classes = [IsAuthenticated] pagination_class = ReportPagination filter_backends = [ filters.SearchFilter, DjangoFilterBackend, ] search_fields = [ 'username', 'name', 'first_name', 'last_name', 'email' ] def get_queryset(self): queryset = super().get_queryset() group_id = self.request.query_params.get('group') queryset = queryset.filter(groups__id=group_id, is_active=True) return queryset class UserAccessView(views.APIView): permission_classes = [permissions.IsAuthenticated] def format_date(self, timestamp): from django.utils import timezone tz_str = timezone.localtime(timestamp) date, hour = tz_str.strftime('%d-%m-%Y %H:%M:%S').split() return '{} às {}'.format(date, hour) def get(self, request, format=None): user_id = request.GET['user'] user = User.objects.get(id=user_id) actions = user.actor_actions.all().order_by('timestamp') last_access = '' last_login = '' last_completed_activity = '' accesses_7_days = 0 accesses_30_days = 0 if actions: last_access_obj = actions.filter(verb='access').last() last_activity_obj = actions.filter(verb='answered').last() if last_access_obj: last_access = self.format_date(last_access_obj.timestamp) if last_activity_obj: last_completed_activity = self.format_date(last_activity_obj.timestamp) time_delta = timezone.now() - timedelta(days=7) accesses_7_days = actions.filter(verb='access', timestamp__gte=time_delta).count() time_delta = timezone.now() - timedelta(days=30) accesses_30_days = actions.filter(verb='access', timestamp__gte=time_delta).count() if user.last_login: last_login = self.format_date(user.last_login) access_data = { 'last_login': last_login, 'last_completed_activity': last_completed_activity, 'last_access': last_access, 'accesses_7_days': accesses_7_days, 'accesses_30_days': accesses_30_days } return Response(data=access_data, status=status.HTTP_200_OK)