LibreOffice Module sc (master)  1
dputil.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 
10 #include <dputil.hxx>
11 #include <dpitemdata.hxx>
12 #include <dpnumgroupinfo.hxx>
13 #include <globstr.hrc>
14 #include <scresid.hxx>
15 #include <generalfunction.hxx>
16 
17 #include <comphelper/string.hxx>
20 #include <svl/numformat.hxx>
21 #include <svl/zforlist.hxx>
22 #include <rtl/math.hxx>
23 #include <o3tl/string_view.hxx>
24 #include <osl/diagnose.h>
25 
26 #include <com/sun/star/sheet/DataPilotFieldGroupBy.hpp>
27 #include <com/sun/star/i18n/CalendarDisplayIndex.hpp>
28 
29 using namespace com::sun::star;
30 
31 namespace {
32 
33 const sal_uInt16 SC_DP_LEAPYEAR = 1648; // arbitrary leap year for date calculations
34 
35 OUString getTwoDigitString(sal_Int32 nValue)
36 {
37  OUString aRet = OUString::number( nValue );
38  if ( aRet.getLength() < 2 )
39  aRet = "0" + aRet;
40  return aRet;
41 }
42 
43 void appendDateStr(OUStringBuffer& rBuffer, double fValue, SvNumberFormatter* pFormatter)
44 {
45  sal_uInt32 nFormat = pFormatter->GetStandardFormat( SvNumFormatType::DATE, ScGlobal::eLnge );
46  OUString aString;
47  pFormatter->GetInputLineString(fValue, nFormat, aString);
48  rBuffer.append(aString);
49 }
50 
51 OUString getSpecialDateName(double fValue, bool bFirst, SvNumberFormatter* pFormatter)
52 {
53  OUStringBuffer aBuffer;
54  aBuffer.append( bFirst ? '<' : '>' );
55  appendDateStr(aBuffer, fValue, pFormatter);
56  return aBuffer.makeStringAndClear();
57 }
58 
59 }
60 
61 bool ScDPUtil::isDuplicateDimension(std::u16string_view rName)
62 {
63  return o3tl::ends_with(rName, u"*");
64 }
65 
66 OUString ScDPUtil::getSourceDimensionName(std::u16string_view rName)
67 {
68  return comphelper::string::stripEnd(rName, '*');
69 }
70 
71 sal_uInt8 ScDPUtil::getDuplicateIndex(const OUString& rName)
72 {
73  // Count all trailing '*'s.
74 
75  sal_Int32 n = rName.getLength();
76  if (!n)
77  return 0;
78 
79  sal_uInt8 nDupCount = 0;
80  const sal_Unicode* p = rName.getStr();
81  const sal_Unicode* pStart = p;
82  p += n-1; // Set it to the last char.
83  for (; p != pStart; --p, ++nDupCount)
84  {
85  if (*p != '*')
86  break;
87  }
88 
89  return nDupCount;
90 }
91 
92 OUString ScDPUtil::createDuplicateDimensionName(const OUString& rOriginal, size_t nDupCount)
93 {
94  if (!nDupCount)
95  return rOriginal;
96 
97  OUStringBuffer aBuf(rOriginal);
98  for (size_t i = 0; i < nDupCount; ++i)
99  aBuf.append('*');
100 
101  return aBuf.makeStringAndClear();
102 }
103 
105  sal_Int32 nDatePart, sal_Int32 nValue, SvNumberFormatter* pFormatter,
106  double fStart, double fEnd)
107 {
108  if (nValue == ScDPItemData::DateFirst)
109  return getSpecialDateName(fStart, true, pFormatter);
110  if (nValue == ScDPItemData::DateLast)
111  return getSpecialDateName(fEnd, false, pFormatter);
112 
113  switch ( nDatePart )
114  {
115  case sheet::DataPilotFieldGroupBy::YEARS:
116  return OUString::number(nValue);
117  case sheet::DataPilotFieldGroupBy::QUARTERS:
118  return ScGlobal::getLocaleData().getQuarterAbbreviation(sal_Int16(nValue-1)); // nValue is 1-based
119  case css::sheet::DataPilotFieldGroupBy::MONTHS:
121  i18n::CalendarDisplayIndex::MONTH, sal_Int16(nValue-1), 0); // 0-based, get short name
122  case sheet::DataPilotFieldGroupBy::DAYS:
123  {
124  Date aDate(1, 1, SC_DP_LEAPYEAR);
125  aDate.AddDays(nValue - 1); // nValue is 1-based
126  tools::Long nDays = aDate - pFormatter->GetNullDate();
127 
128  const sal_uInt32 nFormat = pFormatter->GetFormatIndex(NF_DATE_SYS_DDMMM, ScGlobal::eLnge);
129  const Color* pColor;
130  OUString aStr;
131  pFormatter->GetOutputString(nDays, nFormat, aStr, &pColor);
132  return aStr;
133  }
134  case sheet::DataPilotFieldGroupBy::HOURS:
135  {
136  //TODO: allow am/pm format?
137  return getTwoDigitString(nValue);
138  }
139  break;
140  case sheet::DataPilotFieldGroupBy::MINUTES:
141  case sheet::DataPilotFieldGroupBy::SECONDS:
142  {
143  return ScGlobal::getLocaleData().getTimeSep() + getTwoDigitString(nValue);
144  }
145  break;
146  default:
147  OSL_FAIL("invalid date part");
148  }
149 
150  return "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  return -std::numeric_limits<double>::infinity();
157 
158  if (fValue > rInfo.mfEnd && !rtl::math::approxEqual(fValue, rInfo.mfEnd))
159  return std::numeric_limits<double>::infinity();
160 
161  double fDiff = fValue - rInfo.mfStart;
162  double fDiv = rtl::math::approxFloor( fDiff / rInfo.mfStep );
163  double fGroupStart = rInfo.mfStart + fDiv * rInfo.mfStep;
164 
165  if (rtl::math::approxEqual(fGroupStart, rInfo.mfEnd) &&
166  !rtl::math::approxEqual(fGroupStart, rInfo.mfStart))
167  {
168  if (!rInfo.mbDateValues)
169  {
170  // A group that would consist only of the end value is not
171  // created, instead the value is included in the last group
172  // before. So the previous group is used if the calculated group
173  // start value is the selected end value.
174 
175  fDiv -= 1.0;
176  return rInfo.mfStart + fDiv * rInfo.mfStep;
177  }
178 
179  // For date values, the end value is instead treated as above the
180  // limit if it would be a group of its own.
181 
182  return rInfo.mfEnd + rInfo.mfStep;
183  }
184 
185  return fGroupStart;
186 }
187 
188 namespace {
189 
190 void lcl_AppendDateStr( OUStringBuffer& rBuffer, double fValue, SvNumberFormatter* pFormatter )
191 {
192  sal_uInt32 nFormat = pFormatter->GetStandardFormat( SvNumFormatType::DATE, ScGlobal::eLnge );
193  OUString aString;
194  pFormatter->GetInputLineString( fValue, nFormat, aString );
195  rBuffer.append( aString );
196 }
197 
198 OUString lcl_GetSpecialNumGroupName( double fValue, bool bFirst, sal_Unicode cDecSeparator,
199  bool bDateValues, SvNumberFormatter* pFormatter )
200 {
201  OSL_ENSURE( cDecSeparator != 0, "cDecSeparator not initialized" );
202 
203  OUStringBuffer aBuffer;
204  aBuffer.append( bFirst ? '<' : '>' );
205  if ( bDateValues )
206  lcl_AppendDateStr( aBuffer, fValue, pFormatter );
207  else
208  rtl::math::doubleToUStringBuffer( aBuffer, fValue, rtl_math_StringFormat_Automatic,
209  rtl_math_DecimalPlaces_Max, cDecSeparator, true );
210  return aBuffer.makeStringAndClear();
211 }
212 
213 OUString lcl_GetNumGroupName(
214  double fStartValue, const ScDPNumGroupInfo& rInfo, sal_Unicode cDecSep,
215  SvNumberFormatter* pFormatter)
216 {
217  OSL_ENSURE( cDecSep != 0, "cDecSeparator not initialized" );
218 
219  double fStep = rInfo.mfStep;
220  double fEndValue = fStartValue + fStep;
221  if (rInfo.mbIntegerOnly && (rInfo.mbDateValues || !rtl::math::approxEqual(fEndValue, rInfo.mfEnd)))
222  {
223  // The second number of the group label is
224  // (first number + size - 1) if there are only integer numbers,
225  // (first number + size) if any non-integer numbers are involved.
226  // Exception: The last group (containing the end value) is always
227  // shown as including the end value (but not for dates).
228 
229  fEndValue -= 1.0;
230  }
231 
232  if ( fEndValue > rInfo.mfEnd && !rInfo.mbAutoEnd )
233  {
234  // limit the last group to the end value
235 
236  fEndValue = rInfo.mfEnd;
237  }
238 
239  OUStringBuffer aBuffer;
240  if ( rInfo.mbDateValues )
241  {
242  lcl_AppendDateStr( aBuffer, fStartValue, pFormatter );
243  aBuffer.append( " - " ); // with spaces
244  lcl_AppendDateStr( aBuffer, fEndValue, pFormatter );
245  }
246  else
247  {
248  rtl::math::doubleToUStringBuffer( aBuffer, fStartValue, rtl_math_StringFormat_Automatic,
249  rtl_math_DecimalPlaces_Max, cDecSep, true );
250  aBuffer.append( '-' );
251  rtl::math::doubleToUStringBuffer( aBuffer, fEndValue, rtl_math_StringFormat_Automatic,
252  rtl_math_DecimalPlaces_Max, cDecSep, true );
253  }
254 
255  return aBuffer.makeStringAndClear();
256 }
257 
258 }
259 
261  double fValue, const ScDPNumGroupInfo& rInfo, sal_Unicode cDecSep, SvNumberFormatter* pFormatter)
262 {
263  if ( fValue < rInfo.mfStart && !rtl::math::approxEqual( fValue, rInfo.mfStart ) )
264  return lcl_GetSpecialNumGroupName( rInfo.mfStart, true, cDecSep, rInfo.mbDateValues, pFormatter );
265 
266  if ( fValue > rInfo.mfEnd && !rtl::math::approxEqual( fValue, rInfo.mfEnd ) )
267  return lcl_GetSpecialNumGroupName( rInfo.mfEnd, false, cDecSep, rInfo.mbDateValues, pFormatter );
268 
269  double fDiff = fValue - rInfo.mfStart;
270  double fDiv = rtl::math::approxFloor( fDiff / rInfo.mfStep );
271  double fGroupStart = rInfo.mfStart + fDiv * rInfo.mfStep;
272 
273  if ( rtl::math::approxEqual( fGroupStart, rInfo.mfEnd ) &&
274  !rtl::math::approxEqual( fGroupStart, rInfo.mfStart ) )
275  {
276  if (rInfo.mbDateValues)
277  {
278  // For date values, the end value is instead treated as above the limit
279  // if it would be a group of its own.
280  return lcl_GetSpecialNumGroupName( rInfo.mfEnd, false, cDecSep, rInfo.mbDateValues, pFormatter );
281  }
282  }
283 
284  return lcl_GetNumGroupName(fGroupStart, rInfo, cDecSep, pFormatter);
285 }
286 
288  double fValue, const ScDPNumGroupInfo* pInfo, sal_Int32 nDatePart,
289  const SvNumberFormatter* pFormatter)
290 {
291  // Start and end are inclusive
292  // (End date without a time value is included, with a time value it's not)
293 
294  if (pInfo)
295  {
296  if (fValue < pInfo->mfStart && !rtl::math::approxEqual(fValue, pInfo->mfStart))
298  if (fValue > pInfo->mfEnd && !rtl::math::approxEqual(fValue, pInfo->mfEnd))
299  return ScDPItemData::DateLast;
300  }
301 
302  sal_Int32 nResult = 0;
303 
304  if (nDatePart == sheet::DataPilotFieldGroupBy::HOURS ||
305  nDatePart == sheet::DataPilotFieldGroupBy::MINUTES ||
306  nDatePart == sheet::DataPilotFieldGroupBy::SECONDS)
307  {
308  // handle time
309  // (do as in the cell functions, ScInterpreter::ScGetHour() etc.)
310 
311  sal_uInt16 nHour, nMinute, nSecond;
312  double fFractionOfSecond;
313  tools::Time::GetClock( fValue, nHour, nMinute, nSecond, fFractionOfSecond, 0);
314 
315  switch (nDatePart)
316  {
317  case sheet::DataPilotFieldGroupBy::HOURS:
318  nResult = nHour;
319  break;
320  case sheet::DataPilotFieldGroupBy::MINUTES:
321  nResult = nMinute;
322  break;
323  case sheet::DataPilotFieldGroupBy::SECONDS:
324  nResult = nSecond;
325  break;
326  }
327  }
328  else
329  {
330  Date aDate = pFormatter->GetNullDate();
331  aDate.AddDays(::rtl::math::approxFloor(fValue));
332 
333  switch ( nDatePart )
334  {
335  case css::sheet::DataPilotFieldGroupBy::YEARS:
336  nResult = aDate.GetYear();
337  break;
338  case css::sheet::DataPilotFieldGroupBy::QUARTERS:
339  nResult = 1 + (aDate.GetMonth() - 1) / 3; // 1..4
340  break;
341  case css::sheet::DataPilotFieldGroupBy::MONTHS:
342  nResult = aDate.GetMonth(); // 1..12
343  break;
344  case css::sheet::DataPilotFieldGroupBy::DAYS:
345  {
346  Date aYearStart(1, 1, aDate.GetYear());
347  nResult = (aDate - aYearStart) + 1; // Jan 01 has value 1
348  if (nResult >= 60 && !aDate.IsLeapYear())
349  {
350  // days are counted from 1 to 366 - if not from a leap year, adjust
351  ++nResult;
352  }
353  }
354  break;
355  default:
356  OSL_FAIL("invalid date part");
357  }
358  }
359 
360  return nResult;
361 }
362 
363 namespace {
364 
365 const TranslateId aFuncStrIds[] = {
366  {}, // SUBTOTAL_FUNC_NONE
367  STR_FUN_TEXT_AVG, // SUBTOTAL_FUNC_AVE
368  STR_FUN_TEXT_COUNT, // SUBTOTAL_FUNC_CNT
369  STR_FUN_TEXT_COUNT, // SUBTOTAL_FUNC_CNT2
370  STR_FUN_TEXT_MAX, // SUBTOTAL_FUNC_MAX
371  STR_FUN_TEXT_MIN, // SUBTOTAL_FUNC_MIN
372  STR_FUN_TEXT_PRODUCT, // SUBTOTAL_FUNC_PROD
373  STR_FUN_TEXT_STDDEV, // SUBTOTAL_FUNC_STD
374  STR_FUN_TEXT_STDDEV, // SUBTOTAL_FUNC_STDP
375  STR_FUN_TEXT_SUM, // SUBTOTAL_FUNC_SUM
376  STR_FUN_TEXT_VAR, // SUBTOTAL_FUNC_VAR
377  STR_FUN_TEXT_VAR, // SUBTOTAL_FUNC_VARP
378  STR_FUN_TEXT_MEDIAN, // SUBTOTAL_FUNC_MED
379  {} // SUBTOTAL_FUNC_SELECTION_COUNT - not used for pivot table
380 };
381 
382 }
383 
384 OUString ScDPUtil::getDisplayedMeasureName(const OUString& rName, ScSubTotalFunc eFunc)
385 {
386  assert(unsigned(eFunc) < SAL_N_ELEMENTS(aFuncStrIds));
387  TranslateId pId = aFuncStrIds[eFunc];
388  if (!pId)
389  return rName;
390  return ScResId(pId) + // function name
391  " - " +
392  rName; // field name
393 }
394 
396 {
398  switch (eGenFunc)
399  {
400  case ScGeneralFunction::NONE: eSubTotal = SUBTOTAL_FUNC_NONE; break;
401  case ScGeneralFunction::SUM: eSubTotal = SUBTOTAL_FUNC_SUM; break;
402  case ScGeneralFunction::COUNT: eSubTotal = SUBTOTAL_FUNC_CNT2; break;
403  case ScGeneralFunction::AVERAGE: eSubTotal = SUBTOTAL_FUNC_AVE; break;
404  case ScGeneralFunction::MEDIAN: eSubTotal = SUBTOTAL_FUNC_MED; break;
405  case ScGeneralFunction::MAX: eSubTotal = SUBTOTAL_FUNC_MAX; break;
406  case ScGeneralFunction::MIN: eSubTotal = SUBTOTAL_FUNC_MIN; break;
407  case ScGeneralFunction::PRODUCT: eSubTotal = SUBTOTAL_FUNC_PROD; break;
408  case ScGeneralFunction::COUNTNUMS: eSubTotal = SUBTOTAL_FUNC_CNT; break;
409  case ScGeneralFunction::STDEV: eSubTotal = SUBTOTAL_FUNC_STD; break;
410  case ScGeneralFunction::STDEVP: eSubTotal = SUBTOTAL_FUNC_STDP; break;
411  case ScGeneralFunction::VAR: eSubTotal = SUBTOTAL_FUNC_VAR; break;
412  case ScGeneralFunction::VARP: eSubTotal = SUBTOTAL_FUNC_VARP; break;
413  case ScGeneralFunction::AUTO: eSubTotal = SUBTOTAL_FUNC_NONE; break;
414  default:
415  assert(false);
416  }
417  return eSubTotal;
418 }
419 
420 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
static SC_DLLPUBLIC OUString createDuplicateDimensionName(const OUString &rOriginal, size_t nDupCount)
Definition: dputil.cxx:92
OString stripEnd(std::string_view rIn, char c)
function is determined automatically.
OUString ScResId(TranslateId aId)
Definition: scdll.cxx:89
const sal_uInt16 SC_DP_LEAPYEAR
Definition: dpgroup.cxx:45
sal_uInt32 GetFormatIndex(NfIndexTableOffset, LanguageType eLnge=LANGUAGE_DONTKNOW)
long Long
static OUString getNumGroupName(double fValue, const ScDPNumGroupInfo &rInfo, sal_Unicode cDecSep, SvNumberFormatter *pFormatter)
Definition: dputil.cxx:260
sal_Int64 n
aBuf
bool IsLeapYear() const
numerical values are counted.
sum of all numerical values is calculated.
product of all numerical values is calculated.
static bool isDuplicateDimension(std::u16string_view rName)
Definition: dputil.cxx:61
NF_DATE_SYS_DDMMM
sal_uInt16 sal_Unicode
static SC_DLLPUBLIC const LocaleDataWrapper & getLocaleData()
Definition: global.cxx:1004
static SC_DLLPUBLIC OUString getDateGroupName(sal_Int32 nDatePart, sal_Int32 nValue, SvNumberFormatter *pFormatter, double fStart, double fEnd)
Definition: dputil.cxx:104
sal_uInt16 GetMonth() const
variance is calculated based on the entire population.
static double getNumGroupStartValue(double fValue, const ScDPNumGroupInfo &rInfo)
Definition: dputil.cxx:153
static SC_DLLPUBLIC OUString getSourceDimensionName(std::u16string_view rName)
Definition: dputil.cxx:66
maximum value of all numerical values is calculated.
#define SAL_N_ELEMENTS(arr)
sal_Int16 GetYear() const
standard deviation is calculated based on the entire population.
int i
average of all numerical values is calculated.
float u
void GetInputLineString(const double &fOutNumber, sal_uInt32 nFIndex, OUString &rOutString, bool bFiltering=false)
void AddDays(sal_Int32 nAddDays)
static SC_DLLPUBLIC LanguageType eLnge
Definition: global.hxx:538
static sal_uInt8 getDuplicateIndex(const OUString &rName)
Get a duplicate index in case the dimension is a duplicate.
Definition: dputil.cxx:71
OUString getDisplayName(sal_Int16 nCalendarDisplayIndex, sal_Int16 nIdx, sal_Int16 nNameType) const
median of all numerical values is calculated.
std::unique_ptr< char[]> aBuffer
static void GetClock(double fTimeInDays, sal_uInt16 &nHour, sal_uInt16 &nMinute, sal_uInt16 &nSecond, double &fFractionOfSecond, int nFractionDecimals)
static const sal_Int32 DateLast
Definition: dpitemdata.hxx:36
unsigned char sal_uInt8
ScGeneralFunction
the css::sheet::GeneralFunction enum is extended by constants in GeneralFunction2, which causes some type-safety issues.
static CalendarWrapper & GetCalendar()
Definition: global.cxx:1022
sal_uInt32 GetStandardFormat(SvNumFormatType eType, LanguageType eLnge=LANGUAGE_DONTKNOW)
static const sal_Int32 DateFirst
Definition: dpitemdata.hxx:35
variance is calculated based on a sample.
all values, including non-numerical values, are counted.
static sal_Int32 getDatePartValue(double fValue, const ScDPNumGroupInfo *pInfo, sal_Int32 nDatePart, const SvNumberFormatter *pFormatter)
Definition: dputil.cxx:287
nothing is calculated.
void * p
const OUString & getTimeSep() const
minimum value of all numerical values is calculated.
standard deviation is calculated based on a sample.
constexpr bool ends_with(std::basic_string_view< charT, traits > sv, std::basic_string_view< charT, traits > x) noexcept
static SC_DLLPUBLIC OUString getDisplayedMeasureName(const OUString &rName, ScSubTotalFunc eFunc)
Definition: dputil.cxx:384
const OUString & getQuarterAbbreviation(sal_Int16 nQuarter) const
ScSubTotalFunc
Definition: global.hxx:834
const Date & GetNullDate() const
aStr
void GetOutputString(const double &fOutNumber, sal_uInt32 nFIndex, OUString &sOutString, const Color **ppColor, bool bUseStarFormat=false)
static SC_DLLPUBLIC ScSubTotalFunc toSubTotalFunc(ScGeneralFunction eGenFunc)
Definition: dputil.cxx:395