public GroupedInfoSheetGroups GetInfoSheetList(InfoSheet.CategoryFilter category, InfoSheet.ProFilter proFilter, string filter = null) { const string symbols = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ?"; var gg = new GroupedInfoSheetGroups(); var groups = (from char c in symbols let cs = c.ToString() select new InfoSheetGroup(cs, cs)).ToList(); var data = getAllSheetsForCategory(category, proFilter, filter); foreach (var d in data) { var fc = string.IsNullOrWhiteSpace(d.Title) ? "?" : d.Title[0].ToString().ToUpper(); if (!symbols.Contains(fc)) { fc = "?"; } var g = groups.FirstOrDefault(group => @group.GroupShortName == fc) ?? groups[groups.Count - 1]; g.Add(d); } for (var i = groups.Count - 1; i >= 0; i--) { if (groups[i].Count == 0) { groups.RemoveAt(i); } } gg.AddRange(groups); return(gg); }
public Tuple <int, int> GetInfoSheetCount(InfoSheet.CategoryFilter category, InfoSheet.ProFilter proFilter) { lock (locker) { using (var db = openConnexion()) { var all = db.ExecuteScalar <int>("SELECT COUNT(*) FROM [InfoSheet]"); var filtered = db.ExecuteScalar <int>(getQuery(category, proFilter, true)); return(new Tuple <int, int>(all, filtered)); } } }
private string getQuery(InfoSheet.CategoryFilter category, InfoSheet.ProFilter proFilter, bool forCount) { string pro; switch (proFilter) { case InfoSheet.ProFilter.All: pro = $"{(int)InfoSheet.ProFilter.Profesional},{(int)InfoSheet.ProFilter.Personal}"; break; case InfoSheet.ProFilter.Profesional: pro = $"{(int)InfoSheet.ProFilter.Profesional}";; break; case InfoSheet.ProFilter.Personal: pro = $"{(int)InfoSheet.ProFilter.Personal}"; break; default: throw new ArgumentOutOfRangeException(nameof(proFilter), proFilter, null); } string cat; switch (category) { case InfoSheet.CategoryFilter.All: cat = $"{(int)InfoSheet.CategoryFilter.Login},{(int)InfoSheet.CategoryFilter.Note},{(int)InfoSheet.CategoryFilter.Misc}"; break; case InfoSheet.CategoryFilter.Login: cat = $"{(int)InfoSheet.CategoryFilter.Login}"; break; case InfoSheet.CategoryFilter.Note: cat = $"{(int)InfoSheet.CategoryFilter.Note}"; break; case InfoSheet.CategoryFilter.Misc: cat = $"{(int)InfoSheet.CategoryFilter.Misc}"; break; default: throw new ArgumentOutOfRangeException(nameof(category), category, null); } var cols = forCount ? "COUNT(*)" : "*"; var query = $"SELECT {cols} FROM [InfoSheet] WHERE [PRO] IN({pro}) AND [Category] IN({cat}) ORDER BY [Title]"; return(query); }
private IEnumerable <InfoSheet> getAllSheetsForCategory(InfoSheet.CategoryFilter category, InfoSheet.ProFilter proFilter, string filter = null) { var aps = ServiceLocator.Current.GetInstance <IAppSettings>(); lock (locker) { InfoSheet.Loading = true; try { using (var db = openConnexion()) { var sheets = db.Query <InfoSheet>(getQuery(category, proFilter, false)).Select(s => { var x = s.ToUnencryptedSheet(); x.IsModified = false; return(x); }); // no filter if (string.IsNullOrWhiteSpace(filter)) { return(sheets.ToList()); } // filter on title if (!aps.SearchInNote && !aps.SearchEverywhere) { return(sheets.Where(s => s.Title.Contains(filter, StringComparison.CurrentCultureIgnoreCase)).ToList()); } // filter on title and note if (aps.SearchInNote && !aps.SearchEverywhere) { return(sheets.Where(s => s.Title.Contains(filter, StringComparison.CurrentCultureIgnoreCase) || s.Note.Contains(filter, StringComparison.CurrentCultureIgnoreCase)).ToList()); } // filter on everything return(sheets.Where(s => s.Title.Contains(filter, StringComparison.CurrentCultureIgnoreCase) || s.Note.Contains(filter, StringComparison.CurrentCultureIgnoreCase) || s.Login.Contains(filter, StringComparison.CurrentCultureIgnoreCase) || s.Password.Contains(filter, StringComparison.CurrentCultureIgnoreCase) || s.UrlOrName.Contains(filter, StringComparison.CurrentCultureIgnoreCase)).ToList()); } } finally { InfoSheet.Loading = false; } } }