Resolves: tdf#127831 implement RAND.NV() and RANDBETWEEN.NV() non-volatile

Same as RAND() and RANDBETWEEN() but not recalculating on every
change, just the normal expression recalculation.

Change-Id: I8ba7099125e487a78bd3d91db8b666c2f36b22fd
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/92994
Tested-by: Jenkins
Reviewed-by: Eike Rathke <erack@redhat.com>

11 files changed, 73 insertions, 9 deletions

diff --git a/sc/inc/helpids.h b/sc/inc/helpids.h
index d6616f106784..f9c30fc615a0 100644
--- a/sc/inc/helpids.h
+++ b/sc/inc/helpids.h
@@ -579,6 +579,8 @@
 #define HID_FUNC_SEARCHB                                        "SC_HID_FUNC_SEARCHB"
 #define HID_FUNC_REGEX                                          "SC_HID_FUNC_REGEX"
 #define HID_FUNC_FOURIER                                        "SC_HID_FUNC_FOURIER"
+#define HID_FUNC_RAND_NV                                        "SC_HID_FUNC_RAND_NV"
+#define HID_FUNC_RANDBETWEEN_NV                                 "SC_HID_FUNC_RANDBETWEEN_NV"
 
 #endif
 
diff --git a/sc/inc/scfuncs.hrc b/sc/inc/scfuncs.hrc
index 77031c5a5ac8..9a96b3d9975c 100644
--- a/sc/inc/scfuncs.hrc
+++ b/sc/inc/scfuncs.hrc
@@ -4134,6 +4134,22 @@ const char* SC_OPCODE_FOURIER_ARY[] =
     NC_("SC_OPCODE_FOURIER", "In case of Polar=TRUE, the frequency components below this magnitude are clipped out (default 0.0).")
 };
 
+// -=*# Resource for function RAND.NV #*=-
+const char* SC_OPCODE_RANDOM_NV_ARY[] =
+{
+    NC_("SC_OPCODE_RANDOM_NV", "Returns a random number between 0 and 1, non-volatile.")
+};
+
+// -=*# Resource for function RANDBETWEEN.NV #*=-
+const char* SC_OPCODE_RANDBETWEEN_NV_ARY[] =
+{
+    NC_("SC_OPCODE_RANDBETWEEN_NV", "Returns a random integer between the numbers you specify, non-volatile."),
+    NC_("SC_OPCODE_RANDBETWEEN_NV", "Bottom"),
+    NC_("SC_OPCODE_RANDBETWEEN_NV", "The smallest integer returned."),
+    NC_("SC_OPCODE_RANDBETWEEN_NV", "Top"),
+    NC_("SC_OPCODE_RANDBETWEEN_NV", "The largest integer returned.")
+};
+
 #endif
 
 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/sc/qa/extras/scfunctionlistobj.cxx b/sc/qa/extras/scfunctionlistobj.cxx
index cf8c962bfb06..385ea7d8962a 100644
--- a/sc/qa/extras/scfunctionlistobj.cxx
+++ b/sc/qa/extras/scfunctionlistobj.cxx
@@ -82,7 +82,7 @@ private:
 ScFunctionListObj::ScFunctionListObj()
     : CalcUnoApiTest("/sc/qa/extras/testdocuments")
     , XElementAccess(cppu::UnoType<uno::Sequence<beans::PropertyValue>>::get())
