public IActionResult Admin(int id) { List <Data> data = (from d in _context.Data where d.DepartmentID == id group d by d.Date into g select new Data { Date = g.Key, EnergyConsumption = g.Sum(ec => ec.EnergyConsumption), EnergyAdjustment = g.Sum(ea => ea.EnergyAdjustment), GasConsumption = g.Sum(gc => gc.GasConsumption), GasAdjustment = g.Sum(ga => ga.GasAdjustment), DepartmentID = id }).ToList(); Data total = new Data { EnergyConsumption = (from d in data where d.DepartmentID == id select d.EnergyConsumption).Sum() + (from d in data where d.DepartmentID == id select d.EnergyAdjustment).Sum(), GasConsumption = (from d in data where d.DepartmentID == id select d.GasConsumption).Sum() + (from d in data where d.DepartmentID == id select d.GasAdjustment).Sum(), }; List <Data> orderedData = DataWithDateTime.OrderedData(data); Department current = (from d in _context.Department where d.DepartmentID == id select d).ToList()[0]; var tuple = Tuple.Create(_context.Department.ToList(), orderedData, total, false, current); return(View(tuple)); }
public FileResult ExportData(int id) { //Create the datatable List <Data> data = (from d in _context.Data where d.DepartmentID == id group d by d.Date into g select new Data { Date = g.Key, EnergyConsumption = g.Sum(ec => ec.EnergyConsumption), EnergyAdjustment = g.Sum(ea => ea.EnergyAdjustment), GasConsumption = g.Sum(gc => gc.GasConsumption), GasAdjustment = g.Sum(ga => ga.GasAdjustment), DepartmentID = id }).ToList(); Data total = new Data { EnergyConsumption = (from d in data where d.DepartmentID == id select d.EnergyConsumption).Sum() + (from d in data where d.DepartmentID == id select d.EnergyAdjustment).Sum(), GasConsumption = (from d in data where d.DepartmentID == id select d.GasConsumption).Sum() + (from d in data where d.DepartmentID == id select d.GasAdjustment).Sum(), }; List <Data> orderedData = DataWithDateTime.OrderedData(data); DataTable dt = new DataTable(); DataColumn[] columns = new DataColumn[] { new DataColumn("Dag"), new DataColumn("Energieverbruik"), new DataColumn("Aanpassing energieverbruik"), new DataColumn("Totaal energieverbruik"), new DataColumn("Gasverbruik"), new DataColumn("Aanpassing gasverbruik"), new DataColumn("Totaal gasverbruik") }; dt.Columns.AddRange(columns); foreach (Data d in data) { dt.Rows.Add(d.Date, d.EnergyConsumption, d.EnergyAdjustment, (d.EnergyConsumption + d.EnergyAdjustment), d.GasConsumption, d.GasAdjustment, (d.GasConsumption + d.GasAdjustment)); } string depName = (from d in _context.Department where d.DepartmentID == id select d.Name).ToList()[0]; dt.Rows.Add("TOTAAL -" + depName + ":", "", "", total.EnergyConsumption, "", "", total.GasConsumption); //convert the datatable to csv and return the file string path = _env.WebRootPath + ".\\Content\\Data-" + depName + ".csv"; ToCSV(dt, path); Response.Headers.Add("Content-Disposition", "inline; filename=Data-" + depName + ".csv"); return(File("~/Content/Data-" + depName + ".csv", "tekst/csv", "Data-" + depName + ".csv")); }
public FileResult ExportData() { //Create the datatable List <Data> data = (from d in _context.Data group d by d.Date into g select new Data { Date = g.Key, EnergyConsumption = (g.Sum(ec => ec.EnergyConsumption) + g.Sum(ea => ea.EnergyAdjustment)), GasConsumption = (g.Sum(gc => gc.GasConsumption) + g.Sum(ga => ga.GasAdjustment)), EnergyGenerated = g.Average(eg => eg.EnergyGenerated), EnergyGenAdjustment = g.Average(eg => eg.EnergyGenAdjustment) }).ToList(); List <Data> orderedData = DataWithDateTime.OrderedData(data); Data total = new Data { EnergyConsumption = (from d in data select d.EnergyConsumption).Sum() + (from d in data select d.EnergyAdjustment).Sum(), GasConsumption = (from d in data select d.GasConsumption).Sum() + (from d in data select d.GasAdjustment).Sum(), EnergyGenerated = ((from d in _context.Data group d by d.Date into g select g.Average(eg => eg.EnergyGenerated)).Sum() + (from d in _context.Data group d by d.Date into g select g.Average(eg => eg.EnergyGenAdjustment)).Sum()) }; DataTable dt = new DataTable(); DataColumn[] columns = new DataColumn[] { new DataColumn("Dag"), new DataColumn("Energieverbruik"), new DataColumn("Gasverbruik"), new DataColumn("Oorspronkelijke opgewekte energie"), new DataColumn("Aanpassing opgewekte energie"), new DataColumn("Totaal opgewekte energie") }; dt.Columns.AddRange(columns); foreach (Data d in data) { dt.Rows.Add(d.Date, d.EnergyConsumption, d.GasConsumption, d.EnergyGenerated, d.EnergyGenAdjustment, (d.EnergyGenerated + d.EnergyGenAdjustment)); } dt.Rows.Add("TOTAAL:", total.EnergyConsumption, total.GasConsumption, "", "", total.EnergyGenerated); //convert the datatable to csv and return the file string path = _env.WebRootPath + ".\\Content\\Data-Totaal.csv"; ToCSV(dt, path); Response.Headers.Add("Content-Disposition", "inline; filename=Data-Totaal.csv"); return(File("~/Content/Data-Totaal.csv", "tekst/csv", "Data-totaal.csv")); }
public IActionResult Admin() { List <Data> data = (from d in _context.Data group d by d.Date into g select new Data { Date = g.Key, EnergyConsumption = (g.Sum(ec => ec.EnergyConsumption) + g.Sum(ea => ea.EnergyAdjustment)), GasConsumption = (g.Sum(gc => gc.GasConsumption) + g.Sum(ga => ga.GasAdjustment)), EnergyGenerated = g.Average(eg => eg.EnergyGenerated), EnergyGenAdjustment = g.Average(eg => eg.EnergyGenAdjustment) }).ToList(); List <Data> orderedData = DataWithDateTime.OrderedData(data); Data total = new Data { EnergyConsumption = (from d in data select d.EnergyConsumption).Sum() + (from d in data select d.EnergyAdjustment).Sum(), GasConsumption = (from d in data select d.GasConsumption).Sum() + (from d in data select d.GasAdjustment).Sum(), EnergyGenerated = ((from d in _context.Data group d by d.Date into g select g.Average(eg => eg.EnergyGenerated)).Sum() + (from d in _context.Data group d by d.Date into g select g.Average(eg => eg.EnergyGenAdjustment)).Sum()) }; var tuple = Tuple.Create(_context.Department.ToList(), orderedData, total, true, new Department()); return(View(tuple)); }