Automated Invoice Management System: Dynamic Updates and Status Handling for Payments and Overpayments

[Olumide]

I have an invoice table whereby if payment is made or recorded, it should update the table by setting the tendered_amount, if the tendered_amount is greater than the total_amount, the result/value should be store in change_balance (e.g. 500 - 300 = 200) and if the tendered_amount is lower than the total_amount, that is debts because the amount expected is not equal to the tendered amount, but if the tendered amount is equal to the total amount, the user has fully paid and the status should change to 'paid', also, if the tendered amount is greater than the total amount, the status will also change to paid because he overpaid. 

 

 

// Calculate change_balance, debts, and update status
            $change_balance = max(0, $amount_paid - $invoice_data['total_amount']);
            $debts = max(0, $invoice_data['total_amount'] - $amount_paid);
            $status = ($debts == 0) ? 'paid' : 'unpaid';

            // Update the tendered_amount, change_balance, debts, and status columns
            $update_query = "UPDATE `invoices` 
                             SET `tendered_amount` = '{$amount_paid}', 
                                 `change_balance` = '{$change_balance}', 
                                 `debts` = '{$debts}', 
                                 `status` = '{$status}'
                             WHERE `invoice_code` = '{$invoice_code}'";

 

[mac_gyver]

you would not UPDATE amounts in a table to track this data, since that doesn't provide an accounting trail should a programming mistake, duplicate entry, or nafarious activity alter a value. these are derived amounts that you would calculate when needed. you would have an invoice_accounting (or similar named) table, that gets a row inserted for every amount that affects an invoice amount. you would query when needed (a UNION between the invoice(s) table and the invoice accounting table) to determine the current total +/-/0 for any invoice.

Edited December 19, 2023 by mac_gyver

[Olumide]

On 12/19/2023 at 10:54 PM, mac_gyver said:

you would not UPDATE amounts in a table to track this data, since that doesn't provide an accounting trail should a programming mistake, duplicate entry, or nafarious activity alter a value. these are derived amounts that you would calculate when needed. you would have an invoice_accounting (or similar named) table, that gets a row inserted for every amount that affects an invoice amount. you would query when needed (a UNION between the invoice(s) table and the invoice accounting table) to determine the current total +/-/0 for any invoice.

 

Thank you. I will work on that and update you.