//**************************************
// Name: Output simple query custom tag
// Description:This code takes arguments related to table display options and table querying then outputs the query results in a formatted table. ColdFusion custom tag that is tested with MX.
// By: Marvin Eads
//
//This code is copyrighted and has// limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=132&lngWId=9//for details.//**************************************
<!---
ColdfusionMX Custom Tag
Purpose: Display query output in a table format
Coded by: Marvin Eads
Coded date: 10 JAN 07
Required Attributes: dsn, table
Other Attributes:
fieldlist comma delimited list of fields to use
aliaslist comma delimited list of header titles to use, follow order of fieldlist
alternate alternate row colors of output data, default no
arc alterate row background color if alternate is yes
mrc main row background color if alternate is yes
hrc header row background color if alternate is yes
afc alternate row font color if alternate is yes
mfc main row font color if alternate is yes
hfc header row font color
border border around cells, default yes
bc border color
dt formatting for date/time fields, default date, available options are date, time, datetime
od comma delimited list of fields to sort by
All other attributes are used in name/value pairs as arguments for WHERE clause in query
--->
<cfparam name="attributes.dsn" default="" type="string"> <!--- datasource to pull from --->
<cfparam name="attributes.table" default="" type="string"> <!--- table to pull data from --->
<cfparam name="attributes.fieldlist" default="" type="string"> <!--- field list to pull from specified table --->
<cfparam name="attributes.aliaslist" default="" type="string"> <!--- column header listing in same order as fieldlist, fieldlist required --->
<cfparam name="attributes.alternate" default="no" type="boolean"> <!--- alternate row colors --->
<cfparam name="attributes.arc" default="##CCCCCC" type="string"> <!--- alternate row background color --->
<cfparam name="attributes.mrc" default="##FFFFFF" type="string"> <!--- main row background color --->
<cfparam name="attributes.hrc" default="##000000" type="string"> <!--- header row background color --->
<cfparam name="attributes.afc" default="##000000" type="string"> <!--- alternate row font color --->
<cfparam name="attributes.mfc" default="##000000" type="string"> <!--- main row font color --->
<cfparam name="attributes.hfc" default="##FFFFFF" type="string"> <!--- header row font color --->
<cfparam name="attributes.border" default="yes" type="boolean"> <!--- border on table --->
<cfparam name="attributes.bc" default="##000000" type="string"> <!--- border color --->
<cfparam name="attributes.dt" default="date" type="string"> <!--- how to format date/time, date = yyyy-mm-dd datetime = yyyy-mm-dd hh:mm:ss time = hh:mm:ss --->
<cfparam name="attributed.od" default="" type="string"> <!--- comma delimited list of fields to order by --->
<!--- creates new structure and duplicates attributes structure, then strips out all known attributes --->
<cfset wh = StructNew()>
<cfset wh = Duplicate(Attributes)>
<cfset b = StructDelete(wh, 'dsn', false)>
<cfset b = StructDelete(wh, 'table', false)>
<cfset b = StructDelete(wh, 'fieldlist', false)>
<cfset b = StructDelete(wh, 'aliaslist', false)>
<cfset b = StructDelete(wh, 'alternate', false)>
<cfset b = StructDelete(wh, 'arc', false)>
<cfset b = StructDelete(wh, 'mrc', false)>
<cfset b = StructDelete(wh, 'hrc', false)>
<cfset b = StructDelete(wh, 'afc', false)>
<cfset b = StructDelete(wh, 'mfc', false)>
<cfset b = StructDelete(wh, 'hfc', false)>
<cfset b = StructDelete(wh, 'border', false)>
<cfset b = StructDelete(wh, 'bc', false)>
<cfset b = StructDelete(wh, 'dt', false)>
<cfset b = StructDelete(wh, 'od', false)>
<cfif attributes.dsn eq '' or attributes.table eq ''> <!--- error checking --->
The dsn and table attributes must be specified.
<cfelseif attributes.aliaslist neq '' and attributes.fieldlist eq ''> <!--- error checking --->
You must specify the fieldlist attribute when using the aliaslist attribute.
<cfelse> <!--- main body of function --->
<cfquery datasource="#attributes.dsn#" name="DisplayQuery">
SELECT <cfif attributes.fieldlist neq ''>#attributes.fieldlist#<cfelse>*</cfif>
FROM #attributes.table#
<cfif StructCount(wh) gt 0>
<cfset c = 1>
<cfloop collection="#wh#" item="m">
<cfset n = StructFind(wh, m)>
<cfif c eq 1>
WHERE #m# = <cfif isnumeric(n)>#n#<cfelse>'#n#'</cfif>
<cfelse>
AND #m# = <cfif isnumeric(n)>#n#<cfelse>'#n#'</cfif>
</cfif>
<cfset c = c + 1>
</cfloop>
</cfif>
<cfif attributes.od neq ''>
ORDER BY #attributes.od#
</cfif>
</cfquery>
<cfoutput>
<table cellpadding="0" cellspacing="0" style="border-collapse:collapse;<cfif attributes.border eq 'yes'> border:thin solid #attributes.bc#;</cfif>">
<thead style="background-color:#attributes.hrc#; color:#attributes.hfc#;">
<tr>
<cfif attributes.aliaslist neq ''>
<cfloop list="#attributes.aliaslist#" index="s">
<th<cfif attributes.border eq 'yes'> style="border:thin solid #attributes.bc#"</cfif>>#s#</th>
</cfloop>
<cfelseif attributes.fieldlist neq ''>
<cfloop list="#attributes.fieldlist#" index="s">
<th<cfif attributes.border eq 'yes'> style="border:thin solid #attributes.bc#"</cfif>>#s#</th>
</cfloop>
<cfelse>
<cfloop list="#DisplayQuery.ColumnList#" index="s">
<th<cfif attributes.border eq 'yes'> style="border:thin solid #attributes.bc#"</cfif>>#s#</th>
</cfloop>
</cfif>
</tr>
</thead>
<cfloop query="DisplayQuery">
<cfset alt = DisplayQuery.CurrentRow MOD 2>
<tbody<cfif attributes.alternate eq 'yes'> style="<cfif alt eq 1>background-color:#attributes.mrc#; color:#attributes.mfc#;<cfelse>background-color:#attributes.arc#; color:#attributes.afc#;</cfif>"</cfif>>
<tr>
<cfloop list="#DisplayQuery.ColumnList#" index="u">
<td<cfif attributes.border eq 'yes'> style="border:thin solid #attributes.bc#; "</cfif>>
<cfif isdate(#evaluate('DisplayQuery.#u#')#)>
<cfswitch expression="#attributes.dt#">
<cfcase value="date">
#dateformat(evaluate('DisplayQuery.#u#'),'yyyy-mm-dd')#
</cfcase>
<cfcase value="datetime">
#dateformat(evaluate('DisplayQuery.#u#'),'yyyy-mm-dd hh:mm:ss')#
</cfcase>
<cfcase value="time">
#dateformat(evaluate('DisplayQuery.#u#'),'hh:mm:ss')#
</cfcase>
</cfswitch>
<cfelse>
#evaluate('DisplayQuery.#u#')#
</cfif>
</td>
</cfloop>
</tr>
</tbody>
</cfloop>
</table>
</cfoutput>
</cfif>
|
