sc/source/core/data/dputil.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
  10 #include "dputil.hxx"
  11 #include "dpitemdata.hxx"
  12 #include "dpnumgroupinfo.hxx"
  13 #include "globalnames.hxx"
  14 #include "globstr.hrc"
  15
  16 #include <comphelper/string.hxx>
  17 #include <unotools/localedatawrapper.hxx>
  18 #include <unotools/calendarwrapper.hxx>
  19 #include <svl/zforlist.hxx>
  20 #include <rtl/math.hxx>
  21 #include <osl/diagnose.h>
  22
  23 #include <com/sun/star/sheet/DataPilotFieldGroupBy.hpp>
  24 #include <com/sun/star/i18n/CalendarDisplayIndex.hpp>
  25
  26 using namespace com::sun::star;
  27
  28 namespace {
  29
  30 const sal_uInt16 SC_DP_LEAPYEAR = 1648;     // arbitrary leap year for date calculations
  31
  32 OUString getTwoDigitString(sal_Int32 nValue)
  33 {
  34     OUString aRet = OUString::number( nValue );
  35     if ( aRet.getLength() < 2 )
  36         aRet = "0" + aRet;
  37     return aRet;
  38 }
  39
  40 void appendDateStr(OUStringBuffer& rBuffer, double fValue, SvNumberFormatter* pFormatter)
  41 {
  42     sal_uLong nFormat = pFormatter->GetStandardFormat( css::util::NumberFormat::DATE, ScGlobal::eLnge );
  43     OUString aString;
  44     pFormatter->GetInputLineString(fValue, nFormat, aString);
  45     rBuffer.append(aString);
  46 }
  47
  48 OUString getSpecialDateName(double fValue, bool bFirst, SvNumberFormatter* pFormatter)
  49 {
  50     OUStringBuffer aBuffer;
  51     aBuffer.append( bFirst ? '<' : '>' );
  52     appendDateStr(aBuffer, fValue, pFormatter);
  53     return aBuffer.makeStringAndClear();
  54 }
  55
  56 }
  57
  58 bool ScDPUtil::isDuplicateDimension(const OUString& rName)
  59 {
  60     return rName.endsWith("*");
  61 }
  62
  63 OUString ScDPUtil::getSourceDimensionName(const OUString& rName)
  64 {
  65     return comphelper::string::stripEnd(rName, '*');
  66 }
  67
  68 sal_uInt8 ScDPUtil::getDuplicateIndex(const OUString& rName)
  69 {
  70     // Count all trailing '*'s.
  71
  72     sal_Int32 n = rName.getLength();
  73     if (!n)
  74         return 0;
  75
  76     sal_uInt8 nDupCount = 0;
  77     const sal_Unicode* p = rName.getStr();
  78     const sal_Unicode* pStart = p;
  79     p += n-1; // Set it to the last char.
  80     for (; p != pStart; --p, ++nDupCount)
  81     {
  82         if (*p != '*')
  83             break;
  84     }
  85
  86     return nDupCount;
  87 }
  88
  89 OUString ScDPUtil::createDuplicateDimensionName(const OUString& rOriginal, size_t nDupCount)
  90 {
  91     if (!nDupCount)
  92         return rOriginal;
  93
  94     OUStringBuffer aBuf(rOriginal);
  95     for (size_t i = 0; i < nDupCount; ++i)
  96         aBuf.append('*');
  97
  98     return aBuf.makeStringAndClear();
  99 }
 100
 101 OUString ScDPUtil::getDateGroupName(
 102         sal_Int32 nDatePart, sal_Int32 nValue, SvNumberFormatter* pFormatter,
 103         double fStart, double fEnd)
 104 {
 105     if (nValue == ScDPItemData::DateFirst)
 106         return getSpecialDateName(fStart, true, pFormatter);
 107     if (nValue == ScDPItemData::DateLast)
 108         return getSpecialDateName(fEnd, false, pFormatter);
 109
 110     switch ( nDatePart )
 111     {
 112         case sheet::DataPilotFieldGroupBy::YEARS:
 113             return OUString::number(nValue);
 114         case sheet::DataPilotFieldGroupBy::QUARTERS:
 115             return ScGlobal::pLocaleData->getQuarterAbbreviation(sal_Int16(nValue-1));    // nValue is 1-based
 116         case com::sun::star::sheet::DataPilotFieldGroupBy::MONTHS:
 117             return ScGlobal::GetCalendar()->getDisplayName(
 118                         i18n::CalendarDisplayIndex::MONTH, sal_Int16(nValue-1), 0);    // 0-based, get short name
 119         case sheet::DataPilotFieldGroupBy::DAYS:
 120         {
 121             Date aDate(1, 1, SC_DP_LEAPYEAR);
 122             aDate += (nValue - 1);            // nValue is 1-based
 123             Date aNullDate = *pFormatter->GetNullDate();
 124             long nDays = aDate - aNullDate;
 125
 126             sal_uLong nFormat = pFormatter->GetFormatIndex(NF_DATE_SYS_DDMMM, ScGlobal::eLnge);
 127             Color* pColor;
 128             OUString aStr;
 129             pFormatter->GetOutputString(nDays, nFormat, aStr, &pColor);
 130             return aStr;
 131         }
 132         case sheet::DataPilotFieldGroupBy::HOURS:
 133         {
 134             //TODO: allow am/pm format?
 135             return getTwoDigitString(nValue);
 136         }
 137         break;
 138         case sheet::DataPilotFieldGroupBy::MINUTES:
 139         case sheet::DataPilotFieldGroupBy::SECONDS:
 140         {
 141             OUStringBuffer aBuf(ScGlobal::pLocaleData->getTimeSep());
 142             aBuf.append(getTwoDigitString(nValue));
 143             return aBuf.makeStringAndClear();
 144         }
 145         break;
 146         default:
 147             OSL_FAIL("invalid date part");
 148     }
 149
 150     return OUString("FIXME: unhandled value");
 151 }
 152
 153 double ScDPUtil::getNumGroupStartValue(double fValue, const ScDPNumGroupInfo& rInfo)
 154 {
 155     if (fValue < rInfo.mfStart && !rtl::math::approxEqual(fValue, rInfo.mfStart))
 156     {
 157         rtl::math::setInf(&fValue, true);
 158         return fValue;
 159     }
 160
 161     if (fValue > rInfo.mfEnd && !rtl::math::approxEqual(fValue, rInfo.mfEnd))
 162     {
 163         rtl::math::setInf(&fValue, false);
 164         return fValue;
 165     }
 166
 167     double fDiff = fValue - rInfo.mfStart;
 168     double fDiv = rtl::math::approxFloor( fDiff / rInfo.mfStep );
 169     double fGroupStart = rInfo.mfStart + fDiv * rInfo.mfStep;
 170
 171     if (rtl::math::approxEqual(fGroupStart, rInfo.mfEnd) &&
 172         !rtl::math::approxEqual(fGroupStart, rInfo.mfStart))
 173     {
 174         if (!rInfo.mbDateValues)
 175         {
 176             // A group that would consist only of the end value is not
 177             // created, instead the value is included in the last group
 178             // before. So the previous group is used if the calculated group
 179             // start value is the selected end value.
 180
 181             fDiv -= 1.0;
 182             return rInfo.mfStart + fDiv * rInfo.mfStep;
 183         }
 184
 185         // For date values, the end value is instead treated as above the
 186         // limit if it would be a group of its own.
 187
 188         return rInfo.mfEnd + rInfo.mfStep;
 189     }
 190
 191     return fGroupStart;
 192 }
 193
 194 namespace {
 195
 196 void lcl_AppendDateStr( OUStringBuffer& rBuffer, double fValue, SvNumberFormatter* pFormatter )
 197 {
 198     sal_uLong nFormat = pFormatter->GetStandardFormat( css::util::NumberFormat::DATE, ScGlobal::eLnge );
 199     OUString aString;
 200     pFormatter->GetInputLineString( fValue, nFormat, aString );
 201     rBuffer.append( aString );
 202 }
 203
 204 OUString lcl_GetSpecialNumGroupName( double fValue, bool bFirst, sal_Unicode cDecSeparator,
 205     bool bDateValues, SvNumberFormatter* pFormatter )
 206 {
 207     OSL_ENSURE( cDecSeparator != 0, "cDecSeparator not initialized" );
 208
 209     OUStringBuffer aBuffer;
 210     aBuffer.append( bFirst ? '<' : '>' );
 211     if ( bDateValues )
 212         lcl_AppendDateStr( aBuffer, fValue, pFormatter );
 213     else
 214         rtl::math::doubleToUStringBuffer( aBuffer, fValue, rtl_math_StringFormat_Automatic,
 215         rtl_math_DecimalPlaces_Max, cDecSeparator, true );
 216     return aBuffer.makeStringAndClear();
 217 }
 218
 219 OUString lcl_GetNumGroupName(
 220     double fStartValue, const ScDPNumGroupInfo& rInfo, sal_Unicode cDecSep,
 221     SvNumberFormatter* pFormatter)
 222 {
 223     OSL_ENSURE( cDecSep != 0, "cDecSeparator not initialized" );
 224
 225     double fStep = rInfo.mfStep;
 226     double fEndValue = fStartValue + fStep;
 227     if (rInfo.mbIntegerOnly && (rInfo.mbDateValues || !rtl::math::approxEqual(fEndValue, rInfo.mfEnd)))
 228     {
 229         //  The second number of the group label is
 230         //  (first number + size - 1) if there are only integer numbers,
 231         //  (first number + size) if any non-integer numbers are involved.
 232         //  Exception: The last group (containing the end value) is always
 233         //  shown as including the end value (but not for dates).
 234
 235         fEndValue -= 1.0;
 236     }
 237
 238     if ( fEndValue > rInfo.mfEnd && !rInfo.mbAutoEnd )
 239     {
 240         // limit the last group to the end value
 241
 242         fEndValue = rInfo.mfEnd;
 243     }
 244
 245     OUStringBuffer aBuffer;
 246     if ( rInfo.mbDateValues )
 247     {
 248         lcl_AppendDateStr( aBuffer, fStartValue, pFormatter );
 249         aBuffer.appendAscii( " - " );   // with spaces
 250         lcl_AppendDateStr( aBuffer, fEndValue, pFormatter );
 251     }
 252     else
 253     {
 254         rtl::math::doubleToUStringBuffer( aBuffer, fStartValue, rtl_math_StringFormat_Automatic,
 255             rtl_math_DecimalPlaces_Max, cDecSep, true );
 256         aBuffer.append( '-' );
 257         rtl::math::doubleToUStringBuffer( aBuffer, fEndValue, rtl_math_StringFormat_Automatic,
 258             rtl_math_DecimalPlaces_Max, cDecSep, true );
 259     }
 260
 261     return aBuffer.makeStringAndClear();
 262 }
 263
 264 }
 265
 266 OUString ScDPUtil::getNumGroupName(
 267     double fValue, const ScDPNumGroupInfo& rInfo, sal_Unicode cDecSep, SvNumberFormatter* pFormatter)
 268 {
 269     if ( fValue < rInfo.mfStart && !rtl::math::approxEqual( fValue, rInfo.mfStart ) )
 270         return lcl_GetSpecialNumGroupName( rInfo.mfStart, true, cDecSep, rInfo.mbDateValues, pFormatter );
 271
 272     if ( fValue > rInfo.mfEnd && !rtl::math::approxEqual( fValue, rInfo.mfEnd ) )
 273         return lcl_GetSpecialNumGroupName( rInfo.mfEnd, false, cDecSep, rInfo.mbDateValues, pFormatter );
 274
 275     double fDiff = fValue - rInfo.mfStart;
 276     double fDiv = rtl::math::approxFloor( fDiff / rInfo.mfStep );
 277     double fGroupStart = rInfo.mfStart + fDiv * rInfo.mfStep;
 278
 279     if ( rtl::math::approxEqual( fGroupStart, rInfo.mfEnd ) &&
 280         !rtl::math::approxEqual( fGroupStart, rInfo.mfStart ) )
 281     {
 282         if (rInfo.mbDateValues)
 283         {
 284             //  For date values, the end value is instead treated as above the limit
 285             //  if it would be a group of its own.
 286             return lcl_GetSpecialNumGroupName( rInfo.mfEnd, false, cDecSep, rInfo.mbDateValues, pFormatter );
 287         }
 288     }
 289
 290     return lcl_GetNumGroupName(fGroupStart, rInfo, cDecSep, pFormatter);
 291 }
 292
 293 sal_Int32 ScDPUtil::getDatePartValue(
 294     double fValue, const ScDPNumGroupInfo* pInfo, sal_Int32 nDatePart,
 295     SvNumberFormatter* pFormatter)
 296 {
 297     // Start and end are inclusive
 298     // (End date without a time value is included, with a time value it's not)
 299
 300     if (pInfo)
 301     {
 302         if (fValue < pInfo->mfStart && !rtl::math::approxEqual(fValue, pInfo->mfStart))
 303             return ScDPItemData::DateFirst;
 304         if (fValue > pInfo->mfEnd && !rtl::math::approxEqual(fValue, pInfo->mfEnd))
 305             return ScDPItemData::DateLast;
 306     }
 307
 308     sal_Int32 nResult = 0;
 309
 310     if (nDatePart == sheet::DataPilotFieldGroupBy::HOURS ||
 311         nDatePart == sheet::DataPilotFieldGroupBy::MINUTES ||
 312         nDatePart == sheet::DataPilotFieldGroupBy::SECONDS)
 313     {
 314         // handle time
 315         // (as in the cell functions, ScInterpreter::ScGetHour etc.: seconds are rounded)
 316
 317         double fTime = fValue - rtl::math::approxFloor(fValue);
 318         long nSeconds = (long)rtl::math::approxFloor(fTime*DATE_TIME_FACTOR+0.5);
 319
 320         switch (nDatePart)
 321         {
 322             case sheet::DataPilotFieldGroupBy::HOURS:
 323                 nResult = nSeconds / 3600;
 324                 break;
 325             case sheet::DataPilotFieldGroupBy::MINUTES:
 326                 nResult = ( nSeconds % 3600 ) / 60;
 327                 break;
 328             case sheet::DataPilotFieldGroupBy::SECONDS:
 329                 nResult = nSeconds % 60;
 330                 break;
 331         }
 332     }
 333     else
 334     {
 335         Date aDate = *(pFormatter->GetNullDate());
 336         aDate += (long)::rtl::math::approxFloor(fValue);
 337
 338         switch ( nDatePart )
 339         {
 340             case com::sun::star::sheet::DataPilotFieldGroupBy::YEARS:
 341                 nResult = aDate.GetYear();
 342                 break;
 343             case com::sun::star::sheet::DataPilotFieldGroupBy::QUARTERS:
 344                 nResult = 1 + (aDate.GetMonth() - 1) / 3;     // 1..4
 345                 break;
 346             case com::sun::star::sheet::DataPilotFieldGroupBy::MONTHS:
 347                 nResult = aDate.GetMonth();     // 1..12
 348                 break;
 349             case com::sun::star::sheet::DataPilotFieldGroupBy::DAYS:
 350                 {
 351                     Date aYearStart(1, 1, aDate.GetYear());
 352                     nResult = (aDate - aYearStart) + 1;       // Jan 01 has value 1
 353                     if (nResult >= 60 && !aDate.IsLeapYear())
 354                     {
 355                         // days are counted from 1 to 366 - if not from a leap year, adjust
 356                         ++nResult;
 357                     }
 358                 }
 359                 break;
 360             default:
 361                 OSL_FAIL("invalid date part");
 362         }
 363     }
 364
 365     return nResult;
 366 }
 367
 368 namespace {
 369
 370 sal_uInt16 nFuncStrIds[12] = {
 371     0,                              // SUBTOTAL_FUNC_NONE
 372     STR_FUN_TEXT_AVG,               // SUBTOTAL_FUNC_AVE
 373     STR_FUN_TEXT_COUNT,             // SUBTOTAL_FUNC_CNT
 374     STR_FUN_TEXT_COUNT,             // SUBTOTAL_FUNC_CNT2
 375     STR_FUN_TEXT_MAX,               // SUBTOTAL_FUNC_MAX
 376     STR_FUN_TEXT_MIN,               // SUBTOTAL_FUNC_MIN
 377     STR_FUN_TEXT_PRODUCT,           // SUBTOTAL_FUNC_PROD
 378     STR_FUN_TEXT_STDDEV,            // SUBTOTAL_FUNC_STD
 379     STR_FUN_TEXT_STDDEV,            // SUBTOTAL_FUNC_STDP
 380     STR_FUN_TEXT_SUM,               // SUBTOTAL_FUNC_SUM
 381     STR_FUN_TEXT_VAR,               // SUBTOTAL_FUNC_VAR
 382     STR_FUN_TEXT_VAR                // SUBTOTAL_FUNC_VARP
 383 };
 384
 385 }
 386
 387 OUString ScDPUtil::getDisplayedMeasureName(const OUString& rName, ScSubTotalFunc eFunc)
 388 {
 389     OUStringBuffer aRet;
 390     sal_uInt16 nId = nFuncStrIds[eFunc];
 391     if (nId)
 392     {
 393         aRet.append(ScGlobal::GetRscString(nId));        // function name
 394         aRet.append(" - ");
 395     }
 396     aRet.append(rName);                   // field name
 397
 398     return aRet.makeStringAndClear();
 399 }
 400
 401 ScSubTotalFunc ScDPUtil::toSubTotalFunc(com::sun::star::sheet::GeneralFunction eGenFunc)
 402 {
 403     ScSubTotalFunc eSubTotal;
 404     switch (eGenFunc)
 405     {
 406         case sheet::GeneralFunction_NONE:       eSubTotal = SUBTOTAL_FUNC_NONE; break;
 407         case sheet::GeneralFunction_SUM:        eSubTotal = SUBTOTAL_FUNC_SUM;  break;
 408         case sheet::GeneralFunction_COUNT:      eSubTotal = SUBTOTAL_FUNC_CNT2; break;
 409         case sheet::GeneralFunction_AVERAGE:    eSubTotal = SUBTOTAL_FUNC_AVE;  break;
 410         case sheet::GeneralFunction_MAX:        eSubTotal = SUBTOTAL_FUNC_MAX;  break;
 411         case sheet::GeneralFunction_MIN:        eSubTotal = SUBTOTAL_FUNC_MIN;  break;
 412         case sheet::GeneralFunction_PRODUCT:    eSubTotal = SUBTOTAL_FUNC_PROD; break;
 413         case sheet::GeneralFunction_COUNTNUMS:  eSubTotal = SUBTOTAL_FUNC_CNT;  break;
 414         case sheet::GeneralFunction_STDEV:      eSubTotal = SUBTOTAL_FUNC_STD;  break;
 415         case sheet::GeneralFunction_STDEVP:     eSubTotal = SUBTOTAL_FUNC_STDP; break;
 416         case sheet::GeneralFunction_VAR:        eSubTotal = SUBTOTAL_FUNC_VAR;  break;
 417         case sheet::GeneralFunction_VARP:       eSubTotal = SUBTOTAL_FUNC_VARP; break;
 418         case sheet::GeneralFunction_AUTO:
 419         default:
 420             eSubTotal = SUBTOTAL_FUNC_NONE;
 421     }
 422     return eSubTotal;
 423 }
 424
 425 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */