public void logout(String token) {
String selectQuery =
"SELECT * FROM " + TABLE_USER + " WHERE " + KEY_TOKEN + " = '" + token + "'";
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
User user = new User();
user.setUserId(Integer.parseInt(cursor.getString(0)));
ContentValues values = new ContentValues();
values.put(KEY_TOKEN, "");
// updating row
db.update(
TABLE_USER,
values,
KEY_USER_ID + " = ?",
new String[] {String.valueOf(user.getUserId())});
}
cursor.close();
}
public List<User> getAllUser() {
List<User> userList = new ArrayList<User>();
// Select All Query
String selectQuery = "SELECT * FROM " + TABLE_USER;
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
User user = new User();
user.setPassword(cursor.getString(0));
user.setToken(cursor.getString(1));
user.setUserId(Integer.parseInt(cursor.getString(2)));
user.setUsername(cursor.getString(3));
user.setDateCreated(cursor.getString(4));
user.setFirstName(cursor.getString(5));
user.setLastName(cursor.getString(6));
user.setGender(cursor.getString(7));
// Adding user to list
userList.add(user);
} while (cursor.moveToNext());
}
// return user list
return userList;
}
void revokeUserRole(User user, String role) {
SQLiteDatabase db = this.getWritableDatabase();
int role_id = 0;
String selectQuery =
"SELECT "
+ KEY_ROLE_ID
+ " FROM "
+ TABLE_ROLES
+ " WHERE "
+ KEY_ROLE
+ " = '"
+ role
+ "'";
Cursor cursor = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
role_id = Integer.parseInt(cursor.getString(0));
}
if (role_id > 0) {
// Delete Row
db.delete(
TABLE_USER_ROLES,
KEY_USER_ID + " = ? AND " + KEY_ROLE_ID + " = ?",
new String[] {String.valueOf(user.getUserId()), String.valueOf(role_id)});
}
db.close();
}
void addUserRole(User user, String role) {
SQLiteDatabase db = this.getWritableDatabase();
int role_id = 0;
String selectQuery =
"SELECT "
+ KEY_ROLE_ID
+ " FROM "
+ TABLE_ROLES
+ " WHERE "
+ KEY_ROLE
+ " = '"
+ role
+ "'";
Cursor cursor = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
role_id = Integer.parseInt(cursor.getString(0));
}
ContentValues values = new ContentValues();
values.put(KEY_USER_ID, user.getUserId());
values.put(KEY_ROLE_ID, role_id);
// Insert Row
db.insert(TABLE_USER_ROLES, null, values);
db.close();
}
public int updateUser(User user) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_PASSWORD, user.getPassword());
values.put(KEY_TOKEN, user.getToken());
values.put(KEY_USER_ID, user.getUserId());
values.put(KEY_USERNAME, user.getUsername());
values.put(KEY_DATE_CREATED, user.getDateCreated());
values.put(KEY_FIRST_NAME, user.getFirstName());
values.put(KEY_LAST_NAME, user.getLastName());
values.put(KEY_GENDER, user.getGender());
values.put(KEY_USER_STATUS, user.getStatus());
// updating row
return db.update(
TABLE_USER, values, KEY_USER_ID + " = ?", new String[] {String.valueOf(user.getUserId())});
}
// Getting All Users
public List<User> getAllUsers(int page) {
List<User> userList = new ArrayList<User>();
// Select All Query
// String selectQuery = "SELECT * FROM " + TABLE_USER;
SQLiteDatabase db = this.getWritableDatabase();
// Cursor cursor = db.rawQuery(selectQuery, null);
Cursor cursor =
db.query(
TABLE_USER,
null,
null,
null,
null,
null,
null,
((page - 1) * PAGE_SIZE) + ", " + PAGE_SIZE);
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
User user = new User();
user.setUserId(Integer.parseInt(cursor.getString(0)));
user.setUsername(cursor.getString(1));
user.setPassword(cursor.getString(2));
user.setToken(cursor.getString(3));
user.setFirstName(cursor.getString(5));
user.setLastName(cursor.getString(6));
user.setGender(cursor.getString(7));
user.setStatus(cursor.getString(8));
// Adding person to list
userList.add(user);
} while (cursor.moveToNext());
}
cursor.close();
// return person list
return userList;
}
void addUser(User user) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_PASSWORD, user.getPassword());
values.put(KEY_TOKEN, user.getToken());
values.put(KEY_USERNAME, user.getUsername());
values.put(KEY_DATE_CREATED, user.getDateCreated());
values.put(KEY_FIRST_NAME, user.getFirstName());
values.put(KEY_LAST_NAME, user.getLastName());
values.put(KEY_GENDER, user.getGender());
values.put(KEY_USER_STATUS, user.getStatus());
// Insert Row
db.insert(TABLE_USER, null, values);
db.close();
}
public void deleteUser(User user) {
SQLiteDatabase db = this.getWritableDatabase();
db.delete(TABLE_USER, KEY_USER_ID + " = ?", new String[] {String.valueOf(user.getUserId())});
db.close();
}
/** All CRUD(Create, Read, Update, Delete) Operations */
public String login(String username, String password) {
AeSimpleSHA1 sha = new AeSimpleSHA1();
String passHash = "";
try {
passHash = sha.SHA1(password);
} catch (NoSuchAlgorithmException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
String selectQuery =
"SELECT * FROM "
+ TABLE_USER
+ " WHERE "
+ KEY_USERNAME
+ " = '"
+ username
+ "' AND "
+ KEY_PASSWORD
+ " = '"
+ passHash
+ "'";
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
char[] chars = "abcdefghijklmnopqrstuvwxyz0123456789".toCharArray();
StringBuilder sb = new StringBuilder();
Random random = new Random();
for (int i = 0; i < 20; i++) {
char c = chars[random.nextInt(chars.length)];
sb.append(c);
}
String output = sb.toString();
User user = new User();
user.setUserId(Integer.parseInt(cursor.getString(0)));
ContentValues values = new ContentValues();
values.put(KEY_TOKEN, output);
// updating row
db.update(
TABLE_USER,
values,
KEY_USER_ID + " = ?",
new String[] {String.valueOf(user.getUserId())});
mCurrentUserId = user.getUserId();
cursor.close();
return output;
} else {
cursor.close();
return "";
}
}