A ColdFusion component for using CFML functions within CF Script code blocks.

CfscriptFunctions.cfc

<!------------------------------------------------------------------------------
|| Component      :   CfscriptFunctions.cfc
|| Author         :   Jason Luttrell
|| Description    :   Provides a library of functions to be used by other CFCs.
|| Public Methods :   cfabort
||                    cfdump
||                    cfhtmlHead
||                    cfthrow
||                    cfqueryofquery
||                    etc...
------------------------------------------------------------------------------->
<cfcomponent output="no">

	<!--------------------------------------------------------------------------
	|| COLDFUSION FUNCTION WRAPPERS
	--------------------------------------------------------------------------->
	
	<!--- CFABORT --->
	<cffunction name="cfabort" access="public" output="no">
		<cfabort />
	</cffunction>
	
	<!--- CFCONTENT: change type (e.g., "application/msexcel") --->
	<cffunction name="cfcontent_changeType" access="public" output="no">
		<cfargument name="type" type="string" required="yes" />
		<cfif arrayLen(arguments) gt 1>
			<cfcontent type="#arguments.type#" file="#arguments[2]#" />
		<cfelse>
			<cfcontent type="#arguments.type#" />
		</cfif>
		
	</cffunction>
	
	<!--- CFDIRECTORY: list files --->
	<cffunction name="cfdirectory_listfiles" access="public" output="no">
		<cfargument name="directory" type="string" required="yes" />
		<cfdirectory action="list" 
			directory="#arguments.directory#" 
			name="listing" />
		<cfreturn listing />
	</cffunction>
	
	<!--- CFDUMP --->
	<cffunction name="cfdump" access="public" output="yes">
		<cfargument name="var" type="any" required="yes" />
		<cfdump var="#var#" />
	</cffunction>
	
	<!--- CFFILE: action="copy" --->
	<cffunction name="cffile_copy" access="public" output="no">
		<cfargument name="source"      type="string" required="yes" />
		<cfargument name="destination" type="string" required="yes" />
		<cffile 
		   action="copy"
		   source="#arguments.source#"
		   destination="#arguments.destination#" />
	</cffunction>
	
	<!--- CFFILE: action="delete" --->
	<cffunction name="cffile_delete" access="public" output="no">
		<cfargument name="pathToFile" type="string" required="yes" />
		<cffile 
		   action="delete"
		   file="#pathToFile#" />
	</cffunction>
	
	<!--- CFFILE: action="read" --->
	<cffunction name="cffile_read" 
		access="public" 
		output="no" 
		returntype="any">
		<cfargument name="source" type="string" required="yes" /> 
		<cffile
			action="READ"
			file="#source#"
			variable="strData" />
		<cfreturn strData />
	</cffunction>
	
	<!--- CFFILE: action="rename" --->
	<cffunction name="cffile_rename" access="public" output="no">
		<cfargument name="source"      type="string" required="yes" />
		<cfargument name="destination" type="string" required="yes" />
		<cffile 
		   action="rename"
		   source="#arguments.source#"
		   destination="#arguments.destination#" />
	</cffunction>
	
	<!--- CFFILE: action="upload" --->
	<cffunction name="cffile_upload" access="public" output="no">
		<cfargument name="fileField"   type="string" required="yes" />
		<cfargument name="dirToSaveTo" type="string" required="yes" />
		<cffile 
			action="upload" 
			fileField="#arguments.fileField#" 
			destination="#arguments.dirToSaveTo#" 
			nameConflict="overwrite"
			accept="text/comma-separated-values,
				text/csv, 
				application/csv, 
				application/excel, 
				application/vnd.ms-excel,
				application/vnd.msexcel, 
				text/plain,
				text/anytext" />
	</cffunction>
	
	<!--- CFFILE: action="write" --->
	<cffunction name="cffile_write" access="public" output="no">
		<cfargument name="targetFile" type="string" required="yes" />
		<cfargument name="contents"   type="string" required="yes" />
		<cffile
			action="write"
			file="#arguments.targetFile#"
			output="#arguments.contents#" />
	</cffunction>
	
	<!--- CFHEADER --->
	<cffunction name="cfheader" access="public" output="no">
		<cfargument name="name"  type="string" required="yes" />
		<cfargument name="value" type="string" required="yes" />
		<cfheader name="#arguments.name#" value="#arguments.value#" />
	</cffunction>
	
	<!--- CFHEADER: set attachment filename --->
	<cffunction name="cfheader_setFileName" access="public" output="no">
		<cfargument name="fileName" type="string" required="yes" />
		<cfheader name="Content-Disposition" 
			value="attachment; filename=#arguments.fileName#" />
	</cffunction>
	
	<!--- CFHTMLHEAD --->
	<cffunction name="cfhtmlHead" access="public" output="no">
		<cfargument name="text" type="string" required="yes" />
		<cfhtmlhead text="#arguments.text#" />
	</cffunction>
	
	<!--- CFPARAM --->
	<cffunction name="cfparam" access="public" output="no">
		<cfargument name="scope"   default="" />
		<cfargument name="varname" default="" />
		<cfscript>
		var value = "";
		if(arrayLen(arguments) gte 3)
			value = arguments[3];
		if(NOT structKeyExists(arguments.scope, arguments.varname)
			or 
				(
					not isQuery(arguments.scope[arguments.varname])
					and 
					not isArray(arguments.scope[arguments.varname])
					and 
					not isStruct(arguments.scope[arguments.varname])
					and
					not isNumeric(arguments.scope[arguments.varname])
					and 
					not isBoolean(arguments.scope[arguments.varname])
					and
					len(trim(arguments.scope[arguments.varname])) eq 0
				)
			) {
			arguments.scope[arguments.varname] = value;
		}
		return arguments.scope[arguments.varname];
		</cfscript>
	</cffunction>
	
	<!--- CFSETTING: Show debugging --->
	<cffunction name="cfsetting_showDebugging" access="public" output="no">
		<cfargument name="showDebugging" type="boolean" required="yes" />
		<cfsetting showdebugoutput="#arguments.showDebugging#" />
	</cffunction>
	
	<!--- CFTHROW --->
	<cffunction name="cfthrow" access="public">
		<cfargument name="strErrorMessage"  /> 
		<cfthrow message="#strErrorMessage#" />
	</cffunction>
	
	<!--- CFQUERY: Regular query --->
	<cffunction name="cfquery" 
		access="public" 
		output="no" 
		returntype="any">
		<cfargument name="datasource"    required="yes" />
		<cfargument name="strSQL"        required="yes" />
		<cfargument name="cacheTimeSpan" required="no" default=""  />
		 <cftry>
			<!---
			We must use preserveSingleQuotes() to fix a ColdFusion
			bug, in which variables used in CFQUERY automatically
			have single quotes escaped.
			--->
			<cfif len(arguments.cacheTimeSpan) gt 0>
				<cfquery name="qryResults" 
					datasource="#arguments.datasource#" 
					cachedWithin="#arguments.cacheTimeSpan#">
					#trim(preserveSingleQuotes(arguments.strSQL))#
				</cfquery>
			<cfelse>
				<cfquery name="qryResults" 
					datasource="#arguments.datasource#">
					#trim(preserveSingleQuotes(arguments.strSQL))#
				</cfquery>
			</cfif>
			<cfcatch type="any">
				<cfthrow message="
					Error Executing Database Query:
					#trim(preserveSingleQuotes(arguments.strSQL))#
				" />
			</cfcatch>
		</cftry>
		<cfif isDefined("qryResults")>
			<cfreturn qryResults />
		<cfelse>
			<cfreturn />
		</cfif>
	</cffunction>
	
	<!--- CFQUERY: Identity insert --->
	<cffunction name="cfquery_identityInsert" 
		access="public" 
		output="no" 
		returntype="any">
		<cfargument name="datasource" required="yes" />
		<cfargument name="tablename" required="yes" />
		<cfargument name="strSQL"     required="no" />
		<cftry>
			<!--- 
			We must use preserveSingleQuotes() to fix a ColdFusion
			bug, in which variables used in CFQUERY automatically
			have single quotes escaped.
			--->
			<cfquery name="qryResults" 
				datasource="#arguments.datasource#">
				SET IDENTITY_INSERT #arguments.tablename# ON
				#trim(preserveSingleQuotes(arguments.strSQL))#
				SET IDENTITY_INSERT #arguments.tablename# OFF
			</cfquery>
			<cfcatch type="any">
				<cfthrow message="
					Error Executing Database Query:
					SET IDENTITY_INSERT #arguments.tablename# ON
					#trim(preserveSingleQuotes(arguments.strSQL))#
					SET IDENTITY_INSERT #arguments.tablename# OFF
				" />
			</cfcatch>
		</cftry>
		<cfif isDefined("qryResults")>
			<cfreturn qryResults />
		<cfelse>
			<cfreturn />
		</cfif>
	</cffunction>
	
	<!--- CFQUERY: Query of query --->
	<cffunction name="cfqueryofquery" 
		access="public"
		returntype="any">
		<cfargument name="strSQL" required="yes" />
		<cfargument name="qryObject1" type="query" required="yes" />
		<cfargument name="qryObject2" type="query" required="no" />
		<cfargument name="qryObject3" type="query" required="no" />
		<cfargument name="qryObject4" type="query" required="no" />
		<cfset qryObject1 = arguments.qryObject1 />
		<cfset strSQL = 
			replace(arguments.strSQL, "{query}", "qryObject1", "ALL") />
		<cfset strSQL = 
			replace(arguments.strSQL, "{query1}", "qryObject1", "ALL") />
		<cfif isDefined("arguments.qryObject2") 
			and isQuery(arguments.qryObject2)>
			<cfset qryObject2 = arguments.qryObject2 />
			<cfset strSQL = replace(strSQL, 
									"{query2}", 
									"qryObject2", 
									"ALL") />
		</cfif>
		<cfif isDefined("arguments.qryObject3") 
			and isQuery(arguments.qryObject3)>
			<cfset qryObject3 = arguments.qryObject3 />
			<cfset strSQL = replace(strSQL, 
									"{query3}", 
									"qryObject3", 
									"ALL") />
		</cfif>
		<cfif isDefined("arguments.qryObject4") 
			and isQuery(arguments.qryObject4)>
			<cfset qryObject4 = arguments.qryObject4 />
			<cfset strSQL = replace(strSQL, 
									"{query4}", 
									"qryObject4", 
									"ALL") />
		</cfif>
		<cftry>
			<!--- 
			We must use preserveSingleQuotes() to fix a ColdFusion
			bug, in which variables used in CFQUERY automatically
			have single quotes escaped.
			--->
			<cfquery name="qryResults" dbtype="query">
			#trim(preserveSingleQuotes(arguments.strSQL))#
			</cfquery>
			<cfcatch type="Any">
				<!---
				Un-comment the following for debugging purposes:
				<cfdump var="#arguments#" />
				<cfdump var="#GetMetaData(arguments.qryObject1)#" />				
				<cfabort />
				--->
				<cfthrow message="
					Error Executing Database Query:
					#trim(preserveSingleQuotes(arguments.strSQL))#
				" />
			</cfcatch>
		</cftry>
		<cfif isDefined("qryResults")>
			<cfreturn qryResults />
		<cfelse>
			<cfreturn />
		</cfif>
	</cffunction>
	<cfscript>
	
	//================
	// SITE-WIDE UDFS
	//================
	
	//--------------------------------------------------------------------------
	// Function  :   arrayReplace()
	// Type      :   public
	// Arguments :   (1) arrayToSearch - The array to search. (Required)
	//               (2) valueToFind - The value to look for. (Required)
	//               (3) replaceValue - The value to replace with. (Required)
	//               (4) isExactMatch [OPTIONAL] - indicates whether or not the 
	//                   search should replace values if the value is found
	//                   within an array cell, or if the whole array cell must
	//                   be equal to the valueToFind. Default is FALSE.
	// Action    :   Searches an array and replaces all values
	// Returns   :   Number; the index of the array element that was found.
	// Author    :   Nathan Dintenfass ([email protected])
	//               Modified by Jason Luttrell
	// Version   :   1.0, September 6, 2002
	//--------------------------------------------------------------------------
	function arrayReplace(arrayToSearch, valueToFind, replaceValue)
	{
		// Define local scope
		var local = structNew();
		
		// Get the function's arguments
		local.arrayToSearch  = arguments.arrayToSearch;
		local.valueToFind    = trim(arguments.valueToFind);
		local.replaceValue   = trim(arguments.replaceValue);
		if (arrayLen(arguments) gte 4
			and isBoolean(arguments[4]))
			local.isExactMatch = arguments[4];
		else
			local.isExactMatch = false;
		
		// Loop through the array, looking for the value
		for(local.i = 1; 
			local.i lte arrayLen(local.arrayToSearch); 
			local.i = local.i + 1) {
			
			// If the array element is another sub-array...
			if (isArray(local.arrayToSearch[local.i])) {
				
				// Loop through the sub-array, looking for the value
				for(local.j =   1;
					local.j lte arrayLen(local.arrayToSearch[local.i]);
					local.j =   local.j + 1) {
					
					// Get value of array element
					local.arrElem = 
						trim(local.arrayToSearch[local.i][local.j]);
					
					// If this array cell is the value...
					if (
						(
						 local.isExactMatch
						 and
						 local.valueToFind eq local.arrElem
						)
					   ) {
						
						// Replace array cell
						local.arrayToSearch[local.i][local.j] =
							local.replaceValue;
					}
					// Otherwise, if the array cell contains the value...
					else if(
							(
							 not local.isExactMatch
							 and
							 findNoCase(local.valueToFind, local.arrElem)
							)
						   ) {
						
						// Replace value within the cell
						local.arrayToSearch[local.i][local.j] =
							replaceNoCase(local.arrayToSearch[local.i][local.j],
										  local.valueToFind,
										  local.replaceValue);
					}
				}
			}
			// Otherwise, if the array element is not a sub-array...
			else {
			
				// Get value of array element
				local.arrElem = trim(local.arrayToSearch[local.i]);
				
				// If this array cell is the value...
				if (
					(
					 local.isExactMatch
					 and
					 local.valueToFind eq local.arrElem
					)
				   ) {
					
					// Replace array cell
					local.arrayToSearch[local.i] =
						local.replaceValue;
				}
				// Otherwise, if the array cell contains the value...
				else if(
						(
						 not local.isExactMatch
						 and
						 findNoCase(local.valueToFind, local.arrElem)
						)
					   ) {
					
					// Replace value within the cell
					local.arrayToSearch[local.i] =
						replaceNoCase(local.arrayToSearch[local.i],
									  local.valueToFind,
									  local.replaceValue);
				}
			}
		}
		
		// Return the updated array
		return local.arrayToSearch;
	}
	
	//--------------------------------------------------------------------------
	// Function  :   arraySearchNoCase()
	// Type      :   public
	// Arguments :   (1) arrayToSearch - The array to search. (Required)
	//               (2) valueToFind - The value to look for. (Required)
	//               (3) isExactMatch [OPTIONAL] - indicates whether or not the 
	//                   search should only return exact matches. The default 
	//                   is FALSE.
	// Action    :   Searches an array (like listFindNoCase(), but for arrays).
	// Returns   :   Number; the index of the array element that was found.
	// Author    :   Nathan Dintenfass ([email protected])
	//               Modified by Jason Luttrell
	// Version   :   1.0, September 6, 2002
	//--------------------------------------------------------------------------
	function arraySearchNoCase(arrayToSearch, valueToFind)
	{
		// Define local scope
		var local = structNew();
		
		// Get the function's arguments
		local.arrayToSearch = arguments.arrayToSearch;
		local.valueToFind   = trim(arguments.valueToFind);
		if (arrayLen(arguments) gte 3
			and isBoolean(arguments[3]))
			local.isExactMatch = arguments[3];
		else
			local.isExactMatch = false;
		
		// Loop through the array, looking for the value
		for(local.i = 1; 
			local.i lte arrayLen(local.arrayToSearch); 
			local.i = local.i + 1) {
			
			// Get value of array element
			local.arrElem = trim(local.arrayToSearch[local.i]);
			
			// If this is the value, return the index
			if (
				findNoCase(local.valueToFind, local.arrElem)
			    and
				(
			     (
				  local.isExactMatch 
				  and 
				  local.valueToFind eq local.arrElem
				 )
				 or
				 (
				  not local.isExactMatch
				 )
				)
			   )
				return local.i;
		}
		
		// If we got this far, it means the value was not found, so return 0
		return 0;
	}
	
	//--------------------------------------------------------------------------
	// Function  :   cfqueryOfQuery_caseInsensitive()
	// Type      :   public
	// Arguments :   (1) qryObject - the query object to perform a query on.
	//               (2) listColNames - string; list of column names to select.
	//               (3) listColDataTypes - string; a list of the column data 
	//                   types. ColdFusion has an error in which data types must
	//                   be explicitly stated in order for the correct ordering 
	//                   to occur (i.e., for it to differentiate between
	//                   numbers, text, and dates).
	//               (4) orderByColumnList - a list of the columns to sort on.
	//               (5) orderBySortOrderList - a list of column sort ordering.
	//               (6) whereClause - [OPTIONAL] string; the SQL WHERE clause.
	// Actions   :   Performs a case-insensitive, data type aware, ColdFusion 
	//               Query-of-Query Select on a passed-in query object (that 
	//               also does not choke on NULL values), to overcome ColdFusion
	//               bugs. This function does not perform joins, as it is 
	//               primarily for sorting returned data sets.
	// Returns   :   Query object; the re-sorted data results.
	//--------------------------------------------------------------------------
	function cfqueryOfQuery_caseInsensitive(
		qryObject,
		listColNames,
		listColDataTypes,
		orderByColumnList,
		orderBySortOrderList
	)
	{
		// Define the local scope
		var local = structNew();
		
		// Get the function parameters
		local.qryObject            = arguments.qryObject;
		local.listColNames         = arguments.listColNames;
		local.listColDataTypes     = arguments.listColDataTypes;
		local.orderByColumnList    = arguments.orderByColumnList;
		local.orderBySortOrderList = arguments.orderBySortOrderList;
		if (arrayLen(arguments) gte 6) {
			local.whereClause      = arguments[6];
		}
		else {
			local.whereClause      = "";	
		}
		
		//====================================================================//
		// CHECK PARAMETERS                                                   //
		//====================================================================//
		
		// qryObject
		if (not isQuery(local.qryObject)) {
			cfthrow("
				The cfqueryOfQuery_caseInsensitive() function requires a valid
				query object.
			");
		}
		
		// listColNames
		if (isArray(local.listColNames) 
			or 
			isStruct(local.listColNames)) {
			cfthrow("
				The cfqueryOfQuery_caseInsensitive() function is expecting a 
				string for the list of column names.
			");
		}
		
		// listColDataTypes
		if (isArray(local.listColDataTypes) 
			or 
			isStruct(local.listColDataTypes)) {
			cfthrow("
				The cfqueryOfQuery_caseInsensitive() function is expecting a 
				string for the list of column data types.
			");
		}
		
		// Compare list length of column names and data types to make sure that 
		// they are the same.
		if (listLen(local.listColNames) neq 
				listLen(local.listColDataTypes)) {
			cfthrow("
				The cfqueryOfQuery_caseInsensitive() function is expecting a 
				list of the the column data types with the same number of items
				as the list of column names.
			");
		}
		
		// Loop through column lists
		for (
			 i =   1;
			 i lte listLen(local.listColNames);
			 i =   i+1
			) {
			
			// Trim white spaces from list items...
			
			// Column names
			local.colName = trim(listGetAt(local.listColNames, i));
			local.listColNames =
				listSetAt(local.listColNames, i, local.colName);
			
			// Column data types
			local.colDataType = trim(listGetAt(local.listColDataTypes, i));
			local.listColDataTypes =
				listSetAt(local.listColDataTypes, i, local.colDataType);
			
			// Check for white space in column name
			if (find(local.colName, " ")) {
				cfthrow('
					The cfqueryOfQuery_caseInsensitive() function requires that 
					column names do not have spaces in them.
				');
			}
			
			// Check for valid column data types
			if (
				not findNoCase(local.colDataType, "binary")
				and
				not findNoCase(local.colDataType, "bigint")
				and
				not findNoCase(local.colDataType, "bit")
				and
				not findNoCase(local.colDataType, "date")
				and
				not findNoCase(local.colDataType, "decimal")
				and
				not findNoCase(local.colDataType, "double")
				and
				not findNoCase(local.colDataType, "integer")
				and
				not findNoCase(local.colDataType, "time")
				and
				not findNoCase(local.colDataType, "timestamp")
				and
				not findNoCase(local.colDataType, "varchar")
			   ) {
				cfthrow('
					The cfqueryOfQuery_caseInsensitive() function is expecting 
					a list of column data types, which may include: binary, 
					bigint, bit, date, decimal, double, integer, time, 
					timestamp, and varchar. A data type of "#local.colDataType#" 
					was specified for a column, which is not valid.
				');
				// To reference other possible data types, go to:
				// http://livedocs.adobe.com/coldfusion/7/htmldocs/wwhelp/
				// wwhimpl/common/html/wwhelp.htm?context=
				// ColdFusion_Documentation&file=00001271.htm
			}
		}
		
		//====================================================================//
		// BUILD SQL "SELECT" CLAUSE                                          //
		//====================================================================//
		local.selectString = "";
		
		// Loop through columns in SELECT clause
		for (
			 local.i =   1;
			 local.i lte listLen(local.listColNames);
			 local.i =   local.i + 1
			) {
			
			// Get the name of the column in loop
			local.colName = listGetAt(local.listColNames, local.i);
			
			// Get the column data type
			local.colDataType = listGetAt(local.listColDataTypes, local.i);
			
			// If this is a column in the "Order By" clause...
			if (listFindNoCase(local.orderByColumnList, local.colName)) {
			
				// If it is anything but a varchar column...
				if (
					findNoCase(local.colDataType, "binary")
					or
					findNoCase(local.colDataType, "bigint")
					or
					findNoCase(local.colDataType, "bit")
					or
					findNoCase(local.colDataType, "date")
					or
					findNoCase(local.colDataType, "decimal")
					or
					findNoCase(local.colDataType, "double")
					or
					findNoCase(local.colDataType, "integer")
					or
					findNoCase(local.colDataType, "time")
					or
					findNoCase(local.colDataType, "timestamp")
				   ) {
					
					// Create an additional column, with correct data type
					local.selectString = local.selectString & "
						#local.colName#
						,
						CAST(
							 {query}.#local.colName# 
							 AS #ucase(local.colDataType)#
							) AS #local.colName#_#local.colDataType#
					";
				}
				// Otherwise, if it is a text column...
				else {
					
					// Create an additional column, with all capital letters
					local.selectString = local.selectString & "
						#local.colName#
						,
						UPPER({query}.#local.colName#) 
							AS #local.colName#_upperCase
					";
				}
			}
			// Otherwise, if this column is not in the "Order By" clause...
			else {
				
				// Just include the column
				local.selectString = local.selectString & "
					#local.colName#
				";
			}
			
			// If this is not the last column in the list...
			if (local.i lt listLen(local.listColNames)) {
			
				// Append a comma to column list
				local.selectString = local.selectString & "
					,
				";
			}
				
		// Get next column name / end loop
		}
		
		//====================================================================//
		// BUILD SQL "ORDER BY" CLAUSE                                        //
		//====================================================================//
		local.orderByString = "";
		
		// Loop through columns in ORDER BY clause
		for (
			 local.j =   1;
			 local.j lte listLen(local.orderByColumnList);
			 local.j =   local.j + 1
			) {
		
			// Get the name of the column in loop
			local.colName = listGetAt(local.orderByColumnList, local.j);
			
			//  Get the associated sort order
			local.sortOrder = listGetAt(local.orderBySortOrderList, local.j);
			
			// Get the column data type
			local.colListIndex = listFind(local.listColNames, local.colName);
			local.colDataType = listGetAt(local.listColDataTypes, 
										  local.colListIndex);
			
			// If it is anything but a varchar column...
			if (
				findNoCase(local.colDataType, "binary")
				or
				findNoCase(local.colDataType, "bigint")
				or
				findNoCase(local.colDataType, "bit")
				or
				findNoCase(local.colDataType, "date")
				or
				findNoCase(local.colDataType, "decimal")
				or
				findNoCase(local.colDataType, "double")
				or
				findNoCase(local.colDataType, "integer")
				or
				findNoCase(local.colDataType, "time")
				or
				findNoCase(local.colDataType, "timestamp")
			   ) {
				
				// Reference the added column, with correct data type
				local.orderByString = local.orderByString & "
					#local.colName#_#local.colDataType# #ucase(local.sortOrder)#
				";
				
				// If this is a date column...
				if (
					findNoCase(local.colDataType, "date")
					or
					findNoCase(local.colDataType, "timestamp")
					or
					findNoCase(local.colDataType, "time")
				    ) {
					
					// Reformat query object to replace NULL date values with 0.
					// (This addresses a ColdFusion defect with casting null 
					// values to date data types.)
					local.qryObject = 
						replaceNulls(local.qryObject, local.colName, 0);
				}
			}
			// Otherwise, if it is a text column...
			else {
				
				// Reference the added column, with all capital letters
				local.orderByString = local.orderByString & "
					#local.colName#_upperCase #ucase(local.sortOrder)#
				";
			}
			
			// If this is not the last column in the list...
			if (local.i lt listLen(local.listColNames)) {
			
				// Append a comma to column list
				local.orderByString = local.orderByString & "
					,
				";
			}
		
		// Get next column name / end loop
		}
		
		//====================================================================//
		// COMPILE SQL STATEMENT                                              //
		//====================================================================//
		
		// Build SQL statement
		local.sqlStatement = "
			SELECT
		";
		if (len(local.selectString) gt 0) {
			local.sqlStatement = local.sqlStatement & "
					#local.selectString#
			";
		}
		else {
			local.sqlStatement = local.sqlStatement & "
					*
			";
		}
		local.sqlStatement = local.sqlStatement & "
			FROM	 {query}
		";
		if (len(local.whereClause) gt 0) {
			local.sqlStatement = local.sqlStatement & "
				WHERE    #local.whereClause#
			";
		}
		if (len(local.orderByString) gt 0) {
			local.sqlStatement = local.sqlStatement & "
				ORDER BY #local.orderByString#
			";
		}
		
		// Execute SQL statement
		local.resortedQuery = cfqueryofquery(
			local.sqlStatement,
			local.qryObject
		);
		
		// Return sorted results
		if (isDefined("local.resortedQuery")) 
			return local.resortedQuery;
		else 
			return;
	}
	
	//--------------------------------------------------------------------------
	// Function  :   formatPhoneNo()
	// Type      :   public
	// Arguments :   phoneNo - The string to re-format. (Required)
	// Action    :   Re-formats the phone number with hyphens, etc.
	// Returns   :   String; the re-formatted phone number
	// Author    :   Jason Luttrell
	//--------------------------------------------------------------------------
	function formatPhoneNo(phoneNo)
	{
		// Define local scope
		var local = structNew();
		
		// Get the function's arguments
		local.phoneNo = trim(arguments.phoneNo);
		
		// Set internal parameters
		local.phoneLen    = len(local.phoneNo);
		local.phoneSuffix = "";	
		
		// Check to make sure that the phone number is not already formatted
		if (not find("-", local.phoneNo)) {
		
			// Check to see if there are any letters or # symbols
			local.charIdx  = reFind("[A-Za-z##]",local.phoneNo);
			if (local.charIdx gt 0) {
				
				// Separate phone number from string if there are any characters
				local.suffixLen   = local.phoneLen - local.charIdx + 1;
				local.phoneLen    = local.charIdx  - 1;
				if (local.suffixLen gt 0)
					local.phoneSuffix = right(local.phoneNo, local.suffixLen);
				else
					local.phoneSuffix = "";
				if (local.phoneLen gt 0)
					local.phoneNo     = left( local.phoneNo, local.phoneLen);
				else
					local.phoneNo     = "";
				
				// Insert space before phone suffix and remove extra spaces
				local.phoneSuffix = " " & rtrim(local.phoneSuffix);
				local.phoneNo     = trim(local.phoneNo);
				local.phoneLen    = len(local.phoneNo);
			}
			
			// Only reformat the number part if it consists of digits only
			if (isNumeric(local.phoneNo)) {
			
				// 7 digits (e.g., 123-4567)
				if (local.phoneLen eq 7) {
					local.phoneNo = 
						left( local.phoneNo, 3) & "-" & 
						right(local.phoneNo, 4)
					;
				}
				// 10 digits (e.g., 123-145-1563)
				else if (local.phoneLen eq 10) {
					local.phoneNo = 
						left( local.phoneNo, 3   ) & "-" & 
						mid(  local.phoneNo, 4, 3) & "-" &
						right(local.phoneNo, 4   )
					;
				}
				// 11 digits (e.g, 1-800-123-1456)
				else if (local.phoneLen eq 11) {
					local.phoneNo = 
						left (local.phoneNo, 1   ) & "-" & 
						mid(  local.phoneNo, 2, 3) & "-" & 
						mid(  local.phoneNo, 5, 3) & "-" & 
						right(local.phoneNo, 4   )
					;
				}
			}
			
			// Return the reformatted phone number
			return trim(local.phoneNo & local.phoneSuffix);
		}
		// Otherwise, if it is already formatted...
		else {
			
			// Return the original phone number
			return local.phoneNo;
		}
	}
	
	//--------------------------------------------------------------------------
	// Function  :   replaceNulls()
	// Type      :   public
	// Arguments :   queryObj - the query object to replace NULLs in.
	//               colName (optional)
	//               stringReplacement (optional)
	// Actions   :   Replaces NULLs with string equivalents
	// Returns   :   The reformatted query object.
	//--------------------------------------------------------------------------
	function replaceNulls(queryObj)
	{
		// Define the local scope
		var local = structNew();
		
		// Get the function parameters
		local.queryObj = arguments.queryObj;
		if (arrayLen(arguments) gte 2)
			local.colName = arguments[2];
		else
			local.colName = "";
		if (arrayLen(arguments) gte 3)
			local.stringReplacement = arguments[3];
		else
			local.stringReplacement = "";
		
		// Get information about the query
		local.arrColNames = local.queryObj.getColumnNames();
		local.colNameList = arrayToList(local.arrColNames);
		local.colCount    = listLen(local.colNameList);
		
		// Create a list of column data types
		local.colDataTypes = "";
		for (i = 1; i lte local.colCount; i = i+1) {
			local.colDataTypes = listAppend(local.colDataTypes, "varchar");
		}
		
		// Create a new, temporary query object
		local.tmpQuery = queryNew(local.colNameList, local.colDataTypes);
		
		// Loop through query row
		local.maxRows = local.queryObj.recordCount;
		for (row = 1; row lte local.maxRows; row = row + 1) {
			
			// Add the row to the temporary query object
			queryAddRow(local.tmpQuery,1);
			
			// Loop through the query's columns
			for (col = 1; col lte local.colCount; col = col + 1) {
				
				// Get the name of the column currently searching on
				local.searchCol = listGetAt(local.colNameList, col);
			
				// Get the contents of the cell
				strDataCell = trim(
					local.queryObj[local.searchCol][row]
				);
				
				// If this is the column that we intend to reformat...
				if (len(local.colName) eq 0
					or 
					local.searchCol eq local.colName) {
				
					// BEGIN PROCESSING THE DATA CELL
					
					// Replace NULL with string equivalent
					if (len(trim(strDataCell)) eq 0) {
						strDataCell = local.stringReplacement;
					}
					
					// END PROCESSING THE DATA CELL
				}
			
				// Set the original cell's contents (must use JavaCast)
				local.tmpQuery[local.searchCol][row]
					= JavaCast("string", strDataCell);
			}
		}
		
		// Set the reformatted query to the global query
		return local.tmpQuery;
	}
	
	//--------------------------------------------------------------------------
	// Function  :   xhtmlFormat()
	// Type      :   public
	// Arguments :   urlString - The URL string to re-format. (Required)
	// Action    :   Re-formats a URL string for XHTML-compliance (replaces
	//               "&" with "&amp;").
	// Returns   :   String; the re-formatted URL string
	// Author    :   Jason Luttrell
	//--------------------------------------------------------------------------
	function xhtmlFormat(urlString)
	{
		// Define local scope
		var local = structNew();
		local.urlString = arguments.urlString;
		
		// Loop through characters in string
		for (local.i=1; 
			 local.i lte len(local.urlString); 
			 local.i=local.i+1) {
			
			// Look for "&" in string, after current index
			local.indexOfAmpersand = 0;
			local.indexOfAmpersand = find("&",local.urlString,local.i);
			if (local.indexOfAmpersand gt 0) {
				
				// Check to see if it is not the start of "&amp;"
				local.snippet = mid(local.urlString,local.indexOfAmpersand,5);
				if (local.snippet neq "&amp;") {
					
					// Insert "&amp;" into string
					local.countBeforeAmpersand = 
						local.indexOfAmpersand-1;
					local.countAfterAmpersand  = 
						len(local.urlString)-local.indexOfAmpersand;
					if (local.indexOfAmpersand gt 1)
						local.stringBeforeAmerpsand = 
							left(local.urlString,local.countBeforeAmpersand);
					else 
						local.stringBeforeAmerpsand = "";
					local.stringAfterAmpersand = 
						right(local.urlString,local.countAfterAmpersand);
					local.urlString = 
						local.stringBeforeAmerpsand & "&amp;" & 
						local.stringAfterAmpersand;
				}
			}
		}
		
		// Return reformatted string
		return local.urlString;
	}
	</cfscript>
</cfcomponent>