Invoice items from database or api

[jbr1]

Hi,

 

I'm trying to get the items of an invoice from the database.

The information i want to get is just like on the invoice, only then with every item i also want to know the product id.

 

When is use the API function "Get Invoice" it doesn't display the items at all.

 

I'm asking this on this forum because i tried a lot of ways to get the information i want, but WHMCS doesn't seem to save this information?

 

Another way this could be solved is expanding the Hook "InvoiceCreated" so that it doesn't only gives back the invoiceid, but also the information like products and prices.

 

Thanks in advance, Jeffry

[laszlof]

According to the API docs, The GetInvoice function should return the items.

Edited September 29, 2010 by laszlof

[jbr1]

I know, but i only get this:

 

<?xml version="1.0" encoding="utf-8"?>
<whmcsapi version="4.3.1">
<action>getinvoice</action>
<result>success</result>
<invoiceid></invoiceid>
<invoicenum></invoicenum>
<userid>3</userid>
<date>2010-09-29</date>
<duedate>2010-10-14</duedate>
<datepaid>0000-00-00 00:00:00</datepaid>
<subtotal>26.61</subtotal>

<credit>0.00</credit>
<tax>0.00</tax>
<tax2>0.00</tax2>
<total>26.61</total>
<balance>26.61</balance>
<taxrate>0.00</taxrate>
<taxrate2>0.00</taxrate2>
<status>Unpaid</status>
<paymentmethod>banktransfer</paymentmethod>

<notes><![CDATA[]]></notes>
</whmcsapi>

 

And i use this code:

$url = "http://www.myurl.com/includes/api.php"; # URL to WHMCS API file

$postfields["username"] = "<username>"; 
$postfields["password"] = md5(<pass>);

$postfields["action"] = "getinvoice";
$postfields["invoiceid"] = $_GET["Id"]; // for testing so i only have to put ?Id=<id> behind my URL, doesn't change anything to how it works


$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch, CURLOPT_TIMEOUT, 100);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_POSTFIELDS, $postfields);
$data = curl_exec($ch);
curl_close($ch);

echo $data;

[laszlof]

Probably should open a ticket, I am getting the same results. The API documentation appears to be wrong.

 

You could write a SQL query to grab the stuff you need though.

[jbr1]

Ok, thanks for checking, i will open a ticket.

 

Yes I preferably make the connection with MYSQL.

But to me the database tables of an invoice are not structured in a way you can get the product id's.

Normally i know my way in databases, i've been working with them for quite some time now.

 

But maybe i am overlooking something or there are smarter people here (there have to be), that is why i asked the question here.

 

 

Jeffry

[laszlof]

I see what you mean. You'll probably have to match the description field in tblinvoiceitems against the stuff in tblproducts.. But even then you arent going to get all the config options correctly unless you do some post processing with regular expression matching.

 

Its doable, just a pain in the butt.

[jbr1]

Yeah, thats the only way i can think of. It's not the prettiest way.

E.g. someone changes the name of an product before the information of the invoice is synchronized and it doesn't properly work... Thats just a thing I have to deal with i gues.

[laszlof]

I can understand why they do it this way, as there are an infinite number of possible packages when you start adding in configurable options. I supposed if those were separate line items it would make it easier..

 

There doesn't seem to be any easy or nice way of doing this.