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