Using jQuery based jqGrid with ColdFusion: Part 4: Full CRUD grid with Custom Search Screen

<script>
$(document).ready(function()
{
	//Our Search/Filter window, we tell jqModal to treat it as a Modal dialog window
	$('#filter').jqm();
	var selectedRow = 0;
	$("#list").jqGrid(
	{
		url:'Users3.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','User Accounting Code','Phone'], //Column Names
		/*
		The Column Model to define the data. Note you can make columns non sortable, specify width, alignment, etc.
		We also specify the editoptions, edittype=text shows a nice textbox for inline edit. 
		We have also specified Edit Rules, to say which fields are required/not required for add/edit
		*/
		colModel :[
		{name:'id',index:'id', width:50,resizable:false, sorttype:"int",resizable:false,editrules:{readonly:true}},
		{name:'FirstName',index:'FirstName', width:150,resizable:false, sorttype:"text",editable:true,edittype:"text",editoptions:{size:30,maxlength:50},editrules:{required:true}},
		{name:'LastName',index:'LastName', width:150,resizable:false, align:"left",sorttype:"text",editable:true,edittype:"text",editoptions:{size:30,maxlength:50},editrules:{required:true}},
		{name:'DisplayName',index:'DisplayName', width:150,resizable:false, align:"left",sorttype:"text",editable:true,edittype:"text",editoptions:{size:30,maxlength:50},editrules:{required:true}},
		{name:'UserName',index:'UserName', width:150,resizable:false,align:"left",sorttype:"text",editable:true,edittype:"text",editoptions:{size:30,maxlength:50},editrules:{required:true}},
		{name:'UserAccountingCode',index:'UserAccountingCode', width:150,resizable:false, sortable:false,editable:true,edittype:"text",editoptions:{size:15,maxlength:15},editrules:{required:false}},
		{name:'Phone',index:'Phone', width:150,resizable:false, sortable:false,search:false,editable:true,edittype:"text",editoptions:{size:15,maxlength:12},editrules:{required:false}}
		],						   					
		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/steel/images', //Image path for prev/next etc images
		caption: 'Users', //Grid Name
		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:'GET',
		recordtext:'Total Records', //On the demo you will notice "7 Total Records" - The Total Reocrds text comes from here
		pgtext:' of', //You notice the 1/3, you can change the /. You can make it say 1 of 3
		editurl:"Users3.cfc?method=addeditUser", //The Add/Edit function call
		toolbar:[true,"top"], //Shows the toolbar at the top. We will use it to display user feedback	 			
		//Things to do when grid is finished loading
		loadComplete:function(){
			//We get the Userdata for the grid.
			var recorddata = $("#list").getUserData();
			//show the msg in the toolbar
			$("#t_list").html(recorddata.MSG);
		},
		//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 frist column
		}
}
);//End document.ready function

//Set toolbar text color to red
$("#t_list").css("color","red");

//Here we use the navGrid to display the Search button with label Filter, and disable the add/edit/delete buttons.
$("#list").navGrid(			
	"#pager",{edit:true,edittext:"Edit",addtext:"Add",deltext:"Delete",del:true,search:false,refresh:false},//We will specify custom search/refresh buttons
	{reloadAfterSubmit:true,afterSubmit:commonSubmit,closeAfterEdit:true,width:"400"},//Options for the Edit Dialog
	{reloadAfterSubmit:true,afterSubmit:commonSubmit,closeAfterAdd:false,width:"400"},//Options for the Add Dialog
	{url:"Users3.cfc?method=delUser",closeAfterDelete:true,reloadAftersubmit:true,caption:"Delete User",msg:"Delete selected User(s)",width:"400"}//Options for Del Dialog			
	).navButtonAdd('#pager',{caption:"Search",title:"Toggle Search",buttonimg:'/Test/themes/steel/images/find.gif', onClickButton: function(id)//We add the Search button.
	{ 
		//If the search screen is hidden
		if(jQuery("#filter").css("display")=="none") 
		{ 
			//We display it 
			$("#filter").jqmShow(); 
		}else
		{
			//Otherwise its being displayed, so we hide it
			$("#filter").jqmHide(); 
		} 
	}
	}).navButtonAdd('#pager',{caption:"Refresh",title:"Reload",buttonimg:'/Test/themes/steel/images/refresh.gif',onClickButton:function(id)//The refresh button
	{
		//We reset our search form (clear any form values)
		resetForm();
		//We reset the Grid URL as we change it for the search in the gridSearch function below. This resets the grid to its original state         			
		$("#list").setGridParam(
		{
			url:"Users3.cfc?method=getUsers",
			page:1
		}
		).trigger("reloadGrid");//Reload grid trigger
	}
	});
});//End navigator manipulations