-    , XIndexAccess(393)
+    , XIndexAccess(395)
     , XNameAccess("IF")
     , XServiceInfo("stardiv.StarCalc.ScFunctionListObj", "com.sun.star.sheet.FunctionDescriptions")
 {
diff --git a/sc/qa/unit/ucalc.cxx b/sc/qa/unit/ucalc.cxx
index 964247b9fe7f..0bc11aaeeaae 100644
--- a/sc/qa/unit/ucalc.cxx
+++ b/sc/qa/unit/ucalc.cxx
@@ -2405,6 +2405,8 @@ void Test::testFunctionLists()
         "PRODUCT",
         "RADIANS",
         "RAND",
+        "RAND.NV",
+        "RANDBETWEEN.NV",
         "RAWSUBTRACT",
         "ROUND",
         "ROUNDDOWN",
diff --git a/sc/source/core/data/funcdesc.cxx b/sc/source/core/data/funcdesc.cxx
index 7c37b12f3d65..6768dd6f8bcd 100644
--- a/sc/source/core/data/funcdesc.cxx
+++ b/sc/source/core/data/funcdesc.cxx
@@ -408,6 +408,7 @@ ScFunctionList::ScFunctionList()
         { SC_OPCODE_GET_ACT_TIME, ENTRY(SC_OPCODE_GET_ACT_TIME_ARY), 0, ID_FUNCTION_GRP_DATETIME, HID_FUNC_JETZT, 0, { } },
         { SC_OPCODE_NO_VALUE, ENTRY(SC_OPCODE_NO_VALUE_ARY), 0, ID_FUNCTION_GRP_INFO, HID_FUNC_NV, 0, { } },
         { SC_OPCODE_CURRENT, ENTRY(SC_OPCODE_CURRENT_ARY), 0, ID_FUNCTION_GRP_INFO, HID_FUNC_AKTUELL, 0, { } },
+        { SC_OPCODE_RANDOM_NV, ENTRY(SC_OPCODE_RANDOM_NV_ARY), 0, ID_FUNCTION_GRP_MATH, HID_FUNC_RAND_NV, 0, { } },
         { SC_OPCODE_DEG, ENTRY(SC_OPCODE_DEG_ARY), 0, ID_FUNCTION_GRP_MATH, HID_FUNC_DEG, 1, { 0 } },
         { SC_OPCODE_RAD, ENTRY(SC_OPCODE_RAD_ARY), 0, ID_FUNCTION_GRP_MATH, HID_FUNC_RAD, 1, { 0 } },
         { SC_OPCODE_SIN, ENTRY(SC_OPCODE_SIN_ARY), 0, ID_FUNCTION_GRP_MATH, HID_FUNC_SIN, 1, { 0 } },
@@ -786,7 +787,8 @@ ScFunctionList::ScFunctionList()
         { SC_OPCODE_FINDB, ENTRY(SC_OPCODE_FINDB_ARY), 0, ID_FUNCTION_GRP_TEXT, HID_FUNC_FINDB, 3, { 0, 0, 1 } },
         { SC_OPCODE_SEARCHB, ENTRY(SC_OPCODE_SEARCHB_ARY), 0, ID_FUNCTION_GRP_TEXT, HID_FUNC_SEARCHB, 3, { 0, 0, 1 } },
         { SC_OPCODE_REGEX, ENTRY(SC_OPCODE_REGEX_ARY), 0, ID_FUNCTION_GRP_TEXT, HID_FUNC_REGEX, 4, { 0, 0, 1, 1 } },
-        { SC_OPCODE_FOURIER, ENTRY(SC_OPCODE_FOURIER_ARY), 0, ID_FUNCTION_GRP_MATRIX, HID_FUNC_FOURIER, 5, { 0, 0, 1, 1, 1 } }
+        { SC_OPCODE_FOURIER, ENTRY(SC_OPCODE_FOURIER_ARY), 0, ID_FUNCTION_GRP_MATRIX, HID_FUNC_FOURIER, 5, { 0, 0, 1, 1, 1 } },
+        { SC_OPCODE_RANDBETWEEN_NV, ENTRY(SC_OPCODE_RANDBETWEEN_NV_ARY), 0, ID_FUNCTION_GRP_MATH, HID_FUNC_RANDBETWEEN_NV, 2, { 0, 0 } }
     };
 
     ScFuncDesc* pDesc = nullptr;
diff --git a/sc/source/core/inc/interpre.hxx b/sc/source/core/inc/interpre.hxx
index 4cecd7ef3058..9aaeeb96259c 100644
--- a/sc/source/core/inc/interpre.hxx
+++ b/sc/source/core/inc/interpre.hxx
@@ -530,6 +530,9 @@ private:
     void ScUnionFunc();
     void ScPi();
     void ScRandom();
+    void ScRandbetween();
+    void ScRandomImpl( const std::function<double( double fFirst, double fLast )>& RandomFunc,
+            double fFirst, double fLast );
     void ScTrue();
     void ScFalse();
     void ScDeg();
