Hi all,
I am having an issue getting amounts to accumulate and disobeying the DRY rule.
Here is a very much abbreviated synopsis of my existing code. As you can see I
have several parameters to account for within each sub-query which I've tried
to accomodate using cfloops 3 levels deep, as well as cfswitches nested 2
levels deep:
<cfquery name="master1" datasource="test">
SELECT type1,amount1,staff
FROM event_log
WHERE location = 'LOC1'
AND (dos BETWEEN '1/1/2014'
AND '3/31/2014')
ORDER BY staff
</cfquery>
<cfquery name="master2" datasource="test">
SELECT type2,amount2,staff
FROM event_log
WHERE location = 'LOC1'
AND (dos BETWEEN '1/1/2014'
AND '3/31/2014')
ORDER BY staff
</cfquery>
<cfquery name="master3" datasource="test">
SELECT type3,amount3,staff
FROM event_log
WHERE location = 'LOC1'
AND (dos BETWEEN '1/1/2014'
AND '3/31/2014')
ORDER BY staff
</cfquery>
<cfset location1=structNew()>
<cfset location1.john.typeA.amt = 0>
<cfset location1.john.typeA.ct = 0>
<cfset location1.john.typeB.amt = 0>
<cfset location1.john.typeB.ct = 0>
<cfset location1.john.typeC.amt = 0>
<cfset location1.john.typeC.ct = 0>
<cfset location1.jane.typeA.amt = 0>
<cfset location1.jane.typeA.ct = 0>
<cfset location1.jane.typeB.amt = 0>
<cfset location1.jane.typeB.ct = 0>
<cfset location1.jane.typeC.amt = 0>
<cfset location1.jane.typeC.ct = 0>
<cfloop list="A 1,A 2,A 3,B 1,B 2,B 3,CASH1,CASH2,CASH3" index="i">
<cfloop list="JOHN DOE,JANE DOE" index="j">
<cfquery name="subqry1" dbtype="query">
SELECT * FROM master1 WHERE type1 = '#trim(left(i,4))#'
AND staff = '#trim(j)#'
</cfquery>
<cfquery name="subqry2" dbtype="query">
SELECT * FROM master2 WHERE type2 = '#trim(left(i,4))#'
AND staff = '#trim(j)#'
</cfquery>
<cfquery name="subqry3" dbtype="query">
SELECT * FROM master3 WHERE type3 = '#trim(left(i,4))#'
AND staff = '#trim(j)#'
</cfquery>
<cfloop query="subqry1">
<cfswitch expression="#trim(left(i,4))#">
<cfcase value="A">
<cfswitch expression="#trim(j)#">
<cfcase value="JOHN DOE">
<cfset
location1.john.typeA.amt += val(subqry1.amount1)>
<cfset
location1.john.typeA.ct += 1>
</cfcase>
<cfcase value="JANE DOE">
<cfset
location1.jane.typeA.amt += val(subqry1.amount1)>
<cfset
location1.jane.typeA.ct += 1>
</cfcase>
</cfswitch>
</cfcase>
<cfcase value="B">
<cfswitch expression="#trim(j)#">
...and so on...
</cfswitch>
...then finally...
</cfloop>
...then start all over again w/ subqry2...
<cfloop query="subqry2">
<cfswitch expression="#trim(left(i,4))#">
...etc, etc, ad infinitum.
I guess I'm just not understanding how to loop through the different levels of
the struct correctly. I don't see using a struct built this way as feasible:
location1.JOHN DOE.A 1.amt += val(subqry1.amount1)
...because of the spaces...but the spaces have to be there to run the actual
data field from SQL against due to the way the database had been set up (i.e.
'CASH' spelled out for cash transactions, w/ 'A' and 'B' for C/C and Net-30,
respectively. Also, I could see looping the first list (index="i") twice as
A,B,CASH and 1,2,3, but then I'd have cfloops FOUR deep.
Personally, I don't like the clutter of the code, but I could live with it if
not for the fact that it is not accurately adding the amounts. When I do a
cfdump of 'location1' OUTSIDE all loops, and change the date range in the
master queries to one that I've pulled out of SSMS, the amounts and counts do
not add up correctly. It's further complicated by the fact that the original
DBA/Web Dev set each 'event' in the event log to have 3 possible transactions,
hence the type1|2|3 and amount1|2|3.
Quite a mess, I know. Please advise me on how to a) make this code work and b)
make it more elegant? I'm greatly indebted to anyone who can help! Thank you
in advance, House of Fusion.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-newbie/message.cfm/messageid:6108
Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-newbie/unsubscribe.cfm
