1882fcab3d
- fix_setpaymentstatusv2_commented.sql: bloque de stock duplicado comentado - rollback_setpaymentstatusv2.sql: restaura funcion a estado original - Nota explicativa incluida en el codigo de la funcion
121 lines
3.6 KiB
PL/PgSQL
121 lines
3.6 KiB
PL/PgSQL
-- ============================================================
|
|
-- ROLLBACK: Restaurar setpaymentstatusv2 a su estado original
|
|
-- Fecha: 2026-06-09
|
|
-- ============================================================
|
|
-- Usar este script SOLO si el fix temporal causa problemas
|
|
-- y se necesita restaurar el comportamiento anterior.
|
|
-- ============================================================
|
|
|
|
CREATE OR REPLACE FUNCTION public.setpaymentstatusv2(
|
|
expense integer,
|
|
status integer)
|
|
RETURNS integer
|
|
LANGUAGE 'plpgsql'
|
|
COST 100
|
|
VOLATILE PARALLEL UNSAFE
|
|
AS $BODY$
|
|
DECLARE
|
|
rows_updated INT := 0;
|
|
details RECORD;
|
|
approval int :=0;
|
|
BEGIN
|
|
SELECT id_approval_stat INTO approval FROM expenses where id_expense = expense;
|
|
IF(approval = 1 ) --si el gasto esta aprobado se procede al pago
|
|
THEN
|
|
-- Validar estados permitidos
|
|
IF status = 1 THEN
|
|
UPDATE payments
|
|
SET id_payment_status = status,
|
|
payment_date = CURRENT_DATE
|
|
WHERE id_expense = expense;
|
|
GET DIAGNOSTICS rows_updated = ROW_COUNT;
|
|
|
|
IF rows_updated = 0 THEN
|
|
RETURN -1;
|
|
END IF;
|
|
|
|
-- Procesar solo si existen movimientos de stock
|
|
IF EXISTS (
|
|
SELECT 1
|
|
FROM stock_movements st
|
|
INNER JOIN purchase_detail pd ON st.id_expense = pd.id_expense
|
|
WHERE pd.id_expense = expense
|
|
) THEN
|
|
|
|
-- se obtiene el id del detalle de la compra
|
|
--Ya no se necesita con el cambio nuevo
|
|
/*SELECT id_expense
|
|
INTO id_purchase_detail
|
|
FROM purchase_detail
|
|
WHERE id_expense = expense
|
|
LIMIT 1;*/
|
|
-- Actualizar movimiento
|
|
UPDATE stock_movements
|
|
SET date_st_mov = CURRENT_DATE
|
|
WHERE id_expense = expense;
|
|
|
|
-- Obtener id movimiento
|
|
--Ya no se necesita con el cambio nuevo
|
|
/*SELECT id_sto_mov
|
|
INTO id_stock_movement
|
|
FROM stock_movements
|
|
WHERE id_purchase_dt = id_purchase_detail
|
|
LIMIT 1;*/
|
|
|
|
-- Actualizar ajuste
|
|
UPDATE stock_adjusments
|
|
SET date_adjusment = CURRENT_DATE
|
|
WHERE id_expense = expense;
|
|
|
|
-- Actualizar stock
|
|
FOR details IN
|
|
SELECT id_product, quantity
|
|
FROM purchase_detail
|
|
WHERE id_expense = expense
|
|
LOOP
|
|
UPDATE stock
|
|
SET stock = stock + details.quantity
|
|
WHERE id_product = details.id_product;
|
|
END LOOP;
|
|
/*Cambio viejo
|
|
UPDATE stock
|
|
SET stock = stock + (
|
|
SELECT *
|
|
FROM purchase_detail
|
|
WHERE id_purchase_dt = id_purchase_detail
|
|
)
|
|
WHERE id_product = (
|
|
SELECT id_product
|
|
FROM purchase_detail
|
|
WHERE id_purchase_dt = id_purchase_detail
|
|
);*/
|
|
END IF;
|
|
RETURN status;
|
|
|
|
ELSIF status = 3 THEN
|
|
-- Actualizar pago
|
|
UPDATE payments
|
|
SET id_payment_status = status,
|
|
payment_date = CURRENT_DATE
|
|
WHERE id_expense = expense;
|
|
GET DIAGNOSTICS rows_updated = ROW_COUNT;
|
|
|
|
IF rows_updated = 0 THEN
|
|
RETURN -1;
|
|
END IF;
|
|
|
|
RETURN status;
|
|
|
|
ELSE
|
|
-- Status invalido
|
|
RETURN -2;
|
|
END IF;
|
|
ELSE
|
|
return -3; --si no esta aprobado o rechazado no se puede actualizar el pago
|
|
END IF;
|
|
END;
|
|
$BODY$;
|
|
|
|
-- Confirmacion
|
|
SELECT 'Funcion setpaymentstatusv2 restaurada a su estado original' AS resultado;
|