All About Database Handler - Part 1

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 = "database_name.db";

// user table name
private static final String TABLE_DATA = "User_Details";
private static final String TABLE_POINTS = "Points_Details";

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

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

String CREATE_DATA_TABLE = "CREATE TABLE IF NOT EXISTS "
+ TABLE_DATA
+ "(UserName TEXT, Email TEXT, Password TEXT, BirthDate TEXT)";
db.execSQL(CREATE_DATA_TABLE);

String CREATE_POINTS_TABLE = "CREATE TABLE IF NOT EXISTS "
+ TABLE_POINTS
+ "(point INTEGER, [date] DATETIME, type STRING)";
db.execSQL(CREATE_POINTS_TABLE);


}

// Upgrading database
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + TABLE_DATA);
db.execSQL("DROP TABLE IF EXISTS " + TABLE_POINTS);
   onCreate(db);
}

void delete() {
SQLiteDatabase db = this.getReadableDatabase();
db.execSQL("DROP TABLE IF EXISTS " + TABLE_DATA);
onCreate(db);
db.close();
}

boolean checkIfExist() {
SQLiteDatabase db = this.getReadableDatabase();

Cursor c = db.rawQuery("SELECT Email FROM User_Details", null);

if (c.moveToFirst()) {
return true;
} else {
return false;
}
}

/**
* All CRUD(Create, Read, Update, Delete) Operations
*/
// Adding user details
void addData(String UserName, String Email, String Password,
String BirthDate) {
SQLiteDatabase db = this.getWritableDatabase();

ContentValues values = new ContentValues();
values.put("UserName", UserName);
values.put("Email", Email);
values.put("Password", Password);
values.put("BirthDate", BirthDate);

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

public void deleteData() {
// db.delete(String tableName, String whereClause, String[] whereArgs);
// If whereClause is null, it will delete all rows.
SQLiteDatabase db = this.getWritableDatabase(); // helper is object
// extends
// SQLiteOpenHelper
db.delete(TABLE_DATA, null, null);
db.delete(TABLE_POINTS, null, null);
}

// Updating
public int updateUserDetails(String UserName, String Email,
String BirthDate) {
SQLiteDatabase db = this.getWritableDatabase();

ContentValues values = new ContentValues();
values.put("UserName", UserName);
values.put("Email", Email);
values.put("Password", Password);
values.put("BirthDate", BirthDate);

// updating row
return db.update(TABLE_DATA, values, "Email = ?",
new String[] { String.valueOf(Email) });
}

// public int updateUserPassword(String Password, String Email) {
// SQLiteDatabase db = this.getWritableDatabase();
//
// ContentValues values = new ContentValues();
// values.put("Password", Password);
//
// // updating row
// return db.update(TABLE_DATA, values, "Email = ?",
// new String[] { String.valueOf(Email) });
// }

// Updating
public void updatePoints(String date, int point, String type) {

SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.query(TABLE_POINTS, new String[] { "point", "date",
"type" }, "type = ?  AND date = ?",
new String[] { String.valueOf(type), String.valueOf(date) },
null, null, null);
Log.i("cursorrrrrrrrrrr", "" + cursor.getCount());
if (cursor.getCount() != 0) {
int actualPoint = 0;
if (cursor.moveToFirst()) {
do {
actualPoint = cursor.getInt(0);
} while (cursor.moveToNext());
}
cursor.close();

ContentValues values = new ContentValues();

values.put("point", actualPoint + point);

// Inserting Row
db.update(TABLE_POINTS, values, "type = ?  AND date = ?",
new String[] { String.valueOf(type), String.valueOf(date) });

} else {

ContentValues values = new ContentValues();

values.put("point", point);
values.put("date", date);
values.put("type", type);
// Inserting Row
db.insert(TABLE_POINTS, null, values);
}

db.close();

}

// Getting all data
public int getTodayPoints(String date) {
int points = 0;
SQLiteDatabase db = this.getReadableDatabase();

Cursor cursor = db.query(TABLE_POINTS,
new String[] { "point", "date" }, "date" + "=?",
new String[] { String.valueOf(date) }, null, null, null, null);
if (cursor != null) {

if (cursor.moveToFirst()) {
do {
points = cursor.getInt(0);
} while (cursor.moveToNext());
}

}

db.close();
return points;
}

// Getting all data
public Cursor getData() {

SQLiteDatabase db = this.getReadableDatabase();

Cursor cursor = db.query(TABLE_DATA, new String[] { "UserName",
"Email", "Password", "BirthDate" }, null, null, null,
null, null, null);
// cursor.close();
// db.close();
return cursor;
}

public Cursor getAllPoints() {
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.query(TABLE_POINTS, new String[] { "point", "date",
"type" }, null, null, null, null, null, null);

return cursor;
}


@SuppressLint("SimpleDateFormat")
public int getMonthlyPoints(int total_Point) {

//this will show points of add and referal type only
total_Point = 0;
int st_addPoint = 0;
int mYear, mMonth, mDay;
String date1, date2;
final Calendar c = Calendar.getInstance();
c.add(Calendar.DAY_OF_YEAR, +1);
mYear = c.get(Calendar.YEAR);
mMonth = c.get(Calendar.MONTH);
mDay = c.get(Calendar.DAY_OF_MONTH);

final Calendar c1 = Calendar.getInstance();
c1.add(Calendar.DAY_OF_YEAR, -(mDay + 1));
Integer md1 = c1.get(Calendar.DAY_OF_MONTH);
Integer m1 = c1.get(Calendar.MONTH);
Integer y1 = c1.get(Calendar.YEAR);
date1 = y1 + "-" + (m1 + 1) + "-" + md1;
date2 = mYear + "-" + (mMonth + 1) + "-" + mDay;
SQLiteDatabase db = this.getReadableDatabase();


Cursor cursor_add = db.query(TABLE_POINTS, new String[] { "point", "date",
"type" }, "type like 'ADD'", null, null, null, null);
// Cursor cursor = db.query(TABLE_POINTS,
// new String[] { "point", "date" }, null, null, null, null, null);
if (cursor_add.moveToFirst()) {
do {
String str_date = cursor_add.getString(1);
SimpleDateFormat formatter;
Date date, datefr, datels;
formatter = new SimpleDateFormat("yyyy-MM-dd");
try {
date = formatter.parse(str_date);
datefr = formatter.parse(date1);
datels = formatter.parse(date2);
if (date.after(datefr) && date.before(datels)) {

st_addPoint = cursor_add.getInt(0) + st_addPoint;

}
} catch (ParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

catch (java.text.ParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} while (cursor_add.moveToNext());
}
if (cursor_add != null && !cursor_add.isClosed()) {
cursor_add.close();
}
}
}

to be continued..