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 (var db = new DB_76984_hostedEntities())
{
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 ExpensesResponse GetMonthlyData(int year, int month)
{
DateTime cycleStart = new DateTime(year, month, 1);
DateTime cycleEnd = cycleStart.AddMonths(1);
DateTime yearStart = new DateTime(year, 1, 1);
using (var db = new DB_76984_hostedEntities())
{
var exps = (from e in db.Expenses
where e.TransactionDate >= cycleStart && e.TransactionDate < cycleEnd
orderby e.TransactionDate descending
select e);
var cats = (from e in exps
group e by e.Category into g
orderby g.Key
select new ExpensesResponse.Category { ID = g.Key, Amount = g.Sum(x => x.Amount) }).ToArray();
var catsYtm = (from e in db.Expenses.Where(x => x.TransactionDate >= yearStart && x.TransactionDate < cycleEnd)
group e by e.Category into g
orderby g.Key
select new { ID = g.Key, Amount = g.Sum(x => x.Amount) }).ToArray();
foreach(var c in cats)
{
c.YTM = catsYtm.Where(x => x.ID == c.ID).Single().Amount;
}
return new ExpensesResponse
{
Expenses = exps.ToArray(),
Categories = cats
};
}
}
public LastCharge[] GetLastChargesDates()
{
using (var db = new DB_76984_hostedEntities())
{
return (from e in db.Expenses
group e by e.PaymentMethod into g
orderby g.Max(x => x.TransactionDate) descending
select new LastCharge { Method = g.Key, Date = g.Max(x => x.TransactionDate) }).ToArray();
}
}
public List<Tuple<string, DateTime>> GetPaymentMethodLastDates()
{
using (var db = new DB_76984_hostedEntities())
{
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 (var db = new DB_76984_hostedEntities())
{
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;
}
}
private string PredictCategory(string desc, decimal amount)
{
if (_hints == null)
{
using (var db = new DB_76984_hostedEntities())
_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", RegexOptions.IgnoreCase);
if (regex.IsMatch(desc))
return h.Category;
}
return Math.Abs(amount) < 35 ? "1" : "";
}
private IEnumerable<ExpenseEx> FilterDuplicates(IEnumerable<ExpenseEx> expenses)
{
using (var db = new DB_76984_hostedEntities())
{
var descs = expenses.Select(x => x.Description);
var potential_dups = db.Expenses.Where(x => descs.Contains(x.Description)).ToList();
return expenses.Where(x => potential_dups.Any(y => y.TransactionDate == x.TransactionDate && y.Description == x.Description
&& y.Amount == x.Amount && y.PaymentMethod == x.PaymentMethod)).AsEnumerable();
}
}
public ExpensesResponse Search([FromBody] SearchRequest req)
{
if (req.endDate.HasValue) req.endDate = req.endDate.Value.AddDays(1);
if (req.selectedCategories == null) req.selectedCategories = new string[0];
using (var db = new DB_76984_hostedEntities())
{
bool useCategories = req.selectedCategories.Length > 0;
var exps = (from e in db.Expenses
where (req.startDate == null || e.TransactionDate >= req.startDate) &&
(req.endDate == null || e.TransactionDate < req.endDate) &&
(string.IsNullOrEmpty(req.textToSearch) || e.Description.Contains(req.textToSearch)) &&
(!useCategories || req.selectedCategories.Contains(e.Category))
orderby e.TransactionDate descending
select e);
var cats = (from e in exps
group e by e.Category into g
orderby g.Key
select new { ID = g.Key, Amount = g.Sum(x => x.Amount) }).AsEnumerable();
return new ExpensesResponse
{
Expenses = exps.ToArray(),
Categories = cats.Select(c => new ExpensesResponse.Category { ID = c.ID, Amount = (int)Math.Round(c.Amount, MidpointRounding.AwayFromZero) }).ToArray()
};
}
}
public SaveResponse Save([FromBody] ExpenseEx[] expenses)
{
//Transaction submitted from <input type=date> where the timezone is GMT. Convert back to local.
foreach (var exp in expenses)
{
exp.TransactionDate = exp.TransactionDate.ToLocalTime();
exp.Description = Util.LimitLen(exp.Description, 100);
}
var dups = FilterDuplicates(expenses);
using (var db = new DB_76984_hostedEntities())
{
var existingHints = db.Hints.Select(x => x.Keyword).ToArray();
foreach (Expense e in expenses.Except(dups))
{
db.Expenses.Add(new Expense()
{
TransactionDate = e.TransactionDate,
Description = e.Description,
Amount = e.Amount,
Category = e.Category,
PaymentMethod = e.PaymentMethod
});
}
List<string> newHints = new List<string>();
List<string> badHints = new List<string>();
foreach (var e in expenses.Where(x => x.Hint.HasContent()))
{
//chars that need escaping for use in Regex
string escs = @".^$+?()[{\|";
StringBuilder sb = new StringBuilder();
foreach (char c in e.Hint.Trim())
{
if (escs.Contains(c)) sb.Append(@"\");
sb.Append(c);
}
string hint = sb.ToString();
if (!existingHints.Contains(hint) && !newHints.Contains(hint))
{
var re = new Regex(@"\b" + hint + @"\b");
//check for conflict
var firstConflict = db.Expenses.Where(x => x.Description.Contains(hint) && x.Category != e.Category).AsEnumerable()
.FirstOrDefault(x => re.IsMatch(x.Description));
if (firstConflict != null)
{
badHints.Add(string.Format("\"{0}\" matches \"{1}\" but is of category {2}", firstConflict.Description, hint, firstConflict.Category));
}
else
{
db.Hints.Add(new Hint() { Category = e.Category, Keyword = hint });
newHints.Add(hint);
}
}
}
try
{
db.SaveChanges();
}
catch (Exception ex)
{
int i = 0;
}
return new SaveResponse { badHints = badHints.ToArray(), duplicateExpenses = dups.ToArray() };
}
}