fdo#72645: Allow GETPIVOTDATA to get result from leaf node of result tree.

Change-Id: I0fc1fd069440ed6fee378fc2dfd2ed761afbdeab

6 files changed, 174 insertions, 9 deletions

diff --git a/sc/inc/dpresfilter.hxx b/sc/inc/dpresfilter.hxx
index be84b8c83c2d..bc7ffc1f62b3 100644
--- a/sc/inc/dpresfilter.hxx
+++ b/sc/inc/dpresfilter.hxx
@@ -15,6 +15,7 @@
 #include <map>
 #include <vector>
 #include <boost/noncopyable.hpp>
+#include <boost/unordered_map.hpp>
 
 
 namespace com { namespace sun { namespace star { namespace sheet {
@@ -85,6 +86,15 @@ private:
 #endif
     };
 
+    typedef std::pair<OUString, OUString> NamePairType;
+
+    struct NamePairHash
+    {
+        size_t operator() (const NamePairType& rPair) const;
+    };
+    typedef boost::unordered_map<NamePairType, double, NamePairHash> LeafValuesType;
+    LeafValuesType maLeafValues;
+
     OUString maPrimaryDimName;
     MemberNode* mpRoot;
 
@@ -115,6 +125,8 @@ public:
         const com::sun::star::uno::Sequence<
             com::sun::star::sheet::DataPilotFieldFilter>& rFilters) const;
 
+    double getLeafResult(const com::sun::star::sheet::DataPilotFieldFilter& rFilter) const;
+
 #if DEBUG_PIVOT_TABLE
     void dump() const;
 #endif
diff --git a/sc/qa/unit/ucalc.hxx b/sc/qa/unit/ucalc.hxx
index 98528f6d6734..b56c31e86063 100644
--- a/sc/qa/unit/ucalc.hxx
+++ b/sc/qa/unit/ucalc.hxx
@@ -123,6 +123,7 @@ public:
     void testFuncINDIRECT();
     void testFuncIFERROR();
     void testFuncGETPIVOTDATA();
+    void testFuncGETPIVOTDATALeafAccess();
     void testCopyAttributes();
 
     void testCopyToDocument();
@@ -340,6 +341,7 @@ public:
     CPPUNIT_TEST(testFuncINDIRECT);
     CPPUNIT_TEST(testFuncIFERROR);
     CPPUNIT_TEST(testFuncGETPIVOTDATA);
+    CPPUNIT_TEST(testFuncGETPIVOTDATALeafAccess);
     CPPUNIT_TEST(testCopyToDocument);
     CPPUNIT_TEST(testSheetsFunc);
     CPPUNIT_TEST(testVolatileFunc);
diff --git a/sc/qa/unit/ucalc_pivottable.cxx b/sc/qa/unit/ucalc_pivottable.cxx
index a052adcf32e1..00e83b9f122c 100644
--- a/sc/qa/unit/ucalc_pivottable.cxx
+++ b/sc/qa/unit/ucalc_pivottable.cxx
@@ -2235,4 +2235,97 @@ void Test::testFuncGETPIVOTDATA()
     m_pDoc->DeleteTab(0);
 }
 
+void Test::testFuncGETPIVOTDATALeafAccess()
+{
+    m_pDoc->InsertTab(0, "Data");
+    m_pDoc->InsertTab(1, "Table");
+
+    // Raw data
+    const char* aData[][3] = {
+        { "Type", "Member", "Value" },
+        { "A", "Anna", "1" },
+        { "B", "Brittany", "2" },
+        { "A", "Cecilia", "3" },
+        { "B", "Donna", "4" },
+    };
+
+    ScAddress aPos(1,1,0);
+    ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData, SAL_N_ELEMENTS(aData));
+    CPPUNIT_ASSERT_MESSAGE("failed to insert range data at correct position", aDataRange.aStart == aPos);
+
+    ScDPObject* pDPObj = NULL;
+
+    // Dimension definition
+    DPFieldDef aFields[] = {
+        { "Type", sheet::DataPilotFieldOrientation_ROW, 0 },
+        { "Member", sheet::DataPilotFieldOrientation_ROW, 0 },
+        { "Value", sheet::DataPilotFieldOrientation_DATA, sheet::GeneralFunction_SUM },
+    };
+
+    // Create pivot table at A1 on 2nd sheet.
+    pDPObj = createDPFromRange(m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);
+
+    ScDPCollection* pDPs = m_pDoc->GetDPCollection();
+    bool bSuccess = pDPs->InsertNewTable(pDPObj);
+
+    CPPUNIT_ASSERT_MESSAGE("failed to insert a new pivot table object into document.", bSuccess);
+    CPPUNIT_ASSERT_MESSAGE("there should be only one data pilot table.",
+                           pDPs->GetCount() == 1);
+    pDPObj->SetName(pDPs->CreateNewName());
+    ScRange aOutRange = refresh(pDPObj);
+
+    {
+        // Expected output table content.  0 = empty cell
+        const char* aOutputCheck[][3] = {
+            { "Type",         "Member",     0  },
+            { "A",            "Anna",     "1"  },
+            {  0,             "Cecilia",  "3"  },
+            { "B",            "Brittany", "2"  },
+            {  0,             "Donna",    "4"  },
+            { "Total Result",  0,         "10" },
+        };
+
+        bSuccess = checkDPTableOutput<3>(m_pDoc, aOutRange, aOutputCheck, "Pivot table refreshed");
+        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
+    }
+
+    // Insert formulas with GETPIVOTDATA in column E, and check their results.
+
+    struct Check
+    {
+        const char* mpFormula;
+        double mfResult;
+    };
+
+    Check aChecks[] = {
+        { "=GETPIVOTDATA($A$1;\"Member[Anna]\")",     1.0 },
+        { "=GETPIVOTDATA($A$1;\"Member[Brittany]\")", 2.0 },
+        { "=GETPIVOTDATA($A$1;\"Member[Cecilia]\")",  3.0 },
+        { "=GETPIVOTDATA($A$1;\"Member[Donna]\")",    4.0 },
+    };
+
+    for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
+        m_pDoc->SetString(ScAddress(4,i,1), OUString::createFromAscii(aChecks[i].mpFormula));
+
+    m_pDoc->CalcAll();
+
+    const sal_uInt16 nNoError = 0; // no error
+    for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
+    {
+        sal_uInt16 nErr = m_pDoc->GetErrCode(ScAddress(4,i,1));
+        CPPUNIT_ASSERT_EQUAL(nNoError, nErr);
+        double fVal = m_pDoc->GetValue(ScAddress(4,i,1));
+        CPPUNIT_ASSERT_EQUAL(aChecks[i].mfResult, fVal);
+    }
+
+    pDPs->FreeTable(pDPObj);
+
+    CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", pDPs->GetCount(), static_cast<size_t>(0));
+    CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
+                           pDPs->GetSheetCaches().size(), static_cast<size_t>(0));
+
+    m_pDoc->DeleteTab(1);
+    m_pDoc->DeleteTab(0);
+}
+
 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/sc/source/core/data/dpobject.cxx b/sc/source/core/data/dpobject.cxx
