2016-10-24

Software List (Enhanced Version) Load into DB

'On Error Resume Next

SourcePath = "D:\Info\Data"

GapString = "=====Gap====="
InfoString = "=====Info====="

DebugMode = 0

SQLServer="127.0.0.1"
SQLUser="Info"
SQLPassword="password"
SQLDB="Info"
SQLConnectionTimeout = 600

DonnotKeepOSLastBootUpTime = 1


DateTimeString = date & " " & time
DateTimeString = Year(DateTimeString) & "/" & Month(DateTimeString) & "/" &  Day(DateTimeString) & " "& Hour(DateTimeString) & ":" &  Minute(DateTimeString) & ":" &  Second(DateTimeString)

Dim SQLConnOpen
Dim SQLConnString
Dim SQLConnection
Dim SQLString
Dim SQLRecordSet
Dim InfoDataType
SQLConnOpen = 0

' ----- Set Object Class & Variables
Set fso = CreateObject("Scripting.FileSystemObject")
Set WshShell = CreateObject( "WScript.Shell" )

' Kill Runnung Process
ProcessId = CurrProcessId
set service = GetObject ("winmgmts:")
for each Process in Service.InstancesOf ("Win32_Process")
If LCase(Process.Name) = LCase(WScript.ScriptName) then
if Process.ProcessId <> ProcessId then
Taskkill = WshShell.Run("taskkill /F /T /PID " & Process.ProcessId , , False)
end if
End if
next
' Kill Runnung Process


Set SQLConnection = CreateObject("ADODB.Connection")
Set SQLRecordSet = CreateObject("ADODB.RecordSet")

' Check Source Path Exists
If not fso.FolderExists(SourcePath) Then
'wscript.echo "Cannot Reach Target Path"
wscript.quit
end if

InfoDateTime = ""
InfoLastLogonAccount = ""
InfoComputerSID = ""

' ----- MB
Dim MBIndex
Dim MBManufacturer()
Dim MBProduct()
Dim MBSerialNumber()
Dim MBVersion()
MBIndex = -1

' ----- CPU
Dim CPUIndex
Dim CPUAddressWidth()
Dim CPUArchitecture()
Dim CPUCaption()
Dim CPUDataWidth()
Dim CPUDeviceID()
Dim CPUExtClock()
Dim CPUFamily()
Dim CPULevel()
Dim CPUManufacturer()
Dim CPUMaxClockSpeed()
Dim CPUName()
Dim CPUNumberOfCores()
Dim CPUNumberOfLogicalProcessors()
Dim CPUProcessorId()
Dim CPUSocketDesignation()
CPUIndex = -1

' ----- MEM
Dim MEMIndex
Dim MEMBankLabel()
Dim MEMCapacity()
Dim MEMDeviceLocator()
Dim MEMManufacturer()
Dim MEMMemoryType()
Dim MEMPartNumber()
Dim MEMSerialNumber()
Dim MEMSpeed()
Dim MEMTag()
MEMIndex = -1

' ----- NIC
Dim NICIndex
Dim NICCaption()
Dim NICDHCPEnabled()
Dim NICDNSHostName()
Dim NICIPAddress()
Dim NICMACAddress()
Dim NICServiceName()
NICIndex = -1
Dim AllInfoDNSHostName

' ----- DiskIndex
Dim DiskIndex
Dim DiskCaption()
Dim DiskFirmwareRevision()
Dim DiskInterfaceType()
Dim DiskMediaType()
Dim DiskModel()
Dim DiskSerialNumber()
Dim DiskSignature()
Dim DiskSize()
DiskIndex = -1

' ----- CDROMIndex
Dim CDROMIndex
Dim CDROMCaption()
Dim CDROMMediaType()
Dim CDROMMfrAssignedRevisionLevel()
Dim CDROMName()
CDROMIndex = -1

' ----- OS
Dim OSIndex
Dim OSBuildNumber()
Dim OSCaption()
Dim OSCodeSet()
Dim OSCountryCode()
Dim OSCSDVersion()
Dim OSCSName()
Dim OSCurrentTimeZone()
Dim OSInstallDate()
Dim OSLastBootUpTime()
Dim OSLocale()
Dim OSManufacturer()
Dim OSOperatingSystemSKU()
Dim OSOSArchitecture()
Dim OSOSLanguage()
Dim OSOSProductSuite()
Dim OSOSType()
Dim OSOtherTypeDescription()
Dim OSProductKey()
Dim OSProductType()
Dim OSRegisteredUser()
Dim OSSerialNumber()
Dim OSVersion()
OSIndex = -1

' ----- Software List
Dim SoftwareListIndex
Dim SoftwareDisplayName()
Dim SoftwareDisplayVersion()
Dim SoftwarePublisher()
Dim SoftwareInstallDate()
SoftwareListIndex = -1

' ----- Office List
Dim OfficeListIndex
Dim OfficeVersion()
Dim OfficeProductName()
Dim OfficeProductID()
Dim OfficeType()
Dim OfficeProductKey()
Dim OfficeNote()
OfficeListIndex = -1
Dim oReg
Dim osType
Dim aOffID(4,1)
Dim aOffIDX

' ----- GroupMember List
Dim GroupMemberListIndex
Dim GroupMemberAccount()
Dim GroupMemberType()
GroupMemberListIndex = -1

' ----- SQL

Dim AllInfoID
Dim InsertAllInfoIndex
Dim InsertAllInfoLinkType()
Dim InsertAllInfoLinkValue()
InsertAllInfoIndex = -1

Dim BaseRecordIndex
Dim BaseRecordType()
Dim BaseRecordTypeSerial()
BaseRecordIndex = -1

Dim TempRecordIndex
Dim TempRecordType()
Dim TempRecordTypeSerial()
TempRecordIndex = -1

Dim FinalInsertRecordIndex
Dim FinalInsertType()
Dim FinalInsertTypeSerial()
FinalInsertRecordIndex = -1

' Analysis Start

' ----- List Files
Dim InfoFile
InfoFile = ""

Set SourceFolder = fso.GetFolder(SourcePath)
Set SourceFiles = SourceFolder.Files
For Each SourceFile in SourceFiles
SkipToNext = 1
If (InStr(SourceFile.Name, ".") > 0) Then
If (LCase(Mid(SourceFile.Name, InStrRev(SourceFile.Name, "."))) = ".dbd") Then
InfoFile = SourceFile.Path
SET CheckFile = fso.GetFile(InfoFile)
if (abs(DateDiff("s", DateTimeString, CDATE(CheckFile.DateLastModified))) > 60) then
SkipToNext = 0
end if
end if
End If

if (SkipToNext = 0) then
ProcessDone = ReadInfoFileWriteIntoSQL(InfoFile)
WScript.Sleep 1000
if (DebugMode = 0) then
RunAgain = WshShell.Run(WScript.ScriptFullName, , False)
end if
wscript.quit
end if


Next

if (DebugMode = 0) then
'HouseKeeping()
WScript.Sleep 30000
RunAgain = WshShell.Run(WScript.ScriptFullName, , False)
end if
wscript.quit

Function ReadInfoFileWriteIntoSQL(InfoFile)

RetuanValue = 1

if (InfoFile = "") then
'wscript.echo "No File to Process"
RetuanValue = 0
else
Set InfoFileStream = fso.OpenTextFile(InfoFile, 1, True, -1)
If InfoFileStream.AtEndOfStream Then
InfoFileStream.Close
if (DebugMode = 0) then
DelInfoFile = fso.DeleteFile(InfoFile, True)
end if
RetuanValue = 0
else
' ----- Read and Split Info into Array
InfoDetails = InfoFileStream.ReadAll
InfoFileStream.Close
if (FindStr(InfoDetails,GapString) = 0) then
if (DebugMode = 0) then
DelInfoFile = fso.DeleteFile(InfoFile, True)
end if
RetuanValue = 0
else
InfoArray = Split(InfoDetails,GapString & vbcrlf)
ReadFileDataIntoArray(InfoArray)

if (AllInfoDNSHostName = "") Then
'Wscript.ehco "No HostName"
RetuanValue = 0
else
' ----- Write to SQL
WriteInfoToSQL()
if (DebugMode = 0) then
DelInfoFile = fso.DeleteFile(InfoFile, True)
end if
End if
end if
end if
end if
ReadInfoFileWriteIntoSQL = RetuanValue
End Function


Function HouseKeeping()

SQLString = ""
SQLString = SQLString & "DELETE FROM [" & SQLDB & "].[dbo].[InfoAllLink] WHERE [Serial] in ("
SQLString = SQLString & " select [Serial] from ("
SQLString = SQLString & " select Table_D.* from ("
SQLString = SQLString & " select [Record],[Type],[TypeSerial] from ("
SQLString = SQLString & " SELECT count([Serial]) as [Count],[Record],[Type],[TypeSerial]"
SQLString = SQLString & " FROM [" & SQLDB & "].[dbo].[InfoAllLink]"
SQLString = SQLString & " group by [Record],[Type],[TypeSerial]"
SQLString = SQLString & " ) as Table_A"
SQLString = SQLString & " where [Count] > 1"
SQLString = SQLString & " ) as Table_B"
SQLString = SQLString & " left join ("
SQLString = SQLString & " select [Serial],[Record],[Type],[TypeSerial] from [" & SQLDB & "].[dbo].[InfoAllLink]"
SQLString = SQLString & " ) as Table_D"
SQLString = SQLString & " on Table_B.[Record] = Table_D.[Record] and Table_B.[Type] = Table_D.[Type] and Table_B.[TypeSerial] = Table_D.[TypeSerial]"
SQLString = SQLString & " ) as Table_G"
SQLString = SQLString & " where [Serial] not in ("
SQLString = SQLString & " select Table_N.[Serial] from ("
SQLString = SQLString & " select [Record],[Type],[TypeSerial] from ("
SQLString = SQLString & " SELECT count([Serial]) as [Count],[Record],[Type],[TypeSerial]"
SQLString = SQLString & " FROM [" & SQLDB & "].[dbo].[InfoAllLink]"
SQLString = SQLString & " group by [Record],[Type],[TypeSerial]"
SQLString = SQLString & " ) as Table_L"
SQLString = SQLString & " where [Count] > 1"
SQLString = SQLString & " ) as Table_M"
SQLString = SQLString & " left join ("
SQLString = SQLString & " select MIN([Serial]) as [Serial],[Record],[Type],[TypeSerial]"
SQLString = SQLString & " from [" & SQLDB & "].[dbo].[InfoAllLink]"
SQLString = SQLString & " group by [Record],[Type],[TypeSerial]"
SQLString = SQLString & " ) as Table_N"
SQLString = SQLString & " on Table_M.[Record] = Table_N.[Record]and Table_M.[Type] = Table_N.[Type]and Table_M.[TypeSerial] = Table_N.[TypeSerial]"
SQLString = SQLString & " )"
SQLString = SQLString & ")"


