Quelle  ucalc_pivottable.cxx

/* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
/*
 * This file is part of the LibreOffice project.
 *
 * This Source Code Form is subject to the terms of the Mozilla Public
 * License, v. 2.0. If a copy of the MPL was not distributed with this
 * file, You can obtain one at http://mozilla.org/MPL/2.0/.
 */

#include <rtl/math.hxx>
#include "helper/qahelper.hxx"
#include <dpshttab.hxx>
#include <dpobject.hxx>
#include <dpsave.hxx>
#include <dpdimsave.hxx>
#include <scopetools.hxx>
#include <stringutil.hxx>
#include <dbdocfun.hxx>
#include <generalfunction.hxx>
#include <tabprotection.hxx>

#include <formula/errorcodes.hxx>
#include <com/sun/star/sheet/DataPilotFieldGroupBy.hpp>
#include <com/sun/star/sheet/DataPilotFieldReferenceType.hpp>
#include <com/sun/star/sheet/DataPilotFieldReferenceItemType.hpp>

template<> std::string CppUnit::assertion_traits<ScDPItemData>::toString(ScDPItemData const &)
{ return "ScDPItemData"; } //TODO: combine with ScDPItemData::Dump?

using namespace ::com::sun::star;

namespace {

struct DPFieldDef
{
    const char* pName;
    sheet::DataPilotFieldOrientation eOrient;

    /**
     * Function for data field.  It's used only for data field.  When 0, the
     * default function (SUM) is used.
     */
    ScGeneralFunction eFunc;
    bool bRepeatItemLabels;
};

bool checkDPTableOutput(
    const ScDocument* pDoc, const ScRange& aOutRange,
    const std::vector<std::vector<const char*>>& aOutputCheck, const char* pCaption )
{
    return checkOutput(pDoc, aOutRange, aOutputCheck, pCaption);
}

ScDPObject* createDPFromSourceDesc(
    ScDocument* pDoc, const ScSheetSourceDesc& rDesc, const DPFieldDef aFields[], size_t nFieldCount,
    bool bFilterButton)
{
    ScDPObject* pDPObj = new ScDPObject(pDoc);
    pDPObj->SetSheetDesc(rDesc);
    pDPObj->SetOutRange(ScRange(ScAddress(0, 0, 1)));

    ScDPSaveData aSaveData;
    // Set data pilot table output options.
    aSaveData.SetIgnoreEmptyRows(false);
    aSaveData.SetRepeatIfEmpty(false);
    aSaveData.SetColumnGrand(true);
    aSaveData.SetRowGrand(true);
    aSaveData.SetFilterButton(bFilterButton);
    aSaveData.SetDrillDown(true);

    // Check the sanity of the source range.
    const ScRange& rSrcRange = rDesc.GetSourceRange();
    SCROW nRow1 = rSrcRange.aStart.Row();
    SCROW nRow2 = rSrcRange.aEnd.Row();
    CPPUNIT_ASSERT_MESSAGE("source range contains no data!", nRow2 - nRow1 > 1);

    // Set the dimension information.
    for (size_t i = 0; i < nFieldCount; ++i)
    {
        OUString aDimName = OUString::createFromAscii(aFields[i].pName);
        ScDPSaveDimension* pDim = aSaveData.GetNewDimensionByName(aDimName);
        pDim->SetOrientation(aFields[i].eOrient);
        pDim->SetUsedHierarchy(0);

        if (aFields[i].eOrient == sheet::DataPilotFieldOrientation_DATA)
        {
            ScGeneralFunction eFunc = ScGeneralFunction::SUM;
            if (aFields[i].eFunc != ScGeneralFunction::NONE)
                eFunc = aFields[i].eFunc;

            pDim->SetFunction(eFunc);
            pDim->SetReferenceValue(nullptr);
        }
        else
        {
            sheet::DataPilotFieldSortInfo aSortInfo;
            aSortInfo.IsAscending = true;
            aSortInfo.Mode = 2;
            pDim->SetSortInfo(&aSortInfo);

            sheet::DataPilotFieldLayoutInfo aLayInfo;
            aLayInfo.LayoutMode = 0;
            aLayInfo.AddEmptyLines = false;
            pDim->SetLayoutInfo(&aLayInfo);
            sheet::DataPilotFieldAutoShowInfo aShowInfo;
            aShowInfo.IsEnabled = false;
            aShowInfo.ShowItemsMode = 0;
            aShowInfo.ItemCount = 0;
            pDim->SetAutoShowInfo(&aShowInfo);
            pDim->SetRepeatItemLabels(aFields[i].bRepeatItemLabels);
        }
    }

    // Don't forget the data layout dimension.
    ScDPSaveDimension* pDim = aSaveData.GetDataLayoutDimension();
    pDim->SetOrientation(sheet::DataPilotFieldOrientation_ROW);
    pDim->SetShowEmpty(true);

    pDPObj->SetSaveData(aSaveData);
    pDPObj->InvalidateData();

    return pDPObj;
}

ScDPObject* createDPFromRange(
    ScDocument* pDoc, const ScRange& rRange, const DPFieldDef aFields[], size_t nFieldCount,
    bool bFilterButton)
{
    ScSheetSourceDesc aSheetDesc(pDoc);
    aSheetDesc.SetSourceRange(rRange);
    return createDPFromSourceDesc(pDoc, aSheetDesc, aFields, nFieldCount, bFilterButton);
}

ScRange refresh(ScDPObject* pDPObj)
{
    bool bOverflow = false;
    ScRange aOutRange = pDPObj->GetNewOutputRange(bOverflow);
    CPPUNIT_ASSERT_MESSAGE("Table overflow!?", !bOverflow);

    pDPObj->Output(aOutRange.aStart);
    aOutRange = pDPObj->GetOutRange();
    return aOutRange;
}

ScRange refreshGroups(ScDPCollection* pDPs, ScDPObject* pDPObj)
{
    // We need to first create group data in the cache, then the group data in
    // the object.
    o3tl::sorted_vector<ScDPObject*> aRefs;
    bool bSuccess = pDPs->ReloadGroupsInCache(pDPObj, aRefs);
    CPPUNIT_ASSERT_MESSAGE("Failed to reload group data in cache.", bSuccess);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be only one table linked to this cache.", size_t(1), aRefs.size());
    pDPObj->ReloadGroupTableData();

    return refresh(pDPObj);
}

}

class TestPivottable : public ScUcalcTestBase
{
protected:
    template<size_t Size>
    ScRange insertDPSourceData(ScDocument* pDoc, DPFieldDef const aFields[], size_t nFieldCount, const char* aData[][Size], size_t nDataCount);
};

template<size_t Size>
ScRange TestPivottable::insertDPSourceData(ScDocument* pDoc, DPFieldDef const aFields[], size_t nFieldCount, const char* aData[][Size], size_t nDataCount)
{
    // Insert field names in row 0.
    for (size_t i = 0; i < nFieldCount; ++i)
        pDoc->SetString(static_cast<SCCOL>(i), 0, 0, OUString(aFields[i].pName, strlen(aFields[i].pName), RTL_TEXTENCODING_UTF8));

    // Insert data into row 1 and downward.
    for (size_t i = 0; i < nDataCount; ++i)
    {
        SCROW nRow = static_cast<SCROW>(i) + 1;
        for (size_t j = 0; j < nFieldCount; ++j)
        {
            SCCOL nCol = static_cast<SCCOL>(j);
            pDoc->SetString(
                nCol, nRow, 0, OUString(aData[i][j], strlen(aData[i][j]), RTL_TEXTENCODING_UTF8));
        }
    }

    SCROW nRow1 = 0, nRow2 = 0;
    SCCOL nCol1 = 0, nCol2 = 0;
    pDoc->GetDataArea(0, nCol1, nRow1, nCol2, nRow2, true, false);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("Data is expected to start from (col=0,row=0).", SCCOL(0), nCol1);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("Data is expected to start from (col=0,row=0).", SCROW(0), nRow1);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("Unexpected data range.",
                           static_cast<SCCOL>(nFieldCount - 1), nCol2);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("Unexpected data range.",
                           static_cast<SCROW>(nDataCount), nRow2);

    ScRange aSrcRange(nCol1, nRow1, 0, nCol2, nRow2, 0);
    printRange(pDoc, aSrcRange, "Data sheet content");
    return aSrcRange;
}


CPPUNIT_TEST_FIXTURE(TestPivottable, testPivotTable)
{
    /**
     * Basic test for pivot tables.
     */
    m_pDoc->InsertTab(0, u"Data"_ustr);
    m_pDoc->InsertTab(1, u"Table"_ustr);

    // Dimension definition
    static const DPFieldDef aFields[] = {
        { "Name",  sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
        { "Group", sheet::DataPilotFieldOrientation_COLUMN, ScGeneralFunction::NONE, false },
        { "Score", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::NONE, false }
    };

    // Raw data
    const char* aData[][3] = {
        { "Andy",    "A", "30" },
        { "Bruce",   "A", "20" },
        { "Charlie", "B", "45" },
        { "David",   "B", "12" },
        { "Edward",  "C",  "8" },
        { "Frank",   "C", "15" },
    };

    size_t nFieldCount = SAL_N_ELEMENTS(aFields);
    size_t const nDataCount = SAL_N_ELEMENTS(aData);

    ScRange aSrcRange = insertDPSourceData(m_pDoc, aFields, nFieldCount, aData, nDataCount);
    SCROW nRow1 = aSrcRange.aStart.Row(), nRow2 = aSrcRange.aEnd.Row();
    SCCOL nCol1 = aSrcRange.aStart.Col(), nCol2 = aSrcRange.aEnd.Col();

    ScDPObject* pDPObj = createDPFromRange(
        m_pDoc, ScRange(nCol1, nRow1, 0, nCol2, nRow2, 0), aFields, nFieldCount, false);

    ScDPCollection* pDPs = m_pDoc->GetDPCollection();
    pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj));
    CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
                           size_t(1), pDPs->GetCount());
    pDPObj->SetName(pDPs->CreateNewName());

    bool bOverflow = false;
    ScRange aOutRange = pDPObj->GetNewOutputRange(bOverflow);
    CPPUNIT_ASSERT_MESSAGE("Table overflow!?", !bOverflow);

    pDPObj->Output(aOutRange.aStart);
    aOutRange = pDPObj->GetOutRange();
    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Sum - Score", "Group", nullptr, nullptr, nullptr },
            { "Name", "A", "B", "C", "Total Result" },
            { "Andy", "30", nullptr, nullptr, "30" },
            { "Bruce", "20", nullptr, nullptr, "20" },
            { "Charlie", nullptr, "45", nullptr, "45" },
            { "David", nullptr, "12", nullptr, "12" },
            { "Edward", nullptr, nullptr, "8", "8" },
            { "Frank", nullptr, nullptr, "15", "15" },
            { "Total Result", "50", "57", "23", "130" }
        };

        bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }
    CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be only one data cache.", size_t(1), pDPs->GetSheetCaches().size());

    // Update the cell values.
    double aData2[] = { 100, 200, 300, 400, 500, 600 };
    for (size_t i = 0; i < SAL_N_ELEMENTS(aData2); ++i)
    {
        SCROW nRow = i + 1;
        m_pDoc->SetValue(2, nRow, 0, aData2[i]);
    }

    printRange(m_pDoc, ScRange(nCol1, nRow1, 0, nCol2, nRow2, 0), "Data sheet content (modified)");

    // Now, create a copy of the datapilot object for the updated table, but
    // don't reload the cache which should force the copy to use the old data
    // from the cache.
    ScDPObject* pDPObj2 = new ScDPObject(*pDPObj);
    pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj2));

    aOutRange = pDPObj2->GetOutRange();
    pDPObj2->ClearTableData();
    pDPObj2->Output(aOutRange.aStart);
    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Sum - Score", "Group", nullptr, nullptr, nullptr },
            { "Name", "A", "B", "C", "Total Result" },
            { "Andy", "30", nullptr, nullptr, "30" },
            { "Bruce", "20", nullptr, nullptr, "20" },
            { "Charlie", nullptr, "45", nullptr, "45" },
            { "David", nullptr, "12", nullptr, "12" },
            { "Edward", nullptr, nullptr, "8", "8" },
            { "Frank", nullptr, nullptr, "15", "15" },
            { "Total Result", "50", "57", "23", "130" }
        };

        bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output (from old cache)");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be only one data cache.", size_t(1), pDPs->GetSheetCaches().size());

    // Free the first datapilot object after the 2nd one gets reloaded, to
    // prevent the data cache from being deleted before the reload.
    pDPs->FreeTable(pDPObj);

    CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be only one data cache.", size_t(1), pDPs->GetSheetCaches().size());

    // This time clear the cache to refresh the data from the source range.
    CPPUNIT_ASSERT_MESSAGE("This datapilot should be based on sheet data.", pDPObj2->IsSheetData());
    o3tl::sorted_vector<ScDPObject*> aRefs;
    TranslateId pErrId = pDPs->ReloadCache(pDPObj2, aRefs);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("Cache reload failed.", false, bool(pErrId));
    CPPUNIT_ASSERT_EQUAL_MESSAGE("Reloading a cache shouldn't remove any cache.",
                           static_cast<size_t>(1), pDPs->GetSheetCaches().size());

    pDPObj2->ClearTableData();
    pDPObj2->Output(aOutRange.aStart);

    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Sum - Score", "Group", nullptr, nullptr, nullptr },
            { "Name", "A", "B", "C", "Total Result" },
            { "Andy", "100", nullptr, nullptr, "100" },
            { "Bruce", "200", nullptr, nullptr, "200" },
            { "Charlie", nullptr, "300", nullptr, "300" },
            { "David", nullptr, "400", nullptr, "400" },
            { "Edward", nullptr, nullptr, "500", "500" },
            { "Frank", nullptr, nullptr, "600", "600" },
            { "Total Result", "300", "700", "1100", "2100" }
        };

        bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output (refreshed)");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    CPPUNIT_ASSERT_MESSAGE("Cache should be here.", pDPs->GetSheetCaches().hasCache(aSrcRange));

    // Swap the two sheets.
    m_pDoc->MoveTab(1, 0);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("Swapping the sheets shouldn't remove the cache.",
                           size_t(1), pDPs->GetSheetCaches().size());
    CPPUNIT_ASSERT_MESSAGE("Cache should have moved.", !pDPs->GetSheetCaches().hasCache(aSrcRange));
    aSrcRange.aStart.SetTab(1);
    aSrcRange.aEnd.SetTab(1);
    CPPUNIT_ASSERT_MESSAGE("Cache should be here.", pDPs->GetSheetCaches().hasCache(aSrcRange));

    pDPs->FreeTable(pDPObj2);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any data pilot table stored with the document.",
                           size_t(0), pDPs->GetCount());

    CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more data cache.",
                           size_t(0), pDPs->GetSheetCaches().size());

    // Insert a brand new pivot table object once again, but this time, don't
    // create the output to avoid creating a data cache.
    m_pDoc->DeleteTab(1);
    m_pDoc->InsertTab(1, u"Table"_ustr);

    pDPObj = createDPFromRange(
        m_pDoc, ScRange(nCol1, nRow1, 0, nCol2, nRow2, 0), aFields, nFieldCount, false);
    pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj));
    CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
                           size_t(1), pDPs->GetCount());
    pDPObj->SetName(pDPs->CreateNewName());
    CPPUNIT_ASSERT_EQUAL_MESSAGE("Data cache shouldn't exist yet before creating the table output.",
                           size_t(0), pDPs->GetSheetCaches().size());

    // Now, "refresh" the table.  This should still return a reference to self
    // even with the absence of data cache.
    aRefs.clear();
    pDPs->ReloadCache(pDPObj, aRefs);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("It should return the same object as a reference.",
                           o3tl::sorted_vector<ScDPObject*>::size_type(1), aRefs.size());
    CPPUNIT_ASSERT_EQUAL_MESSAGE("It should return the same object as a reference.",
                           pDPObj, *aRefs.begin());

    pDPs->FreeTable(pDPObj);

    m_pDoc->DeleteTab(1);
    m_pDoc->DeleteTab(0);
}

