DBHelper

package info.lina.netstatlog;

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;

/**
 * Created by user on 13/02/2016.
 */
public class DBHelper extends SQLiteOpenHelper {

    public static final String DATABASE_NAME = "DbSMS.db";
    public static final String SMS_TABLE_NAME = "sms";
    public static final String SMS_COLUMN_DATE = "date";
    public static final String SMS_COLUMN_SENDER = "address";
    public static final String SMS_COLUMN_CONTENT = "body";
    public static final String SMS_CONTENT_INETUP = "inet up";
    public static final String SMS_CONTENT_INETDOWN = "inet down";
    public static final String SMS_CONTENT_UPSUP = "ups up";
    public static final String SMS_CONTENT_UPSDOWN = "ups down";
    public static final String SMS_CONTENT_POWERUP = "power up";
    public static final String SMS_CONTENT_POWERDOWN = "power down";

    private static final String TABLE_LOG = "log";
    private static final String TABLE_SMS = "sms";
    private static final String TABLE_UPS = "ups";
    private static final String TABLE_INET = "inet";
    private static final String TABLE_PWR = "power";
    private static final String KEY_ID = "id";
    private static final String KEY_SENDER = "sender";
    private static final String KEY_CONTENT = "content";
    private static final String KEY_DATE = "date";
    private static final String KEY_CREATED_AT = "created_at";

