Fixing #1608, second issue: QueryByExample-Datediff does not work properly.

This solution is not the best. I will rethink it as I have enough time. But it should
work..

2 files changed, 37 insertions, 9 deletions

diff --git a/libopie2/opiepim/ChangeLog b/libopie2/opiepim/ChangeLog
index 84517fb..acb6cb1 100644
--- a/libopie2/opiepim/ChangeLog
+++ b/libopie2/opiepim/ChangeLog
@@ -1,17 +1,19 @@
-2005-03.19 Stefan Eilers <stefan@eilers-online.net>
+2005-03-20 Stefan Eilers <stefan@eilers-online.net>
+	* #1608 Quickfix for problem with DateDiff on SQL backend. I have to rethink this solution, but due to the short time, this should work.  
+2005-03-19 Stefan Eilers <stefan@eilers-online.net>
 	* Minor update for sorted(). Now ignoring any category search if "DoNotShowWithCategory" filter is activated.
 	* Fixing uninitialized member variable, caused crash of backend
 2005-03-18 Stefan Eilers <stefan@eilers-online.net>
 	* Rewrote generic sorted filter and added filter for "DoNotShowWithCategory", needed by addressbook (other filters need to be added!)
 2005-01-16 Stefan Eilers <stefan@eilers-online.net>
 	* Added new OPimEventSortVector class, improved OPimSortVector
 	* OPimAccessBackend now supports generic sorting.
 2005-01-03 Stefan Eilers <stefan@eilers-online.net>
 	* Fixing bug in API documentation
 	* Moving hasQuerySettings() and querySettings() to OPimAccessTemplate to be available for all frontends
 2004-12-28  Stefan Eilers <stefan@eilers-online.net>
 	* Make improved query by example accessable via frontend
 	* Some API documentation improvement
 	* Cleanup of backend api..
 	* Fixing bug #1501
 2004-11-23  Stefan Eilers <stefan@eilers-online.net>