if (SQLConnOpen = 0) then
Set SQLConnection = CreateObject("ADODB.Connection")
Set SQLRecordSet = CreateObject("ADODB.RecordSet")
SQLConnString = "DRIVER={SQL Server};SERVER=" & SQLServer & ";UID=" & SQLUser & ";PWD=" & SQLPassword & ";DATABASE=" & SQLDB
SQLConnection.ConnectionTimeout = SQLConnectionTimeout
SQLConnection.Open SQLConnString
SQLConnOpen = 1
End If

Set SQLRecordSet = SQLConnection.Execute(SQLString)

SQLConnection.Close

End Function




' ----- Info Array
Function ReadFileDataIntoArray(InfoArray)

for i=0 to ubound(InfoArray)

set InfoClass = nothing
InfoClass = Split(InfoArray(i),InfoString & vbcrlf)
InfoClass(0) = Replace(InfoClass(0),vbcrlf,"")

Select Case InfoClass(0)

Case "DateTime"
Set InfoClassArray = Nothing
Set ClassDataArray = Nothing
InfoClassArray = Split(InfoClass(1),vbcrlf)
InfoDateTime = Replace(DateTimeFormat(InfoClassArray(0)),vbcrlf,"")
InfoLastLogonAccount = Replace(InfoClassArray(1),vbcrlf,"")
InfoDataType = Replace(InfoClassArray(2),vbcrlf,"")
InfoComputerSID = ""
if (ubound(InfoClassArray) = 4) then
InfoComputerSID = InfoClassArray(3)
end if
Case "MB"
Set InfoClassArray = Nothing
Set ClassDataArray = Nothing
InfoClassArray = Split(InfoClass(1),vbcrlf)
InfoClassDataCheck = InfoClassArrayCheck(InfoClass(0),InfoClassArray)
If (InfoClassDataCheck <> 1) Then
'wscript.echo InfoClass(0) & " Not Correct: " & InfoClassDataCheck
wscript.quit
End If
MBIndex = CLng(InfoClassArray(0))
ReDim Preserve MBManufacturer(MBIndex)
ReDim Preserve MBProduct(MBIndex)
ReDim Preserve MBSerialNumber(MBIndex)
ReDim Preserve MBVersion(MBIndex)
MBIndex = -1
for j=2 to UBound(InfoClassArray)
If (InfoClassArray(j) <> "") Then
MBIndex = MBIndex + 1
ClassDataArray = Split(InfoClassArray(j),vbtab)
if (Ubound(ClassDataArray) <> 3) Then
'wscript.echo InfoClass(0) & " Data Count Error"
wscript.quit
End If
MBManufacturer(MBIndex) = ClassDataArray(0)
MBProduct(MBIndex) = ClassDataArray(1)
MBSerialNumber(MBIndex) = ClassDataArray(2)
MBVersion(MBIndex) = ClassDataArray(3)
End If
Next

Case "CPU"
Set InfoClassArray = Nothing
Set ClassDataArray = Nothing
InfoClassArray = Split(InfoClass(1),vbcrlf)
InfoClassDataCheck = InfoClassArrayCheck(InfoClass(0),InfoClassArray)
If (InfoClassDataCheck <> 1) Then
'wscript.echo InfoClass(0) & " Not Correct: " & InfoClassDataCheck
wscript.quit
End If
CPUIndex = CLng(InfoClassArray(0))
ReDim Preserve CPUAddressWidth(CPUIndex)
ReDim Preserve CPUArchitecture(CPUIndex)
ReDim Preserve CPUCaption(CPUIndex)
ReDim Preserve CPUDataWidth(CPUIndex)
ReDim Preserve CPUDeviceID(CPUIndex)
ReDim Preserve CPUExtClock(CPUIndex)
ReDim Preserve CPUFamily(CPUIndex)
ReDim Preserve CPULevel(CPUIndex)
ReDim Preserve CPUManufacturer(CPUIndex)
ReDim Preserve CPUMaxClockSpeed(CPUIndex)
ReDim Preserve CPUName(CPUIndex)
ReDim Preserve CPUNumberOfCores(CPUIndex)
ReDim Preserve CPUNumberOfLogicalProcessors(CPUIndex)
ReDim Preserve CPUProcessorId(CPUIndex)
ReDim Preserve CPUSocketDesignation(CPUIndex)
CPUIndex = -1
for j=2 to UBound(InfoClassArray)
If (InfoClassArray(j) <> "") Then
CPUIndex = CPUIndex + 1
ClassDataArray = Split(InfoClassArray(j),vbtab)
if (Ubound(ClassDataArray) <> 14) Then
'wscript.echo InfoClass(0) & " Data Count Error"
wscript.quit
End If
CPUAddressWidth(CPUIndex) = ClassDataArray(0)
CPUArchitecture(CPUIndex) = ClassDataArray(1)
CPUCaption(CPUIndex) = ClassDataArray(2)
CPUDataWidth(CPUIndex) = ClassDataArray(3)
CPUDeviceID(CPUIndex) = ClassDataArray(4)
CPUExtClock(CPUIndex) = ClassDataArray(5)
CPUFamily(CPUIndex) = ClassDataArray(6)
CPULevel(CPUIndex) = ClassDataArray(7)
CPUManufacturer(CPUIndex) = ClassDataArray(8)
CPUMaxClockSpeed(CPUIndex) = ClassDataArray(9)
CPUName(CPUIndex) = ClassDataArray(10)
CPUNumberOfCores(CPUIndex) = ClassDataArray(11)
CPUNumberOfLogicalProcessors(CPUIndex) = ClassDataArray(12)
CPUProcessorId(CPUIndex) = ClassDataArray(13)
CPUSocketDesignation(CPUIndex) = ClassDataArray(14)
End If
Next

Case "MEM"
Set InfoClassArray = Nothing
Set ClassDataArray = Nothing
InfoClassArray = Split(InfoClass(1),vbcrlf)
InfoClassDataCheck = InfoClassArrayCheck(InfoClass(0),InfoClassArray)
If (InfoClassDataCheck <> 1) Then
'wscript.echo InfoClass(0) & " Not Correct: " & InfoClassDataCheck
wscript.quit
End If
MEMIndex = CLng(InfoClassArray(0))
ReDim Preserve MEMBankLabel(MEMIndex)
ReDim Preserve MEMCapacity(MEMIndex)
ReDim Preserve MEMDeviceLocator(MEMIndex)
ReDim Preserve MEMManufacturer(MEMIndex)
ReDim Preserve MEMMemoryType(MEMIndex)
ReDim Preserve MEMPartNumber(MEMIndex)
ReDim Preserve MEMSerialNumber(MEMIndex)
ReDim Preserve MEMSpeed(MEMIndex)
ReDim Preserve MEMTag(MEMIndex)
MEMIndex = -1
for j=2 to UBound(InfoClassArray)
If (InfoClassArray(j) <> "") Then
MEMIndex = MEMIndex + 1
ClassDataArray = Split(InfoClassArray(j),vbtab)
if (Ubound(ClassDataArray) <> 8) Then
'wscript.echo InfoClass(0) & " Data Count Error"
wscript.quit
End If
MEMBankLabel(MEMIndex) = ClassDataArray(0)
MEMCapacity(MEMIndex) = ClassDataArray(1)
MEMDeviceLocator(MEMIndex) = ClassDataArray(2)
MEMManufacturer(MEMIndex) = ClassDataArray(3)
MEMMemoryType(MEMIndex) = ClassDataArray(4)
MEMPartNumber(MEMIndex) = ClassDataArray(5)
MEMSerialNumber(MEMIndex) = ClassDataArray(6)
MEMSpeed(MEMIndex) = ClassDataArray(7)
MEMTag(MEMIndex) = ClassDataArray(8)
End If
Next

Case "NIC"
Set InfoClassArray = Nothing
Set ClassDataArray = Nothing
InfoClassArray = Split(InfoClass(1),vbcrlf)
InfoClassDataCheck = InfoClassArrayCheck(InfoClass(0),InfoClassArray)
If (InfoClassDataCheck <> 1) Then
'wscript.echo InfoClass(0) & " Not Correct: " & InfoClassDataCheck
wscript.quit
End If
NICIndex = CLng(InfoClassArray(0))
ReDim Preserve NICCaption(NICIndex)
ReDim Preserve NICDHCPEnabled(NICIndex)
ReDim Preserve NICDNSHostName(NICIndex)
ReDim Preserve NICIPAddress(NICIndex)
ReDim Preserve NICMACAddress(NICIndex)
ReDim Preserve NICServiceName(NICIndex)
NICIndex = -1
for j=2 to UBound(InfoClassArray)
If (InfoClassArray(j) <> "") Then
NICIndex = NICIndex + 1
ClassDataArray = Split(InfoClassArray(j),vbtab)
if (Ubound(ClassDataArray) <> 5) Then
'wscript.echo InfoClass(0) & " Data Count Error"
wscript.quit
End If
NICCaption(NICIndex) = ClassDataArray(0)
NICDHCPEnabled(NICIndex) = ClassDataArray(1)
NICDNSHostName(NICIndex) = ClassDataArray(2)
If (NICDNSHostName(NICIndex) <> "") Then
AllInfoDNSHostName = NICDNSHostName(NICIndex)
End If
NICIPAddress(NICIndex) = ClassDataArray(3)
NICMACAddress(NICIndex) = ClassDataArray(4)
NICServiceName(NICIndex) = ClassDataArray(5)
End If
Next

