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