Is there a Generic Component to generate DHTML table for SQL records with Pagination in web???


Hi Folks, Hope life is treating you well. Recently i had a requirement to build a DHTML view from SQL records for a lotus notes web application. The requirement was simple that the records from SQL should be shown as a DHTML table in web with Navigation. To save time i was googling for a  component that can do the work for me. But so sad that i could not find any generic component that could do this.

So i have decided to build a component by myself and this is how i have done it.

Created a form that will display the DHTML view.

Step1: Wrote a Agent that pulls the record from SQL using LSXLC

Step2: Wrote a Agent with Input Parameter as the result LC FieldList from the above agent. This agent will generate the DHMTL and store that in a Hidden Rich Text field as mentioned below. I have used a Richtext field with the name “HTML” to avoid 32K problem while generating a huge DHTML.  The Final Table will be shown in a blocked element in the form instead of the rich text field.

Creat Table – <Table>

Get the first Record -<Tr>

Loop through all the records fetched

Generate td’s based on the column values -<Td>

Close the Rows -</tr>

Finally Close the table tag -</Table>

Step3: Included the following 2 Java script functions to to do the pagination for the HTML value stored in the Rich Text field.

function pagination()
{
 var htmlValue
 var arrResult
 var i
 var j
 var finalCount
 var noPages
 var selectedValues = “”
 var header = “”
 var row = “”
 var pages = “”
 var PagAdd
 var pageHeader = “”
 var start = “<table cellpadding=0 cellspacing=0 border=0 width=770px class=’DominoViewasTable’ id=’unique_id’>”
 var divStart = “<span style = ‘border:0;height:275px’>”
 var divEnd = “</span>”
 var Pagestart = “<table cellpadding=0 cellspacing=0 border=0 width=770px class=’DominoViewasTable’>”
 var end = “</table>”
 htmlValue = document.forms[0].HTML.value

 htmlValue = htmlValue.replace(“[[<table cellpadding=0 cellspacing=0 border=0 width=770 class=’DominoViewasTable’>”,””)

 htmlValue = htmlValue.replace(/\<\/table\>\]/gi,””)
 arrResult = htmlValue.split(“<tr”) 

// alert(arrResult.length)
 
// alert(noPages)

 if (arrResult.length>9)
 {
  noPages = arrResult.length/8
  
  var templen = noPages.toString().split(‘.’)
  if (templen.length == 2)
  {
   PagAdd = 1
  }
  else if(templen.length == 1)
  {
   PagAdd = 0
  }
 }
 else
 {
 noPages = 0
 PagAdd = 0
 }

 //noPages = parseInt(arrResult.length/5)

 if (document.forms[0].KeytoChild)
 {
 document.getElementById(“answer”).value = “”
 }
 
 if ( arrResult.length > 8)
 {
  finalCount = 10 }
 else
 {
  finalCount = arrResult.length
 }
 
 if (  noPages > 0 )
 {
  pages = “<select name=’PageNumber’ class=’txtfldstyle’ style=’width:50′ onChange=’javascript:showPages(this[this.selectedIndex].text)’>”
  

  for ( k=1;k<=parseInt(noPages)+PagAdd;k++)
  {
   pages += “<option>” + k + “</option>”
  }
  pages += “</select>”
  pageHeader = Pagestart + “<tr><td style=’text-align:center’><input type=’button’ value=’Previous’ disabled=’disabled’ class=’BtnDisabled’ title=’Previous’>&nbsp;”+ pages
  pageHeader = pageHeader +”&nbsp;<input type=’button’ value=’Next’ class=’Btn’ title=’Next’  onclick=’javacript:showPages(“+2+”)’ onmouseover=this.style.cursor=’hand’ ></td></tr>” + end
 }

 if ( arrResult[0] == “”)
 {
  i = 2  
  header = “<tr” + arrResult[1]
 }
 else
 {
  i = 1
  header = “<tr” + arrResult[0]
 }
 
 for(j=i;j<finalCount;j++)
 {
  
  if(arrResult[j] != “” && arrResult[j] )
  {
   
   row += “<tr” + arrResult[j]
  }
 }
 //alert(row)
 //alert(divStart+start + header + row + end+divEnd+pageHeader)
 document.getElementById(‘ResultTable’).innerHTML =  divStart+start + header + row + end+divEnd+pageHeader
}
function showPages(startNumber)
{
 if ( startNumber == 1 )
 {
  pagination()
  return
 }
 var htmlValue
 var arrResult
 var i
 var j
 var finalCount
 var noPages
 var selectedValues = “”
 var row = “”
 var pages = “”
 var header = “”
 var PagAdd
 var pageHeader = “”
 var divStart = “<span style = ‘border:0;height:275px’>”
 var divEnd = “</span>”
 var start = “<table cellpadding=0 cellspacing=0 border=0 width=770px class=’DominoViewasTable’ id=’unique_id’>”
 var Pagestart = “<table cellpadding=0 cellspacing=0 border=0 width=770px class=’DominoViewasTable’ >”
 var end = “</table>”
 htmlValue = document.forms[0].HTML.value
 htmlValue = htmlValue.replace(“[<table cellpadding=0 cellspacing=0 border=0 width=770 class=’DominoViewasTable’>”,””)
 htmlValue = htmlValue.replace(/\<\/table\>\]/gi,””)
 arrResult = htmlValue.split(“<tr” )
 i = (parseInt(startNumber)-1) * 8
 
 if (arrResult.length>9)
 {
  noPages = arrResult.length/8
  var templen = noPages.toString().split(‘.’)

  if (templen.length == 2)
  {
   PagAdd = 1
  }
  else if(templen.length == 1)
  {
   PagAdd = 0
  }
 }
 else
 {
 noPages = 0
 PagAdd = 0
 }
 
 if (document.forms[0].KeytoChild)
 {
 document.getElementById(“answer”).value = “”
 }
 
 if ( arrResult[0] == “”)
 {
  header = “<tr” + arrResult[1]
  
 }
 else
 {
  header = “<tr” + arrResult[0]
 }
 
 if (  noPages > 0 )
 {
  pages = “<select name=’PageNumber’ class=’txtfldstyle’ style=’width:50′ onChange=’javascript:showPages(this[this.selectedIndex].text)’>”
  for ( k=1;k<=parseInt(noPages)+PagAdd;k++)
  {
   pages += “<option>” + k + “</option>”
  }
  pages += “</select>”
  
  if ( startNumber == (parseInt(noPages)+PagAdd) )
  {
   pageHeader = Pagestart+ “<tr><td style=’text-align:center’><input type=’button’ value=’Previous’ class=’Btn’ title=’Previous’onclick=’javacript:showPages(“+(parseInt(startNumber)-1)+”)’>&nbsp;” + pages + “&nbsp;”
   pageHeader = pageHeader +”<input type=’button’ value=’Next’ disabled=’disabled’ class=’BtnDisabled’ title=’Next’></td></tr>”+end
  }
  else
  {
   pageHeader = Pagestart+”<tr><td style=’text-align:center’><input type=’button’ value=’Previous’ class=’Btn’ title=’Previous’ onclick=’javacript:showPages(“+(parseInt(startNumber)-1)+”)’>&nbsp;” + pages + “&nbsp;”
   pageHeader = pageHeader +”<input type=’button’ value=’Next’ class=’Btn’ title=’Next’ onclick=’javacript:showPages(“+(parseInt(startNumber)+1)+”)’></td></tr>”+end
  }
 }
 
 if ( arrResult.length > i )
 {
  finalCount = parseInt(startNumber) * 8
 }
 else
 {
  finalCount = arrResult.length
 }
 

 for(j=i;j<finalCount;j++)
 {
  if(arrResult[j] != “” && arrResult[j] )

  {
   row += “<tr” + arrResult[j]
  }
 }
 
 
 document.getElementById(‘ResultTable’).innerHTML =  divStart+start + header + row + end+divEnd+pageHeader
 
 for ( z=0;z<document.forms[0].PageNumber.length;z++)
 
 {
  if ( z+1 == startNumber )
  {
   document.forms[0].PageNumber[z].selected = true
   break
   
  }
 }
}

This was the final output of the above pagination and DHTML generation agent.

Hope you find this component useful.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s