Case "Disk"
Set InfoClassArray = Nothing
Set ClassDataArray = Nothing
InfoClassArray = Split(InfoClass(1),vbcrlf)
InfoClassDataCheck = InfoClassArrayCheck(InfoClass(0),InfoClassArray)
If (InfoClassDataCheck <> 1) Then
'wscript.echo InfoClass(0) & " Not Correct: " & InfoClassDataCheck
wscript.quit
End If
DiskIndex = CLng(InfoClassArray(0))
ReDim Preserve DiskCaption(DiskIndex)
ReDim Preserve DiskFirmwareRevision(DiskIndex)
ReDim Preserve DiskInterfaceType(DiskIndex)
ReDim Preserve DiskMediaType(DiskIndex)
ReDim Preserve DiskModel(DiskIndex)
ReDim Preserve DiskSerialNumber(DiskIndex)
ReDim Preserve DiskSignature(DiskIndex)
ReDim Preserve DiskSize(DiskIndex)
DiskIndex = -1
for j=2 to UBound(InfoClassArray)
If (InfoClassArray(j) <> "") Then
DiskIndex = DiskIndex + 1
ClassDataArray = Split(InfoClassArray(j),vbtab)

DiskCaption(DiskIndex) = ClassDataArray(0)
DiskFirmwareRevision(DiskIndex) = ClassDataArray(1)
DiskInterfaceType(DiskIndex) = ClassDataArray(2)
DiskMediaType(DiskIndex) = ClassDataArray(3)
DiskModel(DiskIndex) = ClassDataArray(4)
DiskSerialNumber(DiskIndex) = ClassDataArray(5)
DiskSignature(DiskIndex) = ClassDataArray(6)
DiskSize(DiskIndex) = ClassDataArray(7)
End If
Next

Case "CDROM"
Set InfoClassArray = Nothing
Set ClassDataArray = Nothing
InfoClassArray = Split(InfoClass(1),vbcrlf)
InfoClassDataCheck = InfoClassArrayCheck(InfoClass(0),InfoClassArray)
If (InfoClassDataCheck <> 1) Then
'wscript.echo InfoClass(0) & " Not Correct: " & InfoClassDataCheck
wscript.quit
End If
CDROMIndex = CLng(InfoClassArray(0))
ReDim Preserve CDROMCaption(CDROMIndex)
ReDim Preserve CDROMMediaType(CDROMIndex)
ReDim Preserve CDROMMfrAssignedRevisionLevel(CDROMIndex)
ReDim Preserve CDROMName(CDROMIndex)
CDROMIndex = -1
for j=2 to UBound(InfoClassArray)
If (InfoClassArray(j) <> "") Then
CDROMIndex = CDROMIndex + 1
ClassDataArray = Split(InfoClassArray(j),vbtab)
if (Ubound(ClassDataArray) <> 3) Then
'wscript.echo InfoClass(0) & " Data Count Error"
wscript.quit
End If
CDROMCaption(CDROMIndex) = ClassDataArray(0)
CDROMMediaType(CDROMIndex) = ClassDataArray(1)
CDROMMfrAssignedRevisionLevel(CDROMIndex) = ClassDataArray(2)
CDROMName(CDROMIndex) = ClassDataArray(3)
End If
Next

Case "OS"
Set InfoClassArray = Nothing
Set ClassDataArray = Nothing
InfoClassArray = Split(InfoClass(1),vbcrlf)
InfoClassDataCheck = InfoClassArrayCheck(InfoClass(0),InfoClassArray)
If (InfoClassDataCheck <> 1) Then
'wscript.echo InfoClass(0) & " Not Correct: " & InfoClassDataCheck
wscript.quit
End If
OSIndex = CLng(InfoClassArray(0))
ReDim Preserve OSBuildNumber(OSIndex)
ReDim Preserve OSCaption(OSIndex)
ReDim Preserve OSCodeSet(OSIndex)
ReDim Preserve OSCountryCode(OSIndex)
ReDim Preserve OSCSDVersion(OSIndex)
ReDim Preserve OSCSName(OSIndex)
ReDim Preserve OSCurrentTimeZone(OSIndex)
ReDim Preserve OSInstallDate(OSIndex)
ReDim Preserve OSLastBootUpTime(OSIndex)
ReDim Preserve OSLocale(OSIndex)
ReDim Preserve OSManufacturer(OSIndex)
ReDim Preserve OSOperatingSystemSKU(OSIndex)
ReDim Preserve OSOSArchitecture(OSIndex)
ReDim Preserve OSOSLanguage(OSIndex)
ReDim Preserve OSOSProductSuite(OSIndex)
ReDim Preserve OSOSType(OSIndex)
ReDim Preserve OSOtherTypeDescription(OSIndex)
ReDim Preserve OSProductKey(OSIndex)
ReDim Preserve OSProductType(OSIndex)
ReDim Preserve OSRegisteredUser(OSIndex)
ReDim Preserve OSSerialNumber(OSIndex)
ReDim Preserve OSVersion(OSIndex)
OSIndex = -1
for j=2 to UBound(InfoClassArray)
If (InfoClassArray(j) <> "") Then
OSIndex = OSIndex + 1
ClassDataArray = Split(InfoClassArray(j),vbtab)
if (Ubound(ClassDataArray) <> 21) Then
'wscript.echo InfoClass(0) & " Data Count Error"
wscript.quit
End If
OSBuildNumber(OSIndex) = ClassDataArray(0)
OSCaption(OSIndex) = ClassDataArray(1)
OSCodeSet(OSIndex) = ClassDataArray(2)
OSCountryCode(OSIndex) = ClassDataArray(3)
OSCSDVersion(OSIndex) = ClassDataArray(4)
OSCSName(OSIndex) = ClassDataArray(5)
If (AllInfoDNSHostName = "") Then
AllInfoDNSHostName = OSCSName(OSIndex)
End If
OSCurrentTimeZone(OSIndex) = ClassDataArray(6)
OSInstallDate(OSIndex) = ConvertTime(ClassDataArray(7))
if (DonnotKeepOSLastBootUpTime = 1) then
OSLastBootUpTime(OSIndex) = ""
else
OSLastBootUpTime(OSIndex) = ConvertTime(ClassDataArray(8))
end if
OSLocale(OSIndex) = ClassDataArray(9)
OSManufacturer(OSIndex) = ClassDataArray(10)
OSOperatingSystemSKU(OSIndex) = ClassDataArray(11)
OSOSArchitecture(OSIndex) = ClassDataArray(12)
OSOSLanguage(OSIndex) = ClassDataArray(13)
OSOSProductSuite(OSIndex) = ClassDataArray(14)
OSOSType(OSIndex) = ClassDataArray(15)
OSOtherTypeDescription(OSIndex) = ClassDataArray(16)
OSProductKey(OSIndex) = ClassDataArray(17)
OSProductType(OSIndex) = ClassDataArray(18)
OSRegisteredUser(OSIndex) = ClassDataArray(19)
OSSerialNumber(OSIndex) = ClassDataArray(20)
OSVersion(OSIndex) = ClassDataArray(21)
End If
Next

Case "GroupMember"
Set InfoClassArray = Nothing
Set ClassDataArray = Nothing
InfoClassArray = Split(InfoClass(1),vbcrlf)
InfoClassDataCheck = InfoClassArrayCheck(InfoClass(0),InfoClassArray)
If (InfoClassDataCheck <> 1) Then
'wscript.echo InfoClass(0) & " Not Correct: " & InfoClassDataCheck
wscript.quit
End If
GroupMemberListIndex = CLng(InfoClassArray(0))
ReDim Preserve GroupMemberType(GroupMemberListIndex)
ReDim Preserve GroupMemberAccount(GroupMemberListIndex)
GroupMemberListIndex = -1
for j=2 to UBound(InfoClassArray)
If (InfoClassArray(j) <> "") Then
GroupMemberListIndex = GroupMemberListIndex + 1
ClassDataArray = Split(InfoClassArray(j),vbtab)
if (Ubound(ClassDataArray) <> 1) Then
'wscript.echo InfoClass(0) & " Data Count Error"
wscript.quit
End If
GroupMemberType(GroupMemberListIndex) = ClassDataArray(0)
GroupMemberAccount(GroupMemberListIndex) = ClassDataArray(1)
End If
Next


Case "Software"
Set InfoClassArray = Nothing
Set ClassDataArray = Nothing
InfoClassArray = Split(InfoClass(1),vbcrlf)
InfoClassDataCheck = InfoClassArrayCheck(InfoClass(0),InfoClassArray)
If (InfoClassDataCheck <> 1) Then
'wscript.echo InfoClass(0) & " Not Correct: " & InfoClassDataCheck
wscript.quit
End If
SoftwareListIndex = CLng(InfoClassArray(0))
ReDim Preserve SoftwareDisplayName(SoftwareListIndex)
ReDim Preserve SoftwareDisplayVersion(SoftwareListIndex)
ReDim Preserve SoftwarePublisher(SoftwareListIndex)
ReDim Preserve SoftwareInstallDate(SoftwareListIndex)
a = ""
SoftwareListIndex = -1
for j=2 to UBound(InfoClassArray)
If (InfoClassArray(j) <> "") Then
SoftwareListIndex = SoftwareListIndex + 1
ClassDataArray = Split(InfoClassArray(j),vbtab)
if (Ubound(ClassDataArray) <> 3) Then
'wscript.echo InfoClass(0) & " Data Count Error"
wscript.quit
End If
SoftwareDisplayName(SoftwareListIndex) = ClassDataArray(0)
SoftwareDisplayVersion(SoftwareListIndex) = ClassDataArray(1)
SoftwarePublisher(SoftwareListIndex) = ClassDataArray(2)
SoftwareInstallDate(SoftwareListIndex) = ClassDataArray(3)
if ( FindStr(SoftwareInstallDate(SoftwareListIndex),"/") and (len(SoftwareInstallDate(SoftwareListIndex)) < 10) ) then
if (len(SoftwareInstallDate(SoftwareListIndex)) - len(replace(SoftwareInstallDate(SoftwareListIndex),"/","")) = 2) then
DateConvert = Split(SoftwareInstallDate(SoftwareListIndex),"/")
SoftwareInstallDate(SoftwareListIndex) = Int(DateConvert(0))+1911 & Right("0" & DateConvert(1),2) & Right("0" & DateConvert(2),2)
end if
end if
End If
Next

