tdf#142910 sc filter: fix "greater than" or "smaller than" etc

Filter "greater than" or "smaller than" (>, <, >=, <=)
conditions according to the cell number format.

Regression from commit: d5c2584bf36d21580db677b231c57f99f49aa2cb
(Related: tdf#140968 avoid duplicated filter values)

Follow-up to commit: 1f755525189884e4b2824889a6b9dea8933402db
(tdf#142402 sc UI: store formatted values in standard filter)

Clean-up for commit: d5c2584bf36d21580db677b231c57f99f49aa2cb
(Related: tdf#140968 avoid duplicated filter values)

Change-Id: I1284892398c9964ca5407b4d617a617f20341107
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/118272
Tested-by: Jenkins
Tested-by: László Németh <nemeth@numbertext.org>
Reviewed-by: László Németh <nemeth@numbertext.org>

4 files changed, 32 insertions, 48 deletions

diff --git a/sc/source/core/data/column3.cxx b/sc/source/core/data/column3.cxx
index 73a2ed613548..8faa97d2a050 100644
--- a/sc/source/core/data/column3.cxx
+++ b/sc/source/core/data/column3.cxx
@@ -2499,10 +2499,11 @@ class FilterEntriesHandler
             sal_uInt32 nIndex = pFormatter->GetFormatIndex(NF_DATETIME_ISO_YYYYMMDD_HHMMSS);
             pFormatter->GetInputLineString(fVal, nIndex, aStr);
         }
-        /* use string compare later for formatted and filtered cell values
-        to avoid duplicates in the filter lists with setting the mbIsFormatted */
-        bool bFormFiltVal = mrColumn.HasFiltering() && nFormat;
-        mrFilterEntries.push_back(ScTypedStrData(aStr, fVal, ScTypedStrData::Value, bDate, bFormFiltVal));
+        // store the formatted/rounded value for filtering
+        if (nFormat && !bDate)
+            mrFilterEntries.push_back(ScTypedStrData(aStr, fVal, rColumn.GetDoc().RoundValueAsShown(fVal, nFormat), ScTypedStrData::Value, bDate));
+        else
+            mrFilterEntries.push_back(ScTypedStrData(aStr, fVal, fVal, ScTypedStrData::Value, bDate));
     }
 
 public:
diff --git a/sc/source/core/data/table3.cxx b/sc/source/core/data/table3.cxx
index f8e9aada25b7..48c05bcaa40d 100644
--- a/sc/source/core/data/table3.cxx
+++ b/sc/source/core/data/table3.cxx
@@ -2339,12 +2339,16 @@ public:
         bool bOk = false;
         bool bTestEqual = false;
         double nCellVal;
+        double fRoundedValue = rItem.mfVal;
+        sal_uInt32 nNumFmt = pContext ? mrTab.GetNumberFormat(*pContext, ScAddress(nCol, nRow, mrTab.GetTab())) :
+            mrTab.GetNumberFormat(nCol, nRow);
+
         if (!rCell.isEmpty())
         {
             switch (rCell.meType)
             {
                 case CELLTYPE_VALUE :
-                    nCellVal = rCell.mfValue;
+                    nCellVal = mrDoc.RoundValueAsShown(rCell.mfValue, nNumFmt, pContext);
                 break;
                 case CELLTYPE_FORMULA :
                     nCellVal = rCell.mpFormula->GetValue();
@@ -2352,7 +2356,6 @@ public:
                 default:
                     nCellVal = 0.0;
             }
-
         }
         else
             nCellVal = mrTab.GetValue(nCol, nRow);
@@ -2363,10 +2366,9 @@ public:
          * the same, in other words only if rEntry.nVal is an integer value
          * rEntry.bQueryByDate should be true and the time fraction be
          * stripped here. */
+
         if (rItem.meType == ScQueryEntry::ByDate)
         {
-            sal_uInt32 nNumFmt = pContext ? mrTab.GetNumberFormat(*pContext, ScAddress(nCol, nRow, mrTab.GetTab())) :
-                mrTab.GetNumberFormat(nCol, nRow);
             SvNumberFormatter* pFormatter = pContext ? pContext->GetFormatTable() : mrDoc.GetFormatTable();
             const SvNumberformat* pEntry = pFormatter->GetEntry(nNumFmt);
             if (pEntry)
@@ -2386,30 +2388,32 @@ public:
                 }
             }
         }
