public static int UpdateInitializeIncome(Incomes model, string username)
{
DateTime current = DateTime.Now;
DateTime userCreateDate = UserQueries.GetUserByUsername(username).CreatedDate;
Entities entities = new Entities();
Incomes income = entities.Incomes.Where(x => x.Id == model.Id).FirstOrDefault();
if (!income.Name.Equals(model.Name))
{
income.Name = model.Name;
}
if (income.Value != model.Value)
{
income.Value = model.Value;
}
if (income.Note != model.Note)
{
income.Note = model.Note;
}
if (!income.StartDate.Equals(model.StartDate))
{
income.StartDate = model.StartDate;
}
if (income.IncomeDay != model.IncomeDay)
{
income.IncomeDay = model.IncomeDay;
income.StartDate = new DateTime(model.StartDate.Year, model.StartDate.Month, model.IncomeDay.Value);
}
if (!income.EndDate.Equals(model.EndDate))
{
income.EndDate = model.EndDate;
}
entities.Incomes.Attach(income);
entities.Entry(income).State = EntityState.Modified;
int result = entities.SaveChanges();
return(result);
}
public static int InitializeIncome(Incomes income, int type, string username)
{
DateTime current = DateTime.Now;
DateTime userCreateDate = UserQueries.GetUserByUsername(username).CreatedDate;
Entities entities = new Entities();
//Create income
income.Id = -1;
income.StartDate = new DateTime(income.StartDate.Year, income.StartDate.Month, income.IncomeDay.Value);
if (income.EndDate.HasValue)
{
income.EndDate = new DateTime(income.EndDate.Value.Year, income.EndDate.Value.Month, income.IncomeDay.Value);
}
income.CreatedDate = current;
income.IncomeType = type;
income.Username = username;
income.CreatedBy = Constants.Constants.USER;
entities.Incomes.Add(income);
int result = entities.SaveChanges();
return(result);
}
public static int CreateExpense(Expenses expense, int type, string username)
{
DateTime current = DateTime.Now;
DateTime userCreateDate = UserQueries.GetUserByUsername(username).CreatedDate;
Entities entities = new Entities();
//Create income
expense.Id = -1;
expense.StartDate = new DateTime(expense.StartDate.Year, expense.StartDate.Month, expense.ExpenseDay);
if (expense.EndDate.HasValue)
{
expense.EndDate = new DateTime(expense.EndDate.Value.Year, expense.EndDate.Value.Month, expense.ExpenseDay);
}
expense.CreatedDate = current;
expense.Username = username;
expense.CreatedBy = Constants.Constants.USER;
entities.Expenses.Add(expense);
//Create income creation log
History incomeHistory = new History();
incomeHistory.Type = type;
incomeHistory.Content = "Tạo mới " + expense.Name;
incomeHistory.CreatedDate = current;
incomeHistory.ActionType = (int)Constants.Constants.HISTORY_TYPE.CREATE;
incomeHistory.CreatedBy = Constants.Constants.USER;
incomeHistory.Username = username;
incomeHistory.Expenses = expense;
entities.History.Add(incomeHistory);
//Create income cashflow
DateTime cashflowStartDate = new DateTime(expense.StartDate.Year, expense.StartDate.Month, 1);
cashflowStartDate = cashflowStartDate < userCreateDate ? userCreateDate : cashflowStartDate;
DateTime cashflowEndDate = current;
if (expense.EndDate.HasValue)
{
if (expense.EndDate < current)
{
cashflowEndDate = expense.EndDate.Value;
}
}
while (cashflowStartDate <= cashflowEndDate)
{
Assets monthlyCashflow = new Assets();
monthlyCashflow.AssetName = "Chi tiêu từ " + expense.Name;
monthlyCashflow.Value = -expense.Value;
monthlyCashflow.StartDate = cashflowStartDate;
monthlyCashflow.CreatedDate = current;
monthlyCashflow.CreatedBy = Constants.Constants.SYSTEM;
monthlyCashflow.AssetType = (int)Constants.Constants.ASSET_TYPE.AVAILABLE_MONEY;
monthlyCashflow.Username = username;
monthlyCashflow.Expenses = expense;
entities.Assets.Add(monthlyCashflow);
History cashflowHistory = new History();
cashflowHistory.Type = (int)Constants.Constants.ASSET_TYPE.AVAILABLE_MONEY;
cashflowHistory.ActionType = (int)Constants.Constants.HISTORY_TYPE.CREATE;
cashflowHistory.Content = "Tạo mới chi tiêu từ " + expense.Name + " tháng " + cashflowStartDate.ToString("MM/yyyy");
cashflowHistory.CreatedDate = current;
cashflowHistory.CreatedBy = Constants.Constants.SYSTEM;
cashflowHistory.Username = username;
cashflowHistory.Assets = monthlyCashflow;
entities.History.Add(cashflowHistory);
cashflowStartDate = cashflowStartDate.AddMonths(1);
}
int result = entities.SaveChanges();
return(result);
}
public static CashFlowDetailListViewModel GetCashFlowDetail(Expenses expense, int?id, string username)
{
CashFlowDetailListViewModel result = new CashFlowDetailListViewModel();
DateTime current = DateTime.Now;
DateTime userCreatedDate = UserQueries.GetUserByUsername(username).CreatedDate;
Expenses dbExpense = null;
result.BeforeAvailableMoney = AssetQueries.CheckAvailableMoney(username, current);
result.AfterAvailableMoney = result.BeforeAvailableMoney;
DateTime startDate = expense.StartDate < userCreatedDate ? userCreatedDate : expense.StartDate;
DateTime endDate = current;
if (id > 0)
{
expense = ExpenseQueries.GetExpenseById(id.Value);
while (startDate <= endDate)
{
CashFlowDetailViewModel model = new CashFlowDetailViewModel
{
Month = startDate.ToString("MM/yyyy"),
IncomeBefore = 0 - expense.Value,
IncomeAfter = 0
};
result.CashflowDetails.Add(model);
result.AfterAvailableMoney += expense.Value;
startDate = startDate.AddMonths(1);
}
result.Action = "Delete";
}
else if (expense.Id == 0)
{
while (startDate <= endDate)
{
CashFlowDetailViewModel model = new CashFlowDetailViewModel
{
Month = startDate.ToString("MM/yyyy"),
IncomeBefore = 0,
IncomeAfter = 0 - expense.Value
};
result.CashflowDetails.Add(model);
result.AfterAvailableMoney -= expense.Value;
startDate = startDate.AddMonths(1);
}
result.Action = "Create";
}
else
{
dbExpense = ExpenseQueries.GetExpenseById(expense.Id);
while (startDate <= endDate)
{
CashFlowDetailViewModel model = new CashFlowDetailViewModel();
model.Month = startDate.ToString("MM/yyyy");
if (startDate >= dbExpense.StartDate && startDate <= (dbExpense.EndDate.HasValue ? dbExpense.EndDate : current))
{
model.IncomeBefore = 0 - dbExpense.Value;
}
else
{
model.IncomeBefore = 0;
}
if (startDate >= expense.StartDate && startDate <= (expense.EndDate.HasValue ? expense.EndDate : current))
{
model.IncomeAfter = 0 - expense.Value;
}
else
{
model.IncomeAfter = 0;
}
result.CashflowDetails.Add(model);
result.AfterAvailableMoney -= model.IncomeAfter - model.IncomeBefore;
startDate = startDate.AddMonths(1);
}
result.Action = "Update";
}
return(result);
}
/// <summary>
/// Update expense
/// </summary>
/// <param name="model">Expense updated information</param>
/// <param name="username">Username of account</param>
/// <returns>Update status</returns>
public static int UpdateExpense(Expenses model, string username)
{
DateTime current = DateTime.Now;
DateTime userCreateDate = UserQueries.GetUserByUsername(username).CreatedDate;
Entities entities = new Entities();
Expenses expense = entities.Expenses.Where(x => x.Id == model.Id).FirstOrDefault();
if (!expense.Name.Equals(model.Name))
{
History incomeHistory = new History();
incomeHistory.Type = expense.ExpenseType;
incomeHistory.Content = "Cập nhật " + expense.Name;
incomeHistory.CreatedDate = current;
incomeHistory.ActionType = (int)Constants.Constants.HISTORY_TYPE.UPDATE;
incomeHistory.Field = "Name";
incomeHistory.OldValue = expense.Name;
incomeHistory.NewValue = model.Name;
incomeHistory.CreatedBy = Constants.Constants.USER;
incomeHistory.Username = username;
incomeHistory.Expenses = expense;
entities.History.Add(incomeHistory);
expense.Name = model.Name;
}
if (expense.Value != model.Value)
{
History incomeHistory = new History();
incomeHistory.Type = expense.ExpenseType;
incomeHistory.Content = "Cập nhật " + expense.Name;
incomeHistory.CreatedDate = current;
incomeHistory.ActionType = (int)Constants.Constants.HISTORY_TYPE.UPDATE;
incomeHistory.Field = "Value";
incomeHistory.OldValue = expense.Value.ToString();
incomeHistory.NewValue = model.Value.ToString();
incomeHistory.CreatedBy = Constants.Constants.USER;
incomeHistory.Username = username;
incomeHistory.Expenses = expense;
entities.History.Add(incomeHistory);
expense.Value = model.Value;
}
if (expense.Note != model.Note)
{
History incomeHistory = new History();
incomeHistory.Type = expense.ExpenseType;
incomeHistory.Content = "Cập nhật " + expense.Name;
incomeHistory.CreatedDate = current;
incomeHistory.ActionType = (int)Constants.Constants.HISTORY_TYPE.UPDATE;
incomeHistory.Field = "Note";
incomeHistory.OldValue = expense.Note;
incomeHistory.NewValue = model.Note;
incomeHistory.CreatedBy = Constants.Constants.USER;
incomeHistory.Username = username;
incomeHistory.Expenses = expense;
entities.History.Add(incomeHistory);
expense.Note = model.Note;
}
if (!expense.StartDate.Equals(model.StartDate))
{
History incomeHistory = new History();
incomeHistory.Type = expense.ExpenseType;
incomeHistory.Content = "Cập nhật " + expense.Name;
incomeHistory.CreatedDate = current;
incomeHistory.ActionType = (int)Constants.Constants.HISTORY_TYPE.UPDATE;
incomeHistory.Field = "StartDate";
incomeHistory.OldValue = expense.StartDate.ToString("MM/yyyy");
incomeHistory.NewValue = model.StartDate.ToString("MM/yyyy");
incomeHistory.CreatedBy = Constants.Constants.USER;
incomeHistory.Username = username;
incomeHistory.Expenses = expense;
entities.History.Add(incomeHistory);
expense.StartDate = model.StartDate;
}
if (expense.ExpenseDay != model.ExpenseDay)
{
History incomeHistory = new History();
incomeHistory.Type = expense.ExpenseType;
incomeHistory.Content = "Cập nhật " + expense.Name;
incomeHistory.CreatedDate = current;
incomeHistory.ActionType = (int)Constants.Constants.HISTORY_TYPE.UPDATE;
incomeHistory.Field = "IncomeDay";
incomeHistory.OldValue = expense.ExpenseDay.ToString();
incomeHistory.NewValue = model.ExpenseDay.ToString();
incomeHistory.CreatedBy = Constants.Constants.USER;
incomeHistory.Username = username;
incomeHistory.Expenses = expense;
entities.History.Add(incomeHistory);
expense.ExpenseDay = model.ExpenseDay;
expense.StartDate = new DateTime(model.StartDate.Year, model.StartDate.Month, model.ExpenseDay);
}
if (!expense.EndDate.Equals(model.EndDate))
{
History incomeHistory = new History();
incomeHistory.Type = expense.ExpenseType;
incomeHistory.Content = "Cập nhật " + expense.Name;
incomeHistory.CreatedDate = current;
incomeHistory.ActionType = (int)Constants.Constants.HISTORY_TYPE.UPDATE;
incomeHistory.Field = "EndDate";
incomeHistory.OldValue = expense.EndDate.HasValue ? expense.EndDate.Value.ToString("MM/yyyy") : string.Empty;
incomeHistory.NewValue = model.EndDate.HasValue ? model.EndDate.Value.ToString("MM/yyyy") : string.Empty;
incomeHistory.CreatedBy = Constants.Constants.USER;
incomeHistory.Username = username;
incomeHistory.Expenses = expense;
entities.History.Add(incomeHistory);
expense.EndDate = model.EndDate;
}
foreach (var cashflow in expense.Assets.Where(x => !x.DisabledDate.HasValue))
{
if (cashflow.StartDate <= (expense.EndDate.HasValue ? expense.EndDate.Value : current))
{
cashflow.Value = -expense.Value;
cashflow.StartDate = expense.StartDate;
}
else
{
cashflow.DisabledDate = current;
cashflow.DisabledBy = Constants.Constants.SYSTEM;
}
entities.Assets.Attach(cashflow);
entities.Entry(cashflow).State = EntityState.Modified;
}
entities.Expenses.Attach(expense);
entities.Entry(expense).State = EntityState.Modified;
int result = entities.SaveChanges();
return(result);
}
public static FinancialStatusViewModel GetFinancialStatusByUser(string username)
{
FinancialStatusViewModel result = new FinancialStatusViewModel();
Entities entities = new Entities();
DateTime current = DateTime.Now;
result.CompleteInitialization = UserQueries.GetUserByUsername(username).CompleteInitialization;
result.SalaryIncome = entities.Incomes.Where(x => x.Username.Equals(username) &&
x.IncomeType == (int)Constants.Constants.INCOME_TYPE.SALARY_INCOME &&
!x.DisabledDate.HasValue && x.StartDate <= current).Select(x => x.Value).DefaultIfEmpty(0).Sum();
result.RealEstateIncome = entities.Incomes.Where(x => x.Username.Equals(username) &&
x.IncomeType == (int)Constants.Constants.INCOME_TYPE.REAL_ESTATE_INCOME &&
!x.DisabledDate.HasValue).Select(x => x.Value).DefaultIfEmpty(0).Sum();
result.BusinessIncome = entities.Incomes.Where(x => x.Username.Equals(username) &&
x.IncomeType == (int)Constants.Constants.INCOME_TYPE.BUSINESS_INCOME &&
!x.DisabledDate.HasValue).Select(x => x.Value).DefaultIfEmpty(0).Sum();
result.InterestIncome = entities.Assets.Where(x => x.Username.Equals(username) &&
x.AssetType == (int)Constants.Constants.ASSET_TYPE.BANK_DEPOSIT &&
!x.DisabledDate.HasValue).Select(x => x.Value * x.InterestRate.Value / 1200).DefaultIfEmpty(0).Sum();
var stocks = entities.Assets.Where(x => x.Username.Equals(username) &&
x.AssetType == (int)Constants.Constants.ASSET_TYPE.STOCK &&
!x.DisabledDate.HasValue);
foreach (var stock in stocks)
{
var transactions = entities.StockTransactions.Where(x => x.Username.Equals(username) && x.AssetId == stock.Id && !x.DisabledDate.HasValue);
if (transactions.Any())
{
double quantity = entities.StockTransactions.Where(x => x.Username.Equals(username) && x.AssetId == stock.Id && !x.DisabledDate.HasValue).Sum(x => x.NumberOfShares);
double interestRate = entities.StockTransactions.Where(x => x.Username.Equals(username) && x.AssetId == stock.Id && !x.DisabledDate.HasValue).OrderByDescending(x => x.TransactionDate).FirstOrDefault().ExpectedDividend;
result.DividendIncome += quantity * 10000 * interestRate / 100;
}
else
{
result.DividendIncome = 0;
}
}
result.FamilyExpenses = entities.Expenses.Where(x => x.Username.Equals(username) &&
x.ExpenseType == (int)Constants.Constants.EXPENSE_TYPE.FAMILY &&
!x.DisabledDate.HasValue).Select(x => x.Value).DefaultIfEmpty(0).Sum();
result.FamilyExpenses += entities.Expenses.Where(x => x.Username.Equals(username) &&
x.ExpenseType == (int)Constants.Constants.EXPENSE_TYPE.INSURANCE &&
!x.DisabledDate.HasValue).Select(x => x.Value).DefaultIfEmpty(0).Sum();
result.OtherExpenses = entities.Expenses.Where(x => x.Username.Equals(username) &&
x.ExpenseType == (int)Constants.Constants.EXPENSE_TYPE.OTHERS &&
!x.DisabledDate.HasValue).Select(x => x.Value).DefaultIfEmpty(0).Sum();
result.AvailableMoney = entities.Assets.Where(x => x.Username.Equals(username) &&
x.AssetType == (int)Constants.Constants.ASSET_TYPE.AVAILABLE_MONEY &&
!x.DisabledDate.HasValue).Select(x => x.Value).DefaultIfEmpty(0).Sum();
result.RealEstateValue = entities.Assets.Where(x => x.Username.Equals(username) &&
x.AssetType == (int)Constants.Constants.ASSET_TYPE.REAL_ESTATE &&
!x.DisabledDate.HasValue).Select(x => x.Value).DefaultIfEmpty(0).Sum();
result.BusinessValue = entities.Assets.Where(x => x.Username.Equals(username) &&
x.AssetType == (int)Constants.Constants.ASSET_TYPE.BUSINESS &&
!x.DisabledDate.HasValue).Select(x => x.Value).DefaultIfEmpty(0).Sum();
result.BankDepositValue = entities.Assets.Where(x => x.Username.Equals(username) &&
x.AssetType == (int)Constants.Constants.ASSET_TYPE.BANK_DEPOSIT &&
!x.DisabledDate.HasValue).Select(x => x.Value).DefaultIfEmpty(0).Sum();
result.InsuranceValue = entities.Assets.Where(x => x.Username.Equals(username) &&
x.AssetType == (int)Constants.Constants.ASSET_TYPE.INSURANCE &&
!x.DisabledDate.HasValue).Select(x => x.Value).DefaultIfEmpty(0).Sum();
result.StockValue = entities.StockTransactions.Where(x => x.Username.Equals(username) &&
!x.DisabledDate.HasValue).Select(x => x.TransactionType == (int)Constants.Constants.TRANSACTION_TYPE.SELL ? 0 - x.Value : x.Value).DefaultIfEmpty(0).Sum();
result.HomeMortgageLiability = entities.Liabilities.Where(x => x.Username.Equals(username) &&
x.LiabilityType == (int)Constants.Constants.LIABILITY_TYPE.REAL_ESTATE &&
!x.ParentLiabilityId.HasValue && !x.DisabledDate.HasValue).Select(x => x.Value).DefaultIfEmpty(0).Sum();
var carLiabilities = entities.Liabilities.Where(x => x.Username.Equals(username) &&
x.LiabilityType == (int)Constants.Constants.LIABILITY_TYPE.CAR &&
!x.DisabledDate.HasValue && x.StartDate <= current && x.EndDate >= current);
foreach (var carLiability in carLiabilities)
{
result.CarPayment += LiabilityQueries.GetCurrentMonthlyPayment(carLiability.Id);
}
var creditCardLiabilities = entities.Liabilities.Where(x => x.Username.Equals(username) &&
x.LiabilityType == (int)Constants.Constants.LIABILITY_TYPE.CREDIT_CARD &&
!x.DisabledDate.HasValue);
foreach (var creditCarLiability in creditCardLiabilities)
{
result.CreditCard += creditCarLiability.Value * creditCarLiability.InterestRate / 1200;
}
var homeLiabilities = entities.Liabilities.Where(x => x.Username.Equals(username) &&
x.LiabilityType == (int)Constants.Constants.LIABILITY_TYPE.REAL_ESTATE &&
!x.DisabledDate.HasValue && x.StartDate <= current && x.EndDate >= current);
foreach (var homeLiability in homeLiabilities)
{
result.HomeMortgage += LiabilityQueries.GetCurrentMonthlyPayment(homeLiability.Id);
}
var businessLiabilities = entities.Liabilities.Where(x => x.Username.Equals(username) &&
x.LiabilityType == (int)Constants.Constants.LIABILITY_TYPE.BUSINESS &&
!x.DisabledDate.HasValue && x.StartDate <= current && x.EndDate >= current);
foreach (var businessLiability in businessLiabilities)
{
result.BusinessLoanExpenses += LiabilityQueries.GetCurrentMonthlyPayment(businessLiability.Id);
}
var otherLiabilities = entities.Liabilities.Where(x => x.Username.Equals(username) &&
x.LiabilityType == (int)Constants.Constants.LIABILITY_TYPE.OTHERS &&
!x.DisabledDate.HasValue && x.StartDate <= current && x.EndDate >= current);
foreach (var otherLiability in otherLiabilities)
{
result.OtherLoanExpenses += LiabilityQueries.GetCurrentMonthlyPayment(otherLiability.Id);
}
result.StockLoan = entities.Liabilities.Where(x => x.Username.Equals(username) &&
x.LiabilityType == (int)Constants.Constants.LIABILITY_TYPE.STOCK &&
!x.DisabledDate.HasValue && x.StartDate <= current && x.EndDate >= current).Select(x => x.Value).DefaultIfEmpty(0).Sum();
var stockLiabilities = entities.Liabilities.Where(x => x.Username.Equals(username) &&
x.LiabilityType == (int)Constants.Constants.LIABILITY_TYPE.STOCK &&
!x.DisabledDate.HasValue);
foreach (var stockLiability in stockLiabilities)
{
result.StockExpenses += LiabilityQueries.GetCurrentMonthlyPayment(stockLiability.Id);
}
result.CreditCardLiability = entities.Liabilities.Where(x => x.Username.Equals(username) &&
x.LiabilityType == (int)Constants.Constants.LIABILITY_TYPE.CREDIT_CARD &&
!x.DisabledDate.HasValue).Select(x => x.Value).DefaultIfEmpty(0).Sum();
result.CarLoan = entities.Liabilities.Where(x => x.Username.Equals(username) &&
x.LiabilityType == (int)Constants.Constants.LIABILITY_TYPE.CAR &&
!x.DisabledDate.HasValue && x.StartDate <= current && x.EndDate >= current).Select(x => x.Value).DefaultIfEmpty(0).Sum();
result.BusinessLoan = entities.Liabilities.Where(x => x.Username.Equals(username) &&
x.LiabilityType == (int)Constants.Constants.LIABILITY_TYPE.BUSINESS &&
!x.DisabledDate.HasValue && !x.ParentLiabilityId.HasValue && x.StartDate <= current && x.EndDate >= current).Select(x => x.Value).DefaultIfEmpty(0).Sum();
result.OtherLoans = entities.Liabilities.Where(x => x.Username.Equals(username) &&
x.LiabilityType == (int)Constants.Constants.LIABILITY_TYPE.OTHERS &&
!x.DisabledDate.HasValue && x.StartDate <= current && x.EndDate >= current).Select(x => x.Value).DefaultIfEmpty(0).Sum();
result.TotalIncomes = result.SalaryIncome + result.RealEstateIncome + result.BusinessIncome + result.InterestIncome + result.DividendIncome;
result.TotalAssets = result.AvailableMoney + result.RealEstateValue + result.BusinessValue + result.BankDepositValue + result.StockValue + result.InsuranceValue;
result.TotalExpenses = result.HomeMortgage + result.CarPayment + result.CreditCard + result.BusinessLoanExpenses + result.StockExpenses + result.OtherExpenses + result.FamilyExpenses;
result.TotalLiabilities = result.HomeMortgageLiability + result.CarLoan + result.CreditCardLiability + result.BusinessLoan + result.StockLoan + result.OtherLoans;
result.Equipty = result.TotalAssets - result.TotalLiabilities;
result.MonthlyCashflow = result.TotalIncomes - result.TotalExpenses;
result.PassiveIncome = result.BusinessIncome + result.RealEstateIncome + result.InterestIncome + result.DividendIncome;
result.FinancialFreedom = result.TotalExpenses > 0 && result.TotalIncomes >= result.TotalExpenses ? result.PassiveIncome / result.TotalExpenses * 100 : 0;
return(result);
}