I have the following which is working half way. I am trying to CFZip the contents of a downloaded Excel file but there is one problem involved. The code below shows that there are 3 tickets. 1 main ticket and 2 tickets that are the extended answers of the main ticket. Attachments are allowed on all tickets, the main ones and the children's too.
When I try to download the Excel file, I am also trying to download two folders that should be inside the Zip file. One that should be pointing to the child and one to the parent. Both should have their respective attachments. I am missing some big piece of code here.
Here is the code:
<cfquery datasource="#request.dsn#" name="mainTickets">
SELECT s.ticketID,CAST(s.DateRaised as
varchar) AS DateRaised,s.Summary,s.RaisedBy,s.AssignedTo,
st.Status
,CAST(s.LastUpdatedDate as varchar),CAST(s.TimeSpent as float) as
timespent,stt.TicketType,s.LastUpdatedComment
from supportTickets s
inner join SupportStatusType st on st.statusID = s.status
inner join supportTicketType stt on stt.TicketTypeID = s.TicketType
where s.ticketID IN (<cfqueryparam cfsqltype="cf_sql_numeric" value="#url.ticketID#" list="yes">
)
</cfquery>
<cfset filename = "Detailed_Ticket_Summary" & dateformat(now(), 'mm_dd_yyyy') & "." & "xls">
<cfset s = spreadsheetNew("Tickets Summary")>
<!--- Add header row --->
<cfset spreadsheetAddRow(s, "TicketID,Date Raised,Summary,Raised By,AssignedTo,Status,Last Updated Date, Time Spent, Ticket Type, Last Updated Comment, Details")>
<!--- format header --->
<cfset spreadsheetFormatRow(s, #format1#, 1)>
<cfset spreadsheetAddRows(s, mainTickets)>
<cfset SpreadsheetFormatColumn(s, {textwrap=true}, 10)>
<cfset SpreadsheetFormatColumn(s, {textwrap=true}, 3)>
<cfloop from="1" to="#mainTickets.recordcount#" index="i">
<cfset k = i + 1>
<cfset SpreadsheetSetCellFormula(s, 'HYPERLINK("[Tickets Summary]TicketID_#mainTickets.ticketID#!A1","Click Here")',#k#,11)>
<cfset SpreadsheetFormatCell(s, {bold=true, color='blue', bottomborder='dotted'}, #k#, 11)>
</cfloop>
<cfif mainTickets.recordcount>
<cfoutput query="mainTickets">
<cfquery datasource="#request.dsn#" name="fetchTickets">
SELECT s.ticketID,s.ticketNumber,CAST(s.DateRaised as
varchar),s.Summary,s.RaisedBy,s.AssignedTo,
st.Status
,CAST(s.LastUpdatedDate as varchar),s.TimeSpent,stt.TicketType,s.LastUpdatedComment
from supportTicketsHistory s
inner join SupportStatusType st on st.statusID = s.status
inner join supportTicketType stt on stt.TicketTypeID = s.TicketType
where s.ticketnumber = <cfqueryparam cfsqltype="cf_sql_numeric" value="#mainTickets.ticketID#">
</cfquery>
<!--- Add query --->
<cfif fetchTickets.recordcount>
<cfset SpreadsheetCreateSheet(s, 'TicketID_#ticketID#')>
<cfset SpreadsheetSetActiveSheet(s, 'TicketID_#ticketID#')>
<!--- Add header row --->
<cfset spreadsheetAddRow(s, "TicketID,Ticket Number,Date Raised,Summary,Raised By,AssignedTo,Status,Last Updated Date, Time Spent, Ticket Type, Last Updated Comment")>
<!--- format header --->
<cfset spreadsheetFormatRow(s, #format1#, 1)>
<cfset SpreadsheetAddRows(s, fetchTickets)>
<cfset SpreadsheetFormatColumn(s, {textwrap=true}, 11)>
<cfset SpreadsheetSetActiveSheetNumber(s, 1)>
<cfset ielements = ValueList(mainTickets.ticketID)>
<cfquery datasource="#request.dsn#" name="getAttachments">
SELECT Attach_FileName,Attach_FileSize,Attach_Time,SupportTicketID
FROM support_attachments
WHERE SupportTicketID = #val(fetchTickets.ticketnumber)#
</cfquery>
<cfquery datasource="#request.dsn#" name="getChkAttachments">
SELECT Support_Attach_FileName,Support_Attach_FileSize,Support_Attach_Time,
Support_SupportTktHistoryID,Support_SupportTicketID
FROM support_attach_History
WHERE Support_SupportTicketID = #val(fetchTickets.ticketnumber)#AND Support_SupportTktHistoryID
in (SELECT ss.ticketID FROM supportTicketsHistory ss
WHERE ss.ticketNumber = #val(fetchTickets.ticketnumber)#)
</cfquery>
<!--- handle the attachments for the Ticket in ZIp Format --->
<cfset currentDirectory = GetDirectoryFromPath(GetTemplatePath()) & getAttachments.SupportTicketID>
<cfif !DirectoryExists(currentDirectory)>
<cfdirectory action="create" directory="#currentDirectory#">
</cfif>
<cfset currentDirectory2 = GetDirectoryFromPath(GetTemplatePath()) & getChkAttachments.Support_SupportTktHistoryID>
<cfif !DirectoryExists(currentDirectory2)>
<cfdirectory action="create" directory="#currentDirectory2#">
</cfif>
<cfif getAttachments.recordcount>
<cfloop query="getAttachments">
<cffile action="copy"
source="#ExpandPath('attachments/#getAttachments.Attach_FileName#')#"
destination="#currentDirectory#\">
</cfloop>
</cfif>
<cfif getChkAttachments.recordcount>
<cfloop query="getChkAttachments">
<cffile action="copy"
source="#ExpandPath('attachments/#getChkAttachments.Support_Attach_FileName#')#"
destination="#currentDirectory2#\">
</cfloop>
</cfif>
<!--- handle the attachments for the Ticket in ZIP Format --->
<cfset spreadsheetWrite(s, filename, true)>
<cfset dest2 = getTempDirectory() & "/" & "Tickets" & ".zip">
<cfzip action="zip" file="#dest2#">
<cfdirectory action="list" directory="#currentDirectory#/" name="listRoot">
<cfdirectory action="list" directory="#currentDirectory2#/" name="listRoot2">
<cfset lstFiles = ValueList(listRoot.name)>
<cfset lstFiles2 = ValueList(listRoot2.name)>
<cfloop list="#lstFiles#" index="k">
<cfzipparam source="#currentDirectory#/#k#" recurse="yes" filter="*"/>
</cfloop>
<cfloop list="#lstFiles2#" index="kk">
<cfzipparam source="#currentDirectory2#/#kk#" recurse="yes" filter="*"/>
</cfloop>
</cfzip>
</cfif>
</cfoutput>
</cfif>
<cfheader name="Content-Disposition" value="attachment;filename=#ListFirst(filename,'.')#.zip">
<cfcontent file="#dest2#" type="application/zip" deletefile="true" reset="true">
I sorted it out here is the way I have done it.
This can help someone instead how to nest the cfzip functionality