SQLiteHandler

package filipkarol.zadania.helper;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import java.io.IOException;
import java.util.HashMap;

public class SQLiteHandler extends SQLiteOpenHelper {

    private static final String TAG = SQLiteHandler.class.getSimpleName();

    // All Static variables
    // Database Version
    private static final int DATABASE_VERSION = 1;

    // Database Name
    private static final String DATABASE_NAME = "filipkarol_android_bd";// androidh_bdapp

    // Login table name
    private static final String TABLE_USER = "user";

    // Login Table Columns names
    private static final String KEY_ID = "id";
    private static final String KEY_NAME = "name";
    private static final String KEY_EMAIL = "email";
    private static final String KEY_UID = "uid";
    private static final String KEY_CREATED_AT = "created_at";


    private static final String TABLE_PERSON = "Person";

    private static final String KEY_PERSON_ID = "id";
    private static final String KEY_PERSON_EMAIL = "email";
    private static final String KEY_PERSON_NAME = "name";
    private static final String KEY_PERSON_SURNAME = "surname";


    private static final String TABLE_ACTION = "Action";

    private static final String KEY_ACTION_ID = "id";
    private static final String KEY_ACTION_NAME = "name";
    private static final String KEY_ACTION_ISDONE = "isDone";
    private static final String KEY_ACTION_TYPE = "type";
    private static final String KEY_ACTION_EXPECTEDTIME = "expectedTime";
    private static final String KEY_ACTION_IDGROUP = "idGroup";


    private static final String TABLE_PERSONACTION = "PersonAction";

    private static final String KEY_PERSONACTION_ID = "id";
    private static final String KEY_PERSONACTION_IDPERSON = "idPerson";
    private static final String KEY_PERSONACTION_IDACTION = "idAction";
    private static final String KEY_PERSONACTION_DATEFROM = "dateFrom";
    private static final String KEY_PERSONACTION_DATETO = "dateTo";

    private static final String TABLE_ACTIONDETAILS = "ActionDetails";

    private static final String KEY_ACTIONDETAILS_ID = "id";
    private static final String KEY_ACTIONDETAILS_IDPLACE = "idPlace";
    private static final String KEY_ACTIONDETAILS_DESCR = "descr";
    private static final String KEY_ACTIONDETAILS_DATEFROM = "dateFrom";
    private static final String KEY_ACTIONDETAILS_DATETO = "dateTo";

    private static final String TABLE_PLACE = "Place";

    private static final String KEY_PLACE_ID = "id";
    private static final String KEY_PLACE_NAME = "name";
    private static final String KEY_PLACE_ADDRESS = "address";
    private static final String KEY_PLACE_LAT = "lat";
    private static final String KEY_PLACE_LNG = "lng";
    private static final String KEY_PLACE_TYPE = "type";

    private static final String TABLE_GROUP = "Group";

    private static final String KEY_GROUP_ID = "id";
    private static final String KEY_GROUP_NAME ="name";

    private static final String TABLE_PERSONGROUP = "PersonGroup";

    private static final String KEY_PERSONGROUP_ID = "id";
    private static final String KEY_PERSONGROUP_IDPERSON = "idPerson";
    private static final String KEY_PERSONGROUP_IDGROUP = "idGroup";


