USE [bn_patp_dob]
GO

/****** Object:  UserDefinedFunction [dbo].[BNComplex_GetLastValidDate]    Script Date: 08/12/2011 16:36:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:  Бондарь Михаил
-- Create date: 12-12-2011
-- Description:    возвращает дату последней валидной координаты по обеъкту и прибору
-- =============================================
CREATE FUNCTION [dbo].[BNComplex_GetLastValidDate]    
(
    @objectid int,@deviceid int, @lastdate datetime
)
RETURNS datetime
AS
BEGIN
    return (/****** Сценарий для команды SelectTopNRows среды SSMS  ******/
SELECT max ([NavTime])
            
     FROM [BNComplex].[dbo].[ObjectsMotions] with(index(IX_ObjectsMotions))
     where objectid=@objectid and deviceid=deviceid
     and navtime>=@lastdate
     and valid=1)

END

GO

USE [bn_patp_dob]
GO
/****** Object:  StoredProcedure [dbo].[bncomplex_tc_alarm]    Script Date: 08/12/2011 16:34:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:  <Бондарь Михаил>
-- Create date: 12/08/2011
-- Description:    Отправка инфо об отсуствии данных по ТС
-- =============================================
Create PROCEDURE [dbo].[bncomplex_tc_alarm]    
    (@alarm_hour int=96)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

/*    
версия от 12-08-2011    
скрипт формирует таблицу приборов (подписок), по которым нет данных более чем указанное количество часов
и отправляет все это по указанным в процедуре адресам.
*/

    declare @recipients_1    as varchar (max)
    declare @copy_recipients_1 as varchar (max)
    declare @blind_copy_recipients_1 as varchar (max)
    declare @query_1 as varchar (8000)

DECLARE @tableHTML  NVARCHAR(MAX) ;

declare @t table (DefaultName nvarchaR(32)
     ,GosNum NVARCHAR(12)
     ,devnum varchar(15)
     ,deviceid int
     ,objectid int
         ,[user] nvarchar(64)
         ,NavTime datetime
         ,Valid int
         ,lastvalid datetime)
insert into @t
SELECT o.DefaultName
     ,o.GosNum
     ,d.devnum
     ,d.deviceid
     ,o.objectid
         ,L.[user]
         ,oc.NavTime
         ,oc.Valid
         ,case when oc.Valid=1 then oc.NavTime else    
         case when oc.Valid is null then null else
         bn_patp_dob.dbo.BNComplex_GetLastValidDate (o.objectid,d.deviceid,dateadd(hh,-24*30, oc.navtime))
         end
         end as lastvalid
     FROM [BNComplex].[dbo].[Subscriptions] as sub
     inner join [BNComplex].dbo.Objects as O on o.ObjectID=sub.[ObjectID] -- подписки
     inner join [BNComplex].dbo.Login as l on l.[LoginID]=sub.[LoginID] -- логины
     left join [BNComplex].dbo.DevicesOnObjects as doo on doo.ObjectID=o.ObjectID -- список приоборов на объектах
     inner join [BNComplex].dbo.Devices as D on d.DeviceID=doo.DeviceID -- спиок приборов
     left join [BNComplex].dbo.ObjectsCoords as OC on OC.DeviceID=d.DeviceID -- последние координаты
     --getdate() between sub.start and sub.[end] -- только действующие подпискавумтгь=ээ
     order by L.[user]
        

SET @tableHTML =
       N'<H1>BNComplex M2M проверка наличия данных</H1>'+
       N'<table border="1">' +
       N'<tr><th>Пользователь</th><th>Машина</th>'+
    N'<th>№ Гос</th><th>АТ</th><th>Связь</th><th>Валидные</th>' +
       CAST ( (   select [user] td,'',isnull(DefaultName,'') td,'',isnull(gosNum,'') td,'',devNum td,'',
       isnull(convert(varchar(19),NavTime,21),'no data') td,'',
       isnull(convert(varchar(19),LastValid,21),'>30 day') td from @t
      where (lastvalid is null) or datediff (hh,lastvalid,getdate())>=@alarm_hour
                 FOR XML PATH('tr'), TYPE    
       ) AS NVARCHAR(MAX) ) +
       N'</table>' ;
--set @query_1 = 'select    t.Garag_Number as Гаражный_N,    
--  t.Gos_Number as Гос,    
--  cast(m.name as varchar (25)) as Маршрут ,O.ShortName as Организация    
--    from  bn_patp_dob.dbo.no_ordered_work as NoOW
--    inner join bn_patp.dbo.PATP_Transport as T on t.id=NoOW.transport_id
--    inner join bn_patp.dbo.PATP_Marshruts as M on m.id=NoOW.marshrut_id
--    inner join bn_patp.dbo.SYS_OrgRequisite as O on o.id=T.Orgid
--    where start_time between cast(convert (varchar,getdate()-1,112)+'' 03:00:00''as datetime)    
--    and cast (convert (varchar,getdate(),112)+'' 02:59:59'' as datetime)'
    set @recipients_1 = 'pochta@yandex.ru'
    set @copy_recipients_1 = 'pochta@gmail.com'
    set @blind_copy_recipients_1 = ''

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
EXEC msdb.dbo.sp_send_dbmail  @profile_name =  'основной'    
       ,  @recipients =  @recipients_1
       , @copy_recipients =  @copy_recipients_1    
       ,  @blind_copy_recipients =  @blind_copy_recipients_1
       , @subject =  'BNComplex - нет данных'    
       , @body =  @tableHTML    
       , @body_format =  'HTML'    
--   [ , [ @importance = ] 'importance' ]
--    [ , [ @sensitivity = ] 'sensitivity' ]
--    [ , [ @file_attachments = ] 'attachment [ ; ...n ]' ]
--     ,  @query = @query_1
--    ,   @execute_query_database = 'bn_patp_dob'
--    ,   @attach_query_result_as_file = ] attach_query_result_as_file ]
--    [ , [ @query_attachment_filename = ] query_attachment_filename ]
--     , @query_result_header = 1
--     , @query_result_width = 200
--    ,  @query_result_separator =  ' ; '
--    ,  @exclude_query_output = 0
--     ,  @append_query_error = 1
--    [ , [ @query_no_truncate = ] query_no_truncate ]
--    [ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]

END

DECLARE @RC int
DECLARE @alarm_hour int

-- TODO: задайте здесь значения параметров.

EXECUTE @RC = [bn_patp_dob].[dbo].[bncomplex_tc_alarm]   
     @alarm_hour=96
GO