internal static decimal CalculateTotalIncome(int userID)
{
using (var ctx = new BudgetContext())
{
return(ctx.Incomes.Where(i => i.User.UserID == userID).AsEnumerable().Sum(i => i.Amount));
}
}
/// <summary>
/// Try to add Expense Type to given Category with given name to user
/// </summary>
/// <param name="newETypeName"></param>
/// <param name="userID"></param>
/// <param name="cat"></param>
///<returns>returns 1 if success, 0 if fail because ExpenseType already exists</returns>
internal static int AddExpenseTypetoCategory(string newETypeName, int userID, string cat)
{
string nameCaseCorrected = CultureInfo.CurrentCulture.TextInfo.ToTitleCase(newETypeName.ToLower());
using (var ctx = new BudgetContext())
{
Console.WriteLine("\n\n\n\n\n" + cat + "\n\n\n\n\n");
ExpenseType eType = ctx.ExpenseTypes.FirstOrDefault(et => et.ExpenseTypeName == nameCaseCorrected && et.ExpenseCategory.User_ID == userID);
ExpenseCategory eCat = ctx.ExpenseCategories.FirstOrDefault(ec => ec.ExpenseCategoryName == cat && ec.User_ID == userID);
if (eType == null)
{
ctx.ExpenseTypes.Add(new ExpenseType()
{
ExpenseTypeName = nameCaseCorrected, ExpenseCategory = eCat
});
ctx.SaveChanges();
return(1);
}
return(0);
}
}
internal static void DeleteIncome(int incID)
{
using (var ctx = new BudgetContext())
{
ctx.Incomes.Remove(ctx.Incomes.Find(incID));
ctx.SaveChanges();
}
}
internal static void DeleteExpense(int expID)
{
using (var ctx = new BudgetContext())
{
ctx.Expenses.Remove(ctx.Expenses.Find(expID));
ctx.SaveChanges();
}
}
internal static decimal CalculateToDateIncome(int userID)
{
using (var ctx = new BudgetContext())
{
return(ctx.Incomes.Where(i => i.User.UserID == userID &&
i.Date.CompareTo(DateTime.Now) < 0).AsEnumerable().Sum(i => i.Amount));
}
}
internal static String GetAccountType(int userID)
{
using (var ctx = new BudgetContext())
{
User u = ctx.Users.Find(userID);
return(u.UserType.UserTypeName);
}
}
internal static List <String> GetEmployeeNames(int userID)
{
using (var ctx = new BudgetContext())
{
return((from u in ctx.Users
where u.Employer.UserID == userID
select u.UserName).ToList <String>());
}
}
internal static List <Expense> GetExpensesOfType(string et, int userID)
{
using (var ctx = new BudgetContext())
{
return((from e in ctx.Expenses
where e.ExpenseType.ExpenseTypeName == et && e.User.UserID == userID
select e).ToList <Expense>());
}
}
internal static List <string> GetExpenseTypesOfCat(string eCat, int userID)
{
using (var ctx = new BudgetContext())
{
return((from et in ctx.ExpenseTypes
where et.ExpenseCategory.User_ID == userID && et.ExpenseCategory.ExpenseCategoryName == eCat
select et.ExpenseTypeName).ToList <string>());
}
}
internal static List <Income> GetIncomesOfType(string it, int userID)
{
using (var ctx = new BudgetContext())
{
return((from i in ctx.Incomes
where i.IncomeType.IncomeTypeName == it && i.User.UserID == userID
select i).ToList <Income>());
}
}
internal static decimal CalculateTotalIncome(int userID, DateTime dt)
{
using (var ctx = new BudgetContext())
{
return(ctx.Incomes.Where(i => i.User.UserID == userID &&
i.Date.Year == dt.Year &&
i.Date.Month == dt.Month).AsEnumerable().Sum(i => i.Amount));
}
}
internal static List <string> GetIncomeTypesOfCat(string iCat, int userID)
{
using (var ctx = new BudgetContext())
{
return((from it in ctx.IncomeTypes
where it.IncomeCategory.User_ID == userID && it.IncomeCategory.IncomeCategoryName == iCat
select it.IncomeTypeName).ToList <string>());
}
}
internal static List <String> GetUserNames()
{
using (var ctx = new BudgetContext())
{
return((from u in ctx.Users
select u.UserName)
.ToList <String>());
}
}
/// <summary>
/// returns Admin and User Accounts that contain the given text in their usernames.
/// </summary>
/// <param name="text">Text to search</param>
/// <returns>String list of usernames</returns>
internal static List <String> NoncorporateUsernamesThatContain(string text)
{
using (var ctx = new BudgetContext())
{
var users = from u in ctx.Users
where u.UserName.Contains(text) && u.UserType.UserTypeName != "Corporate"
select u.UserName;
return(users.ToList <String>());
}
}
internal static void RemoveEmployee(int employerID, int employeeID)
{
using (var ctx = new BudgetContext())
{
User Employer = ctx.Users.Find(employerID);
User Employee = ctx.Users.Find(employeeID);
Employee.Employer = null;
ctx.SaveChanges();
}
}
internal static List <string> GetIncomeCats(int userID)
{
using (var ctx = new BudgetContext())
{
var exp = from cat in ctx.IncomeCategories
where cat.User_ID == userID
select cat.IncomeCategoryName;
return(exp.ToList <string>());
}
}
internal static List <object> GetIncomeHistory(int userID)
{
using (var ctx = new BudgetContext())
{
var inc = from incomes in ctx.Incomes
where incomes.User.UserID == userID
orderby incomes.Date
select new { Date = incomes.Date.ToString(), incomes.Amount, Type = incomes.IncomeType.IncomeTypeName, incomes.IncomeID };
return(inc.ToList <object>());
}
}
internal static List <object> GetExpenseHistory(int userID)
{
using (var ctx = new BudgetContext())
{
var exp = from expenses in ctx.Expenses
where expenses.User.UserID == userID
orderby expenses.Date
select new { Date = expenses.Date.ToString(), expenses.Amount, Type = expenses.ExpenseType.ExpenseTypeName, expenses.ExpenseID };
return(exp.ToList <object>());
}
}
/// <summary>
/// Add income to given date
/// </summary>
/// <param name="amount"></param>
/// <param name="iTypeStr">Income Type Name</param>
/// <param name="userID"></param>
/// <param name="dt">Date</param>
internal static void AddIncome(decimal amount, string iTypeStr, int userID, DateTime dt)
{
using (var ctx = new BudgetContext())
{
IncomeType iType = ctx.IncomeTypes.FirstOrDefault(it => it.IncomeTypeName == iTypeStr);
User usr = ctx.Users.Find(userID);
ctx.Incomes.Add(new Income()
{
Amount = amount, Date = dt, IncomeType = iType, User = usr
});
ctx.SaveChanges();
}
}
/// <summary>
/// Adds Expense to User to given date
/// </summary>
/// <param name="amount">decimal amount of expense</param>
/// <param name="eTypeStr">Expense Type</param>
/// <param name="userID"></param>
/// <param name="dt">Date of expense</param>
internal static void AddExpense(decimal amount, string eTypeStr, int userID, DateTime dt)
{
using (var ctx = new BudgetContext())
{
ExpenseType eType = ctx.ExpenseTypes.FirstOrDefault(et => et.ExpenseTypeName == eTypeStr);
User usr = ctx.Users.Find(userID);
ctx.Expenses.Add(new Expense()
{
Amount = amount, Date = dt, ExpenseType = eType, User = usr
});
ctx.SaveChanges();
}
}
internal static void DeleteExpensesOfType(string eType, int userID)
{
using (var ctx = new BudgetContext())
{
foreach (Expense e in ctx.Expenses)
{
if (e.ExpenseType.ExpenseTypeName == eType && e.User.UserID == userID)
{
ctx.Expenses.Remove(e);
}
}
ctx.SaveChanges();
}
}
internal static void DeleteIncomesOfType(string iType, int userID)
{
using (var ctx = new BudgetContext())
{
foreach (Income i in ctx.Incomes)
{
if (i.IncomeType.IncomeTypeName == iType && i.User.UserID == userID)
{
ctx.Incomes.Remove(i);
}
}
ctx.SaveChanges();
}
}
/// <summary>
///
/// </summary>
/// <param name="username"></param>
///<returns>returns 0 if user does not exist</returns>
internal static int GetUserID(string username)
{
using (var ctx = new BudgetContext())
{
User usr = ctx.Users.FirstOrDefault(u => u.UserName == username);
if (usr == null)
{
return(0);
}
else
{
return(usr.UserID);
}
}
}
internal static void DeleteTypesAndExpensesofExpenseCategory(string catName, int userID)
{
using (var ctx = new BudgetContext())
{
foreach (ExpenseType et in ctx.ExpenseTypes)
{
if (et.ExpenseCategory.ExpenseCategoryName == catName && et.ExpenseCategory.User_ID == userID)
{
DeleteExpensesOfType(et.ExpenseTypeName, userID);
ctx.ExpenseTypes.Remove(et);
}
}
ctx.SaveChanges();
}
}
internal static void DeleteTypesAndIncomesofIncomeCategory(string catName, int userID)
{
using (var ctx = new BudgetContext())
{
foreach (IncomeType it in ctx.IncomeTypes)
{
if (it.IncomeCategory.IncomeCategoryName == catName && it.IncomeCategory.User_ID == userID)
{
DeleteIncomesOfType(it.IncomeTypeName, userID);
ctx.IncomeTypes.Remove(it);
}
}
ctx.SaveChanges();
}
}
internal static bool TypeOfIncomeCategoryExists(string iCat, int userID)
{
using (var ctx = new BudgetContext())
{
IncomeType iType = ctx.IncomeTypes.FirstOrDefault(it => it.IncomeCategory.IncomeCategoryName == iCat && it.IncomeCategory.User_ID == userID);
if (iType == null)
{
return(false);
}
else
{
return(true);
}
}
}
internal static bool TypeOfExpenseCategoryExists(string eCat, int userID)
{
using (var ctx = new BudgetContext())
{
ExpenseType eType = ctx.ExpenseTypes.FirstOrDefault(et => et.ExpenseCategory.ExpenseCategoryName == eCat && et.ExpenseCategory.User_ID == userID);
if (eType == null)
{
return(false);
}
else
{
return(true);
}
}
}
internal static int TryLogin(string name, string pass)
{
using (var ctx = new BudgetContext())
{
var usr = ctx.Users.FirstOrDefault(u => u.UserName == name &&
u.Password == pass);
if (usr == null)
{
return(0);
}
else
{
return(usr.UserType.UserTypeID);
}
}
}
internal static bool IncomeOfTypeExists(string iType, int userID)
{
using (var ctx = new BudgetContext())
{
Income inc = ctx.Incomes.FirstOrDefault(i => i.IncomeType.IncomeTypeName == iType && i.User.UserID == userID);
if (inc == null)
{
return(false);
}
else
{
return(true);
}
}
}
internal static bool ExpenseOfTypeExists(string eType, int userID)
{
using (var ctx = new BudgetContext())
{
Expense exp = ctx.Expenses.FirstOrDefault(e => e.ExpenseType.ExpenseTypeName == eType && e.User.UserID == userID);
if (exp == null)
{
return(false);
}
else
{
return(true);
}
}
}