Hook to cancel invoices for terminated products

[DennisHermannsen]

Don't know if this is the right category to post this in, but I thought I wanted to share this.

It's a very small hook that cancels an invoice once the product gets terminated.

//<?php

use WHMCS\Database\Capsule;
add_hook('PreModuleTerminate', 1, function($vars) {
	$invoiceid = Capsule::table('tblinvoiceitems')
		->where('relid', '=', $vars['params']['serviceid'])
		->orderBy('duedate', 'desc')
		->first();
	Capsule::table('tblinvoices')
		->where('id', '=', $invoiceid->invoiceid)
		->update(['Status' => 'Cancelled']);
});

Any feedback is much appreciated.

[DennisHermannsen]

//Updated to only target products of Hosting-type.
<?php
use WHMCS\Database\Capsule;
add_hook('PreModuleTerminate', 1, function($vars) {
	$invoiceid = Capsule::table('tblinvoiceitems')
		->where('relid', '=', $vars['params']['serviceid'])
                ->where('type','Hosting')
		 ->orderBy('duedate', 'desc')
		->first();
	Capsule::table('tblinvoices')
		->where('id', '=', $invoiceid->invoiceid)
		->update(['Status' => 'Cancelled']);
});

[MikeP]

I'm guessing this would just go in /includes/hooks?

[DennisHermannsen]

Correct.

[brian!]

4 minutes ago, MikeP said:

I'm guessing this would just go in /includes/hooks?

yes. 🙂

[MikeP]

Thanks for confirming.  Would it cancel the invoice if there was another product on the invoice as well?

[DennisHermannsen]

1 minute ago, MikeP said:

Thanks for confirming.  Would it cancel the invoice if there was another product on the invoice as well?

Oh. Great catch. It actually will - I've never had that in mind.

[MikeP]

There are various paid options available for this functionality but at least you've made the effort to provide this free of charge 🙂

I'd be interested if you were to add additional functionality, such as remove the product from the invoice and provide a log in the activity log.

[Believe_]

Appreciate your efforts to publish this hook code since there are many paid versions available on the market for the same. The current hook code will cancel the entire invoice even if only one products require termination. 

If the invoice has two or more items, and if a product requires termination along with its Addons (if any) then we will need to split the invoice and mark the one which terminates as cancelled and other one stay as unpaid on the account. Also it would be good to provide log in Activity log as he suggested. 

Can you write it up, and your effort is much appreciated! 🙂

[DennisHermannsen]

Deleted

Edited October 8, 2019 by DennisHermannsen

[DennisHermannsen]

@Believe_ and @MikeP, here's an updated version - please note that this currently doesn't work very well if a client has a Group Discount.

<?php
use WHMCS\Database\Capsule;
add_hook('PreModuleTerminate', 1, function($vars) {
	$invoices = Capsule::table('tblinvoiceitems')
		->where('relid', $vars['params']['serviceid'])
		->where('type', '!=', 'GroupDiscount')
		->get();
	logActivity('Auto Cancel Invoice: Starting...');
	foreach($invoices as $key){
		
		$invoiceIDs = Capsule::table('tblinvoices')
			->where('id', $key->invoiceid)
			->where('status', 'Unpaid')
			->value('id');
		
		$invoiceLine = Capsule::table('tblinvoiceitems')
			->where('invoiceid', $invoiceIDs)
			->where('relid', $vars['params']['serviceid'])
			->value('id');
				
		$invoiceLines = Capsule::table('tblinvoiceitems')
			->where('invoiceid', $invoiceIDs)
			->get();
			
		$description = Capsule::table('tblinvoiceitems')
			->where('relid', $vars['params']['serviceid'])
			->where('invoiceid', $invoiceIDs)
			->value('description');
			
		
		$discountLine = Capsule::table('tblinvoiceitems')
			->where('invoiceid', $invoiceIDs)
			->where('type', 'GroupDiscount')
			->where('description', 'like', '% '.$description.'%')
			->value('id');

		if($invoiceIDs){
			logActivity('Auto Cancel Invoice: Invoice ' . $invoiceIDs . ' has had a line removed. This invoice previously had ' . count($invoiceLines) . ' lines.');
			if(count($invoiceLines) > 1){
				$command = 'UpdateInvoice';
				$postData = array(
					'invoiceid' => $invoiceIDs,
					'deletelineids' => array($invoiceLine),
				);

				$results = localAPI($command, $postData);
			}elseif(count($invoiceLines) == 1){
				logActivity('Auto Cancel Invoice: Invoice ' . $invoiceIDs . ' has been cancelled because the product was terminated.');
				$command = 'UpdateInvoice';
				$postData = array(
					'invoiceid' => $invoiceIDs,
					'status' => 'Cancelled',
				);
				$results = localAPI($command, $postData);
			}
			if($discountLine){
				logActivity('Auto Cancel Invoice: Invoice ' . $invoiceIDs . ' had a Group Discount. This has been removed.');
				$command = 'UpdateInvoice';
				$postData = array(
					'invoiceid' => $invoiceIDs,
					'deletelineids' => array($discountLine),
				);
				$results = localAPI($command, $postData);
			}
		}
	}
});

