New HintId and unit test for hidden rows and SUBTOTAL, tdf#93171 follow-up

Adding new Hintid for HideRows so that we notify only formulas with subtotal
and aggregate function for recalculation. Added unit testing.

Change-Id: I44f2e45acaf697f91744bc8202f27b218faa5b43

7 files changed, 52 insertions, 6 deletions

diff --git a/include/svl/hint.hxx b/include/svl/hint.hxx
index 2c94c4529300..f7f0c73e062e 100644
--- a/include/svl/hint.hxx
+++ b/include/svl/hint.hxx
@@ -85,6 +85,7 @@ enum class SfxHintId {
     ScRefModeChanged,
     ScKillEditView,
     ScKillEditViewNoPaint,
+    ScHiddenRowsChanged,
 
 // SC accessibility hints
     ScAccTableChanged,
diff --git a/sc/inc/column.hxx b/sc/inc/column.hxx
index e163a1e295a1..0c195eb316d0 100644
--- a/sc/inc/column.hxx
+++ b/sc/inc/column.hxx
@@ -588,7 +588,7 @@ public:
 
     void Broadcast( SCROW nRow );
     void BroadcastCells( const std::vector<SCROW>& rRows, SfxHintId nHint );
-    void BroadcastRows( SCROW nStartRow, SCROW nEndRow );
+    void BroadcastRows( SCROW nStartRow, SCROW nEndRow, SfxHintId nHint );
 
     // cell notes
     ScPostIt* GetCellNote( SCROW nRow );
diff --git a/sc/qa/unit/ucalc.hxx b/sc/qa/unit/ucalc.hxx
index 732ab5b1ec3d..5d1def66c7c7 100644
--- a/sc/qa/unit/ucalc.hxx
+++ b/sc/qa/unit/ucalc.hxx
@@ -530,6 +530,7 @@ public:
     void testPrecisionAsShown();
     void testProtectedSheetEditByRow();
     void testProtectedSheetEditByColumn();
+    void testFuncRowsHidden();
 
     CPPUNIT_TEST_SUITE(Test);
     CPPUNIT_TEST(testCollator);
@@ -800,6 +801,7 @@ public:
     CPPUNIT_TEST(testPrecisionAsShown);
     CPPUNIT_TEST(testProtectedSheetEditByRow);
     CPPUNIT_TEST(testProtectedSheetEditByColumn);
+    CPPUNIT_TEST(testFuncRowsHidden);
     CPPUNIT_TEST_SUITE_END();
 
 private:
diff --git a/sc/qa/unit/ucalc_formula.cxx b/sc/qa/unit/ucalc_formula.cxx
index 3290a7eb89a3..cfae1a45a00d 100644
--- a/sc/qa/unit/ucalc_formula.cxx
+++ b/sc/qa/unit/ucalc_formula.cxx
@@ -7866,4 +7866,48 @@ void Test::testIntersectionOpExcel()
     m_pDoc->DeleteTab(0);
 }
 
