;========================================================================================================================================== ; ; How to use udsArrMap to aggregate amounts? ; ; How to create a report with aggregated amounts per group ; by utilizing the user defined subroutine "udsArrMap". ; ;========================================================================================================================================== ; ; Following example demonstrates how to build a condensed list of "sums per item" ; by using WinBatch User Defined Functions and SubRoutines. ; ; This example uses the WinBatch array features. ; Because arrays resides entirely in the PC's memory, ; the amount of source data should be rational low. ; ; This example uses following "User Defined Functions" resp. "User Defined SubRoutines": ; ; udsArrMap (__strCallback, __strArrayList, __strArrayListSep) ; udfArrItemLocate (arrArray, anyItem) ; udfArrUnique (arrArray, intSortMode, intSortDirection) ; udfArrayFromFile (strFilename, intBaseMode) ; The Binary Buffer version. ; ... replaced by WB native function ArrayFileGet. ; udfIsValidArray (arrArray) ; udfFileCreateTemp (strPrefix, strExtension) ; udfStrQuote (strString, strLeft, strRight) ; ; This example uses the "udsArrMap" subroutine extensively. ; The UDS "udsArrMap" calls the following routines, ; which are small and simple UDF resp. UDS: ; ; cbExtractUser (strRow) ; cbExtractValue (strRow) ; cbSumPerUser (strUser, intValue) ; cbBuildSumList (strUser, intValue) ; cbFileWriteSum (strUser, intValue) ; ;------------------------------------------------------------------------------------------------------------------------------------------; ; Detlev Dalitz.20020822.20090522.20100122. ;========================================================================================================================================== ;========================================================================================================================================== ;------------------------------------------------------------------------------------------------------------------------------------------ #DefineFunction udfFileCreateTemp (strPrefix, strExtension) strFilenameTemp = FileCreateTemp (strPrefix) strFilename = strFilenameTemp If strExtension != "" Then strFilename = ItemReplace (strExtension, -1, strFilenameTemp, ".") blnResult = FileRename (strFilenameTemp, strFilename) Return strFilename ;.......................................................................................................................................... ; This UDF "udfFileCreateTemp" creates a 0-byte file with unique name in the user's temporary folder ; (as specified by the "TMP" or "TEMP" environment variable). ; ; The prefix string can be set by parameter strPrefix and will be truncated to 3 chars. ; The file extension string can be set by parameter strExtension. ; If strExtension is empty, then the file extension is set to "tmp". ; ; The WinBatch FileCreateTemp function can create maximal 65535 temporary files ; of the form "hexnumber.tmp" from "1.tmp" to "FFFF.tmp". ; One more attempt will create WB error 1653. ; ; Detlev Dalitz.20090521. ;.......................................................................................................................................... #EndFunction ;------------------------------------------------------------------------------------------------------------------------------------------ ;------------------------------------------------------------------------------------------------------------------------------------------ #DefineFunction udfIsValidArray (arrArray) Return ArrInfo (arrArray, -1) && !!ArrInfo (arrArray, 0); Is Variable of type array AND has dim>0? ;------------------------------------------------------------------------------------------------------------------------------------------ ; This Function "udfIsValidArray" returns a boolean value, ; which indicates if the given variable is a valid usable array. ; ; Detlev Dalitz.20020809.20090522. ;------------------------------------------------------------------------------------------------------------------------------------------ #EndFunction ;------------------------------------------------------------------------------------------------------------------------------------------ ;------------------------------------------------------------------------------------------------------------------------------------------ #DefineFunction udfArrItemLocate (arrArray, anyItem) If !ArrInfo (arrArray, -1) Then Return -1 ; No array. If !ArrInfo (arrArray, 6) Then Return -1 ; No elements. If ArrInfo (arrArray, 0) > 1 Then Return -1 ; Too much dimensions. intTop = Max (0, ArrInfo (arrArray, 1) - 1) intBot = 0 While (intTop - intBot - 1) intMid = (intTop + intBot) / 2 If anyItem > arrArray [intMid] Then intBot = intMid Else intTop = intMid EndWhile If arrArray [intTop] == anyItem Then Return intTop If arrArray [intBot] == anyItem Then Return intBot Return -1 ;.......................................................................................................................................... ; This UDF "udfArrItemLocate" uses the binary search algorithm ; to locate a given item in a given ascending sorted array. ; The function returns the index number of the found element, ; or returns -1 if the item was not found or the given array does not fit. ; ; The algorithm needs an ascending sorted array. ; ; Detlev Dalitz.20020821.20090507.20090522. ;.......................................................................................................................................... #EndFunction ;------------------------------------------------------------------------------------------------------------------------------------------ ;------------------------------------------------------------------------------------------------------------------------------------------ #DefineFunction udfStrQuote (strString, strLeft, strRight) If "" == strLeft If "" == strRight strQuoteChars = """'`" strQuotes = StrClean (strString, strQuoteChars, "", @TRUE, 2) If "" == StrClean (strQuoteChars, strQuotes, "", @TRUE, 1) strQuoteChar = '"' strString = StrReplace (strString, strQuoteChar, StrFill (strQuoteChar, 2)) Else strQuotes = StrClean (strQuoteChars, strQuotes, "", @TRUE, 1) strQuoteChar = StrSub (strQuotes, 1, 1) EndIf strLeft = strQuoteChar strRight = strQuoteChar EndIf EndIf Return strLeft : strString : strRight ;------------------------------------------------------------------------------------------------------------------------------------------ ; With strLeft = "" and strRight = "" ; the function chooses a winbatch quote delimiter automagically ; and doubles the quotation char in strString if necessary. ; ; With strLeft = """" and strRight = """" ; the function allows quotation without doubling of quotation char in strString. ; (Note: """" is the same as '"'.) ; ; With strLeft = "(* " and strRight = " *)" ; the function encloses strString in pairs of pascal comments. ; ; With strLeft = "/* " and strRight = " */" ; the function encloses strString in pairs of C comments. ; ; DD.20010722.20020628.20090427. ;------------------------------------------------------------------------------------------------------------------------------------------ #EndFunction ;------------------------------------------------------------------------------------------------------------------------------------------ ;------------------------------------------------------------------------------------------------------------------------------------------ #DefineSubRoutine udsArrMap (__strCallback, __strArrayList, __strArrayListSep) If __strArrayListSep == "" Then __strArrayListSep = @TAB __ = ArrDimension (0) __intParamLow = 1 __intParamHigh = ItemCount (__strArrayList, __strArrayListSep) ; If no items in list then return dim0 array with no element. If __intParamHigh < __intParamLow Then Return ArrDimension (0) For __intParam = __intParamLow To __intParamHigh __arrA%__intParam% = ItemExtract (__intParam, __strArrayList, __strArrayListSep) ; If the extracted array name is an empty string, then treat it as an empty array. If __arrA%__intParam% == "" Then __arrA%__intParam% = "__" __arrA = __arrA%__intParam% ; If the extracted array name points not to an array then return dim0 array with no element. If !ArrInfo (%__arrA%, -1) Then Return ArrDimension (0) ; If there is an array with greater than 1 dimension then return dim0 array with no element. If ArrInfo (%__arrA%, 0) > 1 Then Return ArrDimension (0) Next If __strCallback > "" __arrA = __arrA%__intParamLow% __intElementCount = ArrInfo (%__arrA%, 1) For __intParam = 1 + __intParamLow To __intParamHigh __arrA = __arrA%__intParam% __intElementCount = Min (__intElementCount, ArrInfo (%__arrA%, 1)) Next Drop (_) _ = ArrDimension (__intElementCount) __intNewLow = 0 __intNewHigh = __intElementCount - 1 For __intNew = __intNewLow To __intNewHigh __strParamList = "" For __intParam = __intParamLow To __intParamHigh __arrA = __arrA%__intParam% If VarType (%__arrA% [__intNew]) == 2 ; If string, which may contain comma, then enclose in quotes. __strParamList = ItemInsert (udfStrQuote (%__arrA% [__intNew], "", ""), -1, __strParamList, ",") Else __strParamList = ItemInsert (%__arrA% [__intNew], -1, __strParamList, ",") EndIf Next _ [__intNew] = %__strCallback% (%__strParamList%) Next Else __intElementCount = 0 For __intParam = __intParamLow To __intParamHigh __arrA = __arrA%__intParam% __intElement = ArrInfo (%__arrA%, 1) __intElementCount = Max (__intElementCount, __intElement) __int%__arrA%High = __intElement - 1 Next Drop (_) _ = ArrDimension (__intElementCount, __intParamHigh) __intNewLow = 0 __intNewHigh = __intElementCount - 1 For __intNew = __intNewLow To __intNewHigh For __intParam = __intParamLow To __intParamHigh __arrA = __arrA%__intParam% If __intNew <= __int%__arrA%High If VarType (%__arrA% [__intNew]) _ [__intNew, __intParam - 1] = %__arrA% [__intNew] EndIf EndIf Next Next EndIf DropWild ("__*") Return _ ;------------------------------------------------------------------------------------------------------------------------------------------ ; Sorry, this code looks so ugly because of the "__" prefixes. ; At this time there is no better way known in WinBatch to get rid of "local" variables defined in a user defined subroutine. ;------------------------------------------------------------------------------------------------------------------------------------------ ; This UDS subroutine works in two ways: ; 1. The subroutine "udsArrMap" calls a user defined function or subroutine given by parameter 'strCallback' ; and calls the callback routine with a parameterlist built from defined array elements, ; which are extracted from one ore more arrays given by parameter 'strArrayList'. ; ; The "udsArrMap" subroutine returns a dim1 array containing the results of the callback routine. ; If "udsArrMap" detects an exception to its inner rules, it will return a dim0 array with no element, ; which has to be checked by the caller, for example: "If ArrInfo (arrArray, 0) == 0 Then ...". ; Note: The callback routine will be called as much as the smallest dim1 array contains defined elements. ; ; 2. If parameter 'strCallback' is an empty string, then the one or more dim1 arrays given by parameter 'strArrayList' ; will be combined into a dim2 array. ; The "udsArrMap" subroutine returns a dim2 array, that has as much number of rows as the largest dim2 array given, ; and the number of 'columns' is defined by the given number of items in the arraylist parameter. ; If arraylist contains an 'empty' item, then an empty column with undefined elements will be inserted. ; ; Detlev Dalitz.20020809.20020821.20090521. ;------------------------------------------------------------------------------------------------------------------------------------------ #EndSubRoutine ;------------------------------------------------------------------------------------------------------------------------------------------ ;------------------------------------------------------------------------------------------------------------------------------------------ #DefineFunction udfArrayFromFile (strFilename, intBaseMode) ; The Binary Buffer version. If strFilename == "" Then Return ArrDimension (0) intBBSize = FileSize (strFilename) If !intBBSize Then Return ArrDimension (0) intBaseMode = !!intBaseMode hdlBB = BinaryAlloc (intBBSize + intBaseMode) If intBaseMode Then BinaryPokeStr (hdlBB, 0, @LF) ; Insert a leading empty line. BinaryReadEx (hdlBB, intBaseMode, strFilename, 0, -1) ; Read the whole file. BinaryReplace (hdlBB, @CRLF, @LF, @TRUE) ; Unify EOL. BinaryReplace (hdlBB, @CR, " ", @TRUE) ; Replace lonesome CR's with spaces. intBBEod = BinaryEodGet (hdlBB) strString = BinaryPeekStr (hdlBB, 0, intBBEod - (@LF == BinaryPeekStr (hdlBB, intBBEod - 1, 1))) ; Omit trailing @LF. hdlBB = BinaryFree (hdlBB) arrArray = Arrayize (strString, @LF) If intBaseMode Then arrArray [0] = ArrInfo (arrArray, 1) - 1 ; If one based array, then poke number of file lines into array element [0]. Return arrArray ;------------------------------------------------------------------------------------------------------------------------------------------ ; This UDF "udfArrayFromFile" reads a textfile and returns a dim1 array. ; Each array element contains one line of the given input file, with EndOfLine characters stripped off. ; ; The intBaseMode parameter controls the creation of a zero based or a one based array. ; The array contains n elements (zero based) resp. n+1 elements (one based), with n = Number of file lines. ; After returning from this function the number of file lines read can be retrieved ; by 'LineCount = Array [0]' (one based array) ; or 'LineCount = ArrInfo (Array, 1)' (zero based array). ; ; If the specified Filename is empty or the FileSize is zero, then this function returns a valid ; dim0 array but with no element. This result has to be checked by the caller for further processing. ; ; strFilename ..... The File to be read into the array. ; intBaseMode=0 ... Creates a zero based array with n elements. ; intBaseMode=1 ... Creates a one based array with n+1 elements. ; ; Detlev Dalitz.20020808.20090510. ;------------------------------------------------------------------------------------------------------------------------------------------ #EndFunction ;------------------------------------------------------------------------------------------------------------------------------------------ ;------------------------------------------------------------------------------------------------------------------------------------------ #DefineFunction udfArrUnique (arrArray, intSortMode, intSortDirection) If !ArrInfo (arrArray, -1) Then Return ArrDimension (0) ; No array, return dim0 array with no element. If ArrInfo (arrArray, 0) > 1 Then Return ArrDimension (0) ; Too much dimensions, return dim0 array with no element. If !ArrInfo (arrArray, 6) Then Return ArrDimension (0) ; No elements, return dim0 array with no element. strDelimiter = Num2Char (7) ; Surrogate, assuming that ASCII-7 'bell' control character does not occur in array data! strItemList = "" intListLow = 1 intListHigh = ArrInfo (arrArray, 1) intArrLow = 0 intArrHigh = intListHigh - 1 For intElem = intArrLow To intArrHigh If !!VarType (arrArray [intElem]) strItemList = strItemList : strDelimiter : arrArray [intElem] Else strItemList = strItemList : strDelimiter EndIf Next strItemList = StrSub (strItemList, 2, -1) strListUnique = "" For intElem = intListLow To intListHigh strItem = ItemExtract (intElem, strItemList, strDelimiter) If strItem != "" Then If !ItemLocate (strItem, strListUnique, strDelimiter) Then strListUnique = ItemInsert (strItem, -1, strListUnique, strDelimiter) Next Switch intSortMode Case @SORTED Switch intSortDirection Case @ASCENDING strListUnique = ItemSort (strListUnique, strDelimiter) Break Case @DESCENDING strListUnique = ItemSort (strListUnique, strDelimiter) intListHigh = ItemCount (strListUnique, strDelimiter) - 1 For intElem = intListHigh To intListLow By -1 strListUnique = ItemRemove (intElem, ItemInsert (ItemExtract (intElem, strListUnique, strDelimiter), -1, strListUnique, strDelimiter), strDelimiter) Next Break EndSwitch Break Case @UNSORTED Break EndSwitch Return Arrayize (strListUnique, strDelimiter) ;.......................................................................................................................................... ; This UDF "udfArrUnique" removes double entries from a given dim1 array and returns a new dim1 array. ; If the input array parameter does not fit to process, then the function returns a dim0 array with no element, ; which must be checked by the caller. ; ; intSortMode = @UNSORTED .......... Returns the unique array as is. ; intSortMode = @SORTED ............ Returns the unique array sorted. ; intSortDirection = @ASCENDING .... Performs an ascending 'word' sorting (using WinBatch function ItemSort). ; intSortDirection = @DESCENDING ... Performs a descending 'word' sorting (using WinBatch function ItemSort). ; ; Note: ; This UDF uses the ASCII-7 'bell' control character as a delimiter to build a temporary itemlist. ; Therefore make sure, that your array elements do not contain an ASCII-7 character, ; or define some other 'strange' ASCII character as delimiter. ; ; Detlev Dalitz.200200820.20090507.20090522. ;.......................................................................................................................................... #EndFunction ;------------------------------------------------------------------------------------------------------------------------------------------ ;========================================================================================================================================== ;========================================================================================================================================== ;------------------------------------------------------------------------------------------------------------------------------------------ ; The Callback Routines ;------------------------------------------------------------------------------------------------------------------------------------------ #DefineFunction cbExtractUser (strRow) Return ItemExtract (1, strRow, ",") #EndFunction ;------------------------------------------------------------------------------------------------------------------------------------------ #DefineFunction cbExtractValue (strRow) ; Additionally convert string type to integer. Return Int ("0" : ItemExtract (2, strRow, ",")) #EndFunction ;------------------------------------------------------------------------------------------------------------------------------------------ #DefineSubRoutine cbSumPerUser (strUser, intValue) intPos = udfArrItemLocate (arrUserUnique, strUser) If intPos >= 0 Then arrUserSum[intPos] = arrUserSum[intPos] + intValue #EndSubRoutine ;------------------------------------------------------------------------------------------------------------------------------------------ #DefineSubRoutine cbBuildSumList (strUser, intValue) strSumList = ItemInsert (strUser : "," : intValue, -1, strSumList, @LF) #EndSubRoutine ; ----------------------------------------------------------------------------------------------------------------------------------------- #DefineSubRoutine cbFileWriteSum (strUser, intValue) FileWrite (hdlFW, strUser : "," : intValue) #EndSubRoutine ; ----------------------------------------------------------------------------------------------------------------------------------------- ;========================================================================================================================================== ; Test. ; We have a comma delimited file that contains information about different users, ; such as how many minutes they have been online, etc. ; What we want to do is adding up all online minutes per user (GroupBy). ; Create the input data csv file. ; user3,14,....more stuff ; user1,30,....more stuff ; user2,10,....more stuff ; user3,17,....more stuff ; user1,25,....more stuff ; user3,7,....more stuff strData = "user3,14,....more stuff" : @CRLF strData = strData : "user1,30,....more stuff" : @CRLF strData = strData : "user2,10,....more stuff" : @CRLF strData = strData : "user3,17,....more stuff" : @CRLF strData = strData : "user1,25,....more stuff" : @CRLF strData = strData : "user3,7,....more stuff" : @CRLF strFilenameIn = udfFileCreateTemp ("WBT", "txt") intResult = FilePut (strFilenameIn, strData) ; Should become ... ; user1,55 ; user2,10 ; user3,38 strFilenameOut = udfFileCreateTemp ("WBT", "txt") ; Get the complete file into an array. ;arrData = udfArrayFromFile (strFilenameIn, 0) arrData = ArrayFileGet (strFilenameIn) ; This line needs WinBatch DLL 4.0gda. First showing up in WB 2003G. Terminate (!udfIsValidArray (arrData), "Demo aborted.", "Error while loading file into array:" : @LF : strFilenameIn) ; Extract the columns we need. arrUser = udsArrMap ("cbExtractUser", "arrData", "") arrValue = udsArrMap ("cbExtractValue", "arrData", "") Drop (_, arrData) ; Make the group elements unique, and sort ascending. arrUserUnique = udfArrUnique (arrUser, @SORTED, @ASCENDING) intUserUniqueLow = 0 intUserUniqueHigh = Max (0, ArrInfo (arrUserUnique, 1) - 1) ; Create an empty array for holding user related sums. arrUserSum = ArrDimension (ArrInfo (arrUserUnique, 1)) ArrInitialize (arrUserSum, 0) ; Do the calculation. udsArrMap ("cbSumPerUser", "arrUser,arrValue", ",") Drop (_, arrUser, arrValue) ; Write the result to diskfile. hdlFW = FileOpen (strFilenameOut, "WRITE") udsArrMap ("cbFileWriteSum", "arrUserUnique,arrUserSum", ",") Drop (_) FileClose (hdlFW) ; View output file. hdlWinId = WinItemProcId (RunShell (strFilenameOut, "", "", @ZOOMED, @GETPROCID), 0, 0) ; Ready. ; Just for the demo ... strSumList = "" udsArrMap ("cbBuildSumList", "arrUserUnique,arrUserSum", ",") Drop (_) Drop (arrUserUnique, arrUserSum) strMsgTitle = "Demo: Grouped Summing with udsArrMap" strMsgText = strSumList IntControl (28, 1, 0, 0, 0) IntControl (63, 200, 100, 800, 600) AskItemlist (strMsgTitle, strMsgText, @LF, @UNSORTED, @SINGLE) :CANCEL blnResult = FileDelete (strFilenameIn) blnResult = FileDelete (strFilenameOut) If WinExist (hdlWinId) Then WinClose (hdlWinId) Exit ;==========================================================================================================================================