<!---
SQLB
SQL Browser
Version 1.0a
Intellectual property, Aaron Collegeman.  Used with permission.
*** Modifications (c)2003 Shentel Service Company.  All rights reserved.

Adds dynamic SQL browsing/filtering to any CF app.
Also provides quick output to Microsoft Excel and Microsoft Word HTML formats.

	
SYNTAX:
<CF_SQLB

Required Attributes
-------------------------------------------------

Because SQLB is query driven, you must provide values for the attributes normally required
by a CFQUERY tag.  You must also provide a value for the NAME attribute, which will be used
to create a variable in the Caller scope to which the CFQUERY element will be assigned.
	

Functionality Attributes
-------------------------------------------------

NAME
	A name to assign the internal CFQUERY variable with the Caller scope.
	SO, if you do not specify an external CFQUERY element via the QUERY attribute,
	a varible "Caller.NAME" will be created via an internal CFQUERY tag.
	The SQL used by the internal CFQUERY tag is derived from the body of the
	SQLB tag, i.e., <CF_SQLB>[sql statement here]</CF_SQLB>
	
	For the following attributes, please refer to the CFML Reference, Version 5:
	DATASOURCE, USERNAME, PASSWORD, DBTYPE, DBSERVER, DBNAME, CONNECTIONSTRING,
	MAXROWS, BLOCKFACTOR, TIMEOUT, CACHEDAFTER, CACHEDWITHIN, PROVIDER, PROVIDERDSN

SHOWCOLUMNS
	A comma-delimited list of fields to use when displaying data to the user.
	By default, all columns from the CFQUERY results are used (i.e., CFQUERY.columnList).
	SHOWCOLUMNS will also impose the order (left to right) by which the fields are listed,
	which by default is an alphabetical listing of the columns.  Columns specified in
	FILTERCOLUMNS that are not included in SHOWCOLUMNS are appended to the end of
	this list.  (See FILTERCOLUMNS for more information.)

FILTERCOLUMNS
	A comma-delimited list of fields that become "filterable" by the user. 
	By default, all columns from the CFQUERY results are used (i.e., CFQUERY.columnList).
	If SHOWCOLUMNS is specified, and FILTERCOLUMNS is not, then all fields specified by
	SHOWCOLUMNS will be "filterable."  If both SHOWCOLUMNS and FILTERCOLUMNS are specified,
	an exclusion list will be created, and fields not listed in SHOWCOLUMNS that are listed
	in FILTERCOLUMNS will be added to SHOWCOLUMNS and will be "filterable."
	
FILTERDATATYPES
	A comma-delimited list of data types that correlates directly with the list
	specified in FILTERCOLUMNS.  This list defines the datatypes for each of the fields
	listed in FILTERDATATYPES, and there must be one data type specification for each 
	item in FILTERCOLUMNS (the lists must be the same length).
	
	Valid data types are as follows:
		Number - correponds to SQL Server Data types INT, SMALLINT, TINYINT, BIT, DECIMAL,
			FLOAT, REAL, MONEY, and SMALLMONEY
		Text - corresponds to SQL Server Data types CHAR, VARCHAR, NCHAR, NVARCHAR, NTEXT,
			BINARY, VARBINARY, TEXT, IMAGE
		DateTime - corresponds to SQL Server Data types DATETIME and SMALLDATETIME
	
SORTBY
	A column name that will act as the primary sorting mechanism by default.  The reason
	the column specified here is labled "primary" is because there will always exist
	a secondary sorting mechanism which is the order in which the records are returned
	by the CFQUERY element.  

	For instance, the SQL statement "SELECT * FROM table ORDER BY DATEENTERED" will 
	presumably return a result set that is organized by DATEENTERED.  If you then specify
	the primary sorting (via SORTBY) as being the NAME field, if two records happen to
	have the same value in the Name field they are then sorted by DATEENTERED, hence
	"secondary sorting mechanism."

SORTORDER
	Either "DESC" for descending order or "ASC" for ascending order.  The value of this
	attribute is ignored if no value is submitted for SORTBY.

LOCKSORTING
	By default, the data interface permits the user to configuring sorting and filtering 
	on the fly.  While filtering is specifically managed by the FILTERCOLUMNS (i.e.,
	if a column is not specified in FILTERCOLUMNS, the user will not be able to filter it),
	dynamic sorting is either _on_ or _off_.  If dynamic sorting is not permitted,
	only the sorting specified by SORTBY will be acknowledged.
	
ENABLEEDIT
	If ENABLEEDIT is set to TRUE, an extra column will be prepended to each data output
	row that will contain a button labeled "edit."  This button will submit a form
	whose parameters must be specified by the attributes FORMACTION, FORMMETHOD, 
	ADDFORMFIELDS, ADDFORMVALUES.  All columns in the data output will be
	submitted to the form CGI.
	
EDITACTION
	A qualified URL to the CGI used for editing records displayed by SQLB.
	
EDITMETHOD
	Either "Post" or "Get."  Post mode is recommended.
	
