tdf#96370 rework filtering to be aware of WHERE vs HAVING clause

Several bugs (AFAIK not filed into tdf bugzilla) fixed.

Remaining problems:

When some filter clauses go into WHERE and others in HAVING,
they are always logically ANDed (it cannot be any other way),
but that is not communicated to the user in the UI.

Some things left undone:

* DatabaseDataProvider (and its users?) needs to be updated
to be HAVING-aware, too.

* Form-based filter (.uno:FormFilter) not HAVING-aware
it reads the current filter in function
svxform::FormController::setFilter
in
svx/source/form/formcontrollers.cxx
That's one place that needs to be updated.
The other place is the one that applies the filter.

Change-Id: I0e9d30a1927b6739a16ae7627e8d0dae8823b376

3 files changed, 166 insertions, 14 deletions

diff --git a/connectivity/source/commontools/dbtools2.cxx b/connectivity/source/commontools/dbtools2.cxx
index 3db5b431bd35..43f4aaef2774 100644
--- a/connectivity/source/commontools/dbtools2.cxx
+++ b/connectivity/source/commontools/dbtools2.cxx
@@ -52,6 +52,7 @@ namespace dbtools
 
     using namespace ::com::sun::star::uno;
     using namespace ::com::sun::star::beans;
+    using namespace ::com::sun::star::sdb;
     using namespace ::com::sun::star::sdbc;
     using namespace ::com::sun::star::sdbcx;
     using namespace ::com::sun::star::lang;
@@ -1007,6 +1008,40 @@ OUString getDefaultReportEngineServiceName(const Reference< XComponentContext >&
     return OUString();
 }
 
+bool isAggregateColumn(const Reference< XSingleSelectQueryComposer > &_xParser, const Reference< XPropertySet > &_xField, bool whenNotFound)
+{
+    OUString sName;
+    _xField->getPropertyValue("Name") >>= sName;
+    Reference< XColumnsSupplier > xColumnsSupplier(_xParser, UNO_QUERY);
+    Reference< css::container::XNameAccess >  xCols;
+    if (xColumnsSupplier.is())
+        xCols = xColumnsSupplier->getColumns();
+
+    return isAggregateColumn(xCols, sName, whenNotFound);
+}
+
+bool isAggregateColumn(const Reference< XNameAccess > &_xColumns, const OUString &_sName, bool whenNotFound)
+{
+    if ( _xColumns.is() && _xColumns->hasByName(_sName) )
+    {
+        Reference<XPropertySet> xProp(_xColumns->getByName(_sName),UNO_QUERY);
+        assert(xProp.is());
+        return isAggregateColumn( xProp );
+    }
+    return  whenNotFound;
+}
+
+bool isAggregateColumn( const Reference< XPropertySet > &_xColumn )
+{
+    bool bAgg(false);
+
+    static const char sAgg[] = "AggregateFunction";
+    if ( _xColumn->getPropertySetInfo()->hasPropertyByName(sAgg) )
+        _xColumn->getPropertyValue(sAgg) >>= bAgg;
+
+    return bAgg;
+}
+
 
 }   // namespace dbtools
 
