jqGrid Implementation using SpServices in SharePoint

Hi All, 

This article is regarding implementation of jqGrid(demo) using SpServices(CodePlex) 

As SpServices is a jQuery library which abstracts SharePoint's Web Services and makes them easier to use. It also includes functions which use the various Web Service operations to provide more useful (and cool) capabilities. It works entirely client side and requires no server installation.

Use of SpServices and jQuery is best explained at Marc’s blog.

Following Js files are needed for JqGrid Implementation with SpServices

• jquery.js 
• jquery-ui-1.8.1.custom.min.js
• grid.locale-en.js
• jquery.jqGrid.min.js
• json2-min.js// Parsing data to json format
• jquery.SPServices-0.6.2.js// For getting the list items 

Css file required  

 ui.jqgrid.css//Style sheet of Grid 

HTML controls for jqGrid are as mentioned below. 

<div id='tblMain' style="float:left">
  <table  id="list" ></table>
  <div id="pager" style="text-align:center;"></div> 
</div> 

Loading JqGrid on the page load:

jQuery("#list").jqGrid({

  datatype: GetMyData, 
  colNames:["Project ID","Project Name","Delivery Manager","ApprovalStatus"],
  colModel:[{name:'ProjectId',index:'ProjectId',align:'left',sortable: true},
            {name:'ProjectName',index:'ProjectName',align:'left',sortable: true },
          {name:'DeliveryManager',index:'DeliveryManager',align:'left',sortable:true},
          {name:'ApprovalStatus',index:'ApprovalStatus',align: 'left',sortable: true }
                       ],
  pager: true,
  pager: '#pager', 
  pageinput: true,
  rowNum: 5,
  rowList: [5, 10, 20, 50, 100],
  sortname: 'ApprovalStatus',
  sortorder: "asc",
  viewrecords: true,
  autowidth: true,
  emptyrecords: "No records to view",
  loadtext: "Loading..."    
   });

In the above jqGrid load function I have mentioned the datatype for the grid as GetMyData() which is a function that gets triggerred first. 

The GetMyData method has function GetDataOnLoad which uses the SpServices which has the basic operation of getting the list items i.e. GetListItems, which need optional CAML Query property which will fetch the data from list with some WHERE clause.

In the code I have a list called ProjectDetailsList which will contain details of some projects which are inserted by some Project Manager or delivery manager. So the requirement was when a user log in to the system I should get the current login user name and pass the same user name to the “where” clause of query so the grid will contain data of projects to which the current logged in user is assigned as PM or DM.

To get the current login user am using SpServices Operation SpGetCurrentUser.

The method GetTheOrderByType function will make the query part for SpServices.

The functions code is as follwos:

function ForGettingUserName() {
            var userName = $().SPServices.SPGetCurrentUser({
                fieldName: "Title",
                debug: false
            });
            return userName;
        }
 

function GetMyData() {
            sortIdexName = jQuery("#list").getGridParam("sortname"); //Maintaining Consitant SortName after the Sortcol event 
            sortOrderName = jQuery("#list").getGridParam("sortorder"); //Maintaining Consistant Sort Order 
            Query = GetTheOrderByType(sortIdexName, sortOrderName);
            var CAMLViewFields = "<ViewFields>" +
                      +"<FieldRef Name='projectName' /><FieldRef Name='projectID' />"
                      + "<FieldRef Name='Title' /><FieldRef Name='deliveryManager' />"
                      + "<FieldRef Name='projectSQA' /><FieldRef Name='approvalStatus' />"
                    + "<FieldRef Name='projectStartDate' /><FieldRef Name='projectEndDate' />"
                      + "<FieldRef Name='sqasiteurl' /><FieldRef Name='ID' />"
                      + "</ViewFields>";
            GetDataOnLoad(Query, CAMLViewFields);
        } 

