Example 5: Custom filter

Source code for this example 

<!--- 
Note that the database for this example must be set up as a datasource first. The Microsoft Access database is located in the examples folder. Place the datasource name in the request.dsn variable.

Microsoft Access is not recommended for web purposes, and is provided here for demonstration purposes only. Better performance will be achieved with enterprise databases.
--->

<cfimport prefix="esw" taglib="../customtags/eswsoftware/">

<!--- allow values from either the FORM or URL scopes --->
<cfparam name="url.filter" default="">
<cfparam name="url.pointType" default="">
<cfparam name="url.near" default="">
<cfparam name="form.filter" default="#url.filter#">
<cfparam name="form.pointType" default="#url.pointType#">
<cfparam name="form.near" default="#url.near#">

<!--- filter by the point type --->
<cfif len(form.pointType)>
	<cfset whereClause = "(namedata.pointdescriptioncode = '#htmlEditFormat(form.pointType)#')">
<cfelse>
	<cfset whereClause = "">
</cfif>

<!--- filter by near main centre (i.e. find points within 50km of the main centre) --->
<cfif len(form.near)>
	<!--- first find the coordinates of the main centre --->
	<cfquery name="mainCentre" datasource="#request.dsn#" cachedwithin="#createTimeSpan(0, 0, 10, 0)#">
		SELECT 		northing,
					easting
		FROM		namedata
		WHERE		relatedid = <cfqueryparam value="#form.near#" cfsqltype="CF_SQL_INTEGER">
	</cfquery>
	<cfif mainCentre.recordCount>
		<cfif len(whereClause)>
			<cfset whereClause = whereClause & " AND ">
		</cfif>
		<!--- 
		Here is the explanation for the following formula....
		
		if x is the distance between the two points' eastings
		and y is the distance between the two points' northings
		and z is the direct-line distance between the two points
		then x^2 + y^2 = z^2
		
		We want z to be less than 50km (50000) so we want z^2 to be less than 50000^2 or 2500000000
		--->
		<cfset whereClause = whereClause & "(
			SQUARE(namedata.northing-#mainCentre.northing#)
			+ SQUARE(namedata.easting-#mainCentre.easting#)
			< 2500000000
		)">
	</cfif>
</cfif>

<!--- get all the point types for the select box --->
<cfquery name="pointTypes" datasource="#request.dsn#" cachedwithin="#createTimeSpan(0, 0, 10, 0)#">
	SELECT 		pointDescriptionName,
				pointDescriptionCode
	FROM		pointdes
	ORDER BY	pointDescriptionName
</cfquery>

<!--- styles for our filter form --->
<style>
	#filterForm {
		border : 1px solid black;
		background-color : #dddddd;
		margin-left : 10px;
		float : left;
	}
	#filterForm THEAD {
		background-color : #666666;
		font-size : x-small;
	}
	#filterForm TBODY {
		font-size : xx-small;
	}
	#filterForm THEAD TH {
		color : white;
	}
	#filterForm TH,
	#filterForm TD {
		padding : 5px;
	}
</style>


<form action="example5.cfm" method="post">
	<div id="filterForm">
		<table border="0" cellspacing="0" cellpadding="0">
			<thead>
				<tr>
					<th colspan="2">
						Search point data
					</th>
				</tr>
			</thead>
			<tbody>
				<tr>
					<th>
						<label for="pointType">Type of point:</label>
					</th>
					<td>
						<select id="pointType" name="pointType" style="width : 150px;">
							<option value="">[All types]</option>
							<cfoutput query="pointTypes">
								<option 
									<cfif form.pointType eq pointDescriptionCode>selected</cfif>
									value="#pointDescriptionCode#"
								>#pointDescriptionName#</option>
							</cfoutput>
						</select>
					</td>
				</tr>
				<tr>
					<th>
						<label for="near">Near:</label>
					</th>
					<td>
						<select id="near" name="near" style="width : 150px;">
							<option value="">[Anywhere]</option>
							<option <cfif form.near eq "37145">selected</cfif> value="37145">Auckland</option>
							<option <cfif form.near eq "57206">selected</cfif> value="57206">Hamilton</option>
							<option <cfif form.near eq "53400">selected</cfif> value="53400">Wellington</option>
							<option <cfif form.near eq "22959">selected</cfif> value="22959">Christchurch</option>
							<option <cfif form.near eq "13106">selected</cfif> value="13106">Dunedin</option>
						</select>
					</td>
				</tr>
				<tr>
					<th>
						<label for="filter">Search keywords:</label>
					</th>
					<td>
						<input type="text" id="filter" name="filter" style="width : 150px;" value="<cfoutput>#htmlEditFormat(form.filter)#</cfoutput>" onclick="this.select()">
					</td>
				</tr>
				<tr>
					<th></th>
					<td>
						<input type="submit" value="Search">
					</td>
				</tr>
			</tbody>
		</table>
	</div>
</form>

<esw:sortableplus 
	table="namedata inner join pointdes on namedata.pointdescriptioncode = pointdes.pointdescriptioncode"
	dbms="Microsoft Access"
	datasource="#request.dsn#"
	sortBy="pointname"
	rows="10"
	key="relatedid"
	style="width : 600px"
	cachedwithin="#createTimeSpan(0, 0, 10, 0)#"
	showFilter="false"
	where="#whereClause#"
	thisPage = "example5.cfm?pointType=#htmlEditFormat(form.pointType)#&amp;near=#htmlEditFormat(form.near)#"
	skin="glacier_cornflower"
>

	<esw:column
		column="relatedid"
		caption="ID"
		type="numeric"
	/>
	
	<esw:column
		column="pointname"
		caption="Name"
	/>
	
		<esw:column
		column="pointdescriptionname"
		caption="Description"
	/>
	
	<esw:column
		column="Latitude"
		type="numeric"
		mask=".00"
	/>
	
	<esw:column
		column="Longitude"
		type="numeric"
		mask=".00"
	/>

</esw:sortableplus>