<script src="_scripts/dictionary.js" type="text/javascript" language="javascript"></script>
<script src="../../../_scripts/dictionary.js" type="text/javascript" language="javascript"></script>
<cfoutput>
    <cfparam name="url.reportkey" default="None">
    <div id="selectreport">
        <table align="left">
            <tr>
                  <td>
                    <button name="exportreport" style="font-weight:bold;" onclick="Excel()">Export Report to Excel</button>
                </td>
            </tr>
        </table>
    </div>    
    <script>
        function Excel()
        {
            var reportkey = '#url.reportkey#';
            if(reportkey == null)
            {
                alert('not working');
                return;
            }
            alert(reportkey);
            <cftry>
                createExcel();
                <cfcatch type="any">
                    <cfset local.name = 'dictionary.cfm'>
                    <cfset sCurrent = #CFCATCH.TAGCONTEXT[1]#>
                    <cflog text=" Error in #local.name# : #cfcatch.message# : Caught an exception, type = #CFCATCH.TYPE# : #CFCATCH.detail# : The error occurred near statement/line - #sCurrent['ID']# (#sCurrent['LINE']#,#sCurrent['COLUMN']#) #sCurrent['TEMPLATE']# " type="Error" file="customReports" date=now time=now>    
                </cfcatch>
            </cftry>
        }
        function createExcel()
        {
            <cfquery name="getReportData" datasource="#session.sysdb#">
                select *
                from DictionaryReport
                where ReportKey = #reportkey#
                order by ReportKey
            </cfquery>
            <cfobject name="xls" component="_components/excel">
            <cfset sheets = ArrayNew(1)>
            <cfif isDefined("session.OutputDirectory")>
                  <cfset output = session.OutputDirectory>
            <cfelse>
                 <cfset output = "c:\temp\">
            </cfif>
            <cfset columnListArray = getMetaData(getReportData())>
            <cfset columnList = "">
            <cfset columnNames = "">
            <cfset columnNames = ListAppend(columnNames, 'Report Key')>
            <cfset columnNames = ListAppend(columnNames, 'Report Name')>
            <cfset columnNames = ListAppend(columnNames, 'Report Description')>
            <cfset columnNames = ListAppend(columnNames, 'Module')>
            <cfset columnNames = ListAppend(columnNames, 'Report Type')>
            <cfset columnNames = ListAppend(columnNames, 'Entity')>
            <cfset columnNames = ListAppend(columnNames, 'Version Added')>
            <cfset columnNames = ListAppend(columnNames, 'Visible')>
            <cfset columnNames = ListAppend(columnNames, 'Main Table')>
            <cfset columnNames = ListAppend(columnNames, 'Display Order')>
            <cfloop index="i" from="1" to="#arrayLen(columnListArray)#" >
                <cfset columnList = ListAppend(columnList, columnListArray[i].name)>
            </cfloop>
            <cfset sheets[1] = StructNew()>
            <cfset sheets[1].Query = getReportData()>
            <cfset sheets[1].ColumnList = columnList>
            <cfset sheets[1].ColumnNames = columnNames>
            <cfset sheets[1].SheetName = "Report Data">
            <cfset tempFileName = "ReportData_#RandRange(100,10000)#.xls">
            <cfset test = xls.WriteExcel('#output##tempFileName#','#sheets#',',')>
            <cfheader name="Pragma" value="">
            <cfheader name="Cache-control" value="">
            <CF_PROMPT4DOWNLOAD FILENAME="#tempFileName#" DIRECTORY="#output#">
        }
    </script>
</cfoutput>

<script src="_scripts/dictionary.js" type="text/javascript" language="javascript"></script>
<script src="../../../_scripts/dictionary.js" type="text/javascript" language="javascript"></script>
<cfoutput>
    
