exec DropSP 'aspt_getOperatorMessages'
go

create proc aspt_getOperatorMessages as
set nocount on

declare 
	@servers cursor, 
	@rd int, 
	@serverName nvarchar(128),
	@from datetime,
	@to datetime,
	@asptSql nvarchar(4000)

set @to = dbo.ConvDate(getdate())
set @from = dateadd(day, -1, @to)

set @servers = cursor fast_forward for
select 
	sd.RD,
	s.Servername
from ZMLentaStyk s
	join ZMLentaStykDepots sd on sd.ZmLentaStykID = s.ID
where 
	s.ASPT = 1

open @servers

while 1 = 1 begin
	fetch next from @servers into @rd, @serverName

	if @@fetch_status != 0
		break

	set @asptSql = 'exec _aspt_getNBDExams ' + 
		cast(@rd as varchar) + ', ''' + dbo.DateTimeToISOString(@from) + ''', ''' + dbo.DateTimeToISOString(@to) + ''''

	set @asptSql = 
		'select * into #asptResult from openquery(' + @serverName + ', ''' + replace(@asptSql, '''', '''''') + ''') ' +
		'exec aspt_getOperatorMessagesProcessResult'	
	
	exec(@asptSql)
end

close @servers
deallocate @servers
go

exec DropSP 'aspt_getOperatorMessagesProcessResult'
go

create proc aspt_getOperatorMessagesProcessResult as

/*create table #asptResult 
(
	PersID int null,
	PersRD int null,
	DateExam datetime null,
	[Крайний срок] datetime null,
	[Код нарушения] int null,
	[Код нарушения НСИ] int null,
	[Нарушение] varchar(150) null
)*/

declare 
	@c cursor,
	@PersRD int, @PersID int,
	@dateExam datetime,
	@deadline datetime,
	@violation varchar(150),
	
	@nl varchar(10),
	@messageID int,
	@messageText varchar(8000)

set @nl = char(13) + char(10)

set @c = cursor fast_forward for
select
	PersRD,
	PersID,
	DateExam,
	[Крайний срок],
	[Нарушение]
from #asptResult

open @c

while 1 = 1 begin
	fetch next from @c into @PersRD, @PersID, @dateExam, @deadline, @violation

	if @@fetch_status != 0
		break

	set @messageText = 
		dbo.DatetimeToStr(@dateExam, 1) + @nl +
		'Вам назначено внеплановое тестирование в АСПТ (из АСУ НБД).' + @nl +
		'Крайний срок тестирования: ' + dbo.DatetimeToStr(@deadline, 1) + @nl +
		'Нарушение в АСУ НБД: ' + @violation

	if exists(select * from PersBase where RD = @PersRD and ID = @PersID) begin
		begin tran

		insert into OperatorMessages(RD, MessageText)
		values(@PersRD, @messageText)

		set @messageID = scope_identity()

		insert into OperatorMessagesDestPers(MessageID, DestPersRD, DestPersID, ForOperatorRead)
		values(@messageID, @PersRD, @PersID, 0)

		commit tran
	end
end

close @c
deallocate @c
go