calcishmakkica: #i101466# implement AVERAGEIF

Merged from Apache OO with adaptions.
http://svn.apache.org/viewvc?rev=1381445&view=rev
Original Apache OO committer: Andrew Rist <arist@apache.org>

Original Author: Marina Plakalovic <makkica@openoffice.org>
Original Author: Eike Rathke [er] <eike.rathke@oracle.com>
Original Committer: Eike Rathke [er] <eike.rathke@oracle.com>

slightly reworked patch by <makkica>

# HG changeset patch
# User Eike Rathke [er] <eike.rathke@oracle.com>
# Date 1284055400 -7200
# Node ID 237cb91dd986ff11eb100cc631206cda102e91f7
# Parent  1c32319bb02fdf173de6a609fca9f2e9c9f4c73a

Change-Id: Id168462272dc8d618513319ebab41fde5c728b08

5 files changed, 443 insertions, 12 deletions

diff --git a/sc/source/core/inc/interpre.hxx b/sc/source/core/inc/interpre.hxx
index 24230dab8aa7..1c408f89ae75 100644
--- a/sc/source/core/inc/interpre.hxx
+++ b/sc/source/core/inc/interpre.hxx
@@ -68,6 +68,12 @@ enum ScIterFunc {
     ifMAX                               // Maximum
 };
 
+enum ScIterFuncIf
+{
+    ifSUMIF,                            // Conditional sum
+    ifAVERAGEIF                         // Conditional average
+};
+
 struct FormulaTokenRef_less
 {
     bool operator () ( const formula::FormulaConstTokenRef& r1, const formula::FormulaConstTokenRef& r2 ) const
@@ -473,8 +479,10 @@ void ScColumn();
 void ScRow();
 void ScTable();
 void ScMatch();
+double IterateParametersIf( ScIterFuncIf );
 void ScCountIf();
 void ScSumIf();
+void ScAverageIf();
 void ScCountEmptyCells();
 void ScLookup();
 void ScHLookup();
diff --git a/sc/source/core/tool/interpr1.cxx b/sc/source/core/tool/interpr1.cxx
index d382152d4a9f..ba3c11f9faa0 100644
--- a/sc/source/core/tool/interpr1.cxx
+++ b/sc/source/core/tool/interpr1.cxx
@@ -5109,6 +5109,386 @@ void ScInterpreter::ScCountEmptyCells()
 }
 
 
+double ScInterpreter::IterateParametersIf( ScIterFuncIf eFunc )
+{
+    sal_uInt8 nParamCount = GetByte();
+    if ( MustHaveParamCount( nParamCount, 2, 3 ) )
+    {
+        SCCOL nCol3 = 0;
+        SCROW nRow3 = 0;
+        SCTAB nTab3 = 0;
+
+        ScMatrixRef pSumExtraMatrix;
+        bool bSumExtraRange = (nParamCount == 3);
+        if (bSumExtraRange)
+        {
+            // Save only the upperleft cell in case of cell range.  The geometry
+            // of the 3rd parameter is taken from the 1st parameter.
+
+            switch ( GetStackType() )
+            {
+                case svDoubleRef :
+                    {
+                        SCCOL nColJunk = 0;
+                        SCROW nRowJunk = 0;
+                        SCTAB nTabJunk = 0;
+                        PopDoubleRef( nCol3, nRow3, nTab3, nColJunk, nRowJunk, nTabJunk );
+                        if ( nTabJunk != nTab3 )
+                        {
+                            SetError( errIllegalParameter);
+                        }
+                    }
+                    break;
+                case svSingleRef :
+                    PopSingleRef( nCol3, nRow3, nTab3 );
+                    break;
+                case svMatrix:
+                    pSumExtraMatrix = PopMatrix();
+                    //! nCol3, nRow3, nTab3 remain 0
+                    break;
+                default:
+                    SetError( errIllegalParameter);
+            }
+        }
+        String rString;
+        double fVal = 0.0;
+        bool bIsString = true;
+        switch ( GetStackType() )
+        {
+            case svDoubleRef :
+            case svSingleRef :
+                {
+                    ScAddress aAdr;
+                    if ( !PopDoubleRefOrSingleRef( aAdr ) )
+                        return 0;
+
+                    ScBaseCell* pCell = GetCell( aAdr );
+                    switch ( GetCellType( pCell ) )
+                    {
+                        case CELLTYPE_VALUE :
+                            fVal = GetCellValue( aAdr, pCell );
+                            bIsString = false;
+                            break;
+                        case CELLTYPE_FORMULA :
+                            if( ((ScFormulaCell*)pCell)->IsValue() )
+                            {
+                                fVal = GetCellValue( aAdr, pCell );
+                                bIsString = false;
+                            }
+                            else
+                                GetCellString(rString, pCell);
+                            break;
+                        case CELLTYPE_STRING :
+                        case CELLTYPE_EDIT :
+                            GetCellString(rString, pCell);
+                            break;
+                        default:
+                            fVal = 0.0;
+                            bIsString = false;
+                    }
+                }
+                break;
+            case svString:
+                rString = GetString();
+                break;
+            case svMatrix :
+                {
+                    ScMatValType nType = GetDoubleOrStringFromMatrix( fVal, rString);
+                    bIsString = ScMatrix::IsNonValueType( nType);
+                }
+                break;
+            default:
+                {
+                    fVal = GetDouble();
+                    bIsString = false;
+                }
+        }
+
+        double fSum = 0.0;
+        double fMem = 0.0;
+        double fRes = 0.0;
+        double fCount = 0.0;
+        bool bNull = true;
+        short nParam = 1;
+        size_t nRefInList = 0;
+        while (nParam-- > 0)
+        {
+            SCCOL nCol1 = 0;
+            SCROW nRow1 = 0;
+            SCTAB nTab1 = 0;
+            SCCOL nCol2 = 0;
+            SCROW nRow2 = 0;
+            SCTAB nTab2 = 0;
+            ScMatrixRef pQueryMatrix;
+            switch ( GetStackType() )
+            {
+                case svRefList :
+                    if (bSumExtraRange)
+                    {
+                        SetError( errIllegalParameter);
+                    }
+                    else
+                    {
+                        ScRange aRange;
+                        PopDoubleRef( aRange, nParam, nRefInList);
+                        aRange.GetVars( nCol1, nRow1, nTab1, nCol2, nRow2, nTab2);
+                    }
+                    break;
+                case svDoubleRef :
+                    PopDoubleRef( nCol1, nRow1, nTab1, nCol2, nRow2, nTab2 );
+                    break;
+                case svSingleRef :
+                    PopSingleRef( nCol1, nRow1, nTab1 );
+                    nCol2 = nCol1;
+                    nRow2 = nRow1;
+                    nTab2 = nTab1;
+                    break;
+                case svMatrix:
+                    {
+                        pQueryMatrix = PopMatrix();
+                        if (!pQueryMatrix)
+                        {
+                            SetError( errIllegalParameter);
+                        }
+                        nCol1 = 0;
+                        nRow1 = 0;
+                        nTab1 = 0;
+                        SCSIZE nC, nR;
+                        pQueryMatrix->GetDimensions( nC, nR);
+                        nCol2 = static_cast<SCCOL>(nC - 1);
+                        nRow2 = static_cast<SCROW>(nR - 1);
+                        nTab2 = 0;
+                    }
+                    break;
+                default:
+                    SetError( errIllegalParameter);
+            }
+            if ( nTab1 != nTab2 )
+            {
+                SetError( errIllegalParameter);
+            }
+
+            if (bSumExtraRange)
+            {
+                // Take the range geometry of the 1st parameter and apply it to
+                // the 3rd. If parts of the resulting range would point outside
+                // the sheet, don't complain but silently ignore and simply cut
+                // them away, this is what Xcl does :-/
+
+                // For the cut-away part we also don't need to determine the
+                // criteria match, so shrink the source range accordingly,
+                // instead of the result range.
+                SCCOL nColDelta = nCol2 - nCol1;
+                SCROW nRowDelta = nRow2 - nRow1;
+                SCCOL nMaxCol;
+                SCROW nMaxRow;
+                if (pSumExtraMatrix)
+                {
+                    SCSIZE nC, nR;
+                    pSumExtraMatrix->GetDimensions( nC, nR);
+                    nMaxCol = static_cast<SCCOL>(nC - 1);
+                    nMaxRow = static_cast<SCROW>(nR - 1);
+                }
+                else
+                {
+                    nMaxCol = MAXCOL;
+                    nMaxRow = MAXROW;
+                }
+                if (nCol3 + nColDelta > nMaxCol)
+                {
+                    SCCOL nNewDelta = nMaxCol - nCol3;
+                    nCol2 = nCol1 + nNewDelta;
+                }
+
+                if (nRow3 + nRowDelta > nMaxRow)
+                {
+                    SCROW nNewDelta = nMaxRow - nRow3;
+                    nRow2 = nRow1 + nNewDelta;
+                }
+            }
+            else
+            {
+                nCol3 = nCol1;
+                nRow3 = nRow1;
+                nTab3 = nTab1;
+            }
+
+            if (nGlobalError == 0)
+            {
+                ScQueryParam rParam;
+                rParam.nRow1       = nRow1;
+                rParam.nRow2       = nRow2;
+
+                ScQueryEntry& rEntry = rParam.GetEntry(0);
+                ScQueryEntry::Item& rItem = rEntry.GetQueryItem();
+                rEntry.bDoQuery = true;
+                if (!bIsString)
+                {
+                    rItem.meType = ScQueryEntry::ByValue;
+                    rItem.mfVal = fVal;
+                    rEntry.eOp = SC_EQUAL;
+                }
+                else
+                {
+                    rParam.FillInExcelSyntax(rString, 0);
+                    sal_uInt32 nIndex = 0;
+                    bool bNumber = pFormatter->IsNumberFormat(
+                            rItem.maString, nIndex, rItem.mfVal);
+                    rItem.meType = bNumber ? ScQueryEntry::ByValue : ScQueryEntry::ByString;
+                    if (rItem.meType == ScQueryEntry::ByString)
+                        rParam.bRegExp = MayBeRegExp(rItem.maString, pDok);
+                }
+                ScAddress aAdr;
+                aAdr.SetTab( nTab3 );
+                rParam.nCol1  = nCol1;
+                rParam.nCol2  = nCol2;
+                rEntry.nField = nCol1;
+                SCsCOL nColDiff = nCol3 - nCol1;
+                SCsROW nRowDiff = nRow3 - nRow1;
+                if (pQueryMatrix)
+                {
+                    // Never case-sensitive.
+                    ScCompareOptions aOptions( pDok, rEntry, rParam.bRegExp);
+                    ScMatrixRef pResultMatrix = QueryMat( pQueryMatrix, aOptions);
+                    if (nGlobalError || !pResultMatrix)
+                    {
+                        SetError( errIllegalParameter);
+                    }
+
+                    if (pSumExtraMatrix)
+                    {
+                        for (SCCOL nCol = nCol1; nCol <= nCol2; ++nCol)
+                        {
+                            for (SCROW nRow = nRow1; nRow <= nRow2; ++nRow)
+                            {
+                                if (pResultMatrix->IsValue( nCol, nRow) && 
+                                        pResultMatrix->GetDouble( nCol, nRow))
+                                {
+                                    SCSIZE nC = nCol + nColDiff;
+                                    SCSIZE nR = nRow + nRowDiff;
+                                    if (pSumExtraMatrix->IsValue( nC, nR))
+                                    {
+                                        fVal = pSumExtraMatrix->GetDouble( nC, nR);
+                                        ++fCount;
+                                        if ( bNull && fVal != 0.0 )
+                                        {
+                                            bNull = false;
+                                            fMem = fVal;
+                                        }
+                                        else
+                                            fSum += fVal;
+                                    }
+                                }
+                            }
+                        }
+                    }
+                    else
+                    {
+                        for (SCCOL nCol = nCol1; nCol <= nCol2; ++nCol)
+                        {
+                            for (SCROW nRow = nRow1; nRow <= nRow2; ++nRow)
+                            {
+                                if (pResultMatrix->GetDouble( nCol, nRow))
+                                {
+                                    aAdr.SetCol( nCol + nColDiff);
+                                    aAdr.SetRow( nRow + nRowDiff);
+                                    ScBaseCell* pCell = GetCell( aAdr );
+                                    if ( HasCellValueData(pCell) )
+                                    {
+                                        fVal = GetCellValue( aAdr, pCell );
+                                        ++fCount;
+                                        if ( bNull && fVal != 0.0 )
+                                        {
+                                            bNull = false;
+                                            fMem = fVal;
+                                        }
+                                        else
+                                            fSum += fVal;
+                                    }
+                                }
+                            }
+                        }
+                    }
+                }
+                else
+                {
+                    ScQueryCellIterator aCellIter(pDok, nTab1, rParam, false);
+                    // Increment Entry.nField in iterator when switching to next column.
+                    aCellIter.SetAdvanceQueryParamEntryField( true );
+                    if ( aCellIter.GetFirst() )
+                    {
+                        if (pSumExtraMatrix)
+                        {
+                            do
+                            {
+                                SCSIZE nC = aCellIter.GetCol() + nColDiff;
+                                SCSIZE nR = aCellIter.GetRow() + nRowDiff;
+                                if (pSumExtraMatrix->IsValue( nC, nR))
+                                {
+                                    fVal = pSumExtraMatrix->GetDouble( nC, nR);
+                                    ++fCount;
+                                    if ( bNull && fVal != 0.0 )
+                                    {
+                                        bNull = false;
+                                        fMem = fVal;
+                                    }
+                                    else
+                                        fSum += fVal;
+                                }
+                            } while ( aCellIter.GetNext() );
+                        }
+                        else
+                        {
+                            do
+                            {
+                                aAdr.SetCol( aCellIter.GetCol() + nColDiff);
+                                aAdr.SetRow( aCellIter.GetRow() + nRowDiff);
+                                ScBaseCell* pCell = GetCell( aAdr );
+                                if ( HasCellValueData(pCell) )
+                                {
+                                    fVal = GetCellValue( aAdr, pCell );
+                                    ++fCount;
+                                    if ( bNull && fVal != 0.0 )
+                                    {
+                                        bNull = false;
+                                        fMem = fVal;
+                                    }
+                                    else
+                                        fSum += fVal;
+                                }
+                            } while ( aCellIter.GetNext() );
+                        }
+                    }
+                }
+            }
+            else
+            {
+                SetError( errIllegalParameter);
+            }
+        }
+
+        switch( eFunc )
+        {
+            case ifSUMIF:     fRes = ::rtl::math::approxAdd( fSum, fMem ); break;
+            case ifAVERAGEIF: fRes = div( ::rtl::math::approxAdd( fSum, fMem ), fCount); break;
+        }
+        return fRes;
+    }
+    return 0;
+}
+
+void ScInterpreter::ScSumIf()
+{
+    RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScSumIf" );
+    PushDouble( IterateParametersIf( ifSUMIF));
+}
+
+void ScInterpreter::ScAverageIf()
+{
+    RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "makkica", "ScInterpreter::ScAverageIf" );
+    PushDouble( IterateParametersIf( ifAVERAGEIF));
+}
+
 void ScInterpreter::ScCountIf()
 {
     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScCountIf" );
@@ -5172,7 +5552,7 @@ void ScInterpreter::ScCountIf()
                 bIsString = false;
             }
         }