diff --git a/sc/source/core/tool/interpr1.cxx b/sc/source/core/tool/interpr1.cxx
index 03a8f46731a6..839c5135d665 100644
--- a/sc/source/core/tool/interpr1.cxx
+++ b/sc/source/core/tool/interpr1.cxx
@@ -1736,13 +1736,14 @@ void ScInterpreter::ScPi()
     PushDouble(F_PI);
 }
 
-void ScInterpreter::ScRandom()
+void ScInterpreter::ScRandomImpl( const std::function<double( double fFirst, double fLast )>& RandomFunc,
+        double fFirst, double fLast )
 {
     if (bMatrixFormula)
     {
         SCCOL nCols = 0;
         SCROW nRows = 0;
-        if(pMyFormulaCell)
+        if (pMyFormulaCell)
             pMyFormulaCell->GetMatColsRows( nCols, nRows);
 
         if (nCols == 1 && nRows == 1)
@@ -1752,7 +1753,7 @@ void ScInterpreter::ScRandom()
             // default are executed in array context unless
             // FA.setPropertyValue("IsArrayFunction",False) was set, return a
             // scalar double instead of a 1x1 matrix object. tdf#128218
-            PushDouble( comphelper::rng::uniform_real_distribution());
+            PushDouble( RandomFunc( fFirst, fLast));
             return;
         }
 
@@ -1773,7 +1774,7 @@ void ScInterpreter::ScRandom()
             {
                 for (SCROW j=0; j < nRows; ++j)
                 {
-                    pResMat->PutDouble( comphelper::rng::uniform_real_distribution(),
+                    pResMat->PutDouble( RandomFunc( fFirst, fLast),
                             static_cast<SCSIZE>(i), static_cast<SCSIZE>(j));
                 }
             }
@@ -1782,8 +1783,39 @@ void ScInterpreter::ScRandom()
     }
     else
     {
-        PushDouble( comphelper::rng::uniform_real_distribution());
+        PushDouble( RandomFunc( fFirst, fLast));
+    }
+}
+
+void ScInterpreter::ScRandom()
+{
+    auto RandomFunc = []( double, double )
+    {
+        return comphelper::rng::uniform_real_distribution();
+    };
+    ScRandomImpl( RandomFunc, 0.0, 0.0);
+}
+
+void ScInterpreter::ScRandbetween()
+{
+    if (!MustHaveParamCount( GetByte(), 2))
+        return;
+
+    // Same like scaddins/source/analysis/analysis.cxx
+    // AnalysisAddIn::getRandbetween()
+    double fMax = rtl::math::round( GetDouble(), 0, rtl_math_RoundingMode_Up);
+    double fMin = rtl::math::round( GetDouble(), 0, rtl_math_RoundingMode_Up);
+    if (nGlobalError != FormulaError::NONE || fMin > fMax)
+    {
+        PushIllegalArgument();
+        return;
     }
+    fMax = std::nextafter( fMax+1, -DBL_MAX);
+    auto RandomFunc = []( double fFirst, double fLast )
+    {
+        return floor( comphelper::rng::uniform_real_distribution( fFirst, fLast));
+    };
+    ScRandomImpl( RandomFunc, fMin, fMax);
 }
 
 void ScInterpreter::ScTrue()
diff --git a/sc/source/core/tool/interpr4.cxx b/sc/source/core/tool/interpr4.cxx
index 6fd4da8a1e36..7a6f03026121 100644
--- a/sc/source/core/tool/interpr4.cxx
+++ b/sc/source/core/tool/interpr4.cxx
@@ -4062,6 +4062,8 @@ StackVar ScInterpreter::Interpret()
                 case ocPercentSign      : ScPercentSign();              break;
                 case ocPi               : ScPi();                       break;
                 case ocRandom           : ScRandom();                   break;
+                case ocRandomNV         : ScRandom();                   break;
+                case ocRandbetweenNV    : ScRandbetween();              break;
                 case ocTrue             : ScTrue();                     break;
                 case ocFalse            : ScFalse();                    break;
                 case ocGetActDate       : ScGetActDate();               break;
diff --git a/sc/source/core/tool/parclass.cxx b/sc/source/core/tool/parclass.cxx
index 3f9843fb333c..b2f0914b680a 100644
--- a/sc/source/core/tool/parclass.cxx
+++ b/sc/source/core/tool/parclass.cxx
@@ -221,6 +221,7 @@ const ScParameterClassification::RawData ScParameterClassification::pRawData[] =
     { ocQuartile_Inc,    {{ Reference, Value                                     }, 0, Value }},
     { ocRSQ,             {{ ForceArray, ForceArray                               }, 0, Value }},
     { ocRandom,          {{ Bounds                                               }, 0, Value }},
