Лучший способ написания этого отчета на основе ColdFusion

Я смотрю, как лучше всего написать этот отчет, который я разрабатываю в ColdFusion 10.

По сути, он состоит из чтения данных из двух таблиц в базе данных MSSQL, применения некоторых условий на основе того, что он находит в определенном столбце, заполнения данных в Excel и отправки их по электронной почте. Раньше я писал отчеты, где было так же просто, как чтение данных из таблицы, использование POIUTILITY для создания электронной таблицы и ее отправка по электронной почте, но это немного отличается из-за условий. Я читал об использовании cfspreadsheet вместо poiutility, но я не уверен в этом.

Вот пример того, как просто выглядит макет таблицы:

Table1
ID | Name | Address

Table2 
ID | AppsInfo

В отчете Excel у меня будет один лист, где данные располагаются примерно так:

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

Моя проблема в том, что столбец AppsInfo на table2 содержит форматирование xml для каждого идентификатора:

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

В каждой строке моего листа Excel данные будут записаны так, что если столбец appsinfo для определенного идентификатора содержит приложение, то укажите его в соответствующей строке как да, если в нем нет этого приложения, то нет.

Итак, исходя из приведенного выше примера компоновки, окончательный формат Excel будет отображаться следующим образом:

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

и так далее и так далее для каждого удостоверения личности....

Что было бы наилучшим способом для разработки этого, так что, если он содержит конкретные приложения в столбце table2 appsinfo, он напишет это как Да, а если нет, напишите нет для каждого идентификатора в соответствующей строке?

Источник

3 ответа

Ли опередил меня до ответа, но у меня было похожее решение с использованием XML-возможностей SQL. С XML может быть сложно работать, но SQL Server имеет синтаксис для работы с 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

Моя установка была:

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

Мне также пришлось немного изменить XML, чтобы сделать его действительным. Единственная реальная разница между моим решением и решением Ли состоит в том, что я использую SQL CASE для вывода Да / Нет вместо того, чтобы делать это в Excel. В любом случае будет работать так же хорошо.

Однако структура фактического XML, который вы используете, может повлиять на эти запросы. Вы можете разместить здесь образец блока? А какую версию SQL Server вы используете? Синтаксис XML для SQL 2000 немного отличается от SQL 2005+, и в SQL 2000 нет типа данных XML.

Чтобы построить электронную таблицу из запроса, у Рэя Камдена есть хорошая статья. И есть также документы Adobe для cfspreadsheet.

Источник

Если существует только три столбца приложения, другой вариант - использовать xml-функции SQL Server для генерации результатов в sql. Просто верните bit значение для каждого из трех столбцов. (Примечание: я настроил пример xml из вашего поста, чтобы сделать его действительным, но SQLFiddle показывает общую идею.)

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

Затем в вашей электронной таблице примените к столбцам пользовательский формат ячейки, чтобы отобразить "да / нет" вместо 0/1. Предполагая, что вы используете встроенную функциональность электронных таблиц CF, формат:

         {dataformat='"Yes";"Yes";"No"'}
Источник

Я думаю, что самый простой и самый грязный способ - через обнаружение строк.

<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>
Источник
Другие вопросы по тегам