Case "Office"
Set InfoClassArray = Nothing
Set ClassDataArray = Nothing
InfoClassArray = Split(InfoClass(1),vbcrlf)
InfoClassDataCheck = InfoClassArrayCheck(InfoClass(0),InfoClassArray)
If (InfoClassDataCheck <> 1) Then
'wscript.echo InfoClass(0) & " Not Correct: " & InfoClassDataCheck
wscript.quit
End If
OfficeListIndex = CLng(InfoClassArray(0))
ReDim Preserve OfficeVersion(OfficeListIndex)
ReDim Preserve OfficeProductName(OfficeListIndex)
ReDim Preserve OfficeProductID(OfficeListIndex)
ReDim Preserve OfficeType(OfficeListIndex)
ReDim Preserve OfficeProductKey(OfficeListIndex)
ReDim Preserve OfficeNote(OfficeListIndex)
OfficeListIndex = -1
for j=2 to UBound(InfoClassArray)
If (InfoClassArray(j) <> "") Then
OfficeListIndex = OfficeListIndex + 1
ClassDataArray = Split(InfoClassArray(j),vbtab)
if (Ubound(ClassDataArray) <> 5) Then
'wscript.echo InfoClass(0) & " Data Count Error"
wscript.quit
End If
OfficeVersion(OfficeListIndex) = ClassDataArray(0)
OfficeProductName(OfficeListIndex) = ClassDataArray(1)
OfficeProductID(OfficeListIndex) = ClassDataArray(2)
OfficeType(OfficeListIndex) = ClassDataArray(3)
OfficeProductKey(OfficeListIndex) = ClassDataArray(4)
OfficeNote(OfficeListIndex) = ClassDataArray(5)
End If
Next
End Select
next
End Function


' ----- Check InfoClassData

Function InfoClassArrayCheck(DataType,InfoClassArray)

Select Case DataType
Case "MB"
ClassTitle = "MBManufacturer" & vbTab & "MBProduct" & vbTab & "MBSerialNumber" & vbTab & "MBVersion"
Case "CPU"
ClassTitle = "CPUAddressWidth" & vbTab & "CPUArchitecture" & vbTab & "CPUCaption" & vbTab & "CPUDataWidth" & vbTab & "CPUDeviceID" & vbTab & "CPUExtClock" & vbTab & "CPUFamily" & vbTab & "CPULevel" & vbTab & "CPUManufacturer" & vbTab & "CPUMaxClockSpeed" & vbTab & "CPUName" & vbTab & "CPUNumberOfCores" & vbTab & "CPUNumberOfLogicalProcessors" & vbTab & "CPUProcessorId" & vbTab & "CPUSocketDesignation"
Case "MEM"
ClassTitle = "MEMBankLabel" & vbTab & "MEMCapacity" & vbTab & "MEMDeviceLocator" & vbTab & "MEMManufacturer" & vbTab & "MEMMemoryType" & vbTab & "MEMPartNumber" & vbTab & "MEMSerialNumber" & vbTab & "MEMSpeed" & vbTab & "MEMTag"
Case "NIC"
ClassTitle = "NICCaption" & vbTab & "NICDHCPEnabled" & vbTab & "NICDNSHostName" & vbTab & "NICIPAddress" & vbTab & "NICMACAddress" & vbTab & "NICServiceName"
Case "Disk"
ClassTitle = "DiskCaption" & vbTab & "DiskFirmwareRevision" & vbTab & "DiskInterfaceType" & vbTab & "DiskMediaType" & vbTab & "DiskModel" & vbTab & "DiskSerialNumber" & vbTab & "DiskSignature" & vbTab & "DiskSize"
Case "CDROM"
ClassTitle = "CDROMCaption" & vbTab & "CDROMMediaType" & vbTab & "CDROMMfrAssignedRevisionLevel" & vbTab & "CDROMName"
Case "OS"
ClassTitle = "OSBuildNumber" & vbTab & "OSCaption" & vbTab & "OSCodeSet" & vbTab & "OSCountryCode" & vbTab & "OSCSDVersion" & vbTab & "OSCSName" & vbTab & "OSCurrentTimeZone" & vbTab & "OSInstallDate" & vbTab & "OSLastBootUpTime" & vbTab & "OSLocale" & vbTab & "OSManufacturer" & vbTab & "OSOperatingSystemSKU" & vbTab & "OSOSArchitecture" & vbTab & "OSOSLanguage" & vbTab & "OSOSProductSuite" & vbTab & "OSOSType" & vbTab & "OSOtherTypeDescription" & vbTab & "OSProductKey" & vbTab & "OSProductType" & vbTab & "OSRegisteredUser" & vbTab & "OSSerialNumber" & vbTab & "OSVersion"
Case "GroupMember"
ClassTitle = "GroupName" & vbTab & "Account"
Case "Software"
ClassTitle = "DisplayName" & vbTab & "DisplayVersion" & vbTab & "Publisher" & vbTab & "InstallDate"
Case "Office"
ClassTitle = "OfficeVersion" & vbTab & "OfficeProductName" & vbTab & "OfficeProductID" & vbTab & "OfficeType" & vbTab & "OfficeProductKey" & vbTab & "OfficeNote"
End Select
ReturnValue = 1
ClassCount = CLng(InfoClassArray(0))
If (UBound(InfoClassArray)-2 <> ClassCount) Then
ReturnValue = 2
End If
If (InfoClassArray(1) <> ClassTitle) Then
ReturnValue = 3
End If
InfoClassArrayCheck = ReturnValue

End Function

Function WriteInfoToSQL()

if (SQLConnOpen = 0) then
Set SQLConnection = CreateObject("ADODB.Connection")
Set SQLRecordSet = CreateObject("ADODB.RecordSet")
SQLConnString = "DRIVER={SQL Server};SERVER=" & SQLServer & ";UID=" & SQLUser & ";PWD=" & SQLPassword & ";DATABASE=" & SQLDB
SQLConnection.ConnectionTimeout = SQLConnectionTimeout
SQLConnection.Open SQLConnString
SQLConnOpen = 1
End If

'AllInfoID = InsertAllInfo()
TableName = "InfoAllDataLink"
ColumnName = Array("Type","Value")

' ----- MB
For i=0 to (MBIndex)
ColumnValue = Array("MBManufacturer",MBManufacturer(i))
InsertAllInfoDataMBManufacturer = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("MBProduct",MBProduct(i))
InsertAllInfoDataMBProduct = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("MBSerialNumber",MBSerialNumber(i))
InsertAllInfoDataMBSerialNumber = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("MBVersion",MBVersion(i))
InsertAllInfoDataMBVersion = InsertData(TableName,ColumnName,ColumnValue)

InsertInfoMBLinkColumnName = Array("Manufacturer","Product","SerialNumber","Version")
InsertInfoMBLinkColumnValue = Array(InsertAllInfoDataMBManufacturer,InsertAllInfoDataMBProduct,InsertAllInfoDataMBSerialNumber,InsertAllInfoDataMBVersion)

InsertAllInfoIndex = InsertAllInfoIndex + 1
ReDim Preserve InsertAllInfoLinkType(InsertAllInfoIndex)
ReDim Preserve InsertAllInfoLinkValue(InsertAllInfoIndex)

InsertAllInfoLinkType(InsertAllInfoIndex) = "MB"
InsertAllInfoLinkValue(InsertAllInfoIndex) = InsertData("InfoMBLink",InsertInfoMBLinkColumnName,InsertInfoMBLinkColumnValue)
Next
' ----- MB

' ----- CPU
For i=0 to (CPUIndex)
ColumnValue = Array("CPUAddressWidth",CPUAddressWidth(i))
InsertAllInfoDataCPUAddressWidth = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("CPUArchitecture",CPUArchitecture(i))
InsertAllInfoDataCPUArchitecture = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("CPUCaption",CPUCaption(i))
InsertAllInfoDataCPUCaption = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("CPUDataWidth",CPUDataWidth(i))
InsertAllInfoDataCPUDataWidth = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("CPUDeviceID",CPUDeviceID(i))
InsertAllInfoDataCPUDeviceID = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("CPUExtClock",CPUExtClock(i))
InsertAllInfoDataCPUExtClock = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("CPUFamily",CPUFamily(i))
InsertAllInfoDataCPUFamily = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("CPULevel",CPULevel(i))
InsertAllInfoDataCPULevel = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("CPUManufacturer",CPUManufacturer(i))
InsertAllInfoDataCPUManufacturer = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("CPUMaxClockSpeed",CPUMaxClockSpeed(i))
InsertAllInfoDataCPUMaxClockSpeed = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("CPUName",CPUName(i))
InsertAllInfoDataCPUName = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("CPUNumberOfCores",CPUNumberOfCores(i))
InsertAllInfoDataCPUNumberOfCores = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("CPUNumberOfLogicalProcessors",CPUNumberOfLogicalProcessors(i))
InsertAllInfoDataCPUNumberOfLogicalProcessors = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("CPUProcessorId",CPUProcessorId(i))
InsertAllInfoDataCPUProcessorId = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("CPUSocketDesignation",CPUSocketDesignation(i))
InsertAllInfoDataCPUSocketDesignation = InsertData(TableName,ColumnName,ColumnValue)