diff --git a/connectivity/source/commontools/filtermanager.cxx b/connectivity/source/commontools/filtermanager.cxx
index ba2d2df20878..5894872625fa 100644
--- a/connectivity/source/commontools/filtermanager.cxx
+++ b/connectivity/source/commontools/filtermanager.cxx
@@ -62,20 +62,64 @@ namespace dbtools
 
     const OUString& FilterManager::getFilterComponent( FilterComponent _eWhich ) const
     {
-        return _eWhich == FilterComponent::PublicFilter ? m_aPublicFilterComponent : m_aLinkFilterComponent;
+        switch (_eWhich)
+        {
+        case FilterComponent::PublicFilter:
+            return m_aPublicFilterComponent;
+        case FilterComponent::PublicHaving:
+            return m_aPublicHavingComponent;
+        case FilterComponent::LinkFilter:
+            return m_aLinkFilterComponent;
+        case FilterComponent::LinkHaving:
+            return m_aLinkHavingComponent;
+        }
+        assert(false);
+
+        static OUString sErr("#FilterManager::getFilterComponent unknown component#");
+        return sErr;
     }
 
 
     void FilterManager::setFilterComponent( FilterComponent _eWhich, const OUString& _rComponent )
     {
-        if (_eWhich == FilterComponent::PublicFilter)
+        switch (_eWhich)
+        {
+        case FilterComponent::PublicFilter:
             m_aPublicFilterComponent = _rComponent;
-        else
+            break;
+        case FilterComponent::PublicHaving:
+            m_aPublicHavingComponent = _rComponent;
+            break;
+        case FilterComponent::LinkFilter:
             m_aLinkFilterComponent = _rComponent;
+            break;
+        case FilterComponent::LinkHaving:
+            m_aLinkHavingComponent = _rComponent;
+            break;
+        }
         try
         {
-            if ( m_xComponentAggregate.is() && (( _eWhich != FilterComponent::PublicFilter ) || m_bApplyPublicFilter ) )
-                m_xComponentAggregate->setPropertyValue( OMetaConnection::getPropMap().getNameByIndex(PROPERTY_ID_FILTER), makeAny( getComposedFilter() ) );
+            if ( m_xComponentAggregate.is() )
+            {
+                bool propagate(true);
+                switch (_eWhich)
+                {
+                case FilterComponent::PublicFilter:
+                    propagate = propagate && m_bApplyPublicFilter;
+                    SAL_FALLTHROUGH;
+                case FilterComponent::LinkFilter:
+                    if (propagate)
+                        m_xComponentAggregate->setPropertyValue( OMetaConnection::getPropMap().getNameByIndex(PROPERTY_ID_FILTER), makeAny( getComposedFilter() ) );
+                    break;
+                case FilterComponent::PublicHaving:
+                    propagate = propagate && m_bApplyPublicFilter;
+                    SAL_FALLTHROUGH;
+                case FilterComponent::LinkHaving:
+                    if (propagate)
+                        m_xComponentAggregate->setPropertyValue( OMetaConnection::getPropMap().getNameByIndex(PROPERTY_ID_HAVINGCLAUSE), makeAny( getComposedHaving() ) );
+                    break;
+                }
+            }
         }
         catch( const Exception& )
         {
@@ -93,9 +137,13 @@ namespace dbtools
 
         try
         {
-            if ( m_xComponentAggregate.is() && !getFilterComponent( FilterComponent::PublicFilter ).isEmpty() )
-            {   // only if there changed something
-                m_xComponentAggregate->setPropertyValue( OMetaConnection::getPropMap().getNameByIndex(PROPERTY_ID_FILTER), makeAny( getComposedFilter() ) );
+            if ( m_xComponentAggregate.is())
+            {
+                // only where/if something changed
+                if (!getFilterComponent( FilterComponent::PublicFilter ).isEmpty())
+                    m_xComponentAggregate->setPropertyValue( OMetaConnection::getPropMap().getNameByIndex(PROPERTY_ID_FILTER), makeAny( getComposedFilter() ) );
+                if (!getFilterComponent( FilterComponent::PublicHaving ).isEmpty())
+                    m_xComponentAggregate->setPropertyValue( OMetaConnection::getPropMap().getNameByIndex(PROPERTY_ID_HAVINGCLAUSE), makeAny( getComposedHaving() ) );
             }
         }
         catch( const Exception& )
@@ -120,7 +168,7 @@ namespace dbtools
     }
 
 
-    bool FilterManager::isThereAtMostOneComponent( OUString& o_singleComponent ) const
+    bool FilterManager::isThereAtMostOneFilterComponent( OUString& o_singleComponent ) const
     {
         if (m_bApplyPublicFilter) {
             if (!m_aPublicFilterComponent.isEmpty() && !m_aLinkFilterComponent.isEmpty())
@@ -143,12 +191,35 @@ namespace dbtools
         }
     }
 
+    bool FilterManager::isThereAtMostOneHavingComponent( OUString& o_singleComponent ) const
+    {
+        if (m_bApplyPublicFilter) {
+            if (!m_aPublicHavingComponent.isEmpty() && !m_aLinkHavingComponent.isEmpty())
+                return false;
+            if (!m_aPublicHavingComponent.isEmpty())
+                o_singleComponent = m_aPublicHavingComponent;
+            else if (!m_aLinkHavingComponent.isEmpty())
+                o_singleComponent = m_aLinkHavingComponent;
+            else
+                o_singleComponent.clear();
+            return true;
+        }
+        else
+        {
+            if (m_aLinkHavingComponent.isEmpty())
+                o_singleComponent.clear();
+            else
+                o_singleComponent = m_aLinkHavingComponent;
+            return true;
+        }
+    }
+
 
     OUString FilterManager::getComposedFilter( ) const
     {
         // if we have only one non-empty component, then there's no need to compose anything
         OUString singleComponent;
-        if ( isThereAtMostOneComponent( singleComponent ) )
+        if ( isThereAtMostOneFilterComponent( singleComponent ) )
         {
             return singleComponent;
         }
@@ -161,6 +232,23 @@ namespace dbtools
     }
 
 
+    OUString FilterManager::getComposedHaving( ) const
+    {
+        // if we have only one non-empty component, then there's no need to compose anything
+        OUString singleComponent;
+        if ( isThereAtMostOneHavingComponent( singleComponent ) )
+        {
+            return singleComponent;
+        }
+        // append the single components
+        OUStringBuffer aComposedFilter(singleComponent);
+        if (m_bApplyPublicFilter)
+            appendFilterComponent( aComposedFilter, m_aPublicHavingComponent );
+        appendFilterComponent( aComposedFilter, m_aLinkHavingComponent );
+        return aComposedFilter.makeStringAndClear();
+    }
+
+
 }   // namespace dbtools
 
 
diff --git a/connectivity/source/commontools/parameters.cxx b/connectivity/source/commontools/parameters.cxx
index 37f6a6bbd2f6..30311ba22aa4 100644
--- a/connectivity/source/commontools/parameters.cxx
+++ b/connectivity/source/commontools/parameters.cxx
@@ -245,7 +245,9 @@ namespace dbtools
 
 
     void ParameterManager::classifyLinks( const Reference< XNameAccess >& _rxParentColumns,
-        const Reference< XNameAccess >& _rxColumns, std::vector< OUString >& _out_rAdditionalFilterComponents )
+        const Reference< XNameAccess >& _rxColumns,
+        std::vector< OUString >& _out_rAdditionalFilterComponents,
+        std::vector< OUString >& _out_rAdditionalHavingComponents )
     {
         OSL_PRECOND( m_aMasterFields.size() == m_aDetailFields.size(),
             "ParameterManager::classifyLinks: master and detail fields should have the same length!" );
@@ -309,7 +311,10 @@ namespace dbtools
                     aInsertionPos.first->second.eType = ParameterClassification::LinkedByColumnName;
 
                     // remember the filter component
-                    _out_rAdditionalFilterComponents.push_back( sFilterCondition );
+                    if (isAggregateColumn(xDetailField))
+                        _out_rAdditionalHavingComponents.push_back( sFilterCondition );
+                    else
+                        _out_rAdditionalFilterComponents.push_back( sFilterCondition );
 
                     // remember the new "detail field" for this link
                     aStrippedDetailFields.push_back( sNewParamName );
@@ -381,7 +386,8 @@ namespace dbtools
 
             // classify the links - depending on what the detail fields in each link pair denotes
             std::vector< OUString > aAdditionalFilterComponents;
-            classifyLinks( xParentColumns, xColumns, aAdditionalFilterComponents );
+            std::vector< OUString > aAdditionalHavingComponents;
+            classifyLinks( xParentColumns, xColumns, aAdditionalFilterComponents, aAdditionalHavingComponents );
 
             // did we find links where the detail field refers to a detail column (instead of a parameter name)?
             if ( !aAdditionalFilterComponents.empty() )
@@ -401,11 +407,34 @@ namespace dbtools
                     sAdditionalFilter.append(" )");
                 }
 
-                // now set this filter at the 's filter manager
+                // now set this filter at the filter manager
                 _rFilterManager.setFilterComponent( FilterManager::FilterComponent::LinkFilter, sAdditionalFilter.makeStringAndClear() );
 
                 _rColumnsInLinkDetails = true;
             }
+
+            if ( !aAdditionalHavingComponents.empty() )
+            {
+                // build a conjunction of all the filter components
+                OUStringBuffer sAdditionalHaving;
+                for (   std::vector< OUString >::const_iterator aComponent = aAdditionalHavingComponents.begin();
+                        aComponent != aAdditionalHavingComponents.end();
+                        ++aComponent
+                    )
+                {
+                    if ( !sAdditionalHaving.isEmpty() )
+                        sAdditionalHaving.append(" AND ");
+
+                    sAdditionalHaving.append("( ");
+                    sAdditionalHaving.append(*aComponent);
+                    sAdditionalHaving.append(" )");
+                }
+
+                // now set this having clause at the filter manager
+                _rFilterManager.setFilterComponent( FilterManager::FilterComponent::LinkHaving, sAdditionalHaving.makeStringAndClear() );
+
+                _rColumnsInLinkDetails = true;
+            }
         }
         catch( const Exception& )
         {