Dim con as new oledb.oledbconnection("PROVIDER=Microsoft.jet.oledb.4.0;Data Source = C:\Program Files (x86)\HomeSeer HS3\scripts\sample.mdb")
Dim Cmd As OleDb.OleDbCommand
Dim Reader As OleDb.OleDbDataReader

Const sRefMapFileName = "HS2_2_HS3_ref_map.ini"

Sub Main(parm as object)
Dim dv As Scheduler.Classes.DeviceClass
Dim EN As Scheduler.Classes.clsDeviceEnumeration

    Try
        con.open()

        EN = hs.GetDeviceEnumerator

        dv = EN.GetNext
        Do While Not EN.finished

            Select Case dv.Interface(Nothing)
                '               Case "RFXCOM"
                '               RFXCOM_INPUT(dv)
                Case "Z-Wave"
                    backup_Device_name(dv)
                    Z_Wave(dv)
                Case Else
                    If Ucase(dv.Location(Nothing) = "IMPORT FROM HS2") Then
                        manual_HC_dc_import(dv)
                    Else
                        hs.writelog("HS2->HS3 Error", "Unhandled Interface: " & dv.Interface(Nothing) & " For Device: " & dv.Name(Nothing))
                    End If
            End Select
            dv = EN.GetNext
        Loop

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

SUB manual_HC_dc_import(byval DV as Scheduler.Classes.DeviceClass)
dim sql as string
Dim hc as string = left(dv.UserNote(nothing),1)
Dim dC as string = str(val(mid(dv.UserNote(nothing),2)))

	sql = "Select * from Devices where hc = '" & hc & "' AND dc=" & DC
	Cmd = New OleDb.OleDbCommand(sql, Con)
	Reader = Cmd.ExecuteReader() 

	if Reader.Read() then
		Update_device_and_map_refs(reader,dv)
	else
                hs.writelog("HS2->HS3 Error","Cant find Device for Manual update with HC='" & hc & "' AND DC='" & dc & "'")
	end if
End Sub

SUB RFXCOM_INPUT(byval DV as Scheduler.Classes.DeviceClass)
dim sql as string

	sql = "Select * from Devices where iomisc = '" & Mid(dv.address(nothing),3) & "'"
	Cmd = New OleDb.OleDbCommand(sql, Con)
	Reader = Cmd.ExecuteReader() 

	if Reader.Read() then
		Update_device_and_map_refs(reader,dv)
	end if
End Sub

SUB Z_Wave(byref DV as Scheduler.Classes.DeviceClass)
dim sql as string
Dim sArr() as string
Dim iChildInst as integer

	' Get Node nr
	sArr=split(dv.address(nothing),"-") 

	' Check Device type
	select case dv.Device_type_String(nothing)
	case "Z-Wave Switch Binary Root Device"
		sql = "Select * from Devices where dev_type_string = 'Z-Wave Switch Binary' AND zwave_nodeid = " & sArr(1) & " AND ZWave_CommandClass_Instance = 0"
	case "Z-Wave Switch Multilevel"
		sql = "Select * from Devices where dev_type_string = 'Z-Wave Switch Multilevel' AND zwave_nodeid = " & sArr(1) & " AND ZWave_CommandClass_Instance = 0"
	case "Z-Wave Switch Binary"
		' Get Child Inst from Name or UserNote
		If instr(dv.name(nothing),"Child Inst ")=0 AND instr(dv.UserNote(nothing),"Child Inst ")=0 Then
			sql="" ' skip	
	                hs.writelog("HS2->HS3 Error","Cant Get Child Instance from Name or UserNote: '" & dv.name(nothing)  & "' / '" & dv.UserNote(nothing) & "' IS The Device already renamed and the name not Backed up to UserNote?")
		else
			if instr(dv.name(nothing),"Child Inst ") then
				iChildInst = right(dv.name(nothing),1)
			else
				iChildInst = right(dv.UserNote(nothing),1)
			end if

			' Convert to HS2 inst
			if iChildInst = 1 then
				iChildInst=-1
			end if
			' Find Root Ref
			sql = "Select * from Devices where dev_type_string = 'Z-Wave Root Device' AND zwave_nodeid = " & sArr(1) & " AND ZWave_CommandClass_Instance = 0"
			Cmd = New OleDb.OleDbCommand(sql, Con)
			Reader = Cmd.ExecuteReader() 
			if Reader.Read() then
				' Use the roots Ref as AssociatedDevices 
				sql = "Select * from Devices where dev_type_string = 'Z-Wave Switch Binary' AND AssociatedDevices = '" & Reader.Item("ref").ToString & "' AND ZWave_CommandClass_Instance = " & cstr(iChildInst)
			else
		                hs.writelog("HS2->HS3 Error","Cant find Root device for : " & sql)
				sql="" ' Something wrong, skip	
			end if
		end if
	case else
		sql="" ' skip	
                hs.writelog("HS2->HS3 Error","Unhandled Z-Wave Device Type: " & dv.Device_type_String(nothing) & " For Device: " & dv.Name(nothing))
	end select


	if sql<>"" Then
		Cmd = New OleDb.OleDbCommand(sql, Con)
		Reader = Cmd.ExecuteReader() 

		if Reader.Read() then
			Update_device_and_map_refs(reader,dv)
		end if
	end if
End Sub

Sub backup_Device_name(byref DV as Scheduler.Classes.DeviceClass)
	
	if dv.UserNote(nothing) = "" then ' only once
		dv.UserNote(nothing) = dv.name(nothing)
	end if
End Sub


Sub Update_device_and_map_refs(byref Reader As OleDb.OleDbDataReader, byref DV as Scheduler.Classes.DeviceClass)
Dim s as string

	hs.writelog("HS2->HS3","# Updating Device: " & dv.Name(nothing))

	' Name
	s=Reader.Item("Name").ToString
        hs.writelog("HS2->HS3","Setting name to:" & S)
	dv.Name(nothing) = s

	' Location
	s=Reader.Item("Location").ToString
      	hs.writelog("HS2->HS3","Setting Location to:" & S)
	dv.Location(nothing) = s

	' Location2
	s=Reader.Item("Location2").ToString
      	hs.writelog("HS2->HS3","Setting Location2 to:" & S)
    dv.Location2(Nothing) = s

	' HC & DC -> Code
'	s=Reader.Item("hc").ToString & Format(Reader.Item("dc").ToString,"00")
'       hs.writelog("HS2->HS3","Setting Code to:" & S)
'	dv.Code(nothing) = s

	' Ref mapping
	s=Reader.Item("ref").ToString
	hs.SaveINISetting("Ref HS2 to HS3", s, dv.Ref(nothing), sRefMapFileName)
	hs.SaveINISetting("Ref HS3 to HS2", dv.Ref(nothing), s, sRefMapFileName)
End Sub