<script>
    function getExcel(){
        var reportkey = document.getElementById('reporttypeselect').value;
        if(reportkey == '')
        {
            alert('you must select a report');
            return;
        }
        alert(reportkey);
        window.open('excelAction.cfm?reportkey=' + reportkey);
    }
</script>
    
    <!---Determine Edit Permissions. --->
    <cfif isDefined("session.report.permission.Admin.Management.User.Dictionary")>
            <cfset onchangeaction = "selectReport(this);">
        <cfelse>
            <cfset onchangeaction = "">
    </cfif>

    <cfquery name="qryReports" datasource="#session.sysdb#">
        SELECT ReportKey, ReportName, ReportDescription, [Module], ReportType, Entity
        FROM DictionaryReport
        <cfif not isDefined("session.report.permission.Admin.Management.User.Dictionary")>
            WHERE Visible = <cfqueryparam value="1" cfsqltype="bit">
        </cfif>
        ORDER BY DisplayOrder, ReportName
    </cfquery>
    
    <div id="selectreport">
        <table align="center">
            <tr>
                <td colspan="2" align="center" style="font-weight:bold;font-size:16px;">Data Dictionary</td>
            </tr>
        </table>
        <table align="left">
            <tr>
                  <td style="font-weight:bold;font-size:14px;" nowrap><label class='reportlabel'>Select a Report Type to Export to Excel:</label></td>
                  <td><select name="reporttypeselect" id="reporttypeselect" onchange="#onchangeaction#" style="width:300px;">
                      <option value="">Choose A Report</option>
                      <cfloop query="qryReports">
                           <option value="#qryReports.ReportKey#">#qryReports.Module# #qryReports.ReportName#</option>
                       </cfloop>
                       </select>
                    <button name="exportreport" style="font-weight:bold;" onclick="getExcel()">Export Report to Excel</button>
                </td>
            </tr>
        </table>
    </div>
</cfoutput>

<cffunction name="generateExcel" output="Yes">
<cfargument name="theQuery" type="query">
<cfargument name="columnList" type="string" default="">
<cfargument name="mode" type="string" default="save">
<cfargument name="theFilename" type="string" default="report.xls">

<cfset var TAB = chr(9)>
<cfset var i = 1>
<cfset var headerArray = ArrayNew(1)>

<cfparam name="url.reportkey" default="None">
<cfsetting enablecfoutputonly="Yes">

<!-- Dump all content before this fn call -->
<cfcontent reset="yes">

<!-- Set up the columnList -->
<cfset ARGUMENTS.columnList = ListAppend(ARGUMENTS.columnList, 'Report Key')>
<cfset ARGUMENTS.columnList = ListAppend(ARGUMENTS.columnList, 'Report Name')>
<cfset ARGUMENTS.columnList = ListAppend(ARGUMENTS.columnList, 'Report Description')>
<cfset ARGUMENTS.columnList = ListAppend(ARGUMENTS.columnList, 'Module')>
<cfset ARGUMENTS.columnList = ListAppend(ARGUMENTS.columnList, 'Report Type')>
<cfset ARGUMENTS.columnList = ListAppend(ARGUMENTS.columnList, 'Entity')>
<cfset ARGUMENTS.columnList = ListAppend(ARGUMENTS.columnList, 'Version Added')>
<cfset ARGUMENTS.columnList = ListAppend(ARGUMENTS.columnList, 'Visible')>
<cfset ARGUMENTS.columnList = ListAppend(ARGUMENTS.columnList, 'Main Table')>
<cfset ARGUMENTS.columnList = ListAppend(ARGUMENTS.columnList, 'Display Order')>

<!-- Gets the data -->
<cfquery name="theQuery" datasource="#session.sysdb#">
    select *
    from DictionaryReport
    where ReportKey = '#url.reportkey#'
    order by ReportKey
</cfquery>