diff --git a/libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp b/libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp
index 2368865..50421e2 100644
--- a/libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp
+++ b/libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp
@@ -535,32 +535,33 @@ UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist,
 {
     QString qu = "SELECT uid FROM addressbook WHERE";
 
     // Just add uid's selection if we really try to search in a subset of all uids! Otherwise this would
     // just take time and memory!
     if ( uidlist.count() != m_uids.count() ) {
 	    qu += " (";
 
 	    for ( uint i = 0; i < uidlist.count(); i++ ) {
 		    qu += " uid = " + QString::number( uidlist[i] ) + " OR";
 	    }
 	    qu.remove( qu.length()-2, 2 ); // Hmmmm..
 	    qu += " ) AND ";
     }
 
     QString searchQuery ="";
+    QString temp_searchQuery = "";
 
     QDate startDate;
 
     if ( qd.isValid() )
         startDate = qd.date();
     else
         startDate = QDate::currentDate();
 
 
     QMap<int, QString> queryFields = query.toMap();
     QStringList fieldList = OPimContactFields::untrfields( false );
     QMap<QString, int> translate = OPimContactFields::untrFieldsToId();
 
     // Convert every filled field to a SQL-Query
 //  bool isAnyFieldSelected = false;
     for ( QStringList::Iterator it = ++fieldList.begin(); it != fieldList.end(); ++it ){
@@ -572,46 +573,55 @@ UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist,
         if ( !queryStr.isEmpty() ){
             // If something is alredy stored in the query, add an "AND"
             // to the end of the string to prepare for the next ..
             if ( !searchQuery.isEmpty() )
                 searchQuery += " AND";
 
 //          isAnyFieldSelected = true;
             switch( id ){
             case Qtopia::Birthday:
                 endDate = new QDate( query.birthday() );
                 // Fall through !
             case Qtopia::Anniversary:
                 if ( endDate == 0l )
                     endDate = new QDate( query.anniversary() );
 
                 if ( settings & OPimContactAccess::DateDiff ) {
-                    searchQuery += QString( " (\"%1\" <= '%2-%3-%4\' AND \"%5\" >= '%6-%7-%8')" )
+		    // To handle datediffs correctly, we need to remove the year information from
+		    // the birthday and anniversary. 
+		    // To do this efficiently, we will create a temporary table which contains the
+		    // information we need and do the query on it. 
+		    // This table is just visible for this process and will be removed
+		    // automatically after using.
+		    temp_searchQuery = "CREATE TEMP TABLE bs ( uid, \"Birthday\", \"Anniversary\" );";
+		    temp_searchQuery += "INSERT INTO bs SELECT uid,substr(\"Birthday\", 6, 10),substr(\"Anniversary\", 6, 10) FROM addressbook WHERE ( \"Birthday\" != '' OR \"Anniversary\" != '' );";
+
+                    temp_searchQuery += QString( "SELECT uid FROM bs WHERE (\"%1\" <= '%2-%3\' AND \"%4\" >= '%5-%6')" )
                         .arg( *it )
-                        .arg( QString::number( endDate->year() ).rightJustify( 4, '0' ) )
+			    //.arg( QString::number( endDate->year() ).rightJustify( 4, '0' ) )
                         .arg( QString::number( endDate->month() ).rightJustify( 2, '0' ) )
                         .arg( QString::number( endDate->day() ).rightJustify( 2, '0' ) )
                         .arg( *it )
-                        .arg( QString::number( startDate.year() ).rightJustify( 4, '0' ) )
+			    //.arg( QString::number( startDate.year() ).rightJustify( 4, '0' ) )
                         .arg( QString::number( startDate.month() ).rightJustify( 2, '0' ) )
                         .arg( QString::number( startDate.day() ).rightJustify( 2, '0' ) ) ;
                 }
 
                 if ( settings & OPimContactAccess::DateYear ){
-                    if ( settings & OPimContactAccess::DateDiff )
-                        searchQuery += " AND";
+                    // if ( settings & OPimContactAccess::DateDiff )
+		    //     searchQuery += " AND";
 
                     searchQuery += QString( " (\"%1\" LIKE '%2-%')" )
                         .arg( *it )
                         .arg( QString::number( endDate->year() ).rightJustify( 4, '0' ) );
                 }
 
                 if ( settings & OPimContactAccess::DateMonth ){
                     if ( ( settings & OPimContactAccess::DateDiff )
                          || ( settings & OPimContactAccess::DateYear ) )
                         searchQuery += " AND";
 
                     searchQuery += QString( " (\"%1\" LIKE '%-%2-%')" )
                         .arg( *it )
                         .arg( QString::number( endDate->month() ).rightJustify( 2, '0' ) );
                 }
 
@@ -630,50 +640,66 @@ UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist,
             default:
                 // Switching between case sensitive and insensitive...
                 // LIKE is not case sensitive, GLOB is case sensitive
                 // Do exist a better solution to switch this ?
                 if ( settings & OPimContactAccess::IgnoreCase )
                     searchQuery += "(\"" + *it + "\"" + " LIKE " + "'"
                         + queryStr.replace(QRegExp("\\*"),"%") + "'" + ")";
                 else
                     searchQuery += "(\"" + *it + "\"" + " GLOB " + "'"
                         + queryStr + "'" + ")";
 
             }
         }
 
         delete endDate;
     }
-    // Skip trailing "AND"
-//  if ( isAnyFieldSelected )
-//      qu = qu.left( qu.length() - 4 );
 
+    // The following is very ugly! (eilers)
+    if ( !temp_searchQuery.isEmpty() && !searchQuery.isEmpty() ){
+	    // If we use DateDiff, we have to intersect two queries.
+	    qu = temp_searchQuery + QString( " INTERSECT " ) + qu + searchQuery;
+    } else if ( temp_searchQuery.isEmpty() && !searchQuery.isEmpty() ){
     qu += searchQuery;
+    } else if ( !temp_searchQuery.isEmpty() && searchQuery.isEmpty() ){
+	    // This will cause wrong results!! Uid filter is not used here!
+	    qu = temp_searchQuery;
+    } else if ( temp_searchQuery.isEmpty() && searchQuery.isEmpty() ){
+	    UIDArray empty;
+	    return empty;
+    } 
 
     odebug << "queryByExample query: " << qu << "" << oendl;
 
     // Execute query and return the received uid's
     OSQLRawQuery raw( qu );
     OSQLResult res = m_driver->query( &raw );
     if ( res.state() != OSQLResult::Success ){
         UIDArray empty;
         return empty;
     }
 
     UIDArray list = extractUids( res );
 
+    // Remove temp table if created
+    if ( !temp_searchQuery.isEmpty( ) ){
+	    qu = "DROP TABLE bs";
+	    OSQLRawQuery raw( qu );
+	    OSQLResult res = m_driver->query( &raw );
+    }
+
     return list;
 }
 
 UIDArray OPimContactAccessBackend_SQL::matchRegexp( const QRegExp &r ) const
 {
 #if 0
     QArray<int> nix(0);
     return nix;
 
 #else
     QString qu = "SELECT uid FROM addressbook WHERE (";
     QString searchlist;
 
     QStringList fieldList = OPimContactFields::untrfields( false );
     // QMap<QString, int> translate = OPimContactFields::untrFieldsToId();
     for ( QStringList::Iterator it = ++fieldList.begin(); it != fieldList.end(); ++it ){