Using jQuery based jqGrid with ColdFusion Revisited: Full CRUD grid with Advanced Searching

<script>
		
	$(document).ready(function()
		{	
			$("#list").jqGrid(
			{
				url:'Users2.cfc?method=getUsers', //CFC that will return the users
				datatype: 'json', //We specify that the datatype we will be using will be JSON
				colNames:['ID','First Name','Last Name', 'Display Name','User Name'], //Column Names
				//The Column Model to define the data. Note you can make columns non sortable, specify width, alignment, etc.
				colModel :[
					{name:'id',index:'id', width:75, sorttype:"int",editable:false,searchoptions:{sopt:['eq','lt','le','gt','ge']}},
					{name:'FirstName',index:'FirstName', width:150, sorttype:"text",searchoptions:{sopt:['eq','bw','ew','cn']},
						editable:true,edittype:"text",editoptions:{size:50},editrules:{required:true},formoptions:{elmprefix:"(*)"}},
					{name:'LastName',index:'LastName', width:150, align:"left",sorttype:"text",searchoptions:{sopt:['eq','bw','ew','cn']},
						editable:true,edittype:"text",editoptions:{size:50},editrules:{required:true},formoptions:{elmprefix:"(*)"}},
					{name:'DisplayName',index:'DisplayName', width:150, align:"left",sorttype:"text",searchoptions:{sopt:['eq','bw','ew','cn']},
						editable:true,edittype:"text",editoptions:{size:50},editrules:{required:true},formoptions:{elmprefix:"(*)"}},
					{name:'UserName',index:'UserName', width:150,align:"left",sorttype:"text",searchoptions:{sopt:['eq','bw','ew','cn']},
						editable:true,edittype:"text",editoptions:{size:50},editrules:{required:true},formoptions:{elmprefix:"(*)"}}
				],//searchoptions parameter is used to limit the operators available during search
				pager: $('#pager'), //The div we have specified, tells jqGrid where to put the pager
				rowNum:20, //Number of records we want to show per page
				rowList:[20,30,40,50], //Row List, to allow user to select how many rows they want to see per page
				sortorder: "asc", //Default sort order
				sortname: "ID", //Default sort column
				viewrecords: true, //Shows the nice message on the pager
				imgpath: '/Test/themes/basic/images', //Image path for prev/next etc images
				caption: 'Users', //Grid Name
				recordtext: "Record {0} - {1} of {2}",//Pager information to show
				rownumbers: true,//Show row numbers
				rownumWidth: "40",//Width of the row numbers column
				sortable: true,
				height:'auto', //I like auto, so there is no blank space between. Using a fixed height can mean either a scrollbar or a blank space before the pager
				mtype:'POST',	
				toolbar:[true,"top"], //Shows the toolbar at the top. We will use it to display user feedback
				//The JSON reader. This defines what the JSON data returned from the CFC should look like
				jsonReader: {
					root: "ROWS", //our data
                    page: "PAGE", //current page
                    total: "TOTAL", //total pages
                    records:"RECORDS", //total records
					userdata:"USERDATA", //Userdata we will pass back for feedback
					cell: "", //Not Used
					id: "0" //Will default to first column
					},
				editurl:"Users2.cfc?method=addeditUser" //The Add/Edit function call				
				}				
			).navGrid('#pager',
				{
					search:true,searchtitle:"Search",//title set for hover over display
					edit:true,edittitle:"Edit User",
					add:true,addtitle:"Add User",
					del:true,deltitle:"Delete User"
				},
			// Edit Options. savekey parameter will keybind the Enter key to submit.
			{editCaption:"Edit User",edittext:"Edit",closeOnEscape:true, savekey: [true,13],errorTextFormat:commonError,width:"450"
				,reloadAfterSubmit:true,bottominfo:"Fields marked with (*) are required",top:"60",left:"70"}, 
			{addCaption:"Add User",closeOnEscape:true,savekey: [true,13],errorTextFormat:commonError,width:"450"
				,reloadAfterSubmit:true,bottominfo:"Fields marked with (*) are required",top:"60",left:"70"}, //Add Options
			{url:"Users2.cfc?method=delUser",caption:"Delete User",closeOnEscape:true,errorTextFormat:commonError,top:"60",left:"70",
				reloadAfterSubmit:true}, //Delete Options
			 //Search Options. multipleSearch parameter lets it know we are going to use the new advanced search feature
			{errorTextFormat:commonError,Find:"Search",closeOnEscape:true,caption:"Search Users",multipleSearch:true,closeAfterSearch:true}
			);			
		
		//Function will be called when add/edit encounters an error. The returned message is what will be shown to user	
		function commonError(data)
		{			
			return "Error Occured during Operation. Please try again";
		}
			
		}
	);
	</script>

