Generating Microsoft Excel files from ColdFusion using Apache POI

For the ColdFusion developer who isn't fluent in Java or a C language (i.e., me), POI is intimidating. There are some good resources out there (listed at left), but going beyond those takes trial and error.

Here is my small contribution. It queries some data tables that store project information. The projects span multiple years and use employees from various labor categories. I was asked to download the Oracle data into a multi-worksheet Excel document. In the past, I've simply created html tables and downloaded them into a pseudo-Excel file. This example includes some nested loops and other nifty things.

The entry form that has this page as its action page prompts the user to enter a range of project ids or a range of dates and the number of outyears desired

Begin the report page

<!---
Author: arcster.com
Create Date: 08/11/08
Purpose: Dowload Labor Category data to excel
Entering: login/report.cfm
Exiting: xls file
Expecting:
session.report
Dependencies:
poi_excel_coldfusion_module.cfm
Server must have the apache roi class files loaded (apparently this is built into CF 7+)
Passing:
Updates:
--->

<cfparam name="form.fromsid" default="">
<cfparam name="form.tosid" default="">
<cfparam name="form.fromdate" default="">
<cfparam name="form.todate" default="">
<cfparam name="form.maxloop" default="">

<!---add calls to modules --->
<cfmodule template="../poi_excel_coldfusion_module.cfm"
fromsid="#form.fromsid#" tosid="#form.tosid#" maxloop="#form.maxloop#"
datasource="#datasource#" fromdate="#form.fromdate#" todate="#form.todate#">

<cfset todaydate = Now()>
<cfset curyear = DatePart("yyyy", todaydate)>
<cfset maxyear = curyear + maxloop>
<!---Change the name of the structure --->
<cfset getInfo = ODCData>
<cfset lis_list = temp2>

<!---Add complete data 6/12/09--->
<cfset getCapInfo = ODCCapData>

<!---Add close data 6/11/09--->
<cfset getCloseInfo = ODCCloseData>

<!---function to construct a formula to do the totals--->
<cffunction name="makeformula" output="false">
<cfargument name="structname" type="string" required="yes">
<cfargument name="istart" type="numeric" required="yes">
<!---
Need to Multiply the number of keys in the structure by 2
because there are two ODC elements for each key
--->
<cfswitch expression="#structname#">
<cfcase value="Active">
<cfif structcount(getInfo)>
<cfset irc = structcount(getInfo)*2 + 1>
<cfelse>
<cfset irc = 2>
</cfif>
</cfcase>
<cfcase value="Completed">
<cfif structcount(getCapInfo)>
<cfset irc = structcount(getCapInfo)*2 + 1>
<cfelse>
<cfset irc = 2>
</cfif>
</cfcase>
<cfcase value="Closed">
<cfif structcount(getCloseInfo)>
<cfset irc = structcount(getCloseInfo)*2 + 1>
<cfelse>
<cfset irc = 2>
</cfif>
</cfcase>
</cfswitch>
<cfswitch expression="#istart#">
<cfcase value="7">
<cfset scol = "H">
</cfcase>
<cfcase value="8">
<cfset scol = "I">
</cfcase>
<cfcase value="9">
<cfset scol = "J">
</cfcase>
<cfcase value="10">
<cfset scol = "K">
</cfcase>
<cfcase value="11">
<cfset scol = "L">
</cfcase>
<cfcase value="12">
<cfset scol = "M">
</cfcase>
<cfcase value="13">
<cfset scol = "N">
</cfcase>
<cfcase value="14">
<cfset scol = "N">
</cfcase>
<cfcase value="15">
<cfset scol = "O">
</cfcase>
<cfcase value="16">
<cfset scol = "P">
</cfcase>
</cfswitch>
<cfset sformula = "SUM(" & scol & "2:" & scol & irc & ")">
<cfreturn sformula>
</cffunction>

 

 

 

<!---Dynamically build URL --->
<cfset sDynFile = CreateUUID()>
<cfset sExt = ".xls">

<cfset sUProtocol = "http://">
<cfset sUServer = cgi.SERVER_NAME>
<cfset sUfolder = "/main/">

<cfset sURL = sUProtocol & sUServer & sUfolder & sDynFile & sExt>

<!--- Dynamically build path --->
<cfset sPPath = "C:\ColdFusion\">
<cfset sPFile = sPPath & sDynFile & sExt>

 

