public decimal GetTotalExpenses()
{
using (var context = new TruckDataContext(@"Data Source=.\sqlexpress;Initial Catalog=Truck;Integrated Security=True"))
{
return(context.Expenses.Sum(x => x.Amount));
}
}
public List <Expense> GetExpenses()
{
using (var context = new TruckDataContext(@"Data Source=.\sqlexpress;Initial Catalog=Truck;Integrated Security=True"))
{
return(context.Expenses.ToList());
}
}
public Product GetProductByName(string name)
{
using (var context = new TruckDataContext(@"Data Source=.\sqlexpress;Initial Catalog=Truck;Integrated Security=True"))
{
return(context.Products.FirstOrDefault(x => x.Name == name));
}
}
public void AddExpense(Expense expense)
{
using (var context = new TruckDataContext(@"Data Source=.\sqlexpress;Initial Catalog=Truck;Integrated Security=True"))
{
context.Expenses.InsertOnSubmit(expense);
context.SubmitChanges();
}
}
public decimal GetTotalSpentOnInventory(string ices, string kliens, string paperGoods, string cones, string toppings)
{
using (var context = new TruckDataContext(@"Data Source=.\sqlexpress;Initial Catalog=Truck;Integrated Security=True"))
{
var inventory = context.Expenses.ToList().Where(e => e.Name == ices || e.Name == kliens || e.Name == paperGoods).ToList();
return(inventory.Sum(s => s.Amount));
}
}
public void AddProduct(Product p)
{
using (var context = new TruckDataContext(@"Data Source=.\sqlexpress;Initial Catalog=Truck;Integrated Security=True"))
{
context.Products.InsertOnSubmit(p);
context.SubmitChanges();
}
}
public void AddStock(AddStock addStock)
{
using (var context = new TruckDataContext(@"Data Source=.\sqlexpress;Initial Catalog=Truck;Integrated Security=True"))
{
context.AddStocks.InsertOnSubmit(addStock);
context.SubmitChanges();
}
}
public void DeleteStock(int id)
{
using (var context = new TruckDataContext(@"Data Source=.\sqlexpress;Initial Catalog=Truck;Integrated Security=True"))
{
var Stock = context.AddStocks.FirstOrDefault(s => s.Id == id);
context.AddStocks.DeleteOnSubmit(Stock);
context.SubmitChanges();
}
}
public void DeleteExpense(int id)
{
using (var context = new TruckDataContext(@"Data Source=.\sqlexpress;Initial Catalog=Truck;Integrated Security=True"))
{
var expense = context.Expenses.FirstOrDefault(e => e.Id == id);
context.Expenses.DeleteOnSubmit(expense);
context.SubmitChanges();
}
}
public void UpdateStock(AddStock a)
{
using (var context = new TruckDataContext(@"Data Source=.\sqlexpress;Initial Catalog=Truck;Integrated Security=True"))
{
context.AddStocks.Attach(a);
context.Refresh(RefreshMode.KeepCurrentValues, a);
context.SubmitChanges();
}
}
public void UpdateExpense(Expense e)
{
using (var context = new TruckDataContext(@"Data Source=.\sqlexpress;Initial Catalog=Truck;Integrated Security=True"))
{
context.Expenses.Attach(e);
context.Refresh(RefreshMode.KeepCurrentValues, e);
context.SubmitChanges();
}
}
public List <AddStock> GetAddStocks()
{
using (var context = new TruckDataContext(@"Data Source=.\sqlexpress;Initial Catalog=Truck;Integrated Security=True"))
{
var loadOptions = new DataLoadOptions();
loadOptions.LoadWith <AddStock>(e => e.Product);
context.LoadOptions = loadOptions;
return(context.AddStocks.ToList());
}
}
public List <Route> GetRoutes()
{
using (var context = new TruckDataContext(@"Data Source=.\sqlexpress;Initial Catalog=Truck;Integrated Security=True"))
{
var loadOptions = new DataLoadOptions();
loadOptions.LoadWith <Route>(r => r.Driver);
context.LoadOptions = loadOptions;
return(context.Routes.ToList());
}
}
public decimal GetTotalAmountSpentOnIceCream()
{
using (var context = new TruckDataContext(@"Data Source=.\sqlexpress;Initial Catalog=Truck;Integrated Security=True"))
{
var loadOptions = new DataLoadOptions();
loadOptions.LoadWith <AddStock>(e => e.Product);
context.LoadOptions = loadOptions;
var softIceCream = context.AddStocks.Where(d => d.Id == 3);
var totalSoftAdded = softIceCream.Sum(d => d.Amount);
var averageSoftPrice = context.Products.First(p => p.Id == 1).AveragePrice;
return(totalSoftAdded * averageSoftPrice);
}
}
public List <Event> GetEvents()
{
using (var context = new TruckDataContext(@"Data Source=.\sqlexpress;Initial Catalog=Truck;Integrated Security=True"))
{
var loadOptions = new DataLoadOptions();
loadOptions.LoadWith <Event>(e => e.Driver);
context.LoadOptions = loadOptions;
return(context.Events.ToList());
//return context.Events.ToList();
}
}
public decimal GetTotalAmountShouldHaveMadeFromProduct(int passedInId)
{
using (var context = new TruckDataContext(@"Data Source=.\sqlexpress;Initial Catalog=Truck;Integrated Security=True"))
{
var loadOptions = new DataLoadOptions();
loadOptions.LoadWith <AddStock>(e => e.Product);
context.LoadOptions = loadOptions;
var item = context.AddStocks.Where(d => d.Id == passedInId);
var totalProductAdded = item.Sum(d => d.Amount);
var soldFor = context.Products.First(p => p.Id == passedInId).SoldFor;
return(totalProductAdded * soldFor);
}
}
public decimal GetTotalAmountSpentOnIces()
{
using (var context = new TruckDataContext(@"Data Source=.\sqlexpress;Initial Catalog=Truck;Integrated Security=True"))
{
var loadOptions = new DataLoadOptions();
loadOptions.LoadWith <AddStock>(e => e.Product);
context.LoadOptions = loadOptions;
var twinPops = context.AddStocks.Where(d => d.Id == 1);
var totalTwinsAdded = twinPops.Sum(d => d.Amount);
var averageTwinPrice = context.Products.First(p => p.Id == 1).AveragePrice;
var totalTwin = totalTwinsAdded * averageTwinPrice;
var luigi = context.AddStocks.Where(d => d.Id == 2);
var totalLuigiAdded = luigi.Sum(d => d.Amount);
var averageLuigiPrice = context.Products.First(p => p.Id == 2).AveragePrice;
var totalLuigi = totalLuigiAdded * averageLuigiPrice;
return(totalTwin + totalLuigi);
}
}