private List <ActivityDto> GetThisMonthsActivities(DateTime dateOfInterest)
{
List <ActivityDto> thisMonthsActivities;
DateTime firstDayOfMonth = new DateTime(dateOfInterest.Year, dateOfInterest.Month, 1);
using (BudgetingEntities db = new BudgetingEntities())
{
thisMonthsActivities = (from a in db.Activities
join c in db.Categories on a.CategoryId equals c.CategoryId
where (a.DateOfActivity >= firstDayOfMonth ||
(a.Recurring == true &&
a.RecurringDay <= dateOfInterest.Day &&
a.DateOfActivity <= dateOfInterest)) &&
a.Active == true &&
c.UserId == UserId
select new ActivityDto
{
ActivityId = a.ActivityId,
CategoryId = a.CategoryId,
CategoryName = c.Name,
Amount = a.Amount,
Description = a.Description,
Recurring = a.Recurring ?? false,
RecurringDay = a.RecurringDay ?? 1,
DateOfActivity = a.DateOfActivity,
Pretax = a.Pretax ?? false,
Expenditure = a.Expenditure
})
.ToList();
}
return(thisMonthsActivities);
}
/// <summary>
///
/// </summary>
/// <param name="dateOfInterest"></param>
/// <returns></returns>
public List <BudgetPlanCategoryDto> GetMonthActivitiesByCategory(DateTime dateOfInterest)
{
DateTime firstDayOfMonth = new DateTime(dateOfInterest.Year, dateOfInterest.Month, 1);
BudgetPlanningService s = new BudgetPlanningService();
List <BudgetPlanCategoryDto> list = new List <BudgetPlanCategoryDto>();
using (BudgetingEntities db = new BudgetingEntities())
{
int budgetPlanId = s.GetSelectedBudgetPlan();
// initialize list
list = s.GetBudgetCategoryList(budgetPlanId);
foreach (BudgetPlanCategoryDto temp in list)
{
temp.Activities = new List <ActivityDto>();
}
list.Add(new BudgetPlanCategoryDto {
CategoryName = "Spending outside of budget",
Activities = new List <ActivityDto>()
});
// get all activities from this month
List <ActivityDto> thisMonthsActivities = GetThisMonthsActivities(dateOfInterest);
// sort this month's activities into the correct categories
foreach (ActivityDto dto in thisMonthsActivities)
{
if (dto.Pretax)
{
dto.Description = dto.Description + " (Pre-tax)";
}
BudgetPlanCategoryDto b = list.Find(x => x.CategoryId == dto.CategoryId);
if (b == null)
{
b = list.Find(x => x.CategoryId == 0);
if (b == null)
{
throw new Exception("Unable to add spending activity outside of budget plan.");
}
}
b.Activities.Add(dto);
}
// calculate spend information for each category
foreach (BudgetPlanCategoryDto c in list)
{
CalculateSpentAndRemaining(c);
// sort activities
c.Activities = c.Activities.OrderByDescending(x => x.DateOfActivity.Day).ToList();
}
// list sort
list = list.OrderByDescending(x => x.SpendingStatus).ToList();
}
return(list);
}
public void SaveActivityUpdate(ActivityDto dto)
{
using (BudgetingEntities db = new BudgetingEntities())
{
Activity a = db.Activities.Find(dto.ActivityId);
a.CategoryId = dto.CategoryId;
db.Entry(a).State = System.Data.Entity.EntityState.Modified;
db.SaveChanges();
}
}
public void DeleteActivity(int activityId)
{
using (BudgetingEntities db = new BudgetingEntities())
{
Activity a = db.Activities.Find(activityId);
db.Activities.Remove(a);
db.Entry(a).State = System.Data.Entity.EntityState.Deleted;
db.SaveChanges();
}
}
public void DeleteCategory(int categoryId)
{
using (BudgetingEntities db = new BudgetingEntities())
{
Category c = (from cat in db.Categories
where cat.CategoryId == categoryId
select cat)
.Single();
db.Categories.Remove(c);
db.SaveChanges();
// TODO catch errors
}
}
private BudgetPlanCategory GetBudgetPlanCategory(int budgetPlanCategoryId)
{
BudgetPlanCategory bpc;
using (BudgetingEntities db = new BudgetingEntities())
{
bpc = (from b in db.BudgetPlanCategories
where b.BudgetPlanCategoryId == budgetPlanCategoryId
select b)
.Single();
}
return(bpc);
}
public int DeleteBudgetPlanCategory(int budgetPlanCategoryId)
{
int budgetPlanId = 0;
using (BudgetingEntities db = new BudgetingEntities())
{
BudgetPlanCategory bpc = db.BudgetPlanCategories.Find(budgetPlanCategoryId);
budgetPlanId = bpc.BudgetPlanId;
db.BudgetPlanCategories.Remove(bpc);
db.SaveChanges();
}
return(budgetPlanId);
}
public int AddCategoryToBudgetPlan(BudgetPlanCategoryDto dto)
{
BudgetPlanCategory bc = new BudgetPlanCategory();
using (BudgetingEntities db = new BudgetingEntities())
{
//if (dto.CategoryId == 0)
//{
// // add to category table
// Category c = new Category();
// c.UserId = UserId;
// c.Name = dto.Name;
// db.Categories.Add(c);
// db.Entry(c).State = System.Data.Entity.EntityState.Added;
// db.SaveChanges();
// dto.CategoryId = c.CategoryId;
//}
// add to budget table
//decimal baseSalary = (from u in db.Users
// where u.UserId == UserId
// select u.BaseSalary)
// .Single() ?? 0;
//baseSalary = baseSalary / 12; // get month salary
bc.CategoryId = dto.CategoryId;
bc.BudgetPlanId = dto.BudgetPlanId;
bc.UsePercent = dto.UsePercent;
decimal tempVal;
if (dto.UsePercent)
{
if (Decimal.TryParse(dto.AllocatedPercentage, out tempVal))
{
bc.AllocatedPercentage = tempVal;
//bc.AllocatedAmount = (baseSalary == 0 ? null : (bc.AllocatedPercentage * baseSalary) / 100);
}
}
else
{
if (Decimal.TryParse(dto.AllocatedAmount, out tempVal))
{
bc.AllocatedAmount = tempVal;
//bc.AllocatedPercentage = (baseSalary == 0 ? null : (bc.AllocatedAmount / baseSalary) * 100);
}
}
db.BudgetPlanCategories.Add(bc);
db.Entry(bc).State = System.Data.Entity.EntityState.Added;
db.SaveChanges();
}
return(bc.BudgetPlanCategoryId);
}
public int CreateNewBudgetPlan(string name)
{
BudgetPlan bp = new BudgetPlan();
bp.Name = name;
bp.UserId = UserId;
using (BudgetingEntities db = new BudgetingEntities())
{
db.BudgetPlans.Add(bp);
db.Entry(bp).State = System.Data.Entity.EntityState.Added;
db.SaveChanges();
}
return(bp.BudgetPlanId);
}
public List <CategoryDto> GetUserCategories()
{
List <CategoryDto> list = new List <CategoryDto>();
using (BudgetingEntities db = new BudgetingEntities())
{
list = (from c in db.Categories
where c.UserId == UserId
select new CategoryDto
{
CategoryId = c.CategoryId,
Name = c.Name
})
.ToList();
}
return(list);
}
public List <LookupDto> GetBudgetPlanList()
{
List <LookupDto> list = new List <LookupDto>();
using (BudgetingEntities db = new BudgetingEntities())
{
list = (from b in db.BudgetPlans
where b.UserId == UserId
select new LookupDto
{
Id = b.BudgetPlanId,
Name = b.Name
})
.ToList();
}
return(list);
}
public void SaveBudgetPlanCategory(BudgetPlanCategoryDto dto)
{
using (BudgetingEntities db = new BudgetingEntities())
{
BudgetPlanCategory bpc = GetBudgetPlanCategory(dto.BudgetPlanCategoryId);
if (!dto.UsePercent)
{
bpc.AllocatedAmount = Decimal.Parse(dto.AllocatedAmount);
}
else
{
bpc.AllocatedPercentage = Decimal.Parse(dto.AllocatedPercentage);
}
db.Entry(bpc).State = System.Data.Entity.EntityState.Modified;
db.SaveChanges();
}
}
public BudgetPlanDto FillBudgetPlanDto(int budgetPlanId)
{
BudgetPlanDto dto = new BudgetPlanDto();
dto.BudgetPlanId = budgetPlanId;
dto.Description = null;
if (budgetPlanId != 0)
{
dto.Categories = GetBudgetCategoryList(budgetPlanId);
using (BudgetingEntities db = new BudgetingEntities())
{
dto.Description = (from bp in db.BudgetPlans
where bp.BudgetPlanId == budgetPlanId
select bp.Name)
.Single();
}
}
return(dto);
}
public void SaveActivity(ActivityDto dto)
{
Activity a = new Activity();
a.CategoryId = dto.CategoryId;
a.Amount = (dto.Amount ?? 0);
a.Expenditure = dto.Expenditure;
a.Recurring = dto.Recurring;
a.RecurringDay = dto.RecurringDay;
a.DateOfActivity = dto.DateOfActivity;
a.Description = dto.Description;
a.Active = true;
a.Pretax = dto.Pretax;
using (BudgetingEntities db = new BudgetingEntities())
{
db.Activities.Add(a);
db.Entry(a).State = System.Data.Entity.EntityState.Added;
db.SaveChanges();
}
}
public int GetSelectedBudgetPlan()
{
int budgetPlanId;
using (BudgetingEntities db = new BudgetingEntities())
{
try
{
budgetPlanId = (from b in db.BudgetPlans
where b.UserId == UserId &&
b.Selected == true
select b.BudgetPlanId)
.Single();
}
catch (Exception)
{
budgetPlanId = 0;
}
}
return(budgetPlanId);
}
public void SaveCategory(CategoryDto dto)
{
using (BudgetingEntities db = new BudgetingEntities())
{
Category c = new Category();
c.Name = dto.Name;
c.UserId = UserId;
if (dto.CategoryId == 0)
{
// add new
db.Categories.Add(c);
db.Entry(c).State = System.Data.Entity.EntityState.Added;
}
else
{
// update existing
c.CategoryId = dto.CategoryId;
db.Entry(c).State = System.Data.Entity.EntityState.Modified;
}
db.SaveChanges();
}
}
public List <LookupDto> GetCategoryList(int budgetPlanId, bool GetOnlyUnused = true)
{
List <LookupDto> list = new List <LookupDto>();
using (BudgetingEntities db = new BudgetingEntities())
{
var categoriesInBudgetPlan = (from bc in db.BudgetPlanCategories
where bc.BudgetPlanId == budgetPlanId
select bc)
.ToList();
var allUserCategories = (from c in db.Categories
where c.UserId == UserId
select new LookupDto
{
Id = c.CategoryId,
Name = c.Name
})
.ToList();
foreach (LookupDto l in allUserCategories)
{
// get all user categories that are not already a part of the budget plan
if (GetOnlyUnused)
{
if (!categoriesInBudgetPlan.Where(x => x.CategoryId == l.Id).Any())
{
list.Add(l);
}
}
else
{
list.Add(l);
}
}
list = list.OrderBy(x => x.Name).ToList();
}
return(list);
}
public UserDto GetUserOverallSummary()
{
using (BudgetingEntities db = new BudgetingEntities())
{
UserDto dto;
// get base user information
var dtos = (from u in db.Users
where u.UserId == UserId
select new UserDto
{
UserId = u.UserId,
BaseSalary = u.BaseSalary ?? 0,
ExpectedTaxPercentage = u.ExpectedTaxPerc ?? 0
})
.ToList();
if (!dtos.Any())
{
throw new Exception("Cannot find User with UserId = " + UserId);
}
if (dtos.Count > 1)
{
// TODO database shenanighans
}
dto = dtos.First();
dto.BaseSalary = dto.BaseSalary / 12;
// get activity information
var activities = GetThisMonthsActivities(DateTime.Now);
var pretaxActivities = activities
.Where(x => x.Pretax == true)
.Select(x => x.Amount)
.ToList();
decimal temp = 0;
foreach (int amount in pretaxActivities)
{
temp += amount;
}
dto.TotalPretaxSpending = temp;
decimal salaryAfterPretaxSpending = dto.BaseSalary - dto.TotalPretaxSpending;
dto.ExpectedTaxAmount = salaryAfterPretaxSpending * (dto.ExpectedTaxPercentage / 100);
dto.SalaryAfterTax = dto.BaseSalary - dto.TotalPretaxSpending - dto.ExpectedTaxAmount;
var totalExpenditures = activities
.Where(x => x.Expenditure == true)
.Select(x => x.Amount)
.ToList();
temp = 0;
foreach (int amount in totalExpenditures)
{
temp += amount;
}
dto.CurrentMonthSpendingTotal = temp;
var rev = activities
.Where(x => x.Pretax == false && x.Expenditure == false)
.Select(x => x.Amount)
.ToList();
temp = 0;
foreach (int amount in rev)
{
temp += amount;
}
dto.OtherRevenue = temp;
// get budget information
var budgetCategoryAllocation = (from b in db.BudgetPlanCategories
join p in db.BudgetPlans on b.BudgetPlanId equals p.BudgetPlanId
join c in db.Categories on b.CategoryId equals c.CategoryId
where c.UserId == UserId && p.Selected == true
select new
{
AllocatedAmount = b.AllocatedAmount,
AllocatedPercentage = b.AllocatedPercentage,
UsePercent = b.UsePercent
}
)
.ToList();
temp = 0;
foreach (var bca in budgetCategoryAllocation)
{
if (bca.UsePercent)
{
// budgeted amounts do NOT take pretax spending into account
temp += ((bca.AllocatedPercentage ?? 0) / 100) * (dto.BaseSalary - dto.ExpectedTaxAmount);
}
else
{
temp += bca.AllocatedAmount ?? 0;
}
}
dto.CurrentMonthBudgetedAmount = temp;
if (dto.CurrentMonthBudgetedAmount > 0)
{
dto.CurrentMonthSpendingPercentage = (dto.CurrentMonthSpendingTotal / dto.CurrentMonthBudgetedAmount) * 100;
dto.CurrentMonthRemainingAmount = dto.CurrentMonthBudgetedAmount - dto.CurrentMonthSpendingTotal + dto.OtherRevenue;
}
return(RoundToTwoDecimals(dto));
}
}
public List <BudgetPlanCategoryDto> GetBudgetCategoryList(int budgetPlanId)
{
List <BudgetPlanCategoryDto> list = new List <BudgetPlanCategoryDto>();
using (BudgetingEntities db = new BudgetingEntities())
{
var cats = (from bpc in db.BudgetPlanCategories
join cat in db.Categories on bpc.CategoryId equals cat.CategoryId
join u in db.Users on cat.UserId equals u.UserId
where bpc.BudgetPlanId == budgetPlanId
select new
{
BudgetPlanId = budgetPlanId,
Tax = u.ExpectedTaxPerc,
BaseSalary = u.BaseSalary / 12,
BudgetPlanCategoryId = bpc.BudgetPlanCategoryId,
CategoryId = cat.CategoryId,
CategoryName = cat.Name,
AllocatedAmount = bpc.AllocatedAmount,
AllocatedPercentage = bpc.AllocatedPercentage,
UsePercent = bpc.UsePercent
})
.ToList();
if (cats.Any())
{
decimal salaryAfterTaxes = (cats[0].BaseSalary ?? 0) * (100 - (cats[0].Tax ?? 0)) / 100;
foreach (var cat in cats)
{
decimal?amount = cat.AllocatedAmount;
decimal?perc = cat.AllocatedPercentage;
if (cat.UsePercent)
{
if (cat.BaseSalary == 0)
{
amount = null;
}
else
{
amount = ((cat.AllocatedPercentage ?? 0) / 100) * salaryAfterTaxes;
}
}
else
{
if (cat.BaseSalary == 0)
{
perc = null;
}
else
{
perc = ((cat.AllocatedAmount ?? 0) / salaryAfterTaxes) * 100;
}
}
list.Add(new BudgetPlanCategoryDto
{
BudgetPlanId = cat.BudgetPlanId,
BudgetPlanCategoryId = cat.BudgetPlanCategoryId,
CategoryId = cat.CategoryId,
CategoryName = cat.CategoryName,
AllocatedAmount = Decimal.Round(amount ?? 0, 2).ToString(),
AllocatedPercentage = Decimal.Round(perc ?? 0, 2).ToString(),
UsePercent = cat.UsePercent
});
}
}
}
list = list.OrderBy(x => x.CategoryName).ToList();
return(list);
}