    public SQLiteHandler(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    // Creating Tables
    @Override
    public void onCreate(SQLiteDatabase db) {

        String CREATE_LOGIN_TABLE = "CREATE TABLE " + TABLE_USER + "("
                + KEY_ID +          " INTEGER PRIMARY KEY,"
                + KEY_NAME +        " TEXT,"
                + KEY_EMAIL +       " TEXT UNIQUE,"
                + KEY_UID +         " TEXT,"
                + KEY_CREATED_AT +  " TEXT"
                + ")";
        db.execSQL(CREATE_LOGIN_TABLE); // Ka:w sumie to można wywalic tą tabelkę


        String CREATE_PERSON_TABLE = "CREATE TABLE "
                + TABLE_PERSON + "("
                + KEY_PERSON_ID +    " INTEGER PRIMARY KEY,"
                + KEY_PERSON_EMAIL + " TEXT UNIQUE,"
                + KEY_PERSON_NAME +  " TEXT,"
                + KEY_PERSON_SURNAME +" TEXT"
                + ")";
        db.execSQL(CREATE_PERSON_TABLE );


        String CREATE_ACTION_TABLE = "CREATE TABLE "
                + TABLE_ACTION + "("
                + KEY_ACTION_ID +           " INTEGER PRIMARY KEY,"
                + KEY_ACTION_NAME +         " TEXT,"
                + KEY_ACTION_ISDONE +       " INTEGER,"
                + KEY_ACTION_TYPE +         " TEXT,"
                + KEY_ACTION_EXPECTEDTIME + " INTEGER,"  // Ka:time  - >  min
                + KEY_ACTION_IDGROUP +      " INTEGER "
                + ")";
        db.execSQL(CREATE_ACTION_TABLE);


        String CREATE_PERSONACTION_TABLE = "CREATE TABLE "
                + TABLE_PERSONACTION + "("
                + KEY_PERSONACTION_ID +       " INTEGER PRIMARY KEY,"
                + KEY_PERSONACTION_IDPERSON + " INTEGER, "
                + KEY_PERSONACTION_IDACTION + " INTEGER, "
                + KEY_PERSONACTION_DATEFROM + " DATETIME,"
                + KEY_PERSONACTION_DATETO +   " DATETIME"
                + ")";
        db.execSQL(CREATE_PERSONACTION_TABLE);


        String CREATE_ACTIONDETAILS_TABLE = "CREATE TABLE "
                + TABLE_ACTIONDETAILS + "("
                + KEY_ACTIONDETAILS_ID +        " INTEGER PRIMARY KEY,"
                + KEY_ACTIONDETAILS_IDPLACE +   " INTEGER, "
                + KEY_ACTIONDETAILS_DESCR +     " TEXT, "
                + KEY_ACTIONDETAILS_DATEFROM +  " DATETIME,"
                + KEY_ACTIONDETAILS_DATETO +    " DATETIME"
                + ")";
        db.execSQL(CREATE_ACTIONDETAILS_TABLE);


        String CREATE_PLACE_TABLE = "CREATE TABLE "
                + TABLE_PLACE+ "("
                + KEY_PLACE_ID +        " INTEGER PRIMARY KEY,"
                + KEY_PLACE_NAME +      " TEXT,"
                + KEY_PLACE_ADDRESS +   " TEXT,"
                + KEY_PLACE_LAT +       " REAL," // Ka:to real to niby na nasze float  http://www.sqlite.org/datatype3.html
                + KEY_PLACE_LNG +       " REAL,"
                + KEY_PLACE_TYPE +      " TEXT"
                + ")";
        db.execSQL(CREATE_PLACE_TABLE);


        String CREATE_GROUP_TABLE = "CREATE TABLE "
                + TABLE_GROUP+ "("
                + KEY_GROUP_ID +        " INTEGER PRIMARY KEY,"
                + KEY_GROUP_NAME +      " TEXT"
                + ")";
        db.execSQL(CREATE_GROUP_TABLE);


        String CREATE_PERSONGROUP_TABLE = "CREATE TABLE "
                + TABLE_PERSONGROUP+ "("
                + KEY_PERSONGROUP_ID +      " INTEGER PRIMARY KEY,"
                + KEY_PERSONGROUP_IDGROUP + " INTEGER, "
                + KEY_PERSONGROUP_IDPERSON +" INTEGER"
                + ")";
        db.execSQL(CREATE_PERSONGROUP_TABLE);


        Log.d(TAG, "Database tables created");
    }

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

        // Ka:tutaj se zrobimy jak będzie Upgrade

        // Create tables again
        onCreate(db);
    }