It will remove the product from the invoice (as split is not possible using the API, and WHMCS does just remove lines from invoices any way) if the invoice has more than 1 line. If it only has 1 line, the invoice will be cancelled.

But please note that this doesn't work with Client Group Discounts at all. There's no way for me to know for certain if a 'discount' line is associated with another line. If you use this hook with Client Group Discounts enabled, be aware that an invoice can have a negative total.

Does anyone have any ideas of a way to "link" a discount line with the relevant product line? The only way I could think of is to copy the line for the product, and match on %PRODUCT-LINE%, but we could potentially target an incorrect discount line. Do you know any smarter way, @brian!?

Edit: I added very simple logic to check if the product has a line for GroupDiscount. Please test this thoroughly before using it. I've only tested it on my dev installation, and everything seems fine from there.

Edited October 8, 2019 by DennisHermannsen

[Chris74]

Anyone using this? Does it work ok with PHP 7.2 and latest WHMCS?

[DennisHermannsen]

Yes it does.

[Chris74]

Does it add entries into the log?

[DennisHermannsen]

It does.

I would still advise you to do some tests with the hook before using it on a production installation.

[Believe_]

@DennisHermannsen 

Thanks for including all these functions, however, I've come across a silly mistake on this hook. 

The invoices that has the Late Fee added which is treating as a separate line, so the item gets removed from the invoice and invoice stay unpaid with just the Late fees value. This is not correct!

I think it is best to add an additional check for the remaining line is whether Late fee or not, and if yes, then cancel off the entire invoice. I'm also looking to add an email notification of these actions to administrators  along with that activity log entries.

Can you sort this up or help me to include these two functions on above hook, that would be highly appreciated :)

[DennisHermannsen]

Hi @Believe_

Thanks for your suggestions.
The hook is in no way perfect - I'm well aware 😉 We don't use late fees, so I have no way of testing it unfortunately. If you could tell me how WHMCS tells that a line is for a late fee, please let me know. If it's stored as something specific in the database, that would be the best.

[Believe_]

Hi @DennisHermannsen

I've gone through this and here are the details that you asked. 

WHMCS invoice page:  http://i.is.cc/1HU5jht3.png

Database has this line in "tblinvoiceitems" table: http://i.is.cc/1HTJHzBc.png

When the Late Fee added to an invoice, I guess it is adding a new table row in tblinvoiceitems as shown in above screenshot. I hope this information is enough to include on above hook functions. 

Please also include a email notification to all administrators when an invoice modified and invoice cancelled. Please let me know if you require any further information/details.

Thanks, 🙂

 

[brian!]

1 hour ago, DennisHermannsen said:

If you could tell me how WHMCS tells that a line is for a late fee, please let me know. If it's stored as something specific in the database, that would be the best.

I think in previous versions, the type value in tblinvoiceitems would be 'LateFee' (no spaces, singular, capitalised as shown)... but from the above screenshots, maybe something has changed or a third-party addon is adding these late fees ?

if so, and you're having to check descriptions, that 'Late Fee' text is possibly a language string rather than just Late Fee for all languages - so you might need to bear that in mind.

[DennisHermannsen]

@Believe_ Ouch. It seems like something that is difficult to fix. The Late Fee line in tblinvoiceitems doesn't have anything to indicate that it's a Late Fee item. I tried to check with our own installation if 'type' being 0 had anything to do with it, but it doesn't - we have plenty of those in our database.

It seems like the only thing to spot a Late Fee item is by looking at the description itself - but as @brian! says, it's just a string that can be translated. It would appear different depending on what language the user has.

If anyone has an idea to work around that, let me know.

[Believe_]

Just a correction on my above screenshot, that was manually edited invoice item. Here is the actual WHMCS created late fee table raw: http://i.is.cc/1HYVpUpj.png

The type column has the unique "LateFee" text by default, so I guess we could get it by cross checking the invoice id + type section and get it removed. 

Any help would be much appreciated 🙂

[brian!]

23 minutes ago, DennisHermannsen said:

Ouch. It seems like something that is difficult to fix. The Late Fee line in tblinvoiceitems doesn't have anything to indicate that it's a Late Fee item.

