jqGrid Implementation Using SpServices in SharePoint

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 triggered first.

The GetMyData method calls the function GetDataOnLoad which uses SpServices to get the list items i.e. GetListItems, which need an optional CAML Query property which will fetch the data from the 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 logs in to the system I should get the current login user name and pass the same user name to the "where" clause of the 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 logged-in user I am using the SpServices operation SpGetCurrentUser.

The GetTheOrderByType function will make the query part for SpServices.

The functions code is as follows:

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 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;
}

The processResult is the function which formats the data which can be converted to Json and added to the JqGrid.

The reason of formatting of data in the following particular format is to make it readable by the Json parser which isjson2.js file. I had implemented the same JqGrid in an ASP.Net application with AJAX calls where it was returning the data in this format and some other bloggers also used the same data format in the MVC or ASP.Net application with the help of the JsonHelper class which mainly formats the data returned from the DB.

//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 {
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);
}

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 a hyperlink which I will blog in the next part.

A sample grid is as follows and this grid has more columns than in the previous code.