/
home
/
suroeste
/
public_html
/
payments.transportessuroeste.com
/
scripts
/
/home/suroeste/public_html/payments.transportessuroeste.com/scripts
mkdir
upload
Name
Size
Mode
Actions
scripts/
-
0755
rm
backup.sh
2638
0644
edit
dl
rm
deploy.sh
4127
0644
edit
dl
rm
generate_report.py
30839
0644
edit
dl
rm
Edit:
/home/suroeste/public_html/payments.transportessuroeste.com/scripts/generate_report.py
(30839B)
#!/usr/bin/env python3 """ Transportes Suroeste - Generador de Reporte de Estimacion de Horas Version 2.0 - Recalculo con criterios realistas mercado colombiano """ import openpyxl from openpyxl.styles import Font, PatternFill, Alignment, Border, Side from openpyxl.utils import get_column_letter from datetime import date # ============================================================================ # CONSTANTS # ============================================================================ HOURLY_RATE = 75000 # COP PROJECT_NAME = "Transportes Suroeste - API Pasarela de Pagos" ANALYSIS_DATE = date.today().strftime("%Y-%m-%d") OUTPUT_FILE = "/home/santiago/transportes-suroeste/transportes_suroeste_estimacion_horas_2026-02-02.xlsx" # Overhead realista (25% total) TESTING_PCT = 0.10 DOCS_PCT = 0.05 REFINEMENT_PCT = 0.10 # ============================================================================ # STYLES # ============================================================================ HEADER_FONT = Font(name="Calibri", bold=True, color="FFFFFF", size=11) HEADER_FILL = PatternFill(start_color="1F3864", end_color="1F3864", fill_type="solid") TOTAL_FONT = Font(name="Calibri", bold=True, size=11) TOTAL_FILL = PatternFill(start_color="FFF2CC", end_color="FFF2CC", fill_type="solid") TITLE_FONT = Font(name="Calibri", bold=True, size=14, color="1F3864") SUBTITLE_FONT = Font(name="Calibri", bold=True, size=12, color="2E75B6") LABEL_FONT = Font(name="Calibri", bold=True, size=11) VALUE_FONT = Font(name="Calibri", size=11) THIN_BORDER = Border( left=Side(style="thin"), right=Side(style="thin"), top=Side(style="thin"), bottom=Side(style="thin"), ) CENTER = Alignment(horizontal="center", vertical="center", wrap_text=True) LEFT = Alignment(horizontal="left", vertical="center", wrap_text=True) RIGHT = Alignment(horizontal="right", vertical="center") GRAND_TOTAL_FILL = PatternFill(start_color="D6E4F0", end_color="D6E4F0", fill_type="solid") GRAND_TOTAL_FONT = Font(name="Calibri", bold=True, size=12, color="1F3864") NOTE_FONT = Font(name="Calibri", italic=True, size=10, color="666666") COP_FORMAT = '$#,##0' HOURS_FORMAT = '0.0' def apply_header_style(ws, row, cols): for col in range(1, cols + 1): cell = ws.cell(row=row, column=col) cell.font = HEADER_FONT cell.fill = HEADER_FILL cell.alignment = CENTER cell.border = THIN_BORDER def apply_row_style(ws, row, cols, is_total=False, is_grand_total=False): for col in range(1, cols + 1): cell = ws.cell(row=row, column=col) cell.border = THIN_BORDER if is_grand_total: cell.font = GRAND_TOTAL_FONT cell.fill = GRAND_TOTAL_FILL elif is_total: cell.font = TOTAL_FONT cell.fill = TOTAL_FILL else: cell.font = VALUE_FONT def set_col_widths(ws, widths): for i, w in enumerate(widths, 1): ws.column_dimensions[get_column_letter(i)].width = w def write_table(ws, headers, data, start_row, col_widths=None, money_cols=None, hours_cols=None): money_cols = money_cols or [] hours_cols = hours_cols or [] for c, h in enumerate(headers, 1): ws.cell(row=start_row, column=c, value=h) apply_header_style(ws, start_row, len(headers)) for r, row_data in enumerate(data, start_row + 1): for c, val in enumerate(row_data, 1): cell = ws.cell(row=r, column=c, value=val) if c in money_cols and isinstance(val, (int, float)): cell.number_format = COP_FORMAT cell.alignment = RIGHT elif c in hours_cols and isinstance(val, (int, float)): cell.number_format = HOURS_FORMAT cell.alignment = RIGHT elif isinstance(val, (int, float)): cell.alignment = RIGHT else: cell.alignment = LEFT apply_row_style(ws, r, len(headers)) if col_widths: set_col_widths(ws, col_widths) return start_row + len(data) + 1 # ============================================================================ # DATA - MODULOS (horas directas, sin multiplicadores inflados) # ============================================================================ # (nombre, descripcion, archivos, complejidad, horas_directas, justificacion) modules = [ ( "Core / Configuracion", "config.php, .env parser, PSR-4 autoloading, composer, constantes", "config.php, composer.json, .env.example, index.php (root)", "Baja", 2.5, "Setup estandar PHP: config con getenv(), autoload PSR-4, composer.json basico" ), ( "Base de Datos", "12 tablas con FK, 2 vistas, 2 stored procedures, 2 triggers, 2 eventos, indices", "database.sql (579 LOC), Database.php", "Media", 3.0, "Schema relacional con SPs y triggers; Database.php es singleton PDO con transacciones" ), ( "Router y Front Controller", "Routing regex con parametros nombrados, middleware pipeline, CORS, error handler", "public/index.php, Router.php, JsonResponse.php", "Baja", 2.5, "Router custom simple (~87 LOC), front controller con middleware chain" ), ( "Autenticacion API", "API Key + HMAC-SHA512, validacion timestamp anti-replay, IP whitelist", "AuthMiddleware.php", "Media", 4.0, "Autenticacion custom con HMAC y anti-replay; no es OAuth ni JWT, pero incluye IP whitelist" ), ( "Rate Limiting", "Redis INCR atomico + TTL como backend primario, MySQL fallback, bloqueo IP, headers", "RateLimiter.php", "Media", 3.5, "Dual backend Redis/MySQL; la logica Redis es simple (INCR+EXPIRE), MySQL replica patron" ), ( "Security Middleware", "Deteccion SQLi (8 regex), XSS (6 regex), bots, path traversal, CSRF, sanitizacion", "SecurityMiddleware.php (285 LOC)", "Media", 4.5, "Muchos patrones regex pero son listas conocidas; sanitizacion de 5 tipos de input" ), ( "Servicio de Cifrado", "AES-256-GCM con IV+TAG+HMAC, Argon2id, firma HMAC-SHA512, UUID v4", "EncryptionService.php", "Media", 3.0, "Wrappers sobre funciones nativas PHP (openssl, password_hash, random_bytes)" ), ( "Servicio de Logging", "5 canales (transactions, security, audit, errors, access), archivo + BD, redaccion", "LogService.php", "Baja", 2.5, "Logging a archivo + inserts BD; sanitizacion de datos sensibles con regex" ), ( "Servicio Redis", "Singleton, get/set/incr/expire/ttl/del/exists, serializacion JSON, graceful fallback", "RedisService.php", "Baja", 1.5, "Wrapper basico sobre phpredis con deteccion de disponibilidad" ), ( "Integracion ePayco", "Smart Checkout v2 via Apify, login Basic Auth, session, payment, webhook SHA256, dispatch", "PaymentService.php (482 LOC), ApiController.php (326 LOC)", "Alta", 7.0, "Componente mas complejo: integracion API externa, manejo sesiones, webhook validation" ), ( "Validacion de Datos", "12 metodos validacion, validatePayment, validateQuery", "Validator.php", "Baja", 2.0, "Validadores estandar: required, email, numeric, min/max, regex; nada custom" ), ( "Pagina de Checkout", "Integracion ePayco JS SDK, sesion handling, validacion parametros, loading overlay", "checkout.php", "Baja", 2.0, "Vista HTML con JS SDK de ePayco; formulario simple con overlay de carga" ), ( "Pagina de Respuesta", "5 estados de pago con UI diferenciada, auto-refresh pending, detalles transaccion", "response.php", "Baja", 1.5, "Vista HTML con switch de estados; auto-refresh es un meta tag simple" ), ( "Diseno UI/CSS", "CSS variables, gradients, animaciones, responsive mobile, spinner, cards", "payment-response.css (513 LOC)", "Baja", 2.0, "CSS moderno pero estandar; sin frameworks, solo 2 paginas a estilizar" ), ( "Sistema de Excepciones", "8 clases exception: Api, Auth, Database, Payment, Validation, RateLimit, Security + base", "8 archivos en src/Exceptions/", "Baja", 0.5, "Boilerplate puro: 8 clases que solo extienden Exception con mensaje custom" ), ( "Dockerizacion", "Multi-stage Dockerfile, 3 docker-compose, Nginx, PHP-FPM, MySQL tuning, Redis, redes", "Dockerfile, 3x compose, nginx.conf, default.conf, php.ini, php-fpm.conf, my.cnf", "Media-Alta", 5.0, "Infra Docker completa con security hardening, healthchecks, redes aisladas" ), ( "DevOps / Scripts", "Makefile 25+ targets, backup.sh con retencion, deploy.sh con rollback y health check", "Makefile, backup.sh, deploy.sh", "Media", 3.5, "Automatizacion de operaciones; muchos targets pero son comandos docker/mysql directos" ), ( "Documentacion", "API docs HTML, diagramas HTML, funcional MD ~1000 LOC con Mermaid, README, CHANGELOG, CONTRIBUTING", "6 archivos en docs/ y raiz", "Media", 4.5, "Documentacion completa con diagramas Mermaid; funcional.md es el mas extenso" ), ] # ============================================================================ # CALCULATIONS # ============================================================================ base_dev_hours = sum(m[4] for m in modules) # 55.0h testing_hours = round(base_dev_hours * TESTING_PCT, 1) # 5.5h docs_hours = round(base_dev_hours * DOCS_PCT, 1) # 2.8h -> 3.0h refinement_hours = round(base_dev_hours * REFINEMENT_PCT, 1) # 5.5h overhead_hours = testing_hours + docs_hours + refinement_hours total_hours = round(base_dev_hours + overhead_hours, 1) total_value = round(total_hours * HOURLY_RATE) # ============================================================================ # WORK TYPE BREAKDOWN (vista alternativa por tipo de trabajo) # ============================================================================ # (categoria, subcategoria, cantidad, horas_unitarias, horas_total) work_types = [ ("Backend", "Configuracion proyecto (PSR-4, env, autoload)", 1, 2.5, 2.5), ("Backend", "API Endpoints REST (6 endpoints)", 6, 0.75, 4.5), ("Backend", "Logica de pagos ePayco (Apify v2)", 1, 5.0, 5.0), ("Backend", "Cifrado AES-256-GCM + Argon2id", 1, 3.0, 3.0), ("Backend", "Logging multi-canal (5 canales + BD)", 1, 2.5, 2.5), ("Backend", "Autenticacion API Key + HMAC-SHA512", 1, 4.0, 4.0), ("Backend", "Rate Limiting (Redis + MySQL fallback)", 1, 3.5, 3.5), ("Backend", "Security Middleware (SQLi/XSS/CSRF)", 1, 4.5, 4.5), ("Backend", "Validaciones de datos (12 metodos)", 1, 2.0, 2.0), ("Backend", "Servicio Redis (singleton + CRUD)", 1, 1.5, 1.5), ("Backend", "Excepciones personalizadas", 8, 0.06, 0.5), ("Backend", "Webhook processing + dispatch", 1, 2.0, 2.0), ("Frontend", "Pagina Checkout (ePayco JS SDK)", 1, 2.0, 2.0), ("Frontend", "Pagina Respuesta de Pago", 1, 1.5, 1.5), ("Frontend", "Diseno CSS responsive", 1, 2.0, 2.0), ("Base de Datos", "Schema (12 tablas, FK, indices)", 1, 2.0, 2.0), ("Base de Datos", "Vistas, SPs, triggers, eventos", 1, 1.0, 1.0), ("DevOps", "Dockerfile multi-stage + PHP config", 1, 1.5, 1.5), ("DevOps", "Docker Compose (base + dev + prod)", 3, 0.5, 1.5), ("DevOps", "Nginx configuracion segura", 1, 1.0, 1.0), ("DevOps", "Makefile + scripts (backup, deploy)", 1, 2.5, 2.5), ("Documentacion", "Docs tecnica y funcional completa", 1, 4.5, 4.5), ] work_base_total = sum(w[4] for w in work_types) # ============================================================================ # FILE INVENTORY (sin cambios) # ============================================================================ files = [ ("config.php", "config/", "PHP", 164, "Core / Configuracion"), (".env.example", "config/", "Config", 42, "Core / Configuracion"), ("index.php (public)", "public/", "PHP", 220, "Router y Front Controller"), ("Router.php", "src/Utils/", "PHP", 87, "Router y Front Controller"), ("JsonResponse.php", "src/Utils/", "PHP", 71, "Router y Front Controller"), ("ApiController.php", "src/Controllers/", "PHP", 326, "Integracion ePayco"), ("PaymentService.php", "src/Services/", "PHP", 482, "Integracion ePayco"), ("EncryptionService.php", "src/Services/", "PHP", 129, "Servicio de Cifrado"), ("LogService.php", "src/Services/", "PHP", 205, "Servicio de Logging"), ("RedisService.php", "src/Services/", "PHP", 193, "Servicio Redis"), ("AuthMiddleware.php", "src/Middleware/", "PHP", 158, "Autenticacion API"), ("RateLimiter.php", "src/Middleware/", "PHP", 264, "Rate Limiting"), ("SecurityMiddleware.php", "src/Middleware/", "PHP", 285, "Security Middleware"), ("Validator.php", "src/Validators/", "PHP", 192, "Validacion de Datos"), ("Database.php", "src/Models/", "PHP", 230, "Base de Datos"), ("checkout.php", "public/payment/", "PHP", 164, "Pagina de Checkout"), ("response.php (public)", "public/payment/", "PHP", 160, "Pagina de Respuesta"), ("response.php (src)", "src/payment/", "PHP", 403, "Pagina de Respuesta"), ("Exceptions.php", "src/Exceptions/", "PHP", 0, "Excepciones"), ("ApiException.php", "src/Exceptions/", "PHP", 24, "Excepciones"), ("AuthenticationException.php", "src/Exceptions/", "PHP", 16, "Excepciones"), ("DatabaseException.php", "src/Exceptions/", "PHP", 16, "Excepciones"), ("PaymentException.php", "src/Exceptions/", "PHP", 24, "Excepciones"), ("ValidationException.php", "src/Exceptions/", "PHP", 24, "Excepciones"), ("RateLimitException.php", "src/Exceptions/", "PHP", 24, "Excepciones"), ("SecurityException.php", "src/Exceptions/", "PHP", 16, "Excepciones"), ("epayco-check.php", "public/", "PHP", 5, "Core / Configuracion"), ("index.php (root)", "./", "PHP", 4, "Core / Configuracion"), ("database.sql", "sql/", "SQL", 579, "Base de Datos"), ("database_xampp.sql", "src/Exceptions/", "SQL", 407, "Base de Datos"), ("payment-response.css", "public/assets/css/", "CSS", 513, "Diseno UI/CSS"), ("DOCUMENTACION.html", "docs/", "HTML", 328, "Documentacion"), ("DIAGRAMAS.html", "docs/", "HTML", 288, "Documentacion"), ("DOCUMENTACION_FUNCIONAL.md", "docs/", "Markdown", 993, "Documentacion"), ("README.md", "./", "Markdown", 186, "Documentacion"), ("CHANGELOG.md", "./", "Markdown", 54, "Documentacion"), ("CONTRIBUTING.md", "./", "Markdown", 90, "Documentacion"), ("Dockerfile", "docker/php/", "Docker", 100, "Dockerizacion"), ("docker-compose.yml", "./", "YAML", 205, "Dockerizacion"), ("docker-compose.override.yml", "./", "YAML", 91, "Dockerizacion"), ("docker-compose.prod.yml", "./", "YAML", 110, "Dockerizacion"), ("nginx.conf", "docker/nginx/", "Config", 96, "Dockerizacion"), ("default.conf", "docker/nginx/sites/", "Config", 140, "Dockerizacion"), ("php.ini", "docker/php/", "Config", 103, "Dockerizacion"), ("php-fpm.conf", "docker/php/", "Config", 42, "Dockerizacion"), ("my.cnf", "docker/mysql/", "Config", 62, "Dockerizacion"), ("Makefile", "./", "Make", 165, "DevOps / Scripts"), ("backup.sh", "scripts/", "Shell", 84, "DevOps / Scripts"), ("deploy.sh", "scripts/", "Shell", 139, "DevOps / Scripts"), ("install.sh", "./", "Shell", 110, "DevOps / Scripts"), (".htaccess", "public/", "Apache", 43, "Core / Configuracion"), ("composer.json", "./", "JSON", 25, "Core / Configuracion"), ] # ============================================================================ # LINES OF CODE (sin cambios) # ============================================================================ loc_summary = [ ("PHP", 27, 3886), ("SQL", 2, 986), ("CSS", 1, 513), ("HTML", 2, 616), ("Markdown", 4, 1323), ("Docker/YAML", 4, 406), ("Config (Nginx/PHP/MySQL)", 5, 443), ("Shell Scripts", 4, 498), ("Make/Apache/JSON", 3, 233), ] # ============================================================================ # FUNCTIONALITIES (horas realistas por funcionalidad) # ============================================================================ functionalities = [ ("F01", "Crear transaccion de pago", "Integracion ePayco", "Completo", 3.0, "POST /api/v1/payments - crea sesion Smart Checkout v2 via Apify"), ("F02", "Consultar transaccion por UUID", "Integracion ePayco", "Completo", 0.5, "GET /api/v1/payments/{uuid} - query simple a BD"), ("F03", "Consultar transaccion por ticket", "Integracion ePayco", "Completo", 0.5, "GET /api/v1/payments/ticket/{ref} - query simple a BD"), ("F04", "Listar bancos PSE", "Integracion ePayco", "Completo", 0.5, "GET /api/v1/banks/pse - proxy directo al SDK ePayco"), ("F05", "Procesar webhook ePayco", "Integracion ePayco", "Completo", 2.5, "Validacion firma SHA256, IP whitelist, update BD, dispatch a cliente"), ("F06", "Pagina de checkout", "Pagina de Checkout", "Completo", 2.0, "HTML + ePayco JS SDK, sesion handling, UI responsive"), ("F07", "Pagina de respuesta", "Pagina de Respuesta", "Completo", 1.5, "5 estados HTML, auto-refresh con meta tag, detalles de pago"), ("F08", "Autenticacion API Key + HMAC", "Autenticacion API", "Completo", 2.5, "Validacion API Key, firma HMAC-SHA512, timestamp anti-replay"), ("F09", "IP Whitelisting", "Autenticacion API", "Completo", 0.5, "Validacion array de IPs permitidas por cliente"), ("F10", "Rate Limiting Redis", "Rate Limiting", "Completo", 2.0, "INCR atomico + TTL por ventana, headers X-RateLimit"), ("F11", "Rate Limiting MySQL (fallback)", "Rate Limiting", "Completo", 1.5, "Ventanas en tabla rate_limits, cleanup automatico"), ("F12", "Deteccion SQL Injection", "Security Middleware", "Completo", 1.0, "8 patrones regex aplicados a URI y body"), ("F13", "Deteccion XSS", "Security Middleware", "Completo", 0.5, "6 patrones regex - patron similar a SQLi detection"), ("F14", "Deteccion bots maliciosos", "Security Middleware", "Completo", 0.5, "Blacklist de User-Agents conocidos: sqlmap, nikto, nmap"), ("F15", "Proteccion Path Traversal", "Security Middleware", "Completo", 0.5, "Regex simple para ../ en URI"), ("F16", "Sanitizacion de datos", "Security Middleware", "Completo", 1.5, "5 tipos: array, string, email, document, phone"), ("F17", "CSRF Token", "Security Middleware", "Completo", 0.5, "Token en session con expiracion 1h - patron estandar"), ("F18", "Cifrado AES-256-GCM", "Servicio de Cifrado", "Completo", 2.0, "Wrapper sobre openssl_encrypt con IV + TAG + HMAC"), ("F19", "Hashing Argon2id", "Servicio de Cifrado", "Completo", 0.5, "Wrapper sobre password_hash() nativo de PHP"), ("F20", "Firma HMAC-SHA512", "Servicio de Cifrado", "Completo", 0.5, "Wrapper sobre hash_hmac() nativo"), ("F21", "Generacion UUID v4", "Servicio de Cifrado", "Completo", 0.5, "random_bytes() + formateo RFC 4122"), ("F22", "Logging multi-canal", "Servicio de Logging", "Completo", 1.5, "5 canales con escritura a archivos separados"), ("F23", "Logging a BD", "Servicio de Logging", "Completo", 1.0, "Inserts a transaction_logs, security_events, audit_trail"), ("F24", "Sanitizacion datos sensibles en logs", "Servicio de Logging", "Completo", 0.5, "Regex para redactar passwords, tokens, tarjetas"), ("F25", "Webhook dispatch a clientes", "Integracion ePayco", "Parcial", 1.0, "Registro en BD; sin worker de reintentos implementado"), ("F26", "Health Check", "Core / Configuracion", "Completo", 0.5, "GET /api/v1/health - endpoint trivial"), ("F27", "Database abstraction layer", "Base de Datos", "Completo", 2.5, "Singleton PDO con query/insert/update/delete, transacciones anidadas con savepoints"), ("F28", "Conciliacion diaria (SP)", "Base de Datos", "Completo", 1.0, "Stored procedure + evento programado a las 2AM"), ("F29", "Cleanup rate limits (SP)", "Base de Datos", "Completo", 0.5, "SP simple + evento cada hora"), ("F30", "Dockerizacion completa", "Dockerizacion", "Completo", 5.0, "Multi-stage build, 4 servicios, security hardening, healthchecks"), ("F31", "Backup automatizado", "DevOps / Scripts", "Completo", 1.0, "mysqldump + gzip + retencion 30 dias"), ("F32", "Deploy script", "DevOps / Scripts", "Completo", 1.5, "Backup previo, rebuild, health check con reintentos"), ("F33", "Documentacion tecnica", "Documentacion", "Completo", 4.5, "API HTML, diagramas, funcional MD ~1000 LOC, README, CHANGELOG"), ] # ============================================================================ # CREATE WORKBOOK # ============================================================================ wb = openpyxl.Workbook() # ============================================================================ # SHEET 1: Resumen Ejecutivo # ============================================================================ ws1 = wb.active ws1.title = "Resumen Ejecutivo" ws1.sheet_properties.tabColor = "1F3864" ws1.merge_cells("A1:D1") ws1.cell(row=1, column=1, value="ESTIMACION DE HORAS - REPORTE EJECUTIVO").font = TITLE_FONT ws1.merge_cells("A2:D2") ws1.cell(row=2, column=1, value=PROJECT_NAME).font = SUBTITLE_FONT ws1.row_dimensions[1].height = 30 ws1.row_dimensions[2].height = 25 summary_data = [ ("Nombre del Proyecto", PROJECT_NAME), ("Fecha de Analisis", ANALYSIS_DATE), ("Metodologia", "Estimacion realista - mercado colombiano"), ("Total de Modulos", len(modules)), ("Total de Funcionalidades", len(functionalities)), ("Total Archivos (sin vendor)", len(files)), ("Total Lineas de Codigo", sum(l[2] for l in loc_summary)), ("Clasificacion del Proyecto", "Pequeno-Mediano (API de pagos)"), ("", ""), ("DESGLOSE DE HORAS", ""), ("Horas Desarrollo Base", base_dev_hours), (f"Horas Testing ({int(TESTING_PCT*100)}%)", testing_hours), (f"Horas Documentacion ({int(DOCS_PCT*100)}%)", docs_hours), (f"Horas Refinamiento ({int(REFINEMENT_PCT*100)}%)", refinement_hours), ("Overhead Total (25%)", overhead_hours), ("", ""), ("TOTAL HORAS ESTIMADAS", total_hours), ("Valor Hora (COP)", HOURLY_RATE), ("VALOR TOTAL PROYECTO (COP)", total_value), ("", ""), ("NOTAS METODOLOGICAS", ""), ("- Sin multiplicadores inflados", "Horas directas por modulo, sin factores x1.6 o x2.0"), ("- Codigo repetido cuenta 1 vez", "Patrones similares (exceptions, validators) = tiempo minimo"), ("- Boilerplate y config basica", "Tiempo real de un dev senior, no tiempo teorico"), ("- Overhead conservador 25%", "Testing 10% + Docs 5% + Refinamiento 10%"), ] row = 4 for label, value in summary_data: c1 = ws1.cell(row=row, column=1, value=label) c2 = ws1.cell(row=row, column=2, value=value) c1.font = LABEL_FONT c1.border = THIN_BORDER c2.border = THIN_BORDER c2.font = VALUE_FONT if label == "": row += 1 continue if label.startswith("TOTAL") or label.startswith("VALOR TOTAL"): c1.fill = GRAND_TOTAL_FILL c2.fill = GRAND_TOTAL_FILL c1.font = GRAND_TOTAL_FONT c2.font = GRAND_TOTAL_FONT if isinstance(value, (int, float)) and value > 1000: c2.number_format = COP_FORMAT elif label.startswith("DESGLOSE") or label.startswith("NOTAS"): c1.font = SUBTITLE_FONT c2.font = VALUE_FONT elif label.startswith("-"): c1.font = NOTE_FONT c2.font = NOTE_FONT elif isinstance(value, (int, float)) and value > 1000: c2.number_format = COP_FORMAT row += 1 set_col_widths(ws1, [38, 52, 15, 15]) # ============================================================================ # SHEET 2: Detalle por Modulos # ============================================================================ ws2 = wb.create_sheet("Detalle por Modulos") ws2.sheet_properties.tabColor = "2E75B6" headers2 = ["Modulo", "Descripcion", "Archivos", "Complejidad", "Horas", "Valor COP", "Justificacion"] data2 = [] for m in modules: val = round(m[4] * HOURLY_RATE) data2.append([m[0], m[1], m[2], m[3], m[4], val, m[5]]) # Subtotal desarrollo data2.append([ "SUBTOTAL DESARROLLO", "", "", "", base_dev_hours, round(base_dev_hours * HOURLY_RATE), "Suma directa de horas por modulo" ]) # Overhead lines data2.append([ f"Testing ({int(TESTING_PCT*100)}%)", "Pruebas manuales y verificacion de endpoints", "-", "-", testing_hours, round(testing_hours * HOURLY_RATE), "10% del desarrollo base" ]) data2.append([ f"Documentacion ({int(DOCS_PCT*100)}%)", "Comentarios inline, ejemplos de uso", "-", "-", docs_hours, round(docs_hours * HOURLY_RATE), "5% - docs principales ya estan como modulo" ]) data2.append([ f"Refinamiento ({int(REFINEMENT_PCT*100)}%)", "Bug fixing, optimizacion, code review", "-", "-", refinement_hours, round(refinement_hours * HOURLY_RATE), "10% para ajustes post-desarrollo" ]) data2.append([ "TOTAL PROYECTO", "", "", "", total_hours, total_value, f"= {base_dev_hours}h base + {overhead_hours}h overhead (25%)" ]) write_table(ws2, headers2, data2, 1, col_widths=[26, 48, 40, 14, 10, 16, 50], money_cols=[6], hours_cols=[5]) # Style subtotal and grand total rows subtotal_row = 1 + len(modules) + 1 grand_total_row = 1 + len(data2) apply_row_style(ws2, subtotal_row, len(headers2), is_total=True) apply_row_style(ws2, grand_total_row, len(headers2), is_grand_total=True) # ============================================================================ # SHEET 3: Detalle por Tipo de Trabajo # ============================================================================ ws3 = wb.create_sheet("Detalle por Tipo Trabajo") ws3.sheet_properties.tabColor = "548235" headers3 = ["Categoria", "Subcategoria", "Cantidad", "Horas Unit.", "Horas Total", "Valor COP"] data3 = [] for w in work_types: val = round(w[4] * HOURLY_RATE) data3.append([w[0], w[1], w[2], w[3], w[4], val]) data3.append([ "SUBTOTAL DESARROLLO", "", "", "", work_base_total, round(work_base_total * HOURLY_RATE) ]) data3.append([ "Overhead", f"Testing ({int(TESTING_PCT*100)}%)", "-", "-", testing_hours, round(testing_hours * HOURLY_RATE) ]) data3.append([ "Overhead", f"Documentacion ({int(DOCS_PCT*100)}%)", "-", "-", docs_hours, round(docs_hours * HOURLY_RATE) ]) data3.append([ "Overhead", f"Refinamiento ({int(REFINEMENT_PCT*100)}%)", "-", "-", refinement_hours, round(refinement_hours * HOURLY_RATE) ]) work_grand_total = work_base_total + testing_hours + docs_hours + refinement_hours data3.append([ "TOTAL PROYECTO", "", "", "", work_grand_total, round(work_grand_total * HOURLY_RATE) ]) write_table(ws3, headers3, data3, 1, col_widths=[16, 45, 12, 12, 14, 20], money_cols=[6], hours_cols=[4, 5]) apply_row_style(ws3, 1 + len(work_types) + 1, len(headers3), is_total=True) apply_row_style(ws3, 1 + len(data3), len(headers3), is_grand_total=True) # ============================================================================ # SHEET 4: Inventario de Archivos # ============================================================================ ws4 = wb.create_sheet("Inventario de Archivos") ws4.sheet_properties.tabColor = "BF8F00" headers4 = ["Archivo", "Ruta", "Tipo", "Lineas de Codigo", "Modulo Asociado"] data4 = [list(f) for f in files] data4.append(["TOTAL", "", "", sum(f[3] for f in files), ""]) write_table(ws4, headers4, data4, 1, col_widths=[30, 25, 14, 18, 28]) apply_row_style(ws4, 1 + len(data4), len(headers4), is_total=True) # ============================================================================ # SHEET 5: Lineas de Codigo # ============================================================================ ws5 = wb.create_sheet("Lineas de Codigo") ws5.sheet_properties.tabColor = "C00000" headers5 = ["Tipo de Archivo", "Cantidad Archivos", "Total Lineas"] data5 = [list(l) for l in loc_summary] data5.append(["TOTAL", sum(l[1] for l in loc_summary), sum(l[2] for l in loc_summary)]) write_table(ws5, headers5, data5, 1, col_widths=[30, 20, 18]) apply_row_style(ws5, 1 + len(data5), len(headers5), is_total=True) # ============================================================================ # SHEET 6: Funcionalidades # ============================================================================ ws6 = wb.create_sheet("Funcionalidades") ws6.sheet_properties.tabColor = "7030A0" headers6 = ["ID", "Funcionalidad", "Modulo", "Estado", "Horas", "Observaciones"] data6 = [list(f) for f in functionalities] func_total_hours = sum(f[4] for f in functionalities) data6.append(["", "TOTAL FUNCIONALIDADES", "", "", func_total_hours, ""]) write_table(ws6, headers6, data6, 1, col_widths=[8, 38, 24, 12, 10, 55], hours_cols=[5]) apply_row_style(ws6, 1 + len(data6), len(headers6), is_total=True) # ============================================================================ # SAVE # ============================================================================ wb.save(OUTPUT_FILE) print(f"Excel generado: {OUTPUT_FILE}") print(f"") print(f"=== RESUMEN ===") print(f"Horas desarrollo base: {base_dev_hours}h") print(f" Testing ({int(TESTING_PCT*100)}%): {testing_hours}h") print(f" Documentacion ({int(DOCS_PCT*100)}%): {docs_hours}h") print(f" Refinamiento ({int(REFINEMENT_PCT*100)}%): {refinement_hours}h") print(f"Overhead total (25%): {overhead_hours}h") print(f"---") print(f"TOTAL HORAS: {total_hours}h") print(f"TOTAL COP: ${total_value:,.0f}") print(f"Rango objetivo: $5,000,000 - $6,000,000 COP")
Save
cmd:
run