CPPUNIT_TEST_FIXTURE(TestPivottable, testPivotTableLabels)
{
    /**
     * Test against unwanted automatic format detection on field names and
     * field members in pivot tables.
     */
    m_pDoc->InsertTab(0, u"Data"_ustr);
    m_pDoc->InsertTab(1, u"Table"_ustr);

    // Dimension definition
    static const DPFieldDef aFields[] = {
        { "Software", sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
        { "Version",  sheet::DataPilotFieldOrientation_COLUMN, ScGeneralFunction::NONE, false },
        { "1.2.3",    sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::NONE, false }
    };

    // Raw data
    const char* aData[][3] = {
        { "LibreOffice", "3.3.0", "30" },
        { "LibreOffice", "3.3.1", "20" },
        { "LibreOffice", "3.4.0", "45" },
    };

    size_t nFieldCount = SAL_N_ELEMENTS(aFields);
    size_t const nDataCount = SAL_N_ELEMENTS(aData);

    ScRange aSrcRange = insertDPSourceData(m_pDoc, aFields, nFieldCount, aData, nDataCount);
    SCROW nRow1 = aSrcRange.aStart.Row(), nRow2 = aSrcRange.aEnd.Row();
    SCCOL nCol1 = aSrcRange.aStart.Col(), nCol2 = aSrcRange.aEnd.Col();

    ScDPObject* pDPObj = createDPFromRange(
        m_pDoc, ScRange(nCol1, nRow1, 0, nCol2, nRow2, 0), aFields, nFieldCount, false);

    ScDPCollection* pDPs = m_pDoc->GetDPCollection();
    pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj));
    CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
                           size_t(1), pDPs->GetCount());
    pDPObj->SetName(pDPs->CreateNewName());

    ScRange aOutRange = refresh(pDPObj);
    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Sum - 1.2.3", "Version", nullptr, nullptr, nullptr },
            { "Software", "3.3.0", "3.3.1", "3.4.0", "Total Result" },
            { "LibreOffice", "30", "20", "45", "95" },
            { "Total Result", "30", "20", "45", "95" }
        };

        bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    pDPs->FreeTable(pDPObj);

    m_pDoc->DeleteTab(1);
    m_pDoc->DeleteTab(0);
}

CPPUNIT_TEST_FIXTURE(TestPivottable, testPivotTableDateLabels)
{
    /**
     * Make sure that we set cells displaying date values numeric cells,
     * rather than text cells.  Grouping by date or number functionality
     * depends on this.
     */
    m_pDoc->InsertTab(0, u"Data"_ustr);
    m_pDoc->InsertTab(1, u"Table"_ustr);

    // Dimension definition
    static const DPFieldDef aFields[] = {
        { "Name",  sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
        { "Date",  sheet::DataPilotFieldOrientation_COLUMN, ScGeneralFunction::NONE, false },
        { "Value", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::NONE, false }
    };

    // Raw data
    const char* aData[][3] = {
        { "Zena",   "2011-1-1", "30" },
        { "Yodel",  "2011-1-2", "20" },
        { "Xavior", "2011-1-3", "45" }
    };

    size_t nFieldCount = SAL_N_ELEMENTS(aFields);
    size_t const nDataCount = SAL_N_ELEMENTS(aData);

    ScRange aSrcRange = insertDPSourceData(m_pDoc, aFields, nFieldCount, aData, nDataCount);
    SCROW nRow1 = aSrcRange.aStart.Row(), nRow2 = aSrcRange.aEnd.Row();
    SCCOL nCol1 = aSrcRange.aStart.Col(), nCol2 = aSrcRange.aEnd.Col();

    ScDPObject* pDPObj = createDPFromRange(
        m_pDoc, ScRange(nCol1, nRow1, 0, nCol2, nRow2, 0), aFields, nFieldCount, false);

    ScDPCollection* pDPs = m_pDoc->GetDPCollection();
    pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj));
    CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
                           size_t(1), pDPs->GetCount());
    pDPObj->SetName(pDPs->CreateNewName());

    ScRange aOutRange = refresh(pDPObj);
    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Sum - Value", "Date", nullptr, nullptr, nullptr },
            { "Name", "2011-01-01", "2011-01-02", "2011-01-03", "Total Result" },
            { "Xavior",  nullptr, nullptr, "45", "45" },
            { "Yodel",  nullptr, "20", nullptr, "20" },
            { "Zena",  "30", nullptr, nullptr, "30" },
            { "Total Result", "30", "20", "45", "95" }
        };

        bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    {
        const char* const aChecks[] = {
            "2011-01-01", "2011-01-02", "2011-01-03"
        };

        // Make sure those cells that contain dates are numeric.
        SCROW nRow = aOutRange.aStart.Row() + 1;
        nCol1 = aOutRange.aStart.Col() + 1;
        nCol2 = nCol1 + 2;
        for (SCCOL nCol = nCol1; nCol <= nCol2; ++nCol)
        {
            OUString aVal = m_pDoc->GetString(nCol, nRow, 1);
            CPPUNIT_ASSERT_MESSAGE("Cell value is not as expected.", aVal.equalsAscii(aChecks[nCol-nCol1]));
            CPPUNIT_ASSERT_MESSAGE("This cell contains a date value and is supposed to be numeric.",
                                   m_pDoc->HasValueData(nCol, nRow, 1));
        }
    }

    pDPs->FreeTable(pDPObj);

    m_pDoc->DeleteTab(1);
    m_pDoc->DeleteTab(0);
}

CPPUNIT_TEST_FIXTURE(TestPivottable, testPivotTableFilters)
{
    /**
     * Test for pivot table's filtering functionality by page fields.
     */
    m_pDoc->InsertTab(0, u"Data"_ustr);
    m_pDoc->InsertTab(1, u"Table"_ustr);

    // Dimension definition
    static const DPFieldDef aFields[] = {
        { "Name",   sheet::DataPilotFieldOrientation_HIDDEN, ScGeneralFunction::NONE, false },
        { "Group1", sheet::DataPilotFieldOrientation_HIDDEN, ScGeneralFunction::NONE, false },
        { "Group2", sheet::DataPilotFieldOrientation_PAGE, ScGeneralFunction::NONE, false },
        { "Val1",   sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::NONE, false },
        { "Val2",   sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::NONE, false }
    };

    // Raw data
    const char* aData[][5] = {
        { "A", "1", "A", "1", "10" },
        { "B", "1", "A", "1", "10" },
        { "C", "1", "B", "1", "10" },
        { "D", "1", "B", "1", "10" },
        { "E", "2", "A", "1", "10" },
        { "F", "2", "A", "1", "10" },
        { "G", "2", "B", "1", "10" },
        { "H", "2", "B", "1", "10" }
    };

    size_t nFieldCount = SAL_N_ELEMENTS(aFields);
    size_t const nDataCount = SAL_N_ELEMENTS(aData);

    ScRange aSrcRange = insertDPSourceData(m_pDoc, aFields, nFieldCount, aData, nDataCount);
    SCROW nRow1 = aSrcRange.aStart.Row(), nRow2 = aSrcRange.aEnd.Row();
    SCCOL nCol1 = aSrcRange.aStart.Col(), nCol2 = aSrcRange.aEnd.Col();

    ScDPObject* pDPObj = createDPFromRange(
        m_pDoc, ScRange(nCol1, nRow1, 0, nCol2, nRow2, 0), aFields, nFieldCount, true);

    ScDPCollection* pDPs = m_pDoc->GetDPCollection();
    pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj));
    CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
                           size_t(1), pDPs->GetCount());
    pDPObj->SetName(pDPs->CreateNewName());

    ScRange aOutRange = refresh(pDPObj);
    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Filter", nullptr },
            { "Group2", "- all -" },
            { nullptr, nullptr },
            { "Data", nullptr },
            { "Sum - Val1", "8" },
            { "Sum - Val2", "80" }
        };

        bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output (unfiltered)");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn on auto calculation.

    ScAddress aFormulaAddr = aOutRange.aEnd;
    aFormulaAddr.IncRow(2);
    m_pDoc->SetString(aFormulaAddr.Col(), aFormulaAddr.Row(), aFormulaAddr.Tab(),
                      u"=B6"_ustr);
    double fTest = m_pDoc->GetValue(aFormulaAddr);
    ASSERT_DOUBLES_EQUAL_MESSAGE("Incorrect formula value that references a cell in the pivot table output.", 80.0, fTest);

    // Set current page of 'Group2' to 'A'.
    pDPObj->BuildAllDimensionMembers();
    ScDPSaveData aSaveData(*pDPObj->GetSaveData());
    ScDPSaveDimension* pPageDim = aSaveData.GetDimensionByName(
        u"Group2"_ustr);
    CPPUNIT_ASSERT_MESSAGE("Dimension not found", pPageDim);
    OUString aPage(u"A"_ustr);
    pPageDim->SetCurrentPage(&aPage);
    pDPObj->SetSaveData(aSaveData);
    aOutRange = refresh(pDPObj);
    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Filter", nullptr },
            { "Group2", "A" },
            { nullptr, nullptr },
            { "Data", nullptr },
            { "Sum - Val1", "4" },
            { "Sum - Val2", "40" }
        };

        bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output (filtered by page)");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    fTest = m_pDoc->GetValue(aFormulaAddr);
    ASSERT_DOUBLES_EQUAL_MESSAGE("Incorrect formula value that references a cell in the pivot table output.", 40.0, fTest);

    // Set query filter.
    ScSheetSourceDesc aDesc(*pDPObj->GetSheetDesc());
    ScQueryParam aQueryParam(aDesc.GetQueryParam());
    CPPUNIT_ASSERT_MESSAGE("There should be at least one query entry.", aQueryParam.GetEntryCount() > 0);
    ScQueryEntry& rEntry = aQueryParam.GetEntry(0);
    rEntry.bDoQuery = true;
    rEntry.nField = 1;  // Group1
    rEntry.GetQueryItem().mfVal = 1;
    aDesc.SetQueryParam(aQueryParam);
    pDPObj->SetSheetDesc(aDesc);
    aOutRange = refresh(pDPObj);
    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Filter", nullptr },
            { "Group2", "A" },
            { nullptr, nullptr },
            { "Data", nullptr },
            { "Sum - Val1", "2" },
            { "Sum - Val2", "20" }
        };

        bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output (filtered by query)");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    fTest = m_pDoc->GetValue(aFormulaAddr);
    ASSERT_DOUBLES_EQUAL_MESSAGE("Incorrect formula value that references a cell in the pivot table output.", 20.0, fTest);

    // Set the current page of 'Group2' back to '- all -'. The query filter
    // should still be in effect.
    pPageDim->SetCurrentPage(nullptr); // Remove the page.
    pDPObj->SetSaveData(aSaveData);
    aOutRange = refresh(pDPObj);
    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Filter", nullptr },
            { "Group2", "- all -" },
            { nullptr, nullptr },
            { "Data", nullptr },
            { "Sum - Val1", "4" },
            { "Sum - Val2", "40" }
        };

        bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output (filtered by page)");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    pDPs->FreeTable(pDPObj);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any data pilot table stored with the document.",
                           size_t(0), pDPs->GetCount());

    m_pDoc->DeleteTab(1);
    m_pDoc->DeleteTab(0);
}

