Generating HTML reports for dynamic table-structures

в 11:16, , рубрики: dynamic sql, html, reports, sql, sql server, t-sql, XML, метки: , , , , ,

В относительно недавнем прошлом, возникла задача автоматизировать процесс генерации и рассылки 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

Автор: AlanDenton

Источник



https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js