public List<Message> getThreads(
SQLiteDatabase db, int myUserId, boolean both, int limit, int offset) {
List<Message> list = new ArrayList<Message>();
String myId = String.valueOf(myUserId);
try {
String sql =
"(select max(id) as latest_id from "
+ TABLE_CHAT
+ " group by max(FROMJID, TOJID), "
+ "min(FROMJID, TOJID) having (FROMJID = '"
+ myId
+ "' or TOJID = '"
+ myId
+ "' "
+ ")order by latest_id desc limit "
+ limit
+ " offset "
+ offset
+ ") AS threads";
sql =
"select * from "
+ TABLE_CHAT
+ ", "
+ sql
+ " where "
+ TABLE_CHAT
+ ".id = threads.latest_id;";
Cursor cursor = db.rawQuery(sql, null);
int count = cursor.getCount();
if (count > 0) {
cursor.moveToFirst();
while (!cursor.isAfterLast()) {
// "(ID INTEGER PRIMARY KEY,FROM TEXT,TO TEXT,TIME LONG,TEXT TEXT, ATTACHID TEXT,
// ATTACHTYPE INTEGER, STATE INTEGER);";
Message msg = new Message(cursor.getString(2), Message.MSG_TYPE_CHAT);
msg.setFrom(cursor.getString(1));
msg.setBody(cursor.getString(4));
msg.setTimestamp(new Date(cursor.getLong(3)));
msg.setState(cursor.getInt(7));
msg.setExtraType(cursor.getString(8));
msg.setExtraJson(cursor.getString(9));
msg.setSubject(cursor.getString(10));
msg.setThread(msg.getFromUserId().equals(myId) ? msg.getToUserId() : msg.getFromUserId());
list.add(msg);
cursor.moveToNext();
}
cursor.close();
return list;
} else {
cursor.close();
}
} catch (SQLException e) {
Log.e(TAG, "Error getRecords" + e.getMessage());
}
return list;
}
public List<Message> getMessages(
SQLiteDatabase db, String from, String to, boolean both, int limit, int offset) {
List<Message> list = new ArrayList<Message>();
try {
String fromJid = from.split("@")[0];
String toJid = to.split("@")[0];
String sql =
"SELECT * FROM "
+ TABLE_CHAT
+ " where (FROMJID = '"
+ fromJid
+ "' and TOJID = '"
+ toJid
+ "')";
if (both) {
sql += " OR (FROMJID = '" + toJid + "' and TOJID = '" + fromJid + "')";
}
sql += " order by TIME DESC limit " + limit + " offset " + offset + ";";
Cursor cursor = db.rawQuery(sql, null);
int count = cursor.getCount();
if (count > 0) {
cursor.moveToFirst();
while (!cursor.isAfterLast()) {
// "(ID INTEGER PRIMARY KEY,FROM TEXT,TO TEXT,TIME LONG,TEXT TEXT, ATTACHID TEXT,
// ATTACHTYPE INTEGER, STATE INTEGER);";
Message msg = new Message(cursor.getString(2), Message.MSG_TYPE_CHAT);
msg.setId(cursor.getInt(0));
msg.setFrom(cursor.getString(1));
msg.setBody(cursor.getString(4));
msg.setTimestamp(new Date(cursor.getLong(3)));
msg.setState(cursor.getInt(7));
msg.setExtraType(cursor.getString(8));
msg.setExtraJson(cursor.getString(9));
msg.setSubject(cursor.getString(10));
list.add(msg);
cursor.moveToNext();
}
cursor.close();
return list;
} else {
cursor.close();
}
} catch (SQLException e) {
Log.e(TAG, "Error getRecords" + e.getMessage());
}
return list;
}