(495) 925-0049, ITShop интернет-магазин 229-0436, Учебный Центр 925-0049
  Главная страница Карта сайта Контакты
Поиск
Вход
Регистрация
Рассылки сайта
 
 
 
 
 

Генерация отчетов в формате HTML для динамической таблицы-структур

Источник: habrahabr
AlanDenton

В относительно недавнем прошлом, возникла задача автоматизировать процесс генерации и рассылки HTML отчетов руководству по продажам за текущий месяц. Так уж вышло, что для каждого руководящего лица создавались отдельные таблицы с необходимой только им информацией.

Поскольку, для каждого отчета, все делалось вручную, для начала мы решили воспользоваться возможностями dbForge, который позволял делать экспорт группы таблиц в HTML формат.

Однако, рассылку по-прежнему приходилось формировать вручную, что, мягко говоря, было нерациональным.

Было решено генерировать HTML со стороны сервера базы данных и через Database Mail формировать рассылку путем выполнения команды sp_send_dbmail .

Большинство примеров, приведенный в Сети, создавали разметку вручную - это было не слишком эффективным подходом. При этом я не нашел универсального решения, позволяющего работать с таблицой имеющей произвольную структуру.

Чтобы заполнить этот пробел предлагаю на рассмотрение мой вариант решения.

Из системного представления получаем список столбцов для требуемой таблицы:

