TryCF Gist

trycf-gist-1644893042376-42ecc15c-8138-7f65-357e-b6381ac54c2f.cfm

<cffunction name="GetData" access="remote" returnformat="json" returntype="any" output="false">
		<cfargument name="start" type="numeric" default="1">
  		<cfargument name="length" type="numeric" default="200">
		<cfparam name="draw" default="1" type="integer" />
		<cfparam name="search" default="" type="string" />
		<cfset var aData = arrayNew(1) />
		<cfset var sGridData = StructNew() />
		<cfif len(inparam["search[value]"]) gt 0>
		    <cfset search=inparam["search[value]"]>
		</cfif>
		<cfset var rsQuery = ''>
		<cfset rsQuery = orders(inparam,search,start,length)>
		<cfset rQuery = rsQuery.query>
		<cfset rCount = rsQuery.count>
		<cfset var totalRecords = val(rCount.totalcount)>
		<cfset aData = getJSONData('query',rQuery,aData,sGridData)>
		<cfset sGridReturn["draw"] = val(draw) />
		<cfset sGridReturn["recordsTotal"] = totalRecords />
		<cfset sGridReturn["recordsFiltered"] = totalRecords />
		<cfset sGridReturn["data"] = aData />
		<cfreturn sGridReturn />
	</cffunction>

<cffunction name="orders" returntype="any">
		<cfargument name="inparam" required="false" default="" type="any">
		<cfargument name="search" required="false" default="">
		<cfargument name="start" type="numeric" default="1">
  		<cfargument name="length" type="numeric" default="200">

        <cfset var offset = (start-1) * length>
		<cfset var rsQuery = ''>
		<cfsavecontent variable="MainQuery">
			SELECT * 
			FROM
			  bills
			WHERE 
			  billtype = 'Bill'
		</cfsavecontent>	
		
		<cfquery name="rsQuery" datasource="#Application.dsn#">
			#preserveSingleQuotes(MainQuery)#
			<cfif len(trim(search))>
			AND
			    ( 
			    <cfloop list="#listColumns#" index="thisColumn">
			    <cfif thisColumn neq listFirst(listColumns)> 
			    OR 
			    </cfif>
			    	#thisColumn# LIKE <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="%#trim(search)#%" />    
			    </cfloop>
			    )
			</cfif>
			
				ORDER BY BillID desc
			
			LIMIT #length# OFFSET #offset#
		</cfquery>
	
		<cfreturn rsQuery>
	</cffunction>