+//Test Subtotal and Aggregate during hide rows #tdf93171
+void Test::testFuncRowsHidden()
+{
+    sc::AutoCalcSwitch aACSwitch(*m_pDoc, true); // turn auto calc on.
+    m_pDoc->InsertTab(0, "Test");
+    m_pDoc->SetValue(0, 0, 0, 1); //A1
+    m_pDoc->SetValue(0, 1, 0, 2); //A2
+    m_pDoc->SetValue(0, 2, 0, 4); //A3
+    m_pDoc->SetValue(0, 3, 0, 8); //A4
+    m_pDoc->SetValue(0, 4, 0, 16); //A5
+    m_pDoc->SetValue(0, 5, 0, 32); //A6
+
+    ScAddress aPos(0,6,0);
+    m_pDoc->SetString(aPos, "=SUBTOTAL(109; A1:A6)");
+    CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUBTOTAL failed", 63.0, m_pDoc->GetValue(aPos));
+    //Hide row 1
+    m_pDoc->SetRowHidden(0, 0, 0, true);
+    CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUBTOTAL failed", 62.0, m_pDoc->GetValue(aPos));
+    m_pDoc->SetRowHidden(0, 0, 0, false);
+    //Hide row 2 and 3
+    m_pDoc->SetRowHidden(1, 2, 0, true);
+    CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUBTOTAL failed", 57.0, m_pDoc->GetValue(aPos));
+    m_pDoc->SetRowHidden(1, 2, 0, false);
+    CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUBTOTAL failed", 63.0, m_pDoc->GetValue(aPos));
+
+    m_pDoc->SetString(aPos, "=AGGREGATE(9; 5; A1:A6)"); //9=SUM 5=Ignore only hidden rows
+    CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of AGGREGATE failed", 63.0, m_pDoc->GetValue(aPos));
+    //Hide row 1
+    m_pDoc->SetRowHidden(0, 0, 0, true);
+    CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of AGGREGATE failed", 62.0, m_pDoc->GetValue(aPos));
+    m_pDoc->SetRowHidden(0, 0, 0, false);
+    //Hide rows 3 to 5
+    m_pDoc->SetRowHidden(2, 4, 0, true);
+    CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of AGGREGATE failed", 35.0, m_pDoc->GetValue(aPos));
+    m_pDoc->SetRowHidden(2, 4, 0, false);
+    CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of AGGREGATE failed", 63.0, m_pDoc->GetValue(aPos));
+
+    m_pDoc->SetString(aPos, "=SUM(A1:A6)");
+    m_pDoc->SetRowHidden(2, 4, 0, true);
+    CPPUNIT_ASSERT_EQUAL_MESSAGE("Calculation of SUM failed", 63.0, m_pDoc->GetValue(aPos));
+
+    m_pDoc->DeleteTab(0);
+}
+
 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/sc/source/core/data/column3.cxx b/sc/source/core/data/column3.cxx
index 97759267945d..7942ad881bc0 100644
--- a/sc/source/core/data/column3.cxx
+++ b/sc/source/core/data/column3.cxx
@@ -88,16 +88,15 @@ void ScColumn::BroadcastCells( const std::vector<SCROW>& rRows, SfxHintId nHint
     }
 }
 
-void ScColumn::BroadcastRows( SCROW nStartRow, SCROW nEndRow )
+void ScColumn::BroadcastRows( SCROW nStartRow, SCROW nEndRow, SfxHintId nHint )
 {
     sc::SingleColumnSpanSet aSpanSet;
     aSpanSet.scan(*this, nStartRow, nEndRow);
     std::vector<SCROW> aRows;
     aSpanSet.getRows(aRows);
-    BroadcastCells(aRows, SfxHintId::ScDataChanged);
+    BroadcastCells(aRows, nHint);
 }
 
-
 struct DirtyCellInterpreter
 {
     void operator() (size_t, ScFormulaCell* p)
diff --git a/sc/source/core/data/formulacell.cxx b/sc/source/core/data/formulacell.cxx
index 552f73e83c32..18eb260db938 100644
--- a/sc/source/core/data/formulacell.cxx
+++ b/sc/source/core/data/formulacell.cxx
@@ -2275,7 +2275,7 @@ void ScFormulaCell::Notify( const SfxHint& rHint )
 
     if ( pDocument->GetHardRecalcState() == ScDocument::HARDRECALCSTATE_OFF )
     {
-        if (nHint == SfxHintId::ScDataChanged || nHint == SfxHintId::ScTableOpDirty)
+        if (nHint == SfxHintId::ScDataChanged || nHint == SfxHintId::ScTableOpDirty || (bSubTotal && nHint == SfxHintId::ScHiddenRowsChanged))
         {
             bool bForceTrack = false;
             if ( nHint == SfxHintId::ScTableOpDirty )
diff --git a/sc/source/core/data/table5.cxx b/sc/source/core/data/table5.cxx
index 9342a7401a06..c7a3b7938b68 100644
--- a/sc/source/core/data/table5.cxx
+++ b/sc/source/core/data/table5.cxx
@@ -595,7 +595,7 @@ bool ScTable::SetRowHidden(SCROW nStartRow, SCROW nEndRow, bool bHidden)
             SetStreamValid(false);
         for (SCCOL i = 0; i < aCol.size(); i++)
         {
-            aCol[i].BroadcastRows(nStartRow, nEndRow);
+            aCol[i].BroadcastRows(nStartRow, nEndRow, SfxHintId::ScHiddenRowsChanged);
         }
     }