deleting old, unpaid "add funds" invoices

[Remitur]

Problem:

Many customers insert "add funds" orders, and later give them up.

Some of them also in a compulsive way (a dozen or so of different orders).

This may happen for many different reasons:

•  "what's happen if I click on "add funds" button? let's try..."
• "Let's do a 100 USD credit order... damn, I wanted it of 100 USD, not 1000 USD... let's do another one
• "I have to renew this domain... how to do it... maybe this button "add funds"? ... o... let's try something different..."

... and so on...

So now I have a lot of unuseful, old and unpaid "add funds" pro-forma invoices, and customers sometime rising tickets "please, delete this six-months-old add-funds invoice..."

An idea would be a daily cron to delete all "add funds" unpaid pro-forma invoices older than 30 days... but I did'nt found any way to filter only "add-funds unpaid invoice"... :-(

 

[twhiting9275]

To start with, deleting invoices, not the best approach. It's better to cancel them outright. This way, for all legal purposes they're still there

That being said, here's a hook that'll run through these and cancel them on run. There's two spots you want to change, pretty well documented there. Save this as a php file in whmcs/includes/hooks

<?php
/*
Hook to automatically cancel invoices after XXX days on cron run
Provided courtesy of https://www.whmcs.guru/
*/

if (!defined("WHMCS"))
die("This file cannot be accessed directly");
use Illuminate\Database\Capsule\Manager as Capsule;

function guru_unpaid_addfunds($vars)
{
	//BEGIN CHANGE SECTION
	//change the period here
	$olderthan = "30";
	//whmcs API user, change it
	$apiuser="CHANGEME";

	//END CHANGE SECTION


	logActivity('Starting daily funds invoice cleanup	', 0);
	$invoices = Capsule::table('tblinvoices')->select('id', 'date')->WHERE('status', 'Unpaid')->get();
	foreach ($invoices as $data)
	{
		$myid = $data->id;
		$mydate = $data->date;
		$createdon = strtotime($mydate);
		$calctime = time() - ($olderthan * 24 * 60 * 60);
		if ($createdon > $calctime)
		{
			//skip it, we shouldn't be here yet
			continue;
		}
		//do something with myid and unique column
		$numrows = Capsule::table('tblinvoiceitems') ->select('id')->WHERE('invoiceid', $myid) ->WHERE('type', 'AddFunds')->count();
		//invoice matches , cancel it
		if ($numrows > 0)
		{
			$notedate = date("F j, Y");
			//this invoice matches, so let's delete it

			$command = 'UpdateInvoice';
			$values['invoiceid'] = $myid;
			$values['status'] = 'Cancelled';
			$values['notes'] = "Automatically cancelled via daily cron run on $notedate";

			$results = localAPI($command, $values, $apiuser);
			logActivity('Invoice ID $myid - Cancelled due to daily funds invoice cleanup', 0);
			//print_r($results);
		}
	}
	logActivity('Ending daily funds invoice cleanup	', 0);
}
add_hook("DailyCronJob",1,"guru_unpaid_addfunds");
?>

 

This will log the cancellation, both in admin logs, and in the invoice notes itself (will overwrite anything in the existing invoice notes), and trigger only on unpaid addfunds invoices

[Nesooo]

Is this valid on WHMCS 7.5 I added it, and run /cron.php but it look like nothing is removed

For API username I have set my WHMCS Admin usrname ?

[brian!]

35 minutes ago, Nesooo said:

Is this valid on WHMCS 7.5 I added it, and run /cron.php but it look like nothing is removed

the hook just changes the status of applicable invoices to "Cancelled" - it doesn't delete them... though if it's making any changes, you should be able to see them detailed in the Activity Log.

43 minutes ago, Nesooo said:

For API username I have set my WHMCS Admin username ?

take a look at the API Credentials page..

https://docs.whmcs.com/API_Authentication_Credentials

[Nesooo]

I added API username in file, under API role, I only selected: UpdateInvoice

But still it is not changing invoice status to CANCELED when I execute crons/cron.php 

- Invoices are older that date set in file.
- Do I need to allow some other API actions ?

[brian!]

if you run the query below in phpmyadmin, is it finding any applicable invoices ? it's basically just the select query from the above hook...

SELECT tblinvoices.id,tblinvoices.date,tblinvoices.`status`,tblinvoiceitems.description,tblinvoiceitems.amount
FROM tblinvoices
INNER JOIN tblinvoiceitems ON tblinvoices.id = tblinvoiceitems.invoiceid
WHERE tblinvoices.`status` = 'Unpaid' AND tblinvoiceitems.type = 'AddFunds' AND DATE(tblinvoices.date) > (NOW() - INTERVAL 30 DAY)