480 lines
20 KiB
Python
480 lines
20 KiB
Python
import urllib.request
|
|
import csv
|
|
import io
|
|
import re
|
|
import os
|
|
from datetime import datetime, date, time, timedelta
|
|
from app import db
|
|
from app.models.classroom import Classroom
|
|
from app.models.career import Career
|
|
from app.models.subject import Subject, Commission
|
|
from app.models.reservation import Reservation, ReservationStatus
|
|
from app.models.user import User
|
|
|
|
BASE_CSV_URL = 'https://docs.google.com/spreadsheets/d/e/2PACX-1vSc_T_BQjbn3uPelioCgx52UM5Py-qNhJN0TYPd1kmsN5jdb3Q8rAaIvNMF_2ZTzQt6bH--yIWQKrKR/pub?single=true&output=csv'
|
|
|
|
def normalize_sheets_url(url):
|
|
"""
|
|
Normaliza enlaces de Google Sheets (compartidos, de edición o publicados)
|
|
para obtener la URL base que acepta parámetro gid=<gid>&output=csv o format=csv.
|
|
"""
|
|
if not url:
|
|
return BASE_CSV_URL
|
|
url = url.strip()
|
|
|
|
# 1. Enlace publicado web (/d/e/2PACX-.../pub...)
|
|
if '/d/e/' in url:
|
|
base_match = re.match(r'^(https://docs\.google\.com/spreadsheets/d/e/[^/?#]+)/pub', url)
|
|
if base_match:
|
|
return f"{base_match.group(1)}/pub?single=true&output=csv"
|
|
clean = url.split('&gid=')[0].split('?gid=')[0]
|
|
sep = '&' if '?' in clean else '?'
|
|
if 'output=csv' not in clean:
|
|
clean = f"{clean}{sep}single=true&output=csv"
|
|
return clean
|
|
|
|
# 2. Enlace de edición o visualización (/d/<SPREADSHEET_ID>/edit...)
|
|
sheet_id_match = re.search(r'/spreadsheets/d/([a-zA-Z0-9-_]+)', url)
|
|
if sheet_id_match:
|
|
sheet_id = sheet_id_match.group(1)
|
|
return f"https://docs.google.com/spreadsheets/d/{sheet_id}/export?format=csv"
|
|
|
|
return url
|
|
|
|
SHEETS_CONFIG = [
|
|
{ 'name': 'Lunes', 'gid': '1915752353', 'day_idx': 0 },
|
|
{ 'name': 'Martes', 'gid': '466778263', 'day_idx': 1 },
|
|
{ 'name': 'Miércoles', 'gid': '615483345', 'day_idx': 2 },
|
|
{ 'name': 'Jueves', 'gid': '682416571', 'day_idx': 3 },
|
|
{ 'name': 'Viernes', 'gid': '1162906711', 'day_idx': 4 },
|
|
{ 'name': 'Sábado', 'gid': '752305601', 'day_idx': 5 },
|
|
]
|
|
|
|
TIME_REGEX = re.compile(r'(\d{1,2})(?::(\d{2}))?\s*(?:a|-|to)\s*(\d{1,2})(?::(\d{2}))?', re.IGNORECASE)
|
|
|
|
class GoogleSheetsImporter:
|
|
"""
|
|
Service to fetch, parse, and synchronize academic schedules,
|
|
classrooms, careers, and subjects from the official UniCABA Google Sheet.
|
|
"""
|
|
|
|
def __init__(self, base_url=None):
|
|
if base_url:
|
|
self.base_url = normalize_sheets_url(base_url)
|
|
else:
|
|
configured_url = None
|
|
try:
|
|
from app.models.setting import SystemSetting
|
|
configured_url = SystemSetting.get_value('google_sheets_url')
|
|
except Exception:
|
|
pass
|
|
if not configured_url:
|
|
configured_url = os.getenv('GOOGLE_SHEETS_URL')
|
|
self.base_url = normalize_sheets_url(configured_url) if configured_url else BASE_CSV_URL
|
|
|
|
def fetch_sheet_csv(self, gid):
|
|
"""Fetch CSV string for a specific sheet gid"""
|
|
separator = '&' if '?' in self.base_url else '?'
|
|
url = f"{self.base_url}{separator}gid={gid}"
|
|
if not (url.startswith('https://') or url.startswith('http://')):
|
|
raise ValueError(f"URL scheme not permitted: {url}")
|
|
req = urllib.request.Request(
|
|
url,
|
|
headers={'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) EduSpace/2.0'}
|
|
)
|
|
with urllib.request.urlopen(req, timeout=25) as resp: # nosec B310
|
|
return resp.read().decode('utf-8', errors='replace')
|
|
|
|
def parse_sheet_rows(self, csv_content, sheet_name):
|
|
"""Parse raw CSV text into a structured list of class records"""
|
|
reader = csv.reader(io.StringIO(csv_content))
|
|
rows = list(reader)
|
|
|
|
header_idx = -1
|
|
col_offset = 0
|
|
|
|
for r_idx, row in enumerate(rows):
|
|
for c_idx, cell in enumerate(row):
|
|
cell_clean = cell.lower().strip()
|
|
if 'codigo' in cell_clean or 'código' in cell_clean:
|
|
header_idx = r_idx
|
|
col_offset = c_idx
|
|
break
|
|
if header_idx != -1:
|
|
break
|
|
|
|
if header_idx == -1:
|
|
return []
|
|
|
|
records = []
|
|
current_shift = 'Mañana'
|
|
for row in rows[header_idx + 1:]:
|
|
if len(row) >= col_offset + 5:
|
|
raw_cell = row[col_offset].strip().replace('\n', ' - ')
|
|
row_full_text = ' '.join(row).upper()
|
|
|
|
# Detect shift delimiters
|
|
if 'TURNO MAÑANA' in row_full_text or 'TURNO MANANA' in row_full_text:
|
|
current_shift = 'Mañana'
|
|
continue
|
|
elif 'TURNO VESPERTINO' in row_full_text or 'TURNO TARDE' in row_full_text or 'TURNO NOCHE' in row_full_text:
|
|
current_shift = 'Tarde'
|
|
continue
|
|
elif raw_cell.lower().startswith('turno'):
|
|
if 'mañ' in raw_cell.lower() or 'man' in raw_cell.lower():
|
|
current_shift = 'Mañana'
|
|
else:
|
|
current_shift = 'Tarde'
|
|
continue
|
|
|
|
codigo = raw_cell
|
|
asignatura = row[col_offset + 1].strip().replace('\n', ' ')
|
|
aula = row[col_offset + 2].strip().replace('\n', ' ')
|
|
carrera = row[col_offset + 3].strip().replace('\n', ' ')
|
|
horario = row[col_offset + 4].strip().replace('\n', ' ')
|
|
|
|
# Check valid data row
|
|
if codigo and asignatura and not 'codigo' in codigo.lower():
|
|
# Double-check shift from start hour if possible
|
|
record_shift = current_shift
|
|
time_match = re.search(r'(\d{1,2}):(\d{2})', horario)
|
|
if time_match:
|
|
h = int(time_match.group(1))
|
|
if h >= 14:
|
|
record_shift = 'Tarde'
|
|
elif h < 13:
|
|
record_shift = 'Mañana'
|
|
|
|
records.append({
|
|
'dia': sheet_name,
|
|
'codigo': codigo,
|
|
'asignatura': asignatura,
|
|
'aula': aula,
|
|
'carrera': carrera,
|
|
'horario': horario,
|
|
'turno': record_shift
|
|
})
|
|
return records
|
|
|
|
def normalize_room(self, aula_raw):
|
|
"""
|
|
Normalize room string into (building, room_number, floor, capacity)
|
|
"""
|
|
aula_upper = aula_raw.upper().strip()
|
|
if not aula_upper or any(x in aula_upper for x in ['FERIADO', 'SIN ACTIVIDAD', 'CONSULTAR EN BEDELIA']):
|
|
return None
|
|
|
|
if 'VIRTUAL' in aula_upper:
|
|
return {
|
|
'building': 'Campus Virtual',
|
|
'code': 'VIRTUAL',
|
|
'floor': 0,
|
|
'capacity': 0,
|
|
'description': 'Espacio áulico virtual UniCABA (Capacidad Ilimitada ∞)'
|
|
}
|
|
|
|
if 'AUDITORIO' in aula_upper:
|
|
return {
|
|
'building': 'Edificio Central',
|
|
'code': 'Auditorio',
|
|
'floor': 0,
|
|
'capacity': 150,
|
|
'description': 'Auditorio principal UniCABA'
|
|
}
|
|
|
|
if 'SALA DE REUNI' in aula_upper:
|
|
floor = 2 if ('2DO' in aula_upper or '2°' in aula_upper) else 4
|
|
return {
|
|
'building': 'Edificio Central',
|
|
'code': f'Sala Reunión {floor}°P',
|
|
'floor': floor,
|
|
'capacity': 15,
|
|
'description': f'Sala de reunión piso {floor}'
|
|
}
|
|
|
|
# Standard classrooms: "Aula 402", "AULA 203", "306 - 406"
|
|
num_match = re.search(r'\d+', aula_raw)
|
|
if num_match:
|
|
num = num_match.group(0)
|
|
floor = int(num[0]) if len(num) >= 3 else 1
|
|
return {
|
|
'building': 'Edificio Central',
|
|
'code': f'Aula {num}',
|
|
'floor': floor,
|
|
'capacity': 35,
|
|
'description': f'Aula física {num} - Piso {floor}'
|
|
}
|
|
|
|
# Generic fallback
|
|
return {
|
|
'building': 'Edificio Central',
|
|
'code': aula_raw,
|
|
'floor': 1,
|
|
'capacity': 30,
|
|
'description': aula_raw
|
|
}
|
|
|
|
def parse_time_range(self, horario_str):
|
|
"""Parse '8:00 a 9:00hs' into (time(8, 0), time(9, 0))"""
|
|
m = TIME_REGEX.search(horario_str)
|
|
if not m:
|
|
return None, None
|
|
try:
|
|
h1 = int(m.group(1))
|
|
m1 = int(m.group(2) or 0)
|
|
h2 = int(m.group(3))
|
|
m2 = int(m.group(4) or 0)
|
|
return time(h1, m1), time(h2, m2)
|
|
except Exception:
|
|
return None, None
|
|
|
|
def sync(self):
|
|
"""
|
|
Main synchronization process:
|
|
1. Fetches all 6 day sheets.
|
|
2. Upserts Careers, Classrooms, Subjects, Commissions.
|
|
3. Generates Reservations in the calendar for the semester.
|
|
"""
|
|
stats = {
|
|
'total_rows_processed': 0,
|
|
'careers_created': 0,
|
|
'careers_updated': 0,
|
|
'classrooms_created': 0,
|
|
'classrooms_updated': 0,
|
|
'subjects_created': 0,
|
|
'subjects_updated': 0,
|
|
'commissions_created': 0,
|
|
'reservations_created': 0,
|
|
'errors': [],
|
|
'sample_records': []
|
|
}
|
|
|
|
all_raw_records = []
|
|
for sheet_cfg in SHEETS_CONFIG:
|
|
try:
|
|
csv_data = self.fetch_sheet_csv(sheet_cfg['gid'])
|
|
rows = self.parse_sheet_rows(csv_data, sheet_cfg['name'])
|
|
all_raw_records.extend([(r, sheet_cfg['day_idx']) for r in rows])
|
|
except Exception as e:
|
|
stats['errors'].append(f"Error al descargar o procesar la hoja de {sheet_cfg['name']}: {str(e)}")
|
|
|
|
stats['total_rows_processed'] = len(all_raw_records)
|
|
if not all_raw_records:
|
|
return stats
|
|
|
|
# Default admin user for reservations
|
|
default_user = User.query.filter_by(role='ADMIN').first() or User.query.first()
|
|
default_user_id = default_user.id if default_user else 1
|
|
|
|
# Ensure institutional buildings exist with official address
|
|
from app.models.building import Building
|
|
b_central = Building.query.filter_by(name='Edificio Central').first()
|
|
if not b_central:
|
|
b_central = Building(
|
|
name='Edificio Central',
|
|
code='CENTRAL',
|
|
address='Tte. Gral. Juan Domingo Perón 802, CABA',
|
|
floors=4,
|
|
description='Sede central institucional de UniCABA. Aulas de grado, auditorio y laboratorios.'
|
|
)
|
|
db.session.add(b_central)
|
|
db.session.flush()
|
|
else:
|
|
b_central.address = 'Tte. Gral. Juan Domingo Perón 802, CABA'
|
|
|
|
b_virtual = Building.query.filter_by(name='Campus Virtual').first()
|
|
if not b_virtual:
|
|
b_virtual = Building(
|
|
name='Campus Virtual',
|
|
code='VIRTUAL',
|
|
address='Entorno Digital / Microsoft Teams - Moodle',
|
|
floors=0,
|
|
description='Entorno de aulas virtuales y clases a distancia'
|
|
)
|
|
db.session.add(b_virtual)
|
|
db.session.flush()
|
|
|
|
buildings_cache = {b.name.strip().upper(): b for b in Building.query.all()}
|
|
careers_cache = {c.name.strip().upper(): c for c in Career.query.all()}
|
|
classrooms_cache = {(c.building.strip().upper(), c.code.strip().upper()): c for c in Classroom.query.all()}
|
|
subjects_cache = {s.code.strip().upper(): s for s in Subject.query.all()}
|
|
|
|
# Calculate current semester dates (anchored on academic year 2026)
|
|
# Week starting Monday 2026-08-31 to Saturday 2026-09-05
|
|
base_monday = date(2026, 8, 31)
|
|
weeks_to_generate = 4 # Generate reservations for 4 consecutive weeks
|
|
|
|
for rec, day_idx in all_raw_records:
|
|
try:
|
|
codigo = rec['codigo']
|
|
asignatura = rec['asignatura']
|
|
aula_raw = rec['aula']
|
|
carrera_name = rec['carrera'].strip()
|
|
horario_raw = rec['horario']
|
|
dia = rec['dia']
|
|
turno = rec.get('turno', 'Mañana')
|
|
|
|
# 1. Upsert Career
|
|
carrera_key = carrera_name.upper()
|
|
career = careers_cache.get(carrera_key)
|
|
if not career:
|
|
career = Career(name=carrera_name)
|
|
db.session.add(career)
|
|
db.session.flush()
|
|
careers_cache[carrera_key] = career
|
|
stats['careers_created'] += 1
|
|
|
|
# 2. Upsert Classroom
|
|
room_info = self.normalize_room(aula_raw)
|
|
classroom = None
|
|
if room_info:
|
|
room_key = (room_info['building'].upper(), room_info['code'].upper())
|
|
classroom = classrooms_cache.get(room_key)
|
|
b_entity = buildings_cache.get(room_info['building'].upper())
|
|
if not classroom:
|
|
classroom = Classroom(
|
|
building=room_info['building'],
|
|
building_id=b_entity.id if b_entity else None,
|
|
floor=room_info['floor'],
|
|
capacity=room_info['capacity'],
|
|
description=room_info['description'],
|
|
is_active=True
|
|
)
|
|
classroom.code = room_info['code']
|
|
db.session.add(classroom)
|
|
db.session.flush()
|
|
classrooms_cache[room_key] = classroom
|
|
stats['classrooms_created'] += 1
|
|
else:
|
|
classroom.capacity = max(classroom.capacity, room_info['capacity'])
|
|
if not classroom.building_id and b_entity:
|
|
classroom.building_id = b_entity.id
|
|
|
|
# 3. Upsert Subject
|
|
subject_key = codigo.upper()
|
|
subject = subjects_cache.get(subject_key)
|
|
if not subject:
|
|
subject = Subject(
|
|
code=codigo,
|
|
name=asignatura,
|
|
career_id=career.id,
|
|
department=career.name,
|
|
credits=4,
|
|
is_active=True
|
|
)
|
|
db.session.add(subject)
|
|
db.session.flush()
|
|
subjects_cache[subject_key] = subject
|
|
stats['subjects_created'] += 1
|
|
else:
|
|
if subject.name != asignatura:
|
|
subject.name = asignatura
|
|
stats['subjects_updated'] += 1
|
|
subject.career_id = career.id
|
|
subject.department = career.name
|
|
|
|
# 4. Upsert Commission
|
|
# Identify commission subcode e.g. from code suffix or default 'C1'
|
|
comm_code = 'C1'
|
|
if '-E' in codigo:
|
|
comm_code = codigo.split('-')[-1]
|
|
|
|
commission = Commission.query.filter_by(
|
|
subject_id=subject.id,
|
|
code=comm_code,
|
|
semester='2026-2',
|
|
year=2026
|
|
).first()
|
|
|
|
if not commission:
|
|
commission = Commission(
|
|
subject_id=subject.id,
|
|
code=comm_code,
|
|
semester='2026-2',
|
|
year=2026,
|
|
teacher_id=default_user_id,
|
|
max_students=classroom.capacity if classroom else 35,
|
|
current_students=0,
|
|
schedule=f"{dia} {horario_raw}",
|
|
shift=turno,
|
|
active=True
|
|
)
|
|
db.session.add(commission)
|
|
db.session.flush()
|
|
stats['commissions_created'] += 1
|
|
else:
|
|
commission.schedule = f"{dia} {horario_raw}"
|
|
commission.shift = turno
|
|
|
|
# 5. Generate Calendar Reservations
|
|
t_start, t_end = self.parse_time_range(horario_raw)
|
|
if classroom and t_start and t_end and day_idx is not None:
|
|
is_room_virtual = bool(classroom.is_virtual or 'VIRTUAL' in (classroom.code or '').upper())
|
|
|
|
# Generar enlace de videollamada para clases virtuales si no tienen uno asignado
|
|
v_link = None
|
|
if is_room_virtual:
|
|
clean_subj = re.sub(r'[^a-zA-Z0-9]', '', subject.code or 'cls').lower()
|
|
clean_comm = re.sub(r'[^a-zA-Z0-9]', '', commission.code or 'c1').lower()
|
|
v_link = f"https://meet.google.com/edu-{clean_subj}-{clean_comm}"
|
|
if not commission.virtual_link:
|
|
commission.virtual_link = v_link
|
|
|
|
for w in range(weeks_to_generate):
|
|
class_date = base_monday + timedelta(days=day_idx, weeks=w)
|
|
dt_start = datetime.combine(class_date, t_start)
|
|
dt_end = datetime.combine(class_date, t_end)
|
|
|
|
# En aulas virtuales la capacidad es infinita y pueden cursar múltiples comisiones en paralelo
|
|
if is_room_virtual:
|
|
existing_res = Reservation.query.filter_by(
|
|
classroom_id=classroom.id,
|
|
commission_id=commission.id,
|
|
start_time=dt_start,
|
|
end_time=dt_end
|
|
).first()
|
|
else:
|
|
existing_res = Reservation.query.filter_by(
|
|
classroom_id=classroom.id,
|
|
start_time=dt_start,
|
|
end_time=dt_end
|
|
).first()
|
|
|
|
if not existing_res:
|
|
res = Reservation(
|
|
classroom_id=classroom.id,
|
|
commission_id=commission.id,
|
|
user_id=default_user_id,
|
|
start_time=dt_start,
|
|
end_time=dt_end,
|
|
purpose=f"{subject.code} - {subject.name}",
|
|
status='CONFIRMED',
|
|
expected_attendees=min(classroom.capacity, 30) if not is_room_virtual else 35,
|
|
shift=turno,
|
|
virtual_link=v_link,
|
|
notes=f"Clase semanal {dia} ({turno}) - Carrera: {career.name}"
|
|
)
|
|
db.session.add(res)
|
|
stats['reservations_created'] += 1
|
|
else:
|
|
existing_res.shift = turno
|
|
existing_res.notes = f"Clase semanal {dia} ({turno}) - Carrera: {career.name}"
|
|
if is_room_virtual and not existing_res.virtual_link:
|
|
existing_res.virtual_link = v_link
|
|
|
|
if len(stats['sample_records']) < 10:
|
|
stats['sample_records'].append({
|
|
'dia': dia,
|
|
'codigo': codigo,
|
|
'asignatura': asignatura,
|
|
'carrera': career.name,
|
|
'aula': classroom.code_display if classroom else aula_raw,
|
|
'horario': horario_raw
|
|
})
|
|
|
|
except Exception as row_err:
|
|
stats['errors'].append(f"Error en fila '{rec.get('codigo', '?')}': {str(row_err)}")
|
|
|
|
db.session.commit()
|
|
return stats
|