DROP PROCEDURE IF EXISTS db_bukuku_solutions.validate_balance_before_delete;
DELIMITER //
CREATE FUNCTION db_bukuku_solutions.validate_balance_before_delete(
jurnalId VARCHAR(36),
cashbankId VARCHAR(36),
userId VARCHAR(36),
createdAt DATETIME
) RETURNS INT
BEGIN
DECLARE CURSOR_JOURNAL_ID INT;
DECLARE CURSOR_JOURNAL_CASHBANK_ID VARCHAR(190);
DECLARE CURSOR_JOURNAL_USER_ID VARCHAR(190);
DECLARE CURSOR_JOURNAL_AMOUNT INT;
DECLARE CURSOR_JOURNAL_STATUS_CREDIT INT;
DECLARE CURSOR_JOURNAL_CREATED_AT DATETIME;
DECLARE CURSOR_JOURNAL_DELETED_AT DATETIME;
DECLARE totalBalanceBeforeDelete INT DEFAULT 0;
DECLARE VALID_FOR_DELETE INT DEFAULT 1;
DECLARE BALANCE INT DEFAULT 0;
DECLARE continueLoop INT DEFAULT FALSE;
DECLARE cursor_journals CURSOR FOR
SELECT id, cashbank_id, user_id, amount, status_credit, created_at, deleted_at
from db_bukuku_solutions.journals
where journals.cashbank_id = cashbankId
and journals.user_id = userId
and journals.created_at > createdAt
and journals.id != jurnalId
and journals.deleted_at is null;;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET continueLoop = TRUE;
OPEN cursor_journals;
SELECT sum(amount * status_credit)
into totalBalanceBeforeDelete
from db_bukuku_solutions.journals
where journals.cashbank_id = cashbankId
and journals.user_id = userId
and journals.created_at < createdAt
and journals.id != jurnalId
and journals.deleted_at is null;
IF totalBalanceBeforeDelete < 0 THEN
set VALID_FOR_DELETE = 0;
SELECT VALID_FOR_DELETE;
END IF;
SET BALANCE = totalBalanceBeforeDelete;
loop_through_rows:
LOOP
FETCH cursor_journals INTO
CURSOR_JOURNAL_ID,
CURSOR_JOURNAL_CASHBANK_ID,
CURSOR_JOURNAL_USER_ID,
CURSOR_JOURNAL_AMOUNT,
CURSOR_JOURNAL_STATUS_CREDIT,
CURSOR_JOURNAL_CREATED_AT,
CURSOR_JOURNAL_DELETED_AT;
SET BALANCE = BALANCE + (CURSOR_JOURNAL_AMOUNT * CURSOR_JOURNAL_STATUS_CREDIT);
IF BALANCE < 0 THEN
SET VALID_FOR_DELETE = FALSE;
LEAVE loop_through_rows;
END IF;
//
end LOOP
//
close cursor_journals;
SELECT VALID_FOR_DELETE;
END;
//