Hi all, i've been having a really sticky time with the following function.
the output
should be an array like this:
1 = 90
2 = Registered Blind
3 = Y
4 = D
5 = 40
6 = empty
7 = empty
8 = empty
9 = empty
10 = empty
11 = empty
12 = empty
(you can ignore elements 6 - 12, they should not be populated anyway)
what i get outputed is the initial array with defaulted values, even though the queries are being run and the correct data is being outputed.
CODE
<cffunction name="personalstatus" output="false" returntype="array">
<cfargument name="peo_id" type="string" required="yes">
<cfargument name="cot_id" type="string" required="yes">
<cfargument name="ext_flag" type="string" required="no">
<cfargument name="status_flag" type="string" required="no">
<cfset var x = 1>
<cfset var proof=ArrayNew(2)>
<!--- Now populate the array with default values --->
<!--- this aids comparison checks later --->
<cfset proof[1][1] = "0">
<cfset proof[1][2] = "0">
<cfset proof[1][3] = "Y">
<cfset proof[1][4] = "0">
<cfset proof[1][5] = "0">
<cfset proof[1][6] = "empty">
<cfset proof[1][7] = "empty">
<cfset proof[1][8] = "empty">
<cfset proof[1][9] = "empty">
<cfset proof[1][10] = "empty">
<cfset proof[1][11] = "empty">
<cfset proof[1][12] = "empty">
<!--- Query used to retreive the applicants Personal Statuses --->
<cfquery name="qryPersonalDetails" datasource="#application.dsn#">
SELECT pst.id, pst.description, tps.ext_verification_required,
pst.status_type, tps.weight
FROM cdh_personal_status_histories psh,
cdh_personal_statuses pst,
cdh_trans_personal_statuses tps
WHERE psh.peo_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.peo_id#" />
AND psh.tty_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.cot_id#" />
AND trunc(psh.start_date) <= trunc(sysdate)
AND end_date is null
<cfif isDefined("status_flag")>
AND psh.status_type = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.status_flag#" />
</cfif>
AND psh.pst_id = pst.id
AND pst.active = 'Y'
AND pst.id = tps.pst_id
AND tps.tty_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.cot_id#" />
<cfif isDefined("ext_flag")>
AND tps.ext_verification_required = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.ext_flag#" />
</cfif>
</cfquery>
<!--- If the personal details query has returned values, run the proof details query --->
<cfif qryPersonalDetails.recordcount>
<!--- Loop around the Personal Statuses for the applicant --->
<cfloop query="qryPersonalDetails">
<!--- Query used to retreive any proofs required to prove the Personal Statuses --->
<cfquery name="qryProofDetails" datasource="#application.dsn#">
SELECT prt.id, prt.description, tpt.issue_required, tpt.expiry_required,
tpt.length_award, ppr.issue_date, ppr.expiry_date
FROM cdh_personal_status_proofs psp,
cdh_proof_types prt,
cdh_trans_proof_types tpt,
cdh_provided_proofs ppr
WHERE psp.pst_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#qryPersonalDetails.id#" />
AND psp.tty_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.cot_id#" />
AND prt.id = psp.prt_id
AND prt.active = 'Y'
AND tpt.prt_id = prt.id
AND tpt.tty_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.cot_id#" />
AND ppr.prt_id = prt.id
AND ppr.peo_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.peo_id#" />
AND ppr.tty_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.cot_id#" />
AND trunc(ppr.start_date) <= trunc(sysdate)
AND end_date is null
</cfquery>
<!--- If the proof details query has returned values, populate the array --->
<cfif qryProofDetails.recordcount>
<cfloop query="qryProofDetails">
<cfset proof[x][1]="#qryPersonalDetails["id"][qryPersonalDetails.CurrentRow]#">
<cfset proof[x][2]="#qryPersonalDetails["description"][qryPersonalDetails.CurrentRow]#">
<cfset proof[x][3]="#qryPersonalDetails["ext_verification_required"][qryPersonalDetails.CurrentRow]#">
<cfset proof[x][4]="#qryPersonalDetails["status_type"][qryPersonalDetails.CurrentRow]#">
<cfset proof[x][5]="#qryPersonalDetails["weight"][qryPersonalDetails.CurrentRow]#">
<cfset proof[x][6]="#qryProofDetails.id#">
<cfset proof[x][7]="#qryProofDetails.description#">
<cfset proof[x][8]="#qryProofDetails.issue_required#">
<cfset proof[x][9]="#qryProofDetails.expiry_required#">
<cfset proof[x][10]="#qryProofDetails.issue_date#">
<cfset proof[x][11]="#qryProofDetails.expiry_date#">
<cfset proof[x][12]="#qryProofDetails.length_award#">
<cfset x = x + 1>
</cfloop>
<!--- The proof details query has not returned values, populate the array using empty values --->
<cfelse>
<!--- <cfdump var="#qryPersonalDetails#">
<cfabort> ---><!--- dump and abort were used for diagnostic purposes --->
<cfset proof[x][1]="#qryPersonalDetails["id"][qryPersonalDetails.CurrentRow]#">
<cfset proof[x][2]="#qryPersonalDetails["description"][qryPersonalDetails.CurrentRow]#">
<cfset proof[x][3]="#qryPersonalDetails["ext_verification_required"][qryPersonalDetails.CurrentRow]#">
<cfset proof[x][4]="#qryPersonalDetails["status_type"][qryPersonalDetails.CurrentRow]#">
<cfset proof[x][5]="#qryPersonalDetails["weight"][qryPersonalDetails.CurrentRow]#">
<cfset proof[x][6]="empty">
<cfset proof[x][7]="empty">
<cfset proof[x][8]="empty">
<cfset proof[x][9]="empty">
<cfset proof[x][10]="empty">
<cfset proof[x][11]="empty">
<cfset proof[x][12]="empty">
<cfset x = x + 1>
</cfif>
</cfloop>
</cfif>
<cfreturn proof>
</cffunction>
this has been getting me down for a few days! any help would be great
Bruce
13 Sep, 2007 - 05:37 AM
| 