Creating XML output for accounting software

[Guido1982]

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:

<?php

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

 include_once('vtlib/Vtiger/Module.php');
 global $adb;
 
 // 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];
 
 }
 
 }
 
 $domtree->save('TESTXML.xml');
?>

Which produces:

Code:

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

[joebordes]

inventoryproduct_rel.id = invoiceid or quoteid or salesorderid or purchaseorderid

[joebordes]

I just tried editing and saving an invoice and the inventory details module acted correctly: as expected it eliminated the other records and created new ones.

Note that the system never really deletes information it only marks it as deleted by setting vtiger_crmentity.deleted=1, you have to filter those out from your query with an inner join

[Guido1982]

Thanks, I'll take yout tips and work on this. In the application the records are deleted yes, but not in the database, but this may be due to the behaviour you described.