InsertInfoCPULinkColumnName = Array("AddressWidth","Architecture","Caption","DataWidth","DeviceID","ExtClock","Family","Level","Manufacturer","MaxClockSpeed","Name","NumberOfCores","NumberOfLogicalProcessors","ProcessorId","SocketDesignation")
InsertInfoCPULinkColumnValue = Array(InsertAllInfoDataCPUAddressWidth,InsertAllInfoDataCPUArchitecture,InsertAllInfoDataCPUCaption,InsertAllInfoDataCPUDataWidth,InsertAllInfoDataCPUDeviceID,InsertAllInfoDataCPUExtClock,InsertAllInfoDataCPUFamily,InsertAllInfoDataCPULevel,InsertAllInfoDataCPUManufacturer,InsertAllInfoDataCPUMaxClockSpeed,InsertAllInfoDataCPUName,InsertAllInfoDataCPUNumberOfCores,InsertAllInfoDataCPUNumberOfLogicalProcessors,InsertAllInfoDataCPUProcessorId,InsertAllInfoDataCPUSocketDesignation)

InsertAllInfoIndex = InsertAllInfoIndex + 1
ReDim Preserve InsertAllInfoLinkType(InsertAllInfoIndex)
ReDim Preserve InsertAllInfoLinkValue(InsertAllInfoIndex)

InsertAllInfoLinkType(InsertAllInfoIndex) = "CPU"
InsertAllInfoLinkValue(InsertAllInfoIndex) = InsertData("InfoCPULink",InsertInfoCPULinkColumnName,InsertInfoCPULinkColumnValue)
Next
' ----- CPU

' ----- MEM
For i=0 to (MEMIndex)
ColumnValue = Array("MEMBankLabel",MEMBankLabel(i))
InsertAllInfoDataMEMBankLabel = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("MEMDeviceLocator",MEMDeviceLocator(i))
InsertAllInfoDataMEMDeviceLocator = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("MEMTag",MEMTag(i))
InsertAllInfoDataMEMTag = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("MEMCapacity",MEMCapacity(i))
InsertAllInfoDataMEMCapacity = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("MEMManufacturer",MEMManufacturer(i))
InsertAllInfoDataMEMManufacturer = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("MEMMemoryType",MEMMemoryType(i))
InsertAllInfoDataMEMMemoryType = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("MEMPartNumber",MEMPartNumber(i))
InsertAllInfoDataMEMPartNumber = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("MEMSerialNumber",MEMSerialNumber(i))
InsertAllInfoDataMEMSerialNumber = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("MEMSpeed",MEMSpeed(i))
InsertAllInfoDataMEMSpeed = InsertData(TableName,ColumnName,ColumnValue)

InsertInfoMEMLinkColumnName = Array("BankLabel","DeviceLocator","Tag","Capacity","Manufacturer","MemoryType","PartNumber","SerialNumber","Speed")
InsertInfoMEMLinkColumnValue = Array(InsertAllInfoDataMEMBankLabel,InsertAllInfoDataMEMDeviceLocator,InsertAllInfoDataMEMTag,InsertAllInfoDataMEMCapacity,InsertAllInfoDataMEMManufacturer,InsertAllInfoDataMEMMemoryType,InsertAllInfoDataMEMPartNumber,InsertAllInfoDataMEMSerialNumber,InsertAllInfoDataMEMSpeed)

InsertAllInfoIndex = InsertAllInfoIndex + 1
ReDim Preserve InsertAllInfoLinkType(InsertAllInfoIndex)
ReDim Preserve InsertAllInfoLinkValue(InsertAllInfoIndex)

InsertAllInfoLinkType(InsertAllInfoIndex) = "MEM"
InsertAllInfoLinkValue(InsertAllInfoIndex) = InsertData("InfoMEMLink",InsertInfoMEMLinkColumnName,InsertInfoMEMLinkColumnValue)
Next
' ----- MEM

' ----- NIC
For i=0 to (NICIndex)
ColumnValue = Array("NICDHCPEnabled",NICDHCPEnabled(i))
InsertAllInfoDataNICDHCPEnabled = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("NICDNSHostName",NICDNSHostName(i))
InsertAllInfoDataNICDNSHostName = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("NICIPAddress",NICIPAddress(i))
InsertAllInfoDataNICIPAddress = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("NICCaption",NICCaption(i))
InsertAllInfoDataNICCaption = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("NICMACAddress",NICMACAddress(i))
InsertAllInfoDataNICMACAddress = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("NICServiceName",NICServiceName(i))
InsertAllInfoDataNICServiceName = InsertData(TableName,ColumnName,ColumnValue)

InsertInfoNICLinkColumnName = Array("DHCPEnabled","DNSHostName","IPAddress","Caption","MACAddress","ServiceName")
InsertInfoNICLinkColumnValue = Array(InsertAllInfoDataNICDHCPEnabled,InsertAllInfoDataNICDNSHostName,InsertAllInfoDataNICIPAddress,InsertAllInfoDataNICCaption,InsertAllInfoDataNICMACAddress,InsertAllInfoDataNICServiceName)

InsertAllInfoIndex = InsertAllInfoIndex + 1
ReDim Preserve InsertAllInfoLinkType(InsertAllInfoIndex)
ReDim Preserve InsertAllInfoLinkValue(InsertAllInfoIndex)

InsertAllInfoLinkType(InsertAllInfoIndex) = "NIC"
InsertAllInfoLinkValue(InsertAllInfoIndex) = InsertData("InfoNICLink",InsertInfoNICLinkColumnName,InsertInfoNICLinkColumnValue)

Next
' ----- NIC

' ----- Disk
For i=0 to (DiskIndex)
ColumnValue = Array("DiskCaption",DiskCaption(i))
InsertAllInfoDataDiskCaption = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("DiskFirmwareRevision",DiskFirmwareRevision(i))
InsertAllInfoDataDiskFirmwareRevision = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("DiskInterfaceType",DiskInterfaceType(i))
InsertAllInfoDataDiskInterfaceType = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("DiskMediaType",DiskMediaType(i))
InsertAllInfoDataDiskMediaType = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("DiskModel",DiskModel(i))
InsertAllInfoDataDiskModel = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("DiskSerialNumber",DiskSerialNumber(i))
InsertAllInfoDataDiskSerialNumber = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("DiskSignature",DiskSignature(i))
InsertAllInfoDataDiskSignature = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("DiskSize",DiskSize(i))
InsertAllInfoDataDiskSize = InsertData(TableName,ColumnName,ColumnValue)

InsertInfoDiskLinkColumnName = Array("Caption","FirmwareRevision","InterfaceType","MediaType","Model","SerialNumber","Signature","Size")
InsertInfoDiskLinkColumnValue = Array(InsertAllInfoDataDiskCaption,InsertAllInfoDataDiskFirmwareRevision,InsertAllInfoDataDiskInterfaceType,InsertAllInfoDataDiskMediaType,InsertAllInfoDataDiskModel,InsertAllInfoDataDiskSerialNumber,InsertAllInfoDataDiskSignature,InsertAllInfoDataDiskSize)

InsertAllInfoIndex = InsertAllInfoIndex + 1
ReDim Preserve InsertAllInfoLinkType(InsertAllInfoIndex)
ReDim Preserve InsertAllInfoLinkValue(InsertAllInfoIndex)

InsertAllInfoLinkType(InsertAllInfoIndex) = "Disk"
InsertAllInfoLinkValue(InsertAllInfoIndex) = InsertData("InfoDiskLink",InsertInfoDiskLinkColumnName,InsertInfoDiskLinkColumnValue)
Next
' ----- Disk

' ----- CDROM
For i=0 to (CDROMIndex)
ColumnValue = Array("CDROMCaption",CDROMCaption(i))
InsertAllInfoDataCDROMCaption = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("CDROMMediaType",CDROMMediaType(i))
InsertAllInfoDataCDROMMediaType = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("CDROMMfrAssignedRevisionLevel",CDROMMfrAssignedRevisionLevel(i))
InsertAllInfoDataCDROMMfrAssignedRevisionLevel = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("CDROMName",CDROMName(i))
InsertAllInfoDataCDROMName = InsertData(TableName,ColumnName,ColumnValue)


InsertInfoCDROMLinkColumnName = Array("Caption","MediaType","MfrAssignedRevisionLevel","Name")
InsertInfoCDROMLinkColumnValue = Array(InsertAllInfoDataCDROMCaption,InsertAllInfoDataCDROMMediaType,InsertAllInfoDataCDROMMfrAssignedRevisionLevel,InsertAllInfoDataCDROMName)

InsertAllInfoIndex = InsertAllInfoIndex + 1
ReDim Preserve InsertAllInfoLinkType(InsertAllInfoIndex)
ReDim Preserve InsertAllInfoLinkValue(InsertAllInfoIndex)

InsertAllInfoLinkType(InsertAllInfoIndex) = "CD"
InsertAllInfoLinkValue(InsertAllInfoIndex) = InsertData("InfoCDROMLink",InsertInfoCDROMLinkColumnName,InsertInfoCDROMLinkColumnValue)
Next
' ----- CDROM

' ----- OS
For i=0 to (OSIndex)
ColumnValue = Array("OSBuildNumber",OSBuildNumber(i))
InsertAllInfoDataOSBuildNumber = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("OSCaption",OSCaption(i))
InsertAllInfoDataOSCaption = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("OSCodeSet",OSCodeSet(i))
InsertAllInfoDataOSCodeSet = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("OSCountryCode",OSCountryCode(i))
InsertAllInfoDataOSCountryCode = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("OSCSDVersion",OSCSDVersion(i))
InsertAllInfoDataOSCSDVersion = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("OSCurrentTimeZone",OSCurrentTimeZone(i))
InsertAllInfoDataOSCurrentTimeZone = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("OSLocale",OSLocale(i))
InsertAllInfoDataOSLocale = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("OSManufacturer",OSManufacturer(i))
InsertAllInfoDataOSManufacturer = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("OSOperatingSystemSKU",OSOperatingSystemSKU(i))
InsertAllInfoDataOSOperatingSystemSKU = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("OSOSArchitecture",OSOSArchitecture(i))
InsertAllInfoDataOSOSArchitecture = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("OSOSLanguage",OSOSLanguage(i))
InsertAllInfoDataOSOSLanguage = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("OSOSProductSuite",OSOSProductSuite(i))
InsertAllInfoDataOSOSProductSuite = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("OSOSType",OSOSType(i))
InsertAllInfoDataOSOSType = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("OSOtherTypeDescription",OSOtherTypeDescription(i))
InsertAllInfoDataOSOtherTypeDescription = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("OSProductKey",OSProductKey(i))
InsertAllInfoDataOSProductKey = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("OSProductType",OSProductType(i))
InsertAllInfoDataOSProductType = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("OSVersion",OSVersion(i))
InsertAllInfoDataOSVersion = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("OSCSName",OSCSName(i))
InsertAllInfoDataOSCSName = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("OSRegisteredUser",OSRegisteredUser(i))
InsertAllInfoDataOSRegisteredUser = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("OSSerialNumber",OSSerialNumber(i))
InsertAllInfoDataOSSerialNumber = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("OSInstallDate",OSInstallDate(i))
InsertAllInfoDataOSInstallDate = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("OSLastBootUpTime",OSLastBootUpTime(i))
InsertAllInfoDataOSLastBootUpTime = InsertData(TableName,ColumnName,ColumnValue)