<!-- Set up the file and location for spreadsheet to be saved in -->
<cfset guid = CreateUUID()>
<cfset location = "c:\temp\">
<cfset filename = location & "ReportData_" & guid & ".xls">
<cfset REQUEST.headerDoneFlag = true>
<cfheader name="Content-type" value="application/octet-stream">
<cfheader name="Content-Disposition" value="attachment;filename=""#ARGUMENTS.theFilename#""">
<cfsavecontent variable="output">
    <cfset generateExcel(ARGUMENTS.theQuery, ARGUMENTS.columnList, "save")>
</cfsavecontent><cfoutput>#output#</cfoutput><cfabort>
<cffile action="WRITE" file="#filename#" output="#output#">
<cfcontent type="application/msexcel" file="#filename#" deleteFile="Yes">
    
<!-- Write the header row -->
<cfloop index="i" from="1" to="#ListLen(ARGUEMENTS.columnList)#">
    <cfset item = Trim(ListGetAt(ARGUEMENTS.columnList, i))>
    <cfset headerArray[ArrayLen(headerArray )+1] = item>
    <cfoutput>#item##TAB#</cfoutput>
</cfloop>
<cfoutput>#chr(13)#</cfoutput>

<!-- Write all the sub rows --->
<cfloop query="ARGUMENTS.theQuery">
    <cfloop index="i" from="1" to="#ArrayLen(headerArray)#">
        <cfset dbVal = Trim(ARGUMENTS.theQuery[headerArray[i]][CurrentRow])>
        <cfif IsDate(dbVal)>
            <cfset dbVal = DateFormat(dbVal, "mm/dd/yyyy")>
        <cfelse>
            <cfset dbVal = replaceList(dbVal,"#TAB#,#chr(10)#,#chr(13)#"," , , ")>
        </cfif>
        <cfoutput>#dbVal##TAB#</cfoutput>
    </cfloop>
    <cfoutput>#chr(13)#</cfoutput>
</cfloop>

<cfsetting enablecfoutputonly="No">
</cffunction>

<!-- use cfsetting to block output of HTML
outside of cfoutput tags -->
<cfsetting enablecfoutputonly="Yes">
<cfparam name="url.reportkey" default="None">
<cfsetting showdebugoutput="no">

<!-- get employee info -->
<cfquery name="getReport" datasource="#session.sysdb#">
  SELECT *
FROM DictionaryReport
</cfquery>

<!-- set vars for special chars -->
<cfset TabChar = Chr(9)>
<cfset NewLine = Chr(13) & Chr(10)>
<!-- set content type to invoke Excel -->
<cfcontent type="application/vnd.ms-excel">

<!-- suggest default name for XLS file -->
<!-- use "Content-Disposition" in cfheader for Internet Explorer  -->
<cfheader name="Content-Disposition" value="filename=Report_Data.xls">

<!-- output data using cfloop & cfoutput -->
<cfloop query="getReport">
  <cfoutput>#ReportKey##TabChar##ReportName#
  #TabChar##ReportDescription##TabChar##Module##NewLine#</cfoutput>
</cfloop>

