467 lines
18 KiB
Python
467 lines
18 KiB
Python
"""
|
||
Сервисы для аналитики и отчетов.
|
||
"""
|
||
from django.db.models import Sum, Avg, Count, Q, F, Min, Max
|
||
from django.utils import timezone
|
||
from datetime import timedelta, datetime
|
||
from decimal import Decimal
|
||
import logging
|
||
|
||
logger = logging.getLogger(__name__)
|
||
|
||
|
||
class AnalyticsService:
|
||
"""Сервис для аналитики ментора."""
|
||
|
||
@staticmethod
|
||
def get_detailed_lesson_stats(mentor, start_date, end_date):
|
||
"""
|
||
Детальная статистика по занятиям.
|
||
|
||
Returns:
|
||
dict: Детальная статистика
|
||
"""
|
||
from apps.schedule.models import Lesson
|
||
|
||
lessons = Lesson.objects.filter(
|
||
mentor=mentor,
|
||
start_time__gte=start_date,
|
||
start_time__lte=end_date
|
||
).select_related('client__user', 'subject')
|
||
|
||
# Общая статистика
|
||
total_lessons = lessons.count()
|
||
completed_lessons = lessons.filter(status='completed')
|
||
cancelled_lessons = lessons.filter(status='cancelled')
|
||
|
||
# Время занятий
|
||
total_duration_minutes = completed_lessons.aggregate(
|
||
total=Sum('duration')
|
||
)['total'] or 0
|
||
avg_duration = completed_lessons.aggregate(
|
||
avg=Avg('duration')
|
||
)['avg'] or 0
|
||
|
||
# Доходы
|
||
total_revenue = completed_lessons.filter(
|
||
price__isnull=False
|
||
).aggregate(
|
||
total=Sum('price')
|
||
)['total'] or Decimal('0')
|
||
|
||
avg_price = completed_lessons.filter(
|
||
price__isnull=False
|
||
).aggregate(
|
||
avg=Avg('price')
|
||
)['avg'] or Decimal('0')
|
||
|
||
# Оценки
|
||
graded_lessons = completed_lessons.filter(mentor_grade__isnull=False)
|
||
avg_grade = graded_lessons.aggregate(avg=Avg('mentor_grade'))['avg'] or 0
|
||
max_grade = graded_lessons.aggregate(max=Max('mentor_grade'))['max'] or 0
|
||
min_grade = graded_lessons.aggregate(min=Min('mentor_grade'))['min'] or 0
|
||
|
||
# По часам дня
|
||
# Оптимизация: используем один запрос с Extract вместо 24 запросов count()
|
||
from django.db.models.functions import ExtractHour
|
||
by_hour_data = completed_lessons.annotate(
|
||
hour=ExtractHour('start_time')
|
||
).values('hour').annotate(count=Count('id')).order_by('hour')
|
||
|
||
# Создаем словарь для быстрого доступа
|
||
by_hour_dict = {item['hour']: item['count'] for item in by_hour_data}
|
||
|
||
# Заполняем все 24 часа (если для какого-то часа нет данных, count = 0)
|
||
by_hour = [
|
||
{
|
||
'hour': hour,
|
||
'count': by_hour_dict.get(hour, 0),
|
||
}
|
||
for hour in range(24)
|
||
]
|
||
|
||
# По времени суток
|
||
# Оптимизация: используем один запрос с фильтрацией вместо 4 запросов count()
|
||
time_of_day_stats = completed_lessons.aggregate(
|
||
morning=Count('id', filter=Q(start_time__hour__gte=6, start_time__hour__lt=12)),
|
||
afternoon=Count('id', filter=Q(start_time__hour__gte=12, start_time__hour__lt=18)),
|
||
evening=Count('id', filter=Q(start_time__hour__gte=18, start_time__hour__lt=24)),
|
||
night=Count('id', filter=Q(start_time__hour__gte=0, start_time__hour__lt=6))
|
||
)
|
||
morning = time_of_day_stats['morning'] or 0
|
||
afternoon = time_of_day_stats['afternoon'] or 0
|
||
evening = time_of_day_stats['evening'] or 0
|
||
night = time_of_day_stats['night'] or 0
|
||
|
||
# Топ клиентов по количеству занятий
|
||
top_clients = lessons.values(
|
||
'client__user__first_name',
|
||
'client__user__last_name',
|
||
'client__user__email'
|
||
).annotate(
|
||
lessons_count=Count('id'),
|
||
completed_count=Count('id', filter=Q(status='completed')),
|
||
total_revenue=Sum('price', filter=Q(status='completed', price__isnull=False)),
|
||
avg_grade=Avg('mentor_grade', filter=Q(status='completed', mentor_grade__isnull=False))
|
||
).order_by('-lessons_count')[:10]
|
||
|
||
return {
|
||
'summary': {
|
||
'total_lessons': total_lessons,
|
||
'completed': completed_lessons.count(),
|
||
'cancelled': cancelled_lessons.count(),
|
||
'completion_rate': round((completed_lessons.count() / total_lessons * 100) if total_lessons > 0 else 0, 1),
|
||
},
|
||
'time': {
|
||
'total_duration_minutes': total_duration_minutes,
|
||
'total_duration_hours': round(total_duration_minutes / 60, 1),
|
||
'average_duration_minutes': round(avg_duration, 1),
|
||
'by_hour': by_hour,
|
||
'by_time_of_day': {
|
||
'morning': morning,
|
||
'afternoon': afternoon,
|
||
'evening': evening,
|
||
'night': night,
|
||
},
|
||
},
|
||
'revenue': {
|
||
'total': float(total_revenue),
|
||
'average_per_lesson': float(avg_price),
|
||
'potential_revenue': float(
|
||
cancelled_lessons.filter(price__isnull=False).aggregate(
|
||
total=Sum('price')
|
||
)['total'] or Decimal('0')
|
||
),
|
||
},
|
||
'grades': {
|
||
'average': round(avg_grade, 1),
|
||
'max': max_grade,
|
||
'min': min_grade,
|
||
'graded_count': graded_lessons.count(),
|
||
},
|
||
'top_clients': [
|
||
{
|
||
'name': f"{item['client__user__first_name']} {item['client__user__last_name']}".strip() or item['client__user__email'],
|
||
'email': item['client__user__email'],
|
||
'lessons_count': item['lessons_count'],
|
||
'completed_count': item['completed_count'],
|
||
'total_revenue': float(item['total_revenue'] or 0),
|
||
'average_grade': round(item['avg_grade'] or 0, 1),
|
||
}
|
||
for item in top_clients
|
||
],
|
||
}
|
||
|
||
@staticmethod
|
||
def get_time_series_data(mentor, start_date, end_date, group_by='day'):
|
||
"""
|
||
Временные ряды для графиков.
|
||
|
||
Args:
|
||
group_by: 'day', 'week', 'month'
|
||
"""
|
||
from apps.schedule.models import Lesson
|
||
|
||
lessons = Lesson.objects.filter(
|
||
mentor=mentor,
|
||
start_time__gte=start_date,
|
||
start_time__lte=end_date,
|
||
status='completed'
|
||
)
|
||
|
||
if group_by == 'day':
|
||
# Группируем по дням
|
||
data = lessons.extra(
|
||
select={'date': "DATE(start_time AT TIME ZONE 'UTC')"}
|
||
).values('date').annotate(
|
||
lessons_count=Count('id'),
|
||
revenue=Sum('price', filter=Q(price__isnull=False)),
|
||
avg_duration=Avg('duration'),
|
||
avg_grade=Avg('mentor_grade', filter=Q(mentor_grade__isnull=False))
|
||
).order_by('date')
|
||
|
||
return [
|
||
{
|
||
'date': item['date'].strftime('%Y-%m-%d'),
|
||
'lessons_count': item['lessons_count'],
|
||
'revenue': float(item['revenue'] or 0),
|
||
'avg_duration': round(item['avg_duration'] or 0, 1),
|
||
'avg_grade': round(item['avg_grade'] or 0, 1),
|
||
}
|
||
for item in data
|
||
]
|
||
|
||
elif group_by == 'week':
|
||
# Группируем по неделям
|
||
data = lessons.extra(
|
||
select={'week': "DATE_TRUNC('week', start_time AT TIME ZONE 'UTC')"}
|
||
).values('week').annotate(
|
||
lessons_count=Count('id'),
|
||
revenue=Sum('price', filter=Q(price__isnull=False)),
|
||
avg_duration=Avg('duration'),
|
||
avg_grade=Avg('mentor_grade', filter=Q(mentor_grade__isnull=False))
|
||
).order_by('week')
|
||
|
||
return [
|
||
{
|
||
'week': item['week'].strftime('%Y-W%W'),
|
||
'week_start': item['week'].strftime('%Y-%m-%d'),
|
||
'lessons_count': item['lessons_count'],
|
||
'revenue': float(item['revenue'] or 0),
|
||
'avg_duration': round(item['avg_duration'] or 0, 1),
|
||
'avg_grade': round(item['avg_grade'] or 0, 1),
|
||
}
|
||
for item in data
|
||
]
|
||
|
||
elif group_by == 'month':
|
||
# Группируем по месяцам
|
||
data = lessons.extra(
|
||
select={'month': "DATE_TRUNC('month', start_time AT TIME ZONE 'UTC')"}
|
||
).values('month').annotate(
|
||
lessons_count=Count('id'),
|
||
revenue=Sum('price', filter=Q(price__isnull=False)),
|
||
avg_duration=Avg('duration'),
|
||
avg_grade=Avg('mentor_grade', filter=Q(mentor_grade__isnull=False))
|
||
).order_by('month')
|
||
|
||
return [
|
||
{
|
||
'month': item['month'].strftime('%Y-%m'),
|
||
'month_name': item['month'].strftime('%B %Y'),
|
||
'lessons_count': item['lessons_count'],
|
||
'revenue': float(item['revenue'] or 0),
|
||
'avg_duration': round(item['avg_duration'] or 0, 1),
|
||
'avg_grade': round(item['avg_grade'] or 0, 1),
|
||
}
|
||
for item in data
|
||
]
|
||
|
||
return []
|
||
|
||
@staticmethod
|
||
def get_comparison_data(mentor, current_start, current_end, previous_start, previous_end):
|
||
"""
|
||
Сравнение текущего периода с предыдущим.
|
||
"""
|
||
from apps.schedule.models import Lesson
|
||
|
||
# Текущий период
|
||
current_lessons = Lesson.objects.filter(
|
||
mentor=mentor,
|
||
start_time__gte=current_start,
|
||
start_time__lte=current_end,
|
||
status='completed'
|
||
)
|
||
|
||
current_stats = {
|
||
'lessons_count': current_lessons.count(),
|
||
'revenue': float(
|
||
current_lessons.filter(price__isnull=False).aggregate(
|
||
total=Sum('price')
|
||
)['total'] or Decimal('0')
|
||
),
|
||
'avg_duration': current_lessons.aggregate(avg=Avg('duration'))['avg'] or 0,
|
||
'avg_grade': current_lessons.filter(mentor_grade__isnull=False).aggregate(
|
||
avg=Avg('mentor_grade')
|
||
)['avg'] or 0,
|
||
}
|
||
|
||
# Предыдущий период
|
||
previous_lessons = Lesson.objects.filter(
|
||
mentor=mentor,
|
||
start_time__gte=previous_start,
|
||
start_time__lte=previous_end,
|
||
status='completed'
|
||
)
|
||
|
||
previous_stats = {
|
||
'lessons_count': previous_lessons.count(),
|
||
'revenue': float(
|
||
previous_lessons.filter(price__isnull=False).aggregate(
|
||
total=Sum('price')
|
||
)['total'] or Decimal('0')
|
||
),
|
||
'avg_duration': previous_lessons.aggregate(avg=Avg('duration'))['avg'] or 0,
|
||
'avg_grade': previous_lessons.filter(mentor_grade__isnull=False).aggregate(
|
||
avg=Avg('mentor_grade')
|
||
)['avg'] or 0,
|
||
}
|
||
|
||
# Вычисляем изменения
|
||
def calculate_change(current, previous):
|
||
if previous == 0:
|
||
return 100.0 if current > 0 else 0.0
|
||
return round(((current - previous) / previous) * 100, 1)
|
||
|
||
return {
|
||
'current': current_stats,
|
||
'previous': previous_stats,
|
||
'changes': {
|
||
'lessons_count': calculate_change(
|
||
current_stats['lessons_count'],
|
||
previous_stats['lessons_count']
|
||
),
|
||
'revenue': calculate_change(
|
||
current_stats['revenue'],
|
||
previous_stats['revenue']
|
||
),
|
||
'avg_duration': calculate_change(
|
||
current_stats['avg_duration'],
|
||
previous_stats['avg_duration']
|
||
),
|
||
'avg_grade': calculate_change(
|
||
current_stats['avg_grade'],
|
||
previous_stats['avg_grade']
|
||
),
|
||
},
|
||
}
|
||
|
||
@staticmethod
|
||
def get_overview_data(mentor, start_date, end_date):
|
||
"""
|
||
Получить данные для overview отчета.
|
||
|
||
Args:
|
||
mentor: Пользователь-ментор
|
||
start_date: Начало периода
|
||
end_date: Конец периода
|
||
|
||
Returns:
|
||
dict: Данные overview
|
||
"""
|
||
from apps.schedule.models import Lesson
|
||
from apps.users.models import Client
|
||
from apps.homework.models import Homework, HomeworkSubmission
|
||
|
||
# Занятия
|
||
lessons = Lesson.objects.filter(
|
||
mentor=mentor,
|
||
start_time__gte=start_date,
|
||
start_time__lte=end_date
|
||
)
|
||
|
||
total_lessons = lessons.count()
|
||
completed_lessons = lessons.filter(status='completed').count()
|
||
cancelled_lessons = lessons.filter(status='cancelled').count()
|
||
|
||
# Доходы
|
||
total_revenue = lessons.filter(
|
||
status='completed',
|
||
price__isnull=False
|
||
).aggregate(total=Sum('price'))['total'] or Decimal('0')
|
||
|
||
# Ученики
|
||
active_students = Client.objects.filter(
|
||
mentors=mentor
|
||
).count()
|
||
|
||
# Домашние задания
|
||
homeworks = Homework.objects.filter(
|
||
mentor=mentor,
|
||
created_at__gte=start_date,
|
||
created_at__lte=end_date
|
||
)
|
||
|
||
total_homeworks = homeworks.count()
|
||
pending_submissions = HomeworkSubmission.objects.filter(
|
||
homework__mentor=mentor,
|
||
status='pending'
|
||
).count()
|
||
|
||
# Средний балл
|
||
average_grade = lessons.filter(
|
||
status='completed',
|
||
mentor_grade__isnull=False
|
||
).aggregate(avg=Avg('mentor_grade'))['avg'] or 0
|
||
|
||
return {
|
||
'period': {
|
||
'start': start_date.isoformat(),
|
||
'end': end_date.isoformat(),
|
||
},
|
||
'lessons': {
|
||
'total': total_lessons,
|
||
'completed': completed_lessons,
|
||
'cancelled': cancelled_lessons,
|
||
},
|
||
'revenue': {
|
||
'total': float(total_revenue),
|
||
'average_per_lesson': float(total_revenue / completed_lessons) if completed_lessons > 0 else 0,
|
||
},
|
||
'students': {
|
||
'active': active_students,
|
||
},
|
||
'homeworks': {
|
||
'total': total_homeworks,
|
||
'pending': pending_submissions,
|
||
},
|
||
'grades': {
|
||
'average': round(average_grade, 1),
|
||
},
|
||
}
|
||
|
||
@staticmethod
|
||
def get_revenue_data(mentor, start_date, end_date):
|
||
"""
|
||
Получить данные для revenue отчета.
|
||
|
||
Args:
|
||
mentor: Пользователь-ментор
|
||
start_date: Начало периода
|
||
end_date: Конец периода
|
||
|
||
Returns:
|
||
dict: Данные revenue
|
||
"""
|
||
from apps.schedule.models import Lesson
|
||
|
||
# Доходы по дням
|
||
lessons = Lesson.objects.filter(
|
||
mentor=mentor,
|
||
start_time__gte=start_date,
|
||
start_time__lte=end_date,
|
||
status='completed',
|
||
price__isnull=False
|
||
)
|
||
|
||
# Группируем по дням
|
||
revenue_by_day = lessons.extra(
|
||
select={'day': "DATE(start_time AT TIME ZONE 'UTC')"}
|
||
).values('day').annotate(
|
||
revenue=Sum('price'),
|
||
lessons_count=Count('id')
|
||
).order_by('day')
|
||
|
||
# Доходы по предметам
|
||
revenue_by_subject = lessons.values('subject__name').annotate(
|
||
revenue=Sum('price', filter=Q(price__isnull=False)),
|
||
lessons_count=Count('id')
|
||
).order_by('-revenue')[:5]
|
||
|
||
total_revenue = lessons.aggregate(total=Sum('price'))['total'] or Decimal('0')
|
||
total_lessons = lessons.count()
|
||
|
||
return {
|
||
'total_revenue': float(total_revenue),
|
||
'total_lessons': total_lessons,
|
||
'average_per_lesson': float(total_revenue / total_lessons) if total_lessons > 0 else 0,
|
||
'by_day': [
|
||
{
|
||
'date': item['day'].strftime('%Y-%m-%d'),
|
||
'revenue': float(item['revenue']),
|
||
'lessons_count': item['lessons_count'],
|
||
}
|
||
for item in revenue_by_day
|
||
],
|
||
'by_subject': [
|
||
{
|
||
'subject': item['subject__name'] or 'Без предмета',
|
||
'revenue': float(item['revenue'] or 0),
|
||
'lessons_count': item['lessons_count'],
|
||
}
|
||
for item in revenue_by_subject
|
||
],
|
||
}
|
||
|