public static CarLiabilityListViewModel GetCarLiabilityByUser(string username)
{
Entities entities = new Entities();
CarLiabilityListViewModel result = new CarLiabilityListViewModel();
DateTime current = DateTime.Now;
var liabilities = entities.Liabilities.Where(x => x.Username.Equals(username) &&
x.LiabilityType == (int)Constants.Constants.LIABILITY_TYPE.CAR &&
!x.DisabledDate.HasValue).OrderBy(x => x.Name);
foreach (var liability in liabilities)
{
CarLiabilityViewModel viewModel = CreateViewModel(liability);
result.Liabilities.Add(viewModel);
}
var lbts = result.Liabilities.Where(x => x.StartDate <= current && x.EndDate >= current);
result.TotalOriginalValue = lbts.Sum(x => x.LiabilityValue);
result.TotalLiabilityValue = lbts.Sum(x => x.Value.Value);
result.TotalInterestPayment = lbts.Sum(x => x.MonthlyInterestPayment);
result.TotalOriginalPayment = lbts.Sum(x => x.MonthlyOriginalPayment);
result.TotalPayment = lbts.Sum(x => x.TotalMonthlyPayment);
result.TotalRemainedValue = lbts.Sum(x => x.RemainedValue);
result.TotalInterestRate = result.TotalLiabilityValue > 0 ? lbts.Sum(x => x.OriginalInterestPayment) / result.TotalLiabilityValue * 12 : 0;
result.IsInitialized = UserQueries.IsCompleteInitialized(username);
return(result);
}
public static SalaryListViewModel GetSalaryByUser(string username)
{
Entities entities = new Entities();
var salaries = entities.Incomes.Where(x => x.Username.Equals(username) &&
x.IncomeType == (int)Constants.Constants.INCOME_TYPE.SALARY_INCOME &&
!x.DisabledDate.HasValue).OrderBy(x => x.Name).ToList();
SalaryListViewModel result = new SalaryListViewModel();
foreach (var salary in salaries)
{
SalaryViewModel viewModel = new SalaryViewModel
{
Id = salary.Id,
Source = salary.Name,
IncomeDay = salary.IncomeDay,
Income = salary.Value,
AnnualIncome = salary.Value * 12,
Note = salary.Note
};
result.Salaries.Add(viewModel);
}
result.TotalIncome = result.Salaries.Sum(x => x.Income.Value);
result.TotalAnnualIncome = result.TotalIncome * 12;
result.IsInitialized = UserQueries.IsCompleteInitialized(username);
return(result);
}
public static OtherExpenseListViewModel GetOtherExpenseByUser(string username)
{
Entities entities = new Entities();
var otherExpenses = entities.Expenses.Where(x => x.Username.Equals(username) &&
x.ExpenseType == (int)Constants.Constants.EXPENSE_TYPE.OTHERS &&
!x.DisabledDate.HasValue).OrderBy(x => x.Name).ToList();
OtherExpenseListViewModel result = new OtherExpenseListViewModel();
foreach (var otherExpense in otherExpenses)
{
OtherExpenseViewModel viewModel = new OtherExpenseViewModel
{
Id = otherExpense.Id,
Source = otherExpense.Name,
ExpenseDay = otherExpense.ExpenseDay,
Expense = otherExpense.Value,
AnnualExpense = otherExpense.Value * 12,
Note = otherExpense.Note
};
result.Expenses.Add(viewModel);
}
result.TotalExpense = result.Expenses.Sum(x => x.Expense.Value);
result.TotalAnnualExpense = result.TotalExpense * 12;
result.IsInitialized = UserQueries.IsCompleteInitialized(username);
return(result);
}
public static BusinessListViewModel GetBusinessByUser(string username)
{
Entities entities = new Entities();
BusinessListViewModel result = new BusinessListViewModel();
DateTime current = DateTime.Now;
var businesss = entities.Assets.Include("Incomes").Include("Liabilities").Where(x => x.Username.Equals(username) &&
x.AssetType == (int)Constants.Constants.ASSET_TYPE.BUSINESS &&
!x.DisabledDate.HasValue);
foreach (var business in businesss)
{
BusinessViewModel businessViewModel = new BusinessViewModel();
businessViewModel.Id = business.Id;
businessViewModel.Name = business.AssetName;
businessViewModel.Value = business.Value;
if (business.Incomes1.Where(x => !x.DisabledDate.HasValue).Any())
{
businessViewModel.Income = business.Incomes1.FirstOrDefault().Value;
}
else
{
businessViewModel.Income = 0;
}
businessViewModel.AnnualIncome = businessViewModel.Income * 12;
businessViewModel.RentYield = businessViewModel.Value > 0 ? businessViewModel.AnnualIncome / businessViewModel.Value : 0;
foreach (var liability in business.Liabilities.Where(x => !x.DisabledDate.HasValue))
{
BusinessLiabilityViewModel liabilityViewModel = BusinessLiabilityQueries.CreateViewModel(liability);
businessViewModel.Liabilities.Add(liabilityViewModel);
}
var liabilities = businessViewModel.Liabilities.Where(x => x.StartDate <= current && x.EndDate >= current);
businessViewModel.TotalLiabilityValue = liabilities.Select(x => x.Value.Value).DefaultIfEmpty(0).Sum();
businessViewModel.TotalOriginalPayment = liabilities.Select(x => x.MonthlyOriginalPayment).DefaultIfEmpty(0).Sum();
businessViewModel.TotalInterestPayment = liabilities.Select(x => x.MonthlyInterestPayment).DefaultIfEmpty(0).Sum();
businessViewModel.TotalMonthlyPayment = liabilities.Select(x => x.TotalMonthlyPayment).DefaultIfEmpty(0).Sum();
businessViewModel.TotalPayment = liabilities.Select(x => x.TotalPayment).DefaultIfEmpty(0).Sum();
businessViewModel.TotalRemainedValue = liabilities.Select(x => x.RemainedValue).DefaultIfEmpty(0).Sum();
businessViewModel.TotalInterestRate = businessViewModel.TotalLiabilityValue > 0 ? liabilities.Select(x => x.OriginalInterestPayment).DefaultIfEmpty(0).Sum() / businessViewModel.TotalLiabilityValue * 12 : 0;
businessViewModel.RowSpan = businessViewModel.Liabilities.Any() ? businessViewModel.Liabilities.Count() + 3 : 2;
result.Businesses.Add(businessViewModel);
}
result.TotalValue = result.Businesses.Select(x => x.Value).DefaultIfEmpty(0).Sum();
result.TotalMonthlyIncome = result.Businesses.Select(x => x.Income).DefaultIfEmpty(0).Sum();
result.TotalAnnualIncome = result.TotalMonthlyIncome * 12;
result.TotalRentYield = result.TotalValue > 0 ? result.TotalAnnualIncome / result.TotalValue : 0;
result.IsInitialized = UserQueries.IsCompleteInitialized(username);
return(result);
}
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 InsuranceListViewModel GetInsuranceByUser(string username)
{
Entities entities = new Entities();
var insurances = entities.Assets.Include("Expenses").Where(x => x.Username.Equals(username) &&
x.AssetType == (int)Constants.Constants.ASSET_TYPE.INSURANCE &&
!x.DisabledDate.HasValue).OrderBy(x => x.AssetName).ToList();
InsuranceListViewModel result = new InsuranceListViewModel();
foreach (var insurance in insurances)
{
var expense = insurance.Expenses1.FirstOrDefault();
InsuranceViewModel viewModel = new InsuranceViewModel
{
Id = insurance.Id,
Name = insurance.AssetName,
Value = insurance.Value,
StartDate = insurance.StartDate.Value,
EndDate = insurance.EndDate.Value,
PaymentPeriod = Helper.TimePeriodString(insurance.StartDate.Value, insurance.EndDate.Value),
Expense = expense.Value,
AnnualExpense = expense.Value * 12,
Note = insurance.Note
};
int paymentPeriod = Helper.CalculateTimePeriod(insurance.StartDate.Value, insurance.EndDate.Value);
viewModel.TotalExpense = paymentPeriod * viewModel.Expense;
viewModel.YieldRate = viewModel.TotalExpense > 0 ? (viewModel.Value - viewModel.TotalExpense) / viewModel.TotalExpense : 0;
int currentPeriod = Helper.CalculateTimePeriod(viewModel.StartDate, DateTime.Now);
viewModel.RemainedValue = viewModel.TotalExpense - viewModel.Expense * currentPeriod;
result.Insurances.Add(viewModel);
}
result.TotalValue = result.Insurances.Sum(x => x.Value);
result.TotalTotalExpense = result.Insurances.Sum(x => x.TotalExpense);
result.TotalExpense = result.Insurances.Sum(x => x.Expense);
result.TotalAnnualExpense = result.Insurances.Sum(x => x.AnnualExpense);
result.TotalRemainedValue = result.Insurances.Sum(x => x.RemainedValue);
result.IsInitialized = UserQueries.IsCompleteInitialized(username);
return(result);
}
public static AvailableMoneyCreateViewModel GetInitializedAvailableMoney(string username)
{
Entities entities = new Entities();
AvailableMoneyCreateViewModel viewModel = new AvailableMoneyCreateViewModel();
Users user = entities.Users.Where(x => x.Username.Equals(username)).FirstOrDefault();
Assets availableMoney = entities.Assets.Where(x => x.Username.Equals(username) &&
x.AssetType == (int)Constants.Constants.ASSET_TYPE.AVAILABLE_MONEY &&
!x.DisabledDate.HasValue).OrderBy(x => x.CreatedDate).FirstOrDefault();
if (availableMoney != null)
{
viewModel.Name = "Tiền mặt có sẵn khởi tạo của " + user.FullName;
viewModel.AvailableMoney = availableMoney.Value;
viewModel.IsInitialized = UserQueries.IsCompleteInitialized(username);
return(viewModel);
}
else
{
return(null);
}
}
public static CreditCardLiabilityListViewModel GetCreditCardLiabilityByUser(string username)
{
Entities entities = new Entities();
CreditCardLiabilityListViewModel result = new CreditCardLiabilityListViewModel();
var liabilities = entities.Liabilities.Where(x => x.Username.Equals(username) &&
x.LiabilityType == (int)Constants.Constants.LIABILITY_TYPE.CREDIT_CARD &&
!x.DisabledDate.HasValue).OrderBy(x => x.Name);
foreach (var liability in liabilities)
{
CreditCardLiabilityViewModel viewModel = CreateViewModel(liability);
result.Liabilities.Add(viewModel);
}
result.TotalValue = result.Liabilities.Sum(x => x.Value);
result.TotalMonthlyPayment = result.Liabilities.Sum(x => x.MonthlyPayment);
result.TotalAnnualPayment = result.Liabilities.Sum(x => x.AnnualPayment);
result.TotalInterestRate = result.TotalAnnualPayment / result.TotalValue;
result.IsInitialized = UserQueries.IsCompleteInitialized(username);
return(result);
}
public static OtherAssetListViewModel GetOtherAssetByUser(string username)
{
Entities entities = new Entities();
OtherAssetListViewModel result = new OtherAssetListViewModel();
DateTime current = DateTime.Now;
var otherAssets = entities.Assets.Include("Incomes").Include("Liabilities").Where(x => x.Username.Equals(username) &&
x.AssetType == (int)Constants.Constants.ASSET_TYPE.OTHERS &&
!x.DisabledDate.HasValue);
foreach (var otherAsset in otherAssets)
{
OtherAssetViewModel otherAssetViewModel = new OtherAssetViewModel();
otherAssetViewModel.Id = otherAsset.Id;
otherAssetViewModel.Name = otherAsset.AssetName;
otherAssetViewModel.Value = otherAsset.Value;
if (otherAsset.Incomes1.Where(x => !x.DisabledDate.HasValue).Any())
{
otherAssetViewModel.Income = otherAsset.Incomes1.FirstOrDefault().Value;
}
else
{
otherAssetViewModel.Income = 0;
}
otherAssetViewModel.AnnualIncome = otherAssetViewModel.Income * 12;
otherAssetViewModel.RentYield = otherAssetViewModel.AnnualIncome / otherAssetViewModel.Value;
result.Assets.Add(otherAssetViewModel);
}
result.TotalValue = result.Assets.Select(x => x.Value).DefaultIfEmpty(0).Sum();
result.TotalMonthlyIncome = result.Assets.Select(x => x.Income).DefaultIfEmpty(0).Sum();
result.TotalAnnualIncome = result.TotalMonthlyIncome * 12;
result.TotalRentYield = result.TotalMonthlyIncome / result.TotalValue;
result.IsInitialized = UserQueries.IsCompleteInitialized(username);
return(result);
}
public static BankDepositListViewModel GetBankDepositByUser(string username)
{
Entities entities = new Entities();
var bankDeposits = entities.Assets.Where(x => x.Username.Equals(username) &&
x.AssetType == (int)Constants.Constants.ASSET_TYPE.BANK_DEPOSIT &&
!x.DisabledDate.HasValue).OrderBy(x => x.AssetName).ToList();
BankDepositListViewModel result = new BankDepositListViewModel();
foreach (var bankDeposit in bankDeposits)
{
BankDepositViewModel viewModel = new BankDepositViewModel
{
Id = bankDeposit.Id,
Name = bankDeposit.AssetName,
Value = bankDeposit.Value,
StartDate = bankDeposit.StartDate.Value,
EndDate = bankDeposit.EndDate.Value,
Income = bankDeposit.InterestRatePerX.Value == (int)Constants.Constants.INTEREST_RATE_PER.MONTH ? bankDeposit.Value * bankDeposit.InterestRate.Value / 100 : bankDeposit.Value * bankDeposit.InterestRate.Value / 1200,
InterestRate = bankDeposit.InterestRate.Value / 100,
InterestRatePerX = Helper.GetInterestTypePerX(bankDeposit.InterestRatePerX.Value),
InterestObtainWay = Helper.GetObtainWay(bankDeposit.ObtainedBy.Value),
PaymentPeriod = Helper.CalculateTimePeriod(bankDeposit.StartDate.Value, bankDeposit.EndDate.Value),
Note = bankDeposit.Note
};
viewModel.AnnualIncome = viewModel.Income * 12;
result.BankDeposits.Add(viewModel);
}
result.TotalValue = result.BankDeposits.Sum(x => x.Value);
result.TotalIncome = result.BankDeposits.Sum(x => x.Income);
result.TotalAnnualIncome = result.BankDeposits.Sum(x => x.AnnualIncome);
result.TotalInterestRate = result.TotalValue > 0 ? result.TotalAnnualIncome / result.TotalValue : 0;
result.IsInitialized = UserQueries.IsCompleteInitialized(username);
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 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);
}
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 StockListViewModel GetStockByUser(string username)
{
Entities entities = new Entities();
StockListViewModel result = new StockListViewModel();
DateTime current = DateTime.Now;
var stocks = entities.Assets.Include("StockTransactions").Include("Liabilities").Where(x => x.Username.Equals(username) &&
x.AssetType == (int)Constants.Constants.ASSET_TYPE.STOCK &&
!x.DisabledDate.HasValue);
foreach (var stock in stocks)
{
StockViewModel stockViewModel = new StockViewModel();
stockViewModel.Id = stock.Id;
stockViewModel.Name = stock.AssetName;
stockViewModel.Note = stock.Note;
foreach (var transactions in stock.StockTransactions.Where(x => !x.DisabledDate.HasValue))
{
StockTransactionViewModel transactionViewModel = StockQueries.CreateViewModel(transactions);
stockViewModel.Transactions.Transactions.Add(transactionViewModel);
}
var liabilities = stockViewModel.Transactions.Transactions.Select(x => x.Liabilities.Liabilities.Where(y => y.StartDate <= current && y.EndDate >= current));
stockViewModel.TotalLiabilityValue = liabilities.Sum(x => x.Sum(y => y.Value.HasValue ? y.Value.Value : 0));
stockViewModel.TotalOriginalPayment = liabilities.Sum(x => x.Sum(y => y.MonthlyOriginalPayment));
stockViewModel.TotalInterestPayment = liabilities.Sum(x => x.Sum(y => y.MonthlyInterestPayment));
stockViewModel.TotalMonthlyPayment = liabilities.Sum(x => x.Sum(y => y.TotalMonthlyPayment));
stockViewModel.TotalPayment = liabilities.Sum(x => x.Sum(y => y.TotalPayment));
stockViewModel.TotalRemainedValue = liabilities.Sum(x => x.Sum(y => y.RemainedValue));
stockViewModel.TotalOriginalInterestPayment = liabilities.Sum(x => x.Sum(y => y.OriginalInterestPayment));
stockViewModel.TotalInterestRate = stockViewModel.TotalLiabilityValue > 0 ? stockViewModel.TotalOriginalInterestPayment / stockViewModel.TotalLiabilityValue * 12 : 0;
stockViewModel.RowSpan = stockViewModel.Transactions.Transactions.Any() ? stockViewModel.Transactions.Transactions.Count() + stockViewModel.Transactions.Transactions.Select(x => x.Liabilities.Liabilities).Count() + 4 : 4;
if (stockViewModel.Transactions.Transactions.Any())
{
stockViewModel.RowSpan = 4;
bool flag = false;
foreach (var transaction in stockViewModel.Transactions.Transactions)
{
if (transaction.Liabilities.Liabilities.Count() > 0)
{
if (flag == false)
{
flag = true;
}
stockViewModel.RowSpan += transaction.Liabilities.Liabilities.Count();
}
}
if (flag == true)
{
stockViewModel.RowSpan += 1;
}
}
else
{
stockViewModel.RowSpan = 4;
}
result.Stocks.Add(stockViewModel);
}
result.TotalValue = result.Stocks.Select(x => x.Transactions.Transactions).Sum(x => x.Sum(y => y.StockValue.Value));
result.IsInitialized = UserQueries.IsCompleteInitialized(username);
return(result);
}
// GET: OtherAsset
public ActionResult Index()
{
bool result = UserQueries.IsCompleteInitialized(UserQueries.GetCurrentUsername());
return(View(result));
}