-        double fSum = 0.0;
+        double fCount = 0.0;
         short nParam = 1;
         size_t nRefInList = 0;
         while (nParam-- > 0)
@@ -5278,7 +5658,7 @@ void ScInterpreter::ScCountIf()
                     {
                         if (pResultMatrix->IsValue( nIndex) &&
                                 pResultMatrix->GetDouble( nIndex))
-                            ++fSum;
+                            ++fCount;
                     }
                 }
                 else
@@ -5290,7 +5670,7 @@ void ScInterpreter::ScCountIf()
                     {
                         do
                         {
-                            fSum++;
+                            fCount++;
                         } while ( aCellIter.GetNext() );
                     }
                 }
@@ -5301,11 +5681,14 @@ void ScInterpreter::ScCountIf()
                 return;
             }
         }
-        PushDouble(fSum);
+        PushDouble(fCount);
     }
 }
 
 
+/* FIXME: kept for reference to properly adapt the new IterateParametersIf() to 
+ * current code after merge commit */
+#if 0
 void ScInterpreter::ScSumIf()
 {
     sal_uInt8 nParamCount = GetByte();
@@ -5463,8 +5846,7 @@ void ScInterpreter::ScSumIf()
             case svRefList :
                 if (bSumExtraRange)
                 {
-                    PushIllegalParameter();
-                    return;
+                        SetError( errIllegalParameter);
                 }
                 else
                 {
@@ -5503,13 +5885,11 @@ void ScInterpreter::ScSumIf()
             }
             break;
             default:
-                PushIllegalParameter();
-                return ;
+                    SetError( errIllegalParameter);
         }
         if ( nTab1 != nTab2 )
         {
-            PushIllegalArgument();
-            return;
+                SetError( errIllegalParameter);
         }
 
         if (bSumExtraRange)
