tdf#162262 sc add "Summary below data" option for Subtotal dialog

With this option we can set where the summary rows should appear,
above or below the datas when we create a new Subtotal area. The default
option is "True" (which means the summary rows are below the datas)
when we create a new subtotal area. Unless if we already have one subtotal
area on the same sheet, in that case the sheet level property, "TotalsRowBelow"
contains where should the summary rows be for the new Subtotal's.

TODO: add new ODF xml attribute for Summary below

Change-Id: Icf86c85041d75c24919cb528846d5bb2b517ca78
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/175556
Tested-by: Jenkins
Reviewed-by: Balazs Varga <balazs.varga.extern@allotropia.de>

4 files changed, 121 insertions, 0 deletions

diff --git a/sc/qa/uitest/data/tdf162262.ods b/sc/qa/uitest/data/tdf162262.ods
new file mode 100644
index 000000000000..138348366298
--- /dev/null
+++ b/sc/qa/uitest/data/tdf162262.ods
diff --git a/sc/qa/uitest/sort/subtotals.py b/sc/qa/uitest/sort/subtotals.py
index b824dcab98c8..6eddd1bc03b4 100644
--- a/sc/qa/uitest/sort/subtotals.py
+++ b/sc/qa/uitest/sort/subtotals.py
@@ -137,4 +137,113 @@ class Subtotals(UITestCase):
             self.assertEqual(get_cell_by_position(calc_doc, 0, 0, 1).getValue(), 1)
             self.assertEqual(get_cell_by_position(calc_doc, 0, 1, 1).getValue(), 2)
 
+    def test_tdf162262(self):
+        with self.ui_test.load_file(get_url_for_data_file("tdf162262.ods")) as calc_doc:
+            XcalcDoc = self.xUITest.getTopFocusWindow()
+            gridwin = XcalcDoc.getChild("grid_window")
+            # One group level
+            # Select cell range
+            gridwin.executeAction("SELECT", mkPropertyValues({"RANGE": "A1:C15"}))
+            # Select from the menu bar Data
+            # Select option subtotal
+            # Subtotal dialog displays
+            with self.ui_test.execute_dialog_through_command(".uno:DataSubTotals") as xDialog:
+                # Select group by: Day
+                xGroupBy = xDialog.getChild("group_by1")
+                select_by_text(xGroupBy, "Day")
+                # Select 'Calculate subtotals for' -> Value 1 and Value 2
+                xCheckListMenu = xDialog.getChild("grid1")
+                xTreeList = xCheckListMenu.getChild("columns1")
+                xFirstEntry = xTreeList.getChild("1")
+                xFirstEntry.executeAction("CLICK", tuple())
+                xFirstEntry = xTreeList.getChild("2")
+                xFirstEntry.executeAction("CLICK", tuple())
+
+                # Select tab options
+                xTabs = xDialog.getChild("tabcontrol")
+                select_pos(xTabs, "3")
+                # Unselect option Summary below -> false
+                xSummarybelow = xDialog.getChild("summarybelow")
+                xSummarybelow.executeAction("CLICK", tuple())
+                # apply with OK
+
+            self.assertEqual(get_cell_by_position(calc_doc, 0, 0, 1).getString(), "Grand Sum")
+            self.assertEqual(get_cell_by_position(calc_doc, 0, 1, 1).getValue(), 105)
+            self.assertEqual(get_cell_by_position(calc_doc, 0, 2, 1).getValue(), 119)
+
+            self.assertEqual(get_cell_by_position(calc_doc, 0, 0, 2).getString(), "Friday Result")
+            self.assertEqual(get_cell_by_position(calc_doc, 0, 1, 2).getValue(), 19)
+            self.assertEqual(get_cell_by_position(calc_doc, 0, 2, 2).getValue(), 21)
+
+            self.assertEqual(get_cell_by_position(calc_doc, 0, 0, 20).getString(), "Wednesday Result")
+            self.assertEqual(get_cell_by_position(calc_doc, 0, 1, 20).getValue(), 11)
+            self.assertEqual(get_cell_by_position(calc_doc, 0, 2, 20).getValue(), 13)
+
+    def test_tdf162262_multi(self):
+        with self.ui_test.load_file(get_url_for_data_file("tdf162262.ods")) as calc_doc:
+            XcalcDoc = self.xUITest.getTopFocusWindow()
+            gridwin = XcalcDoc.getChild("grid_window")
+            # Multi group level
+            # Select cell range
+            gridwin.executeAction("SELECT", mkPropertyValues({"RANGE": "A1:C15"}))
+            # Select from the menu bar Data
+            # Select option subtotal
+            # Subtotal dialog displays
+            with self.ui_test.execute_dialog_through_command(".uno:DataSubTotals") as xDialog:
+                # Select group by 1: Day
+                xGroupBy = xDialog.getChild("group_by1")
+                select_by_text(xGroupBy, "Day")
+                # Select 'Calculate subtotals for' -> Value 1
+                xCheckListMenu = xDialog.getChild("grid1")
+                xTreeList = xCheckListMenu.getChild("columns1")
+                # Select 1 column
+                xFirstEntry = xTreeList.getChild("1")
+                xFirstEntry.executeAction("CLICK", tuple())
+
+                # Select tab Group by 2
+                xTabs = xDialog.getChild("tabcontrol")
+                select_pos(xTabs, "1")
+
+                # Select group by 2: Day
+                xGroupBy = xDialog.getChild("group_by2")
+                select_by_text(xGroupBy, "Day")
+                # Select 'Calculate subtotals for' -> Value 2
+                xCheckListMenu = xDialog.getChild("grid2")
+                xTreeList = xCheckListMenu.getChild("columns2")
+                # Select second column
+                xFirstEntry = xTreeList.getChild("2")
+                xFirstEntry.executeAction("CLICK", tuple())
+
+                # Select tab options
+                xTabs = xDialog.getChild("tabcontrol")
+                select_pos(xTabs, "3")
+                # Unselect option Summary below -> false
+                xSummarybelow = xDialog.getChild("summarybelow")
+                xSummarybelow.executeAction("CLICK", tuple())
+                # apply with OK
+
+            self.assertEqual(get_cell_by_position(calc_doc, 0, 0, 1).getString(), "Grand Sum")
+            self.assertEqual(get_cell_by_position(calc_doc, 0, 1, 1).getValue(), 0)
+            self.assertEqual(get_cell_by_position(calc_doc, 0, 2, 1).getValue(), 119)
+
+            self.assertEqual(get_cell_by_position(calc_doc, 0, 0, 2).getString(), "Grand Sum")
+            self.assertEqual(get_cell_by_position(calc_doc, 0, 1, 2).getValue(), 105)
+            self.assertEqual(get_cell_by_position(calc_doc, 0, 2, 2).getValue(), 0)
+
+            self.assertEqual(get_cell_by_position(calc_doc, 0, 0, 3).getString(), "Friday Sum")
+            self.assertEqual(get_cell_by_position(calc_doc, 0, 1, 3).getValue(), 0)
+            self.assertEqual(get_cell_by_position(calc_doc, 0, 2, 3).getValue(), 21)
+
+            self.assertEqual(get_cell_by_position(calc_doc, 0, 0, 4).getString(), "Friday Sum")
+            self.assertEqual(get_cell_by_position(calc_doc, 0, 1, 4).getValue(), 19)
+            self.assertEqual(get_cell_by_position(calc_doc, 0, 2, 4).getValue(), 0)
+
+            self.assertEqual(get_cell_by_position(calc_doc, 0, 0, 27).getString(), "Wednesday Sum")
+            self.assertEqual(get_cell_by_position(calc_doc, 0, 1, 27).getValue(), 0)
+            self.assertEqual(get_cell_by_position(calc_doc, 0, 2, 27).getValue(), 13)
+
+            self.assertEqual(get_cell_by_position(calc_doc, 0, 0, 28).getString(), "Wednesday Sum")
+            self.assertEqual(get_cell_by_position(calc_doc, 0, 1, 28).getValue(), 11)
+            self.assertEqual(get_cell_by_position(calc_doc, 0, 2, 28).getValue(), 0)
+
 # vim: set shiftwidth=4 softtabstop=4 expandtab:
