vbscriptkofax
VBScript issue with values in a table
I have this code to build a table and send it via e-mail to business.
They want to know, on a daily basis, how many files were transferred from a Source to a Destination, and if there's a difference in the number of files (possible failed/corrupted transfers). All this by folder.
The problem is that this code somehow misses the row of the Number of Missing Files in the Destination folder. It seems like if it were attributing the row in a random manner. The image below shows that even though the folder 3 is complete, it does say there is 1 missing file, and the folder 5 has 1 missing file (59 in the Source and only 58 in the Destination folder) and it states 0 missing items.
What am I missing here?
The code is:
' >>> init a Windows Shell object to run system commands
SET WshShell = WScript.CREATEOBJECT("WScript.Shell")
' >>> load email Class code
WDIR = "D:\Kofax_Scripts"
SET objFSO = CreateObject("Scripting.FileSystemObject")
SET mailObjectFile = objFSO.OpenTextFile( WDIR & "\email.vbs", 1)
Execute mailObjectFile.ReadAll()
' >>> TEST passed arguments
If WScript.Arguments.Count = 0 Then
Wscript.echo vbCr & vbLf & "Usage is: cscript.exe //nologo Kofax_SAP_crosscheck.vbs DEV|PRD [date]" & vbCrLf
Wscript.echo "If date is not given, script uses system current date. To run this script for other dates, you must pass it in format YYYY-MM-DD"
wscript.quit
End If
If WScript.Arguments.Item(0) = "DEV" Then
Wscript.echo "Running in DEV..."
ElseIf WScript.Arguments.Item(0) = "PRD" Then
Wscript.echo "Running in PRD..."
Else
Wscript.echo vbCr & vbLf & "Environment parameter is wrong! Possible choices: DEV|PRD"
wscript.quit
End If
' >>> Get today's date
t1=Now()
Wscript.echo "starting at: " & t1
' >>> Set date to use for the files' date crosscheck
Dim date_cross_check
If WScript.Arguments.Count = 2 Then
date_cross_check = CDate(WScript.Arguments.Item(1))
Else
date_cross_check = t1
End If
' >>> compose date string from the files pathname to be checked
ano = Year(date_cross_check)
mes = Month(date_cross_check)
dia = Day(date_cross_check)
date_cross_check_str = ano & "/" & mes & "/" & dia
' Set lists of Folders to cross-check
Set KofaxFolders = CreateObject("Scripting.Dictionary")
Set SapFolders = CreateObject("Scripting.Dictionary")
KofaxFolders.Add "AMOS", CreateObject("Scripting.Dictionary")
KofaxFolders.Add "bomdia", CreateObject("Scripting.Dictionary")
KofaxFolders.Add "cockpit", CreateObject("Scripting.Dictionary")
KofaxFolders.Add "irreg", CreateObject("Scripting.Dictionary")
KofaxFolders.Add "miro", CreateObject("Scripting.Dictionary")
KofaxFolders.Add "BSP", CreateObject("Scripting.Dictionary")
SapFolders.Add "AMOS", CreateObject("Scripting.Dictionary")
SapFolders.Add "bomdia", CreateObject("Scripting.Dictionary")
SapFolders.Add "cockpit", CreateObject("Scripting.Dictionary")
SapFolders.Add "irreg", CreateObject("Scripting.Dictionary")
SapFolders.Add "miro", CreateObject("Scripting.Dictionary")
SapFolders.Add "BSP", CreateObject("Scripting.Dictionary")
' init dictionaries
For each key in KofaxFolders
KofaxFolders(key).Add "files", CreateObject("Scripting.Dictionary")
KofaxFolders(key).Add "count", 0
Next
For each key in SapFolders
SapFolders(key).Add "files", CreateObject("Scripting.Dictionary")
SapFolders(key).Add "missing", 0
SapFolders(key).Add "count", 0
Next
' init File System Object
Dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")
' Loop on KofaxFolders to fill contents folders
for each key in KofaxFolders
fldr = "D:\Projetos\EXPORT\" & key &"\Save\"& ano &"\"& mes &"\"& dia
If (objFSO.FolderExists(fldr)) Then
Set objFolder = objFSO.GetFolder(fldr)
For Each objFile In objFolder.Files
' Don't consider garbage files like Thumbs.db
If ( objFile.name <> "Thumbs.db" ) Then
' Wscript.echo "Folder : " & key & "Filename: " & objFile.name & ", size (bytes): " & objFile.size
KofaxFolders(key)("files").Add objFile.name, objFile.size
KofaxFolders(key)("count") = KofaxFolders(key)("count") + 1
End If
Next
End If
next
' Loop on SapFolders to fill contents folders
for each key in SapFolders
Set objFolder = objFSO.GetFolder("\\iftpv01\TPP\transdata\InB\OCRSave\Save"&key)
'WScript.Echo "Folder : " & key
For Each objFile In objFolder.Files
'Wscript.echo "Folder : " & key & "Filename: " & objFile.name & ", size (bytes): " & objFile.size
' Check only SAP files with last Modified Date equal to specified date
' --------------------------------------------------------------------
If ( DateDiff("d",objFile.DateLastModified, CDate(date_cross_check)) = 0 )Then
' Don't consider garbage files like Thumbs.db
If ( objFile.name <> "Thumbs.db" ) Then
SapFolders(key)("files").Add objFile.name, objFile.size
SapFolders(key)("count") = SapFolders(key)("count") + 1
End If
End If
Next
next
' ------------------------'
' Start new empty log file'
' ------------------------'
Dim log_file
log_file = WDIR & "\tmp\kofax_sap_crosscheck.log"
Set objLogFile = objFSO.CreateTextFile(log_file,True)
objLogFile.Close
' open file in write mode
Set objLogFile = objFSO.OpenTextFile(log_file, 2)
' Loop on KofaxFolders Contents and check if file exists in SAP structure
For each key in KofaxFolders
For each file in KofaxFolders(key)("files")
If ( NOT SapFolders(key)("files").Exists(file) ) Then
objLogFile.WriteLine("file " & file & " is missing from InB SAP folder "&key)
SapFolders(key)("missing") = SapFolders(key)("missing") + 1
Else
' If file size is different between Kofax and SAP, this may be due to corrupt transfer
If SapFolders(key)("files")(file) <> KofaxFolders(key)("files")(file) Then
objLogFile.WriteLine("file " & file & " has not same size in SAP and Kofax " & key &" Folders!!! Kofax size: " _
& KofaxFolders(key)("files")(file) & "| SAP size: " _
& SapFolders(key)("files")(file) _
)
SapFolders(key)("missing") = SapFolders(key)("missing") + 1
End If
End If
Next
Next
' close log file
objLogFile.Close
' compute execution time
exec_time = datediff("s",t1,Now)
' Global missing count
Dim missing_files : missing_files = 0
For each key in SapFolders
missing_files = missing_files + SapFolders(key)("missing")
Next
' Build summary HTML table according to "missing_files" count
Dim rep_table : rep_table = ""
if ( missing_files > 0 )Then
rep_table = "<table border=""1""><tr><th>Folder</th><th>Nr of files Source</th><th>Nr of files Dest</th><th>Nr of Files missing Dest</th></tr>"
For each key in SapFolders
rep_table = rep_table & "<tr><td>" & key & "</td><td align=""right"">" & KofaxFolders(key)("count") &"</td><td align=""right"">" & SapFolders(key)("count") & "</td>"
if ( SapFolders(key)("missing") > 0 ) Then
rep_table = rep_table & "<td align=""right"" bgcolor=""#FF0000"">"
Else
rep_table = rep_table & "<td align=""right"">"
End If
rep_table = rep_table & SapFolders(key)("missing") &"</td></tr>"
Next
Else
rep_table = "<table border=""1""><tr><th>Pasta</th><th>Nº ficheiros no Kofax</th><th>Nº ficheiros no SAP</th></tr>"
For each key in SapFolders
rep_table = rep_table & "<tr><td>" & key & "</td><td align=""right"">" & KofaxFolders(key)("count") &"</td><td align=""right"">" & SapFolders(key)("count") & "</td></tr>"
Next
End If
rep_table = rep_table & "</table>"
Solution
I am not 100% sure about what is happening here, but I think that the issue is that SapFolders is a dictionary and you are using the line
For each key in SapFolders
to iterate over it when creating the table. The order of keys in such an iteration is (essentially) random. In your case, it isn't true that the loop iterate over the keys "AMOS", "bomdia", "cockpit", "irreg", "miro", "BSP" in that order.
What you could do is to create an array:
keys = Array("AMOS", "bomdia", "cockpit", "irreg", "miro", "BSP")
and replace every loop which begins
For each key in SapFolders
by
For i = 0 to UBound(keys)
key = keys(i)
(and maybe do a similar move for iterations involving KofaxFolders).
This will guarantee that you know the order with which you are populating the report table.