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");
sortOrderName = jQuery("#list").getGridParam("sortorder");
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;
}
<pre>
Function GetDataOnLoad (Query,CAMLViewFields) {
$().SPServices({
operation: "GetListItems",
async: false,
listName: "ProjectDetailsList",
CAMLQuery: Query,
CAMLViewFields: CAMLViewFields,
completefunc: processResult
});
} The
processResult is the function which formats the data which can be converted to Json
and adds to the JqGrid.
The reason of formatting of data in the
following particular format is to make it readable by the Json parser which is
json2.js file. I had implemented the same JqGrid in 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 for JsonHelper class which mainly formats the data returned from the
DB <o:p>
<pre>
function processResult(xData, status) {
var counter = 0;
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");
newStr = "{total:" + '"' + totalPages + '"' + "," + "page:" + '"' + PageNumber + '"' + ","
+ "records:" + '"'
+ resultCount + '"' + ","
+ "rows:"
+ "[" + dataFromList + "]}";
var thegrid = jQuery("#list")[0];
thegrid.addJSONData(JSON.parse(newStr));
}
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
