CREATE SCHEMA [report]
GO

-- =============================================
-- Author:		Evgeny Garaev
-- Create date: 20180104
-- Last modified: 20180105
-- Description:	Returns a list of emails for a particular report
-- =============================================
CREATE FUNCTION [report].[get_recipients] (@report_id int)
RETURNS varchar(500)
AS
BEGIN
	--The list of emails
	declare @list varchar(500);

	--Get the list of emails in the variable
	set @list = (select rc.email+';'
	from report.recipient rc
		inner join report.report_recipient rr
			on rr.recipient_id = rc.id
	where rr.report_id = @report_id
	FOR XML PATH (''));

	--Return the result value
	RETURN @list;
END
GO

-- =============================================
-- Author:		Evgeny Garaev
-- Create date: 20180105
-- Last modified: 20180105
-- Description:	Returns the standart HTML header for a table
-- =============================================
CREATE FUNCTION [report].[get_std_header] (@table_name sysname)
RETURNS nvarchar(max)
AS
BEGIN
	--The header
	declare @header nvarchar(max);

	--If the object definition is available
	if exists(select 1 from sys.columns where object_id = OBJECT_ID(@table_name))
	begin
		--Form a standard header for a table
		set @header = N'<table border="1"><tr>'

		--Add a header for each column
		select @header = @header + N'<th bgcolor="#B0B0FF">' + name + N'</th>'
		from sys.columns
		where object_id = OBJECT_ID(@table_name)
		order by column_id;
	end;

	--Return the result value
	RETURN @header;
END
GO

-- =============================================
-- Author:		Evgeny Garaev
-- Create date: 20180104
-- Last modified: 20180105
-- Description:	Returns value of a db mail profile name
-- =============================================
CREATE FUNCTION [report].[profile_name] 
()
RETURNS sysname
AS
BEGIN
	--This is a return value for the profile name
	DECLARE @profile sysname;

	--Retrive the profile name value from the config table
	select @profile = value
	from report.config
	where id = 1;

	-- Return the profile name
	RETURN @profile;
END
GO

CREATE TABLE [report].[body](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[report_id] [int] NOT NULL,
	[caption] [varchar](500) NOT NULL,
	[table_name] [sysname] NOT NULL,
	[header] [nvarchar](max) NULL,
 CONSTRAINT [PK_body] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)
)
GO

CREATE TABLE [report].[config](
	[id] [int] NOT NULL,
	[name] [varchar](50) NULL,
	[value] [varchar](100) NULL,
 CONSTRAINT [PK_config] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)
)
GO

CREATE TABLE [report].[recipient](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[email] [varchar](50) NOT NULL,
 CONSTRAINT [PK_recipient] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)
) ON [PRIMARY]
GO

CREATE TABLE [report].[report](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[subject] [varchar](200) NOT NULL,
 CONSTRAINT [PK_report] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)
)
GO

CREATE TABLE [report].[report_recipient](
	[report_id] [int] NOT NULL,
	[recipient_id] [int] NOT NULL,
 CONSTRAINT [PK_report_recipient] PRIMARY KEY CLUSTERED 
(
	[report_id] ASC,
	[recipient_id] ASC
)
) ON [PRIMARY]
GO
ALTER TABLE [report].[body]  WITH CHECK ADD  CONSTRAINT [FK_body_report] FOREIGN KEY([report_id])
REFERENCES [report].[report] ([id])
GO
ALTER TABLE [report].[body] CHECK CONSTRAINT [FK_body_report]
GO
ALTER TABLE [report].[report_recipient]  WITH CHECK ADD  CONSTRAINT [FK_report_recipient_recipient] FOREIGN KEY([recipient_id])
REFERENCES [report].[recipient] ([id])
GO
ALTER TABLE [report].[report_recipient] CHECK CONSTRAINT [FK_report_recipient_recipient]
GO
ALTER TABLE [report].[report_recipient]  WITH CHECK ADD  CONSTRAINT [FK_report_recipient_report] FOREIGN KEY([report_id])
REFERENCES [report].[report] ([id])
GO
ALTER TABLE [report].[report_recipient] CHECK CONSTRAINT [FK_report_recipient_report]
GO

-- =============================================
-- Author:		Evgeny Garaev
-- Create date: 20180105
-- Last modified: 20180105
-- Description:	Generate a HTML content of a table
-- =============================================
CREATE procedure [report].[generate_html_table] @tbl_name varchar(200), @out varchar(max) output
as
BEGIN
set nocount on;

--Declare variables for iterating through the list of columns
declare @c_cnt int;
declare @sql nvarchar(max);

