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
24 // MARKER(update_precomp.py): autogen include statement, do not remove
25 #include "precompiled_sc.hxx"
26
27 // INCLUDE ---------------------------------------------------------------
28
29 #include "doubleref.hxx"
30 #include "cell.hxx"
31 #include "global.hxx"
32 #include "document.hxx"
33 #include "queryparam.hxx"
34 #include "globstr.hrc"
35
36 #include <memory>
37 #include <vector>
38
39 using ::rtl::OUString;
40 using ::std::auto_ptr;
41 using ::std::vector;
42
43 namespace {
44
lcl_toUpper(OUString & rStr)45 void lcl_toUpper(OUString& rStr)
46 {
47 rStr = ScGlobal::pCharClass->toUpper(rStr.trim(), 0, static_cast<xub_StrLen>(rStr.getLength()));
48 }
49
lcl_createStarQuery(ScQueryParamBase * pParam,const ScDBRangeBase * pDBRef,const ScDBRangeBase * pQueryRef)50 bool lcl_createStarQuery(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 sal_Bool bValid;
65 sal_Bool bFound;
66 OUString aCellStr;
67 SCSIZE nIndex = 0;
68 SCROW nRow = 0;
69 SCROW nRows = pDBRef->getRowSize();
70 SCSIZE nNewEntries = static_cast<SCSIZE>(nRows);
71 pParam->Resize(nNewEntries);
72
73 do
74 {
75 ScQueryEntry& rEntry = pParam->GetEntry(nIndex);
76
77 bValid = sal_False;
78
79 if (nIndex > 0)
80 {
81 // For all entries after the first one, check the and/or connector in the first column.
82 aCellStr = pQueryRef->getString(0, nRow);
83 lcl_toUpper(aCellStr);
84 if ( aCellStr.equals(ScGlobal::GetRscString(STR_TABLE_UND)) )
85 {
86 rEntry.eConnect = SC_AND;
87 bValid = sal_True;
88 }
89 else if ( aCellStr.equals(ScGlobal::GetRscString(STR_TABLE_ODER)) )
90 {
91 rEntry.eConnect = SC_OR;
92 bValid = sal_True;
93 }
94 }
95
96 if ((nIndex < 1) || bValid)
97 {
98 // field name in the 2nd column.
99 bFound = sal_False;
100 aCellStr = pQueryRef->getString(1, nRow);
101 SCCOL nField = pDBRef->findFieldColumn(aCellStr); // TODO: must be case insensitive comparison.
102 if (ValidCol(nField))
103 {
104 rEntry.nField = nField;
105 bValid = true;
106 }
107 else
108 bValid = false;
109 }
110
111 if (bValid)
112 {
113 // equality, non-equality operator in the 3rd column.
114 bFound = sal_False;
115 aCellStr = pQueryRef->getString(2, nRow);
116 lcl_toUpper(aCellStr);
117 const sal_Unicode* p = aCellStr.getStr();
118 if (p[0] == sal_Unicode('<'))
119 {
120 if (p[1] == sal_Unicode('>'))
121 rEntry.eOp = SC_NOT_EQUAL;
122 else if (p[1] == sal_Unicode('='))
123 rEntry.eOp = SC_LESS_EQUAL;
124 else
125 rEntry.eOp = SC_LESS;
126 }
127 else if (p[0] == sal_Unicode('>'))
128 {
129 if (p[1] == sal_Unicode('='))
130 rEntry.eOp = SC_GREATER_EQUAL;
131 else
132 rEntry.eOp = SC_GREATER;
133 }
134 else if (p[0] == sal_Unicode('='))
135 rEntry.eOp = SC_EQUAL;
136
137 }
138
139 if (bValid)
140 {
141 // Finally, the right-hand-side value in the 4th column.
142 *rEntry.pStr = pQueryRef->getString(3, nRow);
143 rEntry.bDoQuery = sal_True;
144 }
145 nIndex++;
146 nRow++;
147 }
148 while (bValid && (nRow < nRows) /* && (nIndex < MAXQUERY) */ );
149 return bValid;
150 }
151
lcl_createExcelQuery(ScQueryParamBase * pParam,const ScDBRangeBase * pDBRef,const ScDBRangeBase * pQueryRef)152 bool lcl_createExcelQuery(
153 ScQueryParamBase* pParam, const ScDBRangeBase* pDBRef, const ScDBRangeBase* pQueryRef)
154 {
155 bool bValid = true;
156 SCCOL nCols = pQueryRef->getColSize();
157 SCROW nRows = pQueryRef->getRowSize();
158 vector<SCCOL> aFields(nCols);
159 SCCOL nCol = 0;
160 while (bValid && (nCol < nCols))
161 {
162 OUString aQueryStr = pQueryRef->getString(nCol, 0);
163 SCCOL nField = pDBRef->findFieldColumn(aQueryStr);
164 if (ValidCol(nField))
165 aFields[nCol] = nField;
166 else
167 bValid = false;
168 ++nCol;
169 }
170
171 if (bValid)
172 {
173 // sal_uLong nVisible = 0;
174 // for ( nCol=nCol1; nCol<=nCol2; nCol++ )
175 // nVisible += aCol[nCol].VisibleCount( nRow1+1, nRow2 );
176
177 // Count the number of visible cells (excluding the header row). Each
178 // visible cell corresponds with a single query.
179 SCSIZE nVisible = pQueryRef->getVisibleDataCellCount();
180 if ( nVisible > SCSIZE_MAX / sizeof(void*) )
181 {
182 DBG_ERROR("zu viele Filterkritierien");
183 nVisible = 0;
184 }
185
186 SCSIZE nNewEntries = nVisible;
187 pParam->Resize( nNewEntries );
188
189 SCSIZE nIndex = 0;
190 SCROW nRow = 1;
191 String aCellStr;
192 while (nRow < nRows)
193 {
194 nCol = 0;
195 while (nCol < nCols)
196 {
197 aCellStr = pQueryRef->getString(nCol, nRow);
198 ScGlobal::pCharClass->toUpper( aCellStr );
199 if (aCellStr.Len() > 0)
200 {
201 if (nIndex < nNewEntries)
202 {
203 pParam->GetEntry(nIndex).nField = aFields[nCol];
204 pParam->FillInExcelSyntax(aCellStr, nIndex);
205 nIndex++;
206 if (nIndex < nNewEntries)
207 pParam->GetEntry(nIndex).eConnect = SC_AND;
208 }
209 else
210 bValid = sal_False;
211 }
212 nCol++;
213 }
214 nRow++;
215 if (nIndex < nNewEntries)
216 pParam->GetEntry(nIndex).eConnect = SC_OR;
217 }
218 }
219 return bValid;
220 }
221
lcl_fillQueryEntries(ScQueryParamBase * pParam,const ScDBRangeBase * pDBRef,const ScDBRangeBase * pQueryRef)222 bool lcl_fillQueryEntries(
223 ScQueryParamBase* pParam, const ScDBRangeBase* pDBRef, const ScDBRangeBase* pQueryRef)
224 {
225 SCSIZE nCount = pParam->GetEntryCount();
226 for (SCSIZE i = 0; i < nCount; ++i)
227 pParam->GetEntry(i).Clear();
228
229 // Standard QueryTabelle
230 bool bValid = lcl_createStarQuery(pParam, pDBRef, pQueryRef);
231 // Excel QueryTabelle
232 if (!bValid)
233 bValid = lcl_createExcelQuery(pParam, pDBRef, pQueryRef);
234
235 nCount = pParam->GetEntryCount();
236 if (bValid)
237 {
238 // bQueryByString muss gesetzt sein
239 for (SCSIZE i = 0; i < nCount; ++i)
240 pParam->GetEntry(i).bQueryByString = true;
241 }
242 else
243 {
244 // nix
245 for (SCSIZE i = 0; i < nCount; ++i)
246 pParam->GetEntry(i).Clear();
247 }
248 return bValid;
249 }
250
251 }
252
253 // ============================================================================
254
ScDBRangeBase(ScDocument * pDoc,RefType eType)255 ScDBRangeBase::ScDBRangeBase(ScDocument* pDoc, RefType eType) :
256 mpDoc(pDoc), meType(eType)
257 {
258 }
259
~ScDBRangeBase()260 ScDBRangeBase::~ScDBRangeBase()
261 {
262 }
263
fillQueryEntries(ScQueryParamBase * pParam,const ScDBRangeBase * pDBRef) const264 bool ScDBRangeBase::fillQueryEntries(ScQueryParamBase* pParam, const ScDBRangeBase* pDBRef) const
265 {
266 if (!pDBRef)
267 return false;
268
269 return lcl_fillQueryEntries(pParam, pDBRef, this);
270 }
271
fillQueryOptions(ScQueryParamBase * pParam)272 void ScDBRangeBase::fillQueryOptions(ScQueryParamBase* pParam)
273 {
274 pParam->bHasHeader = true;
275 pParam->bByRow = true;
276 pParam->bInplace = true;
277 pParam->bCaseSens = false;
278 pParam->bRegExp = false;
279 pParam->bDuplicate = true;
280 pParam->bMixedComparison = false;
281 }
282
getDoc() const283 ScDocument* ScDBRangeBase::getDoc() const
284 {
285 return mpDoc;
286 }
287
288 // ============================================================================
289
ScDBInternalRange(ScDocument * pDoc,const ScRange & rRange)290 ScDBInternalRange::ScDBInternalRange(ScDocument* pDoc, const ScRange& rRange) :
291 ScDBRangeBase(pDoc, INTERNAL), maRange(rRange)
292 {
293 }
294
~ScDBInternalRange()295 ScDBInternalRange::~ScDBInternalRange()
296 {
297 }
298
getRange() const299 const ScRange& ScDBInternalRange::getRange() const
300 {
301 return maRange;
302 }
303
getColSize() const304 SCCOL ScDBInternalRange::getColSize() const
305 {
306 return maRange.aEnd.Col() - maRange.aStart.Col() + 1;
307 }
308
getRowSize() const309 SCROW ScDBInternalRange::getRowSize() const
310 {
311 return maRange.aEnd.Row() - maRange.aStart.Row() + 1;
312 }
313
getVisibleDataCellCount() const314 SCSIZE ScDBInternalRange::getVisibleDataCellCount() const
315 {
316 SCCOL nCols = getColSize();
317 SCROW nRows = getRowSize();
318 if (nRows <= 1)
319 return 0;
320
321 return (nRows-1)*nCols;
322 }
323
getString(SCCOL nCol,SCROW nRow) const324 OUString ScDBInternalRange::getString(SCCOL nCol, SCROW nRow) const
325 {
326 String aStr;
327 const ScAddress& s = maRange.aStart;
328 // #i109200# this is used in formula calculation, use GetInputString, not GetString
329 // (consistent with ScDBInternalRange::getCellString)
330 // GetStringForFormula is not used here, to allow querying for date values.
331 getDoc()->GetInputString(s.Col() + nCol, s.Row() + nRow, maRange.aStart.Tab(), aStr);
332 return aStr;
333 }
334
getFirstFieldColumn() const335 SCCOL ScDBInternalRange::getFirstFieldColumn() const
336 {
337 return getRange().aStart.Col();
338 }
339
findFieldColumn(SCCOL nIndex) const340 SCCOL ScDBInternalRange::findFieldColumn(SCCOL nIndex) const
341 {
342 const ScRange& rRange = getRange();
343 const ScAddress& s = rRange.aStart;
344 const ScAddress& e = rRange.aEnd;
345
346 SCCOL nDBCol1 = s.Col();
347 SCCOL nDBCol2 = e.Col();
348
349 if ( nIndex <= 0 || nIndex > (nDBCol2 - nDBCol1 + 1) )
350 return nDBCol1;
351
352 return Min(nDBCol2, static_cast<SCCOL>(nDBCol1 + nIndex - 1));
353 }
354
findFieldColumn(const OUString & rStr,sal_uInt16 * pErr) const355 SCCOL ScDBInternalRange::findFieldColumn(const OUString& rStr, sal_uInt16* pErr) const
356 {
357 const ScAddress& s = maRange.aStart;
358 const ScAddress& e = maRange.aEnd;
359 OUString aUpper = rStr;
360 lcl_toUpper(aUpper);
361
362 SCCOL nDBCol1 = s.Col();
363 SCROW nDBRow1 = s.Row();
364 SCTAB nDBTab1 = s.Tab();
365 SCCOL nDBCol2 = e.Col();
366
367 SCCOL nField = nDBCol1;
368 sal_Bool bFound = sal_True;
369
370 bFound = sal_False;
371 OUString aCellStr;
372 ScAddress aLook( nDBCol1, nDBRow1, nDBTab1 );
373 while (!bFound && (aLook.Col() <= nDBCol2))
374 {
375 sal_uInt16 nErr = getDoc()->GetStringForFormula( aLook, aCellStr );
376 if (pErr)
377 *pErr = nErr;
378 lcl_toUpper(aCellStr);
379 bFound = ScGlobal::GetpTransliteration()->isEqual(aCellStr, aUpper);
380 if (!bFound)
381 aLook.IncCol();
382 }
383 nField = aLook.Col();
384
385 return bFound ? nField : -1;
386 }
387
createQueryParam(const ScDBRangeBase * pQueryRef) const388 ScDBQueryParamBase* ScDBInternalRange::createQueryParam(const ScDBRangeBase* pQueryRef) const
389 {
390 auto_ptr<ScDBQueryParamInternal> pParam(new ScDBQueryParamInternal);
391
392 // Set the database range first.
393 const ScAddress& s = maRange.aStart;
394 const ScAddress& e = maRange.aEnd;
395 pParam->nCol1 = s.Col();
396 pParam->nRow1 = s.Row();
397 pParam->nCol2 = e.Col();
398 pParam->nRow2 = e.Row();
399 pParam->nTab = s.Tab();
400
401 fillQueryOptions(pParam.get());
402
403 // Now construct the query entries from the query range.
404 if (!pQueryRef->fillQueryEntries(pParam.get(), this))
405 return NULL;
406
407 return pParam.release();
408 }
409
isRangeEqual(const ScRange & rRange) const410 bool ScDBInternalRange::isRangeEqual(const ScRange& rRange) const
411 {
412 return maRange == rRange;
413 }
414
415 // ============================================================================
416
ScDBExternalRange(ScDocument * pDoc,const ScMatrixRef & pMat)417 ScDBExternalRange::ScDBExternalRange(ScDocument* pDoc, const ScMatrixRef& pMat) :
418 ScDBRangeBase(pDoc, EXTERNAL), mpMatrix(pMat)
419 {
420 SCSIZE nC, nR;
421 mpMatrix->GetDimensions(nC, nR);
422 mnCols = static_cast<SCCOL>(nC);
423 mnRows = static_cast<SCROW>(nR);
424 }
425
~ScDBExternalRange()426 ScDBExternalRange::~ScDBExternalRange()
427 {
428 }
429
getColSize() const430 SCCOL ScDBExternalRange::getColSize() const
431 {
432 return mnCols;
433 }
434
getRowSize() const435 SCROW ScDBExternalRange::getRowSize() const
436 {
437 return mnRows;
438 }
439
getVisibleDataCellCount() const440 SCSIZE ScDBExternalRange::getVisibleDataCellCount() const
441 {
442 SCCOL nCols = getColSize();
443 SCROW nRows = getRowSize();
444 if (nRows <= 1)
445 return 0;
446
447 return (nRows-1)*nCols;
448 }
449
getString(SCCOL nCol,SCROW nRow) const450 OUString ScDBExternalRange::getString(SCCOL nCol, SCROW nRow) const
451 {
452 if (nCol >= mnCols || nRow >= mnRows)
453 return OUString();
454
455 return mpMatrix->GetString(nCol, nRow);
456 }
457
getFirstFieldColumn() const458 SCCOL ScDBExternalRange::getFirstFieldColumn() const
459 {
460 return 0;
461 }
462
findFieldColumn(SCCOL nIndex) const463 SCCOL ScDBExternalRange::findFieldColumn(SCCOL nIndex) const
464 {
465 if (nIndex < 1)
466 // 1st field
467 return 0;
468
469 if (nIndex > mnCols)
470 // last field
471 return mnCols - 1;
472
473 return nIndex - 1;
474 }
475
findFieldColumn(const OUString & rStr,sal_uInt16 * pErr) const476 SCCOL ScDBExternalRange::findFieldColumn(const OUString& rStr, sal_uInt16* pErr) const
477 {
478 if (pErr)
479 pErr = 0;
480
481 OUString aUpper = rStr;
482 lcl_toUpper(aUpper);
483 for (SCCOL i = 0; i < mnCols; ++i)
484 {
485 OUString aUpperVal = mpMatrix->GetString(i, 0);
486 lcl_toUpper(aUpperVal);
487 if (aUpper.equals(aUpperVal))
488 return i;
489 }
490 return -1;
491 }
492
createQueryParam(const ScDBRangeBase * pQueryRef) const493 ScDBQueryParamBase* ScDBExternalRange::createQueryParam(const ScDBRangeBase* pQueryRef) const
494 {
495 auto_ptr<ScDBQueryParamMatrix> pParam(new ScDBQueryParamMatrix);
496 pParam->mpMatrix = mpMatrix;
497 fillQueryOptions(pParam.get());
498
499 // Now construct the query entries from the query range.
500 if (!pQueryRef->fillQueryEntries(pParam.get(), this))
501 return NULL;
502
503 return pParam.release();
504 }
505
isRangeEqual(const ScRange &) const506 bool ScDBExternalRange::isRangeEqual(const ScRange& /*rRange*/) const
507 {
508 return false;
509 }
510
511