Invoice Overpayment Notification

[SolaDrive]

WHMCS suggested I post here for better help. Does anyone know how to enable a notification if a clients invoice gets a double payment or over payment. Clients often are not even aware of this if they have multiple subscriptions setup. Having a notification for this would help avoid this.

[Remitur]

I use a module (BX2, by Katamaze) that does this and, for this feature, works fine.

But even with this module, if you use Paypal there are a number of different situations that you can get payments that WHMCS does not process.

So, on a monthly basis I download the WHMCS transaction report, the PayPal transactions report, put them in a spreadsheet and cross-check all of them.

A mess? Of course, but sadly we don't live in a perfect world... 

[leemahoney3]

Quick hook that'll send an admin report on the daily cron run. Can be improved greatly but I don't have time.

<?php

use WHMCS\Billing\Invoice;

add_hook('DailyCronJob', 1, static function () {
    $invoices = [];
    Invoice::paid()->each(function (Invoice $invoice) use (&$invoices) {
        if ($invoice->balance >= 0) {
            return;
        }

        $invoices[$invoice->id] = $invoice->balance;
    });

    if (!$invoices) {
        return;
    }

    $body = '<p>The following invoices are currently overpaid:</p>';
    foreach ($invoices as $invoiceId => $invoiceBalance) {
        $body .= "Invoice #$invoiceId (Balance: $invoiceBalance)<br />";
    }

    $postData = [
        'customsubject' => 'Overpaid Invoice Report',
        'custommessage' => $body,
    ];
    localAPI('SendAdminEmail', $postData);
});

Results in an email such as

[SolaDrive]

This is great, thank you so much! Its exactly what I was looking for.

[leemahoney3]

8 hours ago, SolaDrive said:

This is great, thank you so much! Its exactly what I was looking for.

Great! If I've some time later I'll see if I can optimize the hook further as its currently looping through all paid invoices (WHMCS sets the balance attribute in the model rather than having it as a field in the database which makes the lookup a bit trickier)

Thanks for the coffees!

[SolaDrive]

That would be great because right now it gives a very long list of invoices as you mention. If you end up finding the time I would be happy to pay for you to optimize this hook.

[leemahoney3]

1 hour ago, SolaDrive said:

That would be great because right now it gives a very long list of invoices as you mention. If you end up finding the time I would be happy to pay for you to optimize this hook.

The list would still be the same, unless you have a criteria for it to meet?

[SolaDrive]

Well for me I just want to see overpaid invoices since the last time the daily report ran, so last 24 hours. Right now its showing invoices all the way back to 2010.

[leemahoney3]

Oh ok, so you only want to see invoices that got an overpayment in the last 24 hours?

[SolaDrive]

Yes exactly, only the last 24 hours.

[leemahoney3]

On 10/29/2023 at 11:01 PM, SolaDrive said:

Yes exactly, only the last 24 hours.

Sorry for the delay, I'll pop something together tomorrow.

[leemahoney3]

Another day late, my bad!

Here you go, it checks for transactions in the last 24 hours and then checks their invoice for overpayments...

<?php

use Carbon\Carbon;
use WHMCS\Billing\Invoice;
use WHMCS\Billing\Payment\Transaction;

add_hook('DailyCronJob', 1, static function () {
    $invoices = [];
    Transaction::where('date', '>=', Carbon::now()->subDay())->each(function (Transaction $transaction) use (&$invoices) {
        $invoice = $transaction->invoice;

        if ($invoice->balance >= 0 || $invoice->status !== Invoice::STATUS_PAID) {
            return;
        }

        $invoices[$invoice->id] = $invoice->balance;
    });

    if (!$invoices) {
        return;
    }

    $body = '<p>The following invoices are currently overpaid:</p>';
    foreach ($invoices as $invoiceId => $invoiceBalance) {
        $body .= "Invoice #$invoiceId (Balance: $invoiceBalance)<br />";
    }

    $postData = [
        'customsubject' => 'Overpaid Invoice Report',
        'custommessage' => $body,
    ];
    localAPI('SendAdminEmail', $postData);
});

 

Edited November 2, 2023 by leemahoney3

[SolaDrive]

Just tested the new code for the first time as part of the daily cron. Its working as expected, thank you! This should really help us out a lot with duplicate payment issues. How many coffees should I buy ya?

[leemahoney3]

1 hour ago, SolaDrive said:

Just tested the new code for the first time as part of the daily cron. Its working as expected, thank you! This should really help us out a lot with duplicate payment issues. How many coffees should I buy ya?

Delighted to hear that! You've already bought me coffees so thank you, just happy that I can help 🙂