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