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(); } } }