Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Creating XML output for accounting software
I'm in the process of creating an XLM output for accounting software. At first I wanted to base this on the 'Inventory details' module, but there's a problem. Here's what I have so far:

PHP Code:

// error_reporting(E_ALL);
// ini_set("display_errors", "on");

// Create DOM document
$domtree = new DOMDocument('1.0''UTF-8');
// Create root element
$xmlRoot $domtree->createElement('xml');
// Append XML element to root
$xmlRoot $domtree->appendChild($xmlRoot);
// Get all invoices
$invoiceresult $adb->pquery('SELECT invoiceid, subject, invoicedate, invoice_no FROM vtiger_invoice',array());
// Loop invoices
while ($invoicerow=$adb->fetch_array($invoiceresult)) {
// Set empty 'previousInvoice'
$previousInvoice '';
// Get inventory detail records for this invoice
$inventoryresult $adb->pquery('SELECT productid, account_id, related_to FROM vtiger_inventorydetails WHERE related_to=?',array($invoicerow[invoiceid]));
// Loop inventory details
while ($inventoryrow=$adb->fetch_array($inventoryresult)) {
// Set and append XML children to xml root element only when not the same number as the last iteration
if ($inventoryrow[related_to] != $previousInvoice) {
// Create the element in the DOM
$currentInvoice $domtree->createElement('invoice');
// Append it to the XML tree
$currentInvoice $xmlRoot->appendChild($currentInvoice);
// Create the invoice number element in the DOM
$currentInvoiceNo $domtree->createElement('invoicenumber'$invoicerow[invoice_no]);
// Append it to the current invoice
$currentInvoiceNo $currentInvoice->appendChild($currentInvoiceNo);
// Set the invoice date to correct date format
$date = new DateTime($invoicerow[invoicedate]);
// Create the invoice date element in the DOM
$currentInvoiceDate $domtree->createElement('invoicedate'$date->format('d-m-Y'));
// Append it to the current invoice
$currentInvoiceDate $currentInvoice->appendChild($currentInvoiceDate);
// Create an element called 'products'
$currentInvoiceProducts $domtree->createElement('products');
// Append it to the invoice
$currentInvoiceProducts $currentInvoice->appendChild($currentInvoiceProducts);

// Add an 'account' element to the DOM
$currentAccount $domtree->createElement('accounts');
// append the account element to the current invoice
$currentAccount $currentInvoice->appendChild($currentAccount);
// Select account details from the database based on account_id in inventory details line
$accountresult $adb->pquery('SELECT accountname, account_no FROM vtiger_account WHERE accountid=?',array($inventoryrow[account_id]));
// Get the account's row from the query
$accountName $adb->query_result_rowdata($accountresult,0);
// Create a DOM element for the current account name
$currentAccountName $domtree->createElement('accountname',$accountName[accountname]);
// Append this to the currentAccount
$currentAccountName $currentAccount->appendChild($currentAccountName);
// Create a DOM element for the current account no
$currentAccountNo $domtree->createElement('accountno',$accountName[account_no]);
// Append this to the currentAccount
$currentAccountNo $currentAccount->appendChild($currentAccountNo); 
// Select products from the database based on product id from inventory details
$productresult $adb->pquery('SELECT productname FROM vtiger_products WHERE productid=?',array($inventoryrow[productid]));
 while (
$product=$adb->fetch_array($productresult)) {
// Create an XML dom element for each product
$currentProduct $domtree->createElement('product',$product[productname]);
// Append each one to the 'CurrentProducts' element
$currentProduct $currentInvoiceProducts->appendChild($currentProduct);

$previousInvoice $inventoryrow[related_to];

Which produces:

<xml><invoice><invoicenumber>20151252</invoicenumber><invoicedate>09-11-2015</invoicedate><products><product>Zak granulaat</product><product>Verpakking- en verzendkosten</product><product>Zak granulaat</product><product>Verpakking- en verzendkosten</product></products><accounts><accountname>Schurer Kamphuis</accountname><accountno>ACC962</accountno></accounts></invoice><invoice><invoicenumber>20151256</invoicenumber><invoicedate>06-11-2015</invoicedate><products><product>GVL40</product><product>Verpakking- en verzendkosten</product></products><accounts><accountname>Autobedrijf Taris</accountname><accountno>ACC1055</accountno></accounts></invoice><invoice><invoicenumber>20151261</invoicenumber><invoicedate>06-11-2015</invoicedate><products/><accounts><accountname>Autoherstel Charlois</accountname><accountno>ACC1979</accountno></accounts></invoice></xml>

That's all very nice, but as you can see the first invoice holds the same products twice. This is because I edited and saved this invoice twice. I checked the database and the Inventory details module creates a record each and every time the related module record saves. This could be fine, maybe it's the designers intended behaviour, so not a complaint about the module, but not the behaviour I want.

So I searched for a way to list products and services related to an invoice from the traditional inventory module. I found that in the inventory_products_rel table all products ever listed get a record, and products that belong to the same inventory (list) receive the same inventory number. What I can't find out is how an invoice, SO or something like this knows which inventory number to use. I need this information to list the products, prices etc. in my XML.

Messages In This Thread
Creating XML output for accounting software - Guido1982 - 11-12-2015, 07:23 PM

Forum Jump:

Users browsing this thread: 1 Guest(s)