#i102750# supported external db range for DSUM for by-value query entries.

5 files changed, 251 insertions, 53 deletions

diff --git a/sc/inc/dociter.hxx b/sc/inc/dociter.hxx
index 33ecb84e4fcf..b52228081b83 100644
--- a/sc/inc/dociter.hxx
+++ b/sc/inc/dociter.hxx
@@ -179,7 +179,12 @@ private:
         virtual bool getNext(double &rValue, USHORT &rErr);
 
     private:
+        bool isValidQuery(SCROW mnRow, const ScMatrix& rMat) const;
+
         const ScDBQueryParamMatrix* mpParam;
+        SCROW mnCurRow;
+        SCROW mnRows;
+        SCCOL mnCols;
     };
 
     ::std::auto_ptr<ScDBQueryParamBase> mpParam;
diff --git a/sc/source/core/data/dociter.cxx b/sc/source/core/data/dociter.cxx
index 7434588f093c..b7aade1f0be7 100644
--- a/sc/source/core/data/dociter.cxx
+++ b/sc/source/core/data/dociter.cxx
@@ -47,6 +47,43 @@
 #include "docoptio.hxx"
 #include "cellform.hxx"
 
+using ::rtl::math::approxEqual;
+
+#include <stdio.h>
+#include <string>
+#include <sys/time.h>
+
+namespace {
+
+class StackPrinter
+{
+public:
+    explicit StackPrinter(const char* msg) :
+        msMsg(msg)
+    {
+        fprintf(stdout, "%s: --begin\n", msMsg.c_str());
+        mfStartTime = getTime();
+    }
+
+    ~StackPrinter()
+    {
+        double fEndTime = getTime();
+        fprintf(stdout, "%s: --end (duration: %g sec)\n", msMsg.c_str(), (fEndTime-mfStartTime));
+    }
+
+private:
+    double getTime() const
+    {
+        timeval tv;
+        gettimeofday(&tv, NULL);
+        return tv.tv_sec + tv.tv_usec / 1000000.0;
+    }
+
+    ::std::string msMsg;
+    double mfStartTime;
+};
+
+}
 
 // STATIC DATA -----------------------------------------------------------
 
