import csv import re import sys from collections import defaultdict from datetime import datetime CLIENT_NAME_MAP = { 'UCSC': 'UCSC', 'Universidad de Concepción': 'UDEC', 'Universidad del Desarrollo': 'UDD', 'Superintendencia de Salud': 'SUPERINTENDENCIA', 'Mapatex S.A.': 'VERDEAGUA', 'Rijk Zwaan': 'Rijk Zawaan', 'PUC': 'PUC', 'Universidad de Viña del Mar': 'UVM', 'Catálogo Arquitectura': 'CATALOGOARQ', 'Falabella': 'FALABELLA', 'Pluxee': 'SODEXO', 'e[ad] PUCV': 'PUCV', 'DuocUC': 'DUOC', 'Corporación Cultural Amereida': 'CORP. AMEREIDA', 'Ministerio de Economía, Fomento y Turismo': 'MINECON', 'ULTRAMAR': 'ULTRAMAR', 'LIBERA TU CICLO': 'LIBERA TU CICLO', } CLIENT_SHORT_NAMES = { 'Universidad de Concepción': 'UdeC', 'Universidad del Desarrollo': 'UDD', 'Superintendencia de Salud': 'Superdesalud', 'Mapatex S.A.': 'Mapatex / VerdeAgua', 'UCSC': 'UCSC', 'e[ad] PUCV': 'e[ad] PUCV', 'Pluxee': 'Pluxee', 'Falabella': 'Falabella', 'Ministerio de Economía, Fomento y Turismo': 'Min. Economía', 'DuocUC': 'DuocUC', 'Rijk Zwaan': 'Rijk Zwaan', 'PUC': 'PUC', 'Universidad de Viña del Mar': 'UVM', 'Catálogo Arquitectura': 'CatArq', } def parse_time_csv(filepath, year): with open(filepath, 'r', encoding='utf-8') as f: content = f.read() lines = content.split('\n') records = [] current_record = None for line in lines[1:]: if not line.strip(): continue if line.startswith('TimeRecord,'): if current_record: records.append(current_record) current_record = line else: if current_record: current_record += '\n' + line if current_record: records.append(current_record) parsed = [] for rec in records: rec_clean = rec.replace('\n', ' ') reader = csv.reader([rec_clean]) for row in reader: if len(row) >= 17: parsed.append(row) data = [] for row in parsed: try: date_str = row[11] if not re.match(r'\d{4}-\d{2}-\d{2}', date_str): continue date = datetime.strptime(date_str, '%Y-%m-%d') if year and date.year != year: continue user_name = row[13] client_name = row[10] project_name = row[8] task_name = row[6] value = float(row[16]) status = row[17] if len(row) > 17 else '' is_billable = status == 'Billable' is_internal = client_name == 'Bloom User Experience' data.append({ 'date': date, 'week': date.isocalendar()[1], 'month': date.month, 'user': user_name, 'client': client_name, 'project': project_name, 'task': task_name, 'hours': value, 'billable': is_billable, 'internal': is_internal }) except (ValueError, IndexError): continue return data def parse_money(s): if not s or s.strip() == '': return 0 s = s.strip().replace('$', '').replace('.', '').replace(',', '').replace('"', '') try: return float(s) except ValueError: return 0 def parse_ingresos_csv(filepath, year): data = [] with open(filepath, 'r', encoding='utf-8') as f: reader = csv.reader(f) header = next(reader) for row in reader: if len(row) < 12: continue fecha_str = row[4] if len(row) > 4 else '' mes_emision = row[6] if len(row) > 6 else '' try: if mes_emision and re.match(r'\d{4}\s+\d{2}', mes_emision): parts = mes_emision.strip().split() em_year = int(parts[0]) em_month = int(parts[1]) elif fecha_str: fecha_clean = fecha_str.strip() if re.match(r'\d{2}-\d{2}-\d{4}', fecha_clean): em_year = int(fecha_clean[6:10]) em_month = int(fecha_clean[3:5]) else: continue else: continue except (ValueError, IndexError): continue if em_year != year: continue nombre = row[0].strip() if len(row) > 0 else '' total = parse_money(row[7]) if len(row) > 7 else 0 neto = parse_money(row[8]) if len(row) > 8 else 0 iva = parse_money(row[9]) if len(row) > 9 else 0 estado = row[10].strip() if len(row) > 10 else '' fecha_pago = row[11].strip() if len(row) > 11 else '' observaciones = row[14].strip() if len(row) > 14 else '' clasificacion = row[16].strip() if len(row) > 16 else '' tipo_proyecto = row[17].strip() if len(row) > 17 else '' pagado = estado.lower() == 'pagada' data.append({ 'nombre': nombre, 'total': total, 'neto': neto, 'iva': iva, 'estado': estado, 'pagado': pagado, 'mes_emision': em_month, 'clasificacion': clasificacion, 'tipo_proyecto': tipo_proyecto, 'observaciones': observaciones, }) return data def calculate_metrics(data, ingresos_data, year, max_month=12): filtered_data = [d for d in data if d['month'] <= max_month] filtered_ingresos = [i for i in ingresos_data if i['mes_emision'] <= max_month] total_hours = sum(d['hours'] for d in filtered_data) billable_hours = sum(d['hours'] for d in filtered_data if d['billable']) internal_hours = sum(d['hours'] for d in filtered_data if d['internal']) external_hours = total_hours - internal_hours billable_pct = (billable_hours / total_hours * 100) if total_hours > 0 else 0 internal_pct = (internal_hours / total_hours * 100) if total_hours > 0 else 0 external_pct = (external_hours / total_hours * 100) if total_hours > 0 else 0 users = set(d['user'] for d in filtered_data) person_month = defaultdict(lambda: defaultdict(float)) person_week = defaultdict(lambda: defaultdict(float)) person_internal = defaultdict(float) person_external = defaultdict(float) person_total = defaultdict(float) for d in filtered_data: person_month[d['user']][d['month']] += d['hours'] person_week[d['user']][d['week']] += d['hours'] person_total[d['user']] += d['hours'] if d['internal']: person_internal[d['user']] += d['hours'] else: person_external[d['user']] += d['hours'] month_total = defaultdict(float) month_billable = defaultdict(float) month_internal = defaultdict(float) for d in filtered_data: month_total[d['month']] += d['hours'] if d['billable']: month_billable[d['month']] += d['hours'] if d['internal']: month_internal[d['month']] += d['hours'] client_hours = defaultdict(float) for d in filtered_data: if not d['internal']: client_hours[d['client']] += d['hours'] ingresos_by_client = defaultdict(lambda: {'total': 0, 'neto': 0, 'pagado': 0, 'pendiente': 0, 'facturas': 0, 'facturas_pagadas': 0}) ingresos_month = defaultdict(float) ingresos_month_pagado = defaultdict(float) total_ingresos = 0 total_ingresos_pagados = 0 total_ingresos_pendientes = 0 for ing in filtered_ingresos: nombre = ing['nombre'] ingresos_by_client[nombre]['total'] += ing['total'] ingresos_by_client[nombre]['neto'] += ing['neto'] ingresos_by_client[nombre]['facturas'] += 1 total_ingresos += ing['total'] if ing['pagado']: ingresos_by_client[nombre]['pagado'] += ing['total'] ingresos_by_client[nombre]['facturas_pagadas'] += 1 total_ingresos_pagados += ing['total'] ingresos_month_pagado[ing['mes_emision']] += ing['total'] else: ingresos_by_client[nombre]['pendiente'] += ing['total'] total_ingresos_pendientes += ing['total'] ingresos_month[ing['mes_emision']] += ing['total'] client_ingresos_mapped = {} for export_name, ing_name in CLIENT_NAME_MAP.items(): if ing_name in ingresos_by_client: client_ingresos_mapped[export_name] = ingresos_by_client[ing_name] task_hours = defaultdict(lambda: {'hours': 0, 'internal': False, 'project': ''}) for d in filtered_data: key = f"{d['task']}|{d['project']}" task_hours[key]['hours'] += d['hours'] task_hours[key]['internal'] = d['internal'] task_hours[key]['project'] = d['project'] task_hours[key]['task'] = d['task'] weeks_in_period = max_month * 4.33 return { 'year': year, 'max_month': max_month, 'weeks_in_period': weeks_in_period, 'total': total_hours, 'billable': billable_hours, 'billable_pct': billable_pct, 'internal': internal_hours, 'internal_pct': internal_pct, 'external': external_hours, 'external_pct': external_pct, 'users': sorted(users), 'person_month': dict(person_month), 'person_week': dict(person_week), 'person_internal': dict(person_internal), 'person_external': dict(person_external), 'person_total': dict(person_total), 'month_total': dict(month_total), 'month_billable': dict(month_billable), 'month_internal': dict(month_internal), 'client_hours': dict(client_hours), 'task_hours': dict(task_hours), 'client_short_names': CLIENT_SHORT_NAMES, 'ingresos_by_client': dict(ingresos_by_client), 'client_ingresos_mapped': client_ingresos_mapped, 'ingresos_month': dict(ingresos_month), 'ingresos_month_pagado': dict(ingresos_month_pagado), 'total_ingresos': total_ingresos, 'total_ingresos_pagados': total_ingresos_pagados, 'total_ingresos_pendientes': total_ingresos_pendientes, } def format_num(n, decimals=1): return f"{n:,.{decimals}f}".replace(',', 'X').replace('.', ',').replace('X', '.') def format_clp(n): n = int(round(n)) s = f"{n:,}".replace(',', '.') return f"${s}" def generate_html(metrics): year = metrics['year'] max_month = metrics['max_month'] weeks_in_period = metrics['weeks_in_period'] month_names = ['Ene', 'Feb', 'Mar', 'Abr', 'May', 'Jun', 'Jul', 'Ago', 'Sep', 'Oct', 'Nov', 'Dic'] month_names_full = ['Enero', 'Febrero', 'Marzo', 'Abril', 'Mayo', 'Junio', 'Julio', 'Agosto', 'Septiembre', 'Octubre', 'Noviembre', 'Diciembre'] users_sorted = sorted(metrics['person_total'].keys(), key=lambda u: metrics['person_total'][u], reverse=True) total_persons = len(users_sorted) person_rows = [] for user in users_sorted: months_data = [metrics['person_month'].get(user, {}).get(m, 0) for m in range(1, max_month + 1)] total = metrics['person_total'].get(user, 0) internal = metrics['person_internal'].get(user, 0) external = metrics['person_external'].get(user, 0) internal_pct = (internal / total * 100) if total > 0 else 0 weeks_data = metrics['person_week'].get(user, {}) active_weeks = len([w for w, h in weeks_data.items() if h > 0]) prom_sem_bruto = total / weeks_in_period if total > 0 else 0 prom_sem_ajustado = total / active_weeks if active_weeks > 0 else 0 person_rows.append({ 'name': user, 'months': months_data, 'total': total, 'internal': internal, 'external': external, 'internal_pct': internal_pct, 'prom_sem': prom_sem_bruto, 'prom_sem_ajustado': prom_sem_ajustado }) month_totals = [] for m in range(1, max_month + 1): t = metrics['month_total'].get(m, 0) b = metrics['month_billable'].get(m, 0) i = metrics['month_internal'].get(m, 0) month_totals.append({ 'name': month_names_full[m-1], 'total': t, 'billable_pct': (b / t * 100) if t > 0 else 0, 'internal_pct': (i / t * 100) if t > 0 else 0 }) clients_sorted = sorted(metrics['client_hours'].items(), key=lambda x: x[1], reverse=True) total_external = metrics['external'] client_rows = [] for client, hours in clients_sorted: pct = (hours / total_external * 100) if total_external > 0 else 0 short_name = metrics['client_short_names'].get(client, client[:20]) ingresos = metrics['client_ingresos_mapped'].get(client, {'total': 0, 'neto': 0, 'pagado': 0, 'pendiente': 0, 'facturas': 0, 'facturas_pagadas': 0}) ingreso_total = ingresos['total'] dolar_hora = (ingreso_total / hours) if hours > 0 else 0 client_rows.append({ 'name': client, 'short_name': short_name, 'hours': hours, 'pct': pct, 'ingresos': ingreso_total, 'dolar_hora': dolar_hora, 'facturas': ingresos['facturas'], 'pagado': ingresos['pagado'], 'pendiente': ingresos['pendiente'] }) top3_external = sum(h for _, h in clients_sorted[:3]) top3_pct = (top3_external / total_external * 100) if total_external > 0 else 0 tasks_sorted = sorted(metrics['task_hours'].items(), key=lambda x: x[1]['hours'], reverse=True)[:15] total_prom_sem = metrics['total'] / weeks_in_period total_ingresos = metrics['total_ingresos'] total_pagados = metrics['total_ingresos_pagados'] total_pendientes = metrics['total_ingresos_pendientes'] pct_cobrado = (total_pagados / total_ingresos * 100) if total_ingresos > 0 else 0 valor_hora_promedio = (total_ingresos / metrics['external']) if metrics['external'] > 0 else 0 horas_promedio_semana = metrics['total'] / weeks_in_period if weeks_in_period > 0 else 0 ingresos_sorted = sorted(client_rows, key=lambda c: c['ingresos'], reverse=True) ingresos_con_datos = [c for c in ingresos_sorted if c['ingresos'] > 0] subtitle_text = f"Enero — Diciembre {year}" if max_month == 12 else f"Enero — Junio {year}" period_label = f"{year}" if max_month == 12 else f"H1 {year}" months_count = max_month logo_svg = '''''' html = f''' Informe {period_label} — Bloom

Informe de Horas {period_label}

{subtitle_text}
Total horas equipo
{format_num(metrics["total"], 0).replace(',0', '')}
{total_persons} personas · {months_count} meses
Billable
{format_num(metrics["billable_pct"], 1)}%
{format_num(metrics["billable"], 0).replace(',0', '')} hrs facturables
Horas a clientes
{format_num(metrics["external"], 0).replace(',0', '')}
{format_num(metrics["external_pct"], 1)}% del total
Trabajo interno
{format_num(metrics["internal"], 0).replace(',0', '')}
{format_num(metrics["internal_pct"], 1)}% del total
Ingresos facturados
{format_clp(total_ingresos)}
{len(ingresos_con_datos)} clientes facturados
Valor hora promedio
{format_clp(total_ingresos / total_external if total_external > 0 else 0)}
Ingresos / horas externas
Horas promedio por semana
{format_num(metrics["total"] / weeks_in_period, 1)}
{format_num(weeks_in_period, 0)} semanas en el período
Ticket promedio
{format_clp(total_ingresos / sum(c['facturas'] for c in ingresos_con_datos) if ingresos_con_datos else 0)}
{sum(c['facturas'] for c in ingresos_con_datos)} facturas en total
Horas por persona
Horas externas Horas internas
''' for mn in month_names[:max_month]: html += f' \n' html += ''' ''' for pr in person_rows: badge_class = 'pct-high' if pr['internal_pct'] >= 20 else 'pct-low' html += f' \n \n' for m_val in pr['months']: html += f' \n' html += f''' ''' html += ' \n \n \n \n' for m in range(1, max_month + 1): html += f' \n' html += f'''
Persona{mn}Total Prom. sem. Prom. sem. ajustado Internas Externas
{pr["name"]}{format_num(m_val, 1)}{format_num(pr["total"], 1)} {format_num(pr["prom_sem"], 1)} {format_num(pr["prom_sem_ajustado"], 1)} {format_num(pr["internal"], 1)} {format_num(pr["internal_pct"], 0)}% {format_num(pr["external"], 1)}
Total{format_num(metrics["month_total"].get(m, 0), 1)}{format_num(metrics["total"], 1)} {format_num(total_prom_sem, 1)} — {format_num(metrics["internal"], 1)} {format_num(metrics["external"], 1)}

Valores en naranja indican caídas significativas respecto al promedio mensual. % interno = horas internas / total. Prom. sem.: base 52 semanas brutas. Prom. sem. ajustado: excluye semanas con 0 hrs registradas (vacaciones identificadas).

Volumen mensual
Externas Internas
''' for mt in month_totals: html += f' \n' html += f'''
MesTotal hrsBillableInternas
{mt["name"]}{format_num(mt["total"], 1)}{format_num(mt["billable_pct"], 0)}%{format_num(mt["internal_pct"], 0)}%
{period_label}{format_num(metrics["total"], 1)}{format_num(metrics["billable_pct"], 1)}%{format_num(metrics["internal_pct"], 1)}%
Clientes: horas e ingresos
''' for cr in ingresos_con_datos: html += f''' ''' total_facturas = sum(c['facturas'] for c in ingresos_con_datos) avg_dolar_hora = (total_ingresos / total_external) if total_external > 0 else 0 html += f'''
Cliente Horas % horas Ingresos $/hora Facturas
{cr["short_name"]} {format_num(cr["hours"], 1)} {format_num(cr["pct"], 1)}% {format_clp(cr["ingresos"])} {format_clp(cr["dolar_hora"])} {cr["facturas"]}
Total {format_num(total_external, 1)} 100% {format_clp(total_ingresos)} {format_clp(avg_dolar_hora)} {total_facturas}

$/hora = ingresos totales / horas externas por cliente.

Concentración de clientes externos
''' for cr in client_rows[:15]: html += f' \n' html += f'''
ClienteHoras% externo
{cr["name"]}{format_num(cr["hours"], 1)}{format_num(cr["pct"], 1)}%
Total externo{format_num(total_external, 1)}100%
{format_num(top3_pct, 1)}% de las horas externas en 3 clientes
Los 3 principales clientes concentran {format_num(top3_pct, 1)}% de la carga externa del periodo.
Tareas con mayor demanda de horas
Proyecto cliente Proyecto interno Bloom
''' return html def main(): year = int(sys.argv[1]) if len(sys.argv) > 1 else 2025 base = '/var/www/bloom-time-report' time_file = f'{base}/export.csv' if year == 2025 else f'{base}/{year}.csv' records = parse_time_csv(time_file, None) ingresos = parse_ingresos_csv(f'{base}/ingresos-{year}.csv', year) max_month = 12 if year == 2025 else 6 metrics = calculate_metrics(records, ingresos, year, max_month) html = generate_html(metrics) outpath = f'{base}/{year}.html' with open(outpath, 'w', encoding='utf-8') as f: f.write(html) print(f"Informe generado: {year}.html") print(f"Total registros {year}: {len(records)}") print(f"Total horas: {metrics['total']:.1f}") print(f"Billable: {metrics['billable_pct']:.1f}%") print(f"Internas: {metrics['internal']:.1f} ({metrics['internal_pct']:.1f}%)") print(f"Externas: {metrics['external']:.1f} ({metrics['external_pct']:.1f}%)") print(f"Ingresos: {format_clp(metrics['total_ingresos'])}") print(f"Cobrado: {format_clp(metrics['total_ingresos_pagados'])}") print(f"Pendiente: {format_clp(metrics['total_ingresos_pendientes'])}") if __name__ == '__main__': main()