InsertInfoOSLinkColumnName = Array("BuildNumber","Caption","CodeSet","CountryCode","CSDVersion","CurrentTimeZone","Locale","Manufacturer","OperatingSystemSKU","OSArchitecture","OSLanguage","OSProductSuite","OSType","OtherTypeDescription","ProductKey","ProductType","Version","CSName","RegisteredUser","SerialNumber","InstallDate","LastBootUpTime")
InsertInfoOSLinkColumnValue = Array(InsertAllInfoDataOSBuildNumber,InsertAllInfoDataOSCaption,InsertAllInfoDataOSCodeSet,InsertAllInfoDataOSCountryCode,InsertAllInfoDataOSCSDVersion,InsertAllInfoDataOSCurrentTimeZone,InsertAllInfoDataOSLocale,InsertAllInfoDataOSManufacturer,InsertAllInfoDataOSOperatingSystemSKU,InsertAllInfoDataOSOSArchitecture,InsertAllInfoDataOSOSLanguage,InsertAllInfoDataOSOSProductSuite,InsertAllInfoDataOSOSType,InsertAllInfoDataOSOtherTypeDescription,InsertAllInfoDataOSProductKey,InsertAllInfoDataOSProductType,InsertAllInfoDataOSVersion,InsertAllInfoDataOSCSName,InsertAllInfoDataOSRegisteredUser,InsertAllInfoDataOSSerialNumber,InsertAllInfoDataOSInstallDate,InsertAllInfoDataOSLastBootUpTime)

InsertAllInfoIndex = InsertAllInfoIndex + 1
ReDim Preserve InsertAllInfoLinkType(InsertAllInfoIndex)
ReDim Preserve InsertAllInfoLinkValue(InsertAllInfoIndex)

InsertAllInfoLinkType(InsertAllInfoIndex) = "OS"
InsertAllInfoLinkValue(InsertAllInfoIndex) = InsertData("InfoOSLink",InsertInfoOSLinkColumnName,InsertInfoOSLinkColumnValue)
Next
' ----- OS

' ----- Privilege
For i=0 to (GroupMemberListIndex)
ColumnValue = Array("PrivilegeType",GroupMemberType(i))
InsertAllInfoDataPrivilegeType = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("PrivilegeAccount",GroupMemberAccount(i))
InsertAllInfoDataPrivilegeAccount = InsertData(TableName,ColumnName,ColumnValue)

InsertInfoPrivilegeLinkColumnName = Array("PrivilegeType","Account")
InsertInfoPrivilegeLinkColumnValue = Array(InsertAllInfoDataPrivilegeType,InsertAllInfoDataPrivilegeAccount)

InsertAllInfoIndex = InsertAllInfoIndex + 1
ReDim Preserve InsertAllInfoLinkType(InsertAllInfoIndex)
ReDim Preserve InsertAllInfoLinkValue(InsertAllInfoIndex)

InsertAllInfoLinkType(InsertAllInfoIndex) = "GP"
InsertAllInfoLinkValue(InsertAllInfoIndex) = InsertData("InfoPrivilegeLink",InsertInfoPrivilegeLinkColumnName,InsertInfoPrivilegeLinkColumnValue)
Next
' ----- Privilege

' ----- Office
For i=0 to (OfficeListIndex)
ColumnValue = Array("OfficeVersion",OfficeVersion(i))
InsertAllInfoDataOfficeVersion = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("OfficeProductName",OfficeProductName(i))
InsertAllInfoDataOfficeProductName = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("OfficeProductID",OfficeProductID(i))
InsertAllInfoDataOfficeProductID = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("OfficeType",OfficeType(i))
InsertAllInfoDataOfficeType = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("OfficeProductKey",OfficeProductKey(i))
InsertAllInfoDataOfficeProductKey = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("OfficeNote",OfficeNote(i))
InsertAllInfoDataOfficeNote = InsertData(TableName,ColumnName,ColumnValue)

InsertInfoPrivilegeLinkColumnName = Array("Version","ProductName","ProductID","OfficeType","ProductKey","Note")
InsertInfoPrivilegeLinkColumnValue = Array(InsertAllInfoDataOfficeVersion,InsertAllInfoDataOfficeProductName,InsertAllInfoDataOfficeProductID,InsertAllInfoDataOfficeType,InsertAllInfoDataOfficeProductKey,InsertAllInfoDataOfficeNote)

InsertAllInfoIndex = InsertAllInfoIndex + 1
ReDim Preserve InsertAllInfoLinkType(InsertAllInfoIndex)
ReDim Preserve InsertAllInfoLinkValue(InsertAllInfoIndex)

InsertAllInfoLinkType(InsertAllInfoIndex) = "Office"
InsertAllInfoLinkValue(InsertAllInfoIndex) = InsertData("InfoOfficeLink",InsertInfoPrivilegeLinkColumnName,InsertInfoPrivilegeLinkColumnValue)
Next

' ----- Office

' ----- Software
InsertInfoSoftwareLinkID = ""
For i=0 to (SoftwareListIndex)
ColumnValue = Array("SoftwareDisplayName",SoftwareDisplayName(i))
InsertAllInfoDataSoftwareDisplayName = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("SoftwareDisplayVersion",SoftwareDisplayVersion(i))
InsertAllInfoDataSoftwareDisplayVersion = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("SoftwarePublisher",SoftwarePublisher(i))
InsertAllInfoDataSoftwarePublisher = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("SoftwareInstallDate",SoftwareInstallDate(i))
InsertAllInfoDataSoftwareInstallDate = InsertData(TableName,ColumnName,ColumnValue)

InsertInfoSoftwareLinkColumnName = Array("DisplayName","DisplayVersion","Publisher","InstallDate")
InsertInfoSoftwareLinkColumnValue = Array(InsertAllInfoDataSoftwareDisplayName,InsertAllInfoDataSoftwareDisplayVersion,InsertAllInfoDataSoftwarePublisher,InsertAllInfoDataSoftwareInstallDate)

InsertAllInfoIndex = InsertAllInfoIndex + 1
ReDim Preserve InsertAllInfoLinkType(InsertAllInfoIndex)
ReDim Preserve InsertAllInfoLinkValue(InsertAllInfoIndex)

InsertAllInfoLinkType(InsertAllInfoIndex) = "SW"
InsertAllInfoLinkValue(InsertAllInfoIndex) = InsertData("InfoSoftwareLink",InsertInfoSoftwareLinkColumnName,InsertInfoSoftwareLinkColumnValue)
Next
' ----- Software

ColumnValue = Array("RecordDateTime",InfoDateTime)
InsertAllInfoDataRecordDateTime = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("RecordHostName",AllInfoDNSHostName)
InsertAllInfoDataRecordHostName = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("RecordDataType",InfoDataType)
InsertAllInfoDataRecordDataType = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("RecordLastLogonAccount",InfoLastLogonAccount)
InsertAllInfoDataLastLogonAccount = InsertData(TableName,ColumnName,ColumnValue)
ColumnValue = Array("RecordComputerSID",InfoComputerSID)
InsertAllInfoDataRecordComputerSID = InsertData(TableName,ColumnName,ColumnValue)
InsertInfoRecordLinkColumnName = Array("DateTime","HostName","DataType","LastLogonAccount","ComputerSID")
InsertInfoRecordLinkColumnValue = Array(InsertAllInfoDataRecordDateTime,InsertAllInfoDataRecordHostName,InsertAllInfoDataRecordDataType,InsertAllInfoDataLastLogonAccount,InsertAllInfoDataRecordComputerSID)

'Test = "MB: " & InsertInfoMBLinkID & vbcrlf & "CPU: " & InsertInfoCPULinkID & vbcrlf & "MEM: " & InsertInfoMEMLinkID & vbcrlf
'Test = Test & "NIC: " & InsertInfoNICLinkID & vbcrlf & "Disk: " & InsertInfoDiskLinkID & vbcrlf & "CD: " & InsertInfoCDROMLinkID & vbcrlf
'Test = Test & "OS: " & InsertInfoOSLinkID & vbcrlf & "Privilege: " & InsertInfoPrivilegeLinkID & vbcrlf & "Office: " & InsertInfoOfficeLinkID & vbcrlf
'Test = Test & "Software: " & InsertInfoSoftwareLinkID & vbcrlf & "Record: " & InsertInfoRecordLinkID & vbcrlf
'wscript.echo Test

'wscript.echo Join(InsertAllInfoLinkType)
'wscript.echo Join(InsertAllInfoLinkValue)

BaseRecordMigration()
FinalRecordMigration()

InsertInfoRecordLinkID = InsertData("InfoRecordLink",InsertInfoRecordLinkColumnName,InsertInfoRecordLinkColumnValue)

SQLString = "INSERT INTO [" & SQLDB & "].[dbo].[InfoAllLink] ([Record],[Type],[TypeSerial]) Values "
SQLStringADD = ""
For i=0 to (FinalInsertRecordIndex)
If (SQLStringADD <> "") Then
SQLStringADD = SQLStringADD & ","
End If
SQLStringADD = SQLStringADD & "(" & InsertInfoRecordLinkID & ",'" & FinalInsertType(i) & "'," & FinalInsertTypeSerial(i) & ")"
next

