Arrays Structures Multidimensional Arrays Arrays of Structures Or

Arrays, Structures, Multidimensional Arrays, Arrays of Structures Or what I like to call them, "Complex Containers" CFUNITED – The premier Cold. Fusion conference www. cfunited. com

About Me 1. I own Komputer. Man. com, a 1 employee Cold Fusion and Flex application development consulting company. 2. Developing CF applications since 1998 and have been consulting since 1999. 3. Developed my first Flex App this year. 4. I am not one of those people who delves into how CF does its magic. 5. I am much more concerned with how I can use CF to make my own magic! June 27 th – June 30 th 2007

What we are going to discuss ü Overview of an Array and a Structure. ü Create, write to, and retrieve data from a 1 dimensional array, a 2 dimensional array, and a structure. ü Create and populate a 2 dimensional array that contains a 2 dimensional array. ü Add a structure to the multi dimensional array. ü What I call a Complex Container. ü Looping through the complex container. ü Practical uses for Multi dimensional Arrays and Structures. June 27 th – June 30 th 2007

The 1 Dimensional Array ü An Array is a virtual container used to store data. ü Think of a 1 Dimensional Array as a named list referenced by sequential integers. June 27 th – June 30 th 2007

The 1 Dimensional Array ü First you define the Array with a unique name. <CFSET Aerosmith = Array. New(1)> ü Use CFSET to populate the array as shown below. <CFSET Aerosmith[1] = 'Tom'> <CFSET Aerosmith[2] = 'Joe'> <CFSET Aerosmith[3] = 'Brad'> <CFSET Aerosmith[4] = 'Joey'> <CFSET Aerosmith[5] = 'Steven'> ü Notice the sequential integer referencing of Array. Name[Row. Sequence]. ü <CFSET Aerosmith[7] = 'foobar'> is a valid command but because the 6 th element was skipped you can have issues when working with the array. June 27 th – June 30 th 2007

