Java tutorial
/* * This file is part of OppiaMobile - https://digital-campus.org/ * * OppiaMobile is free software: you can redistribute it and/or modify * it under the terms of the GNU General Public License as published by * the Free Software Foundation, either version 3 of the License, or * (at your option) any later version. * * OppiaMobile is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * You should have received a copy of the GNU General Public License * along with OppiaMobile. If not, see <http://www.gnu.org/licenses/>. */ package org.digitalcampus.oppia.application; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import org.digitalcampus.oppia.activity.PrefsActivity; import org.digitalcampus.oppia.exception.InvalidXMLException; import org.digitalcampus.oppia.exception.UserNotFoundException; import org.digitalcampus.oppia.listener.DBListener; import org.digitalcampus.oppia.model.Activity; import org.digitalcampus.oppia.model.ActivitySchedule; import org.digitalcampus.oppia.model.Client; import org.digitalcampus.oppia.model.ClientSession; import org.digitalcampus.oppia.model.SearchOutput; import org.digitalcampus.oppia.model.Course; import org.digitalcampus.oppia.model.QuizAttempt; import org.digitalcampus.oppia.model.QuizStats; import org.digitalcampus.oppia.model.SearchResult; import org.digitalcampus.oppia.model.TrackerLog; import org.digitalcampus.oppia.model.User; import org.digitalcampus.oppia.task.Payload; import org.digitalcampus.oppia.utils.xmlreaders.CourseXMLReader; import org.joda.time.DateTime; import org.json.JSONException; import org.json.JSONObject; import android.content.ContentValues; import android.content.Context; import android.content.SharedPreferences; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import android.preference.PreferenceManager; import android.provider.BaseColumns; import android.util.Log; import com.splunk.mint.Mint; public class DbHelper extends SQLiteOpenHelper { static final String TAG = DbHelper.class.getSimpleName(); static final String DB_NAME = "bright-future.db"; static final int DB_VERSION = 23; private static SQLiteDatabase db; private SharedPreferences prefs; private static final String COURSE_TABLE = "Module"; private static final String COURSE_C_ID = BaseColumns._ID; private static final String COURSE_C_VERSIONID = "versionid"; private static final String COURSE_C_TITLE = "title"; private static final String COURSE_C_DESC = "description"; private static final String COURSE_C_SHORTNAME = "shortname"; private static final String COURSE_C_LOCATION = "location"; private static final String COURSE_C_SCHEDULE = "schedule"; private static final String COURSE_C_IMAGE = "imagelink"; private static final String COURSE_C_LANGS = "langs"; private static final String COURSE_C_ORDER_PRIORITY = "orderpriority"; private static final String ACTIVITY_TABLE = "Activity"; private static final String ACTIVITY_C_ID = BaseColumns._ID; private static final String ACTIVITY_C_COURSEID = "modid"; // reference to // COURSE_C_ID private static final String ACTIVITY_C_SECTIONID = "sectionid"; private static final String ACTIVITY_C_ACTID = "activityid"; private static final String ACTIVITY_C_ACTTYPE = "activitytype"; private static final String ACTIVITY_C_ACTIVITYDIGEST = "digest"; private static final String ACTIVITY_C_STARTDATE = "startdate"; private static final String ACTIVITY_C_ENDDATE = "enddate"; private static final String ACTIVITY_C_TITLE = "title"; private static final String TRACKER_LOG_TABLE = "TrackerLog"; private static final String TRACKER_LOG_C_ID = BaseColumns._ID; private static final String TRACKER_LOG_C_COURSEID = "modid"; // reference to COURSE_C_ID private static final String TRACKER_LOG_C_DATETIME = "logdatetime"; private static final String TRACKER_LOG_C_ACTIVITYDIGEST = "digest"; private static final String TRACKER_LOG_C_DATA = "logdata"; private static final String TRACKER_LOG_C_SUBMITTED = "logsubmitted"; private static final String TRACKER_LOG_C_INPROGRESS = "loginprogress"; private static final String TRACKER_LOG_C_COMPLETED = "completed"; private static final String TRACKER_LOG_C_USERID = "userid"; private static final String QUIZATTEMPTS_TABLE = "results"; private static final String QUIZATTEMPTS_C_ID = BaseColumns._ID; private static final String QUIZATTEMPTS_C_DATETIME = "resultdatetime"; private static final String QUIZATTEMPTS_C_DATA = "content"; private static final String QUIZATTEMPTS_C_SENT = "submitted"; private static final String QUIZATTEMPTS_C_COURSEID = "moduleid"; private static final String QUIZATTEMPTS_C_USERID = "userid"; private static final String QUIZATTEMPTS_C_SCORE = "score"; private static final String QUIZATTEMPTS_C_MAXSCORE = "maxscore"; private static final String QUIZATTEMPTS_C_PASSED = "passed"; private static final String QUIZATTEMPTS_C_ACTIVITY_DIGEST = "actdigest"; private static final String SEARCH_TABLE = "search"; private static final String SEARCH_C_TEXT = "fulltext"; private static final String SEARCH_C_COURSETITLE = "coursetitle"; private static final String SEARCH_C_SECTIONTITLE = "sectiontitle"; private static final String SEARCH_C_ACTIVITYTITLE = "activitytitle"; private static final String USER_TABLE = "user"; private static final String USER_C_ID = BaseColumns._ID; private static final String USER_C_USERNAME = "username"; private static final String USER_C_FIRSTNAME = "firstname"; private static final String USER_C_LASTNAME = "lastname"; private static final String USER_C_PASSWORDENCRYPTED = "passwordencrypted"; private static final String USER_C_APIKEY = "apikey"; private static final String USER_C_LAST_LOGIN_DATE = "lastlogin"; private static final String USER_C_NO_LOGINS = "nologins"; private static final String USER_C_POINTS = "points"; private static final String USER_C_BADGES = "badges"; private static final String USER_PROPS_TABLE = "userprops"; public void beginTransaction() { db.beginTransaction(); } public void endTransaction(boolean success) { if (success) { db.setTransactionSuccessful(); } db.endTransaction(); } // string constants for database client table private static final String CLIENT_TABLE = "client"; private static final String CLIENT_C_ID = BaseColumns._ID; private static final String CLIENT_C_NAME = "clientname"; private static final String CLIENT_C_MOBILENUMBER = "clientmobilenumber"; private static final String CLIENT_C_GENDER = "clientgender"; private static final String CLIENT_C_MARITALSTATUS = "clientmaritalstatus"; private static final String CLIENT_C_AGE = "clientage"; private static final String CLIENT_C_PARITY = "clientparity"; private static final String CLIENT_C_LIFESTAGE = "clientlifestage"; private static final String CLIENT_C_SERVER_ID = "clientserverid"; private static final String CLIENT_C_MODIFIED_DATE = "clientsynceddate"; private static final String CLIENT_C_AGEYOUNGESTCHILD = "clientageyoungestchild"; private static final String CLIENT_C_HEALTHWORKER = "clienthealthworker"; private static final String CLIENT_C_HUSBANDNAME = "clienthusbandname"; private static final String CLIENT_C_METHODNAME = "clientmethodname"; private static final String CLIENT_CLOSE_CASE = "clientclosecase"; private static final String CLIENT_DELETE_RECORD = "clientdeleterecord"; private static final String CLIENT_ADAPTED_METHOD_NAME = "clientadaptedmethodname"; private static final String CLIENT_ADAPTED_METHOD_TIME = "clientadaptedmethodtime"; private static final String CLIENT_LAST_CREATED = "clientlastcreated"; // string constants for database clienttracker table private static final String CLIENT_TRACKER_TABLE = "clienttracker"; private static final String CLIENT_TRACKER_C_ID = BaseColumns._ID; private static final String CLIENT_TRACKER_C_START = "clienttrackerstart"; private static final String CLIENT_TRACKER_C_END = "clienttrackerend"; private static final String CLIENT_TRACKER_C_CLIENT = "clienttrackerclient"; private static final String CLIENT_TRACKER_C_USER = "clienttrackeruser"; // private static final String CLIENT_TRACKER_C_ISSENT = "clienttrackerissent"; private static final String CLIENT_TRACKER_C_CLIENTSTATUS = "clienttrackerisclientsynced"; // Constructor public DbHelper(Context ctx) { // super(ctx, DB_NAME, null, DB_VERSION); prefs = PreferenceManager.getDefaultSharedPreferences(ctx); DatabaseManager.initializeInstance(this); db = DatabaseManager.getInstance().openDatabase(); } @Override public void onCreate(SQLiteDatabase db) { createCourseTable(db); createActivityTable(db); createLogTable(db); createQuizAttemptsTable(db); createSearchTable(db); createUserTable(db); createClientTable(db); // create client table createClientTrackerTable(db); // create client table } public void createCourseTable(SQLiteDatabase db) { String m_sql = "create table " + COURSE_TABLE + " (" + COURSE_C_ID + " integer primary key autoincrement, " + COURSE_C_VERSIONID + " int, " + COURSE_C_TITLE + " text, " + COURSE_C_LOCATION + " text, " + COURSE_C_SHORTNAME + " text," + COURSE_C_SCHEDULE + " int," + COURSE_C_IMAGE + " text," + COURSE_C_DESC + " text," + COURSE_C_ORDER_PRIORITY + " integer default 0, " + COURSE_C_LANGS + " text)"; db.execSQL(m_sql); } public void createActivityTable(SQLiteDatabase db) { String a_sql = "create table " + ACTIVITY_TABLE + " (" + ACTIVITY_C_ID + " integer primary key autoincrement, " + ACTIVITY_C_COURSEID + " int, " + ACTIVITY_C_SECTIONID + " int, " + ACTIVITY_C_ACTID + " int, " + ACTIVITY_C_ACTTYPE + " text, " + ACTIVITY_C_STARTDATE + " datetime null, " + ACTIVITY_C_ENDDATE + " datetime null, " + ACTIVITY_C_ACTIVITYDIGEST + " text, " + ACTIVITY_C_TITLE + " text)"; db.execSQL(a_sql); } public void createLogTable(SQLiteDatabase db) { String l_sql = "create table " + TRACKER_LOG_TABLE + " (" + TRACKER_LOG_C_ID + " integer primary key autoincrement, " + TRACKER_LOG_C_COURSEID + " integer, " + TRACKER_LOG_C_DATETIME + " datetime default current_timestamp, " + TRACKER_LOG_C_ACTIVITYDIGEST + " text, " + TRACKER_LOG_C_DATA + " text, " + TRACKER_LOG_C_SUBMITTED + " integer default 0, " + TRACKER_LOG_C_INPROGRESS + " integer default 0, " + TRACKER_LOG_C_COMPLETED + " integer default 0, " + TRACKER_LOG_C_USERID + " integer default 0 " + ")"; db.execSQL(l_sql); } public void createQuizAttemptsTable(SQLiteDatabase db) { String sql = "create table " + QUIZATTEMPTS_TABLE + " (" + QUIZATTEMPTS_C_ID + " integer primary key autoincrement, " + QUIZATTEMPTS_C_DATETIME + " datetime default current_timestamp, " + QUIZATTEMPTS_C_DATA + " text, " + QUIZATTEMPTS_C_ACTIVITY_DIGEST + " text, " + QUIZATTEMPTS_C_SENT + " integer default 0, " + QUIZATTEMPTS_C_COURSEID + " integer, " + QUIZATTEMPTS_C_USERID + " integer default 0, " + QUIZATTEMPTS_C_SCORE + " real default 0, " + QUIZATTEMPTS_C_MAXSCORE + " real default 0, " + QUIZATTEMPTS_C_PASSED + " integer default 0)"; db.execSQL(sql); } public void createSearchTable(SQLiteDatabase db) { String sql = "CREATE VIRTUAL TABLE " + SEARCH_TABLE + " USING FTS3 (" + SEARCH_C_TEXT + " text, " + SEARCH_C_COURSETITLE + " text, " + SEARCH_C_SECTIONTITLE + " text, " + SEARCH_C_ACTIVITYTITLE + " text " + ")"; db.execSQL(sql); } public void createUserTable(SQLiteDatabase db) { String sql = "CREATE TABLE [" + USER_TABLE + "] (" + "[" + USER_C_ID + "]" + " integer primary key autoincrement, " + "[" + USER_C_USERNAME + "]" + " TEXT, " + "[" + USER_C_FIRSTNAME + "] TEXT, " + "[" + USER_C_LASTNAME + "] TEXT, " + "[" + USER_C_PASSWORDENCRYPTED + "] TEXT, " + "[" + USER_C_APIKEY + "] TEXT, " + "[" + USER_C_LAST_LOGIN_DATE + "] datetime null, " + "[" + USER_C_NO_LOGINS + "] integer default 0, " + "[" + USER_C_POINTS + "] integer default 0, " + "[" + USER_C_BADGES + "] integer default 0 " + ");"; db.execSQL(sql); } public void createClientTable(SQLiteDatabase db) { String sql = "CREATE TABLE [" + CLIENT_TABLE + "] (" + "[" + CLIENT_C_ID + "]" + " integer primary key autoincrement, " + "[" + CLIENT_C_NAME + "]" + " TEXT , " + "[" + CLIENT_C_MOBILENUMBER + "] integer , " + "[" + CLIENT_C_GENDER + "] TEXT , " + "[" + CLIENT_C_MARITALSTATUS + "] TEXT , " + "[" + CLIENT_C_AGE + "] integer ," + "[" + CLIENT_C_PARITY + "] TEXT ," + "[" + CLIENT_C_LIFESTAGE + "] TEXT ," + "[" + CLIENT_C_MODIFIED_DATE + "] integer , " + "[" + CLIENT_C_SERVER_ID + "] integer null , " + "[" + CLIENT_C_HEALTHWORKER + "] TEXT ," + "[" + CLIENT_C_AGEYOUNGESTCHILD + "] integer default 0 ," + "[" + CLIENT_C_HUSBANDNAME + "] TEXT null , " + "[" + CLIENT_C_METHODNAME + "] TEXT null ," + "[" + CLIENT_CLOSE_CASE + "] integer default 0 ," + "[" + CLIENT_DELETE_RECORD + "] integer default 0 ," + "[" + CLIENT_ADAPTED_METHOD_NAME + "] TEXT null ," + "[" + CLIENT_ADAPTED_METHOD_TIME + "] integer ," + "[" + CLIENT_LAST_CREATED + "] integer default 0 " + ");"; db.execSQL(sql); } public void createClientTrackerTable(SQLiteDatabase db) { String sql = "CREATE TABLE [" + CLIENT_TRACKER_TABLE + "] (" + "[" + CLIENT_TRACKER_C_ID + "]" + " integer primary key autoincrement, " + "[" + CLIENT_TRACKER_C_START + "]" + " integer default 0, " + "[" + CLIENT_TRACKER_C_END + "] integer default 0, " + "[" + CLIENT_TRACKER_C_CLIENT + "] integer , " + "[" + CLIENT_TRACKER_C_USER + "] text , " + // "["+CLIENT_TRACKER_C_ISSENT +"] integer default 0 ," + "[" + CLIENT_TRACKER_C_CLIENTSTATUS + "] integer default 0 " + ");"; db.execSQL(sql); } public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { if (oldVersion < 7) { db.execSQL("drop table if exists " + COURSE_TABLE); db.execSQL("drop table if exists " + ACTIVITY_TABLE); db.execSQL("drop table if exists " + TRACKER_LOG_TABLE); db.execSQL("drop table if exists " + QUIZATTEMPTS_TABLE); createCourseTable(db); createActivityTable(db); createLogTable(db); createQuizAttemptsTable(db); return; } if (oldVersion <= 7 && newVersion >= 8) { String sql = "ALTER TABLE " + ACTIVITY_TABLE + " ADD COLUMN " + ACTIVITY_C_STARTDATE + " datetime null;"; db.execSQL(sql); sql = "ALTER TABLE " + ACTIVITY_TABLE + " ADD COLUMN " + ACTIVITY_C_ENDDATE + " datetime null;"; db.execSQL(sql); } if (oldVersion <= 8 && newVersion >= 9) { String sql = "ALTER TABLE " + COURSE_TABLE + " ADD COLUMN " + COURSE_C_SCHEDULE + " int null;"; db.execSQL(sql); } if (oldVersion <= 9 && newVersion >= 10) { String sql = "ALTER TABLE " + ACTIVITY_TABLE + " ADD COLUMN " + ACTIVITY_C_TITLE + " text null;"; db.execSQL(sql); } // This is a fix as previous versions may not have upgraded db tables correctly if (oldVersion <= 10 && newVersion >= 11) { String sql1 = "ALTER TABLE " + ACTIVITY_TABLE + " ADD COLUMN " + ACTIVITY_C_STARTDATE + " datetime null;"; String sql2 = "ALTER TABLE " + ACTIVITY_TABLE + " ADD COLUMN " + ACTIVITY_C_ENDDATE + " datetime null;"; String sql3 = "ALTER TABLE " + COURSE_TABLE + " ADD COLUMN " + COURSE_C_SCHEDULE + " int null;"; String sql4 = "ALTER TABLE " + ACTIVITY_TABLE + " ADD COLUMN " + ACTIVITY_C_TITLE + " text null;"; try { db.execSQL(sql1); } catch (Exception e) { } try { db.execSQL(sql2); } catch (Exception e) { } try { db.execSQL(sql3); } catch (Exception e) { } try { db.execSQL(sql4); } catch (Exception e) { } } if (oldVersion <= 11 && newVersion >= 12) { String sql = "ALTER TABLE " + COURSE_TABLE + " ADD COLUMN " + COURSE_C_LANGS + " text null;"; db.execSQL(sql); sql = "ALTER TABLE " + COURSE_TABLE + " ADD COLUMN " + COURSE_C_IMAGE + " text null;"; db.execSQL(sql); } if (oldVersion <= 12 && newVersion >= 13) { String sql = "ALTER TABLE " + TRACKER_LOG_TABLE + " ADD COLUMN " + TRACKER_LOG_C_COMPLETED + " integer default 0;"; db.execSQL(sql); } // skip jump from 13 to 14 if (oldVersion <= 14 && newVersion >= 15) { ContentValues values = new ContentValues(); values.put(TRACKER_LOG_C_COMPLETED, true); db.update(TRACKER_LOG_TABLE, values, null, null); } if (oldVersion <= 15 && newVersion >= 16) { String sql = "ALTER TABLE " + COURSE_TABLE + " ADD COLUMN " + COURSE_C_DESC + " text null;"; db.execSQL(sql); } if (oldVersion <= 16 && newVersion >= 17) { String sql = "ALTER TABLE " + COURSE_TABLE + " ADD COLUMN " + COURSE_C_ORDER_PRIORITY + " integer default 0;"; db.execSQL(sql); } if (oldVersion <= 17 && newVersion >= 18) { //create search table this.createSearchTable(db); // alter quiz results table String sql1 = "ALTER TABLE " + QUIZATTEMPTS_TABLE + " ADD COLUMN " + QUIZATTEMPTS_C_USERID + " integer default 0;"; db.execSQL(sql1); // alter tracker table String sql2 = "ALTER TABLE " + TRACKER_LOG_TABLE + " ADD COLUMN " + TRACKER_LOG_C_USERID + " integer default 0;"; db.execSQL(sql2); // create user table this.createUserTable(db); } if (oldVersion <= 18 && newVersion >= 19) { // alter quiz results table String sql1 = "ALTER TABLE " + QUIZATTEMPTS_TABLE + " ADD COLUMN " + QUIZATTEMPTS_C_SCORE + " real default 0;"; db.execSQL(sql1); String sql2 = "ALTER TABLE " + QUIZATTEMPTS_TABLE + " ADD COLUMN " + QUIZATTEMPTS_C_PASSED + " integer default 0;"; db.execSQL(sql2); // alter user table String sql3 = "ALTER TABLE " + USER_TABLE + " ADD COLUMN " + USER_C_LAST_LOGIN_DATE + " datetime null;"; db.execSQL(sql3); String sql4 = "ALTER TABLE " + USER_TABLE + " ADD COLUMN " + USER_C_NO_LOGINS + " integer default 0;"; db.execSQL(sql4); //create client table db.execSQL("DROP TABLE IF EXISTS " + CLIENT_TABLE); this.createClientTable(db); } if (oldVersion <= 19 && newVersion >= 20) { db.execSQL("DROP TABLE IF EXISTS " + CLIENT_TRACKER_TABLE); this.createClientTrackerTable(db); String sql = "ALTER TABLE " + CLIENT_TABLE + " ADD COLUMN " + CLIENT_C_AGEYOUNGESTCHILD + " integer default 0;"; try { db.execSQL(sql); } catch (Exception e) { } sql = "ALTER TABLE " + CLIENT_TABLE + " ADD COLUMN " + CLIENT_C_HUSBANDNAME + " text null ;"; try { db.execSQL(sql); } catch (Exception e) { } sql = "ALTER TABLE " + CLIENT_TABLE + " ADD COLUMN " + CLIENT_C_METHODNAME + " text null ;"; try { db.execSQL(sql); } catch (Exception e) { } sql = "ALTER TABLE " + CLIENT_TABLE + " ADD COLUMN " + CLIENT_CLOSE_CASE + " integer default 0;"; try { db.execSQL(sql); } catch (Exception e) { Log.d(TAG, e.getMessage()); } sql = "ALTER TABLE " + CLIENT_TABLE + " ADD COLUMN " + CLIENT_DELETE_RECORD + " integer default 0;"; try { db.execSQL(sql); } catch (Exception e) { Log.d(TAG, e.getMessage()); } sql = "ALTER TABLE " + CLIENT_TABLE + " ADD COLUMN " + CLIENT_ADAPTED_METHOD_NAME + " TEXT null;"; try { db.execSQL(sql); } catch (Exception e) { Log.d(TAG, e.getMessage()); } sql = "ALTER TABLE " + CLIENT_TABLE + " ADD COLUMN " + CLIENT_ADAPTED_METHOD_TIME + " integer;"; try { db.execSQL(sql); } catch (Exception e) { Log.d(TAG, e.getMessage()); } sql = "ALTER TABLE " + CLIENT_TABLE + " ADD COLUMN " + CLIENT_LAST_CREATED + " integer default 0;"; try { db.execSQL(sql); } catch (Exception e) { Log.d(TAG, e.getMessage()); } // alter quiz results table String sql1 = "ALTER TABLE " + QUIZATTEMPTS_TABLE + " ADD COLUMN " + QUIZATTEMPTS_C_MAXSCORE + " real default 0;"; db.execSQL(sql1); } if (oldVersion <= 20 && newVersion >= 21) { db.execSQL("DROP TABLE IF EXISTS " + CLIENT_TABLE); this.createClientTable(db); // alter quiz results table String sql1 = "ALTER TABLE " + QUIZATTEMPTS_TABLE + " ADD COLUMN " + QUIZATTEMPTS_C_ACTIVITY_DIGEST + " text;"; db.execSQL(sql1); } if (oldVersion <= 21 && newVersion >= 22) { // add points and badges columns String sql1 = "ALTER TABLE " + USER_TABLE + " ADD COLUMN " + USER_C_POINTS + " integer default 0;"; db.execSQL(sql1); String sql2 = "ALTER TABLE " + USER_TABLE + " ADD COLUMN " + USER_C_BADGES + " integer default 0;"; db.execSQL(sql2); } if (oldVersion <= 22 && newVersion >= 23) { // update courses db.execSQL("drop table if exists " + COURSE_TABLE); db.execSQL("drop table if exists " + ACTIVITY_TABLE); db.execSQL("drop table if exists " + TRACKER_LOG_TABLE); db.execSQL("drop table if exists " + QUIZATTEMPTS_TABLE); createCourseTable(db); createActivityTable(db); createLogTable(db); createQuizAttemptsTable(db); } } public void updateV43(long userId) { // update existing trackers ContentValues values = new ContentValues(); values.put(TRACKER_LOG_C_USERID, userId); db.update(TRACKER_LOG_TABLE, values, "1=1", null); // update existing trackers ContentValues values2 = new ContentValues(); values2.put(QUIZATTEMPTS_C_USERID, userId); db.update(QUIZATTEMPTS_TABLE, values2, "1=1", null); } // returns id of the row public long addOrUpdateCourse(Course course) { ContentValues values = new ContentValues(); values.put(COURSE_C_VERSIONID, course.getVersionId()); values.put(COURSE_C_TITLE, course.getTitleJSONString()); values.put(COURSE_C_SHORTNAME, course.getShortname()); values.put(COURSE_C_LANGS, course.getLangsJSONString()); values.put(COURSE_C_IMAGE, course.getImageFile()); values.put(COURSE_C_DESC, course.getDescriptionJSONString()); values.put(COURSE_C_ORDER_PRIORITY, course.getPriority()); if (!this.isInstalled(course.getShortname())) { Log.v(TAG, "Record added"); return db.insertOrThrow(COURSE_TABLE, null, values); } else if (this.toUpdate(course.getShortname(), course.getVersionId())) { long toUpdate = this.getCourseID(course.getShortname()); // remove existing course info from search index this.searchIndexRemoveCourse(toUpdate); if (toUpdate != 0) { db.update(COURSE_TABLE, values, COURSE_C_ID + "=" + toUpdate, null); // remove all the old activities String s = ACTIVITY_C_COURSEID + "=?"; String[] args = new String[] { String.valueOf(toUpdate) }; db.delete(ACTIVITY_TABLE, s, args); return toUpdate; } } return -1; } // returns id of the row public long addOrUpdateUser(User user) { if (user.getUsername().equals("") || user.getUsername() == null) { return 0; } ContentValues values = new ContentValues(); values.put(USER_C_USERNAME, user.getUsername()); values.put(USER_C_FIRSTNAME, user.getFirstname()); values.put(USER_C_LASTNAME, user.getLastname()); values.put(USER_C_PASSWORDENCRYPTED, user.getPasswordEncrypted()); values.put(USER_C_APIKEY, user.getApiKey()); values.put(USER_C_POINTS, user.getPoints()); values.put(USER_C_BADGES, user.getBadges()); long userId = this.isUser(user.getUsername()); if (userId == -1) { Log.v(TAG, "Record added"); return db.insertOrThrow(USER_TABLE, null, values); } else { String s = USER_C_ID + "=?"; String[] args = new String[] { String.valueOf(userId) }; db.update(USER_TABLE, values, s, args); return userId; } } // returns id of the new client row, adding new client public long addClient(Client client) { ContentValues values = new ContentValues(); values.put(CLIENT_C_NAME, client.getClientName()); values.put(CLIENT_C_MOBILENUMBER, client.getClientMobileNumber()); values.put(CLIENT_C_GENDER, client.getClientGender()); values.put(CLIENT_C_MARITALSTATUS, client.getClientMaritalStatus()); values.put(CLIENT_C_AGE, client.getClientAge()); values.put(CLIENT_C_PARITY, client.getClientParity()); values.put(CLIENT_C_LIFESTAGE, client.getClientLifeStage()); values.put(CLIENT_C_HEALTHWORKER, client.getHealthWorker()); values.put(CLIENT_C_SERVER_ID, client.getClientServerId()); values.put(CLIENT_C_MODIFIED_DATE, System.currentTimeMillis() / 1000); values.put(CLIENT_C_AGEYOUNGESTCHILD, client.getAgeYoungestChild()); values.put(CLIENT_C_METHODNAME, client.getMethodName()); values.put(CLIENT_C_HUSBANDNAME, client.getHusbandName()); values.put(CLIENT_CLOSE_CASE, client.getClientCloseCase()); values.put(CLIENT_DELETE_RECORD, client.getClientDeleteRecord()); values.put(CLIENT_ADAPTED_METHOD_NAME, (client.getAdaptedMethodName() != null) ? (((client.getAdaptedMethodName()).split("_")[0] != null) ? ((client.getAdaptedMethodName()).split("_")[0]) : "") : ""); values.put(CLIENT_ADAPTED_METHOD_TIME, System.currentTimeMillis() / 1000); values.put(CLIENT_LAST_CREATED, 1); Log.v(TAG, "Client Record added"); return db.insertOrThrow(CLIENT_TABLE, null, values); } public long isUser(String username) { String s = USER_C_USERNAME + "=?"; String[] args = new String[] { username }; Cursor c = db.query(USER_TABLE, null, s, args, null, null, null); if (c.getCount() == 0) { c.close(); return -1; } else { c.moveToFirst(); int userId = c.getInt(c.getColumnIndex(USER_C_ID)); c.close(); return userId; } } public int getCourseID(String shortname) { String s = COURSE_C_SHORTNAME + "=?"; String[] args = new String[] { shortname }; Cursor c = db.query(COURSE_TABLE, null, s, args, null, null, null); if (c.getCount() == 0) { c.close(); return 0; } else { c.moveToFirst(); int courseId = c.getInt(c.getColumnIndex(COURSE_C_ID)); c.close(); return courseId; } } public void updateScheduleVersion(long courseId, long scheduleVersion) { ContentValues values = new ContentValues(); values.put(COURSE_C_SCHEDULE, scheduleVersion); db.update(COURSE_TABLE, values, COURSE_C_ID + "=" + courseId, null); } public void insertActivities(ArrayList<Activity> acts) { beginTransaction(); for (Activity a : acts) { ContentValues values = new ContentValues(); values.put(ACTIVITY_C_COURSEID, a.getCourseId()); values.put(ACTIVITY_C_SECTIONID, a.getSectionId()); values.put(ACTIVITY_C_ACTID, a.getActId()); values.put(ACTIVITY_C_ACTTYPE, a.getActType()); values.put(ACTIVITY_C_ACTIVITYDIGEST, a.getDigest()); values.put(ACTIVITY_C_TITLE, a.getTitleJSONString()); db.insertOrThrow(ACTIVITY_TABLE, null, values); } endTransaction(true); } public void insertSchedule(ArrayList<ActivitySchedule> actsched) { beginTransaction(); for (ActivitySchedule as : actsched) { ContentValues values = new ContentValues(); values.put(ACTIVITY_C_STARTDATE, as.getStartTimeString()); values.put(ACTIVITY_C_ENDDATE, as.getEndTimeString()); db.update(ACTIVITY_TABLE, values, ACTIVITY_C_ACTIVITYDIGEST + "='" + as.getDigest() + "'", null); } endTransaction(true); } public void insertTrackers(ArrayList<TrackerLog> trackers) { beginTransaction(); for (TrackerLog t : trackers) { ContentValues values = new ContentValues(); values.put(TRACKER_LOG_C_DATETIME, t.getDateTimeString()); values.put(TRACKER_LOG_C_ACTIVITYDIGEST, t.getDigest()); values.put(TRACKER_LOG_C_SUBMITTED, t.isSubmitted()); values.put(TRACKER_LOG_C_COURSEID, t.getCourseId()); values.put(TRACKER_LOG_C_COMPLETED, t.isCompleted()); values.put(TRACKER_LOG_C_USERID, t.getUserId()); db.insertOrThrow(TRACKER_LOG_TABLE, null, values); } endTransaction(true); } public void resetSchedule(int courseId) { ContentValues values = new ContentValues(); values.put(ACTIVITY_C_STARTDATE, ""); values.put(ACTIVITY_C_ENDDATE, ""); db.update(ACTIVITY_TABLE, values, ACTIVITY_C_COURSEID + "=" + courseId, null); } public ArrayList<Course> getAllCourses() { ArrayList<Course> courses = new ArrayList<Course>(); String order = COURSE_C_ORDER_PRIORITY + " DESC, " + COURSE_C_TITLE + " ASC"; Cursor c = db.query(COURSE_TABLE, null, null, null, null, null, order); c.moveToFirst(); while (c.isAfterLast() == false) { Course course = new Course(prefs.getString(PrefsActivity.PREF_STORAGE_LOCATION, "")); course.setCourseId(c.getInt(c.getColumnIndex(COURSE_C_ID))); course.setVersionId(c.getDouble(c.getColumnIndex(COURSE_C_VERSIONID))); course.setTitlesFromJSONString(c.getString(c.getColumnIndex(COURSE_C_TITLE))); course.setImageFile(c.getString(c.getColumnIndex(COURSE_C_IMAGE))); course.setLangsFromJSONString(c.getString(c.getColumnIndex(COURSE_C_LANGS))); course.setShortname(c.getString(c.getColumnIndex(COURSE_C_SHORTNAME))); course.setPriority(c.getInt(c.getColumnIndex(COURSE_C_ORDER_PRIORITY))); courses.add(course); c.moveToNext(); } c.close(); return courses; } public ArrayList<QuizAttempt> getAllQuizAttempts() { ArrayList<QuizAttempt> quizAttempts = new ArrayList<QuizAttempt>(); Cursor c = db.query(QUIZATTEMPTS_TABLE, null, null, null, null, null, null); c.moveToFirst(); while (c.isAfterLast() == false) { QuizAttempt qa = new QuizAttempt(); qa.setId(c.getInt(c.getColumnIndex(QUIZATTEMPTS_C_ID))); qa.setActivityDigest(c.getString(c.getColumnIndex(QUIZATTEMPTS_C_ACTIVITY_DIGEST))); qa.setData(c.getString(c.getColumnIndex(QUIZATTEMPTS_C_DATA))); qa.setSent(Boolean.parseBoolean(c.getString(c.getColumnIndex(QUIZATTEMPTS_C_SENT)))); qa.setCourseId(c.getLong(c.getColumnIndex(QUIZATTEMPTS_C_COURSEID))); qa.setUserId(c.getLong(c.getColumnIndex(QUIZATTEMPTS_C_USERID))); qa.setScore(c.getFloat(c.getColumnIndex(QUIZATTEMPTS_C_SCORE))); qa.setMaxscore(c.getFloat(c.getColumnIndex(QUIZATTEMPTS_C_MAXSCORE))); qa.setPassed(Boolean.parseBoolean(c.getString(c.getColumnIndex(QUIZATTEMPTS_C_PASSED)))); quizAttempts.add(qa); c.moveToNext(); } c.close(); return quizAttempts; } public ArrayList<Course> getCourses(long userId) { ArrayList<Course> courses = new ArrayList<Course>(); String order = COURSE_C_ORDER_PRIORITY + " DESC, " + COURSE_C_TITLE + " ASC"; Cursor c = db.query(COURSE_TABLE, null, null, null, null, null, order); c.moveToFirst(); while (c.isAfterLast() == false) { Course course = new Course(prefs.getString(PrefsActivity.PREF_STORAGE_LOCATION, "")); course.setCourseId(c.getInt(c.getColumnIndex(COURSE_C_ID))); course.setVersionId(c.getDouble(c.getColumnIndex(COURSE_C_VERSIONID))); course.setTitlesFromJSONString(c.getString(c.getColumnIndex(COURSE_C_TITLE))); course.setImageFile(c.getString(c.getColumnIndex(COURSE_C_IMAGE))); course.setLangsFromJSONString(c.getString(c.getColumnIndex(COURSE_C_LANGS))); course.setShortname(c.getString(c.getColumnIndex(COURSE_C_SHORTNAME))); course.setPriority(c.getInt(c.getColumnIndex(COURSE_C_ORDER_PRIORITY))); course.setDescriptionsFromJSONString(c.getString(c.getColumnIndex(COURSE_C_DESC))); course = this.courseSetProgress(course, userId); courses.add(course); c.moveToNext(); } c.close(); return courses; } public ArrayList<Course> getLearningCourses(long userId) { ArrayList<Course> courses = new ArrayList<Course>(); String order = COURSE_C_ORDER_PRIORITY + " DESC, " + COURSE_C_TITLE + " ASC"; String where = COURSE_C_SHORTNAME + " NOT IN (" + MobileLearning.CLIENT_COUNSELLING_COURSES + ")"; Cursor c = db.query(COURSE_TABLE, null, where, null, null, null, order); //Cursor c = db.query(COURSE_TABLE, null, null, null, null, null, order); c.moveToFirst(); while (c.isAfterLast() == false) { Course course = new Course(prefs.getString(PrefsActivity.PREF_STORAGE_LOCATION, "")); course.setCourseId(c.getInt(c.getColumnIndex(COURSE_C_ID))); course.setVersionId(c.getDouble(c.getColumnIndex(COURSE_C_VERSIONID))); course.setTitlesFromJSONString(c.getString(c.getColumnIndex(COURSE_C_TITLE))); course.setImageFile(c.getString(c.getColumnIndex(COURSE_C_IMAGE))); course.setLangsFromJSONString(c.getString(c.getColumnIndex(COURSE_C_LANGS))); course.setShortname(c.getString(c.getColumnIndex(COURSE_C_SHORTNAME))); course.setPriority(c.getInt(c.getColumnIndex(COURSE_C_ORDER_PRIORITY))); course.setDescriptionsFromJSONString(c.getString(c.getColumnIndex(COURSE_C_DESC))); course = this.courseSetProgress(course, userId); courses.add(course); c.moveToNext(); } c.close(); return courses; } public Course getCourse(long courseId, long userId) { Course course = null; String s = COURSE_C_ID + "=?"; String[] args = new String[] { String.valueOf(courseId) }; Cursor c = db.query(COURSE_TABLE, null, s, args, null, null, null); c.moveToFirst(); while (c.isAfterLast() == false) { course = new Course(prefs.getString(PrefsActivity.PREF_STORAGE_LOCATION, "")); course.setCourseId(c.getInt(c.getColumnIndex(COURSE_C_ID))); course.setVersionId(c.getDouble(c.getColumnIndex(COURSE_C_VERSIONID))); course.setTitlesFromJSONString(c.getString(c.getColumnIndex(COURSE_C_TITLE))); course.setImageFile(c.getString(c.getColumnIndex(COURSE_C_IMAGE))); course.setLangsFromJSONString(c.getString(c.getColumnIndex(COURSE_C_LANGS))); course.setShortname(c.getString(c.getColumnIndex(COURSE_C_SHORTNAME))); course.setPriority(c.getInt(c.getColumnIndex(COURSE_C_ORDER_PRIORITY))); course.setDescriptionsFromJSONString(c.getString(c.getColumnIndex(COURSE_C_DESC))); course = this.courseSetProgress(course, userId); c.moveToNext(); } c.close(); return course; } private Course courseSetProgress(Course course, long userId) { // get no activities String s = ACTIVITY_C_COURSEID + "=?"; String[] args = new String[] { String.valueOf(course.getCourseId()) }; Cursor c = db.query(ACTIVITY_TABLE, null, s, args, null, null, null); course.setNoActivities(c.getCount()); c.close(); // get no completed String sqlCompleted = "SELECT DISTINCT " + TRACKER_LOG_C_ACTIVITYDIGEST + " FROM " + TRACKER_LOG_TABLE + " WHERE " + TRACKER_LOG_C_COURSEID + "=" + course.getCourseId() + " AND " + TRACKER_LOG_C_USERID + "=" + userId + " AND " + TRACKER_LOG_C_COMPLETED + "=1" + " AND " + TRACKER_LOG_C_ACTIVITYDIGEST + " IN ( SELECT " + ACTIVITY_C_ACTIVITYDIGEST + " FROM " + ACTIVITY_TABLE + " WHERE " + ACTIVITY_C_COURSEID + "=" + course.getCourseId() + ")"; c = db.rawQuery(sqlCompleted, null); course.setNoActivitiesCompleted(c.getCount()); c.close(); // get no started String sqlStarted = "SELECT DISTINCT " + TRACKER_LOG_C_ACTIVITYDIGEST + " FROM " + TRACKER_LOG_TABLE + " WHERE " + TRACKER_LOG_C_COURSEID + "=" + course.getCourseId() + " AND " + TRACKER_LOG_C_USERID + "=" + userId + " AND " + TRACKER_LOG_C_COMPLETED + "=0" + " AND " + TRACKER_LOG_C_ACTIVITYDIGEST + " NOT IN (" + sqlCompleted + ")" + " AND " + TRACKER_LOG_C_ACTIVITYDIGEST + " IN ( SELECT " + ACTIVITY_C_ACTIVITYDIGEST + " FROM " + ACTIVITY_TABLE + " WHERE " + ACTIVITY_C_COURSEID + "=" + course.getCourseId() + ")"; c = db.rawQuery(sqlStarted, null); course.setNoActivitiesStarted(c.getCount()); c.close(); return course; } public ArrayList<Activity> getCourseActivities(long courseId) { ArrayList<Activity> activities = new ArrayList<Activity>(); String s = ACTIVITY_C_COURSEID + "=?"; String[] args = new String[] { String.valueOf(courseId) }; Cursor c = db.query(ACTIVITY_TABLE, null, s, args, null, null, null); c.moveToFirst(); while (c.isAfterLast() == false) { Activity activity = new Activity(); activity.setDbId(c.getInt(c.getColumnIndex(ACTIVITY_C_ID))); activity.setDigest(c.getString(c.getColumnIndex(ACTIVITY_C_ACTIVITYDIGEST))); activity.setTitlesFromJSONString(c.getString(c.getColumnIndex(ACTIVITY_C_TITLE))); activities.add(activity); c.moveToNext(); } c.close(); return activities; } public ArrayList<Activity> getCourseQuizzes(long courseId) { ArrayList<Activity> quizzes = new ArrayList<Activity>(); String s = ACTIVITY_C_COURSEID + "=? AND " + ACTIVITY_C_ACTTYPE + "=? AND " + ACTIVITY_C_SECTIONID + ">0"; String[] args = new String[] { String.valueOf(courseId), "quiz" }; Cursor c = db.query(ACTIVITY_TABLE, null, s, args, null, null, null); c.moveToFirst(); while (c.isAfterLast() == false) { Activity quiz = new Activity(); quiz.setDbId(c.getInt(c.getColumnIndex(ACTIVITY_C_ID))); quiz.setDigest(c.getString(c.getColumnIndex(ACTIVITY_C_ACTIVITYDIGEST))); quiz.setTitlesFromJSONString(c.getString(c.getColumnIndex(ACTIVITY_C_TITLE))); quizzes.add(quiz); c.moveToNext(); } c.close(); return quizzes; } public QuizStats getQuizAttempt(String digest, long userId) { QuizStats qs = new QuizStats(); qs.setDigest(digest); qs.setAttempted(false); qs.setPassed(false); // find if attempted String s1 = QUIZATTEMPTS_C_USERID + "=? AND " + QUIZATTEMPTS_C_ACTIVITY_DIGEST + "=?"; String[] args1 = new String[] { String.valueOf(userId), digest }; Cursor c1 = db.query(QUIZATTEMPTS_TABLE, null, s1, args1, null, null, null); if (c1.getCount() == 0) { return qs; } c1.moveToFirst(); while (c1.isAfterLast() == false) { float userScore = c1.getFloat(c1.getColumnIndex(QUIZATTEMPTS_C_SCORE)); if (userScore > qs.getUserScore()) { qs.setUserScore(userScore); } qs.setMaxScore(c1.getFloat(c1.getColumnIndex(QUIZATTEMPTS_C_MAXSCORE))); c1.moveToNext(); } c1.close(); qs.setAttempted(true); // find if passed String s2 = QUIZATTEMPTS_C_USERID + "=? AND " + QUIZATTEMPTS_C_ACTIVITY_DIGEST + "=? AND " + QUIZATTEMPTS_C_PASSED + "=1"; String[] args2 = new String[] { String.valueOf(userId), digest }; Cursor c2 = db.query(QUIZATTEMPTS_TABLE, null, s2, args2, null, null, null); if (c2.getCount() > 0) { qs.setPassed(true); } c2.close(); /* String s3 = QUIZATTEMPTS_C_USERID + "=? AND " + QUIZATTEMPTS_C_ACTIVITY_DIGEST +"=?"; String[] args3 = new String[] { String.valueOf(userId), digest }; Cursor c3 = db.query(QUIZATTEMPTS_TABLE, new String [] {"MAX("+ QUIZATTEMPTS_C_SCORE +") as userscore"}, s3, args3, null, null, null); c3.moveToFirst(); while (c3.isAfterLast() == false) { int userScore = c3.getInt(c3.getColumnIndex("userscore")); if (userScore > qs.getUserScore()){ qs.setUserScore(userScore); } Log.d(TAG, "Score: " + c3.getInt(c3.getColumnIndex("userscore"))); Log.d(TAG, "passed: " + qs.isPassed()); c3.moveToNext(); } c3.close(); */ return qs; } public void insertTracker(int courseId, String digest, String data, boolean completed) { //get current user id long userId = this.getUserId(prefs.getString(PrefsActivity.PREF_USER_NAME, "")); ContentValues values = new ContentValues(); values.put(TRACKER_LOG_C_COURSEID, courseId); values.put(TRACKER_LOG_C_ACTIVITYDIGEST, digest); values.put(TRACKER_LOG_C_DATA, data); values.put(TRACKER_LOG_C_COMPLETED, completed); values.put(TRACKER_LOG_C_USERID, userId); db.insertOrThrow(TRACKER_LOG_TABLE, null, values); } public void resetCourse(long courseId, long userId) { // delete quiz results this.deleteQuizAttempts(courseId, userId); this.deleteTrackers(courseId, userId); } public void deleteCourse(int courseId) { // remove from search index this.searchIndexRemoveCourse(courseId); // delete activities String s = ACTIVITY_C_COURSEID + "=?"; String[] args = new String[] { String.valueOf(courseId) }; db.delete(ACTIVITY_TABLE, s, args); // delete course s = COURSE_C_ID + "=?"; args = new String[] { String.valueOf(courseId) }; db.delete(COURSE_TABLE, s, args); } public boolean isInstalled(String shortname) { String s = COURSE_C_SHORTNAME + "=?"; String[] args = new String[] { shortname }; Cursor c = db.query(COURSE_TABLE, null, s, args, null, null, null); if (c.getCount() == 0) { c.close(); return false; } else { c.close(); return true; } } public boolean toUpdate(String shortname, Double version) { String s = COURSE_C_SHORTNAME + "=? AND " + COURSE_C_VERSIONID + "< ?"; String[] args = new String[] { shortname, String.format("%.0f", version) }; Cursor c = db.query(COURSE_TABLE, null, s, args, null, null, null); if (c.getCount() == 0) { c.close(); return false; } else { c.close(); return true; } } public boolean toUpdateSchedule(String shortname, Double scheduleVersion) { String s = COURSE_C_SHORTNAME + "=? AND " + COURSE_C_SCHEDULE + "< ?"; String[] args = new String[] { shortname, String.format("%.0f", scheduleVersion) }; Cursor c = db.query(COURSE_TABLE, null, s, args, null, null, null); if (c.getCount() == 0) { c.close(); return false; } else { c.close(); return true; } } public long getUserId(String username) { String s = USER_C_USERNAME + "=? "; String[] args = new String[] { username }; Cursor c = db.query(USER_TABLE, null, s, args, null, null, null); c.moveToFirst(); long userId = -1; while (c.isAfterLast() == false) { userId = c.getLong(c.getColumnIndex(USER_C_ID)); c.moveToNext(); } c.close(); return userId; } public User getUser(long userId) throws UserNotFoundException { String s = USER_C_ID + "=? "; String[] args = new String[] { String.valueOf(userId) }; Cursor c = db.query(USER_TABLE, null, s, args, null, null, null); c.moveToFirst(); User u = null; while (c.isAfterLast() == false) { u = new User(); u.setUserId(c.getLong(c.getColumnIndex(USER_C_ID))); u.setApiKey(c.getString(c.getColumnIndex(USER_C_APIKEY))); u.setUsername(c.getString(c.getColumnIndex(USER_C_USERNAME))); u.setFirstname(c.getString(c.getColumnIndex(USER_C_FIRSTNAME))); u.setLastname(c.getString(c.getColumnIndex(USER_C_LASTNAME))); u.setPoints(c.getInt(c.getColumnIndex(USER_C_POINTS))); u.setBadges(c.getInt(c.getColumnIndex(USER_C_BADGES))); u.setPasswordEncrypted(c.getString(c.getColumnIndex(USER_C_PASSWORDENCRYPTED))); c.moveToNext(); } c.close(); if (u == null) { throw new UserNotFoundException(); } return u; } public User getUser(String userName) throws UserNotFoundException { String s = USER_C_USERNAME + "=? "; String[] args = new String[] { userName }; Cursor c = db.query(USER_TABLE, null, s, args, null, null, null); c.moveToFirst(); User u = null; while (c.isAfterLast() == false) { u = new User(); u.setUserId(c.getLong(c.getColumnIndex(USER_C_ID))); u.setApiKey(c.getString(c.getColumnIndex(USER_C_APIKEY))); u.setUsername(c.getString(c.getColumnIndex(USER_C_USERNAME))); u.setFirstname(c.getString(c.getColumnIndex(USER_C_FIRSTNAME))); u.setLastname(c.getString(c.getColumnIndex(USER_C_LASTNAME))); u.setPoints(c.getInt(c.getColumnIndex(USER_C_POINTS))); u.setBadges(c.getInt(c.getColumnIndex(USER_C_BADGES))); u.setPasswordEncrypted(c.getString(c.getColumnIndex(USER_C_PASSWORDENCRYPTED))); c.moveToNext(); } c.close(); if (u == null) { throw new UserNotFoundException(); } return u; } public void updateUserPoints(String userName, int points) { ContentValues values = new ContentValues(); values.put(USER_C_POINTS, points); String s = USER_C_USERNAME + "=? "; String[] args = new String[] { userName }; db.update(USER_TABLE, values, s, args); } public void updateUserBadges(String userName, int badges) { ContentValues values = new ContentValues(); values.put(USER_C_BADGES, badges); String s = USER_C_USERNAME + "=? "; String[] args = new String[] { userName }; db.update(USER_TABLE, values, s, args); } public void updateUserPoints(long userId, int points) { ContentValues values = new ContentValues(); values.put(USER_C_POINTS, points); String s = USER_C_ID + "=? "; String[] args = new String[] { String.valueOf(userId) }; db.update(USER_TABLE, values, s, args); } public void updateUserBadges(long userId, int badges) { ContentValues values = new ContentValues(); values.put(USER_C_BADGES, badges); String s = USER_C_ID + "=? "; String[] args = new String[] { String.valueOf(userId) }; db.update(USER_TABLE, values, s, args); } public ArrayList<User> getAllUsers() { Cursor c = db.query(USER_TABLE, null, null, null, null, null, null); c.moveToFirst(); ArrayList<User> users = new ArrayList<User>(); while (c.isAfterLast() == false) { User u = new User(); u.setUserId(c.getInt(c.getColumnIndex(USER_C_ID))); u.setApiKey(c.getString(c.getColumnIndex(USER_C_APIKEY))); u.setUsername(c.getString(c.getColumnIndex(USER_C_USERNAME))); u.setFirstname(c.getString(c.getColumnIndex(USER_C_FIRSTNAME))); u.setLastname(c.getString(c.getColumnIndex(USER_C_LASTNAME))); u.setPoints(c.getInt(c.getColumnIndex(USER_C_POINTS))); u.setBadges(c.getInt(c.getColumnIndex(USER_C_BADGES))); u.setPasswordEncrypted(c.getString(c.getColumnIndex(USER_C_PASSWORDENCRYPTED))); users.add(u); c.moveToNext(); } c.close(); return users; } // return all the clients pertaining to a particular health worker / user public ArrayList<Client> getAllClients(String userName) { String s = CLIENT_C_HEALTHWORKER + "=? "; String[] args = new String[] { userName }; Cursor c = db.query(CLIENT_TABLE, null, s, args, null, null, null); Client client; ArrayList<Client> clients = new ArrayList<Client>(); c.moveToFirst(); while (c.isAfterLast() == false) { client = new Client(); client.setClientId(c.getInt(c.getColumnIndex(CLIENT_C_ID))); client.setClientName(c.getString(c.getColumnIndex(CLIENT_C_NAME))); client.setClientMobileNumber(c.getLong(c.getColumnIndex(CLIENT_C_MOBILENUMBER))); client.setClientGender(c.getString(c.getColumnIndex(CLIENT_C_GENDER))); client.setClientMaritalStatus(c.getString(c.getColumnIndex(CLIENT_C_MARITALSTATUS))); client.setClientAge(c.getInt(c.getColumnIndex(CLIENT_C_AGE))); client.setClientLifeStage(c.getString(c.getColumnIndex(CLIENT_C_LIFESTAGE))); client.setClientParity(c.getString(c.getColumnIndex(CLIENT_C_PARITY))); client.setHealthWorker(c.getString(c.getColumnIndex(CLIENT_C_HEALTHWORKER))); client.setClientServerId(c.getLong(c.getColumnIndex(CLIENT_C_SERVER_ID))); client.setAgeYoungestChild(c.getInt(c.getColumnIndex(CLIENT_C_AGEYOUNGESTCHILD))); client.setHusbandName(c.getString(c.getColumnIndex(CLIENT_C_HUSBANDNAME))); client.setMethodName(c.getString(c.getColumnIndex(CLIENT_C_METHODNAME))); client.setClientDeleteRecord(c.getInt(c.getColumnIndex(CLIENT_DELETE_RECORD))); client.setClientCloseCase(c.getInt(c.getColumnIndex(CLIENT_CLOSE_CASE))); client.setAdaptedMethodName( ((c.getString(c.getColumnIndex(CLIENT_ADAPTED_METHOD_NAME))).split("_")[0] != null) ? (c.getString(c.getColumnIndex(CLIENT_ADAPTED_METHOD_NAME))).split("_")[0] : ""); clients.add(client); c.moveToNext(); } c.close(); return clients; } public ArrayList<ClientSession> getAllClientSessions(String userName) { String s = CLIENT_TRACKER_C_USER + "=? "; String[] args = new String[] { userName }; Cursor c = db.query(CLIENT_TRACKER_TABLE, null, s, args, null, null, null); c.moveToFirst(); ClientSession clientSession; ArrayList<ClientSession> clientSessions = new ArrayList<ClientSession>(); while (c.isAfterLast() == false) { clientSession = new ClientSession(); clientSession.setId(c.getLong(c.getColumnIndex(CLIENT_TRACKER_C_ID))); clientSession.setStartDateTime(c.getLong(c.getColumnIndex(CLIENT_TRACKER_C_START))); clientSession.setClientId(c.getLong(c.getColumnIndex(CLIENT_TRACKER_C_CLIENT))); clientSession.setHealthWorker(c.getString(c.getColumnIndex(CLIENT_TRACKER_C_USER))); clientSession.setEndDateTime(c.getLong(c.getColumnIndex(CLIENT_TRACKER_C_END))); clientSessions.add(clientSession); c.moveToNext(); } c.close(); return clientSessions; } // return client based on local client id public Client getClient(long clientID) { String s = CLIENT_C_ID + "=? "; String[] args = new String[] { Long.toString(clientID) }; Cursor c = db.query(CLIENT_TABLE, null, s, args, null, null, null); c.moveToFirst(); Client client = new Client(); while (c.isAfterLast() == false) { client.setClientId(c.getInt(c.getColumnIndex(CLIENT_C_ID))); client.setClientName(c.getString(c.getColumnIndex(CLIENT_C_NAME))); client.setClientMobileNumber(c.getLong(c.getColumnIndex(CLIENT_C_MOBILENUMBER))); client.setClientGender(c.getString(c.getColumnIndex(CLIENT_C_GENDER))); client.setClientMaritalStatus(c.getString(c.getColumnIndex(CLIENT_C_MARITALSTATUS))); client.setClientAge(c.getInt(c.getColumnIndex(CLIENT_C_AGE))); client.setClientLifeStage(c.getString(c.getColumnIndex(CLIENT_C_LIFESTAGE))); client.setClientParity(c.getString(c.getColumnIndex(CLIENT_C_PARITY))); client.setHealthWorker(c.getString(c.getColumnIndex(CLIENT_C_HEALTHWORKER))); client.setClientServerId(c.getLong(c.getColumnIndex(CLIENT_C_SERVER_ID))); client.setAgeYoungestChild(c.getInt(c.getColumnIndex(CLIENT_C_AGEYOUNGESTCHILD))); client.setHusbandName(c.getString(c.getColumnIndex(CLIENT_C_HUSBANDNAME))); client.setMethodName(c.getString(c.getColumnIndex(CLIENT_C_METHODNAME))); client.setAdaptedMethodName( ((c.getString(c.getColumnIndex(CLIENT_ADAPTED_METHOD_NAME))).split("_")[0] != null) ? (c.getString(c.getColumnIndex(CLIENT_ADAPTED_METHOD_NAME))).split("_")[0] : ""); c.moveToNext(); } c.close(); return client; } // return last created client public Client getLastCreatedClient() { String s = CLIENT_C_ID + "= (SELECT MAX(" + CLIENT_C_ID + ") FROM" + CLIENT_TABLE + " )"; //String[] args = new String[] { Integer.toString(1) }; Cursor c = db.query(CLIENT_TABLE, null, s, null, null, null, null); c.moveToFirst(); Client client = new Client(); while (c.isAfterLast() == false) { client.setClientId(c.getInt(c.getColumnIndex(CLIENT_C_ID))); client.setClientName(c.getString(c.getColumnIndex(CLIENT_C_NAME))); client.setClientMobileNumber(c.getLong(c.getColumnIndex(CLIENT_C_MOBILENUMBER))); client.setClientGender(c.getString(c.getColumnIndex(CLIENT_C_GENDER))); client.setClientMaritalStatus(c.getString(c.getColumnIndex(CLIENT_C_MARITALSTATUS))); client.setClientAge(c.getInt(c.getColumnIndex(CLIENT_C_AGE))); client.setClientLifeStage(c.getString(c.getColumnIndex(CLIENT_C_LIFESTAGE))); client.setClientParity(c.getString(c.getColumnIndex(CLIENT_C_PARITY))); client.setHealthWorker(c.getString(c.getColumnIndex(CLIENT_C_HEALTHWORKER))); client.setClientServerId(c.getLong(c.getColumnIndex(CLIENT_C_SERVER_ID))); client.setAgeYoungestChild(c.getInt(c.getColumnIndex(CLIENT_C_AGEYOUNGESTCHILD))); client.setHusbandName(c.getString(c.getColumnIndex(CLIENT_C_HUSBANDNAME))); client.setMethodName(c.getString(c.getColumnIndex(CLIENT_C_METHODNAME))); client.setAdaptedMethodName( ((c.getString(c.getColumnIndex(CLIENT_ADAPTED_METHOD_NAME))).split("_")[0] != null) ? (c.getString(c.getColumnIndex(CLIENT_ADAPTED_METHOD_NAME))).split("_")[0] : ""); c.moveToNext(); } c.close(); return client; } public int getLastCreatedClientCount() { String s = CLIENT_LAST_CREATED + "=? "; String[] args = new String[] { Integer.toString(1) }; Cursor c = db.query(CLIENT_TABLE, null, s, args, null, null, null); int count = c.getCount(); c.close(); return count; } /* * get localClient based on clientServerID */ public Client getServerClient(long clientServerID) { String s = CLIENT_C_SERVER_ID + "=? "; String[] args = new String[] { Long.toString(clientServerID) }; Cursor c = db.query(CLIENT_TABLE, null, s, args, null, null, null); c.moveToFirst(); Client client = new Client(); while (c.isAfterLast() == false) { client.setClientId(c.getInt(c.getColumnIndex(CLIENT_C_ID))); client.setClientName(c.getString(c.getColumnIndex(CLIENT_C_NAME))); client.setClientMobileNumber(c.getLong(c.getColumnIndex(CLIENT_C_MOBILENUMBER))); client.setClientGender(c.getString(c.getColumnIndex(CLIENT_C_GENDER))); client.setClientMaritalStatus(c.getString(c.getColumnIndex(CLIENT_C_MARITALSTATUS))); client.setClientAge(c.getInt(c.getColumnIndex(CLIENT_C_AGE))); client.setClientLifeStage(c.getString(c.getColumnIndex(CLIENT_C_LIFESTAGE))); client.setClientParity(c.getString(c.getColumnIndex(CLIENT_C_PARITY))); client.setHealthWorker(c.getString(c.getColumnIndex(CLIENT_C_HEALTHWORKER))); client.setClientServerId(c.getLong(c.getColumnIndex(CLIENT_C_SERVER_ID))); client.setAgeYoungestChild(c.getInt(c.getColumnIndex(CLIENT_C_AGEYOUNGESTCHILD))); client.setHusbandName(c.getString(c.getColumnIndex(CLIENT_C_HUSBANDNAME))); client.setMethodName(c.getString(c.getColumnIndex(CLIENT_C_METHODNAME))); client.setAdaptedMethodName( ((c.getString(c.getColumnIndex(CLIENT_ADAPTED_METHOD_NAME))).split("_")[0] != null) ? (c.getString(c.getColumnIndex(CLIENT_ADAPTED_METHOD_NAME))).split("_")[0] : ""); c.moveToNext(); } c.close(); return client; } public int getSentTrackersCount(long userId) { String s = TRACKER_LOG_C_SUBMITTED + "=? AND " + TRACKER_LOG_C_USERID + "=? "; String[] args = new String[] { "1", String.valueOf(userId) }; Cursor c = db.query(TRACKER_LOG_TABLE, null, s, args, null, null, null); int count = c.getCount(); c.close(); return count; } public int getUnsentTrackersCount(long userId) { String s = TRACKER_LOG_C_SUBMITTED + "=? AND " + TRACKER_LOG_C_USERID + "=? "; String[] args = new String[] { "0", String.valueOf(userId) }; Cursor c = db.query(TRACKER_LOG_TABLE, null, s, args, null, null, null); int count = c.getCount(); c.close(); return count; } public Payload getUnsentTrackers(long userId) { String s = TRACKER_LOG_C_SUBMITTED + "=? AND " + TRACKER_LOG_C_USERID + "=? "; String[] args = new String[] { "0", String.valueOf(userId) }; Cursor c = db.query(TRACKER_LOG_TABLE, null, s, args, null, null, null); c.moveToFirst(); ArrayList<Object> sl = new ArrayList<Object>(); while (c.isAfterLast() == false) { TrackerLog so = new TrackerLog(); so.setId(c.getLong(c.getColumnIndex(TRACKER_LOG_C_ID))); so.setDigest(c.getString(c.getColumnIndex(TRACKER_LOG_C_ACTIVITYDIGEST))); String content = ""; try { JSONObject json = new JSONObject(); json.put("data", c.getString(c.getColumnIndex(TRACKER_LOG_C_DATA))); json.put("tracker_date", c.getString(c.getColumnIndex(TRACKER_LOG_C_DATETIME))); json.put("digest", c.getString(c.getColumnIndex(TRACKER_LOG_C_ACTIVITYDIGEST))); json.put("completed", c.getInt(c.getColumnIndex(TRACKER_LOG_C_COMPLETED))); Course m = this.getCourse(c.getLong(c.getColumnIndex(TRACKER_LOG_C_COURSEID)), userId); if (m != null) { json.put("course", m.getShortname()); } content = json.toString(); } catch (JSONException e) { Mint.logException(e); e.printStackTrace(); } so.setContent(content); sl.add(so); c.moveToNext(); } Payload p = new Payload(sl); c.close(); return p; } public int markLogSubmitted(long rowId) { ContentValues values = new ContentValues(); values.put(TRACKER_LOG_C_SUBMITTED, 1); return db.update(TRACKER_LOG_TABLE, values, TRACKER_LOG_C_ID + "=" + rowId, null); } public long insertQuizAttempt(QuizAttempt qa) { ContentValues values = new ContentValues(); values.put(QUIZATTEMPTS_C_DATA, qa.getData()); values.put(QUIZATTEMPTS_C_COURSEID, qa.getCourseId()); values.put(QUIZATTEMPTS_C_USERID, qa.getUserId()); values.put(QUIZATTEMPTS_C_MAXSCORE, qa.getMaxscore()); values.put(QUIZATTEMPTS_C_SCORE, qa.getScore()); values.put(QUIZATTEMPTS_C_PASSED, qa.isPassed()); values.put(QUIZATTEMPTS_C_ACTIVITY_DIGEST, qa.getActivityDigest()); return db.insertOrThrow(QUIZATTEMPTS_TABLE, null, values); } public void updateQuizAttempt(QuizAttempt qa) { ContentValues values = new ContentValues(); values.put(QUIZATTEMPTS_C_DATA, qa.getData()); values.put(QUIZATTEMPTS_C_COURSEID, qa.getCourseId()); values.put(QUIZATTEMPTS_C_USERID, qa.getUserId()); values.put(QUIZATTEMPTS_C_MAXSCORE, qa.getMaxscore()); values.put(QUIZATTEMPTS_C_SCORE, qa.getScore()); values.put(QUIZATTEMPTS_C_PASSED, qa.isPassed()); values.put(QUIZATTEMPTS_C_ACTIVITY_DIGEST, qa.getActivityDigest()); db.update(QUIZATTEMPTS_TABLE, values, QUIZATTEMPTS_C_ID + "=" + qa.getId(), null); } public void insertQuizAttempts(ArrayList<QuizAttempt> quizAttempts) { beginTransaction(); for (QuizAttempt qa : quizAttempts) { ContentValues values = new ContentValues(); values.put(QUIZATTEMPTS_C_DATA, qa.getData()); values.put(QUIZATTEMPTS_C_COURSEID, qa.getCourseId()); values.put(QUIZATTEMPTS_C_USERID, qa.getUserId()); values.put(QUIZATTEMPTS_C_MAXSCORE, qa.getMaxscore()); values.put(QUIZATTEMPTS_C_SCORE, qa.getScore()); values.put(QUIZATTEMPTS_C_PASSED, qa.isPassed()); values.put(QUIZATTEMPTS_C_ACTIVITY_DIGEST, qa.getActivityDigest()); values.put(QUIZATTEMPTS_C_SENT, qa.isSent()); values.put(QUIZATTEMPTS_C_DATETIME, qa.getDateTimeString()); db.insertOrThrow(QUIZATTEMPTS_TABLE, null, values); } endTransaction(true); } public ArrayList<QuizAttempt> getUnsentQuizAttempts() { String s = QUIZATTEMPTS_C_SENT + "=? "; String[] args = new String[] { "0" }; Cursor c = db.query(QUIZATTEMPTS_TABLE, null, s, args, null, null, null); c.moveToFirst(); ArrayList<QuizAttempt> quizAttempts = new ArrayList<QuizAttempt>(); while (c.isAfterLast() == false) { try { QuizAttempt qa = new QuizAttempt(); qa.setId(c.getLong(c.getColumnIndex(QUIZATTEMPTS_C_ID))); qa.setData(c.getString(c.getColumnIndex(QUIZATTEMPTS_C_DATA))); qa.setUserId(c.getLong(c.getColumnIndex(QUIZATTEMPTS_C_USERID))); User u = this.getUser(qa.getUserId()); qa.setUser(u); quizAttempts.add(qa); } catch (UserNotFoundException unfe) { // do nothing } c.moveToNext(); } c.close(); return quizAttempts; } public int markQuizSubmitted(long rowId) { ContentValues values = new ContentValues(); values.put(QUIZATTEMPTS_C_SENT, 1); String s = QUIZATTEMPTS_C_ID + "=? "; String[] args = new String[] { String.valueOf(rowId) }; return db.update(QUIZATTEMPTS_TABLE, values, s, args); } public void deleteQuizAttempts(long courseId, long userId) { // delete any quiz attempts String s = QUIZATTEMPTS_C_COURSEID + "=? AND " + QUIZATTEMPTS_C_USERID + "=?"; String[] args = new String[] { String.valueOf(courseId), String.valueOf(userId) }; db.delete(QUIZATTEMPTS_TABLE, s, args); } public void deleteTrackers(long courseId, long userId) { // delete any trackers String s = TRACKER_LOG_C_COURSEID + "=? AND " + TRACKER_LOG_C_USERID + "=? "; String[] args = new String[] { String.valueOf(courseId), String.valueOf(userId) }; db.delete(TRACKER_LOG_TABLE, s, args); } public boolean activityAttempted(long courseId, String digest, long userId) { String s = TRACKER_LOG_C_ACTIVITYDIGEST + "=? AND " + TRACKER_LOG_C_USERID + "=? AND " + TRACKER_LOG_C_COURSEID + "=?"; String[] args = new String[] { digest, String.valueOf(userId), String.valueOf(courseId) }; Cursor c = db.query(TRACKER_LOG_TABLE, null, s, args, null, null, null); if (c.getCount() == 0) { c.close(); return false; } else { c.close(); return true; } } public boolean activityCompleted(int courseId, String digest, long userId) { String s = TRACKER_LOG_C_ACTIVITYDIGEST + "=? AND " + TRACKER_LOG_C_COURSEID + "=? AND " + TRACKER_LOG_C_USERID + "=? AND " + TRACKER_LOG_C_COMPLETED + "=1"; String[] args = new String[] { digest, String.valueOf(courseId), String.valueOf(userId) }; Cursor c = db.query(TRACKER_LOG_TABLE, null, s, args, null, null, null); if (c.getCount() == 0) { c.close(); return false; } else { c.close(); return true; } } public void getCourseQuizResults(ArrayList<QuizStats> stats, int courseId, long userId) { String quizResultsWhereClause = QUIZATTEMPTS_C_COURSEID + " =? AND " + QUIZATTEMPTS_C_USERID + "=?"; String[] quizResultsArgs = new String[] { String.valueOf(courseId), String.valueOf(userId) }; String[] quizResultsColumns = new String[] { QUIZATTEMPTS_C_ACTIVITY_DIGEST, QUIZATTEMPTS_C_PASSED, QUIZATTEMPTS_C_MAXSCORE, QUIZATTEMPTS_C_SCORE }; //We get the attempts made by the user for this course's quizzes Cursor c = db.query(QUIZATTEMPTS_TABLE, quizResultsColumns, quizResultsWhereClause, quizResultsArgs, null, null, null); if (c.getCount() <= 0) return; //we return the empty array if (stats == null) stats = new ArrayList<QuizStats>(); c.moveToFirst(); while (!c.isAfterLast()) { String quizDigest = c.getString(c.getColumnIndex(QUIZATTEMPTS_C_ACTIVITY_DIGEST)); int score = (int) (c.getFloat(c.getColumnIndex(QUIZATTEMPTS_C_SCORE)) * 100); int maxScore = (int) (c.getFloat(c.getColumnIndex(QUIZATTEMPTS_C_MAXSCORE)) * 100); boolean passed = c.getInt(c.getColumnIndex(QUIZATTEMPTS_C_PASSED)) > 0; boolean alreadyInserted = false; for (QuizStats quiz : stats) { if (quiz.getDigest().equals(quizDigest)) { if (quiz.getUserScore() < score) quiz.setUserScore(score); if (quiz.getMaxScore() < maxScore) quiz.setMaxScore(maxScore); quiz.setAttempted(true); quiz.setPassed(passed); Log.d(TAG, "quiz score: " + quiz.getUserScore()); Log.d(TAG, "quiz passed: " + quiz.isPassed()); alreadyInserted = true; break; } } if (!alreadyInserted) { QuizStats quiz = new QuizStats(); quiz.setAttempted(true); quiz.setDigest(quizDigest); quiz.setUserScore(score); quiz.setMaxScore(maxScore); quiz.setPassed(passed); stats.add(quiz); } c.moveToNext(); } c.close(); } public Activity getActivityByDigest(String digest) { String sql = "SELECT * FROM " + ACTIVITY_TABLE + " a " + " WHERE " + ACTIVITY_C_ACTIVITYDIGEST + "='" + digest + "'"; Cursor c = db.rawQuery(sql, null); c.moveToFirst(); Activity a = new Activity(); while (c.isAfterLast() == false) { if (c.getString(c.getColumnIndex(ACTIVITY_C_TITLE)) != null) { a.setDigest(c.getString(c.getColumnIndex(ACTIVITY_C_ACTIVITYDIGEST))); a.setDbId(c.getInt(c.getColumnIndex(ACTIVITY_C_ID))); a.setTitlesFromJSONString(c.getString(c.getColumnIndex(ACTIVITY_C_TITLE))); a.setSectionId(c.getInt(c.getColumnIndex(ACTIVITY_C_SECTIONID))); } c.moveToNext(); } c.close(); return a; } public Activity getActivityByActId(int actId) { String sql = "SELECT * FROM " + ACTIVITY_TABLE + " a " + " WHERE " + ACTIVITY_C_ACTID + "=" + actId; Cursor c = db.rawQuery(sql, null); c.moveToFirst(); Activity a = new Activity(); while (c.isAfterLast() == false) { if (c.getString(c.getColumnIndex(ACTIVITY_C_TITLE)) != null) { a.setDigest(c.getString(c.getColumnIndex(ACTIVITY_C_ACTIVITYDIGEST))); a.setDbId(c.getInt(c.getColumnIndex(ACTIVITY_C_ID))); a.setTitlesFromJSONString(c.getString(c.getColumnIndex(ACTIVITY_C_TITLE))); a.setSectionId(c.getInt(c.getColumnIndex(ACTIVITY_C_SECTIONID))); } c.moveToNext(); } c.close(); return a; } public ArrayList<Activity> getActivitiesDue(int max, long userId) { ArrayList<Activity> activities = new ArrayList<Activity>(); DateTime now = new DateTime(); String nowDateString = MobileLearning.DATETIME_FORMAT.print(now); String sql = "SELECT a.* FROM " + ACTIVITY_TABLE + " a " + " INNER JOIN " + COURSE_TABLE + " m ON a." + ACTIVITY_C_COURSEID + " = m." + COURSE_C_ID + " LEFT OUTER JOIN (SELECT * FROM " + TRACKER_LOG_TABLE + " WHERE " + TRACKER_LOG_C_COMPLETED + "=1 AND " + TRACKER_LOG_C_USERID + "=" + userId + ") tl ON a." + ACTIVITY_C_ACTIVITYDIGEST + " = tl." + TRACKER_LOG_C_ACTIVITYDIGEST + " WHERE tl." + TRACKER_LOG_C_ID + " IS NULL " + " AND a." + ACTIVITY_C_STARTDATE + "<='" + nowDateString + "'" + " AND a." + ACTIVITY_C_TITLE + " IS NOT NULL " + " ORDER BY a." + ACTIVITY_C_ENDDATE + " ASC" + " LIMIT " + max; Cursor c = db.rawQuery(sql, null); c.moveToFirst(); while (c.isAfterLast() == false) { Activity a = new Activity(); if (c.getString(c.getColumnIndex(ACTIVITY_C_TITLE)) != null) { a.setTitlesFromJSONString(c.getString(c.getColumnIndex(ACTIVITY_C_TITLE))); a.setCourseId(c.getLong(c.getColumnIndex(ACTIVITY_C_COURSEID))); a.setDigest(c.getString(c.getColumnIndex(ACTIVITY_C_ACTIVITYDIGEST))); activities.add(a); } c.moveToNext(); } if (c.getCount() < max) { //just add in some extra suggested activities unrelated to the date/time String sql2 = "SELECT a.* FROM " + ACTIVITY_TABLE + " a " + " INNER JOIN " + COURSE_TABLE + " m ON a." + ACTIVITY_C_COURSEID + " = m." + COURSE_C_ID + " LEFT OUTER JOIN (SELECT * FROM " + TRACKER_LOG_TABLE + " WHERE " + TRACKER_LOG_C_COMPLETED + "=1) tl ON a." + ACTIVITY_C_ACTIVITYDIGEST + " = tl." + TRACKER_LOG_C_ACTIVITYDIGEST + " WHERE (tl." + TRACKER_LOG_C_ID + " IS NULL " + " OR tl." + TRACKER_LOG_C_COMPLETED + "=0)" + " AND a." + ACTIVITY_C_TITLE + " IS NOT NULL " + " AND a." + ACTIVITY_C_ID + " NOT IN (SELECT " + ACTIVITY_C_ID + " FROM (" + sql + ") b)" + " LIMIT " + (max - c.getCount()); Cursor c2 = db.rawQuery(sql2, null); c2.moveToFirst(); while (c2.isAfterLast() == false) { Activity a = new Activity(); if (c2.getString(c.getColumnIndex(ACTIVITY_C_TITLE)) != null) { a.setTitlesFromJSONString(c2.getString(c2.getColumnIndex(ACTIVITY_C_TITLE))); a.setCourseId(c2.getLong(c2.getColumnIndex(ACTIVITY_C_COURSEID))); a.setDigest(c2.getString(c2.getColumnIndex(ACTIVITY_C_ACTIVITYDIGEST))); activities.add(a); } c2.moveToNext(); } c2.close(); } c.close(); return activities; } /* * SEARCH Functions * */ public void searchIndexRemoveCourse(long courseId) { ArrayList<Activity> activities = this.getCourseActivities(courseId); Log.d(TAG, "deleting course from index: " + courseId); for (Activity a : activities) { this.deleteSearchRow(a.getDbId()); } } public void insertActivityIntoSearchTable(String courseTitle, String sectionTitle, String activityTitle, int activityDbId, String fullText) { // strip out all html tags from string (not needed for search) String noHTMLString = fullText.replaceAll("\\<.*?\\>", " "); ContentValues values = new ContentValues(); values.put("docid", activityDbId); values.put(SEARCH_C_TEXT, noHTMLString); values.put(SEARCH_C_COURSETITLE, courseTitle); values.put(SEARCH_C_SECTIONTITLE, sectionTitle); values.put(SEARCH_C_ACTIVITYTITLE, activityTitle); try { db.insertOrThrow(SEARCH_TABLE, null, values); } catch (Exception e) { } } /* * Perform a search */ public ArrayList<SearchOutput> search(String searchText, int limit, long userId, Context ctx, DBListener listener, String userName, boolean isOnlyClientSearch) { ArrayList<SearchOutput> results = new ArrayList<SearchOutput>(); Cursor c; if (!isOnlyClientSearch) { String sqlSeachFullText = String.format( "SELECT c.%s AS courseid, a.%s as activitydigest, a.%s as sectionid, 1 AS ranking FROM %s ft " + " INNER JOIN %s a ON a.%s = ft.docid" + " INNER JOIN %s c ON a.%s = c.%s " + " WHERE %s MATCH '%s' ", COURSE_C_ID, ACTIVITY_C_ACTIVITYDIGEST, ACTIVITY_C_SECTIONID, SEARCH_TABLE, ACTIVITY_TABLE, ACTIVITY_C_ID, COURSE_TABLE, ACTIVITY_C_COURSEID, COURSE_C_ID, SEARCH_C_TEXT, searchText); String sqlActivityTitle = String.format( "SELECT c.%s AS courseid, a.%s as activitydigest, a.%s as sectionid, 5 AS ranking FROM %s ft " + " INNER JOIN %s a ON a.%s = ft.docid" + " INNER JOIN %s c ON a.%s = c.%s " + " WHERE %s MATCH '%s' ", COURSE_C_ID, ACTIVITY_C_ACTIVITYDIGEST, ACTIVITY_C_SECTIONID, SEARCH_TABLE, ACTIVITY_TABLE, ACTIVITY_C_ID, COURSE_TABLE, ACTIVITY_C_COURSEID, COURSE_C_ID, SEARCH_C_ACTIVITYTITLE, searchText); String sqlSectionTitle = String.format( "SELECT c.%s AS courseid, a.%s as activitydigest, a.%s as sectionid, 10 AS ranking FROM %s ft " + " INNER JOIN %s a ON a.%s = ft.docid" + " INNER JOIN %s c ON a.%s = c.%s " + " WHERE %s MATCH '%s' ", COURSE_C_ID, ACTIVITY_C_ACTIVITYDIGEST, ACTIVITY_C_SECTIONID, SEARCH_TABLE, ACTIVITY_TABLE, ACTIVITY_C_ID, COURSE_TABLE, ACTIVITY_C_COURSEID, COURSE_C_ID, SEARCH_C_SECTIONTITLE, searchText); String sqlCourseTitle = String.format( "SELECT c.%s AS courseid, a.%s as activitydigest, a.%s as sectionid, 15 AS ranking FROM %s ft " + " INNER JOIN %s a ON a.%s = ft.docid" + " INNER JOIN %s c ON a.%s = c.%s " + " WHERE %s MATCH '%s' ", COURSE_C_ID, ACTIVITY_C_ACTIVITYDIGEST, ACTIVITY_C_SECTIONID, SEARCH_TABLE, ACTIVITY_TABLE, ACTIVITY_C_ID, COURSE_TABLE, ACTIVITY_C_COURSEID, COURSE_C_ID, SEARCH_C_COURSETITLE, searchText); String sql = String.format( "SELECT DISTINCT courseid, activitydigest FROM ( SELECT * FROM (" + "%s UNION %s UNION %s UNION %s) ORDER BY ranking DESC LIMIT 0,%d)", sqlSeachFullText, sqlActivityTitle, sqlSectionTitle, sqlCourseTitle, limit); // till this part search is being implemented for Courses, Activities and Sections c = db.rawQuery(sql, null); if (c != null && c.getCount() > 0) { //We inform the AsyncTask that the query has been performed listener.onQueryPerformed(); long startTime = System.currentTimeMillis(); HashMap<Long, Course> fetchedCourses = new HashMap<Long, Course>(); HashMap<Long, CourseXMLReader> fetchedXMLCourses = new HashMap<Long, CourseXMLReader>(); c.moveToFirst(); while (!c.isAfterLast()) { SearchResult result = new SearchResult(); long courseId = c.getLong(c.getColumnIndex("courseid")); Course course = fetchedCourses.get(courseId); if (course == null) { course = this.getCourse(courseId, userId); fetchedCourses.put(courseId, course); } result.setCourse(course); int digest = c.getColumnIndex("activitydigest"); Activity activity = this.getActivityByDigest(c.getString(digest)); result.setActivity(activity); int sectionOrderId = activity.getSectionId(); CourseXMLReader cxr = fetchedXMLCourses.get(courseId); try { if (cxr == null) { cxr = new CourseXMLReader(course.getCourseXMLLocation(), course.getCourseId(), ctx); fetchedXMLCourses.put(courseId, cxr); } result.setSection(cxr.getSection(sectionOrderId)); results.add(result); } catch (InvalidXMLException e) { // TODO Auto-generated catch block e.printStackTrace(); } c.moveToNext(); } long ellapsedTime = System.currentTimeMillis() - startTime; Log.d(TAG, "Performing search query and fetching. " + ellapsedTime + " ms ellapsed"); } if (c != null) { c.close(); } } // Search items for courses, activities and sections added to the SearchOutput arraylist Client client; String sqlClientTitle = "SELECT * FROM " + CLIENT_TABLE + " WHERE " + CLIENT_C_NAME + " LIKE '%" + searchText + "%' AND " + CLIENT_C_HEALTHWORKER + " = '" + userName + "';"; c = db.rawQuery(sqlClientTitle, null); c.moveToFirst(); while (c.isAfterLast() == false) { client = new Client(); client.setClientId(c.getInt(c.getColumnIndex(CLIENT_C_ID))); client.setClientName(c.getString(c.getColumnIndex(CLIENT_C_NAME))); client.setClientMobileNumber(c.getLong(c.getColumnIndex(CLIENT_C_MOBILENUMBER))); client.setClientGender(c.getString(c.getColumnIndex(CLIENT_C_GENDER))); client.setClientMaritalStatus(c.getString(c.getColumnIndex(CLIENT_C_MARITALSTATUS))); client.setClientAge(c.getInt(c.getColumnIndex(CLIENT_C_AGE))); client.setClientLifeStage(c.getString(c.getColumnIndex(CLIENT_C_LIFESTAGE))); client.setClientParity(c.getString(c.getColumnIndex(CLIENT_C_PARITY))); client.setHealthWorker(c.getString(c.getColumnIndex(CLIENT_C_HEALTHWORKER))); client.setClientServerId(c.getLong(c.getColumnIndex(CLIENT_C_SERVER_ID))); client.setAgeYoungestChild(c.getInt(c.getColumnIndex(CLIENT_C_AGEYOUNGESTCHILD))); client.setHusbandName(c.getString(c.getColumnIndex(CLIENT_C_HUSBANDNAME))); client.setMethodName(c.getString(c.getColumnIndex(CLIENT_C_METHODNAME))); client.setAdaptedMethodName( ((c.getString(c.getColumnIndex(CLIENT_ADAPTED_METHOD_NAME))).split("_")[0] != null) ? (c.getString(c.getColumnIndex(CLIENT_ADAPTED_METHOD_NAME))).split("_")[0] : ""); results.add(client); c.moveToNext(); } c.close(); return results; } /* * Delete the entire search index */ public void deleteSearchIndex() { db.execSQL("DELETE FROM " + SEARCH_TABLE); Log.d(TAG, "Deleted search index..."); } /* * Delete a particular activity from the search index */ public void deleteSearchRow(int activityDbId) { String s = "docid=?"; String[] args = new String[] { String.valueOf(activityDbId) }; db.delete(SEARCH_TABLE, s, args); } /* * */ public boolean isPreviousSectionActivitiesCompleted(Course course, Activity activity, long userId) { // get this activity Log.d(TAG, "this digest = " + activity.getDigest()); Log.d(TAG, "this actid = " + activity.getActId()); Log.d(TAG, "this courseid = " + activity.getCourseId()); Log.d(TAG, "this sectionid = " + activity.getSectionId()); // get all the previous activities in this section String sql = String.format( "SELECT * FROM " + ACTIVITY_TABLE + " WHERE " + ACTIVITY_C_ACTID + " < %d " + " AND " + ACTIVITY_C_COURSEID + " = %d " + " AND " + ACTIVITY_C_SECTIONID + " = %d", activity.getActId(), activity.getCourseId(), activity.getSectionId()); Log.d(TAG, "sql: " + sql); Cursor c = db.rawQuery(sql, null); if (c != null && c.getCount() > 0) { c.moveToFirst(); boolean completed = true; // check if each activity has been completed or not while (c.isAfterLast() == false) { String sqlCheck = String.format("SELECT * FROM " + TRACKER_LOG_TABLE + " WHERE " + TRACKER_LOG_C_ACTIVITYDIGEST + " = '%s'" + " AND " + TRACKER_LOG_C_COMPLETED + " =1" + " AND " + TRACKER_LOG_C_USERID + " = %d", c.getString(c.getColumnIndex(ACTIVITY_C_ACTIVITYDIGEST)), userId); Cursor c2 = db.rawQuery(sqlCheck, null); if (c2 == null || c2.getCount() == 0) { completed = false; break; } c2.close(); c.moveToNext(); } c.close(); return completed; } else { c.close(); return true; } } /* * */ public boolean isPreviousCourseActivitiesCompleted(Course course, Activity activity, long userId) { Log.d(TAG, "this digest = " + activity.getDigest()); Log.d(TAG, "this actid = " + activity.getActId()); Log.d(TAG, "this courseid = " + activity.getCourseId()); Log.d(TAG, "this sectionid = " + activity.getSectionId()); // get all the previous activities in this section String sql = String.format( "SELECT * FROM " + ACTIVITY_TABLE + " WHERE (" + ACTIVITY_C_COURSEID + " = %d " + " AND " + ACTIVITY_C_SECTIONID + " < %d )" + " OR (" + ACTIVITY_C_ACTID + " < %d " + " AND " + ACTIVITY_C_COURSEID + " = %d " + " AND " + ACTIVITY_C_SECTIONID + " = %d)", activity.getCourseId(), activity.getSectionId(), activity.getActId(), activity.getCourseId(), activity.getSectionId()); Log.d(TAG, "sql: " + sql); Cursor c = db.rawQuery(sql, null); if (c != null && c.getCount() > 0) { c.moveToFirst(); boolean completed = true; // check if each activity has been completed or not while (c.isAfterLast() == false) { String sqlCheck = String.format("SELECT * FROM " + TRACKER_LOG_TABLE + " WHERE " + TRACKER_LOG_C_ACTIVITYDIGEST + " = '%s'" + " AND " + TRACKER_LOG_C_COMPLETED + " =1" + " AND " + TRACKER_LOG_C_USERID + " = %d", c.getString(c.getColumnIndex(ACTIVITY_C_ACTIVITYDIGEST)), userId); Cursor c2 = db.rawQuery(sqlCheck, null); if (c2 == null || c2.getCount() == 0) { completed = false; break; } c2.close(); c.moveToNext(); } c.close(); return completed; } else { c.close(); return true; } } // client sync process, select clients for update for given user public ArrayList<Client> getClientsForUpdates(String userName, long previousSyncTime) { ArrayList<Client> clients = new ArrayList<Client>(); Client client; String sql = "SELECT * FROM " + CLIENT_TABLE + " WHERE " + CLIENT_C_HEALTHWORKER + " = ? AND (" + CLIENT_C_SERVER_ID + " = 0 or " + CLIENT_C_MODIFIED_DATE + " > " + Long.toString(previousSyncTime) + " or " + CLIENT_DELETE_RECORD + " > 0 " + " or " + CLIENT_CLOSE_CASE + " > 0 " + ") AND " + CLIENT_LAST_CREATED + " = 0;"; Cursor c = db.rawQuery(sql, new String[] { userName }); c.moveToFirst(); while (c.isAfterLast() == false) { client = new Client(); client.setClientServerId(c.getLong(c.getColumnIndex(CLIENT_C_SERVER_ID))); client.setHealthWorker(c.getString(c.getColumnIndex(CLIENT_C_HEALTHWORKER))); client.setClientId(c.getLong(c.getColumnIndex(CLIENT_C_ID))); client.setClientName(c.getString(c.getColumnIndex(CLIENT_C_NAME))); client.setClientMobileNumber(c.getLong(c.getColumnIndex(CLIENT_C_MOBILENUMBER))); client.setClientGender(c.getString(c.getColumnIndex(CLIENT_C_GENDER))); client.setClientMaritalStatus(c.getString(c.getColumnIndex(CLIENT_C_MARITALSTATUS))); client.setClientAge(c.getInt(c.getColumnIndex(CLIENT_C_AGE))); client.setClientParity(c.getString(c.getColumnIndex(CLIENT_C_PARITY))); client.setClientLifeStage(c.getString(c.getColumnIndex(CLIENT_C_LIFESTAGE))); client.setAgeYoungestChild(c.getInt(c.getColumnIndex(CLIENT_C_AGEYOUNGESTCHILD))); client.setHusbandName(c.getString(c.getColumnIndex(CLIENT_C_HUSBANDNAME))); client.setMethodName(c.getString(c.getColumnIndex(CLIENT_C_METHODNAME))); client.setClientDeleteRecord(c.getInt(c.getColumnIndex(CLIENT_DELETE_RECORD))); client.setClientCloseCase(c.getInt(c.getColumnIndex(CLIENT_CLOSE_CASE))); client.setAdaptedMethodName( ((c.getString(c.getColumnIndex(CLIENT_ADAPTED_METHOD_NAME))).split("_")[0] != null) ? (c.getString(c.getColumnIndex(CLIENT_ADAPTED_METHOD_NAME))).split("_")[0] : ""); clients.add(client); c.moveToNext(); } return clients; } // add or update clients public void addOrUpdateClientAfterSync(ArrayList<Client> ClientList) { ContentValues values; for (Client client : ClientList) { values = new ContentValues(); values.put(CLIENT_C_NAME, client.getClientName()); values.put(CLIENT_C_MOBILENUMBER, client.getClientMobileNumber()); values.put(CLIENT_C_GENDER, client.getClientGender()); values.put(CLIENT_C_MARITALSTATUS, client.getClientMaritalStatus()); values.put(CLIENT_C_AGE, client.getClientAge()); values.put(CLIENT_C_PARITY, client.getClientParity()); values.put(CLIENT_C_LIFESTAGE, client.getClientLifeStage()); values.put(CLIENT_C_HEALTHWORKER, client.getHealthWorker()); values.put(CLIENT_C_SERVER_ID, client.getClientServerId()); values.put(CLIENT_C_MODIFIED_DATE, System.currentTimeMillis() / 1000); values.put(CLIENT_C_AGEYOUNGESTCHILD, client.getAgeYoungestChild()); values.put(CLIENT_C_METHODNAME, client.getMethodName()); values.put(CLIENT_C_HUSBANDNAME, client.getHusbandName()); values.put(CLIENT_CLOSE_CASE, client.getClientCloseCase()); values.put(CLIENT_DELETE_RECORD, client.getClientDeleteRecord()); values.put(CLIENT_ADAPTED_METHOD_NAME, (client.getAdaptedMethodName() != null) ? (((client.getAdaptedMethodName()).split("_")[0] != null) ? ((client.getAdaptedMethodName()).split("_")[0]) : "") : ""); values.put(CLIENT_ADAPTED_METHOD_TIME, System.currentTimeMillis() / 1000); if (client.getClientId() == -1) { long localId = isClientSyncedWithServer(client.getClientServerId(), client.getHealthWorker()); if (localId == -1) { // local data has been wiped out db.insertOrThrow(CLIENT_TABLE, null, values); } else { // not possible as there is no option to edit clients on server db.update(CLIENT_TABLE, values, CLIENT_C_ID + "=" + localId, null); } } else { // update the client serverID db.update(CLIENT_TABLE, values, CLIENT_C_ID + "=" + client.getClientId(), null); } } } public void updateClientSession(ArrayList<Client> ClientList) { ContentValues values; List<Long> sessionsToBeCorrected; for (Client client : ClientList) { sessionsToBeCorrected = checkSessionCorrected(client.getClientId(), client.getHealthWorker()); for (long localId : sessionsToBeCorrected) { values = new ContentValues(); values.put(CLIENT_TRACKER_C_CLIENT, client.getClientServerId()); values.put(CLIENT_TRACKER_C_CLIENTSTATUS, 1); if (localId > 0) { db.update(CLIENT_TRACKER_TABLE, values, CLIENT_TRACKER_C_ID + "=" + localId, null); } } } } public void updateClientCreatedStatus() { //Client clinet = getLastCreatedClient(); ContentValues values; values = new ContentValues(); values.put(CLIENT_LAST_CREATED, 0); values.put(CLIENT_C_MODIFIED_DATE, System.currentTimeMillis() / 1000); db.update(CLIENT_TABLE, values, CLIENT_LAST_CREATED + " = 1", null); int count = getLastCreatedClientCount(); //Log.d() System.out.println(count); } public void updateClientSession(Client client, long sessionId) { ContentValues values; values = new ContentValues(); if (client.getClientServerId() > 0) { values.put(CLIENT_TRACKER_C_CLIENT, client.getClientServerId()); values.put(CLIENT_TRACKER_C_CLIENTSTATUS, 1); db.update(CLIENT_TRACKER_TABLE, values, CLIENT_TRACKER_C_CLIENT + "=" + client.getClientId(), null); } else { deleteClientSession(sessionId); } } public List<Long> checkSessionCorrected(long clientId, String username) { String sql = "SELECT * FROM " + CLIENT_TRACKER_TABLE + " WHERE (" + CLIENT_TRACKER_C_CLIENTSTATUS + " = 0 AND " + CLIENT_TRACKER_C_CLIENT + " = ? AND " + CLIENT_TRACKER_C_USER + " = ? );"; long trackerId; Cursor c = db.rawQuery(sql, new String[] { Long.toString(clientId), username }); List<Long> sessionsToBeCorrected = new ArrayList<Long>(); if (c.getCount() == 0) { c.close(); return sessionsToBeCorrected; } else { c.moveToFirst(); while (c.isAfterLast() == false) { trackerId = c.getLong(c.getColumnIndex(CLIENT_TRACKER_C_ID)); sessionsToBeCorrected.add(trackerId); c.moveToNext(); } c.close(); return sessionsToBeCorrected; } } public void addOrUpdateClient(Client client, int isNewClient) { ContentValues values; values = new ContentValues(); values.put(CLIENT_C_NAME, client.getClientName()); values.put(CLIENT_C_MOBILENUMBER, client.getClientMobileNumber()); values.put(CLIENT_C_GENDER, client.getClientGender()); values.put(CLIENT_C_MARITALSTATUS, client.getClientMaritalStatus()); values.put(CLIENT_C_AGE, client.getClientAge()); values.put(CLIENT_C_PARITY, client.getClientParity()); values.put(CLIENT_C_LIFESTAGE, client.getClientLifeStage()); values.put(CLIENT_C_HEALTHWORKER, client.getHealthWorker()); values.put(CLIENT_C_SERVER_ID, client.getClientServerId()); values.put(CLIENT_C_MODIFIED_DATE, System.currentTimeMillis() / 1000); values.put(CLIENT_C_AGEYOUNGESTCHILD, client.getAgeYoungestChild()); if (isNewClient == 1) { values.put(CLIENT_C_METHODNAME, client.getMethodName()); } values.put(CLIENT_C_HUSBANDNAME, client.getHusbandName()); values.put(CLIENT_CLOSE_CASE, client.getClientCloseCase()); values.put(CLIENT_DELETE_RECORD, client.getClientDeleteRecord()); values.put(CLIENT_ADAPTED_METHOD_NAME, (client.getAdaptedMethodName() != null) ? (((client.getAdaptedMethodName()).split("_")[0] != null) ? ((client.getAdaptedMethodName()).split("_")[0]) : "") : ""); values.put(CLIENT_ADAPTED_METHOD_TIME, System.currentTimeMillis() / 1000); //values.put(CLIENT_LAST_CREATED, client.getClientDeleteRecord()); values.put(CLIENT_LAST_CREATED, isNewClient); if (client.getClientServerId() > 0) { db.update(CLIENT_TABLE, values, CLIENT_C_SERVER_ID + "=" + client.getClientServerId(), null); } else { db.update(CLIENT_TABLE, values, CLIENT_C_ID + "=" + client.getClientId(), null); } } public void updateClientAfterSync(Client client) { ContentValues values; values = new ContentValues(); values.put(CLIENT_CLOSE_CASE, client.getClientCloseCase()); values.put(CLIENT_DELETE_RECORD, client.getClientDeleteRecord()); if (client.getClientServerId() > 0) { db.update(CLIENT_TABLE, values, CLIENT_C_SERVER_ID + "=" + client.getClientServerId(), null); } else { db.update(CLIENT_TABLE, values, CLIENT_C_ID + "=" + client.getClientId(), null); } } // deleting clients newly created clients and replacing them with registered clients public void deleteUnregisteredClients(long clientId) { String s = CLIENT_C_ID + "=? AND (" + CLIENT_CLOSE_CASE + " = 1 OR " + CLIENT_DELETE_RECORD + " = 1 )"; String[] args = new String[] { String.valueOf(clientId) }; db.delete(CLIENT_TABLE, s, args); } // check if registered client or not, return local id public long isClientSyncedWithServer(long clientServerId, String username) { String sql = "SELECT * FROM " + CLIENT_TABLE + " WHERE " + CLIENT_C_HEALTHWORKER + " = ? AND " + CLIENT_C_SERVER_ID + " = ? ;"; Cursor c = db.rawQuery(sql, new String[] { username, Long.toString(clientServerId) }); if (c.getCount() == 0) { c.close(); return -1; } else { c.moveToLast(); long userId = c.getInt(c.getColumnIndex(CLIENT_C_ID)); c.close(); return userId; } } public long addStartClientSession(ClientSession session) { ContentValues values = new ContentValues(); values.put(CLIENT_TRACKER_C_START, session.getStartDateTime()); values.put(CLIENT_TRACKER_C_CLIENT, session.getClientId()); values.put(CLIENT_TRACKER_C_USER, session.getHealthWorker()); if (session.getIsSynced()) { values.put(CLIENT_TRACKER_C_CLIENTSTATUS, 1); } else { values.put(CLIENT_TRACKER_C_CLIENTSTATUS, 0); } Log.v(TAG, "Client Record added"); return db.insertOrThrow(CLIENT_TRACKER_TABLE, null, values); } public void addEndClientSession(long id, long endtime) { ContentValues values = new ContentValues(); values.put(CLIENT_TRACKER_C_END, endtime); db.update(CLIENT_TRACKER_TABLE, values, CLIENT_TRACKER_C_ID + " = ?", new String[] { Long.toString(id) }); } public ArrayList<ClientSession> getUnsentClientTrackers(String userName) { ClientSession clientSession; ArrayList<ClientSession> clientSessions = new ArrayList<ClientSession>(); String sql = "SELECT * FROM " + CLIENT_TRACKER_TABLE + " WHERE " + CLIENT_TRACKER_C_USER + " = ? AND " + CLIENT_TRACKER_C_END + " > 0;"; Cursor c = db.rawQuery(sql, new String[] { userName }); c.moveToFirst(); while (c.isAfterLast() == false) { clientSession = new ClientSession(); clientSession.setHealthWorker(c.getString(c.getColumnIndex(CLIENT_TRACKER_C_USER))); clientSession.setStartDateTime(c.getLong(c.getColumnIndex(CLIENT_TRACKER_C_START))); clientSession.setEndDateTime(c.getLong(c.getColumnIndex(CLIENT_TRACKER_C_END))); clientSession.setId(c.getLong(c.getColumnIndex(CLIENT_TRACKER_C_ID))); clientSession.setClientId(c.getLong(c.getColumnIndex(CLIENT_TRACKER_C_CLIENT))); if (c.getInt(c.getColumnIndex(CLIENT_TRACKER_C_CLIENTSTATUS)) > 0) clientSession.setIsSynced(true); else clientSession.setIsSynced(false); clientSessions.add(clientSession); c.moveToNext(); } return clientSessions; } public void deleteClientSession(long clientSessionId) { db.delete(CLIENT_TRACKER_TABLE, CLIENT_TRACKER_C_ID + " = ?", new String[] { Long.toString(clientSessionId) }); } }