339 lines
15 KiB
Python
339 lines
15 KiB
Python
from flask import Blueprint, jsonify, request
|
|
from app.utils.jwt_decorators import jwt_required
|
|
from app.models.classroom import Classroom
|
|
from app.models.subject import Subject
|
|
from app.models.user import User
|
|
from app.models.reservation import Reservation
|
|
from app.models.audit_log import AuditLog
|
|
from datetime import datetime, timedelta
|
|
from sqlalchemy import func
|
|
from app import db
|
|
|
|
from app.models.milestone import AcademicMilestone
|
|
from app.models.subject import Commission
|
|
|
|
api_dashboard_bp = Blueprint('api_dashboard', __name__, url_prefix='/api/v1/dashboard')
|
|
|
|
def serialize_reservation(r):
|
|
subject_name = (r.commission.subject.name if (r.commission and r.commission.subject) else r.purpose) or 'Actividad Académica'
|
|
subject_code = (r.commission.subject.code if (r.commission and r.commission.subject) else '')
|
|
classroom_code = r.classroom.code if r.classroom else 'Sin Aula'
|
|
is_virtual = ('virtual' in classroom_code.lower()) or (getattr(r.classroom, 'is_virtual', False) if r.classroom else False)
|
|
return {
|
|
'id': r.id,
|
|
'start_time': r.start_time.strftime('%H:%M') if r.start_time else '',
|
|
'end_time': r.end_time.strftime('%H:%M') if r.end_time else '',
|
|
'date_display': r.start_time.strftime('%d/%m') if r.start_time else '',
|
|
'classroom': {
|
|
'id': r.classroom_id,
|
|
'code': classroom_code,
|
|
'is_virtual': is_virtual,
|
|
'location_display': getattr(r.classroom, 'location_display', f'Sede Central · {classroom_code}') if r.classroom else 'Sin Aula'
|
|
},
|
|
'subject_name': subject_name,
|
|
'subject_code': subject_code,
|
|
'shift': r.shift or 'Mañana',
|
|
'commission': {
|
|
'id': r.commission_id,
|
|
'code': r.commission.code if r.commission else 'COM-1'
|
|
},
|
|
'status': str(r.status.value if hasattr(r.status, 'value') else r.status),
|
|
'user': {
|
|
'id': r.user_id,
|
|
'name': r.user.name if r.user else 'Docente'
|
|
},
|
|
'virtual_link': r.virtual_link or ('https://meet.google.com/edu-space-' + classroom_code.lower().replace(' ', '-') if is_virtual else '')
|
|
}
|
|
|
|
def serialize_milestone(m):
|
|
return {
|
|
'id': m.id,
|
|
'title': m.title,
|
|
'materia_codigo': m.materia_codigo,
|
|
'subject_name': m.subject.name if (hasattr(m, 'subject') and m.subject) else m.materia_codigo,
|
|
'date': m.date.strftime('%d/%m/%Y') if m.date else '',
|
|
'type_obj': {
|
|
'name': m.type_obj.name if m.type_obj else 'Evaluación',
|
|
'color': m.type_obj.color if m.type_obj else '#0d6efd'
|
|
},
|
|
'classroom': m.classroom or 'Sede Central',
|
|
'link': m.link or '',
|
|
'notes': m.notes or ''
|
|
}
|
|
|
|
@api_dashboard_bp.route('/stats', methods=['GET'])
|
|
@jwt_required
|
|
def get_stats():
|
|
"""Retorna estadísticas clave y datos operativos para el Dashboard."""
|
|
today = datetime.utcnow().date()
|
|
|
|
try:
|
|
total_classrooms = Classroom.query.filter_by(is_active=True).count()
|
|
total_subjects = Subject.query.filter_by(is_active=True).count()
|
|
total_users = User.query.filter_by(is_active=True).count()
|
|
|
|
# Aulas presenciales y virtuales
|
|
all_active_classrooms = Classroom.query.filter_by(is_active=True).all()
|
|
virtual_classrooms_list = [
|
|
c for c in all_active_classrooms
|
|
if getattr(c, 'is_virtual', False)
|
|
or 'virtual' in (c.building or '').lower()
|
|
or 'virtual' in (c.code or '').lower()
|
|
]
|
|
virtual_classrooms = len(virtual_classrooms_list)
|
|
|
|
physical_classrooms_list = [
|
|
c for c in all_active_classrooms
|
|
if c not in virtual_classrooms_list
|
|
]
|
|
total_physical_count = len(physical_classrooms_list)
|
|
physical_classrooms = total_physical_count
|
|
physical_ids = set(c.id for c in physical_classrooms_list)
|
|
|
|
# 1. Asignación General del Cuatrimestre / Cronograma
|
|
assigned_all_ids = set(
|
|
r[0] for r in db.session.query(Reservation.classroom_id)
|
|
.filter(Reservation.classroom_id.isnot(None)).distinct().all()
|
|
)
|
|
assigned_physical_ids = physical_ids.intersection(assigned_all_ids)
|
|
assigned_classrooms = len(assigned_physical_ids)
|
|
unassigned_classrooms = max(0, total_physical_count - assigned_classrooms)
|
|
|
|
assigned_pct = round((assigned_classrooms / max(1, total_physical_count)) * 100, 1) if total_physical_count > 0 else 0.0
|
|
unassigned_pct = round(100.0 - assigned_pct, 1) if total_physical_count > 0 else 0.0
|
|
|
|
# 2. Asignación Operativa del Día de Hoy
|
|
assigned_today_ids = set(
|
|
r[0] for r in db.session.query(Reservation.classroom_id).filter(
|
|
Reservation.classroom_id.isnot(None),
|
|
func.date(Reservation.start_time) == today
|
|
).distinct().all()
|
|
)
|
|
assigned_today_physical_ids = physical_ids.intersection(assigned_today_ids)
|
|
assigned_today_count = len(assigned_today_physical_ids)
|
|
unassigned_today_count = max(0, total_physical_count - assigned_today_count)
|
|
|
|
assigned_today_pct = round((assigned_today_count / max(1, total_physical_count)) * 100, 1) if total_physical_count > 0 else 0.0
|
|
unassigned_today_pct = round(100.0 - assigned_today_pct, 1) if total_physical_count > 0 else 0.0
|
|
|
|
today_reservations_q = Reservation.query.filter(
|
|
func.date(Reservation.start_time) == today
|
|
).order_by(Reservation.start_time.asc()).all()
|
|
today_reservations = len(today_reservations_q)
|
|
|
|
pending_reservations_q = Reservation.query.filter(
|
|
Reservation.status == 'PENDING'
|
|
).order_by(Reservation.start_time.asc()).all()
|
|
pending_count = len(pending_reservations_q)
|
|
|
|
upcoming_reservations_q = Reservation.query.filter(
|
|
Reservation.start_time >= datetime.utcnow()
|
|
).order_by(Reservation.start_time.asc()).limit(10).all()
|
|
|
|
milestones_q = AcademicMilestone.query.filter(
|
|
AcademicMilestone.is_active == True
|
|
).order_by(AcademicMilestone.date.asc()).limit(10).all()
|
|
|
|
recent_audits = AuditLog.query.order_by(AuditLog.created_at.desc()).limit(6).all()
|
|
audit_data = [{
|
|
'created_at': a.created_at.strftime('%Y-%m-%d %H:%M') if a.created_at else None,
|
|
'action_type': a.action_type,
|
|
'ip_address': a.ip_address,
|
|
'user_email': getattr(a.user, 'email', 'System') if hasattr(a, 'user') else 'System'
|
|
} for a in recent_audits]
|
|
|
|
occupancy_rate = min(100, int((today_reservations / max(1, physical_classrooms * 3)) * 100)) if physical_classrooms > 0 else 0
|
|
|
|
pending_reservations = [serialize_reservation(r) for r in pending_reservations_q]
|
|
today_schedule_list = [serialize_reservation(r) for r in today_reservations_q]
|
|
upcoming_reservations = [serialize_reservation(r) for r in upcoming_reservations_q]
|
|
milestones = [serialize_milestone(m) for m in milestones_q]
|
|
|
|
# ---- Bedelía: Comisiones sin aula asignada (semana y mes) ----
|
|
import calendar
|
|
# Calcular rango de semana actual (lunes a domingo)
|
|
today_dt = datetime.utcnow()
|
|
week_start = today_dt - timedelta(days=today_dt.weekday()) # lunes
|
|
week_start = week_start.replace(hour=0, minute=0, second=0, microsecond=0)
|
|
week_end = week_start + timedelta(days=6, hours=23, minutes=59, seconds=59)
|
|
|
|
# Calcular rango del mes actual
|
|
month_start = today_dt.replace(day=1, hour=0, minute=0, second=0, microsecond=0)
|
|
last_day = calendar.monthrange(today_dt.year, today_dt.month)[1]
|
|
month_end = today_dt.replace(day=last_day, hour=23, minute=59, second=59, microsecond=0)
|
|
|
|
# Comisiones activas totales
|
|
all_active_commissions = Commission.query.filter_by(active=True).all()
|
|
total_active_commissions = len(all_active_commissions)
|
|
|
|
# IDs de comisiones que SÍ tienen reserva en la semana
|
|
comm_ids_with_res_week = set(
|
|
r[0] for r in db.session.query(Reservation.commission_id).filter(
|
|
Reservation.commission_id.isnot(None),
|
|
Reservation.start_time >= week_start,
|
|
Reservation.start_time <= week_end
|
|
).distinct().all()
|
|
)
|
|
# IDs de comisiones que SÍ tienen reserva en el mes
|
|
comm_ids_with_res_month = set(
|
|
r[0] for r in db.session.query(Reservation.commission_id).filter(
|
|
Reservation.commission_id.isnot(None),
|
|
Reservation.start_time >= month_start,
|
|
Reservation.start_time <= month_end
|
|
).distinct().all()
|
|
)
|
|
|
|
unassigned_commissions_week = []
|
|
unassigned_commissions_month = []
|
|
for comm in all_active_commissions:
|
|
comm_data = {
|
|
'id': comm.id,
|
|
'code': comm.code,
|
|
'full_code': comm.get_full_code(),
|
|
'subject_name': comm.subject.name if comm.subject else 'Sin materia',
|
|
'subject_code': comm.subject.code if comm.subject else '',
|
|
'teacher_name': comm.teacher_name,
|
|
'schedule': comm.schedule or 'Sin horario',
|
|
'shift': comm.shift or 'Sin turno',
|
|
'max_students': comm.max_students,
|
|
'current_students': comm.current_students
|
|
}
|
|
if comm.id not in comm_ids_with_res_week:
|
|
unassigned_commissions_week.append(comm_data)
|
|
if comm.id not in comm_ids_with_res_month:
|
|
unassigned_commissions_month.append(comm_data)
|
|
|
|
# ---- Bedelía: Aulas disponibles (sin reservas) en la semana y mes ----
|
|
classroom_ids_used_week = set(
|
|
r[0] for r in db.session.query(Reservation.classroom_id).filter(
|
|
Reservation.classroom_id.isnot(None),
|
|
Reservation.start_time >= week_start,
|
|
Reservation.start_time <= week_end
|
|
).distinct().all()
|
|
)
|
|
classroom_ids_used_month = set(
|
|
r[0] for r in db.session.query(Reservation.classroom_id).filter(
|
|
Reservation.classroom_id.isnot(None),
|
|
Reservation.start_time >= month_start,
|
|
Reservation.start_time <= month_end
|
|
).distinct().all()
|
|
)
|
|
|
|
available_classrooms_week = []
|
|
available_classrooms_month = []
|
|
for c in all_active_classrooms:
|
|
c_data = {
|
|
'id': c.id,
|
|
'code': c.code,
|
|
'building': c.building or 'Sin edificio',
|
|
'floor': c.floor or '',
|
|
'capacity': c.capacity,
|
|
'is_virtual': getattr(c, 'is_virtual', False) or 'virtual' in (c.building or '').lower() or 'virtual' in (c.code or '').lower()
|
|
}
|
|
if c.id not in classroom_ids_used_week:
|
|
available_classrooms_week.append(c_data)
|
|
if c.id not in classroom_ids_used_month:
|
|
available_classrooms_month.append(c_data)
|
|
|
|
except Exception as e:
|
|
print(f"Error fetching stats: {e}")
|
|
total_classrooms = 0
|
|
total_subjects = 0
|
|
total_users = 0
|
|
today_reservations = 0
|
|
pending_count = 0
|
|
physical_classrooms = 0
|
|
virtual_classrooms = 0
|
|
occupancy_rate = 0
|
|
assigned_classrooms = 0
|
|
assigned_pct = 0.0
|
|
unassigned_classrooms = 0
|
|
unassigned_pct = 0.0
|
|
assigned_today_count = 0
|
|
assigned_today_pct = 0.0
|
|
unassigned_today_count = 0
|
|
unassigned_today_pct = 0.0
|
|
audit_data = []
|
|
pending_reservations = []
|
|
today_schedule_list = []
|
|
upcoming_reservations = []
|
|
unassigned_commissions_week = []
|
|
unassigned_commissions_month = []
|
|
available_classrooms_week = []
|
|
available_classrooms_month = []
|
|
total_active_commissions = 0
|
|
milestones = []
|
|
|
|
# Mocked monthly data for chart since complex SQL group by month might differ based on dialect (SQLite vs Postgres)
|
|
monthly_data = [
|
|
{"month": "Ene", "reservations": 120},
|
|
{"month": "Feb", "reservations": 180},
|
|
{"month": "Mar", "reservations": 250},
|
|
{"month": "Abr", "reservations": 220},
|
|
{"month": "May", "reservations": 300},
|
|
{"month": "Jun", "reservations": 310},
|
|
{"month": "Jul", "reservations": 200},
|
|
{"month": "Ago", "reservations": 350},
|
|
{"month": "Sep", "reservations": 420},
|
|
{"month": "Oct", "reservations": 480},
|
|
{"month": "Nov", "reservations": 520},
|
|
{"month": "Dic", "reservations": 210}
|
|
]
|
|
|
|
return jsonify({
|
|
'status': 'success',
|
|
'stats': {
|
|
'total_classrooms': total_classrooms,
|
|
'active_subjects': total_subjects,
|
|
'total_active_users': total_users,
|
|
'today_reservations': today_reservations,
|
|
'pending_count': pending_count,
|
|
'physical_classrooms': physical_classrooms,
|
|
'virtual_classrooms': virtual_classrooms,
|
|
'occupancy_rate': occupancy_rate,
|
|
'assigned_classrooms': assigned_classrooms,
|
|
'assigned_classrooms_pct': assigned_pct,
|
|
'unassigned_classrooms': unassigned_classrooms,
|
|
'unassigned_classrooms_pct': unassigned_pct,
|
|
'assigned_today_classrooms': assigned_today_count,
|
|
'assigned_today_pct': assigned_today_pct,
|
|
'unassigned_today_classrooms': unassigned_today_count,
|
|
'unassigned_today_pct': unassigned_today_pct,
|
|
'total_active_commissions': total_active_commissions
|
|
},
|
|
'audit_logs': audit_data,
|
|
'monthly_data': monthly_data,
|
|
'pending_reservations': pending_reservations,
|
|
'today_schedule_list': today_schedule_list,
|
|
'upcoming_reservations': upcoming_reservations,
|
|
'milestones': milestones,
|
|
'bedelia': {
|
|
'unassigned_commissions_week': unassigned_commissions_week,
|
|
'unassigned_commissions_month': unassigned_commissions_month,
|
|
'available_classrooms_week': available_classrooms_week,
|
|
'available_classrooms_month': available_classrooms_month
|
|
}
|
|
}), 200
|
|
|
|
@api_dashboard_bp.route('/sync-sheets', methods=['POST'])
|
|
@jwt_required
|
|
def sync_google_sheets():
|
|
"""Ejecuta la sincronización real con las hojas de cálculo de Google Sheets."""
|
|
from app.services.sheets_importer import GoogleSheetsImporter
|
|
try:
|
|
data = request.get_json(silent=True) or {}
|
|
custom_url = data.get('sheets_url') or data.get('base_url')
|
|
importer = GoogleSheetsImporter(base_url=custom_url)
|
|
stats = importer.sync()
|
|
return jsonify({
|
|
'status': 'success',
|
|
'stats': stats,
|
|
'source_url': importer.base_url
|
|
}), 200
|
|
except Exception as e:
|
|
return jsonify({
|
|
'status': 'error',
|
|
'message': str(e)
|
|
}), 500
|
|
|