    /**
     * Storing user details in database
     * */
    public void addUser(String name, String email, String uid, String created_at) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_NAME, name); // Name
        values.put(KEY_EMAIL, email); // Email
        values.put(KEY_UID, uid); // Email
        values.put(KEY_CREATED_AT, created_at); // Created At

        // Inserting Row
        long id = db.insert(TABLE_USER, null, values);
        db.close(); // Closing database connection

        Log.d(TAG, "New user inserted into sqlite: " + id);
    }
    public void addPerson(String email, String name, String surname ) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_PERSON_EMAIL, email);
        values.put(KEY_PERSON_NAME, name);
        values.put(KEY_PERSON_SURNAME, surname);

        long id = db.insert(TABLE_PERSON, null, values);
        db.close();

        Log.d(TAG, "New Person inserted into sqlite: " + id);
    }
    public void addAction(String name, int isdone, String type, int expectedtime, int idgroup) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_ACTION_NAME, name);
        values.put(KEY_ACTION_ISDONE, isdone);
        values.put(KEY_ACTION_TYPE, type);
        values.put(KEY_ACTION_EXPECTEDTIME, expectedtime);
        values.put(KEY_ACTION_IDGROUP, idgroup);

        long id = db.insert(TABLE_ACTION, null, values);
        db.close();

        Log.d(TAG, "New Action inserted into sqlite: " + id);
    }
    // add PersonACTION hmmm
    public void addActionDetails( int idplace, String descr,String datefrom, String dateto) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_ACTIONDETAILS_IDPLACE, idplace );
        values.put(KEY_ACTIONDETAILS_DESCR, descr );
        values.put(KEY_ACTIONDETAILS_DATEFROM, datefrom );
        values.put(KEY_ACTIONDETAILS_DATETO, dateto );


        long id = db.insert(TABLE_ACTIONDETAILS, null, values);
        db.close();

        Log.d(TAG, "New ACTIONDETAILS inserted into sqlite: " + id);
    }
    public void addPlace( String name,String address,float lat, float lng, String type) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_PLACE_NAME, name);
        values.put(KEY_PLACE_ADDRESS, address);
        values.put(KEY_PLACE_LAT, lat);
        values.put(KEY_PLACE_LNG, lng);
        values.put(KEY_PLACE_TYPE, type);

        long id = db.insert(TABLE_PLACE, null, values);
        db.close();

        Log.d(TAG, "New PLACE inserted into sqlite: " + id);
    }
    public void addGroup( String name) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_GROUP_NAME, name);
        long id = db.insert(TABLE_GROUP, null, values);
        db.close();

        Log.d(TAG, "New GROUP inserted into sqlite: " + id);
    }


    public void addPersonToGroup(String email, String nameGroup) {
        int idGroup = 0;
        int idPerson = 0;

        SQLiteDatabase db1 = this.getReadableDatabase();

        Cursor cursor = db1.query(
                TABLE_PERSON,                            // The table to query
                new String[]{KEY_PERSON_ID},            // The columns to return
                "email = " + email,                    // The columns for the WHERE clause
                null,                                    // The values for the WHERE clause
                null,                                   // don't group the rows
                null,                                    // don't filter by row groups
                null                                    // The sort order
        );  // http://stackoverflow.com/questions/1243199/how-to-perform-an-sqlite-query-within-an-android-application
        // http://developer.android.com/training/basics/data-storage/databases.html#ReadDbRow

        if (cursor != null && cursor.moveToFirst()) { // http://www.android4devs.pl/2011/07/sqlite-androidzie-kompletny-poradnik-dla-poczatkujacych/
            idPerson = Integer.parseInt(cursor.getString(cursor.getColumnIndex(KEY_PERSON_ID)));
        }


        Cursor cursor2 = db1.query(
                TABLE_GROUP,                            // The table to query
                new String[]{KEY_GROUP_ID},            // The columns to return
                "name = " + nameGroup,                    // The columns for the WHERE clause
                null,                                    // The values for the WHERE clause
                null,                                   // don't group the rows
                null,                                    // don't filter by row groups
                null                                    // The sort order
        );  // http://stackoverflow.com/questions/1243199/how-to-perform-an-sqlite-query-within-an-android-application
        // http://developer.android.com/training/basics/data-storage/databases.html#ReadDbRow

        if (cursor != null && cursor.moveToFirst()) { // http://www.android4devs.pl/2011/07/sqlite-androidzie-kompletny-poradnik-dla-poczatkujacych/
            idGroup = Integer.parseInt(cursor.getString(cursor.getColumnIndex(KEY_GROUP_ID)));
        }

        if (idPerson == 0 || idGroup == 0) {
            Log.e(TAG, "addPersonToGroup  idPerson, idGroup " + idPerson + " " + idGroup );
            return;
        }

        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_PERSONGROUP_IDGROUP, idGroup);
        values.put(KEY_PERSONGROUP_IDPERSON, idPerson);

        long id = db.insert(TABLE_PERSONGROUP, null, values);
        db.close();

        Log.d(TAG, "New PERSONGROUP inserted into sqlite: " + id);

    }

    /**
     * Getting user data from database
     * */
    public HashMap<String, String> getUserDetails() {
        HashMap<String, String> user = new HashMap<String, String>();
        String selectQuery = "SELECT  * FROM " + TABLE_USER;

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        // Move to first row
        cursor.moveToFirst();
        if (cursor.getCount() > 0) {
            user.put("name", cursor.getString(1));
            user.put("email", cursor.getString(2));
            user.put("uid", cursor.getString(3));
            user.put("created_at", cursor.getString(4));
        }
        cursor.close();
        db.close();
        // return user
        Log.d(TAG, "Fetching user from Sqlite: " + user.toString());

        return user;
    }

    /**
     * Re crate database Delete all tables and create them again
     * */
    public void deleteUsers() {
        SQLiteDatabase db = this.getWritableDatabase();
        // Delete All Rows
        db.delete(TABLE_USER, null, null);
        db.close();

        Log.d(TAG, "Deleted all user info from sqlite");
    }

}

/**
 * Autor Karol
 * Thanks Ravi Tamada
 * URL: www.androidhive.info
 * twitter: http://twitter.com/ravitamada
 * */