Dim con As New oledb.oledbconnection("PROVIDER=Microsoft.jet.oledb.4.0;Data Source = C:\Program Files (x86)\HomeSeer HS3\scripts\sample.mdb")
Const sRefMapFileName = "HS2_2_HS3_ref_map.ini"

Sub Main(parm as object)
dim sql as string
    Dim Cmd As OleDb.OleDbCommand
    Dim Reader As OleDb.OleDbDataReader
    Dim lEventRef As Long
    Dim sHS2EvRef As String

   Try
	hs.writelog("HS2->HS3","# Start Importing Events #")

	con.open()

	sql = "Select * from Events"
	Cmd = New OleDb.OleDbCommand(sql, Con)
	Reader = Cmd.ExecuteReader() 

        '	Reader.Read() 
        Do While Reader.Read()

            ' Delete to replace
            hs.deleteevent(Reader.Item("Name").ToString)

            sHS2EvRef = Reader.Item("EvRef").ToString
            lEventRef = hs.GetEventRefByNameAndGroup(Reader.Item("Name").ToString, Reader.Item("group").ToString)
            If lEventRef = -1 Then  ' Not found
                hs.writelog("HS2->HS3", "- New Event: " & Reader.Item("Name").ToString)
                lEventRef = hs.NewEventGetRef(Reader.Item("Name").ToString, Reader.Item("group").ToString, "Imported From HS2")
            Else
                hs.writelog("HS2->HS3 Error", "Event Exists: " & Reader.Item("Name").ToString & " lEventRef=" & lEventRef)
            End If

            create_Actions(lEventRef, reader)

            hs.SaveINISetting("Event Ref HS2 to HS3", sHS2EvRef, lEventRef, sRefMapFileName)
            hs.SaveINISetting("Event Ref HS3 to HS2", lEventRef, sHS2EvRef, sRefMapFileName)
        Loop

        hs.writelog("HS2->HS3", "# Finish Importing Devices #")

    Catch ex As Exception
        hs.WriteLog("HS2->HS3 Error", "Exception in script: " & ex.Message)
    End Try

   con.close
End Sub


Sub create_Actions(ByVal lEventRef As Long, ByVal EventReader As OleDb.OleDbDataReader)
    Dim s As String
    Dim sql As String
    Dim Cmd As OleDb.OleDbCommand
    Dim Reader As OleDb.OleDbDataReader
    Dim sHS2Dev As String
    Dim sHS3Dev As String
    Dim sHS3DevName As String
    Dim DevHS3 As Scheduler.Classes.DeviceClass
    Dim objCAPIControl As CAPIControl
    Dim Requiredsetpoint As Double = 5
    Dim sArr() As String


    sql = "Select * from EventActions WHERE action_type=1 AND evref = " & EventReader.Item("evref").ToString
    Cmd = New OleDb.OleDbCommand(sql, Con)
    Reader = Cmd.ExecuteReader()

    Do While Reader.Read()
        sArr = split(Reader.Item("device_refs").ToString, ",")
        For Each sHS2Dev In sArr
            hs.writelog("HS2->HS3", "New Action: " & Reader.Item("Action_type").ToString & " DimVal:" & Reader.Item("device_dimval").tostring)
            sHS3Dev = hs.GetINISetting("Device Ref HS2 to HS3", sHS2Dev, "*", sRefMapFileName)
            If sHS3Dev = "*" Then
                hs.WriteLog("HS2->HS3 Error", "HS2 Dev not found in Lookup file: " & sHS2Dev)
            Else
                DevHS3 = hs.GetDeviceByRef(CLng(sHS3Dev))
                If DevHS3 Is Nothing Then
                    hs.WriteLog("HS2->HS3 Error", "HS3 Dev found in Lookup file, not found in HS3: " & sHS3Dev)
                Else
                    sHS3DevName = DevHS3.name(hs).tostring
                    Select Case Reader.Item("device_dimval")
                        Case 0
                            hs.writelog("HS2->HS3", "New Action: Turn OFF HS3 Dev: " & sHS3DevName)
                            objCAPIControl = hs.CAPIGetSingleControlByUse(CLng(sHS3Dev), ePairControlUse._Off)
                            s = hs.AddDeviceActionToEvent(lEventRef, objCAPIControl)
                        Case 100
                            hs.writelog("HS2->HS3", "New Action: Turn ON HS3 Dev: " & sHS3DevName)
                            objCAPIControl = hs.CAPIGetSingleControlByUse(CLng(sHS3Dev), ePairControlUse._On)
                            s = hs.AddDeviceActionToEvent(lEventRef, objCAPIControl)
                        Case Else
                            hs.writelog("HS2->HS3", "New Action: Dim HS3 Dev: " & sHS3DevName & " TO: " & Reader.Item("device_dimval").tostring)
                            objCAPIControl = hs.CAPIGetSingleControlByUse(CLng(sHS3Dev), ePairControlUse._Dim)
                            objCAPIControl.ControlValue = CLng(Reader.Item("device_dimval"))
                            'objCAPIControl.Controlstring = "Dim " & Reader.Item("device_dimval").tostring & "%"
                            s = hs.AddDeviceActionToEvent(lEventRef, objCAPIControl)
                    End Select
                    If s <> "" Then
                        hs.writelog("HS2->HS3 Error", "AddDeviceAction ret val:" & s)
                    End If
                End If
            End If
        Next
    Loop
End Sub