private List<SubscriptionItem> LoadSubscriptionItems(SQLiteConnection connection)
{
var result = new List<SubscriptionItem>();
using (var statement = connection.Prepare(@"SELECT ID, SORT_ID, TITLE, UNREAD_COUNT, URL, HTML_URL, ICON_URL, FIRST_ITEM_MSEC FROM SUB_ITEM;"))
{
while (statement.Step() == SQLiteResult.ROW)
{
var item = new SubscriptionItem
{
Id = (string)statement[0],
SortId = (string)statement[1],
Title = (string)statement[2],
PageTitle = (string)statement[2],
UnreadCount = (long)statement[3],
Url = (string)statement[4],
HtmlUrl = (string)statement[5],
IconUrl = (string)statement[6],
FirstItemMsec = (long)statement[7]
};
result.Add(item);
}
}
return result;
}
public async Task<List<TreeItemBase>> LoadSubscriptionsAsync()
{
var stopwatch = Stopwatch.StartNew();
var tags = await _apiClient.GetTagsAsync();
var subscriptions = await _apiClient.GetSubscriptionsAsync();
var unreadCount = await _apiClient.GetUnreadCountAsync();
stopwatch.Stop();
_telemetryClient.TrackMetric(TemetryMetrics.GetSubscriptionsTotalResponseTime, stopwatch.Elapsed.TotalSeconds);
var unreadCountDictionary = new Dictionary<string, int>();
foreach (var unreadcount in unreadCount.UnreadCounts)
{
unreadCountDictionary[unreadcount.Id] = unreadcount.Count;
}
var catsQuery = from tag in tags.Tags
where CategoryRegex.IsMatch(tag.Id)
select new CategoryItem
{
Id = tag.Id,
SortId = tag.SortId,
};
var categories = catsQuery.ToList();
foreach (var categoryItem in categories)
{
var subsQuery = from s in subscriptions.Subscriptions
where s.Categories != null
&& s.Categories.Any(c => String.Equals(c.Id, categoryItem.Id, StringComparison.OrdinalIgnoreCase))
orderby s.Title// descending
select CreateSubscriptionItem(s, unreadCountDictionary);
categoryItem.Subscriptions = new List<SubscriptionItem>(subsQuery);
categoryItem.Title = (from s in subscriptions.Subscriptions
from c in s.Categories
where String.Equals(c.Id, categoryItem.Id, StringComparison.OrdinalIgnoreCase)
select HtmlUtilities.ConvertToText(c.Label)).FirstOrDefault();
categoryItem.UnreadCount = categoryItem.Subscriptions.Sum(t => t.UnreadCount);
var readAllItem = new SubscriptionItem
{
Id = categoryItem.Id,
SortId = categoryItem.SortId,
IconUrl = ReadAllIconUrl,
Title = Strings.Resources.ReadAllSubscriptionItem,
PageTitle = categoryItem.Title,
UnreadCount = categoryItem.UnreadCount
};
categoryItem.Subscriptions.Insert(0, readAllItem);
}
// hide empty groups
categories.RemoveAll(c => c.Subscriptions.Count == 0);
var singleItems = (from s in subscriptions.Subscriptions
where s.Categories == null || s.Categories.Length == 0
orderby s.Title
select CreateSubscriptionItem(s, unreadCountDictionary)).ToList();
var allItems = new List<TreeItemBase>(categories.OrderBy(c => c.Title));
allItems.AddRange(singleItems);
var totalUnreadCount = allItems.Sum(t => t.UnreadCount);
var readAllRootItem = new SubscriptionItem
{
Id = SpecialTags.Read,
IconUrl = ReadAllIconUrl,
Title = Strings.Resources.ReadAllSubscriptionItem,
PageTitle = Strings.Resources.ReadAllSubscriptionItem,
UnreadCount = totalUnreadCount
};
allItems.Insert(0, readAllRootItem);
if (_settingsService.HideEmptySubscriptions)
{
HideEmpty(allItems);
}
return allItems;
}
private void SaveSubscriptionItems(SubscriptionItem[] subItems, SQLiteConnection connection)
{
if (subItems.Length == 0) return;
var skipIdList = new List<string>(subItems.Length);
using (
var statement =
connection.Prepare(
@"INSERT INTO SUB_ITEM(ID, SORT_ID, TITLE, UNREAD_COUNT, URL, HTML_URL, ICON_URL, FIRST_ITEM_MSEC)
VALUES(@ID, @SORT_ID, @TITLE, @UNREAD_COUNT, @URL, @HTML_URL, @ICON_URL, @FIRST_ITEM_MSEC);"))
{
foreach (var item in subItems)
{
if (skipIdList.Contains(item.Id))
continue;
statement.Bind("@ID", item.Id);
statement.Bind("@SORT_ID", item.SortId);
statement.Bind("@TITLE", item.Title);
statement.Bind("@UNREAD_COUNT", item.UnreadCount);
statement.Bind("@URL", item.Url);
statement.Bind("@HTML_URL", item.HtmlUrl);
statement.Bind("@ICON_URL", item.IconUrl);
statement.Bind("@FIRST_ITEM_MSEC", item.FirstItemMsec);
statement.Step();
skipIdList.Add(item.Id);
// Resets the statement, to that it can be used again (with different parameters).
statement.Reset();
statement.ClearBindings();
}
}
}