<cfsilent>
<!--- getprojectswithadvisors.cfm Copyright (c) 2003 UW Board of Regents

	Description:
		Returns a query with the projects and the advisors info
	Parameters:
		results - the name of the returned query.
	Usage:
		called as a custom tag <cfmodule  template="#request.customtagspath#/projectswithadvisors.cfm">
	Created:
		4/28/04; Deanna
	Revised
		today's date; developer
--->

<cfparam name="attributes.results" default="getAdvisors">


<cfset structclear(application)>
<!--- If the query hasn't been cached to the application scope, do that first --->
<cfif not isdefined("application.querystruct.advisors") or isDefined("url.reset")>

<cfquery name="getadvisors" password="#request.pword#" username="#request.uname#" datasource="#request.dsn#">
SELECT  pt.name AS project, pt.projectid, p.personid, d.working_ttl, d.fullname, d.last_name, d.phone, d.fax, d.address, d.city, d.state, d.zip, d.email
FROM    project pt, person p,
        (SELECT  p.prsn_id, r.working_ttl, dirfunctions.formatdirfullname(p.prsn_id) AS fullname, p.last_name,
        dirfunctions.formatdirphone(p.prsn_id, dpp.phn_id) AS phone,
        dirfunctions.formatdirfax(p.prsn_id, dpf.fax_id) AS fax,
        CASE WHEN dpa.str IS NOT NULL THEN dpa.str ELSE dpa.pobox END AS address,
        dpa.city, dpa.st as state, dpa.zip,
        CASE WHEN dirfunctions.formatdiremail(n.prsn_id, n.net_id) != 'Unknown' THEN '<a href="mailto:' || dirfunctions.formatdiremail(n.prsn_id, n.net_id) || '">' || dirfunctions.formatdiremail(n.prsn_id, n.net_id) || '</a>' END AS clickableemail,
        dirfunctions.formatdiremail(n.prsn_id, n.net_id) AS email
        FROM    dir_role r, dir_emp_role e, dir_prsn p,
        dir_prsn_phn dpp, dir_prsn_fax dpf, dir_prsn_addr dpa, dir_netid_mapping n
        WHERE   r.deleted_fl = 'N'
        AND     r.role_id = e.role_id
        AND     e.prsn_id = p.prsn_id
        AND     p.prsn_id = dpp.prsn_id
        AND     p.prsn_id = dpf.prsn_id
        AND     dpp.prmy_fl = 'Y'
        AND     p.prsn_id = dpa.prsn_id
        AND     p.prsn_id = n.prsn_id (+)) d
WHERE   pt.advisor = p.personid (+)
AND     p.personid = d.prsn_id (+)
AND     pt.active = 1
AND     pt.projectid IN (SELECT distinct(projectid) FROM projectcounty)
ORDER BY project
</cfquery>

<cflock scope="application" type="exclusive" timeout="10">
<cfset application.querystruct.advisors = getadvisors>
</cflock>
</cfif>

<!--- Set the caller results to the application variable --->
<cfset "caller.#attributes.results#" = application.querystruct.advisors>

</cfsilent>