Aggiornamento delle coordinate GPS di clienti e fornitori

' Il seguente snippet può essere inserito in un task per aggiornare le coordinate GPS di clienti e fornitori
'

dim key as string = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"  ' Inserire qui l'api key che si ottiene registrandosi gratuitamente al sito https://developer.mapquest.com/ (free fino a 15000 richieste/mese)



dim via,citta,prov,cap,nazione,CODICE as string
dim x as integer
Dim Q as new QWTable
Q.Database=DB
Q.sql="select * from CLIFOR where VIA is not null and CITTA is not null and (GPS_LAT=0 or GPS_LONG=0)"
Q.requestlive=false
Q.allowallrecords=true
Q.active=true


if not q.rowset.endofset
   for x=0 to q.rowset.count-1
      via = q.rowset.fields("via").value
      citta = q.rowset.fields("citta").value
      prov = q.rowset.fields("prov").value
      cap = q.rowset.fields("cap").value
      nazione = q.rowset.fields("nazione").value
      if empty(nazione)
         nazione="Italy"
      End If
      CODICE = q.rowset.fields("CODICE").value

      Dim url As string 
   url="https://www.mapquestapi.com/geocoding/v1/address?key=" & key & "&street="+escape(via)+"&city="+escape(citta)+iif(not empty(cap),"&postalCode="+cap,"")+"&country="+escape(nazione)

      Dim webClient As New System.Net.WebClient
      Dim json As String = webClient.DownloadString(Url)
      Dim obj As Newtonsoft.Json.linq.JObject = Newtonsoft.Json.linq.JObject.Parse(json)
      if obj.selecttoken("results").count>0 then
         dim lat as double=obj.SelectToken("results[0].locations[0].latLng.lat")
         dim lng as double=obj.SelectToken("results[0].locations[0].latLng.lng")

         Dim par5 As AssocArray = New AssocArray
         par5("CODICE")=CODICE
         dim msg5 as string
         dim SQL5 as string
         dim q5 as qwtable
         SQL5="select * from clifor where CODICE=:CODICE"
         q5=OpenTable(db,SQL5,par5,false,true,msg5)
         if empty(msg5) then
            If not q5.rowset.endofset
               q5.replace("GPS_LAT", lat)
               q5.replace("GPS_LONG", lng)
               q5.saverecord()
            end if  
            CloseTable(q5)
         else
            'Errore
            writelog("Errore: "+msg5)
         end if
      end if

      q.rowset.next()		   
   next
end if
q.active = false
q.dispose()