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/zforlist.hxx>
21 #include <rtl/math.hxx>
22 #include <osl/diagnose.h>
23 
24 #include <com/sun/star/sheet/DataPilotFieldGroupBy.hpp>
25 #include <com/sun/star/i18n/CalendarDisplayIndex.hpp>
26 
27 using namespace com::sun::star;
28 
29 namespace {
30 
31 const sal_uInt16 SC_DP_LEAPYEAR = 1648; // arbitrary leap year for date calculations
32 
33 OUString getTwoDigitString(sal_Int32 nValue)
34 {
35  OUString aRet = OUString::number( nValue );
36  if ( aRet.getLength() < 2 )
37  aRet = "0" + aRet;
38  return aRet;
39 }
40 
41 void appendDateStr(OUStringBuffer& rBuffer, double fValue, SvNumberFormatter* pFormatter)
42 {
43  sal_uInt32 nFormat = pFormatter->GetStandardFormat( SvNumFormatType::DATE, ScGlobal::eLnge );
44  OUString aString;
45  pFormatter->GetInputLineString(fValue, nFormat, aString);
46  rBuffer.append(aString);
47 }
48 
49 OUString getSpecialDateName(double fValue, bool bFirst, SvNumberFormatter* pFormatter)
50 {
51  OUStringBuffer aBuffer;
52  aBuffer.append( bFirst ? '<' : '>' );
53  appendDateStr(aBuffer, fValue, pFormatter);
54  return aBuffer.makeStringAndClear();
55 }
56 
57 }
58 
59 bool ScDPUtil::isDuplicateDimension(const OUString& rName)
60 {
61  return rName.endsWith("*");
62 }
63 
64 OUString ScDPUtil::getSourceDimensionName(const OUString& rName)
65 {
66  return comphelper::string::stripEnd(rName, '*');
67 }
68 
69 sal_uInt8 ScDPUtil::getDuplicateIndex(const OUString& rName)
70 {
71  // Count all trailing '*'s.
72 
73  sal_Int32 n = rName.getLength();
74  if (!n)
75  return 0;
76 
77  sal_uInt8 nDupCount = 0;
78  const sal_Unicode* p = rName.getStr();
79  const sal_Unicode* pStart = p;
80  p += n-1; // Set it to the last char.
81  for (; p != pStart; --p, ++nDupCount)
82  {
83  if (*p != '*')
84  break;
85  }
86 
87  return nDupCount;
88 }
89 
90 OUString ScDPUtil::createDuplicateDimensionName(const OUString& rOriginal, size_t nDupCount)
91 {
92  if (!nDupCount)
93  return rOriginal;
94 
95  OUStringBuffer aBuf(rOriginal);
96  for (size_t i = 0; i < nDupCount; ++i)
97  aBuf.append('*');
98 
99  return aBuf.makeStringAndClear();
100 }
101 
103  sal_Int32 nDatePart, sal_Int32 nValue, SvNumberFormatter* pFormatter,
104  double fStart, double fEnd)
105 {
106  if (nValue == ScDPItemData::DateFirst)
107  return getSpecialDateName(fStart, true, pFormatter);
108  if (nValue == ScDPItemData::DateLast)
109  return getSpecialDateName(fEnd, false, pFormatter);
110 
111  switch ( nDatePart )
112  {
113  case sheet::DataPilotFieldGroupBy::YEARS:
114  return OUString::number(nValue);
115  case sheet::DataPilotFieldGroupBy::QUARTERS:
116  return ScGlobal::getLocaleDataPtr()->getQuarterAbbreviation(sal_Int16(nValue-1)); // nValue is 1-based
117  case css::sheet::DataPilotFieldGroupBy::MONTHS:
119  i18n::CalendarDisplayIndex::MONTH, sal_Int16(nValue-1), 0); // 0-based, get short name
120  case sheet::DataPilotFieldGroupBy::DAYS:
121  {
122  Date aDate(1, 1, SC_DP_LEAPYEAR);
123  aDate.AddDays(nValue - 1); // nValue is 1-based
124  tools::Long nDays = aDate - pFormatter->GetNullDate();
125 
126  const sal_uInt32 nFormat = pFormatter->GetFormatIndex(NF_DATE_SYS_DDMMM, ScGlobal::eLnge);
127  const 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::getLocaleDataPtr()->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 "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_uInt32 nFormat = pFormatter->GetStandardFormat( SvNumFormatType::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.append( " - " ); // 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 
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 
294  double fValue, const ScDPNumGroupInfo* pInfo, sal_Int32 nDatePart,
295  const 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))
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  // (do as in the cell functions, ScInterpreter::ScGetHour() etc.)
316 
317  sal_uInt16 nHour, nMinute, nSecond;
318  double fFractionOfSecond;
319  tools::Time::GetClock( fValue, nHour, nMinute, nSecond, fFractionOfSecond, 0);
320 
321  switch (nDatePart)
322  {
323  case sheet::DataPilotFieldGroupBy::HOURS:
324  nResult = nHour;
325  break;
326  case sheet::DataPilotFieldGroupBy::MINUTES:
327  nResult = nMinute;
328  break;
329  case sheet::DataPilotFieldGroupBy::SECONDS:
330  nResult = nSecond;
331  break;
332  }
333  }
334  else
335  {
336  Date aDate = pFormatter->GetNullDate();
337  aDate.AddDays(::rtl::math::approxFloor(fValue));
338 
339  switch ( nDatePart )
340  {
341  case css::sheet::DataPilotFieldGroupBy::YEARS:
342  nResult = aDate.GetYear();
343  break;
344  case css::sheet::DataPilotFieldGroupBy::QUARTERS:
345  nResult = 1 + (aDate.GetMonth() - 1) / 3; // 1..4
346  break;
347  case css::sheet::DataPilotFieldGroupBy::MONTHS:
348  nResult = aDate.GetMonth(); // 1..12
349  break;
350  case css::sheet::DataPilotFieldGroupBy::DAYS:
351  {
352  Date aYearStart(1, 1, aDate.GetYear());
353  nResult = (aDate - aYearStart) + 1; // Jan 01 has value 1
354  if (nResult >= 60 && !aDate.IsLeapYear())
355  {
356  // days are counted from 1 to 366 - if not from a leap year, adjust
357  ++nResult;
358  }
359  }
360  break;
361  default:
362  OSL_FAIL("invalid date part");
363  }
364  }
365 
366  return nResult;
367 }
368 
369 namespace {
370 
371 const char* aFuncStrIds[] = {
372  nullptr, // SUBTOTAL_FUNC_NONE
373  STR_FUN_TEXT_AVG, // SUBTOTAL_FUNC_AVE
374  STR_FUN_TEXT_COUNT, // SUBTOTAL_FUNC_CNT
375  STR_FUN_TEXT_COUNT, // SUBTOTAL_FUNC_CNT2
376  STR_FUN_TEXT_MAX, // SUBTOTAL_FUNC_MAX
377  STR_FUN_TEXT_MIN, // SUBTOTAL_FUNC_MIN
378  STR_FUN_TEXT_PRODUCT, // SUBTOTAL_FUNC_PROD
379  STR_FUN_TEXT_STDDEV, // SUBTOTAL_FUNC_STD
380  STR_FUN_TEXT_STDDEV, // SUBTOTAL_FUNC_STDP
381  STR_FUN_TEXT_SUM, // SUBTOTAL_FUNC_SUM
382  STR_FUN_TEXT_VAR, // SUBTOTAL_FUNC_VAR
383  STR_FUN_TEXT_VAR, // SUBTOTAL_FUNC_VARP
384  STR_FUN_TEXT_MEDIAN, // SUBTOTAL_FUNC_MED
385  nullptr // SUBTOTAL_FUNC_SELECTION_COUNT - not used for pivot table
386 };
387 
388 }
389 
390 OUString ScDPUtil::getDisplayedMeasureName(const OUString& rName, ScSubTotalFunc eFunc)
391 {
392  OUStringBuffer aRet;
393  assert(unsigned(eFunc) < SAL_N_ELEMENTS(aFuncStrIds));
394  const char* pId = aFuncStrIds[eFunc];
395  if (pId)
396  {
397  aRet.append(ScResId(pId)); // function name
398  aRet.append(" - ");
399  }
400  aRet.append(rName); // field name
401 
402  return aRet.makeStringAndClear();
403 }
404 
406 {
408  switch (eGenFunc)
409  {
410  case ScGeneralFunction::NONE: eSubTotal = SUBTOTAL_FUNC_NONE; break;
411  case ScGeneralFunction::SUM: eSubTotal = SUBTOTAL_FUNC_SUM; break;
412  case ScGeneralFunction::COUNT: eSubTotal = SUBTOTAL_FUNC_CNT2; break;
413  case ScGeneralFunction::AVERAGE: eSubTotal = SUBTOTAL_FUNC_AVE; break;
414  case ScGeneralFunction::MEDIAN: eSubTotal = SUBTOTAL_FUNC_MED; break;
415  case ScGeneralFunction::MAX: eSubTotal = SUBTOTAL_FUNC_MAX; break;
416  case ScGeneralFunction::MIN: eSubTotal = SUBTOTAL_FUNC_MIN; break;
417  case ScGeneralFunction::PRODUCT: eSubTotal = SUBTOTAL_FUNC_PROD; break;
418  case ScGeneralFunction::COUNTNUMS: eSubTotal = SUBTOTAL_FUNC_CNT; break;
419  case ScGeneralFunction::STDEV: eSubTotal = SUBTOTAL_FUNC_STD; break;
420  case ScGeneralFunction::STDEVP: eSubTotal = SUBTOTAL_FUNC_STDP; break;
421  case ScGeneralFunction::VAR: eSubTotal = SUBTOTAL_FUNC_VAR; break;
422  case ScGeneralFunction::VARP: eSubTotal = SUBTOTAL_FUNC_VARP; break;
423  case ScGeneralFunction::AUTO: eSubTotal = SUBTOTAL_FUNC_NONE; break;
424  default:
425  assert(false);
426  }
427  return eSubTotal;
428 }
429 
430 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
OString stripEnd(const OString &rIn, char c)
static SC_DLLPUBLIC OUString createDuplicateDimensionName(const OUString &rOriginal, size_t nDupCount)
Definition: dputil.cxx:90
function is determined automatically.
const sal_uInt16 SC_DP_LEAPYEAR
Definition: dpgroup.cxx:44
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:266
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 OUString getDateGroupName(sal_Int32 nDatePart, sal_Int32 nValue, SvNumberFormatter *pFormatter, double fStart, double fEnd)
Definition: dputil.cxx:102
const BorderLinePrimitive2D *pCandidateB assert(pCandidateA)
sal_uInt16 GetMonth() const
variance is calculated based on the entire population.
static double getNumGroupStartValue(double fValue, const ScDPNumGroupInfo &rInfo)
Definition: dputil.cxx:153
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.
static SC_DLLPUBLIC OUString getSourceDimensionName(const OUString &rName)
Definition: dputil.cxx:64
OUString ScResId(const char *pId)
Definition: scdll.cxx:95
static bool isDuplicateDimension(const OUString &rName)
Definition: dputil.cxx:59
void AddDays(sal_Int32 nAddDays)
static SC_DLLPUBLIC LanguageType eLnge
Definition: global.hxx:548
void GetInputLineString(const double &fOutNumber, sal_uInt32 nFIndex, OUString &rOutString)
static sal_uInt8 getDuplicateIndex(const OUString &rName)
Get a duplicate index in case the dimension is a duplicate.
Definition: dputil.cxx:69
OUString getDisplayName(sal_Int16 nCalendarDisplayIndex, sal_Int16 nIdx, sal_Int16 nNameType) const
median of all numerical values is calculated.
static SC_DLLPUBLIC const LocaleDataWrapper * getLocaleDataPtr()
Definition: global.cxx:1001
std::unique_ptr< char[]> aBuffer
static CalendarWrapper * GetCalendar()
Definition: global.cxx:1019
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:37
unsigned char sal_uInt8
ScGeneralFunction
the css::sheet::GeneralFunction enum is extended by constants in GeneralFunction2, which causes some type-safety issues.
sal_uInt32 GetStandardFormat(SvNumFormatType eType, LanguageType eLnge=LANGUAGE_DONTKNOW)
static const sal_Int32 DateFirst
Definition: dpitemdata.hxx:36
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:293
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:390
const OUString & getQuarterAbbreviation(sal_Int16 nQuarter) const
ScSubTotalFunc
Definition: global.hxx:845
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:405