I just checked a friend's install, who's using v7.10.2 and applies late fees - I don't have remote db access, but I ran a custom invoiceitems report that shows types and it returned the following on an invoice that had a late fee added...

so v7.10.2 is still adding a type of 'LateFee' to tblinvoiceitems for late fees... the question is why is believe_'s install not doing that ?

2 minutes ago, Believe_ said:

Just a correction on my above screenshot, that was manually edited invoice item. Here is the actual WHMCS created late fee table raw: http://i.is.cc/1HYVpUpj.png

problem solved. 🙂

30 minutes ago, DennisHermannsen said:

It seems like the only thing to spot a Late Fee item is by looking at the description itself - but as @brian! says, it's just a string that can be translated. It would appear different depending on what language the user has.

if that had been the case, then it would have been doable - but shouldn't be necessary now.

[DennisHermannsen]

Great, that solves the issue 😃

So, if any LateFee is added to the invoice, the invoice should just be left alone?

[Believe_]

He @DennisHermannsen

At this stage with that above hook code; If the invoice having the item + Late Fee, then when we terminate the service the item gets removed from the invoice but since the Late fee is there the invoice remains unpaid with just the late fee amount. If the last remaining line is Late fee then the invoice should mark as cancelled.

[DennisHermannsen]

There's just a few issues with this:

If an invoice has 4 lines: 2 products and 2 Late Fees (if that's even possible).
If one of the products is terminated, while the other isn't, there's no way for me to know which late fee to remove.

Anyways, you can use this:
 

<?php
use WHMCS\Database\Capsule;
add_hook('PreModuleTerminate', 1, function($vars) {
	$invoices = Capsule::table('tblinvoiceitems')
		->where('relid', $vars['params']['serviceid'])
		->where('type', '!=', 'GroupDiscount')
		->get();
	logActivity('Auto Cancel Invoice: Starting...');
	foreach($invoices as $key){
		
		$invoiceIDs = Capsule::table('tblinvoices')
			->where('id', $key->invoiceid)
			->where('status', 'Unpaid')
			->value('id');
		
		$invoiceLine = Capsule::table('tblinvoiceitems')
			->where('invoiceid', $invoiceIDs)
			->where('relid', $vars['params']['serviceid'])
			->value('id');
				
		$invoiceLines = Capsule::table('tblinvoiceitems')
			->where('invoiceid', $invoiceIDs)
			->get();
			
		$description = Capsule::table('tblinvoiceitems')
			->where('relid', $vars['params']['serviceid'])
			->where('invoiceid', $invoiceIDs)
			->value('description');
		
		$discountLine = Capsule::table('tblinvoiceitems')
			->where('invoiceid', $invoiceIDs)
			->where('type', 'GroupDiscount')
			->where('description', 'like', '% '.$description.'%')
			->value('id');
		if($invoiceIDs){
			logActivity('Auto Cancel Invoice: Invoice ' . $invoiceIDs . ' has had a line removed. This invoice previously had ' . count($invoiceLines) . ' lines.');
			if(count($invoiceLines) > 1){
				$command = 'UpdateInvoice';
				$postData = array(
					'invoiceid' => $invoiceIDs,
					'deletelineids' => array($invoiceLine),
				);
				$results = localAPI($command, $postData);
			}elseif(count($invoiceLines) == 1){
				logActivity('Auto Cancel Invoice: Invoice ' . $invoiceIDs . ' has been cancelled because the product was terminated.');
				$command = 'UpdateInvoice';
				$postData = array(
					'invoiceid' => $invoiceIDs,
					'status' => 'Cancelled',
				);
				$results = localAPI($command, $postData);
			}
			if(count($invoiceLines) <= 2 && Capsule::table('tblinvoiceitems')->where('invoiceid', $invoiceIDs)->where('type', 'LateFee')->get()){
				logActivity('Auto Cancel Invoice: Invoice ' . $invoiceIDs . ' has been cancelled because the only line was a Late Fee.');
				$command = 'UpdateInvoice';
				$postData = array(
					'invoiceid' => $invoiceIDs,
					'status' => 'Cancelled',
				);
				$results = localAPI($command, $postData);
			}
			if($discountLine){
				logActivity('Auto Cancel Invoice: Invoice ' . $invoiceIDs . ' had a Group Discount. This has been removed.');
				$command = 'UpdateInvoice';
				$postData = array(
					'invoiceid' => $invoiceIDs,
					'deletelineids' => array($discountLine),
				);
				$results = localAPI($command, $postData);
			}
		}
	}
});

If there's 2 or less lines, and one of them is a Late Fee, it will cancel the invoice.
When using the PreModuleTerminate hook, I can't do it for less than 2 lines because the LateFee doesn't have a relid. This should work just fine though.