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

    Добрый день! Пару лет назад почитал форумы в интернете и подготовил прямой запрос на языке 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 — запретный плод), слышал он ушел на Авиастар.
    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More
    Ads

    Comments 26

      +2
      Переименуйте статью в «как сделать так чтобы меня не уволили или happy debugging, bitches»
      Поставил бы минус, но кармы не хватает.
        0
        Доброе утро!
        Возможно вам приходилось решать такие задачи другим путем.
        Пишите вас оценят.
        С уважением
          +1
          А SQL не разрешает делать комментарии в коде?
          А 1C не позволяет вызывать процедуры из внешних библиотек?
          Почему нельзя было, наконец, написать, скрипт на любом языке программирования и просто положить результаты в базу?
          Мне всегда казалось что база данных — слишком ценный ресурс сети чтобы тратить его время на подобные задачи.
          Вы, конечно, извините за прямоту, но с моей стороны статья выглядит как «потому что могу». Решение ужасное со всех точек зрения.
          В серьезной компании такая статья в резюме, для меня например, служила бы прямым поводом даже не приглашать на собеседование.
            –1
            Добрый день еще раз!
            Комментарий есть. Внешние библиотеки тоже возможны.
            Можно и так, но это займет на порядок больше времени.
            Решение задачи было не для вас. К вам я пока не обращался.
            П.С. В резюме этой статьи нет. Публикации я делаю для потомков.
            С уважением
              +1
              Если вы выкладываете статьи в общий доступ на ресурс с другими участниками, то будьте готовы к коментариям. И не только к восхваляющим. Если для потомков и без комментариев — то можно же в своем блоге.
              И еще раз — подходы «потому что работает», «не умею по другому», «потому что написал быстро», «потому что могу», «не знаю другого языка» не очень ценятся.
              Решение работает, для себя можно писать как угодно.
              Но для «потомков» я бы хотел высказать и свое предупреждение: не делайте так. На SQL можно много чего сделать, но это не значит что нужно. Код должен быть легко читаем, сопровождаем и следовать хоть каким-то общим принципам построения архитектуры.

              Вы показали как с помощью SQL можно скачать данные и разобрать их. Ок, вы молодец что знаете как. Именно как «я молодец» статья может выглядеть.
              Но как руководство к созданию подобных подходов — я считаю что она вредна.

              Заметьте, я не критикую подход к ручному разбору HTML(XML?) вместо использования парсеров. Для небольшого объема так иногда проще, соглашусь.
              Использование COM-объекта WinHttpRequest — не переносимо, могут быть вопросы по безопасности, но ок, имеет право на жизнь в каких-то условиях.

              Был, кажется, доклад Федора Сигаева (могу ошибаться автором) на одной из конференций по PostgreSQL, где он рассказывал как рекурсивно нарисовать снежинку или елочку в PostgreSQL или написать парсер JSON на SQL. Каждый пример завершался фразой «да, можно, но зачем?»
                0
                Прав не хватает на плюсик, я с вами согласен
                  0
                  Добрый день ещё раз!
                  Спасибо за ваш комментарий.
                  Рад, что хоть кто то прочитал статью.
                  Публикацию делал как подсказку к
                  решению любых задач за пару часов.
                  С уважением
          +1
          Будет много запросов к геокодеру яндекса (запросы в курсоре) и вас забанят. Если запросов немного, то конечно прокатит. Как вариант, можно использовать геокодер от DaData, у них бесплатный тариф вполне может подойти чтобы лимит по запросам не превысить. На боевом окружении я бы предпочел легально использовать апи, чем однажды обнаружить что все перестало работать. Чисто мысли вслух)
            0
            Доброе утро еще раз!
            Данный геокодер легальный (API).
            С уважением
              +1
              Доброе утро. Конечно этот апи легальный при соблюдении всех условий его использования, но условия использования геокодера от яндекс подразумевают неиспользование его без отображения карты и результаты должны быть отображены на общедоступной карте.
              Использовать геокодер можно бесплатно, если в сутки к нему, а также к маршрутизатору и панорамам вы делаете суммарно не больше 25 тысяч запросов. Подробности о том, как считаются запросы, можно узнать в документации. Даже если обращения происходят по протоколу HTTPS, результаты должны быть отображены на общедоступной карте. Результаты геокодирования нельзя сохранять и использовать без карты. Также нужно соблюдать остальные условия бесплатного использования API.

              https://tech.yandex.ru/maps/geocoder/
              Как я понимаю, все пункты условия использования геокодера вы не соблюдаете (обязательное отображение карты на общедоступной странице, например), т.к. действо происходит в 1с, у меня бы их тоже не получилось соблюсти, поэтому я и считаю что использование этого апи нелегально для этой задачи (не соблюдаются все условия).
                0
                Доброе утро еще раз!
                Вы не знакомы ко сожалению с настройками нашей базы увы.
                В справочнике данные координаты (полученные через геокодинг)
                выводятся на карте яндекса (как по вашему их увидит пользователь).
                По количеству записей в сутки мы проходим (максимум 300).
                Классная ссылка — я там тоже в свое время активно бывал ;)
                С уважением
                  0
                  Эта карта общедоступна? Я ее могу увидеть или любой другой? Может быть у вас проект некоммерческий? Я действительно не знаю тонкостей вашей реализации, но условия использования этого апи все же не полностью соблюдаются:) Если я ошибаюсь, то приношу вам свои извинения. Я пока останусь при своем мнении.
                  Вообще статья мне нравится, решение рабочее.
                    0
                    Спасибо
            +1
            Где тэг «ненормальное программирование»?
              –1
              Доброе утро еще раз!
              Предложите свое решение «программированием».
              С уважением
                0
                Отчасти я согласен по поводу «ненормальности» этого решения, конкретно это касается вызова sp_OACreate, эта фича во включенном состоянии создает потенциальную дыру в безопасности, а она вполне не иллюзорна, учитывая что sql server у вас имеет доступ в инет. Веб запрос к геокодеру мне кажется лучше делать из 1с, а в хранимку передавать ответ сервера, если парсинг все же хочется сделать на t-sql. А если sql server доступен извне, то ему надо уделить достаточно внимания в плане безопасности. Возможно у вас просто не было возможности сделать иначе, поэтому не берусь судить, зачастую рамки исполнения сильно сужены и приходится искать такие решения.
                  0
                  Доброе утро еще раз!
                  Мое мнение:
                  — Получение данных средствами 1С заняли бы больше ресурсов (на порядок).
                  Про решение я имею в виду — реально выполненная задача.
                  С уважением
                  0
                  Не предназначен T-SQL для написания парсеров, для этого есть другие ЯП, да хоть на том же 1С можно было написать. Вы конечно решили задачу, но решили её путём забивания гвоздя пассатижами. Конечно, если молотка нет, решение оправдано, но это не значит, что забивать гвозди пассатижами — нормально.
                    –1
                    Добрый день еще раз!
                    Возможно вы автор этого языка, тогда да я с вами абсолютно согласен.
                    Если же это просто ваше мнение, то боюсь мне придется вам возразить.
                    В моем понимании решать задачу на встроенном языке 1с и есть забивание
                    С уважением
                      0

                      Держите -1 в карму по совокупности прилежного хамства в комментариях.

                0

                Во-первых, согласен с комментаторами о том, что T-SQL не предназначен для таких задач и что статья вредная. Во-вторых, почему Geektimes, а не Habrahabr? Там бы пост с большой вероятностью ушел в минус.

                  0
                  Добрый вечер!
                  Не соглашусь с вами коллега.
                  На хабре статья есть — у нее рейтинг +4.
                  С уважением
                    0

                    Ну и где она? Нашел только эту, скрытую в черновики автором или самим НЛО.


                    На заметку: писать "Добрый вечер" и "С уважением" в каждом сообщении — информационный шум.

                      0
                      Доброе утро!
                      Да, она скрыта — но рейтинг +4!
                      И снова не соглашусь с вами коллега.
                      -«Только лаской можно приручить любое живое существо» (М. Булгаков)
                      С уважением
                        0

                        Зачем ее скрывать если у нее положительный рейтинг? Позвольте вам не поверить.

                          0
                          Добрый день!
                          Это ваше дело.
                          С уважением

                Only users with full accounts can post comments. Log in, please.