362 lines
9.4 KiB
Markdown
362 lines
9.4 KiB
Markdown
# ET-EST-001: Modelo de Datos de Estimaciones
|
|
|
|
**ID:** ET-EST-001
|
|
**Módulo:** MAI-008
|
|
**Relacionado con:** RF-EST-001, RF-EST-002
|
|
|
|
---
|
|
|
|
## 📋 Schema de Base de Datos: `estimations`
|
|
|
|
### Tabla: estimations
|
|
|
|
```sql
|
|
CREATE TYPE estimations.estimation_type AS ENUM ('to_client', 'to_subcontractor');
|
|
CREATE TYPE estimations.estimation_status AS ENUM (
|
|
'draft', 'submitted', 'reviewed', 'authorized', 'paid', 'rejected', 'cancelled'
|
|
);
|
|
|
|
CREATE TABLE estimations.estimations (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
numero VARCHAR(50) UNIQUE NOT NULL, -- EST-PRJ001-CLI-2025-001
|
|
|
|
-- Relaciones
|
|
project_id UUID NOT NULL REFERENCES projects.projects(id),
|
|
contract_id UUID NOT NULL REFERENCES contracts.contracts(id),
|
|
client_id UUID REFERENCES clients.clients(id), -- Si es to_client
|
|
subcontractor_id UUID REFERENCES subcontractors.subcontractors(id), -- Si es to_subcontractor
|
|
|
|
-- Tipo y periodo
|
|
estimation_type estimations.estimation_type NOT NULL,
|
|
numero_estimacion INT NOT NULL, -- 1, 2, 3...
|
|
periodo_inicio DATE NOT NULL,
|
|
periodo_fin DATE NOT NULL,
|
|
|
|
-- Montos (en centavos para precision)
|
|
monto_bruto BIGINT NOT NULL,
|
|
amortizacion_anticipo BIGINT DEFAULT 0,
|
|
total_retenciones BIGINT DEFAULT 0,
|
|
monto_neto BIGINT NOT NULL,
|
|
|
|
-- Desglose retenciones
|
|
retencion_fondo_garantia BIGINT DEFAULT 0,
|
|
retencion_isr BIGINT DEFAULT 0,
|
|
retencion_iva BIGINT DEFAULT 0,
|
|
otras_retenciones BIGINT DEFAULT 0,
|
|
|
|
-- Estado
|
|
status estimations.estimation_status DEFAULT 'draft',
|
|
|
|
-- Fechas workflow
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
submitted_at TIMESTAMPTZ,
|
|
reviewed_at TIMESTAMPTZ,
|
|
authorized_at TIMESTAMPTZ,
|
|
paid_at TIMESTAMPTZ,
|
|
fecha_pago_estimada DATE,
|
|
fecha_pago_programada DATE, -- Para subcontratistas
|
|
|
|
-- Usuarios workflow
|
|
created_by UUID NOT NULL REFERENCES auth_management.users(id),
|
|
submitted_by UUID REFERENCES auth_management.users(id),
|
|
reviewed_by UUID REFERENCES auth_management.users(id),
|
|
authorized_by UUID REFERENCES auth_management.users(id),
|
|
|
|
-- Pagos
|
|
referencia_pago VARCHAR(100),
|
|
comprobante_pago TEXT,
|
|
|
|
-- Metadata
|
|
observaciones TEXT,
|
|
razon_rechazo TEXT,
|
|
documentos_pdf TEXT[],
|
|
documentos_excel TEXT[],
|
|
|
|
-- Audit
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_estimations_project ON estimations.estimations(project_id);
|
|
CREATE INDEX idx_estimations_contract ON estimations.estimations(contract_id);
|
|
CREATE INDEX idx_estimations_status ON estimations.estimations(status);
|
|
CREATE INDEX idx_estimations_type ON estimations.estimations(estimation_type);
|
|
CREATE INDEX idx_estimations_periodo ON estimations.estimations(periodo_inicio, periodo_fin);
|
|
CREATE INDEX idx_estimations_numero ON estimations.estimations(numero);
|
|
```
|
|
|
|
### Tabla: estimation_items
|
|
|
|
```sql
|
|
CREATE TABLE estimations.estimation_items (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
estimation_id UUID NOT NULL REFERENCES estimations.estimations(id) ON DELETE CASCADE,
|
|
|
|
-- Concepto
|
|
concept_catalog_id UUID NOT NULL, -- Link a catalog de conceptos
|
|
descripcion VARCHAR(500) NOT NULL,
|
|
unidad VARCHAR(50) NOT NULL,
|
|
precio_unitario BIGINT NOT NULL, -- En centavos
|
|
|
|
-- Cantidades
|
|
cantidad_contratada DECIMAL(15,4),
|
|
cantidad_estimada_anterior DECIMAL(15,4) DEFAULT 0,
|
|
cantidad_estimada_actual DECIMAL(15,4) NOT NULL,
|
|
cantidad_acumulada DECIMAL(15,4),
|
|
porcentaje_avance DECIMAL(5,2),
|
|
|
|
-- Importes
|
|
importe_actual BIGINT NOT NULL,
|
|
importe_acumulado BIGINT,
|
|
|
|
-- Trazabilidad
|
|
avance_obra_id UUID, -- Link a MAI-005
|
|
verificado_por UUID REFERENCES auth_management.users(id),
|
|
fecha_verificacion TIMESTAMPTZ,
|
|
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_estimation_items_estimation ON estimations.estimation_items(estimation_id);
|
|
CREATE INDEX idx_estimation_items_concept ON estimations.estimation_items(concept_catalog_id);
|
|
```
|
|
|
|
### Tabla: estimation_workflow_history
|
|
|
|
```sql
|
|
CREATE TABLE estimations.estimation_workflow_history (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
estimation_id UUID NOT NULL REFERENCES estimations.estimations(id),
|
|
|
|
from_status estimations.estimation_status,
|
|
to_status estimations.estimation_status NOT NULL,
|
|
|
|
user_id UUID NOT NULL REFERENCES auth_management.users(id),
|
|
user_role VARCHAR(50) NOT NULL,
|
|
|
|
timestamp TIMESTAMPTZ DEFAULT NOW(),
|
|
comentario TEXT,
|
|
razon_rechazo TEXT,
|
|
|
|
tiempo_respuesta INT -- Minutos desde transición anterior
|
|
);
|
|
|
|
CREATE INDEX idx_workflow_history_estimation ON estimations.estimation_workflow_history(estimation_id);
|
|
CREATE INDEX idx_workflow_history_timestamp ON estimations.estimation_workflow_history(timestamp DESC);
|
|
```
|
|
|
|
---
|
|
|
|
## 🔧 Backend: NestJS Entities
|
|
|
|
### estimation.entity.ts
|
|
|
|
```typescript
|
|
import { Entity, PrimaryGeneratedColumn, Column, ManyToOne, OneToMany } from 'typeorm';
|
|
|
|
export enum EstimationType {
|
|
TO_CLIENT = 'to_client',
|
|
TO_SUBCONTRACTOR = 'to_subcontractor'
|
|
}
|
|
|
|
export enum EstimationStatus {
|
|
DRAFT = 'draft',
|
|
SUBMITTED = 'submitted',
|
|
REVIEWED = 'reviewed',
|
|
AUTHORIZED = 'authorized',
|
|
PAID = 'paid',
|
|
REJECTED = 'rejected',
|
|
CANCELLED = 'cancelled'
|
|
}
|
|
|
|
@Entity({ schema: 'estimations', name: 'estimations' })
|
|
export class Estimation {
|
|
@PrimaryGeneratedColumn('uuid')
|
|
id: string;
|
|
|
|
@Column({ unique: true })
|
|
numero: string;
|
|
|
|
@Column({ name: 'project_id' })
|
|
projectId: string;
|
|
|
|
@Column({ name: 'contract_id' })
|
|
contractId: string;
|
|
|
|
@Column({ name: 'client_id', nullable: true })
|
|
clientId?: string;
|
|
|
|
@Column({ name: 'subcontractor_id', nullable: true })
|
|
subcontractorId?: string;
|
|
|
|
@Column({ type: 'enum', enum: EstimationType })
|
|
estimationType: EstimationType;
|
|
|
|
@Column({ name: 'numero_estimacion' })
|
|
numeroEstimacion: number;
|
|
|
|
@Column({ type: 'date' })
|
|
periodoInicio: Date;
|
|
|
|
@Column({ type: 'date' })
|
|
periodoFin: Date;
|
|
|
|
@Column({ type: 'bigint' })
|
|
montoBruto: number;
|
|
|
|
@Column({ type: 'bigint', default: 0 })
|
|
amortizacionAnticipo: number;
|
|
|
|
@Column({ type: 'bigint', default: 0 })
|
|
totalRetenciones: number;
|
|
|
|
@Column({ type: 'bigint' })
|
|
montoNeto: number;
|
|
|
|
@Column({ type: 'bigint', default: 0 })
|
|
retencionFondoGarantia: number;
|
|
|
|
@Column({ type: 'enum', enum: EstimationStatus, default: EstimationStatus.DRAFT })
|
|
status: EstimationStatus;
|
|
|
|
@Column({ type: 'timestamptz', default: () => 'NOW()' })
|
|
createdAt: Date;
|
|
|
|
@Column({ name: 'submitted_at', nullable: true })
|
|
submittedAt?: Date;
|
|
|
|
@Column({ name: 'authorized_at', nullable: true })
|
|
authorizedAt?: Date;
|
|
|
|
@Column({ name: 'paid_at', nullable: true })
|
|
paidAt?: Date;
|
|
|
|
@Column({ name: 'created_by' })
|
|
createdBy: string;
|
|
|
|
@Column({ name: 'authorized_by', nullable: true })
|
|
authorizedBy?: string;
|
|
|
|
@Column({ nullable: true, type: 'text' })
|
|
observaciones?: string;
|
|
|
|
@OneToMany(() => EstimationItem, item => item.estimation, { cascade: true })
|
|
items: EstimationItem[];
|
|
|
|
// Métodos de cálculo
|
|
calculateMontoNeto(): number {
|
|
return this.montoBruto - this.amortizacionAnticipo - this.totalRetenciones;
|
|
}
|
|
}
|
|
|
|
@Entity({ schema: 'estimations', name: 'estimation_items' })
|
|
export class EstimationItem {
|
|
@PrimaryGeneratedColumn('uuid')
|
|
id: string;
|
|
|
|
@Column({ name: 'estimation_id' })
|
|
estimationId: string;
|
|
|
|
@ManyToOne(() => Estimation, estimation => estimation.items)
|
|
estimation: Estimation;
|
|
|
|
@Column({ name: 'concept_catalog_id' })
|
|
conceptCatalogId: string;
|
|
|
|
@Column({ length: 500 })
|
|
descripcion: string;
|
|
|
|
@Column({ length: 50 })
|
|
unidad: string;
|
|
|
|
@Column({ type: 'bigint' })
|
|
precioUnitario: number;
|
|
|
|
@Column({ type: 'decimal', precision: 15, scale: 4 })
|
|
cantidadEstimadaActual: number;
|
|
|
|
@Column({ type: 'bigint' })
|
|
importeActual: number;
|
|
|
|
@Column({ name: 'avance_obra_id', nullable: true })
|
|
avanceObraId?: string;
|
|
}
|
|
```
|
|
|
|
---
|
|
|
|
## 📊 Funciones SQL Útiles
|
|
|
|
### Función: get_next_estimation_number
|
|
|
|
```sql
|
|
CREATE OR REPLACE FUNCTION estimations.get_next_estimation_number(
|
|
p_project_id UUID,
|
|
p_estimation_type estimations.estimation_type,
|
|
p_year INT
|
|
)
|
|
RETURNS VARCHAR AS $$
|
|
DECLARE
|
|
next_number INT;
|
|
project_code VARCHAR;
|
|
type_code VARCHAR;
|
|
BEGIN
|
|
-- Obtener siguiente consecutivo
|
|
SELECT COALESCE(MAX(numero_estimacion), 0) + 1
|
|
INTO next_number
|
|
FROM estimations.estimations
|
|
WHERE project_id = p_project_id
|
|
AND estimation_type = p_estimation_type
|
|
AND EXTRACT(YEAR FROM created_at) = p_year;
|
|
|
|
-- Obtener código proyecto
|
|
SELECT code INTO project_code
|
|
FROM projects.projects
|
|
WHERE id = p_project_id;
|
|
|
|
-- Tipo
|
|
type_code := CASE
|
|
WHEN p_estimation_type = 'to_client' THEN 'CLI'
|
|
WHEN p_estimation_type = 'to_subcontractor' THEN 'SUB'
|
|
END;
|
|
|
|
RETURN FORMAT('EST-%s-%s-%s-%s',
|
|
project_code,
|
|
type_code,
|
|
p_year,
|
|
LPAD(next_number::TEXT, 3, '0')
|
|
);
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
```
|
|
|
|
### Función: validate_estimation_totals
|
|
|
|
```sql
|
|
CREATE OR REPLACE FUNCTION estimations.validate_estimation_totals()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
-- Validar que monto_neto = monto_bruto - amortizacion - retenciones
|
|
IF NEW.monto_neto != (NEW.monto_bruto - NEW.amortizacion_anticipo - NEW.total_retenciones) THEN
|
|
RAISE EXCEPTION 'Monto neto incorrecto. Debe ser: monto_bruto - amortizacion - retenciones';
|
|
END IF;
|
|
|
|
-- Validar que monto_neto >= 0
|
|
IF NEW.monto_neto < 0 THEN
|
|
RAISE EXCEPTION 'Monto neto no puede ser negativo';
|
|
END IF;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_validate_estimation_totals
|
|
BEFORE INSERT OR UPDATE ON estimations.estimations
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION estimations.validate_estimation_totals();
|
|
```
|
|
|
|
---
|
|
|
|
**Generado:** 2025-11-20
|
|
**Estado:** ✅ Completo
|