CPPUNIT_TEST_FIXTURE(TestPivottable, testPivotTableNamedSource)
{
    /**
     * Test for pivot table's named source range.
     */
    m_pDoc->InsertTab(0, u"Data"_ustr);
    m_pDoc->InsertTab(1, u"Table"_ustr);

    // Dimension definition
    static const DPFieldDef aFields[] = {
        { "Name",  sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
        { "Group", sheet::DataPilotFieldOrientation_COLUMN, ScGeneralFunction::NONE, false },
        { "Score", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::NONE, false }
    };

    // Raw data
    const char* aData[][3] = {
        { "Andy",    "A", "30" },
        { "Bruce",   "A", "20" },
        { "Charlie", "B", "45" },
        { "David",   "B", "12" },
        { "Edward",  "C",  "8" },
        { "Frank",   "C", "15" },
    };

    size_t nFieldCount = SAL_N_ELEMENTS(aFields);
    size_t const nDataCount = SAL_N_ELEMENTS(aData);

    // Insert the raw data.
    ScRange aSrcRange = insertDPSourceData(m_pDoc, aFields, nFieldCount, aData, nDataCount);
    OUString aRangeStr(aSrcRange.Format(*m_pDoc, ScRefFlags::RANGE_ABS_3D));

    // Name this range.
    OUString aRangeName(u"MyData"_ustr);
    ScRangeName* pNames = m_pDoc->GetRangeName();
    CPPUNIT_ASSERT_MESSAGE("Failed to get global range name container.", pNames);
    ScRangeData* pName = new ScRangeData(
        *m_pDoc, aRangeName, aRangeStr);
    bool bSuccess = pNames->insert(pName);
    CPPUNIT_ASSERT_MESSAGE("Failed to insert a new name.", bSuccess);

    ScSheetSourceDesc aSheetDesc(m_pDoc);
    aSheetDesc.SetRangeName(aRangeName);
    ScDPObject* pDPObj = createDPFromSourceDesc(m_pDoc, aSheetDesc, aFields, nFieldCount, false);
    CPPUNIT_ASSERT_MESSAGE("Failed to create a new pivot table object.", pDPObj);

    ScDPCollection* pDPs = m_pDoc->GetDPCollection();
    pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj));
    CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
                           size_t(1), pDPs->GetCount());
    pDPObj->SetName(pDPs->CreateNewName());

    ScRange aOutRange = refresh(pDPObj);
    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Sum - Score", "Group", nullptr, nullptr, nullptr },
            { "Name", "A", "B", "C", "Total Result" },
            { "Andy", "30", nullptr, nullptr, "30" },
            { "Bruce", "20", nullptr, nullptr, "20" },
            { "Charlie", nullptr, "45", nullptr, "45" },
            { "David", nullptr, "12", nullptr, "12" },
            { "Edward", nullptr, nullptr, "8", "8" },
            { "Frank", nullptr, nullptr, "15", "15" },
            { "Total Result", "50", "57", "23", "130" }
        };

        bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be one named range data cache.",
                           size_t(1), pDPs->GetNameCaches().size());
    CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be one named range data cache.",
                           size_t(0), pDPs->GetSheetCaches().size());

    // Move the table with pivot table to the left of the source data sheet.
    m_pDoc->MoveTab(1, 0);
    OUString aTabName;
    m_pDoc->GetName(0, aTabName);
    CPPUNIT_ASSERT_EQUAL_MESSAGE( "Wrong sheet name.", u"Table"_ustr, aTabName);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("Pivot table output is on the wrong sheet!",
                           static_cast<SCTAB>(0), pDPObj->GetOutRange().aStart.Tab());

    CPPUNIT_ASSERT_EQUAL_MESSAGE("Moving the pivot table to another sheet shouldn't have changed the cache state.",
                           size_t(1), pDPs->GetNameCaches().size());
    CPPUNIT_ASSERT_EQUAL_MESSAGE("Moving the pivot table to another sheet shouldn't have changed the cache state.",
                           size_t(0), pDPs->GetSheetCaches().size());

    const ScSheetSourceDesc* pDesc = pDPObj->GetSheetDesc();
    CPPUNIT_ASSERT_MESSAGE("Sheet source description doesn't exist.", pDesc);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("Named source range has been altered unexpectedly!",
                           pDesc->GetRangeName(), aRangeName);

    CPPUNIT_ASSERT_MESSAGE("Cache should exist.", pDPs->GetNameCaches().hasCache(aRangeName));

    pDPs->FreeTable(pDPObj);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", size_t(0), pDPs->GetCount());
    CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
                           size_t(0), pDPs->GetNameCaches().size());

    pNames->clear();
    m_pDoc->DeleteTab(1);
    m_pDoc->DeleteTab(0);
}

CPPUNIT_TEST_FIXTURE(TestPivottable, testPivotTableCache)
{
    /**
     * Test for pivot table cache.  Each dimension in the pivot cache stores
     * only unique values that are sorted in ascending order.
     */
    m_pDoc->InsertTab(0, u"Data"_ustr);

    // Raw data
    const std::vector<std::vector<const char*>> aData = {
        { "F1", "F2", "F3" },
        { "Z",  "A", "30" },
        { "R",  "A", "20" },
        { "A",  "B", "45" },
        { "F",  "B", "12" },
        { "Y",  "C",  "8" },
        { "12", "C", "15" },
    };

    ScAddress aPos(1,1,0);
    ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("failed to insert range data at correct position", aPos, aDataRange.aStart);

    ScDPCache aCache(*m_pDoc);
    aCache.InitFromDoc(*m_pDoc, aDataRange);
    tools::Long nDimCount = aCache.GetColumnCount();
    CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong dimension count.", tools::Long(3), nDimCount);
    OUString aDimName = aCache.GetDimensionName(0);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong dimension name", u"F1"_ustr, aDimName);
    aDimName = aCache.GetDimensionName(1);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong dimension name", u"F2"_ustr, aDimName);
    aDimName = aCache.GetDimensionName(2);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong dimension name", u"F3"_ustr, aDimName);

    // In each dimension, member ID values also represent their sort order (in
    // source dimensions only, not in group dimensions). Value items are
    // sorted before string ones. Also, no duplicate dimension members should
    // exist.

    // Dimension 0 - a mix of strings and values.
    tools::Long nMemCount = aCache.GetDimMemberCount(0);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong dimension member count", tools::Long(6), nMemCount);
    const ScDPItemData* pItem = aCache.GetItemDataById(0, 0);
    CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value", ScDPItemData::Value, pItem->GetType());
    CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value",
                           12.0, pItem->GetValue());
    pItem = aCache.GetItemDataById(0, 1);
    CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value", ScDPItemData::String, pItem->GetType());
    CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value",
                           u"A"_ustr, pItem->GetString());
    pItem = aCache.GetItemDataById(0, 2);
    CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value", ScDPItemData::String, pItem->GetType());
    CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value",
                           u"F"_ustr, pItem->GetString());
    pItem = aCache.GetItemDataById(0, 3);
    CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value", ScDPItemData::String, pItem->GetType());
    CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value",
                           u"R"_ustr, pItem->GetString());
    pItem = aCache.GetItemDataById(0, 4);
    CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value", ScDPItemData::String, pItem->GetType());
    CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value",
                           u"Y"_ustr, pItem->GetString());
    pItem = aCache.GetItemDataById(0, 5);
    CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value", ScDPItemData::String, pItem->GetType());
    CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value",
                           u"Z"_ustr, pItem->GetString());
    pItem = aCache.GetItemDataById(0, 6);
    CPPUNIT_ASSERT_MESSAGE("wrong item value", !pItem);

    // Dimension 1 - duplicate values in source.
    nMemCount = aCache.GetDimMemberCount(1);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong dimension member count", tools::Long(3), nMemCount);
    pItem = aCache.GetItemDataById(1, 0);
    CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value", ScDPItemData::String, pItem->GetType());
    CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value",
                           u"A"_ustr, pItem->GetString());
    pItem = aCache.GetItemDataById(1, 1);
    CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value", ScDPItemData::String, pItem->GetType());
    CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value",
                           u"B"_ustr, pItem->GetString());
    pItem = aCache.GetItemDataById(1, 2);
    CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value", ScDPItemData::String, pItem->GetType());
    CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value",
                           u"C"_ustr, pItem->GetString());
    pItem = aCache.GetItemDataById(1, 3);
    CPPUNIT_ASSERT_MESSAGE("wrong item value", !pItem);

    // Dimension 2 - values only.
    nMemCount = aCache.GetDimMemberCount(2);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong dimension member count", tools::Long(6), nMemCount);
    pItem = aCache.GetItemDataById(2, 0);
    CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value", ScDPItemData::Value, pItem->GetType());
    CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value",
                           8.0, pItem->GetValue());
    pItem = aCache.GetItemDataById(2, 1);
    CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value", ScDPItemData::Value, pItem->GetType());
    CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value",
                           12.0, pItem->GetValue());
    pItem = aCache.GetItemDataById(2, 2);
    CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value", ScDPItemData::Value, pItem->GetType());
    CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value",
                           15.0, pItem->GetValue());
    pItem = aCache.GetItemDataById(2, 3);
    CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value", ScDPItemData::Value, pItem->GetType());
    CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value",
                           20.0, pItem->GetValue());
    pItem = aCache.GetItemDataById(2, 4);
    CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value", ScDPItemData::Value, pItem->GetType());
    CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value",
                           30.0, pItem->GetValue());
    pItem = aCache.GetItemDataById(2, 5);
    CPPUNIT_ASSERT_MESSAGE("wrong item value", pItem);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value", ScDPItemData::Value, pItem->GetType());
    CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong item value",
                           45.0, pItem->GetValue());
    pItem = aCache.GetItemDataById(2, 6);
    CPPUNIT_ASSERT_MESSAGE("wrong item value", !pItem);

    {
        // Check the integrity of the source data.
        ScDPItemData aTest;
        tools::Long nDim;

        {
            // Dimension 0: Z, R, A, F, Y, 12
            nDim = 0;
            const char* aChecks[] = { "Z", "R", "A", "F", "Y" };
            for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
            {
                pItem = aCache.GetItemDataById(nDim, aCache.GetItemDataId(nDim, i, false));
                aTest.SetString(OUString::createFromAscii(aChecks[i]));
                CPPUNIT_ASSERT_MESSAGE("wrong data value", pItem);
                CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong data value", aTest, *pItem);
            }

            pItem = aCache.GetItemDataById(nDim, aCache.GetItemDataId(nDim, 5, false));
            aTest.SetValue(12);
            CPPUNIT_ASSERT_MESSAGE("wrong data value", pItem);
            CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong data value", aTest, *pItem);
        }

        {
            // Dimension 1: A, A, B, B, C, C
            nDim = 1;
            const char* aChecks[] = { "A", "A", "B", "B", "C", "C" };
            for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
            {
                pItem = aCache.GetItemDataById(nDim, aCache.GetItemDataId(nDim, i, false));
                aTest.SetString(OUString::createFromAscii(aChecks[i]));
                CPPUNIT_ASSERT_MESSAGE("wrong data value", pItem);
                CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong data value", aTest, *pItem);
            }
        }

        {
            // Dimension 2: 30, 20, 45, 12, 8, 15
            nDim = 2;
            double aChecks[] = { 30, 20, 45, 12, 8, 15 };
            for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
            {
                pItem = aCache.GetItemDataById(nDim, aCache.GetItemDataId(nDim, i, false));
                aTest.SetValue(aChecks[i]);
                CPPUNIT_ASSERT_MESSAGE("wrong data value", pItem);
                CPPUNIT_ASSERT_EQUAL_MESSAGE("wrong data value", aTest, *pItem);
            }
        }
    }

    // Now, on to testing the filtered cache.

    {
        // Non-filtered cache - everything should be visible.
        ScDPFilteredCache aFilteredCache(aCache);
        aFilteredCache.fillTable();

        sal_Int32 nRows = aFilteredCache.getRowSize();
        CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong dimension.", sal_Int32(6), nRows);
        CPPUNIT_ASSERT_EQUAL_MESSAGE("Wrong dimension.", sal_Int32(3), aFilteredCache.getColSize());

        for (sal_Int32 i = 0; i < nRows; ++i)
        {
            if (!aFilteredCache.isRowActive(i))
            {
                std::ostringstream os;
                os << "Row " << i << " should be visible but it isn't.";
                CPPUNIT_ASSERT_MESSAGE(os.str(), false);
            }
        }
    }

    // TODO : Add test for filtered caches.

    m_pDoc->DeleteTab(0);
}