    public DBHelper(Context context) {
        super(context, DATABASE_NAME, null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        System.out.println("Creating table...");
        // creating required tables
        db.execSQL(CREATE_TABLE_SMS);
        db.execSQL(CREATE_TABLE_UPS);
        db.execSQL(CREATE_TABLE_INET);
        db.execSQL(CREATE_TABLE_PWR);
    }

    private static final String CREATE_TABLE_SMS = "CREATE TABLE "
            + TABLE_SMS + "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_DATE
            + " INTEGER," + KEY_SENDER + " TEXT," + KEY_CONTENT + " TEXT," + KEY_CREATED_AT
            + " DATETIME" + ")";

    private static final String CREATE_TABLE_UPS = "CREATE TABLE "
            + TABLE_UPS + "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_DATE
            + " INTEGER," + KEY_SENDER + " TEXT," + KEY_CONTENT + " TEXT," + KEY_CREATED_AT
            + " DATETIME" + ")";

    private static final String CREATE_TABLE_INET = "CREATE TABLE "
            + TABLE_INET + "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_DATE
            + " INTEGER," + KEY_SENDER + " TEXT," + KEY_CONTENT + " TEXT," + KEY_CREATED_AT
            + " DATETIME" + ")";

    private static final String CREATE_TABLE_PWR = "CREATE TABLE "
            + TABLE_PWR + "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_DATE
            + " INTEGER," + KEY_SENDER + " TEXT," + KEY_CONTENT + " TEXT," + KEY_CREATED_AT
            + " DATETIME" + ")";

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_SMS);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_UPS);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_INET);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_PWR);
        onCreate(db);
    }

    public boolean insertSms(String date, String sender, String content) {
        System.out.println("Inserting table...");

        boolean status = isExist(date, content, TABLE_SMS);
        if (!status) {
            SQLiteDatabase db = this.getWritableDatabase();
            ContentValues contentValues = new ContentValues();
            contentValues.put("date", date);
            contentValues.put("sender", sender);
            contentValues.put("content", content);
            db.insert(TABLE_SMS, null, contentValues);
            System.out.println("Insert sms: " + date + ", " + sender + ", " + content);
        }
        return true;
    }

    public boolean insertUps(String date, String sender, String content) {
        System.out.println("Inserting table...");

        boolean status = isExist(date, content, TABLE_UPS);
        if (!status) {
            SQLiteDatabase db = this.getWritableDatabase();
            ContentValues contentValues = new ContentValues();
            contentValues.put("date", date);
            contentValues.put("sender", sender);
            contentValues.put("content", content);
            db.insert(TABLE_UPS, null, contentValues);
            System.out.println("Insert ups: " + date + ", " + sender + ", " + content);
        }
        return true;
    }

    public boolean insertInet(String date, String sender, String content) {
        System.out.println("Inserting table...");

        boolean status = isExist(date, content, TABLE_INET);
        if (!status) {
            SQLiteDatabase db = this.getWritableDatabase();
            ContentValues contentValues = new ContentValues();
            contentValues.put("date", date);
            contentValues.put("sender", sender);
            contentValues.put("content", content);
            db.insert(TABLE_INET, null, contentValues);
            System.out.println("Insert inet: " + date + ", " + sender + ", " + content);
        }
        return true;
    }

    public boolean insertPwr(String date, String sender, String content) {
        System.out.println("Inserting table...");

        boolean status = isExist(date, content, TABLE_PWR);
        if (!status) {
            SQLiteDatabase db = this.getWritableDatabase();
            ContentValues contentValues = new ContentValues();
            contentValues.put("date", date);
            contentValues.put("sender", sender);
            contentValues.put("content", content);
            db.insert(TABLE_PWR, null, contentValues);
            System.out.println("Insert pwr: " + date + ", " + sender + ", " + content);
        }
        return true;
    }

    public boolean isExist(String date, String content, String table) {
        boolean status = false;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor res = db.rawQuery("select * from '" + table + "' where date='" + date
                + "' and content='" + content + "'", null);
        res.moveToFirst();

        while (res.isAfterLast() == false) {
            String cdate = res.getString(res.getColumnIndex(SMS_COLUMN_DATE));
            res.moveToNext();
            if (cdate != null) {
                status = true;
            }
        }
        System.out.println("isExist: " + status);
        return status;
    }

    public ArrayList<String> getAll(String date) {
        ArrayList<String> array_list = new ArrayList<String>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor res = db.rawQuery("select * from sms where date='" + date + "'", null);
        res.moveToFirst();

        while (res.isAfterLast() == false) {
            array_list.add(res.getString(res.getColumnIndex(SMS_COLUMN_DATE)));
            res.moveToNext();
        }

        return array_list;
    }

    public ArrayList<String> getInetUp(String content) {
        ArrayList<String> array_list = new ArrayList<String>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor res = db.rawQuery("select * from sms where content='" + content + "'", null);
        res.moveToFirst();

        while (res.isAfterLast() == false) {
            array_list.add(res.getString(res.getColumnIndex(SMS_COLUMN_DATE)));
            res.moveToNext();
        }

        return array_list;
    }

    public ArrayList<String> getInetDown(String content) {
        ArrayList<String> array_list = new ArrayList<String>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor res = db.rawQuery("select * from sms where content=" + content + "", null);
        res.moveToFirst();

        while (res.isAfterLast() == false) {
            array_list.add(res.getString(res.getColumnIndex(SMS_COLUMN_DATE)));
            res.moveToNext();
        }

        return array_list;
    }

    public ArrayList<String> getPowerUp(String content) {
        ArrayList<String> array_list = new ArrayList<String>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor res = db.rawQuery("select * from sms where content=" + content + "", null);
        res.moveToFirst();

        while (res.isAfterLast() == false) {
            array_list.add(res.getString(res.getColumnIndex(SMS_COLUMN_DATE)));
            res.moveToNext();
        }

        return array_list;
    }

    public ArrayList<String> getPowerDown(String content) {
        ArrayList<String> array_list = new ArrayList<String>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor res = db.rawQuery("select * from sms where content=" + content + "", null);
        res.moveToFirst();

        while (res.isAfterLast() == false) {
            array_list.add(res.getString(res.getColumnIndex(SMS_COLUMN_DATE)));
            res.moveToNext();
        }

        return array_list;
    }

    public ArrayList<String> getUpsUp(String content) {
        ArrayList<String> array_list = new ArrayList<String>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor res = db.rawQuery("select * from sms where content=" + content + "", null);
        res.moveToFirst();

        while (res.isAfterLast() == false) {
            array_list.add(res.getString(res.getColumnIndex(SMS_COLUMN_DATE)));
            res.moveToNext();
        }

        return array_list;
    }

    public ArrayList<String> getUpsDown(String content) {
        ArrayList<String> array_list = new ArrayList<String>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor res = db.rawQuery("select * from sms where content=" + content + "", null);
        res.moveToFirst();

        while (res.isAfterLast() == false) {
            array_list.add(res.getString(res.getColumnIndex(SMS_COLUMN_DATE)));
            res.moveToNext();
        }

        return array_list;
    }

    public ArrayList<String> getLog() {
        ArrayList<String> array_list = new ArrayList<String>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor res = db.rawQuery("select * from sms", null);
        res.moveToFirst();

        while (res.isAfterLast() == false) {
            String content = res.getString(res.getColumnIndex(KEY_CONTENT)) + " : " +
                    res.getString(res.getColumnIndex(SMS_COLUMN_DATE));
            array_list.add(content);
            res.moveToNext();
        }
        return array_list;
    }
}