lundi 22 février 2016

Update query to check if event dose overlaps

I have created events. I want to check if the events overlaps. For this I have a function in database to check if events overlaps. I have saved dates for this, from date and to date.

But for update, if I want to extend the time of my event , it shows event exists.

As may be start time is equal every time. How to query for update to check if other current event dose not overlap another event.

function:

     boolean doesEventOverlap(String startTime, String endTime, String day) {
    String selectQuery = "SELECT COUNT(*) FROM " + TABLE + " WHERE " + KEY_DAY_OF_WEEK + " = '" + day + "'" + " AND "

            + "(" + KEY_FROM_DATE + " < '" + startTime
            + "' AND '" + startTime + "' < " + KEY_TO_DATE + ") OR "

            + " (" + KEY_FROM_DATE + " < '" + endTime
            + "' AND '" + endTime + "' < " + KEY_TO_DATE + ") OR "

            + " (" + KEY_FROM_DATE + " < '" + startTime
            + "' AND '" + endTime + "' < " + KEY_TO_DATE + ") OR "

            + " ('" + startTime + "' < " + KEY_FROM_DATE
            + " AND " + KEY_TO_DATE + " < '" + endTime + "')";


    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(selectQuery, null);

    if (cursor.moveToFirst()) {
        do {
            EventData event = new EventData();
            return cursor.getInt(0) > 0;
        } while (cursor.moveToNext());
    }
    return false;
}

create table:

public void createTable(SQLiteDatabase db){
    String CREATE_EVENTS_TABLE = "CREATE TABLE " + TABLE_EVENTS + "("
            + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + KEY_TITLE + " TEXT,"
            + KEY_FROM_DATE + " DATE,"
            + KEY_TO_DATE + " DATE,"
            + KEY_DAY_OF_WEEK + " TEXT,"
            + KEY_LOCATION + " TEXT,"
            + KEY_NOTIFICATION_TIME + " DATE,"
            + KEY_COLOR + " TEXT" + ")";

    db.execSQL(CREATE_EVENTS_TABLE);
}

Thank you..

Aucun commentaire:

Enregistrer un commentaire