Hi,
what kind of performance are expected using html formatting in a yearly report giving about 1.000 records?
I’m because I’m not getting any error; nevertheless I have very poor performances (more than 3 minutes on a G4 733) that seem to be due to html building instead of the query itself.
This is the method I’m using (if anybody has some suggestions, I’ll be glad to hear them
globals.resocontopiani = ""
var query_anno = ""
var query_anno = "select distinct yearnr from invoices order by yearnr"
var dataset_anniOrd = databaseManager.getDataSetByQuery(controller.getServerName(), query_anno, null, 100000);
var anni_ordini = dataset_anniOrd.getColumnAsArray(1)
var yearchoice = ""
var yearchoice = plugins.dialogs.showSelectDialog("Attenzione","Choose the year",
anni_ordini)
//create HTML table
var HTML='<html>'+
'<head>'+
'<style type="text/css">'+
'.normal {'+
' font-family: Arial, Helvetica, sans-serif;'+
' font-size: 11pt;'+
'}'+
'.headerUnderlined {'+
' font-family: Arial, Helvetica, sans-serif;'+
' font-size: 12pt;'+
' font-weight: bold;'+
' text-decoration: underline;'+
'}'+
'.normalItalic {'+
' font-family: Arial, Helvetica, sans-serif;'+
' font-size: 11pt;'+
' font-style: italic;'+
'}'+
'</style>'+
'</head>'+
'<table border="0" cellpadding="2" cellspacing="0" width="400" bgcolor="#ffffff">'
var query = ""
query = "SELECT"+
" r.invoice_nr, r.data,a.nome, a.cognome, SUM(li.prezuni), r.invoicesid"+
" from customers a, lineitems li, invoices r "+
" WHERE r.data BETWEEN '" + yearchoice +"-1-1' AND '" + yearchoice + "-12-31'" +
" and r.invoicesid = li.invoicesid" +
" and a.customerid = r.customerid" +
" group by r.invoicesid, r.data, a.nome, a.cognome, r.invoice_nr"+
" order by 1 asc"
var dataSet = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, 100000);
for(var i=0 ; i<= dataSet.getMaxRowIndex() ; i++)
{
dataSet.rowIndex= i
//replace null with blank
var column1 = dataSet[1]
if(column1 == null) column1 = ""
var column2 = utils.dateFormat(dataSet[2], 'dd-MM-yy')
if(column2 == null) column2 = ""
var column3 = dataSet[3]
if(column3 == null) column3 = ""
var column4 = dataSet[4]
if(column4 == null) column4 = ""
var column5 = dataSet[5]
if(column5 == null) column5 = ""
else column5 = column5.toFixed(2) //sets to fixed decimal 2
var column6 = dataSet[6]
if(column6 == null) column6 = ""
HTML = HTML + '<tr class="normal"><td><a href="javascript:globals.GOTO_invoices('+"'"+dataSet[6]+"'"+')">' + column1+'</a></td><td>'+column2+' </td><td>'+column3+'</td><td>'+column4+'</td><td align="right">'+column5+'</td></tr>'
}
var querybis = ""
querybis = "SELECT"+
" SUM(li.prezuni)"+ //sostituire con SUM(li.totbollo)
" from customers a, lineitems li, invoices r"+
" WHERE r.data BETWEEN '" + yearchoice +"-1-1' AND '" + yearchoice + "-12-31'" +
" and r.invoicesid = li.invoicesid" +
" and a.customerid = r.customerid"
var dataSetbis = databaseManager.getDataSetByQuery(controller.getServerName(), querybis, null, 100);
for(var i=0 ; i<= dataSetbis.getMaxRowIndex() ; i++)
{
dataSetbis.rowIndex= i
//replace null with blank
var column2 = dataSetbis[1]
if(column2 == null) column2 = ""
else column2 = column2.toFixed(2) //sets to fixed decimal 2
HTML = HTML + '<tr class="normal"><td></td><td></td><td></td><td></td><td align="right"><b>'+column2+'</b></td></tr>'
}
//end of the table
HTML = HTML+'</table>'+'</html>';
globals.resocontopiani = HTML
controller.show()
