public List <People> DisplayPeople()
{
using (var db = new InvestmentdbContext())
{
return(db.Peoples.ToList());
}
}
public List <Expenditure> DisplayExpenditure()
{
using (var db = new InvestmentdbContext())
{
return(db.Expenditures.ToList());
}
}
// Display The data
public List <Income> DisplayIncome()
{
using (var db = new InvestmentdbContext())
{
return(db.Incomes.ToList());
}
}
// To find Total expenditure
public float TotalExpenditure()
{
float total = 0;
using (var db = new InvestmentdbContext())
{
total = db.Expenditures.Sum(s => s.ExpenseAmount);
}
return(total);
}
//To find the total income(Query the DataBase)
public virtual float TotalIncome()
{
float total = 0;
using (var db = new InvestmentdbContext())
{
total = db.Incomes.Sum(s => s.IncomeReceived);
}
return(total);
}
public void AddPersonName(string PersonName)
{
using (var db = new InvestmentdbContext())
{
db.Add(new People {
Name = PersonName
});
db.SaveChanges();
}
}
// To give options to the user on categories to select.
public List <string> DisplayPeopleName()
{
var listofnames = new List <string>();
using (var db = new InvestmentdbContext())
{
var QueryPeople = db.Peoples;
foreach (var p in QueryPeople)
{
listofnames.Add(p.Name);
}
return(listofnames);
}
}
//To calculate total expenditure monthwise
public object Monthy_Expenditure()
{
using (var db = new InvestmentdbContext())
{
var monthlyExpenditure = db.Expenditures
.Select(y => new
{
y.Day.Month,
y.ExpenseAmount
})
.GroupBy(y => y.Month, y => y.ExpenseAmount,
(Key, values) => new { Month = Key, MonthlyExpense = values.Sum() }).ToList();
return(monthlyExpenditure);
}
}
//To update the wrong entry made in Expenditure Table
public void Update_Expenditure(int id, float expenditure, string purpose)
{
using (var db = new InvestmentdbContext())
{
var SExpenditure = db.Expenditures.OrderBy(e => e.ExpenditureId);
foreach (var Eid in SExpenditure)
{
if (Eid.ExpenditureId == id)
{
Eid.ExpenseAmount = expenditure;
Eid.PurposeOfExpenditure = purpose;
}
}
db.SaveChanges();
}
}
//To update the wrong entry made in Income Table
public void Update_Income(int id, float income, string source)
{
using (var db = new InvestmentdbContext())
{
var SIncome = db.Incomes.OrderBy(i => i.IncomeId);
foreach (var Iid in SIncome)
{
if (Iid.IncomeId == id)
{
Iid.IncomeReceived = income;
Iid.SourceOfIncome = source;
}
}
db.SaveChanges();
}
}
//to Delete Wrong Expenditure
public void Delete_Expenditure(int id)
{
using (var db = new InvestmentdbContext())
{
var queryIncome = db.Expenditures.OrderBy(i => i.ExpenditureId);
foreach (var Eid in queryIncome)
{
if (Eid.ExpenditureId == id)
{
db.Remove(Eid);
}
}
db.SaveChanges();
}
}
//To calculate Total income monthwise
public object Monthly_Income()
{
using (var db = new InvestmentdbContext())
{
var monthlyIncome = db.Incomes
.Select(x => new
{
x.Day.Month,
x.IncomeReceived
})
.GroupBy(x => x.Month, x => x.IncomeReceived,
(Key, values) => new { Month = Key, MonthlyIncome = values.Sum() }).ToList();
return(monthlyIncome);
}
}
public void Delete_People(int id)
{
using (var db = new InvestmentdbContext())
{
var peopleId = db.Peoples.OrderBy(i => i.PeopleId);
foreach (var Pid in peopleId)
{
if (Pid.PeopleId == id)
{
db.Remove(Pid);
}
}
db.SaveChanges();
}
}
//to Delete Wrong Income
public void Delete_Income(int id)
{
using (var db = new InvestmentdbContext())
{
var queryIncome = db.Incomes.OrderBy(i => i.IncomeId);
// var fid = (float)id;
foreach (var Iid in queryIncome)
{
if (Iid.IncomeId == id)
{
db.Remove(Iid);
}
}
db.SaveChanges();
}
}
public void AddExpenditure(float amount, string purpose, string name)
{
if (amount <= 0)
{
throw new Exception("Invalid input");
}
else
{
using (var db = new InvestmentdbContext())
{
var q2 = db.Peoples.Where(p => p.Name == name);
foreach (var p in q2)
{
db.Add(new Expenditure {
ExpenseAmount = amount, Day = DateTime.Now, PurposeOfExpenditure = purpose, PeopleId = p.PeopleId
});
}
db.SaveChanges();
}
}
}
// Adding entry to database with exception handling
public void AddIncome(float amount, string source, string name)
{
if (amount <= 0)
{
throw new Exception("Invalid input");
}
else
{
using (var db = new InvestmentdbContext())
{
var q1 = db.Peoples.Where(p => p.Name == name);
foreach (var p in q1)
{
db.Add(new Income {
IncomeReceived = amount, Day = DateTime.Now, SourceOfIncome = source, PeopleId = p.PeopleId
});
}
db.SaveChanges();
}
}
}
