#include-once

; #INCLUDES# ====================================================================================================================
#include <Array.au3>
#include "_ArrayNaturalSort.au3" ; Credit to this file goes to Erik Pilsits.
                                 ; If unwanted, search for _ArrayNaturalSort and replace with _ArraySort
; ===============================================================================================================================

; #FUNCTION# ====================================================================================================================
; Name...........: _ArrayGroupBy
; Description ...: Group and aggregate 2D Array columns
; Syntax.........: _ArrayGroupBy(Const ByRef $aArrayInput, $aAggregations)
; Parameters ....: $aArrayInput   - The 2D array to use for grouping and aggregation
;                  $aAggregations - 2D array holding parameters of the aggregation format
;                                   Format: [Column to be grouped/aggregated, Group/aggregation function, Delimiter or ""]
;                                   NOTE 1: The row order in the aggregation parameters will reflect the column order in the final array
;                                   NOTE 2: A column can be used more than once.
;                                   Possible group/aggregation function:
;                                   GROUP ................: No implementation yet, please use GROUP_SORT instead.
;                                   GROUP_SORT ...........: Indicates a column to be used for grouping, sorted.
;                                   CONCAT ...............: Concatenate each item using the provided delimiter.
;                                   CONCAT_SORT ..........: Sort first then concatenate each item using the provided delimiter.
;                                   CONCAT_UNIQUE ........: Concatenate only unique items using the provided delimiter.
;                                   CONCAT_SORT_UNIQUE ...: Sort first then concatenate only unique items using the provided delimiter.
;                                   COUNT ................: Count all items in the group.
;                                   COUNT_UNIQUE .........: Count only unique items in the group.
;                                   FIRST ................: Returns the first entry of the group.
;                                   FIRST_SORT ...........: First sort then returns the first entry of the group.
;                                   LAST .................: Returns the last entry of the group.
;                                   LAST_SORT ............: First sort then returns the last entry of the group.
;                                   SUM ..................: Returns the sum of all numeric items
; Return values .: Success - New grouped/aggregated 2D array
;                  Failure: @error = 0 with @extended set as follows:
;                  | 1 = Incorrectly formatted parameter
;                  | 2 = Must have at least one GROUP parameter
;
; Author ........: Ethan Phoenix
; Modified.......: Nov. 24, 21 - Version 1.0, Initial release
;                  Nov. 24, 21 - Version 1.0.1, Added a few more aggregation function and optimized the code in some section
;                  Sep. 14, 22 - Version 1.0.2, Fixed issue with single row array (would return an unexpected array)
;                  Oct. 02, 22 - Version 1.1.0, Fixed a bug (overlooked a column count)
;												Added quotes cleanup in case of SUM
;                  Nov. 24, 22 - Version 1.2, Fixed variable not resetting issue and overlooked UBound counting rows instead of columns...
; Example .......: 
;	Local $aTestArray[][] = [ _
;								["123", "!!!", "GHI", 10], _
;								["456", "!!!", "DEF", 10], _
;								["456", "@@@", "ABC", 15], _
;								["123", "!!!", "ABC", 10], _
;								["111", "!!!", "DEF", 20], _
;								["123", "$$$", "ABC", 50] _
;							]
;	_ArrayDisplay($aTestArray, "Before _ArrayGroupBy")
;	
;	Local $aAggregations[][] = [ _
;								[2, "GROUP", ""], _
;								[2, "COUNT", ""], _
;								[0, "GROUP", ""], _
;								[1, "CONCAT_UNIQUE", ", "], _
;								[3, "SUM", ""] _
;							   ]
;	
;	_ArrayDisplay(_ArrayGroupBy($aTestArray, $aAggregations), "After _ArrayGroupBy")
; ===============================================================================================================================

