use bn_patp_dob -- работа с БД bn_patp_dob
go
SET XACT_ABORT ON;
BEGIN TRAN T1; -- работа с транзакцией - если что пойдет не так, записи не будут произведены

delete from dbo.CF_Text_Char_Point
delete from dbo.CF_Text_Char
if object_id('tempdb..#temp') is not null drop table #temp
create table #temp (
  [str] varchar(1000)
) --создадим временную таблицу с тремя колонками
-- как в таблице FLEET_Object

---create table #temp (code varchar(16)) --создадим временную таблицу с одной колонкой
BULK INSERT #temp
  -- вставка из файла во временную таблицу
FROM 'd:\1\font.eps' -- из файла
WITH  
(  
--FIELDTERMINATOR=';', -- разделитель полей - exel именно с ним выгружает
FIRSTROW = 1, -- с номера строки строки
ROWTERMINATOR = '\r', -- разделитель строк enter
codepage = 'acp'
--KEEPNULLS
)

--select * from #temp

declare cur cursor local for
select [str] from #temp;

declare @str varchar(1000), @n_str varchar(1000)
declare @num_str float, @lastid int
declare @code int,@second_space int,@ravno_index int,@CF_Text_Char_ID int, @long float, @lat float
set @num_str=-1

open cur

while 1=1 begin
  fetch next from cur into @str
  if @@FETCH_STATUS!=0 break
  set @str=LTRIM(rtrim(@str))
  --print ''''+@str+''''
  --print LEFT (@str,8)
  if PATINDEX('%Note%', @str)>0 -- строка начала описания символа
   begin
    --print '----нашли'
    set @n_str=SUBSTRING (@str,1,1000)
    set @ravno_index=PATINDEX('%=%', @n_str)
    set @second_space=PATINDEX('% %', @n_str)
    set @code=cast(SUBSTRING (@n_str,@ravno_index+1,@second_space-@ravno_index ) as int)  
    --print @code
    insert into dbo.CF_Text_Char (code)
    values (@code);
    set @CF_Text_Char_ID=@@IDENTITY
    set @num_str=0
     
   end
   
  if @num_str>=0 begin
   --print @num_str
   if @num_str>=3 begin -- описание точек
    --print  
    if @str='s' -- вот тут закончили
     begin
      select @code=0, @num_str=-1
      delete from dbo.CF_Text_Char_Point where id=@lastid
     end
    else
    if RIGHT (@str,1)='m' or RIGHT (@str,1)='l' begin
     --print substring(@str,1,PATINDEX('% %', @str)-1)
     --print substring(@str,PATINDEX('% %', @str)+1,LEN(@str)-2-PATINDEX('% %', @str))
     select @long = cast(substring(@str,1,PATINDEX('% %', @str)-1) as float),@lat = cast(substring(@str,PATINDEX('% %', @str)+1,LEN(@str)-2-PATINDEX('% %', @str)) as float)
     insert into dbo.CF_Text_Char_Point (CF_Text_Char_ID,ord,long,lat)
     values (@CF_Text_Char_ID,@num_str-2,@long,@lat)
     set @lastid=@@IDENTITY
    end
    else begin set @num_str=@num_str-1 end
   end
   set @num_str=@num_str+1
   
  end
end
close cur
deallocate cur

drop table #temp

update t1
set len_long= long,  len_lat=lat
--select *  
from dbo.CF_Text_Char as t1
inner join  
(select CF_Text_Char_ID,
       0.000+max([long])-MIN(long) as long, 0.000+max(lat)-min(lat) as lat
     from [dbo].[CF_Text_Char_Point]
     group by [CF_Text_Char_ID]) as t2 on t1.[id]=t2.[CF_Text_Char_ID]

update t1
set long=t1.long-t2.long,lat=t1.lat-t2.lat

from CF_Text_Char_Point as t1  
inner join (select CF_Text_Char_ID,
       MIN(long) as long, min(lat) as lat
     from [dbo].[CF_Text_Char_Point]
     group by [CF_Text_Char_ID]) as t2 on t2.CF_Text_Char_ID=t1.CF_Text_Char_ID