CPPUNIT_TEST_FIXTURE(TestPivottable, testPivotTableDuplicateDataFields)
{
    /**
     * Test for pivot table containing data fields that reference the same
     * source field but different functions.
     */
    m_pDoc->InsertTab(0, u"Data"_ustr);
    m_pDoc->InsertTab(1, u"Table"_ustr);

    // Raw data
    const std::vector<std::vector<const char*>> aData = {
        { "Name", "Value" },
        { "A",       "45" },
        { "A",        "5" },
        { "A",       "41" },
        { "A",       "49" },
        { "A",        "4" },
        { "B",       "33" },
        { "B",       "84" },
        { "B",       "74" },
        { "B",        "8" },
        { "B",       "68" }
    };

    // Dimension definition
    static const DPFieldDef aFields[] = {
        { "Name",  sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
        { "Value", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::SUM, false },
        { "Value", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::COUNT, false }
    };

    ScAddress aPos(2,2,0);
    ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("failed to insert range data at correct position", aPos, aDataRange.aStart);

    ScDPObject* pDPObj = createDPFromRange(
        m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);

    ScDPCollection* pDPs = m_pDoc->GetDPCollection();
    pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj));
    CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
                           size_t(1), pDPs->GetCount());
    pDPObj->SetName(pDPs->CreateNewName());

    ScRange aOutRange = refresh(pDPObj);
    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Name", "Data", nullptr },
            { "A", "Sum - Value", "144" },
            { nullptr, "Count - Value", "5" },
            { "B", "Sum - Value", "267" },
            { nullptr, "Count - Value", "5" },
            { "Total Sum - Value", nullptr, "411" },
            { "Total Count - Value", nullptr, "10" },
        };

        bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    // Move the data layout dimension from row to column.
    ScDPSaveData* pSaveData = pDPObj->GetSaveData();
    CPPUNIT_ASSERT_MESSAGE("No save data!?", pSaveData);
    ScDPSaveDimension* pDataLayout = pSaveData->GetDataLayoutDimension();
    CPPUNIT_ASSERT_MESSAGE("No data layout dimension.", pDataLayout);
    pDataLayout->SetOrientation(sheet::DataPilotFieldOrientation_COLUMN);
    pDPObj->SetSaveData(*pSaveData);

    // Refresh the table output.
    aOutRange = refresh(pDPObj);
    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { nullptr, "Data", nullptr },
            { "Name", "Sum - Value", "Count - Value" },
            { "A", "144", "5" },
            { "B", "267", "5" },
            { "Total Result", "411", "10" }
        };

        bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    ScPivotParam aParam;
    pDPObj->FillLabelData(aParam);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be exactly 4 labels (2 original, 1 data layout, and 1 duplicate dimensions).",
                           size_t(4), aParam.maLabelArray.size());

    pDPs->FreeTable(pDPObj);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", size_t(0), pDPs->GetCount());
    CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
                           size_t(0), pDPs->GetSheetCaches().size());

    m_pDoc->DeleteTab(1);
    m_pDoc->DeleteTab(0);
}

CPPUNIT_TEST_FIXTURE(TestPivottable, testPivotTableNormalGrouping)
{
    m_pDoc->InsertTab(0, u"Data"_ustr);
    m_pDoc->InsertTab(1, u"Table"_ustr);

    // Raw data
    const std::vector<std::vector<const char*>> aData = {
        { "Name", "Value" },
        { "A", "1" },
        { "B", "2" },
        { "C", "3" },
        { "D", "4" },
        { "E", "5" },
        { "F", "6" },
        { "G", "7" }
    };

    // Dimension definition
    static const DPFieldDef aFields[] = {
        { "Name",  sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
        { "Value", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::SUM, false },
    };

    ScAddress aPos(1,1,0);
    ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("failed to insert range data at correct position", aPos, aDataRange.aStart);

    ScDPObject* pDPObj = createDPFromRange(
        m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);

    ScDPCollection* pDPs = m_pDoc->GetDPCollection();
    pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj));
    CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
                           size_t(1), pDPs->GetCount());
    pDPObj->SetName(pDPs->CreateNewName());

    ScRange aOutRange = refresh(pDPObj);
    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Name", "Sum - Value" },
            { "A", "1" },
            { "B", "2" },
            { "C", "3" },
            { "D", "4" },
            { "E", "5" },
            { "F", "6" },
            { "G", "7" },
            { "Total Result", "28" }
        };

        bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Initial output without grouping");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    ScDPSaveData* pSaveData = pDPObj->GetSaveData();
    CPPUNIT_ASSERT_MESSAGE("No save data !?", pSaveData);
    ScDPDimensionSaveData* pDimData = pSaveData->GetDimensionData();
    CPPUNIT_ASSERT_MESSAGE("Failed to create dimension data.", pDimData);

    OUString aGroupPrefix(u"Group"_ustr);
    OUString aBaseDimName(u"Name"_ustr);
    OUString aGroupDimName =
        pDimData->CreateGroupDimName(aBaseDimName, *pDPObj, false, nullptr);

    {
        // Group A, B and C together.
        ScDPSaveGroupDimension aGroupDim(aBaseDimName, aGroupDimName);
        OUString aGroupName = aGroupDim.CreateGroupName(aGroupPrefix);
        CPPUNIT_ASSERT_EQUAL_MESSAGE("Unexpected group name", u"Group1"_ustr, aGroupName);

        ScDPSaveGroupItem aGroup(aGroupName);
        aGroup.AddElement(u"A"_ustr);
        aGroup.AddElement(u"B"_ustr);
        aGroup.AddElement(u"C"_ustr);
        aGroupDim.AddGroupItem(aGroup);
        pDimData->AddGroupDimension(aGroupDim);

        ScDPSaveDimension* pDim = pSaveData->GetDimensionByName(aGroupDimName);
        pDim->SetOrientation(sheet::DataPilotFieldOrientation_ROW);
        pSaveData->SetPosition(pDim, 0); // Set it before the base dimension.
    }

    pDPObj->SetSaveData(*pSaveData);
    aOutRange = refreshGroups(pDPs, pDPObj);
    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Name2", "Name", "Sum - Value" },
            { "D", "D", "4" },
            { "E", "E", "5" },
            { "F", "F", "6" },
            { "G", "G", "7" },
            { "Group1", "A", "1" },
            { nullptr,        "B", "2" },
            { nullptr,        "C", "3" },
            { "Total Result", nullptr, "28" }
        };

        bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "A, B, C grouped by Group1.");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    pSaveData = pDPObj->GetSaveData();
    pDimData = pSaveData->GetDimensionData();

    {
        // Group D, E, F together.
        ScDPSaveGroupDimension* pGroupDim = pDimData->GetGroupDimAccForBase(aBaseDimName);
        CPPUNIT_ASSERT_MESSAGE("There should be an existing group dimension.", pGroupDim);
        OUString aGroupName = pGroupDim->CreateGroupName(aGroupPrefix);
        CPPUNIT_ASSERT_EQUAL_MESSAGE("Unexpected group name", u"Group2"_ustr, aGroupName);

        ScDPSaveGroupItem aGroup(aGroupName);
        aGroup.AddElement(u"D"_ustr);
        aGroup.AddElement(u"E"_ustr);
        aGroup.AddElement(u"F"_ustr);
        pGroupDim->AddGroupItem(aGroup);
    }

    pDPObj->SetSaveData(*pSaveData);
    aOutRange = refreshGroups(pDPs, pDPObj);
    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Name2", "Name", "Sum - Value" },
            { "G", "G", "7" },
            { "Group1", "A", "1" },
            { nullptr,        "B", "2" },
            { nullptr,        "C", "3" },
            { "Group2", "D", "4" },
            { nullptr,        "E", "5" },
            { nullptr,        "F", "6" },
            { "Total Result", nullptr, "28" }
        };

        bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "D, E, F grouped by Group2.");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    pDPs->FreeTable(pDPObj);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", size_t(0), pDPs->GetCount());
    CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
                           size_t(0), pDPs->GetSheetCaches().size());

    m_pDoc->DeleteTab(1);
    m_pDoc->DeleteTab(0);
}

CPPUNIT_TEST_FIXTURE(TestPivottable, testPivotTableNumberGrouping)
{
    m_pDoc->InsertTab(0, u"Data"_ustr);
    m_pDoc->InsertTab(1, u"Table"_ustr);

    // Raw data
    const std::vector<std::vector<const char*>> aData = {
        { "Order", "Score" },
        { "43", "171" },
        { "18", "20"  },
        { "69", "159" },
        { "95", "19"  },
        { "96", "163" },
        { "46", "70"  },
        { "22", "36"  },
        { "81", "49"  },
        { "54", "61"  },
        { "39", "62"  },
        { "86", "17"  },
        { "34", "0"   },
        { "30", "25"  },
        { "24", "103" },
        { "16", "59"  },
        { "24", "119" },
        { "15", "86"  },
        { "69", "170" }
    };

    // Dimension definition
    static const DPFieldDef aFields[] = {
        { "Order", sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
        { "Score", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::SUM, false },
    };

    ScAddress aPos(1,1,0);
    ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("failed to insert range data at correct position", aPos, aDataRange.aStart);

    ScDPObject* pDPObj = createDPFromRange(
        m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);

    ScDPCollection* pDPs = m_pDoc->GetDPCollection();
    pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj));
    CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
                           size_t(1), pDPs->GetCount());
    pDPObj->SetName(pDPs->CreateNewName());

    ScDPSaveData* pSaveData = pDPObj->GetSaveData();
    CPPUNIT_ASSERT_MESSAGE("No save data !?", pSaveData);
    ScDPDimensionSaveData* pDimData = pSaveData->GetDimensionData();
    CPPUNIT_ASSERT_MESSAGE("No dimension data !?", pDimData);

    {
        ScDPNumGroupInfo aInfo;
        aInfo.mbEnable = true;
        aInfo.mbAutoStart = false;
        aInfo.mbAutoEnd = false;
        aInfo.mbDateValues = false;
        aInfo.mbIntegerOnly = true;
        aInfo.mfStart = 30;
        aInfo.mfEnd = 60;
        aInfo.mfStep = 10;
        ScDPSaveNumGroupDimension aGroup(u"Order"_ustr, aInfo);
        pDimData->AddNumGroupDimension(aGroup);
    }

    pDPObj->SetSaveData(*pSaveData);
    ScRange aOutRange = refreshGroups(pDPs, pDPObj);
    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Order", "Sum - Score" },
            { "<30",   "423" },
            { "30-39", "87"  },
            { "40-49", "241" },
            { "50-60", "61"  },
            { ">60",   "577" },
            { "Total Result", "1389" }
        };

        bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Order grouped by numbers");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    pDPs->FreeTable(pDPObj);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", size_t(0), pDPs->GetCount());
    CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
                           size_t(0), pDPs->GetSheetCaches().size());

    m_pDoc->DeleteTab(1);
    m_pDoc->DeleteTab(0);
}

