DatabaseHelper.java
public class DatabaseHelper extends SQLiteOpenHelper {
private static DatabaseHelper instance;
private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = "didoi.db";
public static final String TABLE_USERS = "tb_users";
public static final String TABLE_CHATS = "tb_chats";
public static final String TABLE_MESSAGES = "tb_messages";
public static final String COLUMN_ID = "id";
public static final String COLUMN_NAME = "name";
public static final String COLUMN_PROFILE_PIC_URL = "profilePicUrl";
public static final String COLUMN_MESSAGE_ID = "messageId";
public static final String COLUMN_MESSAGE_BODY = "messageBody";
public static final String COLUMN_TYPE = "type";
public static final String COLUMN_ATTACHMENT_URL = "attachmentUrl";
public static final String COLUMN_THUMB_URL = "thumbUrl";
public static final String COLUMN_SENDER_ID = "senderId";
public static final String COLUMN_RECEIVER_ID = "receiverId";
public static final String COLUMN_TIMER = "timer";
public static final String COLUMN_TIMESTAMP = "timestamp";
public static final String COLUMN_LAST_DELETE_TIMESTAMP = "lastDeleteTimestamp";
public static final String COLUMN_UNREAD_COUNT = "unreadCount";
public static final String COLUMN_LOCAL_PATH = "localPath";
public static final String COLUMN_TIMER_EXPIRED = "isExpired";
public static final String COLUMN_LATITUDE = "lat";
public static final String COLUMN_LONGITUDE = "lng";
public static synchronized DatabaseHelper getInstance(Context context) {
if (instance == null) {
instance = new DatabaseHelper(context);
}
return instance;
}
public DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
private String SQL_CREATE_TABLE_USERS = "CREATE TABLE IF NOT EXISTS " + TABLE_USERS + " (" + COLUMN_ID + " INTEGER PRIMARY KEY" + Constants.COMMA + COLUMN_NAME + " TEXT" + Constants.COMMA + COLUMN_PROFILE_PIC_URL + " TEXT" + ");";
private String SQL_CREATE_TABLE_CHATS = "CREATE TABLE IF NOT EXISTS " + TABLE_CHATS + " (" + COLUMN_ID + " INTEGER PRIMARY KEY" + Constants.COMMA + COLUMN_MESSAGE_ID + " TEXT" + Constants.COMMA + COLUMN_MESSAGE_BODY + " TEXT" + Constants.COMMA + COLUMN_TYPE + " INTEGER" + Constants.COMMA + COLUMN_ATTACHMENT_URL + " TEXT" + Constants.COMMA + COLUMN_THUMB_URL + " TEXT" + Constants.COMMA + COLUMN_SENDER_ID + " INTEGER NOT NULL" + Constants.COMMA + COLUMN_RECEIVER_ID + " INTEGER NOT NULL" + Constants.COMMA + COLUMN_TIMER + " INTEGER DEFAULT 0" + Constants.COMMA + COLUMN_TIMESTAMP + " INTEGER" + Constants.COMMA + COLUMN_LAST_DELETE_TIMESTAMP + " INTEGER" + Constants.COMMA + COLUMN_LATITUDE + " INTEGER" + Constants.COMMA + COLUMN_LONGITUDE + " INTEGER" + Constants.COMMA + COLUMN_UNREAD_COUNT + " INTEGER" + ");";
private String SQL_CREATE_TABLE_MESSAGES = "CREATE TABLE IF NOT EXISTS " + TABLE_MESSAGES + " (" + COLUMN_ID + " TEXT NOT NULL UNIQUE" + Constants.COMMA + COLUMN_MESSAGE_BODY + " TEXT" + Constants.COMMA + COLUMN_TYPE + " INTEGER" + Constants.COMMA + COLUMN_SENDER_ID + " INTEGER NOT NULL" + Constants.COMMA + COLUMN_RECEIVER_ID + " INTEGER NOT NULL" + Constants.COMMA + COLUMN_ATTACHMENT_URL + " TEXT" + Constants.COMMA + COLUMN_THUMB_URL + " TEXT" + Constants.COMMA + COLUMN_LOCAL_PATH + " TEXT" + Constants.COMMA + COLUMN_TIMER + " INTEGER" + Constants.COMMA + COLUMN_TIMESTAMP + " INTEGER" + Constants.COMMA + COLUMN_TIMER_EXPIRED + " INTEGER DEFAULT 0" + Constants.COMMA + COLUMN_LATITUDE + " INTEGER" + Constants.COMMA + COLUMN_LONGITUDE + " INTEGER" + ");";
@Override public void onCreate(SQLiteDatabase db) {
db.execSQL(SQL_CREATE_TABLE_USERS);
db.execSQL(SQL_CREATE_TABLE_CHATS);
db.execSQL(SQL_CREATE_TABLE_MESSAGES);
}
@Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + TABLE_USERS);
db.execSQL("DROP TABLE IF EXISTS " + TABLE_CHATS);
db.execSQL("DROP TABLE IF EXISTS " + TABLE_MESSAGES);
}
}
DatabaseManager.java
public class DatabaseManager {
public static DatabaseManager sInstance;
private static DatabaseHelper mDatabaseHelper;
private int mOpenCounter;
private SQLiteDatabase mDatabase;
public static synchronized DatabaseManager getInstance(Context context) {
if (sInstance == null) {
sInstance = new DatabaseManager();
mDatabaseHelper = DatabaseHelper.getInstance(context);
}
if (mDatabaseHelper == null) {
mDatabaseHelper = DatabaseHelper.getInstance(context);
}
return sInstance;
}
public static DatabaseHelper getmDatabaseHelper() {
return mDatabaseHelper;
}
public synchronized SQLiteDatabase openDatabase() {
mOpenCounter++;
if (mOpenCounter == 1) {
// Opening new database mDatabase = mDatabaseHelper.getWritableDatabase();
}
return mDatabase;
}
public synchronized void closeDatabase() {
mOpenCounter--;
if (mOpenCounter == 0) {
// Closing database mDatabase.close();
}
}
public void insertUser(User user) {
ContentValues values = new ContentValues();
values.put(DatabaseHelper.COLUMN_ID, user.userId);
values.put(DatabaseHelper.COLUMN_NAME, user.name);
values.put(DatabaseHelper.COLUMN_PROFILE_PIC_URL, user.profileImageUrl);
mDatabase.insertWithOnConflict(DatabaseHelper.TABLE_USERS, null, values, SQLiteDatabase.CONFLICT_REPLACE);
}
public void insertChat(Chat chat) {
ContentValues values = new ContentValues();
values.put(DatabaseHelper.COLUMN_ID, chat.userId);
values.put(DatabaseHelper.COLUMN_MESSAGE_ID, chat.messageId);
values.put(DatabaseHelper.COLUMN_MESSAGE_BODY, chat.messageBody);
values.put(DatabaseHelper.COLUMN_TYPE, chat.type);
values.put(DatabaseHelper.COLUMN_ATTACHMENT_URL, chat.attachmentUrl);
values.put(DatabaseHelper.COLUMN_THUMB_URL, chat.thumbUrl);
values.put(DatabaseHelper.COLUMN_SENDER_ID, chat.senderId);
values.put(DatabaseHelper.COLUMN_RECEIVER_ID, chat.receiverId);
values.put(DatabaseHelper.COLUMN_TIMER, chat.timer);
values.put(DatabaseHelper.COLUMN_TIMESTAMP, chat.getTimeStampLong());
values.put(DatabaseHelper.COLUMN_LAST_DELETE_TIMESTAMP, chat.getLastDeleteTimeStampLong());
values.put(DatabaseHelper.COLUMN_LATITUDE, chat.lat);
values.put(DatabaseHelper.COLUMN_LONGITUDE, chat.lng);
values.put(DatabaseHelper.COLUMN_UNREAD_COUNT, chat.unreadCount);
mDatabase.insertWithOnConflict(DatabaseHelper.TABLE_CHATS, null, values, SQLiteDatabase.CONFLICT_REPLACE);
}
public ArrayList<Chat> getChats() {
ArrayList<Chat> chatList = new ArrayList<>();
String query = "SELECT c.*, u." + DatabaseHelper.COLUMN_NAME + Constants.COMMA + "u." + DatabaseHelper.COLUMN_PROFILE_PIC_URL + " FROM " + DatabaseHelper.TABLE_CHATS + " as c LEFT JOIN " + DatabaseHelper.TABLE_USERS + " as u ON c." + DatabaseHelper.COLUMN_ID + " = u." + DatabaseHelper.COLUMN_ID + " ORDER BY c." + DatabaseHelper.COLUMN_TIMESTAMP + " DESC;";
Cursor cursor = mDatabase.rawQuery(query, null);
if (cursor != null) {
if (cursor.moveToFirst()) {
do {
Chat chat = new Chat();
chat.userId = cursor.getInt(cursor.getColumnIndex(DatabaseHelper.COLUMN_ID));
chat.userName = cursor.getString(cursor.getColumnIndex(DatabaseHelper.COLUMN_NAME));
chat.userProfilePicUrl = cursor.getString(cursor.getColumnIndex(DatabaseHelper.COLUMN_PROFILE_PIC_URL));
chat.messageId = cursor.getString(cursor.getColumnIndex(DatabaseHelper.COLUMN_MESSAGE_ID));
chat.type = cursor.getInt(cursor.getColumnIndex(DatabaseHelper.COLUMN_TYPE));
chat.messageBody = cursor.getString(cursor.getColumnIndex(DatabaseHelper.COLUMN_MESSAGE_BODY));
chat.attachmentUrl = cursor.getString(cursor.getColumnIndex(DatabaseHelper.COLUMN_ATTACHMENT_URL));
chat.thumbUrl = cursor.getString(cursor.getColumnIndex(DatabaseHelper.COLUMN_THUMB_URL));
chat.senderId = cursor.getInt(cursor.getColumnIndex(DatabaseHelper.COLUMN_SENDER_ID));
chat.timer = cursor.getInt(cursor.getColumnIndex(DatabaseHelper.COLUMN_TIMER));
chat.timeStamp = cursor.getInt(cursor.getColumnIndex(DatabaseHelper.COLUMN_TIMESTAMP));
chat.lastDeleteTimeStamp = cursor.getInt(cursor.getColumnIndex(DatabaseHelper.COLUMN_LAST_DELETE_TIMESTAMP));
chat.lat = cursor.getDouble(cursor.getColumnIndex(DatabaseHelper.COLUMN_LATITUDE));
chat.lng = cursor.getDouble(cursor.getColumnIndex(DatabaseHelper.COLUMN_LONGITUDE));
chat.unreadCount = cursor.getInt(cursor.getColumnIndex(DatabaseHelper.COLUMN_UNREAD_COUNT));
chatList.add(chat);
} while (cursor.moveToNext());
}
cursor.close();
}
return chatList;
}
public Chat getChatWithId(int uid) {
Chat chat = null;
String query = "SELECT c.*, u." + DatabaseHelper.COLUMN_NAME + Constants.COMMA + "u." + DatabaseHelper.COLUMN_PROFILE_PIC_URL + " FROM " + DatabaseHelper.TABLE_CHATS + " as c LEFT JOIN " + DatabaseHelper.TABLE_USERS + " as u ON c." + DatabaseHelper.COLUMN_ID + " = u." + DatabaseHelper.COLUMN_ID + " WHERE c." + DatabaseHelper.COLUMN_ID + " = " + uid + ";";
Cursor cursor = mDatabase.rawQuery(query, null);
if (cursor != null) {
if (cursor.moveToFirst()) {
chat = new Chat();
chat.userId = cursor.getInt(cursor.getColumnIndex(DatabaseHelper.COLUMN_ID));
chat.userName = cursor.getString(cursor.getColumnIndex(DatabaseHelper.COLUMN_NAME));
chat.userProfilePicUrl = cursor.getString(cursor.getColumnIndex(DatabaseHelper.COLUMN_PROFILE_PIC_URL));
chat.messageId = cursor.getString(cursor.getColumnIndex(DatabaseHelper.COLUMN_MESSAGE_ID));
chat.type = cursor.getInt(cursor.getColumnIndex(DatabaseHelper.COLUMN_TYPE));
chat.messageBody = cursor.getString(cursor.getColumnIndex(DatabaseHelper.COLUMN_MESSAGE_BODY));
chat.attachmentUrl = cursor.getString(cursor.getColumnIndex(DatabaseHelper.COLUMN_ATTACHMENT_URL));
chat.thumbUrl = cursor.getString(cursor.getColumnIndex(DatabaseHelper.COLUMN_THUMB_URL));
chat.receiverId = cursor.getInt(cursor.getColumnIndex(DatabaseHelper.COLUMN_RECEIVER_ID));
chat.senderId = cursor.getInt(cursor.getColumnIndex(DatabaseHelper.COLUMN_SENDER_ID));
chat.timer = cursor.getInt(cursor.getColumnIndex(DatabaseHelper.COLUMN_TIMER));
chat.timeStamp = cursor.getInt(cursor.getColumnIndex(DatabaseHelper.COLUMN_TIMESTAMP));
chat.lastDeleteTimeStamp = cursor.getInt(cursor.getColumnIndex(DatabaseHelper.COLUMN_LAST_DELETE_TIMESTAMP));
chat.lat = cursor.getDouble(cursor.getColumnIndex(DatabaseHelper.COLUMN_LATITUDE));
chat.lng = cursor.getDouble(cursor.getColumnIndex(DatabaseHelper.COLUMN_LONGITUDE));
chat.unreadCount = cursor.getInt(cursor.getColumnIndex(DatabaseHelper.COLUMN_UNREAD_COUNT));
}
cursor.close();
}
return chat;
}
public User getUserWithId(int userId) {
User user = null;
Cursor cursor = mDatabase.query(DatabaseHelper.TABLE_USERS, null, DatabaseHelper.COLUMN_ID + " = " + userId, null, null, null, null);
if (cursor != null) {
if (cursor.moveToFirst()) {
user = new User();
user.userId = userId;
user.name = cursor.getString(cursor.getColumnIndex(DatabaseHelper.COLUMN_NAME));
user.profileImageUrl = cursor.getString(cursor.getColumnIndex(DatabaseHelper.COLUMN_PROFILE_PIC_URL));
}
cursor.close();
}
return user;
}
/** * @param message * @return -1 - operation fails, 1 - new insert, 2 - update */ public int insertMessage(Message message) {
if (message == null || message.id == null) return -1;
ContentValues values = new ContentValues();
values.put(DatabaseHelper.COLUMN_ID, message.id);
values.put(DatabaseHelper.COLUMN_MESSAGE_BODY, message.body);
values.put(DatabaseHelper.COLUMN_TYPE, message.type);
values.put(DatabaseHelper.COLUMN_SENDER_ID, message.senderId);
values.put(DatabaseHelper.COLUMN_RECEIVER_ID, message.receiverId);
values.put(DatabaseHelper.COLUMN_ATTACHMENT_URL, message.attachmentUrl);
values.put(DatabaseHelper.COLUMN_THUMB_URL, message.thumbUrl);
values.put(DatabaseHelper.COLUMN_LOCAL_PATH, message.attachmentLocalPath);
values.put(DatabaseHelper.COLUMN_TIMER, message.timer);
values.put(DatabaseHelper.COLUMN_TIMESTAMP, message.getTimeStampLong() != null ? message.getTimeStampLong() : 0);
values.put(DatabaseHelper.COLUMN_TIMER_EXPIRED, message.isExpired ? 1 : 0);
values.put(DatabaseHelper.COLUMN_LATITUDE, message.lat);
values.put(DatabaseHelper.COLUMN_LONGITUDE, message.lng);
if (doesMessageExist(message.id)) {
return mDatabase.update(DatabaseHelper.TABLE_MESSAGES, values, DatabaseHelper.COLUMN_ID + " = '" + message.id + "'", null) > 0 ? 2 : -1;
} else {
return mDatabase.insert(DatabaseHelper.TABLE_MESSAGES, null, values) > 0 ? 1 : -1;
}
}
public boolean doesMessageExist(String messageId) {
boolean doesMessageExist = false;
Cursor cursor = mDatabase.query(DatabaseHelper.TABLE_MESSAGES, new String[]{DatabaseHelper.COLUMN_ID}, DatabaseHelper.COLUMN_ID + " = '" + messageId + "'", null, null, null, null, null);
if (cursor != null) {
if (cursor.moveToFirst()) {
doesMessageExist = cursor.getString(cursor.getColumnIndex(DatabaseHelper.COLUMN_ID)) != null;
}
cursor.close();
}
return doesMessageExist;
}
public boolean doesUserExist(int userId) {
boolean doesUser = false;
Cursor cursor = mDatabase.query(DatabaseHelper.TABLE_USERS, new String[]{DatabaseHelper.COLUMN_ID}, DatabaseHelper.COLUMN_ID + " = " + userId, null, null, null, null, null);
if (cursor != null) {
if (cursor.moveToFirst()) {
doesUser = cursor.getString(cursor.getColumnIndex(DatabaseHelper.COLUMN_ID)) != null;
}
cursor.close();
}
return doesUser;
}
public long getLatestTimeStamp(int userId) {
long timestamp = 0;
String query = "SELECT MAX(" + DatabaseHelper.COLUMN_TIMESTAMP + ") as " + DatabaseHelper.COLUMN_TIMESTAMP + " FROM " + DatabaseHelper.TABLE_MESSAGES + " WHERE " + DatabaseHelper.COLUMN_SENDER_ID + " = " + userId + " OR " + DatabaseHelper.COLUMN_RECEIVER_ID + " = " + userId;
Cursor cursor = mDatabase.rawQuery(query, null);
if (cursor != null) {
if (cursor.moveToFirst()) {
timestamp = cursor.getLong(cursor.getColumnIndex(DatabaseHelper.COLUMN_TIMESTAMP));
}
cursor.close();
}
return timestamp;
}
public long getLastDeleteTimestamp(int userId) {
long timestamp = 0;
Cursor cursor = mDatabase.query(DatabaseHelper.TABLE_CHATS, new String[]{DatabaseHelper.COLUMN_LAST_DELETE_TIMESTAMP}, DatabaseHelper.COLUMN_ID + " = " + userId, null, null, null, null);
if (cursor != null) {
if (cursor.moveToFirst()) {
timestamp = cursor.getLong(cursor.getColumnIndex(DatabaseHelper.COLUMN_LAST_DELETE_TIMESTAMP));
}
cursor.close();
}
return timestamp;
}
public ArrayList<Message> getMessagesForChat(int userId, long timestampOffset, int limit) {
ArrayList<Message> messages = new ArrayList<>();
String query = "SELECT * FROM " + DatabaseHelper.TABLE_MESSAGES + " WHERE " + DatabaseHelper.COLUMN_TIMESTAMP + " > " + timestampOffset + " AND (" + DatabaseHelper.COLUMN_SENDER_ID + " = " + userId
+ " OR " + DatabaseHelper.COLUMN_RECEIVER_ID + " = " + userId + ")" + " ORDER BY " + DatabaseHelper.COLUMN_TIMESTAMP + " DESC LIMIT 0, " + limit + ";";
Cursor cursor = mDatabase.rawQuery(query, null);
if (cursor != null) {
if (cursor.moveToFirst()) {
do {
Message message = new Message();
message.id = cursor.getString(cursor.getColumnIndex(DatabaseHelper.COLUMN_ID));
message.body = cursor.getString(cursor.getColumnIndex(DatabaseHelper.COLUMN_MESSAGE_BODY));
message.type = cursor.getInt(cursor.getColumnIndex(DatabaseHelper.COLUMN_TYPE));
message.senderId = cursor.getInt(cursor.getColumnIndex(DatabaseHelper.COLUMN_SENDER_ID));
message.receiverId = cursor.getInt(cursor.getColumnIndex(DatabaseHelper.COLUMN_RECEIVER_ID));
message.attachmentUrl = cursor.getString(cursor.getColumnIndex(DatabaseHelper.COLUMN_ATTACHMENT_URL));
message.thumbUrl = cursor.getString(cursor.getColumnIndex(DatabaseHelper.COLUMN_THUMB_URL));
message.attachmentLocalPath = cursor.getString(cursor.getColumnIndex(DatabaseHelper.COLUMN_LOCAL_PATH));
message.timer = cursor.getInt(cursor.getColumnIndex(DatabaseHelper.COLUMN_TIMER));
message.timeStamp = cursor.getLong(cursor.getColumnIndex(DatabaseHelper.COLUMN_TIMESTAMP));
message.isExpired = cursor.getInt(cursor.getColumnIndex(DatabaseHelper.COLUMN_TIMER_EXPIRED)) == 1;
message.lat = cursor.getDouble(cursor.getColumnIndex(DatabaseHelper.COLUMN_LATITUDE));
message.lng = cursor.getDouble(cursor.getColumnIndex(DatabaseHelper.COLUMN_LONGITUDE));
message.mFormattedDate = DateTimeUtils.convertFromMsToLocalDateTime(message.getTimeStampLong());
messages.add(message);
} while (cursor.moveToNext());
}
cursor.close();
}
return messages;
}
public void markMessageAsExpired(String id) {
ContentValues values = new ContentValues();
values.put(DatabaseHelper.COLUMN_TIMER_EXPIRED, 1);
mDatabase.update(DatabaseHelper.TABLE_MESSAGES, values, DatabaseHelper.COLUMN_ID + " = '" + id + "'", null);
}
/** * delete all tables */
public void deleteAllTables() {
mDatabase.delete(DatabaseHelper.TABLE_MESSAGES, null, null);
mDatabase.delete(DatabaseHelper.TABLE_CHATS, null, null);
mDatabase.delete(DatabaseHelper.TABLE_USERS, null, null);
}
public void deleteChat(int chatId) {
mDatabase.delete(DatabaseHelper.TABLE_CHATS, DatabaseHelper.COLUMN_ID + " = " + chatId, null);
mDatabase.delete(DatabaseHelper.TABLE_MESSAGES, DatabaseHelper.COLUMN_SENDER_ID + " = " + chatId + " OR " + DatabaseHelper.COLUMN_RECEIVER_ID + " = " + chatId, null);
}
public boolean doesChatExist(int userId) {
boolean doesUser = false;
Cursor cursor = mDatabase.query(DatabaseHelper.TABLE_CHATS, new String[]{DatabaseHelper.COLUMN_ID}, DatabaseHelper.COLUMN_ID + " = '" + userId + "'", null, null, null, null, null);
if (cursor != null) {
if (cursor.moveToFirst()) {
doesUser = cursor.getString(cursor.getColumnIndex(DatabaseHelper.COLUMN_ID)) != null;
}
cursor.close();
}
return doesUser;
}
/** * @return Pair<Integer, ArrayList<Message>> where pair.first is total count of chats which have unreadMessages * and chat.second is ArrayList of unread messages of that chat */ public Pair<Integer, ArrayList<Message>> getUnreadMessageData() {
ArrayList<Message> unreadMessages = new ArrayList<>();
int conversationCount = 0;
/*int unreadCount = 0; int conversationCount = 0;*/
String query = "SELECT " + DatabaseHelper.COLUMN_UNREAD_COUNT + "," + DatabaseHelper.COLUMN_ID + " FROM " + DatabaseHelper.TABLE_CHATS + " WHERE " + DatabaseHelper.COLUMN_UNREAD_COUNT + " > 0 " + ";";
Cursor c = mDatabase.rawQuery(query, null);
if (c != null) {
if (c.moveToFirst()) {
do {
int uc = c.getInt(0);
int chatId = c.getInt(1);
conversationCount++;
String q = "SELECT m.*, u." + DatabaseHelper.COLUMN_NAME + " FROM " + DatabaseHelper.TABLE_MESSAGES + " as m LEFT JOIN " + DatabaseHelper.TABLE_USERS + " as u ON m." + DatabaseHelper.COLUMN_SENDER_ID + " = u." + DatabaseHelper.COLUMN_ID + " WHERE m." + DatabaseHelper.COLUMN_SENDER_ID + " = " + chatId + " ORDER BY m." + DatabaseHelper.COLUMN_TIMESTAMP + " DESC LIMIT 0," + uc + ";";
Cursor c1 = mDatabase.rawQuery(q, null);
if (c1 != null) {
if (c1.moveToFirst()) {
do {
Message message = new Message();
message.id = c1.getString(c1.getColumnIndex(DatabaseHelper.COLUMN_ID));
message.body = c1.getString(c1.getColumnIndex(DatabaseHelper.COLUMN_MESSAGE_BODY));
message.type = c1.getInt(c1.getColumnIndex(DatabaseHelper.COLUMN_TYPE));
message.senderId = c1.getInt(c1.getColumnIndex(DatabaseHelper.COLUMN_SENDER_ID));
message.receiverId = c1.getInt(c1.getColumnIndex(DatabaseHelper.COLUMN_RECEIVER_ID));
message.attachmentUrl = c1.getString(c1.getColumnIndex(DatabaseHelper.COLUMN_ATTACHMENT_URL));
message.thumbUrl = c1.getString(c1.getColumnIndex(DatabaseHelper.COLUMN_THUMB_URL));
message.attachmentLocalPath = c1.getString(c1.getColumnIndex(DatabaseHelper.COLUMN_LOCAL_PATH));
message.timer = c1.getInt(c1.getColumnIndex(DatabaseHelper.COLUMN_TIMER));
message.timeStamp = c1.getLong(c1.getColumnIndex(DatabaseHelper.COLUMN_TIMESTAMP));
message.isExpired = c1.getInt(c1.getColumnIndex(DatabaseHelper.COLUMN_TIMER_EXPIRED)) == 1;
message.lat = c1.getDouble(c1.getColumnIndex(DatabaseHelper.COLUMN_LATITUDE));
message.lng = c1.getDouble(c1.getColumnIndex(DatabaseHelper.COLUMN_LONGITUDE));
message.senderName = c1.getString(c1.getColumnIndex(DatabaseHelper.COLUMN_NAME));
message.mFormattedDate = DateTimeUtils.convertFromMsToLocalDateTime(message.getTimeStampLong());
unreadMessages.add(message);
} while (c1.moveToNext());
}
c1.close();
}
/*unreadCount += c.getInt(c.getColumnIndex(DatabaseHelper.COLUMN_UNREAD_COUNT)); conversationCount++;*/ } while (c.moveToNext());
}
c.close();
}
Collections.sort(unreadMessages, Collections.reverseOrder(new Comparator<Message>() {
@Override public int compare(Message m1, Message m2) {
long t1 = m1.getTimeStampLong() != null ? m1.getTimeStampLong().longValue() : 0;
long t2 = m2.getTimeStampLong() != null ? m2.getTimeStampLong().longValue() : 0;
if (t1 == t2) return 0;
else if (t1 > t2) return 1;
else return -1;
}
}));
return new Pair<>(conversationCount, unreadMessages);
}
/** * @return Pair<Integer, ArrayList<Message>> where pair.first is total count of chats which have unreadMessages * and chat.second is ArrayList of unread messages of that chat */ public Pair<Integer, ArrayList<Message>> getUnreadNotificationCount() {
ArrayList<Message> unreadMessages = new ArrayList<>();
int unreadCount = 0;
int conversationCount = 0;
int id = 0;
String body,messageId;
int type;
long timestamp;
String query = "SELECT * FROM " + DatabaseHelper.TABLE_CHATS + " WHERE " + DatabaseHelper.COLUMN_UNREAD_COUNT + " > 0 " + ";";
Cursor c = mDatabase.rawQuery(query, null);
if (c != null) {
c.moveToFirst();
{
do {
unreadCount += c.getInt(c.getColumnIndex(DatabaseHelper.COLUMN_UNREAD_COUNT));
id = c.getInt(c.getColumnIndex(DatabaseHelper.COLUMN_ID));
type = c.getInt(c.getColumnIndex(DatabaseHelper.COLUMN_TYPE));
body = c.getString(c.getColumnIndex(DatabaseHelper.COLUMN_MESSAGE_BODY));
timestamp = c.getLong(c.getColumnIndex(DatabaseHelper.COLUMN_TIMESTAMP));
messageId = c.getString(c.getColumnIndex(DatabaseHelper.COLUMN_MESSAGE_ID));
conversationCount++;
String q = " SELECT " + DatabaseHelper.COLUMN_NAME + " FROM " + DatabaseHelper.TABLE_USERS + " WHERE " + DatabaseHelper.COLUMN_ID + "=" + id;
Cursor c1 = mDatabase.rawQuery(q, null);
if (c1 != null) {
if (c1.moveToFirst()) {
do {
// for (int i = 0; i < unreadCount; i++) { Message message = new Message();
message.id = messageId;
message.senderName = c1.getString(c1.getColumnIndex(DatabaseHelper.COLUMN_NAME));
message.timeStamp = timestamp;
message.body = body;
message.type = type;
unreadMessages.add(message);
// }
} while (c1.moveToNext());
}
c1.close();
}
} while (c.moveToNext());
}
c.close();
}
Collections.sort(unreadMessages, Collections.reverseOrder(new Comparator<Message>() {
@Override public int compare(Message m1, Message m2) {
long t1 = m1.getTimeStampLong() != null ? m1.getTimeStampLong().longValue() : 0;
long t2 = m2.getTimeStampLong() != null ? m2.getTimeStampLong().longValue() : 0;
if (t1 == t2) return 0;
else if (t1 > t2) return 1;
else return -1;
}
}));
return new Pair<>(conversationCount, unreadMessages);
}
/** * FETCH TOTAL UNREAD COUNT OF CHAT TO SHOW AT APP SLIDER * * @return */ public int getTotalUnreadCount() {
int unReadCount = 0;
Cursor c = mDatabase.rawQuery("SELECT SUM(" + DatabaseHelper.COLUMN_UNREAD_COUNT + ")" + " FROM " + DatabaseHelper.TABLE_CHATS + " ORDER BY id;", null);
if (c != null) {
unReadCount = c.moveToFirst() ? c.getInt(0) : 0;
c.close();
}
return unReadCount;
}
code to use....
DatabaseManager manager = DatabaseManager.getInstance(getActivity());
manager.openDatabase();
mChatList.addAll(manager.getChats());
manager.closeDatabase();
Comments
Post a Comment