847 lines
33 KiB
Plaintext
Executable File
847 lines
33 KiB
Plaintext
Executable File
|
|
'============== static variables =========================
|
|
dim DATA_START_ROW
|
|
dim PLAYER_START_COLUMN
|
|
dim PLAYER_START_ROW
|
|
dim PLAYER_DATA_COLUMNS
|
|
dim MAX_NUM_PLAYERS
|
|
dim DURATION_TO_IGNORE
|
|
|
|
DATA_START_ROW = 23
|
|
PLAYER_START_COLUMN = 6
|
|
PLAYER_START_ROW = 17
|
|
PLAYER_DATA_COLUMNS = 4
|
|
MAX_NUM_PLAYERS = 24
|
|
DURATION_TO_IGNORE = 15000
|
|
|
|
dim iTotalNumberOfPlayers
|
|
|
|
|
|
'============== DEBUG FILE =========================
|
|
dim bUseDebugFile
|
|
dim debugFile
|
|
bUseDebugFile = TRUE
|
|
|
|
'create the debug file
|
|
Set filesys = CreateObject("Scripting.FileSystemObject")
|
|
if bUseDebugFile then
|
|
Set debugFile = filesys.OpenTextFile("X:\gta5\script\dev\Tools\Metrics\bin\debug.txt", 2, True)
|
|
end if
|
|
|
|
function debug_write(strDebugString)
|
|
if bUseDebugFile then
|
|
debugFile.Write(CStr(strDebugString))
|
|
debugFile.WriteLine()
|
|
end if
|
|
end function
|
|
|
|
|
|
'============== FUNCTIONS =========================
|
|
|
|
function is_true(inString)
|
|
if isNull(inString) then
|
|
is_true = false
|
|
end if
|
|
is_true = inString
|
|
end function
|
|
|
|
function no_cache_string()
|
|
dim strReturn
|
|
strReturn = "&Now="
|
|
strReturn = strReturn & Now()
|
|
no_cache_string = strReturn
|
|
end function
|
|
|
|
function tidy_up_string(inContents)
|
|
dim result
|
|
result = trim(replace(replace(inContents,chr(34), ""), vbTab, ""))
|
|
if isnumeric(result) then
|
|
result = CDbl(result)
|
|
end if
|
|
tidy_up_string = result
|
|
end function
|
|
|
|
function does_string_exist_in_string(smallString, fullString)
|
|
If (InStr(UCase(fullString), UCase(smallString)) > 0) then
|
|
does_string_exist_in_string = true
|
|
exit function
|
|
end if
|
|
does_string_exist_in_string = false
|
|
end function
|
|
|
|
function find_string_between_strings(inContents, string1, string2)
|
|
dim pos, pos2
|
|
dim length
|
|
pos = InStr(UCase(inContents), UCase(string1))
|
|
if pos > 0 then
|
|
length = len(string1)
|
|
pos = pos + length
|
|
pos2 = InStr(pos, UCase(inContents), UCase(string2))
|
|
if pos2 > 0 then
|
|
find_string_between_strings = tidy_up_string(mid(inContents, pos, pos2-pos))
|
|
exit function
|
|
end if
|
|
end if
|
|
find_string_between_strings = ""
|
|
end function
|
|
|
|
function find_string_between_strings_starting_from(inContents, startString, string1, string2)
|
|
dim pos
|
|
|
|
'check for empty strings being passed in
|
|
if StrComp(startString, "", 1) = 0 then
|
|
inContents = "StartOfString" & inContents
|
|
startString = "StartOfString"
|
|
end if
|
|
if StrComp(string1, "", 1) = 0 then
|
|
string1 = "StartOfString"
|
|
end if
|
|
if StrComp(string2, "", 1) = 0 then
|
|
inContents = inContents & "EndOfString"
|
|
string2 = "EndOfString"
|
|
end if
|
|
|
|
pos = InStr(UCase(inContents), UCase(startString))
|
|
if pos > 0 then
|
|
find_string_between_strings_starting_from = tidy_up_string(find_string_between_strings(right(inContents, (len(inContents) - pos) + 1), string1, string2))
|
|
exit function
|
|
end if
|
|
|
|
find_string_between_strings_starting_from = ""
|
|
end function
|
|
|
|
function does_string_exist_in_array(smallString, ByRef sArray)
|
|
If isArray(sArray) then
|
|
for each item in sArray
|
|
if StrComp(smallString, item, 1) = 0 then
|
|
does_string_exist_in_array = true
|
|
exit function
|
|
end if
|
|
next
|
|
end if
|
|
does_string_exist_in_array = false
|
|
end function
|
|
|
|
sub save_string_in_array(smallString, ByRef sArray)
|
|
if isArray(sArray) then
|
|
|
|
iMin = LBound(sArray)
|
|
iMax = UBound(sArray)
|
|
For i=iMin to iMax
|
|
if StrComp("", sArray(i), 1) = 0 then
|
|
sArray(i) = smallString
|
|
debug_write(smallString & " - saved to array")
|
|
exit sub
|
|
end if
|
|
Next
|
|
|
|
end if
|
|
end sub
|
|
|
|
function ColString(inNumber)
|
|
|
|
If inNumber > 26 Then
|
|
ColString = Chr(Int((inNumber - 1) / 26) + 64) & Chr(((inNumber - 1) Mod 26) + 65)
|
|
Else
|
|
ColString = Chr(inNumber + 64)
|
|
End If
|
|
|
|
end function
|
|
|
|
|
|
|
|
function GetStringForColumnRange(inCol, iLastDataRow)
|
|
dim strReturn
|
|
strReturn = ColString(inCol) & DATA_START_ROW & ":" & ColString(inCol) & iLastDataRow
|
|
GetStringForColumnRange = strReturn
|
|
end function
|
|
|
|
function ignore_div_zero(inFormula)
|
|
ignore_div_zero = "=IF(ISERROR(" & inFormula & ") ," & chr(34) & chr(34) &", " & inFormula & ")"
|
|
'ignore_div_zero = "=" & inFormula
|
|
end function
|
|
|
|
|
|
'==================================================================
|
|
' First loop through the data and store all the different scripts
|
|
'==================================================================
|
|
|
|
dim bWriteToFiles
|
|
|
|
bWriteToFiles = true
|
|
|
|
dim sScriptsToProcess(128)
|
|
dim iCountOfScripts
|
|
dim i
|
|
dim objFSO, objFile, draggedFile
|
|
dim readfile
|
|
dim thisScript
|
|
dim ScriptMetricFile
|
|
|
|
'initialise the array
|
|
for i=0 to 127
|
|
sScriptsToProcess(i) = ""
|
|
next
|
|
|
|
Set objFSO = CreateObject("Scripting.FileSystemObject")
|
|
|
|
if WScript.Arguments.Count > 0 then
|
|
for each draggedFile in WScript.Arguments
|
|
|
|
set objFile = objFSO.GetFile(draggedFile)
|
|
set readfile = objFSO.OpenTextFile(draggedFile, 1, false)
|
|
|
|
debug_write("creating temp files... " & " time = " & Time())
|
|
|
|
'get the file details
|
|
contents = readfile.readline
|
|
while not readfile.AtEndOfStream
|
|
|
|
|
|
'get script name
|
|
if does_string_exist_in_string("Script:", contents) then
|
|
|
|
thisScript = find_string_between_strings(contents, "Script:", ";")
|
|
|
|
'if the name is greater than 31 then truncate it
|
|
if not len(thisScript) < 32 then
|
|
thisScript = mid(thisScript, 1, 31)
|
|
end if
|
|
|
|
if len(thisScript) < 32 then
|
|
if not (does_string_exist_in_string(":", thisScript) and not does_string_exist_in_string(".", thisScript) and not isnumeric(thisScript)) then
|
|
|
|
'have we already stored this script?
|
|
if not does_string_exist_in_array(thisScript, sScriptsToProcess) then
|
|
save_string_in_array thisScript, sScriptsToProcess
|
|
iCountOfScripts = iCountOfScripts + 1
|
|
if (bWriteToFiles) then
|
|
Set ScriptMetricFile = filesys.OpenTextFile("X:\gta5\script\dev\Tools\Metrics\bin\temp_data\smd_" & thisScript & ".txt", 2, True)
|
|
ScriptMetricFile.Close
|
|
end if
|
|
end if
|
|
|
|
'write to metrics data file
|
|
if (bWriteToFiles) then
|
|
Set ScriptMetricFile = filesys.OpenTextFile("X:\gta5\script\dev\Tools\Metrics\bin\temp_data\smd_" & thisScript & ".txt", 8, True)
|
|
ScriptMetricFile.write(contents)
|
|
ScriptMetricFile.WriteLine()
|
|
ScriptMetricFile.Close
|
|
end if
|
|
else
|
|
debug_write("***** name contains illegal character ***** " & thisScript)
|
|
end if
|
|
else
|
|
|
|
debug_write("***** string name too long ***** " & thisScript)
|
|
|
|
end if
|
|
|
|
end if
|
|
|
|
'go to next line
|
|
contents = readfile.readline
|
|
|
|
wend
|
|
|
|
|
|
next
|
|
end if
|
|
|
|
'add end of file lines to each file
|
|
if (bWriteToFiles) then
|
|
debug_write("writing END OF FILE to each file..." & Time())
|
|
for each item in sScriptsToProcess
|
|
if not StrComp("", item, 1) = 0 then
|
|
Set ScriptMetricFile = filesys.OpenTextFile("X:\gta5\script\dev\Tools\Metrics\bin\temp_data\smd_" & item & ".txt", 8, True)
|
|
ScriptMetricFile.write("END OF FILE")
|
|
ScriptMetricFile.WriteLine()
|
|
ScriptMetricFile.Close
|
|
end if
|
|
next
|
|
end if
|
|
|
|
debug_write("finished creating temp files " & " time = " & Time())
|
|
|
|
|
|
'==================================================================
|
|
' Next process each script and add the details to excel
|
|
'==================================================================
|
|
dim iCountOfLines
|
|
|
|
if iCountOfScripts > 0 then
|
|
|
|
'variables for excel
|
|
dim objExcel, objWorkbook, objWorksheet
|
|
dim strFileName
|
|
|
|
'variables for stored data
|
|
dim strVersionName
|
|
dim iNoPlayers
|
|
dim iNoBots
|
|
dim strExeVersion
|
|
dim strScriptVersion
|
|
dim iPlayerNumber
|
|
dim iPlayerTeam
|
|
dim strScriptName
|
|
dim fCurrentTime
|
|
dim fAverageTime
|
|
dim fPeakTime
|
|
dim iNetworkTime
|
|
dim iDuration
|
|
dim thisScriptInLog
|
|
dim bIsMarker
|
|
dim strMarkerMessage
|
|
dim iHost
|
|
|
|
'Launch Excel
|
|
Set objExcel = CreateObject("Excel.Application")
|
|
|
|
'Save excel sheet
|
|
objExcel.Application.Visible = false
|
|
objExcel.ScreenUpdating = false
|
|
|
|
dim minuteString
|
|
if Minute(Time()) < 10 then
|
|
minuteString = "0" & Minute(Time())
|
|
else
|
|
minuteString = Minute(Time())
|
|
end if
|
|
|
|
|
|
Set objWorkbook = objExcel.Workbooks.Add()
|
|
strFileName = "X:\gta5\script\dev\Tools\Metrics\excel_sheets\sm_" & Day(Date()) & Month(Date()) & Year(Date()) & "_" & Hour(Time()) & minuteString
|
|
objWorkbook.SaveAs(strFileName)
|
|
|
|
objExcel.Application.Calculation = xlCalculationManual
|
|
|
|
'delete the default worksheets
|
|
for each item in sScriptsToProcess
|
|
|
|
iTotalNumberOfPlayers = 0
|
|
|
|
if not StrComp("", item, 1) = 0 then
|
|
|
|
debug_write("----------------------------------------------------------------------")
|
|
debug_write(item & " processing " & " time = " & Time())
|
|
|
|
iCountOfLines = 0
|
|
|
|
'create a new worksheet
|
|
set objWorksheet = objWorkbook.WorkSheets.Add()
|
|
objWorksheet.Name = item
|
|
objWorksheet.Unprotect
|
|
|
|
objWorksheet.Cells(1, 1).Value = "Script Name:"
|
|
objWorksheet.Cells(1, 1).Font.Bold = TRUE
|
|
objWorksheet.Cells(1, 2).Value = item
|
|
|
|
objWorksheet.Cells(2, 1).Value = "Version:"
|
|
objWorksheet.Cells(2, 1).Font.Bold = TRUE
|
|
|
|
objWorksheet.Cells(3, 1).Value = "Exe:"
|
|
objWorksheet.Cells(3, 1).Font.Bold = TRUE
|
|
|
|
objWorksheet.Cells(4, 1).Value = "Script Version:"
|
|
objWorksheet.Cells(4, 1).Font.Bold = TRUE
|
|
|
|
objWorksheet.Cells(5, 1).Value = "Date:"
|
|
objWorksheet.Cells(5, 1).Font.Bold = TRUE
|
|
|
|
objWorksheet.Cells(6, 1).Value = "Time:"
|
|
objWorksheet.Cells(6, 1).Font.Bold = TRUE
|
|
|
|
objWorksheet.Cells(7, 1).Value = "HOST"
|
|
objWorksheet.Cells(7, 1).Font.Bold = TRUE
|
|
|
|
objWorksheet.Cells(7, 2).Value = "Overall"
|
|
objWorksheet.Cells(7, 2).Font.Bold = TRUE
|
|
|
|
for i = 0 to MAX_NUM_PLAYERS - 1
|
|
objWorksheet.Cells(7, 3 + i).Value = i + 1
|
|
objWorksheet.Cells(7, 3 + i).Font.Bold = TRUE
|
|
objWorksheet.Cells(7, 3 + MAX_NUM_PLAYERS + i).Value = i + 1
|
|
objWorksheet.Cells(7, 3 + MAX_NUM_PLAYERS + i).Font.Bold = TRUE
|
|
next
|
|
|
|
objWorksheet.Cells(8, 1).Value = "Av Current"
|
|
objWorksheet.Cells(8, 1).Font.Bold = FALSE
|
|
|
|
objWorksheet.Cells(9, 1).Value = "Av Average"
|
|
objWorksheet.Cells(9, 1).Font.Bold = FALSE
|
|
|
|
objWorksheet.Cells(10, 1).Value = "Av Peak"
|
|
objWorksheet.Cells(10, 1).Font.Bold = FALSE
|
|
|
|
objWorksheet.Cells(11, 1).Value = "Max Peak"
|
|
objWorksheet.Cells(11, 1).Font.Bold = FALSE
|
|
|
|
objWorksheet.Cells(12, 1).Value = "NON HOST"
|
|
objWorksheet.Cells(12, 1).Font.Bold = TRUE
|
|
|
|
objWorksheet.Cells(13, 1).Value = "Av Current"
|
|
objWorksheet.Cells(13, 1).Font.Bold = FALSE
|
|
|
|
objWorksheet.Cells(14, 1).Value = "Av Average"
|
|
objWorksheet.Cells(14, 1).Font.Bold = FALSE
|
|
|
|
objWorksheet.Cells(15, 1).Value = "Av Peak"
|
|
objWorksheet.Cells(15, 1).Font.Bold = FALSE
|
|
|
|
objWorksheet.Cells(16, 1).Value = "Max Peak"
|
|
objWorksheet.Cells(16, 1).Font.Bold = FALSE
|
|
|
|
|
|
objWorksheet.Cells(17, 1).Value = "COMBINED"
|
|
objWorksheet.Cells(17, 1).Font.Bold = TRUE
|
|
|
|
objWorksheet.Cells(18, 1).Value = "Av Current"
|
|
objWorksheet.Cells(18, 1).Font.Bold = FALSE
|
|
|
|
objWorksheet.Cells(19, 1).Value = "Av Average"
|
|
objWorksheet.Cells(19, 1).Font.Bold = FALSE
|
|
|
|
objWorksheet.Cells(20, 1).Value = "Av Peak"
|
|
objWorksheet.Cells(20, 1).Font.Bold = FALSE
|
|
|
|
objWorksheet.Cells(21, 1).Value = "Max Peak"
|
|
objWorksheet.Cells(21, 1).Font.Bold = FALSE
|
|
|
|
|
|
objWorksheet.Cells(22, 1).Value = "Network Time"
|
|
objWorksheet.Cells(22, 1).Font.Bold = TRUE
|
|
|
|
objWorksheet.Cells(22, 2).Value = "Duration Time"
|
|
objWorksheet.Cells(22, 2).Font.Bold = TRUE
|
|
|
|
objWorksheet.Cells(22, 3).Value = "No Players"
|
|
objWorksheet.Cells(22, 3).Font.Bold = TRUE
|
|
|
|
objWorksheet.Cells(22, 4).Value = "No Bots"
|
|
objWorksheet.Cells(22, 4).Font.Bold = TRUE
|
|
|
|
objWorksheet.Cells(22, 5).Value = "PlayerID"
|
|
objWorksheet.Cells(22, 5).Font.Bold = TRUE
|
|
|
|
objWorksheet.Cells(22, 6).Value = "Host"
|
|
objWorksheet.Cells(22, 6).Font.Bold = TRUE
|
|
|
|
objWorksheet.Cells(22, 7).Value = "Team"
|
|
objWorksheet.Cells(22, 7).Font.Bold = TRUE
|
|
|
|
objWorksheet.Cells(22, 8).Value = "Current"
|
|
objWorksheet.Cells(22, 8).Font.Bold = TRUE
|
|
|
|
objWorksheet.Cells(22, 9).Value = "Average"
|
|
objWorksheet.Cells(22, 9).Font.Bold = TRUE
|
|
|
|
objWorksheet.Cells(22, 10).Value = "Peak"
|
|
objWorksheet.Cells(22, 10).Font.Bold = TRUE
|
|
|
|
objWorksheet.Cells(22, 11).Value = "Markers"
|
|
objWorksheet.Cells(22, 11).Font.Bold = TRUE
|
|
|
|
objWorksheet.Cells(6, 3).Value = "No of Players"
|
|
objWorksheet.Cells(6, 3).Font.Bold = TRUE
|
|
objWorksheet.Cells(6, 3+MAX_NUM_PLAYERS).Value = "No of Bots"
|
|
objWorksheet.Cells(6, 3+MAX_NUM_PLAYERS).Font.Bold = TRUE
|
|
|
|
objWorksheet.Cells(1, 4).Value = "Max Players"
|
|
objWorksheet.Cells(1, 4).Font.Bold = TRUE
|
|
|
|
objWorksheet.Cells(2, 4).Value = "Max Bots"
|
|
objWorksheet.Cells(2, 4).Font.Bold = TRUE
|
|
|
|
objWorksheet.Columns(1).AutoFit()
|
|
objWorksheet.Columns(2).AutoFit()
|
|
objWorksheet.Columns(3).AutoFit()
|
|
objWorksheet.Columns(4).AutoFit()
|
|
objWorksheet.Columns(5).AutoFit()
|
|
objWorksheet.Columns(6).AutoFit()
|
|
objWorksheet.Columns(7).AutoFit()
|
|
objWorksheet.Columns(8).AutoFit()
|
|
objWorksheet.Columns(9).AutoFit()
|
|
objWorksheet.Columns(10).AutoFit()
|
|
|
|
|
|
|
|
'loop through the log and extract the data for this script
|
|
'if WScript.Arguments.Count > 0 then
|
|
' for each draggedFile in WScript.Arguments
|
|
|
|
draggedFile = "X:\gta5\script\dev\Tools\Metrics\bin\temp_data\smd_" & item & ".txt"
|
|
|
|
set objFile = objFSO.GetFile(draggedFile)
|
|
'set readfile = objFSO.OpenTextFile(draggedFile, 1, false)
|
|
|
|
readfile = objFSO.OpenTextFile(draggedFile).ReadAll
|
|
dim arrLines
|
|
dim iLineCount
|
|
|
|
arrLines = Split(readfile, vbCrLf)
|
|
iLineCount = UBound(arrLines) + 1
|
|
|
|
dim arrData
|
|
redim arrData(iLineCount,11)
|
|
|
|
dim iActiveRow
|
|
iActiveRow = DATA_START_ROW
|
|
'dim iStartNetworkTime
|
|
|
|
set readfile = objFSO.OpenTextFile(draggedFile, 1, false)
|
|
|
|
'get the file details
|
|
contents = readfile.readline
|
|
while not readfile.AtEndOfStream
|
|
|
|
|
|
'is it the script we are processing?
|
|
if does_string_exist_in_string("Script:" & item, contents) then
|
|
|
|
'debug_write("thisScriptInLog = " & thisScriptInLog & " item = " & item)
|
|
iCountOfLines = iCountOfLines + 1
|
|
|
|
'is this a marker?
|
|
if does_string_exist_in_string(";Marker:", contents) then
|
|
bIsMarker = 1
|
|
'store the info we are interested in
|
|
else
|
|
bIsMarker = 0
|
|
end if
|
|
|
|
'store the info we are interested in
|
|
strScriptName = item
|
|
if (bIsMarker = 0) then
|
|
strVersionName = find_string_between_strings(contents, "V:", ";")
|
|
iNoPlayers = find_string_between_strings(contents, "Plyrs:", ";")
|
|
iNoBots = find_string_between_strings(contents, "Bots:", ";")
|
|
strExeVersion = find_string_between_strings(contents, "Exe:", ";")
|
|
strScriptVersion = find_string_between_strings(contents, "SV:", ";")
|
|
fCurrentTime = find_string_between_strings(contents, "Current:", ";")
|
|
fAverageTime = find_string_between_strings(contents, "Average:", ";")
|
|
fPeakTime = find_string_between_strings(contents, "Peak:", ";")
|
|
iDuration = find_string_between_strings(contents, "Duration:", ";")
|
|
end if
|
|
iPlayerNumber = find_string_between_strings(contents, "PID:", ";")
|
|
iPlayerTeam = find_string_between_strings(contents, "Team:", ";")
|
|
iHost = find_string_between_strings(contents, "Host:", ";")
|
|
iNetworkTime = find_string_between_strings(contents, "NetworkTime:", ";")
|
|
if (bIsMarker = 1) then
|
|
strMarkerMessage = find_string_between_strings(contents, "Marker:", ";")
|
|
end if
|
|
|
|
|
|
'if (iActiveRow = DATA_START_ROW) then
|
|
' iStartNetworkTime = iNetworkTime
|
|
'end if
|
|
|
|
'if (iNetworkTime < iStartNetworkTime) then
|
|
' iStartNetworkTime = iNetworkTime
|
|
'end if
|
|
|
|
' add the rest of the data
|
|
arrData(iCountOfLines-1, 0) = iNetworkTime
|
|
arrData(iCountOfLines-1, 4) = iPlayerNumber
|
|
arrData(iCountOfLines-1, 5) = iHost
|
|
arrData(iCountOfLines-1, 6) = iPlayerTeam
|
|
if (bIsMarker = 0) then
|
|
arrData(iCountOfLines-1, 1) = iDuration
|
|
arrData(iCountOfLines-1, 3) = iNoBots
|
|
arrData(iCountOfLines-1, 2) = iNoPlayers
|
|
arrData(iCountOfLines-1, 7) = fCurrentTime
|
|
arrData(iCountOfLines-1, 8) = fAverageTime
|
|
arrData(iCountOfLines-1, 9) = fPeakTime
|
|
end if
|
|
if (bIsMarker = 1) then
|
|
arrData(iCountOfLines-1, 10) = strMarkerMessage
|
|
end if
|
|
|
|
|
|
|
|
iActiveRow = iActiveRow + 1
|
|
|
|
'debug_write("processed line " & iCountOfLines)
|
|
|
|
end if
|
|
|
|
|
|
|
|
'go to next line
|
|
contents = readfile.readline
|
|
|
|
wend
|
|
|
|
'fill the start times
|
|
'dim iSelectedLine
|
|
'for iSelectedLine = 0 to iCountOfLines - 1
|
|
' arrData(iSelectedLine, 1) = arrData(iSelectedLine, 0) - iStartNetworkTime
|
|
'next
|
|
|
|
'fill all data
|
|
objWorksheet.Range("A" & DATA_START_ROW & ":K" & iActiveRow-1) = arrData
|
|
|
|
'sort the data
|
|
objWorksheet.Sort.SortFields.Clear
|
|
objWorksheet.Range("A" & DATA_START_ROW & ":K" & iActiveRow-1).Sort(objWorksheet.Range("A" & DATA_START_ROW))
|
|
debug_write(item & " sorted " & " time = " & Time())
|
|
|
|
'remove the duplicates
|
|
objWorksheet.Range("A" & DATA_START_ROW & ":K" & iActiveRow-1).select
|
|
objWorksheet.Range("A" & DATA_START_ROW & ":K" & iActiveRow-1).RemoveDuplicates(Array(1,5,11))'network time, player id, marker
|
|
debug_write(item & " removed duplicates " & " time = " & Time())
|
|
|
|
'fill in the general info
|
|
objWorksheet.Cells(2, 2).Value = strVersionName
|
|
objWorksheet.Cells(3, 2).Value = strExeVersion
|
|
objWorksheet.Cells(4, 2).Value = strScriptVersion
|
|
objWorksheet.Cells(5, 2).Value = Date()
|
|
objWorksheet.Cells(5, 2).NumberFormat = "dd/mm/yyyy"
|
|
objWorksheet.Cells(6, 2).Value = Time()
|
|
objWorksheet.Cells(6, 2).NumberFormat = "hh:mm:ss"
|
|
|
|
'next
|
|
'end if
|
|
|
|
objWorksheet.Columns(11).AutoFit()
|
|
|
|
debug_write(item & " finished processing, number of entries = " & iCountOfLines & " time = " & Time())
|
|
|
|
|
|
'Calculate all the averages etc.
|
|
dim LastDataRow
|
|
dim strFormula
|
|
|
|
|
|
' store what the last row of data is
|
|
LastDataRow = 0
|
|
i = 0
|
|
while (LastDataRow = 0)
|
|
if IsEmpty(objWorksheet.Cells(DATA_START_ROW + i, 1).Value) then
|
|
LastDataRow = DATA_START_ROW + (i-1)
|
|
end if
|
|
i = i + 1
|
|
wend
|
|
|
|
|
|
' OVERALL
|
|
|
|
'Max players
|
|
strFormula = "MAX(" & GetStringForColumnRange(3,LastDataRow) & ")"
|
|
objWorksheet.Cells(1, 5).Formula = ignore_div_zero(strFormula)
|
|
|
|
'Max bots
|
|
strFormula = "MAX(" & GetStringForColumnRange(4,LastDataRow) & ")"
|
|
objWorksheet.Cells(2, 5).Formula = ignore_div_zero(strFormula)
|
|
|
|
' HOST
|
|
'--------------------
|
|
|
|
'Current Time
|
|
strFormula = "AVERAGEIFS(" & GetStringForColumnRange(8,LastDataRow) & "," & GetStringForColumnRange(6,LastDataRow) & ",1," & GetStringForColumnRange(2,LastDataRow) & "," & chr(34) & ">" & DURATION_TO_IGNORE & chr(34) & ")"
|
|
'strFormula = ignore_div_zero(strFormula)
|
|
'debug_write(strFormula)
|
|
objWorksheet.Cells(8, 2).Formula = ignore_div_zero(strFormula)
|
|
|
|
'Average Time
|
|
strFormula = "AVERAGEIFS(" & GetStringForColumnRange(9,LastDataRow) & "," & GetStringForColumnRange(6,LastDataRow) & ",1," & GetStringForColumnRange(2,LastDataRow) & "," & chr(34) & ">" & DURATION_TO_IGNORE & chr(34) & ")"
|
|
objWorksheet.Cells(9, 2).Formula = ignore_div_zero(strFormula)
|
|
|
|
'Average Peak
|
|
strFormula = "AVERAGEIFS(" & GetStringForColumnRange(10,LastDataRow) & "," & GetStringForColumnRange(6,LastDataRow) & ",1," & GetStringForColumnRange(2,LastDataRow) & "," & chr(34) & ">" & DURATION_TO_IGNORE & chr(34) & ")"
|
|
objWorksheet.Cells(10, 2).Formula = ignore_div_zero(strFormula)
|
|
|
|
'Highest Peak
|
|
strFormula = "=MAX(IF(" & GetStringForColumnRange(6,LastDataRow) & "=1, IF(" & GetStringForColumnRange(2,LastDataRow) & ">" & DURATION_TO_IGNORE & ", " & GetStringForColumnRange(10,LastDataRow) &")))"
|
|
objWorksheet.Cells(11, 2).FormulaArray = strFormula
|
|
|
|
' NON HOST
|
|
'--------------------
|
|
|
|
'Current Time
|
|
strFormula = "AVERAGEIFS(" & GetStringForColumnRange(8,LastDataRow) & "," & GetStringForColumnRange(6,LastDataRow) & ",0," & GetStringForColumnRange(2,LastDataRow) & "," & chr(34) & ">" & DURATION_TO_IGNORE & chr(34) & ")"
|
|
objWorksheet.Cells(13, 2).Formula = ignore_div_zero(strFormula)
|
|
|
|
'Average Time
|
|
strFormula = "AVERAGEIFS(" & GetStringForColumnRange(9,LastDataRow) & "," & GetStringForColumnRange(6,LastDataRow) & ",0," & GetStringForColumnRange(2,LastDataRow) & "," & chr(34) & ">" & DURATION_TO_IGNORE & chr(34) & ")"
|
|
objWorksheet.Cells(14, 2).Formula = ignore_div_zero(strFormula)
|
|
|
|
'Average Peak
|
|
strFormula = "AVERAGEIFS(" & GetStringForColumnRange(10,LastDataRow) & "," & GetStringForColumnRange(6,LastDataRow) & ",0," & GetStringForColumnRange(2,LastDataRow) & "," & chr(34) & ">" & DURATION_TO_IGNORE & chr(34) & ")"
|
|
objWorksheet.Cells(15, 2).Formula = ignore_div_zero(strFormula)
|
|
|
|
'Highest Peak
|
|
strFormula = "=MAX(IF(" & GetStringForColumnRange(6,LastDataRow) & "=0, IF(" & GetStringForColumnRange(2,LastDataRow) & ">" & DURATION_TO_IGNORE & ", " & GetStringForColumnRange(10,LastDataRow) &")))"
|
|
objWorksheet.Cells(16, 2).FormulaArray = strFormula
|
|
|
|
|
|
' COMBINED
|
|
'--------------------
|
|
|
|
'Current Time
|
|
strFormula = "AVERAGEIFS(" & GetStringForColumnRange(8,LastDataRow) & "," & GetStringForColumnRange(2,LastDataRow) & "," & chr(34) & ">" & DURATION_TO_IGNORE & chr(34) & ")"
|
|
objWorksheet.Cells(18, 2).Formula = ignore_div_zero(strFormula)
|
|
|
|
'Average Time
|
|
strFormula = "AVERAGEIFS(" & GetStringForColumnRange(9,LastDataRow) & "," & GetStringForColumnRange(2,LastDataRow) & "," & chr(34) & ">" & DURATION_TO_IGNORE & chr(34) & ")"
|
|
objWorksheet.Cells(19, 2).Formula = ignore_div_zero(strFormula)
|
|
|
|
'Average Peak
|
|
strFormula = "AVERAGEIFS(" & GetStringForColumnRange(10,LastDataRow) & "," & GetStringForColumnRange(2,LastDataRow) & "," & chr(34) & ">" & DURATION_TO_IGNORE & chr(34) & ")"
|
|
objWorksheet.Cells(20, 2).Formula = ignore_div_zero(strFormula)
|
|
|
|
'Highest Peak
|
|
strFormula = "=MAX(IF(" & GetStringForColumnRange(2,LastDataRow) & ">" & DURATION_TO_IGNORE & ", " & GetStringForColumnRange(10,LastDataRow) &"))"
|
|
objWorksheet.Cells(21, 2).FormulaArray = strFormula
|
|
|
|
|
|
' FOR EACH NUMBER OF PLAYERS
|
|
dim iSelectedNoPlayers
|
|
|
|
for iSelectedNoPlayers = 1 to MAX_NUM_PLAYERS
|
|
|
|
'host
|
|
|
|
'current
|
|
strFormula = "AVERAGEIFS(" & GetStringForColumnRange(8,LastDataRow) & "," & GetStringForColumnRange(6,LastDataRow) & ",1," & GetStringForColumnRange(3,LastDataRow) & "," & iSelectedNoPlayers & "," & GetStringForColumnRange(2,LastDataRow) & "," & chr(34) & ">" & DURATION_TO_IGNORE & chr(34) & ")"
|
|
objWorksheet.Cells(8, 2 + iSelectedNoPlayers).Formula = ignore_div_zero(strFormula)
|
|
|
|
'average
|
|
strFormula = "AVERAGEIFS(" & GetStringForColumnRange(9,LastDataRow) & "," & GetStringForColumnRange(6,LastDataRow) & ",1," & GetStringForColumnRange(3,LastDataRow) & "," & iSelectedNoPlayers & "," & GetStringForColumnRange(2,LastDataRow) & "," & chr(34) & ">" & DURATION_TO_IGNORE & chr(34) & ")"
|
|
objWorksheet.Cells(9, 2 + iSelectedNoPlayers).Formula = ignore_div_zero(strFormula)
|
|
|
|
'peak
|
|
strFormula = "AVERAGEIFS(" & GetStringForColumnRange(10,LastDataRow) & "," & GetStringForColumnRange(6,LastDataRow) & ",1," & GetStringForColumnRange(3,LastDataRow) & "," & iSelectedNoPlayers & "," & GetStringForColumnRange(2,LastDataRow) & "," & chr(34) & ">" & DURATION_TO_IGNORE & chr(34) & ")"
|
|
objWorksheet.Cells(10, 2 + iSelectedNoPlayers).Formula = ignore_div_zero(strFormula)
|
|
|
|
'max peak
|
|
strFormula = "=MAX(IF(" & GetStringForColumnRange(6,LastDataRow) & "=1, IF(" & GetStringForColumnRange(2,LastDataRow) & ">" & DURATION_TO_IGNORE & ", IF(" & GetStringForColumnRange(3,LastDataRow) & "=" & iSelectedNoPlayers & "," & GetStringForColumnRange(10,LastDataRow) &"))))"
|
|
objWorksheet.Cells(11, 2 + iSelectedNoPlayers).FormulaArray = strFormula
|
|
|
|
|
|
'non-host
|
|
|
|
'current
|
|
strFormula = "AVERAGEIFS(" & GetStringForColumnRange(8,LastDataRow) & "," & GetStringForColumnRange(6,LastDataRow) & ",0," & GetStringForColumnRange(3,LastDataRow) & "," & iSelectedNoPlayers & "," & GetStringForColumnRange(2,LastDataRow) & "," & chr(34) & ">" & DURATION_TO_IGNORE & chr(34) & ")"
|
|
objWorksheet.Cells(13, 2 + iSelectedNoPlayers).Formula = ignore_div_zero(strFormula)
|
|
|
|
'average
|
|
strFormula = "AVERAGEIFS(" & GetStringForColumnRange(9,LastDataRow) & "," & GetStringForColumnRange(6,LastDataRow) & ",0," & GetStringForColumnRange(3,LastDataRow) & "," & iSelectedNoPlayers & "," & GetStringForColumnRange(2,LastDataRow) & "," & chr(34) & ">" & DURATION_TO_IGNORE & chr(34) & ")"
|
|
objWorksheet.Cells(14, 2 + iSelectedNoPlayers).Formula = ignore_div_zero(strFormula)
|
|
|
|
'peak
|
|
strFormula = "AVERAGEIFS(" & GetStringForColumnRange(10,LastDataRow) & "," & GetStringForColumnRange(6,LastDataRow) & ",0," & GetStringForColumnRange(3,LastDataRow) & "," & iSelectedNoPlayers & "," & GetStringForColumnRange(2,LastDataRow) & "," & chr(34) & ">" & DURATION_TO_IGNORE & chr(34) & ")"
|
|
objWorksheet.Cells(15, 2 + iSelectedNoPlayers).Formula = ignore_div_zero(strFormula)
|
|
|
|
'max peak
|
|
strFormula = "=MAX(IF(" & GetStringForColumnRange(6,LastDataRow) & "=0, IF(" & GetStringForColumnRange(2,LastDataRow) & ">" & DURATION_TO_IGNORE & ", IF(" & GetStringForColumnRange(3,LastDataRow) & "=" & iSelectedNoPlayers & "," & GetStringForColumnRange(10,LastDataRow) &"))))"
|
|
objWorksheet.Cells(16, 2 + iSelectedNoPlayers).FormulaArray = strFormula
|
|
|
|
'combined
|
|
|
|
'current
|
|
strFormula = "AVERAGEIFS(" & GetStringForColumnRange(8,LastDataRow) & "," & GetStringForColumnRange(3,LastDataRow) & "," & iSelectedNoPlayers & "," & GetStringForColumnRange(2,LastDataRow) & "," & chr(34) & ">" & DURATION_TO_IGNORE & chr(34) & ")"
|
|
objWorksheet.Cells(18, 2 + iSelectedNoPlayers).Formula = ignore_div_zero(strFormula)
|
|
|
|
'average
|
|
strFormula = "AVERAGEIFS(" & GetStringForColumnRange(9,LastDataRow) & "," & GetStringForColumnRange(3,LastDataRow) & "," & iSelectedNoPlayers & "," & GetStringForColumnRange(2,LastDataRow) & "," & chr(34) & ">" & DURATION_TO_IGNORE & chr(34) & ")"
|
|
objWorksheet.Cells(19, 2 + iSelectedNoPlayers).Formula = ignore_div_zero(strFormula)
|
|
|
|
'peak
|
|
strFormula = "AVERAGEIFS(" & GetStringForColumnRange(10,LastDataRow) & "," & GetStringForColumnRange(3,LastDataRow) & "," & iSelectedNoPlayers & "," & GetStringForColumnRange(2,LastDataRow) & "," & chr(34) & ">" & DURATION_TO_IGNORE & chr(34) & ")"
|
|
objWorksheet.Cells(20, 2 + iSelectedNoPlayers).Formula = ignore_div_zero(strFormula)
|
|
|
|
'max peak
|
|
strFormula = "=MAX(IF(" & GetStringForColumnRange(2,LastDataRow) & ">" & DURATION_TO_IGNORE & ", IF(" & GetStringForColumnRange(3,LastDataRow) & "=" & iSelectedNoPlayers & "," & GetStringForColumnRange(10,LastDataRow) &")))"
|
|
objWorksheet.Cells(21, 2 + iSelectedNoPlayers).FormulaArray = strFormula
|
|
|
|
next
|
|
|
|
' FOR EACH NUMBER OF BOTS
|
|
for iSelectedNoPlayers = 1 to MAX_NUM_PLAYERS
|
|
|
|
'host
|
|
|
|
'current
|
|
strFormula = "AVERAGEIFS(" & GetStringForColumnRange(8,LastDataRow) & "," & GetStringForColumnRange(6,LastDataRow) & ",1," & GetStringForColumnRange(4,LastDataRow) & "," & iSelectedNoPlayers & "," & GetStringForColumnRange(2,LastDataRow) & "," & chr(34) & ">" & DURATION_TO_IGNORE & chr(34) & ")"
|
|
objWorksheet.Cells(8, 2 + MAX_NUM_PLAYERS + iSelectedNoPlayers).Formula = ignore_div_zero(strFormula)
|
|
|
|
'average
|
|
strFormula = "AVERAGEIFS(" & GetStringForColumnRange(9,LastDataRow) & "," & GetStringForColumnRange(6,LastDataRow) & ",1," & GetStringForColumnRange(4,LastDataRow) & "," & iSelectedNoPlayers & "," & GetStringForColumnRange(2,LastDataRow) & "," & chr(34) & ">" & DURATION_TO_IGNORE & chr(34) & ")"
|
|
objWorksheet.Cells(9, 2 + MAX_NUM_PLAYERS + iSelectedNoPlayers).Formula = ignore_div_zero(strFormula)
|
|
|
|
'peak
|
|
strFormula = "AVERAGEIFS(" & GetStringForColumnRange(10,LastDataRow) & "," & GetStringForColumnRange(6,LastDataRow) & ",1," & GetStringForColumnRange(4,LastDataRow) & "," & iSelectedNoPlayers & "," & GetStringForColumnRange(2,LastDataRow) & "," & chr(34) & ">" & DURATION_TO_IGNORE & chr(34) & ")"
|
|
objWorksheet.Cells(10, 2 + MAX_NUM_PLAYERS + iSelectedNoPlayers).Formula = ignore_div_zero(strFormula)
|
|
|
|
'max peak
|
|
strFormula = "=MAX(IF(" & GetStringForColumnRange(6,LastDataRow) & "=1, IF(" & GetStringForColumnRange(2,LastDataRow) & ">" & DURATION_TO_IGNORE & ", IF(" & GetStringForColumnRange(4,LastDataRow) & "=" & iSelectedNoPlayers & "," & GetStringForColumnRange(10,LastDataRow) &"))))"
|
|
objWorksheet.Cells(11, 2 + MAX_NUM_PLAYERS + iSelectedNoPlayers).FormulaArray = strFormula
|
|
|
|
'non-host
|
|
|
|
'current
|
|
strFormula = "AVERAGEIFS(" & GetStringForColumnRange(8,LastDataRow) & "," & GetStringForColumnRange(6,LastDataRow) & ",0," & GetStringForColumnRange(4,LastDataRow) & "," & iSelectedNoPlayers & "," & GetStringForColumnRange(2,LastDataRow) & "," & chr(34) & ">" & DURATION_TO_IGNORE & chr(34) & ")"
|
|
objWorksheet.Cells(13, 2 + MAX_NUM_PLAYERS + iSelectedNoPlayers).Formula = ignore_div_zero(strFormula)
|
|
|
|
'average
|
|
strFormula = "AVERAGEIFS(" & GetStringForColumnRange(9,LastDataRow) & "," & GetStringForColumnRange(6,LastDataRow) & ",0," & GetStringForColumnRange(4,LastDataRow) & "," & iSelectedNoPlayers & "," & GetStringForColumnRange(2,LastDataRow) & "," & chr(34) & ">" & DURATION_TO_IGNORE & chr(34) & ")"
|
|
objWorksheet.Cells(14, 2 + MAX_NUM_PLAYERS + iSelectedNoPlayers).Formula = ignore_div_zero(strFormula)
|
|
|
|
'peak
|
|
strFormula = "AVERAGEIFS(" & GetStringForColumnRange(10,LastDataRow) & "," & GetStringForColumnRange(6,LastDataRow) & ",0," & GetStringForColumnRange(4,LastDataRow) & "," & iSelectedNoPlayers & "," & GetStringForColumnRange(2,LastDataRow) & "," & chr(34) & ">" & DURATION_TO_IGNORE & chr(34) & ")"
|
|
objWorksheet.Cells(15, 2 + MAX_NUM_PLAYERS + iSelectedNoPlayers).Formula = ignore_div_zero(strFormula)
|
|
|
|
'max peak
|
|
strFormula = "=MAX(IF(" & GetStringForColumnRange(6,LastDataRow) & "=0, IF(" & GetStringForColumnRange(2,LastDataRow) & ">" & DURATION_TO_IGNORE & ", IF(" & GetStringForColumnRange(4,LastDataRow) & "=" & iSelectedNoPlayers & "," & GetStringForColumnRange(10,LastDataRow) &"))))"
|
|
objWorksheet.Cells(16, 2 + MAX_NUM_PLAYERS + iSelectedNoPlayers).FormulaArray = strFormula
|
|
|
|
'combined
|
|
|
|
'current
|
|
strFormula = "AVERAGEIFS(" & GetStringForColumnRange(8,LastDataRow) & "," & GetStringForColumnRange(4,LastDataRow) & "," & iSelectedNoPlayers & "," & GetStringForColumnRange(2,LastDataRow) & "," & chr(34) & ">" & DURATION_TO_IGNORE & chr(34) & ")"
|
|
objWorksheet.Cells(18, 2 + MAX_NUM_PLAYERS + iSelectedNoPlayers).Formula = ignore_div_zero(strFormula)
|
|
|
|
'average
|
|
strFormula = "AVERAGEIFS(" & GetStringForColumnRange(9,LastDataRow) & "," & GetStringForColumnRange(4,LastDataRow) & "," & iSelectedNoPlayers & "," & GetStringForColumnRange(2,LastDataRow) & "," & chr(34) & ">" & DURATION_TO_IGNORE & chr(34) & ")"
|
|
objWorksheet.Cells(19, 2 + MAX_NUM_PLAYERS + iSelectedNoPlayers).Formula = ignore_div_zero(strFormula)
|
|
|
|
'peak
|
|
strFormula = "AVERAGEIFS(" & GetStringForColumnRange(10,LastDataRow) & "," & GetStringForColumnRange(4,LastDataRow) & "," & iSelectedNoPlayers & "," & GetStringForColumnRange(2,LastDataRow) & "," & chr(34) & ">" & DURATION_TO_IGNORE & chr(34) & ")"
|
|
objWorksheet.Cells(20, 2 + MAX_NUM_PLAYERS + iSelectedNoPlayers).Formula = ignore_div_zero(strFormula)
|
|
|
|
'max peak
|
|
strFormula = "=MAX(IF(" & GetStringForColumnRange(2,LastDataRow) & ">" & DURATION_TO_IGNORE & ", IF(" & GetStringForColumnRange(4,LastDataRow) & "=" & iSelectedNoPlayers & "," & GetStringForColumnRange(10,LastDataRow) &")))"
|
|
objWorksheet.Cells(21, 2 + MAX_NUM_PLAYERS + iSelectedNoPlayers).FormulaArray = strFormula
|
|
|
|
next
|
|
|
|
end if
|
|
|
|
objWorksheet.Calculate
|
|
|
|
next
|
|
|
|
'delete default worksheets
|
|
objExcel.Application.DisplayAlerts = false
|
|
objWorkbook.WorkSheets("Sheet1").Delete
|
|
objWorkbook.WorkSheets("Sheet2").Delete
|
|
objWorkbook.WorkSheets("Sheet3").Delete
|
|
objExcel.Application.DisplayAlerts = true
|
|
objExcel.Application.Calculation = xlCalculationAutomatic
|
|
objExcel.Application.ScreenUpdating = true
|
|
objWorkbook.Save
|
|
|
|
objExcel.Quit
|
|
|
|
debug_write("FINISHED!! " & " time = " & Time())
|
|
WScript.Echo "Finished processing script_metric_data!"
|
|
|
|
else
|
|
WScript.Echo "script_metric_data was empty!"
|
|
end if
|
|
|
|
|
|
'WScript.Echo "Process Complete - iCountOfScripts = " & iCountOfScripts
|
|
'WScript.quit
|
|
|