public async Task <ActionResult> EnergyConsumption(string venueCodeSelected, string orderStatus)
{
//GET DATA REQUIRED FOR CSV GENERATION
OverviewDAL db = new OverviewDAL();
RentalsDAL rentals_db = new RentalsDAL();
ViewModels.Overview.OverviewViewModel model = new ViewModels.Overview.OverviewViewModel();
model.TrackUnitStartDate = Convert.ToDateTime(Session["StartDate"]);
model.TrackUnitEndDate = Convert.ToDateTime(Session["EndDate"]);
var motorUsages = await GetMotroUsage(model.TrackUnitStartDate, model.TrackUnitEndDate, orderStatus);
model.FuelConsumptionPerMachine = db.GetDWHCustConsPerMachine(ActiveCustomer.DatabaseName, ActiveCustomer.CompanyId, ActiveCustomer.CustomerId, model.TrackUnitStartDate.Date, model.TrackUnitEndDate.Date);
var groupedConsumption = model.FuelConsumptionPerMachine.GroupBy(x => new { x.inventtransid, x.serialNumber });
List <_ConsumptionAndSerialNo> csList = new List <_ConsumptionAndSerialNo>();
foreach (var gc in groupedConsumption)
{
//There will be only one value against the group of inventransid and serialno so we will take the first value
var consumption = gc.Select(x => x.machineconsumption).FirstOrDefault(); //this is the total consumption for the machine
var noOfHours = gc.Select(x => x.machinerunningHourM1).FirstOrDefault(); //this is the total number of hours the above is cost is calculated
var unit = gc.Select(x => x.unit).FirstOrDefault();
csList.Add(new _ConsumptionAndSerialNo {
ConsumptionCost = noOfHours > 0 ? consumption / noOfHours : 0, SerialNo = gc.Key.serialNumber, Unit = unit
});
}
//we will further check if there is more than one serial numbers with different cost value so to take the average of the same.
var finalConsumptionList = csList.GroupBy(x => x.SerialNo).Select(y => new _ConsumptionAndSerialNo {
SerialNo = y.Key, ConsumptionCost = y.Average(z => z.ConsumptionCost), Unit = y.FirstOrDefault().Unit
});
var reportInfo = (from mu in motorUsages
join sc in finalConsumptionList on mu.SerialNumber equals sc.SerialNo
select new _ReportInfo
{
MachineName = mu.MachineType,
MachineType = mu.MachineType,
SerialNumber = mu.SerialNumber,
TransactionDate = mu.TransDate,
Value = mu.RunningHour * sc.ConsumptionCost,
ValueUnit = sc.Unit
}).ToList();
return(CreateReport(Resources.EnergyConsumption, model.TrackUnitStartDate, model.TrackUnitEndDate, reportInfo, "Energy_Consumption"));
}
public async Task <ActionResult> CO2Emissions(string venueCodeSelected, string orderStatus)
{
//GET DATA REQUIRED FOR CSV GENERATION
OverviewDAL db = new OverviewDAL();
RentalsDAL rentals_db = new RentalsDAL();
ViewModels.Overview.OverviewViewModel model = new ViewModels.Overview.OverviewViewModel();
model.TrackUnitStartDate = Convert.ToDateTime(Session["TrackUnitStartDate"]);
model.TrackUnitEndDate = Convert.ToDateTime(Session["TrackUnitEndDate"]);
IEnumerable <RentalOrder> orders = await ActiveCustomer.GetRentalOrders(rentals_db, model.TrackUnitStartDate.Date, model.TrackUnitEndDate.Date);
model.FuelConsumptionPerMachine = db.GetDWHCustConsPerMachine(ActiveCustomer.DatabaseName, ActiveCustomer.CompanyId, ActiveCustomer.CustomerId, model.TrackUnitStartDate, model.TrackUnitEndDate);
if (!string.IsNullOrWhiteSpace(orderStatus))
{
string[] orderStatusCodes = orderStatus.Split(',');
IEnumerable <RentalOrder> FuelConsumptionPerMachineOrders = await ActiveCustomer.GetRentalOrders(new RentalsDAL(), model.TrackUnitStartDate, model.TrackUnitEndDate);
string[] orderInventTransIds = FuelConsumptionPerMachineOrders.Where(i => orderStatusCodes.Contains(i.OrderStatusId)).Select(i => i.InventTransId).ToArray();
model.FuelConsumptionPerMachine = model.FuelConsumptionPerMachine.Where(i => orderInventTransIds.Contains(i.inventtransid));
}
if (!string.IsNullOrEmpty(venueCodeSelected))
{
model.FuelConsumptionPerMachine = model.FuelConsumptionPerMachine.Where(i => i.rorvenuecode == venueCodeSelected).ToList();
}
model.SelectMachines =
model.FuelConsumptionPerMachine
.GroupBy(
i => i.machineType,
(i1, i2) => new SelectMachine()
{
Code = i1, Name = i2.First().machineTypeDesc, Count = i2.Count()
})
.OrderBy(i => i.Name);
//Find the template breeam report
var fi = new FileInfo(HttpContext.Server.MapPath("/App_Data/ReportTemp/breeam-report.xlsx"));
//Load the template into memory and begin adding customer information
using (var breeamReport = new ExcelPackage(fi))
{
var ws = breeamReport.Workbook.Worksheets["H. Materieel"];
ws.Cells["A1"].Value = Resources.CO2ProducedByMachines;
ws.Cells["D1"].Value = Resources.BuildingSiteMachines;
ws.Cells["A7"].Value = Resources.Date;
ws.Cells["B7"].Value = Resources.CompanyName;
ws.Cells["C7"].Value = Resources.MachineType;
ws.Cells["D7"].Value = Resources.Brand;
ws.Cells["E7"].Value = Resources.SerialNumber;
ws.Cells["F7"].Value = Resources.AdverageFuel;
ws.Cells["G7"].Value = Resources.RunningHours;
ws.Cells["H7"].Value = Resources.DailyFuelConsumption;
ws.Cells["I7"].Value = Resources.KGCo2Total;
ws.Cells["J7"].Value = Resources.RentalPeriod;
int rowCount = 8;
foreach (var machine in model.FuelConsumptionPerMachine)
{
string date = model.TrackUnitStartDate.ToShortDateString() + "-" + model.TrackUnitEndDate.ToShortDateString();
RentalOrder order = orders.Where(i => i.InventTransId == machine.inventtransid && i.ObjectId == machine.serialNumber).FirstOrDefault();
string companyName = ActiveCustomer.Name;
string machineType = "N/A";
foreach (var machineModel in model.SelectMachines)
{
if (machineModel.Code == machine.machineType)
{
machineType = machineModel.Name;
break;
}
}
string brandAndModel = machine.machineType;
string serialNumber = machine.serialNumber;
string adverageFuel = string.Format("{0:n1}", (machine.machineconsumption / (Decimal)(model.TrackUnitEndDate - model.TrackUnitStartDate).TotalDays));
string runningHours = string.Format("{0:n1}", machine.machinerunningHourM1);
string dailyConsumption = string.Format("{0:n1}", machine.machineconsumption);
string kgCO2Total = string.Format("{0:n1}", machine.machineEmission);
string rentalPeriod = "N/A";
if (order != null)
{
rentalPeriod = order.DateOnHire.ToShortDateString() + "-" + order.ExpectedDateOffHire.ToShortDateString();
}
ws.Cells["A" + rowCount.ToString()].Value = date;
ws.Cells["B" + rowCount.ToString()].Value = companyName;
ws.Cells["C" + rowCount.ToString()].Value = machineType;
ws.Cells["D" + rowCount.ToString()].Value = brandAndModel;
ws.Cells["E" + rowCount.ToString()].Value = serialNumber;
ws.Cells["F" + rowCount.ToString()].Value = adverageFuel;
ws.Cells["G" + rowCount.ToString()].Value = runningHours;
ws.Cells["H" + rowCount.ToString()].Value = dailyConsumption;
ws.Cells["I" + rowCount.ToString()].Value = kgCO2Total;
ws.Cells["J" + rowCount.ToString()].Value = rentalPeriod;
rowCount++;
}
return(File(breeamReport.GetAsByteArray(), ".xlsx", "BREEAM_" + model.TrackUnitStartDate.ToShortDateString() + "-" + model.TrackUnitEndDate.ToShortDateString() + "_" + ActiveCustomer.Name + ".xlsx"));
};
}