@@ -5596,8 +5976,7 @@ void ScInterpreter::ScSumIf()
                 ScMatrixRef pResultMatrix = QueryMat( pQueryMatrix, aOptions);
                 if (nGlobalError || !pResultMatrix)
                 {
-                    PushIllegalParameter();
-                    return;
+                        SetError( errIllegalParameter);
                 }
 
                 if (pSumExtraMatrix)
@@ -5710,6 +6089,7 @@ void ScInterpreter::ScSumIf()
     }
     PushDouble( ::rtl::math::approxAdd( fSum, fMem ) );
 }
+#endif
 
 
 void ScInterpreter::ScLookup()
diff --git a/sc/source/core/tool/interpr4.cxx b/sc/source/core/tool/interpr4.cxx
index 4ab243fa5e92..e68426c93311 100644
--- a/sc/source/core/tool/interpr4.cxx
+++ b/sc/source/core/tool/interpr4.cxx
@@ -4054,6 +4054,7 @@ StackVar ScInterpreter::Interpret()
                 case ocCountEmptyCells  : ScCountEmptyCells();          break;
                 case ocCountIf          : ScCountIf();                  break;
                 case ocSumIf            : ScSumIf();                    break;
+                case ocAverageIf        : ScAverageIf();                break;
                 case ocLookup           : ScLookup();                   break;
                 case ocVLookup          : ScVLookup();                  break;
                 case ocHLookup          : ScHLookup();                  break;
