-- =============================================
--	Author:			
--	Create date:	
--	Description:	Відправка по E-mail RS-звіту (Reporting Service) @RSReportName
--	Alter date:		
-- =============================================
create PROCEDURE [dbo].[spSendMailRSReport] 

		 @RSReportName				nvarchar(425)							--	Назва RS-звіту

		,@Mail_body					nvarchar(max)=null						--	"Тело" листа в форматі HTML 
		,@Mail_subject 				nvarchar(255)=null						--	Тема листа 
		,@Mail_importance			nvarchar(6)='Normal'					--	"Важливість" листа. 
		,@RenderFormat				nvarchar(100)=N'EXCELOPENXML'			--	Формат, в якому буде відправлено звіт
		,@RSParameters				nvarchar(max)=N'<ParameterValues />'	--	xml-строка значений параметров для отчета, вида <ParameterValues><ParameterValue><Name>BD</Name><Value>01.11.2016</Value></ParameterValue><ParameterValue><Name>ED</Name><Value>30.11.2016</Value></ParameterValue></ParameterValues>
		,@TO						nvarchar(max)='billy@microsoft.com'		--	Список адрес (через ";") на які буде відіслано звіт (адресат) 
		,@CC						nvarchar(max)=null						--	Список адрес (через ";") на які буде відіслано звіт (копія) 
		,@BCC						nvarchar(max)=null						--	Список адрес (через ";") на які буде відіслано звіт (скрита копія) 
AS
begin
	set nocount on
	--
	declare 
		 @SubscriptionID		uniqueidentifier	--	ID "подписки" на звіт (Subscription)
		,@ReportID				uniqueidentifier
		,@ReportPath			nvarchar(425)
		,@ScheduleID			uniqueidentifier	
		,@D						datetime=getdate()
		,@User					nvarchar(260)=N'NT AUTHORITY\SYSTEM' 
		,@UserSID				varbinary(85)

	declare @ExtensionSettings	nvarchar(max)
		
	declare @ES table (id int identity, Name nvarchar(100), Value nvarchar(max))
	
	--
	if @Mail_subject is null set @Mail_subject = @RSReportName;

	--	Параметри "підписки"
	insert into @ES (Name, Value) values ('TO', @TO)
	insert into @ES (Name, Value) values ('CC', @CC)
	insert into @ES (Name, Value) values ('BCC', @BCC)

	insert into @ES (Name, Value) values ('IncludeReport', 'True')
	insert into @ES (Name, Value) values ('RenderFormat', @RenderFormat)
	insert into @ES (Name, Value) values ('Subject', @Mail_subject)
	insert into @ES (Name, Value) values ('Comment', @Mail_body	)
	insert into @ES (Name, Value) values ('IncludeLink', 'False')

	insert into @ES (Name, Value) values ('Priority', @Mail_importance)

	--	xml-параметр ExtensionSettings "підписки"
	set @ExtensionSettings = cast(
				(select 
					Name 
					,Value

				from	@ES
				where	nullif(Value,'') is not null
				order by id 
	 
				for xml path('ParameterValue'), root('ParameterValues') )
			as nvarchar(max))

	--	Запис в "підписку"
	set @SubscriptionID=newid() 
	set @ScheduleID=newid() 

	begin try
		execute as login='sa'

			select @ReportID=c.ItemID, @ReportPath=c.Path from [ReportServer].dbo.[Catalog] c where c.Name = @RSReportName and c.[Type] in (2,4,8)

			select top 1 @UserSID=[Sid] from [ReportServer].dbo.[Users] c where c.UserName=@User 
	
			--	Создание "подписки". В ней сохраняются все параметры, по которым будет формироваться/отправляться отчет
			exec [ReportServer].[dbo].[CreateSubscription]      
						@id						=@SubscriptionID
						,@Locale				=N'uk-UA'
						,@Report_Name			=@ReportPath
						,@ReportZone			=0
						,@OwnerSid				=@UserSID 
						,@OwnerName				=@User
						,@OwnerAuthType			=1
						,@DeliveryExtension		=N'Report Server Email'
						,@InactiveFlags			=0
						,@ExtensionSettings		=@ExtensionSettings
						,@ModifiedBySid			=@UserSID
						,@ModifiedByName		=@User
						,@ModifiedByAuthType	=1 
						,@ModifiedDate			=@D 
						,@Description			=N'Temporary Subscription'
						,@LastStatus			='' 
						,@EventType				=N'TimedSubscription'
						,@Parameters			=@RSParameters					--N'<ParameterValues />'
						,@Version				=3


			--	
			exec [ReportServer].[dbo].[CreateTimeBasedSubscriptionNotification] 
						@SubscriptionID			=@SubscriptionID
						,@LastRunTime			=@D
						,@LastStatus			=N'New Subscription'


			--
			exec [ReportServer].dbo.AddEvent @EventType='TimedSubscription',  @EventData=@SubscriptionID

			--	Ожидание выполнения выгрузки/отправки отчета
			while exists(select 1 from [ReportServer].[dbo].[Event] where [EventData]=@SubscriptionID)
				WAITFOR DELAY '00:00:08';
			--	 

			--	Удаление "подписки"
			exec [ReportServer].[dbo].AddSubscriptionToBeingDeleted @SubscriptionID
			exec [ReportServer].[dbo].DeleteSubscription @SubscriptionID

		revert
	end try

	begin catch
		select 
			ERROR_NUMBER() AS ErrorNumber
			,ERROR_SEVERITY() AS ErrorSeverity
			,ERROR_STATE() AS ErrorState
			,ERROR_PROCEDURE() AS ErrorProcedure
			,ERROR_LINE() AS ErrorLine
			,ERROR_MESSAGE() AS ErrorMessage;
		--	
		return ERROR_NUMBER()			
	end catch

	--
	return 0 
end