package org.softeg.slartus.forpdaplus.db;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.text.TextUtils;
import com.readystatesoftware.sqliteasset.SQLiteAssetHelper;
import org.softeg.slartus.forpdaapi.Topic;
import org.softeg.slartus.forpdaplus.App;
import org.softeg.slartus.forpdaplus.classes.Themes;
import org.softeg.slartus.forpdaplus.classes.forum.ExtTopic;
import org.softeg.slartus.forpdaplus.common.AppLog;
import org.softeg.slartus.forpdaplus.notes.Note;
import java.io.IOException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
/**
* Created with IntelliJ IDEA.
* User: slinkin
* Date: 20.02.13
* Time: 11:21
* To change this template use File | Settings | File Templates.
*/
public class NotesTable {
public static final String TABLE_NAME = "Notes";
public static final String COLUMN_ID = "_id";
public static final String COLUMN_TITLE = "Title";
public static final String COLUMN_BODY = "Body";
public static final String COLUMN_URL = "Url";
public static final String COLUMN_TOPIC_ID = "TopicId";
public static final String COLUMN_POST_ID = "PostId";
public static final String COLUMN_USER_ID = "UserId";
public static final String COLUMN_USER = "User";
public static final String COLUMN_TOPIC = "Topic";
public static final String COLUMN_DATE = "Date";
public static void insertRow(String title, String body, String url, CharSequence topicId, String topic,
String postId, String userId, String user) {
SQLiteDatabase db = null;
try {
NotesDbHelper dbHelper = new NotesDbHelper(App.getInstance());
db = dbHelper.getWritableDatabase();
// db.beginTransaction();
ContentValues values = new ContentValues();
values.put(COLUMN_TITLE, title);
values.put(COLUMN_BODY, body);
values.put(COLUMN_URL, url);
values.put(COLUMN_TOPIC_ID, topicId.toString());
values.put(COLUMN_POST_ID, postId);
values.put(COLUMN_USER_ID, userId);
values.put(COLUMN_USER, user);
values.put(COLUMN_TOPIC, topic);
values.put(COLUMN_DATE, DbHelper.DateTimeFormat.format(new Date()));
db.insertOrThrow(TABLE_NAME, null, values);
} catch (Exception ex) {
AppLog.e(App.getInstance(), ex);
} finally {
if (db != null) {
// db.endTransaction();
db.close();
}
}
}
public static void insertNote(SQLiteAssetHelper helper, Note note) {
SQLiteDatabase db = null;
try {
db = helper.getWritableDatabase();
// db.beginTransaction();
ContentValues values = new ContentValues();
values.put(COLUMN_TITLE, note.Title);
values.put(COLUMN_BODY, note.Body);
values.put(COLUMN_URL, note.Url);
values.put(COLUMN_TOPIC_ID, note.TopicId);
values.put(COLUMN_POST_ID, note.PostId);
values.put(COLUMN_USER_ID, note.UserId);
values.put(COLUMN_USER, note.User);
values.put(COLUMN_TOPIC, note.Topic);
values.put(COLUMN_DATE, DbHelper.DateTimeFormat.format(note.Date));
db.insertOrThrow(TABLE_NAME, null, values);
} catch (Exception ex) {
AppLog.e(App.getInstance(), ex);
} finally {
if (db != null) {
// db.endTransaction();
db.close();
}
}
}
public static ArrayList<Topic> getNotes(String topicId) throws ParseException, IOException {
ArrayList<Topic> res = new ArrayList<Topic>();
SQLiteDatabase db = null;
Cursor c = null;
try {
NotesDbHelper dbHelper = new NotesDbHelper(App.getInstance());
db = dbHelper.getWritableDatabase();
String selection = null;
String[] selectionArgs = null;
if (!TextUtils.isEmpty(topicId)) {
selection = COLUMN_TOPIC_ID + "=?";
selectionArgs = new String[]{topicId};
}
assert db != null;
c = db.query(TABLE_NAME, null, selection, selectionArgs, null, null, COLUMN_DATE + " DESC");
if (c.moveToFirst()) {
int columnIdIndex = c.getColumnIndex(COLUMN_ID);
int columnTitleIndex = c.getColumnIndex(COLUMN_TITLE);
int columnBodyIndex = c.getColumnIndex(COLUMN_BODY);
int columnUserIndex = c.getColumnIndex(COLUMN_USER);
int columnDateIndex = c.getColumnIndex(COLUMN_DATE);
do {
String id = c.getString(columnIdIndex);
String title = c.getString(columnTitleIndex);
Date createDate = DbHelper.parseDate(c.getString(columnDateIndex));
Topic topic = new Topic(id, title);
topic.setDescription(c.getString(columnBodyIndex));
topic.setLastMessageAuthor(c.getString(columnUserIndex));
topic.setLastMessageDate(createDate);
res.add(topic);
} while (c.moveToNext());
}
} finally {
if (db != null) {
if (c != null)
c.close();
db.close();
}
}
return res;
}
public static Themes getNoteThemes(String topicId) throws ParseException, IOException {
Themes downloadTasks = new Themes();
SQLiteDatabase db = null;
Cursor c = null;
try {
NotesDbHelper dbHelper = new NotesDbHelper(App.getInstance());
db = dbHelper.getWritableDatabase();
String selection = null;
String[] selectionArgs = null;
if (!TextUtils.isEmpty(topicId)) {
selection = COLUMN_TOPIC_ID + "=?";
selectionArgs = new String[]{topicId};
}
c = db.query(TABLE_NAME, null, selection, selectionArgs, null, null, COLUMN_DATE + " DESC");
if (c.moveToFirst()) {
int columnIdIndex = c.getColumnIndex(COLUMN_ID);
int columnTitleIndex = c.getColumnIndex(COLUMN_TITLE);
int columnBodyIndex = c.getColumnIndex(COLUMN_BODY);
int columnUrlIndex = c.getColumnIndex(COLUMN_URL);
int columnTopicIdIndex = c.getColumnIndex(COLUMN_TOPIC_ID);
int columnPostIdIndex = c.getColumnIndex(COLUMN_POST_ID);
int columnUserIdIndex = c.getColumnIndex(COLUMN_USER_ID);
int columnUserIndex = c.getColumnIndex(COLUMN_USER);
int columnTopicIndex = c.getColumnIndex(COLUMN_TOPIC);
int columnDateIndex = c.getColumnIndex(COLUMN_DATE);
do {
String id = c.getString(columnIdIndex);
String title = c.getString(columnTitleIndex);
Date createDate = DbHelper.parseDate(c.getString(columnDateIndex));
ExtTopic topic = new ExtTopic(id, title);
topic.setDescription(c.getString(columnBodyIndex));
topic.setLastMessageAuthor(c.getString(columnUserIndex));
topic.setLastMessageDate(createDate);
downloadTasks.add(topic);
} while (c.moveToNext());
}
} finally {
if (db != null) {
if (c != null)
c.close();
db.close();
}
}
return downloadTasks;
}
public static ArrayList<Note> getNotes(SQLiteAssetHelper helper, String topicId) throws ParseException, IOException {
ArrayList<Note> notes = new ArrayList<Note>();
SQLiteDatabase db = null;
Cursor c = null;
try {
db = helper.getWritableDatabase();
String selection = null;
String[] selectionArgs = null;
if (!TextUtils.isEmpty(topicId)) {
selection = COLUMN_TOPIC_ID + "=?";
selectionArgs = new String[]{topicId};
}
c = db.query(TABLE_NAME, null, selection, selectionArgs, null, null, COLUMN_DATE + " DESC");
if (c.moveToFirst()) {
int columnIdIndex = c.getColumnIndex(COLUMN_ID);
int columnTitleIndex = c.getColumnIndex(COLUMN_TITLE);
int columnBodyIndex = c.getColumnIndex(COLUMN_BODY);
int columnUrlIndex = c.getColumnIndex(COLUMN_URL);
int columnTopicIdIndex = c.getColumnIndex(COLUMN_TOPIC_ID);
int columnPostIdIndex = c.getColumnIndex(COLUMN_POST_ID);
int columnUserIdIndex = c.getColumnIndex(COLUMN_USER_ID);
int columnUserIndex = c.getColumnIndex(COLUMN_USER);
int columnTopicIndex = c.getColumnIndex(COLUMN_TOPIC);
int columnDateIndex = c.getColumnIndex(COLUMN_DATE);
do {
Note note = new Note();
note.Id = c.getString(columnIdIndex);
note.Title = c.getString(columnTitleIndex);
note.Body = c.getString(columnBodyIndex);
note.Url = c.getString(columnUrlIndex);
note.TopicId = c.getString(columnTopicIdIndex);
note.PostId = c.getString(columnPostIdIndex);
note.UserId = c.getString(columnUserIdIndex);
note.User = c.getString(columnUserIndex);
note.Topic = c.getString(columnTopicIndex);
note.Date = DbHelper.parseDate(c.getString(columnDateIndex));
notes.add(note);
} while (c.moveToNext());
}
} finally {
if (db != null) {
if (c != null)
c.close();
db.close();
}
}
return notes;
}
public static Note getNote(String id) throws IOException, ParseException {
SQLiteDatabase db = null;
Cursor c = null;
try {
NotesDbHelper dbHelper = new NotesDbHelper(App.getInstance());
db = dbHelper.getWritableDatabase();
String selection = "_id=?";
String[] selectionArgs = new String[]{id};
c = db.query(TABLE_NAME, null, selection, selectionArgs, null, null, COLUMN_DATE + " DESC");
if (c.moveToFirst()) {
int columnTitleIndex = c.getColumnIndex(COLUMN_TITLE);
int columnBodyIndex = c.getColumnIndex(COLUMN_BODY);
int columnUrlIndex = c.getColumnIndex(COLUMN_URL);
int columnTopicIdIndex = c.getColumnIndex(COLUMN_TOPIC_ID);
int columnPostIdIndex = c.getColumnIndex(COLUMN_POST_ID);
int columnUserIdIndex = c.getColumnIndex(COLUMN_USER_ID);
int columnUserIndex = c.getColumnIndex(COLUMN_USER);
int columnTopicIndex = c.getColumnIndex(COLUMN_TOPIC);
int columnDateIndex = c.getColumnIndex(COLUMN_DATE);
Note note = new Note();
note.Id = id;
note.Title = c.getString(columnTitleIndex);
note.Body = c.getString(columnBodyIndex);
note.Url = c.getString(columnUrlIndex);
note.TopicId = c.getString(columnTopicIdIndex);
note.PostId = c.getString(columnPostIdIndex);
note.UserId = c.getString(columnUserIdIndex);
note.User = c.getString(columnUserIndex);
note.Topic = c.getString(columnTopicIndex);
note.Date = DbHelper.parseDate(c.getString(columnDateIndex));
return note;
}
} finally {
if (db != null) {
if (c != null)
c.close();
db.close();
}
}
return null;
}
public static void delete(String id) throws IOException {
SQLiteDatabase db = null;
Cursor c = null;
try {
NotesDbHelper dbHelper = new NotesDbHelper(App.getInstance());
db = dbHelper.getWritableDatabase();
db.execSQL("delete from " + TABLE_NAME + " where " + COLUMN_ID + "=" + id);
} finally {
if (db != null) {
if (c != null)
c.close();
db.close();
}
}
}
public static void deleteAll(SQLiteAssetHelper helper) throws IOException {
SQLiteDatabase db = null;
Cursor c = null;
try {
db = helper.getWritableDatabase();
db.execSQL("delete from " + TABLE_NAME);
} finally {
if (db != null) {
if (c != null)
c.close();
db.close();
}
}
}
}