<cffunction name="getthename" output="false">
<cfargument name="labor_category_id" type="numeric" required="yes">
<cfset var selname = "">
<cfquery name="selname" datasource="#datasource#" cachedwithin="#CreateTimespan(0,1,0,0)#">
select labor_category_name from labor_category where labor_category_id = #ARGUMENTS.labor_category_id#
</cfquery>
<cfif Find("Labor Category - ", selname.labor_category_name)>
<cfset tbd = Len(selname.labor_category_name) - 13>
<cfset selname.labor_category_name = Right(selname.labor_category_name, tbd)>
</cfif>
<cfreturn selname.labor_category_name>
</cffunction>

<!---Begin Active project worksheet--->
<cfset workBook = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init()/>
<!---<cfset ointstyle = workBook.createCellStyle()/>--->
<!---style examples at http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFDataFormat.html--->
<cfset cellStyle = workBook.createCellStyle()/>
<cfset cellStyle.setDataFormat(createObject("java","org.apache.poi.hssf.usermodel.HSSFDataFormat").getBuiltinFormat("0.00"))/>
<cfset cellDateStyle = workBook.createCellStyle()/>
<cfset cellDateStyle.setDataFormat(createObject("java","org.apache.poi.hssf.usermodel.HSSFDataFormat").getBuiltinFormat("m/d/yy"))/>
<cfset newSheet = workBook.createSheet("Active")/>
<cfset freeze = newSheet.createFreezePane( 0, 1, 0, 1)/>
<cfset row0 = newSheet.createRow(0)/>
<cfset cell = row0.createCell(0).setCellValue(javacast("string", "project"))/>
<cfset cell = row0.createCell(1).setCellValue(javacast("string", "Description"))/>
<cfset width = newSheet.setColumnWidth(1, 9000)>
<cfset cell = row0.createCell(2).setCellValue(javacast("string", "project Manager/Org"))/>
<cfset width = newSheet.setColumnWidth(2, 9000)>
<cfset cell = row0.createCell(3).setCellValue(javacast("string", "Theme"))/>
<cfset width = newSheet.setColumnWidth(3, 9000)>
<cfset cell = row0.createCell(4).setCellValue(javacast("string", "Business Unit"))/>
<cfset width = newSheet.setColumnWidth(4, 9000)>
<cfset cell = row0.createCell(5).setCellValue(javacast("string", "Completed?"))/>
<cfset cell = row0.createCell(6).setCellValue(javacast("string", "Labor Support Cost"))/>
<cfset istart = 7>
<cfloop from="#curyear#" to="#maxyear#" index="fy">
<cfset cell = row0.createCell(javacast("int",istart)).setCellValue(javacast("int", fy))/>
<cfset istart = istart + 1>
</cfloop>

