USE [bn_patp_dob]
GO
/****** Object:  StoredProcedure [dbo].[FLEET_GetZonesObjectsForReport]    Script Date: 04/25/2012 12:59:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

alter PROCEDURE [dbo].[FLEET_GetZonesObjectsForReport_sverh]
      @ObjectGos varchar(30),@ZoneName varchar(256),
     @PeriodFrom smalldatetime,
     @PeriodTo smalldatetime,
     @SkipZoneTime int, --игнорируемое время внутри зон, с
     @SkipInBothTables bit=0
AS
BEGIN
     SET NOCOUNT ON
declare @ObjectID int

select top 1 @ObjectID=ObjectID from bn.dbo.FLEET_Object where ObjectGosNum=@ObjectGos and ObjectActive=1
if @ObjectID is null begin print 'не найдено ТС' return end
     Create table #TempData
     (
       ZoneName varchar(256),
       ZonePeriodFrom datetime,
       ZonePeriodTo datetime,
       ZoneTime int null,
     )

     Declare
        @ZoneID int
       ,@ZonePeriodFrom datetime
       ,@ZonePeriodTo datetime
       ,@ZoneRunFrom int
       ,@ZoneRunTo int
       ,@InZone bit
       ,@First bit

       Declare ZoneCurs cursor local fast_forward for select
         ZoneID,
         Name
       from bn.dbo.FLEET_Zone with(nolock)
       where
         (ZoneActive=1
         or
         (
           ZoneActive = 0 -- Zone has been deleted
           and IsTemporary = 1 -- Zone was temporary zone (inserted by some kind of integration procedure)
           and @PeriodTo >= AddDate -- And report period is crossing with zone life period
           and @PeriodFrom <= DeleteDate -- --"-- If zone was deleted before adding of DeleteDate column it should not present in report in any case.
                      -- It archived by FALSE result for comparison with NULL value in this column
         ))
         and name = @zoneName
       

     Open ZoneCurs
     while 0=0
     begin
       Fetch from ZoneCurs into @ZoneID, @ZoneName
       if @@Fetch_Status != 0 break

       Select
          @ZonePeriodFrom = null
         ,@ZonePeriodTo = null
         ,@InZone = 0
         ,@First = 1

       while 1 = 1
       begin

         if @First = 1
         begin
           -- Ищем первую за период отчёта координату в этой зоне
           Select top 1
              @ZonePeriodFrom = tele_time
             ,@ZoneRunFrom = CurrentRun
           from bn.dbo.SYS_DEV_ArchiveData with(index(IX2_SYS_DEV_ArchiveData))
           where
             ObjectID = @ObjectID
             and tele_time between @PeriodFrom and @PeriodTo
             and valid = 1
             and bn.dbo.FLEET_IsCoordinateInZone(long, lat, @ZoneID) = 1
           order by
             tele_time
             
           if @ZonePeriodFrom is null
             break -- До конца периода отчёта больше не нашлось координаты в этой зоне
        
           Select
              @InZone = 1
             ,@First = 0
         end
         else
           if @InZone = 0
           begin
             -- Ищем очередную первую координату в этой зоне
             Select top 1
                @ZonePeriodFrom = tele_time
               ,@ZoneRunFrom = CurrentRun
             from bn.dbo.SYS_DEV_ArchiveData with(index(IX2_SYS_DEV_ArchiveData))
             where
               ObjectID = @ObjectID
               and tele_time > @ZonePeriodTo
               and tele_time <= @PeriodTo
               and valid = 1
               and bn.dbo.FLEET_IsCoordinateInZone(long, lat, @ZoneID) = 1
             order by
               tele_time
             
             if @ZonePeriodFrom is null
               break -- До конца периода отчёта больше не нашлось координаты в этой зоне
        
             Select
                @InZone = 1
           end
           else
           begin
             -- Ищем первую очередную координату вне зоны
             Set @ZonePeriodTo = null

             Select top 1
                @ZonePeriodTo = tele_time
               ,@ZoneRunTo = CurrentRun
             from bn.dbo.SYS_DEV_ArchiveData with(index(IX2_SYS_DEV_ArchiveData))
             where
               ObjectID = @ObjectID
               and tele_time > @ZonePeriodFrom
               and tele_time <= @PeriodTo
               and valid = 1
               and bn.dbo.FLEET_IsCoordinateInZone(long, lat, @ZoneID) = 0
             order by
               tele_time
                
             if @ZonePeriodTo is null
               break -- До конца периода отчёта не найдено координат вне этой зоны

             Insert into #TempData
             (
                ZoneName
               ,ZonePeriodFrom
               ,ZonePeriodTo
             )
             values
             (
                @ZoneName
               ,@ZonePeriodFrom
               ,@ZonePeriodTo
             )

             Select
                @InZone = 0
               ,@ZonePeriodFrom = null

           end
       end

       if @ZonePeriodFrom is not null
       begin
         -- До окончания периода была обнаружена координата в этой зоне,
         -- но до окончания периода отчёта выход из зоны так и не произошёл.
         -- Надо взять данные из последней координаты в периоде отчёта.
         Select top 1
            @ZonePeriodTo = tele_time
           ,@ZoneRunTo = CurrentRun
         from bn.dbo.SYS_DEV_ArchiveData with(index(IX2_SYS_DEV_ArchiveData))
         where
           ObjectID = @ObjectID
           and tele_time between @ZonePeriodFrom and @PeriodTo
           and valid = 1
         order by
           tele_time desc

         Insert into #TempData
         (
            ZoneName
           ,ZonePeriodFrom
           ,ZonePeriodTo
         )
         values
         (
            @ZoneName
           ,@ZonePeriodFrom
           ,@ZonePeriodTo
         )
       end

     end  -- Zone cursor

     Close ZoneCurs
     Deallocate ZoneCurs

     Update TTD
     set
       ZoneTime=datediff(ss, ZonePeriodFrom, ZonePeriodTo)
     from #TempData TTD

     Update #TempData
     set
       ZoneTime=1
     where
       ZoneTime<1

     Select
       ZoneName,
       ZonePeriodFrom,
       ZonePeriodTo,
       ZoneTime
     from #TempData
     where
       ZoneTime>=@SkipZoneTime
     order by
       ZonePeriodFrom,
       ZoneName

    
     Drop table #TempData
END

DECLARE @RC int
DECLARE @ObjectGos varchar(30)
DECLARE @ZoneName varchar(256)
DECLARE @PeriodFrom smalldatetime
DECLARE @PeriodTo smalldatetime
DECLARE @SkipZoneTime int
DECLARE @IsFilterGroup bit
DECLARE @SkipInBothTables bit

-- TODO: задайте здесь значения параметров.
set dateformat dmy
EXECUTE @RC = [bn_patp_dob].[dbo].[FLEET_GetZonesObjectsForReport_sverh]    
      @ObjectGos='М613ТХ 68'-- гос номер
     ,@ZoneName='АЗС СтройПлюс Бастионная' -- название зоны
     ,@PeriodFrom ='01/04/2012 00:00:00' -- периода начала проверки
     ,@PeriodTo ='24/04/2012 00:00:00' -- период окончания проверки
     ,@SkipZoneTime = 5 -- фильтр на время мин внутри зоны - посещения меньше будут удалены
     ,@SkipInBothTables=0
GO