Func _ArrayGroupBy(Const ByRef $aArrayInput, $aAggregations)
	If UBound($aArrayInput) > 1 Then
		If UBound($aAggregations) > 0 AND UBound($aAggregations, 2) == 3 Then
			Local $aGroups = _ArrayFindAll($aAggregations, "GROUP", 0, 0, 0, 1, 1)
			If UBound($aGroups) > 0 Then
				Local $aArrayTemp[0][UBound($aArrayInput)]
				For $i=0 To UBound($aAggregations)-1
					$aTempColumn = _ArrayExtract($aArrayInput, -1, -1, $aAggregations[$i][0], $aAggregations[$i][0])
					_ArrayTranspose($aTempColumn)
					_ArrayAdd($aArrayTemp, $aTempColumn)
					$aAggregations[$i][0] = $i
				Next
				_ArrayAdd($aArrayTemp, "")
				_ArrayTranspose($aArrayTemp)
				For $i=0 To UBound($aArrayTemp)-1
					For $i2=0 To UBound($aGroups)-1
						$aArrayTemp[$i][UBound($aArrayTemp, 2)-1] &= $aArrayTemp[$i][$aGroups[$i2]]
					Next
				Next
				$aTempColumn = _ArrayUnique($aArrayTemp, UBound($aArrayTemp, 2)-1, 0, 0, $ARRAYUNIQUE_NOCOUNT)
				_ArrayNaturalSort($aTempColumn)

				Local $aArrayOutput[UBound($aTempColumn)][UBound($aAggregations)]

				For $iGroup=0 To UBound($aTempColumn)-1
					$aFilteredGroup = __ArrayExtractRange($aArrayTemp, _ArrayFindAll($aArrayTemp, $aTempColumn[$iGroup], 0, 0, 0, 0, UBound($aArrayTemp, 2)-1))
					For $iGroupCol=0 To UBound($aGroups)-1
						$aArrayOutput[$iGroup][$aGroups[$iGroupCol]] = $aFilteredGroup[0][$aGroups[$iGroupCol]]
					Next
					For $iAgCount=0 To UBound($aAggregations)-1
						If StringInStr($aAggregations[$iAgCount][1], "GROUP") == 0 Then
							$aCollector = _ArrayExtract($aFilteredGroup, -1, -1, $aAggregations[$iAgCount][0], $aAggregations[$iAgCount][0])
							If StringInStr($aAggregations[$iAgCount][1], "SORT") == 0 Then
								_ArrayNaturalSort($aCollector)
							EndIf
							Local $sAgResults = 0
							Switch $aAggregations[$iAgCount][1]
								Case "CONCAT"
									$sAgResults = _ArrayToString($aCollector, $aAggregations[$iAgCount][2])
								Case "CONCAT_SORT"
									$sAgResults = _ArrayToString($aCollector, $aAggregations[$iAgCount][2])
								Case "CONCAT_UNIQUE"
									$aCollector = _ArrayUnique($aCollector, 0, 0, 0, $ARRAYUNIQUE_NOCOUNT)
									$sAgResults = _ArrayToString($aCollector, $aAggregations[$iAgCount][2])
								Case "CONCAT_SORT_UNIQUE"
									$aCollector = _ArrayUnique($aCollector, 0, 0, 0, $ARRAYUNIQUE_NOCOUNT)
									$sAgResults = _ArrayToString($aCollector, $aAggregations[$iAgCount][2])
								Case "COUNT"
									$sAgResults = UBound($aCollector)
								Case "COUNT_UNIQUE"
									$aCollector = _ArrayUnique($aCollector, 0, 0, 0, $ARRAYUNIQUE_NOCOUNT)
									$sAgResults = UBound($aCollector)
								Case "FIRST"
									$sAgResults = $aCollector[0]
								Case "FIRST_SORT"
									$sAgResults = $aCollector[0]
								Case "LAST"
									$sAgResults = $aCollector[UBound($aCollector)-1]
								Case "LAST_SORT"
									$sAgResults = $aCollector[UBound($aCollector)-1]
								Case "SUM"
									For $sValue In $aCollector
										$sAgResults += Number(StringReplace($sValue, '"', ''))
									Next
							EndSwitch
							$aArrayOutput[$iGroup][$aAggregations[$iAgCount][0]] = $sAgResults
						EndIf
					Next
				Next
				Return $aArrayOutput
			Else
				Return SetError(0, 2, "Must have at least one GROUP parameter")
			EndIf
		Else
			Return SetError(0, 1, "Incorrectly formatted parameter")
		EndIf
	Else
		Return $aArrayInput
	EndIf
EndFunc

; #INTERNAL_USE_ONLY#============================================================================================================
; Name...........: __ArrayExtractRange
; Description ...: Extract range of rows from a given array
; Syntax.........: __ArrayExtractRange(ByRef $aArrayInput, $vRange)
; Parameters ....: $aArrayInput - Array to extract rows from
;                  $vRange      - An array or a string of indices to extract. When a string, using ; as delimiter.
;                                 Both can use hyphen - for spanning range.
; Return values .: A new array 
; Author ........: Ethan Phoenix
; Modified.......: Nov. 24, 21 - Version 1.0, Initial release
; Example .......: __ArrayExtractRange($aArrayInput, "0:2-4")
; ===============================================================================================================================

Func __ArrayExtractRange(Const ByRef $aArrayInput, $vRange)
	Local $aArrayOut[0][UBound($aArrayInput, 2)]
	If IsArray($vRange) <> 1 Then
		$vRange = StringSplit($vRange, ";", $STR_NOCOUNT )
	EndIf
	For $i=0 To UBound($vRange)-1
		$iStart = $vRange[$i]
		$iEnd = $vRange[$i]
		$iIsRange = StringInStr($vRange[$i], "-")
		If $iIsRange <> 0 Then
			$iStart = StringLeft($vRange[$i], $iIsRange-1)
			$iEnd = StringRight($vRange[$i], StringLen($vRange[$i])-$iIsRange)
		EndIf
		_ArrayAdd($aArrayOut, _ArrayExtract($aArrayInput, $iStart, $iEnd))
	Next
	Return $aArrayOut
EndFunc