Generate invoices API

[Swehoster]

Hi all.

I'm in need of generating a one time invoice for about 2000 clients.
It is for their domains with a certain registrar.

Clients can have more than one domain and therefore there should only be one invoice with multiple domains in that case.

I get domain prices and info from tbldomainpricing and tbldomains.

I'm having problem with setting all together as a foreach loop around the CreateInvoice function in WHMCS.

Can anyone guide me in the right direction it would be most appreciated.

I've attached a test file as well.

/Fredrik

 

transfer_generate_invoice.php

Edited March 29, 2019 by Swehoster

[steven99]

From a quick look at the code, here is my thoughts:

• You need a main foreach to loop through the clients
• Put the createinvoice in to the $domaintld foreach loop
  • Change the loop to give you the array key number so that you can use that as the number in the line item.
    • foreach($domaintld as $key => $tld)
    • Or increment a variable like:  $x++; and use that for the number
    • Then for line items, you would use:
      •  'itemamount'.$key+1 => '10.00',
        • (arrays start at 0, so need to add a one to them)

If you are not sure about the above, let me know.  (though this thread probably should be in the developer community.)

[Kian]

This one works but please notice that I'm using PDO therefore you should update the query to Capsule or implement your own PDO class or whatever you want to use.

<?php

$Registrar = 'internetbs'; // Change accordingly
$AdminUser = 'admin'; // Just for backward-compatibility

// I'm selecting UserIDs based on Registrar and specific Domain status. In fact I suppose that you don't want to bill customers with Cancelled domains

$query = $pdo->prepare('SELECT userid FROM tbldomains WHERE registrar = :registrar AND status IN ("Active", "Grace", "Redemption", "Expired") GROUP BY userid');
$query->execute(array('registrar' => $Registrar));
while ($row = $query->fetch())
{
	$postData['userid'] = $row->userid;
	$postData['status'] = 'Unpaid';
	$postData['sendinvoice'] = '1'; // 0 = Do not send Invoice Created Email
	$postData['date'] = date('Y-m-d');
	$postData['duedate'] = date('Y-m-d', strtotime('+15 day')); // Due Date is current date +15 days. Change accordingly
	$postData['autoapplycredit'] = '1'; // 0 = Do not apply credit automatically
	$postData['itemdescription1'] = 'Pizza'; // Invoice Item description
	$postData['itemamount1'] = '9.99'; // Price
	$postData['itemtaxed1'] = '1'; // 0 = Not taxed
	
	$results = localAPI('CreateInvoice', $postData, $AdminUser); // Create Invoice
}

 

[Swehoster]

Thank you all.
I finally managed to get it running with the following code.

<?php
// *************************************************************************
// * Generate invoices for client domains with certain registrar.
// *															
// * CreateInvoice:												
// * https://developers.whmcs.com/api-reference/createinvoice/
// * Interacting with the Database:							
// * https://developers.whmcs.com/advanced/db-interaction/	
// * GetTLDPricing:											
// * https://developers.whmcs.com/api-reference/gettldpricing/
// *														
// *************************************************************************
// * @author     Fredrik Hallgren							
// * @copyright  Ballou Internet Services AB 2019			
// * @version    WHMCS Version: 7.7.1						
// *************************************************************************
use WHMCS\ClientArea;
use WHMCS\Database\Capsule;

define('CLIENTAREA', false);

require __DIR__ . '/init.php';

//### Get pricing
$tldPrices = localAPI('GetTLDPricing', ['currencyId' => '1']);

//### Get UserID & Domains
foreach (Capsule::table('tbldomains')->select('userid','domain')->where([['registrar', '=', 'theregistrar'],['status', '=', 'Active']])->get() as $domains) {
    $oneDomain = ['domain' => $domains->domain,
                  'price' => getRenewPrice($tldPrices, $domains->domain)];
    
    $invoiceData[$domains->userid][] = $oneDomain;
}

//### Get domains per user and create array
foreach($invoiceData as $userId => $domains) {
    $postData = array(
        'userid' => $userId,
        'status' => 'Unpaid',
        'sendinvoice' => '1',
        'paymentmethod' => 'paypal',
        'taxrate' => '25.00',
        'date' => date('Y-m-d'),
        'duedate' => date('Y-m-d', strtotime('+30 day')),
        'autoapplycredit' => '0',
    );

    $index = 0;
    foreach ($domains as $domain) {
        $index++;
        $postData['itemdescription' . $index] = 'Renewal of domain: ' . $domain['domain'];
        $postData['itemamount' . $index] = $domain['price'];
        $postData['itemtaxed' . $index] = '1';
    }

    $results = localAPI('CreateInvoice', $postData);

}
//### Get price per TLD (add discount here if needed)
function getRenewPrice($tldPrices, $domain)
{
    $tld = ltrim(strstr($domain, '.'), '.');
    return $tldPrices["pricing"][$tld]["renew"]["1"];
}
?>

 

[Kian]

Are you sure that you want to filter just by Active status? You should probably consider also Grace, Redemption and Expired since domain in such status can turn back to Active if renewed.

[Swehoster]

In our case it works just fine.
But how would I be able to use that kind of query in the "Capsule"?
It would be great to know how to use your "status IN ("Active", "Grace", "Redemption", "Expired")"

Regards
Fredrik

[brian!]

32 minutes ago, Swehoster said:

But how would I be able to use that kind of query in the "Capsule"?
It would be great to know how to use your "status IN ("Active", "Grace", "Redemption", "Expired")" 

foreach (Capsule::table('tbldomains')->select('userid','domain')->where('registrar','theregistrar')->whereIn('status',['Active','Grace','Redemption','Expired'])->get() as $domains) {

 

[Swehoster]

Thank you Brian, I'll save this.