ADDEDITFIELDS	
	A list of additional form fields to include in each edit form.
	Use caution not to employ a field name that will already be assigned because
	it was included in the SQL query.  This list must correlate directly
	with the values provided in ADDFORMVALUES.
	
ADDEDITVALUES
	A comma-delimited list of values that correlate directly to the field list in
	ADDFORMFIELDS.  To include commas in the field list, escape the commas with
	a backslash, i.e., "\,"  -- for all other escaping, use standard CF escaping
	mechanisms.
	
ENABLEDELETE
	Similar to ENABLEEDIT, this field provides the user with the ability to delete
	records directly from the output.  The way deleting is implemented is controlled
	by the parameter DELETESYSTEM.  See DELETESYSTEM for more information.
	
DELETESYSTEM
	There are two types of delete systems: "BATCH" (batch deleting) and "SINGLE"
	(single-line deleting).  In BATCH mode, each record row is prepended by a check
	box that allows the user to indicate that this record has been earmarked for deletion.
	In SINGLE mode, each record row is prepended by a button labeled "delete."  Both
	modes submit to the CGI specified by DELETEACTION.  
	
	In batch mode, a field named "deleteKeyValues" is dynamically populated with
	a comma delimited list of all the selected records' keys (as specified by the DELETEKEY 
	attribute).  
	
	** Batch mode requires that the user have JavaScript 1.1 enabled on his/her console.
	
DELETEKEY
	For "BATCH" delete mode, the key used to tell the application in DELETEACTION which 
	records to remove from the system.
	
DELETEACTION
	A qualified URL to the CGI used for deleting records displayed by SQLB.
	Make sure that the CGI is engineered to handle whichever delete system is being
	used to invoke the delete action.
	
DELETEMETHOD
	Either "Post" or "Get."  Post mode is recommended.
	
ADDDELETEFIELDS
	Please refer to ADDEDITFIELDS
	
ADDDELETEVALUES
	Please refer to ADDEDITVALUES
	
WHERE
	A where clause for your SQL query
	
Style Attributes
-------------------------------------------------

CSSLINK
	A qualified HREF that points to an external CSS file that in turn defines the 
	look and feel for SQLB.  (Please refer to About CSS Styling for more information
	regarding CSS class usage.)  Please note that in Internet Explorer, pages will
	load even if the external CSS file does not exist.  In Netscape, however, a 404
	error will be presented to the user if the CSS file does not exist.

>	
	{ SQL query }

</CF_SQLB>	

About CSS Styling
-------------------------------------------------
SQLB calls upon the following CSS classes for dynamic L&F:

	.sqlb_filterLink - all filter links within the component
	.sqlb_toolLink - all tool links within the component
	.sqlb_button - all form buttons within the component
	.sqlb_columnHead - the column headings
	.sqlb_textInput - all text input elements within the component
	.sqlb_text - all text within the component
	
These classes may be defined in an external CSS file, which in turn should be referenced
by the CSSLINK attribute.  (Please refer to Style Attributes for more information.)
	
--->

<!---=====| APPLICATION |===-------------------->

<CFIF IsDefined("URL.buildField") AND IsDefined("URL.buildDataType")>
	<CFIF thisTag.executionMode EQ "start">	
		<CFINCLUDE template="sqlb_expressionBuilder.cfm"/>
	</CFIF>
	<CFEXIT/>
</CFIF>

