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