Notification DBHElper

package app.seesharpsolutions.com.iscity.Utils;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import java.util.ArrayList;
import app.seesharpsolutions.com.iscity.Model.Notification;


public class DatabaseHandler extends SQLiteOpenHelper{
    // All Static variables
    // Database Version
    private static final int DATABASE_VERSION = 1;

    // Database Name
    private static final String DATABASE_NAME = "NotificationManager";

    // Contacts table name
    private static final String TABLE_CONTACTS = "notifications";

    // Contacts Table Columns names
    private static final String KEY_ID = "id";
    private static final String KEY_TITLE = "title";
    private static final String KEY_MESSAGE = "message";
    private static final String KEY_TIME = "time";
    private static final String KEY_DATE = "date";
    private static final String KEY_HASCHECK = "hascheck";
    private static final String KEY_TYPE = "type";
    //
    //    private int id;
    //    private String title;
    //    private String message;
    //    private String time;
    //    private String date;
    //    private boolean hasCheck;
    //
    public DatabaseHandler(Context context) {
        //super(context, DATABASE_NAME, null, DATABASE_VERSION);
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    // Creating Tables
    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_CONTACTS_TABLE = "CREATE TABLE IF NOT EXISTS " + TABLE_CONTACTS + "("
                + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_TITLE + " TEXT,"
                + KEY_MESSAGE + " TEXT," + KEY_TIME+" TEXT,"+KEY_DATE+" TEXT," + KEY_HASCHECK + " INTEGER," + KEY_TYPE + " TEXT)";
        db.execSQL(CREATE_CONTACTS_TABLE);
    }

