11-12-2015, 07:23 PM
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:
Which produces:
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.
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.