diff --git a/sc/source/core/tool/parclass.cxx b/sc/source/core/tool/parclass.cxx
index 083eb531f658..bb3b1347c413 100644
--- a/sc/source/core/tool/parclass.cxx
+++ b/sc/source/core/tool/parclass.cxx
@@ -166,6 +166,7 @@ const ScParameterClassification::RawData ScParameterClassification::pRawData[] =
     { ocSubTotal,        {{ Value, Reference                                     }, true }},
     { ocSum,             {{ Reference                                            }, true }},
     { ocSumIf,           {{ Reference, Value, Reference                          }, false }},
+    { ocAverageIf,       {{ Reference, Value, Reference                          }, false }},
     { ocSumProduct,      {{ ForceArray                                           }, true }},
     { ocSumSQ,           {{ Reference                                            }, true }},
     { ocSumX2MY2,        {{ ForceArray, ForceArray                               }, false }},
diff --git a/sc/source/ui/src/scfuncs.src b/sc/source/ui/src/scfuncs.src
index bb32178cc793..e7f986d28a8b 100644
--- a/sc/source/ui/src/scfuncs.src
+++ b/sc/source/ui/src/scfuncs.src
@@ -2929,6 +2929,47 @@ Resource RID_SC_FUNCTION_DESCRIPTIONS1
             Text [ en-US ] = "The range from which the values are to be totalled." ;
         };
     };
+
+	// -=*# Resource for function AVERAGEIF #*=-
+	Resource SC_OPCODE_AVERAGE_IF
+	{
+		String 1 // Description
+		{
+			Text [ en-US ] = "Averages the arguments that meet the conditions." ;
+		};
+		ExtraData =
+		{
+            0;
+			ID_FUNCTION_GRP_MATH;
+			U2S( HID_FUNC_AVERAGEIF );
+			3;	0;	0;	1;
+            0;
+		};
+		String 2 // Name of Parameter 1
+		{
+			Text [ en-US ] = "range" ;
+		};
+		String 3 // Description of Parameter 1
+		{
+			Text [ en-US ] = "The range to be evaluated by the criteria given." ;
+		};
+		String 4 // Name of Parameter 2
+		{
+			Text [ en-US ] = "criteria" ;
+		};
+		String 5 // Description of Parameter 2
+		{
+			Text [ en-US ] = "The cell range in which the search criteria are given." ;
+		};
+		String 6 // Name of Parameter 3
+		{
+			Text [ en-US ] = "average_range" ;
+		};
+		String 7 // Description of Parameter 3
+		{
+			Text [ en-US ] = "The range from which the values are to be averaged." ;
+		};
+	};
      // -=*# Resource for function ZÄHLENWENN #*=-
     Resource SC_OPCODE_COUNT_IF
     {