If (SQLStringADD <> "") Then
SQLString = SQLString & SQLStringADD & ";"
Set SQLRecordSet = SQLConnection.Execute(SQLString)
End If

End Function

Function FinalRecordMigration()
' Compare for New Add Item

if (DebugMode = 1) then
wscript.echo InfoFile & vbcrlf & vbcrlf & "Base3" & vbcrlf & vbcrlf & BaseRecordIndex & vbcrlf & vbcrlf & join(BaseRecordType) & vbcrlf & vbcrlf & join(BaseRecordTypeSerial)
wscript.echo InfoFile & vbcrlf & vbcrlf & "Insert3" & vbcrlf & vbcrlf & InsertAllInfoIndex & vbcrlf & vbcrlf & join(InsertAllInfoLinkType) & vbcrlf & vbcrlf & join(InsertAllInfoLinkValue)
end if
for j=0 to InsertAllInfoIndex
Add = 1
if (BaseRecordIndex > 0) then
for i=0 to BaseRecordIndex
if ( (InsertAllInfoLinkType(j) = BaseRecordType(i)) and (InsertAllInfoLinkValue(j) = BaseRecordTypeSerial(i)) ) then
Add = 0
exit for
end if
next
end if
if (Add = 1) then
FinalInsertRecordIndex = FinalInsertRecordIndex + 1
ReDim Preserve FinalInsertType(FinalInsertRecordIndex)
ReDim Preserve FinalInsertTypeSerial(FinalInsertRecordIndex)

FinalInsertType(FinalInsertRecordIndex) = InsertAllInfoLinkType(j)
FinalInsertTypeSerial(FinalInsertRecordIndex) = InsertAllInfoLinkValue(j)
end if
next
' Compare for Item to Remove

for i=0 to BaseRecordIndex
Remove = 1
for j=0 to InsertAllInfoIndex
if ( (InsertAllInfoLinkType(j) = BaseRecordType(i)) and (InsertAllInfoLinkValue(j) = BaseRecordTypeSerial(i)) ) then
Remove = 0
exit for
end if
next
if (Remove = 1) then
FinalInsertRecordIndex = FinalInsertRecordIndex + 1
ReDim Preserve FinalInsertType(FinalInsertRecordIndex)
ReDim Preserve FinalInsertTypeSerial(FinalInsertRecordIndex)

FinalInsertType(FinalInsertRecordIndex) = BaseRecordType(i)
FinalInsertTypeSerial(FinalInsertRecordIndex) = BaseRecordTypeSerial(i) - (2*BaseRecordTypeSerial(i))
end if
next

'if ( (FinalInsertRecordIndex > 0) and (BaseRecordIndex >= 0) )then
' wscript.echo "! Modify ! "& InfoFile
' wscript.echo "Base" & vbcrlf & vbcrlf & BaseRecordIndex & vbcrlf & vbcrlf & join(BaseRecordType) & vbcrlf & vbcrlf & join(BaseRecordTypeSerial)
' wscript.echo "Insert" & vbcrlf & vbcrlf & InsertAllInfoIndex & vbcrlf & vbcrlf & join(InsertAllInfoLinkType) & vbcrlf & vbcrlf & join(InsertAllInfoLinkValue)
' wscript.echo "Final" & vbcrlf & vbcrlf & FinalInsertRecordIndex & vbcrlf & vbcrlf & join(FinalInsertType) & vbcrlf & vbcrlf & join(FinalInsertTypeSerial)
'end if

if (DebugMode = 1) then
wscript.echo InfoFile & vbcrlf & vbcrlf & "Final4" & vbcrlf & vbcrlf & FinalInsertRecordIndex & vbcrlf & vbcrlf & join(FinalInsertType) & vbcrlf & vbcrlf & join(FinalInsertTypeSerial)
end if
End Function


Function BaseRecordMigration()

SQLString = "SELECT [Serial] FROM [" & SQLDB & "].[dbo].[InfoRecord] where [HostName] = '" & AllInfoDNSHostName & "' order by [DateTime]"
BaseRecord = ""
Set SQLRecordSet = SQLConnection.Execute(SQLString)
while not SQLRecordSet.EoF
if (BaseRecord <> "") then
BaseRecord = BaseRecord & ";"
end if
BaseRecord = BaseRecord & SQLRecordSet("Serial")
SQLRecordSet.movenext
wend
SQLRecordSet.Close

if (BaseRecord <> "") then
BaseRecordArray = Split(BaseRecord,";")

' 最早的第一筆新資料
SQLString = "SELECT [Type],[TypeSerial] FROM [" & SQLDB & "].[dbo].[InfoAllLink] where [Record] = " & BaseRecordArray(0) & ""
Set SQLRecordSet = SQLConnection.Execute(SQLString)
while not SQLRecordSet.EoF
BaseRecordIndex = BaseRecordIndex + 1
ReDim Preserve BaseRecordType(BaseRecordIndex)
ReDim Preserve BaseRecordTypeSerial(BaseRecordIndex)

BaseRecordType(BaseRecordIndex) = SQLRecordSet("Type")
BaseRecordTypeSerial(BaseRecordIndex) = CLng(SQLRecordSet("TypeSerial"))
SQLRecordSet.movenext
wend
SQLRecordSet.Close

if (UBound(BaseRecordArray) > 0) then
' 每一筆新資料
for i=1 to UBound(BaseRecordArray)
TempRecordIndex = -1
Erase TempRecordType
Erase TempRecordTypeSerial
SQLString = "SELECT [Type],[TypeSerial] FROM [" & SQLDB & "].[dbo].[InfoAllLink] where [Record] = " & BaseRecordArray(i) & ""
Set SQLRecordSet = SQLConnection.Execute(SQLString)
while not SQLRecordSet.EoF
TempRecordIndex = TempRecordIndex + 1
ReDim Preserve TempRecordType(TempRecordIndex)
ReDim Preserve TempRecordTypeSerial(TempRecordIndex)

TempRecordType(TempRecordIndex) = SQLRecordSet("Type")
TempRecordTypeSerial(TempRecordIndex) = CLng(SQLRecordSet("TypeSerial"))
SQLRecordSet.movenext
wend
SQLRecordSet.Close


if (DebugMode = 1) then
wscript.echo InfoFile & vbcrlf & vbcrlf & "Base1" & vbcrlf & vbcrlf & BaseRecordIndex & vbcrlf & vbcrlf & join(BaseRecordType) & vbcrlf & vbcrlf & join(BaseRecordTypeSerial)
wscript.echo InfoFile & vbcrlf & vbcrlf & "Temp1" & vbcrlf & vbcrlf & TempRecordIndex & vbcrlf & vbcrlf & join(TempRecordType) & vbcrlf & vbcrlf & join(TempRecordTypeSerial)
end if
for t=0 to TempRecordIndex
Add = 1
for b=0 to BaseRecordIndex
' 一正一負合為 0 表示有異動
' 皆負皆正合不為 0 表示無異動
if ( (BaseRecordType(b) = TempRecordType(t)) and ((BaseRecordTypeSerial(b) + TempRecordTypeSerial(t)) = 0) ) then
BaseRecordTypeSerial(b) = TempRecordTypeSerial(t)
Add = 0
exit for
end if
next
if (Add = 1) then
BaseRecordIndex = BaseRecordIndex + 1
ReDim Preserve BaseRecordType(BaseRecordIndex)
ReDim Preserve BaseRecordTypeSerial(BaseRecordIndex)

BaseRecordType(BaseRecordIndex) = TempRecordType(t)
BaseRecordTypeSerial(BaseRecordIndex) = TempRecordTypeSerial(t)
end if
next
next

' 排除移除的項目寫入暫存陣列
TempRecordIndex = -1
Erase TempRecordType
Erase TempRecordTypeSerial
for b=0 to BaseRecordIndex
if (BaseRecordTypeSerial(b) > 0) then
TempRecordIndex = TempRecordIndex + 1
ReDim Preserve TempRecordType(TempRecordIndex)
ReDim Preserve TempRecordTypeSerial(TempRecordIndex)

TempRecordType(TempRecordIndex) = BaseRecordType(b)
TempRecordTypeSerial(TempRecordIndex) = BaseRecordTypeSerial(b)
end if
next

BaseRecordIndex = -1
Erase BaseRecordType
Erase BaseRecordTypeSerial

for t=0 to TempRecordIndex
BaseRecordIndex = BaseRecordIndex + 1
ReDim Preserve BaseRecordType(BaseRecordIndex)
ReDim Preserve BaseRecordTypeSerial(BaseRecordIndex)

BaseRecordType(BaseRecordIndex) = TempRecordType(t)
BaseRecordTypeSerial(BaseRecordIndex) = TempRecordTypeSerial(t)
next


if (DebugMode = 1) then
wscript.echo InfoFile & vbcrlf & vbcrlf & "Base2" & vbcrlf & vbcrlf & BaseRecordIndex & vbcrlf & vbcrlf & join(BaseRecordType) & vbcrlf & vbcrlf & join(BaseRecordTypeSerial)
wscript.echo InfoFile & vbcrlf & vbcrlf & "Temp2" & vbcrlf & vbcrlf & TempRecordIndex & vbcrlf & vbcrlf & join(TempRecordType) & vbcrlf & vbcrlf & join(TempRecordTypeSerial)
end if
End if
end if
End Function

Function InsertAllInfo()
If (InfoDateTime = "") Then
InfoDateTime = "GETDATE()"
End If
SQLString = "SET NOCOUNT ON; INSERT INTO [" & SQLDB & "].[dbo].[ListAllInfo] ([DateTime],[HostName],[DataType],[LastLogonAccount]) Values (" & InfoDateTime & ",'" & AllInfoDNSHostName & "'," & InfoDataType & ",'" & InfoLastLogonAccount & "'); SELECT @@IDENTITY AS [NewID];"
' wscript.echo SQLString
Set SQLRecordSet = SQLConnection.Execute(SQLString)
InsertAllInfo = SQLRecordSet("NewID")
'wscript.echo InsertAllInfo
SQLRecordSet.Close
End Function

