"""
CLEAN MINIMAL SALES DASHBOARD - Start from scratch
Focus: Get the data RIGHT first, then add features
"""
from flask import Flask, session, redirect, url_for, request, jsonify
from flask_login import LoginManager, UserMixin, login_user, logout_user, login_required, current_user
from google_auth_oauthlib.flow import Flow
from google.oauth2.credentials import Credentials
from google.auth.transport.requests import Request
import gspread
import pandas as pd
import os
import re
from urllib.parse import urlencode, quote_plus
from datetime import timedelta, datetime
# ============================================================================
# CONFIG
# ============================================================================
app = Flask(__name__)
app.secret_key = 'e7ac3a5f4f6e4d0d8c3b7a5e9f2a1b0c9e8d7c6b5a4f3e2d1c0b9a8f7e6d5c4b'
app.config['SESSION_COOKIE_SECURE'] = False
app.config['SESSION_COOKIE_HTTPONLY'] = True
app.permanent_session_lifetime = timedelta(minutes=60)
os.environ['OAUTHLIB_INSECURE_TRANSPORT'] = '1'
CLIENT_SECRETS_FILE = "credentials.json"
DEFAULT_SPREADSHEET_ID = "1ayEGU0h_R7CY55COC1U94-p0rJch109YBGvezjYjHWw"
# Role-based access control
# Roles: admin, viewer, comercial
USERS_ROLES = {
# Admins - access to all information, can edit data
"de.globalerc@gmail.com": "admin",
"op.globalerc@gmail.com": "admin",
"df.globalerc@gmail.com": "admin",
# Viewers - view-only access to all data
"adm.globalerc@gmail.com": "viewer",
# Comercials - restricted view of their own data only
"joseamor.globalerc@gmail.com": "comercial",
"helderoliveira.globalerc@gmail.com": "comercial",
}
# Legacy access map for backward compatibility with comercial role filtering
SALES_ACCESS_MAP = {
"joseamor.globalerc@gmail.com": ["José Amor"],
"helderoliveira.globalerc@gmail.com": ["Hélder Oliveira"],
}
# Commission rates by comercial
COMMISSION_RATES = {
"José Amor": 0.085,
"Hélder Oliveira": 0.07,
}
SCOPES = [
'https://www.googleapis.com/auth/spreadsheets',
'https://www.googleapis.com/auth/drive',
'https://www.googleapis.com/auth/userinfo.email',
'https://www.googleapis.com/auth/userinfo.profile',
'openid'
]
REDIRECT_URI = 'https://regulative-clotilde-subflexuously.ngrok-free.dev/oauth2callback'
login_manager = LoginManager()
login_manager.init_app(app)
login_manager.login_view = 'login'
users = {}
def normalize_email(user_email):
if not user_email:
return None
return user_email.strip().lower()
# Helper function to get user role
def get_user_role(user_email):
"""Return user role: 'admin', 'viewer', 'comercial', or None if not found"""
return USERS_ROLES.get(normalize_email(user_email))
# Helper function to check permissions
def has_permission(user_email, permission):
"""
Check if user has a specific permission
Permissions: 'view_all', 'edit_data', 'view_own'
"""
role = get_user_role(user_email)
if role == 'admin':
return permission in ['view_all', 'edit_data', 'view_own']
elif role == 'viewer':
return permission in ['view_all', 'view_own']
elif role == 'comercial':
return permission in ['view_own']
return False
# ============================================================================
# USER MODEL
# ============================================================================
class User(UserMixin):
def __init__(self, id_):
self.id = id_
self.email = id_
self.role = get_user_role(id_)
@login_manager.user_loader
def load_user(user_id):
return users.get(user_id)
# ============================================================================
# AUTH HELPERS
# ============================================================================
def get_flow():
flow = Flow.from_client_secrets_file(
CLIENT_SECRETS_FILE,
scopes=SCOPES,
redirect_uri=REDIRECT_URI
)
# Request offline access to get refresh token
flow.client_config['access_type'] = 'offline'
return flow
def get_google_credentials():
print(f"[CREDS] Checking session... has 'credentials'? {'credentials' in session}")
if 'credentials' not in session:
print("[CREDS] ERROR: No credentials in session")
return None
print("[CREDS] Found credentials in session")
creds_data = session['credentials']
print(f"[CREDS] Has refresh_token? {bool(creds_data.get('refresh_token'))}")
print(f"[CREDS] Token: {creds_data.get('token', 'NONE')[:20] if creds_data.get('token') else 'NONE'}...")
creds = Credentials.from_authorized_user_info(info=creds_data)
print(f"[CREDS] Valid: {creds.valid}, Expired: {creds.expired}, Has refresh_token: {bool(creds.refresh_token)}")
if not creds.valid:
print("[CREDS] Credentials not valid")
if creds.expired and creds.refresh_token:
try:
print("[CREDS] Attempting to refresh...")
creds.refresh(Request())
session['credentials'] = {
'token': creds.token,
'refresh_token': creds.refresh_token,
'token_uri': creds.token_uri,
'client_id': creds.client_id,
'client_secret': creds.client_secret,
'scopes': creds.scopes
}
print("[CREDS] Credentials refreshed successfully")
except Exception as e:
print(f"[CREDS] Error refreshing: {e}")
return None
else:
print("[CREDS] Cannot refresh - either not expired or no refresh_token. Need re-login.")
return None
print("[CREDS] Credentials are valid and ready to use")
return creds
# ============================================================================
# DATA HELPERS - SIMPLE & CLEAN
# ============================================================================
def parse_number(value):
"""Parse numbers from Google Sheets - handle European format."""
if pd.isna(value) or value == '' or value is None:
return 0.0
if isinstance(value, (int, float)):
return float(value)
# Convert to string
s = str(value).strip()
if not s or s.lower() in ['nan', 'none', 'null', '']:
return 0.0
# Remove currency symbols and spaces
s = s.replace('€', '').replace('$', '').replace(' ', '').strip()
# European format: 6.005.182,69 -> 6005182.69
# US format: 6,005,182.69 -> 6005182.69
# Simple format: 6005182.69 or 6005182,69
# Count dots and commas
dots = s.count('.')
commas = s.count(',')
if dots > 0 and commas > 0:
# Both exist - last one is decimal separator
last_dot_pos = s.rfind('.')
last_comma_pos = s.rfind(',')
if last_comma_pos > last_dot_pos:
# European: dots=thousands, comma=decimal
s = s.replace('.', '').replace(',', '.')
else:
# US: commas=thousands, dot=decimal
s = s.replace(',', '')
elif commas > 0:
# Only commas
parts = s.split(',')
if len(parts) == 2 and len(parts[1]) == 2:
# Decimal comma: 1234,56
s = s.replace(',', '.')
else:
# Thousands: 1,234 or multiple commas
s = s.replace(',', '')
# else: only dots or nothing - leave as is
try:
return float(s)
except:
print(f"[PARSE ERROR] Could not parse: '{value}' -> '{s}'")
return 0.0
def fetch_data():
"""Fetch data from Google Sheets - use PANDAS for parsing only."""
print("\n[FETCH] Starting data fetch...")
creds = get_google_credentials()
if not creds:
print("[FETCH] ERROR: No credentials")
return None
print("[FETCH] Credentials OK")
SPREADSHEET_ID = session.get('spreadsheet_id') or DEFAULT_SPREADSHEET_ID
if not SPREADSHEET_ID:
print("[FETCH] ERROR: No spreadsheet ID available")
return None
print(f"[FETCH] Spreadsheet ID: {SPREADSHEET_ID}")
try:
gc = gspread.authorize(creds)
spreadsheet = gc.open_by_key(SPREADSHEET_ID)
# Get sheet "BASE" (index 1)
worksheet = spreadsheet.worksheets()[1]
print(f"[DATA] Reading from sheet: {worksheet.title}")
# Get all data
all_values = worksheet.get_all_values()
headers = all_values[0]
data_rows = all_values[1:]
# Create DataFrame
df = pd.DataFrame(data_rows, columns=headers)
print(f"[DATA] Loaded {len(df)} rows")
print(f"[DATA] Columns: {df.columns.tolist()}")
# CRITICAL FILTER: Only include "Funerária" in Tipo de Cliente (Column A)
tipo_cliente_col = None
for col in df.columns:
if 'tipo' in col.lower() and 'cliente' in col.lower():
tipo_cliente_col = col
break
if tipo_cliente_col:
print(f"[DATA] Found 'Tipo de Cliente' column: '{tipo_cliente_col}'")
print(f"[DATA] Before filter: {len(df)} rows")
print(f"[DATA] Unique values in Tipo de Cliente: {df[tipo_cliente_col].unique()[:10]}")
# Filter to only "Funerária"
df = df[df[tipo_cliente_col].astype(str).str.strip().str.lower() == 'funerária'].copy()
print(f"[DATA] After 'Funerária' filter: {len(df)} rows")
else:
print(f"[DATA] WARNING: 'Tipo de Cliente' column not found. Using all data.")
print(f"[DATA] ALL column names with 'fatura': {[col for col in df.columns if 'fatura' in col.lower()]}")
# Find and parse Faturaçao column using ONLY PANDAS
fat_col = None
for col in df.columns:
if 'fatura' in col.lower():
fat_col = col
break
if fat_col:
print(f"\n[DATA] Found Faturaçao column: '{fat_col}'")
print(f"[DATA] First 10 RAW values: {df[fat_col].head(10).tolist()}")
# Show samples from the MIDDLE and END of the data
mid_point = len(df) // 2
print(f"[DATA] Middle 10 RAW values (row {mid_point}): {df[fat_col].iloc[mid_point:mid_point+10].tolist()}")
print(f"[DATA] Last 10 RAW values: {df[fat_col].tail(10).tolist()}")
# Robust parse for European currency strings
def parse_euro(value):
if value is None:
return None
s = str(value).strip()
if s == "":
return None
s = s.replace("\u00A0", " ") # normalize NBSP
# Keep digits, separators, and minus sign only
s = re.sub(r"[^0-9,\.\-]", "", s)
if s in {"", "-"}:
return None
last_comma = s.rfind(",")
last_dot = s.rfind(".")
if last_comma != -1 and last_dot != -1:
# Both present: last separator is decimal
if last_comma > last_dot:
s = s.replace(".", "")
s = s.replace(",", ".")
else:
s = s.replace(",", "")
elif last_comma != -1:
# Only comma present
parts = s.split(",")
if len(parts) == 2 and len(parts[1]) == 2:
s = s.replace(",", ".")
else:
s = s.replace(",", "")
elif last_dot != -1:
# Only dot present
parts = s.split(".")
if len(parts) == 2 and len(parts[1]) == 2:
pass
else:
s = s.replace(".", "")
try:
return float(s)
except:
return None
parsed_values = df[fat_col].apply(parse_euro)
# Diagnostics: show parsed samples and failures
print(f"[DATA] First 20 PARSED values: {parsed_values.head(20).tolist()}")
failure_mask = parsed_values.isna() & df[fat_col].astype(str).str.strip().ne("")
if failure_mask.any():
failed_samples = df.loc[failure_mask, fat_col].head(20).tolist()
print(f"[DATA] Example FAILED raw values: {failed_samples}")
df[fat_col] = parsed_values
total = df[fat_col].sum()
null_count = df[fat_col].isna().sum()
non_null_count = len(df) - null_count
print(f"[DATA] Total Faturação: €{total:,.2f}")
print(f"[DATA] Null values: {null_count}")
print(f"[DATA] Non-null values: {non_null_count}")
# Parse Quantidade
quant_col = None
for col in df.columns:
if 'quant' in col.lower():
quant_col = col
break
if quant_col:
df[quant_col] = pd.to_numeric(df[quant_col], errors='coerce')
return df
except Exception as e:
print(f"[ERROR] Failed to fetch data: {e}")
import traceback
traceback.print_exc()
return None
# ============================================================================
# OBJECTIVES MANAGEMENT (Sales Targets)
# ============================================================================
def fetch_objectives():
"""Fetch objectives from 'Objetivos' sheet."""
print("\n[OBJECTIVES] Fetching objectives...")
try:
creds = get_google_credentials()
if not creds:
print("[OBJECTIVES] No credentials")
return None
SPREADSHEET_ID = session.get('spreadsheet_id') or DEFAULT_SPREADSHEET_ID
gc = gspread.authorize(creds)
spreadsheet = gc.open_by_key(SPREADSHEET_ID)
# Find the "Objetivos" sheet
worksheet = None
for sheet in spreadsheet.worksheets():
if sheet.title.lower() == 'objetivos':
worksheet = sheet
break
if not worksheet:
print("[OBJECTIVES] 'Objetivos' sheet not found")
return None
all_values = worksheet.get_all_values()
if len(all_values) < 2:
print("[OBJECTIVES] Empty Objetivos sheet")
return None
headers = all_values[0]
data_rows = all_values[1:]
df = pd.DataFrame(data_rows, columns=headers)
print(f"[OBJECTIVES] Loaded {len(df)} objectives")
return df
except Exception as e:
print(f"[OBJECTIVES] Error: {e}")
return None
def calculate_performance(comercial_name, period_type='annual', period_value=None):
"""
Calculate performance vs objectives for a comercial.
Args:
comercial_name: Name of the comercial (e.g., "Jose Amor")
period_type: 'annual', 'quarterly', 'monthly'
period_value: Year (2025), Quarter (Q1 2025), Month (01/2025), or None for current
Returns:
{
'comercial': str,
'period': str,
'total_revenue_target': float,
'total_revenue_actual': float,
'revenue_achievement_pct': float,
'total_urnas_target': float,
'total_urnas_actual': float,
'urnas_achievement_pct': float,
'by_client': [
{
'client': str,
'revenue_target': float,
'revenue_actual': float,
'revenue_pct': float,
'urnas_target': float,
'urnas_actual': float,
'urnas_pct': float
}
]
}
"""
df_sales = fetch_data()
df_objectives = fetch_objectives()
if df_sales is None or df_objectives is None:
return None
# Find columns
def find_col(*keywords):
for col in df_sales.columns:
name = col.lower()
if all(k in name for k in keywords):
return col
return None
comercial_col = find_col('comercial')
cliente_col = find_col('cliente')
fat_col = find_col('fatura')
quant_col = find_col('quant')
zona_col = find_col('zona')
familia_col = find_col('familia') or find_col('família')
mes_col = find_col('mês') or find_col('mes')
# Filter by scope
if comercial_name == 'TOTAL':
comercial_data = df_sales.copy()
elif comercial_name == 'EXPORTAÇÃO':
if zona_col:
comercial_data = df_sales[df_sales[zona_col].astype(str).str.lower().str.contains('export', na=False)].copy()
else:
comercial_data = df_sales.iloc[0:0].copy()
else:
comercial_data = df_sales[df_sales[comercial_col] == comercial_name].copy()
if comercial_data.empty:
return None
# Parse year/month for period filtering and target calc
if mes_col:
def parse_period(value):
if value is None:
return (None, None)
s = str(value).strip()
if not s:
return (None, None)
s = s.replace('-', '/').replace('.', '/')
m = re.search(r"(\d{4})\D?(\d{1,2})", s)
if m:
year = m.group(1)
month = m.group(2).zfill(2)
return (year, month)
m = re.search(r"(\d{1,2})\D?(\d{4})", s)
if m:
month = m.group(1).zfill(2)
year = m.group(2)
return (year, month)
return (None, None)
ym = comercial_data[mes_col].apply(parse_period)
comercial_data['__year'] = ym.apply(lambda x: x[0])
comercial_data['__month'] = ym.apply(lambda x: x[1])
# Filter objectives by comercial/scope
if df_objectives is not None and not df_objectives.empty and 'Comercial' in df_objectives.columns:
obj_data = df_objectives[df_objectives['Comercial'] == comercial_name].copy()
else:
obj_data = pd.DataFrame()
# Ensure numeric columns
if fat_col:
comercial_data[fat_col] = pd.to_numeric(comercial_data[fat_col], errors='coerce')
if quant_col:
comercial_data[quant_col] = pd.to_numeric(comercial_data[quant_col], errors='coerce')
# Calculate for both previous year (for baseline) and current year (for progress)
prev_year = None
current_year = str(datetime.now().year) # 2026
# Get previous year data (for baseline comparison)
prev_year_data = comercial_data.copy()
if '__year' in comercial_data.columns:
years = sorted([y for y in comercial_data['__year'].dropna().unique() if str(y).isdigit()])
if years:
prev_year = years[-1] # Most recent year in data
prev_year_data = comercial_data[comercial_data['__year'] == prev_year]
# Get current year data (for current progress)
current_year_data = comercial_data[comercial_data['__year'] == current_year] if '__year' in comercial_data.columns else pd.DataFrame()
# Calculate previous year totals (baseline)
prev_revenue = prev_year_data[fat_col].sum() if fat_col and not prev_year_data.empty else 0
prev_urnas_data = prev_year_data.copy()
if familia_col and not prev_year_data.empty:
prev_urnas_data = prev_year_data[prev_year_data[familia_col].astype(str).str.lower().str.contains('urna', na=False)]
prev_urnas_qty = prev_urnas_data[quant_col].sum() if quant_col and not prev_urnas_data.empty else 0
# Calculate current year totals (current progress)
current_revenue = current_year_data[fat_col].sum() if fat_col and not current_year_data.empty else 0
current_urnas_data = current_year_data.copy()
if familia_col and not current_year_data.empty:
current_urnas_data = current_year_data[current_year_data[familia_col].astype(str).str.lower().str.contains('urna', na=False)]
current_urnas_qty = current_urnas_data[quant_col].sum() if quant_col and not current_year_data.empty else 0
# Get objectives for this period (simple approach: use first matching period)
total_revenue_target = 0
total_urnas_target = 0
if not obj_data.empty and 'Cliente' in obj_data.columns:
# Find row with 'Total' client or matching period
total_obj = obj_data[obj_data['Cliente'] == 'Total']
if not total_obj.empty:
# Handle European locale (comma as decimal separator)
rev_str = str(total_obj.iloc[0].get('Target_Valor', 0) or 0).strip()
urnas_str = str(total_obj.iloc[0].get('Target_Urnas', 0) or 0).strip()
# Replace comma with period for float conversion
rev_str = rev_str.replace(',', '.')
urnas_str = urnas_str.replace(',', '.')
try:
total_revenue_target = float(rev_str)
except (ValueError, TypeError):
total_revenue_target = 0
try:
total_urnas_target = float(urnas_str)
except (ValueError, TypeError):
total_urnas_target = 0
# Calculate achievement percentages (current year vs target)
revenue_achievement_pct = (current_revenue / total_revenue_target * 100) if total_revenue_target > 0 else 0
urnas_achievement_pct = (current_urnas_qty / total_urnas_target * 100) if total_urnas_target > 0 else 0
# Calculate how much % growth needed to reach target from current position
revenue_to_target_pct = ((total_revenue_target - current_revenue) / current_revenue * 100) if current_revenue > 0 else 0
urnas_to_target_pct = ((total_urnas_target - current_urnas_qty) / current_urnas_qty * 100) if current_urnas_qty > 0 else 0
# Calculate historical data for previous 3 years (for trend chart)
historical_data = []
if mes_col and mes_col in df_sales.columns:
# Apply same scope filter using existing data (avoid refetch)
if comercial_name == 'TOTAL':
scope_data = df_sales.copy()
elif comercial_name == 'EXPORTAÇÃO':
if zona_col:
scope_data = df_sales[df_sales[zona_col].astype(str).str.lower().str.contains('export', na=False)].copy()
else:
scope_data = df_sales.iloc[0:0].copy()
else:
scope_data = df_sales[df_sales[comercial_col] == comercial_name].copy()
ym = scope_data[mes_col].apply(parse_period)
scope_data['__year'] = ym.apply(lambda x: x[0])
# Ensure numeric
if fat_col:
scope_data[fat_col] = pd.to_numeric(scope_data[fat_col], errors='coerce')
if quant_col:
scope_data[quant_col] = pd.to_numeric(scope_data[quant_col], errors='coerce')
# Get last 3 years available (>= 2022)
all_years = sorted([y for y in scope_data['__year'].dropna().unique() if str(y).isdigit() and int(y) >= 2022])
last_3_years = all_years[-3:] if len(all_years) >= 3 else all_years
prev_revenue = None
prev_avg_total = None
prev_avg_urnas = None
for year in sorted(last_3_years):
year_data = scope_data[scope_data['__year'] == year]
year_revenue = year_data[fat_col].sum() if fat_col else 0
year_clients = year_data[cliente_col].nunique() if cliente_col else 0
# URNAS for this year
year_urnas = year_data.copy()
if familia_col:
year_urnas = year_data[year_data[familia_col].astype(str).str.lower().str.contains('urna', na=False)]
year_urnas_rev = year_urnas[fat_col].sum() if fat_col else 0
year_urnas_qty = year_urnas[quant_col].sum() if quant_col else 0
# Calculate averages
avg_per_urna_total = (year_revenue / year_urnas_qty) if year_urnas_qty else 0
avg_per_urna_urnas = (year_urnas_rev / year_urnas_qty) if year_urnas_qty else 0
# Revenue growth
if prev_revenue is None:
growth_text = "—"
growth_pct = None
else:
diff = year_revenue - prev_revenue
growth_pct = (diff / prev_revenue * 100) if prev_revenue else 0
growth_text = f"€{diff:,.2f} ({growth_pct:+.1f}%)"
# Avg Total growth
if prev_avg_total is None or prev_avg_total == 0:
growth_avg_total_text = "—"
growth_avg_total_pct = None
else:
growth_avg_total_pct = ((avg_per_urna_total - prev_avg_total) / prev_avg_total * 100)
growth_avg_total_text = f"{growth_avg_total_pct:+.1f}%"
# Avg URNAS growth
if prev_avg_urnas is None or prev_avg_urnas == 0:
growth_avg_urnas_text = "—"
growth_avg_urnas_pct = None
else:
growth_avg_urnas_pct = ((avg_per_urna_urnas - prev_avg_urnas) / prev_avg_urnas * 100)
growth_avg_urnas_text = f"{growth_avg_urnas_pct:+.1f}%"
historical_data.append({
'year': year,
'revenue': round(year_revenue, 2),
'urnas': round(year_urnas_qty, 0),
'clients': year_clients,
'avg_per_urna_total': round(avg_per_urna_total, 2),
'avg_per_urna_urnas': round(avg_per_urna_urnas, 2),
'growth_text': growth_text,
'growth_pct': growth_pct,
'growth_avg_total_text': growth_avg_total_text,
'growth_avg_total_pct': growth_avg_total_pct,
'growth_avg_urnas_text': growth_avg_urnas_text,
'growth_avg_urnas_pct': growth_avg_urnas_pct
})
prev_revenue = year_revenue
prev_avg_total = avg_per_urna_total
prev_avg_urnas = avg_per_urna_urnas
# Client-level objectives disabled (sales force only)
by_client = []
commission_rate = COMMISSION_RATES.get(comercial_name, 0)
commission_value = current_revenue * commission_rate if current_revenue else 0
return {
'comercial': comercial_name,
'period': period_value or 'Annual',
'total_revenue_target': round(total_revenue_target, 2),
'total_revenue_prev_year': round(prev_revenue, 2), # Previous year (baseline)
'total_revenue_current': round(current_revenue, 2), # Current year (progress)
'revenue_achievement_pct': round(revenue_achievement_pct, 1), # Current vs target
'revenue_to_target_pct': round(revenue_to_target_pct, 1), # % growth needed
'total_urnas_target': round(total_urnas_target, 0),
'total_urnas_prev_year': round(prev_urnas_qty, 0), # Previous year (baseline)
'total_urnas_current': round(current_urnas_qty, 0), # Current year (progress)
'urnas_achievement_pct': round(urnas_achievement_pct, 1), # Current vs target
'urnas_to_target_pct': round(urnas_to_target_pct, 1), # % growth needed
'commission_rate': commission_rate,
'commission_value': round(commission_value, 2),
'historical_data': historical_data,
'by_client': by_client
}
# ============================================================================
# ROUTES
# ============================================================================
@app.route('/')
def index():
if current_user.is_authenticated:
return redirect(url_for('dashboard'))
return redirect(url_for('login'))
@app.route('/login')
def login():
print("[LOGIN] Starting login flow...")
flow = get_flow()
authorization_url, state = flow.authorization_url(
access_type='offline',
prompt='consent'
)
print(f"[LOGIN] Authorization URL: {authorization_url[:80]}...")
session['state'] = state
return redirect(authorization_url)
@app.route('/oauth2callback')
def oauth2callback():
print("\n[AUTH] OAuth2 callback received")
flow = get_flow()
flow.fetch_token(authorization_response=request.url)
credentials = flow.credentials
print(f"[AUTH] Got credentials: {credentials.token[:20]}...")
session['credentials'] = {
'token': credentials.token,
'refresh_token': credentials.refresh_token,
'token_uri': credentials.token_uri,
'client_id': credentials.client_id,
'client_secret': credentials.client_secret,
'scopes': credentials.scopes
}
session.permanent = True
print(f"[AUTH] Saved credentials to session")
# Get user info
import requests
user_info = requests.get(
'https://www.googleapis.com/oauth2/v1/userinfo',
headers={'Authorization': f'Bearer {credentials.token}'}
).json()
session['user_email'] = normalize_email(user_info.get('email'))
session['user_name'] = user_info.get('name') or user_info.get('given_name')
user_id = user_info['id']
user = User(user_id)
users[user_id] = user
login_user(user, remember=True)
print(f"[AUTH] User logged in: {user_id} ({session.get('user_email')})")
return redirect(url_for('dashboard'))
@app.route('/logout')
@login_required
def logout():
logout_user()
session.clear()
return redirect(url_for('login'))
@app.route('/test-data')
@login_required
def test_data():
"""Debug endpoint to test data loading."""
print("\n" + "="*80)
print("[TEST] Testing data load...")
print("="*80)
df = fetch_data()
if df is None:
print("[TEST] fetch_data() returned None")
return jsonify({'error': 'fetch_data returned None', 'session_id': session.get('spreadsheet_id')}), 400
print("[TEST] fetch_data() succeeded!")
# Find columns
fat_col = None
quant_col = None
for col in df.columns:
if 'fatura' in col.lower() and not fat_col:
fat_col = col
if 'quant' in col.lower() and not quant_col:
quant_col = col
# Get totals
total_fat_all = df[fat_col].sum() if fat_col else 0
total_fat_2025 = 0
mes_col = None
for col in df.columns:
if 'mês' in col.lower() or 'mes' in col.lower():
mes_col = col
break
if mes_col and fat_col:
total_fat_2025 = df[df[mes_col].str.contains('2025', na=False)][fat_col].sum()
result = {
'total_rows': len(df),
'total_faturacao_all': float(total_fat_all),
'total_faturacao_2025': float(total_fat_2025),
'looker_studio_expected': 6005182.69,
'match': abs(float(total_fat_2025) - 6005182.69) < 1,
'columns_found': {
'faturaçao': fat_col,
'quantidade': quant_col,
'mes': mes_col
}
}
print(f"[TEST] Result: {result}")
return jsonify(result)
@app.route('/raw-data')
@login_required
def raw_data():
"""Show completely raw data - no parsing, no filtering."""
print("\n" + "="*80)
print("[RAW] Loading COMPLETELY RAW data from sheet...")
print("="*80)
creds = get_google_credentials()
if not creds:
return jsonify({'error': 'No credentials'}), 401
SPREADSHEET_ID = session.get('spreadsheet_id')
if not SPREADSHEET_ID:
return jsonify({'error': 'No spreadsheet ID'}), 400
try:
gc = gspread.authorize(creds)
spreadsheet = gc.open_by_key(SPREADSHEET_ID)
# Get sheet BASE (index 1)
worksheet = spreadsheet.worksheets()[1]
print(f"[RAW] Sheet name: {worksheet.title}")
print(f"[RAW] Sheet size: {worksheet.row_count} rows x {worksheet.col_count} cols")
# Get ALL values
all_values = worksheet.get_all_values()
print(f"[RAW] get_all_values() returned {len(all_values)} rows")
headers = all_values[0]
print(f"\n[RAW] Column headers ({len(headers)} cols):")
for i, h in enumerate(headers):
print(f"[RAW] [{i}] '{h}'")
# Find Faturaçao column
fat_col_idx = None
fat_col_name = None
for i, h in enumerate(headers):
if 'fatura' in h.lower():
fat_col_idx = i
fat_col_name = h
break
if fat_col_idx is None:
print("[RAW] ERROR: Faturaçao column NOT FOUND!")
return jsonify({'error': 'Faturaçao column not found', 'columns': headers}), 400
print(f"\n[RAW] Found Faturaçao at column [{fat_col_idx}]: '{fat_col_name}'")
# Get all data rows (skip header)
data_rows = all_values[1:]
print(f"[RAW] Total data rows: {len(data_rows)}")
# Create DataFrame WITHOUT any parsing
df = pd.DataFrame(data_rows, columns=headers)
print(f"[RAW] DataFrame shape: {df.shape}")
# Show raw Faturaçao values
print(f"\n[RAW] First 20 RAW Faturaçao values (NO PARSING):")
for i, val in enumerate(df[fat_col_name].head(20)):
print(f"[RAW] [{i}] {repr(val)} (type: {type(val).__name__})")
# Try to convert to numeric (pandas auto-convert)
print(f"\n[RAW] Attempting pandas numeric conversion...")
fat_numeric = pd.to_numeric(df[fat_col_name], errors='coerce')
print(f"[RAW] Converted values:")
print(f"[RAW] Non-null count: {fat_numeric.notna().sum()}")
print(f"[RAW] Null/NaN count: {fat_numeric.isna().sum()}")
print(f"[RAW] Sum: {fat_numeric.sum():,.2f}")
print(f"[RAW] First 20 converted values: {fat_numeric.head(20).tolist()}")
# Show sample of problematic values
print(f"\n[RAW] Sample of values that couldn't be converted:")
problem_vals = df[fat_col_name][fat_numeric.isna()].head(10)
for i, val in enumerate(problem_vals):
print(f"[RAW] {repr(val)}")
result = {
'total_rows': len(data_rows),
'total_columns': len(headers),
'column_names': headers,
'faturaçao_column': fat_col_name,
'faturaçao_column_index': fat_col_idx,
'raw_faturaçao_samples': df[fat_col_name].head(20).tolist(),
'numeric_sum': float(fat_numeric.sum()),
'converted_non_null': int(fat_numeric.notna().sum()),
'converted_null': int(fat_numeric.isna().sum()),
'expected_from_looker_studio': 34467590.51
}
print(f"\n[RAW] RESULT: {result}")
return jsonify(result)
except Exception as e:
print(f"[RAW] ERROR: {e}")
import traceback
traceback.print_exc()
return jsonify({'error': str(e)}), 500
@app.route('/dashboard')
@login_required
def dashboard():
"""Minimal dashboard - just show the data."""
print("\n[DASHBOARD] Loading dashboard...")
# Check if we need to re-login
creds = get_google_credentials()
if not creds:
print("[DASHBOARD] Credentials invalid, need to re-login")
html = f"""
Dashboard - Re-login Required
Session Expired
Your Google credentials have expired. Please re-login.
Re-login with Google
"""
return html
df = fetch_data()
if df is None:
html = """
Dashboard
Configurar Spreadsheet
O seu ID da Spreadsheet: 1ayEGU0h_R7CY55COC1U94-p0rJch109YBGvezjYjHWw
"""
return html
def find_col(*keywords):
for col in df.columns:
name = col.lower()
if all(k in name for k in keywords):
return col
return None
fat_col = find_col('fatura')
quant_col = find_col('quant')
cliente_col = find_col('cliente')
zona_col = find_col('zona')
comercial_col = find_col('comercial')
familia_col = find_col('familia') or find_col('família')
mes_col = find_col('mês') or find_col('mes')
# Build year/month columns from mes_col
if mes_col:
def parse_period(value):
if value is None:
return (None, None)
s = str(value).strip()
if not s:
return (None, None)
s = s.replace('-', '/').replace('.', '/')
m = re.search(r"(\d{4})\D?(\d{1,2})", s)
if m:
year = m.group(1)
month = m.group(2).zfill(2)
return (year, month)
m = re.search(r"(\d{1,2})\D?(\d{4})", s)
if m:
month = m.group(1).zfill(2)
year = m.group(2)
return (year, month)
return (None, None)
ym = df[mes_col].apply(parse_period)
df['__year'] = ym.apply(lambda x: x[0])
df['__month'] = ym.apply(lambda x: x[1])
else:
df['__year'] = None
df['__month'] = None
# Apply access control based on user role
assigned_comerciais = []
user_email = session.get('user_email')
user_name = session.get('user_name')
user_role = get_user_role(user_email) if user_email else None
# For comercial users: only show their own data
if user_role == 'comercial' and user_email in SALES_ACCESS_MAP:
assigned_comerciais = SALES_ACCESS_MAP[user_email]
# Last 3 years summary (access-limited, not filter-limited)
df_access = df.copy()
if comercial_col and assigned_comerciais:
allowed = [a.strip().lower() for a in assigned_comerciais]
df_access = df_access[
df_access[comercial_col].astype(str).str.strip().str.lower().isin(allowed)
]
df_access_num = df_access.copy()
if fat_col:
df_access_num[fat_col] = pd.to_numeric(df_access_num[fat_col], errors='coerce')
if quant_col:
df_access_num[quant_col] = pd.to_numeric(df_access_num[quant_col], errors='coerce')
last3_rows_html = "
No data
"
if '__year' in df_access_num.columns and fat_col:
all_years = sorted([y for y in df_access_num['__year'].dropna().unique() if str(y).isdigit() and int(y) >= 2022])
last_3_years = all_years[-3:]
rows = []
prev_revenue = None
prev_avg_total = None
prev_avg_urnas = None
for year in last_3_years:
year_data = df_access_num[df_access_num['__year'] == year]
year_revenue = year_data[fat_col].sum() if fat_col else 0
year_clients = year_data[cliente_col].nunique() if cliente_col else 0
year_urnas_rev = 0
year_urnas_qty = 0
if familia_col:
year_urnas = year_data[year_data[familia_col].astype(str).str.lower().str.contains('urna', na=False)]
if fat_col:
year_urnas_rev = year_urnas[fat_col].sum()
if quant_col:
year_urnas_qty = year_urnas[quant_col].sum()
avg_per_urna_total = (year_revenue / year_urnas_qty) if year_urnas_qty else 0
avg_per_urna_urnas = (year_urnas_rev / year_urnas_qty) if year_urnas_qty else 0
# Revenue growth
if prev_revenue is None:
growth_text = "—"
else:
diff = year_revenue - prev_revenue
pct = (diff / prev_revenue * 100) if prev_revenue else 0
growth_text = f"€{diff:,.2f} ({pct:+.1f}%)"
# Avg Total growth
if prev_avg_total is None or prev_avg_total == 0:
growth_avg_total = "—"
else:
pct_total = ((avg_per_urna_total - prev_avg_total) / prev_avg_total * 100)
growth_avg_total = f"{pct_total:+.1f}%"
# Avg URNAS growth
if prev_avg_urnas is None or prev_avg_urnas == 0:
growth_avg_urnas = "—"
else:
pct_urnas = ((avg_per_urna_urnas - prev_avg_urnas) / prev_avg_urnas * 100)
growth_avg_urnas = f"{pct_urnas:+.1f}%"
rows.append(
f"
{year}
€{year_revenue:,.2f}
{growth_text}
€{avg_per_urna_total:,.2f}
{growth_avg_total}
€{avg_per_urna_urnas:,.2f}
{growth_avg_urnas}
{year_clients:,}
"
)
prev_revenue = year_revenue
prev_avg_total = avg_per_urna_total
prev_avg_urnas = avg_per_urna_urnas
if rows:
last3_rows_html = "".join(rows)
# Apply filters
year_filter = request.args.get('year', 'all')
month_filter = request.args.get('month', 'all')
zona_filter = request.args.get('zona', 'all')
comercial_filter = request.args.get('comercial', 'all')
familia_filter = request.args.get('familia', 'all')
cliente_filter = request.args.get('cliente', 'all')
filter_qs = urlencode(
{
'year': year_filter,
'month': month_filter,
'zona': zona_filter,
'comercial': comercial_filter,
'familia': familia_filter,
},
quote_via=quote_plus
)
filtered = df.copy()
if year_filter != 'all':
filtered = filtered[filtered['__year'] == year_filter]
if month_filter != 'all':
filtered = filtered[filtered['__month'] == month_filter]
if zona_col and zona_filter != 'all':
filtered = filtered[filtered[zona_col] == zona_filter]
if comercial_col and comercial_filter != 'all':
filtered = filtered[filtered[comercial_col] == comercial_filter]
if familia_col and familia_filter != 'all':
filtered = filtered[filtered[familia_col] == familia_filter]
if cliente_col and cliente_filter != 'all':
filtered = filtered[filtered[cliente_col] == cliente_filter]
if comercial_col and assigned_comerciais:
allowed = [a.strip().lower() for a in assigned_comerciais]
filtered = filtered[
filtered[comercial_col].astype(str).str.strip().str.lower().isin(allowed)
]
# Calculate totals
total_fat = filtered[fat_col].sum() if fat_col else 0
total_quant = filtered[quant_col].sum() if quant_col else 0
total_clients = filtered[cliente_col].nunique() if cliente_col else 0
total_rows = len(filtered)
# Analytics
monthly = []
if fat_col:
# Check if any filter is applied
any_filter_applied = (
year_filter != 'all' or
month_filter != 'all' or
zona_filter != 'all' or
comercial_filter != 'all' or
familia_filter != 'all' or
cliente_filter != 'all'
)
# Show max 12 months if filters applied, otherwise 24
months_to_show = 12 if any_filter_applied else 24
monthly = (
filtered.dropna(subset=['__year', '__month'])
.assign(period=lambda d: d['__year'] + '-' + d['__month'])
.groupby('period')[fat_col]
.sum()
.sort_index(ascending=False)
.head(months_to_show)
.reset_index()
.values.tolist()
)
by_zona = []
if zona_col and fat_col:
by_zona = (
filtered.groupby(zona_col)[fat_col]
.sum()
.sort_values(ascending=False)
.head(10)
.reset_index()
.values.tolist()
)
by_comercial = []
if comercial_col and fat_col:
by_comercial = (
filtered.groupby(comercial_col)[fat_col]
.sum()
.sort_values(ascending=False)
.head(10)
.reset_index()
.values.tolist()
)
by_familia = []
if familia_col and fat_col:
by_familia = (
filtered.groupby(familia_col)[fat_col]
.sum()
.sort_values(ascending=False)
.head(10)
.reset_index()
.values.tolist()
)
by_zona = []
if zona_col and fat_col:
by_zona = (
filtered.groupby(zona_col)[fat_col]
.sum()
.sort_values(ascending=False)
.reset_index()
.values.tolist()
)
# Top products by quantity (URNAS ONLY)
referencia_col = find_col('referencia')
by_product_qty = []
urnas_norm = None
if referencia_col and familia_col:
urnas_data = filtered[filtered[familia_col].astype(str).str.lower().str.contains('urna', na=False)]
def normalize_ref(value):
s = str(value).strip()
if not s:
return s
m = re.match(r'^[Cc]\s*(\d+)$', s)
if m:
return m.group(1)
m = re.match(r'^(\d+)$', s)
if m:
return m.group(1)
return s
special_merge = {'122', '124', '126'}
def build_ref_display(norm, refs):
refs_clean = [str(v).strip() for v in refs if str(v).strip()]
# For merged groups, show all unique references
if norm == '122_124_126':
return " / ".join(sorted(set(refs_clean)))
# For others, show the normalized reference
return str(norm)
urnas_norm = urnas_data.assign(
__ref_norm=urnas_data[referencia_col].apply(normalize_ref),
__ref_orig=urnas_data[referencia_col].astype(str).str.strip()
)
urnas_norm['__ref_group'] = urnas_norm['__ref_norm'].apply(
lambda r: '122_124_126' if str(r) in special_merge else str(r)
)
if urnas_norm is not None and quant_col:
grouped_qty = (
urnas_norm.groupby('__ref_group')
.agg(
__qty=(quant_col, 'sum'),
__refs=('__ref_orig', lambda x: list({v for v in x if v}))
)
)
grouped_qty['__display'] = grouped_qty.apply(
lambda row: build_ref_display(row.name, row['__refs']), axis=1
)
by_product_qty = (
grouped_qty.sort_values(by='__qty', ascending=False)
.head(20)
.reset_index(drop=True)
[['__display', '__qty']]
.values.tolist()
)
# Top products by revenue
by_product_rev = []
if urnas_norm is not None and fat_col:
grouped_rev = (
urnas_norm.groupby('__ref_group')
.agg(
__rev=(fat_col, 'sum'),
__refs=('__ref_orig', lambda x: list({v for v in x if v}))
)
)
grouped_rev['__display'] = grouped_rev.apply(
lambda row: build_ref_display(row.name, row['__refs']), axis=1
)
by_product_rev = (
grouped_rev.sort_values(by='__rev', ascending=False)
.head(20)
.reset_index(drop=True)
[['__display', '__rev']]
.values.tolist()
)
# Client summary (URNAS-only for quantity)
clients_summary = []
if cliente_col and fat_col and quant_col:
# Filter to urnas only for quantity calculations
urnas_filtered = filtered[filtered[familia_col].astype(str).str.lower().str.contains('urna', na=False)] if familia_col else filtered
clients_summary = (
filtered.groupby(cliente_col)[fat_col].sum().reset_index(name=fat_col)
)
if not urnas_filtered.empty:
urnas_qty = urnas_filtered.groupby(cliente_col)[quant_col].sum().reset_index(name='__urnas_qty')
clients_summary = clients_summary.merge(urnas_qty, on=cliente_col, how='left')
clients_summary['__urnas_qty'] = clients_summary['__urnas_qty'].fillna(0)
else:
clients_summary['__urnas_qty'] = 0
clients_summary = (
clients_summary
.sort_values(by=fat_col, ascending=False)
.values.tolist()
)
# Convert to tuples (cliente, faturacao, urnas_qty) for template rendering
clients_summary = [(c, f, int(q)) for c, f, q in clients_summary]
# Filter options
years = sorted([y for y in df['__year'].dropna().unique().tolist() if y])
months = sorted([m for m in df['__month'].dropna().unique().tolist() if m])
zonas = sorted(df[zona_col].dropna().unique().tolist()) if zona_col else []
comerciais = sorted(df[comercial_col].dropna().unique().tolist()) if comercial_col else []
familias = sorted(df[familia_col].dropna().unique().tolist()) if familia_col else []
clientes = sorted(df[cliente_col].dropna().unique().tolist()) if cliente_col else []
user_role = get_user_role(user_email)
restricted_user = user_role == 'comercial'
annual_year_options = ''.join([
f''
for y in years
])
annual_report_link = (
f''''''
if not restricted_user else
f''''''
)
html = f"""
Dashboard de Vendas
Filters
Logged in: {session.get('user_email') or 'Unknown'} | Role: {user_role.upper() if user_role else 'UNKNOWN'}{'' if not assigned_comerciais else f' | Access: {", ".join(assigned_comerciais)}'}🚪 Logout
{annual_report_link}
📈 Performance
{f'''''' if user_role == 'admin' else ''}
Resultados dos Últimos 3 Anos
Ano
Faturação
Crescimento vs Ant
Média €/URNA (Total)
Crescimento
Média €/URNA (URNAS)
Crescimento
Clientes
{last3_rows_html}
Monthly Faturação (Last {12 if (year_filter != 'all' or month_filter != 'all' or zona_filter != 'all' or comercial_filter != 'all' or familia_filter != 'all' or cliente_filter != 'all') else 24})
"""
return html
@app.route('/zona-clients')
@login_required
def zona_clients():
"""Show all clients from a specific zona."""
zona_name = request.args.get('zona')
if not zona_name:
return redirect(url_for('dashboard'))
df = fetch_data()
if df is None:
return "Error loading data", 500
def find_col(*keywords):
for col in df.columns:
name = col.lower()
if all(k in name for k in keywords):
return col
return None
zona_col = find_col('zona')
cliente_col = find_col('cliente')
fat_col = find_col('fatura')
quant_col = find_col('quant')
comercial_col = find_col('comercial')
familia_col = find_col('familia') or find_col('família')
mes_col = find_col('mês') or find_col('mes')
if not zona_col or not cliente_col:
return "Zona or Cliente column not found", 500
# Build year/month columns from mes_col (for filtering)
if mes_col:
def parse_period(value):
if value is None:
return (None, None)
s = str(value).strip()
if not s:
return (None, None)
s = s.replace('-', '/').replace('.', '/')
m = re.search(r"(\d{4})\D?(\d{1,2})", s)
if m:
year = m.group(1)
month = m.group(2).zfill(2)
return (year, month)
m = re.search(r"(\d{1,2})\D?(\d{4})", s)
if m:
month = m.group(1).zfill(2)
year = m.group(2)
return (year, month)
return (None, None)
ym = df[mes_col].apply(parse_period)
df['__year'] = ym.apply(lambda x: x[0])
df['__month'] = ym.apply(lambda x: x[1])
else:
df['__year'] = None
df['__month'] = None
# Access control based on user role
user_email = session.get('user_email')
user_role = get_user_role(user_email) if user_email else None
# Filter to this zona
zona_data = df[df[zona_col] == zona_name].copy()
# Apply dashboard filters (year, month, comercial, familia)
year_filter = request.args.get('year', 'all')
month_filter = request.args.get('month', 'all')
comercial_filter = request.args.get('comercial', 'all')
familia_filter = request.args.get('familia', 'all')
if year_filter != 'all':
zona_data = zona_data[zona_data['__year'] == year_filter]
if month_filter != 'all':
zona_data = zona_data[zona_data['__month'] == month_filter]
if comercial_col and comercial_filter != 'all':
zona_data = zona_data[zona_data[comercial_col] == comercial_filter]
if familia_col and familia_filter != 'all':
zona_data = zona_data[zona_data[familia_col] == familia_filter]
# For comercial users: only show their own clients' data
if user_role == 'comercial' and user_email in SALES_ACCESS_MAP:
assigned = SALES_ACCESS_MAP[user_email]
allowed = [a.strip().lower() for a in assigned]
if comercial_col:
zona_data = zona_data[
zona_data[comercial_col].astype(str).str.strip().str.lower().isin(allowed)
]
if zona_data.empty:
return f"
"
# Build filter querystring for client links
filter_params = []
if year_filter != 'all':
filter_params.append(f'year={year_filter}')
if month_filter != 'all':
filter_params.append(f'month={month_filter}')
if comercial_filter != 'all':
filter_params.append(f'comercial={quote_plus(comercial_filter)}')
if familia_filter != 'all':
filter_params.append(f'familia={quote_plus(familia_filter)}')
filter_params.append(f'zona={quote_plus(zona_name)}')
filter_qs = '&'.join(filter_params)
# Get clients in this zona with their totals (URNAS-only for quantity)
clients_list = []
for cliente in zona_data[cliente_col].unique():
if pd.isna(cliente):
continue
client_subset = zona_data[zona_data[cliente_col] == cliente]
total_fat = client_subset[fat_col].sum() if fat_col else 0
# Calculate URNAS-only quantity for this client
if familia_col and quant_col:
client_urnas = client_subset[client_subset[familia_col].astype(str).str.lower().str.contains('urna', na=False)]
total_quant = client_urnas[quant_col].sum() if not client_urnas.empty else 0
else:
total_quant = client_subset[quant_col].sum() if quant_col else 0
clients_list.append({
'name': str(cliente),
'revenue': total_fat,
'quantity': total_quant
})
# Sort by revenue descending
clients_list.sort(key=lambda x: x['revenue'], reverse=True)
# Total summary - filtered revenue, URNAS-only quantity
total_zona_revenue = zona_data[fat_col].sum() if fat_col else 0
# Calculate URNAS-only quantity
urnas_data = zona_data[zona_data[familia_col].astype(str).str.lower().str.contains('urna', na=False)] if familia_col else zona_data
total_zona_quantity = urnas_data[quant_col].sum() if quant_col and not urnas_data.empty else 0
num_clients = len(clients_list)
# Top 10 URNAS references for this zona
referencia_col = find_col('referencia')
top_urnas = []
if referencia_col and quant_col and not urnas_data.empty:
def normalize_ref(value):
s = str(value).strip()
if not s:
return s
m = re.match(r'^[Cc]\s*(\d+)$', s)
if m:
return m.group(1)
m = re.match(r'^(\d+)$', s)
if m:
return m.group(1)
return s
special_merge = {'122', '124', '126'}
def build_ref_display(norm, refs):
refs_clean = [str(v).strip() for v in refs if str(v).strip()]
if norm == '122_124_126':
return " / ".join(sorted(set(refs_clean)))
return str(norm)
urnas_norm = urnas_data.assign(
__ref_norm=urnas_data[referencia_col].apply(normalize_ref),
__ref_orig=urnas_data[referencia_col].astype(str).str.strip()
)
urnas_norm['__ref_group'] = urnas_norm['__ref_norm'].apply(
lambda r: '122_124_126' if str(r) in special_merge else str(r)
)
grouped_qty = (
urnas_norm.groupby('__ref_group')
.agg(
__qty=(quant_col, 'sum'),
__refs=('__ref_orig', lambda x: list({v for v in x if v}))
)
)
grouped_qty['__display'] = grouped_qty.apply(
lambda row: build_ref_display(row.name, row['__refs']), axis=1
)
top_urnas = (
grouped_qty.sort_values(by='__qty', ascending=False)
.head(10)
.reset_index(drop=True)
[['__display', '__qty']]
.values.tolist()
)
# Build HTML with filter parameters in client links
clients_rows = ''.join([
f'
📋 Data Origin:
Year: {year_filter if year_filter != 'all' else 'All Years'}
{f" | Month: {month_filter}" if month_filter != 'all' else ''}
{f" | Comercial: {comercial_filter}" if comercial_filter != 'all' else ''}
{f" | Familia: {familia_filter}" if familia_filter != 'all' else ''}
Faturação Total
€{total_zona_revenue:,.2f}
Quantidade Urnas
{int(total_zona_quantity):,}
Clientes
{num_clients}
🏆 Top 10 Urnas (Referências)
Referência
Quantidade
{urnas_rows}
👥 Clientes desta Zona
Cliente
Faturação
Quantidade (Urnas)
{clients_rows}
'''
return html
@app.route('/client-details')
@login_required
def client_details():
"""Show detailed breakdown for a specific client."""
cliente_name = request.args.get('cliente')
if not cliente_name:
return redirect(url_for('dashboard'))
df = fetch_data()
if df is None:
return "Error loading data", 500
def find_col(*keywords):
for col in df.columns:
name = col.lower()
if all(k in name for k in keywords):
return col
return None
cliente_col = find_col('cliente')
referencia_col = find_col('referencia')
fat_col = find_col('fatura')
quant_col = find_col('quant')
mes_col = find_col('mês') or find_col('mes')
familia_col = find_col('familia') or find_col('família')
desconto_col = find_col('desconto')
prazo_col = find_col('prazo', 'pagamento') or find_col('prazo')
if not cliente_col:
return "Cliente column not found", 500
# Build year/month columns from mes_col (for filtering)
if mes_col:
def parse_period(value):
if value is None:
return (None, None)
s = str(value).strip()
if not s:
return (None, None)
s = s.replace('-', '/').replace('.', '/')
m = re.search(r"(\d{4})\D?(\d{1,2})", s)
if m:
year = m.group(1)
month = m.group(2).zfill(2)
return (year, month)
m = re.search(r"(\d{1,2})\D?(\d{4})", s)
if m:
month = m.group(1).zfill(2)
year = m.group(2)
return (year, month)
return (None, None)
ym = df[mes_col].apply(parse_period)
df['__year'] = ym.apply(lambda x: x[0])
df['__month'] = ym.apply(lambda x: x[1])
else:
df['__year'] = None
df['__month'] = None
# Filter to this client
client_data = df[df[cliente_col] == cliente_name]
# Apply filters from dashboard
year_filter = request.args.get('year', 'all')
month_filter = request.args.get('month', 'all')
zona_filter = request.args.get('zona', 'all')
comercial_filter = request.args.get('comercial', 'all')
familia_filter = request.args.get('familia', 'all')
zona_col = find_col('zona')
comercial_col = find_col('comercial')
if year_filter != 'all':
client_data = client_data[client_data['__year'] == year_filter]
if month_filter != 'all':
client_data = client_data[client_data['__month'] == month_filter]
if zona_col and zona_filter != 'all':
client_data = client_data[client_data[zona_col] == zona_filter]
if comercial_col and comercial_filter != 'all':
client_data = client_data[client_data[comercial_col] == comercial_filter]
if familia_col and familia_filter != 'all':
client_data = client_data[client_data[familia_col] == familia_filter]
# Access control based on user role
user_email = session.get('user_email')
user_role = get_user_role(user_email) if user_email else None
# For comercial users: only show their own clients' data
if user_role == 'comercial' and user_email in SALES_ACCESS_MAP:
assigned = SALES_ACCESS_MAP[user_email]
allowed = [a.strip().lower() for a in assigned]
if comercial_col:
client_data = client_data[
client_data[comercial_col].astype(str).str.strip().str.lower().isin(allowed)
]
if client_data.empty:
return f"
Discount: {(client_data[desconto_col].iloc[0] if desconto_col and len(client_data) > 0 and pd.notna(client_data[desconto_col].iloc[0]) else 'Not defined')}
Payment Terms (Days): {(int(float(str(client_data[prazo_col].iloc[0]).replace(',', '.'))) if prazo_col and len(client_data) > 0 and pd.notna(client_data[prazo_col].iloc[0]) else 'Not defined')} days
Total Performance
€ {total_fat:,.2f}
Urnas Summary
{urnas_total_qty:,.0f}
Total Urnas Quantity Sold
€ {urnas_total_fat:,.2f}
Total Value (Urnas + Estofo)
€ {avg_value_per_qty_urnas:,.2f}
Average Value per Urna Unit
€ {total_fat:,.2f}
Total Value (All Families)
€ {avg_value_per_qty_total:,.2f}
Average Value per Unit (All Families)
Products Purchased
Referência
Faturação
YoY Change
Quantidade
YoY Change
{''.join([f'''
{r}
€{f_curr:,.2f}
{('↑' if (f_pct is not None and f_pct > 2) else ('↓' if (f_pct is not None and f_pct < -2) else '→')) if f_pct is not None else '-'}
{f" {f_pct:+.1f}%" if f_pct is not None else ''}
{int(q_curr):,}
{('↑' if (q_pct is not None and q_pct > 2) else ('↓' if (q_pct is not None and q_pct < -2) else '→')) if q_pct is not None else '-'}
{f" {q_pct:+.1f}%" if q_pct is not None else ''}
''' for r, f_curr, q_curr, f_prev, q_prev, f_pct, q_pct in by_product])}
Monthly Purchases (Last 12)
Month
Faturação
YoY Value Change
YoY % Change
{''.join([f'''
{m}
€{f_curr:,.2f}
{f"€{change:+,.2f}" if f_prev > 0 else '-'}
{('↑' if pct > 2 else ('↓' if pct < -2 else '→')) if f_prev > 0 else '-'}
{f" {pct:+.1f}%" if f_prev > 0 else ''}
''' for m, f_curr, f_prev, change, pct in by_month])}
"""
return html
@app.route('/annual-report')
@login_required
def annual_report():
"""Generate comprehensive annual report with annual, semestral, and trimestral breakdowns."""
user_email = session.get('user_email')
user_role = get_user_role(user_email)
scope = request.args.get('scope', 'all')
# Comercials can only view personal scope, others can view all
if user_role == 'comercial' and scope != 'personal':
return redirect(url_for('dashboard'))
if user_role is None:
return redirect(url_for('login'))
year_param = request.args.get('year', '2025')
df = fetch_data()
if df is None:
return "Error loading data", 500
def find_col(*keywords):
for col in df.columns:
name = col.lower()
if all(k in name for k in keywords):
return col
return None
# Parse dates
mes_col = find_col('mês') or find_col('mes')
referencia_col = find_col('referencia')
fat_col = find_col('fatura')
quant_col = find_col('quant')
cliente_col = find_col('cliente')
comercial_col = find_col('comercial')
familia_col = find_col('familia')
zona_col = find_col('zona')
if mes_col:
def parse_period(value):
if value is None:
return (None, None)
s = str(value).strip()
if not s:
return (None, None)
s = s.replace('-', '/').replace('.', '/')
m = re.search(r"(\d{4})\D?(\d{1,2})", s)
if m:
year = m.group(1)
month = m.group(2).zfill(2)
return (year, month)
m = re.search(r"(\d{1,2})\D?(\d{4})", s)
if m:
month = m.group(1).zfill(2)
year = m.group(2)
return (year, month)
return (None, None)
ym = df[mes_col].apply(parse_period)
df['__year'] = ym.apply(lambda x: x[0])
df['__month'] = ym.apply(lambda x: x[1])
else:
df['__year'] = None
df['__month'] = None
# Apply access control for personal scope
if user_role == 'comercial':
comercial_col = find_col('comercial')
if comercial_col:
assigned = SALES_ACCESS_MAP.get(user_email, [])
allowed = [a.strip().lower() for a in assigned]
df = df[df[comercial_col].astype(str).str.lower().isin(allowed)]
# Filter by year
year_data = df[df['__year'] == year_param]
if year_data.empty:
return f"
Globale RC Relatório de Vendas{'' if user_role != 'comercial' else ' (Meus Dados)'} - {year_param}
"""
return html
@app.route('/set-spreadsheet')
@login_required
def set_spreadsheet():
sid = request.args.get('id')
if sid:
session['spreadsheet_id'] = sid
return jsonify({'success': True})
return jsonify({'success': False}), 400
# ============================================================================
# PERFORMANCE & OBJECTIVES ROUTES
# ============================================================================
@app.route('/performance')
@login_required
def performance():
"""Performance dashboard showing objectives vs actual sales."""
user_email = session.get('user_email')
user_role = get_user_role(user_email)
# Get all scopes for admin/viewer, own data for comercial
comercials_to_show = []
if user_role == 'admin' or user_role == 'viewer':
comercials_to_show = ['TOTAL', 'EXPORTAÇÃO', 'José Amor', 'Hélder Oliveira']
elif user_role == 'comercial' and user_email in SALES_ACCESS_MAP:
comercials_to_show = SALES_ACCESS_MAP[user_email]
if not comercials_to_show:
return f"Access denied (email={user_email}, role={user_role})", 403
# Get performance data for each comercial
performance_data = []
for comercial in comercials_to_show:
perf = calculate_performance(comercial)
if perf:
performance_data.append(perf)
# HTML template
html = """
Performance Dashboard - Objetivos de Vendas
Vendido Atual (2026): {perf['total_urnas_current']:.0f} unidades
📊 Execução (Ano atual):{perf['urnas_achievement_pct']:.1f}%(Atual vs Meta)
🎯 % Para Atingir Meta:{perf['urnas_to_target_pct']:+.1f}%(Crescimento necessário)
📈 Crescimento vs Ano Anterior:{urnas_growth_pct:+.1f}%(Meta vs Ano anterior)
"""
html += """
"""
html += """
"""
return html
@app.route('/performance-report/')
@login_required
def performance_report(comercial):
"""Detailed performance report for a specific comercial."""
user_email = session.get('user_email')
user_role = get_user_role(user_email)
# Access control
if user_role == 'comercial' and user_email in SALES_ACCESS_MAP:
if comercial not in SALES_ACCESS_MAP[user_email]:
return f"Access denied (email={user_email}, role={user_role})", 403
elif user_role not in ['admin', 'viewer']:
return f"Access denied (email={user_email}, role={user_role})", 403
perf = calculate_performance(comercial)
if not perf:
return "Sem dados disponíveis", 404
# Generate PDF-friendly HTML report
html = f"""
Relatório de Desempenho - {comercial}
Relatório de Desempenho de Vendas
{comercial} - Desempenho Anual
Gerado em {{}} | Globale RC
📊 Resumo de Desempenho Geral
Meta de Faturação 2026
€{perf['total_revenue_target']:,.2f}
Vendido Atual (2026)
€{perf['total_revenue_current']:,.2f}
Resultado 2025
€{perf['total_revenue_prev_year']:,.2f}
Cumprimento de Faturação
{perf['revenue_achievement_pct']:.1f}%
Estado
{'✓ Meta Atingida' if perf['revenue_achievement_pct'] >= 100 else '⚠ Em Progresso' if perf['revenue_achievement_pct'] >= 80 else '✗ Abaixo da Meta'}
% Para Atingir
{perf['revenue_to_target_pct']:+.1f}%
Meta URNAS 2026
{perf['total_urnas_target']:.0f}
Vendido Atual (2026)
{perf['total_urnas_current']:.0f} unidades
Resultado 2025
{perf['total_urnas_prev_year']:.0f} unidades
Cumprimento URNAS
{perf['urnas_achievement_pct']:.1f}%
Estado
{'✓ Meta Atingida' if perf['urnas_achievement_pct'] >= 100 else '⚠ Em Progresso' if perf['urnas_achievement_pct'] >= 80 else '✗ Abaixo da Meta'}
% Para Atingir
{perf['urnas_to_target_pct']:+.1f}%
Comissão
€{perf['commission_value']:,.2f}
Taxa
{perf['commission_rate']*100:.1f}% sobre faturação real
� Last 3 Years Results - Personal Performance
Ano
Faturação
Crescimento vs Ant
Média €/URNA (Total)
Crescimento
Média €/URNA (URNAS)
Crescimento
Clientes
{''.join([f'''
{item['year']}
€{item['revenue']:,.2f}
{item['growth_text']}
€{item['avg_per_urna_total']:,.2f}
{item['growth_avg_total_text']}
€{item['avg_per_urna_urnas']:,.2f}
{item['growth_avg_urnas_text']}
{item['clients']:,}
''' for item in perf['historical_data']])}
�👥 Performance by Client
Client-level objectives are disabled. This report shows sales force totals only.
"""
return html
@app.route('/setup-objectives')
@login_required
def setup_objectives():
"""Create and populate the Objetivos sheet with targets based on the previous year."""
user_email = session.get('user_email')
user_role = get_user_role(user_email)
# Only admin can set up objectives
if user_role != 'admin':
return "Acesso negado - Apenas Admin", 403
print("\n[SETUP] Creating Objetivos sheet...")
try:
creds = get_google_credentials()
if not creds:
return "No credentials", 500
SPREADSHEET_ID = session.get('spreadsheet_id') or DEFAULT_SPREADSHEET_ID
gc = gspread.authorize(creds)
spreadsheet = gc.open_by_key(SPREADSHEET_ID)
# Fetch and analyze sales data
df_sales = fetch_data()
if df_sales is None:
return "Could not fetch sales data", 500
# Find columns
def find_col(*keywords):
for col in df_sales.columns:
name = col.lower()
if all(k in name for k in keywords):
return col
return None
comercial_col = find_col('comercial')
cliente_col = find_col('cliente')
fat_col = find_col('fatura')
quant_col = find_col('quant')
zona_col = find_col('zona')
familia_col = find_col('familia') or find_col('família')
mes_col = find_col('mês') or find_col('mes')
if not all([comercial_col, cliente_col, fat_col, quant_col]):
return "Missing required columns", 500
# Parse numeric data
df_sales[fat_col] = pd.to_numeric(df_sales[fat_col], errors='coerce')
df_sales[quant_col] = pd.to_numeric(df_sales[quant_col], errors='coerce')
# Build year column
if mes_col:
def parse_period(value):
if value is None:
return (None, None)
s = str(value).strip()
if not s:
return (None, None)
s = s.replace('-', '/').replace('.', '/')
m = re.search(r"(\d{4})\D?(\d{1,2})", s)
if m:
year = m.group(1)
month = m.group(2).zfill(2)
return (year, month)
m = re.search(r"(\d{1,2})\D?(\d{4})", s)
if m:
month = m.group(1).zfill(2)
year = m.group(2)
return (year, month)
return (None, None)
ym = df_sales[mes_col].apply(parse_period)
df_sales['__year'] = ym.apply(lambda x: x[0])
else:
df_sales['__year'] = None
# Determine previous year and growth % from query params
growth_total = request.args.get('growth_total', '5')
growth_export = request.args.get('growth_export', '5')
growth_comercial = request.args.get('growth_comercial', '5')
def parse_growth(value, default=5.0):
try:
return float(value)
except (TypeError, ValueError):
return default
growth_total = parse_growth(growth_total)
growth_export = parse_growth(growth_export)
growth_comercial = parse_growth(growth_comercial)
prev_year = str(datetime.now().year - 1)
years = sorted([y for y in df_sales['__year'].dropna().unique() if str(y).isdigit()])
if prev_year not in years:
prev_year = years[-1] if years else None
target_year = str(int(prev_year) + 1) if prev_year and str(prev_year).isdigit() else None
# Analyze totals (previous year only)
analysis = {}
if prev_year:
base_data = df_sales[df_sales['__year'] == prev_year]
else:
base_data = df_sales.copy()
total_revenue_all = base_data[fat_col].sum() if fat_col else 0
urnas_all = base_data
if familia_col:
urnas_all = base_data[base_data[familia_col].astype(str).str.lower().str.contains('urna', na=False)]
total_urnas_all = urnas_all[quant_col].sum() if quant_col else 0
analysis['TOTAL'] = {
'total_revenue': total_revenue_all,
'total_urnas': total_urnas_all
}
if zona_col:
export_data = base_data[base_data[zona_col].astype(str).str.lower().str.contains('export', na=False)]
else:
export_data = base_data.iloc[0:0].copy()
export_revenue = export_data[fat_col].sum() if fat_col else 0
export_urnas = export_data
if familia_col:
export_urnas = export_data[export_data[familia_col].astype(str).str.lower().str.contains('urna', na=False)]
export_urnas_total = export_urnas[quant_col].sum() if quant_col else 0
analysis['EXPORTAÇÃO'] = {
'total_revenue': export_revenue,
'total_urnas': export_urnas_total
}
for comercial in df_sales[comercial_col].unique():
if pd.isna(comercial):
continue
comercial = str(comercial).strip()
# Skip if this comercial is already in analysis (e.g., EXPORTAÇÃO was added as a scope)
# Also skip if comercial name contains "export" since that's the EXPORTAÇÃO scope
if comercial in analysis or 'export' in comercial.lower():
continue
comercial_data = df_sales[df_sales[comercial_col] == comercial]
if prev_year:
comercial_data = comercial_data[comercial_data['__year'] == prev_year]
# Total revenue and URNAS for comercial
total_revenue = comercial_data[fat_col].sum()
# URNAS: only from Familia containing 'urna'
urnas_data = comercial_data[
comercial_data[familia_col].astype(str).str.lower().str.contains('urna', na=False)
]
total_urnas = urnas_data[quant_col].sum()
# Sales force totals only (no client-level objectives)
analysis[comercial] = {
'total_revenue': total_revenue,
'total_urnas': total_urnas
}
# Create or update Objetivos sheet
sheet_exists = False
worksheet = None
for sheet in spreadsheet.worksheets():
if sheet.title.lower() == 'objetivos':
sheet_exists = True
worksheet = sheet
break
if not sheet_exists:
print("[SETUP] Creating new Objetivos sheet...")
worksheet = spreadsheet.add_worksheet(title="Objetivos", rows=200, cols=5)
else:
print("[SETUP] Updating existing Objetivos sheet...")
# Prepare data with user-provided growth projection
growth_factor_total = 1 + (growth_total / 100.0)
growth_factor_export = 1 + (growth_export / 100.0)
growth_factor_comercial = 1 + (growth_comercial / 100.0)
headers = ["Comercial", "Cliente", "Target_Valor", "Target_Urnas", "Period"]
data_rows = [headers]
for comercial, comercial_data in sorted(analysis.items()):
# Total row for comercial
if comercial == 'TOTAL':
growth_factor = growth_factor_total
elif comercial == 'EXPORTAÇÃO':
growth_factor = growth_factor_export
else:
growth_factor = growth_factor_comercial
total_revenue_target = comercial_data['total_revenue'] * growth_factor
total_urnas_target = comercial_data['total_urnas'] * growth_factor
data_rows.append([
comercial,
"Total",
round(total_revenue_target, 2),
round(total_urnas_target, 0),
"Annual"
])
# Write to sheet
worksheet.clear()
worksheet.update(data_rows, range_name='A1')
print(f"[SETUP] Wrote {len(data_rows)-1} objectives")
# Format header
worksheet.format("A1:E1", {
"backgroundColor": {"red": 0.4, "green": 0.6, "blue": 1.0},
"textFormat": {"bold": True, "foregroundColor": {"red": 1, "green": 1, "blue": 1}}
})
# Create success page
html = """
Setup Complete
✅ Folha Objetivos Criada!
Sucesso! A folha 'Objetivos' foi criada e preenchida com metas de vendas para {target_year or 'o próximo ano'}.
📊 Detalhes da Análise:
• Analisadas vendas do ano anterior para TOTAL, EXPORTAÇÃO e cada comercial
• Extraídas faturação real (€) e quantidades URNAS para cada âmbito
• Aplicadas projeções de crescimento — Total: {growth_total:.1f}%, Export: {growth_export:.1f}%, Comercial: {growth_comercial:.1f}%
• Criadas linhas para objetivos TOTAL, EXPORTAÇÃO e comerciais
Cobertura:
• 2 Comerciais: Jose Amor, Helder Oliveira
• Apenas totais da força de vendas (sem divisão por cliente)
• Ano base utilizado: {prev_year or 'N/D'}
📈 Próximos Passos:
1. Revise a folha 'Objetivos' nas suas Google Sheets
2. Ajuste as metas se necessário (5% de crescimento pode não se adequar ao seu negócio)
3. Visite /performance para ver o dashboard
4. Acompanhe a % de cumprimento para cada comercial e cliente
