FXT_TravelDistance_FTD.txt

'--DESCRIPTION---------------------------------------------------------------------------------------
                ' This Extender business rule can be used to calculate travel dinstances and time between adresses/ places
                ' It is used to populate maps in dashboard tables, already discoverd distances should be stored in a table to reduce API costs
                ' Make sure --Imports System.Net and others below -- are added on the top of your code
                '----------------------------------------------------------------------------------------------------

                '--VARIABLES-----------------------------------------------------------------------------------------
                Dim wfUnitPK As WorkflowUnitPk = BRApi.Workflow.General.GetWorkflowUnitPk(si)
                Dim sYear As String = TimeDimHelper.GetYearFromId(wfUnitPK.TimeKey).ToString
                Dim sMonth As String = TimeDimHelper.GetSubComponentsFromId(wfUnitPK.TimeKey).Month.ToString
                Dim sTime As String = sYear & "M" & sMonth
                Dim iTimeId As Integer = BRApi.Finance.Time.GetIdFromName(si, sTime)
                Dim iScenarioTypeID As Integer = BRApi.Finance.Scenario.GetScenarioType(si, brapi.Finance.Members.GetMemberId(si, dimtype.Scenario.Id, "Plan")).Id
                Dim sDestination As String = Nothing
                Dim sOrigin As String = Nothing
                Dim sAPIKey As String = "<YOUR API KEY GOES HERE>"
                Dim sSQL As String = Nothing
                '----------------------------------------------------------------------------------------------------

                '--FIRST RUN FOR DISTANCEMATRIX----------------------------------------------------------------------

                'Create SQL table if it doesn't exist yet
                Using dbConnApp As DBConnInfo = BRAPi.Database.CreateApplicationDbConnInfo(si)

                    'Create the aGoogleData Table if it doesn't exist
                    sSQL = "IF NOT EXISTS (SELECT 1 FROM information_Schema.tables WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = 'aGoogleData')
                            --DROP TABLE [dbo].[aGoogleData] --Only use for recreation of table

                            CREATE TABLE [aGoogleData] (
                                [OriginDestinationKey] [varChar] (250) Not Null,
                                [Employee] [varChar] (100) Not Null,
                                [Origin] [varChar] (150) Not Null,
                                [Destination] [varChar] (150) Not Null,
                                [iDistance] integer,
                                [Distance] [VarChar] (50),
                                [iTravelTime] integer,
                                [TravelTime] [VarChar] (50),
                                CONSTRAINT PK_aGoogleData PRIMARY KEY CLUSTERED
                                    (
                                    OriginDestinationKey ASC,
                                    Employee ASC
                                    )
                                ) ON [PRIMARY]"
                    'Execute the database creation script
                    BRAPi.Database.ExecuteActionQuery(dbConnApp, sSQL.ToString, False, True)

                    'Retrieve existing table from OneStream database
                    sSQL = "SELECT OriginDestinationKey
                            , Employee
                            , Origin
                            , Destination
                            , iDistance
                            , Distance
                            , iTravelTime
                            , TravelTime
                            FROM aGoogleData"

                    Dim dt As DataTable = BRApi.Database.ExecuteSqlUsingReader(dbConnApp, sSQL, False)

                    'Create a list for all employees that have an address in Text1
                    Dim lstEmployees As List(Of member) = BRApi.Finance.Members.GetBaseMembers(si, brapi.Finance.Dim.GetDimPk(si, "Employees"),brapi.Finance.Members.GetMemberId(si, dimtype.UD4.Id, "Employees"))
                    Dim lstEmployeesFiltered As New List(Of String)
                    'Remove members that don't have a Text1 populated
                    For Each mbrEmployee In lstEmployees
                        Dim sText1 As String = BRApi.Finance.UD.Text(si, dimtype.ud4.Id, mbrEmployee.MemberId, 1, iScenarioTypeID, iTimeId)
                        If sText1 <> ""
                            'Text1 found, add to filtered list
                            lstEmployeesFiltered.Add(sText1 & "|" & mbrEmployee.name)

                        End If 'sText1 <> ""
                    Next 'mbrEmployee

                    'Create a list for all locaitons that have an address in Text 1
                    Dim lstLocations As List(Of member) = BRApi.Finance.Members.GetBaseMembers(si, brapi.Finance.Dim.GetDimPk(si, "Locations"), brapi.Finance.Members.GetMemberId(si, dimtype.UD1.Id, "Locations"))
                    Dim lstLocationsFiltered As New List(Of String)
                    'Remove members that don't have a Text1 populated
                    For Each mbrLocation In lstLocations
                        Dim sText1 As String = BRApi.Finance.UD.Text(si, dimtype.ud1.Id, mbrLocation.MemberId, 1, iScenarioTypeID, iTimeId)
                        If sText1 <> ""
                            'Text1 found, add to filtered list
                            lstLocationsFiltered.Add(sText1)
                        End If 'sText1 <> ""
                    Next 'mbrEmployee

                    'Generate request for each combination
                    For Each sLocation In lstLocationsFiltered 'loop over locations
                        For Each sEmployee In lstEmployeesFiltered 'loop over employees
                            'Update parameters
                            sDestination = sLocation
                            sOrigin = sEmployee.Substring(0,sEmployee.IndexOf("|"))

                            'Validate if OrignDestinationKey already exists
                            Dim drFound () As DataRow
                            drFound = dt.select("OriginDestinationKey='" & sOrigin & "_TO_" & sDestination & "' AND Employee='" & sEmployee.Substring(sEmployee.IndexOf("|")+1) & "'")


                            'If found return value, else retrieve from Google and add to DT
                            If drFound.Length > 0
                                brapi.ErrorLog.LogMessage(si, "DR Found for: " & sOrigin & "_TO_" & sDestination & vbNewLine & drFound.ToString)
                            Else
                                'REST API request
                                Dim wRequest As WebRequest = WebRequest.Create("https://maps.googleapis.com/maps/api/distancematrix/json?destinations=" & sDestination & "&origins=" &sOrigin & "&units=metric&key=" & sApiKey)
                                Dim request As HttpWebRequest = DirectCast(wRequest, HttpWebRequest)
                                    request.ContentType = "application/json"
                                    request.Method = "GET"
                                Dim sJson As String = String.Empty

                                'Make the REST api call
                                Using httpResponse As HttpWebResponse = request.GetResponse()
                                    Using respStream As Stream = httpResponse.GetResponseStream()
                                        Using streamReader As New StreamReader(respStream)
                                            Do While streamReader.Peek() >= 0
                                                sJson = streamReader.ReadToEnd
                                            Loop 'While streamReader.Peek() >= 0
                                        End Using 'streamReader
                                    End Using 'respStream
                                End Using 'httpResponse



                                'Parse sJson
                                Dim objResponse As distanceMatrixResponse = JsonConvert.deserializeObject(Of distanceMatrixResponse)(sJson.Replace("[","").Replace("]",""))


                                If objResponse.status = "OK" AndAlso objResponse.rows.elements.status = "OK"
                                    'Insert into dataTable
                                    Dim drNew As datarow = dt.NewRow
                                    drNew("OriginDestinationKey") = sOrigin & "_TO_" & sDestination
                                    drNew("Employee") = sEmployee.Substring(sEmployee.IndexOf("|")+1)
                                    drNew("Origin") = sOrigin
                                    drNew("Destination") = sDestination
                                    drNew("iDistance") = objResponse.rows.elements.distance.value
                                    drNew("Distance") = objResponse.rows.elements.distance.text
                                    drNew("iTravelTime") = objResponse.rows.elements.duration.value
                                    drNew("Traveltime") = objResponse.rows.elements.duration.text
                                    'Add row to datatable
                                    dt.Rows.Add(drNew)
                                Else
                                    brapi.ErrorLog.LogMessage(si, "Error retrieving Google data for " & sOrigin & "_TO_" & sDestination & vbNewLine & "Overall: " & objResponse.status & vbNewLine & "Row: " & objResponse.rows.elements.status)
                                End If 'objResponse.status = "OK"
                            End If 'drFound.Length > 0
                        Next 'sEmployee
                    Next 'sLocation

                    'Write datatable back to database (if updated)
                    If dt.Rows.Count > 0
                        'Clear data from database
                        sSQL = "DELETE FROM aGoogleData"

                        'Execute the database creation script
                        BRAPi.Database.ExecuteActionQuery(dbConnApp, sSQL.ToString, False, True)

                        'Write datatable back into SQL
                        brapi.Database.SaveCustomDataTable(si, "Application", "aGoogleData", dt, True)
                    End If 'dt.Rows.Count > 0

                '--SECOND RUN FOR GEOCODE----------------------------------------------------------------------------

                    'Create the aGoogleDataLatLong Table if it doesn't exist
                    sSQL = "IF NOT EXISTS (SELECT 1 FROM information_Schema.tables WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = 'aGoogleDataLatLong')
                            --DROP TABLE [dbo].[aGoogleData] --Only use for recreation of table

                            CREATE TABLE [aGoogleDataLatLong] (
                                [Location] [varChar] (150) Not Null,
                                [Lat] [varChar] (50) Not Null,
                                [Long] [varChar] (50) Not Null,
                                CONSTRAINT PK_aGoogleDataLatLong PRIMARY KEY CLUSTERED
                                    (
                                    Location ASC
                                    )
                                ) ON [PRIMARY]"
                    'Execute the database creation script
                    BRAPi.Database.ExecuteActionQuery(dbConnApp, sSQL.ToString, False, True)

                    'Retrieve existing table from OneStream database
                    sSQL = "SELECT Location
                            , Lat
                            , Long
                            FROM aGoogleDataLatLong"

                    Dim dtLL As DataTable = BRApi.Database.ExecuteSqlUsingReader(dbConnApp, sSQL, False)

                    'Generate request for each Location (both Locations and Employees)
                    For Each sLocation In lstLocationsFiltered 'loop over locations
                        'Validate if Location already exists
                        Dim drFound () As DataRow
                        drFound = dtLL.select("Location='" & sLocation & "'")


                        'If found return value, else retrieve from Google and add to DT
                        If drFound.Length > 0
                            brapi.ErrorLog.LogMessage(si, "DR Found for: " & sLocation)
                        Else
                            'REST API request
                            Dim wRequest As WebRequest = WebRequest.Create("https://maps.googleapis.com/maps/api/geocode/json?address=" & sLocation & "&key=" & sApiKey)
                            Dim request As HttpWebRequest = DirectCast(wRequest, HttpWebRequest)
                                request.ContentType = "application/json"
                                request.Method = "GET"
                            Dim sJson As String = String.Empty

                            'Make the REST api call
                            Using httpResponse As HttpWebResponse = request.GetResponse()
                                Using respStream As Stream = httpResponse.GetResponseStream()
                                    Using streamReader As New StreamReader(respStream)
                                        Do While streamReader.Peek() >= 0
                                            sJson = streamReader.ReadToEnd
                                        Loop 'While streamReader.Peek() >= 0
                                    End Using 'streamReader
                                End Using 'respStream
                            End Using 'httpResponse



                            'Parse sJson
                            Dim objResponse As GoogleGeoCodeResponse = JsonConvert.deserializeObject(Of GoogleGeoCodeResponse)(sJson)


                            If objResponse.status = "OK"
                                'Insert into dataTable
                                Dim drNew As datarow = dtLL.NewRow
                                drNew("Location") = sLocation
                                drNew("Lat") = objResponse.results(0).geometry.location.lat
                                drNew("Long") = objResponse.results(0).geometry.location.lng
                                dtLL.Rows.Add(drNew)
                            Else
                                brapi.ErrorLog.LogMessage(si, "Error retrieving Google data for " & sLocation & vbNewLine & "Overall: " & objResponse.status)
                            End If 'objResponse.status = "OK"
                        End If 'drFound.Length > 0
                    Next 'sLocation


                    For Each sEmployee In lstEmployeesFiltered 'loop over employees
                        'Validate if Location already exists
                        Dim drFound () As DataRow
                        drFound = dtLL.select("Location='" & sEmployee.Substring(0,sEmployee.IndexOf("|")) & "'")


                        'If found return value, else retrieve from Google and add to DT
                        If drFound.Length > 0
                            brapi.ErrorLog.LogMessage(si, "DR Found for: " & sEmployee.Substring(0,sEmployee.IndexOf("|")))
                        Else
                            'REST API request
                            Dim wRequest As WebRequest = WebRequest.Create("https://maps.googleapis.com/maps/api/geocode/json?address=" & sEmployee.Substring(0,sEmployee.IndexOf("|")) & "&key=" & sApiKey)
                            Dim request As HttpWebRequest = DirectCast(wRequest, HttpWebRequest)
                                request.ContentType = "application/json"
                                request.Method = "GET"
                            Dim sJson As String = String.Empty

                            'Make the REST api call
                            Using httpResponse As HttpWebResponse = request.GetResponse()
                                Using respStream As Stream = httpResponse.GetResponseStream()
                                    Using streamReader As New StreamReader(respStream)
                                        Do While streamReader.Peek() >= 0
                                            sJson = streamReader.ReadToEnd
                                        Loop 'While streamReader.Peek() >= 0
                                    End Using 'streamReader
                                End Using 'respStream
                            End Using 'httpResponse



                            'Parse sJson
                            Dim objResponse As GoogleGeoCodeResponse = JsonConvert.deserializeObject(Of GoogleGeoCodeResponse)(sJson)

                            If objResponse.status = "OK"
                                'Insert into dataTable
                                Dim drNew As datarow = dtLL.NewRow
                                drNew("Location") = sEmployee.Substring(0,sEmployee.IndexOf("|"))
                                drNew("Lat") = objResponse.results(0).geometry.location.lat
                                drNew("Long") = objResponse.results(0).geometry.location.lng
                                dtLL.Rows.Add(drNew)
                            Else
                                brapi.ErrorLog.LogMessage(si, "Error retrieving Google data for " & sEmployee.Substring(0,sEmployee.IndexOf("|")) & vbNewLine & "Overall: " & objResponse.status)
                            End If 'objResponse.status = "OK"
                        End If 'drFound.Length > 0
                    Next 'sEMployee

                    'Write datatable back to database (if updated)
                    If dtLL.Rows.Count > 0
                        'Clear data from database
                        sSQL = "DELETE FROM aGoogleDataLatLong"

                        'Execute the database creation script
                        BRAPi.Database.ExecuteActionQuery(dbConnApp, sSQL.ToString, False, True)

                        'Write datatable back into SQL
                        brapi.Database.SaveCustomDataTable(si, "Application", "aGoogleDataLatLong", dtLL, True)
                    End If 'dt.Rows.Count > 0
                End Using 'dbConnApp

                Return Nothing
            Catch ex As Exception
                Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
            End Try
        End Function
    End Class

    Public Class distanceMatrixResponse

        'JsonProperty(PropertyName = "origin_addresses")
        Public Property destination_addresses() As String
        Public Property origin_addresses() As String
        Public Property rows() As row
        Public Property status As String

        Public Class row
            Public Property elements() As element
        End Class

        Public Class element
            Public Property distance As distance
            Public Property duration As duration
            Public Property status As String
        End Class

        Public Class distance
            Public Property text As String
            Public Property value As Integer
        End Class

        Public Class duration
            Public Property text As String
            Public Property value As Integer
        End Class

    End Class 'distanceMatrixResponse

    Public Class GooglCitiesResult
        Public Property city As String
        Public Property country As String
        Public Property country_code As String
    End Class

    Public Class GoogleGeoCodeResponse
        Public Property status As String
        Public Property results As results()
    End Class

    Public Class results
        Public Property formatted_address As String
        Public Property geometry As geometry
        Public Property types As String()
        Public Property address_components As address_component()
    End Class

    Public Class geometry
        Public Property location_type As String
        Public Property location As location
    End Class

    Public Class location
        Public Property lat As String
        Public Property lng As String
    End Class

    Public Class address_component
        Public Property long_name As String
        Public Property short_name As String
        Public Property types As String()
    End Class
End Namespace