LibreOffice Module sc (master)  1
doubleref.cxx
Go to the documentation of this file.
1 /* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
2 /*
3  * This file is part of the LibreOffice project.
4  *
5  * This Source Code Form is subject to the terms of the Mozilla Public
6  * License, v. 2.0. If a copy of the MPL was not distributed with this
7  * file, You can obtain one at http://mozilla.org/MPL/2.0/.
8  *
9  * This file incorporates work covered by the following license notice:
10  *
11  * Licensed to the Apache Software Foundation (ASF) under one or more
12  * contributor license agreements. See the NOTICE file distributed
13  * with this work for additional information regarding copyright
14  * ownership. The ASF licenses this file to you under the Apache
15  * License, Version 2.0 (the "License"); you may not use this file
16  * except in compliance with the License. You may obtain a copy of
17  * the License at http://www.apache.org/licenses/LICENSE-2.0 .
18  */
19 
20 #include <doubleref.hxx>
21 #include <global.hxx>
22 #include <document.hxx>
23 #include <queryparam.hxx>
24 #include <queryentry.hxx>
25 #include <globstr.hrc>
26 #include <scresid.hxx>
27 #include <scmatrix.hxx>
28 
29 #include <svl/sharedstringpool.hxx>
30 #include <osl/diagnose.h>
31 #include <unotools/charclass.hxx>
32 
33 #include <memory>
34 #include <utility>
35 #include <vector>
36 
37 using ::std::unique_ptr;
38 using ::std::vector;
39 
40 namespace {
41 
42 void lcl_uppercase(OUString& rStr)
43 {
44  rStr = ScGlobal::getCharClass().uppercase(rStr.trim());
45 }
46 
47 bool lcl_createStarQuery(
48  const ScDocument* pDoc,
49  svl::SharedStringPool& rPool, ScQueryParamBase* pParam, const ScDBRangeBase* pDBRef, const ScDBRangeBase* pQueryRef)
50 {
51  // A valid StarQuery must be at least 4 columns wide. To be precise it
52  // should be exactly 4 columns ...
53  // Additionally, if this wasn't checked, a formula pointing to a valid 1-3
54  // column Excel style query range immediately left to itself would result
55  // in a circular reference when the field name or operator or value (first
56  // to third query range column) is obtained (#i58354#). Furthermore, if the
57  // range wasn't sufficiently specified data changes wouldn't flag formula
58  // cells for recalculation.
59 
60  if (pQueryRef->getColSize() < 4)
61  return false;
62 
63  bool bValid;
64  OUString aCellStr;
65  SCSIZE nIndex = 0;
66  SCROW nRow = 0;
67  SCROW nRows = pDBRef->getRowSize();
68  SCSIZE nNewEntries = static_cast<SCSIZE>(nRows);
69  pParam->Resize(nNewEntries);
70 
71  do
72  {
73  ScQueryEntry& rEntry = pParam->GetEntry(nIndex);
74 
75  bValid = false;
76 
77  if (nIndex > 0)
78  {
79  // For all entries after the first one, check the and/or connector in the first column.
80  aCellStr = pQueryRef->getString(0, nRow);
81  lcl_uppercase(aCellStr);
82  if ( aCellStr == ScResId(STR_TABLE_AND) )
83  {
84  rEntry.eConnect = SC_AND;
85  bValid = true;
86  }
87  else if ( aCellStr == ScResId(STR_TABLE_OR) )
88  {
89  rEntry.eConnect = SC_OR;
90  bValid = true;
91  }
92  }
93 
94  if ((nIndex < 1) || bValid)
95  {
96  // field name in the 2nd column.
97  aCellStr = pQueryRef->getString(1, nRow);
98  SCCOL nField = pDBRef->findFieldColumn(aCellStr); // TODO: must be case insensitive comparison.
99  if (pDoc->ValidCol(nField))
100  {
101  rEntry.nField = nField;
102  bValid = true;
103  }
104  else
105  bValid = false;
106  }
107 
108  if (bValid)
109  {
110  // equality, non-equality operator in the 3rd column.
111  aCellStr = pQueryRef->getString(2, nRow);
112  lcl_uppercase(aCellStr);
113  const sal_Unicode* p = aCellStr.getStr();
114  if (p[0] == '<')
115  {
116  if (p[1] == '>')
117  rEntry.eOp = SC_NOT_EQUAL;
118  else if (p[1] == '=')
119  rEntry.eOp = SC_LESS_EQUAL;
120  else
121  rEntry.eOp = SC_LESS;
122  }
123  else if (p[0] == '>')
124  {
125  if (p[1] == '=')
126  rEntry.eOp = SC_GREATER_EQUAL;
127  else
128  rEntry.eOp = SC_GREATER;
129  }
130  else if (p[0] == '=')
131  rEntry.eOp = SC_EQUAL;
132 
133  }
134 
135  if (bValid)
136  {
137  // Finally, the right-hand-side value in the 4th column.
138  rEntry.GetQueryItem().maString =
139  rPool.intern(pQueryRef->getString(3, nRow));
140  rEntry.bDoQuery = true;
141  }
142  nIndex++;
143  nRow++;
144  }
145  while (bValid && (nRow < nRows) /* && (nIndex < MAXQUERY) */ );
146  return bValid;
147 }
148 
149 bool lcl_createExcelQuery(
150  const ScDocument* pDoc,
151  svl::SharedStringPool& rPool, ScQueryParamBase* pParam, const ScDBRangeBase* pDBRef, const ScDBRangeBase* pQueryRef)
152 {
153  bool bValid = true;
154  SCCOL nCols = pQueryRef->getColSize();
155  SCROW nRows = pQueryRef->getRowSize();
156  vector<SCCOL> aFields(nCols);
157  SCCOL nCol = 0;
158  while (bValid && (nCol < nCols))
159  {
160  OUString aQueryStr = pQueryRef->getString(nCol, 0);
161  SCCOL nField = pDBRef->findFieldColumn(aQueryStr);
162  if (pDoc->ValidCol(nField))
163  aFields[nCol] = nField;
164  else
165  bValid = false;
166  ++nCol;
167  }
168 
169  if (bValid)
170  {
171  // Count the number of visible cells (excluding the header row). Each
172  // visible cell corresponds with a single query.
173  SCSIZE nVisible = pQueryRef->getVisibleDataCellCount();
174  if ( nVisible > SCSIZE_MAX / sizeof(void*) )
175  {
176  OSL_FAIL("too many filter criteria");
177  nVisible = 0;
178  }
179 
180  SCSIZE nNewEntries = nVisible;
181  pParam->Resize( nNewEntries );
182 
183  SCSIZE nIndex = 0;
184  SCROW nRow = 1;
185  OUString aCellStr;
186  while (nRow < nRows)
187  {
188  nCol = 0;
189  while (nCol < nCols)
190  {
191  aCellStr = pQueryRef->getString(nCol, nRow);
192  aCellStr = ScGlobal::getCharClass().uppercase( aCellStr );
193  if (!aCellStr.isEmpty())
194  {
195  if (nIndex < nNewEntries)
196  {
197  pParam->GetEntry(nIndex).nField = aFields[nCol];
198  pParam->FillInExcelSyntax(rPool, aCellStr, nIndex, nullptr);
199  nIndex++;
200  if (nIndex < nNewEntries)
201  pParam->GetEntry(nIndex).eConnect = SC_AND;
202  }
203  else
204  bValid = false;
205  }
206  nCol++;
207  }
208  nRow++;
209  if (nIndex < nNewEntries)
210  pParam->GetEntry(nIndex).eConnect = SC_OR;
211  }
212  }
213  return bValid;
214 }
215 
216 bool lcl_fillQueryEntries(
217  const ScDocument* pDoc,
218  svl::SharedStringPool& rPool, ScQueryParamBase* pParam, const ScDBRangeBase* pDBRef, const ScDBRangeBase* pQueryRef)
219 {
220  SCSIZE nCount = pParam->GetEntryCount();
221  for (SCSIZE i = 0; i < nCount; ++i)
222  pParam->GetEntry(i).Clear();
223 
224  // Standard QueryTabelle
225  bool bValid = lcl_createStarQuery(pDoc, rPool, pParam, pDBRef, pQueryRef);
226  // Excel QueryTabelle
227  if (!bValid)
228  bValid = lcl_createExcelQuery(pDoc, rPool, pParam, pDBRef, pQueryRef);
229 
230  nCount = pParam->GetEntryCount();
231  if (bValid)
232  {
233  // bQueryByString must be set
234  for (SCSIZE i = 0; i < nCount; ++i)
236  }
237  else
238  {
239  // nothing
240  for (SCSIZE i = 0; i < nCount; ++i)
241  pParam->GetEntry(i).Clear();
242  }
243  return bValid;
244 }
245 
246 }
247 
249  mpDoc(pDoc)
250 {
251 }
252 
254 {
255 }
256 
258 {
259  if (!pDBRef)
260  return false;
261 
262  return lcl_fillQueryEntries(getDoc(), getDoc()->GetSharedStringPool(), pParam, pDBRef, this);
263 }
264 
266 {
267  pParam->bHasHeader = true;
268  pParam->bByRow = true;
269  pParam->bInplace = true;
270  pParam->bCaseSens = false;
272  pParam->bDuplicate = true;
273 }
274 
276  ScDBRangeBase(pDoc), maRange(rRange)
277 {
278 }
279 
281 {
282 }
283 
285 {
286  return maRange.aEnd.Col() - maRange.aStart.Col() + 1;
287 }
288 
290 {
291  return maRange.aEnd.Row() - maRange.aStart.Row() + 1;
292 }
293 
295 {
296  SCCOL nCols = getColSize();
297  SCROW nRows = getRowSize();
298  if (nRows <= 1)
299  return 0;
300 
301  return (nRows-1)*nCols;
302 }
303 
304 OUString ScDBInternalRange::getString(SCCOL nCol, SCROW nRow) const
305 {
306  OUString aStr;
307  const ScAddress& s = maRange.aStart;
308  // #i109200# this is used in formula calculation, use GetInputString, not GetString
309  // (consistent with ScDBInternalRange::getCellString)
310  // GetStringForFormula is not used here, to allow querying for date values.
311  getDoc()->GetInputString(s.Col() + nCol, s.Row() + nRow, maRange.aStart.Tab(), aStr);
312  return aStr;
313 }
314 
316 {
317  return getRange().aStart.Col();
318 }
319 
321 {
322  const ScRange& rRange = getRange();
323  const ScAddress& s = rRange.aStart;
324 
325  SCCOL nDBCol1 = s.Col();
326 
327  // Don't handle out-of-bound condition here. We'll do that later.
328  return nIndex + nDBCol1 - 1;
329 }
330 
331 SCCOL ScDBInternalRange::findFieldColumn(const OUString& rStr, FormulaError* pErr) const
332 {
333  const ScAddress& s = maRange.aStart;
334  const ScAddress& e = maRange.aEnd;
335  OUString aUpper = rStr;
336  lcl_uppercase(aUpper);
337 
338  SCCOL nDBCol1 = s.Col();
339  SCROW nDBRow1 = s.Row();
340  SCTAB nDBTab1 = s.Tab();
341  SCCOL nDBCol2 = e.Col();
342 
343  bool bFound = false;
344 
345  OUString aCellStr;
346  ScAddress aLook( nDBCol1, nDBRow1, nDBTab1 );
347  while (!bFound && (aLook.Col() <= nDBCol2))
348  {
349  FormulaError nErr = getDoc()->GetStringForFormula( aLook, aCellStr );
350  if (pErr)
351  *pErr = nErr;
352  lcl_uppercase(aCellStr);
353  bFound = ScGlobal::GetTransliteration().isEqual(aCellStr, aUpper);
354  if (!bFound)
355  aLook.IncCol();
356  }
357  SCCOL nField = aLook.Col();
358 
359  return bFound ? nField : -1;
360 }
361 
362 std::unique_ptr<ScDBQueryParamBase> ScDBInternalRange::createQueryParam(const ScDBRangeBase* pQueryRef) const
363 {
364  unique_ptr<ScDBQueryParamInternal> pParam(new ScDBQueryParamInternal);
365 
366  // Set the database range first.
367  const ScAddress& s = maRange.aStart;
368  const ScAddress& e = maRange.aEnd;
369  pParam->nCol1 = s.Col();
370  pParam->nRow1 = s.Row();
371  pParam->nCol2 = e.Col();
372  pParam->nRow2 = e.Row();
373  pParam->nTab = s.Tab();
374 
375  fillQueryOptions(pParam.get());
376 
377  // Now construct the query entries from the query range.
378  if (!pQueryRef->fillQueryEntries(pParam.get(), this))
379  return nullptr;
380 
381  return std::unique_ptr<ScDBQueryParamBase>(std::move(pParam));
382 }
383 
384 bool ScDBInternalRange::isRangeEqual(const ScRange& rRange) const
385 {
386  return maRange == rRange;
387 }
388 
390  ScDBRangeBase(pDoc), mpMatrix(pMat)
391 {
392  SCSIZE nC, nR;
393  mpMatrix->GetDimensions(nC, nR);
394  mnCols = static_cast<SCCOL>(nC);
395  mnRows = static_cast<SCROW>(nR);
396 }
397 
399 {
400 }
401 
403 {
404  return mnCols;
405 }
406 
408 {
409  return mnRows;
410 }
411 
413 {
414  SCCOL nCols = getColSize();
415  SCROW nRows = getRowSize();
416  if (nRows <= 1)
417  return 0;
418 
419  return (nRows-1)*nCols;
420 }
421 
422 OUString ScDBExternalRange::getString(SCCOL nCol, SCROW nRow) const
423 {
424  if (nCol >= mnCols || nRow >= mnRows)
425  return OUString();
426 
427  return mpMatrix->GetString(nCol, nRow).getString();
428 }
429 
431 {
432  return 0;
433 }
434 
436 {
437  return nIndex - 1;
438 }
439 
440 SCCOL ScDBExternalRange::findFieldColumn(const OUString& rStr, FormulaError* pErr) const
441 {
442  if (pErr)
443  *pErr = FormulaError::NONE;
444 
445  OUString aUpper = rStr;
446  lcl_uppercase(aUpper);
447  for (SCCOL i = 0; i < mnCols; ++i)
448  {
449  OUString aUpperVal = mpMatrix->GetString(i, 0).getString();
450  lcl_uppercase(aUpperVal);
451  if (aUpper == aUpperVal)
452  return i;
453  }
454  return -1;
455 }
456 
457 std::unique_ptr<ScDBQueryParamBase> ScDBExternalRange::createQueryParam(const ScDBRangeBase* pQueryRef) const
458 {
459  unique_ptr<ScDBQueryParamMatrix> pParam(new ScDBQueryParamMatrix);
460  pParam->mpMatrix = mpMatrix;
461  fillQueryOptions(pParam.get());
462 
463  // Now construct the query entries from the query range.
464  if (!pQueryRef->fillQueryEntries(pParam.get(), this))
465  return nullptr;
466 
467  return std::unique_ptr<ScDBQueryParamBase>(std::move(pParam));
468 }
469 
470 bool ScDBExternalRange::isRangeEqual(const ScRange& /*rRange*/) const
471 {
472  return false;
473 }
474 
475 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
ScDBInternalRange(ScDocument *pDoc, const ScRange &rRange)
Definition: doubleref.cxx:275
virtual SCCOL getColSize() const override
Definition: doubleref.cxx:284
sal_Int32 nIndex
const ScRange & getRange() const
Definition: doubleref.hxx:97
ScAddress aStart
Definition: address.hxx:499
SharedString intern(const OUString &rStr)
static void fillQueryOptions(ScQueryParamBase *pParam)
Populate query options that are always the same for all database queries.
Definition: doubleref.cxx:265
SC_DLLPUBLIC void GetInputString(SCCOL nCol, SCROW nRow, SCTAB nTab, OUString &rString)
Definition: document.cxx:3571
OUString ScResId(TranslateId aId)
Definition: scdll.cxx:89
SCROW Row() const
Definition: address.hxx:261
virtual SCCOL getColSize() const =0
const Item & GetQueryItem() const
Definition: queryentry.hxx:84
virtual std::unique_ptr< ScDBQueryParamBase > createQueryParam(const ScDBRangeBase *pQueryRef) const override
Definition: doubleref.cxx:457
virtual SCSIZE getVisibleDataCellCount() const override
Definition: doubleref.cxx:294
SCCOLROW nField
Definition: queryentry.hxx:60
ScAddress aEnd
Definition: address.hxx:500
ScDBRangeBase()=delete
sal_uInt16 sal_Unicode
const ScMatrixRef mpMatrix
Definition: doubleref.hxx:173
virtual SCCOL findFieldColumn(SCCOL nIndex) const override
Get a 0-based column index that corresponds with the passed field index.
Definition: doubleref.cxx:320
size_t SCSIZE
size_t typedef to be able to find places where code was changed from USHORT to size_t and is used to ...
Definition: address.hxx:44
virtual SCSIZE getVisibleDataCellCount() const =0
int nCount
SC_DLLPUBLIC SCSIZE GetEntryCount() const
Definition: queryparam.cxx:120
SC_DLLPUBLIC const ScQueryEntry & GetEntry(SCSIZE n) const
Definition: queryparam.cxx:125
::boost::intrusive_ptr< ScMatrix > ScMatrixRef
Definition: types.hxx:25
SCTAB Tab() const
Definition: address.hxx:270
void FillInExcelSyntax(svl::SharedStringPool &rPool, const OUString &aCellStr, SCSIZE nIndex, SvNumberFormatter *pFormatter)
Definition: queryparam.cxx:224
virtual OUString getString(SCCOL nCol, SCROW nRow) const override
Get a string value of a specified cell position.
Definition: doubleref.cxx:304
virtual bool isRangeEqual(const ScRange &rRange) const override
Definition: doubleref.cxx:470
int i
virtual SCROW getRowSize() const override
Definition: doubleref.cxx:289
void IncCol(SCCOL nDelta=1)
Definition: address.hxx:303
sal_Int16 SCCOL
Definition: types.hxx:21
bool ValidCol(SCCOL nCol) const
Definition: document.hxx:874
static SC_DLLPUBLIC const CharClass & getCharClass()
Definition: global.cxx:1016
Base class for abstracting range data backends for database functions.
Definition: doubleref.hxx:37
const SCSIZE SCSIZE_MAX
Definition: address.hxx:59
virtual ~ScDBRangeBase()=0
Definition: doubleref.cxx:253
OUString uppercase(const OUString &rStr, sal_Int32 nPos, sal_Int32 nCount) const
void Resize(size_t nNew)
Definition: queryparam.cxx:205
virtual SCSIZE getVisibleDataCellCount() const override
Definition: doubleref.cxx:412
virtual bool isRangeEqual(const ScRange &rRange) const override
Definition: doubleref.cxx:384
virtual ~ScDBExternalRange() override
Definition: doubleref.cxx:398
virtual std::unique_ptr< ScDBQueryParamBase > createQueryParam(const ScDBRangeBase *pQueryRef) const override
Definition: doubleref.cxx:362
ScDocument * getDoc() const
Definition: doubleref.hxx:79
svl::SharedString maString
Definition: queryentry.hxx:49
FormulaError
SCCOL Col() const
Definition: address.hxx:266
sal_Int32 SCROW
Definition: types.hxx:17
virtual SCCOL getFirstFieldColumn() const override
Definition: doubleref.cxx:430
virtual SCCOL findFieldColumn(SCCOL nIndex) const override
Get a 0-based column index that corresponds with the passed field index.
Definition: doubleref.cxx:435
virtual SCCOL getFirstFieldColumn() const override
Definition: doubleref.cxx:315
virtual SCCOL findFieldColumn(SCCOL nIndex) const =0
Get a 0-based column index that corresponds with the passed field index.
virtual SCROW getRowSize() const =0
bool fillQueryEntries(ScQueryParamBase *pParam, const ScDBRangeBase *pDBRef) const
Definition: doubleref.cxx:257
void * p
virtual SCCOL getColSize() const override
Definition: doubleref.cxx:402
ScQueryConnect eConnect
Definition: queryentry.hxx:62
virtual ~ScDBInternalRange() override
Definition: doubleref.cxx:280
ScDBExternalRange(ScDocument *pDoc, const ScMatrixRef &pMat)
Definition: doubleref.cxx:389
virtual OUString getString(SCCOL nCol, SCROW nRow) const =0
Get a string value of a specified cell position.
static SC_DLLPUBLIC::utl::TransliterationWrapper & GetTransliteration()
Definition: global.cxx:978
FormulaError GetStringForFormula(const ScAddress &rPos, OUString &rString)
Definition: document.cxx:3579
ScQueryOp eOp
Definition: queryentry.hxx:61
aStr
Each instance of this struct represents a single filtering criteria.
Definition: queryentry.hxx:33
utl::SearchParam::SearchType eSearchType
Definition: queryparam.hxx:43
B2DRange maRange
sal_Int16 SCTAB
Definition: types.hxx:22
virtual SCROW getRowSize() const override
Definition: doubleref.cxx:407
virtual OUString getString(SCCOL nCol, SCROW nRow) const override
Get a string value of a specified cell position.
Definition: doubleref.cxx:422