//This function will perform the search for us
function gridSearch()
{
	//Get the values from the sarch form. Very simple using jquery.
	var fn = $("#firstname_search").val();
	var ln = $("#lastname_search").val();
	var dn = $("#displayname_search").val();
	var un = $("#username_search").val();
	var uac = $("#useraccountingcode_search").val();
	//We set the grid URL and pass in some extra params for the search.
	$("#list").setGridParam(
	{
		url:"Users3.cfc?method=getUsers&FirstName="+fn+"&LastName="+ln+"&DisplayName="+dn+"&UserName="+un+"&UserAccountingCode="+uac,
		page:1
	}).trigger("reloadGrid");//Reload grid trigger
	$("#filter").jqmHide();//Hide our search/filter window dialog
}

//This function is called after an add/edit happens. We just take the MSG from the response and display it in the toolbar.
//Note currently, since we reload the grid after the add/edit, the msg will only be visible for a short second or so
function commonSubmit(data,params)
{
	var a = eval( "(" + data.responseText + ")" );//Convert returned string into JSON
	$("#t_list").html(a.USERDATA.MSG);
	resetForm();//Clear our search form
	return true;
}

//Clear our search form
function resetForm()
{
	document.FilterForm.reset();
}

</script>

<div id="filter" class="modalwin jqmWindow jqmID1" style="margin-left:5px;">		
	<div id="filterlist" class="modalwin jqmWindow jqmID3" style="top: 48px; left: 6px; z-index: 950; display: block;">
		<div id="filterheader" class="modalhead">
			<table width="100%">
			<tbody>
				<tr>
					<td class="modaltext" style="cursor: move;">Search Users</td>
					<td style="text-align: right;">
						<a class="jqmClose" href="javascript:void(0);">
							<img border="0" src="/Test/themes/steel/images/ico-close.gif"/>
						</a>
					</td>
				</tr>
			</tbody>
			</table>
		</div>
		<div id="FitlerDiv" class="modalcontent">
			<form id="FilterForm" class="FormGrid" name="FilterForm">
				<table id="Filter_list" class="EditTable" cellspacing="0" border="0" cellpading="0">
				<tbody>
					<tr id="FormError" style="display: none;"></tr>
					<tr id="tr_FirstName" class="FormData">
						<td class="CaptionTD">
							First Name: 
						</td>
						<td class="DataTD">
							<input id="firstname_search" class="FormElement" type="text" size="30" maxlength="50" name="firstname_search"/>
						</td>
					</tr>
					<tr id="tr_LastName" class="FormData">
						<td class="CaptionTD">
							Last Name: 
						</td>
						<td class="DataTD">
							<input id="lastname_search" class="FormElement" type="text" size="30" maxlength="50" name="lastname_search"/>
						</td>
					</tr>
					<tr id="tr_DisplayName" class="FormData">
						<td class="CaptionTD">
							Display Name: 
						</td>
						<td class="DataTD">
							<input id="displayname_search" class="FormElement" type="text" size="30" maxlength="50" name="displayname_search"/>
						</td>
					</tr>
					<tr id="tr_UserName" class="FormData">
						<td class="CaptionTD">
							User Name: 
						</td>
						<td class="DataTD">
							<input id="username_search" class="FormElement" type="text" size="30" maxlength="50" name="username_search"/>
						</td>
					</tr>
					<tr id="tr_UserAccountingCode" class="FormData">
						<td class="CaptionTD">
							User Accounting Code: 
						</td>
						<td class="DataTD">
							<input id="useraccountingcode_search" class="FormElement" type="text" size="30" maxlength="50" name="useraccountingcode_search"/>
						</td>
					</tr>
					<tr id="Act_Buttons">
						<td class="EditButton" colspan="2">
							<input id="sData" class="EditButton" type="submit" value="Search" onClick="return gridSearch()"/>
							<input id="cData" class="EditButton" type="reset" value="Clear"/>
						</td>
					</tr>
				</tbody>
				</table>
			</form>
		</div>
	</div>
</div>

