Jump to content
DennisHermannsen

Hook to cancel invoices for terminated products

Recommended Posts

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.

Share this post


Link to post
Share on other sites
//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']);
});

Share this post


Link to post
Share on other sites
4 minutes ago, MikeP said:

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

yes. 🙂

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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! 🙂

Share this post


Link to post
Share on other sites

@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 by DennisHermannsen

Share this post


Link to post
Share on other sites

@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 :)

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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, 🙂

 

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

@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.

Share this post


Link to post
Share on other sites

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 🙂

Share this post


Link to post
Share on other sites
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...

eOfZ6cv.png

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use & Guidelines and understand your posts will initially be pre-moderated