Best method in writing this ColdFusion based repor

问题:

I'm looking to see what the best way in writing this report which I'm developing in ColdFusion 10 would be.

Basically, it consists of reading data from two tables in a MSSQL database, applying some conditions based on what it finds on a specific column, populating the data into excel and sending it out via email. I've written reports before where it's as simple as reading data from the table, using POIUTILITY to create the spreadsheet and sending it via email, but this one is a little different because of the conditions. I've read about using cfspreadsheet instead of poiutility, but I'm not so sure in this case.

Here’s an example of what the tables layout looks like as easy as I can put it:

Table1
ID | Name | Address

Table2 
ID | AppsInfo

In the excel report, I’ll have one sheet where the data is laid out similar to the following:

ID    | Name  | Address | AppAlpha | AppBravo | AppDelta | 
12345 | John  | 123 Ave | Yes      | Yes      | No       | 

My problem is, the AppsInfo column on table2 contains xml formatting for each ID :

<start>
<id=”12345”>
<AppName=”AppDelta”>
<AppName=”AppBravo”
</id>
</start>

In each row on my excel sheet, the data would be written out such that if appsinfo column for a specific ID contains an app, then list it as yes on the corresponding row, if it does not contain that app, then No.

So going from layout example above, the final format of excel would display this way:

ID    | Name  | Address | AppAlpha | AppBravo | AppDelta |
12345 | John  | 123 Ave | No       | Yes      | Yes      |

and so on and so on for each ID….

What would be the best way in developing this so if it contains specific apps on table2 appsinfo column, it will write it as Yes and if it does not, write no for each ID on the corresponding row?

回答1:

Leigh beat me to the answer, but I had a similar solution using the XML abilities of SQL. XML can be a pain to work with, but SQL Server has a syntax for working with XML.

SELECT t1.ID
    , t1.Name
    , t1.Address
    --, t2.AppsInfo
    , CASE WHEN t2.AppsInfo.exist('//start/id/AppName[@value="AppAlpha"]') = 1 THEN 'Yes' ELSE 'No' END AS AppAlpha
    , CASE WHEN t2.AppsInfo.exist('//start/id/AppName[@value="AppBravo"]') = 1 THEN 'Yes' ELSE 'No' END AS AppBravo
    , CASE WHEN t2.AppsInfo.exist('//start/id/AppName[@value="AppDelta"]') = 1 THEN 'Yes' ELSE 'No' END AS AppDelta
FROM #table1 t1
INNER JOIN #table2 t2 ON t1.ID = t2.ID

My setup was:

Create TABLE #table1 ( ID int, Name varchar(100), Address varchar(200) )
Create TABLE #table2 ( ID int, AppsInfo xml )

INSERT INTO #table1 (ID, Name, Address)
SELECT 1, 'John', '123 Sesame St'
UNION ALL
SELECT 2, 'Jim', '42 Douglas Ln'
UNION ALL
SELECT 3, 'Jack', '1 Elm St'
UNION ALL
SELECT 4, 'Joe', '21 Jump St'

INSERT INTO #table2 (ID, AppsInfo)
SELECT 1, '<start><id value="1"><AppName value="AppDelta"/><AppName value="AppBravo"/></id></start>'
UNION ALL
SELECT 2, '<start><id value="2"><AppName value="AppAlpha"/><AppName value="AppDelta"/><AppName value="AppBravo"/></id></start>'
UNION ALL 
SELECT 3, '<start><id value="3"><AppName value="AppBravo"/></id></start>'
UNION ALL 
SELECT 4, '<start><id value="4"><AppName/></id></start>'

I also had to modify the XML slightly to make it valid. The only real difference between my solution and Leigh's is that I'm using SQL CASEs to output the Yes/No instead of doing that in Excel. Either way would work just as well.

The structure of the actual XML you are using could have an effect on these queries, though. Are you able to put a sample block of it here? And what version of SQL Server are you using? The XML syntax for SQL 2000 is a bit different than SQL 2005+ and there's not an XML datatype in SQL 2000.

To build the spreadsheet from the query, Ray Camden has a good article. And there's also the Adobe docs for cfspreadsheet.

回答2:

If there are only three application columns, another option is using SQL Server's xml functions to generate the results in sql. Simply return a bit value for each of the three columns. (Note: I tweaked the xml sample from your post to make it valid, but the SQLFiddle shows the general idea.)

SELECT t1.ID
        , t1.Name
        , t1.Address
        , t2.appsInfo.exist('//start/id/App[@Name="AppAlpha"]') AS AppAlpha
        , t2.appsInfo.exist('//start/id/App[@Name="AppBravo"]') AS AppBravo
        , t2.appsInfo.exist('//start/id/App[@Name="AppDelta"]') AS AppDelta
FROM @table1 t1 INNER JOIN @table2 t2 ON t2.ID = t1.ID

Then in your spreadsheet, apply a custom cell format to the columns to display yes/no instead of 0/1. Assuming you are using CF's built-in spreadsheet functionality, the format is:

         {dataformat='"Yes";"Yes";"No"'}

回答3:

I think the easiest and dirtiest way, would be through string detection.

<cfquery name="AppData" datasource="MyDSN">
SELECT
    t1.id, t1.name, t1.address, t2.appinfo
FROM
    table1 t1
    INNER JOIN table2 t2 ON t1.id = t2.id
</cfquery>

<cfscript>
// Return a Yes/No for App's Existence in a string
function checkApp( app, string) {
  return findNoCase( app, string ) ? 'NO' : 'YES';
}
</cfscript>

<cfoutput query="AppData">
#id# #name# #address# #checkApp( 'AppDelta', appinfo )# #checkApp( 'AppBravo', appinfo )#
</cfoutput>