CoreBOSBB

Full Version: Creating XML output for accounting software
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
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.
inventoryproduct_rel.id = invoiceid or quoteid or salesorderid or purchaseorderid
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
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.