Using jQuery based jqGrid with ColdFusion: Part 2 - Filter Records & work with Custom Data

loadComplete:function(){

//We get the Userdata for the grid.
var recorddata = $("#list").getUserData();
//If the msg type is error, we do some CSS and change text color to red, otherwise its blue
if(recorddata.TYPE == "Error"){
	$("#t_list").css("color","red")
}else{
        $("#t_list").css("color","blue")
}
//Display the message in the toolbar
$("#t_list").html(recorddata.MSG)

}

<cffunction name="buildSearchString" access="private" hint="Returns the Search Opeator based on Short Form Value">
<cfargument name="searchField" required="no" default="" hint="Field to perform Search on">
<cfargument name="searchOper" required="no" default="" hint="Search Operator Short Form">
<cfargument name="searchString" required="no" default="" hint="Search Text">

	<cfset var searchVal = "">
	
	<cfscript>
	switch(Arguments.searchOper)
	{
	case "eq":
		searchVal = "#Arguments.searchField# = '#Arguments.searchString#'";
		break;
	case "ne":
		searchVal = "#Arguments.searchField# <> '#Arguments.searchString#'";
		break;
	case "lt":
		searchVal = "#Arguments.searchField# < '#Arguments.searchString#'";
		break;
	case "le":
		searchVal = "#Arguments.searchField# <= '#Arguments.searchString#'";
		break;
	case "gt":
		searchVal = "#Arguments.searchField# > '#Arguments.searchString#'";
		break;
	case "ge":
		searchVal = "#Arguments.searchField# >= '#Arguments.searchString#'";
		break;
	case "bw":
		searchVal = "#Arguments.searchField# LIKE '#Arguments.searchString#%'";
		break;
	case "ew":
	//Purposefully breaking ends with operator (no leading ')
		searchVal = "#Arguments.searchField# LIKE %#Arguments.searchString#'";
		break;
	case "cn":
		searchVal = "#Arguments.searchField# LIKE '%#Arguments.searchString#%'";
		break;
}	
			
</cfscript>
			
<cfreturn searchVal>
	
</cffunction>

<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" hint="Whether search is performed by user on data grid">
<cfargument name="searchField" required="no" default="" hint="Field to perform Search on">
<cfargument name="searchOper" required="no" default="" hint="Search Operator Short Form">
<cfargument name="searchString" required="no" default="" hint="Search Text">
		
	<cfset var arrUsers = ArrayNew(1)>
	<cfset var strMsg = "">
	<cfset var strMsgType = "Success">
	<cfset var records = "">
	<cfset var blnSearch = false>
	<cfset var strSearch = "">		
		
	<cfif Arguments._search>
		<!--- Check search query to verify we will have no errors. --->
		<cfset strSearch = buildSearchString(Arguments.searchField,Arguments.searchOper,Arguments.searchString)>
		<cfset blnSearch = true>
		<cftry>
			<cfquery name="qryCheck" datasource="RIADemo" result="res">
				SELECT
					TOP 1 ID
				FROM
					Users
				WHERE 
					#PreserveSingleQuotes(strSearch)#
			</cfquery>
			<cfcatch type="any">
				<!--- Had an error, we won't run the search --->
				<cfset blnSearch = false>					
				<cfset strMsgType = "Error">
				<cfset strMsg = "There was an issue with the Search. An Error Report has been submitted to Support.">					
			</cfcatch>		
		</cftry>
	</cfif>
		
	<cfquery name="selUsers" datasource="RIADemo" result="res">
		SELECT
			ID, FirstName, LastName, DisplayName, UserName, UserAccountingCode, Phone
		FROM
			Users
		<!--- Run search based on flag --->
		<cfif blnSearch>
			WHERE 
				#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#,#UserAccountingCode#,#Phone#]>
		<cfset i = i + 1>			
	</cfloop>
		
	<!--- Calculate the Total Number of Pages for your records. --->
	<cfset totalPages = Ceiling(records.recordcount/arguments.rows)>
		
	<!--- If we have been succesful, we will return the Total number of records we counted --->
	<cfif strMsgType EQ "Success">
		<cfset strMsg = "Retreived #records.recordcount# Records.">
	</cfif>
		
	<!--- Set te user data. The JSON format expected back is, USERDATA : {type:value,msg:text}  --->
	<cfset userdata = {type="#strMsgType#",msg="#strMsg#"}>
		
	<!--- The JSON return --->
	<cfset strReturn = {total=#totalPages#,page=#Arguments.page#,records=#records.recordcount#,rows=arrUsers,userdata=#userdata#}>
		
	<cfreturn strReturn>
		
</cffunction>