USE [bn_patp_dob]
GO

/****** Object:  StoredProcedure [dbo].[CyberFleet_export_ts_at]    Script Date: 02/12/2012 14:17:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CyberFleet_export_ts_at]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[CyberFleet_export_ts_at]
GO

USE [bn_patp_dob]
GO

/****** Object:  StoredProcedure [dbo].[CyberFleet_export_ts_at]    Script Date: 02/12/2012 14:17:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:  <Бондарь Михаил>
-- Create date: 05/11/2011
-- Description:    скрипт для экспорт прибора с сенсорами, тарировками, ТС, АТ в другую базу данных.
-- на выходе возвращает набор строк, составляющий тело нового скрипта, при выполеннии которого действие будет выполнено
-- в своей работе скрипт расчитан на добавление данных - если он находит уже существующий прибор с указанным номером
-- существование АТ не проверяется - просто создается новое, и привызявается к созданному прибору.
-- без доработки кода для ОБНОВЛЕНИЯ данных скрипт не подходит - только для добавления.
-- =============================================
CREATE PROCEDURE [dbo].[CyberFleet_export_ts_at]      
      (
      @device_code varchar(16)-- на входе номер прибора
      ,@CF_DB_name varchar(20)='[BN]' -- имя базы данных клиента
      )      
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

      declare @type int, @device_id int
      declare @t table (str1 varchar(500)) -- таблица строк нового скрипта      
      declare @str varchar(500)
      select @type=type_id,@device_id=id  from BN.dbo.SYS_DEV_Device where code=@device_code and DeviceActive=1
      if @device_id is null begin
      insert into @t      
      values ('Ошибка поиска прибора с номером '+@device_code+' Обработка прервана!')      
      select * from @t
      return
      end

      insert into @t      
      values ('use '+@CF_DB_name + ' set dateformat ymd set nocount on')      
      --insert into @t      
      --values ('go')      

      insert into @t      
      values ('declare @code varchar(16), @type int,@device_id int')      
      insert into @t      
      values ('select @code='''+@device_code+''', @type='+CAST(@type as varchar))      
      insert into @t      
      values ('-- сначала проверка, что такого прибора нет.')      

      insert into @t      
      values ('if exists (select ID from SYS_DEV_Device where code=@code and type_id=@type and DeviceActive=1)')      
      insert into @t      
      values ('begin print ''Уже существует прибор №''+@code+'' Обработка будет прервана.'' return end')      
      insert into @t      
      values ('insert into SYS_DEV_Device ([type_id],[code],[name],[opt_int1],[opt_int2],[opt_int3],[tel_number],[IsGSM],[IsGPRS],[SimNum],[RunByTrack],[InstallDate])')      
      set @str=(select 'values (@type,@code,'+case when name is null then 'null' else ''''+name+''''end
      +','+CAST(opt_int1 as varchar)+','+CAST(opt_int2 as varchar)+','+CAST(opt_int3 as varchar)+','
      +case when tel_number is null then 'null' else ''''+tel_number+''''end+','+CAST(IsGSM as varchar)+','+CAST(IsGPRS as varchar)+','
      +case when [SimNum] is null then 'null' else ''''+[SimNum]+''''end+','+CAST([RunByTrack] as varchar)+','''+convert(varchar(50),[InstallDate],20)+''')'
      from bn.dbo.SYS_DEV_Device where id=@device_id)
      insert into @t      
      values (@str)
      --values ('values (@type,@code,'тест',320,420,0,'тел',0,1,'sim',0,'2011-11-05 13:07:46.427')')      
      insert into @t      
      values ('select @device_id=IDENT_CURRENT(''sys_dev_device'')')
      insert into @t      
      values ('-- теперь работаем с датчиками, сначала удаляем, а потом заполняем')
      insert into @t      
      values ('delete from SYS_DEV_Sensor where device_id=@device_id')
      insert into @t      
      values ('-- цикл по сенсорам')

      declare @sens_id int

      declare cur cursor local for      
      select id from bn.dbo.SYS_DEV_Sensor where device_id=@device_id
      open cur
      fetch next from cur into @sens_id
      while @@FETCH_STATUS=0 begin
      insert into @t      
      values ('insert into SYS_DEV_Sensor')
      insert into @t      
      values ('([device_id],[num],[sensor_type_id],[IsAlarm],[description],[IconID],[SensFuelConsWin],[SensFuelConsSum],[SaveAsEvent],[priority],[Units]')
      insert into @t      
      values (',[SensorActive],[HasOwnTank],[MinValue],[MaxValue],[SensorClassificationId],[MapColor],[TableColor],[SignFontName],[SignCharCode],[VarThreshold]')
      insert into @t      
      values (',[AnalogSensorPrecision],[TankSensorID],[WorkOnStop],[FuelGradeID],[DischargeThreshold],[RefuelingThreshold],[RefuelingTimeThreshold],[DischargeTimeThreshold])')
      set @str=(select 'values (@device_id,'+CAST(num as varchar(2))+','+CAST([sensor_type_id] as varchar(2))+','+CAST([IsAlarm] as varchar(2))+','
      +case when description is null then 'null' else ''''+description+''''end+','
      +case when IconID is null then 'null' else ''''+CAST(IconID as varchar)+''''end+','
      +case when SensFuelConsWin is null then 'null' else ''''+cast(SensFuelConsWin as varchar)+''''end+','
      +case when SensFuelConsSum is null then 'null' else ''''+cast(SensFuelConsSum as varchar)+''''end+','
      + CAST(SaveAsEvent as varchar)+','+CAST(priority as varchar)+','+case when Units is null then 'null' else ''''+Units+''''end+','
      from bn.dbo.SYS_DEV_Sensor where id=@sens_id)
      insert into @t      
      values (@str)

      set @str=(select      
      ''+CAST(SensorActive as varchar)+','+CAST(HasOwnTank as varchar)+','+
      +case when MinValue is null then 'null' else ''''+cast(MinValue as varchar)+''''end+','
      +case when MaxValue is null then 'null' else ''''+cast(MaxValue as varchar)+''''end+','
      +case when [SensorClassificationId] is null then 'null' else ''''+cast([SensorClassificationId] as varchar)+''''end+','
      +case when [MapColor] is null then 'null' else ''''+cast([MapColor] as varchar)+''''end+','
      +case when [TableColor] is null then 'null' else ''''+cast([TableColor] as varchar)+''''end+','
      +case when [SignFontName] is null then 'null' else ''''+cast([SignFontName] as varchar)+''''end+','
      +case when [SignCharCode] is null then 'null' else ''''+cast([SignCharCode] as varchar)+''''end+','
      +CAST([VarThreshold] as varchar)+','
      from bn.dbo.SYS_DEV_Sensor where id=@sens_id)
      insert into @t      
      values (@str)

      set @str=(select      
      ''+CAST([AnalogSensorPrecision] as varchar)+',null,'
      +CAST([WorkOnStop] as varchar)+','
      +case when [FuelGradeID] is null then 'null' else ''''+cast([FuelGradeID] as varchar)+''''end+','
      +case when [DischargeThreshold] is null then 'null' else ''''+cast([DischargeThreshold] as varchar)+''''end+','
      +case when [RefuelingThreshold] is null then 'null' else ''''+cast([RefuelingThreshold] as varchar)+''''end+','
      +case when [RefuelingTimeThreshold] is null then 'null' else ''''+cast([RefuelingTimeThreshold] as varchar)+''''end+','
      +case when [DischargeTimeThreshold] is null then 'null' else ''''+cast([DischargeTimeThreshold] as varchar)+''''end+')'
      from bn.dbo.SYS_DEV_Sensor where id=@sens_id)
      insert into @t      
      values (@str)
      fetch next from cur into @sens_id
      end --while @@FETCH_STATUS=0 begin
      close cur
      -- теперь надо нати те датчики, которые ссылаются нв другие
      insert into @t      
      values ('---- цикл по сенсорам для привязки [TankSensorID]')
      insert into @t      
      values ('declare @sens_num int, @sens_id int, @sens_id1 int')

      declare @TankSensorID int
      open cur
      fetch next from cur into @sens_id
      while @@FETCH_STATUS=0 begin
      set @TankSensorID=(select TankSensorID from bn.dbo.SYS_DEV_Sensor where id=@sens_id)
      if @TankSensorID IS not null       
       begin
        set @str=(select      
        'set @sens_id= (select id from SYS_DEV_Sensor where device_id=@device_id and sensor_type_id='+cast(sensor_type_id as varchar)+' and num='+cast(num as varchar)+')'      
        from bn.dbo.SYS_DEV_Sensor where id=@TankSensorID)
        insert into @t      
        values (@str)

        set @str=(select      
        'set @sens_id1= (select id from SYS_DEV_Sensor where device_id=@device_id and sensor_type_id='+cast(sensor_type_id as varchar)+' and num='+cast(num as varchar)+')'      
        from bn.dbo.SYS_DEV_Sensor where id=@sens_id)
        insert into @t      
        values (@str)

        insert into @t      
        values ('update SYS_DEV_Sensor set TankSensorID=@sens_id where id=@sens_id1')
       end

       fetch next from cur into @sens_id
      end --while @@FETCH_STATUS=0 begin
      close cur

      insert into @t      
      values ('---- цикл по тарировке аналоговых датчиков')
      declare @IDRec int

      open cur
      fetch next from cur into @sens_id
      while @@FETCH_STATUS=0 begin
       if exists (select IDRec from bn.dbo.FLEET_AnalogTarirovka where IDSensor=@sens_id)
       begin -- есть тарировка
        set @str=(
        select 'set @sens_num='+cast (num as varchar)
        from bn.dbo.SYS_DEV_Sensor where id=@sens_id
        )
        insert into @t      
        values (@str)
        insert into @t      
        values ('select @sens_id=ID from SYS_DEV_Sensor where [device_id]=@device_id and [sensor_type_id]=0 and num=@sens_num')
        insert into @t      
        values ('if @sens_id is null begin print ''Ошибка поиска сенсора! ''+cast(@sens_num as varchar)+'' Обработка будет прервана.'' return end')
        declare tar cursor local for
        select IDRec from bn.dbo.FLEET_AnalogTarirovka where IDSensor=@sens_id order by MinValue_Real
        open tar
        fetch next from tar into @IdRec
        while @@FETCH_STATUS=0      
         begin
               
        insert into @t      
        values ('insert into FLEET_AnalogTarirovka (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor)')
        set @str=(
        select 'values('+cast(MinValue_Real as varchar)+','+cast(MaxValue_Real as varchar)+','
          +cast(MinValue_Tar as varchar)+','+cast(MaxValue_Tar as varchar)+',@sens_id)'
        from bn.dbo.FLEET_AnalogTarirovka where IDRec=@IDRec
        )
        insert into @t      
        values (@str)
          fetch next from tar into @IdRec
         end
        close tar
        deallocate tar
             
       end
            
       fetch next from cur into @sens_id
      end
      close cur
      deallocate cur

      insert into @t      
      values ('INSERT INTO FLEET_Object (Device_ID,ObjectGarNum,ObjectGosNum,[ObjectTextPosType],[ObjectActive])')      
      set @str=(
      select 'values (@device_id,'''+ObjectGarNum+''','
      +case when ObjectGosNum is null then 'null' else ''''+ObjectGosNum+''''end+','
      +cast(ObjectTextPosType as varchar)+',1)'
      from bn.dbo.FLEET_Object as O
      inner join bn.dbo.SYS_DEV_Device as D on d.id=o.Device_ID and d.id=@device_id
      where o.ObjectActive=1
      )
      insert into @t      
      values (@str)

      insert into @t      
      values ('INSERT INTO '+@CF_DB_name+'.dbo.SYS_DEV_CurrentData (Device_ID,device_code,tele_time,server_time,transfer_time,long,lat,valid,speed,direction,[digit_sens_num]')      
      insert into @t      
      values (',[analog_sens_num],[analog_data],[flags],ObjectID,CurrentRun)')      
      insert into @t      
      values ('select d.id,d.code, ''1900'', getdate(),getdate(), 0 as lat,0 lon,0 valid,0 speed,0 dir,0 [digit_sens_num]')      
      insert into @t      
      values ('      ,0 [analog_sens_num],-1 [analog_data],0 [flags],O.ObjectID ObjectID ,0 CurrentRun ')      
      insert into @t      
      values ('from '+@CF_DB_name+'.dbo.SYS_DEV_Device as d')      
      insert into @t      
      values ('inner join '+@CF_DB_name+'.dbo.FLEET_Object as O on O.Device_ID=d.id')      
      insert into @t      
      values ('left join '+@CF_DB_name+'.dbo.SYS_DEV_CurrentData as CD on CD.device_id=d.id and cd.[device_code]=d.code and O.ObjectID=CD.[ObjectID]')      
      insert into @t      
      values ('where cd.id is null and d.id=@device_id')      

      select * from @t      
END

GO