commit tran t1 -- фиксации транзакции

UPDATE [bn_patp_dob].[dbo].[CF_Text_Char]  
     SET [char] = 'х'  
   WHERE code=32  

UPDATE [bn_patp_dob].[dbo].[CF_Text_Char]  
     SET [char] = 'у'  
   WHERE code=33  
UPDATE [bn_patp_dob].[dbo].[CF_Text_Char]  
     SET [char] = 'й'  
   WHERE code=34  
UPDATE [bn_patp_dob].[dbo].[CF_Text_Char]  
     SET [char] = 'в'  
   WHERE code=35  
UPDATE [bn_patp_dob].[dbo].[CF_Text_Char]  
     SET [char] = 'а'  
   WHERE code=36  

UPDATE [bn_patp_dob].[dbo].[CF_Text_Char]  
     SET [char] = 'м'  
   WHERE code=37  
   UPDATE [bn_patp_dob].[dbo].[CF_Text_Char]  
     SET [char] = '2'  
   WHERE code=38  

UPDATE [bn_patp_dob].[dbo].[CF_Text_Char]  
     SET [char] = ' '  
   WHERE code=39  
    
    
UPDATE [bn_patp_dob].[dbo].[CF_Text_Char]  
     SET [char] = 'с'  
   WHERE code=40  
    
    
UPDATE [bn_patp_dob].[dbo].[CF_Text_Char]  
     SET [char] = '1'  
   WHERE code=41  
    
    
UPDATE [bn_patp_dob].[dbo].[CF_Text_Char]  
     SET [char] = '0'  
   WHERE code=42  
    
    
UPDATE [bn_patp_dob].[dbo].[CF_Text_Char]  
     SET [char] = 'л'  
   WHERE code=43  
    
    
UPDATE [bn_patp_dob].[dbo].[CF_Text_Char]  
     SET [char] = 'е'  
   WHERE code=44  
    
    
UPDATE [bn_patp_dob].[dbo].[CF_Text_Char]  
     SET [char] = 'т'  
   WHERE code=45  
      
UPDATE [bn_patp_dob].[dbo].[CF_Text_Char]  
     SET [char] = 'и'  
   WHERE code=46

UPDATE [bn_patp_dob].[dbo].[CF_Text_Char]  
     SET [char] = '-'  
   WHERE code=47

UPDATE [bn_patp_dob].[dbo].[CF_Text_Char]  
     SET [char] = 'Р'  
   WHERE code=48

UPDATE [bn_patp_dob].[dbo].[CF_Text_Char]  
     SET [char] = 'Д'  
   WHERE code=49

UPDATE [bn_patp_dob].[dbo].[CF_Text_Char]  
     SET [char] = 'Ц'  
   WHERE code=50

UPDATE [bn_patp_dob].[dbo].[CF_Text_Char]  
     SET [char] = 'Т'  
   WHERE code=51

UPDATE [bn_patp_dob].[dbo].[CF_Text_Char]  
     SET [char] = 'б'  
   WHERE code=52

UPDATE [bn_patp_dob].[dbo].[CF_Text_Char]  
     SET [char] = 'о'  
   WHERE code=53

DECLARE @RC int
DECLARE @text varchar(1000)
DECLARE @ATCode varchar(16)
DECLARE @start_long float
DECLARE @start_lat float
DECLARE @start_date date
DECLARE @diff_date_ss int

-- TODO: задайте здесь значения параметров.
set dateformat ymd
EXECUTE @RC = [bn_patp_dob].[dbo].[CF_Write_text]  
    @text ='йух вам' -- что выводим
  ,@ATCode ='00000000' -- для какого прибора
  ,@start_long =41 -- точка начала long
  ,@start_lat =52 -- точка начала lat
  ,@start_date ='2012/05/03 05:00' -- время начала вывода
  , @diff_date_ss =60  

EXECUTE @RC = [bn_patp_dob].[dbo].[CF_Write_text]  
    @text ='м2м' -- что выводим
  ,@ATCode ='00000000' -- для какого прибора
  ,@start_long =41.2 -- точка начала long
  ,@start_lat =51.86 -- точка начала lat
  ,@start_date ='2012/05/03 09:00' -- время начала вывода
  , @diff_date_ss =60  
