updated datatables_processing.cfm

datatables_processing.cfm

<!---
Script: DataTables server-side script for ColdFusion (cfm) and MySQL
License: GPL v2 or BSD (3-point)
Notes:
tested with DataTables 1.6.1 and jQuery 1.2.6+, Adobe ColdFusion 9 (but should work fine on at least 7+)
to work with pre 1.6 datatables replace both occurances of sSortDir_ with iSortDir_
Get a free developer version of ColdFusion from http://www.adobe.com/products/coldfusion/
or try out the open source railo cfml engine from http://www.getrailo.org/
or try out the open source openbd cfml engine from http://www.openbluedragon.org/
--->
<!--- turn off debugging output --->
<cfsetting showDebugOutput="No">

<!---
Paging
--->
<cfparam name="url.iDisplayStart" default="0" type="integer" />
<cfparam name="url.iDisplayLength" default="10" type="integer" />
<cfparam name="url.sIndexColumn" default="1" />
<cfparam name="url.sSearch" default="" type="string" />
<cfparam name="url.iSortingCols" default="0" type="integer" />

<cfset columnOrderStruct=structnew()> 

<cfoutput>
<cfloop collection=#mycolumnstruct# item="i">
  <cfset val =StructInsert(columnOrderStruct, "#i#", "#mycolumnstruct[i]#")> 
</cfloop>
</cfoutput>

<!--- initial data set for query of queries --->
<CFQUERY DATASOURCE='#DataSource#' USERNAME='#DataUserName#' PASSWORD='#DataPassWord#' NAME='qFiltered'>
;WITH Results_CTE AS
(
	SELECT
	<cfoutput>
		<cfloop list = "#columnOrder#" index = "column">
			<cfset temp = StructFind(columnOrderStruct,column)>
			#PreserveSingleQuotes(temp)# as [#column#],
		</cfloop>
	</cfoutput>
	ROW_NUMBER() OVER (
		ORDER BY
	  	<cfloop from="0" to="#url.iSortingCols-1#" index="thisS">
	   		<cfif thisS is not 0>, </cfif>
	   		<cfset temp = StructFind(columnOrderStruct,listGetAt(columnOrder,(url["iSortCol_"&thisS]+1)))>
	   		#PreserveSingleQuotes(temp)#
	   		<cfif listFindNoCase("asc,desc",url["sSortDir_"&thisS]) gt 0>
		   		#url["sSortDir_"&thisS]#
			</cfif>
		</cfloop>
	
	) as [RowNum]
	<cfinclude template="#filename#_clause.cfm">		
	<cfif len(trim(url.sSearch))>
		<!--- filter --->
		AND (
			1 = 0
			<cfoutput>
				<cfloop list = "#columnOrder#" index = "column">
				<cfset temp = mycolumnstruct[column]>
				OR #PreserveSingleQuotes(temp)# LIKE <cfqueryparam value="%#ucase(trim(url.sSearch))#%">
				</cfloop>
			</cfoutput>
		)
	</cfif>
	  
)
SELECT *
FROM Results_CTE
WHERE RowNum >= #url.iDisplayStart#
AND RowNum < #url.iDisplayStart# + #url.iDisplayLength#
</cfquery>

<!--- get count for iTotalDisplayRecords --->
<CFQUERY DATASOURCE='#DataSource#' USERNAME='#DataUserName#' PASSWORD='#DataPassWord#' NAME='qFilter'>
	select count(#columnCount#) as [total]
	<cfinclude template="#filename#_clause.cfm">
	<cfif len(trim(url.sSearch))>
		<!--- filter --->
		AND (
			1 = 0
			<cfoutput>
				<cfloop list = "#columnOrder#" index = "column">
				<cfset temp = mycolumnstruct[column]>
				OR #PreserveSingleQuotes(temp)# LIKE <cfqueryparam value="%#ucase(trim(url.sSearch))#%">
				</cfloop>
			</cfoutput>
		)
	</cfif>	
</CFQUERY>

<!--- get count for iTotalRecords --->
<CFQUERY DATASOURCE='#DataSource#' USERNAME='#DataUserName#' PASSWORD='#DataPassWord#' NAME='qTotal'>
select count(#columnCount#) as [total]
<cfinclude template="#filename#_clause.cfm">		
</CFQUERY>

<!--- handles no records --->
<cfif qFiltered.recordcount>
	<cfset mytest = SerializeJSON(qFiltered)>
	<cfset findDataStart = FindNoCase('[[',mytest)>
	<cfset mydata = Mid(mytest,findDataStart,len(mytest)-findDataStart)>
<cfelse>
	<cfset mydata = "[]">
</cfif>

<!--- Output --->
<cfcontent reset="true" type="application/json" />
<cfprocessingdirective suppresswhitespace="Yes">
{"sEcho":
	<cfoutput>#val(url.sEcho)#</cfoutput>,
	"iTotalRecords":<cfoutput>#qTotal.total#</cfoutput>,
	"iTotalDisplayRecords":<cfoutput>#qFilter.total#</cfoutput>,
	"aaData":
		<cfoutput>#mydata#</cfoutput>
}
</cfprocessingdirective>