Woodstock and AJAX with DHTMLXGRID (Netbeans 6.5)

Did you know that you can use the great woodstock components with DHTMLXGRID (and all the other components), this is the first of a series of articles to show you how. (http://www.dhtmlx.com/)

Before you start – if you are coding Javascript like this you best switch to Firefox and install the FIREBUG plugin, this lets you see whats going on with javascript pages like you’ve never done before. It will also help you identify the id’s of components before you use them and see if you get any problems.

The first thing to do is to copy the DHTMLXGRID javascript and CSS code into your Resources directory of your web project. Copy the whole of the codebase directory including the subdirectories, just drag and drop them.

Now create a new page and add a layout panel called it grdJOBDISPLAY Stretch it out to the size you want.

One of the problems we have is that we want to do some javascript that runs after the component tree is loaded, this happens after the page is loaded, I found that doing onload didn’t work, so you have to create a Static Text box, turn of ESCAPE and then in the text box put

<script language=”javascript”>
initgrid();

</script>

Now switch to your JSP it’s time to implement the javascript for the grid.

First, between the <head> tags we’ll add the code to load the grid support

<webuijsf:link id=”link91″ url=”/resources/dhtmlxgrid.css”/>
<webuijsf:script id=”link29″ url=”/resources/dhtmlxcommon.js”/>
<webuijsf:script id=”link39″ url=”/resources/dhtmlxgrid.js”/>
<webuijsf:script id=”link49″ url=”/resources/dhtmlxgridcell.js”/>
<webuijsf:script id=”link59″ url=”/resources/ext/dhtmlxgrid_srnd.js”/>
<webuijsf:script id=”link69″ url=”/resources/ext/dhtmlxgrid_filter.js”/>
<webuijsf:script id=”link71″ url=”/resources/ext/dhtmlxgrid_splt.js”/>
<webuijsf:script id=”link331″ url=”/resources/ext/dhtmlxgrid_hmenu.js”/>

And finally a section of script that will run to initialise the grid. In this case you can see my code for a real screen this is more complex that you will use, here I’m using the dynamic loading grid but you can adjust the code for your own use. Note that if you are going to use the dynamic loading grid with a SQL server you should be using some paging code to return the pages of data that is required, this is easy with SQL 2005 and MYSQL (LIMIT) but with SQL 2000 it is a complete dog.

To do paging with SQL Server 2000 I use http://esersahin.wordpress.com/2008/11/24/efficient-and-dynamic-server-side-paging-with-sql-server-2000/ this works like a dream and more than makes up for the lack of it working otherwise.

Also in this code I stretch the grid to be the width of the screen.

                        getElementByIdCompatible('form1:grdJOBDISPLAY').style.height=(pageHeight()-200)+"px";
                        getElementByIdCompatible('form1:grdJOBDISPLAY').style.width=(pageWidth()-30)+"px";

And in fact I make it the height of the screen – 200 pixels.

Note that here I also have a couple of other hidden fields (HIDDEN boxes) I use to pass data from the javascript at the front to the code at the back end.

form1:layoutPanel1:txtTABLENAME – stores the tablename to pull from, I dynamically set this when searching etc. It is bound to a variable stored in the session bean so I can set it or read it from the backing bean OR javascript.

form1:layoutPanel1:txtSQL- stores the WHERE statement that I’m going to use to filter the data in the grid. Again, bound to a session bean variable means that I can have a button you click on that changes the where statement.

<webuijsf:markup id="gridscript">
                        <script language="javascript">

                        var gridQString = "";

                        // called from a javascript function

                        function init() {
                        getElementByIdCompatible('form1:grdJOBDISPLAY').style.height=(pageHeight()-200)+"px";
                        getElementByIdCompatible('form1:grdJOBDISPLAY').style.width=(pageWidth()-30)+"px";
                        mygrid = new dhtmlXGridObject('form1:grdJOBDISPLAY');
                        mygrid.imgURL = "img/";
                        mygrid.setHeader("Job No, Job Date,POD,Hist, Svc,Sub,Haz,Pcs,Wgt,Acc No,Customer,Co Name, Town, Driver, Hdlg Dep,Close Time,Status,Print ");
                        mygrid.setInitWidths("100,100,60,60,50,50,50,50,50,100,200,200,200,100,200,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100");
                        mygrid.setColAlign("left,left,center,left,left,left,left,left,left,left,left,left,left")
                  //      mygrid.setColTypes("ro,ro,ro,ro,ro,ro,ro,ro,ro,ro,ro,ro,ro,ro,ro");
                        mygrid.setColSorting("str,date,str,str,str,str,str,str,str,str,str,str,str,str")
                        mygrid.setSkin("light");
                        mygrid.setDateFormat("%d/%b/%Y");
                        mygrid.enableHeaderMenu();
                        mygrid.setAwaitedRowHeight(25);

                        // mygrid.attachHeader("#text_filter,#combo_filter,#numeric_filter");

                        // NOTE YOU NEED TO INSTALL A STORED PROCEDURE IN THE SQL SERVER TO MAKE THIS WORK

                                        mygrid.attachHeader("#text_filter,#rspan,#rspan,#rspan,#rspan,#rspan,#rspan,#rspan,#rspan,#text_filter,#rspan,#rspan,#rspan,#text_filter,#text_filter,#rspan,#text_filter");
                        mygrid.init();
                        // mygrid.splitAt(1);
                        gridQString ="../jobscreendriver.jsp?tbl="+getElementByIdCompatible("form1:layoutPanel1:txtTABLENAME").value+"&amp;whr="+getElementByIdCompatible("form1:layoutPanel1:txtSQL").value;
                        mygrid.loadXML(gridQString);
                        mygrid.enableSmartRendering(true);

                        var sLastSQLWhere='';

                        mygrid.attachEvent("onFilterStart",function(cols,vals) {

                        var sAND='';
                        var sSQLWhere='';
                        if (vals[0]!='') {
                            sSQLWhere="jobno LIKE 'PERCENT"+vals[0]+"PERCENT'"
                            sAND=" AND "
                        }
                        if (vals[1]!='') {
                            sSQLWhere=sSQLWhere + sAND+" accountno LIKE 'PERCENT"+vals[1]+"PERCENT'";
                            sAND=" AND ";
                        }
                        if (vals[2]!='') {
                            sSQLWhere=sSQLWhere + sAND+" isporfleetid LIKE 'PERCENT"+vals[2]+"PERCENT'";
                            sAND=" AND ";
                        }
                         if (vals[3]!='') {
                            sSQLWhere=sSQLWhere + sAND+" handlingdepot LIKE 'PERCENT"+vals[3]+"PERCENT'";
                            sAND=" AND ";
                        }
                        if (sLastSQLWhere!=sSQLWhere) {
                        sLastSQLWhere=sSQLWhere;
                        mygrid.clearAll();
                        getElementByIdCompatible("form1:layoutPanel1:txtSQL").value=sSQLWhere;
                        mygrid.loadXML("../jobscreendriver.jsp?tbl="+getElementByIdCompatible("form1:layoutPanel1:txtTABLENAME").value+"&amp;whr="+ sSQLWhere);
                        mygrid.enableSmartRendering(true);
                        }
                        return false;  //block default filters
                        });

                        }

                        function getElementByIdCompatible(the_id) {
                        if (typeof the_id != 'string') {
                        return the_id;
                        }

                        if (typeof document.getElementById != 'undefined') {
                        return document.getElementById(the_id);
                        } else if (typeof document.all != 'undefined') {
                        return document.all[the_id];
                        } else if (typeof document.layers != 'undefined') {
                        return document.layers[the_id];
                        } else {
                        return null;
                        }
                        }

                        // Process the clicked Job button
                        function editJob(sJobNo) {
                        getElementByIdCompatible("form1:layoutPanel1:txtJOBNO").value=sJobNo.id;
                        getElementByIdCompatible("form1:layoutPanel1:cmdEDITJOB").click();
                        return false;
                        }

                        </script>
                    </webuijsf:markup>

Now then. You will need a JSP file to deliver the data to the grid –

            mygrid.loadXML("../jobscreendriver.jsp?tbl="+getElementByIdCompatible("form1:layoutPanel1:txtTABLENAME").value+"&amp;whr="+ sSQLWhere);

Here I have a JSP file that creates some xml – this is more complex than you might need for your applications but it does some interesting things with being able to filter data. You will have to adjust this to your database and situation, note that it uses the paging stored procedure to provide the data back to the grid on an SQL 2000 server.  Also I change the colour of the row to reflect the status of the record.

Lastly before I send the sql statement I replace the % signs with PERCENT and then convert it back in the code – you also need to filter out & signs in the xml and replace with &amp; otherwise you’ll get the “invalid XML statement”

<?xml version="1.0" encoding="ISO-8859-1"?>
<%@ page contentType="text/xml;charset=ISO-8859-1" %>
<%@ page import = "java.sql.*" %>
<%
        String db_ipp_addr = "insert ip address";
        String db_username = "insert username";
        String db_password = "insert password";
        String db_name = "insert database";

// set content type and xml tag

//   out.println("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
// response.setContentType("text/xml");
// define variables from incoming values
        Integer posStart = 0;
        if (request.getParameter("posStart") != null) {
            posStart = new Integer(request.getParameter("posStart"));
        } else {
            posStart = 0;
        }

        Integer count = 0;
        if (request.getParameter("count") != null) {
            count = new Integer(request.getParameter("count"));
        } else {
            count = 20;
        }
        if (posStart.equals("0")) {
            posStart = 0;
        }

// connect to database
        Connection connection = null;
        Statement statement = null;
        Statement history = null;
        ResultSet rs = null;
        ResultSet Historyrs = null;

        String connectionURL = "jdbc:sqlserver://" + db_ipp_addr + ":1433;databaseName=" + db_name;

        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
        connection = DriverManager.getConnection(connectionURL, db_username, db_password);
// connection.setCatalog("dbo");
// query to products table
        String sTableName = "dbo.tbljobs";
        if (request.getParameter("tbl") != null) {
            sTableName = request.getParameter("tbl");

        }

        String sql = "SELECT * FROM " + sTableName;
        String sCancelled = " Cancelled=0 AND ";

        if (request.getParameter("whr") != null) {
            if (request.getParameter("whr").toLowerCase().contains(" cancelled")) {
                sCancelled = "";
            }
            sql = sql.trim() + " WHERE " + sCancelled + " JobNo Not Like 'CR%' AND " + request.getParameter("whr");
        } else {
            sql = sql.trim() + " WHERE " + sCancelled + " JobNo Not Like 'CR%'";
        }
        sql=sql.trim();
        if (sql.substring(sql.length()-3,sql.length()).equals("AND")) {
            sql=sql.substring(0,sql.length()-3);
            }
        sql=sql.replace("PERCENT","%");
// if this is the first query - get total number of records in the query result
        String totalCount = "";
        if (posStart == 0) {
            String sqlCount = "Select count(*) as cnt from (" + sql + ") as tbl";
            statement = connection.createStatement();

            rs = statement.executeQuery(sqlCount);
            rs.next();
            totalCount = rs.getString("cnt");

            rs.close();
        } else {
            totalCount = "";
        }

        CallableStatement getData = connection.prepareCall("{call returnpage(?,?,?,?)}");
        getData.setString(1, sql);

        getData.setInt(3, posStart);
        getData.setInt(4, (posStart + count));
        try {
        getData.executeQuery();

        rs = getData.getResultSet();
        } catch (Exception e) {
            System.out.println (sql);
        System.out.println ("Error Getting Record "+e);
          }
// output data in XML format
        out.println("<rows total_count='" + totalCount + "' pos='" + posStart + "'>");
        String sThisColor;
        String sStatus;

        while (rs.next()) {

            sStatus = rs.getString("status").trim();

            sThisColor = "#ffffff";
            if (sStatus.equals("XXXX")) {
                sThisColor = "#ff0000";
            } else if (sStatus.equals("YYYYY")) {
                sThisColor = "#ffcc00";
            } else if (sStatus.equals("EEEEE")) {
                sThisColor = "#ffcc00";
            } 

            out.println("<row style=' border-width: 1px; border-style: solid; border-color: rgb(253, 253, 253) rgb(147, 175, 186) rgb(147, 175, 186) white;padding:1px;height:22px;background-color:" + sThisColor + "' id='" + rs.getString("jobno") + "'>");

            out.println("<cell>");
            out.println(rs.getString("field1"));
            out.println("</cell>");
            out.println("<cell>");
            out.println(rs.getDate("field2"));
            out.println("</cell>");
            out.println("<cell>");
            out.println(rs.getString("field4") + "");  // value for product name
            out.println("</cell>");
            out.println("</row>");
        }
        out.write("</rows>");

        rs.close();

%>

Have fun!
Advertisements

About Paul C

I am a Senior Java programmer/Web Technologies, Play in a band called Mandolin Monday, I have a cat and I live on a boat.

Posted on March 13, 2009, in Howto. Bookmark the permalink. 1 Comment.

  1. Yaneth Lizardo

    Excelente, lo estaba buscando desde hace mucho, gracias.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: