tdf#74667 Regression dialog: linear, logarithmic, power

Add a new statistics dialog for calculating regression. First
supported regression models are linear, logarithmic and power.

Change-Id: I6fa18136455d4bc4d69edbaa7d19ee6b5b6e5703

9 files changed, 354 insertions, 7 deletions

diff --git a/sc/source/ui/StatisticsDialogs/RegressionDialog.cxx b/sc/source/ui/StatisticsDialogs/RegressionDialog.cxx
new file mode 100644
index 000000000000..b6a5b6dfb46b
--- /dev/null
+++ b/sc/source/ui/StatisticsDialogs/RegressionDialog.cxx
@@ -0,0 +1,232 @@
+/* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
+/*
+ * This file is part of the LibreOffice project.
+ *
+ * This Source Code Form is subject to the terms of the Mozilla Public
+ * License, v. 2.0. If a copy of the MPL was not distributed with this
+ * file, You can obtain one at http://mozilla.org/MPL/2.0/.
+ *
+ */
+
+#include <sfx2/dispatch.hxx>
+#include <svl/zforlist.hxx>
+#include <svl/undo.hxx>
+
+#include "formulacell.hxx"
+#include "rangelst.hxx"
+#include "scitems.hxx"
+#include "docsh.hxx"
+#include "document.hxx"
+#include "uiitems.hxx"
+#include "reffact.hxx"
+#include "strload.hxx"
+#include "docfunc.hxx"
+#include "StatisticsDialogs.hrc"
+#include "TableFillingAndNavigationTools.hxx"
+
+#include "RegressionDialog.hxx"
+
+namespace
+{
+    sal_Int16 constRegressionModel[] =
+    {
+        STR_LABEL_LINEAR,
+        STR_LABEL_LOGARITHMIC,
+        STR_LABEL_POWER
+    };
+
+    OUString constTemplateRSQUARED[] =
+    {
+        "=RSQ(%VARIABLE2_RANGE% ; %VARIABLE1_RANGE%)",
+        "=RSQ(%VARIABLE2_RANGE% ; LN(%VARIABLE1_RANGE%))",
+        "=RSQ(LN(%VARIABLE2_RANGE%) ; LN(%VARIABLE1_RANGE%))"
+    };
+
+    OUString constTemplatesSTDERR[] =
+    {
+        "=STEYX(%VARIABLE2_RANGE% ; %VARIABLE1_RANGE%)",
+        "=STEYX(%VARIABLE2_RANGE% ; LN(%VARIABLE1_RANGE%))",
+        "=STEYX(LN(%VARIABLE2_RANGE%) ; LN(%VARIABLE1_RANGE%))"
+    };
+
+    OUString constTemplatesSLOPE[] =
+    {
+        "=SLOPE(%VARIABLE2_RANGE% ; %VARIABLE1_RANGE%)",
+        "=SLOPE(%VARIABLE2_RANGE% ; LN(%VARIABLE1_RANGE%))",
+        "=EXP(INTERCEPT(LN(%VARIABLE2_RANGE%) ; LN(%VARIABLE1_RANGE%)))"
+    };
+
+    OUString constTemplatesINTERCEPT[] =
+    {
+        "=INTERCEPT(%VARIABLE2_RANGE% ; %VARIABLE1_RANGE%)",
+        "=INTERCEPT(%VARIABLE2_RANGE% ; LN(%VARIABLE1_RANGE%))",
+        "=SLOPE(LN(%VARIABLE2_RANGE%) ; LN(%VARIABLE1_RANGE%))"
+    };
+
+    OUString constRegressionFormula[] =
+    {
+        "=%A% * %ADDRESS% + %B%",
+        "=%A% * LN(%ADDRESS%) + %B%",
+        "=%A% * %ADDRESS% ^ %B%"
+    };
+
+} // end anonymous namespace
+
+ScRegressionDialog::ScRegressionDialog(
+                    SfxBindings* pSfxBindings, SfxChildWindow* pChildWindow,
+                    vcl::Window* pParent, ScViewData* pViewData ) :
+    ScStatisticsTwoVariableDialog(
+            pSfxBindings, pChildWindow, pParent, pViewData,
+            "RegressionDialog", "modules/scalc/ui/regressiondialog.ui" )
+{
+    get(mpLinearCheckBox, "linear-check");
+    get(mpLogarithmicCheckBox, "logarithmic-check");
+    get(mpPowerCheckBox, "power-check");
+}
+
+ScRegressionDialog::~ScRegressionDialog()
+{}
+
+bool ScRegressionDialog::Close()
+{
+    return DoClose(ScRegressionDialogWrapper::GetChildWindowId());
+}
+
+sal_Int16 ScRegressionDialog::GetUndoNameId()
+{
+    return STR_REGRESSION_UNDO_NAME;
+}
+
+ScRange ScRegressionDialog::ApplyOutput(ScDocShell* pDocShell)
+{
+    AddressWalkerWriter aOutput(mOutputAddress, pDocShell, mDocument,
+            formula::FormulaGrammar::mergeToGrammar( formula::FormulaGrammar::GRAM_ENGLISH, mAddressDetails.eConv));
+    FormulaTemplate aTemplate(mDocument);
+    aTemplate.autoReplaceUses3D(false);
+
+    std::unique_ptr<DataRangeIterator> pVariable1Iterator;
+    if (mGroupedBy == BY_COLUMN)
+        pVariable1Iterator.reset(new DataRangeByColumnIterator(mVariable1Range));
+    else
+        pVariable1Iterator.reset(new DataRangeByRowIterator(mVariable1Range));
+
+    std::unique_ptr<DataRangeIterator> pVariable2Iterator;
+    if (mGroupedBy == BY_COLUMN)
+        pVariable2Iterator.reset(new DataRangeByColumnIterator(mVariable2Range));
+    else
+        pVariable2Iterator.reset(new DataRangeByRowIterator(mVariable2Range));
+
+    aTemplate.autoReplaceRange("%VARIABLE1_RANGE%", pVariable1Iterator->get());
+    aTemplate.autoReplaceRange("%VARIABLE2_RANGE%", pVariable2Iterator->get());
+
+    aOutput.writeBoldString(SC_STRLOAD(RID_STATISTICS_DLGS, STR_REGRESSION));
+    aOutput.newLine();
+    aOutput.newLine();
+    aOutput.push();
+
+    // REGRESSION MODEL
+    aOutput.writeString(SC_STRLOAD(RID_STATISTICS_DLGS, STR_LABEL_REGRESSION_MODEL));
+    aOutput.nextRow();
+
+    // RSQUARED
+    aOutput.writeString(SC_STRLOAD(RID_STATISTICS_DLGS, STR_LABEL_RSQUARED));
+    aOutput.nextRow();
+
+    // Standard Error
+    aOutput.writeString(SC_STRLOAD(RID_STATISTICS_DLGS, STRID_CALC_STD_ERROR));
+    aOutput.nextRow();
+
+    aOutput.nextRow();
+
+    // Slope
+    aOutput.writeString(SC_STRLOAD(RID_STATISTICS_DLGS, STR_LABEL_SLOPE));
+    aOutput.nextRow();
+
+    // Intercept
+    aOutput.writeString(SC_STRLOAD(RID_STATISTICS_DLGS, STR_LABEL_INTERCEPT));
+    aOutput.nextRow();
+
+    aOutput.nextRow();
+
+    size_t nVariable1Size = pVariable1Iterator->size();
+
+    OUString sFormula;
+    if (mGroupedBy == BY_COLUMN)
+        sFormula = "=INDEX(%VARIABLE1_RANGE%; %VAR1_CELL_INDEX%; 1)";
+    else
+        sFormula = "=INDEX(%VARIABLE1_RANGE%; 1; %VAR1_CELL_INDEX%)";
+
+    for (size_t i = 0; i < nVariable1Size; i++)
+    {
+        aTemplate.setTemplate(sFormula);
+        aTemplate.applyNumber("%VAR1_CELL_INDEX%", i + 1);
+        aOutput.writeFormula(aTemplate.getTemplate());
+        aOutput.nextRow();
+    }
+
+    aOutput.reset();
+
+    bool aEnabledRegressionTypes[3];
+
+    aEnabledRegressionTypes[0] = mpLinearCheckBox->IsChecked();
+    aEnabledRegressionTypes[1] = mpLogarithmicCheckBox->IsChecked();
+    aEnabledRegressionTypes[2] = mpPowerCheckBox->IsChecked();
+
+    sal_Int16 nColumn = 0;
+
+    for (size_t nRegressionIndex = 0; nRegressionIndex < SAL_N_ELEMENTS(aEnabledRegressionTypes); ++nRegressionIndex)
+    {
+        if (!aEnabledRegressionTypes[nRegressionIndex])
+            continue;
+
+        aOutput.nextColumn();
+        nColumn += 1;
+
+        // REGRESSION MODEL
+        aOutput.writeString(SC_STRLOAD(RID_STATISTICS_DLGS, constRegressionModel[nRegressionIndex]));
+        aOutput.nextRow();
+
+        // RSQUARED
+        aTemplate.setTemplate(constTemplateRSQUARED[nRegressionIndex]);
+        aOutput.writeMatrixFormula(aTemplate.getTemplate());
+        aTemplate.autoReplaceAddress("%RSQUARED%", aOutput.current());
+        aOutput.nextRow();
+
+        // Standard Error
+        aTemplate.setTemplate(constTemplatesSTDERR[nRegressionIndex]);
+        aOutput.writeMatrixFormula(aTemplate.getTemplate());
+        aTemplate.autoReplaceAddress("%STD_ERROR%", aOutput.current());
+        aOutput.nextRow();
+
+        aOutput.nextRow();
+
+        // Slope
+        aTemplate.setTemplate(constTemplatesSLOPE[nRegressionIndex]);
+        aOutput.writeMatrixFormula(aTemplate.getTemplate());
+        aTemplate.autoReplaceAddress("%A%", aOutput.current());
+        aOutput.nextRow();
+
+        // Intercept
+        aTemplate.setTemplate(constTemplatesINTERCEPT[nRegressionIndex]);
+        aOutput.writeMatrixFormula(aTemplate.getTemplate());
+        aTemplate.autoReplaceAddress("%B%", aOutput.current());
+        aOutput.nextRow();
+
+        aOutput.nextRow();
+
+        for (size_t i = 0; i < nVariable1Size; i++)
+        {
+            aTemplate.setTemplate(constRegressionFormula[nRegressionIndex]);
+            aTemplate.applyAddress("%ADDRESS%", aOutput.current(-nColumn), false);
+            aOutput.writeFormula(aTemplate.getTemplate());
+
+            aOutput.nextRow();
+        }
+
+        aOutput.resetRow();
+    }
+
+    return ScRange(aOutput.mMinimumAddress, aOutput.mMaximumAddress);
+}
+
+/* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/sc/source/ui/StatisticsDialogs/StatisticsDialogs.hrc b/sc/source/ui/StatisticsDialogs/StatisticsDialogs.hrc
index 662dc6dfd416..f8cbcbbdc4fe 100644
--- a/sc/source/ui/StatisticsDialogs/StatisticsDialogs.hrc
+++ b/sc/source/ui/StatisticsDialogs/StatisticsDialogs.hrc
@@ -55,13 +55,15 @@
 #define STR_COVARIANCE_UNDO_NAME                    63
 #define STR_EXPONENTIAL_SMOOTHING_UNDO_NAME         64
 #define STR_MOVING_AVERAGE_UNDO_NAME                65
-#define STR_TTEST                                   66
-#define STR_TTEST_UNDO_NAME                         67
-#define STR_FTEST                                   68
-#define STR_FTEST_UNDO_NAME                         69
-#define STR_ZTEST                                   70
-#define STR_ZTEST_UNDO_NAME                         71
-#define STR_CHI_SQUARE_TEST                         72
+#define STR_REGRESSION                              66
+#define STR_REGRESSION_UNDO_NAME                    67
+#define STR_TTEST                                   68
+#define STR_TTEST_UNDO_NAME                         69
+#define STR_FTEST                                   70
+#define STR_FTEST_UNDO_NAME                         71
+#define STR_ZTEST                                   72
+#define STR_ZTEST_UNDO_NAME                         73
+#define STR_CHI_SQUARE_TEST                         74
 
 #define STR_COLUMN_LABEL_TEMPLATE                   100
 #define STR_ROW_LABEL_TEMPLATE                      101
@@ -93,6 +95,15 @@
 #define STR_CRITICAL_VALUE_LABEL                    150
 #define STR_TEST_STATISTIC_LABEL                    151
 
+#define STR_LABEL_LINEAR                            160
+#define STR_LABEL_LOGARITHMIC                       161
+#define STR_LABEL_POWER                             162
+
+#define STR_LABEL_REGRESSION_MODEL                  170
+#define STR_LABEL_RSQUARED                          171
+#define STR_LABEL_SLOPE                             172
+#define STR_LABEL_INTERCEPT                         173
+
 #define STR_FTEST_P_RIGHT_TAIL                      200
 #define STR_FTEST_F_CRITICAL_RIGHT_TAIL             201
 #define STR_FTEST_P_LEFT_TAIL                       202
diff --git a/sc/source/ui/StatisticsDialogs/StatisticsDialogs.src b/sc/source/ui/StatisticsDialogs/StatisticsDialogs.src
index 501e443aa761..d807e4d1e673 100644
--- a/sc/source/ui/StatisticsDialogs/StatisticsDialogs.src
+++ b/sc/source/ui/StatisticsDialogs/StatisticsDialogs.src
@@ -305,6 +305,14 @@ Resource RID_STATISTICS_DLGS
     {
         Text [ en-US ] = "Test of Independence (Chi-Square)";
     };
+    String STR_REGRESSION_UNDO_NAME
+    {
+        Text [ en-US ] = "Regression";
+    };
+    String STR_REGRESSION
+    {
+        Text [ en-US ] = "Regression";
+    };
 
     /* Common */
     String STR_COLUMN_LABEL_TEMPLATE
