388 lines
13 KiB
PL/PgSQL
388 lines
13 KiB
PL/PgSQL
-- ===========================================
|
|
-- MECANICAS DIESEL - Schema de Contabilidad Analitica
|
|
-- ===========================================
|
|
-- Resuelve: GAP-05
|
|
-- Permite P&L por orden de servicio
|
|
-- Referencia: SPEC-CONTABILIDAD-ANALITICA-MULTIDIMENSIONAL.md
|
|
|
|
-- ============================================
|
|
-- SCHEMA: analytics
|
|
-- ============================================
|
|
CREATE SCHEMA IF NOT EXISTS analytics;
|
|
COMMENT ON SCHEMA analytics IS 'Contabilidad analitica simplificada - costos e ingresos por orden';
|
|
|
|
-- Grants
|
|
GRANT USAGE ON SCHEMA analytics TO mecanicas_user;
|
|
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA analytics TO mecanicas_user;
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA analytics GRANT ALL ON TABLES TO mecanicas_user;
|
|
|
|
-- ============================================
|
|
-- CUENTAS ANALITICAS
|
|
-- ============================================
|
|
|
|
-- Tipos de cuenta analitica
|
|
CREATE TABLE analytics.account_types (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
code VARCHAR(20) NOT NULL UNIQUE,
|
|
name VARCHAR(100) NOT NULL,
|
|
description TEXT,
|
|
sequence INTEGER NOT NULL DEFAULT 10,
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
COMMENT ON TABLE analytics.account_types IS 'Clasificacion de cuentas analiticas';
|
|
|
|
-- Seed de tipos predeterminados para taller
|
|
INSERT INTO analytics.account_types (code, name, description, sequence) VALUES
|
|
('service_order', 'Orden de Servicio', 'Cuenta por orden de servicio individual', 1),
|
|
('project', 'Proyecto', 'Agrupacion de multiples ordenes', 2),
|
|
('vehicle', 'Vehiculo', 'Costos historicos por vehiculo', 3),
|
|
('customer', 'Cliente', 'Rentabilidad por cliente', 4),
|
|
('department', 'Departamento', 'Costos por area del taller', 5);
|
|
|
|
-- Cuentas analiticas
|
|
CREATE TABLE analytics.accounts (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
|
|
-- Identificacion
|
|
code VARCHAR(30) NOT NULL,
|
|
name VARCHAR(150) NOT NULL,
|
|
|
|
-- Clasificacion
|
|
account_type_id UUID NOT NULL REFERENCES analytics.account_types(id),
|
|
|
|
-- Referencia al documento origen (opcional)
|
|
res_model VARCHAR(100), -- ej: 'service_management.service_orders'
|
|
res_id UUID, -- ID del documento
|
|
|
|
-- Jerarquia (para agrupaciones)
|
|
parent_id UUID REFERENCES analytics.accounts(id),
|
|
|
|
-- Presupuesto (opcional)
|
|
budget_amount DECIMAL(20,6) DEFAULT 0,
|
|
|
|
-- Metadatos
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ,
|
|
|
|
CONSTRAINT uq_analytics_account UNIQUE(tenant_id, code)
|
|
);
|
|
|
|
COMMENT ON TABLE analytics.accounts IS 'Cuentas analiticas para tracking de costos/ingresos';
|
|
COMMENT ON COLUMN analytics.accounts.res_model IS 'Modelo relacionado (ej: service_orders)';
|
|
COMMENT ON COLUMN analytics.accounts.res_id IS 'ID del documento relacionado';
|
|
|
|
-- Indices para accounts
|
|
CREATE INDEX idx_analytics_accounts_tenant ON analytics.accounts(tenant_id);
|
|
CREATE INDEX idx_analytics_accounts_type ON analytics.accounts(account_type_id);
|
|
CREATE INDEX idx_analytics_accounts_parent ON analytics.accounts(parent_id) WHERE parent_id IS NOT NULL;
|
|
CREATE INDEX idx_analytics_accounts_resource ON analytics.accounts(res_model, res_id) WHERE res_model IS NOT NULL;
|
|
|
|
-- RLS para accounts
|
|
SELECT create_tenant_rls_policies('analytics', 'accounts');
|
|
|
|
-- Trigger para updated_at
|
|
CREATE TRIGGER set_updated_at_analytics_accounts
|
|
BEFORE UPDATE ON analytics.accounts
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION trigger_set_updated_at();
|
|
|
|
-- ============================================
|
|
-- LINEAS ANALITICAS (Movimientos)
|
|
-- ============================================
|
|
|
|
-- Categorias de linea (costo vs ingreso)
|
|
CREATE TYPE analytics.line_category AS ENUM ('cost', 'revenue', 'adjustment');
|
|
|
|
-- Lineas analiticas
|
|
CREATE TABLE analytics.lines (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL,
|
|
|
|
-- Cuenta analitica
|
|
account_id UUID NOT NULL REFERENCES analytics.accounts(id),
|
|
|
|
-- Fecha y descripcion
|
|
date DATE NOT NULL,
|
|
name VARCHAR(256) NOT NULL,
|
|
ref VARCHAR(100), -- Referencia externa (factura, orden, etc.)
|
|
|
|
-- Importes
|
|
amount DECIMAL(20,6) NOT NULL, -- Positivo = ingreso, negativo = costo
|
|
category analytics.line_category NOT NULL,
|
|
unit_amount DECIMAL(20,6), -- Cantidad de unidades (horas, piezas)
|
|
unit_cost DECIMAL(20,6), -- Costo unitario
|
|
|
|
-- Origen del movimiento
|
|
source_model VARCHAR(100), -- Modelo que genero la linea
|
|
source_id UUID, -- ID del registro origen
|
|
|
|
-- Detalle adicional
|
|
product_id UUID, -- Producto/refaccion si aplica
|
|
employee_id UUID, -- Empleado si es mano de obra
|
|
|
|
-- Auditoria
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
created_by UUID
|
|
);
|
|
|
|
COMMENT ON TABLE analytics.lines IS 'Movimientos de costos/ingresos en cuentas analiticas';
|
|
COMMENT ON COLUMN analytics.lines.amount IS 'Monto: positivo=ingreso, negativo=costo';
|
|
COMMENT ON COLUMN analytics.lines.unit_amount IS 'Cantidad (horas de mano de obra, unidades de refaccion)';
|
|
|
|
-- Indices para lines
|
|
CREATE INDEX idx_analytics_lines_tenant ON analytics.lines(tenant_id);
|
|
CREATE INDEX idx_analytics_lines_account ON analytics.lines(account_id);
|
|
CREATE INDEX idx_analytics_lines_date ON analytics.lines(date);
|
|
CREATE INDEX idx_analytics_lines_category ON analytics.lines(category);
|
|
CREATE INDEX idx_analytics_lines_source ON analytics.lines(source_model, source_id) WHERE source_model IS NOT NULL;
|
|
CREATE INDEX idx_analytics_lines_product ON analytics.lines(product_id) WHERE product_id IS NOT NULL;
|
|
|
|
-- RLS para lines
|
|
SELECT create_tenant_rls_policies('analytics', 'lines');
|
|
|
|
-- ============================================
|
|
-- FUNCIONES PARA GESTION ANALITICA
|
|
-- ============================================
|
|
|
|
-- Funcion para crear cuenta analitica automatica para orden de servicio
|
|
CREATE OR REPLACE FUNCTION analytics.create_service_order_account(
|
|
p_tenant_id UUID,
|
|
p_service_order_id UUID,
|
|
p_order_number VARCHAR(50),
|
|
p_customer_name VARCHAR(256) DEFAULT NULL
|
|
)
|
|
RETURNS UUID AS $$
|
|
DECLARE
|
|
v_account_id UUID;
|
|
v_type_id UUID;
|
|
v_code VARCHAR(30);
|
|
v_name VARCHAR(150);
|
|
BEGIN
|
|
-- Obtener tipo 'service_order'
|
|
SELECT id INTO v_type_id
|
|
FROM analytics.account_types
|
|
WHERE code = 'service_order';
|
|
|
|
-- Generar codigo y nombre
|
|
v_code := 'OS-' || p_order_number;
|
|
v_name := 'Orden ' || p_order_number;
|
|
IF p_customer_name IS NOT NULL THEN
|
|
v_name := v_name || ' - ' || LEFT(p_customer_name, 50);
|
|
END IF;
|
|
|
|
-- Insertar cuenta
|
|
INSERT INTO analytics.accounts (
|
|
tenant_id, code, name, account_type_id,
|
|
res_model, res_id
|
|
)
|
|
VALUES (
|
|
p_tenant_id, v_code, v_name, v_type_id,
|
|
'service_management.service_orders', p_service_order_id
|
|
)
|
|
ON CONFLICT (tenant_id, code) DO UPDATE SET
|
|
name = EXCLUDED.name,
|
|
updated_at = NOW()
|
|
RETURNING id INTO v_account_id;
|
|
|
|
RETURN v_account_id;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
COMMENT ON FUNCTION analytics.create_service_order_account IS 'Crea cuenta analitica automatica para orden de servicio';
|
|
|
|
-- Funcion para registrar costo de refaccion
|
|
CREATE OR REPLACE FUNCTION analytics.log_parts_cost(
|
|
p_tenant_id UUID,
|
|
p_account_id UUID,
|
|
p_part_id UUID,
|
|
p_part_name VARCHAR(256),
|
|
p_quantity DECIMAL(20,6),
|
|
p_unit_cost DECIMAL(20,6),
|
|
p_source_model VARCHAR(100) DEFAULT NULL,
|
|
p_source_id UUID DEFAULT NULL
|
|
)
|
|
RETURNS UUID AS $$
|
|
DECLARE
|
|
v_line_id UUID;
|
|
v_total_cost DECIMAL(20,6);
|
|
BEGIN
|
|
v_total_cost := p_quantity * p_unit_cost * -1; -- Negativo porque es costo
|
|
|
|
INSERT INTO analytics.lines (
|
|
tenant_id, account_id, date, name, ref,
|
|
amount, category, unit_amount, unit_cost,
|
|
source_model, source_id, product_id, created_by
|
|
)
|
|
VALUES (
|
|
p_tenant_id, p_account_id, CURRENT_DATE,
|
|
'Refaccion: ' || p_part_name, NULL,
|
|
v_total_cost, 'cost', p_quantity, p_unit_cost,
|
|
p_source_model, p_source_id, p_part_id, get_current_user_id()
|
|
)
|
|
RETURNING id INTO v_line_id;
|
|
|
|
RETURN v_line_id;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
COMMENT ON FUNCTION analytics.log_parts_cost IS 'Registra costo de refacciones usadas';
|
|
|
|
-- Funcion para registrar costo de mano de obra
|
|
CREATE OR REPLACE FUNCTION analytics.log_labor_cost(
|
|
p_tenant_id UUID,
|
|
p_account_id UUID,
|
|
p_employee_id UUID,
|
|
p_employee_name VARCHAR(256),
|
|
p_hours DECIMAL(20,6),
|
|
p_hourly_rate DECIMAL(20,6),
|
|
p_description VARCHAR(256) DEFAULT NULL,
|
|
p_source_model VARCHAR(100) DEFAULT NULL,
|
|
p_source_id UUID DEFAULT NULL
|
|
)
|
|
RETURNS UUID AS $$
|
|
DECLARE
|
|
v_line_id UUID;
|
|
v_total_cost DECIMAL(20,6);
|
|
BEGIN
|
|
v_total_cost := p_hours * p_hourly_rate * -1; -- Negativo porque es costo
|
|
|
|
INSERT INTO analytics.lines (
|
|
tenant_id, account_id, date, name, ref,
|
|
amount, category, unit_amount, unit_cost,
|
|
source_model, source_id, employee_id, created_by
|
|
)
|
|
VALUES (
|
|
p_tenant_id, p_account_id, CURRENT_DATE,
|
|
COALESCE(p_description, 'Mano de obra: ' || p_employee_name), NULL,
|
|
v_total_cost, 'cost', p_hours, p_hourly_rate,
|
|
p_source_model, p_source_id, p_employee_id, get_current_user_id()
|
|
)
|
|
RETURNING id INTO v_line_id;
|
|
|
|
RETURN v_line_id;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
COMMENT ON FUNCTION analytics.log_labor_cost IS 'Registra costo de mano de obra';
|
|
|
|
-- Funcion para registrar ingreso (facturacion)
|
|
CREATE OR REPLACE FUNCTION analytics.log_revenue(
|
|
p_tenant_id UUID,
|
|
p_account_id UUID,
|
|
p_amount DECIMAL(20,6),
|
|
p_description VARCHAR(256),
|
|
p_ref VARCHAR(100) DEFAULT NULL,
|
|
p_source_model VARCHAR(100) DEFAULT NULL,
|
|
p_source_id UUID DEFAULT NULL
|
|
)
|
|
RETURNS UUID AS $$
|
|
DECLARE
|
|
v_line_id UUID;
|
|
BEGIN
|
|
INSERT INTO analytics.lines (
|
|
tenant_id, account_id, date, name, ref,
|
|
amount, category, unit_amount, unit_cost,
|
|
source_model, source_id, created_by
|
|
)
|
|
VALUES (
|
|
p_tenant_id, p_account_id, CURRENT_DATE,
|
|
p_description, p_ref,
|
|
ABS(p_amount), 'revenue', NULL, NULL,
|
|
p_source_model, p_source_id, get_current_user_id()
|
|
)
|
|
RETURNING id INTO v_line_id;
|
|
|
|
RETURN v_line_id;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
COMMENT ON FUNCTION analytics.log_revenue IS 'Registra ingreso en cuenta analitica';
|
|
|
|
-- ============================================
|
|
-- VISTAS PARA REPORTES
|
|
-- ============================================
|
|
|
|
-- Vista de P&L por cuenta analitica
|
|
CREATE VIEW analytics.v_account_pnl AS
|
|
SELECT
|
|
a.id as account_id,
|
|
a.tenant_id,
|
|
a.code,
|
|
a.name,
|
|
at.code as account_type,
|
|
a.res_model,
|
|
a.res_id,
|
|
a.budget_amount,
|
|
COALESCE(SUM(CASE WHEN l.category = 'revenue' THEN l.amount ELSE 0 END), 0) as total_revenue,
|
|
COALESCE(SUM(CASE WHEN l.category = 'cost' THEN ABS(l.amount) ELSE 0 END), 0) as total_cost,
|
|
COALESCE(SUM(l.amount), 0) as net_profit,
|
|
CASE
|
|
WHEN COALESCE(SUM(CASE WHEN l.category = 'revenue' THEN l.amount ELSE 0 END), 0) = 0 THEN 0
|
|
ELSE ROUND(
|
|
(COALESCE(SUM(l.amount), 0) /
|
|
COALESCE(SUM(CASE WHEN l.category = 'revenue' THEN l.amount ELSE 0 END), 1)) * 100,
|
|
2
|
|
)
|
|
END as margin_percent,
|
|
COUNT(DISTINCT l.id) as line_count
|
|
FROM analytics.accounts a
|
|
JOIN analytics.account_types at ON at.id = a.account_type_id
|
|
LEFT JOIN analytics.lines l ON l.account_id = a.id
|
|
WHERE a.is_active = true
|
|
GROUP BY a.id, a.tenant_id, a.code, a.name, at.code, a.res_model, a.res_id, a.budget_amount;
|
|
|
|
COMMENT ON VIEW analytics.v_account_pnl IS 'Estado de resultados por cuenta analitica';
|
|
|
|
-- Vista de detalle de costos por orden
|
|
CREATE VIEW analytics.v_service_order_costs AS
|
|
SELECT
|
|
a.res_id as service_order_id,
|
|
a.tenant_id,
|
|
a.code as account_code,
|
|
l.date,
|
|
l.name,
|
|
l.category,
|
|
l.amount,
|
|
l.unit_amount,
|
|
l.unit_cost,
|
|
l.product_id,
|
|
l.employee_id,
|
|
l.source_model,
|
|
l.source_id,
|
|
l.created_at
|
|
FROM analytics.accounts a
|
|
JOIN analytics.account_types at ON at.id = a.account_type_id AND at.code = 'service_order'
|
|
JOIN analytics.lines l ON l.account_id = a.id
|
|
WHERE a.res_model = 'service_management.service_orders'
|
|
ORDER BY l.created_at DESC;
|
|
|
|
COMMENT ON VIEW analytics.v_service_order_costs IS 'Detalle de costos e ingresos por orden de servicio';
|
|
|
|
-- Vista resumen mensual
|
|
CREATE VIEW analytics.v_monthly_summary AS
|
|
SELECT
|
|
a.tenant_id,
|
|
DATE_TRUNC('month', l.date) as month,
|
|
at.code as account_type,
|
|
COUNT(DISTINCT a.id) as account_count,
|
|
SUM(CASE WHEN l.category = 'revenue' THEN l.amount ELSE 0 END) as total_revenue,
|
|
SUM(CASE WHEN l.category = 'cost' THEN ABS(l.amount) ELSE 0 END) as total_cost,
|
|
SUM(l.amount) as net_profit
|
|
FROM analytics.lines l
|
|
JOIN analytics.accounts a ON a.id = l.account_id
|
|
JOIN analytics.account_types at ON at.id = a.account_type_id
|
|
GROUP BY a.tenant_id, DATE_TRUNC('month', l.date), at.code
|
|
ORDER BY month DESC, account_type;
|
|
|
|
COMMENT ON VIEW analytics.v_monthly_summary IS 'Resumen de rentabilidad mensual por tipo de cuenta';
|
|
|
|
-- ============================================
|
|
-- GRANTS ADICIONALES
|
|
-- ============================================
|
|
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA analytics TO mecanicas_user;
|
|
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO mecanicas_user;
|