    // Upgrading database
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop older table if existed
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS);

        // Create tables again
        onCreate(db);
    }

    public void addNotification(Notification notification) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_TITLE, notification.getTitle());
        values.put(KEY_MESSAGE, notification.getMessage());
        values.put(KEY_TIME, notification.getTime());
        values.put(KEY_DATE, notification.getDate());
        values.put(KEY_HASCHECK, notification.getHasCheck());
        values.put(KEY_TYPE, notification.getType());

        // Inserting Row
        db.insert(TABLE_CONTACTS, null, values);
        db.close(); // Closing database connection
    }

    public Notification getNotification(int id) {
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.query(TABLE_CONTACTS, new String[] { KEY_ID,
                KEY_TITLE, KEY_MESSAGE, KEY_TIME, KEY_DATE, KEY_HASCHECK, KEY_TYPE }, KEY_ID + "=?",
                new String[] { String.valueOf(id) }, null, null, null, null);
        if (cursor != null)
            cursor.moveToFirst();

        Notification notifications = new Notification(cursor.getInt(0),
                cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(4), cursor.getInt(5), cursor.getString(6));
        // return contact
        cursor.close();
        db.close();
        return notifications;
    }

    public ArrayList<Notification> getAllNotification() {
        ArrayList<Notification> notificationsList = new ArrayList<Notification>();
        // Select All Query
        String selectQuery = "SELECT  * FROM " + TABLE_CONTACTS;

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

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                Notification notifications = new Notification();
                notifications.setId(cursor.getInt(0));
                notifications.setTitle(cursor.getString(1));
                notifications.setMessage(cursor.getString(2));
                notifications.setTime(cursor.getString(3));
                notifications.setDate(cursor.getString(4));
                notifications.setHasCheck(cursor.getInt(5));
                notifications.setType(cursor.getString(6));
                // Adding contact to list
                notificationsList.add(notifications);
            } while (cursor.moveToNext());
        }

        // return contact list
        cursor.close();
        db.close();
        return notificationsList;
    }

    public ArrayList<Notification> getAllCompNotificationGroup() {
        ArrayList<Notification> notificationsList = new ArrayList<Notification>();
        // Select All Query
        String selectQuery = "SELECT MAX("+KEY_ID+"),"+
        KEY_TITLE+","+KEY_MESSAGE+","+KEY_TIME+","+KEY_DATE+","+KEY_HASCHECK+","+KEY_TYPE+" FROM " + TABLE_CONTACTS + " WHERE type LIKE " + "'com' GROUP BY "+KEY_TITLE+" ORDER BY "+KEY_ID+" DESC";

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

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                Notification notifications = new Notification();
                notifications.setId(cursor.getInt(0));
                notifications.setTitle(cursor.getString(1));
                notifications.setMessage(cursor.getString(2));
                notifications.setTime(cursor.getString(3));
                notifications.setDate(cursor.getString(4));
                notifications.setHasCheck(cursor.getInt(5));
                notifications.setType(cursor.getString(6));
                // Adding contact to list
                notificationsList.add(notifications);
            } while (cursor.moveToNext());
        }

        // return contact list
        cursor.close();
        db.close();
        return notificationsList;
    }

    public ArrayList<Notification> getAllComNotificationByCategory(String text) {
        ArrayList<Notification> notificationsList = new ArrayList<Notification>();
        // Select All Query
        String selectQuery = "SELECT  * FROM " + TABLE_CONTACTS + " WHERE type LIKE " + "'com' AND "+KEY_TITLE+" LIKE '%"+text+"%' ORDER BY "+ KEY_ID + " DESC";

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

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                Notification notifications = new Notification();
                notifications.setId(cursor.getInt(0));
                notifications.setTitle(cursor.getString(1));
                notifications.setMessage(cursor.getString(2));
                notifications.setTime(cursor.getString(3));
                notifications.setDate(cursor.getString(4));
                notifications.setHasCheck(cursor.getInt(5));
                notifications.setType(cursor.getString(6));
                // Adding contact to list
                notificationsList.add(notifications);
            } while (cursor.moveToNext());
        }

        // return contact list
        cursor.close();
        db.close();
        return notificationsList;
    }

    public ArrayList<Notification> getAllMemNotificationByCategory(String text) {
        ArrayList<Notification> notificationsList = new ArrayList<Notification>();
        // Select All Query
        String selectQuery = "SELECT  * FROM " + TABLE_CONTACTS + " WHERE type LIKE " + "'mem' AND "+KEY_TITLE+" LIKE '%"+text+"%' ORDER BY "+ KEY_ID + " DESC";
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                Notification notifications = new Notification();
                notifications.setId(cursor.getInt(0));
                notifications.setTitle(cursor.getString(1));
                notifications.setMessage(cursor.getString(2));
                notifications.setTime(cursor.getString(3));
                notifications.setDate(cursor.getString(4));
                notifications.setHasCheck(cursor.getInt(5));
                notifications.setType(cursor.getString(6));
                // Adding contact to list
                notificationsList.add(notifications);
            } while (cursor.moveToNext());
        }
        // return contact list
        cursor.close();
        db.close();
        return notificationsList;
    }


    public ArrayList<Notification> getAllMemNotificationGroup() {
        ArrayList<Notification> notificationsList = new ArrayList<Notification>();
        // Select All Query
        String selectQuery = "SELECT MAX("+KEY_ID+"),"+
                                KEY_TITLE+","+KEY_MESSAGE+","+KEY_TIME+","+KEY_DATE+","+KEY_HASCHECK+","+KEY_TYPE+" FROM " + TABLE_CONTACTS + " WHERE type LIKE " + "'mem' GROUP BY "+KEY_TITLE+" ORDER BY "+KEY_ID+" DESC";
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                Notification notifications = new Notification();
                notifications.setId(cursor.getInt(0));
                notifications.setTitle(cursor.getString(1));
                notifications.setMessage(cursor.getString(2));
                notifications.setTime(cursor.getString(3));
                notifications.setDate(cursor.getString(4));
                notifications.setHasCheck(cursor.getInt(5));
                notifications.setType(cursor.getString(6));
                // Adding contact to list
                notificationsList.add(notifications);
            } while (cursor.moveToNext());
        }
        // return contact list
        cursor.close();
        db.close();
        return notificationsList;
    }

    public ArrayList<Notification> getAllCompNotification() {
        ArrayList<Notification> notificationsList = new ArrayList<Notification>();
        // Select All Query
        String selectQuery = "SELECT "+KEY_ID+","+
                KEY_TITLE+","+KEY_MESSAGE+","+KEY_TIME+","+KEY_DATE+","+KEY_HASCHECK+","+KEY_TYPE+" FROM " + TABLE_CONTACTS + " WHERE type LIKE " + "'com' ORDER BY "+KEY_ID+" DESC";

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

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                Notification notifications = new Notification();
                notifications.setId(cursor.getInt(0));
                notifications.setTitle(cursor.getString(1));
                notifications.setMessage(cursor.getString(2));
                notifications.setTime(cursor.getString(3));
                notifications.setDate(cursor.getString(4));
                notifications.setHasCheck(cursor.getInt(5));
                notifications.setType(cursor.getString(6));
                // Adding contact to list
                notificationsList.add(notifications);
            } while (cursor.moveToNext());
        }

        // return contact list
        cursor.close();
        db.close();
        return notificationsList;
    }

    public ArrayList<Notification> getAllMemNotification() {
        ArrayList<Notification> notificationsList = new ArrayList<Notification>();
        // Select All Query
        String selectQuery = "SELECT "+KEY_ID+","+
                KEY_TITLE+","+KEY_MESSAGE+","+KEY_TIME+","+KEY_DATE+","+KEY_HASCHECK+","+KEY_TYPE+" FROM " + TABLE_CONTACTS + " WHERE type LIKE " + "'mem' ORDER BY "+KEY_ID+" DESC";
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                Notification notifications = new Notification();
                notifications.setId(cursor.getInt(0));
                notifications.setTitle(cursor.getString(1));
                notifications.setMessage(cursor.getString(2));
                notifications.setTime(cursor.getString(3));
                notifications.setDate(cursor.getString(4));
                notifications.setHasCheck(cursor.getInt(5));
                notifications.setType(cursor.getString(6));
                // Adding contact to list
                notificationsList.add(notifications);
            } while (cursor.moveToNext());
        }
        // return contact list
        cursor.close();
        db.close();
        return notificationsList;
    }

    public int getNotificationCount() {
        int count;
        String countQuery = "SELECT  * FROM " + TABLE_CONTACTS;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        count = cursor.getCount();
        cursor.close();
        db.close();
        // return count
        return count;
    }

    public int getComNotificationCountUncheckWithTile(String title) {
        int count;
        String countQuery = "SELECT  * FROM " + TABLE_CONTACTS+" WHERE "+KEY_TITLE+" LIKE '%"+title+"%' AND type LIKE 'com' AND "+KEY_HASCHECK+" = 0";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        count = cursor.getCount();
        cursor.close();
        db.close();
        // return count
        return count;
    }

    public int getMemNotificationCountUncheckWithTile(String title) {
        int count;
        String countQuery = "SELECT  * FROM " + TABLE_CONTACTS+" WHERE "+KEY_TITLE+" LIKE '%"+title+"%' AND type LIKE 'mem' AND "+KEY_HASCHECK+" = 0";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        count = cursor.getCount();
        cursor.close();
        db.close();
        // return count
        return count;
    }

    public int getComNotificationCount() {
        int count;
        String countQuery = "SELECT * FROM " + TABLE_CONTACTS + " WHERE type LIKE " + "'com' AND "+KEY_HASCHECK+" = 0";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        count = cursor.getCount();
        cursor.close();
        db.close();
        // return count
        return count;
    }

    public int getComNotificationCountUncheck() {
        int count;
        String countQuery = "SELECT  * FROM " + TABLE_CONTACTS + " WHERE type LIKE " + "'com'" + " AND " + KEY_HASCHECK + " = 0";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        count = cursor.getCount();
        cursor.close();
        db.close();
        // return count
        return count;
    }


    public int getMemNotificationCountUncheck() {
        int count;
        String countQuery = "SELECT  * FROM " + TABLE_CONTACTS + " WHERE type LIKE " + "'mem'" + " AND " + KEY_HASCHECK + " = 0";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        count = cursor.getCount();
        cursor.close();
        db.close();
        // return count
        return count;
    }

    public int getMemNotificationCount() {
        int count;
        String countQuery = "SELECT  * FROM " + TABLE_CONTACTS + " WHERE type LIKE " + "'mem'";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        count = cursor.getCount();
        cursor.close();
        db.close();
        // return count
        return count;
    }

    public int updateNotification(Notification notifications) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_TITLE, notifications.getTitle());
        values.put(KEY_MESSAGE, notifications.getMessage());
        values.put(KEY_TIME, notifications.getTime());
        values.put(KEY_DATE, notifications.getDate());
        values.put(KEY_HASCHECK, notifications.getHasCheck());
        values.put(KEY_TYPE, notifications.getType());

        // updating row
        int row = db.update(TABLE_CONTACTS, values, KEY_ID + " = ?",
                new String[] { String.valueOf(notifications.getId()) });

        db.close();
        return row;
    }

    public void deleteNotification(Notification notifications) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_CONTACTS, KEY_ID + " = ?",
                new String[] { String.valueOf(notifications.getId()) });
        db.close();
    }