<cfsetting showdebugoutput="false">
<cfheader name="content-disposition" value="inline;filename=#url.reportkey#_Report_Data.xls">
<cfcontent type="application/msexcel">
<cfparam name="url.reportkey" default="None">
<cfquery name="getReport" datasource="#session.sysdb#">
SELECT *
FROM DictionaryReport
WHERE ReportKey = '#url.reportkey#'
</cfquery>
<cfquery name="getTable" datasource="#session.sysdb#">
SELECT *
FROM DictionaryTable
WHERE ReportKey = '#url.reportkey#'
</cfquery>
<?xml version="1.0"?>
<Workbook
xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">

   <Worksheet ss:Name="Report Data">
      <Table>
        <Row>
            <Cell ss:Index="1"><Data ss:Type="String">Report Key</Data></Cell>
            <Cell ss:Index="2"><Data ss:Type="String">Report Name</Data></Cell>
            <Cell ss:Index="3"><Data ss:Type="String">Module</Data></Cell>
            <Cell ss:Index="4"><Data ss:Type="String">Report Type</Data></Cell>
            <Cell ss:Index="5"><Data ss:Type="String">Entity</Data></Cell>
            <Cell ss:Index="6"><Data ss:Type="String">Version Added</Data></Cell>
            <Cell ss:Index="7"><Data ss:Type="String">Visible</Data></Cell>
            <Cell ss:Index="8"><Data ss:Type="String">Main Table</Data></Cell>
            <Cell ss:Index="9"><Data ss:Type="String">Display Order</Data></Cell>
            <Cell ss:Index="10"><Data ss:Type="String">Report Description</Data></Cell>
        </Row>
        <Row></Row>
        <cfoutput query="getReport">
        <Row>
            <Cell ss:Index="1"><Data ss:Type="String">#ReportKey#</Data></Cell>
            <Cell ss:Index="2"><Data ss:Type="String">#ReportName#</Data></Cell>
            <Cell ss:Index="3"><Data ss:Type="String">#Module#</Data></Cell>
            <Cell ss:Index="4"><Data ss:Type="String">#ReportType#</Data></Cell>
            <Cell ss:Index="5"><Data ss:Type="String">#Entity#</Data></Cell>
            <Cell ss:Index="6"><Data ss:Type="String">#VersionAdded#</Data></Cell>
            <Cell ss:Index="7"><Data ss:Type="Number">#Visible#</Data></Cell>
            <Cell ss:Index="8"><Data ss:Type="String">#MainTable#</Data></Cell>
            <Cell ss:Index="9"><Data ss:Type="Number">#DisplayOrder#</Data></Cell>
            <Cell ss:Index="10"><Data ss:Type="String">#ReportDescription#</Data></Cell>
        </Row>
        </cfoutput>
      </Table>
   </Worksheet>
  
   <Worksheet ss:Name="Table Data">
      <Table>
        <Row>
            <Cell ss:Index="1"><Data ss:Type="String">Report Key</Data></Cell>
            <Cell ss:Index="2"><Data ss:Type="String">Table Key</Data></Cell>
            <Cell ss:Index="3"><Data ss:Type="String">Table Name</Data></Cell>
            <Cell ss:Index="4"><Data ss:Type="String">Table Short Name</Data></Cell>
            <Cell ss:Index="5"><Data ss:Type="String">Table Description</Data></Cell>
            <Cell ss:Index="6"><Data ss:Type="String">Table Ordinality</Data></Cell>
            <Cell ss:Index="7"><Data ss:Type="String">Display Order</Data></Cell>
            <Cell ss:Index="8"><Data ss:Type="String">Parent Key</Data></Cell>
            <Cell ss:Index="9"><Data ss:Type="String">Visible</Data></Cell>
        </Row>
        <Row></Row>
        <cfoutput query="getTable">
         <Row>
            <Cell ss:Index="1"><Data ss:Type="String">#ReportKey#</Data></Cell>
            <Cell ss:Index="2"><Data ss:Type="String">#TableKey#</Data></Cell>
            <Cell ss:Index="3"><Data ss:Type="String">#TableName#</Data></Cell>
            <Cell ss:Index="4"><Data ss:Type="String">#TableShortName#</Data></Cell>
            <Cell ss:Index="5"><Data ss:Type="String">#TableDescription#</Data></Cell>
            <Cell ss:Index="6"><Data ss:Type="String">#TableOrdinality#</Data></Cell>
            <Cell ss:Index="7"><Data ss:Type="Number">#DisplayOrder#</Data></Cell>
            <Cell ss:Index="8"><Data ss:Type="String">#ParentKey#</Data></Cell>
            <Cell ss:Index="9"><Data ss:Type="Number">#Visible#</Data></Cell>
         </Row>
        </cfoutput>
      </Table>
   </Worksheet>
  
</Workbook>