1 /*************************************************************************
2  *
3  * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER.
4  *
5  * Copyright 2000, 2010 Oracle and/or its affiliates.
6  *
7  * OpenOffice.org - a multi-platform office productivity suite
8  *
9  * This file is part of OpenOffice.org.
10  *
11  * OpenOffice.org is free software: you can redistribute it and/or modify
12  * it under the terms of the GNU Lesser General Public License version 3
13  * only, as published by the Free Software Foundation.
14  *
15  * OpenOffice.org is distributed in the hope that it will be useful,
16  * but WITHOUT ANY WARRANTY; without even the implied warranty of
17  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
18  * GNU Lesser General Public License version 3 for more details
19  * (a copy is included in the LICENSE file that accompanied this code).
20  *
21  * You should have received a copy of the GNU Lesser General Public License
22  * version 3 along with OpenOffice.org.  If not, see
23  * <http://www.openoffice.org/license.html>
24  * for a copy of the LGPLv3 License.
25  *
26  ************************************************************************/
27 
28 
29 package ifc.sheet;
30 
31 import java.util.ArrayList;
32 
33 import com.sun.star.beans.XPropertySet;
34 import com.sun.star.container.XIndexAccess;
35 import com.sun.star.container.XNamed;
36 import com.sun.star.lang.IllegalArgumentException;
37 import com.sun.star.sheet.*;
38 import com.sun.star.table.CellAddress;
39 import com.sun.star.table.CellRangeAddress;
40 import com.sun.star.table.XCell;
41 import com.sun.star.table.XCellCursor;
42 import com.sun.star.table.XCellRange;
43 import com.sun.star.uno.AnyConverter;
44 import com.sun.star.uno.UnoRuntime;
45 
46 import lib.MultiMethodTest;
47 import lib.Status;
48 import lib.StatusException;
49 
50 /**
51  * Testing <code>com.sun.star.sheet.XDataPilotTable2</code>
52  * interface methods :
53  * <ul>
54  *  <li><code> getDrillDownData()</code><li>
55  *  <li><code> getPositionData()</code></li>
56  *  <li><code> insertDrillDownSheet()</code></li>
57  *  <li><code> getOutputRangeByType</code></li>
58  * </ul>
59  *
60  * @see com.sun.star.sheet.XDataPilotTable2
61  * @see com.sun.star.table.CellAddress
62  *
63  */
64 public class _XDataPilotTable2 extends MultiMethodTest
65 {
66     private XSpreadsheetDocument xSheetDoc = null;
67     private XDataPilotTable2 xDPTab2 = null;
68     private CellRangeAddress mRangeWhole = null;
69     private CellRangeAddress mRangeTable = null;
70     private CellRangeAddress mRangeResult = null;
71     private ArrayList mDataFieldDims = null;
72     private ArrayList mResultCells = null;
73 
74     /**
75      * exception to be thrown when obtaining a result data for a cell fails
76      * (probably because the cell is not a result cell).
77      */
78     private class ResultCellFailure extends com.sun.star.uno.Exception {}
79 
80     protected void before()
81     {
82         Object o = tEnv.getObjRelation("DATAPILOTTABLE2");
83         xDPTab2 = (XDataPilotTable2)UnoRuntime.queryInterface(
84             XDataPilotTable2.class, o);
85 
86         if (xDPTab2 == null)
87             throw new StatusException(Status.failed("Relation not found"));
88 
89         xSheetDoc = (XSpreadsheetDocument)tEnv.getObjRelation("SHEETDOCUMENT");
90 
91         getOutputRanges();
92         buildDataFields();
93         try
94         {
95             buildResultCells();
96         }
97         catch (ResultCellFailure e)
98         {
99             e.printStackTrace(log);
100             throw new StatusException( "Failed to build result cells.", e);
101         }
102     }
103 
104     public void _getDrillDownData()
105     {
106         boolean testResult = true;
107         int cellCount = mResultCells.size();
108         for (int i = 0; i < cellCount; ++i)
109         {
110             CellAddress addr = (CellAddress)mResultCells.get(i);
111             DataPilotTablePositionData posData = xDPTab2.getPositionData(addr);
112             DataPilotTableResultData resData = (DataPilotTableResultData)posData.PositionData;
113             int dim = ((Integer)mDataFieldDims.get(resData.DataFieldIndex)).intValue();
114             DataResult res = resData.Result;
115             double val = res.Value;
116 
117             Object[][] data = xDPTab2.getDrillDownData(addr);
118             double sum = 0.0;
119             if (data.length > 1)
120             {
121                 for (int row = 1; row < data.length; ++row)
122                 {
123                     Object o = data[row][dim];
124                     if (AnyConverter.isDouble(o))
125                         sum += ((Double)o).doubleValue();
126                 }
127             }
128             log.println(formatCell(addr) + ": " + data.length + " rows (" + (data.length-1) + " records)");
129 
130             if (val != sum)
131                 testResult = false;
132         }
133         tRes.tested("getDrillDownData()", testResult);
134     }
135 
136     public void _getPositionData()
137     {
138         boolean testResult = false;
139 
140         do
141         {
142             CellAddress addr = new CellAddress();
143             addr.Sheet  = mRangeTable.Sheet;
144 
145             boolean rangeGood = true;
146             for (int x = mRangeTable.StartColumn; x <= mRangeTable.EndColumn && rangeGood; ++x)
147             {
148                 for (int y = mRangeTable.StartRow; y <= mRangeTable.EndRow && rangeGood; ++y)
149                 {
150                     addr.Column = x;
151                     addr.Row = y;
152                     log.println("checking " + formatCell(addr));
153                     DataPilotTablePositionData posData = xDPTab2.getPositionData(addr);
154                     if (posData.PositionType == DataPilotTablePositionType.NOT_IN_TABLE)
155                     {
156                         log.println("specified cell address not in table: " + formatCell(addr));
157                         rangeGood = false;
158                         continue;
159                     }
160 
161                     switch (posData.PositionType)
162                     {
163                     case DataPilotTablePositionType.NOT_IN_TABLE:
164                         break;
165                     case DataPilotTablePositionType.COLUMN_HEADER:
166                         printHeaderData(posData);
167                         break;
168                     case DataPilotTablePositionType.ROW_HEADER:
169                         printHeaderData(posData);
170                         break;
171                     case DataPilotTablePositionType.RESULT:
172                         printResultData(posData);
173                         break;
174                     case DataPilotTablePositionType.OTHER:
175                         break;
176                     default:
177                         log.println("unknown position");
178                     }
179                 }
180             }
181 
182             if (!rangeGood)
183             {
184                 log.println("table range check failed");
185                 break;
186             }
187 
188             testResult = true;
189         }
190         while (false);
191 
192         tRes.tested("getPositionData()", testResult);
193     }
194 
195     public void _insertDrillDownSheet()
196     {
197         boolean testResult = true;
198         int cellCount = mResultCells.size();
199         XSpreadsheets xSheets = xSheetDoc.getSheets();
200         XIndexAccess xIA = (XIndexAccess)UnoRuntime.queryInterface(
201             XIndexAccess.class, xSheets);
202         int sheetCount = xIA.getCount();
203         for (int i = 0; i < cellCount && testResult; ++i)
204         {
205             CellAddress addr = (CellAddress)mResultCells.get(i);
206 
207             Object[][] data = xDPTab2.getDrillDownData(addr);
208 
209             // sheet is always inserted at the current sheet position.
210             xDPTab2.insertDrillDownSheet(addr);
211 
212             int newSheetCount = xIA.getCount();
213             if (newSheetCount == sheetCount + 1)
214             {
215                 log.println("drill-down sheet for " + formatCell(addr) + " inserted");
216                 if (data.length < 2)
217                 {
218                     // There is no data for this result.  It should never have
219                     // inserted a drill-down sheet.
220                     log.println("new sheet inserted; however, there is no data for this result");
221                     testResult = false;
222                     continue;
223                 }
224 
225                 // Retrieve the object of the sheet just inserted.
226                 XSpreadsheet xSheet = null;
227                 try
228                 {
229                     xSheet = (XSpreadsheet)UnoRuntime.queryInterface(
230                         XSpreadsheet.class, xIA.getByIndex(addr.Sheet));
231                 }
232                 catch (com.sun.star.uno.Exception e)
233                 {
234                     e.printStackTrace();
235                     throw new StatusException("Failed to get the spreadsheet object.", e);
236                 }
237 
238                 // Check the integrity of the data on the inserted sheet.
239                 if (!checkDrillDownSheetContent(xSheet, data))
240                 {
241                     log.println("dataintegrity check on the inserted sheet failed");
242                     testResult = false;
243                     continue;
244                 }
245 
246                 log.println("  sheet data integrity check passed");
247 
248                 // Remove the sheet just inserted.
249 
250                 XNamed xNamed = (XNamed)UnoRuntime.queryInterface(XNamed.class, xSheet);
251                 String name = xNamed.getName();
252                 try
253                 {
254                     xSheets.removeByName(name);
255                 }
256                 catch (com.sun.star.uno.Exception e)
257                 {
258                     e.printStackTrace();
259                     throw new StatusException("Failed to removed the inserted sheet named " + name + ".", e);
260                 }
261             }
262             else if (newSheetCount == sheetCount)
263             {
264                 if (data.length > 1)
265                 {
266                     // There is data for this result.  It should have inserted
267                     // a new sheet.
268                     log.println("no new sheet is inserted, despite the data being present.");
269                     testResult = false;
270                 }
271             }
272             else
273             {
274                 log.println("what just happened!?");
275                 testResult = false;
276             }
277         }
278 
279         tRes.tested("insertDrillDownSheet()", testResult);
280     }
281 
282     public void _getOutputRangeByType()
283     {
284         boolean testResult = false;
285 
286         do
287         {
288             // Let's make sure this doesn't cause a crash.  A range returned for an
289             // out-of-bound condition is undefined.
290             try
291             {
292                 CellRangeAddress rangeOutOfBound = xDPTab2.getOutputRangeByType(-1);
293                 log.println("exception not raised");
294                 break;
295             }
296             catch (IllegalArgumentException e)
297             {
298                 log.println("exception raised on invalid range type (good)");
299             }
300 
301             try
302             {
303                 CellRangeAddress rangeOutOfBound = xDPTab2.getOutputRangeByType(100);
304                 log.println("exception not raised");
305                 break;
306             }
307             catch (IllegalArgumentException e)
308             {
309                 log.println("exception raised on invalid range type (good)");
310             }
311 
312             // Check to make sure the whole range is not empty.
313             if (mRangeWhole.EndColumn - mRangeWhole.StartColumn <= 0 ||
314                 mRangeWhole.EndRow - mRangeWhole.EndColumn <= 0)
315             {
316                 log.println("whole range is empty");
317                 break;
318             }
319 
320             log.println("whole range is not empty (good)");
321 
322             // Table range must be of equal width with the whole range, and the same
323             // bottom.
324             if (mRangeTable.Sheet != mRangeWhole.Sheet ||
325                 mRangeTable.StartColumn != mRangeWhole.StartColumn ||
326                 mRangeTable.EndColumn != mRangeWhole.EndColumn ||
327                 mRangeTable.EndRow != mRangeWhole.EndRow)
328             {
329                 log.println("table range is incorrect");
330                 break;
331             }
332 
333             log.println("table range is correct");
334 
335             // Result range must be smaller than the table range, and must share the
336             // same lower-right corner.
337             if (mRangeResult.Sheet != mRangeTable.Sheet ||
338                 mRangeResult.StartColumn < mRangeTable.StartColumn ||
339                 mRangeResult.StartRow < mRangeTable.StartRow ||
340                 mRangeResult.EndColumn != mRangeTable.EndColumn ||
341                 mRangeResult.EndRow != mRangeTable.EndRow)
342                 break;
343 
344             log.println("result range is correct");
345 
346             testResult = true;
347         }
348         while (false);
349 
350         tRes.tested("getOutputRangeByType()", testResult);
351     }
352 
353     private void printHeaderData(DataPilotTablePositionData posData)
354     {
355         DataPilotTableHeaderData header = (DataPilotTableHeaderData)posData.PositionData;
356         String posType = "";
357         if (posData.PositionType == DataPilotTablePositionType.COLUMN_HEADER)
358             posType = "column header";
359         else if (posData.PositionType == DataPilotTablePositionType.ROW_HEADER)
360             posType = "row header";
361 
362         log.println(posType + "; member name: " + header.MemberName + "; dimension: " +
363                     header.Dimension + "; hierarchy: " + header.Hierarchy +
364                     "; level: " + header.Level);
365     }
366 
367     private void printResultData(DataPilotTablePositionData posData)
368     {
369         DataPilotTableResultData resultData = (DataPilotTableResultData)posData.PositionData;
370         int dataId = resultData.DataFieldIndex;
371         DataResult res = resultData.Result;
372         double val = res.Value;
373         int flags = res.Flags;
374         int filterCount = resultData.FieldFilters.length;
375         log.println("result; data field index: " + dataId + "; value: " + val + "; flags: " + flags +
376                     "; filter count: " + filterCount);
377 
378         for (int i = 0; i < filterCount; ++i)
379         {
380             DataPilotFieldFilter fil = resultData.FieldFilters[i];
381             log.println("  field name: " + fil.FieldName + "; match value: " + fil.MatchValue);
382         }
383     }
384 
385     private String formatCell(CellAddress addr)
386     {
387         String str = "(" + addr.Column + "," + addr.Row + ")";
388         return str;
389     }
390 
391     private void printRange(String text, CellRangeAddress rangeAddr)
392     {
393         log.println(text + ": (" + rangeAddr.StartColumn + "," + rangeAddr.StartRow + ") - (" +
394                     rangeAddr.EndColumn + "," + rangeAddr.EndRow + ")");
395     }
396 
397     private void buildResultCells() throws ResultCellFailure
398     {
399         if (mResultCells != null)
400             return;
401 
402         getOutputRanges();
403 
404         mResultCells = new ArrayList();
405         for (int x = mRangeResult.StartColumn; x <= mRangeResult.EndColumn; ++x)
406         {
407             for (int y = mRangeResult.StartRow; y <= mRangeResult.EndRow; ++y)
408             {
409                 CellAddress addr = new CellAddress();
410                 addr.Sheet = mRangeResult.Sheet;
411                 addr.Column = x;
412                 addr.Row = y;
413                 DataPilotTablePositionData posData = xDPTab2.getPositionData(addr);
414                 if (posData.PositionType != DataPilotTablePositionType.RESULT)
415                 {
416                     log.println(formatCell(addr) + ": this is not a result cell");
417                     throw new ResultCellFailure();
418                 }
419                 mResultCells.add(addr);
420             }
421         }
422     }
423 
424     private void buildDataFields()
425     {
426         mDataFieldDims = new ArrayList();
427         XDataPilotDescriptor xDesc = (XDataPilotDescriptor)UnoRuntime.queryInterface(
428             XDataPilotDescriptor.class, xDPTab2);
429 
430         XIndexAccess xFields = xDesc.getDataPilotFields();
431         int fieldCount = xFields.getCount();
432         for (int i = 0; i < fieldCount; ++i)
433         {
434             try
435             {
436                 Object field = xFields.getByIndex(i);
437                 XPropertySet propSet = (XPropertySet)UnoRuntime.queryInterface(
438                     XPropertySet.class, field);
439                 DataPilotFieldOrientation orient =
440                     (DataPilotFieldOrientation)propSet.getPropertyValue("Orientation");
441                 if (orient == DataPilotFieldOrientation.DATA)
442                 {
443                     Integer item = new Integer(i);
444                     mDataFieldDims.add(item);
445                 }
446             }
447             catch (com.sun.star.uno.Exception e)
448             {
449                 e.printStackTrace(log);
450                 throw new StatusException( "Failed to get a field.", e);
451             }
452         }
453     }
454 
455     private void getOutputRanges()
456     {
457         if (mRangeWhole != null && mRangeTable != null && mRangeResult != null)
458             return;
459 
460         try
461         {
462             mRangeWhole = xDPTab2.getOutputRangeByType(DataPilotOutputRangeType.WHOLE);
463             printRange("whole range ", mRangeWhole);
464             mRangeTable = xDPTab2.getOutputRangeByType(DataPilotOutputRangeType.TABLE);
465             printRange("table range ", mRangeTable);
466             mRangeResult = xDPTab2.getOutputRangeByType(DataPilotOutputRangeType.RESULT);
467             printRange("result range", mRangeResult);
468         }
469         catch (IllegalArgumentException e)
470         {
471             e.printStackTrace(log);
472             throw new StatusException( "Failed to get output range by type.", e);
473         }
474     }
475 
476     private boolean checkDrillDownSheetContent(XSpreadsheet xSheet, Object[][] data)
477     {
478         CellAddress lastCell = getLastUsedCellAddress(xSheet, 0, 0);
479         if (data.length <= 0 || lastCell.Row == 0 || lastCell.Column == 0)
480         {
481             log.println("empty data condition");
482             return false;
483         }
484 
485         if (data.length != lastCell.Row + 1 || data[0].length != lastCell.Column + 1)
486         {
487             log.println("data size differs");
488             return false;
489         }
490 
491         XCellRange xCR = null;
492         try
493         {
494             xCR = xSheet.getCellRangeByPosition(0, 0, lastCell.Column, lastCell.Row);
495         }
496         catch (com.sun.star.lang.IndexOutOfBoundsException e)
497         {
498             return false;
499         }
500 
501         XCellRangeData xCRD = (XCellRangeData)UnoRuntime.queryInterface(
502             XCellRangeData.class, xCR);
503 
504         Object[][] sheetData = xCRD.getDataArray();
505         for (int x = 0; x < sheetData.length; ++x)
506         {
507             for (int y = 0; y < sheetData[x].length; ++y)
508             {
509                 Object cell1 = sheetData[x][y];
510                 Object cell2 = data[x][y];
511                 if (AnyConverter.isString(cell1) && AnyConverter.isString(cell2))
512                 {
513                     String s1 = (String)cell1, s2 = (String)(cell2);
514                     if (!s1.equals(s2))
515                     {
516                         log.println("string cell values differ");
517                         return false;
518                     }
519                 }
520                 else if (AnyConverter.isDouble(cell1) && AnyConverter.isDouble(cell2))
521                 {
522                     double f1 = 0.0, f2 = 0.0;
523                     try
524                     {
525                         f1 = AnyConverter.toDouble(cell1);
526                         f2 = AnyConverter.toDouble(cell2);
527                     }
528                     catch (com.sun.star.lang.IllegalArgumentException e)
529                     {
530                         log.println("failed to convert cells to double");
531                         return false;
532                     }
533 
534                     if (f1 != f2)
535                     {
536                         log.println("numerical cell values differ");
537                         return false;
538                     }
539                 }
540                 else
541                 {
542                     log.println("cell types differ");
543                     return false;
544                 }
545             }
546         }
547 
548         return true;
549     }
550 
551     private CellAddress getLastUsedCellAddress(XSpreadsheet xSheet, int nCol, int nRow)
552     {
553         try
554         {
555             XCellRange xRng = xSheet.getCellRangeByPosition(nCol, nRow, nCol, nRow);
556             XSheetCellRange xSCR = (XSheetCellRange)UnoRuntime.queryInterface(
557                 XSheetCellRange.class, xRng);
558 
559             XSheetCellCursor xCursor = xSheet.createCursorByRange(xSCR);
560             XCellCursor xCellCursor = (XCellCursor)UnoRuntime.queryInterface(
561                 XCellCursor.class, xCursor);
562 
563             xCellCursor.gotoEnd();
564             XCell xCell = xCursor.getCellByPosition(0, 0);
565             XCellAddressable xCellAddr = (XCellAddressable)UnoRuntime.queryInterface(
566                 XCellAddressable.class, xCell);
567 
568             return xCellAddr.getCellAddress();
569         }
570         catch (com.sun.star.lang.IndexOutOfBoundsException ex)
571         {
572         }
573         return null;
574     }
575 }
576 
577