public SparseArray<String> getUnreadItemCountForFolder(Context mContext) {
String buildSQL =
"SELECT f.rowid, COUNT("
+ RSS_ITEM_RSSITEM_ID
+ ")"
+ " FROM "
+ RSS_ITEM_TABLE
+ " rss "
+ " JOIN "
+ SUBSCRIPTION_TABLE
+ " st ON rss."
+ RSS_ITEM_SUBSCRIPTION_ID
+ " = st.rowid"
+ " JOIN "
+ FOLDER_TABLE
+ " f ON st."
+ SUBSCRIPTION_FOLDER_ID
+ " = f.rowid"
+ " WHERE "
+ RSS_ITEM_READ_TEMP
+ " != 1 "
+ " GROUP BY f."
+ FOLDER_LABEL_ID;
SparseArray<String> values = getSparseArrayFromSQL(buildSQL, 0, 1);
values.put(
ALL_UNREAD_ITEMS.getValue(),
new UnreadFolderCount(mContext, ALL_UNREAD_ITEMS.getValueString()).getText());
values.put(
ALL_STARRED_ITEMS.getValue(),
new UnreadFolderCount(mContext, ALL_STARRED_ITEMS.getValueString()).getText());
return values;
}
public Cursor getAllSubscriptionForFolder(String ID_FOLDER, boolean onlyUnread) {
// String buildSQL = "SELECT sc.rowid as _id, sc.* " +
String buildSQL =
"SELECT sc.rowid as _id, sc."
+ SUBSCRIPTION_HEADERTEXT
+ ", sc."
+ SUBSCRIPTION_ID
+ ", sc."
+ SUBSCRIPTION_FAVICON_URL
+ " FROM "
+ SUBSCRIPTION_TABLE
+ " sc "
+ " LEFT OUTER JOIN folder f ON sc.folder_idfolder = f.rowid ";
if (ID_FOLDER.equals("-11")) // Starred
{
buildSQL +=
" JOIN "
+ RSS_ITEM_TABLE
+ " rss ON sc.rowid = rss."
+ RSS_ITEM_SUBSCRIPTION_ID
+ " WHERE rss."
+ RSS_ITEM_STARRED_TEMP
+ " = 1"
+ " GROUP BY sc.rowid "
+ " HAVING COUNT(*) > 0";
} else if (onlyUnread
|| ID_FOLDER.equals(
ALL_UNREAD_ITEMS.getValueString()) /*ID_SUBSCRIPTION.matches("-10|-11")*/) {
buildSQL +=
" JOIN "
+ RSS_ITEM_TABLE
+ " rss ON sc.rowid = rss."
+ RSS_ITEM_SUBSCRIPTION_ID
+ " WHERE f.rowid = "
+ ID_FOLDER
+ " AND rss."
+ RSS_ITEM_READ_TEMP
+ " != 1"
+ " GROUP BY sc.rowid "
+ " HAVING COUNT(*) > 0";
if (ID_FOLDER.equals(ALL_UNREAD_ITEMS.getValueString()))
buildSQL =
buildSQL.replace(
"f.rowid = " + ID_FOLDER + " AND",
""); // Remove to ID stuff because i want the result of all feeds where are unread
// items in
} else buildSQL += "WHERE f.rowid = " + ID_FOLDER;
if (DATABASE_DEBUG_MODE)
Log.d("DB_HELPER", "getAllSub_SubscriptionForSubscription SQL: " + buildSQL);
return database.rawQuery(buildSQL, null);
}
public String getAllItemsIdsForFolderSQL(
String ID_FOLDER, boolean onlyUnread, SORT_DIRECTION sortDirection) {
String buildSQL = "SELECT " + RSS_ITEM_RSSITEM_ID + " FROM " + RSS_ITEM_TABLE;
if (!(ID_FOLDER.equals(ALL_UNREAD_ITEMS.getValueString())
|| ID_FOLDER.equals(ALL_STARRED_ITEMS.getValueString())
|| ID_FOLDER.equals(
ALL_ITEMS
.getValueString()))) // Wenn nicht Alle Artikel ausgewaehlt wurde (-10) oder (-11)
// fuer Starred Feeds
{
buildSQL +=
" WHERE subscription_id_subscription IN "
+ "(SELECT sc.rowid "
+ "FROM subscription sc "
+ "JOIN folder f ON sc."
+ SUBSCRIPTION_FOLDER_ID
+ " = f.rowid "
+ "WHERE f.rowid = "
+ ID_FOLDER
+ ")";
if (onlyUnread) buildSQL += " AND " + RSS_ITEM_READ_TEMP + " != 1";
}
// else if(ID_FOLDER.equals(SubscriptionExpandableListAdapter.ALL_UNREAD_ITEMS) &&
// onlyUnread)//only unRead should only be null when testing the size of items
else if (ID_FOLDER.equals(ALL_UNREAD_ITEMS.getValueString()))
buildSQL += " WHERE " + RSS_ITEM_STARRED_TEMP + " != 1 AND " + RSS_ITEM_READ_TEMP + " != 1";
// else if(ID_FOLDER.equals(SubscriptionExpandableListAdapter.ALL_UNREAD_ITEMS))
// buildSQL += " WHERE " + RSS_ITEM_STARRED + " != 1";
// else if(ID_FOLDER.equals(SubscriptionExpandableListAdapter.ALL_STARRED_ITEMS) && onlyUnread)
// buildSQL += " WHERE " + RSS_ITEM_STARRED_TEMP + " = 1 AND " + RSS_ITEM_READ_TEMP + " != 1";
else if (ID_FOLDER.equals(ALL_STARRED_ITEMS.getValueString()))
buildSQL += " WHERE " + RSS_ITEM_STARRED_TEMP + " = 1";
buildSQL += " ORDER BY " + RSS_ITEM_PUBDATE + " " + sortDirection.toString();
// buildSQL += " WHERE starred = 1";
if (DATABASE_DEBUG_MODE) Log.d("DB_HELPER", "getAllFeedData SQL: " + buildSQL);
return buildSQL;
}
