rckrdmrd/erp-construccion-database-v2
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
main
erp-construccion-database-v2/seeds/dev/06-estimaciones.sql
Adrian Flores Cortes 6cd5507fc2 feat(seeds): Add comprehensive seed validation and new data files 
- Add 02b-hr-employees.sql: 15 puestos, 15 employees for HSE EPP assignments
- Add 11-infonavit.sql: registro, ofertas, derechohabientes, asignaciones
- Add validate-seeds-comprehensive.sql: FK integrity, calculations, coverage
- Update 06-estimaciones.sql: Document formula in header
- Update 08-hse.sql: Fix hse.capacitacion → hse.capacitaciones, add 6 new tables
- Update validate-seeds.sql: Fix table name reference

New tables seeded: tipos_inspeccion, inspecciones, epp_matriz_puesto,
epp_asignaciones, cumplimiento_obra, dias_sin_accidente

Coverage increased: HSE 7%→17%, INFONAVIT 0%→50%

Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
2026-02-03 11:31:59 -06:00

249 lines
14 KiB
PL/PgSQL
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

-- ═══════════════════════════════════════════════════════════════════════════════
-- SEED 06: CONTRATOS Y ESTIMACIONES
-- ═══════════════════════════════════════════════════════════════════════════════
-- Proyecto: ERP Construcción
-- Autor: Claude Opus 4.5
-- Fecha: 2026-02-03
-- Descripción: Contratos, estimaciones, generadores, anticipos, retenciones
-- Dependencias: 04-proyectos-obras.sql, 05-presupuestos.sql
--
-- FÓRMULA DE CÁLCULO ESTIMACIONES:
-- ────────────────────────────────────────────────────────────────────────────────
-- total_amount = subtotal + tax_amount - advance_amount - retention_amount
--
-- Donde:
-- subtotal = Monto base del trabajo ejecutado en el periodo
-- tax_amount = subtotal × 0.16 (IVA 16%)
-- advance_amount = subtotal × (advance_percentage / 100) [amortización anticipo]
-- retention_amount = subtotal × (retention_percentage / 100) [fondo de garantía]
--
-- Ejemplo verificación EST-2024-001-01:
-- subtotal = 350,000.00
-- tax = 350,000 × 0.16 = 56,000.00
-- advance = 350,000 × 0.30 = 105,000.00
-- retention = 350,000 × 0.05 = 17,500.00
-- total = 350,000 + 56,000 - 105,000 - 17,500 = 283,500.00 ✓
-- ═══════════════════════════════════════════════════════════════════════════════
-- UUIDs: 00000000-0000-0000-0006-XXXXXXXXXXXX
BEGIN;
-- ─────────────────────────────────────────────────────────────────────────────────
-- 1. CONTRATOS DE OBRA
-- ─────────────────────────────────────────────────────────────────────────────────
INSERT INTO construction.contratos (
id, tenant_id, subcontratista_id, fraccionamiento_id,
contract_number, name, contract_type,
start_date, end_date,
total_amount, advance_percentage, retention_percentage,
status, signed_at, signed_by,
created_at, created_by
) VALUES
-- Contrato Cimentaciones - Las Palmas
('00000000-0000-0000-0006-000000000001', '00000000-0000-0000-0003-000000000001',
'00000000-0000-0000-0004-000000000801', '00000000-0000-0000-0004-000000000101',
'CONT-2024-001', 'Cimentación Etapa 1 Las Palmas', 'unit_price',
'2024-03-01', '2024-08-31',
2500000.00, 30.00, 5.00,
'active', '2024-02-28', '00000000-0000-0000-0002-000000000102',
NOW(), '00000000-0000-0000-0002-000000000103'),
-- Contrato Instalaciones Eléctricas - Las Palmas
('00000000-0000-0000-0006-000000000002', '00000000-0000-0000-0003-000000000001',
'00000000-0000-0000-0004-000000000802', '00000000-0000-0000-0004-000000000101',
'CONT-2024-002', 'Instalaciones Eléctricas Etapa 1 Las Palmas', 'unit_price',
'2024-05-01', '2024-12-31',
1800000.00, 20.00, 5.00,
'active', '2024-04-25', '00000000-0000-0000-0002-000000000102',
NOW(), '00000000-0000-0000-0002-000000000103'),
-- Contrato Plomería - Las Palmas
('00000000-0000-0000-0006-000000000003', '00000000-0000-0000-0003-000000000001',
'00000000-0000-0000-0004-000000000803', '00000000-0000-0000-0004-000000000101',
'CONT-2024-003', 'Instalaciones Hidro-Sanitarias Etapa 1', 'unit_price',
'2024-05-15', '2025-01-31',
1650000.00, 20.00, 5.00,
'active', '2024-05-10', '00000000-0000-0000-0002-000000000102',
NOW(), '00000000-0000-0000-0002-000000000103'),
-- Contrato Acabados - Las Palmas
('00000000-0000-0000-0006-000000000004', '00000000-0000-0000-0003-000000000001',
'00000000-0000-0000-0004-000000000804', '00000000-0000-0000-0004-000000000101',
'CONT-2024-004', 'Acabados y Pintura Etapa 1 Las Palmas', 'unit_price',
'2024-07-01', '2025-03-31',
2200000.00, 15.00, 5.00,
'active', '2024-06-28', '00000000-0000-0000-0002-000000000102',
NOW(), '00000000-0000-0000-0002-000000000103'),
-- Contrato Estructura - Torre Diamante
('00000000-0000-0000-0006-000000000005', '00000000-0000-0000-0003-000000000001',
'00000000-0000-0000-0004-000000000801', '00000000-0000-0000-0004-000000000102',
'CONT-2024-005', 'Estructura Torre Diamante', 'fixed_price',
'2024-06-01', '2025-12-31',
8500000.00, 25.00, 5.00,
'active', '2024-05-28', '00000000-0000-0000-0002-000000000102',
NOW(), '00000000-0000-0000-0002-000000000103')
ON CONFLICT (id) DO NOTHING;
-- ─────────────────────────────────────────────────────────────────────────────────
-- 2. ESTIMACIONES
-- ─────────────────────────────────────────────────────────────────────────────────
INSERT INTO estimates.estimaciones (
id, tenant_id, contrato_id, fraccionamiento_id,
estimate_number, period_start, period_end, sequence_number,
status, subtotal, advance_amount, retention_amount, tax_amount, total_amount,
submitted_at, submitted_by, approved_at, approved_by,
created_at, created_by
) VALUES
-- Estimaciones del Contrato Cimentaciones
('00000000-0000-0000-0006-000000000101', '00000000-0000-0000-0003-000000000001',
'00000000-0000-0000-0006-000000000001', '00000000-0000-0000-0004-000000000101',
'EST-2024-001-01', '2024-03-01', '2024-03-15', 1,
'paid', 350000.00, 105000.00, 17500.00, 56000.00, 283500.00,
'2024-03-18', '00000000-0000-0000-0002-000000000104',
'2024-03-20', '00000000-0000-0000-0002-000000000103',
NOW(), '00000000-0000-0000-0002-000000000104'),
('00000000-0000-0000-0006-000000000102', '00000000-0000-0000-0003-000000000001',
'00000000-0000-0000-0006-000000000001', '00000000-0000-0000-0004-000000000101',
'EST-2024-001-02', '2024-03-16', '2024-03-31', 2,
'paid', 420000.00, 126000.00, 21000.00, 67200.00, 340200.00,
'2024-04-03', '00000000-0000-0000-0002-000000000104',
'2024-04-05', '00000000-0000-0000-0002-000000000103',
NOW(), '00000000-0000-0000-0002-000000000104'),
('00000000-0000-0000-0006-000000000103', '00000000-0000-0000-0003-000000000001',
'00000000-0000-0000-0006-000000000001', '00000000-0000-0000-0004-000000000101',
'EST-2024-001-03', '2024-04-01', '2024-04-15', 3,
'approved', 380000.00, 114000.00, 19000.00, 60800.00, 307800.00,
'2024-04-18', '00000000-0000-0000-0002-000000000104',
'2024-04-20', '00000000-0000-0000-0002-000000000103',
NOW(), '00000000-0000-0000-0002-000000000104'),
('00000000-0000-0000-0006-000000000104', '00000000-0000-0000-0003-000000000001',
'00000000-0000-0000-0006-000000000001', '00000000-0000-0000-0004-000000000101',
'EST-2024-001-04', '2024-04-16', '2024-04-30', 4,
'submitted', 450000.00, 135000.00, 22500.00, 72000.00, 364500.00,
'2024-05-03', '00000000-0000-0000-0002-000000000104',
NULL, NULL,
NOW(), '00000000-0000-0000-0002-000000000104'),
-- Estimaciones del Contrato Eléctrico
('00000000-0000-0000-0006-000000000105', '00000000-0000-0000-0003-000000000001',
'00000000-0000-0000-0006-000000000002', '00000000-0000-0000-0004-000000000101',
'EST-2024-002-01', '2024-05-01', '2024-05-15', 1,
'paid', 280000.00, 56000.00, 14000.00, 44800.00, 254800.00,
'2024-05-18', '00000000-0000-0000-0002-000000000104',
'2024-05-20', '00000000-0000-0000-0002-000000000103',
NOW(), '00000000-0000-0000-0002-000000000104'),
('00000000-0000-0000-0006-000000000106', '00000000-0000-0000-0003-000000000001',
'00000000-0000-0000-0006-000000000002', '00000000-0000-0000-0004-000000000101',
'EST-2024-002-02', '2024-05-16', '2024-05-31', 2,
'approved', 320000.00, 64000.00, 16000.00, 51200.00, 291200.00,
'2024-06-03', '00000000-0000-0000-0002-000000000104',
'2024-06-05', '00000000-0000-0000-0002-000000000103',
NOW(), '00000000-0000-0000-0002-000000000104')
ON CONFLICT (id) DO NOTHING;
-- ─────────────────────────────────────────────────────────────────────────────────
-- 3. ANTICIPOS
-- ─────────────────────────────────────────────────────────────────────────────────
INSERT INTO estimates.anticipos (
id, tenant_id, contrato_id,
advance_type, advance_number, advance_date,
gross_amount, tax_amount, net_amount,
amortization_percentage, amortized_amount,
is_fully_amortized,
approved_at, approved_by, paid_at, payment_reference,
created_at, created_by
) VALUES
-- Anticipo Contrato Cimentaciones (30%)
('00000000-0000-0000-0006-000000000201', '00000000-0000-0000-0003-000000000001',
'00000000-0000-0000-0006-000000000001',
'initial', 'ANT-2024-001-01', '2024-02-28',
750000.00, 120000.00, 870000.00,
30.00, 480000.00, false,
'2024-02-28', '00000000-0000-0000-0002-000000000102',
'2024-03-01', 'TRANSF-BBVA-001',
NOW(), '00000000-0000-0000-0002-000000000103'),
-- Anticipo Contrato Eléctrico (20%)
('00000000-0000-0000-0006-000000000202', '00000000-0000-0000-0003-000000000001',
'00000000-0000-0000-0006-000000000002',
'initial', 'ANT-2024-002-01', '2024-04-25',
360000.00, 57600.00, 417600.00,
20.00, 120000.00, false,
'2024-04-25', '00000000-0000-0000-0002-000000000102',
'2024-04-26', 'TRANSF-BBVA-002',
NOW(), '00000000-0000-0000-0002-000000000103'),
-- Anticipo Contrato Estructura Torre (25%)
('00000000-0000-0000-0006-000000000203', '00000000-0000-0000-0003-000000000001',
'00000000-0000-0000-0006-000000000005',
'initial', 'ANT-2024-005-01', '2024-05-28',
2125000.00, 340000.00, 2465000.00,
25.00, 0.00, false,
'2024-05-28', '00000000-0000-0000-0002-000000000102',
'2024-05-30', 'TRANSF-BBVA-005',
NOW(), '00000000-0000-0000-0002-000000000103')
ON CONFLICT (id) DO NOTHING;
-- ─────────────────────────────────────────────────────────────────────────────────
-- 4. FONDO DE GARANTÍA
-- ─────────────────────────────────────────────────────────────────────────────────
INSERT INTO estimates.fondo_garantia (
id, tenant_id, contrato_id,
accumulated_amount, released_amount,
created_at, created_by
) VALUES
('00000000-0000-0000-0006-000000000301', '00000000-0000-0000-0003-000000000001',
'00000000-0000-0000-0006-000000000001',
80000.00, 0.00, NOW(), '00000000-0000-0000-0002-000000000103'),
('00000000-0000-0000-0006-000000000302', '00000000-0000-0000-0003-000000000001',
'00000000-0000-0000-0006-000000000002',
30000.00, 0.00, NOW(), '00000000-0000-0000-0002-000000000103'),
('00000000-0000-0000-0006-000000000303', '00000000-0000-0000-0003-000000000001',
'00000000-0000-0000-0006-000000000005',
0.00, 0.00, NOW(), '00000000-0000-0000-0002-000000000103')
ON CONFLICT (id) DO NOTHING;
-- ─────────────────────────────────────────────────────────────────────────────────
-- VERIFICACIÓN
-- ─────────────────────────────────────────────────────────────────────────────────
DO $$
DECLARE
v_contratos INTEGER;
v_estimaciones INTEGER;
v_anticipos INTEGER;
v_fondos INTEGER;
BEGIN
SELECT COUNT(*) INTO v_contratos FROM construction.contratos;
SELECT COUNT(*) INTO v_estimaciones FROM estimates.estimaciones;
SELECT COUNT(*) INTO v_anticipos FROM estimates.anticipos;
SELECT COUNT(*) INTO v_fondos FROM estimates.fondo_garantia;
RAISE NOTICE '══════════════════════════════════════════════════════════════';
RAISE NOTICE 'SEED 06 - ESTIMACIONES - COMPLETADO';
RAISE NOTICE '══════════════════════════════════════════════════════════════';
RAISE NOTICE 'Contratos: %', v_contratos;
RAISE NOTICE 'Estimaciones: %', v_estimaciones;
RAISE NOTICE 'Anticipos: %', v_anticipos;
RAISE NOTICE 'Fondos de garantía: %', v_fondos;
RAISE NOTICE '══════════════════════════════════════════════════════════════';
END $$;
COMMIT;
-- ═══════════════════════════════════════════════════════════════════════════════
-- FIN SEED 06
-- ═══════════════════════════════════════════════════════════════════════════════
Reference in New Issue View Git Blame Copy Permalink