CPPUNIT_TEST_FIXTURE(TestPivottable, testPivotTableDateGrouping)
{
    m_pDoc->InsertTab(0, u"Data"_ustr);
    m_pDoc->InsertTab(1, u"Table"_ustr);

    // Raw data
    const std::vector<std::vector<const char*>> aData = {
        { "Date", "Value" },
        { "2011-01-01", "1" },
        { "2011-03-02", "2" },
        { "2012-01-04", "3" },
        { "2012-02-23", "4" },
        { "2012-02-24", "5" },
        { "2012-03-15", "6" },
        { "2011-09-03", "7" },
        { "2012-12-25", "8" }
    };

    // Dimension definition
    static const DPFieldDef aFields[] = {
        { "Date", sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
        { "Value", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::SUM, false },
    };

    ScAddress aPos(1,1,0);
    ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("failed to insert range data at correct position", aPos, aDataRange.aStart);

    ScDPObject* pDPObj = createDPFromRange(
        m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);

    ScDPCollection* pDPs = m_pDoc->GetDPCollection();
    pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj));
    CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
                           size_t(1), pDPs->GetCount());
    pDPObj->SetName(pDPs->CreateNewName());

    ScDPSaveData* pSaveData = pDPObj->GetSaveData();
    CPPUNIT_ASSERT_MESSAGE("No save data !?", pSaveData);
    ScDPDimensionSaveData* pDimData = pSaveData->GetDimensionData();
    CPPUNIT_ASSERT_MESSAGE("No dimension data !?", pDimData);

    OUString aBaseDimName(u"Date"_ustr);

    ScDPNumGroupInfo aInfo;
    aInfo.mbEnable = true;
    aInfo.mbAutoStart = true;
    aInfo.mbAutoEnd = true;
    {
        // Turn the Date dimension into months.  The first of the date
        // dimensions is always a number-group dimension which replaces the
        // original dimension.
        ScDPSaveNumGroupDimension aGroup(aBaseDimName, aInfo, sheet::DataPilotFieldGroupBy::MONTHS);
        pDimData->AddNumGroupDimension(aGroup);
    }

    {
        // Add quarter dimension.  This will be an additional dimension.
        OUString aGroupDimName =
            pDimData->CreateDateGroupDimName(
                sheet::DataPilotFieldGroupBy::QUARTERS, *pDPObj, true, nullptr);
        ScDPSaveGroupDimension aGroupDim(aBaseDimName, aGroupDimName);
        aGroupDim.SetDateInfo(aInfo, sheet::DataPilotFieldGroupBy::QUARTERS);
        pDimData->AddGroupDimension(aGroupDim);

        // Set orientation.
        ScDPSaveDimension* pDim = pSaveData->GetDimensionByName(aGroupDimName);
        pDim->SetOrientation(sheet::DataPilotFieldOrientation_ROW);
        pSaveData->SetPosition(pDim, 0); // set it to the left end.
    }

    {
        // Add year dimension.  This is a new dimension also.
        OUString aGroupDimName =
            pDimData->CreateDateGroupDimName(
                sheet::DataPilotFieldGroupBy::YEARS, *pDPObj, true, nullptr);
        ScDPSaveGroupDimension aGroupDim(aBaseDimName, aGroupDimName);
        aGroupDim.SetDateInfo(aInfo, sheet::DataPilotFieldGroupBy::YEARS);
        pDimData->AddGroupDimension(aGroupDim);

        // Set orientation.
        ScDPSaveDimension* pDim = pSaveData->GetDimensionByName(aGroupDimName);
        pDim->SetOrientation(sheet::DataPilotFieldOrientation_ROW);
        pSaveData->SetPosition(pDim, 0); // set it to the left end.
    }

    pDPObj->SetSaveData(*pSaveData);
    ScRange aOutRange = refreshGroups(pDPs, pDPObj);
    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Years", "Quarters", "Date", "Sum - Value" },
            { "2011", "Q1", "Jan", "1" },
            { nullptr, nullptr,         "Mar", "2" },
            { nullptr,      "Q3", "Sep", "7" },
            { "2012", "Q1", "Jan", "3" },
            { nullptr, nullptr,         "Feb", "9" },
            { nullptr, nullptr,         "Mar", "6" },
            { nullptr,      "Q4", "Dec", "8" },
            { "Total Result", nullptr, nullptr, "36" },
        };

        bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Years, quarters and months date groups.");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    {
        // Let's hide year 2012.
        pSaveData = pDPObj->GetSaveData();
        ScDPSaveDimension* pDim = pSaveData->GetDimensionByName(u"Years"_ustr);
        CPPUNIT_ASSERT_MESSAGE("Years dimension should exist.", pDim);
        ScDPSaveMember* pMem = pDim->GetMemberByName(u"2012"_ustr);
        CPPUNIT_ASSERT_MESSAGE("Member should exist.", pMem);
        pMem->SetIsVisible(false);
    }
    pDPObj->SetSaveData(*pSaveData);
    pDPObj->ReloadGroupTableData();
    pDPObj->InvalidateData();

    aOutRange = refresh(pDPObj);
    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Years", "Quarters", "Date", "Sum - Value" },
            { "2011", "Q1", "Jan", "1" },
            { nullptr, nullptr,         "Mar", "2" },
            { nullptr,      "Q3", "Sep", "7" },
            { "Total Result", nullptr, nullptr, "10" },
        };

        bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Year 2012 data now hidden");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    // Remove all date grouping. The source dimension "Date" has two
    // external dimensions ("Years" and "Quarters") and one internal ("Date"
    // the same name but different hierarchy).  Remove all of them.
    pSaveData = pDPObj->GetSaveData();
    pSaveData->RemoveAllGroupDimensions(aBaseDimName);
    pDPObj->SetSaveData(*pSaveData);
    pDPObj->ReloadGroupTableData();
    pDPObj->InvalidateData();

    aOutRange = refresh(pDPObj);
    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Date", "Sum - Value" },
            { "2011-01-01", "1" },
            { "2011-03-02", "2" },
            { "2011-09-03", "7" },
            { "2012-01-04", "3" },
            { "2012-02-23", "4" },
            { "2012-02-24", "5" },
            { "2012-03-15", "6" },
            { "2012-12-25", "8" },
            { "Total Result", "36" }
        };

        bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Remove all date grouping.");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    pDPs->FreeTable(pDPObj);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", size_t(0), pDPs->GetCount());
    CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
                           size_t(0), pDPs->GetSheetCaches().size());

    m_pDoc->DeleteTab(1);
    m_pDoc->DeleteTab(0);
}

CPPUNIT_TEST_FIXTURE(TestPivottable, testPivotTableEmptyRows)
{
    m_pDoc->InsertTab(0, u"Data"_ustr);
    m_pDoc->InsertTab(1, u"Table"_ustr);

    // Raw data
    const std::vector<std::vector<const char*>> aData = {
        { "Name", "Value" },
        { "A", "1" },
        { "B", "2" },
        { "C", "3" },
        { "D", "4" },
    };

    // Dimension definition
    static const DPFieldDef aFields[] = {
        { "Name", sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
        { "Value", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::SUM, false },
    };

    ScAddress aPos(1,1,0);
    ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("failed to insert range data at correct position", aPos, aDataRange.aStart);

    // Extend the range downward to include some trailing empty rows.
    aDataRange.aEnd.IncRow(2);

    ScDPObject* pDPObj = createDPFromRange(
        m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);

    ScDPCollection* pDPs = m_pDoc->GetDPCollection();
    pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj));
    CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
                           size_t(1), pDPs->GetCount());
    pDPObj->SetName(pDPs->CreateNewName());

    ScRange aOutRange = refresh(pDPObj);

    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Name", "Sum - Value" },
            { "A", "1" },
            { "B", "2" },
            { "C", "3" },
            { "D", "4" },
            { "(empty)", nullptr },
            { "Total Result", "10" },
        };

        bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Include empty rows");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    // This time, ignore empty rows.
    ScDPSaveData* pSaveData = pDPObj->GetSaveData();
    CPPUNIT_ASSERT_MESSAGE("Save data doesn't exist.", pSaveData);
    pSaveData->SetIgnoreEmptyRows(true);
    pDPObj->ClearTableData();
    aOutRange = refresh(pDPObj);

    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Name", "Sum - Value" },
            { "A", "1" },
            { "B", "2" },
            { "C", "3" },
            { "D", "4" },
            { "Total Result", "10" },
        };

        bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Ignore empty rows");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    // Modify the source to remove member 'A', then refresh the table.
    m_pDoc->SetString(1, 2, 0, u"B"_ustr);

    o3tl::sorted_vector<ScDPObject*> aRefs;
    TranslateId pErr = pDPs->ReloadCache(pDPObj, aRefs);
    CPPUNIT_ASSERT_MESSAGE("Failed to reload cache.", !pErr);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("There should only be one pivot table linked to this cache.",
                           o3tl::sorted_vector<ScDPObject*>::size_type(1), aRefs.size());
    CPPUNIT_ASSERT_EQUAL_MESSAGE("There should only be one pivot table linked to this cache.",
                           pDPObj, *aRefs.begin());

    pDPObj->ClearTableData();
    aOutRange = refresh(pDPObj);

    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Name", "Sum - Value" },
            { "B", "3" },
            { "C", "3" },
            { "D", "4" },
            { "Total Result", "10" },
        };

        bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Ignore empty rows");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    pDPs->FreeTable(pDPObj);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", size_t(0), pDPs->GetCount());
    CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
                           size_t(0), pDPs->GetSheetCaches().size());

    m_pDoc->DeleteTab(1);
    m_pDoc->DeleteTab(0);
}

CPPUNIT_TEST_FIXTURE(TestPivottable, testPivotTableTextNumber)
{
    m_pDoc->InsertTab(0, u"Data"_ustr);
    m_pDoc->InsertTab(1, u"Table"_ustr);

    // Raw data
    const char* aData[][2] = {
        { "Name", "Value" },
        { "0001", "1" },
        { "0002", "2" },
        { "0003", "3" },
        { "0004", "4" },
    };

    // Dimension definition
    static const DPFieldDef aFields[] = {
        { "Name", sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
        { "Value", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::SUM, false },
    };

    // Insert raw data such that the first column values are entered as text.
    for (size_t nRow = 0; nRow < SAL_N_ELEMENTS(aData); ++nRow)
    {
        ScSetStringParam aParam;
        aParam.mbDetectNumberFormat = false;
        aParam.meSetTextNumFormat = ScSetStringParam::Always;
        m_pDoc->SetString(0, nRow, 0, OUString::createFromAscii(aData[nRow][0]), &aParam);
        aParam.meSetTextNumFormat = ScSetStringParam::Never;
        m_pDoc->SetString(1, nRow, 0, OUString::createFromAscii(aData[nRow][1]), &aParam);

        if (nRow == 0)
            // Don't check the header row.
            continue;

        // Check the data rows.
        CPPUNIT_ASSERT_MESSAGE("This cell is supposed to be text.", m_pDoc->HasStringData(0, nRow, 0));
        CPPUNIT_ASSERT_MESSAGE("This cell is supposed to be numeric.", m_pDoc->HasValueData(1, nRow, 0));
    }

    ScRange aDataRange(0, 0, 0, 1, 4, 0);

    ScDPObject* pDPObj = createDPFromRange(
        m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);

    ScDPCollection* pDPs = m_pDoc->GetDPCollection();
    pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj));
    CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
                           size_t(1), pDPs->GetCount());
    pDPObj->SetName(pDPs->CreateNewName());

    ScRange aOutRange = refresh(pDPObj);

    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Name", "Sum - Value" },
            { "0001", "1" },
            { "0002", "2" },
            { "0003", "3" },
            { "0004", "4" },
            { "Total Result", "10" },
        };

        bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Text number field members");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    // Set the Name dimension to page dimension.
    pDPObj->BuildAllDimensionMembers();
    ScDPSaveData aSaveData(*pDPObj->GetSaveData());
    ScDPSaveDimension* pDim = aSaveData.GetExistingDimensionByName(u"Name");
    CPPUNIT_ASSERT(pDim);
    pDim->SetOrientation(sheet::DataPilotFieldOrientation_PAGE);
    OUString aVisiblePage(u"0004"_ustr);
    pDim->SetCurrentPage(&aVisiblePage);
    pDPObj->SetSaveData(aSaveData);

    aOutRange = refresh(pDPObj);

    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Name", "0004" },
            {  nullptr, nullptr },
            { "Sum - Value", nullptr },
            { "4", nullptr }
        };

        bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Text number field members");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    pDPs->FreeTable(pDPObj);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", size_t(0), pDPs->GetCount());
    CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
                                 size_t(0), pDPs->GetSheetCaches().size());

    m_pDoc->DeleteTab(1);
    m_pDoc->DeleteTab(0);
}