+    { ocRandomNV,        {{ Bounds                                               }, 0, Value }},
     { ocRange,           {{ Reference, Reference                                 }, 0, Reference }},
     { ocRank,            {{ Value, Reference, Value                              }, 0, Value }},
     { ocRank_Avg,        {{ Value, Reference, Value                              }, 0, Value }},
diff --git a/sc/source/filter/excel/xlformula.cxx b/sc/source/filter/excel/xlformula.cxx
index c4037b4c9d91..30db442f67de 100644
--- a/sc/source/filter/excel/xlformula.cxx
+++ b/sc/source/filter/excel/xlformula.cxx
@@ -638,7 +638,9 @@ static const XclFunctionInfo saFuncTable_OOoLO[] =
     EXC_FUNCENTRY_OOO( ocForecast_ETS_STM, 3,  6,  0,  "ORG.LIBREOFFICE.FORECAST.ETS.STAT.MULT" ),
     EXC_FUNCENTRY_OOO( ocRoundSig,      2,  2,  0,  "ORG.LIBREOFFICE.ROUNDSIG" ),
     EXC_FUNCENTRY_OOO( ocRegex,         2,  4,  0,  "ORG.LIBREOFFICE.REGEX" ),
-    EXC_FUNCENTRY_OOO( ocFourier,       2,  5,  0,  "ORG.LIBREOFFICE.FOURIER" )
+    EXC_FUNCENTRY_OOO( ocFourier,       2,  5,  0,  "ORG.LIBREOFFICE.FOURIER" ),
+    EXC_FUNCENTRY_OOO( ocRandomNV,      0,  0,  0,  "ORG.LIBREOFFICE.RAND.NV" ),
+    EXC_FUNCENTRY_OOO( ocRandbetweenNV, 2,  2,  0,  "ORG.LIBREOFFICE.RANDBETWEEN.NV" )
 };
 
 #undef EXC_FUNCENTRY_OOO_IBR
diff --git a/sc/source/filter/oox/formulabase.cxx b/sc/source/filter/oox/formulabase.cxx
index b2c074a1d277..f29c922a26ba 100644
--- a/sc/source/filter/oox/formulabase.cxx
+++ b/sc/source/filter/oox/formulabase.cxx
@@ -915,7 +915,9 @@ static const FunctionData saFuncTableOOoLO[] =
     { "ORG.LIBREOFFICE.FORECAST.ETS.STAT.MULT", "ORG.LIBREOFFICE.FORECAST.ETS.STAT.MULT", NOID,   NOID,   3,  6,  V, { VR, VA, VR }, FuncFlags::MACROCALL_NEW },
     { "ORG.LIBREOFFICE.ROUNDSIG",   "ORG.LIBREOFFICE.ROUNDSIG", NOID, NOID,  2,  2,  V, { RX }, FuncFlags::MACROCALL_NEW },
     { "ORG.LIBREOFFICE.REGEX",      "ORG.LIBREOFFICE.REGEX", NOID, NOID,  2,  4,  V, { RX }, FuncFlags::MACROCALL_NEW },
-    { "ORG.LIBREOFFICE.FOURIER",    "ORG.LIBREOFFICE.FOURIER", NOID, NOID,  2,  5,  A, { RX }, FuncFlags::MACROCALL_NEW }
+    { "ORG.LIBREOFFICE.FOURIER",    "ORG.LIBREOFFICE.FOURIER", NOID, NOID,  2,  5,  A, { RX }, FuncFlags::MACROCALL_NEW },
+    { "ORG.LIBREOFFICE.RAND.NV",    "ORG.LIBREOFFICE.RAND.NV", NOID, NOID,  0,  0,  V, {}, FuncFlags::MACROCALL_NEW },
+    { "ORG.LIBREOFFICE.RANDBETWEEN.NV", "ORG.LIBREOFFICE.RANDBETWEEN.NV", NOID, NOID,  2,  2,  V, { VR }, FuncFlags::MACROCALL_NEW }
 
 };