//Function for Getting the Query Type and the particular WHERE CLAUSE TO send the  Query to GetListItem function.   

 function GetTheOrderByType(index, sortOrder, userName) {
            var OrderByType;
if (index == "ProjectName") {
     if (sortOrder == "desc") {
OrderByType = "<Query>"+
             +"<Where><Or>"+"<Eq>"+"<FieldRef Name='deliveryManager'/><Value Type='Text'>" 
             + userName +"</Value>"+"</Eq>"
             +"<Eq><FieldRef Name='projectManager'/><Value Type='Text'>" 
             + userName + "</Value></Eq>"+"</Or></Where>"+ 
             +"<OrderBy><FieldRef  Name='projectName' Ascending='FALSE' /></OrderBy>"+
            +"</Query>"; 
} 
    else {    
OrderByType =  "<Query>"+
             +"<Where><Or>"+"<Eq>"+"<FieldRef Name='deliveryManager'/><Value Type='Text'>" 
             + userName +"</Value>"+"</Eq>"
             +"<Eq><FieldRef Name='projectManager'/><Value Type='Text'>" 
             + userName + "</Value></Eq>"+"</Or></Where>"+ 
             +"<OrderBy><FieldRef  Name='projectName' Ascending='FALSE' /></OrderBy>"+
            +"</Query>"; 
 }
}
 
 else if (index == "ApprovalStatus") {
      if (sortOrder == "desc") {
OrderByType =  "<Query>"+
             +"<Where><Or>"+"<Eq>"+"<FieldRef Name='deliveryManager'/><Value Type='Text'>" 
             + userName +"</Value>"+"</Eq>"
             +"<Eq><FieldRef Name='projectManager'/><Value Type='Text'>" 
             + userName + "</Value></Eq>"+"</Or></Where>"+ 
             +"<OrderBy><FieldRef  Name='approvalStatus' Ascending='FALSE' /></OrderBy>"+
            +"</Query>"; 
             } 
        else {  
 OrderByType = "<Query>"+
             +"<Where><Or>"+"<Eq>"+"<FieldRef Name='deliveryManager'/><Value Type='Text'>" 
             + userName +"</Value>"+"</Eq>"
             +"<Eq><FieldRef Name='projectManager'/><Value Type='Text'>" 
             + userName + "</Value></Eq>"+"</Or></Where>"+ 
             +"<OrderBy><FieldRef  Name='approvalStatus' Ascending='FALSE' /></OrderBy>"+
            +"</Query>";             
     }
       return OrderByType;
  }    

//This function gets the data from List using SpServices 
Function GetDataOnLoad (Query,CAMLViewFields) {
        $().SPServices({ 
                operation: "GetListItems",
                async: false,
                listName: "ProjectDetailsList",
                CAMLQuery: Query,
                CAMLViewFields: CAMLViewFields,
                completefunc: processResult 
            }); 
      }  

//Processing the XML result to formatted Json so that We can bind data to grid in Json format
        function processResult(xData, status) {
            var counter = 0; // Gets the total number of records retrieved from the list (We can also use xData.ItemCount method for counting the number of rows in the data )
            var newJqData = "";
            $(xData.responseXML).find("[nodeName='z:row']").each(function () {
                var JqData;
                if (counter == 0) {
                   JqData = "{id:'" + $(this).attr("ows_projectID") + "',"
                              + "cell:[" + "'" + $(this).attr("ows_projectID") + "','" +
                              $(this).attr("ows_projectName") + "','" +
                              $(this).attr("ows_deliveryManager") + "','," +
                              "]}";
                    newJqData = newJqData + JqData;
                    counter = counter + 1;
                }
                else {
                     var  JqData = "{id:'" + $(this).attr("ows_projectID") + "',"
                              + "cell:[" + "'" + $(this).attr("ows_projectID") + "','" +
                              $(this).attr("ows_projectName") + "','" +
                              $(this).attr("ows_deliveryManager") + "','," +
                              "]}";
                    newJqData = newJqData + JqData;
                    counter = counter + 1;
                }


            });
            FinalDataForGrid(newJqData, counter);
        }  
That’s it. Add the data to the grid
with the div control and the other page number calculation is for showing the
pager.


function FinalDataForGrid(jqData, resultCount) {
        dataFromList = jqData.substring(0, jqData.length - 1);
        var currentValue = jQuery("#list").getGridParam('rowNum');
        var totalPages = Math.ceil(resultCount / currentValue);
        var PageNumber = jQuery("#list").getGridParam("page"); // Current page number selected in the selection box of the JqGrid           
        //formatting rows 
        newStr = "{total:" + '"' + totalPages + '"' + "," + "page:" + '"' + PageNumber + '"' + ","
                  + "records:" + '"'
                  + resultCount + '"' + ","
                  + "rows:"
                  + "[" + dataFromList + "]}";
        var thegrid = jQuery("#list")[0]; 
        thegrid.addJSONData(JSON.parse(newStr)); //Binding data to the grid which is of JSON Format 
    } 



And the grid
works fine and fast on paging, sorting and also even search, we can make the
particular column as hyperlink which I will blog in the next part.

Sample grid is as follows and this grid has some extra columns then the mentioned in above code 

 



Thanks to Nagaraj My TL and Kamal Sir for the support... Enjoy SharePoint!!!!!!!! Check out my blog