public Cursor getBookmarkCursor(String limit) {
SQLiteDatabase db = mNavigationDBHelper.getReadableDatabase();
String[] fields =
new String[] {
SCHOOL_DISTRICT_TABLE + "." + POINT_ID,
NAME,
SCHOOL_KEY,
PARENT_ID,
TYPE,
TITLEURL,
BOOKMARK,
STATUS
};
String queryBuilder =
SQLiteQueryBuilder.buildQueryString(
false,
SCHOOL_DISTRICT_TABLE + ", " + SCHOOL_BOOKMARK_TABLE,
fields,
SCHOOL_DISTRICT_TABLE + "." + POINT_ID + "=" + SCHOOL_BOOKMARK_TABLE + "." + POINT_ID,
null,
null,
NAME + " ASC",
limit);
return db.rawQuery(queryBuilder, null);
}
public Cursor getNavigationCursor(int parent_id, String limit) {
SQLiteDatabase db = mNavigationDBHelper.getReadableDatabase();
String queryBuilder =
SQLiteQueryBuilder.buildQueryString(
false,
SCHOOL_DISTRICT_TABLE,
null,
PARENT_ID + "=" + parent_id,
null,
null,
NAME + " ASC",
limit);
return db.rawQuery(queryBuilder, null);
}
private boolean pointExists(int point_id) {
SQLiteDatabase db = mNavigationDBHelper.getReadableDatabase();
Cursor cursor =
db.query(
SCHOOL_BOOKMARK_TABLE,
null,
POINT_ID_WHERE,
new String[] {String.valueOf(point_id)},
null,
null,
null);
int count = cursor.getCount();
cursor.close();
if (count > 0) {
return true;
} else {
return false;
}
}
private boolean isNavigationListItemExists(int point_id) {
SQLiteDatabase db = mNavigationDBHelper.getReadableDatabase();
Cursor cursor =
db.query(
NAVIGATION_TYPE_TABLE,
null,
POINT_ID_WHERE,
new String[] {point_id + ""},
null,
null,
null);
int count = cursor.getCount();
cursor.close();
if (count > 0) {
return true;
} else {
return false;
}
}
private boolean schoolDistrictExist(int point_id) {
SQLiteDatabase db = mNavigationDBHelper.getReadableDatabase();
Cursor cursor =
db.query(
SCHOOL_DISTRICT_TABLE,
null,
POINT_ID_WHERE,
new String[] {point_id + ""},
null,
null,
NAME + " ASC");
int count = cursor.getCount();
cursor.close();
if (count > 0) {
return true;
} else {
return false;
}
}
public NavigationItem getSchoolItem(String schoolkey) {
NavigationItem item = new NavigationItem();
SQLiteDatabase db = mNavigationDBHelper.getReadableDatabase();
String queryBuilder =
SQLiteQueryBuilder.buildQueryString(
false,
SCHOOL_DISTRICT_TABLE,
null,
SCHOOL_KEY + " = '" + schoolkey + "'",
null,
null,
null,
null);
Cursor result = db.rawQuery(queryBuilder, null);
if (null != result) {
if (result.moveToFirst()) {
item = retrieveNavigationItem(result);
}
result.close();
}
return item;
}
public String getNameByID(int point_id) {
String name = "";
SQLiteDatabase db = mNavigationDBHelper.getReadableDatabase();
String queryBuilder =
SQLiteQueryBuilder.buildQueryString(
false,
SCHOOL_DISTRICT_TABLE,
new String[] {NAME},
POINT_ID + "=" + point_id,
null,
null,
null,
null);
Cursor result = db.rawQuery(queryBuilder, null);
if (null != result) {
result.moveToFirst();
int name_index = result.getColumnIndex(NAME);
name = result.getString(name_index);
result.close();
}
return name;
}
public Cursor searchItemsByKey(String key, String limit) {
SQLiteDatabase db = mNavigationDBHelper.getReadableDatabase();
String queryString =
SQLiteQueryBuilder.buildQueryString(
false,
SCHOOL_DISTRICT_TABLE,
null,
NAME
+ " LIKE '%"
+ key
+ "%' AND "
+ STATUS
+ "="
+ "1"
+ " AND "
+ TYPE
+ "= 'school'",
null,
null,
POINT_ID + " DESC",
limit);
return db.rawQuery(queryString, null);
}
public List<NavigationListItem> getNavigationLists() {
SQLiteDatabase db = mNavigationDBHelper.getReadableDatabase();
List<NavigationListItem> list = new ArrayList<NavigationListItem>();
String queryBuilder =
SQLiteQueryBuilder.buildQueryString(
false, NAVIGATION_TYPE_TABLE, null, null, null, null, POINT_ID + " ASC", null);
Cursor result = db.rawQuery(queryBuilder, null);
if (result != null) {
result.moveToFirst();
if (result.getCount() > 0) {
while (!result.isAfterLast()) {
NavigationListItem item = new NavigationListItem();
int type_index = result.getColumnIndex(TYPE);
int point_id_index = result.getColumnIndex(POINT_ID);
item.type = result.getString(type_index);
item.point_id = result.getInt(point_id_index);
list.add(item);
result.moveToNext();
}
}
result.close();
}
return list;
}