author | zecke <zecke> | 2002-10-07 14:59:44 (UTC) |
---|---|---|
committer | zecke <zecke> | 2002-10-07 14:59:44 (UTC) |
commit | e68537563c9950654b9a771730f0fc3890803a54 (patch) (unidiff) | |
tree | fd64fada00405ca8ad65e9334bcdb4c3da3f1a9a /libopie/pim | |
parent | 645b377506fb32f4519e70d43c020084943debae (diff) | |
download | opie-e68537563c9950654b9a771730f0fc3890803a54.zip opie-e68537563c9950654b9a771730f0fc3890803a54.tar.gz opie-e68537563c9950654b9a771730f0fc3890803a54.tar.bz2 |
Fix sorted SQL query
-rw-r--r-- | libopie/pim/otodoaccesssql.cpp | 7 |
1 files changed, 5 insertions, 2 deletions
diff --git a/libopie/pim/otodoaccesssql.cpp b/libopie/pim/otodoaccesssql.cpp index 25536e0..8add9f7 100644 --- a/libopie/pim/otodoaccesssql.cpp +++ b/libopie/pim/otodoaccesssql.cpp | |||
@@ -262,193 +262,196 @@ OTodo OTodoAccessBackendSQL::find(int uid ) const{ | |||
262 | 262 | ||
263 | } | 263 | } |
264 | void OTodoAccessBackendSQL::clear() { | 264 | void OTodoAccessBackendSQL::clear() { |
265 | ClearQuery cle; | 265 | ClearQuery cle; |
266 | OSQLResult res = m_driver->query( &cle ); | 266 | OSQLResult res = m_driver->query( &cle ); |
267 | CreateQuery qu; | 267 | CreateQuery qu; |
268 | res = m_driver->query(&qu); | 268 | res = m_driver->query(&qu); |
269 | } | 269 | } |
270 | bool OTodoAccessBackendSQL::add( const OTodo& t) { | 270 | bool OTodoAccessBackendSQL::add( const OTodo& t) { |
271 | InsertQuery ins( t ); | 271 | InsertQuery ins( t ); |
272 | OSQLResult res = m_driver->query( &ins ); | 272 | OSQLResult res = m_driver->query( &ins ); |
273 | 273 | ||
274 | if ( res.state() == OSQLResult::Failure ) | 274 | if ( res.state() == OSQLResult::Failure ) |
275 | return false; | 275 | return false; |
276 | int c = m_uids.count(); | 276 | int c = m_uids.count(); |
277 | m_uids.resize( c+1 ); | 277 | m_uids.resize( c+1 ); |
278 | m_uids[c] = t.uid(); | 278 | m_uids[c] = t.uid(); |
279 | 279 | ||
280 | return true; | 280 | return true; |
281 | } | 281 | } |
282 | bool OTodoAccessBackendSQL::remove( int uid ) { | 282 | bool OTodoAccessBackendSQL::remove( int uid ) { |
283 | RemoveQuery rem( uid ); | 283 | RemoveQuery rem( uid ); |
284 | OSQLResult res = m_driver->query(&rem ); | 284 | OSQLResult res = m_driver->query(&rem ); |
285 | 285 | ||
286 | if ( res.state() == OSQLResult::Failure ) | 286 | if ( res.state() == OSQLResult::Failure ) |
287 | return false; | 287 | return false; |
288 | 288 | ||
289 | update(); | 289 | update(); |
290 | return true; | 290 | return true; |
291 | } | 291 | } |
292 | /* | 292 | /* |
293 | * FIXME better set query | 293 | * FIXME better set query |
294 | * but we need the cache for that | 294 | * but we need the cache for that |
295 | * now we remove | 295 | * now we remove |
296 | */ | 296 | */ |
297 | bool OTodoAccessBackendSQL::replace( const OTodo& t) { | 297 | bool OTodoAccessBackendSQL::replace( const OTodo& t) { |
298 | remove( t.uid() ); | 298 | remove( t.uid() ); |
299 | return add(t); | 299 | return add(t); |
300 | } | 300 | } |
301 | QArray<int> OTodoAccessBackendSQL::overDue() { | 301 | QArray<int> OTodoAccessBackendSQL::overDue() { |
302 | OverDueQuery qu; | 302 | OverDueQuery qu; |
303 | return uids( m_driver->query(&qu ) ); | 303 | return uids( m_driver->query(&qu ) ); |
304 | } | 304 | } |
305 | QArray<int> OTodoAccessBackendSQL::effectiveToDos( const QDate& s, | 305 | QArray<int> OTodoAccessBackendSQL::effectiveToDos( const QDate& s, |
306 | const QDate& t, | 306 | const QDate& t, |
307 | bool u) { | 307 | bool u) { |
308 | EffQuery ef(s, t, u ); | 308 | EffQuery ef(s, t, u ); |
309 | return uids (m_driver->query(&ef) ); | 309 | return uids (m_driver->query(&ef) ); |
310 | } | 310 | } |
311 | /* | 311 | /* |
312 | * | 312 | * |
313 | */ | 313 | */ |
314 | QArray<int> OTodoAccessBackendSQL::sorted( bool asc, int sortOrder, | 314 | QArray<int> OTodoAccessBackendSQL::sorted( bool asc, int sortOrder, |
315 | int sortFilter, int cat ) { | 315 | int sortFilter, int cat ) { |
316 | QString query; | 316 | QString query; |
317 | query = "select uid from todolist WHERE "; | 317 | query = "select uid from todolist WHERE "; |
318 | 318 | ||
319 | /* | 319 | /* |
320 | * Sort Filter stuff | 320 | * Sort Filter stuff |
321 | * not that straight forward | 321 | * not that straight forward |
322 | * | 322 | * |
323 | */ | 323 | */ |
324 | /* Category */ | 324 | /* Category */ |
325 | if ( sortFilter & 1 ) { | 325 | if ( sortFilter & 1 ) { |
326 | query += " categories like '%" +QString::number(cat)+"%' AND"; | 326 | QString str; |
327 | if (cat != 0 ) str = QString::number( cat ); | ||
328 | query += " categories like '%" +str+"%' AND"; | ||
327 | } | 329 | } |
328 | /* Show only overdue */ | 330 | /* Show only overdue */ |
329 | if ( sortFilter & 2 ) { | 331 | if ( sortFilter & 2 ) { |
330 | QDate date = QDate::currentDate(); | 332 | QDate date = QDate::currentDate(); |
331 | QString due; | 333 | QString due; |
332 | QString base; | 334 | QString base; |
333 | base = QString("DueDate <= '%1-%2-%3' AND WHERE completed = 0").arg( date.year() ).arg( date.month() ).arg( date.day() ); | 335 | base = QString("DueDate <= '%1-%2-%3' AND completed = 0").arg( date.year() ).arg( date.month() ).arg( date.day() ); |
334 | query += " " + base + " AND"; | 336 | query += " " + base + " AND"; |
335 | } | 337 | } |
336 | /* not show completed */ | 338 | /* not show completed */ |
337 | if ( sortFilter & 4 ) { | 339 | if ( sortFilter & 4 ) { |
338 | query += " completed = 0 AND"; | 340 | query += " completed = 0 AND"; |
339 | }else{ | 341 | }else{ |
340 | query += " ( completed = 1 OR completed = 0) AND"; | 342 | query += " ( completed = 1 OR completed = 0) AND"; |
341 | } | 343 | } |
342 | /* srtip the end */ | 344 | /* srtip the end */ |
343 | query = query.remove( query.length()-3, 3 ); | 345 | query = query.remove( query.length()-3, 3 ); |
344 | 346 | ||
345 | 347 | ||
346 | /* | 348 | /* |
347 | * sort order stuff | 349 | * sort order stuff |
348 | * quite straight forward | 350 | * quite straight forward |
349 | */ | 351 | */ |
350 | query += "ORDER BY "; | 352 | query += "ORDER BY "; |
351 | switch( sortOrder ) { | 353 | switch( sortOrder ) { |
352 | /* completed */ | 354 | /* completed */ |
353 | case 0: | 355 | case 0: |
354 | query += "completed"; | 356 | query += "completed"; |
355 | break; | 357 | break; |
356 | case 1: | 358 | case 1: |
357 | query += "priority"; | 359 | query += "priority"; |
358 | break; | 360 | break; |
359 | case 2: | 361 | case 2: |
360 | query += "description"; | 362 | query += "description"; |
361 | break; | 363 | break; |
362 | case 3: | 364 | case 3: |
363 | query += "DueDate"; | 365 | query += "DueDate"; |
364 | break; | 366 | break; |
365 | } | 367 | } |
366 | if ( !asc ) | 368 | if ( !asc ) |
367 | query += " DESC"; | 369 | query += " DESC"; |
368 | 370 | ||
369 | qWarning( query ); | 371 | qWarning( query ); |
370 | OSQLRawQuery raw(query ); | 372 | OSQLRawQuery raw(query ); |
371 | return uids( m_driver->query(&raw) ); | 373 | return uids( m_driver->query(&raw) ); |
372 | } | 374 | } |
373 | bool OTodoAccessBackendSQL::date( QDate& da, const QString& str ) const{ | 375 | bool OTodoAccessBackendSQL::date( QDate& da, const QString& str ) const{ |
374 | if ( str == "0-0-0" ) | 376 | if ( str == "0-0-0" ) |
375 | return false; | 377 | return false; |
376 | else{ | 378 | else{ |
377 | int day, year, month; | 379 | int day, year, month; |
378 | QStringList list = QStringList::split("-", str ); | 380 | QStringList list = QStringList::split("-", str ); |
379 | year = list[0].toInt(); | 381 | year = list[0].toInt(); |
380 | month = list[1].toInt(); | 382 | month = list[1].toInt(); |
381 | day = list[2].toInt(); | 383 | day = list[2].toInt(); |
382 | da.setYMD( year, month, day ); | 384 | da.setYMD( year, month, day ); |
383 | return true; | 385 | return true; |
384 | } | 386 | } |
385 | } | 387 | } |
386 | OTodo OTodoAccessBackendSQL::todo( const OSQLResult& res) const{ | 388 | OTodo OTodoAccessBackendSQL::todo( const OSQLResult& res) const{ |
387 | if ( res.state() == OSQLResult::Failure ) { | 389 | if ( res.state() == OSQLResult::Failure ) { |
388 | OTodo to; | 390 | OTodo to; |
389 | return to; | 391 | return to; |
390 | } | 392 | } |
391 | 393 | ||
392 | OSQLResultItem::ValueList list = res.results(); | 394 | OSQLResultItem::ValueList list = res.results(); |
393 | OSQLResultItem::ValueList::Iterator it = list.begin(); | 395 | OSQLResultItem::ValueList::Iterator it = list.begin(); |
394 | 396 | ||
395 | bool has = false; QDate da = QDate::currentDate(); | 397 | bool has = false; QDate da = QDate::currentDate(); |
396 | has = date( da, (*it).data("DueDate") ); | 398 | has = date( da, (*it).data("DueDate") ); |
397 | QStringList cats = QStringList::split(";", (*it).data("categories") ); | 399 | QStringList cats = QStringList::split(";", (*it).data("categories") ); |
398 | 400 | ||
399 | OTodo to( (bool)(*it).data("completed").toInt(), (*it).data("priority").toInt(), | 401 | OTodo to( (bool)(*it).data("completed").toInt(), (*it).data("priority").toInt(), |
400 | cats, (*it).data("summary"), (*it).data("description"), | 402 | cats, (*it).data("summary"), (*it).data("description"), |
401 | (*it).data("progress").toUShort(), has, da, (*it).data("uid").toInt() ); | 403 | (*it).data("progress").toUShort(), has, da, (*it).data("uid").toInt() ); |
402 | return to; | 404 | return to; |
403 | } | 405 | } |
404 | OTodo OTodoAccessBackendSQL::todo( int uid )const { | 406 | OTodo OTodoAccessBackendSQL::todo( int uid )const { |
405 | FindQuery find( uid ); | 407 | FindQuery find( uid ); |
406 | return todo( m_driver->query(&find) ); | 408 | return todo( m_driver->query(&find) ); |
407 | } | 409 | } |
408 | /* | 410 | /* |
409 | * update the dict | 411 | * update the dict |
410 | */ | 412 | */ |
411 | void OTodoAccessBackendSQL::fillDict() { | 413 | void OTodoAccessBackendSQL::fillDict() { |
412 | /* initialize dict */ | 414 | /* initialize dict */ |
413 | /* | 415 | /* |
414 | * UPDATE dict if you change anything!!! | 416 | * UPDATE dict if you change anything!!! |
415 | */ | 417 | */ |
416 | m_dict.setAutoDelete( TRUE ); | 418 | m_dict.setAutoDelete( TRUE ); |
417 | m_dict.insert("Categories" , new int(OTodo::Category) ); | 419 | m_dict.insert("Categories" , new int(OTodo::Category) ); |
418 | m_dict.insert("Uid" , new int(OTodo::Uid) ); | 420 | m_dict.insert("Uid" , new int(OTodo::Uid) ); |
419 | m_dict.insert("HasDate" , new int(OTodo::HasDate) ); | 421 | m_dict.insert("HasDate" , new int(OTodo::HasDate) ); |
420 | m_dict.insert("Completed" , new int(OTodo::Completed) ); | 422 | m_dict.insert("Completed" , new int(OTodo::Completed) ); |
421 | m_dict.insert("Description" , new int(OTodo::Description) ); | 423 | m_dict.insert("Description" , new int(OTodo::Description) ); |
422 | m_dict.insert("Summary" , new int(OTodo::Summary) ); | 424 | m_dict.insert("Summary" , new int(OTodo::Summary) ); |
423 | m_dict.insert("Priority" , new int(OTodo::Priority) ); | 425 | m_dict.insert("Priority" , new int(OTodo::Priority) ); |
424 | m_dict.insert("DateDay" , new int(OTodo::DateDay) ); | 426 | m_dict.insert("DateDay" , new int(OTodo::DateDay) ); |
425 | m_dict.insert("DateMonth" , new int(OTodo::DateMonth) ); | 427 | m_dict.insert("DateMonth" , new int(OTodo::DateMonth) ); |
426 | m_dict.insert("DateYear" , new int(OTodo::DateYear) ); | 428 | m_dict.insert("DateYear" , new int(OTodo::DateYear) ); |
427 | m_dict.insert("Progress" , new int(OTodo::Progress) ); | 429 | m_dict.insert("Progress" , new int(OTodo::Progress) ); |
428 | m_dict.insert("Completed", new int(OTodo::Completed) ); | 430 | m_dict.insert("Completed", new int(OTodo::Completed) ); |
429 | m_dict.insert("CrossReference", new int(OTodo::CrossReference) ); | 431 | m_dict.insert("CrossReference", new int(OTodo::CrossReference) ); |
430 | m_dict.insert("HasAlarmDateTime",new int(OTodo::HasAlarmDateTime) ); | 432 | m_dict.insert("HasAlarmDateTime",new int(OTodo::HasAlarmDateTime) ); |
431 | m_dict.insert("AlarmDateTime", new int(OTodo::AlarmDateTime) ); | 433 | m_dict.insert("AlarmDateTime", new int(OTodo::AlarmDateTime) ); |
432 | } | 434 | } |
433 | void OTodoAccessBackendSQL::update() { | 435 | void OTodoAccessBackendSQL::update() { |
434 | LoadQuery lo; | 436 | LoadQuery lo; |
435 | OSQLResult res = m_driver->query(&lo); | 437 | OSQLResult res = m_driver->query(&lo); |
436 | if ( res.state() != OSQLResult::Success ) | 438 | if ( res.state() != OSQLResult::Success ) |
437 | return; | 439 | return; |
438 | 440 | ||
439 | m_uids = uids( res ); | 441 | m_uids = uids( res ); |
440 | } | 442 | } |
441 | QArray<int> OTodoAccessBackendSQL::uids( const OSQLResult& res) const{ | 443 | QArray<int> OTodoAccessBackendSQL::uids( const OSQLResult& res) const{ |
442 | 444 | ||
443 | OSQLResultItem::ValueList list = res.results(); | 445 | OSQLResultItem::ValueList list = res.results(); |
444 | OSQLResultItem::ValueList::Iterator it; | 446 | OSQLResultItem::ValueList::Iterator it; |
445 | QArray<int> ints(list.count() ); | 447 | QArray<int> ints(list.count() ); |
448 | qWarning(" count = %d", list.count() ); | ||
446 | 449 | ||
447 | int i = 0; | 450 | int i = 0; |
448 | for (it = list.begin(); it != list.end(); ++it ) { | 451 | for (it = list.begin(); it != list.end(); ++it ) { |
449 | ints[i] = (*it).data("uid").toInt(); | 452 | ints[i] = (*it).data("uid").toInt(); |
450 | i++; | 453 | i++; |
451 | } | 454 | } |
452 | return ints; | 455 | return ints; |
453 | } | 456 | } |
454 | 457 | ||