CPPUNIT_TEST_FIXTURE(TestPivottable, testPivotTableCaseInsensitiveStrings)
{
    /**
     * Test for checking that pivot table treats strings in a case insensitive
     * manner.
     */
    m_pDoc->InsertTab(0, u"Data"_ustr);
    m_pDoc->InsertTab(1, u"Table"_ustr);

    // Raw data
    const std::vector<std::vector<const char*>> aData = {
        { "Name", "Value" },
        { "A", "1" },
        { "a", "2" },
    };

    // Dimension definition
    static const DPFieldDef aFields[] = {
        { "Name", sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
        { "Value", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::SUM, false },
    };

    ScAddress aPos(1,1,0);
    ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("failed to insert range data at correct position", aPos, aDataRange.aStart);

    ScDPObject* pDPObj = createDPFromRange(
        m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);

    ScDPCollection* pDPs = m_pDoc->GetDPCollection();
    pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj));
    CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
                           size_t(1), pDPs->GetCount());
    pDPObj->SetName(pDPs->CreateNewName());

    ScRange aOutRange = refresh(pDPObj);

    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Name", "Sum - Value" },
            { "A", "3" },
            { "Total Result", "3" },
        };

        bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Case insensitive strings");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    pDPs->FreeTable(pDPObj);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", size_t(0), pDPs->GetCount());
    CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
                           size_t(0), pDPs->GetSheetCaches().size());

    m_pDoc->DeleteTab(1);
    m_pDoc->DeleteTab(0);
}

CPPUNIT_TEST_FIXTURE(TestPivottable, testPivotTableNumStability)
{
    /**
     * Test for pivot table's handling of double-precision numbers that are
     * very close together.
     */
    FormulaGrammarSwitch aFGSwitch(m_pDoc, formula::FormulaGrammar::GRAM_ENGLISH_XL_R1C1);

    // Raw Data
    const std::vector<std::vector<const char*>> aData = {
        { "Name",   "Time Start", "Time End", "Total"          },
        { "Sam",    "07:48 AM",   "09:00 AM", "=RC[-1]-RC[-2]" },
        { "Sam",    "09:00 AM",   "10:30 AM", "=RC[-1]-RC[-2]" },
        { "Sam",    "10:30 AM",   "12:30 PM", "=RC[-1]-RC[-2]" },
        { "Sam",    "12:30 PM",   "01:00 PM", "=RC[-1]-RC[-2]" },
        { "Sam",    "01:00 PM",   "01:30 PM", "=RC[-1]-RC[-2]" },
        { "Sam",    "01:30 PM",   "02:00 PM", "=RC[-1]-RC[-2]" },
        { "Sam",    "02:00 PM",   "07:15 PM", "=RC[-1]-RC[-2]" },
        { "Sam",    "07:47 AM",   "09:00 AM", "=RC[-1]-RC[-2]" },
        { "Sam",    "09:00 AM",   "10:00 AM", "=RC[-1]-RC[-2]" },
        { "Sam",    "10:00 AM",   "11:00 AM", "=RC[-1]-RC[-2]" },
        { "Sam",    "11:00 AM",   "11:30 AM", "=RC[-1]-RC[-2]" },
        { "Sam",    "11:30 AM",   "12:45 PM", "=RC[-1]-RC[-2]" },
        { "Sam",    "12:45 PM",   "01:15 PM", "=RC[-1]-RC[-2]" },
        { "Sam",    "01:15 PM",   "02:30 PM", "=RC[-1]-RC[-2]" },
        { "Sam",    "02:30 PM",   "02:45 PM", "=RC[-1]-RC[-2]" },
        { "Sam",    "02:45 PM",   "04:30 PM", "=RC[-1]-RC[-2]" },
        { "Sam",    "04:30 PM",   "06:00 PM", "=RC[-1]-RC[-2]" },
        { "Sam",    "06:00 PM",   "07:15 PM", "=RC[-1]-RC[-2]" },
        { "Mike",   "06:15 AM",   "08:30 AM", "=RC[-1]-RC[-2]" },
        { "Mike",   "08:30 AM",   "10:03 AM", "=RC[-1]-RC[-2]" },
        { "Mike",   "10:03 AM",   "12:00 PM", "=RC[-1]-RC[-2]" },
        { "Dennis", "11:00 AM",   "01:00 PM", "=RC[-1]-RC[-2]" },
        { "Dennis", "01:00 PM",   "02:00 PM", "=RC[-1]-RC[-2]" }
    };

    // Dimension definition
    static const DPFieldDef aFields[] = {
        { "Name",  sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
        { "Total", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::SUM, false },
    };

    m_pDoc->InsertTab(0, u"Data"_ustr);
    m_pDoc->InsertTab(1, u"Table"_ustr);

    ScAddress aPos(1,1,0);
    ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData);

    // Insert formulas to manually calculate sums for each name.
    m_pDoc->SetString(aDataRange.aStart.Col(), aDataRange.aEnd.Row()+1, aDataRange.aStart.Tab(), u"=SUMIF(R[-23]C:R[-1]C;\"Dennis\";R[-23]C[3]:R[-1]C[3])"_ustr);
    m_pDoc->SetString(aDataRange.aStart.Col(), aDataRange.aEnd.Row()+2, aDataRange.aStart.Tab(), u"=SUMIF(R[-24]C:R[-2]C;\"Mike\";R[-24]C[3]:R[-2]C[3])"_ustr);
    m_pDoc->SetString(aDataRange.aStart.Col(), aDataRange.aEnd.Row()+3, aDataRange.aStart.Tab(), u"=SUMIF(R[-25]C:R[-3]C;\"Sam\";R[-25]C[3]:R[-3]C[3])"_ustr);

    m_pDoc->CalcAll();

    // Get correct sum values.
    double fDennisTotal = m_pDoc->GetValue(aDataRange.aStart.Col(), aDataRange.aEnd.Row()+1, aDataRange.aStart.Tab());
    double fMikeTotal = m_pDoc->GetValue(aDataRange.aStart.Col(), aDataRange.aEnd.Row()+2, aDataRange.aStart.Tab());
    double fSamTotal = m_pDoc->GetValue(aDataRange.aStart.Col(), aDataRange.aEnd.Row()+3, aDataRange.aStart.Tab());

    ScDPObject* pDPObj = createDPFromRange(
        m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);

    ScDPCollection* pDPs = m_pDoc->GetDPCollection();
    pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj));
    CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
                           size_t(1), pDPs->GetCount());
    pDPObj->SetName(pDPs->CreateNewName());

    ScRange aOutRange = refresh(pDPObj);

    // Manually check the total value for each name.
    //
    // +--------------+----------------+
    // | Name         |                |
    // +--------------+----------------+
    // | Dennis       | <Dennis total> |
    // +--------------+----------------+
    // | Mike         | <Miks total>   |
    // +--------------+----------------+
    // | Sam          | <Sam total>    |
    // +--------------+----------------+
    // | Total Result | ...            |
    // +--------------+----------------+

    aPos = aOutRange.aStart;
    aPos.IncCol();
    aPos.IncRow();
    double fTest = m_pDoc->GetValue(aPos);
    CPPUNIT_ASSERT_MESSAGE("Incorrect value for Dennis.", rtl::math::approxEqual(fTest, fDennisTotal));
    aPos.IncRow();
    fTest = m_pDoc->GetValue(aPos);
    CPPUNIT_ASSERT_MESSAGE("Incorrect value for Mike.", rtl::math::approxEqual(fTest, fMikeTotal));
    aPos.IncRow();
    fTest = m_pDoc->GetValue(aPos);
    CPPUNIT_ASSERT_MESSAGE("Incorrect value for Sam.", rtl::math::approxEqual(fTest, fSamTotal));

    pDPs->FreeTable(pDPObj);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", size_t(0), pDPs->GetCount());
    CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
                           size_t(0), pDPs->GetSheetCaches().size());

    m_pDoc->DeleteTab(1);
    m_pDoc->DeleteTab(0);
}

CPPUNIT_TEST_FIXTURE(TestPivottable, testPivotTableFieldReference)
{
    /**
     * Test for pivot table that include field with various non-default field
     * references.
     */
    m_pDoc->InsertTab(0, u"Data"_ustr);
    m_pDoc->InsertTab(1, u"Table"_ustr);

    // Raw data
    const std::vector<std::vector<const char*>> aData = {
        { "Name", "Value" },
        { "A", "1" },
        { "B", "2" },
        { "C", "4" },
        { "D", "8" },
    };

    // Dimension definition
    static const DPFieldDef aFields[] = {
        { "Name", sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
        { "Value", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::SUM, false },
    };

    ScAddress aPos(1,1,0);
    ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("failed to insert range data at correct position", aPos, aDataRange.aStart);

    ScDPObject* pDPObj = createDPFromRange(
        m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);

    ScDPCollection* pDPs = m_pDoc->GetDPCollection();
    pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj));
    CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
                           size_t(1), pDPs->GetCount());
    pDPObj->SetName(pDPs->CreateNewName());

    ScRange aOutRange = refresh(pDPObj);

    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Name", "Sum - Value" },
            { "A", "1" },
            { "B", "2" },
            { "C", "4" },
            { "D", "8" },
            { "Total Result", "15" },
        };

        bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Field reference (none)");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    ScDPSaveData aSaveData = *pDPObj->GetSaveData();
    sheet::DataPilotFieldReference aFieldRef;
    aFieldRef.ReferenceType = sheet::DataPilotFieldReferenceType::ITEM_DIFFERENCE;
    aFieldRef.ReferenceField = "Name";
    aFieldRef.ReferenceItemType = sheet::DataPilotFieldReferenceItemType::NAMED;
    aFieldRef.ReferenceItemName = "A";
    ScDPSaveDimension* pDim = aSaveData.GetDimensionByName(u"Value"_ustr);
    CPPUNIT_ASSERT_MESSAGE("Failed to retrieve dimension 'Value'.", pDim);
    pDim->SetReferenceValue(&aFieldRef);
    pDPObj->SetSaveData(aSaveData);

    aOutRange = refresh(pDPObj);
    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Name", "Sum - Value" },
            { "A", nullptr },
            { "B", "1" },
            { "C", "3" },
            { "D", "7" },
            { "Total Result", nullptr },
        };

        bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Field reference (difference from)");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    aFieldRef.ReferenceType = sheet::DataPilotFieldReferenceType::ITEM_PERCENTAGE;
    pDim->SetReferenceValue(&aFieldRef);
    pDPObj->SetSaveData(aSaveData);

    aOutRange = refresh(pDPObj);
    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Name", "Sum - Value" },
            { "A", "100.00%" },
            { "B", "200.00%" },
            { "C", "400.00%" },
            { "D", "800.00%" },
            { "Total Result", nullptr },
        };

        bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Field reference (% of)");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    aFieldRef.ReferenceType = sheet::DataPilotFieldReferenceType::ITEM_PERCENTAGE_DIFFERENCE;
    pDim->SetReferenceValue(&aFieldRef);
    pDPObj->SetSaveData(aSaveData);

    aOutRange = refresh(pDPObj);
    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Name", "Sum - Value" },
            { "A", nullptr },
            { "B", "100.00%" },
            { "C", "300.00%" },
            { "D", "700.00%" },
            { "Total Result", nullptr },
        };

        bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Field reference (% difference from)");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    aFieldRef.ReferenceType = sheet::DataPilotFieldReferenceType::RUNNING_TOTAL;
    pDim->SetReferenceValue(&aFieldRef);
    pDPObj->SetSaveData(aSaveData);

    aOutRange = refresh(pDPObj);
    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Name", "Sum - Value" },
            { "A", "1" },
            { "B", "3" },
            { "C", "7" },
            { "D", "15" },
            { "Total Result", nullptr },
        };

        bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Field reference (Running total)");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    aFieldRef.ReferenceType = sheet::DataPilotFieldReferenceType::COLUMN_PERCENTAGE;
    pDim->SetReferenceValue(&aFieldRef);
    pDPObj->SetSaveData(aSaveData);

    aOutRange = refresh(pDPObj);
    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Name", "Sum - Value" },
            { "A", "6.67%" },
            { "B", "13.33%" },
            { "C", "26.67%" },
            { "D", "53.33%" },
            { "Total Result", "100.00%" },
        };

        bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Field reference (% of column)");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    pDPs->FreeTable(pDPObj);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", size_t(0), pDPs->GetCount());
    CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
                           size_t(0), pDPs->GetSheetCaches().size());

    m_pDoc->DeleteTab(1);
    m_pDoc->DeleteTab(0);
}

