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 <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 #include <unotools/transliterationwrapper.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::getCharClass().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::getCharClass().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)
 236             pParam->GetEntry(i).GetQueryItem().meType = ScQueryEntry::ByString;
 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
 249 ScDBRangeBase::ScDBRangeBase(ScDocument* pDoc) :
 250     mpDoc(pDoc)
 251 {
 252 }
 253
 254 ScDBRangeBase::~ScDBRangeBase()
 255 {
 256 }
 257
 258 bool ScDBRangeBase::fillQueryEntries(ScQueryParamBase* pParam, const ScDBRangeBase* pDBRef) const
 259 {
 260     if (!pDBRef)
 261         return false;
 262
 263     return lcl_fillQueryEntries(getDoc(), getDoc()->GetSharedStringPool(), pParam, pDBRef, this);
 264 }
 265
 266 void ScDBRangeBase::fillQueryOptions(ScQueryParamBase* pParam)
 267 {
 268     pParam->bHasHeader = true;
 269     pParam->bByRow = true;
 270     pParam->bInplace = true;
 271     pParam->bCaseSens = false;
 272     pParam->eSearchType = utl::SearchParam::SearchType::Normal;
 273     pParam->bDuplicate = true;
 274 }
 275
 276 ScDBInternalRange::ScDBInternalRange(ScDocument* pDoc, const ScRange& rRange) :
 277     ScDBRangeBase(pDoc), maRange(rRange)
 278 {
 279 }
 280
 281 ScDBInternalRange::~ScDBInternalRange()
 282 {
 283 }
 284
 285 SCCOL ScDBInternalRange::getColSize() const
 286 {
 287     return maRange.aEnd.Col() - maRange.aStart.Col() + 1;
 288 }
 289
 290 SCROW ScDBInternalRange::getRowSize() const
 291 {
 292     return maRange.aEnd.Row() - maRange.aStart.Row() + 1;
 293 }
 294
 295 SCSIZE ScDBInternalRange::getVisibleDataCellCount() const
 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     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     return getDoc()->GetInputString(s.Col() + nCol, s.Row() + nRow, maRange.aStart.Tab());
 312 }
 313
 314 SCCOL ScDBInternalRange::getFirstFieldColumn() const
 315 {
 316     return getRange().aStart.Col();
 317 }
 318
 319 SCCOL ScDBInternalRange::findFieldColumn(SCCOL nIndex) const
 320 {
 321     const ScRange& rRange = getRange();
 322     const ScAddress& s = rRange.aStart;
 323
 324     SCCOL nDBCol1 = s.Col();
 325
 326     // Don't handle out-of-bound condition here.  We'll do that later.
 327     return nIndex + nDBCol1 - 1;
 328 }
 329
 330 SCCOL ScDBInternalRange::findFieldColumn(const OUString& rStr, FormulaError* pErr) const
 331 {
 332     const ScAddress& s = maRange.aStart;
 333     const ScAddress& e = maRange.aEnd;
 334     OUString aUpper = rStr;
 335     lcl_uppercase(aUpper);
 336
 337     SCCOL nDBCol1 = s.Col();
 338     SCROW nDBRow1 = s.Row();
 339     SCTAB nDBTab1 = s.Tab();
 340     SCCOL nDBCol2 = e.Col();
 341
 342     bool bFound = false;
 343
 344     OUString aCellStr;
 345     ScAddress aLook( nDBCol1, nDBRow1, nDBTab1 );
 346     while (!bFound && (aLook.Col() <= nDBCol2))
 347     {
 348         FormulaError nErr = getDoc()->GetStringForFormula( aLook, aCellStr );
 349         if (pErr)
 350             *pErr = nErr;
 351         lcl_uppercase(aCellStr);
 352         bFound = ScGlobal::GetTransliteration().isEqual(aCellStr, aUpper);
 353         if (!bFound)
 354             aLook.IncCol();
 355     }
 356     SCCOL nField = aLook.Col();
 357
 358     return bFound ? nField : -1;
 359 }
 360
 361 std::unique_ptr<ScDBQueryParamBase> ScDBInternalRange::createQueryParam(const ScDBRangeBase* pQueryRef) const
 362 {
 363     unique_ptr<ScDBQueryParamInternal> pParam(new ScDBQueryParamInternal);
 364
 365     // Set the database range first.
 366     const ScAddress& s = maRange.aStart;
 367     const ScAddress& e = maRange.aEnd;
 368     pParam->nCol1 = s.Col();
 369     pParam->nRow1 = s.Row();
 370     pParam->nCol2 = e.Col();
 371     pParam->nRow2 = e.Row();
 372     pParam->nTab  = s.Tab();
 373
 374     fillQueryOptions(pParam.get());
 375
 376     // Now construct the query entries from the query range.
 377     if (!pQueryRef->fillQueryEntries(pParam.get(), this))
 378         return nullptr;
 379
 380     return std::unique_ptr<ScDBQueryParamBase>(std::move(pParam));
 381 }
 382
 383 bool ScDBInternalRange::isRangeEqual(const ScRange& rRange) const
 384 {
 385     return maRange == rRange;
 386 }
 387
 388 ScDBExternalRange::ScDBExternalRange(ScDocument* pDoc, ScMatrixRef pMat) :
 389     ScDBRangeBase(pDoc), mpMatrix(std::move(pMat))
 390 {
 391     SCSIZE nC, nR;
 392     mpMatrix->GetDimensions(nC, nR);
 393     mnCols = static_cast<SCCOL>(nC);
 394     mnRows = static_cast<SCROW>(nR);
 395 }
 396
 397 ScDBExternalRange::~ScDBExternalRange()
 398 {
 399 }
 400
 401 SCCOL ScDBExternalRange::getColSize() const
 402 {
 403     return mnCols;
 404 }
 405
 406 SCROW ScDBExternalRange::getRowSize() const
 407 {
 408     return mnRows;
 409 }
 410
 411 SCSIZE ScDBExternalRange::getVisibleDataCellCount() const
 412 {
 413     SCCOL nCols = getColSize();
 414     SCROW nRows = getRowSize();
 415     if (nRows <= 1)
 416         return 0;
 417
 418     return (nRows-1)*nCols;
 419 }
 420
 421 OUString ScDBExternalRange::getString(SCCOL nCol, SCROW nRow) const
 422 {
 423     if (nCol >= mnCols || nRow >= mnRows)
 424         return OUString();
 425
 426     return mpMatrix->GetString(nCol, nRow).getString();
 427 }
 428
 429 SCCOL ScDBExternalRange::getFirstFieldColumn() const
 430 {
 431     return 0;
 432 }
 433
 434 SCCOL ScDBExternalRange::findFieldColumn(SCCOL nIndex) const
 435 {
 436     return nIndex - 1;
 437 }
 438
 439 SCCOL ScDBExternalRange::findFieldColumn(const OUString& rStr, FormulaError* pErr) const
 440 {
 441     if (pErr)
 442         *pErr = FormulaError::NONE;
 443
 444     OUString aUpper = rStr;
 445     lcl_uppercase(aUpper);
 446     for (SCCOL i = 0; i < mnCols; ++i)
 447     {
 448         OUString aUpperVal = mpMatrix->GetString(i, 0).getString();
 449         lcl_uppercase(aUpperVal);
 450         if (aUpper == aUpperVal)
 451             return i;
 452     }
 453     return -1;
 454 }
 455
 456 std::unique_ptr<ScDBQueryParamBase> ScDBExternalRange::createQueryParam(const ScDBRangeBase* pQueryRef) const
 457 {
 458     unique_ptr<ScDBQueryParamMatrix> pParam(new ScDBQueryParamMatrix);
 459     pParam->mpMatrix = mpMatrix;
 460     fillQueryOptions(pParam.get());
 461
 462     // Now construct the query entries from the query range.
 463     if (!pQueryRef->fillQueryEntries(pParam.get(), this))
 464         return nullptr;
 465
 466     return std::unique_ptr<ScDBQueryParamBase>(std::move(pParam));
 467 }
 468
 469 bool ScDBExternalRange::isRangeEqual(const ScRange& /*rRange*/) const
 470 {
 471     return false;
 472 }
 473
 474 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */