USE [BN_Jobs]
GO

/****** Object:  StoredProcedure [dbo].[Create_script_tarirovka]    Script Date: 09/07/2011 18:16:16 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:  Бондарь Михаил aka LogOff
-- Create date: 07/09/2011
-- Description:    Процедура создает скрипт переноса тарировки по номеру прибора и номеру аналогового датчика
-- Новый скрипт ищет прибор (не создает), ищет у него датчик (создает, если нужно с теми же параметрами что и в базе)
-- для найденного датчика тарировка удалеяется, и заполняется заного
-- =============================================
CREATE PROCEDURE [dbo].[Create_script_tarirovka] (
@device_code varchar(20), @sens_num int)
      -- Add the parameters for the stored procedure here
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

-- сначала надо определить id прибора      
declare @t table (string varchar(500))
declare @device_id int,  @sens_id int
set @device_id=(select id from bn.dbo.SYS_DEV_Device where code=@device_code and DeviceActive=1)
if @device_id is null      
begin      
insert into @t (string)
values ('не найден id прибора по его номеру '+@device_code)
insert into @t
values ('выполнение прервано')
select * from @t;
return
end
print @device_id
set @sens_id=(select [id] from [BN].dbo.SYS_DEV_Sensor      
         where num=@sens_num and device_id=@device_id and sensor_type_id=0)
print @sens_id

if @sens_id is null      
begin      
insert into @t (string) values ('по прибору не найден сенсор по его номеру с типом аналоговый'+@device_code)
insert into @t values ('выполнение прервано')
select * from @t; return end

-- нужно проверить, что есть тарировка - может датчик без нее - защита от дурака
if not exists (select * from bn.dbo.FLEET_AnalogTarirovka where IDSensor=@sens_id)      
begin
insert into @t (string)
values ('для прибора '+@device_code +' с аналоговым датчиком №'+cast(@sens_num as varchar(5))+' тарировка не внесена!')
select * from @t;
end

-- все данные есть, можно готовить скрипт внесения сенсора и его тарировки
insert into @t
values ('declare @t table (string varchar(500)) declare @device_code varchar(20), @device_id int, @sens_num int, @sens_id int')
insert into @t
values ('select @device_code='''+@device_code+''',@sens_num='+cast(@sens_num as varchar(5)))

insert into @t
values ('set @device_id=(select id from bn.dbo.SYS_DEV_Device where code=@device_code and DeviceActive=1)')
insert into @t
values ('if @device_id is null begin print ''не найден id прибора по его номеру ''+@device_code')
insert into @t
values ('print ''выполнение прервано''')
insert into @t
values ('select * from @t; return end')

insert into @t
values ('set @sens_id=(select [id] from BN.dbo.SYS_DEV_Sensor      
         where num=@sens_num and device_id=@device_id and sensor_type_id=0)')
insert into @t
values ('if @sens_id is null begin ')

insert into @t
values ('insert into bn.dbo.SYS_DEV_Sensor ([device_id],[num],[sensor_type_id],[IsAlarm],[description]')
insert into @t
values ('      ,[IconID],[SensFuelConsWin],[SensFuelConsSum],[SaveAsEvent],[priority],[Units]')
insert into @t
values ('      ,[SensorActive],[HasOwnTank],[MinValue],[MaxValue],[SensorClassificationId],[ObjectExtStatusID]')
insert into @t
values ('      ,[MapColor],[TableColor],[SignFontName],[SignCharCode],[progress_total],[VarThreshold]')
insert into @t
values ('      ,[AnalogSensorPrecision],[TankSensorID] ,[WorkOnStop] ,[FuelGradeID] ,[DischargeThreshold]')
insert into @t
values ('      ,[RefuelingThreshold] ,[RefuelingTimeThreshold],[DischargeTimeThreshold])')

DECLARE @res VARCHAR(8000)
SET @res = ''

declare @rest TABLE ( item varchar(8000) )
INSERT INTO @rest      
SELECT '@device_id'+','+'@sens_num '+','+'0'+','+cast([IsAlarm] as varchar(2))+','''+[description]+''''
           +','+cast([IconID] as varchar(5))+','+(case when [SensFuelConsWin] is null then 'null' else cast([SensFuelConsWin]as varchar(10)) end)      
           +','+(case when SensFuelConsSum is null then 'null' else cast([SensFuelConsSum]as varchar(10)) end)
           +','+cast([SaveAsEvent]as varchar(1))+','+cast([priority]as varchar(1))
           +','+(case when[Units] is null then 'null' else cast(''''+[Units]+'''' as varchar(30)) end)+','+
           cast([SensorActive] as varchar(1))+','+cast([HasOwnTank]as varchar(1))+','+
           (case when[MinValue] is null then 'null' else cast([MinValue]as varchar(10)) end)+','+
           (case when[MaxValue] is null then 'null' else cast([MaxValue]as varchar(10)) end)+','+
           (case when[SensorClassificationId] is null then 'null' else cast([SensorClassificationId]as varchar(10)) end)+','+
           (case when [ObjectExtStatusID] is null then 'null' else cast( [ObjectExtStatusID] as varchar(10)) end)+','+
           (case when [MapColor] is null then 'null' else cast( [MapColor] as varchar(10)) end)+','+
           (case when [TableColor] is null then 'null' else cast( [TableColor] as varchar(10)) end)+','+
           (case when [SignFontName] is null then 'null' else cast( ''''+[SignFontName]+'''' as varchar(10)) end)+','+
           (case when [SignCharCode] is null then 'null' else cast( [SignCharCode] as varchar(30)) end)+','+
           cast([progress_total] as varchar(3))+','+
           cast([VarThreshold]as varchar(3))+','+
           cast([AnalogSensorPrecision]as varchar(3))+','+
           (case when [TankSensorID]  is null then 'null' else cast( [TankSensorID]  as varchar(10)) end)+','+
           (case when [WorkOnStop] is null then 'null' else cast( [WorkOnStop] as varchar(10)) end)+','+
           (case when [FuelGradeID]  is null then 'null' else cast( [FuelGradeID]  as varchar(10)) end)+','+
           (case when [DischargeThreshold]  is null then 'null' else cast( [DischargeThreshold] as varchar(10)) end)+','+
           (case when [RefuelingThreshold]  is null then 'null' else cast( [RefuelingThreshold] as varchar(10)) end)+','+
           (case when [RefuelingTimeThreshold] is null then 'null' else cast( [RefuelingTimeThreshold] as varchar(10)) end)+','+
           (case when [DischargeTimeThreshold] is null then 'null' else cast( [DischargeTimeThreshold] as varchar(10)) end)
                
FROM bn.dbo.SYS_DEV_Sensor where id=@sens_id
ORDER BY id

SELECT @res = @res + item + ', ' from @rest      
delete from @rest -- надо все строки удалить, что бы не мешались

insert into @t
SELECT 'values('+substring(@res,1,len(@res)-1)+')'
insert into @t
values('set @sens_id=@@IDENTITY      
      end')
           
      -- теперь блок удаления тарировки по данному датчику
           
insert into @t
values('-- удаление старой тарировки')
insert into @t
values('DELETE FROM [BN].[dbo].[FLEET_AnalogTarirovka]
           WHERE IDSensor=@sens_id')
-- теперь добавляем новую тарировку           

      declare cur cursor local for
      select  cast([MinValue_Real]as varchar(10))+','+
           cast([MaxValue_Real]as varchar(10))+','+
           cast([MinValue_Tar]as varchar(10))+','+
           cast([MaxValue_Tar]as varchar(10))
           from [BN].[dbo].[FLEET_AnalogTarirovka]
           where IDSensor=@sens_id

set @res=''
      open cur
      declare @str varchar(8000)
      fetch next from cur into @res
      while @@fetch_status=0 begin
insert into @t
values('insert into [BN].[dbo].[FLEET_AnalogTarirovka]
(MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor
)
values ('+@res+',@sens_id)')
      fetch next from cur into @res
      end
close cur
deallocate cur
           
select * from @t
END

GO

declare @t table (string varchar(500)) declare @device_code varchar(20), @device_id int, @sens_num int, @sens_id int
select @device_code='00163000',@sens_num=10
set @device_id=(select id from bn.dbo.SYS_DEV_Device where code=@device_code and DeviceActive=1)
if @device_id is null begin print 'не найден id прибора по его номеру '+@device_code
print 'выполнение прервано'
select * from @t; return end
set @sens_id=(select [id] from BN.dbo.SYS_DEV_Sensor       where num=@sens_num and device_id=@device_id and sensor_type_id=0)
if @sens_id is null begin      
insert into bn.dbo.SYS_DEV_Sensor ([device_id],[num],[sensor_type_id],[IsAlarm],[description]
           ,[IconID],[SensFuelConsWin],[SensFuelConsSum],[SaveAsEvent],[priority],[Units]
           ,[SensorActive],[HasOwnTank],[MinValue],[MaxValue],[SensorClassificationId],[ObjectExtStatusID]
           ,[MapColor],[TableColor],[SignFontName],[SignCharCode],[progress_total],[VarThreshold]
           ,[AnalogSensorPrecision],[TankSensorID] ,[WorkOnStop] ,[FuelGradeID] ,[DischargeThreshold]
           ,[RefuelingThreshold] ,[RefuelingTimeThreshold],[DischargeTimeThreshold])
values(@device_id,@sens_num ,0,0,'топливо',2268,null,null,0,0,'л.',1,0,null,null,1,null,null,null,null,null,0,0,2,null,0,null,20,20,180,90)
set @sens_id=@@IDENTITY    end
-- удаление старой тарировки
DELETE FROM [BN].[dbo].[FLEET_AnalogTarirovka]        WHERE IDSensor=@sens_id
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (0,14,0,0,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (14,15,0,2,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (15,24,2,4,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (24,38,4,6,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (38,53,6,8,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (53,65,8,10,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (65,77,10,12,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (77,87,12,14,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (87,98,14,16,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (98,111,16,18,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (111,131,18,20,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (131,250,20,35.3,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (250,378,35.3,50.2,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (378,499,50.2,65.3,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (499,622,65.3,80.3,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (622,744,80.3,95.3,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (744,866,95.3,110.3,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (866,988,110.3,125.4,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (988,1109,125.4,140.4,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (1109,1230,140.4,155.4,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (1230,1348,155.4,170.2,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (1348,1469,170.2,185.3,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (1469,1589,185.3,200.3,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (1589,1709,200.3,215.3,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (1709,1812,215.3,230.4,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (1812,1925,230.4,245.3,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (1925,2044,245.3,260.3,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (2044,2164,260.3,275.3,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (2164,2282,275.3,290.3,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (2282,2402,290.3,305.3,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (2402,2521,305.3,320.3,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (2521,2641,320.3,335.3,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (2641,2760,335.3,350.3,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (2760,2880,350.3,365.2,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (2880,2999,365.2,380.3,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (2999,3105,380.3,395.3,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (3105,3226,395.3,410.4,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (3226,3348,410.4,425.3,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (3348,3470,425.3,440.3,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (3470,3594,440.3,455.3,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (3594,3720,455.3,470.2,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (3720,3856,470.2,485.3,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (3856,3954,485.3,495.7,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (3954,4095,495.7,500,@sens_id)
insert into [BN].[dbo].[FLEET_AnalogTarirovka]  (MinValue_Real,MaxValue_Real,MinValue_Tar,MaxValue_Tar,IDSensor  )  values (4095,4096,500,500,@sens_id)

DECLARE @RC int
DECLARE @device_code varchar(20)
DECLARE @sens_num int

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

EXECUTE @RC = [BN_Jobs].[dbo].[Create_script_tarirovka]    
      @device_code='00163000'
     ,@sens_num=10
GO