sc/source/core/tool/doubleref.cxx

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