Function InsertData(TableName,ColumnName,ColumnValue)
ReturnID = 0
SQLString = ""
For k = 0 to ubound(ColumnName)
If (SQLString = "") Then
SQLString = "Select Top 1 [Serial] from [" & SQLDB & "].[dbo].[" & TableName & "] where"
else
SQLString = SQLString & " and "
End If
if (ColumnName(k) = "Type") then
SQLString = SQLString & " [" & ColumnName(k) & "] = (Select Top 1 [Serial] from [" & SQLDB & "].[dbo].[InfoAllDataType] where [Type] = '" & replace(ColumnValue(k),"'","''") & "')"
else
SQLString = SQLString & " [" & ColumnName(k) & "] = '" & replace(ColumnValue(k),"'","''") & "'"
end if
Next
If (SQLString <> "") Then
SQLRecordSet.Open SQLString,SQLConnection
If not SQLRecordSet.EoF Then
ReturnID = SQLRecordSet.Fields("Serial")
SQLRecordSet.close
else
SQLRecordSet.close
SQLString = ""
For k = 0 to ubound(ColumnName)
If (SQLString = "") Then
SQLString = "SET NOCOUNT ON; INSERT INTO [" & SQLDB & "].[dbo].[" & TableName & "] ("
else
SQLString = SQLString & ","
End If
SQLString = SQLString & "[" & ColumnName(k) & "]"
Next
SQLString = SQLString & ") Values ("
SQLStringADD = ""
For k = 0 to ubound(ColumnName)
If (SQLStringADD <> "") Then
SQLStringADD = SQLStringADD & ","
End If
if (ColumnName(k) = "Type") then
SQLStringADD =  SQLStringADD & "(Select Top 1 [Serial] from [" & SQLDB & "].[dbo].[InfoAllDataType] where [Type] = '" & replace(ColumnValue(k),"'","''") & "')"
else
SQLStringADD =  SQLStringADD & "'" & replace(ColumnValue(k),"'","''") & "'"
end if
Next
SQLStringADD = SQLStringADD & "); SELECT @@IDENTITY AS [NewID];"
SQLString = SQLString & SQLStringADD
On Error Resume Next
Set SQLRecordSet = SQLConnection.Execute(SQLString)
        If SQLRecordSet("NewID") Is Nothing Then
            ' Error occured
            RunAgain = WshShell.Run(ExecuteProgram & " " & WScript.ScriptFullName , , False)
            wscript.quit
        Else
            ' do the right thing
        End If
    On error goto 0
ReturnID = SQLRecordSet("NewID")
SQLRecordSet.Close
End If
End If
InsertData = CLng(ReturnID)
End Function


Function DateTimeFormat(DateTime)
ReturnValue = DateTime
ReturnValue = Replace(ReturnValue,"AM 12","00")
ReturnValue = Replace(ReturnValue,"AM 01","01")
ReturnValue = Replace(ReturnValue,"AM 02","02")
ReturnValue = Replace(ReturnValue,"AM 03","03")
ReturnValue = Replace(ReturnValue,"AM 04","04")
ReturnValue = Replace(ReturnValue,"AM 05","05")
ReturnValue = Replace(ReturnValue,"AM 06","06")
ReturnValue = Replace(ReturnValue,"AM 07","07")
ReturnValue = Replace(ReturnValue,"AM 08","08")
ReturnValue = Replace(ReturnValue,"AM 09","09")
ReturnValue = Replace(ReturnValue,"AM 10","10")
ReturnValue = Replace(ReturnValue,"AM 11","11")
ReturnValue = Replace(ReturnValue,"PM 12","12")
ReturnValue = Replace(ReturnValue,"PM 01","13")
ReturnValue = Replace(ReturnValue,"PM 02","14")
ReturnValue = Replace(ReturnValue,"PM 03","15")
ReturnValue = Replace(ReturnValue,"PM 04","16")
ReturnValue = Replace(ReturnValue,"PM 05","17")
ReturnValue = Replace(ReturnValue,"PM 06","18")
ReturnValue = Replace(ReturnValue,"PM 07","19")
ReturnValue = Replace(ReturnValue,"PM 08","20")
ReturnValue = Replace(ReturnValue,"PM 09","21")
ReturnValue = Replace(ReturnValue,"PM 10","22")
ReturnValue = Replace(ReturnValue,"PM 11","23")
ReturnValue = Replace(ReturnValue,"上午 12","00")
ReturnValue = Replace(ReturnValue,"上午 01","01")
ReturnValue = Replace(ReturnValue,"上午 02","02")
ReturnValue = Replace(ReturnValue,"上午 03","03")
ReturnValue = Replace(ReturnValue,"上午 04","04")
ReturnValue = Replace(ReturnValue,"上午 05","05")
ReturnValue = Replace(ReturnValue,"上午 06","06")
ReturnValue = Replace(ReturnValue,"上午 07","07")
ReturnValue = Replace(ReturnValue,"上午 08","08")
ReturnValue = Replace(ReturnValue,"上午 09","09")
ReturnValue = Replace(ReturnValue,"上午 10","10")
ReturnValue = Replace(ReturnValue,"上午 11","11")
ReturnValue = Replace(ReturnValue,"下午 12","12")
ReturnValue = Replace(ReturnValue,"下午 01","13")
ReturnValue = Replace(ReturnValue,"下午 02","14")
ReturnValue = Replace(ReturnValue,"下午 03","15")
ReturnValue = Replace(ReturnValue,"下午 04","16")
ReturnValue = Replace(ReturnValue,"下午 05","17")
ReturnValue = Replace(ReturnValue,"下午 06","18")
ReturnValue = Replace(ReturnValue,"下午 07","19")
ReturnValue = Replace(ReturnValue,"下午 08","20")
ReturnValue = Replace(ReturnValue,"下午 09","21")
ReturnValue = Replace(ReturnValue,"下午 10","22")
ReturnValue = Replace(ReturnValue,"下午 11","23")
ReturnValue = Replace(ReturnValue,"AM 1","01")
ReturnValue = Replace(ReturnValue,"AM 2","02")
ReturnValue = Replace(ReturnValue,"AM 3","03")
ReturnValue = Replace(ReturnValue,"AM 4","04")
ReturnValue = Replace(ReturnValue,"AM 5","05")
ReturnValue = Replace(ReturnValue,"AM 6","06")
ReturnValue = Replace(ReturnValue,"AM 7","07")
ReturnValue = Replace(ReturnValue,"AM 8","08")
ReturnValue = Replace(ReturnValue,"AM 9","09")
ReturnValue = Replace(ReturnValue,"PM 1","13")
ReturnValue = Replace(ReturnValue,"PM 2","14")
ReturnValue = Replace(ReturnValue,"PM 3","15")
ReturnValue = Replace(ReturnValue,"PM 4","16")
ReturnValue = Replace(ReturnValue,"PM 5","17")
ReturnValue = Replace(ReturnValue,"PM 6","18")
ReturnValue = Replace(ReturnValue,"PM 7","19")
ReturnValue = Replace(ReturnValue,"PM 8","20")
ReturnValue = Replace(ReturnValue,"PM 9","21")
ReturnValue = Replace(ReturnValue,"上午 1","01")
ReturnValue = Replace(ReturnValue,"上午 2","02")
ReturnValue = Replace(ReturnValue,"上午 3","03")
ReturnValue = Replace(ReturnValue,"上午 4","04")
ReturnValue = Replace(ReturnValue,"上午 5","05")
ReturnValue = Replace(ReturnValue,"上午 6","06")
ReturnValue = Replace(ReturnValue,"上午 7","07")
ReturnValue = Replace(ReturnValue,"上午 8","08")
ReturnValue = Replace(ReturnValue,"上午 9","09")
ReturnValue = Replace(ReturnValue,"下午 1","13")
ReturnValue = Replace(ReturnValue,"下午 2","14")
ReturnValue = Replace(ReturnValue,"下午 3","15")
ReturnValue = Replace(ReturnValue,"下午 4","16")
ReturnValue = Replace(ReturnValue,"下午 5","17")
ReturnValue = Replace(ReturnValue,"下午 6","18")
ReturnValue = Replace(ReturnValue,"下午 7","19")
ReturnValue = Replace(ReturnValue,"下午 8","20")
ReturnValue = Replace(ReturnValue,"下午 9","21")
ReturnValue = FormatDateTime(ReturnValue,2) & " " & FormatDateTime(ReturnValue,4)
DateTimeFormat = ReturnValue
End Function

function ConvertTime(sTime)

if ( (sTime="**************.******+***") or (sTime = "") ) then
ConvertTime = ""
else
if (Trim(sTime)="") then
sTime="Unknown"
else
sYear = Mid(sTime,1,4)
sMonth = Mid(sTime,5,2)
sDay = Mid(sTime,7,2)
sHour = Mid(sTime,9,2)
sMin = Mid(sTime,11,2)
sSec = Mid(sTime,13,2)
end if
ConvertTime =  sYear & "-" & sMonth & "-" & sDay & " " & sHour & ":" & sMin & ":" & sSec
end if
end function

function FindStr(Str,Find)
ReturnValue = 0
SStr = LCase(Str)
FFind = LCase(Find)
if (SStr <> replace(SStr,FFind,"")) then
ReturnValue = 1
end if
FindStr = ReturnValue
end function

Function CurrProcessId
Dim oShell, sCmd, oWMI, oChldPrcs, oCols, lOut
lOut = 0
Set oShell  = CreateObject("WScript.Shell")
Set oWMI    = GetObject(_
"winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2")
sCmd = "/K " & Left(CreateObject("Scriptlet.TypeLib").Guid, 38)
oShell.Run "%comspec% " & sCmd, 0
WScript.Sleep 100 'For healthier skin, get some sleep
Set oChldPrcs = oWMI.ExecQuery(_
"Select * From Win32_Process Where CommandLine Like '%" & sCmd & "'", ,32)
For Each oCols In oChldPrcs
lOut = oCols.ParentProcessId 'get parent
oCols.Terminate 'process terminated
Exit For
Next
Set oChldPrcs = Nothing
Set oWMI = Nothing
Set oShell = Nothing
CurrProcessId = lOut
End Function

沒有留言: