Парсинг сайта средствами языка T-SQL

в 13:37, , рубрики: Microsoft SQL Server, метки: , ,

Добрый день!
Пару лет назад почитал форумы в интернете и подготовил прямой запрос на языке T-SQL получающий координаты GPS из адресов доставки всех клиентов компании в базе «1С: Управление торговлей, Версия 11». Вызван переход на работу по координатам был частыми случаями изменения адресного классификатора в разных программах отделов (отдела продаж и отдела доставки). Уговоры отделов работать в одной программе пока без успешны.

Шаг первый:
— Получить координаты одного клиента из адреса его доставки (для отладки шаблона):

Declare @URI1 nvarchar(4000)='107113, Москва г, Поперечный просек, дом № 1-Г'
DECLARE @count int, @i1 int, @urlReturn nvarchar(4000)
Declare @s1 char
SET @count = LEN(@URI1)
SET @i1 = 1
SET @urlReturn = ''
while (@i1 <= @count)
begin 
  select @s1 = SUBSTRING(@URI1, @i1, 1)
  if (ASCII(@s1) > 191 AND ASCII(@s1) <= 256)
  begin
    select @urlReturn = @urlReturn + sys.fn_varbintohexstr(CAST(@s1 AS VARBINARY(MAX)))
    select @urlReturn = replace(@urlReturn, '0x', '%')
  end
  else
    select @urlReturn = @urlReturn + @s1
  set @i1 = @i1 +1
end
DECLARE @URI varchar(2000)='https://geocode-maps.yandex.ru/1.x/?geocode='+@urlReturn, @methodName varchar(50)='GET'
DECLARE @objectID int, @hResult int
EXEC 	@hResult = sp_OACreate 'WinHttp.WinHttpRequest.5.1', @objectID OUT
IF @hResult <> 0 goto destroy
EXEC @hResult = sp_OAMethod @objectID, 'open', null, @methodName, @URI, 'false'
IF @hResult <> 0 goto destroy
EXEC 	@hResult = sp_OAMethod @objectID, 'send', null
IF @hResult <> 0 goto destroy
DECLARE @t TABLE(s nvarchar(max))
INSERT @t
	EXEC sp_OAGetProperty @objectID, 'responseText'
IF @hResult <> 0 goto destroy
		DECLARE @n int
		DECLARE @STRLEN int
		SELECT @STRLEN=LEN(SUBSTRING(s,CHARINDEX('<pos>',s)+5,(CHARINDEX('</pos>',s)-CHARINDEX('<pos>',s))-5))+1 FROM @t
		SELECT @n=CHARINDEX(' ',SUBSTRING(s,CHARINDEX('<pos>',s)+5,(CHARINDEX('</pos>',s)-CHARINDEX('<pos>',s))-5)) FROM @t
		SELECT 
			SUBSTRING(s,CHARINDEX('<pos>',s)+5,@n) AS LON,
			SUBSTRING(SUBSTRING(s,CHARINDEX('<pos>',s)+5,(CHARINDEX('</pos>',s)-CHARINDEX('<pos>',s))-5),@n,@STRLEN-@n) AS LAT,
			SUBSTRING(s,CHARINDEX('<pos>',s)+5,(CHARINDEX('</pos>',s)-CHARINDEX('<pos>',s))-5) AS MAP
		FROM @t
destroy:
	exec sp_OADestroy @objectID

Шаг второй:
— Перебор всех адресов доставки клиентов и получение координат GPS:

DECLARE @URI1 AS nvarchar(4000)
DECLARE @ID1 AS nvarchar(11)
DECLARE curMarks CURSOR
    LOCAL SCROLL STATIC
    FOR
		SELECT
			[ki]._Fld2260 AS [Adress],
			[p].[_CODE]
		FROM [UT_TEST_COPY].[dbo].[_Reference107] as [p]
			LEFT OUTER JOIN [UT_TEST_COPY].[dbo].[_Reference107_VT2256] as [ki] WITH (NOLOCK) ON ([p].[_IDRRef]=[ki].[_Reference107_IDRRef]) 
		WHERE
			[ki]._Fld2259RRef=0x8757A30F90F658984F74B3E6BDCE0041
			AND [p]._Fld11004RRef=0xA576BCAEC54B2C9E11E23ACC96E85A13
			/*AND [p]._Fld11721RRef=0x814665286A763EC746207B8AD89C8693*/
			AND [p]._Fld11721RRef=0xB54E694250E409A6463884A95998E32A
    OPEN curMarks
    FETCH NEXT FROM curMarks
    INTO @URI1,@ID1;        
    WHILE @@FETCH_STATUS = 0
    BEGIN
		DECLARE @count int, @i1 int, @urlReturn nvarchar(4000)
		Declare @s1 char
		SET @count = LEN(@URI1)
		SET @i1 = 1
		SET @urlReturn = ''
		while (@i1 <= @count)
		begin 
		  select @s1 = SUBSTRING(@URI1, @i1, 1)
		  if (ASCII(@s1) > 191 AND ASCII(@s1) <= 256)
		  begin
			select @urlReturn = @urlReturn + sys.fn_varbintohexstr(CAST(@s1 AS VARBINARY(MAX)))
			select @urlReturn = replace(@urlReturn, '0x', '%')
		  end
		  else
			select @urlReturn = @urlReturn + @s1
		  set @i1 = @i1 +1
		end
		DECLARE @URI varchar(2000)='https://geocode-maps.yandex.ru/1.x/?geocode='+@urlReturn, @methodName varchar(50)='GET'
		DECLARE @objectID int, @hResult int
		EXEC 	@hResult = sp_OACreate 'WinHttp.WinHttpRequest.5.1', @objectID OUT
		IF @hResult <> 0 goto destroy
		EXEC @hResult = sp_OAMethod @objectID, 'open', null, @methodName, @URI, 'false'
		IF @hResult <> 0 goto destroy
		EXEC 	@hResult = sp_OAMethod @objectID, 'send', null
		IF @hResult <> 0 goto destroy
		DECLARE @t TABLE(s nvarchar(max))
		INSERT @t
			EXEC sp_OAGetProperty @objectID, 'responseText'
		IF @hResult <> 0 goto destroy
		DECLARE @LAT nvarchar(20)
		DECLARE @LON nvarchar(20)
		DECLARE @n int
		DECLARE @STRLEN int
		SELECT @STRLEN=LEN(SUBSTRING(s,CHARINDEX('<pos>',s)+5,(CHARINDEX('</pos>',s)-CHARINDEX('<pos>',s))-5))+1 FROM @t
		SELECT @n=CHARINDEX(' ',SUBSTRING(s,CHARINDEX('<pos>',s)+5,(CHARINDEX('</pos>',s)-CHARINDEX('<pos>',s))-5)) FROM @t
		SELECT 
			@LON=SUBSTRING(s,CHARINDEX('<pos>',s)+5,@n),
			@LAT=SUBSTRING(SUBSTRING(s,CHARINDEX('<pos>',s)+5,(CHARINDEX('</pos>',s)-CHARINDEX('<pos>',s))-5),@n,@STRLEN-@n)
		FROM @t
		DECLARE @UpdateSQL AS VARCHAR(MAX)		
		SET @UpdateSQL = ' UPDATE [UT_TEST_COPY].[dbo].[_Reference107] ' +
						 ' SET _Fld11002 = ' + RTRIM(LTRIM(@LAT)) + ',' +
						 ' _Fld11003 = ' + RTRIM(LTRIM(@LON)) + 
						 ' WHERE _CODE = '+CHAR(39)+ @ID1 + CHAR(39)
		EXECUTE(@UpdateSQL)
		destroy:
			exec sp_OADestroy @objectID
		DELETE FROM @t
		FETCH NEXT FROM curMarks
		INTO @URI1,@ID1;        
	END
CLOSE curMarks
DEALLOCATE curMarks

Шаг третий:
— Очистка таблицы импорта заявок на выезд в программе построения маршрутов «ANTOR LogisticsMaster»: 

DELETE FROM [LMaster].[dbo].[D__IMPORT0]

Шаг четвертый:
— Выгрузка заявок на выезд в программу построения маршрутов «ANTOR LogisticsMaster»:

INSERT INTO [LMaster].[dbo].[D__IMPORT0]
       ([EXT_ID]
       ,[EXT_STRID]
       ,[OPER_ID]
       ,[ORD_TYP]
       ,[DELIV_DATE]
       ,[ROUTE_ID]
       ,[ROUTE_NUM]
       ,[NUM_INROUTE]
       ,[CUST_ID]
       ,[CUST_STRID]
       ,[RENTED]
       ,[UNLOAD_TYP]
       ,[CATEGORY_ID]
       ,[TIME_BEG]
       ,[TIME_END]
       ,[TIME_UNLOAD]
       ,[ACTIVE]
       ,[ZONE_ID]
       ,[ACCESS_ID]
       ,[OGRSUM1]
       ,[OGRSUM2]
       ,[SUM3]
       ,[SUM4]
       ,[ADDR]
       ,[DISTR]
       ,[TOWN]
       ,[STREET]
       ,[HOUS]
       ,[CORP]
       ,[LINKED]
       ,[X]
       ,[Y]
       ,[DISTANC]
       ,[TIME_ARR]
       ,[VIRT]
       ,[SOST]
       ,[STR1]
       ,[STR2]
       ,[STR3]
       ,[STR4]
       ,[STR5]
       ,[STR6]
       ,[INT1]
       ,[INT2]
       ,[INT3]
       ,[MIN_CAR]
       ,[MAX_CAR]
       ,[EXP1]
       ,[EXP2]
       ,[EXP3]
       ,[MACROZONE_ID]
       ,[PICT]
       ,[WIDTH]
       ,[HEIGHT]
       ,[LENGTH]
       ,[STORE1]
       ,[STORE2]
       ,[STORE3]
       ,[STORE4]
       ,[STORE5]
       ,[LATITUDE]
       ,[LONGITUDE])
 VALUES
       (NULL /*[EXT_ID], int*/
       ,'"+НомерДокумента_SQL+"' /*[EXT_STRID], nvarchar(36)*/
       ,0 /*[OPER_ID], int*/
       ,0 /*[ORD_TYP], int*/
       ,(Convert(datetime,'"+ДатаДокумента_SQL+"',104)) /*[DELIV_DATE], datetime*/
       ,NULL /*[ROUTE_ID], int*/
       ,NULL /*[ROUTE_NUM], int*/
       ,NULL /*[NUM_INROUTE], int*/
       ,NULL /*[CUST_ID], int*/
       ,'"+Код_SQL+"' /*[CUST_STRID], nvarchar(36)*/
       ,NULL /*[RENTED], bit*/
       ,0 /*[UNLOAD_TYP], int*/
       ,0 /*[CATEGORY_ID], int*/
       ,'"+ВремяДоставкиС_SQL+"' /*[TIME_BEG], datetime*/
       ,'"+ВремяДоставкиПо_SQL+"' /*[TIME_END], datetime*/
       ,(Convert(datetime,'19000101',104)) /*[TIME_UNLOAD], datetime*/
       ,NULL /*[ACTIVE], int*/
       ,"+Зона_SQL+" /*[ZONE_ID], int*/
       ,NULL /*[ACCESS_ID], int*/
       ,"+Забрать_SQL+" /*[OGRSUM1], float*/
       ,"+Доставить_SQL+" /*[OGRSUM2], float*/
       ,0 /*[SUM3], float*/
       ,0 /*[SUM4], float*/
       ,'"+Адрес_SQL+"' /*[ADDR], nvarchar(100)*/
       ,"+Регион_SQL+" /*[DISTR], nvarchar(50)*/
       ,"+Город_SQL+" /*[TOWN], nvarchar(50)*/
       ,"+Улица_SQL+" /*[STREET], nvarchar(50)*/
       ,"+Дом_SQL+" /*[HOUS], nvarchar(20)*/
       ,"+Корпус_SQL+" /*[CORP], nvarchar(20)*/
       ,NULL /*[LINKED], int*/
       ,NULL /*[X], int*/
       ,NULL /*[Y], int*/
       ,NULL /*[DISTANC], float*/
       ,NULL /*[TIME_ARR], datetime*/
       ,NULL /*[VIRT], int*/
       ,NULL /*[SOST], int*/
       ,'"+Наименование_SQL+"' /*[STR1], nvarchar(255)*/
       ,'"+Договор_SQL+"' /*[STR2], nvarchar(255)*/
       ,'"+Менеджер_SQL+"' /*[STR3], nvarchar(255)*/
       ,'"+Телефон_SQL+"' /*[STR4], nvarchar(255)*/
       ,'' /*[STR5], nvarchar(100)*/
       ,'"+Задание_SQL+"' /*[STR6], nvarchar(255)*/
       ,0 /*[INT1], int*/
       ,0 /*[INT2], int*/
       ,0 /*[INT3], int*/
       ,NULL /*[MIN_CAR], float*/
       ,NULL /*[MAX_CAR], float*/
       ,NULL /*[EXP1], nvarchar(36)*/
       ,NULL /*[EXP2], nvarchar(36)*/
       ,NULL /*[EXP3], nvarchar(36)*/
       ,NULL /*[MACROZONE_ID], int*/
       ,NULL /*[PICT], int*/
       ,NULL /*[WIDTH], float*/
       ,NULL /*[HEIGHT], float*/
       ,NULL /*[LENGTH], float*/
       ,NULL /*[STORE1], int*/
       ,NULL /*[STORE2], int*/
       ,NULL /*[STORE3], int*/
       ,NULL /*[STORE4], int*/
       ,NULL /*[STORE5], int*/
       ,"+Широта_SQL+" /*[LATITUDE], FLOAT*/
       ,"+Долгота_SQL+" /*[LONGITUDE], FLOAT*/)