CPPUNIT_TEST_FIXTURE(TestPivottable, testPivotTableDocFunc)
{
    /**
     * Test pivot table functionality performed via ScDBDocFunc.
     */
    m_pDoc->InsertTab(0, u"Data"_ustr);
    m_pDoc->InsertTab(1, u"Table"_ustr);

    // Raw data
    const std::vector<std::vector<const char*>> aData = {
        { "Name",      "Value" },
        { "Sun",       "1" },
        { "Oracle",    "2" },
        { "Red Hat",   "4" },
        { "SUSE",      "8" },
        { "Apple",     "16" },
        { "Microsoft", "32" },
    };

    // Dimension definition
    static const DPFieldDef aFields[] = {
        { "Name", sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
        { "Value", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::SUM, false },
    };

    ScAddress aPos(1,1,0);
    ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("failed to insert range data at correct position", aPos, aDataRange.aStart);

    std::unique_ptr<ScDPObject> pDPObj(createDPFromRange(
        m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false));

    CPPUNIT_ASSERT_MESSAGE("Failed to create pivot table object.", pDPObj);

    // Create a new pivot table output.
    ScDBDocFunc aFunc(*m_xDocShell);
    bool bSuccess = aFunc.CreatePivotTable(*pDPObj, false, true);
    CPPUNIT_ASSERT_MESSAGE("Failed to create pivot table output via ScDBDocFunc.", bSuccess);
    ScDPCollection* pDPs = m_pDoc->GetDPCollection();
    CPPUNIT_ASSERT_MESSAGE("Failed to get pivot table collection.", pDPs);
    CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(1), pDPs->GetCount());
    ScDPObject* pDPObject = &(*pDPs)[0];
    ScRange aOutRange = pDPObject->GetOutRange();
    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Name", "Sum - Value" },
            { "Apple", "16" },
            { "Microsoft", "32" },
            { "Oracle", "2" },
            { "Red Hat", "4" },
            { "Sun", "1" },
            { "SUSE", "8" },
            { "Total Result", "63" },
        };

        bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Pivot table created via ScDBDocFunc");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    // Start: Test Pivot table with tab protection
    ScTableProtection aProtect;
    aProtect.setProtected(true);
    m_pDoc->SetTabProtection(1, &aProtect);

    bSuccess = aFunc.RemovePivotTable(*pDPObject, false, true);
    CPPUNIT_ASSERT_MESSAGE("Pivot table should not be allowed to remove.", !bSuccess);

    bSuccess = aFunc.UpdatePivotTable(*pDPObject, false, true);
    CPPUNIT_ASSERT_MESSAGE("Pivot table should not be allowed to update.", !bSuccess);

    // Allow pivot table usage
    aProtect.setOption(ScTableProtection::PIVOT_TABLES, true);
    m_pDoc->SetTabProtection(1, &aProtect);

    bSuccess = aFunc.RemovePivotTable(*pDPObject, false, true);
    CPPUNIT_ASSERT_MESSAGE("Pivot table should not be allowed to remove.", !bSuccess);

    bSuccess = aFunc.UpdatePivotTable(*pDPObject, false, true);
    CPPUNIT_ASSERT_MESSAGE("Pivot table should be allowed to update.", bSuccess);

    aProtect.setProtected(false);
    aProtect.setOption(ScTableProtection::PIVOT_TABLES, false);
    m_pDoc->SetTabProtection(1, &aProtect);
    // End: Test Pivot table with tab protection

    // Remove this pivot table output. This should also clear the pivot cache
    // it was referencing.
    bSuccess = aFunc.RemovePivotTable(*pDPObject, false, true);
    CPPUNIT_ASSERT_MESSAGE("Failed to remove pivot table output via ScDBDocFunc.", bSuccess);
    CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(0), pDPs->GetCount());
    CPPUNIT_ASSERT_EQUAL(static_cast<size_t>(0), pDPs->GetSheetCaches().size());

    m_pDoc->DeleteTab(1);
    m_pDoc->DeleteTab(0);
}

CPPUNIT_TEST_FIXTURE(TestPivottable, testFuncGETPIVOTDATA)
{
    m_pDoc->InsertTab(0, u"Data"_ustr);
    m_pDoc->InsertTab(1, u"Table"_ustr);

    // Raw data
    const std::vector<std::vector<const char*>> aData = {
        { "Name", "Value" },
        { "A", "1" },
        { "A", "2" },
        { "A", "3" },
        { "B", "4" },
        { "B", "5" },
        { "B", "6" },
    };

    ScAddress aPos(1,1,0);
    ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("failed to insert range data at correct position", aPos, aDataRange.aStart);

    ScDPObject* pDPObj = nullptr;

    {
        // Dimension definition
        static const DPFieldDef aFields[] = {
            { "Name", sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
            { "Value", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::SUM, false },
        };

        pDPObj = createDPFromRange(m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);
    }

    ScDPCollection* pDPs = m_pDoc->GetDPCollection();
    pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj));
    CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
                           size_t(1), pDPs->GetCount());
    pDPObj->SetName(pDPs->CreateNewName());

    ScRange aOutRange = refresh(pDPObj);
    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Name", "Sum - Value" },
            { "A", "6" },
            { "B", "15" },
            { "Total Result", "21" },
        };

        bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Pivot table created for GETPIVOTDATA");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    aPos = aOutRange.aEnd;
    aPos.IncRow(2); // Move 2 rows down from the table output.

    OUString aPivotPosStr(aOutRange.aStart.Format(ScRefFlags::ADDR_ABS));

    sc::AutoCalcSwitch aSwitch(*m_pDoc, true); // turn autocalc on.

    // First, get the grand total.
    OUString aFormula = "=GETPIVOTDATA(\"Value\";" + aPivotPosStr + ")";
    m_pDoc->SetString(aPos, aFormula);
    double fVal = m_pDoc->GetValue(aPos);
    CPPUNIT_ASSERT_EQUAL(21.0, fVal);

    // Get the subtotal for 'A'.
    aFormula = "=GETPIVOTDATA(\"Value\";" + aPivotPosStr + ";\"Name\";\"A\")";
    m_pDoc->SetString(aPos, aFormula);
    fVal = m_pDoc->GetValue(aPos);
    CPPUNIT_ASSERT_EQUAL(6.0, fVal);

    // Get the subtotal for 'B'.
    aFormula = "=GETPIVOTDATA(\"Value\";" + aPivotPosStr + ";\"Name\";\"B\")";
    m_pDoc->SetString(aPos, aFormula);
    fVal = m_pDoc->GetValue(aPos);
    CPPUNIT_ASSERT_EQUAL(15.0, fVal);

    clearRange(m_pDoc, ScRange(aPos)); // Delete the formula.

    pDPs->FreeTable(pDPObj);

    {
        // Dimension definition
        static const DPFieldDef aFields[] = {
            { "Name", sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
            { "Value", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::SUM, false },
            { "Value", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::COUNT, false },
        };

        pDPObj = createDPFromRange(m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);
    }

    pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj));
    aOutRange = refresh(pDPObj);

    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Name",                "Data",           nullptr   },
            { "A",                   "Sum - Value",   "6"  },
            {  nullptr,                    "Count - Value", "3"  },
            { "B",                   "Sum - Value",  "15"  },
            {  nullptr,                    "Count - Value", "3"  },
            { "Total Sum - Value",   nullptr,               "21" },
            { "Total Count - Value", nullptr,               "6"  },
        };

        bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Pivot table refreshed");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    aPos = aOutRange.aEnd;
    aPos.IncRow(2); // move 2 rows down from the output.

    aPivotPosStr = aOutRange.aStart.Format(ScRefFlags::ADDR_ABS);

    // First, get the grand totals.
    aFormula = "=GETPIVOTDATA(\"Sum - Value\";" + aPivotPosStr + ")";
    m_pDoc->SetString(aPos, aFormula);
    fVal = m_pDoc->GetValue(aPos);
    CPPUNIT_ASSERT_EQUAL(21.0, fVal);
    aFormula = "=GETPIVOTDATA(\"Count - Value\";" + aPivotPosStr + ")";
    m_pDoc->SetString(aPos, aFormula);
    fVal = m_pDoc->GetValue(aPos);
    CPPUNIT_ASSERT_EQUAL(6.0, fVal);

    // Get the subtotals for 'A'.
    aFormula = "=GETPIVOTDATA(\"Sum - Value\";" + aPivotPosStr + ";\"Name\";\"A\")";
    m_pDoc->SetString(aPos, aFormula);
    fVal = m_pDoc->GetValue(aPos);
    CPPUNIT_ASSERT_EQUAL(6.0, fVal);
    aFormula = "=GETPIVOTDATA(\"Count - Value\";" + aPivotPosStr + ";\"Name\";\"A\")";
    m_pDoc->SetString(aPos, aFormula);
    fVal = m_pDoc->GetValue(aPos);
    CPPUNIT_ASSERT_EQUAL(3.0, fVal);

    // Get the subtotals for 'B'.
    aFormula = "=GETPIVOTDATA(\"Sum - Value\";" + aPivotPosStr + ";\"Name\";\"B\")";
    m_pDoc->SetString(aPos, aFormula);
    fVal = m_pDoc->GetValue(aPos);
    CPPUNIT_ASSERT_EQUAL(15.0, fVal);
    aFormula = "=GETPIVOTDATA(\"Count - Value\";" + aPivotPosStr + ";\"Name\";\"B\")";
    m_pDoc->SetString(aPos, aFormula);
    fVal = m_pDoc->GetValue(aPos);
    CPPUNIT_ASSERT_EQUAL(3.0, fVal);

    pDPs->FreeTable(pDPObj);

    CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", size_t(0), pDPs->GetCount());
    CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
                           size_t(0), pDPs->GetSheetCaches().size());

    m_pDoc->DeleteTab(1);
    m_pDoc->DeleteTab(0);
}

