-- Автор:  Бондарь Михаил
-- Описание:    Скрипт формирует новый скрипт для CyberFleet, для загрузки в него приборов и машин.
-- база данных BNComplex = BNcomplex1 --  изменить при необходимости
use [BNComplex1] -- имя базы данных BNComplex  v.2
go

declare @t table (str1 varchar(500)) -- таблица строк нового скрипта
declare @user_name nvarchar(64), @Org_name varchar(64)
declare @CF varchar(20)
set @user_name=N'StroyPlus' -- имя пользователя BNCOmplex, из чьих подписок нужно выбрать приборы и машины   
set @Org_name=N'СтройПлюс' -- имя организации в КиберФлит = должна уже быть создана в киберфлите,   
set @CF='[BN].'
declare cur cursor local for
   SELECT D.devnum,d.PhoneNum,[DefaultName]
   FROM [dbo].[Subscriptions] as s
   inner join dbo.Objects as O on O.ObjectID=s.[ObjectID]
   inner join dbo.DevicesOnObjects as DOO on DOO.ObjectID=s.[ObjectID]
   inner join dbo.Devices as D on d.DeviceID=DOO.DeviceID
   inner join dbo.Login as L on L.LoginID=s.LoginID
   where [User]=@user_name and getdate() between [start] and [end]   
   and EquipID=13 -- фильтр только для приборов M2M Cyber GLX            
   ;
declare @devnum  varchar(16), @tel_number char(50),@gar_number varchar(20);
insert into @t
values ('set nocount on')
insert into @t
values ('declare @codes table (code varchar(16),tel_number char(50),gar_number varchar (20))')

open cur
fetch next from cur into @devnum, @tel_number,@gar_number
while @@FETCH_STATUS=0 begin
   insert into @t
   values ('insert into @codes (code,tel_number,gar_number)')
   insert into @t
   values ('values ('''+@devnum+''','''+isnull(@tel_number,'')+''','''+@gar_number+''')')
   fetch next from cur into @devnum, @tel_number, @gar_number
end -- while @@FETCH_STATUS=0
insert into @t
values ('')
insert into @t
values ('declare @code varchar(16),@tel_number char(50)')
insert into @t
values ('declare cur cursor local for ')
insert into @t
values ('select C.code,c.tel_number from @codes as c')
insert into @t
values ('left join [BN].dbo.SYS_DEV_Device as D on d.code=c.code and d.DeviceActive=1')
insert into @t
values ('where d.id is null')
insert into @t
values ('')
insert into @t
values ('open cur')
insert into @t
values ('fetch next from cur into @code,@tel_number')
insert into @t
values ('while @@FETCH_STATUS=0 begin')
insert into @t
values ('    INSERT INTO [BN].[dbo].[SYS_DEV_Device] ([type_id],[code],[tel_number],[DeviceActive],[opt_int1],[opt_int2],[opt_int3])')
insert into @t
values ('    values (25, @code,@tel_number,1,0,0,0)')
insert into @t
values ('    fetch next from cur into @code,@tel_number')
insert into @t
values ('end')
insert into @t
values ('close cur')

insert into @t
values ('deallocate cur')

insert into @t
values ('')

insert into @t
values ('declare @OrganizationId int')
insert into @t
values ('set @OrganizationId=(select id from '+@CF+'dbo.SYS_OrgRequisite where ShortName='''+@Org_name+''');')
insert into @t
values ('')

insert into @t
values ('if @OrganizationId is not null')
insert into @t
values ('INSERT INTO '+@CF+'dbo.FLEET_Object (Device_ID,ObjectGarNum,ObjectGosNum,[ObjectTextPosType],[ObjectActive],OrganizationId)')
insert into @t
values ('select d.id,c.gar_number,c.gar_number,2,1,@OrganizationId from @codes as c')
insert into @t
values ('inner join '+@CF+'dbo.SYS_DEV_Device as d on C.code=d.code and d.DeviceActive=1')
insert into @t
values ('left join '+@CF+'dbo.FLEET_Object as O on O.Device_ID=d.id')
insert into @t
values ('where O.ObjectID is null')
insert into @t
values ('else begin print ''Error: Не найдена организация. Обработка превана'' return end')
insert into @t
values ('')

insert into @t
values ('INSERT INTO '+@CF+'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 from @codes as c')
insert into @t
values ('inner join '+@CF+'dbo.SYS_DEV_Device as d on C.code=d.code and d.DeviceActive=1')
insert into @t
values ('inner join '+@CF+'dbo.FLEET_Object as O on O.Device_ID=d.id')
insert into @t
values ('left join '+@CF+'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')

select * from @t
GO