public ActionResult Create(ExpenseSummary expenseSummary)
{
try
{
try
{
ExpenseManager expenseManager = new ExpenseManager();
expenseSummary.EmpID = Convert.ToInt32(Session["UserID"]);
int value = expenseManager.CreateExpense(expenseSummary);
if (value == 1)
{
TempData["Message"] = "Expense Created Successfully";
}
else if (value == 0)
{
TempData["Message"] = "Error Occured";
}
return(Redirect("~/Expense/Index"));
}
catch (Exception ex)
{
return(View());
}
}
catch
{
return(View());
}
}
public int CreateExpense(ExpenseSummary expenseSummary)
{
int returnValue = 0;
try
{
var connectionString = ConfigurationManager.AppSettings["PayMe-Connectionstring"];
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("CreateExpenseSummary", connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ProjectID", expenseSummary.ProjectID);
cmd.Parameters.AddWithValue("@ExpenseName", expenseSummary.Name);
cmd.Parameters.AddWithValue("@FromDate", expenseSummary.FromDate);
cmd.Parameters.AddWithValue("@ToDate", expenseSummary.ToDate);
cmd.Parameters.AddWithValue("@Description", expenseSummary.Description);
cmd.Parameters.AddWithValue("@EmployeeID", expenseSummary.EmpID);
cmd.Parameters.Add("@output", SqlDbType.Int).Direction = ParameterDirection.Output;
connection.Open();
cmd.ExecuteNonQuery();
returnValue = Convert.ToInt32(cmd.Parameters["@output"].Value);
connection.Close();
}
catch (Exception ex)
{
throw new ApplicationException(ex.Message.ToString());
}
return(returnValue);
}
public void UpdateExpenseSummary(ExpenseSummary es)
{
using (var _context = new AdminPortalEntities())
{
_context.ExpenseSummaries.Attach(es);
_context.Entry(es).State = EntityState.Modified;
_context.SaveChanges();
}
}
public bool CreateExpenseSummary(ExpenseSummary summary)
{
using (var _context = new AdminPortalEntities())
{
_context.ExpenseSummaries.Add(summary);
_context.SaveChanges();
return(true);
}
}
public IEnumerable <ExpenseSummary> ExpenseSummeryReport(int clientNo, int ProjectNo)
{
try
{
var connectionString = ConfigurationManager.AppSettings["PayMe-Connectionstring"];
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("GetExpenseSummeryReport", connection);
cmd.Parameters.AddWithValue("@ClientId", clientNo);
cmd.Parameters.AddWithValue("@ProjectId", ProjectNo);
cmd.CommandType = CommandType.StoredProcedure;
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
List <ExpenseSummary> ExpenseSummaryList = new List <ExpenseSummary>();
if (reader.HasRows)
{
while (reader.Read())
{
ExpenseSummary expenseSummary = new ExpenseSummary();
expenseSummary.ID = Convert.ToInt32(reader["ID"].ToString());
expenseSummary.UserName = reader["UserName"].ToString();
expenseSummary.Name = reader["Name"].ToString();
expenseSummary.ClientName = reader["ClientName"].ToString();
expenseSummary.ExpenseStatusID = Convert.ToInt32(reader["ExpenseStatusID"]);
expenseSummary.ExpenseStatusName = reader["ExpenseStatusName"].ToString();
expenseSummary.TotalAmount = Convert.ToDecimal(reader["TotalAmount"] == DBNull.Value ? 0 : reader["TotalAmount"]);
expenseSummary.ApprovedAmount = Convert.ToDecimal(reader["ApprovedAmount"] == DBNull.Value ? 0 : reader["ApprovedAmount"]);
expenseSummary.ProjectName = reader["ProjectName"].ToString();
expenseSummary.EmpID = Convert.ToInt32(reader["EmpID"]);
expenseSummary.ProjectID = Convert.ToInt32(reader["ProjectID"]);
expenseSummary.ProjectName = reader["ProjectName"].ToString();
expenseSummary.FromDate = Convert.ToDateTime(reader["FromDate"].ToString());
expenseSummary.ToDate = Convert.ToDateTime(reader["ToDate"].ToString());
expenseSummary.Description = reader["Description"].ToString();
ExpenseSummaryList.Add(expenseSummary);
}
}
reader.Close();
connection.Close();
return(ExpenseSummaryList);
}
catch (Exception ex)
{
throw new ApplicationException(ex.Message.ToString());
}
}
public async Task <ExpenseSummary> GetExpenseSummary(int expenseId)
{
Expense retrievedExpense = await expenseRepository.Get(expenseId);
User retrievedUser = await userRepository.Get(retrievedExpense.UserId);
ExpenseSummary summary = new ExpenseSummary()
{
Id = retrievedExpense.Id,
User = string.Format("{0} {1}", retrievedUser.FirstName, retrievedUser.SecondName),
Date = retrievedExpense.Date,
ExpenseType = retrievedExpense.ExpenseType,
Amount = retrievedExpense.Amount,
Currency = retrievedExpense.Currency,
Comment = retrievedExpense.Comment
};
return(summary);
}
public ExpenseSummary GetExpenseSummary(int accountId, BudgetPlanDetails budgetPlan)
{
var account = _bankAccountRepository.GetById(accountId, x => x.Bank, x => x.Currency);
if (account == null)
{
throw new ArgumentException("Account can't be null.");
}
var today = DateTime.Now;
var over12MonthsInterval = new Interval(today, DateTimeUnitEnums.Years, 1);
var over6MonthsInterval = new Interval(today, DateTimeUnitEnums.Months, 6);
var currentMonthInterval = new Interval(today, today);
var previousInterval = new Interval(today, DateTimeUnitEnums.Months, 1);
var categories = _ExpenseTypeRepository.GetList2().GroupBy(x => x.Id).ToDictionary(x => x.Key, y => y.Single());
var over12MonthsNames = over12MonthsInterval.GetIntervalsByMonth();
var currentMonthName = currentMonthInterval.GetSingleMonthName();
// Retrieve both current month expenses and over 12 months expenses
var expenses = GetExpenses(new PFM.Services.DTOs.SearchParameters.ExpenseGetListSearchParameters
{
AccountId = accountId,
StartDate = over12MonthsInterval.StartDate,
EndDate = currentMonthInterval.EndDate
});
// Reset the start date to the first movement (First day of the same month)
over12MonthsInterval.StartDate = DateTimeFormatHelper.GetFirstDayOfMonth(
expenses.Any() ?
expenses.OrderBy(x => x.DateExpense).First().DateExpense :
today);
// Count the number of months in the interval
var nbMonthInterval = over12MonthsInterval.Count(DateTimeUnitEnums.Months);
if (nbMonthInterval == 0)
{
nbMonthInterval = 1; // No expenses -> no division by zero
}
// Get current budget plan if it exists
var budgetPlanByCategory =
budgetPlan?.ExpenseTypes.GroupBy(x => x.ExpenseType.Id).ToDictionary(x => x.Key, y => y.Single().ExpectedValue)
?? categories.ToDictionary(x => x.Key, y => (decimal)0.00);
var expensesByCategories = expenses.Any()
? expenses.GroupBy(x => x.ExpenseTypeId).ToDictionary(x => x.Key, y => y.ToList())
: categories.ToDictionary(x => x.Key, y => new List <ExpenseList>());
var expensesByCategory = new List <ExpenseSummaryByCategory>();
foreach (var exp in expensesByCategories)
{
var category = categories[exp.Key];
var expensesCurrentMonth = exp.Value.Where(x => currentMonthInterval.IsBetween(x.DateExpense)).ToList();
var expensesPreviousMonth = exp.Value.Where(x => previousInterval.IsBetween(x.DateExpense)).ToList();
var expensesOver12Months = exp.Value.Where(x => over12MonthsInterval.IsBetween(x.DateExpense)).ToList();
// ReSharper disable once UseObjectOrCollectionInitializer
var expenseSummary = new ExpenseSummaryByCategory();
expenseSummary.CurrencySymbol = account.Currency.Symbol;
expenseSummary.CategoryId = category.Id;
expenseSummary.CategoryName = category.Name;
expenseSummary.CategoryColor = category.GraphColor;
expenseSummary.CostCurrentMonth = expensesCurrentMonth.Sum(x => x.Cost);
expenseSummary.CostPreviousMonth = expensesPreviousMonth.Sum(x => x.Cost);
expenseSummary.CostPlannedMonthly = budgetPlanByCategory.ContainsKey(exp.Key) ? budgetPlanByCategory[exp.Key] : 0;
expenseSummary.CostOver12Month = expensesOver12Months.Sum(x => x.Cost);
expenseSummary.AverageCostOver12Months = expenseSummary.CostOver12Month / nbMonthInterval;
// Retrieve the expenses per months (details and summary)
foreach (var month in over12MonthsNames)
{
var interval = month.Value;
var expByMonth = exp.Value.Where(x => interval.IsBetween(x.DateExpense)).ToList();
expenseSummary.Expenses.Add(month.Key, expByMonth);
expenseSummary.ExpensesByMonth.Add(month.Key, new ExpenseSummaryByCategoryAndByMonth(expByMonth.Sum(x => x.Cost)));
}
expenseSummary.Expenses.Add(currentMonthName, expensesCurrentMonth);
expenseSummary.ExpensesByMonth.Add(currentMonthName, new ExpenseSummaryByCategoryAndByMonth(expensesCurrentMonth.Sum(x => x.Cost)));
expensesByCategory.Add(expenseSummary);
}
var totalExpensesOver12Months = expenses.Where(x => over12MonthsInterval.IsBetween(x.DateExpense)).Sum(x => x.Cost);
// Get actual/expected expenses by month for last 12 Months
var budgetPlanExpenses = budgetPlanByCategory.Values.Sum(x => x);
var detailedExpensesOver12Months = new Dictionary <string, ExpenseSummaryByMonth>();
foreach (var month in over12MonthsNames)
{
var interval = month.Value;
var expByMonth = expenses.Where(x => interval.IsBetween(x.DateExpense)).Sum(x => x.Cost);
detailedExpensesOver12Months.Add(month.Key, new ExpenseSummaryByMonth()
{
ExpenseValue = expByMonth,
ExpenseExpectedValue = budgetPlanExpenses
});
}
detailedExpensesOver12Months.Add(currentMonthName, new ExpenseSummaryByMonth()
{
ExpenseValue = expenses.Where(x => currentMonthInterval.IsBetween(x.DateExpense)).Sum(x => x.Cost),
ExpenseExpectedValue = budgetPlanExpenses
});
var incomes = _incomeRepository.GetList2().Where(x => x.AccountId == accountId && x.DateIncome >= over12MonthsInterval.StartDate && x.DateIncome < currentMonthInterval.EndDate).ToList();
var savings = _savingRepository.GetList2().Where(x => x.AccountId == accountId && x.DateSaving >= over12MonthsInterval.StartDate && x.DateSaving < currentMonthInterval.EndDate).ToList();
// Get the incomes/expenses/savings by month for last 6 months
var over6MonthsNames = over6MonthsInterval.GetIntervalsByMonth();
var detailedMovementsOver6Months = new Dictionary <string, ExpenseSummaryByMonth>();
foreach (var month in over6MonthsNames)
{
var interval = month.Value;
var incomeByMonth = incomes.Where(x => interval.IsBetween(x.DateIncome)).Sum(x => x.Cost);
var savingByMonth = savings.Where(x => interval.IsBetween(x.DateSaving)).Sum(x => x.Amount);
detailedMovementsOver6Months.Add(month.Key, new ExpenseSummaryByMonth()
{
ExpenseValue = detailedExpensesOver12Months[month.Key].ExpenseValue,
IncomeValue = incomeByMonth,
SavingValue = savingByMonth
});
}
detailedMovementsOver6Months.Add(currentMonthName, new ExpenseSummaryByMonth()
{
ExpenseValue = detailedExpensesOver12Months[currentMonthName].ExpenseValue,
IncomeValue = incomes.Where(x => currentMonthInterval.IsBetween(x.DateIncome)).Sum(x => x.Cost),
SavingValue = savings.Where(x => currentMonthInterval.IsBetween(x.DateSaving)).Sum(x => x.Amount)
});
var ExpenseSummary = new ExpenseSummary()
{
Account = Mapper.Map <AccountDetails>(account),
ExpensesByCategory = expensesByCategory.OrderByDescending(x => x.CostCurrentMonth).ToList(),
LabelCurrentMonth = DateTimeFormatHelper.GetMonthNameAndYear(today),
LabelPreviousMonth = DateTimeFormatHelper.GetMonthNameAndYear(today.AddMonths(-1)),
BudgetPlanName = budgetPlan != null ? budgetPlan.Name : string.Empty,
AccountName = account.Name,
DisplayDashboard = true,
CurrencySymbol = account.Currency.Symbol,
HasCurrentBudgetPlan = budgetPlan != null,
HasExpenses = expenses.Any(),
HasCategories = categories.Any(),
TotalExpensesOver12Months = totalExpensesOver12Months,
DetailedExpensesOver12Months = detailedExpensesOver12Months,
DetailedMovementsOver6Months = detailedMovementsOver6Months,
CurrentMonthTotalExpense = expenses.Where(x => currentMonthInterval.IsBetween(x.DateExpense)).Sum(x => x.Cost),
AverageExpenses = expenses.Where(x => over12MonthsInterval.IsBetween(x.DateExpense)).Sum(x => x.Cost) / nbMonthInterval,
AverageIncomes = incomes.Where(x => over12MonthsInterval.IsBetween(x.DateIncome)).Sum(x => x.Cost) / nbMonthInterval,
AverageSavings = savings.Where(x => over12MonthsInterval.IsBetween(x.DateSaving)).Sum(x => x.Amount) / nbMonthInterval
};
return(ExpenseSummary);
}
