Speeding up code with arrays and loops
Hi All,
I'm having a few troubles with some code which is taking too long to load. I've become so bogged down in the code that perhaps I've missed something obvious which will make the whole thing load faster so I thought I'd post the algorithm here and see if anyone had some ideas on ways to make it faster.
Summary
The code creates a report which outputs in html and is ordered by transaction date (from earliest to latest). The catch is that each client has up to 20 different types of transactions and the report needs to be ordered by ANY of the transaction dates.
EG. Client 1 has 3 transactions: the 15th May, 17th May and the 3rd June. Client 2 has 4 transactions: the 10th May, 14th may, 16th may, 7th June.
The report outputs each of these transactions and how much they were for, in the order of earliest transaction to latest transaction.
I don't believe I can (or at least I don't know how) to use SQL to order the transactions and use a simple cfoutput query= because of the fact that each client has the 20 different types of transactions (eg. GeneralFees1Amount, GeneralFees1Date - GeneralFees2Amount, GeneralFees2Date - GlassFees1Amount, GlassFees1Date - GlassFees2Amount, GlassFees2Date - GlassFees3Amount, GlassFees3Date etc).
Because, of this we aren't ordering by a single field but by all the date fields. The report outputs on a month by month basis (one month at a time)
Detail
To achieve this I do the following:
Do a full query to extract the correct data for a month and year from the database
Do a loop through each day of this month and do a query of the query to get the data for this specific day.
Loop through each of the various transaction dates for each customer on this day and check if they are null or not, if they aren't null then they go into an array which has:
Array[month][day][inner-ar
So, this way the array does the sorting for me and I get to go through each customer to load the array. The inner array has things like the customer name, transaction amount, notes etc.
Now we have loaded the main array up we use the main array to output the report by looping through each day in the array and then looping through the inner arrays (transactions) stored in that day of the array. Then i grab the information stored in the inner array (which i could convert to a 6 item list if this would speed things up as its just simple text data) and output it on one line after another.
This works but it takes a LONG time because, first the array has to be built then it has to be looped through repeatedly to output the report.
Anyone have some ideas to speed up this report? Am I missing something obvious?
Cheers,
Brad
I'm having a few troubles with some code which is taking too long to load. I've become so bogged down in the code that perhaps I've missed something obvious which will make the whole thing load faster so I thought I'd post the algorithm here and see if anyone had some ideas on ways to make it faster.
Summary
The code creates a report which outputs in html and is ordered by transaction date (from earliest to latest). The catch is that each client has up to 20 different types of transactions and the report needs to be ordered by ANY of the transaction dates.
EG. Client 1 has 3 transactions: the 15th May, 17th May and the 3rd June. Client 2 has 4 transactions: the 10th May, 14th may, 16th may, 7th June.
The report outputs each of these transactions and how much they were for, in the order of earliest transaction to latest transaction.
I don't believe I can (or at least I don't know how) to use SQL to order the transactions and use a simple cfoutput query= because of the fact that each client has the 20 different types of transactions (eg. GeneralFees1Amount, GeneralFees1Date - GeneralFees2Amount, GeneralFees2Date - GlassFees1Amount, GlassFees1Date - GlassFees2Amount, GlassFees2Date - GlassFees3Amount, GlassFees3Date etc).
Because, of this we aren't ordering by a single field but by all the date fields. The report outputs on a month by month basis (one month at a time)
Detail
To achieve this I do the following:
Do a full query to extract the correct data for a month and year from the database
Do a loop through each day of this month and do a query of the query to get the data for this specific day.
Loop through each of the various transaction dates for each customer on this day and check if they are null or not, if they aren't null then they go into an array which has:
Array[month][day][inner-ar
So, this way the array does the sorting for me and I get to go through each customer to load the array. The inner array has things like the customer name, transaction amount, notes etc.
Now we have loaded the main array up we use the main array to output the report by looping through each day in the array and then looping through the inner arrays (transactions) stored in that day of the array. Then i grab the information stored in the inner array (which i could convert to a 6 item list if this would speed things up as its just simple text data) and output it on one line after another.
This works but it takes a LONG time because, first the array has to be built then it has to be looped through repeatedly to output the report.
Anyone have some ideas to speed up this report? Am I missing something obvious?
Cheers,
Brad
ASKER
Perhaps it would be useful if i post the code, so thats what I've done:
<cfset TodayOutputDate = DateAdd('H', 17, Now())>
<!--- see if there is a year url variable, if not then default to this year --->
<cfif IsDefined("Form.FormYear")
<cfif IsNumeric(Form.FormYear)>
<cfset GetYear = Form.FormYear>
<cfelse>
<!--- redirect straight to new-business.cfm --->
<cflocation url="report.cfm">
</cfif>
<cfelse>
<!--- default to this year --->
<cfset GetYear = DateFormat(TodayOutputDate
</cfif>
<cfif IsDefined("Form.FormMonth"
<cfif IsNumeric(Form.FormMonth)>
<cfset GetMonth = Form.FormMonth>
<cfelse>
<!--- redirect straight to new-business.cfm --->
<cflocation url="report.cfm">
</cfif>
<cfelse>
<!--- default to this month --->
<cfset GetMonth = DateFormat(TodayOutputDate
</cfif>
<!--- set the m variable for the array later --->
<cfset m = GetMonth>
<body>
<cfinclude template="inc-menu.cfm">
<br>
<table border="0" cellspacing="0" cellpadding="0">
<tr>
<td>
<STRONG>Report <cfoutput>as at #DateFormat(TodayOutputDat
<!--- what years to output? --->
<cfset FirstYear = 2004>
<cfset ThisYear = DateFormat(TodayOutputDate
<!--- drop down of years --->
<cfoutput>
<form style="display:inline; " name="theyear" action="report.cfm" method="post">
<select name="FormMonth">
<option value="1" <cfif GetMonth EQ 1>SELECTED</cfif>>January<
<option value="2" <cfif GetMonth EQ 2>SELECTED</cfif>>February
<option value="3" <cfif GetMonth EQ 3>SELECTED</cfif>>March</o
<option value="4" <cfif GetMonth EQ 4>SELECTED</cfif>>April</o
<option value="5" <cfif GetMonth EQ 5>SELECTED</cfif>>May</opt
<option value="6" <cfif GetMonth EQ 6>SELECTED</cfif>>June</op
<option value="7" <cfif GetMonth EQ 7>SELECTED</cfif>>July</op
<option value="8" <cfif GetMonth EQ 8>SELECTED</cfif>>August</
<option value="9" <cfif GetMonth EQ 9>SELECTED</cfif>>Septembe
<option value="10" <cfif GetMonth EQ 10>SELECTED</cfif>>October
<option value="11" <cfif GetMonth EQ 11>SELECTED</cfif>>Novembe
<option value="12" <cfif GetMonth EQ 12>SELECTED</cfif>>Decembe
</select>
<select name="FormYear">
<cfloop from="#FirstYear#" to="#ThisYear#" index="i">
<option value="#i#" <cfif i EQ GetYear>SELECTED</cfif>>#i
</cfloop>
</select>
<!--- onChange="document.theyear
<input type="submit" value="Submit Form">
</form>
</cfoutput>
- <input type="button" value="Print Page" onClick="window.print()">
</td>
</tr>
</table>
<CFQUERY NAME="Qry_ClientDetails" DATASOURCE="#DS#">
SELECT *, S.FullName AS StaffName,
month(c.ValuationFeesDate)
month(c.PropertySearchFees
month(c.LegalFeesDate) as LegalFeesDateMonth,
month(c.CompanySearchFeesD
month(c.OtherCostsDate) as OtherCostsDateMonth,
month(c.Fees4Date) as Fees4DateMonth,
month(c.Fees5Date) as Fees5DateMonth,
month(c.Fees6Date) as Fees6DateMonth,
month(c.Fees7Date) as Fees7DateMonth,
month(c.Fees8Date) as Fees8DateMonth,
month(c.Fees9Date) as Fees9DateMonth,
month(c.Fees10Date) as Fees10DateMonth,
month(c.PartPaymentDate1) as PartPaymentDate1Month,
month(c.PartPaymentDate2) as PartPaymentDate2Month,
month(c.PartPaymentDate3) as PartPaymentDate3Month,
month(c.PartPaymentDate4) as PartPaymentDate4Month,
month(c.PartPaymentDate5) as PartPaymentDate5Month,
month(c.PartPaymentDate6) as PartPaymentDate6Month,
month(c.PartPaymentDate7) as PartPaymentDate7Month,
month(c.PartPaymentDate8) as PartPaymentDate8Month,
month(c.PartPaymentDate9) as PartPaymentDate9Month,
month(c.PartPaymentDate10)
month(c.DateOfPrincipalAdv
day(c.ValuationFeesDate) as ValuationFeesDateDay,
day(c.PropertySearchFeesDa
day(c.LegalFeesDate) as LegalFeesDateDay,
day(c.CompanySearchFeesDat
day(c.OtherCostsDate) as OtherCostsDateDay,
day(c.Fees4Date) as Fees4DateDay,
day(c.Fees5Date) as Fees5DateDay,
day(c.Fees6Date) as Fees6DateDay,
day(c.Fees7Date) as Fees7DateDay,
day(c.Fees8Date) as Fees8DateDay,
day(c.Fees9Date) as Fees9DateDay,
day(c.Fees10Date) as Fees10DateDay,
day(c.PartPaymentDate1) as PartPaymentDate1Day,
day(c.PartPaymentDate2) as PartPaymentDate2Day,
day(c.PartPaymentDate3) as PartPaymentDate3Day,
day(c.PartPaymentDate4) as PartPaymentDate4Day,
day(c.PartPaymentDate5) as PartPaymentDate5Day,
day(c.PartPaymentDate6) as PartPaymentDate6Day,
day(c.PartPaymentDate7) as PartPaymentDate7Day,
day(c.PartPaymentDate8) as PartPaymentDate8Day,
day(c.PartPaymentDate9) as PartPaymentDate9Day,
day(c.PartPaymentDate10) as PartPaymentDate10Day,
day(c.DateOfPrincipalAdvan
FROM ClientDetails C
INNER JOIN Staff S
On C.StaffID = S.StaffID
WHERE (year(C.ValuationFeesDate)
OR (year(C.PropertySearchFees
OR (year(C.LegalFeesDate) = #GetYear# AND month(C.LegalFeesDate) = #m#)
OR (year(C.CompanySearchFeesD
OR (year(C.OtherCostsDate) = #GetYear# AND month(C.OtherCostsDate) = #m#)
OR (year(C.Fees4Date) = #GetYear# AND month(C.Fees4Date) = #m#)
OR (year(C.Fees5Date) = #GetYear# AND month(C.Fees5Date) = #m#)
OR (year(C.Fees6Date) = #GetYear# AND month(C.Fees6Date) = #m#)
OR (year(C.Fees7Date) = #GetYear# AND month(C.Fees7Date) = #m#)
OR (year(C.Fees8Date) = #GetYear# AND month(C.Fees8Date) = #m#)
OR (year(C.Fees9Date) = #GetYear# AND month(C.Fees9Date) = #m#)
OR (year(C.Fees10Date) = #GetYear# AND month(C.Fees10Date) = #m#)
OR (year(C.PartPaymentDate1) = #GetYear# AND month(C.PartPaymentDate1) = #m#)
OR (year(C.PartPaymentDate2) = #GetYear# AND month(C.PartPaymentDate2) = #m#)
OR (year(C.PartPaymentDate3) = #GetYear# AND month(C.PartPaymentDate3) = #m#)
OR (year(C.PartPaymentDate4) = #GetYear# AND month(C.PartPaymentDate4) = #m#)
OR (year(C.PartPaymentDate5) = #GetYear# AND month(C.PartPaymentDate5) = #m#)
OR (year(C.PartPaymentDate6) = #GetYear# AND month(C.PartPaymentDate6) = #m#)
OR (year(C.PartPaymentDate7) = #GetYear# AND month(C.PartPaymentDate7) = #m#)
OR (year(C.PartPaymentDate8) = #GetYear# AND month(C.PartPaymentDate8) = #m#)
OR (year(C.PartPaymentDate9) = #GetYear# AND month(C.PartPaymentDate9) = #m#)
OR (year(C.PartPaymentDate10)
OR (year(C.DateOfPrincipalAdv
</CFQUERY>
<cfloop query="Qry_ClientDetails">
<!--- create the array --->
<cfset BankArray = #ArrayNew(3)#>
<!--- loop through the months --->
<cfset ThisMonth = createDate(getYear, m, 1)>
<cfset numOfDaysInThisMonth = daysInMonth(ThisMonth)>
<cfset MonthName = monthAsString(m)>
<cfquery name="Qry_GetThisMonth" dbtype="query">
SELECT *
FROM Qry_ClientDetails
</cfquery>
<!--- loop through the days --->
<cfloop from="1" to="#numOfDaysInThisMonth#
<cfquery name="GetThisDay" dbtype="query">
SELECT ClientID,
FullName,
StaffName,
ValuationFees,
PropertySearchFees,
LegalFees,
CompanySearchFees,
OtherCosts,
Fees4Amount,
Fees5Amount,
Fees6Amount,
Fees7Amount,
Fees8Amount,
Fees9Amount,
Fees10Amount,
PartPayment1,
PartPayment2,
PartPayment3,
PartPayment4,
PartPayment5,
PartPayment6,
PartPayment7,
PartPayment8,
PartPayment9,
PartPayment10,
PrincipalAdvance,
ValuationFeesDate,
PropertySearchFeesDate,
LegalFeesDate,
CompanySearchFeesDate,
OtherCostsDate,
Fees4Date,
Fees5Date,
Fees6Date,
Fees7Date,
Fees8Date,
Fees9Date,
Fees10Date,
PartPaymentDate1,
PartPaymentDate2,
PartPaymentDate3,
PartPaymentDate4,
PartPaymentDate5,
PartPaymentDate6,
PartPaymentDate7,
PartPaymentDate8,
PartPaymentDate9,
PartPaymentDate10,
DateOfPrincipalAdvance,
ValuationFeeNotes,
PropertySearchNotes,
LegalFee1Notes,
LegalFee2Notes,
LegalFee3Notes,
Fees4Notes,
Fees5Notes,
Fees6Notes,
Fees7Notes,
Fees8Notes,
Fees9Notes,
Fees10Notes,
PartPaymentNotes1,
PartPaymentNotes2,
PartPaymentNotes3,
PartPaymentNotes4,
PartPaymentNotes5,
PartPaymentNotes6,
PartPaymentNotes7,
PartPaymentNotes8,
PartPaymentNotes9,
PartPaymentNotes10
FROM Qry_GetThisMonth
WHERE
ValuationFeesDateDay = #d#
OR PropertySearchFeesDateDay = #d#
OR LegalFeesDateDay = #d#
OR CompanySearchFeesDateDay = #d#
OR OtherCostsDateDay = #d#
OR Fees4DateDay = #d#
OR Fees5DateDay = #d#
OR Fees6DateDay = #d#
OR Fees7DateDay = #d#
OR Fees8DateDay = #d#
OR Fees9DateDay = #d#
OR Fees10DateDay = #d#
OR PartPaymentDate1Day = #d#
OR PartPaymentDate2Day = #d#
OR PartPaymentDate3Day = #d#
OR PartPaymentDate4Day = #d#
OR PartPaymentDate5Day = #d#
OR PartPaymentDate6Day = #d#
OR PartPaymentDate7Day = #d#
OR PartPaymentDate8Day = #d#
OR PartPaymentDate9Day = #d#
OR PartPaymentDate10Day = #d#
OR DateOfPrincipalAdvanceDay = #d#
</cfquery>
<cfoutput query="GetThisDay">
<cfset CheckListNames = "ValuationFees,PropertySea
<cfset CheckListAmountsArray = #ArrayNew(1)#>
<cfset CheckListAmountsArray[1] = #GetThisDay.ValuationFees#
<cfset CheckListAmountsArray[2] = #GetThisDay.PropertySearch
<cfset CheckListAmountsArray[3] = #GetThisDay.LegalFees#>
<cfset CheckListAmountsArray[4] = #GetThisDay.CompanySearchF
<cfset CheckListAmountsArray[5] = #GetThisDay.OtherCosts#>
<cfset CheckListAmountsArray[6] = #GetThisDay.Fees4Amount#>
<cfset CheckListAmountsArray[7] = #GetThisDay.Fees5Amount#>
<cfset CheckListAmountsArray[8] = #GetThisDay.Fees6Amount#>
<cfset CheckListAmountsArray[9] = #GetThisDay.Fees7Amount#>
<cfset CheckListAmountsArray[10] = #GetThisDay.Fees8Amount#>
<cfset CheckListAmountsArray[11] = #GetThisDay.Fees9Amount#>
<cfset CheckListAmountsArray[12] = #GetThisDay.Fees10Amount#>
<cfset CheckListAmountsArray[13] = #GetThisDay.PartPayment1#>
<cfset CheckListAmountsArray[14] = #GetThisDay.PartPayment2#>
<cfset CheckListAmountsArray[15] = #GetThisDay.PartPayment3#>
<cfset CheckListAmountsArray[16] = #GetThisDay.PartPayment4#>
<cfset CheckListAmountsArray[17] = #GetThisDay.PartPayment5#>
<cfset CheckListAmountsArray[18] = #GetThisDay.PartPayment6#>
<cfset CheckListAmountsArray[19] = #GetThisDay.PartPayment7#>
<cfset CheckListAmountsArray[20] = #GetThisDay.PartPayment8#>
<cfset CheckListAmountsArray[21] = #GetThisDay.PartPayment9#>
<cfset CheckListAmountsArray[22] = #GetThisDay.PartPayment10#
<cfset CheckListAmountsArray[23] = #GetThisDay.PrincipalAdvan
<cfset CheckListDatesArray = #ArrayNew(1)#>
<cfset CheckListDatesArray[1] = #GetThisDay.ValuationFeesD
<cfset CheckListDatesArray[2] = #GetThisDay.PropertySearch
<cfset CheckListDatesArray[3] = #GetThisDay.LegalFeesDate#
<cfset CheckListDatesArray[4] = #GetThisDay.CompanySearchF
<cfset CheckListDatesArray[5] = #GetThisDay.OtherCostsDate
<cfset CheckListDatesArray[6] = #GetThisDay.Fees4Date#>
<cfset CheckListDatesArray[7] = #GetThisDay.Fees5Date#>
<cfset CheckListDatesArray[8] = #GetThisDay.Fees6Date#>
<cfset CheckListDatesArray[9] = #GetThisDay.Fees7Date#>
<cfset CheckListDatesArray[10] = #GetThisDay.Fees8Date#>
<cfset CheckListDatesArray[11] = #GetThisDay.Fees9Date#>
<cfset CheckListDatesArray[12] = #GetThisDay.Fees10Date#>
<cfset CheckListDatesArray[13] = #GetThisDay.PartPaymentDat
<cfset CheckListDatesArray[14] = #GetThisDay.PartPaymentDat
<cfset CheckListDatesArray[15] = #GetThisDay.PartPaymentDat
<cfset CheckListDatesArray[16] = #GetThisDay.PartPaymentDat
<cfset CheckListDatesArray[17] = #GetThisDay.PartPaymentDat
<cfset CheckListDatesArray[18] = #GetThisDay.PartPaymentDat
<cfset CheckListDatesArray[19] = #GetThisDay.PartPaymentDat
<cfset CheckListDatesArray[20] = #GetThisDay.PartPaymentDat
<cfset CheckListDatesArray[21] = #GetThisDay.PartPaymentDat
<cfset CheckListDatesArray[22] = #GetThisDay.PartPaymentDat
<cfset CheckListDatesArray[23] = #GetThisDay.DateOfPrincipa
<cfset CheckListNotesArray = #ArrayNew(1)#>
<cfset CheckListNotesArray[1] = #GetThisDay.ValuationFeeNo
<cfset CheckListNotesArray[2] = #GetThisDay.PropertySearch
<cfset CheckListNotesArray[3] = #GetThisDay.LegalFee1Notes
<cfset CheckListNotesArray[4] = #GetThisDay.LegalFee2Notes
<cfset CheckListNotesArray[5] = #GetThisDay.LegalFee3Notes
<cfset CheckListNotesArray[6] = #GetThisDay.Fees4Notes#>
<cfset CheckListNotesArray[7] = #GetThisDay.Fees5Notes#>
<cfset CheckListNotesArray[8] = #GetThisDay.Fees6Notes#>
<cfset CheckListNotesArray[9] = #GetThisDay.Fees7Notes#>
<cfset CheckListNotesArray[10] = #GetThisDay.Fees8Notes#>
<cfset CheckListNotesArray[11] = #GetThisDay.Fees9Notes#>
<cfset CheckListNotesArray[12] = #GetThisDay.Fees10Notes#>
<cfset CheckListNotesArray[13] = #GetThisDay.PartPaymentNot
<cfset CheckListNotesArray[14] = #GetThisDay.PartPaymentNot
<cfset CheckListNotesArray[15] = #GetThisDay.PartPaymentNot
<cfset CheckListNotesArray[16] = #GetThisDay.PartPaymentNot
<cfset CheckListNotesArray[17] = #GetThisDay.PartPaymentNot
<cfset CheckListNotesArray[18] = #GetThisDay.PartPaymentNot
<cfset CheckListNotesArray[19] = #GetThisDay.PartPaymentNot
<cfset CheckListNotesArray[20] = #GetThisDay.PartPaymentNot
<cfset CheckListNotesArray[21] = #GetThisDay.PartPaymentNot
<cfset CheckListNotesArray[22] = #GetThisDay.PartPaymentNot
<cfset CheckListNotesArray[23] = "">
<cfset LoopCounter = 0>
<cfloop list="#CheckListNames#" index="i">
<cfset LoopCounter = LoopCounter + 1>
<!--- set value to be checked - the evaluate code will actually retrieve the data in GetThisDay.whatever (for example, GetThisDay.ValuationFees) --->
<cfset CheckValue = #evaluate("GetThisDay.#i#"
<cfif CheckValue NEQ 0 AND CheckValue NEQ "">
<cfif CheckListDatesArray[LoopCo
<cfif day(CheckListDatesArray[Lo
<!--- there is something in this fee so put details into array --->
<!--- find what type of fee this is first --->
<cfif FindNoCase("PartPayment", i)>
<!--- this is a partpayment --->
<cfset FeeType = 3>
<cfelseif FindNoCase("PrincipalAdvan
<cfset FeeType = 1>
<cfelse>
<cfset FeeType = 2>
</cfif>
<cfset ListDetails = ArrayNew(1)>
<cfset ListDetails[1] = #GetThisDay.FullName#>
<cfset ListDetails[2] = #GetThisDay.StaffName#>
<cfset ListDetails[3] = #FeeType#>
<cfset ListDetails[4] = #CheckListAmountsArray[Loo
<cfset ListDetails[5] = #DateFormat(CheckListDates
<cfset ListDetails[6] = #CheckListNotesArray[LoopC
<cfset tempVar = #ArrayAppend(BankArray[m][
</cfif>
</cfif>
</cfif>
</cfloop>
</cfoutput>
</cfloop>
</cfloop>
<cfset TheMonthName = monthAsString(m)>
<cfset RowDisplay = "RowOne">
<table width="100%" border="0" cellspacing="0" cellpadding="6">
<tr>
<td colspan="7" align="right" bgcolor="#C9FBC4"><strong>
</tr>
<cfif IsDefined("BankArray")>
<tr>
<td><strong>Advisor</stron
<td><strong>Full Name</strong></td>
<td><strong>Entry Type</strong></td>
<td><strong>In</strong></t
<td><strong>Out</strong></
<td align="right"><strong>Tran
<td><strong>Notes</strong>
</tr>
<cfloop from="1" to ="#ArrayLen(BankArray[m])#
<cfloop from="1" to ="#ArrayLen(BankArray[m][O
<cfif IsArray(BankArray[m][Outpu
<cfset TempInnerLoopArray = BankArray[m][OutputOuterIn
<tr bgcolor="<cfif RowDisplay EQ "RowOne">white<cfset RowDisplay = "RowTwo"><cfelse>##E6FFE4<
<td>#TempInnerLoopArray[2]
<td>#TempInnerLoopArray[1]
<td>
<cfswitch expression="#TempInnerLoop
<cfcase value="1">Principal Advance</cfcase>
<cfcase value="2">Costs and Disb</cfcase>
<cfcase value="3">Payment</cfcase>
</cfswitch>
</td>
<td>
<cfif #TempInnerLoopArray[3]# EQ 3>
$#NumberFormat(TempInnerLo
<cfset TotalBankIn = TotalBankIn + TempInnerLoopArray[4]>
</cfif>
</td>
<td>
<cfif #TempInnerLoopArray[3]# NEQ 3>
$#NumberFormat(TempInnerLo
<cfset TotalBankOut = TotalBankOut + TempInnerLoopArray[4]>
</cfif>
</td>
<td align="right">#TempInnerLo
<td>#TempInnerLoopArray[6]
</tr>
</cfif>
</cfloop>
</cfloop>
<cfelse>
<tr>
<td colspan="7">No Data Available</td>
</tr>
</cfif>
</table>
<!--- <cfdump var="#BankArray#"> --->
Does that help?
Thanks,
Brad
<cfset TodayOutputDate = DateAdd('H', 17, Now())>
<!--- see if there is a year url variable, if not then default to this year --->
<cfif IsDefined("Form.FormYear")
<cfif IsNumeric(Form.FormYear)>
<cfset GetYear = Form.FormYear>
<cfelse>
<!--- redirect straight to new-business.cfm --->
<cflocation url="report.cfm">
</cfif>
<cfelse>
<!--- default to this year --->
<cfset GetYear = DateFormat(TodayOutputDate
</cfif>
<cfif IsDefined("Form.FormMonth"
<cfif IsNumeric(Form.FormMonth)>
<cfset GetMonth = Form.FormMonth>
<cfelse>
<!--- redirect straight to new-business.cfm --->
<cflocation url="report.cfm">
</cfif>
<cfelse>
<!--- default to this month --->
<cfset GetMonth = DateFormat(TodayOutputDate
</cfif>
<!--- set the m variable for the array later --->
<cfset m = GetMonth>
<body>
<cfinclude template="inc-menu.cfm">
<br>
<table border="0" cellspacing="0" cellpadding="0">
<tr>
<td>
<STRONG>Report <cfoutput>as at #DateFormat(TodayOutputDat
<!--- what years to output? --->
<cfset FirstYear = 2004>
<cfset ThisYear = DateFormat(TodayOutputDate
<!--- drop down of years --->
<cfoutput>
<form style="display:inline; " name="theyear" action="report.cfm" method="post">
<select name="FormMonth">
<option value="1" <cfif GetMonth EQ 1>SELECTED</cfif>>January<
<option value="2" <cfif GetMonth EQ 2>SELECTED</cfif>>February
<option value="3" <cfif GetMonth EQ 3>SELECTED</cfif>>March</o
<option value="4" <cfif GetMonth EQ 4>SELECTED</cfif>>April</o
<option value="5" <cfif GetMonth EQ 5>SELECTED</cfif>>May</opt
<option value="6" <cfif GetMonth EQ 6>SELECTED</cfif>>June</op
<option value="7" <cfif GetMonth EQ 7>SELECTED</cfif>>July</op
<option value="8" <cfif GetMonth EQ 8>SELECTED</cfif>>August</
<option value="9" <cfif GetMonth EQ 9>SELECTED</cfif>>Septembe
<option value="10" <cfif GetMonth EQ 10>SELECTED</cfif>>October
<option value="11" <cfif GetMonth EQ 11>SELECTED</cfif>>Novembe
<option value="12" <cfif GetMonth EQ 12>SELECTED</cfif>>Decembe
</select>
<select name="FormYear">
<cfloop from="#FirstYear#" to="#ThisYear#" index="i">
<option value="#i#" <cfif i EQ GetYear>SELECTED</cfif>>#i
</cfloop>
</select>
<!--- onChange="document.theyear
<input type="submit" value="Submit Form">
</form>
</cfoutput>
- <input type="button" value="Print Page" onClick="window.print()">
</td>
</tr>
</table>
<CFQUERY NAME="Qry_ClientDetails" DATASOURCE="#DS#">
SELECT *, S.FullName AS StaffName,
month(c.ValuationFeesDate)
month(c.PropertySearchFees
month(c.LegalFeesDate) as LegalFeesDateMonth,
month(c.CompanySearchFeesD
month(c.OtherCostsDate) as OtherCostsDateMonth,
month(c.Fees4Date) as Fees4DateMonth,
month(c.Fees5Date) as Fees5DateMonth,
month(c.Fees6Date) as Fees6DateMonth,
month(c.Fees7Date) as Fees7DateMonth,
month(c.Fees8Date) as Fees8DateMonth,
month(c.Fees9Date) as Fees9DateMonth,
month(c.Fees10Date) as Fees10DateMonth,
month(c.PartPaymentDate1) as PartPaymentDate1Month,
month(c.PartPaymentDate2) as PartPaymentDate2Month,
month(c.PartPaymentDate3) as PartPaymentDate3Month,
month(c.PartPaymentDate4) as PartPaymentDate4Month,
month(c.PartPaymentDate5) as PartPaymentDate5Month,
month(c.PartPaymentDate6) as PartPaymentDate6Month,
month(c.PartPaymentDate7) as PartPaymentDate7Month,
month(c.PartPaymentDate8) as PartPaymentDate8Month,
month(c.PartPaymentDate9) as PartPaymentDate9Month,
month(c.PartPaymentDate10)
month(c.DateOfPrincipalAdv
day(c.ValuationFeesDate) as ValuationFeesDateDay,
day(c.PropertySearchFeesDa
day(c.LegalFeesDate) as LegalFeesDateDay,
day(c.CompanySearchFeesDat
day(c.OtherCostsDate) as OtherCostsDateDay,
day(c.Fees4Date) as Fees4DateDay,
day(c.Fees5Date) as Fees5DateDay,
day(c.Fees6Date) as Fees6DateDay,
day(c.Fees7Date) as Fees7DateDay,
day(c.Fees8Date) as Fees8DateDay,
day(c.Fees9Date) as Fees9DateDay,
day(c.Fees10Date) as Fees10DateDay,
day(c.PartPaymentDate1) as PartPaymentDate1Day,
day(c.PartPaymentDate2) as PartPaymentDate2Day,
day(c.PartPaymentDate3) as PartPaymentDate3Day,
day(c.PartPaymentDate4) as PartPaymentDate4Day,
day(c.PartPaymentDate5) as PartPaymentDate5Day,
day(c.PartPaymentDate6) as PartPaymentDate6Day,
day(c.PartPaymentDate7) as PartPaymentDate7Day,
day(c.PartPaymentDate8) as PartPaymentDate8Day,
day(c.PartPaymentDate9) as PartPaymentDate9Day,
day(c.PartPaymentDate10) as PartPaymentDate10Day,
day(c.DateOfPrincipalAdvan
FROM ClientDetails C
INNER JOIN Staff S
On C.StaffID = S.StaffID
WHERE (year(C.ValuationFeesDate)
OR (year(C.PropertySearchFees
OR (year(C.LegalFeesDate) = #GetYear# AND month(C.LegalFeesDate) = #m#)
OR (year(C.CompanySearchFeesD
OR (year(C.OtherCostsDate) = #GetYear# AND month(C.OtherCostsDate) = #m#)
OR (year(C.Fees4Date) = #GetYear# AND month(C.Fees4Date) = #m#)
OR (year(C.Fees5Date) = #GetYear# AND month(C.Fees5Date) = #m#)
OR (year(C.Fees6Date) = #GetYear# AND month(C.Fees6Date) = #m#)
OR (year(C.Fees7Date) = #GetYear# AND month(C.Fees7Date) = #m#)
OR (year(C.Fees8Date) = #GetYear# AND month(C.Fees8Date) = #m#)
OR (year(C.Fees9Date) = #GetYear# AND month(C.Fees9Date) = #m#)
OR (year(C.Fees10Date) = #GetYear# AND month(C.Fees10Date) = #m#)
OR (year(C.PartPaymentDate1) = #GetYear# AND month(C.PartPaymentDate1) = #m#)
OR (year(C.PartPaymentDate2) = #GetYear# AND month(C.PartPaymentDate2) = #m#)
OR (year(C.PartPaymentDate3) = #GetYear# AND month(C.PartPaymentDate3) = #m#)
OR (year(C.PartPaymentDate4) = #GetYear# AND month(C.PartPaymentDate4) = #m#)
OR (year(C.PartPaymentDate5) = #GetYear# AND month(C.PartPaymentDate5) = #m#)
OR (year(C.PartPaymentDate6) = #GetYear# AND month(C.PartPaymentDate6) = #m#)
OR (year(C.PartPaymentDate7) = #GetYear# AND month(C.PartPaymentDate7) = #m#)
OR (year(C.PartPaymentDate8) = #GetYear# AND month(C.PartPaymentDate8) = #m#)
OR (year(C.PartPaymentDate9) = #GetYear# AND month(C.PartPaymentDate9) = #m#)
OR (year(C.PartPaymentDate10)
OR (year(C.DateOfPrincipalAdv
</CFQUERY>
<cfloop query="Qry_ClientDetails">
<!--- create the array --->
<cfset BankArray = #ArrayNew(3)#>
<!--- loop through the months --->
<cfset ThisMonth = createDate(getYear, m, 1)>
<cfset numOfDaysInThisMonth = daysInMonth(ThisMonth)>
<cfset MonthName = monthAsString(m)>
<cfquery name="Qry_GetThisMonth" dbtype="query">
SELECT *
FROM Qry_ClientDetails
</cfquery>
<!--- loop through the days --->
<cfloop from="1" to="#numOfDaysInThisMonth#
<cfquery name="GetThisDay" dbtype="query">
SELECT ClientID,
FullName,
StaffName,
ValuationFees,
PropertySearchFees,
LegalFees,
CompanySearchFees,
OtherCosts,
Fees4Amount,
Fees5Amount,
Fees6Amount,
Fees7Amount,
Fees8Amount,
Fees9Amount,
Fees10Amount,
PartPayment1,
PartPayment2,
PartPayment3,
PartPayment4,
PartPayment5,
PartPayment6,
PartPayment7,
PartPayment8,
PartPayment9,
PartPayment10,
PrincipalAdvance,
ValuationFeesDate,
PropertySearchFeesDate,
LegalFeesDate,
CompanySearchFeesDate,
OtherCostsDate,
Fees4Date,
Fees5Date,
Fees6Date,
Fees7Date,
Fees8Date,
Fees9Date,
Fees10Date,
PartPaymentDate1,
PartPaymentDate2,
PartPaymentDate3,
PartPaymentDate4,
PartPaymentDate5,
PartPaymentDate6,
PartPaymentDate7,
PartPaymentDate8,
PartPaymentDate9,
PartPaymentDate10,
DateOfPrincipalAdvance,
ValuationFeeNotes,
PropertySearchNotes,
LegalFee1Notes,
LegalFee2Notes,
LegalFee3Notes,
Fees4Notes,
Fees5Notes,
Fees6Notes,
Fees7Notes,
Fees8Notes,
Fees9Notes,
Fees10Notes,
PartPaymentNotes1,
PartPaymentNotes2,
PartPaymentNotes3,
PartPaymentNotes4,
PartPaymentNotes5,
PartPaymentNotes6,
PartPaymentNotes7,
PartPaymentNotes8,
PartPaymentNotes9,
PartPaymentNotes10
FROM Qry_GetThisMonth
WHERE
ValuationFeesDateDay = #d#
OR PropertySearchFeesDateDay = #d#
OR LegalFeesDateDay = #d#
OR CompanySearchFeesDateDay = #d#
OR OtherCostsDateDay = #d#
OR Fees4DateDay = #d#
OR Fees5DateDay = #d#
OR Fees6DateDay = #d#
OR Fees7DateDay = #d#
OR Fees8DateDay = #d#
OR Fees9DateDay = #d#
OR Fees10DateDay = #d#
OR PartPaymentDate1Day = #d#
OR PartPaymentDate2Day = #d#
OR PartPaymentDate3Day = #d#
OR PartPaymentDate4Day = #d#
OR PartPaymentDate5Day = #d#
OR PartPaymentDate6Day = #d#
OR PartPaymentDate7Day = #d#
OR PartPaymentDate8Day = #d#
OR PartPaymentDate9Day = #d#
OR PartPaymentDate10Day = #d#
OR DateOfPrincipalAdvanceDay = #d#
</cfquery>
<cfoutput query="GetThisDay">
<cfset CheckListNames = "ValuationFees,PropertySea
<cfset CheckListAmountsArray = #ArrayNew(1)#>
<cfset CheckListAmountsArray[1] = #GetThisDay.ValuationFees#
<cfset CheckListAmountsArray[2] = #GetThisDay.PropertySearch
<cfset CheckListAmountsArray[3] = #GetThisDay.LegalFees#>
<cfset CheckListAmountsArray[4] = #GetThisDay.CompanySearchF
<cfset CheckListAmountsArray[5] = #GetThisDay.OtherCosts#>
<cfset CheckListAmountsArray[6] = #GetThisDay.Fees4Amount#>
<cfset CheckListAmountsArray[7] = #GetThisDay.Fees5Amount#>
<cfset CheckListAmountsArray[8] = #GetThisDay.Fees6Amount#>
<cfset CheckListAmountsArray[9] = #GetThisDay.Fees7Amount#>
<cfset CheckListAmountsArray[10] = #GetThisDay.Fees8Amount#>
<cfset CheckListAmountsArray[11] = #GetThisDay.Fees9Amount#>
<cfset CheckListAmountsArray[12] = #GetThisDay.Fees10Amount#>
<cfset CheckListAmountsArray[13] = #GetThisDay.PartPayment1#>
<cfset CheckListAmountsArray[14] = #GetThisDay.PartPayment2#>
<cfset CheckListAmountsArray[15] = #GetThisDay.PartPayment3#>
<cfset CheckListAmountsArray[16] = #GetThisDay.PartPayment4#>
<cfset CheckListAmountsArray[17] = #GetThisDay.PartPayment5#>
<cfset CheckListAmountsArray[18] = #GetThisDay.PartPayment6#>
<cfset CheckListAmountsArray[19] = #GetThisDay.PartPayment7#>
<cfset CheckListAmountsArray[20] = #GetThisDay.PartPayment8#>
<cfset CheckListAmountsArray[21] = #GetThisDay.PartPayment9#>
<cfset CheckListAmountsArray[22] = #GetThisDay.PartPayment10#
<cfset CheckListAmountsArray[23] = #GetThisDay.PrincipalAdvan
<cfset CheckListDatesArray = #ArrayNew(1)#>
<cfset CheckListDatesArray[1] = #GetThisDay.ValuationFeesD
<cfset CheckListDatesArray[2] = #GetThisDay.PropertySearch
<cfset CheckListDatesArray[3] = #GetThisDay.LegalFeesDate#
<cfset CheckListDatesArray[4] = #GetThisDay.CompanySearchF
<cfset CheckListDatesArray[5] = #GetThisDay.OtherCostsDate
<cfset CheckListDatesArray[6] = #GetThisDay.Fees4Date#>
<cfset CheckListDatesArray[7] = #GetThisDay.Fees5Date#>
<cfset CheckListDatesArray[8] = #GetThisDay.Fees6Date#>
<cfset CheckListDatesArray[9] = #GetThisDay.Fees7Date#>
<cfset CheckListDatesArray[10] = #GetThisDay.Fees8Date#>
<cfset CheckListDatesArray[11] = #GetThisDay.Fees9Date#>
<cfset CheckListDatesArray[12] = #GetThisDay.Fees10Date#>
<cfset CheckListDatesArray[13] = #GetThisDay.PartPaymentDat
<cfset CheckListDatesArray[14] = #GetThisDay.PartPaymentDat
<cfset CheckListDatesArray[15] = #GetThisDay.PartPaymentDat
<cfset CheckListDatesArray[16] = #GetThisDay.PartPaymentDat
<cfset CheckListDatesArray[17] = #GetThisDay.PartPaymentDat
<cfset CheckListDatesArray[18] = #GetThisDay.PartPaymentDat
<cfset CheckListDatesArray[19] = #GetThisDay.PartPaymentDat
<cfset CheckListDatesArray[20] = #GetThisDay.PartPaymentDat
<cfset CheckListDatesArray[21] = #GetThisDay.PartPaymentDat
<cfset CheckListDatesArray[22] = #GetThisDay.PartPaymentDat
<cfset CheckListDatesArray[23] = #GetThisDay.DateOfPrincipa
<cfset CheckListNotesArray = #ArrayNew(1)#>
<cfset CheckListNotesArray[1] = #GetThisDay.ValuationFeeNo
<cfset CheckListNotesArray[2] = #GetThisDay.PropertySearch
<cfset CheckListNotesArray[3] = #GetThisDay.LegalFee1Notes
<cfset CheckListNotesArray[4] = #GetThisDay.LegalFee2Notes
<cfset CheckListNotesArray[5] = #GetThisDay.LegalFee3Notes
<cfset CheckListNotesArray[6] = #GetThisDay.Fees4Notes#>
<cfset CheckListNotesArray[7] = #GetThisDay.Fees5Notes#>
<cfset CheckListNotesArray[8] = #GetThisDay.Fees6Notes#>
<cfset CheckListNotesArray[9] = #GetThisDay.Fees7Notes#>
<cfset CheckListNotesArray[10] = #GetThisDay.Fees8Notes#>
<cfset CheckListNotesArray[11] = #GetThisDay.Fees9Notes#>
<cfset CheckListNotesArray[12] = #GetThisDay.Fees10Notes#>
<cfset CheckListNotesArray[13] = #GetThisDay.PartPaymentNot
<cfset CheckListNotesArray[14] = #GetThisDay.PartPaymentNot
<cfset CheckListNotesArray[15] = #GetThisDay.PartPaymentNot
<cfset CheckListNotesArray[16] = #GetThisDay.PartPaymentNot
<cfset CheckListNotesArray[17] = #GetThisDay.PartPaymentNot
<cfset CheckListNotesArray[18] = #GetThisDay.PartPaymentNot
<cfset CheckListNotesArray[19] = #GetThisDay.PartPaymentNot
<cfset CheckListNotesArray[20] = #GetThisDay.PartPaymentNot
<cfset CheckListNotesArray[21] = #GetThisDay.PartPaymentNot
<cfset CheckListNotesArray[22] = #GetThisDay.PartPaymentNot
<cfset CheckListNotesArray[23] = "">
<cfset LoopCounter = 0>
<cfloop list="#CheckListNames#" index="i">
<cfset LoopCounter = LoopCounter + 1>
<!--- set value to be checked - the evaluate code will actually retrieve the data in GetThisDay.whatever (for example, GetThisDay.ValuationFees) --->
<cfset CheckValue = #evaluate("GetThisDay.#i#"
<cfif CheckValue NEQ 0 AND CheckValue NEQ "">
<cfif CheckListDatesArray[LoopCo
<cfif day(CheckListDatesArray[Lo
<!--- there is something in this fee so put details into array --->
<!--- find what type of fee this is first --->
<cfif FindNoCase("PartPayment", i)>
<!--- this is a partpayment --->
<cfset FeeType = 3>
<cfelseif FindNoCase("PrincipalAdvan
<cfset FeeType = 1>
<cfelse>
<cfset FeeType = 2>
</cfif>
<cfset ListDetails = ArrayNew(1)>
<cfset ListDetails[1] = #GetThisDay.FullName#>
<cfset ListDetails[2] = #GetThisDay.StaffName#>
<cfset ListDetails[3] = #FeeType#>
<cfset ListDetails[4] = #CheckListAmountsArray[Loo
<cfset ListDetails[5] = #DateFormat(CheckListDates
<cfset ListDetails[6] = #CheckListNotesArray[LoopC
<cfset tempVar = #ArrayAppend(BankArray[m][
</cfif>
</cfif>
</cfif>
</cfloop>
</cfoutput>
</cfloop>
</cfloop>
<cfset TheMonthName = monthAsString(m)>
<cfset RowDisplay = "RowOne">
<table width="100%" border="0" cellspacing="0" cellpadding="6">
<tr>
<td colspan="7" align="right" bgcolor="#C9FBC4"><strong>
</tr>
<cfif IsDefined("BankArray")>
<tr>
<td><strong>Advisor</stron
<td><strong>Full Name</strong></td>
<td><strong>Entry Type</strong></td>
<td><strong>In</strong></t
<td><strong>Out</strong></
<td align="right"><strong>Tran
<td><strong>Notes</strong>
</tr>
<cfloop from="1" to ="#ArrayLen(BankArray[m])#
<cfloop from="1" to ="#ArrayLen(BankArray[m][O
<cfif IsArray(BankArray[m][Outpu
<cfset TempInnerLoopArray = BankArray[m][OutputOuterIn
<tr bgcolor="<cfif RowDisplay EQ "RowOne">white<cfset RowDisplay = "RowTwo"><cfelse>##E6FFE4<
<td>#TempInnerLoopArray[2]
<td>#TempInnerLoopArray[1]
<td>
<cfswitch expression="#TempInnerLoop
<cfcase value="1">Principal Advance</cfcase>
<cfcase value="2">Costs and Disb</cfcase>
<cfcase value="3">Payment</cfcase>
</cfswitch>
</td>
<td>
<cfif #TempInnerLoopArray[3]# EQ 3>
$#NumberFormat(TempInnerLo
<cfset TotalBankIn = TotalBankIn + TempInnerLoopArray[4]>
</cfif>
</td>
<td>
<cfif #TempInnerLoopArray[3]# NEQ 3>
$#NumberFormat(TempInnerLo
<cfset TotalBankOut = TotalBankOut + TempInnerLoopArray[4]>
</cfif>
</td>
<td align="right">#TempInnerLo
<td>#TempInnerLoopArray[6]
</tr>
</cfif>
</cfloop>
</cfloop>
<cfelse>
<tr>
<td colspan="7">No Data Available</td>
</tr>
</cfif>
</table>
<!--- <cfdump var="#BankArray#"> --->
Does that help?
Thanks,
Brad
bradderick,
Sorry not really.
What you can do is a <cfdump var="#now()#" />
after each section.
Then have a look at the page, to see all the times. This will help you establish which bit is the problem.
Regards
Plucka
Sorry not really.
What you can do is a <cfdump var="#now()#" />
after each section.
Then have a look at the page, to see all the times. This will help you establish which bit is the problem.
Regards
Plucka
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Guys,
Thanks for the comments. It looks like ExpertAdmin's comment is the most useful and it has helped me to speed the process up.
Much appreciated and sorry about the delay in getting back to you.
Cheers,
Brad
Thanks for the comments. It looks like ExpertAdmin's comment is the most useful and it has helped me to speed the process up.
Much appreciated and sorry about the delay in getting back to you.
Cheers,
Brad
> This works but it takes a LONG time because, first the array has to be
> built then it has to be looped through repeatedly to output the report.
I don't understand why you loop through repeatedly
Regards
Plucka