//    public void deleteNotification(String title) {
//        SQLiteDatabase db = this.getWritableDatabase();
//        db.delete(TABLE_CONTACTS, KEY_TITLE + " = ?",
//                new String[] { title });
//        db.close();
//    }
    public void deleteNotification(String id) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_CONTACTS, KEY_ID + " = ?",
                new String[] { id });
        db.close();
    }

    public void deleteNotificationbytitle(String title) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_CONTACTS, KEY_TITLE + " = ?",
                new String[] { title });
        db.close();
    }

    public void deleteAll()
    {
        SQLiteDatabase db= this.getWritableDatabase();
        db.delete(TABLE_CONTACTS, null, null);
        db.close();
    }

    public void deleteTable(Context context)
    {
        //SQLiteDatabase db= this.getWritableDatabase();
        //db.delete(TABLE_CONTACTS, null, null);
        //db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS);
        //db.close();
        context.deleteDatabase(DATABASE_NAME);
    }

    public boolean checkDbExist()
    {
        SQLiteDatabase db= this.getReadableDatabase();
        Cursor cursor = db.rawQuery("select DISTINCT tbl_name from sqlite_master where tbl_name = '"+TABLE_CONTACTS+"'", null);

        boolean flag = false;
        if(cursor != null) {
            if(cursor.getCount()>0)
            {
                flag = true;
            }
            else
            {
                flag = false;
            }
           cursor.close();
        }
        return flag;
    }

    public Notification getLastNotification()
    {
        Notification notifications = null;
        String query = "SELECT * FROM " +TABLE_CONTACTS;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(query, null);
        if (cursor != null)
        {
            cursor.moveToLast();
            //notification = new Notification(Integer.parseInt(cursor.getString(0)),
            //    cursor.getString(2), cursor.getString(3), cursor.getString(1), cursor.getString(4));
        }

        cursor.close();
        db.close();
        return notifications;
    }
}