public List<Tuple<string, string, decimal>> GetCycleSummary(int monthAgo) { DateTime cycleStart = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1); cycleStart = cycleStart.AddMonths(-monthAgo); DateTime cycleEnd = cycleStart.AddMonths(1); using (ShowcaseDB db = new ShowcaseDB()) { var l = (from e in db.Expenses where e.TransactionDate >= cycleStart && e.TransactionDate < cycleEnd group e by e.Category into grp orderby grp.Key select new { grp.Key, grp }).ToList(); //var l2 = from cc in CategoryCharToName.Keys // join c in l on cc equals c.Key into g // from c in g.DefaultIfEmpty() // select Tuple.Create(cc, CategoryCharToName[cc], c != null ? c.grp.Sum(e => e.Amount) : 0); var ret = l.Select(g => Tuple.Create<string, string, decimal>(g.Key, CategoryCharToName[g.Key], g.grp.Sum(e => e.Amount))).ToList(); if (ret.Count == 0) ret.Add(Tuple.Create("1", CategoryCharToName["1"], 0M)); return ret; } }
public List<Tuple<string, DateTime>> GetPaymentMethodLastDates() { using (ShowcaseDB db = new ShowcaseDB()) { return (from e in db.Expenses group e by e.PaymentMethod into grp orderby grp.Key select new { grp.Key, grp }).AsEnumerable().Select(g => Tuple.Create<string, DateTime>(g.Key, g.grp.Max(e => e.TransactionDate))).ToList(); } }
public List<Expense> GetTransactions(int monthAgo, string category) { DateTime cycleStart = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1); cycleStart = cycleStart.AddMonths(-monthAgo); DateTime cycleEnd = cycleStart.AddMonths(1); using (ShowcaseDB db = new ShowcaseDB()) { var l = (from e in db.Expenses where e.TransactionDate >= cycleStart && e.TransactionDate < cycleEnd && (string.IsNullOrEmpty(category) || e.Category == category) orderby e.TransactionDate descending select e).ToList(); return l; } }
public string ProcessCSVs(string csv, string paymentMethod) { if (!csv.HasContent()) return null; int skipCount = 0; int badHintCount = 0; using (ShowcaseDB db = new ShowcaseDB()) { CsvContext cc = new CsvContext(); var csvFlags = new CsvFileDescription() { FirstLineHasColumnNames = false, EnforceCsvColumnAttribute = true }; var csvItems = cc.Read<AddTransactionItem>(csv.ToReader(), csvFlags).ToList(); //add expense items foreach (var itm in csvItems) { if (!db.Expenses.Any(e => e.TransactionDate == itm.TransactionDate && e.Description == itm.Description && e.Amount == itm.Amount && e.PaymentMethod == paymentMethod)) _expToAdd.Add(new Expense() { TransactionDate = itm.TransactionDate, Description = itm.Description, Amount = itm.Amount, Category = itm.Category, PaymentMethod = paymentMethod }); else skipCount++; } //add hints foreach (var itm in csvItems) { string kw = itm.NewKeyword; if (kw.HasContent()) { if (db.Expenses.Any(e => e.Description.Contains(kw) && e.Category != itm.Category) || db.Hints.Any(h => h.Keyword == kw) || _hintToAdd.Any(h => h.Keyword == kw)) badHintCount++; else _hintToAdd.Add(new Hint() { Keyword = itm.NewKeyword.Trim().ToLower(), Category = itm.Category }); } } Save(); } return string.Format("Duplicate expenses: {0}\r\nAmbiguous hints: {1}\r\n", skipCount, badHintCount); }
private string PredictCategory(string desc) { if (_hints == null) { using (var db = new ShowcaseDB()) _hints = (from h in db.Hints select h).ToList(); } desc = desc.ToLower(); foreach (var h in _hints) { Regex regex = new Regex(@"\b" + h.Keyword + @"\b"); if (regex.IsMatch(desc)) return h.Category; } return "UNKNOWN"; }
public void Save() { using (var db = new ShowcaseDB()) { foreach (Expense e in _expToAdd) db.Expenses.Add(e); foreach (Hint h in _hintToAdd) db.Hints.Add(h); db.SaveChanges(); } _expToAdd.Clear(); _hintToAdd.Clear(); }