diff --git a/sc/qa/unit/data/xlsx/subtotal-above.xlsx b/sc/qa/unit/data/xlsx/subtotal-above.xlsx
new file mode 100644
index 000000000000..b271dbab19be
--- /dev/null
+++ b/sc/qa/unit/data/xlsx/subtotal-above.xlsx
diff --git a/sc/qa/unit/subsequent_export_test4.cxx b/sc/qa/unit/subsequent_export_test4.cxx
index e6b5a6389af3..fdb3b6f9a876 100644
--- a/sc/qa/unit/subsequent_export_test4.cxx
+++ b/sc/qa/unit/subsequent_export_test4.cxx
@@ -398,6 +398,18 @@ CPPUNIT_TEST_FIXTURE(ScExportTest4, testTdf81470)
     assertXPath(pHeaders, "/x:headers/x:header[3]", "userName", u"Kohei Yoshida");
 }
 
+CPPUNIT_TEST_FIXTURE(ScExportTest4, testTdf162262)
+{
+    createScDoc("xlsx/subtotal-above.xlsx");
+
+    save(u"Calc Office Open XML"_ustr);
+
+    xmlDocUniquePtr pSheet = parseExport(u"xl/worksheets/sheet1.xml"_ustr);
+    CPPUNIT_ASSERT(pSheet);
+
+    assertXPath(pSheet, "/x:worksheet/x:sheetPr/x:outlinePr", "summaryBelow", u"0");
+}
+
 CPPUNIT_TEST_FIXTURE(ScExportTest4, testTdf122331)
 {
     createScDoc("ods/tdf122331.ods");