--Calculate number of columns
select @c_cnt = count(1)
from [sys].[all_columns]
where object_id = object_id(@tbl_name);

--In case the object exists and has columns
if (@c_cnt > 0 and @c_cnt is not null)
begin
	--Initialize cycle parameters
	declare @column_id int;
	set @column_id = 1;

	--A dynamic SQL will be used, the new temp table will be created
	set @sql = 'create table #t_report( '+CHAR(13)+CHAR(10);

	--For each column of the object
	while @column_id < @c_cnt
	begin
		--Add the column in the select statement
		set @sql = @sql + 'c' + cast(@column_id as varchar(2)) + 
			' nvarchar(200), '+CHAR(13)+CHAR(10);

		--Continue with the next column
		set @column_id = @column_id + 1;
	end;

	--The last column is special
	set @sql = @sql + 'c' + cast(@column_id as varchar(2)) + 
		' nvarchar(200) );'+CHAR(13)+CHAR(10);

	--The temp table will be populated with the data from the real object
	set @sql = @sql + 'insert into #t_report ' +CHAR(13)+CHAR(10);
	set @sql = @sql + 'select * ' +CHAR(13)+CHAR(10);
	set @sql = @sql + 'from ' + @tbl_name + ';' + CHAR(13)+CHAR(10);

	--Generate code for dynamic transformation of the data into a HTML table
	set @sql = @sql + 'select @str = ISNULL(@str, '''') + ''<TR>'' + ' + CHAR(13)+CHAR(10);

	set @column_id = 1;
	while @column_id <= @c_cnt
	begin
		set @sql = @sql + '''<TD>'' + ISNULL(c' + cast(@column_id as varchar(2)) + 
			', '''') + ''</TD>'' + '+CHAR(13)+CHAR(10);

		set @column_id = @column_id + 1;
	end;

	set @sql = @sql + '''</TR>''' +CHAR(13)+CHAR(10);
	set @sql = @sql + 'from #t_report;' +CHAR(13)+CHAR(10);

	--Execute the generated dynamic SQL
	EXECUTE sp_executesql @sql, N'@str varchar(max) OUTPUT', @str=@out OUTPUT;
end;

END;
GO

-- =============================================
-- Author:		Evgeny Garaev
-- Create date: 20180104
-- Last modified: 20180105
-- Description:	Sends the report to the recipients
-- =============================================
CREATE procedure [report].[send_report] (@report_id int)
AS
BEGIN
set nocount on;

--Declare variables for sp_send_dbmail
declare @body nvarchar(max);
declare @table_name sysname;
declare @html_table nvarchar(max);
declare @table_header nvarchar(max);
declare @body_id int;
declare @subj nvarchar(200);
declare @rec nvarchar(500);
declare @profile sysname;

--Initialize body, it is empty by default
set @body = N'';

--Initialize the body_id in order to iterate through all the tables for the report
set @body_id = 0;

--Initialize the subject value for the report
set @subj = (select subject
				from [report].[report]
				where id = @report_id);

--Get the list of recipients for the report
set @rec = [report].[get_recipients](@report_id);

--Initialize the db mail profile from the config
set @profile = [report].[profile_name]();

--For each table in the report generate an HTML table
while (exists(select 1 
			from [report].[body]
			where id > @body_id 
				and report_id = @report_id))
begin
	--Clear the cycle used variables
	set @html_table = N'';
	set @table_name = NULL;
	set @table_header = NULL;
	
	--Get the next body_id for the report
	set @body_id = (select min(id) 
					from [report].[body]
					where id > @body_id 
						and report_id = @report_id);

	--Add the caption for the table
	set @body = @body +
		(select N'<h1>'+caption+N'</h1>'
		from [report].[body] where id = @body_id);

	--Get the table name
	set @table_name =
		(select [table_name]
		from [report].[body] where id = @body_id);

	--Get a table header if exists
	set @table_header =
		(select header 
		from [report].[body] where id = @body_id);

	--If the custom header is not defined then generate a default one
	if (@table_header is NULL) 
		set @table_header = [report].[get_std_header](@table_name);

	--Generate HTML table
	exec [report].[generate_html_table] @table_name, @html_table out;

	--Add the table content to the email body
	set @body = @body + coalesce(@table_header + @html_table + N'</TABLE>', 'No data');

end;

--Send the email
EXEC msdb.dbo.sp_send_dbmail
	@profile_name = @profile,
	@recipients = @rec,
    @subject = @subj,
    @body = @body,
    @body_format = 'HTML';

END;
GO