+        else if (nNumFmt)
+            fRoundedValue = mrDoc.RoundValueAsShown(rItem.mfVal, nNumFmt, pContext);
 
         switch (rEntry.eOp)
         {
             case SC_EQUAL :
-                bOk = ::rtl::math::approxEqual(nCellVal, rItem.mfVal);
+                bOk = ::rtl::math::approxEqual(nCellVal, fRoundedValue);
                 break;
             case SC_LESS :
-                bOk = (nCellVal < rItem.mfVal) && !::rtl::math::approxEqual(nCellVal, rItem.mfVal);
+                bOk = (nCellVal < fRoundedValue) && !::rtl::math::approxEqual(nCellVal, fRoundedValue);
                 break;
             case SC_GREATER :
-                bOk = (nCellVal > rItem.mfVal) && !::rtl::math::approxEqual(nCellVal, rItem.mfVal);
+                bOk = (nCellVal > fRoundedValue) && !::rtl::math::approxEqual(nCellVal, fRoundedValue);
                 break;
             case SC_LESS_EQUAL :
-                bOk = (nCellVal < rItem.mfVal) || ::rtl::math::approxEqual(nCellVal, rItem.mfVal);
+                bOk = (nCellVal < fRoundedValue) || ::rtl::math::approxEqual(nCellVal, fRoundedValue);
                 if ( bOk && mpTestEqualCondition )
-                    bTestEqual = ::rtl::math::approxEqual(nCellVal, rItem.mfVal);
+                    bTestEqual = ::rtl::math::approxEqual(nCellVal, fRoundedValue);
                 break;
             case SC_GREATER_EQUAL :
-                bOk = (nCellVal > rItem.mfVal) || ::rtl::math::approxEqual( nCellVal, rItem.mfVal);
+                bOk = (nCellVal > fRoundedValue) || ::rtl::math::approxEqual( nCellVal, fRoundedValue);
                 if ( bOk && mpTestEqualCondition )
-                    bTestEqual = ::rtl::math::approxEqual(nCellVal, rItem.mfVal);
+                    bTestEqual = ::rtl::math::approxEqual(nCellVal, fRoundedValue);
                 break;
             case SC_NOT_EQUAL :
-                bOk = !::rtl::math::approxEqual(nCellVal, rItem.mfVal);
+                bOk = !::rtl::math::approxEqual(nCellVal, fRoundedValue);
                 break;
             default:
             {
@@ -3055,10 +3059,6 @@ public:
     {
         if (rItem.meType != ScQueryEntry::ByString && rItem.meType != ScQueryEntry::ByDate)
             return;
-        // return only if the type is ByString and the values are formatted, in other cases
-        // we have to optimize the filter in CanOptimizeQueryStringToNumber().
-        if (rItem.mbFormattedValue && rItem.meType == ScQueryEntry::ByString)
-            return;
 
         sal_uInt32 nIndex = 0;
         bool bNumber = mrDoc.GetFormatTable()->
diff --git a/sc/source/core/tool/queryentry.cxx b/sc/source/core/tool/queryentry.cxx
index 5853a9760c1d..121c257e0cf6 100644
--- a/sc/source/core/tool/queryentry.cxx
+++ b/sc/source/core/tool/queryentry.cxx
@@ -34,7 +34,7 @@
 
 bool ScQueryEntry::Item::operator== (const Item& r) const
 {
-    return meType == r.meType && mfVal == r.mfVal && maString == r.maString && mbMatchEmpty == r.mbMatchEmpty && mbFormattedValue == r.mbFormattedValue;
+    return meType == r.meType && mfVal == r.mfVal && maString == r.maString && mbMatchEmpty == r.mbMatchEmpty;
 }
 
 ScQueryEntry::ScQueryEntry() :
diff --git a/sc/source/core/tool/typedstrdata.cxx b/sc/source/core/tool/typedstrdata.cxx
index 0420b359ab76..59831dac7271 100644
--- a/sc/source/core/tool/typedstrdata.cxx
+++ b/sc/source/core/tool/typedstrdata.cxx
@@ -47,22 +47,14 @@ bool ScTypedStrData::EqualCaseSensitive::operator() (const ScTypedStrData& left,
     if (left.meStrType != right.meStrType)
         return false;
 
-    if (left.meStrType == Value && left.mfValue != right.mfValue &&
-        !left.mbIsFormatted)
+    if (left.meStrType == Value && left.mfRoundedValue != right.mfRoundedValue)
         return false;
 
     if (left.mbIsDate != right.mbIsDate )
         return false;
 
-    if (ScGlobal::GetCaseCollator()->compareString(
-        left.maStrValue, right.maStrValue) == 0)
-    {
-        // hack: it's possible, because we only compare values of the same filter range
-        const_cast<bool&>(left.mbIsDuplicated) = true;
-        return true;
-    }
-    else
-        return false;
+    return ScGlobal::GetCaseCollator()->compareString(
+        left.maStrValue, right.maStrValue) == 0;
 }
 
 bool ScTypedStrData::EqualCaseInsensitive::operator() (const ScTypedStrData& left, const ScTypedStrData& right) const
@@ -70,22 +62,14 @@ bool ScTypedStrData::EqualCaseInsensitive::operator() (const ScTypedStrData& lef
     if (left.meStrType != right.meStrType)
         return false;
 
-    if (left.meStrType == Value && left.mfValue != right.mfValue &&
-        !left.mbIsFormatted)
+    if (left.meStrType == Value && left.mfRoundedValue != right.mfRoundedValue)
         return false;
 
     if (left.mbIsDate != right.mbIsDate )
         return false;
 
-    if (ScGlobal::GetCollator()->compareString(
-        left.maStrValue, right.maStrValue) == 0)
-    {
-        // hack: it's possible, because we only compare values of the same filter range
-        const_cast<bool&>(left.mbIsDuplicated) = true;
-        return true;
-    }
-    else
-        return false;
+    return ScGlobal::GetCollator()->compareString(
+        left.maStrValue, right.maStrValue) == 0;
 }
 
 bool ScTypedStrData::operator< (const ScTypedStrData& r) const
@@ -95,13 +79,12 @@ bool ScTypedStrData::operator< (const ScTypedStrData& r) const
 }
 
 ScTypedStrData::ScTypedStrData(
-    const OUString& rStr, double nVal, StringType nType, bool bDate, bool bFormatted, bool bDuplicated ) :
+    const OUString& rStr, double fVal, double fRVal, StringType nType, bool bDate ) :
     maStrValue(rStr),
-    mfValue(nVal),
+    mfValue(fVal),
+    mfRoundedValue(fRVal),
     meStrType(nType),
-    mbIsDate( bDate ),
-    mbIsFormatted( bFormatted ),
-    mbIsDuplicated( bDuplicated ) {}
+    mbIsDate( bDate ) {}
 
 FindTypedStrData::FindTypedStrData(const ScTypedStrData& rVal, bool bCaseSens) :
     maVal(rVal), mbCaseSens(bCaseSens) {}