DECLARE @object_name SYSNAME , @object_id INT , @SQL NVARCHAR(MAX) SELECT @object_name = '[dbo].[Products]' , @object_id = OBJECT_ID(@object_name) SELECT @SQL = 'SELECT [header/style/@type] = ''text/css'' , [header/style] = '' table {border-collapse:collapse;} td, table { border:1px solid silver; padding:3px; } th, td { vertical-align: top; font-family: Tahoma; font-size: 8pt; text-align: left; }'' , body = ( SELECT * FROM ( SELECT tr = ( SELECT * FROM ( VALUES ' + STUFF(CAST(( SELECT ', (''' + c.name + ''')' FROM sys.columns c WITH(NOLOCK) WHERE c.[object_id] = @object_id AND c.system_type_id NOT IN (34, 36, 98, 128, 129, 130, 165, 173, 189, 241) ORDER BY c.column_id FOR XML PATH(''), TYPE) AS NVARCHAR(MAX)), 1, 2, '') + ' ) t (th) FOR XML PATH('''') ) UNION ALL SELECT ( SELECT * FROM ( VALUES' + STUFF(CAST(( SELECT ', ' + CASE WHEN c.is_nullable = 1 THEN '(ISNULL(' ELSE '(' END + CASE WHEN TYPE_NAME(c.system_type_id) NOT IN ('nvarchar', 'nchar', 'varchar', 'char') THEN 'CAST(' + '[' + c.name + '] AS NVARCHAR(MAX))' ELSE '[' + c.name + ']' END + CASE WHEN c.is_nullable = 1 THEN ',''''))' ELSE ')' END FROM sys.columns c WITH(NOLOCK) WHERE c.[object_id] = @object_id AND c.system_type_id NOT IN (34, 36, 98, 128, 129, 130, 165, 173, 189, 241) ORDER BY c.column_id FOR XML PATH(''), TYPE) AS NVARCHAR(MAX)), 1, 2, ' ') + ' ) t (td) FOR XML PATH(''''), TYPE) FROM ' + @object_name + ' ) t FOR XML PATH(''''), ROOT(''table''), TYPE ) FOR XML PATH(''''), ROOT(''html''), TYPE' PRINT @SQL EXEC sys.sp_executesql @SQL

Далее динамическим SQL создаем запрос, который генерирует XML:

SELECT [header/style/@type] = 'text/css' , [header/style] = 'css style ...' , body = ( SELECT * FROM ( SELECT tr = ( SELECT * FROM ( VALUES ('column_name1', 'column_name2', ...) ) t (th) FOR XML PATH('') ) UNION ALL SELECT ( SELECT * FROM ( VALUES ([column_value1], [column_value2], ...) )t (td) FOR XML PATH(''), TYPE ) FROM [table] ) t FOR XML PATH(''), ROOT('table'), TYPE ) FOR XML PATH(''), ROOT('html'), TYPE

При этом столбцы, содержащие специфичные типы данных (например, UNIQUEIDENTIFIER ) в генерируемый отчет не включаются:

SELECT name FROM sys.types WHERE user_type_id IN ( 34, 36, 98, 128, 129, 130, 165, 173, 189, 241 )

При выполнении запроса мы получаем следующую HTML разметку, которая прикреплялась к письму:

<html> <header> <style type="text/css"> ... </style> </header> <body> <table> <tr> <th>column_name1</th> <th>column_name2</th> ... </tr> <tr> <td>column_value1</td> <td>column_value2</td> ... </tr> </table> </body> </html> 

Чтобы вручную не выполнять этот скрипт каждую неделю, в SQL Agent был добавлен Job, который автоматически генерировал и отправлял отчеты.

Надеюсь, что приведенное здесь решение будет полезно при решении подобных задач.

PS: Многострочная конструкция VALUES появилась только в SQL Server 2008, поэтому, для экономии времени, привожу пример того же скрипта, но для 2005 сервера:

DECLARE @object_name SYSNAME , @object_id INT , @SQL NVARCHAR(MAX) SELECT @object_name = '[dbo].[Products]' , @object_id = OBJECT_ID(@object_name) SELECT @SQL = 'SELECT [header/style/@type] = ''text/css'' , [header/style] = '' table {border-collapse:collapse;} td, table { border:1px solid silver; padding:3px; } th, td { vertical-align: top; font-family: Tahoma; font-size: 8pt; text-align: left; }'' , body = ( SELECT * FROM ( SELECT tr = ( SELECT * FROM ( ' + STUFF(CAST(( SELECT ' UNION ALL SELECT ''' + c.name + '''' FROM sys.columns c WITH(NOLOCK) WHERE c.[object_id] = @object_id AND c.system_type_id NOT IN (34, 36, 98, 128, 129, 130, 165, 173, 189, 241) ORDER BY c.column_id FOR XML PATH(''), TYPE) AS NVARCHAR(MAX)), 1, 17, 'SELECT th =') + ' ) t FOR XML PATH('''') ) UNION ALL SELECT ( SELECT * FROM ( ' + STUFF(CAST(( SELECT ' UNION ALL SELECT ' + CASE WHEN c.is_nullable = 1 THEN 'ISNULL(' ELSE '' END + CASE WHEN TYPE_NAME(c.system_type_id) NOT IN ('nvarchar', 'nchar', 'varchar', 'char') THEN 'CAST(' + '[' + c.name + '] AS NVARCHAR(MAX))' ELSE '[' + c.name + ']' END + CASE WHEN c.is_nullable = 1 THEN ','''')' ELSE '' END FROM sys.columns c WITH(NOLOCK) WHERE c.[object_id] = @object_id AND c.system_type_id NOT IN (34, 36, 98, 128, 129, 130, 165, 173, 189, 241) ORDER BY c.column_id FOR XML PATH(''), TYPE) AS NVARCHAR(MAX)), 1, 17, 'SELECT td =') + ' ) t FOR XML PATH(''''), TYPE) FROM ' + @object_name + ' ) t FOR XML PATH(''''), ROOT(''table''), TYPE ) FOR XML PATH(''''), ROOT(''html''), TYPE' PRINT @SQL EXEC sys.sp_executesql @SQL 

Ссылки по теме


 Распечатать »
 Правила публикации »
  Обсудить материал в конференции Дискуссии и обсуждения общего плана »
Написать редактору 
 Рекомендовать » Дата публикации: 28.11.2013 
 

Магазин программного обеспечения   WWW.ITSHOP.RU
IBM Domino Enterprise Server Processor Value Unit (PVU) Annual SW Subscription & Support Renewal
AutoCAD LT 2019 Commercial New Single-user ELD Annual Subscription PROMO
IBM DOMINO COLLABORATION EXPRESS AUTHORIZED USER LICENSE + SW SUBSCRIPTION & SUPPORT 12 MONTHS
SmartBear Collaborator - Concurrent User License (Includes 1 Year Maintenance)
GFI FaxMaker и 1 год поддержки (10-49 лицензий)
 
Другие предложения...
 
Курсы обучения   WWW.ITSHOP.RU
 
Другие предложения...
 
Магазин сертификационных экзаменов   WWW.ITSHOP.RU
 
Другие предложения...
 
3D Принтеры | 3D Печать   WWW.ITSHOP.RU
 
Другие предложения...
 
Новости по теме
 
Рассылки Subscribe.ru
Информационные технологии: CASE, RAD, ERP, OLAP
Программирование на Microsoft Access
CASE-технологии
OS Linux для начинающих. Новости + статьи + обзоры + ссылки
СУБД Oracle "с нуля"
Мир OLAP и Business Intelligence: новости, статьи, обзоры
Компьютерная библиотека: книги, статьи, полезные ссылки
 
Статьи по теме
 
Новинки каталога Download
 
Исходники
 
Документация
 
Обсуждения в форумах
Модульный метод создания сайтов (5)
Модульный метод создания (компоновки) сайтов на основе наборов типовых отлаженных функциональных...
 
Excell не печатает :-( (2)
Почему то Excell не печатает страницы в альбомной ориентаций, то есть страница в аольбомной...
 
ODBC: dBase CREATE TABLE... (9)
Есть проблема Из программы на CTD 2.0 PTF3 подключаемся к DBF-файлам Set SqlDatabase='Файлы...
 
Как сменить пароль для схемы SYSTEM? (6)
Как сменить пароль по умолчанию для схемы SYSTEM c 'manager' на свой? То же и для SYS
 
Помощь студентам по программированию (2)
https://xn-d1aqfdigh.xn-p1ai/ Помощь студентам по программированию на языках высокого уровня.
 
 
 



    
rambler's top100 Rambler's Top100