Шаг пятый:
— Обновление статуса заявок на выезд в программе «1С: Управление торговлей, Версия 11»:

SELECT 
     D.EXT_STRID AS Код_CRM, 
     D.NAME AS Водитель, 
     O.TIME_ARR AS Прибытие, 
     O.EXT_STRID AS Номер, 
     O.DISTANC AS Расстояние, 
     R.LEN-(SELECT 
             sum(O1.DISTANC)
           FROM dbo.D__ORDERS0 AS O1
           WHERE O1.ROUTE_ID=R.ID) AS РасстояниеДоОфиса,
     O.LATITUDE AS LATITUDE, 
     O.LONGITUDE AS LONGITUDE, 
     O.ZONE_ID AS Код_ЗоныДоставки 
 FROM dbo.D__ZONE0 AS Z WITH(NOLOCK)
   INNER JOIN dbo.D__DRIVERS AS D WITH(NOLOCK)
   INNER JOIN dbo.D__CARS AS C WITH(NOLOCK) ON D.ID = C.DRIVER_ID 
   INNER JOIN dbo.D__ROUTE0 AS R WITH(NOLOCK) ON C.ID = R.CAR_ID ON Z.ID = R.ZONE_ID 
   RIGHT OUTER JOIN dbo.D__ORDERS0 AS O WITH(NOLOCK) ON R.ID = O.ROUTE_ID 
 WHERE 
     (O.SOST >= 0) AND (O.ROUTE_ID <> 0) 
 ORDER BY 
  D.NAME, 
  O.ROUTE_NUM, 
  O.NUM_INROUTE

Вы спросите как я до «такого» докатился?
Ответ приходит (если немного покопаться в памяти с психологом):
— 20 лет назад я «работал» на УАЗе в дружном коллективе отдела АСУП техником.
Работой я это назвать никак не могу (зеленый подросток вечно делающий все не так).
Всех ребят я помню и вспоминаю со слезами, как я по ним скучаю. К сожалению никак не получается найти координаты Давиденко Ивана Ивановича (именно он и отговаривал
меня от изучения языка T-SQL — запретный плод), слышал он ушел на Авиастар.
С уважением

Автор: new player

Источник

* - обязательные к заполнению поля

Главная   |  Архив новостей  |   Android  |   Google  |   Apple  |   Microsoft  |   Информационная безопасность  |   Веб – разработка
Публикации RSS  |  Комментарии RSS
https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js