1 /************************************************************** 2 * 3 * Licensed to the Apache Software Foundation (ASF) under one 4 * or more contributor license agreements. See the NOTICE file 5 * distributed with this work for additional information 6 * regarding copyright ownership. The ASF licenses this file 7 * to you under the Apache License, Version 2.0 (the 8 * "License"); you may not use this file except in compliance 9 * with the License. You may obtain a copy of the License at 10 * 11 * http://www.apache.org/licenses/LICENSE-2.0 12 * 13 * Unless required by applicable law or agreed to in writing, 14 * software distributed under the License is distributed on an 15 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY 16 * KIND, either express or implied. See the License for the 17 * specific language governing permissions and limitations 18 * under the License. 19 * 20 *************************************************************/ 21 22 /** 23 * 24 */ 25 package fvt.gui.sc.subtotals; 26 27 import static org.junit.Assert.*; 28 import static org.openoffice.test.common.Testspace.*; 29 import static org.openoffice.test.vcl.Tester.*; 30 import static testlib.gui.AppTool.*; 31 import static testlib.gui.UIMap.*; 32 33 import org.junit.After; 34 import org.junit.Before; 35 import org.junit.Rule; 36 import org.junit.Test; 37 import org.openoffice.test.common.Logger; 38 39 import testlib.gui.SCTool; 40 41 /** 42 * 43 * 44 */ 45 public class SubtotalsFunctions { 46 47 @Rule 48 public Logger log = Logger.getLogger(this); 49 50 @Before 51 public void setUp() throws Exception { 52 app.start(true); 53 String file = prepareData("sc/SubtotalsSampleFile.ods"); 54 app.dispatch(".uno:Open"); 55 submitOpenDlg(file); 56 calc.waitForExistence(10, 2); 57 SCTool.selectRange("A1:E7"); 58 app.dispatch(".uno:DataSubTotals"); 59 } 60 61 @After 62 public void tearDown() throws Exception { 63 64 } 65 66 /** 67 * Just support en-US language Verify Average function in SubTotals 68 */ 69 @Test 70 public void testAverage() { 71 scSubTotalsGroupByListBox.select(4); // "Team" 72 // SCCalcSubTotalForColumns.click(10, 25); // In different platform, can 73 // not focus on same checkbox 74 scCalcSubTotalForColumns.select(1); 75 scCalcSubTotalForColumns.check(1); // "Code" 76 sleep(1); 77 scCalcSubTotolsFuncionList.select(2); // "Average" 78 scSubTotalsGroup1Dialog.ok(); 79 sleep(1); 80 81 assertArrayEquals("Subtotal table", new String[][] { { "Level", "Code", "No.", "Team", "Name" }, { "BS", "20", "2", "A", "Chcomic" }, { "CS", "30", "5", "A", "Ally" }, 82 { "MS", "10", "1", "A", "Joker" }, { "", "20", "", "A Average", "" }, { "BS", "20", "4", "B", "Elle" }, { "MS", "10", "3", "B", "Kevin" }, 83 { "", "15", "", "B Average", "" }, { "BS", "20", "6", "C", "Sweet" }, { "", "20", "", "C Average", "" }, { "", "18.33333333", "", "Grand Total", "" } }, 84 SCTool.getCellTexts("A1:E11")); 85 } 86 87 /** 88 * Just support en-US language Verify Count Numbers only function in 89 * SubTotals 90 */ 91 @Test 92 public void testCountNumbersOnly() { 93 // SCCalcSubTotalForColumns.click(10, 45); 94 scCalcSubTotalForColumns.select(2); 95 scCalcSubTotalForColumns.check(2); // "No." 96 scCalcSubTotolsFuncionList.select(6); // "Count (numbers only)" 97 scSubTotalsGroup1Dialog.ok(); 98 sleep(1); 99 100 assertArrayEquals("Subtotal table", new String[][] { { "Level", "Code", "No.", "Team", "Name" }, { "BS", "20", "4", "B", "Elle" }, { "BS", "20", "6", "C", "Sweet" }, 101 { "BS", "20", "2", "A", "Chcomic" }, { "BS Count", "", "3", "", "" }, { "CS", "30", "5", "A", "Ally" }, { "CS Count", "", "1", "", "" }, 102 { "MS", "10", "1", "A", "Joker" }, { "MS", "10", "3", "B", "Kevin" }, { "MS Count", "", "2", "", "" }, { "Grand Total", "", "6", "", "" } }, 103 SCTool.getCellTexts("A1:E11")); 104 } 105 106 /** 107 * Just support en-US language Verify Max Numbers function in SubTotals 108 */ 109 @Test 110 public void testMax() { 111 // SCCalcSubTotalForColumns.click(10, 45); 112 scCalcSubTotalForColumns.select(2); 113 scCalcSubTotalForColumns.check(2); // "No." 114 scCalcSubTotolsFuncionList.select(3); // "Max" 115 scSubTotalsGroup1Dialog.ok(); 116 sleep(1); 117 118 assertArrayEquals("Subtotal table", new String[][] { { "Level", "Code", "No.", "Team", "Name" }, { "BS", "20", "4", "B", "Elle" }, { "BS", "20", "6", "C", "Sweet" }, 119 { "BS", "20", "2", "A", "Chcomic" }, { "BS Max", "", "6", "", "" }, { "CS", "30", "5", "A", "Ally" }, { "CS Max", "", "5", "", "" }, 120 { "MS", "10", "1", "A", "Joker" }, { "MS", "10", "3", "B", "Kevin" }, { "MS Max", "", "3", "", "" }, { "Grand Total", "", "6", "", "" } }, 121 SCTool.getCellTexts("A1:E11")); 122 } 123 124 /** 125 * Just support en-US language Verify Min Numbers function in SubTotals 126 */ 127 @Test 128 public void testMin() { 129 // SCCalcSubTotalForColumns.click(10, 45); 130 scCalcSubTotalForColumns.select(2); 131 scCalcSubTotalForColumns.check(2); // "No." 132 scCalcSubTotolsFuncionList.select(4); // "Min" 133 scSubTotalsGroup1Dialog.ok(); 134 sleep(1); 135 136 assertArrayEquals("Subtotal table", new String[][] { { "Level", "Code", "No.", "Team", "Name" }, { "BS", "20", "4", "B", "Elle" }, { "BS", "20", "6", "C", "Sweet" }, 137 { "BS", "20", "2", "A", "Chcomic" }, { "BS Min", "", "2", "", "" }, { "CS", "30", "5", "A", "Ally" }, { "CS Min", "", "5", "", "" }, 138 { "MS", "10", "1", "A", "Joker" }, { "MS", "10", "3", "B", "Kevin" }, { "MS Min", "", "1", "", "" }, { "Grand Total", "", "1", "", "" } }, 139 SCTool.getCellTexts("A1:E11")); 140 } 141 142 /** 143 * Just support en-US language Verify Product function in SubTotals 144 */ 145 @Test 146 public void testProduct() { 147 // SCCalcSubTotalForColumns.click(10, 45); 148 scCalcSubTotalForColumns.select(2); 149 scCalcSubTotalForColumns.check(2); // "No." 150 scCalcSubTotolsFuncionList.select(5); // "Product" 151 scSubTotalsGroup1Dialog.ok(); 152 sleep(1); 153 154 assertArrayEquals("Subtotal table", new String[][] { { "Level", "Code", "No.", "Team", "Name" }, { "BS", "20", "4", "B", "Elle" }, { "BS", "20", "6", "C", "Sweet" }, 155 { "BS", "20", "2", "A", "Chcomic" }, { "BS Product", "", "48", "", "" }, { "CS", "30", "5", "A", "Ally" }, { "CS Product", "", "5", "", "" }, 156 { "MS", "10", "1", "A", "Joker" }, { "MS", "10", "3", "B", "Kevin" }, { "MS Product", "", "3", "", "" }, { "Grand Total", "", "720", "", "" } }, 157 SCTool.getCellTexts("A1:E11")); 158 } 159 160 /** 161 * Just support en-US language Verify StDevP (Population) function in 162 * SubTotals 163 */ 164 @Test 165 public void testStDevPPopulation() { 166 // SCCalcSubTotalForColumns.click(10, 45); 167 scCalcSubTotalForColumns.select(2); 168 scCalcSubTotalForColumns.check(2); // "No." 169 scCalcSubTotolsFuncionList.select(8); // "StDevP (Population)" 170 scSubTotalsGroup1Dialog.ok(); 171 sleep(1); 172 173 assertArrayEquals("Subtotal table", new String[][] { { "Level", "Code", "No.", "Team", "Name" }, { "BS", "20", "4", "B", "Elle" }, { "BS", "20", "6", "C", "Sweet" }, 174 { "BS", "20", "2", "A", "Chcomic" }, { "BS StDev", "", "1.63299316", "", "" }, { "CS", "30", "5", "A", "Ally" }, { "CS StDev", "", "0", "", "" }, 175 { "MS", "10", "1", "A", "Joker" }, { "MS", "10", "3", "B", "Kevin" }, { "MS StDev", "", "1", "", "" }, { "Grand Total", "", "1.70782513", "", "" } }, 176 SCTool.getCellTexts("A1:E11")); 177 } 178 179 /** 180 * Just support en-US language Verify sum function in SubTotals 181 */ 182 @Test 183 public void testSum() { 184 // SCCalcSubTotalForColumns.click(10, 45); 185 scCalcSubTotalForColumns.select(2); 186 scCalcSubTotalForColumns.check(2); // "No." 187 scCalcSubTotolsFuncionList.select(0); // "Sum" 188 scSubTotalsGroup1Dialog.ok(); 189 sleep(1); 190 191 assertArrayEquals("Subtotal table", new String[][] { { "Level", "Code", "No.", "Team", "Name" }, { "BS", "20", "4", "B", "Elle" }, { "BS", "20", "6", "C", "Sweet" }, 192 { "BS", "20", "2", "A", "Chcomic" }, { "BS Sum", "", "12", "", "" }, { "CS", "30", "5", "A", "Ally" }, { "CS Sum", "", "5", "", "" }, 193 { "MS", "10", "1", "A", "Joker" }, { "MS", "10", "3", "B", "Kevin" }, { "MS Sum", "", "4", "", "" }, { "Grand Total", "", "21", "", "" } }, 194 SCTool.getCellTexts("A1:E11")); 195 } 196 197 /** 198 * Just support en-US language Verify Var Sample function in SubTotals 199 */ 200 @Test 201 public void testVarSample() { 202 // SCCalcSubTotalForColumns.click(10, 45); 203 scCalcSubTotalForColumns.select(2); 204 scCalcSubTotalForColumns.check(2); // "No." 205 scCalcSubTotolsFuncionList.select(9); // "Var (Sample)" 206 scSubTotalsGroup1Dialog.ok(); 207 sleep(1); 208 209 assertArrayEquals("Subtotal table", new String[][] { { "Level", "Code", "No.", "Team", "Name" }, { "BS", "20", "4", "B", "Elle" }, { "BS", "20", "6", "C", "Sweet" }, 210 { "BS", "20", "2", "A", "Chcomic" }, { "BS Var", "", "4", "", "" }, { "CS", "30", "5", "A", "Ally" }, { "CS Var", "", "#DIV/0!", "", "" }, 211 { "MS", "10", "1", "A", "Joker" }, { "MS", "10", "3", "B", "Kevin" }, { "MS Var", "", "2", "", "" }, { "Grand Total", "", "3.5", "", "" } }, 212 SCTool.getCellTexts("A1:E11")); 213 } 214 215 /** 216 * Just support en-US language Verify Don't sort in Sub totals 217 */ 218 @Test 219 public void testDoNotSortOption() { 220 scSubTotalsGroupByListBox.select(4); // "Team" 221 // SCCalcSubTotalForColumns.click(10, 25); 222 scCalcSubTotalForColumns.select(1); 223 scCalcSubTotalForColumns.check(1); // "Code" 224 scSubTotalsOptionsTabPage.select(); 225 scSubtotalsPreSortToGroupCheckBox.uncheck(); 226 scSubTotalsOptionsTabPage.ok(); 227 sleep(1); 228 229 assertArrayEquals("Subtotal table", new String[][] { { "Level", "Code", "No.", "Team", "Name" }, { "BS", "20", "4", "B", "Elle" }, { "", "20", "", "B Sum", "" }, 230 { "BS", "20", "6", "C", "Sweet" }, { "", "20", "", "C Sum", "" }, { "BS", "20", "2", "A", "Chcomic" }, { "CS", "30", "5", "A", "Ally" }, 231 { "MS", "10", "1", "A", "Joker" }, { "", "60", "", "A Sum", "" }, { "MS", "10", "3", "B", "Kevin" }, { "", "10", "", "B Sum", "" }, 232 { "", "110", "", "Grand Total", "" } }, SCTool.getCellTexts("A1:E12")); 233 } 234 235 /** 236 * Just support en-US language Verify SubTotals Options default UI 237 */ 238 @Test 239 public void testDefaultUI() { 240 assertArrayEquals("Group List: ", new String[] { "- none -", "Level", "Code", "No.", "Team", "Name" }, scSubTotalsGroupByListBox.getItemsText()); 241 242 scSubTotalsOptionsTabPage.select(); 243 assertFalse(scSubtotalsInsertPageBreakCheckBox.isChecked()); 244 assertFalse(scSubtotalsCaseSensitiveCheckBox.isChecked()); 245 assertTrue(scSubtotalsPreSortToGroupCheckBox.isChecked()); 246 assertTrue(scSubtotalSortAscendingRadioButton.isChecked()); 247 assertFalse(scSubtotalSortDescendingRadioButton.isChecked()); 248 assertFalse(scSubtotalsIncludeFormatsCheckBox.isChecked()); 249 assertFalse(scSubtotalsCustomSortOrderCheckBox.isChecked()); 250 assertFalse(scSubtotalsCustomSortListBox.isEnabled()); 251 scSubTotalsOptionsTabPage.ok(); 252 } 253 254 /** 255 * Just support en-US language Verify Subtotals using all group 256 */ 257 @Test 258 public void testUsingAllGroup() { 259 scSubTotalsGroup1Dialog.select(); 260 scSubTotalsGroupByListBox.select(1); // "Level" 261 // SCCalcSubTotalForColumns.click(10, 45); 262 scCalcSubTotalForColumns.select(2); 263 scCalcSubTotalForColumns.check(2); // "No." 264 scSubTotalsGroup2Dialog.select(); 265 scSubTotalsGroupByListBox.select(4); // "Team" 266 // SCCalcSubTotalForColumns.click(10, 25); 267 scCalcSubTotalForColumns.select(1); 268 scCalcSubTotalForColumns.check(1); // "Code" 269 scCalcSubTotolsFuncionList.select(3); // "Max" 270 scSubTotalsGroup3Dialog.select(); 271 scSubTotalsGroupByListBox.select(5); // "Name" 272 // SCCalcSubTotalForColumns.click(10, 25); 273 scCalcSubTotalForColumns.select(1); 274 scCalcSubTotalForColumns.check(1); // "Code" 275 scSubTotalsGroup1Dialog.select(); 276 scSubTotalsGroup1Dialog.ok(); 277 sleep(1); 278 279 assertArrayEquals("Subtotal table", new String[][] { { "Level", "Code", "No.", "Team", "Name" }, { "BS", "20", "2", "A", "Chcomic" }, { "", "20", "", "", "Chcomic Sum" }, 280 { "", "20", "", "A Max", "" }, { "BS", "20", "4", "B", "Elle" }, { "", "20", "", "", "Elle Sum" }, { "", "20", "", "B Max", "" }, 281 { "BS", "20", "6", "C", "Sweet" }, { "", "20", "", "", "Sweet Sum" }, { "", "20", "", "C Max", "" }, { "BS Sum", "", "12", "", "" }, 282 { "CS", "30", "5", "A", "Ally" }, { "", "30", "", "", "Ally Sum" }, { "", "30", "", "A Max", "" }, { "CS Sum", "", "5", "", "" }, 283 { "MS", "10", "1", "A", "Joker" }, { "", "10", "", "", "Joker Sum" }, { "", "10", "", "A Max", "" }, { "MS", "10", "3", "B", "Kevin" }, 284 { "", "10", "", "", "Kevin Sum" }, { "", "10", "", "B Max", "" }, { "MS Sum", "", "4", "", "" }, { "Grand Total", "", "21", "", "" } }, 285 SCTool.getCellTexts("A1:E23")); 286 } 287 288 /** 289 * Just support en-US language Verify Subtotals_Recalculate and refresh 290 * results when data rows deleted. 291 */ 292 @Test 293 public void testRecalculateAfterRowDeleted() { 294 // SCCalcSubTotalForColumns.click(10,45); 295 scCalcSubTotalForColumns.select(2); 296 scCalcSubTotalForColumns.check(2); // "No." 297 scSubTotalsGroup1Dialog.ok(); 298 sleep(1); 299 300 assertArrayEquals("Subtotal table before deleting row", new String[][] { { "Level", "Code", "No.", "Team", "Name" }, { "BS", "20", "4", "B", "Elle" }, 301 { "BS", "20", "6", "C", "Sweet" }, { "BS", "20", "2", "A", "Chcomic" }, { "BS Sum", "", "12", "", "" }, { "CS", "30", "5", "A", "Ally" }, 302 { "CS Sum", "", "5", "", "" }, { "MS", "10", "1", "A", "Joker" }, { "MS", "10", "3", "B", "Kevin" }, { "MS Sum", "", "4", "", "" }, 303 { "Grand Total", "", "21", "", "" } }, SCTool.getCellTexts("A1:E11")); 304 305 SCTool.selectRange("A3:E3"); 306 app.dispatch(".uno:DeleteCell"); 307 scDeleteCellsDeleteRowsRadioButton.check(); 308 scDeleteCellsDialog.ok(); 309 sleep(1); 310 311 assertArrayEquals("Subtotal table after deleting row", new String[][] { { "Level", "Code", "No.", "Team", "Name" }, { "BS", "20", "4", "B", "Elle" }, 312 { "BS", "20", "2", "A", "Chcomic" }, { "BS Sum", "", "6", "", "" }, { "CS", "30", "5", "A", "Ally" }, { "CS Sum", "", "5", "", "" }, 313 { "MS", "10", "1", "A", "Joker" }, { "MS", "10", "3", "B", "Kevin" }, { "MS Sum", "", "4", "", "" }, { "Grand Total", "", "15", "", "" }, { "", "", "", "", "" } }, 314 SCTool.getCellTexts("A1:E11")); 315 316 } 317 318 /** 319 * Verify Recalculates when source data changed with Average function in 320 * SubTotals 321 */ 322 @Test 323 public void testRecalculateWhenDataChanged() { 324 scSubTotalsGroupByListBox.select(4); // "Team" 325 // SCCalcSubTotalForColumns.click(10,25); 326 scCalcSubTotalForColumns.select(1); 327 scCalcSubTotalForColumns.check(1); // "Code" 328 scCalcSubTotolsFuncionList.select(2); // "Average" 329 scSubTotalsGroup1Dialog.ok(); 330 sleep(1); 331 332 SCTool.selectRange("B4"); 333 typeKeys("40" + "<enter>"); 334 sleep(1); 335 assertEquals("30", SCTool.getCellText("B5")); 336 assertEquals("23.33333333", SCTool.getCellText("B11")); 337 338 SCTool.selectRange("B7"); 339 typeKeys("50" + "<enter>"); 340 sleep(1); 341 assertEquals("35", SCTool.getCellText("B8")); 342 assertEquals("30", SCTool.getCellText("B11")); 343 344 SCTool.selectRange("B9"); 345 typeKeys("30" + "<enter>"); 346 sleep(1); 347 assertEquals("30", SCTool.getCellText("B10")); 348 assertEquals("31.66666667", SCTool.getCellText("B11")); 349 } 350 } 351