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,3 +1,5 @@
-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
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
@@ -549,4 +549,5 @@ UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist,
 
     QString searchQuery ="";
+    QString temp_searchQuery = "";
 
     QDate startDate;
@@ -586,11 +587,20 @@ UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist,
 
                 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' ) ) ;
@@ -598,6 +608,6 @@ UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist,
 
                 if ( settings & OPimContactAccess::DateYear ){
-                    if ( settings & OPimContactAccess::DateDiff )
-                        searchQuery += " AND";
+                    // if ( settings & OPimContactAccess::DateDiff )
+		    //     searchQuery += " AND";
 
                     searchQuery += QString( " (\"%1\" LIKE '%2-%')" )
@@ -644,9 +654,18 @@ UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist,
         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;
@@ -662,4 +681,11 @@ UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist,
     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;
 }