CPPUNIT_TEST_FIXTURE(TestPivottable, testFuncGETPIVOTDATALeafAccess)
{
    m_pDoc->InsertTab(0, u"Data"_ustr);
    m_pDoc->InsertTab(1, u"Table"_ustr);

    // Raw data
    const std::vector<std::vector<const char*>> aData = {
        { "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);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("failed to insert range data at correct position", aPos, aDataRange.aStart);

    ScDPObject* pDPObj = nullptr;

    // Dimension definition
    static const DPFieldDef aFields[] = {
        { "Type", sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
        { "Member", sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
        { "Value", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::SUM, false },
    };

    // Create pivot table at A1 on 2nd sheet.
    pDPObj = createDPFromRange(m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false);

    ScDPCollection* pDPs = m_pDoc->GetDPCollection();
    pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj));
    CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
                           size_t(1), pDPs->GetCount());
    pDPObj->SetName(pDPs->CreateNewName());
    ScRange aOutRange = refresh(pDPObj);

    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Type",         "Member",   "Sum - Value" },
            { "A",            "Anna",     "1"           },
            {  nullptr,             "Cecilia",  "3"           },
            { "B",            "Brittany", "2"           },
            {  nullptr,             "Donna",    "4"           },
            { "Total Result",  nullptr,         "10"          },
        };

        bool bSuccess = checkDPTableOutput(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;
    };

    static const 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();

    for (size_t i = 0; i < SAL_N_ELEMENTS(aChecks); ++i)
    {
        FormulaError nErr = m_pDoc->GetErrCode(ScAddress(4,i,1));
        CPPUNIT_ASSERT_EQUAL(sal_uInt16(FormulaError::NONE), static_cast<sal_uInt16>(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.", size_t(0), pDPs->GetCount());
    CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
                           size_t(0), pDPs->GetSheetCaches().size());

    m_pDoc->DeleteTab(1);
    m_pDoc->DeleteTab(0);
}

CPPUNIT_TEST_FIXTURE(TestPivottable, testPivotTableRepeatItemLabels)
{
    /**
     * Test pivot table per-field repeat item labels functionality
     */
    m_pDoc->InsertTab(0, u"Data"_ustr);
    m_pDoc->InsertTab(1, u"Table"_ustr);

    // Dimension definition
    static const DPFieldDef aFields[] = {
        { "Name",  sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, true },
        { "Country", sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
        { "Year", sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
        { "Score", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::NONE, false }
    };

    // Raw data
    const char* aData[][4] = {
        { "Andy",    "US", "1999", "30" },
        { "Andy",    "US", "2002", "20" },
        { "Andy",    "US", "2010", "45" },
        { "David",   "GB", "1998", "12" },
        { "Edward",  "NO", "2000",  "8" },
        { "Frank",   "FR", "2009", "15" },
        { "Frank",   "FR", "2008", "45" },
        { "Frank",   "FR", "2007", "45" },
    };

    size_t nFieldCount = SAL_N_ELEMENTS(aFields);
    size_t const nDataCount = SAL_N_ELEMENTS(aData);

    ScRange aSrcRange = insertDPSourceData(m_pDoc, aFields, nFieldCount, aData, nDataCount);
    SCROW nRow1 = aSrcRange.aStart.Row(), nRow2 = aSrcRange.aEnd.Row();
    SCCOL nCol1 = aSrcRange.aStart.Col(), nCol2 = aSrcRange.aEnd.Col();

    ScDPObject* pDPObj = createDPFromRange(
        m_pDoc, ScRange(nCol1, nRow1, 0, nCol2, nRow2, 0), aFields, nFieldCount, false);

    ScDPCollection* pDPs = m_pDoc->GetDPCollection();
    pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj));
    CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.",
                           size_t(1), pDPs->GetCount());
    pDPObj->SetName(pDPs->CreateNewName());

    bool bOverflow = false;
    ScRange aOutRange = pDPObj->GetNewOutputRange(bOverflow);
    CPPUNIT_ASSERT_MESSAGE("Table overflow!?", !bOverflow);

    pDPObj->Output(aOutRange.aStart);
    aOutRange = pDPObj->GetOutRange();
    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Name",         "Country", "Year", "Sum - Score" },
            { "Andy",         "US",      "1999", "30"          },
            { "Andy",         nullptr,         "2002", "20"          },
            { "Andy",         nullptr,         "2010", "45"          },
            { "David",        "GB",      "1998", "12"          },
            { "Edward",       "NO",      "2000", "8"           },
            { "Frank",        "FR",      "2007", "45"          },
            { "Frank",        nullptr,         "2008", "45"          },
            { "Frank",        nullptr,         "2009", "15"          },
            { "Total Result", nullptr,         nullptr,      "220"         }
        };

        bool bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "DataPilot table output");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be only one data cache.", size_t(1), pDPs->GetSheetCaches().size());

    pDPs->FreeTable(pDPObj);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("There should be no more tables.", size_t(0), pDPs->GetCount());
    CPPUNIT_ASSERT_EQUAL_MESSAGE("There shouldn't be any more cache stored.",
                                 size_t(0), pDPs->GetSheetCaches().size());

    m_pDoc->DeleteTab(1);
    m_pDoc->DeleteTab(0);
}

CPPUNIT_TEST_FIXTURE(TestPivottable, testPivotTableDPCollection)
{
    /**
     * Test DPCollection public methods
     */
    m_pDoc->InsertTab(0, u"Data"_ustr);
    m_pDoc->InsertTab(1, u"Table"_ustr);

    // Dimension definition
    static const DPFieldDef aFields[] = {
        { "Software", sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
        { "Version",  sheet::DataPilotFieldOrientation_COLUMN, ScGeneralFunction::NONE, false },
        { "1.2.3",    sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::NONE, false }
    };

    // Raw data
    const char* aData[][3] = {
        { "LibreOffice", "3.3.0", "30" },
        { "LibreOffice", "3.3.1", "20" },
        { "LibreOffice", "3.4.0", "45" },
    };

    size_t nFieldCount = SAL_N_ELEMENTS(aFields);
    size_t const nDataCount = SAL_N_ELEMENTS(aData);

    ScRange aSrcRange = insertDPSourceData(m_pDoc, aFields, nFieldCount, aData, nDataCount);
    SCROW nRow1 = aSrcRange.aStart.Row(), nRow2 = aSrcRange.aEnd.Row();
    SCCOL nCol1 = aSrcRange.aStart.Col(), nCol2 = aSrcRange.aEnd.Col();
    ScRange aDataRange(nCol1, nRow1, 0, nCol2, nRow2, 0);

    ScDPCollection* pDPs = m_pDoc->GetDPCollection();

    // Check at the beginning
    CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be no DP table", size_t(0), pDPs->GetCount());

    CPPUNIT_ASSERT_EQUAL_MESSAGE("should return nullptr",
                                 static_cast<ScDPObject*>(nullptr), pDPs->GetByName(u"DP1"));
    CPPUNIT_ASSERT_EQUAL_MESSAGE("should return nullptr",
                                 static_cast<ScDPObject*>(nullptr), pDPs->GetByName(u""));

    // Add 2 DP objects
    ScDPObject* pDPObj = createDPFromRange(m_pDoc, aDataRange , aFields, nFieldCount, false);
    pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj));
    pDPObj->SetName(u"DP1"_ustr); // set custom name

    CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one data pilot table.", size_t(1), pDPs->GetCount());

    ScDPObject* pDPObj2 = createDPFromRange(m_pDoc, aDataRange, aFields, nFieldCount, false);
    pDPs->InsertNewTable(std::unique_ptr<ScDPObject>(pDPObj2));
    pDPObj2->SetName(u"DP2"_ustr); // set custom name

    CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be two DP tables", size_t(2), pDPs->GetCount());
    CPPUNIT_ASSERT_EQUAL_MESSAGE("should return first DPObject",
                                 pDPObj, pDPs->GetByName(u"DP1"));
    CPPUNIT_ASSERT_EQUAL_MESSAGE("should return second DPObject",
                                 pDPObj2, pDPs->GetByName(u"DP2"));
    CPPUNIT_ASSERT_EQUAL_MESSAGE("empty string should return nullptr",
                                 static_cast<ScDPObject*>(nullptr), pDPs->GetByName(u""));
    CPPUNIT_ASSERT_EQUAL_MESSAGE("non existent name should return nullptr",
                                 static_cast<ScDPObject*>(nullptr), pDPs->GetByName(u"Non"));
    // Remove first DP Object
    pDPs->FreeTable(pDPObj);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("there should be only one DP table", size_t(1), pDPs->GetCount());

    CPPUNIT_ASSERT_EQUAL_MESSAGE("first DP object was deleted, should return nullptr",
                                 static_cast<ScDPObject*>(nullptr), pDPs->GetByName(u"DP1"));
    CPPUNIT_ASSERT_EQUAL_MESSAGE("should return second DPObject",
                                 pDPObj2, pDPs->GetByName(u"DP2"));
    CPPUNIT_ASSERT_EQUAL_MESSAGE("empty string should return nullptr",
                                 static_cast<ScDPObject*>(nullptr), pDPs->GetByName(u""));
    CPPUNIT_ASSERT_EQUAL_MESSAGE("non existent name should return nullptr",
                                 static_cast<ScDPObject*>(nullptr), pDPs->GetByName(u"Non"));

    // Remove second DP Object
    pDPs->FreeTable(pDPObj2);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("first DP object was deleted, should return nullptr",
                                 static_cast<ScDPObject*>(nullptr), pDPs->GetByName(u"DP1"));
    CPPUNIT_ASSERT_EQUAL_MESSAGE("second DP object was deleted, should return nullptr",
                                 static_cast<ScDPObject*>(nullptr), pDPs->GetByName(u"DP2"));
    CPPUNIT_ASSERT_EQUAL_MESSAGE("empty string should return nullptr",
                                 static_cast<ScDPObject*>(nullptr), pDPs->GetByName(u""));
    CPPUNIT_ASSERT_EQUAL_MESSAGE("non existent name should return nullptr",
                                 static_cast<ScDPObject*>(nullptr), pDPs->GetByName(u"Non"));

    // Clean-up
    m_pDoc->DeleteTab(1);
    m_pDoc->DeleteTab(0);
}

CPPUNIT_TEST_FIXTURE(TestPivottable, testPivotTableMedianFunc)
{
    /**
    * Test pivot table median function
    */
    m_pDoc->InsertTab(0, u"Data"_ustr);
    m_pDoc->InsertTab(1, u"Table"_ustr);

    // Raw data
    const std::vector<std::vector<const char*>> aData = {
        { "Condition", "Day1Hit", "Day1Miss", "Day1FalseAlarm" },
        { "False Memory", "7", "3", "0" },
        { "Control", "10", "0", "1" },
        { "False Memory", "9", "1", "0" },
        { "Control", "9", "1", "2" },
        { "False Memory", "7", "3", "3" },
        { "Control", "10", "0", "0" },
        { "False Memory", "9", "1", "1" },
        { "Control", "6", "4", "2" },
        { "False Memory", "8", "2", "1" },
        { "Control", "7", "3", "3" },
        { "False Memory", "9", "1", "1" },
        { "Control", "10", "0", "0" },
        { "False Memory", "10", "0", "0" },
        { "Control", "10", "0", "0" },
        { "False Memory", "10", "0", "0" },
        { "Control", "9", "1", "1" },
        { "False Memory", "10", "0", "0" },
        { "Control", "10", "0", "0" },
    };

    // Dimension definition
    static const DPFieldDef aFields[] = {
        { "Condition", sheet::DataPilotFieldOrientation_ROW, ScGeneralFunction::NONE, false },
        { "Day1Hit", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::MEDIAN, false },
        { "Day1Miss", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::MEDIAN, false },
        { "Day1FalseAlarm", sheet::DataPilotFieldOrientation_DATA, ScGeneralFunction::MEDIAN, false },
    };

    ScAddress aPos(1, 1, 0);
    ScRange aDataRange = insertRangeData(m_pDoc, aPos, aData);
    CPPUNIT_ASSERT_EQUAL_MESSAGE("failed to insert range data at correct position", aPos, aDataRange.aStart);

    std::unique_ptr<ScDPObject> pDPObj(createDPFromRange(
        m_pDoc, aDataRange, aFields, SAL_N_ELEMENTS(aFields), false));
    CPPUNIT_ASSERT_MESSAGE("Failed to create pivot table object.", pDPObj);

    // Create a new pivot table output.
    ScDBDocFunc aFunc(*m_xDocShell);
    bool bSuccess = aFunc.CreatePivotTable(*pDPObj, false, true);
    CPPUNIT_ASSERT_MESSAGE("Failed to create pivot table output via ScDBDocFunc.", bSuccess);
    ScDPCollection* pDPs = m_pDoc->GetDPCollection();
    CPPUNIT_ASSERT_MESSAGE("Failed to get pivot table collection.", pDPs);
    ScDPObject* pDPObject = &(*pDPs)[0];
    ScRange aOutRange = pDPObject->GetOutRange();
    {
        // Expected output table content.  0 = empty cell
        std::vector<std::vector<const char*>> aOutputCheck = {
            { "Condition", "Data", nullptr },
            { "Control", "Median - Day1Hit", "10" },
            { nullptr, "Median - Day1Miss", "0" },
            { nullptr, "Median - Day1FalseAlarm", "1", },
            { "False Memory", "Median - Day1Hit", "9" },
            { nullptr, "Median - Day1Miss", "1" },
            { nullptr, "Median - Day1FalseAlarm", "0", "0" },
            { "Total Median - Day1Hit", nullptr, "9", nullptr },
            { "Total Median - Day1Miss", nullptr, "1", nullptr },
            { "Total Median - Day1FalseAlarm", nullptr, "0.5", nullptr }
        };

        bSuccess = checkDPTableOutput(m_pDoc, aOutRange, aOutputCheck, "Pivot table created via ScDBDocFunc");
        CPPUNIT_ASSERT_MESSAGE("Table output check failed", bSuccess);
    }

    bSuccess = aFunc.RemovePivotTable(*pDPObject, false, true);
    CPPUNIT_ASSERT_MESSAGE("Failed to remove pivot table object.", bSuccess);

    m_pDoc->DeleteTab(1);
    m_pDoc->DeleteTab(0);
}

CPPUNIT_PLUGIN_IMPLEMENT();

/* vim:set shiftwidth=4 softtabstop=4 expandtab: */