CFZip功能的问题 [英] Issues with CFZip functionality

问题描述

我有以下工作半途。我试图 CFZip 下载的Excel文件的内容，但有一个问题涉及。下面的代码显示有3张票。 1张主票和2张是主票扩展答案的票。

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.

当我尝试下载Excel文件时，我还试图下载两个文件夹，这些文件夹应该是里面的Zip文件。一个应该指向孩子，一个指向父母。两者都应有各自的附件。我在这里缺少一些大块代码。

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.

这里是代码：

<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">

推荐答案

它。

    <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 --->
        <cfset Temp = GetDirectoryFromPath(GetTemplatePath()) & 'temp'>
        <cfif !DirectoryExists(Temp)>
            <cfdirectory action="create" directory="#Temp#">
        </cfif>
        <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>
          <cfif getAttachments.recordcount>
            <!--- handle the attachments for the Ticket in ZIp Format --->
            <cfset currentDirectory = "MainTicketID_" & "Number_" & getAttachments.SupportTicketID & "_Attachments">
            <cfdump var="#temp#\#currentDirectory#">
            <cfif !DirectoryExists("#temp#\#currentDirectory#")>
                <cfdirectory action="create" directory="#temp#\#currentDirectory#">
            </cfif>
            <cfdump var="#currentDirectory#">
                <cfif getAttachments.recordcount> 
                <cfloop query="getAttachments">   
                    <cffile action="copy" source="#ExpandPath('attachments/#getAttachments.Attach_FileName#')#" 
                  destination="#temp#\#currentDirectory#\">
                </cfloop> 
                </cfif>
          </cfif>
        <cfif getChkAttachments.recordcount>
            <cfset currentDirectory2 = "MainTicket_" & getChkAttachments.Support_SupportTicketID & "_Updated_Child_" & getChkAttachments.Support_SupportTktHistoryID & "_Attachments">
            <cfif !DirectoryExists("#temp#\#currentDirectory2#")>
              <cfdirectory action="create" directory="#temp#\#currentDirectory2#">
            </cfif>
                <cfif getChkAttachments.recordcount>    
                <cfloop query="getChkAttachments">   
                    <cffile action="copy" source="#ExpandPath('attachments/#getChkAttachments.Support_Attach_FileName#')#" 
                    destination="#temp#\#currentDirectory2#\">
                </cfloop>
                </cfif>
        </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#" overwrite="true">
             <cfzipparam source="#temp#"/>
             <cfzipparam source="#filename#"/>
          </cfzip>
        </cfif>
        </cfoutput>
      </cfif>
    <cfdirectory action="delete" directory="#temp#" recurse="yes">
  <cfheader name="Content-Disposition" value="attachment;filename=#ListFirst(filename,'.')#.zip">
  <cfcontent file="#dest2#" type="application/zip" deletefile="true" reset="true">

这可以帮助别人改变如何嵌套cfzip功能

This can help someone instead how to nest the cfzip functionality

这篇关于CFZip功能的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！