from datetime import date from typing import Optional from fastapi import APIRouter, Depends, HTTPException, Query, Response from sqlalchemy.orm import Session from openpyxl import Workbook from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from io import BytesIO from database.connection import get_db from database.models import User, UserPresence, DailyParkingAssignment, UserRole, Office from utils.auth_middleware import require_manager_or_admin router = APIRouter(prefix="/api/reports", tags=["reports"]) @router.get("/team-export") def export_team_data( start_date: date, end_date: date, office_id: Optional[str] = None, db: Session = Depends(get_db), current_user: User = Depends(require_manager_or_admin) ): """ Export team presence and parking data to Excel. """ # 1. Determine Scope (Admin vs Manager) target_office_id = office_id if current_user.role == UserRole.MANAGER: # Manager is restricted to their own office if office_id and office_id != current_user.office_id: raise HTTPException(status_code=403, detail="Cannot export data for other offices") target_office_id = current_user.office_id # 2. Fetch Users query = db.query(User) if target_office_id: query = query.filter(User.office_id == target_office_id) users = query.all() user_ids = [u.id for u in users] # Map users for quick lookup user_map = {u.id: u for u in users} # 3. Fetch Presences presences = db.query(UserPresence).filter( UserPresence.user_id.in_(user_ids), UserPresence.date >= start_date, UserPresence.date <= end_date ).all() # 4. Fetch Parking Assignments assignments = db.query(DailyParkingAssignment).filter( DailyParkingAssignment.user_id.in_(user_ids), DailyParkingAssignment.date >= start_date, DailyParkingAssignment.date <= end_date ).all() # Organize data by Date -> User -> Info # Structure: data_map[date_str][user_id] = { presence: ..., parking: ... } data_map = {} for p in presences: d_str = p.date.isoformat() if d_str not in data_map: data_map[d_str] = {} if p.user_id not in data_map[d_str]: data_map[d_str][p.user_id] = {} data_map[d_str][p.user_id]['presence'] = p.status.value # 'present', 'remote', etc. for a in assignments: d_str = a.date.isoformat() if d_str not in data_map: data_map[d_str] = {} if a.user_id not in data_map[d_str]: data_map[d_str][a.user_id] = {} if a.spot: data_map[d_str][a.user_id]['parking'] = a.spot.name else: data_map[d_str][a.user_id]['parking'] = "Unknown" # 5. Generate Excel wb = Workbook() ws = wb.active ws.title = "Report Presenze Matrix" # --- Header Row (Dates) --- # Column A: "Utente" ws.cell(row=1, column=1, value="Utente") # Generate date range from datetime import timedelta date_cols = {} # date_str -> col_index col_idx = 2 curr = start_date while curr <= end_date: d_str = curr.isoformat() # Header: DD/MM header_val = f"{curr.day}/{curr.month}" cell = ws.cell(row=1, column=col_idx, value=header_val) date_cols[d_str] = col_idx # Style Header cell.font = Font(bold=True) cell.alignment = Alignment(horizontal="center") col_idx += 1 curr += timedelta(days=1) # Style First Header (Utente) first_header = ws.cell(row=1, column=1) first_header.font = Font(bold=True) first_header.alignment = Alignment(horizontal="left") # Define Fills fill_present = PatternFill(start_color="dcfce7", end_color="dcfce7", fill_type="solid") # Light Green fill_remote = PatternFill(start_color="dbeafe", end_color="dbeafe", fill_type="solid") # Light Blue fill_absent = PatternFill(start_color="fee2e2", end_color="fee2e2", fill_type="solid") # Light Red fill_trip = PatternFill(start_color="fef3c7", end_color="fef3c7", fill_type="solid") # Light Orange (matching frontend warning-bg) # --- User Rows --- row_idx = 2 for user in users: # User Name in Col 1 name_cell = ws.cell(row=row_idx, column=1, value=user.name) name_cell.font = Font(bold=True) # Determine Office label (optional append?) # name_cell.value = f"{user.name} ({user.office.name})" if user.office else user.name # Fill Dates curr = start_date while curr <= end_date: d_str = curr.isoformat() if d_str in date_cols: c_idx = date_cols[d_str] # Get Data u_data = data_map.get(d_str, {}).get(user.id, {}) presence = u_data.get('presence', '') parking = u_data.get('parking', '') cell_val = "" fill = None if presence == 'present': cell_val = "In Sede" fill = fill_present elif presence == 'remote': cell_val = "Remoto" fill = fill_remote elif presence == 'absent': cell_val = "Ferie" fill = fill_absent elif presence == 'business_trip': cell_val = "Trasferta" fill = fill_trip # Append Parking info if present if parking: if cell_val: cell_val += f" ({parking})" else: cell_val = f"({parking})" # Parking without presence? Unusual but possible cell = ws.cell(row=row_idx, column=c_idx, value=cell_val) if fill: cell.fill = fill cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) curr += timedelta(days=1) row_idx += 1 # Adjust column widths ws.column_dimensions['A'].width = 25 # User name column # Auto-width for date columns (approx) for i in range(2, col_idx): col_letter = ws.cell(row=1, column=i).column_letter ws.column_dimensions[col_letter].width = 12 # Save to buffer output = BytesIO() wb.save(output) output.seek(0) filename = f"report_parking_matrix_{start_date}_{end_date}.xlsx" headers = { 'Content-Disposition': f'attachment; filename="{filename}"' } return Response( content=output.getvalue(), media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", headers=headers )