<!--- primary application process is handled at the end tag --->	
<CFIF thisTag.executionMode EQ "end">

	<!--- wipe out thisTag.generatedContent; generatedContent is reassigned by the end tag --->
	<CFIF IsDefined("Caller.SQL")>
		<CFSET sql=Caller.sql/>
	<CFELSE>
		<CFSET sql=trim(thisTag.generatedContent)/>
	</CFIF>
	<CFSET thisTag.generatedContent=''/>
	<CFIF sql EQ "" OR NOT ReFindNoCase("SELECT", sql)>

		[ <strong>SQLB Error:</strong> SQL query must be a SELECT query. ]
		<CFEXIT/>
	</CFIF>
	
	<CFSET colspan=ArrayLen(ListToArray(a.showColumns))/>
	<CFIF a.enableEdit AND a.editAction NEQ "">
		<CFSET colspan=colspan+1/>
	</CFIF>
	<CFIF a.enableDelete AND a.deleteAction NEQ "">
		<CFSET colspan=colspan+1/>
	</CFIF>
	
	<!--- create where clause from the query --->
	<CFSET userWhereClause=ArrayNew(1)/>
	<CFLOOP list="#a.filterColumns#" index="item">
		<CFIF IsDefined("URL.filter_#item#")>
			<CFSCRIPT>
				if (evaluate("URL.filter_#item#") NEQ "") {
					ArrayAppend(userWhereClause, evaluate("URL.filter_#item#"));
				}
			</CFSCRIPT>
		</CFIF>
	</CFLOOP>
	
	<CFSET filteringOn=FALSE/>
				
	
	<!--- try to do the query submitted --->
	<CFTRY>
		<CFSAVECONTENT variable="mySQL">
			<CFOUTPUT>
				#sql#
				<CFIF ArrayLen(userWhereClause) OR a.where NEQ "">
					WHERE
				</CFIF>
				<CFIF ArrayLen(userWhereClause)>
					<CFSET filteringOn=TRUE/>
					(#ArrayToList(userWhereClause, " AND ")#)
					<CFIF a.where NEQ "">
						AND
					</CFIF>
				</CFIF>
					#a.where#				 
				<CFIF a.sortBy NEQ "">
					ORDER BY #a.sortBy# #a.sortOrder#
				</CFIF>
			</CFOUTPUT>
		</CFSAVECONTENT>
		<CFSET mySQL=ReplaceList(mysql, "%22,%27", """,'")/>
		<CFQUERY name="Caller.#a.Name#" dataSource="#a.Datasource#" username="#a.username#"  password="#a.password#">
			#preserveSingleQuotes(mySQL)#			
		</CFQUERY>
		<CFCATCH>
			<CFOUTPUT>
				<CFSET singleLineSQL=REReplaceNoCase(mySQL, "[#Chr(10)##Chr(13)##Chr(9)#]", " ", "ALL")/>
				<CFSET singleLineSQL=REReplaceNoCase(singleLineSQL, "  ", " ", "ALL")/>
				[ <strong>SQLB Error:</strong> Improperly formatted SQL statement; <em>#singleLineSQL#</em> : #cfcatch.detail# ]
			</CFOUTPUT>
			<CFEXIT/>
		</CFCATCH>
	</CFTRY>
			
	<CFSAVECONTENT variable="secondaryContent">
		<CFOUTPUT>
			
			
	
			<CFSET recordCount=evaluate("Caller." & a.Name & ".recordCount")/>
			
			<!--- load column list into a.showColumns if a.showColumns is empty --->
			<CFIF trim(a.showColumns) EQ "">
				<CFSET a.showColumns=evaluate("Caller." & a.Name & ".columnList")/>
			</CFIF>
			
			<!--- append a.filterColumns to a.showColumns if they are not already there --->
			<CFLOOP list="#a.filterColumns#" index="index">
				<CFIF NOT ListFind(a.showColumns, index)>
					<CFSCRIPT>
						a.showColumns = ListAppend(a.showColumns, index);
					</CFSCRIPT>
				</CFIF>
			</CFLOOP>
			
			
			<!--- generate record HTML content --->
			<CFSAVECONTENT variable="recordHTML">
				<CFSET recordOutCount=0/>
				<CFSET recordsPassedOver=0/>
				<CFSET recordIndex=u.onIdx/>
				<CFSET rowIndex=u.onRow/>
				<CFSET totalRecords=evaluate("Caller.#a.Name#.recordCount")/>
				<CFSET endRow=rowIndex+u.RPP-1/>
				<CFIF endRow GT totalRecords>
					<CFSET endRow=totalRecords/>
				</CFIF>
				
				<CFLOOP query="Caller.#a.Name#" startRow="#rowIndex#" endRow="#endRow#">
					
					<tr <CFIF recordOutCount MOD 2 EQ 0>bgcolor="##eeeeee"</CFIF>>
						
						<CFIF a.enableEdit AND a.editAction NEQ "">
							<CFIF a.editMethod EQ "get">
								<CFSET functionOptions=ArrayNew(1)/>
								<CFLOOP list="#evaluate("Caller." & a.Name & ".columnList")#" index="index">
									<CFSCRIPT>
										//thisVal = ReReplaceNoCase(evaluate("Caller.#a.Name#.#index#[Caller.#a.Name#.currentRow]"), "'", "%27", "ALL");
										//thisVal = ReReplaceNoCase(thisVal, """", "%22", "ALL");
										//thisVal = ReReplaceNoCase(thisVal, "##", "%23", "ALL");
										thisVal=URLEncodedFormat(evaluate("Caller.#a.Name#.#index#[Caller.#a.Name#.currentRow]"));
										ArrayAppend(functionOptions, "#index#=#thisVal#");
									</CFSCRIPT>
								</CFLOOP>
								<CFSET addEditFields=ListToArray(a.addEditFields)/>
								<CFSET addEditValues=ListToArray(a.addEditValues)/>
								<CFIF ArrayLen(addEditFields) EQ ArrayLen(addEditValues)>
									<CFLOOP from="1" to="#ArrayLen(addEditFields)#" step="1" index="i">
										<CFSCRIPT>
											//thisVal = ReReplaceNoCase(addEditValues[i], "'", "%27", "ALL");
											//thisVal = ReReplaceNoCase(thisVal, """", "%22", "ALL");
											//thisVal = ReReplaceNoCase(thisVal, "##", "%23", "ALL");
											thisVal=URLEncodedFormat(addEditValues[i]);
											ArrayAppend(functionOptions, "#addEditFields[i]#=#thisVal#");
										</CFSCRIPT>
									</CFLOOP>
								</CFIF>
										
								<CFSET functionOptions=ArrayToList(functionOptions, "&")/>		
							
								<td
									nowrap="nowrap"
									style="padding:2px;"
								>
									&nbsp;<a class="sqlb_link" href="#a.editAction#?#functionOptions#">edit</a>&nbsp;
								</td>
							
							</CFIF>
						
						
						
						</CFIF>
						
						<CFIF a.enableDelete AND a.deleteAction NEQ "" AND a.deleteSystem EQ "single">
							<CFIF a.deleteMethod EQ "get">
								<CFSET functionOptions=ArrayNew(1)/>
								<CFLOOP list="#evaluate("Caller." & a.Name & ".columnList")#" index="index">
									<CFSCRIPT>
										//thisVal = ReReplaceNoCase(URLEncodedFormat(evaluate("Caller.#a.Name#.#index#[Caller.#a.Name#.currentRow]")), "'", "%27", "ALL");
										//thisVal = ReReplaceNoCase(thisVal, """", "%22", "ALL");
										//thisVal = ReReplaceNoCase(thisVal, "##", "%23", "ALL");
										thisVal = URLEncodedFormat(evaluate("Caller.#a.Name#.#index#[Caller.#a.Name#.currentRow]"));
										ArrayAppend(functionOptions, "#index#=#thisVal#");
									</CFSCRIPT>
								</CFLOOP>
								<CFSET addDeleteFields=ListToArray(a.addDeleteFields)/>
								<CFSET addDeleteValues=ListToArray(a.addDeleteValues)/>
								<CFIF ArrayLen(addDeleteFields) EQ ArrayLen(addDeleteValues)>
									<CFLOOP from="1" to="#ArrayLen(addDeleteFields)#" step="1" index="i">
										<CFSCRIPT>
											thisVal = ReReplaceNoCase(URLEncodedFormat(addDeleteValues[i]), "'", "%27", "ALL");
											thisVal = ReReplaceNoCase(thisVal, """", "%22", "ALL");
											thisVal = ReReplaceNoCase(thisVal, "##", "%23", "ALL");
											ArrayAppend(functionOptions, "#addDeleteFields[i]#=#thisVal#");
										</CFSCRIPT>
									</CFLOOP>
								</CFIF>
										
								<CFSET functionOptions=ArrayToList(functionOptions, "&")/>		
							
								<td
									nowrap="nowrap"
									style="padding:2px;"
								>
									&nbsp;<a 
											class="sqlb_link" 
											style="color:##ff0000;" 
											href="#a.deleteAction#?#functionOptions#"
											onmouseover="window.status='Delete this record'; return true;"
											onmouseout="window.status=''; return true;"
											onclick="{window.location='#a.deleteAction#?#functionOptions#';}"
										>delete</a>&nbsp;
										<!--- if(confirm('Are you sure you want to delete this record?') == true) --->
											
								</td>
							
							</CFIF>
						
						<CFELSEIF a.enableDelete AND a.deleteAction NEQ "" AND a.deleteSystem EQ "batch">
						
							<td>
								<input type="checkbox" onClick="if(this.checked){SQLB_addBatchDelete(this.value);}else{SQLB_removeBatchDelete(this.value);}" name="SQLB_delete#recordOutCount#" value="#evaluate("Caller.#a.Name#.#a.deleteKey#[Caller.#a.Name#.currentRow]")#"/>							
							</td>
						
						</CFIF>
						
						<CFIF a.sortBy NEQ "">
							<td 
								nowrap="nowrap"
								style="padding:2px;"
							>
								<span class="sqlb_text">
								<CFIF ListFindNoCase(evaluate("Caller.#a.Name#.columnList"), a.sortBy)>
									<CFSET thisVal=evaluate("Caller.#a.Name#.#a.sortBy#[#recordIndex#]")/>
									<CFSET thisVal=ReReplaceNoCase(thisVal, "<[^>]*>", "", "ALL")/>
									#thisVal# 
								<CFELSE>
									(not in query)
								</CFIF>
								&nbsp;
								</span>
							</td>
						</CFIF>
						
						<CFLOOP list="#a.showColumns#" index="key">
							<CFIF key NEQ a.sortBy>
								<td 
									nowrap="nowrap"
									style="padding:2px;"
								>
									<span class="sqlb_text">
									<CFIF ListFindNoCase(evaluate("Caller.#a.Name#.columnList"), key)>
										<CFSET thisVal=evaluate("Caller.#a.Name#.#key#[Caller.#a.Name#.currentRow]")/>
										<CFSET thisVal=ReReplaceNoCase(thisVal, "<[^>]*>", "", "ALL")/>
										#thisVal# 
										<!--- <textarea 
											readonly="readonly" 
											class="sqlb_text" 
											type="text" 
											rows="1" 
											cols="#evaluate(Len(thisVal)+10)#"
											style="<CFIF recordOutCount MOD 2 EQ 0>background-color:##eeeeee; </CFIF>border:0px;"
										>#thisVal#</textarea>--->
									<CFELSE>
										(not in query)
									</CFIF>
									&nbsp;
									</span>
								</td>
							</CFIF>
						</CFLOOP>
						
						
					</tr>
					
					<CFIF evaluate("Caller.#a.Name#.recordCount") EQ evaluate("Caller.#a.Name#.currentRow")>
						<tr>
							<CFIF a.enableEdit AND a.editAction NEQ "">
								<td></td>
							</CFIF>
							<CFIF a.enableDelete AND a.deleteAction NEQ "">
								<td></td>
							</CFIF>
							<td colspan="#colspan#">
								<div class="sqlb_text" style="color:##ff0000; font-weight:bold;">[ end of list ]</div>							
							</td>
						</tr>
					</CFIF>

					<CFSET recordOutCount=recordOutCount+1/>
					<CFSET recordIndex=recordIndex+1/>
				</CFLOOP>
			</CFSAVECONTENT>
			
			<!--- after data has been used programmatically, remove apostrophes --->
			<CFSET collectionList="URL,attributes,form"/>
			<CFLOOP list="#collectionList#" index="index">
				<CFIF IsStruct(evaluate(index))>
					<CFLOOP collection="#evaluate(index)#" item="key">
						<CFSCRIPT>
							val=ReReplaceNoCase(evaluate("#index#.#key#"), "'", "%27", "ALL");
							val=ReReplaceNoCase(val, """", "%22", "ALL");
							val=ReReplaceNoCase(val, "##", "%23", "ALL");
							evaluate("#index#.#key#=val");
						</CFSCRIPT>
					</CFLOOP>
				</CFIF>
			</CFLOOP>
			
			<!--- begin output table --->
			<table border="0" cellpadding="0" cellspacing="0" width="100%">
			
				<!--- output controls --->
				<CFINCLUDE template="sqlb_navControls.cfm"/>
				
				<tr>
					<td colspan="#colspan#"><hr noshade="noshade" size="1" color="##000000"/></td>
				</tr>
				
				<!--- output heading --->
				<tr>
					<CFIF a.enableEdit AND a.editAction NEQ "">
						<td></td>
					</CFIF>
					<CFIF a.enableDelete AND a.deleteAction NEQ "">
						<td></td>
					</CFIF>
				
					<CFIF a.sortBy NEQ "">
						<td>
							<table border="0" cellpadding="0" cellspacing="0" width="100%">
								<tr>
									<td rowspan="3" bgcolor="##eeeeee"><img src="/images/spacer.gif" width="2" height="1"/></td>
									<td bgcolor="##eeeeee" colspan="2"><img src="/images/spacer.gif" width="1" height="2"/></td>
								</tr>
								<tr>
									<CFSET urlArray=ArrayNew(1)/>
									<CFLOOP collection="#url#" item="key">
										<CFIF key NEQ "sortOrder" AND 
											  key NEQ "sortBy" AND
											  key NEQ "onRow"
										>
											<CFSCRIPT>
												ArrayAppend(urlArray, key & "=" & evaluate("url.#key#"));
											</CFSCRIPT>
										</CFIF>
									</CFLOOP>
									<CFSET urlOptions=ArrayToList(urlArray, "&")/>
									<CFSET orderOption="ASC"/>
									<CFIF a.sortOrder EQ "ASC">
										<CFSET orderOption="DESC"/>
									</CFIF>
									<CFSET sortLink="#CGI.PATH_INFO#?onRow=1&sortOrder=#orderOption#&sortBy=#a.sortBy#&#urlOptions#"/>
									<td bgcolor="##cccccc" width="100%" style="padding-left:5px; padding-right:5px;" onClick="window.location='#sortLink#';" onMouseOver="this.style.cursor='hand';" onMouseOut="this.style.cursor='auto';">
										<a class="sqlb_columnHead" href="#sortLink#">#a.sortBy#</a>
										<img src="/images/SQLB_sort#a.sortOrder#.gif"/>
									</td>
									<td bgcolor="##999999" rowspan="2"><img src="/images/spacer.gif" width="2" height="1"/></td>
								</tr>
								<tr>
									<td bgcolor="##999999"><img src="/images/spacer.gif" width="1" height="2"/></td>
								</tr>
							</table>
						</td>
					</CFIF>
				
					<CFLOOP list="#a.showColumns#" index="index">
						<CFIF index NEQ a.sortBy>
							<td>
								<table border="0" cellpadding="0" cellspacing="0" width="100%">
									<tr>
										<td rowspan="3" bgcolor="##eeeeee"><img src="/images/spacer.gif" width="2" height="1"/></td>
										<td bgcolor="##eeeeee" colspan="2"><img src="/images/spacer.gif" width="1" height="2"/></td>
									</tr>
									<tr>
										<CFSET urlArray=ArrayNew(1)/>
										<CFLOOP collection="#url#" item="key">
											<CFIF key NEQ "sortOrder" AND 
												  key NEQ "sortBy" AND
												  key NEQ "onRow"
											>
												<CFSCRIPT>
													ArrayAppend(urlArray, key & "=" & evaluate("url.#key#"));
												</CFSCRIPT>
											</CFIF>
										</CFLOOP>
										<CFSET urlOptions=ArrayToList(urlArray, "&")/>
										<CFSET orderOption="ASC"/>
										<CFIF a.sortOrder EQ "ASC" AND index EQ a.sortBy>
											<CFSET orderOption="DESC"/>
										</CFIF>
										<CFSET sortLink="#CGI.PATH_INFO#?onRow=1&sortOrder=#orderOption#&sortBy=#index#&#urlOptions#"/>
										<td bgcolor="##cccccc" width="100%" style="padding-left:5px; padding-right:5px;" onClick="window.location='#sortLink#';" onMouseOver="this.style.cursor='hand';" onMouseOut="this.style.cursor='auto';">
											<a class="sqlb_columnHead" href="#sortLink#">#index#</a>
											<CFIF index EQ a.sortBy>
												<img src="/images/SQLB_sort#a.sortOrder#.gif"/>
											</CFIF>	
										</td>
										<td bgcolor="##999999" rowspan="2"><img src="/images/spacer.gif" width="2" height="1"/></td>
									</tr>
									<tr>
										<td bgcolor="##999999"><img src="/images/spacer.gif" width="1" height="2"/></td>
									</tr>
								</table>
							</td>
						</CFIF>
					</CFLOOP>
				</tr>
				
				<!--- output filter fields --->
				<form name="filterFields">
					<tr>
						<td colspan="#colspan#"><hr noshade="noshade" size="1" color="##000000"/></td>
					</tr>
					<tr>
						<CFIF a.enableEdit AND a.editAction NEQ "">
							<td></td>
						</CFIF>
						<CFIF a.enableDelete AND a.deleteAction NEQ "">
							<td></td>
						</CFIF>
						
						<CFIF a.sortBy NEQ "">
						
							<CFSET allURLOptionsArray=ArrayNew(1)/>
							<CFLOOP collection="#URL#" item="key">
								<CFSCRIPT>
									if (key NEQ "filter_#a.sortBy#") {
										ArrayAppend(allURLOptionsArray, "#key#=" & evaluate("URL.#key#"));
									}
								</CFSCRIPT>
							</CFLOOP>
						
							<CFPARAM name="URL.filter_#a.sortBy#" default=""/>
							<td style="padding-right:2px;" nowrap="nowrap">
								<CFIF ListFindNoCase(a.filterColumns, a.sortBy)>
									<CFSET filterDataTypesArray=ListToArray(a.filterDataTypes)/>
									<CFSET filterDataType=filterDataTypesArray[ListFindNoCase(a.filterColumns, a.sortBy)]/>
									<textarea readonly="readonly" class="sqlb_textInput" cols="20" rows="1" name="filter_#a.sortBy#">#evaluate("URL.filter_" & a.sortBy)#</textarea><input type="button" value="" onClick="window.location='#CGI.PATH_INFO#?queryName=#a.Name#&buildField=#a.sortBy#&buildDataType=#filterDataType#&#ArrayToList(allURLOptionsArray, "&")#'" class="sqlb_button" style="width:10px;"/>
								<CFELSE>
									&nbsp;
								</CFIF>	
							</td>
						</CFIF>
						
						<CFLOOP list="#a.showColumns#" index="key">
							<CFIF key NEQ a.sortBy>
							
								<CFSET allURLOptionsArray=ArrayNew(1)/>
								<CFLOOP collection="#URL#" item="k">
									<CFSCRIPT>
										if (key NEQ "filter_#k#") {
											ArrayAppend(allURLOptionsArray, "#k#=" & evaluate("URL.#k#"));
										}
									</CFSCRIPT>
								</CFLOOP>
							
								<CFPARAM name="URL.filter_#key#" default=""/>
								<td style="padding-right:2px;" nowrap="nowrap">
									<CFIF ListFindNoCase(a.filterColumns, key)>
										<CFSET filterDataTypesArray=ListToArray(a.filterDataTypes)/>
										<CFSET filterDataType=filterDataTypesArray[ListFindNoCase(a.filterColumns, key)]/>
										<textarea readonly="readonly" class="sqlb_textInput" cols="20" rows="1" name="filter_#key#">#evaluate("URL.filter_" & key)#</textarea><input type="button" value="" onClick="window.location='#CGI.PATH_INFO#?queryName=#a.Name#&buildField=#key#&buildDataType=#filterDataType#&#ArrayToList(allURLOptionsArray, "&")#'" class="sqlb_button" style="width:10px;"/>
									<CFELSE>
										&nbsp;
									</CFIF>	
								</td>
							</CFIF>
						</CFLOOP>
					</tr>
					<tr>
						<td colspan="#colspan#"><hr noshade="noshade" size="1" color="##000000"/></td>
					</tr>
				</form>
					
				<CFIF a.enableDelete AND a.deleteAction NEQ "" AND a.deleteSystem EQ "batch">
				
					<script language="javascript1.2"><!--
						
						function SQLB_addBatchDelete ( val ) {
						
							var f = document.forms['batchDelete'];
							var toDelete = null;
							if (f.deleteKeyValues.value != '') {
								toDelete = f.deleteKeyValues.value.split(",");
							}
							else {
								toDelete = new Array();
							}
							toDelete[toDelete.length] = val;
							f.deleteKeyValues.value=toDelete.join(",");						
						
						}	
						
						function SQLB_removeBatchDelete ( val ) {
						
							var f = document.forms['batchDelete'];
							var toDelete = f.deleteKeyValues.value.split(",");
							var newToDelete = new Array();
							for (var i=0; i<toDelete.length; i++) {
								if (toDelete[i] != val) {
									newToDelete[newToDelete.length] = toDelete[i];
								}
							}
							f.deleteKeyValues.value=newToDelete.join(",");
							
						}
						
						function SQLB_testSubmit ( form ) {
						
							if (form.deleteKeyValues.value != '') {
								var toDelete = form.deleteKeyValues.value.split(",");
								if (confirm('Are you sure you want to delete ' + toDelete.length + ' record(s)?')) {
									return true;
								}
								else {
									return false;
								}
							}
							else {
								alert('No records are selected!');
								return false;
							}
							
						}
						
						function SQLB_toggleAll ( b ) {
						
							var form = b.form;
						
							for (var i=0; i < form.elements.length; i++) {
							
								var e = form.elements[i];
								if (e.name.indexOf('SQLB_delete') == 0) {
									if (b.value == 'select all records') {
										e.checked = true;
										SQLB_removeBatchDelete(e.value);
										SQLB_addBatchDelete(e.value);
									}
									else {
										e.checked = false;
										SQLB_removeBatchDelete(e.value);
									}
								}
								
							}
							
							if (b.value == 'select all records') {
								b.value = 'deselect all records';
							}
							else {
								b.value = 'select all records';
							}
							
						}
					
					//--></script>
				
					
				
					<form onSubmit="return SQLB_testSubmit(this);" name="batchDelete" action="#a.deleteAction#" method="#a.deleteMethod#">
						<input type="hidden" name="deleteKey" value="#a.deleteKey#"/>
						<input type="hidden" name="deleteKeyValues" value=""/>
						
						<CFSET addDeleteFieldsArray=ListToArray(a.addDeleteFields)/>
						<CFSET addDeleteValuesArray=ListToArray(a.addDeleteValues)/>
						<CFLOOP from="1" to="#ArrayLen(addDeleteFieldsArray)#" step="1" index="i">
							<input type="hidden" name="#addDeleteFieldsArray[i]#" value="#addDeleteValuesArray[i]#"/>
						</CFLOOP>
					
						<tr>
							<td colspan="#colspan#">
								<input type="submit" value="delete selected records" style="font-family:Tahoma, sans-serif; font-size:8pt; color:##ffffff; background-color:##ff0000; border-color:##990000;"/>
								<input type="button" value="select all records" style="font-family:Tahoma, sans-serif; font-size:8pt;" onClick="SQLB_toggleAll(this);"/>
							</td>
						</tr>
						<tr>
							<td colspan="#colspan#">
								<hr noshade="noshade" size="1" color="##000000"/>
							</td>
						</tr>
						#recordHTML#
					</form>
				
				<CFELSE>
							
					#recordHTML#
					
				</CFIF>
					
				
			
				<tr>
					<td colspan="#colspan#"><hr noshade="noshade" size="1" color="##000000"/></td>
				</tr>
			
			<!--- output controls --->
			<CFINCLUDE template="sqlb_navControls.cfm"/>
									
			<!--- end output table --->
			</table>
			
		</CFOUTPUT>
	</CFSAVECONTENT>
	
	<CFSET newGeneratedContent=newGeneratedContent & secondaryContent/>

	<CFSET thisTag.generatedContent=newGeneratedContent/>
		
<!--- pre-processing is handled at the start tag --->
<CFELSEIF thisTag.executionMode EQ "start">

	<CFSAVECONTENT variable="newGeneratedContent">
		<CFOUTPUT>
			
			<!--- query parameters --->
			<CFPARAM name="attributes.name" default=""/>
			<CFPARAM name="attributes.dbType" default=""/>
			<CFPARAM name="attributes.dbServer" default=""/>
			<CFPARAM name="attributes.dbName" default=""/>
			<CFPARAM name="attributes.connectionString" default=""/>
			<CFPARAM name="attributes.username" default=""/>
			<CFPARAM name="attributes.password" default=""/>
			<CFPARAM name="attributes.maxRows" default=""/>
			<CFPARAM name="attributes.blockFactor" default=""/>
			<CFPARAM name="attributes.timeout" default=""/>
			<CFPARAM name="attributes.cachedAfter" default=""/>
			<CFPARAM name="attributes.cachedWithin" default=""/>
			<CFPARAM name="attributes.provider" default=""/>
			<CFPARAM name="attributes.providerDSN" default=""/>
			<CFPARAM name="attributes.showColumns" default=""/>
			<CFPARAM name="attributes.filterColumns" default=""/>
			<CFPARAM name="attributes.filterDataTypes" default=""/>
			<CFPARAM name="attributes.sortBy" default=""/>
			<CFPARAM name="attributes.sortOrder" default="ASC"/>
			<CFPARAM name="attributes.lockSorting" default="FALSE"/>
			<CFPARAM name="attributes.enableEdit" default="FALSE"/>
			<CFPARAM name="attributes.editAction" default=""/>
			<CFPARAM name="attributes.editMethod" default="GET"/>
			<CFPARAM name="attributes.addEditFields" default=""/>
			<CFPARAM name="attributes.addEditValues" default=""/>
			<CFPARAM name="attributes.enableDelete" default="FALSE"/>
			<CFPARAM name="attributes.deleteKey" default="ID"/>
			<CFPARAM name="attributes.deleteSystem" default="SINGLE"/>
			<CFPARAM name="attributes.deleteAction" default=""/>
			<CFPARAM name="attributes.deleteMethod" default="GET"/>
			<CFPARAM name="attributes.addDeleteFields" default=""/>
			<CFPARAM name="attributes.addDeleteValues" default=""/>
			<CFPARAM name="attributes.where" default=""/>
			
			<!--- style parameters --->
			<CFPARAM name="attributes.cssLink" default=""/>
			
			<!--- functionality parameters from the URL or a Form --->
			<CFPARAM name="Form.sortBy" default=""/>
			<CFPARAM name="URL.sortBy" default="#Form.sortBy#"/>
			<CFPARAM name="Form.sortOrder" default=""/>
			<CFPARAM name="URL.sortOrder" default="#Form.sortOrder#"/>
			<CFPARAM name="Form.onRow" default="1"/>
			<CFPARAM name="URL.onRow" default="#Form.onRow#"/>
			<CFPARAM name="Form.RPP" default="25"/>
			<CFPARAM name="URL.RPP" default="#Form.RPP#"/>
			<CFPARAM name="Form.onIdx" default="1"/>
			<CFPARAM name="URL.onIdx" default="#Form.onIdx#"/>
		
			<CFIF NOT IsDefined("attributes.datasource")>
				[ <strong>SQLB Error:</strong> You must specify a data source name. ]
				<CFEXIT/>
			</CFIF>
			
			<!--- test filterColumn list length for congruity with filterDataTypes list --->
			<CFIF ArrayLen(ListToArray(attributes.filterColumns)) GT 0 AND ArrayLen(ListToArray(attributes.filterColumns)) NEQ ArrayLen(ListToArray(attributes.filterDataTypes))>
				[ <strong>SQLB Error:</strong> The lengths of filterColumns and filterDataTypes lists must match. ]
				<CFEXIT/>
			</CFIF>
			
			<CFIF attributes.enableEdit EQ "true" AND
				  (attributes.editAction EQ "" OR attributes.editMethod EQ "")
			>
				[ <strong>SQLB Error:</strong> To enable editing links, you must also specify an editAction and an editMethod. ]
				<CFEXIT/>
			</CFIF>
			
			<CFIF trim(attributes.name) EQ "">
				<CFSET attributes.name="SQLB"/>
			</CFIF>
		
			<!--- Form/URL submitted sorting orders override attribute sort order if
				  attributes.lockSorting is FALSE --->
			<CFIF URL.sortBy NEQ "" AND
				  URL.sortOrder NEQ "" AND
				  NOT attributes.lockSorting
			>
				<CFSET attributes.sortBy=URL.sortBy/>
				<CFSET attributes.sortOrder=URL.sortOrder/>
			</CFIF>
			<CFIF attributes.sortOrder NEQ "ASC" AND attributes.sortOrder NEQ "DESC">
				<CFSET attributes.sortOrder="ASC"/>
			</CFIF>
			
			<!--- create abbreviated scope varibles for programming ease --->
			<CFSET a=StructNew()/>
			<CFSET f=StructNew()/>
			<CFSET u=StructNew()/>
			<CFLOOP list="attributes,form,URL" index="collectionName">
				<CFLOOP collection="#evaluate(collectionName)#" item="key">
					<CFSCRIPT>
						evaluate(mid(collectionName, 1, 1) & ".#key#=#collectionName#.#key#");
					</CFSCRIPT>
				</CFLOOP>
			</CFLOOP>
			
			<!--- load stylesheet --->
			<CFIF a.cssLink NEQ "">
				<link rel="stylesheet" href="#a.cssLink#" type="text/css"/>
			</CFIF>
		
			
			
		</CFOUTPUT>
	</CFSAVECONTENT>
	
</CFIF>