Parsing the site using T-SQL

Good day! A couple of years ago, I read forums on the Internet and prepared a direct T-SQL query that received GPS coordinates from the delivery addresses of all the company's customers in the 1C: Trade Management, Version 11 database. The transition to work by coordinates was caused by frequent cases of changes in the address classifier in different programs of departments (sales department and delivery department). The persuasion of departments to work in the same program without success.

Step one:

- Get the coordinates of a single client from his delivery address (for debugging a template):

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 

Step two:

- Enumerate all client delivery addresses and get GPS coordinates:

 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 

Step Three:

- Clearing the table of import of exit requests in the “ANTOR LogisticsMaster” route building program:
')

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

Step Four:

- Uploading applications for exit to the ANTOR LogisticsMaster route building program:

 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*/) 

Step Five:

- Update the status of requests for exit in the program "1C: Trade Management, Version 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 

You ask how I got to "this"?

The answer comes (if you dig a little in the memory with a psychologist):

- 20 years ago I “worked” at the UAZ in a friendly team of the department of automated enterprise management system technician.
I can’t call it work at all (the green teenager is always doing everything wrong).
I remember all the guys and remember with tears how I miss them. Unfortunately, it’s impossible to find the coordinates of Ivan Ivanovich Davydenko (he was the one who tried to dissuade
me from learning the language T-SQL - the forbidden fruit), I heard he went to Aviastar.