index 677bd5e3f19f..4370d606da01 100644
--- a/sc/source/core/data/dpobject.cxx
+++ b/sc/source/core/data/dpobject.cxx
@@ -1328,8 +1328,8 @@ public:
     {
         // Layout name takes precedence.
         const OUString* pLayoutName = pDim->GetLayoutName();
-        if (pLayoutName)
-            return *pLayoutName == maName;
+        if (pLayoutName && *pLayoutName == maName)
+            return true;
 
         sheet::GeneralFunction eGenFunc = static_cast<sheet::GeneralFunction>(pDim->GetFunction());
         ScSubTotalFunc eFunc = ScDPUtil::toSubTotalFunc(eGenFunc);
diff --git a/sc/source/core/data/dpresfilter.cxx b/sc/source/core/data/dpresfilter.cxx
index 968be6b424e7..41b2d7eb396d 100644
--- a/sc/source/core/data/dpresfilter.cxx
+++ b/sc/source/core/data/dpresfilter.cxx
@@ -10,6 +10,8 @@
 #include "dpresfilter.hxx"
 #include "global.hxx"
 
+#include <rtl/math.hxx>
+
 #include <com/sun/star/sheet/DataPilotFieldFilter.hpp>
 
 using namespace com::sun::star;
@@ -21,6 +23,12 @@ ScDPResultFilter::ScDPResultFilter(const OUString& rDimName, bool bDataLayout) :
 ScDPResultFilterContext::ScDPResultFilterContext() :
     mnCol(0), mnRow(0) {}
 
+size_t ScDPResultTree::NamePairHash::operator() (const NamePairType& rPair) const
+{
+    OUStringHash aHash;
+    return aHash(rPair.first) + aHash(rPair.second);
+}
+
 ScDPResultTree::DimensionNode::DimensionNode(const MemberNode* pParent) :
     mpParent(pParent) {}
 
@@ -89,6 +97,8 @@ void ScDPResultTree::add(
 {
     // TODO: I'll work on the col / row to value node mapping later.
 
+    const OUString* pDimName = NULL;
+    const OUString* pMemName = NULL;
     MemberNode* pMemNode = mpRoot;
 
     std::vector<ScDPResultFilter>::const_iterator itFilter = rFilters.begin(), itFilterEnd = rFilters.end();
@@ -117,6 +127,8 @@ void ScDPResultTree::add(
             itDim = r.first;
         }
 
+        pDimName = &itDim->first;
+
         // Now, see if this dimension member exists.
         DimensionNode* pDim = itDim->second;
         MembersType& rMembers = pDim->maChildMembers;
@@ -135,9 +147,26 @@ void ScDPResultTree::add(
             itMem = r.first;
         }
 
+        pMemName = &itMem->first;
         pMemNode = itMem->second;
     }
 
+    if (pDimName && pMemName)
+    {
+        NamePairType aNames(*pDimName, *pMemName);
+        LeafValuesType::iterator it = maLeafValues.find(aNames);
+        if (it == maLeafValues.end())
+        {
+            // This name pair doesn't exist.  Associate a new value for it.
+            maLeafValues.insert(LeafValuesType::value_type(aNames, fVal));
+        }
+        else
+        {
+            // This name pair already exists. Set the value to NaN.
+            rtl::math::setNan(&it->second);
+        }
+    }
+
     pMemNode->maValues.push_back(fVal);
 }
 
@@ -145,6 +174,7 @@ void ScDPResultTree::swap(ScDPResultTree& rOther)
 {
     std::swap(maPrimaryDimName, rOther.maPrimaryDimName);
     std::swap(mpRoot, rOther.mpRoot);
+    maLeafValues.swap(rOther.maLeafValues);
 }
 
 bool ScDPResultTree::empty() const
@@ -184,6 +214,20 @@ const ScDPResultTree::ValuesType* ScDPResultTree::getResults(
     return &pMember->maValues;
 }
 
+double ScDPResultTree::getLeafResult(const com::sun::star::sheet::DataPilotFieldFilter& rFilter) const
+{
+    NamePairType aPair(rFilter.FieldName, rFilter.MatchValue);
+    LeafValuesType::const_iterator it = maLeafValues.find(aPair);
+    if (it != maLeafValues.end())
+        // Found!
+        return it->second;
+
+    // Not found.  Return an NaN.
+    double fNan;
+    rtl::math::setNan(&fNan);
+    return fNan;
+}
+
 #if DEBUG_PIVOT_TABLE
 void ScDPResultTree::dump() const
 {
diff --git a/sc/source/core/data/dptabsrc.cxx b/sc/source/core/data/dptabsrc.cxx
index 2dcb23189ab7..2371e4f87a5a 100644
--- a/sc/source/core/data/dptabsrc.cxx
+++ b/sc/source/core/data/dptabsrc.cxx
@@ -423,15 +423,29 @@ uno::Sequence<double> ScDPSource::getFilteredResults(
 
     // Get result values from the tree.
     const ScDPResultTree::ValuesType* pVals = maResFilterSet.getResults(aFilters);
-    if (!pVals)
-        return uno::Sequence<double>();
+    if (pVals)
+    {
+        size_t n = pVals->size();
+        uno::Sequence<double> aRet(n);
+        for (size_t i = 0; i < n; ++i)
+            aRet[i] = (*pVals)[i];
 
-    size_t n = pVals->size();
-    uno::Sequence<double> aRet(n);
-    for (size_t i = 0; i < n; ++i)
-        aRet[i] = (*pVals)[i];
+        return aRet;
+    }
 
-    return aRet;
+    if (aFilters.getLength() == 1)
+    {
+        // Try to get result from the leaf nodes.
+        double fVal = maResFilterSet.getLeafResult(aFilters[0]);
+        if (!rtl::math::isNan(fVal))
+        {
+            uno::Sequence<double> aRet(1);
+            aRet[0] = fVal;
+            return aRet;
+        }
+    }
+
+    return uno::Sequence<double>();
 }
 
 void SAL_CALL ScDPSource::refresh() throw(uno::RuntimeException)