Validate Running Balance

@tangtang

Thank you for the answer, in my particular example, we are accepting if the user wants to edit their data, since it is like a personal financial tracker, but we need to validate the data since the balance must not be a negative number since it will make the transaction invalid.

Let say we use your approach by inserting a new data as a correction, in my case, it will make the app flow much wider and we cant comprehend that at the moment.

Also i might thinking of a solution where i need to only calculate the balance if there is a quantity subtraction and checks if it return a negative number, but i dont quite understand how to implement that or is that a best approach to solve my current problem.

@aldesrahim

well, if that's the case

you can check this code reference

public function updateTransaction($id, $newQuantity)
    {
        $transaction = Transaction::find($id);

        if (!$transaction) {
			// add the message not found data here . . . .
        }

        // get the running balance before the update
        $previousBalance = Transaction::where('id', '>=', $id)->sum('quantity');

        // calculate the new balance after the update
        $newBalance = $previousBalance - $transaction->quantity + $newQuantity;

		// this is the core for negative result
        // validate the new balance
        if ($newBalance < 0) {
            // throw an message for invalid balance here . . . . .
        }

		// your other code here to update the other balance . . . .
    }

@tangtang

After some test, by using this example:

ID: 3, Update qty to 4, i found this result, which is returns as expected

+-----+------------------------+
|   3 | ID                     |
| -10 | $previousBalance       |
|  10 | $transaction->quantity |
|   4 | $newQuantity           |
| -16 | $newBalance            |
+-----+------------------------+

But if i update to 12 instead of 4 (as an addition), it's still throw an error

+-----+------------------------+
|   3 | ID                     |
| -10 | $previousBalance       |
|  10 | $transaction->quantity |
|  12 | $newQuantity           |
|  -8 | $newBalance            |
+-----+------------------------+

@aldesrahim

the code in my response just checked the Quantity column may as represent the Qty Balance in result, but seems like even with addition the result is still negative number.

well . . . .

another way is, you can still save the negative number in database table column Qty Balance, after that check if there an negative number in Qty Balance, if there is negative throw a message and rollback the transaction.

this is the reference

$negativeBalance = Transaction::where('id', '>=', $id)->where('qty_balance', '<', 0)->exists();
if ($negativeBalance) {
		// message here
		// and after the message
		// rollback the transaction to the recent state
}

but still this code is lack of range and parameter, this code will update all the transaction with id is more than selected id, imagine if there more 200 row after the selected id.

that's why I suggest not to edit transactions that have already occurred, unless there is no recent transaction data other than that specific transaction.

@tangtang

Thank you for the answer, although in my case "Qty balance" is not stored in the database, because i think it's really hard to maintain the data integrity if i have the qty balance stored in the database.

i appreciate your advice, and thank you for giving me references. Cheers.

@jlrdw

Thanks for the answer, but, unfortunately "Qty Balance" is not an existing column, it's just an example to easily understand the calculation.

@aldesrahim https://laracasts.com/discuss/channels/laravel/how-do-i-get-a-running-total-from-the-previous-transaction