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