I'm running into a snag as I populate the rows of a cf spreadsheet object and create an xls file. On my dev server, the entire process takes only about a minute, but on my prod server, the page starts hanging indefinitely inside the very first iteration of my query loop. I've commented where it starts hanging in the code below.
For every row in qReconciled, the query being looped over (which is the result of a join), I'm creating 3 rows in the spreadsheet - the first holds data from one side of the join, the second holds data from the other side of the join, and the third is a blank row.
There's no problem with the query qReconciled (which has about 2,200 rows); I ran the query in SQL Server studio on dev and prod, and in both cases it took only about a minute to run.
Note that for both dev and prod server, the CPU is pinned at or near 100% as the process runs. But while the dev server is a dedicated box, the prod server is a virtual machine with 3 other VMs sharing the same box, so I'm wondering if it's just a VM memory allocation issue.
<cfparam name="url.from" default="" />
<cfparam name="url.to" default="" >
<!---dates for from and to come in the format "yyyy-mm-dd"--->
<cfset vImportName = "" />
<cfset vDateTitle = "" />
<cfset oStarsImporter = CreateObject("component", "#application.cfcpath#.starsImporter") />
<cfset qReconciled = oStarsImporter.getReconciled(url.importId, url.from, url.to) />
<cfif isDate(url.from) AND isDate(url.to)>
<cfif len(trim(vImportName))>
<cfset vImportName = vImportname & "-" />
</cfif>
<cfset vDateTitle = "From_" & dateFormat(url.from, "mm-dd-yyyy") & "_to_" & dateFormat(url.to, "mm-dd-yyyy") />
</cfif>
<cfset vFileName = vImportName & vDateTitle />
<cfif NOT len(trim(vFileName))>
<cfset vFileName = "All_reconciled_" & dateFormat(now(), "mm-dd-yyyy") />
</cfif>
<cfset currentSpreadSheetRow = 1> <!---start at 1, to ignore spreadsheet header row--->
<!---Below is handy a way to generate an Excel sheet without having to save any file to the server...--->
<cfset s = spreadsheetNew()>
<!--- Add header row --->
<cfset spreadsheetAddRow(s, "Assessment Source,Client Last,Client First,SSN,Gender,Client Case Number,DoB,Zip Code,Assessment Type,Assessment Status,NYCWAY Appt Code,Appt Date,Appt Time,CASAC Worker,Approving Supervisor,Benefit Status (Case Type),Final Recommendation,Site Code,Re-Assessment,Current Living Situation,Mental Conditions,Candidate for WeCARE,Attend Substance Use,Agency Name,Currently Attending Treatment Program Name,Type of Treatment,Opioid Taken,Housing Outcome,Return to Treatment Provider,Non HRA-Approved,Name non-HRA Site,New Mandate,Transfer to New Treatment Provider,Check-in,Assessment Start,Checked Out Time,Checked-in Site,Addendum,Consent B signed")>
<!---For each row in the query, create *3* rows in the spreadsheet. One for STARS, one for SACAP, and a blank row--->
<cfloop query="qReconciled">
<!---Need to do this, else spreadsheet won't recognize as string--->
<cfset tCheckin = " #timeformat(dcheckin,'short')#" />
<cfset tAssessmentStart = " #timeformat(dAssessmentStart,'short')#" />
<cfset tCheckouttime = " #timeformat(dCheckouttime,'short')#" />
<cfset tTimeInBin = " #timeformat(timeInBin,'short')#" />
<cfset tApptBeginTime = " #timeformat(dApptBeginTime,'short')#" />
<cfset tApptEndTime = " #timeformat(dApptEndTime,'short')#" />
<cfset tAppointmentTime = " #timeformat(dAppointmentdate,'short')#" />
<cfset tScheduledTime = " #timeformat(scheduledTime, 'short')#" />
<cfset arrThisRowSTARSValues = ['STARS','#vClientlname#','#vClientfname#','#vSSN#','#vGender#',#listFirst(vClientcasenumber, '-')#,#dateFormat(dDOB, 'mm/dd/yyyy')#,'#vZipcode#','#vAssessmenttype#','#vAssessmentstatus#','#vNycwayappointmentcode#',#dateFormat(dAssessmentStart, 'mm/dd/yyyy')#, #tAppointmentTime#,'#vCasacworker#','#vApprovingsupervisor#','#vBenefitstatus_casetype#','#vFinalrecommendation#','#vSitecode#','#dReassessment#','#vCurrentlivingsituation#','#vMentalconditions#','#vCandidateforwecare#','#vAttendsubstanceuse#','#vAgencyname#','#vTreatmentprogramname#','#vTypeoftreatment#','#vOpioidtaken#','#iHousingoutcome#','#vReturntotreatmentprovider#','#vNonhraapproved#','#vNamenonhrasite#','#vNewhramandate#','#vTransfertonewtreatmentprovider#', #tCheckin#, #tAssessmentStart#, #tCheckouttime#,'#vCheckedinsite#','#vAddendum#','#vConsentbsigned#'] />
<cfset arrThisRowSACAPValues = ['SACAP','#sacap_clientLName#','#sacap_clientFName#','#sacap_ssn#','#sacap_gender#','#vHRAClientId#',#dateFormat(vAge, 'mm/dd/yyyy')#,'#vClientPermZip#','#sacap_StarsAssessmentType#',' ',#sacap_clienttype#,#dateFormat(dCasacDate, 'mm/dd/yyyy')#,#tScheduledtime#,'#vEmpFName# #vEmpLName#','#supervisor_fname# #supervisor_lname#','#sacap_benefitstatus#','#sacap_finalRecommendation#','#vHRACode#','#sacap_reassessment#',' ','#vMHdiagnoses#','#sacap_wecaretype#',' ','#sacap_projects#',' ',' ','#sacap_buprenorphine#','#sacap_housingstatus#','#sacap_encounteroutcome#','#sacap_nonhraapproved#','#sacap_namenonhrasite#','#isNewlyMandated#','#sacap_transferreason#', #tTimeInBin#, #tApptBeginTime#, #tApptEndTime#,'#sacap_sacapsite#',' ',' '] />
<!---This method of populating the spreadsheet is necessary; with spreadsheetAddRow(), commas in values will be interpreted as a new column, even if the value is wrapped in single-quotes, and this screws everything up--->
<cfset currentSpreadSheetRow = currentSpreadSheetRow + 1 />
<!---STARS Row--->
<cfloop from="1" to="39" index="x">
<cfset spreadsheetSetCellValue(s,arrThisRowSTARSValues[x],currentSpreadSheetRow,x) />
</cfloop>
<cfset currentSpreadSheetRow = currentSpreadSheetRow + 1 />
<!---THIS IS WHERE IT HANGS, ON THE VERY FIRST ITERATION OF QRECONCILED. CPU IS PINNED AT 100%--->
<!---SACAP Row--->
<cfloop from="1" to="39" index="x">
<cfset spreadsheetSetCellValue(s,arrThisRowSACAPValues[x],currentSpreadSheetRow,x) />
</cfloop>
<!---Blank Row--->
<cfset spreadsheetAddRow(s, "") />
<cfset currentSpreadSheetRow = currentSpreadSheetRow + 1 />
</cfloop>
<cfheader name="content-disposition" value="attachment; filename=#vFileName#_Reconciled.xls">
<cfcontent type="application/msexcel" variable="#spreadsheetReadBinary(s)#" reset="true">
I've considered using Ben Nadel's POI Utility cfc, in which case I'd create a new query and populate it the same way I'm populating the spreadsheet (3 rows for every 1 row in qReconciled), and feed the query to writeSingleExcel().
Yes, using the POI Utility cfc cleared the problem up. What used to run for 30 minutes and time out now only takes about 90 seconds. I've posted the new code for the above page, and a new cfc function I created, below.
What I did in place of using the spreadsheet object was create a new query object, loop over qReconciled, and populate an array for each of the 39 columns in that query object. With each loop iteration, all of the 39 arrays get values for a "STARS" row, a "SACAP" row, and a blank row. Then once all iterations are done, I use queryAddColumn() to populate all of the columns in the query object, and then feed that query to poiUtility.writeSingleExcel(). The excel file is then saved to a folder on the server, but is also automatically downloaded to the user's machine, by virtue of the cfheader/cfcontent tags.
Action page:
<!---Originally used cf spreadsheet object to create Excel Export.
But this method used up too much memory; page would hang indefinitely on Production. Now using Ben Nadel's POI Utility instead.--->
<!---dates for from and to come in the format "yyyy-mm-dd"--->
<!---NOTE- if doing a date range without a specific importId, include ImportId as one of the columns in the report--->
<cfif len(trim(url.from & url.to)) AND NOT ( isDate(url.from) AND isDate(url.to) )>
<cfset url.from = "" />
<cfset url.to = "" />
</cfif>
<cfset vImportName = "" />
<cfset vDateTitle = "" />
<cfset oStarsImporter = CreateObject("component", "#application.cfcpath#.starsImporter") />
<cfset oPOI = CreateObject("component", "#application.cfcpath#.poiUtility").init() />
<cfset qReconciled = oStarsImporter.getReconciled(url.from, url.to) />
<cfset qDisplay = oStarsImporter.prepForExport(qReconciled) />
<cfif isDate(url.from) AND isDate(url.to)>
<cfif len(trim(vImportName))>
<cfset vImportName = vImportname & "-" />
</cfif>
<cfset vDateTitle = "From_" & dateFormat(url.from, "mm-dd-yyyy") & "_to_" & dateFormat(url.to, "mm-dd-yyyy") />
</cfif>
<cfset vFileName = vImportName & vDateTitle />
<cfif NOT len(trim(vFileName))>
<cfset vFileName = "All_reconciled_" & dateFormat(now(), "mm-dd-yyyy") />
<cfelse>
<cfset vFileName = vFileName & "_reconciled" />
</cfif>
<cfset oPOI.WriteSingleExcel(
FilePath=application.starsExportDirectory & vFileName & ".xls"
,query=qDisplay
,columnList="Assessment_Source,Client_Last,Client_First,SSN,Gender,Client_Case_Number,DoB,Zip_Code,Assessment_Type,Assessment_Status,NYCWAY_Appt_Code,Appt_Date,Appt_Time,CASAC_Worker,Approving_Supervisor,Benefit_Status_Case_Type,Final_Recommendation,Site_Code,Re_Assessment,Current_Living_Situation,Mental_Conditions,Candidate_for_WeCARE,Attend_Substance_Use,Agency_Name,Currently_Attending_Treatment_Program_Name,Type_of_Treatment,Opioid_Taken,Housing_Outcome,Return_to_Treatment_Provider,Non_HRA_Approved,Name_non_HRA_Site,New_Mandate,Transfer_to_New_Treatment_Provider,Check_in,Assessment_Start,Checked_Out_Time,Checked_in_Site,Addendum,Consent_B_signed"
,columnNames="Assessment Source,Client Last,Client First,SSN,Gender,Client Case Number,DoB,Zip Code,Assessment Type,Assessment Status,NYCWAY Appt Code,Appt Date,Appt Time,CASAC Worker,Approving Supervisor,Benefit Status (Case Type),Final Recommendation,Site Code,Re-Assessment,Current Living Situation,Mental Conditions,Candidate for WeCARE,Attend Substance Use,Agency Name,Currently Attending Treatment Program Name,Type of Treatment,Opioid Taken,Housing Outcome,Return to Treatment Provider,Non HRA-Approved,Name non-HRA Site,New Mandate,Transfer to New Treatment Provider,Check-in,Assessment Start,Checked Out Time,Checked-in Site,Addendum,Consent B signed"
)/>
<cfheader name="Content-Disposition" value="inline; filename=#vFileName#.xls">
<cfcontent type="application/vnd.ms-excel" file="#application.starsExcelExportURL##vFileName#.xls">
prepForExport():
<cffunction name="prepForExport" returntype="query" hint="Preps query of reconciled assessments for Excel export">
<cfargument name="qReconciled" type="query" required="true" />
<cfset var qryRet = queryNew("") />
<cfset var arrAssessmentSource = arrayNew(1) />
<cfset var arrClientLast = arrayNew(1) />
<cfset var arrClientFirst = arrayNew(1) />
<cfset var arrSSN = arrayNew(1) />
<cfset var arrGender = arrayNew(1) />
<cfset var arrCaseNumber = arrayNew(1) />
<cfset var arrDob = arrayNew(1) />
<cfset var arrZip = arrayNew(1) />
<cfset var arrAssessmentType = arrayNew(1) />
<cfset var arrAssessmentStatus = arrayNew(1) />
<cfset var arrNYCApptCode = arrayNew(1) />
<cfset var arrApptDate = arrayNew(1) />
<cfset var arrApptTime = arrayNew(1) />
<cfset var arrCasacWorker = arrayNew(1) />
<cfset var arrApprovingSup = arrayNew(1) />
<cfset var arrBenStatus = arrayNew(1) />
<cfset var arrFinalRecommend = arrayNew(1) />
<cfset var arrSiteCode = arrayNew(1) />
<cfset var arrReAssess = arrayNew(1) />
<cfset var arrLivingSit = arrayNew(1) />
<cfset var arrMentalCond = arrayNew(1) />
<cfset var arrCandidateWecare = arrayNew(1) />
<cfset var arrAttendSubsUse = arrayNew(1) />
<cfset var arrAgencyname = arrayNew(1) />
<cfset var arrCurrentTxProg = arrayNew(1) />
<cfset var arrTypeTx = arrayNew(1) />
<cfset var arrOpioid = arrayNew(1) />
<cfset var arrHousingOutcome = arrayNew(1) />
<cfset var arrReturnToTxProv = arrayNew(1) />
<cfset var arrNonHRAApproved = arrayNew(1) />
<cfset var arrNameNonHRASite = arrayNew(1) />
<cfset var arrNewMandate = arrayNew(1) />
<cfset var arrXferToNewTxprov = arrayNew(1) />
<cfset var arrCheckIn = arrayNew(1) />
<cfset var arrAssessmentStart = arrayNew(1) />
<cfset var arrCheckOut = arrayNew(1) />
<cfset var arrCheckedInSite = arrayNew(1) />
<cfset var arrAddendum = arrayNew(1) />
<cfset var arrConsentBSigned = arrayNew(1) />
<cfset var tCheckin = "" />
<cfset var tAssessmentStart = "" />
<cfset var tCheckouttime = "" />
<cfset var tTimeInBin = "" />
<cfset var tApptBeginTime = "" />
<cfset var tApptEndTime = "" />
<cfset var tAppointmentTime = "" />
<cfset var tScheduledTime = "" />
<!---populate qryRet by inserting 3 rows for every row in qReconciled: one for STARS, one for SACAP, and one blank row as a seperator--->
<cfloop query="arguments.qReconciled">
<cfset tCheckin = timeformat(dcheckin,'short') />
<cfset tAssessmentStart = timeformat(dAssessmentStart,'short') />
<cfset tCheckouttime = timeformat(dCheckouttime,'short') />
<cfset tTimeInBin = timeformat(timeInBin,'short') />
<cfset tApptBeginTime = timeformat(dApptBeginTime,'short') />
<cfset tApptEndTime = timeformat(dApptEndTime,'short') />
<cfset tAppointmentTime = timeformat(dAppointmentdate,'short') />
<cfset tScheduledTime = timeformat(scheduledTime, 'short') />
<cfset arrayAppend(arrAssessmentSource, 'STARS') />
<cfset arrayAppend(arrAssessmentSource, 'SACAP') />
<cfset arrayAppend(arrAssessmentSource, '') />
<cfset arrayAppend(arrClientLast, vClientlname) />
<cfset arrayAppend(arrClientLast, sacap_clientLName) />
<cfset arrayAppend(arrClientLast, '') />
<cfset arrayAppend(arrClientFirst, vClientfname) />
<cfset arrayAppend(arrClientFirst, sacap_clientFName) />
<cfset arrayAppend(arrClientFirst, '') />
<cfset arrayAppend(arrSSN, vSSN) />
<cfset arrayAppend(arrSSN, sacap_ssn) />
<cfset arrayAppend(arrSSN, '') />
<cfset arrayAppend(arrGender, vGender) />
<cfset arrayAppend(arrGender, sacap_gender) />
<cfset arrayAppend(arrGender, '') />
<cfset arrayAppend(arrCaseNumber, listFirst(vClientcasenumber, '-')) />
<cfset arrayAppend(arrCaseNumber, vHRAClientId) />
<cfset arrayAppend(arrCaseNumber, '') />
<cfset arrayAppend(arrDob, dateFormat(dDOB, 'mm/dd/yyyy')) />
<cfset arrayAppend(arrDob, dateFormat(vAge, 'mm/dd/yyyy')) />
<cfset arrayAppend(arrDob, '') />
<cfset arrayAppend(arrZip, vZipcode) />
<cfset arrayAppend(arrZip, vClientPermZip) />
<cfset arrayAppend(arrZip, '') />
<cfset arrayAppend(arrAssessmentType, vAssessmenttype) />
<cfset arrayAppend(arrAssessmentType, sacap_StarsAssessmentType) />
<cfset arrayAppend(arrAssessmentType, '') />
<cfset arrayAppend(arrAssessmentStatus, vAssessmentstatus) />
<cfset arrayAppend(arrAssessmentStatus, '') />
<cfset arrayAppend(arrAssessmentStatus, '') />
<cfset arrayAppend(arrNYCApptCode, vNycwayappointmentcode) />
<cfset arrayAppend(arrNYCApptCode, sacap_clienttype) />
<cfset arrayAppend(arrNYCApptCode, '') />
<cfset arrayAppend(arrApptDate, dateFormat(dAssessmentStart, 'mm/dd/yyyy')) />
<cfset arrayAppend(arrApptDate, dateFormat(dCasacDate, 'mm/dd/yyyy')) />
<cfset arrayAppend(arrApptDate, '') />
<cfset arrayAppend(arrApptTime, tAppointmentTime) />
<cfset arrayAppend(arrApptTime, tScheduledtime) />
<cfset arrayAppend(arrApptTime, '') />
<cfset arrayAppend(arrCasacWorker, vCasacworker) />
<cfset arrayAppend(arrCasacWorker, vEmpFName & ' ' & vEmpLName) />
<cfset arrayAppend(arrCasacWorker, '') />
<cfset arrayAppend(arrApprovingSup, vApprovingsupervisor) />
<cfset arrayAppend(arrApprovingSup, supervisor_fname & ' ' & supervisor_lname) />
<cfset arrayAppend(arrApprovingSup, '') />
<cfset arrayAppend(arrBenStatus, vBenefitstatus_casetype) />
<cfset arrayAppend(arrBenStatus, sacap_benefitstatus) />
<cfset arrayAppend(arrBenStatus, '') />
<cfset arrayAppend(arrFinalRecommend, vFinalrecommendation) />
<cfset arrayAppend(arrFinalRecommend, sacap_finalRecommendation) />
<cfset arrayAppend(arrFinalRecommend, '') />
<cfset arrayAppend(arrSiteCode, vSitecode) />
<cfset arrayAppend(arrSiteCode, vHRACode) />
<cfset arrayAppend(arrSiteCode, '') />
<cfset arrayAppend(arrReAssess, dReassessment) />
<cfset arrayAppend(arrReAssess, sacap_reassessment) />
<cfset arrayAppend(arrReAssess, '') />
<cfset arrayAppend(arrLivingSit, vCurrentlivingsituation) />
<cfset arrayAppend(arrLivingSit, '') />
<cfset arrayAppend(arrLivingSit, '') />
<cfset arrayAppend(arrMentalCond, vMentalconditions) />
<cfset arrayAppend(arrMentalCond, vMHdiagnoses) />
<cfset arrayAppend(arrMentalCond, '') />
<cfset arrayAppend(arrCandidateWecare, vCandidateforwecare) />
<cfset arrayAppend(arrCandidateWecare, sacap_wecaretype) />
<cfset arrayAppend(arrCandidateWecare, '') />
<cfset arrayAppend(arrAttendSubsUse, vAttendsubstanceuse) />
<cfset arrayAppend(arrAttendSubsUse, '') />
<cfset arrayAppend(arrAttendSubsUse, '') />
<cfset arrayAppend(arrAgencyname, vAgencyname) />
<cfset arrayAppend(arrAgencyname, sacap_projects) />
<cfset arrayAppend(arrAgencyname, '') />
<cfset arrayAppend(arrCurrentTxProg, vTreatmentprogramname) />
<cfset arrayAppend(arrCurrentTxProg, '') />
<cfset arrayAppend(arrCurrentTxProg, '') />
<cfset arrayAppend(arrTypeTx, vTypeoftreatment) />
<cfset arrayAppend(arrTypeTx, '') />
<cfset arrayAppend(arrTypeTx, '') />
<cfset arrayAppend(arrOpioid, vOpioidtaken) />
<cfset arrayAppend(arrOpioid, sacap_buprenorphine) />
<cfset arrayAppend(arrOpioid, '') />
<cfset arrayAppend(arrHousingOutcome, iHousingoutcome) />
<cfset arrayAppend(arrHousingOutcome, sacap_housingstatus) />
<cfset arrayAppend(arrHousingOutcome, '') />
<cfset arrayAppend(arrReturnToTxProv, vReturntotreatmentprovider) />
<cfset arrayAppend(arrReturnToTxProv, sacap_encounteroutcome) />
<cfset arrayAppend(arrReturnToTxProv, '') />
<cfset arrayAppend(arrNonHRAApproved, vNonhraapproved) />
<cfset arrayAppend(arrNonHRAApproved, sacap_nonhraapproved) />
<cfset arrayAppend(arrNonHRAApproved, '') />
<cfset arrayAppend(arrNameNonHRASite, vNamenonhrasite) />
<cfset arrayAppend(arrNameNonHRASite, sacap_namenonhrasite) />
<cfset arrayAppend(arrNameNonHRASite, '') />
<cfset arrayAppend(arrNewMandate, vNewhramandate) />
<cfset arrayAppend(arrNewMandate, iSNewlyMandated) />
<cfset arrayAppend(arrNewMandate, '') />
<cfset arrayAppend(arrXferToNewTxprov, vTransfertonewtreatmentprovider) />
<cfset arrayAppend(arrXferToNewTxprov, sacap_transferreason) />
<cfset arrayAppend(arrXferToNewTxprov, '') />
<cfset arrayAppend(arrCheckIn, tCheckin) />
<cfset arrayAppend(arrCheckIn, tTimeInBin) />
<cfset arrayAppend(arrCheckIn, '') />
<cfset arrayAppend(arrAssessmentStart, tAssessmentStart) />
<cfset arrayAppend(arrAssessmentStart, tApptBeginTime) />
<cfset arrayAppend(arrAssessmentStart, '') />
<cfset arrayAppend(arrCheckOut, tCheckouttime) />
<cfset arrayAppend(arrCheckOut, tApptEndTime) />
<cfset arrayAppend(arrCheckOut, '') />
<cfset arrayAppend(arrCheckedInSite, vCheckedinsite) />
<cfset arrayAppend(arrCheckedInSite, sacap_sacapsite) />
<cfset arrayAppend(arrCheckedInSite, '') />
<cfset arrayAppend(arrAddendum, vAddendum) />
<cfset arrayAppend(arrAddendum, '') />
<cfset arrayAppend(arrAddendum, '') />
<cfset arrayAppend(arrConsentBSigned, vConsentbsigned) />
<cfset arrayAppend(arrConsentBSigned, '') />
<cfset arrayAppend(arrConsentBSigned, '') />
</cfloop>
<cfset queryAddColumn(qryRet, "Assessment_Source", "varchar", arrAssessmentSource) />
<cfset queryAddColumn(qryRet, "Client_Last", "varchar", arrClientLast) />
<cfset queryAddColumn(qryRet, "Client_First","varchar", arrClientFirst) />
<cfset queryAddColumn(qryRet, "SSN", "varchar", arrSSN) />
<cfset queryAddColumn(qryRet, "Gender", "varchar", arrGender) />
<cfset queryAddColumn(qryRet, "Client_Case_Number", "varchar", arrCaseNumber) />
<cfset queryAddColumn(qryRet, "DoB", "varchar", arrDob) />
<cfset queryAddColumn(qryRet, "Zip_Code", "varchar", arrZip) />
<cfset queryAddColumn(qryRet, "Assessment_Type", "varchar", arrAssessmentType) />
<cfset queryAddColumn(qryRet, "Assessment_Status", "varchar", arrAssessmentStatus) />
<cfset queryAddColumn(qryRet, "NYCWAY_Appt_Code", "varchar", arrNYCApptCode) />
<cfset queryAddColumn(qryRet, "Appt_Date", "varchar", arrApptDate) />
<cfset queryAddColumn(qryRet, "Appt_Time", "varchar", arrApptTime) />
<cfset queryAddColumn(qryRet, "CASAC_Worker", "varchar", arrCasacWorker) />
<cfset queryAddColumn(qryRet, "Approving_Supervisor", "varchar", arrApprovingSup) />
<cfset queryAddColumn(qryRet, "Benefit_Status_Case_Type", "varchar", arrBenStatus) />
<cfset queryAddColumn(qryRet, "Final_Recommendation", "varchar", arrFinalRecommend) />
<cfset queryAddColumn(qryRet, "Site_Code", "varchar", arrSiteCode) />
<cfset queryAddColumn(qryRet, "Re_Assessment", "varchar", arrReAssess) />
<cfset queryAddColumn(qryRet, "Current_Living_Situation", "varchar", arrLivingSit) />
<cfset queryAddColumn(qryRet, "Mental_Conditions", "varchar", arrMentalCond) />
<cfset queryAddColumn(qryRet, "Candidate_for_WeCARE", "varchar", arrCandidateWecare) />
<cfset queryAddColumn(qryRet, "Attend_Substance_Use", "varchar", arrAttendSubsUse) />
<cfset queryAddColumn(qryRet, "Agency_Name", "varchar", arrAgencyname) />
<cfset queryAddColumn(qryRet, "Currently_Attending_Treatment_Program_Name", "varchar", arrCurrentTxProg) />
<cfset queryAddColumn(qryRet, "Type_of_Treatment", "varchar", arrTypeTx) />
<cfset queryAddColumn(qryRet, "Opioid_Taken", "varchar", arrOpioid) />
<cfset queryAddColumn(qryRet, "Housing_Outcome", "varchar", arrHousingOutcome) />
<cfset queryAddColumn(qryRet, "Return_to_Treatment_Provider", "varchar", arrReturnToTxProv) />
<cfset queryAddColumn(qryRet, "Non_HRA_Approved", "varchar", arrNonHRAApproved) />
<cfset queryAddColumn(qryRet, "Name_non_HRA_Site", "varchar", arrNameNonHRASite) />
<cfset queryAddColumn(qryRet, "New_Mandate", "varchar", arrNewMandate) />
<cfset queryAddColumn(qryRet, "Transfer_to_New_Treatment_Provider", "varchar", arrXferToNewTxprov) />
<cfset queryAddColumn(qryRet, "Check_in", "varchar", arrCheckIn) />
<cfset queryAddColumn(qryRet, "Assessment_Start", "varchar", arrAssessmentStart) />
<cfset queryAddColumn(qryRet, "Checked_Out_Time", "varchar", arrCheckOut) />
<cfset queryAddColumn(qryRet, "Checked_in_Site", "varchar", arrCheckedInSite) />
<cfset queryAddColumn(qryRet, "Addendum", "varchar", arrAddendum) />
<cfset queryAddColumn(qryRet, "Consent_B_signed", "varchar", arrConsentBSigned) />
<cfreturn qryRet />
</cffunction>