CFLib.org – Common Function Library Project

queryConvertForjqGrid(q, page, pageSize)

Last updated March 24, 2011

author

Scott Stroz

Version: 0 | Requires: CF9 | Library: DataManipulationLib

Description:
This function works similarly to the native function queryConvertForGrid(). It is used to format query data in a format that is easily used by jqGrid. In order for jqGrid to be able to read/use the data structure, you must specify the following in your jqGrid configuration: jsonReader : { repeatitems: false, id: "{id}}" }, Where {id} is the unique identifier for each row in the query object. If the data contained in a column is a date, it is automatically formatted into yyyy-dd-mm HH:mm:ss format.

Return Values:
Returns a structure with the following keys {page, records, rows[], total}

Example:

<cfset myQuery = queryNew("id,score") />
<cfloop from="1" to="100" index="i">
	<cfset queryAddRow(myQuery) />
	<cfset querySetCell(myQuery, "id", i) />
	<cfset querySetCell(myQuery, "score", randRange(50, 200)) />
</cfloop>
<cfset pagedresults = queryConvertForjqGrid(myQuery, 2, 15) />
<cfdump var="#pagedresults#">

Parameters:

Name Description Required
q The query to be paginated Yes
page The page number to be returned Yes
pageSize The number of items per page Yes

Full UDF Source:

/**
 * Creates a data structure that can be easily used by jqGrid.
 * 
 * @param q 	 The query to be paginated (Required)
 * @param page 	 The page number to be returned (Required)
 * @param pageSize 	 The number of items per page (Required)
 * @return Returns a structure with the following keys {page, records, rows[], total} 
 * @author Scott Stroz (scott@boyzoid.com) 
 * @version 0, March 24, 2011 
 */
function queryConvertForjQGrid( q, page, pageSize ){
	/*
	NOTE: In order for jqGrid to be able to use the result of this function
	you MUST add this to you jqGrid config:
	jsonReader : {
			repeatitems: false, 
			id: "{id}}"
		},
	Where {id} is the unique identifier for each row in the query object.
	*/
	var ret = {};
	var row = {};
	var cols = listToArray( q.columnList );
	var col = "";
	var i = 0;
	var end = arguments.page * arguments.pageSize;
	var start = end - (arguments.pagesize - 1);
	ret[ "total" ] = 0;
	ret[ "page" ] = arguments.page;
	ret[ "records" ] = arguments.q.recordcount;
	if( q.recordCount ){
		ret[ "total" ] = ceiling( arguments.q.recordcount / arguments.pageSize );
	}
	ret["rows"] = [];
	for( i=start; i LTE min(q.recordCount, end); i++ ){
		structClear( row );
		for(col in cols){
			if(isDate( q[ col ][ i ] ) ){
				row[ lcase( col ) ] = dateFormat( q[ col ][ i ], "yyyy-dd-mm" ) & " " & timeFormat( q[ col ][ i ], "HH:mm:ss" );
			}
			else{
				row[ lcase( col )] = q[ col ][ i ];
			}
		}
		arrayAppend( ret[ "rows" ], duplicate( row ) );
	}
	return ret;
}
blog comments powered by Disqus

Search CFLib.org


Latest Additions

Kevin Cotton added
date2ExcelDate
May 5, 2016

Raymond Camden added
CapFirst
April 25, 2016

Chris Wigginton added
loremIpsum
January 18, 2016

Gary Stanton added
calculateArrival...
November 19, 2015

Sebastiaan Naafs - van Dijk added
getDaysInQuarter
November 13, 2015

Created by Raymond Camden / Design by Justin Johnson