@@ -625,27 +662,156 @@ bool ScDBQueryValueIterator::DataAccessInternal::getNext(double& rValue, USHORT&
 ScDBQueryValueIterator::DataAccessMatrix::DataAccessMatrix(const ScDBQueryParamMatrix* pParam) :
     mpParam(pParam)
 {
+    SCSIZE nC, nR;
+    mpParam->mpMatrix->GetDimensions(nC, nR);
+    mnRows = static_cast<SCROW>(nR);
+    mnCols = static_cast<SCCOL>(nC);
 }
 
 ScDBQueryValueIterator::DataAccessMatrix::~DataAccessMatrix()
 {
 }
 
-bool ScDBQueryValueIterator::DataAccessMatrix::getCurrent(double& /*rValue*/, USHORT& /*rErr*/)
+bool ScDBQueryValueIterator::DataAccessMatrix::getCurrent(double& rValue, USHORT& rErr)
 {
+    rErr = 0; // There is never a cell error in matrix backends.
+
+    // Starting from row == mnCurRow, get the first row that satisfies all the
+    // query parameters.
+    for ( ;mnCurRow < mnRows; ++mnCurRow)
+    {
+        const ScMatrix& rMat = *mpParam->mpMatrix;
+        if (isValidQuery(mnCurRow, rMat))
+        {
+            rValue = rMat.GetDouble(mpParam->mnField, mnCurRow);
+            return true;
+        }
+    }
     return false;
 }
 
-bool ScDBQueryValueIterator::DataAccessMatrix::getFirst(double& /*rValue*/, USHORT& /*rErr*/)
+bool ScDBQueryValueIterator::DataAccessMatrix::getFirst(double& rValue, USHORT& rErr)
 {
-    return false;
+    mnCurRow = 1;
+    return getCurrent(rValue, rErr);
+}
+
+bool ScDBQueryValueIterator::DataAccessMatrix::getNext(double& rValue, USHORT& rErr)
+{
+    ++mnCurRow;
+    return getCurrent(rValue, rErr);
+}
+
+namespace {
+
+bool lcl_isQueryByValue(const ScQueryEntry& rEntry, const ScMatrix& rMat, SCSIZE nCol, SCSIZE nRow)
+{
+    if (rEntry.bQueryByString)
+        return false;
+
+    if (!rMat.IsValueOrEmpty(nCol, nRow))
+        return false;
+
+    return true;
 }
 
-bool ScDBQueryValueIterator::DataAccessMatrix::getNext(double& /*rValue*/, USHORT& /*rErr*/)
+bool lcl_isQueryByString(const ScQueryEntry& rEntry, const ScMatrix& rMat, SCSIZE nCol, SCSIZE nRow)
 {
+    switch (rEntry.eOp)
+    {
+        case SC_EQUAL:
+        case SC_NOT_EQUAL:
+        case SC_CONTAINS:
+        case SC_DOES_NOT_CONTAIN:
+        case SC_BEGINS_WITH:
+        case SC_ENDS_WITH:
+        case SC_DOES_NOT_BEGIN_WITH:
+        case SC_DOES_NOT_END_WITH:
+            return true;
+        default:
+            ;
+    }
+
+    if (rEntry.bQueryByString && rMat.IsString(nCol, nRow))
+        return true;
+
     return false;
 }
 
+}
+
+bool ScDBQueryValueIterator::DataAccessMatrix::isValidQuery(SCROW nRow, const ScMatrix& rMat) const
+{
+    StackPrinter __stack_printer__("ScDBQueryValueIterator:DataAccessMatrix::isValidQuery");
+    SCSIZE nEntryCount = mpParam->GetEntryCount();
+    bool bEverValid = false;
+    for (SCSIZE i = 0; i < nEntryCount; ++i)
+    {
+        const ScQueryEntry& rEntry = mpParam->GetEntry(i);
+        if (!rEntry.bDoQuery)
+            continue;
+
+        bool bValid = false;
+
+        SCSIZE nField = static_cast<SCSIZE>(rEntry.nField);
+        if (lcl_isQueryByValue(rEntry, rMat, nField, nRow))
+        {
+            // By value
+            fprintf(stdout, "ScDBQueryValueIterator:DataAccessMatrix::isValidQuery:   by value\n");
+            double fMatVal = rMat.GetDouble(nField, nRow);
+            bool bEqual = approxEqual(fMatVal, rEntry.nVal);
+            switch (rEntry.eOp)
+            {
+                case SC_EQUAL:
+                    bValid = bEqual;
+                break;
+                case SC_LESS:
+                    bValid = (fMatVal < rEntry.nVal) && !bEqual;
+                break;
+                case SC_GREATER:
+                    bValid = (fMatVal > rEntry.nVal) && !bEqual;
+                break;
+                case SC_LESS_EQUAL:
+                    bValid = (fMatVal < rEntry.nVal) || bEqual;
+                break;
+                case SC_GREATER_EQUAL:
+                    bValid = (fMatVal > rEntry.nVal) || bEqual;
+                break;
+                case SC_NOT_EQUAL:
+                    bValid = !bEqual;
+                break;
+                default:
+                    ;
+            }
+        }
+        else if (lcl_isQueryByString(rEntry, rMat, nField, nRow))
+        {
+            // By string
+            fprintf(stdout, "ScDBQueryValueIterator:DataAccessMatrix::isValidQuery:   by string\n");
+        }
+        else if (mpParam->bMixedComparison)
+        {
+        }
+
+        if (i > 0)
+        {
+            // Evaluate the query connector for secondary queries.
+            switch (rEntry.eConnect)
+            {
+                case SC_AND:
+                    if (!bValid)
+                        return false;
+                break;
+                case SC_OR:
+                break;
+            }
+        }
+
+        bEverValid |= bValid;
+    }
+    return bEverValid;
+}
+
 // ----------------------------------------------------------------------------
 
 ScDBQueryValueIterator::ScDBQueryValueIterator(ScDocument* pDocument, ScDBQueryParamBase* pParam) :
diff --git a/sc/source/core/inc/doubleref.hxx b/sc/source/core/inc/doubleref.hxx
index 27b973db45d9..f010587236c3 100644
--- a/sc/source/core/inc/doubleref.hxx
+++ b/sc/source/core/inc/doubleref.hxx
@@ -69,7 +69,17 @@ public:
     virtual ::rtl::OUString getString(SCCOL nCol, SCROW nRow) const = 0;
 
     virtual SCCOL getFirstFieldColumn() const = 0;
-    virtual SCCOL findFieldColumn(SCCOL nColIndex) const = 0;
+
+    /**
+     * Get a <i>0-based</i> column index that corresponds with the passed field
+     * index.  Note that the field index passed as the 1st parameter is
+     * <i>1-based.</i>
+     *
+     * @param nIndex 1-based field index.
+     *
+     * @return 0-based column index
+     */
+    virtual SCCOL findFieldColumn(SCCOL nIndex) const = 0;
     virtual SCCOL findFieldColumn(const ::rtl::OUString& rStr, sal_uInt16* pErr = NULL) const = 0;
     virtual ScDBQueryParamBase* createQueryParam(const ScDBRangeBase* pQueryRef) const = 0;
     virtual bool isRangeEqual(const ScRange& rRange) const = 0;
@@ -110,7 +120,16 @@ public:
     virtual ::rtl::OUString getString(SCCOL nCol, SCROW nRow) const;
 
     virtual SCCOL getFirstFieldColumn() const;
-    virtual SCCOL findFieldColumn(SCCOL nColIndex) const;
+    /**
+     * Get a <i>0-based</i> column index that corresponds with the passed field
+     * index.  Note that the field index passed as the 1st parameter is
+     * <i>1-based.</i>
+     *
+     * @param nIndex 1-based field index.
+     *
+     * @return 0-based column index
+     */
+    virtual SCCOL findFieldColumn(SCCOL nIndex) const;
     virtual SCCOL findFieldColumn(const ::rtl::OUString& rStr, sal_uInt16* pErr = NULL) const;
     virtual ScDBQueryParamBase* createQueryParam(const ScDBRangeBase* pQueryRef) const;
     virtual bool isRangeEqual(const ScRange& rRange) const;
@@ -145,7 +164,17 @@ public:
     virtual ::rtl::OUString getString(SCCOL nCol, SCROW nRow) const;
 
     virtual SCCOL getFirstFieldColumn() const;
-    virtual SCCOL findFieldColumn(SCCOL nColIndex) const;
+
+    /**
+     * Get a <i>0-based</i> column index that corresponds with the passed field
+     * index.  Note that the field index passed as the 1st parameter is
+     * <i>1-based.</i>
+     *
+     * @param nIndex 1-based field index.
+     *
+     * @return 0-based column index
+     */
+    virtual SCCOL findFieldColumn(SCCOL nIndex) const;
     virtual SCCOL findFieldColumn(const ::rtl::OUString& rStr, sal_uInt16* pErr = NULL) const;
     virtual ScDBQueryParamBase* createQueryParam(const ScDBRangeBase* pQueryRef) const;
     virtual bool isRangeEqual(const ScRange& rRange) const;
diff --git a/sc/source/core/tool/doubleref.cxx b/sc/source/core/tool/doubleref.cxx
index e36eaec94308..95b8d2bbc72a 100644
--- a/sc/source/core/tool/doubleref.cxx
+++ b/sc/source/core/tool/doubleref.cxx
@@ -47,42 +47,6 @@ using ::rtl::OUString;
 using ::std::auto_ptr;
 using ::std::vector;
 
-#include <stdio.h>
-#include <string>
-#include <sys/time.h>
-
-namespace {
-
-class StackPrinter
-{
-public:
-    explicit StackPrinter(const char* msg) :
-        msMsg(msg)
-    {
-        fprintf(stdout, "%s: --begin\n", msMsg.c_str());
-        mfStartTime = getTime();
-    }
-
-    ~StackPrinter()
-    {
-        double fEndTime = getTime();
-        fprintf(stdout, "%s: --end (duration: %g sec)\n", msMsg.c_str(), (fEndTime-mfStartTime));
-    }
-
-private:
-    double getTime() const
-    {
-        timeval tv;
-        gettimeofday(&tv, NULL);
-        return tv.tv_sec + tv.tv_usec / 1000000.0;
-    }
-
-    ::std::string msMsg;
-    double mfStartTime;
-};
-
-}
-
 namespace {
 
 void lcl_toUpper(OUString& rStr)
@@ -371,7 +335,7 @@ SCCOL ScDBInternalRange::getFirstFieldColumn() const
     return getRange().aStart.Col();
 }
 
-SCCOL ScDBInternalRange::findFieldColumn(SCCOL nColIndex) const
+SCCOL ScDBInternalRange::findFieldColumn(SCCOL nIndex) const
 {
     const ScRange& rRange = getRange();
     const ScAddress& s = rRange.aStart;
@@ -380,10 +344,10 @@ SCCOL ScDBInternalRange::findFieldColumn(SCCOL nColIndex) const
     SCCOL nDBCol1 = s.Col();
     SCCOL nDBCol2 = e.Col();
 
-    if ( nColIndex <= 0 || nColIndex > (nDBCol2 - nDBCol1 + 1) )
+    if ( nIndex <= 0 || nIndex > (nDBCol2 - nDBCol1 + 1) )
         return nDBCol1;
 
-    return Min(nDBCol2, static_cast<SCCOL>(nDBCol1 + nColIndex - 1));
+    return Min(nDBCol2, static_cast<SCCOL>(nDBCol1 + nIndex - 1));
 }
 
 sal_uInt16 ScDBInternalRange::getCellString(OUString& rStr, ScBaseCell* pCell) const
@@ -544,22 +508,56 @@ OUString ScDBExternalRange::getString(SCCOL nCol, SCROW nRow) const
 
 SCCOL ScDBExternalRange::getFirstFieldColumn() const
 {
-    return -1;
+    return 0;
 }
 
-SCCOL ScDBExternalRange::findFieldColumn(SCCOL /*nColIndex*/) const
+SCCOL ScDBExternalRange::findFieldColumn(SCCOL nIndex) const
 {
-    return -1;
+    if (nIndex < 1)
+        // 1st field
+        return 0;
+
+    if (nIndex > mnCols)
+        // last field
+        return mnCols - 1;
+
+    return nIndex - 1;
 }
 
-SCCOL ScDBExternalRange::findFieldColumn(const OUString& /*rStr*/, sal_uInt16* /*pErr*/) const
+SCCOL ScDBExternalRange::findFieldColumn(const OUString& rStr, sal_uInt16* pErr) const
 {
+    if (pErr)
+        pErr = 0;
+
+    OUString aUpper = rStr;
+    lcl_toUpper(aUpper);
+    for (SCCOL i = 0; i < mnCols; ++i)
+    {
+        OUString aUpperVal = mpMatrix->GetString(i, 0);
+        lcl_toUpper(aUpperVal);
+        if (aUpper.equals(aUpperVal))
+            return i;
+    }
     return -1;
 }
 
-ScDBQueryParamBase* ScDBExternalRange::createQueryParam(const ScDBRangeBase* /*pQueryRef*/) const
+ScDBQueryParamBase* ScDBExternalRange::createQueryParam(const ScDBRangeBase* pQueryRef) const
 {
-    return NULL;
+    auto_ptr<ScDBQueryParamMatrix> pParam(new ScDBQueryParamMatrix);
+    pParam->mpMatrix = mpMatrix;
+
+    pParam->bHasHeader = TRUE;
+    pParam->bByRow = TRUE;
+    pParam->bInplace = TRUE;
+    pParam->bCaseSens = FALSE;
+    pParam->bRegExp = FALSE;
+    pParam->bDuplicate = TRUE;
+
+    // Now construct the query entries from the query range.
+    if (!pQueryRef->fillQueryEntries(pParam.get(), this))
+        return NULL;
+
+    return pParam.release();
 }
 
 bool ScDBExternalRange::isRangeEqual(const ScRange& /*rRange*/) const
diff --git a/sc/source/core/tool/interpr1.cxx b/sc/source/core/tool/interpr1.cxx
index 08ad107fe2ee..da82cc9caa08 100644
--- a/sc/source/core/tool/interpr1.cxx
+++ b/sc/source/core/tool/interpr1.cxx
@@ -5818,7 +5818,7 @@ void ScInterpreter::DBIterator( ScIterFunc eFunc )
     if (pQueryParam.get())
     {
         double nVal;
-        USHORT nErr;
+        USHORT nErr = 0;
         ScDBQueryValueIterator aValIter(pDok, pQueryParam.release());
         if ( aValIter.GetFirst(nVal, nErr) && !nErr )
         {