GO

USE [bn_patp_dob]
GO
/****** Object:  StoredProcedure [dbo].[CF_Write_text]    Script Date: 05/23/2012 11:29:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:  <Бондарь Михаил>
-- Create date: 12/08/2011
-- Description:    Отправка инфо об отсуствии данных по ТС
-- =============================================
ALTER PROCEDURE [dbo].[CF_Write_text]  
  (@text varchar(1000)='хуй' -- что выводим
  ,@ATCode varchar(16)='00000000' -- для какого прибора
  ,@start_long float=0 -- точка начала long
  ,@start_lat float =0 -- точка начала lat
  ,@start_date datetime='2012/05/02 13:00:00' -- время начала вывода
  , @diff_date_ss int=60 -- шаг времени вывода
  )
AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;
   
  declare @string_c varchar(1),@TC_id int
  declare @ObjID int, @DevID int
   
  declare @k1 float, @k2 float,@k1l float
  select @k1=10,@k1l=12,@k2=7
   
  set @DevID=(select id from BN.dbo.SYS_DEV_Device where code=@ATCode and DeviceActive=1)
  set @ObjID=(
  select objectid from bn.dbo.FLEET_Object as o  
  where O.Device_ID=@DevID and O.ObjectActive=1  
  )  
   
  if @ObjID is null return 1
declare cur cursor local for
select string.c, TC.id from  
(select substring(e.ename,iter.number,1) as C
from (select @text as ename) as e,
(select number
FROM master..spt_values
WHERE spt_values.type = 'P' AND number between 1 and 1000) as iter
where iter.number <= len(e.ename)) as string
left join dbo.CF_Text_Char as TC on UNICODE (TC.char)=UNICODE (string.c);

declare @long float, @lat float, @num int, @llong float, @llat float
set @num=0
open cur
while 1=1 begin
  fetch next from cur into @string_c,@TC_id
  if @@FETCH_STATUS!=0 break
  if @TC_id is null print 'для символа '+@string_c+ ' не задано сотвествия'  
  else  
  begin
   declare cur_point cursor local for
   select [long],[lat] FROM [bn_patp_dob].[dbo].[CF_Text_Char_Point] where [CF_Text_Char_ID]=@TC_id order by [ord]
   open cur_point
   while 1=1 begin
    fetch next from cur_point into @long, @lat
    if @@FETCH_STATUS!=0 break
    --print  [BN].[dbo].[SYS_GetAzimuthOnEarth] (@llong,@llat,@long/1000+@start_long+cast(@num as float)*0.1,@lat/1000+@start_lat)
    --print '--'
    --print @llong
    --print @llat
    --print @long/1000+@start_long+cast(@num as float)*0.1
    --print @lat/1000+@start_lat
    insert into bn.dbo.SYS_DEV_ArchiveData ([device_id],[device_code],[tele_time],[server_time],[transfer_time]
            ,[long],[lat],[valid],[speed],[direction],[height]
            ,[digit_sens_num],[analog_sens_num],[analog_data],[flags],[BasePoint_id],[Distanse]
            ,[directionPoint],[TextHistory],[ObjectID],[CurrentRun])
            select  @DevID, @ATCode, @start_date, GETDATE(),GETDATE(),
            @long/@k1+@start_long+cast(@num as float)*@k2, @lat/@k1l+@start_lat,0,0,
            [dbo].[get_direction] (@llat,@llong,@lat/@k1l+@start_lat,@long/@k1+@start_long+cast(@num as float)*@k2)
             
            ,0,
            0,0,-1,0,null,0,
            0,'',@ObjID,0
             
            set @start_date=DATEADD(SS,@diff_date_ss,@start_date)
            select @llat=@lat/@k1l+@start_lat,@llong=@long/@k1+@start_long+cast(@num as float)*@k2
     
   end
   close cur_point
   deallocate cur_point
    
  end
  set @num=@num+1

end
close cur
deallocate cur

END