<cfcomponent output="false">
	
	<cffunction name="getUsers" access="remote" returnformat="json">
		<cfargument name="page" required="no" default="1" hint="Page user is on">
	    <cfargument name="rows" required="no" default="10" hint="Number of Rows to display per page">
	    <cfargument name="sidx" required="no" default="" hint="Sort Column">
	    <cfargument name="sord" required="no" default="ASC" hint="Sort Order">
	    <cfargument name="nd" required="no" default="0">
	    <cfargument name="_search" required="no" default="false">
	    <cfargument name="filters" required="no" default="">
		
		<cfset var arrUsers = ArrayNew(1)>
		<cfset var strMsg = "">
		<cfset var strMsgType = "Success">
		<cfset var records = "">
		<cfset var blnSearch = Arguments._search>
		<cfset var strSearch = "">	
		
		<!--- Determine whether this a search call and if so build our search parameters --->
		<cfif Arguments.filters NEQ "" AND blnSearch>
			<cfset stcSearch = DeserializeJSON(Arguments.filters)>
			<cfif isDefined("stcSearch.groupOp")>
				<cfset strSearch = buildSearch(stcSearch)>
			</cfif>			
		</cfif>
		
		<cfquery name="selUsers" datasource="RIADemo" result="res">
			SELECT
				ID, FirstName, LastName, DisplayName, UserName
			FROM
				Users
			<cfif blnSearch AND strSearch NEQ "">
				#PreserveSingleQuotes(strSearch)#
			</cfif>
			ORDER BY #sidx# #sord#				
		</cfquery>
		
		
		
		<cfset records = selUsers>
		
		
		<!--- Calculate the Start Position for the loop query.
		So, if you are on 1st page and want to display 4 rows per page, for first page you start at: (1-1)*4+1 = 1.
		If you go to page 2, you start at (2-)1*4+1 = 5  --->
		<cfset start = ((arguments.page-1)*arguments.rows)+1>
		
		<!--- Calculate the end row for the query. So on the first page you go from row 1 to row 4. --->
		<cfset end = (start-1) + arguments.rows>
		
		<!--- When building the array --->
		<cfset i = 1>
		
		<cfloop query="records" startrow="#start#" endrow="#end#">
			<!--- Array that will be passed back needed by jqGrid JSON implementation --->		
			<cfset arrUsers[i] = [#ID#,#FirstName#,#LastName#,#DisplayName#,#UserName#]>
			<cfset i = i + 1>			
		</cfloop>
		
		<!--- Calculate the Total Number of Pages for your records. --->
		<cfset totalPages = Ceiling(records.recordcount/arguments.rows)>
		
		<!--- The JSON return --->
		<cfset strReturn = {total=#totalPages#,page=#Arguments.page#,records=#records.recordcount#,rows=arrUsers}>
		
		<cfreturn strReturn>
		
	</cffunction>
	
	<cffunction name="addeditUser" access="remote" hint="Add or Edit User Data" returnformat="json">
		<cfargument name="FirstName" required="yes" hint="Field that was Added or editted">
		<cfargument name="LastName" required="yes" hint="Field that was Added or editted">
		<cfargument name="DisplayName" required="yes" hint="Field that was Added or editted">
		<cfargument name="UserName" required="yes" hint="Field that was Added or editted">		
		<cfargument name="ID" required="yes" hint="the User that was Added or editted">
		<cfargument name="oper" required="no" default="edit" hint="Whether this is an add or edit">
		
		<cfset var strMsg = "">
		<cfset var strMsgType = "Success">
		<cfset var userdata = "">
		
		<cfif oper EQ "edit">
			<cfset strMsg = "User Editted">		
			<!--- Take the data, update your record. Simple. --->
			<cftry>
				<cfquery name="editUser" datasource="RIADemo">
					UPDATE
						Users
					SET
						FirstName = '#Arguments.FirstName#',
						LastName = '#Arguments.LastName#',
						DisplayName = '#Arguments.DisplayName#',
						UserName = '#Arguments.UserName#'						
					WHERE
						ID = #Val(Arguments.ID)#
				</cfquery>
			<cfcatch type="any">
				<!--- Error, return message --->
				<cfset strMsgType = "Error">
				<cfset strMsg = "Error Occured when editing user. An Error report has been submitted to support.">
				<cfthrow message="Error Occured During Edit">
			</cfcatch>
			</cftry>
		<cfelse>
			<cfset strMsg = "User Added">
			<cftry>				
			<cfquery name="addUser" datasource="RIADemo">
				INSERT INTO USERS(FirstName,LastName,DisplayName,UserName)
				VALUES ('#Arguments.FirstName#','#Arguments.LastName#','#Arguments.DisplayName#','#Arguments.UserName#')
			</cfquery>
			<cfcatch>
				<!--- Error, return message --->
				<cfset strMsgType = "Error">
				<cfset strMsg = "Error occured when Adding User. An Error report has been submitted to support.">
				<cfthrow message="Error Occured During Add">
			</cfcatch>
			</cftry>
		</cfif>
		<!--- We just need to pass back some user data for display purposes --->
		<cfset userdata = {type="#strMsgType#",msg="#strMsg#"}>
		<cfset strReturn = {userdata=#userdata#}>
		
		<cfreturn strReturn>
		
	</cffunction>
	
	<cffunction name="delUser" access="remote" hint="Delete Selected User" returnformat="json">		
		<cfargument name="id" required="yes" hint="id to delete">
		
		<cfset var strMsg = "">
		<cfset var strMsgType = "Success">
		<cfset var userdata = "">
		<cfset var delUser = "">
		
		<cftry>
			<cfset strMsg = "Deleted User">
			<cfquery name="delUser" datasource="RIADemo">
				DELETE FROM Users WHERE ID = #Val(Arguments.id)#
			</cfquery>
		<cfcatch>
			<!--- Error, return message --->
			<cfset strMsgType = "Error">
			<cfset strMsg = "Error occured when Deleting User. An Error report has been submitted to support.">
		</cfcatch>
		</cftry>
		
		<cfset userdata = {type="#strMsgType#",msg="#strMsg#"}>
		<cfset strReturn = {userdata=#userdata#}>
		
		<cfreturn strReturn>
		
	</cffunction>
	
	<cffunction name="buildSearch" access="private" hint="Build our Search Parameters">
		<cfargument name="stcSearch" required="true">
		
		<!--- strOp will be either AND or OR based on user selection --->
		<cfset var strOp = stcSearch.groupOp>
		<cfset var arrFilter = stcSearch.rules>
		<cfset var strSearch = "">
		<cfset var strSearchVal = "">
		
		<!--- Loop over array of passed in search filter rules to build our query string --->
		<cfloop array="#arrFilter#" index="arrIndex">
			<cfset strField = arrIndex["field"]>
			<cfset strOp = arrIndex["op"]>
			<cfset strValue = arrIndex["data"]>
			
			<cfset strSearchVal = buildSearchArgument(strField,strOp,strValue)>
			
			<cfif strSearchVal NEQ "">
				<cfif strSearch EQ "">
					<cfset strSearch = "WHERE (#PreserveSingleQuotes(strSearchVal)#)">
				<cfelse>
					<cfset strSearch = strSearch & "#strOp# (#PreserveSingleQuotes(strSearchVal)#)">				
				</cfif>
			</cfif>
			
		</cfloop>
		
		<cfreturn strSearch>
				
	</cffunction>
	
	<cffunction name="buildSearchArgument" access="private" hint="Build our Search Argument based on parameters">
		<cfargument name="strField" required="true" hint="The Field which will be searched on">
		<cfargument name="strOp" required="true" hint="Operator for the search criteria">
		<cfargument name="strValue" required="true" hint="Value that will be searched for">
		
		<cfset var searchVal = "">
		
		<cfif Arguments.strValue EQ "">
			<cfreturn "">
		</cfif>
		
		<cfscript>
			switch(Arguments.strOp)
			{
				case "eq":
					//ID is numeric so we will check for that
					if(Arguments.strField EQ "id")
					{
						searchVal = "#Arguments.strField# = #Arguments.strValue#";
					}else{
						searchVal = "#Arguments.strField# = '#Arguments.strValue#'";
					}
					break;				
				case "lt":
					searchVal = "#Arguments.strField# < #Arguments.strValue#";
					break;
				case "le":
					searchVal = "#Arguments.strField# <= #Arguments.strValue#";
					break;
				case "gt":
					searchVal = "#Arguments.strField# > #Arguments.strValue#";
					break;
				case "ge":
					searchVal = "#Arguments.strField# >= #Arguments.strValue#";
					break;
				case "bw":
					searchVal = "#Arguments.strField# LIKE '#Arguments.strValue#%'";
					break;
				case "ew":					
					searchVal = "#Arguments.strField# LIKE '%#Arguments.strValue#'";
					break;
				case "cn":
					searchVal = "#Arguments.strField# LIKE '%#Arguments.strValue#%'";
					break;
			}			
		</cfscript>
		
		<cfreturn searchVal>
	
	</cffunction>
	
</cfcomponent>