<!--- Let the giant loop begin --->
<cfset inewrow = 1>
<cfoutput>
<cfloop collection="#getInfo#" item="project_id">
<cfloop list="#lis_list#" index="labor_category_id">
<cfset row = newSheet.createRow(javacast("int",inewrow))/>
<cfset cell = row.createCell(0).setCellValue(javacast("string", #project_id#))/>
<cfset cell = row.createCell(1).setCellValue(javacast("string", #getInfo[project_id]['project_desc']#))/>
<cfset cell = row.createCell(2).setCellValue(javacast("string", #getInfo[project_id]['pmName']# & " / " & #getInfo[project_id]['pmOrg']#))/>
<cfset cell = row.createCell(3).setCellValue(javacast("string", #getInfo[project_id]['theme']#))/>
<cfset cell = row.createCell(4).setCellValue(javacast("string", #getInfo[project_id]['Business_Unit']#))/>
<cfset cell = row.createCell(5).setCellValue(javacast("string", #getInfo[project_id]['status']#))/>
<cfset cell = row.createCell(6).setCellValue(javacast("string", #getthename(labor_category_id)#))/>
<cfset istart = 7>
<cfloop from="#curyear#" to="#maxyear#" index="fiscal_year">
<!---<cfset cell = row.CreateCell(javacast("int",istart)).setCellValue(javacast("int", #getInfo[project_id][fiscal_year][labor_category_id]["amt"]#))/>--->
<cfset cell = row.CreateCell(javacast("int",istart))/>
<cfset vcell = cell.setCellValue(javacast("float", #getInfo[project_id][fiscal_year][labor_category_id]["amt"]#))/>
<cfset stycell = cell.SetCellStyle(cellStyle)/>
<cfset istart = istart + 1>
</cfloop>
<cfset inewrow = inewrow + 1/>
</cfloop>
</cfloop>
<cfset row = newSheet.createRow(javacast("int",inewrow))/>
<cfset cell = row.createCell(7).setCellValue(javacast("string", "TOTAL"))/>
<cfset istart = 7>
<cfloop from="#curyear#" to="#maxyear#" index="fiscal_year">
<cfset cell = row.createCell(javacast("int",istart))/>
<cfset fcell = cell.setCellFormula(javacast("string",makeformula("Active",istart)))/>
<cfset stycell = cell.SetCellStyle(cellStyle)/>
<cfset istart = istart + 1>
</cfloop>
</cfoutput>
<!---End Active project worksheet--->

<!---Begin Closed project worksheet--->
<cfset newSheet = workBook.createSheet("Closed")/>
<cfset freeze = newSheet.createFreezePane( 0, 1, 0, 1)/>
<cfset row0 = newSheet.createRow(0)/>
<cfset cell = row0.createCell(0).setCellValue(javacast("string", "project"))/>
<cfset cell = row0.createCell(1).setCellValue(javacast("string", "Description"))/>
<cfset width = newSheet.setColumnWidth(1, 9000)>
<cfset cell = row0.createCell(2).setCellValue(javacast("string", "project Manager/Org"))/>
<cfset width = newSheet.setColumnWidth(2, 9000)>
<cfset cell = row0.createCell(3).setCellValue(javacast("string", "Theme"))/>
<cfset width = newSheet.setColumnWidth(3, 9000)>
<cfset cell = row0.createCell(4).setCellValue(javacast("string", "Business_Unit"))/>
<cfset width = newSheet.setColumnWidth(4, 9000)>
<cfset cell = row0.createCell(5).setCellValue(javacast("string", "Completed?"))/>
<cfset cell = row0.createCell(6).setCellValue(javacast("string", "Labor Support Cost"))/>
<cfset istart = 7>
<cfloop from="#curyear#" to="#maxyear#" index="fy">
<cfset cell = row0.createCell(javacast("int",istart)).setCellValue(javacast("int", fy))/>
<cfset istart = istart + 1>
</cfloop>

<!--- Let the giant loop begin --->
<cfset inewrow = 1>
<cfoutput>
<cfloop collection="#getCloseInfo#" item="project_id">
<cfloop list="#lis_list#" index="labor_category_id">
<cfset row = newSheet.createRow(javacast("int",inewrow))/>
<cfset cell = row.createCell(0).setCellValue(javacast("string", #project_id#))/>
<cfset cell = row.createCell(1).setCellValue(javacast("string", #getCloseInfo[project_id]['project_desc']#))/>
<cfset cell = row.createCell(2).setCellValue(javacast("string", #getCloseInfo[project_id]['pmName']# & " / " & #getInfo[project_id]['pmOrg']#))/>
<cfset cell = row.createCell(3).setCellValue(javacast("string", #getCloseInfo[project_id]['theme']#))/>
<cfset cell = row.createCell(4).setCellValue(javacast("string", #getCloseInfo[project_id]['Business_Unit']#))/>
<cfset cell = row.createCell(5).setCellValue(javacast("string", #getCloseInfo[project_id]['status']#))/>
<cfset cell = row.createCell(6).setCellValue(javacast("string", #getthename(labor_category_id)#))/>
<cfset istart = 7>
<cfloop from="#curyear#" to="#maxyear#" index="fiscal_year">
<cfset cell = row.CreateCell(javacast("int",istart))/>
<cfset stycell = cell.SetCellStyle(cellStyle)/>
<cfset vcell = cell.setCellValue(javacast("float", #getInfo[project_id][fiscal_year][labor_category_id]["amt"]#))/>
<cfset istart = istart + 1>
</cfloop>
<cfset inewrow = inewrow + 1/>
</cfloop>
</cfloop>
<cfset row = newSheet.createRow(javacast("int",inewrow))/>
<cfset cell = row.createCell(6).setCellValue(javacast("string", "TOTAL"))/>
<cfset istart = 7>
<cfloop from="#curyear#" to="#maxyear#" index="fiscal_year">
<cfset cell = row.createCell(javacast("int",istart))/>
<cfset fcell = cell.setCellFormula(makeformula("Closed",istart))/>
<cfset stycell = cell.SetCellStyle(cellStyle)/>
<cfset istart = istart + 1>
</cfloop>

</cfoutput>
<!---End Closed project Worksheet--->

<!---Begin Completed project worksheet--->
<cfset newSheet = workBook.createSheet("Completed")/>
<cfset freeze = newSheet.createFreezePane( 0, 1, 0, 1)/>
<cfset row0 = newSheet.createRow(0)/>
<cfset cell = row0.createCell(0).setCellValue(javacast("string", "project"))/>
<cfset cell = row0.createCell(1)/>
<cfset vcell = cell.SetCellValue(javacast("string", "Complete Date"))/>
<cfset cell = row0.createCell(2).setCellValue(javacast("string", "Description"))/>
<cfset width = newSheet.setColumnWidth(1, 10000)>
<cfset cell = row0.createCell(3).setCellValue(javacast("string", "project Manager/Org"))/>
<cfset width = newSheet.setColumnWidth(2, 9000)>
<cfset cell = row0.createCell(4).setCellValue(javacast("string", "Theme"))/>
<cfset width = newSheet.setColumnWidth(3, 9000)>
<cfset cell = row0.createCell(5).setCellValue(javacast("string", "Business_Unit"))/>
<cfset width = newSheet.setColumnWidth(4, 9000)>
<cfset cell = row0.createCell(6).setCellValue(javacast("string", "Completed?"))/>
<cfset cell = row0.createCell(7).setCellValue(javacast("string", "Labor Support Cost"))/>
<cfset istart = 8>
<cfloop from="#curyear#" to="#maxyear#" index="fy">
<cfset cell = row0.createCell(javacast("int",istart)).setCellValue(javacast("int", fy))/>
<cfset istart = istart + 1>
</cfloop>

<!--- Let the giant loop begin --->
<cfset inewrow = 1>
<cfoutput>
<cfloop collection="#getCapInfo#" item="project_id">
<cfloop list="#lis_list#" index="labor_category_id">
<cfset row = newSheet.createRow(javacast("int",inewrow))/>
<cfset cell = row.createCell(0).setCellValue(javacast("string", #project_id#))/>
<cfset cell = row.createCell(1)/>
<cfset vcell = cell.setCellValue(javacast("string", #DateFormat(getCapInfo[project_id]['complete_date'],"m/d/yy")#))/>
<cfset sdcell = cell.setCellStyle(cellDateStyle)/>
<cfset cell = row.createCell(2).setCellValue(javacast("string", #getCapInfo[project_id]['project_desc']#))/>
<cfset cell = row.createCell(3).setCellValue(javacast("string", #getCapInfo[project_id]['pmName']# & " / " & #getInfo[project_id]['pmOrg']#))/>
<cfset cell = row.createCell(4).setCellValue(javacast("string", #getCapInfo[project_id]['theme']#))/>
<cfset cell = row.createCell(5).setCellValue(javacast("string", #getCapInfo[project_id]['Business_Unit']#))/>
<cfset cell = row.createCell(6).setCellValue(javacast("string", #getCapInfo[project_id]['status']#))/>
<cfset cell = row.createCell(7).setCellValue(javacast("string", #getthename(labor_category_id)#))/>
<cfset istart = 8>
<cfloop from="#curyear#" to="#maxyear#" index="fiscal_year">
<cfset cell = row.CreateCell(javacast("int",istart))/>
<cfset stycell = cell.SetCellStyle(cellStyle)/>
<cfset vcell = cell.setCellValue(javacast("float", getInfo[project_id][fiscal_year][labor_category_id]["amt"]))/>
<cfset istart = istart + 1>
</cfloop>
<cfset inewrow = inewrow + 1/>
</cfloop>
</cfloop>
<cfset row = newSheet.createRow(javacast("int",inewrow))/>
<cfset cell = row.createCell(8).setCellValue(javacast("string", "TOTAL"))/>
<cfset istart = 8>
<cfloop from="#curyear#" to="#maxyear#" index="fiscal_year">
<cfset cell = row.createCell(javacast("int",istart))/>
<cfset fcell = cell.setCellFormula(makeformula("Completed",istart))/>
<cfset stycell = cell.SetCellStyle(cellStyle)/>
<cfset istart = istart + 1>
</cfloop>
</cfoutput>
<!---End Completed project Worksheet--->

<cfset fileOut = createObject("java","java.io.FileOutputStream").init(sPFile)/>
<cfset workBook.write(fileOut)/>
<cfset fileOut.close()/>
<html>
<body>
<cfoutput>The file is available for <a href="#sURL#">download</a>.</cfoutput>

</body>
</html>

End Report page

Begin module

<!---
Author: arcster
Create date: 08/08/08
Purpose: Run queries to get Labor Category data
Expecting: datasource [string]
maxloop [integer] - maximum number of outyears
datefrom, dateto [date] - date range OR sidfrom, sidto [integer] - project sids

Called by:
login/lienreport.cfm
Passing: ODCData [structure]
temp2 [list]
tlen [int: length of list temp2]
ODCListlen [int: number of projects]
Update info:

Dependencies:
Notes:

--->

 

<!---Get the max projectsid--->
<cfquery name="selmaxpur" datasource="#ATTRIBUTES.datasource#">
SELECT MAX(project_id) AS maxpur
FROM
project
WHERE
project_status_id = 1
AND
display = 1
</cfquery>

<cfset VARIABLES.monthago = DateAdd("d", -30, Now())>

<cfparam name="ATTRIBUTES.maxloop" default="12">
<cfparam name="ATTRIBUTES.fromdate" default="#DateFormat(VARIABLES.monthago)#">
<cfparam name="ATTRIBUTES.todate" default="DateFormat(Now())">
<cfparam name="ATTRIBUTES.fromsid" default="">
<cfparam name="ATTRIBUTES.tosid" default="#selmaxpur.maxpur#">
<cfparam name="VARIABLES.searchtype" default="bydate">

<!---
Give sid searches priority over date searches, since the dates are prepopulated on the form
--->
<cfif len(ATTRIBUTES.fromsid)>
<cfset ATTRIBUTES.fromdate = "">
<cfset ATTRIBUTES.todate = "">
<cfset VARIABLES.searchtype = "bysid">
<cfelse>
<cfset VARIABLES.searchtype = "bydate">

</cfif>
<!---
useful links
informit
http://www.roseindia.net/java/poi/setDataFormat.shtml
http://www.onjava.com/pub/a/onjava/2003/04/16/poi_excel.html
http://www.d-ross.org/index.cfm?objectid=9C65ECEC-508B-E116-6F8A9F878188D7CA--->

<!---
Get the current year
--->

<cfset todaydate = Now()>
<cfset curyear = DatePart("yyyy", todaydate)>

<cfset maxyear = curyear + maxloop>

<!---try http://www.sitepoint.com/article/coldfusion-ii-structs/2--->
<!---also: http://www.adobe.com/devnet/server_archive/articles/structures_arrays_cf.html--->

<!---Create a structure to hold the Query Results--->
<cfset ODCData = structnew()>

<!---Create another structure to hold the Closed Query Results--->
<cfset ODCCloseData = structnew()>

<!---Create another structure to hold the Closed Query Results--->
<cfset ODCCapData = structnew()>

<cffunction name="getstatus" output="false">
<cfargument name="project_status_id" type="numeric" required="yes">
<cfif project_status_id EQ 4>
<cfset statusname = "Y">
<cfelse>
<cfset statusname = "N">
</cfif>
<cfreturn statusname>
</cffunction>

<cffunction name="getpm" output="false">
<cfargument name="project_id" type="numeric" required="yes">
<cfquery name="selpm" datasource="#ATTRIBUTES.datasource#">
select
nba.full_name,
oc.org_code_abbrev
from
nbaccess nba,
org_code oc,
project_people pp
WHERE
pp.project_id = #ARGUMENTS.project_id#
and
pp.project_role_id = 2
AND
nba.uniqueidentifier = pp.uniqueidentifier
AND
oc.org_code_id = nba.org_code_id
</cfquery>
<cfreturn selpm>
</cffunction>

<!---Get the total FTEs committed by year--->
<!--- 5/21/09 - add Theme and Business_Unit--->
<cfquery name="selSum0" datasource="#ATTRIBUTES.datasource#">
select
l.lien_amount,
p.project_id,
p.project_desc,
p.project_status_id,
e.entity_desc as Business_Unit,
p.theme,
l.fiscal_year,
l.labor_category_id,
li.labor_category_name
from
lien l,
labor_category li,
project p,
project_entity pe,
entity e
where
li.labor_category_id = l.labor_category_id
and
p.project_id = l.project_id
and
p.project_id = pe.project_id
and
l.labor_category_id in
(SELECT li.labor_category_id from labor_category li where li.labor_category_name IN ('Labor Cat 1','Labor Cat 2'))
<cfswitch expression="#VARIABLES.searchtype#">
<cfcase value="bysid">
and
p.project_id BETWEEN #ATTRIBUTES.fromsid# AND #ATTRIBUTES.tosid#
</cfcase>
<cfcase value="bydate">
and
p.date_added BETWEEN '#DateFormat(ATTRIBUTES.fromdate)#' AND '#DateFormat(ATTRIBUTES.todate)#'
</cfcase>
</cfswitch>

ORDER BY
p.project_id, l.fiscal_year
</cfquery>

<!---
Added selSum1 query 6/11/09. Gets projects closed in time period
--->
<cfquery name="selSum1" datasource="#ATTRIBUTES.datasource#">
select
l.lien_amount,
p.project_id,
p.project_desc,
p.project_status_id,
e.entity_desc as Business_Unit,
p.theme,
l.fiscal_year,
l.labor_category_id,
li.labor_category_name
from
lien l,
labor_category li,
project p,
project_entity pe,
entity e
where
li.labor_category_id = l.labor_category_id
and
p.project_id = l.project_id
and
p.project_id = pe.project_id
and
l.labor_category_id in
(SELECT li.labor_category_id from labor_category li where li.labor_category_name IN ('OTHER DIRECT COST - LABOR SUPPORT SERVICES','OTHER DIRECT COST - ENG. TOOLS/SUPPORT'))
<cfswitch expression="#VARIABLES.searchtype#">
<cfcase value="bysid">
and
p.project_id BETWEEN #ATTRIBUTES.fromsid# AND #ATTRIBUTES.tosid#
and
project_status_id <> 1
</cfcase>
<cfcase value="bydate">
and
p.date_status_change BETWEEN '#DateFormat(ATTRIBUTES.fromdate)#' AND '#DateFormat(ATTRIBUTES.todate)#'
</cfcase>
</cfswitch>

ORDER BY
p.project_id, l.fiscal_year
</cfquery>
<!---
******** complete query************
complete_date added 6/18/09
--->
<cfquery name="selSum2" datasource="#ATTRIBUTES.datasource#">
select
l.lien_amount,
p.project_id,
p.project_desc,
p.project_status_id,
e.entity_desc as Business_Unit,
p.theme,
l.fiscal_year,
l.labor_category_id,
li.labor_category_name,
c.complete_date
from
lien l,
labor_category li,
project p,
project_entity pe,
entity e,
complete c,
project_complete pc
where
pc.complete_id = c.complete_id
and
pc.project_id = p.project_id
and
li.labor_category_id = l.labor_category_id
and
p.project_id = l.project_id
and
p.project_id = pe.project_id
and
l.labor_category_id in
(SELECT li.labor_category_id from labor_category li where li.labor_category_name IN ('OTHER DIRECT COST - LABOR SUPPORT SERVICES','OTHER DIRECT COST - ENG. TOOLS/SUPPORT'))
<cfswitch expression="#VARIABLES.searchtype#">
<cfcase value="bysid">
and
p.project_id BETWEEN #ATTRIBUTES.fromsid# AND #ATTRIBUTES.tosid#
and
project_status_id <> 1
</cfcase>
<cfcase value="bydate">
and
c.complete_date BETWEEN '#DateFormat(ATTRIBUTES.fromdate)#' AND '#DateFormat(ATTRIBUTES.todate)#'
</cfcase>
</cfswitch>

ORDER BY
p.project_id, l.fiscal_year
</cfquery>

 

<!---added new theme and Business_Unit keys 5/21/09--->
<!--- Add NumberFormat and mask 6/18/09--->
<cfloop query="selSum0">
<cfset ODCData["#project_id#"]["#fiscal_year#"]["#labor_category_id#"]["amt"] = "#NumberFormat(lien_amount, '___.__')#">
<cfset ODCData["#project_id#"]["#fiscal_year#"]["#labor_category_id#"]["name"] = "#labor_category_name#">
<cfif NOT StructKeyExists(ODCData['#project_id#'], "project_desc")>
<cfset ODCData[#project_id#]["project_desc"] = "#project_desc#">
</cfif>
<cfif NOT StructKeyExists(ODCData['#project_id#'], "status")>
<cfset ODCData[#project_id#]["status"] = "#getstatus(project_status_id)#">
</cfif>
<cfif NOT StructKeyExists(ODCData['#project_id#'], "pmName")>
<cfset ODCData[#project_id#]["pmName"] = "#getpm(project_id).full_name#">
</cfif>
<cfif NOT StructKeyExists(ODCData['#project_id#'], "pmOrg")>
<cfset ODCData[#project_id#]["pmOrg"] = "#getpm(project_id).org_code_abbrev#">
</cfif>
<cfif NOT StructKeyExists(ODCData['#project_id#'], "theme")>
<cfset ODCData[#project_id#]["theme"] = "#theme#">
</cfif>
<cfif NOT StructKeyExists(ODCData['#project_id#'], "Business_Unit")>
<cfset ODCData[#project_id#]["Business_Unit"] = "#Business_Unit#">
</cfif>
</cfloop>

<!---Loop added 6/11/09--->
<cfloop query="selSum1">
<cfset ODCCloseData["#project_id#"]["#fiscal_year#"]["#labor_category_id#"]["amt"] = "#NumberFormat(lien_amount, '999.99')#">
<cfset ODCCloseData["#project_id#"]["#fiscal_year#"]["#labor_category_id#"]["name"] = "#labor_category_name#">
<cfif NOT StructKeyExists(ODCCloseData['#project_id#'], "project_desc")>
<cfset ODCCloseData[#project_id#]["project_desc"] = "#project_desc#">
</cfif>
<cfif NOT StructKeyExists(ODCCloseData['#project_id#'], "status")>
<cfset ODCCloseData[#project_id#]["status"] = "#getstatus(project_status_id)#">
</cfif>
<cfif NOT StructKeyExists(ODCCloseData['#project_id#'], "pmName")>
<cfset ODCCloseData[#project_id#]["pmName"] = "#getpm(project_id).full_name#">
</cfif>
<cfif NOT StructKeyExists(ODCCloseData['#project_id#'], "pmOrg")>
<cfset ODCCloseData[#project_id#]["pmOrg"] = "#getpm(project_id).org_code_abbrev#">
</cfif>
<cfif NOT StructKeyExists(ODCCloseData['#project_id#'], "theme")>
<cfset ODCCloseData[#project_id#]["theme"] = "#theme#">
</cfif>
<cfif NOT StructKeyExists(ODCCloseData['#project_id#'], "Business_Unit")>
<cfset ODCCloseData[#project_id#]["Business_Unit"] = "#Business_Unit#">
</cfif>
</cfloop>

<!---Loop added 6/12/09--->
<cfloop query="selSum2">
<cfset ODCCapData["#project_id#"]["#fiscal_year#"]["#labor_category_id#"]["amt"] = "#NumberFormat(lien_amount, '999.99')#">
<cfset ODCCapData["#project_id#"]["#fiscal_year#"]["#labor_category_id#"]["name"] = "#labor_category_name#">
<cfif NOT StructKeyExists(ODCCapData['#project_id#'], "project_desc")>
<cfset ODCCapData[#project_id#]["project_desc"] = "#project_desc#">
</cfif>
<cfif NOT StructKeyExists(ODCCapData['#project_id#'], "status")>
<cfset ODCCapData[#project_id#]["status"] = "#getstatus(project_status_id)#">
</cfif>
<cfif NOT StructKeyExists(ODCCapData['#project_id#'], "pmName")>
<cfset ODCCapData[#project_id#]["pmName"] = "#getpm(project_id).full_name#">
</cfif>
<cfif NOT StructKeyExists(ODCCapData['#project_id#'], "pmOrg")>
<cfset ODCCapData[#project_id#]["pmOrg"] = "#getpm(project_id).org_code_abbrev#">
</cfif>
<cfif NOT StructKeyExists(ODCCapData['#project_id#'], "theme")>
<cfset ODCCapData[#project_id#]["theme"] = "#theme#">
</cfif>
<cfif NOT StructKeyExists(ODCCapData['#project_id#'], "Business_Unit")>
<cfset ODCCapData[#project_id#]["Business_Unit"] = "#Business_Unit#">
</cfif>
<cfif NOT StructKeyExists(ODCCapData['#project_id#'], "complete_date")>
<cfset ODCCapData[#project_id#]["complete_date"] = "#DateFormat(complete_date, 'mm-dd-yyyy')#">
</cfif>
</cfloop>

<cfset temp = ListSort(StructKeyList(ODCData), "numeric")>
<cfset tempcls = ListSort(StructKeyList(ODCCloseData), "numeric")>
<cfset tempcap = ListSort(StructKeyList(ODCCapData), "numeric")>

<cfquery name="sellis" datasource="#ATTRIBUTES.datasource#">
SELECT labor_category_id from labor_category where labor_category_name IN ('OTHER DIRECT COST - LABOR SUPPORT SERVICES','OTHER DIRECT COST - ENG. TOOLS/SUPPORT')
</cfquery>

<cfset temp2 = ValueList(sellis.labor_category_id)>

<!---
If no fiscal_year key exists,
then loop through all labor_category amounts for the whole year and make them 0.

If no keys exist for a single labor_category_id, set the amount to 0
--->
<cfloop list="#temp#" index="project_id">
<cfloop from="#curyear#" to="#maxyear#" index="fiscal_year">
<cfloop list="#temp2#" index="labor_category_id">
<cfif Not(StructKeyExists(ODCData['#project_id#'], "#fiscal_year#"))>
<cfset ODCData[#project_id#][#fiscal_year#][#labor_category_id#]["amt"] = #NumberFormat(0,'999.99')#>
</cfif>
<cfif Not(StructKeyExists(ODCData['#project_id#']['#fiscal_year#'], "#labor_category_id#"))>
<cfset ODCData[#project_id#][#fiscal_year#][#labor_category_id#]["amt"] = #NumberFormat(0,'999.99')#>
</cfif>
</cfloop>
</cfloop>
</cfloop>

<!---added 6/11/09--->
<cfloop list="#tempcls#" index="project_id">
<cfloop from="#curyear#" to="#maxyear#" index="fiscal_year">
<cfloop list="#temp2#" index="labor_category_id">
<cfif Not(StructKeyExists(ODCCloseData['#project_id#'], "#fiscal_year#"))>
<cfset ODCCloseData[#project_id#][#fiscal_year#][#labor_category_id#]["amt"] = #NumberFormat(0,'999.99')#>
</cfif>
<cfif Not(StructKeyExists(ODCCloseData['#project_id#']['#fiscal_year#'], "#labor_category_id#"))>
<cfset ODCCloseData[#project_id#][#fiscal_year#][#labor_category_id#]["amt"] = #NumberFormat(0,'999.99')#>
</cfif>
</cfloop>
</cfloop>
</cfloop>

<!---added 6/12/09--->
<cfloop list="#tempcap#" index="project_id">
<cfloop from="#curyear#" to="#maxyear#" index="fiscal_year">
<cfloop list="#temp2#" index="labor_category_id">
<cfif Not(StructKeyExists(ODCCapData['#project_id#'], "#fiscal_year#"))>
<cfset ODCCapData[#project_id#][#fiscal_year#][#labor_category_id#]["amt"] = #NumberFormat(0,'999.99')#>
</cfif>
<cfif Not(StructKeyExists(ODCCapData['#project_id#']['#fiscal_year#'], "#labor_category_id#"))>
<cfset ODCCapData[#project_id#][#fiscal_year#][#labor_category_id#]["amt"] = #NumberFormat(0,'999.99')#>
</cfif>
</cfloop>
</cfloop>
</cfloop>

<cfset caller.ODCData = ODCData>
<cfset caller.ODCCloseData = ODCCloseData>
<cfset caller.ODCCapData = ODCCapData>
<cfset caller.temp2 = temp2>
<cfset caller.ODClistlen = ListLen(StructKeyList(ODCData))>
<cfset caller.ODCCloselistlen = ListLen(StructKeyList(ODCCloseData))>
<cfset caller.ODCCaplistlen = ListLen(StructKeyList(ODCCapData))>
<cfset caller.tlen = ListLen(temp2)>

End module