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