<cfcomponent output="false">	
	<cffunction name="getUserData" access="private" returntype="query" hint="function that returns all users from database sorted">
		<cfargument name="sidx" required="no" default="" hint="Sort Column">
		<cfargument name="sord" required="no" default="ASC" hint="Sort Order">	    
	    	<cfset var selUsers = "">
	    	
		<cfquery name="selUsers" datasource="RIADemo" result="res">
			SELECT
				ID, FirstName, LastName, DisplayName, UserName, UserAccountingCode, Phone
			FROM
				Users			
			ORDER BY #sidx# #sord#				
		</cfquery>
			
		<cfreturn selUsers>
	</cffunction>
	
	<cffunction name="getUsers" access="remote" returnformat="json">
		<cfargument name="ID" required="no" default="0" hint="Search parameter">
		<cfargument name="FirstName" required="no" default="" hint="Search parameter">
		<cfargument name="LastName" required="no" default="" hint="Search parameter">
		<cfargument name="DisplayName" required="no" default="" hint="Search parameter">
		<cfargument name="UserName" required="no" default="" hint="Search parameter">
		<cfargument name="UserAccountingCode" required="no" default="" hint="Search parameter">
		<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">	    
		<cfset var strMsg = "">
		<cfset var strMsgType = "Success">
		<cfset var strReturn = "">
		<cfset var selUsers = "">
		<cfset var blnSearch = true>	

		<cftry>
			<cfquery name="selUsers" datasource="RIADemo" result="res">
				SELECT
					ID, FirstName, LastName, DisplayName, UserName, UserAccountingCode, Phone
				FROM
					Users
				WHERE
				<!--- Always true, hopefully for all Databases --->
				1=1
				<cfif Val(Arguments.ID) GT 0>
					AND ID = #Val(Arguments.ID)#
				</cfif>
				<cfif Arguments.FirstName NEQ "">
					AND FirstName LIKE '%#Arguments.FirstName#%'
				</cfif>
				<cfif Arguments.LastName NEQ "">
					AND LastName LIKE '%#Arguments.LastName#%'
				</cfif>
				<cfif Arguments.DisplayName NEQ "">
					AND DisplayName LIKE '%#Arguments.DisplayName#%'
				</cfif>
				<cfif Arguments.UserName NEQ "">
					AND UserName LIKE '%#Arguments.UserName#%'
				</cfif>
				<cfif Arguments.UserAccountingCode NEQ "">
					AND UserAccountingCode LIKE '%#Arguments.UserAccountingCode#%'
				</cfif>
				ORDER BY #sidx# #sord#				
			</cfquery>
			<cfcatch type="any">
			<!--- Send back error message if search produced bad result --->			
				<cfset blnSearch = false>
				<cfset strMsgType = "Error">
				<cfset strMsg = "There was an issue with the Search. An Error Report has been submitted to Support.">
				<cfset selUsers = getUserData(sidx=sidx,sord=sord)>
			</cfcatch>
		</cftry>
		
		<!--- Search was good, but we got no records. So we return that message back --->
		<cfif blnSearch AND selUsers.RecordCount EQ 0>
			<cfset strMsgType = "Error">
			<cfset strMsg = "No Records found matching search criteria">	
		<cfelse>
			<!--- All good, send back total records message --->
			<cfset strMsg = "#selUsers.RecordCount# Records Retreived">
		</cfif>
		
		<!--- User data ftw --->
		<cfset userdata = {type="#strMsgType#",msg="#strMsg#"}>
		
		<!--- Get JSON Format for the grid from a private function --->
		<cfset strReturn = getGridJSONFormat(page=Arguments.page,rows=Arguments.rows,qryRecords=selUsers,userdata=userdata)>

		<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="UserAccountingCode" required="yes" hint="Field that was Added or editted">
		<cfargument name="Phone" 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#',
						UserAccountingCode = '#Arguments.UserAccountingCode#',
						Phone = '#Arguments.Phone#'
					WHERE
						ID = #Val(Arguments.ID)#
				</cfquery>
			<cfcatch type="any">
				<!--- Error, return message --->
				<cfset strMsgType = "Error">
				<cfset strMsg = "Error occured when Editting User. An Error report has been submitted to support.">
			</cfcatch>
			</cftry>
		<cfelse>
			<cfset strMsg = "User Added">
			<cftry>				
			<cfquery name="addUser" datasource="RIADemo">
                                INSERT INTO USERS(FirstName,LastName,DisplayName,UserName,UserAccountingCode,Phone)
				VALUES ('#Arguments.FirstName#',
					'#Arguments.LastName#',
					'#Arguments.DisplayName#',
					'#Arguments.UserName#',
					'#Arguments.UserAccountingCode#',
					'#Arguments.Phone#')
			</cfquery>
			<cfcatch>
				<!--- Error, return message --->
				<cfset strMsgType = "Error">
				<cfset strMsg = "Error occured when Adding User. An Error report has been submitted to support.">
			</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="getGridJSONFormat" access="private" hint="get JSON Format for query for grid" retunformat="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="qryRecords" required="yes" hint="Query to convert to JSON format">
		<cfargument name="userdata" required="no" default="">
		<cfset var arrUsers = ArrayNew(1)>
		<cfset var start = 0>
		<cfset var end = 0>
		<cfset var totalPages = 0>	    
		<cfset var i = 1>
		<cfset strReturn = "">		

		<!--- 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>

		<cfloop query="qryRecords" 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(qryRecords.recordcount/arguments.rows)>

		<!--- The JSON return --->
		<cfset strReturn = {total=#totalPages#,page=#Arguments.page#,records=#qryRecords.recordcount#,rows=arrUsers,userdata=#userdata#}>
		<cfreturn strReturn>	    
	</cffunction>
</cfcomponent>