@@ -356,6 +364,38 @@ Resource RID_STATISTICS_DLGS
         Text [ en-US ] = "Test Statistic";
     };
 
+    /* RegressionDialog */
+
+    String STR_LABEL_LINEAR
+    {
+        Text [ en-US ] = "Linear";
+    };
+    String STR_LABEL_LOGARITHMIC
+    {
+        Text [ en-US ] = "Logarithmic";
+    };
+    String STR_LABEL_POWER
+    {
+        Text [ en-US ] = "Power";
+    };
+
+    String STR_LABEL_REGRESSION_MODEL
+    {
+        Text [ en-US ] = "Regression Model";
+    };
+    String STR_LABEL_RSQUARED
+    {
+        Text [ en-US ] = "R^2";
+    };
+    String STR_LABEL_SLOPE
+    {
+        Text [ en-US ] = "Slope";
+    };
+    String STR_LABEL_INTERCEPT
+    {
+        Text [ en-US ] = "Intercept";
+    };
+
     /*F Test */
     String STR_FTEST_P_RIGHT_TAIL
     {
diff --git a/sc/source/ui/app/scdll.cxx b/sc/source/ui/app/scdll.cxx
index c9761f5c9527..56cfc4527814 100644
--- a/sc/source/ui/app/scdll.cxx
+++ b/sc/source/ui/app/scdll.cxx
@@ -243,6 +243,7 @@ void ScDLL::Init()
     ScCovarianceDialogWrapper           ::RegisterChildWindow(false, pMod);
     ScExponentialSmoothingDialogWrapper ::RegisterChildWindow(false, pMod);
     ScMovingAverageDialogWrapper        ::RegisterChildWindow(false, pMod);
+    ScRegressionDialogWrapper           ::RegisterChildWindow(false, pMod);
     ScTTestDialogWrapper                ::RegisterChildWindow(false, pMod);
     ScFTestDialogWrapper                ::RegisterChildWindow(false, pMod);
     ScZTestDialogWrapper                ::RegisterChildWindow(false, pMod);
diff --git a/sc/source/ui/inc/RegressionDialog.hxx b/sc/source/ui/inc/RegressionDialog.hxx
new file mode 100644
index 000000000000..a4c7089382e0
--- /dev/null
+++ b/sc/source/ui/inc/RegressionDialog.hxx
@@ -0,0 +1,39 @@
+/* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
+/*
+ * This file is part of the LibreOffice project.
+ *
+ * This Source Code Form is subject to the terms of the Mozilla Public
+ * License, v. 2.0. If a copy of the MPL was not distributed with this
+ * file, You can obtain one at http://mozilla.org/MPL/2.0/.
+ *
+ */
+
+#ifndef INCLUDED_SC_SOURCE_UI_INC_REGRESSIONDIALOG_HXX
+#define INCLUDED_SC_SOURCE_UI_INC_REGRESSIONDIALOG_HXX
+
+#include "StatisticsTwoVariableDialog.hxx"
+
+class ScRegressionDialog : public ScStatisticsTwoVariableDialog
+{
+    VclPtr<CheckBox> mpLinearCheckBox;
+    VclPtr<CheckBox> mpLogarithmicCheckBox;
+    VclPtr<CheckBox> mpPowerCheckBox;
+
+public:
+    ScRegressionDialog(
+        SfxBindings* pB, SfxChildWindow* pCW,
+        vcl::Window* pParent, ScViewData* pViewData );
+
+    virtual ~ScRegressionDialog();
+
+    virtual bool Close() override;
+
+protected:
+    virtual sal_Int16 GetUndoNameId() override;
+    virtual ScRange ApplyOutput(ScDocShell* pDocShell) override;
+};
+
+
+#endif
+
+/* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/sc/source/ui/inc/reffact.hxx b/sc/source/ui/inc/reffact.hxx
index 4c6e7339429b..142decf94174 100644
--- a/sc/source/ui/inc/reffact.hxx
+++ b/sc/source/ui/inc/reffact.hxx
@@ -108,6 +108,13 @@ private:
     ScMovingAverageDialogWrapper() = delete;
 };
 
+class ScRegressionDialogWrapper :
+    public ChildWindowWrapper<SID_REGRESSION_DIALOG>
+{
+private:
+    ScRegressionDialogWrapper() = delete;
+};
+
 class ScTTestDialogWrapper :
     public ChildWindowWrapper<SID_TTEST_DIALOG>
 {
diff --git a/sc/source/ui/view/cellsh1.cxx b/sc/source/ui/view/cellsh1.cxx
index 4d5d912b7afb..27eb61498720 100644
--- a/sc/source/ui/view/cellsh1.cxx
+++ b/sc/source/ui/view/cellsh1.cxx
@@ -985,6 +985,15 @@ void ScCellShell::ExecuteEdit( SfxRequest& rReq )
                 pScMod->SetRefDialog( nId, pWnd == nullptr );
             }
             break;
+        case SID_REGRESSION_DIALOG:
+            {
+                sal_uInt16 nId  = ScRegressionDialogWrapper::GetChildWindowId();
+                SfxViewFrame* pViewFrm = pTabViewShell->GetViewFrame();
+                SfxChildWindow* pWnd = pViewFrm->GetChildWindow( nId );
+
+                pScMod->SetRefDialog( nId, pWnd == nullptr );
+            }
+            break;
         case SID_TTEST_DIALOG:
             {
                 sal_uInt16 nId  = ScTTestDialogWrapper::GetChildWindowId();
diff --git a/sc/source/ui/view/tabvwsh.cxx b/sc/source/ui/view/tabvwsh.cxx
index a765612b2448..f9c6f2048ccd 100644
--- a/sc/source/ui/view/tabvwsh.cxx
+++ b/sc/source/ui/view/tabvwsh.cxx
@@ -96,6 +96,7 @@ void ScTabViewShell::InitInterface_Impl()
     GetStaticInterface()->RegisterChildWindow(ScCovarianceDialogWrapper::GetChildWindowId());
     GetStaticInterface()->RegisterChildWindow(ScExponentialSmoothingDialogWrapper::GetChildWindowId());
     GetStaticInterface()->RegisterChildWindow(ScMovingAverageDialogWrapper::GetChildWindowId());
+    GetStaticInterface()->RegisterChildWindow(ScRegressionDialogWrapper::GetChildWindowId());
     GetStaticInterface()->RegisterChildWindow(ScTTestDialogWrapper::GetChildWindowId());
     GetStaticInterface()->RegisterChildWindow(ScFTestDialogWrapper::GetChildWindowId());
     GetStaticInterface()->RegisterChildWindow(ScZTestDialogWrapper::GetChildWindowId());
diff --git a/sc/source/ui/view/tabvwshc.cxx b/sc/source/ui/view/tabvwshc.cxx
index de6eddcdcad9..67a73b90b428 100644
--- a/sc/source/ui/view/tabvwshc.cxx
+++ b/sc/source/ui/view/tabvwshc.cxx
@@ -66,6 +66,7 @@
 #include "CovarianceDialog.hxx"
 #include "ExponentialSmoothingDialog.hxx"
 #include "MovingAverageDialog.hxx"
+#include "RegressionDialog.hxx"
 #include "TTestDialog.hxx"
 #include "FTestDialog.hxx"
 #include "ZTestDialog.hxx"
@@ -371,6 +372,12 @@ VclPtr<SfxModelessDialog> ScTabViewShell::CreateRefDialog(
         }
         break;
 
+        case SID_REGRESSION_DIALOG:
+        {
+            pResult = VclPtr<ScRegressionDialog>::Create( pB, pCW, pParent, &GetViewData() );
+        }
+        break;
+
         case SID_TTEST_DIALOG:
         {
             pResult = VclPtr<ScTTestDialog>::Create( pB, pCW, pParent, &GetViewData() );