The 1 Dimensional Array ü I prefer to loop through an Array when viewing or working with the data stored in the container. <CFLOOP FROM="1" TO="#Array. Len(Aerosmith)#" INDEX="xx"> <CFOUTPUT> Aerosmith[#xx#] = #Aerosmith[xx]# <BR> </CFOUTPUT> </CFLOOP> ü Output looks like this: Aerosmith[1] = Tom Aerosmith[2] = Joe Aerosmith[3] = Brad Aerosmith[4] = Joey ü See Example 1 June 27 th – June 30 th 2007 Aerosmith[5] = Steven

The 2 Dimensional Array ü Think of a 2 dimensional array as a spreadsheet with the rows and columns referenced by sequential integers. Rows get referenced first, then columns. ü Data in the columns is type less but I prefer to keep like data in the same column. ü I. E. Do not put Date data and Integer data in the same column. June 27 th – June 30 th 2007

The 2 Dimensional Array ü First you define the Array with a unique name. <CFSET Aerosmith = Array. New(2)> ü Then populate the Array as shown below. <CFSET Aerosmith[1][1] = 'Tom'> <CFSET Aerosmith[1][2] = 'Hamilton'> <CFSET Aerosmith[2][1] = 'Joe'> <CFSET Aerosmith[2][2] = 'Perry'> <CFSET Aerosmith[3][1] = 'Brad'> <CFSET Aerosmith[3][2] = 'Whitford'> <CFSET Aerosmith[4][1] = 'Joey'> <CFSET Aerosmith[4][2] = 'Kramer'> <CFSET Aerosmith[5][1] = 'Steven'> <CFSET Aerosmith[5][2] = 'Tyler'> ü Notice the referencing of Array. Name[Row][Column]. June 27 th – June 30 th 2007

The 2 Dimensional Array ü Loop through the Array. <CFLOOP FROM="1" TO="#Array. Len(Aerosmith)#" INDEX="xx"> <CFOUTPUT> Aerosmith[#xx#] = #Aerosmith[xx][1]# - #Aerosmith[xx][2]#<BR> </CFOUTPUT> </CFLOOP> ü Output looks like this: Aerosmith[1] = Tom - Hamilton Aerosmith[2] = Joe - Perry Aerosmith[3] = Brad - Whitford Aerosmith[4] = Joey - Kramer Aerosmith[5] = Steven - Tyler ü See Example 2. June 27 th – June 30 th 2007

2 -D Array KDL Preferences ü I prefer to know the number of "columns" stored in the Array. ü Formatting the output is easier if the number of columns are known. ü I don't care about the number of Rows in an Array because I use '#Array. Len(Array. Name)#' and I like to Loop! ü Name the array something that is indicative of the data being stored inside of it. ü Always put something into every cell of the array even if it is a empty string because nulls cause problems in an array. ü <CFSET Aerosmith[6][1]= ''> June 27 th – June 30 th 2007

The Structure ü Think of a structure as a 1 dimensional array that can is referenced by a Key Value pair. First. Name. ['Tom'] Hamilton First. Name. ['Joe'] Perry First. Name. ['Brad'] Whitford First. Name. ['Joey'] Kramer First. Name. ['Steven'] Tyler June 27 th – June 30 th 2007

The Structure ü Think of a structure as a database table with two columns, 1 column (Key) is the primary key and the other column (Value) is the value column. ü Together they make up a Key - Value pair ü Biggest difference between the structure and the 1 dimensional array is that arrays are referenced by sequential numbers and structures can be referenced by alphanumeric text or sequential numbers. ü Arrays are sorted, and structures are not. ü First in does not mean first out when looping through a structure. June 27 th – June 30 th 2007

How to Populate a Structure ü First define the Structure with a unique name <CFSET Aero_Struct = Struct. New()> ü Use CFSET to populate the structure <CFSET Aero_Struct ['Tom'] = 'Hamilton'> <CFSET Aero_Struct ['Joe'] = 'Perry'> <CFSET Aero_Struct ['Brad'] = 'Whitford'> <CFSET Aero_Struct ['Joey'] = 'Kramer'> <CFSET Aero_Struct ['Steven'] = 'Tyler'> ü Notice the referencing by alphanumeric characters, also referred to as a 'Key - Value' pair. 'Tom' would be the KEY and 'Hamilton' would be the value. June 27 th – June 30 th 2007

Contents of a Structure § I generally do not loop through a structure when viewing or working with the data stored in the structure, but you can like this <CFLOOP COLLECTION="#Aero_Struct#" ITEM="My. Name"> <CFOUTPUT> #My. Name# - #Aero_Struct[My. Name]#<BR> </CFOUTPUT> </CFLOOP> § Output MAY look like this (order is not guaranteed): Tom - Hamilton Brad - Whitford Joey - Kramer Steven - Tyler Joe - Perry June 27 th – June 30 th 2007

Structure KDL Preferences § I don't like to loop through a structure because the data in the structure is un-sorted. First in doesn't mean first out! § Structures are great for lookup values. § Name you structure something that is indicative of the data being stored inside of it. June 27 th – June 30 th 2007

That’s Enough of the Easy Stuff § Here is the challenge… ü I have reports that can be automatically generated daily, weekly, monthly, quarterly, or yearly. ü Each report has its own set of variables that are used to generate the report. (I. E. 'Agency_Name, Project_Manager, etc. . . ') ü One or more users can receive the report when it gets generated. ü Each user has their own values that get passed into the report as variables. (I. E. for User 1 Agency_Name = 'Dept. of Admin', for User 2 Agency_Name = 'Health and Welfare') § How do I put all of that into an Array like container? ? ? June 27 th – June 30 th 2007

The Database Schema June 27 th – June 30 th 2007

About the Database § The Report. Names table stores data about the report itself, such as… ü The Report. Name is the logical path to the *. cfm template to be executed. ü Is. Active tells me if this report is still active (Y/N). ü Start. Date tells me when to start generating the report. ü Rpt. Frequency tells me how often to generate the report. (daily, weekly, etc. . . ) ü The Email fields pass along some information to the CFMAIL tag when the report gets mailed out to the user. June 27 th – June 30 th 2007

About the Database § The Report. Params table stores data about the variables that the report will accept. ü The Param. Name is the name of the parameter, I. E. 'Agency_Name'. ü Param. Default. Value is the default value of the parameter in case a customized value does not get passed to the report (I. E. 'Dept. of Admin'). ü Param. Desc is just a description of the parameter so I can remember what was getting passed and why. ü Rpt. Name. Cnt links the parameter to a specific report. June 27 th – June 30 th 2007

About the Database § The Report. Users table tells me which users will get which reports. One user can receive multiple reports and one report can be generated for multiple users. ü Rpt. Name. Cnt is the link to a specific report. ü User. Cnt is the link to a specific User. § The Report. User. Params table tells me what the User value is for each parameter associated to the report. ü Rpt. Param. Cnt is the link to a specific report parameter. ü Rpt. User. Cnt is the link to a specific User receiving a specific report. ü User. Param. Value is the value of the parameter to be passed to the report for this user, I. E, 'Health and Welfare'. June 27 th – June 30 th 2007

Build the Array § Get the data about the report itself ü Run a similar query for week, month, and year ü This example only deals with daily reports <CFQUERY NAME="Get. Daily" DATASOURCE="#Request. dsn#"> SELECT Rpt. Name. Cnt, Report. Name, EMail. Subject, EMail. Body, EMail. Sender FROM Report. Names WHERE Rpt. Frequency = 'DAY' AND Is. Active <> 0 AND Start. Date <= #Create. ODBCDate(Now())# </CFQUERY> June 27 th – June 30 th 2007

Build the Array § Put the data into an Array called Report. Array <CFSET Report. Array = Array. New(2)> <CFSET My. Cnt = 1> <CFIF Get. Daily. Record. Count NEQ 0> <CFOUTPUT QUERY="Get. Daily"> <CFSET Report. Array[My. Cnt][1] = Get. Daily. Rpt. Name. Cnt> <CFSET Report. Array[My. Cnt][2] = Get. Daily. Report. Name> <CFSET Report. Array[My. Cnt][3] = Get. Daily. EMail. Body> <CFSET Report. Array[My. Cnt][4] = Get. Daily. EMail. Sender> <CFSET Report. Array[My. Cnt][5] = Get. Daily. EMail. Subject> <CFSET Report. Array[My. Cnt][6] = Array. New(2)> <CFSET My. Cnt = My. Cnt + 1> </CFOUTPUT> </CFIF> June 27 th – June 30 th 2007

Build the Array § Notice the line that creates a 2 dimensional array in the existing 2 -D Array. <CFSET Report. Array[My. Cnt][6] = Array. New(2)> § The Variable My. Cnt increments to keep track of each row in the array. <CFSET My. Cnt = My. Cnt + 1> § The CFOUTPUT tag loops through the Get. Daily record set. June 27 th – June 30 th 2007

Add Users to Report. Array § Now that we know what reports are going to be generated find the users to get the reports. § Loop through the array and run a query based on the reports stored in the array. ü There are other ways to do this in SQL but for long term maintenance I thought this would be easier. June 27 th – June 30 th 2007

Add Users to Report. Array § Loop through the Report. Array and get the Users associated to the report. <CFLOOP FROM "1" TO "#Array. Len(Report. Array)# INDEX="cc"> <CFSET My. Rpt. Name. Cnt = Report. Array[cc][1]> <CFQUERY NAME="Get. Users" DATASOURCE="#Request. dsn#"> SELECT Rpt. User. Cnt, EMail. Addr FROM Report. Users, Users WHERE Rpt. Name. Cnt = #My. Rpt. Name. Cnt# AND Users. User. Cnt = Report. Users. User. Cnt </CFQUERY> June 27 th – June 30 th 2007

Add Users to Report. Array § The CFSET command is optional, I like it to self document what my WHERE clause contains. § The query returns the Primary key from the Report. Users and the e-mail address associated to the user. ü The PK value will be used to get the users report parameters June 27 th – June 30 th 2007

Add Users to Report. Array § If the query returned any records add it to the Report. Array. ü Remember we are still inside the 1 st loop. <CFIF Get. Users. Record. Count GT 0> <CFSET New. Cnt = 1> <CFLOOP QUERY="Get. Users"> <CFSET Report. Array[cc][6][New. Cnt][1] = Get. Users. Rpt. User. Cnt> <CFSET Report. Array[cc][6][New. Cnt][2] = Struct. New()> <CFSET Report. Array[cc][6][New. Cnt][3] = Get. Users. EMail. Addr> June 27 th – June 30 th 2007

Add Users to Report. Array § If a user exists add them to Report. Array. § Set New. Cnt to keep track of the row of the 2 nd MD array inside of the 1 st one. § Loop through the Get. Users Query to add the data to Report. Array. ü Syntax: Report. Array[cc][6][New. Cnt][1] = Get. Users. Rpt. User. Cnt June 27 th – June 30 th 2007

Add Users to Report. Array § Create a structure in the 2 nd cell of the 2 nd MD array. ü Syntax: Report. Array[cc][6][New. Cnt][2] = Struct. New() § The structure is named Report. Array[cc][6][New. Cnt][2] June 27 th – June 30 th 2007

Add User Parameters § While looping through the Get. Users Query get the Users parameters associated to the report. <CFQUERY NAME="Get. Params" DATASOURCE="#Request. dsn#"> SELECT Param. Name, User. Param. Value FROM Report. User. Params, Report. Params WHERE Rpt. Name. Cnt = #My. Rpt. Name. Cnt# AND Report. User. Params. Rpt. Param. Cnt = Report. Params. Rpt. Param. Cnt AND Rpt. User. Cnt = #Get. Users. Rpt. User. Cnt# </CFQUERY> June 27 th – June 30 th 2007

Add User Parameters § The query returns any specific user parameters associated to the person getting the report. ü Remember we are still inside the 1 st loop (the Report. Array loop). ü We are also looping through the Get. Users Query. June 27 th – June 30 th 2007

Add User Parameters § If the query returns any records add them to the structure in Report. Array. <CFIF Get. Params. Record. Count GT 0> <CFLOOP QUERY="Get. Params"> <CFSET Report. Array[cc][6][New. Cnt][2]["#Get. Params. Param. Name#"] = Get. Params. User. Param. Value> </CFLOOP> </CFIF> § The structure is populated with the key value pair returned from the query. June 27 th – June 30 th 2007

Add User Parameters § Now we have all our data we close the loops. <CFSET New. Cnt = New. Cnt + 1> </CFLOOP> </CFIF> </CFLOOP> § Increment the 2 nd MD array counter. § Close the Get. Users query loop. ü The CFIF is closed if there were any Get. User records. § Close the Report. Array loop. June 27 th – June 30 th 2007

Data In the Complex Container Report. Array[1][1] = PK value of the report to be ran Report. Array[1][2] = Actual name of report to be ran (Report. cfm) Report. Array[1][3] = Text to be included in the E-Mail body Report. Array[1][4] = E-Mail address of the person sending the report Report. Array[1][5] = E-Mail subject Report. Array[1][6] = 2 nd Multidimensional array Report. Array[1][6][1][1] = PK of the person getting THIS report Report. Array[1][6][1][2] = Structure containing Key value pair of parameters and parameter value Report. Array[1][6][1][3] = E-Mail addr of the person getting the report June 27 th – June 30 th 2007

View The Complex Container § Loop through the Complex Container to view the data. <CFLOOP FROM="1" TO="#Array. Len(Report. Array)#" INDEX="xx"> <CFSET Report. Name = "/Your. App/Reports/" & Report. Array[xx][2]> <CFLOOP FROM="1" TO="#Array. Len(Report. Array[xx][6])#" INDEX="yy"> <CFINCLUDE TEMPLATE="#Report. Name#"> </CFLOOP> June 27 th – June 30 th 2007

View The Complex Container § 1 st loop returns the report name. § The CFSET tag sets up the relative path to the report template that gets included. § The 2 nd loop calls the report one time for each user receiving the report. § The report uses the data in the array too. June 27 th – June 30 th 2007

The Report § The report initializes all parameters. § If any of them are customized for the user. <CFLOOP COLLECTION="#Report. Array[xx][6][yy][2]#" ITEM="Param"> <CFIF Param EQ "Agency. Cnt"> <CFSET My. Agency. Cnt = Report. Array[xx][6][yy][2][Param]> </CFIF> <CFIF Param EQ "Del. Proj"> <CFSET My. Del. Proj = Report. Array[xx][6][yy][2][Param]> </CFIF> <CFIF Param EQ "Hold. Act"> <CFSET My. Hold. Act = Report. Array[xx][6][yy][2][Param]> </CFIF> </CFLOOP> June 27 th – June 30 th 2007